c# - How to count number of BEGIN TRANSACTION statements in PostgreSQL?

Question

using (DbConnection dbConn = myContext.Database.GetDbConnection()) {
                        using (DbCommand dbCommand = dbConn.CreateCommand()) {
                            dbCommand.CommandText = "SELECT @@TRANCOUNT";
                            dbCommand.Transaction = transaction.GetDbTransaction();
                            return (int)dbCommand.ExecuteScalar();
                        }
                    }

The code above is used with Microsoft SQL Server to get number of BEGIN TRANSACTION statements.

What is the equivalent command for PostgreSQL? Does PostgreSQL support such method?

Thanks.

Answer 1

SQL Server and PG works very differently. MS SQL Server support nested transaction, so the count of BEGIN TRAN is very important to know the nest level while PostGreSQL does not support nested transaction. So there is absolutly no equivalent of @@TRANCOUNT in PostGreSQL.

Whatever how many BEGIN TRANSACTION you will execute in PostGreSQL, the first time a finalization of transaction is initiate by a COMMIT or ROLLBACK, the transaction is ended immeditaly, which is not the case in SQL Server that use the asymetric model of nested transaction (The first ROLLBACK ends the transaction by a cancel while the last COMMIT - in equivalent number of BEGIN TRAN - ends the transaction and accepts finaly all the databases modifications).

Many other functionalities are completly different between PostGreSQL and SQL Server and I am writing a series of papers about those differences. The first one is about performances of DBA queries, the secound about COUT performances and the third a complete panorama of functional differences will arrive in few days or weeks...