User: Password:
Subscribe / Log in / New account

PostgreSQL and the SQL standards process

PostgreSQL and the SQL standards process

Posted Sep 21, 2011 23:59 UTC (Wed) by Cyberax (✭ supporter ✭, #52523)
In reply to: PostgreSQL and the SQL standards process by robert_s
Parent article: PostgreSQL and the SQL standards process

The problem is, you need DB engine to see the 'whole' query to do some optimizations. For example if you do JOINs in SQL as for-loops over the tables inside your application, then DB engine wouldn't be able to use indexes to optimize them.

That's why a separate fully-analyzable language is needed. FLWOR is a nice example because it's functional and fully reference-transparent. So optimization engine can backtrack through definitions and get the complete picture of what's happening. And then use tried-and-true join optimizations.

(Log in to post comments)

PostgreSQL and the SQL standards process

Posted Sep 22, 2011 19:43 UTC (Thu) by iabervon (subscriber, #722) [Link]

You do need some representation of the complete query you are performing. The question is actually: is there any benefit to having database operations go through a representation which is a standardized character sequence? Generally, both applications and databases have some sort of data structure representing a query, and the application flattens this into a character sequence which the database has to read in order to construct approximately the same data structure in order to analyze it. If the query API were specified in terms of a data structure instead of a character sequence, it would eliminate an awkward and error-prone step on each side.

PostgreSQL and the SQL standards process

Posted Sep 22, 2011 20:08 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link]

Doesn't really matter much for me.

Explicit query-construction API should express roughly the same ideas as the textual query representation.

PostgreSQL and the SQL standards process

Posted Sep 22, 2011 20:14 UTC (Thu) by dlang (subscriber, #313) [Link]

you would not eliminate the conversion step, you would just replace the conversion between an text string and the internal data structure with the conversion between the binary sequence and the internal data structure.

different languages (and for that matter, different libraries for a single language) will want to represent the data in different ways.

it would be good to have a standard format that could eliminate the SQL injection type of vulnerability, but it's hard to think of anything that can take an arbitrary byte stream and use it in an arbitrary place in the statement, while still allowing multiple statements in one blob that would eliminate the problem

PostgreSQL and the SQL standards process

Posted Sep 30, 2011 18:43 UTC (Fri) by Baylink (guest, #755) [Link]

You're effectively asking: "should queries be one string of text, instead of a whole sequence of API calls". (Unless I've misunderstood you badly, in which case, I apologize.)

The answer is "you've never been 400ms away from your database engine, have you"?

Yes, the architecture of SQL is the way it is for a reason. You young whippersnappers (:-) have never run a database engine with an API interface across even a LAN, much less a WAN, so you forget both halves of the latency term of the equation, much less the data reduction of having *all* the intermediate work happen inside the server.

Copyright © 2018, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds