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

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