BUG #18647: INSERT statements execute functions twice.

On Tue, Oct 8, 2024, 12:31 PG Bug reporting form <noreply@postgresql.org>
wrote:

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(...)

Show that create function command then.

dchain=# INSERT INTO public.party

The create table for party probably helps too.

IOW, make it possible for someone to reproduce your issue.

You may also wish to be running a supported version before diving down bug
hunting.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

The create table for party probably helps too.

I'm wondering about ON INSERT rules for that table ...

IOW, make it possible for someone to reproduce your issue.

Indeed. I tried some simple experiments with setting a breakpoint
on the standard nextval() function, and I don't see more than one
call.

regards, tom lane

Here is the CREATE table for party. It was nothing more than a test table at this time:

CREATE TABLE party (
uuid uuid NOT NULL,
date_updated timestamp(0) NOT NULL,
user_updated pg_catalog.regrole NOT NULL,
organization uuid NOT NULL,

CONSTRAINT pk_party
PRIMARY KEY ( uuid )
);

That’s the extend of it. The body of the uuid.nextval() function is an SQL function.

CREATE FUNCTION uuid.nextval ( IN i_regclass pg_catalog.regclass ) RETURNS pg_catalog.uuid AS $$
SELECT raise.notice('***** uuid.nextval');
WITH t_pool AS (
SELECT pool.id_entity AS id_entity,
pool.uuid AS uuid
FROM uuid.pool
WHERE pool.id_entity = i_regclass
ORDER BY pg_catalog.RANDOM() ASC
LIMIT 1
), t_delete AS (
DELETE FROM uuid.pool
USING t_pool
WHERE pool.id_entity = t_pool.id_entity
AND pool.uuid = t_pool.uuid
RETURNING pool.*
)
SELECT t_delete.uuid
FROM t_delete
LIMIT 1; $$
LANGUAGE SQL VOLATILE NOT LEAKPROOF STRICT PARALLEL UNSAFE SECURITY DEFINER;

On Wednesday, October 9, 2024, Todd Brandys <brandystodd@gmail.com> wrote:

Here is the CREATE table for party. It was nothing more than a test table
at this time:

CREATE TABLE party (
uuid uuid NOT NULL,
date_updated timestamp(0) NOT NULL,
user_updated pg_catalog.regrole NOT NULL,
organization uuid NOT NULL,

CONSTRAINT pk_party
PRIMARY KEY ( uuid )
);

As an aside, using regrole is not a good idea. The meaning of the integer
that gets saved will change if you perform an upgrade or otherwise have to
rebuild the global role catalog table. User-space should not be storing
OID values.

David J.

More detail is still needed. We don't have access to your schemas and
functions. But at first glance, there is something else going on, not
explained by the given code. Try to remove the unimportant parts and get it
down to a reproducible test case. For example, here is a test case showing
the expected behavior on Postgres 16:

create function mynextval(text) returns text language plpgsql as $$ BEGIN
RAISE NOTICE 'Inside mynextval!'; RETURN 'x'; END; $$;
create table foo(a text, b text);

greg=# insert into foo values (mynextval('abc'));
NOTICE: Inside mynextval!
INSERT 0 1

greg=# insert into foo values (mynextval('abc'), mynextval('def'));
NOTICE: Inside mynextval!
NOTICE: Inside mynextval!
INSERT 0 1

greg=# insert into foo select mynextval('abc'), mynextval('def');
NOTICE: Inside mynextval!
NOTICE: Inside mynextval!
INSERT 0 1

greg=# insert into foo select x from mynextval('abc') x;
NOTICE: Inside mynextval!
INSERT 0 1

greg=# insert into foo select mynextval('def') from mynextval('abc') x;
NOTICE: Inside mynextval!
NOTICE: Inside mynextval!
INSERT 0 1

Cheers,
Greg

Greg Sabino Mullane <htamfids@gmail.com> writes:

More detail is still needed. We don't have access to your schemas and
functions. But at first glance, there is something else going on, not
explained by the given code. Try to remove the unimportant parts and get it
down to a reproducible test case.

Yes, we really need a self-contained test case.

Also, is this stock community Postgres, or some derivative
(RDS, EDB, etc)? Do you have any extensions installed?

regards, tom lane

On 2024-Oct-09, Todd Brandys wrote:

CREATE FUNCTION uuid.nextval ( IN i_regclass pg_catalog.regclass ) RETURNS pg_catalog.uuid AS $$
SELECT raise.notice('***** uuid.nextval');
WITH t_pool AS (
SELECT pool.id_entity AS id_entity,
pool.uuid AS uuid
FROM uuid.pool
WHERE pool.id_entity = i_regclass
ORDER BY pg_catalog.RANDOM() ASC
LIMIT 1

Not related to your reported problem, but note that the "order by
random()" pattern might be slow as uuid.pool becomes large. Andrew
Gierth wrote a blog post on this topic awhile ago which may be helpful:
https://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers