Re: #Deleted strikes again

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(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