Skip to content

Commit

Permalink
RELEASE v0.2.0
Browse files Browse the repository at this point in the history
  • Loading branch information
@aanhh
Nguyen Thi Van Anh authored and aanhh committed May 13, 2022
1 parent d20b583 commit 9387022
Show file tree
Hide file tree
Showing 32 changed files with 1,683 additions and 705 deletions.
10 changes: 8 additions & 2 deletions JDBCUtils.java
View file
Original file line number Diff line number Diff line change
Expand Up @@ -285,12 +285,18 @@ public Object[] getResultSet(int resultSetID) throws SQLException {
* has the value of the fields of the current
* row as it values. */
return tmpArrayOfResultRow;
} else {
/*
* All of resultSet's rows have been returned to the C code.
* Close tmpResultSet's statement
*/
tmpResultSet.getStatement().close();
clearResultSetID(resultSetID);
return null;
}
} catch (Throwable e) {
throw e;
}
/* All of resultSet's rows have been returned to the C code. */
return null;
}

/*
Expand Down
2 changes: 1 addition & 1 deletion Makefile
View file
Original file line number Diff line number Diff line change
Expand Up @@ -7,7 +7,7 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK = $(libpq)

EXTENSION = jdbc_fdw
DATA = jdbc_fdw--1.0.sql
DATA = jdbc_fdw--1.0.sql jdbc_fdw--1.0--1.1.sql

REGRESS = postgresql/jdbc_fdw postgresql/int4 postgresql/int8 postgresql/float4 postgresql/float8 postgresql/select postgresql/insert postgresql/update postgresql/aggregates

Expand Down
7 changes: 2 additions & 5 deletions README.md
View file
Original file line number Diff line number Diff line change
@@ -1,7 +1,7 @@
JDBC Foreign Data Wrapper for PostgreSQL
=========================================
* This PostgreSQL extension is a Foreign Data Wrapper (FDW) for JDBC.
* The current version can work with PostgreSQL 13, 14.
* The current version can work with PostgreSQL 13.
* Java 5 or later is required (Confirmed version is Java OpenJDK 1.8.0).
* This jdbc_fdw is based on [JDBC\_FDW](http://github.com/atris/JDBC_FDW.git), [jdbc2\_fdw](https://github.com/heimir-sverrisson/jdbc2_fdw).

Expand Down Expand Up @@ -121,7 +121,7 @@ This is a performance feature.
#### Aggregate function push-down
List of aggregate functions push-down:
```
sum, avg, stddev, variance, max, min, count.
sum, avg, stddev, stddev_pop, stddev_samp, var_pop, var_samp, variance, max, min, count.
```

Usage
Expand All @@ -147,9 +147,6 @@ IMPORT FOREIGN SCHEMA public

Limitations
-----------
#### Multiple aggregate functions push-down
Currently, jdbc_fdw can only push-down one aggregation function in single SQL.

#### Unsupported clause
The following clasues are not support in jdbc_fdw:
RETURNING, GROUPBY, ORDER BY clauses, casting type, transaction control
Expand Down
105 changes: 55 additions & 50 deletions deparse.c
View file
Original file line number Diff line number Diff line change
Expand Up @@ -156,6 +156,25 @@ static void jdbc_append_function_name(Oid funcid, deparse_expr_cxt *context);
static const char *jdbc_quote_identifier(const char *ident,
char *q_char,
bool quote_all_identifiers);
static bool jdbc_func_exist_in_list(char *funcname, const char **funclist);

/*
* JdbcSupportedBuiltinAggFunction
* List of supported builtin aggregate functions for Jdbc
*/
static const char *JdbcSupportedBuiltinAggFunction[] = {
"sum",
"avg",
"max",
"min",
"count",
"stddev",
"stddev_pop",
"stddev_samp",
"var_pop",
"var_samp",
"variance",
NULL};

/*
* Deparse given targetlist and append it to context->buf.
Expand Down Expand Up @@ -495,6 +514,10 @@ jdbc_foreign_expr_walker(Node *node,
{
FuncExpr *fe = (FuncExpr *) node;

/* Does not support push down explicit cast function */
if (fe->funcformat == COERCE_EXPLICIT_CAST)
return false;

/*
* If function used by the expression is not built-in, it
* can't be sent to remote because it might have incompatible
Expand Down Expand Up @@ -722,9 +745,6 @@ jdbc_foreign_expr_walker(Node *node,
Aggref *agg = (Aggref *) node;
ListCell *lc;
char *opername = NULL;
bool is_math_func = false;
bool is_selector_func = false;
bool is_count_func = false;
HeapTuple tuple;

/* get function name */
Expand All @@ -736,21 +756,8 @@ jdbc_foreign_expr_walker(Node *node,
opername = pstrdup(((Form_pg_proc) GETSTRUCT(tuple))->proname.data);
ReleaseSysCache(tuple);

/* these function can be passed to JDBC */
if (strcmp(opername, "sum") == 0 ||
strcmp(opername, "avg") == 0 ||
strcmp(opername, "stddev") == 0 ||
strcmp(opername, "variance") == 0)
is_math_func = true;

if (strcmp(opername, "max") == 0 ||
strcmp(opername, "min") == 0)
is_selector_func = true;

if (strcmp(opername, "count") == 0)
is_count_func = true;

if (!(is_math_func || is_selector_func || is_count_func))
/* Only function exist in JdbcSupportedBuiltinAggFunction can be passed to JDBC */
if (!jdbc_func_exist_in_list(opername, JdbcSupportedBuiltinAggFunction))
return false;

/* Not safe to pushdown when not in grouping context */
Expand All @@ -761,6 +768,15 @@ jdbc_foreign_expr_walker(Node *node,
if (agg->aggsplit != AGGSPLIT_SIMPLE)
return false;

/*
* Does not push down DISTINCT inside aggregate function
* because of undefined behavior of the GridDB JDBC driver.
* TODO: We may hanlde DISTINCT in future with new release
* of GridDB JDBC driver.
*/
if (agg->aggdistinct != NIL)
return false;

/*
* Recurse to input args. aggdirectargs, aggorder and
* aggdistinct are all present in args, so no need to check
Expand All @@ -776,41 +792,9 @@ jdbc_foreign_expr_walker(Node *node,
if (IsA(n, TargetEntry))
{
TargetEntry *tle = (TargetEntry *) n;
Var *tmp_var;

n = (Node *) tle->expr;
tmp_var = (Var *) n;
switch (tmp_var->vartype)
{
case INT2OID:
case INT4OID:
case INT8OID:
case OIDOID:
case FLOAT4OID:
case FLOAT8OID:
case NUMERICOID:
{
if (!(is_math_func || is_selector_func))
{
return false;
}
break;
}
case TIMESTAMPOID:
case TIMESTAMPTZOID:
{
if (!is_selector_func)
{
return false;
}
break;
}
default:
return false;
}
}
else if (!(agg->aggstar == true && is_count_func))
return false;

if (!jdbc_foreign_expr_walker(n, glob_cxt, &inner_cxt))
return false;
Expand Down Expand Up @@ -2506,3 +2490,24 @@ jdbc_quote_identifier(const char *ident, char *q_char, bool quote_all_identifier

return result;
}

/*
* Return true if function name existed in list of function
*/
static bool
jdbc_func_exist_in_list(char *funcname, const char **funclist)
{
int i;

if (funclist == NULL || /* NULL list */
funclist[0] == NULL || /* List length = 0 */
funcname == NULL) /* Input function name = NULL */
return false;

for (i = 0; funclist[i]; i++)
{
if (strcmp(funcname, funclist[i]) == 0)
return true;
}
return false;
}
110 changes: 52 additions & 58 deletions expected/13.4/griddb/aggregates.out
View file
Original file line number Diff line number Diff line change
Expand Up @@ -15,7 +15,7 @@ drivername :DB_DRIVERNAME,
url :DB_URL,
querytimeout '10',
jarfile :DB_DRIVERPATH,
maxheapsize '6000'
maxheapsize '600'
);
--Testcase 3:
CREATE USER MAPPING FOR public SERVER :DB_SERVERNAME OPTIONS(username :DB_USER,password :DB_PASS);
Expand Down Expand Up @@ -105,16 +105,20 @@ SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
-- Round the result to 3 digits to avoid platform-specific results.
--Testcase 14:
EXPLAIN VERBOSE SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
QUERY PLAN
-------------------------------------------------------------
QUERY PLAN
-------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=16)
Output: ((avg(b))::numeric(10,3))
Remote SQL: SELECT "numeric"(avg(b), 655367) FROM aggtest
Output: ((avg(b)))::numeric(10,3)
Remote SQL: SELECT avg(b) FROM aggtest
(3 rows)

--Testcase 15:
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
psql:sql/13.4/aggregates.sql:80: ERROR: remote server returned an error
avg_107_943
-------------
107.943
(1 row)

--Testcase 16:
EXPLAIN VERBOSE SELECT sum(four) AS sum_1500 FROM onek;
QUERY PLAN
Expand Down Expand Up @@ -213,14 +217,12 @@ SELECT max(aggtest.b) AS max_324_78 FROM aggtest;

--Testcase 28:
EXPLAIN VERBOSE SELECT stddev_pop(b) FROM aggtest;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=205.06..205.07 rows=1 width=8)
Output: stddev_pop(b)
-> Foreign Scan on public.aggtest (cost=100.00..197.75 rows=2925 width=4)
Output: id, a, b
Remote SQL: SELECT b FROM aggtest
(5 rows)
QUERY PLAN
-------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=8)
Output: (stddev_pop(b))
Remote SQL: SELECT stddev_pop(b) FROM aggtest
(3 rows)

--Testcase 29:
SELECT stddev_pop(b) FROM aggtest;
Expand All @@ -231,14 +233,12 @@ SELECT stddev_pop(b) FROM aggtest;

--Testcase 30:
EXPLAIN VERBOSE SELECT stddev_samp(b) FROM aggtest;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=205.06..205.07 rows=1 width=8)
Output: stddev_samp(b)
-> Foreign Scan on public.aggtest (cost=100.00..197.75 rows=2925 width=4)
Output: id, a, b
Remote SQL: SELECT b FROM aggtest
(5 rows)
QUERY PLAN
--------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=8)
Output: (stddev_samp(b))
Remote SQL: SELECT stddev_samp(b) FROM aggtest
(3 rows)

--Testcase 31:
SELECT stddev_samp(b) FROM aggtest;
Expand All @@ -249,14 +249,12 @@ SELECT stddev_samp(b) FROM aggtest;

--Testcase 32:
EXPLAIN VERBOSE SELECT var_pop(b) FROM aggtest;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=205.06..205.07 rows=1 width=8)
Output: var_pop(b)
-> Foreign Scan on public.aggtest (cost=100.00..197.75 rows=2925 width=4)
Output: id, a, b
Remote SQL: SELECT b FROM aggtest
(5 rows)
QUERY PLAN
------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=8)
Output: (var_pop(b))
Remote SQL: SELECT var_pop(b) FROM aggtest
(3 rows)

--Testcase 33:
SELECT var_pop(b) FROM aggtest;
Expand All @@ -267,14 +265,12 @@ SELECT var_pop(b) FROM aggtest;

--Testcase 34:
EXPLAIN VERBOSE SELECT var_samp(b) FROM aggtest;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=205.06..205.07 rows=1 width=8)
Output: var_samp(b)
-> Foreign Scan on public.aggtest (cost=100.00..197.75 rows=2925 width=4)
Output: id, a, b
Remote SQL: SELECT b FROM aggtest
(5 rows)
QUERY PLAN
------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=8)
Output: (var_samp(b))
Remote SQL: SELECT var_samp(b) FROM aggtest
(3 rows)

--Testcase 35:
SELECT var_samp(b) FROM aggtest;
Expand Down Expand Up @@ -593,14 +589,12 @@ DROP FOREIGN TABLE regr_test;
-- test count, distinct
--Testcase 72:
EXPLAIN VERBOSE SELECT count(four) AS cnt_1000 FROM onek;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=205.06..205.07 rows=1 width=8)
Output: count(four)
-> Foreign Scan on public.onek (cost=100.00..197.75 rows=2925 width=4)
Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
Remote SQL: SELECT four FROM onek
(5 rows)
QUERY PLAN
------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=8)
Output: (count(four))
Remote SQL: SELECT count(four) FROM onek
(3 rows)

--Testcase 73:
SELECT count(four) AS cnt_1000 FROM onek;
Expand Down Expand Up @@ -652,9 +646,14 @@ from tenk1 o;
(13 rows)

--Testcase 77:
-- select
-- (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
-- from tenk1 o;
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
max
------
9999
(1 row)

--
-- test boolean aggregates
--
Expand Down Expand Up @@ -873,15 +872,10 @@ select distinct max(unique2) from tenk1;
--Testcase 99:
explain (costs off)
select max(100) from tenk1;
QUERY PLAN
----------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
-> Result
One-Time Filter: (100 IS NOT NULL)
-> Foreign Scan on tenk1
(6 rows)
QUERY PLAN
--------------
Foreign Scan
(1 row)

--Testcase 100:
select max(100) from tenk1;
Expand Down
Loading

0 comments on commit 9387022

Please sign in to comment.