Wrong insert before trigger examples

On Wed, Feb 5, 2020 at 09:36:49AM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/ddl-partitioning.html
Description:

Hi,

I just noticed that the example in 5.11.3.1. Point 5 contains an "before
insert trigger" which will not work anymore (I think since Postgres 12).
This trigger is not needed anymore and causes an error message.

Sorry for the delay in replying. I was able to successfully create the
trigger:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);

CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

There are actually two definitions of 'measurement' in the docs above,
and you might have used the partitioned one:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

I have developed the attached patch to clarify which definition to use.
I am not sure if more extensive changes are needed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Patch applied back through PG 10.

---------------------------------------------------------------------------

On Fri, Mar 13, 2020 at 06:56:37PM -0400, Bruce Momjian wrote:

On Wed, Feb 5, 2020 at 09:36:49AM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/ddl-partitioning.html
Description:

Hi,

I just noticed that the example in 5.11.3.1. Point 5 contains an "before
insert trigger" which will not work anymore (I think since Postgres 12).
This trigger is not needed anymore and causes an error message.

Sorry for the delay in replying. I was able to successfully create the
trigger:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);

CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

There are actually two definitions of 'measurement' in the docs above,
and you might have used the partitioned one:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

I have developed the attached patch to clarify which definition to use.
I am not sure if more extensive changes are needed.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8d3a0d1c22..d2588876c0 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4103,8 +4103,8 @@ ALTER INDEX measurement_city_id_logdate_key
<title>Example</title>

<para>
-      We use the same <structname>measurement</structname> table we used
-      above.  To implement partitioning using inheritance, use
+      We use the non-partitioned <structname>measurement</structname>
+      table above.  To implement partitioning using inheritance, use
the following steps:

<orderedlist spacing="compact">

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +