Can rename table but can not truncate table, renametruncate

Source: Internet
Author: User
Tags psql

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.