X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 117EB9DC8AB for ; Tue, 31 Jan 2006 19:51:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92638-04 for ; Tue, 31 Jan 2006 19:51:35 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id B770D9DC861 for ; Tue, 31 Jan 2006 19:26:28 -0400 (AST) Received: from mailserver.sandvine.com (sandvine.com [199.243.201.138]) by svr4.postgresql.org (Postfix) with ESMTP id 1C8C65AF835 for ; Tue, 31 Jan 2006 23:26:32 +0000 (GMT) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: partitioning and locking problems Date: Tue, 31 Jan 2006 18:25:01 -0500 Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: partitioning and locking problems Thread-Index: AcYmvcI/wJEcTqj/SumMC+ZFsd4mVQ== From: "Marc Morin" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/500 X-Sequence-Number: 16978 We have a large database system designed around partitioning. Our application is characterized with =20 - terabytes of data - billions of rows in dozens of base tables (and 100s of paritions) - 24x7 insert load of new data that cannot be stopped, data is time sensitive. - periodic reports that can have long running queries with query times measured in hours =20 We have 2 classes of "maintenance" activities that are causing us problems: - periodically we need to change an insert rule on a view to point to a different partition. - periodically we need to delete data that is no longer needed. Performed via truncate. =20 Under both these circumstances (truncate and create / replace rule) the locking behaviour of these commands can cause locking problems for us. The scenario is best illustrated as a series of steps: =20 1- long running report is running on view 2- continuous inserters into view into a table via a rule 3- truncate or rule change occurs, taking an exclusive lock. Must wait for #1 to finish. 4- new reports and inserters must now wait for #3. 5- now everyone is waiting for a single query in #1. Results in loss of insert data granularity (important for our application). =20 Would like to understand the implications of changing postgres' code/locking for rule changes and truncate to not require locking out select statements? =20 =20 The following is a simplified schema to help illustrate the problem. =20 create table a_1 ( pkey int primary key ); create table a_2 ( pkey int primary key ); =20 create view a as select * from a_1 union all select * from a_2; =20 create function change_rule(int) returns void as ' begin execute ''create or replace rule insert as on insert to a do instead insert into a_''||$1||''(pkey) values(NEW.pkey)''; end; ' language plpgsql; =20 -- change rule, execute something like the following periodically select change_rule(1); =20 We've looked at the code and the rule changes appear "easy" but we are concerned about the required changes for truncate. =20 Thanks Marc