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/mail
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
> >
On Mon, Dec 2, 2013 at 06:57:53PM +0100, Karsten Hilbert wrote:
> 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
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 clu
On Sun, Dec 1, 2013 at 09:22:52AM +0100, Karsten Hilbert wrote:
> 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
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 did
On Sat, Nov 30, 2013 at 06:48:02PM -0500, Tom Lane wrote:
> Kevin Grittner writes:
> > Tom Lane wrote:
> >> What is the point of this, given that Kevin fixed pg_dumpall?
> >> Don't those fixes take care of the issue?
>
> > If there were databases or users with default_transaction_read_only
> >
Kevin Grittner writes:
> Tom Lane wrote:
>> What is the point of this, given that Kevin fixed pg_dumpall?
>> Don't those fixes take care of the issue?
> 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
Tom Lane wrote:
> Bruce Momjian writes:
>
>> I have fixed pg_upgrade in git-head with the attached patch,
>> which prepends default_transaction_read_only=false to PGOPTIONS.
>
> What is the point of this, given that Kevin fixed pg_dumpall?
> Don't those fixes take care of the issue?
>
> If your
Bruce Momjian writes:
> I have fixed pg_upgrade in git-head with the attached patch, which
> prepends default_transaction_read_only=false to PGOPTIONS.
What is the point of this, given that Kevin fixed pg_dumpall? Don't
those fixes take care of the issue?
If your argument is that you want pg_up
On Fri, Nov 22, 2013 at 01:55:10PM -0800, Kevin Grittner wrote:
> Kevin Grittner wrote:
>
> This covers pg_dumpall globals. Tested with a read-only postgres
> database and with default_transaction_read_only = on in the
> postgresql.conf file.
>
> It does nothing about pg_upgrade, which is sort
Bruce Momjian wrote:
> Once the patch is applied, I will be patching pg_upgrade by appending to
> PGOPTIONS, but that will only be for 9.4. The patch will be too risky
> and there are not enough problem reports to override that and warrant
> backpatching.
pg_dumpall patch applied, back to 8.4.
On Fri, Nov 29, 2013 at 12:46:01AM +0100, Karsten Hilbert wrote:
> 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
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 t
On Thu, Nov 28, 2013 at 10:20:53AM +0100, Karsten Hilbert wrote:
> > 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_onl
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 t
On Wed, Nov 27, 2013 at 03:36:12PM -0800, Kevin Grittner wrote:
> Tom Lane wrote:
>
> > I'm inclined to agree with Kevin that this behavior is wrong and
> > should be fixed (and back-patched), so far as pg_dumpall is concerned.
> > pg_dumpall's charter is to be able to recreate a database cluster
On Wed, Nov 27, 2013 at 03:36:12PM -0800, Kevin Grittner wrote:
> Of the people who posted on this thread supporting that, I think
> Tom said it best:
>
> Tom Lane wrote:
>
> > I'm inclined to agree with Kevin that this behavior is wrong and
> > should be fixed (and back-patched), so far as pg_d
Bruce Momjian wrote:
> On Wed, Nov 27, 2013 at 06:05:13AM -0800, Kevin Grittner wrote:
>> Bruce Momjian wrote:
>>> I am confused what we are patching. Are we patching pg_dump,
>>> pg_dumpall, or both?
>>
>> Just pg_dumpall.c.
>
> OK, there was a pg_dump patch earlier which we are not using now.
On Wed, Nov 27, 2013 at 06:05:13AM -0800, Kevin Grittner wrote:
> Bruce Momjian wrote:
> > On Tue, Nov 26, 2013 at 03:25:44PM -0800, Kevin Grittner wrote:
> >> Bruce Momjian wrote:
> >>
> >>> How are we handling breakage of pg_dump, not pg_dumpall?
> >>
> >> That was discussed. Do you have somet
Bruce Momjian wrote:
> On Tue, Nov 26, 2013 at 03:25:44PM -0800, Kevin Grittner wrote:
>> Bruce Momjian wrote:
>>
>>> How are we handling breakage of pg_dump, not pg_dumpall?
>>
>> That was discussed. Do you have something to add?
>
> I am confused what we are patching. Are we patching pg_dump,
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 res
On Tue, Nov 26, 2013 at 03:25:44PM -0800, Kevin Grittner wrote:
> Bruce Momjian wrote:
>
> > How are we handling breakage of pg_dump, not pg_dumpall?
>
> That was discussed. Do you have something to add?
I am confused what we are patching. Are we patching pg_dump,
pg_dumpall, or both? Can I
Bruce Momjian wrote:
> How are we handling breakage of pg_dump, not pg_dumpall?
That was discussed. Do you have something to add?
> 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_dumpa
On Mon, Nov 25, 2013 at 02:24:25PM -0800, Kevin Grittner wrote:
> Bruce Momjian wrote:
> > Kevin Grittner wrote:
> >> Bruce Momjian wrote:
> >>
> >>> I am not a fan of backpatching any of this.
> >>
> >> Here's my problem with that. Here's setup to create what I
> >> don't think is all that weir
Bruce Momjian wrote:
> Kevin Grittner wrote:
>> Bruce Momjian wrote:
>>
>>> I am not a fan of backpatching any of this.
>>
>> Here's my problem with that. Here's setup to create what I
>> don't think is all that weird a setup:
>>
>> The cluster is created in the state that was dumped, default
>>
On Sat, Nov 23, 2013 at 08:44:42AM -0800, Kevin Grittner wrote:
> Bruce Momjian wrote:
>
> > I am not a fan of backpatching any of this.
>
> Here's my problem with that. Here's setup to create what I don't
> think is all that weird a setup:
>
> The cluster is created in the state that was dump
Am Samstag, 23. November 2013, 08:44:42 schrieb Kevin Grittner:
> Bruce Momjian wrote:
> > I am not a fan of backpatching any of this.
>
> Here's my problem with that. Here's setup to create what I don't
> think is all that weird a setup:
>
> initdb Debug/data
> pg_ctl -D Debug/data -l Debug/da
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 so
Kevin Grittner writes:
> Bruce Momjian wrote:
>> I am not a fan of backpatching any of this.
> Are you saying that you find current behavior acceptable in back
> branches?
I'm inclined to agree with Kevin that this behavior is wrong and
should be fixed (and back-patched), so far as pg_dumpall i
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 though
Bruce Momjian wrote:
> I am not a fan of backpatching any of this.
Here's my problem with that. Here's setup to create what I don't
think is all that weird a setup:
initdb Debug/data
pg_ctl -D Debug/data -l Debug/data/logfile -w start
createdb test
psql test /dev/null 2>&1
psql postgres -c "al
On Fri, Nov 22, 2013 at 04:38:53PM -0800, Kevin Grittner wrote:
> Andres Freund wrote:
> > On 2013-11-22 13:34:18 -0800, Kevin Grittner wrote:
> >> Oddly, it didn't complain about creating users within a read-only
> >> transaction. That seems like a potential bug.
> >
> > There's lots of things t
On Fri, Nov 22, 2013 at 04:25:57PM -0800, Kevin Grittner wrote:
> Bruce Momjian wrote:
> > On Fri, Nov 22, 2013 at 01:55:10PM -0800, Kevin Grittner wrote:
> >
> >> It does nothing about pg_upgrade, which is sort of a separate
> >> issue. My inclination is that connections to the new cluster
> >>
Andres Freund wrote:
> On 2013-11-22 13:34:18 -0800, Kevin Grittner wrote:
>> Oddly, it didn't complain about creating users within a read-only
>> transaction. That seems like a potential bug.
>
> There's lots of things that escape XactReadOnly. I've thought (and I
> think suggested) before that
Bruce Momjian wrote:
> On Fri, Nov 22, 2013 at 01:55:10PM -0800, Kevin Grittner wrote:
>
>> It does nothing about pg_upgrade, which is sort of a separate
>> issue. My inclination is that connections to the new cluster
>> should set this and connections to the old should not.
>
> It is going to be
On Fri, Nov 22, 2013 at 01:55:10PM -0800, Kevin Grittner wrote:
> It does nothing about pg_upgrade, which is sort of a separate
> issue. My inclination is that connections to the new cluster
> should set this and connections to the old should not.
It is going to be tricky to conditionally set/res
Andres Freund wrote:
> FWIW, I am less than convinced that it is correct for
> pg_dump[all] to emit SET default_transaction_readonly = off;
It doesn't change the database setting, just the connection which
is issuing commands to create global object -- ones that don't
reside in the database we a
On 2013-11-22 13:34:18 -0800, Kevin Grittner wrote:
> 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)
FWIW, I am less than convinced that it is correct for pg_
Kevin Grittner wrote:
This covers pg_dumpall globals. Tested with a read-only postgres
database and with default_transaction_read_only = on in the
postgresql.conf file.
It does nothing about pg_upgrade, which is sort of a separate
issue. My inclination is that connections to the new cluster
sh
On 2013-11-22 13:34:18 -0800, Kevin Grittner wrote:
> Oddly, it didn't complain about creating users within a read-only
> transaction. That seems like a potential bug.
There's lots of things that escape XactReadOnly. I've thought (and I
think suggested) before that we should put in another layer
Andres Freund wrote:
> On 2013-11-22 13:07:29 -0800, Kevin Grittner wrote:
>> Andres Freund 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?
>
>
Andres Freund writes:
> On 2013-11-22 13:07:29 -0800, Kevin Grittner wrote:
>> 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
On 2013-11-22 13:07:29 -0800, Kevin Grittner wrote:
> Andres Freund 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 glo
Andres Freund 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?
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent
On 2013-11-22 12:45:25 -0800, Kevin Grittner wrote:
> Tom Lane wrote:
>
> > That looks sane for pg_dump, but I would rather have expected
> > that pg_dumpall would need to emit the same thing (possibly more
> > than once due to reconnections).
>
> I was kinda surprised myself. I changed it for
Tom Lane wrote:
> That looks sane for pg_dump, but I would rather have expected
> that pg_dumpall would need to emit the same thing (possibly more
> than once due to reconnections).
I was kinda surprised myself. I changed it for pg_dump, tested
that, and then tested pg_dumpall to get a baseline
Kevin Grittner writes:
> Kevin Grittner wrote:
>> See the attached patch.
> Trying that again.
That looks sane for pg_dump, but I would rather have expected that
pg_dumpall would need to emit the same thing (possibly more than once
due to reconnections).
regards, tom la
Kevin Grittner wrote:
> See the attached patch.
Trying that again.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Companydiff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 63a8009..199ffb0 100644
--- a/src/bin/pg_dump/pg_
Bruce Momjian wrote:
>> Not sure about backpatching. default_transaction_read_only has been
>> around since 7.4. Setting it to true would cause pg_dump to fail unless
>> you changed the database setting, and pg_dumpall would fail completely
>> as there is no way to turn off the database setting
Sending to hackers for comment; seems setting
default_transaction_read_only to true via ALTER database/user or
postgresql.conf can cause pg_dump, pg_dumpall, and pg_upgrade failures.
We are considering the right solution:
51 matches
Mail list logo
