3

I have two Postgres 9.1 installations. One works properly, the other does not.

On the working installation:

dc=# select * from pg_extension;
extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
----------+----------+--------------+----------------+------------+-----------+--------------
plpgsql  |       10 |           11 | f              | 1.0        |           | 
plperl   |       10 |           11 | f              | 1.0        |           | 
intarray |       10 |         2200 | t              | 1.0        |           | 
pgtap    |       10 |        66181 | t              | 0.94.0     |           | 
(4 rows)

dc=# select * from pg_language;
lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal |       10 | f       | f            |             0 |         0 |         2246 | 
c        |       10 | f       | f            |             0 |         0 |         2247 | 
sql      |       10 | f       | t            |             0 |         0 |         2248 | 
plpgsql  |       10 | t       | t            |         16392 |     16393 |        16394 | 
plperl   |       10 | t       | t            |         16397 |     16398 |        16399 | 
(5 rows)

On the 'broken' one:

dc=# select * from pg_extension;
extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
----------+----------+--------------+----------------+------------+-----------+--------------
intarray |       10 |         2200 | t              | 1.0        |           | 
(1 row)

dc=# select * from pg_language;
lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal |       10 | f       | f            |             0 |         0 |         2246 | 
c        |       10 | f       | f            |             0 |         0 |         2247 | 
sql      |       10 | f       | t            |             0 |         0 |         2248 | 
plpgsql  |       10 | t       | t            |         27558 |     27559 |        27560 | 
plperl   |       10 | t       | t            |         27562 |     27563 |        27564 | 
(5 rows)

Is there any reason that plpgsql and plperl ought to be listed in pg_language but not in pg_extension on the 'broken' system, or is it truly broken? What is the 'cure'?

  • Weird. I wouldn't have expected plpgsql to appear as an extension at all, but when I look it is indeed shown as an extension. If someone had done a DROP EXTENSION plpgsql it would be missing from pg_language too. Somebody messing with the catalogs directly? – Craig Ringer May 30 '14 at 0:28
  • @CraigRinger: Not intentionally, anyway... :) – Flimzy May 30 '14 at 12:44
3

On PostgreSQL 9.1, it is possible to get to this state without directly manipulating the catalogs.

I started from having plpgsql as an extension, and did the following:

# DROP EXTENSION plpgsql CASCADE; -- do this only if you are prepared to lose your plpgsql functions, too
# \dx
                                     List of installed extensions
     Name      │ Version │ Schema │                            Description                             
───────────────┼─────────┼────────┼────────────────────────────────────────────────────────────────────
 btree_gin     │ 1.0     │ public │ support for indexing common datatypes in GIN
 btree_gist    │ 1.0     │ public │ support for indexing common datatypes in GiST
 dblink        │ 1.0     │ public │ connect to other PostgreSQL databases from within a database
 fuzzystrmatch │ 1.0     │ public │ determine similarities and distance between strings
 hstore        │ 1.0     │ public │ data type for storing sets of (key, value) pairs
 intarray      │ 1.0     │ public │ functions, operators, and index support for 1-D arrays of integers
 pgcrypto      │ 1.0     │ public │ cryptographic functions

# SELECT lanname FROM pg_language;
  lanname  
───────────
 internal
 c
 sql
 plpythonu

CREATE PROCEDURAL LANGUAGE plpgsql; -- this won't work in 9.3, for example

After this, I have no plpgsql as an extension, but it is listed among the languages. You can define functions in this language as usual.

0

I don't think that the server would allow you to drop the "plpgsql" extension. It seems a case of somebody manually deleting the entry from pg_extension catalog table.

You can recreate the extension if it is available in the pg_available_extensions table. If that doesn't work, you can try inserting the required row for plpgsql.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.