PostgreSQL truncation is broken on 2.0.1

[GoktugOzturk]

With the recent change on #58 you started using RESTRICT But it breaks our tests right now.

You should only use RESTRICT if the KEEP_TABLES option is defined. Otherwise, CASCADE is the only option to truncate related tables without getting errors.

here is the error:

ActiveRecord::StatementInvalid:
  PG::FeatureNotSupported: ERROR:  cannot truncate a table referenced in a foreign key constraint
  DETAIL:  Table "teams" references "events".
  HINT:  Truncate table "teams" at the same time, or use TRUNCATE ... CASCADE.

[foton]

Downgrading to gem 'database_cleaner-active_record', '2.0.0' fixes it.
I got weider version of error (for 2.0.1)

ActiveRecord::StatementInvalid:
  PG::FeatureNotSupported: ERROR:  cannot truncate a table referenced in a foreign key constraint
  DETAIL:  Table "product_variants" references "product_variants".
  HINT:  Truncate table "product_variants" at the same time, or use TRUNCATE ... CASCADE.

note the same table references.
But structure.sql do not mention such reference

ALTER TABLE ONLY catalog.product_variants
    ADD CONSTRAINT variants_product_id_fk FOREIGN KEY (product_id) REFERENCES catalog.products(id) ON DELETE CASCADE;


ALTER TABLE ONLY stats.product_variants
    ADD CONSTRAINT fk_rails_ba48c4f29e FOREIGN KEY (product_variant_id) REFERENCES catalog.product_variants(id);

[marcocarvalho]

The #58 change also breaks if you use active storage.

ActiveRecord::StatementInvalid:
       PG::FeatureNotSupported: ERROR:  cannot truncate a table referenced in a foreign key constraint
       DETAIL:  Table "active_storage_variant_records" references "active_storage_blobs".
       HINT:  Truncate table "active_storage_variant_records" at the same time, or use TRUNCATE ... CASCADE.

maybe we could add some option to choose between CASCADE and RESTRICT.

[tanelsuurhans]

Is there any plans on mitigating this issue?

[bsuchodolski]

@GoktugOzturk @foton @marcocarvalho @tanelsuurhans I was the original contributor who introduced using RESTRICT because in organization I was working at the time using CASCADE was silently dropping tables that we wanted to not drop. It cause us a lot of time to debug what is happening and this change was intended to make it more visible if you don't declare upfront all related tables you want to retain in the database.

I know that the issue was introduced almost 2 years ago and I'm sorry I haven't noticed it since then, which isn't the best example of how ownership should work in OSS 🙈

Could you give me more details on the issue? Are the tables that cause it to blow up created dynamically so you don't know what are their names at the time of declaring which tables to truncate?

[aurels]

As of today, 1.99.0 is still the newest version working for us.

[thegeorgeous]

@bsuchodolski, in our case, it is marked as part of the tables to be truncated. There are other tables containing configurations that are not truncated. However, the tables raising the error are marked for truncation but raise the error because of foreign key references. Would it make sense to set RESTRICT and CASCADE via configuration rather than hard-coding it?

[ceneon]

When doing a big Ruby+Rails upgrade on a Rails 5 app, I've encountered this same issue when needing to bump DatabaseCleaner and other gems for compatibility.
For now I forked the repository and reverted this change: #58

I think this could be an option to choose from as variants of the truncation strategy.