[SH] Support for session-scoped TEMP tables

[jseldess]

Background: https://airtable.com/tblD3oZPLJgGhCmch/viw1DKmbKhg2MIECH/recokw8S2pkhwhPP2

Description: CockroachDB now supports passing the modifier TEMP or TEMPORARY to CREATE TABLE. This indicates that the table should be dropped when the session ends. The ON COMMIT clause of CREATE TEMP TABLE (txn-scoped temp tables) remains unsupported.

Like other databases, using the unqualified table name for the remainder of the session would reference the temporary table -- an existing, non-temporary table of the same name would need to be accessed using a qualified name.

It is too hard for developers to use CockroachDB with Postgres drivers and ORMs. There are three main reasons for this:

• Bugs in our implementation
• Missing PG functionality
• Different underlying architecture

Conceptually, this is hard for developers to understand because we use the same wire protocol as Postgres. Developers will interact with CRDB and Postgres in the same manner but have different results based on bugs in our implementation, missing functionality, or the underlying differences in the architecture.

Temporary tables are commonly used by third-party tools to implement their functionality or test its capabilities within their test suites. Examples include the Java ORM Hibernate.

Team: Andrew Woods, Jordan Lewis, Arul Ajmani

Github Tracking Issue: cockroachdb/cockroach#5807 #5959

[jseldess]

From release notes:

• The new global default cluster setting sql.defaults.temporary_tables.enabled can be used to enable temporary tables. [#43816][#43816]

• Added support for temporary view creation, if temporary tables are enabled. Temporary views disappear at the end of a connection. Views that depend on temporary tables are automatically temporary. [#44729][#44729]

[jseldess]

From release notes:

• If temporary table creation is enabled, users now have the ability to create temporary sequences as well. [#44806][#44806]