Re: Death by regexp_replace

Benedikt Grundmann <bgrundmann(at)janestreet(dot)com> wrote:
> 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

9.2 what? This responds to cancel just fine for me. See 9.2.14
release notes.

(FWIW, I think you probably wanted ,+ not ,* in the regex, else there's
practically no constraint there, leading to having to consider O(N^2)
or more possibilities.)

regards, tom lane