Error if the table is renamed concurrently during repacking

[JohnHien]

I executed the following command with pg_repack v1.5.0 on PG14:

pg_repack --dbname=ddl_full0 -k --table=normal.t1 --jobs=3

Concurrent DMLs were executing during the repack process and they failed with error that the table repack.log_26813 didn't exist.

2024-04-11 01:27:50.933 UTC 33214 ddl_full0  42P01 2356 ERROR:  relation "repack.log_26813" does not exist at character 13
2024-04-11 01:27:50.933 UTC 33214 ddl_full0  42P01 2356 QUERY:  INSERT INTO repack.log_26813(pk, row) VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id, $1.e)::repack.pk_26813) END, $2)
2024-04-11 01:27:50.933 UTC 33214 ddl_full0  42P01 2356 STATEMENT:  DELETE FROM t1 WHERE id BETWEEN ($1)::INTEGER AND ($2)::INTEGER;

I found log records of creating the log table but, the table's name was repack.log_26797 instead of repack.log_26813, referencing t2 instead of t1:

2024-04-11 01:27:50.070 UTC 35477 ddl_full0  00000 2328 CONTEXT:  SQL statement "CREATE TABLE repack.log_26797 (id bigserial PRIMARY KEY, pk repack.pk_26797, row normal.t2)"

Its the name for repack.table_26797 and it also referenced t2, not t1:

2024-04-11 01:27:50.698 UTC 35477 ddl_full0  00000 2346 CONTEXT:  SQL statement "CREATE TABLE repack.table_26797 WITH (toast_tuple_target=128, oids = false)  TABLESPACE pg_default AS SELECT id,unique1,hundred,tenthous,a,b,c,d,f_char,g_vchar,h_bool,i_enum,j_array_bigint,extra,e FROM ONLY normal.t2 WITH NO DATA"

In the repack_one_table function, while reading configs from the repack.tables view, it used the t1 table to filter. However, when it came time to actually create the log table, it had changed to t2.

2024-04-11 01:26:26.524 UTC 35477 ddl_full0  00000 0 LOG:  execute <unnamed>: /*pg_catalog, pg_temp, public*/ SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t,  (VALUES (quote_ident(NULL::text))) as v (tablespace) WHERE (relid = $$normal.t1$$::regclass) ORDER BY t.relname, t.schemaname

The reason for t1 becoming t2 was due to concurrent DDLs I ran on my testing system, and one of the table renaming tests had swapped t1 and t2:

2024-04-11 01:27:49.690 UTC 33232 ddl_full0  00000 2198 LOG:  execute <unnamed>: /*normal, public*/ DO $$
BEGIN
  DROP TABLE IF EXISTS t2_tmp;
  ALTER TABLE t2 RENAME TO t2_tmp;
  DROP TABLE IF EXISTS t2;
  ALTER TABLE t1 RENAME TO t2;
  DROP TABLE IF EXISTS t1;
  ALTER TABLE t2_tmp RENAME TO t1;
END $$;

Maybe the root cause is that the functions repack.create_log_table and repack.create_table accept an OID parameter, and the OID is only converted to a table name by the repack.oid2text function when the CREATE TABLE statement is actually executed. The OID is still that of the old t1 table, but by the time repack.oid2text is executed, this OID belongs to the new t2.

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) || ')';
END
$$
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';
END
$$
LANGUAGE plpgsql;

Such a case is unlikely in the actual use of pg_repack because it's too extreme. However, since I've encountered this issue, I decided to report it for a better pg_repack. Thanks.