Re: [HACKERS] Triggers on foreign tables

2013-10-10 Thread Ronan Dunklau
Le dimanche 6 octobre 2013 22:33:23 Kohei KaiGai a écrit : 2013/9/10 Ronan Dunklau rdunk...@gmail.com: For row-level triggers, it seems more complicated. From what I understand, OLD/NEW tuples are fetched from the heap using their ctid (except for BEFORE INSERT triggers). How could this be

Re: [HACKERS] PSQL return coder

2013-10-10 Thread Tom Lane
James Sewell james.sew...@lisasoft.com writes: My question is in a rollback scenario is it possible to get PSQL to return a non 0 exit status? Maybe you could use -c instead of -f? $ psql -c 'select 1; select 1/0' regression ERROR: division by zero $ echo $? 1 You won't need explicit

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-10-10 Thread Andres Freund
On 2013-10-02 13:16:06 +0900, Michael Paquier wrote: Each patch applied with its parents compiles, has no warnings AFAIK and passes regression/isolation tests. Working on 0004 by the end of the CF seems out of the way IMO, so I'd suggest focusing on 0002 and 0003 now, and I can put some time

Re: [HACKERS] Patch for fast gin cache performance improvement

2013-10-10 Thread Etsuro Fujita
Ian Link wrote: Although I asked this question, I've reconsidered about these parameters, and it seems that these parameters not only make code rather complex but are a little confusing to users. So I'd like to propose to introduce only one parameter: fast_cache_size. While users that

Re: [HACKERS] CommitFest progress

2013-10-10 Thread Dimitri Fontaine
Robert Haas robertmh...@gmail.com writes: The CommitFest is supposed to be a time to *commit the patches that are ready to be committed*, not to wait indefinitely for them to become ready to be committed. I beg to differ. Commit Fests are the time when patch authors know they can get

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread pilum . 70
The V7-Patch applied cleanly and I got no issues in my first tests. The change from column session_start to a function seems very reasonable for me. Concernig the usability, I would like to suggest a minor change, that massively increases the usefulness of the patch for beginners, who often

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Sameer Thakur
Please find patch attached which adds documentation for session_start and introduced fields and corrects documentation for queryid to be query_id. session_start remains in the view as agreed. regards Sameer pg_stat_statements-identification-v8.patch.gz Description: GNU Zip compressed data --

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 3:11 AM, pilum...@uni-muenster.de wrote: But the drawback of this approach is impossibility to use explain analyze without further substitutions. You can fairly easily disable the swapping of constants with '?' symbols, so that the query text stored would match the full

Re: [HACKERS] CommitFest progress

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 5:21 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: The CommitFest is supposed to be a time to *commit the patches that are ready to be committed*, not to wait indefinitely for them to become ready to be committed. I beg

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Wed, Oct 9, 2013 at 11:35 PM, Peter Geoghegan p...@heroku.com wrote: On Wed, Oct 9, 2013 at 8:20 PM, Bruce Momjian br...@momjian.us wrote: I am not sure that having that external to the backend really makes sense because I am concerned people will not use it. We can certainly add it to

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander mag...@hagander.net wrote: I think it would be even simpler, and more reliable, to start with the parameter to initdb - I like that. But instead of having it set a new variable based on that and then autotune off that, just have *initdb* do

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

2013-10-10 Thread Heikki Linnakangas
On 09.10.2013 02:04, Tomas Vondra wrote: On 8.10.2013 21:59, Heikki Linnakangas wrote: On 08.10.2013 17:47, Alexander Korotkov wrote: Hi, Tomas! On Sun, Oct 6, 2013 at 3:58 AM, Tomas Vondrat...@fuzzy.cz wrote: I've attempted to rerun the benchmarks tests I did a few weeks ago, but I

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread pilum . 70
Thx for your reply. On Thu, 10 Oct 2013, Peter Geoghegan wrote: On Thu, Oct 10, 2013 at 3:11 AM, pilum...@uni-muenster.de wrote: But the drawback of this approach is impossibility to use explain analyze without further substitutions. You can fairly easily disable the swapping of constants

[HACKERS] strange behavior of pg_trgm's similarity function

2013-10-10 Thread Fujii Masao
Hi, The behavior of pg_trgm's similarity function seems strange. Is this intentional? I was thinking that the following three calls of the similarity function return the same number because the second argument is just the three characters contained in the first argument in every calls. =#

Re: [HACKERS] strange behavior of pg_trgm's similarity function

2013-10-10 Thread Heikki Linnakangas
On 10.10.2013 15:03, Fujii Masao wrote: Hi, The behavior of pg_trgm's similarity function seems strange. Is this intentional? I was thinking that the following three calls of the similarity function return the same number because the second argument is just the three characters contained in

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Peter Geoghegan (p...@heroku.com) wrote: On Wed, Oct 9, 2013 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote: There is definitely something to be said for simplicity and just up'ing the default would have a more dramatic impact with a setting like work_mem than it would with

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Magnus Hagander (mag...@hagander.net) wrote: I think it would be even simpler, and more reliable, to start with the parameter to initdb - I like that. But instead of having it set a new variable based on that and then autotune off that, just have *initdb* do these calculations you're

Re: [HACKERS] space reserved for WAL record does not match what was written: panic on windows

2013-10-10 Thread Robert Haas
On Tue, Oct 8, 2013 at 6:24 PM, Andres Freund and...@2ndquadrant.com wrote: Do you have a better alternative? Making the computation unconditionally 64bit will have a runtime overhead and adding a StaticAssert in the existing macro doesn't work because we use it in array sizes where gcc balks.

Re: [HACKERS] Patch: FORCE_NULL option for copy COPY in CSV mode

2013-10-10 Thread Andrew Dunstan
On 10/09/2013 11:47 PM, Amit Kapila wrote: One of the advantage, I could see using NULL For .. syntax is that already we have one syntax with which user can specify what strings can be replaced with NULL, now just to handle quoted empty string why to add different syntax. FORCE_NULL has

Re: [HACKERS] space reserved for WAL record does not match what was written: panic on windows

2013-10-10 Thread Andres Freund
On 2013-10-10 08:59:47 -0400, Robert Haas wrote: On Tue, Oct 8, 2013 at 6:24 PM, Andres Freund and...@2ndquadrant.com wrote: Do you have a better alternative? Making the computation unconditionally 64bit will have a runtime overhead and adding a StaticAssert in the existing macro doesn't

Re: [HACKERS] PSQL return coder

2013-10-10 Thread Merlin Moncure
On Thu, Oct 10, 2013 at 1:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: James Sewell james.sew...@lisasoft.com writes: My question is in a rollback scenario is it possible to get PSQL to return a non 0 exit status? Maybe you could use -c instead of -f? $ psql -c 'select 1; select 1/0'

Re: [HACKERS] Backup throttling

2013-10-10 Thread Antonin Houska
On 10/09/2013 08:56 PM, Robert Haas wrote: There seem to be several review comments made since you posted this version. I'll mark this Waiting on Author in the CommitFest application, since it seems that the patch needs to be further updated. Since it was waiting for reviewer, I was not sure

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

2013-10-10 Thread Amit Kapila
One of the user's of PostgreSQL has reported that if tablespace path is long, it leads to hang and the hang is unbreakable. Simple testcase to reproduce hang is: a. initdb -D

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

2013-10-10 Thread Mike Blackwell
I'd received an email from Gibheer suggesting it be move due to lack of time to work on it. I can certainly move it back if that's no longer the case. On Oct 9, 2013, at 23:25, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Oct 10, 2013 at 3:17 AM, Gibheer gibh...@zero-knowledge.org

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread MauMau
From: Bruce Momjian br...@momjian.us I will work on auto-tuning temp_buffers next. Any other suggestions? wal_buffers is already auto-tuned. Great work. I'm looking forward to becoming able to fully utilize system resources right after initdb. Although this is not directly related to

Re: [HACKERS] strange behavior of pg_trgm's similarity function

2013-10-10 Thread Merlin Moncure
On Thu, Oct 10, 2013 at 7:12 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 10.10.2013 15:03, Fujii Masao wrote: Hi, The behavior of pg_trgm's similarity function seems strange. Is this intentional? I was thinking that the following three calls of the similarity function return

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate *into* initdb.  So you'd do initdb --system-memory 8GB or something like that and it would do the rest.  That'd be slick, at least IMHO. How would you handle the case that the machine

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Fujii Masao
On Thu, Oct 10, 2013 at 7:20 PM, Sameer Thakur samthaku...@gmail.com wrote: Please find patch attached which adds documentation for session_start and introduced fields and corrects documentation for queryid to be query_id. session_start remains in the view as agreed. Thanks for updating the

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread MauMau
From: Robert Haas robertmh...@gmail.com On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander mag...@hagander.net wrote: I think it would be even simpler, and more reliable, to start with the parameter to initdb - I like that. But instead of having it set a new variable based on that and then

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote: From: Bruce Momjian br...@momjian.us I will work on auto-tuning temp_buffers next. Any other suggestions? wal_buffers is already auto-tuned. Great work. I'm looking forward to becoming able to fully utilize system resources right

Re: [HACKERS] [COMMITTERS] pgsql: Revive line type

2013-10-10 Thread Robert Haas
On Wed, Oct 9, 2013 at 10:43 PM, Peter Eisentraut pete...@gmx.net wrote: Revive line type Kevin just pointed out to me that there are a bunch of buildfarm failures. I'm looking at the ones that are attributable to shared memory, but there seem to be some problems with this patch as well. Check

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Dimitri Fontaine
MauMau maumau...@gmail.com writes: Although this is not directly related to memory, could you set max_prepared_transactions = max_connections at initdb time? People must You really need to have a transaction manager around when issuing prepared transaction as failing to commit/rollback them

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate *into* initdb.  So you'd do initdb --system-memory 8GB or something like that and it would do the rest.  That'd be

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate *into* initdb.  So you'd do initdb --system-memory 8GB or something

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Daniel Farina
On Thu, Oct 10, 2013 at 7:40 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Oct 10, 2013 at 7:20 PM, Sameer Thakur samthaku...@gmail.com wrote: Please find patch attached which adds documentation for session_start and introduced fields and corrects documentation for queryid to be

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Wed, Oct 9, 2013 at 09:34:16PM -0400, Robert Haas wrote: But your auto-tuned value can easily be too low or too high, too. Consider someone with a system that has 64GB of RAM. EnterpriseDB has had customers who have found that with, say, a 40GB database, it's best to set shared_buffers

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I actually had the thought that it might be something we'd integrate

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote: For this case, I think the suggestion made by MauMau would be better- tell the user (in the postgresql.conf comments) a command they can run with different memory settings to see what

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:45:41AM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote: For this case, I think the suggestion made by MauMau would be better- tell the user (in the postgresql.conf comments) a

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling the tool, though the tool then would need to be in C probably as we can't require python for initdb. The tool would not address Robert's issue of someone increasing shared_buffers on their own. I'm really not

[HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Robert Haas
Since, as has been previously discussed in this forum on multiple occasions [citation needed], the default System V shared memory limits are absurdly low on many systems, the dynamic shared memory patch defaults to POSIX shared memory, which has often been touted as a superior alternative

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

2013-10-10 Thread Dimitri Fontaine
Hi, I did a partial review of this patch, wherein I focused on the patch and the code itself, as I saw other contributors already did some testing on it, so that we know it applies cleanly and work to some good extend. Fujii Masao masao.fu...@gmail.com writes: In this patch, full_page_writes

Re: [HACKERS] Add json_typeof() and json_is_*() functions.

2013-10-10 Thread Andrew Dunstan
On 08/06/2013 08:42 AM, Robert Haas wrote: On Fri, Aug 2, 2013 at 8:22 AM, Andrew Tipton and...@kiwidrew.com wrote: But without json_is_scalar(), the choice is one of these two forms: json_typeof() NOT IN ('object', 'array') json_typeof() IN ('string', 'number', 'boolean', 'null') The

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling the tool, though the tool then would need to be in C probably as we can't require python for initdb. The tool would not address Robert's issue of

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

2013-10-10 Thread Gibheer
On Thu, 10 Oct 2013 09:55:24 +0530 Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Oct 10, 2013 at 3:17 AM, Gibheer gibh...@zero-knowledge.org wrote: On Mon, 7 Oct 2013 11:39:55 +0530 Amit Kapila amit.kapil...@gmail.com wrote: Robert Haas wrote: On Mon, Aug 5, 2013 at 2:04 AM, Andres

Re: [HACKERS] [COMMITTERS] pgsql: Add record_image_ops opclass for matview concurrent refresh.

2013-10-10 Thread Kevin Grittner
Kevin Grittner kgri...@postgresql.org wrote: Add record_image_ops opclass for matview concurrent refresh. The buildfarm pointed out that I had not handled pass-by-value data types correctly.  Fixed based on advice from Robert.  We'll see whether that clears up the part of the buildfarm breakage

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 12:28 PM, Bruce Momjian wrote: How do we handle the Python dependency, or is this all to be done in some other language? I certainly am not ready to take on that job. Without considering any wider question here, let me just note this: Anything that can be done in this area

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:28 PM, Bruce Momjian wrote: How do we handle the Python dependency, or is this all to be done in some other language? I certainly am not ready to take on that job. Without considering any wider question

Re: [HACKERS] Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

2013-10-10 Thread Andrew Dunstan
On 09/19/2013 06:12 PM, Pavel Stehule wrote: 2013/9/16 Satoshi Nagayasu sn...@uptime.jp mailto:sn...@uptime.jp I'm looking at this patch, and I have a question here. Should DROP TRIGGER IF EXISTS ignore error for non-existing trigger and non-existing table? Or just only

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 12:45 PM, Bruce Momjian wrote: On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:28 PM, Bruce Momjian wrote: How do we handle the Python dependency, or is this all to be done in some other language? I certainly am not ready to take on that job.

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Fujii Masao
On Fri, Oct 11, 2013 at 12:19 AM, Daniel Farina dan...@heroku.com wrote: Probably. The idea is that without those fields it's, to wit, impossible to explain non-monotonic movement in metrics of those queries for precise use in tools that insist on monotonicity of the fields, which are all

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:59:39PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:45 PM, Bruce Momjian wrote: On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote: On 10/10/2013 12:28 PM, Bruce Momjian wrote: How do we handle the Python dependency, or is this all to be done in some

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
Because 'maintenance' operations were rarer, so we figured we could use more memory in those cases. Once we brought Autovacuum into core, though, we should have changed that. However, I agree with Magnus that the simple course is to have an autovacuum_worker_memory setting which overrides

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
On 10/09/2013 02:15 PM, Bruce Momjian wrote: and for shared_buffers of 2GB: test= show shared_buffers; shared_buffers 2GB (1 row) test= SHOW work_mem; work_mem -- 6010kB (1 row) Huh?

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 10:20:02AM -0700, Josh Berkus wrote: On 10/09/2013 02:15 PM, Bruce Momjian wrote: and for shared_buffers of 2GB: test= show shared_buffers; shared_buffers 2GB (1 row) test= SHOW work_mem; work_mem

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

2013-10-10 Thread Fujii Masao
On Tue, Oct 8, 2013 at 10:07 PM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: Hi, I tested dbt-2 benchmark in single instance and synchronous replication. Thanks! Unfortunately, my benchmark results were not seen many differences... * Test server Server: HP Proliant DL360 G7

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

2013-10-10 Thread Fujii Masao
On Wed, Oct 9, 2013 at 1:35 PM, Haribabu kommi haribabu.ko...@huawei.com wrote: On 08 October 2013 18:42 KONDO Mitsumasa wrote: (2013/10/08 20:13), Haribabu kommi wrote: I will test with sync_commit=on mode and provide the test results. OK. Thanks! Pgbench test results with synchronous_commit

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
All, We can't reasonably require user input at initdb time, because most users don't run initdb by hand -- their installer does it for them. So any tuning which initdb does needs to be fully automated. So, the question is: can we reasonably determine, at initdb time, how much RAM the system

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Alvaro Herrera
Daniel Farina escribió: On Thu, Oct 10, 2013 at 7:40 AM, Fujii Masao masao.fu...@gmail.com wrote: In my test, I found that pg_stat_statements.total_time always indicates a zero. I guess that the patch might handle pg_stat_statements.total_time wrongly. +values[i++] =

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: I'm really not impressed with this argument. Either the user is going to go and modify the config file, in which case I would hope that they'd at least glance around at what

[HACKERS] Re: dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread David Johnston
Robert Haas wrote Unfortunately, the buildfarm isn't entirely happy with this decision. On buildfarm member anole (HP-UX B.11.31), allocation of dynamic shared memory fails with a Permission denied error, and on smew (Debian GNU/Linux 6.0), it fails with Function not implemented, which

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Jeff Janes
On Wed, Oct 9, 2013 at 8:06 AM, Andrew Dunstan and...@dunslane.net wrote: On 10/09/2013 10:45 AM, Bruce Momjian wrote: On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote: Effectively, if every session uses one full work_mem, you end up with total work_mem usage equal

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Claudio Freire
On Thu, Oct 10, 2013 at 1:13 PM, Robert Haas robertmh...@gmail.com wrote: (1) Define the issue as not our problem. IOW, as of now, if you want to use PostgreSQL, you've got to either make POSIX shared memory work on your machine, or change the GUC that selects the type of dynamic shared

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
Bruce, That's way low, and frankly it's not worth bothering with this if all we're going to get is an incremental increase. In that case, let's just set the default to 4MB like Robert suggested. Uh, well, 100 backends at 6MB gives us 600MB, and if each backend uses 3x work_mem, that gives

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 12:13 PM, Robert Haas wrote: Since, as has been previously discussed in this forum on multiple occasions [citation needed], the default System V shared memory limits are absurdly low on many systems, the dynamic shared memory patch defaults to POSIX shared memory, which has often

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
It also doesn't address my point that, if we are worst-case-scenario default-setting, we're going to end up with defaults which aren't materially different from the current defaults. In which case, why even bother with this whole exercise? Oh, and let me reiterate: the way to optimize

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 2:21 PM, Andrew Dunstan and...@dunslane.net wrote: Other votes? Other ideas? 5) test and set it in initdb. Are you advocating for that option, or just calling out that it's possible? I'd say that's closely related to option #3, except at initdb time rather than

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 9:13 AM, Robert Haas robertmh...@gmail.com wrote: (2) Default to using System V shared memory. If people want POSIX shared memory, let them change the default. After some consideration, I think my vote is for option #2. Wouldn't that become the call of packagers?

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling the tool, though the tool then would need to be in C probably as we can't

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus j...@agliodbs.com wrote: So, the question is: can we reasonably determine, at initdb time, how much RAM the system has? As long as you are willing to write platform-dependent code, yes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The

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

2013-10-10 Thread Fujii Masao
On Fri, Oct 11, 2013 at 1:20 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Hi, I did a partial review of this patch, wherein I focused on the patch and the code itself, as I saw other contributors already did some testing on it, so that we know it applies cleanly and work to some good

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 11:41 AM, Robert Haas robertmh...@gmail.com wrote: I don't see why it can't be done in C. The server is written in C, and so is initdb. So no matter where we do this, it's gonna be in C. Where does Python enter into it? I mentioned that pgtune was written in Python,

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 11:43 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus j...@agliodbs.com wrote: So, the question is: can we reasonably determine, at initdb time, how much RAM the system has? As long as you are willing to write

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Eisentraut
On 10/10/13 11:31 AM, Bruce Momjian wrote: Let me walk through the idea of adding an available_mem setting, that Josh suggested, and which I think addresses Robert's concern about larger shared_buffers and Windows servers. I think this is a promising idea. available_mem could even be set

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 2:45 PM, Josh Berkus j...@agliodbs.com wrote: On 10/10/2013 11:41 AM, Robert Haas wrote: tunedb --available-memory=32GB ...and it will print out a set of proposed configuration settings. If we want a mode that rewrites the configuration file, we could have: tunedb

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Fujii Masao
On Fri, Oct 11, 2013 at 2:49 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Daniel Farina escribió: On Thu, Oct 10, 2013 at 7:40 AM, Fujii Masao masao.fu...@gmail.com wrote: In my test, I found that pg_stat_statements.total_time always indicates a zero. I guess that the patch might

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Magnus Hagander
On Thu, Oct 10, 2013 at 8:41 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: Well, I like the idea of initdb calling

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
On 10/10/2013 11:41 AM, Robert Haas wrote: tunedb --available-memory=32GB ...and it will print out a set of proposed configuration settings. If we want a mode that rewrites the configuration file, we could have: tunedb --available-memory=32GB --rewrite-config-file=$PATH ...but that

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 2:36 PM, Peter Geoghegan p...@heroku.com wrote: On Thu, Oct 10, 2013 at 9:13 AM, Robert Haas robertmh...@gmail.com wrote: (2) Default to using System V shared memory. If people want POSIX shared memory, let them change the default. After some consideration, I think my

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Wed, Oct 9, 2013 at 10:21 PM, Magnus Hagander mag...@hagander.net wrote: Well, the Postgres defaults won't really change, because the default vacuum_work_mem will be -1, which will have vacuum defer to maintenance_work_mem. Under this scheme, vacuum only *prefers* to get bound working

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Eisentraut
On 10/10/13 11:45 AM, Bruce Momjian wrote: I think the big win for a tool would be to query the user about how they are going to be using Postgres, and that can then spit out values the user can add to postgresql.conf, or to a config file that is included at the end of postgresql.conf. I

Re: [HACKERS] strange behavior of pg_trgm's similarity function

2013-10-10 Thread Fujii Masao
On Thu, Oct 10, 2013 at 11:00 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Oct 10, 2013 at 7:12 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 10.10.2013 15:03, Fujii Masao wrote: Hi, The behavior of pg_trgm's similarity function seems strange. Is this intentional? I

Re: [HACKERS] [v9.4] row level security

2013-10-10 Thread Marc Munro
On Wed, 2013-09-04 at 14:35 +, Robert Haas wrote: On Fri, Aug 30, 2013 at 3:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think it's entirely sensible to question whether we should reject (not hold up) RLS if it has major covert-channel problems. We've already had this argument

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Magnus Hagander
On Thu, Oct 10, 2013 at 8:46 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 10, 2013 at 2:45 PM, Josh Berkus j...@agliodbs.com wrote: On 10/10/2013 11:41 AM, Robert Haas wrote: tunedb --available-memory=32GB ...and it will print out a set of proposed configuration settings. If we

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 02:35 PM, Robert Haas wrote: On Thu, Oct 10, 2013 at 2:21 PM, Andrew Dunstan and...@dunslane.net wrote: Other votes? Other ideas? 5) test and set it in initdb. Are you advocating for that option, or just calling out that it's possible? I'd say that's closely related to option

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Thu, Oct 10, 2013 at 2:36 PM, Peter Geoghegan p...@heroku.com wrote: On Thu, Oct 10, 2013 at 9:13 AM, Robert Haas robertmh...@gmail.com wrote: (2) Default to using System V shared memory. If people want POSIX shared memory, let them change

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Christopher Browne
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: How do we handle the Python dependency, or is this all to be done in some other language? I certainly am not ready to take on that job. I should think it possible to reimplement it in C. It was considerably useful to

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Daniel Farina
On Thu, Oct 10, 2013 at 10:12 AM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Oct 11, 2013 at 12:19 AM, Daniel Farina dan...@heroku.com wrote: Probably. The idea is that without those fields it's, to wit, impossible to explain non-monotonic movement in metrics of those queries for

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 3:41 PM, Christopher Browne cbbro...@gmail.com wrote: On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: How do we handle the Python dependency, or is this all to be done in some other language? I certainly am not ready to take on that job. I

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Merlin Moncure
On Thu, Oct 10, 2013 at 11:13 AM, Robert Haas robertmh...@gmail.com wrote: Since, as has been previously discussed in this forum on multiple occasions [citation needed], the default System V shared memory limits are absurdly low on many systems, the dynamic shared memory patch defaults to

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Daniel Farina
On Thu, Oct 10, 2013 at 10:49 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Daniel Farina escribió: On Thu, Oct 10, 2013 at 7:40 AM, Fujii Masao masao.fu...@gmail.com wrote: In my test, I found that pg_stat_statements.total_time always indicates a zero. I guess that the patch might

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Alvaro Herrera
Daniel Farina escribió: Given that, perhaps a way to fix this is something like this patch-fragment: { PGSS_TUP_V1_0 = 1, PGSS_TUP_V1_1, - PGSS_TUP_LATEST + PGSS_TUP_V1_2 } pgssTupVersion; +#define PGSS_TUP_LATEST PGSS_TUP_V1_2 This sounds good. I have seen other places

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Andrew Dunstan
On 10/10/2013 02:45 PM, Robert Haas wrote: On Thu, Oct 10, 2013 at 2:36 PM, Peter Geoghegan p...@heroku.com wrote: On Thu, Oct 10, 2013 at 9:13 AM, Robert Haas robertmh...@gmail.com wrote: (2) Default to using System V shared memory. If people want POSIX shared memory, let them change the

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread David Fetter
On Thu, Oct 10, 2013 at 12:13:20PM -0400, Robert Haas wrote: Since, as has been previously discussed in this forum on multiple occasions [citation needed], the default System V shared memory limits are absurdly low on many systems, the dynamic shared memory patch defaults to POSIX shared

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread pilum . 70
The V7-Patch applied cleanly and I got no issues in my first tests. The change from column session_start to a function seems very reasonable for me. Concernig the usability, I would like to suggest a minor change, that massively increases the usefulness of the patch for beginners, who often

[HACKERS] Pattern matching operators a index

2013-10-10 Thread soroosh sardari
Hi I'm developing a new type for character string, like varchar. I wrote operators for btree and so forth. I wonder how pattern matching operators using btree index, because btree operator class ony knows about , =, =, and = operators, but operators for pattern matching, such as LIKE, are not

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 4:00 PM, Merlin Moncure mmonc...@gmail.com wrote: (2) Default to using System V shared memory. If people want POSIX shared memory, let them change the default. Doesn't #2 negate all advantages of this effort? Bringing sysv management back on the table seems like a

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

2013-10-10 Thread Daniel Farina
On Thu, Oct 10, 2013 at 1:30 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Just noticed that you changed the timer to struct Instrumentation. Not really sure about that change. Since you seem to be using only the start time and counter, wouldn't it be better to store only those?

Re: [HACKERS] dynamic shared memory: wherein I am punished for good intentions

2013-10-10 Thread Josh Berkus
Robert, Doesn't #2 negate all advantages of this effort? Bringing sysv management back on the table seems like a giant step backwards -- or am I missing something? Not unless there's no difference between the default and the only option. Well, per our earlier discussion about the first 15

Re: [HACKERS] Re: Request for Patch Feedback: Lag Lead Window Functions Can Ignore Nulls

2013-10-10 Thread Alvaro Herrera
We have this block: + { + /* +* This is the window we want - but we have to tweak the +* definition slightly (e.g. to support the IGNORE NULLS frame +* option) as we're not using the default (i.e. parent) frame +* options. +

  1   2   >