ERROR: informix FDW error: "Type (bpchar) not found. "

[rossj-cargotel]

Hi again!

I've been trying to convert a working function to use the informix foreign tables instead of the local postgres tables and I'm running into this issue:

DEBUG:  StartTransactionCommand
DEBUG:  StartTransaction
DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG:  informix_fdw: get foreign relation size, cmd 1
DEBUG:  ifx_fdw set param table=load_flags
DEBUG:  ifx_fdw set param database=pra
DEBUG:  ifx_fdw set param client_locale=en_US.utf8
DEBUG:  ifx_fdw set param db_locale=en_US.819
DEBUG:  ifx_fdw set param informixserver=cargonet
DEBUG:  ifx_fdw set param informixdir=/opt/IBM/informix
DEBUG:  ifx_fdw set param username=nobody
DEBUG:  ifx_fdw set param password=mailonly66
DEBUG:  informix connection dsn "pra@cargonet"
DEBUG:  created new cached informix connection "nobodypracargonet"
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I01: "Database has transactions "
DEBUG:  informix database connection using transactions
DEBUG:  informix_fdw: using rowid 1
DEBUG:  varno 1, bogus_varno 1, varlevelsup 0
DEBUG:  deparsed pushdown predicate 0, type = 'R'::bpchar
DEBUG:  varno 1, bogus_varno 1, varlevelsup 0
DEBUG:  RestrictInfo doesn't hold anything interesting, skipping
DEBUG:  predicate for pushdown:   type = 'R'::bpchar
DEBUG:  prepare query "SELECT *, rowid FROM load_flags WHERE   type = 'R'::bpchar"
DEBUG:  informix FDW exception count: 1
DEBUG:  informix_fdw: xact_callback on connection "nobodypracargonet"
ERROR:  informix FDW error: "Type (bpchar) not found. "
DETAIL:  SQLSTATE IX000 (SQLCODE=100)

load_flags on informix:

Column name          Type                                    Nulls

id                   serial                                  no
timestamp            datetime year to minute                 yes
type                 char(1)                                 yes
load_id              integer                                 yes
carrier_id           integer                                 yes
info                 varchar(255)                            yes
user_id              integer                                 yes

The foreign table is the same:

postgres@cargotel [local]# \d+ pra_ft.load_flags
                                                                Foreign table "pra_ft.load_flags"
   Column   |            Type             |                           Modifiers                            | FDW Options | Storage  | Stats target | Description
------------+-----------------------------+----------------------------------------------------------------+-------------+----------+--------------+-------------
 id         | integer                     | not null default nextval('pra_ft.load_flags_id_seq'::regclass) |             | plain    |              |
 timestamp  | timestamp without time zone |                                                                |             | plain    |              |
 type       | character(1)                |                                                                |             | extended |              |
 load_id    | integer                     |                                                                |             | plain    |              |
 carrier_id | integer                     |                                                                |             | plain    |              |
 info       | character varying(255)      |                                                                |             | extended |              |
 user_id    | integer                     |                                                                |             | plain    |              |
Server: cargotel_tcp
FDW Options: ("table" 'load_flags', database 'pra', client_locale 'en_US.utf8', db_locale 'en_US.819')

Any ideas?

Thanks! Jeff

[rossj-cargotel]

As a workaround I found that I can cast char() columns to text and avoid the error.

[psoo]

Yeah, looks like the FDW is not careful enough when deparsing pushdown predicates. Will look if i can prepare a patch for this.

Btw., your debugging output leaks sensitive user information! I've removed the responsible part from the code to be safe in the future. Sorry for that, it was tempting to have the associated options in the log for debugging, but it seems it's too dangerous if it gets listed in public bug reports easily.

[apollo13]

For everyone else trying to use this fdw and wants to understand what is happening:

SELECT * FROM load_flags WHERE   type = 'R'

basically means that 'R' which is text needs a cast via ::bpchar to match char(1) (see http://www.postgresql.org/docs/9.5/static/typeconv-query.html for details).

This cast is also pushed down to the target server, which does not support bpchar, as a quick fix two options are possible:

SELECT * FROM load_flags WHERE   type::text = 'R'

which eliminates the WHERE push down and results in all rows being transferred and filtered in postgres, or:

SELECT * FROM load_flags WHERE   type = 'R'::char(1)

which results in a properly pushed down query

SELECT *, rowid FROM load_flags WHERE type = 'R'::character(1)

[apollo13]

Oh, another option:

ALTER FOREIGN TABLE load_flags ALTER "type" TYPE varchar(1)

which will interestingly enough result in for :

 SELECT *, rowid FROM load_flags WHERE type = 'R'::character varying(255)

not sure why 255 all of a sudden ;)

Cannot wait to see the actual fix -- I tried fixing it myself, but I am lost in the fdw code for now :(

[psoo]

The current caveat in the pushdown code is, that the machinery currently doesn't have any clue about the typmods used in the foreign table and in your specific example gets a TEXT expression for deparsing. Responsible for this is ifxConvertNodeConst(), which has a special case for TEXT only, which silently assumes IFX_MAX_VARCHAR_LEN for a varchar column in Informix. The reason why that even counts for varchar is, that the PostgreSQL parser adds a RelabelType Node into the query tree expression, which results in type TEXT.

I'm currently working on the deparsing code to make it more smarter for CHAR, VARCHAR and TEXT, which need certainly more special care.

[psoo]

So some news here, sorry for the long delay.

I finally came up with this patch https://github.com/credativ/informix_fdw/commit/8a49038064d144024f7dbb62ce1a8b84e61f1a92. Its pushed in its own testing branch, so beware to use it in production. However, the fixes there aim to address all the issues Florian and rossj-cargotel brought up here. Opinions?

[apollo13]

Thanks, I'll see that I can test it. Regarding your commit message: I did not mean to gripe, I merely wanted to provide more background and help in debugging since I have/had no idea about fdws at all. Thank you for your work on it!

[apollo13]

The changes seem to work nicely for me, all of my examples are pushed down properly. During testing I noted that IN-clauses are not pushed down, but that also happens for integers, so that would be another problem…

[df7cb]

@apollo13 : "gripe" is PostgreSQL jargon for basically any kind of bug report, if you grep the PostgreSQL git log, you'll see it used a lot there.

[apollo13]

Tells you how much I am involved with PG development :D

[psoo]

Please note the other commit in the branch. I've just pushed https://github.com/credativ/informix_fdw/commit/bd384d77425b375581572592b1cc9f114ae34513, which adds the ability to pushdown IN() expressions to the remote Informix instance.

I'm going to prepare the FDW also for 9.6 and make a release if everything is in shape.

[psoo]

Related patches merged with master, so i'm closing this, too.