fe2d670793
This uses a new feature I developed for Sequel with Ubicloud in mind.
I found that explicit casts for array parameters used in this case
are not required, as long as PostgreSQL can implicitly cast elements of
the array to the type of the LHS of the expression.
Taking an example query from the query parameterization analysis:
```sql
DELETE FROM "applied_action_tag" WHERE (("tag_id" = $1) AND ("action_id" = ANY($2)))
```
In this case, $2 is a PostgreSQL array containing uuids. As action_id is a
uuid type, PostgreSQL will assume $2 is uuid[], and things will work correctly.
It took me a while to figure this out, because PostgreSQL's behavior is
different when using ARRAY. When not auto parameterizing, Sequel literalizes
arrays using ARRAY. Initial work on auto parameterization used ARRAY and
parameterized the elements, and this let me to believe that explicitly casting
was necessary for arrays, when it turns out not to be.
As an example, this doesn't work (assuming $2 is a uuid string):
```sql
DELETE FROM "applied_action_tag" WHERE (("tag_id" = $1) AND ("action_id" = ANY(ARRAY[$2])))
```
For some reason, PostgreSQL thinks the array is text[] instead of
uuid[], even though the LHS is uuid.
The previous work around was to assume string arrays could be represented as
PostgreSQL text[] types. I've used this successfully in other applications,
but in those, I wasn't using uuids or enum types.
As the majority of Ubicloud's usage in these cases is either uuid[] type or
enum array types, the assumption of text[] was not a good one for Ubicloud.
I previously added the Sequel.any_{type,uuid} methods to Ubicloud to make
it easier to fix the failing cases. However, this is really a leaky
abstraction, and it's likely something that would trip up other developers.
By avoiding the explicit cast for string arrays used as parameters,
we can remove all usage of Sequel.any_{type,uuid} and have everything
work correctly. This approach should be safe to run in all
environments. I've made it so that the conversion is performed for
arrays with one or more elements only in frozen testing mode, since that is
how the query parameterization analysis is run and it can result in
a reduced number of distinct queries. It can also catch potential issues
if the tests only test with single element arrays, but we are using multiple
element arrays in production.
By default, only arrays of two elements or more are use this conversion,
because PostgreSQL will use a more optimized query plan for a single element
value list than for a multiple element value list.
Over 10% of Ubicloud's distinct parameterized queries use this new feature
(126/1130).
6.2 KiB
6.2 KiB