[YSQL] Effects of TRUNCATE persists even after ROLLBACK

[Karvy-yb]

Jira Link: DB-5304

Description

Steps to reproduce:

1. create database test;
2. \c test
3. create table tb_test(k int);
4. insert into tb_test values (1), (2), (3);
5. select * from tb_test;
6. begin transaction;
7. truncate tb_test;
8. select * from tb_test;
9. rollback;
10. select * from tb_test;

The result will still return empty table even though the TRUNCATE statement has been rolledback. Postgres treats TRUNCATE as DML and thus I was able to rollback TRUNCATE command.

Even if we don't allow rollback of TRUNCATE, we should specify that as a warning here.

[ddorian]

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

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. See Section 13.5 for more details.

TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

[jasonyb]

Duplicate of #1404