今天看到有人问“PostgreSQL中的copy命令是在同一个事务中执行的吗”。其实我想之所以提出这个问题，主要是想知道在执行copy语句时如果出现错误导致中断，那么数据是会导出/入部分，还是会像事务一样回滚。为了解答这个问题，我们先来简单了解下copy协议。

什么是copy协议？

我们使用客户端和数据库进行交互时，都必须要遵守PostgreSQL数据库的通信协议才可以。让我们比较熟悉的协议有TCP/IP 协议和 HTTP 协议等。

而PostgreSQL在TCP/IP 协议之上实现了一套基于消息的通信协议，同时，为避免客户端和服务端在同一台机器时的网络通信代价，也支持在 Unix 域套接字上使用该协议。

我们要说的copy协议就是这些通信协议的一种，但不同于普通的与数据库交互的协议，copy协议是专门为了高效地导入/导出数据，当我们执行copy操作时会将当前连接切换至一种截然不同的子协议。

Copy 子协议对应三种模式：

  • copy-in：导入数据，对应命令 COPY FROM STDIN
  • copy-out：导出数据，对应命令 COPY TO STDOUT
  • copy-both：用于 walsender，在主备间批量传输数据

copy协议简介

我们以copy-in为例，先看一下相关的消息格式。

copy-inCopyInResponse：

postgre 创建拷贝用户 post copy_数据

  • Byte1(‘G’)：标识这条消息是一条Start Copy In（开始拷贝入）响应消息。前端现在必须发送拷贝入数据（如果还没准备好做这些事情，那么发送一条CopyFail消息）。
  • Int32：以字节计的消息内容的长度，包括长度本身。
  • Int8：0表示全体拷贝格式都是文本（数据行由新符分隔， 列由分隔字符分隔等等）。1 表示全体拷贝格式都是二进制的（类似于DataRow 格式）。
  • Int16：要拷贝的数据中的列数
  • Int16[N]：每个列要使用的格式代码。目前每个都必须是零（文本）或者一（二进制）。 如果全体拷贝格式都是文本，那么所有的都必须是零。

CopyData：

postgre 创建拷贝用户 post copy_postgresql_02

  • Byte1(‘d’)：标识这条消息是一个COPY数据。
  • Int32：以字节计的消息内容的长度，包括长度本身。
  • data：构成COPY数据流的一部分的数据。从后端发出的消息总是对应单一的数据行，但是前端发出的消息可能会任意分割数据流。

以copy-in为例，其大致流程如下：

postgre 创建拷贝用户 post copy_postgresql_03

服务端收到 COPY 命令后，进入 COPY 模式，并回复 CopyInResponse。随后客户端通过 CopyData 消息传输数据，CopyComplete 消息标识数据传输完成，服务端收到该消息后，发送 CommandComplete 和 ReadyForQuery 消息。

copy与事务：

和普通的insert语句不同，copy是在一条命令里加载所有记录， 而不是一连串的INSERT命令。 因此其本质上和事务是类似的，但是和事务还是有很大区别。

在事务中，如果我们遇到错误会停止，然后回滚这个事务，而copy会在遇到错误时中止，但是不是简单的回滚。 如果是copy to，那么则出现错误前的数据不会受影响；而如果是copy from，那么在出现错误前的数据在目标表中会变的不可见，这个并不是说回滚了，而是不可见或者不可访问，所以这部分数据在目标表中仍然占用磁盘空间。

例子： –copy to： 我们可以看到表t1中有1000W条数据，copy到文件大小为390M

bill@bill=>copy t1 to '/home/pg13/t1.sql';
COPY 10000000

pg13@cnndr4pptliot-> du -sh t1.sql
390M    t1.sql

重新copy，然后中断该操作，发现只有部分数据被copy to文件中了，说明中断前的数据没受到影响。

bill@bill=>copy t1 to '/home/pg13/t1.sql';
^CCancel request sent
ERROR:  canceling statement due to user request

pg13@cnndr4pptliot-> du -sh t1.sql
362M    t1.sql

–copy from： copy时进行中断。

bill@bill=>truncate table t1;
TRUNCATE TABLE

bill@bill=>copy t1 from '/home/pg13/t1.sql';
^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  COPY t1, line 5260603: "4539963       4dbe984c7a23aa2674d5d1fcd82f1b91"

查看： 发现表中没有任何数据，但是大小为374MB！

bill@bill=>select count(*) from t1;
 count
-------
     0
(1 row)

bill@bill=>\dt+ t1
                         List of relations
 Schema | Name | Type  | Owner | Persistence |  Size  | Description
--------+------+-------+-------+-------------+--------+-------------
 public | t1   | table | bill  | permanent   | 374 MB |
(1 row)

日志：

2021-07-14 11:34:58.527 CST,,,28662,,60ee5b60.6ff6,1,,2021-07-14 11:34:56 CST,4/3245,3897,LOG,00000,"automatic vacuum of table ""bill.public.t1"": index scans: 0
pages: 47859 removed, 0 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 5743000 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 3897
buffer usage: 52219 hits, 91400 misses, 45729 dirtied
avg read rate: 395.184 MB/s, avg write rate: 197.717 MB/s
system usage: CPU: user: 0.51 s, system: 0.62 s, elapsed: 1.80 s
WAL usage: 143579 records, 2 full page images, 30980935 bytes",,,,,,,,"heap_vacuum_rel, vacuumlazy.c:691","","autovacuum worker"

再次查看： 可以看到日志中，当该表被vacuum后，表占用的磁盘空间便被回收了。

bill@bill=>\dt+ t1
                         List of relations
 Schema | Name | Type  | Owner | Persistence | Size  | Description
--------+------+-------+-------+-------------+-------+-------------
 public | t1   | table | bill  | permanent   | 24 kB |
(1 row)

总结：

通过上面的实验我们可以的出结论，对于copy操作，如果中途出现问题导致中断：

  • copy to：不会受到影响，中断前已经copy的数据仍然有效；
  • copy from：目标表中已经拷贝的数据会变得不可见，但是仍然占用磁盘空间，等到被vacuum时空间将被回收。

参考链接： https://www.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf http://www.postgres.cn/docs/13/sql-copy.html http://www.postgres.cn/docs/13/protocol-message-formats.html