Re: WIP: Deferrable unique constraints

On Tue, 2009-07-28 at 13:41 -0400, Tom Lane wrote:
> * It's sheer luck that it gets through bootstrap without crashing.
> Those aren't part of the core set of catalogs that we expect to be
> accessed by primitive catalog searches. I wouldn't be too surprised
> if it gets the wrong answer, and the only reason there's not a visible
> bug is none of the core catalogs have deferrable indexes (so there's
> no pg_depend entry to be found).
>
> * It increases the runtime of BuildIndexInfo by what seems likely to
> be orders of magnitude (note that get_index_constraint is not a
> syscacheable operation). Did anyone do any performance checks on
> this patch, like seeing if pgbench got slower?

Not I. I didn't notice anything that made me think it would slow down in
the default case, but I'll be more careful in the future.

This also impacts my patch. After moving the constraint information from
pg_index (where my patch currently has it) to pg_constraint, I will also
need access to the index and the constraint at the same time. Perhaps
this can all be handled more like CHECK constraints, storing this
information in the ResultRelInfo?

> I think we had better add the deferrability state to pg_index
> to avoid this.

This might make it difficult to allow multiple constraints to use the
same index. We might have to make some kind of rules about when two
constraints can share an index, and when they can't.

> I also notice that it doesn't particularly care about the deferrability
> state in the sense that the triggers use (ie, whether to check at end of
> statement or end of transaction). What we want to know here is whether
> to force an old-school immediate uniqueness check in the index AM.

[...]

> And I'm further thinking about intentionally calling it something other
> than "deferred", to emphasize that the semantics aren't quite like
> regular constraint deferral. Maybe invert the sense and call it
> "immediate". Comments?

I'm trying to figure out how this fits with the generalized index
constraints idea. We may want the generalized index constraints to have
the same "immediate" behavior, but that doesn't have much to do with the
index.

Regards,
Jeff Davis