Обсуждение: Exclusion constraints with time expressions

Hi

I am using 9.1.6, and I've set up a partitioned table as described in the manual, with partitions based on a timestamptz column called 'time'.  The exclusion constraints work nicely when I select ranges of times with literal constants.  But why would a WHERE clause like the following not benefit from exclusion constraints?

Thomas Munro wrote:
> I am using 9.1.6, and I've set up a partitioned table as described in
the manual, with partitions
> based on a timestamptz column called 'time'.  The exclusion
constraints work nicely when I select
> ranges of times with literal constants.  But why would a WHERE clause
like the following not benefit
> from exclusion constraints?
>
>   time > TIMESTAMPTZ '2012-11-03 00:00:00Z' + INTERVAL '24 hours'
>
>
> Isn't that expression on the right reducible to a constant up front?
Obviously I can use a host
> language to do the arithmetic and provide a constant, but I am curious
to understand why that wouldn't
> be constant-folded.

I think the problem is that this + operator is implemented
by the function "timestamptz_pl_interval", which is STABLE
but not IMMUTABLE.

I am not sure why this function cannot be IMMUTABLE, it
seems to me that it should be.

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:

> I think the problem is that this + operator is implemented
> by the function "timestamptz_pl_interval", which is STABLE
> but not IMMUTABLE.
>
> I am not sure why this function cannot be IMMUTABLE, it
> seems to me that it should be.

No: the result of e.g.

  SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';

depends on the client's timezone and its DST rules.

hari.fuchs@gmail.com wrote:
> > I think the problem is that this + operator is implemented
> > by the function "timestamptz_pl_interval", which is STABLE
> > but not IMMUTABLE.
> >
> > I am not sure why this function cannot be IMMUTABLE, it
> > seems to me that it should be.
>
> No: the result of e.g.
>
>   SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';
>
> depends on the client's timezone and its DST rules.

You are right; timestamptz_in itself is STABLE, and I forgot
about daylight savings time.

Yours,
Laurenz Albe

hari.fuchs@gmail.com

> No: the result of e.g.
>
>  SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';
>
> depends on the client's timezone and its DST rules.

Can you give an example of where adding an interval based on *hours*
to TIMESTAMP WITH TIME ZONE would give a different value based on
client's time zone and DST rules? If there is such a case, we have a
bug, IMO.

Now, if you wanted to argue that *this* query might depend on time
zone information, I'd be more willing to believe it, and maybe the
problem is that we use the same function for both:

SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '1 day';

-Kevin

Albe Laurenz wrote:
> Thomas Munro wrote:
>> I am using 9.1.6, and I've set up a partitioned table as described
>> in the manual, with partitions based on a timestamptz column
>> called 'time'. The exclusion constraints work nicely when I select
>> ranges of times with literal constants. But why would a WHERE
>> clause like the following not benefit from exclusion constraints?
>>
>> time > TIMESTAMPTZ '2012-11-03 00:00:00Z' + INTERVAL '24 hours'
>>
>>
>> Isn't that expression on the right reducible to a constant up
>> front? Obviously I can use a host language to do the arithmetic
>> and provide a constant, but I am curious to understand why that
>> wouldn't be constant-folded.
>
> I think the problem is that this + operator is implemented
> by the function "timestamptz_pl_interval", which is STABLE
> but not IMMUTABLE.
>
> I am not sure why this function cannot be IMMUTABLE, it
> seems to me that it should be.

For TIMESTAMP WITHOUT TIME ZONE it couldn't be IMMUTABLE, because the
result would be based on the time zone setting of the client
connection; but adding a fixed interval to a UTC time to get a UTC
time seems pretty immutable to me. That said, I'm not sure why STABLE
wouldn't be good enough for such an optimization, if it were
supported at all. I don't think we evaluate such expressions before
developing the plan, though.

If you run EXPLAIN ANALYZE on one of the queries involved, does it
actually perform the scan of partitions which can be skipped at
run-time, or does it show "never executed"?

-Kevin

"Kevin Grittner" <kgrittn@mail.com> writes:
> Now, if you wanted to argue that *this* query might depend on time
> zone information, I'd be more willing to believe it, and maybe the
> problem is that we use the same function for both:

> SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '1 day';

The "problem" is not with the function, but with the fact that both
kinds of interval are the same data type.  That's not something we have
the flexibility to change AFAICS.

It should be possible to use the protransform feature to allow
argument-value-dependent const folding, if anyone is annoyed enough
about this specific case to write some code for it.

            regards, tom lane

On 2012-11-06, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> hari.fuchs@gmail.com wrote:
>> > I think the problem is that this + operator is implemented
>> > by the function "timestamptz_pl_interval", which is STABLE
>> > but not IMMUTABLE.
>> >
>> > I am not sure why this function cannot be IMMUTABLE, it
>> > seems to me that it should be.
>>
>> No: the result of e.g.
>>
>>   SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';
>>
>> depends on the client's timezone and its DST rules.
>
> You are right; timestamptz_in itself is STABLE, and I forgot
> about daylight savings time.

The original poster had a timestamptz constant (using the naval
timezone 'Z') yet he still had the problem.,

--
⚂⚃ 100% natural

On 2012-11-06, Kevin Grittner <kgrittn@mail.com> wrote:
>
> For TIMESTAMP WITHOUT TIME ZONE it couldn't be IMMUTABLE, because the
> result would be based on the time zone setting of the client
> connection; but adding a fixed interval to a UTC time to get a UTC
> time seems pretty immutable to me. That said, I'm not sure why STABLE
> wouldn't be good enough for such an optimization, if it were
> supported at all. I don't think we evaluate such expressions before
> developing the plan, though.

> If you run EXPLAIN ANALYZE on one of the queries involved, does it
> actually perform the scan of partitions which can be skipped at
> run-time, or does it show "never executed"?

In 8.4 I've seen scans of excluded partitions, fortunately they are index scans
which hit an index and find no rows on the first index page, so not too painful,
but there's still the locking overhead. I've learned to precompute the timestamptz
constants.





--
⚂⚃ 100% natural

On 2012-11-06, Kevin Grittner <kgrittn@mail.com> wrote:
> hari.fuchs@gmail.com
>
>> No: the result of e.g.
>>
>>  SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';
>>
>> depends on the client's timezone and its DST rules.
>
> Can you give an example of where adding an interval based on *hours*
> to TIMESTAMP WITH TIME ZONE would give a different value based on
> client's time zone and DST rules? If there is such a case, we have a
> bug, IMO.

the problem is with the value of  TIMESTAMPTZ '2012-10-28 01:30:00'
more than how the addition is handled.

> Now, if you wanted to argue that *this* query might depend on time
> zone information, I'd be more willing to believe it, and maybe the
> problem is that we use the same function for both:
>
> SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '1 day';

even done right (with a timezone in the left argument) that one's
going to be locale dependant.


--
⚂⚃ 100% natural