David Fetter | 1 Nov 15:58 2010

Re: Slony1-general Digest, Vol 44, Issue 16

On Sun, Oct 31, 2010 at 09:55:05AM +0100, Ger Timmens wrote:
> Can we change the definitions of all 'timestamp' columns
> from 'timestamp without time zone' to timestamp with time stamp'
> in slony (we are using 1.2.20 rightnow).

You need to send any DDL changes via EXECUTE SCRIPT.  ALTER TABLE is a
DDL change :)

Cheers,
David.
--

-- 
David Fetter <david@...> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@...
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Ger Timmens | 1 Nov 16:05 2010

Re: Slony1-general Digest, Vol 44, Issue 16

I understand, but maybe I wasn't clear:
The column defition of the slony sl_ tables should be adapted
to 'timestamp with time zone' instead of 'timestamp without
timezone' to prevent any errors in monitoring scripts when
the clock goes one hour back from summer to winter time.

So it's more of a suggestion/patch for
slony-1.2.22 and slony-2.0.6.

Regards,

Ger Timmens

On 11/01/2010 03:58 PM, David Fetter wrote:
> On Sun, Oct 31, 2010 at 09:55:05AM +0100, Ger Timmens wrote:
>> Can we change the definitions of all 'timestamp' columns
>> from 'timestamp without time zone' to timestamp with time stamp'
>> in slony (we are using 1.2.20 rightnow).
> 
> You need to send any DDL changes via EXECUTE SCRIPT.  ALTER TABLE is a
> DDL change :)
> 
> Cheers,
> David.
Christopher Browne | 1 Nov 16:15 2010

Re: Slony1-general Digest, Vol 44, Issue 16

Ger Timmens <Ger.Timmens@...> writes:
> Can we change the definitions of all 'timestamp' columns
> from 'timestamp without time zone' to timestamp with time stamp'
> in slony (we are using 1.2.20 rightnow).

Sure, you could submit DDL requests via the slonik command EXECUTE
SCRIPT
   <http://slony.info/documentation/1.2/stmtddlscript.html>

The script would consist of a series of ALTER TABLE commands, one for
each column to be thus altered.

I suspect you'll find this to be a mighty expensive operation, as it:

  a) locks all the tables (in 1.2, ALL replicated tables, in 2.0, just
     the ones that need to be altered) for the duration, and

  b) fairly likely leads to the affected tables being rewritten, so all
     the old tuples are dead, so they need vacuuming rather badly.

So, expensive, but it can certainly be done.
--

-- 
let name="cbbrowne" and tld="afilias.info" in String.concat " <at> " [name;tld];;
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"
David Fetter | 1 Nov 16:22 2010

Re: Slony1-general Digest, Vol 44, Issue 16

This may or may not be a bug fix.  It's possible it's a large enough
feature change that it might need to go into 2.1.

Cheers,
David.
On Mon, Nov 01, 2010 at 04:05:43PM +0100, Ger Timmens wrote:
> I understand, but maybe I wasn't clear:
> The column defition of the slony sl_ tables should be adapted
> to 'timestamp with time zone' instead of 'timestamp without
> timezone' to prevent any errors in monitoring scripts when
> the clock goes one hour back from summer to winter time.
> 
> So it's more of a suggestion/patch for
> slony-1.2.22 and slony-2.0.6.
> 
> Regards,
> 
> Ger Timmens
> 
> 
> On 11/01/2010 03:58 PM, David Fetter wrote:
> > On Sun, Oct 31, 2010 at 09:55:05AM +0100, Ger Timmens wrote:
> >> Can we change the definitions of all 'timestamp' columns
> >> from 'timestamp without time zone' to timestamp with time stamp'
> >> in slony (we are using 1.2.20 rightnow).
> > 
> > You need to send any DDL changes via EXECUTE SCRIPT.  ALTER TABLE is a
> > DDL change :)
> > 
> > Cheers,
(Continue reading)

Ger Timmens | 1 Nov 16:25 2010

Re: Slony1-general Digest, Vol 44, Issue 16

In addition to my previous posts:

I suggest (for slony 1.2.2x):

ALTER TABLE sl_archive_count ALTER COLUMN ac_timestamp TYPE
timestamp with time zone;

ALTER TABLE sl_confirm ALTER COLUMN con_timestamp TYPE timestamp
with time zone;

ALTER TABLE sl_registry ALTER COLUMN reg_timestamp TYPE timestamp
with time zone;

ALTER TABLE sl_event ALTER COLUMN ev_timestamp TYPE timestamp with
time zone;

Probably slony 2.0.x needs the same changes.

Regards,

Ger

On 11/01/2010 04:15 PM, Christopher Browne wrote:
> Ger Timmens <Ger.Timmens@...> writes:
>> Can we change the definitions of all 'timestamp' columns
>> from 'timestamp without time zone' to timestamp with time stamp'
>> in slony (we are using 1.2.20 rightnow).
> 
> Sure, you could submit DDL requests via the slonik command EXECUTE
> SCRIPT
(Continue reading)

Christopher Browne | 1 Nov 16:53 2010

Re: Slony1-general Digest, Vol 44, Issue 16

Ger Timmens <Ger.Timmens@...> writes:
> I understand, but maybe I wasn't clear:
> The column defition of the slony sl_ tables should be adapted
> to 'timestamp with time zone' instead of 'timestamp without
> timezone' to prevent any errors in monitoring scripts when
> the clock goes one hour back from summer to winter time.
>
> So it's more of a suggestion/patch for
> slony-1.2.22 and slony-2.0.6.

Ah.

If the slon processes are running in UTC/GMT, as is recommended, then
there's no issue whatever, because UTC/GMT do not adjust for DST.

 <http://www.slony.info/adminguide/slony1-1.2.6/doc/adminguide/requirements.html#TIMES>
--

-- 
let name="cbbrowne" and tld="ca.afilias.info" in String.concat " <at> " [name;tld];;
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"
Brian Hirt | 1 Nov 18:26 2010

Re: Slony1-general Digest, Vol 44, Issue 16

If it's known that slon has problems with time zones with daylight savings time, wouldn't it make sense to
default it to UTC or at least give you a warning when it starts up?   I had no idea about this and I could see
others being bit in the rump by this as well.  I've set up my scripts to use TZ=UTC now instead of the machines
local time zone and have restarted them, but now I wonder what would have happened if I didn't read this thread?

That being said wouldn't it make more sense, like the OP is saying, to  use timestamp without timezone?   Why
give someone a gun to shoot themselves in the foot if it can be avoided?

--brian

On Nov 1, 2010, at 9:53 AM, Christopher Browne wrote:

> Ger Timmens <Ger.Timmens@...> writes:
>> I understand, but maybe I wasn't clear:
>> The column defition of the slony sl_ tables should be adapted
>> to 'timestamp with time zone' instead of 'timestamp without
>> timezone' to prevent any errors in monitoring scripts when
>> the clock goes one hour back from summer to winter time.
>> 
>> So it's more of a suggestion/patch for
>> slony-1.2.22 and slony-2.0.6.
> 
> Ah.
> 
> If the slon processes are running in UTC/GMT, as is recommended, then
> there's no issue whatever, because UTC/GMT do not adjust for DST.
> 
> <http://www.slony.info/adminguide/slony1-1.2.6/doc/adminguide/requirements.html#TIMES>
> -- 
> let name="cbbrowne" and tld="ca.afilias.info" in String.concat " <at> " [name;tld];;
(Continue reading)

Andrew Sullivan | 1 Nov 19:33 2010
Picon

Re: Slony1-general Digest, Vol 44, Issue 16

On Mon, Nov 01, 2010 at 11:26:34AM -0600, Brian Hirt wrote:
> If it's known that slon has problems with time zones with daylight
> savings time, wouldn't it make sense to default it to UTC or at
> least give you a warning when it starts up?  I had no idea about
> this and I could see others being bit in the rump by this as well.

Well, it _is_ in the documentation.  I dimly recall that we attempted
to do something with the time zones in the past, but then people would
try to make the machines operate in different time zones, and that
broke in different and unpleasant ways.  Maybe I'm imagining that,
however.

Anyway, yes, all your machines need to be in the same time zone, and
one where the time doesn't occasionally whip around in insane ways.
It's generally a good idea to run in UTC for other reasons, too.  For
instance, your syslog has this problem as well.  If someone manages to
reboot the box in the one hour of "lost" time on Sunday, how will you
know what time it happened?

A

--

-- 
Andrew Sullivan
ajs@...
Vick Khera | 1 Nov 20:06 2010

Re: Slony1-general Digest, Vol 44, Issue 16

On Mon, Nov 1, 2010 at 11:53 AM, Christopher Browne
<cbbrowne@...> wrote:
> If the slon processes are running in UTC/GMT, as is recommended, then
> there's no issue whatever, because UTC/GMT do not adjust for DST.
>

I disagree.  At least at some point in history this happened to me at
the DST switchover:

[rt]% psql -U postgres -x -c 'select * from _rt.sl_status' rt3
-[ RECORD 1 ]-------------+---------------------------
st_origin                 | 20
st_received               | 10
st_last_event             | 4896924
st_last_event_ts          | 2007-11-04 01:08:57.010633
st_last_received          | 4896923
st_last_received_ts       | 2007-11-04 01:08:53.002901
st_last_received_event_ts | 2007-11-04 01:08:53.002292
st_lag_num_events         | 1
st_lag_time               | -00:59:54.583198

[rt]% date
Sun Nov  4 01:09:01 EDT 2007

I'm not 100% convinced that having the slon run in UTC is sufficient
for there to be "no problems" as the above is totally based on the
server's idea of the time.

For that full hour the lag time was off by one hour.  It made for some
fun nagios alerts.
(Continue reading)

Brian Hirt | 1 Nov 20:21 2010

Re: Slony1-general Digest, Vol 44, Issue 16

On Nov 1, 2010, at 1:06 PM, Vick Khera wrote:
> 
> For that full hour the lag time was off by one hour.  It made for some
> fun nagios alerts.
> 

Vick, 

Do you know if it is just lag time that's reported incorrectly, or are there actual problems with
replication during this window?

--brian

Gmane