Re: Serials: removing the holes? (consecutive)
От | Masaru Sugawara |
---|---|
Тема | Re: Serials: removing the holes? (consecutive) |
Дата | |
Msg-id | 20020805192451.900E.RK73@sea.plala.or.jp обсуждение исходный текст |
Ответ на | Re: Serials: removing the holes? (consecutive) (Masaru Sugawara <rk73@sea.plala.or.jp>) |
Список | pgsql-general |
On Sun, 4 Aug 2002 09:02:02 -0500 "Leland F. Jackson, CPA" <smvfp@mail.smvfp.com> wrote: > It is usually a bad idea to change a primary unique key in a table once it > has be placed into use. This can especially be problamatic where other > tables have foreign key constraints referenced into the table's primary key > to enforce referential integrity. > Also, the concept of next record and previous record does not exist in most > enterprise class databases. All records are accessed by selection. This > include postgres. Therefore, it is not possible to step through a postgres > table one record at a time while updating the unique primary key with a new > sequence number. Further complicating matters is when establishing a new > sequence of number the danger exist that a currently used number may be > duplicated causing an error with corresponding roll back to all updates by > postgres. I would also think that most DBs have none of such a concept, and it is probably hard to avoid being duplicated the primary key by some other sessions while a table's owner updates them. However, many of relational DB have LOCK modes. if a target table is locked with an appropriate mode, it is possible to prevent the others from inserting and deleting. And as far as v7.2.1 at least, it seems to me that the owner don't need to be anxious about the duplications even though a roll back occurs. Because a new sequence is supposed to only have the numbers within the maximum number of the old sequence(see below). Actually, if I intentionally add an error statement next the UPDATE statement in the transaction, then the transaction is aborted. But PG will succeed in rolling back without duplicating. old key temp key new key ------------------------------------- 1 1 1 3 -> 2 -> 2 updating if old key <> temp key 4 -> 3 -> 3 updating if old key <> temp key 5 -> 4 -> 4 updating if old key <> temp key (setval) 5 new inserting > However, if you wanted to proceed with this, I would build a file, probably > using perl, of the OID numbers. Each row of your table should have a unique > OID number which postgres automatically add of each row in a table at the > time the row is inserted. > > Once I had a file of OID numbers, I would build a program that read each > line of the file, grab the corresponding row in your table and insert the > new sequence number. I believe your approach is one of the right ways. BTW, where do you think you are going to insert the new sequence number ? If into new column, you'll probably have to treat the new rows that inserted while you are grabbing the old rows, I guess. After all, I think this way also needs to lock tables and/or to kill all connections so that no rows are inserted or deleted. But, from a reliable point of view, I realize your thought is better than mine because of no change of a primary key From your reply, I notice I should have said that there were some dangerous points if a primary key was updated, and there is still a bag in my idea. Thank you for your advice. Note: * to change the number of a primary key is not good(but I don't think it is bad). * how to deal with some other tables that refer to the primary key of a target table as a foreign key * whenever setval() is executed unless it is in the outside of a transaction which includes a lock mode, there is possibility that changing a sequence will cause an error of a duplicate key * the fact that this method may not run correctly in the prior PG versions. * this method depends on the behavior of PG's sequence-and- transaction systems, and basically no error occurs unless updating is executed in the order of the sort * LIMIT ALL statement cannot be removed from the UPDATE statement, because the temp. sequence acts unexpectedly in the WHERE clause if there is not its statement in the subquery. * and, etc. -- to eliminate gaps of the sequence BEGIN; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; CREATE TEMP SEQUENCE seq_n; UPDATE tbl SET a = t1.i FROM (SELECT t0.*, nextval('seq_n') AS i FROM (SELECT * FROM tbl ORDER BY a) AS t0 LIMIT ALL ) AS t1 WHERE t1.a <> t1.i AND tbl.a = t1.a; DROP SEQUENCE seq_n; -- SELECT setval('seq_p_key', (SELECT COUNT(*) FROM tbl)); <- mistake SELECT setval('seq_p_key', (SELECT max(a) FROM tbl)); END; Regards, Masaru Sugawara
В списке pgsql-general по дате отправления:
Предыдущее
От: marko.asplund@kronodoc.fiДата:
Сообщение: concept question: PostgreSQL vs. Oracle database
Следующее
От: Elielson FontaneziДата:
Сообщение: RES: concept question: PostgreSQL vs. Oracle database