Thread: Error handling in ODBC
Hi all, Recently I found the case that NOTICE causes an error in ODBC while examining Kristis' error report. It seems that NOTICE had never been the cause of SQL_ERROR before Zoltan's change about a year ago. Before his change *NOTICE* only caused a PGRES_NONFATAL_ERROR and SC_execute() returned SQL_SUCCESS_WITH_INFO. However currently *ERROR* also causes a PGRES_NONFATAL_ERROR and SC_Execute() returns SQL_ERROR. Why was this changed ? I'd like to put it back if there's no reason. regards, Hiroshi Inoue
> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> Sent: 14 June 2001 07:27
> To: Zoltan Kovacs
> Cc: pgsql-odbc
> Subject: [ODBC] Error handling in ODBC
>
>
> Hi all,
>
> Recently I found the case that NOTICE causes an error in
> ODBC while examining Kristis' error report.
>
> It seems that NOTICE had never been the cause of SQL_ERROR
> before Zoltan's change about a year ago. Before his change
> *NOTICE* only caused a PGRES_NONFATAL_ERROR and SC_execute()
> returned SQL_SUCCESS_WITH_INFO. However currently *ERROR*
> also causes a PGRES_NONFATAL_ERROR and SC_Execute() returns
> SQL_ERROR. Why was this changed ? I'd like to put it back if
> there's no reason.
Morning,
Actually this is a big problem for me (not what you are proposing, but
NOTICES in general). I'm trying to implement EXPLAIN in pgAdmin. Query Plans
are returned by the backend in a notice, which the ODBC driver happily seems
to log (if logging is switched on) and then do nothing with them (from
connection.c):
case 'N':
SOCK_get_string(sock, cmdbuffer, ERROR_MSG_LENGTH);
qlog("NOTICE
from backend during clear: '%s'\n", cmdbuffer);
break;
...
case 'N': /* NOTICE: */
SOCK_get_string(sock, cmdbuffer,
ERROR_MSG_LENGTH);
res = QR_Constructor();
QR_set_status(res, PGRES_NONFATAL_ERROR);
QR_set_notice(res, cmdbuffer); /* will dup
this string */
mylog("~~~ NOTICE: '%s'\n", cmdbuffer);
qlog("NOTICE from backend during send_query:
'%s'\n", cmdbuffer);
continue;
As far as I can tell, it is then not possible to access the notice from a
client app (cetainly SQLError won't return it in my test prog, yet I can
quite happily get a real error from an invalid query for example.
As I said, this is a real issue for me, obviously NOTICEs probably shouldn't
raise an error that a client app might think is an actual problem, on the
other hand it would be *really* useful to be able to access them somehow.
The only way I could think of that's not completely horrendous would be to
add another info type to SQLGetInfo - say PGLastNotice.
Regards, Dave.
Dave Page wrote: > [snip] > > As far as I can tell, it is then not possible to access the notice from a > client app (cetainly SQLError won't return it in my test prog, yet I can > quite happily get a real error from an invalid query for example. > Hmm it may be possible that SQLERROR returns a notice message in an appropriate condition(?????) though I'm not sure. regards, Hiroshi Inoue
> -----Original Message----- > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > Sent: 14 June 2001 11:09 > To: Dave Page > Cc: Zoltan Kovacs; pgsql-odbc > Subject: Re: [ODBC] Error handling in ODBC > > > Dave Page wrote: > > > > [snip] > > > > > As far as I can tell, it is then not possible to access the notice > > from a client app (cetainly SQLError won't return it in my > test prog, > > yet I can quite happily get a real error from an invalid query for > > example. > > > > Hmm it may be possible that SQLERROR returns a notice message > in an appropriate condition(?????) though I'm not sure. Unfortunately I'm not really familiar enough with either C or the guts of the driver to know or be able to figure out in a reasonable amount of time (though I have spent hours staring at the code :-)) what exactly it is/is trying to do. I do know it's not doing what I need it to :-(, and - I suspect - what it should be doing (though these are not necessarily one and the same). Any suggestions from those more experienced in these things would be greatfully received.... Regards, Dave.
Hiroshi Inoue writes: > Recently I found the case that NOTICE causes an error in ODBC > while examining Kristis' error report. > > It seems that NOTICE had never been the cause of SQL_ERROR > before Zoltan's change about a year ago. Before his change *NOTICE* > only caused a PGRES_NONFATAL_ERROR and SC_execute() > returned SQL_SUCCESS_WITH_INFO. However currently *ERROR* > also causes a PGRES_NONFATAL_ERROR and SC_Execute() > returns SQL_ERROR. > Why was this changed ? Zoltan changed the driver because nobody else did it. As I recall, we asked this list to correct the driver to work correctly with 7.0 pre releases with RI, but we received no answers. We tried to correct the problem for ouselves. It worked. But later we were asked to share the patch. Lastly it used to commit the main CVS by sombody else but we asked this list again to werify the modifycatons by sombody knowing something about ODBC and the current driver. > I'd like to put it back if there's no reason. I think it should be corrected. But if I rmember correctly, the patch was for report RI violatoions to the app, and these errors comes as NOTICEs not ERRORs. May I wrong? Is it the case now? We still use the old patched driver. (It says "(non fatal)" error this case in the error message - may the shared patch says it too.) In addition later we encountered the same problem as you... And Zoltan made his second patch, against the backend to do not drop other NOTICE-s :-) And it works fine too... :-)) Is somebody interested in? :-))) Regards, Tibor Laszlo ltibor@mail.tiszanet.hu
Tibor Laszlo wrote: > > Hiroshi Inoue writes: > > > Recently I found the case that NOTICE causes an error in ODBC > > while examining Kristis' error report. > > > > It seems that NOTICE had never been the cause of SQL_ERROR > > before Zoltan's change about a year ago. Before his change *NOTICE* > > only caused a PGRES_NONFATAL_ERROR and SC_execute() > > returned SQL_SUCCESS_WITH_INFO. However currently *ERROR* > > also causes a PGRES_NONFATAL_ERROR and SC_Execute() > > returns SQL_ERROR. > > Why was this changed ? > > Zoltan changed the driver because nobody else did it. As I recall, we asked this > list to correct the driver to work correctly with 7.0 pre releases with RI, but > we received no answers. We tried to correct the problem for ouselves. It worked. > But later we were asked to share the patch. Lastly it used to commit the main > CVS by sombody else but we asked this list again to werify the modifycatons by > sombody knowing something about ODBC and the current driver. > Unfortunately we have no ODBC maintainer. > > I'd like to put it back if there's no reason. > > I think it should be corrected. But if I rmember correctly, the patch was for > report RI violatoions to the app, and these errors comes as NOTICEs not ERRORs. > May I wrong? Is it the case now? > I don't know how it came about. ISTM the old driver didn't take 'E' after 'C' response into account and failed to report RI violation errors by ignoring the 'E'. The 'E' correponds to an ERROR not a NOTICE. regards, Hiroshi Inoue
Dave Page wrote: > > > -----Original Message----- > > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > > Sent: 14 June 2001 11:09 > > To: Dave Page > > Cc: Zoltan Kovacs; pgsql-odbc > > Subject: Re: [ODBC] Error handling in ODBC > > > > > > Dave Page wrote: > > > > > > > [snip] > > > > > > > > As far as I can tell, it is then not possible to access the notice > > > from a client app (cetainly SQLError won't return it in my > > test prog, > > > yet I can quite happily get a real error from an invalid query for > > > example. > > > > > > > Hmm it may be possible that SQLERROR returns a notice message > > in an appropriate condition(?????) though I'm not sure. > > Unfortunately I'm not really familiar enough with either C or the guts of > the driver to know or be able to figure out in a reasonable amount of time > (though I have spent hours staring at the code :-)) what exactly it is/is > trying to do. I do know it's not doing what I need it to :-(, and - I > suspect - what it should be doing (though these are not necessarily one and > the same). > IMHO *ERROR* messages should return SQL_ERROR with the SQLSTATE <> "00000". As for NOTICEs we seem to be able to change the driver to return SQL_SUCCESS_WITH_INFO and let the succeeding SQLError() to return the NOTICE message. However I've not recognized the circumstances why both ERRORs and (some) NOTICEs return SQL_ERROR with the SQLSTATE = '00000' now. Comments ? regards, Hiroshi Inoue
> -----Original Message----- > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > Sent: 18 June 2001 07:06 > To: Dave Page > Cc: pgsql-odbc > Subject: Re: [ODBC] Error handling in ODBC > > > Dave Page wrote: > > > > > -----Original Message----- > > > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > > > Sent: 14 June 2001 11:09 > > > To: Dave Page > > > Cc: Zoltan Kovacs; pgsql-odbc > > > Subject: Re: [ODBC] Error handling in ODBC > > > > > > > > > Dave Page wrote: > > > > > > > > > > [snip] > > > > > > > > > > > As far as I can tell, it is then not possible to access > the notice > > > > from a client app (cetainly SQLError won't return it in my > > > test prog, > > > > yet I can quite happily get a real error from an > invalid query for > > > > example. > > > > > > > > > > Hmm it may be possible that SQLERROR returns a notice > message in an > > > appropriate condition(?????) though I'm not sure. > > > > Unfortunately I'm not really familiar enough with either C > or the guts > > of the driver to know or be able to figure out in a > reasonable amount > > of time (though I have spent hours staring at the code :-)) what > > exactly it is/is trying to do. I do know it's not doing > what I need it > > to :-(, and - I suspect - what it should be doing (though these are > > not necessarily one and the same). > > > > IMHO *ERROR* messages should return SQL_ERROR with the > SQLSTATE <> "00000". As for NOTICEs we seem to be able to > change the driver to return SQL_SUCCESS_WITH_INFO and let the > succeeding SQLError() to return the NOTICE message. Yes. That (in theory) should do exactly what I need, and sounds like it's how it should work anyway (as I understand ODBC anyway). > However > I've not recognized the circumstances why both ERRORs and > (some) NOTICEs return SQL_ERROR with the SQLSTATE = '00000' now. I noticed that in connection.c, CC_send_query loops waiting waiting for a message from the backend. If it is 'N' then the notice is raised as a PGRES_NONFATAL_ERROR, but, if it is a 'C', then another loop is entered processing messages until an 'I' is received. If an 'N' is received in this loop then is basically gets ignored as far as I can tell. Does this help at all or have I missed the point completely? :-) Regards, Dave.
On Mon, 18 Jun 2001, Hiroshi Inoue wrote: > > > Why was this changed ? The truth is that I don't and didn't understand PostgreSQL internals when I modified this part of the source. It worked for me and noone complained. > > > I'd like to put it back if there's no reason. Yes, of course. > > I think it should be corrected. But if I rmember correctly, the patch was for > > report RI violatoions to the app, and these errors comes as NOTICEs not ERRORs. > > May I wrong? Is it the case now? Tibor, the referential integrity messages come as 'E'rrors, not 'N'otices: ERROR: <unnamed> referential integrity violation - key in X still referenced from Y > I don't know how it came about. ISTM the old driver didn't > take 'E' after 'C' response into account and failed to report > RI violation errors by ignoring the 'E'. The 'E' correponds > to an ERROR not a NOTICE. IIRC, the reference integrity error reporting didn't work because the server had some different protocol in sending those letters. In other words, the driver didn't handle this type of message well. You may almost certainly put back the old code but please check this part if it is not corrected yet. Regards, Zoltan
Dave Page <dpage@vale-housing.co.uk> writes:
> I noticed that in connection.c, CC_send_query loops waiting waiting for a
> message from the backend. If it is 'N' then the notice is raised as a
> PGRES_NONFATAL_ERROR, but, if it is a 'C', then another loop is entered
> processing messages until an 'I' is received. If an 'N' is received in this
> loop then is basically gets ignored as far as I can tell.
This all sounds very wrong. Notices should be collected for display
somehow, but they should not affect the state of the query protocol
exchange --- ie, you should keep reading messages until you see a
termination condition ('Z' message, normally) regardless of notices.
Notices are not errors.
And you should definitely be prepared to accept multiple notices in
a query, and notices arriving at any time during a query.
regards, tom lane
Kovacs Zoltan wrote: > > On Mon, 18 Jun 2001, Hiroshi Inoue wrote: > > > > > Why was this changed ? > > The truth is that I don't and didn't understand PostgreSQL internals when > I modified this part of the source. It worked for me and noone complained. > > > > > I'd like to put it back if there's no reason. > > Yes, of course. > OK I would fix it. ISTM your change was right except setting PGRES_NONFATAL_ERROR for *ERROR*s. regards, Hiroshi Inoue
Hi all, I've just committed a fix to cvs. Changed the error handling as follows. 1) ERRORs causes an SQL_ERROR and the SQLSTATE='S1000'. 2) NOTICEs causes an SQL_SUCCESS_WITH_INFO and the succeeding SQLError() returns the NOTICE message. Remarks. 1) RI violation ERRORs are reported properly. 2) The NOTICE is the last NOTICE message and is truncated to 4095 bytes. regards, Hiroshi Inoue
> -----Original Message----- > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > Sent: 19 June 2001 03:22 > To: Kovacs Zoltan; Tibor Laszlo; Dave Page; Kristis Makris; Mister ics > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Error handling in ODBC > > > Hi all, > > I've just committed a fix to cvs. > > Changed the error handling as follows. > 1) ERRORs causes an SQL_ERROR and the SQLSTATE='S1000'. > 2) NOTICEs causes an SQL_SUCCESS_WITH_INFO and the succeeding > SQLError() returns the NOTICE message. > > Remarks. > 1) RI violation ERRORs are reported properly. > 2) The NOTICE is the last NOTICE message and > is truncated to 4095 bytes. > > regards, > Hiroshi Inoue I owe you the drink of your choice Hiroshi - that worked a treat, many thanks. There's been a few updates recently, so if you agree and no-one objects shall we package 07.01.0006? If so, if you can update the version number, I'll do the build and add the various distros to the ftp site. Regards, Dave.
> -----Original Message-----
> From: Dave Page
> Sent: 19 June 2001 09:49
> To: 'Hiroshi Inoue'; Kovacs Zoltan; Tibor Laszlo; Dave Page;
> Kristis Makris; Mister ics
> Cc: pgsql-odbc@postgresql.org
> Subject: RE: [ODBC] Error handling in ODBC
>
>
>
>
> > -----Original Message-----
> > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> > Sent: 19 June 2001 03:22
> > To: Kovacs Zoltan; Tibor Laszlo; Dave Page; Kristis Makris;
> Mister ics
> > Cc: pgsql-odbc@postgresql.org
> > Subject: Re: [ODBC] Error handling in ODBC
> >
> >
> > Hi all,
> >
> > I've just committed a fix to cvs.
> >
> > Changed the error handling as follows.
> > 1) ERRORs causes an SQL_ERROR and the SQLSTATE='S1000'.
> > 2) NOTICEs causes an SQL_SUCCESS_WITH_INFO and the succeeding
> > SQLError() returns the NOTICE message.
> >
> > Remarks.
> > 1) RI violation ERRORs are reported properly.
> > 2) The NOTICE is the last NOTICE message and
> > is truncated to 4095 bytes.
> >
> > regards,
> > Hiroshi Inoue
>
> I owe you the drink of your choice Hiroshi - that worked a
> treat, many thanks. There's been a few updates recently, so
> if you agree and no-one objects shall we package 07.01.0006?
> If so, if you can update the version number, I'll do the
> build and add the various distros to the ftp site.
Perhaps I spoke too soon - a minor problem:
Executing a more complex query gives the following notice in the commlog:
NOTICE from backend during send_query: 'NOTICE: QUERY PLAN:
Hash Join (cost=844.62..860.22 rows=8 width=148)
-> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36)
-> Hash (cost=844.60..844.60 rows=8 width=112)
-> Nested Loop (cost=0.00..844.60 rows=8 width=112)
-> Seq Scan on pg_class c (cost=0.00..833.30 rows=1
width=58)
SubPlan
-> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1
width=32)
-> Index Scan using pg_attribute_relid_attnum_index on
pg_attribute a (cost=0.00..11.16 rows=11 width=54)
'
However, the driver is returning the following to my test prog:
NOTICE: QUERY PLAN:
Hash Join (cost=844.62..860.22 rows=8 width=148)
-> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36)
-> Hash (cost=844.60..844.60 rows=8 width=112)
-> Nested Loop (cost=0.00..844.60 rows=8 width=112)
-> Seq Scan on pg_class c (cost=0.00..833.30 rows=1
width=58)
SubPlan
-> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1
width=32)
-> Index Scan using pg_attribute_relid_attnum_index on p
00000 ?????????? ?? ? ? ? ? ?
64 Query Plan
I couldn't see any obvious cause for this in the code, but as I've said
before, I'm not that familiar with it. I've included my test code below BTW
incase I'm doing something stupid.
On a slightly different note, I also noticed in connection.c that there is a
CC_send_function function that I didn't notice before. This has the same
sort of message processing loop in it as CC_send_query, but doesn't look
like it will process notices correctly either.
Regards, Dave.
Test Code (VB)
--------------
Private Sub Command1_Click()
Dim iStatus As Integer
Dim iSize As Integer
Dim iRet As Integer
Dim lEnv As Long
Dim lDBC As Long
Dim lStmt As Long
Dim sConnect As String
Dim sResult As String * 256
Dim lRet As Long
Dim lErrNo As Long
Dim iLen As Integer
Dim sSQLState As String * 1024
Dim sErrorMsg As String * 1024
Dim sMsg As String
Dim sSQL As String
'Initialisze the ODBC subsystem
If SQLAllocEnv(lEnv) <> 0 Then
MsgBox "Unable to initialize ODBC API drivers!"
Exit Sub
End If
If SQLAllocConnect(lEnv, lDBC) <> 0 Then
MsgBox "Could not allocate memory for connection Handle!"
'Free the Environment
iStatus = SQLFreeEnv(lEnv)
If iStatus = SQL_ERROR Then
MsgBox "Error Freeing Environment From ODBC Drivers"
End If
Exit Sub
End If
sConnect = "DSN=Helpdesk-Local;UID=postgres;PWD="
iRet = SQLDriverConnect(lDBC, Me.hWnd, sConnect, Len(sConnect), sResult,
Len(sResult), iSize, 1)
If iRet <> SQL_SUCCESS Then
MsgBox "Could not establish connection to ODBC driver!" & vbCrLf &
"Error: " & iRet
Exit Sub
End If
SQLGetInfoString lDBC, SQL_DBMS_VER, sResult, Len(sResult), vbNull
MsgBox sResult
If SQLAllocStmt(lDBC, lStmt) <> 0 Then
MsgBox "Could not allocate memory for a statement handle!"
Exit Sub
End If
sSQL = "EXPLAIN SELECT " & _
" c.oid AS table_oid, c.relname AS table_name,
pg_get_userbyid(c.relowner) AS table_owner, c.relacl AS table_acl, " & _
" CASE WHEN c.relhasindex = TRUE THEN 'Yes'::text ELSE 'No'::text END AS
table_has_indexes, CASE WHEN c.relhasrules = TRUE THEN 'Yes'::text ELSE
'No'::text END AS table_has_rules, CASE WHEN c.relisshared = TRUE THEN
'Yes'::text ELSE 'No'::text END AS table_is_shared, CASE WHEN c.relhaspkey
= TRUE THEN 'Yes'::text ELSE 'No'::text END AS table_has_primarykey, CASE
WHEN c.reltriggers > 0 THEN 'Yes'::text ELSE 'No'::text END AS
table_has_triggers, " & _
" table_rows(pgadmin_get_rows(c.oid)) AS table_rows,
pgadmin_get_desc(c.oid) AS table_comments, a.oid AS column_oid, a.attname AS
column_name, a.attnum AS column_position, t.typname As column_type, " & _
" CASE WHEN ((a.attlen = -1) AND ((a.atttypmod)::int4 = (-1)::int4)) THEN
(0)::int4 ELSE CASE WHEN a.attlen = -1 THEN " & _
" CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char') OR (t.typname
= 'varchar')) THEN (a.atttypmod -4)::int4 ELSE (a.atttypmod)::int4 END " & _
" ELSE (a.attlen)::int4 END END AS column_length, " & _
" CASE WHEN a.attnotnull = TRUE THEN 'Yes'::text ELSE 'No'::text END AS
column_not_null, CASE WHEN a.atthasdef = TRUE THEN 'Yes'::text ELSE
'No'::text END AS column_has_default, " & _
" CASE WHEN (pgadmin_get_col_def(c.oid, a.attnum) NOTNULL) THEN
pgadmin_get_col_def(c.oid, a.attnum) ELSE '' END AS column_default,
pgadmin_get_desc(a.oid) AS column_comments " & _
"FROM pg_attribute a, pg_type t, pg_class c " & _
"WHERE a.atttypid = t.oid AND a.attrelid = c.oid AND (((c.relkind::char =
'r'::char) OR (c.relkind::char = 's'::char)) AND (NOT (EXISTS (SELECT
pg_rewrite.rulename FROM pg_rewrite WHERE ((pg_rewrite.ev_class = c.oid) AND
(pg_rewrite.ev_type::char = '1'::char))))))"
If SQLExecDirect(lStmt, sSQL, Len(sSQL)) = SQL_SUCCESS_WITH_INFO Then
lRet = SQLError(lEnv, lDBC, lStmt, sSQLState, lErrNo, sErrorMsg, 1024,
iLen)
Debug.Print Left$(sErrorMsg, iLen), vbInformation, "Query Plan"
End If
If lDBC <> 0 Then
iStatus = SQLDisconnect(lDBC)
If iStatus = SQL_ERROR Then
MsgBox "Error logging out of data source!"
End If
End If
'Release memory associated with the connections:
iStatus = SQLFreeConnect(lDBC)
If iStatus = SQL_ERROR Then
MsgBox "Error unloading ODBC drivers!"
End If
'Release memory for the ODBC environment
If lEnv <> 0 Then
iStatus = SQLFreeEnv(lEnv)
If iStatus = SQL_ERROR Then
MsgBox "Error Freeing Environment From ODBC Drivers"
End If
End If
End Sub
Hi All,
I tried to post this yesterday, but I think there was a problem
somewhere along the line.
I have a problem which I am hoping you will be able to help me with. I
have Redhat 7.1 with Postgres 7.0. I am accessing this from my windoze
machine running PowerBuilder 7, using the 0005 ODBC driver. This has
been working wonderfully well, but the other day we loaded more data in
preparation of using the system in a production environment, when all of
a sudden (with no changes in code) we started getting an error. The
message returned to PowerBuilder was simply Select Error:.
What I have done is generated a trace of the ODBC connection, I won't
bore you with it all. But here is the relevant error message:
Error 2 (rc -1) : SQLSTATE = 01004
The buffer was too small for the result.; Unknown connect option (Set)
1st question: Does anyone know what the problem is? Or what this error
message means?
2nd question: Where can I get a list of the sqlstate errors that are
returned from PostgreSQL? I have looked around the doco's but I
couldn't find anything. (thats not to say that it's not there, but just
that I didn't find it)
Many thanks
Mike Zbierski
Hi Hiroshi, thank you for the fix. regards, Silvio > >Hi all, > >I've just committed a fix to cvs. > >Changed the error handling as follows. >1) ERRORs causes an SQL_ERROR and the SQLSTATE='S1000'. >2) NOTICEs causes an SQL_SUCCESS_WITH_INFO and the succeeding > SQLError() returns the NOTICE message. > >Remarks. >1) RI violation ERRORs are reported properly. >2) The NOTICE is the last NOTICE message and > is truncated to 4095 bytes. > >regards, >Hiroshi Inoue _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Dave Page wrote: > > > Perhaps I spoke too soon - a minor problem: > > Executing a more complex query gives the following notice in the commlog: > > NOTICE from backend during send_query: 'NOTICE: QUERY PLAN: > > Hash Join (cost=844.62..860.22 rows=8 width=148) > -> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36) > -> Hash (cost=844.60..844.60 rows=8 width=112) > -> Nested Loop (cost=0.00..844.60 rows=8 width=112) > -> Seq Scan on pg_class c (cost=0.00..833.30 rows=1 > width=58) > SubPlan > -> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1 > width=32) > -> Index Scan using pg_attribute_relid_attnum_index on > pg_attribute a (cost=0.00..11.16 rows=11 width=54) > > ' > > However, the driver is returning the following to my test prog: > > NOTICE: QUERY PLAN: > > Hash Join (cost=844.62..860.22 rows=8 width=148) > -> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36) > -> Hash (cost=844.60..844.60 rows=8 width=112) > -> Nested Loop (cost=0.00..844.60 rows=8 width=112) > -> Seq Scan on pg_class c (cost=0.00..833.30 rows=1 > width=58) > SubPlan > -> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1 > width=32) > -> Index Scan using pg_attribute_relid_attnum_index on p > 00000 ?????????? ?? ? ? ? ? ? > 64 Query Plan > > I couldn't see any obvious cause for this in the code, but as I've said > before, I'm not that familiar with it. I've included my test code below BTW > incase I'm doing something stupid. > The length of the NOTICE message may exceed the buffer size prepared by the caller of SQLError() though I'm not sure. I'm not sure about the spec of SQLError() but it seems hard to return a large error message at once. I would change the driver to return a large error message by multiple SQLError calls. You seem to have to call SQLError() until it returns SQL_NO_DATA. > On a slightly different note, I also noticed in connection.c that there is a > CC_send_function function that I didn't notice before. This has the same > sort of message processing loop in it as CC_send_query, but doesn't look > like it will process notices correctly either. > OK I would take care of this also. regards, Hiroshi Inoue
I wrote: > > Dave Page wrote: > > > > > > Perhaps I spoke too soon - a minor problem: > > > > Executing a more complex query gives the following notice in the commlog: > > > > NOTICE from backend during send_query: 'NOTICE: QUERY PLAN: > > > > Hash Join (cost=844.62..860.22 rows=8 width=148) > > -> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36) > > -> Hash (cost=844.60..844.60 rows=8 width=112) > > -> Nested Loop (cost=0.00..844.60 rows=8 width=112) > > -> Seq Scan on pg_class c (cost=0.00..833.30 rows=1 > > width=58) > > SubPlan > > -> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1 > > width=32) > > -> Index Scan using pg_attribute_relid_attnum_index on > > pg_attribute a (cost=0.00..11.16 rows=11 width=54) > > > > ' > > > > However, the driver is returning the following to my test prog: > > > > NOTICE: QUERY PLAN: > > > > Hash Join (cost=844.62..860.22 rows=8 width=148) > > -> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36) > > -> Hash (cost=844.60..844.60 rows=8 width=112) > > -> Nested Loop (cost=0.00..844.60 rows=8 width=112) > > -> Seq Scan on pg_class c (cost=0.00..833.30 rows=1 > > width=58) > > SubPlan > > -> Seq Scan on pg_rewrite (cost=0.00..3.28 rows=1 > > width=32) > > -> Index Scan using pg_attribute_relid_attnum_index on p > > 00000 ?????????? ?? ? ? ? ? ? > > 64 Query Plan > > > > I couldn't see any obvious cause for this in the code, but as I've said > > before, I'm not that familiar with it. I've included my test code below BTW > > incase I'm doing something stupid. > > > > The length of the NOTICE message may exceed the buffer size > prepared by the caller of SQLError() though I'm not sure. The driver manager seems to set the buffer size to 512 even though we set the size bigger. The following seems to be needed. > I'm not sure about the spec of SQLError() but it seems hard > to return a large error message at once. I would change > the driver to return a large error message by multiple SQLError > calls. You seem to have to call SQLError() until it returns > SQL_NO_DATA. > > > On a slightly different note, I also noticed in connection.c that there is a > > CC_send_function function that I didn't notice before. This has the same > > sort of message processing loop in it as CC_send_query, but doesn't look > > like it will process notices correctly either. > > > > OK I would take care of this also. > Hmm CC_send_function seems to be only for large objects. I would postpone this, sorry. regards, Hiroshi Inoue
> -----Original Message----- > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > Sent: 20 June 2001 08:51 > To: Dave Page; 'pgsql-odbc@postgresql.org' > Subject: Re: [ODBC] Error handling in ODBC > > > I wrote: > > > > Dave Page wrote: > > > > > > > > > Perhaps I spoke too soon - a minor problem: > > > > > > Executing a more complex query gives the following notice in the > > > commlog: > > > > > > NOTICE from backend during send_query: 'NOTICE: QUERY PLAN: > > > > > > Hash Join (cost=844.62..860.22 rows=8 width=148) > > > -> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36) > > > -> Hash (cost=844.60..844.60 rows=8 width=112) > > > -> Nested Loop (cost=0.00..844.60 rows=8 width=112) > > > -> Seq Scan on pg_class c > (cost=0.00..833.30 rows=1 > > > width=58) > > > SubPlan > > > -> Seq Scan on pg_rewrite > (cost=0.00..3.28 > > > rows=1 > > > width=32) > > > -> Index Scan using > pg_attribute_relid_attnum_index on > > > pg_attribute a (cost=0.00..11.16 rows=11 width=54) > > > > > > ' > > > > > > However, the driver is returning the following to my test prog: > > > > > > NOTICE: QUERY PLAN: > > > > > > Hash Join (cost=844.62..860.22 rows=8 width=148) > > > -> Seq Scan on pg_type t (cost=0.00..6.30 rows=230 width=36) > > > -> Hash (cost=844.60..844.60 rows=8 width=112) > > > -> Nested Loop (cost=0.00..844.60 rows=8 width=112) > > > -> Seq Scan on pg_class c > (cost=0.00..833.30 rows=1 > > > width=58) > > > SubPlan > > > -> Seq Scan on pg_rewrite > (cost=0.00..3.28 > > > rows=1 > > > width=32) > > > -> Index Scan using > pg_attribute_relid_attnum_index on p > > > 00000 ?????????? ?? ? ? ? ? ? > > > 64 Query Plan > > > > > > I couldn't see any obvious cause for this in the code, > but as I've > > > said before, I'm not that familiar with it. I've included my test > > > code below BTW incase I'm doing something stupid. > > > > > > > The length of the NOTICE message may exceed the buffer size > prepared > > by the caller of SQLError() though I'm not sure. > > The driver manager seems to set the buffer size to 512 even > though we set the size bigger. The following seems to be needed. Ok, I can code around that in my apps. > > I'm not sure about the spec of SQLError() but it seems hard > to return > > a large error message at once. I would change the driver to > return a > > large error message by multiple SQLError calls. You seem to have to > > call SQLError() until it returns SQL_NO_DATA. > > > > > On a slightly different note, I also noticed in connection.c that > > > there is a CC_send_function function that I didn't notice before. > > > This has the same sort of message processing loop in it as > > > CC_send_query, but doesn't look like it will process notices > > > correctly either. > > > > > > > OK I would take care of this also. > > > > Hmm CC_send_function seems to be only for large objects. > I would postpone this, sorry. Not a problem with me, I don't use large objects. I only pointed it out in case it was a problem that might affect others. Let me know when you think we're ready to package the 07.01.0006.... regards, Dave.