Valentine's tech log

Real-time console based monitoring of PostgreSQL databases (pg_view)

2013-08-24T13:11:00.001+02:00

In many cases, it is important to be able to keep your hand on the pulse of your database in real-time. For example when you are running a big migration task that can introduce some unexpected locks, or when you are trying to understand how the current long running query is influencing your IO subsystem.

For a long time I was using a very simple bash alias that was injected from the .bashrc script and that included the calls to system utilities like watch, iostat, uptime, df, some additional statistics from the /proc/meminfo and psql that was extracting information about currently running queries and if that queries are waiting for a lock. But this approach had several disadvantages. In many cases I was interested in the disk read/write information for query processes or PostgreSQL system processes, like WAL and archive writers. Also I wanted to have a really easy way to notice the queries that are waiting for locks and probably highlight them by color.
Several weeks ago we finally open-sourced our new tool, that makes our lives much easier. That tool combines all the feature requests that I was dreaming of for a long time. Here it is: pg_view.

I already have some more feature requests actually and hope that Alexey will find some time to add them to the tool in nearest future. So if somebody wants to contribute or give some more ideas, please comment and open feature requests on the github page :)

Schema based versioning and deployment for PostgreSQL

2012-01-22T20:25:00.001+01:00

I am one of the supporters of keeping as much business logic in the database itself. This reduces the access layer of the application to mostly dumb transport and data transformation logic that can be implemented using different technologies and frameworks, without the need to re-implement critical data consistency and data distribution logic in several places, and gives an easy possibility to control what you are doing with your data and how your applications are allowed to access this data or even change exchange the underlying data structures transparently from the upper level of the code and without the need of a downtime. It also gives a possibility to add an additional layer of security allowing access to the data only through stored procedures, that can change their security execution context as needed (SECURITY DEFINER feature of PostgreSQL).

This approach has some disadvantages of course. One of the biggest technical problems, that is very easily becoming an organizational problem if you have a relatively big teem of developers, a problem of how to rapidly rollout new features without touching old functioning stored procedures, so that old versions of your upper level applications can still access the previous versions of stored procedures, and newly rolled out nodes with new software stack on them, access new stored procedures doing something more, or less, or returning some other data sets compared to their previous versions. And of course hundreds of stored procedures that are there to access and manipulate data are enough to make any attempt to keep all new versions of them backwards compatible, a nightmare.

Classical way to do this, would be to keep all the changes backwards compatible and if it is not possible, then create a new version of a stored procedure with some version suffix like _v2, mark the previous version as deprecated and after all your software stack is rolled out to use that new function, just drop the previous version. But if you are rolling out new version of the whole stack once of twice a week, the control of what is used and that is not becomes quite a challenge... and discipline of all the developers should be really good as well. Stored procedures are not the only objects, that are changing together with them. The return or input types can change as well. Changing of a return type, that is used by more then 2 stored procedures in a backwards compatible fashion is a pure horror if you want to do it without creating a new version of such a type and new versions of all the stored procedures, that use it. Dependency control becomes another problem.

My solution to that problem was to introduce a schema based versioning of PostgreSQL stored procedures. It uses an idea of PostgreSQL schema and search_path for a session.

So all the stored procedures, that are exposed to the client software stack, are grouped in one API schema that contains only stored procedures and types needed by them.

Schema name contains a version in it, like proj_api_vX_Y_Z, where X_Y_Z is a version, that a software stack is targeted to. Software stack does SET search_path to proj_api_vX_Y_Z, public; immedeately after it gets a connection from the pool and all calls to the stored procedures are done without explicitly specifying a schema name for that API stored procedure and PostgreSQL finds the needed stored procedure from the specified schema.

So when a branch is stable and branch version is fixed, it is used as a property that will be used when setting the default search_path for the software, that is being deployed for that branch. For example in Java using BoneCP JDBC Pool, setting an initSQL property of all the pools used to access proj database.

We are storing the sources of all the stored procedures (and other database objects) in a special database directory structure that is checked in into a usual SCM system. All the files sorted in corresponding folders and are prefixed with a 2 digit numeric prefix to ensure the order of sorting (good old BASIC times :) ). Like

`50_proj_api`
	`00_create_schema.sql`
	`20_types`
		`20_simple_object_input_type.sql`
	`30_stored_procedures`
		`20_get_object.sql`
		`20_set_object.sql`

Here 00_create_schema.sql file is containing CREATE SCHEMA proj_api; statement, statements to set default security options for newly created stored procedures and a SET search_path TO proj_api, public; statement, that ensures, that all the objects, that are coming after that file are injected into the correct API schema. An example of 00_create_schema.sql file can look like:

RESET role;

CREATE SCHEMA proj_api AUTHORIZATION proj_api_owner;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA proj_api REVOKE EXECUTE ON FUNCTIONS FROM public;

GRANT USAGE ON SCHEMA proj_api TO proj_api_usage;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA proj_api GRANT EXECUTE ON FUNCTIONS TO proj_api_executor;
ALTER DEFAULT PRIVILEGES FOR ROLE proj_api_owner IN SCHEMA proj_api GRANT EXECUTE ON FUNCTIONS TO proj_api_executor;
ALTER DEFAULT PRIVILEGES IN SCHEMA proj_api GRANT EXECUTE ON FUNCTIONS TO proj_api_executor;

SET search_path to proj_api, public;

DO $SQL$
BEGIN
  IF CURRENT_DATABASE() ~ '^(prod|staging|integration)_proj_db$' THEN
    -- change default search_path for production, staging and integration databases
    EXECUTE 'ALTER DATABASE ' || CURRENT_DATABASE() || ' SET search_path to proj_api, public;';
  END IF;
END
$SQL$;

SET role TO proj_api_owner;

This kind of layout gives a possibility to bootstrap API schema objects into a needed database easily and that is very important, to keep track of all the database logic changes in SCM system that lets you review and compare the changes between releases.

Bootstrapping into a development database can be done by a very easy script like:

(
echo 'DROP SCHEMA proj_api CASCADE;'
find 50_proj -type f -name '*.sql' \
  | sort \
  | xargs cat \
) | psql dev_proj_db -1 -f -

In case of development database, we are actually bootstrapping all the objects including tables into a freshly prepared database instance, so that integration tests can run and modify data as they want.

Injecting into a production or staging database can be automated and implemented with different kind of additional checks, but at the end it is something like:

(
cat 50_proj/00_create_schema.sql | sed s/proj_api/proj_api_vX_Y_Z/g 
find 50_proj -type f -name '*.sql' ! -name '00_create_schema.sql' \
  | sort \
  | xargs cat \
) | psql prod_proj_db -1 -f -

So after that, we have a fresh copy of the whole shiny API schema with all the dependencies rolled out to the production database. And this schema objects are only accessed by the software, that is supposed to do so, that is tested to run with this very combination and this versions of the stored procedures and depended types. And if we see any problems with the rollout, we can just rollback the software stack so it can still access our old stored procedures, located in a schema with previous version of out API.

This method does not solve the problem of versioning of tables in our data schema (we would keep all the tables, related objects and low level transformation stored procedures in proj_data schema) but for that, there is a very simple, but very nice, solution, http://www.depesz.com/index.php/2010/08/22/versioning/ suggested and implemented by Depesz. Of cause, changes in table structure should be still kept backwards compatible and nicely written database diff rollout and rollback files should be written for every such change.

I am not going into details about how to prepare Springs configuration of the JDBC pools for the java clients or how to configure the bootstrapping for integration testing in your Maven project configuration as this information will not add any real value to this blog post that became much longer then I expected from the beginning.

NOTE: Because of a bug in PostgreSQL JDBC driver the types that are used as input parameters for stored procedures cannot be located in different schemas (TYPE OIDs are being searched only by name only, without consideration of a schema and search_path). Patching of the driver is very easy and we did so, in my company to be able to use the schema based versioning in our Java projects. I reported the bug twice already (http://archives.postgresql.org/pgsql-jdbc/2011-03/msg00007.php, http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00083.php), but unfortunately no response from anybody. Probably have to submit a patch myself sometime.

Index sizes depending on the type of the field being indexed

2011-04-06T17:21:00.001+02:00

The sizes of indexes (and tables of cause) are influencing the look up speed directly (the smaller are the corresponding file system files, the faster one can scan it, not saying anything about the sizes, needed to be kept in memory caches)

So I did some simple experiment to demonstrate the effect of choosing different field types to be used as index fields to the size of the indexes and tables.

DROP DATABASE IF EXISTS eval;
CREATE DATABASE eval;
\c eval
*/
SET work_mem TO '128MB';
SET maintenance_work_mem TO '128MB';

DROP SCHEMA IF EXISTS eval_schema CASCADE;
CREATE SCHEMA eval_schema;

SET search_path to eval_schema;

CREATE TABLE eval_config ( table_name text, id_field_type text, id_field_expression text, row_count integer DEFAULT 100000 );
INSERT INTO eval_config VALUES 
( 'integer_short_id_table', 'integer', 's.i' ),
( 'integer_large_id_table', 'integer', 's.i * 123' ),
( 'bigint_short_id_table', 'bigint', 's.i' ),
( 'bigint_large_id_table', 'bigint', 's.i * 123' ),
( 'text_number_short_id_table', 'text', 's.i' ),
( 'text_number_large_id_table', 'text', 's.i * 123' ),
( 'numeric_short_id_table', 'numeric', 's.i' ),
( 'numeric_large_id_table', 'numeric', 's.i * 123' ),
( 'binary_md5_text_table', 'bytea', $$decode( md5( s.i::text || '-text-filler'), 'hex' ) $$ ),
( 'md5_text_table', 'text', $$md5( s.i::text || '-text-filler' )$$ );

CREATE VIEW eval_table_stats AS
  SELECT t.relname as "Table name",
       c.id_field_type as "Indexed field type",
       c.id_field_expression as "Expression",
       c.row_count as "Row count",
       s.stawidth as "Average id field width",
       pg_size_pretty(pg_table_size(t.oid)) as "Table size without index",
       pg_size_pretty(pg_indexes_size(t.oid)) as "Index size" /*,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_tup_hot_upd,
       n_live_tup,
       n_dead_tup */
  FROM eval_config as c
  JOIN pg_class as t
    ON c.table_name = t.relname
   AND t.relkind = 'r'
  JOIN pg_namespace as n
    ON relnamespace = n.oid
   AND n.nspname = 'eval_schema'
  LEFT 
  JOIN pg_statistic as s
    ON s.starelid = t.oid
   AND s.staattnum = 1
  LEFT
  JOIN pg_stat_user_tables as w
    ON w.relid = t.oid;

DO $SQL$
DECLARE 
  config record;
BEGIN
  FOR config IN SELECT * FROM eval_config
  LOOP
    RAISE INFO 'Creating table %', quote_ident( config.table_name );
    EXECUTE $$
    CREATE TABLE $$ || quote_ident( config.table_name ) || $$
    ( id $$ || config.id_field_type || $$, data text );
    $$;
    RAISE INFO 'Filling table %', quote_ident( config.table_name );
    EXECUTE $$
    INSERT INTO $$ || quote_ident( config.table_name ) || $$
    SELECT ( $$ || config.id_field_expression || $$ )::$$ || config.id_field_type || $$, 'some filling data'
      FROM generate_series(1, $$ || config.row_count || $$) as s(i);
    $$;
    RAISE INFO 'Building index on %', quote_ident( config.table_name );
    EXECUTE $$
    CREATE INDEX ON $$ || quote_ident( config.table_name ) || $$ ( id );
    $$;
    RAISE INFO 'Analyzing table %', quote_ident( config.table_name );
    EXECUTE $$
    ANALYZE $$ || quote_ident( config.table_name ) || $$;
    $$;
  END LOOP;
END;
$SQL$;

SELECT * FROM eval_table_stats;

DO $SQL$
DECLARE 
  config record;
BEGIN
  FOR config IN SELECT * FROM eval_config
  LOOP
    RAISE INFO 'Bloating table % (phase 1)', quote_ident( config.table_name );
    EXECUTE $$
    UPDATE $$ || quote_ident( config.table_name ) || $$
       SET data = data
     WHERE random() > 0.5;
    $$;
    RAISE INFO 'Bloating table % (phase 2)', quote_ident( config.table_name );
    EXECUTE $$
    UPDATE $$ || quote_ident( config.table_name ) || $$
       SET data = data
     WHERE random() > 0.5;
    $$;
    RAISE INFO 'Analyzing table %', quote_ident( config.table_name );
    EXECUTE $$
    ANALYZE $$ || quote_ident( config.table_name ) || $$;
    $$;
  END LOOP;
END;
$SQL$;

SELECT * FROM eval_table_stats;

As a result of execution of this script, we got several tables and some statistics on the table and index sizes.
I created the tables with id field with types: integer, bigint, text and numeric, additionally bytea and text for the md5 hash indexes. Actually the table size is including the filler text data, so it's size is not only the size of the fields being evaluated:

Table sizes just after insertion of 100T rows

Table name	Indexed field type	Expression	Row count	Average id field width	Table size without index	Index size
integer_short_id_table	integer	s.i	100000	4	5128 kB	1768 kB
integer_large_id_table	integer	s.i * 1234	100000	4	5128 kB	1768 kB
bigint_short_id_table	bigint	s.i	100000	8	5552 kB	2208 kB
bigint_large_id_table	bigint	s.i * 1234	100000	8	5552 kB	2208 kB
text_number_short_id_table	text	s.i	100000	5	5128 kB	2200 kB
text_number_large_id_table	text	s.i * 1234	100000	9	5552 kB	2624 kB
numeric_short_id_table	numeric	s.i	100000	8	5552 kB	2616 kB
numeric_large_id_table	numeric	s.i * 1234	100000	9	5624 kB	2656 kB
binary_md5_text_table	bytea	decode( md5( s.i::text \|\| '-text-filler'), 'hex' )	100000	17	6336 kB	3552 kB
md5_text_table	text	md5( s.i::text \|\| '-text-filler' )	100000	33	7880 kB	5328 kB

Table sizes after random bloating (2 times random update of 50% of rows)

Table name	Indexed field type	Expression	Row count	Average id field width	Table size without index	Index size
integer_short_id_table	integer	s.i	100000	4	10232 kB	5288 kB
integer_large_id_table	integer	s.i * 1234	100000	4	10232 kB	5272 kB
bigint_short_id_table	bigint	s.i	100000	8	11 MB	6592 kB
bigint_large_id_table	bigint	s.i * 1234	100000	8	11 MB	6560 kB
text_number_short_id_table	text	s.i	100000	5	10232 kB	5896 kB
text_number_large_id_table	text	s.i * 1234	100000	9	11 MB	7096 kB
numeric_short_id_table	numeric	s.i	100000	8	11 MB	7752 kB
numeric_large_id_table	numeric	s.i * 1234	100000	9	11 MB	7880 kB
binary_md5_text_table	bytea	decode( md5( s.i::text \|\| '-text-filler'), 'hex' )	100000	17	12 MB	7336 kB
md5_text_table	text	md5( s.i::text \|\| '-text-filler' )	100000	33	15 MB	11 MB

Table sizes just after insertion of 5M rows

Table name	Indexed field type	Expression	Row count	Average id field width	Table size without index	Index size
integer_short_id_table	integer	s.i	5000000	4	249 MB	86 MB
integer_large_id_table	integer	s.i * 123	5000000	4	249 MB	86 MB
bigint_short_id_table	bigint	s.i	5000000	8	269 MB	107 MB
bigint_large_id_table	bigint	s.i * 123	5000000	8	269 MB	107 MB
text_number_short_id_table	text	s.i	5000000	7	269 MB	107 MB
text_number_large_id_table	text	s.i * 123	5000000	9	269 MB	128 MB
numeric_short_id_table	numeric	s.i	5000000	8	269 MB	129 MB
numeric_large_id_table	numeric	s.i * 123	5000000	10	284 MB	129 MB
binary_md5_text_table	bytea	decode( md5( s.i::text \|\| '-text-filler'), 'hex' )	5000000	17	308 MB	172 MB
md5_text_table	text	md5( s.i::text \|\| '-text-filler' )	5000000	33	383 MB	259 MB

Table sizes after random bloating (2 times random update of 50% of rows)

Table name	Indexed field type	Expression	Row count	Average id field width	Table size without index	Index size
integer_short_id_table	integer	s.i	5000000	4	498 MB	257 MB
integer_large_id_table	integer	s.i * 123	5000000	4	498 MB	257 MB
bigint_short_id_table	bigint	s.i	5000000	8	539 MB	321 MB
bigint_large_id_table	bigint	s.i * 123	5000000	8	539 MB	321 MB
text_number_short_id_table	text	s.i	5000000	7	538 MB	287 MB
text_number_large_id_table	text	s.i * 123	5000000	9	539 MB	340 MB
numeric_short_id_table	numeric	s.i	5000000	8	539 MB	384 MB
numeric_large_id_table	numeric	s.i * 123	5000000	10	569 MB	384 MB
binary_md5_text_table	bytea	decode( md5( s.i::text \|\| '-text-filler'), 'hex' )	5000000	17	615 MB	354 MB
md5_text_table	text	md5( s.i::text \|\| '-text-filler' )	5000000	33	766 MB	545 MB

Ok, we see here, that actually it is a field byte width, that is important and for small numbers, the difference is really not so significant, but still an index on integer (4 bytes wide) filed is little smaller then index on text representation of the same ids even for tables that keep 10000 records only (these results are not posted here).

I specially was creating the tables in 2 variants, id as a result of 1..N series, and multiplied by 1234 or 123 to make the numbers wider in text representation to see how the length of the text representation of the number is influencing the size of the index.

Some more play with the smallint type, showed that it does save space in the table itself, but the index size is the same as for integer.

What was really surprising for me, that numeric type had bigger indexes then text indexes for the same numbers.

And of cause again and again -- indexes on some long text fields can be built on the md5 hash of such a text and be much more performant, when you index bytea version of the md5 hash instead of text representation of the md5 hash itself. Though md5 hash collisions can theoretically happen, I have not experienced them yet myself in practice.

pgAdmin III macros: get table fields

2011-02-17T17:22:00.002+01:00

When writing SQL statements of Stored Procedure code in pgAdmin, it is quite often quite handy to know the names and restrictions on the fields of some table.

Here is a simple macro that can be assigned to a key combination in pgAdmin -> Query Window -> Macros -> Manage macros...

select quote_ident(nspname) || '.' || quote_ident(relname) as table_name, 
       quote_ident(attname) as field_name, 
       format_type(atttypid,atttypmod) as field_type, 
       case when attnotnull then ' NOT NULL' else '' end as null_constraint,
       case when atthasdef then 'DEFAULT ' || 
                                ( select pg_get_expr(adbin, attrelid) 
                                    from pg_attrdef 
                                   where adrelid = attrelid and adnum = attnum )::text else '' 
       end as dafault_value,
       case when nullif(confrelid, 0) is not null 
            then confrelid::regclass::text || '( ' || 
                 array_to_string( ARRAY( select quote_ident( fa.attname ) 
                                           from pg_attribute as fa 
                                          where fa.attnum = ANY ( confkey ) 
                                            and fa.attrelid = confrelid
                                          order by fa.attnum 
                                        ), ',' 
                                 ) || ' )' 
            else '' end as references_to
  from pg_attribute 
       left outer join pg_constraint on conrelid = attrelid 
                                    and attnum = conkey[1] 
                                    and array_upper( conkey, 1 ) = 1,
       pg_class, 
       pg_namespace
 where pg_class.oid = attrelid
   and pg_namespace.oid = relnamespace
   and pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
   and attnum > 0
   and not attisdropped
 order by attrelid, attnum;

Just select a table name, that you are interested in, and press the key binding, that you selected for that macros. pgAdmin will execute the query and show the list of all the columns of that table, including default values and foreign key references...

My preferred key binding is CTRL+1

Type of NULL is important

2011-02-17T16:20:00.002+01:00

The following SQL query shows how different can be the result of concatenating arrays with NULL values:

select '{e1,e2}'::text[] || 't'::text     as normal_array_concatenation,
       NULL::text[] || 't'::text         as appending_element_to_existing_NULL_value_array, 
       NULL || 't'::text                 as appending_element_to_NULL_value,
       '{e1,e2}'::text[] || NULL::text   as appending_typed_NULL,
       '{e1,e2}'::text[] || NULL::text[] as appending_typed_NULL_array;

The result of the execution (on 9.0) is:

─[ RECORD 1 ]──────────────────────────────────┬─────────────
normal_array_concatenation                     │ {e1,e2,t}
appending_element_to_existing_null_value_array │ {t}
appending_element_to_null_value                │ 
appending_typed_null                           │ {e1,e2,NULL}
appending_typed_null_array                     │ {e1,e2}

That explains why one can simply initialize a new array variable in PL/pgSQL and then immediately start concatenating it with values, not pre-initializing it with an empty array (that you have to do when you want to populate a text string... you cannot just take a NULL::text variable and start concatenating strings to it, you have to first pre-inizialize it with en empty string, but with arrays you can).

Another very important issue, that is related to that beheviour of arrays, is that when creating dynamic SQL queries for EXECUTE command in PL/pgSQL you SHOULD always put explicit type casts to the variables that you quote using quote_literal() or quote_nullable() when building dynamic queries:

DO $SQL$
DECLARE
  r text[];
  t text   := NULL;
  a text[] := NULL;
BEGIN

  SELECT '{e1,e2}'::text[] || a INTO r;
  RAISE INFO 'array concatenate: r is %', r;

  EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(a) || $$::text[] $$ INTO r;
  RAISE INFO 'array concatenate from EXECUTE: r is %', r;

  SELECT '{e1,e2}'::text[] || t INTO r;
  RAISE INFO 'array append: r is %', r;

  EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(t) || $$::text $$ INTO r;
  RAISE INFO 'array append from EXECUTE: r is %', r;

  /* These 2 examples will fail on the runtime 
     throwing exception (operator is not unique: text[] || unknown)
  --EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(a) INTO r;
  --RAISE INFO 'r is %', r;
  --
  --EXECUTE $$SELECT '{e1,e2}'::text[] || $$ || quote_nullable(t) INTO r;
  --RAISE INFO 'r is %', r;
  */

END;
$SQL$;

ALTER ENUM in PostgreSQL

2010-08-08T14:57:00.010+02:00

Note: do not use this method on PostgreSQL 9.1. This can corrupt your catalog as new enum model differentiates between even and odd OIDs for enum values. But in PostgreSQL 9.1 there is a nice ALTER TYPE command, that can alter you enum correctly.

Unfortunately PostgreSQL (9.0 and before) does not support altering ENUM types, that makes their use quite limited. Very often, it is important to have a possibility to extend already existing enumeration type and in very few cases delete or rename an element of an enumeration type.

The standard way to do that, would be to create a new enumeration type with some temporary name, convert the fields of the type of your old enumeration type to the new enumeration type you just created, then drop the old one and rename the temporary name to the original enum type. This operation is doable, but conversion of the field type is usually getting a full lock on that table and can be quite a long operation (maybe in 9.1 this will change).

By now, the only way to do it without table lock and messing up with temporary enumeration types, is to play with the catalog directly.

As internally postgres is storing OIDs of the enumeration type labels in the tables and types, where this enumeration is being used, one can actually add new labels and change the names of existing labels in the needed enumeration without a fear to destroy integrity of existing data structures, that use that enumeration labels already (i.e. OIDs of that enumeration labels).

To add one additional label in an existing ENUM one can do the following:

insert into pg_enum(enumtypid, enumlabel)
select t.oid, 'NEW_ENUM_VALUE'
  from pg_type as t
 where t.typtype = 'e'
   and t.oid = 'existing_enum'::regtype::oid
   and string_to_array( split_part(version(), ' ', 2), '.' )::int[] < ARRAY[9,1]
returning enumtypid::regtype::text, enumlabel;

This will add a label of NEW_ENUM_VALUE to an existing enumeration type existing_enum (be sure to put the right letter case for your enumeration type and enumeration label names).

To rename an existing value name one can do the following:

update pg_enum as e
   set enumlabel = 'UPDATED_ENUM_VALUE'
 where e.enumtypid = 'existing_enum'::regtype::oid
   and e.enumlabel = 'OLD_ENUM_VALUE'
   and string_to_array( split_part(version(), ' ', 2), '.' )::int[] < ARRAY[9,1]
returning e.enumtypid::regtype::text, e.enumlabel;

Deletion of the enumeration labels can be really dangerous as one can accidentally drop an OID of the label, that is still being used in the tables. Dmitry Koterov wrote a stored procedure, that does some easiest checks on tables (not types or types of types) that use the enumeration that you want to change. So if you really need to drop an enumeration label from an existing enumeration, think twice first and then read the post with the source of this stored procedure :)

Merging and manipulating arrays in PostgreSQL

2010-05-28T13:59:00.002+02:00

Though, strictly speaking, using arrays in relational databases is kind of not correct, still we use arrays, especially in stored procedures.

Actually PostgreSQL provides us with quite an number of nice array manipulation functions. That can be used at least starting from PostgreSQL 8.0. But what about set operations. How to sort an array in Postgres? How to merge arrays without repetitions? How to remove duplicate elements keeping the order of the appearance in the original array?

If you are working with the integer based arrays, there is quite an old and effective module intarray that allows us to perform some basic set operations on the integer based arrays.

But what to do with not integer based arrays? Or if we want to do some relatively complicated operation on integer bases array? SQL is a set manipulation language, so that means that actually, we can use internal SQL mechanics to manipulate, merge and sort arrays. But for that, first we have to be able to convert an array into kind of a table, so that we can use usual SQL manipulation on it's elements.

generate_series(), generate_subscripts() and unnest() are the most important functions in our case. Unfortunately generate_subscripts() and unnest() are only available starting from PostgreSQL 8.4, but one can easily create a small helper unnest() function using generate_series(), that will be not as efficient as the build in one, but still it is quite good trade off.

CREATE OR REPLACE FUNCTION unnest(a anyarray)
  RETURNS SETOF anyelement AS
$BODY$
/**
 *  Unnests given array into a table
 */
 /* -- testing
     select unnest(ARRAY[1,2,3,4,5]) as i
     select * from unnest(ARRAY[1,2,3,4,5]) as u(i)
  */
select ($1)[s.i] 
  from generate_series( array_lower($1, 1), array_upper($1, 1 ) ) as s(i);
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

A good thing about unnest() written in SQL and not PL/pgSQL is that one can use such a set retuning function directly after SELECT not necessarily pushing it in the list of used tables after FROM clause. The reason was explained by Tom Lane some time back in one of the newsgroup threads and is related to the implementation drawbacks of the PL/pgSQL.

Ok, so now I will simply give several examples of how to use these functions.

Merge 2 or more text arrays excluding duplicates:
select ARRAY( select unnest(ARRAY[ 'a', 'b', 'c' ]) union select unnest(ARRAY[ 'c', 'd', 'e' ]) )

If we use UNION ALL instead, we are doing just the same as array_cat(), but slower :)

Note, that UNION actually sorts the elements of the result set fist, to eliminate the duplicates, so the resulting array will be sorted usually. But one cannot relay on that behavior and if you really need a sorted array on the output, you have to use ORDER BY explicitly.

Merge 2 or more text arrays excluding duplicates and sorting elements:
select ARRAY( select unnest(ARRAY[ 'a', 'b', 'c' ]) as e union select unnest(ARRAY[ 'c', 'd', 'e' ]) as e order by e )

Here we are forcing PostgreSQL to sort the elements alphabetically. If you check the execution plan for that query, you will see that it is exactly the same as for the previous query. But here we can actually reverse the order or sort by element length or first by element length and then alphabetically and so on. This makes this approach quite flexible not still the whole construct is very readable and understandable. But what if we want to trim text of each of the elements, lower the case or even rewrite each element using regular expression replace?

Trim or rewrite text array elements:
select ARRAY( select btrim(lower(regexp_replace(unnest(ARRAY['a a','B b','c C ']), $$\s+$$, ' ', 'g') ) ) as e )

We can use this approach in all our previous queries as well, so we actually can merge several arrays reducing all repeated space characters into one, trimming and lowering each element before, removing duplicates from the resulting array and all that in one simple and readable SQL statement.

By now, with this syntax we cannot actually filter some elements out of the original array.

Filter elements from an array depending on element properties:
select ARRAY( select a.e from unnest(ARRAY[0,4,2,5,4,1,6,9,8,7]) as a(e) where a.e % 2 = 0 )

This query actually rewrites an integer array so, that it drops all the add elements from it. We have to push unnest() to FROM list to be able to name the virtual table, that unnest() creates as well as name the element column so that we can reference it in the where clause. Again there are not guaranties here that our array will be contain elements in the same order that it was in the original one.

The problem is that all these queries do not know anything about the original position of the elements in the original array. To solve this problem we need element indexes to be visible inside the query. One can do it using a plain old generate_series() or not so old generate_subscripts() (one actually should prefer the later one if you are on the PostgreSQL 8.4+) to unnest our original array. You cannot use the unnest() directly as there is not way to determine the position or index other then using window functions like row_number(), but this is much much slower on practice.

Filtering elements from an array depending on element position (index):
select ARRAY( select (ARRAY[0,4,2,5,4,1,6,9,8,7])[s.i] from generate_series(array_lower(ARRAY[0,4,2,5,4,1,6,9,8,7], 1), array_upper(ARRAY[0,4,2,5,4,1,6,9,8,7], 1) ) as s(i) where s.i % 2 = 0 order by s.i )

Filtering elements from an array depending on element position (index):

select ARRAY(
  select (ARRAY[0,4,2,5,4,1,6,9,8,7])[s.i]
    from generate_series(array_lower(ARRAY[0,4,2,5,4,1,6,9,8,7], 1),
                         array_upper(ARRAY[0,4,2,5,4,1,6,9,8,7], 1) ) as s(i)
   where s.i % 2 = 0
   order by s.i
)

This query is dropping all the elements of the original array that are located on the odd positions and ensures that the elements are ordered in the resulting array the same way as they were in the original one. forcing ordering the elements here is probably not the best thing to do for performance reasons, but I want to demonstrate that it is possible and, strictly speaking, even a proper way to do.

But using generate_series() or generate_subscripts() shows a small problem actually. We have to push the original array at least 2 times in the query. Inside a PL/pgSQL code the arrays are usually replaced with the variable names, and the queries do not look so bulky there, but there is a possibility to create an additional helper function that will unnest an array returning not only a element itself, but also it's index in the original array:

CREATE OR REPLACE FUNCTION array_enumerate(IN a anyarray, 
  OUT i integer, OUT e anyelement)
  RETURNS SETOF record AS
$BODY$
/** 
 *  Unnests array into a table together with element indexes
 */
 /* -- testing
     select * from array_enumerate(ARRAY['a', 'b', 'c']) as u
  */
select s.i, ($1)[s.i] from generate_series( array_lower($1, 1), array_upper($1, 1 ) ) as s(i)
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

on the PostgreSQL versions after 8.4 one can write it using generate_subscripts() like:

CREATE OR REPLACE FUNCTION array_enumerate(IN a anyarray, 
  OUT i integer, OUT e anyelement)
  RETURNS SETOF record AS
$BODY$
/** 
 *  Unnests array into a table together with element indexes
 */
 /* -- testing
     select * from array_enumerate(ARRAY['a', 'b', 'c']) as u
  */
select s.i, ($1)[s.i] from generate_subscripts( $1, 1 ) as s(i)
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

Using generate_subscripts() should be preferable as one can create some crazy array with elements having noncontinuous indexes actually. And it should be also a little bit faster, then generate_series().

This actually gives us a possibility to rewrite previous query like this:

select ARRAY(
  select s.e
    from utils.array_enumerate(ARRAY[0,4,2,5,4,1,6,9,8,7]) as s(i,e)
   where s.i % 2 = 0
   order by s.i
)

Now we can even do something practical using this approach. for example

Remove duplicate elements from array keeping the element appearance order:
select ARRAY( select s.e from utils.array_enumerate(ARRAY[0,4,2,5,4,1,6,9,8,7]) as s(i,e) group by s.e order by min(s.i) )

Actually this gives a possibility to process lists of words using string_to_array() and array_to_string() methods, or on newer versions of PostgreSQL regexp_split_to_array() to remove the repeating space or punctuation characters during splitting directly.

Practically everywhere in the queries, one could use the array_agg() to generate the resulting arrays or, in the coming 9.0, a very efficient string_agg() to generate strings directly, without creating preliminary arrays and then using array_to_string().

On my tests comparing the performance of the ARRAY(select) and array_agg() I could not find any significant performance difference.

re-assigning values to the parameters in plpythonu

2010-02-24T18:04:00.002+01:00

As discussed in Postgres BUG #5232 assigning values to the parameters fails with the following error:

Detail: <type 'exceptions.UnboundLocalError'>: local variable 'src' 
referenced before assignment

If assignment to the parameter is still needed one can solve the issue by adding global definition for such a parameter.

CREATE OR REPLACE FUNCTION pyreplace(src text, s text) 
  RETURNS text AS 
$BODY$ 
global src
src=src.replace(s,'') 
return src 
$BODY$ 
  LANGUAGE 'plpythonu' VOLATILE 
  COST 100;

Getting arrays from PostgreSQL database using JDBC in Java

2009-02-27T10:10:00.021+01:00

getArray() standard JDBC methods are working for basic types in PostgreSQL as documented in JDBC specifications.

As PostgreSQL 8.2 does not support arrays of types, and PostgreSQL JDBC driver does not support getting of the arrays of types even from PostgreSQL 8.3+, we can pass this kind of structures as text arrays, were text elements are postgres records (not types), serialized with textin(record_out(ROW(a, b, c)))::text approach (for PostgreSQL 8.2 this is the only way to serialize the record, in 8.3+ it is now possible to simply convert the record to text like ROW(a, b, c)::text) and the way of deserialization of the received text array data with java method that is shown below:

public class Utils {
...

/**
* Method parses a postgres Row into a List of Strings.
* <p>
* The postgres row is represented by a String and consists of one or more columns, that are separated by a comma.
* The row must begin with an open bracket and must end with a closing bracket.
* Each column must begin with a letter or a quote. If a column begins with a quote, the column must end with a quote.
* Inside quotation a quote is represented by a double quote or by backslash and quote, a backslash is represented by double backslash.
*
* @param value
* @return List of Strings
* @throws JBackendParserException
*/
public static List<String> postgresROW2StringList(String value) throws JBackendParserException
{
    return postgresROW2StringList(value, 128);
}

/**
* Method parses a postgres Row into a List of Strings.
* <p>
* The postgres row is represented by a String and consists of one or more columns, that are separated by a comma.
* The row must begin with an open bracket and must end with a closing bracket.
* Each column must begin with a letter or a quote. If a column begins with a quote, the column must end with a quote.
* Inside quotation a quote is represented by a double quote or by backslash and quote, a backslash is represented by double backslash.
* <p>
* The appendStringSize is the Size for StringBuilder.
*
* @param value, the postgres Row
* @param appendStringSize
* @return List of Strings
* @throws JBackendParserException
*/
public static List<String> postgresROW2StringList(String value, int appendStringSize)
throws JBackendParserException
{
    if (!(value.startsWith("(") && value.endsWith(")")))
    throw new ParseException("postgresROW2StringList() ROW must begin with '(' and end with ')': " + value);

    List<String> result = new ArrayList<String>();

    char[] c = value.toCharArray();

    StringBuilder element = new StringBuilder(appendStringSize);
    int i = 1;
    while (c[i] != ')')
    {
        if (c[i] == ',')
        {
            if (c[i+1] == ',')
            {
                result.add(new String());
            } else if (c[i+1] == ')')
            {
                result.add(new String());
            }
            i++;
        } else if (c[i] == '\"')
        {
            i++;
            boolean insideQuote = true;
            while(insideQuote)
            {
                char nextChar = c[i + 1];
                if(c[i] == '\"')
                {
                    if (nextChar == ',' || nextChar == ')')
                    {
                        result.add(element.toString());
                        element = new StringBuilder(appendStringSize);
                        insideQuote = false;
                    } else if(nextChar == '\"')
                    {
                        i++;
                        element.append(c[i]);
                    } else
                    {
                        throw new ParseException("postgresROW2StringList() char after \" is not valid");
                    }
                } else if (c[i] == '\\')
                {
                    if(nextChar == '\\' || nextChar == '\"')
                    {
                        i++;
                        element.append(c[i]);
                    } else
                    {
                        throw new ParseException("postgresROW2StringList() char after \\ is not valid");
                    }
                } else
                {
                    element.append(c[i]);
                }
                i++;
            }
        }else
        {
            while(!(c[i] == ',' || c[i] == ')'))
            {
                element.append(c[i]);
                i++;
            }
            result.add(element.toString());
            element = new StringBuilder(appendStringSize); // we aways loose the last object here, but its easier then checking for flag every time before append (definitely we loose some performance here)
        }
    }
return result;
}
...
}

We can use the following example SQL statement to demonstrate how to pack needed data structures into the serialized text arrays

select s.i as id,
'row ' || s.i as text_data,
ARRAY( select textin(record_out( ROW( 100 * s.i + a.i,
'element ' || 100 * s.i + a.i,
'constant text with some "quoting"' ) ))::text
from generate_series( 1, 5 ) as a(i) ) as serialized_row_array
from generate_series(1, 10) as s(i)

The result of the execution of this query is:

And then read these text arrays using the following java code in the springsframework row mapper

And then read these text arrays using the following java code in the
springframework row mapper (as this example uses ResultSet actually, you can see as well, how to read data directly from ResultSet in the same example):

public class ArrayRowMapper<ITEM> implements ParameterizedRowMapper<ITEM> {

...
private ITEM createEmptyItem() {
...
}
private Element createEmptyElement() {
...
}

public final ITEM mapRow(ResultSet rs, int rowNum) throws SQLException {
ITEM item = createEmptyItem();
item.setId( rs.getInt("id") );
item.setTextData( rs.getString("text_data") );
Array sqlArray = rs.getArray("serialized_row_array");
if ( sqlArray == null ) {
item.setElements(null);
} else {
String[] textArray = (String[])sqlArray.getArray();
List<Element> elements = new ArrayList<Element>(textArray.length);

for(int i = 0; i < textArray.length; i++)
{
try
{
List<String> stringResultList = Utils.postgresROW2StringList(textArray[i]);

Element element = createEmptyElement();
element.setId(Integer.parseInt(stringResultList.get(0)));
element.setTextData(stringResultList.get(1));
element.setConstantTextData(stringResultList.get(2));
elements.add(element);
}catch (JBackendParserException pe) {
logger.error("Problem parsing received ROW value [" + textArray[i] + "]: " + pe.getMessage(), pe);
}catch (Exception e) {
logger.error("Problem setting values to Element object from received ROW value [" + textArray[i] + "] : " + e.getMessage(), e);
}
}
item.setElements(elements);
}
}
}

Passing arrays to PostgreSQL database from java (JDBC)

2009-02-26T17:47:00.014+01:00

Normally JDBC driver needs to know, how to serialize some database type so, that the database can accept it. In case of PostgreSQL JDBC driver we use 2 implementations for passing integer and text arrays.

to pass an integer array to PostgreSQL database the following java.sql.Array implementation can be used:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>int4</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLInt4Array implements java.sql.Array {

    private final int[] intArray;
    private final String stringValue;

    public PostgreSQLInt4Array(int[] intArray) {
        this.intArray = intArray;
        this.stringValue = intArrayToPostgreSQLInt4ArrayString(intArray);
    }

    public String toString() {
        return stringValue;
    }

    /**
     * This static method can be used to convert an integer array to string representation of PostgreSQL integer array.
     * @param a source integer array
     * @return string representation of a given integer array
     */
    public static String intArrayToPostgreSQLInt4ArrayString(int[] a) {
        if ( a == null ) {
            return "NULL";
        }
        final int al = a.length;
        if ( al == 0 ) {
            return "{}";
        }
        StringBuilder sb = new StringBuilder( 2 + al * 7 ); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
        sb.append('{');
        for (int i = 0; i < al; i++) {
            if ( i > 0 ) sb.append(',');
            sb.append(a[i]);
        }
        sb.append('}');
        return sb.toString();
    }


    public static String intArrayToCommaSeparatedString(int[] a) {
        if ( a == null ) {
            return "NULL";
        }
        final int al = a.length;
        if ( al == 0 ) {
            return "";
        }
        StringBuilder sb = new StringBuilder( al * 7 ); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
        for (int i = 0; i < al; i++) {
            if ( i > 0 ) sb.append(',');
            sb.append(a[i]);
        }
        return sb.toString();
    }

    public Object getArray() throws SQLException {
        return intArray == null ? null : Arrays.copyOf(intArray, intArray.length);
    }

    public Object getArray(Map<String, Class<?>> map) throws SQLException {
        return getArray();
    }

    public Object getArray(long index, int count) throws SQLException {
        return intArray == null ? null : Arrays.copyOfRange(intArray, (int)index, (int)index + count );
    }

    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
        return getArray(index, count);
    }

    public int getBaseType() throws SQLException {
        return java.sql.Types.INTEGER;
    }

    public String getBaseTypeName() throws SQLException {
        return "int4";
    }

    public ResultSet getResultSet() throws SQLException {
        throw new UnsupportedOperationException();
    }

    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    public ResultSet getResultSet(long index, int count) throws SQLException {
        throw new UnsupportedOperationException();
    }

    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    public void free() throws SQLException {
    }

}

the same way we can create a class to pass a string array to PostgreSQL database:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>text</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLTextArray implements java.sql.Array {

    private final String[] stringArray;
    private final String stringValue;

    /**
     * Initializing constructor
     * @param stringArray
     */
    public PostgreSQLTextArray(String[] stringArray) {
        this.stringArray = stringArray;
        this.stringValue = stringArrayToPostgreSQLTextArray(this.stringArray);

    }

    @Override
    public String toString() {
        return stringValue;
    }

    private static final String NULL = "NULL";

    /**
     * This static method can be used to convert an string array to string representation of PostgreSQL text array.
     * @param a source String array
     * @return string representation of a given text array
     */
    public static String stringArrayToPostgreSQLTextArray(String[] stringArray) {
        final int arrayLength;
        if ( stringArray == null ) {
            return NULL;
        } else if ( ( arrayLength = stringArray.length ) == 0 ) {
            return "{}";
        }
        // count the string length and if need to quote
        int neededBufferLentgh = 2; // count the beginning '{' and the ending '}' brackets
        boolean[] shouldQuoteArray = new boolean[stringArray.length];
        for (int si = 0; si < arrayLength; si++) {
            // count the comma after the first element
            if ( si > 0 )  neededBufferLentgh++;

            boolean shouldQuote;
            final String s = stringArray[si];
            if ( s == null ) {
                neededBufferLentgh += 4;
                shouldQuote = false;
            } else {
                final int l = s.length();
                neededBufferLentgh += l;
                if ( l == 0 || s.equalsIgnoreCase(NULL) ) {
                    shouldQuote = true;
                } else {
                    shouldQuote = false;
                    // scan for commas and quotes
                    for (int i = 0; i < l; i++) {
                        final char ch = s.charAt(i);
                        switch(ch) {
                            case '"':
                            case '\\':
                                shouldQuote = true;
                                // we will escape these characters
                                neededBufferLentgh++;
                                break;
                            case ',':
                            case '\'':
                            case '{':
                            case '}':
                                shouldQuote = true;
                                break;
                            default:
                                if ( Character.isWhitespace(ch) ) {
                                    shouldQuote = true;
                                }
                                break;
                        }
                    }
                }
                // count the quotes
                if ( shouldQuote ) neededBufferLentgh += 2;
            }
            shouldQuoteArray[si] = shouldQuote;
        }

        // construct the String
        final StringBuilder sb = new StringBuilder(neededBufferLentgh);
        sb.append('{');
        for (int si = 0; si < arrayLength; si++) {
            final String s = stringArray[si];
            if ( si > 0 ) sb.append(',');
            if ( s == null ) {
                sb.append(NULL);
            } else {
                final boolean shouldQuote = shouldQuoteArray[si];
                if ( shouldQuote ) sb.append('"');
                for (int i = 0, l = s.length(); i < l; i++) {
                    final char ch = s.charAt(i);
                    if ( ch == '"' || ch == '\\' ) sb.append('\\');
                    sb.append(ch);
                }
                if ( shouldQuote ) sb.append('"');
            }
        }
        sb.append('}');
        assert sb.length() == neededBufferLentgh;
        return sb.toString();
    }


    @Override
    public Object getArray() throws SQLException {
        return stringArray == null ? null : Arrays.copyOf(stringArray, stringArray.length);
    }

    @Override
    public Object getArray(Map<String, Class<?>> map) throws SQLException {
        return getArray();
    }

    @Override
    public Object getArray(long index, int count) throws SQLException {
        return stringArray == null ? null : Arrays.copyOfRange(stringArray, (int)index, (int)index + count);
    }

    @Override
    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
        return getArray(index, count);
    }

    @Override
    public int getBaseType() throws SQLException {
        return java.sql.Types.VARCHAR;
    }

    @Override
    public String getBaseTypeName() throws SQLException {
        return "text";
    }

    @Override
    public ResultSet getResultSet() throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(long index, int count) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public void free() throws SQLException {
    }

//  public static void main(String[] args) {
//      // test the method
//      String[][] stringArrayArray = new String[][] {
//              { "shm\taliko", "", null, "kluku" },
//              { "", "NULL", "NuLL", "\"kuku\"", "valiko, shmaliko" },
//              { "", "NULL", "NuLL", "\"ku\\ku\"", "valiko, shm\taliko", "shm\taliko" },
//              { }
//      };
//
//      for( String[] stringArray : stringArrayArray ) {
//          PostgreSQLTextArray a = new PostgreSQLTextArray(stringArray);
//          String s = a.toString();
//          System.out.println(s);
//      }
//  }

}

Definitely it is possible to merge these two classes so that one wrapper is used instead of two and more known database types can be added in such a wrapper. This implementation relies on the fact that PostgreSQL type names are fixed and the serialization technique does not change much from type to type. So actually all the numeric types can be serialized using an example shown in the first class.

In springframework database abstraction model these classes can be used like that:

...
String sql = "select * from test.array_accepting_procedure( :text_array_param, :int_array_param)";

MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("text_array_param", new PostgreSQLTextArray(dto.getTextArray()),    java.sql.Types.ARRAY );
namedParameters.addValue("int_array_param",  new PostgreSQLInt4Array(dto.getIntegerArray()), java.sql.Types.ARRAY );

resultList =  getSimpleJdbcTemplate().queryForList( sql, namedParameters, mapper);
...

Passing arrays to PostgreSQL database from java (JDBC) by Valentine Gogichashvili is licensed under a Creative Commons Attribution 3.0 Unported License.

Table partitioning automation triggers in PostgreSQL

2008-04-10T14:20:00.001+02:00

Table partitioning is described in the Postgres documentation Partitioning chapter. Unfortunately until partition data distribution is done automatically in some future version of the Postgres we need some triggers to handle partitioning automatically.

Here is one such example trigger script, that can be useful when developing a tailor made one:

CREATE OR REPLACE FUNCTION myschema.ruled_indexed_partition_multiplexer_by_view_day()
RETURNS TRIGGER AS 
$BODY$
-- $Header: $
/**
* This is a common trigger function that can be used to partition any table 
* that has a VIEW_DAY partitioning column.
* This function will only work on BEFORE INSERT row level triggers.
* If the first parameter is specified, it can only be 'week' or 'month' 
* to indicate the needed partitioning schedule.
*/
DECLARE
  schema_name_prefix CONSTANT text := quote_ident( TG_TABLE_SCHEMA ) || '.';
  table_name_prefix CONSTANT text := TG_TABLE_NAME || '_';
  needed_month_table_name text;
  partitioning_interval CONSTANT text := coalesce( TG_ARGV[0], 'week' );
  s text;
BEGIN
  if not ( TG_WHEN = 'BEFORE' and TG_LEVEL = 'ROW' and TG_OP = 'INSERT' ) then 
    raise exception 'This trigger function can only be used with BEFORE INSERT row level triggers!';
  end if;
  -- raise info 'starting partition_multiplexer for %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
  if new.view_day is null then 
    raise exception 'partitioning column "view_day" cannot be NULL';
  end if;

  needed_month_table_name := 
myschema_partitions.need_ruled_indexed_partition_table(
TG_TABLE_SCHEMA, 
"name" 'myschema_partitions', 
TG_TABLE_NAME, 
"name" 'view_day', 
new.view_day, partitioning_interval );

  -- raise info 'needed_month_table_name is %', needed_month_table_name;
  select new into s;
  s := $$INSERT INTO myschema_partitions.$$ || needed_month_table_name || 
  $$ SELECT ($$ || quote_literal( s ) || $$::$$ || 
  schema_name_prefix || TG_TABLE_NAME || $$).*  $$;
  -- raise info 'executing statement [%]', s;
  EXECUTE s;
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

The trigger function that will use a VIEW_DAY table column of type DATE and can be assigned to a root table with the following command:

CREATE TRIGGER mytable_multiplexer_trigger
BEFORE INSERT
ON myschema.mytable
FOR EACH ROW
EXECUTE PROCEDURE myschema.ruled_indexed_partition_multiplexer_by_view_day('week');

The function uses an additional helper function that creates a needed partition table when it is needed and creates a INSTEAD INSERT rule, that will prevent the system from calling this trigger (that is actually doing at least one catalog look-up for every inserted record) again, if the table already exists. The rules probably should be dropped by hand for the older partitions, so the planner does not have to check too many rule conditions when rewriting the original insert statement, trying to insert into the root table (I suppose here, that we actively insert only into some recent table partitions and when the rule does not exist and we still have to insert something in to an old table the trigger will still work and choose a needed one).

Here is the helper function:

CREATE OR REPLACE FUNCTION myschema_partitions.need_ruled_indexed_partition_table
(TG_TABLE_SCHEMA "name", 
TG_ARCHIVE_SCHEMA "name", 
TG_TABLE_NAME "name", 
partitioning_column_name "name", 
needed_partitioning_date date, 
partitioning_interval text)
RETURNS "name" AS 
$BODY$
/**
* This stored procedure checks if the needed partitioning table exists, as if not,
* it creates it. 
* It also creates all the indexes, that exist on the parent table renaming it
* according to the new partition table name. 
* 
* Be careful about the maximum length of the object name. 
* 
* It is usually to be called from the trigger function like 
* myschema.ruled_indexed_partition_multiplexer_by_view_day()
*
* @param TG_TABLE_SCHEMA - the source (shallow) table schema name
* @param TG_ARCHIVE_SCHEMA - name of the schema, where the partitioning table should be created
* @param TG_TABLE_NAME - the source (shallow) table name
* @param partitioning_column_name - the name of the column, that is used to perform the partitioning (this column should exist in the source table) 
* @param needed_partitioning_date - the value of the partitioning column, this value is used to determine the name of the needed partitioning table
* @param partitioning_interval - partitioning interval. can be 'week' or 'month'
*
* @author Valentine Gogichashvili
*/
DECLARE
partition_beginning_date CONSTANT date := date_trunc( partitioning_interval, needed_partitioning_date )::date;
needed_partition_table_name "name";
BEGIN
-- raise info 'starting partition_multiplexer for %.%, needed table is %, partitioning date is %', TG_TABLE_SCHEMA, TG_TABLE_NAME, needed_partition_table_name, needed_partitioning_date;
-- calculate the name of the needed table
-- we start with the beginning of the week (week partitioning)
needed_partition_table_name := TG_TABLE_NAME || 
to_char( partition_beginning_date, '_YYYYMMDD_') || partitioning_interval;

-- check that the needed table exists on the database
perform 1 
from pg_class, pg_namespace
where relnamespace = pg_namespace.oid 
and relkind = 'r'::"char"
and relname = needed_partition_table_name
and nspname = TG_ARCHIVE_SCHEMA;

if not found then 
DECLARE
archive_schema_name_prefix CONSTANT text := quote_ident( TG_ARCHIVE_SCHEMA ) || '.';
base_schema_name_prefix CONSTANT text := quote_ident( TG_TABLE_SCHEMA ) || '.';
base_table_name CONSTANT text := base_schema_name_prefix || quote_ident( TG_TABLE_NAME );
quoted_column_name CONSTANT text := quote_ident( partitioning_column_name );
partition_beginning_date CONSTANT date := date_trunc( partitioning_interval, needed_partitioning_date )::date;
next_partition_beginning_date date := date_trunc( partitioning_interval, needed_partitioning_date + ( '1 ' || partitioning_interval )::interval )::date;
quoted_needed_table_name CONSTANT text := archive_schema_name_prefix || quote_ident ( needed_partition_table_name );
quoted_rule_name CONSTANT text := quote_ident( 'rule_' || TG_TABLE_NAME || to_char( partition_beginning_date, '_YYYYMMDD') );
base_table_owner name;
s text;
a text;
parent_index_name text;
parent_index_has_valid_name boolean;
BEGIN
SET search_path = myschema_partitions, myschema, public;
-- we have to create a needed table now
-- check if the partitioning date has been passed correctly
if needed_partitioning_date is null then 
raise exception 'partitioning_date should not be NULL';
end if;
-- check if the partitioning interval is correct
-- we check it here and not in the trigger function to improve the performance
if partitioning_interval not in ( 'week', 'month' ) then 
raise exception $$partitioning_interval is set to [%] and should be 'week' or 'month'$$, partitioning_interval;
end if;
-- check for the base table and extract the table owner
select pg_roles.rolname into base_table_owner
from pg_class, pg_namespace, pg_roles
where relnamespace = pg_namespace.oid 
and relkind = 'r'::"char"
and relowner = pg_roles.oid
and relname = TG_TABLE_NAME
and nspname = TG_TABLE_SCHEMA;
if not found then 
raise exception 'cannot find base table %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
end if;
-- now check that the base table contains the partitioning column
perform 1 from information_schema.columns where table_schema = TG_TABLE_SCHEMA and table_name = TG_TABLE_NAME and column_name = partitioning_column_name;
if not found then 
raise exception 'cannot find partitioning column % in the table %.%', quoted_column_name, TG_TABLE_SCHEMA, TG_TABLE_NAME;
end if;

s := $$
CREATE TABLE $$ || quoted_needed_table_name || $$ (
CHECK ( $$ || quoted_column_name || $$ >= DATE $$ || quote_literal( partition_beginning_date ) || $$ AND 
$$ || quoted_column_name || $$ < DATE $$ || quote_literal( next_partition_beginning_date ) || $$ )
) INHERITS ( $$ || base_table_name || $$ ); $$;
raise notice 'creating table as [%]', s;
EXECUTE s;

if coalesce(length(base_table_owner), 0) = 0 then 
raise exception 'base_table_owner is unknown';
end if;
s := $$
ALTER TABLE $$ || quoted_needed_table_name || 
$$ OWNER TO $$ || base_table_owner;
raise notice 'changing owner as [%]', s;
EXECUTE s;

-- extract all the indexes existing on the parent table and apply them to the newly created partition
for a, s, parent_index_name, parent_index_has_valid_name
in  SELECT CASE indisclustered WHEN TRUE THEN 'ALTER TABLE ' || needed_partition_table_name::text || ' CLUSTER ON ' || replace( i.relname, c.relname, needed_partition_table_name::text ) ELSE NULL END as clusterdef,
replace( pg_get_indexdef(i.oid), TG_TABLE_NAME::text, needed_partition_table_name::text ),
i.relname,
strpos( i.relname, TG_TABLE_NAME::text ) > 0
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
WHERE c.relkind = 'r'::"char" 
AND i.relkind = 'i'::"char"
AND n.nspname = TG_TABLE_SCHEMA
AND c.relname = TG_TABLE_NAME
loop
if parent_index_has_valid_name then 
if strpos( s, quote_ident( TG_TABLE_SCHEMA ) || '.' ) then 
raise info 'create index statement contains original schema name, removing it';
s := replace( s, quote_ident( TG_TABLE_SCHEMA ) || '.', '' );
end if;
raise notice 'creating index as [%]', s;
EXECUTE s;
if a is not null then 
if strpos( a, quote_ident( TG_TABLE_SCHEMA ) || '.' ) then 
raise info 'alter index statement contains original schema name, removing it';
a := replace( a, quote_ident( TG_TABLE_SCHEMA ) || '.', '' );
end if;
raise notice 'setting clustering as [%]', a;
EXECUTE a;
end if;
else 
raise exception 'parent index name [%] should contain the name of the parent table [%]', parent_index_name, TG_TABLE_NAME;
end if;
end loop;

-- now we create a rule, that will be assigned to the original table
s := $$
CREATE RULE $$ || quoted_rule_name || $$ AS
ON INSERT TO $$ || base_table_name || $$ 
WHERE ( $$ || quoted_column_name || $$ >= DATE $$ || quote_literal( partition_beginning_date ) || $$ AND 
$$ || quoted_column_name || $$ < DATE $$ || quote_literal( next_partition_beginning_date ) || $$ )
DO INSTEAD
INSERT INTO $$ || quoted_needed_table_name || $$ VALUES (NEW.*);$$;
-- raise notice 'creating a rule as [%]', s;
EXECUTE s;
END;
end if;
return needed_partition_table_name;
END;
$BODY$
LANGUAGE plpgsql strict volatile;

Note: when using inherited tables, to make real use of setting constraint_exclusion on, we have actually to use constant values for partition criteria checks. That means in practice, that we have to always construct SQL statements (not forgetting to use quote_ident() and quote_literal()) and then EXECUTE them (when writing PL/pgSQL code of course)

P.S.: Creation of the partitions on the fly will cause parallel transactions the fail on the moment of creation the tables, but this happens only at that moment and the client should be ready to retry the attempt in case of failure...

PostgreSQL array aggregate

2008-04-04T18:12:00.012+02:00

Interestingly enough, I have only now have found this declaration in the User-Defined Aggregates related Postgres documentation chapter:

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

This array aggregate function is very useful when working with arrays in PostgreSQL and it is not included to the default installation (starting from version 8.4 array_agg() function is available). It can be used as a reverse to the ARRAY(query) construct and sometimes together with generate_series() result set generation function.

Another, sometimes quite important, aggregate function to aggregate text is

CREATE AGGREGATE text_accum (text)
(
sfunc = textcat,
stype = text,
initcond = ''
);

but as it does not allow to insert delimiters in the accumulated text it's usage is quite limited.

To accumulate texts using say a comma as a delimiter array_to_string(array_accum(TEXT_COLUMN_TO_AGGREGATE), ', ') construct can be used (starting from version 9.0 a fast string_agg() is available to do that).

To concatenate several arrays in one aggregated array, very simple aggregate can be used

CREATE AGGREGATE array_accum_cat(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}'
);

This makes it possible to merge several arrays together in one one-dimensional array.

One can find more related information in my other post Merging and Manipulating Arrays in PostgreSQL

Merlin's stuff: Advisory Locks Part 2

2008-01-15T10:38:00.000+01:00

Merlin's stuff: Advisory Locks Part 2

Quite an interesting article about the possibility to use PostgreSQL Advisory Locks

PostgreSQL 8.3 vs. 8.2 - a simple benchmark

2007-12-12T10:16:00.000+01:00

Quite an interesting benchmark results for PostgreSQL 8.3 vs. 8.2