Re: plpgsql plan caching allowing invalid data to enter table?

Joe Van Dyk <joe(at)tanga(dot)com> writes:
> It's looking like I can use a plpgsql function to insert data into a table
> that violates a domain constraint. Is this a known problem?

I think it's not really plpgsql's fault but domain_in's --- there's no
provision for flushing the latter's cached info about how to check
domain constraints. (You can't see this in simple commands because
the cache only lives as long as the statement, but I think plpgsql
is letting it get put into the function's definitional memory context,
which will pretty much survive for the whole session if you don't
redefine the function.)

We could ameliorate this case and probably improve performance as well
by keeping domain check info in the typcache rather than using ad-hoc
storage for it.

However, I think it's a mistake to imagine that there's ever going to be
a bulletproof guarantee that you can whack domain constraints around
in a live database and not have any risk of some data going unchecked.
As a couple of examples:

* suppose you do the ALTER DOMAIN, and commit it at an instant where
the plpgsql function is actively executing and has a live variable
value of that domain type. Nothing is going to make the constraint
change apply retroactively to that variable.

* suppose you don't do an ALTER DOMAIN at all, but just change the
behavior of a function that's used in a check constraint.

regards, tom lane