<div dir="ltr">@msavy - After looking at the code a bit I have no idea why hibernate is doing this. :( I can't see anything in there that would be problematic based on my current level of understanding of hibernate. That said, the root of the issue is here:<div><br></div><div><a href="https://github.com/apiman/apiman/blob/master/manager/api/rest-impl/src/main/java/io/apiman/manager/api/rest/impl/OrganizationResourceImpl.java#L3446-L3451">https://github.com/apiman/apiman/blob/master/manager/api/rest-impl/src/main/java/io/apiman/manager/api/rest/impl/OrganizationResourceImpl.java#L3446-L3451</a><br></div><div><br></div><div>Clearly no Tx is required to simply read data, so I presume the .beginTx() and .commitTx() calls are not required. Without that, no data would ever get written back to the DB (which in this case is what we want). Of course, that's only true of JPA and not necessarily every storage layer. I think perhaps we have two choices:</div><div><br></div><div>1) Remove the .beginTx() and .commitTx() calls for ALL read-only operations. This should work for both existing storage mechanisms now (JPA and ES[1]).</div><div>2) Add a flag to IStorage::beginTx() to indicate whether it should be read-only or not: </div><div><br></div><div> public void beginTx(boolean readOnly) throws StorageException;</div><div><br></div><div>For #2, the JPA implementation would simply *not* create a transaction if the readOnly flag was true.</div><div><br></div><div>Lastly, we should probably *also* add a unique constraint to the pd_templates table. :)</div><div><br></div><div>-Eric</div><div><br></div><div><br></div><div>[1] - the Elasticsearch implementation ignores the Tx related calls because it does not support transactions</div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Jun 6, 2017 at 8:05 AM, Eric Wittmann <span dir="ltr"><<a href="mailto:eric.wittmann@redhat.com" target="_blank">eric.wittmann@redhat.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Great thanks! Now we just need to figure out why the heck that would happen...<div><br></div><div>Perhaps the ORM gods are angry. An offering will be required.</div><div><br></div><div>Note: I wonder what would happen if we put a unique constraint on pd_templates (combination of id and lang columns). That could be a workaround until we found the root cause.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>-Eric</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Jun 6, 2017 at 2:37 AM, Andy Yar <span dir="ltr"><<a href="mailto:andyyar66@gmail.com" target="_blank">andyyar66@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I've enabled logging of org.hibernate.SQL. Now I can see inserts to<br>
the "pd_templates" in my server.log:<br>
<a href="https://hastebin.com/zovefusoyo.sql" rel="noreferrer" target="_blank">https://hastebin.com/zovefusoy<wbr>o.sql</a>. These are triggered when browsing<br>
an OAuth policy.<br>
<br>
The "policydefs" table seems to be OK - only a single record for the<br>
Keycloak OAuth.<br>
<div class="m_-6118064526170842431HOEnZb"><div class="m_-6118064526170842431h5"><br>
On Fri, Jun 2, 2017 at 2:50 PM, Eric Wittmann <<a href="mailto:eric.wittmann@redhat.com" target="_blank">eric.wittmann@redhat.com</a>> wrote:<br>
> This is a very strange problem. I'll add some technical info to this<br>
> discussion in case it helps.<br>
><br>
> The "pd_templates" table stores potentially multiple html templates for<br>
> displaying the *summary* of a configured instance of a policy. It can store<br>
> multiple templates by language - this is basically an i18n feature. In<br>
> practice, we only really have English language versions, so there should<br>
> only be one row in that table for each row in the "policydefs" table. The<br>
> "policydefs" table should have one row per policy definition (basically one<br>
> row for each item in the Policy Type drop-down when adding a new policy to<br>
> an API).<br>
><br>
> The ORM class for the pd_templates table is here:<br>
><br>
> <a href="https://github.com/apiman/apiman/blob/master/manager/api/beans/src/main/java/io/apiman/manager/api/beans/policies/PolicyDefinitionTemplateBean.java" rel="noreferrer" target="_blank">https://github.com/apiman/apim<wbr>an/blob/master/manager/api/<wbr>beans/src/main/java/io/apiman/<wbr>manager/api/beans/policies/Pol<wbr>icyDefinitionTemplateBean.java</a><br>
><br>
> And the policy definition ORM bean is here:<br>
><br>
> <a href="https://github.com/apiman/apiman/blob/master/manager/api/beans/src/main/java/io/apiman/manager/api/beans/policies/PolicyDefinitionBean.java" rel="noreferrer" target="_blank">https://github.com/apiman/apim<wbr>an/blob/master/manager/api/<wbr>beans/src/main/java/io/apiman/<wbr>manager/api/beans/policies/<wbr>PolicyDefinitionBean.java</a><br>
><br>
> This sounds like hibernate making a mess of things (or more likely my<br>
> mis-understanding of something hibernate is doing). It's one of the reasons<br>
> I'm on the fence about ORM technologies in general. They can be so helpful,<br>
> but if you wrote your own SQL statements you'd never have a weird issue like<br>
> this.<br>
><br>
> In any case, at this point I have no theory about how this could be<br>
> happening.<br>
><br>
> I assume your "policydefs" table is sensible. So the question is why<br>
> hibernate would be duplicating the data in "pd_templates", especially when<br>
> doing a simple GET of the information (shouldn't be mutating anything!).<br>
><br>
> Could try enabling SQL logging in hibernate to see what's going on.<br>
><br>
> But of course trying to replicate the problem locally is always the best<br>
> approach. :)<br>
><br>
> -Eric<br>
><br>
><br>
> On Fri, Jun 2, 2017 at 7:49 AM, Marc Savy <<a href="mailto:marc.savy@redhat.com" target="_blank">marc.savy@redhat.com</a>> wrote:<br>
>><br>
>> Hi Andy,<br>
>><br>
>> Can you describe how your API, ClientApp & Plan is set up?<br>
>><br>
>> e.g.<br>
>><br>
>> API = [Keycloak OAuth2 Policy]<br>
>> Plan = [<nothing>]<br>
>> ClientApp = [Header Policy]<br>
>><br>
>><br>
>> Regards,<br>
>> Marc<br>
>><br>
>> On 2 June 2017 at 12:20, Andy Yar <<a href="mailto:andyyar66@gmail.com" target="_blank">andyyar66@gmail.com</a>> wrote:<br>
>> > Oh, sorry I missed version info.<br>
>> ><br>
>> > psql (PostgreSQL) 9.4.12<br>
>> > JDBC connector - PostgreSQL 9.4.1212<br>
>> > Apiman 1.2.9.Final<br>
>> > CentOS7<br>
>> > Oracle Java 1.8.0_111<br>
>> ><br>
>> > No export/import was done prior this issue.<br>
>> ><br>
>> > I can say I've been observed slower and slower responses during<br>
>> > operation with the Manager OAuth related pages.<br>
>> ><br>
>> > The trigger action seems to be a GET on a URL pattern like this:<br>
>> > apimanui/api-manager/orgs/MyOr<wbr>g/plans/MyOrgKeycloakOAuth/1/<wbr>policies/1155<br>
>> ><br>
>> > Resulting screen loads a policy config scheme from Keycloak OAuth<br>
>> > Plugin and displays it. It also generates N^2 new rows in<br>
>> > pd_templates. I've observed 16, 32, 64, 128 and then 256 added new<br>
>> > rows...<br>
>> ><br>
>> > Thanks<br>
>> ><br>
>> ><br>
>> > On Fri, Jun 2, 2017 at 12:26 PM, Marc Savy <<a href="mailto:marc.savy@redhat.com" target="_blank">marc.savy@redhat.com</a>> wrote:<br>
>> >> Hi Andy,<br>
>> >><br>
>> >> I spent time yesterday evening trying to replicate this after our chat<br>
>> >> on IRC, but I haven't been able to trigger it. Perhaps with more<br>
>> >> information we can narrow this down.<br>
>> >><br>
>> >> Which version of Postgres are you using? Which driver version?<br>
>> >><br>
>> >> I've been using: Postgres 9.6.2, Driver 42.1.1, apiman 1.3.0.Final<br>
>> >> (should be same as 1.2.9.Final for this).<br>
>> >><br>
>> >> Have you done any export-import?<br>
>> >><br>
>> >> Have you observed the precise action that corresponds with the<br>
>> >> extraneous pd_templates entries being inserted?<br>
>> >><br>
>> >> Regards,<br>
>> >> Marc<br>
>> >><br>
>> >> On 2 June 2017 at 08:05, Andy Yar <<a href="mailto:andyyar66@gmail.com" target="_blank">andyyar66@gmail.com</a>> wrote:<br>
>> >>><br>
>> >>> Hello,<br>
>> >>> Keycloak OAuth plugin's presence generates a certain amount of records<br>
>> >>> to pd_templates table on each action of Apiman Manager related to a<br>
>> >>> Keycloak OAuth policy (even read-only ones like listing a plan's<br>
>> >>> policies, etc.).<br>
>> >>><br>
>> >>> Over time the number of records in pd_templates table can grow to<br>
>> >>> milions resulting in Apiman Manager OoM exceptions. Given these<br>
>> >>> records are basically just text hints it is really funny.<br>
>> >>><br>
>> >>> A workaround is to periodically dedup the records.<br>
>> >>><br>
>> >>> I guess this issue should be an easy fix.<br>
>> >>><br>
>> >>> Affected version is: Apiman 1.2.9.Final + corresponding Keycloak OAuth<br>
>> >>> plugin.<br>
>> >>> ______________________________<wbr>_________________<br>
>> >>> Apiman-user mailing list<br>
>> >>> <a href="mailto:Apiman-user@lists.jboss.org" target="_blank">Apiman-user@lists.jboss.org</a><br>
>> >>> <a href="https://lists.jboss.org/mailman/listinfo/apiman-user" rel="noreferrer" target="_blank">https://lists.jboss.org/mailma<wbr>n/listinfo/apiman-user</a><br>
>> ______________________________<wbr>_________________<br>
>> Apiman-user mailing list<br>
>> <a href="mailto:Apiman-user@lists.jboss.org" target="_blank">Apiman-user@lists.jboss.org</a><br>
>> <a href="https://lists.jboss.org/mailman/listinfo/apiman-user" rel="noreferrer" target="_blank">https://lists.jboss.org/mailma<wbr>n/listinfo/apiman-user</a><br>
><br>
><br>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>