INSERT to partitioned table doesn't return row count

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

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

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.

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

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

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

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.

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