Обсуждение: INSERT to partitioned table doesn't return row count
Is it possible to return the number of rows inserted to a partitioned table set up as per the examples on http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html, in the same way as if you inserted directly into a target table? I can sort of see why it returns 0, because 0 rows are actually inserted in the parent table, but I'd prefer not to have to make all the code around this use case partition-aware; most of it isn't mine. I tried modifying the last RETURN line in the trigger function but couldn't find anything that was even valid syntax. -kgd
On 12/03/2014 11:19 AM, Kris Deugau wrote: > Is it possible to return the number of rows inserted to a partitioned > table set up as per the examples on > http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html, in > the same way as if you inserted directly into a target table? > > I can sort of see why it returns 0, because 0 rows are actually inserted > in the parent table, but I'd prefer not to have to make all the code > around this use case partition-aware; most of it isn't mine. I have not used partitioned tables enough, but it would seem this is covered here: http://www.postgresql.org/docs/9.3/interactive/sql-select.html table_name The name (optionally schema-qualified) of an existing table or view. If ONLY is specified before the table name, only that table is scanned. If ONLY is not specified, the table and all its descendant tables (if any) are scanned. < ***>Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.<***> > > I tried modifying the last RETURN line in the trigger function but > couldn't find anything that was even valid syntax. > > -kgd > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > On 12/03/2014 11:19 AM, Kris Deugau wrote: >> Is it possible to return the number of rows inserted to a partitioned >> table set up as per the examples on >> http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html, in >> the same way as if you inserted directly into a target table? >> >> I can sort of see why it returns 0, because 0 rows are actually inserted >> in the parent table, but I'd prefer not to have to make all the code >> around this use case partition-aware; most of it isn't mine. > > I have not used partitioned tables enough, but it would seem this is > covered here: > > http://www.postgresql.org/docs/9.3/interactive/sql-select.html > > table_name > > The name (optionally schema-qualified) of an existing table or > view. If ONLY is specified before the table name, only that table is > scanned. If ONLY is not specified, the table and all its descendant > tables (if any) are scanned. < ***>Optionally, * can be specified after > the table name to explicitly indicate that descendant tables are > included.<***> I don't see how what you quoted has any relation to the problem posed by the OP... Going from recent memory this particular behavior complaint has now come up three times in the past six months - the main complaint previously is that given an insert trigger for the partition you have to copy, not move, the insert to the child tables - leaving the parent table populated during the insert and thus returning the count - and then delete the record from the parent table. That sequence, while solving the row number problem, then causes vacuum to behave undesirably. David J. -- View this message in context: http://postgresql.nabble.com/INSERT-to-partitioned-table-doesn-t-return-row-count-tp5829148p5829157.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David G Johnston wrote: >> http://www.postgresql.org/docs/9.3/interactive/sql-select.html >> >> table_name >> >> The name (optionally schema-qualified) of an existing table or >> view. If ONLY is specified before the table name, only that table is >> scanned. If ONLY is not specified, the table and all its descendant >> tables (if any) are scanned. < ***>Optionally, * can be specified after >> the table name to explicitly indicate that descendant tables are >> included.<***> > > I don't see how what you quoted has any relation to the problem posed by the > OP... *nod* SELECTs work just fine; by default they'll pull data from all necessary child tables, and return the correct result row count. It's on INSERT where if you have a trigger that diverts the actual INSERT to a child table that you get: INSERT 0 0 returned in psql, instead of INSERT 0 1 for one row, or INSERT 0 10000 for 10K rows, and similar results from eg Perl DBI. > Going from recent memory this particular behavior complaint has now come up > three times in the past six months - the main complaint previously is that > given an insert trigger for the partition you have to copy, not move, the > insert to the child tables - leaving the parent table populated during the > insert and thus returning the count - and then delete the record from the > parent table. That sequence, while solving the row number problem, then > causes vacuum to behave undesirably. Eugh. For the (mostly) one-off bulk-copy process I've been preparing I have a couple of other workarounds (simplest being just inserting in the child table directly), but if it comes down to it it will be simpler to put up with the relatively minor nuisance of staying unpartitioned rather than (potentially) destabilizing someone else's code. After all, I've already written the code to archive old records from the unpartitioned table anyway... it just would have been nice to be able to "pg_dump dbname -t table_2013" instead. -kgd
Kris Deugau wrote > David G Johnston wrote: >> Going from recent memory this particular behavior complaint has now come >> up >> three times in the past six months - the main complaint previously is >> that >> given an insert trigger for the partition you have to copy, not move, the >> insert to the child tables - leaving the parent table populated during >> the >> insert and thus returning the count - and then delete the record from the >> parent table. That sequence, while solving the row number problem, then >> causes vacuum to behave undesirably. > > Eugh. For the (mostly) one-off bulk-copy process I've been preparing I > have a couple of other workarounds (simplest being just inserting in the > child table directly), but if it comes down to it it will be simpler to > put up with the relatively minor nuisance of staying unpartitioned > rather than (potentially) destabilizing someone else's code. After all, > I've already written the code to archive old records from the > unpartitioned table anyway... it just would have been nice to be able > to "pg_dump dbname -t table_2013" instead. The specific thread I was thinking of is here: http://postgresql.nabble.com/Autovacuum-on-partitioned-tables-in-version-9-1-td5826595.html The links referenced there provide the basis for my thought that there might be 3 recent examples... David J. -- View this message in context: http://postgresql.nabble.com/INSERT-to-partitioned-table-doesn-t-return-row-count-tp5829148p5829163.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.