Anonymous | Login
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2019-08-12 12:382019-08-12 12:38
ReportershuehnerView Statuspublic 
Assigned Toplatform 
PrioritynormalResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo

0041631: update.database sometimes fails when tad is not a superuser as 2 operators have wrong owner

DescriptionSometimes 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] 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 |

- 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

- 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
Steps To ReproduceUnknown still:
See description for best info available.
Proposed SolutionFollow 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.
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
shuehner (administrator)
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;

- Issue History
Date Modified Username Field Change
2019-08-12 12:38 shuehner New Issue
2019-08-12 12:38 shuehner Assigned To => platform
2019-08-12 12:38 shuehner Modules => Core
2019-08-12 12:38 shuehner Triggers an Emergency Pack => No
2019-08-12 12:38 shuehner Note Added: 0113945

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker