Re: A problem with sequences... - Mailing list pgsql-general

>
>
>Well, in that case you need to setval() the sequence to something bigger than
>any used numbers after the import.
>
>
I know. (And I do).

>OK - let's look at the rule:
>
>create table answer
>(
>    id    serial primary key,
>    data  text
>);
>
>create rule answer_id_seq as on insert to answer do select coalesce (new.id,
>last_value) as id from answer_id_seq;
>
>Well - you're going to have problems if you do something like:
>
>INSERT INTO answer (data) (SELECT d FROM foo);
>
>I daresay you're not, but something to bear in mind.
>
That's fine. The problen in that case would be that I'll be always
getting back the last id inserted, right.
I've seen this happenning, and it is OK, because I only need that output
in this java app, and the java app only inserts them
one at a time.
(In fact, the most annoying thing with statement like that is having to
scroll through all of that useless output if there are too many rows in
foo - so, I usually just disable the rule whenever I am about to do
something like that)....

>
>To see why you're getting problems with duplicate ID numbers, open two psql
>windows and do:
>
>1> SELECT nextval('answer_id_seq');
>2> SELECT nextval('answer_id_seq');
>1> SELECT last_value FROM answer_id_seq;
>2> SELECT last_value FROM answer_id_seq;
>
>As you'll see, last_value isn't concurrent-safe like currval() and nextval()
>are.
>
Well... yeah. I know this. That's why I said in the very beginning, that
this rule is no good.
*However* this would only exp[lain a situation with my java app getting
an incorrect id back after inserting a row, but it never uses that id to
insert other rows (if it did, it would cause a duplication even if the
rule was safe), so, I still don't see any reason how this would cause a
duplicated id to be inserted - nextval() should still return unique
numbers, right? Once again, I know that last_val may not be the same
thing nextval() just returned, but the real question is how can this
possibly cause a duplicated id to be genrated???

>
>So - if you want to keep your rule, you'll want to rewrite it to use currval()
>as you mentioned.
>
>
Nah... I guess, I'll rather do something in the java (append ";select
currval..." to the insert statement).

>Personally, I'd write a function to insert into the table and make the app use
>that, or create a view and have the app insert via that. Getting a result-set
>back from an insert would spook me if I wasn't expecting it.
>
Well... If you do not expect it, you can just ignore it - it's not a big
deal :-)
I don't see how having a view would help...

As for the function, I would have to create many of them - one for each
table I need to insert into, because the arguments would be different,
and with the rule I was able to get away with only writing it once ...
If postgres had something like (void *) to pass to a function (and also
if it allowed variable number of parameters), I would ceratinly stick
with the function solution, because then I would also be able to cache a
query plan...

Dima