Re: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, 2010-04-28 at 22:17 +0200, Dimitri Fontaine wrote: IMO the real fun begins when we talk about multi-slaves support and their roles (a failover slave wants the master to wait for it to have applied the WAL before to commit, a reporting slave not so much). So you'd set the Availability level on each slave and wouldn't commit on the master until each slave got what it's configured for, or something like that. Just for the record, I outlined desirable semantics for this on hackers in 2008 and want to keep those ideas on the table. http://archives.postgresql.org/pgsql-hackers/2008-07/msg01001.php My view is that it should be up to the master what happens on master. An additional standby connection should not have the ability to make transactions on the master wait. If we give control to the master rather than the standby, we are then able to allow transactions on the master choose how robust they should be, just as we do with synchronous_commit. IMHO that is extremely important, since we already know that sync rep performs poorly and applications need to mitigate that in some way. Those are the objectives, the parameters to do that are a different story and we might expect much debate. One way of doing this would be to have a parameter called synchronous_replication = N, which would cause the transaction on primary to wait for at least N standbys to reply that they have the data. This would allow settings like synchronous_commit = 0--async synchronous_commit = 1--first reply wins == max performance synchronous_commit = 2--multiple replies needed == max availability ... -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Fujii Masao wrote:
On Thu, Apr 29, 2010 at 1:14 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Ok, I've finally committed the patch, using wal_level as the name of the
GUC.
!if (InArchiveRecovery XLogRequestRecoveryConnections)
!{
!if (ControlFile-wal_level WAL_LEVEL_HOT_STANDBY)
!ereport(ERROR,
!(errmsg(recovery connections cannot
start because wal_level was not set to 'hot_standby' on the WAL source
server)));
I still have the complaint against the above check. Since the default value
of recovery_connections is TRUE, the users who need only archiving not
replication (i.e., wal_level is set to 'archive') are likely to often
see the failure
of the archive recovery by the above check.
Should we change the default to recovery_connections=off ? It is a quite
big change in default behavior over previous releases that hot standby
is enabled by default, so maybe that would be the right thing to do anyway.
Or maybe add a hint to the above, disable hot standby by setting
recovery_connections=off, or set wal_level='hot_standby' in the primary
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 10:55 +0300, Heikki Linnakangas wrote: Should we change the default to recovery_connections=off ? It is a quite big change in default behavior over previous releases that hot standby is enabled by default, so maybe that would be the right thing to do anyway. Or maybe add a hint to the above, disable hot standby by setting recovery_connections=off, or set wal_level='hot_standby' in the primary I've been waiting for this suggestion, a clear knock-on effect from your earlier changes. If we just have a static default its bad either way. The most sensible way is to make the default act intelligently depending upon what it finds in the control file. If WAL contains hot_standby info, then recovery_connections should be on by default, though can be turned off explicitly if required. If WAL does not contain hot_standby info we then we throw a WARNING and continue as if recovery_connections = off, or if recovery_connections is specified explicitly then we should throw an ERROR so that the user knows it won't be possible to use this. I think that means we'd need to change recovery_connections to have 2 or 3 values, but non-boolean: recovery_connections = auto (default) | off or recovery_connections = auto (default) | on | off and I would suggest removing it from postgresql.conf.sample -- Simon Riggs www.2ndQuadrant.com -- 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] XML Todo List
Peter Eisentraut wrote: On ons, 2010-04-28 at 15:21 +0100, Mike Fowler wrote: xpath_exists() is needed. It checks, whether or not the path specified exists in the XML value. (W/o this function we need to use weird array_dims(xpath(...)) IS NOT NULL syntax.) That sounds like a reasonable project. Is any one else working on the XML todos who might have some friendly pointers to help me on my way or am I just better off getting some code together for general review? I think you're it. :) Great, I won't be stepping on any toes! -- Mike Fowler Registered Linux user: 379787 I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it -PULP 'Glory Days' -- 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
2010/4/29 Jaime Casanova jcasa...@systemguards.com.ec: 2009/10/12 Teodor Sigaev teo...@sigaev.ru: Are you planning to submit this as a /contrib module? I haven't objections to do that, we don't planned that because we know sceptical relation of community to hints :) this could be very useful now that we have HS and we aren't able to use hash indexes on the slave so we can advice to disable those indexes there if we know that, can the planner now that too ? the only problem is that seems like we can't put plantuner.forbid_index='a_hash_index' on postgresql.conf ala auto_explain, that could make this better -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cédric Villemain -- 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] Toast rel options
On Wed, 2010-04-28 at 21:11 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: It's possible to set toast reloptions for tables that don't have toast tables at all. e.g. create table test (c1 integer); ALTER TABLE test SET (toast.autovacuum_enabled = off); Why? 1. Why not? 2. They might have toast tables later, after an ALTER ADD COLUMN for instance. Main reason is that this doesn't do anything. The toast reloptions are stored on the toast table, so if it doesn't exist then there are no reloptions. They aren't saved for later and won't be set of later add a column which causes a toast table to be added. 3. They might have had/needed a toast table in the past. Do we need to make ALTER DROP COLUMN capable of flushing those reloptions, so that they won't cause a failure at dump/reload? I don't see any advantage whatsoever to forbidding this, and a lot of corner cases to take care of if we did try to forbid it. Not required. Why do we protect against this? postgres=# alter table test set (nonexistent = on); ERROR: unrecognized parameter notexistent Also, this seems not to work? postgres=# alter table test set (my.expectation = on); ERROR: unrecognized parameter namespace my I thought we had enabled custom table options in this release? Or was that deferred, or even rejected completely? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Tom Lane wrote: Yeah. ISTM the real bottom line here is that we have only a weak grasp on how these features will end up being used; or for that matter what the common error scenarios will be. I think that for the time being we should err on the side of being permissive. We can tighten things up and add more nanny-ism in the warnings later on, when we have more field experience. Ok, here's a proposed patch. Per discussion, it relaxes the checks in pg_start/stop_backup() so that they can be used as long as wal_level = 'archive', even if archiving is disabled. If archiving is not enabled, it can't wait for the files to be archived. Instead, it prints a notice: NOTICE: WAL archiving is not enabled, you must ensure that all required WAL segments are streamed or copied through other means to restore the backup That is instead of the usual notice when archiving is enabled: NOTICE: pg_stop_backup complete, all required WAL segments have been archived -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 8200,8217 pg_start_backup(PG_FUNCTION_ARGS) errmsg(recovery is in progress), errhint(WAL control functions cannot be executed during recovery.))); ! if (!XLogArchivingActive()) ! ereport(ERROR, ! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ! errmsg(WAL archiving is not active), ! errhint(archive_mode must be enabled at server start.))); ! ! if (!XLogArchiveCommandSet()) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ! errmsg(WAL archiving is not active), ! errhint(archive_command must be defined before ! online backups can be made safely.))); backupidstr = text_to_cstring(backupid); --- 8200,8210 errmsg(recovery is in progress), errhint(WAL control functions cannot be executed during recovery.))); ! if (!XLogIsNeeded()) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ! errmsg(WAL level not sufficient for making an online backup), ! errhint(wal_level must be set to 'archive' or 'hot_standby' at server start.))); backupidstr = text_to_cstring(backupid); *** *** 8399,8409 pg_stop_backup(PG_FUNCTION_ARGS) errmsg(recovery is in progress), errhint(WAL control functions cannot be executed during recovery.))); ! if (!XLogArchivingActive()) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ! errmsg(WAL archiving is not active), ! errhint(archive_mode must be enabled at server start.))); /* * OK to clear forcePageWrites --- 8392,8402 errmsg(recovery is in progress), errhint(WAL control functions cannot be executed during recovery.))); ! if (!XLogIsNeeded()) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), ! errmsg(WAL level not sufficient for making an online backup), ! errhint(wal_level must be set to 'archive' or 'hot_standby' at server start.))); /* * OK to clear forcePageWrites *** *** 8511,8526 pg_stop_backup(PG_FUNCTION_ARGS) CleanupBackupHistory(); /* ! * Wait until both the last WAL file filled during backup and the history ! * file have been archived. We assume that the alphabetic sorting ! * property of the WAL files ensures any earlier WAL files are safely ! * archived as well. * * We wait forever, since archive_command is supposed to work and we * assume the admin wanted his backup to work completely. If you don't * wish to wait, you can set statement_timeout. Also, some notices are * issued to clue in anyone who might be doing this interactively. */ XLByteToPrevSeg(stoppoint, _logId, _logSeg); XLogFileName(lastxlogfilename, ThisTimeLineID, _logId, _logSeg); --- 8504,8530 CleanupBackupHistory(); /* ! * If archiving is enabled, wait for all the required WAL files to be ! * archived before returning. If archiving isn't enabled, the required ! * WAL needs to be transported via streaming replication (hopefully ! * with wal_keep_segments set high enough), or some more exotic ! * mechanism like polling and copying files from pg_xlog with script. ! * We have no control over those mechanisms, so it's up to the user to ! * ensure that he gets all the required WAL. ! * ! * We wait until both the last WAL file filled during backup and the ! * history file have been archived, and assume that the alphabetic ! * sorting property of the WAL files ensures any earlier WAL files are ! * safely archived as well. * * We wait forever, since archive_command is supposed to work and we * assume the admin wanted his backup to work completely. If you don't * wish to wait, you can set statement_timeout. Also, some notices are * issued to clue in anyone who
[HACKERS] Choosing between seqscan and bitmap scan
Hi! There is some strange on current CVS with correct choosing of scans. Although bitmap scan is cheaper but postgresql chooses seqscan. Test suite: CREATE OR REPLACE FUNCTION genvect() RETURNS tsvector AS $$ SELECT array_to_string( ARRAY( SELECT (random()*random()*random()*1000.0)::int::text FROM generate_series(1, 10 + (100.0*random())::bigint) ), ' ' )::tsvector; $$ LANGUAGE SQL VOLATILE; SELECT t::int4 AS id, genvect() AS ts INTO foo FROM generate_series(1, 10) AS t; CREATE INDEX foo_idx ON foo USING gin (ts); VACCUM ANALYZE foo; postgres=# explain select count(*) from foo where ts @@ '259'; QUERY PLAN --- Aggregate (cost=5817.27..5817.28 rows=1 width=0) - Seq Scan on foo (cost=0.00..5805.00 rows=4907 width=0) Filter: (ts @@ '''259'''::tsquery) (3 rows) Time: 6,370 ms postgres=# set enable_seqscan = off; SET Time: 2,014 ms postgres=# explain select count(*) from foo where ts @@ '259'; QUERY PLAN - Aggregate (cost=5767.35..5767.36 rows=1 width=0) - Bitmap Heap Scan on foo (cost=942.46..5755.08 rows=4907 width=0) Recheck Cond: (ts @@ '''259'''::tsquery) - Bitmap Index Scan on foo_idx (cost=0.00..941.24 rows=4907 width=0) Index Cond: (ts @@ '''259'''::tsquery) (5 rows) Why does pgsql choose seqscan (5817.28) instead of bitmap one (5767.36)? Changed options in postgresql.conf: shared_buffers=128MB temp_buffers=16MB work_mem=16MB maintenance_work_mem=256MB effective_cache_size=1024MB -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: On Thu, 2010-04-29 at 10:55 +0300, Heikki Linnakangas wrote: Should we change the default to recovery_connections=off ? It is a quite big change in default behavior over previous releases that hot standby is enabled by default, so maybe that would be the right thing to do anyway. Or maybe add a hint to the above, disable hot standby by setting recovery_connections=off, or set wal_level='hot_standby' in the primary I've been waiting for this suggestion, a clear knock-on effect from your earlier changes. If we just have a static default its bad either way. The most sensible way is to make the default act intelligently depending upon what it finds in the control file. If WAL contains hot_standby info, then recovery_connections should be on by default, though can be turned off explicitly if required. If WAL does not contain hot_standby info we then we throw a WARNING and continue as if recovery_connections = off, or if recovery_connections is specified explicitly then we should throw an ERROR so that the user knows it won't be possible to use this. I think that means we'd need to change recovery_connections to have 2 or 3 values, but non-boolean: recovery_connections = auto (default) | off or recovery_connections = auto (default) | on | off Seems overly complicated. It would be simpler to just set it 'off' by default. If it's 'off' by default, and you want to use hot standby, you will notice quickly that the server doesn't accept connections, and you switch it on. If it's 'on' (or 'auto'), you might not realize that your standby server is accepting connections, when you only wanted to set it up as a warm standby server for high availability. The 'auto' mode just makes it more surprising. Connections might be allowed at first, but when someone switches wal_level in the primary for some reason, your reporting standby is up, but no longer allows connections. And vice versa, if you set up a warm standby server for high availability where you don't want to allow connections, and someone flips the wal_level switch in the master, the standby suddenly starts accepting connections. I can't imagine a situation where allow connections if the WAL allows it would be a sensible setting. If there is one, we could have an 'auto' mode, but not as the default. and I would suggest removing it from postgresql.conf.sample -1. Why try to hide it? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Choosing between seqscan and bitmap scan
2010/4/29 Teodor Sigaev teo...@sigaev.ru: Hi! There is some strange on current CVS with correct choosing of scans. Also true with 8.4, default configuration. Although bitmap scan is cheaper but postgresql chooses seqscan. Test suite: CREATE OR REPLACE FUNCTION genvect() RETURNS tsvector AS $$ SELECT array_to_string( ARRAY( SELECT (random()*random()*random()*1000.0)::int::text FROM generate_series(1, 10 + (100.0*random())::bigint) ), ' ' )::tsvector; $$ LANGUAGE SQL VOLATILE; SELECT t::int4 AS id, genvect() AS ts INTO foo FROM generate_series(1, 10) AS t; CREATE INDEX foo_idx ON foo USING gin (ts); VACCUM ANALYZE foo; postgres=# explain select count(*) from foo where ts @@ '259'; QUERY PLAN --- Aggregate (cost=5817.27..5817.28 rows=1 width=0) - Seq Scan on foo (cost=0.00..5805.00 rows=4907 width=0) Filter: (ts @@ '''259'''::tsquery) (3 rows) Time: 6,370 ms postgres=# set enable_seqscan = off; SET Time: 2,014 ms postgres=# explain select count(*) from foo where ts @@ '259'; QUERY PLAN - Aggregate (cost=5767.35..5767.36 rows=1 width=0) - Bitmap Heap Scan on foo (cost=942.46..5755.08 rows=4907 width=0) Recheck Cond: (ts @@ '''259'''::tsquery) - Bitmap Index Scan on foo_idx (cost=0.00..941.24 rows=4907 width=0) Index Cond: (ts @@ '''259'''::tsquery) (5 rows) Why does pgsql choose seqscan (5817.28) instead of bitmap one (5767.36)? Changed options in postgresql.conf: shared_buffers=128MB temp_buffers=16MB work_mem=16MB maintenance_work_mem=256MB effective_cache_size=1024MB -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cédric Villemain -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 12:55 +0300, Heikki Linnakangas wrote: Seems overly complicated. If you turn wal_level = hot_standby in the master, then the slaves work, unless you say otherwise. What is complicated about that? It would be simpler to just set it 'off' by default. As a parameter, yes, it is simpler. The behaviour is not simplified by doing that. I care about the behaviour. If it's 'off' by default, and you want to use hot standby, you will notice quickly that the server doesn't accept connections, and you switch it on. If it's 'on' (or 'auto'), you might not realize that your standby server is accepting connections, when you only wanted to set it up as a warm standby server for high availability. That requires we hit the problem, alter the parameter and restart. We don't need to do that at all. It could Just Work. The 'auto' mode just makes it more surprising. Connections might be allowed at first, but when someone switches wal_level in the primary for some reason, your reporting standby is up, but no longer allows connections. If you don't do this, what would happen on standby? Does it silently stop applying changes after the point you turned it off, or does it throw an ERROR. And vice versa, if you set up a warm standby server for high availability where you don't want to allow connections, and someone flips the wal_level switch in the master, the standby suddenly starts accepting connections. Some people would regard that as a good thing. The whole point of wal_level discussion was to avoid needing multiple switches to turn something on. You are proposing exactly that here. Why is this discussion different? Why should we not be able to set in just one place. If that behaviour concerns the DBA then they can turn it off explicitly. I can't imagine a situation where allow connections if the WAL allows it would be a sensible setting. If there is one, we could have an 'auto' mode, but not as the default. I can. Simple, obvious behaviour. Turn it on in the master, works on the standbys. We want people to use the feature, not fumble with it. Just think auto means linked to master. If you don't like it you can turn it off. -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: The whole point of wal_level discussion was to avoid needing multiple switches to turn something on. No, the point of wal_level was to make the behavior easier to understand, by uncoupling the level of WAL-logging from various other switches, controling it directly and explicitly with a new GUC instead. It added a new switch, but made the system as a whole easier to understand and configure. I can't imagine a situation where allow connections if the WAL allows it would be a sensible setting. If there is one, we could have an 'auto' mode, but not as the default. I can. Simple, obvious behaviour. Turn it on in the master, works on the standbys. Yes, but when would you want that? Here's the use cases I can think of: purpose of the standby - do you want hot standby or not? reporting - yes offloading queries from master - yes warm standby for high availability - no offloading taking filesystem-level backups from master - no offloading pg_dump from master - yes All of those either want hot standby, or don't. What use case is there for enabled, if the required information is in the WAL? If there is one, it sure doesn't seem like the most common one. When you just want hot standby to be on or off, it's weird to control that from the master. Action at a distance. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 5:38 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: NOTICE: WAL archiving is not enabled, you must ensure that all required WAL segments are streamed or copied through other means to restore the backup I might think about dropping the words through other means from this sentence. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 5:55 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The most sensible way is to make the default act intelligently depending upon what it finds in the control file. If WAL contains hot_standby info, then recovery_connections should be on by default, though can be turned off explicitly if required. If WAL does not contain hot_standby info we then we throw a WARNING and continue as if recovery_connections = off, or if recovery_connections is specified explicitly then we should throw an ERROR so that the user knows it won't be possible to use this. I think that means we'd need to change recovery_connections to have 2 or 3 values, but non-boolean: recovery_connections = auto (default) | off or recovery_connections = auto (default) | on | off Seems overly complicated. It would be simpler to just set it 'off' by default. I kind of agree with Simon on this one, except I would probably choose to have just on and off and make on work like his auto. In other words, recovery_connections=on means, give me recovery connections if possible, otherwise don't worry about it. I'd rather not have to change the default to recovery_connections=off - that's one more parameter someone has to set properly. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 13:46 +0300, Heikki Linnakangas wrote: purpose of the standby - do you want hot standby or not? reporting - yes offloading queries from master - yes offloading pg_dump from master - yes These would work using the proposed default. warm standby for high availability - no offloading taking filesystem-level backups from master - no These can be explicitly turned off. You're presuming there is benefit in turning recovery_connections = off, though it is perfectly valid to do those use cases with it on. There are many ways to control connections, not just that switch. It will certainly be easier to monitor the HA system by running queries against it than not. Do you have any evidence there is benefit in the *typical* case for turning the setting off? All of those either want hot standby, or don't. What use case is there for enabled, if the required information is in the WAL? If there is one, it sure doesn't seem like the most common one. I think I want it to just work is fairly common. When you just want hot standby to be on or off, it's weird to control that from the master. Action at a distance. That's the proposed default, not the only control. The standby can override what the master says if it definitely doesn't want it, but is smart enough to avoid silly configuration errors. Turning off wal_level on the master *does* affect the standby, so pretending we have a completely separate configuration option makes no sense for me. Robert's point when he raised the wal_level discussion was about reducing the number of parameter settings required to make this work. An intelligent default will reduce level of configuration and allow us to make Hot Standby work on the standby, out of the box, and that is worth having. -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas wrote: I kind of agree with Simon on this one, except I would probably choose to have just on and off and make on work like his auto. In other words, recovery_connections=on means, give me recovery connections if possible, otherwise don't worry about it. If you're setting up a reporting server, and hot standby can't start, the server is not functioning properly. I would like to get an error in that case. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 7:19 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: I kind of agree with Simon on this one, except I would probably choose to have just on and off and make on work like his auto. In other words, recovery_connections=on means, give me recovery connections if possible, otherwise don't worry about it. If you're setting up a reporting server, and hot standby can't start, the server is not functioning properly. I would like to get an error in that case. Presumably you will actually try connecting to it, no? And what happens when someone changes the setting on the master from hot_standby back to archive? I'd rather have the reporting server continue recovery without being able to accept connections rather than die in its tracks. I think this is a problem that should be solved by monitoring, rather than by automatically taking the server down. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: On Thu, 2010-04-29 at 13:46 +0300, Heikki Linnakangas wrote: warm standby for high availability - no offloading taking filesystem-level backups from master - no These can be explicitly turned off. You're presuming there is benefit in turning recovery_connections = off, though it is perfectly valid to do those use cases with it on. There are many ways to control connections, not just that switch. It will certainly be easier to monitor the HA system by running queries against it than not. Do you have any evidence there is benefit in the *typical* case for turning the setting off? It depends on your exact configuration, but one typical one is that you have a work-balancing router or pgbouncer sitting in front of the servers, directing traffic to the server that's up and running. If the standby starts accepting connections prematurely, the clients will be incorrectly routed to the standby server and update operations will fail (and SELECTs will return slightly delayed data). All of those either want hot standby, or don't. What use case is there for enabled, if the required information is in the WAL? If there is one, it sure doesn't seem like the most common one. I think I want it to just work is fairly common. You need quite a bit of set up anyway, flipping one more GUC hardly makes a difference. There is less risk of oversight and accidental misconfiguration if the admin makes a conscious decision to turn it on. I'd like to scaremonger with the following fictional story: An administrator sets up two PostgreSQL servers in a high availability warm standby set up. Clients are set up to try to connect to both servers, so that when failover happens, they will automatically reconnect to the remaining server. wal_level is set to 'archive' in the master, and all is well. After running successfully for six months in production, a reporting server is introduced to offload heavy queries from the mission-critical OLTP server. wal_level is set to 'hot_standby' in the master to allow read-only queries to be run against the reporting server, and the reporting server is set up using the same WAL archive used for the warm standby server. All seems to be running well, the admin logs in to the application and clicks through a few screens to test it. A few hours later a user rings and complains that he's getting a cannot execute INSERT in a read-only transaction error. What happened, and why does it work just fine when the admin tries the same? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 14:55 +0300, Heikki Linnakangas wrote: I'd like to scaremonger Seems so. recovery_connections was on by default and unanimous agreement until recently and I don't want to change that now, just because a change somewhere else appears to be forcing that but need not be so. It was sensible to add a switch to turn HS off, but it should not be the default, especially not one that requires a restart to enable a high availability feature. That is important in a feature that takes a while to kick-in and so the user may patiently wait for it to come up and it never does. I have no wish to repeat the situation that PostgreSQL requires a restart to enable a feature, while other forks retain the ability to enable the parameter without restart, as occurs with archive_mode, IIRC. Perhaps we should not re-think wal_level if we've just moved the parameter problem somewhere else? -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: recovery_connections was on by default and unanimous agreement until recently and I don't want to change that now, just because a change somewhere else appears to be forcing that but need not be so. We never really had that discussion, until now. It has always been 'on' by default, and that has been useful to get more testing during the development cycle, but it's not clear that's a good default for real-life usage. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 7:55 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: What happened, I don't find that story very compelling because there are an infinite number of ways to have high-availability not work if you start by supposing that the person who sets them up doesn't test them properly and verify that everything actually works as expected. You could do all sorts of things wrong in that case. How about this one? The administrator sets up a master and a slave. She's heard about this new Hot Standby feature and so decides to enable it on the slave just to play around with it. Subsequently, she takes a better job at another company and they hire a new administrator, who thinks the Hot Standby WAL may be causing a performance problem on the master, so he switches wal_mode to archive. Six months later the primary fails. I think you can construct a scenario where just about any default setting causes a problem, but I like the idea of having this enabled by default, and I think it works fine if you just treat the case where recovery_connections=on but wal_mode=archive as a LOG or WARNING rather than an ERROR. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas wrote: On Thu, Apr 29, 2010 at 7:19 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: I kind of agree with Simon on this one, except I would probably choose to have just on and off and make on work like his auto. In other words, recovery_connections=on means, give me recovery connections if possible, otherwise don't worry about it. If you're setting up a reporting server, and hot standby can't start, the server is not functioning properly. I would like to get an error in that case. Presumably you will actually try connecting to it, no? Sure. I guess it would be acceptable if 'on' meant 'on, if possible', as long as 'off' is the default. Otherwise it's too surprising. And what happens when someone changes the setting on the master from hot_standby back to archive? I'd rather have the reporting server continue recovery without being able to accept connections rather than die in its tracks. As the code stands, if wal_level is switched from 'hot_standby' to 'archive' in the primary, and it's restarted, the standby will die. There is currently no way to stop to accepting read-only connections once they're allowed. It could restart with connections disallowed, but it needs a restart. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas wrote: How about this one? The administrator sets up a master and a slave. She's heard about this new Hot Standby feature and so decides to enable it on the slave just to play around with it. Subsequently, she takes a better job at another company and they hire a new administrator, who thinks the Hot Standby WAL may be causing a performance problem on the master, so he switches wal_mode to archive. Six months later the primary fails. Umm, I don't see the problem. For high availability purposes, the standby works just as well with wal_mode='archive'. Or are you saying that the standby was configured with recovery_connections='on', and failed to start for those six months because hot standby could not be enabled, and no-one noticed? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 9:00 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: On Thu, Apr 29, 2010 at 7:19 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: I kind of agree with Simon on this one, except I would probably choose to have just on and off and make on work like his auto. In other words, recovery_connections=on means, give me recovery connections if possible, otherwise don't worry about it. If you're setting up a reporting server, and hot standby can't start, the server is not functioning properly. I would like to get an error in that case. Presumably you will actually try connecting to it, no? Sure. I guess it would be acceptable if 'on' meant 'on, if possible', as long as 'off' is the default. Otherwise it's too surprising. I disagree. I think on should mean 'on, if possible' and 'on' should be the default. I think that's how it was before this round of changes - but maybe I'm mistaken? It seems like it makes sense, at any rate. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 9:07 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: How about this one? The administrator sets up a master and a slave. She's heard about this new Hot Standby feature and so decides to enable it on the slave just to play around with it. Subsequently, she takes a better job at another company and they hire a new administrator, who thinks the Hot Standby WAL may be causing a performance problem on the master, so he switches wal_mode to archive. Six months later the primary fails. Umm, I don't see the problem. For high availability purposes, the standby works just as well with wal_mode='archive'. Or are you saying that the standby was configured with recovery_connections='on', and failed to start for those six months because hot standby could not be enabled, and no-one noticed? Yep. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas robertmh...@gmail.com writes: On Thu, Apr 29, 2010 at 7:19 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: If you're setting up a reporting server, and hot standby can't start, the server is not functioning properly. I would like to get an error in that case. Presumably you will actually try connecting to it, no? [...] I think this is a problem that should be solved by monitoring, rather than by automatically taking the server down. +1 on both counts. Is that a +2? Regards, -- dim -- 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] Choosing between seqscan and bitmap scan
Teodor Sigaev teo...@sigaev.ru writes: [ planner prefers ] - Seq Scan on foo (cost=0.00..5805.00 rows=4907 width=0) to - Bitmap Heap Scan on foo (cost=942.46..5755.08 rows=4907 width=0) Why does pgsql choose seqscan (5817.28) instead of bitmap one (5767.36)? There's a fuzz factor of (IIRC) 1% in path cost comparisons. It's deciding that the seqscan and bitmapscan total costs are not meaningfully different; then since the startup costs *are* meaningfully different, it's making the choice on the basis of cheaper startup cost. regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com writes: recovery_connections was on by default and unanimous agreement until recently and I don't want to change that now, Uh, it was on by default only because a lot of us hadn't noticed that. I agree with Heikki's position here: it should not be on by default. regards, tom lane -- 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] Choosing between seqscan and bitmap scan
On Thu, 29 Apr 2010, Tom Lane wrote: Teodor Sigaev teo...@sigaev.ru writes: [ planner prefers ] - Seq Scan on foo (cost=0.00..5805.00 rows=4907 width=0) to - Bitmap Heap Scan on foo (cost=942.46..5755.08 rows=4907 width=0) Why does pgsql choose seqscan (5817.28) instead of bitmap one (5767.36)? There's a fuzz factor of (IIRC) 1% in path cost comparisons. It's deciding that the seqscan and bitmapscan total costs are not meaningfully different; then since the startup costs *are* meaningfully different, it's making the choice on the basis of cheaper startup cost. hmm, what if we add index scan preference inside 1% tolerance ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Choosing between seqscan and bitmap scan
Oleg Bartunov o...@sai.msu.su writes: On Thu, 29 Apr 2010, Tom Lane wrote: There's a fuzz factor of (IIRC) 1% in path cost comparisons. It's deciding that the seqscan and bitmapscan total costs are not meaningfully different; then since the startup costs *are* meaningfully different, it's making the choice on the basis of cheaper startup cost. hmm, what if we add index scan preference inside 1% tolerance ? Why? IMO this behavior is perfectly reasonable; in fact I've sometimes thought the fuzz threshold should be a lot more than 1%. There is no reason for the planner to believe that the bitmapscan is meaningfully superior on total cost, while it is clearly inferior on startup cost. If your problem is that the seqscan is a lot slower in reality, the answer to that is to twiddle the cost parameters so that the planner knows that, not to object to this logic. regards, tom lane -- 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] Toast rel options
Simon Riggs wrote: Also, this seems not to work? postgres=# alter table test set (my.expectation = on); ERROR: unrecognized parameter namespace my I thought we had enabled custom table options in this release? Or was that deferred, or even rejected completely? IIRC you can define your own parameter namespaces for access methods you define, but you can't add namespaces to hardcoded AMs. Possibly not a very useful definition (because you don't really define new AMs all that frequently), but it covers what was needed at the time. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Toast rel options
Simon Riggs wrote: Why do we protect against this? postgres=# alter table test set (nonexistent = on); ERROR: unrecognized parameter notexistent Eh? The parameter doesn't exist. It's not exactly the same as defining a value for a parameter that exists but is unused. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 09:48 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: recovery_connections was on by default and unanimous agreement until recently and I don't want to change that now, Uh, it was on by default only because a lot of us hadn't noticed that. I agree with Heikki's position here: it should not be on by default. We're talking about the case where somebody has set up a standby database. It's not like they happen on this accidentally. * HS on by default, in the standby, via recovery_connections. * HS off by default, in the master, via wal_level. Overall, that *is* off by default. (Note: I said nothing about that). We don't need it off *twice*, nor do we even need two switches. Last week we had one switch and it was on by default, now we're looking at two switches and off by default. I haven't yet heard a good reason for the change being proposed here by Heikki. The use cases are rare, if they truly exist at all. Monitoring the standby is much easier with HS on, for example. What is the reason for the *extra* off switch? Why two? Why off twice? -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
* Simon Riggs si...@2ndquadrant.com [100429 11:24]: What is the reason for the *extra* off switch? Why two? Why off twice? Because I run a PITR slave off a WAL archive... And I'm going to continue to try and use this setup... I expect to try and experiment with wal-streaming too when everything is up to 9, and be a little upset when the old faithfull backup I've got and am betting my job on suddenly starts acting differentlyl, or accepting connections and causing my db clients to start thowing errors because I'm trying to get a streaming replication w/ hot standby up on a *different* slave... It's all about the path of least *suprise*. My least suprise would have been that a similar config I have now with my PITR slaves would pretty much still work, and not suddenly start accepting connections, and even worse, at some later date when I've already verified that it was doing what I expected with the configuration. There has got to be *something* that tells me it is trying to allow connectins during recovery, but failing... And i think that just loging a WARNING and continuing isn't enough, because I'll admit to having upgraded PG and just testing it, not looking thoroughly through the logs for any new messages that might peak my interest... That said, since I follow -hackers, that something could be considered this email thread, and I know I will be extra careful about my deployment of PITR slaves w/ 9 wrt making sure I'm explicit in all the settings I can find... And I'll make sure I look more carefully at logs when deploying 9 as well ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: On Thu, 2010-04-29 at 09:48 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: recovery_connections was on by default and unanimous agreement until recently and I don't want to change that now, Uh, it was on by default only because a lot of us hadn't noticed that. I agree with Heikki's position here: it should not be on by default. We're talking about the case where somebody has set up a standby database. It's not like they happen on this accidentally. * HS on by default, in the standby, via recovery_connections. * HS off by default, in the master, via wal_level. Overall, that *is* off by default. (Note: I said nothing about that). We don't need it off *twice*, nor do we even need two switches. wal_level is not supposed to control if Hot Standby is on or off. It controls what information is written to WAL. If you have wal_level='archive' and recovery_connections='on' in the standby, that's a configuration error, just like setting wal_level='minimal' and archive_mode='on'. You wanted to enable Hot Standby, but the information required isn't in the WAL. It's error-prone to control what happens in the standby from the master. That's the action at a distance effect I mentioned earlier. The master should be configured in the master, and each standby should configured in the standby. The right mental model is that wal_mode controls what is written to the WAL. In fact, I might recommend always setting it to 'hot_standby' instead of 'archive', even if you have no standbys and you're only doing WAL archival - it's a lot easier to do PITR with hot standby. To control whether hot standby is enabled in the standby, use recovery_connections. And I'd prefer it to be off by default because off is the safer option. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 18:40 +0300, Heikki Linnakangas wrote: It's error-prone to control what happens in the standby from the master. That's the action at a distance effect I mentioned earlier. The master should be configured in the master, and each standby should configured in the standby. Repeating the same thing when its been refuted doesn't help. What you say has not been proposed. If there is a case for HS-off-by-default, make it. If you want to change code, arguing directly against your own position, mentioned many times, we need a reason. How else can we know which argument of yours to believe? -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 11:37 -0400, Aidan Van Dyk wrote: * Simon Riggs si...@2ndquadrant.com [100429 11:24]: What is the reason for the *extra* off switch? Why two? Why off twice? Because I run a PITR slave off a WAL archive... And I'm going to continue to try and use this setup... I expect to try and experiment with wal-streaming too when everything is up to 9, and be a little upset when the old faithfull backup I've got and am betting my job on suddenly starts acting differentlyl, or accepting connections and causing my db clients to start thowing errors because I'm trying to get a streaming replication w/ hot standby up on a *different* slave... It's all about the path of least *suprise*. My least suprise would have been that a similar config I have now with my PITR slaves would pretty much still work, and not suddenly start accepting connections, and even worse, at some later date when I've already verified that it was doing what I expected with the configuration. If people upgrade to 9.0 and then are surprised that the features listed are actually available, I too would be surprised. If we all believe that these radical surprises are a problem, then we should also turn off join removal and loads of other features in 9.0 that will also cause surprises. HS is the most requested feature across multiple polls and *not* being able to connect to it is likely to come as a huge surprise to many people. HS is just as secure as the main database. There is no big use case for run with HS turned off. Everybody wants it on, as long as it works and don't cause problems. So far, there is no evidence to make anyone think that it would and I wish to avoid that implication. Heikki previously argued strongly against having any switch at all, so it could be turned on all the time. Nothing's changed. -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
* Simon Riggs si...@2ndquadrant.com [100429 12:06]: Repeating the same thing when its been refuted doesn't help. What you say has not been proposed. If there is a case for HS-off-by-default, make it. If you want to change code, arguing directly against your own position, mentioned many times, we need a reason. How else can we know which argument of yours to believe? I'm not against HS being on-by-default.But if it is, and the WAL it's consuming doesn't have the HS-records by default, then I want PG to consider that a problem, make sure I absolutely know it's a problem... I agree with Heikki that the action-at-a-distance of HS trying-to-work-but-maybe-not-this-time-depending-on-the-master is an undesirable state... Like everything else in PG, I'ld like it to work completely, or tell me there is a problem. That said, I'ld probalby be happy with PG 9 having a default config of: wal_mode = hot_standby recovery_connections = on Make it set to generate enough WAL and actually do recovery connections. But also make the recover_connections boolean really mean what it s called. It's not called try_recovery_connections a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: On Thu, 2010-04-29 at 18:40 +0300, Heikki Linnakangas wrote: It's error-prone to control what happens in the standby from the master. That's the action at a distance effect I mentioned earlier. The master should be configured in the master, and each standby should configured in the standby. Repeating the same thing when its been refuted doesn't help. What you say has not been proposed. I was responding to your mail where you said that there is two settings for turning hot standby off, and asking why we need two. What I'm saying is that you shouldn't think of wal_level as a setting to turn hot standby on or off. It would be error-prone to control that from the master. So there is only one setting to turn hot standby on/off, recovery_connections in the standby. If there is a case for HS-off-by-default, make it. If you want to change code, arguing directly against your own position, mentioned many times, we need a reason. How else can we know which argument of yours to believe? Now you lost me. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
* Simon Riggs si...@2ndquadrant.com [100429 12:14]: HS is the most requested feature across multiple polls and *not* being able to connect to it is likely to come as a huge surprise to many people. HS is just as secure as the main database. There is no big use case for run with HS turned off. Everybody wants it on, as long as it works and don't cause problems. So far, there is no evidence to make anyone think that it would and I wish to avoid that implication. Heikki previously argued strongly against having any switch at all, so it could be turned on all the time. Nothing's changed. I'm not arguing against having it on by default. What I'm against (and that's strong, it should probably be prefer not to have) is having it configured on, but having it not work. If it's been configured to run in a state it can't, I would prefer it didn't run, not that it ran, but in a slightly different state... But I know that's just a preference... And one from an old-school unix admin too... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 12:17 PM, Aidan Van Dyk ai...@highrise.ca wrote: * Simon Riggs si...@2ndquadrant.com [100429 12:06]: Repeating the same thing when its been refuted doesn't help. What you say has not been proposed. If there is a case for HS-off-by-default, make it. If you want to change code, arguing directly against your own position, mentioned many times, we need a reason. How else can we know which argument of yours to believe? I'm not against HS being on-by-default. But if it is, and the WAL it's consuming doesn't have the HS-records by default, then I want PG to consider that a problem, make sure I absolutely know it's a problem... Nobody is proposing otherwise. What Simon and I are proposing is that if the master is configured to support HS, it comes up on the slave by default without requiring additional configuration. Now maybe that's too much spooky action at a distance, but I suspect it IS the behavior most people will want. If Tom and Heikki get their way and change the default behavior, it'll just mean (nearly) everyone flips one extra configuration switch. I agree with Heikki that the action-at-a-distance of HS trying-to-work-but-maybe-not-this-time-depending-on-the-master is an undesirable state... Like everything else in PG, I'ld like it to work completely, or tell me there is a problem. That said, I'ld probalby be happy with PG 9 having a default config of: wal_mode = hot_standby recovery_connections = on Make it set to generate enough WAL and actually do recovery connections. That would be a bad idea - there's a significant performance penalty from setting wal_level to anything other than minimal (just as there is for turning on archive_mode in 8.4). But also make the recover_connections boolean really mean what it s called. It's not called try_recovery_connections Well, sure. But setting work_mem to 1GB doesn't force the planner to use a gigabyte of memory for every sort, either. It just gives permission. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com wrote: There is no big use case for run with HS turned off. There was mention of running the live database and a warm standby, with a connection pooler pointed to both so that it could automatically reconnect on failover. Everybody wants it on, as long as it works and don't cause problems. I don't see any immediate use cases for our shop, unless it is the only way to get WAL copied to our backup server through SR. Franky, I'd much rather have a WAL receiver which assembled the WAL file segments (at the archive level of logging) as they arrived and then fed them to our warm standby clusters. (Of course, I've never claimed to have a typical environment.) I would be uncomfortable with a default of auto for a replica to allow connections based on WAL file contents. If someone explicitly sets it to auto, then it's up to them to understand the implications. IMO, If someone sets it to on, they should get a highly visible failure if it can't run in that mode. -Kevin -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: On Thu, 2010-04-29 at 11:37 -0400, Aidan Van Dyk wrote: It's all about the path of least *suprise*. My least suprise would have been that a similar config I have now with my PITR slaves would pretty much still work, and not suddenly start accepting connections, and even worse, at some later date when I've already verified that it was doing what I expected with the configuration. If people upgrade to 9.0 and then are surprised that the features listed are actually available, I too would be surprised. Being available is not the same as being on by default. There is no big use case for run with HS turned off. Everybody wants it on, as long as it works and don't cause problems. So far, there is no evidence to make anyone think that it would and I wish to avoid that implication. The use case I explained earlier exists, the one with a master and a warm standby server with pgbouncer in front. And Aidan and me are human beings, included in everybody. You know that there is cases where it causes problems in the standby, even ignoring the possibility of bugs. For example, if you increase max_connections in the master, the standby will abort. It's safer to run with recovery_connections off if you don't need the feature. Heikki previously argued strongly against having any switch at all, so it could be turned on all the time. Nothing's changed. I argued that we should always WAL-log the information required to enable hot standby, on the grounds that the overhead is small. I.e. that wal_level would be a two-state switch, either 'minimal' or 'hot_standby'. But I'm happy with how that is now. But that's not what we're discussing now, don't confuse things. We're talking about recovery_connections, not wal_level. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas robertmh...@gmail.com writes: Nobody is proposing otherwise. What Simon and I are proposing is that if the master is configured to support HS, it comes up on the slave by default without requiring additional configuration. Now maybe that's too much spooky action at a distance, but I suspect it IS the behavior most people will want. If Tom and Heikki get their way and change the default behavior, it'll just mean (nearly) everyone flips one extra configuration switch. We already bought into the one extra switch penalty when we agreed to invent a separate wal_level parameter. The entire point of that was to have more, but simpler-to-understand, parameters with fewer hidden interactions. Arguing that there are now too many knobs to twiddle amounts to trying to revisit that discussion, which we don't have time for now. That said, I'ld probalby be happy with PG 9 having a default config of: wal_mode = hot_standby recovery_connections = on That would be a bad idea - there's a significant performance penalty from setting wal_level to anything other than minimal (just as there is for turning on archive_mode in 8.4). There is not only a performance penalty, but a reliability penalty. Enabling these switches turns on a whole lot of code that, with all due respect to those who have worked on it, is absolutely positively guaranteed to be full of bugs. Not all of which are going to be flushed out during beta. If we ship 9.0 with these things on by default, it will result in an immediate reliability downgrade for installations that are simply doing what they did before and not even interested in HS/SR. Maybe by 9.1 or 9.2 it would be sensible to have some of this code turned on by default. But it is absolutely not in keeping with this project's mindset or historical practice to enable it by default now. regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 11:48 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: There is no big use case for run with HS turned off. There was mention of running the live database and a warm standby, with a connection pooler pointed to both so that it could automatically reconnect on failover. pgpool already copes by design and has been working against HS for months. If the connection pooler doesn't have some way of knowing the server is a standby, then it will try to connect and fail, which presumably it would retry. This seems like a hypothetical connection pooler rather than a real one, to me. HS can continue to be used with people's existing Warm Standby setup, just now you can read the database as well. Which is what everybody has requested. -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com wrote: Which is what everybody has requested. You continue to use the term everybody rather loosely. I don't begrudge people features they want, even when *I* don't need them; but please don't take my lack of argument against adding this feature as a silent request for it. SR has a place in our shop, especially if it can create traditional WAL file segments on the receiving end. (I understand that will not be a built-in feature for 9.0, but perhaps there will be a pgfoundry project or some such.) HS is no use to us, and I would rather not pay a performance penalty or take the risks of exercising complex new code paths because others need it. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] s/recovery_connections/allow_standby_queries/, or something like that?
I've just realized that one of the confusing things about this debate is that the recovery_connections parameter is very confusingly named. It might have been okay when HS existed in isolation, but with SR in the mix, it's not at all clear that the parameter refers to client connections made to a standby server, and not to replication connections made from a standby to its master. It is easy to think that this is a parameter that needs to be turned on in the master to allow standby slaves to connect to it. Another problem is that it looks more like an integer parameter (ie, maximum number of such connections) than a boolean. I think a different name would help. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. Comments? regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 1:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: That said, I'ld probalby be happy with PG 9 having a default config of: wal_mode = hot_standby recovery_connections = on That would be a bad idea - there's a significant performance penalty from setting wal_level to anything other than minimal (just as there is for turning on archive_mode in 8.4). There is not only a performance penalty, but a reliability penalty. Enabling these switches turns on a whole lot of code that, with all due respect to those who have worked on it, is absolutely positively guaranteed to be full of bugs. Not all of which are going to be flushed out during beta. If we ship 9.0 with these things on by default, it will result in an immediate reliability downgrade for installations that are simply doing what they did before and not even interested in HS/SR. This is a pretty good argument, and Heikki's argument just upthread that a mismatched max_connections setting could bollix things is an even better one. So I'm now changing my mind and thinking this should be off by default, also. ...Robert -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
On Thu, Apr 29, 2010 at 1:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've just realized that one of the confusing things about this debate is that the recovery_connections parameter is very confusingly named. It might have been okay when HS existed in isolation, but with SR in the mix, it's not at all clear that the parameter refers to client connections made to a standby server, and not to replication connections made from a standby to its master. It is easy to think that this is a parameter that needs to be turned on in the master to allow standby slaves to connect to it. Another problem is that it looks more like an integer parameter (ie, maximum number of such connections) than a boolean. I think a different name would help. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. Comments? I agree that name is better. It would also be nice if the name of that GUC matched the value that must be set for wal_level as closely as possible. ...Robert -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
Tom Lane t...@sss.pgh.pa.us wrote: It is easy to think that this is a parameter that needs to be turned on in the master to allow standby slaves to connect to it. I think a different name would help. Yeah, I had to double-check because the current name wasn't, in itself, unambiguous. We'd be sure to get many posts from confused users on this if we don't rename it. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. That seems clear and self-explanatory to me. -Kevin -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
Tom Lane wrote: I think a different name would help. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. Comments? One objection to that name is that it also works during archive recovery, like during PITR, which is not a standby server. But that's probably a rare use case. +1 on changing it to something. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 13:03 -0400, Tom Lane wrote: There is not only a performance penalty I've asked for evidence that recovery is any slower as a result of HS. If we had some, I'm guessing we'd be tuning rather than discussing this. Enabling these switches turns on a whole lot of code that, with all due respect to those who have worked on it, is absolutely positively guaranteed to be full of bugs. I understand that view and don't take offence at all, thank you for your concern. I wish to take seriously the possibility you may be correct, so I want to squash unfounded rumours that will scare people into keeping HS turned off, because that will keep the bugs in, rather than flush them out so we can fix them. Can we keep the default=on during beta and collect evidence before making a final decision at release? -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Aidan Van Dyk wrote: It's all about the path of least *suprise*. My least suprise would have been that a similar config I have now with my PITR slaves would pretty much still work, and not suddenly start accepting connections, and even worse, at some later date when I've already verified that it was doing what I expected with the configuration. The first time I setup a system to test HS, when I got to the point where the slave was up and running as a standard warm standby, I expected there to be something else I had to do in order for it to be available for queries. When I fired up psql and I was able to run queries without doing anything extra, I was surprised--but it was that fun, everything just works when I expected it to be harder than that kind of surprise. One of the reasons the version number was bumped up to 9.0 was to put people on warning that they should not assume their old setups would port forward without behavioral changes. The fact that existing warm-standby server users will be surprised to find they can run queries without doing anything special could be considered under that banner. If you feel that's not obvious enough, that could argue for more prominent documentation of that fact, rather than turning it off. The idea that it should be made harder to enable just to protect the expectations current users, and therefore introduce yet another place where PostgreSQL is less friendly to get started with than it could be, is backwards from the perspective of making things as easy as possible for new users. Arguing from a usability standpoint needs to consider both new and existing user requirements, and those are quite opposed to one another in terms of what default makes more sense IMHO. Now, if the argument is from the perspective of this adds performance/reliability issues that weren't there before, and those go away if the feature is disabled by default, that's a respectable and indisputable reason to do so. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 13:03 -0400, Tom Lane wrote: There is not only a performance penalty, but a reliability penalty. Enabling these switches turns on a whole lot of code that, with all due respect to those who have worked on it, is absolutely positively guaranteed to be full of bugs. Not all of which are going to be flushed out during beta. If we ship 9.0 with these things on by default, it will result in an immediate reliability downgrade for installations that are simply doing what they did before and not even interested in HS/SR. Maybe by 9.1 or 9.2 it would be sensible to have some of this code turned on by default. But it is absolutely not in keeping with this project's mindset or historical practice to enable it by default now. regards, tom lane +1 Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
On Thu, Apr 29, 2010 at 1:38 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Tom Lane wrote: I think a different name would help. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. Comments? One objection to that name is that it also works during archive recovery, like during PITR, which is not a standby server. But that's probably a rare use case. Gee, I think of it as a standby regardless of whether it's created using archiving or SR. Do we not have a generic term that covers both cases? +1 on changing it to something. Boy, my blog post is going to have a darn short shelf life (see the last paragraph). http://rhaas.blogspot.com/2010/04/write-ahead-logging-in-postgresql-90.html ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 12:16 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: Which is what everybody has requested. You continue to use the term everybody rather loosely. Sorry, that was imprecise, I didn't mean to exaggerate. -- Simon Riggs www.2ndQuadrant.com -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: I think a different name would help. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. Comments? One objection to that name is that it also works during archive recovery, like during PITR, which is not a standby server. But that's probably a rare use case. Huh, that is an interesting point. I think it might eventually be a common use case: when you're trying to determine where to stop a PITR recovery, it would be really nice to be able to roll forward to some point, pause the recovery, and then snoop around in the database in a read-only fashion before deciding whether to advance further. We don't currently have a good mechanism for the pause-and-resume bit but I bet something like walreceiver could be built to do that. The snoop around part is already handled nicely by HS. +1 on changing it to something. Any thoughts on what? regards, tom lane -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
On Thu, 2010-04-29 at 13:18 -0400, Tom Lane wrote: I've just realized that one of the confusing things about this debate is that the recovery_connections parameter is very confusingly named. It might have been okay when HS existed in isolation, but with SR in the mix, it's not at all clear that the parameter refers to client connections made to a standby server, and not to replication connections made from a standby to its master. It is easy to think that this is a parameter that needs to be turned on in the master to allow standby slaves to connect to it. Another problem is that it looks more like an integer parameter (ie, maximum number of such connections) than a boolean. I think a different name would help. The best idea I can come up with on the spur of the moment is allow_standby_queries, but I'm not sure that can't be improved on. Comments? enable_standby_queries (nitpicky but it seems more appropriate) Joshua D. Drake regards, tom lane -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: Can we keep the default=on during beta and collect evidence before making a final decision at release? That's tempting to get more (inadvertent) testing of HS, but I don't think it would be fair to the beta testers. The expectation is that what's in beta is what's in the release, unless some new issue pops up. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com wrote: I've asked for evidence that recovery is any slower as a result of HS. Can you quantify the impact on the number of bytes written to WAL when switching from the archiving level to the hot standby level? -Kevin -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 19:55 +0300, Heikki Linnakangas wrote: And Aidan and me are human beings, included in everybody. Yes, that was too loose. I dislike that kind of argument and I'm sorry that slipped in here. You know that there is cases where it causes problems in the standby, even ignoring the possibility of bugs. For example, if you increase max_connections in the master, the standby will abort. That behaviour was suggested by you. I don't think its anywhere near necessary that it does that and would like to remove that restriction. The likelihood we'll ever run out of slots is small even with large increases in max_connections. Example: if we increase from 100 to 500 we'd only hit the limit if we had all 500 connections on the master simultaneously running write transactions with an average 12 subtransactions each. It's safer to run with recovery_connections off if you don't need the feature. Presumably your advice is also that people should not run with Streaming Replication if they don't need that feature? And that we should also have an enable_joinremoval flag so the risk it poses can be minimized? You didn't argue this way with regard to vacuum/FSM in 8.4, which was much more critical; we're just talking about a standby server. -- Simon Riggs www.2ndQuadrant.com -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
Tom Lane wrote: Huh, that is an interesting point. I think it might eventually be a common use case: when you're trying to determine where to stop a PITR recovery, it would be really nice to be able to roll forward to some point, pause the recovery, and then snoop around in the database in a read-only fashion before deciding whether to advance further. We don't currently have a good mechanism for the pause-and-resume bit but I bet something like walreceiver could be built to do that. The snoop around part is already handled nicely by HS. Yeah, it's too bad we never got around to fix the pause/resume functions the original HS patch included. +1 on changing it to something. Any thoughts on what? Well, the obvious possibility is: hot_standby = on/off allow_recovery_queries? Not sure I like either of those more than allow_standby_queries, though, despite what I just wrote. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com writes: Can we keep the default=on during beta and collect evidence before making a final decision at release? I'd be for doing that if it didn't have adverse effects on the use of pg_start_backup (which is where we started this thread). But it would be to get more testing, not because we would consider beta results as even possibly giving sufficient confidence to justify shipping 9.0.0 with the default = on. I realize that you've sweated blood over a long period to get this stuff in there, and if I were you I'd probably be wishing it could be on by default too. But from a project management standpoint it's just way too risky to even consider. HS/SR at this point have to be seen as being about as trustworthy as the Windows port was in 8.0.0. No DBA is going to be happy with that stuff getting turned on unless he specifically asks for it. Maybe in 9.1 or 9.2, but not in 9.0. regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 13:20 -0400, Robert Haas wrote: This is a pretty good argument, and Heikki's argument just upthread that a mismatched max_connections setting could bollix things is an even better one. So I'm now changing my mind and thinking this should be off by default, also. What Heikki hasn't said is that setting max_prepared_transactions incorrectly has always caused a fatal error at failover, and still does so now when HS is disabled. If these concerns were real ones, then that situation would not have existed for years and would not still do so. 9.0 without HS enabled is fairly untested on the standby, so it's not clear to me turning it off makes you any safer. -- Simon Riggs www.2ndQuadrant.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com wrote: 9.0 without HS enabled is fairly untested on the standby, so it's not clear to me turning it off makes you any safer. I think you've just made the strongest possible case for not defaulting it on during beta testing. -Kevin -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: On Thu, 2010-04-29 at 19:55 +0300, Heikki Linnakangas wrote: It's safer to run with recovery_connections off if you don't need the feature. Presumably your advice is also that people should not run with Streaming Replication if they don't need that feature? Umm, yes. Why would you bother to set it up if you don't need it? And that we should also have an enable_joinremoval flag so the risk it poses can be minimized? You didn't argue this way with regard to vacuum/FSM in 8.4, which was much more critical; we're just talking about a standby server. This is getting bizarre... I wasn't really following the join removal discussions, but it seems much safer and less complex. And it would not have been feasible to have both implementations of FSM around and have the user to choose. I have been relieved by the lack of bug reports on the new FSM, that was a big change that impacted all installations. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 13:01 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: I've asked for evidence that recovery is any slower as a result of HS. Can you quantify the impact on the number of bytes written to WAL when switching from the archiving level to the hot standby level? Sure, done that a few times. Extra WAL data is written for these actions, listed in order of increasing size * commit records contain a variable length list of relcacheinvalidations, mostly applies only to DDL * one extra WAL record in most VACUUMs, fairly small, optimised away in some cases * a transaction issues more than 64 subtransactions it will issue a record approx 256 bytes plus header * one extra WAL record every checkpoint, containing a full current snapshot's worth of running xids 100-400 bytes typically, could go up from there to 4000 bytes in very extreme write workloads that also have many, many subtransactions -- Simon Riggs www.2ndQuadrant.com -- 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] s/recovery_connections/allow_standby_queries/, or something like that?
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: +1 on changing it to something. Any thoughts on what? Well, the obvious possibility is: hot_standby = on/off allow_recovery_queries? Not sure I like either of those more than allow_standby_queries, though, despite what I just wrote. I don't think we want the word recovery in there at all. From the standpoint of system internals it might make sense, but the user is not going to think of a hot standby server as being in recovery. I could go with just plain hot_standby, though. It's sensible and it also fits Robert's suggestion that it should match up with the corresponding wal_level setting. regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs wrote: What Heikki hasn't said is that setting max_prepared_transactions incorrectly has always caused a fatal error at failover, and still does so now when HS is disabled. Hmm, good point. That should probably be documented somewhere. Now that we WAL-log the max_prepared_xacts value, I suppose we could throw a WARNING too. If these concerns were real ones, then that situation would not have existed for years and would not still do so. There's very few people using two-phase commit out there, and even less so in a standby configuration. I wouldn't draw any conclusions from that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 21:27 +0300, Heikki Linnakangas wrote: I wasn't really following the join removal discussions, but it seems much safer and less complex. And it would not have been feasible to have both implementations of FSM around and have the user to choose. I have been relieved by the lack of bug reports on the new FSM, that was a big change that impacted all installations. I agree that Simon's argument doesn't hold a lot of water in comparison to the features he is citing. I say leave it on for Beta just so we can hopefully get some inherit testing but other than that, its off for release. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] Add column if not exists (CINE)
robertmh...@gmail.com (Robert Haas) writes: On Wed, Apr 28, 2010 at 1:40 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Robert Haas robertmh...@gmail.com writes: Well, how would you define CREATE OR REPLACE TABLE? I think that doesn't make much sense, which is why I think CREATE IF NOT EXISTS is a reasonable approach. hand waving time The behavior I'd like to have would be to allow me to give a SELECT query to run for replacing what is there if there's something. If the query can not be run on the existing data set, error out of course. So you know the state for sure after the command, but it depends on your query being correct. And you can (de)normalize existing data using joins. The REPLACE keyword would here mean that there's a CTAS going under the hood, then we add the constraints and indexes and triggers etc. That would mean being able to express those entities changes too, but it seems important. Well, that may be not precise enough as a spec, but at least that's food for though I hope. This type of hand-waving convinces me more than ever that we should just implement CINE, and it should just C if it doesn't already E. This is what has been requested multiple times, by multiple people, including various people who don't normally poke their head into -hackers. I think the resistance to a straightforward implementation with easy-to-understand behavior is completely unjustifiable. It's completely unobvious to me that all of the above will work at all and, if it did, whether it would actually solve the problems that I care about, like being able to write schema-upgrade scripts that would work in a simple and predictable fashion. I tend to agree with you here. While yes, CINE is a simplification of COR (CREATE OR REPLACE), I'm not at all sure that it's reasonable to hope for the latter, in that it elides potentially grave problems that aren't reasonable to expect solved. Notably, the and what if a substantial data transformation is needed to accomplish this? CINE doesn't propose to try to do that transformation, which seems like the right choice to me. When I put my we've got things replicating using Slony-I hat on, CINE looks pretty preferable to me. It's unambitious - but it is certainly NOT doing a bunch of magic behind your back so as to make it tougher to predict what might happen in a trigger-replicated environment. In any case, CINE seems pretty useful to me. I'm prepared to listen to persuasion, but thus far, it looks like a +1 from me. An alternative that seems likable is COR, raising an exception if there's a type mismatch. Where there's certainly room to debate how much of a difference represents a mismatch. -- cbbrowne,@,gmail.com http://linuxfinances.info/info/wp.html Predestination was doomed from the start. -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Kevin Grittner kevin.gritt...@wicourts.gov writes: Simon Riggs si...@2ndquadrant.com wrote: 9.0 without HS enabled is fairly untested on the standby, so it's not clear to me turning it off makes you any safer. I think you've just made the strongest possible case for not defaulting it on during beta testing. Yes. If we've had it on so far, flipping it at the start of beta seems sensible from a test coverage standpoint. We will certainly have a reasonable number of beta testers who are interested in the feature and turn it on to test it; but we need to make sure the off position isn't accidentally broken. regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com wrote: [most significant extra WAL logging for hot standby is:] * one extra WAL record every checkpoint, containing a full current snapshot's worth of running xids 100-400 bytes typically, could go up from there to 4000 bytes in very extreme write workloads that also have many, many subtransactions The most convincing evidence that there's no significant performance hit for those not needing the feature (and not a bad thing from a testing point of view anyway) would be for someone to run some benchmarks comparing the archive and hot_standby logging levels, with no archive script or SR. If that hasn't been done yet, maybe you could find somebody who knows his way around pgbench-tools, who could construct a reasonable test and produce graphs and all that cool stuff. ;-) IMO, isolating the cost of generating and writing the extra WAL would be very valuable, and it would be reassuring to know that it worked with a large number of clients without exposing any dire race conditions. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: Yeah. ISTM the real bottom line here is that we have only a weak grasp on how these features will end up being used; or for that matter what the common error scenarios will be. I think that for the time being we should err on the side of being permissive. We can tighten things up and add more nanny-ism in the warnings later on, when we have more field experience. Ok, here's a proposed patch. Per discussion, it relaxes the checks in pg_start/stop_backup() so that they can be used as long as wal_level = 'archive', even if archiving is disabled. This patch seems reasonably noncontroversial (except possibly for message wording, which we can fine-tune later anyway). Please apply. 9.0beta1 is going to get wrapped in only a few hours. BTW, the documentation for these functions might need a bit of adjustment. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby tuning for btree_xlog_vacuum()
Simple tuning of btree_xlog_vacuum() using an idea I had a while back,
just never implemented. XXX comments removed.
Allows us to avoid reading in blocks during VACUUM replay that are only
required for correctness of index scans.
Objections to commit?
--
Simon Riggs www.2ndQuadrant.com
*** a/src/backend/access/nbtree/nbtxlog.c
--- b/src/backend/access/nbtree/nbtxlog.c
***
*** 486,505 btree_xlog_vacuum(XLogRecPtr lsn, XLogRecord *record)
for (; blkno xlrec-block; blkno++)
{
/*
! * XXX we don't actually need to read the block, we just need to
! * confirm it is unpinned. If we had a special call into the
! * buffer manager we could optimise this so that if the block is
! * not in shared_buffers we confirm it as unpinned.
! *
! * Another simple optimization would be to check if there's any
! * backends running; if not, we could just skip this.
*/
! buffer = XLogReadBufferExtended(xlrec-node, MAIN_FORKNUM, blkno, RBM_NORMAL);
! if (BufferIsValid(buffer))
! {
! LockBufferForCleanup(buffer);
! UnlockReleaseBuffer(buffer);
! }
}
}
--- 486,496
for (; blkno xlrec-block; blkno++)
{
/*
! * We don't actually need to read the block, we just need to
! * confirm it is unpinned, since if it's not in shared_buffers then
! * we're OK.
*/
! XLogConfirmBufferIsUnpinned(xlrec-node, MAIN_FORKNUM, blkno);
}
}
*** a/src/backend/access/transam/xlogutils.c
--- b/src/backend/access/transam/xlogutils.c
***
*** 342,347 XLogReadBufferExtended(RelFileNode rnode, ForkNumber forknum,
--- 342,377
return buffer;
}
+ void
+ XLogConfirmBufferIsUnpinned(RelFileNode rnode, ForkNumber forknum,
+ BlockNumber blkno)
+ {
+ BlockNumber lastblock;
+ SMgrRelation smgr;
+
+ Assert(blkno != P_NEW);
+
+ /* Open the relation at smgr level */
+ smgr = smgropen(rnode);
+
+ /*
+ * Create the target file if it doesn't already exist. This lets us cope
+ * if the replay sequence contains writes to a relation that is later
+ * deleted. (The original coding of this routine would instead suppress
+ * the writes, but that seems like it risks losing valuable data if the
+ * filesystem loses an inode during a crash. Better to write the data
+ * until we are actually told to delete the file.)
+ */
+ smgrcreate(smgr, forknum, true);
+
+ lastblock = smgrnblocks(smgr, forknum);
+
+ if (blkno = lastblock)
+ return;
+
+ /* page exists in file */
+ ConfirmBufferIsUnpinned(rnode, forknum, blkno);
+ }
/*
* Struct actually returned by XLogFakeRelcacheEntry, though the declared
*** a/src/backend/storage/buffer/bufmgr.c
--- b/src/backend/storage/buffer/bufmgr.c
***
*** 475,480 ReadBuffer_common(SMgrRelation smgr, bool isLocalBuf, ForkNumber forkNum,
--- 475,520
return BufferDescriptorGetBuffer(bufHdr);
}
+ void
+ ConfirmBufferIsUnpinned(RelFileNode rnode, ForkNumber forkNum, BlockNumber blockNum)
+ {
+ BufferTag bufTag; /* identity of requested block */
+ uint32 bufHash; /* hash value for newTag */
+ LWLockId bufPartitionLock; /* buffer partition lock for it */
+ int buf_id;
+ SMgrRelation smgr = smgropen(rnode);
+
+ /* create a tag so we can lookup the buffer */
+ INIT_BUFFERTAG(bufTag, smgr-smgr_rnode, forkNum, blockNum);
+
+ /* determine its hash code and partition lock ID */
+ bufHash = BufTableHashCode(bufTag);
+ bufPartitionLock = BufMappingPartitionLock(bufHash);
+
+ /* see if the block is in the buffer pool already */
+ LWLockAcquire(bufPartitionLock, LW_SHARED);
+
+ buf_id = BufTableLookup(bufTag, bufHash);
+
+ /*
+ * If buffer isn't present it must be unpinned.
+ */
+ if (buf_id = 0)
+ {
+ volatile BufferDesc *buf;
+
+ buf = BufferDescriptors[buf_id];
+
+ /*
+ * Found it. Now, pin/unpin the buffer to prove it's unpinned.
+ */
+ if (PinBuffer(buf, NULL))
+ UnpinBuffer(buf, false);
+ }
+
+ LWLockRelease(bufPartitionLock);
+ }
+
/*
* BufferAlloc -- subroutine for ReadBuffer. Handles lookup of a shared
* buffer. If no buffer exists already, selects a replacement
*** a/src/include/access/xlogutils.h
--- b/src/include/access/xlogutils.h
***
*** 28,33 extern void XLogTruncateRelation(RelFileNode rnode, ForkNumber forkNum,
--- 28,35
extern Buffer XLogReadBuffer(RelFileNode rnode, BlockNumber blkno, bool init);
extern Buffer XLogReadBufferExtended(RelFileNode rnode, ForkNumber forknum,
BlockNumber blkno, ReadBufferMode mode);
+ extern void XLogConfirmBufferIsUnpinned(RelFileNode rnode, ForkNumber forknum,
+ BlockNumber blkno);
extern Relation CreateFakeRelcacheEntry(RelFileNode rnode);
extern void FreeFakeRelcacheEntry(Relation fakerel);
*** a/src/include/storage/bufmgr.h
--- b/src/include/storage/bufmgr.h
***
*** 163,168 extern Buffer ReadBufferExtended(Relation reln, ForkNumber
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Okay, this thread seems to be running out of steam, and we are running out of hours before the beta1 wrap. I am going to take it on my own authority to do the following: * rename recovery_connections to hot_standby (and the underlying variable from XLogRequestRecoveryConnections to something like EnableHotStandby); * change its default value to 'off'. I believe the only other code changes needed for beta1 are the pg_start/stop_backup error check changes that Heikki proposed at 4bd953a6.70...@enterprisedb.com. If he doesn't commit that within an hour or two, I will. This train is leaving the station. regards, tom lane -- 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] Hot Standby tuning for btree_xlog_vacuum()
Simon Riggs si...@2ndquadrant.com writes: Objections to commit? This is not the time to be hacking stuff like this. You haven't even demonstrated that there's a significant performance issue here. regards, tom lane -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, Apr 29, 2010 at 4:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: This train is leaving the station. An enthusiastic +1 for train departure. ...Robert -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Thu, 2010-04-29 at 16:18 -0400, Tom Lane wrote: * rename recovery_connections to hot_standby (and the underlying variable from XLogRequestRecoveryConnections to something like EnableHotStandby); OK * change its default value to 'off'. OK -- Simon Riggs www.2ndQuadrant.com -- 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] pg_migrator
Andrew Dunstan wrote: Bruce Momjian wrote: I concur; it's about a month too late to propose this. I am confused why it is late. We add to /contrib even during beta, and I didn't bring it up earlier because I didn't want to be pushing my own software. Was someone else supposed to suggest it a month ago? Bruce, you're not usually such a shrinking violet. If you don't push your project it's less likely others will, IMNSHO. Well, I am sensitive to be pushing my external project into /contrib when I am someone who puts other stuff into contrib, and I have been working on pg_migrator for only one year. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_migrator to /contrib in a later 9.0 beta
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian br...@momjian.us wrote: There was talk of including pg_migrator in Postgres 9.0 in /contrib. ?Do we still want to do that? I think you articulated some pretty good reasons previously for keeping it separate and, at any rate, I'm not eager to do it at the 11th hour without due consideration and adequate engineering time. I concur; it's about a month too late to propose this. I talked to a few people personally about this, and it seems there was a misunderstanding. I was not asking if pg_migrator should be in 9.0 beta1. I was asking if we should think about putting it into a later 9.0 beta, like 9.0 beta3. It would be another major 9.0 feature. Because pg_migrator is an external project, it seemed best to do it after beta1, while we are just waiting for bug reports, and not during our main push to beta1. FYI, here was the last discussion about having pg_migrator in /contrib in December 2009: http://archives.postgresql.org/pgsql-hackers/2009-12/msg01787.php and most of the limitations of pg_migrator are gone when migrating to 9.0, even from Postgres 8.3. This could help beta testers move their data to 9.0 as well. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- 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] pg_migrator to /contrib in a later 9.0 beta
Bruce Momjian wrote: and most of the limitations of pg_migrator are gone when migrating to 9.0, even from Postgres 8.3. This could help beta testers move their data to 9.0 as well. Wouldn't this help even for beta1? Cheers Mark -- 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] pg_migrator to /contrib in a later 9.0 beta
Mark Kirkwood mark.kirkw...@catalyst.net.nz writes: Bruce Momjian wrote: and most of the limitations of pg_migrator are gone when migrating to 9.0, even from Postgres 8.3. This could help beta testers move their data to 9.0 as well. Wouldn't this help even for beta1? It's too late for beta1. It probably should have been proposed when there was still time ... but it wasn't. I'm not necessarily averse to shoving it in as of beta2 or beta3 or so; we've always been laxer about contrib than the core server. regards, tom lane -- 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] pg_migrator to /contrib in a later 9.0 beta
Mark Kirkwood wrote: Bruce Momjian wrote: and most of the limitations of pg_migrator are gone when migrating to 9.0, even from Postgres 8.3. This could help beta testers move their data to 9.0 as well. Wouldn't this help even for beta1? It would, but there is so much work going into getting other features done that there just isn't enough time. We don't want pg_migrator delaying beta1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] failed assertion and panic in standby mode
Hi, i was trying recent HS and get this when trying to start the standby, actually i was expecting a crash because i use full_page_writes=off and i guess it won't work. Maybe we could say full_page_writes=off and wal_level=hot_standby are conflicting and avoid such setup? LOG: database system was interrupted; last known up at 2010-04-29 23:38:53 ECT LOG: entering standby mode LOG: restored log file 00010004 from archive LOG: redo starts at 0/452A898 TRAP: FailedAssertion(!(( (metabuffer) != 0 (metabuffer) = -NLocBuffer (metabuffer) = NBuffers )), File: ginxlog.c, Line: 590) LOG: startup process (PID 8287) was terminated by signal 6: Aborted LOG: terminating any other active server processes then i try again, i make the backup when running make installcheck for the second time and i get this: LOG: database system was interrupted; last known up at 2010-04-29 23:48:13 ECT LOG: entering standby mode LOG: restored log file 0001000B from archive LOG: redo starts at 0/B20 WARNING: could not open directory base/40596: No existe el fichero o el directorio CONTEXT: xlog redo drop db: dir 40596/1663 WARNING: some useless files may be left behind in old database directory base/40596 CONTEXT: xlog redo drop db: dir 40596/1663 LOG: restored log file 0001000C from archive PANIC: btree_redo: unknown op code 208 CONTEXT: xlog redo UNKNOWN LOG: startup process (PID 9264) was terminated by signal 6: Aborted LOG: terminating any other active server processes -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] failed assertion and panic in standby mode
Jaime Casanova jcasa...@systemguards.com.ec writes: i was trying recent HS and get this when trying to start the standby, TRAP: FailedAssertion(!(( (metabuffer) != 0 (metabuffer) = -NLocBuffer (metabuffer) = NBuffers )), File: ginxlog.c, Line: 590) Hm, can you provide a test case? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] COPY is not working
Hi, COPY is not working on latest HEAD? regression=# select * from a; aa 32 56 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 0 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] COPY is not working
Jaime Casanova jcasa...@systemguards.com.ec wrote: COPY is not working on latest HEAD? regression=# select * from a; aa 32 56 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 0 -- Please send the actual test pattern and your environment information to reproduce the misbehavior. It works fine on my machine. regression=# CREATE TABLE a (aa integer); CREATE TABLE regression=# INSERT INTO a VALUES(32), (56); INSERT 0 2 regression=# select * from a; aa 32 56 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 2 regression=# \! cat /tmp/copy_test 32 56 $ uname -a Linux xxx 2.6.29.4-167.fc11.x86_64 #1 SMP Wed May 27 17:27:08 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux $ postgres --version postgres (PostgreSQL) 9.0beta1 Regards, --- Takahiro Itagaki NTT Open Source Software Center -- 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] COPY is not working
2010/4/30 Jaime Casanova jcasa...@systemguards.com.ec: Hi, COPY is not working on latest HEAD? regression=# select * from a; aa 32 56 (2 rows) regression=# COPY a TO '/tmp/copy_test'; COPY 0 ah! this is because COPY doesn't follow inherited tables... should it? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
