Determinar el número de filas en PostgreSQL

Como suelo olvidar con facilidad ciertas cosas y al no usar mucho del.icio.us para recordarlas, pongo aquí un artículo que encontré hace unos días por si alguien más le encuentra utilidad. 😉

En cualquier gestor de base de datos, para conocer el número de registros basta con hacer uso de la función de agregación count.

sql:
SELECT count(*) FROM tabla;

Pero hacer esta operación en PostgreSQL sobre tablas grandes es bastante costosa porque se realiza un recorrido secuencial para obtener el número exacto de registros.

code:
test=> EXPLAIN ANALYZE SELECT COUNT(*) FROM actor;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.50..4.51 rows=1 width=0) (actual time=0.882..0.884 rows=1 loops=1)
   ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=0) (actual time=0.011..0.432 rows=200 loops=1)
 Total runtime: 0.963 ms
(3 filas)

Una forma de obtener el número aproximado de registros de manera rápida y sin los problemas inherentes al uso de la función count, es haciendo una consulta a los catálogos del sistema (pg_class):

sql:
SELECT reltuples FROM pg_class
   JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid)
WHERE nspname = 'public' -- esquema
AND relname = 'actor'; -- nombre de la tabla

Para que lo anterior funcione o dé resultados más cercanos a la realidad, es necesario ejecutar ANALYZE sobre esa tabla, tarea que se debe realizar periódicamente.

Tipos personalizados de PostgreSQL y Npgsql

A raíz de una pregunta en la lista de discusión en español de PostgreSQL en el que hacían la siguiente pregunta:

Hola, tengo una función definida en mi BD postgresql que recive un array de un elementos de un tipo definido en la BD. (este tipo es un bigint y un character varying). Ahora mi duda es si el conector npgsql (Conector para .NET) soporta la posibilidad de llamar a una funcion con estas características.

Estos tipos compuestos en PostgreSQL se crean con la sentencia CREATE TYPE:

code:
test=> CREATE TYPE complex AS (
test(>     r       double precision,
test(>     i       double precision
test(> );
CREATE TYPE
test=> create table foo (item complex);
CREATE TABLE
test=> insert into foo values(ROW(5.2,1.6));
INSERT 0 1
test=> insert into foo values(ROW(2,-1));
INSERT 0 1
test=> select * from foo;
   item
-----------
 (5.2,1.6)
 (2,-1)
(2 filas)

Npgsql trae soporte sólo para los tipos de datos nativos que ofrece PostgreSQL, y trata a estos tipos compuestos como una cadena de caracteres. Si bien es cierto que se podría trabajar solamente con cadenas tanto para insertar como para recuperar este tipo de datos, esto probablemente causaría algunos problemas en la etapa de desarrollo, porque estos valores no serían comprobados en tiempo de compilación (más detalles); puesto que disponemos del código de Npgsql, es posible extenderlo sin mayores problemas para que soporte los tipos de datos que usemos en una aplicación X.

Lo primero, es definir una clase que represente el tipo que hayamos definido en la base de datos:

csharp:
public struct NpgsqlComplex
{
        private double _r;
        private double _i;

        public double I
        {
                get { return _i; }
                set { _i = value; }
        }

        public double R
        {
                get { return _r; }
                set { _r = value; }
        }

        public NpgsqlComplex(double r, double i)
        {
                _r = r;
                _i = i;
        }       
}

Luego se tiene que registrar esta clase en el método VerifyDefaultTypesMap de la clase NpgsqlTypesHelper:

csharp:
NativeTypeMapping.AddType("complex", NpgsqlDbType.Complex, DbType.Object, true,
        new ConvertNativeToBackendHandler(ExtendedNativeToBackendTypeConverter.ToComplex));

NativeTypeMapping.AddTypeAlias("complex", typeof(NpgsqlComplex));

y agregar un nuevo elemento a la variable TypeInfoList ubicado en el método CreateAndLoadInitialTypesMapping de la misma clase:

csharp:
new NpgsqlBackendTypeInfo(0, "complex", NpgsqlDbType.Complex, DbType.Object, typeof(NpgsqlComplex),
                        new ConvertBackendToNativeHandler(ExtendedBackendToNativeTypeConverter.ToComplex)),

Los métodos ExtendedNativeToBackendTypeConverter.ToComplex y ExtendedBackendToNativeTypeConverter.ToComplex se usan para convertir estos tipos compuestos desde .NET a PostgreSQL y desde PostgreSQL a .NET respectivamente

csharp:
// clase ExtendedNativeToBackendTypeConverter
private static readonly Regex complexRegex = new Regex(@"\(([-+]?\b[0-9]*\.?[0-9]+(?:[eE][-+]?[0-9]+)?\b),([-+]?\b[0-9]*\.?[0-9]+(?:[eE][-+]?[0-9]+)?\b)\)", RegexOptions.Compiled);
internal static Object ToComplex(NpgsqlBackendTypeInfo TypeInfo, String BackendData, Int16 TypeSize, Int32 TypeModifier)
{
        Match m = complexRegex.Match(BackendData);

        return new NpgsqlComplex(
                        Double.Parse(m.Groups[1].ToString(), NumberStyles.Any,
                                                 CultureInfo.InvariantCulture.NumberFormat),
                        Double.Parse(m.Groups[2].ToString(), NumberStyles.Any,
                                                 CultureInfo.InvariantCulture.NumberFormat));
}


// clase ExtendedBackendToNativeTypeConverter
internal static String ToComplex(NpgsqlNativeTypeInfo TypeInfo, Object NativeData)
{
        if (NativeData is NpgsqlComplex)
        {
                NpgsqlComplex complex = (NpgsqlComplex)NativeData;
                return String.Format(CultureInfo.InvariantCulture, "({0},{1})", complex.R, complex.I);
        }
        else
        {
                throw new InvalidCastException("Unable to cast data to NpgsqlComplex type");
        }
}

Finalmente, la clase de prueba para ver si todo funciona como debería:

csharp:
using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using NpgsqlTypes;

namespace Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (NpgsqlCommand command = new NpgsqlCommand("SELECT item FROM foo LIMIT 1", new NpgsqlConnection("server=192.168.1.20;uid=alex;database=test")))
            {
                command.Connection.Open();

                NpgsqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    NpgsqlComplex num = (NpgsqlComplex)reader["item"];
                    Console.WriteLine("{0} + {1}i", num.R, num.I);
                }
                reader.Close();

                command.CommandText = "INSERT INTO foo VALUES (@item)";
                command.Parameters.Add("@item", NpgsqlDbType.Complex);
                command.Parameters["@item"].Value = new NpgsqlComplex(15.5, -5);

                command.ExecuteNonQuery();
            }
        }
    }
}

Aunque el ejemplo es bastante trivial y tiene poco o nulo valor en aplicaciones reales, este ejemplo intenta mostrar uno de los beneficios del uso de Software Libre: el hecho de poder acomodar a nuestras necesidades las cosas que usemos.

Nota: Si alguien le interesa el ejemplo mostrado, puede descargar una versión modificada de Npgsql (para Visual Studio 2005) más el proyecto de prueba.

Mapeo de secuencias de PostgreSQL en NHibernate

En PostgreSQL, para que una tabla tenga un campo autoincrementable se puede usar SERIAL o BIGSERIAL, éstos últimos en realidad no son tipos de datos reales puesto que internamente se crean secuencias para los campos que usan esta sintáxis, por ejemplo:

sql:
CREATE TABLE blog_category (
    id serial NOT NULL,
    name character varying,
    description character varying
);
code:
test=> \d blog_category
                                 Tabla «public.blog_category»
   Columna   |       Tipo        |                       Modificadores
-------------+-------------------+------------------------------------------------------------
 id          | integer           | not null default nextval('blog_category_id_seq'::regclass)
 name        | character varying |
 description | character varying |
 

Para representar representar esa tabla en NHibernate, hay que hacer algo adicional a lo que normalmente se hace con otro tipo de bases de datos:

xml:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Buayacorp.Domain.BlogCategory, Buayacorp.Domain" table="Blog_Category">
    <id name="Id" type="Int32" unsaved-value="0">
      <column name="Id" length="4" sql-type="int" unique="true"/>
      <generator class="native">
        <param name="sequence">blog_category_id_seq</param>
      </generator>
    </id>
    <property name="Name" type="String">
      <column name="Name" length="255" sql-type="varchar"/>
    </property>
    <property name="Description" type="String">
      <column name="Description" length="3000" sql-type="varchar"/>
    </property>
    <bag name="Blogs" inverse="true" lazy="true" cascade="all-delete-orphan">
      <key column="category_id"/>
      <one-to-many class="Buayacorp.Domain.Blog, Buayacorp.Domain"/>
    </bag>
  </class>
</hibernate-mapping>

En las líneas resaltadas se puede apreciar que para mapear el campo id es necesario especificar el nombre de la secuencia definida en la base de datos, esto es porque para generar los nuevos valores para ese campo se usa la función nextval, que justamente toma como parámetro ese nombre. Por ejemplo, para la siguiente porción de código:

csharp:
using System;
using System.Collections.Generic;
using System.Text;
using NHibernate.Cfg;
using NHibernate;
using Buayacorp.Domain;
using System.Xml;

namespace Buayacorp.Demo
{
    static class Program
    {
        static void Main()
        {
            Configuration cfg = new Configuration();
            cfg.AddAssembly("Buayacorp.Domain");
            ISessionFactory factory = cfg.BuildSessionFactory();

            ISession session = null;
            ITransaction transaction = null;
            try
            {
                session = factory.OpenSession();
                transaction = session.BeginTransaction();

                BlogCategory cat = new BlogCategory("Tecnología", "Blogs con información sobre lo último en tecnología...");
                session.Save(cat);

                Console.WriteLine(cat.Id); // cat.Id ya tiene un valor generado
               
                transaction.Rollback();
            }
            finally
            {
                if (session != null)
                    session.Close();
            }
        }
    }
}

Las consultas que se ejecutan en el servidor de base de datos son:

code:
2007-04-22 17:44:38 PET LOG:  sentencia: SELECT oid, typname FROM pg_type WHERE typname IN ('unknown', 'refcursor', 'char', 'bpchar', 'varchar', 'text', 'name', 'bytea', 'bit', 'bool', 'int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'numeric', 'inet', 'money', 'date', 'time', 'timetz', 'timestamp', 'timestamptz', 'point', 'lseg', 'path', 'box', 'circle', 'polygon')
2007-04-22 17:44:38 PET LOG:  sentencia: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2007-04-22 17:44:38 PET LOG:  sentencia: select nextval ('blog_category_id_seq')
2007-04-22 17:44:38 PET LOG:  sentencia: ROLLBACK
2007-04-22 17:44:38 PET LOG:  sentencia: unlisten *

Para los curiosos, pueden descargar el archivo que contiene el código mostrado en esta entrada.

Tip: Eliminar tildes desde PostgreSQL

Una forma sencilla para eliminar las tildes de un texto en PostgreSQL, es utilizar la función to_ascii

code:
buayacorp=> select lower(to_ascii('PERÚ')) = 'peru';
 ?column?
----------
 t
(1 fila)

Esta función, como se puede apreciar en el manual de PostgreSQL, sólo soporta las siguientes codificaciones: LATIN1, LATIN2, LATIN9 y WIN1250.

Siguiendo la tradición, a continuación una función básica que elimina las tildes de las urls.

sql:
CREATE OR REPLACE FUNCTION sanitize_url(VARCHAR)
RETURNS VARCHAR AS $$
        SELECT trim('-' FROM regexp_replace(lower(to_ascii($1)), '[ -]+', '-', 'g'));
$$ LANGUAGE sql;

El código mostrado, primero elimina las tildes del texto que se pase como parámetro, lo convierte a minúsculas y en seguida reemplaza cualquier ocurrencia de -uno o más- espacios o guiones por otro guión, finalmente elimina cualquier guión que haya quedado al inicio y final del texto.

NHibernate y PostgreSQL

Esta es la primera de dos entradas donde mostraré -de manera básica, como realizar reportes usando NHibernate , PostgreSQL y los controles para Reportes (ReportViewer) que trae cualquier instalación de Visual Studio 2005 -corríjanme si me equivoco 🙂 .

NHibernate es un framework que hace el mapeo de un modelo orientado a objetos a uno relacional.

Al usar NHibernate para el acceso a datos el desarrollador se asegura de que su aplicación es agnóstica en cuanto al motor de base de datos a utilizar en producción, pues NHibernate soporta los más habituales en el mercado: MySQL, PostgreSQL, Oracle, MS SQL Server, etc.

NHibernate tiene demasiadas características como para hablar sobre alguna en especial en este par de entradas, además que ya se ha escrito bastante sobre estos temas. En este apartado, sólo quiero hacer notar un pequeño detalle al momento de trabajar con PostgreSQL como base de datos.

xml:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="nhibernate" type="System.Configuration.NameValueSectionHandler" />
  </configSections>
  <nhibernate>
    <add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
    <add key="hibernate.dialect" value="NHibernate.Dialect.PostgreSQLDialect" />
    <add key="hibernate.connection.driver_class" value="NHibernate.Driver.NpgsqlDriver" />
    <add key="hibernate.connection.connection_string"
         value="Server=192.168.1.20;Database=test;User Id=alex;Encoding=LATIN1;" />

  </nhibernate>
</configuration>

Como se puede apreciar en el código mostrado, la cadena de conexión incluye un parámetro Encoding -sólo sirve para PostgreSQL, donde se especifica la codificación de caracteres que usará nuestra aplicación en la comunicación con el servidor de Base de Datos, si bien es cierto que PostgreSQL intentará hacer automáticamente una conversión entre la codificación definida en el servidor y la del cliente, es recomendable utilizar explícitamente la misma codificación en ambos extremos.

En la siguiente entrada, se tocará específicamente el diseño de reportes usando un ensamblado que contiene nuestro modelo.

Class Diagram for NHibernate Sample