Fwd: ability to return number of rows inserted into child partition tables request

From: Roger Pack <rogerdpack2(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Fwd: ability to return number of rows inserted into child partition tables request
Date: 2014-11-17 23:03:37
Message-ID: CAL1QdWf9Rg-KmpJ8gapyH5PgQdKZsVrz4NED6VBwn_hfw21YLQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hello.

I was trying to get postgres to return the "correct" number of rows
inserted for batch inserts to a partitioned table [using the triggers as
suggested here
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html results in
it always returning 0 by default].

What I ideally wanted it to do is to be able to insert into just the child
partitions, and return number of rows updated.

It seems the state of the art is either to return the NEW row from the
insert trigger [which causes it to also be saved to the parent master
table], then define an extra trigger to remove the parent table. So 2
inserts and 1 delete for an insert. [1]

Or you can use an unconditional rule and it will return the number of rows
updated [however, in this case, since we're using partitioning, we I think
need multiple rules, once for each child table].

It is possible for a view to use a trigger and still return the number of
rows updated, which provides another work around. (See bottom of [1]).

Is there some more elegant way here? It seems odd that partitioned tables
basically cannot, without a *lot* of massaging, return number of rows
updated, am I missing something or do I understand ok? [Today this requires
people to put in lots of work arounds, like *not* checking for number of
rows returned for batch inserts, etc.-- potentially dangerous as well]

Is there, for instance, some work around, like a way to manually cause the
count of the number of rows affected by the command to be incremented
here? Or possibly conditional rules could be made possible to return the
output string with number of rows affected (feature request)?

I guess this has come up before, FWIW.
http://grokbase.com/t/postgresql/pgsql-general/0863bjzths/insert-into-master-table-0-rows-affected-hibernate-problems

One way of fixing this would be to allow "do instead" rules on normal
tables, instead of only on views (otherwise we are forced to use a rule,
correct me if I'm wrong). I'd wager there would be other viable options as
well.

Thanks!
-roger-

[1]
http://stackoverflow.com/questions/83093/hibernate-insert-batch-with-partitioned-postgresql

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Roger Pack 2014-11-17 23:18:24 count distinct slow?
Previous Message Thomas Kellerer 2014-11-17 22:16:26 Re: Can't drop a view ("view does not exist") that has an entry in "INFORMATION_SCHEMA.views"

Browse pgsql-hackers by date

  From Date Subject
Next Message Mats Erik Andersson 2014-11-17 23:40:51 vacuumdb: Help text for --analyze-only.
Previous Message Simon Riggs 2014-11-17 22:58:37 Re: proposal: plpgsql - Assert statement