Roland Bouman wrote:
Hi Brian, All,
I can't seem to find anything in the (2003) standard that indicates a
temporary table can in fact have the same name as an existing base
table or view.
Perhaps I am not looking in the right place, but that would solve it:
simply don't allow creation of temporary tables if such a table exists
already in that schema.
For the ones that you create under the hood, you could either create
them in a special schema reserved for that purpose, or otherwise
prefix them with a reserved prefix.
(I'd still like to learn where the standard says you can mask an
existing table with a temporary one - genuinely interested, I just
can't seem to find it)
AFAIK, this is correct. Names for temporary tables are created in the same
namespace as for ordinary tables and views, henece there is never a problem in
ALTER TABLE or DROP TABLE to know what table you are referring to. The special
thing about temporary tables is that the names are visible to all users, but
they are instantiated as private copies within a session. Regular GRANT/REVOKE
privileges can be used to restrict visibility, but as long as you may have
multiple sessions per user, you can still have multiple instantiations of one
temporary table.
I guess this may have strange effects when a user tries to delete a temporary
table that is currently instantiated by other sessions, but it is probably as
simple as keeping references for all existing users, and delete the remains when
the last reference is gone.
I think this approach is much better than the approach of overlapping name
spaces that MySQL has. The drawback is that you must register a temporary table
in the global name space, and you have to access the global name space each time
you instantiate a temporary table, which requires global synchronization.
But the standard also defines the declared local temporary table that is created
and instantiated privately. These are kept out of the namespace problem by
requring all references to start with "MODULE.".
That allows you to write ALTER TABLE MODULE.MY_TEMPORARY_TABLE whatever...
IMHO, it becomes even more explicit to replace MODULE with TEMPORARY - it just
means that you have a reserved schema name TEMPORARY that you cannot allow the
user to create...
The SQL standard also mentions an "effective" schema name for temporary table
contents, which may be made up from the schema name of the temporary table and
the unique session identifier of the session instantiating that table.
Roy
On Thu, Mar 25, 2010 at 11:06 AM, Brian Aker <[email protected]> wrote:
Hi!
You can have a table which is both a temporary and a standard table in the
same schema. Awesome... there are a lot of good reasons for this.
You cannot though do an alter table on the standard table if the temporary
table is around (which I personally think is bad).
For error messages? You don't know what the error message was on... sure if
you are sitting there you might know that it was the temp table (since a
temporary table always gets referenced before the standard table), but in
error messages? There is no way to know.
We internally have three basic forms of tables. Temporary, Standard, and
"Internal". We use internal for results/alter/etc. Those come up in error
messages from time to time, but since the names are gibberish it is pretty
simple to know when they are at fault (though not perfect).
We have internally "temporary" reserved as a schema for our own use (same
with data_dictionary and information_schema). Temporary you can't see
though. Since "internal" tables live there, I've been having error messages
write out as "internal.#sql34324" as the sql path for error messages. Right
now temporary and standard are the same... but I would like to change that.
SqlServer uses a # symbol to represent those tables. I think we should do
the same for error messages. In fact, I think we should reserve the "#"
character for our own internal use and not allow its usage as the first
character.
Thoughts? I want some bit of difference in the names in the end. Trying to
figure out errors when I don't know if it was a transient table or not is a
bit of a pain.
Cheers,
-Brian
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
