Unindex Postgresql

The issue of foreign keys without indexing was the starting point of my blog. Just browsing and I found a postgresql version of unindex query. Copied the query here. Visit the original authors page for more explanation and an example. Expecially liked the commented code /*the first index columns must be the same as the key columns, but order doesn't matter*/.

SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table,
       'create index '||c.conname||'_idx on '||c.conrelid::regclass||'('||string_agg(a.attname, ',' ORDER BY x.n)||');' idx
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               @> c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;

Access Path Suggestor - Postgresql

Here is an improved access path suggestor from postgresql metadata. Based from my Oracle version. In addition to visualized path alternative this generates a query based on the found access path. Could be used in getting familiar with a new data model. Should work also with foreign keys with multiple columns. Cycle detection missing. Just give names of the tables whose path alternatives you are interested in as :foo and :bar binds.

WITH RECURSIVE fks as (
SELECT cc.fk_schematable,
       cc.fk_table,
       string_agg(a.attname, ',' ORDER BY x.n) AS fk_columns,
       cc.conname AS constraint_name,
       cc.uk_schematable,
       cc.uk_table,
       string_agg(b.attname, ',' ORDER BY y.n) AS pk_columns,
       string_agg(cc.uk_table||'.'||b.attname||' = '||cc.fk_table||'.'||a.attname, ' and ' ORDER BY y.n) AS joi
  FROM (
  SELECT c.conrelid::regclass as uk_schematable,
         pk.relname uk_table,
         c.conname,
         c.confrelid::regclass as fk_schematable,
         fk.relname fk_table,
         c.conkey,
         c.confkey,
         c.conrelid,
         c.confrelid
    FROM pg_catalog.pg_constraint c
   INNER JOIN pg_catalog.pg_class pk ON c.conrelid!=0 AND c.conrelid=pk.oid
   INNER JOIN pg_catalog.pg_class fk ON c.confrelid!=0 AND c.confrelid=fk.oid
   WHERE c.contype = 'f'
  ) cc
   /* enumerated key column numbers per foreign key */
 CROSS JOIN LATERAL
      unnest(cc.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each foreign key column */
  JOIN pg_catalog.pg_attribute a
    ON a.attnum = x.attnum
   AND a.attrelid = cc.conrelid
 CROSS JOIN LATERAL
      unnest(cc.confkey) WITH ORDINALITY AS y(attnum, n)
   /* name for each unique key column */
  JOIN pg_catalog.pg_attribute b
    ON b.attnum = y.attnum
   AND b.attrelid = cc.confrelid
 WHERE x.n=y.n 
 GROUP BY cc.fk_schematable, cc.fk_table, cc.conname, cc.uk_schematable, cc.uk_table
), pths as (
SELECT uk_schematable as o1,uk_table as t1,constraint_name,fk_schematable as o2,fk_table as t2,'-<' dir, joi
  FROM fks
 UNION ALL
SELECT fk_schematable as o1,fk_table as t1,constraint_name,uk_schematable as o2,uk_table as t2,'>-' dir, joi
  FROM fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth,fro,joi) as (
SELECT o1,t1,constraint_name,o2,t2,dir,1 lvl,o1||dir||o2 pth, o1||' '||t1||', '||o2||' '||t2, joi
  FROM pths
 WHERE t1 = :foo
 UNION ALL 
SELECT s.o1,s.t1,s.constraint_name,s.o2,s.t2
     , s.dir,lvl+1
     , prio.pth||s.dir||s.o2
     , prio.fro||', '||s.o2||' '||s.t2
     , prio.joi||' and '||s.joi
 FROM rcte prio INNER JOIN pths s ON prio.o2=s.o1 AND prio.constraint_name != s.constraint_name
WHERE prio.lvl < 30
)
SELECT pth,'select * from '||fro||' where '||joi||';' sq
  FROM rcte
 WHERE t2 = :baz
 ORDER BY lvl,pth
;

Testing

create table foo(i int primary key);

create table bar(j int primary key, i int references foo);

create table baz(k int primary key, j int references bar);

pth             sq
foo>-bar>-baz select * from foo foo, bar bar, baz baz where bar.i = foo.i and baz.j = bar.j;

Pascal Matrix - Wishes Come True

Years ago I made a wish in my blog that an analytic function should work in a recursive query. My approach to generate Pascal Matrix seems to work with Oracle 19c database.

with n (u) as (
select 1 from dual
union all
select n.u+1 
  from n
 where n.u < 8
), q as (
select n.u v, m.u w 
  from n, n m 
), r (v,w,s,d,e) as (
select v,w, v,w,sum(w)over(order by w)
  from q
 where v = 1
union all
select q.v,q.w
      ,r.d
      ,r.e
      ,sum(r.e)over(order by r.w)
  from r 
 inner join q 
    on r.w=q.w and r.v+1=q.v
)
select v,w,s
  from r
;

Alter view and dba_dependencies

Adding constraints to a view. Maybe better to consider recreating. Dependencies are populated more complete that way.

create table huba(a int primary key);

create table hubb(b int primary key);

create table link(a int references huba, b int references hubb);


create or replace view fact as (select a,b from link);

alter view fact add constraint f_a_fk foreign key (a) references huba disable;

alter view fact add constraint f_b_fk foreign key (b) references hubb disable;

select referenced_name from dba_dependencies where name = 'FACT';

--HUBB
--LINK

create or replace view fact (
    a
  , b
  , constraint f_a_fk foreign key (a) references huba disable
  , constraint f_b_fk foreign key (b) references hubb disable
)as (select a,b from link);

select referenced_name from dba_dependencies where name = 'FACT';

--HUBA
--HUBB
--LINK

drop table huba cascade constraints purge;

drop table hubb cascade constraints purge;

drop view fact;

Errors With Depth First Hierarchical Query and Table Of Types

Struggling with a hierarchical query and table of types. Getting ORA-00600: internal error code, arguments: [koxsi2sz1] and [rworupo.1] errors with 12.1.0.2 version.

WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH breadth FIRST BY operand SET order1
SELECT * FROM Factorial
;

Breadth first and everything is fine. But changing to depth first the problems occur.

WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH depth FIRST BY operand SET order1
SELECT * FROM Factorial
;

With 18.4 version the error message is more tolerable ORA-00932: inconsistent datatypes: expected UDT got SYS.ODCINUMBERLIST. But no success with the results.

Getting around the problem is to create a NumberListWrapper wrapper type for the array type. The workaround seems to work at least with 12.2 and 18.4 versions.

CREATE OR REPLACE TYPE NumberListWrapper AS OBJECT (
    numbertable sys.odciNumberlist,
    MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC
);
/

CREATE OR REPLACE TYPE BODY NumberListWrapper AS
    MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC IS
    BEGIN
        RETURN 1;
    END;
END;
/


WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, NumberListWrapper(sys.odcinumberlist(1,2)) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH DEPTH FIRST BY operand SET order1
SELECT * FROM Factorial
;

Select For Update Locks Joined Rows

Select for update locks the joined rows even thou columns from the table are not in the select list. The rows used in a predicate sub query are not locked. Here is an example. First preparing tables:

drop table bar;

drop table foo;

create table foo as select column_value i from table(sys.odcinumberlist(1,2,3,4));

alter table foo add constraint foo_pk primary key (i);

create table bar as select i,i j from foo;

alter table bar add constraint bar_pk primary key(j);

alter table bar add constraint bar_foo_fk foreign key(i) references foo;

Selecting for update. The foo table used in exists predicate is not locked.

select j from bar where j = 1 and exists (select 0 from foo where foo.i=bar.i) for update;

--only the row in BAR table is locked

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) BAR

rollback;

When joining is used, also the joined row in FOO table is locked

select bar.j from bar inner join foo on foo.i=bar.i where j = 1 for update;

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) FOO
--Row-X (SX) BAR

rollback;

Access Path Suggestor

Reverse engineering an existing schema or creating a new sql query. Join access paths are often following foreign keys. Here is a query that searches foreign key dependency paths between two tables.

with fks as(
select pk.owner o1, pk.table_name t1, fk.constraint_name, fk.owner o2, fk.table_name t2
  from all_constraints fk, all_constraints pk
 where fk.r_owner= pk.owner
   and fk.r_constraint_name = pk.constraint_name
   and fk.constraint_type = 'R' 
), pths as (
 select o1,t1,constraint_name,o2,t2,'-<' dir
   from fks
 union all
 select o2,t2,constraint_name,o1,t1,'>-' dir
   from fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth) as (
 select o1,t1,constraint_name,o2,t2,dir,1,o1||'.'||t1||dir||constraint_name||'-'||o2||'.'||t2
   from pths
 where o1 = :owner1
   and t1 = :table1
 union all 
 select s.o1,s.t1,s.constraint_name,s.o2,s.t2
      , s.dir,lvl+1
      , prio.pth||s.dir||case when s.constraint_name not like 'SYS\_%' escape '\' then s.constraint_name||'-' end||s.o2||'.'||s.t2
  from rcte prio inner join pths s on prio.o2=s.o1 and prio.t2=s.t1 and prio.constraint_name != s.constraint_name
 where prio.lvl < 7
) cycle o1,t1 set cycle to 1 default 0
 select lvl,cycle,pth
   from rcte
 where o2 = :owner2
   and t2 = :table2
  order by lvl,pth
;