Re: Insert into partition table hangs

["Nik" <XLPizza@xxxxxxxxx>]

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