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) 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 > -- Roland Bouman http://rpbouman.blogspot.com/ Author of "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL", http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
