From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | misleading error message in 8.5, and bad (?) way deferred uniqueness works |
Date: | 2009-08-11 11:14:46 |
Message-ID: | 20090811111446.GA25965@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Rasheed | 2009-08-11 12:21:25 | Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works |
Previous Message | Sergei Abramov | 2009-08-11 06:40:53 | Malfunction in dropping database with pgAdmin |