Why does 'plpgsql' not appear in the pg_extension table?

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

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.

StackExchange Database Administrators Q#66195, answer score: 3