Обсуждение: Partitioned tables do not return affected row counts to client
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/Partitioned-tables-do-not-return-affected-row-counts-to-client-tp5906112.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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