If session A and session B are concurrently doing the same master-detail transaction, wouldn't currval possibly reflect the sequence value used by the other session ? Or are you saying that since this will be an explicit transaction that currval won't reflect the fact that the sequence may have been incremented by another session ?
On Thursday 13 June 2002 02:06 pm, Stephan Szabo wrote: > 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 6: Have you searched our list archives? http://archives.postgresql.org
