Re: Incorrect number of rows inserted into partitioned table

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.vondra@enterprisedb.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