search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
496 stars 156 forks source link

pg_terminate_backend / pg_cancel_backend not working #194

Closed josser closed 7 years ago

josser commented 7 years ago

Hi! It looks like I can't cancel big query from foreign table using pg_terminate_backend / pg_cancel_backend. If I'm using pg_terminate_backend, it returns 'true' but process still exists. If i'm using pg_cancel_backend, it just hangs. Is it possible at all or do I want something strange :) ?

laurenz commented 7 years ago

I can reproduce the first behaviour (pg_terminate_backend does nothing), but pg_cancel_backend works for me.

Could it be that canceling hangs because it takes Oracle a long time to cancel and rollback the query? Does it work if you interrupt such a query with CTRL+C in sqlplus?

About termination, it looks like Oracle messes up PostgreSQL's signal handling. I'll have to look into that.

josser commented 7 years ago

I have made some tests and It looks like in most cases pg_cancel_backend works (or not) like pg_ternimnate_backend. It just returns 'true' but query is still running. I can't reproduce 'hanging' behavior again.

laurenz commented 7 years ago

Great. I will look into the remaining problem as soon as I get to it.

It is probably just a case of setting a SIGTERM handler that does the right thing after OCI has been initialized.

laurenz commented 7 years ago

Huh, I've misread your last message: you are saying that pg_cancel_backend does not work ok.

What operating system are you on? It seems to work well on my Linux system. The Oracle query is interrupted, and an error is shown.

I have fixed the pg_terminate_backend problem with commit aac2fc116994ac3bda957420f9a07e28e2963ba8, could you test if it works for you?

josser commented 7 years ago

cool! Will try this today. I'm on debian 8. I will test both today. Maybe there is something different in my case: Actually I'm terminating not query itself but 'refresh materialize view' query which actually querying oracle.

Sorry for bad english )

laurenz commented 7 years ago

I tried with REFRESH MATERIALIZED VIEW based on a foreign table on my Fedora Linux system both with a local and an IP connection, and it worked fine.

josser commented 7 years ago

Ok, looks like still not working Maybe I'm doing something wrong? Here is video https://www.dropbox.com/s/s3bvmethumw2j44/pg_cancel_terminate_bugornot.mov?dl=0

laurenz commented 7 years ago

My company's web proxy forbids me to use dropbox, and I don't have a dropbox account.

Could you write up a complete set of statements from CREATE SERVER to CREATE/REFRESH MATERIALIZED VIEW to reproduce it (no need for INSERTs to add lots of data).

What happens if you press CTRL+C in the psql session where REFRESH MATERIALIZED VIEW is running?

What happens if you run the Oracle SELECT statement in sqlplus and press CTRL+C there?

laurenz commented 7 years ago

Anything new? If there's no feedback, I'll have to close the issue, but I would like to know if my fix really missed the problem or not.

josser commented 7 years ago

it's turned out that I can reproduce this only if I'm doing refreshing materialized views. Neither regular queries via pgadmin (via fdw), nor same queries via sqlplus or sqlDeveloper are hanging.
And I've stoped using materialized views so I've lost access to environment where I can test this. So, for now, I think you can close