linuxpostgresqlpsql

psql previous created functions disappeared after quit

Here are the steps and problems I meet in linux environment:

  1. I ran psql -d mimic -U postgres -W to log in psql
  2. I ran \i postgres-function.sql and use \df to display a list of functions. It does display functions I want under the proper schema
  3. I ran \q to quit the psql
  4. I ran psql -d mimic -U postgres -W to log in psql, then ran \df again. However, it displays an empty table. The previously stored functions automatically disappeared.

The reason I would like to \q in the psql because I want to run the .sh script after creating the functions. Could someone tell the reason why the functions created before disappeared?

Solution

  • I am reading https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-BASE-FUNCTIONS

    I connected to a 9.6.22 server (not as "postgres"), and defined one() as in the example. I could SELECT from it, and see it with \df

    Upon disconnecting and re-connecting, I could still SELECT from it, and see it with \df

    I connected to an 11.10 server and obtained identical results.

    tl;dr: Cannot repro the reported problem.

    Perhaps you connected as a user (-U postgres) that lacks write permission for new functions in the public schema? Or a TEMPORARY table is involved, one which DROPs upon TCP disconnect?

    The biggest difference seemed to be PLPGSQL. I tried defining a simple function from the docs:

    CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
  f1 int;
BEGIN
  RETURN f1;
END;
$$ LANGUAGE plpgsql;

    Upon re-connecting I could still use it with SELECT and \df

    And then I noticed this detail in the source code:

    SET search_path TO mimiciii;

    You are not creating REGEXP_EXTRACT(), not creating public.REGEXP_EXTRACT(). No, not by a long shot.

    You are creating mimiciii.REGEXP_EXTRACT().

    The \i command made the search path take effect for the rest of your session. When you start a new session, you will need to issue another search path directive if you wish to refer to functions by their "short" names. Alternatively, you can choose to use a "long" name with SELECT or \df