Re: #Deleted strikes again

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