The first sharding table after creating a database always fails when `check-table-metadata-enabled` is set to `true`.

[duerwuyi]

Bug Report

Which version of ShardingSphere did you use?

shardingsphere-proxy 5.5.2 postgres 17.2 as backend

Expected behavior

I will provide the following steps to reproduce this bug.

global.yaml:

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props: 
      namespace: governance_ds
      server-lists: host.docker.internal:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60

authority:
 users:
   - user: postgres@%
     password: 123abc
     admin: true
   - user: sharding
     password: sharding
 privilege:
   type: ALL_PERMITTED

logging:
 loggers:
 - loggerName: ShardingSphere-SQL
   additivity: true
   level: DEBUG
   props:
     enable: true

sqlFederation:
 sqlFederationEnabled: true
 allQueryUseSQLFederation: false
 executionPlanCache:
   initialCapacity: 2000
   maximumSize: 65535

props:
  system-log-level: DEBUG
  sql-show: true
  check-table-metadata-enabled: true
  proxy-frontend-database-protocol-type: PostgreSQL

database-sharding.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.yml

version: '3.8'
services:
  zookeeper:
    image: zookeeper:3.8.4
    container_name: zookeeper
    restart: always
    ports:
      - "2181:2181"
    environment:
      ZOO_MY_ID: 1
  shardingsphere-proxy:
    image: apache/shardingsphere-proxy:5.5.2
    container_name: shardingsphere-proxy
    environment:
      - PORT=5432
    ports:
      - "5440:5432" 
    volumes:
      - ./conf:/opt/shardingsphere-proxy/conf
      - ./logs:/opt/shardingsphere-proxy/logs
      - ./ext-lib:/opt/shardingsphere-proxy/ext-lib
    depends_on:
      - zookeeper
      - 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:

after starting the cluster, create a new database named testdb on all postgres-deatabases and sharding-sphere proxy, then try:

REGISTER STORAGE UNIT ds_0 (HOST="host.docker.internal",PORT=5443,DB="testdb",USER="postgres",PASSWORD="123abc");

REGISTER STORAGE UNIT ds_2 (HOST="host.docker.internal",PORT=5445,DB="testdb",USER="postgres",PASSWORD="123abc");

REGISTER STORAGE UNIT ds_4 (HOST="host.docker.internal",PORT=5447,DB="testdb",USER="postgres",PASSWORD="123abc");

REGISTER STORAGE UNIT ds_3 (HOST="host.docker.internal",PORT=5446,DB="testdb",USER="postgres",PASSWORD="123abc");

CREATE SHARDING TABLE RULE IF NOT EXISTS t0 (STORAGE_UNITS(ds_0, ds_2, ds_3, ds_4), SHARDING_COLUMN=c3, TYPE(NAME="mod",PROPERTIES("sharding-count"="5")), KEY_GENERATE_STRATEGY(COLUMN=pkey,TYPE(NAME="UUID")), AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true));

DROP TABLE IF EXISTS t0;

create table t0 ( 
vkey int4 ,
pkey int4 ,
c0 text ,
c1 text ,
c2 numeric ,
c3 numeric ,
c4 int4 ,
c5 timestamp ,
c6 numeric ,
c7 numeric ,
c8 int4 
);

ERROR:  Rule and storage meta data mismatched, reason is: Can not get uniformed table structure for logic table 't0', it has different meta data of actual tables are as follows: 

CREATE SHARDING TABLE RULE IF NOT EXISTS t1 (STORAGE_UNITS(ds_0, ds_4), SHARDING_COLUMN=pkey, TYPE(NAME="mod",PROPERTIES("sharding-count"="7")), KEY_GENERATE_STRATEGY(COLUMN=pkey,TYPE(NAME="SNOWFLAKE")), AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true));

DROP TABLE IF EXISTS t1;

create table t1 ( 
vkey int4 ,
pkey int4 ,
c7 text ,
c8 timestamp ,
c9 text ,
c10 timestamp ,
c11 int4 
);

Both t0 and t1 should execute successfully.

Actual behavior

t0 got error: Rule and storage meta data mismatched, reason is: Can not get uniformed table structure for logic table 't0', it has different meta data of actual tables are as follows.

Besides, if we swap the creation order of t0 and t1, then t1 will fail, while t0 will be created successfully.

Reason analyze (If you can)

This bug is related to dist variable check-table-metadata-enabled with postgres, when it is set to false, the table could be created successfully, but in this case, the table is acutally sharded and could see the physical tables in those backend postgres, I guess the bug happens in the checking stage of creating a sharding table.