Framework: Model::truncate() cleares all foreign key records in postgres

[ppshobi]

• Laravel Version: 5.8
• PHP Version: 7.3
• Database Driver & Version: Postgres 10

Description:

I believe this is something specific with Postgres

I am trying to truncate a table using Model::truncate() which of course truncates the table, and deletes all the foreign keyed records in the other tables. Even though ON DELETE CASCADE is not specified.

Tried to truncate the table within Schema::disableForeignKeyConstraints() & Schema::enableForeignKeyConstraints() statements. Still it clears all the records related via any foreign key. No exceptions or any warnings also.

Eg: truncating (temporarily) currency table will clear all the records in the products table 🥶

Is this an expected behaviour? it was not the case with MySQL. Mysql triggered errors when the FK constraints are active. And it didn't delete FK records

[staudenmeir]

This behavior is indeed specific to PostgreSQL. It was introduced in #26389.

[ppshobi]

Okay. Any idea how I can truncate a table without delete cascade?

[staudenmeir]

What should happen with the referenced rows in products? Unlike MySQL, PostgreSQL doesn't allow you to create invalid data by disabling the foreign keys.

[ppshobi]

I agree Postgres is more strict in that way. But I would expect an exception to be thrown when you truncate a table with foreign keys active. Since its Postgres, you probably will have to set the column ON DELETE to NO ACTION which seems the default for laravel foreign keys.

Also, I can truncate a table without cascading by default. By disabling the triggers on that table.

ALTER TABLE "table_name" DISABLE TRIGGER ALL;
and enable them back
ALTER TABLE "table_name" ENABLE TRIGGER ALL;

[driesvints]

Is this more a bug or a feature request?

[ppshobi]

I think it should be treated as a bug. Because

1. Schema::disableForeignKeyConstraints() doesn't disable foreign key constraints in Postgres. (I was using postgres 10)
2. Model::truncate() by default applies CASCADE option which in turn deletes all the related records, I think a more explicit way is better. like Model::truncate()->cascade()?. Otherwise, there is a higher probability that people like me may clear out the entire database. (This is clearly not a bug, but I think its kind of bug prone feaure)

[driesvints]

@staudenmeir what do you think?

[staudenmeir]

The current behavior is not ideal, but I don't really see a better solution. If we remove the cascade option, there is no "Laravel way" to truncate tables that are referenced by foreign keys on PostgreSQL (unlike on MySQL where you can use disableForeignKeyConstraints() ).

Schema::disableForeignKeyConstraints() doesn't disable foreign key constraints in Postgres.

Is there a PostgreSQL equivalent to MySQL's SET FOREIGN_KEY_CHECKS=0?

I think a more explicit way is better

The issue with such an option is that it would specific to PostgreSQL. I don't think that's ideal.

[ppshobi]

Hmm... Does it seem like for specific features it will be better to use some hacks (raw SQL queries)?. But at the same time I see disableForeignKeyConstraints() is specific to MySQL/SQLite.

[driesvints]

I don't think we'll be able to provide a fix for this anytime soon. We could maybe look into adding some Postgres specific functionality but that's more of a feature request.

[crazyscience]

And what about not destroying databases? Is that something a fix could be provided for? We DB::truncate()ed a table with 3 rows in and it cascaded to clear out 250GB of data across 4 other referencing tables that was vaguely related. No warning. Laravel was just like "oh yeah! I totally truncated that table, just like I was told to!". Perhaps rename truncate() to truncateAllYourTablesIrresponsiblyAndWithoutWarning(). smh...couldn't be a more dangerous, undocumented, reckless piece of code in Laravel. TRUNCATE table; is VERY different from TRUNCATE table CASCADE;. This is stupid. And you close the issue like it doesn't matter if data is destroyed without warning. @driesvints This should be reopened and the related PR that Roni pushed through should be reverted. Hidden data destruction is super not cool.

[crazyscience]

I mean, seriously. I said to remove 3 rows, not 300,000,000 rows across 4 tables. That's why the table name is specified in the truncate. Because that's the table to truncate. NOT a bunch of other stuff.

[devcircus]

Doesn't truncate just defer to the database specific implementation of truncate? If so, it would follow your db design and cascade only if cascade is specified. Honestly haven't looked at the source in a while, but seems like remembering that's how it behaves.

[crazyscience]

@devcircus Yeah, what you're describing is how it SHOULD behave. I couldn't possibly agree with you more. But unfortunately, someone who's name must not be spoken in #26389 decided that the best default action was to cascade truncates to all foreign referenced tables. For my team, this resulted in a massive production database loss while intentionally truncating a very small lookup table for reseeding. If you look at #26389, you can very clearly see how the code was changed to add the word cascade, thus implementing this vastly more dangerous query. It should have never hit production, yet here we are.

[crazyscience]

This was further discussed in a new PR to revert the reversion of the reversions (Yo dawg. I heard you like reversions...). #34527

[crazyscience]

@blakethepatton has provided a basic example repository that demonstrates the dangers of this particular issue and allows for simple failure reproduction. https://github.com/blakethepatton/pg-truncate-example