Re: LEFT JOIN in pg_dumpall is a bug

Tom Lane writes:

> > $PSQL -d template1 -At -F ' ' \
> > -c "SELECT datname, usename, pg_encoding_to_char(d.encoding),
> > datistemplate, datpath FROM pg_database d LEFT JOIN pg_shadow u ON (datdba
> > = usesysid) WHERE datallowconn;" | \
> > while read DATABASE DBOWNER ENCODING ISTEMPLATE DBPATH; do

> Oops, you're right, the read won't keep the columns straight. Come to
> think of it, it would do the wrong thing for empty-string datname or
> usename, too,

It won't actually work to restore such a setup, because zero-length
identifiers are no longer allowed.

> Is there a more robust way of reading the data into the script?

Provided that 'cut' is portable, then this works for me:

TAB=' ' # tab here

$PSQL -d template1 -At -F "$TAB" \
-c "SELECT datname, usename, pg_encoding_to_char(d.encoding),
datistemplate, datpath FROM pg_database d LEFT JOIN pg_shadow u ON (datdba
= usesysid) WHERE datallowconn;" | \
while read THINGS; do
DATABASE=`echo "$THINGS" | cut -f 1`
DBOWNER=`echo "$THINGS" | cut -f 2`
ENCODING=`echo "$THINGS" | cut -f 3`
ISTEMPLATE=`echo "$THINGS" | cut -f 4`
DBPATH=`echo "$THINGS" | cut -f 5`

If 'cut' is not portable, then I don't believe you can do it with
IFS-based word splitting, because two adjacent separator characters don't
seem to indicate an empty field but are instead taken as one separator.

> I think I'd rather see a warning, though, and let the script try to dump
> the DB anyway.

Maybe for databases without an owner, but not for empty database or user
names.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/