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: | 64366.200.174.148.100.1073343048.squirrel@webmail.webnow.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | vhikida | 2004-01-06 00:19:44 | Re: postgresql in windows |
Previous Message | Marcus Andree S. Magalhaes | 2004-01-05 22:40:32 | Re: checking update/insert return |