Re: [GENERAL] pg_upgrade ?deficiency

Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-11-22 13:07:29 -0800, Kevin Grittner wrote:
>> Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>>
>>> are you sure it also unsets default_transaction_readonly for
>>> the roles etc. it creates?
>>
>> I'm not sure I understand.  Could you give an example of what
>> you're concerned about?
>
> pg_dumpall first spits out global data (users, databases, tablespaces)
> and then invokes pg_dump for every single database. So I'd very strongly
> suspect that your patch will issue the CREATE ROLE etc. calls without
> unsetting default_transaction_readonly.

I changed my postgres database to default to read only (which is
not insane, given that I've seen so many people accidentally run
DDL there rather than in the database they intended), and got these
errors when I used it for my connection to restore pg_dumpall
output:

ERROR:  cannot execute COMMENT in a read-only transaction
ERROR:  cannot execute CREATE EXTENSION in a read-only transaction
ERROR:  cannot execute COMMENT in a read-only transaction
ERROR:  cannot execute REVOKE in a read-only transaction
ERROR:  cannot execute REVOKE in a read-only transaction
ERROR:  cannot execute GRANT in a read-only transaction
ERROR:  cannot execute GRANT in a read-only transaction

Oddly, it didn't complain about creating users within a read-only
transaction.  That seems like a potential bug.

Will look at covering pg_dumpall for that initial connection.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company