Re: DDL commands take forever

In response to "Leonardo M." Ramé <l(dot)rame(at)griensu(dot)com>:

> Hi, I need to create a trigger on a table used by our sofware, the
> problem is, when I issue a "create trigger...." on this table, it takes
> forever. It doesn't matter if I use pgAdmin, or psql.
>
> The only way to do it is by disconnecting all the instances of the
> program from the database, execute the DDL command, and reconnect the
> program again.
>
> What can be causing this behavior? any workaround?.

Most likely those programs have locks out that are causing the DDL command
to have to wait until the locks clear.

Disconnecting everyone is the quick workaround.

The real fix is to ensure that your application doesn't hold onto DB locks
longer than is needed, which won't guarantee that everything moves fast,
but will help immensely. Often this is caused by the application holding
transactions open for long periods of time, which is a bad idea anyway
since it interferes with vacuum and other maintenance operations.

You can look at the pg_locks table to see what locks are out, to help you
diagnose what apps are holding locks open.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/