Re: INSERT to partitioned table doesn't return row count

From: Kris Deugau <kdeugau(at)vianet(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: INSERT to partitioned table doesn't return row count
Date: 2014-12-03 21:20:08
Message-ID: 547F7E88.7080901@vianet.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G Johnston 2014-12-03 21:31:28 Re: INSERT to partitioned table doesn't return row count
Previous Message Tom Lane 2014-12-03 20:56:50 Re: segmentation fault postgres 9.3.5 core dump perlu related ?