[FOLIO-2978] Remove uuid_generate_v4(), it fails in pgpool native replication Created: 25/Jan/21  Updated: 16/Aug/21

Status: Open
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Bug Priority: P4
Reporter: Julian Ladisch Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Relates
relates to MODFISTO-215 Remove uuid_generate_v4(), it fails i... Closed
relates to FOLIO-2447 Remove gen_random_uuid(), it fails in... Open
Sprint: CP: Non-roadmap backlog
Development Team: Core: Platform

 Description   

uuid_generate_v4() creates a different UUID in each node of a replicated PostgreSQL environment if Pgpool-II runs in native replication mode.
This has been reported for the similar function gen_random_uuid() with Pgpool-II replication in #sys-ops on Slack. From the Pgpool-II manual:
https://www.pgpool.net/docs/latest/en/html/restrictions.html

There is no guarantee that any data provided using a context-dependent mechanism (e.g. random number, transaction ID, OID, SERIAL, sequence), will be replicated correctly on multiple backends.

RMB documentation mentions it in the upgrading guide for RMB 25 and in connection with "generateId": https://github.com/folio-org/raml-module-builder/#the-post-tenant-api

Tasks:

Note:
For predefined records use a hard-coded UUID.
For calculated records re-use the UUID of the primary source record.
Or use the md5sum trick: If a string (for example a JSONB::text) is unique calculate md5sum(s)::text::uuid to get a unique UUID.

To prevent any new usage of uuid_generate_v4() RMB and Spring base should add this function that takes precedence of the uuid-ossp function:

CREATE OR REPLACE FUNCTION public.uuid_generate_v4() RETURNS uuid
language plpgsql as $$
BEGIN
  RAISE 'FOLIO doesn''t allow uuid_generate_v4() because it doesn''t work with a replicated database like Pgpool-II'
    USING ERRCODE = 'feature_not_supported';
END;
$$;

Note:

This doesn't affect Pgpool-II streaming replication mode, it only affect Pgpool-II native replication mode.


Generated at Thu Feb 08 23:24:41 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.