0

In postgres 11.5 for a running session suddenly lot of errors reported in pg_logs "relation does not exist". After enabling extra logging we have found out that just before the errors the following statement is getting executed and feels same is the culprit.

SELECT pg_catalog.set_config('search_path', '', false)

We are not running it. Could anyone please help me to understand by whom and under what circumstances it is getting executed.

0

Let me pull out my crystal ball and see what you did...

Ah! I see you restoring a plain format pg_dump from an interactive session using \i dumpfile, so that the settings from the dump remain active in your session after the restore is completed.

7
  • Yes we have plain text format dumps. I am confuse about \i option, is it for psql or for pg_restore? How can I prevent this setting to be remain active in the session? I still need to check if any scheduled restore is happening. Nov 20 '20 at 7:29
  • 1
    Either say RESET search_path; or - simpler - disconnect and reconnect. Settings changed with SET only hold for the duration of a database session. Nov 20 '20 at 7:56
  • I rechecked pg_logs and found out 1. autovacuum: processing database 2. postgres child[8760]: starting with (postgres) 3. InitPostgres 4. above mentioned query. and not seeing anything related to pg_restore or pg_dump . DO you think it can be executed by vacuum or something related ? these queries are just before the error. Nov 20 '20 at 8:05
  • similar logs in dba.stackexchange.com/questions/269365/… Nov 20 '20 at 8:12
  • 1
    You are confused. pg_dump and pg_restore are not SQL statements, they are not run in the database. pg_dump produces a file that contains the statement in your question. You run that file with \i, so the statement shows up in your log. Nothing to worry. Just disconnect and reconnect, and you are good. Nov 20 '20 at 8:15
0

After looking at the source code of Postgres I have found that libpqconnect is running it while making a connection. There are many places but libpqconnect is the file where pg_is_in_reovery is also running after the search path and satisfying the my criteria.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.