project-tsurugi / tsurugi_fdw Goto Github PK

INNER JOINをしたSELECT文を実行した後、SELECT文を実行するとエラーになります。
一度postgreSQLからログアウトし再度ログインすると正常に取得できますが、
INNER JOINをした後はログアウトしない限りエラーが発生します。
// SELECT文実行(任意のSQL)
postgres=# SELECT * from map;
 map_id | map_name | map_type | epsg |      init_x       |      init_y       | init_z | init_scale | max_scale | min_scale | description 
--------+----------+----------+------+-------------------+-------------------+--------+------------+-----------+-----------+-------------
   1001 | 2d map   |        1 | 3857 | 15454818.86530255 | 4501176.688619994 |        |         13 |        26 |        10 | 
(1 row)

// INNER JOINしたSELECT文実行
postgres=# SELECT * FROM map INNER JOIN map_layers ON map.map_id = map_layers.map_id;
 map_id | map_name | map_type | epsg |      init_x       |      init_y       | init_z | init_scale | max_scale | min_scale | description | map_id | layer_id | layer_order 
--------+----------+----------+------+-------------------+-------------------+--------+------------+-----------+-----------+-------------+--------+----------+-------------
   1001 | 2d map   |        1 | 3857 | 15454818.86530255 | 4501176.688619994 |        |         13 |        26 |        10 |             |   1001 |        3 |           2
   1001 | 2d map   |        1 | 3857 | 15454818.86530255 | 4501176.688619994 |        |         13 |        26 |        10 |             |   1001 |        3 |           2
(2 rows)

// 成功していたSELECT文を再実行
postgres=# SELECT * from map;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
使用データを以下に記します。
// map
CREATE TABLE map (
    map_id integer NOT NULL PRIMARY KEY,
    map_name character varying(40) NOT NULL,
    map_type integer NOT NULL,
    epsg integer,
    init_x double precision NOT NULL,
    init_y double precision NOT NULL,
    init_z double precision,
    init_scale integer NOT NULL,
    max_scale integer NOT NULL,
    min_scale integer NOT NULL,
    description character varying(256)
) TABLESPACE tsurugi;

CREATE FOREIGN TABLE map (
    map_id integer NOT NULL,
    map_name character varying(40) NOT NULL,
    map_type integer NOT NULL,
    epsg integer,
    init_x double precision NOT NULL,
    init_y double precision NOT NULL,
    init_z double precision,
    init_scale integer NOT NULL,
    max_scale integer NOT NULL,
    min_scale integer NOT NULL,
    description character varying(256)
) SERVER ogawayama;

INSERT INTO map (map_id, map_name, map_type, epsg, init_x, init_y, init_z, init_scale, max_scale, min_scale, description) VALUES (1001,'2d map',1,3857,15454818.8653025497,4501176.68861999363,NULL,13,26,10,NULL);

// map_layers
CREATE TABLE map_layers (
    map_id integer NOT NULL,
    layer_id integer NOT NULL,
    layer_order integer NOT NULL,
    PRIMARY KEY (map_id, layer_id)
) TABLESPACE tsurugi;

CREATE FOREIGN TABLE map_layers (
    map_id integer NOT NULL,
    layer_id integer NOT NULL,
    layer_order integer NOT NULL
) SERVER ogawayama;

INSERT INTO map_layers (map_id, layer_id, layer_order) VALUES (1001,5,1);
INSERT INTO map_layers (map_id, layer_id, layer_order) VALUES (1001,3,2);
ご検討よろしくお願いいたします。

昨日のデータで、以下のSELECTが失敗するようです。
postgres=# select block_num from job_status;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: 2023-01-20 09:52:54.191 JST [2791097] LOG:  server process (PID 2792124) was terminated by signal 11: Segmentation fault
2023-01-20 09:52:54.191 JST [2791097] DETAIL:  Failed process was running: select block_num from job_status;
2023-01-20 09:52:54.191 JST [2791097] LOG:  terminating any other active server processes
2023-01-20 09:52:54.191 JST [2792120] WARNING:  terminating connection because of crash of another server process
2023-01-20 09:52:54.191 JST [2792120] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2023-01-20 09:52:54.191 JST [2792120] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2023-01-20 09:52:54.192 JST [2792130] FATAL:  the database system is in recovery mode
Failed.
!> 2023-01-20 09:52:54.193 JST [2791097] LOG:  all server processes terminated; reinitializing
2023-01-20 09:52:54.213 JST [2792131] LOG:  database system was interrupted; last known up at 2023-01-20 09:51:11 JST
2023-01-20 09:52:54.235 JST [2792131] LOG:  database system was not properly shut down; automatic recovery in progress
2023-01-20 09:52:54.237 JST [2792131] LOG:  redo starts at 0/1750B78
2023-01-20 09:52:54.237 JST [2792131] LOG:  invalid record length at 0/1750BB0: wanted 24, got 0
2023-01-20 09:52:54.237 JST [2792131] LOG:  redo done at 0/1750B78
2023-01-20 09:52:54.243 JST [2791097] LOG:  database system is ready to accept connections
以下のSELECTは成功します。
postgres=# select * from job_status;
ご確認お願いいたします。

CREATE TABLE t1(c1 INTEGER NOT NULL PRIMARY KEY) TABLESPACE tsurugi;
ERROR:  The table is not found when registing constraints. (name: t1)

make tests

k-okada@malta:~/frontend/third_party/metadata-manager$ git log
commit e3310d244122e750f56cb4c1982f3953b0481f20 (HEAD, origin/master, origin/HEAD)
Merge: ddeda3a 166059b
Author: OKADA Kohei <[email protected]>
Date:   Tue Jun 6 11:40:57 2023 +0900

    Merge pull request #68 from project-tsurugi/feature/experimental

    Feature/experimental

CREATE FOREIGN TABLE table_for_check (
    colpk INTEGER,
    col3  float,
    col4  double precision
) SERVER ogawayama;

CREATE TABLE table_for_check (
    colpk INTEGER NOT NULL PRIMARY KEY,
    col3  float,
    col4  double precision
) TABLESPACE tsurugi;

        {
            "formatVersion": "1",
            "generation": "1",
            "id": "2",
            "name": "col3",
            "tableId": "1",
            "columnNumber": "2",
            "dataTypeId": "701",
            "dataLength": "",
            "varying": "false",
            "isNotNull": "false",
            "isFuncExpr": "false"
        },

ostgresqlでSELECT文にORDER BYをつけるとエラーとなります。
tsurugiで実行した場合にはデータが正常に取得されます。

-- ORDER BY #3
-- PG
SELECT
    c1, c4, c5, c6, c7
FROM
    pt2
WHERE
    c1 > 2
ORDER BY
    c7;
 c1 | c4  |  c5  |     c6     | c7  
----+-----+------+------------+-----
  3 | 3.3 | 3.33 | three      | ABC
  5 | 5.5 | 5.55 | five       | XYZ
  4 |     |      | NULL       | 
(3 rows)

-- TG
SELECT
    c1, c4, c5, c6, c7
FROM
    t2
WHERE
    c1 > 2
ORDER BY
    c7;
 c1 | c4  |  c5  |     c6     | c7  
----+-----+------+------------+-----
  4 |   0 |    0 | NULL       | 
  3 | 3.3 | 3.33 | three      | ABC
  5 | 5.5 | 5.55 | five       | XYZ
(3 rows)