pg.ddx.io reorg/pg_repack GitHub issues and pull requests
help / color / mirror / Atom feedFrom: za-arthur (@za-arthur) <noreply+za-arthur@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] PR #482: Add `--use-original-schema` option
Date: Mon, 23 Mar 2026 16:13:48 +0000
Message-ID: <gh-reorg-pg_repack-482@github.com> (raw)
This introduces the `--use-original-schema` option to allow to create temporary objects in the target table's original schema instead of the `repack` schema.
- Replace the `repack.tables` view by the `repack.get_tables()` function to generate DDL statements using the target temporary schema.
- Update internal and SQL helper functions to accept target schema name.
Additionally:
- Rewrite functions in `pg_repack.sql.in` by using `format()` instead of string concatenation: https://github.com/reorg/pg_repack/commit/735aa86eab0dd2207469812e37f2451d4a7261d6
- Refactor `repack.repack_indexdef`: pass table name instead of oid: https://github.com/reorg/pg_repack/commit/08ccf05c85507c83bbba7bac3801674a2c8c4f09
Discussion for additional context:
https://github.com/reorg/pg_repack/pull/475
inline patch 1: bin/pg_repack.c, lib/pg_repack.sql.in, lib/repack.c and 12 more (+588/-301 lines) download inline-1.patch
diff --git a/bin/pg_repack.c b/bin/pg_repack.c
index 1c930d1..1122553 100644
--- a/bin/pg_repack.c
+++ b/bin/pg_repack.c
@@ -183,9 +183,9 @@ typedef struct repack_index
typedef struct repack_table
{
const char *target_name; /* target: relname */
+ const char *target_schema; /* target: schemaname */
+ const char *temp_schema; /* target: temp_schemaname */
Oid target_oid; /* target: OID */
- Oid target_toast; /* target: toast OID */
- Oid target_tidx; /* target: toast index OID */
Oid pkid; /* target: PK OID */
Oid ckid; /* target: CK OID */
Oid temp_oid; /* temp: OID */
@@ -194,12 +194,12 @@ typedef struct repack_table
const char *create_trigger; /* CREATE TRIGGER repack_trigger */
const char *enable_trigger; /* ALTER TABLE ENABLE ALWAYS TRIGGER repack_trigger */
const char *create_table; /* CREATE TABLE table AS SELECT WITH NO DATA*/
- const char *dest_tablespace; /* Destination tablespace */
const char *copy_data; /* INSERT INTO */
const char *alter_col_storage; /* ALTER TABLE ALTER COLUMN SET STORAGE */
const char *drop_columns; /* ALTER TABLE DROP COLUMNs */
const char *delete_log; /* DELETE FROM log */
const char *lock_table; /* LOCK TABLE table */
+ const char *lock_temp_table; /* LOCK TABLE temp table */
const char *sql_peek; /* SQL used in flush */
const char *sql_insert; /* SQL used in flush */
const char *sql_delete; /* SQL used in flush */
@@ -262,6 +262,7 @@ static bool no_error_on_publication = false; /* repack even though publicatio
static bool no_error_on_invalid_index = false; /* repack even though invalid index is found */
static bool error_on_invalid_index = false; /* don't repack when invalid index is found,
* deprecated, this the default behavior now */
+static bool use_original_schema = false; /* use original schema of a table */
static int apply_count = APPLY_COUNT_DEFAULT;
static int switch_threshold = SWITCH_THRESHOLD_DEFAULT;
@@ -293,6 +294,7 @@ static pgut_option options[] =
{ 'b', 'D', "no-kill-backend", &no_kill_backend },
{ 'b', 'k', "no-superuser-check", &no_superuser_check },
{ 'l', 'C', "exclude-extension", &exclude_extension_list },
+ { 'b', 6, "use-original-schema", &use_original_schema },
{ 'b', 5, "no-error-on-publication", &no_error_on_publication },
{ 'b', 4, "no-error-on-invalid-index", &no_error_on_invalid_index },
{ 'b', 3, "error-on-invalid-index", &error_on_invalid_index },
@@ -363,6 +365,9 @@ main(int argc, char *argv[])
else if (jobs)
ereport(WARNING, (errcode(EINVAL),
errmsg("option -j (--jobs) has no effect, repacking indexes does not use parallel jobs")));
+ if (use_original_schema)
+ ereport(WARNING, (errcode(EINVAL),
+ errmsg("option --use-original-schema has no effect while repacking indexes")));
if (!repack_all_indexes(errbuf, sizeof(errbuf)))
ereport(ERROR,
(errcode(ERROR), errmsg("%s", errbuf)));
@@ -805,11 +810,11 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
num_schemas = simple_string_list_size(schema_list);
num_excluded_extensions = simple_string_list_size(exclude_extension_list);
- /* 1st param is the user-specified tablespace */
+ /* 1st param is tablespace, 2nd is use_original_schema */
num_params = num_excluded_extensions +
num_parent_tables +
num_tables +
- num_schemas + 1;
+ num_schemas + 2;
params = pgut_malloc(num_params * sizeof(char *));
initStringInfo(&sql);
@@ -828,13 +833,12 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
/* acquire target tables */
appendStringInfoString(&sql,
- "SELECT t.*,"
- " coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest"
- " FROM repack.tables t, "
- " (VALUES ($1::text)) as v (tablespace)"
+ "SELECT t.*"
+ " FROM repack.get_tables($1::name, $2::boolean) t "
" WHERE ");
params[iparam++] = tablespace;
+ params[iparam++] = use_original_schema ? "true" : "false";
if (num_tables || num_parent_tables)
{
/* standalone tables */
@@ -947,9 +951,8 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
table.target_name = getstr(res, i, c++);
table.target_oid = getoid(res, i, c++);
- table.target_toast = getoid(res, i, c++);
- table.target_tidx = getoid(res, i, c++);
- c++; // Skip schemaname
+ table.target_schema = getstr(res, i, c++);
+ table.temp_schema = getstr(res, i, c++);
table.pkid = getoid(res, i, c++);
table.ckid = getoid(res, i, c++);
table.temp_oid = InvalidOid; /* filled after creating the temp table */
@@ -967,25 +970,23 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
table.enable_trigger = getstr(res, i, c++);
table.create_table = getstr(res, i, c++);
- getstr(res, i, c++); /* tablespace_orig is clobbered */
table.copy_data = getstr(res, i , c++);
table.alter_col_storage = getstr(res, i, c++);
table.drop_columns = getstr(res, i, c++);
table.delete_log = getstr(res, i, c++);
table.lock_table = getstr(res, i, c++);
+ table.lock_temp_table = getstr(res, i, c++);
ckey = getstr(res, i, c++);
table.sql_peek = getstr(res, i, c++);
table.sql_insert = getstr(res, i, c++);
table.sql_delete = getstr(res, i, c++);
table.sql_update = getstr(res, i, c++);
table.sql_pop = getstr(res, i, c++);
- table.dest_tablespace = getstr(res, i, c++);
/* Craft Copy SQL */
initStringInfo(©_sql);
appendStringInfoString(©_sql, table.copy_data);
if (!orderby)
-
{
if (ckey != NULL)
{
@@ -1268,12 +1269,14 @@ repack_one_table(repack_table *table, const char *orderby)
int num;
char *vxid = NULL;
char buffer[12];
+ char pid_buf[12];
StringInfoData sql;
bool ret = false;
PGresult *indexres = NULL;
- const char *indexparams[2];
+ const char *indexparams[3];
char indexbuffer[12];
int j;
+ Oid log_oid;
/* appname will be "pg_repack" in normal use on 9.0+, or
* "pg_regress/<testname>" when run under `make installcheck`
@@ -1295,9 +1298,9 @@ repack_one_table(repack_table *table, const char *orderby)
elog(DEBUG2, "---- repack_one_table ----");
elog(DEBUG2, "target_name : %s", table->target_name);
+ elog(DEBUG2, "target_schema : %s", table->target_schema);
+ elog(DEBUG2, "temp_schema : %s", table->temp_schema);
elog(DEBUG2, "target_oid : %u", table->target_oid);
- elog(DEBUG2, "target_toast : %u", table->target_toast);
- elog(DEBUG2, "target_tidx : %u", table->target_tidx);
elog(DEBUG2, "pkid : %u", table->pkid);
elog(DEBUG2, "ckid : %u", table->ckid);
elog(DEBUG2, "create_pktype : %s", table->create_pktype);
@@ -1305,11 +1308,9 @@ repack_one_table(repack_table *table, const char *orderby)
elog(DEBUG2, "create_trigger : %s", table->create_trigger);
elog(DEBUG2, "enable_trigger : %s", table->enable_trigger);
elog(DEBUG2, "create_table : %s", table->create_table);
- elog(DEBUG2, "dest_tablespace : %s", table->dest_tablespace);
elog(DEBUG2, "copy_data : %s", table->copy_data);
- elog(DEBUG2, "alter_col_storage : %s", table->alter_col_storage ?
- table->alter_col_storage : "(skipped)");
- elog(DEBUG2, "drop_columns : %s", table->drop_columns ? table->drop_columns : "(skipped)");
+ elog(DEBUG2, "alter_col_storage : %s", table->alter_col_storage);
+ elog(DEBUG2, "drop_columns : %s", table->drop_columns);
elog(DEBUG2, "delete_log : %s", table->delete_log);
elog(DEBUG2, "lock_table : %s", table->lock_table);
elog(DEBUG2, "sql_peek : %s", table->sql_peek);
@@ -1349,7 +1350,8 @@ repack_one_table(repack_table *table, const char *orderby)
*/
indexparams[0] = utoa(table->target_oid, indexbuffer);
- indexparams[1] = moveidx ? tablespace : NULL;
+ indexparams[1] = table->temp_schema;
+ indexparams[2] = moveidx ? tablespace : NULL;
/* First, just display a warning message for any invalid indexes
* which may be on the table (mostly to match the behavior of 1.1.8),
@@ -1375,9 +1377,9 @@ repack_one_table(repack_table *table, const char *orderby)
indexres = execute(
"SELECT indexrelid,"
- " repack.repack_indexdef(indexrelid, indrelid, $2, FALSE) "
+ " repack.repack_indexdef(indexrelid, format('%I.table_%s', $2::text, indrelid), $3, FALSE) "
" FROM pg_index WHERE indrelid = $1 AND indisvalid",
- 2, indexparams);
+ 3, indexparams);
table->n_indexes = PQntuples(indexres);
table->indexes = pgut_malloc(table->n_indexes * sizeof(repack_index));
@@ -1424,12 +1426,14 @@ repack_one_table(repack_table *table, const char *orderby)
command(table->create_pktype, 0, NULL);
temp_obj_num++;
- command(table->create_log, 0, NULL);
+ res = execute(table->create_log, 0, NULL);
+ log_oid = getoid(res, 0, 0);
+ CLEARPGRES(res);
temp_obj_num++;
command(table->create_trigger, 0, NULL);
temp_obj_num++;
command(table->enable_trigger, 0, NULL);
- printfStringInfo(&sql, "SELECT repack.disable_autovacuum('repack.log_%u')", table->target_oid);
+ printfStringInfo(&sql, "SELECT repack.disable_autovacuum(%u)", log_oid);
command(sql.data, 0, NULL);
/* While we are still holding an AccessExclusive lock on the table, submit
@@ -1444,8 +1448,8 @@ repack_one_table(repack_table *table, const char *orderby)
* pg_locks momentarily.
*/
res = pgut_execute(conn2, "SELECT pg_backend_pid()", 0, NULL);
- buffer[0] = '\0';
- strncat(buffer, PQgetvalue(res, 0, 0), sizeof(buffer) - 1);
+ pid_buf[0] = '\0';
+ strncat(pid_buf, PQgetvalue(res, 0, 0), sizeof(pid_buf) - 1);
CLEARPGRES(res);
/*
@@ -1536,7 +1540,7 @@ repack_one_table(repack_table *table, const char *orderby)
/* Fetch an array of Virtual IDs of all transactions active right now.
*/
- params[0] = buffer; /* backend PID of conn2 */
+ params[0] = pid_buf; /* backend PID of conn2 */
params[1] = PROGRAM_NAME;
res = execute(SQL_XID_SNAPSHOT, 2, params);
vxid = pgut_strdup(PQgetvalue(res, 0, 0));
@@ -1569,27 +1573,19 @@ repack_one_table(repack_table *table, const char *orderby)
* Before copying data to the target table, we need to set the column storage
* type if its storage type has been changed from the type default.
*/
- params[0] = utoa(table->target_oid, buffer);
- params[1] = table->dest_tablespace;
- command(table->create_table, 2, params);
- if (table->alter_col_storage)
- command(table->alter_col_storage, 0, NULL);
+ res = execute(table->create_table, 0, NULL);
+ table->temp_oid = getoid(res, 0, 0);
+ Assert(OidIsValid(table->temp_oid));
+ CLEARPGRES(res);
+
+ command(table->alter_col_storage, 0, NULL);
command(table->copy_data, 0, NULL);
temp_obj_num++;
- printfStringInfo(&sql, "SELECT repack.disable_autovacuum('repack.table_%u')", table->target_oid);
- if (table->drop_columns)
- command(table->drop_columns, 0, NULL);
+ printfStringInfo(&sql, "SELECT repack.disable_autovacuum(%u)", table->temp_oid);
+ command(table->drop_columns, 0, NULL);
command(sql.data, 0, NULL);
command("COMMIT", 0, NULL);
- /* Get OID of the temp table */
- printfStringInfo(&sql, "SELECT 'repack.table_%u'::regclass::oid",
- table->target_oid);
- res = execute(sql.data, 0, NULL);
- table->temp_oid = getoid(res, 0, 0);
- Assert(OidIsValid(table->temp_oid));
- CLEARPGRES(res);
-
/*
* 3. Create indexes on temp table.
*/
@@ -1671,10 +1667,8 @@ repack_one_table(repack_table *table, const char *orderby)
* Acquire AccessExclusiveLock on the temp table to prevent concurrent
* operations during swapping relations.
*/
- printfStringInfo(&sql, "LOCK TABLE repack.table_%u IN ACCESS EXCLUSIVE MODE",
- table->target_oid);
if (!(lock_exclusive(conn2, utoa(table->temp_oid, buffer),
- sql.data, false)))
+ table->lock_temp_table, false)))
{
elog(WARNING, "lock_exclusive() failed in conn2 for table_%u",
table->target_oid);
@@ -1683,7 +1677,8 @@ repack_one_table(repack_table *table, const char *orderby)
apply_log(conn2, table, 0);
params[0] = utoa(table->target_oid, buffer);
- pgut_command(conn2, "SELECT repack.repack_swap($1)", 1, params);
+ params[1] = utoa(table->temp_oid, indexbuffer);
+ pgut_command(conn2, "SELECT repack.repack_swap($1, $2)", 2, params);
pgut_command(conn2, "COMMIT", 0, NULL);
/*
@@ -1693,8 +1688,10 @@ repack_one_table(repack_table *table, const char *orderby)
elog(DEBUG2, "---- drop ----");
command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
- params[1] = utoa(temp_obj_num, indexbuffer);
- command("SELECT repack.repack_drop($1, $2)", 2, params);
+ params[0] = utoa(table->target_oid, buffer);
+ params[1] = table->temp_schema;
+ params[2] = utoa(temp_obj_num, indexbuffer);
+ command("SELECT repack.repack_drop($1, $2, $3)", 3, params);
command("COMMIT", 0, NULL);
temp_obj_num = 0; /* reset temporary object counter after cleanup */
@@ -2053,14 +2050,15 @@ repack_cleanup_callback(bool fatal, void *userdata)
{
repack_table *table = (repack_table *) userdata;
Oid target_table = table->target_oid;
- const char *params[2];
+ const char *params[3];
char buffer[12];
char num_buff[12];
if(fatal)
{
params[0] = utoa(target_table, buffer);
- params[1] = utoa(temp_obj_num, num_buff);
+ params[1] = table->temp_schema;
+ params[2] = utoa(temp_obj_num, num_buff);
/* testing PQstatus() of connection and conn2, as we do
* in repack_cleanup(), doesn't seem to work here,
@@ -2076,7 +2074,7 @@ repack_cleanup_callback(bool fatal, void *userdata)
table->target_name);
}
- command("SELECT repack.repack_drop($1, $2)", 2, params);
+ command("SELECT repack.repack_drop($1, $2, $3)", 3, params);
command("COMMIT", 0, NULL);
temp_obj_num = 0; /* reset temporary object counter after cleanup */
}
@@ -2097,7 +2095,7 @@ repack_cleanup(bool fatal, const repack_table *table)
{
char buffer[12];
char num_buff[12];
- const char *params[2];
+ const char *params[3];
/* Try reconnection if not available. */
if (PQstatus(connection) != CONNECTION_OK ||
@@ -2106,7 +2104,8 @@ repack_cleanup(bool fatal, const repack_table *table)
/* do cleanup */
params[0] = utoa(table->target_oid, buffer);
- params[1] = utoa(temp_obj_num, num_buff);
+ params[1] = table->temp_schema;
+ params[2] = utoa(temp_obj_num, num_buff);
command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
if (!(lock_exclusive(connection, params[0], table->lock_table, false)))
@@ -2116,7 +2115,7 @@ repack_cleanup(bool fatal, const repack_table *table)
table->target_name);
}
- command("SELECT repack.repack_drop($1, $2)", 2, params);
+ command("SELECT repack.repack_drop($1, $2, $3)", 3, params);
command("COMMIT", 0, NULL);
temp_obj_num = 0; /* reset temporary object counter after cleanup */
}
@@ -2238,7 +2237,7 @@ repack_table_indexes(PGresult *index_details)
continue;
params[0] = utoa(index, buffer[0]);
- res = execute("SELECT repack.repack_indexdef($1, $2, $3, true)", 3,
+ res = execute("SELECT repack.repack_indexdef($1, repack.oid2text($2), $3, true)", 3,
params);
if (PQntuples(res) < 1)
diff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.in
index b059645..4095208 100644
--- a/lib/pg_repack.sql.in
+++ b/lib/pg_repack.sql.in
@@ -44,102 +44,146 @@ $$
$$
LANGUAGE sql STABLE STRICT;
-CREATE FUNCTION repack.get_order_by(oid, oid) RETURNS text AS
+CREATE FUNCTION repack.get_order_by(oid) RETURNS text AS
'MODULE_PATHNAME', 'repack_get_order_by'
LANGUAGE C STABLE STRICT;
-CREATE FUNCTION repack.create_log_table(oid) RETURNS void AS
+CREATE FUNCTION repack.create_log_table(relid oid, schemaname name)
+RETURNS oid AS
$$
+DECLARE
+ new_oid oid;
BEGIN
- EXECUTE 'CREATE TABLE repack.log_' || $1 ||
- ' (id bigserial PRIMARY KEY,' ||
- ' pk repack.pk_' || $1 || ',' ||
- ' row ' || repack.oid2text($1) || ')';
+ EXECUTE format(
+ $fmt$
+ CREATE TABLE %2$I.log_%1$s (
+ id bigserial PRIMARY KEY,
+ pk %2$I.pk_%1$s,
+ row %3$s)
+ $fmt$,
+ relid,
+ schemaname,
+ repack.oid2text(relid)
+ );
+
+ EXECUTE format('SELECT %L::regclass::oid', format('%I.log_%s', schemaname, relid)) INTO new_oid;
+ RETURN new_oid;
END
$$
LANGUAGE plpgsql;
-CREATE FUNCTION repack.create_table(oid, name) RETURNS void AS
+CREATE FUNCTION repack.create_table(relid oid, schemaname name, reltablespace name)
+RETURNS oid AS
$$
+DECLARE
+ new_oid oid;
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(
+ $fmt$
+ CREATE TABLE %2$I.table_%1$s
+ WITH (%3$s)
+ TABLESPACE %4$I
+ AS SELECT %5$s
+ FROM ONLY %6$s WITH NO DATA
+ $fmt$,
+ relid,
+ schemaname,
+ repack.get_storage_param(relid),
+ reltablespace,
+ repack.get_columns_for_create_as(relid),
+ repack.oid2text(relid)
+ );
+
+ EXECUTE format('SELECT %L::regclass::oid', format('%I.table_%s', schemaname, relid)) INTO new_oid;
+ RETURN new_oid;
END
$$
LANGUAGE plpgsql;
-CREATE FUNCTION repack.create_index_type(oid, oid) RETURNS void AS
+CREATE FUNCTION repack.create_index_type(relid oid, pkid oid, schemaname name)
+RETURNS void AS
$$
BEGIN
- EXECUTE repack.get_create_index_type($1, 'repack.pk_' || $2);
+ EXECUTE format(
+ 'CREATE TYPE %s AS (%s)',
+ format('%I.pk_%s', schemaname, relid),
+ coalesce(string_agg(
+ format('%I %s',
+ attname,
+ format_type(atttypid, atttypmod)),
+ ', '),
+ '')
+ )
+ FROM pg_attribute,
+ (SELECT indrelid,
+ indkey,
+ generate_series(0, indnatts-1) AS i
+ FROM pg_index
+ WHERE indexrelid = pkid
+ ) AS keys
+ WHERE attrelid = indrelid
+ AND attnum = indkey[i];
END
$$
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 indrelid,
- indkey,
- generate_series(0, indnatts-1) AS i
- FROM pg_index
- WHERE indexrelid = $1
- ) AS keys
- WHERE attrelid = indrelid
- AND attnum = indkey[i];
+CREATE FUNCTION repack.create_trigger(relid oid, pkid oid, schemaname name)
+ RETURNS void AS
$$
-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) || ')';
+BEGIN
+ EXECUTE format(
+ $fmt$
+ CREATE TRIGGER repack_trigger
+ AFTER INSERT OR DELETE OR UPDATE
+ ON %s
+ FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger(%L, %s)
+ $fmt$,
+ repack.oid2text(relid),
+ -- Pass the schema name as string, it will be quoted as identifier by `repack_trigger()`
+ schemaname,
+ repack.get_index_columns(pkid)
+ );
+END
$$
-LANGUAGE sql STABLE STRICT;
+LANGUAGE plpgsql;
-CREATE FUNCTION repack.get_enable_trigger(relid oid)
- RETURNS text AS
+CREATE FUNCTION repack.enable_trigger(relid oid)
+RETURNS void AS
$$
- SELECT 'ALTER TABLE ' || repack.oid2text($1) ||
- ' ENABLE ALWAYS TRIGGER repack_trigger';
+BEGIN
+ EXECUTE format('ALTER TABLE %s ENABLE ALWAYS TRIGGER repack_trigger',
+ repack.oid2text(relid));
+END
$$
-LANGUAGE sql STABLE STRICT;
+LANGUAGE plpgsql;
-CREATE FUNCTION repack.get_assign(oid, text) RETURNS text AS
+CREATE FUNCTION repack.get_assign(relid oid, arg text)
+RETURNS text AS
$$
- SELECT '(' || coalesce(string_agg(quote_ident(attname), ', '), '') ||
- ') = (' || $2 || '.' ||
- coalesce(string_agg(quote_ident(attname), ', ' || $2 || '.'), '') || ')'
+ SELECT format('(%s) = (%s)',
+ coalesce(string_agg(quote_ident(attname), ', '), ''),
+ coalesce(string_agg(arg || '.' || quote_ident(attname), ', '), ''))
FROM (SELECT attname FROM pg_attribute
- WHERE attrelid = $1 AND attnum > 0 AND NOT attisdropped
- ORDER BY attnum) tmp;
+ WHERE attrelid = relid AND attnum > 0 AND NOT attisdropped
+ ORDER BY attnum) tmp;
$$
LANGUAGE sql STABLE STRICT;
-CREATE FUNCTION repack.get_compare_pkey(oid, text)
- RETURNS text AS
+CREATE FUNCTION repack.get_compare_pkey(pkid oid, arg text)
+RETURNS text AS
$$
- SELECT '(' || coalesce(string_agg(quote_ident(attname), ', '), '') ||
- ') = (' || $2 || '.' ||
- coalesce(string_agg(quote_ident(attname), ', ' || $2 || '.'), '') || ')'
+ SELECT format('(%s) = (%s)',
+ coalesce(string_agg(quote_ident(attname), ', '), ''),
+ coalesce(string_agg(arg || '.' || quote_ident(attname), ', '), ''))
FROM pg_attribute,
- (SELECT indrelid,
- indkey,
- generate_series(0, indnatts-1) AS i
- FROM pg_index
- WHERE indexrelid = $1
- ) AS keys
- WHERE attrelid = indrelid
- AND attnum = indkey[i];
+ (SELECT indrelid,
+ indkey,
+ generate_series(0, indnatts-1) AS i
+ FROM pg_index
+ WHERE indexrelid = pkid
+ ) AS keys
+ WHERE attrelid = indrelid
+ AND attnum = indkey[i];
$$
LANGUAGE sql STABLE STRICT;
@@ -159,26 +203,36 @@ WHERE attrelid = $1 AND attnum > 0 ORDER BY attnum
$$
LANGUAGE sql STABLE STRICT;
--- Get a SQL text to DROP dropped columns for the table,
--- or NULL if it has no dropped columns.
-CREATE FUNCTION repack.get_drop_columns(oid, text)
- RETURNS text AS
+-- DROP dropped columns of the temporary table.
+CREATE FUNCTION repack.drop_columns(relid oid, schemaname name)
+RETURNS void AS
$$
-SELECT
- 'ALTER TABLE ' || $2 || ' ' || array_to_string(dropped_columns, ', ')
-FROM (
- SELECT
- array_agg('DROP COLUMN ' || quote_ident(attname)) AS dropped_columns
- FROM (
- SELECT * FROM pg_attribute
- WHERE attrelid = $1 AND attnum > 0 AND attisdropped
- ORDER BY attnum
- ) T
-) T
-WHERE
- array_upper(dropped_columns, 1) > 0
+DECLARE
+ sql text;
+BEGIN
+ SELECT format('ALTER TABLE %I.table_%s %s',
+ schemaname,
+ relid,
+ array_to_string(dropped_columns, ', '))
+ INTO sql
+ FROM (
+ SELECT
+ array_agg('DROP COLUMN ' || quote_ident(attname)) AS dropped_columns
+ FROM (
+ SELECT * FROM pg_attribute
+ WHERE attrelid = relid AND attnum > 0 AND attisdropped
+ ORDER BY attnum
+ ) T
+ ) T
+ WHERE
+ array_upper(dropped_columns, 1) > 0;
+
+ IF sql IS NOT NULL THEN
+ EXECUTE sql;
+ END IF;
+END
$$
-LANGUAGE sql STABLE STRICT;
+LANGUAGE plpgsql;
-- Get a comma-separated storage parameter for the table including
-- parameters for the corresponding TOAST table.
@@ -215,36 +269,52 @@ FROM (
$$
LANGUAGE sql STABLE STRICT;
--- GET a SQL text to set column storage option for the table.
-CREATE FUNCTION repack.get_alter_col_storage(oid)
- RETURNS text AS
+-- ALTER the table to set column storage option.
+CREATE FUNCTION repack.alter_col_storage(relid oid, schemaname name)
+RETURNS void AS
$$
- SELECT 'ALTER TABLE repack.table_' || $1 || array_to_string(column_storage, ',')
- FROM (
- SELECT
- array_agg(' ALTER ' || quote_ident(attname) ||
- 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'
- END) AS column_storage
- FROM (
+DECLARE
+ sql text;
+BEGIN
+ SELECT format('ALTER TABLE %I.table_%s %s',
+ schemaname,
+ relid,
+ array_to_string(column_storage, ','))
+ INTO sql
+ FROM (
+ SELECT
+ array_agg(
+ format('ALTER %s %s',
+ quote_ident(attname),
+ 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'
+ 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
WHERE typstorage <> attstorage
- AND attrelid = $1
+ AND attrelid = relid
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum
- ) T
- ) T
-WHERE array_upper(column_storage , 1) > 0
+ ) T
+ ) T
+ WHERE array_upper(column_storage , 1) > 0;
+
+ IF sql IS NOT NULL THEN
+ EXECUTE sql;
+ END IF;
+END
$$
-LANGUAGE sql STABLE STRICT;
+LANGUAGE plpgsql;
-- includes not only PRIMARY KEYS but also UNIQUE NOT NULL keys
DO $$
@@ -285,55 +355,130 @@ END;
$$;
CREATE VIEW repack.tables AS
- SELECT repack.oid2text(R.oid) AS relname,
- R.oid AS relid,
- R.reltoastrelid AS reltoastrelid,
- CASE WHEN R.reltoastrelid = 0 THEN 0 ELSE (
- SELECT indexrelid FROM 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,
- 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,
- 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_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
+ SELECT repack.oid2text(R.oid) AS relname,
+ R.oid AS relid,
+ N.nspname AS schemaname,
+ PK.indexrelid AS pkid,
+ CK.indexrelid AS ckid,
+ coalesce(S.spcname, S2.spcname) AS tablespace
FROM pg_class R
- LEFT JOIN 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
- 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
- CROSS JOIN (SELECT S2.spcname
+ LEFT JOIN repack.primary_keys PK
+ ON R.oid = PK.indrelid
+ LEFT JOIN (SELECT CKI.* FROM pg_index CKI, 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
+ CROSS JOIN (SELECT S2.spcname
FROM pg_catalog.pg_database D
JOIN pg_catalog.pg_tablespace S2 ON S2.oid = D.dattablespace
WHERE D.datname = current_database()) S2
- WHERE R.relkind = 'r'
- AND R.relpersistence = 'p'
- AND N.nspname NOT IN ('pg_catalog', 'information_schema')
- AND N.nspname NOT LIKE E'pg\\_temp\\_%';
+ WHERE R.relkind = 'r'
+ AND R.relpersistence = 'p'
+ AND N.nspname NOT IN ('pg_catalog', 'information_schema')
+ AND N.nspname !~ '^pg_temp_';
+
+CREATE FUNCTION repack.get_tables(dest_tablespace name, use_original_schema boolean)
+RETURNS TABLE (
+ relname text,
+ relid oid,
+ schemaname name,
+ temp_schemaname name,
+ pkid oid,
+ ckid oid,
+ create_pktype text,
+ create_log text,
+ create_trigger text,
+ enable_trigger text,
+ create_table text,
+ copy_data text,
+ alter_col_storage text,
+ drop_columns text,
+ delete_log text,
+ lock_table text,
+ lock_temp_table text,
+ ckey text,
+ sql_peek text,
+ sql_insert text,
+ sql_delete text,
+ sql_update text,
+ sql_pop text
+) AS $$
+ SELECT R.relname,
+ R.relid,
+ R.schemaname,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.pkid,
+ R.ckid,
+ format('SELECT repack.create_index_type(%L, %L, %L)',
+ R.relid, R.pkid,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END
+ ) AS create_pktype,
+ format('SELECT repack.create_log_table(%L, %L)',
+ R.relid,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END
+ ) AS create_log,
+ format('SELECT repack.create_trigger(%L, %L, %L)',
+ R.relid, R.pkid,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END
+ ) AS create_trigger,
+ format('ALTER TABLE %s ENABLE ALWAYS TRIGGER repack_trigger',
+ repack.oid2text(R.relid)) AS enable_trigger,
+ format('SELECT repack.create_table(%L, %L, %L)',
+ R.relid,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ coalesce(dest_tablespace, R.tablespace)
+ ) AS create_table,
+ format('INSERT INTO %I.table_%s SELECT %s FROM ONLY %s',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid,
+ repack.get_columns_for_create_as(R.relid),
+ repack.oid2text(R.relid)
+ ) AS copy_data,
+ format('SELECT repack.alter_col_storage(%L, %L)',
+ R.relid,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END
+ ) AS alter_col_storage,
+ format('SELECT repack.drop_columns(%L, %L)',
+ R.relid,
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END
+ ) AS drop_columns,
+ format('DELETE FROM %I.log_%s',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid) AS delete_log,
+ format('LOCK TABLE %s IN ACCESS EXCLUSIVE MODE',
+ repack.oid2text(R.relid)) AS lock_table,
+ format('LOCK TABLE %I.table_%s IN ACCESS EXCLUSIVE MODE',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid) AS lock_temp_table,
+ repack.get_order_by(R.ckid) AS ckey,
+ format('SELECT * FROM %I.log_%s ORDER BY id LIMIT $1',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid) AS sql_peek,
+ format('INSERT INTO %I.table_%s VALUES($1.*)',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid) AS sql_insert,
+ format('DELETE FROM %I.table_%s WHERE %s',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid,
+ repack.get_compare_pkey(R.pkid, '$1')
+ ) AS sql_delete,
+ format('UPDATE %I.table_%s SET %s WHERE %s',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid,
+ repack.get_assign(R.relid, '$2'),
+ repack.get_compare_pkey(R.pkid, '$1')
+ ) AS sql_update,
+ format('DELETE FROM %I.log_%s WHERE id IN (',
+ CASE WHEN use_original_schema THEN R.schemaname ELSE 'repack' END,
+ R.relid) AS sql_pop
+ FROM repack.tables R;
+$$ LANGUAGE sql STABLE;
-CREATE FUNCTION repack.repack_indexdef(oid, oid, name, bool) RETURNS text AS
+CREATE FUNCTION repack.repack_indexdef(oid, text, name, bool) RETURNS text AS
'MODULE_PATHNAME', 'repack_indexdef'
LANGUAGE C STABLE;
@@ -365,11 +510,11 @@ RETURNS integer AS
'MODULE_PATHNAME', 'repack_apply'
LANGUAGE C VOLATILE;
-CREATE FUNCTION repack.repack_swap(oid) RETURNS void AS
+CREATE FUNCTION repack.repack_swap(oid, oid) RETURNS void AS
'MODULE_PATHNAME', 'repack_swap'
LANGUAGE C VOLATILE STRICT;
-CREATE FUNCTION repack.repack_drop(oid, int) RETURNS void AS
+CREATE FUNCTION repack.repack_drop(oid, name, int) RETURNS void AS
'MODULE_PATHNAME', 'repack_drop'
LANGUAGE C VOLATILE STRICT;
diff --git a/lib/repack.c b/lib/repack.c
index 6aa519b..84c8556 100644
--- a/lib/repack.c
+++ b/lib/repack.c
@@ -155,8 +155,9 @@ repack_version(PG_FUNCTION_ARGS)
* @fn Datum repack_trigger(PG_FUNCTION_ARGS)
* @brief Insert a operation log into log-table.
*
- * repack_trigger(column1, ..., columnN)
+ * repack_trigger(temp_schema, column1, ..., columnN)
*
+ * @param temp_schema A schema where temporary objects are stored.
* @param column1 A column of the table in primary key/unique index.
* ...
* @param columnN A column of the table in primary key/unique index.
@@ -172,6 +173,9 @@ repack_trigger(PG_FUNCTION_ARGS)
Oid argtypes[2];
Oid relid;
StringInfo sql;
+ char *temp_schema = trigdata->tg_trigger->tgargs[0];
+ char **columns = trigdata->tg_trigger->tgargs + 1;
+ int16 ncolumns = trigdata->tg_trigger->tgnargs - 1;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo) ||
@@ -213,12 +217,14 @@ repack_trigger(PG_FUNCTION_ARGS)
/* prepare INSERT query */
sql = makeStringInfo();
- appendStringInfo(sql, "INSERT INTO repack.log_%u(pk, row) "
- "VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW(", relid);
- appendStringInfo(sql, "$1.%s", quote_identifier(trigdata->tg_trigger->tgargs[0]));
- for (int i = 1; i < trigdata->tg_trigger->tgnargs; ++i)
- appendStringInfo(sql, ", $1.%s", quote_identifier(trigdata->tg_trigger->tgargs[i]));
- appendStringInfo(sql, ")::repack.pk_%u) END, $2)", relid);
+ appendStringInfo(sql, "INSERT INTO %s.log_%u(pk, row) "
+ "VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW(",
+ quote_identifier(temp_schema), relid);
+ appendStringInfo(sql, "$1.%s", quote_identifier(columns[0]));
+ for (int i = 1; i < ncolumns; ++i)
+ appendStringInfo(sql, ", $1.%s", quote_identifier(columns[i]));
+ appendStringInfo(sql, ")::%s.pk_%u) END, $2)",
+ quote_identifier(temp_schema), relid);
/* execute the INSERT query */
execute_with_args(SPI_OK_INSERT, sql->data, 2, argtypes, values, nulls);
@@ -374,7 +380,6 @@ typedef struct IndexDef
{
char *create; /* CREATE INDEX or CREATE UNIQUE INDEX */
char *index; /* index name including schema */
- char *table; /* table name including schema */
char *type; /* btree, hash, gist or gin */
char *columns; /* column definition */
char *options; /* options after columns, before TABLESPACE (e.g. COLLATE) */
@@ -569,11 +574,10 @@ skip_until(Oid index, char *sql, char end)
}
static void
-parse_indexdef(IndexDef *stmt, Oid index, Oid table)
+parse_indexdef(IndexDef *stmt, Oid index)
{
char *sql = pg_get_indexdef_string(index);
const char *idxname = get_quoted_relname(index);
- const char *tblname = get_relation_name(table);
const char *limit = strchr(sql, '\0');
/* CREATE [UNIQUE] INDEX */
@@ -584,9 +588,10 @@ parse_indexdef(IndexDef *stmt, Oid index, Oid table)
sql = skip_const(index, sql, idxname, NULL);
/* ON */
sql = skip_const(index, sql, "ON", NULL);
+ /* schema */
+ sql = skip_ident(index, sql);
/* table */
- stmt->table = sql;
- sql = skip_const(index, sql, tblname, NULL);
+ sql = skip_ident(index, sql);
/* USING */
sql = skip_const(index, sql, "USING", NULL);
/* type */
@@ -623,7 +628,6 @@ parse_indexdef(IndexDef *stmt, Oid index, Oid table)
elog(DEBUG2, "indexdef.create = %s", stmt->create);
elog(DEBUG2, "indexdef.index = %s", stmt->index);
- elog(DEBUG2, "indexdef.table = %s", stmt->table);
elog(DEBUG2, "indexdef.type = %s", stmt->type);
elog(DEBUG2, "indexdef.columns = %s", stmt->columns);
elog(DEBUG2, "indexdef.options = %s", stmt->options);
@@ -671,14 +675,12 @@ parse_indexdef_col(char *token, char **desc, char **nulls, char **collate)
* repack_get_order_by(index, table)
*
* @param index Oid of target index.
- * @param table Oid of table of the index.
* @retval Create index DDL for temp table.
*/
Datum
repack_get_order_by(PG_FUNCTION_ARGS)
{
Oid index = PG_GETARG_OID(0);
- Oid table = PG_GETARG_OID(1);
IndexDef stmt;
char *token;
char *next;
@@ -686,7 +688,7 @@ repack_get_order_by(PG_FUNCTION_ARGS)
Relation indexRel = NULL;
int nattr;
- parse_indexdef(&stmt, index, table);
+ parse_indexdef(&stmt, index);
/*
* FIXME: this is very unreliable implementation but I don't want to
@@ -771,10 +773,10 @@ repack_get_order_by(PG_FUNCTION_ARGS)
* @fn Datum repack_indexdef(PG_FUNCTION_ARGS)
* @brief Reproduce DDL that create index at the temp table.
*
- * repack_indexdef(index, table)
+ * repack_indexdef(index, tablename, tablespace, concurrent_index)
*
* @param index Oid of target index.
- * @param table Oid of table of the index.
+ * @param tablename Full name of table of the index.
* @param tablespace Namespace for the index. If NULL keep the original.
* @param boolean Whether to use CONCURRENTLY when creating the index.
* @retval Create index DDL for temp table.
@@ -783,7 +785,7 @@ Datum
repack_indexdef(PG_FUNCTION_ARGS)
{
Oid index;
- Oid table;
+ const char *tablename;
Name tablespace = NULL;
IndexDef stmt;
StringInfoData str;
@@ -793,20 +795,20 @@ repack_indexdef(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
index = PG_GETARG_OID(0);
- table = PG_GETARG_OID(1);
+ tablename = text_to_cstring(PG_GETARG_TEXT_PP(1));
if (!PG_ARGISNULL(2))
tablespace = PG_GETARG_NAME(2);
- parse_indexdef(&stmt, index, table);
+ parse_indexdef(&stmt, index);
initStringInfo(&str);
if (concurrent_index)
appendStringInfo(&str, "%s CONCURRENTLY index_%u ON %s USING %s (%s)%s",
- stmt.create, index, stmt.table, stmt.type, stmt.columns, stmt.options);
+ stmt.create, index, tablename, stmt.type, stmt.columns, stmt.options);
else
- appendStringInfo(&str, "%s index_%u ON repack.table_%u USING %s (%s)%s",
- stmt.create, index, table, stmt.type, stmt.columns, stmt.options);
+ appendStringInfo(&str, "%s index_%u ON %s USING %s (%s)%s",
+ stmt.create, index, tablename, stmt.type, stmt.columns, stmt.options);
/* specify the new tablespace or the original one if any */
if (tablespace || stmt.tablespace)
@@ -832,22 +834,24 @@ getoid(HeapTuple tuple, TupleDesc desc, int column)
* @brief Swapping relfilenode of tables and relation ids of toast tables
* and toast indexes.
*
- * repack_swap(oid, relname)
+ * repack_swap(oid, oid)
*
* TODO: remove useless CommandCounterIncrement().
*
* @param oid Oid of table of target.
+ * @param tempoid Oid of temporary table.
* @retval None.
*/
Datum
repack_swap(PG_FUNCTION_ARGS)
{
Oid oid = PG_GETARG_OID(0);
+ Oid tempoid = PG_GETARG_OID(1);
const char *relname = get_quoted_relname(oid);
const char *nspname = get_quoted_nspname(oid);
- Oid argtypes[1] = { OIDOID };
- bool nulls[1] = { 0 };
- Datum values[1];
+ Oid argtypes[2] = { OIDOID, OIDOID };
+ bool nulls[2] = { 0, 0 };
+ Datum values[2];
SPITupleTable *tuptable;
TupleDesc desc;
HeapTuple tuple;
@@ -870,6 +874,7 @@ repack_swap(PG_FUNCTION_ARGS)
/* swap relfilenode and dependencies for tables. */
values[0] = ObjectIdGetDatum(oid);
+ values[1] = ObjectIdGetDatum(tempoid);
execute_with_args(SPI_OK_SELECT,
"SELECT X.reltoastrelid, TX.indexrelid, X.relowner,"
" Y.oid, Y.reltoastrelid, TY.indexrelid, Y.relowner"
@@ -878,8 +883,8 @@ repack_swap(PG_FUNCTION_ARGS)
" pg_catalog.pg_class Y LEFT JOIN pg_catalog.pg_index TY"
" ON Y.reltoastrelid = TY.indrelid AND TY.indisvalid"
" WHERE X.oid = $1"
- " AND Y.oid = ('repack.table_' || X.oid)::regclass",
- 1, argtypes, values, nulls);
+ " AND Y.oid = $2",
+ 2, argtypes, values, nulls);
tuptable = SPI_tuptable;
desc = tuptable->tupdesc;
@@ -934,16 +939,16 @@ repack_swap(PG_FUNCTION_ARGS)
/* swap indexes. */
values[0] = ObjectIdGetDatum(oid);
+ values[1] = ObjectIdGetDatum(tempoid);
execute_with_args(SPI_OK_SELECT,
- "SELECT X.oid, Y.oid"
- " FROM pg_catalog.pg_index I,"
- " pg_catalog.pg_class X,"
- " pg_catalog.pg_class Y"
- " WHERE I.indrelid = $1"
- " AND I.indexrelid = X.oid"
- " AND I.indisvalid"
- " AND Y.oid = ('repack.index_' || X.oid)::regclass",
- 1, argtypes, values, nulls);
+ "SELECT IX.indexrelid, Y.oid"
+ " FROM pg_catalog.pg_index IX"
+ " JOIN pg_catalog.pg_class Y ON Y.relname = 'index_' || IX.indexrelid"
+ " JOIN pg_catalog.pg_index IY ON IY.indexrelid = Y.oid"
+ " WHERE IX.indrelid = $1"
+ " AND IX.indisvalid"
+ " AND IY.indrelid = $2",
+ 2, argtypes, values, nulls);
tuptable = SPI_tuptable;
desc = tuptable->tupdesc;
@@ -1044,16 +1049,19 @@ repack_swap(PG_FUNCTION_ARGS)
* @fn Datum repack_drop(PG_FUNCTION_ARGS)
* @brief Delete temporarily objects.
*
- * repack_drop(oid, relname)
+ * repack_drop(oid, temp_schemaname, numobj)
*
- * @param oid Oid of target table.
- * @retval None.
+ * @param oid Oid of target table.
+ * @param temp_schema A schema where temporary objects are stored.
+ * @param numobj Number of objects to drop.
+ * @retval None.
*/
Datum
repack_drop(PG_FUNCTION_ARGS)
{
Oid oid = PG_GETARG_OID(0);
- int numobj = PG_GETARG_INT32(1);
+ Name temp_schema = PG_GETARG_NAME(1);
+ int numobj = PG_GETARG_INT32(2);
const char *relname = get_quoted_relname(oid);
const char *nspname = get_quoted_nspname(oid);
bool trigger_exists = true;
@@ -1121,8 +1129,8 @@ repack_drop(PG_FUNCTION_ARGS)
{
execute_with_format(
SPI_OK_UTILITY,
- "DROP TABLE IF EXISTS repack.log_%u CASCADE",
- oid);
+ "DROP TABLE IF EXISTS %s.log_%u CASCADE",
+ quote_identifier(NameStr(*temp_schema)), oid);
--numobj;
}
@@ -1131,8 +1139,8 @@ repack_drop(PG_FUNCTION_ARGS)
{
execute_with_format(
SPI_OK_UTILITY,
- "DROP TYPE IF EXISTS repack.pk_%u",
- oid);
+ "DROP TYPE IF EXISTS %s.pk_%u",
+ quote_identifier(NameStr(*temp_schema)), oid);
--numobj;
}
@@ -1155,8 +1163,8 @@ repack_drop(PG_FUNCTION_ARGS)
{
execute_with_format(
SPI_OK_UTILITY,
- "DROP TABLE IF EXISTS repack.table_%u CASCADE",
- oid);
+ "DROP TABLE IF EXISTS %s.table_%u CASCADE",
+ quote_identifier(NameStr(*temp_schema)), oid);
--numobj;
}
diff --git a/regress/Makefile b/regress/Makefile
index b9462f7..d745cea 100644
--- a/regress/Makefile
+++ b/regress/Makefile
@@ -17,7 +17,7 @@ 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 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 publication original_schema
USE_PGXS = 1 # use pgxs if not in contrib directory
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/regress/expected/get_order_by.out b/regress/expected/get_order_by.out
index b9c2e5e..195aff1 100644
--- a/regress/expected/get_order_by.out
+++ b/regress/expected/get_order_by.out
@@ -3,7 +3,7 @@
--
CREATE TABLE issue3_1 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_1_idx ON issue3_1 (col1, col2 DESC);
-SELECT repack.get_order_by('issue3_1_idx'::regclass::oid, 'issue3_1'::regclass::oid);
+SELECT repack.get_order_by('issue3_1_idx'::regclass::oid);
get_order_by
-----------------
col1, col2 DESC
@@ -13,7 +13,7 @@ SELECT repack.get_order_by('issue3_1_idx'::regclass::oid, 'issue3_1'::regclass::
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);
+SELECT repack.get_order_by('issue3_2_idx'::regclass::oid);
get_order_by
---------------------------
col1 DESC, col2 USING ~<~
@@ -23,7 +23,7 @@ SELECT repack.get_order_by('issue3_2_idx'::regclass::oid, 'issue3_2'::regclass::
INFO: repacking table "public.issue3_2"
CREATE TABLE issue3_3 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_3_idx ON issue3_3 (col1 DESC, col2 DESC);
-SELECT repack.get_order_by('issue3_3_idx'::regclass::oid, 'issue3_3'::regclass::oid);
+SELECT repack.get_order_by('issue3_3_idx'::regclass::oid);
get_order_by
----------------------
col1 DESC, col2 DESC
@@ -33,7 +33,7 @@ SELECT repack.get_order_by('issue3_3_idx'::regclass::oid, 'issue3_3'::regclass::
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);
+SELECT repack.get_order_by('issue3_4_idx'::regclass::oid);
get_order_by
--------------------------------------------------
col1 NULLS FIRST, col2 DESC USING ~<~ NULLS LAST
@@ -43,7 +43,7 @@ SELECT repack.get_order_by('issue3_4_idx'::regclass::oid, 'issue3_4'::regclass::
INFO: repacking table "public.issue3_4"
CREATE TABLE issue3_5 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_5_idx ON issue3_5 (col1 DESC NULLS FIRST, col2 COLLATE "POSIX" DESC);
-SELECT repack.get_order_by('issue3_5_idx'::regclass::oid, 'issue3_5'::regclass::oid);
+SELECT repack.get_order_by('issue3_5_idx'::regclass::oid);
get_order_by
--------------------------------------
col1 DESC, col2 COLLATE "POSIX" DESC
@@ -56,7 +56,11 @@ INFO: repacking table "public.issue3_5"
--
CREATE TABLE issue321 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue321_idx ON issue321 (col1);
-SELECT repack.get_order_by('issue321_idx'::regclass::oid, 1);
-ERROR: table name not found for OID 1
-SELECT repack.get_order_by(1, 1);
+SELECT repack.get_order_by('issue321_idx'::regclass::oid);
+ get_order_by
+--------------
+ col1
+(1 row)
+
+SELECT repack.get_order_by(1);
ERROR: cache lookup failed for index 1
diff --git a/regress/expected/original_schema.out b/regress/expected/original_schema.out
new file mode 100644
index 0000000..d0eed49
--- /dev/null
+++ b/regress/expected/original_schema.out
@@ -0,0 +1,70 @@
+--
+-- Test repack by storing temporary objects in original schemas of target tables
+--
+-- Test that temporary objects are create in the original schema
+CREATE SCHEMA test_orig;
+CREATE TABLE test_orig.tbl (id integer PRIMARY KEY);
+INSERT INTO test_orig.tbl VALUES (1), (2), (3);
+-- Setup event trigger to verify where temporary objects are created
+CREATE TABLE public.audit_log (schema_name text, obj_ident text);
+CREATE OR REPLACE FUNCTION public.trg_audit_ddl()
+RETURNS event_trigger AS $$
+DECLARE
+ obj record;
+BEGIN
+ FOR obj IN
+ SELECT * FROM pg_event_trigger_ddl_commands()
+ WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE TYPE')
+ LOOP
+ IF obj.object_identity ~ '(log|table|pk)_' THEN
+ INSERT INTO public.audit_log
+ VALUES (obj.schema_name, regexp_replace(obj.object_identity, '\d+', 'OID', 'g'));
+ END IF;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER audit_ddl ON ddl_command_end
+ WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE TYPE')
+ EXECUTE PROCEDURE public.trg_audit_ddl();
+\! pg_repack --dbname=contrib_regression --table=test_orig.tbl --use-original-schema
+INFO: repacking table "test_orig.tbl"
+SELECT * FROM test_orig.tbl ORDER BY id;
+ id
+----
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM public.audit_log ORDER BY obj_ident;
+ schema_name | obj_ident
+-------------+---------------------
+ test_orig | test_orig.log_OID
+ test_orig | test_orig.pk_OID
+ test_orig | test_orig.table_OID
+(3 rows)
+
+-- Check that temporary objects were cleaned up
+SELECT relname
+FROM pg_class
+WHERE relnamespace = 'test_orig'::regnamespace AND relname ~ '^(log|table)_';
+ relname
+---------
+(0 rows)
+
+SELECT typname
+FROM pg_type
+WHERE typnamespace = 'test_orig'::regnamespace AND typname ~ '^pk_';
+ typname
+---------
+(0 rows)
+
+-- Cleanup
+DROP EVENT TRIGGER audit_ddl;
+DROP FUNCTION public.trg_audit_ddl();
+DROP TABLE public.audit_log;
+-- Test that --only-indexes doesn't support --use-original-schema
+\! pg_repack --dbname=contrib_regression --table=test_orig.tbl --use-original-schema --only-indexes
+WARNING: option --use-original-schema has no effect while repacking indexes
+INFO: repacking indexes of "test_orig.tbl"
+INFO: repacking index "test_orig.tbl_pkey"
diff --git a/regress/expected/tablespace_1.out b/regress/expected/tablespace_1.out
index 007dac4..44cbf1a 100644
--- a/regress/expected/tablespace_1.out
+++ b/regress/expected/tablespace_1.out
@@ -19,7 +19,7 @@ INSERT INTO testts1 (data) values ('b');
INSERT INTO testts1 (data) values ('c');
-- check the indexes definitions
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, NULL, false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -31,7 +31,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -43,7 +43,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', NULL, true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -55,7 +55,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', 'foo', true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -68,7 +68,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- Test that a tablespace is quoted as an identifier
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo bar', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo bar', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
diff --git a/regress/expected/tablespace_2.out b/regress/expected/tablespace_2.out
index f5a56a7..cdef4c3 100644
--- a/regress/expected/tablespace_2.out
+++ b/regress/expected/tablespace_2.out
@@ -19,7 +19,7 @@ INSERT INTO testts1 (data) values ('b');
INSERT INTO testts1 (data) values ('c');
-- check the indexes definitions
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, NULL, false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -31,7 +31,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -43,7 +43,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', NULL, true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -55,7 +55,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', 'foo', true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -68,7 +68,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- Test that a tablespace is quoted as an identifier
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo bar', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo bar', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
diff --git a/regress/expected/tablespace_3.out b/regress/expected/tablespace_3.out
index 4ff1bc4..8f91ee1 100644
--- a/regress/expected/tablespace_3.out
+++ b/regress/expected/tablespace_3.out
@@ -19,7 +19,7 @@ INSERT INTO testts1 (data) values ('b');
INSERT INTO testts1 (data) values ('c');
-- check the indexes definitions
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, NULL, false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -31,7 +31,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -43,7 +43,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', NULL, true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -55,7 +55,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', 'foo', true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -68,7 +68,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- Test that a tablespace is quoted as an identifier
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo bar', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo bar', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
diff --git a/regress/expected/tablespace_4.out b/regress/expected/tablespace_4.out
index d997029..b3262c0 100644
--- a/regress/expected/tablespace_4.out
+++ b/regress/expected/tablespace_4.out
@@ -19,7 +19,7 @@ INSERT INTO testts1 (data) values ('b');
INSERT INTO testts1 (data) values ('c');
-- check the indexes definitions
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, NULL, false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -31,7 +31,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -43,7 +43,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', NULL, true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -55,7 +55,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
(3 rows)
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', 'foo', true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
@@ -68,7 +68,7 @@ WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- Test that a tablespace is quoted as an identifier
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo bar', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo bar', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
diff --git a/regress/expected/trigger.out b/regress/expected/trigger.out
index 1ab71e6..8b0e9f7 100644
--- a/regress/expected/trigger.out
+++ b/regress/expected/trigger.out
@@ -3,10 +3,11 @@
--
CREATE TABLE trigger_t1 (a int, b int, primary key (a, b));
CREATE INDEX trigger_t1_idx ON trigger_t1 (a, b);
-SELECT create_trigger FROM repack.tables WHERE relname = 'public.trigger_t1';
- create_trigger
-----------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON public.trigger_t1 FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('a', 'b')
+SELECT regexp_replace(create_trigger, '\d+', 'OID', 'g')
+FROM repack.get_tables(NULL, false) WHERE relname = 'public.trigger_t1';
+ regexp_replace
+------------------------------------------------------
+ SELECT repack.create_trigger('OID', 'OID', 'repack')
(1 row)
SELECT oid AS t1_oid FROM pg_catalog.pg_class WHERE relname = 'trigger_t1'
@@ -14,7 +15,7 @@ SELECT oid AS t1_oid FROM pg_catalog.pg_class WHERE relname = 'trigger_t1'
CREATE TYPE repack.pk_:t1_oid AS (a integer, b integer);
CREATE TABLE repack.log_:t1_oid (id bigserial PRIMARY KEY, pk repack.pk_:t1_oid, row public.trigger_t1);
CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON trigger_t1
- FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('a', 'b');
+ FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('repack', 'a', 'b');
INSERT INTO trigger_t1 VALUES (111, 222);
UPDATE trigger_t1 SET a=333, b=444 WHERE a = 111;
DELETE FROM trigger_t1 WHERE a = 333;
diff --git a/regress/sql/get_order_by.sql b/regress/sql/get_order_by.sql
index 5b18e86..803b0cb 100644
--- a/regress/sql/get_order_by.sql
+++ b/regress/sql/get_order_by.sql
@@ -3,27 +3,27 @@
--
CREATE TABLE issue3_1 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_1_idx ON issue3_1 (col1, col2 DESC);
-SELECT repack.get_order_by('issue3_1_idx'::regclass::oid, 'issue3_1'::regclass::oid);
+SELECT repack.get_order_by('issue3_1_idx'::regclass::oid);
\! pg_repack --dbname=contrib_regression --table=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);
+SELECT repack.get_order_by('issue3_2_idx'::regclass::oid);
\! pg_repack --dbname=contrib_regression --table=issue3_2
CREATE TABLE issue3_3 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_3_idx ON issue3_3 (col1 DESC, col2 DESC);
-SELECT repack.get_order_by('issue3_3_idx'::regclass::oid, 'issue3_3'::regclass::oid);
+SELECT repack.get_order_by('issue3_3_idx'::regclass::oid);
\! pg_repack --dbname=contrib_regression --table=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);
+SELECT repack.get_order_by('issue3_4_idx'::regclass::oid);
\! pg_repack --dbname=contrib_regression --table=issue3_4
CREATE TABLE issue3_5 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue3_5_idx ON issue3_5 (col1 DESC NULLS FIRST, col2 COLLATE "POSIX" DESC);
-SELECT repack.get_order_by('issue3_5_idx'::regclass::oid, 'issue3_5'::regclass::oid);
+SELECT repack.get_order_by('issue3_5_idx'::regclass::oid);
\! pg_repack --dbname=contrib_regression --table=issue3_5
--
@@ -31,5 +31,5 @@ SELECT repack.get_order_by('issue3_5_idx'::regclass::oid, 'issue3_5'::regclass::
--
CREATE TABLE issue321 (col1 int NOT NULL, col2 text NOT NULL);
CREATE UNIQUE INDEX issue321_idx ON issue321 (col1);
-SELECT repack.get_order_by('issue321_idx'::regclass::oid, 1);
-SELECT repack.get_order_by(1, 1);
+SELECT repack.get_order_by('issue321_idx'::regclass::oid);
+SELECT repack.get_order_by(1);
diff --git a/regress/sql/original_schema.sql b/regress/sql/original_schema.sql
new file mode 100644
index 0000000..4518568
--- /dev/null
+++ b/regress/sql/original_schema.sql
@@ -0,0 +1,59 @@
+--
+-- Test repack by storing temporary objects in original schemas of target tables
+--
+
+-- Test that temporary objects are create in the original schema
+
+CREATE SCHEMA test_orig;
+
+CREATE TABLE test_orig.tbl (id integer PRIMARY KEY);
+INSERT INTO test_orig.tbl VALUES (1), (2), (3);
+
+-- Setup event trigger to verify where temporary objects are created
+CREATE TABLE public.audit_log (schema_name text, obj_ident text);
+
+CREATE OR REPLACE FUNCTION public.trg_audit_ddl()
+RETURNS event_trigger AS $$
+DECLARE
+ obj record;
+BEGIN
+ FOR obj IN
+ SELECT * FROM pg_event_trigger_ddl_commands()
+ WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE TYPE')
+ LOOP
+ IF obj.object_identity ~ '(log|table|pk)_' THEN
+ INSERT INTO public.audit_log
+ VALUES (obj.schema_name, regexp_replace(obj.object_identity, '\d+', 'OID', 'g'));
+ END IF;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER audit_ddl ON ddl_command_end
+ WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE TYPE')
+ EXECUTE PROCEDURE public.trg_audit_ddl();
+
+\! pg_repack --dbname=contrib_regression --table=test_orig.tbl --use-original-schema
+
+SELECT * FROM test_orig.tbl ORDER BY id;
+
+SELECT * FROM public.audit_log ORDER BY obj_ident;
+
+-- Check that temporary objects were cleaned up
+SELECT relname
+FROM pg_class
+WHERE relnamespace = 'test_orig'::regnamespace AND relname ~ '^(log|table)_';
+
+SELECT typname
+FROM pg_type
+WHERE typnamespace = 'test_orig'::regnamespace AND typname ~ '^pk_';
+
+-- Cleanup
+DROP EVENT TRIGGER audit_ddl;
+DROP FUNCTION public.trg_audit_ddl();
+DROP TABLE public.audit_log;
+
+-- Test that --only-indexes doesn't support --use-original-schema
+
+\! pg_repack --dbname=contrib_regression --table=test_orig.tbl --use-original-schema --only-indexes
+
diff --git a/regress/sql/tablespace.sql b/regress/sql/tablespace.sql
index 8f770fd..cce6cd7 100644
--- a/regress/sql/tablespace.sql
+++ b/regress/sql/tablespace.sql
@@ -18,32 +18,32 @@ INSERT INTO testts1 (data) values ('c');
-- check the indexes definitions
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, NULL, false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, NULL, true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', NULL, true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo', true),
+ repack.repack_indexdef(indexrelid, 'public.testts1', 'foo', true),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
-- Test that a tablespace is quoted as an identifier
SELECT regexp_replace(
- repack.repack_indexdef(indexrelid, 'testts1'::regclass, 'foo bar', false),
+ repack.repack_indexdef(indexrelid, 'repack.table_' || 'testts1'::regclass::oid, 'foo bar', false),
'_[0-9]+', '_OID', 'g')
FROM pg_index i join pg_class c ON c.oid = indexrelid
WHERE indrelid = 'testts1'::regclass ORDER BY relname;
diff --git a/regress/sql/trigger.sql b/regress/sql/trigger.sql
index ab4ae6e..d3753ff 100644
--- a/regress/sql/trigger.sql
+++ b/regress/sql/trigger.sql
@@ -5,7 +5,8 @@
CREATE TABLE trigger_t1 (a int, b int, primary key (a, b));
CREATE INDEX trigger_t1_idx ON trigger_t1 (a, b);
-SELECT create_trigger FROM repack.tables WHERE relname = 'public.trigger_t1';
+SELECT regexp_replace(create_trigger, '\d+', 'OID', 'g')
+FROM repack.get_tables(NULL, false) WHERE relname = 'public.trigger_t1';
SELECT oid AS t1_oid FROM pg_catalog.pg_class WHERE relname = 'trigger_t1'
\gset
@@ -13,7 +14,7 @@ SELECT oid AS t1_oid FROM pg_catalog.pg_class WHERE relname = 'trigger_t1'
CREATE TYPE repack.pk_:t1_oid AS (a integer, b integer);
CREATE TABLE repack.log_:t1_oid (id bigserial PRIMARY KEY, pk repack.pk_:t1_oid, row public.trigger_t1);
CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON trigger_t1
- FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('a', 'b');
+ FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('repack', 'a', 'b');
INSERT INTO trigger_t1 VALUES (111, 222);
UPDATE trigger_t1 SET a=333, b=444 WHERE a = 111;
view thread (5+ messages) latest in thread
Message-ID: <gh-reorg-pg_repack-482@github.com>
Permalink: ../gh-reorg-pg_repack-482@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