Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to "Account" table.

My CREATE TABLE statement looks like this:

CREATE TABLE "NewTable"
   (
       "column1" VARCHAR(100) NOT NULL,
       "column2" INTEGER NOT NULL,
       PRIMARY KEY("column1")
       CONSTRAINT "SomeFK" FOREIGN KEY ("column2") REFERENCES "Account" ("ID")
    )

The above statement executed for more than 25 minutes and didn't complete. We killed the transaction.

When we remove the foreign key constraint, it executes immediately.

Can someone let us know what is the problem? We initially thought it was locking the "Account" table for some reason, but when we looked at "pg_locks", we did not find any entry for "Account" - only an exclusive lock for "NewTable".

share|improve this question
    
Just an idea: if you define column2 allowing NULLs, is it still slow? –  dezso Jul 3 '12 at 7:55
    
Is Account.ID indexed? Is NewTable.column2 indexed? –  FreshPhilOfSO Jul 3 '12 at 8:00
    
@Phil - does FK work without index on foreign column? –  dezso Jul 3 '12 at 8:07
    
@deszo, if column2 is NULL, it is still slow. Atleast we waited a minute before cancelling it. –  Shivasubramanian A Jul 3 '12 at 8:26
    
@Phil, Account.ID is the primary key for Account, so it already has an index. NewTable.column2 is not indexed yet, atleast when we create the table. –  Shivasubramanian A Jul 3 '12 at 8:27
show 3 more comments

2 Answers

up vote 7 down vote accepted

Your query got locked waiting for something - my bet is that it was waiting for some other transaction to finish.

When you're doing the create table, just issue (in another psql session):

select * from pg_locks where pid = XXX and not granted;

where xxx is pid of backend doing the create table.

This will show you what is the lock that the create table is waiting for.

share|improve this answer
    
Thank you so much. That revealed an exclusive lock on Account!! –  Shivasubramanian A Jul 5 '12 at 5:16
add comment

This is explained in this thread; http://www.postgresql.org/message-id/75218696-61be-4730-89f6-dd6058fa9eda@a28g2000prb.googlegroups.com

According to Tom Lane,

this create operation references Account table and PostgreSQL should add triggers to both tables. So if there is a long-running (probably idle) open transaction that's holding AccessShare lock on auth_user Postgres is blocked waiting for that xact to finish and release its lock. Everything else queues up behind the Create.

That PostgreSQL Mail thread is a good read.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.