Re: How different is AWS-RDS postgres?

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?

-- 
Angular momentum makes the world go 'round.