Problems using Postgres datasource

Discussion:

Gonzalo Ortiz Jaureguizar

2017-08-29 07:08:06 UTC

I think I sent that email to the wrong address. Reposting it here

---------- Forwarded message ----------
From: Gonzalo Ortiz Jaureguizar <***@gmail.com>
Date: 2017-08-25 11:24 GMT+02:00
Subject: Problems using Postgres datasource
To: user-***@drill.apache.org

Hi there,

My name is Gonzalo and I'm working at Logtrust, where we have use our own
sql-like database to store and query logs. We provide a JDBC driver and we
would like to join some data with a Posgres database. I'm evaluating
different approaches and the two more promising are Apache Drill with two
datasources or, if it doesn't work, use Apache Calcite to implement our own
solution. I expect to find problems when trying to integrate our own JDBC
driver with Drill (as I am not sure how much JDBC compliat we are), so I
decided to first try the JDBC connector with the Postgres database. Sadly,
I found some problems.

I have followed Drill's documentation, installing Drill 1.11 on my linux
ubuntu 17.04 notebook and then installed postgres from the official
repositories. After creating a postgres user and a database, I started
Drill on embedded mode and execute the following query to test everything
is working:

0: jdbc:drill:zk=local> select * from INFORMATION_SCHEMA.`TABLES`;

Then I place the postgres driver on jar/3rdparty, configure the datasource,
quits on the shell and start it again. The configuration I have used is the

{
"type": "jdbc",
"driver": "org.postgresql.Driver",
"url": "jdbc:postgresql://127.0.0.1/gortiz",
"username": "gortiz",
"password": <the password>,
"enabled": true
}

So i sould be connected to the database *gortiz* on 127.0.0.1 with username
*gortiz*. Just for the record, I have also tried to connect to
jdbc:postgresql://127.0.0.1/testdrill and I have the same problems.

Error: SYSTEM ERROR: NullPointerException: Error. Type information for
table postgres.gortiz.pg_aggregate_fnoid_index provided is null.
Fragment 0:0
[Error Id: c41990a5-a114-411f-a06f-330f60a44318 on anduar:31010]
(state=,code=0)

I am not expert on postgres (although I have been working on that db some
time) but as far as I know and I don't know what is the relation p
g_aggregate_fnoid_index, but Google doesn't have too many records about
that.

If I execute a select * from INFORMATION_SCHEMA.`COLUMNS` I can see tons
of rows, some of them having TABLE_SCHEMA = 'postgres' (the name I gave to
the storage plugin). If I execute select * from
INFORMATION_SCHEMA.`COLUMNS` where TABLE_SCHEMA = 'postgres' then I get the

I don't know if a query on the table *test* on postgres should be written
on drill as select * from postgres.`gortiz.test` or select * from

0: jdbc:drill:zk=local> select * from postgres.`gortiz.test`;
Aug 25, 2017 11:13:22 AM org.apache.calcite.sql.validate.SqlValidatorException
<init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
'postgres.gortiz.test' not found
Aug 25, 2017 11:13:22 AM org.apache.calcite.runtime.CalciteException
<init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1,
column 15 to line 1, column 22: Table 'postgres.gortiz.test' not found
Table 'postgres.gortiz.test' not found
SQL Query null
[Error Id: 60a6a6d6-8eb3-470f-a36e-181378ccb565 on anduar:31010]
(state=,code=0)
0: jdbc:drill:zk=local> select * from postgres.`test`;
Aug 25, 2017 11:13:31 AM org.apache.calcite.sql.validate.SqlValidatorException
<init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
'postgres.test' not found
Aug 25, 2017 11:13:31 AM org.apache.calcite.runtime.CalciteException
<init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1,
column 15 to line 1, column 22: Table 'postgres.test' not found
Table 'postgres.test' not found
SQL Query null
[Error Id: d8c28bb3-388d-4307-b25f-66ab0d1b6018 on anduar:31010]
(state=,code=0)

First I try all these sentences by using postgres 10 and the last JDBC
driver (https://jdbc.postgresql.org/download/postgresql-42.1.4.jar). Then I
tried other combinations like:

- Postgres 9.6 and driver postgresql-42.1.4.jre7.jar
- Postgres 9.6 and driver postgresql-42.1.4.jre6.jar
- Postgres 9.2 (the older I can find on the apt repos) and driver
9.1-903 JDBC 4 (Your documentation recomends 9.1-901-1.jdbc4, but 9.1-903
JDBC 4 is the closest I could find).

With all configurations, the results are the same. Do you know whar is
happening? It may be due to some misconfiguration on my side, but I'm
pretty blocked right now.

Thanks for your time,

Gonzalo

Gonzalo Ortiz Jaureguizar

2017-09-01 07:27:50 UTC

Permalink

I do not want to look like I'm spamming, but there has been some activity
on the list and yet no one has answered me. I don't know if the topic is
not be very interesting or it is out of scope. Maybe the silence is due to
the fact that the email was forwarded and you may not read the interesting
part. As it can be a bug, it may be better to create a ticket instead of
talking about this list. What do you recommend me?

Bests,

Gonzalo

Post by Gonzalo Ortiz Jaureguizar
I think I sent that email to the wrong address. Reposting it here
---------- Forwarded message ----------
Date: 2017-08-25 11:24 GMT+02:00
Subject: Problems using Postgres datasource
Hi there,
My name is Gonzalo and I'm working at Logtrust, where we have use our own
sql-like database to store and query logs. We provide a JDBC driver and we
would like to join some data with a Posgres database. I'm evaluating
different approaches and the two more promising are Apache Drill with two
datasources or, if it doesn't work, use Apache Calcite to implement our own
solution. I expect to find problems when trying to integrate our own JDBC
driver with Drill (as I am not sure how much JDBC compliat we are), so I
decided to first try the JDBC connector with the Postgres database. Sadly,
I found some problems.
I have followed Drill's documentation, installing Drill 1.11 on my linux
ubuntu 17.04 notebook and then installed postgres from the official
repositories. After creating a postgres user and a database, I started
Drill on embedded mode and execute the following query to test everything
0: jdbc:drill:zk=local> select * from INFORMATION_SCHEMA.`TABLES`;

Then I place the postgres driver on jar/3rdparty, configure the
datasource, quits on the shell and start it again. The configuration I have

{
"type": "jdbc",
"driver": "org.postgresql.Driver",
"url": "jdbc:postgresql://127.0.0.1/gortiz",
"username": "gortiz",
"password": <the password>,
"enabled": true
}

So i sould be connected to the database *gortiz* on 127.0.0.1 with
username *gortiz*. Just for the record, I have also tried to connect to
jdbc:postgresql://127.0.0.1/testdrill and I have the same problems.

I am not expert on postgres (although I have been working on that db some
time) but as far as I know and I don't know what is the relation p
g_aggregate_fnoid_index, but Google doesn't have too many records about
that.
If I execute a select * from INFORMATION_SCHEMA.`COLUMNS` I can see tons
of rows, some of them having TABLE_SCHEMA = 'postgres' (the name I gave to
the storage plugin). If I execute select * from
INFORMATION_SCHEMA.`COLUMNS` where TABLE_SCHEMA = 'postgres' then I get

I don't know if a query on the table *test* on postgres should be written
on drill as select * from postgres.`gortiz.test` or select * from

First I try all these sentences by using postgres 10 and the last JDBC
driver (https://jdbc.postgresql.org/download/postgresql-42.1.4.jar). Then
- Postgres 9.6 and driver postgresql-42.1.4.jre7.jar
- Postgres 9.6 and driver postgresql-42.1.4.jre6.jar
- Postgres 9.2 (the older I can find on the apt repos) and driver
9.1-903 JDBC 4 (Your documentation recomends 9.1-901-1.jdbc4, but 9.1-903
JDBC 4 is the closest I could find).
With all configurations, the results are the same. Do you know whar is
happening? It may be due to some misconfiguration on my side, but I'm
pretty blocked right now.
Thanks for your time,
Gonzalo

William Dickenson

2017-09-01 07:31:49 UTC

Permalink

Gonzalo - we use drill against Postgres extensively although almost exclusively with odbc - that being said, our expert returns on Tuesday so I will walk your question over

Thanks

Sent from my iPhone

Post by Gonzalo Ortiz Jaureguizar
I do not want to look like I'm spamming, but there has been some activity
on the list and yet no one has answered me. I don't know if the topic is
not be very interesting or it is out of scope. Maybe the silence is due to
the fact that the email was forwarded and you may not read the interesting
part. As it can be a bug, it may be better to create a ticket instead of
talking about this list. What do you recommend me?
Bests,
Gonzalo

Then I place the postgres driver on jar/3rdparty, configure the
datasource, quits on the shell and start it again. The configuration I have

{
"type": "jdbc",
"driver": "org.postgresql.Driver",
"url": "jdbc:postgresql://127.0.0.1/gortiz",
"username": "gortiz",
"password": <the password>,
"enabled": true
}

So i sould be connected to the database *gortiz* on 127.0.0.1 with
username *gortiz*. Just for the record, I have also tried to connect to
jdbc:postgresql://127.0.0.1/testdrill and I have the same problems.

I am not expert on postgres (although I have been working on that db some
time) but as far as I know and I don't know what is the relation p
g_aggregate_fnoid_index, but Google doesn't have too many records about
that.
If I execute a select * from INFORMATION_SCHEMA.`COLUMNS` I can see tons
of rows, some of them having TABLE_SCHEMA = 'postgres' (the name I gave to
the storage plugin). If I execute select * from
INFORMATION_SCHEMA.`COLUMNS` where TABLE_SCHEMA = 'postgres' then I get

I don't know if a query on the table *test* on postgres should be written
on drill as select * from postgres.`gortiz.test` or select * from

Kunal Khatua

2017-09-01 07:33:10 UTC

Permalink

I'm not very familiar with the details of Postgres, but I do so see people occassionally asking about it

Have you checked the mailing list archives? You might find your answers there.

-----Original Message-----
From: Gonzalo Ortiz Jaureguizar [mailto:***@gmail.com]
Sent: Friday, September 01, 2017 12:28 AM
To: ***@drill.apache.org
Subject: Re: Problems using Postgres datasource

I do not want to look like I'm spamming, but there has been some activity on the list and yet no one has answered me. I don't know if the topic is not be very interesting or it is out of scope. Maybe the silence is due to the fact that the email was forwarded and you may not read the interesting part. As it can be a bug, it may be better to create a ticket instead of talking about this list. What do you recommend me?

Bests,

Gonzalo

Post by Gonzalo Ortiz Jaureguizar
I think I sent that email to the wrong address. Reposting it here
---------- Forwarded message ----------
Date: 2017-08-25 11:24 GMT+02:00
Subject: Problems using Postgres datasource
Hi there,
My name is Gonzalo and I'm working at Logtrust, where we have use our
own sql-like database to store and query logs. We provide a JDBC
driver and we would like to join some data with a Posgres database.
I'm evaluating different approaches and the two more promising are
Apache Drill with two datasources or, if it doesn't work, use Apache
Calcite to implement our own solution. I expect to find problems when
trying to integrate our own JDBC driver with Drill (as I am not sure
how much JDBC compliat we are), so I decided to first try the JDBC
connector with the Postgres database. Sadly, I found some problems.
I have followed Drill's documentation, installing Drill 1.11 on my
linux ubuntu 17.04 notebook and then installed postgres from the
official repositories. After creating a postgres user and a database,
I started Drill on embedded mode and execute the following query to
0: jdbc:drill:zk=local> select * from INFORMATION_SCHEMA.`TABLES`;

Then I place the postgres driver on jar/3rdparty, configure the
datasource, quits on the shell and start it again. The configuration I

{
"type": "jdbc",
"driver": "org.postgresql.Driver",
"url": "jdbc:postgresql://127.0.0.1/gortiz",
"username": "gortiz",
"password": <the password>,
"enabled": true
}

So i sould be connected to the database *gortiz* on 127.0.0.1 with
username *gortiz*. Just for the record, I have also tried to connect
to jdbc:postgresql://127.0.0.1/testdrill and I have the same problems.

Error: SYSTEM ERROR: NullPointerException: Error. Type information
for table postgres.gortiz.pg_aggregate_fnoid_index provided is null.
Fragment 0:0
[Error Id: c41990a5-a114-411f-a06f-330f60a44318 on anduar:31010]
(state=,code=0)

I am not expert on postgres (although I have been working on that db some
time) but as far as I know and I don't know what is the relation p
g_aggregate_fnoid_index, but Google doesn't have too many records
about that.
If I execute a select * from INFORMATION_SCHEMA.`COLUMNS` I can see
tons of rows, some of them having TABLE_SCHEMA = 'postgres' (the name
I gave to the storage plugin). If I execute select * from
INFORMATION_SCHEMA.`COLUMNS` where TABLE_SCHEMA = 'postgres' then I

I don't know if a query on the table *test* on postgres should be
written on drill as select * from postgres.`gortiz.test` or select *

0: jdbc:drill:zk=local> select * from postgres.`gortiz.test`; Aug 25,
2017 11:13:22 AM
org.apache.calcite.sql.validate.SqlValidatorException
<init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
'postgres.gortiz.test' not found Aug 25, 2017 11:13:22 AM
org.apache.calcite.runtime.CalciteException
<init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line
1, column 15 to line 1, column 22: Table 'postgres.gortiz.test' not
found
Table 'postgres.gortiz.test' not found
SQL Query null
[Error Id: 60a6a6d6-8eb3-470f-a36e-181378ccb565 on anduar:31010]
(state=,code=0)
0: jdbc:drill:zk=local> select * from postgres.`test`; Aug 25, 2017
11:13:31 AM org.apache.calcite.sql.validate.SqlValidatorException
<init>
SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Table
'postgres.test' not found Aug 25, 2017 11:13:31 AM
org.apache.calcite.runtime.CalciteException
<init>
SEVERE: org.apache.calcite.runtime.CalciteContextException: From line
1, column 15 to line 1, column 22: Table 'postgres.test' not found
Table 'postgres.test' not found
SQL Query null
[Error Id: d8c28bb3-388d-4307-b25f-66ab0d1b6018 on anduar:31010]
(state=,code=0)

First I try all these sentences by using postgres 10 and the last JDBC
driver (https://jdbc.postgresql.org/download/postgresql-42.1.4.jar).
- Postgres 9.6 and driver postgresql-42.1.4.jre7.jar
- Postgres 9.6 and driver postgresql-42.1.4.jre6.jar
- Postgres 9.2 (the older I can find on the apt repos) and driver
9.1-903 JDBC 4 (Your documentation recomends 9.1-901-1.jdbc4, but 9.1-903
JDBC 4 is the closest I could find).
With all configurations, the results are the same. Do you know whar is
happening? It may be due to some misconfiguration on my side, but I'm
pretty blocked right now.
Thanks for your time,
Gonzalo

Gonzalo Ortiz Jaureguizar

2017-09-01 07:41:41 UTC

Permalink

Thanks for your fast answer.

I bet it is a misconfiguration from my side, but I'm pretty blocked and it
seems the typical thing that is easily detected by a 3rd observer ;)

Post by Kunal Khatua
I'm not very familiar with the details of Postgres, but I do so see people
occassionally asking about it
Have you checked the mailing list archives? You might find your answers there.
-----Original Message-----
Sent: Friday, September 01, 2017 12:28 AM
Subject: Re: Problems using Postgres datasource
I do not want to look like I'm spamming, but there has been some activity
on the list and yet no one has answered me. I don't know if the topic is
not be very interesting or it is out of scope. Maybe the silence is due to
the fact that the email was forwarded and you may not read the interesting
part. As it can be a bug, it may be better to create a ticket instead of
talking about this list. What do you recommend me?
Bests,
Gonzalo