Conditionally drop temporary table in Redshift

amazon-redshift, amazon-web-services

Solution

The test I ran showed that I could see other users' temp tables in stv_tbl_perm using a non-super user id. The cluster version I tested in is 1.0.797. Note that no users can see other users' temp tables in pg_class.

Problem

We are using http://aws.amazon.com/redshift/ and I am creating/dropping temporary tables in reports. Occasionally we encounter cases where someone has created a temporary table and failed to drop it. In other databases, for instance PostgreSQL which Redshift is based on, I could simply: `DROP TEMP TABLE IF EXISTS tblfoo;` But that is a syntax error in Redshift. I can check for the existence of temporary tables myself using http://docs.aws.amazon.com/redshift/latest/dg/r_STV_TBL_PERM.html but that only works if I am a superuser and I am not running as a superuser. I could also go and swallow exceptions, but with my reporting framework I'd prefer not to go there. So how can I, as a regular user and without generating database errors, conditionally drop a temporary table if it exists?

Original source