Sharing a script that logs all long running queries, kills them :) and sends out an email alert to my inbox. I generally modify it according to the production requirement. In my environment there are a couple of production servers where I would setup this script to only alert and Not kill any query.

Please double test before implementing in realtime production environment.Follow the general thumb rule to implement all changes/scripts from lower to higher env. :) this script comes with disclaimer that person using has complete ownership for it’s results.

Ensure postfix installed and configured for emails to work. The script works for postgre user since postgre has privs over all databases in the server

############################

if [ `whoami` != "postgres" ]; then

exit 0;

fi

# selecting non-idle queries which are running since at least 6 minutes.

psql -c "select pid, client_addr, query_start, current_query from pg_stat_activity

where current_query != '<IDLE>' and current_query != 'COPY' and current_query != 'VACUUM' and query_start + '6 min'::interval < now()

and substring(current_query, 1, 11) != 'autovacuum:'

order by query_start desc" > $LOGFILE

NUMBER_OF_STUCK_QUERIES=`cat $LOGFILE | grep "([0-9]* row[s]*)" | sed 's/(//' | awk '{ print $1}'`

if [ $NUMBER_OF_STUCK_QUERIES != 0 ]; then

# Getting the first column from the output discarding alphfanumeric values (table elements in psql's output).

STUCK_PIDS=`cat $LOGFILE | sed "s/([0-9]* row[s]*)//" | awk '{ print $1 }' | sed "s/[^0-9]//g"`

for PID in $STUCK_PIDS; do

echo -n "Cancelling PID $PID ... " >> $LOGFILE

# "t" means the query is successfully cancelled.

SUCCESS=`psql -c "SELECT pg_cancel_backend($PID);" | grep " t"`

if [ $SUCCESS ]; then

SUCCESS="OK.";

else

SUCCESS="Failed.";

fi

echo $SUCCESS >> $LOGFILE

done

cat $LOGFILE | mail -s "Stuck PLpgSQL processes detected and killed that were running over 6 minutes." youremail@whatever.com;

fi

rm $LOGFILE

#######################################

MERGE Feature in PostgreSQL

From PostgreSQL 9.1, onwards user can implement the feature of Merge using the writable CTE (Common Table Expressions)

WITH  provides a way to write auxiliary statements for use in a larger query. This can be thought of as defining temporary tables that exist just for one query.Each auxiliary statement in a WITH clause can be a SELECT,INSERT,UPDATE or DELETE and the clause WITH witself is attached to a primary statement that can also cause a SELECT,INSERT,UPDATE or DELETE

Created 2 tables and insert some data into it. Now merge the 2 tables 

[root@ip--- ~]# psql tejidatabase

psql (9.2.4.8)

Type "help" for help.

tejidatabase=# select * from testmerge;

 pid | age |  name  

-----+-----+--------

   2 |  48 | RAM

   4 |  61 | SHYAM

   6 |  85 | SONIA

   8 |  44 | RAHUL

  10 |  34 | MAMTA

  12 |  45 | SURJIT

  14 |  21 | ISHIKA

  16 |  19 | IPSA

(8 rows)

tejidatabase=# select * from merge2;

 pid | age |  name  

-----+-----+--------

  18 |  56 | YASUDA

   8 |   0 | RAHUL

  14 |   5 | ISHIKA

(3 rows)

tejidatabase=# WITH upsert as (update merge2 m set age=d.age+100 ,name=d.name from testmerge d where m.pid=d.pid RETURNING m.*) insert into merge2 select a.pid,a.age,'NEW' from testmerge a where a.pid not in ( select b.pid from upsert b);

INSERT 0 6

tejidatabase=# select * from merge2;

 pid | age |  name  

-----+-----+--------

  18 |  56 | YASUDA

   8 | 144 | RAHUL

  14 | 121 | ISHIKA

   2 |  48 | NEW

   4 |  61 | NEW

   6 |  85 | NEW

  10 |  34 | NEW

  12 |  45 | NEW

  16 |  19 | NEW

(9 rows)

tejidatabase=# 

As you can see all the rows of test merge are now added in merge2 with name=’NEW’ and the matching pid of test merge and merge2, the ages have been added by 100.

The magic of Writable CTE which can make UPSERT in PostgreSQL

:-)