logo       
<prev   next>

Re: about PGCluster and JDBC Driver question (On Solaris 8 OS): msg#00046

Subject: Re: about PGCluster and JDBC Driver question (On Solaris 8 OS) 
This is a multi-part message in MIME format.
Hi Peter,

I'm sure Atsushi will put it into the next release.

Kind Regards,

Christian


Peter Duong wrote:
Hi Christian,

Thank you for the patch.  This seems to pass my simple java test.  I'll continue with further testings.  Do you know whether this patch will be integrate into the mainstream code?  Because I do see the same problem in 1.7 rc1. 

best regards,

Peter

On 12/16/06, Christian Dannemann <reports@xxxxxxxxxxx> wrote:
Hi Peter,

this is a bug. Atshushi has written a patch for it that I have attached - but the patch is for 1.5 rc11, so please unzip/untar that, run the patch against it (path -p0 < skip_select3.patch) and try again. Did the trick for me.

Kind Regards,

Christian


Peter Duong wrote:
Hi Christian,

I ran into the similar JDBC problem with 1.5 r10 and 1.7 rc1 in 2 nodes configuration.  The cluster seems to stop responding after I made a jdbc call.  (testing with postgresql-8.1-408.jdbc3.jar and postgresql-8.2-504.jdbc3.jar )

Here are the errors I got from pgreplicate log

2006-12-14 10:48:40 [25918] ERROR:read_answer():none response packet received
2006-12-14 10:48:40 [25918] ERROR:thread_send_source(): invxmdev03[5432] should be down
2006-12-14 10:48:58 [25926] ERROR:read_answer():none response packet received
2006-12-14 10:48:58 [25926] ERROR:thread_send_source(): invxmdev04[5432] should be down

I also attached the verbose log from the pgreplicate.log.  I notice in a previous mail archive you mentioned about v2 protocol query with JDBC not working.  Is this related? and is there a solution to it yet?

Thanks,

Peter

On 12/14/06, Christian Dannemann <reports@xxxxxxxxxxx > wrote:
Hi Betty,

can you run pgreplicate with verbose output and check for ERROR in the log file?

Thanks,

Christian

Sun.betty wrote:
everybody here:
          How are you!
 
about PGCluster and JDBC Driver question (On Solaris 8 OS)
concurrent transactions test on PGCluster
use pure C program : pgbench -i -c 10 -t 1000 -U test -h 127.0.0.1 postgres
all is OK. because pgbench use ODBC Driver
then
use java program : JDBCBench.java
use JDBC Driver

java -classpath .:./postgresql-8.1-407.jdbc2.jar JDBCBench -driver org.postgresql.Driver -url jdbc:postgresql://127.0.0.1:5432/postgres?user=test -v -tpc 132 -clients 10
it will hang up. no answer and info show. then access to db , select operation is OK, insert can't , delete can't , update can't
anybody know what's wrong , How to work it out?
 
Thanks for help
 
Sincerely
---------------
Betty

ÇÀ×¢ÑÅ»¢Ãâ·ÑÓÊÏä-3.5GÈÝÁ¿£¬20M¸½¼þ£¡ 

_______________________________________________
Pgcluster-general mailing list
Pgcluster-general@xxxxxxxxxxxxx


http://pgfoundry.org/mailman/listinfo/pgcluster-general
  


-- 
Christian Denning
Development Manager
DDI: +44 (0)161 241 8602

_______________________________________________
Pgcluster-general mailing list
Pgcluster-general@xxxxxxxxxxxxx
http://pgfoundry.org/mailman/listinfo/pgcluster-general




_______________________________________________
Pgcluster-general mailing list
Pgcluster-general@xxxxxxxxxxxxx

http://pgfoundry.org/mailman/listinfo/pgcluster-general
  


-- 
Christian Denning
Development Manager
DDI: +44 (0)161 241 8602

diff -aruN pgcluster-1.5.0rc11/configure pgcluster-1.5.0rc12/configure
--- pgcluster-1.5.0rc11/configure       2006-12-09 01:41:33.000000000 +0100
+++ pgcluster-1.5.0rc12/configure       2006-12-12 22:28:05.000000000 +0100
@@ -275,7 +275,7 @@
 PACKAGE_STRING='PostgreSQL 8.1.5'
 PACKAGE_BUGREPORT='pgsql-bugs@xxxxxxxxxxxxxx'

-PGCLUSTER_VERSION='1.5.0rc11'
+PGCLUSTER_VERSION='1.5.0rc12'

 ac_unique_file="src/backend/access/common/heaptuple.c"
 ac_default_prefix=/usr/local/pgsql
diff -aruN pgcluster-1.5.0rc11/src/backend/commands/prepare.c pgcluster-1.5.0rc12/src/backend/commands/prepare.c
--- pgcluster-1.5.0rc11/src/backend/commands/prepare.c  2005-12-14 18:06:37.000000000 +0100
+++ pgcluster-1.5.0rc12/src/backend/commands/prepare.c  2006-12-13 11:01: 14.329866116 +0100
@@ -28,6 +28,9 @@
 #include "utils/hsearch.h"
 #include "utils/memutils.h"

+#ifdef USE_REPLICATION
+#include "replicate.h"
+#endif /* USE_REPLICATION */

 /*
 * The hash table in which prepared queries are stored. This is
@@ -657,3 +660,26 @@
       if (estate)
               FreeExecutorState(estate);
 }
+
+#ifdef USE_REPLICATION
+bool
+PGR_is_select_prepared_statement(PrepareStmt *stmt)
+{
+       PreparedStatement *entry;
+       if ((stmt == NULL) || (stmt->name == NULL))
+       {
+               return false;
+       }
+       entry = FetchPreparedStatement(stmt->name, true);
+       if (entry == NULL)
+       {
+               return false;
+       }
+       if (!strcmp(entry->commandTag,"SELECT"))
+       {
+               return true;
+       }
+       return false;
+}
+#endif /* USE_REPLICATION */
+
diff -aruN pgcluster-1.5.0rc11/src/backend/libpq/replicate.c pgcluster-1.5.0rc12/src/backend/libpq/replicate.c
--- pgcluster-1.5.0rc11/src/backend/libpq/replicate.c   2006-12-08 17:28: 22.000000000 +0100
+++ pgcluster-1.5.0rc12/src/backend/libpq/replicate.c   2006-12-13 12:05:18.575420185 +0100
@@ -60,12 +60,15 @@
 #include "libpq/libpq.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
+#include "commands/prepare.h"
 #include "nodes/nodes.h"
 #include "nodes/print.h"
 #include "utils/guc.h"
 #include "parser/parser.h"
 #include "access/xact.h"
 #include "storage/proc.h"
+#include "tcop/tcopprot.h"
+#include "tcop/utility.h"
 #include "postmaster/postmaster.h"
 #include " replicate.h"

@@ -73,6 +76,7 @@
 #define VAR_OID_PREFETCH                (8192)

 PGR_ReplicationLog_Info ReplicationLog_Info;
+bool pgr_skip_in_prepared_query = false;

 /*--------------------------------------
 * PROTOTYPE DECLARATION
@@ -114,7 +118,11 @@
 static int set_replication_id(char * id);
 static int return_current_oid(void);
 static int sync_oid(char * oid);
-
+static int skip_non_blank(char * ptr, int max);
+static int skip_blank(char * ptr, int max);
+static int parse_message(char * query_string);
+static bool is_prepared_as_select(char * query_string);
+static bool is_statement_as_select(char * query_string);
 extern ssize_t secure_read(Port *, void *, size_t);
 /*--------------------------------------------------------------------
 * SYMBOL
@@ -1674,10 +1682,10 @@
       }
       else if (!strcmp(commandTag,"SET"))
       {
-               VariableSetStmt *stmt = (VariableSetStmt *)parsetree;
               cmdType = CMD_TYPE_SET;

               /*
+               VariableSetStmt *stmt = (VariableSetStmt *)parsetree;
               if (strcasecmp(stmt->name, "TRANSACTION ISOLATION LEVEL") &&
                       strcasecmp(stmt->name, "datestyle") &&
                       strcasecmp(stmt->name, "autocommit") &&
@@ -3565,6 +3573,40 @@
       }
       len = input_message->len+1;
       ptr = input_message->data;
+       if (cmdType == CMD_TYPE_P_PARSE)
+       {
+               switch (parse_message(ptr+1))
+               {
+                       case PGR_MESSAGE_SELECT:
+                               pgr_skip_in_prepared_query = true;
+                               break;
+                       case PGR_MESSAGE_PREPARE:
+                               if (is_prepared_as_select(ptr+1) == true)
+                               {
+                                       pgr_skip_in_prepared_query = true;
+                               }
+                               break;
+                       case PGR_MESSAGE_EXECUTE:
+                       case PGR_MESSAGE_DEALLOCATE:
+                               if (is_statement_as_select(ptr+1) == true)
+                               {
+                                       pgr_skip_in_prepared_query = true;
+                               }
+                               break;
+               }
+               if (pgr_skip_in_prepared_query == true)
+               {
+                       return STATUS_OK;
+               }
+       }
+       if (pgr_skip_in_prepared_query == true)
+       {
+               if (cmdType == CMD_TYPE_P_SYNC)
+               {
+                       pgr_skip_in_prepared_query = false;
+               }
+               return STATUS_OK;
+       }
       result = PGR_Send_Replicate_Command(ptr,len, CMD_STS_PREPARE,cmdType);
       if (result != NULL)
       {
@@ -3579,4 +3621,210 @@
       }
 }

+static int
+skip_non_blank(char * ptr, int max)
+{
+       int i= 0;
+       while(!isspace(*(ptr+i)))
+       {
+               if ((*(ptr+1) == '(') || (*(ptr+1) == ')'))
+               {
+                       return i;
+               }
+               i++;
+               if (i > max)
+                       return -1;
+       }
+       return i;
+}
+
+static int
+skip_blank(char * ptr, int max)
+{
+       int i = 0;
+       while(isspace(*(ptr+i)))
+       {
+               i++;
+               if (i > max)
+                       return -1;
+       }
+       return i;
+}
+
+static int
+parse_message(char * query_string)
+{
+       char * ptr =NULL;
+       int rtn = 0;
+       int i = 0;
+       int len = 0;
+       if (query_string == NULL)
+       {
+               return PGR_MESSAGE_OTHER;
+       }
+       len = strlen (query_string);
+       if (len <= 0)
+       {
+               return PGR_MESSAGE_OTHER;
+       }
+       ptr = (char *)query_string;
+       i = 0;
+       /* skip space */
+       rtn = skip_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return PGR_MESSAGE_OTHER;
+       i += rtn;
+
+       if (!strncasecmp(ptr+i,"SELECT",strlen("SELECT")))
+       {
+               return PGR_MESSAGE_SELECT;
+       }
+       if (!strncasecmp(ptr+i,"PREPARE",strlen("PREPARE")))
+       {
+               return PGR_MESSAGE_PREPARE;
+       }
+       if (!strncasecmp(ptr+i,"EXECUTE",strlen("EXECUTE")))
+       {
+               return PGR_MESSAGE_EXECUTE;
+       }
+       if (!strncasecmp(ptr+i,"DEALLOCATE",strlen("DEALLOCATE")))
+       {
+               return PGR_MESSAGE_DEALLOCATE;
+       }
+       return PGR_MESSAGE_OTHER;
+}
+
+static bool
+is_prepared_as_select(char * query_string)
+{
+       char * ptr =NULL;
+       int rtn = 0;
+       int i = 0;
+       int len = 0;
+       int args =0;
+       if (query_string == NULL)
+       {
+               return false;
+       }
+       ptr = (char *)query_string;
+       len = strlen (query_string);
+       i = 0;
+       /* skip "PREPARE" word */
+       rtn = skip_non_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       /* skip space */
+       rtn = skip_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       /* skip plan_name */
+       rtn = skip_non_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       /* skip space */
+       rtn = skip_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       /* skip args */
+       args = 0;
+       if (*(ptr+i) == '(')
+       {
+               args ++;
+               i++;
+               while(args > 0)
+               {
+                       if (*(ptr+i) == ')')
+                               args --;
+                       else if (*(ptr+i) == '(')
+                               args ++;
+                       i++;
+                       if (i >= len)
+                               return false;
+               }
+               /* skip space */
+               rtn = skip_blank(ptr+i, len-i);
+               if (rtn < 0)
+                       return false;
+               i += rtn;
+       }
+       /* skip "AS" word */
+       i += strlen("AS");
+       if (i >= len)
+               return false;
+       /* skip space */
+       rtn = skip_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       /* check "SELECT" word */
+       if (len-i < strlen("SELECT"))
+               return false;
+       if (!strncasecmp(ptr+i,"SELECT",strlen("SELECT")))
+       {
+               return true;
+       }
+       return false;
+
+}
+
+static bool
+is_statement_as_select(char * query_string)
+{
+       char * ptr =NULL;
+       int rtn = 0;
+       int i = 0;
+       int j = 0;
+       int len = 0;
+       bool result = false;
+       PrepareStmt stmt;
+       char * name = NULL;
+       if (query_string == NULL)
+       {
+               return false;
+       }
+       ptr = (char *)query_string;
+       len = strlen (query_string);
+       i = 0;
+       /* skip "EXECUTE" or "DEALLOCATE" word */
+       rtn = skip_non_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       /* skip space */
+       rtn = skip_blank(ptr+i, len-i);
+       if (rtn < 0)
+               return false;
+       i += rtn;
+       if ((name = malloc(len)) == NULL)
+               return false;
+       memset(name,0,len);
+       j = 0;
+       while(isalnum(*(ptr+i)))
+       {
+               *(name+j) = *(ptr+i);
+               i++;
+               j++;
+               if (i > len)
+                       return false;
+       }
+       stmt.name = name;
+       result = PGR_is_select_prepared_statement(&stmt);
+       free(name);
+       return result;
+}
+
+bool
+PGR_is_select_prepare_query(void)
+{
+       if (debug_query_string == NULL)
+       {
+               return false;
+       }
+       return (is_prepared_as_select((char *)debug_query_string));
+}
+
 #endif /* USE_REPLICATION */
diff -aruN pgcluster-1.5.0rc11/src/backend/tcop/utility.c pgcluster-1.5.0rc12/src/backend/tcop/utility.c
--- pgcluster-1.5.0rc11/src/backend/tcop/utility.c      2006-11-15 12:15:17.000000000 +0100
+++ pgcluster-1.5.0rc12/src/backend/tcop/utility.c      2006-12-13 01:40:59.000000000 +0100
@@ -1730,14 +1730,32 @@

               case T_PrepareStmt:
                       tag = "PREPARE";
+#ifdef USE_REPLICATION
+                       if (PGR_is_select_prepare_query())
+                       {
+                               PGR_Not_Replication_Query = true;
+                       }
+#endif /* USE_REPLICATION */
                       break;

               case T_ExecuteStmt:
                       tag = "EXECUTE";
+#ifdef USE_REPLICATION
+                       if (PGR_is_select_prepared_statement((PrepareStmt *)parsetree))
+                       {
+                               PGR_Not_Replication_Query = true;
+                       }
+#endif /* USE_REPLICATION */
                       break;

               case T_DeallocateStmt:
                       tag = "DEALLOCATE";
+#ifdef USE_REPLICATION
+                       if (PGR_is_select_prepared_statement((PrepareStmt *)parsetree))
+                       {
+                               PGR_Not_Replication_Query = true;
+                       }
+#endif /* USE_REPLICATION */
                       break;

               default:
diff -aruN pgcluster-1.5.0rc11/src/include/commands/prepare.h pgcluster-1.5.0rc12/src/include/commands/prepare.h
--- pgcluster-1.5.0rc11 /src/include/commands/prepare.h  2005-12-14 18:06:37.000000000 +0100
+++ pgcluster-1.5.0rc12/src/include/commands/prepare.h  2006-12-13 11:01:45.000000000 +0100
@@ -62,4 +62,7 @@
 extern bool PreparedStatementReturnsTuples(PreparedStatement *stmt);
 extern List *FetchPreparedStatementTargetList(PreparedStatement *stmt);

+#ifdef USE_REPLICATION
+extern bool PGR_is_select_prepared_statement(PrepareStmt *stmt);
+#endif /* USE_REPLICATION */
 #endif   /* PREPARE_H */
diff -aruN pgcluster-1.5.0rc11/src/include/replicate.h pgcluster-1.5.0rc12/src/include/replicate.h
--- pgcluster-1.5.0rc11/src/include/replicate.h 2006-11-15 12:15:17.000000000 +0100
+++ pgcluster-1.5.0rc12/src/include/replicate.h 2006-12-13 11:12: 33.000000000 +0100
@@ -73,6 +73,12 @@
 #define PGR_HOT_RECOVERY (2)
 #define PGR_WITHOUT_BACKUP (3)

+#define PGR_MESSAGE_OTHER (0)
+#define PGR_MESSAGE_SELECT (1)
+#define PGR_MESSAGE_PREPARE (2)
+#define PGR_MESSAGE_EXECUTE (3)
+#define PGR_MESSAGE_DEALLOCATE (4)
+
 typedef struct
 {
       bool is_stand_alone;
@@ -194,6 +200,7 @@
 extern uint32_t PGRget_replication_id(void);
 extern Oid PGRGetNewObjectId(Oid last_id);
 extern int PGR_Send_Input_Message(char cmdType,StringInfo input_message);
+extern bool PGR_is_select_prepare_query(void);

 /* in backend/libpq/recovery.c */
 extern int PGR_Master_Main(void);
diff -aruN pgcluster-1.5.0rc11/src/pgcluster/pglb/pglb.h pgcluster-1.5.0rc12/src/pgcluster/pglb/pglb.h
--- pgcluster-1.5.0rc11/src/pgcluster/pglb/pglb.h       2006-12-09 01:42:01.000000000 +0100
+++ pgcluster-1.5.0rc12/src/pgcluster/pglb/pglb.h       2006-12-12 22:28: 22.000000000 +0100
@@ -8,7 +8,7 @@
 #ifndef PGLB_H
 #define PGLB_H

-#define PGLB_VERSION   "1.5.0rc11"
+#define PGLB_VERSION   "1.5.0rc12"

 #include "../libpgc/libpgc.h"

diff -aruN pgcluster-1.5.0rc11/src/pgcluster/pgrp/pgreplicate.h pgcluster-1.5.0rc12/src/pgcluster/pgrp/pgreplicate.h
--- pgcluster-1.5.0rc11/src/pgcluster/pgrp/pgreplicate.h        2006-12-09 01:42:17.000000000 +0100
+++ pgcluster-1.5.0rc12/src/pgcluster/pgrp/pgreplicate.h        2006-12-12 22:28:29.000000000 +0100
@@ -8,7 +8,7 @@
 #ifndef PGREPLICATE_H
 #define PGREPLICATE_H

-#define PGREPLICATE_VERSION            " 1.5.0rc11"
+#define PGREPLICATE_VERSION            "1.5.0rc12"

 #include "lib/dllist.h"
 #include "lib/stringinfo.h"
diff -aruN pgcluster-1.5.0rc11/src/pgcluster/pgrp/replicate.c pgcluster-1.5.0rc12/src/pgcluster/pgrp/replicate.c
--- pgcluster-1.5.0rc11/src/pgcluster/pgrp/replicate.c  2006-11-15 12:15:17.000000000 +0100
+++ pgcluster-1.5.0rc12/src/pgcluster/pgrp/replicate.c  2006-12-12 10:23: 47.000000000 +0100
@@ -1901,7 +1901,11 @@
 {
       bool rtn = false;

-       if ((header->cmdType == CMD_TYPE_COPY) ||
+       if (header->cmdSts == CMD_STS_PREPARE)
+       {
+               rtn = false;
+       }
+       else if ((header->cmdType == CMD_TYPE_COPY) ||
               (header->cmdType == CMD_TYPE_COPY_DATA) ||
               (header->cmdType == CMD_TYPE_COPY_DATA_END))
       {


_______________________________________________
Pgcluster-general mailing list
Pgcluster-general@xxxxxxxxxxxxx
http://pgfoundry.org/mailman/listinfo/pgcluster-general




_______________________________________________
Pgcluster-general mailing list
Pgcluster-general@xxxxxxxxxxxxx
http://pgfoundry.org/mailman/listinfo/pgcluster-general
  


-- 
Christian Denning
Development Manager
DDI: +44 (0)161 241 8602
<Prev in Thread] Current Thread [Next in Thread>
Previous by Date:  Re: about PGCluster and JDBC Driver question (On Solaris 8 OS), Peter Duong
Next by Date:  Re: about PGCluster and JDBC Driver question (On Solaris 8 OS), Thang Duong
Previous by Thread:  Re: about PGCluster and JDBC Driver question (On Solaris 8 OS), Peter Duong
Next by Thread:  Re: about PGCluster and JDBC Driver question (On Solaris 8 OS), Thang Duong
Indexes:  [Date] [Thread] [Top] [All Lists]

Google Custom Search
Recently Viewed:
science.linguis...    culture.sf.lite...    video.mplayer.c...    yellowdog.gener...    ietf.rfc822/199...    emacs.help/2002...    redhat.release....    kernel.speakup/...    java.openejb.de...    debian.devel.gt...    xfree86.newbie/...    bug-tracking.ma...    pam/2003-05/msg...    games.devel.ope...    user-groups.lin...    music.pancham/2...    network.mq.deve...    web.html.genera...    arklinux.bugs/2...    linux.ecasound/...    qnx.openqnx.dev...    org.user-groups...    file-systems.sf...    trustix.contrib...   
Home | blog view | USPTO Patent Archive | advertise | OSDir is an inevitable website. super tiny logo

Free Magazines

Cisco News
Receive a free quarterly e-newsletter with exclusive articles on how Cisco IT uses its own products and solutions to enable the business.
subscribe

Systems Management News, the newspaper for IT systems administration and data center managers! Each issue of Systems Management News is chock-full of news and analysis to help you understand what's happening in your field.
subscribe

The Enterprise Newsweekly eWeek is the essential technology information source for builders of e-business.
subscribe

Oracle Magazine Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world's largest enterprise software company.
subscribe

Total Telecom Total Telecom is "The Economist of the communications industry".
subscribe