From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | liam(at)morland(dot)ca |
Subject: | BUG #18151: pg_upgradecluster fails when column default refers to column |
Date: | 2023-10-08 17:41:14 |
Message-ID: | 18151-cdc2191b130172c7@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18151
Logged by: Liam Morland
Email address: liam(at)morland(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Vik Fearing | 2023-10-08 19:35:15 | Re: Access to old versions of a row |
Previous Message | Tom Lane | 2023-10-08 16:17:53 | Re: BUG #18150: The results returned by index only scan using gist index of bpchar and seqscan have difference. |