Re: [HACKERS] Compression of full-page-writes

2013-10-15 Thread KONDO Mitsumasa
(2013/10/15 13:33), Amit Kapila wrote: Snappy is good mainly for un-compressible data, see the link below: http://www.postgresql.org/message-id/CAAZKuFZCOCHsswQM60ioDO_hk12tA7OG3YcJA8v=4yebmoa...@mail.gmail.com This result was gotten in ARM architecture, it is not general CPU. Please see detail

Re: [HACKERS] CF 2013-09 Wrap Up

2013-10-15 Thread Noah Misch
On Mon, Oct 14, 2013 at 01:56:42PM -0500, Mike Blackwell wrote: CF 2013-09 will be wrapping up this week. As a reminder, beginning on the official CF end date (11/15), patches Waiting for Author will be Returned with Feedback. Authors are welcome to add their patch to the next CF (2013-11).

Re: [HACKERS] [PATCH] Add use of asprintf()

2013-10-15 Thread Asif Naeem
On Tue, Oct 15, 2013 at 10:55 AM, David Rowley dgrowle...@gmail.com wrote: Though this is not yet enough to get the windows build work with me... I'm still getting link failures for isolationtester.c D:\Postgres\c\pgsql.sln (default target) (1) - D:\Postgres\c\isolationtester.vcxproj

Re: [HACKERS] Long paths for tablespace leads to uninterruptible hang in Windows

2013-10-15 Thread Amit Kapila
On Mon, Oct 14, 2013 at 11:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Oct 10, 2013 at 9:34 AM, Amit Kapila amit.kapil...@gmail.com wrote: On further analysis, I found that hang occurs in some of Windows API(FindFirstFile, RemoveDirectroy) when

Re: [HACKERS] [PATCH] Add use of asprintf()

2013-10-15 Thread David Rowley
On Tue, Oct 15, 2013 at 8:00 PM, Asif Naeem anaeem...@gmail.com wrote: On Tue, Oct 15, 2013 at 10:55 AM, David Rowley dgrowle...@gmail.comwrote: Though this is not yet enough to get the windows build work with me... I'm still getting link failures for isolationtester.c

[HACKERS] Description on bind message

2013-10-15 Thread Tatsuo Ishii
In manual of 48.5. Message Formats section, there is a description of Bind message. Int16[C] The parameter format codes. Each must presently be zero (text) or one (binary). This could be completely non-existent field in the current implementation of PostgreSQL. I think the fact is not very

Re: [HACKERS] Description on bind message

2013-10-15 Thread Marko Tiikkaja
On 10/15/13 11:02 AM, Tatsuo Ishii wrote: In manual of 48.5. Message Formats section, there is a description of Bind message. Int16[C] The parameter format codes. Each must presently be zero (text) or one (binary). This could be completely non-existent field in the current implementation

Re: [HACKERS] Description on bind message

2013-10-15 Thread Tatsuo Ishii
This is already explicitly said in the description of the previous field: The number of parameter format codes that follow (denoted C below). This can be zero to indicate that there are no parameters or that the parameters all use the default format (text); or one, in which case the

Re: [HACKERS] Heavily modified big table bloat even in auto vacuum is running

2013-10-15 Thread Haribabu kommi
On 12 October 2013 11:30 Tom Lane wrote: Haribabu kommi haribabu.ko...@huawei.com writes: To handle the above case instead of directly resetting the dead tuples as zero, how if the exact dead tuples are removed from the table stats. With this approach vacuum gets triggered frequently thus it

[HACKERS] Standby catch up state change

2013-10-15 Thread Pavan Deolasee
Hello, I wonder if there is an issue with the way state change happens from WALSNDSTATE_CATCHUP to WALSNDSTATE_STREAMING. Please note my question is solely based on a strange behavior reported by a colleague and my limited own code reading. The colleague is trying out replication with a

Re: [HACKERS] Standby catch up state change

2013-10-15 Thread Andres Freund
On 2013-10-15 15:51:46 +0530, Pavan Deolasee wrote: Should we not instead wait for the standby to have received all the WAL before declaring that it has caught up ? If a failure happens while the data is still in the sender's buffer, the standby may not actually catch up to the desired point

Re: [HACKERS] Standby catch up state change

2013-10-15 Thread Pavan Deolasee
On Tue, Oct 15, 2013 at 3:59 PM, Andres Freund and...@2ndquadrant.comwrote: I don't think that'd be a good idea - the caughtup logic is used to determine whether we need to wait for further wal to be generated locally if we haven't got anything else to do. And we only need to do so when we

Re: [HACKERS] Standby catch up state change

2013-10-15 Thread Andres Freund
On 2013-10-15 16:12:56 +0530, Pavan Deolasee wrote: On Tue, Oct 15, 2013 at 3:59 PM, Andres Freund and...@2ndquadrant.comwrote: I don't think that'd be a good idea - the caughtup logic is used to determine whether we need to wait for further wal to be generated locally if we haven't got

Re: [HACKERS] WITHIN GROUP patch

2013-10-15 Thread Vik Fearing
On 10/09/2013 04:19 PM, Pavel Stehule wrote: I checked a conformance with ANSI SQL - and I didn't find any issue. I found so following error message is not too friendly (mainly because this functionality will be new) postgres=# select dense_rank(3,3,2) within group (order by

Re: [HACKERS] Standby catch up state change

2013-10-15 Thread Pavan Deolasee
On Tue, Oct 15, 2013 at 4:16 PM, Andres Freund and...@2ndquadrant.comwrote: I don't think delaying the message is a good idea. Comment in walsender.c says: /* * If we're in catchup state, move to streaming. This is an * important state change for

Re: [HACKERS] Standby catch up state change

2013-10-15 Thread Andres Freund
On 2013-10-15 16:29:47 +0530, Pavan Deolasee wrote: On Tue, Oct 15, 2013 at 4:16 PM, Andres Freund and...@2ndquadrant.comwrote: I don't think delaying the message is a good idea. Comment in walsender.c says: /* * If we're in catchup state, move to

Re: [HACKERS] SSL renegotiation

2013-10-15 Thread Vik Fearing
On 09/23/2013 10:51 PM, Alvaro Herrera wrote: + /* are we in the middle of a renegotiation? */ + static bool in_ssl_renegotiation = false; + Since this was committed, I'm getting the following warning: be-secure.c:105:13: warning: ‘in_ssl_renegotiation’ defined but not used

Re: [HACKERS] [PATCH] Statistics collection for CLUSTER command

2013-10-15 Thread Vik Fearing
On 09/16/2013 08:26 AM, Satoshi Nagayasu wrote: (2013/08/08 20:52), Vik Fearing wrote: As part of routine maintenance monitoring, it is interesting for us to have statistics on the CLUSTER command (timestamp of last run, and number of runs since stat reset) like we have for (auto)ANALYZE and

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Robert Haas
On Fri, Oct 11, 2013 at 2:30 PM, Peter Geoghegan p...@heroku.com wrote: But that's simpler than any of the alternatives that I see. Does there really need to be a new snapshot type with one tiny difference that apparently doesn't actually affect conventional clients of MVCC snapshots? I

Re: [HACKERS] removing old ports and architectures

2013-10-15 Thread Andres Freund
On 2013-10-13 16:56:12 +0200, Tom Lane wrote: More to the point for this specific case, it seems like our process ought to be (1) select a preferably-small set of gcc atomic intrinsics that we want to use. I suggest: * pg_atomic_load_u32(uint32 *) * uint32 pg_atomic_store_u32(uint32 *) *

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread MauMau
From: Magnus Hagander mag...@hagander.net On Oct 12, 2013 2:13 AM, MauMau maumau...@gmail.com wrote: I'm not sure if many use XA features, but I saw the questions and answer a few times, IIRC. In the trouble situation, PostgreSQL outputs an intuitive message like increase

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Mon, Oct 14, 2013 at 9:51 AM, Andres Freund and...@2ndquadrant.com wrote: Well, I just think relying on specific symbol names in the .so file is kind of unfortunate. It means that, for example, you can't have multiple output plugins provided by a single .so. And in general I think it's

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 21:41:18 +0900, MauMau wrote: Likewise, non-zero max_prepared_transactons would improve the impression of PostgreSQL (for limited number of users, though), and it wouldn't do any harm. I've seen several sites shutting down because of forgotten prepared transactions causing bloat

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread MauMau
From: Dimitri Fontaine dimi...@2ndquadrant.fr The reason why that parameter default has changed from 5 to 0 is that some people would mistakenly use a prepared transaction without a transaction manager. Few only people are actually using a transaction manager that it's better to have them have

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Mon, Oct 14, 2013 at 5:07 PM, Andres Freund and...@2ndquadrant.com wrote: So, see the attatched benchmark skript. I've always done using a disk bound and a memory bound (using eatmydata, preventing fsyncs) run. * unpatched run, wal_level = hot_standby, eatmydata * unpatched run, wal_level

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 2:47 PM, MauMau maumau...@gmail.com wrote: From: Dimitri Fontaine dimi...@2ndquadrant.fr The reason why that parameter default has changed from 5 to 0 is that some people would mistakenly use a prepared transaction without a transaction manager. Few only people are

Re: [HACKERS] Long paths for tablespace leads to uninterruptible hang in Windows

2013-10-15 Thread Robert Haas
On Mon, Oct 14, 2013 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, that sucks. So it's a Windows bug. It's not clear to me that we should do anything about this at all, except perhaps document that people should avoid long tablespace path names on an unknown set of Windows versions.

Re: [HACKERS] Long paths for tablespace leads to uninterruptible hang in Windows

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 14, 2013 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, that sucks. So it's a Windows bug. It's not clear to me that we should do anything about this at all, except perhaps document that people should

Re: [HACKERS] Compression of full-page-writes

2013-10-15 Thread k...@rice.edu
On Tue, Oct 15, 2013 at 03:11:22PM +0900, KONDO Mitsumasa wrote: (2013/10/15 13:33), Amit Kapila wrote: Snappy is good mainly for un-compressible data, see the link below: http://www.postgresql.org/message-id/CAAZKuFZCOCHsswQM60ioDO_hk12tA7OG3YcJA8v=4yebmoa...@mail.gmail.com This result was

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 08:42:20 -0400, Robert Haas wrote: On Mon, Oct 14, 2013 at 9:51 AM, Andres Freund and...@2ndquadrant.com wrote: Well, I just think relying on specific symbol names in the .so file is kind of unfortunate. It means that, for example, you can't have multiple output plugins

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 08:49:26 -0400, Robert Haas wrote: On Mon, Oct 14, 2013 at 5:07 PM, Andres Freund and...@2ndquadrant.com wrote: So, see the attatched benchmark skript. I've always done using a disk bound and a memory bound (using eatmydata, preventing fsyncs) run. * unpatched run, wal_level

Re: [HACKERS] Triggers on foreign tables

2013-10-15 Thread Robert Haas
On Mon, Oct 14, 2013 at 5:24 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: And, I also want some comments from committers, not only from mine. +1 +1 /me pokes head up. I know I'm going to annoy people with this comment, but I feel like it's going to have to be made at some point by somebody,

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 15:17:58 +0200, Andres Freund wrote: If we go for CSV I think we should put the entire primary key as one column (containing all the columns) and the entire row another. What about columns like: * action B|I|U|D|C * xid * timestamp * tablename * key name * key column names *

Re: [HACKERS] Triggers on foreign tables

2013-10-15 Thread Stephen Frost
Robert, * Robert Haas (robertmh...@gmail.com) wrote: /me pokes head up. I know I'm going to annoy people with this comment, but I feel like it's going to have to be made at some point Perhaps some folks will be annoyed- I'm not annoyed, but I don't really agree. :) by somebody, so here

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 9:17 AM, Andres Freund and...@2ndquadrant.com wrote: It allows you to use the shared libary both as a normal extension loaded via shared_preload_library or adhoc and as an output plugin which seems like a sensible goal. We could have a single _PG_init_output_plugin()

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Hannu Krosing
On 10/15/2013 01:42 PM, Robert Haas wrote: On Mon, Oct 14, 2013 at 9:51 AM, Andres Freund and...@2ndquadrant.com wrote: Well, I just think relying on specific symbol names in the .so file is kind of unfortunate. It means that, for example, you can't have multiple output plugins provided by a

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Hannu Krosing
On 10/15/2013 02:47 PM, Andres Freund wrote: On 2013-10-15 15:17:58 +0200, Andres Freund wrote: If we go for CSV I think we should put the entire primary key as one column (containing all the columns) and the entire row another. just use JSON :) What about columns like: * action B|I|U|D|C *

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 9:47 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 15:17:58 +0200, Andres Freund wrote: If we go for CSV I think we should put the entire primary key as one column (containing all the columns) and the entire row another. What about columns like: *

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 10:09 AM, Hannu Krosing ha...@krosing.net wrote: I don't see how you can fail to know what all is. We instinctively know what all is - as in the famous case of buddhist ordering a hamburger - Make me All wit Everything :) - but the requirements of different

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 10:20:55 -0400, Robert Haas wrote: For multi-master / conflict resolution you may also want all old values to make sure that they have not changed on target. The patch as proposed doesn't make that information available. If you want that to be an option, now would be the

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 10:09:05 -0400, Robert Haas wrote: On Tue, Oct 15, 2013 at 9:17 AM, Andres Freund and...@2ndquadrant.com wrote: It allows you to use the shared libary both as a normal extension loaded via shared_preload_library or adhoc and as an output plugin which seems like a sensible

Re: [HACKERS] Patch for reserved connections for replication users

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 12:13 AM, Amit Kapila amit.kapil...@gmail.com wrote: If we think this way, then may be we should have max_user_connections instead of max_connections and then max_wal_connections. But still there are other's like pg_basebackup who needs connections and tomorrow there

Re: [HACKERS] Patch for reserved connections for replication users

2013-10-15 Thread Andres Freund
On 2013-10-15 10:29:58 -0400, Robert Haas wrote: On Tue, Oct 15, 2013 at 12:13 AM, Amit Kapila amit.kapil...@gmail.com wrote: If we think this way, then may be we should have max_user_connections instead of max_connections and then max_wal_connections. But still there are other's like

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 10:27 AM, Andres Freund and...@2ndquadrant.com wrote: I think part of the problem may be that you're using the library name to identify the output plugin. I'm not excited about that design. For functions, you give the function a name and that is a pointer to where to

Re: [HACKERS] Release note fix for timeline item

2013-10-15 Thread Bruce Momjian
On Tue, Oct 15, 2013 at 02:32:47PM +0900, KONDO Mitsumasa wrote: Sorry for my reply late... (2013/10/08 23:26), Bruce Momjian wrote: First, I want to apologize for not completing the release notes earlier so that others could review them. I started working on the release notes on

Re: [HACKERS] Patch for reserved connections for replication users

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 10:34 AM, Andres Freund and...@2ndquadrant.com wrote: But I also agree that making max_wal_senders act as both a minimum and a maximum is no good. +1 to everything Josh Berkus said. Josh said we should treat replication connections in a separate pool from normal

Re: [HACKERS] Patch for reserved connections for replication users

2013-10-15 Thread Andres Freund
On 2013-10-15 10:36:41 -0400, Robert Haas wrote: On Tue, Oct 15, 2013 at 10:34 AM, Andres Freund and...@2ndquadrant.com wrote: But I also agree that making max_wal_senders act as both a minimum and a maximum is no good. +1 to everything Josh Berkus said. Josh said we should treat

Re: [HACKERS] Doc Patch: Subquery section to say that subqueries can't modify data

2013-10-15 Thread Vik Fearing
On 08/06/2013 11:03 PM, Karl O. Pinc wrote: The attached documentation patch, doc-subqueries-v1.patch, applies against head. I wanted to document that subqueries can't modify data. This is mentioned in the documentation for SELECT and implied elsewhere but I was looking for something more

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 10:15:14 -0400, Robert Haas wrote: On Tue, Oct 15, 2013 at 9:47 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 15:17:58 +0200, Andres Freund wrote: If we go for CSV I think we should put the entire primary key as one column (containing all the columns) and the

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 10:34:53 -0400, Robert Haas wrote: On Tue, Oct 15, 2013 at 10:27 AM, Andres Freund and...@2ndquadrant.com wrote: I think part of the problem may be that you're using the library name to identify the output plugin. I'm not excited about that design. For functions, you give

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 5:15 AM, Robert Haas robertmh...@gmail.com wrote: Well, the SQL standard way of doing this type of operation is MERGE. The alternative we know exists in other databases is REPLACE; there's also INSERT .. ON DUPLICATE KEY update. In all of those cases, whatever

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 10:56 AM, Andres Freund and...@2ndquadrant.com wrote: That means you allow trivial remote code execution since you could try to load system() or something else that's available in every shared object. Now you can argue that that's OK since we have special checks for

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread k...@rice.edu
On Tue, Oct 15, 2013 at 11:02:39AM -0400, Robert Haas wrote: goals may be in conflict; we'll have to pick something. Note that parsing COPYs is a major PITA from most languages... Perhaps we should make the default output json instead? With every action terminated by a nullbyte?

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Andres Freund
On 2013-10-15 11:11:24 -0400, Robert Haas wrote: I'm not saying go implement MERGE. I'm saying, make the insert-or-update operation a single statement, using some syntax TBD, instead of requiring the use of a new insert statement that makes invisible rows visible as a side effect, so that you

Re: [HACKERS] CF 2013-09 Wrap Up

2013-10-15 Thread Mike Blackwell
On Tue, Oct 15, 2013 at 1:39 AM, Noah Misch n...@leadboat.com wrote: On Mon, Oct 14, 2013 at 01:56:42PM -0500, Mike Blackwell wrote: Any patches marked Needs Review will be automatically moved to the next CF. We will try to make sure that all patches in the current CF have received at

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Andres Freund
On 2013-10-15 11:02:39 -0400, Robert Haas wrote: If the plugin interface isn't rich enough to provide a convenient way to avoid that, then it needs to be fixed so that it is, because it will be a common requirement. Oh, it surely is possibly to avoid repeating it. The output plugin

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 8:07 AM, Peter Geoghegan p...@heroku.com wrote: Naturally we all want MERGE. It seems self-defeating to insist on something significantly harder that there is significant less demand for, though. I hasten to add: which is not to imply that you're insisting rather than

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 10:48 AM, Andres Freund and...@2ndquadrant.com wrote: What about columns like: * action B|I|U|D|C BEGIN and COMMIT? That's B and C, yes. You'd rather not have them? When would you replay the commit without an explicit message telling you to? No, BEGIN and COMMIT

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 11:07 AM, Peter Geoghegan p...@heroku.com wrote: On Tue, Oct 15, 2013 at 5:15 AM, Robert Haas robertmh...@gmail.com wrote: Well, the SQL standard way of doing this type of operation is MERGE. The alternative we know exists in other databases is REPLACE; there's also

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Josh Berkus
On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders. +1 for having replication supported out-of-the-box aside from pg_hba.conf. To put it another way: users are more likely to care

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders. +1 for having replication supported out-of-the-box aside from pg_hba.conf.

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should definitely have us change wal_level and max_wal_senders.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Andres Freund
On 2013-10-15 10:19:17 -0700, Peter Geoghegan wrote: On Tue, Oct 15, 2013 at 9:56 AM, Robert Haas robertmh...@gmail.com wrote: Well, I don't know that any of us can claim to have a lock on what the syntax should look like. Sure. But it's not just syntax. We're talking about functional

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote: On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus Hagander wrote: But the argument about being friendly for new users should

Re: [HACKERS] Patch for reserved connections for replication users

2013-10-15 Thread Josh Berkus
On 10/15/2013 07:36 AM, Robert Haas wrote: On Tue, Oct 15, 2013 at 10:34 AM, Andres Freund and...@2ndquadrant.com wrote: Josh said we should treat replication connections in a separate pool from normal database connections, right? So you withdraw your earlier objection to that? I don't

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 7:32 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote: On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:19:06 -0700, Josh Berkus wrote: On 10/15/2013 05:52 AM, Magnus

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 10:29 AM, Andres Freund and...@2ndquadrant.com wrote: I think anything that only works by breaking visibility rules that way is a nonstarter. Doing that from the C level is one thing, exposing it this way seems a bad idea. What visibility rule is that? Upsert *has* to

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Josh Berkus
On 10/15/2013 08:11 AM, Robert Haas wrote: I'm not saying go implement MERGE. I'm saying, make the insert-or-update operation a single statement, using some syntax TBD, instead of requiring the use of a new insert statement that makes invisible rows visible as a side effect, so that you can

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 10:58 AM, Josh Berkus j...@agliodbs.com wrote: Hmmm. Is the plan NOT to eventually get to a single-statement upsert? If not, then I'm not that keen on this feature. See the original e-mail in the thread for what I imagine idiomatic usage will look like.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 11:05 AM, Peter Geoghegan p...@heroku.com wrote: See the original e-mail in the thread for what I imagine idiomatic usage will look like. http://www.postgresql.org/message-id/cam3swzthwrktvurf1awaih8qthgnmzafydcnw8qju7pqhk5...@mail.gmail.com Note also that this

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Josh Berkus
Peter, Note also that this doesn't preclude a variant with a more direct update part (not that I think that's all that compelling). Doing things this way was motivated by: I can see the value in the CTE format for this for existing PostgreSQL users. (although, AFAICT it doesn't allow for the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 11:23 AM, Josh Berkus j...@agliodbs.com wrote: (although, AFAICT it doesn't allow for the implementation of one of my personal desires, which is UPDATE ... ON NOT FOUND INSERT, for cases where updates are expected to occur 95% of the time, but that's another topic.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Josh Berkus
On 10/15/2013 11:38 AM, Peter Geoghegan wrote: We thought about prioritizing where to look (mostly as a performance optimization), but right now no. It works with amcanunique methods, which in practice means btrees. There is no such thing as a GiST unique index, so I guess you're referring to

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 11:55 AM, Josh Berkus j...@agliodbs.com wrote: However, it does seem like the new syntax could be extended with and optional USING unqiue_index_name in the future (9.5), no? There is no reason why we couldn't do that and just consider that one unique index. Whether we

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Josh Berkus
On 10/15/2013 12:03 PM, Peter Geoghegan wrote: On Tue, Oct 15, 2013 at 11:55 AM, Josh Berkus j...@agliodbs.com wrote: However, it does seem like the new syntax could be extended with and optional USING unqiue_index_name in the future (9.5), no? There is no reason why we couldn't do that and

Re: [HACKERS] background workers, round three

2013-10-15 Thread Kohei KaiGai
2013/10/14 Robert Haas robertmh...@gmail.com: * ephemeral-precious-v1.patch AtEOXact_BackgroundWorker() is located around other AtEOXact_* routines. Doesn't it makes resource management complicated? In case when main process goes into error handler but worker process is still running in

Re: [HACKERS] Long paths for tablespace leads to uninterruptible hang in Windows

2013-10-15 Thread Amit Kapila
On Tue, Oct 15, 2013 at 6:28 PM, Magnus Hagander mag...@hagander.net wrote: On Tue, Oct 15, 2013 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 14, 2013 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, that sucks. So it's a Windows bug. It's not clear to me that we

Re: [HACKERS] Triggers on foreign tables

2013-10-15 Thread Kohei KaiGai
2013/10/15 Robert Haas robertmh...@gmail.com: On Mon, Oct 14, 2013 at 5:24 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: And, I also want some comments from committers, not only from mine. +1 +1 /me pokes head up. I know I'm going to annoy people with this comment, but I feel like it's

Re: [HACKERS] Long paths for tablespace leads to uninterruptible hang in Windows

2013-10-15 Thread Robert Haas
On Tue, Oct 15, 2013 at 4:14 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Oct 15, 2013 at 6:28 PM, Magnus Hagander mag...@hagander.net wrote: On Tue, Oct 15, 2013 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 14, 2013 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote:

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Andres Freund
On 2013-10-15 10:53:35 -0700, Peter Geoghegan wrote: On Tue, Oct 15, 2013 at 10:29 AM, Andres Freund and...@2ndquadrant.com wrote: I think anything that only works by breaking visibility rules that way is a nonstarter. Doing that from the C level is one thing, exposing it this way seems a

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Andres Freund
On 2013-10-15 11:23:44 -0700, Josh Berkus wrote: (although, AFAICT it doesn't allow for the implementation of one of my personal desires, which is UPDATE ... ON NOT FOUND INSERT, for cases where updates are expected to occur 95% of the time, but that's another topic. Unless rejects for an

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Andres Freund
On 2013-10-15 11:55:06 -0700, Josh Berkus wrote: Also, because you can't INDEX CONCURRENTLY a PK, I've been building a lot of databases which have no PKs, only UNIQUE indexes. You know that you can add prebuilt primary keys using ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY (...) USING

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Josh Berkus
On 10/15/2013 07:56 AM, Andres Freund wrote: Well, just providing the C API + an example in a first step didn't work out too badly for FDWs. I am pretty sure that once released there will soon be extensions for it on PGXN or whatever for special usecases. I suspect so, too. But I also

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Josh Berkus
On 10/15/2013 02:31 PM, Andres Freund wrote: On 2013-10-15 11:55:06 -0700, Josh Berkus wrote: Also, because you can't INDEX CONCURRENTLY a PK, I've been building a lot of databases which have no PKs, only UNIQUE indexes. You know that you can add prebuilt primary keys using ALTER TABLE ...

Re: [HACKERS] [PoC] pgstattuple2: block sampling to reduce physical read

2013-10-15 Thread Mark Kirkwood
On 11/10/13 17:49, Mark Kirkwood wrote: On 11/10/13 17:08, Satoshi Nagayasu wrote: (2013/10/11 7:32), Mark Kirkwood wrote: On 11/10/13 11:09, Mark Kirkwood wrote: On 16/09/13 16:20, Satoshi Nagayasu wrote: (2013/09/15 11:07), Peter Eisentraut wrote: On Sat, 2013-09-14 at 16:18 +0900, Satoshi

Re: [HACKERS] [PoC] pgstattuple2: block sampling to reduce physical read

2013-10-15 Thread Mark Kirkwood
On 11/10/13 17:33, Jaime Casanova wrote: also the name pgstattuple2, doesn't convince me... maybe you can use pgstattuple() if you use a second argument (percentage of the sample) to overload the function +1, that seems much nicer. Regards Mark -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Patch for reserved connections for replication users

2013-10-15 Thread Gibheer
On Mon, 14 Oct 2013 11:52:57 +0530 Amit Kapila amit.kapil...@gmail.com wrote: On Sun, Oct 13, 2013 at 2:08 PM, Gibheer gibh...@zero-knowledge.org wrote: On Sun, 13 Oct 2013 11:38:17 +0530 Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Oct 10, 2013 at 3:17 AM, Gibheer

Re: [HACKERS] PSQL return coder

2013-10-15 Thread James Sewell
I was avoiding ON_ERROR_STOP because I was using ON_ERROR_ROLLBACK, but have just realised that if I encase my SQL in a transaction then rollback will still happen. Perfect! James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St,

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread David Fetter
On Tue, Oct 15, 2013 at 10:09:05AM -0400, Robert Haas wrote: On Tue, Oct 15, 2013 at 9:17 AM, Andres Freund and...@2ndquadrant.com wrote: User: So, what's new in PostgreSQL 9.4? Hacker: Well, now we have logical replication! User: Why is that cool? Hacker: Well, streaming replication is

Re: [HACKERS] logical changeset generation v6.2

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 7:09 AM, Robert Haas robertmh...@gmail.com wrote: Let's try that again. User: Wow, that sounds great. How do I use it? Hacker: Well, currently, the output gets dumped as a series of text files that are designed to be parsed using a scripting language. We have sample

Re: [HACKERS] Long paths for tablespace leads to uninterruptible hang in Windows

2013-10-15 Thread Amit Kapila
On Wed, Oct 16, 2013 at 2:04 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Oct 15, 2013 at 4:14 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Oct 15, 2013 at 6:28 PM, Magnus Hagander mag...@hagander.net wrote: On Tue, Oct 15, 2013 at 2:55 PM, Robert Haas robertmh...@gmail.com

Re: [HACKERS] Improve setup for documentation building with FOP

2013-10-15 Thread Peter Eisentraut
On 9/16/13 12:19 PM, Alvaro Herrera wrote: The FOP-based build works fine for me. I gave the output a look. I like that text formatted with fixed-width font wraps at the right margin, instead of continuing beyond it; there are some strange artifacts about it (such as addition of hyphens in

Re: [HACKERS] buildfarm failures on smew and anole

2013-10-15 Thread Peter Eisentraut
On Mon, 2013-10-14 at 18:14 -0400, Robert Haas wrote: I cleaned the semaphores on smew, but they came back. Whatever is crashing is leaving the semaphores lying around. Ugh. When did you do that exactly? I thought I fixed the problem that was causing that days ago, and the last 4 days

Re: [HACKERS] [PATCH] pg_sleep(interval)

2013-10-15 Thread Vik Fearing
On 09/30/2013 01:47 PM, Vik Fearing wrote: Yes, I understand you are trying to help, and I appreciate it! My opinion, and that of others as well from the original thread, is that this patch should either go in as is and break that one case, or not go in at all. I'm fine with either (although

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-10-15 Thread Peter Geoghegan
On Tue, Oct 15, 2013 at 2:25 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-10-15 10:53:35 -0700, Peter Geoghegan wrote: On Tue, Oct 15, 2013 at 10:29 AM, Andres Freund and...@2ndquadrant.com wrote: I think anything that only works by breaking visibility rules that way is a

Re: [HACKERS] Compression of full-page-writes

2013-10-15 Thread KONDO Mitsumasa
(2013/10/15 22:01), k...@rice.edu wrote: Google's lz4 is also a very nice algorithm with 33% better compression performance than snappy and 2X the decompression performance in some benchmarks also with a bsd license: https://code.google.com/p/lz4/ If we judge only performance, we will select

Re: [HACKERS] Standby catch up state change

2013-10-15 Thread Pavan Deolasee
On Tue, Oct 15, 2013 at 4:51 PM, Andres Freund and...@2ndquadrant.comwrote: I think you're over-intrepreting it. I think you are right. Someone who understands the replication code very well advised us to use that log message as a way to measure how much time it takes to send all the missing