pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDBeaver session populating pg_stat_activity.backend_xmin
6+ messages / 4 participants
[nested] [flat]
* DBeaver session populating pg_stat_activity.backend_xmin
@ 2023-01-25 19:36 Dirschel, Steve <steve.dirschel@thomsonreuters.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dirschel, Steve @ 2023-01-25 19:36 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Not sure if anyone on this list may have ideas on this or not. I will also try and find a DBeaver e-mail list and send to that also.
We are running into a problem where users will connect to a database through DBeaver. I have found if I leave all DBeaver options as default this issue does not happen. But I have found if I edit my connection under "Connection settings", "Initialization" and if I uncheck Auto-commit and then under "Connection settings", "Metadata" if I unselect "Open separate connection for metadata read" I can duplicate the issue.
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. I don't know if changing those 2 default settings is the only way to get the session to get like this but it does mimic the problem. The problem is users will connect using DBeaver and their sessions will sit idle. We have a table with high update activity and I will start seeing queries hitting this table do more and more logical reads because autovacuum is not able to cleanup records newer than the backend_xmin for those session. We have told users not to leave their sessions sitting idle like that and are also considering setting idle_in_transaction_session_timeout to kill sessions that sit like this.
But I am trying to understand what DBeaver is doing to get a session into that state.
I've set log_statement to ALL and did a login through DBeaver with the 2 settings changed to see what all it is executing. I then changed the DBeaver settings back to default so I could see what it executes upon connect without having xact_start and backend_xmin populated.
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. The log info is below with <=================================== next to the BEGIN/COMMIT's that happen with the non-default settings.
I try and duplicate this in psql and cannot. In psql I do a:
\set AUTOCOMMIT off
I then run the exact same commands as log_statements = ALL shows DBeaver runs without a COMMIT at the end of the last statement (those are shown below). I can find that psql session in pg_stat_activity and xact_start is populated but backend_xmin is NOT populated. Under that scenario it would not cause the problem of autovacuum not being able to cleanup this heavy update table due to a session sitting out there with backend_xmin populated.
Any ideas why I cannot duplicate this in psql if I'm running what appears to be the exact same commands DBeaver is? Is it possible DBeaver is running something else that does not get written to the log file when log_statements is set to all? One slight difference in my psql test was a couple of the queries below were using bind variables when executed through DBeaver. I put the literal values in the query when I executed through psql. Is it possible that somehow using bind variables with autocommit off, using BEGIN causes backend_xmin to get populated?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DBeaver session populating pg_stat_activity.backend_xmin
@ 2023-01-25 19:56 Thomas Kellerer <shammat@gmx.net>
parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2023-01-25 19:56 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 statements during the "log in".
And the first query will start a transaction, but as autocommit is disabled, 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 sent through the JDBC driver, the driver will
send a BEGIN to start the transaction, but the the application (or the user) is responsible to end it through a COMMIT
(or ROLLBACK).
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [EXT] Re: DBeaver session populating pg_stat_activity.backend_xmin
@ 2023-01-25 22:21 Dirschel, Steve <steve.dirschel@thomsonreuters.com>
parent: Thomas Kellerer <shammat@gmx.net>
0 siblings, 2 replies; 6+ messages in thread
From: Dirschel, Steve @ 2023-01-25 22:21 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
>> 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 statements during the "log in".
>And the first query will start a transaction, but as autocommit is disabled, nothing will end that transaction.
Yes, I agree with that. And if I set autocommit off in psql I see the same where a select will start a transaction.
>> The problem is users will connect using DBeaver and their sessions
>> will sit idle.
>Idle is not a problem, "idle in transaction" is.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [EXT] Re: DBeaver session populating pg_stat_activity.backend_xmin
@ 2023-01-25 22:34 Ron <ronljohnsonjr@gmail.com>
parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ron @ 2023-01-25 22:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 1/25/23 16:21, Dirschel, Steve wrote:
[snip]
>>> The problem is users will connect using DBeaver and their sessions
>>> will sit idle.
>> Idle is not a problem, "idle in transaction" is.
> From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions sitting like that for a long time).
Well, it is, since you /are/ sitting in an open transaction.
This fixes it: idle_in_transaction_session_timeout
https://www.postgresql.org/docs/9.6/runtime-config-client.html
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [EXT] DBeaver session populating pg_stat_activity.backend_xmin
@ 2023-01-25 22:49 Christophe Pettus <xof@thebuild.com>
parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
1 sibling, 1 reply; 6+ messages in thread
From: Christophe Pettus @ 2023-01-25 22:49 UTC (permalink / raw)
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> On Jan 25, 2023, at 14:21, Dirschel, Steve <steve.dirschel@thomsonreuters.com> wrote:
> From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions sitting like that for a long time). The problem is when pg_stat_activity.backend_xmin is populated- that can prevent autovacuum from cleaning up old records.
I don't think that analysis is quite correct. There's nothing about backend_xmin that blocks tuple cleanup *if the transaction has been committed*. The problem is that the session is sitting in idle in transaction state, and *that* blocks tuple cleanup. The fix is to not leave sessions open in idle in transaction state.
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [EXT] DBeaver session populating pg_stat_activity.backend_xmin
@ 2023-01-31 16:29 Dirschel, Steve <steve.dirschel@thomsonreuters.com>
parent: Christophe Pettus <xof@thebuild.com>
0 siblings, 0 replies; 6+ messages in thread
From: Dirschel, Steve @ 2023-01-31 16:29 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi Christophe,
Thanks for the reply. I am fairly new to Postgres and based on your reply below I am not understanding something. Please see my test case below. I can show where a session is "idle in transaction" but backend_xmin is NOT populated and I show vacuuming a table with 10 dead rows will work fine. I then show a session is "idle in transaction" with backend_xmin populated and vacuuming a table with 10 dead rows will not work because of that session having backend_xmin populated. Note in all cases here these sessions have not executed any DML. Based on your reply below in both of these cases the vacuum should not have been able to cleanup these 10 dead rows. What am I missing here?
Regards
Steve
> -----Original Message-----
> From: Christophe Pettus <xof@thebuild.com>
> Sent: Wednesday, January 25, 2023 4:49 PM
> To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
> Cc: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org
> Subject: Re: [EXT] DBeaver session populating pg_stat_activity.backend_xmin
> > On Jan 25, 2023, at 14:21, Dirschel, Steve <steve.dirschel@thomsonreuters.com> wrote:
> > From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions sitting like that for a long time). The problem is when pg_stat_activity.backend_xmin is populated- that can prevent autovacuum from cleaning up old records.
> I don't think that analysis is quite correct. There's nothing about backend_xmin that blocks tuple cleanup *if the transaction has been committed*. The problem is that the session is sitting in idle in transaction state, and *that* blocks tuple cleanup. The fix is to not leave sessions open in idle in transaction state.
Test case:
create table test1(a numeric, b numeric);
***********
* TEST #1 *
***********
Dbeaver setup:
Connection setting, Initialization, Auto-commit not checked
Connection setting, Metadata, Open separate connection for metadata read is checked.
Connect to the database through DBeaver. Here are the 2 sessions:
select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
dbtest=> select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
pid | application_name | xact_start | backend_xmin | state | query
-------+------------------------------------+------------+--------------+-------+----------------------------------------------------------------------------------------------------------------
30229 | DBeaver 22.0.0 - Main <dbtest> | | | idle | SHOW TRANSACTION ISOLATION LEVEL
30231 | DBeaver 22.0.0 - Metadata <dbtest> | | | idle | SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+
| | | | | FROM pg_catalog.pg_type t +
| | | | | LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem +
| | | | | LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid +
| | | | | LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid +
| | | | | WHERE t.typname IS NOT NULL +
| | | | | AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')
*** Note neither session is in a transaction. State for both is idle.
Leave the 2 DBeaver sessions as is. In psql I run this:
dbtest=> do $$
dbtest$> begin
dbtest$> for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$> end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO: vacuuming "public.test1"
INFO: table "test1": removed 10 dead item identifiers in 1 pages
INFO: table "test1": found 10 removable, 0 nonremovable row versions in 1 out of 1 pages <------------------- the 10 dead rows are removable and removed. This is expected.
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368520
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table "test1": truncated 1 to 0 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pg_toast.pg_toast_49187"
INFO: table "pg_toast_49187": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368521
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
***********
* TEST #2 *
***********
Leave DBeaver config as is. Now in DBeaver I open the SQL Console and start a transaction by running BEGIN;
Look at sessions now:
dbtest=> select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
pid | application_name | xact_start | backend_xmin | state | query
-------+--------------------------------------+-------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------
30229 | DBeaver 22.0.0 - Main <dbtest> | | | idle | SHOW TRANSACTION ISOLATION LEVEL
30231 | DBeaver 22.0.0 - Metadata <dbtest> | | | idle | SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+
| | | | | FROM pg_catalog.pg_type t
| | | | | LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
| | | | | LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
| | | | | LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
| | | | | WHERE t.typname IS NOT NULL
| | | | | AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')
31163 | DBeaver 22.0.0 - SQLEditor <Console> | 2023-01-31 10:05:16.125645-06 | | idle in transaction | SHOW search_path
(3 rows)
*** Now we see the SQL Editor session with xact_start populated, state is idle in transaction, but backend_xmin is NOT populated.
Run the test again with sessions in that state:
dbtest=> do $$
dbtest$> begin
dbtest$> for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$> end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO: vacuuming "public.test1"
INFO: table "test1": removed 10 dead item identifiers in 1 pages
INFO: table "test1": found 10 removable, 0 nonremovable row versions in 1 out of 1 pages <------------------- again, finds 10 dead rows which are removable and it removes them. I believe you are saying the session that is idle in transaction should have blocked this from cleaning those up.
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368826
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: table "test1": truncated 1 to 0 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: vacuuming "pg_toast.pg_toast_49187"
INFO: table "pg_toast_49187": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19368827
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
***********
* TEST #3 *
***********
Close my DBeaver connections.
Dbeaver setup:
Connection setting, Initialization, Auto-commit not checked
Connection setting, Metadata, Open separate connection for metadata read is NOT checked.
Connect to the database through DBeaver. Here are the 2 sessions:
dbtest=> select pid, application_name, xact_start, backend_xmin, state, query from pg_stat_activity where usename = 'postgres' and application_name like 'DBeaver%';
pid | application_name | xact_start | backend_xmin | state | query
-------+--------------------------------+------------------------------+--------------+---------------------+---------------------------------------------------------------------------------------------------
-------------
31421 | DBeaver 22.0.0 - Main <dbtest> | 2023-01-31 10:09:08.35667-06 | 19369010 | idle in transaction | SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) as base_type_name, d.description+
| | | | | FROM pg_catalog.pg_type t
| | | | | LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem
| | | | | LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
| | | | | LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
| | | | | WHERE t.typname IS NOT NULL
| | | | | AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR et.typcategory <> 'C')
(1 row)
*** Now I only see 1 DBeaver session. xact_start is populated, state is idle in transaction. But now backend_xmin IS populated. That is the key (at least in my opinion) to show the problem where backend_xmin is populated.
Now run the test again:
dbtest=> do $$
dbtest$> begin
dbtest$> for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$> end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO: vacuuming "public.test1"
INFO: table "test1": found 0 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL: 10 dead row versions cannot be removed yet, oldest xmin: 19369010 <------------------- now these 10 rows are nonremovable due to the oldest xmin from the session above.
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: vacuuming "pg_toast.pg_toast_49187"
INFO: table "pg_toast_49187": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 19369010
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-01-31 16:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-25 19:36 DBeaver session populating pg_stat_activity.backend_xmin Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2023-01-25 19:56 ` Thomas Kellerer <shammat@gmx.net>
2023-01-25 22:21 ` Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2023-01-25 22:34 ` Ron <ronljohnsonjr@gmail.com>
2023-01-25 22:49 ` Christophe Pettus <xof@thebuild.com>
2023-01-31 16:29 ` Dirschel, Steve <steve.dirschel@thomsonreuters.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