Re: #Deleted strikes again - Mailing list pgsql-odbc
From | Peter Bense |
---|---|
Subject | Re: #Deleted strikes again |
Date | |
Msg-id | s229b204.044@gwm.sc.edu Whole thread Raw |
In response to | #Deleted strikes again ("Peter Bense" <Ptbense@gwm.sc.edu>) |
List | pgsql-odbc |
To me it seems like if it was a time-out issue, we would see it on more tables than just this particular one returning this error? Actually, for is it is two tables specifically that return this error. In any case -- I think it is important to know whether or not connection time-out is something which is handled on a per-link-table basis? That's an issue that must be clarified if we're to energize on taking further measures to resolve time-out issues as being our most plausible cause of error... ./peter Peter T. Bense - Teradata Certified Professional (ptbense@gwm.sc.edu) - 803-777-9476 Database Administrator/Webmaster Prevention Research Center University of South Carolina >>> Steve Backman <earlysteve@earthlink.net> 03/05/05 7:38 AM >>> Peter, As you know, I have been wrestling with similar things for months in one application. One application only among several using the same technology. After trying many different things, I can only believe there is some kind of timing/time out issues that bring on loss of connection. You can open and close a table (just a table, no query) in this application over and over again with no problem, and then a bit later, open it once can see #delete# or else get general odbc error. And it also seems to happen more frequently the slower the envionment making the connection. Maybe its time out related in conjunction with some subtle settings that we have not been able to hone in on. At this point, we're facing rewriting this application to use client-server architecture, retrieving data on the fly as it needs it in code and then writing it back. As for the things you use here, we have gone back to using older pd driver in stead of 8x. At 3/4/2005 07:57 PM, Peter Bense wrote: >Ladies and Gentlemen, > >The much feared #Deleted phenomena is has struck again, but this time >I'm more well prepared (i.e. better documented) after turning on error >logging... :) I also have gone to efforts to gather more details about >the nature of our problem: > >Operating system on server side: Gentoo Linux - 2.4.x kernel >Postgresq server version: 7.4.6 >ODBC Driver Version: 8.00.04 >Client platform: MS-Access 2000 [and 2003, I think?] >Client OS: Windows XP & 2000 > >Some information about the view (and underlying table) which today >demonstrated this issue: > >afl=# \d vi_tblaled_groupmember > View "public.vi_tblaled_groupmember" > Column | Type | Modifiers >----------------+----------------------+----------- > groupmember_id | integer | > group_id | character varying(3) | > ppt_id | integer | >View definition: > SELECT tblaled_groupmember.groupmember_id, >tblaled_groupmember.group_id, tblaled_groupmember.ppt_id > FROM tblaled_groupmember > WHERE (tblaled_groupmember.ppt_id IN ( SELECT >tblpis_participant.ppt_id > FROM tblpis_participant > WHERE tblpis_participant.login::name = "current_user"())); >Rules: tblaled_groupmember_del, > tblaled_groupmember_in, > tblaled_groupmember_upd > >....and here's the base table: > >afl=# \d tblaled_groupmember > Table >"public.tblaled_groupmember" > Column | Type | > Modifiers >----------------+----------------------+--------------------------------------------------------------------------------- > groupmember_id | integer | not null default >nextval('public.tblaled_groupmember_groupmember_id_seq'::text) > group_id | character varying(3) | not null > ppt_id | integer | not null >Indexes: > "tblaled_groupmember_ppt_id_key" unique, btree (ppt_id) > >Today the guy who is doing most of the application development sent me >an e-mail stating that the error occured around 3:01 PM local time. So >I started perusing the logfiles. > >Below I have pasted the query before, query after, and the query in >question which returns BIZARRE results: > >15:02:56[681-1] LOG: statement: SELECT "group_id","group_descriptor" >FROM "public"."vi_tblaled_group" WHERE "group_id" = '3' OR "group_id" >= >15:02:56[681-2] '2' OR "group_id" = '1' OR "group_id" = '1' OR >"group_id" = '1' OR "group_id" = '1' OR "group_id" = '1' OR "group_id" = >'1' OR >15:02:56[681-3] "group_id" = '1' OR "group_id" = '1' >15:02:56[682-1] LOG: statement: SELECT "groupmember_id","group_id" >FROM "public"."vi_tblaled_groupmember" WHERE "groupmember_id" = 1534 >OR >15:02:56[683-2] '1' OR "group_id" = '1' OR "group_id" = '1' OR >"group_id" = '1' OR "group_id" = '1' OR "group_id" = '1' OR "group_id" = >'1' OR >15:02:56[683-3] "group_id" = '1' OR "group_id" = '1' >15:02:57[684-1] LOG: statement: SELECT "groupmember_id","group_id" >FROM "public"."vi_tblaled_groupmember" WHERE "groupmember_id" = 9 OR >15:02:57[684-2] "groupmember_id" = 10 OR "groupmember_id" = 11 OR >"groupmember_id" = 12 OR "groupmember_id" = 13 OR "groupmember_id" = 14 >OR >15:02:57[684-3] "groupmember_id" = 15 OR "groupmember_id" = 16 OR >"groupmember_id" = 17 OR "groupmember_id" = 18 > >The query which gives odd results is #682. Note the results upon >execution (I believe this is some kind of indirect UNION query??) >Notice how it elected to fill out the OR "group_id" = '1' multiple >times. I can't understand why it would want to pass ODBC this query, >and have a feeling that the order that the results were returned in is >causing MS-Access to panic?... > >afl=> SELECT "groupmember_id","group_id" FROM >"public"."vi_tblaled_groupmember" WHERE "groupmember_id" = 1534 OR '1' >OR "group_id" = '1' OR "group_id" = '1' OR "group_id" = '1' OR >"group_id" = '1' OR "group_id" = '1' OR "group_id" = '1' OR "group_id" = >'1' OR "group_id" = '1'; > groupmember_id | group_id >----------------+---------- > 179 | 11 > 29 | 2 > 178 | 11 > 28 | 2 > 177 | 11 > 27 | 2 > 176 | 11 > 26 | 2 > 175 | 11 > 25 | 2 > 174 | 11 > 24 | 2 > 173 | 11 > 23 | 2 > 172 | 8 > 22 | 3 > 171 | 7 > 21 | 2 > 170 | 10 > 20 | 1 > 169 | 10 > 19 | 1 > 168 | 10 > 18 | 1 > 167 | 10 > 17 | 1 > 166 | 10 > 16 | 1 > 165 | 10 > 15 | 1 > 164 | 10 > 14 | 1 > 163 | 10 > 13 | 1 > 162 | 10 > 12 | 1 > 161 | 10 > 160 | 8 > 11 | 1 > 10 | 1 > 159 | 8 > 9 | 1 > 158 | 8 > 8 | 1 > 157 | 9 > 352 | 20 > 7 | 1 > 156 | 9 > 155 | 9 > 154 | 9 > 6 | 1 > 5 | 1 > 153 | 9 > 4 | 1 > 152 | 9 > 3 | 1 > 151 | 9 > 2 | 1 > 150 | 9 > 1 | 1 > 149 | 8 > 148 | 7 > 147 | 8 > 146 | 8 > 145 | 8 > 144 | 8 > 143 | 8 > 142 | 8 > 141 | 8 > 140 | 8 > 139 | 8 > 138 | 8 > 137 | 6 > 136 | 7 > 135 | 7 > 134 | 7 > 133 | 7 > 132 | 7 > 131 | 7 > 130 | 7 > 129 | 7 > 128 | 7 > 127 | 7 > 93 | 5 > 126 | 7 > 254 | 16 > 92 | 5 > 125 | 7 > 253 | 16 > 91 | 5 > 124 | 7 > 252 | 16 > 90 | 5 > 123 | 7 > 251 | 16 > 89 | 5 > 122 | 6 > 250 | 16 > 88 | 5 > 121 | 6 > 249 | 16 > 87 | 5 > 120 | 6 > 248 | 16 > 86 | 5 > 119 | 6 > 247 | 16 > 85 | 5 > 118 | 6 > 246 | 16 > 84 | 5 > 117 | 6 > 245 | 12 > 116 | 6 > 244 | 15 > 83 | 5 > 115 | 6 > 243 | 14 > 82 | 5 > 114 | 6 > 242 | 15 > 81 | 5 > 113 | 6 > 241 | 9 > 80 | 5 > 112 | 6 > 240 | 9 > 79 | 5 > 111 | 6 > 239 | 11 > 78 | 5 > 110 | 6 > 238 | 11 > 77 | 5 > 109 | 6 > 237 | 11 > 108 | 6 > 236 | 15 > 76 | 5 > 107 | 6 > 235 | 15 > 75 | 5 > 106 | 6 > 234 | 15 > 74 | 4 > 105 | 6 > 233 | 15 > 73 | 4 > 104 | 6 > 232 | 15 > 72 | 4 > 103 | 6 > 231 | 15 > 351 | 19 > 71 | 4 > 102 | 6 > 230 | 15 > 350 | 18 > 70 | 4 > 101 | 6 > 229 | 15 > 349 | 20 > 228 | 15 > 348 | 20 > 69 | 4 > 227 | 15 > 347 | 20 > 68 | 4 > 226 | 15 > 346 | 21 > 67 | 4 > 225 | 15 > 345 | 21 > 66 | 4 > 224 | 15 > 344 | 21 > 65 | 4 > 223 | 15 > 343 | 21 > 64 | 4 > 222 | 14 > 342 | 21 > 63 | 4 > 221 | 11 > 341 | 21 > 62 | 4 > 220 | 14 > 340 | 21 > 61 | 4 > 219 | 14 > 339 | 21 > 60 | 4 > 218 | 14 > 338 | 21 > 59 | 4 > 217 | 14 > 337 | 21 > 58 | 4 > 216 | 15 > 336 | 21 > 57 | 4 > 215 | 14 > 335 | 21 > 56 | 4 > 214 | 14 > 334 | 21 > 55 | 0 > 213 | 14 > 333 | 22 > 212 | 14 > 332 | 22 > 211 | 14 > 331 | 20 > 210 | 14 > 330 | 20 > 209 | 14 > 208 | 14 > 329 | 20 > 207 | 15 > 328 | 20 > 54 | 3 > 206 | 14 > 327 | 20 > 205 | 14 > 53 | 2 > 326 | 19 > 52 | 2 > 204 | 14 > 325 | 18 > 51 | 1 > 203 | 14 > 324 | 18 > 50 | 1 > 202 | 13 > 323 | 18 > 49 | 3 > 201 | 13 > 322 | 17 > 200 | 13 > 321 | 19 > 48 | 3 > 199 | 13 > 320 | 19 > 47 | 3 > 198 | 13 > 319 | 19 > 46 | 2 > 197 | 13 > 318 | 19 > 45 | 2 > 196 | 13 > 317 | 19 > 44 | 3 > 195 | 13 > 316 | 20 > 43 | 3 > 194 | 13 > 315 | 19 > 42 | 2 > 193 | 13 > 314 | 19 > 41 | 2 > 192 | 13 > 313 | 19 > 40 | 2 > 191 | 12 > 312 | 19 > 190 | 12 > 311 | 19 > 39 | 2 > 189 | 12 > 310 | 19 > 38 | 3 > 188 | 12 > 309 | 19 > 37 | 3 > 187 | 12 > 308 | 19 > 36 | 3 > 186 | 12 > 307 | 19 > 1535 | 2 > 35 | 3 > 185 | 12 > 306 | 19 > 34 | 2 > 184 | 12 > 305 | 20 > 1533 | 3 > 33 | 3 > 183 | 12 > 304 | 20 > 1532 | 2 > 32 | 3 > 182 | 12 > 303 | 23 > 1531 | 2 > 1534 | 3 > 31 | 3 > 181 | 11 > 302 | 23 > 1530 | 2 > 30 | 2 > 180 | 11 > 301 | 23 > 300 | 23 > 299 | 23 > 298 | 23 > 297 | 23 > 296 | 23 > 295 | 23 > 294 | 23 > 293 | 20 > 292 | 22 > 291 | 22 > 290 | 22 > 289 | 22 > 288 | 22 > 287 | 22 > 286 | 22 > 285 | 22 > 284 | 22 > 283 | 22 > 282 | 22 > 281 | 22 > 280 | 22 > 279 | 18 > 278 | 18 > 277 | 18 > 276 | 18 > 275 | 18 > 274 | 18 > 273 | 18 > 272 | 18 > 271 | 19 > 270 | 17 > 269 | 17 > 268 | 17 > 267 | 17 > 266 | 17 > 265 | 16 > 264 | 15 > 100 | 5 > 263 | 16 > 99 | 5 > 262 | 16 > 98 | 5 > 261 | 16 > 97 | 5 > 260 | 16 > 96 | 5 > 259 | 16 > 258 | 16 > 95 | 5 > 257 | 16 > 94 | 5 > 256 | 16 > 255 | 9 >(358 rows) > > >The problem disappears when MS-Access is closed and re-opened. > >Any ideas? > >./peter > > >Peter T. Bense - Teradata Certified Professional >(ptbense@gwm.sc.edu) - 803-777-9476 >Database Administrator/Webmaster >Prevention Research Center >University of South Carolina > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > >-- >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.6.0 - Release Date: 3/2/2005 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
pgsql-odbc by date: