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
In-reply-to: <18151-cdc2191b130172c7@postgresql.org>
References: <18151-cdc2191b130172c7@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sun, 08 Oct 2023 17:41:14 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <137187.1696860691.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 09 Oct 2023 10:11:31 -0400
Message-ID: <137188.1696860691@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/137188.1696860691%40sss.pgh.pa.us>
Precedence: bulk

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" doe=
s
> not exist

> The reason is the the default value for column "event_id" of table "even=
t"
> 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/75a7b7483aeb331aa017328d606d568=
fc715b90d.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