From: ritojoao (@ritojoao) <noreply+ritojoao@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] PR #493: Use format() and pg_catalog-qualified catalog tables in pg_repack.sql.in
Date: Thu, 12 Nov 2026 00:48:55 +0000
Message-ID: <gh-reorg-pg_repack-493@github.com> (raw)
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.patchdiff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.inindex 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;
$$