Streaming large result sets from an RDS postgres read replica and sqlalchemy terminates prematurely
I'm trying to run a large query using SQLAlchemy with the following code on a Postgres 9.4 RDS server set up as a read replica.
# self.sa_engine is a SQLAlchemy engine
with self.sa_engine.connect() as conn:
conn = conn.execution_options(stream_results=True)
# pd = pandas
# self.sql = "select * from mylargetable"
for chunk in pd.read_sql(self.sql, conn, chunksize=50000):
# do stuff, write file, etc....
The problem is that I get the following error after about 30-60 seconds. During this time, files are being written as expected.
TransactionRollbackError: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Everything I've googled says to set the following parameters on the read replica in RDS:
hot_standby_feedback=1
max_standby_archive_delay=600000
max_standby_streaming_delay=600000
With these parameters set, I would expect to get the above error only if the query ran for longer than 10 minutes, but I'm getting it after 30-60 seconds.
Additionally, my understanding of this issue is that it would only occur if the table in the master database was being modified while the query on the replica is running. However, this table hasn't been updated in months.
All of this works when I run it against the master database (which I can't do in production) and when I run it against smaller tables on the read replica.
I'm totally stumped and would appreciate any help.
postgresql rds amazon-read-replica
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
add a comment |
I'm trying to run a large query using SQLAlchemy with the following code on a Postgres 9.4 RDS server set up as a read replica.
# self.sa_engine is a SQLAlchemy engine
with self.sa_engine.connect() as conn:
conn = conn.execution_options(stream_results=True)
# pd = pandas
# self.sql = "select * from mylargetable"
for chunk in pd.read_sql(self.sql, conn, chunksize=50000):
# do stuff, write file, etc....
The problem is that I get the following error after about 30-60 seconds. During this time, files are being written as expected.
TransactionRollbackError: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Everything I've googled says to set the following parameters on the read replica in RDS:
hot_standby_feedback=1
max_standby_archive_delay=600000
max_standby_streaming_delay=600000
With these parameters set, I would expect to get the above error only if the query ran for longer than 10 minutes, but I'm getting it after 30-60 seconds.
Additionally, my understanding of this issue is that it would only occur if the table in the master database was being modified while the query on the replica is running. However, this table hasn't been updated in months.
All of this works when I run it against the master database (which I can't do in production) and when I run it against smaller tables on the read replica.
I'm totally stumped and would appreciate any help.
postgresql rds amazon-read-replica
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
add a comment |
I'm trying to run a large query using SQLAlchemy with the following code on a Postgres 9.4 RDS server set up as a read replica.
# self.sa_engine is a SQLAlchemy engine
with self.sa_engine.connect() as conn:
conn = conn.execution_options(stream_results=True)
# pd = pandas
# self.sql = "select * from mylargetable"
for chunk in pd.read_sql(self.sql, conn, chunksize=50000):
# do stuff, write file, etc....
The problem is that I get the following error after about 30-60 seconds. During this time, files are being written as expected.
TransactionRollbackError: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Everything I've googled says to set the following parameters on the read replica in RDS:
hot_standby_feedback=1
max_standby_archive_delay=600000
max_standby_streaming_delay=600000
With these parameters set, I would expect to get the above error only if the query ran for longer than 10 minutes, but I'm getting it after 30-60 seconds.
Additionally, my understanding of this issue is that it would only occur if the table in the master database was being modified while the query on the replica is running. However, this table hasn't been updated in months.
All of this works when I run it against the master database (which I can't do in production) and when I run it against smaller tables on the read replica.
I'm totally stumped and would appreciate any help.
postgresql rds amazon-read-replica
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
I'm trying to run a large query using SQLAlchemy with the following code on a Postgres 9.4 RDS server set up as a read replica.
# self.sa_engine is a SQLAlchemy engine
with self.sa_engine.connect() as conn:
conn = conn.execution_options(stream_results=True)
# pd = pandas
# self.sql = "select * from mylargetable"
for chunk in pd.read_sql(self.sql, conn, chunksize=50000):
# do stuff, write file, etc....
The problem is that I get the following error after about 30-60 seconds. During this time, files are being written as expected.
TransactionRollbackError: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Everything I've googled says to set the following parameters on the read replica in RDS:
hot_standby_feedback=1
max_standby_archive_delay=600000
max_standby_streaming_delay=600000
With these parameters set, I would expect to get the above error only if the query ran for longer than 10 minutes, but I'm getting it after 30-60 seconds.
Additionally, my understanding of this issue is that it would only occur if the table in the master database was being modified while the query on the replica is running. However, this table hasn't been updated in months.
All of this works when I run it against the master database (which I can't do in production) and when I run it against smaller tables on the read replica.
I'm totally stumped and would appreciate any help.
postgresql rds amazon-read-replica
postgresql rds amazon-read-replica
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
asked Nov 27 '18 at 1:42
Sterling ParamoreSterling Paramore
128212
128212
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Right after asking this question I searched for parameters that were set to 30 seconds by default and found the solution. There's one more parameter I had to adjust:
wal_receiver_timeout=600000
Setting that did the trick!
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53491571%2fstreaming-large-result-sets-from-an-rds-postgres-read-replica-and-sqlalchemy-ter%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Right after asking this question I searched for parameters that were set to 30 seconds by default and found the solution. There's one more parameter I had to adjust:
wal_receiver_timeout=600000
Setting that did the trick!
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
add a comment |
Right after asking this question I searched for parameters that were set to 30 seconds by default and found the solution. There's one more parameter I had to adjust:
wal_receiver_timeout=600000
Setting that did the trick!
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
add a comment |
Right after asking this question I searched for parameters that were set to 30 seconds by default and found the solution. There's one more parameter I had to adjust:
wal_receiver_timeout=600000
Setting that did the trick!
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
Right after asking this question I searched for parameters that were set to 30 seconds by default and found the solution. There's one more parameter I had to adjust:
wal_receiver_timeout=600000
Setting that did the trick!
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
answered Nov 27 '18 at 2:54
Sterling ParamoreSterling Paramore
128212
128212
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
add a comment |
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
That's surprising. It must be something specific to RDS, because according to the documentation it should just terminate the replication connection between primary and standby if the former goes down, not interrupt long queries.
– Laurenz Albe
Nov 27 '18 at 3:27
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
Yeah, another strange thing about this situation is that I had a similar process running using Ruby postgres libraries and had no problem. I think there's something about the mechanism by which psycopg2 is setting up the streaming that is causing it.
– Sterling Paramore
Nov 27 '18 at 16:46
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53491571%2fstreaming-large-result-sets-from-an-rds-postgres-read-replica-and-sqlalchemy-ter%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
