Francisco Figueiredo Jr Activities - Seite 2 
SSLStream support added to Npgsql!
Yesterday I merged a pull request which adds support to .Net SSLStream in Npgsql. When Npgsql development started, in 2002, there was no SSL support in .net 1.0 and 1.1. It was added later in .net 2.0 but the support to SSLStream was never added to Npgsql. This all changed a few weeks ago when Dave G created a patch which adds support to .Net SSLStream in Npgsql. He also said that one of the motivations was the fact that the current SSL support was returning error when using client certificate authentication. (I'll post a tutorial about how to use client certificates later) There is a very old feature request (like since 2005!) to somehow create a single Npgsql.dll which incorporates Mono.Security.dll assembly. This would easy the deployment of Npgsql and version control. This feature request generated a discussion about the removal of Mono.Security dependency and possible impact in current user code. In order to get the lowest impact by this change, instead of immediately remove the callbacks and break a lot of user code, I asked Dave to keep the callbacks and mark them obsolete. This way, users will be warned about those callbacks instructed to use the SSLStream RemoteCertificateValidationCallback instead. In order to use the new SSL code, you just need to add a callback to ValidateRemoteCertificateCallback. It can be as simple as: NpgsqlConnection conn = new NpgsqlConnection(CONNSTRING); conn.ValidateRemoteCertificateCallback += (a, b, c) => { return true; }; This callback simply returns true indicating you are accepting the server certificate. Obviously, returning true without doing any validation should be done for testing purposes only. Please, give it a try and let me know if you have any problems.
Today we released the first beta of Npgsql 2.0.13! This new beta release had a lot of bugs fixed and initial support for Entity Framework 6! More information about how to use Entity framework 6 with Npgsql can be found in this post. Checkout the release notes for more information about the bugs fixed in this release. Download it from our downloads section. Important notice about this release Unfortunately, I made a mistake when updating the assembly version for this release and it was created with a wrong value. I'm very sorry for that. This release should have been 2.0.12.91 and not 2.0.13.91 Next beta release will have the version value fixed. What are the implications? The biggest problem is that this beta version will have a version number higher than the final 2.0.13 version while this beta version has 2.0.13.91. As it is a beta and it is not supposed to be deployed in production systems, we think this won't give problems to our users. We are very sorry for any problem this may have caused. How is Npgsql assembly versioned? In the beginning, we used to use the same assembly version througout all the beta release cycle until the final release. With this approach, Npgsql 2.0.10 beta1 would have the assembly version of 2.0.10.0 and beta2 would also have the assembly version of 2.0.10.0. This was creating confusion between users because they couldn't differentiate which version they were working with. Starting with 2.0.11, we changed this in order to identify each version. We started to use the last two parts of the assembly version in order to identify beta releases. This way, a beta release was identified in the following form: Npgsql 2.0.11 beta1 would be identified as: 2.0.10.91; Npgsql 2.0.11 beta2 would be identified as 2.0.10.92 and so on. With this schema, the beta versions will be incremented towards the final version 2.0.11.0. Regarding this 2.0.13 beta1 release, its assembly version should have been 2.0.12.91 and not 2.0.13.91. The former value indicates the assembly is approaching the 2.0.13 while the latter indicates the assembly is approaching 2.0.14 which is not the case. We hope this won't happen again. :(
Performance improvements when creating NpgsqlConnection objects
Recently, I applied a patch from Kevin Pullin which will improve the performance of programs using Npgsql. This patch reduces significantly the time to create new NpgsqlConnection objects. This particularly applies in scenarios where you are creating and disposing a lot of NpgsqlConnection objects, like when you are using connection pool, ( you are using it, right? :) ). Comparison test I made an artificial test to show the impact of this patch. This test consists of a simple loop where I create 10k NpgsqlConnection objects. class Program { static void Main(string[] args) { var connString = "server=127.0.0.1;userid=npgsql_tests;database=npgsql_tests;"; Stopwatch sw = Stopwatch.StartNew(); for (int i = 0; i < 10000; i++) { var conn = new NpgsqlConnection(connString); } sw.Stop(); Console.WriteLine(sw.ElapsedMilliseconds); } } These are the results Code without patch: 1st run: 1316 ms 2nd run: 1333 ms 3rd run: 1310 ms Average time: 1319 ms Code with patch: 1st run: 33 ms 2nd run: 39 ms 3rd run: 33 ms Average time: 35 ms The new code, on this test, was more than 30 times faster! Of course this doesn't mean your code will be 30 times faster, after all your code doesn't consist of only creating NpgsqlConnection objects, but imagine a high traffic server which receives a lot of requests. When you sum up all the time spent creating NpgsqlConnection objects, this performance gain would make a difference. Please, give it a try and let me know how it works for you. Just go to Npgsql git page and press the "Download ZIP" button and get a snapshot of the code. Open the Npgsql2010.sln solution file, build and test! Again, thank you very much, Kevin, for your patch!
Initial EF-6 support added to Npgsql
In my last post, I said there is a pull request by Pēteris Ņikiforovs to add support for EF-6 to Npgsql. Yesterday, I merged this pull request to the master branch of Npgsql. With this merge, Npgsql has officially initial support for EF-6! How to compile For now, in order to compile Npgsql to use EF-6, you have to open the solution file NpgsqlEF6.sln. Later, as suggested by Pēteris Ņikiforovs, the idea is that we create a new configuration inside main project solution instead of maintain 2 separated projects. Another thing you will need to compile Npgsql is the latest release of EntityFramework assembly through NuGet: PM> Install-Package EntityFramework -Pre That's it! Now you will be able to play with Npgsql and EF-6. Check out my previous post about how to use Npgsql with EntityFramework. I'd like to thank Pēteris Ņikiforovs for his patch. And maxbundchen for his patch about Open/Close events needed for EF-6. Please, give it a try and let me know how it works for you.
Npgsql Code First Entity Framework 4.3.1 Sample
After reading the excellent article about entity framework on Postgresql by Brice Lambson, I decided to write this post to document my experience playing with Entity Framework 4.3.1 and Npgsql. This post will be an adaptation of the Code First To a New Database walkthrough in order to make it work with Npgsql. First Steps You should follow the first 4 steps of Code First To a New Database. Go ahead, I''l wait for you. Next steps Here is where the adaptation of the walkthrough begins. As Brice noted in his post, Npgsql currently doesn't support database creation. ( I'm working on that and hope to get news about it soon.) So, for while, you have to create the database manually. Those are the steps you have to do to create the database and the model: First, run this command in the terminal to create the database (or you can use pgAdmin if you prefer a GUI): > createdb ef_code_first_sample After that, you have to run the following commands inside the database you have just created (to simplify permissions, remember to run this script connected as the same user who is specified in your connection string): create table "Blog" ("BlogId" serial, "Name" varchar(255)); create table "Post" ("PostId" serial, "Title" varchar(255), "Content" varchar(8000), "BlogId" int); And here comes the first trick you have to use when working with EF and Npgsql: the table names as well as column names need to be double quoted. Entity Framework generates code with table and column names double quoted and, to Postgresql, using double quotes means you want to preserve the casing of the names. So you need to create the tables with the correct case or else, Postgresql will complain it can't find your tables. With the database and tables created, let's make some more configuration before we can run the sample. Entity Framework installation Unfortunately Npgsql doesn't support EF 5. Currently it supports 4.3.1 and there is a pull request by Pēteris Ņikiforovs to add support for EF 6. Yeah, Npgsql will have support for latest EF version soon! You will need to install the 4.3.1 version of EF. According to EF Nuget project page, this is done with the following command: PM> Install-Package EntityFramework -Version 4.3.1 This is needed because if you don't specify the 4.3.1 version, Nuget will install the latest version which isn't supported by Npgsql yet. Npgsql installation If you don't have Npgsql installed already, you should install Npgsql from Nuget too: PM> Install-Package Npgsql And then you should configure Npgsql in your App.config to be found by the DbProviderFactory API: <system.data> <DbProviderFactories> <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" /> </DbProviderFactories> </system.data> and configure your connection string in the same App.config file: <connectionStrings> <add name="BloggingContext" providerName="Npgsql" connectionString="server=10.0.2.2;userid=npgsql_tests;password=npgsql_tests;database=ef_code_first_sample"/> </connectionStrings> Running the code Now it's time to run the code. If you have everything configured and hit Ctrl+F5 you should get the code running and will be greeted with the Blog name question. Unfortunately after answering the question and pressing enter, an exception will be thrown: Unhandled Exception: System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. --- > System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 3F000: schema "dbo" does not exist This error occurs because by default, Entity Framework uses the schema dbo and Postgresql uses the schema public. This is the message you get in the Postgresql log: INSERT INTO "dbo"."Blogs"("Name") VALUES (E'test');SELECT currval(pg_get_serial_sequence('"dbo"."Blogs"', 'BlogId')) AS "BlogId"ERROR: schema "dbo" does not exist at character 13 As pointed out by Brice in his answer to a user question about this error, you have to tell Entity Framework to use a different schema. This is done by using Data Annotations and adding a Table attribute to the classes of the model: [Table("Blog", Schema = "public")] public class Blog and [Table("Post", Schema = "public")] public class Post To use those attributes, you have to import the Data Annotations namespace: using System.ComponentModel.DataAnnotations; More information about that can be found in the Code First To a New Database article linked at the beginning of this post. That's it! After making those changes, you should now get the data correctly inserted in the database and everything should work ok: LOG: statement: INSERT INTO "public"."Blog"("Name") VALUES (E'test');SELECT currval(pg_get_serial_sequence('"public"."Blog"', 'BlogId')) AS "BlogId" I hope you enjoyed this post and could get started to Entity Framework and Npgsql. Please, let me know what you think in your comments. I'd like to thank Brice Lambson for his excellent article and the Microsoft Entity Framework team for their Code First To a New Database walkthrough and all the EF stuff.
Npgsql code moved to GitHub!
Since the beginning, in 2002, Npgsql has been using cvs as its source code management system (SCM). At the time, cvs was being used by a lot of opensource projects, and so it was a natural choice for Npgsql. A couple of days ago, Npgsql code moved to GitHub. I didn't blog about that in the same day because I wanted to make sure things went well before spreading the word. :) ( You may be asking yourself: But Npgsql code was already at GitHub, wasn't it? Yes, it was. But it was only a mirror of the main cvs repository. And I had to update the code manually every time. Every change had to go first to the main repository cvs and then I would update in GitHub. Obviously the code was often outdated. This is a thing of the past now.) Git was chosen mainly because there is a lot of documentation about it, it is powerful and because of GitHub. GitHub provides many resources which will help us have a much better environment for our collaborators and users. For our collaborators there is the idea of cloned repositories and the ability to give us a much better feedback based on pull requests. We will be able to much easily apply changes from our collaborators based on those pull requests. Besides that, our collaborators will be able to use all the power of git to get a better workflow when playing with Npgsql code. Users will also get a lot of benefits. GitHub provides links to download code from each branch. This will allow users who want to test an unreleased version of Npgsql to try out new features without having to either install git or wait for us to create a beta version. I'm very confident this change will bring a lot of benefits to Npgsql community. I hope you enjoy this change as much as I do. I'd like to thank a person who contributed a lot to make this happen. I'm sure that, without his help, this migration would have taken a lot more time: Shay Rojansky. Shay has been helping me since the beginning and gave me all the support I needed to get Npgsql code into GitHub. Thank you very much, Shay! Also, I'd like to thank the persons behind the excellent script cvs2git. This script did all the hard work of converting the code from cvs to git. Thank you guys! And last but not the least, I'd like to thank people from pgfoundry where Npgsql had its code hosted until now. Thank you for all the fish. Npgsql can be found on GitHub at the following addresses: http://git.npgsql.org or https://github.com/franciscojunior/Npgsql2 Please, give it a try and let me know if you find any problems.
Better tracing of Npgsql connection pool usage in the works
Sometimes, Npgsql users receive errors when working with connection pooling. The problem appears when they try to open a new connection and receive the following message: "Timeout while getting a connection from pool." This is caused when an attempt is made to get a connection from a pool which has all its connections used. Most of the time this problem is a difficult one to track because it generally happens when the system is in production and not in the development phase. Of course. :) Some time ago, I received a report from Miłosz Kubański about such a problem. I told him I would work in a way to get more information so we could check what was happening. In order to help us find the problem, I added a little "hack" to Npgsql: Whenever an error while getting a connection from the pool occurs, Npgsql would log a stacktrace of the allocation of all the connections which were in the pool. Theoretically, those connections which were allocated should not be retained and by taking a "snapshot" of those allocations, we could get some tips about which code allocated the connection and check for potential missing releases. Last week I sent Milosz this modified version of Npgsql so he could give it a try and send me back the log. Yesterday he sent me the log. Inside it, I chose two stacktraces and asked him if would be possible there was any missing close. Milosz replied saying there was indeed a missing close call. It was an exception inside a very complex system. Based on this feedback, I think this "hack" has shown to be very useful in future cases and I'll work to get this change in a "non-hack" status and add it to Npgsql code. I hope this helps Npgsql users to find possible causes for connection pooling problems. When this feature is available I'll let you know.
Where is vs.net design time support?
You may already know that VS.Net design time support has started a long time ago and didn't have too much support since then. Now that Npgsql release 2.0.12 is out, I want to put more attention to finish a version which adds design time support. I noticed that this is the biggest missing feature in Npgsql and I want to fix that. Npgsql users deserve to be able to use VS.Net design time support to help them create better apps which access Postgresql databases. Although I can't give any concrete timeframe of when it will be available, I want you to know that I'm focused on this feature and it is not stalled. I hope to be able to give you more information soon. Stay tuned.
ConnectionPool performance improvements
Hi, all! Today I committed a change to Npgsql which will improve connection pool performance. This change was motivated by Andrew's bug report where he noticed that a lot of threads were waiting to get a new connection from pool. In order to keep consistence of the pool, Npgsql has to lock access to it. Andrew's problem appeared in a busy server where a lot of threads were trying to get a new connection from the pool. They had to wait in line. And obviously this isn't good. The current implementation of Npgsql creates a big lock surrounding all the code needed to work with the pool and more! As Andrew noticed in his bug report, I/O operations were being done inside this lock which was contributing to more delays to get a connection from the pool. So, to fix that, I rewrote connection pool logic to remove this big lock and break it down to smaller ones only when really needed. All the I/O operations were left out of the locks, this way, other threads waiting to get a new connection from the pool don't need to wait for those expensive operations to finish. I made a lot of tests and could confirm that when I break the code inside the debugger, threads are spread throughout connection pool code as expected instead of waiting in line on the big lock. As this change is somewhat critical to Npgsql usage, I'd like to ask you to download the code, compile it and give it a try and see if everything is working ok or even better than before. I expect busy servers to be able to increase their raw throughput because it will have to wait less to get connections from the pool. As always, please, let me know if you have any problems and all feedback is very welcome!
Npgsql2 source code mirror is now available on github
Hello, all! Npgsql2 source code hosted on pgfoundry is now mirrored on github: https://github.com/franciscojunior/Npgsql2 What does this mean to developers and users? You have another way of access to latest Npgsql2 code through github interface I hope it will be easier to developers and users to provide patches. You can make forks and pull requests. Please, if you weren't being able to access Npgsql2 source code because of cvs, please give it a try on this new interface and let me know if you have any problems. Thanks github for providing this service to open source community.