pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
[reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
9+ messages / 2 participants
[nested] [flat]

* [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-17 10:56  "Melkij (@Melkij)" <noreply+Melkij@github.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Melkij (@Melkij) @ 2026-03-17 10:56 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

pg_repack is aborted with error if a publication FOR ALL TABLES exists and apply_log requires deleting or updating a row in a temporary table.

Reproduction:
```
create table foo(id int primary key, val text);
insert into foo values (1, 'hi!');
create publication foo for all tables ;
# start pg_repack with break at rebuild_indexes
update foo set val = 'h2' where id = 1;
# continue pg_repack process and see error like this:

ERROR: query failed: ERROR:  cannot update table "table_16388" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
CONTEXT:  SQL statement "UPDATE repack.table_16388 SET (id, val) = ($2.id, $2.val) WHERE (id) = ($1.id)"
DETAIL: query was: SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
```

It's not that difficult to format `ALTER TABLE repack.table_' || R.oid || ' REPLICA IDENTITY USING INDEX index_' || PK.indexrelid` in repack.tables, but for this to work, I also need to set NOT NULL on columns, which is less trivial. Furthermore, repack might break subscribers of this publication.

Given the lack of previous bug reports (this setup never worked since introduction of publications in pg10), I suggest adding a check for publications FOR ALL TABLES and simply refusing to work in such a database by default.

inline patch 1: .github/workflows/regression.yml, bin/pg_repack.c, doc/pg_repack.rst and 4 more (+76/-2 lines) download inline-1.patchdiff --git a/.github/workflows/regression.yml b/.github/workflows/regression.yml index b6ff888f..3d1be41e 100644 --- a/.github/workflows/regression.yml +++ b/.github/workflows/regression.yml @@ -25,7 +25,7 @@ jobs: steps: - name: Start PostgreSQL ${{ matrix.pg }} - run: pg-start ${{ matrix.pg }} + run: CREATE_OPTIONS="--pgoption wal_level=logical" pg-start ${{ matrix.pg }} - name: Install build-dependencies run: apt-get install -y liblz4-dev libreadline-dev zlib1g-dev libzstd-dev diff --git a/bin/pg_repack.c b/bin/pg_repack.c index ac8453bb..1c930d10 100644 --- a/bin/pg_repack.c +++ b/bin/pg_repack.c @@ -258,6 +258,7 @@ static unsigned int temp_obj_num = 0; /* temporary objects counter */ static bool no_kill_backend = false; /* abandon when timed-out */ static bool no_superuser_check = false; static SimpleStringList exclude_extension_list = {NULL, NULL}; /* don't repack tables of these extensions */ +static bool no_error_on_publication = false; /* repack even though publication FOR ALL TABLES is found */ 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 */ @@ -292,6 +293,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', 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 }, { 'i', 2, "apply-count", &apply_count }, @@ -559,6 +561,42 @@ preliminary_checks(char *errbuf, size_t errsize){ } CLEARPGRES(res); + /* + * repack will be aborted with error like + * + * ERROR: cannot update table "table_16388" because it does not + * have a replica identity and publishes updates + * + * if a publication FOR ALL TABLES exists and apply_log requires + * deleting or updating a row in a temp table. To make this works, + * we need to set NOT NULL on columns of primary key and command + * + * ALTER TABLE repack.table_' || R.oid || ' REPLICA IDENTITY USING INDEX index_' || PK.indexrelid + * + * In addition, repack might break subscribers of this publication. + * For now, we simply refuse to continue. + */ + if (PQserverVersion(connection) >= 100000 && !no_error_on_publication) + { + res = execute_elevel("SELECT pubname FROM pg_publication WHERE puballtables LIMIT 1", + 0, NULL, DEBUG2); + if (PQresultStatus(res) == PGRES_TUPLES_OK) + { + if (PQntuples(res) > 0) + { + const char *pubname; + pubname = getstr(res, 0, 0); + snprintf(errbuf, errsize, "publication \"%s\" FOR ALL TABLES found: won't be able to apply concurrent UPDATE and DELETE", pubname); + goto cleanup; + } + /* no publications, continue to work */ + } else { + if (errbuf) + snprintf(errbuf, errsize, "%s", PQerrorMessage(connection)); + goto cleanup; + } + CLEARPGRES(res); + } /* Disable statement timeout. */ command("SET statement_timeout = 0", 0, NULL); @@ -2450,6 +2488,7 @@ pgut_help(bool details) printf(" -Z, --no-analyze don't analyze at end\n"); printf(" -k, --no-superuser-check skip superuser checks in client\n"); printf(" -C, --exclude-extension don't repack tables which belong to specific extension\n"); + printf(" --no-error-on-publication repack even though a publication FOR ALL TABLES is found\n"); printf(" --no-error-on-invalid-index repack even though invalid index is found\n"); printf(" --error-on-invalid-index don't repack when invalid index is found, deprecated, as this is the default behavior now\n"); printf(" --apply-count number of tuples to apply in one transaction during replay\n"); diff --git a/doc/pg_repack.rst b/doc/pg_repack.rst index a416e554..398f782e 100644 --- a/doc/pg_repack.rst +++ b/doc/pg_repack.rst @@ -126,6 +126,7 @@ Options: -Z, --no-analyze don't analyze at end -k, --no-superuser-check skip superuser checks in client -C, --exclude-extension don't repack tables which belong to specific extension + --no-error-on-publication repack even though publication for all tables is found --no-error-on-invalid-index repack even though invalid index is found --error-on-invalid-index don't repack when invalid index is found, deprecated, as this is the default behavior now --apply-count number of tuples to apply in one trasaction during replay diff --git a/regress/Makefile b/regress/Makefile index bf6edcb4..b9462f79 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 +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 USE_PGXS = 1 # use pgxs if not in contrib directory PGXS := $(shell $(PG_CONFIG) --pgxs) diff --git a/regress/expected/publication.out b/regress/expected/publication.out new file mode 100644 index 00000000..3fc6a498 --- /dev/null +++ b/regress/expected/publication.out @@ -0,0 +1,9 @@ +-- +-- repack will conflict with publication FOR ALL TABLES +-- +create publication test for all tables ; +\! pg_repack --dbname=contrib_regression --table=tbl_cluster +ERROR: pg_repack failed with error: publication "test" FOR ALL TABLES found: won't be able to apply concurrent UPDATE and DELETE +\! pg_repack --dbname=contrib_regression --table=tbl_cluster --no-error-on-publication +INFO: repacking table "public.tbl_cluster" +drop publication test; diff --git a/regress/expected/publication_1.out b/regress/expected/publication_1.out new file mode 100644 index 00000000..22524a77 --- /dev/null +++ b/regress/expected/publication_1.out @@ -0,0 +1,15 @@ +-- +-- repack will conflict with publication FOR ALL TABLES +-- +create publication test for all tables ; +ERROR: syntax error at or near "publication" +LINE 1: create publication test for all tables ; + ^ +\! pg_repack --dbname=contrib_regression --table=tbl_cluster +INFO: repacking table "public.tbl_cluster" +\! pg_repack --dbname=contrib_regression --table=tbl_cluster --no-error-on-publication +INFO: repacking table "public.tbl_cluster" +drop publication test; +ERROR: syntax error at or near "publication" +LINE 1: drop publication test; + ^ diff --git a/regress/sql/publication.sql b/regress/sql/publication.sql new file mode 100644 index 00000000..08f8629a --- /dev/null +++ b/regress/sql/publication.sql @@ -0,0 +1,10 @@ +-- +-- repack will conflict with publication FOR ALL TABLES +-- + +create publication test for all tables ; + +\! pg_repack --dbname=contrib_regression --table=tbl_cluster +\! pg_repack --dbname=contrib_regression --table=tbl_cluster --no-error-on-publication + +drop publication test;
^ permalink raw [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-19 11:54  "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: za-arthur (@za-arthur) @ 2026-03-19 11:54 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

(on bin/pg_repack.c)

I suggest a minor change of formatting, mainly changing white spaces to tabs (which is used by most of the code):
```suggestion
	/*
	 * repack will be aborted with error like
	 *
	 * ERROR:  cannot update table "table_16388" because it does not
	 * have a replica identity and publishes updates
	 *
	 * if a publication FOR ALL TABLES exists and apply_log requires
	 * deleting or updating a row in a temp table. To make this works,
	 * we need to set NOT NULL on columns of primary key and command
	 *
	 * ALTER TABLE repack.table_' || R.oid || ' REPLICA IDENTITY USING INDEX index_' || PK.indexrelid
	 *
	 * In addition, repack might break subscribers of this publication.
	 * For now, we simply refuse to continue.
	 */
	if (PQserverVersion(connection) >= 100000 && !no_error_on_publication)
	{
		res = execute_elevel("SELECT pubname FROM pg_publication WHERE puballtables LIMIT 1",
			0, NULL, DEBUG2);
		if (PQresultStatus(res) == PGRES_TUPLES_OK)
		{
			if (PQntuples(res) > 0)
			{
				const char *pubname;
				pubname = getstr(res, 0, 0);
				snprintf(errbuf, errsize, "publication \"%s\" FOR ALL TABLES found: won't be able to apply concurrent UPDATE and DELETE", pubname);
				goto cleanup;
			}
			/* no publications, continue to work */
		} else {
			if (errbuf)
				snprintf(errbuf, errsize, "%s", PQerrorMessage(connection));
			goto cleanup;
		}
		CLEARPGRES(res);
	}
```

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-19 11:57  "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: za-arthur (@za-arthur) @ 2026-03-19 11:57 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

Makes sense. Thank you! Could you add new option to the documentation as well?
I have minor formatting suggestion below as well.

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-20 08:24  "Melkij (@Melkij)" <noreply+Melkij@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: Melkij (@Melkij) @ 2026-03-20 08:24 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

Thanks, I made the changes

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-20 08:26  "Melkij (@Melkij)" <noreply+Melkij@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: Melkij (@Melkij) @ 2026-03-20 08:26 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

I was in a hurry, I'll add a little more now...

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-20 08:29  "Melkij (@Melkij)" <noreply+Melkij@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: Melkij (@Melkij) @ 2026-03-20 08:29 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

Sorry for the noise, that's correct. _I'll go get some coffee..._

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481 (approved): pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-20 10:53  "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: za-arthur (@za-arthur) @ 2026-03-20 10:53 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

Thank you for the fixes. LGTM.

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-20 21:19  "Melkij (@Melkij)" <noreply+Melkij@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: Melkij (@Melkij) @ 2026-03-20 21:19 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

@Melkij closed this.

^ permalink  raw  [nested|flat] 9+ messages in thread

* Re: [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist
@ 2026-03-20 21:19  "Melkij (@Melkij)" <noreply+Melkij@github.com>
  7 siblings, 0 replies; 9+ messages in thread

From: Melkij (@Melkij) @ 2026-03-20 21:19 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

@Melkij merged this (commit b209a08d01d7).

^ permalink  raw  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2026-03-20 21:19 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-17 10:56 [reorg/pg_repack] PR #481: pg_repack is aborted with error if a publication FOR ALL TABLES exist "Melkij (@Melkij)" <noreply+Melkij@github.com>
2026-03-19 11:54 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-03-19 11:57 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-03-20 08:24 ` "Melkij (@Melkij)" <noreply+Melkij@github.com>
2026-03-20 08:26 ` "Melkij (@Melkij)" <noreply+Melkij@github.com>
2026-03-20 08:29 ` "Melkij (@Melkij)" <noreply+Melkij@github.com>
2026-03-20 10:53 ` Re: [reorg/pg_repack] PR #481 (approved): pg_repack is aborted with error if a publication FOR ALL TABLES exist "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-03-20 21:19 ` "Melkij (@Melkij)" <noreply+Melkij@github.com>
2026-03-20 21:19 ` "Melkij (@Melkij)" <noreply+Melkij@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