From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?B?0KDQvtC80LDQvSDQntGB0LjQv9C+0LI=?=
 <osipovromanvladimirovich@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Incorrect number of rows inserted into partitioned table
In-reply-to: 
 <CAAqk126uv_J-T8MhOvMqmUUD6MPsxFpq=zojDnWeftZokDYyEw@mail.gmail.com>
References: 
 <CAAqk126uv_J-T8MhOvMqmUUD6MPsxFpq=zojDnWeftZokDYyEw@mail.gmail.com>
Comments: In-reply-to =?UTF-8?B?0KDQvtC80LDQvSDQntGB0LjQv9C+0LI=?=
 <osipovromanvladimirovich@gmail.com>
	message dated "Mon, 10 Apr 2023 21:42:28 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <23168.1681164279.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 10 Apr 2023 18:04:39 -0400
Message-ID: <23169.1681164279@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/23169.1681164279%40sss.pgh.pa.us>
Precedence: bulk

=3D?UTF-8?B?0KDQvtC80LDQvSDQntGB0LjQv9C+0LI=3D?=3D <osipovromanvladimirovi=
ch@gmail.com> writes:
> There is an insertion not of the amount specified in *limit*,, but a lit=
tle
> more or less than it.

I think you're getting burnt by the fact that a rule is a macro,
combined with the fact that your query is underspecified:

insert into book_inherit_test select * from book_test limit 1000000;

This doesn't constrain *which* 1000000 rows of book_test get inserted.
If we EXPLAIN it we get:

=3D# explain insert into book_inherit_test select * from book_test limit 1=
000000;
                                              QUERY PLAN                  =
                            =

--------------------------------------------------------------------------=
----------------------------
 Insert on book_inherit_test  (cost=3D0.00..32353.00 rows=3D0 width=3D0)
   ->  Subquery Scan on "*SELECT*"  (cost=3D0.00..32353.00 rows=3D617176 w=
idth=3D31)
         Filter: ((("*SELECT*".releaseyear >=3D 1900) AND ("*SELECT*".rele=
aseyear < 1951)) IS NOT TRUE)
         ->  Limit  (cost=3D0.00..17353.00 rows=3D1000000 width=3D31)
               ->  Seq Scan on book_test  (cost=3D0.00..34706.00 rows=3D20=
00000 width=3D31)
 =

 Insert on book_inherit_test_before_1950  (cost=3D0.00..32353.00 rows=3D0 =
width=3D0)
   ->  Subquery Scan on "*SELECT*"  (cost=3D0.00..32353.00 rows=3D382824 w=
idth=3D31)
         Filter: (("*SELECT*".releaseyear >=3D 1900) AND ("*SELECT*".relea=
seyear < 1951))
         ->  Limit  (cost=3D0.00..17353.00 rows=3D1000000 width=3D31)
               ->  Seq Scan on book_test  (cost=3D0.00..34706.00 rows=3D20=
00000 width=3D31)
(11 rows)

from which we can see that the insertions into book_inherit_test and
those into book_inherit_test_before_1950 are driven off completely
separate scans of book_test.  And that table is big enough that
the synchronize_seqscans feature kicks in, meaning that indeed this
will scan two different million-row subsets of book_test, producing
results fundamentally unlike what you expected.

I get stable results after disabling synchronize_seqscans, but
a more correct way would be to add ORDER BY to fully determine which
rows of book_test are considered.

Advice: don't use a RULE for this sort of thing.  If you really want to,
you can get the same effects more predictably with a trigger.
But as David said, the whole thing looks like a poor reimplementation
of partitioning.

			regards, tom lane