Structure of in_vote table
conseo
conseo at polyc0l0r.net
Sat Jun 1 19:18:14 EDT 2013
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
More information about the Votorola
mailing list