????????д?????????
????sharddb=# INSERT INTO customer_reviews (customer_id?? review_rating) VALUES ('HN802'?? 5);
????sharddb=# INSERT INTO customer_reviews VALUES ('HN802'?? '2004-01-01'?? 1?? 10?? 4?? 'B00007B5DN'?? 'Tug of War'?? 133191?? 'Music'?? 'Indie Music'?? 'Pop'?? '{}');
????sharddb=# INSERT INTO customer_reviews (customer_id?? review_rating) VALUES ('FA2K1'?? 10);
??????WHERE????SELECT
????sharddb=# select * from customer_reviews ;
????customer_id | review_date | review_rating | review_votes | review_helpful_votes | product_id | product_title | product_sales_rank | product_group | product_category | product_subcategory | similar_product_ids
????----------{}----------------{}------------------------{}---------------{}-----------------------{}---------------------------+------------------
????HN802 | | 5 | | | | | | | | |
????HN802 | 2004-01-01 | 1 | 10 | 4 | B00007B5DN | Tug of War | 133191 | Music | Indie Music | Pop | {}
????FA2K1 | | 10 | | | | | | | | |
????(3 rows)
??????WHERE????avg
????sharddb=# SELECT avg(review_rating) FROM customer_reviews;
????avg
????--------------------
????5.3333333333333333
????(1 row)
????????GROUP BY????avg
????sharddb=# SELECT customer_id??avg(review_rating) from customer_reviews GROUP BY customer_id;
????customer_id | avg
????----------+------------------
????FA2K1 | 10.0000000000000000
????HN802 | 3.0000000000000000
????(2 rows)
????????HAVING????avg
????sharddb=# SELECT customer_id??avg(review_rating) as avgrating from customer_reviews GROUP BY customer_id HAVING customer_id <> 'FA2K1';
????customer_id | avgrating
????----------+-----------------
????HN802 | 3.0000000000000000
????(1 row)
??????WHERE????avg
????sharddb=# SELECT avg(review_rating) FROM customer_reviews WHERE customer_id = 'HN802';
????avg
????--------------------
????3.0000000000000000
????(1 row)
????COUNT ?? NULL?
sharddb=# SELECT count(*) FROM customer_reviews;
count
-------
3
(1 row)
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes <> 4;
count
-------
0
(1 row)
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes = 4;
count
-------
1
(1 row)
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes IS NULL;
count
-------
2
(1 row)
^
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes IS NOT NULL;
count
-------
1
(1 row)
???????з????????е?UPDATE????
????sharddb=# UPDATE customer_reviews SET review_votes = 10 WHERE customer_id = 'HN802';
????UPDATE 2
????sharddb=#
?????????????????е?UPDATE??????
????sharddb=# UPDATE customer_reviews SET review_votes = 10 + 1 WHERE review_votes = 10;
????ERROR: cannot modify multiple shards during a single query
????sharddb=#
?????????????????е?DELETE??????
????sharddb=# DELETE FROM customer_reviews WHERE review_votes <> 99;
????ERROR: cannot modify multiple shards during a single query
????sharddb=#
???????з????????к??????е?UPDATE????
????sharddb=# UPDATE customer_reviews SET review_votes = 10 + 1 WHERE customer_id = 'HN802' AND review_votes = 10;
????UPDATE 2
????sharddb=#
???????????
????pgs_distribution_metadata SCHEMA master???????????????
sharddb=# dn+
List of schemas
Name | Owner | Access privileges | Description
------------------------{}--------------------+---------------------
pgs_distribution_metadata | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
sharddb=# SELECT * FROM pgs_distribution_metadata.partition;
relation_id | partition_method | key
----------{}----------------------
24842 | h | customer_id
(1 row)
sharddb=# SELECT * FROM pgs_distribution_metadata.shard;
id | relation_id | storage | min_value | max_value
----{}----------{}-----------------
10000 | 24842 | t | -2147483648 | -1879048194
10001 | 24842 | t | -1879048193 | -1610612739
10002 | 24842 | t | -1610612738 | -1342177284
10003 | 24842 | t | -1342177283 | -1073741829
10004 | 24842 | t | -1073741828 | -805306374
10005 | 24842 | t | -805306373 | -536870919
10006 | 24842 | t | -536870918 | -268435464
10007 | 24842 | t | -268435463 | -9
10008 | 24842 | t | -8 | 268435446
10009 | 24842 | t | 268435447 | 536870901
10010 | 24842 | t | 536870902 | 805306356
10011 | 24842 | t | 805306357 | 1073741811
10012 | 24842 | t | 1073741812 | 1342177266
10013 | 24842 | t | 1342177267 | 1610612721
10014 | 24842 | t | 1610612722 | 1879048176
10015 | 24842 | t | 1879048177 | 2147483647
(16 rows)
sharddb=# SELECT * FROM pgs_distribution_metadata.shard_placement;
id | shard_id | shard_state | node_name | node_port
--------------{}-------------
1 | 10000 | 1 | localhost | 5433
2 | 10000 | 1 | localhost | 5434
......
32 | 10015 | 1 | localhost | 5434
(32 rows)
??????????????д????????????shard??????????????????????????????????????->??shard-->д?????????????shard??д??????????????????????????????????
????1)??????????·??????
????2)??worker nodes?в???г???????????????д??????
????3)master?????????shard??????customer_detail????????releation_id=24940????????????drop??????????pg?????и????????????
??master??
sharddb=# INSERT INTO customer_detail VALUES ('HN802'??'a')??('HN802'??'b')??('FA2K1'??'c');
INSERT 0 3
sharddb=#
sharddb=# SELECT master_create_distributed_table('customer_detail'?? 'customer_id');
master_create_distributed_table
---------------------------------
(1 row)
sharddb=#
sharddb=# SELECT master_create_worker_shards('customer_detail'?? 16?? 2);
master_create_worker_shards
-----------------------------
(1 row)
sharddb=#
sharddb=#
sharddb=# select * from customer_detail ;
customer_id | customer_val
----------+-----------
(0 rows)
??worker nodes??
sharddb=# drop table customer_detail;
ERROR: table "customer_detail" does not exist
sharddb=#
??master??
sharddb=# SELECT * FROM pgs_distribution_metadata.shard;
id | relation_id | storage | min_value | max_value
----{}----------{}-----------------
10000 | 24842 | t | -2147483648 | -1879048194
......?????
10031 | 24940 | t | 1879048177 | 2147483647
(32 rows)
sharddb=# CREATE TABLE tbl_detail(customer_id text?? fid integer ?? detailval text);
CREATE TABLE
sharddb=#
sharddb=#
sharddb=# SELECT master_create_distributed_table('tbl_detail'?? 'customer_id');
master_create_distributed_table
---------------------------------
(1 row)
sharddb=# SELECT master_create_worker_shards('tbl_detail'?? 16?? 2);
master_create_worker_shards
-----------------------------
(1 row)
sharddb=#
sharddb=#
sharddb=# select customer_id from customer_reviews ;
customer_id
-------------
HN802
HN802
FA2K1
(3 rows)
?????????????????????????????????????????????е????
sharddb=# INSERT INTO tbl_detail VALUES('HN802'??1??'a')??('HN802'??2??'b')??('HN802'??3??'c')??('FA2K1'??4??'d');
ERROR: cannot perform distributed planning for the given query
DETAIL: Multi-row INSERTs to distributed tables are not supported.
sharddb=#
sharddb=# INSERT INTO tbl_detail VALUES('HN802'??1??'a');
INSERT 0 1
sharddb=# INSERT INTO tbl_detail VALUES ('HN802'??2??'b');
INSERT 0 1
sharddb=# INSERT INTO tbl_detail VALUES ('HN802'??3??'c');
INSERT 0 1
sharddb=# INSERT INTO tbl_detail VALUES ('FA2K1'??4??'d');
INSERT 0 1
sharddb=#
sharddb=#
sharddb=#
sharddb=# SELECT * FROM tbl_detail ;
customer_id | fid | detailval
----------{}-----------
HN802 | 1 | a
HN802 | 2 | b
HN802 | 3 | c
FA2K1 | 4 | d
(4 rows)
????????join????
????sharddb=#
????sharddb=# SELECT A.*??B.* FROM customer_reviews A?? tbl_detail B WHERE A.customer_id = B.customer_id;
????ERROR: cannot perform distributed planning for the given query
????DETAIL: Joins are not supported in distributed queries.
????sharddb=#
?????????EXPLAIN???????????????VACUUM ?? ANALYZE ?????????????worker??????
????sharddb=# EXPLAIN SELECT * FROM tbl_detail ;
????ERROR: EXPLAIN commands on distributed tables are unsupported
????sharddb=#
??????MASTER?????????????????worker??????????????DROP?????worker??Ч
????sharddb=# CREATE INDEX CONCURRENTLY ON tbl_detail (customer_id);
????CREATE INDEX
????sharddb=# d+ tbl_detail
????Table "public.tbl_detail"
????Column | Type | Modifiers | Storage | Stats target | Description
????----------{}----------{}------------+----------
????customer_id | text | | extended | |
????fid | integer | | plain | |
????detailval | text | | extended | |
????Indexes:
????"tbl_detail_customer_id_idx" btree (customer_id)
????sharddb=#
????sharddb=# DROP INDEX tbl_detail_customer_id_idx;
????DROP INDEX
????sharddb=#
????ALTER TABLE????????????????????????????????????????????????????????
????sharddb=# ALTER TABLE tbl_detail ADD COLUMN newcolumn text DEFAULT NULL;
????ALTER TABLE
????sharddb=# select * from tbl_detail ;
????WARNING: Bad result from localhost:5434
????DETAIL: Remote message: column "newcolumn" does not exist
????WARNING: Bad result from localhost:5433
????DETAIL: Remote message: column "newcolumn" does not exist
????ERROR: could not receive query results
????sharddb=#
????DROP DATABASE ?????????
??????master??????sharddb?????worker???database?????????DROP??MASTER?????????????????????????
????postgres=# DROP DATABASE sharddb;
????ERROR: database "sharddb" is being accessed by other users
????DETAIL: There is 1 other session using the database