Re: Query was cancelled - reason?

Hello,

i'am currently run into a strange problem with fast subsequent updates in the same table.

The application is quite complex, so its difficult to to sent a code snippet, i included the network traffic instead:

This is whats happening:
---
T 192.168.195.100:5432 -> 192.168.195.1:56810 [AP]
CBEGIN.Z

T 192.168.195.1:56810 -> 192.168.195.100:5432 [AP]
QSELECT * FROM vsysuserfilter_106_u WHERE filter_id=10 FOR UPDATE OF vsysuserfilter_106_u.

T 192.168.195.100:5432 -> 192.168.195.1:56810 [AP]
Pblank.T..datasource..........Dfilter_column...........filter_id...........filter_string...........filter_type...........userremark...........D.....vtblemloyee_firm_groups....eMail....10....mail....1....mailCSELECT.Z

T 192.168.195.1:56810 -> 192.168.195.100:5432 [AP]
QSELECT * FROM vsysuserfilter_108_u WHERE filter_id=10 FOR UPDATE OF vsysuserfilter_108_u.

T 192.168.195.100:5432 -> 192.168.195.1:56810 [AP]
Pblank.T..datasource..........Dfilter_column...........filter_id...........filter_string...........filter_type...........userremark...........D.....vtblemloyee_firm_groups....eMail....10....mail....1....mailCSELECT.Z

T 192.168.195.1:56810 -> 192.168.195.100:5432 [AP]
QUPDATE vsysuserfilter_106_u SET "filter_column" = 'eMail' WHERE "filter_id" = 10.

T 192.168.195.100:5432 -> 192.168.195.1:56810 [AP]
Pblank.CUPDATE 1.Z

T 192.168.195.1:56810 -> 192.168.195.100:5432 [AP]
Qcommit;begin;.

T 192.168.195.100:5432 -> 192.168.195.1:56810 [AP]
CCOMMIT.EERROR: Query was cancelled...
---

So why comes the commit-error?

Notes:
The select-for-updates above go on views which have update-rules, both views go on the same target table and record but do have different columns included, not always both are required, but
that can't be known at the time the select-for-update is initiated. Not sure if that is important here.

I already had a look into the postmaster log at debug level 5 and found nothing suitable.

Thanks for any help,
Guido

Guido Fiala wrote:
> Hello,
>
> i'am currently run into a strange problem with fast subsequent updates in the same table.
>
> The application is quite complex, so its difficult to to sent a code snippet, i included the network traffic instead:
>
> This is whats happening:

[... COMMIT gets cancelled ...]

> So why comes the commit-error?

Three possibilities I can think of:

- Something is sending SIGINT to the backend process.
- You are calling JDBC's Statement.cancel() method (see below) which
ends up sending a SIGINT (indirectly via a backend process).
- You have statement_timeout set too low; queries appear to be cancelled
if they time out.

There is (AFAIK) still a race condition in the JDBC driver's
implementation of Statement.cancel() which means that it can
occasionally cancel a query "in the future" if called just before a
query begins execution, i.e. sometimes this sort of code will result in
a cancelled query:

stmt.cancel();
stmt.executeUpdate("..."); // Could be a different statement
// on the same connection too

In your case it sounds like it might be:

stmt.executeUpdate("...");
stmt.cancel(); // Perhaps done by a connection management layer
conn.commit(); // Actually executes a query behind the scenes

I looked at fixing cancel() some time ago but my changes didn't make it
into the driver in the end.

-O

Am Donnerstag, 1. Juli 2004 13:20 schrieb Oliver Jowett:
> Guido Fiala wrote:
> > Hello,
> >
> > i'am currently run into a strange problem with fast subsequent updates in
> > the same table.
> >
> > The application is quite complex, so its difficult to to sent a code
> > snippet, i included the network traffic instead:
> >
> > This is whats happening:
>
> [... COMMIT gets cancelled ...]
>
> > So why comes the commit-error?
>
> Three possibilities I can think of:
>
> - Something is sending SIGINT to the backend process.
> - You are calling JDBC's Statement.cancel() method (see below) which

Thank you so much - you are right ! This it was. I didn't destroy my thread
that was intentend to cancel the query, so it run on and triggered...
...
AFAIK the Statement.setqueryTimeOut() is currently not implemented/used
or has this already changed in recent driver snapshots?

> ends up sending a SIGINT (indirectly via a backend process).

Why can't i see the SIGINT in network traffic?

Guido