Death by regexp_replace

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