Re: [GENERAL] Timezone locale consistency for functional indexes

[Adrian Klaver]

On 06/12/2017 06:11 AM, Olav Gjerde wrote:

Oops sent previous post before actual reply.


I figured out the issue, it was as simple as some developers used the
default in Java's Hibernate which created the timestamp columns
without time zone.

Anyway I guess this is the correct approach that also take summer time
into consideration? And using the immutable function wrapper is wrong?



By correct approach, you mean setting timezone to 'UTC'?

If by immutable function wrapper, you mean faking the immutability of a 
function, then yes I would see that as wrong.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Timezone locale consistency for functional indexes

[Adrian Klaver]

On 06/12/2017 06:11 AM, Olav Gjerde wrote:

I figured out the issue, it was as simple as some developers used the
default in Java's Hibernate which created the timestamp columns
without time zone.

Anyway I guess this is the correct approach that also take summer time
into consideration? And using the immutable function wrapper is wrong?

On Mon, Jun 12, 2017 at 2:25 PM, Olav Gjerde  wrote:

I have a table that I try create an functional index on like this:

CREATE INDEX my_index_name
ON opening_hours (
 opening_hours_type,
 EXTRACT(YEAR FROM date),
 EXTRACT(MONTH FROM date)
)

But I get the following error: functions in index expression must be
marked IMMUTABLE


But if I change it to:

CREATE INDEX my_index_name
ON opening_hours (
 opening_hours_type,
 EXTRACT(YEAR FROM date AT TIME ZONE 'UTC'),
 EXTRACT(MONTH FROM date AT TIME ZONE 'UTC')
)
The index will be created.

On other systems it could be the oppsite, that it only works without
the additional AT TIME ZONE

On all systems, the default Timezone in postgresql.conf is set to UTC
and show timezone; return UTC. Additionally show lc_time; returns
nb_NO.UTF-8

What kind of system settings could cause this behaviour?  We run
Ubuntu Linux 16.04 and Postgresql 9.6

Another question is, should I just create immutable functions wrappers
for this instead?


--
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Timezone locale consistency for functional indexes

[Olav Gjerde]

I figured out the issue, it was as simple as some developers used the
default in Java's Hibernate which created the timestamp columns
without time zone.

Anyway I guess this is the correct approach that also take summer time
into consideration? And using the immutable function wrapper is wrong?

On Mon, Jun 12, 2017 at 2:25 PM, Olav Gjerde  wrote:
> I have a table that I try create an functional index on like this:
>
> CREATE INDEX my_index_name
> ON opening_hours (
> opening_hours_type,
> EXTRACT(YEAR FROM date),
> EXTRACT(MONTH FROM date)
> )
>
> But I get the following error: functions in index expression must be
> marked IMMUTABLE
>
>
> But if I change it to:
>
> CREATE INDEX my_index_name
> ON opening_hours (
> opening_hours_type,
> EXTRACT(YEAR FROM date AT TIME ZONE 'UTC'),
> EXTRACT(MONTH FROM date AT TIME ZONE 'UTC')
> )
> The index will be created.
>
> On other systems it could be the oppsite, that it only works without
> the additional AT TIME ZONE
>
> On all systems, the default Timezone in postgresql.conf is set to UTC
> and show timezone; return UTC. Additionally show lc_time; returns
> nb_NO.UTF-8
>
> What kind of system settings could cause this behaviour?  We run
> Ubuntu Linux 16.04 and Postgresql 9.6
>
> Another question is, should I just create immutable functions wrappers
> for this instead?
>
>
> --
> Kind Regards / Med Vennlig Hilsen
>
> Olav Grønås Gjerde



-- 
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde

BackupBay Gjerde
Asalvegen 19
4051 SOLA
Norway
Phone: +47 918 000 59


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general