On Tue, Jul 12, 2011 at 11:46 PM, hyelluas <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
