Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lnM3f-0005QV-IE for pgsql-admin@arkaria.postgresql.org; Sun, 30 May 2021 14:04:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lnM3c-0003Zz-3i for pgsql-admin@arkaria.postgresql.org; Sun, 30 May 2021 14:04:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lnM3b-0003Zq-Hh for pgsql-admin@lists.postgresql.org; Sun, 30 May 2021 14:04:47 +0000 Received: from sonic301-2.consmr.mail.bf2.yahoo.com ([74.6.129.41]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lnM3Y-0005tf-K0 for pgsql-admin@lists.postgresql.org; Sun, 30 May 2021 14:04:46 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1622383481; bh=qgUhZvzeApVb2EWpdOBHl8HB6tLSziwYehm87lhu8Ks=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=NyocVBFjF7W0y5q3hXFnGg4B5sGbZKTqouvcE5kD17LiCc9h1N4BPhNFHV2lUYoydYuaRJiauZI8Hrk27eMT6kQURpSD8uF8IoBTxeUMCY8ikLMwTUqjVnu6jI2dxD7nh2WVYTEuqb8FkPEDDdTIXRtshGH86mAW96Fd5x0s/AiEuQWyLvXfN0riz1t22WrbozTdZ2lqrCmqrC0fpV5gbYAD7PjgOtAhUM99XhY99AAQ8wHZ6MelggnEEVN8wwPcHMf6cMZ/7zeggO9Fu2ciDnq57w0ifLnFQi8HIqAXoyVFWUxkJakRQF2IDXJr3N6ya9QiCQ8RxWI+T9aahVjb7A== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1622383481; bh=y/Mg4SWbXFeafKtF2AMMeK+eyQwp+hVP3t9AcdBruNT=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=L0PiKKhptKKbvnKIlksPiTt0Vd2ixsQ0rrlqFVd8szXpa/bLPw4gRfFNs8v5r7lSNqY1hGH+8N5SiBjMbRxeHbkpUfMGkbbblwSElPF7FqmlSe5MWe6PxVjM/tn0nyWT+xlKrJilh0YDGGIjRWOhxsHhFtq2WQHZA1kO7pFjvjmOm7Xt7qVKvf94TD3vU8SZCeo+NDUsMLx0pbsHDIklyCZPkibzL8n1KfjAfZntmkRm2gVTjZGfLdYCzoCs5hy2fRZSxgR8cPnK28y857GcUu4sVzHFxF/gpaILUtPePdrGIRtFsyhmL5+TDLsDth8fbYg71SWYwndwBGaxJ4G1+Q== X-YMail-OSG: xvcGkdcVM1lR5gPe7lWWsI8i709sPewibHDLRnZkYhmj2sPqUUBlyMQqrjpzwIs wU_d0UpebO2DMrj6q_V9ANpsiU50jhSbXwnlD9J0oWMgh6OzJqmWKwpccfEN8UbtJLZawi4kfTHJ 0nW8d2X3uSz6wZ1vx_5wPUpAldmXCSmL4Pl.llMSA7FB4ivOcOZYE0PwlMKM6Hgwz7x1t2GU9Ct0 DtoOQeRYKsGDc284QU90uWuuW.MU1witF9Wa90UxZVjs7XiUlgnitKTeQSPWemE2HhV3CGnAxeBf Fuy6cLShvT2Pqb8AsmTyJmDzZ1NuMWM8lwit0hgPg4sdAiKp5Mravjh3275K83WpWOvj9bGzv_VP bp4eEaPyBUPNmrh8JJfjB2U4WaT3.oK3JEB7QOo3xSY6XNG1fOcKu6Kpl4ehdXraAqnUbo2vrnFx jaoSjFa6HoKzOfKUwmCxn7X_Ya3OqOrV5mmkN8qg.WGL4h9ons_r2qB93qulcebNsILY7Vi8q6pk VwH0odwmXyGr62Bhf.Q93601aXBWptj5QTRhvcHw.QyFikfrBOvcnGyZW8JDazw_5ZaD25WR.GPK cI2a9yFn7T.X1hV9b_hVUvYOc2v3Eo57eu9nTE15GEo9Aek.hx4DvwlXWlB5FmKXtZc3DvSt1lhz a7u9SwQnuloKoPxlGJwgRtQOOxL0v7eLbzJ_Nz9p19P7u5MIAM0e8J7WVViK5xU1K.AGanE1dazJ fshO.gWyCED61U4Y64QoBspaov97W1A_aZbuMWUD5fZ0GZz2CWVXMk.kWIrmqDbIHB8aQBX8Mt8f _bncWo2EgPT0kJOkcVlYvhpvRXnxOxdMgCtJu1Db.u9POoY9TmKEmdXGS80cjN8TeRIXsDnoy9J0 evgw0WZDEcfVP8Ghlp3M2sgg1zUcrjJ_DSiR8ZSfL0Dpk5vEnoMn0uepQGcnInhIGOVTknE3SR0w EUwUTFUhqEPGfkUkt1rNN1sK2FgO77goCXxbyr5gPXYOIe64maNCjifXOwshesdj2bWab2wbTCg. .M4PcWvAIHzJ0VcVj4xCVBrwAVFRK7IP_b5psogYDiGL.934rDymVSUbBaaDzbl6W0uoWfvUQVBQ X0SM5QHcMCwFHskmtRwcXnLoT1P5461dk_AhiWdLmlzjBYEXBEVueipUVPCtgIPQPliY5PYvGDcU 5W4kVZSNpK2QpMtNWDpDMWJUA5f3vRPupu1n4rPQvamcVxCaoHywRIjZFOJHK0UO513SIk4JS2Uy 0k0MQICeWzUB8hbr3.Tf46sYPr_G0SU36KtLvJ_cIpuspLbu_CszjywYIPgbCvnyr4m0M6z1j2Yi lg1lHvARJaQFdJLPBdG_5n7Qi.4mInNmJ5BMHREOecKzNeYZJp3p.WOWUyWDMmL3jAT5..lMlPwd cX8_hI0IU_Ns88LTNoIqglxMsrM.R7BK5Zp901uaO_Xb8.8Ftmrxvn__nJnu77QSboMvrjADjnU0 tRgdZvIYzeBN950mU1sMN5KID.2CrASLKSO1ae6zKyqzuQmb2IGtWWBS3e.ub33lA7fnsQN082Wd BPTrYudBuQXoe3HnubLumXcUz9wpJpCSjWp4mk4ynsH4wCmUuAip6BvQ7_1.A2nbwpUxlXkyCA3x hMXv5CzOB8WE3Xz4M34bmEi9TmzbsRuvIkWMT4ANdwsb8q5ApSizl5N2LjNrJ30k_YYInGfuJ4F9 oYEpNj7pD661UmHCIugSQ9mJyMbKBLP4UpKiZ3ojX9OJPfRB1l5961Bs6mwG6TARumRV3zyVgP5R mFdR8aqiKdW5w4Q_qmDP9T8VMXn4xYB18iXEwzlTFqohlbzDwvGz6RafMapUfZlyibnp62tvKjws 4plhdCAIFXDAFIJa.cq8Um6jusE_XLcwTLVmLhnaHw71hX.iqybNqWtCJpy4Kk0siIg1GndU_Hrf NDeqOrsXaYWJdgH1sFcDrwThtRMkONQxzktVc_g0zFveQAGckpB21d76_BnyKXpzWSn0LchGoNbe HZ2EQBuJjP0mlE8UDcWi2p7i5x2dP84smjxMVVj25jBD8X_.kaxRJZVSioTHqdRwiB315iFu2wM5 SSgURvS_mrKRvNPRIWptQmXLFFvKQPyyflOX3FhKP.9CGmVDSB.6tgI2D7KK2HtmxRH73f8ghq8N uVHhatP3J18.0WlAy1oeHb.YOcBn7YjD5hQdW8FIq0afdMxFE_AhkdR4eGVZlQwfeqZmIUdylfCe xb.uXDSfb.pdP6vqNUd2ZNgK2N21KkEo2JMdng6kxDqkMFRFdTuf8oIbcIZwbJ.NcGiu6XEftLog 9yOZB6kI5L1KP.Y9pwDvpQlxwmvX23w7f901DkHJLiKJL3MWnUdnUsXqz5qKov.QHGI9W.gQLW65 x17fZxBS8stkPFSZFxx4Dzsx062izb_AUxRoKi_fX4ukialItYpQHJP9aHRr5DDSATDuFf9F5jgs C5RXltuo6ZwAbG8CNBXFfOvx_t.4CrZ8sD8vz.bwMEW5noxV35EvjH9kAOsBt0q4xEhWpYWybNlB 4strWNjTUn3u6gnO2hCCs482oupVR978NW9acfdt5lLf6E0CehTrwTCj02yFHRg9b0leIMnEcr9o qhU18b3YtlgoVgWef409HTfS4iZdjIA-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.bf2.yahoo.com with HTTP; Sun, 30 May 2021 14:04:41 +0000 Date: Sun, 30 May 2021 14:04:40 +0000 (UTC) From: Thomas Carroll To: Thomas Kellerer , MichaelDBA Cc: "pgsql-admin@lists.postgresql.org" Message-ID: <304119123.793476.1622383480750@mail.yahoo.com> In-Reply-To: References: <004101d7548b$894e0ce0$9bea26a0$@forenda.com.tr> <445011bb-b76d-9174-aebe-f6727944054d@gmx.net> Subject: Re: equivalent of @@TRANCOUNT PostgreSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_793475_502959236.1622383480749" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 6944 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_793475_502959236.1622383480749 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable This may or may not be helpful: txid_current_if_assigned(). Sometimes @@trancount is used to see if the current context is within a tra= nsaction - without regard to depth.=C2=A0 If that is your case maybe this c= an help. SELECT * FROM txid_current_if_assigned(); =C2=A0txid_current_if_assigned=20 -------------------------- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL (1 row) BEGIN; BEGIN CREATE TABLE delete_me (x INTEGER NOT NULL); CREATE TABLE SELECT * FROM txid_current_if_assigned(); =C2=A0txid_current_if_assigned=20 -------------------------- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 656442 (1 row) ROLLBACK; ROLLBACK SELECT * FROM txid_current_if_assigned(); =C2=A0txid_current_if_assigned=20 -------------------------- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NULL (1 row) Note that I had to actually make a modification to get the transaction ID t= o be assigned.=C2=A0 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 wrote: =20 =20 This is sooooo funny how this thread has been progressing.=C2=A0 The purpo= se=20 for TRANCOUNT is for applications to track the state of transactions for=20 a given connection to the database. TRANCOUNT keeps track of open=20 transactions and is intented for usage in MSSQL server since=20 transactions=C2=A0 are handled differently there.=C2=A0 In PG we don't have= this=20 "stacked" transactions scenario unit PG 11 where we started to support=20 procedures that can call other procedures and hence we can have a=20 stacked list of open transactions.=C2=A0 But still, TRANCOUNT in PG is=20 basically useless, so when you convert from MS SQL to PG, just throw the=20 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 > > =C2=A0=C2=A0 SELECT 1 > > > > =20 ------=_Part_793475_502959236.1622383480749 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
This may or may not be helpf= ul: txid_current_if_assigned().

Sometime= s @@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
--------------------------
 &n= bsp;            = ;       NULL
(1 row)

BEGIN;

= BEGIN

CREATE TABLE delete_me (x INTEGER NOT NULL);

CREATE TAB= LE

SELECT * FROM txid_current_if_assigned();

 txid_curre= nt_if_assigned
--------------------------
    &n= bsp;            = ;  656442
(1 row)

ROLLBACK;

ROLLBACK

SELECT * = FROM txid_current_if_assigned();

 txid_current_if_assigned
= --------------------------
       &nb= sp;            = NULL
(1 row)

Note that I had to actu= ally make a modification to get the transaction ID to be assigned.  Wi= th MSSQL, @@trancount gets set to 1 right away when you open a transaction = - without you having to modify anything.

Tom
=20
On Saturday, May 29, 2021, 6:28:47 PM EDT, MichaelDBA &= lt;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 un= it PG 11 where we started to support
procedures that can= call other procedures and hence we can have a
stacked l= ist 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 pr= ocedure where they have used @@TRANCOUNT , is there an
&g= t;> equivalent of @@TRANCOUNT in PostgreSQL? (Except Savepoint)
> The equivalent in Postgres is
>
>    SELECT 1
>
>
>
>


------=_Part_793475_502959236.1622383480749--