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 1pKlt4-000638-7V for pgsql-general@arkaria.postgresql.org; Wed, 25 Jan 2023 19:56:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pKlt3-0002Om-3I for pgsql-general@arkaria.postgresql.org; Wed, 25 Jan 2023 19:56:49 +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 1pKlt2-0002LH-O6 for pgsql-general@lists.postgresql.org; Wed, 25 Jan 2023 19:56:48 +0000 Received: from mout.gmx.net ([212.227.15.19]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pKlt0-0007A4-Nw for pgsql-general@lists.postgresql.org; Wed, 25 Jan 2023 19:56:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1674676605; bh=o3vy2pRr1fblo7f2OZ4z3veTvlqrIL/2A9qVOduIZ/Y=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=TOb6L6awwr9p4fK2ISY5tfmvR3efyhsiV5iMz2zGZdrmmvTxBmLKf50XGxiXiRp5X 0Lay8aNTneZ9KnQdEzK8cgu0pV1TLNVXLs0W8WLr6pT2a5wh3QE1TOYvp6h/4X1kVP wB2YY69TQIMRKK2xLFx60qo14sYNxwY+HgYHs+moJbSl6X/bp0PpzX+9rqk89XyGbi x3V4pFoQ5FOnpuGpO/SXHkhhO2ljucTy8hKmdz9O+vZLj+Y/1eoPUXjs1F3NxQ9joK pKwLjpduhu7Dj54m3oOkBOsBSJy2yKn9UaTMYi+Lqx43X5bnrDfcGbqaK/RtZ64d53 6GUAbSMieVmiQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [192.168.178.20] ([83.171.170.11]) by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1M9o1v-1pHDVg1CC5-005rtU for ; Wed, 25 Jan 2023 20:56:45 +0100 Subject: Re: DBeaver session populating pg_stat_activity.backend_xmin To: pgsql-general@lists.postgresql.org References: From: Thomas Kellerer Message-ID: Date: Wed, 25 Jan 2023 20:56:45 +0100 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=windows-1252; format=flowed Content-Language: de-DE Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:zmLtw8/l/XEG8EJwn+pxofEwTVrgqXSrIn4c9GAEDmH2OA3CIF1 grAefRsQSyXjt+IlJCbefyS9Xck9EScIfIyZR6MEO6zYjeoc/jIYOMWAnFeEWwbRF52Drod AiEwi74r5b6FhJ4vCL+BoLF7SWwhs+NaEgmM3+qkCNq+RzwcvjTIGeW9mkM9KeCFyQSNfvv 3BLJVwzZEoYVEgwCRnJzA== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:JxJ2VPpknGc=;n+1tkPYFMJiAZ3YXRr90qfGB0dT CSu28fGDDT1O3TXIqWfgkMYV7K4e6jEWhSra+de5x1JWjoR5JW9L03EGh2XyLT+MPo03Iahbk qJx5xLZaJeg17xw8mWbHAE1imYmOU/R1brnDjaRAEfJdtCEnSVzCz4aXoPk1eYguBBG4Fs2hs FoyzHgMCOAzIEmoxoVhAISqQiqz4BJaNX8p4/b5ij8KQWhqipQwNQJXDlQ1DmIGS6/pAljngp rjJenzZgMS/XfyvsYpLq2txcH62or4xdQX2XXB5tYfOlND6D+LFNHsJ9N1Q+Q5W4+CgTpZ1K5 jdGncjrwAxKFs/7hbLPGcycbW9cgvLA6plJdC8Pq1m1uUpK2LDlxuQ97+EgQmRijyFRJ1KFh/ 9IGh2yj95QgalWU+Fq1bKS9JSjgv9zzheDwmq52hLFHh7SQ2AQXAeRETzwvb6t8+egEVaD1K0 xbYmR4mD9dasQfu0pEfmJB1C9SR22/OUnFfGGMOmpi0kNXVkZ9A+CxTgUoQkAfcKimpb6VUzB iVtXmrJxkx70iCL8kltJjDgcW/fDe8HZtU7T9r7Y0wQOdeVs63FzYe0o4bpxHO/wW05N78uWX rZ83F5VHDFbou2LIqEu/0QR0U0BWWD3+bDyDIFjIEHEvMD/bSpylhwvUFTDq+8lYWwaf7vYev 4tYH+GtN/xv5RXBNrgdraEqKeogd/HnEYrBM5FjKx66t+/IzBb/yMUylix/MgU6itxUHP2vkZ g0fIFtJWhTSG3V4Yx/CDXSPA7oSS4tLfRBk510gpGDho2EWc0kDF5MoOCp+509KWIjB0Il4K5 hEiq2clcPkbH1WfWTHAQ97OoE/rGq/fhAEqREybshzioot9nAA4k6lOvOHJ597EHrGJjZXGV6 7oeWTBQlgAsAVoD0/Cxe0Ttm+PQ82PDBCrfDlbK4jALpi130COyaO0zUmngMY0IJK/zxyaZZC /bP4Dw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Dirschel, Steve schrieb am 25.01.2023 um 20:36: > When I connect to the database through DBeaver with those 2 default > settings changed and find that session in pg_stat_activity column > xact_start is populated along with backend_xmin. Those get > populated just by logging in. As you found out in the log, the driver runs DbEaver run multiple SQL stat= ements during the "log in". And the first query will start a transaction, but as autocommit is disable= d, nothing will end that transaction. > The problem is users will connect using DBeaver and their sessions > will sit idle. Idle is not a problem, "idle in transaction" is. > It executes the exact same commands except when the 2 default > DBeaver settings are changed to show the issue it is issuing a BEGIN > and COMMIT around one block of code and then at another part of code > it issues a BEGIN, runs some queries, and never issues a COMMIT. Yes, that's how turning off autocommit works. As soon as a statement is se= nt through the JDBC driver, the driver will send a BEGIN to start the transaction, but the the application (or the use= r) is responsible to end it through a COMMIT (or ROLLBACK).