I try to create table in my Postgres database. I use pgadmin in version 1.18.1. Postgres version on server is 8.3.12.
When I execute create table statement, usually it done in few ms, but sometimes it go on few minutes and I stopping it. Then I have to restart the Postgres server, which is undesirable. I tried create table before people come to work, but that didn't work too.
Has someone similar problem? How can I fix that? Restart server is sometimes really problematic.
Some GUI database administration programs with Postgres support start in noautocommit mode by default. This means that if you don't commit your statements implicitly this programs would maintain idle uncommitted sessions for long time, locking resources — especially when you do DDL statements like create table.
Check:
select now()-query_start as query_age, *
from pg_stat_activity
where state<>'idle'
order by 1 desc;
if you see lines with long times in query_age column and in idle in transaction state then you really need to enable autocommit in your client program. Actually you need to do this even if you don't…