> 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)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
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 25 Jun 2011 08:19:07 -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
***************
*** 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;
--- 84,95 ----
for (i = 0; i < res->numrows; i++)
{
! rel->attr[i].attrname = strdup(res->data[i * 3]);
! if (res->data[i * 2 + 1])
! rel->attr[i].adsrc = strdup(res->data[i * 3 + 1]);
! rel->attr[i].use_timestamp = *(res->data[i * 3 + 2]) == 't';
! pool_debug("attrname %s adsrc %s use_timestamp = %d",
! rel->attr[i].attrname, rel->attr[i].adsrc, 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" \
--- 113,119 ----
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" \
--- 125,131 ----
" 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));
--- 352,362 ----
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++;
}
--- 419,428 ----
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));
--- 433,443 ----
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)
--- 449,463 ----
/*
* 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++;
}
}
--- 480,486 ----
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));
}
}
}
}
--- 501,523 ----
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;
--- 569,578 ----
{
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 ****
--- 842,880 ----
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
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-hackers
