List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 9 2005 6:25pm
Subject:Re: INSERT DELAYED and NOW()
View as plain text  
Eric Bergen <ebergen@stripped> wrote on 06/09/2005 12:56:59 PM:

> How about something like this:

> mysql> select @t := now();
> +---------------------+
> | @t := now()         |
> +---------------------+
> | 2005-06-09 09:55:49 |
> +---------------------+
> 1 row in set (0.00 sec)

> mysql> insert delayed into t set t = @t;
> Query OK, 1 row affected (0.00 sec)

> mysql> select * from t;
> +---------------------+
> | t                   |
> +---------------------+
> | 2005-06-09 09:55:49 |
> +---------------------+
> 1 row in set (0.01 sec)

> 
> This way you get the current time of the call and it doesn't matter how
> long the insert delayed sits for.

> Jochem van Dieten wrote:

> >On 6/9/05, Jeremiah Gowdy wrote:
> >
> >
> >>>Does this seem to break SQL / application logic in some fashion?
> >>>
> >>>
> >>>Not worse then it is currently broken :)
> >>>
> >>>According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a
> >>>synonym for NOW(), is supposed to have a value that does not change
> >>>during a transaction. At which point during the transaction that 
value
> >>>is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section
> >>>6.31)
> >>>
> >>>Since both NOW() and INSERT DELAYED are MySQL extensions I don't
> >>>particularly care how they behave/interfere, but I would prefer any
> >>>solution/hack not to complicate MySQL ever becomming standard
> >>>compliant in this regard (and standard compliance is an official
> >>>goal).
> >>>
> >>>
> >>Does the standard specify when the timestamp is evaluated?
> >>
> >>
> >
> >During the transaction.
> >
> >
> >
> >
> >>I agree that it might be better for it to be a seperate function, but 
since
> >>DELAYED isn't part of the standard, I'm not sure there's anything that 
keeps
> >>an implementation from evaluating the CURRENT_TIMESTAMP for a query 
upon
> >>receipt of the query from the network, rather than when the SQL 
statement is
> >>evaluated.
> >>
> >>
> >
> >Let me reiterate:
> >"Since both NOW() and INSERT DELAYED are MySQL extensions I don't
> >particularly care how they behave/interfere".
> >
> >
> >
> >
> >>If I wrote a SQL server from scratch, would this not
> >>be a valid implementation, to timestamp upon network receive of a 
complete
> >>query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) 
function
> >>while parsing a query?
> >>
> >>
> >
> >That depends on some more implementation issues: perceivably your
> >network receive could even be before the start of the transaction.
> >Evaluate CURRENT_TIMESTAMP only once per transaction, between the
> >start of the transaction and the end of the transaction.
> >
> >Jochem
> >
> >
> >

The problem with that is that you have just doubled the query count at the 
central logging server. That's a lot of traffic it can probably do 
without. 

I like the QNOW() approach. (Use an extension, the new function, to deal 
with a side effect of an extension, DELAYED. It's a universal balance kind 
of thing.) 

Some alternative names: QUEUEDNOW(), QUEUEDTIMESTAMP(), RECEIVEDTIME(), 
RECEIVEDTIMESTAMP(), ARRIVALTIMESTAMP()


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
INSERT DELAYED and NOW()Jeremiah Gowdy9 Jun
  • Re: INSERT DELAYED and NOW()Jeff Smelser9 Jun
  • Re: INSERT DELAYED and NOW()Jeremiah Gowdy9 Jun
  • Re: INSERT DELAYED and NOW()Jochem van Dieten9 Jun
  • Re: INSERT DELAYED and NOW()Jeremiah Gowdy9 Jun
    • Re: INSERT DELAYED and NOW()Jochem van Dieten9 Jun
      • Re: INSERT DELAYED and NOW()Eric Bergen9 Jun
        • Re: INSERT DELAYED and NOW()SGreen9 Jun
          • Re: INSERT DELAYED and NOW()Eric Bergen10 Jun
        • Re: INSERT DELAYED and NOW()Philippe Poelvoorde10 Jun