pg.ddx.io pgsql-docs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Wrong insert before trigger examples 3+ messages / 2 participants [nested] [flat]
* Wrong insert before trigger examples @ 2020-02-05 09:36 PG Doc comments form <noreply@postgresql.org> 0 siblings, 1 reply; 3+ messages in thread From: PG Doc comments form @ 2020-02-05 09:36 UTC (permalink / raw) To: pgsql-docs@lists.postgresql.org; +Cc: mib@nic.at 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. Best, Michael ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong insert before trigger examples @ 2020-03-13 22:56 Bruce Momjian <bruce@momjian.us> parent: PG Doc comments form <noreply@postgresql.org> 0 siblings, 1 reply; 3+ messages in thread From: Bruce Momjian @ 2020-03-13 22:56 UTC (permalink / raw) To: mib@nic.at; pgsql-docs@lists.postgresql.org 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 + Attachments: [text/x-diff] partition.diff (608B, ../../20200313225637.GB10187@momjian.us/2-partition.diff) download | inline diff: 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"> ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong insert before trigger examples @ 2020-03-31 21:08 Bruce Momjian <bruce@momjian.us> parent: Bruce Momjian <bruce@momjian.us> 0 siblings, 0 replies; 3+ messages in thread From: Bruce Momjian @ 2020-03-31 21:08 UTC (permalink / raw) To: mib@nic.at; pgsql-docs@lists.postgresql.org 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 + ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-03-31 21:08 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-02-05 09:36 Wrong insert before trigger examples PG Doc comments form <noreply@postgresql.org> 2020-03-13 22:56 ` Bruce Momjian <bruce@momjian.us> 2020-03-31 21:08 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox