SQL Error while compacting

[seqizz]

Hi,

I saw an SQL error while running this on a Postgresql 13 DB. relevant part is:

[Thu Sep 30 09:38:26 2021] (dbname:public.tablename) Progress: 41%,  107925 pages completed.
[Thu Sep 30 09:39:27 2021] (dbname:public.tablename) SQL Error: ERROR:  operator is not unique: smallint[] pg_catalog.@> smallint[]
LINE 26: ...SELECT string_to_array(indkey::text, ' ')::int2[] operator(p...
                                                              ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
[Thu Sep 30 09:39:27 2021] (dbname:public.tablename) Table handling interrupt.
[Thu Sep 30 09:39:27 2021] (dbname:public.tablename) Processing results: 440512 pages (849670 pages including toasts and indexes), size has been reduced by 540.609MB (491.039MB including toasts and indexes) in total. This attempt has been initially expected to compact ~43% more space (189530 pages, 1.446GB)
[Thu Sep 30 09:39:27 2021] (dbname:public.tablename) Handling tables. Attempt 3
[Thu Sep 30 09:39:38 2021] (dbname:public.tablename) Statistics: 440512 pages (849670 pages including toasts and indexes), it is expected that ~43.030% (189547 pages) can be compacted with the estimated space saving being 1.446GB.
[Thu Sep 30 09:40:38 2021] (dbname:public.tablename) Progress: 41%,  107115 pages completed.
[Thu Sep 30 09:41:38 2021] (dbname:public.tablename) SQL Error: ERROR:  operator is not unique: smallint[] pg_catalog.@> smallint[]
LINE 26: ...SELECT string_to_array(indkey::text, ' ')::int2[] operator(p...

I am not sure what is the issue, there is one unique, one non-unique index and one FK constraint which binds to another external unique value.

P.s.: Not using using the latest version (just saw that it's updated 20 days ago) so if this case is not expected on the latest, please ignore.

[Melkij]

Hello Do you have installed some additional extensions (or custom operators) into pg_catalog schema? This error does not occurs on unmodified postgresql 13.

[seqizz]

Oh god, that seems to be the case..

                                             List of installed extensions
+--------------------+---------+------------+------------------------------------------------------------------------+
|        Name        | Version |   Schema   |                              Description                               |
+--------------------+---------+------------+------------------------------------------------------------------------+
| btree_gin          | 1.3     | pg_catalog | support for indexing common datatypes in GIN                           |
| btree_gist         | 1.5     | pg_catalog | support for indexing common datatypes in GiST                          |
| hypopg             | 1.1.4   | public     | Hypothetical indexes for PostgreSQL                                    |
| intarray           | 1.3     | pg_catalog | functions, operators, and index support for 1-D arrays of integers     |
| pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed |
| pg_trgm            | 1.5     | pg_catalog | text similarity measurement and index searching based on trigrams      |
| pgstattuple        | 1.5     | public     | show tuple-level statistics                                            |
| plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language                                           |
| uuid-ossp          | 1.1     | public     | generate universally unique identifiers (UUIDs)                        |
+--------------------+---------+------------+------------------------------------------------------------------------+

Feel free to close if not supported.

[Melkij]

Yep, unfortunately we have conflict with intarray operators in this query. We fixed similar thing in the past (#16), but I have no idea how to more accurately specify to use the generic operator.

Also not sure if create extension ... with schema pg_catalog is a good idea.

[seqizz]

Ah, I just realized the pg_catalog thing. :thinking: Should be mistake, probably not even needed, will check it out, thanks!