Thread: BUG #11321: describe command is getting locked due to ALTER statement running on public table

The following bug has been logged on the website:

Bug reference:      11321
Logged by:          Ashutosh Durugkar
Email address:      adurugkar@xento.com
PostgreSQL version: 9.3.5
Operating system:   Ubuntu 12.04
Description:

Hello Experts,

I found this bug in postgresql-9.3.5 version-

describe command is getting locked due to ALTER statement running on real
table.

How to reproduce-


Session 1-

create table test (t int, name varchar(10));
alter table test add constraint uk_id UNIQUE  (t);
BEGIN;
alter table test DROP constraint uk_id ;

Session 2-

BEGIN;

\d test      --This command goes into waiting state.


postgres=# select query from pg_stat_activity where waiting='t';
                                                                query

--------------------------------------------------------------------------------------------------------------------------------------
 SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),+
   pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable,
condeferred, c2.reltablespace                             +
 FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
                                                        +
   LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND
conindid = i.indexrelid AND contype IN ('p','u','x'))        +
 WHERE c.oid = '2309188489' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
                                                        +
 ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;


It was working fine on 9.3.4 version.

Thanks for looking.

adurugkar@xento.com writes:
> describe command is getting locked due to ALTER statement running on real
> table.

This isn't a bug IMO; if anything, it's a bug that the old implementation
of pg_get_indexdef didn't result in acquiring any lock.  Without a lock,
you're at least at risk of printing garbage information, and I suspect
crashes were not out of the question.

> It was working fine on 9.3.4 version.

I rather doubt this behavior changed just in 9.3.5.  That code got
rewritten during 9.3 development.

            regards, tom lane