Postgresql – Dropping unique constraint on PostgreSQL table taking 10+ hours

postgresql

We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about half the rows in the table — old data. Now I want to simply remove the multi-column unique constraint, so I executed this SQL:

alter table my_big_table drop constraint my_constraint_name

However, I started the command 10+ hours ago, and it's still running with no sign of abating. Why would dropping a constraint be taking so long? Is there anything I'm missing, or any way to perform actions like this with some kind of progress or status indication?

(I'm using PostgreSQL 9.0 on a Windows 64-bit system, and I generally connect to it using pgAdmin, though I can use the command-line client or connect programmatically too.)

Best Answer

Step one - go into task manager, have a look and see if anything is actually happening. If you can't see a lot of cpu and/or disk bandwidth then it's not doing anything.

Step two - check what PostgreSQL says is happening. There are two system views useful here: pg_stat_activity and pg_locks. See what's in both. The database/relation numbers in pg_locks are internal OIDs - try SELECT OID,* FROM pg_class to see which tables/indexes are being locked.

My guess is that the system is idle and it's just waiting for an exclusive lock on the table in question and something else is blocking it. The two system views should show you what is happening though.

Related Solutions

PostgreSQL – Primary Key Disappears from Test Table

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

Drop and recreate the table.
Drop and recreate the schema or database.
(Temporarily) set log_statement or other relevant settings so the statement is not logged.
Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
Edit the log files.
etc.

Postgresql – check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

Best Answer

Related Solutions

PostgreSQL – Primary Key Disappears from Test Table

Postgresql – check key if exists in other table without fk constraint

Related Question