Friday, August 30, 2013

PostgreSQL. Handling timestamps with timezone

Regarding timestamps and their proper handling, the world of databases is packed with bittersweet stories. With PostgreSQL this issue is rather straightforward if you are aware of the following principles:

- PostgreSQL doesn't store timezone metadata, it converts every timestamp with timezone you throw at it to UTC, and stores it that way.
- If you use timestamp without timezone fields, they're stored using the local timezone valid for the client session, in this way, we know nothing about relative to what that timestamp was stored in the database.
- To present timestamps with timezone to users it converts "on the fly" timestamps to the client's configured timezone.

show timezone;
    TimeZone    
----------------
 Mexico/General

select now();
             now              
------------------------------
 2013-08-30 00:39:55.82538-05

CREATE TABLE test_timestamptz(
id serial primary key,
mytimestamptz timestamp with time zone,
mytimestamp timestamp without time zone
);

INSERT INTO test_timestamptz SELECT 1,now(),now();

select * from test_timestamptz;
 id |        mytimestamptz         |        mytimestamp        
----+------------------------------+---------------------------
  1 | 2013-08-30 00:48:19.55422-05 | 2013-08-30 00:48:19.55422

set timezone = 'UTC';

select * from test_timestamptz;
 id |        mytimestamptz         |        mytimestamp        
----+------------------------------+---------------------------
  1 | 2013-08-30 05:48:19.55422+00 | 2013-08-30 00:48:19.55422

There are some provisions for us not to shoot ourselves in the foot when dealing with timezones, but I'll write about that later.
Cheers!

Friday, August 16, 2013

PostgreSQL. pg_memcache

In high performance applications caching alleviates a great deal of database workload, let's propose a little example using pg_memcache.

Prerequisites:

yum install memcached libmemcached libmemcached-devel
wget 

Download and install pg_memcache

wget http://pgfoundry.org/frs/download.php/3018/pgmemcache_2.0.6.tar.bz2
tar -xzf pgmemcache_2.0.6.tar.bz2
cd pgmemcache
make
sudo make install 

Tuesday, August 6, 2013

Pentaho Data Integration (Kettle) on Fedora 19 x86_64 Core dumped. Problematic frame: C [libsoup-2.4.so.1]

Symptom:
You issue the script spoon.sh on Fedora 19 x86_64, it breaks with the following error:

#
# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00000031e926d9c1, pid=10487, tid=140492000786176
#
# JRE version: 7.0_25-b15
# Java VM: Java HotSpot(TM) 64-Bit Server VM (23.25-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C  [libsoup-2.4.so.1+0x6d9c1]  soup_session_feature_detach+0x11
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# An error report file with more information is saved as:
# /home/darwin/data-integration/hs_err_pid10487.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.sun.com/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#
spoon.sh: line 166: 10487 Aborted                 (core dumped) "$_PENTAHO_JAVA"

Cause:
It turns out that it is an eclipse issue as depicted here:

After updating to GNOME 3.8 with libsoup 2.42.0, Eclipse reliably crashes after a few seconds of using:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=405786

Solution:
the workaround is modifying your spoon.sh file as follows

OPT="$OPT $PENTAHO_DI_JAVA_OPTIONS -Djava.library.path=$LIBPATH -DKETTLE_HOME=$KETTLE_HOME -DKETTLE_REPOSITORY=$KETTLE_REPOSITORY -DKETTLE_USER=$KETTLE_USER -DKETTLE_PASSWORD=$KETTLE_PASSWORD -DKETTLE_PLUGIN_PACKAGES=$KETTLE_PLUGIN_PACKAGES -DKETTLE_LOG_SIZE_LIMIT=$KETTLE_LOG_SIZE_LIMIT -Dorg.eclipse.swt.browser.DefaultType=mozilla"

Note that the thing you need to add to the JAVA options is:
 -Dorg.eclipse.swt.browser.DefaultType=mozilla

Should problems still arise, try adding this option too:
 -Dorg.eclipse.swt.browser.XULRunnerPath=/usr/lib64/xulrunner/
or the equivalent path in your system to xulrunner

and that's it, I hope it helps.