Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Hmm, AFAICS the problem with controldata is that it uses postgres_fe.h instead of postgres.h. It's a bit of a stretch to use the latter, but maybe that's a better solution? After all, it *is* poking into the backend internals. I seem to recall that Solaris had problems with that due to dtrace support or something? However, we are doing it in pg_resetxlog, so I suppose it's ok for pg_controldata as well. Ok, did that. Here's an updated patch: * made incorrect combinations of wal_mode and archive_mode/max_wal_senders throw an ERROR instead of WARNING * renamed wal_mode to wal_level * reworded the documentation changes a bit This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. At commit, should I bump catversion, or PG_CONTROL_VERSION, or both? The patch replaces the unlogged-operation WAL record with a record containing current parameter values, and it changes pg_control. I'm guessing both. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index eb5765a..27506d4 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -689,8 +689,7 @@ archive_command = 'test ! -f /mnt/server/archivedir/%f amp;amp; cp %p /mnt/ser /para para -When varnamearchive_mode/ is literaloff/ and xref -linkend=guc-max-wal-senders is zero some SQL commands +When varnamewal_level/ is literalminimal/ some SQL commands are optimized to avoid WAL logging, as described in xref linkend=populate-pitr. If archiving or streaming replication were turned on during execution of one of these statements, WAL would not diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index c5692ba..9f2a358 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1353,6 +1353,45 @@ SET ENABLE_SEQSCAN TO OFF; titleSettings/title variablelist + varlistentry id=guc-wal-level xreflabel=wal_level + termvarnamewal_level/varname (typeenum/type)/term + indexterm + primaryvarnamewal_level/ configuration parameter/primary + /indexterm + listitem + para +varnamewal_level/ determines how much information is written +to the WAL. The default value is literalminimal/, which writes +only minimal information needed to recover from a crash or immediate +shutdown. literalarchive/ adds logging required for WAL archiving, +and literalhot_standby/ further adds information required to run +read-only queries on a standby server. +This parameter can only be set at server start. + /para + para +In literalminimal/ mode, WAL-logging of some bulk operations, like +commandCREATE INDEX/, commandCLUSTER/ and commandCOPY/ on +a table that was created or truncated in the same transaction can be +safely skipped, which can make those operations much faster (see +xref linkend=populate-pitr). But minimal WAL does not contain +enough information to reconstruct the data from a base backup and the +WAL logs, so at least literalarchive/ level must be used to enable +WAL archiving (xref linkend=guc-archive-mode) and streaming +replication. + /para + para +In literalhot_standby/ mode, the same information is logged as +in literalarchive/ mode, plus information needed to reconstruct +the status of running transactions from the WAL. To enable read-only +queries on a standby server, varnamewal_level/ must be set to +literalhot_standby/ on the primary. It is thought that there is +little measurable difference in performance from using +literalhot_standby/ mode over literalarchive/, so feedback +is welcome if any production impacts are noticeable. + /para + /listitem + /varlistentry + varlistentry id=guc-fsync xreflabel=fsync indexterm primaryvarnamefsync/ configuration parameter/primary @@ -1726,7 +1765,9 @@ SET ENABLE_SEQSCAN TO OFF; varnamearchive_mode/ and varnamearchive_command/ are separate variables so that varnamearchive_command/ can be changed without leaving archiving mode. -This parameter can only be set at server start. +This parameter can only be set at server start. varnamewal_level/ +must be set to literalarchive/ or literalhot_standby/ to +enable varnamearchive_mode/. /para /listitem /varlistentry @@ -1818,7 +1859,9 @@ SET ENABLE_SEQSCAN TO OFF; Specifies the maximum number of concurrent connections from standby servers (i.e., the maximum number of simultaneously running WAL sender processes). The default is zero. This parameter can only be set at -server start. +server
Re: [HACKERS] Error handling for ShmemInitStruct and ShmemInitHash
Tom Lane wrote: The functions ShmemInitStruct and ShmemInitHash will return NULL on certain failure conditions, apparently on the grounds that their caller can print a more useful error message than they can. A quick survey shows that about half the callers aren't remembering to check for NULL, and none of the other half are printing messages that are more useful than out of shared memory (which isn't even necessarily correct). I think that this is pretty error-prone, and that considering that PG hackers are accustomed to not checking palloc() results, it's inevitable that we'll make the same mistake in future if we leave this API as it is. I suggest making these functions throw their own errors rather than returning NULL on failure, and removing the redundant error reports from the callers that have 'em. +1. I was just annoyed by this when working on the known-assigned-xids hash - sorted-array patch. -- 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 Wed, 2010-04-28 at 10:43 +0300, Heikki Linnakangas wrote: * renamed wal_mode to wal_level I'm wondering whether this should be a list rather than an enum? If we add something in the future that adds more info to WAL but doesn't fit the one-dimensional model this implements then we could be in trouble. Should this be e.g. wal_ = feature2, feature3 e.g. wal_ = feature3 e.g. wal_ = feature1 recognising that some features require other features, so as an example feature2 requires and implies feature1. The word level implies a one-dimensionality that mode did not and I feel a little uncertain about that term. Other words: attributes, features, contents, info, options. Hmm, wal_options sounds OK. Anyway, just throwing out some ideas to make sure we're doing the right thing with this. -- 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] providing tokenized version of parsed SQL script
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/04/2010 02:20, Robert Haas wrote: On Tue, Apr 27, 2010 at 7:45 PM, Jehan-Guillaume (ioguix) de Rorthais iog...@free.fr wrote: I thought Michael was trying to write a tokenizer based on node tree returned by raw_parser. As it seems Michael is not even sure about what he's trying to do, I prefer refocus a bit this thread [...] Having dropped an eye here and there in the parser code, I am not sure where I could get required info and mix them to produce something close to my draft yet. But I prefer to discussing first before spending too much time and throwing any potential code after... I can't quite tell, from reading this, what you're trying to do with this... I would like to do what I described here: http://archives.postgresql.org/pgsql-hackers/2010-04/msg00974.php Pavel Stehule convinced me a function would be a better approach than a new SQL keyword. Moreover he points an existing function in the Oracle ecosystem that is doing very closely what I descibed in my draft: http://archives.postgresql.org/pgsql-hackers/2010-04/msg00976.php http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29#PLVlex Presently I am just studying some parts of the parser code to find where I could find : - token/lexer representation of a SQL script - type of each token Sorry the mess and confusion, hope this mail is clearer ? ...Robert - -- JGuillaume (ioguix) de Rorthais http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvX7aIACgkQxWGfaAgowiI5PQCeITOqAlCbeFyYc2KdHPUOfXhe YY8An0jdfF31SK75bmd21ixFffXiyoEF =ymbW -END PGP SIGNATURE- -- 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 Wed, 2010-04-28 at 10:43 +0300, Heikki Linnakangas wrote: * renamed wal_mode to wal_level I'm wondering whether this should be a list rather than an enum? If we add something in the future that adds more info to WAL but doesn't fit the one-dimensional model this implements then we could be in trouble. Should this be e.g. wal_ = feature2, feature3 e.g. wal_ = feature3 e.g. wal_ = feature1 recognising that some features require other features, so as an example feature2 requires and implies feature1. The word level implies a one-dimensionality that mode did not and I feel a little uncertain about that term. That's the same concern Tom had at http://archives.postgresql.org/message-id/17411.1272291...@sss.pgh.pa.us. If we want to make it a list, I think it would be something like: wal_extra_info = '' # archive, or archive, hot_standby That seems OK to me. Still, I'd like to go with level, it's short and descriptive of the current options, and it's not that big a deal to rename it if we add more options in the future. It's hard to picture what the future options might be like. Someone had an idea years ago (you even?) to add more information like table names or primary keys to the WAL records, to make it easier to scrape information from the WAL for 3rd party replication solutions. Like feeding a slony replica from the WAL. Or maybe we'd want to fold full_page_writes to the new GUC. If it's just one or two extra feature orthogonal to the minimal-archive-hot_standby axis, it might still be best to keep the wal_level name, and make it possible to tack extra options to it like a list. Like wal_level='archive, featureX'. Level would still be a good name, that's the main dimension. If the dependencies between the features get complicated, though, I doubt we can correctly guess the best representation for them now anyway. That's just my 2 cents, if others feel like level is painting us to the corner, wal_mode with the same values is the second best option IMO. -- 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 Wed, 2010-04-28 at 11:27 +0300, Heikki Linnakangas wrote: It's hard to picture what the future options might be like. Someone had an idea years ago (you even?) to add more information like table names or primary keys to the WAL records, to make it easier to scrape information from the WAL for 3rd party replication solutions. Like feeding a slony replica from the WAL. Or maybe we'd want to fold full_page_writes to the new GUC. Yeh, lots of ideas for adding value to WAL. WAL can be considered a transport solution with many potential uses and properties. I haven't ever suggested adding something that would only be available to 3rd party solutions, perhaps that information was garbled. Yes, I did suggest that Slony might use a WAL transport in future, as an option. -- 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 Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Ok, did that. Here's an updated patch: +During recovery, specifies whether or not you can connect and run +queries to enable xref linkend=hot-standby. The default value is +literalon/literal. +This parameter can only be set at server start. It is ignored when +not in standby mode. In the patch, I was not able to find any change which ignores recovery_connections when standby_mode is OFF. And that change spoils the combination of pg_standby and HS? We drop the support for it? * renamed wal_mode to wal_level We should replace mode with level in the document of wal_level parameter as follows? @@ -1368,9 +1368,9 @@ SET ENABLE_SEQSCAN TO OFF; read-only queries on a standby server. This parameter can only be set at server start. /para para -In literalminimal/ mode, WAL-logging of some bulk operations, like +In literalminimal/ level, WAL-logging of some bulk operations, like commandCREATE INDEX/, commandCLUSTER/ and commandCOPY/ on a table that was created or truncated in the same transaction can be safely skipped, which can make those operations much faster (see xref linkend=populate-pitr). But minimal WAL does not contain @@ -1379,15 +1379,15 @@ SET ENABLE_SEQSCAN TO OFF; WAL archiving (xref linkend=guc-archive-mode) and streaming replication. /para para -In literalhot_standby/ mode, the same information is logged as -in literalarchive/ mode, plus information needed to reconstruct +In literalhot_standby/ level, the same information is logged as +in literalarchive/ level, plus information needed to reconstruct the status of running transactions from the WAL. To enable read-only queries on a standby server, varnamewal_level/ must be set to literalhot_standby/ on the primary. It is thought that there is little measurable difference in performance from using -literalhot_standby/ mode over literalarchive/, so feedback +literalhot_standby/ level over literalarchive/, so feedback is welcome if any production impacts are noticeable. /para /listitem /varlistentry Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, 2010-04-28 at 18:54 +0900, Fujii Masao wrote: On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Ok, did that. Here's an updated patch: +During recovery, specifies whether or not you can connect and run +queries to enable xref linkend=hot-standby. The default value is +literalon/literal. +This parameter can only be set at server start. It is ignored when +not in standby mode. In the patch, I was not able to find any change which ignores recovery_connections when standby_mode is OFF. And that change spoils the combination of pg_standby and HS? We drop the support for it? Yeh, I hope nothing is being disabled in that area. Many people hope and expect HS to work with 9.0 without any changes to their setup. -- 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 Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Ok, did that. Here's an updated patch: +During recovery, specifies whether or not you can connect and run +queries to enable xref linkend=hot-standby. The default value is +literalon/literal. +This parameter can only be set at server start. It is ignored when +not in standby mode. In the patch, I was not able to find any change which ignores recovery_connections when standby_mode is OFF. And that change spoils the combination of pg_standby and HS? We drop the support for it? That sentence is poorly worded, recovery_connections works in archive recovery too (and with pg_standby). I'll rephrase that to It only has effect during archive recovery or standby mode. * renamed wal_mode to wal_level We should replace mode with level in the document of wal_level parameter as follows? ... Yep. Thanks! -- 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
pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. I'm thinking of changing pg_start_backup and pg_stop_backup so that they just check that wal_level = 'archive', and changing pg_stop_backup so that it doesn't wait for archiving when archive_mode is OFF. This change is very simple and enables us to take a base backup for SR even if archive_mode is OFF. Thought? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, 2010-04-28 at 19:40 +0900, Fujii Masao wrote: On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. I'm thinking of changing pg_start_backup and pg_stop_backup so that they just check that wal_level = 'archive', and changing pg_stop_backup so that it doesn't wait for archiving when archive_mode is OFF. This change is very simple and enables us to take a base backup for SR even if archive_mode is OFF. Thought? Makes sense. I'm wondering whether this could cause problems with people taking hot backups that aren't aimed at SR. Perhaps we could have 2 new functions whose names are more closely linked to the exact purpose: pg_start_replication_copy() etc.. which then act exactly as you suggest. -- 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
On Wed, Apr 28, 2010 at 6:52 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-04-28 at 19:40 +0900, Fujii Masao wrote: On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. I'm thinking of changing pg_start_backup and pg_stop_backup so that they just check that wal_level = 'archive', and changing pg_stop_backup so that it doesn't wait for archiving when archive_mode is OFF. This change is very simple and enables us to take a base backup for SR even if archive_mode is OFF. Thought? Makes sense. I'm wondering whether this could cause problems with people taking hot backups that aren't aimed at SR. Perhaps we could have 2 new functions whose names are more closely linked to the exact purpose: pg_start_replication_copy() etc.. which then act exactly as you suggest. Hmm. That seems a bit complicated. Why can't we just let people use the existing functions the way they always have? ...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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, 2010-04-28 at 06:56 -0400, Robert Haas wrote: On Wed, Apr 28, 2010 at 6:52 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-04-28 at 19:40 +0900, Fujii Masao wrote: On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. I'm thinking of changing pg_start_backup and pg_stop_backup so that they just check that wal_level = 'archive', and changing pg_stop_backup so that it doesn't wait for archiving when archive_mode is OFF. This change is very simple and enables us to take a base backup for SR even if archive_mode is OFF. Thought? Makes sense. I'm wondering whether this could cause problems with people taking hot backups that aren't aimed at SR. Perhaps we could have 2 new functions whose names are more closely linked to the exact purpose: pg_start_replication_copy() etc.. which then act exactly as you suggest. Hmm. That seems a bit complicated. Why can't we just let people use the existing functions the way they always have? We can, but I already gave a reason why we should not. IIRC it was you that suggested changing the names of things if the behaviour changes. -- 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
Robert Haas wrote: On Wed, Apr 28, 2010 at 6:52 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-04-28 at 19:40 +0900, Fujii Masao wrote: On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. I'm thinking of changing pg_start_backup and pg_stop_backup so that they just check that wal_level = 'archive', and changing pg_stop_backup so that it doesn't wait for archiving when archive_mode is OFF. This change is very simple and enables us to take a base backup for SR even if archive_mode is OFF. Thought? Makes sense. I'm wondering whether this could cause problems with people taking hot backups that aren't aimed at SR. Perhaps we could have 2 new functions whose names are more closely linked to the exact purpose: pg_start_replication_copy() etc.. which then act exactly as you suggest. Hmm. That seems a bit complicated. Why can't we just let people use the existing functions the way they always have? Well, it would be nice to allow using pg_start_backup() on the primary when streaming replication is enabled, even if archiving isn't. Otherwise the only way to get the base backup for the standby is to shut down primary first, or use filesystem snapshot etc. The straightforward way to enable that would be to allow pg_start_backup() when wal_level = 'archive', regardless of archive_mode. However, I'm worried that someone might take an online backup without archiving (and replication), not realizing that it's not safe. That risk is there already, though, if you restore from an online backup and forget to create recovery.conf. It will start up in inconsistent state. The proposed change would make it easier to make that mistake. I'm not sure what to do about it, maybe throw a warning if you start up a database and there's a backup_label file in the data directory. Something like: WARNING: database system was interrupted while backup was in progress HINT: If you are restoring from an online backup, you must use a WAL archive for the restore, or the database can be in inconsistent state That would also occur if the primary database crashes while a backup is being taken, in which case the warning can be ignored. Or maybe we should check in pg_start_backup() that either archive_mode or streaming replication (max_wal_senders 0) is enabled. -- 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 Wed, Apr 28, 2010 at 8:28 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Or maybe we should check in pg_start_backup() that either archive_mode or streaming replication (max_wal_senders 0) is enabled. I agree that pg_start_backup checks not only wal_level but also that. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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] Add column if not exists (CINE)
Takahiro Itagaki wrote: Kjell Rune Skaaraas kjell...@yahoo.no wrote: I've been reading the earlier threads at: http://archives.postgresql.org/pgsql-hackers/2009-05/thrd7.php#00252 http://archives.postgresql.org/pgsql-hackers/2005-10/thrd4.php#00632 and I'm not sure I have anything that substantially new to add but: I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. +1 for CINE, just because MySQL supports it. MySQL compatibility has never been our aim. We should adopt ideas from other projects because they are good, not just because they are there. That doesn't mean I don't think this is a good idea. But before developing, we need to decide how to handle an added object that has the same name but has different definitions. The OP explicitly stated that in his opinion nothing should be done in such cases. That's a defensible position, in the case of objects such as tables that must be unique by name (e.g. tables). But what would we do about objects where the name could be overloaded? Since we would presumably want to do this for all (or almost all) of our CREATE/ADD commands, we'd need a policy on those. Also, developers should consider not only ADD COLUMN but also other CREATE or ADD commands. The patch will be large, including documentation adjustments in many places -- it would be hard work. I can speak with some experience on this at least. :-) I don't see that it would be a heck of a lot bigger than the DROP IF EXISTS cases, which after the first few had been done were not hard, merely tedious to do :-) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] bug in build_startup_packet()
Hi, We seem to have forgotten to give conn-replication [0]. diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 77cee8a..1956f00 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -1909,7 +1909,7 @@ build_startup_packet(const PGconn *conn, char *packet, ADD_STARTUP_OPTION(user, conn-pguser); if (conn-dbName conn-dbName[0]) ADD_STARTUP_OPTION(database, conn-dbName); - if (conn-replication conn-replication) + if (conn-replication conn-replication[0]) ADD_STARTUP_OPTION(replication, conn-replication); if (conn-pgoptions conn-pgoptions[0]) ADD_STARTUP_OPTION(options, conn-pgoptions); Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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] testing HS/SR - 1 vs 2 performance
On Tue, 2010-04-27 at 20:13 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-04-27 at 18:08 -0400, Tom Lane wrote: Huh? How is a filter as coarse as an oldest-running-XID filter going to prevent that? And aren't we initializing from trustworthy data in ProcArrayApplyRecoveryInfo, anyway? I still say it's useless. Quite possibly. Your looking at other code outside of this patch. I'm happy that you do so, but is it immediately related? I can have another look when we finish this. Well, it's nearby anyway. I've committed the present patch (with a number of fixes). While I was looking at it I came across several things in the existing code that I think are either wrong or at least inadequately documented --- the above complaint is just the tip of the iceberg. I'm going to make another pass over it to see if I'm just missing things, and then report back. Thank you for your input and changes. You're welcome to share my iceberg. -- 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] bug in build_startup_packet()
On Wed, Apr 28, 2010 at 14:43, Fujii Masao masao.fu...@gmail.com wrote: Hi, We seem to have forgotten to give conn-replication [0]. Oops. Applied, thanks. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: At commit, should I bump catversion, or PG_CONTROL_VERSION, or both? The patch replaces the unlogged-operation WAL record with a record containing current parameter values, and it changes pg_control. I'm guessing both. You *must* bump PG_CONTROL_VERSION because the content of pg_control changed. The correct way to reflect a change in WAL contents is to bump the WAL page magic number. I don't see any change here in either system catalog contents or user table contents, so I see no need to touch catversion. 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] Add column if not exists (CINE)
Andrew Dunstan and...@dunslane.net writes: Takahiro Itagaki wrote: But before developing, we need to decide how to handle an added object that has the same name but has different definitions. The OP explicitly stated that in his opinion nothing should be done in such cases. That's a defensible position, in the case of objects such as tables that must be unique by name (e.g. tables). But what would we do about objects where the name could be overloaded? Even if it's defensible, the consensus position so far has been that it's a bad design. Every time we've looked at this, we have concluded that CREATE OR REPLACE semantics are considerably safer to use, because there is no question what the state of the object is afterwards. That argument is just as valid for a column as for anything larger. AFAICS, the only excuse CINE has for living is that (people think) it would take less work to implement. 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] Error handling for ShmemInitStruct and ShmemInitHash
Tom Lane t...@sss.pgh.pa.us wrote: none of the other half are printing messages that are more useful than out of shared memory (which isn't even necessarily correct). I think the messages in the locking area are a bit more useful than out of shared memory, but it would be trivial to build the equivalent message in the ShmemInitHash function, based on the first parameter. LockMethodProcLockHash = ShmemInitHash(PROCLOCK hash, init_table_size, max_table_size, info, hash_flags); if (!LockMethodProcLockHash) elog(FATAL, could not initialize proclock hash table); Presumably the ShmemInitHash function could add other information which would make the message *more* useful. (Perhaps other parameter information or maybe even the actual *cause* of the failure.) I suggest making these functions throw their own errors rather than returning NULL on failure, and removing the redundant error reports from the callers that have 'em. +1 It would be low priority if the return value was currently being consistently checked for NULL; but since that's not the case we have to do something, and what you suggest sounds best, long term. -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] Add column if not exists (CINE)
On Tue, Apr 27, 2010 at 08:18:13PM -0400, Robert Haas wrote: On Tue, Apr 27, 2010 at 6:45 PM, Kjell Rune Skaaraas kjell...@yahoo.no wrote: [snip] I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper. I've already said my piece on this, but I couldn't agree more. Well said, and your use case is exactly the one I want it for. +1 (Scribbles down the phrase instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper for future repurposing) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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: On Wed, 2010-04-28 at 10:43 +0300, Heikki Linnakangas wrote: * renamed wal_mode to wal_level I'm wondering whether this should be a list rather than an enum? If we add something in the future that adds more info to WAL but doesn't fit the one-dimensional model this implements then we could be in trouble. Should this be e.g. wal_ = feature2, feature3 e.g. wal_ = feature3 e.g. wal_ = feature1 I'm a bit suspicious of going in this direction, mainly because DateStyle has been such a PITA over the years. It's not always obvious to users whether adding or removing an item in a list causes something to turn on or off. In any case, the project's expectations for forward compatibility of postgresql.conf settings have always been very low. I don't think we should try to design wal_mode to solve future problems, just the ones we are faced with right now. If it gets changed to look completely different in some future version, that's not a problem. 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] XML Todo List
Hackers, I'm interested in tackling some of the todo items in XML category. Being new to postgres hacking I'm hoping I chose an item that isn't more than I can chew in the first sitting. One item that has caught my eye that I (naively) hope isn't a huge todo is: 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.) 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? Thanks, -- 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] Error handling for ShmemInitStruct and ShmemInitHash
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: none of the other half are printing messages that are more useful than out of shared memory (which isn't even necessarily correct). I think the messages in the locking area are a bit more useful than out of shared memory, but it would be trivial to build the equivalent message in the ShmemInitHash function, based on the first parameter. Right, I was intending to include the name parameter in the messages. This would actually represent an improvement in message quality in a lot of the cases. 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 Wed, 2010-04-28 at 10:19 -0400, Tom Lane wrote: I'm a bit suspicious of going in this direction, mainly because DateStyle has been such a PITA over the years. LOL. Man that's a pain. That's an unfair brush though! -- 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
WAL page magic number (was Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct)
Tom Lane wrote: The correct way to reflect a change in WAL contents is to bump the WAL page magic number. Browsing the history of XLOG_PAGE_MAGIC, it used to be incremented by one whenever the format changes, at least as long as it has been been in xlog_internal.h. It started at 0xD05B in 2005, and was incremented by one by each commit till 0xD062. But then the hot standby patch bumped it to 0xD166, and on March 19th Simon changed it to 0x9002, and on March 28th to 0x9003. Is there a plan somewhere on all that, or was it just random whacking? It's a bit weird though that the commit on March 19th moved it backwards. We don't guarantee backwards-compatibility, so any program looking at the WAL needs to know not only minimum version it supports but a list of all supported versions, so it's not catastrophic. Still weird, though. It hasn't been like that for long yet, and not in any released version, so we could still change it back to the original scheme (0xD063). 0x9002 sounds like it means the 9.0 release, which might be a good versioning scheme as well. If want to continue with that scheme, it should be documented in the comments. -- 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 Wed, Apr 28, 2010 at 7:22 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-04-28 at 06:56 -0400, Robert Haas wrote: On Wed, Apr 28, 2010 at 6:52 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-04-28 at 19:40 +0900, Fujii Masao wrote: On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This doesn't contain any changes to pg_start_backup() yet, that's a separate issue and still under discussion. I'm thinking of changing pg_start_backup and pg_stop_backup so that they just check that wal_level = 'archive', and changing pg_stop_backup so that it doesn't wait for archiving when archive_mode is OFF. This change is very simple and enables us to take a base backup for SR even if archive_mode is OFF. Thought? Makes sense. I'm wondering whether this could cause problems with people taking hot backups that aren't aimed at SR. Perhaps we could have 2 new functions whose names are more closely linked to the exact purpose: pg_start_replication_copy() etc.. which then act exactly as you suggest. Hmm. That seems a bit complicated. Why can't we just let people use the existing functions the way they always have? We can, but I already gave a reason why we should not. IIRC it was you that suggested changing the names of things if the behaviour changes. Absolutely, but I'm arguing that we shouldn't change the behavior in the first place. At least as I understand it, even when not using archive_mode, streaming replication, or hot standby, it's still perfectly legal to use pg_start_backup() to take a hot backup. I don't see why we would either (a) break that use case or (b) create another function that does the same thing but with one extra error check. ...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: WAL page magic number (was Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: The correct way to reflect a change in WAL contents is to bump the WAL page magic number. Browsing the history of XLOG_PAGE_MAGIC, it used to be incremented by one whenever the format changes, at least as long as it has been been in xlog_internal.h. It started at 0xD05B in 2005, and was incremented by one by each commit till 0xD062. But then the hot standby patch bumped it to 0xD166, and on March 19th Simon changed it to 0x9002, and on March 28th to 0x9003. Is there a plan somewhere on all that, or was it just random whacking? Random whacking. Simon seems to have decided to try to make the number mean something, but it never meant anything before. It's not a version number, it's a magic identifier. We might need to avoid certain values to prevent collisions with other file formats, so I'm suspicious of trying to make it match up with some expectation. 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] Add column if not exists (CINE)
On Wed, Apr 28, 2010 at 9:58 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Takahiro Itagaki wrote: But before developing, we need to decide how to handle an added object that has the same name but has different definitions. The OP explicitly stated that in his opinion nothing should be done in such cases. That's a defensible position, in the case of objects such as tables that must be unique by name (e.g. tables). But what would we do about objects where the name could be overloaded? Even if it's defensible, the consensus position so far has been that it's a bad design. Every time we've looked at this, we have concluded that CREATE OR REPLACE semantics are considerably safer to use, because there is no question what the state of the object is afterwards. That argument is just as valid for a column as for anything larger. AFAICS, the only excuse CINE has for living is that (people think) it would take less work to implement. I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. ...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] Add column if not exists (CINE)
Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas wrote: At least as I understand it, even when not using archive_mode, streaming replication, or hot standby, it's still perfectly legal to use pg_start_backup() to take a hot backup. Nope. The correct procedure to take a hot backup is described in http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-TIPS. It involves setting archive_mode=on, and archive_command to a shell command that normally just returns true, except when backup is in progress. You can't take a hot backup without archiving (or streaming) at least temporarily. (except with filesystem-level snapshot capabilities). Which is unfortunate, really. I wish we had a mode where the server simply refrained from removing/recycling WAL segments while the backup is running. You could then just: 1. pg_start_backup() 2. tar the data directory, except for pg_xlog 3. tar pg_xlog 4. pg_stop_backup(). -- 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] Add column if not exists (CINE)
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. Right. A trivial implementation of CREATE OR REPLACE is to create the object if it doesn't exist, do nothing if it exists already and is identical to the new definition, and throw an error if it's not identical. That covers the same use case as CREATE IF NOT EXISTS, but you know what the state is after a successful execution, is easy to implement, and is in line with the existing CREATE OR REPLACE commands. And can be extended in the future to alter the existing object instead of throwing an error. -- 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
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Which is unfortunate, really. I wish we had a mode where the server simply refrained from removing/recycling WAL segments while the backup is running. You could then just: 1. pg_start_backup() 2. tar the data directory, except for pg_xlog 3. tar pg_xlog 4. pg_stop_backup(). I think there's a termination issue there --- the safe stop point would (appear to be) past whatever WAL you'd copied during step 3. Still, the possibility of adding modes such as this seems to me to be a good argument for not inventing a new version of pg_start_backup/ pg_stop_backup every time. 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] Differential backup
On Tue, 2010-04-27 at 14:30 +0100, Simon Riggs wrote: Thinking about allowing a backup to tell which files have changed in the database since last backup. This would allow an external utility to copy away only changed files. Now there's a few ways of doing this and many will say this is already possible using file access times. An explicit mechanism where Postgres could authoritatively say which files have changed would make many feel safer, especially when other databases also do this. We keep track of which files require fsync(), so we could also keep track of changed files using that same information. Would it make sense to split this in two , one for DML/logical changes (insert, update, delete, truncate) and another for physical, non-functional, file-level changes (vacuum, setting hint bits, ...) BTW, is the stats-collection reliable enough for this or is it still possible to lose some changes if we did this together with updating info for pg_stat_user_tables/pg_statio_user_tables ? -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Differential backup
On Tue, 2010-04-27 at 08:59 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: Thinking about allowing a backup to tell which files have changed in the database since last backup. This would allow an external utility to copy away only changed files. Now there's a few ways of doing this and many will say this is already possible using file access times. Who would say otherwise? Under what circumstances would PostgreSQL modify a file without changing the last modified timestamp or the file size? If you're concerned about the converse, with daemon- based rsync you can copy just the modified portions of a file on which the directory information has changed. Or is this targeting platforms which don't have rsync? I see the main value when doing pg_dump based backups and being able to know if the table was modified by DML (insert/update/delete/truncate) or by something invisible like vacuum or setting hint bits. Currently the only way to keep this info is by having triggers on all tables on all DML An explicit mechanism where Postgres could authoritatively say which files have changed would make many feel safer, especially when other databases also do this. Why? I must be missing something, because my feeling is that if you can't trust your OS to cover something like this, how can you trust any application *running* under that OS to do it? Is this route worthwhile? I'm not seeing it, but I could be missing something. Can you describe a use case where this would be beneficial? -Kevin -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Differential backup
Hannu Krosing ha...@2ndquadrant.com wrote: I see the main value when doing pg_dump based backups Ah, now that makes more sense. -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] Differential backup
On Tue, 2010-04-27 at 12:14 -0400, Merlin Moncure wrote: On Tue, Apr 27, 2010 at 11:13 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Merlin Moncure mmonc...@gmail.com wrote: The proposal only seems a win to me if a fair percentage of the larger files don't change, which strikes me as a relatively low level case to optimize for. That's certainly a situation we face, with a relatively slow WAN in the middle. http://archives.postgresql.org/pgsql-admin/2009-07/msg00071.php I don't know how rare or common that is. hm...interesting read. pretty clever. Your archiving requirements are high. With the new stuff (HS/SR) taken into consideration, would you have done your DR the same way if you had to do it all over again? Part of my concern here is that manual filesystem level backups are going to become an increasingly arcane method of doing things as the HS/SR train starts leaving the station. Actually the HS/SR speaks _for_ adding explicit change dates to files, as the mod times on slave side will be different, and you may still want to know when the table really was last modified hm, it would be pretty neat to see some of the things you do pushed into logical (pg_dump) style backups...with some enhancements so that it can skip tables haven't changed and are exhibited in a previously supplied dump. This is more complicated but maybe more useful for a broader audience? Yes, I see the main value in of this for pg_dump backups, as physical files already have this in terms of file ctime/mtime/atime Side question: is it impractical to backup via pg_dump a hot standby because of query conflict issues? merlin -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Differential backup
On Tue, 2010-04-27 at 10:32 -0400, Michael Tharp wrote: On 04/27/2010 09:59 AM, Kevin Grittner wrote: Under what circumstances would PostgreSQL modify a file without changing the last modified timestamp or the file size? Do all OSes have sub-second precision mtimes? Because otherwise I could see a scenario such at this: * File is modified * Backup inspects and copies the file in the same second * File is modified again in the same second, so the mtime doesn't change * Backup is run again some time later and sees that the mtime has not changed Even with microsecond precision this kind of scenario makes me squidgy, especially if some OSes decide that skipping frequent mtime updates is OK. To be on the safe side you need to record the latest table data change time _after_ the backup anyway, it is easy to wait a few secs to be sure. Florian's point about clock changes is also very relevant. Since Postgres has the capability to give a better answer about what is in the file, it would be best to use that. -- m. tharp -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Differential backup
On Tue, 2010-04-27 at 17:28 +0200, Csaba Nagy wrote: Hi all, On Tue, 2010-04-27 at 11:07 -0400, Merlin Moncure wrote: The block level case seems pretty much covered by the hot standby feature. One use case we would have is to dump only the changes from the last backup of a single table. This table takes 30% of the DB disk space, it is in the order of ~400GB, and it's only inserted, never updated, then after ~1 year the old entries are archived. There's ~10M new entries daily in this table. If the backup would be smart enough to only read the changed blocks (in this case only for newly inserted records), it would be a fairly big win... The standard trick for this kind of table is having this table partitioned by insertion date - this way you have two benefits: 1) you already know which table to backup (the latest, and maye one before that if you just switche to new one) 2) archiving will be fast (copy full latest table away and the truncate it) instead of slow (copy old enough records out, then do delete of the same records, both ow which are quite slow, and you also need to do vacuum after that, which is also slow on large tables) This would actually be a good sample case for tracking latest dml, except that in this particular corner case you can arrange for this yourself. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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)
On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. Actually, that's a good idea. But how will you handle tables? ...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] Add column if not exists (CINE)
Robert Haas wrote: On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. Actually, that's a good idea. But how will you handle tables? What do you mean? -- 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: WAL page magic number (was Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct)
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Browsing the history of XLOG_PAGE_MAGIC, it used to be incremented by one whenever the format changes, at least as long as it has been been in xlog_internal.h. It started at 0xD05B in 2005, and was incremented by one by each commit till 0xD062. But then the hot standby patch bumped it to 0xD166, and on March 19th Simon changed it to 0x9002, and on March 28th to 0x9003. Is there a plan somewhere on all that, or was it just random whacking? Random whacking. Simon seems to have decided to try to make the number mean something, but it never meant anything before. It's not a version number, it's a magic identifier. We might need to avoid certain values to prevent collisions with other file formats, so I'm suspicious of trying to make it match up with some expectation. Ok, for better or worse, I whacked it back to the old series. -- 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
Ok, I've finally committed the patch, using wal_level as the name of the GUC. -- 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] Add column if not exists (CINE)
Robert Haas wrote: On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. Actually, that's a good idea. But how will you handle tables? I think I Iike Heikki's suggestion better, to error out if the object exists but the properties differ. At least I'd like an option for that. cheers andrew -- 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 Wed, 2010-04-28 at 11:10 -0400, Robert Haas wrote: IIRC it was you that suggested changing the names of things if the behaviour changes. Absolutely, but I'm arguing that we shouldn't change the behavior in the first place. At least as I understand it... I feel like you're just arguing against whatever I say - your reasoning makes no sense. Masao would not have proposed it as a change if it already worked like that, would he? Just reading the thread would tell you that much. Plus, you clearly don't know how it works now, so not sure why you're commenting at all, its just minor stuff and a few ideas. -- 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] Differential backup
Hannu Krosing ha...@2ndquadrant.com wrote: On Tue, 2010-04-27 at 17:28 +0200, Csaba Nagy wrote: One use case we would have is to dump only the changes from the last backup of a single table. This table takes 30% of the DB disk space, it is in the order of ~400GB, and it's only inserted, never updated, then after ~1 year the old entries are archived. There's ~10M new entries daily in this table. If the backup would be smart enough to only read the changed blocks (in this case only for newly inserted records), it would be a fairly big win... That is covered pretty effectively in PITR-style backups with the hard link and rsync approach cited earlier in the thread. Those 1GB table segment files which haven't changed aren't read or written, and only those portions of the other files which have actually changed are sent over the wire (although the entire disk file is written on the receiving end). The standard trick for this kind of table is having this table partitioned by insertion date That doesn't always work. In our situation the supreme court sets records retention rules which can be quite complex, but usually key on *final disposition* of a case rather than insertion date; that is, the earliest date on which the data related to a case is *allowed* to be deleted isn't known until weeks or years after insertion. Additionally, it is the elected clerk of court in each county who determines when and if data for that county will be purged once it has reached the minimum retention threshold set by supreme court rules. That's not to say that partitioning couldn't help with some backup strategies; just that it doesn't solve all insert-only (with eventual purge) use cases. One of the nicest things about PostgreSQL is the availability of several easy and viable backup strategies, so that you can tailor one to fit your environment. -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] Add column if not exists (CINE)
Robert Haas robertmh...@gmail.com writes: Actually, that's a good idea. But how will you handle tables? Well, tables are a special case, mainly because it's not clear how to avoid accidentally throwing away data. (In particular if some column in the existing table isn't there in the new definition. It's a bit scary to just drop the column, IMO.) I don't see that that argument applies to doing an automatic ALTER COLUMN, though, especially since the only column type alterations that will go through without a USING clause are reasonably straightforward. 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] Add column if not exists (CINE)
On Wed, Apr 28, 2010 at 12:07 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. Actually, that's a good idea. But how will you handle tables? What do you mean? 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. ...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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, Apr 28, 2010 at 11:25 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: At least as I understand it, even when not using archive_mode, streaming replication, or hot standby, it's still perfectly legal to use pg_start_backup() to take a hot backup. Nope. The correct procedure to take a hot backup is described in http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-TIPS. It involves setting archive_mode=on, and archive_command to a shell command that normally just returns true, except when backup is in progress. You can't take a hot backup without archiving (or streaming) at least temporarily. (except with filesystem-level snapshot capabilities). Oh. Well, in that case the proposed change seems reasonable... but what do you mean by except with filesystem-level snapshot capabilities? ...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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, 2010-04-28 at 12:44 -0400, Robert Haas wrote: On Wed, Apr 28, 2010 at 11:25 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: At least as I understand it, even when not using archive_mode, streaming replication, or hot standby, it's still perfectly legal to use pg_start_backup() to take a hot backup. Nope. The correct procedure to take a hot backup is described in http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-TIPS. It involves setting archive_mode=on, and archive_command to a shell command that normally just returns true, except when backup is in progress. You can't take a hot backup without archiving (or streaming) at least temporarily. (except with filesystem-level snapshot capabilities). Oh. Well, in that case the proposed change seems reasonable... but what do you mean by except with filesystem-level snapshot capabilities? Like LVM, SANS or ZFS. Joshua D. Drake ...Robert -- 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas wrote: but what do you mean by except with filesystem-level snapshot capabilities? If you have a filesystem that supports atomic snapshots, you can take a snapshot of the filesystem the data directory resides on, and then copy the data directory from the snapshot at your leisure, without pg_start/stop_backup(). It is entirely invisible to PostgreSQL and works just like copying the data directory after an immediate shutdown. The server will perform crash recovery after restore. Virtualization software, logical volume managers and SANs tend to have such features, in addition to filesystems. -- 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
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Well, it would be nice to allow using pg_start_backup() on the primary when streaming replication is enabled, even if archiving isn't. Otherwise the only way to get the base backup for the standby is to shut down primary first, or use filesystem snapshot etc. I think I must be missing something: exactly how would you fire up a new standby from such a base backup, if you weren't running archiving? If you aren't archiving then there's no guarantee that you'll still have a continuous WAL series starting from the start of the backup. IOW I think that the requirement in pg_start_backup shouldn't be relaxed without some more thought/work. 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
IOW I think that the requirement in pg_start_backup shouldn't be relaxed without some more thought/work. Yeah, I was talking to Bruce about that this AM, and it seems like a feature we *need* to have ... for 9.1. I'm sufficiently concerned about the amount of flux HS/SR is in right now that I'd like to declare it good enough and move towards release. Otherwise we'll tinker with it forever and there will be no 9.0. Release early, release often *is* the OSS mantra, after all. The question now isn't Is binary replication perfect but is it *good enough* for some substantial portion of our users. And I think the answer to the latter question is, at this point, yes. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.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: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Well, it would be nice to allow using pg_start_backup() on the primary when streaming replication is enabled, even if archiving isn't. Otherwise the only way to get the base backup for the standby is to shut down primary first, or use filesystem snapshot etc. I think I must be missing something: exactly how would you fire up a new standby from such a base backup, if you weren't running archiving? I was replying to Robert's thought on using pg_start/stop_backup() for taking a hot backup. Not for bootstrapping a standby. If you aren't archiving then there's no guarantee that you'll still have a continuous WAL series starting from the start of the backup. I wasn't really thinking of this use case, but you could set wal_keep_segments high enough. Not a configuration I would recommend for high availability, but should be fine for setting up a streaming replication standby for testing etc. If we don't allow pg_start/stop_backup() with archive_mode=off and max_wal_senders0, there's no way to bootstrap a streaming replication standby without archiving. -- 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 Wed, 2010-04-28 at 11:11 -0700, Josh Berkus wrote: IOW I think that the requirement in pg_start_backup shouldn't be relaxed without some more thought/work. Yeah, I was talking to Bruce about that this AM, and it seems like a feature we *need* to have ... for 9.1. I'm sufficiently concerned about the amount of flux HS/SR is in right now that I'd like to declare it good enough and move towards release. Otherwise we'll tinker with it forever and there will be no 9.0. Release early, release often *is* the OSS mantra, after all. The question now isn't Is binary replication perfect but is it *good enough* for some substantial portion of our users. And I think the answer to the latter question is, at this point, yes. As of exactly today, my answer, for my piece of this is also yes. I'm not convinced that the same is true across the board. Some important changes have happened in last few days and I see more coming. -- 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] Add column if not exists (CINE)
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. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] explicit (void *) casts
I'm wondering the reason that I see (void *) casts for function call parameters which are pointers to specific structures. Do some compilers generate warnings if these are omitted? It's hard to believe that the generated code would differ without them. -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: If you aren't archiving then there's no guarantee that you'll still have a continuous WAL series starting from the start of the backup. I wasn't really thinking of this use case, but you could set wal_keep_segments high enough. Ah. Okay, that seems like a workable approach, at least for people with reasonably predictable WAL loads. We could certainly improve on it later to make it more bulletproof, but it's usable now --- if we relax the error checks. (wal_keep_segments can be changed without restarting, right?) Not a configuration I would recommend for high availability, but should be fine for setting up a streaming replication standby for testing etc. If we don't allow pg_start/stop_backup() with archive_mode=off and max_wal_senders0, there's no way to bootstrap a streaming replication standby without archiving. Right. +1 for weakening the tests, then. Is there any use in looking at wal_keep_segments as part of this test? 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Well, it would be nice to allow using pg_start_backup() on the primary when streaming replication is enabled, even if archiving isn't. Otherwise the only way to get the base backup for the standby is to shut down primary first, or use filesystem snapshot etc. I think I must be missing something: exactly how would you fire up a new standby from such a base backup, if you weren't running archiving? I was replying to Robert's thought on using pg_start/stop_backup() for taking a hot backup. Not for bootstrapping a standby. Scratch that, I just reread what I wrote, and starting a streaming replication standby from such a backup was exactly what I was describing.. If you aren't archiving then there's no guarantee that you'll still have a continuous WAL series starting from the start of the backup. I wasn't really thinking of this use case, but you could set wal_keep_segments high enough. Not a configuration I would recommend for high availability, but should be fine for setting up a streaming replication standby for testing etc. If we don't allow pg_start/stop_backup() with archive_mode=off and max_wal_senders0, there's no way to bootstrap a streaming replication standby without archiving. This still makes sense. -- 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] Add column if not exists (CINE)
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. 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: If you aren't archiving then there's no guarantee that you'll still have a continuous WAL series starting from the start of the backup. I wasn't really thinking of this use case, but you could set wal_keep_segments high enough. Ah. Okay, that seems like a workable approach, at least for people with reasonably predictable WAL loads. We could certainly improve on it later to make it more bulletproof, but it's usable now --- if we relax the error checks. Yeah, wal_keep_segments is wishy-woshy in general, not only with backups. (wal_keep_segments can be changed without restarting, right?) It's PG_SIGHUP. Not a configuration I would recommend for high availability, but should be fine for setting up a streaming replication standby for testing etc. If we don't allow pg_start/stop_backup() with archive_mode=off and max_wal_senders0, there's no way to bootstrap a streaming replication standby without archiving. Right. +1 for weakening the tests, then. Is there any use in looking at wal_keep_segments as part of this test? I don't think so. There's no safe setting that would guarantee anything. We could check for wal_keep_segments0, but any small number is the same practice. We don't insist on wal_keep_segments0 to allow WAL streaming without archival in general, let's not treat taking the base backup differently. -- 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] explicit (void *) casts
Kevin Grittner kevin.gritt...@wicourts.gov writes: I'm wondering the reason that I see (void *) casts for function call parameters which are pointers to specific structures. Do some compilers generate warnings if these are omitted? There are some places where we do that to suppress warnings, yes. Any particular place where you think it's unnecessary? 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Wed, 2010-04-28 at 14:21 -0400, Tom Lane wrote: Is there any use in looking at wal_keep_segments as part of this test? I would hope that pg_stop_backup() will have a conditional ERROR message to say ERROR backup inconsistent and cannot be used for SR HINT increase wal_keep_segments or enable archiving for your base backup I think it would also be useful to add a NOTICE to pg_start_backup() NOTICE archiving is not enabled. If we reach exceed wal_keep_segments WAL files then the backup will be invalidated. Expected time for this to happen is X (using linear extrapolation of WAL creation rate since last checkpoint) -- 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] Add column if not exists (CINE)
Robert Haas wrote: On Wed, Apr 28, 2010 at 12:07 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: On Wed, Apr 28, 2010 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't believe you are fairly stating the consensus from previous discussion and I believe that you are actually in the minority on this one. I agree that we probably don't need to support this for object types for which CREATE OR REPLACE is available or can be made available, but that isn't feasible for all object types - tables and columns being the obvious examples. What's obvious about it? In particular, I should think that ADD OR REPLACE COLUMN would usefully be defined as ADD if no such column, else ALTER COLUMN as necessary to match this spec. Dropping the ALTER part of that has no benefit except to lazy implementors; it certainly is not more useful to users if they can't be sure of the column properties after issuing the command. Actually, that's a good idea. But how will you handle tables? What do you mean? Well, how would you define CREATE OR REPLACE TABLE? It the table doesn't exist, create it. If it exists with the same name and same columns and constraints and all, do nothing. Otherwise throw an error. Maybe it should also check that the existing table is empty. -- 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] explicit (void *) casts
Tom Lane t...@sss.pgh.pa.us wrote: Any particular place where you think it's unnecessary? How about the 2nd (tag pointer) parameter for hash_search? -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
Simon Riggs wrote: On Wed, 2010-04-28 at 14:21 -0400, Tom Lane wrote: Is there any use in looking at wal_keep_segments as part of this test? I would hope that pg_stop_backup() will have a conditional ERROR message to say ERROR backup inconsistent and cannot be used for SR HINT increase wal_keep_segments or enable archiving for your base backup Hmm, you could start streaming the WAL before you start the backup, so the fact that you've already removed some segments that are needed to restore from the backup by the time pg_stop_backup() is called doesn't necessarily mean that the backup is useless. You'd need a stand-alone tool to do the streaming in that case, and no such tool exists yet, but I would be surprised if one doesn't appear on pgfoundry sooner or later :-). In case it's not clear to casual readers out there: You will get an error as soon as you try to start the standby, complaining that it can't find the WAL segment it needs in the primary anymore. Not silent corruption. -- 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] Add column if not exists (CINE)
--- Den ons 2010-04-28 skrev Tom Lane t...@sss.pgh.pa.us:
Fra: Tom Lane t...@sss.pgh.pa.us
Emne: Re: [HACKERS] Add column if not exists (CINE)
Til: Robert Haas robertmh...@gmail.com
Kopi: Andrew Dunstan and...@dunslane.net, Takahiro Itagaki
itagaki.takah...@oss.ntt.co.jp, Kjell Rune Skaaraas kjell...@yahoo.no,
pgsql-hackers@postgresql.org
Dato: Onsdag 28. april 2010 17.20
Robert Haas robertmh...@gmail.com
writes:
I don't believe you are fairly stating the consensus
from previous
discussion and I believe that you are actually in the
minority on this
one. I agree that we probably don't need to
support this for object
types for which CREATE OR REPLACE is available or can
be made
available, but that isn't feasible for all object
types - tables and
columns being the obvious examples.
What's obvious about it? In particular, I should
think that ADD OR
REPLACE COLUMN would usefully be defined as ADD if no such
column,
else ALTER COLUMN as necessary to match this spec.
Dropping the
ALTER part of that has no benefit except to lazy
implementors; it
certainly is not more useful to users if they can't be sure
of the
column properties after issuing the command.
To me this construct seems horribly ambigious. Imagine I did a ALTER TABLE foo
ADD COLUMN bar INTEGER NOT NULL UNIQUE DEFAULT 10, then an ALTER TABLE foo ADD
OR REPLACE COLUMN bar BIGINT. Would I get a BIGINT NOT NULL UNIQUE DEFAULT 10
or a plain BIGINT? Either way I think one group will be disappointed because it
either trashes all your other setup *or* forces you to call DROP NOT NULL, DROP
DEFAULT etc. when you don't want it.
There's a reason why there's no ALTER TABLE foo SET COLUMN bar [definition]
and instead many statements. Remember it has to deal with all these possible
column constraints in ADD COLUMN:
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
CHECK ( expression ) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH
SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
What about something like CHECK? Would you assume it's a complete set of CHECKs
and drop the rest? Or just keep creating new CHECKs every time it is run? Dupe
checking?
CINE has none of these problems, either the column didn't exist before so
there's nothing to worry about or the command does nothing. True, you may have
a borked column but not if you follow a simple design pattern of never
recasting a column type but rather add a new, migrate your data and update your
queries. And for the exceptions to that rule, you can add a ALTER COLUMN SET
DATA TYPE (or any of the other ALTERs) after the CINE in your scipt. If the
CINE triggered all is the latest version, if not the detailed ALTERs will
change any column that needs changing. Clean and simple.
Regards,
Kjell Rune
--
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] Differential backup
On Tue, Apr 27, 2010 at 8:15 PM, Craig Ringer cr...@postnewspapers.com.au wrote: Another avenue possibly worth investigating may be using the in-heap mvcc information to do SQL-level differential backups of individual tables or of the whole database. think: You can't use the mvcc information to do incremental backups because transactions don't necessarily commit or take snapshots in sequential order. A row can be inserted by transaction id 500 and then later updated by transaction id 400 and then later deleted by transaction id 300. I think what you need to use is the LSN on the page. Normally when you take a hot backup you note the LSN at the start and end of the backup and know that you need to replay that range of logs to have a consistent restore. An incremental backup would be the same except it would only back up any blocks that have an LSN the start of the last backup. Then you could restore the previous backup, apply this incremental to bring the restore up to the beginning of this backup, then apply the logs for the range of this backup. -- greg -- 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)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Robert Haas wrote: Well, how would you define CREATE OR REPLACE TABLE? It the table doesn't exist, create it. If it exists with the same name and same columns and constraints and all, do nothing. Otherwise throw an error. Maybe it should also check that the existing table is empty. The last bit doesn't seem to make sense. If you want an empty table, you can do DROP IF EXISTS and then CREATE. ISTM that the use-cases where you don't want to do that are cases where you don't want to lose existing data. For either CINE or COR, there are a number of issues that are being hand-waved away here: is it OK to change ownership and/or permissions? What about foreign key constraints relating this table to others? For that matter it's not real clear that indexes, check constraints, etc should be allowed to survive. If they are allowed to survive then CINE TABLE is just the tip of the iceberg: to do anything useful you'd also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc. And the more of those you add, the more problematic it gets to allow existing objects that don't quite match what the command says. Any of these commands are headache-y for something as complicated as a table. I'm not at all impressed by the argument that mysql does it, because they are *notorious* for being willing to ship half-baked solutions. 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
* Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100428 14:49]: You'd need a stand-alone tool to do the streaming in that case, and no such tool exists yet, but I would be surprised if one doesn't appear on pgfoundry sooner or later :-). And this tool is something I will eventually be interested in working on or collaborating on... I'm hoping to be able to build a tool that: 1) Connects to PG walsender (a la walreceiver) 2) Streams WAL from pg master 3) Saves WAL into files (a la archive)... i.e. I'm looking to keep a more-up-to-date PITR archive than waiting for traditional WAL file archiving... And eventually (9.1+) I'm hoping that walsender will have grown enough to allow me to configure PG to wait on the commit until the master has both sync'ed the WAL file, and received a sync ack from my wal-stream-save-to-file tool... Because then I'll have a situation where I can easily have a synchronous, separate machine copy of all my WAL without having to jump through hoops with stuff like drbd or MD+nbd, etc as my WAL disk... And yes, I don't personally care about streaming replication replaying WAL as it comes, or running queries in recovery... I'm looking towards PG not saying my transaction is committed unless it's safely on that machines disks (or BBcache) *and* another machine... That's the type of replication a paranoid guy like me waits for... Yes, that's possible now with exotic os/net/fs configuration, but imagine how nice it will be when it can all be done in userspace with just PG (and pg-compatible) tool, etc... -- 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Hmm, you could start streaming the WAL before you start the backup, so the fact that you've already removed some segments that are needed to restore from the backup by the time pg_stop_backup() is called doesn't necessarily mean that the backup is useless. You'd need a stand-alone tool to do the streaming in that case, and no such tool exists yet, but I would be surprised if one doesn't appear on pgfoundry sooner or later :-). 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. 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Aidan Van Dyk ai...@highrise.ca wrote: I'm hoping to be able to build a tool that: 1) Connects to PG walsender (a la walreceiver) 2) Streams WAL from pg master 3) Saves WAL into files (a la archive)... i.e. I'm looking to keep a more-up-to-date PITR archive than waiting for traditional WAL file archiving... I'm interested in that, too. I don't personally care about streaming replication replaying WAL as it comes, or running queries in recovery... I'm with you that far, but I wouldn't want the sender to wait for remote persistence. -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
Kevin Grittner kevin.gritt...@wicourts.gov writes: Aidan Van Dyk ai...@highrise.ca wrote: I'm hoping to be able to build a tool that: 1) Connects to PG walsender (a la walreceiver) 2) Streams WAL from pg master 3) Saves WAL into files (a la archive)... i.e. I'm looking to keep a more-up-to-date PITR archive than waiting for traditional WAL file archiving... I'm interested in that, too. That looks like we have that integrated into walreceiver the day we have cascading support, right? Or maybe we need a special mode of operation where the receiver is (talking to) an archiver. I don't personally care about streaming replication replaying WAL as it comes, or running queries in recovery... I'm with you that far, but I wouldn't want the sender to wait for remote persistence. That's synchronous replication and its set of synchronicity setting, ranging from sent on the network to the slave, fsync()ed at the slave and applied already on the slave. 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. SyncRep in 9.1 already sounds darn interesting :) 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
* Kevin Grittner kevin.gritt...@wicourts.gov [100428 15:51]: I don't personally care about streaming replication replaying WAL as it comes, or running queries in recovery... I'm with you that far, but I wouldn't want the sender to wait for remote persistence. I remember a presentation at pgcon a while ago, it was probaly Fujii (from NTT?) about their log streaming, and at that time, they talked about different sync options... So I'ld love to be able to have comits be: async (like current option) local wal sync (like current) local wal sync + walsender sent local wal sync + walsender confirmed And ideally, the walsender sent/confirmed would even allow making sure it was sent/confirmed to $X connections... I want to be able to guarantee it's on 2 machines, not that if my slave was connected it would be on there, but something happened and my slave has disconnected, so it's only got local WAL... And then on whatever tool is receiving the log streaming, it can be set to confirm when either: received buffer write buffer to file write buffer to file + sync write buffer to file + sync + replay That should give you all the sync levels they talked about in their presentation... -- 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: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
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 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
[HACKERS] Toast rel options
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? -- 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] Add column if not exists (CINE)
On Wed, Apr 28, 2010 at 2:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Robert Haas wrote: Well, how would you define CREATE OR REPLACE TABLE? It the table doesn't exist, create it. If it exists with the same name and same columns and constraints and all, do nothing. Otherwise throw an error. Maybe it should also check that the existing table is empty. The last bit doesn't seem to make sense. If you want an empty table, you can do DROP IF EXISTS and then CREATE. ISTM that the use-cases where you don't want to do that are cases where you don't want to lose existing data. Right. For either CINE or COR, there are a number of issues that are being hand-waved away here: is it OK to change ownership and/or permissions? What about foreign key constraints relating this table to others? For that matter it's not real clear that indexes, check constraints, etc should be allowed to survive. If they are allowed to survive then CINE TABLE is just the tip of the iceberg: to do anything useful you'd also need CINE for ADD CONSTRAINT, CREATE INDEX, ADD FOREIGN KEY, etc. And the more of those you add, the more problematic it gets to allow existing objects that don't quite match what the command says. Any of these commands are headache-y for something as complicated as a table. I'm not at all impressed by the argument that mysql does it, because they are *notorious* for being willing to ship half-baked solutions. We can artificially make this problem as complicated as we wish, but the people who are asking for this feature (including me) will, I believe, be quite happy with a solution that throws, say, a NOTICE instead of an ERROR when the object already exists, and then returns without doing anything further. There are very few, if any, definitional issues here, except by people who are brainstorming crazy alternative behaviors whose actual usefulness I very much doubt. CREATE OR REPLACE is indeed much more complicated. In fact, for tables, I maintain that you'll need to link with -ldwim to make it work 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] Toast rel options
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. 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. 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] Add column if not exists (CINE)
Robert Haas robertmh...@gmail.com writes: We can artificially make this problem as complicated as we wish, but the people who are asking for this feature (including me) will, I believe, be quite happy with a solution that throws, say, a NOTICE instead of an ERROR when the object already exists, and then returns without doing anything further. There are very few, if any, definitional issues here, except by people who are brainstorming crazy alternative behaviors whose actual usefulness I very much doubt. CREATE OR REPLACE is indeed much more complicated. In fact, for tables, I maintain that you'll need to link with -ldwim to make it work properly. This may in fact be an appropriate way to handle the case for tables, given the complexity of their definitions. However, the original point of the thread was about what to do for columns. I still say that COR rather than CINE semantics would be appropriate for columns. 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Aidan Van Dyk wrote: I remember a presentation at pgcon a while ago, it was probaly Fujii (from NTT?) about their log streaming, and at that time, they talked about different sync options... It's all outlined at http://wiki.postgresql.org/wiki/Streaming_Replication#Synchronization_capability -- 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
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. Ultimately the commit is stuck waiting for the slowest committing sync operation on the list; it's the bottleneck. Let's presume that the commit waits can be done in parallel, after sending the transaction to every slave. Given that and the situation you describe, having per-node sync levels only turns out to be a useful optimization if the reporting slave commits slower than the failover slave does. The master is going to be stuck waiting for the slowest one of the batch regardless of whether you've optimized them individually. There is a related situation that I think a per-node sync option would be more obviously useful for: local failover slave, remote disaster recovery slave over a WAN, where you accept that a serious disaster taking out a whole data center will lose some transactions. In that situation, you'd probably want fsync for the local slave, while going async for the remote datacenter. If the commits are done in a serial fashion, tuning sync per-node would be much more valuable in many use cases. Regardless, I wouldn't want to burden the first sync rep version with this requirement. Let's wait until the current scope is cleared before trying to move the goalposts for the people working on that. -- 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, 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.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
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] XML Todo List
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. :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
