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@gmx.net       http://yi.org/peter-e/