Today we discovered that we had a backend whose client had gone away, the automatic query watching process had send both pg_cancel and pg_terminate_backend but nevertheless the process was sitting there consuming resources and had been for over 1 day...
gdb revealed that we were sitting in pg_regexec (we forced it to return 16 aka invalid regex to return our system into a good state).
Here is the regular expression and the text to run on:
WARNING DO NOT DO THIS ON A PRODUCTION BOX
select regexp_replace('VODI GR,VOD LN,VOD LN,VODN MM,VODPF US,VOD US,VZC LN', '([^,]+)(,*\1)+', '\1');
This was in postgres 9.2
Cheers,
Bene