Out of shared memory error while dropping fhir schema using ibm cloud databases for postgresql

[lmsurpre]

Describe the bug

Error message
ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction.

To Reproduce
Steps to reproduce the behavior:

1. create an IBM Cloud Databases for PostgreSQL instance
2. deploy the fhir server schema to it
3. drop the schema (with cascade)

Expected behavior
it drops the schema

Additional context
After discussing with @punktilious, the issue is that we try to drop the whole schema in a single transaction.
Instead, to properly delete a schema, we should write logic that reads from the FHIR_ADMIN.VERSION_HISTORY table and intelligently drops the schema objects in batches.

[punktilious]

ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction.

To address this, set max_locks_per_transaction = 128.

Update PostgreSQL container and performance guide.

In addition, there were two attempts to drop the same sequence object, causing the second attempt to fail. For PostgreSQL, any statement failure like this causes the transaction to fail. Making the drop sequence idempotent addresses this for PostgreSQL:

        final String ddl = "DROP SEQUENCE IF EXISTS " + sname;

[lmsurpre]

the first time I ran it, it failed due to a dropped connect:

2021-03-23 12:14:15.485 00000001    INFO   com.ibm.fhir.schema.app.Main Opening connection to: jdbc:postgresql://****.databases.appdomain.cloud:****/ibmclouddb
2021-03-23 12:14:17.931 00000001 WARNING utils.postgres.PostgresAdapter [ONCE] PostgreSql does not support ROW ACCESS CONTROL for table: SUBSTANCE_RESOURCE_TOKEN_REFS
2021-03-23 12:14:17.932 00000001 WARNING utils.postgres.PostgresAdapter [ONCE] Not supported in PostgreSQL: DROP PERMISSION TEST.SUBSTANCE_RESOURCE_TOKEN_REFS_TENANT
2021-03-23 12:16:26.879 00000001  SEVERE   com.ibm.fhir.schema.app.Main schema tool failed
org.postgresql.util.PSQLException: This connection has been closed.
com.ibm.fhir.database.utils.api.ConnectionException: org.postgresql.util.PSQLException: This connection has been closed.
	at com.ibm.fhir.database.utils.postgres.PostgresTranslator.translate(PostgresTranslator.java:96)
	at com.ibm.fhir.schema.app.Main.dropSchema(Main.java:468)
	at com.ibm.fhir.schema.app.Main.process(Main.java:1737)
	at com.ibm.fhir.schema.app.Main.main(Main.java:1819)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
	at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:885)
	at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:892)
	at com.ibm.fhir.schema.app.Main.dropSchema(Main.java:462)
	... 2 more
2021-03-23 12:16:26.881 00000001  SEVERE   com.ibm.fhir.schema.app.Main SCHEMA CHANGE: RUNTIME ERROR

However, it succeeded in 45 seconds upon retry.
Then I repeated the whole cycle (create, update, drop) and it succeeded again, so I think its working.

[lmsurpre]

It would be nice if this code were exercised as part of our CI build, so I created #2135 for that.

Additionally, I opened #2136 for being able to drop the schema objects from FHIR_ADMIN.VERSION_HISTORY. Without this, it will not be possible to re-use schema names after they are dropped.

[lmsurpre]

I feel like I must have done something wrong when I confirmed this one earlier. I am now consistently hitting this error for our IBM Cloud Databases for Postgresql instance.

[punktilious]

To work around this, the schema tool now drops all the FK constraints in the schema first in a separate transaction. This reduces the amount of locking that PostgreSQL has to do when it comes to dropping the tables, and therefore avoids the shared memory error (which is actually a running out of locks error).

[lmsurpre]

I confirmed this on IBM Cloud Databases for PostgreSQL by creating a new service instance (with the default settings), then

creating the schema with:

--prop-file postgres.properties
--db-type postgresql
--schema-name test1
--create-schemas

deploying a new schema with:

--prop-file postgres.properties
--db-type postgresql
--schema-name test1
--update-schema
--pool-size 4

and finally dropping it with:

--prop-file postgres.properties
--db-type postgresql
--schema-name test1
--drop-schema-fhir
--confirm-drop

The drop completed successfully in ~213 seconds.

2021-05-11 17:54:21.717 00000001    INFO   com.ibm.fhir.schema.app.Main Processing took: 213.238 s
2021-05-11 17:54:21.718 00000001    INFO   com.ibm.fhir.schema.app.Main SCHEMA CHANGE: OK