Logical replication - empty search_path bug? - Mailing list pgsql-bugs

Hello,

My name is Cezary. I was using postgresql-11 (11.11) logical replication and I came across a strange situation. I have a table looking like that:

CREATE TABLE testrepl(

    salary integer CHECK (salary > get_minimal_salary())

);

get_minimal_salary function looks like that:

CREATE OR REPLACE FUNCTION get_minimal_salary() RETURNS INTEGER AS

$$

BEGIN

    RETURN (SELECT minimal_salary FROM company_standards);

END;

$$ LANGUAGE plpgsql;

And company_standards table looks like that:

CREATE TABLE company_standards(

    minimal_salary integer

);

Contents of the tables on the Master:

database=# SELECT * FROM company_standards;

minimal_salary

----------------

             20

(1 row)

database=# SELECT * FROM testrepl;

salary

--------

     30

     40

(2 rows)

I create a publication with both testrepl and company_standards tables. Replication signals an error on the Replica side when trying to replicate testrepl data. ERROR message looks like follows:

2021-10-22 12:18:33.982 GMT [19728]  LOG:  background worker "logical replication worker" (PID 20198) exited with exit code 1

2021-10-22 12:18:38.992 GMT [20200]  LOG:  logical replication table synchronization worker for subscription "replsub", table "testrepl" has started

2021-10-22 12:18:39.008 GMT [20200]  ERROR:  relation "company_standards" does not exist at character 36

2021-10-22 12:18:39.008 GMT [20200]  QUERY:  SELECT (SELECT minimal_salary FROM company_standards)

2021-10-22 12:18:39.008 GMT [20200]  CONTEXT:  PL/pgSQL function public.get_minimal_salary() line 3 at RETURN

    COPY testrepl, line 1: "30"

However company_standards table has been replicated and contains the same values as on the Master side. get_minimal_salary works when run by hand, problems occur only when it is run as a part of the logical replication process. Global search_path is set to the default "$user", public and there is no custom search path for the replication user.

I was looking for the cause of this situation and I found that during the replication the search_path being used is empty. I did that by modifying get_minimal_salary function like that:

CREATE OR REPLACE FUNCTION get_minimal_salary() RETURNS INTEGER AS

$$

BEGIN

    Copy (SELECT setting FROM pg_settings WHERE name='search_path') To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

    RETURN (SELECT minimal_salary FROM company_standards);

END;

$$ LANGUAGE plpgsql;

Which gave me /tmp/test.csv showing that the search_path is empty in this situation:

setting

""

Replication works fine when the get_minimal_salary function has specified schema for company_standars table (public.company_standards). That’s another thing pointing at the search_path problem.

I searched the documentation before writing to you and I haven’t seen anywhere that such behavior is to be expected, I didn’t find anything clarifying that logical replication alters or cleanses the search_path for its processes. Is this situation a bug, or is it just me missing something in the documentation? And is there a way of setting the search_path to be used for the replication?

The situation was observed on Centos7 (7.7.1908) architecture x86_64.

Kind regards,

Cezary Troska