PetaPoco - PostgreSQL Support and More Improvements
Thursday, 7 April 2011
Today I updated PetaPoco to support PostgreSQL database, tweaked some implementation details to yield better query plan caching and added the ability to query page or records without querying the total record count.
This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.
I've been in conversation with Rob Sullivan of http://datachomp.com who asked about PostgreSQL support in PetaPoco... another database that I've never used. Once again it was understanding the tools that took the longest in getting it to work with PetaPoco.
Once up and running only a few tweaks were needed to PetaPoco itself to get a full set of green lights on the unit tests. The main problem was related to the
Insert method - instead of using
@@IDENTITY, it needed to use
returning <pkcolumn> as NewID. Thanks Rob for tip!
The T4 templates have also been updated for PostgreSQL, mostly this was a straight copy of the SQL Server schema reading with some different data type mapping.
I've also written up some notes on getting the template to work - details like installing providers into the GAC and registering the providers in
machine.config. These notes can be found in the top of the
Parameterizing the Paged Requests
I realized last night that PetaPoco's paged requests weren't using parameters for the offset and limit counts - rather it was using
string.Format to put literal values straight into the SQL. Not a problem for PetaPoco but it means that server side query plans can't be cached and reused. Aparently DBA's call this "plan cache poisoning" - something I learned from Rob's post here.
Simpler Paged Requests
Page requests return not just the page of records but also do a
SELECT COUNT(*) to get the total number of records - so a page count can be calculated and displayed. Sometime though there might be other easier or better ways to calculate the record count, or perhaps it's just not needed.
So I've added an overloaded version of
Fetch that returns a list of records without doing the extra query to get the count.
// Fetch all matching records List<T> Fetch<T>(string sql, params object args); // New fetch to get just a single page of records List<T> Fetch<T>(long page, long itemsPerPage, string sql, params object args); // Fetch a page of records and the total record count Page<T> Page<T>(long page, long itemsPerPage, string sql, params object args);
Scope_Identity() vs @@Identity
This is another point raised by Rob. On SQL Server
@@Identity can return the primary key identity of records inserted by a trigger instead of the actual record being inserted. So, for SQL Server PetaPoco now uses the
Scope_Identity() function to get the correct record ID.
Simplification beats Over-Optimization
In yesterday's somewhat over exuberant optimization I made some changes that I wasn't really happy with. The optimized versions of
FirstOrDefault in particular just added code with such a tiny (if any) performance gain that I've dropped them and reverted to simpler implementations.
All this is available in GitHub and NuGet now.
Finally, special thanks must go out to Rob Sullivan for all his suggestions and feedback.
Leave a comment