Re: BUG #18151: pg_upgradecluster fails when column default refers to column

PG Bug reporting form <noreply(at)postgresql(dot)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.at

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