RDS PostgreSQL to AlloyDB: Unable to create first/last aggregates: syntax error at or near "AGGREGATE"

Customer is trying to run DVT validations on source PostgreSQL(AWS RDS) and destination AlloyDB. All the validations fails with the same error. They are able to use psql client to login to both source and destination and run queries independently.

ERROR LOGS: VT (google-pso-data-validator) is installed. Version: 4.3.0 adding postgresql source connection.... 05/31/2024 05:02:49 PM-INFO: Success! Config output written to /home/ankur_jain_narvar_com/.config/google-pso-data-validator/postgre_source_narvarapps.connection.json adding postgresql distination connection.... 05/31/2024 05:02:51 PM-INFO: Success! Config output written to /home/ankur_jain_narvar_com/.config/google-pso-data-validator/alloydb_destination_narvarapps.connection.json /home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/ibis/backends/postgres/init.py:162: UserWarning: Unable to create first/last aggregates: syntax error at or near "AGGREGATE" LINE 6: CREATE OR REPLACE AGGREGATE public._ibis_first (anyelement) ... ^

warnings.warn(f"Unable to create first/last aggregates: {e}") Traceback (most recent call last): File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context self.dialect.do_execute( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/home/ankur_jain_narvar_com/env/bin/data-validation", line 8, in sys.exit(main()) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/main.py", line 633, in main validate(args) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/main.py", line 611, in validate run(args) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/main.py", line 578, in run run_validations(args, config_managers) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/main.py", line 534, in run_validations run_validation(config_manager, dry_run=args.dry_run, verbose=args.verbose) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/main.py", line 507, in run_validation validator.execute() File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/data_validation.py", line 96, in execute result_df = self._execute_validation( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/data_validation.py", line 270, in _execute_validation source_query = validation_builder.get_source_query() File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/validation_builder.py", line 361, in get_source_query table = self.config_manager.get_source_ibis_table() File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/config_manager.py", line 342, in get_source_ibis_table self._source_ibis_table = clients.get_ibis_table( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/data_validation/clients.py", line 134, in get_ibis_table return client.table(table_name, database=database_name, schema=schema_name) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/init.py", line 519, in table sqla_table = self._get_sqla_table(name, database=database, schema=schema) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/ibis/backends/base/sql/alchemy/init.py", line 422, in _get_sqla_table table = sa.Table( File "", line 2, in new File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned return fn(*args, kwargs) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 619, in new metadata._remove_table(name, schema) File "/home/ankur_jain_narvarcom/env/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit compat.raise( File "/home/ankur_jain_narvarcom/env/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 614, in new table._init(name, metadata, *args, *kw) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 689, in _init self._autoload( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 724, in _autoload conn_insp.reflect_table( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 774, in reflect_table for col_d in self.get_columns( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 497, in get_columns col_defs = self.dialect.get_columns( File "", line 2, in get_columns File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache ret = fn(self, con, args, kw) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3856, in get_columns table_oid = self.get_table_oid( File "", line 2, in get_table_oid File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache ret = fn(self, con, *args, **kw) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 3733, in get_table_oid c = connection.execute(s, dict(table_name=table_name, schema=schema)) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1385, in execute return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS) File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement ret = self._execute_context( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context self._handle_dbapi_exception( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapiexception util.raise( File "/home/ankur_jain_narvarcom/env/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context self.dialect.do_execute( File "/home/ankur_jain_narvar_com/env/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.InternalError: (psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block

[SQL: SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE (n.nspname = %(schema)s) AND c.relname = %(table_name)s AND c.relkind in ('r', 'v', 'm', 'f', 'p') ] [parameters: {'schema': 'information_schema', 'table_name': 'tables'}] (Background on this error at: https://sqlalche.me/e/

SCRIPT TO RUN DVT: data-validation validate column \ -sc $CONN_NAME \ -tc $DEST_CONN_NAME \ --tables-list information_schema.tables \ --filters "table_schema = '$schema'" \ -bqrh $PROJECT_ID.$BQ_DVT_DATASET.results

GoogleCloudPlatform / professional-services-data-validator

RDS PostgreSQL to AlloyDB: Unable to create first/last aggregates: syntax error at or near "AGGREGATE" #1154