Can't drop tables with dependent objects during table version swap

[andrewcstewart]

Hi there. I'm trying to figure out if the following issue is something I can handle at the datamill-co/target-postgres side or the dbt side of the equation.

I'm using Meltano to run a tap-mongodb to target-postgres pipe, followed by some dbt transforms. On the first run it works fine, but on subsequent runs I get the following error from the target:

target-postgres       | psycopg2.errors.DependentObjectsStillExist: cannot drop table tap_mongodb.foobar__old because other objects depend on it
target-postgres       | DETAIL:  view analytics.stg_foobar depends on table tap_mongodb.foobar__old
target-postgres       | HINT:  Use DROP ... CASCADE to drop the dependent objects too.
target-postgres       | 
target-postgres       | CRITICAL ('foobar - Exception activating table version 1617612127282', DependentObjectsStillExist('cannot drop table tap_mongodb.foobar__old because other objects depend on it\nDETAIL:  view analytics.stg_foobar depends on table tap_mongodb.foobar__old\nHINT:  Use DROP ... CASCADE to drop the dependent objects too.\n'))

I believe that target-postgres is saying here that it cannot drop the old table during table-switching because the views created by dbt are linked to it. I was looking into late binding views in dbt, but I'm not sure if that config option only applies to redshift.

Would it be appropriate for target-postgres to be using DROP ... CASCADE in these instances?

[nbparker]

@andrewcstewart did you find a solution for this issue? Running into the same problem with a materialised view on the target table.

2022-05-30 00:06:51 | DETAIL:  materialized view calculation_checkout depends on table staging.checkout_cart
2022-05-30 00:06:51 | HINT:  Use DROP ... CASCADE to drop the dependent objects too.
...
2022-05-30 00:06:51 | psycopg2.errors.DependentObjectsStillExist: cannot drop table staging.checkout_cart because other objects depend on it

[dkarzon]

@nbparker I got around this by removing the table versoning completely with a transform between the tap and target.
Blocking the ACTIVATE_VERSION messages and removing the version property on RECORD messages.

This lets target-postgres update the tables data in place instead of creating a new table and swaping them out. It does however mean I need to manage schema changes to the table myself now.