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

Re: Deferrable Unique Constraints

From: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Deferrable Unique Constraints
Date: 2005-01-27 04:47:43
Message-ID: 20050127044743.GC12171@dcc.uchile.cl (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Jan 27, 2005 at 03:31:29PM +1100, Neil Conway wrote:

> You could perhaps relax the uniqueness of the index during the
> transaction itself, and keep around some backend-local indication of
> which index entries it have been inserted. Then at transaction-commit
> you'd need to re-check the inserted index entries to verify that they
> are unique. It would be nice to just keep a pin on the leaf page that we
> inserted into, although we'd need to take care to follow subsequent page
> splits (could we use the existing L & Y techniques to do this?).

Maybe we can do something like

1. use a boolean-returning unique insertion.  If it fails, returns
false, doesn't ereport(ERROR); if it works, inserts and returns true.

2. the caller checks the return value.  If false, records the insertion
attempt into an should-check-later list.

3. at transaction end, unique insertion is tried again with the items on
the list.  If it fails, the transaction is aborted.

It's only a SMOC, nothing difficult AFAICS.  Disk-spilling logic
included, because it'd be probably needed.

-- 
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.

In response to

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2005-01-27 05:48:53
Subject: Re: Deferrable Unique Constraints
Previous:From: Tom LaneDate: 2005-01-27 04:42:44
Subject: Re: Deferrable Unique Constraints

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