CODEWITHSUNDEEP
sqlpostgresqlalter-table
Juliano Grams
Juliano Grams

Reputation: 545

Alter Table too slow in postgres

I'm trying to add a new column 

ALTER TABLE "Cidade" ADD COLUMN "BoundBox" VARCHAR(255)

to this table:

 "Cidade"
    "Id" integer not null
        constraint "Cidade_PK"
            primary key,
    "Nome" varchar(120),
    "EstadoId" integer not null
        constraint "Estado_Cidade_FK"
            references "Estado",
    "PontoCentralLatitude" numeric,
    "PontoCentralLongitude" numeric

But the query never finish, I've already waited for 5 minutes and nothing happened. The table has only 5,000 records, and I can't wait too much time since it block the access to the table. I have a test database (equal to production), and it worked very quickly. The postgres version is 9.5.6.

Upvotes: 23

Views: 34835

Answers (4)

User Pro
User Pro

Reputation: 253

If nothing blocks your query and query executing very long time and this table has many DELETE queries try to clean your table:

VACUUM (VERBOSE, ANALYZE) table_name;

Upvotes: 5

Yevhen Bondar
Yevhen Bondar

Reputation: 4707

If you are running PostgreSQL 9.6+ you can use pg_blocking_pids() to find PID of queries that lock your one.

select pid, pg_blocking_pids(pid) as blocked_by, query as blocked_query
from pg_stat_activity
where pg_blocking_pids(pid)::text != '{}';

Upvotes: 47

Raj
Raj

Reputation: 502

As per your description it seems this table is highly used in system and your alter statement is taking time to acquire a lock on table to do the job. Try to find a window where you get less load on system and run .

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 248235

This statement is very fast, but it needs an access exclusive lock on the table. There must be a long running transaction that holds a lock on the table and block you.

Use the pg_stat_activity view to find the long transaction.

Upvotes: 11

Related Questions