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

2011-10-16 Thread Kohei KaiGai
Hi Robert, I'm a bit confusing about this sentence. If you can make this work, I think it could be a pretty sweet plannner optimization even apart from the implications for security views. Consider a query of this form: A LEFT JOIN B LEFT JOIN C where B is a view defined as: B1 JOIN B2

Re: [HACKERS] Pushing ScalarArrayOpExpr support into the btree index AM

2011-10-16 Thread Tom Lane
Noah Misch n...@leadboat.com writes: On Sat, Oct 15, 2011 at 02:58:45PM -0400, Tom Lane wrote: [algorithm for a regular index scan satisfying key IN (...)] Sounds sensible. The algorithm applies to more than ScalarArrayOpExpr; is it not the ability to handle an OR'ed list of ScanKey instead

[HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Jan Urbański
Hi, this idea has cropped up last PGCon - the ability to set GUC variables for the duration of a single query. It would work by setting the GUCs for the duration of the query and setting them back to what they were after it has terminated. By setting them back I mean respecting the previously set

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: this idea has cropped up last PGCon - the ability to set GUC variables for the duration of a single query. It would work by setting the GUCs for the duration of the query and setting them back to what they were after it has

Re: [HACKERS] LIMITing number of results in a VIEW with global variables

2011-10-16 Thread Florian Pflug
On Oct15, 2011, at 14:52 , Thomas Girault wrote: Alternatively, we could also set the alpha value before the query : SELECT set_alpha(0.1); SELECT age, young(age) FROM employees WHERE young(age); That's certainly much safer. I would be very interested to know if there is smarter way to set

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Thom Brown
On 16 October 2011 16:44, Jan Urbański wulc...@wulczer.org wrote: Hi, this idea has cropped up last PGCon - the ability to set GUC variables for the duration of a single query. It would work by setting the GUCs for the duration of the query and setting them back to what they were after it

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Jan Urbański
On 16/10/11 17:49, Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: this idea has cropped up last PGCon - the ability to set GUC variables for the duration of a single query. It would work by setting the GUCs for the duration of the query and setting them back to

Re: [HACKERS] Pushing ScalarArrayOpExpr support into the btree index AM

2011-10-16 Thread Tom Lane
Florian Pflug f...@phlo.org writes: On Oct15, 2011, at 20:58 , Tom Lane wrote: So, at least as far as btrees are concerned, it seems like I implemented the ScalarArrayOpExpr logic at the wrong level and it ought to be pushed down into the index AM. The above rules seem pretty btree-specific,

[HACKERS] (patch) regression diffs on collate.linux.utf8 test

2011-10-16 Thread Jeff Davis
On master, I see a minor test error (at least on my machine) as well as a diff. Patch attached. Regards, Jeff Davis *** a/src/test/regress/expected/collate.linux.utf8.out --- b/src/test/regress/expected/collate.linux.utf8.out *** *** 395,401 SELECT relname FROM pg_class

Re: [HACKERS] patch for new feature: Buffer Cache Hibernation

2011-10-16 Thread Greg Stark
On Fri, Oct 14, 2011 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right.  I think this one falls into my class #2, ie, we have no idea how to implement it usefully.  Doesn't (necessarily) mean that the core concept is without merit. Hm. given that we have an implementation I wouldn't say we

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Greg Stark
On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could hack around this by adding more columns to the result so that an index-only scan doesn't work.  But I wonder whether it wouldn't be smarter to add ORDER BY clauses to the window function calls.  I've been known to

Re: [HACKERS] Pushing ScalarArrayOpExpr support into the btree index AM

2011-10-16 Thread Florian Pflug
On Oct16, 2011, at 19:09 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Oct15, 2011, at 20:58 , Tom Lane wrote: So, at least as far as btrees are concerned, it seems like I implemented the ScalarArrayOpExpr logic at the wrong level and it ought to be pushed down into the index AM.

Re: [HACKERS] patch for new feature: Buffer Cache Hibernation

2011-10-16 Thread Tom Lane
Greg Stark st...@mit.edu writes: On Fri, Oct 14, 2011 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right.  I think this one falls into my class #2, ie, we have no idea how to implement it usefully.  Doesn't (necessarily) mean that the core concept is without merit. Hm. given that we have

Re: [HACKERS] Pushing ScalarArrayOpExpr support into the btree index AM

2011-10-16 Thread Tom Lane
Florian Pflug f...@phlo.org writes: On Oct16, 2011, at 19:09 , Tom Lane wrote: That doesn't seem like the same thing at all, because the indexed column is on different sides of the expression in the two cases. The situation that I'm worried about is indexedcolumn op ANY(arrayconstant), and

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Florian Pflug
On Oct16, 2011, at 20:04 , Greg Stark wrote: On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could hack around this by adding more columns to the result so that an index-only scan doesn't work. But I wonder whether it wouldn't be smarter to add ORDER BY clauses to the

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Dimitri Fontaine
Jan Urbański wulc...@wulczer.org writes: this idea has cropped up last PGCon - the ability to set GUC variables for the duration of a single query. It would work by setting the GUCs for the duration of the query and setting them back to what they were after it has terminated. By setting them

Re: [HACKERS] (patch) regression diffs on collate.linux.utf8 test

2011-10-16 Thread Tom Lane
Jeff Davis pg...@j-davis.com writes: On master, I see a minor test error (at least on my machine) as well as a diff. Patch attached. Hmm, yeah, I forgot to fix this regression test when I added that DETAIL line. However, I don't see the need for fooling with the lc_time value?

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Tom Lane
Dimitri Fontaine dimi...@2ndquadrant.fr writes: I think it would fit quite well within our extending of the WITH syntax. WITH work_mem = '1GB', timezone = 'Europe/Amsterdam', foo AS ( SELECT something ) SELECT toplevel FROM foo; That looks pretty non-future-proof to me. WITH

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes: That looks pretty non-future-proof to me. WITH is a SQL-standard syntax, it's not an extension that we control. Now that you mention it, the following might actually already work: WITH settings AS ( SELECT set_config('timezone', 'Europe/Amsterdam', t),

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Tom Lane
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Now that you mention it, the following might actually already work: WITH settings AS ( SELECT set_config('timezone', 'Europe/Amsterdam', t), set_config('work_mem', '1 GB', t) ), foo AS ( SELECT … ) INSERT INTO

Re: GiST for range types (was Re: [HACKERS] Range Types - typo + NULL string constructor)

2011-10-16 Thread Jeff Davis
On Fri, 2011-10-07 at 12:54 +0400, Alexander Korotkov wrote: The first thing caught my eye in existing GiST code is idea of subtype_float. float8 has limited precision and can't respresent, for example, varlena values good enough. Even if we have large int8 value we can loose lower bits, but

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Tom Lane
Hitoshi Harada umi.tan...@gmail.com writes: 2011/10/15 Tom Lane t...@sss.pgh.pa.us: I can't recall whether there was some good reason for underspecifying these test queries.  It looks like all the problematic ones were added in commit ec4be2ee6827b6bd85e0813c7a8993cfbb0e6fa7 Extend the set of

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Tom Lane
Florian Pflug f...@phlo.org writes: But some frame clauses (row 1 preceding, for example) have an effect despite there being no ORDER BY, like here: Yeah, why did you expect differently? Without ORDER BY, all rows are peers in the frame ordering, so there's no way for a RANGE spec to select

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Florian Pflug
On Oct17, 2011, at 00:14 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: But some frame clauses (row 1 preceding, for example) have an effect despite there being no ORDER BY, like here: Yeah, why did you expect differently? Without ORDER BY, all rows are peers in the frame ordering,

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Tom Lane
Florian Pflug f...@phlo.org writes: ... reading those parts again, I realize the it says When ORDER BY is omitted the *default* frame consists ... , and that the second quote is followed by a footnote which says There are options to define the window frame in other ways, but this tutorial

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Hitoshi Harada
2011/10/17 Tom Lane t...@sss.pgh.pa.us: Hitoshi Harada umi.tan...@gmail.com writes: 2011/10/15 Tom Lane t...@sss.pgh.pa.us: I can't recall whether there was some good reason for underspecifying these test queries.  It looks like all the problematic ones were added in commit

Re: [HACKERS] Adding CORRESPONDING to Set Operations

2011-10-16 Thread Kerem Kat
CORRESPONDING clause take 2 After realizing that modifying prepunion.c to include a custom subquery is not easy(incomprehensible to me) as it sounds and turning into a hassle after making several uninformed changes, I decided to go with modifying analyze.c. The incomprehensible part is

Re: [HACKERS] Underspecified window queries in regression tests

2011-10-16 Thread Hitoshi Harada
2011/10/17 Greg Stark st...@mit.edu: On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could hack around this by adding more columns to the result so that an index-only scan doesn't work.  But I wonder whether it wouldn't be smarter to add ORDER BY clauses to the window

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Robert Haas
On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dimitri Fontaine dimi...@2ndquadrant.fr writes: Now that you mention it, the following might actually already work:  WITH settings AS (    SELECT set_config('timezone', 'Europe/Amsterdam', t),          

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I previously floated the idea of using a new keyword, possibly LET, for this, like this: LET var = value [, ...] IN query I'm not sure if anyone bought it, but I'll run it up the flagpole again and see if anyone salutes. I tend to agree with the

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Andrew Dunstan
On 10/16/2011 08:59 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: I previously floated the idea of using a new keyword, possibly LET, for this, like this: LET var = value [, ...] IN query I'm not sure if anyone bought it, but I'll run it up the flagpole again and see if anyone

Re: [HACKERS] proposal: set GUC variables for single query

2011-10-16 Thread Robert Haas
On Sun, Oct 16, 2011 at 8:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I previously floated the idea of using a new keyword, possibly LET, for this, like this: LET var = value [, ...] IN query I'm not sure if anyone bought it, but I'll run it up the

Re: [HACKERS] pg_comments (was: Allow \dd to show constraint comments)

2011-10-16 Thread Robert Haas
On Fri, Oct 14, 2011 at 11:12 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Oct 12, 2011 at 10:20 PM, Josh Kupershmidt schmi...@gmail.com wrote: On the third hand, Josh's previous batch of changes to clean up psql's behavior in this area are clearly a huge improvement: you can now