Re: After upgrade pg_dumpall fails

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Patrick Nelson <pnelson(at)neatech(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, Thomas Lockhart <lockhart(at)fourpalms(dot)org>
Subject: Re: After upgrade pg_dumpall fails
Date: 2002-08-11 21:58:24
Message-ID: 23865.1029103104@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Patrick Nelson <pnelson(at)neatech(dot)com> writes:
> Last night my system ran a pg_dumpall and displayed an error:
> ERROR: Unable to convert abstime 'invalid' to timestamptz

> The error seems to come from pg_dumpall at the following line:

> || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';'

That's moderately annoying. The short-term workaround for you is to
get rid of the "invalid" entry in pg_shadow (set it back to NULL, is
my advice). But pg_dumpall shouldn't spit up on entries that pg_shadow
can store.

The reason pg_dumpall is coded the way it is is that there's no direct
cast path from abstime to text, or at least none that works as we want:

regression=# select now()::abstime::text;
ERROR: Cannot cast type 'abstime' to 'text'
regression=# select text(now()::abstime);
text
------------
1029102814 <<-- seems to be relying on binary equiv to int4
(1 row)

On the other hand, timestamp/timestamptz have no equivalent to the
"invalid" value, and I don't think we want to add one (didn't we just
rip that out, for what seemed good reason?).

One answer is to add an abstime-to-text cast function. But I wonder
whether we should expend more effort on a datatype that's already
deprecated. I wonder how much work there would be in changing
pg_shadow's column to be timestamptz?

Thomas, this seems to be your turf, any thoughts?

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Justin Clift 2002-08-12 00:40:27 Another database web poll
Previous Message Patrick Nelson 2002-08-11 21:21:38 Re: lo_import