waiting on index drop

Started by Rikard Pavelicover 17 years ago2 messagesgeneral
Jump to latest
#1Rikard Pavelic
rikard.pavelic@zg.htnet.hr

Hi!

Does Postgres needs to wait for this lock
or is this something that can be fixed?

Create data:

create table test_table
(
id varchar primary key
);

insert into test_table
select i::text
from generate_series(1000,100000) i;

Session 1:
select * from test_table where id like '1050%';
Uses seq_scan

Session 2:
begin transaction;
create index ix_1 on test_table(id) where id like '1050%';
select * from test_table where id like '1050%';
Uses indx_scan

Session 1:
select * from test_table where id like '1050%';
Uses seq_scan (great, index is not visible)

Session 2:
drop index ix_1;
select * from test_table where id like '1050%';
Uses seq_scan

Session 1:
select * from test_table where id like '1050%';
Blocked

Session 2:
commit;
Session 1 continues

Regards,
Rikard

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Rikard Pavelic (#1)
Re: waiting on index drop

Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:

Does Postgres needs to wait for this lock

yes --- drop index takes exclusive lock on the table.

or is this something that can be fixed?

no

regards, tom lane