table partition with inheritance having current_timestamp issue if we miss range table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

table partition with inheritance having current_timestamp issue if we miss range table

Nagaraj Raj
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 column
2. Place triggers and trigger function
3. 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 function
5. 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 | Threaded
Open this post in threaded view
|

Re: table partition with inheritance having current_timestamp issue if we miss range table

Tom Lane-2
Nagaraj Raj <[hidden email]> 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 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.

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 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 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.  Preferably a script that starts with an empty database,
creates all the requisite objects, and then does whatever is needed
to exhibit the misbehavior.

                        regards, tom lane