Ensure multi-shard temp. tables dropped on commit

We were relying on the default behavior, which would leave many orphan
tables during long-lived sessions. By specifying `ON COMMIT DROP` for
the temporary tables, we can ensure they are dropped as soon as the
query completes and no later.