pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedbugs or my fault
8+ messages / 3 participants
[nested] [flat]
* bugs or my fault
@ 2021-09-15 06:28 Yudianto Prasetyo <mr.yudianto@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Yudianto Prasetyo @ 2021-09-15 06:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
can you explain if this is a bug or my fault ?
I'm making a simple example of hash partition
CREATE TABLE myschema.customers(
id int,
status text,
arr numeric
) PARTITION BY HASH(id);
CREATE TABLE myschema.cust0 PARTITION OF myschema.customers
FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE myschema.cust1 PARTITION OF myschema.customers
FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE myschema.cust2 PARTITION OF myschema.customers
FOR VALUES WITH (modulus 3, remainder 2);
INSERT INTO myschema.customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20),
(3,'EXPIRED',38), (4,'REACTIVATED',144);
when I run the query, the result is:
2 "RECURRING" 20
4 "REACTIVATED" 144
shouldn't be
3 "EXPIRED" 38
i run this in version
PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit
Please help me
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 06:30 Yudianto Prasetyo <mr.yudianto@gmail.com>
parent: Yudianto Prasetyo <mr.yudianto@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Yudianto Prasetyo @ 2021-09-15 06:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
sorry I miss the query
select * from myschema.cust0;
On Wed, Sep 15, 2021 at 1:28 PM Yudianto Prasetyo <mr.yudianto@gmail.com>
wrote:
> Hello,
>
> can you explain if this is a bug or my fault ?
>
> I'm making a simple example of hash partition
>
> CREATE TABLE myschema.customers(
> id int,
> status text,
> arr numeric
> ) PARTITION BY HASH(id);
>
> CREATE TABLE myschema.cust0 PARTITION OF myschema.customers
> FOR VALUES WITH (modulus 3, remainder 0);
>
> CREATE TABLE myschema.cust1 PARTITION OF myschema.customers
> FOR VALUES WITH (modulus 3, remainder 1);
>
> CREATE TABLE myschema.cust2 PARTITION OF myschema.customers
> FOR VALUES WITH (modulus 3, remainder 2);
>
> INSERT INTO myschema.customers VALUES (1,'ACTIVE',100),
> (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
>
> when I run the query, the result is:
>
> 2 "RECURRING" 20
> 4 "REACTIVATED" 144
>
> shouldn't be
>
> 3 "EXPIRED" 38
>
> i run this in version
>
> PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit
>
> Please help me
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 10:16 Peter Smith <smithpb2250@gmail.com>
parent: Yudianto Prasetyo <mr.yudianto@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Smith @ 2021-09-15 10:16 UTC (permalink / raw)
To: Yudianto Prasetyo <mr.yudianto@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Sep 15, 2021 at 7:08 PM Yudianto Prasetyo <mr.yudianto@gmail.com> wrote:
>
> sorry I miss the query
>
> select * from myschema.cust0;
>
> On Wed, Sep 15, 2021 at 1:28 PM Yudianto Prasetyo <mr.yudianto@gmail.com> wrote:
>>
>> Hello,
>>
>> can you explain if this is a bug or my fault ?
>>
>> I'm making a simple example of hash partition
>>
>> CREATE TABLE myschema.customers(
>> id int,
>> status text,
>> arr numeric
>> ) PARTITION BY HASH(id);
>>
>> CREATE TABLE myschema.cust0 PARTITION OF myschema.customers
>> FOR VALUES WITH (modulus 3, remainder 0);
>>
>> CREATE TABLE myschema.cust1 PARTITION OF myschema.customers
>> FOR VALUES WITH (modulus 3, remainder 1);
>>
>> CREATE TABLE myschema.cust2 PARTITION OF myschema.customers
>> FOR VALUES WITH (modulus 3, remainder 2);
>>
>> INSERT INTO myschema.customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
>>
>> when I run the query, the result is:
>>
>> 2 "RECURRING" 20
>> 4 "REACTIVATED" 144
>>
>> shouldn't be
>>
>> 3 "EXPIRED" 38
>>
>> i run this in version
>>
>> PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit
>>
>> Please help me
I could be wrong but IIRC I have been tripped up by this before. The
docs [1] says "Each partition will hold the rows for which the hash
value of the partition key divided by the specified modulus will
produce the specified remainder.". Perhaps you were expecting it to do
the do modulus on the key, but not on the hash value of the key?
------
[1] https://www.postgresql.org/docs/13/ddl-partitioning.html
Kind Regards,
Peter Smith.
Fujitsu Australia.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 10:38 Yudianto Prasetyo <mr.yudianto@gmail.com>
parent: Peter Smith <smithpb2250@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Yudianto Prasetyo @ 2021-09-15 10:38 UTC (permalink / raw)
To: Peter Smith <smithpb2250@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
hallo,
from the document, I think the determinant of each row in the partition is
the residual value (partition key divided by the specified modulus)
So I think the value that will come out is not what I have listed.
The value that should come out like below ( as in the mods column )
SELECT *,(id % 3)as mods FROM myschema.customers;
id status arr mods
2 "RECURRING" 20 2
4 "REACTIVATED" 144 1
3 "EXPIRED" 38 0
1 "ACTIVE" 100 1
table cust0
id = 3
table cust1
id = 4,1
table cust2
id = 2
best regards
Yudianto
On Wed, Sep 15, 2021 at 5:16 PM Peter Smith <smithpb2250@gmail.com> wrote:
> On Wed, Sep 15, 2021 at 7:08 PM Yudianto Prasetyo <mr.yudianto@gmail.com>
> wrote:
> >
> > sorry I miss the query
> >
> > select * from myschema.cust0;
> >
> > On Wed, Sep 15, 2021 at 1:28 PM Yudianto Prasetyo <mr.yudianto@gmail.com>
> wrote:
> >>
> >> Hello,
> >>
> >> can you explain if this is a bug or my fault ?
> >>
> >> I'm making a simple example of hash partition
> >>
> >> CREATE TABLE myschema.customers(
> >> id int,
> >> status text,
> >> arr numeric
> >> ) PARTITION BY HASH(id);
> >>
> >> CREATE TABLE myschema.cust0 PARTITION OF myschema.customers
> >> FOR VALUES WITH (modulus 3, remainder 0);
> >>
> >> CREATE TABLE myschema.cust1 PARTITION OF myschema.customers
> >> FOR VALUES WITH (modulus 3, remainder 1);
> >>
> >> CREATE TABLE myschema.cust2 PARTITION OF myschema.customers
> >> FOR VALUES WITH (modulus 3, remainder 2);
> >>
> >> INSERT INTO myschema.customers VALUES (1,'ACTIVE',100),
> (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
> >>
> >> when I run the query, the result is:
> >>
> >> 2 "RECURRING" 20
> >> 4 "REACTIVATED" 144
> >>
> >> shouldn't be
> >>
> >> 3 "EXPIRED" 38
> >>
> >> i run this in version
> >>
> >> PostgreSQL 13.4, compiled by Visual C++ build 1914, 64-bit
> >>
> >> Please help me
>
> I could be wrong but IIRC I have been tripped up by this before. The
> docs [1] says "Each partition will hold the rows for which the hash
> value of the partition key divided by the specified modulus will
> produce the specified remainder.". Perhaps you were expecting it to do
> the do modulus on the key, but not on the hash value of the key?
>
> ------
> [1] https://www.postgresql.org/docs/13/ddl-partitioning.html
>
> Kind Regards,
> Peter Smith.
> Fujitsu Australia.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 12:36 Sergei Kornilov <sk@zsrv.org>
parent: Yudianto Prasetyo <mr.yudianto@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sergei Kornilov @ 2021-09-15 12:36 UTC (permalink / raw)
To: Yudianto Prasetyo <mr.yudianto@gmail.com>; Peter Smith <smithpb2250@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Hello
> from the document, I think the determinant of each row in the partition is the residual value (partition key divided by the specified modulus)
No, the hash from partition key is used, not partition key itself. (How to divide text "hello!" by the specified modulus?)
regards, Sergei
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 12:44 Yudianto Prasetyo <mr.yudianto@gmail.com>
parent: Sergei Kornilov <sk@zsrv.org>
0 siblings, 1 reply; 8+ messages in thread
From: Yudianto Prasetyo @ 2021-09-15 12:44 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; +Cc: Peter Smith <smithpb2250@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
"No, the hash from partition key is used, not partition key itself."
Sorry, I still don't understand the meaning of the sentence above. can you
give a simple example.
best regards,
yudianto
On Wed, Sep 15, 2021 at 7:36 PM Sergei Kornilov <sk@zsrv.org> wrote:
> Hello
>
> > from the document, I think the determinant of each row in the partition
> is the residual value (partition key divided by the specified modulus)
>
> No, the hash from partition key is used, not partition key itself. (How to
> divide text "hello!" by the specified modulus?)
>
> regards, Sergei
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 13:38 Sergei Kornilov <sk@zsrv.org>
parent: Yudianto Prasetyo <mr.yudianto@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sergei Kornilov @ 2021-09-15 13:38 UTC (permalink / raw)
To: Yudianto Prasetyo <mr.yudianto@gmail.com>; +Cc: Peter Smith <smithpb2250@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
We don't do just id % 3, we calculate some hash value from partition key (id) and route the tuple according this hash value. Such condition for your example:
select *, satisfies_hash_partition('myschema.customers'::regclass, 3, 0, id) as modulus3_remainder0_target_partition from myschema.cust0;
Can't illustrate more at the SQL level due to the lack of a uint64 calculations. At C level we are here: https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/partitioning/partbounds.c#L4595
regards, Sergei
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: bugs or my fault
@ 2021-09-15 14:16 Yudianto Prasetyo <mr.yudianto@gmail.com>
parent: Sergei Kornilov <sk@zsrv.org>
0 siblings, 0 replies; 8+ messages in thread
From: Yudianto Prasetyo @ 2021-09-15 14:16 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; +Cc: Peter Smith <smithpb2250@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Hello,
thanks for the information about hash partitions.
I just think that the hash partition will not speed up the Query, because
we don't know the location of certain rows such as the range partition or
the list partition. For example, transactions in 2000 with a range
partition, we can query the "transaction2000" partition table faster than
we can query the "transaction" table.
best regards
yudianto
On Wed, Sep 15, 2021 at 8:38 PM Sergei Kornilov <sk@zsrv.org> wrote:
> We don't do just id % 3, we calculate some hash value from partition key
> (id) and route the tuple according this hash value. Such condition for your
> example:
>
> select *, satisfies_hash_partition('myschema.customers'::regclass, 3, 0,
> id) as modulus3_remainder0_target_partition from myschema.cust0;
>
> Can't illustrate more at the SQL level due to the lack of a uint64
> calculations. At C level we are here:
> https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/partitioning/partbounds.c#L4595
>
> regards, Sergei
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-09-15 14:16 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-15 06:28 bugs or my fault Yudianto Prasetyo <mr.yudianto@gmail.com>
2021-09-15 06:30 ` Yudianto Prasetyo <mr.yudianto@gmail.com>
2021-09-15 10:16 ` Peter Smith <smithpb2250@gmail.com>
2021-09-15 10:38 ` Yudianto Prasetyo <mr.yudianto@gmail.com>
2021-09-15 12:36 ` Sergei Kornilov <sk@zsrv.org>
2021-09-15 12:44 ` Yudianto Prasetyo <mr.yudianto@gmail.com>
2021-09-15 13:38 ` Sergei Kornilov <sk@zsrv.org>
2021-09-15 14:16 ` Yudianto Prasetyo <mr.yudianto@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox