routing_hint.out

\c spqr-console

		SPQR router admin console
	Here you can configure your routing rules
------------------------------------------------
	You can find documentation here 
https://github.com/pg-sharding/spqr/tree/master/docs

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

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

CREATE KEY RANGE krid2 FROM 11 ROUTE TO sh2 FOR DISTRIBUTION ds1;
 add key range 
---------------
 bound -> 11
(1 row)

ALTER DISTRIBUTION ds1 ATTACH RELATION test DISTRIBUTION KEY id;
      attach table       
-------------------------
 relation name   -> test
 distribution id -> ds1
(2 rows)

\c regress
CREATE TABLE test(id int, age int);
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
-- TODO: specify distribution as well as sharding_key
INSERT INTO test(id, age) VALUES (1210, 16) /*__spqr__sharding_key: 1, __spqr__distribution: ds1  */;
NOTICE: send query to shard(s) : sh1
INSERT INTO test(id, age) VALUES (10, 16) /*__spqr__sharding_key: 30, __spqr__distribution: ds1  */;
NOTICE: send query to shard(s) : sh2
INSERT INTO test(id, age) VALUES (10, 16) /*__spqr__sharding_key: 3000, __spqr__distribution: ds1  */;
NOTICE: send query to shard(s) : sh2
-- test transaction support for route-local variables;
SET __spqr__distribution = 'ds1';
SHOW __spqr__distribution;
ERROR:  parameter "__spqr__distribution" isn't user accessible
SET __spqr__sharding_key = 1;
SHOW __spqr__sharding_key;
 sharding key 
--------------
 1
(1 row)

SELECT * FROM test;
NOTICE: send query to shard(s) : sh1
  id  | age 
------+-----
 1210 |  16
(1 row)

SET __spqr__sharding_key = 12;
SELECT * FROM test;
NOTICE: send query to shard(s) : sh2
 id | age 
----+-----
 10 |  16
 10 |  16
(2 rows)

BEGIN;
SET __spqr__sharding_key = 1;
SELECT * FROM test;
NOTICE: send query to shard(s) : sh1
  id  | age 
------+-----
 1210 |  16
(1 row)

ROLLBACK;
-- should return to previous value, so select from second shard
SELECT * FROM test;
NOTICE: send query to shard(s) : sh2
 id | age 
----+-----
 10 |  16
 10 |  16
(2 rows)

RESET __spqr__sharding_key;
\c regress
-- cleanup test relation to avoid confusion
TRUNCATE test;
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
COPY test (id, age) FROM STDIN;
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
-- SELECT here with order to avoid flaps
SELECT * FROM test ORDER BY id /* __spqr__execute_on: sh1 */;
NOTICE: send query to shard(s) : sh1
 id | age 
----+-----
  1 |   1
  5 |   5
 10 |  10
(3 rows)

SELECT * FROM test ORDER BY id /* __spqr__execute_on: sh2 */;
NOTICE: send query to shard(s) : sh2
 id | age 
----+-----
 15 |  15
 20 |  20
 25 |  25
(3 rows)

SET __spqr__execute_on TO sh1;
SHOW __spqr__execute_on;
 execute on 
------------
 sh1
(1 row)

SELECT * FROM test ORDER BY id;
NOTICE: send query to shard(s) : sh1
 id | age 
----+-----
  1 |   1
  5 |   5
 10 |  10
(3 rows)

SET __spqr__execute_on TO sh2;
SHOW __spqr__execute_on;
 execute on 
------------
 sh2
(1 row)

SELECT * FROM test ORDER BY id;
NOTICE: send query to shard(s) : sh2
 id | age 
----+-----
 15 |  15
 20 |  20
 25 |  25
(3 rows)

SET __spqr__execute_on TO sh1;
-- overrides prev set
SELECT * FROM test ORDER BY id /* __spqr__execute_on: sh2 */;
NOTICE: send query to shard(s) : sh2
 id | age 
----+-----
 15 |  15
 20 |  20
 25 |  25
(3 rows)

-- After stmt select from sh1, not sh2
SELECT * FROM test ORDER BY id;
NOTICE: send query to shard(s) : sh1
 id | age 
----+-----
  1 |   1
  5 |   5
 10 |  10
(3 rows)

SHOW __spqr__scatter_query; -- error
ERROR:  parameter "__spqr__scatter_query" isn't user accessible
SHOW __spqr__default_route_behaviour;
 default route behaviour 
-------------------------
 
(1 row)

RESET __spqr__execute_on;
SHOW __spqr__execute_on;
 execute on 
------------
 
(1 row)

SET __spqr__default_route_behaviour to 'BLOCK';
SELECT 1 FROM test WHERE id IN (5, 25) /* __spqr__scatter_query: true, __spqr__default_route_behaviour: allow */;
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
 ?column? 
----------
        1
        1
(2 rows)

SET __spqr__default_route_behaviour to 'ALLOW';
SHOW __spqr__default_route_behaviour;
 default route behaviour 
-------------------------
 ALLOW
(1 row)

SELECT 1 FROM test WHERE id IN (5, 25) /* __spqr__scatter_query: true */;
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
 ?column? 
----------
        1
        1
(2 rows)

-- restart session, reset all params
\c regress
DROP TABLE test;
NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4
\c spqr-console

		SPQR router admin console
	Here you can configure your routing rules
------------------------------------------------
	You can find documentation here 
https://github.com/pg-sharding/spqr/tree/master/docs

DROP DISTRIBUTION ALL CASCADE;
   drop distribution    
------------------------
 distribution id -> ds1
(1 row)

DROP KEY RANGE ALL;
 drop key range 
----------------
(0 rows)