From: Tom Lane <tgl@sss.pgh.pa.us>
To: liam@morland.ca
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18151: pg_upgradecluster fails when column default refers to column
Date: Mon, 09 Oct 2023 10:11:31 -0400
Message-ID: <137188.1696860691@sss.pgh.pa.us> (raw)
In-Reply-To: <18151-cdc2191b130172c7@postgresql.org>
References: <18151-cdc2191b130172c7@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> I was trying to upgrade to PG 16:
> pg_upgradecluster 15 main
> I got this error:
> pg_restore: error: could not execute query: ERROR: relation "event" does
> not exist
> The reason is the the default value for column "event_id" of table "event"
> is "event_id_nextval()". That function is:
> SELECT COALESCE(MAX(event_id) + 1, 1) FROM event;
Thanks for the report!
> I do not understand why it would have to execute "event_id_nextval()" to do
> the restore.
It shouldn't. I think this might be a variant of the bug recently
reported here:
https://www.postgresql.org/message-id/flat/75a7b7483aeb331aa017328d606d568fc715b90d.camel%40cybertec...
However, that doesn't seem quite right because that'd result in a useless
extra evaluation during COPY to the table, at which time the table
surely must exist. Also, pg_upgrade shouldn't need to use COPY at all.
Is that function written in old-style (with a string literal for the
body) or new-style with BEGIN ATOMIC? In the latter case it's possible
that you've got a circular dependency that pg_dump is failing to work
around.
Can you show us the exact DDL definition of both the table and the
function?
> A related issue: It would have been better if pg_upgradecluster had not
> output a success message and switched to the new cluster. It should have
> detected the error, output an error message, and left the old cluster
> running.
As far as that goes, you'd have to complain to the Debian maintainers
of pg_upgradecluster. That code doesn't belong to the core project.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, liam@morland.ca, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18151: pg_upgradecluster fails when column default refers to column
In-Reply-To: <137188.1696860691@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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