Skip site navigation (1) Skip section navigation (2)

Re: checking update/insert return

From: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
To: <olly(at)lfix(dot)co(dot)uk>
Cc: <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: checking update/insert return
Date: 2004-01-05 22:50:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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.

In response to

pgsql-novice by date

Next:From: vhikidaDate: 2004-01-06 00:19:44
Subject: Re: postgresql in windows
Previous:From: Marcus Andree S. MagalhaesDate: 2004-01-05 22:40:32
Subject: Re: checking update/insert return

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group