Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qJ957-0002q7-Tc for pgsql-general@arkaria.postgresql.org; Tue, 11 Jul 2023 08:50:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qJ956-000607-IQ for pgsql-general@arkaria.postgresql.org; Tue, 11 Jul 2023 08:50:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qJ956-0005zw-7k for pgsql-general@lists.postgresql.org; Tue, 11 Jul 2023 08:50:48 +0000 Received: from mail.prodat-sql.de ([116.202.227.166]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qJ953-003ERu-Hb for pgsql-general@lists.postgresql.org; Tue, 11 Jul 2023 08:50:47 +0000 Received: from [192.168.178.136] ([24.134.240.97]) by mail.prodat-sql.de (IceWarp 12.0.2.1 x64) with ASMTP (SSL) id 202307111050430623; Tue, 11 Jul 2023 10:50:43 +0200 Content-Type: multipart/alternative; boundary="------------9r6dgjYksMl8B20qWQdxH050" Message-ID: <625f6c11-5252-3b3b-575a-5625ddfa8ecc@prodat-sql.de> Date: Tue, 11 Jul 2023 10:50:43 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: search_path for replica-mode To: "David G. Johnston" Cc: "pgsql-general@lists.postgresql.org" References: <547ccc2a-e8cd-10a3-3127-02cb0a274ca8@prodat-sql.de> From: =?UTF-8?Q?Andr=c3=a9_Kutepow?= In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------9r6dgjYksMl8B20qWQdxH050 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Thanks David G. Johnston Am 10.07.2023 um 15:11 schrieb David G. Johnston: > On Monday, July 10, 2023, André Kutepow wrote: > > There is a trigger in the database > > SET search_path TO "$user", public; > > CREATE OR REPLACE FUNCTION art__a_iu_func() > BEGIN >   INSERT INTO table_z...   --//»table_z« is in schema public// > END; > > CREATE OR REPLACE TRIGGER art__a_iu >     AFTER INSERT OR UPDATE OF ak_nr >     ON art >     FOR EACH ROW >     EXECUTE FUNCTION art__a_iu_func(); > > In a regular trigger, it works great! > But, if I announce it, as: > > ALTER TABLE art ENABLE REPLICA TRIGGER art__a_iu; > then I get an error: > > /FEHLER:  Relation »table_z« existiert nicht bei Zeichen xxx// > //ANFRAGE:  INSERT INTO table_z... > > /why does'nt work the search_path for replica-mode? > is it made specifically or is it a bug? > How else can I set the search_path for replica-mode? > > > Relying on external search_path for system executed objects is > problematic.  Don’t do it. Either attach a SET to the function or > schema-qualify references. > > David J. > --------------9r6dgjYksMl8B20qWQdxH050 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Thanks David G. Johnston

Am 10.07.2023 um 15:11 schrieb David G. Johnston:
On Monday, July 10, 2023, André Kutepow <a.kutepow@prodat-sql.de> wrote:
There is a trigger in the database
SET search_path TO "$user", public;

CREATE OR REPLACE FUNCTION art__a_iu_func()
BEGIN
  INSERT INTO table_z...   --»table_z« is in schema public
END;

CREATE OR REPLACE TRIGGER art__a_iu
    AFTER INSERT OR UPDATE OF ak_nr
    ON art
    FOR EACH ROW
    EXECUTE FUNCTION art__a_iu_func();
In a regular trigger, it works great!
But, if I announce it, as:
ALTER TABLE art ENABLE REPLICA TRIGGER art__a_iu;
then I get an error:
FEHLER:  Relation »table_z« existiert nicht bei Zeichen xxx
ANFRAGE:  INSERT INTO table_z...

why does'nt work the search_path for replica-mode?
is it made specifically or is it a bug?
How else can I set the search_path for replica-mode?


Relying on external search_path for system executed objects is problematic.  Don’t do it. Either attach a SET to the function or schema-qualify references.

David J.


--------------9r6dgjYksMl8B20qWQdxH050--