Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0046377Openbravo ERPA. Platformpublic2021-04-27 20:532021-06-08 15:55
Reportershuehner 
Assigned Toplatform 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR21Q3 
Merge Request Statusapproved
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0046377: 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

Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks feature request 0046376 closed shuehner support PostgreSQL 14 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-04-27 20:53shuehnerNew Issue
2021-04-27 20:53shuehnerAssigned To => platform
2021-04-27 20:53shuehnerOBNetwork customer => No
2021-04-27 20:53shuehnerModules => Core
2021-04-27 20:53shuehnerTriggers an Emergency Pack => No
2021-04-27 20:53shuehnerRelationship addedblocks 0046376
2021-04-27 21:22shuehnerNote Added: 0127600
2021-04-27 21:42hgbotMerge Request Status => open
2021-04-27 21:42hgbotNote Added: 0127601
2021-06-08 15:51hgbotMerge Request Statusopen => approved
2021-06-08 15:51hgbotNote Added: 0129201
2021-06-08 15:51hgbotResolutionopen => fixed
2021-06-08 15:51hgbotStatusnew => closed
2021-06-08 15:51hgbotFixed in Version => PR21Q3
2021-06-08 15:51hgbotNote Added: 0129202
2021-06-08 15:55shuehnerNote Added: 0129203

Notes
(0127600)
shuehner   
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   
2021-04-27 21:42   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/353 [^]
(0129201)
hgbot   
2021-06-08 15:51   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/353 [^]
(0129202)
hgbot   
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   
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