Repack without trigger

[mgagliardo91]

We have a unique scenario where we are looking to repack an active table that gets frequent reads, but all of its writes are controlled and infrequent. The table, due to its creation from another extension, does not support triggers but in this case we do not actually need to worry about keeping track of replay logs. Our main goal is to benefit from pg_repack's ability to copy live (with handling of locks) and then ultimately swap carefully to ensure that nothing is broken out from under it.

Is there any simple way to run pg_repack, ignoring the steps to create/activate the replay log trigger?

Thanks!

[andreasscherbaum]

Not really, I think.

What you can do is start a transaction, get a write lock on the table, and then INSERT-SELECT into a new table. Then rename it. All in one transaction.

[za-arthur]

You can try to redefine functions repack.get_create_trigger() and repack.get_enable_trigger():

CREATE OR REPLACE FUNCTION repack.get_create_trigger(relid oid, pkid oid)
  RETURNS text AS
$$
  SELECT 'SELECT 1';
$$
LANGUAGE sql STABLE STRICT;

CREATE OR REPLACE FUNCTION repack.get_enable_trigger(relid oid)
  RETURNS text AS
$$
  SELECT 'SELECT 1';
$$                                          
LANGUAGE sql STABLE STRICT;

or repack.tables view:

pg_repack/lib/pg_repack.sql.in

Lines 300 to 301 in 6a3c282

	repack.get_create_trigger(R.oid, PK.indexrelid) AS create_trigger,
	repack.get_enable_trigger(R.oid) as enable_trigger,

It works when I do a simple test. But we don't generally test such case.

[mgagliardo91]

I was able to comment out the create_trigger and enable_trigger calls and see it work - I appreciate the quick comments. One other related question, if replay logs/trigger is not a concern for our use case, does the requirement for having a table PK/not-null column still stand? Is that requirement for the trigger or is it also used during the live copy command?

[za-arthur]

AFAIK a PK/unique index is used by DELETE/UPDATE operations during applying log:

pg_repack/lib/pg_repack.sql.in

Lines 312 to 313 in 6a3c282

	'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,

You can try to redefine repack.primary_keys view or delete it from repack.tables view:

pg_repack/lib/pg_repack.sql.in

Lines 317 to 318 in 6a3c282

	LEFT JOIN repack.primary_keys PK
	ON R.oid = PK.indrelid