Thread: Deferrable UNIQUE INDEX?

Folks,

I just stumbled across a little problem that looks like this:

CREATE TABLE foo (   id SERIAL PRIMARY KEY,   f_text TEXT NOT NULL
);

CREATE UNIQUE INDEX uniq_foo_f_text ON foo(LOWER(TRIM(f_text)));

COPY foo(f_text) FROM stdin;
a
b
c
\.

Oh, shucks.  I'd like to permute these.  Lemme start a transaction:

test@[local]=> BEGIN;
BEGIN
test@[local]=> UPDATE foo set f_text = 'a' where id=3;
ERROR:  duplicate key violates unique constraint "uniq_foo_f_text"

Dang!  Everything was going to be kosher at the end of the
transaction, but I never got a chance.

Is there some way to make the index check INITIALLY DEFERRABLE the way
a regular column/table constraint could be?

I'd much appreciate any insights into this :)

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

David Fetter said:

> Is there some way to make the index check INITIALLY DEFERRABLE the way
> a regular column/table constraint could be?
>
> I'd much appreciate any insights into this :)
>

The docs say: "Only foreign key constraints currently accept this clause.
All other constraint types are not deferrable."

The TODO list has: "Allow DEFERRABLE UNIQUE constraints?"

send in a patch ;-)

cheers

andrew

On Jan 3, 2006, at 19:45 , David Fetter wrote:

> Dang!  Everything was going to be kosher at the end of the
> transaction, but I never got a chance.
>
> Is there some way to make the index check INITIALLY DEFERRABLE the way
> a regular column/table constraint could be?

Happy New Year, David!

I've run into this when reorganizing nested-set hierarchies (though  
then it's with integers rather than text). There isn't a way to do  
this directly, as you've found out, but you can use a similar  
workaround. You could prepend a string to the key during the  
permutation, and strip the prefix after permutation is finished.

Not the answer you're looking for, but perhaps you'll be able to use  
this workaround.

Michael Glaesemann
grzm myrealbox com

"Andrew Dunstan" <andrew@dunslane.net> writes:
> David Fetter said:
>> Is there some way to make the index check INITIALLY DEFERRABLE the way
>> a regular column/table constraint could be?

> The TODO list has: "Allow DEFERRABLE UNIQUE constraints?"
> send in a patch ;-)

This is definitely on the wish-list, because it is both useful and
required by the SQL spec ... but it seems nontrivial.  I recall
some discussions in the pghackers archives about how to do it.
        regards, tom lane

On Tue, Jan 03, 2006 at 11:17:16PM -0500, Tom Lane wrote:
> "Andrew Dunstan" <andrew@dunslane.net> writes:
> > David Fetter said:
> >> Is there some way to make the index check INITIALLY DEFERRABLE
> >> the way a regular column/table constraint could be?
> 
> > The TODO list has: "Allow DEFERRABLE UNIQUE constraints?" send in
> > a patch ;-)
> 
> This is definitely on the wish-list, because it is both useful and
> required by the SQL spec ... but it seems nontrivial.  I recall some
> discussions in the pghackers archives about how to do it.

There was some discussion in the followups to this posting:

http://archives.postgresql.org/pgsql-hackers/2005-01/msg00882.php

Alvaro mentioned a strategy here

http://archives.postgresql.org/pgsql-hackers/2005-01/msg00904.php

and the "tough part" appears to have come down to "disk-spilling
logic."

I'm out of my depth here, but maybe we could figure out just what
components would need to be touched and how...

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!