Unable to upgrade PostgreSQL from 15 to 18

[nelson....@gmail.com]

I have a running iDempiere 10.0 instance with PostgreSQL 15.15 DB. I try to use "pg_upgrade" to upgrade the DB to 18.1. But the upgrade process fails with the following error:

pg_restore: error: could not execute query: ERROR:  commutator operator - is already the commutator of operator +
Command was: CREATE OPERATOR "adempiere".- (
    FUNCTION = "adempiere"."subtractdays",
    LEFTARG = interval,
    RIGHTARG = numeric,
    COMMUTATOR = OPERATOR("adempiere".-)
);

Anyone knows how to resolve this issue?

Nelson Chan

[zuhri utama]

Hi Nelson,

you already found the root cause. Postgresql 18  has become stricter about operator definitions and now rejects operators whose commutator points back to itself. you can found the best solution by copy paste your question to chatgpt to solve the issue.

you can read here https://chatgpt.com/share/69705615-d800-800c-ab6a-d894ae284196

[Carlos Antonio Ruiz Gomez]

We have same error in our database seed dump.

I think is better to open a ticket for that.

Running the RUN_ImportIdempiere on pg-18 is showing these errors:

psql:/opt/idempiere-server/data/seed/Adempiere_pg.dmp:3914: ERROR:  commutator operator - is already the commutator of operator +
psql:/opt/idempiere-server/data/seed/Adempiere_pg.dmp:3917: ERROR:  operator does not exist: interval adempiere.- numeric

Regards,

Carlos Ruiz