pg.ddx.io reorg/pg_repack GitHub issues and pull requests
help / color / mirror / Atom feed[reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
6+ messages / 2 participants
[nested] [flat]
* [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
@ 2026-11-16 14:18 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
0 siblings, 0 replies; 6+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-16 14:18 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
`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.patch
diff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.in
index 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/Makefile
index 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.out
new file mode 100644
index 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.sql
new file mode 100644
index 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;
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
@ 2026-11-16 14:18 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-16 14:18 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur requested a review.
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
@ 2026-11-16 14:18 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-16 14:18 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur requested a review.
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] PR #495 (approved): Use schema-qualified equality operators in `get_compare_pkey`
@ 2026-11-16 14:25 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: andreasscherbaum (@andreasscherbaum) @ 2026-11-16 14:25 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
@ 2026-11-16 14:42 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-16 14:42 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur merged this (commit e8e903de8408).
^ permalink raw [nested|flat] 6+ messages in thread
* Re: [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey`
@ 2026-11-16 14:42 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
4 siblings, 0 replies; 6+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-16 14:42 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur closed this.
^ permalink raw [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-11-16 14:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-16 14:18 [reorg/pg_repack] PR #495: Use schema-qualified equality operators in `get_compare_pkey` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-16 14:18 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-16 14:18 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-16 14:25 ` Re: [reorg/pg_repack] PR #495 (approved): Use schema-qualified equality operators in `get_compare_pkey` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
2026-11-16 14:42 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-16 14:42 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
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