Re: Check constraints on non-immutable keys

On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> My scintillating contribution to this discussion is the observation
>> that unrestorable dumps suck.
>
> No doubt, but is this a real problem in practice?

Magnus tells me that that was what prompted his original email.

> I can't recall many
> field complaints about it.  And the ones I do recall wouldn't have been
> prevented by a check as stupid as "are there immutable functions in
> here".

Hopefully there aren't too many ways to get data into a table that
doesn't satisfy its check constraint - what else are you thinking of?
Short of direct system catalog manipulation with malice aforethought,
redefining a function to return different results after the fact is
the only other case I can think of, and I'd propose we block that
somehow too if I could figure out how.

> I still say that what such a check is likely to do is encourage
> people to mis-label mutable functions as immutable ... which will cause
> them a lot of *other* headaches.

If it does, those headaches are their fault, whereas this one, at
least as I see it, is our fault. The fact that you can injure
yourself badly with a sharp knife is not an excuse for someone to hand
it to you pointy-end-first.

I think it would be useful to have check constraints that are only
enforced on new data, and allowing immutable functions there would
make sense. But I can't think of any reasonable use case for having a
non-immutable check constraint of the type we have now. Can you?
Besides breaking pg_dump, it can also potentially foul up constraint
exclusion.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company