Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l1sV1-0007Fs-O1 for pgsql-admin@arkaria.postgresql.org; Tue, 19 Jan 2021 15:00:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l1sUz-0000Dh-DX for pgsql-admin@arkaria.postgresql.org; Tue, 19 Jan 2021 15:00:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l1sUy-0000Ay-Bt for pgsql-admin@lists.postgresql.org; Tue, 19 Jan 2021 15:00:49 +0000 Received: from mail-am6eur05olkn2069.outbound.protection.outlook.com ([40.92.91.69] helo=EUR05-AM6-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l1sUt-0006vt-DH for pgsql-admin@postgresql.org; Tue, 19 Jan 2021 15:00:47 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=N+T5fQ2EpMEI7ulTuRhLmUq8aaZkmzfT2j1fTvbm3uIo8s1TT9RApyKLsBFWkEqtcifg/B+szE6LfaAgN7I9t3DRPGV/RXCfMEyGqBA50fI4pmfPOncrcqxDqzVIpGY+zrjTM0Q9UIJ2nzm4TcTjhVgi2/rVWYzKe6Hcmrl4owQZBgNhAtjTXS9Gc4OeqyOV2WOli1sFJpr71LrAIWlvEG0wCvEoHtQqbNkuTQygVuBl9y5KAjr7KBPG8q8kj5GwlTcDr9EfbKDsLV2bdAB9SaZBKvzNMANtbuarMY/PUXHwRMhMjX+6/tO01zLg/uHv7pDSkp7MVQsombjlFMXvHQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=kchpEPrgU0ozGABc+sTNIGDb45ZNkSzgSbaiB0fUGSE=; b=VJClpy+z6c8OUiwbo+WbdhxsEvH2JokXpVRgHJ2FNEfk3sfsTVFiMnGdJ0h2BXpvq+DOkOxxqcu3xqo9Lzt0KGuQrJhTaVdNTUgatMsGAcJqCoA2rJ6cS1QBgTFuQK4wbO/Z4n4Z7Fh5F1TYCFLauHe4rGCJgFGJstVVMqgVf55mnhAOOqaFvjcFYoEpPvCp4xDcCMN1Gs6qcWgQ15ZiskdjDxAVG24RUL9K+EKXxEmrDPIi5BKQkqza6teBbJ5zdB4Zbxs7l3S8MCAhiTg0lyUarYLZIznt4fP7xDUTt+61KYJ6gS/knfrXOUiHa/HaL8F3icBtTpBQcZHWchr0RQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=kchpEPrgU0ozGABc+sTNIGDb45ZNkSzgSbaiB0fUGSE=; b=WtKOLn7vnnrdY6HRxxVV4gW1ZSfS4+j/X7EDJ4aIS2DQOAvS7RjQ0e7xrgQhSvsvTET044/mYNnpdy1pyZUtFayTaMOm6jFmbmVpe7u79Vf/ZBI0SaiUZhI8C2hvaw4bdV3tnlMO/tfAlhMwH3FJEw00fRK4MJkN79mghMkaTNdmxhUXd21Iq1zRZBdc6kNBT8+sl5EqWzEDkco2pMt4aXMncJty5JJKZtacI8clDM7k4v/6MlCpzHulJ3mLnLYoXDvIXftt96OVPOZ/pZho3UGBJHQDnA9JXzwmJ6+T/B2+3p0Jgj+zJj8CFrEVqGW/qMT+GbT8o4B8DFsjI/TkVw== Received: from AM6EUR05FT022.eop-eur05.prod.protection.outlook.com (2a01:111:e400:fc11::48) by AM6EUR05HT193.eop-eur05.prod.protection.outlook.com (2a01:111:e400:fc11::354) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3763.11; Tue, 19 Jan 2021 15:00:41 +0000 Received: from DBAP191MB1193.EURP191.PROD.OUTLOOK.COM (2a01:111:e400:fc11::47) by AM6EUR05FT022.mail.protection.outlook.com (2a01:111:e400:fc11::168) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3784.11 via Frontend Transport; Tue, 19 Jan 2021 15:00:41 +0000 Received: from DBAP191MB1193.EURP191.PROD.OUTLOOK.COM ([fe80::94dc:8222:9099:3db5]) by DBAP191MB1193.EURP191.PROD.OUTLOOK.COM ([fe80::94dc:8222:9099:3db5%6]) with mapi id 15.20.3763.014; Tue, 19 Jan 2021 15:00:41 +0000 From: Graham Everton To: "depesz@depesz.com" , Keith Fiske CC: Anjul Tyagi , pgsql-admin Subject: Re: Table Partitioning Thread-Topic: Table Partitioning Thread-Index: AQHW7kznDm7WYLpdbE6FS4i8fx/Jzaou088AgAAFJgCAAAMJAIAAFn4AgAAYmdo= Date: Tue, 19 Jan 2021 15:00:41 +0000 Message-ID: References: <20210119114246.GA13372@depesz.com> <20210119121204.GB13372@depesz.com>, In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:07253AB27CF16E6D6EC101FC095FA04384CDCF290D1A2223EA6C427B79914E37;UpperCasedChecksum:D0215DC69025FED39F2AC165CFE176A22B1AF8170D516BC74D02879184501C55;SizeAsReceived:7128;Count:45 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [jVO+OKNVJdfiIoKB1tSy8rA7rdchMy9D] x-ms-publictraffictype: Email x-incomingheadercount: 45 x-eopattributedmessage: 0 x-ms-office365-filtering-correlation-id: beebcb35-00ef-4908-fc18-08d8bc8afd53 x-ms-traffictypediagnostic: AM6EUR05HT193: x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: XTILIHmfhNkAd29pKXStJLCSRwLqZ0HCUXb7ANceUNxKjvq+MNpEkLilc5D4XZF29V5B+uU8TyYAT3pEr3/oMtdO+DAiRKkIan3mEjgrVmjeTlodOoxaf+z8nZPXDuh2DOgk/OsKY19h0X6MveHZrdLhD/tXPGGJVYpWsaxRT1NJS4/cEWuO9MzPAR46kP10bc1HL9ysjOWtbrRJvs5x4WoRyr7VVXrEjnVX9hO+aJOSqsYVS9ouBoREXpPkdXGGme1759tV4VcxcUYlc5Z97MR5jJzy9BVhqe9gF4/A3IE= x-ms-exchange-antispam-messagedata: NpAl2KmDjZfnZrtgKKkjGrou4v2e6vCOCa9zRo0se0IHinVuh9+pCt5Sah6mzKe11yW4PgiV72jCYhFqa96MVvbKGwt7kzPafP7Kb6MIJHC1ur0qKMb3nP4mZ20xs8Fhk1kh5hDYX/LuScH/EviuPQ== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_DBAP191MB119391D291282C47F3260CBADBA30DBAP191MB1193EURP_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-AuthSource: AM6EUR05FT022.eop-eur05.prod.protection.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: beebcb35-00ef-4908-fc18-08d8bc8afd53 X-MS-Exchange-CrossTenant-originalarrivaltime: 19 Jan 2021 15:00:41.3657 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM6EUR05HT193 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_DBAP191MB119391D291282C47F3260CBADBA30DBAP191MB1193EURP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Op 0 Get Outlook for Android ________________________________ From: Keith Fiske Sent: Tuesday, January 19, 2021 1:32:34 PM To: depesz@depesz.com Cc: Anjul Tyagi ; pgsql-admin Subject: Re: Table Partitioning On Tue, Jan 19, 2021 at 7:12 AM hubert depesz lubaczewski > wrote: On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote: > Can we handle that in DB side with trigger? I don't think so. At least not easily. You could make a function to do so, though. Best regards, depesz As depesz said, a trigger is possible, but very tricky. I was never able to= find a good trigger-based, solution to these kinds of updates for pg_partm= an myself. Thankfully support for updates that move data between child tabl= es was added for native partitioning in PG11. I would highly suggest planni= ng an upgrade to your major version of PG. Skip 11 and go right to 12 or 13= . An upgrade would be arguably easier than trying to code this yourself. An= d the added features to native partitioning in 11 and above will make manag= ing them far better in the long run. -- Keith Fiske Senior Database Engineer Crunchy Data - http://crunchydata.com --_000_DBAP191MB119391D291282C47F3260CBADBA30DBAP191MB1193EURP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Op 0

From: Keith Fiske <keith= .fiske@crunchydata.com>
Sent: Tuesday, January 19, 2021 1:32:34 PM
To: depesz@depesz.com <depesz@depesz.com>
Cc: Anjul Tyagi <anjul@ibosstech-us.com>; pgsql-admin <pgsq= l-admin@postgresql.org>
Subject: Re: Table Partitioning
 


On Tue, Jan 19, 2021 at 7:12 AM hub= ert depesz lubaczewski <depesz@depesz.com> wrote:
On Tue, Jan 19, 2021 at 12:01:12PM +0000, Anjul Tyagi wrote:
> Can we handle that in DB side with trigger?

I don't think so. At least not easily.

You could make a function to do so, though.

Best regards,

depesz




As depesz said, a trigger is possible, but very tricky. I was never ab= le to find a good trigger-based, solution to these kinds of updates for pg_= partman myself. Thankfully support for updates that move data between child= tables was added for native partitioning in PG11. I would highly suggest planning an upgrade to your major version = of PG. Skip 11 and go right to 12 or 13. An upgrade would be arguably easie= r than trying to code this yourself. And the added features to native parti= tioning in 11 and above will make managing them far better in the long run.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
--_000_DBAP191MB119391D291282C47F3260CBADBA30DBAP191MB1193EURP_--