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
