Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id B7D072E0032 for ; Tue, 13 May 2008 00:57:49 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57620-04-4 for ; Tue, 13 May 2008 00:57:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by developer.postgresql.org (Postfix) with ESMTP id 793812E009D for ; Tue, 13 May 2008 00:46:57 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n4so3807221wag.5 for ; Mon, 12 May 2008 20:46:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=Kxxvo+eSTE//40NDjlUNHBFeOhHtvPczjdPc0G0hcSQ=; b=BHgxTJViezNCG4GsRBiCilyo6woY+ZnIx7U+BN/VT/jzhImiNDRbFQwgSBskioqs5aSJMz4Rr2lxU0fc3DqkQb/yadfhsSz2nGFK5zeVyjOXDYrOHB7QIP+k8DCkmUjzGJCPM6tDrSm7GZH4aXlp5WVmQj8yE3qH910Znc1urfU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=OQhw4EksgogMGgxuFwwboZh1w45IuARjJieNalcNbWaQkk9jGdJDM8hylUoxhdmuOx5bNDHnH74KYDjnuVZl1ttjwm8npt+TI0xIThgoA9OpbFV94FHnMxXns+k/6iU7fxhlIxO01uKunLn/v6uOhJmyBoatXWF6P3fLwHEFHM0= Received: by 10.114.120.1 with SMTP id s1mr8603925wac.82.1210650414658; Mon, 12 May 2008 20:46:54 -0700 (PDT) Received: by 10.114.38.1 with HTTP; Mon, 12 May 2008 20:46:54 -0700 (PDT) Message-ID: Date: Mon, 12 May 2008 23:46:54 -0400 From: "Nikolas Everett" To: "Neil Peter Braggio" Subject: Re: Partitioning: INSERT 0 0 but want INSERT 0 1 Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4983_14166436.1210650414651" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.206 tagged_above=0 required=5 tests=AWL=-0.205, HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200805/151 X-Sequence-Number: 30119 ------=_Part_4983_14166436.1210650414651 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline If I can't find an answer in the next day or so I'll crack open OpenJPA and disable that check. Its a very simple, if ugly, hack. --Nik On 5/12/08, 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. > > If you find a better solution please let me know. > > ---- > Neil Peter Braggio > pbraggio@gmail.com > > > On Tue, May 13, 2008 at 11:48 AM, Nikolas Everett > wrote: > > I figure this subject belongs on the performance mailing list because it > is > > about partitioning, which is a performance issue. > > > > I'm working on partitioning some of the tables used by an application > that > > uses OpenJPA. It turns out that OpenJPA is sensitive to the numbers > > returned when you do an insert. So I put together a test and attached > it. > > My postgres version is 8.3.1 compiled from source. > > > > My problem is that this: > > test=> INSERT INTO ttt (a, b) VALUES ('5-5-08', 'test11212'); > > INSERT 0 0 > > Time: 21.646 ms > > needs to show: > > INSERT 0 1 > > > > or OpenJPA will not accept it. The insert works, but OpenJPA does not > > believe it and aborts the current transaction. > > > > Is it possible to have partitioning and have insert show the right > number of > > rows inserted? > > > > Thanks, > > > > --Nik > > > > > > -- > > Sent via pgsql-performance mailing list ( > pgsql-performance@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance > > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > ------=_Part_4983_14166436.1210650414651 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
If I can't find an answer in the next day or so I'll crack open OpenJPA and disable that check.  Its a very simple, if ugly, hack.
 
--Nik

 
On 5/12/08, Neil Peter Braggio <pbraggio@gmail.com> 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.

If you find a better solution please let me know.

----
Neil Peter Braggio
pbraggio@gmail.com


On Tue, May 13, 2008 at 11:48 AM, Nikolas Everett <nik9000@gmail.com> wrote:
> I figure this subject belongs on the performance mailing list because it is
> about partitioning, which is a performance issue.
>
> I'm working on partitioning some of the tables used by an application that
> uses OpenJPA.  It turns out that OpenJPA is sensitive to the numbers
> returned when you do an insert.  So I put together a test and attached it.
> My postgres version is 8.3.1 compiled from source.
>
> My problem is that this:
> test=> INSERT INTO ttt (a, b) VALUES ('5-5-08', 'test11212');
> INSERT 0 0
> Time: 21.646 ms
> needs to show:
> INSERT 0 1
>
> or OpenJPA will not accept it.  The insert works, but OpenJPA does not
> believe it and aborts the current transaction.
>
> Is it possible to have partitioning and have insert show the right number of
> rows inserted?
>
> Thanks,
>
> --Nik
>
>
>  --
>  Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-performance
>
>

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

------=_Part_4983_14166436.1210650414651--