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 "";
}
}
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
