Tuesday, December 21, 2010

SQL Query to show current activities of the database server at Postgresql

0 comments
I executed the following query at Postgresql 9.0.1:

SELECT pg_stat_activity.datname, pg_stat_activity.procpid,
pg_stat_activity.usename, 
pg_stat_activity.current_query,
pg_stat_activity.waiting,
pg_stat_activity.query_start,pg_stat_activity.client_addr
FROM pg_stat_activity
WHERE ((pg_stat_activity.procpid <> pg_backend_pid())
AND (pg_stat_activity.current_query <> ''))
ORDER BY pg_stat_activity.query_start;
Continue reading →
Thursday, December 9, 2010

Postgresql in Mission-Critical Financial System

1 comments
The following presentation is based on Caixa, 3rd largest Bank in Brazil. I found it very interesting in planning Postgresql for 24x7 system and hope that the readers will also enjoy it.

Continue reading →

Query to find unused indexes in Postgresql

2 comments
Following query finds out the unused indexes for top 20 most used tables in Postgresql:

SELECT
i.relname,i.indexrelname,i.idx_scan,i.idx_tup_read,i.idx_tup_fetch, c.reltuples, c.relpages
FROM
pg_stat_user_indexes as i
INNER JOIN pg_class as c
     ON i.indexrelname = c.relname
WHERE
i.relname IN
    (
    --top 20 most used tables
    SELECT c.relname as name
    FROM pg_stat_user_tables as t
    INNER JOIN pg_class as c
                ON t.relid = c.oid
    WHERE c.relkind ='r'
    ORDER BY
    COALESCE(t.idx_tup_fetch,0)+COALESCE(t.seq_tup_read,0) DESC
    LIMIT 20
    )
ORDER BY i.idx_scan ASC
Continue reading →
Thursday, December 2, 2010

How to get number of rows return by a query using MOVE in Postgresql

0 comments
Create and populate data:
CREATE TABLE emp
(
empno int,
empname varchar(100),
city varchar(100)
);

ALTER TABLE emp
ADD CONSTRAINT empno_pk PRIMARY KEY (empno);

INSERT INTO emp
select q.c as empno, 'testemp_'||q.c as empname, 'testcity_'||q.c as city
FROM
(SELECT generate_series(1,100) as c) AS q
;
Create function:
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR
    SELECT  *
    FROM emp
    ;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;
Get number of rows return:
DECLARE
  i INT;
BEGIN;
SELECT reffunc('funccursor');
MOVE ALL IN funccursor;
COMMIT;
Further Study:
http://www.postgresql.org/docs/current/static/sql-move.html
Continue reading →

Labels