public inbox for [email protected]
help / color / mirror / Atom feedDDL Partitionion Inheritance -- improved trigger function
3+ messages / 2 participants
[nested] [flat]
* DDL Partitionion Inheritance -- improved trigger function
@ 2025-09-23 16:25 Kirk Parker <[email protected]>
2025-09-23 23:30 ` Re: DDL Partitionion Inheritance -- improved trigger function David Rowley <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Kirk Parker @ 2025-09-23 16:25 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DDL Partitionion Inheritance -- improved trigger function
2025-09-23 16:25 DDL Partitionion Inheritance -- improved trigger function Kirk Parker <[email protected]>
@ 2025-09-23 23:30 ` David Rowley <[email protected]>
2025-09-24 17:21 ` Re: DDL Partitionion Inheritance -- improved trigger function Kirk Parker <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2025-09-23 23:30 UTC (permalink / raw)
To: Kirk Parker <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DDL Partitionion Inheritance -- improved trigger function
2025-09-23 16:25 DDL Partitionion Inheritance -- improved trigger function Kirk Parker <[email protected]>
2025-09-23 23:30 ` Re: DDL Partitionion Inheritance -- improved trigger function David Rowley <[email protected]>
@ 2025-09-24 17:21 ` Kirk Parker <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Kirk Parker @ 2025-09-24 17:21 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: [email protected]
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.
Attachments:
[application/x-patch] 0001-DDL-simpler-inheritance-partition-trigger.patch (3.4K, 3-0001-DDL-simpler-inheritance-partition-trigger.patch)
download | inline diff:
* 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-24 17:21 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-23 16:25 DDL Partitionion Inheritance -- improved trigger function Kirk Parker <[email protected]>
2025-09-23 23:30 ` David Rowley <[email protected]>
2025-09-24 17:21 ` Kirk Parker <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox