New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

java.sql.DatabaseMetaData.getImportedKeys()/getMaxIndexKeys() fails against Amazon Redshift #79

Closed
garrettpatterson opened this Issue Aug 12, 2013 · 10 comments

Comments

Projects
None yet
3 participants
@garrettpatterson

garrettpatterson commented Aug 12, 2013

When using Mondrian schema workbench, it is unable to list databases in the schema, the error is: org.postgresql.util.PSQLException: "Unable to determine a value for MaxIndexKeys due to missing system catalog data."

It appears to be incompatibility of SQL used to query metadata and the version which Redshift presents itself as:
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java#L46-59

The query it tries to run reporting as an 8.0 PostgreSQL:
SELECT * FROM pg_catalog.pg_settings WHERE name='max_index_keys'

Query that actually works (Min version >= 7.3 query):
SELECT t1.typlen/t2.typlen from pg_catalog.pg_namespace n, pg_catalog.pg_type t1, pg_catalog.pg_type t2 WHERE t1.typnamespace=n.oid AND n.nspname='pg_catalog' AND t1.typelem=t2.oid AND t1.typname='oidvector'

Is there a way to determine a Redshift version within the driver? Set something in JDBC configuration for Redshift?

@davecramer

This comment has been minimized.

Show comment
Hide comment
@davecramer

davecramer Aug 13, 2013

Member

if it presents as 8.0 and the code checks for min version >= 7.3 then it
should work ?

Dave Cramer

On Mon, Aug 12, 2013 at 6:28 PM, garrettpatterson
notifications@github.comwrote:

When using Mondrian schema workbench, it is unable to list databases in
the schema, the error is: org.postgresql.util.PSQLException: "Unable to
determine a value for MaxIndexKeys due to missing system catalog data."

It appears to be incompatibility of SQL used to query metadata and the
version which Redshift presents itself as:

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java#L46-59

The query it tries to run reporting as an 8.0 PostgreSQL:
SELECT * FROM pg_catalog.pg_settings WHERE name='max_index_keys'

Query that actually works (Min version >= 7.3 query):
SELECT t1.typlen/t2.typlen from pg_catalog.pg_namespace n,
pg_catalog.pg_type t1, pg_catalog.pg_type t2 WHERE t1.typnamespace=n.oid
AND n.nspname='pg_catalog' AND t1.typelem=t2.oid AND t1.typname='oidvector'

Is there a way to determine a Redshift version within the driver? Set
something in JDBC configuration for Redshift?


Reply to this email directly or view it on GitHubhttps://github.com/pgjdbc/pgjdbc/issues/79
.

Member

davecramer commented Aug 13, 2013

if it presents as 8.0 and the code checks for min version >= 7.3 then it
should work ?

Dave Cramer

On Mon, Aug 12, 2013 at 6:28 PM, garrettpatterson
notifications@github.comwrote:

When using Mondrian schema workbench, it is unable to list databases in
the schema, the error is: org.postgresql.util.PSQLException: "Unable to
determine a value for MaxIndexKeys due to missing system catalog data."

It appears to be incompatibility of SQL used to query metadata and the
version which Redshift presents itself as:

https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java#L46-59

The query it tries to run reporting as an 8.0 PostgreSQL:
SELECT * FROM pg_catalog.pg_settings WHERE name='max_index_keys'

Query that actually works (Min version >= 7.3 query):
SELECT t1.typlen/t2.typlen from pg_catalog.pg_namespace n,
pg_catalog.pg_type t1, pg_catalog.pg_type t2 WHERE t1.typnamespace=n.oid
AND n.nspname='pg_catalog' AND t1.typelem=t2.oid AND t1.typname='oidvector'

Is there a way to determine a Redshift version within the driver? Set
something in JDBC configuration for Redshift?


Reply to this email directly or view it on GitHubhttps://github.com/pgjdbc/pgjdbc/issues/79
.

@davecramer

This comment has been minimized.

Show comment
Hide comment
@davecramer

davecramer Apr 25, 2014

Member

I think they recommend using only 8.4

Member

davecramer commented Apr 25, 2014

I think they recommend using only 8.4

@davecramer davecramer closed this Apr 25, 2014

@garrettpatterson

This comment has been minimized.

Show comment
Hide comment
@garrettpatterson

garrettpatterson Apr 25, 2014

Redshift presents as >8, yet the auto-discovery query for PostGres version 8 and up does not work. The query that does work and matches the metadata schema is that of Postgres version >=7.3 but < version 8. I wouldn't consider "I think they recommend using only 8.4" a decent reason to close this.

garrettpatterson commented Apr 25, 2014

Redshift presents as >8, yet the auto-discovery query for PostGres version 8 and up does not work. The query that does work and matches the metadata schema is that of Postgres version >=7.3 but < version 8. I wouldn't consider "I think they recommend using only 8.4" a decent reason to close this.

@davecramer

This comment has been minimized.

Show comment
Hide comment
@davecramer

davecramer Apr 25, 2014

Member

I'll reopen it for now then. I have to find a way to get a hold of redshift. In some ways it's really their bug

Member

davecramer commented Apr 25, 2014

I'll reopen it for now then. I have to find a way to get a hold of redshift. In some ways it's really their bug

@davecramer davecramer reopened this Apr 25, 2014

@garrettpatterson

This comment has been minimized.

Show comment
Hide comment
@garrettpatterson

garrettpatterson Apr 25, 2014

Agreed - they're the ones doing out of band things. thanks for re-opening, would be fine if this went to an enhancement instead since its more about support than a true bug.

garrettpatterson commented Apr 25, 2014

Agreed - they're the ones doing out of band things. thanks for re-opening, would be fine if this went to an enhancement instead since its more about support than a true bug.

@ringerc

This comment has been minimized.

Show comment
Hide comment
@ringerc

ringerc Apr 26, 2014

Member

On 04/26/2014 03:57 AM, Dave Cramer wrote:

I'll reopen it for now then. I have to find a way to get a hold of
redshift. In some ways it's really their bug

I have some contacts within Amazon here. I'll get in touch and see if I
can get a contact within the Redshift team. They really need to start
contributing to PgJDBC if they're going to be relying on it - and I'd
really rather they contributed to mainline PgJDBC than suddenly pop up
with a fork.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Member

ringerc commented Apr 26, 2014

On 04/26/2014 03:57 AM, Dave Cramer wrote:

I'll reopen it for now then. I have to find a way to get a hold of
redshift. In some ways it's really their bug

I have some contacts within Amazon here. I'll get in touch and see if I
can get a contact within the Redshift team. They really need to start
contributing to PgJDBC if they're going to be relying on it - and I'd
really rather they contributed to mainline PgJDBC than suddenly pop up
with a fork.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

@ringerc

This comment has been minimized.

Show comment
Hide comment
@ringerc

ringerc Jun 26, 2014

Member

I've had no response from Amazon/ParAccel. They seem to content to ignore issues with Redshift and PgJDBC.

Member

ringerc commented Jun 26, 2014

I've had no response from Amazon/ParAccel. They seem to content to ignore issues with Redshift and PgJDBC.

@davecramer

This comment has been minimized.

Show comment
Hide comment
@davecramer

davecramer Jun 26, 2014

Member

Well I have an account they will let us use, but I'm less than motivated to
help them. I had to ask them for the account

Dave Cramer

On 26 June 2014 05:21, Craig Ringer notifications@github.com wrote:

I've had no response from Amazon/ParAccel. They seem to content to ignore
issues with Redshift and PgJDBC.


Reply to this email directly or view it on GitHub
#79 (comment).

Member

davecramer commented Jun 26, 2014

Well I have an account they will let us use, but I'm less than motivated to
help them. I had to ask them for the account

Dave Cramer

On 26 June 2014 05:21, Craig Ringer notifications@github.com wrote:

I've had no response from Amazon/ParAccel. They seem to content to ignore
issues with Redshift and PgJDBC.


Reply to this email directly or view it on GitHub
#79 (comment).

@ringerc

This comment has been minimized.

Show comment
Hide comment
@ringerc

ringerc Jun 26, 2014

Member

On 06/26/2014 05:44 PM, Dave Cramer wrote:

Well I have an account they will let us use, but I'm less than motivated to
help them. I had to ask them for the account

... and they still haven't donated one to the SQLFiddle folks, AFAIK.
Which is not sensible.

I see little reason to put any effort into supporting their
closed-source product when they don't dedicate any resources to helping
out here.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Member

ringerc commented Jun 26, 2014

On 06/26/2014 05:44 PM, Dave Cramer wrote:

Well I have an account they will let us use, but I'm less than motivated to
help them. I had to ask them for the account

... and they still haven't donated one to the SQLFiddle folks, AFAIK.
Which is not sensible.

I see little reason to put any effort into supporting their
closed-source product when they don't dedicate any resources to helping
out here.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

@davecramer

This comment has been minimized.

Show comment
Hide comment
@davecramer

davecramer Apr 17, 2015

Member

Not going to fix this

Member

davecramer commented Apr 17, 2015

Not going to fix this

@davecramer davecramer closed this Apr 17, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment