Could not access file "$libdir/plpgsql": No such file or directory

Question

I'm at a loss, I'm having issues creating a stored proc in my local Postgres server (postgres.app, Mac OS X 10.7), as so

$ psql
psql (9.3.0)
Type "help" for help.

dchaston=# CREATE OR REPLACE FUNCTION table_update()
dchaston-# RETURNS TRIGGER AS $$
dchaston$# BEGIN
dchaston$#   NEW.last_edit = now();
dchaston$#   RETURN NEW;
dchaston$# END;
dchaston$# $$ language 'plpgsql';
ERROR:  could not access file "$libdir/plpgsql": No such file or directory

I've checked the following:

Languages installed:

dchaston=# 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            |         12019 |     12020 |        12021 | 
(4 rows)

lib directory (and pkglibdir just in case):

$ pg_config --libdir
/Applications/Postgres.app/Contents/MacOS/lib
$ pg_config --pkglibdir
/Applications/Postgres.app/Contents/MacOS/lib

File present:

$ cd /Applications/Postgres.app/Contents/MacOS/lib; ls plpg*
plpgsql.so

DLSUFFIX set correctly:

lib/pgxs/src/Makefile.shlib:135:    DLSUFFIX        = .so

Have tried uninstalling and reinstalling, but made no difference. Any ideas?

Answer 1

Did you install multiple instances (multiple versions or multiple instances of the same version) of Postgres on the same box? Standard Postgres is not fit for that. Debian or Ubuntu have additional infrastructure to allow multiple versions in parallel. I don't know about OS X, though.

In standard Postgres the path for $libdir is compiled into the program. Multiple versions do not get along.
When you execute pg_config --pkglibdir, make sure it's the one associated with your installation. Run:

which pg_config

Minor notes:

• 9.3.0? It's recommended to always upgrade to the latest point-release, which is 9.3.2 at the moment. Maybe a current source fixes your problem.

• Also check your settings whether you are using the $libdir you think you are using:

  SELECT * FROM pg_settings WHERE  name ~~* '%lib%';
  

• Don't quote the language name 'plpgsql' (though it's tolerated). It's an identifier: plpgsql.

• Use the plpgsql assignment operator :=. = is undocumented but tolerated.
  _{Since Postgres 9.4 both := and = are documented.}

Otherwise your function definition is fine. That's not the root of the problem:

CREATE OR REPLACE FUNCTION table_update()
  RETURNS trigger AS
$func$
BEGIN
   NEW.last_edit := now();
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Answer 2

This one is related to the accepted answer.

I started getting the problem with libdir on MacOS Catalina 10.15.7 while doing the brew update as the brew update failed in between and I noticed that Postgresql was one of the many packages that it was trying to update and the above answer provided a good hint about multiple installations.

I verified that there weren't any additional versions of postgresql installed on my system. So a simple restart of the service did the trick for me: brew services restart postgresql@10