From: Dimitri Fontaine <dfontaine@hi-media.com>
Organization: Hi-Media
To: pgsql-performance@postgresql.org
Subject: Re: Benchmark Data requested
Date: Tue, 5 Feb 2008 18:15:25 +0100
User-Agent: KMail/1.9.7
Cc: Simon Riggs <simon@2ndquadrant.com>, "Jignesh K. Shah" <J.K.Shah@sun.com>,
	Greg Smith <gsmith@gregsmith.com>
References: <1202150236.4252.521.camel@ebony.site>
	<200802051506.51581.dfontaine@hi-media.com>
	<1202221496.4252.680.camel@ebony.site>
In-Reply-To: <1202221496.4252.680.camel@ebony.site>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart7471088.g6Kd9S5Ef1";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200802051815.25800.dfontaine@hi-media.com>

--nextPart7471088.g6Kd9S5Ef1
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Le mardi 05 f=C3=A9vrier 2008, Simon Riggs a =C3=A9crit=C2=A0:
> I'll look at COPY FROM internals to make this faster. I'm looking at
> this now to refresh my memory; I already had some plans on the shelf.

Maybe stealing some ideas from pg_bulkload could somewhat help here?
  http://pgfoundry.org/docman/view.php/1000261/456/20060709_pg_bulkload.pdf

IIRC it's mainly about how to optimize index updating while loading data, a=
nd=20
I've heard complaints on the line "this external tool has to know too much=
=20
about PostgreSQL internals to be trustworthy as non-core code"... so...

> > The basic idea is for pgloader to ask PostgreSQL about
> > constraint_exclusion, pg_inherits and pg_constraint and if pgloader
> > recognize both the CHECK expression and the datatypes involved, and if =
we
> > can implement the CHECK in python without having to resort to querying
> > PostgreSQL, then we can run a thread per partition, with as many COPY
> > FROM running in parallel as there are partition involved (when threads =
=3D
> > -1).
> >
> > I'm not sure this will be quicker than relying on PostgreSQL trigger or
> > rules as used for partitioning currently, but ISTM Jignesh quoted =C2=
=A7 is
> > just about that.
>
> 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 pgload=
er=20
code is able to recognize CHECK constraints, load a module registered to th=
e=20
operator and data types, then use it.
The modules and their registration should be done at the configuration leve=
l,=20
I'll provide some defaults and users will be able to add their code, the sa=
me=20
way on-the-fly reformat modules are handled now.

This means that I'll be able to provide (hopefully) quickly the basic cases=
=20
(CHECK on dates >=3D x and < y), numeric ranges, etc, and users will be abl=
e to=20
care about more complex setups.

When the constraint won't match any configured pgloader exclusion module, t=
he=20
trigger/rule code will get used (COPY will go to the main table), and when=
=20
the python CHECK implementation will be wrong (worst case) PostgreSQL will=
=20
reject the data and pgloader will fill your reject data and log files. And=
=20
you're back to debugging your python CHECK implementation...

All of this is only a braindump as of now, and maybe quite an optimistic=20
one... but baring any 'I know this can't work' objection that's what I'm=20
gonna try to implement for next pgloader version.

Thanks for comments, input is really appreciated !
=2D-=20
dim

--nextPart7471088.g6Kd9S5Ef1
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBHqJmtlBXRlnbh1bkRAlKvAJ0es/QK4oU+HrB2/CUeuR3X46jCwwCgmduK
IMiJvybmpUBOvuXLDPoTmvk=
=aI3W
-----END PGP SIGNATURE-----

--nextPart7471088.g6Kd9S5Ef1--