Re: Check-out mutable functions in check constraints
От | Tomas Vondra |
---|---|
Тема | Re: Check-out mutable functions in check constraints |
Дата | |
Msg-id | 20190713015805.yzxyh73h5j3upfaq@development обсуждение исходный текст |
Ответ на | Re: Check-out mutable functions in check constraints (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Check-out mutable functions in check constraints
|
Список | pgsql-hackers |
On Fri, Jul 12, 2019 at 07:59:13PM -0400, Tom Lane wrote: >Tomas Vondra <tomas.vondra@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
В списке pgsql-hackers по дате отправления: