From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: jian he <jian.universality@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: procedure string constant is parsed at procedure create time.
Date: Tue, 07 Nov 2023 10:08:00 -0500
Message-ID: <120982.1699369680@sss.pgh.pa.us> (raw)
In-Reply-To: <202311071239.goklps3d33jz@alvherre.pgsql>
References: <202311071239.goklps3d33jz@alvherre.pgsql>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2023-Nov-07, jian he wrote:
>> ----2.this will have errors.
> The problem seems to be that the procedure uses a single catalog
> snapshot during execution, so the INSERT doesn't see the table that
> CREATE has made.
It looks to me like fmgr_sql does take a new snapshot per query.
The problem is that we do parse analysis of the entire function
body before we execute any of it. So (roughly speaking) we can
cope with DML changes between statements, but not DDL changes.
At one time it was possible to argue that this was a bug, or
at least a deficiency we ought to rectify sometime. However,
with new-style SQL functions the entire function body is
certainly parsed before execution. So now I'd be against
changing this aspect of old-style functions: it'd mainly have
the result of widening the semantic gap between old style and
new style, which doesn't seem like a good thing.
(The verification done by check_function_bodies likewise can't
work if earlier statements make DDL changes that affect later
ones.)
As per the advice in the fine manual, use plpgsql for this
sort of task.
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, jian.universality@gmail.com
Subject: Re: procedure string constant is parsed at procedure create time.
In-Reply-To: <120982.1699369680@sss.pgh.pa.us>
References: <202311071239.goklps3d33jz@alvherre.pgsql> <120982.1699369680@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