Date: Thu, 13 Jun 2002 14:06:06 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Charlie Toohey <ctoohey@pacbell.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: serial column vs. explicit sequence question
In-Reply-To: <20020613200344.1B97A476B74@postgresql.org>
Message-ID: <20020613140449.Q7376-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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