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

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема misleading error message in 8.5, and bad (?) way deferred uniqueness works
Дата
Msg-id 20090811111446.GA25965@depesz.com
обсуждение исходный текст
Ответы Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works  (Dean Rasheed <dean.a.rasheed@googlemail.com>)
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #4973: number precision or scale lost when altering table column
Следующее
От: Dean Rasheed
Дата:
Сообщение: Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works