From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Liam Morland <liam(at)morland(dot)ca> |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18151: pg_upgradecluster fails when column default refers to column |
Date: | 2023-10-09 16:42:44 |
Message-ID: | 162382.1696869764@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Liam Morland <liam(at)morland(dot)ca> writes:
> 2023-10-09 10:11-0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2023-10-09 23:55:30 | Re: Access to old versions of a row |
Previous Message | Liam Morland | 2023-10-09 16:13:05 | Re: BUG #18151: pg_upgradecluster fails when column default refers to column |