Обсуждение: BUG #15283: Query Result equal 0 for partitioned table
The following bug has been logged on the website: Bug reference: 15283 Logged by: Suan Tay Email address: suan.tay@gmail.com PostgreSQL version: 9.6.9 Operating system: Ubuntu 16,17 Description: Hello, Result of data insertion is alway at 0 when the table is partitionned. I use Sorm as JPA in my Java App, it cacht the result and as it's equal to 0 it send to Error. I solved this issue with SimpleORM but it's not the reel issue. I also test the Postgresql 10. Regards ST
On 2018/07/18 21:59, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 15283 > Logged by: Suan Tay > Email address: suan.tay@gmail.com > PostgreSQL version: 9.6.9 > Operating system: Ubuntu 16,17 > Description: > > Hello, > Result of data insertion is alway at 0 when the table is partitionned. What do you mean by "result is 0"? Is it "INSERT 0 0" that you're referring to? You reported this for 9.6.9 which doesn't have native partitioning. So, you may have set up a trigger on the parent table to redirect INSERT on it to INSERT into child tables / partitions. If that's so, then because of such a trigger, nothing would be actually inserted into the parent table, so the result is INSERT 0 0. > I use Sorm as JPA in my Java App, it cacht the result and as it's equal to 0 > it send to Error. > I solved this issue with SimpleORM but it's not the reel issue. > > I also test the Postgresql 10. If you tried to use native partitioned table (CREATE TABLE with PARTITION BY clause) in Postgres 10, then this shouldn't happen. Inserts into parent are still redirected to child tables / partitions, but no triggers are involved, so the result shows the actual count of rows inserted. For example, if you insert 1 row into the partitioned table (parent), you will get INSERT 0 1 as a result. But if you didn't use native partitioned table, but used the same old method of using triggers as 9.6, you will see the same behavior as described above for 9.6. Thanks, Amit
Thank you for the clarification.
Yes in deed I'm using rules to do such partitioning in Posgresql 9.6.
I also test Postgres 10 with rules : the result of insertion is always at 0.
As you explain, it's throw that in the madder table isn't any insertion but for the app the partitioning has to be completely transparent.
But I don't test CREATE TABLE with PARTITION BY clause . That may solve the issue I have but I have to change all the app part because of the different way to do PARTITIONING
I will do .
Many Thanks.
Regards.
ST
Le jeu. 19 juil. 2018 à 06:44, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> a écrit :
On 2018/07/18 21:59, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15283
> Logged by: Suan Tay
> Email address: suan.tay@gmail.com
> PostgreSQL version: 9.6.9
> Operating system: Ubuntu 16,17
> Description:
>
> Hello,
> Result of data insertion is alway at 0 when the table is partitionned.
What do you mean by "result is 0"? Is it "INSERT 0 0" that you're
referring to?
You reported this for 9.6.9 which doesn't have native partitioning. So,
you may have set up a trigger on the parent table to redirect INSERT on it
to INSERT into child tables / partitions. If that's so, then because of
such a trigger, nothing would be actually inserted into the parent table,
so the result is INSERT 0 0.
> I use Sorm as JPA in my Java App, it cacht the result and as it's equal to 0
> it send to Error.
> I solved this issue with SimpleORM but it's not the reel issue.
>
> I also test the Postgresql 10.
If you tried to use native partitioned table (CREATE TABLE with PARTITION
BY clause) in Postgres 10, then this shouldn't happen. Inserts into
parent are still redirected to child tables / partitions, but no triggers
are involved, so the result shows the actual count of rows inserted. For
example, if you insert 1 row into the partitioned table (parent), you will
get INSERT 0 1 as a result.
But if you didn't use native partitioned table, but used the same old
method of using triggers as 9.6, you will see the same behavior as
described above for 9.6.
Thanks,
Amit
On 2018/07/23 17:04, suan tay wrote: > Thank you for the clarification. > > Yes in deed I'm using rules to do such partitioning in Posgresql 9.6. > I also test Postgres 10 with rules : the result of insertion is always at 0. I forgot to mention that using rules (not triggers) would lead to the same behavior. Thanks, Amit