Feedback-ID: ia2694551:Fastmail
Date: Tue, 15 Oct 2024 11:30:39 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Todd Brandys <brandystodd@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18647: INSERT statements execute functions twice.
Message-ID: <202410150930.prrd666h3um3@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <8394679D-5AA0-4727-A28A-BE08A0A11103@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202410150930.prrd666h3um3%40alvherre.pgsql>
Precedence: bulk

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