DDL Partitionion Inheritance -- improved trigger function
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;
For the modest overhead of an extra call to to_char() and using EXECUTE
rather than a literal INSERT, you get a trigger function that works
forever. Given that the example anticipates one insert per city/day, it
doesn't expect an extremely high rate of inserts where every microsecond
counts.
And yes, bad things happen if the partition table does not exist, but
that's true of the other trigger functions shown here, too.
On Wed, 24 Sept 2025 at 04:25, Kirk Parker <khp@equatoria.us> 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
On Tue, Sep 23, 2025, 16:30 David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 24 Sept 2025 at 04:25, Kirk Parker <khp@equatoria.us> 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, tohttps://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.