Informix Dynamic Server

[apollo13]

I've configured the database adapter as described and when executing a query I am getting warnings ala:

WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I04: "Database selected "
NOTICE:  connected to an non-Informix SE instance

Full config:

createdb -T template0 -E LATIN9 -l de_DE@euro bap

CREATE EXTENSION informix_fdw;

CREATE SERVER ids01_testdb FOREIGN DATA WRAPPER informix_fdw 
OPTIONS (informixserver 'ids01', informixdir '/opt/informix_sdk', database 'testdb', user 'informix', password 'informix', client_locale 'de_de.8859-1',  db_locale 'de_DE.819');
CREATE USER MAPPING FOR CURRENT_USER SERVER ids01_testdb OPTIONS (username 'informix', password 'informix');
CREATE FOREIGN TABLE abetriebe (nummer integer not null, name varchar(40), abeadrbetr integer, abebank integer) SERVER ids01_testdb OPTIONS (table 'abetriebe');

Does this fdw create a new connection for each select? Can I somehow get rid of the warnings?

[psoo]

Yeah, the informix_fdw currently maps SQLSTATE classes to corresponding SQLSTATE values in PostgreSQL. A SQLSTATE value has a class value (the first two digits) and a subclass (the last three digits). Informix sets the SQLSTATE to 01 during connection and delivers SQLSTATE subclasses according to special options derived from the database connection. 01I04 and 01I01 for example are such "informational warnings" generated when selecting or establishing a database connection (see ifx_connection.ec:ifxGetSqlStateClass() for details).

The way this is currently handled in Informix FDW leaves you the option to set your client_min_message properly only, e.g.:

#= SELECT * FROM centosifx.inttest;
WARNUNG:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I01: "Database has transactions "
 f1 | f2 | f3
----+----+----
(0 rows)

Time: 45.056 ms
#= SET client_min_messages TO ERROR;
SET
Time: 5.253 ms
bernd@localhost:bernd #= SELECT * FROM centosifx.inttest;
 f1 | f2 | f3
----+----+----
(0 rows)

Time: 3.915 ms

That will show ERROR messages only and suppress all WARNINGs. I think an option to make this more comfortable would be to special case ifxGetSqlStateClass() and suppress any informational warning. However, the only way to monitor database connection info would be ifx_fdw_get_connections() then:

#= SELECT * FROM ifx_fdw_get_connections();
-[ RECORD 1 ]--------+--------------------------------------
connection_name      | informixregression_dmlol_informix1210
established_by_relid | 17228
servername           | ol_informix1210
informixdir          | /Applications/IBM/informix
database             | regression_dml
username             | informix
usage                | 2
db_locale            | en_US.819
client_locale        | en_US.utf8
uses_tx              | t
tx_in_progress       | 0
db_ansi              | f
tx_num_commit        | 2
tx_num_rollback      | 0

Not sure if that's desired...

[psoo]

Oh, and to answer your other question:

Does this fdw create a new connection for each select? Can I somehow get rid of the warnings?

No, an Informix connection, once established, is cached and reused. However, the cache is local to each PostgreSQL backend, so if another PostgreSQL backend wants to use an Informix FDW connection it needs to open his own one. But once established, they are cached...

The repeating WARNING you are seeing are generated each time a connection gets selected.

[apollo13]

I see, thank you!