[Tfug] RDBMS reprise

[Tim Ottinger]

> One reason that I love using PDA's as examples is I
> have
> yet to come up with a good understanding of why *they*
> are so poorly accepted (the *concept* sounds
> wonderful;
> yet I haven't met anyone who continues to use his/her
> PDA after a few months with any regularity  :< )
>   
Great point.  I have had three, not counting the
PDA phone.  I only use the media player, phone
numbers, and the alarm feature.  They're just
not really convenient.

> So, for example, how do I safeguard against the
> application crashing and leaving that "results" table
> sitting on the server (indefinitely)?  
I can't remember which do what, but some
databases use the 'create temp table' and
destroy them when the session ends.  Some,
sadly, do not.  I have seen some that never
collect and delete them, only keep them with
limited visibility to the user.  That would be
very very bad for you.

However, most systems keep stats and you
could query metadata for temp tables of a
given age, assuming that they're no longer
valuable.  Again, metadata is structured
differently by different vendors.  I suppose
you could make a timestamp wart in the name.
I've not tried it.

> Are there ways
> to explicitly/implicitly free up portions of that
> table as my application determines that it is finished
> examining them?  
Temp tables can be dropped, or you can
"delete from/where"  But sometimes you will
have to clean the db to get space reclaimed.

> Can the server do anything (other
> than crash) that would make that table "go away"
> unexpectedly (e.g., if database is vacuumed,
> reindexed,
> etc. in a scheduled routine maintenance task)?
>   
If you drop them or if the db cleans up temp
tables on session end.   Especially if you hold
open a transaction.
> Just *how* clever are the RDBMS implementers?  I.e. do
> they exploit technologies like CoW to minimize the
> impact of this type of usage
During a transaction, a lot of them use mvcc,
which is pretty amazingly useful.

> (sigh)  I think I'll go back to crimping CAT5 cables!
> At least, there, I can *see* my problems!  
And I don't argue about the questions. :-)

Cheers