Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
BTW, thanks to all who helped in improving this issue. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Mon, Dec 02, 2013 at 11:41:10AM -0500, Bruce Momjian wrote: If there were databases or users with default_transaction_read_only set in the old cluster, the pg_dumpall run will cause that property to be set in the new cluster, so what you are saying seems to be that a cluster can't be upgraded to a new major release if any database within it has that set. That is *precisely* my use case which I initially asked about. The use-case would be that default_transaction_read_only is turned on in postgresql.conf Are you telling me which use case I initially asked about on this thread ? Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Mon, Dec 02, 2013 at 01:24:18PM -0500, Bruce Momjian wrote: If there were databases or users with default_transaction_read_only set in the old cluster, the pg_dumpall run will cause that property to be set in the new cluster, so what you are saying seems to be that a cluster can't be upgraded to a new major release if any database within it has that set. That is *precisely* my use case which I initially asked about. The use-case would be that default_transaction_read_only is turned on in postgresql.conf Are you telling me which use case I initially asked about on this thread ? No, this is another use-case that is fixed the pg_upgrade patch. The ALTER DATABASE SET is also fixed by this patch. I see. Thanks, Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Sat, Nov 30, 2013 at 03:21:08PM -0800, Kevin Grittner wrote: If your argument is that you want pg_upgrade to work even if the user already turned on default_transaction_read_only in the *new* cluster, I would humbly disagree with that goal, for pretty much the same reasons I didn't want pg_dump overriding it. If there were databases or users with default_transaction_read_only set in the old cluster, the pg_dumpall run will cause that property to be set in the new cluster, so what you are saying seems to be that a cluster can't be upgraded to a new major release if any database within it has that set. That is *precisely* my use case which I initially asked about. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Wed, Nov 27, 2013 at 09:22:50PM -0500, Bruce Momjian wrote: Well, I can understand that, but part of the argument was that default_transaction_read_only is not part of the database, but rather just the transaction default: Karsten wrote: Maybe I am splitting hairs but setting transactions to readonly per default does not mean the database *as such* is to be readonly. It literally applies to the *default* state of transactions (as opposed to the ONLY state of transactions). No more, no less. I ask again! What is the logic that has us setting statement_timeout in pg_dump but default_transaction_read_only in pg_dumpall? Why can't I get an answer to that question? Bruce, I can't answer that. I am not versed enough to know. All I can make sure (and hope to have made) is that the failing use case is very clear. Is it that statement_timeout is less likely to lead to a restore failure? That seems right -- default_read_only WILL fail the restore while stmt_timeout CAN. Or rather: One of the *legitimate* settings of read_only WILL fail it. But only *insane* settings of _timeout WILL, too (like, extremely short ones). Saner settings CAN still. Yes, I do agree that it seems useful to temporarily apply a sane stmt_timeout as well. But perhaps the idea was to think of the minimal impact patch solving the immediate problem at hand (my use case) ? Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Thu, Nov 28, 2013 at 10:39:18AM -0500, Bruce Momjian wrote: Well, then we are actually using two different reasons for patching pg_dumpall and not pg_dump. Your reason is based on the probability of failure, while Tom/Kevin's reason is that default_transaction_read_only might be used to block changes to a specific database, and they want a pg_dump restore prevented, but a pg_dumpall restore to succeed. I can't really argue one way or another because *I* am not likely to be able to offer an actual patch. At any rate all I am interested in is that pg_upgrade does not fail to upgrade in surprising ways. Regarding restoring a pg_dump IMO the line would need to be drawn along the -c/--clean option because using such seems to clearly say that, yes, the user *wants* data to be deleted. If -C/--create is used it shouldn't matter ... However, I'm not saying that this is how it is to be done. I am well aware that drawing such subtle distinctions is walking quite a fine line. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Tue, Nov 26, 2013 at 03:25:44PM -0800, Kevin Grittner wrote: doc patch? Instead of the fix you mean, or with it? I don't see what we would change in the docs for the fix; the alternative might be to document that pg_dumpall output will fail to restore if any database (or the restoring user) has this property set. a) since pg_dump is not planned to be changed to deal with it a hint in the pg_dump docs would be helpful I can fully understand the argument that if the dump does NOT contain a create database the restore should indeed honor the existing database setting. In case it does contain a create database statement the issue won't exist -- because the database will need to be gone before the restore. b) if pg_ugprade is not fixed then a hint in the docs is useful (see below) pg_upgrade probably needs a doc patch too, or a reset like pg_dumpall. pg_upgrade is more like pg_dumpall, so a code patch seems most logical, again, assuming we decide that pg_dumpall is the right place for this reset of default_transaction_read_only. I don't have much opinion on what the pg_upgrade aspect except, The situation is this: naive user: - installs new PG version - upgrades old cluster with one default_read_only database - upgrade fails (or check fails - latter requires patch) - user asks someone - user unsets default_read_only - upgrades old cluster - upgrade succeeds - user re-sets default_read_only careful user: - installs new PG version - reads pg_upgrade docs of new version (requires doc patch) - user unsets default_read_only - upgrades old cluster - upgrade succeeds - user re-sets default_read_only I can't for the life of it think of a scenario where one would go: Oh, I've got a default_read_only database -- let's NOT upgrade the cluster. check. Passing the check but failing during the upgrade would not be very user-friendly. Again, I'm not sure that a doc patch is needed to say that pg_upgrade works even when this option is set. Why would anyone assume otherwise? Why would we list this property and not others? A doc patch is only needed if pg_upgrade does NOT work when some databases are default_read_only because THAT is counterintuitive: To the naive user upgrading from version to version is like make a copy, perhaps rename a file or two. It doesn't intuitively suggest a need for WRITE access to individual databases themselves. However, if the current behaviour is retained it would be very useful to document that and also document one or two ways around it (unsetting, PGOPTIONS, ...). Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Sat, Nov 23, 2013 at 08:44:42AM -0800, Kevin Grittner wrote: Here's my problem with that. Here's setup to create what I don't think is all that weird a setup: ... The following appears to produce a good backup, since there is no error: ... Now we attempt to restore what we thought was a good backup: psql postgres ~/dumpall.sql What we get is: 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 ... ERROR: cannot execute CREATE SCHEMA in a read-only transaction ERROR: cannot execute ALTER SCHEMA in a read-only transaction ERROR: cannot execute CREATE EXTENSION in a read-only transaction ERROR: cannot execute COMMENT in a read-only transaction ... If the dump is made with the attached patch, you get this on restore: ... The cluster is created in the state that was dumped, default read only flags and all. I find the patched behaviour more conformant with the Principle Of Least Astonishment. Maybe I am splitting hairs but setting transactions to readonly per default does not mean the database *as such* is to be readonly. It literally applies to the *default* state of transactions (as opposed to the ONLY state of transactions). No more, no less. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade ?deficiency
On Sat, Nov 23, 2013 at 12:11:56PM -0500, Tom Lane wrote: I also agree with *not* changing pg_dump, since it is not the charter of pg_dump to recreate a whole cluster, and the objection about possibly restoring into a database that was meant to be protected by this setting seems to have some force. This is were the suggestion comes in (which was already raised) to add some commandline option to the effect of --ignore-default-tx-readonly which, however, I agree with can be worked around by projects (like GNUmed, in which context this issue came up at all) providing restore scripts setting PGOPTIONS appropriately ... Thanks for all the work, Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ANNOUNCE] == Postgres Weekly News - March 09 2008 ==
On Mon, Mar 10, 2008 at 12:16:52AM -0700, David Fetter wrote: - Add to TODO: Add SQLSTATE severity to PGconn return status. Yes, please. I have recently been discussing this with Andreas, Bernd and Peter at Linuxtage Chemnitz. In GNUmed we want to be able to distinguish connection errors due to faulty credentials (not *which* part of the credentials only *whether* credentials are the cause) from other errors in order to react meaningfully in the program. Currently, PGconn only returns CONNECTION_BAD or _OK and an error message. The error message already says things like FATAL: password authentication failed for user ... so the level of information I want is already there. However, our code would need to parse the message to detect this state. This is a problem because the above message is translated according to the local users LC_MESSAGE setting. IOW, it'd be really helpful if the error state communicated by the message in a way independant of the wording of the message itself. Now, a technically proper way might be to change the API to return a status along with the message. A hacky but easy-to-implement solution might be to simply append or prepend a fixed, non-translated string to the message. Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
