Details
-
Bug
-
Status: Closed (View Workflow)
-
P3
-
Resolution: Done
-
23.11.0
-
-
CP: sprint 62
-
3
-
Core: Platform
Description
gen_random_uuid() creates a different UUID in each node of a replicated PostgreSQL environment.
This has been reported for mod-configuration and Pgpool-II replication in #sys-ops on Slack yesterday. From the Pgpool-II manual:
https://www.pgpool.net/docs/latest/en/html/restrictions.html
Functions, etc. In Native Replication mode
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. For SERIAL, enabling insert_lock will help replicating data. insert_lock also helps SELECT setval() and SELECT nextval().
mod-configuration is special in that it lets the database create a new UUID that always overwrites any provided UUID for the primary key _id:
https://github.com/folio-org/mod-configuration/blob/v5.0.1/mod-configuration-server/src/main/java/org/folio/rest/impl/ConfigAPI.java#L159
https://github.com/folio-org/mod-configuration/blob/v5.0.1/mod-configuration-server/src/main/resources/templates/db_scripts/schema.json#L14-L15
https://github.com/folio-org/raml-module-builder/blob/v23.11.0/domain-models-runtime/src/main/resources/templates/db_scripts/main.ftl#L53
Most other modules never overwrite a primary key UUID _id provided by the caller of the POST API. And if no such primary key is provided they create a new one in Java code so that all replicated nodes receive the same _id. The suggested implementation in PgUtil.post also works this way:
https://github.com/folio-org/raml-module-builder/blob/v23.11.0/domain-models-runtime/src/main/java/org/folio/rest/persist/PgUtil.java#L358
https://github.com/folio-org/raml-module-builder/blob/v23.11.0/domain-models-runtime/src/main/java/org/folio/rest/persist/PgUtil.java#L305
How to fix:
- Add a section to RMB README.md how and why to generate UUIDs in Java.
- Fail with a error message pointing to that section when schema.json contains "generateId": true or "generateId": false so that all modules remove it when updating RMB. https://github.com/folio-org/raml-module-builder/tree/v23.11.0#the-post-tenant-api
- Remove the "generateId" support from schema.json tenant API: https://github.com/folio-org/raml-module-builder/blob/v23.11.0/domain-models-runtime/src/main/resources/templates/db_scripts/main.ftl#L53
- Do the same for "populateJsonWithId" as described in
RMB-346.RMB-347andRMB-346should be implemented at the same time. - No longer "CREATE EXTENSION pgcrypto" to fail any gen_random_uuid() usage.
If there is a consensus about https://issues.folio.org/browse/RMB-277 (default name for primary key name) that should also be implemented at the same time (= removing the schema.json option "pkColumnName").
TestRail: Results
Attachments
Issue Links
- blocks
-
FOLIO-2447 Remove gen_random_uuid(), it fails in pgpool native replication
-
- Open
-
- relates to
-
MODCONF-28 Upgrade to RMB 22 (and later)
-
- Closed
-
-
RMB-372 pgpool: CURRENT_TIMESTAMP in trigger
-
- Open
-
-
RMB-405 PostgresClient.save: Create random UUID if id is missing
-
- Closed
-
-
RMB-433 saveBatch must not overwrite existing id
-
- Closed
-
-
MODINVSTOR-285 upgrade to RMB 26 and resolve related issues
-
- Closed
-
-
MODLOGIN-40 Database insert fails if _id column not populated
-
- Closed
-
-
RMB-277 id for primary key name, drop configuration option
-
- Closed
-
-
RMB-346 Always sync _id and jsonb->>'id' using a trigger, drop "populateJsonWithId: false" option
-
- Closed
-