From: | Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> |
---|---|
To: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Andrey Borodin <x4mmm(at)yandex-team(dot)ru>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: CUBE seems a bit confused about ORDER BY |
Date: | 2017-12-14 11:08:10 |
Message-ID: | CAPpHfdukhbg49MHu-V3CkiHnU55NX2Z=quHEyu-GHzSR=OTiYg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Dec 14, 2017 at 1:36 PM, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:
> Yes. I bet only few users have built indexes over ~> operator if any.
>>> Ask them to reindex in the release notes seems OK for me.
>>>
>>>
>> Is there a good way to detect such cases? Either in pg_upgrade, so that
>> we can print warnings, or at least manually (which would be suitable for
>> release notes).
>>
>
> Hmm, suppose, fix should be backpatched (because now it's unusable) and
> pg_upgrade should not do anything. Just add release note to 10.0 and 11.0
>
> Oh, check expression is affected too, users will need to reinsert data.
I wrote query to find both constraints and indexes depending on ~> cube
operator.
SELECT dep.classid::regclass AS class,
CASE WHEN dep.classid = 'pg_catalog.pg_class'::regclass THEN
dep.objid::regclass::text
WHEN dep.classid = 'pg_catalog.pg_constraint'::regclass THEN (SELECT
conname FROM pg_catalog.pg_constraint WHERE oid = dep.objid)
ELSE NULL
END AS name
FROM
pg_catalog.pg_extension e
JOIN pg_catalog.pg_depend edep ON edep.refclassid =
'pg_catalog.pg_extension'::regclass AND edep.refobjid = e.oid AND deptype =
'e' AND edep.classid = 'pg_catalog.pg_operator'::regclass
JOIN pg_catalog.pg_operator o ON o.oid = edep.objid AND o.oprname = '~>'
JOIN pg_catalog.pg_depend dep ON dep.refclassid =
'pg_catalog.pg_operator'::regclass AND dep.refobjid = o.oid
WHERE
e.extname = 'cube' AND dep.classid IN
('pg_catalog.pg_constraint'::regclass, 'pg_catalog.pg_class'::regclass);
On the below data schema
create table tmp (c cube, check ((c ~> 0 > 0)));
create index tmp_idx on tmp ((c~>0));
it gives following result
class | name
---------------+-------------
pg_class | tmp_idx
pg_constraint | tmp_c_check
(2 rows)
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Sigaev | 2017-12-14 11:31:28 | Re: [HACKERS] [WIP] Zipfian distribution in pgbench |
Previous Message | John Naylor | 2017-12-14 10:59:12 | Re: WIP: a way forward on bootstrap data |