Hacker News Reader

Comment by matthijs | Hacker News Reader

Using `truncate` in combination with `cascade` is another that I found unexpected:

"Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE."

So it will simply clear out other tables that reference table to truncate, even if you have `on delete set null` and the foreign key column is null.

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

View on HN

Another footgun is that, while `DELETE FROM table_name` is transactional, TRUNCATE is not transactional. Once you push the truncate button, that data is gone in every transaction everywhere all at once.

You should be very hesitant about using TRUNCATE on a production database unless that table (and all related foreign keyed tables) are truly ephemeral. Even if the data is cleared every night at midnight, for example, is there going to be a 10 second analysis transaction running across the midnight boundary that will fail hard with suddenly missing rows?

Running a full delete on the rows and vacuum will still result in a tiny file on storage and doesn't wake me up in a cold sweat when I have a flashback. Even renaming the table is in many ways safer.

TRUNCATE is absolutely transactional. You can rollback a TRUNCATE statement if you run it in a transaction.

https://dbfiddle.uk/xkgzxMUU

The only difference to other DML statements is, that it will put an exclusive lock on the table. So until the TRUNCATE is committed, no other transaction can read from the table.

> The only difference to other DML statements is

Truncate is DDL. It's like dropping and recreating the table in a single operation.

> TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred.

Sorry, that's what I mean. It's safe in the sense that you can roll it back, but it's not safe in the sense that other concurrent transactions will see the table as empty if they are long-running.

Reply on news.ycombinator.com