From: | Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | table partition with inheritance having current_timestamp issue if we miss range table |
Date: | 2020-09-16 18:22:54 |
Message-ID: | 280232870.3108583.1600280574763@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-09-16 18:51:38 | Re: table partition with inheritance having current_timestamp issue if we miss range table |
Previous Message | Peter Geoghegan | 2020-09-16 17:47:19 | Re: BUG #16619: Amcheck detects corruption in hstore' btree index (ver 2) |