search

treasure-data / prestogres

PostgreSQL protocol gateway for Presto distributed SQL query engine
Other
292 stars 61 forks source link

postgres_FDW problem #57

Open parisni opened 8 years ago

parisni commented 8 years ago

Hello,

FDW (foreign data wrapper) is a postgres extension that allow queries on remote postgres databases. It allows to remote queries with predicat, join, sort pushdown. My goal is to link integrate presto within postgres thought postgres_fdw and prestogres.

I am able to connect to prestogres instance with psql and query my table :

psql -h localhost -p 5439 hive presto psql (9.6.0) Type "help" for help.

hive=> \c hive You are now connected to database "hive" as user "presto". hive=> SELECT count(1) FROM foodmart.customer;

col0

10281 (1 row)

Within postgres_fdw, I am able to retrieve shema, and I am able to import tables.

create extension postgres_fdw; CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5439', dbname 'hive'); CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'presto');

    IMPORT foreign schema foodmart
    from server foreign_server into public;

But I get a problem :

select * from public.customer Where: Remote SQL command: DECLARE c1 CURSOR FOR SELECT customer_id, account_num, lname, fname, mi, address1, address2, address3, address4, city, state_province, postal_code, country, customer_region_id, phone1, phone2, birthdate, marital_status, yearly_income, gender, total_children, num_children_at_home, education, date_accnt_opened, member_card, occupation, houseowner, num_cars_owned, fullname FROM foodmart.customer ERROR: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

and within the backend I get :

2016-10-23 15:16:43: pid 7294: LOG: statement: SET search_path = pg_catalog 2016-10-23 15:16:49: pid 7294: LOG: statement: SET timezone = 'UTC' 2016-10-23 15:16:49: pid 7294: LOG: statement: SET datestyle = ISO 2016-10-23 15:16:49: pid 7294: LOG: statement: SET intervalstyle = postgres 2016-10-23 15:16:49: pid 7294: LOG: statement: SET extra_float_digits = 3 2016-10-23 15:16:49: pid 7294: LOG: statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ 2016-10-23 15:16:49: pid 7294: ERROR: Parse: Prestogres doesn't support extended query 2016-10-23 15:16:49: pid 7294: LOG: child exiting, 1 connections reached

Do I have a chance to make FDW working, and how (maybe python code) ?

Thanks