Hm, table constraints aren't so unique as all that
От | Tom Lane |
---|---|
Тема | Hm, table constraints aren't so unique as all that |
Дата | |
Msg-id | 113.1359419105@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: Hm, table constraints aren't so unique as all that
(Peter Geoghegan <peter.geoghegan86@gmail.com>)
Re: Hm, table constraints aren't so unique as all that (Tom Lane <tgl@sss.pgh.pa.us>) Re: Hm, table constraints aren't so unique as all that (Jim Nasby <jim@nasby.net>) |
Список | 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
В списке pgsql-hackers по дате отправления:
Следующее
От: Peter GeogheganДата:
Сообщение: Re: Hm, table constraints aren't so unique as all that