Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id EB70B2E371B for ; Wed, 6 Feb 2008 07:28:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 71149-06 for ; Wed, 6 Feb 2008 07:28:10 -0400 (AST) Received: from hi-media.com (9.140-14-84.ripe.coltfrance.com [84.14.140.9]) by postgresql.org (Postfix) with ESMTP id E873C2E36C2 for ; Wed, 6 Feb 2008 07:28:07 -0400 (AST) Received: from dim (LAubervilliers-151-11-28-79.w193-251.abo.wanadoo.fr [193.251.57.79]) by hi-media.com (Postfix) with ESMTP id BF6031880A4; Wed, 6 Feb 2008 12:25:33 +0100 (CET) From: Dimitri Fontaine Organization: Hi-Media To: pgsql-performance@postgresql.org Subject: Re: Benchmark Data requested --- pgloader CE design ideas Date: Wed, 6 Feb 2008 12:27:56 +0100 User-Agent: KMail/1.9.7 Cc: Simon Riggs , "Jignesh K. Shah" , Greg Smith References: <1202150236.4252.521.camel@ebony.site> <1202221496.4252.680.camel@ebony.site> <200802051815.25800.dfontaine@hi-media.com> In-Reply-To: <200802051815.25800.dfontaine@hi-media.com> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart3352950.5fsGLLoQhK"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200802061227.58668.dfontaine@hi-media.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/63 X-Sequence-Number: 28605 --nextPart3352950.5fsGLLoQhK Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, I've been thinking about this topic some more, and as I don't know when I'l= l=20 be able to go and implement it I'd want to publish the ideas here. This way= =20 I'll be able to find them again :) Le mardi 05 f=C3=A9vrier 2008, Dimitri Fontaine a =C3=A9crit=C2=A0: > Le mardi 05 f=C3=A9vrier 2008, Simon Riggs a =C3=A9crit=C2=A0: > > Much better than triggers and rules, but it will be hard to get it to > > work. > > Well, I'm thinking about providing a somewhat modular approach where > pgloader code is able to recognize CHECK constraints, load a module > registered to the operator and data types, then use it. Here's how I think I'm gonna implement it: User level configuration =2D=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- At user level, you will have to add a constraint_exclusion =3D on parameter= to=20 pgloader section configuration for it to bother checking if the destination= =20 table has some children etc. You'll need to provide also a global ce_path parameter (where to find user= =20 python constraint exclusion modules) and a ce_modules parameter for each=20 section where constraint_exclusion =3D on: ce_modules =3D columnA:module:class, columnB:module:class As the ce_path could point to any number of modules where a single type is= =20 supported by several modules, I'll let the user choose which module to use. Constraint exclusion modules =2D=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- The modules will provide one or several class(es) (kind of a packaging issu= e),=20 each one will have to register which datatypes and operators they know abou= t.=20 Here's some pseudo-code of a module, which certainly is the best way to=20 express a code design idea: class MyCE: def __init__(self, operator, constant, cside=3D'r'): """ CHECK ( col operator constant ) =3D> cside =3D 'r', could be 'l' = """ ... @classmethod def support_type(cls, type): return type in ['integer', 'bigint', 'smallint', 'real', 'double'] @classmethod def support_operator(cls, op): return op in ['=3D', '>', '<', '>=3D', '<=3D', '%'] def check(self, op, data): if op =3D=3D '>' : return self.gt(data) ... def gt(self, data): if cside =3D=3D 'l': return self.constant > data elif cside =3D=3D 'r': return data > self.constant This way pgloader will be able to support any datatype (user datatype like= =20 IP4R included) and operator (@@, ~<=3D or whatever). For pgloader to handle= a=20 CHECK() constraint, though, it'll have to be configured to use a CE class=20 supporting the used operators and datatypes. PGLoader constraint exclusion support =2D=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- The CHECK() constraint being a tree of check expressions[*] linked by logic= al=20 operators, pgloader will have to build some logic tree of MyCE (user CE=20 modules) and evaluate all the checks in order to be able to choose the inpu= t=20 line partition. [*]: check((a % 10) =3D 1) makes an expression tree containing 2 check nodes After having parsed pg_constraint.consrc (not conbin which seems too much a= n=20 internal dump for using it from user code) and built a CHECK tree for each= =20 partition, pgloader will try to decide if it's about range partitioning (mo= st=20 common case).=20 If each partition CHECK tree is AND((a>=3Db, a