Postgres DB Restore Error

[shiju01]

Hi

Getting  error while restoring postgres database

the view Invoice_Candidate_V giving error at  line where ever it add or subtract day  

Which function did not restored properly ?

                AND ((TRUNC(o.DateOrdered) <= firstOf(getdate(),'MM')+si.InvoiceDayCutoff-1
                        AND TRUNC(getdate()) >= firstOf(o.DateOrdered,'MM')+si.InvoiceDay-1)
                    OR    (TRUNC(o.DateOrdered) <= firstOf(getdate(),'MM')+si.InvoiceDayCutoff+14
                        AND TRUNC(getdate()) >= firstOf(o.DateOrdered,'MM')+si.InvoiceDay+14))
   

pg_restore: creating FUNCTION "adempiere.trunc(timestamp with time zone, character varying)"

pg_restore: creating FUNCTION "adempiere.update_sequences()"
pg_restore: creating FUNCTION "public.fnnumbertowords(bigint)"
pg_restore: creating OPERATOR "adempiere.+"
pg_restore: WARNING:  operator attribute "function" not recognized
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6026; 2617 161962 OPERATOR + adempiere
pg_restore: [archiver (db)] could not execute query: ERROR:  operator procedure must be specified
    Command was: CREATE OPERATOR adempiere.+ (
    FUNCTION = adempiere.adddays,
    LEFTARG = timestamp with time zone,
    RIGHTARG = numer...
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: timestamp with time zone adempiere.+ numeric
    Command was: ALTER OPERATOR adempiere.+ (timestamp with time zone, numeric) OWNER TO adempiere;


pg_restore: creating OPERATOR "adempiere.+"
pg_restore: WARNING:  operator attribute "function" not recognized
pg_restore: [archiver (db)] Error from TOC entry 6028; 2617 161964 OPERATOR + adempiere
pg_restore: [archiver (db)] could not execute query: ERROR:  operator procedure must be specified
    Command was: CREATE OPERATOR adempiere.+ (
    FUNCTION = adempiere.adddays,
    LEFTARG = interval,
    RIGHTARG = numeric,
    COMMUTAT...
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: interval adempiere.+ numeric
    Command was: ALTER OPERATOR adempiere.+ (interval, numeric) OWNER TO adempiere;


pg_restore: creating OPERATOR "adempiere.-"
pg_restore: WARNING:  operator attribute "function" not recognized
pg_restore: [archiver (db)] Error from TOC entry 6030; 2617 161966 OPERATOR - adempiere
pg_restore: [archiver (db)] could not execute query: ERROR:  operator procedure must be specified
    Command was: CREATE OPERATOR adempiere.- (
    FUNCTION = adempiere.subtractdays,
    LEFTARG = timestamp with time zone,
    RIGHTARG = ...
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: timestamp with time zone adempiere.- numeric
    Command was: ALTER OPERATOR adempiere.- (timestamp with time zone, numeric) OWNER TO adempiere;


pg_restore: creating OPERATOR "adempiere.-"
pg_restore: WARNING:  operator attribute "function" not recognized
pg_restore: [archiver (db)] Error from TOC entry 6031; 2617 161967 OPERATOR - adempiere
pg_restore: [archiver (db)] could not execute query: ERROR:  operator procedure must be specified
    Command was: CREATE OPERATOR adempiere.- (
    FUNCTION = adempiere.subtractdays,
    LEFTARG = interval,
    RIGHTARG = numeric,
    COM...
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: interval adempiere.- numeric
    Command was: ALTER OPERATOR adempiere.- (interval, numeric) OWNER TO adempiere;


pg_restore: creating TABLE "adempiere.a_asset"
pg_restore: creating TABLE "adempiere.a_asset_acct"
pg_restore: creating TABLE "adempiere.a_asset_addition"

pg_restore: creating TABLE "adempiere.c_order"
pg_restore: creating TABLE "adempiere.c_orderline"
pg_restore: creating VIEW "adempiere.c_invoice_candidate_v"
pg_restore: [archiver (db)] Error from TOC entry 582; 1259 167477 VIEW c_invoice_candidate_v adempiere
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: timestamp with time zone adempiere.+ numeric
LINE 18: ...M'::character varying))::timestamp with time zone OPERATOR(a...
                                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
    Command was: CREATE VIEW adempiere.c_invoice_candidate_v AS
 SELECT o.ad_client_id,
    o.ad_org_id,
    o.c_bpartner_id,
    o.c_order_i...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "adempiere.c_invoice_candidate_v" does not exist
    Command was: ALTER TABLE adempiere.c_invoice_candidate_v OWNER TO adempiere;


pg_restore: creating VIEW "adempiere.c_invoice_header_v"
pg_restore: creating VIEW "adempiere.c_invoice_header_vt"
pg_restore: creating TABLE "adempiere.c_invoiceline"

[shiju01]

Hi All,

How suddenly  PostgreSQL lost the ability to add and subtract directly  from timestamp ?

using Version 9.6  - might have lost some function while pg_restore ?

SEVERE: DBExecuteError - ERROR: operator does not exist: timestamp without time zone + integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 73
Dec 06, 2018 7:15:00 PM org.compiere.util.DB executeUpdate
SEVERE: DELETE FROM C_AcctProcessorLog WHERE C_AcctProcessor_ID=1000003 AND (Created+7) < statement_timestamp() [null]
org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone + integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 77
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.compiere.db.StatementProxy.invoke(StatementProxy.java:128)
    at com.sun.proxy.$Proxy4.executeUpdate(Unknown Source)
    at org.compiere.util.DB.executeUpdate(DB.java:1038)
    at org.compiere.util.DB.executeUpdate(DB.java:897)
    at org.compiere.util.DB.executeUpdate(DB.java:884)
    at org.compiere.model.MAcctProcessor.deleteLog(MAcctProcessor.java:167)
    at org.compiere.server.AcctProcessor.doWork(AcctProcessor.java:107)
    at org.compiere.server.AdempiereServer.doRun(AdempiereServer.java:268)
    at org.compiere.server.AdempiereServer.run(AdempiereServer.java:247)
    at org.compiere.server.AdempiereServerMgr$ServerWrapper.run(AdempiereServerMgr.java:570)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

[Carlos Antonio Ruiz Gómez]

Sorry, is it a problem with release 6.1?  Or your customized version?

Regards,

Carlos Ruiz

[shiju01]

Thanks Carlos.

No,  this instance is 5.1 release. 

Postgres Version is 9.6 and Openjdk version "1.8.0_171"

Error is triggered while executing all View tables where ever there is sql phrase which add or subtract a numeric number from a timestamp column    

example: SEVERE: DELETE FROM C_AcctProcessorLog WHERE C_AcctProcessor_ID=1000003 AND (Created+7) <

statement_timestamp() [null] org.postgresql.util.

PSQLException: ERROR: operator does not exist: timestamp without time zone + integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 77

The issue is caused after a pg_restoring a pg_dump.bak file. Not able to identify which builtin function got corrupted ?

[Carlos Antonio Ruiz Gómez]

Hi Shiju, I was just hit by this problem because I made a backup of a server in postgresql 11 and tried to import it in postgres 9.6

Cause of this:

export in postgresql 9.6:

CREATE OPERATOR adempiere.+ (

    PROCEDURE = adempiere.adddays,

    LEFTARG = timestamp with time zone,

    RIGHTARG = numeric,

    COMMUTATOR = OPERATOR(adempiere.+)

);

export in postgresql 11:

CREATE OPERATOR adempiere.+ (

    FUNCTION = adempiere.adddays,

    LEFTARG = timestamp with time zone,

    RIGHTARG = numeric,

    COMMUTATOR = OPERATOR(adempiere.+)

);

Solution: change the word in red in the file - there are several.

Regards,

Carlos Ruiz

[shiju01]

Dear  Carlos

Hats off to you! and you have hit the nail on the head

Now i realize i was restoring a dump from server (postgresql 11) to a laptop (postgresql 9.6) 

So far i believed that database on the server is crashed or on the verge of it

A point to add in to knowledge base

Thank You