Upgrade issue between Q1 and Q2: "public.gin_trgm_ops" does not exist

Description

Upgraded release of mod-orders-storage from v10.0.2 to v11.0.1 for an existing Diku tenant with data.

The request sent to Okapi was this, with reference and sample data set to true:

curl -w '\n' -D - -X POST -H "Content-type: application/json" \
-d @upgrade.json \
$OKAPI_URL/_/proxy/tenants/$TENANT_ID/install?deploy=false\&preRelease=false\&tenantParameters=loadSample%3Dtrue%2CloadReference%3Dtrue

Also tried, after rolling back the DB instances, with sample and reference data set to false, with the same error.

Okapi returned the following error during the tenant API call:

POST request for mod-orders-storage-11.0.1 /_/tenant failed with 400: [ " DO $do$\n BEGIN\n PERFORM rmb_internal_index(\n 'po_line', 'po_line_fundDistribution_code_idx_gin', 'ADD',\n 'CREATE INDEX IF NOT EXISTS po_line_fundDistribution_code_idx_gin ON diku_mod_orders_storage.po_line USING GIN '\n || $rmb$((lower(f_unaccent(jsonb->'fundDistribution'->>'code'))) public.gin_trgm_ops)$rmb$\n );\n END $do$;" ]

mod-orders-storage logs:

2020-07-06T18:45:19,720 ERROR [vert.x-worker-thread-0 ] PostgresClient ERROR: operator class "public.gin_trgm_ops" does not exist for access method "gin"

Where: SQL statement "CREATE INDEX IF NOT EXISTS po_line_fundDistribution_code_idx_gin ON diku_mod_orders_storage.po_line USING GIN ((lower(f_unaccent(jsonb->'fundDistribution'->>'code'))) public.gin_trgm_ops)"

PL/pgSQL function rmb_internal_index(text,text,text,text) line 22 at EXECUTE

SQL statement "SELECT rmb_internal_index(

'po_line', 'po_line_fundDistribution_code_idx_gin', 'ADD',

'CREATE INDEX IF NOT EXISTS po_line_fundDistribution_code_idx_gin ON diku_mod_orders_storage.po_line USING GIN '

|| $rmb$((lower(f_unaccent(jsonb->'fundDistribution'->>'code'))) public.gin_trgm_ops)$rmb$

)"

PL/pgSQL function inline_code_block line 3 at PERFORM

org.postgresql.util.PSQLException: ERROR: operator class "public.gin_trgm_ops" does not exist for access method "gin"

Where: SQL statement "CREATE INDEX IF NOT EXISTS po_line_fundDistribution_code_idx_gin ON diku_mod_orders_storage.po_line USING GIN ((lower(f_unaccent(jsonb->'fundDistribution'->>'code'))) public.gin_trgm_ops)"

PL/pgSQL function rmb_internal_index(text,text,text,text) line 22 at EXECUTE

SQL statement "SELECT rmb_internal_index(

'po_line', 'po_line_fundDistribution_code_idx_gin', 'ADD',

'CREATE INDEX IF NOT EXISTS po_line_fundDistribution_code_idx_gin ON diku_mod_orders_storage.po_line USING GIN '

|| $rmb$((lower(f_unaccent(jsonb->'fundDistribution'->>'code'))) public.gin_trgm_ops)$rmb$

)"

PL/pgSQL function inline_code_block line 3 at PERFORM

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310) ~[mod-orders-storage-fat.jar:?]

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2023) ~[mod-orders-storage-fat.jar:?]

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217) ~[mod-orders-storage-fat.jar:?]

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:421) ~[mod-orders-storage-fat.jar:?]

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318) ~[mod-orders-storage-fat.jar:?]

at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:294) ~[mod-orders-storage-fat.jar:?]

at org.folio.rest.persist.PostgresClient.lambda$80(PostgresClient.java:3496) ~[mod-orders-storage-fat.jar:?]

at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313) ~[mod-orders-storage-fat.jar:?]

at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76) ~[mod-orders-storage-fat.jar:?]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_222]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_222]

at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) [mod-orders-storage-fat.jar:?]

at java.lang.Thread.run(Thread.java:748) [?:1.8.0_222]

2020-07-06T18:45:19,725 ERROR [vert.x-worker-thread-0 ] PostgresClient Rollback for: 1231774605

CSP Request Details

None

CSP Rejection Details

None

Potential Workaround

None

Linked issues

blocks

MODORDSTOR-163

MODORDSTOR (mod-orders-storage) Bugfix release

FOLIO-2662

test automatic migration for Goldenrod

relates to

MODORDSTOR-162

Update to RMB v30.2.3 fixing pg_catalog.pg_trgm

MODPERMS-88

Upgrade issue between Q1 and Q2: "public.gin_trgm_ops" does not exist

MODCONF-54

Upgrade issue between Q1 and Q2: "public.gin_trgm_ops" does not exist

ERM-989

Use public schema when creating extension pg_trgm

ERM-990

Use public schema when creating extension pg_trgm

RMB-584

Make "public" schema read-only (CVE-2018-1058)

RMB-587

Do not use public schema, remove it from search_path

RMB-671

Reinstall PostgreSQL extensions on upgrade, drop pg_catalog.pg_trgm

MODORGSTOR-74

Upgrade issue between Q1 and Q2: "public.gin_trgm_ops" does not exist

MODCAL-78

Update to RMB 30.2.3, fixing pg_catalog.pg_trgm and sql injection

Checklist

hide

TestRail: Results

Activity

Show:

jrootJuly 14, 2020 at 7:20 PM

I was able to successfully upgrade now from Q1 to Q2 with mod-cal and mod-orders RMB updates.

Ann-Marie BreauxJuly 13, 2020 at 1:21 PM

Great - thanks very much, @Julian Ladisch

Julian LadischJuly 13, 2020 at 10:05 AM

If sysOps use latest platform-complete or platform-core to run the upgrade they are safe.
The first module in
https://github.com/folio-org/platform-complete/blob/q2-2020/install.json
is mod-orders-storage 11.0.3 that has the RMB fix.
The first module in
https://github.com/folio-org/platform-core/blob/q2-2020/install.json
is mod-calendar 1.9.1 that has the RMB fix.

If the first module fixes the issue all following modules upgrade successfully even if they use old RMB versions.

I've added an upgrade considerations entry to the Goldenrod release notes:
https://folio-org.atlassian.net/wiki/pages/viewpage.action?pageId=5210318#Q22020(Goldenrod)ReleaseNotes(InProgress)-ImportantUpgradeConsiderations

Ann-Marie BreauxJuly 13, 2020 at 5:40 AM

Hi @Julian Ladisch and @Jakub Skoczen We're trying to figure out if any additional work is needed for MODORGSTOR-74, MODCONF-54, or MODPERMS-88 or they can just be closed. With regards to @jroot's concern a few comments above - would there be a need to upgrade mod-orders-storage before the other affected apps? If that's required, we definitely need to document in the release notes on the wiki. Plus I'm wondering if that requirement might cause issues for upgrading libraries, especially since MODPERMS is one of the affected apps, and it usually needs to be deployed early in the sequencing (I think). Thank you!

cc: @Former user

Ann-Marie BreauxJuly 13, 2020 at 5:31 AM

Closing this ticket.

Done

Details
Assignee
Julian Ladisch
Reporter
jroot
Labels
bugfest_q2.2020
Priority
P2
Story Points
0
Sprint
None
Development Team
Core: Platform
Parent
FOLREL-346 Q2 2020 Schema upgrade
Fix versions
11.0.2
Release
Q2 2020
Affected Institution
TAMU
TestRail: Cases
Open TestRail: Cases
TestRail: Runs
Open TestRail: Runs

Created July 6, 2020 at 7:21 PM

Updated July 29, 2020 at 4:14 PM

Resolved July 8, 2020 at 8:49 PM

TestRail: Cases

TestRail: Runs

Upgrade issue between Q1 and Q2: "public.gin_trgm_ops" does not exist

Description

CSP Request Details

CSP Rejection Details

Potential Workaround

Linked issues

blocks

relates to

Checklist

TestRail: Results

Activity

jrootJuly 14, 2020 at 7:20 PM

Ann-Marie BreauxJuly 13, 2020 at 1:21 PM

Julian LadischJuly 13, 2020 at 10:05 AM

Ann-Marie BreauxJuly 13, 2020 at 5:40 AM

Ann-Marie BreauxJuly 13, 2020 at 5:31 AM

Details

Assignee

Reporter

Labels

Priority

Story Points

Sprint

Development Team

Parent

Fix versions

Release

Affected Institution

TestRail: Cases

TestRail: Runs

Flag notifications

Something's gone wrong