Re: [HACKERS] pg_receivexlog and replication slots
On Fri, Oct 3, 2014 at 8:57 PM, Andres Freund and...@2ndquadrant.com wrote: para +applicationpg_receivexlog/application can run in one of two following +modes, which control physical replication slot: I don't think that's good enough. There's also the important mode where it's not doing --create/--drop at all. Well, yes, however the third mode is not explicitly present, and I don't see much point in adding a --start mode thinking backward-compatibility. Now, I refactored a bit the documentation to mention that pg_receivexlog can perform additional actions to control replication slots. I added as well in the portion of option --slot how it interacts with --create-slot and --drop-slot. + if (db_name) + { + fprintf(stderr, + _(%s: database defined for replication connection \%s\\n), + progname, replication_slot); + disconnect_and_exit(1); + } I don't like 'defined' here. 'replication connection unexpectedly is database specific' or something would be better. Sure, IMO the error message should as well mention the replication slot being used, so I reformulated as such: replication connection using slot foo is unexpectedly database specific I do wonder whether --create/--drop aren't somewhat weird for pg_receivexlog. It's not that clear what it means. It'd be ugly, but we could rename them --create-slot/drop-slot. In line with the other patch sent earlier, options are renamed to --create-slot and --drop-slot. Regards, -- Michael From aba831b62795303ec666b8c18810f404458d8acd Mon Sep 17 00:00:00 2001 From: Michael Paquier mich...@otacoo.com Date: Mon, 1 Sep 2014 20:53:45 +0900 Subject: [PATCH] Support for replslot creation and drop in pg_receivexlog Using the new actions --create-slot and --drop-slot that are similarly present in pg_recvlogical, a user can respectively create and drop a replication slot that can be used afterwards when fetching WALs. --- doc/src/sgml/ref/pg_receivexlog.sgml | 31 ++- src/bin/pg_basebackup/pg_receivexlog.c | 154 + 2 files changed, 169 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/ref/pg_receivexlog.sgml b/doc/src/sgml/ref/pg_receivexlog.sgml index 5916b8f..72290e5 100644 --- a/doc/src/sgml/ref/pg_receivexlog.sgml +++ b/doc/src/sgml/ref/pg_receivexlog.sgml @@ -255,7 +255,9 @@ PostgreSQL documentation to make sure that applicationpg_receivexlog/ cannot become the synchronous standby through an incautious setting of xref linkend=guc-synchronous-standby-names; it does not flush - data frequently enough for this to work correctly. + data frequently enough for this to work correctly. In + option--create-slot/option mode, create the slot with this name. + In option--drop-slot/option mode, delete the slot with this name. /para /listitem /varlistentry @@ -263,6 +265,33 @@ PostgreSQL documentation /para para +applicationpg_receivexlog/application can perform one of the two +following actions in order to control physical replication slots: + +variablelist + varlistentry + termoption--create-slot/option/term + listitem + para +Create a new physical replication slot with the name specified in +option--slot/option, then start stream. + /para + /listitem + /varlistentry + + varlistentry + termoption--drop-slot/option/term + listitem + para +Drop the replication slot with the name specified in +option--slot/option, then exit. + /para + /listitem + /varlistentry +/variablelist + /para + + para Other options are also available: variablelist diff --git a/src/bin/pg_basebackup/pg_receivexlog.c b/src/bin/pg_basebackup/pg_receivexlog.c index 171cf43..5b71f85 100644 --- a/src/bin/pg_basebackup/pg_receivexlog.c +++ b/src/bin/pg_basebackup/pg_receivexlog.c @@ -38,11 +38,15 @@ static int noloop = 0; static int standby_message_timeout = 10 * 1000; /* 10 sec = default */ static int fsync_interval = 0; /* 0 = default */ static volatile bool time_to_abort = false; +static bool do_create_slot = false; +static bool do_drop_slot = false; static void usage(void); +static DIR* get_destination_dir(char *dest_folder); +static void close_destination_dir(DIR *dest_dir, char *dest_folder); static XLogRecPtr FindStreamingStart(uint32 *tli); -static void StreamLog(); +static void StreamLog(void); static bool stop_streaming(XLogRecPtr segendpos, uint32 timeline, bool segment_finished); @@ -78,6 +82,9 @@ usage(void) printf(_( -w, --no-password never prompt for password\n)); printf(_( -W, --password force password prompt (should happen automatically)\n)); printf(_( -S, --slot=SLOTNAMEreplication slot to use\n)); + printf(_(\nOptional actions:\n)); + printf(_(
Re: [HACKERS] How to make ResourceOwnerForgetBuffer() O(1), instead of O(N^2) scale
Heikki Linnakangas hlinnakan...@vmware.com writes: On 10/03/2014 07:08 AM, Kouhei Kaigai wrote: What is the best way to solve the problem? How about creating a separate ResourceOwner for these buffer pins, and doing a wholesale ResourceOwnerRelease() on it when you're done? That's a thought. Another point is that if you can release the buffer pins in reverse order of acquisition, the existing ResourceOwner code works just fine. I have a larger question though: how is it useful to transfer raw contents of shared buffers to a GPU in the first place? Surely you're not going to be putting tasks like tuple visibility verification into the GPU. So it seems like this whole thread is based on a dubious architectural assumption. 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] [RFC] Incremental backup v2: add backup profile to base backup
On Sat, Oct 4, 2014 at 12:31 AM, Marco Nenciarini marco.nenciar...@2ndquadrant.it wrote: Compared to first version, we switched from a timestamp+checksum based approach to one based on LSN. Cool. This patch adds an option to pg_basebackup and to replication protocol BASE_BACKUP command to generate a backup_profile file. It is almost useless by itself, but it is the foundation on which we will build the file based incremental backup (and hopefully a block based incremental backup after it). Hm. I am not convinced by the backup profile file. What's wrong with having a client send only an LSN position to get a set of files (or partial files filed with blocks) newer than the position given, and have the client do all the rebuild analysis? Any comment will be appreciated. In particular I'd appreciate comments on correctness of relnode files detection and LSN extraction code. Please include some documentation with the patch once you consider that this is worth adding to a commit fest. This is clearly WIP yet so it does not matter much, but that's something not to forget. Regards, -- Michael -- 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] How to make ResourceOwnerForgetBuffer() O(1), instead of O(N^2) scale
On 03/10/2014 05:53, Kouhei Kaigai wrote: Yep, that's my pain. Even though usual query does not take many buffers pinned, my use case needs to fetch megabytes scale data at once because of performance reason; page-by-page synchronous scan makes GPU being idle. Doesn't your GPU have an async queue and exec mechanism? Then you could do an asyn DMA to the GPU with an event, use that event in he GPU to start the kernel and in the DB to release the pin? That is exactly what I'm doing now. Problem is, it needs to keep multiple buffers (likely, more than a few dozen thousands) in the queue not to make GPU waiting for the data to be calculated. Thus, I want to pin relatively many buffers than usual query workloads. Thanks, -- NEC OSS Promotion Center / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.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] How to make ResourceOwnerForgetBuffer() O(1), instead of O(N^2) scale
Heikki Linnakangas hlinnakan...@vmware.com writes: On 10/03/2014 07:08 AM, Kouhei Kaigai wrote: What is the best way to solve the problem? How about creating a separate ResourceOwner for these buffer pins, and doing a wholesale ResourceOwnerRelease() on it when you're done? That's a thought. Another point is that if you can release the buffer pins in reverse order of acquisition, the existing ResourceOwner code works just fine. Yep, I'm now trying. I have a larger question though: how is it useful to transfer raw contents of shared buffers to a GPU in the first place? Surely you're not going to be putting tasks like tuple visibility verification into the GPU. So it seems like this whole thread is based on a dubious architectural assumption. In my implementation, it is a job of GPU to check tuple visibility, then it also send GPU an array of visible item index, in addition to the buffer contents itself. GPU code can pick up only visible tuples using this index. OLAP workloads tends to have all-visible pages, so cost of visibility check was not expensive. Thanks, -- NEC OSS Promotion Center / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.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] Fixed xloginsert_locks for 9.4
On Fri, Oct 3, 2014 at 08:04:08PM -0400, Bruce Momjian wrote: On Sat, Oct 4, 2014 at 01:57:01AM +0200, Andres Freund wrote: On 2014-10-03 19:54:36 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Agreeed. Also, reality check --- we can't change postgresql.conf easily without an initdb, and I think our last 9.4 initdb is going to be 9.4beta3, which is going to be packaged on Monday. Good point: independently of all else, it's a bit late to be adding new features to 9.4. This is getting absurd. The feature was there three days ago. My point is we have to have in there/decide by Monday or it isn't going to be in 9.4. I am unclear how we can resolve all the concerns about its addition in that short of a time. Actually, another issue is that we were only able to remove xloginsert_locks this week because we are forcing an initdb for 9.4beta3. If we had not, I am not sure how we would have removed this undocumented setting, except to tell beta users to remove it from their postgresql.conf file after we removed it from the server. This seems sloppy --- we should have addressed this (documented it or removed it) before beta1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Fixed xloginsert_locks for 9.4
On 2014-10-04 11:07:13 -0400, Bruce Momjian wrote: On Fri, Oct 3, 2014 at 08:04:08PM -0400, Bruce Momjian wrote: On Sat, Oct 4, 2014 at 01:57:01AM +0200, Andres Freund wrote: On 2014-10-03 19:54:36 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Agreeed. Also, reality check --- we can't change postgresql.conf easily without an initdb, and I think our last 9.4 initdb is going to be 9.4beta3, which is going to be packaged on Monday. Good point: independently of all else, it's a bit late to be adding new features to 9.4. This is getting absurd. The feature was there three days ago. My point is we have to have in there/decide by Monday or it isn't going to be in 9.4. I am unclear how we can resolve all the concerns about its addition in that short of a time. Actually, another issue is that we were only able to remove xloginsert_locks this week because we are forcing an initdb for 9.4beta3. If we had not, I am not sure how we would have removed this undocumented setting, except to tell beta users to remove it from their postgresql.conf file after we removed it from the server. This seems sloppy --- we should have addressed this (documented it or removed it) before beta1. It was marked as GUC_NOT_IN_SAMPLE. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_receivexlog and replication slots
On 2014-10-04 09:24:07 +0900, Michael Paquier wrote: On Sat, Oct 4, 2014 at 6:48 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-03 14:02:08 -0400, Robert Haas wrote: On Fri, Oct 3, 2014 at 7:57 AM, Andres Freund and...@2ndquadrant.com wrote: I do wonder whether --create/--drop aren't somewhat wierd for pg_receivexlog. It's not that clear what it means. It'd be ugly, but we could rename them --create-slot/drop-slot. +1 on doing it, -1 on it being ugly. The reason I'm calling it uglyu is that it's different from pg_recvlogical. We could change it there, too? A bit late, but probably better than having a discrepancy forever I'm on board to make things as consistent as possible between both utilities, the only reason why --create/--drop are used in my patch is for the sake of consistency btw. 9.4 ship has not sailed yet, and IMO it is important from the user prospective if options are a maximum consistent between pg_receivexlog and pg_recvlogical. That would be even better if change is done before 9.4beta3 shows up, and I doubt that there are many users using the --create/--drop options already. Any opinion on whether whe should accept both --create and --create-slot or only the latter? Accepting both would get rid of problems due to potential usages of the old syntax - and it's easier to type... I don't really care. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_receivexlog and replication slots
On 2014-10-04 14:25:27 +0900, Michael Paquier wrote: On Sat, Oct 4, 2014 at 9:24 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Sat, Oct 4, 2014 at 6:48 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-10-03 14:02:08 -0400, Robert Haas wrote: On Fri, Oct 3, 2014 at 7:57 AM, Andres Freund and...@2ndquadrant.com wrote: I do wonder whether --create/--drop aren't somewhat wierd for pg_receivexlog. It's not that clear what it means. It'd be ugly, but we could rename them --create-slot/drop-slot. +1 on doing it, -1 on it being ugly. The reason I'm calling it uglyu is that it's different from pg_recvlogical. We could change it there, too? A bit late, but probably better than having a discrepancy forever I'm on board to make things as consistent as possible between both utilities, the only reason why --create/--drop are used in my patch is for the sake of consistency btw. 9.4 ship has not sailed yet, and IMO it is important from the user prospective if options are a maximum consistent between pg_receivexlog and pg_recvlogical. That would be even better if change is done before 9.4beta3 shows up, and I doubt that there are many users using the --create/--drop options already. And as I am on it, attached is a patch that can be applied to master and REL9_4_STABLE to rename the --create and --drop to --create-slot and --drop-slot. Misses logicaldecoding.sgml... I'll fix that up, once there's agreement whether we should continue accept the old form. No need to send a new version. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Fixed xloginsert_locks for 9.4
On Sat, Oct 4, 2014 at 05:08:17PM +0200, Andres Freund wrote: On 2014-10-04 11:07:13 -0400, Bruce Momjian wrote: On Fri, Oct 3, 2014 at 08:04:08PM -0400, Bruce Momjian wrote: On Sat, Oct 4, 2014 at 01:57:01AM +0200, Andres Freund wrote: On 2014-10-03 19:54:36 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Agreeed. Also, reality check --- we can't change postgresql.conf easily without an initdb, and I think our last 9.4 initdb is going to be 9.4beta3, which is going to be packaged on Monday. Good point: independently of all else, it's a bit late to be adding new features to 9.4. This is getting absurd. The feature was there three days ago. My point is we have to have in there/decide by Monday or it isn't going to be in 9.4. I am unclear how we can resolve all the concerns about its addition in that short of a time. Actually, another issue is that we were only able to remove xloginsert_locks this week because we are forcing an initdb for 9.4beta3. If we had not, I am not sure how we would have removed this undocumented setting, except to tell beta users to remove it from their postgresql.conf file after we removed it from the server. This seems sloppy --- we should have addressed this (documented it or removed it) before beta1. It was marked as GUC_NOT_IN_SAMPLE. Oh, that's interesting. Thank you. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Log notice that checkpoint is to be written on shutdown
Am Freitag, den 03.10.2014, 12:07 -0300 schrieb Alvaro Herrera: Michael Banck wrote: diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 5a4dbb9..f2716ae 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -8085,10 +8085,14 @@ CreateCheckPoint(int flags) /* * If enabled, log checkpoint start. We postpone this until now so as not -* to log anything if we decided to skip the checkpoint. +* to log anything if we decided to skip the checkpoint. If we are during +* shutdown and checkpoints are not being logged, add a log message that a +* checkpoint is to be written and shutdown is potentially delayed. */ if (log_checkpoints) LogCheckpointStart(flags, false); + else if (shutdown) + ereport(LOG, (errmsg(waiting for checkpoint ...))); TRACE_POSTGRESQL_CHECKPOINT_START(flags); I think if we're going to emit log messages for shutdown checkpoints, we ought to use the same format we already have, i.e. instead of having the separate waiting for checkpoint message, just test log_checkpoints || shutdown, then LogCheckpointStart. I considered this at first, but the rationale is that if somebody sets log_checkpoints = off, they probably don't want the actual checkpoint to be logged, so just a note that a checkpoint is happening was better (Christoph Berg pointed this out). If there is consensus that forcing this one-time checkpoint logging is fine, I can change the patch accordingly. And for consistency also make sure that the checkpoint end log line is also reported on shutdown checkpoints regardless of log_checkpoints. I did this in an earlier draft of the patch, but AIUI this either requires some refactoring to not evaluate log_checkpoints inside LogCheckpointEnd(), and/or to change the function signature of LogCheckpointEnd() to include either the `flags' bitfield or the `shutdown' bool in order to force logging of the finished checkpoint even if log_checkpoints is set to `off'. Cheers, Michael -- Michael Banck Projektleiter / Berater Tel.: +49 (2161) 4643-171 Fax: +49 (2161) 4643-100 Email: michael.ba...@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Hohenzollernstr. 133, 41061 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer -- 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] Log notice that checkpoint is to be written on shutdown
On Thu, Oct 2, 2014 at 6:21 AM, Michael Banck michael.ba...@credativ.de wrote: Hi, we have seen repeatedly that users can be confused about why PostgreSQL is not shutting down even though they requested it. Usually, this is because `log_checkpoints' is not enabled and the final checkpoint is being written, delaying shutdown. As no message besides shutting down is written to the server log in this case, we even had users believing the server was hanging and pondering killing it manually. Wouldn't a better place to write this message be the terminal from which pg_ctl stop was invoked, rather than the server log file? Cheers, Jeff
Re: [HACKERS] Aussie timezone database changes incoming
I wrote: Martijn van Oosterhout klep...@svana.org writes: Indeed, this has been a pain in the ass for a long long time. It's good news that people think this will be an improvement. I've not dug into the change details to be sure, but I think probably I was overthinking it upthread. We seem to already have some of the new abbreviations installed, and the other ones do not conflict with anything. So we'll just add them and be happy. What we should do with the Australia abbreviations file is re-document it as being appropriate for historical usage only. Anyone who's got that selected will continue to see the behavior they did before. I've committed changes for this in advance of the upcoming 9.4beta3 release. Hopefully, if this is seriously bad for anyone, we'll hear about it from beta testers before it gets into any official back-branch releases. 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] proposal: doc: simplify examples of dynamic SQL
On 10/2/14, 11:06 PM, David G Johnston wrote: Jim Nasby-5 wrote On 10/2/14, 6:51 AM, Pavel Stehule wrote: EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L', colname, keyvalue) or -1, because of quoting issues EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1', colname) USING keyvalue; Better, but I think it should really be quote_ident( colname ) http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE The use of %I and %L solve all quoting issues when using format(); they likely call the relevant quote_ function on the user's behalf. Right. Duh. A old examples are very instructive, but little bit less readable and maybe too complex for beginners. Opinions? Honestly, I'm not to fond of either. format() is a heck of a lot nicer than a forest of ||'s, but I think it still falls short of what we'd really want here which is some kind of variable substitution or even a templating language. IE: EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue'; Putting that example into the docs isn't a good idea...it isn't valid in PostgreSQL ;) My point was that format() still isn't what we really need for dynamic SQL, and we should come up with something better. -- 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] Trailing comma support in SELECT statements
On 10/3/14, 4:02 PM, David G Johnston wrote: Should we also allow: SELECT , col1 , col2 , col3 FROM ... ? I would say yes, if we're going to do this. I don't see it being any worse than trailing commas. If we are going to do this, we need to do it EVERYWHERE. FWIW, the way I normally work around this problem is: SELECT blah , foo , bar , baz In my experience, it's quite uncommon to mess with the first item in the list, which mostly eliminates the issue. A missing leading comma is also MUCH easier to spot than a missing trailing comma. -- 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] Aussie timezone database changes incoming
On Sat, Oct 4, 2014 at 02:21:24PM -0400, Tom Lane wrote: I wrote: Martijn van Oosterhout klep...@svana.org writes: Indeed, this has been a pain in the ass for a long long time. It's good news that people think this will be an improvement. I've not dug into the change details to be sure, but I think probably I was overthinking it upthread. We seem to already have some of the new abbreviations installed, and the other ones do not conflict with anything. So we'll just add them and be happy. What we should do with the Australia abbreviations file is re-document it as being appropriate for historical usage only. Anyone who's got that selected will continue to see the behavior they did before. I've committed changes for this in advance of the upcoming 9.4beta3 release. Hopefully, if this is seriously bad for anyone, we'll hear about it from beta testers before it gets into any official back-branch releases. The changes for the Russian Federation timezones taking effect October 26 reinforces our need to get a new set of minor releases out soon. In fact, those storing future dates might already need those updates. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Aussie timezone database changes incoming
On 10/4/14, 2:58 PM, Bruce Momjian wrote: I've committed changes for this in advance of the upcoming 9.4beta3 release. Hopefully, if this is seriously bad for anyone, we'll hear about it from beta testers before it gets into any official back-branch releases. The changes for the Russian Federation timezones taking effect October 26 reinforces our need to get a new set of minor releases out soon. In fact, those storing future dates might already need those updates. This is why I wish we had a data type that stored the timezone that was originally in effect. :/ -- 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] Log notice that checkpoint is to be written on shutdown
On 10/4/14, 1:21 PM, Jeff Janes wrote: On Thu, Oct 2, 2014 at 6:21 AM, Michael Banck michael.ba...@credativ.de mailto:michael.ba...@credativ.de wrote: Hi, we have seen repeatedly that users can be confused about why PostgreSQL is not shutting down even though they requested it. Usually, this is because `log_checkpoints' is not enabled and the final checkpoint is being written, delaying shutdown. As no message besides shutting down is written to the server log in this case, we even had users believing the server was hanging and pondering killing it manually. Wouldn't a better place to write this message be the terminal from which pg_ctl stop was invoked, rather than the server log file? +1 Or do both. I suspect elog( INFO, ... ) might do that. -- 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] Replication identifiers, take 3
On 10/2/14, 7:28 AM, Robert Haas wrote: On Thu, Oct 2, 2014 at 4:49 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: An origin column in the table itself helps tremendously to debug issues with the replication system. In many if not most scenarios, I think you'd want to have that extra column, even if it's not strictly required. I like a lot of what you wrote here, but I strongly disagree with this part. A good replication solution shouldn't require changes to the objects being replicated. I agree that asking users to modify objects is bad, but I also think that if you do have records coming into one table from multiple sources then you will need to know what system they originated on. Maybe some sort of hidden column would work here? That means users don't need to modify anything (including anything doing SELECT *), but the data is there. As for space concerns I think the answer there is to somehow normalize the identifiers themselves. That has the added benefit of allowing a rename of a source to propagate to all the data already replicated from that source. -- 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] Aussie timezone database changes incoming
On Sat, Oct 4, 2014 at 03:01:45PM -0500, Jim Nasby wrote: On 10/4/14, 2:58 PM, Bruce Momjian wrote: I've committed changes for this in advance of the upcoming 9.4beta3 release. Hopefully, if this is seriously bad for anyone, we'll hear about it from beta testers before it gets into any official back-branch releases. The changes for the Russian Federation timezones taking effect October 26 reinforces our need to get a new set of minor releases out soon. In fact, those storing future dates might already need those updates. This is why I wish we had a data type that stored the timezone that was originally in effect. :/ Uh, if we stored the _offset_ that was in effect at the time of storage, it would actually be _worse_ because the new timezone database would not adjust existing stored values. If we stored the name of the time zone, I am not sure how that would help us here. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Aussie timezone database changes incoming
Bruce Momjian br...@momjian.us writes: On Sat, Oct 4, 2014 at 02:21:24PM -0400, Tom Lane wrote: I've committed changes for this in advance of the upcoming 9.4beta3 release. Hopefully, if this is seriously bad for anyone, we'll hear about it from beta testers before it gets into any official back-branch releases. The changes for the Russian Federation timezones taking effect October 26 reinforces our need to get a new set of minor releases out soon. In fact, those storing future dates might already need those updates. Well, the other side of that coin is that those new abbreviation values aren't valid *yet*. It's becoming clear to me that our existing design whereby zone abbreviations represent fixed GMT offsets isn't really good enough. I've been wondering whether we could change things so that, for instance, EDT means daylight time according to America/New_York and the system would consult the zic database to find out what the prevailing GMT offset was in that zone on that date. This would be a lot more robust in the face of the kind of foolishness we now see actually goes on. 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] Aussie timezone database changes incoming
On Sat, Oct 4, 2014 at 05:03:24PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Sat, Oct 4, 2014 at 02:21:24PM -0400, Tom Lane wrote: I've committed changes for this in advance of the upcoming 9.4beta3 release. Hopefully, if this is seriously bad for anyone, we'll hear about it from beta testers before it gets into any official back-branch releases. The changes for the Russian Federation timezones taking effect October 26 reinforces our need to get a new set of minor releases out soon. In fact, those storing future dates might already need those updates. Well, the other side of that coin is that those new abbreviation values aren't valid *yet*. It's becoming clear to me that our existing design whereby zone abbreviations represent fixed GMT offsets isn't really good enough. I've been wondering whether we could change things so that, for instance, EDT means daylight time according to America/New_York and the system would consult the zic database to find out what the prevailing GMT offset was in that zone on that date. This would be a lot more robust in the face of the kind of foolishness we now see actually goes on. I see: SET timezone = 'GMT'; SELECT '1901-01-01 00:00:00 EDT'::timestamptz; timestamptz 1901-01-01 04:00:00+00 SELECT '1901-01-01 00:00:00 EST'::timestamptz; timestamptz 1901-01-01 05:00:00+00 This is returning adjustements for EDT in a year when there was not daylight savings time. How are Russians supposed to deploy Postgres on October 26 if they use abbeviations? At midnight? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] NEXT VALUE FOR sequence
On 3 October 2014 00:18, Tom Lane t...@sss.pgh.pa.us wrote: Thomas Munro mu...@ip9.org writes: I suppose one approach would be to use command IDs as the scope. The spec clearly says one value per row, not one per statement; so command ID is very definitely not the right thing. I think (command ID, estate-es_processed) would work. Tracking those two values in SeqTableData would allow you to detect the level change meaning the next tuple has been returned by a SELECT, updated by an UPDATE or inserted by an INSERT. This could be activated by a new 2-argument nextval with a boolean argument to request the standard behaviour. Then NEXT VALUE FOR could be translated to nextval(..., true). But I just can't figure out how to get my hands on the current EState or QueryDesc from inside a fmgr function, so I can't reach estate-es_processed from nextval... Best regards, Thomas Munro -- 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] Aussie timezone database changes incoming
Bruce Momjian br...@momjian.us writes: How are Russians supposed to deploy Postgres on October 26 if they use abbeviations? At midnight? Pretty much. The only bright spot is that the tznames files are just text and can be edited easily, so you can change them when you need to. This isn't the first time this has happened, of course; in fact we are pretty much reversing changes made in commit 3b91fe185a71c05a. And that was in response to law changes that had happened two years before (and nobody had complained meanwhile). So I'm not prepared to consider this a critical issue. 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] NEXT VALUE FOR sequence
Thomas Munro mu...@ip9.org writes: On 3 October 2014 00:18, Tom Lane t...@sss.pgh.pa.us wrote: The spec clearly says one value per row, not one per statement; so command ID is very definitely not the right thing. I think (command ID, estate-es_processed) would work. Not terribly well, eg each new transaction starts over at command ID 1. You could fix that particular objection by also tracking virtual xid. But the bigger issue is that using es_processed for this seems like an utter hack. It's not meant to be anything but statistical, and it's not maintained anyway for non-canSetTag queries (ie, DO ALSO rule commands). That reflects the fact that what it's meant to do is count the number of rows returned to the executor's caller, which isn't necessarily the definition we'd need here. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_receivexlog and replication slots
On Sun, Oct 5, 2014 at 12:09 AM, Andres Freund and...@2ndquadrant.com wrote: Any opinion on whether we should accept both --create and --create-slot or only the latter? Accepting both would get rid of problems due to potential usages of the old syntax - and it's easier to type... My vote goes for only --create-slot and --drop-slot. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_receivexlog and replication slots
On Sat, Oct 4, 2014 at 7:03 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Sun, Oct 5, 2014 at 12:09 AM, Andres Freund and...@2ndquadrant.com wrote: Any opinion on whether we should accept both --create and --create-slot or only the latter? Accepting both would get rid of problems due to potential usages of the old syntax - and it's easier to type... My vote goes for only --create-slot and --drop-slot. Ditto. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] NEXT VALUE FOR sequence
Tom == Tom Lane t...@sss.pgh.pa.us writes: The spec clearly says one value per row, not one per statement; so command ID is very definitely not the right thing. I think (command ID, estate-es_processed) would work. Tom Not terribly well, eg each new transaction starts over at Tom command ID 1. You could fix that particular objection by also Tom tracking virtual xid. But the bigger issue is that using Tom es_processed for this seems like an utter hack. It's not meant Tom to be anything but statistical, and it's not maintained anyway Tom for non-canSetTag queries (ie, DO ALSO rule commands). That Tom reflects the fact that what it's meant to do is count the number Tom of rows returned to the executor's caller, which isn't Tom necessarily the definition we'd need here. Maybe it would make sense to do something with a SubPlan, rather than trying to hide everything inside a function? -- Andrew (irc:RhodiumToad) -- 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] replicating DROP commands across servers
On Fri, Oct 3, 2014 at 4:58 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: I'm not really very convinced that it's a good idea to expose this instead of just figuring out a way to parse the object identity. That's the first thing I tried. But it's not pretty: you have to extract schema names by splitting at a period (and what if a schema name contains a period?), Please tell me that the literals are escaped if necessary. If so, this is pretty easy. quote_literal() is not a hard transformation to reverse, and splitting on a unquoted period is not hard... split out on ON for certain object types, ...nor is splitting on any other fixed text string, such as ON . figure out parens and argument types and names for functions and aggregates, etc. I certainly agree that parsing out parens and argument types and names for functions and aggregates is the hardest part of this, mostly because you can't count a comma to mark the end of one argument and the beginning of the next - you have to account for quoted identifiers, and you might be inside a numeric typemod or similar. It's just not sane to try to parse such text strings. But this is a pretty ridiculous argument. We have an existing parser that does it just fine, and a special-purpose parser that does just that (and not all of the other stuff that the main parser does) would be a great deal simpler. Maybe there are examples other than the ones you listed here that demonstrate that this is actually a hard problem, but the fact that you might need to undo quote_literal() or search for and split on fixed strings does not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers