From: Thomas Carroll <tomfecarroll@yahoo.com>
To: Thomas Kellerer <shammat@gmx.net>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: equivalent of @@TRANCOUNT PostgreSQL
Date: Sun, 30 May 2021 14:04:40 +0000 (UTC)
Message-ID: <304119123.793476.1622383480750@mail.yahoo.com> (raw)
In-Reply-To: <fd404a59-fc9b-9465-e1b3-45dc7e5bad00@sqlexec.com>
References: <004101d7548b$894e0ce0$9bea26a0$@forenda.com.tr>
<445011bb-b76d-9174-aebe-f6727944054d@gmx.net>
<fd404a59-fc9b-9465-e1b3-45dc7e5bad00@sqlexec.com>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tomfecarroll@yahoo.com, shammat@gmx.net, MichaelDBA@sqlexec.com, pgsql-admin@lists.postgresql.org
Subject: Re: equivalent of @@TRANCOUNT PostgreSQL
In-Reply-To: <304119123.793476.1622383480750@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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