duerwuyi opened a new issue, #34685:
URL: https://github.com/apache/shardingsphere/issues/34685
## Bug Report
### Which version of ShardingSphere did you use?
Proxy 5.5.2 with Postgres 17.2 as backend.
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
#### init state
```sql
CREATE SHARDING TABLE RULE t_order(
STORAGE_UNITS(ds_0,ds_1),
SHARDING_COLUMN=order_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4")),
KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);
CREATE TABLE t_order (
order_id INT NOT NULL,
user_id INT,
status VARCHAR(50),
PRIMARY KEY (order_id)
);
```
#### query
`select * from information_schema.tables where table_schema = 'public';`
should return 4 tables, beacuse of `"sharding-count"="4"`, and the table
name should be `t_order_0` to `t_order_3`
### Actual behavior
```sql
table_catalog | table_schema | table_name | table_type |
self_referencing_column_name | reference_generation | user_defined_type_catalog
| user_defined_type_schema | user_defined_type_name | is_insertable_into |
is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
postgres | public | t_order_0 | BASE TABLE |
| | |
| | YES | NO |
postgres | public | t_order_2 | BASE TABLE |
| | |
| | YES | NO |
(2 rows)
```
### Reason analyze (If you can)
The query is only pushed down to 1 node(that is ds_0), so the other 2
sharding tables on ds_1 are missed.
The `EXPLAIN` of the query:
```sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=34.38..70.67 rows=37 width=608)
Hash Cond: (c.reloftype = t.oid)
-> Hash Join (cost=1.07..34.55 rows=37 width=141)
Hash Cond: (c.relnamespace = nc.oid)
-> Seq Scan on pg_class c (cost=0.00..32.67 rows=148 width=77)
Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND
(pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT,
INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Hash (cost=1.06..1.06 rows=1 width=68)
-> Seq Scan on pg_namespace nc (cost=0.00..1.06 rows=1
width=68)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND
(nspname = 'public'::name))
-> Hash (cost=25.59..25.59 rows=617 width=132)
-> Hash Join (cost=1.09..25.59 rows=617 width=132)
Hash Cond: (t.typnamespace = nt.oid)
-> Seq Scan on pg_type t (cost=0.00..21.17 rows=617
width=72)
-> Hash (cost=1.04..1.04 rows=4 width=68)
-> Seq Scan on pg_namespace nt (cost=0.00..1.04
rows=4 width=68)
(15 rows)
```
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
global.yaml:
```yaml
authority:
users:
- user: postgres@%
password: 123abc
admin: true
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
logging:
loggers:
- loggerName: ShardingSphere-SQL
additivity: true
level: INFO
props:
enable: true
props:
proxy-frontend-database-protocol-type: PostgreSQL
```
database-sharding.yaml:
```yaml
databaseName: postgres
#
dataSources:
ds_0:
url: jdbc:postgresql://host.docker.internal:5443/postgres
username: postgres
password: postgres
ds_1:
url: jdbc:postgresql://host.docker.internal:5444/postgres
username: postgres
password: postgres
ds_2:
url: jdbc:postgresql://host.docker.internal:5445/postgres
username: postgres
password: postgres
ds_3:
url: jdbc:postgresql://host.docker.internal:5446/postgres
username: postgres
password: postgres
ds_4:
url: jdbc:postgresql://host.docker.internal:5447/postgres
username: postgres
password: postgres
```
docker-compose.yaml(to create cluster):
```yaml
version: '3.8'
services:
shardingsphere-proxy:
image: apache/shardingsphere-proxy:5.5.2
container_name: shardingsphere-proxy
environment:
- PORT=5432
ports:
- "3308:5432"
volumes:
- ./conf:/opt/shardingsphere-proxy/conf
- ./logs:/opt/shardingsphere-proxy/logs
- ./ext-lib:/opt/shardingsphere-proxy/ext-lib
depends_on:
- pg1
- pg2
- pg3
- pg4
- pg5
pg1:
image: postgres:17
container_name: pg1
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5443:5432"
volumes:
- pg1_data:/var/lib/postgresql/data
pg2:
image: postgres:17
container_name: pg2
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5444:5432"
volumes:
- pg2_data:/var/lib/postgresql/data
pg3:
image: postgres:17
container_name: pg3
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5445:5432"
volumes:
- pg3_data:/var/lib/postgresql/data
pg4:
image: postgres:17
container_name: pg4
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5446:5432"
volumes:
- pg4_data:/var/lib/postgresql/data
pg5:
image: postgres:17
container_name: pg5
environment:
POSTGRES_USER: postgres
POSTGRES_HOST_AUTH_METHOD: "trust"
ports:
- "5447:5432"
volumes:
- pg5_data:/var/lib/postgresql/data
volumes:
pg1_data:
pg2_data:
pg3_data:
pg4_data:
pg5_data:
```
init state reproduction & query is shown above.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
notifications-unsubscr...@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
