Re: [GENERAL] Simple problem? - Mailing list pgsql-general
From | Ross J. Reedstrom |
---|---|
Subject | Re: [GENERAL] Simple problem? |
Date | |
Msg-id | 20000302110915.C18649@rice.edu Whole thread Raw |
In response to | Re: [GENERAL] Simple problem? (Peter Eisentraut <e99re41@DoCS.UU.SE>) |
Responses |
Re: [GENERAL] Simple problem?
|
List | pgsql-general |
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@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
pgsql-general by date: