Unable to extend postgresql database with timescaledb

Question

I have created a PostgreSQL cluster with 3 nodes using Patroni.

I am using Ubuntu 18.04, Postgresql-10 and Timescaledb 1.4.2.

In postgresql.conf file I have included shared_preload_libraries = 'timescaledb'

When extend the postresql with timescaledb using the command

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; 

it gives the error

ERROR: cache lookup failed for function 1

Answer 1

So, to summarize our discussion on Slack, this is what was the issue. I'm summarizing it here since it can be useful to others that run into a similar problem.

TD;DR. You had problems with all extensions on this machine, not only TimescaleDB, and the problem turned out to be related to creating new C functions which the extension was doing.

When functions are defined, PostgreSQL takes the part after LANGUAGE (in this case C) and look for a language validator in pg_languages. The code for this is in CreateFunction in the file functioncmds.c.

In your case pg_languages contained:

SELECT * FROM pg_languages;
lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
----------+----------+---------+--------------+---------------+-----------+--------------+--------
internal |       10 | f       | f            |             0 |         0 |         2246 |
sql      |       10 | f       | t            |             0 |         0 |         2248 |
plpgsql  |       10 | t       | t            |         13005 |     13006 |        13007 |
c        |       10 | f       | f            |             0 |         0 |            1 |
(4 rows)

From the table above, OID for the language validator (which is our culprit) is 1.

Once that is done, PostgreSQL will call the validator with the function to be defined and allow the validator to check that the function is correctly defined. This is done in ProcedureCreate in the file pg_proc.c.

It proceeds by looking up the OID of the function in the pg_proc table, which in your case contained:

SELECT oid, proname FROM pg_proc WHERE oid = 1;
oid | proname
-----+---------
(0 rows)

So, no procedure for the language validator is found and it generates the error here (this is inside fmgr_info_cxt_security in fmgr.c):

    /* Otherwise we need the pg_proc entry */
    procedureTuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(functionId));
    if (!HeapTupleIsValid(procedureTuple))
        elog(ERROR, "cache lookup failed for function %u", functionId);
    procedureStruct = (Form_pg_proc) GETSTRUCT(procedureTuple);

It is possible to fix this by looking up the correct language validator to use. All the built-in validators are named fmgr_<lang>_validator so we can find them using:

SELECT oid, proname FROM pg_proc WHERE proname LIKE '%fmgr%validator%';
 oid  |         proname         
------+-------------------------
 2246 | fmgr_internal_validator
 2247 | fmgr_c_validator
 2248 | fmgr_sql_validator
(3 rows)

So, this query will update the lanvalidator column to use the right validator.

UPDATE pg_languages
   SET lanvalidator = (SELECT oid FROM pg_proc WHERE proname = 'fmgr_c_validator')
 WHERE lanname = 'c'

Note that this explains why you get the error, but not why pg_languages contained the wrong OID for the lanvalidator column. We hypothesized that it could something that happened during a failover since you had a few promotions happen, but ultimately, it is pure guessing.