Cockroach: sql/pgwire: text decoding and binary en/decoding for arrays is not supported

[jethrogb]

BUG REPORT

1. Please supply the header (i.e. the first few lines) of your most recent
   log file for each node in your cluster.

I170201 17:22:36.747991 1 util/log/clog.go:1012  [config] file created at: 2017/02/01 17:22:36
I170201 17:22:36.747991 1 util/log/clog.go:1012  [config] running on machine: jethroft
I170201 17:22:36.747991 1 util/log/clog.go:1012  [config] binary: CockroachDB CCL beta-20170126 (linux amd64, built 2017/01/26 16:00:11, go1.7.4)
I170201 17:22:36.747991 1 util/log/clog.go:1012  [config] arguments: [./cockroach start --logtostderr]

1. Please describe the issue you observed:

• What did you do?

Tried to run a prepared query such as SELECT * FROM tab WHERE s = ANY($1)

Here's a quick test case that is confirmed working in PostgreSQL 9.5.

#/usr/bin/env ruby
def run_prepared(q,args)
    puts q
    $count ||= 0
    $count += 1
    p $c.prepare("s#{$count}",q)
    p $c.exec_prepared("s#{$count}",args)
end

require "pg"
$c=PG.connect("postgresql://root@localhost:26257/test?sslmode=disable")
p $c.exec("CREATE DATABASE IF NOT EXISTS test")
p $c.exec("CREATE TABLE IF NOT EXISTS tab (s TEXT, i INT, b BYTEA)")
p $c.exec("DELETE FROM tab")
p $c.exec("INSERT INTO tab VALUES ('a', 1, 'a');")
p $c.exec("INSERT INTO tab VALUES ('b', 2, 'b');")
p $c.exec("INSERT INTO tab VALUES ('c', 3, 'c');")
run_prepared("SELECT * FROM tab WHERE s = $1",["a"])
run_prepared("SELECT * FROM tab WHERE i = $1",[2])
run_prepared("SELECT * FROM tab WHERE b = $1",[{value:"c",format:1}])
run_prepared("SELECT * FROM tab WHERE s = ANY($1)",[PG::TextEncoder::Array.new.encode(["b","c"])])
run_prepared("SELECT * FROM tab WHERE i = ANY($1)",[PG::TextEncoder::Array.new.encode([1,3])])
# not sure how to specify a bytea array in Ruby...

• What did you expect to see?

Query results

• What did you see instead?

Various forms of "unsupported OID" error messages

#<PG::Result:0x00000000a75490 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
#<PG::Result:0x00000000a74798 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
#<PG::Result:0x00000000a742c0 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=3>
#<PG::Result:0x00000000a6a220 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=1>
#<PG::Result:0x00000000a69118 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=1>
#<PG::Result:0x00000000a68858 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=1>
SELECT * FROM tab WHERE s = $1
#<PG::Result:0x00000000a68ba0 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
#<PG::Result:0x00000000a63088 status=PGRES_TUPLES_OK ntuples=1 nfields=3 cmd_tuples=1>
SELECT * FROM tab WHERE i = $1
#<PG::Result:0x00000000a62638 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
#<PG::Result:0x00000000a62200 status=PGRES_TUPLES_OK ntuples=1 nfields=3 cmd_tuples=1>
SELECT * FROM tab WHERE b = $1
#<PG::Result:0x00000000a61580 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
#<PG::Result:0x00000000a60c98 status=PGRES_TUPLES_OK ntuples=1 nfields=3 cmd_tuples=1>
SELECT * FROM tab WHERE s = ANY($1)
#<PG::Result:0x00000000a5b6a8 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
/tmp/pg-test.rs:7:in `exec_prepared': ERROR:  error in argument for $1: unsupported OID: 1009 (PG::InternalError)
SELECT * FROM tab WHERE i = ANY($1)
#<PG::Result:0x0000000232b688 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
/tmp/pg-test.rs:7:in `exec_prepared': ERROR:  error in argument for $1: unsupported OID: 1016 (PG::InternalError)

These queries appear to run fine if I don't try to prepare them.

[jordanlewis]

Thanks for the report. The issue is that we don't yet support the Postgres wire encodings for array types in sql/pgwire/v3.go.

[jordanlewis]

Actually the anemic file is sql/pgwire/types.go.

[jordanlewis]

The wire format for array types can be found here: https://github.com/postgres/postgres/blob/master/src/include/utils/array.h

[tamird]

Please remember to add an array test to acceptance/c_test.go. See
http://libpqtypes.esilo.com/man3/pqt-specs.html for reference.

On Mon, Feb 6, 2017 at 2:07 PM, Jordan Lewis [e-mail]
wrote:

The wire format for array types can be found here:
https://github.com/postgres/postgres/blob/master/src/include/utils/array.h

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/cockroachdb/cockroach/issues/13358#issuecomment-277780190,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABdsPJCQi0e3NzenDpMZF5lvCOLzu1dkks5rZ29ggaJpZM4L0qSl
.

[tlvenn]

As discussed with @knz, just wanted to stress out how important this bug is.

The SQL construct Select .. where id = ANY is critical for many patterns especially when dealing with graphs and batching. I am using GraphQL on top of cockroachdb and batching related entities is an essential feature.

Thanks a lot in advance.

[knz]

@tlvenn the thing is that I don't understand, is that this client code seems overly complicated to me.

In particular, why use an ANY expression when the list of values is provided by the client? The standard, much more common and ... CockroachDB-supported way to do this, is the traditional IN keyword!

Instead of: SELECT .. WHERE s= ANY($1) what about SELECT .. WHERE s IN ($1, $2, ...)?

From a technical standpoint there are two separate issues that need to be looked at:

• typing of the placeholder. CockroachDB uses distinct array types for different element types. That's the "first order" issue that causes the symptom above.
• even if the protocol worked OK, we'd have a separate issue on our hands: IN expressions are translated to point lookups (and proper index selection) in CockroachDB, whereas = ANY expressions are not. The performance of the query would be much lower (full table scan) because of this. I filed #13593 for this separate aspect but we're not there yet.

In this light, is there any space for you to consider using IN for the time being?

[tlvenn]

Thanks for the feedback @knz.

You are correct that I would expect the ORM to use SELECT .. WHERE s IN ($1, $2, ...) for this kind of use cases which explains why it does not actually impact so many people.

As you pointed out, ANY array(values) and IN csv(values) is equivalent but it seems like generally speaking, one should favour IN over ANY as it leads to more predictable plans from the planner which in many cases cant use properly indexes when ANY is being used.

http://dba.stackexchange.com/questions/125413/index-not-used-with-any-but-used-with-in

Now some reason, when Ecto is preloading multiple structs using Repo.preload(structs, preloads, opts), it will emit a query that uses ANY rather than IN...

@fishcakez might be able to explain better why Ecto choose to use the former over the later.

[fishcakez]

Using ANY array(..) allows the query to be parameterised, which allows prepare/bind+execute/bind+execute/bind+execute.... style optimisations. I think the queries will be planned equivalently on recent versions of postgresql. Perhaps only 9.2 of the currently supported (officially by postgresql) versions won't do it.

[knz]

Yes pg does the same, I have checked already. But CockroachDB doesn't for now. (Hence issue #13593)

[jethrogb]

ORMs do use the = ANY syntax, this is in fact recommended by some. The main reason is that you don't have to reconstruct and prepare the query every time you do it with a varying number of elements.

[jordanlewis]

For reference, here's how Postgres serializes an array to the wire: https://github.com/postgres/postgres/blob/82f8107b92c9104ec9d9465f3f6a4c6dab4c124a/src/backend/utils/adt/arrayfuncs.c#L1592

[tlvenn]

Thanks a lot @jordanlewis !