From: za-arthur (@za-arthur) <noreply+za-arthur@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
Date: Mon, 16 Nov 2026 14:18:21 +0000
Message-ID: <gh-reorg-pg_repack-495@github.com> (raw)
`repack_apply()` runs with `search_path = pg_catalog, pg_temp`, so the unqualified `=` in the generated key comparison could not find operators living outside `pg_catalog` (e.g. hstore installed in public), failing with `operator does not exist` when concurrent UPDATEs/DELETEs were replayed.
Build the comparison from the btree equality operator of each key column's opclass as `col OPERATOR(schema.op) $N.col`, and add a regression test covering a key type whose operator lives in public.
This is a follow up to https://github.com/reorg/pg_repack/pull/493#issuecomment-6838227215.
inline patch 1: lib/pg_repack.sql.in, regress/Makefile, regress/expected/compare_pkey.out and 1 more (+175/-9 lines) download inline-1.patchdiff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.inindex f07b844..8bb731a 100644--- a/lib/pg_repack.sql.in+++ b/lib/pg_repack.sql.in@@ -146,19 +146,29 @@ SET search_path = pg_catalog, pg_temp;
CREATE FUNCTION repack.get_compare_pkey(oid, text)
RETURNS text AS
$$
- SELECT format('(%s) = (%s.%s)',- coalesce(string_agg(quote_ident(attname), ', '), ''),- $2,- coalesce(string_agg(quote_ident(attname), format(', %s.', $2)), ''))- FROM pg_catalog.pg_attribute,- (SELECT indrelid,+ -- Use the schema-qualified btree equality operator of each key column's+ -- operator class, so that the comparison does not depend on search_path.+ SELECT coalesce(string_agg(+ format('%s OPERATOR(%s.%s) %s.%s',+ quote_ident(a.attname), quote_ident(opnsp.nspname),+ op.oprname, $2, quote_ident(a.attname)),+ ' AND ' ORDER BY keys.i), '')+ FROM (SELECT indrelid,
indkey,
+ indclass,
generate_series(0, indnatts-1) AS i
FROM pg_catalog.pg_index
WHERE indexrelid = $1
) AS keys
- WHERE attrelid = indrelid- AND attnum = indkey[i];+ JOIN pg_catalog.pg_attribute a+ ON a.attrelid = keys.indrelid AND a.attnum = keys.indkey[keys.i]+ JOIN pg_catalog.pg_opclass opc ON opc.oid = keys.indclass[keys.i]+ JOIN pg_catalog.pg_amop amop ON amop.amopfamily = opc.opcfamily+ AND amop.amoplefttype = opc.opcintype+ AND amop.amoprighttype = opc.opcintype+ AND amop.amopstrategy = 3+ JOIN pg_catalog.pg_operator op ON op.oid = amop.amopopr+ JOIN pg_catalog.pg_namespace opnsp ON opnsp.oid = op.oprnamespace;
$$
LANGUAGE sql STABLE STRICT
SET search_path = pg_catalog, pg_temp;
diff --git a/regress/Makefile b/regress/Makefileindex 43c4d33..cad2c15 100644--- a/regress/Makefile+++ b/regress/Makefile@@ -17,7 +17,9 @@ INTVERSION := $(shell echo $$(($$(echo $(VERSION).0 | sed 's/\([[:digit:]]\{1,\}
# Test suite
#
-REGRESS := init-extension repack-setup repack-run error-on-invalid-idx no-error-on-invalid-idx after-schema repack-check nosuper tablespace get_order_by trigger trigger_privs publication+REGRESS := init-extension repack-setup repack-run error-on-invalid-idx \+ no-error-on-invalid-idx after-schema repack-check nosuper \+ tablespace get_order_by trigger trigger_privs publication compare_pkey
USE_PGXS = 1 # use pgxs if not in contrib directory
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/regress/expected/compare_pkey.out b/regress/expected/compare_pkey.outnew file mode 100644index 0000000..65dfddb--- /dev/null+++ b/regress/expected/compare_pkey.out@@ -0,0 +1,88 @@+--+-- repack.get_compare_pkey must emit schema-qualified equality operators, so+-- that repack.repack_apply (which runs with search_path = pg_catalog, pg_temp)+-- works for key types whose "=" operator lives outside pg_catalog.+--+-- A base type with no operators in pg_catalog, reusing the int4 functions.+SET client_min_messages = warning;+CREATE TYPE public.cmpt;+CREATE FUNCTION public.cmpt_in(cstring) RETURNS public.cmpt LANGUAGE internal IMMUTABLE STRICT AS 'int4in';+CREATE FUNCTION public.cmpt_out(public.cmpt) RETURNS cstring LANGUAGE internal IMMUTABLE STRICT AS 'int4out';+CREATE TYPE public.cmpt (INPUT = public.cmpt_in, OUTPUT = public.cmpt_out, LIKE = int4);+CREATE FUNCTION public.cmpt_eq(public.cmpt, public.cmpt) RETURNS boolean LANGUAGE internal IMMUTABLE STRICT AS 'int4eq';+CREATE FUNCTION public.cmpt_cmp(public.cmpt, public.cmpt) RETURNS integer LANGUAGE internal IMMUTABLE STRICT AS 'btint4cmp';+CREATE OPERATOR public.= (LEFTARG = public.cmpt, RIGHTARG = public.cmpt, PROCEDURE = public.cmpt_eq);+CREATE OPERATOR CLASS public.cmpt_ops DEFAULT FOR TYPE public.cmpt USING btree AS+ OPERATOR 3 public.=,+ FUNCTION 1 public.cmpt_cmp(public.cmpt, public.cmpt);+RESET client_min_messages;+-- Table with a primary key of that type+CREATE TABLE cmp_t (id public.cmpt PRIMARY KEY, val text);+-- The key comparison must use a schema-qualified operator+SELECT repack.get_compare_pkey('cmp_t_pkey'::regclass, '$1');+ get_compare_pkey +-----------------------------+ id OPERATOR(public.=) $1.id+(1 row)++-- Apply a log containing an UPDATE and a DELETE with repack.repack_apply,+-- using the SQL generated by the repack.tables view.+SELECT 'cmp_t'::regclass::oid AS oid, indexrelid AS pkid+ FROM pg_index WHERE indrelid = 'cmp_t'::regclass AND indisprimary+\gset+-- Create the objects pg_repack would create: key type, log table, copy table+INSERT INTO cmp_t VALUES ('1', 'x'), ('2', 'y'), ('3', 'z');+SELECT repack.create_index_type(:pkid, :oid);+ create_index_type +-------------------+ +(1 row)++SELECT repack.create_log_table(:oid);+ create_log_table +------------------+ +(1 row)++CREATE TABLE repack.table_:oid (LIKE public.cmp_t);+INSERT INTO repack.table_:oid SELECT * FROM public.cmp_t;+-- Get the generated SQL used to replay the log+SELECT sql_peek, sql_insert, sql_delete, sql_update, sql_pop+ FROM repack.tables WHERE relname = 'public.cmp_t'+\gset+-- Log an UPDATE of row 2 and a DELETE of row 3+INSERT INTO repack.log_:oid (pk, row) VALUES+ (ROW('2')::repack.pk_:oid, ROW('2', 'Y')::public.cmp_t),+ (ROW('3')::repack.pk_:oid, NULL);+-- Replay the log under the pinned search_path+-- Without the schema-qualified operator this fails with:+-- ERROR: operator does not exist: public.cmpt = public.cmpt+SELECT repack.repack_apply(:'sql_peek', :'sql_insert', :'sql_delete', :'sql_update', :'sql_pop', 100);+ repack_apply +--------------+ 2+(1 row)++-- Row 2 is updated, row 3 is deleted, and the log is drained+SELECT * FROM repack.table_:oid ORDER BY id::text;+ id | val +----+-----+ 1 | x+ 2 | Y+(2 rows)++SELECT count(*) AS remaining_log_rows FROM repack.log_:oid;+ remaining_log_rows +--------------------+ 0+(1 row)++-- Cleanup+DROP TABLE repack.log_:oid;+DROP TABLE repack.table_:oid;+DROP TYPE repack.pk_:oid;+DROP TABLE cmp_t;+-- CASCADE also drops the dependent functions, operator and operator class+SET client_min_messages = warning;+DROP TYPE public.cmpt CASCADE;+RESET client_min_messages;diff --git a/regress/sql/compare_pkey.sql b/regress/sql/compare_pkey.sqlnew file mode 100644index 0000000..1b150bf--- /dev/null+++ b/regress/sql/compare_pkey.sql@@ -0,0 +1,66 @@+--+-- repack.get_compare_pkey must emit schema-qualified equality operators, so+-- that repack.repack_apply (which runs with search_path = pg_catalog, pg_temp)+-- works for key types whose "=" operator lives outside pg_catalog.+--++-- A base type with no operators in pg_catalog, reusing the int4 functions.+SET client_min_messages = warning;+CREATE TYPE public.cmpt;+CREATE FUNCTION public.cmpt_in(cstring) RETURNS public.cmpt LANGUAGE internal IMMUTABLE STRICT AS 'int4in';+CREATE FUNCTION public.cmpt_out(public.cmpt) RETURNS cstring LANGUAGE internal IMMUTABLE STRICT AS 'int4out';+CREATE TYPE public.cmpt (INPUT = public.cmpt_in, OUTPUT = public.cmpt_out, LIKE = int4);+CREATE FUNCTION public.cmpt_eq(public.cmpt, public.cmpt) RETURNS boolean LANGUAGE internal IMMUTABLE STRICT AS 'int4eq';+CREATE FUNCTION public.cmpt_cmp(public.cmpt, public.cmpt) RETURNS integer LANGUAGE internal IMMUTABLE STRICT AS 'btint4cmp';+CREATE OPERATOR public.= (LEFTARG = public.cmpt, RIGHTARG = public.cmpt, PROCEDURE = public.cmpt_eq);+CREATE OPERATOR CLASS public.cmpt_ops DEFAULT FOR TYPE public.cmpt USING btree AS+ OPERATOR 3 public.=,+ FUNCTION 1 public.cmpt_cmp(public.cmpt, public.cmpt);++RESET client_min_messages;++-- Table with a primary key of that type+CREATE TABLE cmp_t (id public.cmpt PRIMARY KEY, val text);++-- The key comparison must use a schema-qualified operator+SELECT repack.get_compare_pkey('cmp_t_pkey'::regclass, '$1');++-- Apply a log containing an UPDATE and a DELETE with repack.repack_apply,+-- using the SQL generated by the repack.tables view.+SELECT 'cmp_t'::regclass::oid AS oid, indexrelid AS pkid+ FROM pg_index WHERE indrelid = 'cmp_t'::regclass AND indisprimary+\gset++-- Create the objects pg_repack would create: key type, log table, copy table+INSERT INTO cmp_t VALUES ('1', 'x'), ('2', 'y'), ('3', 'z');+SELECT repack.create_index_type(:pkid, :oid);+SELECT repack.create_log_table(:oid);+CREATE TABLE repack.table_:oid (LIKE public.cmp_t);+INSERT INTO repack.table_:oid SELECT * FROM public.cmp_t;++-- Get the generated SQL used to replay the log+SELECT sql_peek, sql_insert, sql_delete, sql_update, sql_pop+ FROM repack.tables WHERE relname = 'public.cmp_t'+\gset+-- Log an UPDATE of row 2 and a DELETE of row 3+INSERT INTO repack.log_:oid (pk, row) VALUES+ (ROW('2')::repack.pk_:oid, ROW('2', 'Y')::public.cmp_t),+ (ROW('3')::repack.pk_:oid, NULL);++-- Replay the log under the pinned search_path+-- Without the schema-qualified operator this fails with:+-- ERROR: operator does not exist: public.cmpt = public.cmpt+SELECT repack.repack_apply(:'sql_peek', :'sql_insert', :'sql_delete', :'sql_update', :'sql_pop', 100);+-- Row 2 is updated, row 3 is deleted, and the log is drained+SELECT * FROM repack.table_:oid ORDER BY id::text;+SELECT count(*) AS remaining_log_rows FROM repack.log_:oid;++-- Cleanup+DROP TABLE repack.log_:oid;+DROP TABLE repack.table_:oid;+DROP TYPE repack.pk_:oid;+DROP TABLE cmp_t;+-- CASCADE also drops the dependent functions, operator and operator class+SET client_min_messages = warning;+DROP TYPE public.cmpt CASCADE;+RESET client_min_messages;