Re: Bug?
От | Andrew G. Hammond |
---|---|
Тема | Re: Bug? |
Дата | |
Msg-id | E164SD4-00033n-00@xyzzy.lan.internal обсуждение исходный текст |
Ответ на | Re: Bug? (Doug McNaught <doug@wireboard.com>) |
Ответы |
Re: Bug?
Re: Bug? |
Список | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2001 November 15 10:24 am, Doug McNaught wrote: > Rasmus Resen Amossen <NOSPAM@hey.com> writes: > > CREATE TABLE test ( > > id SERIAL NOT NULL, > > val INT > > ); > > > > The the following insertions creates an error: > > INSERT INTO test(id,val) VALUES (1,1); > > INSERT INTO test(val) VALUES (1); > > > > Error: > > Cannot insert a duplicate key into unique index test_id_key > > > > After that I can just reåeat the second line and insert without errors. > > Is this bug fixed in newer versions? > > Not a bug. The second insert of the two above increments the SERIAL > sequence for 'id', and the incremented value is the same as the one > you explicitly inserted earlier. Since SERIAL columns have a unique > index on them, the insert fails. > > When you repeat the second insert, the sequence is incremented again, > and this time it doesn't collide. Now maybe THAT behaviour should be a bug. Since the nextval() is implicit in the second INSERT, and the second INSERT fails, it would make sense to roll back the nextval(). Except of course that it appears that sequences are not within the scope of transactions: test=# SELECT nextval('test_id_seq'); nextval - --------- 3 (1 row) test=# BEGIN; SELECT nextval('test_id_seq'); ROLLBACK; BEGIN nextval - --------- 4 (1 row) ROLLBACK test=# SELECT nextval('test_id_seq'); nextval - --------- 5 (1 row) Is this correct behaviour? ACID compliant? The argument in it's favour it pretty obvious: if you never go backwards then you'll never get an accidental duplication as follows: t1 BEGIN; SELECT nextval('test_id_seq') # 1 t2 SELECT nextval('test_id_seq') # 2 t1 ROLLBACK # test_id_seq decremented t3 SELECT nextval('test_id_seq') # 2 However wouldn't locking the sequence be more proper behaviour? - -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjv0F7cACgkQCT73CrRXhLG4NQCfV38y9IJyx9QYPaucjGY2B5Bf AN8AnjtNjXGn09GDVikNDzx1f/5tb9vk =0NwR -----END PGP SIGNATURE-----
В списке pgsql-general по дате отправления: