Re: How different is AWS-RDS postgres?
> On May 26, 2021, at 10:04 PM, Rob Sargent wrote:
>
>
>
>> On May 26, 2021, at 4:37 PM, Ian Harding wrote:
>>
>>
>> There is an option to send the logs to cloudwatch which makes it less awful
>> to look at them.
> I have that but precious little of interest there. Lots of autovac, a
> smattering of hints to increase wal size!? I have yet to spot anything which
> corresponds to the “I/O failure” which the middle ware gets.
>
> I don’t have query logging on, but I do see reports from my psql session
> fat-fingering.
>
> As to the logs UI, the search is pretty feeble; I don’t understand why there
> are four channels of logs; the graphs are wearing the same rose-coloured as
> the logs.
> And 24 hours without a peep from AWS support. (I don’t call mailing me what I
> sent them “contact”.)
>
> My guess right now is that the entire tomcat connection pool is in a single
> transaction? That’s the only way the tables could disappear. I am making
> separate calls to JDBC getConnection () for each doPost.
We used Aurora (AWS hosted Postgres) and I agree that Cloudwatch search is
pretty limited. I wrote a Python script to download cloudwatch logs to my
laptop where I can use proper tools like grep to search them. It’s attached to
this email. It’s hacky but not too terrible. I hope you find it useful.
Cheers
Philip
import pathlib
import operator
import logging
from collections import namedtuple
import subprocess
import datetime
import json
import boto3
DB_IDENTIFIER = 'your-db-name-here'
PATH = './logs'
Config = namedtuple('Config', ['access_key', 'secret_key', 'region', 'db_identifier', 'rds_client'])
boto_session = boto3.session.Session()
config = Config(
access_key=boto_session._session.get_credentials().access_key,
secret_key=boto_session._session.get_credentials().secret_key,
region=boto_session._session.get_config_variable('region'),
db_identifier=DB_IDENTIFIER,
rds_client=boto_session.client('rds'),
)
class LogFile:
def __init__(self, aws_name, timestamp, size):
self.aws_name = aws_name
self.last_written = datetime.datetime.fromtimestamp(timestamp / 1000)
self.size = int(size)
# typical aws_name = error/postgresql.log.2019-06-21-16
self.local_path = pathlib.Path(PATH, pathlib.Path(aws_name).name + '.txt')
def download(self, config):
# aws rds download-db-log-file-portion \
# --db-instance-identifier wylan-sql \
# --log-file-name error/postgresql.log.2019-06-24-14 \
# --no-paginate --output text
cmd = [
'aws',
'rds',
'download-db-log-file-portion',
'--db-instance-identifier',
config.db_identifier,
'--log-file-name',
self.aws_name,
'--no-paginate',
]
with open(self.local_path, 'wb') as f:
self._proc = subprocess.Popen(cmd, stdout=f)
return_code = self._proc.wait()
if return_code == 0:
# Great, the data were written. It's actually in JSON format. All of the interesting
# info is in the LogFileData element. Grab that and replace the file contents with it.
with open(self.local_path) as f:
d = json.load(f, encoding='utf-8')
log_text = d['LogFileData']
with open(self.local_path, 'w') as f:
f.write(log_text)
else:
# FIXME provide a more helpful exception
raise ValueError
def _get_log_files(config, root_directory):
result = config.rds_client.describe_db_log_files(DBInstanceIdentifier=config.db_identifier)
# FIXME filter out logs where 'Size' == 0?
rds_logs = [LogFile(d['LogFileName'], d['LastWritten'], d['Size'])
for d in result['DescribeDBLogFiles']]
# rds_logs[0].download(config)
# import pprint; pp=pprint.pprint
# import pdb; pdb.set_trace()
rds_logs.sort(key=lambda rds_log: rds_log.aws_name)
if not rds_logs:
print('No RDS logs found')
else:
for rds_log in rds_logs:
print(f'downloading {rds_log.aws_name}...')
rds_log.download(config)
if __name__ == '__main__':
_get_log_files(config, None)
Re: How different is AWS-RDS postgres?
> We used Aurora (AWS hosted Postgres) and I agree that Cloudwatch search is > pretty limited. I wrote a Python script to download cloudwatch logs to my > laptop where I can use proper tools like grep to search them. It’s attached > to this email. It’s hacky but not too terrible. I hope you find it useful. > > Cheers > Philip Thank you. > > >
Re: How different is AWS-RDS postgres?
On 5/27/21 7:45 AM, Philip Semanchuk wrote: On May 26, 2021, at 10:04 PM, Rob Sargent wrote: On May 26, 2021, at 4:37 PM, Ian Harding wrote: There is an option to send the logs to cloudwatch which makes it less awful to look at them. I have that but precious little of interest there. Lots of autovac, a smattering of hints to increase wal size!? I have yet to spot anything which corresponds to the “I/O failure” which the middle ware gets. I don’t have query logging on, but I do see reports from my psql session fat-fingering. As to the logs UI, the search is pretty feeble; I don’t understand why there are four channels of logs; the graphs are wearing the same rose-coloured as the logs. And 24 hours without a peep from AWS support. (I don’t call mailing me what I sent them “contact”.) My guess right now is that the entire tomcat connection pool is in a single transaction? That’s the only way the tables could disappear. I am making separate calls to JDBC getConnection () for each doPost. We used Aurora (AWS hosted Postgres) and I agree that Cloudwatch search is pretty limited. I wrote a Python script to download cloudwatch logs to my laptop where I can use proper tools like grep to search them. It’s attached to this email. It’s hacky but not too terrible. I hope you find it useful. Cheers Philip I may have found another difference: JDBC connections are not logged?! I just reproduce my report, and the CloudWatch view of the logs shows some psql interaction from before and after the test, but no mention of losing 7.5M records.
Re: How different is AWS-RDS postgres?
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. On Thu, May 27, 2021 at 2:01 PM Rob Sargent wrote: > On 5/27/21 7:45 AM, Philip Semanchuk wrote: > > On May 26, 2021, at 10:04 PM, Rob Sargent > wrote: > > > > > On May 26, 2021, at 4:37 PM, Ian Harding > wrote: > > > There is an option to send the logs to cloudwatch which makes it less awful > to look at them. > > I have that but precious little of interest there. Lots of autovac, a > smattering of hints to increase wal size!? I have yet to spot anything which > corresponds to the “I/O failure” which the middle ware gets. > > I don’t have query logging on, but I do see reports from my psql session > fat-fingering. > > As to the logs UI, the search is pretty feeble; I don’t understand why there > are four channels of logs; the graphs are wearing the same rose-coloured as > the logs. > And 24 hours without a peep from AWS support. (I don’t call mailing me what I > sent them “contact”.) > > My guess right now is that the entire tomcat connection pool is in a single > transaction? That’s the only way the tables could disappear. I am making > separate calls to JDBC getConnection () for each doPost. > > We used Aurora (AWS hosted Postgres) and I agree that Cloudwatch search is > pretty limited. I wrote a Python script to download cloudwatch logs to my > laptop where I can use proper tools like grep to search them. It’s attached > to this email. It’s hacky but not too terrible. I hope you find it useful. > > Cheers > Philip > > > > I may have found another difference: JDBC connections are not logged?! > I just reproduce my report, and the CloudWatch view of the logs shows some > psql interaction from before and after the test, but no mention of losing > 7.5M records. > >
Re: How different is AWS-RDS postgres?
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 >= '3000----\ ' and s.probandset_id < '4000----' ]; 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 - bulk."rjs_GEV15_01_c9e224ca_85d2_40b4_ad46_327cfb9f0ac6": Begin transfer from bulk to segment 27-May-2021 20:02:50.338 FINE [Catalina-utility-2] org.apache.catalina.session.ManagerBase.processExpires Start expire sessions StandardManager at 1622145770338 sessioncount 0 27-May-2021 20:02:50.338 FINE [Catalina-utility-2] org.apache.catalina.session.ManagerBase.processExpires End expire sessions StandardManager processingTime 0 expired sessions: 0 27-May-2021 20:02:50.476 FINE [Catalina-utility-2] org.apache.catalina.session.ManagerBase.processExpires Start expire sessions StandardManager at 1622145770476 sessioncount 1 27-May-2021 20:02:50.476 FINE [Catalina-utility-2] org.apache.catalina.session.ManagerBase.processExpires End expire sessions StandardManager processingTime 0 expired sessions: 0 27-May-2021 20:02:51.847 WARNING [Tomcat JDBC Pool Cleaner[1731185718:1622133381802]] org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been abandoned PooledConnection[org.postgresql.jdbc.PgCo\ nnection@1622ead9]:java.lang.Exception at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1163) at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:816) at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:660) at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:198) at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:132) at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:90) at edu.utah.camplab.servlet.AbstractSGSServlet.getDbConn(AbstractSGSServlet.java:123) at edu.utah.camplab.servlet.AbstractSGSServlet.getDbConn(AbstractSGSServlet.java:105) at edu.utah.camplab.servlet.PayloadSaveServlet.doPost(PayloadSaveServlet.java:50) 20:02:51.854 [https-jsse-nio-10.0.2.28-15002-exec-7] DEBUG org.jooq.impl.DefaultConnectionProvider - rollback to savepoint 20:02:51.855 [https-jsse-nio-10.0.2.28-15002-exec-7] DEBUG org.jooq.impl.DefaultConnectionProvider - rollback 20:02:51.855 [https-jsse-nio-10.0.2.28-15002-exec-7] DEBUG org.jooq.impl.DefaultConnectionProvider - setting auto commit : true 20:02:51.855
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 >= '3000----\ ' and s.probandset_id < '4000----' ]; 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.
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 >= '3000----\ ' and s.probandset_id < '4000----' ]; 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.
Re: How different is AWS-RDS postgres?
That sure looks like something is causing your connection to have a transaction rollback. I haven't worked in Java in far too long, but it seems like your connection pool is under the impression your connection was abandoned so it reclaims it and rollback the transaction, which would explain why you aren't seeing the table when all is said and done - all of the work is being undone at the end. One possibility, based on the catalina log you provided - if you have either end of the connection set up to automatically close idle connections after a period of time, then you might receive a closed connection from the pool, which will just error out when you attempt to run a query. In which case, you need to set up your connection pool to test a connection before it returns it to the requester. Usually something as simple as "select 2" will be sufficient to determine if the database connection is open. I can just about guarantee that your connection pool has a parameter which allows you to specify a query to execute when a connection is requested. On Thu, May 27, 2021 at 2: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 >= '3000----\ > ' and s.probandset_id < '4000----' ]; 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 - > bulk."rjs_GEV15_01_c9e224ca_85d2_40b4_ad46_327cfb9f0ac6": Begin transfer > from bulk to segment > 27-May-2021 20:02:50.338 FINE [Catalina-utility-2] > org.apache.catalina.session.ManagerBase.processExpires Start expire > sessions StandardManager at 1622145770338 sessioncount 0 > 27-May-2021 20:02:50.338 FINE [Catalina-utility-2] > org.apache.catalina.session.ManagerBase.processExpires End expire sessions > StandardManager processingTime 0 expired sessions: 0 > 27-May-2021 20:02:50.476 FINE [Catalina-utility-2] > org.apache.catalina.session.ManagerBase.processExpires Start expire > sessions StandardManager at 1622145770476 sessioncount 1 > 27-May-2021 20:02:50.476 FINE [Catalina-utility-2] > org.apache.catalina.session.ManagerBase.processExpires End expire sessions > StandardManager processingTime 0 expired sessions: 0 > 27-May-2021 20:02:51.847 WARNING [Tomcat JDBC Pool > Cleaner[1731185718:1622133381802]] > org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been > abandoned PooledConnection[org.postgresql.jdbc.PgCo\ > nnection@1622ead9]:java.lang.Exception > at > org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1163) > at > org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.ja
Re: How different is AWS-RDS postgres?
On 5/27/21 4:25 PM, Sam Gendler wrote:
That sure looks like something is causing your connection to have a
transaction rollback. I haven't worked in Java in far too long, but
it seems like your connection pool is under the impression your
connection was abandoned so it reclaims it and rollback the
transaction, which would explain why you aren't seeing the table when
all is said and done - all of the work is being undone at the end.
One possibility, based on the catalina log you provided - if you have
either end of the connection set up to automatically close idle
connections after a period of time, then you might receive a closed
connection from the pool, which will just error out when you attempt
to run a query. In which case, you need to set up your connection pool
to test a connection before it returns it to the requester. Usually
something as simple as "select 2" will be sufficient to determine if
the database connection is open. I can just about guarantee that your
connection pool has a parameter which allows you to specify a query to
execute when a connection is requested.
Well I /was/ doing
contextResource.setProperty("validationQuery", "SELECT 1");
but I see that I lost that when I switched to using a properties file.
Thanks for point me there.
The loop of 16 insert statement is in a single transaction, single
connection so I'm not sure who's choking first. Is the connection idle
after the I/O error or is the I/O error from a dead connection? (Small
disclaimer: there is no catalina involved here, just an embedded tomcat
instance.)
How long to get a password reset ???
I have a continuous log feed from my mail server, showing that I've received list eMail to the above eMail address, as recently as this morning. Obviously, eMail is not being blocked at this end. But no responses to a password reset for the above eMail address. Sincerely, Dean
Re: How long to get a password reset ???
On 5/27/21 11:51 AM, Dean Gibson (DB Administrator) wrote: I have a continuous log feed from my mail server, showing that I've received list eMail to the above eMail address, as recently as this morning. Obviously, eMail is not being blocked at this end. But no responses to a password reset for the above eMail address. Password reset from where? Sincerely, Dean -- Adrian Klaver adrian.kla...@aklaver.com
Re: How long to get a password reset ???
On 5/27/21 4:06 PM, Adrian Klaver wrote: On 5/27/21 11:51 AM, Dean Gibson (DB Administrator) wrote: I have a continuous log feed from my mail server, showing that I've received list eMail to the above eMail address, as recently as this morning. Obviously, eMail is not being blocked at this end. But no responses to a password reset for the above eMail address. Password reset from where? I should have added, for your community account or for something else? Sincerely, Dean -- Adrian Klaver adrian.kla...@aklaver.com
Re: How different is AWS-RDS postgres?
Unless something has changed in recent years, the core servlet engine of
tomcat IS catalina. Embedded tomcat is embedded catalina. It looks like
the I/O error is a result of attempting to send a query on an already dead
connection. I'd look for something that is limiting how long a connection
can be open - either an explicit or default value for a timeout in the
connection pool or on the server side. If you don't get the same behaviour
when running against a database locally, I'd definitely look at the default
settings in RDS. It may be automatically closing connections if they are
idle for even a brief period.
On Thu, May 27, 2021 at 3:35 PM Rob Sargent wrote:
> On 5/27/21 4:25 PM, Sam Gendler wrote:
>
> That sure looks like something is causing your connection to have a
> transaction rollback. I haven't worked in Java in far too long, but it
> seems like your connection pool is under the impression your connection was
> abandoned so it reclaims it and rollback the transaction, which would
> explain why you aren't seeing the table when all is said and done - all of
> the work is being undone at the end.
>
> One possibility, based on the catalina log you provided - if you have
> either end of the connection set up to automatically close idle connections
> after a period of time, then you might receive a closed connection from the
> pool, which will just error out when you attempt to run a query. In which
> case, you need to set up your connection pool to test a connection before
> it returns it to the requester. Usually something as simple as "select 2"
> will be sufficient to determine if the database connection is open. I can
> just about guarantee that your connection pool has a parameter which allows
> you to specify a query to execute when a connection is requested.
>
>
> Well I /was/ doing
>
> contextResource.setProperty("validationQuery",
> "SELECT 1");
>
> but I see that I lost that when I switched to using a properties file.
> Thanks for point me there.
>
> The loop of 16 insert statement is in a single transaction, single
> connection so I'm not sure who's choking first. Is the connection idle
> after the I/O error or is the I/O error from a dead connection? (Small
> disclaimer: there is no catalina involved here, just an embedded tomcat
> instance.)
>
>
>
>
>
Re: How different is AWS-RDS postgres?
Not important who does what with whom re Catalina/Tomcat ;)
I will indeed re-examine timeouts and such as inserting 100sK record is not
instantaneous by any stretch. Tomcat is the new kid on my block as prior to
this release I managed a naked port with a Selector and that had no trouble
with this same test data. My biggest concern is the lack of server side
indications. I also need to confirm the table create/bulk-copy and update to
target table (segment) are in separate transactions. Doesn’t look like it, but
they’re supposed to be. I want the data written to the db in bulk, then come
back round and load to the final table in chunks.
> On May 27, 2021, at 5:20 PM, Sam Gendler wrote:
>
> Unless something has changed in recent years, the core servlet engine of
> tomcat IS catalina. Embedded tomcat is embedded catalina. It looks like the
> I/O error is a result of attempting to send a query on an already dead
> connection. I'd look for something that is limiting how long a connection
> can be open - either an explicit or default value for a timeout in the
> connection pool or on the server side. If you don't get the same behaviour
> when running against a database locally, I'd definitely look at the default
> settings in RDS. It may be automatically closing connections if they are
> idle for even a brief period.
>
>> On Thu, May 27, 2021 at 3:35 PM Rob Sargent wrote:
>>> On 5/27/21 4:25 PM, Sam Gendler wrote:
>>> That sure looks like something is causing your connection to have a
>>> transaction rollback. I haven't worked in Java in far too long, but it
>>> seems like your connection pool is under the impression your connection was
>>> abandoned so it reclaims it and rollback the transaction, which would
>>> explain why you aren't seeing the table when all is said and done - all of
>>> the work is being undone at the end.
>>>
>>> One possibility, based on the catalina log you provided - if you have
>>> either end of the connection set up to automatically close idle connections
>>> after a period of time, then you might receive a closed connection from the
>>> pool, which will just error out when you attempt to run a query. In which
>>> case, you need to set up your connection pool to test a connection before
>>> it returns it to the requester. Usually something as simple as "select 2"
>>> will be sufficient to determine if the database connection is open. I can
>>> just about guarantee that your connection pool has a parameter which allows
>>> you to specify a query to execute when a connection is requested.
>>>
>>
>> Well I /was/ doing
>> contextResource.setProperty("validationQuery",
>> "SELECT 1");
>> but I see that I lost that when I switched to using a properties file.
>> Thanks for point me there.
>>
>> The loop of 16 insert statement is in a single transaction, single
>> connection so I'm not sure who's choking first. Is the connection idle
>> after the I/O error or is the I/O error from a dead connection? (Small
>> disclaimer: there is no catalina involved here, just an embedded tomcat
>> instance.)
>>
>>
>>
>>
TRUNCATE memory leak with temporary tables?
I've been seeing what looks like unbounded memory growth (until the OOM killer kicks in and kills the postgres process) when running a pl/pgsql function that performs TRUNCATE statements against various temporary tables in a loop. I think I've been able to come up with some fairly simple reproductions of the issue in isolation, but I'm trying to figure out if this is a memory leak or of I'm perhaps doing something wrong with tuning or other settings. What I've observed: - The memory growth occurs if the temp table has indexes or a primary key set on it. - Alternatively, the memory growth also occurs if the temp table has certain column types on it (eg, "text" types). - If the table doesn't have indexes and only has integer columns present, then the memory growth does *not* occur. - I originally saw this against a PostgreSQL 12 server, but I've tested this against PostgreSQL 9.6.22, 12.7, and 13.3 Docker containers and reproduced it against all versions in the containers. Here are 2 separate examples that seem to show the memory growth on the server (the first being a table with a "text" column, the second example having no text column but a primary key index): DO $$ DECLARE i bigint; BEGIN CREATE TEMPORARY TABLE pg_temp.foo (id integer, bar text); FOR i IN 1..2 LOOP TRUNCATE pg_temp.foo; END LOOP; END $$ DO $$ DECLARE i bigint; BEGIN CREATE TEMPORARY TABLE pg_temp.foo (id integer); ALTER TABLE pg_temp.foo ADD PRIMARY KEY (id); FOR i IN 1..2 LOOP TRUNCATE pg_temp.foo; END LOOP; END $$ Compare that to this example (which doesn't have an index or any other column types that trigger this), which does *not* show any memory growth: DO $$ DECLARE i bigint; BEGIN CREATE TEMPORARY TABLE pg_temp.foo (id integer); FOR i IN 1..2 LOOP TRUNCATE pg_temp.foo; END LOOP; END $$ Any help in determining what's going on here (or if there are other ways to go about this) would be greatly appreciated! Thank you! Nick
Re: How long to get a password reset ???
It's pretty simple: 1. Not having used this mailing list for a while, I went to https://lists.postgresql.org/ to make sure my settings were as I wanted them. 2. I attempted to log in with the above eMail address, which is obviously part of this list, since I receive messages to that address from this list. 3. All known passwords failed. 4. I clicked on the link to the "password reset" form, entered my above eMail address, & pressed "Reset Password." The response was a web page that said I'd soon get an eMail for resetting my password. 5. The rest is history. Meaning zero history (response). And yes, I did it twice to make sure I didn't type the eMail address incorrectly (I cut & pasted the 2nd time). I can try again if it will help (barring the usual definition of insanity). That was six hours ago. Since it looks like I (now) have satisfactory access to the list (I was moderated for a few hours), I will post (in a separate thread) my PostgreSQL issue, & wait for the next full moon (26 days) for my password reset. On 2021-05-27 16:09, Adrian Klaver wrote: On 5/27/21 4:06 PM, Adrian Klaver wrote: On 5/27/21 11:51 AM, Dean Gibson (DB Administrator) wrote: I have a continuous log feed from my mail server, showing that I've received list eMail to the above eMail address, as recently as this morning. Obviously, eMail is not being blocked at this end. But no responses to a password reset for the above eMail address. Password reset from where? I should have added, for your community account or for something else? Sincerely, Dean
