We have some processes that have proved themselves to cause
ridiculous amounts of table/index bloat above and beyond what
autovac can manage.
The good news is that this is very predictable in the application
and cause by background processes that can afford to take a little
extra time to clean up the mess they make.
The problem is that our security model is such that database objects
are owned by a administrative user, and permissions are granted to
specific application users. By design, the job causing the bloat
connects as a non-superuser application user. As a result, the
process can not vacuum the table because of ownership.
My first attempt to work around this was to put VACUUM inside a
SECURITY DEFINER function defined by superuser. Unfortunately, as
most of you know, you can't run VACUUM inside a function.
It doesn't look like there's a permission that I can GRANT on the
table to allow the application user to VACUUM it.
Anyone have any suggestions for this, short of creating a superuser
account for this particular process to connect as?
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/