pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres problem with partition queries
2+ messages / 2 participants
[nested] [flat]
* Postgres problem with partition queries
@ 2010-12-01 05:51 bakkiya <bakkiya@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: bakkiya @ 2010-12-01 05:51 UTC (permalink / raw)
To: pgsql-admin
Hi All,
We are having a partition table and we are querying some rows in the
partition.
select * from events where
events.evt_time >= '2010-11-29 00:00:00'
AND events.evt_time <= '2010-11-30 23:59:59'
While this query is in running state, I am issuing a drop statement on a
partition of this table.
drop table events_p_20101207130000
Please note that the query is running on a different partition and drop is
happening on a different partition.
Now, in a different session, I am running a different query,
select * from events where
events.evt_time >= '2010-11-28 00:00:00'
AND events.evt_time <= '2010-11-29 23:59:59'
Now the issue is both the drop and my 2nd query is in waiting state and both
these queries started running after my first queries completion.
We have set constraint_exclusion to on and we used, select * from
pg_stat_activity where waiting = 'true' query to check the status of the
waiting queries.
Now, can you please explain me on why it is behaving like this. Is this a
postgres bug or any configuration needs to be done to avoid this waiting.
Any help is really appreciated.
Thanks,
Bakki
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-problem-with-partition-queries-tp3287234p3287234.ht...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgres problem with partition queries
@ 2010-12-01 14:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: bakkiya <bakkiya@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-12-01 14:35 UTC (permalink / raw)
To: bakkiya <bakkiya@gmail.com>; +Cc: pgsql-admin
bakkiya <bakkiya@gmail.com> writes:
> We are having a partition table and we are querying some rows in the
> partition.
> select * from events where
> events.evt_time >= '2010-11-29 00:00:00'
> AND events.evt_time <= '2010-11-30 23:59:59'
> While this query is in running state, I am issuing a drop statement on a
> partition of this table.
> [ and it blocks ]
This is hardly surprising. A query on the inheritance tree has to
take AccessShare lock on every member table, because it has to inspect
each table to determine whether it should scan it. So it's going to
conflict with the exclusive lock needed to drop one of the tables.
That means first that the DROP gets blocked by a pre-existing query,
and second that the waiting DROP also blocks other queries of the same
kind.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-12-01 14:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-01 05:51 Postgres problem with partition queries bakkiya <bakkiya@gmail.com>
2010-12-01 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox