Re: pg_upgrade failure due to dependencies

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: "Edward J(dot) Sabol" <edwardjsabol(at)gmail(dot)com>, Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: pg_upgrade failure due to dependencies
Date: 2025-07-01 17:09:21
Message-ID: da61a3315f0f011da1ad5dead781b80a871a783e.camel@cybertec.at
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Tue, 2025-07-01 at 07:10 -0400, Edward J. Sabol wrote:
> On Jul 1, 2025, at 5:37 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> > Moreover, PostgreSQL cannot check dependencies, since the function was defined using
> > the "old" style for SQL functions, where the function body is just a string.
> > If they had used the standard conforming new style, PostgreSQL would try to trach
> > dependencies.  Not sure if that would have been enough to avoid the problem,
> > but clearly better.
>
> Just for my edification, how would one create that PostGIS function using the
> "standard-conforming new style"?

CREATE FUNCTION public.st_transform(geom geometry, from_proj text, to_srid integer)
RETURNS geometry
IMMUTABLE PARALLEL SAFE STRICT COST 5000
BEGIN ATOMIC
SELECT public.postgis_transform_geometry($1, $2, proj4text, $3)
FROM public.spatial_ref_sys WHERE srid=$3;
END;

(The IMMUTABLE would still be a lie, though.)

> *All* of the examples in PostgeSQL's current documentation are the "old" (string)
> style at https://www.postgresql.org/docs/18/xfunc-sql.html.
>
> The only example of the new style in the PostgreSQL documentation that I could
> find is the very trivial second example at
> https://www.postgresql.org/docs/18/sql-createfunction.html. The other examples
> on that web page are all the old style.
>
> If the new style is better, perhaps someone should update the PostgreSQL
> documentation to use it more than just once. More complicated examples of
> "new style" SQL functions are needed, I feel.

That is true; we could do more there.

Yours,
Laurenz Albe

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Raj 2025-07-02 07:37:45 Patroni set up
Previous Message Tom Lane 2025-07-01 17:07:26 Re: pg_upgrade failure due to dependencies