Re: [I] Unknown column 'pg_catalog.oid' in 'on clause' use ShardingSphere-Proxy [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #35374:
URL: https://github.com/apache/shardingsphere/issues/35374#issuecomment-3671001657

   Issue Understanding: 
   
   On ShardingSphere-Proxy (docker latest), running a PostgreSQL system catalog query pg_catalog.pg_class JOIN pg_catalog.pg_namespace returns “Unknown column 'pg_catalog.oid' in 'on clause'” instead of the expected relname rows.
   
     Root Cause: 
   
   The error text is MySQL-style, suggesting the request was handled with the MySQL dialect while the SQL depends on PostgreSQL system table columns (pg_namespace.oid). If proxy-frontend-database-protocol-type is not set to PostgreSQL and the client is not
     connecting to the PostgreSQL port, or if the backend is actually MySQL, the required columns do not exist and this error appears.
   
     Analysis:
   
     - ShardingSphere-Proxy defaults to the MySQL frontend protocol; without switching to PostgreSQL mode it parses SQL as MySQL.
     - PostgreSQL system catalog columns only exist with the PostgreSQL dialect/backend; in MySQL mode or with a MySQL backend you will get “Unknown column … in 'on clause'”.
     - Please check conf/server.yaml for proxy-frontend-database-protocol-type: PostgreSQL and the listen port, verify the backend datasource is PostgreSQL, and confirm the client connects to the Proxy’s PostgreSQL port.
   
     Conclusion: The behavior most likely comes from a configuration/usage mismatch rather than a Proxy kernel bug. Set proxy-frontend-database-protocol-type to PostgreSQL, ensure the backend is PostgreSQL, and connect with a PostgreSQL client to the correct port, then retry. If it still fails, please share server.yaml, your logic DB config, backend type/version, connection port, and the relevant logs so we can continue troubleshooting.


-- 
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-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org