AlchemyFDW: 'Malformed array literal' error on retrieving data from foreign Postgres array columns

[LanceRadioactive]

Hello! Sincere thanks for getting Multicorn working once more.
The issue I have located resides specifically in AlchemyFDW. You can reproduce it by connecting to a foreign database and attempting to create a foreign table with an array column type - _int4, for instance.

The table registers correctly; however, on attempting to query data it returns the following:

SQL Error [22P02]: ERROR: malformed array literal: "[111]"
Detail: Missing "=" after array dimensions.

I am willing to estimate that AlchemyFDW correctly retrieves the data from the foreign server, but attempts to return it in JSON/Python format (with square brackets), rather than Postgres format (using curly brackets). This is further confirmed by it successfully casting itself into other data types, such as text and jsonb.

[luss]

I'm not sure if this is a bug or an enhancement request. Can you reproduce this possible bug on the original Multicorn on PG12 or PG13?

[LanceRadioactive]

Apologies for the late response, I briefly completely forgot about this.
This might be best suited as an echancement request, as it seems the current FDW code doesn't do anything at all to support arrays. In any case, here are the reproduction steps.

1. Dockerfile:

FROM supabase/postgres:13.3.0

RUN apt-get update \
    && apt-get install -y --no-install-recommends \
      build-essential \
      git \
      postgresql-server-dev-13

RUN apt-get install -y python3-pip wget
RUN python3 -m pip install -U pip
RUN python3 -m pip install wheel setuptools
RUN python3 -m pip install pytz
RUN python3 -m pip install sqlalchemy psycopg2


RUN set PATH=/usr/local/pgsql/bin:$PATH \
    && wget https://github.com/pgsql-io/multicorn2/archive/refs/tags/v2.2.tar.gz \
    && tar -xvf v2.2.tar.gz \
    && cd multicorn2-2.2 \
    && make \
    && make install 

ENV POSTGRES_USER postgres 
ENV POSTGRES_PASSWORD postgres

Build and run the resulting image, exposing the port 5432 as desired, with database name, username and password all postgres.

2. Connect to database postgres and do the following:

create database postgres_physical with owner postgres; -- this will store the physical tables

create extension multicorn;

create server alchemy_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

create foreign table array_foreign
(arr _int4)
server alchemy_srv
options (db_url 'postgresql+psycopg2://postgres:postgres@localhost/postgres_physical', schema 'public', tablename 'array_example')

This makes the required foreign table and creates a database for the local tables.

3. Connect to DB postgres_physical, run the following:

create table array_example (arr _int4);
insert into array_example values ('{1, 2, 3, 4, 5}')

Which creates and populates the local table.

4. Finally, at postgres:

select * from array_foreign

Which raises the following error:

Notably, different detail text, same core issue - attempting to shove a python array into postgres syntax.

[luss]

kewl, this is a good and simple enhancement request. Anyone care to take a first crack and submit a pull request. :-)

…

On Wed, May 18, 2022 at 11:40 AM LanceRadioactive ***@***.***> wrote: Apologies for the late response, I briefly completely forgot about this. This might be best suited as an echancement request, as it seems the current FDW code doesn't do anything at all to support arrays. In any case, here are the reproduction steps. 1. Dockerfile: FROM supabase/postgres:13.3.0 RUN apt-get update \ && apt-get install -y --no-install-recommends \ build-essential \ git \ postgresql-server-dev-13 RUN apt-get install -y python3-pip wget RUN python3 -m pip install -U pip RUN python3 -m pip install wheel setuptools RUN python3 -m pip install pytz RUN python3 -m pip install sqlalchemy psycopg2 RUN set PATH=/usr/local/pgsql/bin:$PATH \ && wget https://github.com/pgsql-io/multicorn2/archive/refs/tags/v2.2.tar.gz \ && tar -xvf v2.2.tar.gz \ && cd multicorn2-2.2 \ && make \ && make install ENV POSTGRES_USER postgres ENV POSTGRES_PASSWORD postgres Build and run the resulting image, exposing the port 5432 as desired, with database name, username and password all postgres. 1. Connect to database postgres and do the following: create database postgres_physical with owner postgres; -- create extension multicorn; create server alchemy_srv foreign data wrapper multicorn options ( wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw' ); create foreign table array_foreign (arr _int4) server alchemy_srv options (db_url ***@***.***/postgres_physical', schema 'public', tablename 'array_example') This makes the required foreign table and creates a database for the local tables. 1. Connect to DB postgres_physical, run the following: create table array_example (arr _int4); insert into array_example values ('{1, 2, 3, 4, 5}') Which creates and populates the local table. 1. Finally, at postgres: select * from array_foreign Which raises the following error: [image: изображение] <https://user-images.githubusercontent.com/39345560/169084391-1e3a92fe-928f-4966-b702-b76593fc4f3a.png> Notably, different detail text, same core issue - attempting to shove a python array into postgres syntax. — Reply to this email directly, view it on GitHub <#7 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHXI5GNJKK62XLIH2T3VKUFQDANCNFSM5VYJNMTQ> . You are receiving this because you commented.Message ID: ***@***.***>

[akshayjain3450]

@LanceRadioactive can you tell me how are which python version, sqlalchemy version, psycopg version and postgres version you are using.

while querying I am facing this issue:
postgres=# SELECT total_sales_amount FROM customer_insights;
ERROR: Error in python: ArgumentError
DETAIL: Column expression, FROM clause, or other columns clause element expected, got [Table('customer_insights', MetaData(), Column('total_sales_amount', NUMERIC(), table=<customer_insights>), schema=None)]. Did you mean to say select(Table('customer_insights', MetaData(), Column('total_sales_amount', NUMERIC(), table=<customer_insights>), schema=None))?

[mfenniak]

@akshayjain3450 I think that your issue will be addressed by the upgraded SQLAlchemy support that is being added in #49.