pg.ddx.io reorg/pg_repack GitHub issues and pull requests
help / color / mirror / Atom feed[reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
21+ messages / 3 participants
[nested] [flat]
* [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 00:48 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
0 siblings, 0 replies; 21+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-12 00:48 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Hello, and thank you for maintaining pg_repack.
This PR refactors `lib/pg_repack.sql.in` as described in the issue:
- Dynamic SQL is now built with `format()` (`%s` for values, `%I` for identifiers) instead of string concatenation.
- Every catalog table is schema-qualified with `pg_catalog` (`pg_class`, `pg_attribute`, `pg_index`, `pg_type`, `pg_namespace`, `pg_tablespace`, `pg_trigger`). `pg_database` and `pg_tablespace` in the `repack.tables` view were already qualified in one place and are now consistent.
The whole file was small enough to do in one commit, so I covered all functions and both views (`repack.primary_keys`, `repack.tables`). Only `lib/pg_repack.sql.in` changes. Function names, signatures and the C code are untouched, so I did not bump the version.
This is meant to be a refactor that keeps the generated SQL the same. I tested it on PostgreSQL master (20devel) like this:
- `make installcheck` passes 13/13 on current master and 13/13 with this change.
- I created a few tables (mixed-case names, a schema with a space, dropped columns, storage options, a composite unique key, a clustered index, and a table without a primary key) and compared every SQL string produced by `repack.tables`, `get_storage_param` and `get_create_index_type`. The output is identical before and after, apart from object OIDs.
- In the generated SQL there were 28 unqualified catalog table references before and 0 after.
- I also tried a schema placed first in `search_path` with its own `pg_class`, `pg_attribute`, `pg_index` and `pg_trigger` tables. Before the change the helper functions failed or returned wrong results (for example an empty storage parameter list). After the change they return the correct results.
About NULL: with `||` a NULL input makes the whole string NULL, while `format()` prints an empty string. I kept the old behaviour where an input can be NULL. In `repack.tables`, `create_pktype`, `sql_delete` and `sql_update` are wrapped in `CASE WHEN PK.indexrelid IS NOT NULL THEN format(...) END`, so they stay NULL for tables without a usable unique key. In `get_alter_col_storage`, each `WHEN` branch calls `format()` itself, so an unexpected `attstorage` still gives a NULL element that is skipped. `create_index_type` still uses `||` for the type name. The other `format()` calls are in STRICT functions, behind a `WHERE` guard, or get inputs that are not NULL in practice (OIDs, `coalesce()`). One small difference: calling `repack.create_table()` directly with a NULL tablespace still fails, but with a different error message than before. The C code never passes NULL there. I checked a table without a primary key: those columns are NULL before and after, and the generated SQL is the same as before.
I did not touch the functions or the quoting inside the C code, as that seems like a separate topic (see #368). I am happy to adjust the style or split the change if you prefer.
Thank you for your time and for reviewing!
Tested only on PostgreSQL master (20devel); not tested on other versions, I hope CI covers those.
Fixes #369
inline patch 1: lib/pg_repack.sql.in (+94/-75 lines) download inline-1.patch
diff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.in
index a977c17..5dc1a03 100644
--- a/lib/pg_repack.sql.in
+++ b/lib/pg_repack.sql.in
@@ -32,11 +32,11 @@ LANGUAGE sql STABLE STRICT SET search_path to 'pg_catalog';
CREATE FUNCTION repack.get_index_columns(oid) RETURNS text AS
$$
SELECT coalesce(string_agg(quote_literal(attname), ', '), '')
- FROM pg_attribute,
+ FROM pg_catalog.pg_attribute,
(SELECT indrelid,
indkey,
generate_series(0, indnatts-1) AS i
- FROM pg_index
+ FROM pg_catalog.pg_index
WHERE indexrelid = $1
) AS keys
WHERE attrelid = indrelid
@@ -51,10 +51,12 @@ LANGUAGE C STABLE STRICT;
CREATE FUNCTION repack.create_log_table(oid) RETURNS void AS
$$
BEGIN
- EXECUTE 'CREATE TABLE repack.log_' || $1 ||
- ' (id bigserial PRIMARY KEY,' ||
- ' pk repack.pk_' || $1 || ',' ||
- ' row ' || repack.oid2text($1) || ')';
+ EXECUTE format(
+ 'CREATE TABLE repack.log_%s ('
+ 'id bigserial PRIMARY KEY, '
+ 'pk repack.pk_%s, '
+ 'row %s)',
+ $1, $1, repack.oid2text($1));
END
$$
LANGUAGE plpgsql;
@@ -62,11 +64,14 @@ LANGUAGE plpgsql;
CREATE FUNCTION repack.create_table(oid, name) RETURNS void AS
$$
BEGIN
- EXECUTE 'CREATE TABLE repack.table_' || $1 ||
- ' WITH (' || repack.get_storage_param($1) || ') ' ||
- ' TABLESPACE ' || quote_ident($2) ||
- ' AS SELECT ' || repack.get_columns_for_create_as($1) ||
- ' FROM ONLY ' || repack.oid2text($1) || ' WITH NO DATA';
+ EXECUTE format(
+ 'CREATE TABLE repack.table_%s WITH (%s) TABLESPACE %I '
+ 'AS SELECT %s FROM ONLY %s WITH NO DATA',
+ $1,
+ repack.get_storage_param($1),
+ $2,
+ repack.get_columns_for_create_as($1),
+ repack.oid2text($1));
END
$$
LANGUAGE plpgsql;
@@ -81,14 +86,15 @@ LANGUAGE plpgsql;
CREATE FUNCTION repack.get_create_index_type(oid, name) RETURNS text AS
$$
- SELECT 'CREATE TYPE ' || $2 || ' AS (' ||
- coalesce(string_agg(quote_ident(attname) || ' ' ||
- pg_catalog.format_type(atttypid, atttypmod), ', '), '') || ')'
- FROM pg_attribute,
+ SELECT format('CREATE TYPE %s AS (%s)', $2,
+ coalesce(string_agg(
+ format('%I %s', attname, pg_catalog.format_type(atttypid, atttypmod)),
+ ', '), ''))
+ FROM pg_catalog.pg_attribute,
(SELECT indrelid,
indkey,
generate_series(0, indnatts-1) AS i
- FROM pg_index
+ FROM pg_catalog.pg_index
WHERE indexrelid = $1
) AS keys
WHERE attrelid = indrelid
@@ -99,27 +105,29 @@ LANGUAGE sql STABLE STRICT;
CREATE FUNCTION repack.get_create_trigger(relid oid, pkid oid)
RETURNS text AS
$$
- SELECT 'CREATE TRIGGER repack_trigger' ||
- ' AFTER INSERT OR DELETE OR UPDATE ON ' || repack.oid2text($1) ||
- ' FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger(' ||
- repack.get_index_columns($2) || ')';
+ SELECT format(
+ 'CREATE TRIGGER repack_trigger'
+ ' AFTER INSERT OR DELETE OR UPDATE ON %s'
+ ' FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger(%s)',
+ repack.oid2text($1), repack.get_index_columns($2));
$$
LANGUAGE sql STABLE STRICT;
CREATE FUNCTION repack.get_enable_trigger(relid oid)
RETURNS text AS
$$
- SELECT 'ALTER TABLE ' || repack.oid2text($1) ||
- ' ENABLE ALWAYS TRIGGER repack_trigger';
+ SELECT format('ALTER TABLE %s ENABLE ALWAYS TRIGGER repack_trigger',
+ repack.oid2text($1));
$$
LANGUAGE sql STABLE STRICT;
CREATE FUNCTION repack.get_assign(oid, text) RETURNS text AS
$$
- SELECT '(' || coalesce(string_agg(quote_ident(attname), ', '), '') ||
- ') = (' || $2 || '.' ||
- coalesce(string_agg(quote_ident(attname), ', ' || $2 || '.'), '') || ')'
- FROM (SELECT attname FROM pg_attribute
+ SELECT format('(%s) = (%s.%s)',
+ coalesce(string_agg(quote_ident(attname), ', '), ''),
+ $2,
+ coalesce(string_agg(quote_ident(attname), format(', %s.', $2)), ''))
+ FROM (SELECT attname FROM pg_catalog.pg_attribute
WHERE attrelid = $1 AND attnum > 0 AND NOT attisdropped
ORDER BY attnum) tmp;
$$
@@ -128,14 +136,15 @@ LANGUAGE sql STABLE STRICT;
CREATE FUNCTION repack.get_compare_pkey(oid, text)
RETURNS text AS
$$
- SELECT '(' || coalesce(string_agg(quote_ident(attname), ', '), '') ||
- ') = (' || $2 || '.' ||
- coalesce(string_agg(quote_ident(attname), ', ' || $2 || '.'), '') || ')'
- FROM pg_attribute,
+ 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,
indkey,
generate_series(0, indnatts-1) AS i
- FROM pg_index
+ FROM pg_catalog.pg_index
WHERE indexrelid = $1
) AS keys
WHERE attrelid = indrelid
@@ -150,10 +159,10 @@ CREATE FUNCTION repack.get_columns_for_create_as(oid)
$$
SELECT coalesce(string_agg(c, ','), '') FROM (SELECT
CASE WHEN attisdropped
- THEN 'NULL::integer AS ' || quote_ident(attname)
+ THEN format('NULL::integer AS %I', attname)
ELSE quote_ident(attname)
END AS c
-FROM pg_attribute
+FROM pg_catalog.pg_attribute
WHERE attrelid = $1 AND attnum > 0 ORDER BY attnum
) AS COL
$$
@@ -165,12 +174,12 @@ CREATE FUNCTION repack.get_drop_columns(oid, text)
RETURNS text AS
$$
SELECT
- 'ALTER TABLE ' || $2 || ' ' || array_to_string(dropped_columns, ', ')
+ format('ALTER TABLE %s %s', $2, array_to_string(dropped_columns, ', '))
FROM (
SELECT
- array_agg('DROP COLUMN ' || quote_ident(attname)) AS dropped_columns
+ array_agg(format('DROP COLUMN %I', attname)) AS dropped_columns
FROM (
- SELECT * FROM pg_attribute
+ SELECT * FROM pg_catalog.pg_attribute
WHERE attrelid = $1 AND attnum > 0 AND attisdropped
ORDER BY attnum
) T
@@ -191,24 +200,24 @@ SELECT string_agg(param, ', ')
FROM (
-- table storage parameter
SELECT unnest(reloptions) as param
- FROM pg_class
+ FROM pg_catalog.pg_class
WHERE oid = $1
UNION ALL
-- TOAST table storage parameter
- SELECT ('toast.' || unnest(reloptions)) as param
+ SELECT format('toast.%s', unnest(reloptions)) as param
FROM (
- SELECT reltoastrelid from pg_class where oid = $1
+ SELECT reltoastrelid from pg_catalog.pg_class where oid = $1
) as t,
- pg_class as c
+ pg_catalog.pg_class as c
WHERE c.oid = t.reltoastrelid
UNION ALL
-- table oid
- SELECT 'oids = ' ||
+ SELECT format('oids = %s',
CASE WHEN relhasoids
THEN 'true'
ELSE 'false'
- END
- FROM pg_class
+ END)
+ FROM pg_catalog.pg_class
WHERE oid = $1
) as t
@@ -219,22 +228,22 @@ LANGUAGE sql STABLE STRICT;
CREATE FUNCTION repack.get_alter_col_storage(oid)
RETURNS text AS
$$
- SELECT 'ALTER TABLE repack.table_' || $1 || array_to_string(column_storage, ',')
+ SELECT format('ALTER TABLE repack.table_%s%s', $1, array_to_string(column_storage, ','))
FROM (
SELECT
- array_agg(' ALTER ' || quote_ident(attname) ||
+ array_agg(
CASE attstorage
- WHEN 'p' THEN ' SET STORAGE PLAIN'
- WHEN 'm' THEN ' SET STORAGE MAIN'
- WHEN 'e' THEN ' SET STORAGE EXTERNAL'
- WHEN 'x' THEN ' SET STORAGE EXTENDED'
+ WHEN 'p' THEN format(' ALTER %I SET STORAGE PLAIN', attname)
+ WHEN 'm' THEN format(' ALTER %I SET STORAGE MAIN', attname)
+ WHEN 'e' THEN format(' ALTER %I SET STORAGE EXTERNAL', attname)
+ WHEN 'x' THEN format(' ALTER %I SET STORAGE EXTENDED', attname)
END) AS column_storage
FROM (
SELECT *
- FROM pg_attribute a
- JOIN pg_type t on t.oid = atttypid
- JOIN pg_class r on r.oid = a.attrelid
- JOIN pg_namespace s on s.oid = r.relnamespace
+ FROM pg_catalog.pg_attribute a
+ JOIN pg_catalog.pg_type t on t.oid = atttypid
+ JOIN pg_catalog.pg_class r on r.oid = a.attrelid
+ JOIN pg_catalog.pg_namespace s on s.oid = r.relnamespace
WHERE typstorage <> attstorage
AND attrelid = $1
AND attnum > 0
@@ -252,13 +261,13 @@ BEGIN
IF current_setting('server_version_num')::int >= 110000 THEN
CREATE VIEW repack.primary_keys AS
SELECT indrelid, min(indexrelid) AS indexrelid
- FROM (SELECT indrelid, indexrelid FROM pg_index
+ FROM (SELECT indrelid, indexrelid FROM pg_catalog.pg_index
WHERE indisunique
AND indisvalid
AND indpred IS NULL
AND 0 <> ALL(indkey)
AND NOT EXISTS(
- SELECT 1 FROM pg_attribute
+ SELECT 1 FROM pg_catalog.pg_attribute
WHERE attrelid = indrelid
-- indkey is 0-based int2vector
AND attnum = ANY(indkey[0:indnkeyatts - 1])
@@ -268,13 +277,13 @@ BEGIN
ELSE
CREATE VIEW repack.primary_keys AS
SELECT indrelid, min(indexrelid) AS indexrelid
- FROM (SELECT indrelid, indexrelid FROM pg_index
+ FROM (SELECT indrelid, indexrelid FROM pg_catalog.pg_index
WHERE indisunique
AND indisvalid
AND indpred IS NULL
AND 0 <> ALL(indkey)
AND NOT EXISTS(
- SELECT 1 FROM pg_attribute
+ SELECT 1 FROM pg_catalog.pg_attribute
WHERE attrelid = indrelid
AND attnum = ANY(indkey)
AND NOT attnotnull)
@@ -289,41 +298,51 @@ CREATE VIEW repack.tables AS
R.oid AS relid,
R.reltoastrelid AS reltoastrelid,
CASE WHEN R.reltoastrelid = 0 THEN 0 ELSE (
- SELECT indexrelid FROM pg_index
+ SELECT indexrelid FROM pg_catalog.pg_index
WHERE indrelid = R.reltoastrelid
AND indisvalid) END AS reltoastidxid,
N.nspname AS schemaname,
PK.indexrelid AS pkid,
CK.indexrelid AS ckid,
- 'SELECT repack.create_index_type(' || PK.indexrelid || ',' || R.oid || ')' AS create_pktype,
- 'SELECT repack.create_log_table(' || R.oid || ')' AS create_log,
+ CASE WHEN PK.indexrelid IS NOT NULL
+ THEN format('SELECT repack.create_index_type(%s,%s)', PK.indexrelid, R.oid)
+ END AS create_pktype,
+ format('SELECT repack.create_log_table(%s)', R.oid) AS create_log,
repack.get_create_trigger(R.oid, PK.indexrelid) AS create_trigger,
repack.get_enable_trigger(R.oid) as enable_trigger,
'SELECT repack.create_table($1, $2)'::text AS create_table,
coalesce(S.spcname, S2.spcname) AS tablespace_orig,
- 'INSERT INTO repack.table_' || R.oid || ' SELECT ' || repack.get_columns_for_create_as(R.oid) || ' FROM ONLY ' || repack.oid2text(R.oid) AS copy_data,
+ format('INSERT INTO repack.table_%s SELECT %s FROM ONLY %s',
+ R.oid, repack.get_columns_for_create_as(R.oid), repack.oid2text(R.oid)) AS copy_data,
repack.get_alter_col_storage(R.oid) AS alter_col_storage,
- repack.get_drop_columns(R.oid, 'repack.table_' || R.oid) AS drop_columns,
- 'DELETE FROM repack.log_' || R.oid AS delete_log,
- 'LOCK TABLE ' || repack.oid2text(R.oid) || ' IN ACCESS EXCLUSIVE MODE' AS lock_table,
+ repack.get_drop_columns(R.oid, format('repack.table_%s', R.oid)) AS drop_columns,
+ format('DELETE FROM repack.log_%s', R.oid) AS delete_log,
+ format('LOCK TABLE %s IN ACCESS EXCLUSIVE MODE', repack.oid2text(R.oid)) AS lock_table,
repack.get_order_by(CK.indexrelid, R.oid) AS ckey,
- 'SELECT * FROM repack.log_' || R.oid || ' ORDER BY id LIMIT $1' AS sql_peek,
- 'INSERT INTO repack.table_' || R.oid || ' VALUES ($1.*)' AS sql_insert,
- 'DELETE FROM repack.table_' || R.oid || ' WHERE ' || repack.get_compare_pkey(PK.indexrelid, '$1') AS sql_delete,
- 'UPDATE repack.table_' || R.oid || ' SET ' || repack.get_assign(R.oid, '$2') || ' WHERE ' || repack.get_compare_pkey(PK.indexrelid, '$1') AS sql_update,
- 'DELETE FROM repack.log_' || R.oid || ' WHERE id IN (' AS sql_pop
- FROM pg_class R
- LEFT JOIN pg_class T ON R.reltoastrelid = T.oid
+ format('SELECT * FROM repack.log_%s ORDER BY id LIMIT $1', R.oid) AS sql_peek,
+ format('INSERT INTO repack.table_%s VALUES ($1.*)', R.oid) AS sql_insert,
+ CASE WHEN PK.indexrelid IS NOT NULL
+ THEN format('DELETE FROM repack.table_%s WHERE %s',
+ R.oid, repack.get_compare_pkey(PK.indexrelid, '$1'))
+ END AS sql_delete,
+ CASE WHEN PK.indexrelid IS NOT NULL
+ THEN format('UPDATE repack.table_%s SET %s WHERE %s',
+ R.oid, repack.get_assign(R.oid, '$2'),
+ repack.get_compare_pkey(PK.indexrelid, '$1'))
+ END AS sql_update,
+ format('DELETE FROM repack.log_%s WHERE id IN (', R.oid) AS sql_pop
+ FROM pg_catalog.pg_class R
+ LEFT JOIN pg_catalog.pg_class T ON R.reltoastrelid = T.oid
LEFT JOIN repack.primary_keys PK
ON R.oid = PK.indrelid
- LEFT JOIN (SELECT CKI.* FROM pg_index CKI, pg_class CKT
+ LEFT JOIN (SELECT CKI.* FROM pg_catalog.pg_index CKI, pg_catalog.pg_class CKT
WHERE CKI.indisvalid
AND CKI.indexrelid = CKT.oid
AND CKI.indisclustered
AND CKT.relam = 403) CK
ON R.oid = CK.indrelid
- LEFT JOIN pg_namespace N ON N.oid = R.relnamespace
- LEFT JOIN pg_tablespace S ON S.oid = R.reltablespace
+ LEFT JOIN pg_catalog.pg_namespace N ON N.oid = R.relnamespace
+ LEFT JOIN pg_catalog.pg_tablespace S ON S.oid = R.reltablespace
CROSS JOIN (SELECT S2.spcname
FROM pg_catalog.pg_database D
JOIN pg_catalog.pg_tablespace S2 ON S2.oid = D.dattablespace
@@ -344,7 +363,7 @@ SET search_path = pg_catalog, pg_temp;
CREATE FUNCTION repack.conflicted_triggers(oid) RETURNS SETOF name AS
$$
-SELECT tgname FROM pg_trigger
+SELECT tgname FROM pg_catalog.pg_trigger
WHERE tgrelid = $1 AND tgname = 'repack_trigger'
ORDER BY tgname;
$$
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 09:34 "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: CyberDem0n (@CyberDem0n) @ 2026-11-12 09:34 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@ritojoao since you started working on it, I think it would be very valuable to also set safe search_path on every affected function.
That is, `SET search_path = pg_catalog, pg_temp`.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 10:18 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-12 10:18 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Hi @CyberDem0n, thank you for the review.
I pushed two commits (the second one fixes a problem the first one caused).
1. `SET search_path = pg_catalog, pg_temp` is now on every function in `lib/pg_repack.sql.in` that did not have it (`oid2text` had `pg_catalog` only and now has `pg_catalog, pg_temp`): the SQL and PL/pgSQL helpers (`oid2text`, `get_*`, `create_*`, `conflicted_triggers`) and the C functions (`repack_apply`, `repack_swap`, `repack_drop`, `repack_index_swap`, `repack_indexdef`, `disable_autovacuum`, `get_order_by`, `get_table_and_inheritors`). `repack_trigger` already had it. Only `version()` and `version_sql()` are left without it, because they touch nothing. With the SET, every name in these functions now resolves through `pg_catalog`, whatever the caller's search_path is. I also schema-qualified one `pg_class` lookup done through SPI in `repack_index_swap()`.
2. While testing I found that the SET broke `repack.get_order_by()` for a non-default btree operator class that lives outside `pg_catalog`. `pg_get_indexdef()` then prints the class with its schema (for example `public.my_ops`), and the old code looked that string up by name and failed with `cache lookup failed for opclass 0`. The second commit reads the operator family and input type from the index itself instead of looking up the name. I added a regression case for it in `get_order_by`.
What I ran (PostgreSQL 18, local server):
- With only the first commit, `pg_repack -t t` on a table clustered on an index that uses a custom operator class in `public` fails with `cache lookup failed for opclass 0`. Without either commit it works, and with both commits it works again.
- `make installcheck`: all 13 tests pass with both commits, including the new case.
- Hostile setup: database search_path `evil, pg_catalog, public`, where `evil` has fake `quote_ident`, `quote_literal`, `format_type`, `array_to_string`, a table `h`, and tables `pg_class`, `pg_index` and `pg_attribute`. I called the helpers directly, before and after. Before, `get_index_columns()` returned `evil, evil` and `get_create_trigger()` produced `repack_trigger(evil)`. After, they return the real column list. `get_order_by()` and `repack_indexdef()` were not affected by the fake objects in either run. `repack_indexdef()` now prints the operator class with its schema (`public.hostile_int4_ops`), which is still valid for the client.
- `pg_proc.proconfig` shows the setting on every function in the `repack` schema except `version` and `version_sql`.
Not tested: PostgreSQL versions other than 18, and `repack_index_swap()` with a hostile search_path (not run on its own, only as part of installcheck).
Please tell me if you would prefer to leave the C functions out of this PR.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 10:48 "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: CyberDem0n (@CyberDem0n) @ 2026-11-12 10:48 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@ritojoao I am not a maintainer, and don't know what they would prefer.
What caught my eye is unqualified format() calls, which could be easily shadowed by creating function with the same name and better fitted arguments (for example format(text, text)) in lets say public schema. That opens up a very simple way for SQL injections.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 11:01 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-12 11:01 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Thank you, @CyberDem0n, that is a good point, and I tested it.
I created `public.format(text, text)` (and a few overloads, also in a second schema for setup B) that return a marker string, then ran the helpers and the real `pg_repack` client against a table, on PostgreSQL 18. I tried three setups: the fake function created after `CREATE EXTENSION` with two different search_path orders (A and B), and the fake function created before `CREATE EXTENSION pg_repack` (C).
- Before this PR: the fake `format()` was picked in all three setups, and the generated SQL was changed.
- With the first two commits (`SET search_path = pg_catalog, pg_temp` on the functions): A and B were clean. C was still affected, because the view `repack.tables` has no search_path of its own and binds `format()` when the extension is created. In that setup the client sent the fake SQL, the lock step failed with a syntax error, and the table was not repacked (no swap happened).
- I pushed a third commit that writes `pg_catalog.format()` in the `repack.tables` view, plus `pg_catalog.current_database()`, `pg_catalog.min()` in `repack.primary_keys`, and `pg_catalog.current_setting()` in the `DO` block. With it, A, B and C show no `format()` shadowing in my test, and the client repacks the table in B and C. `make installcheck` passes (13 tests).
Not covered: operators in the view (`=`, `LIKE`) are still resolved through search_path, and I only tested PostgreSQL 18 with a simple table.
A side note from the same test: in setup C the client printed a WARNING and still exited with code 0, because a failed lock in `repack_one_table()` goes to cleanup without changing the exit code. That is outside this PR, but I can open a separate issue if it is useful.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 11:01 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-12 11:01 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
I was working on using `format()` in this PR https://github.com/reorg/pg_repack/pull/482/commits. Although it wasn't merged yet, since it has much bigger changes than just using `format()`.
I think there is no need to use pg_catalog-qualified names (although I created https://github.com/reorg/pg_repack/issues/369 which has this point) since pg_repack sets `search_path = pg_catalog, pg_temp, public` on every connection:
https://github.com/reorg/pg_repack/blob/eaa601410ab2331dde45f7758fd248bf15784709/bin/pg_repack.c#L60...
https://github.com/reorg/pg_repack/blob/eaa601410ab2331dde45f7758fd248bf15784709/bin/pgut/pgut.c#L55...
https://github.com/reorg/pg_repack/blob/eaa601410ab2331dde45f7758fd248bf15784709/bin/pgut/pgut-fe.c#...
Also as Alexander pointed out functions like `format()`, `coalesce()`, `string_agg()` as well as operators (`=`, `>`) are also not used with fully qualified names.
It might make sense to search_path on every function if we want to make calls of that function by other clients other than by pg_repack itself.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 12:51 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-12 12:51 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Thank you, @za-arthur, for the context and for the links.
You are right that the client sets `search_path = pg_catalog, pg_temp, public` on its connection, and in my test that covered case B. The one case where it did not help is setup C from my previous comment: the fake `public.format()` exists before `CREATE EXTENSION pg_repack`. The view `repack.tables` binds `format()` when the extension is created, so the client's search_path at run time does not change it. In that run the real `pg_repack` client sent the fake SQL, the lock step failed with a warning, and the run still ended with exit code 0 (`format-client-after.log`). With the third commit it did not. The commit only qualifies the functions; operators in the view are still resolved by search_path. I only tested this on PG18 with a simple table, so please treat it as one data point.
I also see that #482 changes `lib/pg_repack.sql.in` too. I am happy to follow what the maintainers prefer: keep the PR as it is, drop only the third commit, or rebase it on top of #482 after that one is merged, so the two do not conflict.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 15:35 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-12 15:35 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur applied label "enhancement".
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 20:33 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-12 20:33 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
I'm going to merge the PR with the following follow up issues I identified so far:
- `repack_apply()` can be broken in some rare cases with the error since it won't be able to find index operators (https://github.com/reorg/pg_repack/pull/495):
```
ERROR: query failed: ERROR: operator does not exist: ext.hstore = ext.hstore
QUERY: UPDATE repack.table_24121 SET (id, v) = ($2.id, $2.v) WHERE (id) = ($1.id)
```
- ~~Add `schema = repack` to `pg_repack.control`: postgres will install extension with limited `search_path`~~
- Consider limiting installation schema by adding `schema = pg_catalog` to `pg_repack.control`
- `SET LOCAL search_path = pg_catalog, pg_temp;` at the beginning of the installation script
- Consider removing `public` from `search_path` in `pg_repack`'s client code in `bin/pg_repack.c`, `bin/pgut/pgut.c`, `bin/pgut/pgut-fe.c`
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-12 22:56 "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: ritojoao (@ritojoao) @ 2026-11-12 22:56 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Thank you, @za-arthur. Before you merge, I found one regression in this PR that I think should be fixed first.
When the primary key type lives in `public` (I tested `hstore` installed in `public`), pg_repack works on master but fails with this PR if there are concurrent UPDATEs or DELETEs during the run:
```
ERROR: operator does not exist: public.hstore = public.hstore
```
The cause is the new `SET search_path = pg_catalog, pg_temp` on `repack_apply()`. The `=` in the generated `WHERE (id) = ($1.id)` no longer finds the operator in `public`. It is the same root cause as your first follow-up (extension in another schema), which also fails on master when I call `repack_apply()` by hand.
I tested a fix in `get_compare_pkey()`. It builds `col OPERATOR(schema.=) $N.col` from the btree equality operator of the index opclass. On PG 16 it fixes both cases with a manual `repack_apply()` call, and it fixes the `public` case in a full pg_repack run with concurrent writes. `make installcheck` still passes. The current tests do not cover this, so I will add a regression test and check more PostgreSQL versions.
Would you like me to push the fix to this PR, or keep this PR as it is and send the fix as a separate PR?
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 08:05 "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: CyberDem0n (@CyberDem0n) @ 2026-11-13 08:05 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
> Add schema = repack to pg_repack.control: postgres will install extension with limited search_path
@za-arthur please don't, it will increase attack surface because users can pre-create repack schema.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 10:28 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 10:28 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@ritojoao that regression is described in my first point:
> repack_apply() can be broken in some rare cases with the error since it won't be able to find index operators
The issue existed before, but it didn't affect index operators on `public` before. And I think it is fine to work on that on a separate PR.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 10:40 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 10:40 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@CyberDem0n I updated the plan
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 10:55 "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: CyberDem0n (@CyberDem0n) @ 2026-11-13 10:55 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur
> `SET LOCAL search_path = pg_catalog, pg_temp;` at the beginning of the installation script
You'll get `WARNING: SET LOCAL can only be used in transaction blocks`, and `search_path` will remain unchanged.
PG already does enough to harden search_path for extension creation/update: https://github.com/postgres/postgres/blob/77bba5454f96e626651449780e1614892557c1bc/src/backend/comma...
The only we need to do - use `schema = pg_catalog` in pg_repack.control. This schema already exists, and owned by bootstrap superuser.
> removing `public` from `search_path` in pg_repack's client code
Yes, this is a good one.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:21 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 11:21 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
> PG already does enough to harden search_path for extension creation/update: https://github.com/postgres/postgres/blob/77bba5454f96e626651449780e1614892557c1bc/src/backend/comma...
If I understand correctly this hardening, Postgres adds a schema name, where pg_repack is installed to, to search_path. So if it is installed into `public` then search_path will be `public, pg_temp`.
> The only we need to do - use schema = pg_catalog in pg_repack.control. This schema already exists, and owned by bootstrap superuser.
I think we don't need to do that, since it will force installation of pg_repack into `pg_repack`, which might not work for everybody.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:27 "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: CyberDem0n (@CyberDem0n) @ 2026-11-13 11:27 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
https://github.com/reorg/pg_repack/blob/master/lib/pg_repack.sql.in#L9 creates repack schema without IF EXISTS, and this is the best hardening. Using `schema = pg_catalog` doesn't change it.
For example, pg_cron does the same in pg_cron.control (so as a few more extensions).
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:37 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 11:37 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@CyberDem0n I think it isn't necessarily good decision that pg_cron put `schema = pg_catalog`. Because of that pg_cron can be installed only in `pg_catalog`:
```
select n.nspname, e.extname from pg_extension e
join pg_namespace n on n.oid = e.extnamespace
where extname in ('pg_cron', 'pg_repack')
| nspname | extname |
| ---------- | --------- |
| public | pg_repack |
| pg_catalog | pg_cron |
```
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:40 "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: CyberDem0n (@CyberDem0n) @ 2026-11-13 11:40 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Yes, but neither pg_cron nor pg_repack create any objects in extension schema.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:45 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 11:45 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Okay, added this to the plan for now.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:47 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 11:47 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur closed this.
^ permalink raw [nested|flat] 21+ messages in thread
* Re: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
@ 2026-11-13 11:47 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
19 siblings, 0 replies; 21+ messages in thread
From: za-arthur (@za-arthur) @ 2026-11-13 11:47 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur merged this (commit 34eb5371c5b7).
^ permalink raw [nested|flat] 21+ messages in thread
end of thread, other threads:[~2026-11-13 11:47 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-12 00:48 [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-12 09:34 ` "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
2026-11-12 10:18 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-12 10:48 ` "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
2026-11-12 11:01 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-12 11:01 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-12 12:51 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-12 15:35 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-12 20:33 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-12 22:56 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-13 08:05 ` "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
2026-11-13 10:28 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-13 10:40 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-13 10:55 ` "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
2026-11-13 11:21 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-13 11:27 ` "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
2026-11-13 11:37 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-13 11:40 ` "CyberDem0n (@CyberDem0n)" <noreply+CyberDem0n@github.com>
2026-11-13 11:45 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-13 11:47 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-11-13 11:47 ` "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