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 "";
}
}