Unable to run any postgresql command on a table after trying to alter it

Question

I have created a database with several tables, including tables named users and recipes. At last I have created a table of comments. Here is how I defined those tables:

CREATE TABLE recipes(  
    id SERIAL PRIMARY KEY,
    recipe_name TEXT NOT NULL,
    num_portions INTEGER,
    preparation_time INTEGER,
    cooking_time INTEGER
);

CREATE TABLE users(
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    user_password TEXT NOT NULL UNIQUE,
    last_login DATE
);

CREATE TABLE comments(
    id SERIAL PRIMARY KEY,
    id_user INTEGER NOT NULL REFERENCES users(id),
    id_recipe INTEGER NOT NULL REFERENCES recipes(id),
    comment_description TEXT NOT NULL
);

Commands such as select and insert worked on the table comments until I tried to add a column, with the following command:

ALTER TABLE comments
ADD COLUMN date_of_comment TIMESTAMP;

This failed to succeed and it ran indefinitely. I wanted to drop it with DROP TABLE comments, which ran indefinitely as well. Now when I try to run any command, such as SELECT, on this table it runs indefinitely.

I tried to create the table comments again to check if it got deleted when I tried to drop it with the command:

CREATE TABLE comments(
    id SERIAL PRIMARY KEY,
    id_user INTEGER NOT NULL REFERENCES users(id),
    id_recipe INTEGER NOT NULL REFERENCES recipes(id),
    comment_description TEXT NOT NULL,
    date_of_comment TIMESTAMP
);

However I get an error "relation "comments" already exists" and "Cannot read property 'document' of undefined". Does anyone know what went wrong?

Why have you tagged `sql-server` if you're asking about `postgresql`? — Stu, Jun 17 '23 at 11:53
If you *are* using SQL Server then you're using multiple deprecated data types. — Thom A, Jun 17 '23 at 12:36
Someone is holding a lock on the table. See https://wiki.postgresql.org/wiki/Lock_Monitoring — jjanes, Jun 17 '23 at 16:29
Have you tried closing all connections https://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection etc — Charlieface, Jun 18 '23 at 00:02
Side note: I sincerely hope you are not going to store plain-text passwords... — Charlieface, Jun 18 '23 at 00:03
Do you really intend to allow duplicate user names but require unique passwords? — JohnH, Jun 18 '23 at 00:33

score 0 · Answer 1 · answered Jun 18 '23 at 06:58

Before you ran the ALTER TABLE statement, some concurrent transaction has accessed the table, and that transaction is still open. Such a transaction has a (probably weak) lock on the table, which blocks the ACCESS EXCLUSIVE lock that ALTER TABLE requires.

Once your ALTER TABLE is hanging, all other access to the table is blocked, because it has to queue behind the ACCESS EXCLUSIVE lock.

Find the session that accessed comments and never closed its transaction and kill it using pg_terminate_backend().

Unable to run any postgresql command on a table after trying to alter it

1 Answers1