Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-476

Postgres requires special permissions to disable triggers for migrations

    XMLWordPrintable

Details

    • Core: F - Sprint 91
    • 5
    • Core: Platform
    • Q2 2020 Hot Fix #1

    Description

      In scope of MODINVSTOR-450 we did some performance improvements for long running data migration scripts. The idea was to disable some triggers for the item table.

      Steps to reproduce:

      1. Execute a migration script which disables/enables triggers before migration (for example populateEffectiveCallNumberComponentsForExistingItems.sql) on a hosted environment.
      2. You can use PR preview feature for this.

      Expected:

      Migration executed successfully.

      Actual:

      Module deployment failed with ERROR: permission denied for table pg_trigger error.
      See mod-inventory-storage-19.2.0-SNAPSHOT-420-15.log.gz for more details.

      sduvvuri reported the full error below from the EBSCO FSE environments during a fresh activation of mod-inventory-storage:

      17 Jun 2020 05:37:36:238 INFO  PostgresClient [5465620eqId] Successfully executed:  START TRANSACTION
      17 Jun 2020 05:37:36:238 INFO  PostgresClient [5465620eqId] trying to execute:  UPDATE pg_trigger   SET tgenabled = 'D' WHERE tgrelid = 'fs00001006_mod_inventory_storage.item'::regclass::oid   AND tgisinternal IS FALSE   AND tgenabled = 'O'
      17 Jun 2020 05:37:36:243 ERROR PostgresClient [5465625eqId] ERROR: permission denied for relation pg_trigger
      org.postgresql.util.PSQLException: ERROR: permission denied for relation pg_trigger
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310) ~[ms.jar:?]
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2023) ~[ms.jar:?]
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217) ~[ms.jar:?]
      	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:421) ~[ms.jar:?]
      	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318) ~[ms.jar:?]
      	at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:294) ~[ms.jar:?]
      	at org.folio.rest.persist.PostgresClient.lambda$80(PostgresClient.java:3499) ~[ms.jar:?]
      	at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$2(ContextImpl.java:313) ~[ms.jar:?]
      	at io.vertx.core.impl.TaskQueue.run(TaskQueue.java:76) ~[ms.jar:?]
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_242]
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_242]
      	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) [ms.jar:?]
      	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_242]
      17 Jun 2020 05:37:36:245 ERROR PostgresClient [5465627eqId] Rollback for: -747765398
      17 Jun 2020 05:37:36:304 ERROR PostgresClient [5465686eqId] Opening SQLConnection failed: password authentication failed for user "fs00001006_mod_inventory_storage"
      io.vertx.pgclient.PgException: password authentication failed for user "fs00001006_mod_inventory_storage"
      	at io.vertx.pgclient.impl.codec.ErrorResponse.toException(ErrorResponse.java:29) ~[ms.jar:?]
      	at io.vertx.pgclient.impl.codec.InitCommandCodec.handleErrorResponse(InitCommandCodec.java:97) [ms.jar:?]
      	at io.vertx.pgclient.impl.codec.PgDecoder.decodeError(PgDecoder.java:233) [ms.jar:?]
      	at io.vertx.pgclient.impl.codec.PgDecoder.decodeMessage(PgDecoder.java:122) [ms.jar:?]
      	at io.vertx.pgclient.impl.codec.PgDecoder.channelRead(PgDecoder.java:102) [ms.jar:?]
      	at io.netty.channel.CombinedChannelDuplexHandler.channelRead(CombinedChannelDuplexHandler.java:251) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [ms.jar:?]
      	at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [ms.jar:?]
      	at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [ms.jar:?]
      	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [ms.jar:?]
      	at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) [ms.jar:?]
      	at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:163) [ms.jar:?]
      	at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:714) [ms.jar:?]
      	at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:650) [ms.jar:?]
      	at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:576) [ms.jar:?]
      	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493) [ms.jar:?]
      	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989) [ms.jar:?]
      	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [ms.jar:?]
      	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) [ms.jar:?]
      	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_242]
      17 Jun 2020 05:37:36:304 ERROR MaterialTypeAPI [5465686eqId] password authentication failed for user "fs00001006_mod_inventory_storage"
      17 Jun 2020 05:37:36:305 INFO  LogUtil [5465687eqId] org.folio.rest.RestVerticle start  invoking putMaterialTypesByMaterialtypeId
      17 Jun 2020 05:37:36:305 INFO  LogUtil [5465687eqId] 127.0.0.1:57302 PUT /material-types/1a54b431-2e4f-452d-9cae-9cee66c9a892 null HTTP_1_1 500 71 7 tid=fs00001006 Internal Server Error 
      17 Jun 2020 05:37:36:306 ERROR TenantLoading [5465688eqId] PUT http://pvt.lb.g5ct.folio-eis.us-east-1:8051/mod-inventory-storage/material-types/1a54b431-2e4f-452d-9cae-9cee66c9a892 returned status 500: Internal Server Error, Please contact System Administrator or try again
      17 Jun 2020 05:37:36:306 INFO  LogUtil [5465688eqId] org.folio.rest.RestVerticle start  invoking postTenant
      17 Jun 2020 05:37:36:307 INFO  LogUtil [5465689eqId] 127.0.0.1:57288 POST /_/tenant null HTTP_1_1 500 214 1618 tid=fs00001006 Internal Server Error 
      

      Dev details:

      Explicitly enable/disable triggers which affects performance instead of using the pg_trigger table:

      ALTER TABLE item DISABLE | ENABLE TRIGGER USER
      

      Reference: https://www.postgresql.org/docs/current/sql-altertable.html

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                julianladisch Julian Ladisch
                bohdan-suprun Bohdan Suprun
                Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases