Обсуждение: #Deleted strikes again
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
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
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
If its field types, its not from having access translate pg bigints to
decimals, because we tried that.
it seems to translate pg text to memo ok. At least for me now, its
translating boolean to char1, but its hard to see that as fatal.
At 3/5/2005 02:56 PM, Peggy Baker wrote:
>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@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
>>
>>
>>
>
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.6.2 - Release Date: 3/4/2005
None of the tables that are causing this error for us contain bigints, memos or booleans. ./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/06/05 6:50 AM >>> If its field types, its not from having access translate pg bigints to decimals, because we tried that. it seems to translate pg text to memo ok. At least for me now, its translating boolean to char1, but its hard to see that as fatal.
Peter Bense wrote: >None of the tables that are causing this error for us contain bigints, >memos or booleans. > >./peter > > > What are you using as the primary key? >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/06/05 6:50 AM >>> >>>> >>>> >If its field types, its not from having access translate pg bigints to >decimals, because we tried that. >it seems to translate pg text to memo ok. At least for me now, its >translating boolean to char1, but its hard to see that as fatal. > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > -- Peggy Baker Database Designs Assoc., Inc. www.dbdes.com Boston: 617.248.0007 Chicago: 312.225.3095
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@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
>
>
>
>