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
Date: Wed, 8 Jun 2022 16:12:15 +0200
Message-ID: <YqCuP+j/QIYj1Hqs@msg.credativ.de> (raw)
In-Reply-To: <1615599.1654697159@sss.pgh.pa.us>
References: <YqCfLapinj6+mJ6i@msg.credativ.de>
<1615599.1654697159@sss.pgh.pa.us>
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/datenschutzAttachments:pgdump (text/plain, 5.8K) download
--
-- 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
--
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: christoph.berg@credativ.de, tgl@sss.pgh.pa.us
Subject: Re: Fallout from 'Make type "name" collation-aware' and tg_table_name
In-Reply-To: <YqCuP+j/QIYj1Hqs@msg.credativ.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