Exclusion constraints with time expressions

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.

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

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

"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:

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

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