[Tfug] more RDBMS

[Bexley Hall]

Hi,

--- Nate <nate at seekio.com> wrote:

> Primo:
> 
> The only way that I know of to do that in a portable
> fashion would be to track it yourself.
> 
> 1. Track the updates in a different table that you
> update every time you
> do an update.  For instance, if you had another
> table that simply had a
> timestamp of the last time you updated the table,
> that would work.  The
> problem is that every place that updates your table
> has to also update
> the last_update table.  This may not be a viable
> option in a large
> complex application, or with multiple applications
> acting on that same
> table.  There are other ways to get around this,
> with either dictionary
> lookups or triggers, but they are not portable as
> every RDBMS will do
> it's dictionary info different, and triggers aren't
> supported everywhere.

Hmmm... I *could* move that responsibility into the
client (i.e. encapsulate the update of the "tracking"
table within every update of the *real* tables).
But, I suspect I will *eventually* forget to do this
somewhere and shoot myself in the foot...  :<
 
> 2. Another option is to look for obvious changes to
> a pattern in the
> data.  For instance, if your table is only ever
> inserted into, and every
> insert has a unique id that is sequentially
> incremented, then your
> client just has to keep track of the last ID it knew

Yeah, I had thought about that.  Using a "sequence"
type and just looking at that.  But, I think there
are ways to break that approach, too.

Triggers seem to be the most robust solution...

> about.  Then to
> check if the table has been updated, simply do a,
> "select count(*) from
> table where id > ?" and bind $last_update_id to it. 
> Or if every update
> also includes a change to a timestamp column, then
> you can track it that
> way.  "select count(*) from table where update_time
> > ?" and bind
> $last_update_time to it.  I've used both techniques
> in the past.
> 
> Honestly the easiest and fastest method would
> probably be to do it
> non-portable.  If your application supports 3
> different databases, just
> put in 3 different ways of checking it.  The best
> way being a dictionary
> lookup of some sort.  Or triggers.

I don't want to have to revisit this issue 1, 5 or
10 years from today... :-/
 
> Secundo:
> 
> My first impression is that a SQL call to get the
> time is quite
> expensive, especially if you do it often, like every

No.  More like 1+ times per second per client.  Maybe
less.  I.e. just enough to make sure the clients
*look* like they are tracking the same time-of-day
and that one hasn't gone off kilter, etc.
(what the heck is a kilter?)

> second.  If they're
> kiosks you control, why not just sync their time to
> the server using
> existing tools, such as NTP or rdate.  If you can't

Because then I have to get ntp running on the
client and bear the cost of that process.  I imagine
the overall cost of just spitting out two SELECT now()
queries every minute is probably a lot less than
having to fork() a separate process to watch the
current time to some ultrafine precision.

(e.g., if the clocks in my home are within 5 minutes
of each other, I consider them all "correct"...)

> change the local
> time (such as if you don't have root) you can at
> least use rdate to do a
> remote lookup.
> 
> Another method I have used in the past which is
> possibly a little
> lighter is to do a HEAD request to a web server
> running on a box with
> known good time, such as the database server itself.

I don't care if the time is correct as much as I care
that the times reported on all of the clients are
mutually consistent with the time reported on the
server.  I.e. if the server timestamps entries in
the RDBMS, I should be 100% confident that displaying
any of those timestamps as the result of a query
won't have the user wondering "How the hell could
that have happened at 9:45 when this computer tells
me current time is 8:27?"

>  For example:
> 
> > HEAD 'http://www.google.com'|egrep '^Date'
> Date: Tue, 23 Oct 2007 17:25:43 GMT
> 
> You have to parse it, of course, which can be
> expensive.  You could do
> the lookup every once in a while, and just store a
> delta value to use
> from then on.  That way you're not contantly hitting
> the server for the
> time, and instead you just store how many seconds
> the local machine is
> off from the server, and adjust all of your time
> calculations accordingly.
> 
> So, use NTP if you can.  If you can't, then
> everything you do is going
> to have a little overhead, and increase the
> complexity of your process.

I'll try a thread that just spits out SELECTs and
see how much of a hit that takes...

Thx,
--don

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com