Can rename table but can not truncate table, renametruncate
A table cannot be truncate but can be rename. This sounds strange at first. The process is simulated below.
Three sessions:
Session1 performs the truncate and rename operations;
Session2 performs the lock table operation;
Session3 is monitored.
Session1:
[gpadmin@wx60 contrib]$ psql gtlionspsql (8.2.15)Type "help" for help. gtlions=# \d test Table "public.test" Column | Type | Modifiers --------+------------------------+----------- id | integer | name | character varying(200) | Indexes: "idxtestid" btree (id) "idxtestname" btree (name)Distributed by: (id) gtlions=# select pg_backend_pid(); pg_backend_pid ---------------- 1473(1 row) gtlions=# truncate table test;Cancel request sentERROR: relation "test" does not existgtlions=# alter table test rename to test1;ALTER TABLE
Session2:
[gpadmin@wx60 ~]$ psql gtlionspsql (8.2.15)Type "help" for help. gtlions=# select pg_backend_pid(); pg_backend_pid ---------------- 1555(1 row) gtlions=# begin;BEGINgtlions=# select * from test limit 10; id | name -------+------------ 19672 | 19672-asfd 19674 | 19674-asfd 19676 | 19676-asfd 19678 | 19678-asfd 19680 | 19680-asfd 19682 | 19682-asfd 19684 | 19684-asfd 19686 | 19686-asfd 19688 | 19688-asfd 19690 | 19690-asfd(10 rows) gtlions=# end;COMMIT
Session3:
[gpadmin@wx60 ~]$ psql gtlionspsql (8.2.15)Type "help" for help. gtlions=# select locktype,relation::regclass,pid,mode,granted,gp_segment_id from pg_locks where pid in (1473,1555) order by pid; locktype | relation | pid | mode | granted | gp_segment_id ----------+----------+-----+------+---------+---------------(0 rows) gtlions=# select locktype,relation::regclass,pid,mode,granted,gp_segment_id from pg_locks where pid in (1473,1555) order by pid; locktype | relation | pid | mode | granted | gp_segment_id ---------------+-------------+------+-----------------+---------+--------------- relation | test | 1555 | AccessShareLock | t | -1 relation | idxtestname | 1555 | AccessShareLock | t | -1 transactionid | | 1555 | ExclusiveLock | t | -1 relation | idxtestid | 1555 | AccessShareLock | t | -1(4 rows) gtlions=# select locktype,relation::regclass,pid,mode,granted,gp_segment_id from pg_locks where pid in (1473,1555) order by pid; locktype | relation | pid | mode | granted | gp_segment_id ---------------+-------------+------+---------------------+---------+--------------- transactionid | | 1473 | ExclusiveLock | t | -1 relation | test | 1473 | AccessExclusiveLock | f | -1 transactionid | | 1555 | ExclusiveLock | t | -1 relation | idxtestid | 1555 | AccessShareLock | t | -1 relation | idxtestname | 1555 | AccessShareLock | t | -1 relation | test | 1555 | AccessShareLock | t | -1(6 rows)
-EOF-
SQL help Cannot truncate table 'tranlog' because it is partitioned what causes the above error?
Because your table is a partition table, so you can not truncate it immediately.
Perhaps you can truncate it by partition. or, you can delete it direct.
And then, create table temp as select * from yourtable where rownum <1, then drop your table.
Perhaps can help you!
Give your withs!
The Truncate table deletes all rows in the table, but the structure, columns, and constraints in the table will not be changed?
Deleting all rows does not mean deleting everything. Just like in windows, if you delete all the data on a disk, will your disk disappear?
No way. You just deleted the data from a non-system disk. The size of your disk volume, statistics, and drive letter are all there.
Only when you delete the physical information of the drive letter will the disk disappear during reformatting. This is the drop in the database. The drop table will be used to delete the table fundamentally, all constraints will disappear.