Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M256586=pgsql-hackers-arkari@postgresql.org>)
 id 1XOuXr-0008UG-IY
 for pgsql-hackers@arkaria.postgresql.org; Tue, 02 Sep 2014 20:19:15 +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+M256586=pgsql-hackers-arkari@postgresql.org>)
 id 1XOuXq-0003ii-7a
 for pgsql-hackers@arkaria.postgresql.org; Tue, 02 Sep 2014 20:19:14 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <kleptog@svana.org>) id 1XOuXm-0003bG-Au
 for pgsql-hackers@postgresql.org; Tue, 02 Sep 2014 20:19:10 +0000
Received: from bilbys.org ([2400:c400::113:212:99:188] helo=svana.org)
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <kleptog@svana.org>) id 1XOuXi-0008Mp-Oc
 for pgsql-hackers@postgresql.org; Tue, 02 Sep 2014 20:19:08 +0000
Received: from kleptog by svana.org with local (Exim 4.72)
 (envelope-from <kleptog@svana.org>)
 id 1XOuXT-0007jV-52; Wed, 03 Sep 2014 06:18:52 +1000
Date: Tue, 2 Sep 2014 22:18:51 +0200
From: Martijn van Oosterhout <kleptog@svana.org>
To: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@2ndquadrant.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Message-ID: <20140902201850.GA29409@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <20140829155607.GF7705@eldon.alvh.no-ip.org>
 <20746.1409330150@sss.pgh.pa.us> <20140831204528.GA11069@svana.org>
 <20140902134417.GG19338@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature"; boundary="J2SCkAp4GZ/dPZZf"
Content-Disposition: inline
In-Reply-To: <20140902134417.GG19338@momjian.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


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

On Tue, Sep 02, 2014 at 09:44:17AM -0400, Bruce Momjian wrote:
> On Sun, Aug 31, 2014 at 10:45:29PM +0200, Martijn van Oosterhout wrote:
> > 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".
>=20
> I am unclear why having information per-partition rather than on the
> parent table helps with online reparitioning.

An example:

We have three partitions, one for X<0 (A), one for 0<=3DX<5 (B) and one
for X>=3D5 (C).  These are in three different tables.

Now we give the command to merge the last two partitions B&C. You now
have the choice to lock the table while you move all the tuples from C
to B.

Or you can make some adjustments such that new tuples that would have gone
to C now go to B. And if there is a query for X=3D10 that you look in
*both* B & C. Then the existing tuples can be moved from C to B at any
time without blocking any other operations.

Is this clearer? If you up front decide that which partition to query
will be determined by a function that can only return one table, then
the above becomes impossible.

> Robert's idea of using normal table inheritance means we can access/move
> the data independently of the partitioning system.  My guess is that we
> will need to do repartitioning with some tool, rather than as part of
> normal database operation.

Doing it as some tool seems like a hack to me. And since the idea was (I
thought) that partitions would not be directly accessable from SQL, it
has to be in the database itself.

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

--J2SCkAp4GZ/dPZZf
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)

iQIVAwUBVAYmKkvt++dL5i1EAQjxdRAAxwkWrBk9mP5lEvI9zZoR5HT6bPq7PXgU
u6pTrv0crejoox0kipnojuiD5SdzqCAG6p5LkmQrJKENGv6d9RxDjWkGeprHJ/aK
F5f0rqaOT24iZWwfzD6wblPV1vXmNIaBrNIhV6LGJtWbhQvwyaSFKeg+DPX3aDk0
5HnrftZIW4VtPb4O//IZG4T1uKfsHYt12H/fWquwvi4OFkgpCMt08CecjYX1Ngkg
8ZNslR03HUmDzBScW21nHNr5ld89O7G7SSWRsVvYhobGYo92OUZ8mm7QDzA08Soy
NsNyg5V0wYJk4wJrpBPDOyZduAtXck43KYrLRCivX6xV7MyiScK6PF39Vdra55Ju
X+2Yd58lzG09234kcEc8z1tmuGvbGhBe+k+IjY83/Nq1pp9ZBvbNQ7Z7kwv3E9w8
9dG7K/awUWqodPXx+nJhQwPQBzZ2b/RBXM0KJ0UvidVRslG4YS2jo3v7iS3N3Yis
i9bh9Q35LDxWD1zaEkyQcO9E2dypH5aW15bQo9j68CqSFV+RRsuKDBtaa9kxIr0V
zZ0JD03aUlCBCxXekw3Fuy7WMpTmN0pFXK8ITbTG/SZI0i6QweN65jou/hUhAS5S
yUcELPNm8/HHg2YoiwJS3QPRcRBCbrYWjrRR/ggXnTmawyNDkad9E2inije/5kqt
3q7VqhW1C1o=
=YVgc
-----END PGP SIGNATURE-----

--J2SCkAp4GZ/dPZZf--