On Thu, 13 Jun 2002, Charlie Toohey wrote: > 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 ?
Well, serial really is just an integer with a default value pulling from a sequence, so right now you can use currval on the sequence (which I think gets named something like <table>_<column>_seq ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
