[HACKERS] [RFC] LSN Map

2015-01-07 Thread Marco Nenciarini
Hi Hackers, In order to make incremental backup (https://wiki.postgresql.org/wiki/Incremental_backup) efficient we need a way to track the LSN of a page in a way that we can retrieve it without reading the actual block. Below there is my proposal on how to achieve it. LSN Map --- The

Re: [HACKERS] XLOG_PARAMETER_CHANGE handling of wal_log_hints

2015-01-07 Thread Petr Jelinek
On 07/01/15 00:59, Michael Paquier wrote: On Wed, Jan 7, 2015 at 4:24 AM, Petr Jelinek p...@2ndquadrant.com wrote: Hi, when I was fixing how commit_ts handles the XLOG_PARAMETER_CHANGE I noticed that for wal_log_hints we assign the value in ControFile to current value instead of value that

Re: [HACKERS] [RFC] Incremental backup v3: incremental PoC

2015-01-07 Thread Marco Nenciarini
Il 06/01/15 14:26, Robert Haas ha scritto: I suggest leaving this out altogether for the first version. I can think of three possible ways that we can determine which blocks need to be backed up. One, just read every block in the database and look at the LSN of each one. Two, maintain a

Re: [HACKERS] parallel mode and parallel contexts

2015-01-07 Thread Simon Riggs
On 6 January 2015 at 21:37, Simon Riggs si...@2ndquadrant.com wrote: I get it now and agree Yes, very much. Should we copy both the top-level frame and the current subxid? Hot Standby links subxids directly to the top-level, so this would be similar. If we copied both, we wouldn't need to

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Dean Rasheed
On 6 January 2015 at 20:44, Peter Geoghegan p...@heroku.com wrote: Another issue that I see is that there is only one resultRelation between the INSERT and UPDATE. That means that without some additional special measure, both UPDATE and INSERT WITH CHECK ( ... ) options are applied regardless

Re: [HACKERS] Re: Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs

2015-01-07 Thread Nicolas Barbier
2015-01-05 Tom Lane t...@sss.pgh.pa.us: What would make sense to me is to teach the planner about inlining SQL functions that include ORDER BY clauses, so that the performance issue of a double sort could be avoided entirely transparently to the user. Another way of getting to the point

Re: [HACKERS] Transactions involving multiple postgres foreign servers

2015-01-07 Thread Ashutosh Bapat
On Sat, Jan 3, 2015 at 2:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes: While looking at the patch for supporting inheritance on foreign tables, I noticed that if a transaction makes changes to more than two foreign servers the current

Re: [HACKERS] Transactions involving multiple postgres foreign servers

2015-01-07 Thread Ashutosh Bapat
On Tue, Jan 6, 2015 at 11:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 5, 2015 at 3:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, we intentionally didn't couple the FDW stuff closely into transaction commit, because of the thought that the far end would not necessarily

Re: [HACKERS] pg_rewind in contrib

2015-01-07 Thread Heikki Linnakangas
On 01/07/2015 01:54 AM, Andrew Dunstan wrote: I also think it's a great idea. But I think we should consider the name carefully. pg_resync might be a better name. Strictly, you might not be quite rewinding, AIUI. pg_resync sounds too generic. It's true that if the source server has changes of

Re: [HACKERS] pg_rewind in contrib

2015-01-07 Thread Heikki Linnakangas
On 01/07/2015 02:17 AM, Andres Freund wrote: On 2015-01-06 15:39:29 -0500, Peter Eisentraut wrote: It wouldn't hurt if we could share SimpleXLogPageRead() between pg_xlogdump and pg_rewind as the differences are more or less superficial, but that seems simple enough to achieve by putting a

Re: [HACKERS] pg_rewind in contrib

2015-01-07 Thread Heikki Linnakangas
On 01/06/2015 10:39 PM, Peter Eisentraut wrote: If this ends up shipping, it's going to be a massively popular tool. I see it as a companion to pg_basebackup. So it should sort of work the same way. One problem is that it doesn't use the replication protocol, so the setup is going to be

Re: [HACKERS] Fillfactor for GIN indexes

2015-01-07 Thread Michael Paquier
On Wed, Dec 3, 2014 at 2:37 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Nov 28, 2014 at 4:27 AM, Alexander Korotkov aekorot...@gmail.com wrote: On Fri, Nov 21, 2014 at 8:12 AM, Michael Paquier michael.paqu...@gmail.com wrote: Please find attached a simple patch adding fillfactor as

Re: [HACKERS] parallel mode and parallel contexts

2015-01-07 Thread Robert Haas
On Tue, Jan 6, 2015 at 4:37 PM, Simon Riggs si...@2ndquadrant.com wrote: So when you say Only the top frame of the transaction state stack is copied you don't mean the top, you mean the bottom (the latest subxact)? Which then becomes the top in the parallel worker? OK... The item most recently

Re: [HACKERS] pg_rewind in contrib

2015-01-07 Thread Heikki Linnakangas
On 01/06/2015 10:39 PM, Peter Eisentraut wrote: The test suite should probably be reimplemented in Perl. (I might be able to help.) Again, ingenious, but it's very hard to follow the sequence of what is being tested. And some Windows person is going to complain. ;-) I took a shot at

Re: [HACKERS] psql -c does not honor ON_ERROR_STOP

2015-01-07 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/06/2015 12:36 PM, Tom Lane wrote: -c submits the entire string to the backend in one PQexec(); therefore ON_ERROR_STOP cannot have any impact on its behavior. The backend will abandon processing the whole string upon first error, embedded

Re: [HACKERS] Fillfactor for GIN indexes

2015-01-07 Thread Michael Paquier
On Thu, Jan 8, 2015 at 6:31 AM, Alexander Korotkov aekorot...@gmail.com wrote: On Wed, Jan 7, 2015 at 4:11 PM, Michael Paquier michael.paqu...@gmail.com I am attaching an updated patch, with the default fillfactor value at 75%, and with the page split code using the fillfactor rate. Thoughts?

Re: [HACKERS] Turning recovery.conf into GUCs

2015-01-07 Thread Josh Berkus
On 01/07/2015 02:31 PM, Peter Eisentraut wrote: On 1/6/15 7:33 PM, Josh Berkus wrote: D. Wierd name: for those doing only replication, not PITR, recovery.conf is completely baffling. I don't disagree, but standby.enabled or whatever isn't any better, for the inverse reason. Yeah. Like I

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Peter Geoghegan
On Wed, Jan 7, 2015 at 12:01 PM, Stephen Frost sfr...@snowman.net wrote: Other databases have this capability and have triggers and at least one ends up firing both INSERT and UPDATE triggers, with many complaints from users about how that ends up making the performance suck. Perhaps we could

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Peter Geoghegan
On Wed, Jan 7, 2015 at 12:26 PM, Stephen Frost sfr...@snowman.net wrote: I agree with that up to a point- this case feels more like a POLA violation though rather than a run-of-the-mill you need to test all that. I'm a bit worried this might lead to cases where users can't use UPSERT because

[HACKERS] empty select list allowed when using function as table

2015-01-07 Thread Merlin Moncure
Interestingly, the following query works (it didn't used to): select from generate_series(1, 1); Was this intentional? merlin -- 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] empty select list allowed when using function as table

2015-01-07 Thread Marko Tiikkaja
On 2015-01-08 01:13, Merlin Moncure wrote: Interestingly, the following query works (it didn't used to): select from generate_series(1, 1); Was this intentional? See 1b4f7f93b4693858cb983af3cd557f6097dab67b .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To

Re: [HACKERS] Possible typo in create_policy.sgml

2015-01-07 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Tue, Jan 6, 2015 at 4:07 PM, Peter Geoghegan p...@heroku.com wrote: On Tue, Jan 6, 2015 at 1:03 PM, Robert Haas robertmh...@gmail.com wrote: I thought my rewrite clarified this distinction pretty well. Maybe I'm wrong? We're talking about

Re: [HACKERS] pg_basebackup fails with long tablespace paths

2015-01-07 Thread Robert Haas
On Tue, Jan 6, 2015 at 4:33 PM, Peter Eisentraut pete...@gmx.net wrote: Currently, when you unpack a tarred basebackup with tablespaces, the symlinks will tell you whether you have unpacked the tablespace tars at the right place. Otherwise, how do you know? Secondly, you also have the option

Re: [HACKERS] Possible typo in create_policy.sgml

2015-01-07 Thread Robert Haas
On Tue, Jan 6, 2015 at 4:07 PM, Peter Geoghegan p...@heroku.com wrote: On Tue, Jan 6, 2015 at 1:03 PM, Robert Haas robertmh...@gmail.com wrote: I thought my rewrite clarified this distinction pretty well. Maybe I'm wrong? We're talking about the same paragraph. Sorry, I didn't express

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 7, 2015 at 4:04 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: I think the policies applied should depend on the path taken, so if it does an INSERT, then only the INSERT CHECK policy should be applied (after the insert), but if it

Re: [HACKERS] Possible typo in create_policy.sgml

2015-01-07 Thread Stephen Frost
* Peter Geoghegan (p...@heroku.com) wrote: I also don't see this behavior documented (this is from process_policies()): /* * If we end up with only USING quals, then use those as * WITH CHECK quals also. */ if (with_check_quals == NIL) with_check_quals = copyObject(quals); Now, I

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 7, 2015 at 3:01 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 7, 2015 at 4:04 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: I think the policies applied should depend on

Re: [HACKERS] parallel mode and parallel contexts

2015-01-07 Thread Jim Nasby
On 1/7/15, 9:39 AM, Robert Haas wrote: sequence.c: Is it safe to read a sequence value in a parallel worker if the cache_value is 1? No, because the sequence cache isn't synchronized between the workers. Maybe it would be safe if cache_value == 1, but there's not much use-case: how often are

Re: [HACKERS] Patch: [BUGS] BUG #12320: json parsing with embedded double quotes

2015-01-07 Thread Andrew Dunstan
On 01/07/2015 08:25 AM, Aaron Botsis wrote: Hi folks, I was having a problem importing json data with COPY. Lots of things export data nicely as one json blob per line. This is excellent for directly importing into a JSON/JSONB column for analysis. ...Except when there’s an embedded

Re: [HACKERS] Possible typo in create_policy.sgml

2015-01-07 Thread Robert Haas
On Wed, Jan 7, 2015 at 3:06 PM, Stephen Frost sfr...@snowman.net wrote: If I'm following correctly, Peter's specifically talking about: [ USING ( replaceable class=parameterexpression/replaceable ) ] [ WITH CHECK ( replaceable class=parametercheck_expression/replaceable ) ] Where

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Robert Haas
On Wed, Jan 7, 2015 at 3:01 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 7, 2015 at 4:04 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: I think the policies applied should depend on the path taken, so if it does an INSERT, then only

Re: [HACKERS] pg_rewind in contrib

2015-01-07 Thread Andrew Dunstan
On 01/07/2015 03:22 AM, Heikki Linnakangas wrote: On 01/07/2015 01:54 AM, Andrew Dunstan wrote: I also think it's a great idea. But I think we should consider the name carefully. pg_resync might be a better name. Strictly, you might not be quite rewinding, AIUI. pg_resync sounds too generic.

Re: [HACKERS] [RFC] LSN Map

2015-01-07 Thread Bruce Momjian
On Wed, Jan 7, 2015 at 10:50:38AM +0100, Marco Nenciarini wrote: Implementation -- We create an additional fork which contains a raw stream of LSNs. To limit the space used, every entry represent the maximum LSN of a group of blocks of a fixed size. I chose arbitrarily the size

Re: [HACKERS] [RFC] LSN Map

2015-01-07 Thread Alvaro Herrera
Bruce Momjian wrote: Have you done any measurements to determine how much backup can be skipped using this method for a typical workload, i.e. how many 16MB page ranges are not modified in a typical span between incremental backups? That seems entirely dependent on the specific workload. --

Re: [HACKERS] parallel mode and parallel contexts

2015-01-07 Thread Robert Haas
On Tue, Jan 6, 2015 at 9:37 PM, Jim Nasby jim.na...@bluetreble.com wrote: CreateParallelContext(): Does it actually make sense to have nworkers=0? ISTM that's a bogus case. I'm not sure whether we'll ever use the zero-worker case in production, but I've certainly found it useful for

Re: [HACKERS] [RFC] LSN Map

2015-01-07 Thread Bruce Momjian
On Wed, Jan 7, 2015 at 12:33:20PM -0300, Alvaro Herrera wrote: Bruce Momjian wrote: Have you done any measurements to determine how much backup can be skipped using this method for a typical workload, i.e. how many 16MB page ranges are not modified in a typical span between incremental

Re: [HACKERS] Fillfactor for GIN indexes

2015-01-07 Thread Alexander Korotkov
On Wed, Jan 7, 2015 at 4:11 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Dec 3, 2014 at 2:37 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Nov 28, 2014 at 4:27 AM, Alexander Korotkov aekorot...@gmail.com wrote: On Fri, Nov 21, 2014 at 8:12 AM, Michael Paquier

Re: [HACKERS] Fillfactor for GIN indexes

2015-01-07 Thread Alexander Korotkov
On Thu, Jan 8, 2015 at 12:31 AM, Alexander Korotkov aekorot...@gmail.com wrote: Rewritten version of patch is attached. I made following changes: 1) I removed fillfactor handling from entry tree. Because in this case fillfactor parameter would be only upper bound for actual page fullness.

Re: [HACKERS] KNN-GiST with recheck

2015-01-07 Thread Alexander Korotkov
On Tue, Dec 16, 2014 at 4:37 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Patch attached. It should be applied on top of my pairing heap patch at http://www.postgresql.org/message-id/548ffa2c.7060...@vmware.com. Some caveats: * The signature of the distance function is unchanged,

Re: [HACKERS] Turning recovery.conf into GUCs

2015-01-07 Thread Peter Eisentraut
On 1/6/15 7:33 PM, Josh Berkus wrote: D. Wierd name: for those doing only replication, not PITR, recovery.conf is completely baffling. I don't disagree, but standby.enabled or whatever isn't any better, for the inverse reason. But replication and PITR are the same thing, so any name is going

Re: [HACKERS] VODKA?

2015-01-07 Thread Arthur Silva
On Jan 6, 2015 7:14 PM, Josh Berkus j...@agliodbs.com wrote: Oleg, Teodor: I take it VODKA is sliding to version 9.6? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] parallel mode and parallel contexts

2015-01-07 Thread Simon Riggs
On 7 January 2015 at 13:11, Robert Haas robertmh...@gmail.com wrote: On Tue, Jan 6, 2015 at 4:37 PM, Simon Riggs si...@2ndquadrant.com wrote: So when you say Only the top frame of the transaction state stack is copied you don't mean the top, you mean the bottom (the latest subxact)? Which then

Re: [HACKERS] [RFC] LSN Map

2015-01-07 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Bruce Momjian wrote: Have you done any measurements to determine how much backup can be skipped using this method for a typical workload, i.e. how many 16MB page ranges are not modified in a typical span between incremental backups? That seems

Re: [HACKERS] pg_rewind in contrib

2015-01-07 Thread Alvaro Herrera
What is this define good for? I couldn't understand where it fits; is it just a leftover? +#define pageinfo_set_truncation(forkno, rnode, blkno) datapagemap_set_truncation(pagemap, forkno, rnode, blkno) Please don't name files with generic names such as util.c/h. It's annoying later; for

Re: [HACKERS] Transactions involving multiple postgres foreign servers

2015-01-07 Thread Kevin Grittner
Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: I don't see why would my patch cause inconsistencies. It can cause dangling PREPAREd transactions and I have already acknowledged that fact. Am I missing something? To me that is the big problem. Where I have run into ad hoc distributed

Re: [HACKERS] [PATCH] explain sortorder

2015-01-07 Thread Timmer, Marius
Hi, we have spent the last days to realize your suggestions in the patch. It affects the result of a EXPLAIN-Statement (even in non-verbose-mode). Now you will get the order-information for every single sort-key which is not ordered by the defaults. best regards, Marius --- Marius Timmer

[HACKERS] Patch: [BUGS] BUG #12320: json parsing with embedded double quotes

2015-01-07 Thread Aaron Botsis
Hi folks, I was having a problem importing json data with COPY. Lots of things export data nicely as one json blob per line. This is excellent for directly importing into a JSON/JSONB column for analysisExcept when there’s an embedded doublequote. Or anything that’s escaped. COPY handles this,

Re: [HACKERS] Patch: [BUGS] BUG #12320: json parsing with embedded double quotes

2015-01-07 Thread Aaron Botsis
Ack. Original had a bug. Let’s try again. escape-without-csv-v2.patch Description: Binary data On Jan 7, 2015, at 8:25 AM, Aaron Botsis aa...@bt-r.com wrote:Hi folks, I was having a problem importing json data with COPY. Lots of things export data nicely as one json blob per line. This is

Re: [HACKERS] 9.5: Better memory accounting, towards memory-bounded HashAgg

2015-01-07 Thread Tomas Vondra
Hi, On 23.12.2014 10:16, Jeff Davis wrote: It seems that these two patches are being reviewed together. Should I just combine them into one? My understanding was that some wanted to review the memory accounting patch separately. On Sun, 2014-12-21 at 20:19 +0100, Tomas Vondra wrote: That's

Re: [HACKERS] [PATCH] explain sortorder

2015-01-07 Thread Mike Blackwell
V6 of this patch applies, builds and checks against the current HEAD. The areas below could use some attention. In explain.c: malloc() should not be called directly here. palloc() would be the correct call, I believe, but the functions in stringinfo.h are probably your best choice as they

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Stephen Frost
* Dean Rasheed (dean.a.rash...@gmail.com) wrote: On 6 January 2015 at 20:44, Peter Geoghegan p...@heroku.com wrote: Another issue that I see is that there is only one resultRelation between the INSERT and UPDATE. That means that without some additional special measure, both UPDATE and

[HACKERS] Re: [COMMITTERS] pgsql: Change how first WAL segment on new timeline after promotion is

2015-01-07 Thread Heikki Linnakangas
On 01/06/2015 03:22 PM, Andres Freund wrote: On 2015-01-05 18:45:22 +0200, Heikki Linnakangas wrote: On 01/03/2015 08:59 PM, Andres Freund wrote: Did you perhaps intend to use XLogFileInit(use_existing = true) instead of XLogFileOpen()? That works for me. Hmm, that doesn't sound right

Re: [HACKERS] Turning recovery.conf into GUCs

2015-01-07 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: On 2015-01-06 17:08:20 -0800, Josh Berkus wrote: On 01/06/2015 04:42 PM, Andres Freund wrote: On 2015-01-06 16:33:36 -0800, Josh Berkus wrote: F. Inability to remaster without restarting the replica. That has pretty much nothing to do with the

Re: [HACKERS] INSERT ... ON CONFLICT UPDATE and RLS

2015-01-07 Thread Robert Haas
On Wed, Jan 7, 2015 at 4:04 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: I think the policies applied should depend on the path taken, so if it does an INSERT, then only the INSERT CHECK policy should be applied (after the insert), but if it ends up doing an UPDATE, I would expect the

Re: [HACKERS] HINTing on UPDATE foo SET foo.bar = ..;

2015-01-07 Thread Alvaro Herrera
We're waiting for an updated version here, right? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] HINTing on UPDATE foo SET foo.bar = ..;

2015-01-07 Thread Marko Tiikkaja
On 1/7/15 6:22 PM, Alvaro Herrera wrote: We're waiting for an updated version here, right? Yeah. (The CF entry is also set to Waiting on Author, which seems appropriate.) .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: