Logical replication - empty search_path bug?

From: "Troska, Cezary" <Cezary(dot)Troska(at)Alvaria(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: "Raczynski, Mateusz" <Mateusz(dot)Raczynski(at)Alvaria(dot)com>, "Santos, Gelo" <Gelo(dot)Santos(at)Alvaria(dot)com>
Subject: Logical replication - empty search_path bug?
Date: 2021-10-22 11:55:20
Message-ID: SN6PR08MB4847CEBB43B25ABD56AD10BDFD809@SN6PR08MB4847.namprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
This email (including any attachments) is proprietary to Alvaria and may contain information that is confidential. If you have received this message in error, please do not read, copy or forward this message. Please notify the sender immediately, delete it from your system and destroy any copies. You may not further disclose or distribute this email or its attachments.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2021-10-22 13:52:17 Re: BUG #17243: explain wtih recursive cte error?
Previous Message PG Bug reporting form 2021-10-22 00:49:41 BUG #17243: explain wtih recursive cte error?