Re: [HACKERS] Common Table Expressions applied; some issues remain
On Wed, May 27, 2009 at 12:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not too thrilled about that solution because it still eliminates predictability of execution of volatile functions. How so? It means the volatile function might only be executed for the matching rows but the rows will still have the same value for the same rows for all references to the CTE which seems like the key property to me. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Greg Stark wrote: On Wed, May 27, 2009 at 12:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not too thrilled about that solution because it still eliminates predictability of execution of volatile functions. How so? It means the volatile function might only be executed for the matching rows but the rows will still have the same value for the same rows for all references to the CTE which seems like the key property to me. A volatile function could have side-effects, ie. insert rows to another table. I would not recommend a design that relies on such behavior, but it should be predictable how often the volatile function is run if you do that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Greg Stark st...@enterprisedb.com writes: On Wed, May 27, 2009 at 12:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not too thrilled about that solution because it still eliminates predictability of execution of volatile functions. How so? It means the volatile function might only be executed for the matching rows Exactly. If the point of the CTE is to ensure that nextval() is executed N times, and it actually gets executed less than that, then we've broken the semantics in a visible way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Greg Stark st...@enterprisedb.com writes: [ point 1 here remains unresolved: http://archives.postgresql.org/message-id/9623.1223158...@sss.pgh.pa.us ] One possibility would be to not flatten the query but find these quals and copy them onto the cte when planning the cte. So we would still materialize the result and avoid duplicate execution but only fetch the records which we know a caller will need. We could even do that for multiple callers if we join their quals with an OR -- that still might allow a bitmap index scan. I'm not too thrilled about that solution because it still eliminates predictability of execution of volatile functions. It's really just a partial form of subquery pullup, so we're paying all the disadvantages for only a subset of the advantages. I could still see doing what I mentioned in the prior message, which is to flatten CTEs as if they are plain sub-selects when 1. they are non-recursive, 2. they are referenced only once, and 3. they contain no volatile functions. Restriction #3 is what we need to ensure we aren't causing visible semantics changes. You could argue #2 either way, I guess, but my feeling is that if someone is using a doubly referenced CTE then he's probably doing something more complex than we are currently prepared to optimize well. I think we should let that case go until we understand typical usage and possible optimizations better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
2009/5/27 Tom Lane t...@sss.pgh.pa.us: Greg Stark st...@enterprisedb.com writes: [ point 1 here remains unresolved: http://archives.postgresql.org/message-id/9623.1223158...@sss.pgh.pa.us ] One possibility would be to not flatten the query but find these quals and copy them onto the cte when planning the cte. So we would still materialize the result and avoid duplicate execution but only fetch the records which we know a caller will need. We could even do that for multiple callers if we join their quals with an OR -- that still might allow a bitmap index scan. I'm not too thrilled about that solution because it still eliminates predictability of execution of volatile functions. It's really just a partial form of subquery pullup, so we're paying all the disadvantages for only a subset of the advantages. I could still see doing what I mentioned in the prior message, which is to flatten CTEs as if they are plain sub-selects when 1. they are non-recursive, 2. they are referenced only once, and 3. they contain no volatile functions. And 4. only if the sub-selects use index scan? Or in other cases would it be effective? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Hitoshi Harada umi.tan...@gmail.com writes: 2009/5/27 Tom Lane t...@sss.pgh.pa.us: I could still see doing what I mentioned in the prior message, which is to flatten CTEs as if they are plain sub-selects when 1. they are non-recursive, 2. they are referenced only once, and 3. they contain no volatile functions. And 4. only if the sub-selects use index scan? Or in other cases would it be effective? Uh ... you've got the causality backwards, and I don't see the point of such a restriction anyway. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
(quoting more than usual to provide context because this is such an old thread) On Sat, Oct 4, 2008 at 11:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've applied the latest version of the CTE patch. Congratulations on making that happen! There are still some loose ends that need to be considered, though. 1. As committed, the patch takes an extremely hard line about WITH queries being evaluated independently of the main query and only once per main query execution. This could be seen as a good thing --- it provides much more determinism for execution of volatile functions within complex queries than was really available in the past. It could also be seen as a bad thing --- in particular, we won't push any limiting qualifications from the main query into the WITH queries. So for instance WITH q AS ( SELECT * FROM foo ) SELECT * FROM q WHERE key = 42; is going to be executed quite inefficiently; it won't use an index on foo.key. I think we don't have much choice about this in the case of recursive WITH queries: it would be pretty difficult to determine whether pushing a restriction into a recursive WITH would change the results incorrectly. However, for plain non-recursive WITHs it's all a matter of definition. I gather from http://www.oracle-developer.net/display.php?id=212 that Oracle chooses to treat WITH-queries as if they were plain sub-selects if they're non-recursive and only referenced once. That is, Oracle would rewrite the above into SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42; and then flatten the sub-select and optimize normally. It would not be hard to make Postgres do the same, but then we would lose some guarantees about predictable execution of volatile functions. I'm inclined to think that there is no reason to provide two different syntaxes to do the same thing, and so having the WITH syntax behave like this is okay. But it could well result in performance surprises for people who are used to Oracle. Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. One possibility would be to not flatten the query but find these quals and copy them onto the cte when planning the cte. So we would still materialize the result and avoid duplicate execution but only fetch the records which we know a caller will need. We could even do that for multiple callers if we join their quals with an OR -- that still might allow a bitmap index scan. I'm not sure we will work out with the order of in which the various phases of analysis are done on the outer query compared to the subquery. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Bruce Momjian br...@momjian.us writes: Is this a TODO? I'm inclined to leave it as-is, at least till we get some field feedback about how people want it to behave. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Is this a TODO? --- Tom Lane wrote: [ back to the when-to-inline-WITHs discussion ] Gregory Stark st...@enterprisedb.com writes: Tom Lane wrote: Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. I think we should always inline the view if there's a single call site. If people want to control the subsequent flattening they can do it the same way they can do today for inline views using OFFSET 0. That's certainly a defensible choice, and in fact was what I had intended to do at one point (that's why CommonTableExpr.cterefcount is in there). However, the extent to which you can prevent duplicate evaluation in an inline view is actually pretty limited. As an example consider select ... from table1, (select expensive_function(...) from table2 offset 0) ss where table1.key = table2.key; If the planner chooses to do this as a nestloop with table2 on the inside, then expensive_function() can get evaluated multiple times on the same row of table2. We really don't make very many guarantees about what will happen with functions inside inlined views, even with offset 0 as an optimization fence. So I was thinking that taking a strong reading of the spec's wording about single evaluation of WITH clauses might provide useful leverage for people who need to control evaluation of expensive or volatile functions better than they can now. Another possibility that we could think about is: if a CTE is only referenced once, then push down any restriction clauses that are available at the single call site, but still execute it using the CteScan materialization logic. The evaluation guarantee would then look like no row of the CTE's result is evaluated twice, but some rows might not be evaluated at all. What we'd pay for this is that the CTE could not be the inside of a nestloop with inner indexscan using a join condition, since we don't have any way to keep track of which rows were already fetched in that case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
On Oct 5, 2008, at 1:11 AM, Peter Eisentraut wrote: I don't think we should overload syntax choices with optimization hints. We don't really know why or how people will be using this syntax, and labeling it from the start as will have unusual performance behavior isn't a good sell. As a precedent, consider the JOIN syntax, which is obviously redundant and in its first implementation contained an implicit optimization hint with regard to join order that later had to be done away with because it confused users (I think). The CTE case is quite similar, and maybe the GUC answer of old could apply here as well. But I think by default we should abide by SQL's declarative approach of Tell me what you want and I'll execute it any way I like. Agreed. It's already horrible that we suggest people use OFFSET 0, only because we don't want to define formal optimizer hints (and that's *exactly* what OFFSET 0 is). -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Common Table Expressions applied; some issues remain
Agreed. It's already horrible that we suggest people use OFFSET 0, only because we don't want to define formal optimizer hints (and that's *exactly* what OFFSET 0 is). Yes, especially since TFM says: OFFSET 0 is the same as omitting the OFFSET clause. Unless I'm looking at the wrong part of the manual? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
[ back to the when-to-inline-WITHs discussion ] Gregory Stark [EMAIL PROTECTED] writes: Tom Lane wrote: Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. I think we should always inline the view if there's a single call site. If people want to control the subsequent flattening they can do it the same way they can do today for inline views using OFFSET 0. That's certainly a defensible choice, and in fact was what I had intended to do at one point (that's why CommonTableExpr.cterefcount is in there). However, the extent to which you can prevent duplicate evaluation in an inline view is actually pretty limited. As an example consider select ... from table1, (select expensive_function(...) from table2 offset 0) ss where table1.key = table2.key; If the planner chooses to do this as a nestloop with table2 on the inside, then expensive_function() can get evaluated multiple times on the same row of table2. We really don't make very many guarantees about what will happen with functions inside inlined views, even with offset 0 as an optimization fence. So I was thinking that taking a strong reading of the spec's wording about single evaluation of WITH clauses might provide useful leverage for people who need to control evaluation of expensive or volatile functions better than they can now. Another possibility that we could think about is: if a CTE is only referenced once, then push down any restriction clauses that are available at the single call site, but still execute it using the CteScan materialization logic. The evaluation guarantee would then look like no row of the CTE's result is evaluated twice, but some rows might not be evaluated at all. What we'd pay for this is that the CTE could not be the inside of a nestloop with inner indexscan using a join condition, since we don't have any way to keep track of which rows were already fetched in that case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
If the planner chooses to do this as a nestloop with table2 on the inside, then expensive_function() can get evaluated multiple times on the same row of table2. We really don't make very many guarantees about what will happen with functions inside inlined views, even with offset 0 as an optimization fence. So I was thinking that taking a strong reading of the spec's wording about single evaluation of WITH clauses might provide useful leverage for people who need to control evaluation of expensive or volatile functions better than they can now. +1 for a strong reading. I think the ability to prevent multiple evaluations of expensive functions is key here. Another possibility that we could think about is: if a CTE is only referenced once, then push down any restriction clauses that are available at the single call site, but still execute it using the CteScan materialization logic. The evaluation guarantee would then look like no row of the CTE's result is evaluated twice, but some rows might not be evaluated at all. Assuming a perfectly intelligent optimizer, the only advantage of the =1 guarantee over the =1 guarantee is that you can evaluate the entire CTE for side-effects and then fetch back only a subset of the data to return to the user. This seems likely to be a pretty rare use case, though, and the rest of the time you'd presumably prefer for performance reasons to have as little of the CTE as possible executed so +1 for =1. What we'd pay for this is that the CTE could not be the inside of a nestloop with inner indexscan using a join condition, since we don't have any way to keep track of which rows were already fetched in that case. Is it not possible to consider both plans? That is, compare the cost of evaluating every row and then doing a nestloop with inner indexscan versus using some other plan and evaluating only the rows meeting the quals? As a side note, in theory, I think you could generalize this to CTEs with multiple call sites by taking the logical OR of the available quals. This might not be worth it, though unless the quals are highly selective. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Tom Lane wrote: that Oracle chooses to treat WITH-queries as if they were plain sub-selects if they're non-recursive and only referenced once. That is, Oracle would rewrite the above into SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42; and then flatten the sub-select and optimize normally. It would not be hard to make Postgres do the same, but then we would lose some guarantees about predictable execution of volatile functions. I'm inclined to think that there is no reason to provide two different syntaxes to do the same thing, and so having the WITH syntax behave like this is okay. But it could well result in performance surprises for people who are used to Oracle. Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. I don't think we should overload syntax choices with optimization hints. We don't really know why or how people will be using this syntax, and labeling it from the start as will have unusual performance behavior isn't a good sell. As a precedent, consider the JOIN syntax, which is obviously redundant and in its first implementation contained an implicit optimization hint with regard to join order that later had to be done away with because it confused users (I think). The CTE case is quite similar, and maybe the GUC answer of old could apply here as well. But I think by default we should abide by SQL's declarative approach of Tell me what you want and I'll execute it any way I like. Also, why is predictability about volatile function executation a requirement? Is there some typical use case that involves sequences functions here or something? 2. The patch didn't touch the implicit-RTE code, which means that WITH q AS ( SELECT ... ) SELECT q.* will fail even if you've got add_missing_from enabled. I'm inclined to think that this violates the principle of least surprise. On the other hand, add_missing_from is certainly a legacy thing and maybe we shouldn't bother expending any extra code to make it work with new features. Thoughts? Yes, it's legacy. I wouldn't bother. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: 2. The patch didn't touch the implicit-RTE code, which means that WITH q AS ( SELECT ... ) SELECT q.* will fail even if you've got add_missing_from enabled. I'm inclined to think that this violates the principle of least surprise. On the other hand, add_missing_from is certainly a legacy thing and maybe we shouldn't bother expending any extra code to make it work with new features. Thoughts? Yes, it's legacy. I wouldn't bother. The results would be even more suprising if there *is* a table named q though... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Tom Lane wrote: that Oracle chooses to treat WITH-queries as if they were plain sub-selects if they're non-recursive and only referenced once. That is, Oracle would rewrite the above into SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42; and then flatten the sub-select and optimize normally. It would not be hard to make Postgres do the same, but then we would lose some guarantees about predictable execution of volatile functions. I'm inclined to think that there is no reason to provide two different syntaxes to do the same thing, and so having the WITH syntax behave like this is okay. But it could well result in performance surprises for people who are used to Oracle. Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. I think we should always inline the view if there's a single call site. If people want to control the subsequent flattening they can do it the same way they can do today for inline views using OFFSET 0. The question in my mind is if we can do better for CTEs with multiple call sites. If we have no volatile function calls in them then we should be free to inline some or all call sites. I'm not sure we have enough information early enough to make the decision though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Gregory Stark [EMAIL PROTECTED] writes: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: 2. The patch didn't touch the implicit-RTE code, which means that WITH q AS ( SELECT ... ) SELECT q.* will fail even if you've got add_missing_from enabled. Yes, it's legacy. I wouldn't bother. The results would be even more suprising if there *is* a table named q though... Yeah, the real problem is not so much that it might fail as that it might silently do something quite different from what you would expect. CVS HEAD documentation states (In fact, the WITH query hides any real table of the same name for the purposes of the primary query. If necessary, you can refer to a real table of the same name by schema-qualifying the table's name.) If we don't fix this, I think we'd have to add some disclaimer about how WITH clauses *don't* hide real tables in the case of implicit RTE additions. That seems much uglier than fixing it. (Hmm, memo to self: I'll bet ruleutils.c's decision about whether it needs to schema-qualify a reverse-listed table name doesn't take this into account.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Hi all, While i'm testing the HEAD version of CVS with this new feature, i found a possible bug and like that more persons could try it in you own box. The attached file is a log of my test and I'm using a unprivileged user to do it. Thanks. -- []s Dickson S. Guedes - Projeto Colmeia - Florianopolis, SC (48) 3322-1185 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/ [EMAIL PROTECTED]:/srv/postgresql/HEAD/bin$ /srv/postgresql/HEAD/bin/psql psql (8.4devel) Type help for help. postgres=# SELECT version(); version --- PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7) (1 row) postgres=# CREATE USER blog; CREATE ROLE postgres=# CREATE DATABASE blo postgres=# CREATE DATABASE blog OWNER postgres=# CREATE DATABASE blog OWNER blog; CREATE DATABASE postgres=# \c blog blog psql (8.4devel) You are now connected to database blog as user blog. blog= CREATE SCHEMA hr; CREATE SCHEMA blog= CREATE TABLE hr.person( blog( nro_doc numeric(10) primary key, blog( name varchar(50) not null, blog( nro_doc_parent numeric(10) references hr.person(nro_doc) blog( ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index person_pkey for table person CREATE TABLE blog= SET search_path TO public,hr; SET blog= \dt List of relations Schema | Name | Type | Owner ++---+--- hr | person | table | blog (1 row) blog= \d person Table hr.person Column | Type | Modifiers +---+--- nro_doc| numeric(10,0) | not null name | character varying(50) | not null nro_doc_parent | numeric(10,0) | Indexes: person_pkey PRIMARY KEY, btree (nro_doc) Foreign-key constraints: person_nro_doc_parent_fkey FOREIGN KEY (nro_doc_parent) REFERENCES person(nro_doc) Referenced by: person_nro_doc_parent_fkey IN person FOREIGN KEY (nro_doc_parent) REFERENCES person(nro_doc) blog= INSERT INTO hr.person VALUES blog- (1234567890,'JOAO DA SILVA',NULL), blog- (0987654321,'MARCO ANTONIO DE SOUZA',NULL), blog- (0192837465,'MARIA ANTONIETA DA SILVA',1234567890), blog- (0987612345,'LUIZ ANTONIO MARCOS',1234567890), blog- (0912219083,'ALTAZIGIO FERREIRA MARCOS',0987612345); INSERT 0 5 blog= WITH RECURSIVE parents(son_name,son_doc,parent_name,parent_doc) AS blog- ( blog( SELECT name, nro_doc, '' as parent_name, 0 as parent_doc FROM hr.person WHERE nro_doc_parent is null blog( UNION ALL blog( SELECT a.name as son_name, blog( a.nro_doc as son_doc, blog( b.parent_name, blog( b.parent_doc blog( FROMhr.person a INNER JOIN parents b ON a.nro_doc_parent = b.parent_doc blog( ) blog- SELECT * from filiacao order by 1,2; TRAP: FailedAssertion(!(Node*)(cte-ctequery))-type) == T_Query)), File: parse_target.c, Line: 307) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: LOG: server process (PID 7055) was terminated by signal 6: Aborted LOG: terminating any other active server processes FATAL: the database system is in recovery mode Failed. ! LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-10-05 17:28:57 BRT LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/498380 LOG: redo is not required LOG: autovacuum launcher started LOG: database system is ready to accept connections ! \q [EMAIL PROTECTED]:/srv/postgresql/HEAD/bin$ /srv/postgresql/HEAD/bin/psql -U blog psql (8.4devel) Type help for help. blog= WITH RECURSIVE parents(son_name,son_doc,parent_name,parent_doc) AS ( SELECT name, nro_doc, '' as parent_name, 0 as parent_doc FROM hr.person WHERE nro_doc_parent is null UNION ALL SELECT a.name as son_name, a.nro_doc as son_doc, b.parent_name, b.parent_doc FROMhr.person a INNER JOIN parents b ON a.nro_doc_parent = b.parent_doc ) SELECT * from filiacao order by 1,2; TRAP: FailedAssertion(!(Node*)(cte-ctequery))-type) == T_Query)), File: parse_target.c, Line: 307) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: LOG: server process (PID 7085) was terminated by signal 6: Aborted LOG: terminating any other active server processes FATAL: the
Re: [HACKERS] Common Table Expressions applied; some issues remain
Dickson S. Guedes [EMAIL PROTECTED] writes: While i'm testing the HEAD version of CVS with this new feature, i found a possible bug and like that more persons could try it in you own box. Yeah, that's a bug (two different ones in fact). Fixed --- thanks for the report! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
On Sat, Oct 4, 2008 at 5:22 PM, Tom Lane [EMAIL PROTECTED] wrote: that Oracle chooses to treat WITH-queries as if they were plain sub-selects if they're non-recursive and only referenced once. That is, Oracle would rewrite the above into SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42; and then flatten the sub-select and optimize normally. It would not be hard to make Postgres do the same, but then we would lose some guarantees about predictable execution of volatile functions. [...] Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. maybe i'm missing something but AFAIR postgres will not try to optimize (push down/pull up) if it see any volatile function. -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
I've applied the latest version of the CTE patch. Congratulations on making that happen! Great! and thanks, Tom. Without your great help, we cannot make it reality. I also would like to thank to everyone who helped this project! There are still some loose ends that need to be considered, though. I think in addition to them, we need to update ecpg. 1. As committed, the patch takes an extremely hard line about WITH queries being evaluated independently of the main query and only once per main query execution. This could be seen as a good thing --- it provides much more determinism for execution of volatile functions within complex queries than was really available in the past. It could also be seen as a bad thing --- in particular, we won't push any limiting qualifications from the main query into the WITH queries. So for instance WITH q AS ( SELECT * FROM foo ) SELECT * FROM q WHERE key = 42; is going to be executed quite inefficiently; it won't use an index on foo.key. I think we don't have much choice about this in the case of recursive WITH queries: it would be pretty difficult to determine whether pushing a restriction into a recursive WITH would change the results incorrectly. However, for plain non-recursive WITHs it's all a matter of definition. I gather from http://www.oracle-developer.net/display.php?id=212 that Oracle chooses to treat WITH-queries as if they were plain sub-selects if they're non-recursive and only referenced once. That is, Oracle would rewrite the above into SELECT * FROM ( SELECT * FROM foo ) AS q WHERE key = 42; and then flatten the sub-select and optimize normally. It would not be hard to make Postgres do the same, but then we would lose some guarantees about predictable execution of volatile functions. I'm inclined to think that there is no reason to provide two different syntaxes to do the same thing, and so having the WITH syntax behave like this is okay. But it could well result in performance surprises for people who are used to Oracle. Any thoughts on what to do? One possibility is to flatten only if the subquery doesn't contain any volatile functions. 2. The patch didn't touch the implicit-RTE code, which means that WITH q AS ( SELECT ... ) SELECT q.* will fail even if you've got add_missing_from enabled. I'm inclined to think that this violates the principle of least surprise. On the other hand, add_missing_from is certainly a legacy thing and maybe we shouldn't bother expending any extra code to make it work with new features. Thoughts? 3. ruleutils.c's get_name_for_var_field() hasn't implemented the RTE_CTE case, which means that it doesn't work to reverse-list examples like this: explain verbose with qq as (select x from (values(1,2),(3,4)) as x(c1,c2)) select * from (select (x).c2 from qq offset 0) ss; The reason I let this go is that while poking into it I found out that get_name_for_var_field is pretty broken already; this fails in HEAD: explain verbose select (x).c2 from (select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss ; and this fails even in the back branches: explain select * from (select x from (values(1,2),(3,4)) as x(c1,c2) offset 0) ss where (x).c2 0; It seems we need some redesign in and around EXPLAIN to make that work nicely, so I figured it would be reasonable to tackle that stuff as a separate patch. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions applied; some issues remain
Tom Lane wrote: I've applied the latest version of the CTE patch. Congratulations on making that happen! This is great news. A big thanks to all the people involved in making this happen. I've had several people come up to me during the conference that I've been at that just ended asking if I thought this would make it into 8.4. It's certainly a much asked-for feature! //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
