Real-time console based monitoring of PostgreSQL databases (pg_view)

In many cases, it is important to be able to keep your hand on the pulse of your database in real-time. For example when you are running a big migration task that can introduce some unexpected locks, or when you are trying to understand how the current long running query is influencing your IO subsystem.

For a long time I was using a very simple bash alias that was injected from the .bashrc script and that included the calls to system utilities like watch, iostat, uptime, df, some additional statistics from the /proc/meminfo and psql that was extracting information about currently running queries and if that queries are waiting for a lock. But this approach had several disadvantages. In many cases I was interested in the disk read/write information for query processes or PostgreSQL system processes, like WAL and archive writers. Also I wanted to have a really easy way to notice the queries that are waiting for locks and probably highlight them by color.
Several weeks ago we finally open-sourced our new tool, that makes our lives much easier. That tool combines all the feature requests that I was dreaming of for a long time. Here it is: pg_view.

I already have some more feature requests actually and hope that Alexey will find some time to add them to the tool in nearest future. So if somebody wants to contribute or give some more ideas, please comment and open feature requests on the github page :)

re-assigning values to the parameters in plpythonu

As discussed in Postgres BUG #5232 assigning values to the parameters fails with the following error:

Detail: <type 'exceptions.UnboundLocalError'>: local variable 'src' 
referenced before assignment

If assignment to the parameter is still needed one can solve the issue by adding global definition for such a parameter.

CREATE OR REPLACE FUNCTION pyreplace(src text, s text) 
  RETURNS text AS 
$BODY$ 
global src
src=src.replace(s,'') 
return src 
$BODY$ 
  LANGUAGE 'plpythonu' VOLATILE 
  COST 100; 

64 bit python installation issues on 64 bit Windows 7

After installing 64 bit Windows 7 at work, I got a problem of installing many python utilities that search for installed python locations is windows registry. distutils (setuptools-0.6c11.win32-py2.6.exe) still could not find 64 bit python installation, as well as develer's Unofficial MinGW GCC binaries for Windows (I still do not have a solution for that one :( ).

Actually, what helped me with disttools, was a http://www.mail-archive.com/distutils-sig@python.org/msg10512.html group thread. The currently available 64 bit python installation is registering python in a new location in windows registry. So one have to use a modified script for registering python the old way:

#
# script to register Python 2.0 or later for use with win32all
# and other extensions that require Python registry settings
#
# written by Joakim Loew for Secret Labs AB / PythonWare
#
# source:
# http://www.pythonware.com/products/works/articles/regpy20.htm
#
# modified by Valentine Gogichashvili as described in http://www.mail-archive.com/distutils-sig@python.org/msg10512.html

import sys

from _winreg import *

# tweak as necessary
version = sys.version[:3]
installpath = sys.prefix

regpath = "SOFTWARE\\Python\\Pythoncore\\%s\\" % (version)
installkey = "InstallPath"
pythonkey = "PythonPath"
pythonpath = "%s;%s\\Lib\\;%s\\DLLs\\" % (
    installpath, installpath, installpath
)

def RegisterPy():
    try:
        reg = OpenKey(HKEY_CURRENT_USER, regpath)
    except EnvironmentError as e:
        try:
            reg = CreateKey(HKEY_CURRENT_USER, regpath)
            SetValue(reg, installkey, REG_SZ, installpath)
            SetValue(reg, pythonkey, REG_SZ, pythonpath)
            CloseKey(reg)
        except:
            print "*** Unable to register!"
            return
        print "--- Python", version, "is now registered!"
        return
    if (QueryValue(reg, installkey) == installpath and
        QueryValue(reg, pythonkey) == pythonpath):
        CloseKey(reg)
        print "=== Python", version, "is already registered!"
        return
    CloseKey(reg)
    print "*** Unable to register!"
    print "*** You probably have another Python installation!"

if __name__ == "__main__":
    RegisterPy()

or just inject the following REG file into your registry if you are using python 2.6 installed in C:\Python26 directory

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Python]

[HKEY_CURRENT_USER\Software\Python\Pythoncore]

[HKEY_CURRENT_USER\Software\Python\Pythoncore\2.6]

[HKEY_CURRENT_USER\Software\Python\Pythoncore\2.6\InstallPath]
@="C:\\Python26"

[HKEY_CURRENT_USER\Software\Python\Pythoncore\2.6\PythonPath]
@="C:\\Python26;C:\\Python26\\Lib\\;C:\\Python26\\DLLs\\"

PyDev is finally fully open sourced!

PyDev is finally fully open sourced! These are really good news!