Re: [GENERAL] Simple problem?

On Thu, Mar 02, 2000 at 04:17:30PM +0100, Peter Eisentraut wrote:
> On Wed, 1 Mar 2000, Stan Jacobs wrote:
>
> > I must be confusing my syntax somehow, because I'm having trouble doing a
> > simple update with returned id... As in.... I have two tables: an order
> > table and an orderitems table. I need to write the order record, then use
> > the order_id from the order record in the insert to the orderitems record.
>
> I assume you're using serial columns here. Table schemas always help ...
>
> > 1. Can I use a transaction begin/end for the entire transaction if I need
> > to get the order_id in between the two writes?
>
> Sure.
>
> > 2. How do I get the order_id from the record I just wrote?
>
> You have to select it back out. A select on max(order_id) might do, but
> you might get caught if someone else does updates on your table as well. I
> am personally not so fond of serial columns because of this problem.

Peter, I was _sure_ you were around for the last few times this has come up.
PostgreSQL's 'serial' pseudo type (convenience type?) is multiuser safe.

Stan, there's a couple approaches to solving your problem:

Some (such as Tom) prefer the programmatic solution: Create a sequence
(either manually or automatically by using a 'serial' type), select
the nextval() from the sequence yourself, then insert it, rather than
depending on the default. However, it is also possible to do:

SELECT currval('table_field_seq');

Note that you're passing a string constant to the function currval,
and that constant happens to be the name of the sequence to act on. If
you sequence has MultiCaps (like if you used quoted "InitCaps" in table
or field names), you'll need the double quotes, inside the single quotes:

SELECT currval('"Table_Field_seq"');

This is multiuser safe, since currval is defined for each SQL session
(basically, from open of database to close of connection.) The only
problem with it is that currval() for a sequence is undefined in
that session until a nextval() has happened. Here's a transcript,
demonstrating the problem, as well as the multiuser safety: (NOTICEs
wrapped for readability)

test=> create table test (s serial, t text);
NOTICE: CREATE TABLE will create implicit sequence 'test_s_seq' for
SERIAL column 'test.s'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_s_key'
for table 'test'
CREATE
test=> select currval('test_s_seq');
ERROR: test_s_seq.currval is not yet defined in this session
test=> insert into test (t) values ('some text');
INSERT 883807 1
test=> select currval('test_s_seq');
currval
-------
1
(1 row)

--------- open another session in a seperate xterm -----

test=> select currval('test_s_seq');
ERROR: test_s_seq.currval is not yet defined in this session
test=> insert into test (t) values ('some other text');
INSERT 883808 1
test=> select currval('test_s_seq');
currval
-------
2
(1 row)

test=>

--------- back to first session -----------------------

test=> select currval('test_s_seq');
currval
-------
1
(1 row)

test=>

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005