Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query was cancelled - reason?

Query was cancelled - reason?

From
Guido Fiala
Date:
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
dohave 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

Re: Query was cancelled - reason?

From
Oliver Jowett
Date:
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

Re: Query was cancelled - reason?

From
Guido Fiala
Date:
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