Skip to content
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

client_min_messages and 'SET time zone' incompatible with AWS Redshift: Make configurable? #403

Closed
findchris opened this issue Jun 4, 2013 · 12 comments
Labels

Comments

@findchris
Copy link

Hi there.

AWS's Redshift is PostgreSQL under the hood, with some features restricted. Trying to use this adapter with Redshift does not work, however, because reading and writing client_min_messages and issuing the SET time zone command is not supported.

If these two options were configurable (or potentially capable of handling failures), this adapter would successfully connect to Redshift. I verified this by commenting out those lines locally (in lib/arjdbc/postgresql/adapter.rb).

Cheers.

@kares
Copy link
Member

kares commented Jun 4, 2013

Hey Chris, sounds reasonable - could you review the code if there's some other potentially non-sup commands ?
Also is there a way to detect Redshift (e.g. when resolving DB version or using the Driver API) you know of (I'm not yet sure if we should make those configurable or simply detect and avoid) ?

@findchris
Copy link
Author

Thanks for following up.

I am not aware of any other non-sup command, except for max_identifier_length.

Also, from http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-postgres-jdbc.html, RedShift uses PostgreSQL 8.0.2.

Hope that helps.
Cheers.

@kares
Copy link
Member

kares commented Jun 10, 2013

I'm thinking about introducing a PostgreSQL::Redshift module but since I do not have a RS instance I need some help setting this up, probably knowing what the output of these are under RS would be enough for starters :

connection = ActiveRecord::Base.connection.jdbc_connection # ...
puts connection.getMetaData.getDatabaseProductName
puts connection.getMetaData.getDatabaseProductVersion

Thanks

@findchris
Copy link
Author

I would love to provide this info, but I cannot connect to redshift due to the extra querying the adapter does. See below:

jruby-1.7.4 :001 > ActiveRecord::Base.connection.jdbc_connection
ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: permission denied to set parameter "client_min_messages" to "warning": SET client_min_messages TO 'warning'
ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: permission denied to set parameter "client_min_messages" to "warning": SET client_min_messages TO 'warning'
    from arjdbc/jdbc/RubyJdbcConnection.java:359:in `execute'
    from /Users/chris/.rvm/gems/jruby-1.7.4/bundler/gems/activerec

Anything else I can try to obtain that would be helpful?

@kares
Copy link
Member

kares commented Jun 11, 2013

I was hoping (as you mentioned) that you already commented those lines out locally and was able to connect ...

@kares
Copy link
Member

kares commented Jun 11, 2013

Also I probably won't be able to do this as I thought - documentation seems insufficient, it would be best if you could test and implement Redshift support on top of our ArJdbc::PostgreSQL I was checking PostgreSQL 8.0 doc and :

I did not found these being mentioned anywhere on Amazon's site e.g. http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html also Google showed nothing thus this really is quite constraining for me to implement without access to a Redshift DB instance.

@wilerson
Copy link

wilerson commented Aug 6, 2013

Hi guys, I just stumbled into this issue. As the OP, I managed to connect and make some basic queries by commenting out the calls to SET/SHOW client_min_messages and SET TIME ZONE.

@kares , below is the output for the commands you asked:

irb(main):011:0> puts connection.getMetaData.getDatabaseProductVersion
8.0.2
=> nil
irb(main):012:0> puts connection.getMetaData.getDatabaseProductName
PostgreSQL
=> nil

Unfortunately, I don't think that helps you much if you need to identify Redshift. :(

@kares
Copy link
Member

kares commented Aug 7, 2013

thanks @wilerson! yes that's no help really - I'm sorry but I do not have the time to support this, if someone cares please look into it ... I'll be happy to accept PRs

@wilerson
Copy link

wilerson commented Aug 7, 2013

@kares As there's apparently no way to detect Redshift, I suppose I could patch in an optional configuration option. As Redshift does not support TIMESTAMP WITH TIMEZONE, I suppose there would be no issues on avoiding these configurations for people who explicitly choose Redshift databases, right?

@findchris
Copy link
Author

Good news. I got a response from AWS:

You can use the "select version();" SQL statement to gather this information

Here is an example output:
                                                         version
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.647

Seems like a simple way to detect Redshift, no?

@Gauravshah
Copy link

created a silly gist https://gist.github.com/Gauravshah/a5774acdb75db6ce417b

@kares
Copy link
Member

kares commented Apr 1, 2015

there's an attempt to fix this on the 1-3-stable branch (will be in next 1.3 release) ... please for the future consider opening a PR instead of showcasing patches (should not be that hard esp. when knowing the places that need to get tuned). there's likely more incompatibilities and if someone concerned about Redshift did run the tests against it would likely reveal more issues.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

4 participants