We have a new table partition maintenance system that went into production today, and it is failing.
Each partitioned table has a web external table associated with it, and the shell script that is run performs the partition creation, and also runs a "watchdog" process that monitors to see if the partition creation is causing a lock cascade.
The problem seems to be that Greenplum is repeatedly calling the shell script, but if we change the shell script then Greenplum still runs the same commands as if we had not changed it. It's as if it cached the contents of the shell script and will not update its cache. We have tried recreating the web external table, and we have tried restarting the database.
I will update this ticket with some code tomorrow, but we're pretty desperate for ideas! We have a few days in hand, but soon we will need to back out the new mechanism if we can't fix it, and we can't replicate the problem in any other environment.
We are working around the problem by removing all the web external tables and replacing them with plpythonu calls to the shell scripts and parsing stdout ourselves.
EDIT The problem may have been nothing to do with Greenplum, a script was either deployed incorrectly or was messed up whilst we were diagnosing the problem. Since the problem only occurs in production, and requires a database reboot when it occurs, we are staying well away from it and so cannot run any diagnostics.