Dropping admin schema fails with: ERROR: syntax error at or near "VARIABLE"

[bogeyman61]

Describe the bug
In my attempt to "clean up" our FHIR schemas, I am running the fhir-schema-tool to drop our obsolete/unneeded schemas. In discussing with Lee, we determined that the first step should be to migrate the schemas to the current definition and then run the tool to drop the schema.
The migration worked successfully, however when I include --drop-admin in the command, the drop failed with ERROR: syntax error at or near "VARIABLE" (see full stack below).

Environment
FHIR 4.6.1

To Reproduce
In our environment, using the "postgres-fhir1" database, the failure occurred when dropping schemas "dev_2990" as well as "a_dev_2990". I have not yet attempted to drop any other of our obsolete schemas yet.
Note that these schemas were initially created using FHIR 4.5.4.

Expected behavior
The FHIR schema and the FHIR_ADMIN schema should be dropped.

Alternatively, the tool should provide a better error to explain why the FHIR_ADMIN schema couldn't / shouldn't be dropped (e.g. if there are other tenants with other schemas still?)

Additional context

Full stack of the error during drop schema:

2021-04-07 15:10:51.131 00000001    INFO   com.ibm.fhir.schema.app.Main Opening connection to: jdbc:postgresql:///ibmclouddb
2021-04-07 15:10:52.858 00000001 WARNING utils.postgres.PostgresAdapter [ONCE] PostgreSql does not support ROW ACCESS CONTROL for table: SUBSTANCE_RESOURCE_TOKEN_REFS
2021-04-07 15:10:52.859 00000001 WARNING utils.postgres.PostgresAdapter [ONCE] Not supported in PostgreSQL: DROP PERMISSION A_DEV_2990.SUBSTANCE_RESOURCE_TOKEN_REFS_TENANT
2021-04-07 15:12:38.258 00000001  SEVERE   com.ibm.fhir.schema.app.Main schema tool failed

org.postgresql.util.PSQLException: ERROR: syntax error at or near "VARIABLE"
  Position: 6
com.ibm.fhir.database.utils.api.DataAccessException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "VARIABLE"
  Position: 6
        at com.ibm.fhir.database.utils.postgres.PostgresTranslator.translate(PostgresTranslator.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.dropVariable(CommonDatabaseAdapter.java:318)
        at com.ibm.fhir.database.utils.model.SessionVariableDef.drop(SessionVariableDef.java:34)
        at com.ibm.fhir.database.utils.model.PhysicalDataModel.drop(PhysicalDataModel.java:185)
        at com.ibm.fhir.schema.app.Main.dropSchema(Main.java:459)
        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: ERROR: syntax error at or near "VARIABLE"
  Position: 6
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
        at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:45)
        ... 7 more
2021-04-07 15:12:38.261 00000001  SEVERE   com.ibm.fhir.schema.app.Main SCHEMA CHANGE: RUNTIME ERROR

[lmsurpre]

Looks like that exception is created from attempting to drop the admin schema. You shouldn't be passing --drop-admin if you are just dropping a single schema.

However, I think dropping the admin schema should probably still work, so lets update the description of this issue accordingly and keep it open.

[prb112]

VARIABLE is not supported on Postgres.
I added a null op to the drop.

[prb112]

PR https://github.com/IBM/FHIR/pull/2221/files

[lmsurpre]

I confirm that included the --drop-admin against a provisioned postgresql schema no longer emits the above exception. However, during verification, I repeated ran into #1631. Lets close this one for now and we can reopen after addressing #1631 if there's any additional issues.

side-note: I found out that invoknig --drop-admin without also dropping a fhir schema doesn't actually do anything. I find that rather confusing and we should probably add this to the documentation in the fhir-persistence-schema README.