lib/pg assumes that all queries that start with "copy" (case-insensitive) are special bulk import queries

[flgr]

lib/pg special cases queries starting with "copy" (case-insensitive) to a code path for bulk imports from passed in data. That's nice and all, but unfortunately it has a false positive.

RedShift (pg-compatible, mostly) has a special statement "copy" that is used to bulk import data from S3 and stuff (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html)

Unfortunately such copy statements will end up in the wrong code path in lib/pg:

pq/conn.go

Line 768 in ae8357d

if len(q) >= 4 && strings.EqualFold(q[:4], "COPY") {

I think you could either use invalid syntax such as "copy" to do this, or use a magic inline comment in the query, e.g. "/ bulk */copy".

My work around until this is fixed upstream is prefixing RedShift copy queries with a space...

[freeformz]

All of the AWS COPY statements require a credentials '....' clause. We could look for that as well and if present handle it like a normal query. I'm not overly fond of that, but it works (at least until Postgres' COPY also takes a credentials clause.

[freeformz]

There is also the sentinel that each of the from locations are AWS specific:

from 's3://....'
from 'emr://...'
from 'dynamodb://...'

[freeformz]

postgres would interpret those from locations as file paths and error AFAICT.

[freeformz]

I guess the real question is, how much other redshift stuff is non standard and would be required for full support and should any of this be included in lib/pq ?

[flgr]

From what I can tell other than that issue things seem to be working perfectly fine!

Von meinem iPhone gesendet

Am 04.11.2016 um 19:35 schrieb Edward Muller notifications@github.com:

I guess the real question is, how much other redshift stuff is non standard and would be required for full support and should any of this be included in lib/pq ?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or mute the thread.

[cbandy]

I guess the real question is, how much other redshift stuff is non standard and would be required for full support and should any of this be included in lib/pq ?

@freeformz do you mean

1. specifically related to COPY? or
2. more generally that lib/pq take some stance on Redshift compatibility?

@flgr #508 is about how Redshift requires TLS negotiation after some amount of traffic.

[msakrejda]

I don't suppose there's a dev Redshift available for testing? It's unfortunate that we can't actually add Travis tests to ensure we maintain compatibility (if we do decide to support it).