Temporary tables used by multi-shard SELECTs not dropped

[jberkus]

I was a bit startled to see this on the master:

ERROR:  could not extend file "base/16385/t3_17723": No space left on device
HINT:  Check free disk space.

When I checked, there were literally hundreds of temp tables on the master, using up 100% of disk space. I was doing a \watch 10 on an aggregate query across shards, and apparently the temp tables pg_shard needs to resolve this query do not get dropped automatically:

select count(*) from members;
# count
# ---------
#1929821
# (1 row)

\dt
#                     List of relations
# Schema   |            Name            | Type  |  Owner
# -----------+----------------------------+-------+---------
# pg_temp_2 | pg_shard_temp_table_3648_0 | table | psocial
# public    | members                    | table | psocial
# public    | members_local              | table | psocial
# (3 rows)

Temp tables used by pg_shard should be ON COMMIT DROP at the least; ideally they should be dropped as soon as the query is over. I'll see about documenting the master's use of temp tables somewhat.

[jasonmp85]

Gah. I had seen something like this at one point but figured it was a mistake I had made during some previous queries. That looks problematic. I'll bring it up soon and we'll get something sorted out.

For now I'm adding a ton of unit tests and the good part is this looks like something for which we can easily add a regression case.

[jasonmp85]

I changed the title to reflect that these are used by any query that hits multiple shards (just verified these show up even for SELECT *.

[jasonmp85]

Chatted with @sumedhpathak about this and we've removed the documentation tag from this issue. With the ON COMMIT behavior the temporary tables are mostly an implementation detail. We'll add a FAQ if this becomes a common problem.

[jberkus]

Seems to be working as of current development branch.