Thread: checking update/insert return
Dear list,
Is there any way to check a successful insertion or update on a table
in plpgsql?
We have a highly concurrent system here, and we want to return a valid
and unique ID to the caller, like the following pseudo code (no flames,
didatic use only ;-):
while (true)
found = select count (*) from mytable where id = $id
if (found == 0) /* non existent id */
insert into mytable (id. name) values ($id, $name)
/* someone in parallel could have inserted the same id before
so we need to check if this insertion was OK, but how??? */
if (INSERTED) return $id /* we inserted our id with success */
else $id = $id + 1 /* someone has used this id, increment it
and try again */
end if
else
$id = $id + 1 /*id already exists*/
end if
end while
My question is, is there any postgres internal boolean function
that somewhat resembles what I described here as "INSERTED" ???
I did some research and found an internal function called FOUND, but
it seems to work only with select.
In short, how to determine if an insert (or update) clause has
ended with success? The backend seems to indicate this, by a INSERT
return...
Any help is welcome
Thanks.
Hi, Why do you not use the serial data type : SERIAL ? EX. : create table mytable ( id SERIAL, name text); <- or anything else So, when you want to insert data your request becomes : insert into mytable (name) values ($name) And you are sure that id is unique (you do not need your loop, your tests, ...). Bruno Marcus Andree S. Magalhaes wrote: >Dear list, > >Is there any way to check a successful insertion or update on a table >in plpgsql? > >We have a highly concurrent system here, and we want to return a valid >and unique ID to the caller, like the following pseudo code (no flames, >didatic use only ;-): > >while (true) > found = select count (*) from mytable where id = $id > if (found == 0) /* non existent id */ > insert into mytable (id. name) values ($id, $name) > /* someone in parallel could have inserted the same id before > so we need to check if this insertion was OK, but how??? */ > if (INSERTED) return $id /* we inserted our id with success */ > else $id = $id + 1 /* someone has used this id, increment it > and try again */ > end if > else > $id = $id + 1 /*id already exists*/ > end if >end while > >My question is, is there any postgres internal boolean function >that somewhat resembles what I described here as "INSERTED" ??? > >I did some research and found an internal function called FOUND, but >it seems to work only with select. > >In short, how to determine if an insert (or update) clause has >ended with success? The backend seems to indicate this, by a INSERT >return... > >Any help is welcome > >Thanks. > > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > -- Bruno LEVEQUE System Engineer SARL NET6D bruno.leveque@net6d.com http://www.net6d.com
On Mon, 2004-01-05 at 20:54, Marcus Andree S. Magalhaes wrote:
> Dear list,
>
> Is there any way to check a successful insertion or update on a table
> in plpgsql?
>
> We have a highly concurrent system here, and we want to return a valid
> and unique ID to the caller, like the following pseudo code (no flames,
> didatic use only ;-):
>
> while (true)
> found = select count (*) from mytable where id = $id
> if (found == 0) /* non existent id */
> insert into mytable (id. name) values ($id, $name)
> /* someone in parallel could have inserted the same id before
> so we need to check if this insertion was OK, but how??? */
If it already existed, this insertion would fail and would abort the
current transaction. Therefore finding that out at this stage would be
academic :-(
> if (INSERTED) return $id /* we inserted our id with success */
> else $id = $id + 1 /* someone has used this id, increment it
> and try again */
> end if
> else
> $id = $id + 1 /*id already exists*/
> end if
> end while
>
> My question is, is there any postgres internal boolean function
> that somewhat resembles what I described here as "INSERTED" ???
The normal way of doing this is to use a sequence:
declare the column of type SERIAL;
insert the value DEFAULT;
SELECT currval('schema.table_column_seq') to get the value just
assigned.
The only problem with that is that it does not guarantee a continuous
sequence of ids in the table; some applications do require that and need
some other mechanism to achieve it.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And thou shalt love the LORD thy God with all thine
heart, and with all thy soul, and with all thy might."
Deuteronomy 6:5
> Hi, > > Why do you not use the serial data type : SERIAL ? > I must guarantee to the client-side that the ID field has no holes....
Hi, thanks for your message.
>> while (true)
>> found = select count (*) from mytable where id = $id
>> if (found == 0) /* non existent id */
>> insert into mytable (id. name) values ($id, $name)
>> /* someone in parallel could have inserted the same id before
>> so we need to check if this insertion was OK, but how??? */
>
> If it already existed, this insertion would fail and would abort the
> current transaction. Therefore finding that out at this stage would be
> academic :-(
>
I see the problem now. It's amazing how simple tasks can be really
funny... ;)
>> if (INSERTED) return $id /* we inserted our id with success */
>> else $id = $id + 1 /* someone has used this id, increment it
>> and try again */
>> end if
>> else
>> $id = $id + 1 /*id already exists*/
>> end if
>> end while
>>
>> My question is, is there any postgres internal boolean function
>> that somewhat resembles what I described here as "INSERTED" ???
>
> The normal way of doing this is to use a sequence:
> declare the column of type SERIAL;
> insert the value DEFAULT;
> SELECT currval('schema.table_column_seq') to get the value just
> assigned.
>
> The only problem with that is that it does not guarantee a continuous
> sequence of ids in the table; some applications do require that and need
> some other mechanism to achieve it.
>
Yeah, but that's why I can't use it... Must provide really sequencial
numbers, without holes.
This all leads me to another question... when we use psql, we can tell
that out insert was OK because a number (OID?) is returned to the client.
Maybe other client interfaces can also access the OID of the just-inserted
data as a mean of checking the success of the entire operation and this
problem will be solved entirely in the client.
On Tue, 2004-01-06 at 11:40, Marcus Andree S. Magalhaes wrote:
> > Hi,
> >
> > Why do you not use the serial data type : SERIAL ?
> >
>
> I must guarantee to the client-side that the ID field
> has no holes....
If you _really_ have to do that, then the only way [I have thought of
over the years] to do it in a transaction safe manner is to pre-allocate
numbers, creating records (just the ID number) in a second table. Like
pulling raffle tickets out of a book.
Then, when you want a number, you:
BEGIN
SELECT first unused pre-allocated number FOR UPDATE
DELETE the pre-allocated number
INSERT empty 'it-didn't-happen-yet' record with pre-allocated number
COMMIT
BEGIN
...
do other important stuff
...
COMMIT
Then, if you roll back the first transaction, the DELETE never happened,
and the INSERT never happened, so the next transaction comes along and
uses that code.
If you roll back the second transaction, then you are still left with an
'empty' record in your table, but there _is_ a record there. You could
either (a) leave it like that, and be happy, or (b) have a process which
goes along later and removes them while re-pre-allocating the number,
which I would say is probably more pain than it's worth. Of course the
whole thing is more pain than it's worth, really :-)
Of course something this does not address is _ordering_ of these
records, but you can't guarantee ordering on a multi-user system anyway
unless you make it stop being multi-user for a while.
In the past when people have asked me for this "every code is used"
approach (usually accountants) I've told them "No!", and then baffled
them with justification about how hard it is until their eyes glazed
over.
Then, of course, when I've got them off my back I just can't help
worrying at the problem until I get some sort of half-assed solution
together :-)
Cheers,
Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
The Killer Ducks are coming!!!
-------------------------------------------------------------------------