Re: Insert into partition table hangs

I do not run the INSERTs concurrently. They are being executed as the
data comes in sequentially. I attempted to create a stand-alone example
to reproduce the issue, but I was not successful. I will post the
example anyway. This example ran fine, and did not hang at any point,
but it might give a good idea of the actual application mechanics.

-- Database objects
------------------------------

CREATE TABLE schemaname.master_table
(
   id int4 NOT NULL,
   speed float4 NOT NULL,
   "start" timestamptz NOT NULL
) WITHOUT OIDS TABLESPACE tbsname;
ALTER TABLE schemaname.master_table OWNER TO <username>;

CREATE TABLE schemaname.partition_table
(
  CHECK("start" >= '05-01-2006 00:00:00' AND "start" <= '05-31-2006
23:59:59'),
  CONSTRAINT partition_pk PRIMARY KEY (id, speed, "start") USING INDEX
TABLESPACE tbsname
) INHERITS (schemaname.master_table)
WITHOUT OIDS TABLESPACE tbsname;
ALTER TABLE schemaname.partition_table OWNER TO <username>;

CREATE OR REPLACE RULE master_table_insert AS
ON INSERT TO schemaname.master_table WHERE ("start" >= '05-01-2006
00:00:00' AND "start" <= '05-31-2006 23:59:59')
DO INSTEAD
INSERT INTO schemaname.partition_table VALUES
(
    NEW.id,
    NEW.speed,
    NEW."start"
);

-- Java application that inserts data
---------------------------------------------------

import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.TimeZone;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main
{
    public static void main(String[] args)
    {
        System.out.println("Starting the application");

        Statement stmt=null;
        Connection conn=null;
        String query="";
        Long tstamp1=new Long("1148328357");

        try
        {
            Class.forName("org.postgresql.Driver");
            conn=DriverManager.getConnection("jdbc:postgresql://<ipaddress>/<dbname>",
"<username>","<password>");
            stmt=conn.createStatement();
            while(true)
            {
                tstamp1++;
                String tstamp2=convertTimestamp(tstamp1);

                query="INSERT INTO schemaname.master_table VALUES
(1,53.5,'"+tstamp2+"')";

                System.out.println("Inserting "+tstamp2+" ...");
                stmt.execute(query);
                System.out.println("DONE.");
            }
        }
        catch(SQLException e1)
        {
            System.out.println(e1.toString());
        }
        catch(ClassNotFoundException e2)
        {
            System.out.println(e2.toString());
        }
    }

    // Convert the time in seconds from epoch to the timestamptz format
    public static String convertTimestamp(Long timeIn)
    {
        if(timeIn!=null)
        {
            // Generate a date filetype by converting the seconds to
milliseconds from epoch
            Date d = new Date((timeIn.longValue()*1000));

            // Specify the date format
            SimpleDateFormat df = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");

            // Get the date into a string
            String partDate = df.format(d);

            // Get the current timezone
            TimeZone tz = TimeZone.getDefault();

            // Adjust the timezone offset
            int dstOffset =
((((tz.getRawOffset()+tz.getDSTSavings())/1000)/60)/60);

            // Add the seconds to the date
            String result = partDate+" "+dstOffset+":00";

            return result;
        }
        else
            return "";
    }
}