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 1kNKDF-0001tI-JX for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 18:18:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kNKDE-0005JQ-HZ for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 18:18:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kNKDD-0005JJ-Uh for pgsql-bugs@lists.postgresql.org; Tue, 29 Sep 2020 18:18:52 +0000 Received: from sonic305-21.consmr.mail.ne1.yahoo.com ([66.163.185.147]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kNKD6-0004ob-FI for pgsql-bugs@lists.postgresql.org; Tue, 29 Sep 2020 18:18:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1601403521; bh=Q5HCsAXngPkq49xWmNt8Mi41NbquVtsDrH720DtWuxA=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=G+TFUl7G5s6Re0WktGtrBeXNbWzcAgsBB8aoIgg3WprgDakexkNf0OpJW0y/pE+CNByyqWDAwKAxfzpGaxzFMOdHQRCvq5RntZf1nrtRsChNzrmkiAkcK0S926iep6mmqYLDxG4PVSrIZlUQT54AS1jf1SaGPSlyCccwrAYZU/KcsC5Qq/YQAdXRpPAHrvcYeNaWLNfwHvgVWIy3MXdNA3p9OWJHBWEYkR8EJVCzG6lOKTijloLnYN/LOWBaMh3G+FoOcNS5TLWXSnxNvayG8TVyYO5lkcVDrc0CFtpjhIgKkCu/j/htDwPndEs3puLeF6eBk6dgNk2Iml7uiOdiHQ== X-YMail-OSG: f77NIHoVM1kDy2MHRaTvjZ6TqD4rLX7PYA3ss8lr.LYO4vVpnm5CT7i2gX8x3WQ fDWXo6zAipiwRYvhcWsYyxgLZ_01FgsCtZkUs8rQcACf8t4ChXzIAi2kk5dOTO.gdyHffjUfCtMt 62Lu7U_LhmjJ3QK7e4ntkZ3AdXsDV_KbZE2NDZM3uDodrKZHQt8BzwvyUfWbHrWESNERTBgnWqrx A6WDs3z5vy9khGNzvCFygI4zfaOtyJn3UprrxivqkedijRx.Rg_S_v38rhc_I41KFKEQ7pxdV3uR tyfyI9DS9EMbYGLbLw4ESjS3XWJRSIwyBnE2RTeZqHicDfLVJd33XgQO6Og0P6T2fbFK23MwJfLm KMYdqnVIoCSFz3PrP.lsrAja6VudBUQJJyh0AAz2lpggdQVmilqoJEreHZi2T7ATy.Dv6IRirczY SGHXkwC9GMWM1moyEFip7BRnV3.85qaheDhORBACtZY6t0Q3ZhfoJ2Dz_I7by2orR1H_NP81B0Bj .mzDn0yUBU_DhK1XgNiNjqTg1ik6TCQt3yi0I1hMYsNg6P_fmsPjZtSPYz753UB.sYEzRHPrHlhU N9RgoMMRdp.048eoKWoXuht8CJBxYednUuT7iAVTeqx4skPyy1w3wXN2eUiyhfwNPpfn6QfRl.qc _gxtcjCDGm_Te5fGg4jQoHp5riNFOAgrGS3XQCp3ln.30F98FKice7vJe4VlabIWmaYEqF_fCnrN IKev8iarbGlVIu3OSUSbz3yAHa2cm8hw6Vlk4kaHU1h.15t07NHD37o6xs0vyINA2UDrxt7R.Aec Uz6cux1OXubDT5Zs5AIlGkoSkEy12.z2EcNM2yI2tbEjebpwEQZpF.yS932Os7psW47bl655MFmP NYikK4_9GtzjrlBASU01TGe5jqetChTCgR5USA61kv5rcZVYVPW3J7Q68_2jmFHdhxpVCQ8.rZut XK5o4Ymk6LLWeeWO44dYoBQjNERB0o0YV6fS4o5_jcRZPUsb8T1p_5BOp_nb5D0HODIUZ7kvT6Y_ U4k59cp8RFBLblLN3gA0VuSJooyahF.TECmUqThvmjJJ9TKaQidkvqsZ0Veq0oTQAKig4S0H5kAx jGr5b4uERsfND.xijYfAETdsWxX8_Vq1YKmCn64FQwAyEzEeCWGxPs34HjQne7atyWZe3UGwZx81 tAyiFpbARjqeAE68GuInv522sUq_tVnL8_eopGZuoqKA80FD5VcvkSEs1taP1qfl3Mx8uwFoL4rv Shac4VoSfIT65dVwEW.e6rYJ_mKP7ZDClv4YKo99jBo5sr0jh.Z.v5wwa1JL3aIiv49BxosEfrSv Pc7IpqRKCt9ctGqfSTGeMNA84uE4Gbpt0h.gmyONZmlv2EaE6xbX8LOOTY7_puRqjvxvxUGhTT.Y qYRj_DuXK.CmMtLEx4gC9nAci93NtB6JvnOv2osgOfk4hZJEYZbiMWXoJP_JavoWaIByi9K45Ra9 lJxm0 Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ne1.yahoo.com with HTTP; Tue, 29 Sep 2020 18:18:41 +0000 Date: Tue, 29 Sep 2020 18:18:25 +0000 (UTC) From: Nagaraj Raj To: Tom Lane Cc: "pgsql-bugs@lists.postgresql.org" Message-ID: <1291615891.327892.1601403505183@mail.yahoo.com> In-Reply-To: <1750081.1600282298@sss.pgh.pa.us> References: <280232870.3108583.1600280574763.ref@mail.yahoo.com> <280232870.3108583.1600280574763@mail.yahoo.com> <1750081.1600282298@sss.pgh.pa.us> 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_327891_1653941488.1601403505180" 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: 27797 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_327891_1653941488.1601403505180 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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_327891_1653941488.1601403505180 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 direct= ing 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

=


-- Table: l_billing_account

-- DR= OP TABLE l_billing_account;

CREATE TABLE l_billing= _account
(
    billing_account_guid character= varying(40) COLLATE pg_catalog."default" NOT NULL,
   = ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
&nbs= p;   load_dttm timestamp(6) without time zone NOT NULL,
&nbs= p;   log_position_number character varying(40) COLLATE pg_catalog."def= ault" NOT NULL,
    operation_code character varying(40= ) COLLATE pg_catalog."default" NOT NULL,
    commit_dtt= m timestamp(6) without time zone NOT NULL,
    oracle_s= cn character varying(40) COLLATE pg_catalog."default",
  &nb= sp; ban integer NOT NULL,
    bl_cur_bill_seq_no smalli= nt,
    bl_last_prod_date timestamp(6) without time zon= e,
    bill_method character varying(2) COLLATE pg_cata= log."default",
    bill_method_eff_date timestamp(6) wi= thout time zone,
    rstr_fee_qid character varying(15)= COLLATE pg_catalog."default",
    prev_bill_format cha= racter varying(2) COLLATE pg_catalog."default",
    CON= STRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
<= div>);



-- FUNCTION: func_l_billing_account_insert_t= rigger()



CREATE FUNC= TION func_l_billing_account_insert_trigger()
    RETURN= S trigger
    LANGUAGE 'plpgsql'
   = ; COST 100
    VOLATILE NOT LEAKPROOF
AS $BOD= Y$
BEGIN
   
   IF  = ( NEW.load_dttm >=3D  '2020-02-01 00:00:00-07' AND
 =        NEW.load_dttm <  '2020-02-16 00:00:00-0= 7' ) THEN
        INSERT INTO l_billing_accou= nt_y2020m02begin VALUES (NEW.*);
        &nbs= p; 
=09ELSIF (= NEW.load_dttm >=3D  '2020-02-16 00:00:00-07' AND
  =           NEW.load_dttm <  '2020-03-01 00:= 00:00-07' ) THEN
            <= /div>
        INSERT INTO l_billing_account_y2020m0= 2end VALUES (NEW.*);
        
=     ELSIF ( NEW.load_dttm >=3D  '2020-03-01 00:00:00-07' = AND
            NEW.load_dttm <&= nbsp; '2020-03-16 00:00:00-07' ) THEN
       =     
        INSERT INTO l_bi= lling_account_y2020m03begin VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.load_dttm >=3D  '2020-03-16 00:00:00-07= ' AND
            NEW.load_dttm <= ;  '2020-04-01 00:00:00-07' ) THEN
      &nbs= p;     
        INSERT INTO l_= billing_account_y2020m03end VALUES (NEW.*);

 =   ELSIF ( NEW.load_dttm >=3D  '2020-04-01 00:00:00-07' AND
            NEW.load_dttm <  = '2020-04-16 00:00:00-07' ) THEN
         = ;   
        INSERT INTO l_billing_= account_y2020m04begin VALUES (NEW.*);

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

=09ELSIF ( NE= W.load_dttm >=3D  '2020-05-01 00:00:00-07' AND
  &nb= sp;         NEW.load_dttm <  '2020-05-16 00:00:= 00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m05be= gin VALUES (NEW.*);
=09=09=
=09ELSIF ( = NEW.load_dttm >=3D  '2020-05-16 00:00:00-07' AND
  &= nbsp;         NEW.load_dttm <  '2020-06-01 00:0= 0:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m05= end VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm >=3D  '2020-06-01 00:00:00= -07' AND
            NEW.load_dttm = <  '2020-06-16 00:00:00-07' ) THEN
      &= nbsp;     
        INSERT INTO= l_billing_account_y2020m06begin VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.load_dttm >=3D  '2020-06-16 00:00:= 00-07' AND
            NEW.load_dtt= m <  '2020-07-01 00:00:00-07' ) THEN
     =       
        INSERT IN= TO l_billing_account_y2020m06end VALUES (NEW.*);

<= span style=3D"white-space: pre-wrap;">=09ELSIF ( NEW.load_dttm >= =3D  '2020-07-01 00:00:00-07' AND
       = ;     NEW.load_dttm <  '2020-07-16 00:00:00-07' ) THEN
            
  &n= bsp;     INSERT INTO l_billing_account_y2020m07begin VALUES (NEW.= *);
=09=09
=09ELSIF ( NEW.load_dttm &g= t;=3D  '2020-07-16 00:00:00-07' AND
      &nb= sp;     NEW.load_dttm <  '2020-08-01 00:00:00-07' ) THEN<= /div>
            
  =       INSERT 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
          &nb= sp; 
        INSERT INTO l_billing_accou= nt_y2020m08begin VALUES (NEW.*);
=09=09
=09ELSIF ( NEW.load_dttm >=3D  '2020-08-16 00:00:00-07' AND
=
            NEW.load_dttm <  '20= 20-09-01 00:00:00-07' ) THEN
          &= nbsp; 
        INSERT INTO l_billing_acc= ount_y2020m08end VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm >=3D  '2020-0= 9-01 00:00:00-07' AND
            N= EW.load_dttm <  '2020-09-16 00:00:00-07' ) THEN
=09
=09INSERT INTO l_billing_account_y2020m09begin VALUES= (NEW.*);
           
=09ELSIF ( NEW.lo= ad_dttm >=3D  '2020-09-16 00:00:00-07' AND
    =         NEW.load_dttm <  '2020-10-01 00:00:00-0= 7' ) THEN

        INSERT INTO = l_billing_account_y2020m09end VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm >=3D&= nbsp; '2020-10-01 00:00:00-07' AND
        &n= bsp;   NEW.load_dttm <  '2020-10-16 00:00:00-07' ) THEN
<= div>
=09INSERT = INTO l_billing_account_y2020m10begin VALUES (NEW.*);

=09ELSIF ( NEW.load_dttm &= gt;=3D  '2020-10-16 00:00:00-07' AND
      &n= bsp;     NEW.load_dttm <  '2020-11-01 00:00:00-07' ) THEN=

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

    
    ELSE
  &= nbsp;     RAISE EXCEPTION 
      &n= bsp; ' out of range exception.  Fix the func_l_billing_account_insert_= trigger() function. 
         HINT:= Might need to create a new partition table, load_dttm is out of range in e= xisting child_tables
         Action:&nb= sp; Please contact DBA' ;
    END IF;
  =   RETURN NULL;
END;
$BODY$;




-- Trigger: l_billing_a= ccount_partition_trigger



=
CREATE TRIGGER l_billing_account_partition_trigger
  &n= bsp; BEFORE INSERT OR UPDATE 
    ON l_billing_acc= ount
    FOR EACH ROW
    EXECUTE P= ROCEDURE func_l_billing_account_insert_trigger();

-- Table: l_billing_account_y2= 020m09end

-- DROP TABLE l_billing_account_y2020m09= end;

CREATE TABLE l_billing_account_y2020m09end
( CONSTRAINT l_billing_account_y2020m09end_load_dttm_check CHECK (l= oad_dttm >=3D '2020-09-16 00:00:00'::timestamp without time zone 
AND load_dttm < '2020-10-01 00:00:00'::timestamp without time z= one)
) INHERITS (l_billing_account);





Thanks,
Rj

<= /div>




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


Nagaraj Raj <n= agaraj.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 tr= igger function, update with inheritance tables from September to Decem= ber or appropriate.4. while creating inheritance tables, skip one of t= he tables which updated in the trigger function5. If you started to in= sert, it would write into the master table with a future date.


TBH, I strongly doubt that anyone is going t= o follow up on this report
as given.  It seems at le= ast as likely that the bug is in your 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 someth= ing
interesting means that there's no Postgres bug or jus= t that they'd
failed to duplicate what you did.

If you'd like us to take an interest, please s= ubmit a *self contained*
test case.  Preferably a sc= ript that starts with an empty database,
creates all the = requisite objects, and then does whatever is needed
to ex= hibit the misbehavior.

  &nb= sp;         regards, tom lane


=
------=_Part_327891_1653941488.1601403505180--