From: André Kutepow <a.kutepow@prodat-sql.de>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for replica-mode
Date: Tue, 11 Jul 2023 10:50:43 +0200
Message-ID: <625f6c11-5252-3b3b-575a-5625ddfa8ecc@prodat-sql.de> (raw)
In-Reply-To: <CAKFQuwZtTwMrus2+8_5J-ReLckyH4LSnwtHCcLR1UrRK8YxNOg@mail.gmail.com>
References: <547ccc2a-e8cd-10a3-3127-02cb0a274ca8@prodat-sql.de>
<CAKFQuwZtTwMrus2+8_5J-ReLckyH4LSnwtHCcLR1UrRK8YxNOg@mail.gmail.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: a.kutepow@prodat-sql.de, david.g.johnston@gmail.com
Subject: Re: search_path for replica-mode
In-Reply-To: <625f6c11-5252-3b3b-575a-5625ddfa8ecc@prodat-sql.de>
References: <547ccc2a-e8cd-10a3-3127-02cb0a274ca8@prodat-sql.de> <CAKFQuwZtTwMrus2+8_5J-ReLckyH4LSnwtHCcLR1UrRK8YxNOg@mail.gmail.com> <625f6c11-5252-3b3b-575a-5625ddfa8ecc@prodat-sql.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox