Wednesday, October 16, 2013
Tuesday, October 15, 2013
Use IMMUTABLE on PostgreSQL function to avoid optimization fence
I thought this is the PostgreSQL equivalent of SQL Server's efficient inline table-valued function:
Using that function on this expression..
To achieve the same optimized execution plan of SQL Server on PostgreSQL, must add IMMUTABLE on the function:
Happy Coding!
create or replace function get_orgs() returns table ( ID int, Favorite int ) as $$ select p.person_id, eorg.ounits from person p join lateral ( select case when p.person_id = 4 then p.person_id / 0 else 7 end ) eorg(ounits) on true $$ language sql;
Using that function on this expression..
select p.*, o.* from person p join get_orgs() o on p.person_id = o.ID where p.person_id < 4..produces a divide-by-zero error on PostgreSQL, while SQL Server does not materializes the rows that are greater than or equal to 4, hence this doesn't produce an error on SQL Server:
ERROR: division by zero CONTEXT: SQL function "get_orgs" statement 1 ********** Error ********** ERROR: division by zero SQL state: 22012 Context: SQL function "get_orgs" statement 1
To achieve the same optimized execution plan of SQL Server on PostgreSQL, must add IMMUTABLE on the function:
create or replace function get_orgs() returns table ( ID int, Favorite int ) as $$ select person_id, eorg.ounits from person p join lateral ( select case when p.person_id = 4 then p.person_id / 0 else 7 end ) eorg(ounits) on true $$ language sql IMMUTABLE;When that function is used on the the query above, it doesn't yield divide-by-zero anymore, it's now efficient, it doesn't eager load the function's result, it's like the function is inlined on the query itself, the query that joins to the IMMUTABLE function behaves like a table-deriving query, very efficient, no divide-by-zero will occur, i.e., the RDBMS just expand the function's query to other query, divide-by-zero error won't happen, to wit:
select p.*, o.* from person p join ( select p.person_id as ID, eorg.ounits from person p join lateral ( select case when p.person_id = 4 then p.person_id / 0 else 7 end ) eorg(ounits) on true ) o on p.person_id = o.ID where p.person_id < 4;Produces this output, has no divide by zero error:
person_id | lastname | firstname | nickname | favorite_number | id | favorite -----------+-----------+--------------+----------+-----------------+----+---------- 2 | mccartney | james paul | paul | | 2 | 7 3 | harrison | george | | | 3 | 7 1 | lemon | john winston | john | | 1 | 7 (3 rows)However, PostgreSQL, unlike SQL Server, has an optimization fence on its CTE, i.e., PostgreSQL eagerly loads the result of the CTE. SQL Server's CTE is more efficient than PostgreSQL's CTE on this regard:
with o as ( select p.person_id as ID, eorg.ounits from person p join lateral ( select case when p.person_id = 4 then p.person_id / 0 else 7 end ) eorg(ounits) on true ) select p.*, o.* from person p join o on p.person_id = o.ID where p.person_id < 4;That CTE yields the following error on Postgresql, a proof that Postgresql eagerly load the result of the CTE; while on SQL Server it has no error, SQL Server doesn't eagerly load the result of CTE, a proof that SQL Server sees CTE as subject to execution plan rather than an eager-loading mechanism to fetch the results. Eagerly-loaded results has no chance to be optimized, as it looks like a blackbox from the caller, the caller can't optimize what's inside
ERROR: division by zero ********** Error ********** ERROR: division by zero SQL state: 22012If we will follow C language principle design, I wishes PostgreSQL to use an existing keyword to indicate we don't want an optimization fence on a CTE:
with o immutable as ( select p.person_id as ID, eorg.ounits from person p join lateral ( select case when p.person_id = 4 then p.person_id / 0 else 7 end ) eorg(ounits) on true ) select p.*, o.* from person p join o on p.person_id = o.ID where p.person_id < 4;If a CTE's query can be re-used, the query is better be made as view instead, a query inside a view don't have optimization fence too, i.e., it also behaves as table-deriving query, view's query just get expanded to other query, this won't produce divide-by-zero error:
create or replace view vw_get_orgs as select person_id as ID, eorg.ounits from person p join lateral ( select case when p.person_id = 4 then person_id / 0 else 7 end ) eorg(ounits) on true; select p.*, o.* from person p join vw_get_orgs o on p.person_id = o.ID where p.person_id < 4;
Happy Coding!
Very efficient JSON in PostgreSQL
From this old approach:
To this new one:
select array_to_json(array_agg(row_to_json(t))) from (select firstname, lastname, favorite_number from person) t;
To this new one:
select json_agg(t) from (select firstname, lastname, favorite_number from person) t;
Sunday, October 13, 2013
Sometimes a blog just need to be a web log. Sparrow Framework Day 1
Day 1
Sparrow Framework web log, I'm here now: http://wiki.sparrow-framework.org/manual/textures_and_images
Will keep you posted if I learned some cool tricks not found on the documentation ツ
Sparrow Framework web log, I'm here now: http://wiki.sparrow-framework.org/manual/textures_and_images
Will keep you posted if I learned some cool tricks not found on the documentation ツ
Sunday, October 6, 2013
Bye bye C#, PHP, Java, Ruby. You are so long, and thanks for all the fish*
I told my friend(whose career is built around PHP) about AngularJS, like everybody else who discovered the joys of using AngularJS, he's very excited learning something revolutionary. He shared that one time he's learning AngularJS up to 3 AM.
What he likes in AngularJS is the PHP's role in a web application development is taking a backseat, and the realization that he doesn't even need PHP at all, any server-side languages that can serve JSON would do, be it from C#, Java, Ruby and whathaveyou. He also don't like it that PHP and HTML are being interwoven too much. I don't know if Mark Zuckerberg shares the same sentiment.
One thing is for sure though, given that interweaving PHP and HTML is unavoidable, the integration should feel natural and make the integration cross-site-scripting-attack-proof. Facebook took a page from ASP.NET MVC Razor capabilities on this matter, Facebok created XHP. With XHP, they made interweaving PHP and HTML very seamless, PHP's output is now automatically HTML-escaped too, cross-site-scripting attacks can be avoided. Of course, I'm just massaging the ego of ASP.NET MVC programmers here, open-source folks doesn't need our innovations :-) Seamless integration between programming language and markup language is something everyone are pining for ever since everyone moved to web for rapid application development. I digress.
It's not surprising that AngularJS is the easiest to use JavaScript MVC framework, given that Misko Hevery created AngularJS as an easy-to-use framework for his JSON service business, which is priced by the megabyte. JSON service is a bad kind of business though, if they really wanted to earn well they should serve XML instead of JSON :D
While everyone is abuzz and getting nerdgasmic with the release of iPhone 5S last September 10, I'm eagerly anticipating and excited on the release of PostgreSQL 9.3 last September 9. JSON in PostgreSQL is becoming more and more a first-class language construct, API-wise and syntax-wise.
As for the reason why the excitement and how it relates to AngularJS, let's get to the core principle of AngularJS:
While my friend is happy with lesser role of PHP in a web application development using AngularJS, any server-side languages for that matter, I'm stoked at the idea that we don't even need server-side languages at all. If only RDBMSes can expose RESTful services the way ServiceStack, ASP.NET Web API, WCF, JAX-RS and Recess Framework can do, the days of server-side languages would be numbered. Bye bye C#, PHP, Java, Ruby, you are so long, and thanks for all the fish.
RDBMS RESTful? I'm fancying that. But before we get there, let's see how easy it is to make web applications with AngularJS and the supercharged PostgreSQL JSON functionalities.
Here's how easy it is to serve JSON with PostgreSQL:
Very easy.
The plus signs doesn't turn up in actual JSON result, it's just presented that way by PostgreSQL's commandline tool. The type returned is not a string, PostgreSQL has a JSON datatype. Being a first-class data type that it is, you can manipulate it further with PostgreSQL's superb JSON API and operators.
Returning a JSON paged result(along with total records count) with the latest PostgreSQL:
Sample result:
Here's how to extract the values from JSON:
Now, how about our PHP web developers? What is now the use of their decades-long experience on PHP? Following code is the thinnest and fastest marshaller of data to your web app, courtesy of PostgreSQL's superb JSON functionality
Serving paged results:
Serving persistence requests:
For our web designer bretheren, they would be delighted that there's no more server-side languages that gets in the way between them and their beloved HTML and CSS, they could change the tags and css to their heart's content as long they keep in mind some basic HTML principles like ng-repeat and moustache tags. With AngularJS, web designer could now work in parallel and independently from web developers.
Customers.html
CustomersForm.html
Here's the AngularJS code, the glue framework. This is now where the battle-scarred web application developers need to turn their discipline to:
* The humor of the phrase is not lost on me. The phrase was alluded to what the dolphin said on Hitchhiker's Guide To The Galaxy. Dolphin, being a MySQL mascot, could have titled this blog post as "So long, and thanks for all the dolphins" lol
Happy Coding! ツ
What he likes in AngularJS is the PHP's role in a web application development is taking a backseat, and the realization that he doesn't even need PHP at all, any server-side languages that can serve JSON would do, be it from C#, Java, Ruby and whathaveyou. He also don't like it that PHP and HTML are being interwoven too much. I don't know if Mark Zuckerberg shares the same sentiment.
One thing is for sure though, given that interweaving PHP and HTML is unavoidable, the integration should feel natural and make the integration cross-site-scripting-attack-proof. Facebook took a page from ASP.NET MVC Razor capabilities on this matter, Facebok created XHP. With XHP, they made interweaving PHP and HTML very seamless, PHP's output is now automatically HTML-escaped too, cross-site-scripting attacks can be avoided. Of course, I'm just massaging the ego of ASP.NET MVC programmers here, open-source folks doesn't need our innovations :-) Seamless integration between programming language and markup language is something everyone are pining for ever since everyone moved to web for rapid application development. I digress.
It's not surprising that AngularJS is the easiest to use JavaScript MVC framework, given that Misko Hevery created AngularJS as an easy-to-use framework for his JSON service business, which is priced by the megabyte. JSON service is a bad kind of business though, if they really wanted to earn well they should serve XML instead of JSON :D
While everyone is abuzz and getting nerdgasmic with the release of iPhone 5S last September 10, I'm eagerly anticipating and excited on the release of PostgreSQL 9.3 last September 9. JSON in PostgreSQL is becoming more and more a first-class language construct, API-wise and syntax-wise.
As for the reason why the excitement and how it relates to AngularJS, let's get to the core principle of AngularJS:
Every web-application is really just once complex marshaling problem. We marshal data from the DB, through the HTTP into the heap and to the DOM for the user to read, and then we do it all again in reverse order. At first this is fun, but after you build few applications, you very quickly get tired of marshaling your data through these layers, and I wanted to do something about it. -- Misko Hevery
While my friend is happy with lesser role of PHP in a web application development using AngularJS, any server-side languages for that matter, I'm stoked at the idea that we don't even need server-side languages at all. If only RDBMSes can expose RESTful services the way ServiceStack, ASP.NET Web API, WCF, JAX-RS and Recess Framework can do, the days of server-side languages would be numbered. Bye bye C#, PHP, Java, Ruby, you are so long, and thanks for all the fish.
RDBMS RESTful? I'm fancying that. But before we get there, let's see how easy it is to make web applications with AngularJS and the supercharged PostgreSQL JSON functionalities.
Here's how easy it is to serve JSON with PostgreSQL:
northwind=# with company_summary_info as ( select c."CustomerID", c."CompanyName", c."ContactName" from customers c where c."ContactName" like 'A%' ) select json_agg(i.*) from company_summary_info i; json_agg ----------------------------------------------------------------------------------------------------------- [{"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactName":"Ana Trujillo"}, + {"CustomerID":"ANTON","CompanyName":"Antonio Moreno Taquería","ContactName":"Antonio Moreno"}, + {"CustomerID":"EASTC","CompanyName":"Eastern Connection","ContactName":"Ann Devon"}, + {"CustomerID":"FAMIA","CompanyName":"Familia Arquibaldo","ContactName":"Aria Cruz"}, + {"CustomerID":"GOURL","CompanyName":"Gourmet Lanchonetes","ContactName":"André Fonseca"}, + {"CustomerID":"LAMAI","CompanyName":"La maison d'Asie","ContactName":"Annette Roulet"}, + {"CustomerID":"MORGK","CompanyName":"Morgenstern Gesundkost","ContactName":"Alexander Feuer"}, + {"CustomerID":"ROMEY","CompanyName":"Romero y tomillo","ContactName":"Alejandra Camino"}, + {"CustomerID":"SPLIR","CompanyName":"Split Rail Beer & Ale","ContactName":"Art Braunschweiger"}, + {"CustomerID":"TRADH","CompanyName":"Tradição Hipermercados","ContactName":"Anabela Domingues"}] (1 row)
Very easy.
The plus signs doesn't turn up in actual JSON result, it's just presented that way by PostgreSQL's commandline tool. The type returned is not a string, PostgreSQL has a JSON datatype. Being a first-class data type that it is, you can manipulate it further with PostgreSQL's superb JSON API and operators.
Returning a JSON paged result(along with total records count) with the latest PostgreSQL:
create or replace function get_customers(p_pageNumber int, p_pageSize int) returns json as $$ begin return ( with src as ( select c."CustomerID", c."CompanyName", c."ContactTitle", c."ContactName", c."City" from customers c order by c."CompanyName" ), paged as ( select * from src offset ((p_pageNumber - 1) * p_pageSize) limit p_pageSize ), returnedJson as ( select (select json_agg(p.*) from paged p) as list, (select count(*) from src) as count ) select row_to_json(r.*) from returnedJson r ); end; $$ language 'plpgsql';
Sample result:
northwind=# select get_customers(1,10); get_customers --------------------------------------------------------------------------------------------------------------------------------------------------------------------- {"list":[{"CustomerID":"ALFKI","CompanyName":"Alfreds Futterkiste","ContactTitle":"Sales Representative","ContactName":"Maria Anders","City":"Berlin"}, + {"CustomerID":"ANATR","CompanyName":"Ana Trujillo Emparedados y helados","ContactTitle":"Owner","ContactName":"Ana Trujillo","City":"México D.F."}, + {"CustomerID":"ANTON","CompanyName":"Antonio Moreno Taquería","ContactTitle":"Owner","ContactName":"Antonio Moreno","City":"México D.F."}, + {"CustomerID":"AROUT","CompanyName":"Around the Horn","ContactTitle":"Sales Representative","ContactName":"Thomas Hardy","City":"London"}, + {"CustomerID":"BSBEV","CompanyName":"B's Beverages","ContactTitle":"Sales Representative","ContactName":"Victoria Ashworth","City":"London"}, + {"CustomerID":"BERGS","CompanyName":"Berglunds snabbköp","ContactTitle":"Order Administrator","ContactName":"Christina Berglund","City":"Luleå"}, + {"CustomerID":"BLAUS","CompanyName":"Blauer See Delikatessen","ContactTitle":"Sales Representative","ContactName":"Hanna Moos","City":"Mannheim"}, + {"CustomerID":"BLONP","CompanyName":"Blondesddsl père et fils","ContactTitle":"Marketing Manager","ContactName":"Frédérique Citeaux","City":"Strasbourg"}, + {"CustomerID":"BONAP","CompanyName":"Bon app'","ContactTitle":"Owner","ContactName":"Laurence Lebihan","City":"Marseille"}, + {"CustomerID":"BOTTM","CompanyName":"Bottom-Dollar Markets","ContactTitle":"Accounting Manager","ContactName":"Elizabeth Lincoln","City":"Tsawassen"}],"count":91} (1 row)
Here's how to extract the values from JSON:
create or replace function save_customer(p_customer json) returns json as $$ begin if not exists( select * from customers where "CustomerID" = p_customer->>'CustomerID') then insert into customers ("CustomerID","CompanyName","ContactName","ContactTitle","City", "Region", "PostalCode", "Country", "Phone", "Fax" ) select p_customer->>'CustomerID', p_customer->>'CompanyName', p_customer->>'ContactName', p_customer->>'ContactTitle', p_customer->>'City', '', -- Region '', -- PostalCode '', -- Country '', -- Phone ''; -- Fax else update Customers set "CompanyName" = p_customer->>'CompanyName', "ContactName" = p_customer->>'ContactName', "ContactTitle" = p_customer->>'ContactTitle', "City" = p_customer->>'City' where "CustomerID" = p_customer->>'CustomerID'; end if; return (with src as ( select c."CustomerID", c."CompanyName", c."ContactTitle", c."ContactName", c."City" from customers c where c."CustomerID" = p_customer->>'CustomerID' order by c."CompanyName" ) select row_to_json(c.*) from src c); end; $$ language 'plpgsql';
Now, how about our PHP web developers? What is now the use of their decades-long experience on PHP? Following code is the thinnest and fastest marshaller of data to your web app, courtesy of PostgreSQL's superb JSON functionality
Serving paged results:
<?php header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With'); header("Access-Control-Allow-Origin: *"); $pageNumber = $_GET["PageNumber"]; $pageSize = $_GET["PageSize"]; $con = pg_connect("host=localhost port=5433 dbname=northwind user=postgres password=opensesame93"); $res = pg_query_params($con, "select get_customers($1,$2)", array($pageNumber, $pageSize)); if ($res) { $row = pg_fetch_row($res); echo $row[0]; } else { echo "Error"; } ?>
Serving persistence requests:
<?php header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With'); header("Access-Control-Allow-Origin: *"); $con = pg_connect("host=localhost port=5433 dbname=northwind user=postgres password=opensesame93"); $data = file_get_contents("php://input"); /* $getData = json_decode($data); $CompanyName = $getData->CompanyName; */ $res = pg_query_params($con, "select save_customer($1)", array($data)); if ($res) { $row = pg_fetch_row($res); echo $row[0]; } else { echo "Error"; } ?>
For our web designer bretheren, they would be delighted that there's no more server-side languages that gets in the way between them and their beloved HTML and CSS, they could change the tags and css to their heart's content as long they keep in mind some basic HTML principles like ng-repeat and moustache tags. With AngularJS, web designer could now work in parallel and independently from web developers.
Customers.html
<!DOCTYPE html> <html ng-app="sampleApp"> <head> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link rel="stylesheet" href="angularjs-twitter-bootstrap/assets/bootstrap.css"/> <script src="angularjs/angular.min.js"></script> <script src="angularjs-twitter-bootstrap/ui-bootstrap-0.6.0.min.js"></script> <script src="app.js"></script> <title></title> </head> <body ng-controller="CustomersCtrl"> <p>PostgreSQL loved by DBAs and Application Developers alike</p> <div class="form-inline"> <div class="btn-group"> <button type="button" class="btn btn-default btn-sm" ng-click="prevPage()" ng-disabled="!(pageNumber > 1)">Previous</button> <button type="button" class="btn btn-default btn-sm" ng-click="nextPage()" ng-disabled="isLastPage()">Next</button> </div> <input type="number" ng-model="pageNumber" ng-change="getCustomers()" style="width:50px"> <select ng-model="pageSize" ng-options="opt for opt in pageSizeOptions" ng-change="getCustomers()"></select> </div> <p></p> <div class="panel panel-default"> <div class="panel-heading">Customers: {{customers.count}}</div> <table class="table"> <thead> <th> <i class="icon-large icon-plus-sign" style="width: 20px"></i> </th> <th>Company</th> <th>Contact Title</th> <th>Contact</th> <th>City</th> </thead> <tbody> <tr ng-repeat="c in customers.list"> <td ng-click="showForm(c)" style="cursor: pointer;"> <i class="icon-large icon-pencil" style="width: 20px"> </i> </td> <td>{{c.CompanyName}}</td> <td>{{c.ContactTitle}}</td> <td>{{c.ContactName}}</td> <td>{{c.City}}</td> </tr> </tbody> </table> </div> </body> </html>
CustomersForm.html
<div class="modal-header"> <h3>{{Customer.CustomerID}}</h3> </div> <div class="modal-body"> <div class="form-inline"> Company: <input type="text" ng-model="Customer.CompanyName"> </div> </div> <div class="modal-footer"> <button class="btn btn-primary" ng-click="ok()">OK</button> <button class="btn btn-warning" ng-click="cancel()">Cancel</button> </div>
Here's the AngularJS code, the glue framework. This is now where the battle-scarred web application developers need to turn their discipline to:
var theApp = angular.module('sampleApp', ['ui.bootstrap']); theApp .controller('CustomersCtrl',['$scope','$http', '$modal', function ($scope, $http, $modal) { $scope.customers = { count: 0 }; $scope.pageNumber = 1; $scope.pageSize = 10; $scope.pageSizeOptions = [5, 10, 15, 20, 25]; $scope.pageNumber = 1; $scope.getCustomers = function() { var pageNumber = $scope.pageNumber; var pageSize = $scope.pageSize; $http({ url: 'http://192.168.254.100/getCustomers.php', method: 'GET', params: { PageNumber: pageNumber, PageSize : pageSize } }).success(function (data, status, headers, config) { $scope.customers = data; }).error(function(data, status, headers, config) { $scope.message = "error"; }); } $scope.showForm = function(customer) { var modalInstance = $modal.open({ templateUrl: 'CustomersForm.html', controller: 'CustomersFormCtrl', resolve: { Customer: function () { return angular.copy(customer); }, refresher : function() { return $scope.refresh; } } }); }; $scope.refresh = function() { $scope.getCustomers(); }; $scope.nextPage = function() { ++$scope.pageNumber; $scope.getCustomers(); }; $scope.prevPage = function() { if ($scope.pageNumber == 1) return; --$scope.pageNumber; $scope.getCustomers(); }; $scope.isLastPage = function() { return Math.ceil($scope.customers.count / $scope.pageSize) == $scope.pageNumber; }; $scope.getCustomers(); }]) .controller('CustomersFormCtrl', ['$scope','$http', '$modalInstance', 'Customer', 'refresher', function ($scope, $http, $modalInstance, Customer, refresher) { $scope.Customer = Customer; $scope.ok = function () { $http({ url: 'http://192.168.254.100/saveCustomer.php', method: 'POST', data: $scope.Customer, headers: {'Content-Type': "application/json"} }).success(function (data, status, headers, config) { $modalInstance.dismiss('ok'); refresher(); }).error(function(data, status, headers, config) { // called asynchronously if an error occurs // or server returns response with an error status. $scope.message = "error"; }); }; $scope.cancel = function () { $modalInstance.dismiss('cancel'); }; }]);
* The humor of the phrase is not lost on me. The phrase was alluded to what the dolphin said on Hitchhiker's Guide To The Galaxy. Dolphin, being a MySQL mascot, could have titled this blog post as "So long, and thanks for all the dolphins" lol
Happy Coding! ツ
Wednesday, October 2, 2013
Access-Control-Allow-Headers error
Should not be done separately:
All access should be included on one setting:
header('Access-Control-Allow-Headers: Content-Type, application/json'); header('Access-Control-Allow-Headers: X-Requested-With');
All access should be included on one setting:
header('Access-Control-Allow-Headers: Content-Type, application/json, X-Requested-With');
Subscribe to:
Posts (Atom)