change in behavior declaring index on timestamp column

Question

I am hitting a problem with index creation across different versions of Postgres. We are upgrading a Postgres database from 10.21 to 14.4. In preparation for this, I've been running our tests on the newer version and in doing, so I hit this issue. The following redacted migration script runs fine on Postgres 12 or lower.

CREATE TABLE fs_hourly (
    id VARCHAR (255) NOT NULL,
    hour TIMESTAMP WITH TIME ZONE NOT NULL,
    -- more stuff here
    PRIMARY KEY (id, hour)
);
CREATE INDEX hourly_by_id ON fs_hourly (
    id text_ops,
    hour timestamp_ops
);

But on Postgres 13+ I get the following error:

QueryFailedError: operator class "timestamp_ops" does not accept data type timestamp with time zone

I've tried the following to determine which operator is actually in use, but this doesn't give me enough detail. The following is on PG 12.

uc=# \d fs_hourly
                     Table "public.fs_hourly"
         Column         |            Type             | Collation | Nullable | Default
------------------------+-----------------------------+-----------+----------+---------
 id                     | character varying(255)      |           | not null |
 hour                   | timestamp with time zone    |           | not null |
Indexes:
    "fs_hourly_pkey" PRIMARY KEY, btree (id, hour)
    "fs_hourly_by_id" btree (id, hour)

And I tried this to determine if timestamp_ops did support timestamp with time zone on PG 12 and earlier. No evidence of that here.

uc=# select am.amname AS index_method
                , opc.opcname AS opclass_name
                , opc.opcintype::regtype AS indexed_type
                , opc.opcdefault AS is_default
             from pg_am am
                , pg_opclass opc
            where opc.opcmethod = am.oid
              and am.amname = 'btree'
         order by index_method, opclass_name;
 index_method |    opclass_name     |        indexed_type         | is_default
--------------+---------------------+-----------------------------+------------
 btree        | time_ops            | time without time zone      | t
 btree        | timestamp_ops       | timestamp without time zone | t
 btree        | timestamptz_ops     | timestamp with time zone    | t
 btree        | timetz_ops          | time with time zone         | t

At this point, I am assuming that when the index was created, Postgres said "Silly human. I will fix this for you." and used timestamptz_ops instead of timestamp_ops, and that PG 13+ is stricter in the interpretation. But I would like to be sure of that. We are going to do an in-place upgrade and I would like to know ahead of time if there are going to be problems. I will likely just drop the index and replace it before the upgrade if I can't prove which operator is in use.

Is there a way I can get more detail about exactly what operator class is in use in the index?

A quick look on my end showed that on Postgres 12 CREATE INDEX hourly_by_id ON fs_hourly ( id text_ops, hour timestamp_ops); maps to text_ops and timestamptz_ops respectively in the system catalog. — Adrian Klaver
– Adrian Klaver, Commented May 25, 2023 at 23:31

Adrian Klaver · Accepted Answer · 2023-05-25 23:39:47Z

 select version();
                                       version                                       
-------------------------------------------------------------------------------------
 PostgreSQL 12.15 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit

CREATE TABLE fs_hourly (
    id VARCHAR (255) NOT NULL,
    hour TIMESTAMP WITH TIME ZONE NOT NULL,
    -- more stuff here
    PRIMARY KEY (id, hour)
);
CREATE TABLE

CREATE INDEX hourly_by_id ON fs_hourly (
    id text_ops,
    hour timestamp_ops
);
CREATE INDEX

\d fs_hourly
                      Table "public.fs_hourly"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 id     | character varying(255)   |           | not null | 
 hour   | timestamp with time zone |           | not null | 
Indexes:
    "fs_hourly_pkey" PRIMARY KEY, btree (id, hour)
    "hourly_by_id" btree (id, hour)


SELECT
    c.relname,
    o.opcname
FROM
    pg_class AS c
    JOIN pg_index AS i ON c.oid = i.indexrelid
    JOIN pg_opclass AS o ON o.oid = ANY (i.indclass)
WHERE
    relname = 'hourly_by_id';

relname    |     opcname     
--------------+-----------------
 hourly_by_id | text_ops
 hourly_by_id | timestamptz_ops

Collectives™ on Stack Overflow

change in behavior declaring index on timestamp column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related