PostgresSql Inherit table Generator

Working in many projects and learning new technologies, now everything is about postgresSql and non-sql databases...fun.fun.fun.

So after most debate about creating partitions we decided to used inherit tables. 

So here is a function that will do that :-)

Cheers...

-- Function: SchemaName.batch_gen_complete(integer, character varying)

-- DROP FUNCTION SchemaName.batch_gen_complete(integer, character varying);

CREATE OR REPLACE FUNCTION generate_inherid_child(

    _batch_stage integer,

    _modified_by character varying)

  RETURNS void AS

$BODY$

DECLARE

               _count int;

               _gen_count int;

               _active_count int;

               _is_clean_unique bool;

               _is_clean_duplicate bool;

               _tablename text;

               _exist bool;

BEGIN

    --- Get count of records from stage

              

               SELECT count INTO _count FROM batch_stage WHERE promo_batch_stage = _promo_batch_stage;

               SELECT generated_count, is_clean_unique, is_clean_duplicate INTO _gen_count, _is_clean_unique, _is_clean_duplicate

               FROM batch_generator

               WHERE promo_batch_stage_id = _promo_batch_stage_id;

               IF _gen_count < _count Then

                              RAISE EXCEPTION 'Not enough promo codes available to be able to complete batch: %', _promo_batch_stage;

                              return;

               End If;

               IF _is_clean_unique = false Then

                              RAISE EXCEPTION 'Batch is not marked as clean for unique values, so unable to complete: %', _promo_batch_stage;

                              return;

               End If;

               IF _is_clean_duplicate = false Then

                              RAISE EXCEPTION 'Batch is not marked as clean for duplicate values against active promotions, so unable to complete: %', _promo_batch_stage;

                              return;

               End If;

               SELECT count(*) INTO _active_count FROM generator_parent WHERE promo_batch_stage_id = _promo_batch_stage_id;

               IF _active_count >= _count Then

                              --RAISE EXCEPTION 'Batch already has entries in promotion table, so unable to complete: %', _promo_batch_stage;

                              --return;

                              RAISE INFO '% - %', 'Batch already has entries in promotion table, marking batch complete.', now();

               Else

                              If _count > 9999 Then

                                            

                                             -- Put large batches into an inherited table

                                             _tablename := '_batch_' || _promo_batch_stage;

                                             EXECUTE 'SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname=''SchemaName'' and tablename=''' || quote_ident(_tablename) || ''')' INTO _exist;

                                             IF _exist Then

                                                            -- Delete data from a previous run

                                                            EXECUTE 'TRUNCATE SchemaName.' || quote_ident(_tablename);

                                            

                                             Else

                                            

                                                            -- Create inherited table for large batches

                                                            EXECUTE 'CREATE TABLE SchemaName.' || quote_ident(_tablename) || ' (CHECK ( batch_stage = ' || _batch_stage || ' )) INHERITS (generator_parent)';

                                             End If;

                                            

                                             -- Copy data from temp table to inherited table

                                             EXECUTE format('Insert Into SchemaName.%s (promo_batch_stage, promo_number, is_active, created_by, created_date)       

                                                            Select promo_batch_stage, promo_number, 1::boolean, %L, now()

                                                            From generator_parent_temp

                                                            Where promo_batch_stage = %L

                                                            LIMIT %L'

                                                            , _tablename, _modified_by, _promo_batch_stage, _count);

                                             IF _exist = false Then

                                                            -- Assign owner of of inherited table

                                                            EXECUTE format('ALTER TABLE SchemaName.%s OWNER TO db_owner', _tablename);

                                                            -- Add primary key

                                                            EXECUTE format('ALTER TABLE SchemaName.%s ADD PRIMARY KEY(child_id)', _tablename, _tablename);

                                                           

                                                            --Add FK promo_batch_stage

                                                            EXECUTE format('ALTER TABLE SchemaName.%s ADD CONSTRAINT FK_%s FOREIGN KEY(batch_stage) REFERENCES SchemaName.promo_batch (batch_stage)  MATCH FULL',_tablename, _tablename);

                                                            -- Need to define indexes for inherited tables

                                                            EXECUTE format('CREATE INDEX ix_%s_promo_batch_stage ON SchemaName.%s USING btree ("batch_stage")', _tablename, _tablename);

                                                            //EXECUTE format('CREATE UNIQUE INDEX ix_%s_promo_number ON SchemaName.%s ("")', _tablename, _tablename);

                                             End If;

                                            

                              Else

                                             -- Put smaller batches into general table

                                             Insert Into generator_parent_general (promo_batch_stage, promo_number, is_active, created_by, created_date)

                                             Select batch_stage_id, batch_number, 1::boolean, _modified_by, now()

                                             From generator_parent_temp

                                             Where batch_stage_id = _batch_stage_id

                                             LIMIT _count;

                                            

               End If;

               End If;

               -- Remove codes from staging table

               Delete From generator_parent_temp

               Where batch_stage = _batch_stage;

                             

               -- Mark any incomplete log entries as complete

              

END      

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION SchemaName.batch_gen_complete(integer, character varying)

  OWNER TO db_owner;

GRANT EXECUTE ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) TO public;

GRANT EXECUTE ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) TO db_executor;

REVOKE ALL ON FUNCTION SchemaName.batch_gen_complete(integer, character varying) FROM db_owner;

PostgresSql Index Scripts

Today everything is about maintenace of  the postgres database so, searching and building scripts. Don't try to re-invent the wheel...
cheers


/****http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html***************/

/**************unused index******************/
WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans
),
indexes as (
    SELECT idx_stat.relid, idx_stat.indexrelid,
        idx_stat.schemaname, idx_stat.relname as tablename,
        idx_stat.indexrelname as indexname,
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes,
        indexdef ~* 'USING btree' AS idx_is_btree
    FROM pg_stat_user_indexes as idx_stat
        JOIN pg_index
            USING (indexrelid)
        JOIN pg_indexes as indexes
            ON idx_stat.schemaname = indexes.schemaname
                AND idx_stat.relname = indexes.tablename
                AND idx_stat.indexrelname = indexes.indexname
    WHERE pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT schemaname, tablename, indexname,
    idx_scan, all_scans,
    round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
        ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
    writes,
    round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
        as scans_per_write,
    pg_size_pretty(index_bytes) as index_size,
    pg_size_pretty(table_size) as table_size,
    idx_is_btree, index_bytes
    FROM indexes
    JOIN table_scans
    USING (relid)
),
index_groups AS (
SELECT 'Never Used Indexes' as reason, *, 1 as grp
FROM index_ratios
WHERE
    idx_scan = 0
    and idx_is_btree
UNION ALL
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
FROM index_ratios
WHERE
    scans_per_write <= 1
    and index_scan_pct < 10
    and idx_scan > 0
    and writes > 100
    and idx_is_btree
UNION ALL
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
FROM index_ratios
WHERE
    index_scan_pct < 5
    and scans_per_write > 1
    and idx_scan > 0
    and idx_is_btree
    and index_bytes > 100000000
UNION ALL
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp
FROM index_ratios, all_writes
WHERE
    ( case when total_writes = 0 then 0 else writes::NUMERIC / ( total_writes + 1 )end ) > 0.02
    AND NOT idx_is_btree
    AND index_bytes > 100000000
ORDER BY grp, index_bytes DESC )
SELECT reason, schemaname, tablename, indexname,
    index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups;
/******************Table scan summary***********************************************/
SELECT
x1.table_in_trouble,
pg_relation_size(x1.table_in_trouble) AS sz_n_byts,
x1.seq_scan, x1.idx_scan,
CASE
WHEN pg_relation_size(x1.table_in_trouble) > 500000000
THEN 'Exceeds 500 megs, too large to count in a view. For a count, count individually'::text
ELSE count(x1.table_in_trouble)::text
END AS tbl_rec_count,
x1.priority
FROM
(
SELECT
(schemaname::text || '.'::text) || relname::text AS table_in_trouble,
seq_scan,
idx_scan,
CASE
WHEN (seq_scan - idx_scan) < 500 THEN 'Minor Problem'::text
WHEN (seq_scan - idx_scan) >= 500 AND (seq_scan - idx_scan) < 2500 THEN 'Major Problem'::text
WHEN (seq_scan - idx_scan) >= 2500 THEN 'Extreme Problem'::text
ELSE NULL::text
END AS priority
FROM
pg_stat_all_tables
WHERE
seq_scan > idx_scan
AND schemaname != 'pg_catalog'::name
AND seq_scan > 100) x1
GROUP BY
x1.table_in_trouble,
x1.seq_scan,
x1.idx_scan,
x1.priority
ORDER BY
x1.priority DESC,
x1.seq_scan
;
/********************Check Query Plan*********************/
explain ( analyze, BUFFERS)
select * from ?

/****Postgres index Summary Row Size************/
SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    count(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(case WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(case WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           max(CAST(indisunique AS integer)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts
           AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE
     pg_namespace.nspname='SchemaNameHere'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;













/****Postgres index Stats Summary******/
SELECT
    distinct
    t.tablename,
   indexname,
   c.reltuples AS num_rows,
   pg_size_pretty(pg_relation_size(t.schemaname::TEXT||'.'||quote_ident((t.tablename))::text)) AS table_size,
   pg_size_pretty(pg_relation_size(t.schemaname||'.'||quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
 FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='SchemaNameHere'
ORDER BY 1,2;

The end of the year is approaching so, before going any further, Happy 2016 to everyone. 
Have fun, don't do anything I won't do :-).

Now that is out of the way, I have been spending a lot time thinking on how fast technology is changing, now everything is cloud this, cloud that, but, the word cloud is just an overrated media frenzy for nothing more than San's or network computing.
Finally, I'm getting to good using PostgresSQL, but as things moving you get what you paid for. But, nothing beat free, but as stated again, you get what you paid for.
Cheers.