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.