1.3.1

[pgdbmsstats/pg_dbms_stats.git] / pg_dbms_stats--1.0-9.1.sql

diff --git a/pg_dbms_stats--1.0-9.1.sql b/pg_dbms_stats--1.0-9.1.sql
index 1dc9fbb..145df95 100644 (file)
--- a/pg_dbms_stats--1.0-9.1.sql
+++ b/pg_dbms_stats--1.0-9.1.sql
@@ -188,6 +188,13 @@ CREATE VIEW dbms_stats.column_stats_effective AS
           FULL JOIN dbms_stats._column_stats_locked v
          USING (starelid, staattnum, stainherit)
          WHERE NOT dbms_stats.is_system_catalog(starelid)
+                  AND EXISTS (
+                       SELECT NULL
+                         FROM pg_attribute a
+                        WHERE a.attrelid = starelid
+                          AND a.attnum = staattnum
+                          AND a.attisdropped  = false
+                       )
         ) m
      WHERE starelid IS NOT NULL;
 
@@ -1407,5 +1414,55 @@ END;
 $$
 LANGUAGE plpgsql;
 
+--
+-- CLEAN_STATS: Clean orphan dummy statistic
+--
+
+CREATE OR REPLACE FUNCTION dbms_stats.clean_up_stats() RETURNS SETOF text AS
+$$
+DECLARE
+       clean_relid             Oid;
+       clean_attnum    int2;
+       clean_inherit   bool;
+       clean_rel_col   text;
+BEGIN
+    LOCK dbms_stats._relation_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
+    LOCK dbms_stats._column_stats_locked IN SHARE UPDATE EXCLUSIVE MODE;
+
+       -- We don't have to check that table-level dummy statistic of the table
+       -- exists here, because the foreign key constraints defined on column-level
+       -- dummy static table eusures that.
+       FOR clean_rel_col, clean_relid, clean_attnum, clean_inherit IN
+               SELECT r.relname || ', ' || v.staattnum::text,
+                          v.starelid, v.staattnum, v.stainherit
+                 FROM dbms_stats._column_stats_locked v
+                 JOIN dbms_stats._relation_stats_locked r ON (v.starelid = r.relid)
+                WHERE NOT EXISTS (
+                       SELECT NULL
+                         FROM pg_attribute a
+                        WHERE a.attrelid = v.starelid
+                          AND a.attnum = v.staattnum
+                          AND a.attisdropped  = false
+               )
+       LOOP
+               DELETE FROM dbms_stats._column_stats_locked
+                WHERE starelid = clean_relid
+                  AND staattnum = clean_attnum
+                  AND stainherit = clean_inherit;
+               RETURN NEXT clean_rel_col;
+       END LOOP;
+
+       RETURN QUERY
+               DELETE FROM dbms_stats._relation_stats_locked r
+                WHERE NOT EXISTS (
+                       SELECT NULL
+                         FROM pg_class c
+                        WHERE c.oid = r.relid)
+                RETURNING relname || ',';
+       RETURN;
+END
+$$
+LANGUAGE plpgsql;
+
 GRANT USAGE ON schema dbms_stats TO PUBLIC;
 --