Learning PostgreSql: overloading

Functions in PL/PgSql can be overloaded, which is very different from T-SQL.

Demonstrating overloading 

For example, the second CREATE FUNCTION in the following script does not replace the first function - it creates a second one:

CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumber INTEGER)RETURNS VARCHAR AS$body$BEGIN

RETURN 'Say my name.';
END;$body$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumber REAL)RETURNS VARCHAR AS$body$BEGIN

RETURN 'I am the danger.';
END;$body$
LANGUAGE plpgsql;

Let us verify that two functions have been created:

SELECT * FROM public.GetQuoteOfTheDay(1)UNION ALL SELECT * FROM public.GetQuoteOfTheDay(1.2);

The output shows both "Say my name." and "I am the danger.".

This means that function name alone is not enough to uniquely identify a function. As a result, we cannot drop a function unless we provide its signature. The following command fails:

DROP FUNCTION public.GetQuoteOfTheDay;

Once we have provided the function's signature, we can drop it:

DROP FUNCTION public.GetQuoteOfTheDay(INTEGER);

Optional parameters

Optional parameters in PL/PgSql also behave differently from T-SQL. In T-SQL we cannot add an optional parameter to a function without breaking all the code that uses it. Consider, for example, the following T-SQL function:

CREATE FUNCTION dbo.TestOptionalParameters ( @i INT, @optional INT = 0 )RETURNS TABLE
AS RETURN
  ( SELECT  @i AS i ,
            @optional AS Optional
  ) ;GO

On SQL 2008 R2, we cannot invoke it with only one parameter; we must provide both:

-- this worksSELECT * FROM dbo.TestOptionalParameters(1, 2);
-- this does not compileSELECT * FROM dbo.TestOptionalParameters(1);
Msg 313, Level 16, State 3, Line 2
An insufficient number of arguments were supplied for the procedure or function dbo.TestOptionalParameters.

This behavior is both counter-intuitive for C# developers and not consistent with T-SQL stored procedures. The expected way to invoke a T-SQL function with an optional parameter is as follows:

SELECT * FROM dbo.TestOptionalParameters(1, DEFAULT);

As a result, if we add an optional parameter to a function, we still need to change all the code that invokes it. This makes T-SQL optional parameters less useful than they could be, less useful than optional parameters in other languages.

The following script demonstrates the use of optional parameters in PL/PgSql:

 CREATE OR REPLACE FUNCTION public.EchoName(FirstName VARCHAR = 'Jesse', LastName VARCHAR = 'Pinkman')RETURNS VARCHAR AS$body$DECLAREret VARCHAR;BEGIN

RETURN LastName || ', ' || FirstName;
END;$body$
LANGUAGE plpgsql;
SELECT * FROM public.EchoName()UNION ALL SELECT * FROM public.EchoName('Walter')UNION ALL SELECT * FROM public.EchoName('Walter', 'White');

This script outputs the following results: "Pinkman, Jesse", "Pinkman, Walter", "White, Walter".

Parameter's optionality does not change function's signature

For example, let us recreate the function which we dropped earlier: 

CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumber INTEGER)RETURNS VARCHAR AS$body$BEGIN

RETURN 'Say my name.';
END;$body$
LANGUAGE plpgsql;

The following script will replace it:

CREATE OR REPLACE FUNCTION public.GetQuoteOfTheDay(someNumberINTEGER = 0)RETURNS VARCHAR AS$body$BEGIN

RETURN 'No more half-measures.';
END;$body$
LANGUAGE plpgsql;

When we select from the function, we shall get "No more half measures" in both cases:

SELECT * FROM public.GetQuoteOfTheDay(1)UNION ALL SELECT * FROM public.GetQuoteOfTheDay(); 

Parameter names are optional

Theoretically, we do not have to provide parameters' names. The following script shows a function which does exactly the same thing, although it is less readable:


DROP FUNCTION public.EchoName( VARCHAR,  VARCHAR);CREATE OR REPLACE FUNCTION public.EchoName( VARCHAR = 'Jesse',  VARCHAR = 'Pinkman')RETURNS VARCHAR AS$body$
DECLAREret VARCHAR;BEGIN

RETURN $1 || ', ' || $2;
END;$body$
LANGUAGE plpgsql;   

We can rerun the following script, and get exactly the same results as before:

SELECT * FROM public.EchoName()UNION ALL SELECT * FROM public.EchoName('Walter')UNION ALL SELECT * FROM public.EchoName('Walter', 'White');

Npgsql driver disregards parameter names too

The following code returns results even though both parameters of the function being invoked do not have names:

[Test, Explicit]
public void ReadFromProcWithOptionalParams(){
   using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
   {
       conn.Open();
       for (var i = 0; i < 3; i++)
           using (var tran = conn.BeginTransaction())
           using (var command = conn.CreateCommand())
           {
               command.CommandText = "EchoName"
               command.CommandType = CommandType.StoredProcedure;
               if(i>0)
               {
                   var firstName = new NpgsqlParameter("LastName"
                                        NpgsqlTypes.NpgsqlDbType.Varchar);
                   command.Parameters.Add(firstName);
                   command.Parameters[0].Value = "Hank"
               }
               if (i == 2)
               {
                   var lastName = new NpgsqlParameter("FirstName"
                                        NpgsqlTypes.NpgsqlDbType.Varchar);
                   command.Parameters.Add(lastName);
                   command.Parameters[1].Value = "Schrader"
               }

               using (var dr = command.ExecuteReader())
               {
                   while (dr.Read())
                   {
                       Console.WriteLine(dr.GetString(0));
                   }
               }
           }
   }
}  

If the function's parameters do have names, NpgSql does not match parameter names at all. Instead, it submits the first value to the first parameter, and so on. For example, let us restore the version of our function with named parameters:

DROP FUNCTION public.EchoName( VARCHAR,  VARCHAR);CREATE OR REPLACE FUNCTION public.EchoName(FirstName VARCHAR = 'Jesse', LastName VARCHAR = 'Pinkman')RETURNS VARCHAR AS$body$DECLAREret VARCHAR;BEGIN

RETURN LastName || ', ' || FirstName;
END;$body$
LANGUAGE plpgsql;

Now let us rerun ReadFromProcWithOptionalParams().

Even though we are passing "Hank" to a parameter named "LastName", "Hank" is passed to the first parameter, as we can see from the output:

Jesse, Pinkman
Hank, Pinkman
Hank, Schrader

This is a breaking change.We shall have to change our client code in many places.

Alternatively, are considering if we want to change NpgSql, so that it uses parameter names instead of ignoring them - that would make migration of our client code so very much easier. Because NpgSql is open source, changing it should not be a problem. We do not have to plead our case and, in the best case scenario, wait months or years for the change. If needed, we can get it done it soon.

In the next post we shall briefly discuss PostgreSql's MVCC vs SQL Server's snapshot isolation. This is a huge topic; we do not have the time for a more detailed comparison at this time.

Learning PostgreSql: bulk loading data

In this post we shall start loading data in bulk.

For better performance of inserts, we shall load data into a table without constraints and indexes. This sounds familiar. There is a bulk copy utility, and it is very easy to invoke from C#. The following code feeds the output from a T-SQL stored procedure into a PostgreSql table:

using (var pgTableTarget = new PgTableTarget(PgConnString, "Data.MyPgTable", GetColumns()))using (var conn = new SqlConnection(connectionString)){
   conn.Open();
   using (var command = conn.CreateCommand())
   {
       command.CommandText = "EXEC MyStoredProc";
       command.CommandType = CommandType.Text;
       command.CommandTimeout = 0;
       using (var dr = command.ExecuteReader())
       {
           var columnTypes = SetFields(dr.GetSchemaTable());
           var adapter = new SqlDataReaderToPgTableAdapter(pgTableTarget);
           while (dr.Read())
           {
               for (var columnIndex = 0; columnIndex < columnTypes.Count; columnIndex++)
               {
                   adapter.AddValue(dataReader: dr, columnIndex: columnIndex, columnType: columnTypes[columnIndex]);
               }
               pgTableTarget.EndRow();
           }
       }
   }
}

(snip)
private const int TYPE = 24;
internal static List<string> SetFields(DataTable schema){
   return (from DataRow dataRow in schema.Rows select dataRow[TYPE].ToString().ToLower()).ToList();} 

This code uses a couple of helper classes we developed ourselves. Here is a simple wrapper around COPY utility:

using System;using System.Collections.Generic;using System.Data;using Npgsql;
namespace Drw.Qr.FinDb.PostgresDataLoad
{
    public class PgTableTarget : IDisposable
    {
        private readonly Npgsql.NpgsqlConnection _conn;
        private readonly NpgsqlCommand _command;
        private readonly NpgsqlCopySerializer _serializer;
        private readonly NpgsqlCopyIn _copyIn;

        public PgTableTarget(string connString, string tableName, IEnumerable<string> columns)
        {
            _conn = new NpgsqlConnection(connString);
            _conn.Open();
            _command = _conn.CreateCommand();
            var copyStr = string.Format("COPY {0}({1}) FROM STDIN", tableName, string.Join(",", columns));
            _command.CommandText = copyStr;
            _command.CommandType = CommandType.Text;
            _serializer = new NpgsqlCopySerializer(_conn);
            _copyIn = new NpgsqlCopyIn(_command, _conn, _serializer.ToStream);
            _copyIn.Start();
        }

        public void AddString(string value)
        {
            _serializer.AddString(value);
        }

        public void AddNull()
        {
            _serializer.AddNull();
        }

        public void AddInt32(int value)
        {
            _serializer.AddInt32(value);
        }

        public void AddNumber(double value)
        {
            _serializer.AddNumber(value);
        }

        public void EndRow()
        {
            _serializer.EndRow();
            _serializer.Flush();
        }

        public void Dispose()
        {
            _copyIn.End();
            _serializer.Flush();
            _serializer.Close();
            _command.Dispose();
            _conn.Dispose();
        }
    }
}  

The following code is a straightforward adapter:


using System;using System.Data.SqlClient;
namespace Drw.Qr.FinDb.PostgresDataLoad
{
    public class SqlDataReaderToPgTableAdapter
    {
        private readonly PgTableTarget _pgTableTarget;

        public SqlDataReaderToPgTableAdapter(PgTableTarget pgTableTarget)
        {
            _pgTableTarget = pgTableTarget;           
        }

        public void AddValue(SqlDataReader dataReader,
                             int columnIndex, string columnType)
        {
            if (dataReader.IsDBNull(columnIndex))
            {
                _pgTableTarget.AddNull();
                return;
            }
            switch (columnType)
            {
                case "varchar":
                case "char":
                    _pgTableTarget.AddString(dataReader.GetString(columnIndex));
                    break;
                case "decimal":
                    _pgTableTarget.AddNumber((double)dataReader.GetDecimal(columnIndex));
                    break;
                case "int":
                    _pgTableTarget.AddInt32(dataReader.GetInt32(columnIndex));
                    break;
                case "smallint":
                    _pgTableTarget.AddInt32(dataReader.GetInt16(columnIndex));
                    break;
                case "real":
                    _pgTableTarget.AddNumber(dataReader.GetFloat(columnIndex));
                    break;
                case "float":
                    _pgTableTarget.AddNumber(dataReader.GetDouble(columnIndex));
                    break;
                default:
                    throw new ArgumentException("Not supported type: " + columnType);
            }           
        }
    }
}  

Although both classes do not support all the available types, they do support all the types we need for this small project.

As I was typing this post, the code already moved over several million rows. 

Learning PostgreSql: Functions and refcursors

In this post we shall create a function that returns data, and invoke it from our C# client. There are no stored procedures in PostgreSql, only functions. This is different from T-SQL, but consistent with many other languages, such as C#.

Creating a function

Functions can return many different types. Learning all the available options might take some time.

However, for the project we are working on, we need to replicate several T-SQL stored procedures which take column list as a parameter, and use dynamic SQL to return requested columns. So, we need the ability to return a result set from PostgreSql without having to specify exactly its structure. Because refcursors fit the bill, we shall learn how to use them, and that should be enough for now.

The following function returns a result set, although it does not use dynamic SQL yet:

CREATE OR REPLACE FUNCTION public.SelectTest (pTicker VARCHAR)RETURNS refcursor AS$body$DECLARE
  ref1 refcursor;BEGIN

OPEN ref1 FOR
select * from Prices WHERE Ticker=pTicker;RETURN ref1;
END;$body$
LANGUAGE 'plpgsql'

This function explicitly returns a result set. It is not enough to just issue a SELECT command. Again, this is different from T-SQL, but consistent with many object-oriented languages.

Calling the function from C# 

Let us invoke our function from C#:

[Test, Explicit]public void ReadFromProc2(){
   using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
   {
       conn.Open();
       using (var tran = conn.BeginTransaction())
       using (var command = conn.CreateCommand())
       {
           command.CommandText = "SelectTest";
           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add(new NpgsqlParameter());
           command.Parameters[0].NpgsqlDbType = NpgsqlDbType.Varchar;
           command.Parameters[0].Value = "AAPL";
           using (var dr = command.ExecuteReader())
           {
               var rc = 0;
               while (dr.Read())
               {
                   Console.WriteLine((rc++) + " " + dr.GetString(0) + " " + dr.GetDateTime(1) + " " + dr.GetDecimal(2));
               }
           }
       }
   }
}

Note: this code snippet does not handle null values. This is done to keep the example short.

Note:  it is essential to keep the transaction open for all the time we are consuming the data.This is documented here:http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html

Using dynamic SQL

We need to make sure we can call from C# functions that use dynamic SQL - that is part of the functionality we are going to replicate. The following function uses a dynamic column list:

CREATE OR REPLACE FUNCTION public.SelectTestWithColumnList (Ticker VARCHAR, ColumnList VARCHAR)RETURNS refcursor AS$body$DECLARE
  ref1 refcursor;BEGIN

OPEN ref1 FOR EXECUTE 'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'USING     Ticker;RETURN ref1;
END;$body$
LANGUAGE 'plpgsql' 

Of course, this is all completely different from T-SQL.

The following code calls this function from C#:

 [Test, Explicit]public void ReadFromProcWithColumnList(){
   using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
   {
       conn.Open();
       using (var tran = conn.BeginTransaction())
       using (var command = conn.CreateCommand())
       {
           command.CommandText = "SelectTestWithColumnList";
           command.CommandType = CommandType.StoredProcedure;
           command.Parameters.Add(new NpgsqlParameter());
           command.Parameters[0].NpgsqlDbType = NpgsqlDbType.Varchar;
           command.Parameters[0].Value = "AAPL";
           command.Parameters.Add(new NpgsqlParameter());
           command.Parameters[1].NpgsqlDbType = NpgsqlDbType.Varchar;
           command.Parameters[1].Value = "AsOfDate,Price";
           using (var dr = command.ExecuteReader())
           {
               var rc = 0;
               while (dr.Read())
               {
                   Console.WriteLine((rc++) + " " + dr.GetDateTime(0) + " " + dr.GetDecimal(1));
               }
           }
       }
   }
}

This code works as expected.

Next steps

There is much more to be learned about functions. We shall get back to it later.

However, we want to move over the data to our new database. We need some meaningful data to practice with. While the data is being migrated, we shall continue our learning.

Learning PostgreSql: Reading and Writing From .Net

In this post we shall do some setup tasks, save a few rows of data from a .Net client to PostgreSql, and read it back.

Setting up

We have set up a virtual machine running Red Hat Linux, installed PostgreSql 9.3 on it, and made sure there is enough disk space. 9.3 is a very recent version, released this September. Because PostgreSqlis not known for releasing before the full testing is complete, we did not have to wait for the next service pack or something like that.

Smoke test

On the client machine running Windows 7, we installed SQL Manager Lite, which is a lightweight GUI for simple tasks. We had no problems installing it and connecting to the server. Our first query was cut and pasted from the following article: "Postgres SQL Injection Cheat Sheet".

We ran this:

SELECT version()

followed by this

SELECT 1;

Creating a table

The following script was issued from SQL Manager Lite:

CREATE TABLE prices (
  ticker VARCHAR(20),
  asofdate TIMESTAMP WITHOUT TIME ZONE,
  price NUMERIC(6,2)
) 

Note: the default schema in PostgreSql is named public, not dbo.

Connecting from .Net

We have chosen to use a free open source .Net provider named  Npgsql, documented here:http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html. We have reviewed the source code (it was developed in C#) and built the binaries for our project.

The following code wrote a few rows into our table:

        private const string ConnString = "Server=myserver;Port=5432;Database=AkTest;User Id=myuser;Password=mypassword";

        [Test, Explicit]
        public void WriteTest()
        {
            using(var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
            {
                conn.Open();
                using(var command = conn.CreateCommand())
                {
                    command.CommandText =
                        "INSERT INTO Prices(Ticker, AsOfDate, Price)VALUES('GOOG', '2013-10-23',1001.00),('AAPL', '2013-10-23',517.49)";
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

We were able to select these rows from SQL Manager Lite. The following code read these rows from .Net:

       [Test, Explicit]
        public void ReadTest()
        {
            using (var conn = new Npgsql.NpgsqlConnection(ConnectionString: ConnString))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText =
                        "SELECT Ticker, AsOfDate, Price FROM Prices ORDER BY Ticker, AsOfDate;";
                    command.CommandType = CommandType.Text;
                    using(var dr = command.ExecuteReader())
                        while (dr.Read())
                        {
                            Console.WriteLine(string.Format("Ticker: {0}, AsOfDate: {1}, Price: {2}", dr.GetString(0), dr.GetTimeStamp(1), dr.GetDecimal(2)));
                        }
                }
            }
        } 

In the next post we shall create a stored procedure which reads data, and invoke it from our C# client.