Unable to create new key range routed to different shard

[harinath001]

Hi I am experimenting with SPQR and found an issue, what looks like a bug.

These are the steps to reproduce.

I ran the router using examples/2shardproxy.yaml

and then

db1=> show shards;
 shard 
-------
 sh1
 sh2
(2 rows)

db1=> show distributions;
 Distribution ID | Column types | Default shard 
-----------------+--------------+---------------
(0 rows)

db1=> CREATE DISTRIBUTION ds1 COLUMN TYPES integer;
    add distribution    
------------------------
 distribution id -> ds1
(1 row)


db1=> ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY id;
       attach table        
---------------------------
 relation name   -> orders
 distribution id -> ds1


db1=> ALTER DISTRIBUTION ds1 ATTACH RELATION items DISTRIBUTION KEY order_id;
       attach table       
--------------------------
 relation name   -> items
 distribution id -> ds1
(2 rows)


db1=> CREATE KEY RANGE krid1 FROM 1 ROUTE TO sh1 FOR
 DISTRIBUTION ds1;
 add key range 
---------------
 bound -> 1


db1=> CREATE KEY RANGE krid2 FROM 1000 ROUTE TO sh2 
FOR DISTRIBUTION ds1;
ERROR:  key range krid2 intersects with key range krid1 in QDB

---

Expected behavior To be able to create the key-range krid2 from 1000 routed to shard2.

*Additional context

I compiled the code and the latest commit is dated Wed Sep 24 2025

[xelavopelk]

You have made key range krid1 "from 1" on sh1. Next you insert into table "items" with "order_id" 5000. This record is in sh1 Next you wish create key range "from 1000" on sh2. But you already have data "from 1000" in sh1. So you want to have krid2 "from 1000". But you have krid1 "from 1". You have to split krid1, and move part "from 1000" to sh2. Data consistence is matter for us. :)

[harinath001]

@xelavopelk i see a example here https://docs.pg-sharding.tech/welcome/get_started

CREATE KEY RANGE krid1 FROM 1 ROUTE TO shard1 FOR DISTRIBUTION ds1;
         add key range          
--------------------------------
 created key range with bound 1
(1 row)

CREATE KEY RANGE krid2 FROM 1000 ROUTE TO shard2 FOR DISTRIBUTION ds1;
            add key range          
-----------------------------------
 created key range with bound 1000
(1 row)

Is this incorrect or needs correction ?

[Denchick]

Hi @harinath001, thanks a lot for trying SPQR and for the detailed repro steps.

You’re right that the behaviour here is confusing – this is actually a mistake in our docs / getting-started guide, not in your setup. I was planning to update the get_started section but haven’t made it there yet, sorry about that.

Right now CREATE KEY RANGE in QDB works so that a range created with FROM <value> effectively covers everything from that value to +∞. Because of that, when you do

CREATE KEY RANGE krid2 FROM 1000 ROUTE TO sh2 FOR DISTRIBUTION ds1;
CREATE KEY RANGE krid1 FROM 1 ROUTE TO sh1 FOR DISTRIBUTION ds1;

@xelavopelk thank you so much for your PR!