Re: serial column vs. explicit sequence question

Charlie Toohey <ctoohey@pacbell.net> writes:

> 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

The SERIAL type is a thin veneer over an underlying conglomeration of
a unique index and a sequence, nothing more, nothing less.  I still
prefer to use the old syntax that spelled this out explicitly (mostly
because it reminded me that I needed to drop the sequences as well as
the table if I made changes during the development phases of my
project).  Instead of using a serial type I have a whole pile of
scripts that contain bits that look like this:

DROP TABLE prod_journal;
DROP SEQUENCE prod_journal_id_seq;

CREATE SEQUENCE prod_journal_id_seq;

CREATE TABLE prod_journal (      id            int PRIMARY KEY                       DEFAULT
nextval('prod_journal_id_seq'),     ...
 
);

The SERIAL type does precisely the same sort of thing.  The only
difference is that PostgreSQL thinks up the sequence name for you
(currently PostgreSQL tries to choose a name that looks precisely like
the one I have chosen in this example).  The reason that I bring this
up is A) it makes me happy to think that I have been using PostgreSQL
long enough that my PostgreSQL memories predate the SERIAL type, and
B) to point out that there is not really a difference between using
the SERIAL type and using sequences explicitly.

What you *really* need is to get acquainted with the nifty sequence
functions currval and nextval.  They hold the secret to sequence
Nirvana.  See Chapter 4 Section 11 of the PostgreSQL User's Guide for
the full scoop.  The short story is that curval gives the current
value of the sequence (for whichever backend you are connected to) and
nextval will give you the next value of the sequence.

Now let's say that you had two simple tables foo for the master record
and bar for the detail records.

test=# create table foo (id serial primary key, name text);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL column 'foo.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE

test=# create table bar (master int references foo, detail text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

You could then insert into these tables using something like this:

test=# begin;
BEGIN
test=# insert into foo (name) values ('Jason');
INSERT 67423220 1
test=# insert into bar (master, detail) values (currval('foo_id_seq'), 'Does this work');
INSERT 67423221 1
test=# insert into bar (master, detail) values (currval('foo_id_seq'), 'Apparently So!');
INSERT 67423222 1
test=# commit;
COMMIT

As long as you hang onto your connection to the back end you don't
even have to wrap this as one transaction.  Currval is connection
dependent, and so as long as you have the same connection currval will
give the correct answer, and currval is very very fast.

Hope this was helpful,
Jason