Обсуждение: [BUGS] Deferrable constraint execution not respecting "initially immediate"?
[BUGS] Deferrable constraint execution not respecting "initially immediate"?
От
"David G. Johnston"
Дата:
In bug # 14739 Tom Lane wrote the following. My response follows but I decided to create a new thread since the topic for 14739 is about encoding and not the constraint itself.
fbd@datasapiens.com writes:
> I am testing PG on this query :
> CREATE TABLE T_UNIK (ID INT UNIQUE);
> INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5);
> UPDATE T_UNIK SET ID = ID + 1;
> I know that PG is unable to do this set based operation properly because it
> does it row by row (which is a nonsense since it works on many RDBMS)
The solution for that is documented: declare the unique constraint as
deferrable.
regression=# CREATE TABLE T_UNIK (ID INT UNIQUE deferrable );
CREATE TABLE
regression=#INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5);
INSERT 0 5
regression=# UPDATE T_UNIK SET ID = ID + 1;
UPDATE 5
I was expecting the above to require a "set constraints deferred" since "initially immediate" is the default.
create table t_unik (id int unique deferrable initially immediate);
It appears to be useful, but undocumented, that changing the primary mode to "deferrable" also changes the default timing to "initially deferred" - irrespective of whether the constraint itself is defined as initially immediate or initially deferred. i.e., changing just the create table to explicitly "deferrable initially immediate" doesn't provoke the duplicate key error like I was expecting it to.
Thinking on it further I believe the issue is that regardless of whether the timing is immediate or deferred a deferrable constraint never validates during the execution of an individual command while an immediate constraint does.
From "CREATE TABLE":
"A constraint that is not deferrable will be checked immediately after every command."
I think the above should be "after every row" instead of "after every command". My reading of this is that "command" and "statement" are the same thing and since the only way to get a unique violation is to be checking intra-command the above is wrong.
I read the sequence "deferrable initially immediate" as "deferrable initially "not deferred"" and expect the same behavior as a constraint not defined as "deferrable" unless some other action, at the transaction level, is taken. In this case the example doesn't "set constraints" and so the original failure should persist.
Ultimately my interpretation ends up working just fine because issuing set constraints in a transaction is just a more liberal directive.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jul 10, 2017 at 9:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The solution for that is documented: declare the unique constraint as
>> deferrable.
>>
>> regression=# CREATE TABLE T_UNIK (ID INT UNIQUE deferrable );
>> CREATE TABLE
>> regression=# INSERT INTO T_UNIK VALUES (1), (2), (3), (4), (5);
>> INSERT 0 5
>> regression=# UPDATE T_UNIK SET ID = ID + 1;
>> UPDATE 5
> I was expecting the above to require a "set constraints deferred" since
> "initially immediate" is the default.
Right, but "deferrable initially immediate" still means "at the end of the
statement", not "after each row". "deferrable initially deferred" means
"at the end of the transaction". In this example there's no real
difference between those two behaviors.
> From "CREATE TABLE":
> "A constraint that is not deferrable will be checked immediately after
> every command."
> I think the above should be "after every row" instead of "after every
> command".
I believe that FK constraints work differently from indexes in this
regard. Not sure that we want to get into that level of detail here.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Deferrable constraint execution not respecting "initially immediate"?
От
"David G. Johnston"
Дата:
> From "CREATE TABLE":
> "A constraint that is not deferrable will be checked immediately after
> every command."
> I think the above should be "after every row" instead of "after every
> command".
I believe that FK constraints work differently from indexes in this
regard. Not sure that we want to get into that level of detail here.
Since three of the 4 types are done "after every row" if we want to simplify (I'm leaning toward being precise here) I'd rather be imprecise about the FK. Pretending that a FK change is checked sooner than it really is seems like a minor omission since the observed behavior isn't likely to be noticeable. Wondering why "update pk = pk + 1" doesn't work by default when PK constraints are checked "after every command" has been shown to be noticeable.
David J.