PostgreSQL – Why ‘plpgsql’ Not in pg_extension Table?

plpgsqlpostgresqlpostgresql-extensions

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'?

Best Answer

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.

Related Question