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

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

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

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

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 -

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

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

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

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

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

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

[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 , p.p

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.

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

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

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

[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 we took the fast-and-dirty approach of merging in a modified ruleutils.c, though I’d (obviously) like to get away from that. Man

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

[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 , saying that query rewriting cou

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

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

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

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

[HACKERS] Query to help in debugging

On Fri, Jan 18, 2013 at 07:35:43AM -0800, bricklen wrote: > Please post the results of the following query: > > SELECT 'version'::text AS "name", > version() AS "current_setting" > UNION ALL > SELECT name, > current_setting(name) > FROM pg_settings > WHERE NOT source='default'AND

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

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

Hi All, Query with the sub-query in the projection list ending up with hitting all the partition table even though having proper partition key condition. Example: CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktempint, unitsale

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

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

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

[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. http://archives.postgresql.org/message-id/66ce997fb523c04e9749452273184c6c137cb

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

[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/ (pg

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

  1   2   3   4   5   >