Re: LEFT JOIN in pg_dumpall is a bug

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> This snippet in pg_dumpall
> $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

> won't actually work if there indeed happens to be a database without a
> valid owner, because the 'read' command will take ENCODING as the dba
> name.

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, and it's only because datpath is the last column that
we haven't noticed it doing the wrong thing on empty datpath.

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

> I guess the real question is, what should be done in this case? I think
> it might be better to error out and let the user fix his database before
> backing it up.

Possibly. The prior state of the code (before I put in the LEFT JOIN)
would silently ignore any database with no matching user, which was
definitely NOT a good idea.

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

> (At a glance, I think pg_dump also has some problems with these sort of
> constellations.)

Yes, there are a number of places where pg_dump should be doing outer
joins and isn't. I think Tatsuo is at work on that.

regards, tom lane