Passing arrays to PostgreSQL database from java (JDBC)

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

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

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

Advanced Topics in Programming Languages: A Lock-Free Hash Table

The library is located at https://sourceforge.net/projects/high-scale-lib

Advanced Topics in Programming Languages: The Java Memory Model

Merlin's stuff: Advisory Locks Part 2

Merlin's stuff: Advisory Locks Part 2

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

Pointui, the definitive user interface for mobile devices.

Quite an interesting software that makes your PocketPC look much nicer. And, as many say kind of iPhone like.

Pointui, the definitive user interface for mobile devices.

I have installed it on my HTC Touch without any problems or complaints from the side of Windows Mobile.

The interface is not the best one actually (iPhone shit?). It was quite difficult to understand, that you can close the running task by pressing the title of the running application and so opening the task overview screen where there is a special button to close the application. Another this that disturbs by now is that I cannot find a way of using some kind of virtual keyboard to filter big lists (a list of cities in weather applet for example).

Anyways I should thank the development team. The idea is nice, design is perfect and the implementation looks quite stable.