Cockroach: sql/pgwire: Elixir Connection Issues - Postgrex Protocol Errors - First Test

[Linicks]

All,
When trying to connect to CockroachDB with the Elixir Postgresql driver "Postgrex" (https://github.com/ericmj/postgrex) I get some Errors related to the Postgresql protocol. I'm guessing that there may be some missing pieces in the CockroachDB implementation of the Postgresql protocol that Postgrex is looking for. Here are some details from this initial connection test:

---

Build Vers: go1.6
Build Tag: alpha.v1-1423-ga8c1cb4
Build Time: 2016/03/25 18:03:44

---

Example Error Messages:

16:03:46.617 [error] GenServer #PID<0.3938.0> terminating
** (Postgrex.Error) ERROR (internal_error): syntax error at or near "("
SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
t.typelem, coalesce(r.rngsubtype, 0), ARRAY (
^

(postgrex) lib/postgrex/protocol.ex:87: Postgrex.Protocol.disconnect/2
(postgrex) lib/postgrex/protocol.ex:79: Postgrex.Protocol.connected/1
(db_connection) lib/db_connection/connection.ex:114: DBConnection.Connection.connect/2
(connection) lib/connection.ex:623: Connection.enter_connect/5
(stdlib) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

Last message: nil
State: nil

16:03:46.619 [error] GenServer #PID<0.3939.0> terminating
** (Postgrex.Error) ERROR (internal_error): syntax error at or near "("
SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
t.typelem, coalesce(r.rngsubtype, 0), ARRAY (
^

(postgrex) lib/postgrex/protocol.ex:87: Postgrex.Protocol.disconnect/2
(postgrex) lib/postgrex/protocol.ex:79: Postgrex.Protocol.connected/1
(db_connection) lib/db_connection/connection.ex:114: DBConnection.Connection.connect/2
(connection) lib/connection.ex:623: Connection.enter_connect/5
(stdlib) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

Last message: nil
State: nil

-- Nick

[tamird]

Seems like we don't quite see the whole query, but we do see ARRAY - AFAIK we don't support that yet.

[Linicks]

"ARRAY - AFAIK we don't support that yet"

I just noticed issue #2115, after you mentioned the lack of ARRAY support. I will try again once it's closed.

[jrimmer]

I tried a similar series of tests but using Phoenix Framework hence Ecto w/Postgrex as a starting point. The initial database create failed upon an ENCODING declaration thusly:
** (Mix) The database for HelloCockroachdb.Repo couldn't be created, reason given: ERROR: XX000: syntax error at or near "ENCODING" CREATE DATABASE "hello_cockroachdb_dev" ENCODING='UTF8'

I proceeded to manually create the database but upon creation of a basic model encountered an error related to the lack of ARRAY support mentioned here upon migration:

** (Postgrex.Error) ERROR (internal_error): syntax error at or near "(" SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput, t.typelem, coalesce(r.rngsubtype, 0), ARRAY (

I acknowledge this is more of an FYI at this point.

[tlvenn]

We would very much like to be able to use Cockroach with Elixir but it seems that for the driver (postgrex) to support it, the following issues would need to be resolved:

• [x] Ignores closing prepared query protocol packets
• [ ] Savepoints aren't supported and when followed by a sync message only returns an error message and not the expected ready message
• [ ] Sends errors messages with code internal_error when postgreSQL would return a more specific error code

Source: https://github.com/elixir-ecto/postgrex/issues/213

Is there any chance the above could be solved to help out on the elixir side ?

[bdarnell]

I thought we already supported the "close" packet for prepared statements. Can you provide more detail about what's not working for you?

There are no plans to support savepoints (beyond the limited form that we currently support for transaction retries).

For error codes, we're unlikely to ever be 100% compatible with postgres in this respect, but if you can identify specific error codes that you check for we can prioritize supporting those cases.

[fishcakez]

@bdarnell I am using beta-20160728 @ 2016/07/28 17:02:34 (go1.6.3)

A Close is sent to cockroach but a CloseComplete is never received. There are at least three situations where this is occurring for us:

• Close Sync, where the first received message is ReadyForQuery.
• Close Parse Describe Flush, where the first received message is ParseComplete
• Parse Describe Flush Close Sync, where the next received message after the description is ReadyForQuery

The issue relating to savepoint error handling was a bug in the client, not cockroach. However there is a surprise that a savepoint error does not cause the transaction to enter the failed status.

The different error codes were causing some test failures.

There is also an issue with numeric values. The DataRow message from SELECT 42::numeric has 1 column value in length. However it will include 2 empty column values \x00\x00 after the numeric column value.

[tlvenn]

Regarding matched error codes in postgrex test files, here is my finding:
All postgres error codes are loaded from pg source code and mapped to given atom

https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/errcodes.txt

As far as test goes, I have ignored error code matched in error_code_test.exs given it's to test the mapping logic between int error codes and atoms.

Only 8 error codes are matched in the tests as follow:

| error code | tests |
| --- | --- |
| feature_not_supported | alter_test |
| invalid_catalog_name | login_test |
| syntax_error | query_test |
| invalid_text_representation | query_test, transaction_test |
| unique_violation | query_test, transaction_test |
| in_failed_sql_transaction | query_test, transaction_test |
| query_canceled | query_test |
| invalid_savepoint_specification | transaction_test |

[bdarnell]

Thanks, we'll look into these.

[knz]

I have created separate issues to track your various findings.

Regarding the error codes, what do you propose would work the best for you? As Ben explained earlier we're not keen on matching pg one-for-one, but what would be the next best thing?

[fishcakez]

@knz could we revisit this question after #8296 because is preventing most of our test suite from running?

[knz]

Ok, let's do that

[fishcakez]

Fixing #8296 gets us down to 151 failures (from 213). A lot of these failures are because of unsupported types, tables, functions, SAVEPOINT, COPY, different error codes or #8298. There are only two cases I can think of where error codes really matter to us but the tests fail beforehand because of type errors so I will investigate those shortly (our tests like int4).

The remaining failures are:

• The RowDescription when describing the query SELECT NULL gives a single column oid 0 but there isn't an oid of 0 postgresql will give 705 (unknown).
• An empty query will fail with an error, postgresql handles this with an EmptyQueryResponse message, instead of CommandComplete. This may not matter to cockroach.
• Float decoding NaN::float8 (this is an error in our driver as the data is a valid NaN encoded float). This is a bug in the client and not cockroach.

[knz]

Hi @fishcakez we've addressed #8298 and I have filed the remaining issues that you found. Is there anything else you've found since then?

[tlvenn]

@fishcakez any chance to give it another try now that pretty much all issues have been closed and a new beta has been released ? I wonder if the following from changelog can help a bit as well:

Added new information_schema metatables as well as initial support for the pg_catalog database.

[fishcakez]

I will try to catch up with this again soon.

[tlvenn]

I had a little chat with @fishcakez a few days back and it appears there are still too many issues using the wire protocol/SQL to really make Postgrex working with CockroachDB :(

So the futur of Elixir/Ecto with CockroachDB does not look too bright right now, it seems it will take some substantial efforts to get something working.

It's pretty sad, was very eager to use CockroachDB in my new project but it looks like I have no other choice but to stick with Postgres for the time being.

[knz]

It would really help if you could still give us an overview of what you've run into this time.
"Substantial efforts" is exactly the kind of things we're willing to spend on compatibility!

[tlvenn]

@fishcakez care to elaborate a little bit on the subject please ?

[fishcakez]

@knz the most significant barrier is lack of pg_type, pg_attribute and savepoints. I think without these postgrex won't support cockroachdb, especially savepoints. I know this isn't what you want to hear and I hope you understand that our incentives do not align which is unfortunate. Postgrex has existing API around savepoints, which will not work with cockroachdb. This means it is awkward for us to support it because our existing API won't work. This also means I am not inclined to merge changes to postgrex to provide partial or incremental support for cockroachdb when we aren't going to support it officially.

Also Postgrex is very tied to Ecto (like an ORM but without objects), it is even maintained by the same group of people. The feature set of postgrex is based on Ecto and we only add features to postgrex that we want in Ecto. This leads to a very opinionated client, for example we only support extended queries. To run Ecto's integration test suite or to use Ecto's testing tools for user projects requires the savepoint API. This means we can't support cockroachdb in the main project that determines postgrex's features either.

A postgrex connection uses the pg_type and pg_attribute tables to determine how to encode/decode types and there is a system for custom decoders to be used based on the values from these tables. If the tables don't exist then we can't start a connection. It might be that you don't support the SQL query we run against these tables, it is approximately:

    SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
           t.typelem, ARRAY (
      SELECT a.atttypid
      FROM pg_attribute AS a
      WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
      ORDER BY a.attnum
    )
    FROM pg_type as t
    WHERE t.oid NOT IN (SELECT unnest(ARRAY[1,2]))"

I no longer have my branch of postgrex that could run the postgrex test suite against cockroachdb. If we try to run master (or any recent tag) we get an error setting up the tests:

ERROR:  syntax error at or near "TEMPLATE"
CREATE DATABASE postgrex_test TEMPLATE=template0 ENCODING='UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';

I am sorry that I have quite the opposite of "substantial effort" to help you. I reported the previous issues because I was testing postgrex against cockroachdb to discover issues in postgrex. My advise to anyone wanting to use cockroachdb is to fork postgrex and remove features until it works or use a different client. There are numerous Erlang ones.

[knz]

First of all thank you very much for your detailed answer.
There are many action items in your notes, and I'd like to share that we are already working on many of them!

To summarize the items:

• [x] pg_attribute - we already have an implementation of that (Sept 28), and if that's not sufficient we could perhaps extend it as needed
• [x] pg_type - implemented since Oct 26, again we can extended as needed
• [x] array constructor syntax: #10585 - merged 3 days ago
• [x] additional attributes and syntax in CREATE DATABASE: filed as #10734
• [x] unnest() function over arrays (expands an array into a set of rows): #10520
• [x] support select unnest(...) as opposed to select * from unnest(...): #11215
• [ ] savepoints: filed as #10735.
• [x] pg_range: filed as #10762.
• [x] array constructor from subquery: filed as #10763
• [ ] correlated subqueries #3288.
• [ ] function conversion names in pg_type #12526

Are there any items I forgot to add?

[cuongdo]

Thank you all for working through these issues and helping us understand what work remains. As @knz mentioned, we've added support for some of the missing features such as pg_attribute, and we'll continue to work through the other issues through early 2017.

[tlvenn]

Btw, right now, the immediate stumbling block is still the lack of support for ARRAY as reported by the OP:

Postgrex.Protocol (#PID<0.822.0>) failed to connect: ** (Postgrex.Error) ERROR XX000 (internal_error): syntax error at or near "("
SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
       t.typelem, coalesce(r.rngsubtype, 0), ARRAY (

Now that #10585 has landed, is there any chance to provide support for ARRAY soon ?

[knz]

The short answer is yes soon however I'd like to see the full SQL text of that query in the OP, is that possible?

Sent from my Android device with K-9 Mail. Please excuse my brevity.

[tlvenn]

Here you go @knz :
https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/types.ex#L25

[tlvenn]

So at most:

SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
           t.typelem, coalesce(r.rngsubtype, 0), ARRAY (
      SELECT a.atttypid
      FROM pg_attribute AS a
      WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
      ORDER BY a.attnum
    )
    FROM pg_type AS t
    LEFT JOIN pg_range AS r ON r.rngtypid = t.oid
    WHERE t.oid NOT IN (SELECT unnest(ARRAY[#{Enum.join(oids, ",")}]))

With #{Enum.join(oids, ",")} being interpolated as list of oids

[tlvenn]

Looks like that beside ARRAY and unnest support, we will also need to implement pg_range

[knz]

Ok we're getting somewhere. Thanks for reporting all of this.
Would you be ok with our product lead contacting you separately to discuss your plans and your timeline?

[fishcakez]

Are there any items I forgot to add?

@knz that looks good.

@tlvenn you can try this branch if you want to find other incompatibilities with cockroach: https://github.com/fishcakez/postgrex/tree/bootstrap_cockroach. The test suite won't run but will be able to start a connection.

[tlvenn]

@knz sure thing.

@fishcakez great thanks ! I will play with it and test against cockroachdb master and will report my findings.

[tlvenn]

There is an issue with the create database with encoding:

ERROR XX000 (internal_error): syntax error at or near "UTF8"
CREATE DATABASE "test_dev" ENCODING 'UTF8'

There is no = between ENCODING and 'UTF8' which is valid given the = is optional yet it seems the = is mandatory with cockroachdb.

[jordanlewis]

@tlvenn That syntax error was fixed by @knz in ee5a24ee404a6aea6944f4567405f2b18edbe10b.

[tlvenn]

Since quite a few issues have been closed, I though it would be a good time to give an update on where we stand now.

CockroachDB: Latest from master(Build Tag: 6601da9)
Ecto: 2.1.0
Postgrex: 0.13

The error happens on the same SQL select as before:

sql/executor.go:519  [n1,client=127.0.0.1:56885,user=root] execRequest: SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
       t.typelem, coalesce(r.rngsubtype, 0), ARRAY (
  SELECT a.atttypid
  FROM pg_attribute AS a
  WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
  ORDER BY a.attnum
)
FROM pg_type AS t
LEFT JOIN pg_range AS r ON r.rngtypid = t.oid

With Ecto/postgres reporting:

** (Postgrex.Error) ERROR XX000 (internal_error): source name "t" not found in FROM clause
    (db_connection) lib/db_connection/connection.ex:148: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

I have confirmed that the same error happens using cockroach sql cli:

root@:26257> SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          ->        t.typelem, coalesce(r.rngsubtype, 0), ARRAY (
          ->   SELECT a.atttypid
          ->   FROM pg_attribute AS a
          ->   WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
          ->   ORDER BY a.attnum
          -> )
          -> FROM pg_type AS t
          -> LEFT JOIN pg_range AS r ON r.rngtypid = t.oid;
pq: source name "t" not found in FROM clause

Now the weird part is I believe this should work now. All the necessary tables exists and the ARRAY constructor is also supported.

@knz any idea what might be the culprit ? Thanks a lot in advance !

[tlvenn]

Actually looking at the remaining issue listed above, it does seem to be the culprit

sql: extract DTable-typed render expressions as cross-join https://github.com/cockroachdb/cockroach/issues/11215

Is there any chance to fix that issue soon ? I believe this is the only remaining thing blocking Ecto/Postgrex to connect to the DB properly.

[knz]

Yes this is #3288.

You can perhaps fix with:

SELECT oid, typname, typsend, typreceive, typoutput, typinput,
       typelem, coalesce(r.rngsubtype, 0), arr
FROM 
  (SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          t.typelem, ARRAY_AGG (a.attypid) AS arr
   FROM 
       (SELECT oid, typname, typsend, typreceive, typoutput, typinput, typelem 
          FROM pg_type) AS t
       JOIN 
       (SELECT atttypid, attrelid 
          FROM pg_attribute WHERE attnum > 0 AND NOT attisdropped  ORDER BY attnum) AS a
       ON a.attrelid = t.typrelid
   GROUP BY t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,  t.typelem)
  LEFT JOIN pg_range AS r ON r.rngtypid = t.oid

(I transformed the query manually and didn't test it. The gist of the solution is to extract the correlated sub-query into a join).

[tlvenn]

Thanks for the quick feedback @knz !

I fixed the sql you posted (1 typo and 1 missing field) to end up with:

SELECT oid, typname, typsend, typreceive, typoutput, typinput,
       typelem, coalesce(r.rngsubtype, 0), arr
FROM (SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          t.typelem, ARRAY_AGG (a.atttypid) AS arr
      FROM (SELECT oid, typname, typsend, typreceive, typoutput, typinput, typelem, typrelid FROM pg_type) AS t
      JOIN (SELECT atttypid, attrelid FROM pg_attribute WHERE attnum > 0 AND NOT attisdropped ORDER BY attnum) AS a ON a.attrelid = t.typrelid
      GROUP BY t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,  t.typelem)
LEFT JOIN pg_range AS r ON r.rngtypid = t.oid;

Sadly, the same error happens:

root@:26257> SELECT oid, typname, typsend, typreceive, typoutput, typinput,
          ->        typelem, coalesce(r.rngsubtype, 0), arr
          -> FROM (SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          ->           t.typelem, ARRAY_AGG (a.atttypid) AS arr
          ->       FROM (SELECT oid, typname, typsend, typreceive, typoutput, typinput, typelem, typrelid FROM pg_type) AS t
          ->       JOIN (SELECT atttypid, attrelid FROM pg_attribute WHERE attnum > 0 AND NOT attisdropped ORDER BY attnum) AS a ON a.attrelid = t.typrelid
          ->       GROUP BY t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,  t.typelem)
          -> LEFT JOIN pg_range AS r ON r.rngtypid = t.oid;
pq: source name "t" not found in FROM clause

[knz]

Ah this one is simpler, there was a small mistake remaining, try this:

SELECT oid, typname, typsend, typreceive, typoutput, typinput,
       typelem, coalesce(r.rngsubtype, 0), arr
FROM (SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          t.typelem, ARRAY_AGG (a.atttypid) AS arr
      FROM (SELECT oid, typname, typsend, typreceive, typoutput, typinput, typelem, typrelid FROM pg_type) AS t
      JOIN (SELECT atttypid, attrelid FROM pg_attribute WHERE attnum > 0 AND NOT attisdropped ORDER BY attnum) AS a ON a.attrelid = t.typrelid
      GROUP BY t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,  t.typelem) AS t
LEFT JOIN pg_range AS r ON r.rngtypid = t.oid;

[tlvenn]

It works somewhat ;)

root@:26257> SELECT oid, typname, typsend, typreceive, typoutput, typinput,
          ->        typelem, coalesce(r.rngsubtype, 0), arr
          -> FROM (SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          ->           t.typelem, ARRAY_AGG (a.atttypid) AS arr
          ->       FROM (SELECT oid, typname, typsend, typreceive, typoutput, typinput, typelem, typrelid FROM pg_type) AS t
          ->       JOIN (SELECT atttypid, attrelid FROM pg_attribute WHERE attnum > 0 AND NOT attisdropped ORDER BY attnum) AS a ON a.attrelid = t.typrelid
          ->       GROUP BY t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,  t.typelem) AS t
          -> LEFT JOIN pg_range AS r ON r.rngtypid = t.oid;
+-----+---------+---------+------------+-----------+----------+---------+---------------------------+-----+
| oid | typname | typsend | typreceive | typoutput | typinput | typelem | COALESCE(r.rngsubtype, 0) | arr |
+-----+---------+---------+------------+-----------+----------+---------+---------------------------+-----+
+-----+---------+---------+------------+-----------+----------+---------+---------------------------+-----+
(0 rows)

[knz]

ok, found it: #12523

[tlvenn]

Ha great, good catch. Would that be difficult to implement ?

[knz]

@tlvenn I just updated that issue, just perhaps this may be working as intended: since cockroachdb does not implement composite types for now, it is expected that the reltypid field be 0. We also do not support range types so pg_range is empty in cockroachdb. So strictly speaking it is semantically expected that this query is returning 0 rows.

[tlvenn]

I see your point but if I am not wrong, it would mean that the array would be empty not that the query would return 0 row. Here are the results without the array:

root@:26257> SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
          ->        t.typelem, coalesce(r.rngsubtype, 0)
          -> FROM pg_type AS t
          -> LEFT JOIN pg_range AS r ON r.rngtypid = t.oid;
+------+-------------+---------+------------+-----------+----------+---------+---------------------------+
| oid  |   typname   | typsend | typreceive | typoutput | typinput | typelem | COALESCE(r.rngsubtype, 0) |
+------+-------------+---------+------------+-----------+----------+---------+---------------------------+
| 2283 | anyelement  |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1082 | date        |       0 |          0 |         0 |        0 |       0 |                         0 |
|  701 | float       |       0 |          0 |         0 |        0 |       0 |                         0 |
|   21 | int         |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1043 | string      |       0 |          0 |         0 |        0 |       0 |                         0 |
|   23 | int         |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1007 | int[]       |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1114 | timestamp   |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1184 | timestamptz |       0 |          0 |         0 |        0 |       0 |                         0 |
|   17 | bytes       |       0 |          0 |         0 |        0 |       0 |                         0 |
|   20 | int         |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1005 | int[]       |       0 |          0 |         0 |        0 |       0 |                         0 |
|   16 | bool        |       0 |          0 |         0 |        0 |       0 |                         0 |
|  700 | float       |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1186 | interval    |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1700 | decimal     |       0 |          0 |         0 |        0 |       0 |                         0 |
|   25 | string      |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1009 | string[]    |       0 |          0 |         0 |        0 |       0 |                         0 |
| 1016 | int[]       |       0 |          0 |         0 |        0 |       0 |                         0 |
+------+-------------+---------+------------+-----------+----------+---------+---------------------------+
(19 rows)

And the same with postgresql 9.5:

 oid   |                     typname                     |       typsend       |     typreceive      |      typoutput       |      typinput       | typelem | coalesce 
--------+-------------------------------------------------+---------------------+---------------------+----------------------+---------------------+---------+----------
     16 | bool                                            | boolsend            | boolrecv            | boolout              | boolin              |       0 |        0
     17 | bytea                                           | byteasend           | bytearecv           | byteaout             | byteain             |       0 |        0
     18 | char                                            | charsend            | charrecv            | charout              | charin              |       0 |        0
     20 | int8                                            | int8send            | int8recv            | int8out              | int8in              |       0 |        0
     21 | int2                                            | int2send            | int2recv            | int2out              | int2in              |       0 |        0
     23 | int4                                            | int4send            | int4recv            | int4out              | int4in              |       0 |        0
     24 | regproc                                         | regprocsend         | regprocrecv         | regprocout           | regprocin           |       0 |        0
     25 | text                                            | textsend            | textrecv            | textout              | textin              |       0 |        0
     26 | oid                                             | oidsend             | oidrecv             | oidout               | oidin               |       0 |        0
     27 | tid                                             | tidsend             | tidrecv             | tidout               | tidin               |       0 |        0
     28 | xid                                             | xidsend             | xidrecv             | xidout               | xidin               |       0 |        0
     29 | cid                                             | cidsend             | cidrecv             | cidout               | cidin               |       0 |        0
     71 | pg_type                                         | record_send         | record_recv         | record_out           | record_in           |       0 |        0
     75 | pg_attribute                                    | record_send         | record_recv         | record_out           | record_in           |       0 |        0
     81 | pg_proc                                         | record_send         | record_recv         | record_out           | record_in           |       0 |        0
     83 | pg_class                                        | record_send         | record_recv         | record_out           | record_in           |       0 |        0
    114 | json                                            | json_send           | json_recv           | json_out             | json_in             |       0 |        0
    142 | xml                                             | xml_send            | xml_recv            | xml_out              | xml_in              |       0 |        0
    194 | pg_node_tree                                    | pg_node_tree_send   | pg_node_tree_recv   | pg_node_tree_out     | pg_node_tree_in     |       0 |        0
     32 | pg_ddl_command                                  | pg_ddl_command_send | pg_ddl_command_recv | pg_ddl_command_out   | pg_ddl_command_in   |       0 |        0
    210 | smgr                                            | -                   | -                   | smgrout              | smgrin              |       0 |        0
    602 | path                                            | path_send           | path_recv           | path_out             | path_in             |       0 |        0
    604 | polygon                                         | poly_send           | poly_recv           | poly_out             | poly_in             |       0 |        0
    700 | float4                                          | float4send          | float4recv          | float4out            | float4in            |       0 |        0
    701 | float8                                          | float8send          | float8recv          | float8out            | float8in            |       0 |        0

I am kinda surprised cockroachb is returning 0 for all those columns... This is not what Postgrex is expecting at all.

Looking at how @fishcakez implemented a quick hack to bootstrap cockroachdb on a specific branch of Postgrex, you can see the expected values.

https://github.com/fishcakez/postgrex/commit/cb0be2f94bb13ca6f5c5e10d466fb3c2240606f2#diff-5647d741e3cd245ce78112454a880da8

[knz]

Two things.
1) regarding your comment about pg_type. I hear you, it is the case that cockroachdb currently does not populate pg_type like CockroachDB. Nevertheless, even supposing it did, the join with pg_attribute would still return far fewer rows, because (correct me if I am wrong) what this entire query is trying to do is to capture the relationship between composite types and the types inside them. Since cockroachdb does not support composite types, all the arrays in the "arr" column will be empty. Is that what you expect?

2) by comparing the output between pg and cockroachdb for my manually edited query above I realize the query is probably erroneous, so that explains largely why the output is incorrect. This is because I do not fully understand yet how to perform the transform from correlated subquery to join (suggested in #3288). I'd need to think about it more.

[knz]

Also related question, the "typsend" "typreceive" etc are conversion functions from text/binary to and from SQL data. The query we're talking about also extracts these columns. Does Postgresx then later use these functions to perform data conversions? If so we'd need to look at that separately.

[fishcakez]

Does Postgresx then later use these functions to perform data conversions?

Postgrex allows its users to define how to encode/decode types. It works by defining an encode/decode callback which is used for all oid when typsend (or one of the other columns) matches the calllbacks values. For example send: "int4send" would match anything that has typsend of int4send.

[tlvenn]

all the arrays in the "arr" column will be empty. Is that what you expect?

Yes that would be correct given there are only simple types in CockroachDB at the moment.

[knz]

I added a new issue for this: #12526.

[tlvenn]

Thanks @knz !

[tlvenn]

Little follow up on the support of Elixir/Postgrex/Ecto

Using a patched version of Postgrex which simply hardcore the expected pg_type values for all oids until these issues are fixed #12526 #13524 , I am able to make it work.

I have found what seems to be a new issue however, here is the query with the error reported by cockroachdb:

[debug] QUERY ERROR source="roles" db=7.8ms
SELECT r0."id", r0."public", s1."id" FROM "roles" AS r0 INNER JOIN "staff" AS s1 ON s1."id" = ANY($1) INNER JOIN "staff_roles" AS s2 ON s2."staff_id" = s1."id" WHERE (s2."role_id" = r0."id") ORDER BY s1."id" [[216513146406240257]]
[error] Task #PID<0.639.0> started from #PID<0.630.0> terminating
** (Postgrex.Error) ERROR XX000 (internal_error): error in argument for $1: unsupported OID: 1016
    (ecto) lib/ecto/adapters/sql.ex:436: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:130: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:35: Ecto.Repo.Queryable.all/4
    (elixir) lib/enum.ex:1229: Enum."-map/2-lists^map/1-0-"/2

In the log, i only see the following:

29371 sql/executor.go:392  [client=127.0.0.1:62897,user=root,n1] preparing: SELECT r0."id", r0."public", s1."id" FROM "roles" AS r0 INNER JOIN "staff" AS s1 ON s1."id" = ANY($1) INNER JOIN "staff_roles" AS s2 ON s2."staff_id" = s1."id" WHERE (s2."role_id" = r0."id") ORDER BY s1."id"

Any idea what is going on ? Why dont I see any related error(s) in the logs ?

Thanks a lot in advance.

[jordanlewis]

CockroachDB is reporting that OID 1016 is unsupported. That's the int8[] oid. It seems we don't support the array binary encoding yet - thanks for the report.

[tlvenn]

Thanks for the quick feedback @jordanlewis, any chance to add support for the array binary encoding very soon ? I am kinda stuck without it :(

Thanks a lot in advance !

On a side note, is there any reason why the server is not logging this kind of errors ?

[jordanlewis]

It's an oversight that we're not logging this further. I just remembered that we're already tracking this particular issue in #13358 as well.

[tlvenn]

Ha funny timing ;) Glad to see there is an open issue on this already and I hope this is something relatively easy to implement.

Btw, for anyone who want to give Cockroachdb with Elixir a try, you can use my fork of Postgrex for the time being:

https://github.com/jumpn/postgrex

[liveresume]

Thanks all for working on this. Wondering if we can do basic ops with Ecto yet?

[tlvenn]

Hi @liveresume , yes pretty much everything works just fine now, the only caveat is tests. Ecto Sandbox heavily leverage savepoints in tests and savepoints are not currently supported (#10735).

[petermattis]

Marking as Later because Ecto support isn't on the 1.0 roadmap, though that doesn't mean we'll stop working on this.

[tlvenn]

Hi @petermattis, I believe if we set savepoints aside, it's pretty much a green light already. The only remaining issue to fix so that people can use the default postgrex driver instead of my fork is this one: #12526 which boils down to implement regproc type properly which is on your roadmap already as commented by @jordanlewis.

So I believe you will get another language/ORM to add on your supported ORM list by the time you hit 1.0 :)

[jordanlewis]

Hi @tlvenn, great news! Thanks for keeping track of the remaining items here.

[fire]

@tlvenn Is possible to modify your postgrex library to use bytea for uuids?

[tlvenn]

Hi @fire I am not sure I understand your question / issue ? On CDB side the uuid_v4() return a bytes type which is mapped to bytea and should handled as one already on Postgrex side.

[fire]

@tlvenn oh I mean this:

https://gist.github.com/stevedomin/0ea9d9af96b565cbd0b7 errors about the :uuid type not being defined.

https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/adapters/postgres/connection.ex#L920 This is where the code is and all we need is both UUID.from_binary_to_string! and UUID.string_to_binary!

binary_id isn't defined correctly in ecto. We can fork ecto to have a cockroachdb specific sql. Another way is use the attribute default to auto generate it but that's only on the creation side.

[fire]

@tlvenn https://hexdocs.pm/ecto/Ecto.UUID.html has the UUID related functions if we want to modify ecto, but I think there's a way where we set the type to bytea and use the default of Ecto.UUID.dump(Ecto.UUID.generate())

[yonkeltron]

Can confirm that I get problems with a fresh Phoenix app using stock Ecto and stock Postgrex.

Error:

** (Mix) The database for Cockroach.Repo couldn't be created: ERROR XX000 (internal_error): source name "t" not found in FROM clause

15:04:56.418 [error] GenServer #PID<0.3204.0> terminating
** (Postgrex.Error) ERROR XX000 (internal_error): source name "t" not found in FROM clause
    (db_connection) lib/db_connection/connection.ex:148: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

Elixir-side versions:

  defp deps do
    [{:phoenix, "~> 1.2.4"},
     {:phoenix_pubsub, "~> 1.0"},
     {:phoenix_ecto, "~> 3.0"},
     {:postgrex, ">= 0.0.0"},
     {:phoenix_html, "~> 2.6"},
     {:phoenix_live_reload, "~> 1.0", only: :dev},
     {:gettext, "~> 0.11"},
     {:cowboy, "~> 1.0"}]
  end

Cockroach-side versions:

$ cockroach version
cockroach version
Build Tag:    v1.0.2
Build Time:   2017/06/15 15:11:55
Distribution: CCL
Platform:     darwin amd64
Go Version:   go1.8.3
C Compiler:   4.2.1 Compatible Apple LLVM 8.1.0 (clang-802.0.42)
Build SHA-1:  9e3606bd2863ce7a460fd0c842414673d62f0533
Build Type:   development

[tamird]

Is it possible to get the query that ecto is trying to execute?

[yonkeltron]

Not sure of the query but I get it when running mix ecto.create and am unable to get even past that!

[tlvenn]

@yonkeltron you have to use my Postgrex fork for the time being

https://github.com/jumpn/postgrex

[fire]

@tlvenn Can you make it a proper postgrex fork with a name change?

[jordanlewis]

We should sync up about this again - we've got proper UUID support now, and #12526/#14556 are solved as far as I can tell. What's remaining?

[yonkeltron]

Is this ready to be tested again with Ecto? Happy to spin up a fresh app and give it a shot.

[tlvenn]

@fire this is finally done, I have published the fork at:

https://hexdocs.pm/postgrex_cdb

And to run your test, given CDB does not support arbitrary savepoints, you cant use the sandbox that is bundled with Ecto but fear not, I have created EctoReplaySandbox to leverage a log replay approach to do it:

https://hexdocs.pm/ecto_replay_sandbox

For reference @jordanlewis , the only remaining issue so that we would not need my postgrex fork is support for correlated subqueries which is tracked here: https://github.com/cockroachdb/cockroach/issues/3288

Happy hacking and testing ;)

[cohawk]

@tlvenn et all,

Thank you for your work on this CockroachDB fork of the postgrex adapter.

Over the weekend I spun up a new Elixir :ecto, "2.2.7" project that I was able to get working with CockroachDB and this adapter fork - with some caveats I wanted to share to help others:

https://gist.github.com/cohawk/df29c1c54abd858dd19d8327e862822a

[BramGruneir]

I just wanted to update this issue a bit.

@vilterp and I tried running an example app with Ecto and it looks like we've made significant progress here.

We did run into #32917 during the migration step. So that will be a bit of a blocker. However, once we hacked around that in cockroach, the app actually ran.
I'd really like to be able to run an Ecto or Postgrex test suite against cockroach, but I haven't been able to find the appropriate tests that run against a real DB yet.
So there is still more work to be done here, but we're significantly closer.

[tchoutri]

Hi @BramGruneir! Any update on this?

[fire]

@fishcakez Are you able to provide instructions, the test suite is a bit convoluted to run and the documentation is lacking. There's two suites, one is the sql syntax and the other is an ecto adapter on a real database.

[fishcakez]

MIX_ENV=pg mix test

[jordanlewis]

I'm going to close this in favor of #33441.