Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0041631Openbravo ERPA. Platformpublic2019-08-12 12:382019-08-12 12:38
shuehner 
platform 
normalmajorhave not tried
newopen 
5
 
 
Core
No
0041631: update.database sometimes fails when tad is not a superuser as 2 operators have wrong owner
Sometimes update.database fails when being run in a system which as tad not being a superuser (default when using Appliance 18.04):

Error is as follows:
     [java] SELECT * FROM drop_operator('+'::varchar,'timestamptz'::varchar,'numeric'::varchar)
     [java] 62602 [main] DEBUG org.apache.ddlutils.platform.PGStandardBatchEvaluator - About to execute SQL -- END
     [java]
     [java] CREATE OPERATOR +(
     [java] PROCEDURE = "add_days",
     [java] LEFTARG = timestamptz,
     [java] RIGHTARG = numeric,
     [java] COMMUTATOR = +)
     [java] 62603 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: must be owner of operator +

Checking operator owners in a example database which fails shows 2 operators in public schema with wrong owner (not tad)
 + | b | public | postgres
 + | b | public | postgres
select op.oprname, op.oprkind, n.nspname, r.rolname from pg_operator op, pg_namespace n, pg_roles r where op.oprnamespace = n.oid and op.oprowner = r.oid;

As is already excluding operators created by pg_tgrm extension.

\do+ output in psql is database having the error shows (only showing lines related to this bug):

 public | + | numeric | date | - | - |
 public | + | numeric | timestamp with time zone | - | - |

Comparing that with pristine pi database shows:
 public | + | date | numeric | date | public.add_days |
 public | + | numeric | date | - | - |
 public | + | numeric | timestamp with time zone | - | - |
 public | + | timestamp with time zone | numeric | date | public.add_days |

Context:
- Those operators are created in prescript-postgresql running as user tad (not postgres)
- Bug only shows with the operators related to add_days functions
- There 2 functions with different signature involved

Possible explanation:
The operators related to add_days are the only ones defining a COMMUTATOR=+
Apparently the is creating (or at least showing in \do+) extra variants with the function arguments swapped.
In \do+ those are shown without the Function column showing add_days
When update.database fails the 'drop operator' seems to have suceeded to removal the initial operator entry with the Function=add_days shown. But apparently the extra commutated entry stayed.

So best guess -> Those extra commutated entries somehow got created (probably in the past) with wrong owner postgres.
If those are present with wrong owner -> This bug happens

Note:
- This issue shows typically on Appliance 18.04 migration as there 'tad' user drops is superuser privileges by default (intentionally)
- This issue was also seen in openbravo cloud when using pg_upgrade instead of typical pg_backup/pg_restore
  -> So apparently unrelated to how database migration is done
Unknown still:
See description for best info available.
Follow up best guess in description and try to understand in which exact case operator apparently gets created with wrong owner.
When understood depending on impact (number of instance affected) either document as known issue with known workaround or try to add code to automatically fix it.
No tags attached.
Issue History
2019-08-12 12:38shuehnerNew Issue
2019-08-12 12:38shuehnerAssigned To => platform
2019-08-12 12:38shuehnerModules => Core
2019-08-12 12:38shuehnerTriggers an Emergency Pack => No
2019-08-12 12:38shuehnerNote Added: 0113945

Notes
(0113945)
shuehner   
2019-08-12 12:38   
Known workaround: fix the owner of the 2 operators manually once.

alter operator + (numeric, timestamptz) owner to tad;
alter operator + (numeric, date) owner to tad;