From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: liam@morland.ca
Subject: BUG #18151: pg_upgradecluster fails when column default refers to column
Date: Sun, 08 Oct 2023 17:41:14 +0000
Message-ID: <18151-cdc2191b130172c7@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18151
Logged by: Liam Morland
Email address: liam@morland.ca
PostgreSQL version: 15.4
Operating system: Debian
Description:
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;
I suppose that what is happening is a circular dependency, the table "event"
does not exist yet, because it is in the process of restoring it. I was able
to clear the error by setting the column default to NULL, running
pg_upgradecluster, and restoring the column default back to
"event_id_nextval()".
I did not have to do this on previous upgrades, so something has changed. In
the past, it just worked.
I do not understand why it would have to execute "event_id_nextval()" to do
the restore. All the rows already have a value in that column (they have to,
it is the primary key). (Yes, I am aware of SERIAL.)
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.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, liam@morland.ca
Subject: Re: BUG #18151: pg_upgradecluster fails when column default refers to column
In-Reply-To: <18151-cdc2191b130172c7@postgresql.org>
* 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