Connection gets into state where all queries fail

[Scott Ribe]

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
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Martijn van Oosterhout]

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 isin
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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFA2K4GY5Twig3Ge+YRAqpRAKCeaOWH8mSoA9XQEEUsiK qUQITuTgCePbpG
eMO9CzolAi6elnOZWhoRLjc=
=d7VN
-----END PGP SIGNATURE-----

[Scott Ribe]

>> 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
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFA2LWeY5Twig3Ge+YRAtyDAJ9Vx3tn9iSX5hLmIMOdEu HFl2xFlACguCfz
akWBvbkD5hlwiFLl7QDuC1A=
=bgcL
-----END PGP SIGNATURE-----

[Tom Lane]

Martijn van Oosterhout <kl*****@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Scott Ribe]

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
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFA2OGtY5Twig3Ge+YRAu1zAKCKsR1qtGLvF9bJkFEnub A87iVzPgCgsdqp
WG5eirIX2v+lJe6B2Y9jzS8=
=6tMi
-----END PGP SIGNATURE-----

[Scott Ribe]

> 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
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Alvaro Herrera]

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)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFA4eEsY5Twig3Ge+YRAmMmAKCdseXoOOXOimlWgcgcNR 5fPr1LEgCfe7kK
cqCKI2ovtEI4hlWsNtupsvw=
=CBOW
-----END PGP SIGNATURE-----