Re: Incorrect number of rows inserted into partitioned table

Good afternoon Thomas.

I am using the PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit.

If you answer the question why I use this option.
That answer will be simple. This is just the result of experiments in which
I stumbled upon this behavior.

вт, 11 апр. 2023 г. в 00:58, Роман Осипов <
osipovromanvladimirovich(at)gmail(dot)com>:

> Good afternoon David.
>
> Thanks for the quick response.
> I know about declarative partitioning and just use it. But unfortunately
> that doesn't fix the problem.
>
> In this case, the problem is not how many returns *insert *with *limit*.
> And the fact that the tables always have a different number of rows. For
> example:
>
> 1) truncate table book_inherit_test;
>
> 2) insert into book_inherit_test select * from book_test limit 1000000;
>
> 3) select count(*) from book_inherit_test;
>
> 4) select count(*) from only book_inherit_test;
>
> 5) select count(*) from only book_inherit_test_before_1950;
> Attempt 1
>
> 1) Updated Rows 0
>
> 2) Updated Rows 612528
>
> 3) count(*) 1001227
>
> 4) count(*) 612528
>
> 5) count(*) 388699
>
> (4)+(5)=(3)
>
> 612528 + 388699 = 1001227 That's right, but expected 1000000
>
> Attempt 2
>
> 1) Updated Rows 0
>
> 2) Updated Rows 612602
>
> 3) count(*) 1001320
>
> 4) count(*) 612602
>
> 5) count(*) 388718
>
> (4)+(5)=(3)
>
> 612602 + 388718 = 1001320 That's right, but expected 1000000
>
> вт, 11 апр. 2023 г. в 00:39, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>:
>
>> On 4/10/23 20:42, Роман Осипов wrote:
>> > Good afternoon
>> >
>> > When reloading data from a simple table into a partitioned (through
>> > inheritance) using a query like:*insert into [new_partition_table]
>> > select * from [old_table] limit xxxxxxx*;
>> >
>> > There is an insertion not of the amount specified in *limit*,, but a
>> > little more or less than it.
>> >
>>
>> Which version? I did try this on master, and I can't reproduce it - the
>> count at the end returns the correct value (1M).
>>
>> The rule affects the insert status, which looks e.g. like this:
>>
>> INSERT 0 611532
>>
>> instead of 1M, but that's expected because of the rule, I think.
>>
>> FWIW I wonder why you use rules, it's rather tricky to get that right,
>> which is why the usual recommendation is not to use this if there's an
>> alternative way to do stuff (which for partitioning there is).
>>
>>
>> regards
>>
>> --
>> Tomas Vondra
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>