Issuing a debug SQL statement for a given transaction

[Giulio Cesare Solaroli <gi...@gmail.com>]

Hello,

I am still fighting with the very slow deletion problem I had a few
weeks ago. Now I probably have understood what is causing it.

I have defined most, if not all the constraints in my db schema as
DEFERRABLE INITIALLY DEFERRED.

In Postgres (but probably also in other DB), this causes the firing of
all the constraint triggers upon commit, making very difficult to
track which one is being too slow.

In order to debug this issue, Postgres can override all the
constraints attributes using the statement SET CONSTRAINTS ALL
IMMEDIATE.

How can I issue this command on the transaction where I am
experiencing the slow down?

Thanks for your attention.

Best regards,

Giulio Cesare

Re: Issuing a debug SQL statement for a given transaction

[Andrus Adamchik <an...@objectstyle.org>]

Here is one way of doing it - manual transactions that can wrap an  
arbitrary number of statements. You can use SQLtemplate to execute  
the constraints statement before context commit:

http://cayenne.apache.org/doc/understanding-transactions.html

Andrus

On Oct 24, 2007, at 11:04 PM, Giulio Cesare Solaroli wrote:

> Hello,
>
> I am still fighting with the very slow deletion problem I had a few
> weeks ago. Now I probably have understood what is causing it.
>
> I have defined most, if not all the constraints in my db schema as
> DEFERRABLE INITIALLY DEFERRED.
>
> In Postgres (but probably also in other DB), this causes the firing of
> all the constraint triggers upon commit, making very difficult to
> track which one is being too slow.
>
> In order to debug this issue, Postgres can override all the
> constraints attributes using the statement SET CONSTRAINTS ALL
> IMMEDIATE.
>
> How can I issue this command on the transaction where I am
> experiencing the slow down?
>
> Thanks for your attention.
>
> Best regards,
>
> Giulio Cesare
>