Partitioned tables do not return affected row counts to client

On Thu, 2016-06-02 at 11:01 -0700, rverghese wrote:

We are looking to move from one large table to partitioned tables.
Since the
inserts and updates are made to the master table and then inserted
into the
appropriate partitioned table based on the trigger rules, the
affected_rows
returned to the client (PHP in this case) is always 0. We have been
using
the affected_rows to check various things on the client end. Is there
an
alternative to check for rows affected with partitioned tables? Seems
like a
pretty big missing feature. 
Thanks
RV

--
View this message in context: http://postgresql.nabble.com/Partitione
d-tables-do-not-return-affected-row-counts-to-client-tp5906112.html
Sent from the PostgreSQL - general mailing list archive at
Nabble.com.

pg_affected_rows returns its value from the last SQL statement
executed.
So, if the last one was a "COMMIT" say, then it returns zero.

I guess you already know this.
Without knowing the complete circumstances, all I can suggest is
running a query using your partitioning rules to verify that the rows
were inserted into the correct partition.

HTH,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/06/16 20:01, rverghese wrote:

We are looking to move from one large table to partitioned tables. Since the
inserts and updates are made to the master table and then inserted into the
appropriate partitioned table based on the trigger rules, the affected_rows
returned to the client (PHP in this case) is always 0. We have been using
the affected_rows to check various things on the client end. Is there an
alternative to check for rows affected with partitioned tables? Seems like a
pretty big missing feature.

The standard way to do partitioning is for the trigger on the parent
table to redirect the INSERT and then cancel the original INSERT by
returning NULL. It sounds like that's what you're doing, and since the
INSERT command that you actually gave didn't insert any rows, you
correctly get 0 back.

The solution to this, which is a little ugly but works, is to create a
view over the parent table with an INSTEAD OF trigger and insert into
the view.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general