Re: How different is AWS-RDS postgres?

On 5/27/21 4:10 PM, Ron wrote:
> On 5/27/21 4:58 PM, Rob Sargent wrote:
>> On 5/27/21 3:08 PM, Sam Gendler wrote:
>>> The same JDBC connection that is resulting in lost data?  Sounds to
>>> me like you aren't connecting to the DB you think you are connecting to.
>>>
>> I almost wish that were true.
>>
>> However, looking at AWS "Performance Insights" is see the sql
>> statements generate by my app begin executed on the server.  Not
>> coincidentally this is from the "Top SQL (10)/Load by waits" view. 
>> Now that view does NOT, in point of fact, name the database in which
>> the sql is running, but the rest of my environment pretty much rules
>> out silently switching tracks on myself.  I have to read from the
>> correct database, using a UUID, to get data to analyze, then save the
>> results of the analysis back. I'm using my wonderful webapp to run
>> this and I've successfully analyzed and stored result for small
>> starting data sets.
>>
>> I just notice the "Top database" tab on that screen:  there is only
>> one and it's the correct one.
>>
>> I've reproduced the behaviour.  I'm pretty convinced it a size thing,
>> but which part of the system is being max'd out is not clear.  Not
>> CPU, but that's the only resource the "Performance Insight" mentions
>> (suggesting this UI wasn't designed by a database person).
>>
>> The loss of the staging table is most spectacular.  It filled from a
>> file which has 7.5M records. It's clear in the tomcat logs that is
>> was created and written to, one record read.  Then the "top sql"
>> happens:
>>
>> insert into segment select * from
>> bulk."rjs_GEV15_01_c9e224ca_85d2_40b4_ad46_327cfb9f0ac6" as s
>> where s.probandset_id >= ? and s.probandset_id < ?
>>
>> the "bulk" table is the staging table. The params are filled in with
>> a quasi-uuid which grab roughly 1/16th of the data.  In the stack
>> trace on my tomcat server I get
>>
>> Caused by: org.jooq.exception.DataAccessException: SQL [insert
>> into segment select * from
>> bulk."rjs_GEV15_01_c9e224ca_85d2_40b4_ad46_327cfb9f0ac6" as s
>> where  s.probandset_id >= '30000000-0000-0000-0000-00000000\
>> 0000' and s.probandset_id <
>> '40000000-0000-0000-0000-000000000000' ]; An I/O error occurred
>> while sending to the backend.
>>
>> So this would have been the fourth such insert statement read from
>> that staging table.
>>
>> That table is not deleted by my code.  It is renamed after the last
>> insert into segment, by appending "_done" to the name.  But that
>> table, by either name, is nowhere to be seen on the server.
>>
>> For those scoring at home, the trouble in the tomcat log start with
>>
>> 0:02:11.452 [https-jsse-nio-10.0.2.28-15002-exec-7] INFO
>> edu.utah.camplab.jx.PayloadFromMux -
>>
>
> Is there any way to replicate this in psql?
>
I have the json file which feeds the staging table and I have code which
is designed to load such files.  I suppose an sql file with those 16
insert statements would approximate the the apps call.  Let me give that
a shot.

> --
> Angular momentum makes the world go 'round.