Re: Concurrent read from a partition table.

[Chetan Suttraway <chetan.suttraway@xxxxxxxxxxxxxxxx>]

On Tue, Jul 12, 2011 at 11:46 PM, hyelluas <helen_yelluas@xxxxxxxxxx> 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 (a>10)) 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 a>10;

--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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general