Thread: equivalent of @@TRANCOUNT PostgreSQL

@@TRANCOUNT: Transaction count.

How many transactions are open at the moment.

One transaction per process actively executing a query.

<dave@davebolt.co.uk> writes:
> @@TRANCOUNT: Transaction count.
> How many transactions are open at the moment.
> I’m not seeing anything that looks appropriate. As far as I can see it might be possible to do something using the
StatisticsCollector, see https://www.postgresql.org/docs/13/monitoring-stats.html 

Yeah, something like this should do for that:

select count(*) from pg_stat_activity where state is not null;

            regards, tom lane

MichaelDBA <MichaelDBA@sqlexec.com> writes:
> I don't think so, it's not current active transactions, but number of 
> active transactions on the CURRENT CONNECTION.

Isn't that a constant 1?  We don't do multiple active transactions
in one session.

            regards, tom lane

On Sat, May 29, 2021 at 03:34:41PM +0100, dave@davebolt.co.uk wrote:
> @@TRANCOUNT: Transaction count.
> 
> How many transactions are open at the moment.
> 
> I’m not seeing anything that looks appropriate. As far as I can see it might be
> possible to do something using the Statistics Collector, see https://
> www.postgresql.org/docs/13/monitoring-stats.html
> 
> Hope someone knowledgeable on PostgreSQL will have an answer.
> 
> I did find this from 2006. https://www.postgresql.org/message-id/
> eg5op3$ria$1@sea.gmane.org

Uh, the only way I know to do that is to query pg_stat_activity like
this:

    SELECT *
    FROM pg_stat_activity
    WHERE state != 'idle' AND backend_type = 'client backend';

However, the purpose of why you are using these values probably is not
going to translate to Postgres easily.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

mustafa.pekgoz@forenda.com.tr schrieb am 29.05.2021 um 15:07:
> I have a procedure where they have used @@TRANCOUNT , is there an
> equivalent of @@TRANCOUNT in PostgreSQL? (Except Savepoint)
The equivalent in Postgres is

    SELECT 1

This is sooooo funny how this thread has been progressing.  The purpose 
for TRANCOUNT is for applications to track the state of transactions for 
a given connection to the database. TRANCOUNT keeps track of open 
transactions and is intented for usage in MSSQL server since 
transactions  are handled differently there.  In PG we don't have this 
"stacked" transactions scenario unit PG 11 where we started to support 
procedures that can call other procedures and hence we can have a 
stacked list of open transactions.  But still, TRANCOUNT in PG is 
basically useless, so when you convert from MS SQL to PG, just throw the 
damn thing away!

Regards,
Michael Vitale



Thomas Kellerer wrote on 5/29/2021 2:15 PM:
> mustafa.pekgoz@forenda.com.tr schrieb am 29.05.2021 um 15:07:
>> I have a procedure where they have used @@TRANCOUNT , is there an
>> equivalent of @@TRANCOUNT in PostgreSQL? (Except Savepoint)
> The equivalent in Postgres is
>
>    SELECT 1
>
>
>
>