Add support for multi-dimensional Postgres Arrays

[HoodyJazz]

Backend

PostgreSQL

What do you want to add to diesel?

Hi, is there a plan to add support for multi-dimensional arrays in diesel? I took a bit of a deep dive into diesels array implementation today as well as the Postgres byte encoding for array types and I thought I might give it a go, starting with 2D-arrays first.

Since Postgres defines MAXDIM=6 for arrays, I guess it would technically be possible to add support for all possible dimensions, but I feel like adding 2D support first would be a simpler approach.

I might totally have a blind spot here and underestimate how hard this is or miss some side effects this would cause. But after my research today I'll probably give it a go just out of curiosity anyways. If you are interested in the results, let me know.

Implementation notes

Implement FromSql<Array<ST>, Pg> for Vec<Vec<T>> for arrays with ndim=2 and the equivalent ToSql trait.
In case of a 2D array, the array header would include two additional i32 fields before the actual array payload, containing num_elements and lower_bound for the 2nd dimension. With that information one can loop over the array values accordingly and parse them into a 2D vector of size ndim1 x ndim2.

I haven't looked at the ToSql implementation as closely yet, but I assume taking a slice of a slice one has to construct the correct array header and then flatten the values.

Additional details

I'd keep the current assumption of lower_bound=1 for all dimensions.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

[weiznich]

Thanks for writing this proposal.

The main problem with implementing support for multidimensional arrays for diesel is not writing the actual ToSql and FromSql impls. These should be relatively straightforward. The problematic part is the a level higher with what diesel tries to guarantee about these kind of type mappings. We generally only want to support type mappings that cannot fail. Unfortunately it's not possible to detect how many dimensions a postgres array.

Postgresql doesn't seem to store information about array dimensions in any way. For example postgresql is perfectly fine with the following set of queries:

CREATE TEMPORARY TABLE FOO (
test_array text[],
test_multi_array text[][],
); 
insert into foo values ('{"test", "test2"}', '{{"test"}, {"test2"}}');
insert into foo values ('{{"test"}, {"test2"}}', '{"test", "test2"}');
select * from foo;

That will result in an output like

    test_array    | test_multi_array 
------------------+------------------
 {test,test2}     | {{test},{test2}}
 {{test},{test2}} | {test,test2}

If you check the table definition via \d foo you get something like that:

 tbl |       col        | datatype 
-----+------------------+----------
 foo | test_array       | text[]
 foo | test_multi_array | text[]

So overall postgres doesn't differentiate between single and multidimensional arrays in any way.
Given these results I'm open to consider to add higher dimensional mappings to just Array<T> as that's how postgres behave. I'm happy to review a PR for this if it includes tests + documentation

[HoodyJazz]

Hi Weiznich, thank you for your thorough reply and for considering my request. :)
Adding the support for Array was exactly what I had in mind, as I don't think there is any other type that would be viable. I didn't really make that clear. I'm aware of Postgres' lack of differentiation between arrays of different dimensions, that was what prompted me to investigate this actually.
I can probably start working on this next week and will let you know when I have anything worth reviewing. I assume in that case you'd want an issue created for it and so forth.
Have a nice weekend.

[weiznich]

That sounds good, I converted this discussion in an enhancement issue to track it: #4958