YangGuanQun commented on issue #37997:
URL:
https://github.com/apache/shardingsphere/issues/37997#issuecomment-3889125066
Thank you for your comment. @terrymanu
I have upgraded to the latest release version 5.5.2, and I tried to remove
schema names from logicTable and actualDataNodes, like this:
```java
ShardingTableRuleConfiguration tableRuleConfiguration = new
ShardingTableRuleConfiguration("t_order",
"sharding1.t_order,sharding2.t_order");
```
When I used the following SQL statement:
```SQL
INSERT INTO base.t_order (id, data_time) VALUES (?, ?::timestamp)
```
I encountered the following error:
```
Please check your sharding conditions
`ShardingConditions(conditions=[ShardingCondition(values=[], startIndex=0)],
sqlStatementContext=org.apache.shardingsphere.infra.binder.context.statement.dml.InsertStatementContext@bfdcc1a,
rule=org.apache.shardingsphere.sharding.rule.ShardingRule@4acc5dff,
subqueryContainsShardingCondition=true)` to avoid the same record in table
`t_order` routing to multiple data nodes
```
It seemed that I also had to remove the schema from the SQL statement, so I
modified the SQL to:
```SQL
INSERT INTO t_order (id, data_time) VALUES (?, ?::timestamp)
```
However, I then got this error:
```
Exception in thread "main"
org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view 't_order' does not exist.
at
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type.SimpleTableSegmentBinder.lambda$checkTableExists$12(SimpleTableSegmentBinder.java:178)
at
org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:44)
at
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:178)
at
org.apache.shardingsphere.infra.binder.engine.segment.dml.from.type.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:90)
at
org.apache.shardingsphere.infra.binder.engine.statement.dml.InsertStatementBinder.lambda$bind$1(InsertStatementBinder.java:49)
at java.util.Optional.ifPresent(Optional.java:159)
at
org.apache.shardingsphere.infra.binder.engine.statement.dml.InsertStatementBinder.bind(InsertStatementBinder.java:49)
at
org.apache.shardingsphere.infra.binder.engine.type.DMLStatementBindEngine.bind(DMLStatementBindEngine.java:64)
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindSQLStatement(SQLBindEngine.java:68)
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:58)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:143)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:115)
at
org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:108)
at org.example.ShardingSphereTest.main(ShardingSphereTest.java:44)
```
This makes sense because I did not specify a schema anywhere. I searched for
this issue online, and some documentation states that I need to set the
`search_path` for the data source connection. So I tried the following
configuration:
```java
private static DataSource createDruidDataSource(final String dbName) {
DruidDataSource druidDs = new DruidDataSource();
druidDs.setDriverClassName("org.postgresql.Driver");
druidDs.setUrl(String.format("jdbc:postgresql://localhost:15432/%s?currentSchema=base&characterEncoding=utf8",
dbName));
druidDs.setUsername("postgres");
druidDs.setPassword("xxxxxx");
druidDs.setConnectionInitSqls(Arrays.asList("SET search_path TO base"));
return druidDs;
}
```
But this did not work, and I still encountered the same "table or view does
not exist" error.
I tried debugging this error and found code in `SimpleTableSegmentBinder`
that suggests ShardingSphere only uses the default schema 'public':
```java
private static IdentifierValue getSchemaName(final SimpleTableSegment
segment, final SQLStatementBinderContext binderContext) {
if (segment.getOwner().isPresent()) {
return segment.getOwner().get().getIdentifier();
}
// TODO getSchemaName according to search path
DatabaseType databaseType =
binderContext.getSqlStatement().getDatabaseType();
if ((databaseType instanceof PostgreSQLDatabaseType || databaseType
instanceof OpenGaussDatabaseType)
&&
SystemSchemaManager.isSystemTable(databaseType.getType(), PG_CATALOG,
segment.getTableName().getIdentifier().getValue())) {
return new IdentifierValue(PG_CATALOG);
}
return new IdentifierValue(new
DatabaseTypeRegistry(databaseType).getDefaultSchemaName(binderContext.getCurrentDatabaseName()));
}
```
It seems that specifying a schema is not supported in PostgreSQL for
ShardingSphere, or perhaps there is another way to specify the schema that I am
unaware of.
Actually, I want to use ShardingSphere in an existing project based on
PostgreSQL. This project uses multiple schemas under a single data source, and
each SQL statement explicitly specifies a schema. I hope to implement database
sharding without modifying existing SQL statements and dynamically identify the
schema for each SQL statement. Can ShardingSphere achieve this?
Please let me know more details. Thank you.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
