Re: Weird behavior of INSERT QUERY

Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres <
satalabaha(dot)postgres(at)gmail(dot)com> escreveu:

> Hi Listers,
>
> DB : postgres 14.
>
> We are experiencing weird performance issue of one simple insert statement
> taking several minutes to insert data. The application calls insert
> statement via stored procedure show mentioned below.
>
> The select query in the insert returns about 499 rows. However, this
> insert statement when executed from application user i.e. schema1_u takes
> close to 8 minutes. When the same insert statement gets executed as
> postgres user it takes less than 280 ms. Both the executions use the same
> execution plan with only difference that when schema1_u executes the SQL,
> we observe "Trigger for constraint fk_con_tablea: time=426499.314
> calls=499" taking more time. Both the parent and child tables are not big
> in size. There is no table bloat etc for both of these tables. Below are
> the details.
> Is there any way we can identify why as postgres user the insert statement
> works fine and why not with application user schema1_u?
>
> Stored Procedure:
> ====================
>
> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
> precision, parcreatedby text)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> insert into table_a
> (
> ROWVERSION,
> CREATED,
> ISDELETED,
> ISIGNORED,
> IMPORTEDACCOUNTCODE,
> IMPORTEDUNITCODE,
> BEGINNINGBALANCE,
> ENDINGBALANCE,
> CREATEDBY,
> FILEID
> )
> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
> to_timestamp(To_char(clock_timestamp() at time zone
> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
> false,
> false,
> IMPORTEDACCOUNTCODE,
> IMPORTEDUNITCODE,
> BEGINNINGBALANCE,
> ENDINGBALANCE,
> parCreatedBy,
> FILEID
> from STAGING_table_a
> where FILEID = parFileId;
>
> END;
> $function$
> ;
>
Can you show what type is FILEID?

Can there be type mismatch?

regards,
Ranier Vilela