pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
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.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; $$

view thread (21+ messages)  latest in thread

Message-ID: <gh-reorg-pg_repack-493@github.com>
Permalink:  ../gh-reorg-pg_repack-493@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