Re: plpgsql versus domains

Hi,

On 2015-02-26 12:31:46 -0500, Tom Lane wrote:
> It's really slow, because any assignment to a domain variable goes through
> the slow double-I/O-conversion path in exec_cast_value, even if no actual
> work is needed.

Hm, that's surely not nice for some types. Doing syscache lookups every
time can't help either.

> And I noticed that the domain's constraints get looked up
> only once per function per session; for example this script gets
> unexpected results:
> The reason for this is that domain_in looks up the domain's constraints
> and caches them under the calling FmgrInfo struct.

That's probably far from the only such case we have :(

> 1. Arrange to cache the constraints for domain types in typcache.c,
> so as to reduce the number of trips to the actual catalogs. I think
> typcache.c will have to flush these cache items upon seeing any sinval
> change in pg_constraint, but that's still cheaper than searching
> pg_constraint for every query.

That sounds sane.

> 2. In plpgsql, explicitly model a domain type as being its base type
> plus some constraints --- that is, stop depending on domain_in() to
> enforce the constraints, and do it ourselves. This would mean that
> the FmgrInfo in a PLpgSQL_type struct would reference the base type's
> input function rather than domain_in, so we'd not have to be afraid
> to cache that. The constraints would be represented as a separate list,
> which we'd arrange to fetch from the typcache once per transaction.

Hm. A bit sad to open code that in plpgsql instead of making it
available more generally.

> 3. We could still have domains.c responsible for most of the details;
> the domain_check() API may be good enough as-is, though it seems to lack
> any simple way to force re-lookup of the domain constraints once per xact.
>
> Thoughts, better ideas?

You're probably going to kill me because of the increased complexity,
but how about making typecache.c smarter, more in the vein of
relcache.c, and store the relevant info in there? And then, to avoid
repeated lookups, store a reference to that in fcinfo? The lifetime of
objects wouldn't be entirely trivial, but it doesn't sound impossible.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services