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(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
>