PostgreSQL 8.x fails with error: language "plpgsql" does not exist

RESOLVED FIXED in Bugzilla 5.0

Status

()

P2
blocker
RESOLVED FIXED
4 years ago
4 years ago

People

(Reporter: wicked, Assigned: dylan)

Tracking

({regression})

4.5.5
Bugzilla 5.0
regression
Bug Flags:
approval +

Details

Attachments

(1 attachment, 2 obsolete attachments)

(Reporter)

Description

4 years ago
After bug 936275 tries to add a custom function anyarray_uniq, it's creation attempt makes checksetup.pl fail:

DBD::Pg::db do failed: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database. at Bugzilla/DB/Pg.pm line 255
        Bugzilla::DB::Pg::bz_setup_database('Bugzilla::DB::Pg=HASH(0xb52bac0)') called at ./checksetup.pl line 118

This happens on landfill and the updated install is unusable because checksetup.pl never completes.

BTW, I sincerely hope bug 936275 was tested on Pg and not only on MySQL by the reviewer.. A basic patch and checksetup.pl test should have probably found this problem.
(Assignee)

Comment 1

4 years ago
(In reply to Teemu Mannermaa (:wicked) from comment #0)
> BTW, I sincerely hope bug 936275 was tested on Pg and not only on MySQL by
> the reviewer.. A basic patch and checksetup.pl test should have probably
> found this problem.

It is hardly warranted to jump to such a conclusion, comrade.

What version of PgSQL do you have? And what is the packager?

A full reinstall of postgresql for me has the plpgsql language:

> postgres=# \dL
>                      List of languages
>  Name   |  Owner   | Trusted |         Description          
>---------+----------+---------+------------------------------
> plpgsql | postgres | t       | PL/pgSQL procedural language

Also, the init_db process (which is normally handled by the package manager) reports:

> loading PL/pgSQL server-side language ... ok

Wikipedia's article about PL/pgSQL says: "PL/pgSQL is the only programming language installed by default for PostgreSQL,"

A little more research indicates early 8.x releases didn't have plpgsql.

Checking for this will not be trivial as CREATE LANGUAGE IF NOT EXISTS does not exist in postgres.
We can test for it in perl and conditionally send the CREATE LANGUAGE command.
(Assignee)

Updated

4 years ago
Assignee: database → dylan

Comment 2

4 years ago
I (the reporter and patch submitter) did test this on Pg 9.3 (the default for Ubuntu 14.04), but didn't have access to a Pg 8.x box.

All Postgres 8.x branches are now EOL ( http://www.postgresql.org/about/news/1534/ ), so maybe the best solution is to require >= v9.0 and we can get rid of the hacks we used for v8. People that want to use v8 can still use Bugzilla 4.4 which has 18 months support.

Is there any objections to making Pg 9 the minimum required version. RHEL 7 has Postgres 9 in it, as does the latest Ubuntu LTS.
(Reporter)

Comment 3

4 years ago
(In reply to Dylan William Hardison [:dylan] from comment #1)
> It is hardly warranted to jump to such a conclusion, comrade.

You are absolutely right, sorry about that comment.

> What version of PgSQL do you have? And what is the packager?

Looks like postgresql-8.3.23-1PGDG.rhel5 that came from the repo of PostgreSQL developers themselves at http://yum.postgresql.org/. It also seems they indeed no longer support neither 8.3 nor 8.4 so we'd need to update the version on landfill. However, landfill is going to get an upgrade to RHEL 6 as soon as I get around to it and that comes with a v8.4 and I'd like to stick with stock versions.
 
> A full reinstall of postgresql for me has the plpgsql language:

Looks like a defaultish (no idea what previous maintainers of landfill have done to the install) install from upstream repo reports:

bugs_tip=> select * from pg_language;
 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
----------+----------+---------+--------------+---------------+--------------+--------
 internal |       10 | f       | f            |             0 |         2246 |
 c        |       10 | f       | f            |             0 |         2247 |
 sql      |       10 | f       | t            |             0 |         2248 |
(3 rows)

So, there's no plpgsql but I can see plpgsql.so library installed so it probably just needs enabling.

> A little more research indicates early 8.x releases didn't have plpgsql.

Yeah, looks like v9 release notes indicate plpgsql is installed now by default. Before it was only available but not enabled by default.

> We can test for it in perl and conditionally send the CREATE LANGUAGE
> command.

This might be a good idea if it's simple and robust test. Regardless, a release notes entry to note this new requirement is in order. Maybe relnote is even enough since the error I got is quite clear (at least to a DBA) and even lists a way to fix the situation.

(In reply to sgreen from comment #2)
> to require >= v9.0 and we can get rid of the hacks we used for v8. People
> that want to use v8 can still use Bugzilla 4.4 which has 18 months support.

Problem is RHEL 6 has 8.4 (like you mentioned on irc) so we'd be basically requiring RHEL 7 for Bugzilla 5. I think that's too early. Perl version bump to 5.10.1 meant we require RHEL 6 so, IMHO, we should try to keep RHEL 6 supported and only require plpgsql extension. (I'm assuming there's no other way to do the new custom function.)

There's also the pg devs repo that could be utilized to use newer Pg version on RHEL 5 and 6 (or even 7) but I'm not sure how their support policy lines up with long term support requirements of enterprise installations. That's why sticking with stock RH packages might be a hard requirement for many enterprises.
Keywords: relnote
(Reporter)

Comment 4

4 years ago
FYI, one CREATE LANGUAGE plpgsql later and lanfill pg install is up and alive! \o/

Updated

4 years ago
Priority: -- → P2
(Assignee)

Comment 5

4 years ago
Where can we tie in to checksetup.pl a conditional 'CREATE LANGUAGE plpgsql' when it hasn't been created yet?
Flags: needinfo?(glob)
Flags: needinfo?(dkl)
Bugzilla/Install/DB.pm.

eg. _change_all_mysql_booleans_to_tinyint() has mysql-specific calls.
Flags: needinfo?(glob)
Flags: needinfo?(dkl)
(Assignee)

Comment 7

4 years ago
Created attachment 8484765 [details] [diff] [review]
bug-1044457-v1.patch
Attachment #8484765 - Flags: review?

Comment 8

4 years ago
Comment on attachment 8484765 [details] [diff] [review]
bug-1044457-v1.patch

This is not the right place to do this. You must add your code into Bugzilla::DB::Pg::bz_setup_database(), which is where Pg-specific configuration and functions are declared.

Bugzilla::Install::DB is only to update the DB *schema*, nothing else.
Attachment #8484765 - Flags: review? → review-

Comment 9

4 years ago
I don't think it qualifies as a blocker as plpgsql is installed by default, and so your installation is not affected unless you have some weird configuration.
Severity: blocker → major
(Assignee)

Comment 10

4 years ago
plpgsql is installed by default in 9.x, it is not default in 8.x which is the default on RHEL 6, as discussed previously.
Severity: major → blocker
(Assignee)

Comment 11

4 years ago
Created attachment 8485990 [details] [diff] [review]
bug-1044457-v2.patch

I moved this to Bugzilla/DB/Pg, per previous patch review. It does work if plpgsql doesn't exist, but it should be reviewed by someone with postgres < 9.
Attachment #8484765 - Attachment is obsolete: true
Attachment #8485990 - Flags: review?
(Assignee)

Comment 12

4 years ago
Created attachment 8485992 [details] [diff] [review]
bug-1044457-v2.1.patch

Removed useless check of $self.
Attachment #8485990 - Attachment is obsolete: true
Attachment #8485990 - Flags: review?
Attachment #8485992 - Flags: review?

Updated

4 years ago
Keywords: regression
Summary: Updating Bugzilla DB that uses Pg fails with error: language "plpgsql" does not exist → PostgreSQL 8.x fails with error: language "plpgsql" does not exist
Version: unspecified → 4.5.5
(Assignee)

Updated

4 years ago
Status: NEW → ASSIGNED

Updated

4 years ago
Attachment #8485992 - Flags: review? → review?(sgreen)
(Assignee)

Comment 13

4 years ago
A good suggestion, but Simon doesn't have a postgres 8.x install to test again, last we spoke. :-/
Well *someone* has to review this--simon, you're our postgres person, any way you can test this out?

Updated

4 years ago
Attachment #8485992 - Flags: review?(sgreen) → review?

Updated

4 years ago
Target Milestone: Bugzilla 5.0 → ---

Comment 15

4 years ago
Definitely a blocker for 5.0.
Target Milestone: --- → Bugzilla 5.0
(Assignee)

Comment 16

4 years ago
So, if I provide a CentOS 6 machine with stock postgresql 8.x installed, and provide shell access to *anyone* else that is a reviewer, will they review this patch? I can stand up such a machine in about an hour, and that's being very generous.

Comment 17

4 years ago
Comment on attachment 8485992 [details] [diff] [review]
bug-1044457-v2.1.patch

Tested using Pg 8.4 on Mageia 3. Instead of "language 'plpgsql' doesn't exist", I now get "cannot access $libdir/plpgsql, no such file or directory".
Attachment #8485992 - Flags: review? → review-

Comment 18

4 years ago
Comment on attachment 8485992 [details] [diff] [review]
bug-1044457-v2.1.patch

>+    my ($has_plpgsql) = $self->selectrow_array("select count(*) from pg_language where lanname = 'plpgsql'");

Actually, the plpgsql language was in a separate RPM. Installing it fixed the problem. So r=LpSolit if you write the SQL query uppercase, to match Bugzilla standard:

"SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql'"
Attachment #8485992 - Flags: review- → review+

Comment 19

4 years ago
The fact that some Linux distros have this language in a separate RPM is a bit annoying, though. Really not ideal.
Flags: approval?

Updated

4 years ago
Flags: approval? → approval+
(Assignee)

Comment 20

4 years ago
To ssh://gitolite3@git.mozilla.org/bugzilla/bugzilla.git
   f1897d3..e6fa992  master -> master

Committed -- the uppercased version.
Status: ASSIGNED → RESOLVED
Last Resolved: 4 years ago
Resolution: --- → FIXED

Comment 21

4 years ago
Added to relnotes for 5.0rc1.
Keywords: relnote
You need to log in before you can comment on or make changes to this bug.