Re: After upgrade pg_dumpall fails

Patrick Nelson <pnelson@neatech.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