User Defined Type Definition

I get to do this all the time and I thought it would be a good addition to this blog. My case scenario here is that every time there is a db release going out that modifies a user defined type in my database I get nervous because I need to have a quick rollback plan if the release is called for a rollback. There may be a better way to achieve this feel free to share. It can come in handy if you really need to know what CREATE TYPE syntax is being used.

We do know that just using \dT+ might give some information but it does not give the create statement / complete definition of the type:

\dT+ accounts.billing_info
List of data types
Schema | Name | Internal name | Size | Elements | Description
-----------+------------------------------+--------------------+-------+----------+-------------
accounts | accounts.billing_info | billing_info | tuple | |
(1 row)

To get around this I use the following query that gives me the complete definition of the user defined type. Replace schema and table name in the below to match your requirement.

SELECT 'CREATE TYPE accounts.billing_info as ('||array_to_string(ARRAY(select a.attname||' '|| pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (select oid from pg_class where relname='billing_info' and relnamespace=(select oid from pg_namespace where nspname='accounts'))
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum),',')||');';

?column?

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TYPE accounts.billing_info as (billme character varying(20),serial integer,bill_name character varying(100));
(1 row)

And here I have my complete type definition and can use it as required. Hope this helps!!!

Bloats

Bloats can cause some serious performance impact on queries and you will see server load rise from what it was at an earlier point in time. The reason for bloats in a table/index could be due to various reasons. Updates rollbacks failed transactions etc. What is important here to identify these bloats and remove them before things get too bad.

I have simplified the process of identifying these bloats using the below script. The result set what you will see is:

relation | reltuples | relpages | otta | bloat | wastedpages | wastedbytes | pwastedbytes | relbytes | prelbytes
---------------------------------+------------+------------+------+-------+------------------+-----------------+-------------------+------------+-------------------------------------
account.activation_return_policy | 973111 | 164186 | 5240 | 31.3 | 158946 | 1302085632 | 1242 MB | 1345011712 | 1283 MB
billing.search_bill_info                   | 688832 | 6754 | 3372 | 2.0 | 3382 | 27705344 | 26 MB | 55328768 | 53 MB
(2 rows)

Here you see that there are two tables that have 31.3% and 2.0% bloats respectively. It is a good idea to run pgreorg or cluster command depending on the database availability requirement to remove these bloats.

NOTE: CLUSTER command will lock the table for the time it removes the bloat and is slower than pgreorg. Make a practical choice here by looking at the options you have. If you need more information on pgreorg , look though my post about pgreorg. 

The sql that I have used to get this information is given below. It is a good idea to put the sql in a crontab and probably send out the result set via email so that you are aware of the bloated tables and can work on reindexing /clustering or using pgreorg at a later time.

SELECT
schemaname||'.'||tablename as relation, reltuples::bigint, relpages::bigint, otta,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS bloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
pg_size_pretty((CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END)::bigint) AS pwastedbytes,
bs*relpages::bigint as relbytes,
pg_size_pretty((bs*relpages::bigint)::bigint) as prelbytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename and cc.relkind = 'r'
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
) AS sml
WHERE sml.relpages - otta > 128
AND ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) > 1.2
AND CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END > 1024 * 100
ORDER BY wastedbytes DESC;

Long running queries

Ever wondered if you did not get a call in the middle of the night just for some stupid stuck query that was not supposed to run that long and blocking all the other queries making the database load rise up till its not even manageable?

I did and finally got something to address it. Below is a script that logs all long running queries and kills them with pg_cancel_backend. Finally sends out an email alert, you can modify it according to your production requirement and increase the time of the query that should be killed. Just put it in a cronjob “AFTER TESTING IT PROPERLY”. I am not responsible for any data loss here.

Make sure you have postfix installed and configured for your mail to work.

# The script must run as postgres, since only postgres user has control over every database.
if [ `whoami` != "postgres" ]; then
exit 0;
fi

# Selecting relevant columns, getting non-idle queries only where the query runs since at least 6 minutes.
psql -c "select procpid, 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 alfanumeric 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