Content-Type: text/plain;
  charset="iso-8859-15"
From: Charlie Toohey <ctoohey@pacbell.net>
To: pgsql-sql@postgresql.org
Subject: serial column vs. explicit sequence question
Date: Thu, 13 Jun 2002 13:03:45 -0700
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <20020613200344.1B97A476B74@postgresql.org>

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 ?

Thanks,
Charlie