Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0046377
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformminorhave not tried2021-04-27 20:532021-06-08 15:55
ReportershuehnerView Statuspublic 
Assigned Toplatform 
PrioritynormalResolutionfixedFixed in VersionPR21Q3
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0046377: Errors during create.database with PostgreSQL 14: ERROR: syntax error at or near "'plpgsql'"

DescriptionRunning create.database with a PostgreSQL 14 (unreleased) daily build fails applying the prescript-PostgreSQL.sql file:

[createdatabase] 7744 [main] INFO org.openbravo.ddlutils.task.CreateDatabase - Executing script prescript-PostgreSql.sql
[createdatabase] 7760 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: syntax error at or near "'plpgsql'"
[createdatabase] Position: 116
[createdatabase] 7760 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - -- END
[createdatabase]
[createdatabase] CREATE OR REPLACE FUNCTION insert_pg_language()
[createdatabase] RETURNS integer AS '
[createdatabase] CREATE TRUSTED PROCEDURAL LANGUAGE ''plpgsql''
[createdatabase] HANDLER plpgsql_call_handler
[createdatabase] VALIDATOR plpgsql_validator;
[createdatabase] SELECT 1;
[createdatabase] ' LANGUAGE 'sql'
[createdatabase] 7763 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: function insert_pg_language() does not exist
[createdatabase] Hint: No function matches the given name and argument types. You might need to add explicit type casts.
[createdatabase] Position: 125
[createdatabase] 7764 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - -- END
[createdatabase]
[createdatabase] CREATE OR REPLACE FUNCTION create_language(varchar)
[createdatabase] RETURNS integer AS '
[createdatabase] SELECT
[createdatabase] CASE WHEN exist_language($1)=0
[createdatabase] THEN insert_pg_language()
[createdatabase] END;
[createdatabase] SELECT 1;
[createdatabase] ' LANGUAGE 'sql'
[createdatabase] 7769 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: function create_language(unknown) does not exist
[createdatabase] Hint: No function matches the given name and argument types. You might need to add explicit type casts.
[createdatabase] Position: 23
[createdatabase] 7770 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - -- END
[createdatabase]
[createdatabase] SELECT * FROM create_language('plpgsql')
[createdatabase] 8013 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: function insert_pg_language() does not exist
[createdatabase] 8013 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - -- END
[createdatabase]
[createdatabase] DROP FUNCTION insert_pg_language()
[createdatabase] 8014 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: function create_language(character varying) does not exist
[createdatabase] 8014 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - -- END
[createdatabase]
[createdatabase] DROP FUNCTION create_language(varchar)
[createdatabase] 8048 [main] INFO org.apache.ddlutils.platform.PGStandardBatchEvaluator - Executed 118 SQL command(s) with 5 error(s) in 298 ms
Steps To ReproduceInstall postgresql 14 development build
For example using dev builds available from apt.postgresql.org
source.list entry:
https://apt.postgresql.org/pub/repos/apt [^] bullseye-pgdg-testing main 14
apt install postgresql-14

TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
blocks feature request 0046376 closedshuehner support PostgreSQL 14 

-  Notes
(0127600)
shuehner (administrator)
2021-04-27 21:22

 The code-block in question is quite doubtful anyway:
- Since PostgreSQL 9.0 the pl/pgsql language is now installed by default
  https://www.postgresql.org/docs/9.0/release-9-0.html [^]
- create language command should not be used as it is since a while
  as languages have been changed to be just 'extensions'. So even if pl/pgsql were not installed by default or missing "proper" way would be 'create extension'.

Manually removing the 'default installed' extension (adding 'drop extension' at end of prescript-systemuser-PostgreSQL.sql file shows that old 'create language' apparently still working.

However it should just:
a.) no be needed at all for new installs (we require pg10 > 9.0 having it by default)
b.) Even for updates it should not be needed as a postgres update 'classically' is pg_restore (which is preceeded by a create database which should get new 'installed by default').

If doubtful the code in question could be simplified to be 'create extension plpgsql' instead of the older syntax apparently now having issues.
(0127601)
hgbot (developer)
2021-04-27 21:42

 Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/353 [^]
(0129201)
hgbot (developer)
2021-06-08 15:51

 Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/353 [^]
(0129202)
hgbot (developer)
2021-06-08 15:51

 Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: fe84fb280582a6297c63edb4f53d880e46b0d6e8
Author: Stefan Huehner <stefan@huehner.org>
Date: 2021-06-08T13:50:59+00:00
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/fe84fb280582a6297c63edb4f53d880e46b0d6e8 [^]

Fixes ISSUE-46377: Fix create.database with PostgreSQL 14.

Some very old code to check & create the pl/pgsql language is running in
prescript and is how having syntax issue with upcoming PG14.

However since PG 9.0 the pl/pgsql language is installed by default (and
as an extension) so that code should not be needed anway (as we require
PG 10 since 20Q1 release already).

Remove that code as obsolete which indirectly fixes the PG 14 problem.

---
M src-db/database/model/prescript-PostgreSql.sql
---
(0129203)
shuehner (administrator)
2021-06-08 15:55

 Tests / analysis for behavior:
- pg8.4 create empty database
  \dL (empty)
  \dx (feature of extensions does not exist yet in 8.4)
- pg9.3 (version of ubuntu 14.04)
  \dL plpgsql (owner=postgres,trusted)
  \dx plpgsql
- 8.4 install.source of 15Q2 (last compatible with 8.4)
  \dL plpgsql (owner=tad,trusted)
  Note: owner as we run prescript as tad and this prescript created entry
- 9.3 install.source of 15Q2
  \dL plpgsql (owner=postgres,trusted)
  entry created by postgres itself, our prescript skipped itself so kept owner
- pg_restore of 8.4 dump into 9.3
  - pg_restore shows error 'language plpgsql already exists'
    but continues
  - after that \dL plpgsql (owner=postgres,trusted)
- pg_upgradecluster 8.4 main -v 9.3 (pg_upgrade of 8.4 into 9.3)
  \ḑL maintains plpgsql but old owner=tad

With those tests i see no case were we would still require the code being removed here. As either:
- plpgsql language entry is carried over from old install
- or postgres will auto-create it for a new database (already since 9.0, and we required 10 already today (since 20Q1), we even do not support 9.0 anymore since 15Q3 already

- Issue History
Date Modified Username Field Change
2021-04-27 20:53 shuehner New Issue
2021-04-27 20:53 shuehner Assigned To => platform
2021-04-27 20:53 shuehner Modules => Core
2021-04-27 20:53 shuehner Triggers an Emergency Pack => No
2021-04-27 20:53 shuehner Relationship added blocks 0046376
2021-04-27 21:22 shuehner Note Added: 0127600
2021-04-27 21:42 hgbot Note Added: 0127601
2021-06-08 15:51 hgbot Note Added: 0129201
2021-06-08 15:51 hgbot Resolution open => fixed
2021-06-08 15:51 hgbot Status new => closed
2021-06-08 15:51 hgbot Fixed in Version => PR21Q3
2021-06-08 15:51 hgbot Note Added: 0129202
2021-06-08 15:55 shuehner Note Added: 0129203

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker