Re: ISOLATION LEVEL SERIALIZABLE

power2themacs <power2themacs(at)yahoo(dot)com> writes:

> >In table two you would not create a serial instead you would create an
> >INTEGER because serial is a counter and the values in table 2 may not be
> >in table 1.
> >
> >Use a transaction like as follows
> >
> >BEGIN;
> >INSERT INTO TABLE1 VALUES (Whatever values);
> >var = SELECT CURRVAL('sequence_name');
> >INSERT INTO TABLE2 VALUES (var,whatever else);
> >COMMIT;
> >
>
> But this is the race condition I am trying to avoid. Someone can
> insert before I get the currval and it will beincremented and this
> will result in invalid data. Right now, I'm doing exactly that but I
> add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
> locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
> PG Explorer!

Actually currval is precisely what you need. It will return the
current value of the sequence in question for your particular backend
without paying attention to what might be going on in another
connection. So the above transaction is perfectly safe, and is, in
fact, the standard way of writing these sorts of transactions in
PostgreSQL.

So you can rest assured that I am not making this up, here's the
relevant bit from the PostgreSQL documentation.

currval

Return the value most recently obtained by nextval for this
sequence in the current server process. (An error is reported
if nextval has never been called for this sequence in this
process.) Notice that because this is returning a
process-local value, it gives a predictable answer even if
other server processes are executing nextval meanwhile.

I hope this is helpful,

Jason