Re: [HACKERS] dsm use of uint64

2013-11-01 Thread Peter Eisentraut
On Mon, 2013-10-28 at 12:17 -0400, Robert Haas wrote: > On Sun, Oct 27, 2013 at 11:34 PM, Noah Misch wrote: > > On Fri, Oct 25, 2013 at 10:11:41PM -0400, Robert Haas wrote: > >> When I wrote the dynamic shared memory patch, I used uint64 everywhere > >> to measure sizes - rather than, as we do for

Re: [HACKERS] Feature request: Optimizer improvement

2013-11-01 Thread David Johnston
Jim Nasby-2 wrote > Should that really matter in this case? ISTM we should always handle LIMIT > before moving on to the SELECT clause…? SELECT generate_series(1,10) LIMIT 1 David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Feature-request-Optimizer-improvement

Re: [HACKERS] appendPQExpBufferVA vs appendStringInfoVA

2013-11-01 Thread Robert Haas
On Fri, Nov 1, 2013 at 9:27 PM, Tom Lane wrote: > David Rowley writes: >> Tom commited some changes to appendStringInfoVA a few weeks ago which >> allows it to return the required buffer size if the current buffer is not >> big enough. > >> On looking at appendPQExpBufferVA I'm thinking it would

Re: [HACKERS] appendPQExpBufferVA vs appendStringInfoVA

2013-11-01 Thread Tom Lane
David Rowley writes: > Tom commited some changes to appendStringInfoVA a few weeks ago which > allows it to return the required buffer size if the current buffer is not > big enough. > On looking at appendPQExpBufferVA I'm thinking it would be nice if it could > make use of the new pvsnprintf fun

[HACKERS] appendPQExpBufferVA vs appendStringInfoVA

2013-11-01 Thread David Rowley
Tom commited some changes to appendStringInfoVA a few weeks ago which allows it to return the required buffer size if the current buffer is not big enough. On looking at appendPQExpBufferVA I'm thinking it would be nice if it could make use of the new pvsnprintf function to bring the same potentia

Re: [HACKERS] [BUGS] BUG #8573: int4range memory consumption

2013-11-01 Thread Tom Lane
I wrote: > It's possible that this would result in some net slowdown in tuple output; > but it's also possible that eliminating the retail pfree's in favor of a > single context reset per tuple would make for a net savings. In any case, > we're already using a reset-per-row approach to memory mana

Re: [HACKERS] Feature request: Optimizer improvement

2013-11-01 Thread Atri Sharma
On Friday, November 1, 2013, Jim Nasby wrote: > On Oct 31, 2013, at 2:57 PM, Kevin Grittner > > > wrote: > > Joe Love 'j...@primoweb.com');>> wrote: > > In postgres 9.2 I have a function that is relatively expensive. > > > What did you specify in the COST clause on the CREATE FUNCTION > statemen

Re: Handle LIMIT/OFFSET before select clause (was: [HACKERS] Feature request: optimizer improvement)

2013-11-01 Thread Tom Lane
Jim Nasby writes: > On Oct 31, 2013, at 11:04 AM, Joe Love wrote: >> In postgres 9.2 I have a function that is relatively expensive. When I >> write a query such as: >> >> select expensive_function(o.id),o.* from offeirng o where valid='Y' order by >> name limit 1; > Does anyone know what th

Re: [HACKERS] Feature request: Optimizer improvement

2013-11-01 Thread Jim Nasby
On Oct 31, 2013, at 2:57 PM, Kevin Grittner wrote: > Joe Love wrote: > >> In postgres 9.2 I have a function that is relatively expensive. > > What did you specify in the COST clause on the CREATE FUNCTION > statement? Should that really matter in this case? ISTM we should always handle LIMIT

Re: [HACKERS] [BUGS] BUG #8573: int4range memory consumption

2013-11-01 Thread Jim Nasby
On Nov 1, 2013, at 2:08 PM, Tom Lane wrote: > g.vanluffe...@qipc.com writes: >> int4range ( and any other range function) consumes much memory when used in >> a select statement on a big table. > > The problem is that range_out leaks memory, as a consequence of creating a > number of intermediate

Handle LIMIT/OFFSET before select clause (was: [HACKERS] Feature request: optimizer improvement)

2013-11-01 Thread Jim Nasby
On Oct 31, 2013, at 11:04 AM, Joe Love wrote: > In postgres 9.2 I have a function that is relatively expensive. When I write > a query such as: > > select expensive_function(o.id),o.* from offeirng o where valid='Y' order by > name limit 1; > > the query runs slow and appears to be running th

Re: [HACKERS] buffile.c resource owner breakage on segment extension

2013-11-01 Thread Tom Lane
Andres Freund writes: > While not particularly nice, given the API, it seems best for buffile.c > to remember the resource owner used for the original segment and > temporarily set that during the extension. Hm, yeah, that seems right. It's just like repalloc keeping the memory chunk in its orig

Re: [HACKERS] API bug in DetermineTimeZoneOffset()

2013-11-01 Thread Robert Haas
On Fri, Nov 1, 2013 at 10:50 AM, Tom Lane wrote: > I wrote: >> The second attached patch, to be applied after the first, removes the >> existing checks of HasCTZSet in the backend. The only visible effect of >> this, AFAICT, is that to_char's TZ format spec now delivers something >> useful instea

Re: [HACKERS] [BUGS] BUG #8573: int4range memory consumption

2013-11-01 Thread Tom Lane
g.vanluffe...@qipc.com writes: > int4range ( and any other range function) consumes much memory when used in > a select statement on a big table. The problem is that range_out leaks memory, as a consequence of creating a number of intermediate strings that it doesn't bother to free. I don't belie

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Robert Haas
On Fri, Nov 1, 2013 at 9:49 AM, Tom Lane wrote: > The bigger picture here is that such an approach amounts to deciding that > no one will ever be allowed to fix hash indexes. I'm not for that, even > if I'm not volunteering to be the fixer myself. Yeah. I have thought about doing some work on t

Re: [HACKERS] missing RelationCloseSmgr in FreeFakeRelcacheEntry?

2013-11-01 Thread Andres Freund
Hi Heikki, All, On 2013-10-29 02:16:23 +0100, Andres Freund wrote: > Looking a bit closer it seems to me that the fake relcache > infrastructure seems to neglect the chance that something used the fake > entry to read something which will have done a RelationOpenSmgr(). Which > in turn will have s

[HACKERS] buffile.c resource owner breakage on segment extension

2013-11-01 Thread Andres Freund
Hi, The attached testcase demonstrates that it currently is possible that buffile.c segments get created belonging to the wrong resource owner leading to WARNINGs ala "temporary file leak: File %d still referenced", ERRORs like "write failed", asserts and segfaults. The problem is that while BufF

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Hannu Krosing
On 11/01/2013 03:49 PM, Andres Freund wrote: > On 2013-11-01 09:49:57 -0400, Tom Lane wrote: >> Lastly: what real-world problem are we solving by kicking that code >> to the curb? > It makes hashed lookups much easier to use. Currently if you want > indexed access over wide columns and equality is

Re: [HACKERS] PostgreSQL 9.3 beta breaks some extensions "make install"

2013-11-01 Thread Marti Raudsepp
Hi Andrew, On Mon, Sep 23, 2013 at 6:43 PM, Andrew Dunstan wrote: > I'm working on it. It appears to have a slight problem or two I want to fix > at the same time, rather than backpatch something broken. Any progress on this? I notice that the fixes didn't make it into 9.3.1. Regards, Marti -

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Daniel Farina
On Fri, Nov 1, 2013 at 8:52 AM, Daniel Farina wrote: > On Fri, Nov 1, 2013 at 6:31 AM, Dimitri Fontaine > wrote: >> Also, talking with Peter Geoghegan, it's unclear that there's a use case >> where a hash index would be faster than a btree index over the hash >> function. >> >> Comments? > > I h

Re: [HACKERS] Something fishy happening on frogmouth

2013-11-01 Thread Noah Misch
On Fri, Nov 01, 2013 at 12:27:31AM -0400, Robert Haas wrote: > On Thu, Oct 31, 2013 at 7:48 PM, Heikki Linnakangas > wrote: > > On 31.10.2013 16:43, Robert Haas wrote: > >> There should be no cases where the main shared memory > >> segment gets cleaned up and the dynamic shared memory segments do

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Daniel Farina
On Fri, Nov 1, 2013 at 6:31 AM, Dimitri Fontaine wrote: > Hi, > > Here's an idea: when a user ask for an Hash Index transparently build a > BTree index over an hash function instead. > > Advantages: > > - it works > - it's crash safe > - it's (much?) faster than a hash index anyways > > Draw

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Jeff Janes
On Fri, Nov 1, 2013 at 6:31 AM, Dimitri Fontaine wrote: > Hi, > > Here's an idea: when a user ask for an Hash Index transparently build a > BTree index over an hash function instead. > Could something be added to the planner so that you can just build a btree index on a hash expression, and have

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Atri Sharma
On Friday, November 1, 2013, k...@rice.edu wrote: > On Fri, Nov 01, 2013 at 01:31:10PM +, Dimitri Fontaine wrote: > > Hi, > > > > Here's an idea: when a user ask for an Hash Index transparently build a > > BTree index over an hash function instead. > > > > Advantages: > > > > - it works > >

Re: [HACKERS] API bug in DetermineTimeZoneOffset()

2013-11-01 Thread Tom Lane
I wrote: > The second attached patch, to be applied after the first, removes the > existing checks of HasCTZSet in the backend. The only visible effect of > this, AFAICT, is that to_char's TZ format spec now delivers something > useful instead of an empty string when a brute-force timezone is in u

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Andres Freund
On 2013-11-01 09:49:57 -0400, Tom Lane wrote: > Lastly: what real-world problem are we solving by kicking that code > to the curb? It makes hashed lookups much easier to use. Currently if you want indexed access over wide columns and equality is all you need you need to write rather awkward querie

Re: [HACKERS] [GENERAL] Cannot create matview when referencing another not-populated-yet matview in subquery

2013-11-01 Thread Kevin Grittner
Laurent Sartran wrote: > CREATE MATERIALIZED VIEW t1 AS SELECT text 'foo' AS col1 >   WITH NO DATA; > CREATE MATERIALIZED VIEW t2b AS SELECT * FROM t1 >   WHERE col1 = (SELECT LEAST(col1) FROM t1) >   WITH NO DATA; > > ERROR:  materialized view "t1" has not been populated > HINT:  Use the REFRESH

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Tom Lane
Andrew Dunstan writes: > Yeah, and there's this: I've had at least one client who switched to > using hash indexes and got a significant benefit from it precisely > because they aren't WAL logged. They could afford to rebuild the indexes > in the unlikely event of a crash, but the IO gain was w

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Andrew Dunstan
On 11/01/2013 09:49 AM, Tom Lane wrote: Dimitri Fontaine writes: Here's an idea: when a user ask for an Hash Index transparently build a BTree index over an hash function instead. -1. If someone asks for a hash index, they should get a hash index. If you feel the documentation isn't sufficie

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Dimitri Fontaine
Tom Lane writes: > -1. If someone asks for a hash index, they should get a hash index. > If you feel the documentation isn't sufficiently clear about the problems > involved, we can work on that. Fair enough. > Lastly: what real-world problem are we solving by kicking that code > to the curb?

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread Tom Lane
Dimitri Fontaine writes: > Here's an idea: when a user ask for an Hash Index transparently build a > BTree index over an hash function instead. -1. If someone asks for a hash index, they should get a hash index. If you feel the documentation isn't sufficiently clear about the problems involved,

Re: [HACKERS] Save Hash Indexes

2013-11-01 Thread k...@rice.edu
On Fri, Nov 01, 2013 at 01:31:10PM +, Dimitri Fontaine wrote: > Hi, > > Here's an idea: when a user ask for an Hash Index transparently build a > BTree index over an hash function instead. > > Advantages: > > - it works > - it's crash safe > - it's (much?) faster than a hash index anyw

[HACKERS] Save Hash Indexes

2013-11-01 Thread Dimitri Fontaine
Hi, Here's an idea: when a user ask for an Hash Index transparently build a BTree index over an hash function instead. Advantages: - it works - it's crash safe - it's (much?) faster than a hash index anyways Drawbacks: - root access concurrency - we need a hash_any function stable ag

Re: [HACKERS] Shave a few instructions from child-process startup sequence

2013-11-01 Thread Gurjeet Singh
On Thu, Oct 31, 2013 at 11:20 PM, Tom Lane wrote: > Amit Kapila writes: > > On Thu, Oct 31, 2013 at 2:41 AM, Gurjeet Singh wrote: > >> Just a small patch; hopefully useful. > > > This is valid saving as we are filling array ListenSocket[] in > > StreamServerPort() serially, so during ClosePostm

Re: [HACKERS] Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan

2013-11-01 Thread Etsuro Fujita
> From: Fujii Masao [mailto:masao.fu...@gmail.com] > This is what I'm looking for! This feature is really useful for tuning work_mem > when using full text search with pg_trgm. > > I'm not sure if it's good idea to show the number of the fetches because it > seems difficult to tune work_mem from

[HACKERS] [PATCH] pg_receivexlog: fixed to work with logical segno > 0

2013-11-01 Thread Mika Eloranta
pg_receivexlog calculated the xlog segment number incorrectly when started after the previous instance was interrupted. Resuming streaming only worked when the physical wal segment counter was zero, i.e. for the first 256 segments or so. --- src/bin/pg_basebackup/pg_receivexlog.c | 2 +- 1 file c

Re: [HACKERS] How should row-security affects ON UPDATE RESTRICT / CASCADE ?

2013-11-01 Thread Craig Ringer
On 10/30/2013 11:25 AM, Kohei KaiGai wrote: > + > + /* > +* Row-level security should be disabled in case when foreign-key > +* relation is queried to check existence of tuples that references > +* the primary-key being modified. > +*/ > + temp_sec_context = save_sec_context |

Re: [HACKERS] [BUGS] BUG #8542: Materialized View with another column_name does not work?

2013-11-01 Thread Kevin Grittner
Ashutosh Bapat wrote: > CREATE MATERIALIZED VIEW statement ends up being CREATE TABLE AS > statement underneath with table type matview. In that case, why > don't I see special treatment only for materialized view and not > CTAS in general, which allows column names to specified like the > case i

Re: [HACKERS] [BUGS] BUG #8542: Materialized View with another column_name does not work?

2013-11-01 Thread Kevin Grittner
Michael Paquier wrote: > I am not sure that adding a boolean flag introducing a concept > related to matview inside checkRuleResultList is the best > approach to solve that. checkRuleResultList is something related > only to rules, and has nothing related to matviews in it yet. Well, I was tempt