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
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.last_edit := now();
   RETURN NEW;
END
$func$;