Samofatov, Nickolay wrote:

>I would simply add ATTACHMENT_ID system column (probably with
>appropriate index) to tables declared as temporary and taught optimizer
>to filter out rows which should not be visible by current attachment. At
>startup engine may perform simple clean up process (it doesn't require
>exclusive access to database and thus may be performed in background)
>which can delete rows belonging to old attachments.
>
>It looks like such implementation may be done in a few days and doesn't
>require big modifications of the engine.
>
>
>

I think there is a general misunderstanding of the feature. It is both
subtle and non-intuitive.

Temporary tables are declared once but can "used" many times. The
metadata is persistent. The data is not. The physical storage
structures are created on first reference within a session and are
destroyed at the end of the session. [A local temporary table is
created on first reference within a module and destroyed when the module
completes. But since we don't have modules...].

Definitions of temporary tables have the same scope, visibility, and
protection as persistent tables. The temporary table metadata isn't
owned by a session any more than a persistent table is.

It is meaningless to assign attachment_id to a temporary table. Any
number of parallel session can be "using" the temporary table
simultaneously; each, however, has separate and distinct but private
physical structures.

There are lots of other models for temporary tables that make perfectly
good sense. One is a table with session scope and visibility that
vanishes without trace at the end of the session. It would be useful
and probably easier to use, but it isn't what the standard specifies.