Re: [GENERAL] Concurrent read from a partition table.

2011-07-15 Thread Chetan Suttraway 
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.

2011-07-12 Thread Chetan Suttraway 
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.

2011-07-12 Thread hyelluas 
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.

2011-07-11 Thread hyelluas 
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