Re: [HACKERS] Capturing EXPLAIN ANALYZE for a query without discarding the normal result set

On 1/13/14, 9:14 PM, Tom Lane wrote: Dave Cole writes: It would be really cool if you could direct the EXPLAIN ANALYZE output to a temporary table so that the query being analyzed could execute normally. What happens if the current transaction rolls back? If you want noninvasive explain data

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 1/13/14, 7:41 PM, Gavin Flower wrote: On 14/01/14 14:29, Tom Lane wrote: [...] (2) the float and numeric variants should be implemented under nondefault names (I'm thinking FAST_SUM(), but bikeshed away). People who need extra speed and don't mind the slightly different results can alter th

Re: [HACKERS] nested hstore patch - FailedAssertion("!(value->array.nelems == 1)

On Wed, January 15, 2014 08:01, Oleg Bartunov wrote: > It doesn't crashed in the last version in our repository. > > =# select 'x'::hstore || ('a=>"1"':: hstore) ; >?column? > --- > "x", "a", "1" > (1 row) > OK, shall I use that repository instead of the latest posted patch? No p

Re: [HACKERS] Turning recovery.conf into GUCs

On Wed, Jan 15, 2014 at 2:00 AM, Jaime Casanova wrote: > On Mon, Nov 18, 2013 at 12:27 PM, Andres Freund > wrote: > >> * Maybe we should rename names like pause_at_recovery_target to >> recovery_pause_at_target? Since we already force everyone to bother >> changing their setup... > > i don't

Re: [HACKERS] nested hstore patch - FailedAssertion("!(value->array.nelems == 1)

It doesn't crashed in the last version in our repository. =# select 'x'::hstore || ('a=>"1"':: hstore) ; ?column? --- "x", "a", "1" (1 row) On Wed, Jan 15, 2014 at 3:53 AM, Erik Rijkers wrote: > On Mon, January 13, 2014 16:36, Andrew Dunstan wrote: >> A new version of the patc

Re: [HACKERS] GIN improvements part2: fast scan

On Tue, Jan 14, 2014 at 11:07 PM, Heikki Linnakangas < hlinnakan...@vmware.com> wrote: > On 01/14/2014 05:35 PM, Alexander Korotkov wrote: > >> On Thu, Nov 21, 2013 at 12:14 AM, Alexander Korotkov >> wrote: >> >> Revised version of patch is attached. Changes are so: >>> 1) Support for GinFuzzySea

Re: [HACKERS] GIN improvements part 1: additional information

On Wed, Jan 15, 2014 at 5:17 AM, Tomas Vondra wrote: > On 14.1.2014 00:38, Tomas Vondra wrote: > > On 13.1.2014 18:07, Alexander Korotkov wrote: > >> On Sat, Jan 11, 2014 at 6:15 AM, Tomas Vondra >> > wrote: > >> > >> On 8.1.2014 22:58, Alexander Korotkov wrote: > >>

[HACKERS] drop duplicate buffers in OS

Hi, I create patch that can drop duplicate buffers in OS using usage_count alogorithm. I have developed this patch since last summer. This feature seems to be discussed in hot topic, so I submit it more faster than my schedule. When usage_count is high in shared_buffers, they are hard to drop fro

Re: [HACKERS] Extending BASE_BACKUP in replication protocol: incremental backup and backup format

On 1/14/14, 7:41 AM, Magnus Hagander wrote: Yes, it would be necessary to scan the whole database as the LSN to be checked is kept in PageHeaderData :). Perhaps it is not that performant, but my initial thought was that perhaps the amount of data necessary to maintain incremental

Re: [HACKERS] tests for client programs

On Wed, January 15, 2014 06:30, Peter Eisentraut wrote: > As we all know, the client programs (src/bin/) don't have any real test > suites. Some pieces are tested as part of the backend regression tests, > some as part of the pg_upgrade test script, but nothing is specifically > targeted, and pg_b

Re: [HACKERS] plpgsql.consistent_into

Jim Nasby writes: > Do we actually support = right now? We already support > v_field := field FROM table ... ; > and I think it's a bad idea to have different meaning for = and :=. That ship sailed a *very* long time ago. See other thread about documenting rather than ignoring this more-or-less-

Re: [HACKERS] plpgsql.consistent_into

On 1/14/14, 11:15 AM, Tom Lane wrote: How about: >(a) = SELECT 1; >(a, b) = SELECT 1, 2; >(a, b) = INSERT INTO foo RETURNING col1, col2; >Same semantics: TOO_MANY_ROWS on rows > 1, sets FOUND and row_count. >AFAICT this can be parsed unambiguously, too, and we don't need to look >at t

Re: [HACKERS] Portal suddenly disappears?

>> The portal "portal19528" was created by a bind message (pgpool-II uses >> the identical name as the named statement) then subsequent >> exec_bind_message failed to find the portal. Could it ever happen? > > I'm confused too. Surely there are lots of ways a portal could get > dropped, but most

Re: [HACKERS] plpgsql.warn_shadow

On Jan15, 2014, at 01:34 , Marko Tiikkaja wrote: > It's me again, trying to find a solution to the most common mistakes I make. > This time it's accidental shadowing of variables, especially input variables. > I've wasted several hours banging my head against the wall while shouting > "HOW CA

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Wed, Jan 15, 2014 at 1:07 AM, Jim Nasby wrote: >>> >>> Though, it also occurs to me... perhaps it would be better for us to >>> simply >>> map temp objects to memory and let the kernel swap them out if needed... >> >> >> >> Oum... bad idea. >> >> Swap logic has very poor taste for I/O patterns.

Re: [HACKERS] Create function prototype as part of PG_FUNCTION_INFO_V1

Peter Eisentraut writes: > This idea has appeared at least twice now, in > http://www.postgresql.org/message-id/1386301050.2743.17.ca...@vanquo.pezone.net > and http://www.postgresql.org/message-id/52d25aa2.50...@2ndquadrant.com . > Even if it doesn't help with Windows issues, as discussed in t

Re: [HACKERS] CREATE TABLESPACE WITH

On Wed, Jan 15, 2014 at 2:02 PM, Jim Nasby wrote: > You know, this doesn't do much to encourage people to submit patches since > it was just suggested that we use WITH instead of SET. :( Sorry, you are right. > Anyone have an easy way to see which is more prevalent? I'd be stuck with \h > or tryi

[HACKERS] tests for client programs

As we all know, the client programs (src/bin/) don't have any real test suites. Some pieces are tested as part of the backend regression tests, some as part of the pg_upgrade test script, but nothing is specifically targeted, and pg_basebackup for example is not tested at all. So I wrote somethin

Re: [HACKERS] WAL Rate Limiting

On Wed, Jan 15, 2014 at 7:50 AM, Simon Riggs wrote: > We've discussed previously the negative impact of large bulk > operations, especially wrt WAL writes. Patch here allows maintenance > operations to have their WAL generation slowed down as a replication > lag prevention feature. > > I believe

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 05:38:10PM -0700, Jonathan Corbet wrote: > On Wed, 15 Jan 2014 09:23:52 +1100 > Dave Chinner wrote: > > > It appears to me that we are seeing large memory machines much more > > commonly in data centers - a couple of years ago 256GB RAM was only > > seen in supercomputers.

Re: [HACKERS] plpgsql.warn_shadow

On 1/14/14, 6:34 PM, Marko Tiikkaja wrote: Hi all! It's me again, trying to find a solution to the most common mistakes I make. This time it's accidental shadowing of variables, especially input variables. I've wasted several hours banging my head against the wall while shouting "HOW CAN THI

Re: [HACKERS] Portal suddenly disappears?

Tatsuo Ishii writes: > While looking at pgpool-II user's complain, I see weired thing in the > PostgreSQL log. > Jan 14 10:04:57 dayrhegrdp005 postgres[25223]: [4559-1] LOG: 0: > statement: PREPARE pgpool19528 AS SELECT count(*) from (SELECT > has_function_privilege('measure', > Jan 14 10:

Re: [HACKERS] CREATE TABLESPACE WITH

On 1/14/14, 8:07 PM, Michael Paquier wrote: On Wed, Jan 15, 2014 at 10:27 AM, Vik Fearing wrote: On 12/26/2013 06:10 PM, David Fetter wrote: On Tue, Dec 24, 2013 at 07:25:01PM +0100, Vik Fearing wrote: I was recently annoyed that I had to do CREATE TABLESPACE x LOCATION y; ALTER TABLESPACE x

Re: [HACKERS] shared memory message queues

On Wed, Jan 15, 2014 at 4:32 AM, Robert Haas wrote: > On Tue, Jan 14, 2014 at 1:54 PM, Alvaro Herrera > wrote: >> Robert Haas escribió: >>> On Tue, Jan 14, 2014 at 12:43 PM, Thom Brown wrote: >>> > LOG: worker process: test_shm_mq (PID 22041) exited with exit code 1 >>> > LOG: unregistering ba

Re: [HACKERS] WAL Rate Limiting

On 1/14/14, 8:20 PM, Simon Riggs wrote: We've discussed previously the negative impact of large bulk operations, especially wrt WAL writes. Patch here allows maintenance operations to have their WAL generation slowed down as a replication lag prevention feature. I believe there was originally in

[HACKERS] Portal suddenly disappears?

While looking at pgpool-II user's complain, I see weired thing in the PostgreSQL log. Jan 14 10:04:57 dayrhegrdp005 postgres[25223]: [4559-1] LOG: 0: statement: PREPARE pgpool19528 AS SELECT count(*) from (SELECT has_function_privilege('measure', Jan 14 10:04:57 dayrhegrdp005 postgres[25223

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/14/14, 6:36 PM, Claudio Freire wrote: On Tue, Jan 14, 2014 at 9:22 PM, Jim Nasby wrote: On 1/14/14, 11:30 AM, Jeff Janes wrote: I think the "reclaim this page if you need memory but leave it resident if there is no memory pressure" hint would be more useful for temporary working files th

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/14/14, 10:08 AM, Tom Lane wrote: Trond Myklebust writes: On Jan 14, 2014, at 10:39, Tom Lane wrote: "Don't be aggressive" isn't good enough. The prohibition on early write has to be absolute, because writing a dirty page before we've done whatever else we need to do results in a corrupt

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/14/14, 3:41 PM, Dave Chinner wrote: On Tue, Jan 14, 2014 at 09:40:48AM -0500, Robert Haas wrote: On Mon, Jan 13, 2014 at 5:26 PM, Mel Gorman wrote: IOWs, using sync_file_range() does not avoid the need to fsync() a file for data integrity purposes... I belive the PG community understand

Re: [HACKERS] WAL Rate Limiting

On 01/15/2014 10:20 AM, Simon Riggs wrote: > (discuss: do we need another parameter to specify "cost"? Currently > patch uses "sleep every 64kB of WAL") It might be nicer to express this as "up to n MB of WAL per second", but otherwise seems reasonable, and it's easy enough to convert WAL MB/s int

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/14/14, 4:21 AM, Mel Gorman wrote: There is an interesting side-line here. If all IO is initiated by one process in postgres then the memory locality will be sub-optimal. The consumer of the data may or may not be running on the same node as the process that read the data from disk. It is pos

Re: [HACKERS] shared memory message queues

Peter Eisentraut wrote: > Something is causing this new compiler warning: > > setup.c: In function ‘setup_dynamic_shared_memory’: > setup.c:102:3: error: format ‘%llu’ expects argument of type ‘long long > unsigned > int’, but argument 2 has type ‘Size’ [-Werror=format=] I'm not seeing that one

Re: [HACKERS] shared memory message queues

Something is causing this new compiler warning: setup.c: In function ‘setup_dynamic_shared_memory’: setup.c:102:3: error: format ‘%llu’ expects argument of type ‘long long unsigned int’, but argument 2 has type ‘Size’ [-Werror=format=] -- Sent via pgsql-hackers mailing list (pgsql-hackers@po

[HACKERS] WAL Rate Limiting

We've discussed previously the negative impact of large bulk operations, especially wrt WAL writes. Patch here allows maintenance operations to have their WAL generation slowed down as a replication lag prevention feature. I believe there was originally intended to be some work on I/O rate limitin

Re: [HACKERS] CREATE TABLESPACE WITH

On Wed, Jan 15, 2014 at 10:27 AM, Vik Fearing wrote: > On 12/26/2013 06:10 PM, David Fetter wrote: >> On Tue, Dec 24, 2013 at 07:25:01PM +0100, Vik Fearing wrote: >>> I was recently annoyed that I had to do >>> >>> CREATE TABLESPACE x LOCATION y; >>> ALTER TABLESPACE x SET (random_page_cost = z);

Re: [HACKERS] extension_control_path

On 15 January 2014 03:07, Stephen Frost wrote: > For my 2c, I could absolutely see it as being worthwhile to have an > independent directory to install not-from-package extensions. That > would keep things which are "managed by the package system" and things > which are installed independent sepa

Re: [HACKERS] CREATE TABLESPACE WITH

On 12/26/2013 06:10 PM, David Fetter wrote: > On Tue, Dec 24, 2013 at 07:25:01PM +0100, Vik Fearing wrote: >> I was recently annoyed that I had to do >> >> CREATE TABLESPACE x LOCATION y; >> ALTER TABLESPACE x SET (random_page_cost = z); >> >> The attached patch is a quick n' dirty extension to all

Re: [HACKERS] GIN improvements part 1: additional information

On 14.1.2014 00:38, Tomas Vondra wrote: > On 13.1.2014 18:07, Alexander Korotkov wrote: >> On Sat, Jan 11, 2014 at 6:15 AM, Tomas Vondra > > wrote: >> >> On 8.1.2014 22:58, Alexander Korotkov wrote: >> > Thanks for reporting. Fixed version is attached. >> >> I've t

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 03:03:39PM -0800, Kevin Grittner wrote: > Dave Chinner write: > > > Essentially, changing dirty_background_bytes, dirty_bytes and > > dirty_expire_centiseconds to be much smaller should make the > > kernel start writeback much sooner and so you shouldn't have to > > limit

[HACKERS] commit fest 2014-01 starts this Wednesday

Reminder: Commit fest 2014-01, the fourth and final commit fest in the PostgreSQL 9.4 development cycle, will start this Wednesday, January 15. If you have a patch that you would like to see considered for inclusion into PostgreSQL 9.4, please register it in the commit fest application: https

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Wed, 15 Jan 2014 09:23:52 +1100 Dave Chinner wrote: > It appears to me that we are seeing large memory machines much more > commonly in data centers - a couple of years ago 256GB RAM was only > seen in supercomputers. Hence machines of this size are moving from > "tweaking settings for superco

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 9:22 PM, Jim Nasby wrote: > On 1/14/14, 11:30 AM, Jeff Janes wrote: >> >> I think the "reclaim this page if you need memory but leave it resident if >> there is no memory pressure" hint would be more useful for temporary working >> files than for what was being discussed ab

[HACKERS] plpgsql.warn_shadow

Hi all! It's me again, trying to find a solution to the most common mistakes I make. This time it's accidental shadowing of variables, especially input variables. I've wasted several hours banging my head against the wall while shouting "HOW CAN THIS VARIABLE ALWAYS BE NULL?". I can't beli

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On 1/14/14, 11:30 AM, Jeff Janes wrote: I think the "reclaim this page if you need memory but leave it resident if there is no memory pressure" hint would be more useful for temporary working files than for what was being discussed above (shared buffers). When I do work that needs large tempor

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

On Tue, Jan 14, 2014 at 2:16 PM, Heikki Linnakangas wrote: >> I don't think it's a mundane issue. But in any case, you haven't >> addressed why you think your proposal is more or less better than my >> proposal, which is the pertinent question. > > 1. It's simpler. > > 2. Works for exclusion const

Re: [HACKERS] nested hstore patch - FailedAssertion("!(value->array.nelems == 1)

On Mon, January 13, 2014 16:36, Andrew Dunstan wrote: > A new version of the patch is attached. It includes all of Erik's docs > [ nested_hstore_and_jsonb-2.patch ] This crashes the server: testdb=# select 'x' || ('a=>"1"':: hstore) ; The connection to the server was lost. Attempting reset: F

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Wed, Jan 15, 2014 at 08:03:28AM +1300, Gavin Flower wrote: > On 14/01/14 14:09, Dave Chinner wrote: > >On Mon, Jan 13, 2014 at 09:29:02PM +, Greg Stark wrote: > >>On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund > >>wrote: > [...] > >>The more ambitious and interesting direction is to let Po

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, 2014-01-14 at 15:09 -0500, Robert Haas wrote: > On Tue, Jan 14, 2014 at 3:00 PM, James Bottomley > wrote: > >> Doesn't sound exactly like what I had in mind. What I was suggesting > >> is an analogue of read() that, if it reads full pages of data to a > >> page-aligned address, shares the

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 11:40:38AM -0800, Kevin Grittner wrote: > Robert Haas wrote: > > Jan Kara wrote: > > > >> Just to get some idea about the sizes - how large are the > >> checkpoints we are talking about that cause IO stalls? > > > > Big. > > To quantify that, in a production setting we we

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 09:40:48AM -0500, Robert Haas wrote: > On Mon, Jan 13, 2014 at 5:26 PM, Mel Gorman wrote: > >> Amen to that. Actually, I think NUMA can be (mostly?) fixed by > >> setting zone_reclaim_mode; is there some other problem besides that? > > > > Really? > > > > zone_reclaim_mode

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, 2014-01-14 at 12:39 -0500, Robert Haas wrote: > On Tue, Jan 14, 2014 at 12:20 PM, James Bottomley > wrote: > > On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote: > >> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas wrote: > >> > In terms of avoiding double-buffering, here's my thought

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

On 01/14/2014 11:22 PM, Peter Geoghegan wrote: The problem was that inserted-then-deleted-in-same-xact tuples (both regular and promise) were invisible to all xacts' dirty snapshots, when they should have only been invisible to the deleting xact's dirty snapshot. Right. So it isn't obvious to

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

Dave Chinner write: > Essentially, changing dirty_background_bytes, dirty_bytes and > dirty_expire_centiseconds to be much smaller should make the > kernel start writeback much sooner and so you shouldn't have to > limit the amount of buffers the application has to prevent major > fsync triggered

Re: [HACKERS] [BUGS] Hot standby 9.2.6 -> 9.2.6 PANIC: WAL contains references to invalid pages

I've pushed patches fixing the issues discussed in this thread. While I was working on it, it occurred to me that the whole darn mess might be dead code as of the removal of SnapshotNow. The reason we're doing this at all is the fear that, while some indexscan is in flight from an index entry to

Re: [HACKERS] inherit support for foreign tables

On 11/18/13, 8:36 AM, Robert Haas wrote: On the other hand, the performance costs of checking every row bound for the remote table could be quite steep. Consider an update on an inheritance hierarchy that sets a = a + 1 for every row. If we don't worry about verifying that the resulting rows sa

Re: [HACKERS] Add CREATE support to event triggers

On 1/14/14, 2:05 PM, Robert Haas wrote: On Fri, Jan 10, 2014 at 6:22 PM, Alvaro Herrera wrote: Here's one idea: create a contrib module that (somehow, via APIs to be invented) runs every DDL command that gets executed through the deparsing code, and then parses the result and executes *that* in

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

On 01/14/2014 11:22 PM, Peter Geoghegan wrote: On Tue, Jan 14, 2014 at 2:43 AM, Heikki Linnakangas wrote: You have suspected that many times throughout this thread, and every time there's been a relatively simple solutions to the issues you've raised. I suspect that's also going to be true for

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

James Bottomley wrote: >> We start by creating a chunk of shared memory that all processes >> (we do not use threads) will have mapped at a common address, >> and we read() and write() into that chunk. > > Yes, that's what I was thinking: it's a cache.  About how many > files comprise this cache?

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

I wrote: > to avoid write gluts it must often be limited to 1GB to 1GB. That should have been "1GB to 2GB." -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

James Bottomley wrote: > About how many files comprise this cache?  Are you thinking it's > too difficult for every process to map the files? The shared_buffers area can be mapping anywhere from about 200 files to millions of files, representing a total space of about 6MB on the low end to over

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

On Tue, Jan 14, 2014 at 2:43 AM, Heikki Linnakangas wrote: > Hmm. So the scenario would be that a process inserts a tuple, but kills it > again later in the transaction, and then re-inserts the same value. The > expectation is that because it inserted the value once already, inserting it > again w

Re: [HACKERS] Turning off HOT/Cleanup sometimes

On 8 January 2014 08:33, Simon Riggs wrote: > VACUUM cleans up blocks, which is nice because it happens offline in a > lazy manner. > > We also make SELECT clean up blocks as it goes. That is useful in OLTP > workloads, but it means that large SQL queries and pg_dump effectively > do much the same

Re: [HACKERS] inherit support for foreign tables

Hi all, 2014/1/14 Shigeru Hanada : > I'd like to revisit this feature. Attached patch allows a foreign table to be a child of a table. It also allows foreign tables to have CHECK constraints. These changes provide us a chance to propagate query load to multiple servers via constraint exclusion.

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 3:00 PM, James Bottomley wrote: >> Doesn't sound exactly like what I had in mind. What I was suggesting >> is an analogue of read() that, if it reads full pages of data to a >> page-aligned address, shares the data with the buffer cache until it's >> first written instead

Re: [HACKERS] Add CREATE support to event triggers

On Fri, Jan 10, 2014 at 6:22 PM, Alvaro Herrera wrote: >> Here's one idea: create a contrib module that (somehow, via APIs to be >> invented) runs every DDL command that gets executed through the >> deparsing code, and then parses the result and executes *that* instead >> of the original command.

Re: [HACKERS] [PATCH] Doc fix for VACUUM FREEZE

On Tue, Jan 14, 2014 at 2:39 AM, Amit Kapila wrote: > On Fri, Jan 3, 2014 at 9:02 PM, Peter Eisentraut wrote: >> On 12/17/13, 8:16 PM, Maciek Sakrejda wrote: >>> (now with patch--sorry about that) >> >> This patch doesn't apply. > > There are some recent changes around same place which broke this

Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation

On Tue, Jan 14, 2014 at 1:16 AM, Amit Kapila wrote: > On Tue, Jan 14, 2014 at 2:16 AM, Robert Haas wrote: >> On Sat, Jan 11, 2014 at 1:08 AM, Amit Kapila wrote: >>> Yes, currently this applies to update, what I have in mind is that >>> in future if some one wants to use WAL compression for any o

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

Robert Haas wrote: > Jan Kara wrote: > >> Just to get some idea about the sizes - how large are the >> checkpoints we are talking about that cause IO stalls? > > Big. To quantify that, in a production setting we were seeing pauses of up to two minutes with shared_buffers set to 8GB and default d

Re: [HACKERS] PoC: Partial sort

On Tue, Jan 14, 2014 at 9:28 PM, Alexander Korotkov wrote: > On Tue, Jan 14, 2014 at 11:16 PM, Marti Raudsepp wrote: > >> Oh, this actually highlights a performance regression with the partial >> sort patch. >> > > Interesting. Could you share the dataset? > It occurs with many datasets if work_

Re: [HACKERS] Comment typo in src/include/access/gin_private.h

On Mon, Jan 13, 2014 at 9:59 PM, Etsuro Fujita wrote: > I ran into a typo in src/include/access/gin_private.h. Patch attached. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgres

Re: [HACKERS] shared memory message queues

On Tue, Jan 14, 2014 at 1:54 PM, Alvaro Herrera wrote: > Robert Haas escribió: >> On Tue, Jan 14, 2014 at 12:43 PM, Thom Brown wrote: >> > LOG: worker process: test_shm_mq (PID 22041) exited with exit code 1 >> > LOG: unregistering background worker "test_shm_mq" >> >> This is (perhaps unfortun

Re: [HACKERS] PoC: Partial sort

On Tue, Jan 14, 2014 at 11:16 PM, Marti Raudsepp wrote: > On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov > wrote: > >> I implemented a new > >> enable_partialsort GUC to make it easier to turn on/off > > > I though about such option. Generally not because of testing convenience, > > but bec

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

* Robert Haas (robertmh...@gmail.com) wrote: > I dunno what a typical checkpoint size is but I don't think you'll be > exaggerating much if you imagine that everything that could possibly > be dirty is. This is not uncommon for us, at least: checkpoint complete: wrote 425844 buffers (20.3%); 0 tr

Re: [HACKERS] PoC: Partial sort

On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov wrote: >> I implemented a new >> enable_partialsort GUC to make it easier to turn on/off > I though about such option. Generally not because of testing convenience, > but because of overhead of planning. This way you implement it is quite > naiv

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue 14-01-14 10:04:16, Robert Haas wrote: > On Tue, Jan 14, 2014 at 5:00 AM, Jan Kara wrote: > > I thought that instead of injecting pages into pagecache for aging as you > > describe in 3), you would mark pages as volatile (i.e. for reclaim by > > kernel) through vrange() syscall. Next time yo

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 2:03 PM, Gavin Flower wrote: > Say a byte (this is arbitrary, it could be a single hint bit which meant > "please, Please, PLEASE don't flush, if that is okay with you Mr > Kernel..."), so strength would be S = (unsigned byte value)/256, so 0 <= S < > 1. > > S = 0 flus

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 1:37 PM, Jan Kara wrote: > Just to get some idea about the sizes - how large are the checkpoints we > are talking about that cause IO stalls? Big. Potentially, we might have dirtied all of shared_buffers and then started evicting pages from there to the OS buffer pool and

Re: [HACKERS] Add force option to dropdb

salah jubeh wrote: > For the sake of completeness: > 1. I think also, I need also to temporary disallow conecting to the database, > is that right? > 2. Is there other factors can hinder dropping database? If the user owns objects, that will prevent this from working also. I have the feeling th

Re: [HACKERS] [Lsf-pc] Linux kernel impact on PostgreSQL performance

On 14/01/14 14:09, Dave Chinner wrote: On Mon, Jan 13, 2014 at 09:29:02PM +, Greg Stark wrote: On Mon, Jan 13, 2014 at 9:12 PM, Andres Freund wrote: [...] The more ambitious and interesting direction is to let Postgres tell the kernel what it needs to know to manage everything. To do that

Re: [HACKERS] GIN improvements part2: fast scan

On 01/14/2014 05:35 PM, Alexander Korotkov wrote: On Thu, Nov 21, 2013 at 12:14 AM, Alexander Korotkov wrote: Revised version of patch is attached. Changes are so: 1) Support for GinFuzzySearchLimit. 2) Some documentation. Question about GinFuzzySearchLimit is still relevant. Attached versio

Re: [HACKERS] shared memory message queues

Robert Haas escribió: > On Tue, Jan 14, 2014 at 12:43 PM, Thom Brown wrote: > > LOG: worker process: test_shm_mq (PID 22041) exited with exit code 1 > > LOG: unregistering background worker "test_shm_mq" > > This is (perhaps unfortunately) required by the background-worker API. > When a proce

[HACKERS] Add force option to dropdb

Hello, I think adding a force option to dropdb is usefull for many purposes, mainly; scripting. For example, in a test enviroment, I restore a daily backup of production databases. The newly created databases are used as templates for development and also for reading, thus replacing them is com

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue 14-01-14 06:42:43, Kevin Grittner wrote: > First off, I want to give a +1 on everything in the recent posts > from Heikki and Hannu. > > Jan Kara wrote: > > > Now the aging of pages marked as volatile as it is currently > > implemented needn't be perfect for your needs but you still have

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

* Claudio Freire (klaussfre...@gmail.com) wrote: > On Tue, Jan 14, 2014 at 2:17 PM, Robert Haas wrote: > > I don't know either. I wasn't thinking so much that it would save CPU > > time as that it would save memory. Consider a system with 32GB of > > RAM. If you set shared_buffers=8GB, then in

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 2:39 PM, Robert Haas wrote: > On Tue, Jan 14, 2014 at 12:20 PM, James Bottomley > wrote: >> On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote: >>> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas wrote: >>> > In terms of avoiding double-buffering, here's my thought afte

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 2:17 PM, Robert Haas wrote: > On Tue, Jan 14, 2014 at 12:15 PM, Claudio Freire > wrote: >> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas wrote: >>> In terms of avoiding double-buffering, here's my thought after reading >>> what's been written so far. Suppose we read a pa

Re: [HACKERS] extension_control_path

> But you can have a single $SHAREDIR per set of executables, right? > > Please read the following email to know what they asked for and how they > do operate OpenShift: > > > http://www.postgresql.org/message-id/341087492.2585530.1376776393038.javamail.r...@redhat.com FWIW, I'm talking with

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 6:44 PM, Dave Chinner wrote: > On Tue, Jan 14, 2014 at 02:26:25AM +0100, Andres Freund wrote: > > On 2014-01-13 17:13:51 -0800, James Bottomley wrote: > > > a file into a user provided buffer, thus obtaining a page cache entry > > > and a copy in their userspace buffer, th

Re: [HACKERS] extension_control_path

Tom Lane writes: > Dimitri Fontaine writes: >> Tom Lane writes: >>> Why is that a good idea? It's certainly not going to simplify DBAs' >>> lives, more the reverse. ("This dump won't reload." "Uh, where did >>> you get that extension from?" "Ummm...") > >> The latest users for the feature are

Re: [HACKERS] shared memory message queues

On Tue, Jan 14, 2014 at 12:43 PM, Thom Brown wrote: > postgres=# SELECT test_shm_mq(32768, (select > string_agg(chr(32+(random()*96)::int), '') from generate_series(1,3)), > 1, 10); > ERROR: could not register background process > HINT: You may need to increase max_worker_processes. > STATEMENT:

Re: [HACKERS] plpgsql.consistent_into

Marko Tiikkaja writes: > On 1/14/14, 6:15 PM, Tom Lane wrote: >> I'm not too sure what it'd take to make this work. Right now, >> >> SELECT (SELECT x, y FROM foo WHERE id = 42); >> >> would generate "ERROR: subquery must return only one column", but >> I think it's mostly a historical artifact

Re: [HACKERS] plpgsql.consistent_into

On 1/14/14, 6:15 PM, Tom Lane wrote: Marko Tiikkaja writes: How about: (a) = SELECT 1; (a, b) = SELECT 1, 2; (a, b) = INSERT INTO foo RETURNING col1, col2; Same semantics: TOO_MANY_ROWS on rows > 1, sets FOUND and row_count. AFAICT this can be parsed unambiguously, too, and w

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, 2014-01-14 at 10:39 -0500, Tom Lane wrote: > James Bottomley writes: > > The current mechanism for coherency between a userspace cache and the > > in-kernel page cache is mmap ... that's the only way you get the same > > page in both currently. > > Right. > > > glibc used to have an impl

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote: > On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas wrote: > > > > In terms of avoiding double-buffering, here's my thought after reading > > what's been written so far. Suppose we read a page into our buffer > > pool. Until the page is clean,

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, 2014-01-14 at 11:48 -0500, Robert Haas wrote: > On Tue, Jan 14, 2014 at 11:44 AM, James Bottomley > wrote: > > No, I'm sorry, that's never going to be possible. No user space > > application has all the facts. If we give you an interface to force > > unconditional holding of dirty pages

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

Robert Haas writes: > On Tue, Jan 14, 2014 at 11:57 AM, James Bottomley > wrote: >> No, I do ... you mean the order of write out, if we have to do it, is >> important. In the rest of the kernel, we do this with barriers which >> causes ordered grouping of I/O chunks. If we could force a similar

Re: [HACKERS] shared memory message queues

On 14 January 2014 17:29, Robert Haas wrote: > On Mon, Dec 23, 2013 at 12:46 PM, Robert Haas wrote: >> Oh, dear. That's rather embarrassing. >> >> Incremental (incremental-shm-mq.patch) and full (shm-mq-v3.patch) >> patches attached. > > OK, I have pushed the patches in this stack. I'm not sure

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

On Tue, Jan 14, 2014 at 12:20 PM, James Bottomley wrote: > On Tue, 2014-01-14 at 15:15 -0200, Claudio Freire wrote: >> On Tue, Jan 14, 2014 at 2:12 PM, Robert Haas wrote: >> > In terms of avoiding double-buffering, here's my thought after reading >> > what's been written so far. Suppose we read

Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe

On 7 July 2013 14:24, Simon Riggs wrote: > On 3 January 2012 18:42, Tom Lane wrote: >> I wrote: Another point that requires some thought is that switching SnapshotNow to be MVCC-based will presumably result in a noticeable increase in each backend's rate of wanting to acquire snaps

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

On Mon, Jan 13, 2014 at 2:36 PM, Mel Gorman wrote: > On Mon, Jan 13, 2014 at 06:27:03PM -0200, Claudio Freire wrote: > > On Mon, Jan 13, 2014 at 5:23 PM, Jim Nasby wrote: > > > On 1/13/14, 2:19 PM, Claudio Freire wrote: > > >> > > >> On Mon, Jan 13, 2014 at 5:15 PM, Robert Haas > > >> wrote: >

  1   2   >