Postgres client locking up when creating new table

Question

I'm trying to create a new table in postgres but when I do it just hangs after the CREATE TABLE call.

$ sudo usermod -s /bin/bash postgres
$ sudo su - postgres
postgres@host:~$ psql ranking_analytics
psql (8.4.8)
Type "help" for help.

ranking_analytics=# BEGIN;
BEGIN
ranking_analytics=# CREATE TABLE "about_contactmessage" (
ranking_analytics(#     "id" serial NOT NULL PRIMARY KEY,
ranking_analytics(#     "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
ranking_analytics(#     "subject" text NOT NULL,
ranking_analytics(#     "message" text NOT NULL,
ranking_analytics(#     "recorded_time" timestamp with time zone NOT NULL
ranking_analytics(# )
ranking_analytics-# ;
NOTICE:  CREATE TABLE will create implicit sequence "about_contactmessage_id_seq" for serial column "about_contactmessage.id"

It will then just sit here indefinately until I CTRL-C it.

There are other tables in the database and this one doesn't already exist:

ranking_analytics=# \d about_contactmessage
Did not find any relation named "about_contactmessage".

I'm able to do insert and delete queries on other tables in the database without a problem:

ranking_analytics=# insert into locations_continent (continent_name) VALUES ('testing');
INSERT 0 1
ranking_analytics=# delete from locations_continent where continent_name = 'testing';
DELETE 1

There is plenty of drive space on the machine:

$ df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/xvda               21G   2.3G    18G  12% /
devtmpfs               255M   132k   255M   1% /dev
none                   257M   476k   256M   1% /dev/shm
none                   257M    54k   257M   1% /var/run
none                   257M      0   257M   0% /var/lock

Any ideas what could be wrong?

sudo -u postgres psql is easier, btw.

Craig Ringer
– Craig Ringer

2012-07-03 14:17:22 +00:00
Commented Jul 3, 2012 at 14:17 — Craig Ringer
– Craig Ringer, Commented Jul 3, 2012 at 14:17

tobixen · Accepted Answer · 2020-08-16 14:24:46Z

23

If restarting postgres is an option, then that will most likely solve the issue and will save you from spending time reading the rest of this answer :-)

Check the pg_stat_activity view, there is probably some other transaction blocking the schema change.

select * from pg_stat_activity 
where 
wait_event_type is NULL and xact_start is not NULL order by xact_start;

(the pg_stat_activity is changed a bit in every major pg release, try this for elder versions):

select * from pg_stat_activity 
where 
not waiting and xact_start is not NULL order by xact_start;

The first row to show up is probably the one causing problems. It is often an "idle in transaction" - this may very well hold locks, and if it's an old transaction it may as well kill performance. Probably the programmer forgot to ensure ending the transaction with "commit" or "rollback", or maybe some db session got stuck due to network problems.

To terminate transaction with pid 1234, use select pg_cancel_backend(1234);, if that fails, select pg_terminate_backend(1234). With shell access, the equivalent commands are kill -INT 1234 and kill 1234. (keep in mind, kill -9 1234 is a really bad idea).

There is also a view pg_locks which may give some insight, though it may probably not be that easy to get any useful info out from it. If granted is true, the lock is held, when granted is false it means the query is waiting for the lock. Here are some more hints here on how to extract useful info from pg_locks: http://wiki.postgresql.org/wiki/Lock_Monitoring

If everything else fails, then it's probably time to go for the simple solution, restart that database server.

edited Aug 16, 2020 at 14:24

answered Jul 3, 2012 at 8:03

tobixen

4,1331 gold badge22 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mark L Over a year ago

I found the query which was just hanging on

ALTER TABLE "about_contactmessage" ADD CONSTRAINT "user_id_refs_id_2a9a546ebb1d6a8b" FOREIGN KEY ("user_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED;

, I killed it with select pg_cancel_backend(30969); and ran the create table command again but it's still just hanging. The ALTER TABLE statement was the only one running on the DB at the time.

Mark L Over a year ago

I just had a look at pg_locks, it's full of integers. I'm going to restart postgres and try to create the table again.

Mark L Over a year ago

a combination of killing the hung process, restarting postgres and then trying again fixed it. Thank you very much!

tobixen Over a year ago

I'll add "restart postgres" to the answer. I thought of it, but found it too silly ;-)

Steve Over a year ago

The "waiting" column was replaced with "wait_event_type" and "wait_event" between 9.5 and 9.6. You can probably replace it in the query with wait_event_type IS NULL.

|

fsniper · Accepted Answer · 2014-03-05 14:27:50Z

6

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 auth_user 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.

answered Mar 5, 2014 at 14:27

fsniper

3312 silver badges6 bronze badges

Collectives™ on Stack Overflow

Postgres client locking up when creating new table

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related