pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrien Nayrat <adrien.nayrat@anayrat.info>
Cc: Aleksander Alekseev <aleksander@timescale.com>
Cc: pgsql-hackers@lists.postgresql.org, James Keener <jim@jimkeener.com>
Subject: Re: BUG #18097: Immutable expression not allowed in generated at
Date: Wed, 25 Sep 2024 10:41:07 -0400
Message-ID: <3646824.1727275267@sss.pgh.pa.us> (raw)
In-Reply-To: <594289a1-922d-497b-939e-20a95a71126d@anayrat.info>
References: <18097-ebb179674f22932f@postgresql.org>
	<CAKFQuwZW=wyr_Nfi8Ww+U-jxdh4tvztvMjT23iv2Ly+Oc0n5eQ@mail.gmail.com>
	<118FF128-C9CA-490C-8E67-65888EE8283F@jimkeener.com>
	<1887197.1694189299@sss.pgh.pa.us>
	<2250243.1694287087@sss.pgh.pa.us>
	<2275069.1694300202@sss.pgh.pa.us>
	<CAJ7c6TNNG_x8enNdhqmGdg_9A0fNa0LBadecBkuHc-=a7YqaFg@mail.gmail.com>
	<2502479.1699984134@sss.pgh.pa.us>
	<CAJ7c6TN3myMtC4_r+68=a_z60NkBipowWyX76e1j_7Vp05TPnA@mail.gmail.com>
	<2661435.1700061355@sss.pgh.pa.us>
	<CAJ7c6TNX7itd4TCUOmUXWng_C695t9WQsp8bZ_A61PBXvEV0kQ@mail.gmail.com>
	<389130.1700147198@sss.pgh.pa.us>
	<594289a1-922d-497b-939e-20a95a71126d@anayrat.info>

Adrien Nayrat <adrien.nayrat@anayrat.info> writes:
> A customer encountered an issue while restoring a dump of its database 
> after applying 15.6 minor version.
> It seems due to this fix :
>>> Fix function volatility checking for GENERATED and DEFAULT 
>>> expressions (Tom Lane)

I don't believe this example has anything to do with that.

> CREATE SCHEMA s1;
> CREATE SCHEMA s2;
> CREATE FUNCTION s2.f1 (c1 text) RETURNS text
> LANGUAGE SQL IMMUTABLE
> AS $$
>    SELECT c1
> $$;
> CREATE FUNCTION s2.f2 (c1 text) RETURNS text
> LANGUAGE SQL IMMUTABLE
> AS $$
>    SELECT s2.f1 (c1);
> $$;
> CREATE TABLE s1.t1 (c1 text, c2 text GENERATED ALWAYS AS (s2.f2 (c1)) 
> STORED);

The problem here is that to pg_dump, the body of s2.f2 is just an
opaque string, so it has no idea that that depends on s2.f1, and
it ends up picking a dump order that doesn't respect that
dependency.

It used to be that there wasn't much you could do about this
except choose object names that wouldn't cause the problem.
In v14 and up there's another way, at least for SQL-language
functions: you can write the function in SQL spec style.

CREATE FUNCTION s2.f2 (c1 text) RETURNS text
IMMUTABLE
BEGIN ATOMIC
   SELECT s2.f1 (c1);
END;

Then the dependency is visible, both to the server and to pg_dump,
and you get a valid dump order.

			regards, tom lane





view thread (16+ messages)  latest in thread

Message-ID: <3646824.1727275267@sss.pgh.pa.us>
Permalink:  ../3646824.1727275267@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3646824.1727275267@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, adrien.nayrat@anayrat.info, aleksander@timescale.com, jim@jimkeener.com
  Subject: Re: BUG #18097: Immutable expression not allowed in generated at
  In-Reply-To: <3646824.1727275267@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox