Closed Bug 1044457 Opened 10 years ago Closed 10 years ago

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

Categories

(Bugzilla :: Database, defect, P2)

4.5.5
defect

Tracking

()

RESOLVED FIXED
Bugzilla 5.0

People

(Reporter: wicked, Assigned: dylan)

References

Details

(Keywords: regression)

Attachments

(1 file, 2 obsolete files)

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.
(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: database → dylan
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.
(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
FYI, one CREATE LANGUAGE plpgsql later and lanfill pg install is up and alive! \o/
Priority: -- → P2
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)
Attached patch bug-1044457-v1.patch (obsolete) — Splinter Review
Attachment #8484765 - Flags: review?
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-
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
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
Attached patch bug-1044457-v2.patch (obsolete) — Splinter Review
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?
Removed useless check of $self.
Attachment #8485990 - Attachment is obsolete: true
Attachment #8485990 - Flags: review?
Attachment #8485992 - Flags: review?
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
Status: NEW → ASSIGNED
Attachment #8485992 - Flags: review? → review?(sgreen)
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?
Attachment #8485992 - Flags: review?(sgreen) → review?
Target Milestone: Bugzilla 5.0 → ---
Definitely a blocker for 5.0.
Target Milestone: --- → Bugzilla 5.0
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 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 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+
The fact that some Linux distros have this language in a separate RPM is a bit annoying, though. Really not ideal.
Flags: approval?
Flags: approval? → approval+
To ssh://gitolite3@git.mozilla.org/bugzilla/bugzilla.git
   f1897d3..e6fa992  master -> master

Committed -- the uppercased version.
Status: ASSIGNED → RESOLVED
Closed: 10 years ago
Resolution: --- → FIXED
Added to relnotes for 5.0rc1.
Keywords: relnote
You need to log in before you can comment on or make changes to this bug.

Attachment

General

Created:
Updated:
Size: