From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: brandystodd@gmail.com
Subject: BUG #18647: INSERT statements execute functions twice.
Date: Tue, 08 Oct 2024 19:11:52 +0000
Message-ID: <18647-8ac606f2f23b1621@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18647
Logged by: Todd Brandys
Email address: brandystodd@gmail.com
PostgreSQL version: 16.1
Operating system: Linux
Description:
I am writing a function uuid.nextval(...) that returns a UUID value from a
preallocated pool of values known to be unique. I was seeing the pool
decrease by two per INSERT, and I found something interesting when I raised
a NOTICE in the nextval() function. Here are three INSERTs, and only the
last will result in nextval() being called once. Is this a known issue?
What would be the reason for calling the functions in the VALUES list twice?
For the foreseeable future, this means that the most efficient method would
be to place all functions in a FROM list.
dchain=# INSERT INTO public.party
VALUES ( uuid.nextval('party'), utc.get_timestamp(),
SESSION_USER::regrole, uuid.v4() );
NOTICE: ***** uuid.nextval
DETAIL:
HINT:
NOTICE: ***** uuid.nextval
DETAIL:
HINT:
INSERT 0 1
dchain=# INSERT INTO public.party
SELECT uuid.nextval('party'::regclass) AS party,
utc.get_timestamp() AS date_upated,
SESSION_USER::regrole AS user_updated,
uuid.v4() AS organization;
NOTICE: ***** uuid.nextval
DETAIL:
HINT:
NOTICE: ***** uuid.nextval
DETAIL:
HINT:
INSERT 0 1
dchain=# INSERT INTO public.party
SELECT d_party AS party,
utc.get_timestamp() AS date_upated,
SESSION_USER::regrole AS user_updated,
uuid.v4() AS organization
FROM uuid.nextval('party'::regclass) AS d_party;
NOTICE: ***** uuid.nextval
DETAIL:
HINT:
INSERT 0 1
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, brandystodd@gmail.com
Subject: Re: BUG #18647: INSERT statements execute functions twice.
In-Reply-To: <18647-8ac606f2f23b1621@postgresql.org>
* 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