Re: Fw: postgresql experts please help

> If you have an after trigger on the table that you're inserting into and
> it inserts into another table with a serial column, the lastval call will
> use the wrong sequence. Now this is also a problem with currval if you
That's a pretty exotic scenario...
Looks like lastval is good enough - the same applies to Sybase @@Identity.

Using currval actually seems much more dangerous (as my test shows):
Obviously there's more than one man_id_seq in our db, how to find the right
one to call currval on?

I can select from tables (without providing scheme name) - the same should
be right for the sequences.
That means that the sequence defined in my schema is NOT the one that was
created with CREATE TABLE MAN(ID SERIAL...

Then where this (created with the table) sequence is?
Is it in some kind of system scheme?
If so - what would happen if we create the same named tables in two schema -
would then sequences override each other?

Apparently I had a preexisting sequence named man_id_seq in my scheme -> and
that caused postgresql to create a new sequence somewhere else.
As soon as I deleted this sequence my test strated working!

Thanks a lot for your help, it's greatly appreciated,

Andrei

IMO this behaviour (if there's existing sequence create another one with the
same name but in some other place) is very wrong - be predictible, throw
exception.

----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Andrei Ilitchev" <andrei(dot)ilitchev(at)oracle(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>; "Marina Vatkina" <Marina(dot)Vatkina(at)Sun(dot)COM>
Sent: Thursday, October 18, 2007 3:09 PM
Subject: Re: [JDBC] Fw: postgresql experts please help

>
>
> On Thu, 18 Oct 2007, Andrei Ilitchev wrote:
>
>> In the docs I discovered "select lastval()" which seems to behave exactly
>> like @@Identity in Sybase returning the latest nextval that was obtained
>> in the current session by no-matter-which sequence.
>> That's exactly the functionality I need - much easier to use because no
>> need to track the name.
>>
>> Can you think of any reason why using "select lastval()" would be a bad
>> idea?
>>
>
> If you have an after trigger on the table that you're inserting into and
> it inserts into another table with a serial column, the lastval call will
> use the wrong sequence. Now this is also a problem with currval if you
> have a trigger that inserts into the same table, but that's an unlikely
> application design. Inserting into another table is something that you'll
> find with systems that do auditing or sometimes horizontal partitioning of
> a table.
>
> Kris Jurka
>