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.patchdiff --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