Thread: misleading error message in 8.5, and bad (?) way deferred uniqueness works

While testing deferred unique constraints I found this:

# CREATE TABLE test (
    i INT4 PRIMARY KEY
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE

# set constraints test_pkey deferred;
ERROR:  constraint "test_pkey" does not exist

The constraint definitely exists:

# select * from pg_constraint  where conname = 'test_pkey';
-[ RECORD 1 ]-+----------
conname       | test_pkey
connamespace  | 2200
contype       | p
condeferrable | f
condeferred   | f
conrelid      | 17533
contypid      | 0
conindid      | 17536
confrelid     | 0
confupdtype   |
confdeltype   |
confmatchtype |
conislocal    | t
coninhcount   | 0
conkey        | {1}
confkey       | [null]
conpfeqop     | [null]
conppeqop     | [null]
conffeqop     | [null]
conbin        | [null]
consrc        | [null]


This (set ... deferred) works perfectly if i define the table like this:

# CREATE TABLE test (
    i INT4 PRIMARY KEY DEFERRABLE INITIALLY IMMEDIATE
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE

# set constraints test_pkey deferred;
SET CONSTRAINTS

Also.

As I understand (I might be wrong, so please clarify if I am), when I
create table with primary key that is "deferrable initially immediate",
it will act as immediate unless i will set it to deferred with "set
constraints".

If that's true, then why it works:
# INSERT INTO test (i) values (1), (2), (3);
INSERT 0 3
# update test set i = i + 1;
UPDATE 3

shouldn't it raise exception? and work *only* if i set the constraint to
deferred?

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

2009/8/11 hubert depesz lubaczewski <depesz@depesz.com>:
> While testing deferred unique constraints I found this:
>
> # CREATE TABLE test (
> =A0 =A0i INT4 PRIMARY KEY
> );
> NOTICE: =A0CREATE TABLE / PRIMARY KEY will create implicit index "test_pk=
ey" for table "test"
> CREATE TABLE
>
> # set constraints test_pkey deferred;
> ERROR: =A0constraint "test_pkey" does not exist
>

The constraint needs to be declared DEFERRABLE before you can defer
it, but yes, I agree this is not a helpful error message.

[The reason is that it actually searches for the trigger enforcing the
constraint, and there isn't one if it's not deferrable. So the current
code can't distinguish between a non-existent unique constraint and a
non-deferrable one.]


> As I understand (I might be wrong, so please clarify if I am), when I
> create table with primary key that is "deferrable initially immediate",
> it will act as immediate unless i will set it to deferred with "set
> constraints".
>
> If that's true, then why it works:
> # INSERT INTO test (i) values (1), (2), (3);
> INSERT 0 3
> # update test set i =3D i + 1;
> UPDATE 3
>
> shouldn't it raise exception? and work *only* if i set the constraint to
> deferred?
>

"Immediate" actually means at the end of the statement rather than
after each row for deferrable constraints. See

http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html

 - Dean

Dean Rasheed <dean.a.rasheed@googlemail.com> writes:
> The constraint needs to be declared DEFERRABLE before you can defer
> it, but yes, I agree this is not a helpful error message.

> [The reason is that it actually searches for the trigger enforcing the
> constraint, and there isn't one if it's not deferrable. So the current
> code can't distinguish between a non-existent unique constraint and a
> non-deferrable one.]

Yeah.  Is it worth searching pg_constraint first, just so that we can
give a better error message?

Actually, it strikes me that if we did it that way, we could search
pg_trigger using the constraint OID instead of name, which would permit
replacing the index on tgconstrname with a presumably much smaller one
on tgconstraint.  And the bogus rechecks on namespace in
AfterTriggerSetState could probably be simplified too ...

            regards, tom lane

I wrote:
> Dean Rasheed <dean.a.rasheed@googlemail.com> writes:
>> [The reason is that it actually searches for the trigger enforcing the
>> constraint, and there isn't one if it's not deferrable. So the current
>> code can't distinguish between a non-existent unique constraint and a
>> non-deferrable one.]

> Yeah.  Is it worth searching pg_constraint first, just so that we can
> give a better error message?

Actually, a bit more digging reminded me of why the code does it that
way:

    Note: When tgconstraint is nonzero, tgisconstraint must be true,
    and tgconstrname, tgconstrrelid, tgconstrindid, tgdeferrable,
    tginitdeferred are redundant with the referenced pg_constraint
    entry. The reason we keep these fields is that we support
    "stand-alone" constraint triggers with no corresponding
    pg_constraint entry.

I'm sure somebody would complain if we removed the user-level constraint
trigger facility :-(.  It might be worth the trouble to change things so
that there actually is a pg_constraint entry associated with a user
constraint trigger; and then we could do the search as suggested above.
In principle we could also remove the redundant columns from pg_trigger,
but that would mean an extra catalog search each time we set up a
trigger, so I dunno if that would be a good step or not.

Anyway it's looking like a slightly nontrivial project.  Maybe we should
just rephrase the error message Hubert is complaining about.

            regards, tom lane

On Tuesday 11 August 2009 19:00:30 Tom Lane wrote:
> I wrote:
> > Dean Rasheed <dean.a.rasheed@googlemail.com> writes:
> >> [The reason is that it actually searches for the trigger enforcing the
> >> constraint, and there isn't one if it's not deferrable. So the current
> >> code can't distinguish between a non-existent unique constraint and a
> >> non-deferrable one.]
> >
> > Yeah.  Is it worth searching pg_constraint first, just so that we can
> > give a better error message?
>
> Actually, a bit more digging reminded me of why the code does it that
> way:
>
>     Note: When tgconstraint is nonzero, tgisconstraint must be true,
>     and tgconstrname, tgconstrrelid, tgconstrindid, tgdeferrable,
>     tginitdeferred are redundant with the referenced pg_constraint
>     entry. The reason we keep these fields is that we support
>     "stand-alone" constraint triggers with no corresponding
>     pg_constraint entry.
> I'm sure somebody would complain if we removed the user-level constraint
> trigger facility :-(.
I know of several people using them - out of the simple reason its the only
possibility to get deferred triggers atm... (Which in those cases are used to
update materialized views)

Actually I plan to check (and possibly discuss here) how complex statement
level deferred triggers would be somewhat soon...

Andres

2009/8/11 Tom Lane <tgl@sss.pgh.pa.us>:
> Anyway it's looking like a slightly nontrivial project. =A0Maybe we should
> just rephrase the error message Hubert is complaining about.
>

Yeah, I can't think of any simple way of distinguishing the 2 error
conditions in that code. Perhaps adding a suitable hint would help, as
well as re-wording the error message:

ERROR: deferrable constraint "foo" does not exist
HINT: You must specify the name of a constraint declared with the
DEFERRABLE option.

 - Dean