pgx-managed Postgres w/ wrappers behaved differently from locally-installed Postgres w/ wrappers

[dventimihasura]

Hello,

I'm trying to use the clickhouse_fdw wrapper and am hitting a snag. With correlated sub-query queries, I get an error and a different plan with wrappers installed into my local PostgreSQL. It works without error with wrappers installed in a pgx-managed PostgreSQL. Let me explain.

I have Clickhouse running locally, with related tables account and order and views on those tables. I have PostgreSQL scripts that set up a foreign server from PostgreSQL to Clickhouse running locally, and foreign tables set up on the views for the account and order table. The views are involved to work around the clickhouse_fdw not yet supporting UUID fields, but don't dwell on that fact. I'm pretty sure that's unimportant and focusing on it would be a red herring.

Anyway, the query I'm trying to run in PostgreSQL against the foreign tables looks like this.

select
  *
  from
    account
 where
  id = (select id from account limit 1);

It's a simple query with a sub-query in the where clause to pick out a random account entry but beyond that it's not even a bona-fide correlated sub-query because the sub-query doesn't make reference to any tables in the enclosing query.

If I start a pgx-managed PostgreSQL instance with the clickhouse_fdw with this command in the wrappers/wrappers directory

cargo pgx run pg14 --features clickhouse_fdw

and run that query above, it works fine. If I install the clickhouse_fdw extension in my local PostgreSQL instance with this command

cargo pgx install --features clickhouse_fdw

I get this error with that same query listed above.

ERROR:  query failed: Server error: `ERROR DB::Exception (386): DB::Exception: There is no supertype for types String, UInt8 because some of them are String/FixedString and some of them are not: while executing 'FUNCTION equals(id : 0, 0 : 4) -> equals(id, 0) UInt8 : 5'`
DETAIL:  Wrappers

The query plans are different as well. The working version in the pgx-managed instance yields this plan:

                                                                                                           QUERY PLAN                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on account  (cost=1.00..1001.00 rows=1000 width=32)
   Filter: (id = $0)
   Wrappers: quals = []
   Wrappers: tgts = [Column { name: "id", num: 1, type_oid: 2950 }, Column { name: "name", num: 2, type_oid: 25 }, Column { name: "created_at", num: 3, type_oid: 1114 }, Column { name: "updated_at", num: 4, type_oid: 1114 }]
   Wrappers: sorts = []
   Wrappers: limit = None
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..1.00 rows=1 width=8)
           ->  Foreign Scan on account account_1  (cost=0.00..1000.00 rows=1000 width=8)
                 Wrappers: quals = []
                 Wrappers: tgts = [Column { name: "id", num: 1, type_oid: 2950 }]
                 Wrappers: sorts = []
                 Wrappers: limit = Some(Limit { count: 1, offset: 0 })
(13 rows)

The non-working version in the locally-installed instance yields this plan:

                                                                                                                 QUERY PLAN                                                                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                   QUERY PLAN                                                                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on account  (cost=1.00..2.00 rows=1 width=0)
   Filter: (id = $0)
   Wrappers: quals = [Qual { field: "id", operator: "=", value: Cell(I64(0)), use_or: false, param: Some(Param { id: 0, type_oid: 2950 }) }]
   Wrappers: tgts = [Column { name: "id", num: 1, type_oid: 2950 }, Column { name: "name", num: 2, type_oid: 25 }, Column { name: "created_at", num: 3, type_oid: 1114 }, Column { name: "updated_at", num: 4, type_oid: 1114 }]
   Wrappers: sorts = []
   Wrappers: limit = None
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..1.00 rows=1 width=0)
           ->  Foreign Scan on account account_1  (cost=0.00..1.00 rows=1 width=0)
                 Wrappers: quals = [Qual { field: "id", operator: "=", value: Cell(I64(0)), use_or: false, param: Some(Param { id: 0, type_oid: 2950 }) }]
                 Wrappers: tgts = [Column { name: "id", num: 1, type_oid: 2950 }, Column { name: "name", num: 2, type_oid: 25 }, Column { name: "created_at", num: 3, type_oid: 1114 }, Column { name: "updated_at", num: 4, type_oid: 1114 }]
                 Wrappers: sorts = []
                 Wrappers: limit = None

What the heck is going on here? Any ideas? For the record, both instances of PostgreSQL (my local one and the pgx-managed one) are v14.7.

P.S. I can work up some kind of reproducible demo or at least a screencast, but I figured I'd get the basic question out there first.

[dventimihasura]

Here's a reproducible demo. All you have to do is run docker compose up, connect to the postgres instance with the foreign data wrappers, and start issuing queries.

https://github.com/dventimihasura/ubiquitous-parakeet