found a possible bug, modulus of an integer on a partition table appears to be wrong

Started by Howard A. Chouabout 3 years ago2 messagesbugs
Jump to latest
#1Howard A. Chou
maingroup@yahoo.com

postgres@osboxes:~/pgdata1$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04 LTS
Release:        22.04
Codename:       jammy
postgres@osboxes:~/pgdata1$

psql (14.2 (Ubuntu 14.2-1ubuntu1))
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select version();
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 14.2 (Ubuntu 14.2-1ubuntu1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit

postgres=#

postgres=# create table t1 (c1 int) partition by hash (c1);
CREATE TABLE
postgres=# create table t1_0 partition of t1 for values with (modulus 2, remainder 0);
CREATE TABLE
postgres=# create table t1_1 partition of t1 for values with (modulus 2, remainder 1);
CREATE TABLE
postgres=# \dt
              List of relations
 Schema | Name |       Type        |  Owner
--------+------+-------------------+----------
 public | t1   | partitioned table | postgres
 public | t1_0 | table             | postgres
 public | t1_1 | table             | postgres
(3 rows)

postgres=# insert into t1 values (4),(6), (8);
INSERT 0 3
postgres=# select * from t1_0;
 c1
----
(0 rows)

postgres=# select * from t1_1;
 c1
----
  4
  6
  8
(3 rows)

postgres=# select 4 % 2, 6 % 2, 8 % 2;
 ?column? | ?column? | ?column?
----------+----------+----------
        0 |        0 |        0
(1 row)

The remainder of 4, 6, 8 mod 2 should be 0.  Instead of inserting into t1_0, all values were inserted into t1_1

Thanks,

Howard A Chou

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Howard A. Chou (#1)
Re: found a possible bug, modulus of an integer on a partition table appears to be wrong

"Howard A. Chou" <maingroup@yahoo.com> writes:

The remainder of 4, 6, 8 mod 2 should be 0.  Instead of inserting into t1_0, all values were inserted into t1_1

You are confusing "modulus of the column value" with "modulus of
the hash of the column value". The latter is what determines
tuple routing in a hash-partitioned table.

regards, tom lane