Обсуждение: BUG #5606: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the same
The following bug has been logged online: Bug reference: 5606 Logged by: Frank Heikens Email address: f.heikens@anva.nl PostgreSQL version: PostgreSQL9.0b4 Operating system: WinXP Description: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the same Details: Today I checked out the wiki and did some tests using the deferrable unique constraints described at: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE_UN IQUE_CONSTRAINTS I copied/pasted the examples and to my surprise, DEFERRABLE and DEFERRABLE INITIALLY DEFERRED work exactly the same, there is no need to use CONSTRAINTS ALL DEFERRED; at all. The wiki and manual describe the need for this, but no matter what, it always works the same. (see tests and results below) It's tested on a local machine, Windows XP, 32bit, using the EnterpriseDB-installer: PostgreSQL 9.0beta4, compiled by Visual C++ build 1500, 32-bit -- Test 1 CREATE TABLE test (a int primary key); INSERT INTO test values (1), (2); UPDATE test set a = a+1; -- Result: Error: duplicate key value violates unique constraint "test_pkey" -- This is Ok. -- Test 2 CREATE TABLE test (a int primary key deferrable); INSERT INTO test values (1),(2); UPDATE test set a = a+1; -- Result: Query returned successfully: 2 rows affected, 15 ms execution time. -- Not Ok, see the manual and wiki, should result in the same error as test 1. -- Test 3 CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED); INSERT INTO test values (1),(2); UPDATE test set a = a+1; -- Result: Query returned successfully: 2 rows affected, 31 ms execution time. -- This is Ok. Each test was executed using it's own connection. If you need more information, just let me know. Kind regards, Frank Heikens PostgreSQL DBA ANVA Postbus 190, 3800 AD Amersfoort Stadsring 201, 3817 BA Amersfoort T: +31 (0)33 479 82 00 F: +31 (0)33 472 78 22 www.anva.nl
"Frank Heikens" <f.heikens@anva.nl> writes:
> Description: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the
> same
This test proves no such thing. You are supposing that a deferrable
unique index has the exact same behavior as a nondeferrable one.
Actually, a DEFERRABLE IMMEDIATE index enforces its checks as of the end
of the statement, which is per spec.
regards, tom lane
Frank Heikens <f.heikens@anva.nl> writes: > The wikipage says >> a deferrable constraint CAN be checked at the end of a >> transaction. You still have to ask PostgreSQL to defer it. > http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE_UNIQUE_CONSTRAINTS > I don't have to ask PostgreSQL to defer, it works in the second test as well in the third test without any changes. I guess the example in the wiki isn't correct, right? No, not even a little bit :-(. I hadn't seen that text; it needs to be corrected. Will hack on it in a moment. > A single UPDATE touching all records will check the uniqueness after the complete update, not right after each update ofa row as it does on non-deferrable constraints. Right. non-deferrable -> uniqueness is enforced after each row change DEFERRABLE IMMEDIATE -> uniqueness is enforced at end of statement DEFERRABLE DEFERRED -> uniqueness is enforced at end of transaction > Maybe someone could make this clear in the manual and wiki? AFAIK the manual is OK; do you see a place where it gets this wrong? regards, tom lane
Frank Heikens <f.heikens@anva.nl> writes: >> http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE_UNIQUE_CONSTRAINTS >> I don't have to ask PostgreSQL to defer, it works in the second test >> as well in the third test without any changes. I guess the example in >> the wiki isn't correct, right? > No, not even a little bit :-(. I hadn't seen that text; it needs to be > corrected. Will hack on it in a moment. I edited that wiki section, see what you think. regards, tom lane
On 6 August 2010 16:25, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Frank Heikens <f.heikens@anva.nl> writes: >>> http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRAB= LE_UNIQUE_CONSTRAINTS > >>> I don't have to ask PostgreSQL to defer, it works in the second test >>> as well in the third test without any changes. I guess the example in >>> the wiki isn't correct, right? > >> No, not even a little bit :-(. =A0I hadn't seen that text; it needs to be >> corrected. =A0Will hack on it in a moment. > > I edited that wiki section, see what you think. > Yes, that's much better. I didn't read this page until just now. I did, however, read the release notes, and I didn't spot a similar error t= here: """ This allows UPDATE tab SET col =3D col + 1 to work on columns that have a unique indexes or are marked as primary key, but DEFERRABLE INITIALLY DEFERRED must be used to mark the constraint as deferred. """ Perhaps this should be something like """ This allows UPDATE tab SET col =3D col + 1 to work on columns that have unique indexes or are marked as primary keys. If the constraint is specified as DEFERRABLE it will be checked at the end of the statement rather than as each row is updated. The constraint check may also be DEFERRED until the end of the current transaction allowing updates to be spread over multiple SQL commands. """ Regards, Dean
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On 6 August 2010 16:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I edited that wiki section, see what you think.
> Yes, that's much better. I didn't read this page until just now.
> I did, however, read the release notes, and I didn't spot a similar error there:
> """
> This allows UPDATE tab SET col = col + 1 to work on columns that have
> a unique indexes or are marked as primary key, but DEFERRABLE
> INITIALLY DEFERRED must be used to mark the constraint as deferred.
> """
Hum, maybe that's where the wiki author's mistake came from :-(. Will fix.
regards, tom lane
Hi Tom, The wikipage says > a deferrable constraint CAN be checked at the end of a > transaction. You still have to ask PostgreSQL to defer it. http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE_U= NIQUE_CONSTRAINTS I don't have to ask PostgreSQL to defer, it works in the second test as wel= l in the third test without any changes. I guess the example in the wiki is= n't correct, right? A single UPDATE touching all records will check the uni= queness after the complete update, not right after each update of a row as = it does on non-deferrable constraints. This one fails, as it should: CREATE TABLE test (a int primary key deferrable, b int ) INSERT INTO test values (1,1),(2,2); BEGIN; UPDATE test SET a =3D a+1 WHERE b =3D 1; -- FAIL: ERROR: duplic= ate key value violates unique constraint "test_pkey" UPDATE test SET a =3D a+1 WHERE b =3D 2; COMMIT; And this one works fine, as it should: CREATE TABLE test (a int primary key deferrable, b int ) INSERT INTO test values (1,1),(2,2); BEGIN; SET constraints ALL deferred; UPDATE test SET a =3D a+1 WHERE b =3D 1; UPDATE test SET a =3D a+1 WHERE b =3D 2; COMMIT; And this one works fine as well, and got me confused: CREATE TABLE test (a int primary key deferrable, b int ) INSERT INTO test values (1,1),(2,2); UPDATE test set a =3D a+1; -- single UPDATE statement, only checks after al= l updates, didn't see it coming. Maybe someone could make this clear in the manual and wiki? Regards, Frank Heikens -----Oorspronkelijk bericht----- Van: Tom Lane [mailto:tgl@sss.pgh.pa.us] Verzonden: vrijdag 6 augustus 2010 16:14 Aan: Frank Heikens CC: pgsql-bugs@postgresql.org Onderwerp: Re: [BUGS] BUG #5606: DEFERRABLE and DEFERRABLE INITIALLY DEFERR= ED are the same "Frank Heikens" <f.heikens@anva.nl> writes: > Description: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the > same This test proves no such thing. You are supposing that a deferrable unique index has the exact same behavior as a nondeferrable one. Actually, a DEFERRABLE IMMEDIATE index enforces its checks as of the end of the statement, which is per spec. regards, tom lane ***************************DISCLAIMER*********************************** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking a= an en gebruik door anderen is niet toegestaan. ANVA bv sluit iedere aanspra= kelijkheid uit die voortvloeit uit electronische verzending. This e-mail is intended exclusively for the addressee(s), and may not be pa= ssed on to, or made available for use by any person other than the addresse= e(s). ANVA bv rules out any and every liability resulting from any electron= ic transmission. ***************************************************************************= ***
The manual looks fine, I found the information as well. I started using the= wiki, that's why I got confused. Thanks! Frank -----Oorspronkelijk bericht----- Van: Tom Lane [mailto:tgl@sss.pgh.pa.us] Verzonden: vrijdag 6 augustus 2010 17:04 Aan: Frank Heikens CC: pgsql-bugs@postgresql.org Onderwerp: Re: [BUGS] BUG #5606: DEFERRABLE and DEFERRABLE INITIALLY DEFERR= ED are the same Frank Heikens <f.heikens@anva.nl> writes: > The wikipage says >> a deferrable constraint CAN be checked at the end of a >> transaction. You still have to ask PostgreSQL to defer it. > http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE= _UNIQUE_CONSTRAINTS > I don't have to ask PostgreSQL to defer, it works in the second test as well in the third test without any changes. I guess the example in the wiki isn't correct, right? No, not even a little bit :-(. I hadn't seen that text; it needs to be corrected. Will hack on it in a moment. > A single UPDATE touching all records will check the uniqueness after the = complete update, not right after each update of a row as it does on non-def= errable constraints. Right. non-deferrable -> uniqueness is enforced after each row change DEFERRABLE IMMEDIATE -> uniqueness is enforced at end of statement DEFERRABLE DEFERRED -> uniqueness is enforced at end of transaction > Maybe someone could make this clear in the manual and wiki? AFAIK the manual is OK; do you see a place where it gets this wrong? regards, tom lane ***************************DISCLAIMER*********************************** Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking a= an en gebruik door anderen is niet toegestaan. ANVA bv sluit iedere aanspra= kelijkheid uit die voortvloeit uit electronische verzending. This e-mail is intended exclusively for the addressee(s), and may not be pa= ssed on to, or made available for use by any person other than the addresse= e(s). ANVA bv rules out any and every liability resulting from any electron= ic transmission. ***************************************************************************= ***