pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedequivalent of @@TRANCOUNT PostgreSQL
11+ messages / 9 participants
[nested] [flat]
* equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 13:07 mustafa.pekgoz@forenda.com.tr
0 siblings, 2 replies; 11+ messages in thread
From: mustafa.pekgoz@forenda.com.tr @ 2021-05-29 13:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
I have a procedure where they have used @@TRANCOUNT , is there an equivalent
of @@TRANCOUNT in PostgreSQL? (Except Savepoint)
Saygýlarýmla.
---
Mustafa Pekgöz
Co-Founder, Principal Database Consultant
Tel : +90 534 429 76 83
E-mail : <mailto:mustafa.pekgoz@forenda.com.tr>
mustafa.pekgoz@forenda.com.tr
Website: <http://www.forenda.com.tr/; www.forenda.com.tr
Adres : Barbaros Mah. Begonya Sok. Nidakule No: 1/2 Batý Ataþehir / ÝSTANBUL
Attachments:
image001.jpg (image/jpeg, 3.3K) download
view image
image002.png (image/png, 2.9K) download
view image
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 13:25 Julien Rouhaud <rjuju123@gmail.com>
parent: mustafa.pekgoz@forenda.com.tr
1 sibling, 1 reply; 11+ messages in thread
From: Julien Rouhaud @ 2021-05-29 13:25 UTC (permalink / raw)
To: mustafa.pekgoz@forenda.com.tr; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sat, May 29, 2021 at 9:07 PM <mustafa.pekgoz@forenda.com.tr> wrote:
> Hello,
>
>
>
> I have a procedure where they have used @@TRANCOUNT , is there an
> equivalent of @@TRANCOUNT in PostgreSQL? (Except Savepoint)
>
What does @@TRANCOUNT do?
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 14:34 dave@davebolt.co.uk
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: dave@davebolt.co.uk @ 2021-05-29 14:34 UTC (permalink / raw)
To: 'Julien Rouhaud' <rjuju123@gmail.com>; mustafa.pekgoz@forenda.com.tr; +Cc: 'Pgsql-admin' <pgsql-admin@lists.postgresql.org>
@@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
HTH.
From: Julien Rouhaud [mailto:rjuju123@gmail.com]
Sent: 29 May 2021 14:26
To: mustafa.pekgoz@forenda.com.tr
Cc: Pgsql-admin
Subject: Re: equivalent of @@TRANCOUNT PostgreSQL
On Sat, May 29, 2021 at 9:07 PM <mustafa.pekgoz@forenda.com.tr> wrote:
Hello,
I have a procedure where they have used @@TRANCOUNT , is there an equivalent of @@TRANCOUNT in PostgreSQL? (Except Savepoint)
What does @@TRANCOUNT do?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 14:44 David G. Johnston <david.g.johnston@gmail.com>
parent: dave@davebolt.co.uk
2 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2021-05-29 14:44 UTC (permalink / raw)
To: dave@davebolt.co.uk <dave@davebolt.co.uk>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; mustafa.pekgoz@forenda.com.tr <mustafa.pekgoz@forenda.com.tr>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Saturday, May 29, 2021, <dave@davebolt.co.uk> wrote:
> @@TRANCOUNT: Transaction count.
>
> How many transactions are open at the moment.
>
>
pg_stat_activity could be used for this. One transaction per process
actively executing a query.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 14:50 MichaelDBA <MichaelDBA@sqlexec.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: MichaelDBA @ 2021-05-29 14:50 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: dave@davebolt.co.uk <dave@davebolt.co.uk>; Julien Rouhaud <rjuju123@gmail.com>; mustafa.pekgoz@forenda.com.tr <mustafa.pekgoz@forenda.com.tr>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
I don't think so, it's not current active transactions, but number of
active transactions on the CURRENT CONNECTION. No way you get that from
pg_stat_activity. So if your proc had a bunch of embedded BEGIN/END
blocks, it would be that count.
David G. Johnston wrote on 5/29/2021 10:44 AM:
> One transaction per process actively executing a query.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 14:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: dave@davebolt.co.uk
2 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2021-05-29 14:51 UTC (permalink / raw)
To: dave@davebolt.co.uk; +Cc: 'Julien Rouhaud' <rjuju123@gmail.com>; mustafa.pekgoz@forenda.com.tr, "'Pgsql-admin'" <pgsql-admin@lists.postgresql.org>
<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 Statistics Collector, 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 15:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2021-05-29 15:01 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; dave@davebolt.co.uk <dave@davebolt.co.uk>; Julien Rouhaud <rjuju123@gmail.com>; mustafa.pekgoz@forenda.com.tr <mustafa.pekgoz@forenda.com.tr>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 15:08 Bruce Momjian <bruce@momjian.us>
parent: dave@davebolt.co.uk
2 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2021-05-29 15:08 UTC (permalink / raw)
To: dave@davebolt.co.uk; +Cc: Julien Rouhaud <rjuju123@gmail.com>; mustafa.pekgoz@forenda.com.tr, 'Pgsql-admin' <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 18:15 Thomas Kellerer <shammat@gmx.net>
parent: mustafa.pekgoz@forenda.com.tr
1 sibling, 1 reply; 11+ messages in thread
From: Thomas Kellerer @ 2021-05-29 18:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-29 22:28 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 11+ messages in thread
From: MichaelDBA @ 2021-05-29 22:28 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
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
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: equivalent of @@TRANCOUNT PostgreSQL
@ 2021-05-30 14:04 Thomas Carroll <tomfecarroll@yahoo.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 11+ messages in thread
From: Thomas Carroll @ 2021-05-30 14:04 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
This may or may not be helpful: txid_current_if_assigned().
Sometimes @@trancount is used to see if the current context is within a transaction - without regard to depth. If that is your case maybe this can help.
SELECT * FROM txid_current_if_assigned();
txid_current_if_assigned
--------------------------
NULL
(1 row)
BEGIN;
BEGIN
CREATE TABLE delete_me (x INTEGER NOT NULL);
CREATE TABLE
SELECT * FROM txid_current_if_assigned();
txid_current_if_assigned
--------------------------
656442
(1 row)
ROLLBACK;
ROLLBACK
SELECT * FROM txid_current_if_assigned();
txid_current_if_assigned
--------------------------
NULL
(1 row)
Note that I had to actually make a modification to get the transaction ID to be assigned. With MSSQL, @@trancount gets set to 1 right away when you open a transaction - without you having to modify anything.
Tom
On Saturday, May 29, 2021, 6:28:47 PM EDT, MichaelDBA <michaeldba@sqlexec.com> wrote:
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
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2021-05-30 14:04 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-29 13:07 equivalent of @@TRANCOUNT PostgreSQL mustafa.pekgoz@forenda.com.tr
2021-05-29 13:25 ` Julien Rouhaud <rjuju123@gmail.com>
2021-05-29 14:34 ` dave@davebolt.co.uk
2021-05-29 14:44 ` David G. Johnston <david.g.johnston@gmail.com>
2021-05-29 14:50 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-05-29 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-29 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-29 15:08 ` Bruce Momjian <bruce@momjian.us>
2021-05-29 18:15 ` Thomas Kellerer <shammat@gmx.net>
2021-05-29 22:28 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-05-30 14:04 ` Thomas Carroll <tomfecarroll@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox