On Tue, Sep 23, 2025, 16:30 David Rowley <[email protected]> wrote:
> On Wed, 24 Sept 2025 at 04:25, Kirk Parker <[email protected]> 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.
* unified diff output, ASCII text
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 65bc070d2e5..6ed772c796c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4726,15 +4726,14 @@ CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
We want our application to be able to say <literal>INSERT INTO
measurement ...</literal> and have the data be redirected into the
appropriate child table. We can arrange that by attaching
- a suitable trigger function to the root table.
- If data will be added only to the latest child, we can
- use a very simple trigger function:
+ a suitable trigger function to the root table; here is a simple
+ trigger function that derives the partition table name from the logdate:
<programlisting>
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
- INSERT INTO measurement_y2008m01 VALUES (NEW.*);
+ EXECUTE format('INSERT INTO measurement_%s VALUES (NEW.*)', to_char( NEW.logdate, '"y"YYYY"m"MM'));
RETURN NULL;
END;
$$
@@ -4752,59 +4751,7 @@ CREATE TRIGGER insert_measurement_trigger
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
</programlisting>
- We must redefine the trigger function each month so that it always
- inserts into the current child table. The trigger definition does
- not need to be updated, however.
</para>
-
- <para>
- We might want to insert data and have the server automatically
- locate the child table into which the row should be added. We
- could do this with a more complex trigger function, for example:
-
-<programlisting>
-CREATE OR REPLACE FUNCTION measurement_insert_trigger()
-RETURNS TRIGGER AS $$
-BEGIN
- IF ( NEW.logdate >= DATE '2006-02-01' AND
- NEW.logdate < DATE '2006-03-01' ) THEN
- INSERT INTO measurement_y2006m02 VALUES (NEW.*);
- ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
- NEW.logdate < DATE '2006-04-01' ) THEN
- INSERT INTO measurement_y2006m03 VALUES (NEW.*);
- ...
- ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
- NEW.logdate < DATE '2008-02-01' ) THEN
- INSERT INTO measurement_y2008m01 VALUES (NEW.*);
- ELSE
- RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
- END IF;
- RETURN NULL;
-END;
-$$
-LANGUAGE plpgsql;
-</programlisting>
-
- The trigger definition is the same as before.
- Note that each <literal>IF</literal> test must exactly match the
- <literal>CHECK</literal> constraint for its child table.
- </para>
-
- <para>
- While this function is more complex than the single-month case,
- it doesn't need to be updated as often, since branches can be
- added in advance of being needed.
- </para>
-
- <note>
- <para>
- In practice, it might be best to check the newest child first,
- if most inserts go into that child. For simplicity, we have
- shown the trigger's tests in the same order as in other parts
- of this example.
- </para>
- </note>
-
<para>
A different approach to redirecting inserts into the appropriate
child table is to set up rules, instead of a trigger, on the
