postgresqlosx-lionplpgsqlpostgresql-9.3postgres.app

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


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?


Solution

  • 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:

    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$;