主页 / dba / 问题 / 190780
Accepted
eri
Asked: 2017-11-14 07:41:42 +0800 CST

解析 COPY 的二进制格式以访问 tsrange

  • 772

tsrange 如何以二进制形式存储？

例如创建表

CREATE TABLE public.test (t tsrange);
INSERT INTO test VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT INTO test VALUES ('[2011-01-01 14:31, 2015-11-01 15:30)');
INSERT INTO test VALUES ('[2017-01-01 14:31, 2018-11-01 15:30)');
COPY test TO '/tmp/pgcopy' WITH (FORMAT binary);
COPY test TO '/tmp/pgcopy.csv' WITH (FORMAT csv);

它输出：

 cat /tmp/pgcopy.csv                                                                                                                                                                                                  
"[""2010-01-01 14:30:00"",""2010-01-01 15:30:00"")"
"[""2011-01-01 14:31:00"",""2015-11-01 15:30:00"")"
"[""2017-01-01 14:31:00"",""2018-11-01 15:30:00"")"


hexdump -C /tmp/pgcopy
00000000  50 47 43 4f 50 59 0a ff  0d 0a 00 00 00 00 00 00  |PGCOPY..........|
00000010  00 00 00 00 01 00 00 00  19 02 00 00 00 08 00 01  |................|
00000020  1f 19 f9 a9 aa 00 00 00  00 08 00 01 1f 1a d0 3d  |...............=|
00000030  4e 00 00 01 00 00 00 19  02 00 00 00 08 00 01 3b  |N..............;|
00000040  c8 89 51 11 00 00 00 00  08 00 01 c6 7b 1a 3a 0e  |..Q.........{.:.|
00000050  00 00 01 00 00 00 19 02  00 00 00 08 00 01 e8 08  |................|
00000060  0d 77 11 00 00 00 00 08  00 02 1c 9a dc 4d 0e 00  |.w...........M..|
00000070  ff ff                                             |..|
00000072

一个领域是：

00 00 00 19 02 00 00 00 08 00 01 e8 08 0d 77 11 00 00 00 00 08  00 02 1c 9a dc 4d 0e 00

那里：

00000019- 长度为 25 个字节

02- 括号

00000008- 子字段长度

0001e808 0d771100和00021c9a dc4d0e00- 以微秒为单位的存储时间戳。

如何将其转换为整数时间戳？

postgresql dump

1 个回答

  1. Best Answer

    作为一个小注，COPY .. (WITH BINARY)没有括号。它是标志（其中代表括号）。

    COPY ... (WITH BINARY)

    从文档开始COPY

    要确定实际元组数据的适当二进制格式，您应该查阅 PostgreSQL 源，特别是每列数据类型的*sendand*recv函数（通常这些函数可以在src/backend/utils/adt/源分发的目录中找到）。

    此外，文档说二进制格式（当前）有

    • 11 字节签名
    • 4 个字节用于标志
    • 4 字节潜在可变宽度字段，当前未使用，因此我们跳过大小（4 字节\0\0\0\0），这在技术上并不好。如果这四个字节有 15，我们不仅要跳过这四个，还要跳过另外的 15。

    然后元组有

    • 2 个字节用于字段计数

    然后字段有

    • 4 字节长度限定符后跟那么多字节的字段数据。（我们已经在timestampor的情况下tsrange）

    所以基本上我们跳过 25 个字节到达第一列

    这tsrange

    所以它是指定的格式range_send你可以在上面的评论中看到下面的解释range_recv

    二进制表示：第一个字节是标志，然后是下限（如果存在），然后是上限（如果存在）。 每个边界由一个 4 字节长度的标头和该边界的二进制表示表示（通过调用子类型的 send 函数返回）。

    timestamp亚型_

    在您的情况下，该子类型是时间戳，发送是 timestamp_send.

    您可以看到时间戳存储为 8 个字节，并且只是使用简单pq_sendint64的（64 位/8 字节 int）发送。您必须阅读如何timestamp_recv工作才能了解如何处理时间戳的二进制表示。提示：它进入一个用于内存表示的结构timestamp2tm

    /* timestamp2tm()
 * Convert timestamp data type to POSIX time structure.
 * Note that year is _not_ 1900-based, but is an explicit full value.
 * Also, month is one-based, _not_ zero-based.
 * Returns:
 *   0 on success
 *  -1 on out of range

    我不会在这里更多地娱乐这个，但也许接下来会去。

    玩弄

    我们首先尝试DEADBEEF在其中一个隔离标记来跟踪 8 字节标记。

    psql -d test -c 'COPY ( SELECT E'\''DEADBEEF'\'' ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=25 --endian big --read-bytes=8 -c

    现在我们把它换掉..

    psql -d test -c 'COPY ( SELECT $$2010-01-01 14:30$$::timestamp without time zone ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=25 --endian big --read-bytes=8 --format=d8 -x

    结果：添加了paren-comments。

    0000031      315671400000000  (timestamp in int8)
         0001 1f19 f9a9 aa00  (hex representation)
0000041

    这是您的第一个时区号码。tsrange根据上述部分，我们有

    • 间隔上的一个字节
    • 上：4 字节（标头）+ 8 字节（时间戳）
    • 较低：4 字节（标头）+ 8 字节（时间戳）

    因此，为了访问第一个内部时间戳，我们在已经跳过 25 个字节的基础上再跳过 5 个字节，总共跳过了 30 个字节。

    psql -d test -c 'COPY ( SELECT $$[2010-01-01 14:30, 2010-01-01 15:30)$$::tsrange ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=30 --endian big --read-bytes=8 --format=d8 -x

    这给了我们与上面相同的结果..

    0000036      315671400000000
         0001 1f19 f9a9 aa00
0000046

    只需更改--skip-bytes为 42 以跳过该 8 字节时间戳，以及接下来的 4 字节标题，lower您将获得另一个时间戳。

    • 4

相关问题