[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4120) hibernate failed to insert data into postgres partitioned table.

[sridhar (JIRA)]

hibernate failed to insert data into postgres partitioned table.
----------------------------------------------------------------

                 Key: HHH-4120
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4120
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.3.1
         Environment: RHEL 5.3, Postgres Standard server 8.3, JDK 1.6
            Reporter: sridhar
            Priority: Critical



Hibernate failed to insert data into tables when they are partitioned.
But there is no problem while doing the same operations with JDBC or throw SQL prompt.


The steps to reproduce the problem.

create tablespaces
----------------------------

CREATE TABLESPACE FPSDTS01 LOCATION '/postgres/PostgresPlus/8.3/data/tablespaces/FPSDTS01';
CREATE TABLESPACE FPSDTS02 LOCATION '/postgres/PostgresPlus/8.3/data/tablespaces/FPSDTS02';

create tables as below
-----------------------------------

CREATE TABLE coll_fp_submission_details(

  rrid numeric NOT NULL,
  sid numeric NOT NULL,
  pfid numeric NOT NULL,
  "timestamp" date NOT NULL,
  schema_version numeric NOT NULL,
  details character varying NOT NULL,
  app_txn_id character varying NOT NULL,
  CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)
)
WITH (OIDS=FALSE);

CREATE TABLE coll_fp_subdtls_01
(
  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)
)INHERITS (coll_fp_submission_details)
WITH (OIDS=FALSE)
TABLESPACE fpsdts01;

CREATE TABLE coll_fp_subdtls_02
(
  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)
)
INHERITS (coll_fp_submission_details)
WITH (OIDS=FALSE)
TABLESPACE fpsdts02;


create a function
-----------------------
CREATE OR REPLACE FUNCTION ins_submission_details()
        RETURNS TRIGGER AS $$
          DECLARE
          dateTable TEXT;
          cmd TEXT;
        BEGIN

	IF ((NEW.rrid % 2)= 0) THEN
		dateTable := 'coll_fp_subdtls_01';
	ELSE
		dateTable := 'coll_fp_subdtls_02';

	END IF;

	cmd := 'INSERT INTO ' || dateTable  || '(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||
                ' VALUES (' ||  quote_literal(NEW.rrid) || ',' ||
                            quote_literal(NEW.sid) || ',' ||
                            quote_literal(NEW.pfid) || ',' ||
                            quote_literal(NEW.timestamp) || ',' ||
                            quote_literal(NEW.schema_version) || ',' ||
                            quote_literal(NEW.details) || ',' ||
                            quote_literal(NEW.app_txn_id) || ')';

	EXECUTE cmd;
	RETURN NULL;
        END;
      $$LANGUAGE 'plpgsql';


create a trigger
--------------------
    CREATE TRIGGER trig_ins_submission_details
      BEFORE INSERT ON coll_fp_submission_details
      FOR EACH ROW EXECUTE PROCEDURE ins_submission_details();


now say session.save(submissiondetails)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira