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

[sridhar (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4120?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

sridhar updated HHH-4120:
-------------------------

    Attachment: pg_bug.zip

The following is the enclosed project with sample code.

Run the SubmissiondetailsDAO.java

> 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
>         Attachments: pg_bug.zip
>
>
> 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