public inbox for [email protected]
help / color / mirror / Atom feedRe: Query on Postgres SQL transaction
9+ messages / 3 participants
[nested] [flat]
* Re: Query on Postgres SQL transaction
@ 2024-03-19 16:03 Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-03-19 16:03 UTC (permalink / raw)
To: Bandi, Venkataramana - Dell Team <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote:
> Hi Greg,
>
> We are using hibernate framework to persist the data into Postgres SQL
> DB and data is persisting and committing for all the clients but one of
> the client data is not inserted into DB.
What is different about that client?
Are all the clients passing data through the same instance of the framework?
Are you sure that the client is pointed at the correct database?
Is the log entry below from that client?
>
> Not getting any error/exception for this case. Could you please let us
> know how we can trace out this scenario on transaction level whether
> transaction is committing or not?
>
> We have enabled below properties in postgresql.conf file and verified
> but didn't get any findings about the transaction and below log
> statements are writing in our data store logs.
>
> log_statement = 'all'
>
> logging_collector = on
>
> log_min_messages = debug5
>
> log_min_error_statement = debug5
>
> 2024-02-19 15:21:54.850 +08 [1876] LOG: execute S_48: insert into
> xxxxxxx
> (f_schedule_name,f_id,f_totaldataredtn,f_invalidationtime,f_statuscode,f_module,f_app_type,f_dbbackuptype,f_is_compressed,f_proxy,f_size,f_sizeprotected,f_groupjobid,f_status,f_bytesmodifiednotsent,f_sizetransferredboffset,f_bytesmodifiedsent,f_errcode,f_jobid2,f_media_server,f_starttime,f_storageid,f_pool,f_queuestart,f_sizescannedboffset,f_errorcodesummary,f_ncopies,f_sizeprotectedboffset,f_snap_target_platform,f_backup_servername,f_nfiles,f_expiry,f_owner,f_policy_id,f_parentjobid,f_sub_name,f_completion_status,f_endtime,f_filesscanned,f_idle_wait,f_storage_unit,f_group_id,f_backup_set,f_ntries,f_job_name,f_level,f_agent_name,f_failed_copies,f_restarted_job,f_success_copies,f_domain_id,f_snap_target,f_jobid,f_request_id,f_pluginname,f_sizetransferred,f_is_snap,f_node_id,f_workflow_id,f_action_name,f_agent_id,f_instancename,f_session,f_totalobjdedup,f_changedbytes,f_sizeboffset,f_dedupredtn,f_statuscodesummary,f_workflow_jobid,f_snap_policy,f_size_copies,f_sizescanned,f_sub_id,f_archive_flag,f_nfilesnot,f_media_wait,f_snap_creation,f_effective_path) values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78)
>
> 2024-02-19 15:21:54.851 +08 [10928] DEBUG: bind <unnamed> to <unnamed>
>
> 2024-02-19 15:21:54.852 +08 [10928] DEBUG: CommitTransaction(1) name:
> unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
>
> Regards,
>
> Venkat
>
> *
> *
>
> *
>
> Internal Use - Confidential
>
> From:*Greg Sabino Mullane <[email protected]>
> *Sent:* Saturday, March 16, 2024 12:07 AM
> *To:* Bandi, Venkataramana - Dell Team <[email protected]>
> *Cc:* [email protected]; Kishore, Nanda - Dell Team
> <[email protected]>; Alampalli, Kishore
> <[email protected]>
> *Subject:* Re: Query on Postgres SQL transaction
>
> [EXTERNAL EMAIL]
>
> That's a very vague question, but you can trace exactly what is
> happening by issuing
>
> SET log_statement = 'all';
>
> Ideally at the session level by your application, but can also set it at
> the database and user level. If all else fails, set it globally (i.e.
> postgresql.conf). Turn it off again as soon as possible, it will make
> your logs extremely verbose. But you can track exactly what your
> application is doing.
>
> Cheers,
>
> Greg
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
@ 2024-03-25 07:18 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bandi, Venkataramana - Dell Team @ 2024-03-25 07:18 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
Hi,
Please find my inline comments for your questions.
Regards,
Venkat
Internal Use - Confidential
-----Original Message-----
From: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4-
Sent: Tuesday, March 19, 2024 9:33 PM
To: Bandi, Venkataramana - Dell Team +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4AOw- Greg Sabino Mullane +ADw-htamfids+AEA-gmail.com+AD4-
Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
Subject: Re: Query on Postgres SQL transaction
+AFs-EXTERNAL EMAIL+AF0-
On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote:
+AD4- Hi Greg,
+AD4-
+AD4- We are using hibernate framework to persist the data into Postgres SQL
+AD4- DB and data is persisting and committing for all the clients but one
+AD4- of the client data is not inserted into DB.
What is different about that client?
Ans: In our application data is getting from different nodes(systems) and persisting into Postgres SQL DB but for one of the nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out the transaction why data is not persisting sometimes.
Are all the clients passing data through the same instance of the framework?
Ans: Since it is a monolithic architecture application, it is running on same instance.
Are you sure that the client is pointed at the correct database?
Ans: Yes, its pointed to correct database and with same database connection, data is persisting for other nodes.
Is the log entry below from that client?
Ans: Yes
+AD4-
+AD4- Not getting any error/exception for this case. Could you please let us
+AD4- know how we can trace out this scenario on transaction level whether
+AD4- transaction is committing or not?
+AD4-
+AD4- We have enabled below properties in postgresql.conf file and verified
+AD4- but didn't get any findings about the transaction and below log
+AD4- statements are writing in our data store logs.
+AD4-
+AD4- log+AF8-statement +AD0- 'all'
+AD4-
+AD4- logging+AF8-collector +AD0- on
+AD4-
+AD4- log+AF8-min+AF8-messages +AD0- debug5
+AD4-
+AD4- log+AF8-min+AF8-error+AF8-statement +AD0- debug5
+AD4-
+AD4- 2024-02-19 15:21:54.850 +-08 +AFs-1876+AF0- LOG: execute S+AF8-48: insert into
+AD4- xxxxxxx
+AD4- (f+AF8-schedule+AF8-name,f+AF8-id,f+AF8-totaldataredtn,f+AF8-invalidationtime,f+AF8-statuscode
+AD4- ,f+AF8-module,f+AF8-app+AF8-type,f+AF8-dbbackuptype,f+AF8-is+AF8-compressed,f+AF8-proxy,f+AF8-size,f+AF8-s
+AD4- izeprotected,f+AF8-groupjobid,f+AF8-status,f+AF8-bytesmodifiednotsent,f+AF8-sizetransf
+AD4- erredboffset,f+AF8-bytesmodifiedsent,f+AF8-errcode,f+AF8-jobid2,f+AF8-media+AF8-server,f+AF8-s
+AD4- tarttime,f+AF8-storageid,f+AF8-pool,f+AF8-queuestart,f+AF8-sizescannedboffset,f+AF8-errorc
+AD4- odesummary,f+AF8-ncopies,f+AF8-sizeprotectedboffset,f+AF8-snap+AF8-target+AF8-platform,f+AF8-b
+AD4- ackup+AF8-servername,f+AF8-nfiles,f+AF8-expiry,f+AF8-owner,f+AF8-policy+AF8-id,f+AF8-parentjobid,f
+AD4- +AF8-sub+AF8-name,f+AF8-completion+AF8-status,f+AF8-endtime,f+AF8-filesscanned,f+AF8-idle+AF8-wait,f+AF8-s
+AD4- torage+AF8-unit,f+AF8-group+AF8-id,f+AF8-backup+AF8-set,f+AF8-ntries,f+AF8-job+AF8-name,f+AF8-level,f+AF8-agen
+AD4- t+AF8-name,f+AF8-failed+AF8-copies,f+AF8-restarted+AF8-job,f+AF8-success+AF8-copies,f+AF8-domain+AF8-id,f+AF8-
+AD4- snap+AF8-target,f+AF8-jobid,f+AF8-request+AF8-id,f+AF8-pluginname,f+AF8-sizetransferred,f+AF8-is+AF8-s
+AD4- nap,f+AF8-node+AF8-id,f+AF8-workflow+AF8-id,f+AF8-action+AF8-name,f+AF8-agent+AF8-id,f+AF8-instancename,f+AF8-
+AD4- session,f+AF8-totalobjdedup,f+AF8-changedbytes,f+AF8-sizeboffset,f+AF8-dedupredtn,f+AF8-st
+AD4- atuscodesummary,f+AF8-workflow+AF8-jobid,f+AF8-snap+AF8-policy,f+AF8-size+AF8-copies,f+AF8-sizesca
+AD4- nned,f+AF8-sub+AF8-id,f+AF8-archive+AF8-flag,f+AF8-nfilesnot,f+AF8-media+AF8-wait,f+AF8-snap+AF8-creation,
+AD4- f+AF8-effective+AF8-path) values
+AD4- (+ACQ-1,+ACQ-2,+ACQ-3,+ACQ-4,+ACQ-5,+ACQ-6,+ACQ-7,+ACQ-8,+ACQ-9,+ACQ-10,+ACQ-11,+ACQ-12,+ACQ-13,+ACQ-14,+ACQ-15,+ACQ-16,+ACQ-17,+ACQ-18,+ACQ-19,+ACQ-2
+AD4- 0,+ACQ-21,+ACQ-22,+ACQ-23,+ACQ-24,+ACQ-25,+ACQ-26,+ACQ-27,+ACQ-28,+ACQ-29,+ACQ-30,+ACQ-31,+ACQ-32,+ACQ-33,+ACQ-34,+ACQ-35,+ACQ-36,+ACQ-37,
+AD4- +ACQ-38,+ACQ-39,+ACQ-40,+ACQ-41,+ACQ-42,+ACQ-43,+ACQ-44,+ACQ-45,+ACQ-46,+ACQ-47,+ACQ-48,+ACQ-49,+ACQ-50,+ACQ-51,+ACQ-52,+ACQ-53,+ACQ-54,+ACQ-5
+AD4- 5,+ACQ-56,+ACQ-57,+ACQ-58,+ACQ-59,+ACQ-60,+ACQ-61,+ACQ-62,+ACQ-63,+ACQ-64,+ACQ-65,+ACQ-66,+ACQ-67,+ACQ-68,+ACQ-69,+ACQ-70,+ACQ-71,+ACQ-72,
+AD4- +ACQ-73,+ACQ-74,+ACQ-75,+ACQ-76,+ACQ-77,+ACQ-78)
+AD4-
+AD4- 2024-02-19 15:21:54.851 +-08 +AFs-10928+AF0- DEBUG: bind +ADw-unnamed+AD4- to
+AD4- +ADw-unnamed+AD4-
+AD4-
+AD4- 2024-02-19 15:21:54.852 +-08 +AFs-10928+AF0- DEBUG: CommitTransaction(1) name:
+AD4- unnamed+ADs- blockState: STARTED+ADs- state: INPROGRESS, xid/subid/cid: 0/1/0
+AD4-
+AD4- Regards,
+AD4-
+AD4- Venkat
+AD4-
+AD4- +ACo-
+AD4- +ACo-
+AD4-
+AD4- +ACo-
+AD4-
+AD4- Internal Use - Confidential
+AD4-
+AD4- From:+ACo-Greg Sabino Mullane +ADw-htamfids+AEA-gmail.com+AD4-
+AD4- +ACo-Sent:+ACo- Saturday, March 16, 2024 12:07 AM
+AD4- +ACo-To:+ACo- Bandi, Venkataramana - Dell Team
+AD4- +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4-
+AD4- +ACo-Cc:+ACo- pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team
+AD4- +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore
+AD4- +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
+AD4- +ACo-Subject:+ACo- Re: Query on Postgres SQL transaction
+AD4-
+AD4- +AFs-EXTERNAL EMAIL+AF0-
+AD4-
+AD4- That's a very vague question, but you can trace exactly what is
+AD4- happening by issuing
+AD4-
+AD4- SET log+AF8-statement +AD0- 'all'+ADs-
+AD4-
+AD4- Ideally at the session level by your application, but can also set it
+AD4- at the database and user level. If all else fails, set it globally (i.e.
+AD4- postgresql.conf). Turn it off again as soon as possible, it will make
+AD4- your logs extremely verbose. But you can track exactly what your
+AD4- application is doing.
+AD4-
+AD4- Cheers,
+AD4-
+AD4- Greg
+AD4-
--
Adrian Klaver
adrian.klaver+AEA-aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
@ 2024-03-25 15:09 ` Adrian Klaver <[email protected]>
2024-03-27 04:48 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-03-25 15:09 UTC (permalink / raw)
To: Bandi, Venkataramana - Dell Team <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
On 3/25/24 00:18, Bandi, Venkataramana - Dell Team wrote:
> Hi,
>
> Please find my inline comments for your questions.
>
>
> Regards,
> Venkat
>
>
> Internal Use - Confidential
> -----Original Message-----
> From: Adrian Klaver <[email protected]>
> Sent: Tuesday, March 19, 2024 9:33 PM
> To: Bandi, Venkataramana - Dell Team <[email protected]>; Greg Sabino Mullane <[email protected]>
> Cc: [email protected]; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
> Subject: Re: Query on Postgres SQL transaction
>
>
> [EXTERNAL EMAIL]
>
> On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote:
>> Hi Greg,
>>
>> We are using hibernate framework to persist the data into Postgres SQL
>> DB and data is persisting and committing for all the clients but one
>> of the client data is not inserted into DB.
>
> What is different about that client?
> Ans: In our application data is getting from different nodes(systems) and persisting into Postgres SQL DB but for one of the nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out the transaction why data is not persisting sometimes.
That we knew already. What I was after was whether that particular
node(system) is different in some important way from the others. For
instance different OS or OS version, different encoding, different
location on the network, different data it is working, etc.
Define what you have done to trace the path of the transaction.
> Are all the clients passing data through the same instance of the framework?
> Ans: Since it is a monolithic architecture application, it is running on same instance.
> Are you sure that the client is pointed at the correct database?
> Ans: Yes, its pointed to correct database and with same database connection, data is persisting for other nodes.
> Is the log entry below from that client?
> Ans: Yes
>>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
@ 2024-03-27 04:48 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 11:29 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bandi, Venkataramana - Dell Team @ 2024-03-27 04:48 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
Hi Adrian,
I will check with the customer on below info for that node and will share you.
For instance different OS or OS version, different encoding, different location on the network, different data it is working, etc.
As I already mentioned we have enabled Postgres SQL debug logs to trace out this scenario, but it is not helping us.
Regards,
Venkat
Internal Use - Confidential
-----Original Message-----
From: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4-
Sent: Monday, March 25, 2024 8:40 PM
To: Bandi, Venkataramana - Dell Team +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4AOw- Greg Sabino Mullane +ADw-htamfids+AEA-gmail.com+AD4-
Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
Subject: Re: Query on Postgres SQL transaction
+AFs-EXTERNAL EMAIL+AF0-
On 3/25/24 00:18, Bandi, Venkataramana - Dell Team wrote:
+AD4- Hi,
+AD4-
+AD4- Please find my inline comments for your questions.
+AD4-
+AD4-
+AD4- Regards,
+AD4- Venkat
+AD4-
+AD4-
+AD4- Internal Use - Confidential
+AD4- -----Original Message-----
+AD4- From: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4-
+AD4- Sent: Tuesday, March 19, 2024 9:33 PM
+AD4- To: Bandi, Venkataramana - Dell Team
+AD4- +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4AOw- Greg Sabino Mullane
+AD4- +ADw-htamfids+AEA-gmail.com+AD4-
+AD4- Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team
+AD4- +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore
+AD4- +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
+AD4- Subject: Re: Query on Postgres SQL transaction
+AD4-
+AD4-
+AD4- +AFs-EXTERNAL EMAIL+AF0-
+AD4-
+AD4- On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote:
+AD4APg- Hi Greg,
+AD4APg-
+AD4APg- We are using hibernate framework to persist the data into Postgres
+AD4APg- SQL DB and data is persisting and committing for all the clients but
+AD4APg- one of the client data is not inserted into DB.
+AD4-
+AD4- What is different about that client?
+AD4- Ans: In our application data is getting from different nodes(systems) and persisting into Postgres SQL DB but for one of the nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out the transaction why data is not persisting sometimes.
That we knew already. What I was after was whether that particular
node(system) is different in some important way from the others. For instance different OS or OS version, different encoding, different location on the network, different data it is working, etc.
Define what you have done to trace the path of the transaction.
+AD4- Are all the clients passing data through the same instance of the framework?
+AD4- Ans: Since it is a monolithic architecture application, it is running on same instance.
+AD4- Are you sure that the client is pointed at the correct database?
+AD4- Ans: Yes, its pointed to correct database and with same database connection, data is persisting for other nodes.
+AD4- Is the log entry below from that client?
+AD4- Ans: Yes
+AD4APg-
--
Adrian Klaver
adrian.klaver+AEA-aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-27 04:48 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
@ 2024-03-27 11:29 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 16:01 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bandi, Venkataramana - Dell Team @ 2024-03-27 11:29 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
Hi,
As l already mentioned, for this specific node also data is persisting but sometimes(randomly) data is not persisting.
As you mentioned our application doesn't have any restrictions on OS level and on network etc.
different OS or OS version, different encoding, different location on the network, different data it is working, etc.
Regards,
Venkat
Internal Use - Confidential
-----Original Message-----
From: Bandi, Venkataramana - Dell Team
Sent: Wednesday, March 27, 2024 10:18 AM
To: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4AOw- Greg Sabino Mullane +ADw-htamfids+AEA-gmail.com+AD4-
Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
Subject: RE: Query on Postgres SQL transaction
Hi Adrian,
I will check with the customer on below info for that node and will share you.
For instance different OS or OS version, different encoding, different location on the network, different data it is working, etc.
As I already mentioned we have enabled Postgres SQL debug logs to trace out this scenario, but it is not helping us.
Regards,
Venkat
-----Original Message-----
From: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4-
Sent: Monday, March 25, 2024 8:40 PM
To: Bandi, Venkataramana - Dell Team +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4AOw- Greg Sabino Mullane +ADw-htamfids+AEA-gmail.com+AD4-
Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
Subject: Re: Query on Postgres SQL transaction
+AFs-EXTERNAL EMAIL+AF0-
On 3/25/24 00:18, Bandi, Venkataramana - Dell Team wrote:
+AD4- Hi,
+AD4-
+AD4- Please find my inline comments for your questions.
+AD4-
+AD4-
+AD4- Regards,
+AD4- Venkat
+AD4-
+AD4-
+AD4- Internal Use - Confidential
+AD4- -----Original Message-----
+AD4- From: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4-
+AD4- Sent: Tuesday, March 19, 2024 9:33 PM
+AD4- To: Bandi, Venkataramana - Dell Team
+AD4- +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4AOw- Greg Sabino Mullane
+AD4- +ADw-htamfids+AEA-gmail.com+AD4-
+AD4- Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team
+AD4- +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore
+AD4- +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
+AD4- Subject: Re: Query on Postgres SQL transaction
+AD4-
+AD4-
+AD4- +AFs-EXTERNAL EMAIL+AF0-
+AD4-
+AD4- On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote:
+AD4APg- Hi Greg,
+AD4APg-
+AD4APg- We are using hibernate framework to persist the data into Postgres
+AD4APg- SQL DB and data is persisting and committing for all the clients but
+AD4APg- one of the client data is not inserted into DB.
+AD4-
+AD4- What is different about that client?
+AD4- Ans: In our application data is getting from different nodes(systems) and persisting into Postgres SQL DB but for one of the nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out the transaction why data is not persisting sometimes.
That we knew already. What I was after was whether that particular
node(system) is different in some important way from the others. For instance different OS or OS version, different encoding, different location on the network, different data it is working, etc.
Define what you have done to trace the path of the transaction.
+AD4- Are all the clients passing data through the same instance of the framework?
+AD4- Ans: Since it is a monolithic architecture application, it is running on same instance.
+AD4- Are you sure that the client is pointed at the correct database?
+AD4- Ans: Yes, its pointed to correct database and with same database connection, data is persisting for other nodes.
+AD4- Is the log entry below from that client?
+AD4- Ans: Yes
+AD4APg-
--
Adrian Klaver
adrian.klaver+AEA-aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-27 04:48 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 11:29 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
@ 2024-03-27 16:01 ` Adrian Klaver <[email protected]>
2024-03-30 10:14 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-03-27 16:01 UTC (permalink / raw)
To: Bandi, Venkataramana - Dell Team <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
On 3/27/24 04:29, Bandi, Venkataramana - Dell Team wrote:
> Hi,
>
> As l already mentioned, for this specific node also data is persisting but sometimes(randomly) data is not persisting.
How do you know which data is not persisting?
>
> As you mentioned our application doesn't have any restrictions on OS level and on network etc.
>
> different OS or OS version, different encoding, different location on the network, different data it is working, etc.
I don't understand what the above is saying. Do you mean there are
differences in these attributes between the nodes or no differences?
Also please do not top post, use either bottom or inline posting per:
https://en.wikipedia.org/wiki/Posting_style
>
> Regards,
> Venkat
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-27 04:48 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 11:29 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 16:01 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
@ 2024-03-30 10:14 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-30 10:44 ` Re: Query on Postgres SQL transaction Greg Sabino Mullane <[email protected]>
2024-03-30 15:35 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Bandi, Venkataramana - Dell Team @ 2024-03-30 10:14 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
Hi,
Clarifying the problem statement again, Multiple requests are getting to our application server and using hibernate framework to persist the data into Postgres SQL DB but for one of the request, it is also similar request like other requests and there are no differences b/w these requests in terms of different OS, different network etc. but data is not persisting few times.
We have enabled below properties in postgresql.conf file and verified but didn't get any findings about the transaction and below log statements are writing in our data store logs.
log+AF8-statement +AD0- 'all'
logging+AF8-collector +AD0- on
log+AF8-min+AF8-messages +AD0- debug5
log+AF8-min+AF8-error+AF8-statement +AD0- debug5
2024-02-19 15:21:54.850 +-08 +AFs-1876+AF0- LOG: execute S+AF8-48: insert into xxxxxxx (f+AF8-schedule+AF8-name,f+AF8-id,f+AF8-totaldataredtn,f+AF8-invalidationtime,f+AF8-statuscode,f+AF8-module,f+AF8-app+AF8-type,f+AF8-dbbackuptype,f+AF8-is+AF8-compressed,f+AF8-proxy,f+AF8-size,f+AF8-sizeprotected,f+AF8-groupjobid,f+AF8-status,f+AF8-bytesmodifiednotsent,f+AF8-sizetransferredboffset,f+AF8-bytesmodifiedsent,f+AF8-errcode,f+AF8-jobid2,f+AF8-media+AF8-server,f+AF8-starttime,f+AF8-storageid,f+AF8-pool,f+AF8-queuestart,f+AF8-sizescannedboffset,f+AF8-errorcodesummary,f+AF8-ncopies,f+AF8-sizeprotectedboffset,f+AF8-snap+AF8-target+AF8-platform,f+AF8-backup+AF8-servername,f+AF8-nfiles,f+AF8-expiry,f+AF8-owner,f+AF8-policy+AF8-id,f+AF8-parentjobid,f+AF8-sub+AF8-name,f+AF8-completion+AF8-status,f+AF8-endtime,f+AF8-filesscanned,f+AF8-idle+AF8-wait,f+AF8-storage+AF8-unit,f+AF8-group+AF8-id,f+AF8-backup+AF8-set,f+AF8-ntries,f+AF8-job+AF8-name,f+AF8-level,f+AF8-agent+AF8-name,f+AF8-failed+AF8-copies,f+AF8-restarted+AF8-job,f+AF8-success+AF8-copies,f+AF8-domain+AF8-id,f+AF8-snap+AF8-target,f+AF8-jobid,f+AF8-request+AF8-id,f+AF8-pluginname,f+AF8-sizetransferred,f+AF8-is+AF8-snap,f+AF8-node+AF8-id,f+AF8-workflow+AF8-id,f+AF8-action+AF8-name,f+AF8-agent+AF8-id,f+AF8-instancename,f+AF8-session,f+AF8-totalobjdedup,f+AF8-changedbytes,f+AF8-sizeboffset,f+AF8-dedupredtn,f+AF8-statuscodesummary,f+AF8-workflow+AF8-jobid,f+AF8-snap+AF8-policy,f+AF8-size+AF8-copies,f+AF8-sizescanned,f+AF8-sub+AF8-id,f+AF8-archive+AF8-flag,f+AF8-nfilesnot,f+AF8-media+AF8-wait,f+AF8-snap+AF8-creation,f+AF8-effective+AF8-path) values (+ACQ-1,+ACQ-2,+ACQ-3,+ACQ-4,+ACQ-5,+ACQ-6,+ACQ-7,+ACQ-8,+ACQ-9,+ACQ-10,+ACQ-11,+ACQ-12,+ACQ-13,+ACQ-14,+ACQ-15,+ACQ-16,+ACQ-17,+ACQ-18,+ACQ-19,+ACQ-20,+ACQ-21,+ACQ-22,+ACQ-23,+ACQ-24,+ACQ-25,+ACQ-26,+ACQ-27,+ACQ-28,+ACQ-29,+ACQ-30,+ACQ-31,+ACQ-32,+ACQ-33,+ACQ-34,+ACQ-35,+ACQ-36,+ACQ-37,+ACQ-38,+ACQ-39,+ACQ-40,+ACQ-41,+ACQ-42,+ACQ-43,+ACQ-44,+ACQ-45,+ACQ-46,+ACQ-47,+ACQ-48,+ACQ-49,+ACQ-50,+ACQ-51,+ACQ-52,+ACQ-53,+ACQ-54,+ACQ-55,+ACQ-56,+ACQ-57,+ACQ-58,+ACQ-59,+ACQ-60,+ACQ-61,+ACQ-62,+ACQ-63,+ACQ-64,+ACQ-65,+ACQ-66,+ACQ-67,+ACQ-68,+ACQ-69,+ACQ-70,+ACQ-71,+ACQ-72,+ACQ-73,+ACQ-74,+ACQ-75,+ACQ-76,+ACQ-77,+ACQ-78)
2024-02-19 15:21:54.851 +-08 +AFs-10928+AF0- DEBUG: bind +ADw-unnamed+AD4- to +ADw-unnamed+AD4-
2024-02-19 15:21:54.852 +-08 +AFs-10928+AF0- DEBUG: CommitTransaction(1) name: unnamed+ADs- blockState: STARTED+ADs- state: INPROGRESS, xid/subid/cid: 0/1/0
Could you let us know any other way to trace out these kind of DB transactions?
Regards,
Venkat
Internal Use - Confidential
-----Original Message-----
From: Adrian Klaver +ADw-adrian.klaver+AEA-aklaver.com+AD4-
Sent: Wednesday, March 27, 2024 9:32 PM
To: Bandi, Venkataramana - Dell Team +ADw-Venkataramana.Bandi+AEA-dellteam.com+AD4AOw- Greg Sabino Mullane +ADw-htamfids+AEA-gmail.com+AD4-
Cc: pgsql-general+AEA-lists.postgresql.org+ADs- Kishore, Nanda - Dell Team +ADw-Nanda.Kishore+AEA-Dellteam.com+AD4AOw- Alampalli, Kishore +ADw-Kishoreravishankar.Alampalli+AEA-dellteam.com+AD4-
Subject: Re: Query on Postgres SQL transaction
+AFs-EXTERNAL EMAIL+AF0-
On 3/27/24 04:29, Bandi, Venkataramana - Dell Team wrote:
+AD4- Hi,
+AD4-
+AD4- As l already mentioned, for this specific node also data is persisting but sometimes(randomly) data is not persisting.
How do you know which data is not persisting?
+AD4-
+AD4- As you mentioned our application doesn't have any restrictions on OS level and on network etc.
+AD4-
+AD4- different OS or OS version, different encoding, different location on the network, different data it is working, etc.
I don't understand what the above is saying. Do you mean there are differences in these attributes between the nodes or no differences?
Also please do not top post, use either bottom or inline posting per:
https://urldefense.com/v3/+AF8AXw-https://en.wikipedia.org/wiki/Posting+AF8-style+AF8AXwA7ACEAIQ-LpK...- +AFs-en+AFs-.+AF0-wikipedia+AFs-.+AF0-org+AF0-
+AD4-
+AD4- Regards,
+AD4- Venkat
+AD4-
--
Adrian Klaver
adrian.klaver+AEA-aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-27 04:48 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 11:29 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 16:01 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-30 10:14 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
@ 2024-03-30 10:44 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Greg Sabino Mullane @ 2024-03-30 10:44 UTC (permalink / raw)
To: Bandi, Venkataramana - Dell Team <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
That log snippet shows two different PIDs. Check the logs to see the
complete path that process 1876 took after it did the insert at 2024-02-19
15:21:54.850 +08
Make sure you are not using prepared transactions. This should return 0:
select current_setting('max_prepared_transactions');
Cheers,
Greg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query on Postgres SQL transaction
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-27 04:48 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 11:29 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 16:01 ` Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-30 10:14 ` RE: Query on Postgres SQL transaction Bandi, Venkataramana - Dell Team <[email protected]>
@ 2024-03-30 15:35 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2024-03-30 15:35 UTC (permalink / raw)
To: Bandi, Venkataramana - Dell Team <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]>; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
On 3/30/24 03:14, Bandi, Venkataramana - Dell Team wrote:
> Hi,
>
> Clarifying the problem statement again, Multiple requests are getting to our application server and using hibernate framework to persist the data into Postgres SQL DB but for one of the request, it is also similar request like other requests and there are no differences b/w these requests in terms of different OS, different network etc. but data is not persisting few times.
You have fallen into the trap of working the problem(repeating the
problem definition) instead of working the solution, taking the steps to
solve it. Start with acknowledging that this "...there are no
differences b/w these requests ..." is not the case, otherwise we would
not be having this discusion. This is going to involve working through
the process from the front end to the database.
Start with:
How do you know which data is not persisting?
Did the INSERT entry you show below persist?
>
> We have enabled below properties in postgresql.conf file and verified but didn't get any findings about the transaction and below log statements are writing in our data store logs.
>
> log_statement = 'all'
> logging_collector = on
> log_min_messages = debug5
> log_min_error_statement = debug5
>
> 2024-02-19 15:21:54.850 +08 [1876] LOG: execute S_48: insert into xxxxxxx (f_schedule_name,f_id,f_totaldataredtn,f_invalidationtime,f_statuscode,f_module,f_app_type,f_dbbackuptype,f_is_compressed,f_proxy,f_size,f_sizeprotected,f_groupjobid,f_status,f_bytesmodifiednotsent,f_sizetransferredboffset,f_bytesmodifiedsent,f_errcode,f_jobid2,f_media_server,f_starttime,f_storageid,f_pool,f_queuestart,f_sizescannedboffset,f_errorcodesummary,f_ncopies,f_sizeprotectedboffset,f_snap_target_platform,f_backup_servername,f_nfiles,f_expiry,f_owner,f_policy_id,f_parentjobid,f_sub_name,f_completion_status,f_endtime,f_filesscanned,f_idle_wait,f_storage_unit,f_group_id,f_backup_set,f_ntries,f_job_name,f_level,f_agent_name,f_failed_copies,f_restarted_job,f_success_copies,f_domain_id,f_snap_target,f_jobid,f_request_id,f_pluginname,f_sizetransferred,f_is_snap,f_node_id,f_workflow_id,f_action_name,f_agent_id,f_instancename,f_session,f_totalobjdedup,f_changedbytes,f_sizeboffset,f_dedupredtn,f_statuscodesummary,f_workflow_jobid,f_snap_policy,f_size_copies,f_sizescanned,f_sub_id,f_archive_flag,f_nfilesnot,f_media_wait,f_snap_creation,f_effective_path) values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78)
> 2024-02-19 15:21:54.851 +08 [10928] DEBUG: bind <unnamed> to <unnamed>
> 2024-02-19 15:21:54.852 +08 [10928] DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
>
> Could you let us know any other way to trace out these kind of DB transactions?
>
>
> Regards,
> Venkat
>
>
> Internal Use - Confidential
> -----Original Message-----
> From: Adrian Klaver <[email protected]>
> Sent: Wednesday, March 27, 2024 9:32 PM
> To: Bandi, Venkataramana - Dell Team <[email protected]>; Greg Sabino Mullane <[email protected]>
> Cc: [email protected]; Kishore, Nanda - Dell Team <[email protected]>; Alampalli, Kishore <[email protected]>
> Subject: Re: Query on Postgres SQL transaction
>
>
> [EXTERNAL EMAIL]
>
> On 3/27/24 04:29, Bandi, Venkataramana - Dell Team wrote:
>> Hi,
>>
>> As l already mentioned, for this specific node also data is persisting but sometimes(randomly) data is not persisting.
>
> How do you know which data is not persisting?
>
>>
>> As you mentioned our application doesn't have any restrictions on OS level and on network etc.
>>
>> different OS or OS version, different encoding, different location on the network, different data it is working, etc.
>
> I don't understand what the above is saying. Do you mean there are differences in these attributes between the nodes or no differences?
>
> Also please do not top post, use either bottom or inline posting per:
>
> https://urldefense.com/v3/__https://en.wikipedia.org/wiki/Posting_style__;!!LpKI!glyl28rP3t6cLe3s9tF... [en[.]wikipedia[.]org]
>
>>
>> Regards,
>> Venkat
>>
>
>
> --
> Adrian Klaver
> [email protected]
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-03-30 15:35 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-19 16:03 Re: Query on Postgres SQL transaction Adrian Klaver <[email protected]>
2024-03-25 07:18 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-25 15:09 ` Adrian Klaver <[email protected]>
2024-03-27 04:48 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 11:29 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-27 16:01 ` Adrian Klaver <[email protected]>
2024-03-30 10:14 ` Bandi, Venkataramana - Dell Team <[email protected]>
2024-03-30 10:44 ` Greg Sabino Mullane <[email protected]>
2024-03-30 15:35 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox