[HACKERS] patch: fix a regress tests

2011-10-10 Thread Pavel Stehule
Hello there is fix a locale dependency of regress tests Last time I forgot to attach a patch Regards Pavel Stehule *** ./src/test/regress/expected/foreign_data.out.orig 2011-10-04 13:56:41.0 +0200 --- ./src/test/regress/expected/foreign_data.out 2011-10-06 14:06:20.0 +0200

[HACKERS] Extend file_fdw wrapper

2011-10-10 Thread pasman pasmański
Attached patch. pasman 0001-Extend-file_fdw-wrapper.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread Thom Brown
2011/10/10 pasman pasmański pasma...@gmail.com: Attached patch. ... and what are these new options intended to do? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --

Re: [HACKERS] What is known about PostgreSQL HP-UX support?

2011-10-10 Thread PostgreSQL - Hans-Jürgen Schönig
On Oct 10, 2011, at 4:21 AM, Alex Goncharov wrote: [ Thanks all for the very productive discussion in the thread libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable which I originated. Very useful. Now on something different. ] About two years ago, I had to research some

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-10 Thread Peter Eisentraut
On sön, 2011-10-09 at 11:51 -0400, Tom Lane wrote: The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. Why do we have major and minor protocol version numbers,

Re: [HACKERS] WIP: Collecting statistics on CSV file data

2011-10-10 Thread Etsuro Fujita
(2011/10/07 21:56), David Fetter wrote: (But this is BTW. I'm interested in developing CREATE FOREIGN INDEX. I've examined whether there are discussions about the design and implementation of it in the archive, but could not find information. If you know anything, please tell me.) Look into

Re: [HACKERS] What is known about PostgreSQL HP-UX support?

2011-10-10 Thread Heikki Linnakangas
On 10.10.2011 05:39, Alex Goncharov wrote: 1. The PostgreSQL server will build on HPUX 11.31 IA64. Yes. 2. The server will run all right (speaking on the test cases you personally covered only) on HPUX 11.31 IA64. Yes, works fine. 3. Both PostgreSQL v. 8.4 and 9.1. Not

Re: [HACKERS] What is known about PostgreSQL HP-UX support?

2011-10-10 Thread Alex Goncharov
Thank you all who replied! -- Alex -- alex-goncha...@comcast.net -- /* * They're only trying to make me LOOK paranoid! */ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-10 Thread Kevin Grittner
Florian Pflug wrote: On Oct9, 2011, at 14:20 , Kevin Grittner wrote: Florian Pflug wrote: Coming up with a reasonable algorithm isn't *that* hard. Agreed. Our shop has used a home-grown framework for over a decade where we parse queries using ANTLR ( http://www.antlr.org/ ) and we

Re: [HACKERS] pg_upgrade - add config directory setting

2011-10-10 Thread Bruce Momjian
Bruce Momjian wrote: Bruce Momjian wrote: OK, I have modified the postmaster in PG 9.2 to allow output of the data directory, and modified pg_ctl to use that, so starting in PG 9.2 pg_ctl will work cleanly for config-only directories. I will now work on pg_upgrade to also use the new

Re: [HACKERS] patch: fix a regress tests

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 2:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello there is fix a locale dependency of regress tests Last time I forgot  to attach a patch Committed (but next time please mention the details, so I don't have to guess). -- Robert Haas EnterpriseDB:

Re: [HACKERS] patch: fix a regress tests

2011-10-10 Thread Pavel Stehule
2011/10/10 Robert Haas robertmh...@gmail.com: On Mon, Oct 10, 2011 at 2:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello there is fix a locale dependency of regress tests Last time I forgot  to attach a patch Committed (but next time please mention the details, so I don't have

Re: [HACKERS] Inconsistency in postgresql.conf

2011-10-10 Thread Bruce Momjian
Bruce Momjian wrote: I notice that we use '(none)' as a default for external_pid_file, while other default no-value settings in the file are ''. The attached patch changes this. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB

Re: [HACKERS] [v9.2] DROP statement reworks

2011-10-10 Thread Robert Haas
On Wed, Oct 5, 2011 at 2:58 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hmm. It indeed makes translation hard. I reverted this portion of the part-2 patch, as attached. Please review the newer one, instead of the previous revision. Please fix the compiler warnings. -- Robert Haas

Re: [HACKERS] WIP: Join push-down for foreign tables

2011-10-10 Thread Shigeru Hanada
(2011/10/08 1:06), Kohei KaiGai wrote: What is the reason why the foreign join is not pushed down? Maybe, injected Sort plan prevent the planner to consider both side of relations being foreign scan owned by same server? I'm still investigating the reason. Thanks for your testing. I'm not

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Thom Brown
On 2 October 2011 20:05, Jeff Davis pg...@j-davis.com wrote: On Sun, 2011-10-02 at 11:32 +0200, Florian Pflug wrote: Looking at the patch, I noticed that it's possible to specify the default boundaries ([], [), (] or ()) per individual float type with the DEFAULT_FLAGS clause of CREATE TYPE ..

Re: [HACKERS] [v9.2] DROP statement reworks

2011-10-10 Thread Kohei KaiGai
2011/10/10 Robert Haas robertmh...@gmail.com: On Wed, Oct 5, 2011 at 2:58 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hmm. It indeed makes translation hard. I reverted this portion of the part-2 patch, as attached. Please review the newer one, instead of the previous revision. Please fix the

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread Shigeru Hanada
At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think documents and regression tests would be required for this kind of change. Regards, -- Shigeru Hanada -- Sent via

Re: [HACKERS] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable

2011-10-10 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On sön, 2011-10-09 at 11:51 -0400, Tom Lane wrote: The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. Why do we have

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread David Fetter
On Mon, Oct 10, 2011 at 10:51:03PM +0900, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? I believe that the finer-grained setting should always override the

[HACKERS] ALTER EXTENSION .. ADD/DROP weirdness

2011-10-10 Thread Robert Haas
OK, I'm stumped: rhaas=# create extension pg_stat_statements; CREATE EXTENSION rhaas=# drop view pg_stat_statements; ERROR: cannot drop view pg_stat_statements because extension pg_stat_statements requires it HINT: You can drop extension pg_stat_statements instead. rhaas=# alter extension

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread Andrew Dunstan
On 10/10/2011 09:51 AM, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think documents and regression tests would be required for this kind of change.

Re: [HACKERS] [v9.2] DROP statement reworks

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 9:39 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/10/10 Robert Haas robertmh...@gmail.com: On Wed, Oct 5, 2011 at 2:58 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hmm. It indeed makes translation hard. I reverted this portion of the part-2 patch, as attached.

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread pasman pasmański
Hi. Current behaviour is error message when foreign table and foreign server have the same option defined. I don't know how to write regression test, may i read about it somewhere? -- pasman -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-10-10 Thread Robert Haas
On Sun, Oct 9, 2011 at 11:50 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I tried to refactor the patches based on the interface of WITH (...) and usage of pg_class.reloptions, although here is no functionality changes; including the behavior when a view is replaced. My preference is WITH

Re: [HACKERS] patch: move dumpUserConfig call in dumpRoles function of pg_dumpall.c

2011-10-10 Thread Robert Haas
On Thu, Aug 4, 2011 at 2:04 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Aug 4, 2011 at 1:53 PM, Phil Sorber p...@omniti.com wrote: Ok, here is the patch that just moves the ALTER/SET pieces to the end. Can we get this included in the next commit fest? Yep, just make yourself an

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread David Fetter
On Mon, Oct 10, 2011 at 10:23:51AM -0400, Andrew Dunstan wrote: On 10/10/2011 09:51 AM, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think

Re: [HACKERS] Extend file_fdw wrapper

2011-10-10 Thread Andrew Dunstan
On 10/10/2011 11:59 AM, David Fetter wrote: On Mon, Oct 10, 2011 at 10:23:51AM -0400, Andrew Dunstan wrote: On 10/10/2011 09:51 AM, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table

Re: [HACKERS] patch: move dumpUserConfig call in dumpRoles function of pg_dumpall.c

2011-10-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I don't really understand why it's not OK to just have pg_dump issue RESET ROLE at appropriate points in the process; that seems like it would be sufficient and not particularly ugly. Well, it was alleged that that would fix this problem:

Re: [HACKERS] ALTER EXTENSION .. ADD/DROP weirdness

2011-10-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: rhaas=# alter extension pg_stat_statements drop type pg_stat_statements[]; ERROR: syntax error at or near [ LINE 1: ...extension pg_stat_statements drop type pg_stat_statements[]; ^

Re: [HACKERS] patch: move dumpUserConfig call in dumpRoles function of pg_dumpall.c

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 12:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't really understand why it's not OK to just have pg_dump issue RESET ROLE at appropriate points in the process; that seems like it would be sufficient and not particularly

Re: [HACKERS] WIP: Join push-down for foreign tables

2011-10-10 Thread Robert Haas
2011/10/10 Shigeru Hanada shigeru.han...@gmail.com: (2011/10/08 1:06), Kohei KaiGai wrote: What is the reason why the foreign join is not pushed down? Maybe, injected Sort plan prevent the planner to consider both side of relations being foreign scan owned by same server? I'm still

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Jeff Davis
On Mon, 2011-10-10 at 14:27 +0100, Thom Brown wrote: I don't know if this has already been discussed, but can you explain the following: postgres=# select '[1,8]'::int4range; int4range --- [1,9) (1 row) It seems unintuitive to represent a discrete range using an exclusive

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Tom Lane
Jeff Davis pg...@j-davis.com writes: On Mon, 2011-10-10 at 14:27 +0100, Thom Brown wrote: I don't know if this has already been discussed, but can you explain the following: postgres=# select '[1,8]'::int4range; int4range --- [1,9) (1 row) It seems unintuitive to represent a

Re: [HACKERS] WIP: Join push-down for foreign tables

2011-10-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: This might be out of left field, but wouldn't it make more sense to get postgresql_fdw committed first, and then add the join push-down functionality afterwards? I mean, otherwise, we're going to be left with a situation where we have join pushdown in

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Joe Conway
On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Florian Pflug
On Oct10, 2011, at 18:53 , Tom Lane wrote: What if I write '[1,INT_MAX]'::int4range? The open-parenthesis form will fail with an integer overflow. I suppose you could canonicalize it to an unbounded range, but that seems unnecessarily surprising. That is a very good point. Canonicalizing to

[HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Bruce Momjian
I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Gurjeet Singh
On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Jeff Davis
On Mon, 2011-10-10 at 12:53 -0400, Tom Lane wrote: The canonicalize function (specified at type creation time) allows you to specify the canonical output representation. So, I can change the canonical form for discrete ranges to use '[]' notation if we think that's more expected. What

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Thom Brown
On 10 October 2011 18:23, Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*).  Is this something we can do for PG 9.2?  Is anyone working on this? Yes it does, provided that there is an appropriate WHERE clause. But yes, I

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Greg Stark
On Mon, Oct 10, 2011 at 6:23 PM, Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*).  Is this something we can do for PG 9.2?  Is anyone working on this? People usually conflate multiple problems when they talk about

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Kevin Grittner
Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Well, it's not that it doesn't optimize COUNT(*) -- it's that it doesn't yet cost the index scan as

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Thom Brown
On 10 October 2011 18:31, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-10 at 12:53 -0400, Tom Lane wrote: The canonicalize function (specified at type creation time) allows you to specify the canonical output representation. So, I can change the canonical form for discrete ranges to

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Jeff Davis
On Mon, 2011-10-10 at 19:22 +0200, Florian Pflug wrote: I still think we should strive for consistency here, so let's also make '[]' the default flags for the range constructors. For continuous ranges I don't think that's a good idea. Closed-open is a very widely-accepted convention and there

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Jeff Davis
On Mon, 2011-10-10 at 18:39 +0100, Thom Brown wrote: So the default boundaries should be '[]' as opposed to '[)' as it is now. Would that vary between range types? In other words, do I bring back default_flags? If not, I think a lot of people will object. The most common use-case for range

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 1:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*).  Is this something we can do for PG 9.2?  Is anyone working on this? Well, it's not

Re: [HACKERS] unite recovery.conf and postgresql.conf

2011-10-10 Thread Josh Berkus
Simon, Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing all downstream tools to make major changes in the ways they supply parameters. Actually, this case is easily solved by an include

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Thom Brown
On 10 October 2011 18:45, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-10 at 18:39 +0100, Thom Brown wrote:  So the default boundaries should be '[]' as opposed to '[)' as it is now. Would that vary between range types? In other words, do I bring back default_flags? If not, I think

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Thom Brown
On 10 October 2011 18:53, Thom Brown t...@linux.com wrote: On 10 October 2011 18:45, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-10 at 18:39 +0100, Thom Brown wrote:  So the default boundaries should be '[]' as opposed to '[)' as it is now. Would that vary between range types? In

Re: [HACKERS] ALTER EXTENSION .. ADD/DROP weirdness

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 12:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: rhaas=# alter extension pg_stat_statements drop type pg_stat_statements[]; ERROR:  syntax error at or near [ LINE 1: ...extension pg_stat_statements drop type pg_stat_statements[];  

Re: [HACKERS] [v9.2] DROP statement reworks

2011-10-10 Thread Kohei KaiGai
OK, well, I applied pgsql-v9.2-drop-reworks-2.v4.1.patch and tried to compile, and got this: In file included from ../../../src/include/catalog/dependency.h:17,                 from dependency.c:19: ../../../src/include/catalog/objectaddress.h:21: warning: type defaults to ‘int’ in

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Tom Lane
Gurjeet Singh singh.gurj...@gmail.com writes: On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Well, it's not that it doesn't optimize COUNT(*)

Re: [HACKERS] ALTER EXTENSION .. ADD/DROP weirdness

2011-10-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: But there's a bigger problem: it seems to me that we have an inconsistency between what happens when you create an extension from scratch and when you upgrade it from unpackaged. Both pg_buffercache and pg_stat_statements just do this in the upgrade

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. No kidding. But the point is that if the developer down the hall maliciously destroys

[HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Greg Sabino Mullane
I'm looking into upgrading a fairly busy system to 9.1. They use serializable mode for a few certain things, and suffer through some serialization errors as a result. While looking over the new serializable/SSI documentation, one thing that stood out is:

Re: [HACKERS] ALTER EXTENSION .. ADD/DROP weirdness

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 2:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: But there's a bigger problem: it seems to me that we have an inconsistency between what happens when you create an extension from scratch and when you upgrade it from unpackaged.  Both

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Right now, our costing model for index-only scans is pretty dumb. It assumes that using an index-only scan will avoid 10% of the heap fetches. That could easily be low, and on an insert-only table or one where only the recently-updated rows are

table/index options | was: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Cédric Villemain
2011/10/10 Robert Haas robertmh...@gmail.com: On Mon, Oct 10, 2011 at 1:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: That gives you an index-only scan; but without the WHERE clause it uses a seq scan.  I think it's mainly a matter of doing enough benchmarks to figure out how best

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: I think what Robert is complaining about is that we won't currently consider an index that matches neither any WHERE clauses nor ORDER BY, ie, count(*) over the whole table won't get considered for an index-only scan, regardless of cost estimates. I guess

Re: [HACKERS] Bug in walsender when calling out to do_pg_stop_backup (and others?)

2011-10-10 Thread Magnus Hagander
On Thu, Oct 6, 2011 at 23:46, Florian Pflug f...@phlo.org wrote: On Oct6, 2011, at 21:48 , Magnus Hagander wrote: The question is, should we do more? To me, it'd make sense to terminate a backend once it's connection is gone. We could, for example, make pq_flush() set a global flag, and make

Re: [HACKERS] Should we get rid of custom_variable_classes altogether?

2011-10-10 Thread Alex Shulgin
On Mon, Oct 3, 2011 at 00:05, Tom Lane t...@sss.pgh.pa.us wrote: So at this point I'd vote for just dropping it and always allowing custom (that is, qualified) GUC names to be set, whether the prefix corresponds to any loaded module or not. Comments, other proposals? While working on E.164

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Heikki Linnakangas
On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Dan Ports
On Mon, Oct 10, 2011 at 02:25:59PM -0400, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Kevin Grittner
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I think what Robert is complaining about is that we won't currently consider an index that matches neither any WHERE clauses nor ORDER BY, ie, count(*) over the whole table won't get considered for an

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-10-10 Thread Kohei KaiGai
2011/10/10 Robert Haas robertmh...@gmail.com: On Sun, Oct 9, 2011 at 11:50 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I tried to refactor the patches based on the interface of WITH (...) and usage of pg_class.reloptions, although here is no functionality changes; including the behavior when

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Gurjeet Singh
On Mon, Oct 10, 2011 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. No

Re: [HACKERS] unite recovery.conf and postgresql.conf

2011-10-10 Thread Josh Berkus
On 10/10/11 10:52 AM, Josh Berkus wrote: So after debugging some of our failover scripts, here's the real-world problems I'm trying to solve. These design flaws are issues which cause automated failover or failback to abort, leading to unexpected downtime, so they are not just issues of

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Dan Ports
On Mon, Oct 10, 2011 at 02:59:04PM -0500, Kevin Grittner wrote: I do have some concern about whether the performance improvements from reduced LW locking contention elsewhere in the code may (in whack-a-mole fashion) cause the percentages to go higher in SSI. The biggest performance issues in

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Kevin Grittner
Dan Ports d...@csail.mit.edu wrote: I spent some time thinking about this a while back, but didn't have time to get very far. The problem isn't contention in the predicate lock manager (which is partitioned) but the single lock protecting the active SerializableXact state. It would

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Joe Conway
On 10/10/2011 01:52 PM, Gurjeet Singh wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. Perhaps, but I think our long term goal at least should be to

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Kevin Grittner
Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user

Re: [HACKERS] unite recovery.conf and postgresql.conf

2011-10-10 Thread Simon Riggs
On Mon, Oct 10, 2011 at 6:52 PM, Josh Berkus j...@agliodbs.com wrote: Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing all downstream tools to make major changes in the ways they supply

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Dan Ports
On Mon, Oct 10, 2011 at 04:10:18PM -0500, Kevin Grittner wrote: Did you ever see much contention on SerializablePredicateLockListLock, or was it just SerializableXactHashLock? I think the former might be able to use the non-blocking techniques, but I fear the main issue is with the latter,

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Simon Riggs
On Mon, Oct 10, 2011 at 8:30 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of

Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation

2011-10-10 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: How do we turn it on/off to allow the overhead to be measured? User REPEATABLE READ transactions or SERIALIZABLE transactions. The easiest way, if you're doing it for all transactions (which I recommend) is to set default_transaction_isolation.

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-10-10 Thread Noah Misch
On Sun, Oct 09, 2011 at 05:50:52PM +0200, Kohei KaiGai wrote: [patch v4] Each revision of this patch yielded a 1.2 MiB email. Please gzip attachments this large. The two revisions you sent in September constituted 18% of the pgsql-hackers bits for the month, and the next-largest message was

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-10-10 Thread Peter Geoghegan
On 10 October 2011 21:28, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/10/10 Robert Haas robertmh...@gmail.com: It seems really ugly to me to suppose that we need to add a depth field to every single one of these node types.  If you've missed one, then we have a security hole.  If someone else

[HACKERS] Dumping roles improvements?

2011-10-10 Thread Josh Berkus
It occurs to me that we could really use two things to make it easier to move copies of database stuff around: pg_dump -r, which would include a CREATE ROLE for all roles needed to restore the database (probably without passwords), and pg_dumpall -r --no-passwords which would dump the roles but

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 4:28 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I agreed. We have been on the standpoint that tries to prevent leakable functions to reference a portion of join-tree being already flatten, however, it has been a tough work. It seems to me it is much simple approach

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Florian Pflug
On Oct10, 2011, at 20:06 , Thom Brown wrote: Okay, a real example of why discrete should be '[]' and continuous should be '[)'. If you book a meeting from 09:00 to 11:00 (tsrange), at 11:00 precisely it either becomes free or is available to someone else, so it can be booked 11:00 to 12:00

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Florian Pflug
On Oct10, 2011, at 19:41 , Jeff Davis wrote: On Mon, 2011-10-10 at 19:22 +0200, Florian Pflug wrote: I still think we should strive for consistency here, so let's also make '[]' the default flags for the range constructors. For continuous ranges I don't think that's a good idea. Closed-open

Re: [HACKERS] Bug in walsender when calling out to do_pg_stop_backup (and others?)

2011-10-10 Thread Florian Pflug
On Oct10, 2011, at 21:25 , Magnus Hagander wrote: On Thu, Oct 6, 2011 at 23:46, Florian Pflug f...@phlo.org wrote: It'd be nice to generally terminate a backend if the client vanishes, but so far I haven't had any bright ideas. Using FASYNC and F_SETOWN unfortunately sends a signal *everytime*

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Jeff Janes
On Mon, Oct 10, 2011 at 10:36 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*).  Is this something we can do for PG 9.2?  Is anyone working on this? Well, it's not

Re: [HACKERS] Dumping roles improvements?

2011-10-10 Thread Pavel Stehule
Hello 2011/10/11 Josh Berkus j...@agliodbs.com: It occurs to me that we could really use two things to make it easier to move copies of database stuff around: pg_dump -r, which would include a CREATE ROLE for all roles needed to restore the database (probably without passwords), and

Re: [HACKERS] index-only scans

2011-10-10 Thread Tom Lane
I wrote: I have mostly-working code for approach #3, but I haven't tried to make EXPLAIN work yet. While looking at that I realized that there's a pretty good argument for adding the above-mentioned explicit TargetEntry list representing the index columns to index-only plan nodes. Namely,

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Greg Stark
On Mon, Oct 10, 2011 at 9:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: My intention was to allow it to consider any covering index.  You're thinking about the cost estimate, which is really entirely different. Is there any reason to consider more than one? I would have expected the narrowest one

Re: [HACKERS] COUNT(*) and index-only scans

2011-10-10 Thread Kevin Grittner
Jeff Janes wrote: Kevin Grittner wrote: create table t (id int not null primary key); insert into t select generate_series(1, 100); vacuum freeze analyze; explain analyze select count(*) from t where id between 50 and 500010; That gives you an index-only scan; but without the

Re: [HACKERS] unite recovery.conf and postgresql.conf

2011-10-10 Thread Fujii Masao
On Tue, Oct 11, 2011 at 6:37 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Oct 10, 2011 at 6:52 PM, Josh Berkus j...@agliodbs.com wrote: Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-10 Thread Jeff Davis
On Tue, 2011-10-11 at 03:14 +0200, Florian Pflug wrote: Maybe ranges over discrete types are slightly more likely to be closed, and ranges over continuous types slightly more likely to be open. Still, I very much doubt that the skew in the distribution is large enough to warrant the confusion