Postgres ODBC driver creates hundreds lines of logs when connecting

[BenoitC]

Hello

I have setup a postgres connection using the standard ODBC. Problem is that it generates hundreds lines of logs showing multiple attempts to connect using different ports than the one appearing in the setup. Seems like it does connection pooling though I read that Designer doesn't do connection pooling.

Same driver works as expected when used in Excel: it doesn't create any logs, apart from standard.

I also found this query appearing in the logs, with a bunch of errors attached to it:

select USER(), APPLICATION_ID() from system.iota
select USER(), APPLICATION_ID() from system.iota
select USER as USER_NAME from SYSIBM. SYSDUMMY1
select USER as USER_NAME from SYSIBM. SYSDUMMY1
select * from rdb$relations
select * from rdb$relations
select first 1 dbinfo('version', 'full') from systables
select first 1 dbinfo('version', 'full') from systables
select #01/01/01# as AccessDate
select #01/01/01# as AccessDate
exec sp_server_info1
exec sp_server_info 1
select top (0) * from INFORMATION_SCHEMA. INDEXES
select top (0) * from INFORMATION_SCHEMA. INDEXES
SELECT SERVERPROPERTY('edition')
SELECT SERVERPROPERTY('edition')
select DATABASE() as 'database', VERSION() as 'version"
select DATABASE() as 'database', VERSION() as `version
select cast(version() as char(10)), current_useroid as t
select cast(version() as char(10)), current_useroid as t
select * from sys.V_$VERSION at where RowNum<2
select * from sys.V_$VERSION at where RowNum<2
select cast(version() as char(10)), (select 1 from pg_catalog.pg_class) as t
select NAME from sqlite_master
select NAME from sqlite_master
select xp_msver( 'CompanyName')
select xp_msver('CompanyName')
select @@version
select @@version
select * from dbc.dbcinfo
select * from dbc.dbcinfo
SELECT @@VERSION
SELECT @@VERSION;

 Postgres version is 15, ODBC connector is from https://www.postgresql.org/ftp/odbc/versions/msi/. It is installed using System DSN and using recommended configuration.

Any ideas what is going on?

[apathetichell]

are you setting this up via in-db greenplum? What's your log level at?

[BenoitC]

I use the input tool. Log level is medium but I need to double check that!

The strange part is the queries ran by Alteryx at the beginning of the connection. The query is not Postgresql specific, it has statements compatibles with IBM Db2, SQL server, SQLite etc... 

[apathetichell]

my hunch is you are using a generic odbc driver configuration so it's going through that vs a postgres specific configuration (ie Greenplum in Alteryx). It'll take a bit for me to find any postgres in-db logs but because I'm not seeing anything Postgres specific in your logs - my hypothesis would be to check if you are using a generic - and explore if some internal conversion had to take place.

[BenoitC · Accepted solution]

I have feedback from customer. The issue was related to ODBC version. They were not using the same version for ODBC and database, seems that was the root cause of the problem.

Thanks for your inputs!