Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.6\))
Subject: Re: Optionally automatically disable logical replication
 subscriptions on error
From: Mark Dilger <mark.dilger@enterprisedb.com>
In-Reply-To: 
 <CAA4eK1+NoRbYSH1J08zi4OJ_EUMcjmxTwnmwVqZ6e_xzS0D6VA@mail.gmail.com>
Date: Sun, 20 Jun 2021 22:12:44 -0700
Cc: Masahiko Sawada <sawada.mshk@gmail.com>,
 "Smith, Peter" <peters@fast.au.fujitsu.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <0DABB861-BE21-4138-A0D4-5C561F51CD19@enterprisedb.com>
References: <DB35438F-9356-4841-89A0-412709EBD3AB@enterprisedb.com>
 <CAA4eK1KkhsNAW3=XxOdVRD9+RcQG5PRRFBFzAMibMG0YFFmAmg@mail.gmail.com>
 <B522A0FA-0B5C-4A66-9B73-928CAA86FB88@enterprisedb.com>
 <CAA4eK1KM23D06_rUEsgnTFUpABoeCCoTun2TMrQzWgxWOYY7fA@mail.gmail.com>
 <DE7E13B7-DC76-416A-A98F-3BC3F80E6BE9@enterprisedb.com>
 <CAD21AoB+QH4or-QDiakALZp6pkXiAaGxfGcsvNYJoDHk9kknZw@mail.gmail.com>
 <F25752DE-45EC-4DDE-97D9-75F270FB67CD@enterprisedb.com>
 <CAA4eK1+NoRbYSH1J08zi4OJ_EUMcjmxTwnmwVqZ6e_xzS0D6VA@mail.gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/0DABB861-BE21-4138-A0D4-5C561F51CD19%40enterprisedb.com>
Precedence: bulk


> On Jun 20, 2021, at 8:09 PM, Amit Kapila <amit.kapila16@gmail.com> =
wrote:
>=20
> (a) to define exactly what all
> information is required to be logged on error, (b) where do we want to
> store the information based on requirements.

I'm not sure it has to be stored anywhere durable.  I have a patch in =
the works to do something like:

create function foreign_key_insert_violation_before() returns =
conflict_trigger as $$
BEGIN
    RAISE NOTICE 'elevel: %', TG_ELEVEL:
    RAISE NOTICE 'sqlerrcode: %', TG_SQLERRCODE:
    RAISE NOTICE 'message: %', TG_MESSAGE:
    RAISE NOTICE 'detail: %', TG_DETAIL:
    RAISE NOTICE 'detail_log: %', TG_DETAIL_LOG:
    RAISE NOTICE 'hint: %', TG_HINT:
    RAISE NOTICE 'schema: %', TG_SCHEMA_NAME:
    RAISE NOTICE 'table: %', TG_TABLE_NAME:
    RAISE NOTICE 'column: %', TG_COLUMN_NAME:
    RAISE NOTICE 'datatype: %', TG_DATATYPE_NAME:
    RAISE NOTICE 'constraint: %', TG_CONSTRAINT_NAME:

        -- do something useful to prepare for retry of transaction
        -- which raised a foreign key violation
END
$$ language plpgsql;

create function foreign_key_insert_violation_after() returns =
conflict_trigger as $$
BEGIN
        -- do something useful to cleanup after retry of transaction
        -- which raised a foreign key violation
END
$$ language plpgsql;

create conflict trigger regress_conflict_trigger_insert on =
regress_conflictsub
   before foreign_key_violation
   when tag in ('INSERT')
   execute procedure foreign_key_insert_violation_before();

create conflict trigger regress_conflict_trigger_insert on =
regress_conflictsub
   after foreign_key_violation
   when tag in ('INSERT')
   execute procedure foreign_key_insert_violation_after();

The idea is that, for subscriptions that have conflict triggers defined, =
the apply will be wrapped in a PG_TRY()/PG_CATCH() block.  If it fails, =
the ErrorData will be copied in the ConflictTriggerContext, and then the =
transaction will be attempted again, but this time with any BEFORE and =
AFTER triggers applied.  The triggers could then return a special result =
indicating whether the transaction should be permanently skipped, =
applied, or whatever.  None of the data needs to be stored anywhere =
non-transient, as it just gets handed to the triggers.

I think the other patch is a subset of this functionality, as using this =
system to create triggers which query a table containing transactions to =
be skipped would be enough to get the functionality you've been =
discussing.  But this system could also do other things, like modify =
data.  Admittedly, this is akin to a statement level trigger and not a =
row level trigger, so a number of things you might want to do would be =
hard to do from this.  But perhaps the equivalent of row level triggers =
could also be written?

=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company