xact_start meaning when dealing with procedures?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

xact_start meaning when dealing with procedures?

hubert depesz lubaczewski-2
Hi
I just noticed that when I called a procedure that commits and rollbacks
- the xact_start in pg_stat_activity is not updated. Is it intentional?

I'm on newest 12devel, built today.

Best regards,

depesz


Reply | Threaded
Open this post in threaded view
|

Re: xact_start meaning when dealing with procedures?

Peter Eisentraut-6
On 09/08/2018 19:57, hubert depesz lubaczewski wrote:
> I just noticed that when I called a procedure that commits and rollbacks
> - the xact_start in pg_stat_activity is not updated. Is it intentional?

It's an artifact of the way this is computed.  The reported transaction
timestamp is the timestamp of the first top-level statement of the
transaction.  This assumes that transactions contain statements, not the
other way around, like it is now possible.  I'm not sure what an
appropriate improvement would be here.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: xact_start meaning when dealing with procedures?

Vik Fearing-4
On 09/08/18 20:13, Peter Eisentraut wrote:
> On 09/08/2018 19:57, hubert depesz lubaczewski wrote:
>> I just noticed that when I called a procedure that commits and rollbacks
>> - the xact_start in pg_stat_activity is not updated. Is it intentional?
>
> It's an artifact of the way this is computed.  The reported transaction
> timestamp is the timestamp of the first top-level statement of the
> transaction.  This assumes that transactions contain statements, not the
> other way around, like it is now possible.  I'm not sure what an
> appropriate improvement would be here.

That would just mean that query_start would be older than xact_start,
but that's okay because the displayed query would be a CALL so we'll
know what's going on.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Reply | Threaded
Open this post in threaded view
|

Re: xact_start meaning when dealing with procedures?

Peter Eisentraut-6
On 09/08/2018 20:25, Vik Fearing wrote:

> On 09/08/18 20:13, Peter Eisentraut wrote:
>> On 09/08/2018 19:57, hubert depesz lubaczewski wrote:
>>> I just noticed that when I called a procedure that commits and rollbacks
>>> - the xact_start in pg_stat_activity is not updated. Is it intentional?
>>
>> It's an artifact of the way this is computed.  The reported transaction
>> timestamp is the timestamp of the first top-level statement of the
>> transaction.  This assumes that transactions contain statements, not the
>> other way around, like it is now possible.  I'm not sure what an
>> appropriate improvement would be here.
>
> That would just mean that query_start would be older than xact_start,
> but that's okay because the displayed query would be a CALL so we'll
> know what's going on.

Note that this issue already exists for other commands that start
transactions internally, such as VACUUM and CREATE INDEX CONCURRENTLY.
At the moment, one should interpret xact_start as referring to the
top-level transaction only.

In practice, I think the value of xact_start versus query_start is to
anayze idle transactions.  This doesn't happen with internal
transactions, so it's not a big deal in practice.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: xact_start meaning when dealing with procedures?

Jonathan S. Katz-3

On Aug 10, 2018, at 4:39 AM, Peter Eisentraut <[hidden email]> wrote:

On 09/08/2018 20:25, Vik Fearing wrote:
On 09/08/18 20:13, Peter Eisentraut wrote:
On 09/08/2018 19:57, hubert depesz lubaczewski wrote:
I just noticed that when I called a procedure that commits and rollbacks
- the xact_start in pg_stat_activity is not updated. Is it intentional?

It's an artifact of the way this is computed.  The reported transaction
timestamp is the timestamp of the first top-level statement of the
transaction.  This assumes that transactions contain statements, not the
other way around, like it is now possible.  I'm not sure what an
appropriate improvement would be here.

That would just mean that query_start would be older than xact_start,
but that's okay because the displayed query would be a CALL so we'll
know what's going on.

Note that this issue already exists for other commands that start
transactions internally, such as VACUUM and CREATE INDEX CONCURRENTLY.
At the moment, one should interpret xact_start as referring to the
top-level transaction only.

In practice, I think the value of xact_start versus query_start is to
anayze idle transactions.  This doesn't happen with internal
transactions, so it's not a big deal in practice.

This was added as an open item by Michael[1]. When the RMT discussed,
we were able to make arguments both ways (i.e. adjusting the behavior vs.
not).

Peter, from your analysis it sounds like we should leave it, but I wanted to
confirm before removing the open item.

Thanks,

Jonathan


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: xact_start meaning when dealing with procedures?

Michael Paquier-2
On Wed, Aug 15, 2018 at 06:23:40PM -0400, Jonathan S. Katz wrote:
> This was added as an open item by Michael[1]. When the RMT discussed,
> we were able to make arguments both ways (i.e. adjusting the behavior vs.
> not).
>
> Peter, from your analysis it sounds like we should leave it, but I wanted to
> confirm before removing the open item.

FWIW, I am fine to stick with Peter's judgement.  I added it as an open
item to actually have the discussion as I was not sure about the
intention with the feature.
--
Michael

signature.asc (849 bytes) Download Attachment
Previous Thread Next Thread