Error with 1.0: operation not permitted; cannot drop values in an enumeration: dimdim

[jwals]

I am still attempting to get all of our tables syncing again after the changes with 1.0, and when trying to update the web_conferences table I am receiving the following error: pysqlsync.formation.object_types.FormationError: operation not permitted; cannot drop values in an enumeration: dimdim. Full Lambda stack trace is below. Has anyone else encountered this and have any suggestions beyond dropping and reinitializing?

[INFO]	2024-03-07T19:04:49.155Z		Found credentials in environment variables.
START RequestId: cf2606ea-11d8-4860-924f-f0c21544fb49 Version: $LATEST
[INFO]	2024-03-07T19:06:44.671Z	cf2606ea-11d8-4860-924f-f0c21544fb49	Beginning incremental sync for web_conferences
[INFO]	2024-03-07T19:06:44.672Z	cf2606ea-11d8-4860-924f-f0c21544fb49	connecting to <redacted>
[INFO]	2024-03-07T19:06:44.739Z	cf2606ea-11d8-4860-924f-f0c21544fb49	PostgreSQL version 15.0.2 final
[ERROR]	2024-03-07T19:06:46.983Z	cf2606ea-11d8-4860-924f-f0c21544fb49	operation not permitted; cannot drop values in an enumeration: dimdim
Traceback (most recent call last):
  File "/var/task/app.py", line 46, in lambda_handler
    asyncio.get_event_loop().run_until_complete(
  File "/var/lang/lib/python3.10/asyncio/base_events.py", line 649, in run_until_complete
    return future.result()
  File "/var/task/dap/actions/sync_db.py", line 16, in sync_db
    await SQLReplicator(session, db_connection).synchronize(namespace, table_name)
  File "/var/task/dap/replicator/sql.py", line 77, in synchronize
    await sync_op.run()
  File "/var/task/dap/replicator/sql_op_sync.py", line 39, in run
    await self.explorer.synchronize(modules=[meta_schema, self.namespace_module])
  File "/var/task/pysqlsync/base.py", line 1169, in synchronize
    stmt = generator.get_mutate_stmt(target_state)
  File "/var/task/pysqlsync/base.py", line 231, in get_mutate_stmt
    return self.mutator.mutate_catalog_stmt(self.state, target)
  File "/var/task/pysqlsync/formation/mutation.py", line 256, in mutate_catalog_stmt
    statements.extend(
  File "/var/task/pysqlsync/formation/object_types.py", line 22, in extend
    for item in __iterable:
  File "/var/task/pysqlsync/formation/mutation.py", line 257, in <genexpr>
    self.mutate_namespace_stmt(source_ns, target_ns)
  File "/var/task/pysqlsync/formation/mutation.py", line 204, in mutate_namespace_stmt
    statements.extend(
  File "/var/task/pysqlsync/formation/object_types.py", line 22, in extend
    for item in __iterable:
  File "/var/task/pysqlsync/formation/mutation.py", line 205, in <genexpr>
    self.mutate_enum_stmt(source_enum, target_enum)
  File "/var/task/pysqlsync/formation/mutation.py", line 57, in mutate_enum_stmt
    raise FormationError(
pysqlsync.formation.object_types.FormationError: operation not permitted; cannot drop values in an enumeration: dimdim[INFO]	2024-03-07T19:06:46.985Z	cf2606ea-11d8-4860-924f-f0c21544fb49	event: {'table': 'web_conferences', 'state': 'failed', 'exception': 'operation not permitted; cannot drop values in an enumeration: dimdim'}
END RequestId: cf2606ea-11d8-4860-924f-f0c21544fb49

ETA: Even after dropping with the CLI, a clean dap initdb results in "2024-03-07 14:42:28,188 - ERROR - operation not permitted; cannot drop values in an enumeration: dimdim" so it apparently doesn't have to do with schema migrations.

[LeventeHunyadi · Accepted solution]

dropdb removes the table (e.g. canvas.submissions) but doesn't drop column member types (e.g. enumeration types). You need to do a cleanup and drop the old definition of the enumeration type:

DROP TYPE canvas.web_conferences__conference_type

If you have references to this type (e.g. tables that use this enumeration type), you may need to remove the those references first.

[jwals]

Many thanks as always @LeventeHunyadi, a clean "dap initdb" was successful after dropping that type. Do you have any idea why this wasn't handled behind the scenes? The web_conferences table is not one that we make much use of, and as far as I know we certainly haven't ever done anything with web_conferences__conferences_type.

[LeventeHunyadi]

Unlike in MySQL or Oracle, enumerations are a separate type in PostgreSQL, independent of the table definition. (Microsoft SQL Server doesn't have any form of enumerations.) As such, multiple objects could reference the type, including tables you create and have nothing to do with DAP/CD 2. For example, let's suppose you create a new table and add a column whose type is web_conferences__conferences_type. We can no longer delete the enumeration type because the type is being used in your table, and trying to drop the type would either invalidate data in your table, or simply fail. We could scan the database if the type is used anywhere, and drop it only if there are no references but this logic doesn't currently exist in the DAP client library. So if you have a stale version of the type (as was the case for you), it might interfere with table creation by initdb.

DAP client library version 1.0 is much stricter in enforcing schema than previous versions. As such, stale types are much less risk than earlier.