[
https://issues.apache.org/jira/browse/HAWQ-982?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15412822#comment-15412822
]
Ruilong Huo commented on HAWQ-982:
----------------------------------
RCA: the root cause is compatibility issue between libpq protocol in hawq and
postgres since we modified libpq versioning mechanism in hawq.
To be specific, here are details:
In hawq and postgres, we support libpq v1.0 ~ v3.0. The packet of any
connection request would start with packet length and then libpq version. The
version is represented in a unsigned int32 v. The version is composed of two
parts: major version and minor version, which are extracted as below:
{noformat}
### hawq
/* Upper four bits used special by hawq */
#define PG_PROTOCOL_MAJOR(v) (((v) >> 16) & 0xfff)
#define PG_PROTOCOL_MINOR(v) ((v) & 0x0000ffff)
### postgres
#define PG_PROTOCOL_MAJOR(v) ((v) >> 16)
#define PG_PROTOCOL_MINOR(v) ((v) & 0x0000ffff)
{noformat}
We currently use libpq v3.0 in hawq and postgres by default. When we connect to
remote postgres from udf in hawq, the client psycopg2 uses hawq's libpq and
sends version with 1879244800 (01110000 00000011 00000000 00000000 in binary)
in packet. Where it sends version with 196608 (00000000 00000011 00000000
00000000 in binary) if psycopg2 uses postgres's libpq. Note that hawq reserves
higher 4 bits of the version and uses them specially. Here is add 0111 to the
higher 4 bits before it sends the request packet.
For the version sent from libpq in hawq, the major version is extracted as
28675 (01110000 00000011 in binary), and the minor version is extracted as 0
(00000000 00000000 in binary). The reflects the error message "unsupported
frontend protocol 28675.0: server supports 1.0 to 3.0".
For the version sent from libpq in postgres, the major version is extracted as
3 (00000000 00000011 in binary), and the minor version is extracted as 0
(00000000 00000000 in binary). Thus the query works as the version 3.0 is
between supported version 1.0 ~ 3.0.
> PL/Python with psycopg2 cannot connect to remote postgres
> ---------------------------------------------------------
>
> Key: HAWQ-982
> URL: https://issues.apache.org/jira/browse/HAWQ-982
> Project: Apache HAWQ
> Issue Type: Bug
> Components: Core
> Reporter: Lei Chang
> Assignee: Ruilong Huo
> Fix For: backlog
>
>
> For one use case I want to connect to external postgreSQL database from HAWQ
> PL/Python procedure.
> I use python psycopg2 library.
> Remote postgreSQL server reject connecion from HAWQ with
> this error : FATAL unsupported frontend protocol 28675.0: server supports
> 1.0 to 3.0.
> The same python code is running well from OS level.
> I wonder if it is HAWQ or PostgreSQL PL/Python interpreter related issiue.
> Any help or pointers would be great.
> -----------------------
> my code below:
> CREATE OR REPLACE FUNCTION dchoma.connection_test( ) RETURNS text AS
> $$
> import psycopg2
> try:
> conn = psycopg2.connect("dbname='database_name' user='user'
> host='remote_host' password='pass' port=5432")
> return "Connection successful "
> except Exception , msg :
> return "Exception: {m}".format(m=msg)
> $$
> LANGUAGE 'plpythonu' VOLATILE;
> select * from dchoma.connection_test();
> HAWQ version 2.0.1.0 build dev ( compiled from github)
> Remote database version: PostgreSQL 9.2.15 on x86_64-redhat-linux-gnu,
> compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
> OS: CentOS 7-1511
> i found similar issiue here, but the problem is not solved.
> https://discuss.zendesk.com/hc/en-us/community/posts/200793368-greenplum-dblink-postgresql-remote-is-error
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
