Content-Type: text/plain;
  charset="iso-8859-1"
From: Charlie Toohey <ctoohey@pacbell.net>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: serial column vs. explicit sequence question
Date: Thu, 13 Jun 2002 15:09:10 -0700
Cc: pgsql-sql@postgresql.org
References: <20020613140449.Q7376-100000@megazone23.bigpanda.com>
In-Reply-To: <20020613140449.Q7376-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <20020613220908.A18D2477115@postgresql.org>

If session A and session B are concurrently doing the same master-detail 
transaction, wouldn't currval possibly reflect the sequence value used by the 
other session ? Or are you saying that since this will be an explicit 
transaction that currval won't reflect the fact that the sequence may have 
been incremented by another session ?


On Thursday 13 June 2002 02:06 pm, Stephan Szabo wrote:
> On Thu, 13 Jun 2002, Charlie Toohey wrote:
> > I'm having a problem and there seems to be 2 solutions. It is simple and
> > straighforward, but will take several paragraphs to explain.
> >
> > I have a schema with a master-detail design. The master table does not
> > have an expicit id, so I have a column of type serial.
> >
> > Lets say I need to insert a row into the master table and N rows into the
> > detail table. After inserting a row into master, and before detail, I
> > need to read the master table to obtain the value of the id for the row
> > just inserted, so I can insert this id as the foreign key value for the N
> > rows in the detail table.
> >
> > This seems like a poor solution because I have to write and then read the
> > master table each time. With lot of activity on these tables, I don't
> > know how well this will scale. Additionally, the only way that I can
> > guarantee that I am getting the id of the most recent row inserted into
> > master is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --- because
> > otherwise, if other processes are inserting rows into master/detail
> > concurrently, I may pick up the id from an incorrect row (the technique
> > used to get the correct id is to include a timestamp column on the insert
> > into master and then query for the latest row).
> >
> > A better solution would seem to use a sequence explicitly, rather than a
> > id column of type serial. I would obtain the id value from the sequence,
> > and then insert this id into the master table and into the detail table.
> > This way, I wouldn't be writing/reading the same table constantly -- I
> > would only be writing to it, and, I would guarantee that I would be using
> > the correct id in both master and detail without have to SET TRANSACTION
> > ISOLATION LEVEL SERIALIZEABLE.
> >
> > Any comments on which solution you would choose, or is there a better
> > solution ?
>
> Well, serial really is just an integer with a default value pulling from a
> sequence, so right now you can use currval on the sequence (which I think
> gets named something like <table>_<column>_seq