Re: [GENERAL] Concurrent read from a partition table.
On Tue, Jul 12, 2011 at 11:46 PM, hyelluas helen_yell...@mcafee.com wrote: 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 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. 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 Website: www.enterprisedb.com EnterpriseDB Blog : http://blogs.enterprisedb.com Follow us on Twitter : http://www.twitter.com/enterprisedb --create base table Create table myt( a int, b int); --create child table Create table myt_d( check (a10)) inherits(myt); -- Trigger function which will divert insert into particular partition table create or replace function insert_myt_trigger_func() returns trigger as $$ declare vsql text; begin IF (new.a 10) THEN vSql := 'insert into myt_d values('||new.a||','||new.b||')'; execute vSql; ELSE RAISE EXCEPTION 'column value for a out of range. Fix insert_my_trigger_func() function!'; END IF; return null; end; $$ language plpgsql; --trigger for insert..redirect from base to child create trigger insert_myt_trigger before insert on myt for each row execute procedure insert_myt_trigger_func(); --should fail insert into myt values(10,10); --should be successfully inserted insert into myt values(11,11); begin work; select count(*) from myt where a10; --get oid of base and child tables select oid from pg_class where relname='myt_d' or relname='myt'; --check if both of them are locked select locktype, relation, mode from pg_locks; pg=# select oid from pg_class where relname='myt_d' or relname='myt'; oid 271078 271081 (2 rows) pg=# select locktype, relation, mode from pg_locks; locktype | relation | mode +--+- virtualxid | | ExclusiveLock relation | 271081 | AccessShareLock virtualxid | | ExclusiveLock relation | 271078 | AccessShareLock relation |11001 | AccessShareLock (5 rows) pg=# -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Concurrent read from a partition table.
On Tue, Jul 12, 2011 at 6:01 AM, hyelluas helen_yell...@mcafee.com wrote: 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 Website: www.enterprisedb.com EnterpriseDB Blog : http://blogs.enterprisedb.com Follow us on Twitter : http://www.twitter.com/enterprisedb
Re: [GENERAL] Concurrent read from a partition table.
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general