Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M256310=pgsql-hackers-arkari@postgresql.org>)
 id 1XOC0c-00056k-Eq
 for pgsql-hackers@arkaria.postgresql.org; Sun, 31 Aug 2014 20:45:58 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M256310=pgsql-hackers-arkari@postgresql.org>)
 id 1XOC0b-0003tF-UF
 for pgsql-hackers@arkaria.postgresql.org; Sun, 31 Aug 2014 20:45:57 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <kleptog@svana.org>) id 1XOC0a-0003t5-5k
 for pgsql-hackers@postgresql.org; Sun, 31 Aug 2014 20:45:56 +0000
Received: from bilbys.org ([2400:c400::113:212:99:188] helo=svana.org)
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <kleptog@svana.org>) id 1XOC0O-0000yw-EI
 for pgsql-hackers@postgresql.org; Sun, 31 Aug 2014 20:45:55 +0000
Received: from kleptog by svana.org with local (Exim 4.72)
 (envelope-from <kleptog@svana.org>)
 id 1XOC09-0003EB-A4; Mon, 01 Sep 2014 06:45:32 +1000
Date: Sun, 31 Aug 2014 22:45:29 +0200
From: Martijn van Oosterhout <kleptog@svana.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Message-ID: <20140831204528.GA11069@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <20140829155607.GF7705@eldon.alvh.no-ip.org>
 <20746.1409330150@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature"; boundary="7AUc2qLy4jB3hD7Z"
Content-Disposition: inline
In-Reply-To: <20746.1409330150@sss.pgh.pa.us>
X-PGP-Key-ID: Length=4096; ID=0x4BE62D44
X-PGP-Key-Fingerprint: AF6F C0D6 3B8A AF7D 942B  D2BC 4BED FBE7 4BE6 2D44
User-Agent: Mutt/1.5.20 (2009-06-14)
X-SA-Exim-Connect-IP: <locally generated>
X-SA-Exim-Mail-From: kleptog@svana.org
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on calyx
X-Spam-Level: 
X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00,NO_RELAYS
 autolearn=ham version=3.3.1
Subject: Re: On partitioning
X-SA-Exim-Version: 4.2.1 (built Mon, 22 Mar 2010 06:26:47 +0000)
X-SA-Exim-Scanned: Yes (on svana.org)
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--7AUc2qLy4jB3hD7Z
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Aug 29, 2014 at 12:35:50PM -0400, Tom Lane wrote:
> > Each partition is assigned an Expression that receives a tuple and
> > returns boolean.  This expression returns true if a given tuple belongs
> > into it, false otherwise.
>=20
> -1, in fact minus a lot.  One of the core problems of the current approach
> is that the system, particularly the planner, hasn't got a lot of insight
> into exactly what the partitioning scheme is in a partitioned table built
> on inheritance.  If you allow the partitioning rule to be a black box then
> that doesn't get any better.  I want to see a design wherein the system
> understands *exactly* what the partitioning behavior is.  I'd start with
> supporting range-based partitioning explicitly, and maybe we could add
> other behaviors such as hashing later.
>=20
> In particular, there should never be any question at all that there is
> exactly one partition that a given row belongs to, not more, not less.
> You can't achieve that with a set of independent filter expressions;
> a meta-rule that says "exactly one of them should return true" is an
> untrustworthy band-aid.
>=20
> (This does not preclude us from mapping the tuple through the partitioning
> rule and finding that the corresponding partition doesn't currently exist.
> I think we could view the partitioning rule as a function from tuples to
> partition numbers, and then we look in pg_class to see if such a partition
> exists.)

There is one situation where you need to be more flexible, and that is
if you ever want to support online repartitioning. To do that you have
to distinguish between "I want to insert tuple X, which partition
should it go into" and "I want to know which partitions I need to look
for partition_key=3DY".

For the latter you really have need an expression per partition, or
something equivalent.  If performance is an issue I suppose you could
live with having an "old" and an "new" partition scheme, so you
couldn't have two "live repartitionings" happening simultaneously.

Now, if you want to close the door on online repartitioning forever
then that fine. But being in the position of having to say "yes our
partitioning scheme sucks, but we would have to take the database down
for a week to fix it" is no fun.

Unless logical replication provides a way out maybe??

Have a nice day,
--=20
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

--7AUc2qLy4jB3hD7Z
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

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

iQIVAwUBVAOJaEvt++dL5i1EAQg9MhAAij8Rk15jeR4EDiSz+omDcBUK7Z84Ymtr
FlCpEaGyPMxwovA/IBVmttG+xK+fCb1Y9COeE+DlX4L8RdDms2kFzmbkabX/4b/j
Uxzvvf/cgkUuKSxvhVHreOTIWxO8acX+WqyONvMDshYXRoUMgroOLumjltpT7Vk8
d/jby5DLsilSAWrZ8vdBcCqqdlpbxswh9WL55qtrHn8B6G1J9wA22Tgaf26geWq0
YqeuFdci0gj4uhhZ5gfQ53/+VGepPfgCcO7TQmCO0yYFNG+eXN6DADy7QHx2Xjzg
g5lQN5ryAvsSrrwzt+xuS3gzQK8q1QFiFTfe2pcHGzw2EWS39DKv+Kdi1f9/7yJG
yhgSQq6ulKPwl77T1Y493OEWre0tJf6H5NnXnepDQU87srCcTfAMjBWUzT3MKf2J
uHQ5Ds2//mncgTf2jP74e21ZLxs3ZJQ0nvLaJcgr4yzsLzRljjcma69mK6hd7zv/
Rc4zx1W/78Nkh9jla9KNgnUqa4Dcb9d6804gruQl3JtADFfGFH0rxcY7T0ZXh0f5
9IPIhXlHyVoizovUI4AV28gWqh3waFbL0CPflkDHPwr1bOEGxaaAXB9IrgRLtHbf
OK5b+8gzejFz+r5Y65kMtqiO3y4uLuQ0dyloBAvk+wSeCuQtGMxkcy649OvlojFP
LKYqzvR+oys=
=HaaB
-----END PGP SIGNATURE-----

--7AUc2qLy4jB3hD7Z--