Обсуждение: Concurrent read from a partition table.
Hello, I’m having a problem with concurrent processing. 2 queries are accessing the same parent table that have 24 partitions. I see “shared lock is not granted “ for one of them on one of the children while the other query is running. Does the “ select from a parent table” make a lock on the children? How I can change it? The one of the queries runs hourly ( 8 min) on the server , the other one can be run by a user , and a few users can run the same query. Thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-read-from-a-partition-table-tp4577154p4577154.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Jul 12, 2011 at 6:01 AM, hyelluas <helen_yelluas@mcafee.com> wrote:
In general if there are no predicates in query, then select would be fired across all
children.
Hello,
I’m having a problem with concurrent processing.
2 queries are accessing the same parent table that have 24 partitions.
I see “shared lock is not granted “ for one of them on one of the children
while the other query is running.
Does the “ select from a parent table” make a lock on the children?
In general if there are no predicates in query, then select would be fired across all
children.
How I can change it?
The one of the queries runs hourly ( 8 min) on the server , the other one
can be run by a user , and a few users can run the same query.
Thank you.
Helen
Could you post a self-contained test case?
Regards,
Chetan
--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
The Enterprise PostgreSQL Company
Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
Hi Chetan, I'm not sure how I can create a test case, I'm running queryes on 50g of data to see this. My general questions are about locking children when select from a parent, I have not seen any documentation on it. Does any parameter like 'concurrent read' exist? I'm still new to postgres, came from oracle & mysql. Thanks. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-read-from-a-partition-table-tp4577154p4579785.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Jul 12, 2011 at 11:46 PM, hyelluas <helen_yelluas@mcafee.com> wrote:
I tried with the attached test case and found that both the tables - base as well as child tables are locked in shared mode for select queries.Hi Chetan,
I'm not sure how I can create a test case, I'm running queryes on 50g of
data to see this.
My general questions are about locking children when select from a parent, I
have not seen any documentation on it. Does any parameter like 'concurrent
read' exist? I'm still new to postgres, came from oracle & mysql.
Thanks.
Helen
For details, please check the attached inheritance_locks.txt file.
With "concurrent read" did you mean isolation settings?
Then please refer:
http://www.postgresql.org/docs/9.0/static/sql-set-transaction.html
Regards,
Chetan
--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
The Enterprise PostgreSQL Company
Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb