From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: table partition with inheritance having current_timestamp issue if we miss range table
Date: Wed, 16 Sep 2020 18:22:54 +0000 (UTC)
Message-ID: <280232870.3108583.1600280574763@mail.yahoo.com> (raw)
References: <280232870.3108583.1600280574763.ref@mail.yahoo.com>
Hi,
I have implemented table partition with an inheritance approach (Recently migrated/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 current_timestamp and respective this created trigger function with BEFORE INSERT OR UPDATE.
Everything good so far, but earlier I have inheritance tables 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 function without missing September 2020.
So, when injection started for September 2020 into a master table, the trigger didn't occur through any error because it's satisfied the 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 insert 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_timestamp; it recorded future timestamp, i.e., January 2021.
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 inheritance tables from September to December or appropriate.4. while creating inheritance tables, skip one of the tables which updated in the trigger function5. If you started to insert, it 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: nagaraj.sf@yahoo.com, pgsql-bugs@lists.postgresql.org
Subject: Re: table partition with inheritance having current_timestamp issue if we miss range table
In-Reply-To: <280232870.3108583.1600280574763@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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