Обсуждение: table partition with inheritance having current_timestamp issue if we miss range table

Nagaraj Raj <nagaraj.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 trigger function, update with inheritance tables from September to December or appropriate.4. while
creating inheritancetables, skip one of the tables which updated in the trigger function5. If you started to insert, it
wouldwrite 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

On Wed, 30 Sep 2020 at 07:18, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> Attached trigger function and trigger with table structure, problem is if the child table does exit it's not omitting
theerror and data directing to the master table with a futuristic data value of load_dttm.
 

Using your script, it seems to work ok for me.

postgres=# insert into l_billing_account
values('',1,'2020-09-30','','',now(),'',1,1,now(),'',now(),'','');
INSERT 0 0
postgres=# select tableoid::regclass,load_dttm from l_billing_account;
           tableoid            |      load_dttm
-------------------------------+---------------------
 l_billing_account_y2020m09end | 2020-09-30 00:00:00
(1 row)

postgres=# drop table l_billing_account_y2020m09end ;
DROP TABLE
postgres=# insert into l_billing_account
values('',1,'2020-09-30','','',now(),'',1,1,now(),'',now(),'','');
ERROR:  relation "l_billing_account_y2020m09end" does not exist
LINE 1: INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*)
                    ^
QUERY:  INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*)
CONTEXT:  PL/pgSQL function func_l_billing_account_insert_trigger()
line 82 at SQL statement

postgres=# select version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.3.0-10ubuntu2) 9.3.0, 64-bit
(1 row)

Perhaps you have another table by that name some other namespace
that's in search_path.

David

Please always copy the list when replying.

On Wed, 30 Sep 2020 at 19:40, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>
> >trigger function and trigger with table structure, >problem is if the child table does exit it's not >omitting the
errorand data directing to the master >table with a futuristic data value of load_dttm. 
>
> If table does not exit then its not throwing error and values inserting to master table.

I tried that with the schema and trigger function you provided and on
the same version as you. It worked ok for me.

> I’m having only one table, I was trying to reproduce the error but could.

I suspect you meant "couldn't"?

As Tom mentioned, it seems much more likely there's some bug in your function.

> Not sure what is the root cause for it.

If you can provide a fully reproducible test case (not just a schema)
then you might have more luck here. Posting additional emails on other
threads about this is not a solution to your problem.

David