Re: Boolean partitions syntax

> On Apr 10, 2018, at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <david.rowley@2ndquadrant.com> writes:
>> On 11 April 2018 at 03:34, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Well, that just begs the question: why do these expressions need to
>>> be immutable?  What we really want, I think, is to evaluate them
>>> and reduce them to constants.  After that, it hardly matters whether
>>> the original expression was volatile.  I see absolutely no moral
>>> difference between "for values in (random())" and cases like
>>> this, which works today:
>
>> I'd personally be pretty surprised if this worked.
>
> Well, my point is that we're *already* behaving that way in some cases,
> simply because of the existence of macro-like inputs for some of these
> datatypes.  I'm not sure that users are going to perceive a big difference
> between 'now'::timestamptz and now(), for example.  If we take one but
> not the other, I don't think anybody will see that as a feature.

+1.  Also, one hopes that a user tests their code prior to rolling it out
into a production environment, so a case like the “random()” example
has already been vetted as either not something for their partition, or
they want a one-time randomly generated number to determine how
things are partitioned.

>> What other DDL will execute a volatile function?
>
> This might be a valid concern, not sure.  It's certainly true that
> most other DDL doesn't result in acquiring a transaction snapshot;
> but it's not *universally* true.  Certainly there's DDL that can
> execute nigh-arbitrary user code, such as CREATE INDEX.
>
>> What if the volatile function has side
>> effects?
>
> Can't say that that bothers me.  If the user has thought about what
> they're doing, the results won't surprise them; if they haven't,
> they're likely to be surprised in any case.

+1.  I’m all for protecting users from themselves, but there’s only so
much you can do.  This is where we can make up any knowledge
gap with documentation.

> We might be well advised to do a CCI after evaluating the expressions,
> but that could still be before anything interesting happens.
>
>> What if the user didn't want the function evaluated and
>> somehow thought they wanted the evaluation to take place on INSERT?
>
> You could object to awfully large chunks of SQL on the grounds that
> it might confuse somebody.

That's truer than you may think.

At the end of the day, a user wants to be able to create a partition
with the syntax that they expect from working with other parts of the
database.  If we have clear documentation, e.g. “If you use a volatile
function for a partition, it will only be executed once” etc. should be enough
to educate, or at least say we provided notice about the behavior.

Jonathan