Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common expressions up out of the CTE, etc. It's often claimed to be a behaviour required by the SQL standards.

CTEs are definitely an optimisation fence in PostgreSQL ... but is this required by the standard, or actually just implementation detail?

For example, these mailing list posts claim or suggest that it's standard:

After mentioning it in a comment I was asked where it's specified - and after a look at the only draft of SQL:2008 I have access to I'm not having much luck finding it.

I haven't yet intensively studied the standard, so I'm hoping for a suggestion from somebody who has: Is the optimisation fencing of CTEs in PostgreSQL actually required by the standard? And if so, where's it specified? Or are the statements on the Pg mailing list in error?

See also the thread CTE optimization fence on the todo list?.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

I think it's an implementation detail.

A conforming implementation is not required to perform the exact sequence of actions defined in the General Rules, provided its effect on SQL-data and schemas, on host parameters and host variable, and on SQL parameters and SQL variables is identical to the effect of that sequence. The term effectively is used to emphasize actions whose effect might be achieved in other ways by an implementation.1

I think an implementer could evaluate a common table expression 20 times, even in 20 different ways, and still have a conforming implementation. The only relevant issue is whether "its effect . . . is identical to the effect" of the sequence of actions defined in the General rules.

[1]. Section 6.3.3.3, "Rule evaluation order", in a draft of the SQL 2008 standard, having the local filename 5CD2-01-Framework-2006-01.pdf, p. 41 I have no idea where I got it. Google might know.

share|improve this answer
2  
That makese sense - and since PostgreSQL will allow the use of functions with side effects or data-modifying statements in CTEs, it must fence such CTEs. I guess that means it'd be free to inline CTEs that call only STABLE or IMMUTABLE functions. –  Craig Ringer Oct 31 '12 at 2:15
    
I think writable CTEs are not yet in any SQL standards, but I'm not positive. –  Mike Sherrill 'Cat Recall' Oct 31 '12 at 2:21
    
wCTEs are certainly a PostgreSQL extension. It's less clear if functions that execute DML are, since user defined functions in SQL are SQL/PSM which Pg doesn't support at all... –  Craig Ringer Oct 31 '12 at 2:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.