如何中止JDBC Postgresql CopyManager复制? [英] How to abort JDBC Postgresql CopyManager copying?

问题描述

有没有办法取消在单独线程中调用copyIn()方法开始的复制过程?

Is there a way to cancel the copying process started by calling copyIn() method in a separate thread?

说，我有一个csv文件列表，我需要从中复制这些文件，以最大程度地发挥数据库服务器的功能.因此，我为n个文件创建了n个线程连接，但例如，如果选择了错误的文件，我将找不到中止单个操作的方法.

Say, I have a list of csv-files which I need to copy from, getting maximum of database server power. So I create n-threads-connections for n-files, but I cannot find a way to abort a single operation if, for example, a wrong file has been chosen.

杀死线程不起作用-COPY保持运行.

Killing threads does not work - COPY just keeps running.

FutureTask<>类用于创建线程，因此有它们的列表-每个csv一个.

The FutureTask<> class is used to create the threads, so there is a list of them - one for each csv.

调用task.cancel(true)不会对服务器上的复制过程产生任何影响.只有System.exit()可以用火杀死它.

Calling task.cancel(true) makes nothing in terms of copying process on the server. Only System.exit() can kill it with fire.

有什么想法吗?

我的一些代码:

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);
    }

已解决:

找到了解决方案，但是需要对代码进行一些更改.

The solution was found, however it required some changes in code.

Upload files method body现在看起来像这样

for (File file : files) {
    Uploader uploader = new Uploader(defaultTableName, file);
    uploaders.add(uploader);
    Future<Long> f = execService.submit(uploader);

    //save the Future to get the copy result when finished

}

有了这个，我们可以轻松地调用Uploader的某些方法，在其中可以仅关闭数据库连接并正确处理异常.它将停止在服务器上复制.

Having this, we can easily call some Uploader's method where it is possible to just close the database connection and handle the exception properly. It will stop copying on the server.

我接受该解决方案可能不是最优雅的解决方案，但是它可以工作，可以快速运行，并且不需要太多代码.

I accept that the solution might not be the most elegant one, however it works, it works fast, and not much code is needed.

推荐答案

PostgreSQL实际上不支持带内查询取消.

PostgreSQL doesn't actually support in-band query cancels.

当您从JDBC驱动程序请求查询取消时，它会建立新连接以发送取消消息. (这意味着如果您在max_connections处，取消操作将失败，这是不正确的.)

When you request a query cancel from the JDBC driver it makes a new connection to send the cancel message. (This means that if you're at max_connections a cancel will fail, which is kind of perverse).

其结果是您可以自己做同样的事情:

The upshot of this is that you can do the same thing yourself:

• 在开始复制操作之前，使用pg_backend_pid()获取工作程序的进程ID；

• Use pg_backend_pid() to get the process ID of the worker before starting the copy operation;

当您要取消副本时，打开一个新连接，并使用先前记录的pid发出pg_cancel_backend(?).如果还没有停止，您可以稍等片刻，然后执行pg_terminate_backend(?).

When you want to cancel a copy, open a new connection and issue pg_cancel_backend(?) with the pid recorded earlier. If it doesn't stop, you can wait a bit then do a pg_terminate_backend(?).

这些是普通的SQL级功能.

These are ordinary SQL-level functions.

唯一真正的问题是取消和终止请求是会话级而不是语句级.因此，他们可以与语句完成和新语句的开始进行竞争，例如:

The only real issue is that the cancel and terminate requests are session-level not statement level. So they can race with statement completion and the start of a new statement, eg:

• client1:复制开始
• client2:连接以发送取消消息
• client1:复制完成
• client1:新的单独副本开始
• client2发送pg_cancel_backend(...)

这时，第二个副本将被终止，这可能不是您想要的.因此，您必须确保使用适当的排除客户端来防止这种情况的发生，并确保在开始新语句之前完成所有未完成的取消请求.

At this point, the second copy will be terminated, which may not be what you wanted. So you must make sure to use appropriate exclusion client-side to prevent this from happening, making sure any outstanding cancel requests are finished before starting a new statement.

IIRC JDBC驱动程序内部始终有相同的问题.这是团队真正想要取消特定的每个会话语句序列号的原因之一，例如(当前不存在的)pg_cancel_backend(pid, statementnumber)如果语句已经终止则由于错误而中止，而不是发送还是要取消.

IIRC the JDBC driver has the same issue internally anyway. It's one of the reasons the team really want a way to cancel a particular unique per-session statement sequence number, like a (currently non-existent) pg_cancel_backend(pid, statementnumber) that aborts with an error if the statement has already terminated, instead of sending the cancel anyway.

这篇关于如何中止JDBC Postgresql CopyManager复制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！