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

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

[HACKERS] Query regarding permission on table_column%type access

Hi, 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. Scenario: postgres=# create user u1 with login ; CREATE ROLE postgres=# create user u2 with login ; CREATE

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

[HACKERS] Query got Killed with CTE.

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,100) x) select * from x x1 join x x2 using(c1); Killed

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

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

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

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

Hi, Steps to reproduce - \\ PG HEAD / PG v10  sources . Connect to psql terminal -  create these following object create table tv(n int,n1 char(100)); insert into tv values (generate_series(1,100),'aaa'); insert into tv values (generate_series(1,100),'a'); analyze tv; vacuum tv;

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

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 >

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.

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

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

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

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

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

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

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

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))

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

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

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, >>

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

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

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; Above query is failing with "set-valued function called in context

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

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

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

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 >> ? >>

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 > ?

[HACKERS] Query regarding selectDumpableExtension()

Hi ​,​ ​ 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 ? Thanks ​ & Regards, Amul​

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

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

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

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

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,

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 >>

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:

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

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

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 2016 was fine, then "statement cancel" started to fail. The test executes "select pg_sleep(10)" and

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

[HACKERS] Query Procedures

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 panel. with the query : p.oid AS SELECT oid , p.proname AS name ,

Re: [HACKERS] query in PostgreSQL

On Fri, Aug 14, 2015 at 9:32 PM, Rahul Majumdar/TVM/TCS rahul.majum...@tcs.com 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

[HACKERS] query in PostgreSQL

Hello Everyone, I have a problem related to query in PostgreSQL.Some queries are not running.Please help me to find out a solution. Thanks Rahul =-=-= Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged

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

On Sun, Jun 14, 2015 at 6:43 PM, David Kamholz lautges...@gmail.com 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

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 tables

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

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 am posting it here rather than to -performance. The query is

[HACKERS] Query Deparsing Support

The DDL deparse support that just landed looks impressive, but I’ve needed query deparsing for some time now. Within pg_shard https://github.com/citusdata/pg_shard we took the fast-and-dirty approach of merging in a modified ruleutils.c, though I’d (obviously) like to get away from that. Many

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 http://www.postgresql.org/message-id/cak7uwezzxjjsxp9uoxwhpnjjutjajvpkja9skzalsnnrdjs...@mail.gmail.com proposing to work on it as my Google Summer of Code

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

Robert Haas robertmh...@gmail.com 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

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

On Sun, Feb 15, 2015 at 10:05 PM, Eric Grinstein ericgrinst...@gmail.com 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

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

On Sat, Feb 21, 2015 at 1:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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

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

Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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

[HACKERS] Query Rewrite with Postgres' materialized views

Hello, 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 http://www.postgresql.org/message-id/200501041006.18735.j...@agliodbs.com, saying that query rewriting

[HACKERS] Query cancel in regex library

We have a couple of open bug reports in which the regex library takes unreasonable amounts of time and/or memory to process a regexp. While I have hopes that both of the known issues can be improved, it seems likely that there will always be cases where sufficiently complicated regexps just take

[HACKERS] Query not using index for user defined type

Hi guys. 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. Here is the explain

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

On Mon, Apr 15, 2013 at 3:08 PM, Rodrigo Barboza rodrigombu...@gmail.com 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

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

Rodrigo Barboza rodrigombu...@gmail.com 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

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

On Mon, Apr 15, 2013 at 5:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rodrigo Barboza rodrigombu...@gmail.com 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

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

Rodrigo Barboza rodrigombu...@gmail.com 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

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

On Mon, Apr 15, 2013 at 7:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rodrigo Barboza rodrigombu...@gmail.com 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

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:

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

Re: [HACKERS] Query to help in debugging

Bruce Momjian br...@momjian.us 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,

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, but not

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'); This

Re: [HACKERS] Query to help in debugging

Bruce Momjian br...@momjian.us 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 03:29:36PM -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us 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

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

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 only one child table for which partition_id = 3 is true I guess

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

On Thu, Aug 30, 2012 at 2:00 AM, Matthias nitrogen...@gmail.com 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

[HACKERS] Query planning, nested loops and FDW.

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello. Thanks to the indications given by Tom Lane, I was able to make use of parameterized path in our multicorn extension. For example, given a (local) table real_table and another (foreign) table foreign_table, having the same set of columns, if

[HACKERS] Query from VMWare re use of PostgreSQL in new TPC-V benchmark

Heya Just in case someone missed it on -perform and is interested, a couple of questions have come in from VMWare, who want to use PostgreSQL as the default reference implementation for a new TPC benchmark, TPC-V.

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

Tom Lane t...@sss.pgh.pa.us 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

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

Dimitri Fontaine dimi...@2ndquadrant.fr 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

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 2:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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

Re: [HACKERS] query cache

Robert Haas robertmh...@gmail.com 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

Re: [HACKERS] query cache

On Thu, Mar 29, 2012 at 4:57 PM, Tom Lane t...@sss.pgh.pa.us 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.

Re: [HACKERS] query cache

Thanks.. I'll keep those issues in mind. On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo Ishii is...@postgresql.org 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

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 you

Re: [HACKERS] query cache

Joshua Berkus j...@agliodbs.com 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

Re: [HACKERS] query cache

On Sat, Mar 24, 2012 at 3:22 PM, Joshua Berkus j...@agliodbs.com 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), ...

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 require

[HACKERS] query cache

Greetings! 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), as well as the query cache announced at http://www.postgresql.org/about/news/1296/

Re: [HACKERS] query cache

Billy Earney billy.ear...@gmail.com 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

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us 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

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 11:29 AM, Greg Stark st...@mit.edu wrote: On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: The complication, opportunities for bugs, and general slowdown associated with that would outweigh any possible gain, in the opinion of most hackers who

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 12:29 PM, Greg Stark st...@mit.edu wrote: On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us 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

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 5:03 PM, Robert Haas robertmh...@gmail.com 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

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 12:03 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 23, 2012 at 12:29 PM, Greg Stark st...@mit.edu wrote: On Fri, Mar 23, 2012 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: The complication, opportunities for bugs, and general slowdown associated with that

Re: [HACKERS] query cache

Robert Haas robertmh...@gmail.com 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

Re: [HACKERS] query cache

On Fri, Mar 23, 2012 at 1:51 PM, Greg Stark st...@mit.edu 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

[HACKERS] query planner does not canonicalize infix operators

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. I have included an example: Table with hstore index: de10keipt01939= \d log_data

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

Will Leinweber w...@heroku.com 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

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

On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Will Leinweber w...@heroku.com 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

Daniel Farina dan...@heroku.com writes: On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Will Leinweber w...@heroku.com 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,

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

On Mon, Mar 12, 2012 at 12:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina dan...@heroku.com writes: On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Will Leinweber w...@heroku.com writes: I created an index on an hstore function, fetchval(hstore, text), however

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

  1   2   3   4   >