Re: Deferrable Unique Constraints

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > Off the top of my head it seems the way to go about doing this would be to
> > simply not insert the records in the index until commit time. This doesn't
> > actually sound so hard, is there any problem with this approach?
> 
> Yeah:
>     begin;
>     insert into foo (key, ...) values (33, ...);
>     select * from foo where key = 33;
>     ...
> 
> If the SELECT uses an indexscan it will fail to find the just-inserted
> row.

Well presumably you would need a non-unique index created for query execution
purposes. The unique index would be purely for enforcing the constraint.

-- 
greg