Create Table Postgres in new schema does not complete

Question

When trying to run a simple create statement in a new schema within postgres (healthcare), the query never finishes. My question is why?

CREATE TABLE healthcare.admission_source(
    admission_source_id INTEGER, 
    admission_source INTEGER,
    description varchar(255)
);

Verified the schema exists with ownership & rights:

I have ran this same query before, and it runs endlessly. I canceled it at several hours last time, but wanted to show it takes a long time to run a CREATE TABLE statement:

For comparison, the query runs for 42 msec on the public schema of this database. The table is created correctly and the query finishes quickly, so leaves me thinking that the healthcare schema is the issue. Would love all thoughts and assistance

@nbk PostgreSQL 13.8, compiled by Visual C++ build 1914, 64-bit — Langutang
– Langutang, Commented Mar 4, 2024 at 21:44
Which client application are you using? Does it have autocommit=off? Could be the CREATE has never been committed. — Charlieface
– Charlieface, Commented Mar 4, 2024 at 21:45
1) Make your life easier and use the Postgres command line client psql to do this. 2) Do a select on pg_stat_activity. I'm guessing there is a query on healthcare.admission_source that is holding a lock and preventing the CREATE TABLE. — user7070613
– user7070613, Commented Mar 4, 2024 at 22:34

jjanes · Accepted Answer · 2024-03-04 23:46:35Z

1

It is probably blocking on a lock. If you have log_lock_waits turned on, you can look in the log file to see what is going on.

This could happen for example of another session tried to create a table of the same name inside a transaction and didn't commit the transaction.

answered Mar 4, 2024 at 23:46

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

2 Comments

Langutang Over a year ago

This was exactly the issue - had a lock state on the queries and some that were idle. I was trying to run a create table from jupytr notebook while also in PG Admin. SOLUTION: I ran select * from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0; which showed me the blocked queries. Then ran select pg_cancel_backend(<PID>); & select pg_terminate_backend(<PID>); to terminate the locks. Have then successfully created the tables in the new schema. Thanks @jjanes and @Adrian Klaver

user7070613 Over a year ago

Glad it was solved. What it shows is the importance of holding connections open for only the period necessary to do the work and then closing them.

Collectives™ on Stack Overflow

Create Table Postgres in new schema does not complete

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related