Skip site navigation (1) Skip section navigation (2)

Hm, table constraints aren't so unique as all that

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Hm, table constraints aren't so unique as all that
Date: 2013-01-29 00:25:05
Message-ID: 113.1359419105@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Over in the thread about enhanced error fields, I claimed that
"constraints are uniquely named among those associated with a table,
or with a domain".  But it turns out that that ain't necessarily so,
because the code path for index constraints doesn't pay any attention
to pre-existing check constraints:

d1=# create table t1 (f1 int);
CREATE TABLE
d1=# alter table t1 add constraint c1 check (f1 > 0);
ALTER TABLE
d1=# alter table t1 add constraint c1 unique (f1);
ALTER TABLE
d1=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
Indexes:
    "c1" UNIQUE CONSTRAINT, btree (f1)
Check constraints:
    "c1" CHECK (f1 > 0)

If you do this in the other order it does get rejected:

d1=# create table t2 (f1 int);
CREATE TABLE
d1=# alter table t2 add constraint c2 unique (f1);
ALTER TABLE
d1=# alter table t2 add constraint c2 check (f1 > 0);
ERROR:  constraint "c2" for relation "t2" already exists

Aside from being plain inconsistent, this seems to me to create a
dump/reload hazard: pg_dump has no idea that it would have to dump
these two constraints in a particular order to make them reloadable.

In practice there's not such a big risk because pg_dump prefers to stick
CHECK constraints directly into the CREATE TABLE rather than add them
after-the-fact.  But if it had to split off the CHECK constraint to
avoid a circularity problem, I don't believe there's anything preventing
a reload failure.

I think we need to tighten this down by having index-constraint creation
check for conflicts with other constraint types.  It also seems like it
might be a good idea to put in a unique index to enforce the intended
lack of conflicts --- note that the existing index on (conname,
connamespace) isn't unique.  It's a bit problematic that pg_constraint
contains both table-related constraints and domain-related constraints,
but it strikes me that we could get close enough by changing
pg_constraint_conname_nsp_index to be a unique index on
(conname, connamespace, conrelid, contypid).  That would fix the problem
as long as no pg_constraint entry ever has both conrelid and contypid
nonzero; the unique index couldn't catch such an error.  But it doesn't
seem to me that such a coding error would escape detection anyway.

Of course this wouldn't be material for back-patching, but it seems to
me there's still time to fix this for 9.3, and we should do so if we
want to claim that the enhanced-errors patch uniquely identifies
constraints.

Thoughts?

			regards, tom lane


Responses

pgsql-hackers by date

Next:From: Peter GeogheganDate: 2013-01-29 00:33:48
Subject: Re: Hm, table constraints aren't so unique as all that
Previous:From: Simon RiggsDate: 2013-01-29 00:07:13
Subject: pgsql: Fast promote mode skips checkpoint at end of recovery.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group