Re: BUG #18647: INSERT statements execute functions twice.

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 Oct 8, 2024, at 1:04 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tue, Oct 8, 2024, 12:31 PG Bug reporting form <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)org>> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 18647
>> Logged by: Todd Brandys
>> Email address: brandystodd(at)gmail(dot)com <mailto:brandystodd(at)gmail(dot)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.