SQL: Array of UUIDs as placeholder for SELECT

General
#1

Hi there,

I’m trying to set up an Elixir project (Phoenix, Ecto, Absinthe) connecting to CockroachDB.
I use a modified PostgreSQL driver and roughly followed these steps to make it work with UUIDs.

The problem is with the following SQL query, that is generated by ecto:
SELECT u0.“url”, u0.“link_id”, u0.“link_id” FROM “urls” AS u0 WHERE (u0.“link_id” = ANY($1)) ORDER BY u0.“link_id” [[<<161, 175, 147, 55, 25, 194, 69, 73, 149, 91, 215, 147, 152, 181, 29, 161>>, <<71, 153, 19, 83, 131, 79, 72, 21, 137, 151, 199, 190, 18, 60, 80, 99>>]]

Note that this is the Elixir console output from Ecto. <<…>> is the Elixir representation for binaries.

The following error is raised:
unsupported OID 2951 with format code formatBinary

Does this mean that the combination of an Array of UUIDs as placeholder is not supported in CockroachDB?

Unfortunately, I couldn’t reproduce the query in the CockroachDB CLI, as I couldn’t find how to use placeholders there. Are there any docs I couldn’t find?

The only query I could get to work is:
SELECT u0.“url”, u0.“link_id”, u0.“link_id” FROM “urls” AS u0 WHERE (u0.“link_id” = ANY(ARRAY[’{a1af9337-19c2-4549-955b-d79398b51da1}’, ‘{47991353-834f-4815-8997-c7be123c5063}’])) ORDER BY u0.“link_id”;

I’d appreciate any hints on

  • whether or not a placeholder array of UUIDs is supported and, if yes, how
  • how to use placeholders in the CockroachDB CLI

Cheers, Arno

#2

Arrays of UUIDs are supported for placeholders; for example, you can use placeholders in the CockroachDB CLI as follows:

PREPARE P AS SELECT u0.“url”, u0.“link_id”, u0.“link_id” FROM “urls” AS u0 WHERE (u0.“link_id” = ANY($1)) ORDER BY u0.“link_id”;
EXECUTE P(ARRAY[‘{47991353-834f-4815-8997-c7be123c5063}’]);

That being said perhaps CockroachDB does support such placeholders but not the binary encoding of the array of UUIDs that your elixir driver sends on the wire (this is what the error message suggests).

I’m filing https://github.com/cockroachdb/cockroach/issues/23063 to track this.

#3

Thanks a lot for the helpful reply and opening the issue.

It all makes more sense now that I understand that there’s always (at least) a pair of PREPARE and EXECUTE queries; Ecto makes it look like it’s a single query.

In the meantime, I’ve dug a bit into Ecto types and Postgrex extensions to find a workaround, but without success so far. I hope it becomes obsolete as soon as the PR is merged.

btw, does your PR comment “plz cherry-pick too” mean it could will be backported to v1.1.6?

#4

it means it will make it to 2.0. We already have cut the release branch so now all bug fixes go to 2.1 by default.

#5

Great. Thanks for the explanation.