Re: creating CHECK constraints as NOT VALID

Excerpts from Tom Lane's message of mié jun 15 14:49:04 -0400 2011:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011:
> >> On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera
> >> <alvherre(at)commandprompt(dot)com> wrote:
> >>> Hmm, I think this means we need to send a sinval message to invalidate
> >>> cached plans when a constraint is validated. I'll see about this.
>
> >> I feel like that really ought to be happening automatically, as a
> >> result of committing the transaction that did the system catalog
> >> modification. It seems pretty strange if it isn't.
>
> > The catalog change takes place in pg_constraint, so I'm not sure that
> > it'd cause the sort of event we need. I'm testing whether adding a call
> > to CacheInvalidateRelcache in the appropriate place works.
>
> Currently, only updates in pg_class, pg_attribute, and pg_index cause
> automatic relcache invalidations --- see the logic in
> PrepareForTupleInvalidation. If you want to force replanning after an
> update elsewhere, you need to call CacheInvalidateRelcache. I've
> occasionally thought about extending the number of cases that get
> handled automatically by PrepareForTupleInvalidation, but not gotten off
> my duff to change it. I doubt that we want to make that routine know
> about *every* possible case, so it's a matter of tradeoffs ...

I think pg_trigger is closer to needing a new case in
PrepareForTupleInvalidation than pg_constraint, at this point --
triggers seem to be involved rather more with CacheInvalidateRelcache
(and close relatives) calls than constraints.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support