Framework: Truncating tables in mysql, sqlite, sqlserver abstractions do not cascade, postgres does. This is unexpected and a leaky abstraction

[kdocki]

• Laravel Version: 5.8 (or higher)
• PHP Version: 7.4
• Database Driver & Version: postgresql 12

Description:

When you truncate tables using the laravel illuminate db builder it truncates the table as expected. However, postgresql is different because it changes the DEFAULT behavior of truncate from RESTRICT to CASCADE. This means that you can loose all your data in other "related" tables (something that doesn't happen with the other sql drivers)

Steps To Reproduce:

Fire up postgresql 12 and mysql.
Create 2 tables in both databases. table_a has a foreign key constraint to table_b.
Change your .env to point to mysql db
Run this code DB::table('table_b')->truncate();
you get an error with mysql, because you cannot truncate tables with foreign key constraints by default - this is expected!!!

Change your .env to point to postgersql db
Run this code DB::table('table_b')->truncate();
it deletes your data in both table_b and table_a, which is totally UNEXPECTED behavior

[driesvints]

Thanks. I've sent in a PR to the docs to warn about this: https://github.com/laravel/framework/issues/35157

Most likely we won't change this behavior as it'd be a breaking change to anyone expecting the current behavior.

[tabennett]

@driesvints I don't think anyone is expecting the current behavior. This almost wrecked our company's production servers and is going end up really screwing something up for someone else. Also, this behavior itself is a breaking change that was introduced in https://github.com/laravel/framework/pull/26389 two years ago. I understand that this is a BC break, but is there a reason we can't get a fix for this on the 9x branch?

[driesvints]

@tabennett unfortunately we can't revert the current behavior as there are two things we need to keep in mind here: the breaking change for people expecting the current behavior and the fact that if we revert it it'll be impossible to truncate tables in PostgreSQL when foreign keys are enabled. So it would essentially prevent certain apps from using it at all.

See @staudenmeir's reply here: https://github.com/laravel/framework/issues/29506#issuecomment-520691983

We could maybe look into to pass some sort of flag somehow so you can at least toggle the behavior.

[tabennett]

@driesvints Just wanted to say thank you for your help and time. I understand that this is a hard thing to abstract because the behaviors of these two DBMS are entirely different. Having it as a flag to truncate() would be a much better solution (if you're using Msql or something besides postgres, the flag could be a no op) and prevent a nightmare scenario from unfolding for someone.

[driesvints]

Thanks. I'm currently thinking of a specific truncate method for the postgres grammar maybe. That wouldn't be breaking I believe. We're open to PRs if anyone wants to give that a go.

[kdocki]

If you want to truncate a table with foreign keys on it, you should call raw DB::statement('truncate table_b cascade') because you actually know what you're doing to your database. Having cascade override the default is definitely not expected behavior, it is restrict as you can see in the postgresql docs: :smile:

https://www.postgresql.org/docs/9.1/sql-truncate.html

RESTRICT
Refuse to truncate if any of the tables have foreign-key references from tables that are not listed in the command. This is the default.

It would appear that this terrible pull request was accepted into laravel like 2 years ago and now we are afraid to change it. I don't think we should be. The worst thing that happens is that their code breaks because they can't do a cascade delete, in which a simple update to the code would allow them to truncate cascade. If we leave this bug in place, it will potentially wreck people's databases because cascading truncates are not expected at all.

[driesvints]

Like I said several times already, we aren't going to revert the current behavior at this time. Especially not in a stable release.

[kdocki]

Okay. I understand. What about in a future release? This change could be mentioned in a upgrade changes document and anyone who is relying on this weird quirky behavior would have an upgrade path?

[driesvints]

We're only looking at a way to provide the other behavior as well at this time, sorry. This has now also been properly documented.