#Deleted strikes again
| От | Peter Bense |
|---|---|
| Тема | #Deleted strikes again |
| Дата | |
| Msg-id | s228bdc4.000@gwm.sc.edu обсуждение исходный текст |
| Ответы |
Re: #Deleted strikes again
(Steve Backman <earlysteve@earthlink.net>)
|
| Список | pgsql-odbc |
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
В списке pgsql-odbc по дате отправления: