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

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

"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 )

);

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