fhir-persistence-schema doesn't drop schemas as expected on postgres

[prb112]

Describe the bug

fhir-persistence-schema doesn't drop schemas as expected on postgres

Related to #1858

To Reproduce

1. Create a new fhirdata schema using the schema tool
2. Run the Following

/opt/java/openjdk/bin/java -jar /opt/schematool/fhir-persistence-schema-4.6.0-SNAPSHOT-cli.jar --prop db.host=172.17.0.2 --prop db.port=5432 --prop db.database=fhirdb --prop user=postgres --prop password=change-password --db-type postgresql --drop-schema-fhir --schema-name FHIRDATA10 --drop-schema-batch fhirbatch1 --drop-schema-oauth fhiroauth1 --pool-size 2 --confirm-drop --drop-admin

2021-01-08 20:43:58.683 00000001    INFO   com.ibm.fhir.schema.app.Main Opening connection to: jdbc:postgresql://172.17.0.2:5432/fhirdb
2021-01-08 20:43:59.114 00000001 WARNING s.postgresql.PostgreSqlAdapter [ONCE] PostgreSql does not support ROW ACCESS CONTROL for table: LOCATION_RESOURCE_TOKEN_REFS
2021-01-08 20:43:59.117 00000001 WARNING s.postgresql.PostgreSqlAdapter [ONCE] Not supported in PostgreSQL: DROP PERMISSION FHIRDATA10.LOCATION_RESOURCE_TOKEN_REFS_TENANT
2021-01-08 20:44:06.051 00000001 WARNING s.common.CommonDatabaseAdapter DROP SEQUENCE FHIRDATA10.FHIR_REF_SEQUENCE; Sequence not found
2021-01-08 20:44:06.067 00000001  SEVERE   com.ibm.fhir.schema.app.Main schema tool failed

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
com.ibm.fhir.database.utils.api.DataAccessException: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at com.ibm.fhir.database.utils.postgresql.PostgreSqlTranslator.translate(PostgreSqlTranslator.java:104)
        at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:48)
        at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.runStatement(CommonDatabaseAdapter.java:230)
        at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.dropSequence(CommonDatabaseAdapter.java:546)
        at com.ibm.fhir.database.utils.model.Sequence.drop(Sequence.java:66)
        at com.ibm.fhir.database.utils.model.PhysicalDataModel.drop(PhysicalDataModel.java:185)
        at com.ibm.fhir.schema.app.Main.dropSchema(Main.java:421)
        at com.ibm.fhir.schema.app.Main.process(Main.java:1498)
        at com.ibm.fhir.schema.app.Main.main(Main.java:1580)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:247)
        at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:45)
        ... 7 more
Caused by: org.postgresql.util.PSQLException: ERROR: sequence "fhir_ref_sequence" does not exist
        ... 17 more
2021-01-08 20:44:06.073 00000001  SEVERE   com.ibm.fhir.schema.app.Main SCHEMA CHANGE: RUNTIME ERROR

Expected behavior
The Drop action should work well.

Additional context
n/a

[punktilious]

The drop sequence ends up being called more than once and with PostgreSQL, failed commands cause the transaction to fail. The fix is to override the dropSequence adapter method for PostgreSQL and use

DROP SEQUENCE IF EXISTS <name>;

[punktilious]

In addition, if the server complains about locks or shared memory, set the following in postgresql.conf

max_locks_per_transaction = 128 

requires a server restart.

[prb112]

Verified in another issue