Re: [PATCHES] plpgsql: check domain constraints

Neil Conway <neilc(at)samurai(dot)com> writes:
> GetDomainConstraints() looks fairly expensive, so it would be nice to do
> some caching. What would the best way to implement this be? I had
> thought that perhaps the typcache would work, but there seems to be no
> method to flush stale typcache data. Perhaps we could add support for
> typcache invalidation (via a new sinval message), and then add the
> domain constraint information to the typcache. Or is there an easier
> way?

Yeah, I had been thinking of exactly the same thing a few months ago
after noting that GetDomainConstraints() can be pretty dang slow ---
it seemed to be a major bottleneck for Kevin Grittner here:
http://archives.postgresql.org/pgsql-hackers/2005-09/msg01135.php
Unfortunately the rest of that conversation was unintentionally
off-list, but we identified heavy use of pg_constraint_contypid_index
as the source of his issue, and I said

: Hmm. The only commonly-used code path I can see that would touch
: pg_constraint_contypid_index is GetDomainConstraints(), which would be
: called (once) during startup of a command that involves a CoerceToDomain
: expression node. So if the heavily-hit table has any domain-type
: columns, it's possible that a steady stream of inserts or updates
: could have kept that index tied up.
:
: It might be worth introducing a system cache that could be used to
: extract the constraints for a domain without going to the catalog for
: every single command. There's been very little work done to date on
: optimizing operations on domains :-(

The lack of typcache invalidation is something that will eventually
bite us in other ways, so we need to add the facility anyway. We
don't really need a "new sinval message", as an inval on the pg_type
row will serve perfectly well --- what we need is something comparable
to CacheInvalidateRelcache() to cause such a message to be sent when
we haven't actually changed the pg_type row itself.

Do you want to work on this? I can if you don't.

BTW, in connection with the lookup_rowtype_tupdesc fiasco, it's pretty
obvious that any data structure returned by this function will need
to be either copied or reference-counted.

regards, tom lane