RE: Query on Postgres SQL transaction - Mailing list pgsql-general

Hi,

Please find my inline comments for your questions.


Regards,
Venkat


Internal Use - Confidential
-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, March 19, 2024 9:33 PM
To: Bandi, Venkataramana - Dell Team <Venkataramana.Bandi@dellteam.com>; Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team <Nanda.Kishore@Dellteam.com>; Alampalli, Kishore
<Kishoreravishankar.Alampalli@dellteam.com>
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
ofthe nodes(system) data is not persisting and sometimes data is persisting for this node also. We have to trace out
thetransaction 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
>
> 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_s
> izeprotected,f_groupjobid,f_status,f_bytesmodifiednotsent,f_sizetransf
> erredboffset,f_bytesmodifiedsent,f_errcode,f_jobid2,f_media_server,f_s
> tarttime,f_storageid,f_pool,f_queuestart,f_sizescannedboffset,f_errorc
> odesummary,f_ncopies,f_sizeprotectedboffset,f_snap_target_platform,f_b
> ackup_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_s
> torage_unit,f_group_id,f_backup_set,f_ntries,f_job_name,f_level,f_agen
> t_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_s
> nap,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_st
> atuscodesummary,f_workflow_jobid,f_snap_policy,f_size_copies,f_sizesca
> nned,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,$2
> 0,$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,$5
> 5,$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 <htamfids@gmail.com>
> *Sent:* Saturday, March 16, 2024 12:07 AM
> *To:* Bandi, Venkataramana - Dell Team
> <Venkataramana.Bandi@dellteam.com>
> *Cc:* pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team
> <Nanda.Kishore@Dellteam.com>; Alampalli, Kishore
> <Kishoreravishankar.Alampalli@dellteam.com>
> *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
adrian.klaver@aklaver.com