From: | Charlie Toohey <ctoohey(at)pacbell(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | serial column vs. explicit sequence question |
Date: | 2002-06-13 20:03:45 |
Message-ID: | 20020613200344.1B97A476B74@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-06-13 20:07:26 | Re: Another postgres 'file not found' error |
Previous Message | Josh Berkus | 2002-06-13 19:41:10 | Isn't there a better way? |