From: Tom Lane <tgl@sss.pgh.pa.us>
To: Liam Morland <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: <20231009161305.af4z3fz3cfgo2gwl@burns.morland.ca>
References: <18151-cdc2191b130172c7@postgresql.org>
 <137188.1696860691@sss.pgh.pa.us>
 <20231009161305.af4z3fz3cfgo2gwl@burns.morland.ca>
Comments: In-reply-to Liam Morland <liam@morland.ca>
	message dated "Mon, 09 Oct 2023 12:13:05 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <162381.1696869764.1@sss.pgh.pa.us>
Date: Mon, 09 Oct 2023 12:42:44 -0400
Message-ID: <162382.1696869764@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/162382.1696869764%40sss.pgh.pa.us>
Precedence: bulk

Liam Morland <liam@morland.ca> writes:
> 2023-10-09 10:11-0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: [...]
>> Is that function written in old-style (with a string literal for the 
>> body) or new-style with BEGIN ATOMIC? [...]

> It is old-style. I wrote it before PG 14.

OK, so it should work ...

>> Can you show us the exact DDL definition of both the table and the 
>> function? [...]

>> From the output of pg_dump:

> CREATE FUNCTION public.event_id_nextval() RETURNS integer
>     LANGUAGE sql
>     AS $$
>   SELECT COALESCE(MAX(event_id) + 1, 1) FROM event;
>   $$;

> CREATE TABLE public.event (
>     event_id integer DEFAULT public.event_id_nextval() NOT NULL,


Oh ... I thought of a plausible explanation, or part of an
explanation.  That function is not search-path-safe: if it's run with
a search_path that doesn't include "public", it'll fail as described.
And indeed dump/restore will use a restrictive search_path setting.
So the COPY bug I alluded to before could trigger the reported
failure, if the upgrade is transferring data to the new cluster using
COPY rather than physically moving files around.

I see that pg_upgradecluster defaults to using dump/restore rather
than pg_upgrade, which surprises me, but if you used that mode then
all is explained.

Bug or no bug, that function would be better written as

   SELECT COALESCE(MAX(event_id) + 1, 1) FROM public.event;

so that it still works under a restrictive search path.

			regards, tom lane