pgspider/griddb_fdw

GridDB Foreign Data Wrapper for PostgreSQL

griddb_fdw's Issues

Can not update a null column to not-null value

I would like to report an issue. Null value is supported now, but we can not update a null column to not-null value.
Could you please confirm and fix this issue?

Set text column to null value is error

The server is terminated connection because of setting null value to text column.

-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
connection to server was lost

The schema of table ft5 is below:

CREATE FOREIGN TABLE ft5 (
	c1 int OPTIONS (rowkey 'true'),
	c2 int NOT NULL,
	c3 text
) SERVER griddb_svr OPTIONS (table_name 'T4');

If it supported NULL value, please fix this issue.

Error when using sub-query with multi instances of table

I have a scenario as below:

-- Prepare

-- In GridDB
set_tableInfo(store, "INT8_TBL", &INT8_TBL,
                  3,
                  "id", GS_TYPE_INTEGER, GS_TYPE_OPTION_NOT_NULL,
                  "q1", GS_TYPE_LONG, GS_TYPE_OPTION_NULLABLE,
                  "q2", GS_TYPE_LONG, GS_TYPE_OPTION_NULLABLE);

-- In GridDB FDW
CREATE FOREIGN TABLE int8_tbl(id int4 OPTIONS (rowkey 'true'), q1 int8, q2 int8) SERVER griddb_svr;

INSERT ...

select * from int8_tbl;
 id |        q1        |        q2         
----+------------------+-------------------
  1 |              123 |               456
  2 |              123 |  4567890123456789
  3 | 4567890123456789 |               123
  4 | 4567890123456789 |  4567890123456789
  5 | 4567890123456789 | -4567890123456789
(5 rows)

-- Scenario 1

• Scenario

-- In GridDB FDW
explain (verbose, costs off)
select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;           <-- a, b are 2 instances of int8_tbl 
                     QUERY PLAN                     
----------------------------------------------------
 Merge Right Join
   Output: a.id, a.q1, a.q2, b.id, b.q1, b.q2, a.q2
   Merge Cond: (b.q1 = a.q2)
   ->  Sort
         Output: b.id, b.q1, b.q2, a.q2
         Sort Key: b.q1
         ->  Foreign Scan on public.int8_tbl b
               Output: b.id, b.q1, b.q2, a.q2                          <-- a.q2 is not defined
               Remote SQL: SELECT  *  FROM int8_tbl
   ->  Sort
         Output: a.id, a.q1, a.q2
         Sort Key: a.q2
         ->  Foreign Scan on public.int8_tbl a
               Output: a.id, a.q1, a.q2
               Remote SQL: SELECT  *  FROM int8_tbl
(15 rows)

select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;

                                                                  Error:   b.q2     and         a.q2    have same values
                                                                            |                    |
                                                                            V                    V

 id |        q1        |        q2         | id |        q1        |        q2         |         x         
----+------------------+-------------------+----+------------------+-------------------+-------------------
  5 | 4567890123456789 | -4567890123456789 |    |                  |                   |                  
  3 | 4567890123456789 |               123 |  1 |              123 |               456 |               456
  3 | 4567890123456789 |               123 |  2 |              123 |  4567890123456789 |  4567890123456789
  1 |              123 |               456 |    |                  |                   |                  
  2 |              123 |  4567890123456789 |  3 | 4567890123456789 |               123 |               123
  4 | 4567890123456789 |  4567890123456789 |  3 | 4567890123456789 |               123 |               123
  2 |              123 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  2 |              123 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 | -4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 | -4567890123456789
(10 rows)

• Expected result:

select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;

 id |        q1        |        q2         | id |        q1        |        q2         |         x         
----+------------------+-------------------+----+------------------+-------------------+-------------------
  5 | 4567890123456789 | -4567890123456789 |    |                  |                   |                  
  3 | 4567890123456789 |               123 |  1 |              123 |               456 |               123
  3 | 4567890123456789 |               123 |  2 |              123 |  4567890123456789 |               123
  1 |              123 |               456 |    |                  |                   |                  
  2 |              123 |  4567890123456789 |  3 | 4567890123456789 |               123 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  3 | 4567890123456789 |               123 |  4567890123456789
  2 |              123 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  4 | 4567890123456789 |  4567890123456789 |  4567890123456789
  2 |              123 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 |  4567890123456789
  4 | 4567890123456789 |  4567890123456789 |  5 | 4567890123456789 | -4567890123456789 |  4567890123456789
(10 rows)

-- Scenario 2

• Scenario

-- In GridDB FDW
-- lateral reference in a PlaceHolderVar evaluated at join level
explain (verbose, costs off)
select * from
  int8_tbl a left join lateral
  (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from               <-- a.q1 not found
   int8_tbl b cross join int8_tbl c) ss
  on a.q2 = ss.bq1;
ERROR:  variable not found in subplan target lists
select * from
  int8_tbl a left join lateral
  (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
   int8_tbl b cross join int8_tbl c) ss
  on a.q2 = ss.bq1;
ERROR:  variable not found in subplan target lists

• If I remove a.q1 from this query, the command can run successfully.

I think that there is something wrong with query plan.

• In Scenario 1, when a.q2 is not found, a.q2 is converted to b.q2 (a and b are 2 instances of int8_tbl).
• In Scenario 2, when a.q1 is not found, the error is returned.

I think we can modify query plan to get data from int8_tbl only once time for all instances (a, b and c).
Could you please help me to resolve this issue?

Occur PANIC by SAVEPOINT

I got this error regarding to SAVEPOINT command.

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
FETCH c;
 c1 | c2 |  c3   |            c4            |            c5            | c6 | c7 | c8  
----+----+-------+--------------------------+--------------------------+----+----+-----
  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 | Fri Jan 02 00:00:00 1970 | 1  | 1  | foo
(1 row)

SAVEPOINT s;
WARNING:  AbortSubTransaction while in ABORT state
WARNING:  AbortSubTransaction while in ABORT state
ERROR:  Subtransaction is not supported. It is handled as Main transaction.
ERROR:  Subtransaction is not supported. It is handled as Main transaction.
ERROR:  Subtransaction is not supported. It is handled as Main transaction.
ERROR:  Subtransaction is not supported. It is handled as Main transaction.
PANIC:  ERRORDATA_STACK_SIZE exceeded
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
connection to server was lost

Even if it does not support SAVEPOINT, PANIC error should not occur. Could you please investigate and fix this issue?

Compare 2 strings on GridDB FDW

I have a scenario as below:

-- Prepare
CREATE FOREIGN TABLE onek2 (
  unique1   int4,
  unique2   int4,
  two     int4,
  four    int4,
  ten     int4,
  twenty    int4,
  hundred   int4,
  thousand  int4,
  twothousand int4,
  fivethous int4,
  tenthous  int4,
  odd     int4,
  even    int4,
  stringu1  text,
  stringu2  text,
  string4   text
) SERVER griddb_svr;


-- Scenario
explain (costs off)
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
      QUERY PLAN       
-----------------------
 Foreign Scan on onek2
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 unique2 
---------
      11
(1 row)

explain (costs off)
select * from onek2 where unique2 = 11 and stringu1 < 'B';
      QUERY PLAN       
-----------------------
 Foreign Scan on onek2
(1 row)

select * from onek2 where unique2 = 11 and stringu1 < 'B';
ERROR:  GridDB-API is failed by 150018 at griddb_fdw.c: 2512
  Binary operation is not defined for the types STRING and STRING

explain (costs off)
select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
      QUERY PLAN       
-----------------------
 Foreign Scan on onek2
(1 row)

select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
ERROR:  GridDB-API is failed by 150018 at griddb_fdw.c: 2512
  Binary operation is not defined for the types STRING and STRING

I see that:

• I can use "=" operator to compare 2 strings
• I can not use "<" and ">" operators to compare 2 strings

Could you please help me to resolve this issue?

Error griddb_fdw when creating foreign table

I can not create the foreign table. The issue occurs below:

-- ===================================================================
-- create foreign tables
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
    c0 int,
    "C 1" int NOT NULL,
    c2 int NOT NULL,
    c3 text,
    c4 timestamptz,
    c5 timestamp,
    c6 varchar(10),
    c7 char(10) default 'ft1',
    c8 text
) SERVER griddb_srv;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
ALTER FOREIGN TABLE ft1 OPTIONS (table_name 'T 1');
\det+
                    List of foreign tables
 Schema | Table |  Server  |    FDW options     | Description 
--------+-------+----------+--------------------+-------------
 public | ft1   | loopback | (table_name 'T 1') | 
(1 row)

SELECT * FROM ft1;
ERROR:  No such container: ft1
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
connection to server was lost

I think griddb_fdw can not link table "T 1" to foreign table "ft1".

Could you please fix this issue?

Error griddb_fdw test using "make check" on postgresql 11.0

It cannot complete regression test using "make check" command. The issue occurs below:

SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10;                                                                   
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
connection to server was lost

The sql querry causes the error is SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10;
Following the log of the PostgreSQL server, I found out the error in griddb_fdw.c, line 857:

TRAP: FailedAssertion("!(fsstate->cursor == fsstate->num_tuples)", File: "griddb_fdw.c", Line: 857)
2018-12-26 15:45:56.052 +07 [5763] LOG:  server process (PID 5781) was terminated by signal 6: Aborted
2018-12-26 15:45:56.052 +07 [5763] DETAIL:  Failed process was running: SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10;

The log of PostgreSQL server is attached: postmaster.log

Hash join code support issue

I have a scenario as below:

-- Prepare

set local min_parallel_table_scan_size = 0;
set local parallel_setup_cost = 0;
-- Extract bucket and batch counts from an explain analyze plan.  In
-- general we can't make assertions about how many batches (or
-- buckets) will be required because it can vary, but we can in some
-- special cases and we can check for growth.
create or replace function find_hash(node json)
returns json language plpgsql
as
$$
declare
  x json;
  child json;
begin
  if node->>'Node Type' = 'Hash' then
    return node;
  else
    for child in select json_array_elements(node->'Plans')
    loop
      x := find_hash(child);
      if x is not null then
        return x;
      end if;
    end loop;
    return null;
  end if;
end;
$$;
create or replace function hash_join_batches(query text)
returns table (original int, final int) language plpgsql
as
$$
declare
  whole_plan json;
  hash_node json;
begin
  for whole_plan in
    execute 'explain (analyze, format ''json'') ' || query
  loop
    hash_node := find_hash(json_extract_path(whole_plan, '0', 'Plan'));
    original := hash_node->>'Original Hash Batches';
    final := hash_node->>'Hash Batches';
    return next;
  end loop;
end;
$$;

-- Test with local table

create table simple as
  select generate_series(1, 20000) AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
select original > 1 as initially_multibatch, final > original as increased_batches
  from hash_join_batches(
$$
  select count(*) from simple r join simple s using (id);
$$);
 initially_multibatch | increased_batches 
----------------------+-------------------
 f                    | f
(1 row)

-- Test with foreign table

-- In GridDB
set_tableInfo(store, "simple", &simple,
                  2,
                  "id", GS_TYPE_INTEGER, GS_TYPE_OPTION_NOT_NULL,
                  "t", GS_TYPE_STRING, GS_TYPE_OPTION_NULLABLE);

-- In GridDB FDW
create foreign table simple (id int options (rowkey 'true'), t text) server griddb_svr;
insert into simple select generate_series(1, 20000) AS id, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
select original > 1 as initially_multibatch, final > original as increased_batches
  from hash_join_batches(
$$
  select count(*) from simple r join simple s using (id);
$$);
 initially_multibatch | increased_batches 
----------------------+-------------------
                      | 
(1 row)

I found that GridDB FDW does not operate correctly.
Could you please help me to fix it?

UPDATE with inherit target and an inherited source table is failure

This error occurs when I execute UPDATE with inherit target and inherited source table.
The scenario is as following:

create table foo (f1 int, f2 int);
create foreign table foo2 (f3 int) inherits (foo)
  server griddb_svr options (table_name 'loct1');
create table bar (f1 int, f2 int);
create foreign table bar2 (f3 int) inherits (bar)
  server griddb_svr options (table_name 'loct2');
alter table foo set (autovacuum_enabled = 'false');
alter table bar set (autovacuum_enabled = 'false');
alter foreign table foo2 alter column f1 options (rowkey 'true');
alter foreign table bar2 alter column f1 options (rowkey 'true');
insert into foo values(1,1);
insert into foo values(3,3);
insert into foo2 values(2,2,2);
insert into foo2 values(4,4,4);
insert into bar values(1,11);
insert into bar values(2,22);
insert into bar values(6,66);
insert into bar2 values(3,33,33);
insert into bar2 values(4,44,44);
insert into bar2 values(7,77,77);
update bar set f2 = f2 + 100
from
  ( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
WARNING:  Fetched rowkey is not same as expected
ERROR:  GridDB-API is failed by 140037 at griddb_fdw.c: 2754

Maybe the cursor pointing target is wrong. I investigate the griddb_fdw.c and doubt that.
Could you reproduce this issue and fix this.

COPY FROM causes the crash error

COPY FROM does not work now. And it causes the crash error:

create foreign table rem2 (f1 int, f2 text) server griddb_svr options(table_name 'loc2');
copy rem2 from stdin;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
select * from rem2;
no connection to the server
connection to server was lost

Could you please confirm this function is supported or not?
And I think the crash error should not occur.

Inconsistent about the behavior of SELECT target and WHERE condition on system columns

I tried to SELECT some systems column, tableoid and ctid. The behavior is inconsistent.
These values can be gotten by SELECT command:

SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
 tableoid | c1 | c2 |  c3   |            c4            |            c5            | c6 | c7 | c8  
----------+----+----+-------+--------------------------+--------------------------+----+----+-----
 ft1      |  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 | Fri Jan 02 00:00:00 1970 | 1  | 1  | foo
(1 row)

, but not by SELECT with WHERE condition:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Limit
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   ->  Foreign Scan on public.ft1 t1
         Output: c1, c2, c3, c4, c5, c6, c7, c8
         Remote SQL: SELECT  *  FROM "T1" WHERE ((tableoid = 1259))
(5 rows)

SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1;
ERROR:  GridDB-API is failed by 150012 at griddb_fdw.c: 2276
  No such column tableoid

GridDB FDW tries to push-down system column on the WHERE condition. It seems to be necessary to fix GridDB FDW not to push-down WHERE condition about system column, I think.

The partitioning feature cause the crash error

Regarding to table partitioning feature, I got a crash error by the following scenario:

create table itrtest (a int, b text) partition by list (a);
create foreign table remp1 (a int, b text) server griddb_svr options (table_name 'locp1');
alter table itrtest attach partition remp1 for values in (1);
insert into itrtest values (1, 'foo');
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
connection to server was lost

Does GridDB FDW support the table partitioning feature?
Anyway, the server crashing should not occur. Could you please fix this issue.

Request to improve aggregation push-down

GridDB has same SQL aggregation functions to PostgreSQL. Such as SUM(), COUNT() and so on. But I can not push down SQL aggregation functions.
Could you please improve aggregation push-down?

Not Works with griddb 4.3 c client