serial column vs. explicit sequence question
От | Charlie Toohey |
---|---|
Тема | serial column vs. explicit sequence question |
Дата | |
Msg-id | 20020613200344.1B97A476B74@postgresql.org обсуждение исходный текст |
Ответы |
Re: serial column vs. explicit sequence question
Re: serial column vs. explicit sequence question Re: serial column vs. explicit sequence question Re: serial column vs. explicit sequence question |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: