Re: [postgis-users] QGIS freezes when saving table with AFTER INSERT or UPDATE

[Nicolas Ribot via postgis-users]

Hello,

A trigger is executed in a single transaction: "The execution of an
AFTER trigger
can be deferred to the end of the transaction, rather than the end of the
statement, if it was defined as a *constraint trigger*. In all cases, a
trigger is executed as part of the same transaction as the statement that
triggered it, so if either the statement or the trigger causes an error,
the effects of both will be rolled back." (
https://www.postgresql.org/docs/16/trigger-definition.html)

So QGIS has to wait for it to finish.
Nicolas

On Thu, 4 Jan 2024 at 11:16, Alexandre Neto via postgis-users <
postgis-users@lists.osgeo.org> wrote:

> Sorry for the cross posting.
>
> I have a table with the following trigger
>
> *CREATE* *TRIGGER* tr_base_cont_trocos_ai *AFTER*
> *INSERT* *OR* *UPDATE* *ON* base.cont_troco
> *FOR* *EACH* *STATEMENT*
> *EXECUTE* *FUNCTION* tr_gerar_outputs();
>
> Now, the tr_gerar_outputs() takes some time to process, as it generates
> new tables and updates a few materialized views.
>
> In QGIS, when I add or update one or more features and press save, I would
> expect it to be immediate, but it seems that QGIS hangs waiting for the
> function to finish before considering the save operation complete. If the
> function fails, it even prevent the feature to be commit.
>
> Is this expected for a AFTER INSERT OR UPDATE trigger? Shouldn't
> PostgreSQL commit the changes, inform QGIS that the operation was
> successful and only then execute the trigger?
>
> Thanks,
>
> Alexandre Neto
>
> Sent with Shortwave
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] QGIS freezes when saving table with AFTER INSERT or UPDATE

[Alexandre Neto via postgis-users]

Uhmm... Thanks.

So there's no way to avoid hanging QGIS while the trigger function(s) is 
running. I am guessing that if I have another trigger function in a table that 
is automatically populated by the first trigger, it will also be executed in 
the QGIS transaction, right?

Thanks Again,

Alexandre Neto

Sent with Shortwave 


On Thu Jan 4, 2024, 11:45 AM GMT, Nicolas Ribot 
 wrote:
> Hello,
>
> A trigger is executed in a single transaction: "The execution of an AFTER 
> trigger can be deferred to the end of the transaction, rather than the end of 
> the statement, if it was defined as a constraint trigger. In all cases, a 
> trigger is executed as part of the same transaction as the statement that 
> triggered it, so if either the statement or the trigger causes an error, the 
> effects of both will be rolled back." 
> (https://www.postgresql.org/docs/16/trigger-definition.html 
> )
>
> So QGIS has to wait for it to finish.
>
> Nicolas
>
> On Thu, 4 Jan 2024 at 11:16, Alexandre Neto via postgis-users 
> mailto:postgis-users@lists.osgeo.org>> wrote:
>> Sorry for the cross posting.
>>
>> I have a table with the following trigger
>>
>> CREATE TRIGGER tr_base_cont_trocos_ai AFTER
>> INSERT OR UPDATE ON base.cont_troco
>> FOR EACH STATEMENT
>> EXECUTE FUNCTION tr_gerar_outputs();
>>
>> Now, the tr_gerar_outputs() takes some time to process, as it generates new 
>> tables and updates a few materialized views.
>>
>> In QGIS, when I add or update one or more features and press save, I would 
>> expect it to be immediate, but it seems that QGIS hangs waiting for the 
>> function to finish before considering the save operation complete. If the 
>> function fails, it even prevent the feature to be commit.
>>
>> Is this expected for a AFTER INSERT OR UPDATE trigger? Shouldn't PostgreSQL 
>> commit the changes, inform QGIS that the operation was successful and only 
>> then execute the trigger?
>>
>> Thanks,
>>
>> Alexandre Neto
>>
>> Sent with Shortwave 
>> 
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org 
>> https://lists.osgeo.org/mailman/listinfo/postgis-users 
>> ___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users