Thread: [GENERAL] parray_gin and \d errors in PG10
After installing parray_gin extension and pg_upgrading another instance,
\d is failing like so:
[pryzbyj@database ~]$ psql ts -c '\d pg_class'
ERROR: operator is not unique: "char"[] @> unknown
LINE 6: (stxkind @> '{d}') AS ndist_enabled, ^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
[pryzbyj@database ~]$ psql ts -c '\d pg_class' -E
[...]
********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),',') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN
pg_catalog.pg_attributea ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns,
(stxkind@> '{d}') AS ndist_enabled, (stxkind @> '{f}') AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1259'
ORDER BY 1;
**************************
ERROR: operator is not unique: "char"[] @> unknown
LINE 6: (stxkind @> '{d}') AS ndist_enabled, ^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
Thankfully this is still working:
ts=# \do @>
List of operators Schema | Name | Left arg type | Right arg type |
Resulttype | Description
------------+------+---------------+----------------+-------------+----------------------------------------pg_catalog |
@> | aclitem[] | aclitem | boolean | containspg_catalog | @> | anyarray | anyarray |
boolean | containspg_catalog | @> | anyrange | anyelement | boolean | containspg_catalog | @> |
anyrange | anyrange | boolean | containspg_catalog | @> | box | box | boolean
|containspg_catalog | @> | box | point | boolean | containspg_catalog | @> | circle |
circle | boolean | containspg_catalog | @> | circle | point | boolean |
containspg_catalog| @> | jsonb | jsonb | boolean | containspg_catalog | @> | path |
point | boolean | containspg_catalog | @> | polygon | point | boolean |
containspg_catalog| @> | polygon | polygon | boolean | containspg_catalog | @> | tsquery |
tsquery | boolean | containspublic | @> | hstore | hstore | boolean | public |
@> | text[] | text[] | boolean | text array contains compared by strict
(15 rows)
This query works fine when adding cast to text[]:
ts=# SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),',') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN
pg_catalog.pg_attributea ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns,
(stxkind@> '{d}'::text[]) AS ndist_enabled, (stxkind @> '{f}'::text[]) AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1259'
ORDER BY 1;oid | stxrelid | nsp | stxname | columns | ndist_enabled | deps_enabled
-----+----------+-----+---------+---------+---------------+--------------
(0 rows)
Is this to be considered an issue with parray_gin or with psql ?
I don't think that's an urgent problem to fix, but if someone has a workaround
for \d I would appreciate if you'd pass it along :)
Thanks in advance
Justin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Justin Pryzby <pryzby@telsasoft.com> writes:
> After installing parray_gin extension and pg_upgrading another instance,
> \d is failing like so:
> [pryzbyj@database ~]$ psql ts -c '\d pg_class'
> ERROR: operator is not unique: "char"[] @> unknown
> LINE 6: (stxkind @> '{d}') AS ndist_enabled,
Ugh.
> Thankfully this is still working:
> ts=# \do @>
> ...
> pg_catalog | @> | anyarray | anyarray | boolean | contains
> ...
> public | @> | text[] | text[] | boolean | text array contains compared by strict
I'm inclined to think it wasn't very bright of parray_gin to have
installed an operator that's confusable with the builtin anyarray @>
anyarray operator. Still, we might as well try to work around that.
> This query works fine when adding cast to text[]:
No, that will fail entirely if you don't have parray_gin installed,
because stxkind is of type "char"[], and "char"[] @> text[] will not
match the anyarray operator. Possibly we could use
(stxkind @> '{d}'::pg_catalog."char"[])
That works for me without parray_gin installed, but I wonder whether
it fails due to ambiguity if you do have parray_gin installed. In
principle this'd still match the text[] @> text[] operator, and I'm
not sure whether we have an ambiguity resolution rule that would
prefer one over the other.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, Oct 22, 2017 at 02:36:12PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > After installing parray_gin extension and pg_upgrading another instance,
> > \d is failing like so:
>
> > [pryzbyj@database ~]$ psql ts -c '\d pg_class'
> > ERROR: operator is not unique: "char"[] @> unknown
> > LINE 6: (stxkind @> '{d}') AS ndist_enabled,
> match the anyarray operator. Possibly we could use
>
> (stxkind @> '{d}'::pg_catalog."char"[])
>
> That works for me without parray_gin installed, but I wonder whether
> it fails due to ambiguity if you do have parray_gin installed. In
> principle this'd still match the text[] @> text[] operator, and I'm
> not sure whether we have an ambiguity resolution rule that would
> prefer one over the other.
ts=# SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname, (SELECT
pg_catalog.string_agg(pg_catalog.quote_ident(attname),',') FROM pg_catalog.unnest(stxkeys) s(attnum) JOIN
pg_catalog.pg_attributea ON (stxrelid = a.attrelid AND a.attnum = s.attnum AND NOT attisdropped)) AS columns,
(stxkind@> '{d}'::pg_catalog."char"[]) AS ndist_enabled, (stxkind @> '{d}'::pg_catalog."char"[]) AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1259'
ORDER BY 1;
ERROR: operator is not unique: "char"[] @> "char"[]
LINE 6: (stxkind @> '{d}'::pg_catalog."char"[]) AS ndist_enabled, ^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
Justin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Justin Pryzby <pryzby@telsasoft.com> writes:
> On Sun, Oct 22, 2017 at 02:36:12PM -0400, Tom Lane wrote:
>> ... Possibly we could use
>> (stxkind @> '{d}'::pg_catalog."char"[])
>> That works for me without parray_gin installed, but I wonder whether
>> it fails due to ambiguity if you do have parray_gin installed.
> [ yup ]
Bleah.
One option is to use OPERATOR(pg_catalog.@>), which aside from being
really ugly, isn't a complete fix because it still wouldn't work if
someone had decided to install parray_gin into the pg_catalog schema.
Or maybe what we should do is to avoid @> in favor of using
('d' = any(stxkind))
That's a bit less nice because it doesn't generalize as cheaply to
looking for multiple stxkind values, but since this query has no
current need for that, maybe it's fine.
Anyway, the fact that this is such a mess points up why trying to
alias polymorphic operators isn't such a hot idea. parray_gin
really ought to get rid of that operator.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I wrote:
> Or maybe what we should do is to avoid @> in favor of using
> ('d' = any(stxkind))
Pushed that way.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general