Date: Wed, 8 Jun 2022 16:12:15 +0200
From: Christoph Berg <christoph.berg@credativ.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Bugs <pgsql-bugs@postgresql.org>
Subject: Re: Fallout from 'Make type "name" collation-aware' and tg_table_name
Message-ID: <YqCuP+j/QIYj1Hqs@msg.credativ.de>
Mail-Followup-To: Christoph Berg <christoph.berg@credativ.de>,
	Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Bugs <pgsql-bugs@postgresql.org>
References: <YqCfLapinj6+mJ6i@msg.credativ.de>
 <1615599.1654697159@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="bxOmC7SBR77GnlPx"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1615599.1654697159@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/YqCuP%2Bj/QIYj1Hqs%40msg.credativ.de>
Precedence: bulk


--bxOmC7SBR77GnlPx
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Re: Tom Lane
> Christoph Berg <christoph.berg@credativ.de> writes:
> > I was just debugging a client problem where a trigger was running fine
> > with PG 11, but very slow with PG 13.
> 
> Hmmm ... I do not see how the collation within the PERFORM statement
> would affect whatever it's calling?

Here's the actual reproducer I used:

$ cat run.sql
drop database if exists test;
create database test;
\c test

\i pgdump
reset all;

alter system set log_line_prefix = '%m [%p] %q%u@%d %a ';
select pg_reload_conf();

set jit=off;
LOAD 'auto_explain';
set auto_explain.log_analyze=on;
set auto_explain.log_buffers=on;
set auto_explain.log_min_duration=0;
set auto_explain.log_nested_statements=on;
set auto_explain.log_triggers=on;

set application_name = 'collate_bug';
INSERT INTO public.identity_provider (id, created, lastmodified, "version", identity_zone_id, "name", origin_key, "type", config, active)
VALUES('24e9dd6d-5a63-9ea8-b425-ae89aa419a33', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 0, 'insert-test', 'test', 'test', 'test', 'none', true);


Mit freundlichen Grüßen,
Christoph Berg
-- 
Senior Consultant, Tel.: +49 2166 9901 187
credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Geoff Richardson, Peter Lilley
Unser Umgang mit personenbezogenen Daten unterliegt folgenden
Bestimmungen: https://www.credativ.de/datenschutz

--bxOmC7SBR77GnlPx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename=pgdump

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.6 (Debian 13.6-1.pgdg110+1)
-- Dumped by pg_dump version 13.7 (Ubuntu 13.7-1.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: increase_revision(text, text); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION public.increase_revision(v_table_name text, v_identity_zone_id text) RETURNS integer
    LANGUAGE plpgsql
    AS $$

DECLARE
    rev          INTEGER;
    updated_rows INTEGER;
BEGIN
    SELECT nextval('xs_cache_revisions_seq') INTO rev;
    UPDATE xs_cache_revisions
    SET revision = rev
    WHERE table_name = v_table_name
      AND identity_zone_id = v_identity_zone_id
      AND revision =  (SELECT revision
                       FROM xs_cache_revisions
                       WHERE table_name = v_table_name
                         AND identity_zone_id = v_identity_zone_id FOR UPDATE SKIP LOCKED LIMIT 1);

    GET DIAGNOSTICS updated_rows = ROW_COUNT;

    IF (updated_rows = 0) THEN
        INSERT INTO xs_cache_revisions(table_name, identity_zone_id, revision)
        values (v_table_name, v_identity_zone_id, rev);
    END IF;

    delete from xs_cache_revisions where (table_name,identity_zone_id,revision) in (select table_name,identity_zone_id,revision from xs_cache_revisions where (table_name = v_table_name and identity_zone_id = v_identity_zone_id and revision < rev) for update SKIP LOCKED);

    RETURN rev;
END;
$$;


--ALTER FUNCTION public.increase_revision(v_table_name text, v_identity_zone_id text) OWNER TO root;

--
-- Name: update_cache_revision(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION public.update_cache_revision() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
    v_zone_id    TEXT;
BEGIN
    IF (TG_OP = 'UPDATE') or (TG_OP = 'INSERT') THEN
        v_zone_id := NEW.identity_zone_id;

        PERFORM increase_revision(tg_table_name::TEXT, v_zone_id);

        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_zone_id := OLD.identity_zone_id;

        PERFORM increase_revision(tg_table_name::TEXT, v_zone_id);

        RETURN OLD;
    END IF;

END;

$$;


--ALTER FUNCTION public.update_cache_revision() OWNER TO root;

SET default_tablespace = '';

--SET default_table_access_method = heap;

--
-- Name: identity_provider; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE public.identity_provider (
    id character varying(36) NOT NULL,
    created timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    lastmodified timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    version bigint DEFAULT 0,
    identity_zone_id character varying(36) NOT NULL,
    name character varying(255) NOT NULL,
    origin_key character varying(36) NOT NULL,
    type character varying(255) NOT NULL,
    config text,
    active boolean DEFAULT true NOT NULL
);


--ALTER TABLE public.identity_provider OWNER TO root;

--
-- Name: xs_cache_revisions; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE public.xs_cache_revisions (
    table_name character varying(48) NOT NULL,
    identity_zone_id character varying(36) NOT NULL,
    revision bigint NOT NULL,
    created timestamp without time zone DEFAULT now()
);


--ALTER TABLE public.xs_cache_revisions OWNER TO root;

--
-- Name: xs_cache_revisions_revision_seq; Type: SEQUENCE; Schema: public; Owner: root
--

CREATE SEQUENCE public.xs_cache_revisions_revision_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--ALTER TABLE public.xs_cache_revisions_revision_seq OWNER TO root;

--
-- Name: xs_cache_revisions_revision_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: root
--

ALTER SEQUENCE public.xs_cache_revisions_revision_seq OWNED BY public.xs_cache_revisions.revision;


--
-- Name: xs_cache_revisions_seq; Type: SEQUENCE; Schema: public; Owner: root
--

CREATE SEQUENCE public.xs_cache_revisions_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--ALTER TABLE public.xs_cache_revisions_seq OWNER TO root;

--
-- Name: xs_cache_revisions revision; Type: DEFAULT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.xs_cache_revisions ALTER COLUMN revision SET DEFAULT nextval('public.xs_cache_revisions_revision_seq'::regclass);


--
-- Name: identity_provider identity_provider_pkey; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.identity_provider
    ADD CONSTRAINT identity_provider_pkey PRIMARY KEY (id);


--
-- Name: xs_cache_revisions xs_cache_revisions_constr; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.xs_cache_revisions
    ADD CONSTRAINT xs_cache_revisions_constr UNIQUE (table_name, identity_zone_id, revision);


--
-- Name: active_in_zone; Type: INDEX; Schema: public; Owner: root
--

CREATE INDEX active_in_zone ON public.identity_provider USING btree (identity_zone_id, active);


--
-- Name: key_in_zone; Type: INDEX; Schema: public; Owner: root
--

CREATE UNIQUE INDEX key_in_zone ON public.identity_provider USING btree (identity_zone_id, origin_key);


--
-- Name: xs_cache_revisions_zone_table_rev; Type: INDEX; Schema: public; Owner: root
--

CREATE INDEX xs_cache_revisions_zone_table_rev ON public.xs_cache_revisions USING btree (identity_zone_id, table_name, revision);


--
-- Name: identity_provider t_identity_provider_change_trigger; Type: TRIGGER; Schema: public; Owner: root
--

CREATE TRIGGER t_identity_provider_change_trigger AFTER INSERT OR DELETE OR UPDATE ON public.identity_provider FOR EACH ROW EXECUTE PROCEDURE public.update_cache_revision('false');


--
-- PostgreSQL database dump complete
--


--bxOmC7SBR77GnlPx--