Re: #Deleted strikes again

Peter Bense wrote:

>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
>
>
I'm inclined to be skeptical about the time-out question, and would
focus more on why the driver has trouble translating between field
types. In some situations it will see integer fields as text, in others
as decimal. The #deleted gets displayed because Access knows something
is there, but it can't resolve the data to display it.

I'm not sure how to resolve this issue successfully. How do you test
for a time-out?

>Peter T. Bense - Teradata Certified Professional
>(ptbense(at)gwm(dot)sc(dot)edu) - 803-777-9476
>Database Administrator/Webmaster
>Prevention Research Center
>University of South Carolina
>
>
>>>>Steve Backman <earlysteve(at)earthlink(dot)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(at)gwm(dot)sc(dot)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(at)postgresql(dot)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
>
>
>
>