Postgres Pro
Downloads
Home   >   mailing lists

Thread: Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

From
Avadhut Narayan Joshi
Date:

Hello Experts ,

 

For SQL Server , when executing queries we get value in InternalRowsAffected corresponding to row affected . Do we have equivalent in PostgreSQL ?

 

Regards

Avadhut

 

Sent from Mail for Windows 10

 

Re: Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

From
Vijaykumar Jain
Date:


On Thu, Jul 29, 2021, 5:52 PM Avadhut Narayan Joshi <AJoshi7@sensiaglobal.com> wrote:

Hello Experts ,

 

For SQL Server , when executing queries we get value in InternalRowsAffected corresponding to row affected . 


Have a look at  `get diagnostics` at 42.5.5 section. 


RE: Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

From
Avadhut Narayan Joshi
Date:

Hello Vijaykumar ,

 

For SQL Server , when we execute queries using ExecuteNonQuery , InternalRowsAffected is set by default .

Using diagnostics we have to collect and  return explicitly for further work flow .

 

So is there any out-of-box equivalent in PostgreSQL ?

 

Sent from Mail for Windows 10

 

From: Vijaykumar Jain
Sent: 29 July 2021 17:56
To: Avadhut Narayan Joshi
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

 

 

On Thu, Jul 29, 2021, 5:52 PM Avadhut Narayan Joshi <AJoshi7@sensiaglobal.com> wrote:

Hello Experts ,

 

For SQL Server , when executing queries we get value in InternalRowsAffected corresponding to row affected . 

 

Have a look at  `get diagnostics` at 42.5.5 section. 

 

 

 

Re: Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

From
Vijaykumar Jain
Date:


On Thu, Jul 29, 2021, 6:01 PM Avadhut Narayan Joshi <AJoshi7@sensiaglobal.com> wrote:

Hello Vijaykumar ,

 

For SQL Server , when we execute queries using ExecuteNonQuery , InternalRowsAffected is set by default .

Using diagnostics we have to collect and  return explicitly for further work flow .

 

So is there any out-of-box equivalent in PostgreSQL 


OK, honestly I do not know much about sql server.
You can run explain, on the query to get an estimated rows that would be touched at each node layer.
explain analyze, would give estimated and real number of rows touched at each node layer.
There are stats updated in pg_stat_all_tables catalog for the relevant table for ins, upd, del etc but that is cumulative at the db layer, not sure if that can be useful at per query layer.
I think in that case, someone else would  chime in with more exp.

 

 

Re: Equivalent of InternalRowsAffected in NonExecuteQuery for PostgreSQL

From
Steve Midgley
Date:


On Thu, Jul 29, 2021 at 5:37 AM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


On Thu, Jul 29, 2021, 6:01 PM Avadhut Narayan Joshi <AJoshi7@sensiaglobal.com> wrote:

Hello Vijaykumar ,

 

For SQL Server , when we execute queries using ExecuteNonQuery , InternalRowsAffected is set by default .

Using diagnostics we have to collect and  return explicitly for further work flow .

 

So is there any out-of-box equivalent in PostgreSQL 


OK, honestly I do not know much about sql server.
You can run explain, on the query to get an estimated rows that would be touched at each node layer.
explain analyze, would give estimated and real number of rows touched at each node layer.
There are stats updated in pg_stat_all_tables catalog for the relevant table for ins, upd, del etc but that is cumulative at the db layer, not sure if that can be useful at per query layer.
I think in that case, someone else would  chime in with more exp.


My recollection (hazy) is that for my ORM wrapper of choice (ActiveRecord for Ruby), the AR Pg driver returns the number of rows affected and I just relied on that. (cf https://stackoverflow.com/questions/27552542/affected-rows-for-activerecordbase-connection-execute). 

I hope this input is helpful,
Steve