pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
From: za-arthur (@za-arthur) <noreply+za-arthur@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] PR #496: Harden client code queries and generated SQL against search_path
Date: Fri, 20 Nov 2026 10:22:17 +0000
Message-ID: <gh-reorg-pg_repack-496@github.com> (raw)

Follow-up to #493. The client code connects with `search_path = pg_catalog, pg_temp, public` and some of the SQL it sends still resolved names through that path.

**Changes**:
1. Use exact `oid` comparisons for relation lookups in the client. Queries compared `oid` columns with `regclass` values, such as `relid = $1::regclass` and `classid = 'pg_class'::regclass.pg_catalog` has no `(oid, regclass)` operator, so Postgres picked the operator by implicit cast through the search_path.
2. Fix `ORDER BY` generation for non-default operator classes. `repack_get_order_by()` emitted a bare operator name (`USING <`), which was resolved again through the search_path. It now emits `USING OPERATOR(schema.op)`.
3. Quote the schema name in the index cleanup statement. The cleanup `DROP INDEX CONCURRENTLY` now builds the schema identifier with `PQescapeIdentifier()`.

inline patch 1: bin/pg_repack.c, lib/repack.c, regress/expected/get_order_by.out and 1 more (+136/-28 lines) download inline-1.patchdiff --git a/bin/pg_repack.c b/bin/pg_repack.c index 8cc540a..7774699 100644 --- a/bin/pg_repack.c +++ b/bin/pg_repack.c @@ -662,11 +662,11 @@ is_requested_relation_exists(char *errbuf, size_t errsize){ ") AS given_t(r,kind) WHERE" /* regular --table relation or inherited --parent-table */ " NOT EXISTS(" - " SELECT FROM repack.tables WHERE relid=pg_catalog.to_regclass(given_t.r))" + " SELECT FROM repack.tables WHERE relid=pg_catalog.to_regclass(given_t.r)::oid)" /* declarative partitioned --parent-table */ " AND NOT EXISTS(" " SELECT FROM pg_catalog.pg_class c" - " WHERE c.oid=pg_catalog.to_regclass(given_t.r) AND c.relkind = given_t.kind AND given_t.kind = 'p')" + " WHERE c.oid=pg_catalog.to_regclass(given_t.r)::oid AND c.relkind = given_t.kind AND given_t.kind = 'p')" ); /* double check the parameters array is sane */ @@ -848,7 +848,7 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize) for (cell = table_list.head; cell; cell = cell->next) { /* Construct table name placeholders to be used by PQexecParams */ - appendStringInfo(&sql, "relid = $%d::regclass", iparam + 1); + appendStringInfo(&sql, "relid = $%d::regclass::oid", iparam + 1); params[iparam++] = cell->val; if (cell->next) appendStringInfoString(&sql, " OR "); @@ -867,7 +867,7 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize) { /* Construct table name placeholders to be used by PQexecParams */ appendStringInfo(&sql, - "relid = ANY(repack.get_table_and_inheritors($%d::regclass))", + "relid = ANY(repack.get_table_and_inheritors($%d::regclass)::oid[])", iparam + 1); params[iparam++] = cell->val; if (cell->next) @@ -898,11 +898,11 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize) if (exclude_extension_list.head) { appendStringInfoString(&sql, " AND t.relid NOT IN" - " (SELECT d.objid::regclass" + " (SELECT d.objid" " FROM pg_catalog.pg_depend d" " JOIN pg_catalog.pg_extension e" " ON d.refobjid = e.oid" - " WHERE d.classid = 'pg_class'::regclass AND ("); + " WHERE d.classid = 'pg_class'::regclass::oid AND ("); /* List all excluded extensions */ for (cell = exclude_extension_list.head; cell; cell = cell->next) @@ -2047,7 +2047,7 @@ lock_exclusive(PGconn *conn, const char *relid, const char *lock_query, bool sta { elog(WARNING, "terminating conflicted backends"); cancel_query = - "SELECT pg_catalog.pg_terminate_backend(pid) FROM pg_locks" + "SELECT pg_catalog.pg_terminate_backend(pid) FROM pg_catalog.pg_locks" " WHERE locktype = 'relation'" " AND relation = $1 AND pid <> pg_catalog.pg_backend_pid()"; } @@ -2055,7 +2055,7 @@ lock_exclusive(PGconn *conn, const char *relid, const char *lock_query, bool sta { elog(WARNING, "canceling conflicted backends"); cancel_query = - "SELECT pg_catalog.pg_cancel_backend(pid) FROM pg_locks" + "SELECT pg_catalog.pg_cancel_backend(pid) FROM pg_catalog.pg_locks" " WHERE locktype = 'relation'" " AND relation = $1 AND pid <> pg_catalog.pg_backend_pid()"; } @@ -2191,16 +2191,25 @@ repack_cleanup_index(bool fatal, void *userdata) StringInfoData sql, sql_drop; PGresult *index_details = (PGresult *) userdata; char *schema_name; + char *quoted_schema; int num; schema_name = getstr(index_details, 0, 5); num = PQntuples(index_details); + quoted_schema = PQescapeIdentifier(connection, schema_name, + strlen(schema_name)); + if (quoted_schema == NULL) + elog(ERROR, "Unable to quote schema name \"%s\": %s", + schema_name, PQerrorMessage(connection)); + initStringInfo(&sql); initStringInfo(&sql_drop); appendStringInfoString(&sql, "DROP INDEX CONCURRENTLY IF EXISTS "); - appendStringInfo(&sql, "\"%s\".", schema_name); + appendStringInfo(&sql, "%s.", quoted_schema); + + PQfreemem(quoted_schema); for (int i = 0; i < num; i++) { @@ -2236,7 +2245,7 @@ repack_table_indexes(PGresult *index_details) params[1] = utoa(table, buffer[1]); params[2] = tablespace; schema_name = getstr(index_details, 0, 5); - /* table_name is schema-qualified */ + /* table_name is quoted and schema-qualified (repack.oid2text) */ table_name = getstr(index_details, 0, 4); /* Keep track of which of the table's indexes we have successfully @@ -2281,6 +2290,9 @@ repack_table_indexes(PGresult *index_details) } if (PQntuples(res) > 0) { + char *quoted_schema = PQescapeIdentifier(connection, + schema_name, + strlen(schema_name)); ereport(WARNING, (errcode(E_PG_COMMAND), errmsg("Cannot create index \"%s\".\"index_%u\", " @@ -2288,9 +2300,12 @@ repack_table_indexes(PGresult *index_details) errdetail("An invalid index may have been left behind" " by a previous pg_repack on the table" " which was interrupted. Please use DROP " - "INDEX \"%s\".\"index_%u\"" + "INDEX %s.\"index_%u\"" " to remove this index and try again.", - schema_name, index))); + quoted_schema ? quoted_schema : schema_name, + index))); + if (quoted_schema) + PQfreemem(quoted_schema); continue; } @@ -2424,18 +2439,18 @@ repack_all_indexes(char *errbuf, size_t errsize) " FROM pg_catalog.pg_index idx" " JOIN pg_catalog.pg_class i ON i.oid = idx.indexrelid" " JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace" - " WHERE idx.indexrelid = $1::regclass ORDER BY indisvalid DESC, i.relname, n.nspname"); + " WHERE idx.indexrelid = $1::regclass::oid ORDER BY indisvalid DESC, i.relname, n.nspname"); cell = r_index.head; } else if (table_list.head || parent_table_list.head) { appendStringInfoString(&sql, - "SELECT repack.oid2text(i.oid), idx.indexrelid, idx.indisvalid, idx.indrelid, $1::text, n.nspname" + "SELECT repack.oid2text(i.oid), idx.indexrelid, idx.indisvalid, idx.indrelid, repack.oid2text(idx.indrelid), n.nspname" " FROM pg_catalog.pg_index idx" " JOIN pg_catalog.pg_class i ON i.oid = idx.indexrelid" " JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace" - " WHERE idx.indrelid = $1::regclass ORDER BY indisvalid DESC, i.relname, n.nspname"); + " WHERE idx.indrelid = $1::regclass::oid ORDER BY indisvalid DESC, i.relname, n.nspname"); for (cell = parent_table_list.head; cell; cell = cell->next) { @@ -2455,7 +2470,7 @@ repack_all_indexes(char *errbuf, size_t errsize) res = execute_elevel("SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname)" " FROM pg_catalog.pg_class c" " JOIN pg_catalog.pg_namespace n on n.oid = c.relnamespace" - " WHERE c.oid = ANY (repack.get_table_and_inheritors($1::regclass))" + " WHERE c.oid = ANY (repack.get_table_and_inheritors($1::regclass)::oid[])" " AND c.relkind = 'r'" " ORDER BY n.nspname, c.relname", 1, params, DEBUG2); diff --git a/lib/repack.c b/lib/repack.c index 1b8f5e5..4fe679e 100644 --- a/lib/repack.c +++ b/lib/repack.c @@ -40,6 +40,7 @@ #include "catalog/pg_inherits_fn.h" #endif #include "catalog/pg_namespace.h" +#include "catalog/pg_operator.h" #include "catalog/pg_type.h" #include "commands/tablecmds.h" #include "commands/trigger.h" @@ -707,6 +708,26 @@ parse_indexdef_col(char *token, char **desc, char **nulls, char **collate) } } +/* + * Append the operator as OPERATOR(schema.name), so that it does not depend on + * the search_path of whoever executes the generated ORDER BY. + */ +static void +append_qualified_opname(StringInfo str, Oid oprid) +{ + HeapTuple tuple; + Form_pg_operator opr; + + tuple = SearchSysCache1(OPEROID, ObjectIdGetDatum(oprid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for operator %u", oprid); + opr = (Form_pg_operator) GETSTRUCT(tuple); + appendStringInfo(str, "OPERATOR(%s.%s)", + quote_identifier(get_namespace_name(opr->oprnamespace)), + NameStr(opr->oprname)); + ReleaseSysCache(tuple); +} + /** * @fn Datum repack_get_order_by(PG_FUNCTION_ARGS) * @brief Get key definition of the index. @@ -753,14 +774,18 @@ repack_get_order_by(PG_FUNCTION_ARGS) appendStringInfoString(&str, token); if (colcollate) appendStringInfo(&str, " %s", colcollate); - if (coldesc) + /* + * DESC cannot be combined with USING; for a DESC column with an + * operator class we emit USING <greater-than operator> instead. + */ + if (coldesc && !opcname) appendStringInfo(&str, " %s", coldesc); if (opcname) { /* lookup default operator name from operator class */ Oid oprid; - int16 strategy = BTLessStrategyNumber; + int16 strategy = coldesc ? BTGreaterStrategyNumber : BTLessStrategyNumber; Oid opcintype; Oid opfamily; @@ -790,7 +815,8 @@ repack_get_order_by(PG_FUNCTION_ARGS) strategy, opcintype, opcintype, opfamily); opcname[-1] = '\0'; - appendStringInfo(&str, " USING %s", get_opname(oprid)); + appendStringInfoString(&str, " USING "); + append_qualified_opname(&str, oprid); } if (colnulls) appendStringInfo(&str, " %s", colnulls); diff --git a/regress/expected/get_order_by.out b/regress/expected/get_order_by.out index 1336a7b..74d54e7 100644 --- a/regress/expected/get_order_by.out +++ b/regress/expected/get_order_by.out @@ -14,9 +14,9 @@ INFO: repacking table "public.issue3_1" CREATE TABLE issue3_2 (col1 int NOT NULL, col2 text NOT NULL); CREATE UNIQUE INDEX issue3_2_idx ON issue3_2 (col1 DESC, col2 text_pattern_ops); SELECT repack.get_order_by('issue3_2_idx'::regclass::oid, 'issue3_2'::regclass::oid); - get_order_by ---------------------------- - col1 DESC, col2 USING ~<~ + get_order_by +------------------------------------------------ + col1 DESC, col2 USING OPERATOR(pg_catalog.~<~) (1 row) \! pg_repack --dbname=contrib_regression --table=issue3_2 @@ -34,9 +34,9 @@ INFO: repacking table "public.issue3_3" CREATE TABLE issue3_4 (col1 int NOT NULL, col2 text NOT NULL); CREATE UNIQUE INDEX issue3_4_idx ON issue3_4 (col1 NULLS FIRST, col2 text_pattern_ops DESC NULLS LAST); SELECT repack.get_order_by('issue3_4_idx'::regclass::oid, 'issue3_4'::regclass::oid); - get_order_by --------------------------------------------------- - col1 NULLS FIRST, col2 DESC USING ~<~ NULLS LAST + get_order_by +------------------------------------------------------------------ + col1 NULLS FIRST, col2 USING OPERATOR(pg_catalog.~>~) NULLS LAST (1 row) \! pg_repack --dbname=contrib_regression --table=issue3_4 @@ -69,11 +69,55 @@ CREATE OPERATOR CLASS public.opclass_int4_ops FOR TYPE int4 USING btree AS CREATE TABLE opclass_tbl (col1 int NOT NULL, col2 int NOT NULL); CREATE UNIQUE INDEX opclass_tbl_idx ON opclass_tbl (col1 public.opclass_int4_ops, col2 DESC); SELECT repack.get_order_by('opclass_tbl_idx'::regclass::oid, 'opclass_tbl'::regclass::oid); - get_order_by -------------------------- - col1 USING <, col2 DESC + get_order_by +---------------------------------------------- + col1 USING OPERATOR(pg_catalog.<), col2 DESC (1 row) CLUSTER opclass_tbl USING opclass_tbl_idx; \! pg_repack --dbname=contrib_regression --table=opclass_tbl INFO: repacking table "public.opclass_tbl" +-- +-- DESC with a non-default operator class must not produce "DESC USING" +-- +CREATE TABLE desc_opclass_tbl (col1 int NOT NULL, col2 text NOT NULL); +CREATE UNIQUE INDEX desc_opclass_tbl_idx ON desc_opclass_tbl (col1, col2 text_pattern_ops DESC NULLS LAST); +INSERT INTO desc_opclass_tbl VALUES (2, 'a'), (1, 'b'), (1, 'c'); +CLUSTER desc_opclass_tbl USING desc_opclass_tbl_idx; +SELECT repack.get_order_by('desc_opclass_tbl_idx'::regclass::oid, 'desc_opclass_tbl'::regclass::oid); + get_order_by +------------------------------------------------------ + col1, col2 USING OPERATOR(pg_catalog.~>~) NULLS LAST +(1 row) + +\! pg_repack --dbname=contrib_regression --table=desc_opclass_tbl +INFO: repacking table "public.desc_opclass_tbl" +SELECT col1, col2 FROM desc_opclass_tbl ORDER BY ctid; + col1 | col2 +------+------ + 1 | c + 1 | b + 2 | a +(3 rows) + +-- Quoted schema name in generated SQL +CREATE SCHEMA "q""s"; +CREATE TABLE "q""s".qt (id int PRIMARY KEY, v text) WITH (fillfactor = 70, toast.autovacuum_enabled = false); +CREATE INDEX qt_idx ON "q""s".qt (v); +INSERT INTO "q""s".qt SELECT i, 'v' || i FROM generate_series(1, 5) i; +\! pg_repack --dbname=contrib_regression --table='"q""s".qt' +INFO: repacking table ""q""s".qt" +\! pg_repack --dbname=contrib_regression --only-indexes --table='"q""s".qt' +INFO: repacking indexes of ""q""s".qt" +INFO: repacking index ""q""s".qt_idx" +INFO: repacking index ""q""s".qt_pkey" +SELECT (SELECT count(*) FROM pg_class WHERE relnamespace = '"q""s"'::regnamespace AND relname LIKE 'index\_%') AS leftover, + c.reloptions, t.reloptions AS toast_reloptions, (SELECT count(*) FROM "q""s".qt) AS n +FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid WHERE c.oid = '"q""s".qt'::regclass; + leftover | reloptions | toast_reloptions | n +----------+-----------------+----------------------------+--- + 0 | {fillfactor=70} | {autovacuum_enabled=false} | 5 +(1 row) + +DROP SCHEMA "q""s" CASCADE; +NOTICE: drop cascades to table "q""s".qt diff --git a/regress/sql/get_order_by.sql b/regress/sql/get_order_by.sql index 307315e..12e823a 100644 --- a/regress/sql/get_order_by.sql +++ b/regress/sql/get_order_by.sql @@ -45,3 +45,26 @@ CREATE UNIQUE INDEX opclass_tbl_idx ON opclass_tbl (col1 public.opclass_int4_ops SELECT repack.get_order_by('opclass_tbl_idx'::regclass::oid, 'opclass_tbl'::regclass::oid); CLUSTER opclass_tbl USING opclass_tbl_idx; \! pg_repack --dbname=contrib_regression --table=opclass_tbl + +-- +-- DESC with a non-default operator class must not produce "DESC USING" +-- +CREATE TABLE desc_opclass_tbl (col1 int NOT NULL, col2 text NOT NULL); +CREATE UNIQUE INDEX desc_opclass_tbl_idx ON desc_opclass_tbl (col1, col2 text_pattern_ops DESC NULLS LAST); +INSERT INTO desc_opclass_tbl VALUES (2, 'a'), (1, 'b'), (1, 'c'); +CLUSTER desc_opclass_tbl USING desc_opclass_tbl_idx; +SELECT repack.get_order_by('desc_opclass_tbl_idx'::regclass::oid, 'desc_opclass_tbl'::regclass::oid); +\! pg_repack --dbname=contrib_regression --table=desc_opclass_tbl +SELECT col1, col2 FROM desc_opclass_tbl ORDER BY ctid; + +-- Quoted schema name in generated SQL +CREATE SCHEMA "q""s"; +CREATE TABLE "q""s".qt (id int PRIMARY KEY, v text) WITH (fillfactor = 70, toast.autovacuum_enabled = false); +CREATE INDEX qt_idx ON "q""s".qt (v); +INSERT INTO "q""s".qt SELECT i, 'v' || i FROM generate_series(1, 5) i; +\! pg_repack --dbname=contrib_regression --table='"q""s".qt' +\! pg_repack --dbname=contrib_regression --only-indexes --table='"q""s".qt' +SELECT (SELECT count(*) FROM pg_class WHERE relnamespace = '"q""s"'::regnamespace AND relname LIKE 'index\_%') AS leftover, + c.reloptions, t.reloptions AS toast_reloptions, (SELECT count(*) FROM "q""s".qt) AS n +FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid WHERE c.oid = '"q""s".qt'::regclass; +DROP SCHEMA "q""s" CASCADE;

view thread (3+ messages)  latest in thread

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