Re: [HACKERS] Query regarding permission on table_column%type access

Stephen Frost writes: > * Neha Sharma (neha.sha...@enterprisedb.com) wrote: >> I have observed that even if the user does not have permission on a >> table(created in by some other user),the function parameter still can have >> a parameter of that table_column%type. > This is because the creation

Re: [HACKERS] Query regarding permission on table_column%type access

Greetings, * Neha Sharma (neha.sha...@enterprisedb.com) wrote: > I have observed that even if the user does not have permission on a > table(created in by some other user),the function parameter still can have > a parameter of that table_column%type. This is because the creation of the table also

Re: [HACKERS] Query got Killed with CTE.

On 17 October 2017 at 21:18, Prabhat Sahu wrote: > Hi, > > While quering with CTE against PG HEAD , i found that query got killed > with this below error logs > -- Machine Configuration: (d1.xlarge) CUPs : 8 , RAM : 16GB , SIze : 640GB > > postgres=# with x as (select 5 c1 from generate_series(1

Re: [HACKERS] Query started showing wrong result after Ctrl+c

tushar writes: > On 10/12/2017 03:46 PM, Marko Tiikkaja wrote: >> The subquery: >>     select n from tv limit 1 >> could in theory return any row due to the lack of ORDER BY. What I'm >> guessing happened is that you're seeing a synchronized sequential scan >> in follow-up queries.  Add an ORDER

Re: [HACKERS] Query started showing wrong result after Ctrl+c

On 10/12/2017 03:46 PM, Marko Tiikkaja wrote: The subquery:     select n from tv limit 1 could in theory return any row due to the lack of ORDER BY. What I'm guessing happened is that you're seeing a synchronized sequential scan in follow-up queries.  Add an ORDER BY. Bang on . After adding

Re: [HACKERS] Query started showing wrong result after Ctrl+c

On Thu, Oct 12, 2017 at 12:03 PM, tushar wrote: > postgres=# SELECT * FROM ( SELECT n from tv where n= (select * from > (select n from tv limit 1) c)) as c ; > n > -- > 3713 > (1 row) > > This time , query is started showing wrong result. Is this an expected > behavior and if yes -

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On 2017-04-11 17:42:42 -0400, Tom Lane wrote: > Now, that old behavior matches what you got in the RangeFunction case: > > regression96=# select * from int4_tbl, cast(case when f1>0 then > generate_series(1,2) else null end as int); > f1 | int4 > -+-- >0 |

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Andres Freund writes: > On 2017-04-11 17:25:52 -0400, Tom Lane wrote: >> What was the previous behavior for such cases? If it was reasonably >> sane, we probably have to preserve it. If it was unpredictable or >> completely wacko, maybe we don't. > Previously we'd stash the result in a new tupl

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On 2017-04-11 17:25:52 -0400, Tom Lane wrote: > Andres Freund writes: > > Tom, do you have any opinion on the volatility stuff? > > What was the previous behavior for such cases? If it was reasonably > sane, we probably have to preserve it. If it was unpredictable or > completely wacko, maybe w

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Andres Freund writes: > Tom, do you have any opinion on the volatility stuff? What was the previous behavior for such cases? If it was reasonably sane, we probably have to preserve it. If it was unpredictable or completely wacko, maybe we don't. regards, tom lane --

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On 2017-01-30 18:54:50 -0500, Tom Lane wrote: > Andres Freund writes: > > Wonder if we there's an argument to be made for implementing this > > roughly similarly to split_pathtarget_at_srf - instead of injecting a > > ProjectSet node we'd add a FunctionScan node below a Result node. > > Yeah, poss

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On 2017-03-09 13:34:22 -0500, Robert Haas wrote: > On Mon, Jan 30, 2017 at 6:54 PM, Tom Lane wrote: > > Andres Freund writes: > >> Wonder if we there's an argument to be made for implementing this > >> roughly similarly to split_pathtarget_at_srf - instead of injecting a > >> ProjectSet node we'd

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > On Mon, Jan 30, 2017 at 6:54 PM, Tom Lane wrote: > >> If you don't want to make ExecInitExpr responsible, then the planner would > >> have to do something like split_pathtarget_at_srf anyway to decompose the > >> expressi

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Robert Haas writes: > On Mon, Jan 30, 2017 at 6:54 PM, Tom Lane wrote: >> If you don't want to make ExecInitExpr responsible, then the planner would >> have to do something like split_pathtarget_at_srf anyway to decompose the >> expressions, no matter which executor representation we use. > Did

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On Mon, Jan 30, 2017 at 6:54 PM, Tom Lane wrote: > Andres Freund writes: >> Wonder if we there's an argument to be made for implementing this >> roughly similarly to split_pathtarget_at_srf - instead of injecting a >> ProjectSet node we'd add a FunctionScan node below a Result node. > > Yeah, pos

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Andres Freund writes: > Wonder if we there's an argument to be made for implementing this > roughly similarly to split_pathtarget_at_srf - instead of injecting a > ProjectSet node we'd add a FunctionScan node below a Result node. Yeah, possibly. That would have the advantage of avoiding an ExecP

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On 2017-01-30 17:24:31 -0500, Tom Lane wrote: > Make it work like Agg and WindowFunc. To wit, dump the actually special > function calls (the set-returning functions) into a list that's internal > to the FunctionScan node, and then anything above those goes into scalar > expressions in the node's

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Andres Freund writes: > On 2017-01-30 16:55:56 -0500, Tom Lane wrote: >> No, but it allows whatever looks syntactically like a function, including >> casts. IIRC, we made func_expr work that way ages ago to deflect >> complaints that it wasn't very clear why some things-that-look-like- >> functio

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Hi, On 2017-01-30 16:55:56 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2017-01-27 17:58:04 +0530, Rushabh Lathia wrote: > >> SELECT * > >> FROM pg_constraint pc, > >> CAST(CASE WHEN pc.contype IN ('f','u','p') THEN generate_series(1, > >> array_upper(pc.conkey, 1)) ELSE NULL END AS int

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Andres Freund writes: > On 2017-01-27 17:58:04 +0530, Rushabh Lathia wrote: >> SELECT * >> FROM pg_constraint pc, >> CAST(CASE WHEN pc.contype IN ('f','u','p') THEN generate_series(1, >> array_upper(pc.conkey, 1)) ELSE NULL END AS int) AS position; >> >> Above query is failing with "set-valued f

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

Hi, On 2017-01-27 17:58:04 +0530, Rushabh Lathia wrote: > Consider the below test; > > CREATE TABLE tab ( a int primary key); > > SELECT * > FROM pg_constraint pc, > CAST(CASE WHEN pc.contype IN ('f','u','p') THEN generate_series(1, > array_upper(pc.conkey, 1)) ELSE NULL END AS int) AS position

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On Sat, Jan 28, 2017 at 3:43 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Jan 27, 2017 at 5:28 AM, Rushabh Lathia > wrote: > >> Consider the below test; >> >> CREATE TABLE tab ( a int primary key); >> >> SELECT * >> FROM pg_constraint pc, >> CAST(CASE WHEN pc.contype IN (

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On Fri, Jan 27, 2017 at 3:13 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > In any case the more idiomatic way of writing your query these days (since > 9.4 came out) is: > > SELECT * > FROM pg_constraint pc > LEFT JOIN LATERAL generate_series(1, case when contype in ('f','p','u') >

Re: [HACKERS] Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f)

On Fri, Jan 27, 2017 at 5:28 AM, Rushabh Lathia wrote: > Consider the below test; > > CREATE TABLE tab ( a int primary key); > > SELECT * > FROM pg_constraint pc, > CAST(CASE WHEN pc.contype IN ('f','u','p') THEN generate_series(1, > array_upper(pc.conkey, 1)) ELSE NULL END AS int) AS position;

Re: [HACKERS] Query regarding selectDumpableExtension()

amul sul writes: > Thanks for your explanation, I agree that this is not a single scenario > where we need special care, but still my question stands there, why do we > really need to dump built-in extension? It's not built-in. It's installed by default, yes, but it's also droppable.

Re: [HACKERS] Query regarding selectDumpableExtension()

On 31 Oct 2016 6:48 pm, "Tom Lane" wrote: > > amul sul writes: > > On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas wrote: > >> There's a comment in dumpExtension() that explains it. > > > Let me explain the case I'm trying to tackle. I have two old dump > > data, each of them have couple objects de

Re: [HACKERS] Query regarding selectDumpableExtension()

amul sul writes: > On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas wrote: >> There's a comment in dumpExtension() that explains it. > Let me explain the case I'm trying to tackle. I have two old dump > data, each of them have couple objects depend on plpgsql. I have > restored first dump and trying

Re: [HACKERS] Query regarding selectDumpableExtension()

On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas wrote: > On Thu, Oct 27, 2016 at 2:11 AM, amul sul wrote: >> selectDumpableExtension() function skip >> s dump of >> built-in extensions in case of binary-upgrade only, >> why not in normal >> dump >> ? >> Can't we assume those will already be install

Re: [HACKERS] Query regarding selectDumpableExtension()

On Thu, Oct 27, 2016 at 2:11 AM, amul sul wrote: > selectDumpableExtension() function skip > s dump of > built-in extensions in case of binary-upgrade only, > why not in normal > dump > ? > Can't we assume those will already be installed in the target database > at restore > ? There's a comment

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

On Tue, Oct 18, 2016 at 10:03:39AM -0400, Tom Lane wrote: > Heikki Linnakangas writes: > > On 10/18/2016 04:13 PM, Tom Lane wrote: > >> There's a smoking gun in the postmaster log: > >> 2016-10-18 09:10:34.547 EDT [18502] LOG: wrong key in cancel request for > >> process 18491 > > > Ok, I've re

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

On Tue, Oct 18, 2016 at 11:40 PM, Tom Lane wrote: > I wrote: >> The cleanest fix might be to change those various "long" variables >> to uint32. You'd have to think about how to handle the ntohl/htonl >> calls that are used on them, though. > > Or actually, no, you wouldn't have to think very har

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

I wrote: > The cleanest fix might be to change those various "long" variables > to uint32. You'd have to think about how to handle the ntohl/htonl > calls that are used on them, though. Or actually, no, you wouldn't have to think very hard. I was supposing that those calls were declared to traff

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

Heikki Linnakangas writes: > On 10/18/2016 04:13 PM, Tom Lane wrote: >> There's a smoking gun in the postmaster log: >> 2016-10-18 09:10:34.547 EDT [18502] LOG: wrong key in cancel request for >> process 18491 > Ok, I've reverted that commit for now. It clearly needs more thought, > because of

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

On 10/18/2016 04:13 PM, Tom Lane wrote: Magnus Hagander writes: On Tue, Oct 18, 2016 at 1:00 AM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: The test executes "select pg_sleep(10)" and tries to cancel it. In recent master builds, cancel seems to be ignored, and the statement lasts

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

Magnus Hagander writes: > On Tue, Oct 18, 2016 at 1:00 AM, Vladimir Sitnikov < > sitnikov.vladi...@gmail.com> wrote: >> The test executes "select pg_sleep(10)" and tries to cancel it. In recent >> master builds, cancel seems to be ignored, and the statement lasts for 10 >> seconds. > My guess is

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

>What platform does the postgres server run on? Ubuntu OS name: "linux", version: "3.19.0-66-generic", arch: "amd64", family: "unix" 2016-10-18 11:05 GMT+03:00 Magnus Hagander : > > > On Tue, Oct 18, 2016 at 1:00 AM, Vladimir Sitnikov < > sitnikov.vladi...@gmail.com> wrote: > >> Hi, >> >> In pgj

Re: [HACKERS] Query cancel seems to be broken in master since Oct 17

On Tue, Oct 18, 2016 at 1:00 AM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > Hi, > > In pgjdbc we have regular regression testing against "build from > master" PostgreSQL, and recent master builds fail for "statement cancel" > test. > > The PostgreSQL as of Mon Oct 17 00:09:39 UTC 20

Re: [HACKERS] Query Procedures

On Thu, Apr 21, 2016 at 8:26 AM, Andrea Adami wrote: > > Hello, i'm a developer from italy and i need to make a query to get the > list of stored procedures with its signature. > Basically I would like to get the same list pgAdmin shows under the node > functions of the database tree on left pane

Re: [HACKERS] query in PostgreSQL

On Fri, Aug 14, 2015 at 9:32 PM, Rahul Majumdar/TVM/TCS wrote: > Hello Everyone, > > I have a problem related to query in PostgreSQL.Some queries are not > running.Please help me to find out a solution. Hi! You've reached the Postgres hackers mailing list where Postgres internals are discussed.

Re: [HACKERS] query execution time faster with geqo on than off: bug?

> > Well, for starters you're looking at an estimation miss. The > exhaustive search found the 'cheaper' plan than what geqo came up > with, but that did not correlate to execution time. This is a common > and frustrating problem. Generally to try and avoid it it's good to > avoid things in tabl

Re: [HACKERS] query execution time faster with geqo on than off: bug?

On Sun, Jun 14, 2015 at 6:43 PM, David Kamholz wrote: > I've encountered a query with 11 joins whose execution time (i.e., the time > not taken up by planning) is significantly faster with geqo on rather than > off. This is surprising to me and seems like it might be a bug in the > planner, so I a

Re: [HACKERS] Query Rewrite with Postgres' materialized views

Thank you for your answers. I am very eager to contribute to Postgres, especially in the materialized views area. I have created a thread proposing to work on it as my Google Summer of Code pro

Re: [HACKERS] Query Rewrite with Postgres' materialized views

Tom Lane wrote: > Robert Haas writes: >> I'm not really sure what Josh was talking about in that answer. In >> terms of doing this automatically, I doubt that's likely to happen >> until we have a way to automatically update a materialized view when >> the underlying data changes --- and Kevin Gr

Re: [HACKERS] Query Rewrite with Postgres' materialized views

On Sat, Feb 21, 2015 at 1:04 PM, Tom Lane wrote: > Robert Haas writes: >> I'm not really sure what Josh was talking about in that answer. In >> terms of doing this automatically, I doubt that's likely to happen >> until we have a way to automatically update a materialized view when >> the underl

Re: [HACKERS] Query Rewrite with Postgres' materialized views

Robert Haas writes: > I'm not really sure what Josh was talking about in that answer. In > terms of doing this automatically, I doubt that's likely to happen > until we have a way to automatically update a materialized view when > the underlying data changes --- and Kevin Grittner has done a bunc

Re: [HACKERS] Query Rewrite with Postgres' materialized views

On Sun, Feb 15, 2015 at 10:05 PM, Eric Grinstein wrote: > Are there any plans for implementing query rewriting (i.e. allowing the > optimizer to substitute materialized views for queries) > in upcoming versions? I have recently seen this answer, saying that query > rewriting could be achieved usin

Re: [HACKERS] Query not using index for user defined type

On Mon, Apr 15, 2013 at 7:51 PM, Tom Lane wrote: > Rodrigo Barboza writes: > > I created a implic cast for mytype to bigint. > > So when I do the same query it does seq scan, because the column is > > transformed into bigint. > > Yeah. One reason why there's not an unsigned int type already is

Re: [HACKERS] Query not using index for user defined type

Rodrigo Barboza writes: > I created a implic cast for mytype to bigint. > So when I do the same query it does seq scan, because the column is > transformed into bigint. Yeah. One reason why there's not an unsigned int type already is that it seems impossible to shoehorn it into the numeric promo

Re: [HACKERS] Query not using index for user defined type

On Mon, Apr 15, 2013 at 5:33 PM, Tom Lane wrote: > Rodrigo Barboza writes: > > I created a type 'mytype' (an unsigned int) and created an operator class > > for index. > > Then I created a table with a column of my type and isnerted 1000 > entries. > > But no matter how many entries I have in th

Re: [HACKERS] Query not using index for user defined type

Rodrigo Barboza writes: > I created a type 'mytype' (an unsigned int) and created an operator class > for index. > Then I created a table with a column of my type and isnerted 1000 entries. > But no matter how many entries I have in the table, it never uses the > index. It always does a seq scan.

Re: [HACKERS] Query not using index for user defined type

On Mon, Apr 15, 2013 at 3:08 PM, Rodrigo Barboza wrote: > > Here is the explain analyze with 1000 entries: > > explain analyze select * from mytable where a > 120::mytype and a < > 530::mytype; > I'm not sure this is appropiate for -hackers, maybe should post on -general. Also provide scripts wit

Re: [HACKERS] Query to help in debugging

Bruce Momjian wrote: >> Why are you insisting on cramming version() into this? It could >> just as easily be a different query. > > I am fine with that: Done. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgre

Re: [HACKERS] Query to help in debugging

On Sat, Jan 19, 2013 at 03:29:36PM -0500, Tom Lane wrote: > Bruce Momjian writes: > >> Tom Lane wrote: > >>> select name, setting, source from pg_settings > >>> where source not in ('default', 'override'); > > > Here is my very wide output: > > Why are you insisting on cramming version() into th

Re: [HACKERS] Query to help in debugging

Bruce Momjian writes: >> Tom Lane wrote: >>> select name, setting, source from pg_settings >>> where source not in ('default', 'override'); > Here is my very wide output: Why are you insisting on cramming version() into this? It could just as easily be a different query.

Re: [HACKERS] Query to help in debugging

On Sat, Jan 19, 2013 at 12:58:35PM -0500, Kevin Grittner wrote: > Tom Lane wrote: > > > I find the manual exclusion list to be poor style, and not at all > > future-proof. Maybe we could use > > > > select name, setting, source from pg_settings > > where source not in ('default', 'override'); > >

Re: [HACKERS] Query to help in debugging

Tom Lane wrote: > I find the manual exclusion list to be poor style, and not at all > future-proof. Maybe we could use > > select name, setting, source from pg_settings > where source not in ('default', 'override'); > > This would print a few not-all-that-interesting settings made by initdb, > b

Re: [HACKERS] Query to help in debugging

Bruce Momjian writes: > I am wondering if we should make this query more widely used, perhaps by > putting it in our docs about reporting bugs, or on our website. I find the manual exclusion list to be poor style, and not at all future-proof. Maybe we could use select name, setting, source from

Re: [HACKERS] Query to help in debugging

On Sat, Jan 19, 2013 at 11:20:19AM -0500, Kevin Grittner wrote: > Bruce Momjian wrote: > > > I am wondering if we should make this query more widely used, perhaps by > > putting it in our docs about reporting bugs, or on our website. > > http://wiki.postgresql.org/wiki/Server_Configuration > > h

Re: [HACKERS] Query to help in debugging

Bruce Momjian wrote: > I am wondering if we should make this query more widely used, perhaps by > putting it in our docs about reporting bugs, or on our website. http://wiki.postgresql.org/wiki/Server_Configuration http://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_you_need_to_me

Re: [HACKERS] Query ending up with hitting all the partition with sub-query in the projection list

On Fri, Oct 26, 2012 at 10:39 PM, Tom Lane wrote: > Rushabh Lathia writes: > > *-- Hitting all the partition table* > > postgres=# explain select a , ( select city_id from measurement where > *logdate > > = test.b and logdate = '2006-02-02')* xyz from test; > > Hm. What's happening here is that

Re: [HACKERS] Query ending up with hitting all the partition with sub-query in the projection list

Rushabh Lathia writes: > *-- Hitting all the partition table* > postgres=# explain select a , ( select city_id from measurement where *logdate > = test.b and logdate = '2006-02-02')* xyz from test; Hm. What's happening here is that you get an equivalence class containing logdate, test.b, and '20

Re: [HACKERS] Query plan optimization for CHECK NO INHERIT and single table?

On Thu, Aug 30, 2012 at 2:00 AM, Matthias wrote: > Hey, > > I tried out the new CHECK NO INHERIT feature for inherited tables. > There seems to be an opportunity to generate slightly better query > plans sometimes. E.g. when I do > > SELECT * FROM base WHERE partition_id = 3 > > and there exists o

Re: [HACKERS] query planner does not canonicalize infix operators

Dimitri Fontaine writes: > Looks like we're missing out some operator properties, like the neutral > element and if the operator is transitive, commutative or associative. I > think I remember us talking about how knowing about operators being > associative would also help optimize a class of join

Re: [HACKERS] query planner does not canonicalize infix operators

Tom Lane writes: > More generally, I'm not prepared to buy into the idea that the planner > should be expected to recognize alternate spellings of "the same" > expression. There are too many variants of that idea that are > infeasible either because the planner doesn't have the necessary > knowle

Re: [HACKERS] query cache

On Thu, Mar 29, 2012 at 4:57 PM, Tom Lane wrote: >> It's also probably worth keeping in mind the next time we >> bump the protocol version: it would be nice to have a way of doing >> prepare-bind-execute in a single protocol message, which I believe to >> be not possible at present. > > Huh?  That

Re: [HACKERS] query cache

Robert Haas writes: > Interestingly, Peter Geoghegan's blog post on the pg_stat_statements > patch you just committed[1] claims that the overhead of fingerprinting > queries was only 1-2.5%, which is less than I would have thought, so > if we ever get to the point where we're fairly sure we've got

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 2:54 PM, Tom Lane wrote: > Robert Haas writes: >> What I think is more common is the repeated submission of queries that >> are *nearly* identical, but with either different parameter bindings >> or different constants.  It would be nice to have some kind of cache >> that

Re: [HACKERS] query cache

Thanks.. I'll keep those issues in mind. On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo Ishii wrote: > >> Well, you'd have to start by demonstrating the benefit of it. The > >> advantage of query caches in proxies and clients is well-known, because > you > >> can offload some of the work of the datab

Re: [HACKERS] query cache

>> Well, you'd have to start by demonstrating the benefit of it. The >> advantage of query caches in proxies and clients is well-known, because you >> can offload some of the work of the database onto other servers, this >> increasing capacity. Adding a query cache to the database server would >>

Re: [HACKERS] query cache

On Sat, Mar 24, 2012 at 3:22 PM, Joshua Berkus wrote: > Billy, > > > I've done a brief search of the postgresql mail archives, and I've > > noticed a few projects for adding query caches to postgresql, (for > > example, Masanori Yamazaki's query cache proposal for GSOC 2011), > > ... which was co

Re: [HACKERS] query cache

Joshua Berkus writes: > If you want to do something radical and new, then come up with a way > for a client to request and then reuse a complete query plan by > passing it to the server. [ raised eyebrow ] That seems like a complete nonstarter on two different grounds: cache invalidation needs (

Re: [HACKERS] query cache

Billy, > I've done a brief search of the postgresql mail archives, and I've > noticed a few projects for adding query caches to postgresql, (for > example, Masanori Yamazaki's query cache proposal for GSOC 2011), ... which was completed, btw. Take a look at the current release of pgPool. Are yo

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 1:51 PM, Greg Stark wrote: > Well it's not entirely unlikely. If you step back a web application > looks like a big loop with a switch statement to go to different > pages. It keeps executing the same loop over and over again and there > are only a smallish number of web pa

Re: [HACKERS] query cache

Robert Haas writes: > What I think is more common is the repeated submission of queries that > are *nearly* identical, but with either different parameter bindings > or different constants. It would be nice to have some kind of cache > that would allow us to avoid the overhead of parsing and plan

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 12:03 PM, Robert Haas wrote: > On Fri, Mar 23, 2012 at 12:29 PM, Greg Stark wrote: >> On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane wrote: >>> The complication, opportunities for bugs, and general slowdown >>> associated with that would outweigh any possible gain, in the opin

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 5:03 PM, Robert Haas wrote: > The other thing that makes me skeptical of this proposal is that I am > not very sure that executing absolutely identical queries is a very > common use case for a relational database.  I suppose there might be a > few queries that run over and

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 12:29 PM, Greg Stark wrote: > On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane wrote: >> The complication, opportunities for bugs, and general slowdown >> associated with that would outweigh any possible gain, in the opinion >> of most hackers who have thought about this. > > I w

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 11:29 AM, Greg Stark wrote: > On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane wrote: > > The complication, opportunities for bugs, and general slowdown > > associated with that would outweigh any possible gain, in the opinion > > of most hackers who have thought about this. > >

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane wrote: > The complication, opportunities for bugs, and general slowdown > associated with that would outweigh any possible gain, in the opinion > of most hackers who have thought about this. I wouldn't be quite so pessimistic. I think the problem is that

Re: [HACKERS] query cache

Billy Earney writes: > I'm wondering if anyone would be interested in a query cache as a backend > to postgresql? I believe this has been suggested and rejected several times before. Did you look through the pgsql-hackers archives? > To invalidate cache entries, look at the transactions being co

Re: [HACKERS] query planner does not canonicalize infix operators

On mån, 2012-03-12 at 13:04 -0700, Daniel Farina wrote: > On the more constructive side, if I were to till the fields to change > this aspect of the optimizer, is there any interest in rectifying the > operator-function confusion? I once proposed to do that [1], but there was not much enthusiasm e

Re: [HACKERS] query planner does not canonicalize infix operators

On Mon, Mar 12, 2012 at 12:22 PM, Tom Lane wrote: > Daniel Farina writes: >> On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane wrote: >>> Will Leinweber writes: I created an index on an hstore function, fetchval(hstore, text), however when I use the -> infix operator which resolves to the ver

Re: [HACKERS] query planner does not canonicalize infix operators

Daniel Farina writes: > On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane wrote: >> Will Leinweber writes: >>> I created an index on an hstore function, fetchval(hstore, text), however >>> when I use the -> infix operator which resolves to the very same function, >>> this index is not used. It should be

Re: [HACKERS] query planner does not canonicalize infix operators

On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane wrote: > Will Leinweber writes: >> I created an index on an hstore function, fetchval(hstore, text), however >> when I use the -> infix operator which resolves to the very same function, >> this index is not used. It should be used. > > Don't hold your br

Re: [HACKERS] query planner does not canonicalize infix operators

Will Leinweber writes: > I created an index on an hstore function, fetchval(hstore, text), however > when I use the -> infix operator which resolves to the very same function, > this index is not used. It should be used. Don't hold your breath. Create an index on the expression you intend to use

Re: [HACKERS] Query regarding postgres lock contention - Followup

On Wed, Oct 5, 2011 at 1:48 PM, Kevin Grittner wrote: > Hamza Bin Sohail wrote: > >> My postgres version is 8.3.7 > > Why such an old version?  Why exclude the available bug fixes? > > http://www.postgresql.org/support/versioning > >>> I am aware that lock contention can be checked with lockstat

Re: [HACKERS] Query regarding postgres lock contention - Followup

Hamza Bin Sohail wrote: > My postgres version is 8.3.7 Why such an old version? Why exclude the available bug fixes? http://www.postgresql.org/support/versioning >> I am aware that lock contention can be checked with lockstat (and >> with pg_locks ? ) but I wanted to know if someone can t

Re: [HACKERS] Query to generate CREATE INDEX statement from

Andrew Hammond writes: > I'm building some partitioning support functions. I'm working on writing one > called clone_indexes_to_partition right now. The idea is to take all the > indexes applied to the parent and create a matching index on the child. Is > there existing code that generates a CREAT

Re: [HACKERS] Query to generate CREATE INDEX statement from

2011/3/24 Andrew Hammond : > I'm building some partitioning support functions. I'm working on writing one > called clone_indexes_to_partition right now. The idea is to take all the > indexes applied to the parent and create a matching index on the child. Is > there existing code that generates a CR

Re: [HACKERS] query execution question

amit sehas wrote: > We are making some performance measurements, we are trying to > determine query execution behavior. I hope you're not doing this on an un-tuned server or "toy" tables. There are a number of configuration parameters which should be tuned for your particular server, which wi

Re: [HACKERS] query execution question

2011/2/9 amit sehas : > Lets say that the cost based optimizer determines that the order of the > joins should be   T1.a=T2.b followed by T2.c = T3.d followed by T3.e = T4.f > > the question we have is during query execution are the joins evaluated > completely one by one in that order, or the fir

Re: [HACKERS] Query Optimizer + Parallel Operators

On 26.01.2011 16:46, Felix Schmidt @ Oracle wrote: Everybody, I'm interested in the query optimizer of PostgreSQL DB. Where could I find useful documentation or could you send me a pointer in the source code? The relevant source code is in src/backend/optimizer directory. If you google around

Re: [HACKERS] Query Optimizer + Parallel Operators

This is kinda scary . Oracle guy asking for PostgreSQL documentation and internals of the optimizer. On Thu, Jan 27, 2011 at 12:14 AM, Josh Berkus wrote: > Felix, > > > I'm interested in the query optimizer of PostgreSQL DB. Where could I > > find useful documentation or could you send me

Re: [HACKERS] Query Optimizer + Parallel Operators

Felix, > I'm interested in the query optimizer of PostgreSQL DB. Where could I > find useful documentation or could you send me a pointer in the source code? > > What kind of parallelism does PostgreSQL use for operators, like > selection or join? Normally we're very helpful with this kind of in

Re: [HACKERS] Query Plan Columns

On Nov 9, 2010, at 12:12 AM, Dimitri Fontaine wrote: > WITH plan AS ( > EXPLAIN (format table) SELECT * FROM bar > ) > INSERT INTO plan_audit > SELECT * FROM plan > WHERE actual_total_time > 12 * interval '100 ms'; Yeah, that would be nice, but my current implementation has a row for each node,

Re: [HACKERS] Query Plan Columns

"David E. Wheeler" writes: > I'm wondering if there's any reason why we couldn't have EXPLAIN do something > like this itself in core: > > EXPLAIN (format table) SELECT * FROM bar; +1 from me here, as it happens "parsing" a table-like resultset is already implemented everywhere :) I'd add a

Re: [HACKERS] Query Plan Columns

On Nov 8, 2010, at 3:12 PM, David E. Wheeler wrote: > It could output a table like the above. FWIW, The function I've written works > like this: > >SELECT plan('SELECT * FROM bar'); Sorry, that's SELECT * FROM plan('SELECT * FROM bar'); Best, David -- Sent via pgsql-hackers mailin

Re: [HACKERS] Query Plan Columns

On Nov 7, 2010, at 5:24 AM, Roberto Mello wrote: > Yes, but I am wondering whether you should just stick to what would > come out of a normal explain, for consistency sake. Maybe provide > another function, or parameter that would cast the results to > intervals? I think it's more convenient to h

Re: [HACKERS] Query Plan Columns

On Sat, Nov 6, 2010 at 6:24 PM, David E. Wheeler wrote: > > Would I be right that "Node Type" is the only column can be NOT NULL? > > Also, I'm thinking of making the "Actual Startup Time" and ""Actual Total > Time" columns into INTERVALs. The times are expressed in milliseconds, yes? > I'm wond

Re: [HACKERS] Query Plan Columns

On Nov 6, 2010, at 11:44 AM, David E. Wheeler wrote: > On Nov 5, 2010, at 1:42 PM, David E. Wheeler wrote: > >>> http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/commands/explain.c;h=f494ec98e510c23120e072bd5ee8821ea12738a4;hb=HEAD#l617 >> >> Ah, great, thanks. > > So base

  1   2   3   4   >