Structure of in_vote table

[conseo]

Hi Mike,

> 
> I wasn't objecting in the end to the timestamp datatype, but to the
> "with time zone" qualifier.  My understanding is that it doesn't store
> the time zone as implied; instead it converts the writes to UTC and
> the reads to local time.
I cannot reproduce your described behaviour through JDBC 
(java.sql.Timestamp.getTime() returns millisecs since beginning of Unix Time, 
which is UTC, even though Postgres shows them with MEZ (+01) offset). If we 
leave out the timezone, then timestamp buys us little more than better SQL 
semantics. Take the bigint millis since Unix time for simplicity, it is not 
too important for me, only the actual data is and here the timezone would add 
information. 

> But now I think this is all a red herring.
> There's a larger underlying problem.  We need a clean sequence that
> isn't liable to information loss.  We borked the solution and it's
> mostly my fault.  I've made this mistake in the past; I try to solve a
> sequencing problem with timestamps and instead I create a mess.  The
> correct solution is a serial counter (probably bigserial).  I think
> the primary keys should be:
> 
>     serial, serviceName, voterEmail

Whatever you prefer, I will have to transform it anyway. The relevant voter-
generated data is: 
<timestamp, globally unique voter-id, globally unique consensus target id>
Unless this is shared through mirroring, it is of no use, all additional data 
is nice, but not mandatory and might in fact be just an accidential attribute 
of computation (like the dart-sector).

> 
> All the other data is parked in an xml column for max flex.  And since
> we have so few servers to overhaul, I think we should take advantage
> of the overhaul to normalize the name of the poll column:
> 
>     serial, pollName, voterEmail    (this is better)
> 
> So the serial column will eventually enable us to do historical
> queries for purposes of verification.  If we ever need to query by
> calendar date, then we could construct a sister table to cross-index
> the serial numbers and calendar days, or whatever precision we need
> (we'll make it reconstructable).  It will deal with any anomolies in
> the vote timestamps.  Timestamps are not guaranteed to be correct nor
> even correctly ordered, so we won't depend on them for anything
> important.  We'll document this in the API.
> 
> What do you think?  (please post to list if you prefer)
Sounds reasonable. I still would not put timestamp and candidate in xml, as 
they are first-class attributes imo, but it is your engine, mine will have to 
extract that information first to count, which is supposed to happen anyway in 
vote-mirroring. I hoped we could share the table and I could code an in-place 
alternative, but this should still work out, so don't worry. All I really need 
is the voter-data. This is already in there, so feel free to change as you 
wish.

conseo