java - How to abort JDBC Postgresql CopyManager copying? -

is there way cancel copying process started calling copyin() method in separate thread?

say, have list of csv-files need copy from, getting maximum of database server power. create n-threads-connections n-files, cannot find way abort single operation if, example, wrong file has been chosen.

killing threads not work - copy keeps running.

the futuretask<> class used create threads, there list of them - 1 each csv.

calling task.cancel(true) makes nothing in terms of copying process on server. system.exit() can kill fire.

any ideas?

some of code:

uploader.java implements callable

public static long uploadfile(final file file, final string tablename) {      long status = 0;     try {         copymanager copymanager =             new copymanager((baseconnection) new datasource().connect());         filereader reader = new filereader(file);         status = copymanager.copyin(sql, reader);     } catch (sqlexception | ioexception e) {        ...     }     return status; }  @override public long call() throws exception {     return uploadfile(file, tablename); } 

upload files method body

for (file file : files) {         futuretask<long> ftask =                 new futuretask<>(                         new uploader(defaulttablename, file)                 );         tasks.add(ftask);         execservice.execute(ftask);     } 

solved:

the solution found, required changes in code.

upload files method body looks now

for (file file : files) {     uploader uploader = new uploader(defaulttablename, file);     uploaders.add(uploader);     future<long> f = execservice.submit(uploader);      //save future copy result when finished  } 

having this, can call uploader's method possible close database connection , handle exception properly. stop copying on server.

i accept solution might not elegant one, works, works fast, , not code needed.

postgresql doesn't support in-band query cancels.

when request query cancel jdbc driver makes new connection send cancel message. (this means if you're @ max_connections cancel fail, kind of perverse).

the upshot of can same thing yourself:

• use pg_backend_pid() process id of worker before starting copy operation;

• when want cancel copy, open new connection , issue pg_cancel_backend(?) pid recorded earlier. if doesn't stop, can wait bit pg_terminate_backend(?).

these ordinary sql-level functions.

the real issue cancel , terminate requests session-level not statement level. can race statement completion , start of new statement, eg:

• client1: copy starts
• client2: connects send cancel message
• client1: copy finishes
• client1: new separate copy starts
• client2 sends pg_cancel_backend(...)

at point, second copy terminated, may not wanted. must make sure use appropriate exclusion client-side prevent happening, making sure outstanding cancel requests finished before starting new statement.

iirc jdbc driver has same issue internally anyway. it's 1 of reasons team want way cancel particular unique per-session statement sequence number, (currently non-existent) pg_cancel_backend(pid, statementnumber) aborts error if statement has terminated, instead of sending cancel anyway.