Thread: Connection gets into state where all queries fail
OK, I'm using a pool of worker threads that each have a connection (from my
own software, no PHP or anything like that involved), and a connection is in
a state where all queries fail. Looking back through the log I find the
first entry where this connection has an error (there are successful queries
on it prior, so it's not the first query):
2004-06-21 14:51:19 [5589] LOG: query: begin; set constraints all
deferred;
insert into "PatientCall_Step"
("id", "PatientCallId", "HandledByStaffId", "AssignedToStaffId", "DoneWhen",
"Summary", "Notes", "ContactIsAnxious", "IsMedicallyUrgent",
"PageMessageId", "AssignToNurseTakingCalls", "AssignNextToNurseTakingCalls")
values (7991, 7774, 944557, 297199, '2004-06-21 19:43:00.000-00', '...',
'...', 'f', 'f', 7992, 'f', 'f');
insert into "PageMessage"
("id", "FromStaffId", "ToStaffId", "PagerNum", "PagerMessage", "Source")
values (7992, 944557, 297199, '7872', 'bogus value', 'PatientCall');
commit;
2004-06-21 14:51:19 [5589] ERROR: value too long for type character
varying(80)
I've removed the actual varchar values, because the data is confidential.
Suffice it to say that there's a hole in the UI, and where the above says
'bogus value' there was indeed a value too long for the column, and the
value seems to have had an embedded CR toward the end. So the error message
from pg is correct.
But the very next query on that process is a simple one, and it fails like
so:
2004-06-21 14:51:58 [5589] LOG: query: select * from "PatientCall" where
"PatientId" =' 534824';
2004-06-21 14:51:58 [5589] ERROR: current transaction is aborted, queries
ignored until end of transaction block
In fact every transaction since then (nearly 300) on that same process has
failed with the same error. This is 7.3.2 running on OS X (I've tested 7.4
and intend to upgrade RSN.)
So I don't understand the errors. I even tried copying the initial query
from the log and pasting into psql, got the error, and was able to continue
making queries without further errors. Note that this is not a perfect way
to test, because the "embedded CR" might have been switched from CR to LF or
vice versa at some point in the logging/downloading/copying/pasting process.
- Is there something I need to do after an error like this, in order to get
the connection back to a usable state?
- Is this a bug?
- Is there anything I can do to provide more information? I still have the
server running right now, but I'm going to have to reboot soon because this
is causing problems.
--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
On Tue, Jun 22, 2004 at 03:06:39PM -0600, Scott Ribe wrote: > OK, I'm using a pool of worker threads that each have a connection (from my > own software, no PHP or anything like that involved), and a connection is in > a state where all queries fail. Looking back through the log I find the > first entry where this connection has an error (there are successful queries > on it prior, so it's not the first query): > Your problem is that you have an error within a transaction and as the error message states: > 2004-06-21 14:51:58 [5589] ERROR: current transaction is aborted, queries > ignored until end of transaction block All your queries will be ignored until you complete the transaction, either with a commit or a rollback. I find the message very clear, how do you think it could be reworded to be more clear? > - Is there something I need to do after an error like this, in order to get > the connection back to a usable state? COMMIT or ROLLBACK > - Is this a bug? In your program, yes. Although I find it interesting that your commit is ignored after the error. Just send it as a seperate query. > - Is there anything I can do to provide more information? I still have the > server running right now, but I'm going to have to reboot soon because this > is causing problems. Just add a transaction commit or rollback after the error. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
>> OK, I'm using a pool of worker threads that each have a connection (from my >> own software, no PHP or anything like that involved), and a connection is in >> a state where all queries fail. Looking back through the log I find the >> first entry where this connection has an error (there are successful queries >> on it prior, so it's not the first query): >> > > Your problem is that you have an error within a transaction and as the > error message states: > >> 2004-06-21 14:51:58 [5589] ERROR: current transaction is aborted, queries >> ignored until end of transaction block > > All your queries will be ignored until you complete the transaction, > either with a commit or a rollback. I find the message very clear, how > do you think it could be reworded to be more clear? What's not clear is why I should be told this when the invalid query ended with a commit. >> - Is there something I need to do after an error like this, in order to get >> the connection back to a usable state? > > COMMIT or ROLLBACK > >> - Is this a bug? > > In your program, yes. Although I find it interesting that your commit > is ignored after the error. Just send it as a seperate query. Yes, exactly. I've never seen a commit be ignored like this--believe me I've had other erroneous queries, and the connections never got borked like this. >> - Is there anything I can do to provide more information? I still have the >> server running right now, but I'm going to have to reboot soon because this >> is causing problems. > > Just add a transaction commit or rollback after the error. I'll certainly add such code as a fail-safe, but I'd still like to understand more fully what has happened. Can I really be 100% sure this would keep the connection usable, given that the 1 commit already somehow failed to end the transaction block? I can certainly understand that a commit submitted by itself might be recognized where for some reason the original one was skipped over, but I'd still like to know more about what happened. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
On Tue, Jun 22, 2004 at 04:30:15PM -0600, Scott Ribe wrote:
> > All your queries will be ignored until you complete the transaction,
> > either with a commit or a rollback. I find the message very clear, how
> > do you think it could be reworded to be more clear?
>
> What's not clear is why I should be told this when the invalid query ended
> with a commit.
Hmm, it only happens when you send the whole lot in a single query
strings. That's not recommended IIRC.
> > In your program, yes. Although I find it interesting that your commit
> > is ignored after the error. Just send it as a seperate query.
>
> Yes, exactly. I've never seen a commit be ignored like this--believe me I've
> had other erroneous queries, and the connections never got borked like this.
Will, it's definitly repeatable:
$ perl -MPg -e '$db = Pg::connectdb("dbname=kleptog");
$db->exec("begin; select error; commit;");
$db->exec("select 1");
print $db->errorMessage;'
ERROR: current transaction is aborted, queries ignored until end of
transaction block
> I'll certainly add such code as a fail-safe, but I'd still like to
> understand more fully what has happened. Can I really be 100% sure this
> would keep the connection usable, given that the 1 commit already somehow
> failed to end the transaction block? I can certainly understand that a
> commit submitted by itself might be recognized where for some reason the
> original one was skipped over, but I'd still like to know more about what
> happened.
If you send each query in a seperate request, it'll work. I don't know
if the current behaviour is really a bug...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Jun 22, 2004 at 04:30:15PM -0600, Scott Ribe wrote:
>> What's not clear is why I should be told this when the invalid query ended
>> with a commit.
> Hmm, it only happens when you send the whole lot in a single query
> strings. That's not recommended IIRC.
When you send multiple commands in a single query string, the entire
string is thrown away after an error. That's how it's always worked,
and I believe it's well-documented. There are apps that (mis?)use this
fact.
regards, tom lane
From Martijn van Oosterhout: > Hmm, it only happens when you send the whole lot in a single query > strings. That's not recommended IIRC. And from Tom Lane: > When you send multiple commands in a single query string, the entire > string is thrown away after an error. That's how it's always worked, > and I believe it's well-documented. There are apps that (mis?)use this > fact. OK, so I think I understand now. I'd earlier seen errors in multi-statement strings and this problem didn't happen. But those errors were with constraints that were deferred, so the error wasn't generated until the commit was being processed, so the transaction block was terminated. While this error (string too long for varchar column) happens as soon as the insert is handled and the commit is never seen. And as for: > Will, it's definitly repeatable: I suppose psql sends either a commit or rollback after the query generates the error, maybe after every query, so my attempt to use it to check this wasn't a valid test. Thanks for the help. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
On Tue, Jun 22, 2004 at 07:02:28PM -0600, Scott Ribe wrote: > I suppose psql sends either a commit or rollback after the query generates > the error, maybe after every query, so my attempt to use it to check this > wasn't a valid test. Nope, psql breaks the statements on ';' and sends each query individually, so the issue never comes up. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
> Nope, psql breaks the statements on ';' and sends each query > individually, so the issue never comes up. DUH!!! Thanks for pointing that out ;-) -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
Martijn van Oosterhout wrote:
> On Tue, Jun 22, 2004 at 07:02:28PM -0600, Scott Ribe wrote:
>
>>I suppose psql sends either a commit or rollback after the query generates
>>the error, maybe after every query, so my attempt to use it to check this
>>wasn't a valid test.
>
>
> Nope, psql breaks the statements on ';' and sends each query
> individually, so the issue never comes up.
Now I wonder, it's the psql program or the client library that does
that? Shall I expect the same from within, say, PHP, Perl, Python, C?
Is it a (settable) option of the client code? If not, I expect psql
to have some kind of SQL parser embedded, in order not to be fooled
by such a query: "select * from tab where f1 = 'a;b;c'".
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
On Wed, Jun 23, 2004 at 01:06:24PM +0200, Marco Colombo wrote: > Martijn van Oosterhout wrote: > >Nope, psql breaks the statements on ';' and sends each query > >individually, so the issue never comes up. > > Now I wonder, it's the psql program or the client library that does > that? Shall I expect the same from within, say, PHP, Perl, Python, C? psql does it, and no, other clients do not do that (or maybe they do, but it's not a requirement because the server itself handles multi-statement query strings too). > Is it a (settable) option of the client code? If not, I expect psql > to have some kind of SQL parser embedded, in order not to be fooled > by such a query: "select * from tab where f1 = 'a;b;c'". Yes, psql does some parsing; for example, to determine what character to use at its prompt. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Por suerte hoy explotó el califont porque si no me habría muerto de aburrido" (Papelucho)
On Wed, Jun 23, 2004 at 01:06:24PM +0200, Marco Colombo wrote: > Martijn van Oosterhout wrote: > >Nope, psql breaks the statements on ';' and sends each query > >individually, so the issue never comes up. > > Now I wonder, it's the psql program or the client library that does > that? Shall I expect the same from within, say, PHP, Perl, Python, C? > Is it a (settable) option of the client code? If not, I expect psql > to have some kind of SQL parser embedded, in order not to be fooled > by such a query: "select * from tab where f1 = 'a;b;c'". Correct, there is a basic parser to track strings and such. It's not particularly clever, since it doesn't have know about keywords or nesting, but it does work. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.