On Thu, Dec 14, 2017 at 1:36 PM, Teodor Sigaev <teo...@sigaev.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