Re: [JDBC] insert data into partitioned tables via Java application

Craig,

Thanks for your reply. I found a work around for this. I
created an insert procedure that will directly insert the data into
partitioned tables based on partition key instead off base table. On the
application side they are calling these procedures to perform inserts.

Thanks,
Cap20.

Craig Ringer wrote:
>
> cap20 wrote:
>> I am trying to do an insert data into EDB partitioned tables via front
>> end
>> application (Java) but the application failing to do an insert. The
>> problem
>> I identified is since the insert happening into partitioned tables in the
>> background when you do an insert into base table, so the insert returning
>> returning Zero rows affected message back to application. So it is
>> failing,
>> is there any work-around for this.
>
> This is somewhat of a frequently asked question, and should probably go
> into the published FAQ. See, for example, the recent thread
> "Partitioning: INSERT 0 0 but want INSERT 0 1".
>
> As far as I can tell there's no way to alter Pg's behaviour so it
> returns the number of rows inserted. This is because there really were
> zero rows inserted into the master table; the rows were inserted into
> other tables instead.
>
> It'd be nice to be able to mark a rule so that it added the number of
> rows affected to the total returned by the query, but this is AFAIK not
> currently possible.
>
> Your app needs to either insert into the real table rather than the
> partition master table or it needs to ignore the number of rows
> affected. If you're using a Java persistence layer it probably offers
> the latter as a configuration option. Alternately, as described by Neil
> Peter Braggio just recently on one of the Pg lists:
>
> Neil Peter Braggio wrote:
>> I have the same problem in PG 8.2
>>
>> To resolve this issue I had to create a new table with the same
>> structure than the partitioned table with a trigger for insert and
>> update. All the operations the application have to do are directed to
>> this new table.
>>
>> When a new record is inserted in the new table the trigger insert a
>> new record with the same values into the partitioned table and then
>> delete all records from this new table. In updates operations the
>> trigger redirect the operation to the partitioned table too.
>>
>> With this _not elegant_ solution our Java application is able to do its
>> job.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

--
View this message in context: http://www.nabble.com/insert-data-into-partitioned-tables-via-Java-application-tp17957653p18203492.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc