Oops. I found a bug with it.
Revised patches included.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
>> On 24-06-11 10:15, Tatsuo Ishii wrote:
>>>> Any chance that this idea will be implemented anytime soon?
>>>> http://lists.pgfoundry.org/pipermail/pgpool-hackers/2011-May/000716.html
>>>
>>> Revisiting it now. Since nobody proposed patches for this, I've been
>>> starting coding now:-) The rewriting code is quite complex and I am
>>> not sure whether I could finish the work by the end of this
>>> weekend. Let me try out...
>>
>> Wow, I am impressed. I hope you will succeed.
>>
>> As a matter of fact, a colleague of mine created a patch that handles one
>> specific case: timezone('utc'::text, now()), which is the most important
>> case for us.
>>
>> He told me that the code is too complex for him to quickly handle the
>> generic case, so he added this specific case instead. I can't imagine the
>> patch being useful to you, but if you want to look at it, let me know.
>
> I have finished the work so far. However I could not make it work for
> extended protocol. This means still Java programs or PHP PDO programs
> are still remain same as they were. So I am not sure includes patches
> is usefull for you or not.
>
> What I did was, extract default expressions (such as
> timezone('utc'::text, now()) from the system catalog and get the
> result to replace a query. So you could use any expressions including
> now() for default values.
>
> To adopt the cases for extended protocol, probably I need to modify
> bind_rewrite_timestamp.
>
> Here are test cases:
>
> create table t1(i int, t timestamp default timezone('utc'::text, now()),
> t2 timestamp default current_timestamp);
> CREATE TABLE
> begin;
> BEGIN
> insert into t1 values(1, now());
> INSERT 0 1
> insert into t1 default values;
> INSERT 0 1
> insert into t1 values(2);
> INSERT 0 1
> insert into t1 values(3, default);
> INSERT 0 1
> insert into t1(i) values(4);
> INSERT 0 1
> update t1 set i = 100 where i is null;
> UPDATE 1
> update t1 set i = 101,t = current_timestamp where i = 100;
> UPDATE 1
> select * from t1 where i = 101;
> i | t | t2
> -----+----------------------------+----------------------------
> 101 | 2011-06-25 17:18:30.676202 | 2011-06-25 17:18:30.676202
> (1 row)
>
> update t1 set i = 102,t = default where i = 101;
> UPDATE 1
> select * from t1;
> i | t | t2
> -----+----------------------------+----------------------------
> 1 | 2011-06-25 17:18:30.676202 | 2011-06-25 17:18:30.676202
> 2 | 2011-06-25 08:18:30.676202 | 2011-06-25 17:18:30.676202
> 3 | 2011-06-25 08:18:30.676202 | 2011-06-25 17:18:30.676202
> 4 | 2011-06-25 08:18:30.676202 | 2011-06-25 17:18:30.676202
> 102 | 2011-06-25 08:18:30.676202 | 2011-06-25 17:18:30.676202
> (5 rows)
Index: pool_timestamp.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool-II/pool_timestamp.c,v
retrieving revision 1.17
diff -c -r1.17 pool_timestamp.c
*** pool_timestamp.c 21 Jun 2011 07:24:00 -0000 1.17
--- pool_timestamp.c 27 Jun 2011 01:51:11 -0000
***************
*** 34,40 ****
typedef struct {
! char *attrname;
int use_timestamp;
} TSAttr;
--- 34,41 ----
typedef struct {
! char *attrname; /* attribute name */
! char *adsrc; /* default value expression */
int use_timestamp;
} TSAttr;
***************
*** 63,68 ****
--- 64,70 ----
static bool rewrite_timestamp_update(UpdateStmt *u_stmt, TSRewriteContext *ctx);
static char *get_current_timestamp(POOL_CONNECTION_POOL *backend);
static Node *makeTsExpr(TSRewriteContext *ctx);
+ static A_Const *makeStringConstFromQuery(POOL_CONNECTION_POOL *backend, char *expression);
bool raw_expression_tree_walker(Node *node, bool (*walker) (), void *context);
#define MAX_RELCACHE 32
***************
*** 72,77 ****
--- 74,82 ----
static void *
ts_register_func(POOL_SELECT_RESULT *res)
{
+ /* Number of result columns included in res */
+ #define NUM_COLS 3
+
TSRel *rel;
int i;
***************
*** 82,91 ****
for (i = 0; i < res->numrows; i++)
{
! rel->attr[i].attrname = strdup(res->data[i * 2]);
! rel->attr[i].use_timestamp = *(res->data[i * 2 + 1]) == 't';
! pool_debug("attrname %s use_timestamp = %d",
! rel->attr[i].attrname, rel->attr[i].use_timestamp);
}
rel->relnatts = res->numrows;
--- 87,108 ----
for (i = 0; i < res->numrows; i++)
{
! int index = 0;
!
! rel->attr[i].attrname = strdup(res->data[i * NUM_COLS + index]);
! index++;
!
! if (res->data[i * NUM_COLS + index])
! rel->attr[i].adsrc = strdup(res->data[i * NUM_COLS + index]);
! else
! rel->attr[i].adsrc = NULL;
!
! index++;
!
! rel->attr[i].use_timestamp = *(res->data[i * NUM_COLS + index]) == 't';
! pool_debug("attrname %s adsrc %s use_timestamp = %d",
! rel->attr[i].attrname, (rel->attr[i].adsrc? rel->attr[i].adsrc:"NULL"),
! rel->attr[i].use_timestamp);
}
rel->relnatts = res->numrows;
***************
*** 109,115 ****
static TSRel*
relcache_lookup(TSRewriteContext *ctx)
{
! #define ATTRDEFQUERY "SELECT attname, coalesce((d.adsrc = 'now()' OR d.adsrc LIKE '%%''now''::text%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
--- 126,132 ----
static TSRel*
relcache_lookup(TSRewriteContext *ctx)
{
! #define ATTRDEFQUERY "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
***************
*** 121,127 ****
" WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.relname = '%s'" \
" ORDER BY a.attnum"
! #define ATTRDEFQUERY2 "SELECT attname, coalesce((d.adsrc = 'now()' OR d.adsrc LIKE '%%''now''::text%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
--- 138,144 ----
" WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.relname = '%s'" \
" ORDER BY a.attnum"
! #define ATTRDEFQUERY2 "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
***************
*** 348,354 ****
if (relcache->attr[i].use_timestamp)
{
rewrite = true;
! values = lappend(values, makeTsExpr(ctx));
}
else
values = lappend(values, makeNode(SetToDefault));
--- 365,375 ----
if (relcache->attr[i].use_timestamp)
{
rewrite = true;
! if (ctx->rewrite_to_params)
! values = lappend(values, makeTsExpr(ctx));
! else
! values = lappend(values,
! makeStringConstFromQuery(ctx->backend, relcache->attr[i].adsrc));
}
else
values = lappend(values, makeNode(SetToDefault));
***************
*** 411,417 ****
if (relcache->attr[i].use_timestamp == true && IsA(lfirst(lc_val), SetToDefault))
{
rewrite = true;
! lfirst(lc_val) = makeTsExpr(ctx);
}
i++;
}
--- 432,441 ----
if (relcache->attr[i].use_timestamp == true && IsA(lfirst(lc_val), SetToDefault))
{
rewrite = true;
! if (ctx->rewrite_to_params)
! lfirst(lc_val) = makeTsExpr(ctx);
! else
! lfirst(lc_val) = makeStringConstFromQuery(ctx->backend, relcache->attr[i].adsrc);
}
i++;
}
***************
*** 422,428 ****
if (relcache->attr[i].use_timestamp == true)
{
rewrite = true;
! values = lappend(values, makeTsExpr(ctx));
}
else
values = lappend(values, makeNode(SetToDefault));
--- 446,456 ----
if (relcache->attr[i].use_timestamp == true)
{
rewrite = true;
! if (ctx->rewrite_to_params)
! values = lappend(values, makeTsExpr(ctx));
! else
! values = lappend(values,
! makeStringConstFromQuery(ctx->backend, relcache->attr[i].adsrc));
}
else
values = lappend(values, makeNode(SetToDefault));
***************
*** 434,445 ****
/*
* INSERT INTO rel(col1, col2) VALUES (val, val2)
*
! * if timestamp column does not given by column list
* add colname to column list and add timestamp to values list.
*/
int append_columns = 0;
ResTarget *col;
for (i = 0; i < relcache->relnatts; i++)
{
if (relcache->attr[i].use_timestamp == false)
--- 462,476 ----
/*
* INSERT INTO rel(col1, col2) VALUES (val, val2)
*
! * if timestamp column is not given by column list
* add colname to column list and add timestamp to values list.
*/
int append_columns = 0;
+ int *append_columns_list;
ResTarget *col;
+ append_columns_list = (int *)malloc(sizeof(int)*relcache->relnatts);
+
for (i = 0; i < relcache->relnatts; i++)
{
if (relcache->attr[i].use_timestamp == false)
***************
*** 462,468 ****
col->indirection = NIL;
col->val = NULL;
i_stmt->cols = lappend(i_stmt->cols, col);
! append_columns++;
}
}
--- 493,499 ----
col->indirection = NIL;
col->val = NULL;
i_stmt->cols = lappend(i_stmt->cols, col);
! append_columns_list[append_columns++] = i;
}
}
***************
*** 483,497 ****
if (relcache->attr[i].use_timestamp == true && IsA(lfirst(lc_val), SetToDefault))
{
rewrite = true;
! lfirst(lc_val) = makeTsExpr(ctx);
}
}
/* add ts_const to values list */
for (i = 0; i < append_columns; i++)
{
! values = lappend(values, makeTsExpr(ctx));
}
}
}
}
--- 514,536 ----
if (relcache->attr[i].use_timestamp == true && IsA(lfirst(lc_val), SetToDefault))
{
rewrite = true;
! if (ctx->rewrite_to_params)
! lfirst(lc_val) = makeTsExpr(ctx);
! else
! lfirst(lc_val) = makeStringConstFromQuery(ctx->backend, relcache->attr[i].adsrc);
}
}
/* add ts_const to values list */
for (i = 0; i < append_columns; i++)
{
! if (ctx->rewrite_to_params)
! values = lappend(values, makeTsExpr(ctx));
! else
! values = lappend(values,
! makeStringConstFromQuery(ctx->backend, relcache->attr[append_columns_list[i]].adsrc));
}
+ free(append_columns_list);
}
}
}
***************
*** 543,549 ****
{
if (relcache->attr[i].use_timestamp)
{
! res->val = (Node *) makeTsExpr(ctx);
rewrite = true;
}
break;
--- 582,591 ----
{
if (relcache->attr[i].use_timestamp)
{
! if (ctx->rewrite_to_params)
! res->val = (Node *) makeTsExpr(ctx);
! else
! res->val = (Node *)makeStringConstFromQuery(ctx->backend, relcache->attr[i].adsrc);
rewrite = true;
}
break;
***************
*** 813,818 ****
--- 855,893 ----
return new_msg;
}
+ static A_Const *makeStringConstFromQuery(POOL_CONNECTION_POOL *backend, char *expression)
+ {
+ A_Const *con;
+ POOL_SELECT_RESULT *res;
+ POOL_STATUS status;
+ char query[1024];
+ int len;
+ char *str;
+
+ snprintf(query, sizeof(query), "SELECT %s", expression);
+ status = do_query(MASTER(backend), query, &res, MAJOR(backend));
+ if (status != POOL_CONTINUE)
+ {
+ pool_error("makeStringConstFromQuery: do_query faild");
+ return NULL;
+ }
+
+ if (res->numrows != 1)
+ {
+ free_select_result(res);
+ return NULL;
+ }
+
+ len = strlen(res->data[0]) + 1;
+ str = palloc(len);
+ strcpy(str, res->data[0]);
+ free_select_result(res);
+
+ con = makeNode(A_Const);
+ con->val.type = T_String;
+ con->val.val.str = str;
+ return con;
+ }
/* from nodeFuncs.c start */
_______________________________________________
Pgpool-hackers mailing list
Pgpool-hackers@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-hackers
