treasure-data/prestogres

PostgreSQL protocol gateway for Presto distributed SQL query engine

PostgreSQL protocol gateway for Presto

Prestogres is a gateway server that allows clients to use PostgreSQL protocol to run queries on Presto.

You can use any PostgreSQL clients (see also Limitation section):

• psql command
• PostgreSQL ODBC driver
• PostgreSQL JDBC driver

Prestogres also offers password-based authentication and SSL.

Documents

• How it works?
• Limitation
• Installation
  • 1. Install PostgreSQL >= 9.3
  • 2. Install Prestogres
• Running servers
  • Setting shmem max parameter
• Configuration
• Authentication
  • md5 method
  • external method
• FAQ
  • I can connect from localhost but cannot from remote host
  • I can connect to Prestogres but cannot run any queries
  • All queries by JDBC or ODBC clients fail with "Prestogres doesn't support extended query"
  • Time zone of timestamp type is wrong

---

How it works?

Prestogres uses modified version of pgpool-II to rewrite queries before sending them to PostgreSQL. pgpool-II is originally a middleware to provide connection pool and load balancing to PostgreSQL. Prestogres hacked it as following:

• When a client connects to pgpool-II, the modified pgpool-II runs SELECT setup_system_catalog(...) statement on PostgreSQL.
  • This function is implemented on PostgreSQL using PL/Python.
  • It gets list of tables from Presto, and runs CREATE TABLE for each tables.
  • Those created tables are empty, but clients can get the table schemas.
• When the client runs a regular SELECT statement, the modified pgpool-II rewrites the query to run SELECT * FROM fetch_presto_query_results(...) statement.
  • This function runs the original query on Presto and returns the results.
• If the statement is not regular SELECT (such as SET, SELECT from system catalogs, etc.), pgpool-II simply forwards the statement to PostgreSQL without rewriting.

In fact, there're some more tricks. See prestogres/pgsql/prestogres.py for the real behavior.

---

Limitation

• Extended query is not supported
  • ODBC driver needs to set:
    • Server side prepare = no property (UseServerSidePrepare=0 at .ini file)
    • Level of rollback on errors = Transaction property (Protocol=7.4-0 or Protocol=6.4 at .ini file)
    • Unicode mode
  • JDBC driver needs to set:
    • protocolVersion=2 property
• Temporary table is not supported
• Some SQL commands of Presto don't work
  • Supported:
    • SELECT
    • EXPLAIN
    • INSERT INTO
    • CREATE TABLE
    • CREATE VIEW
  • Not supported:
    • DROP TABLE

---

Installation

1. Install PostgreSQL >= 9.3

You need to install PostgreSQL separately. Following commands install PostgreSQL 9.3 from postgresql.org:

Ubuntu/Debian:

# add apt source
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
# install PostgreSQL
$ sudo apt-get install postgresql-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 postgresql-plpython-9.3
# install other dependencies
$ sudo apt-get install gcc make libssl-dev libpcre3-dev

RedHat/CentOS:

# add yum source
$ sudo yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
# install PostgreSQL
$ sudo yum install postgresql93-server postgresql93-contrib postgresql93-devel postgresql93-plpython
# install other dependencies
$ sudo yum install gcc make openssl-devel pcre-devel

Mac OS X:

You can install PostgreSQL using Homebrew.

brew install postgresql

2. Install Prestogres

Download the latest release from releases or clone the git repository. You can install the binary as following:

$ ./configure --program-prefix=prestogres- # if error occurs, add pg_config command $PATH (e.g. $ export PATH=/usr/pgsql-9.3/bin:$PATH)
$ make
$ sudo make install

You can find prestogres-ctl command:

$ prestogres-ctl --help

---

Running servers

You need to run 2 server programs: pgpool-II and PostgreSQL. You can use prestogres-ctl command to setup & run them as following:

# 1. Configure configuration file (at least presto_server parameter):
$ vi /usr/local/etc/prestogres.conf

# 2. Create a data directory:
$ prestogres-ctl create pgdata
# vi pgdata/postgresql.conf  # edit configuration if necessary

# 3. Start PostgreSQL
$ prestogres-ctl postgres -D pgdata

# 4. Open another shell, and initialize the database to install PL/Python functions
$ prestogres-ctl migrate

# 5. Start pgpool-II:
$ prestogres-ctl pgpool

# 6. Finally, you can connect to pgpool-II using psql command.
#    Database name ('hive') is name of a Presto catalog:
$ psql -h 127.0.0.1 -p 5439 -U presto hive

If configuration is correct, you can run SELECT * FROM sys.node; query. Otherwise, see log messages.

Setting shmem max parameter

Above command fails first time on most of environments! Error message is:

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=6432001, size=3809280, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded
available memory or swap space, or exceeded your kernel's SHMALL parameter.  You can either
reduce the request size or reconfigure the kernel with larger SHMALL.  To reduce the request
size (currently 3809280 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing
shared_buffers or max_connections.

You need to set 2 kernel parameters to run PostgreSQL.

Linux:

sudo bash -c "echo kernel.shmmax = 17179869184 >> /etc/sysctl.conf"
sudo bash -c "echo kernel.shmall = 4194304 >> /etc/sysctl.conf"
sudo sysctl -p /etc/sysctl.conf

Mac OS X:

$ sudo sysctl -w kern.sysv.shmmax=1073741824
$ sudo sysctl -w kern.sysv.shmall=1073741824

---

Configuration

Please read pgpool-II documentation for most of parameters used in prestogres.conf file. Following parameters are unique to Prestogres:

• presto_server: address:port of a presto coordinator.
• presto_catalog: (optional) catalog name of Presto (such as hive, etc.). By default, login database name is used as the catalog name
• presto_schema: (optional) schema name of Presto (such as hive, etc.). By default, login database name is used as the schema name
• presto_external_auth_prog: (optional) path to an external authentication program used by external authentication moethd. See following Authentication section for details.

You can overwrite these parameters for each connecting users (and databases) using prestogres_hba.conf file. See also following Authentication section.

Authentication

By default, Prestogres accepts all connections from 127.0.0.1 without password and rejects any other connections. You can change this behavior by updating $prefix/etc/prestogres_hba.conf file.

See sample prestogres_hba.conf file for details. Basic syntax is:

# TYPE  DATABASE  USER    CIDR-ADDRESS             METHOD        OPTIONS

# trust from 192.168.x.x without password
host    all       all     127.0.0.1/32             trust

# trust from 192.168.x.x without password
host    all       all     192.168.0.0/16           trust

# trust from 10.{1,2}.x.x without password
host    all       all     10.0.0.0/16,10.1.0.0/16  trust

# require password authentication from 10.3.x.x
host    all       all     10.3.0.0/16              md5

# overwrite presto_server address and catalog name if the login database name is altdb
host    altdb     all     0.0.0.0/0                md5           presto_server:alt.presto.example.com:8190,presto_catalog:hive

# run external command to authenticate if login user name is myuser
host    all       myuser  0.0.0.0/0                external      auth_prog:/opt/prestogres/auth.py

md5 method

This authentication method uses a password file ($prefix/etc/prestogres_passwd) to authenticate an user. You can use prestogres passwd command to add an user to this file:

$ prestogres-pg_md5 -pm -u myuser
password: (enter password here)

In prestogres_hba.conf file, you can set following options to the OPTIONS field:

• presto_server: address:port of a presto coordinator, which overwrites presto_servers parameter in prestogres.conf.
• presto_catalog: catalog name of Presto, which overwrites presto_catalog parameter in prestogres.conf.
• presto_schema: schema name of Presto, which overwrites presto_schema parameter in prestogres.conf.
• presto_user: user name to run queries on Presto (X-Presto-User). By default, login user name is used. Following pg_user parameter doesn't affect this parameter.
• pg_database: (advanced) Overwrite database name on PostgreSQL. By default, login database name is used as-is. If this database does not exist on PostgreSQL, Prestogres automatically creates it.
• pg_user: (advanced) Overwrite user name connecting to PostgreSQL. This value should be prestogres in most of cases. If you create another superuser on PostgreSQL manually, you may use this parameter.

external method

This authentication method runs an external command to authentication an user.

• Note: This method is still experimental. Interface could be changed.
• Note: This method requires clients to send password in clear text. It's recommended to enable SSL in prestogres.conf.

You need to set presto_external_auth_prog parameter in prestogres.conf or auth_prog option in prestogres_hba.conf. Prestogres runs the program every time when an user connects. The program receives following data through STDIN:

user:USER_NAME
password:PASSWORD
database:DATABASE
address:IPADDR

If you want to allow this connection, the program optionally prints parameters as following to STDOUT, and exists with status code 0:

presto_server:PRESTO_SERVER_ADDRESS
presto_catalog:PRESTO_CATALOG_NAME
presto_schema:PRESTO_SCHEMA_NAME
presto_user:USER_NAME
pg_database:DATABASE
pg_user:USER_NAME

If you want to reject this connection, the program exists with non-0 status code.

---

FAQ

I can connect from localhost but cannot from remote host

Prestogres trusts connections from localhost and rejects any other connections by default. To connect Prestogres from a remote host, you need to edit prestogres_hba.conf file.

See Authentication section for details.

I can connect to Prestogres but cannot run any queries

Prestogres gets all table information from Presto when you run the first query for each connection. If this initialization fails, all queries fail.

Prestogres runs following SQL on Presto to get table information. If this query fails on your Presto, Prestogres doesn't work.

select table_schema, table_name, column_name, is_nullable, data_type
from information_schema.columns

All queries by JDBC or ODBC clients fail with "Prestogres doesn't support extended query"

PostgreSQL has 2 protocols to run a query: simple query and extended query.

Extended query is a new protocol to support prepared statements (server-side prepared statements). Prestogres supports only simple query.

Fortunately, JDBC and ODBC clients implement prepared statements at client-side to be complatible with old PostgreSQL. You need to disable server-side prepared statements and enable the client-side implementation.

See Limitation section for the parameters.

If you have interest in the detailed protocol specification: PostgreSQL Frontend/Backend Protocol.

Time zone of timestamp type is wrong

Prestogres checks timezone session variable and passes it to Presto (X-Presto-Time-Zone).

• To check timezone session variable: SHOW timezone
• To change the timezone on a session: SET timezone TO UTC
• To change the default timezone, edit timezone parameter at postgresql.conf file located in PostgreSQL's data directory you created using prestogres-ctl create command.

---

Prestogres is licensed under Apache License, Version 2.0.
Copyright (C) 2014 Sadayuki Furuhashi

prestogres's Issues

Could not convert Python object into cstring

Do you have any idea why this error happens?

2015-04-15_01:10:20.34347 LOG:  statement: select * from pg_temp.presto_fetch()
2015-04-15_01:10:44.97052 ERROR:  could not convert Python object into cstring: Python string representation appears to contain null bytes
2015-04-15_01:10:44.97055 CONTEXT:  while creating return value
2015-04-15_01:10:44.97055       PL/Python function "presto_fetch"

Unknown type: TEXT when using CONTAINS in Tableau's calculated field

** Using Presto version 0.141t and my prestogres would be 3.4.0 (latest)

I'm using CONTAINS function inside a calculated field as follow

And the error is as follow

I got raw sql from pgpool-II

SELECT (CASE WHEN (STRPOS(CAST("product_master_2016"."product" AS TEXT),CAST('Fresh' AS TEXT)) > 0) THEN 'Test' ELSE NULL END) AS "Calculation_250231285777633280", "product_master_2016"."product" AS "product" FROM "default"."product_master_2016" "product_master_2016" GROUP BY 1, 2

Direct query from command line "psql -h 127.0.0.1 -p 5439 -U presto hive" also show error like this

I understand that PostgreSQL supports data type TEXT but Presto doesn't have TEXT (according to Presto Doc). So how would I make this function CONTAINS works? Is there any way that I can set VARCHAR as a default data type for string??

ERROR: schema "prestogres_catalog" does not exist

Hi, i dont know if i configured some wrong but when i try to make a query return
ERROR: schema "prestogres_catalog" does not exist

i have this:
EC2 Amazon RDS

---

| prestogres | --> | POSTGRESQL |

---

my backend 0 is

backend_hostname0 = 'RDS IP ' # Host name or IP address to connect to for backend 0
backend_port0 = 5432 # Port number for backend 0

the connection is ok, but show this error "ERROR: schema "prestogres_catalog" does not exist" it's a problem in my config?

best,
dharwin

How to install prestogres to a server which does not Internet access

Hi,

I need to install Prestogres to a server that does not have Internet access. It is RedHat 6 O/S. How to install or compile Prestogres from source? (I have installed Postgresql already).

regards
Arthur

Connected, but can't query: no module named prestogres

Just attempting to set up Prestogres with Presto using the TPCH: tiny catalog/schema. I used the following steps:

1. Create database cluster (initdb)
2. Start cluster (pg_ctl)
3. create prestogres user:
   createuser -h 127.0.0.1 -p 5433 -s -r -e -d prestogres
4. migrate scripts to postgresql:
   psql -h 127.0.0.1 -p 5433 -U prestogres postgres < '/usr/local/share/prestogres/setup.sql'
5. Startup pgpool
6. Connect via psql

bash-3.2$ psql -h 127.0.0.1 -p 5439 -U prestogres tiny
psql (9.4.4)
Type "help" for help.

tiny=# select count(*) from customer;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
tiny=# SELECT * FROM sys.node;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

I can connect to Prestogres, but as soon as I attempt to issue a query through psql, I get the following error in pgpool, with a similar statement in the backend Postgresql database's log. Any idea what I could have missed? I'm no Postgresql pro, so this could be something simple -

2015-08-14 08:31:23: pid 3705: DETAIL:  backend:0 of 1 kind = 'Z'
2015-08-14 08:31:23: pid 3705: DEBUG:  processing backend response
2015-08-14 08:31:23: pid 3705: DETAIL:  received kind 'Z'(5a) from backend
2015-08-14 08:31:23: pid 3705: DEBUG:  processing backend response
2015-08-14 08:31:23: pid 3705: DETAIL:  Ready For Query received
2015-08-14 08:31:23: pid 3705: DEBUG:  reading message length
2015-08-14 08:31:23: pid 3705: DETAIL:  slot: 0 length: 5
2015-08-14 08:31:23: pid 3705: DEBUG:  processing ReadyForQuery
2015-08-14 08:31:23: pid 3705: DETAIL:  transaction state '
2015-08-14 08:34:21: pid 3696: DEBUG:  I am 3696 accept fd 7
2015-08-14 08:34:21: pid 3696: DEBUG:  reading startup packet
2015-08-14 08:34:21: pid 3696: DETAIL:  Protocol Major: 2 Minor: 0 database: tpch user: admin
2015-08-14 08:34:21: pid 3696: DEBUG:  prestogres_init_hba: presto_server: 127.0.0.1:8185
2015-08-14 08:34:21: pid 3696: DEBUG:  prestogres_init_hba: presto_user: admin
2015-08-14 08:34:21: pid 3696: DEBUG:  prestogres_init_hba: presto_catalog: tpch
2015-08-14 08:34:21: pid 3696: DEBUG:  prestogres_init_hba: presto_schema: tiny
2015-08-14 08:34:22: pid 3696: DEBUG:  creating new connection to backend
2015-08-14 08:34:22: pid 3696: DETAIL:  connecting 0 backend
2015-08-14 08:34:22: pid 3696: DEBUG:  SSL is requested but SSL support is not available
2015-08-14 08:34:22: pid 3696: DEBUG:  authentication backend
2015-08-14 08:34:22: pid 3696: DETAIL:  auth kind:0
2015-08-14 08:34:22: pid 3696: DEBUG:  authentication backend
2015-08-14 08:34:22: pid 3696: DETAIL:  cp->info[i]:0x10d587000 pid:3968
2015-08-14 08:34:22: pid 3696: DEBUG:  sending backend key data
2015-08-14 08:34:22: pid 3696: DETAIL:  send pid 3968 to frontend
2015-08-14 08:34:22: pid 3696: DEBUG:  selecting load balance node
2015-08-14 08:34:22: pid 3696: DETAIL:  selected backend id is 0
2015-08-14 08:34:22: pid 3696: DEBUG:  initializing session context
2015-08-14 08:34:22: pid 3696: DETAIL:  selected load balancing node: 0
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing transaction isolation. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing writing transaction. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing failed transaction. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing failed transaction. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing skip reading from backends. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing ignore till sync. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  reading backend data packet kind
2015-08-14 08:34:22: pid 3696: DETAIL:  backend:0 of 1 kind = 'Z'
2015-08-14 08:34:22: pid 3696: DEBUG:  processing backend response
2015-08-14 08:34:22: pid 3696: DETAIL:  received kind 'Z'(5a) from backend
2015-08-14 08:34:22: pid 3696: DEBUG:  processing frontend response
2015-08-14 08:34:22: pid 3696: DETAIL:  received kind 'Q'(51) from frontend
2015-08-14 08:34:22: pid 3696: DEBUG:  reading string data
2015-08-14 08:34:22: pid 3696: DETAIL:  read all from pending data. po:0 len:0
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: clearing doing extended query messaging. DONE
2015-08-14 08:34:22: pid 3696: LOG:  statement: set datestyle = 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end
2015-08-14 08:34:22: pid 3696: DEBUG:  session context: setting query in progress. DONE
2015-08-14 08:34:22: pid 3696: DEBUG:  do_query: extended:0 query:"select 1 from (select prestogres_catalog.setup_system_catalog(E'127.0.0.1:8185', E'admin', E'tpch', E'tiny', E'admin')) s;"
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  do_query: kind: 'P'
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  reading string data
2015-08-14 08:34:22: pid 3696: DETAIL:  read all from pending data. po:6 len:73
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  do_query: kind: 'T'
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  do_query: received ROW DESCRIPTION ('T')
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  do_query: row description: num_fileds: 1
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  reading string data
2015-08-14 08:34:22: pid 3696: DETAIL:  read all from pending data. po:18 len:61
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  do_query: kind: 'E'
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: DEBUG:  reading string data
2015-08-14 08:34:22: pid 3696: DETAIL:  read all from pending data. po:78 len:1
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3696: FATAL:  Backend throw an error message
2015-08-14 08:34:22: pid 3696: DETAIL:  Exiting current session because of an error from backend
*2015-08-14 08:34:22: pid 3696: HINT:  BACKEND Error: "ERROR:  ImportError: No module named prestogres
    "*
2015-08-14 08:34:22: pid 3696: CONTEXT:  while searching system catalog, When relcache is missed
2015-08-14 08:34:22: pid 3695: DEBUG:  reaper handler
2015-08-14 08:34:22: pid 3695: DEBUG:  reaper handler: child with PID:3696 exits with status 256
2015-08-14 08:34:22: pid 3695: DEBUG:  reaper handler: forked a new child with PID:3969
2015-08-14 08:34:22: pid 3695: DEBUG:  reaper handler: exiting normally
2015-08-14 08:34:22: pid 3969: DEBUG:  initializing backend status
2015-08-14 08:34:22: pid 3969: DEBUG:  SSL is requested but SSL support is not available
2015-08-14 08:34:22: pid 3969: DEBUG:  authenticate kind = 0
2015-08-14 08:34:22: pid 3969: DEBUG:  authenticate backend: key data received
2015-08-14 08:34:22: pid 3969: DEBUG:  authenticate backend: transaction state: I

SET doesn't passthrough to presto

I'm trying to set presto session variable but I'm hitting

default=> set columnar_processing  = true;
ERROR:  unrecognized configuration parameter "columnar_processing"

It doesn't look like prestogres handles this, so I was wondering if anyone could point me in the right direction for implementing the most rudimentary way of passing this through directly to presto. Don't need anything fancy like listing variables, just straight through.

postgresql 9.6

Hello,

1. The new variables intreduced with pg 9.6 (eg dynamic_shared_memory_type )
   make the creation of the data directory fail:

prestogres-ctl create pgdata

I had to comment those variable in order to make it working.

2. I do not use the 5432 default port. All prestogres-ctl command were failing. Is there a way to

prestogres-ctl -p 5435 migrate

3. BTW, good job, this is a complex architecture, with a simple install. Do you plan to maintain this (no change for a year)?

Installing prestogres on AWS

I'm trying to install prestogres on the coordinator of my presto cluster, which is deployed on EMR. I'm getting a few errors:

1. When I try to install postgresql93-plpython:

[hadoop@ip-172-18-0-54 ~]$ sudo yum install postgresql93-plpython
Loaded plugins: priorities, update-motd, upgrade-helper
22 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package postgresql93-plpython.x86_64 0:9.3.6-2PGDG.rhel7 will be installed
--> Processing Dependency: postgresql93-server(x86-64) = 9.3.6-2PGDG.rhel7 for package: postgresql93-plpython-9.3.6-2PGDG.rhel7.x86_64
--> Processing Dependency: postgresql93(x86-64) = 9.3.6-2PGDG.rhel7 for package: postgresql93-plpython-9.3.6-2PGDG.rhel7.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql93-plpython-9.3.6-2PGDG.rhel7.x86_64 (pgdg93)
           Requires: postgresql93-server(x86-64) = 9.3.6-2PGDG.rhel7
           Installed: postgresql93-server-9.3.6-1.59.amzn1.x86_64 (@amzn-main)
               postgresql93-server(x86-64) = 9.3.6-1.59.amzn1
Error: Package: postgresql93-plpython-9.3.6-2PGDG.rhel7.x86_64 (pgdg93)
           Requires: postgresql93(x86-64) = 9.3.6-2PGDG.rhel7
           Installed: postgresql93-9.3.6-1.59.amzn1.x86_64 (@amzn-main)
               postgresql93(x86-64) = 9.3.6-1.59.amzn1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

It looks like there is some conflict, where postgresql93-plpython requires the standard postgresql package, yet trying to install those packages resolves to the Amazon versions. I was curious as to how people got around this/if this can be ignored (I suspect it can't).
2. When I try to do ./configure --program-prefix=prestogres- (after doing export PATH=/usr/pgsql-9.3/bin:$PATH), I get this error: configure: error: libpq is not installed or libpq is old

I searched around, and it looks like I need to install pgpool2. This results in another error:

sudo yum install pgpool-II-93.x86_64
Error: Package: pgpool-II-93-3.4.2-1.rhel7.x86_64 (pgdg93)
          Requires: libmemcached.so.11()(64bit)
Error: Package: pgpool-II-93-3.4.2-1.rhel7.x86_64 (pgdg93)
           Requires: systemd-sysv
Error: Package: pgpool-II-93-3.4.2-1.rhel7.x86_64 (pgdg93)
           Requires: systemd

Anyone run into these errors before?

pgpool error - after using JDBC client to access the database

Hi Sadayuki,

I am getting the following error, this can be reproduced easily with a JDBC client such Aqua Data Studio. Any idea what could causing it?

2016-01-17 23:22:39: pid 10030: LOG: child exiting, 1 connections reached
2016-01-17 23:22:39: pid 10030: LOCATION: child.c:291
2016-01-17 23:23:25: pid 10022: ERROR: error while initializing database pg_database = "hive" for pg_user = "prestogres": "ERROR: function prestogres_init_database(unknown, unknown, unknown) does not exist
LINE 1: select prestogres_init_database('prestogres', 'hive', E'host...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
"
2016-01-17 23:23:25: pid 10022: LOCATION: pool_system.c:702
2016-01-17 23:23:25: pid 10022: LOG: child exiting, 1 connections reached
2016-01-17 23:23:25: pid 10022: LOCATION: child.c:291

Thanks,
Tom

Connecting to Tableau 8.2

Hi,

I have prestogres up and running now. I just tried to use tableau 8.2 to connect Prestodb.

Q1: If I specify "default" as the database name, I cannot connect Tableau to PrestoDB,
Connect on a server: select PostgreSQL
sever: localhost
port: 9900
database: default
Error: Unable to connect to the ODBC data source, FATAL: database “default” does not exist.

Q2: If I specify "Postgres" as database name,
Connect on a server: select PostgreSQL
sever: localhost
port: 9900
database: postgres
I can log into PrestoDB, but it cannot display any table

On the command line, I can see "default" DB exists
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+-------+----------+-----------+-------+-------------------
default | pg | UTF8 | C | C |

My PrestoDB version is v0.79

Could you share how you get Tableau connected to Presto?
Regards
Arthur

pgpool warning when using nohup to start

I run 'prestogres-ctl postgres -D pgdata' using nohup and it succesfully starts.
But when I run 'prestogres-ctl pgpool' it starts the pgpool, but the log looks like below:

2015-05-12 14:43:17: pid 63946: LOG:  Setting up socket for 0.0.0.0:5439
2015-05-12 14:43:17: pid 63946: LOG:  Setting up socket for :::5439
2015-05-12 14:43:17: pid 63946: LOG:  prestogres successfully started. version 0.6.7 (tataraboshi)
2015-05-12 14:43:17: pid 63948: WARNING:  Connection to database failed: "FATAL:  the database system is starting up
    "
2015-05-12 14:43:17: pid 63948: DETAIL:  connection failed for database:"prestogres@postgres" on Host:"127.0.0.1:5432"
2015-05-12 14:43:17: pid 63948: WARNING:  failed to make persistent system db connection
2015-05-12 14:43:17: pid 63948: DETAIL:  system_db_connect failed
2015-05-12 14:43:17: pid 63949: WARNING:  Connection to database failed: "FATAL:  the database system is starting up
    "
2015-05-12 14:43:17: pid 63949: DETAIL:  connection failed for database:"prestogres@postgres" on Host:"127.0.0.1:5432"
2015-05-12 14:43:17: pid 63949: WARNING:  failed to make persistent system db connection
2015-05-12 14:43:17: pid 63949: DETAIL:  system_db_connect failed
2015-05-12 14:43:17: pid 63950: WARNING:  Connection to database failed: "FATAL:  the database system is starting up

Is it a problem?

Scheme_Workbench hangs up

I try to connect Prestogres by using Scheme_Workbench 3.7.0.0-752 with PostgreSQL JDBC Driver.

Scheme_Workbench is used to create OLAP cube schemas.

But, Scheme_Workbench hangs up when I open schema.

Honestly, I think that this is the problem of Scheme_Workbench, not Prestogres.
So, you don't hesitate to close this issue.

• Prestogres log

< 2014-09-30 14:59:22.096 JST >WARNING:  column "table_cat" has type "unknown"
< 2014-09-30 14:59:22.096 JST >DETAIL:  Proceeding with relation creation anyway.
< 2014-09-30 14:59:22.096 JST >CONTEXT:  SQL statement "create temporary table presto_result (
          table_cat unknown,
          table_schem name,
          table_name name,
          column_name name,
          key_seq int4,
          pk_name name
        )"
        PL/Python function "run_system_catalog_as_temp_table"

• Scheme_Workbench
  http://sourceforge.net/projects/mondrian/files/schema%20workbench/3.7.0/
• PostgreSQL JDBC Driver
  http://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc4.jar
• Scheme_Workbench connection setting
  host:localhost
  database:hive
  port:9990
  user:pg
  option:protocolVersion=2
  Scheme_Workbench connection test is successful.

"\d" and "\l" don't work at prestogres 0.4.8

Hi.

I try to use prestogres 0.4.8 and presto 0.73 at Centos 6.5, but "\d" and "\l" don't work.

$ psql -h localhost -p 9990 -U pg default
psql (9.3.4)
Type "help" for help.

default=# \d
ERROR:  function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist
default=# \l
ERROR:  function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist

pgdata/postgres/pg_log/postgresql-Fri.log

< 2014-07-25 17:52:31.501 JST >ERROR:  function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist at character 8
< 2014-07-25 17:52:31.501 JST >HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
< 2014-07-25 17:52:31.501 JST >STATEMENT:  select prestogres_catalog.run_system_catalog_as_temp_table(E'localhost:8080', E'pg', E'hive', E'default', E'pg', E'default', E'presto_result', E'SELECT n.nspname as "Schema",
          c.relname as "Name",
          CASE c.relkind WHEN \'r\' THEN \'table\' WHEN \'v\' THEN \'view\' WHEN \'m\' THEN \'materialized view\' WHEN \'i\' THEN \'index\' WHEN \'S\' THEN \'sequence\' WHEN \'s\' THEN \'special\' WHEN \'f\' THEN \'foreign table\' END as "Type",
          pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN (\'r\',\'v\',\'m\',\'S\',\'f\',\'\')
              AND n.nspname <> \'pg_catalog\'
              AND n.nspname <> \'information_schema\'
              AND n.nspname !~ \'^pg_toast\'
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 1,2;')
< 2014-07-25 17:52:31.502 JST >ERROR:  function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist
< 2014-07-25 17:52:31.502 JST >STATEMENT:  do $$ begin raise exception '%', E'function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist' using errcode = E'42883'; end $$ language plpgsql
< 2014-07-25 17:52:32.944 JST >ERROR:  function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist at character 8
< 2014-07-25 17:52:32.944 JST >HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
< 2014-07-25 17:52:32.944 JST >STATEMENT:  select prestogres_catalog.run_system_catalog_as_temp_table(E'localhost:8080', E'pg', E'hive', E'default', E'pg', E'default', E'presto_result', E'SELECT d.datname as "Name",
               pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
               pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
               d.datcollate as "Collate",
               d.datctype as "Ctype",
               pg_catalog.array_to_string(d.datacl, E\'\\n\') AS "Access privileges"
        FROM pg_catalog.pg_database d
        ORDER BY 1;')
< 2014-07-25 17:52:32.944 JST >ERROR:  function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist
< 2014-07-25 17:52:32.944 JST >STATEMENT:  do $$ begin raise exception '%', E'function prestogres_catalog.run_system_catalog_as_temp_table(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist' using errcode = E'42883'; end $$ language plpgsql

cannot be cast to org.apache.hadoop.hive.serde2.Deserializer

Hi , I have this error when trying to /dt inside the console

PrestoQueryException: Query 20140715_193328_00033_wys9z failed: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException java.lang.ClassCastException: org.apache.hadoop.hive.contrib.serde2.RegexSerDe cannot be cast to org.apache.hadoop.hive.serde2.Deserializer

Thanks

Cannot connect to Tableau 9.0

When trying to connect to Prestogres using the Postgres adapter in Tableau 9.0 there are no tables retrieved with any combination of details.

I can see queries being run against Presto, so I know that Tableau is able to connect, however Tableau keeps running the following query over and over; "select table_schema, table_name, column_name, is_nullable, data_type from information_schema.columns".

Sounds like some of the other issues people have seen. Has anyone managed to get this connected to Tableau 9? (Or indeed more recent 8.x releases where it appeared to have stopped working also?)

Connection to database failed: Fatal: the database system is in recovery mode

Once in a while I face the problem when using prestogres, and I have to restart postgresql and prestogres.
Any suggestion?

I'm using prestogres 0.6.7, postgresql 9.3.

Tableau cannot retrieve any Schema and Table Info when using Presto 0.85

Hi,

I just upgraded Presto from 0.79 to 0.85, use the same version of Tableau, it cannot retrieve any schema and table.

Please help!

Arthur

prestogres-ctl pgpool gives fatal error

I'm running on AWS EMR, it gives me this error after the command: prestogres-ctl pgpool
[hadoop@ip-XXX-XX-XX-XX pgdata]$ prestogres-ctl pgpool
/usr/local/bin/prestogres-pgpool -n
2015-07-21 04:49:19: pid 20567: FATAL: initializing pool password, failed to open file:"/usr/local/etc/prestogres_passwd"
2015-07-21 04:49:19: pid 20567: DETAIL: file open failed with error:"Permission denied"

Has anybody seen this problem?

Returns random number or rows with every exeuction

I am using ODBC setup for presto sql 0.334. When I run count (*) against a table or select single column, I get same number of rows and correct result every time. But when I select all the columns, output query returns random number of rows. If the actual result contains 1000 rows, it would return result ranging from 0 to 1000.
My JDBC client gives consistent performance.
Would you please suggest how can I add debugging to know the root cause of this behavior.

prestogres_init_database on every connection

I am having issues connecting after database has been initialised.

Prestogres connects without any errors on the initial connection and successfully initialises the database. However, on subsequent connections it appears to attempt to run prestogres_init_database again (which has now gone - as the init completed?).

I have tried this with all combinations of PostgreSQL 9.3 & 9.5 as well as Prestogres 0.6.7 and the latest master branch; all have the same result.

Configuration is as per the README. I am changing presto_server, presto_catalog, and presto_schema (this obviously works as I can login on initial connection and see all Schemas from Presto). I have added my remote host to prestogres_hba.conf as trusted to allow connection.

Any ideas would be appreciated.

LOG:  statement: create database "postgres"
ERROR:  database "postgres" already exists
STATEMENT:  create database "postgres"
LOG:  statement: create role "prestogres" with login
ERROR:  role "prestogres" already exists
STATEMENT:  create role "prestogres" with login
LOG:  statement: select prestogres_init_database('prestogres', 'postgres', E'host=\'127.0.0.1\' port=5432 dbname=\'postgres\' user=\'prestogres\' password=\'blahblag\'')
ERROR:  function prestogres_init_database(unknown, unknown, unknown) does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:  select prestogres_init_database('prestogres', 'postgres', E'host=\'127.0.0.1\' port=5432 dbname=\'postgres\' user=\'prestogres\' password=\'blahblag\'')
2016-04-26 15:34:43: pid 16006: ERROR:  error while initializing database pg_database = "postgres" for pg_user = "prestogres": "ERROR:  function prestogres_init_database(unknown, unknown, unknown) does not exist
        LINE 1: select prestogres_init_database('prestogres', 'postgres', E'...
                       ^
        HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Thanks.

"\d" and "\l" don't work

Hi.

I try to use prestogres 0.4.6 and presto 0.72 at Centos 6.5, but "\d" and "\l" don't work.

$ psql -h localhost -p 9990 -U pg postgres
psql (9.3.4)
Type "help" for help.

postgres=# \d
ERROR:  PrestoQueryException: Query 20140627_031754_00017_thk7q failed: line 1:30: no viable alternative at input '<EOF>'
postgres=# \l
ERROR:  PrestoQueryException: Query 20140627_031756_00018_thk7q failed: line 1:28: no viable alternative at input '<EOF>'

pgdata/postgres/pg_log/postgresql-Fri.log

< 2014-06-27 12:17:54.594 JST >ERROR:  PrestoQueryException: Query 20140627_031754_00017_thk7q failed: line 1:30: no viable alternative at input '<EOF>'
< 2014-06-27 12:17:54.594 JST >CONTEXT:  Traceback (most recent call last):
          PL/Python function "run_presto_as_temp_table", line 3, in <module>
            prestogres.run_presto_as_temp_table(server, user, catalog, schema, table_name, query)
          PL/Python function "run_presto_as_temp_table", line 210, in run_presto_as_temp_table
          PL/Python function "run_presto_as_temp_table", line 288, in columns
          PL/Python function "run_presto_as_temp_table", line 330, in _raise_error
        PL/Python function "run_presto_as_temp_table"
< 2014-06-27 12:17:54.594 JST >STATEMENT:  select prestogres_catalog.run_presto_as_temp_table(E'localhost:8080', E'pg', E'hive', E'postgres', E'presto_result', E'SELECT n.nspname as "Schema",')
< 2014-06-27 12:17:54.595 JST >ERROR:  PrestoQueryException: Query 20140627_031754_00017_thk7q failed: line 1:30: no viable alternative at input '<EOF>'
< 2014-06-27 12:17:54.595 JST >STATEMENT:  do $$ begin raise exception '%', E'PrestoQueryException: Query 20140627_031754_00017_thk7q failed: line 1:30: no viable alternative at input \'<EOF>\'' using errcode = E'XX000'; end $$ language plpgsql
< 2014-06-27 12:17:56.746 JST >ERROR:  PrestoQueryException: Query 20140627_031756_00018_thk7q failed: line 1:28: no viable alternative at input '<EOF>'
< 2014-06-27 12:17:56.746 JST >CONTEXT:  Traceback (most recent call last):
          PL/Python function "run_presto_as_temp_table", line 3, in <module>
            prestogres.run_presto_as_temp_table(server, user, catalog, schema, table_name, query)
          PL/Python function "run_presto_as_temp_table", line 210, in run_presto_as_temp_table
          PL/Python function "run_presto_as_temp_table", line 288, in columns
          PL/Python function "run_presto_as_temp_table", line 330, in _raise_error
        PL/Python function "run_presto_as_temp_table"
< 2014-06-27 12:17:56.746 JST >STATEMENT:  select prestogres_catalog.run_presto_as_temp_table(E'localhost:8080', E'pg', E'hive', E'postgres', E'presto_result', E'SELECT d.datname as "Name",')
< 2014-06-27 12:17:56.746 JST >ERROR:  PrestoQueryException: Query 20140627_031756_00018_thk7q failed: line 1:28: no viable alternative at input '<EOF>'
< 2014-06-27 12:17:56.746 JST >STATEMENT:  do $$ begin raise exception '%', E'PrestoQueryException: Query 20140627_031756_00018_thk7q failed: line 1:28: no viable alternative at input \'<EOF>\'' using errcode = E'XX000'; end $$ language plpgsql

additional information
In my environment, there is hive view table.

missing property: serilization.lib

Hi there,

Today inexplicably (as far as I currently know) the following error stopped our prestogres adapter being able to talk to Presto:

STATEMENT: select 1 from (select prestogres_catalog.setup_system_catalog(E'[redacted]', E'[redacted]', E'presto', E'default', E'pg')) s;
2015-02-04 00:00:02: pid 21280: FATAL: Backend throw an error message
2015-02-04 00:00:02: pid 21280: DETAIL: Exiting current session because of an error from backend
2015-02-04 00:00:02: pid 21280: HINT: BACKEND Error: "presto_client.PrestoQueryException: Query 20150204_000002_00000_aj2wj failed: missing property: serialization.lib"
2015-02-04 00:00:02: pid 21280: CONTEXT: while searching system catalog, When relcache is missed

If I execute any query I get the above error message. If I execute the same query through the presto-cli it works as expected.

As far as I'm aware there have been no changes made to prestogres, but I have tried a fresh pull and build to check, but no joy.

Any pointers would be greatly appreciated as I don't see this in the docs but may be something quite intricate too.

Thanks.

Prestogres corrupted when using native connector

We are seeing a corruption issue when trying to connect to Prestogres on port 5439 using a native connector (such as pgadmin, or Spotfire Postgres). Whenever a native connector is used, Prestogres seems to get corrupted. The result is that none of the migration functions are then available. The fix to this is to recreate the data directory (prestogres-ctl create pgdata) and the re-run the migration ( prestogres-ctl migrate). Is there any way to avoid this from happening or is it possible to fix this easily?

Thanks

Support variable precision time, timestamp, time with time zone, timestamp with time zone types

Over couple of recent releases (up to including Presto 341), Presto gained support for variable precision time, timestamp, time with time zone, timestamp with time zone types.
For backward compatibility reasons, these are not rendered with actual precision to the client, unless the client sends proper X-Presto-Client-Capabilities header.

Add support for these, along with properly setting the header.

See more at trinodb/trino#1284

postgres_FDW problem

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

Feature request for DROP TABLE

Hi DROP TABLE does not currently work as documented, any plan to get it working? Looks like some code changed in Python is needed to get this working?

Thanks,
Tom

Cannot query PrestoDB 0.107

I am unable to query a Presto 0.107 cluster. For testing, I am attempting to query Presto's JMX data source.

Prestogres (master), ca0fa82
PostgreSQL 9.4
Presto 0.107

I can see that the following query in Presto works:

select table_schema, table_name, column_name, is_nullable, data_type from information_schema.columns

However:

psql -h 127.0.0.1 -p 5439 -U presto jmx   
psql (9.4.5)
Type "help" for help.

jmx=> SELECT * FROM sys.node;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Prestogres logs:

LOG:  statement: select 1 from (select prestogres_catalog.setup_system_catalog(E'presto:8080', E'presto', E'jmx', E'jmx', E'presto')) s;
WARNING:  Table jmx."com.facebook.presto.execution:type=queryqueue,name=global,expansion=global" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."com.facebook.presto.execution:type=queryqueue,name=global,expansion=global" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=abstractconnector$acceptor,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletholder,name=org.eclipse.jetty.servlet.servlethandler$default404servlet-62d1dc3c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconnectionfactory,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconnectionfactory,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconnectionfactory,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.timingfilter-4a901445,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.util.thread:context=http/1.1@63d5874f,type=scheduledexecutorscheduler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.util.thread:context=http/1.1@63d5874f,type=scheduledexecutorscheduler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.util.thread:context=http/1.1@63d5874f,type=scheduledexecutorscheduler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletmapping,name=org.glassfish.jersey.servlet.servletcontainer-6e3ecf5c,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector$serverconnectormanager,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector$serverconnectormanager,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector$serverconnectormanager,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.io:context=http/1.1@63d5874f,type=arraybytebufferpool,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."sun.nio.ch:context=http/1.1@63d5874f,type=serversocketchannelimpl,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=httpconfiguration,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.server:context=http/1.1@63d5874f,type=serverconnector,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filtermapping,name=io.airlift.http.server.tracetokenfilter-6dc9da2d,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servlethandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=filterholder,name=org.eclipse.jetty.servlets.gzipfilter-6fbc1bb,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."org.eclipse.jetty.servlet:context=""root@@http"",type=servletcontexthandler,id=0" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."com.facebook.presto.failuredetector:name=heartbeatfailuredetector" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."com.facebook.presto.failuredetector:name=heartbeatfailuredetector" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."com.facebook.presto.failuredetector:name=heartbeatfailuredetector" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
WARNING:  Table jmx."com.facebook.presto.failuredetector:name=heartbeatfailuredetector" is skipped because its name is longer than 63 characters
CONTEXT:  PL/Python function "setup_system_catalog"
LOG:  statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'pg_namespace'
LOG:  statement: SELECT count(*) from (SELECT has_function_privilege('presto', 'to_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'to_regclass')) AS s
LOG:  statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = to_regclass('columns') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
LOG:  statement: create database "jmx"
ERROR:  database "jmx" already exists
STATEMENT:  create database "jmx"
LOG:  statement: create role "presto" with login
ERROR:  role "presto" already exists
STATEMENT:  create role "presto" with login
LOG:  statement: select prestogres_init_database('presto', 'jmx', E'host=\'127.0.0.1\' port=5432 dbname=\'jmx\' user=\'prestogres\' password=\'\'')
2015-10-27 15:23:10: pid 24: WARNING:  child process with pid: 44 was terminated by segmentation fault
LOG:  statement: do $INIT$ begin
            perform pg_advisory_lock(3235398540741723243);  -- prevent "tuple concurrently updated" error

            if not exists (select * from pg_language where lanname = 'plpythonu') then
                create language plpythonu;
            end if;

            if not exists (select * from pg_namespace where nspname = 'prestogres_catalog') then
                create schema prestogres_catalog;
            end if;

            create or replace function prestogres_catalog.start_presto_query(
                presto_server text, presto_user text, presto_catalog text, presto_schema text,
                function_name text, query text)
            returns void as $$
                import prestogres
                prestogres.start_presto_query(
                    presto_server, presto_user, presto_catalog, presto_schema, function_name, query)
            $$ language plpythonu
            security definer;

            create or replace function prestogres_catalog.setup_system_catalog(
                presto_server text, presto_user text, presto_catalog text, presto_schema text,
                access_role text)
            returns void as $$
                import prestogres
                prestogres.setup_system_catalog(
                    presto_server, presto_user, presto_catalog, presto_schema, access_role)
            $$ language plpythonu
            security definer;

            revoke temporary on database "jmx" from public;  -- reject CREATE TEMPORARY TABLE
            grant usage on schema prestogres_catalog to "presto";
            grant execute on all functions in schema prestogres_catalog to "presto";

        end $INIT$ language plpgsql
2015-10-27 15:30:15: pid 164: LOG:  Frontend terminated

ERROR: PrestoHttpException: Failed to start query: Time zone localtime is not supported

I just tried installing and setting up prestogres on ubuntu 14.04. I faced an issue in one step where it returns me the following error on this step of installation:
psql -h localhost -p 9900 -U pg postgres
SELECT * FROM sys.node;
The above returns
ERROR: PrestoHttpException: Failed to start query: Time zone localtime is not supported

ERROR: Prestogres compatibility issues with Presto 0.82 ROW

Hi,
We've just discovered that after upgrading Presto to 0.82 Prestogres table description commands ( \dt, \d+ tablename) all fail with the error message:

For example when running \dt or \d+ tablename returns

ERROR: SyntaxError: syntax error at or near "row"

In the Presto Web UI the corresponding query (select table_schema, table_name, column_name, is_nullable, data_type from information_schema.columns) is shown to have completed successfully but prestogres will report is as failing with the above error message.

I have a sneaking suspicion this has something to do with the new ROW Data Type in Presto 0.82. Querying all of our existing tables that have flat structures works fine, but querying any tables that have nested arrays in them (converted to ROW in Presto) fails with the same error message.

Prestogres version is reported to be: prestogres (0.4.8)

handle hive view

Hi.

I try to use prestogres 0.4.6 and presto 0.72 and Cognos at Centos 6.5.

If there is a hive view and Cognos connects prestogres, the following error occurs.

pgdata/postgres/pg_log/postgresql-Fri.log

< 2014-06-27 14:23:58.178 JST >ERROR:  PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: 'default.hvac_view'
< 2014-06-27 14:23:58.178 JST >CONTEXT:  Traceback (most recent call last):
          PL/Python function "run_system_catalog_as_temp_table", line 3, in <module>
            prestogres.run_system_catalog_as_temp_table(server, user, catalog, schema, table_name, query)
          PL/Python function "run_system_catalog_as_temp_table", line 250, in run_system_catalog_as_temp_table
          PL/Python function "run_system_catalog_as_temp_table", line 342, in run
          PL/Python function "run_system_catalog_as_temp_table", line 288, in columns
          PL/Python function "run_system_catalog_as_temp_table", line 330, in _raise_error
        PL/Python function "run_system_catalog_as_temp_table"
< 2014-06-27 14:23:58.178 JST >STATEMENT:  select prestogres_catalog.run_system_catalog_as_temp_table(E'localhost:8080', E'pg', E'hive', E'hive', E'presto_result', E'select relname, nspname, relkind from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in (\'r\', \'v\') and nspname not in (\'pg_catalog\', \'information_schema\', \'pg_toast\', \'pg_temp_1\') and n.oid = relnamespace order by nspname, relname')
< 2014-06-27 14:23:58.178 JST >ERROR:  PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: 'default.hvac_view'
< 2014-06-27 14:23:58.178 JST >STATEMENT:  do $$ begin raise exception '%', E'PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: \'default.hvac_view\'' using errcode = E'XX000'; end $$ language plpgsql

column name "ctid" conflicts with a system column name

I tried to get prestogres working on out presto cluster, I followed the install instructions but got problems when running "SELECT * FROM sys.node";

Error log:
< 2014-12-28 15:26:47.526 CST >LOG: database system is ready to accept connections
< 2014-12-28 15:26:47.526 CST >LOG: autovacuum launcher started
< 2014-12-28 15:29:18.971 CST >LOG: statement: create database "hive"
< 2014-12-28 15:29:19.212 CST >LOG: statement: create role "hadp" with login
< 2014-12-28 15:29:19.212 CST >ERROR: role "hadp" already exists
< 2014-12-28 15:29:19.212 CST >STATEMENT: create role "hadp" with login
< 2014-12-28 15:29:19.213 CST >LOG: statement: select prestogres_init_database('hadp', 'hive', E'host='127.0.0.1' port=5432 dbname='hive' user='prestogres' password=''')
< 2014-12-28 15:29:19.230 CST >LOG: statement: do $INIT$ begin
if not exists (select * from pg_language where lanname = 'plpythonu') then
create language plpythonu;
end if;

        if not exists (select * from pg_namespace where nspname = 'prestogres_catalog') then
            create schema prestogres_catalog;
        end if;

        create or replace function prestogres_catalog.start_presto_query(
            presto_server text, presto_user text, presto_catalog text, presto_schema text,
            function_name text, query text)
        returns void as $$
            import prestogres
            prestogres.start_presto_query(
                presto_server, presto_user, presto_catalog, presto_schema, function_name, query)
        $$ language plpythonu
        security definer;

        drop function if exists prestogres_catalog.setup_system_catalog(text, text, text, text);

        create or replace function prestogres_catalog.setup_system_catalog(
            presto_server text, presto_user text, presto_catalog text, presto_schema text,
            access_role text)
        returns void as $$
            import prestogres
            prestogres.setup_system_catalog(
                presto_server, presto_user, presto_catalog, presto_schema, access_role)
        $$ language plpythonu
        security definer;

        revoke temporary on database "hive" from public;  -- reject CREATE TEMPORARY TABLE
        grant usage on schema prestogres_catalog to "hadp";
        grant execute on all functions in schema prestogres_catalog to "hadp";

    end $INIT$ language plpgsql

NOTICE: function prestogres_catalog.setup_system_catalog(text,text,text,text) does not exist, skipping
CONTEXT: SQL statement "drop function if exists prestogres_catalog.setup_system_catalog(text, text, text, text)"
PL/pgSQL function inline_code_block line 20 at SQL statement
< 2014-12-28 15:29:27.372 CST >LOG: statement: select 1 from (select prestogres_catalog.setup_system_catalog(E'127.0.0.1:8089', E'hadp', E'hive', E'hive', E'hadp')) s;
< 2014-12-28 15:29:41.244 CST >ERROR: spiexceptions.DuplicateColumn: column name "ctid" conflicts with a system column name
< 2014-12-28 15:29:41.244 CST >CONTEXT: Traceback (most recent call last):
PL/Python function "setup_system_catalog", line 4, in
presto_server, presto_user, presto_catalog, presto_schema, access_role)
PL/Python function "setup_system_catalog", line 300, in setup_system_catalog
PL/Python function "setup_system_catalog"

My environment is as follows:
System Version: Linux Centos 6.5
Python Version: 2.6
Presto Version: 0.89
PostgreSQL Version: 9.3.5

Do you have any idea what causes the problem?

empty string/varchar(0) cause error

Hi

I use presto 0.145.

Presto 0.145 treats empty string as varchar(0).

create view test as select '' as hoge from ...

presto:default> desc test;
 Column |    Type    | Comment
--------+------------+---------
 hoge   | varchar(0) |
(1 row)

But because postgresql doesn't seem to allow varchar(0), the following prestogres error occurs.

HINT:  BACKEND Error: "ERROR:  spiexceptions.InvalidParameterValue: length for type varchar must be at least 1 at character 238

Workaround is to use cast('' as varchar)

But presto 0.143 treats empty string as varchar.
So maybe this problem doesn't occur at 0.143.

But important problem is that there is no isolation.

If user create varchar(0) presto view at 0.145, prestogres doesn't response at all.

Althought this problem may not be prestogres's problem, I report because this information seems to be helpful.

Thanks