Re: How do I get the SQL statement in a trigger?

2018-05-10 12:50 GMT+02:00 Fabio Ugo Venchiarutti <f(dot)venchiarutti(at)ocado(dot)com>
:

> Querying over pg_stat_activity with clauses in a trigger can be quite
> expensive if your write transaction rate is high.
>
>
> You may want to look into the current_query() function documented at
> https://www.postgresql.org/docs/current/static/functions-info.html

sure - it is better - forgot it

Regards

Pavel

>
>
>
>
>
>
>
>
>
> On 10/05/18 11:44, Pavel Stehule wrote:
>
>>
>>
>> 2018-05-10 12:42 GMT+02:00 a <372660931(at)qq(dot)com <mailto:372660931(at)qq(dot)com
>> >>:
>>
>> Thanks a lot, would you please be so kind to tell me more about what
>> is top level query??
>>
>>
>> for example - if you run some queries from PLpgSQL functions (triggers),
>> then these queries are not top queries.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>> ------------------ Original message ------------------
>> *From:* "Pavel Stehule"__;
>> *Sendtime:* Thursday, May 10, 2018 6:38 PM
>> *To:* "a"<372660931(at)qq(dot)com <mailto:372660931(at)qq(dot)com>>;
>> *Cc:* "pgsql-general"__;
>> *Subject:* Re: How do I get the SQL statement in a trigger?
>>
>> Hi
>>
>> 2018-05-10 12:23 GMT+02:00 a <372660931(at)qq(dot)com
>> <mailto:372660931(at)qq(dot)com>>:
>>
>> Hi I would like to write a trigger that recorded every sql
>> statement under the effected entry. So if it is possible to
>> retrieve the sql statement within a trigger??
>>
>>
>> You can read a tom command from pg_stat_activity table
>>
>> postgres=# select query from pg_stat_activity where pid =
>> pg_backend_pid();
>> +------------------------------------------------------------------+
>> | query |
>> +------------------------------------------------------------------+
>> | select query from pg_stat_activity where pid = pg_backend_pid(); |
>> +------------------------------------------------------------------+
>> (1 row)
>>
>> But this information is available only for top level query.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
> --
> Regards
>
> Fabio Ugo Venchiarutti
> Data Services Department
> Ocado Technology
>
> --
>
>
> Notice: This email is confidential and may contain copyright material of
> members of the Ocado Group. Opinions and views expressed in this message
> may not necessarily reflect the opinions and views of the members of the
> Ocado Group.
>
>
>
> If you are not the intended recipient, please notify us immediately and
> delete all copies of this message. Please note that it is your
> responsibility to scan this message for viruses.
>
>
>
> Fetch and Sizzle are trading names of Speciality Stores Limited and Fabled
> is a trading name of Marie Claire Beauty Limited, both members of the Ocado
> Group.
>
>
>
>
> References to the “Ocado Group” are to Ocado Group plc (registered in
> England and Wales with number 7098618) and its subsidiary undertakings (as
> that expression is defined in the Companies Act 2006) from time to time.
> The registered office of Ocado Group plc is Buildings One & Two, Trident
> Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.
>