cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Harunobu Oyama ...@telensa.com>
Subject Re: Aggregate function
Date Fri, 15 Jul 2016 16:40:25 GMT
FYI,

the reading table has about 100 millions records now.

nobu



On 15 July 2016 at 17:39, Harunobu Oyama <ho@telensa.com> wrote:

> Michael,
>
> Unfortunately the utility did not work for me.
>
> After entering the max method, it ended up failing by throwing an
> exception like following. after spending 5 to 10 seconds.
> The rest of the program works as before, if I comment out the use of
> AggregateUtils.
>
> Any idea?
>
> nobu
>
>
>
>
>
> //////////////////////////////////////////////////////////////////////////////////////////////////
> // console output snippet
>
> INFO: Opening connection: jdbc:postgresql://127.0.0.1:5432/CayenneTest
> Login: postgres
> Password: *******
> Jul 15, 2016 5:16:15 PM org.apache.cayenne.log.CommonsJdbcEventLogger
> logConnectSuccess
> INFO: +++ Connecting: SUCCESS.
>
>   // Spend some time here and then the exception got thrown
>
> Exception in thread "main" java.lang.RuntimeException: Cannot run MAX query
> at
> cbe.fetching.utilities.AggregateUtils.getResultUsingTranslator(AggregateUtils.java:230)
> at
> cbe.fetching.utilities.AggregateUtils.getResultForFunction(AggregateUtils.java:188)
> at cbe.fetching.utilities.AggregateUtils.max(AggregateUtils.java:85)
> at com.telensa.CayenneTest.main(CayenneTest.java:85)
> Caused by: org.apache.cayenne.CayenneRuntimeException: [v.3.1.1 Apr 29
> 2016 12:50:34] Error detecting database type: Can't obtain connection.
> Request timed out. Total used connections: 1
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:113)
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory$1.get(DefaultDbAdapterFactory.java:84)
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory$1.get(DefaultDbAdapterFactory.java:81)
> at org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:93)
> at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java:81)
> at
> org.apache.cayenne.dba.AutoAdapter.getQuotingStrategy(AutoAdapter.java:206)
> at org.apache.cayenne.access.trans.JoinStack.<init>(JoinStack.java:69)
> at
> org.apache.cayenne.access.trans.SelectTranslator.createJoinStack(SelectTranslator.java:102)
> at
> org.apache.cayenne.access.trans.SelectTranslator.getJoinStack(SelectTranslator.java:83)
> at
> org.apache.cayenne.access.trans.SelectTranslator.createSqlString(SelectTranslator.java:112)
> at
> cbe.fetching.utilities.AggregateTranslator.createSqlString(AggregateTranslator.java:72)
> at
> org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:204)
> at
> cbe.fetching.utilities.AggregateUtils.getResultUsingTranslator(AggregateUtils.java:219)
> ... 3 more
> Caused by: java.sql.SQLException: Can't obtain connection. Request timed
> out. Total used connections: 1
> at
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> ... 15 more
>
>
>
>
> On 15 July 2016 at 16:17, Harunobu Oyama <ho@telensa.com> wrote:
>
>> Hi Michael,
>>
>> Thank you for your message. I will give it a try.
>>
>> As for "reading_id", yes it is the primary key and actually generated by
>> using bigserial.
>> While I was populating 3 billions of those, I wrote a throw-away code to
>> assign the
>> value from the Java code without spending time on sequence query. It did
>> made the
>> data population about 20% faster. The query was meant to be used to find
>> out the
>> initial value, but I even cheated to hard code the value this time.
>>
>> nobu
>>
>>
>> On 15 July 2016 at 15:49, Michael Gentry <blacknext@gmail.com> wrote:
>>
>>> Hi Nobu,
>>>
>>> I have an AggregateUtils that I use:
>>>
>>>
>>> https://github.com/mrg/cbe/tree/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities
>>>
>>> Here is an example of code using AggregateUtils:
>>>
>>>
>>> https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/Aggregates.java
>>>
>>> To use it for your example would be something like this:
>>>
>>> SelectQuery query = new SelectQuery(Reading.class);
>>> BigDecimal max = AggregateUtils.max(dataContext, query,
>>> READING.READING_ID_PROPERTY);
>>>
>>> On a side note, I'm guessing "reading_id" is your primary key?  If so,
>>> why
>>> are you trying to get the maximum value for it?  Is the primary key/id
>>> not
>>> being generated by the database?
>>>
>>> mrg
>>>
>>>
>>> On Fri, Jul 15, 2016 at 5:56 AM, Harunobu Oyama <ho@telensa.com> wrote:
>>>
>>> > Hi,
>>> >
>>> > What is the Cayenne way to execute aggregate functions?
>>> >
>>> > For example, SELECT MAX("reading_id") FROM "reading" ?
>>> >
>>> > I tried passing SQLTemplate containing the SQL written above
>>> > to performGenericQuery method, with no luck. Are we supposed to use
>>> JDBC?
>>> > If so, what is the best way to get the JDBC Connection?
>>> >
>>> > Any hint would be appreciated.
>>> >
>>> > thank you,
>>> > nobu
>>> >
>>>
>>
>>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message