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 1kIc5C-0004Hr-MT for pgsql-bugs@arkaria.postgresql.org; Wed, 16 Sep 2020 18:23:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kIc5A-0004iO-Nn for pgsql-bugs@arkaria.postgresql.org; Wed, 16 Sep 2020 18:23:04 +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 1kIc5A-0004iG-7H for pgsql-bugs@lists.postgresql.org; Wed, 16 Sep 2020 18:23:04 +0000 Received: from sonic301-32.consmr.mail.ne1.yahoo.com ([66.163.184.201]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kIc57-0008KH-Fi for pgsql-bugs@lists.postgresql.org; Wed, 16 Sep 2020 18:23:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1600280580; bh=8zmhherd5BDFLAXXy/66TF6H9L/88R4ZFyxqbkzOOJ4=; h=Date:From:To:Subject:References:From:Subject; b=lNn5Qj3tVRbLCgMVLlU1h29IcIrFImSJxruq2bfq9mLNm6wVqxegg48m25joWRysEphfTkZ9YtQ2vBGb9sHEvA4k40Z1VZ2EnlKOEDiXDxfLTwAISK7/2SV+/MkAAXuN26bZC12tJ4Vz43k5t6NG9MOLF6PDi3OQRGEaRZu6K45Ki/3eUTQQSzcAi0IzMSSQ99gWtsXDgW3bnSIG6gzHOAnPZY6zdbORbj8FA9L/RLr3420Qy3Q/j5zbOx+x3yw9zv3xLOK8GRKNcGP/cW8Q8ux2j2SQ464HJZdU3DEfSNb66A7gRughhKkFHcqlznAPlciIyLhJFcXTz2HdEgp6EQ== X-YMail-OSG: KuHYMrMVM1kS2xXWrmb3dW1vQNqS_aD2ZJ8KLOib6bNB7TMj5vsPCbV9dY0OODU fKLjprKoTb7uXLgUt2bRkKTh3s9vTa09e7NtIZzsgV6l7NovyfFoult58_5wept511IPEwRNw2kB w_uisp7gOdKFc0Aro0o77RHU1_5P5qlJPajdfzlF2_9uCzeqz5TzfxE9GYEWWrZn.NZcIOnapyU8 AwY2_F_TKI1zx6MUSRB9hSrB31_4pp8PqAh4TyLKNAQEn.PLqWUiLvuH0HbqmnXXXM4q9cMPyyhF 325QXG7C0wM8MfjVzG85ymWfFynLxwyfXtV25ZsIiTElde2jvrVBgHE8KTGSTniVYHt_1_Y3WezW PpUoANeGn9kbjyZiEY5XOGI_FQ6P.g23kbImvVfljn8kFcD4D6ee.qICtgAg11q4lf73gKW5p02P zKvFy9qk3jdJrpG8BtNIgdzXVjSQzJkZ1I3uCyIq9F.EKcaGHvKUmj.ZUzqQoALhb6hJofHuib03 uLiwa53MMrqExz2ww9lH4TOlW27s9CndUVs8jZi1_F0yMLiD9oJZj_xcUa_z7d6rjzJ5i3CqEF9G 6AjVOFu4kmXdakWVOu0uUFAz9Zsje8KyRpS2gT4Yb.raaePDoloOBOtUwxe76NHbgZgA1gujUM0D f_PM2NT4OP9hZuZAaqFoPF3AvZ8lbFTjzQ_thfSxJa_V7URfu7CEd7WkOtBDeJ1wl.u1a.nfrRZt M.G9wTdGTCqIeXzp8_LrM2tVBX8jhwiJi9bK.jenTZ0ECzqhqFNH9EKf5orJ4p53HpDmRthhzTeH zi_GneEYK.TsCDNG_Saqpfg.2PsTiM0hv.j2Kyg_lWzkL9SBu_caPtcNisS_MwqLD9skeT5LUfl. OgatjMwPucDOBYUnIfGPx9_QNekg95kvF2qYJz092Vo9l.Nu2XqarFCUkjhLbZL9hJx4uimeXiBF 5GtqhFraGVdh_2cA7ZMMzpiwQDFfENbz3MY0.GN.M35gB0HARX4Pr.ZzM9IBPEqbZTfIETgc8aWz htW5r8QXmnUzz2nUa3pj8LBlCheFiv_.mxQh0pgxzYPxteDnXZhMhQD4qIKXbu7vWt9ie4ThjWn_ T5eN1UrKAap4uX7R9_6OrA4Q5Z2Mw9K.mSaUkcCd7TyjU76jLxYgTNmJSSaf.c_0T1oMRNPaxmNG I93CupY8kEb9W_sDB9VPVHKNWJQFDfCDtXd0QtaJn9kYMXef.a38RKwpQ4Fs5cfbEiWuFeJ0plct PlcJd8SuqdD.AEA_lWD2Fxmrx1GdGplLBWIa3Uy6VD8gxLqVOC5MaT1UXTnzoEtcjYuy77.XDMWA Dx0GQ01SBMmwNPrXnAosBaUKdgIaJrpZAv7ufdCT_yqOPKk4.G6HjyriyKdjru6IECkM.9ZC5YOD s4OP9cPTutKhvPW8Ds8yMkJznHbygTrbD0hKyAvz_oQzvHQQSdAVdE7d8KzvL_JdvVjQRIa4A Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.ne1.yahoo.com with HTTP; Wed, 16 Sep 2020 18:23:00 +0000 Date: Wed, 16 Sep 2020 18:22:54 +0000 (UTC) From: Nagaraj Raj To: "pgsql-bugs@lists.postgresql.org" Message-ID: <280232870.3108583.1600280574763@mail.yahoo.com> Subject: table partition with inheritance having current_timestamp issue if we miss range table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3108582_2045008292.1600280574761" References: <280232870.3108583.1600280574763.ref@mail.yahoo.com> X-Mailer: WebService/1.1.16583 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.83 Safari/537.36 Content-Length: 16768 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3108582_2045008292.1600280574761 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, I have implemented table partition with an=C2=A0inheritance approach (Recen= tly migrated/Upgraded from 9.6 to 11.7).=C2=A0 The issue is, I have a table and created=C2=A0inheritance tables for each m= onth; it has a date column load_date as it takes=C2=A0current_timestamp=C2= =A0 and respective this created trigger function with BEFORE=C2=A0=C2=A0INS= ERT OR UPDATE.=C2=A0 Everything good so far, but earlier=C2=A0 I have=C2=A0inheritance tables ti= ll August 2020, so I created=C2=A0inheritance tables till December 2020, an= d somehow I missed for the month September 2020 in the table creation. Stil= l, I updated the trigger function without missing=C2=A0September 2020. So, when injection started for=C2=A0September 2020 into a master table, the= trigger didn't occur through any error because it's satisfied the conditio= ns of the trigger function and after passing through the trigger function, = it should look for a table=C2=A0September 2020, because the condition is ba= se on=C2=A0load_date (current_timestamp=C2=A0) if not it should insert into= a master table or through any error,=20 The server acted strangely, records inserted to the master table, but load_= date didn't take=C2=A0current_timestamp; it recorded future timestamp, i.e.= , January 2021. How to reproduce the issue, 1.=C2=A0Create a=C2=A0table partition with table=C2=A0inheritance by range = on the date column2. Place triggers and trigger function3. In the trigger f= unction, update with=C2=A0inheritance tables from September to December or = appropriate.4. while creating=C2=A0inheritance tables, skip one of the tabl= es which updated in the=C2=A0trigger function5. If you started to insert, i= t would write into the master table with a future date. We are running PostgreSQL 11.7 on x86_64-pc-Linux-gnu, compiled by GCC (GCC= ) 4.9.3, 64-bit Thanks,Rj ------=_Part_3108582_2045008292.1600280574761 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

I have impl= emented table partition with an inheritance approach (Recently m= igrated/Upgraded from 9.6 to 11.7). 

The issue is, I have a table and created inheritance tables for each month; it has a date column= load_date as it takes cur= rent_timestamp  and respective this created trigger function with BEFO= RE  INSERT OR UPDATE. 

Everything good so far, but earlier&nbs= p; I have inheritance tabl= es till August 2020, so I created inheritance tables till December 2020, and somehow I missed for the= month September 2020 in the table creation. Still, I updated the trigger f= unction without missing Se= ptember 2020.
=


So, when injection started for September 2020 into a master = table, the trigger didn't occur through any error because it's satisfied th= e conditions of the trigger function and after passing through the trigger = function, it should look for a table September 2020, because the condition is base on load_date (current_timestamp ) if not it should inser= t into a master table or through any error,

= The = server acted strangely, records inserted to the master table, but load_date= didn't take current_times= tamp; it recorded future timestamp, i.e., January 2021.
<= span>
<= /span>

<= /span>

How = to reproduce the issue,

1. <= /span>Create a = ;table partition = with table inheritance by range on the date column
2. Place triggers and trigger function
3. In the trigger function, update with&nb= sp;inheritance tables from Sept= ember to December or appropriate.
4. while creating inheritance tables, skip one of the tables which updated in the&nb= sp;trigger function
5= . If you started to insert, it would write into the master table with a fut= ure date.


=
We are running PostgreSQL 11.7 on x8= 6_64-pc-Linux-gnu, compiled by GCC (GCC) 4.9.3, 64-bit


Thanks,
Rj
------=_Part_3108582_2045008292.1600280574761--