Thread: After upgrade pg_dumpall fails

Spent yesterday upgrading to 7.2.1, wasn't a walk in the park but it's
working.

Last night my system ran a pg_dumpall and displayed an error:

--
-- pg_dumpall (7.2.1)
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

connected to template1...
ERROR:  Unable to convert abstime 'invalid' to timestamptz

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

$PSQL -d template1 -At -c "\
SELECT
  'CREATE USER \"' || usename || '\" WITH SYSID ' || usesysid
  || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' else
'' end
  || CASE WHEN usecreatedb THEN ' CREATEDB'::text ELSE ' NOCREATEDB' END
  || CASE WHEN usesuper THEN ' CREATEUSER'::text ELSE ' NOCREATEUSER' END
  || CASE WHEN valuntil IS NOT NULL THEN ' VALID UNTIL '''::text
    || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';'
FROM pg_shadow
WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname =
'template0');" \

Looking at pg_shadow the structure looks like:

# \d pg_shadow
         Table "pg_shadow"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 usename     | name    |
 usesysid    | integer |
 usecreatedb | boolean |
 usetrace    | boolean |
 usesuper    | boolean |
 usecatupd   | boolean |
 passwd      | text    |
 valuntil    | abstime |
Unique keys: pg_shadow_usename_index,
             pg_shadow_usesysid_index
Triggers: pg_sync_pg_pwd

Is valuntil's type improper?  Anything else that might cause this?

Patrick Nelson wrote:
----------------->>>>
Spent yesterday upgrading to 7.2.1, wasn't a walk in the park but it's
working.

Last night my system ran a pg_dumpall and displayed an error:

--
-- pg_dumpall (7.2.1)
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

connected to template1...
ERROR:  Unable to convert abstime 'invalid' to timestamptz

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

$PSQL -d template1 -At -c "\
SELECT
  'CREATE USER \"' || usename || '\" WITH SYSID ' || usesysid
  || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' else
'' end
  || CASE WHEN usecreatedb THEN ' CREATEDB'::text ELSE ' NOCREATEDB' END
  || CASE WHEN usesuper THEN ' CREATEUSER'::text ELSE ' NOCREATEUSER' END
  || CASE WHEN valuntil IS NOT NULL THEN ' VALID UNTIL '''::text
    || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';'
FROM pg_shadow
WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname =
'template0');" \

Looking at pg_shadow the structure looks like:

# \d pg_shadow
         Table "pg_shadow"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 usename     | name    |
 usesysid    | integer |
 usecreatedb | boolean |
 usetrace    | boolean |
 usesuper    | boolean |
 usecatupd   | boolean |
 passwd      | text    |
 valuntil    | abstime |
Unique keys: pg_shadow_usename_index,
             pg_shadow_usesysid_index
Triggers: pg_sync_pg_pwd

Is valuntil's type improper?  Anything else that might cause this?
----------------->>>>

Oops thought I should add this:

select valuntil from pg_shadow;
 valuntil
----------




 invalid
(5 rows)

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