Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1TBq-008zj8-OA for pgsql-docs@arkaria.postgresql.org; Wed, 24 Sep 2025 17:22:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v1TBp-00EqRM-Fr for pgsql-docs@arkaria.postgresql.org; Wed, 24 Sep 2025 17:22:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1TBo-00EqQj-W6 for pgsql-docs@lists.postgresql.org; Wed, 24 Sep 2025 17:22:01 +0000 Received: from mail-pf1-x42b.google.com ([2607:f8b0:4864:20::42b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1TBm-002Djd-24 for pgsql-docs@lists.postgresql.org; Wed, 24 Sep 2025 17:22:00 +0000 Received: by mail-pf1-x42b.google.com with SMTP id d2e1a72fcca58-76e6cbb991aso85933b3a.1 for ; Wed, 24 Sep 2025 10:21:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=equatoria-us.20230601.gappssmtp.com; s=20230601; t=1758734518; x=1759339318; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=SuK+h3DPxH7hcV2BYiW50Kx7l0HIuX7kSjC/bkAWWB4=; b=sVY/zYA6VxYnllSkfU0QVzwRTYiMnp6n34fALkdrzLpedyC/rWJiyXst5t60zzK0dr Xxt9r76TjwYYaMmMS8o0w4vTaOsannmPn5PRMQah+1utSj0cBP9jaR2kz3SOUDgVIzke wiOZmz2AiP4ANtMwe/Jrz98wIn0p9bT/P/IMEVmSeYmUR4zzpCm3QWiLrHr/+jWFRGr1 1mrI0KqnMDoehIW99sla1heQktwhAk2kAuA0OuiDf6BLCD0N0wxv6pahkDLa8QVfDxZz s/2B93QX5HoU88W8XmHVs54kSMC79GzTSFx89HAeuMKcOUNuDgAAVXXZwHaiMYUgcQAb TUHw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758734518; x=1759339318; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=SuK+h3DPxH7hcV2BYiW50Kx7l0HIuX7kSjC/bkAWWB4=; b=BvJ5v+zWCQodZeUUsngYEaRCjPaAtxIIyiP8SsrFMjlzT0feUa8r1sm9h+CfKzOVQd 5enWwI2wtXJUpshaPaN4eM0WivBpCqZ8chHz6zWyuPJ4W302fbgIxumWqpcmZkFXfZFv GM4MNCyisHTq3ZXH/O6uzF77WVODe3s0cksHpbE6WMsDVj2KH0QpyKzc5d3FNJH1bTBo uY63klSfzl9z38T3+YvY9x+kqticr9xB1Yo/zQq3O8TUmQnQembCqpRblO2F3fhBAVz6 x7GryPBhQKNUJqL/GjRBibwC5saM4kR45KTusSQ7hBRXHscX2Ra7d4s/4HPSt3WsUCis hH9Q== X-Gm-Message-State: AOJu0YwUhCEc6XVi+wUa82bsd1gSvd7fH/rcwiqgHXaOWR3KOy/oAp8p R4uLQbtaMW97Fccplo+/Pf0/MtDAjGgHIVKF5meeiRuPgKzojNJNW6vPUQzfc8lfoUuTMmJGrrJ luWy5ppwYaPRi6q44p0Ngb2lHD9P62Pt6KV4hgeCC4LYCvqh5cO0M X-Gm-Gg: ASbGncsSQP3iQi4xFrj/rUN6JZt6b7Nr6j2xrTGmui9LniKof1bqz8iFkGiSPL7JAKl YQw2PoxVIItuPTHmgnRRvYq88vYX/HBpz7xPkx3au9FFlLARU+PZJpqhISft5RZfnHid6XBIbRS MYgofPIZX2JL+mMoLducyiCw/pKSnT6zQpyEwCprqY+W2wiCotI5sUa7ix+qsw4a6XU3bhdd0vO yBmFDv8uWMWkYKoOYEJh0CEBArZ9ye2R8RCjDQPESDlI046jBiScg1Agl1ari/2lhdhO1vEHhGT 6JPgLirsHTQwQe+d0A== X-Google-Smtp-Source: AGHT+IF+MpsYFKf8ccErgIQFWXrAqUSogo6IkeitiWOynv3PjF+JJ2mc8GoJk5r/icjGSVh6WeBfiyN+sknCypu2Cmw= X-Received: by 2002:a17:90b:4a47:b0:32e:8931:b59c with SMTP id 98e67ed59e1d1-3342a30c8b4mr543362a91.27.1758734517935; Wed, 24 Sep 2025 10:21:57 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Kirk Parker Date: Wed, 24 Sep 2025 10:21:45 -0700 X-Gm-Features: AS18NWAli9b2xcDfTGVFse63yHU2j2pjx41uLMpOGQq_WY74XY7_pHEluzITe20 Message-ID: Subject: Re: DDL Partitionion Inheritance -- improved trigger function To: David Rowley Cc: pgsql-docs@lists.postgresql.org Content-Type: multipart/mixed; boundary="000000000000326a11063f8f4a6c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000326a11063f8f4a6c Content-Type: multipart/alternative; boundary="000000000000326a11063f8f4a6a" --000000000000326a11063f8f4a6a Content-Type: text/plain; charset="UTF-8" On Tue, Sep 23, 2025, 16:30 David Rowley wrote: > On Wed, 24 Sept 2025 at 04:25, Kirk Parker wrote: > > I'm a big fan of maintenance-free functions. What would you think about > adding the following as an alternative trigger function, or as a > replacement for the current function, to > > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-EXAMPLE > , item #5? > > > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > > RETURNS TRIGGER AS $$ > > BEGIN > > EXECUTE format('INSERT INTO measurement_%s VALUES (NEW.*)', to_char( > NEW.logdate, 'YYYYMM')); > > RETURN NULL; > > END; > > $$ > > LANGUAGE plpgsql; > > I've somewhat mixed feelings about that. While I do agree that it > might be a good way to code things to help prevent a DBA from a > midnight callout, I'm just not sure I'm that onboard with adding the > example. About 10 years ago, I'd likely just have agreed, but since > then we've got declarative partitioning and the legitimate use cases > for using inheritance partitioning over the newer method are very > limited. Today when I look at that page in the documents, I wonder how > we could write less about inheritance partitioning or if we could move > the inheritance section out into another page rather than having it > mixed up with the declarative partitioning sections, perhaps headed up > with a note to redirect people to the declarative partitioning > section. I fear adding your proposed example might increase the > chances of someone landing on that section if they're skimming the > page. > > Overall, I'm about -0.01 on your idea. I might be in favour of it if > the inheritance section had a dedicated page. > > David > I get what you're saying. My email sat in my drafts folder for a couple days while I was debating whether to send it or not, for the exact reason that inheritance-based partitioning is, with a few exceptions, a legacy concept. One way to "write less about inheritance partitioning", though, would be to present the suggested new function as the only example of the trigger function. That would shorten the section by replacing the two current functions along with some of the surrounding explanatory verbiage. Possible patch attached; this is my first ever submission so I hope I didn't miss anything. --000000000000326a11063f8f4a6a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue= , Sep 23, 2025, 16:30 David Rowley <dgrowleyml@gmail.com> wrote:
On W= ed, 24 Sept 2025 at 04:25, Kirk Parker <khp@equatoria.us> wrote: > I'm a big fan of maintenance-free functions.=C2=A0 What would you = think about adding the following as an alternative trigger function, or as = a replacement for the current function, to
> https://www.postgresql.org/docs/current/ddl-partitioning.html= #DDL-PARTITIONING-INHERITANCE-EXAMPLE , item #5?
>
> CREATE OR REPLACE FUNCTION measurement_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>=C2=A0 =C2=A0 =C2=A0EXECUTE format('INSERT INTO measurement_%s VALU= ES (NEW.*)', to_char( NEW.logdate, 'YYYYMM'));
>=C2=A0 =C2=A0 =C2=A0RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;

I've somewhat mixed feelings about that. While I do agree that it
might be a good way to code things to help prevent a DBA from a
midnight callout, I'm just not sure I'm that onboard with adding th= e
example. About 10 years ago, I'd likely just have agreed, but since
then we've got declarative partitioning and the legitimate use cases for using inheritance partitioning over the newer method are very
limited. Today when I look at that page in the documents, I wonder how
we could write less about inheritance partitioning or if we could move
the inheritance section out into another page rather than having it
mixed up with the declarative partitioning sections, perhaps headed up
with a note to redirect people to the declarative partitioning
section. I fear adding your proposed example might increase the
chances of someone landing on that section if they're skimming the
page.

Overall, I'm about -0.01 on your idea. I might be in favour of it if the inheritance section had a dedicated page.

David

I get what you'r= e saying. My email sat in my drafts folder for a couple days while I was de= bating whether to send it or not, for the exact reason that inheritance-bas= ed partitioning is, with a few exceptions, a legacy concept.=C2=A0

One way to "write less abou= t inheritance partitioning", though, would be to present the suggested= new function as the only example of the trigger function. That would short= en the section by replacing the two current functions along with some of th= e surrounding explanatory verbiage.

P= ossible patch attached; this is my first ever submission=C2=A0so I hope I d= idn't miss anything.=C2=A0
--000000000000326a11063f8f4a6a-- --000000000000326a11063f8f4a6c Content-Type: application/x-patch; name="0001-DDL-simpler-inheritance-partition-trigger.patch" Content-Disposition: attachment; filename="0001-DDL-simpler-inheritance-partition-trigger.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_mfy90nfn0 KiB1bmlmaWVkIGRpZmYgb3V0cHV0LCBBU0NJSSB0ZXh0DQpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9z Z21sL2RkbC5zZ21sIGIvZG9jL3NyYy9zZ21sL2RkbC5zZ21sDQppbmRleCA2NWJjMDcwZDJlNS4u NmVkNzcyYzc5NmMgMTAwNjQ0DQotLS0gYS9kb2Mvc3JjL3NnbWwvZGRsLnNnbWwNCisrKyBiL2Rv Yy9zcmMvc2dtbC9kZGwuc2dtbA0KQEAgLTQ3MjYsMTUgKzQ3MjYsMTQgQEAgQ1JFQVRFIElOREVY IG1lYXN1cmVtZW50X3kyMDA4bTAxX2xvZ2RhdGUgT04gbWVhc3VyZW1lbnRfeTIwMDhtMDEgKGxv Z2RhdGUpOw0KICAgICAgICAgIFdlIHdhbnQgb3VyIGFwcGxpY2F0aW9uIHRvIGJlIGFibGUgdG8g c2F5IDxsaXRlcmFsPklOU0VSVCBJTlRPDQogICAgICAgICAgbWVhc3VyZW1lbnQgLi4uPC9saXRl cmFsPiBhbmQgaGF2ZSB0aGUgZGF0YSBiZSByZWRpcmVjdGVkIGludG8gdGhlDQogICAgICAgICAg YXBwcm9wcmlhdGUgY2hpbGQgdGFibGUuICBXZSBjYW4gYXJyYW5nZSB0aGF0IGJ5IGF0dGFjaGlu Zw0KLSAgICAgICAgIGEgc3VpdGFibGUgdHJpZ2dlciBmdW5jdGlvbiB0byB0aGUgcm9vdCB0YWJs ZS4NCi0gICAgICAgICBJZiBkYXRhIHdpbGwgYmUgYWRkZWQgb25seSB0byB0aGUgbGF0ZXN0IGNo aWxkLCB3ZSBjYW4NCi0gICAgICAgICB1c2UgYSB2ZXJ5IHNpbXBsZSB0cmlnZ2VyIGZ1bmN0aW9u Og0KKyAgICAgICAgIGEgc3VpdGFibGUgdHJpZ2dlciBmdW5jdGlvbiB0byB0aGUgcm9vdCB0YWJs ZTsgaGVyZSBpcyBhIHNpbXBsZQ0KKyAgICAgICAgIHRyaWdnZXIgZnVuY3Rpb24gdGhhdCBkZXJp dmVzIHRoZSBwYXJ0aXRpb24gdGFibGUgbmFtZSBmcm9tIHRoZSBsb2dkYXRlOg0KIA0KIDxwcm9n cmFtbGlzdGluZz4NCiBDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBtZWFzdXJlbWVudF9pbnNl cnRfdHJpZ2dlcigpDQogUkVUVVJOUyBUUklHR0VSIEFTICQkDQogQkVHSU4NCi0gICAgSU5TRVJU IElOVE8gbWVhc3VyZW1lbnRfeTIwMDhtMDEgVkFMVUVTIChORVcuKik7DQorICAgIEVYRUNVVEUg Zm9ybWF0KCdJTlNFUlQgSU5UTyBtZWFzdXJlbWVudF8lcyBWQUxVRVMgKE5FVy4qKScsIHRvX2No YXIoIE5FVy5sb2dkYXRlLCAnInkiWVlZWSJtIk1NJykpOw0KICAgICBSRVRVUk4gTlVMTDsNCiBF TkQ7DQogJCQNCkBAIC00NzUyLDU5ICs0NzUxLDcgQEAgQ1JFQVRFIFRSSUdHRVIgaW5zZXJ0X21l YXN1cmVtZW50X3RyaWdnZXINCiAgICAgRk9SIEVBQ0ggUk9XIEVYRUNVVEUgRlVOQ1RJT04gbWVh c3VyZW1lbnRfaW5zZXJ0X3RyaWdnZXIoKTsNCiA8L3Byb2dyYW1saXN0aW5nPg0KIA0KLSAgICAg ICAgIFdlIG11c3QgcmVkZWZpbmUgdGhlIHRyaWdnZXIgZnVuY3Rpb24gZWFjaCBtb250aCBzbyB0 aGF0IGl0IGFsd2F5cw0KLSAgICAgICAgIGluc2VydHMgaW50byB0aGUgY3VycmVudCBjaGlsZCB0 YWJsZS4gIFRoZSB0cmlnZ2VyIGRlZmluaXRpb24gZG9lcw0KLSAgICAgICAgIG5vdCBuZWVkIHRv IGJlIHVwZGF0ZWQsIGhvd2V2ZXIuDQogICAgICAgICA8L3BhcmE+DQotDQotICAgICAgICA8cGFy YT4NCi0gICAgICAgICBXZSBtaWdodCB3YW50IHRvIGluc2VydCBkYXRhIGFuZCBoYXZlIHRoZSBz ZXJ2ZXIgYXV0b21hdGljYWxseQ0KLSAgICAgICAgIGxvY2F0ZSB0aGUgY2hpbGQgdGFibGUgaW50 byB3aGljaCB0aGUgcm93IHNob3VsZCBiZSBhZGRlZC4gV2UNCi0gICAgICAgICBjb3VsZCBkbyB0 aGlzIHdpdGggYSBtb3JlIGNvbXBsZXggdHJpZ2dlciBmdW5jdGlvbiwgZm9yIGV4YW1wbGU6DQot DQotPHByb2dyYW1saXN0aW5nPg0KLUNSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIG1lYXN1cmVt ZW50X2luc2VydF90cmlnZ2VyKCkNCi1SRVRVUk5TIFRSSUdHRVIgQVMgJCQNCi1CRUdJTg0KLSAg ICBJRiAoIE5FVy5sb2dkYXRlICZndDs9IERBVEUgJzIwMDYtMDItMDEnIEFORA0KLSAgICAgICAg IE5FVy5sb2dkYXRlICZsdDsgREFURSAnMjAwNi0wMy0wMScgKSBUSEVODQotICAgICAgICBJTlNF UlQgSU5UTyBtZWFzdXJlbWVudF95MjAwNm0wMiBWQUxVRVMgKE5FVy4qKTsNCi0gICAgRUxTSUYg KCBORVcubG9nZGF0ZSAmZ3Q7PSBEQVRFICcyMDA2LTAzLTAxJyBBTkQNCi0gICAgICAgICAgICBO RVcubG9nZGF0ZSAmbHQ7IERBVEUgJzIwMDYtMDQtMDEnICkgVEhFTg0KLSAgICAgICAgSU5TRVJU IElOVE8gbWVhc3VyZW1lbnRfeTIwMDZtMDMgVkFMVUVTIChORVcuKik7DQotICAgIC4uLg0KLSAg ICBFTFNJRiAoIE5FVy5sb2dkYXRlICZndDs9IERBVEUgJzIwMDgtMDEtMDEnIEFORA0KLSAgICAg ICAgICAgIE5FVy5sb2dkYXRlICZsdDsgREFURSAnMjAwOC0wMi0wMScgKSBUSEVODQotICAgICAg ICBJTlNFUlQgSU5UTyBtZWFzdXJlbWVudF95MjAwOG0wMSBWQUxVRVMgKE5FVy4qKTsNCi0gICAg RUxTRQ0KLSAgICAgICAgUkFJU0UgRVhDRVBUSU9OICdEYXRlIG91dCBvZiByYW5nZS4gIEZpeCB0 aGUgbWVhc3VyZW1lbnRfaW5zZXJ0X3RyaWdnZXIoKSBmdW5jdGlvbiEnOw0KLSAgICBFTkQgSUY7 DQotICAgIFJFVFVSTiBOVUxMOw0KLUVORDsNCi0kJA0KLUxBTkdVQUdFIHBscGdzcWw7DQotPC9w cm9ncmFtbGlzdGluZz4NCi0NCi0gICAgICAgICBUaGUgdHJpZ2dlciBkZWZpbml0aW9uIGlzIHRo ZSBzYW1lIGFzIGJlZm9yZS4NCi0gICAgICAgICBOb3RlIHRoYXQgZWFjaCA8bGl0ZXJhbD5JRjwv bGl0ZXJhbD4gdGVzdCBtdXN0IGV4YWN0bHkgbWF0Y2ggdGhlDQotICAgICAgICAgPGxpdGVyYWw+ Q0hFQ0s8L2xpdGVyYWw+IGNvbnN0cmFpbnQgZm9yIGl0cyBjaGlsZCB0YWJsZS4NCi0gICAgICAg IDwvcGFyYT4NCi0NCi0gICAgICAgIDxwYXJhPg0KLSAgICAgICAgIFdoaWxlIHRoaXMgZnVuY3Rp b24gaXMgbW9yZSBjb21wbGV4IHRoYW4gdGhlIHNpbmdsZS1tb250aCBjYXNlLA0KLSAgICAgICAg IGl0IGRvZXNuJ3QgbmVlZCB0byBiZSB1cGRhdGVkIGFzIG9mdGVuLCBzaW5jZSBicmFuY2hlcyBj YW4gYmUNCi0gICAgICAgICBhZGRlZCBpbiBhZHZhbmNlIG9mIGJlaW5nIG5lZWRlZC4NCi0gICAg ICAgIDwvcGFyYT4NCi0NCi0gICAgICAgIDxub3RlPg0KLSAgICAgICAgIDxwYXJhPg0KLSAgICAg ICAgICBJbiBwcmFjdGljZSwgaXQgbWlnaHQgYmUgYmVzdCB0byBjaGVjayB0aGUgbmV3ZXN0IGNo aWxkIGZpcnN0LA0KLSAgICAgICAgICBpZiBtb3N0IGluc2VydHMgZ28gaW50byB0aGF0IGNoaWxk LiAgRm9yIHNpbXBsaWNpdHksIHdlIGhhdmUNCi0gICAgICAgICAgc2hvd24gdGhlIHRyaWdnZXIn cyB0ZXN0cyBpbiB0aGUgc2FtZSBvcmRlciBhcyBpbiBvdGhlciBwYXJ0cw0KLSAgICAgICAgICBv ZiB0aGlzIGV4YW1wbGUuDQotICAgICAgICAgPC9wYXJhPg0KLSAgICAgICAgPC9ub3RlPg0KLQ0K ICAgICAgICAgPHBhcmE+DQogICAgICAgICAgQSBkaWZmZXJlbnQgYXBwcm9hY2ggdG8gcmVkaXJl Y3RpbmcgaW5zZXJ0cyBpbnRvIHRoZSBhcHByb3ByaWF0ZQ0KICAgICAgICAgIGNoaWxkIHRhYmxl IGlzIHRvIHNldCB1cCBydWxlcywgaW5zdGVhZCBvZiBhIHRyaWdnZXIsIG9uIHRoZQ0K --000000000000326a11063f8f4a6c--