Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNKIz-000264-Iy for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 18:24:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kNKIy-0002ck-CD for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 18:24:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNKIx-0002bz-TR for pgsql-bugs@lists.postgresql.org; Tue, 29 Sep 2020 18:24:48 +0000 Received: from sonic308-9.consmr.mail.ne1.yahoo.com ([66.163.187.32]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kNKIu-0001d8-Ny for pgsql-bugs@lists.postgresql.org; Tue, 29 Sep 2020 18:24:46 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1601403882; bh=ivfuNWs+5IeTVWXXAIWtmKIe7HCuyPMwzVIyn5XSS/o=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=EJjuav3Yp3f3nLCRCRyPmmrwNr1RE0ccivuYR2E+0j/lKuN4Ta12uiGofr6sBMCJFQUXhuOyNdW+sXxy8Gt35rlEELJqHKUGh/Jq8RK8xnXMD/J90qygzhaa+pfhRyhJUOWJalj1HqVPNNXxHMJ/eZz4SXAXrLEBA4WctnDQz7tGmYJjTPjjjZZGln5n8218FZVfOVd9oNeyn/aTFrqLYI654qsl8dyjwgTvn24gg3YCChdFKIrN2zXl3X+iFkh0wQ/Nnwf9qa4SoGz3vTEt080SGk6PJ7lkxBfNz09KwthBn7QccqEk7w0Cdm5+To9rscCMr5WzzDMpjcjWKNPeIQ== X-YMail-OSG: EkzpMysVM1nD9RQgzkLKENkQyBxvEvLhYr_9KmZLGipdB4Q7my8JI2kovfHSHUm XVgHC9wTYcC5DZ6XxJo68ldwPS9f1bMlvTFhbL0khLOybQ7EXEYHtD65nE7JkiAKhiLcdz6JikOW e5zhjjwyRQw81aLzItLyUnZ_j5mX1GfJjxWeviC5B_Qtdv9JYx_cFDRgTXNXXKMT5akbWuvFzHnV BISsIayyt9jRdnWqrG5uFP1PjpHoFwNx7bVIfWPPbQDOYW7ECUkzwz8SLfFpJCzxBu9zhfOGy_nD 1zl2LTVHBscyv_M.FuJddeQ4ZMTAtvcmEDOV6nE0tv4mA4Mkx7KLAVWcjkZy1zdtY15Mh1fWseIu Ufua8X_ucBNvCGcrjrGXSNaosSboh6ByjWp2SrYxtZKoMXrCQhYF4PRBepYh05ZP7rqxYa7FTgjt _gLpi0.zjgtxWX1ECTCxkedtleO0zo5qIePRl5t6Pzc_t.1NTWr6n8Z_op60Zlu4nQ0_2FWY3iQc lpW0QgDuUG6MhwNra078JbefOwQyz4dd_.kGfAbJsRBtRQZpRovrRujgs_wQluipwrMRFz1N6Drl huoUk_7uuf83JHrY1CTTFid7bfQvjP8fhPLM4SZx4PdYLDm2XJ8dA60hrVjuNGT0.VkeKX.1RJTF UTtNYM0YEA.OCcdhGj8DUAG22WKjkm95SQW4rdkU9GZTz3a7V3qzdopZs8HREx.kvTlQWnBOKy65 P7XelKWtyImaqVZfMKSFouZ80M6OB6wTqhEBGJQ2YpRCrm_P0cBzCFz39..vHRqLkv2188zA8kj6 lT1EEcGu9nAIDGV.ItFOC2.Up2OHr1DC90L3nOZtgYfo2nJ6y2SyFRHHy20ncxtdKQ.iOIOn0pkt cdkErw7T31Jp0YD94s3WRN6Rs2BAVlUSDDrf7u_zGMFsspwlqRSM4RaGhq75J9p113__0msJw1zM UtQhtWSHz87HP5dttRUNzLsDC6wiWrNEbwQTpoTD2x5bjwE9bBHkcM4BFliF3PdbiKv2PFzVoyn0 1J7qMxYyfXY6LK8AQJCoYoXAFm68KeqC43tkiR1ijNztRz1CBceBEQZ0ajPwMukbDg1IwPA4a0Ki TdB7z7DyI5z5jHqvEc2bpfo0t1JpM3jTx6TMc5_q5zZmGn02wovK4fdLTwmeCHJiqjw21t.JFIdl 6aYy9GPUqnrgtkDxmH7Ah5XHninjZrdQlIheuCxS7D.0iok5lahgHGOm1lvPQB556iZZ7NGWks5y bzam_giNYfV2aqENJcBclC58HNPKP8.7OU8Phjw.PiYPCqcV0JSixKNUApGFx80sqh81XfH6C8qJ 335KxiYuk49AXafnvT8IFfunZiKP4TC8M1fRZqHG3nDVAZrmFTHBPxDLiJNKQ06qvIAzZidpBR.l UU_npaB3HSytiLfe0yuZNgOSLwydaoTrnkSjQhjg9P0z6eqkeUkdzAVLTqLn8teEVUqYZPbYZvo5 .BahJY8zr2s8FHoTh6Zw- Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.ne1.yahoo.com with HTTP; Tue, 29 Sep 2020 18:24:42 +0000 Date: Tue, 29 Sep 2020 18:24:41 +0000 (UTC) From: Nagaraj Raj To: "pgsql-bugs@lists.postgresql.org" Cc: Tom Lane Message-ID: <739730714.324945.1601403881921@mail.yahoo.com> In-Reply-To: <1291615891.327892.1601403505183@mail.yahoo.com> References: <280232870.3108583.1600280574763.ref@mail.yahoo.com> <280232870.3108583.1600280574763@mail.yahoo.com> <1750081.1600282298@sss.pgh.pa.us> <1291615891.327892.1601403505183@mail.yahoo.com> Subject: Re: table partition with inheritance having current_timestamp issue if we miss range table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_324944_1459944948.1601403881916" X-Mailer: WebService/1.1.16718 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36 Content-Length: 29327 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_324944_1459944948.1601403881916 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =20 =20 Hi, Attached trigger function and trigger with table structure, problem is if t= he child table does exit it's not omitting the error and data directing to = the master table with a futuristic data value of load_dttm. basically, it was working code in version 9.6. Postgres 11 | db<>fiddle |=20 |=20 | |=20 Postgres 11 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | -- Table: l_billing_account -- DROP TABLE l_billing_account; CREATE TABLE l_billing_account(=C2=A0 =C2=A0 billing_account_guid character= varying(40) COLLATE pg_catalog."default" NOT NULL,=C2=A0 =C2=A0 ingestion_= process_id bigint NOT NULL DEFAULT '-1'::integer,=C2=A0 =C2=A0 load_dttm ti= mestamp(6) without time zone NOT NULL,=C2=A0 =C2=A0 log_position_number cha= racter varying(40) COLLATE pg_catalog."default" NOT NULL,=C2=A0 =C2=A0 oper= ation_code character varying(40) COLLATE pg_catalog."default" NOT NULL,=C2= =A0 =C2=A0 commit_dttm timestamp(6) without time zone NOT NULL,=C2=A0 =C2= =A0 oracle_scn character varying(40) COLLATE pg_catalog."default",=C2=A0 = =C2=A0 ban integer NOT NULL,=C2=A0 =C2=A0 bl_cur_bill_seq_no smallint,=C2= =A0 =C2=A0 bl_last_prod_date timestamp(6) without time zone,=C2=A0 =C2=A0 b= ill_method character varying(2) COLLATE pg_catalog."default",=C2=A0 =C2=A0 = bill_method_eff_date timestamp(6) without time zone,=C2=A0 =C2=A0 rstr_fee_= qid character varying(15) COLLATE pg_catalog."default",=C2=A0 =C2=A0 prev_b= ill_format character varying(2) COLLATE pg_catalog."default",=C2=A0 =C2=A0 = CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)); -- FUNCTION: func_l_billing_account_insert_trigger() CREATE FUNCTION func_l_billing_account_insert_trigger()=C2=A0 =C2=A0 RETURN= S trigger=C2=A0 =C2=A0 LANGUAGE 'plpgsql'=C2=A0 =C2=A0 COST 100=C2=A0 =C2= =A0 VOLATILE NOT LEAKPROOFAS $BODY$BEGIN=C2=A0 =C2=A0=C2=A0 =C2=A0IF=C2=A0 = ( NEW.load_dttm >=3D=C2=A0 '2020-02-01 00:00:00-07' AND=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0NEW.load_dttm <=C2=A0 '2020-02-16 00:00:00-07' ) THEN=C2=A0 = =C2=A0 =C2=A0 =C2=A0 INSERT INTO l_billing_account_y2020m02begin VALUES (NE= W.*);=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 ELSIF ( NEW.load_dttm >=3D=C2= =A0 '2020-02-16 00:00:00-07' AND=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 N= EW.load_dttm <=C2=A0 '2020-03-01 00:00:00-07' ) THEN=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO l_billing= _account_y2020m02end VALUES (NEW.*);=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0= =C2=A0 ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-03-01 00:00:00-07' AND=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-03-16 00:00= :00-07' ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 = =C2=A0 =C2=A0 INSERT INTO l_billing_account_y2020m03begin VALUES (NEW.*); = ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-03-16 00:00:00-07' AND=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-04-01 00:00:00-07' = ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0 INSERT INTO l_billing_account_y2020m03end VALUES (NEW.*); =C2=A0 =C2=A0 ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-04-01 00:00:00-07' AND= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-04-16= 00:00:00-07' ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 INSERT INTO l_billing_account_y2020m04begin VALUES (NE= W.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-04-16 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-05-01 00:00:00-07'= ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0 INSERT INTO l_billing_account_y2020m04end VALUES (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-05-01 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-05-16 00:00:00-07'= ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0 INSERT INTO l_billing_account_y2020m05begin VALUES (NEW.*); ELSIF (= NEW.load_dttm >=3D=C2=A0 '2020-05-16 00:00:00-07' AND=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-06-01 00:00:00-07' ) THEN= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 = INSERT INTO l_billing_account_y2020m05end VALUES (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-06-01 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-06-16 00:00:00-07'= ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0 INSERT INTO l_billing_account_y2020m06begin VALUES (NEW.*); ELSIF (= NEW.load_dttm >=3D=C2=A0 '2020-06-16 00:00:00-07' AND=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-07-01 00:00:00-07' ) THEN= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 = INSERT INTO l_billing_account_y2020m06end VALUES (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-07-01 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-07-16 00:00:00-07'= ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0 INSERT INTO l_billing_account_y2020m07begin VALUES (NEW.*); ELSIF (= NEW.load_dttm >=3D=C2=A0 '2020-07-16 00:00:00-07' AND=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-08-01 00:00:00-07' ) THEN= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 = INSERT INTO l_billing_account_y2020m07end VALUES (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-08-01 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-08-16 00:00:00-07'= ) THEN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0 INSERT INTO l_billing_account_y2020m08begin VALUES (NEW.*); ELSIF (= NEW.load_dttm >=3D=C2=A0 '2020-08-16 00:00:00-07' AND=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-09-01 00:00:00-07' ) THEN= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 = INSERT INTO l_billing_account_y2020m08end VALUES (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-09-01 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-09-16 00:00:00-07'= ) THEN INSERT INTO l_billing_account_y2020m09begin VALUES (NEW.*);=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-09-16 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-10-01 00:00:00-07'= ) THEN =C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO l_billing_account_y2020m09end VALUE= S (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-10-01 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-10-16 00:00:00-07'= ) THEN INSERT INTO l_billing_account_y2020m10begin VALUES (NEW.*); ELSIF ( NEW.load_dttm >=3D=C2=A0 '2020-10-16 00:00:00-07' AND=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 NEW.load_dttm <=C2=A0 '2020-11-01 00:00:00-07'= ) THEN INSERT INTO l_billing_account_y2020m10end VALUES (NEW.*); =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 ELSE=C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE EXCE= PTION=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 ' out of range exception.=C2=A0 Fix = the func_l_billing_account_insert_trigger() function.=C2=A0=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0HINT: Might need to create a new partition table, load_= dttm is out of range in existing child_tables=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Action:=C2=A0 Please contact DBA' ;=C2=A0 =C2=A0 END IF;=C2=A0 =C2=A0= RETURN NULL;END;$BODY$; -- Trigger: l_billing_account_partition_trigger CREATE TRIGGER l_billing_account_partition_trigger=C2=A0 =C2=A0 BEFORE INSE= RT OR UPDATE=C2=A0=C2=A0 =C2=A0 ON l_billing_account=C2=A0 =C2=A0 FOR EACH = ROW=C2=A0 =C2=A0 EXECUTE PROCEDURE func_l_billing_account_insert_trigger(); -- Table: l_billing_account_y2020m09end -- DROP TABLE l_billing_account_y2020m09end; CREATE TABLE l_billing_account_y2020m09end( CONSTRAINT l_billing_account_y2= 020m09end_load_dttm_check CHECK (load_dttm >=3D '2020-09-16 00:00:00'::time= stamp without time zone=C2=A0AND load_dttm < '2020-10-01 00:00:00'::timesta= mp without time zone)) INHERITS (l_billing_account); Thanks,Rj On Wednesday, September 16, 2020, 11:51:46 AM PDT, Tom Lane wrote: =20 =20 Nagaraj Raj writes: > How to reproduce the issue, > 1.=C2=A0Create a=C2=A0table partition with table=C2=A0inheritance by rang= e on the date column2. Place triggers and trigger function3. In the trigger= function, update with=C2=A0inheritance tables from September to December o= r appropriate.4. while creating=C2=A0inheritance tables, skip one of the ta= bles which updated in the=C2=A0trigger function5. If you started to insert,= it would write into the master table with a future date. TBH, I strongly doubt that anyone is going to follow up on this report as given.=C2=A0 It seems at least as likely that the bug is in your trigger code as in Postgres proper.=C2=A0 Without the exact schema and trigger code= , anyone who did try couldn't be sure whether failure to see something interesting means that there's no Postgres bug or just that they'd failed to duplicate what you did. If you'd like us to take an interest, please submit a *self contained* test case.=C2=A0 Preferably a script that starts with an empty database, creates all the requisite objects, and then does whatever is needed to exhibit the misbehavior. =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane =20 ------=_Part_324944_1459944948.1601403881916 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


Hi,


Attached trigger function and trigger with table structure, problem is if= the child table does exit it's not omitting the error and data directing t= o the master table with a futuristic data value of load_dttm.

basically, it was workin= g code in version 9.6.








-- Table: l_bil= ling_account

-- DROP TABLE l_billin= g_account;

CREATE TABLE l_billing_a= ccount
(
    billing_account_guid character v= arying(40) COLLATE pg_catalog."default" NOT NULL,
    i= ngestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
 =   load_dttm timestamp(6) without time zone NOT NULL,
 =   log_position_number character varying(40) COLLATE pg_catalog."defau= lt" NOT NULL,
    operation_code character varying(40) = COLLATE pg_catalog."default" NOT NULL,
    commit_dttm = timestamp(6) without time zone NOT NULL,
    oracle_scn= character varying(40) COLLATE pg_catalog."default",
   = ; ban integer NOT NULL,
    bl_cur_bill_seq_no smallint= ,
    bl_last_prod_date timestamp(6) without time zone,=
    bill_method character varying(2) COLLATE pg_catalo= g."default",
    bill_method_eff_date timestamp(6) with= out time zone,
    rstr_fee_qid character varying(15) C= OLLATE pg_catalog."default",
    prev_bill_format chara= cter varying(2) COLLATE pg_catalog."default",
    CONST= RAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
);



-- FUNCTION: func_l_bil= ling_account_insert_trigger()



CREATE FUNCTION func= _l_billing_account_insert_trigger()
    RETURNS trigger=
    LANGUAGE 'plpgsql'
    COST 10= 0
    VOLATILE NOT LEAKPROOF
AS $BODY$
<= div>BEGIN
   
   IF  ( NEW.loa= d_dttm >=3D  '2020-02-01 00:00:00-07' AND
    &= nbsp;    NEW.load_dttm <  '2020-02-16 00:00:00-07' ) THEN=
        INSERT INTO l_billing_account_y2020m= 02begin VALUES (NEW.*);
          <= /div>
=09ELSIF ( NEW.load_= dttm >=3D  '2020-02-16 00:00:00-07' AND
    &nb= sp;       NEW.load_dttm <  '2020-03-01 00:00:00-07' = ) THEN
            
=         INSERT INTO l_billing_account_y2020m02end VALUE= S (NEW.*);
        
  &nb= sp; ELSIF ( NEW.load_dttm >=3D  '2020-03-01 00:00:00-07' AND
<= div>            NEW.load_dttm <  '202= 0-03-16 00:00:00-07' ) THEN
          &n= bsp; 
        INSERT INTO l_billing_acco= unt_y2020m03begin VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.load_dttm >=3D  '2020-03-16 00:00:00-07' AND
<= div>            NEW.load_dttm <  '202= 0-04-01 00:00:00-07' ) THEN
          &n= bsp; 
        INSERT INTO l_billing_acco= unt_y2020m03end VALUES (NEW.*);

&nb= sp;   ELSIF ( NEW.load_dttm >=3D  '2020-04-01 00:00:00-07' AND=
            NEW.load_dttm <&nbs= p; '2020-04-16 00:00:00-07' ) THEN
        &n= bsp;   
        INSERT INTO l_billi= ng_account_y2020m04begin VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm= >=3D  '2020-04-16 00:00:00-07' AND
      =       NEW.load_dttm <  '2020-05-01 00:00:00-07' ) TH= EN
            
&nbs= p;       INSERT INTO l_billing_account_y2020m04end VALUES (N= EW.*);

=09ELSIF ( NEW.load_dttm >=3D  '2020-05-01 00:00:0= 0-07' AND
            NEW.load_dttm= <  '2020-05-16 00:00:00-07' ) THEN
      =       
        INSERT INT= O l_billing_account_y2020m05begin VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.load_dttm >=3D  '2020-05-16 00:00:0= 0-07' AND
            NEW.load_dttm= <  '2020-06-01 00:00:00-07' ) THEN
      =       
        INSERT INT= O l_billing_account_y2020m05end VALUES (NEW.*);

=09ELSIF ( NEW.lo= ad_dttm >=3D  '2020-06-01 00:00:00-07' AND
    =         NEW.load_dttm <  '2020-06-16 00:00:00-0= 7' ) THEN
            
        INSERT INTO l_billing_account_y2020m06begin = VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.lo= ad_dttm >=3D  '2020-06-16 00:00:00-07' AND
    =         NEW.load_dttm <  '2020-07-01 00:00:00-0= 7' ) THEN
            
        INSERT INTO l_billing_account_y2020m06end VA= LUES (NEW.*);

=09ELSIF ( NEW.load_dttm >=3D  '2020-07-01 = 00:00:00-07' AND
            NEW.lo= ad_dttm <  '2020-07-16 00:00:00-07' ) THEN
    =         
        INS= ERT INTO l_billing_account_y2020m07begin VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.load_dttm >=3D  '2020-07-16 = 00:00:00-07' AND
            NEW.lo= ad_dttm <  '2020-08-01 00:00:00-07' ) THEN
    =         
        INS= ERT INTO l_billing_account_y2020m07end VALUES (NEW.*);

=09ELSIF = ( NEW.load_dttm >=3D  '2020-08-01 00:00:00-07' AND
 =           NEW.load_dttm <  '2020-08-16 00= :00:00-07' ) THEN
            =
        INSERT INTO l_billing_account_y2020m= 08begin VALUES (NEW.*);
=09= =09
=09ELSIF = ( NEW.load_dttm >=3D  '2020-08-16 00:00:00-07' AND
 =           NEW.load_dttm <  '2020-09-01 00= :00:00-07' ) THEN
            =
        INSERT INTO l_billing_account_y2020m= 08end VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm >=3D  '2= 020-09-01 00:00:00-07' AND
          &nb= sp; NEW.load_dttm <  '2020-09-16 00:00:00-07' ) THEN
=09
=09INSERT INTO l_billing_account_y2020m09begin VAL= UES (NEW.*);
           
=
=09ELSIF ( NEW.load_dttm >=3D  '2020-09-16 00:00:00-07' AND
            NEW.load_dttm <  '2020= -10-01 00:00:00-07' ) THEN

  &= nbsp;     INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*= );

=09ELSIF ( NEW.load_dttm >=3D  '2020-10-01 00:00:00-07= ' AND
            NEW.load_dttm <= ;  '2020-10-16 00:00:00-07' ) THEN

=
=09INSERT INTO l_billing_= account_y2020m10begin VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm &g= t;=3D  '2020-10-16 00:00:00-07' AND
      &nb= sp;     NEW.load_dttm <  '2020-11-01 00:00:00-07' ) THEN<= /div>

=09INSERT INTO l_billing_account_y2020m10end VALUES (NEW.*);

    
  &n= bsp; ELSE
        RAISE EXCEPTION 
=
        ' out of range exception.  Fix the fu= nc_l_billing_account_insert_trigger() function. 
  &nbs= p;      HINT: Might need to create a new partition table, lo= ad_dttm is out of range in existing child_tables
    &n= bsp;    Action:  Please contact DBA' ;
  &nbs= p; END IF;
    RETURN NULL;
END;
$B= ODY$;




-- Trigger: l_billing_account_partition_trigger
<= div>


CREATE TRIGGER l_billing_account_partition_trigger
    BEFORE INSERT OR UPDATE 
    = ON l_billing_account
    FOR EACH ROW
  =   EXECUTE PROCEDURE func_l_billing_account_insert_trigger();

-- Table: l_billing_a= ccount_y2020m09end

-- DROP TABLE l_= billing_account_y2020m09end;

CREATE= TABLE l_billing_account_y2020m09end
( CONSTRAINT l_billing_accou= nt_y2020m09end_load_dttm_check CHECK (load_dttm >=3D '2020-09-16 00:00:0= 0'::timestamp without time zone 
AND load_dttm < '2020-10= -01 00:00:00'::timestamp without time zone)
) INHERITS (l_billing= _account);



=

Thanks,
Rj





=20
=20
On Wednesday, September 16, 2020, 11:51:46 AM PDT, Tom = Lane <tgl@sss.pgh.pa.us> wrote:


Nagaraj Raj <nagara= j.sf@yahoo.com> writes:

> How= to reproduce the issue,
> 1. Create a table= partition with table inheritance by range on the date column2. Place = triggers and trigger function3. In the trigger function, update with i= nheritance tables from September to December or appropriate.4. while creati= ng inheritance tables, skip one of the tables which updated in the&nbs= p;trigger function5. If you started to insert, it would write into the mast= er table with a future date.


TBH= , I strongly doubt that anyone is going to follow up on this report
as given.  It seems at least as likely that the bug is in y= our trigger
code as in Postgres proper.  Without the= exact schema and trigger code,
anyone who did try couldn= 't be sure whether failure to see something
interesting m= eans that there's no Postgres bug or just that they'd
fai= led to duplicate what you did.

If you'= d like us to take an interest, please submit a *self contained*
test case.  Preferably a script that starts with an empty datab= ase,
creates all the requisite objects, and then does wha= tever is needed
to exhibit the misbehavior.

          &= nbsp; regards, tom lane


------=_Part_324944_1459944948.1601403881916--