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

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

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 deferreduniqueness works
Date: 2009-08-11 11:14:46
Message-ID: 20090811111446.GA25965@depesz.com (view raw or flat)
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

Responses

pgsql-bugs by date

Next:From: Dean RasheedDate: 2009-08-11 12:21:25
Subject: Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works
Previous:From: Sergei AbramovDate: 2009-08-11 06:40:53
Subject: Malfunction in dropping database with pgAdmin

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