CODEWITHSUNDEEP
postgresqlgreenplum
Gurupreet Singh Bhatia
Gurupreet Singh Bhatia

Reputation: 728

Greenplum: Not able to drop/truncate tables

I have a table in Greenplum(4.3.5.1), i want to remove constraint as initially created with a primary key constraint. I tried to do but query is running for 2-3 hours, i have cancelled it as no other option left,

then i have takne a backup and tried to drop table but query is running 2-3 hours and finally i cancelled the query again (When drop table query executed, it is showing RowExclusiveLock on table pg_depend, pg_class and pg_type)

I just tried truncate also but same problem

Can anyone help on this, what could be the reason?? and what will be the best way to resolve this??

Regards

Upvotes: 1

Views: 2086

Answers (2)

0x0FFF
0x0FFF

Reputation: 5018

Most likely you hit a locking issue. First thing to check is pg_locks - it would show you the current locks on the table. I bet your table is locked by some process, this is why truncate and drop table is hanging. Find the blocking query and terminate it, then you would be able to easily drop/truncate target table.

Here is the query that would help you: 

select * from pg_locks where relation = 'mytablename'::regclass::oid;

Upvotes: 5

Istvan
Istvan

Reputation: 8572

You should use truncate:

TRUNCATE TABLE  table_name;

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

Upvotes: 1

Related Questions