Duplicate Postgres database

It is unbelievable how easy is to create copy of Postgres database. The only command you need is

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser

There are 2 possible issues regarding to command above:

1. In some cases database name must be quoted (In my case

   CREATE DATABASE "sirene-migration-testing2" 
     WITH TEMPLATE "sirene-migration-testing" OWNER dbuser;

2. If some other connections are active in source database then error will be thrown (ERROR: source database "sourcedb" is being accessed by other users). There is simple script to kill all other connections in postges database except current connection
   
   In PostgreSQL 9.2 and above, to disconnect everything except your session from the database you are connected to:
   
   SELECT pg_terminate_backend(procpid)
   FROM pg_stat_activity
   WHERE datname = current_database()
     AND procpid <> pg_backend_pid();
   

Virtualbox Ubuntu 12.04 admin privilegs problem

I have Ubuntu 12.04 Virtualbox guest in Win7 

I get admin privilegs problem after executing
sudo usermod -G vboxsf jack

I dont had admin(sudo) privilegs anymore and to fix it I found solution, which needed some improvements

1. boot to recovery mode during Ubuntu Virtualbox startup holding down SHIFT key
2. login as root and execute
3. mount -o remount,rw /
4. usermod -a -G sudo jack
5. reboot

HTC Desire Bravo tweaking

In previous post I wrote about my long way to install Cyanogenmod 7.2.0.1 ROM to HTC Desire Bravo. In this post I wrote about my experience how to tweak Android 2.3.7 further.

1. ADW.Launcher is default launcher application for Cyanogenmod. It's ok launcher but old version was installed so I was not able to add widgets to screen. To upgrade ADW.Launcher to latest version is not easy task as somebody can expect:

• install latest launcher version (trick is that sdcard cannot be used for installation because otherwise launcher is not accessible during bootup and I restored ROM several times before I get it);
• remove old version with Titanium backup, because old version is in /system/app directory, that must be mounted r/w first;
• hold down home button, check "default application" and choose AWD Launcher new version

There is also same problem with widgets as launcher - to be added in list of available widgets these must be installed in phone storage. Side knowledge - its really easy to try/switch to any other application launcher. After downloading launcher press home button and select from list new launcher you have installed.

2. After installation and tweaking I got call from my sister and I was really upset if after call I had black screen and any button does not change this situtation. Only solution was to remove battery and put it back. Fortunately there was also solution to this problem - "working fix for the proximity sensor in-call problem".

3. I want to remove splash screen to stock HTC version and there was solution.

4. There are lot of applications for every single need and its not easy task to find right one. There is list of my favourite tools:

• Superuser - MUST have app, for su access;
• FX with root access - best file explorer with text file editor;
• Titanium Backup - best tool to remove applications;
• ADW.Launcher - lightweight application launcher with lot of configuration options.

5. Conclusion
Mobile phone results after upgrade and tweaking

Cons:

• battery life is shorter (I will try to calibrate)
• phone storage has same limits (so low space problem is soon back again - i dont use Link2SD any more) 

Pros:

•  Android 2.3.7 vs Android 2.2 with better interface (expect contacts) and a lot more options to configure;
• ADW.Launcher vs HTC Sense - nothing to compare;
• tons of widgets vs HTC widgets - you get what you want;
• GPS positioning is working instantly.

Main thing that I learn during last 3 days - how to install custom ROM and recover system. In future I know that nowadays it is quite easy to install custom rom with different tweaks (if you have enough free time :-))

HTC Desire Bravo Cyanogenmod 7.2.0.1 ROM installation

I have never tried install any custom ROM, because I know that after upgrade there can be lot of problems and only internet can help you :-) I thought that it is wise to "not break anything if it works". From other side there were some existing small problems (black screen during call so it was not possible to choose any numbers for answering, old HTC Sense interface, ...) with my phone and if my colleague came and insested to upgrade then I thought - Why not.

Cyanogenmod 7.2.0.1 ROM was ROM that my colleague use and he has very positive experience so it was easy to decide which ROM to install. I installed ROM Manager from Google Play (despite some negative comments) and from ROM Manager I installed ClockMod Recovery and downloaded HTC Desire Bravo Cyanogenmod 7.2.0.1 ROM to my sdcard.

I backup my existing configuration and started upgrade. Problem occurs when Cyanogenmod first time bootup - new splash screen runs and runs and runs ... I was really disappointed and first thing that come to mind was restore from backup. Afterward I know this was mistake, but you must learn from you own experience.

After restoring from backup system bootup, but in really bad shape:
WIFI - ok,
Mobile network - ok,
sdcard - not recognised
usb - unkown device from win7
So I was ready that in worst case I must buy new mobile phone.

In such cases google search is your best friend, but you must be lucky guy to find right article to your problem (and if this article is not in top20 then you will not find it). This day I was lucky guy :-). Article that save my HTC Desire - "USB brick" fix.
After running "fastboot oem enableqxdm 0" and reboot I was able to connect to my sdcard and usb - long step forward :-)

Of course it take much longer to find solution - actually 1 day and there were lot of other things that I try
1. Is sdcard accessible from sdcard reader - of course it was
2. Maybe problem is 2 primary partitions in sdcard - so I do repartioning to 1 4GB FAT32 partition which does not help to recognize my sdcard from phone
3. Some other steps in wrong direction ...

I tried to install Cyanogenmod 7.2.0.1 ROM once again and got same Cyanogenmod start splash screen. Short googling gave me tutorial "How to get out of a bootloop" and it does what it promise!!

From positive side I know much more about Android Custom ROM-s, negative side is that I loose almot 2 days and its not finished yet.

Read next article: HTC Desire Bravo tweaking

Low space problem with my old HTC Desire

I had long time low space problem with my old HTC Desire, so I was forced on to ignore all application updates and new software installation means to remove some existing one. Actually I had accepted my fate and I read reviews to buy new mobile phone until I heard accidentally about Link2SD.


My phone was already rooted and there was excellent tutorial how to install Link2SD software. I have never tried before any custom ROMs so my phone has HTC stock ROM with Android 2.2.Everything go smoothly until I try to run Link2SD first time. It came out that Desire has additional obstacles to copy/move system software - S-OFF.

I find 2 solutions - first one was deprecated and second one was real beta, so not very promising situation :-( My decision was to use older one, because HTC Desire was first phone supported by this software. I followed installation instructions and everything went without problems. There was one side effect - new splash screen instead of HTC "brilliantly quiet" screen, but at this moment it does not disturb me.

So my "low space" problem was resolved and I was able to install additional software to my phone. Afterward I know that this was only small step in my long phone upgrade path :-)

Read next article: HTC Desire Bravo Cyanogenmod 7.2.0.1 ROM installation

Windows7 hotspot

During vacation I was with family in Trysil doing mountain skiing. There was problem with Internet as always in Scandinavia (in Estonia we have free Internet almost everywhere). I buy Cloud Internet for 7 days, but there were more internet devices in our team. It was for me first time to build Internet hotspot with my computer.
I knew how to do it with Android phone, but because I paid with computer there was no possibility to switch internet connection over to phone. After 5 minute googling I found this article.

So solution is simple

1. Run as Administrator cmd.exe
2. netsh wlan set hostednetwork mode=allow ssid=<network_name> key=<password>  keyUsage=persistent
3. netsh wlan start hostednetwork

• Open Control Panel and go to Network and Sharing Center.
• Click Change Adapter Settings.
• Right click your Internet connection and select Properties.
• Click the Sharing tab.
• Check the box that says “Allow other network users to connect.”
• Choose your virtual Wi-Fi adapter and click OK.

The only problem is how to keep network live when computer is not active. The solution to restart network is to run

1. netsh wlan stop hostednetwork
2. netsh wlan start hostednetwork

Performance issue with DBMS_LOB.append in plsql

We have serious performance issue in Estonian Energy live environment where batch process which reads data from database and inserts xml messages to Oracle AQ queue performed astonishing slowly in test environment and even worse in live environment. Whole batch process was written in plsql and it take 1,5days to fix this issue.

Actual problem was that it was really difficult to understand where is problem, because all database select/insert/update statements performed well and it was shortly clear that problem must be somewhere else. But where?

Database administrator pointed to procedure where xml was put together from plain text and DBMS_LOB.append was used to concatenate strings. To make things more complicated there was almost identical process which performed well in all environments. The main difference between these 2 procedures was that in first one there was DBMS_LOB.append called 30 times per xml message and in second (slooow) one 744 times.

I started to investigate in internet about fast text concatenation strategies with CLOB-s in Oracle plsql and find the article from Martin Giffy D'Souza where actual life saver came from last comment(!) (app procedure).

Idea is that to append small text chunks to CLOB use actually to buffers - VARCHAR2(32767) and CLOB. Every time you need to append new chunk to clob you first try to append to varchar2 buffer and if it fails then append varchar2 buffer to clob and assign chunk to varchar2 buffer. In this scenario we will use CLOB.append only cases where text is longer then 32767 and all other cases we use simple text concatenation. Actually I replace CLOB.append with CLOB.writeappend because in test it was little bit faster.

Procedure to implement CLOB/VARCHAR2 concatenation:
 -- fast varchar2/clob text concatenation
  PROCEDURE append_text(io_clob    IN OUT NOCOPY CLOB
                      , io_buffer  IN OUT NOCOPY VARCHAR2
                      , i_text     IN            VARCHAR2) IS
  BEGIN
    io_buffer := io_buffer || i_text;
  EXCEPTION
    WHEN VALUE_ERROR THEN
      IF io_clob IS NULL THEN
        io_clob := io_buffer;
      ELSE
        DBMS_LOB.writeappend(io_clob
                           , LENGTH(io_buffer)
                           , io_buffer);
        io_buffer := i_text;
      END IF;
  END append_text;

Everywhere in my code I replace
DBMS_LOB.append(m_xml, m_tmp);
-- m_xml temporary clob created with DBMS_LOB.createtemporary(m_xml, TRUE);
-- m_tmp varchar2(32767) - xml chunk, what must be appended to end of clob
with

append_text(m_xml, m_buffer, m_tmp);
-- m_buffer procedure varchar2(32767) variable
and additionally before to return clob from function I added this line
DBMS_LOB.writeappend (m_xml, LENGTH(m_buffer), m_buffer);
-- add varchar2 buffer to clob

It came out that only this small change made 4-5times overall performance gain, so we were able to process 80000-90000 source records per minute instead of 20000 records with previous version.

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Final code:
  FUNCTION get_consumption (i_intervaldata_xml_tbl IN t_intervaldata_xml_tbl)
    RETURN CLOB IS
    m_xml                     CLOB;
    m_tmp                     VARCHAR(32767);
    m_buffer                  VARCHAR(32767);    m_in_qty                  VARCHAR(100);
    m_out_qty                 VARCHAR(100);
    m_first_intervaldata_xml_rec t_intervaldata_xml_rec;
    m_last_intervaldata_xml_rec  t_intervaldata_xml_rec;
    m_timezone                VARCHAR2(30);

  BEGIN
 
    DBMS_LOB.createtemporary(m_xml, TRUE);
    m_first_intervaldata_xml_rec := i_intervaldata_xml_tbl(i_intervaldata_xml_tbl.FIRST); 
    m_last_intervaldata_xml_rec := i_intervaldata_xml_tbl(i_intervaldata_xml_tbl.LAST); 
    m_timezone := get_timezone(m_first_intervaldata_xml_rec.intervaldata_type);
 
   m_tmp := get_xml_header() || b_tools.c_eol ||
    '<EnergyReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="EnergyReport.xsd">' || b_tools.c_eol ||
    ' <DocumentIdentification v="' || b_tools.int2str(m_first_intervaldata_xml_rec.document_id) || '"/>' || b_tools.c_eol ||
    ' <SenderIdentification v="'|| g_sender_identification ||'"/>' || b_tools.c_eol ||
    ' <ReceiverIdentification v="'|| g_receiver_identification ||'"/>' || b_tools.c_eol ||
    ' <DocumentDateTime v="' || b_tools.dtime2str(sysdate, 'Y') || '"/>' || b_tools.c_eol ||
    ' <AccountTimeSeries>' || b_tools.c_eol ||
    '  <MeasurementUnit v="'|| g_measurement_unit ||'" />' || b_tools.c_eol ||
    '  <AccountingPoint v="' || m_first_intervaldata_xml_rec.metering_point_eic || '"/>' || b_tools.c_eol ||
    '   <Period>' || b_tools.c_eol ||
    '    <TimeInterval v="' || b_tools.dtime2str(TRUNC(m_first_intervaldata_xml_rec.start_dtime,'HH24'), 'N', m_timezone) || '/' || b_tools.dtime2str(TRUNC(m_last_intervaldata_xml_rec.stop_dtime,'HH24') +  1/24, 'N', m_timezone) || '" />' || b_tools.c_eol ||
    '    <Resolution v="'|| g_resolution ||'" />' || b_tools.c_eol;
    append_text(m_xml, m_buffer, m_tmp);
   
    FOR i IN i_intervaldata_xml_tbl.FIRST .. i_intervaldata_xml_tbl.LAST LOOP
      m_out_qty := case when i_intervaldata_xml_tbl(i).outgoing_qty is null then null else '     <OutQty v="' || b_tools.qty2str(i_intervaldata_xml_tbl(i).outgoing_qty) || '" />' end;
      m_in_qty  := case when i_intervaldata_xml_tbl(i).incoming_qty is null then null else '     <InQty v="'  || b_tools.qty2str(i_intervaldata_xml_tbl(i).incoming_qty) || '" />' end;
      
      m_tmp :=
      '    <AccountInterval>' || b_tools.c_eol ||
      '     <Pos v="' || b_tools.int2str(i_intervaldata_xml_tbl(i).position) || '"/>' || b_tools.c_eol ||
      m_out_qty || b_tools.c_eol ||
      m_in_qty || b_tools.c_eol ||
      '    </AccountInterval>' || b_tools.c_eol;
      append_text(m_xml, m_buffer, m_tmp);
    END LOOP;
   
    m_tmp :=
    '   </Period>' || b_tools.c_eol ||
    '  </AccountTimeSeries>' || b_tools.c_eol ||
    ' </EnergyReport>';
    append_text(m_xml, m_buffer, m_tmp);
    DBMS_LOB.writeappend (m_xml, LENGTH(m_buffer), m_buffer);
    RETURN m_xml;  
  END get_consumption;