On Fri, Jul 12, 2019 at 07:59:13PM -0400, Tom Lane wrote:
Tomas Vondra <tomas.von...@2ndquadrant.com> writes:
On Fri, Jul 12, 2019 at 02:00:25PM +0200, Pavel Stehule wrote:
Mutable function are allowed in check constraint expressions but
it is not right. The attached is a proposed fix for it including
regression test.
Yes, and the question is whether this is the right thing to do (I think
it probably is).
I'm pretty sure this change has been proposed before, and rejected before.
Has anybody excavated in the archives for prior discussions?
Yes, I've done some quick searches like "volatile constraint" and so on.
There are a couple of relevant discussions:
2004:
https://www.postgresql.org/message-id/flat/0C3A1AEC-6BE4-11D8-9224-000A95C88220%40myrealbox.com
2010:
https://www.postgresql.org/message-id/flat/12849.1277918175%40sss.pgh.pa.us#736c8ef9d7810c0bb85f495490fd40f5
But I don't think the conclusions are particularly clear.
In the first thread you seem to agree with requiring immutable functions
for check constraints (and triggers for one-time checks). The second
thread ended up discussing some new related stuff in SQL standard.
There may be other threads and I just haven't found them, of course.
There are, and always will be, lots of ways to shoot yourself in the foot.
In the case at hand, I fear we might just encourage people to mark
functions as immutable when they really aren't --- which will make their
problems *worse* not better, because now other uses besides check
constraints will also be at risk of misoptimization.
OTOH, even if we prohibit mutable functions in check constraints, people
can still create triggers doing those checks (and shoot themselves in
the foot that way).
There are, and always will be, lots of ways to shoot yourself in the foot.
In the case at hand, I fear we might just encourage people to mark
functions as immutable when they really aren't --- which will make their
problems *worse* not better, because now other uses besides check
constraints will also be at risk of misoptimization.
True.
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
