Обсуждение: Re: bug in info.c file - incorrect SQL
For the ODBC driver psqlodbc.dll version 7.02.0003 there is an error in
the info.c. In 2 places the SQL contains an alias in the FROM clause
for the pg_namespace table and then references the table in the where
clause. The below cut from the info.c file should replace all instances
on pg_namespace. in the where clause with pn.
This should be done in both places.
I modified this code, but was unable to compile a new version of
the odbc driver.
When you re-compile, could you reply email to me please.
Thanks
> sprintf(tables_query, "SELECT pt.tgargs, "
> " pt.tgnargs, "
> " pt.tgdeferrable, "
> " pt.tginitdeferred, "
> " pp1.proname, "
> " pp2.proname, "
> " pc.oid, "
> " pc1.oid, "
> " pc1.relname, "
> " pn.nspname "
> "FROM pg_class pc, "
> " pg_proc pp1, "
> " pg_proc pp2, "
> " pg_trigger pt1, "
> " pg_trigger pt2, "
> " pg_proc pp, "
> " pg_trigger pt, "
> " pg_class pc1, "
> " pg_namespace pn "
> "WHERE pt.tgrelid = pc.oid "
> "AND pp.oid = pt.tgfoid "
> "AND pt1.tgconstrrelid = pc.oid "
> "AND pp1.oid = pt1.tgfoid "
> "AND pt2.tgfoid = pp2.oid "
> "AND pt2.tgconstrrelid = pc.oid "
> "AND ((pc.relname='%s') "
> "AND (pg_namespace.oid = pc.relnamespace) "
> "AND (pg_namespace.nspname = '%s') "
> "AND (pp.proname LIKE '%%ins') "
> "AND (pp1.proname LIKE '%%upd') "
> "AND (pp2.proname LIKE '%%del') "
> "AND (pt1.tgrelid=pt.tgconstrrelid) "
> "AND (pt1.tgconstrname=pt.tgconstrname) "
> "AND (pt2.tgrelid=pt.tgconstrrelid) "
> "AND (pt2.tgconstrname=pt.tgconstrname) "
> "AND (pt.tgconstrrelid=pc1.oid) "
> "AND (pc1.relnamespace=pn.oid))",
> fk_table_needed, schema_needed);
> }
Вложения
Good catch, patch attached. I found the problems you saw, plus a few cases where pg_namespace was referenced in the WHERE but not FROM --- not a big problem, but clearer if we specify it both places. Patch applied. --------------------------------------------------------------------------- Jim Holliday wrote: > For the ODBC driver psqlodbc.dll version 7.02.0003 there is an error in > the info.c. In 2 places the SQL contains an alias in the FROM clause > for the pg_namespace table and then references the table in the where > clause. The below cut from the info.c file should replace all instances > on pg_namespace. in the where clause with pn. > > This should be done in both places. > I modified this code, but was unable to compile a new version of > the odbc driver. > > When you re-compile, could you reply email to me please. > Thanks > > > sprintf(tables_query, "SELECT pt.tgargs, " > > " pt.tgnargs, " > > " pt.tgdeferrable, " > > " pt.tginitdeferred, " > > " pp1.proname, " > > " pp2.proname, " > > " pc.oid, " > > " pc1.oid, " > > " pc1.relname, " > > " pn.nspname " > > "FROM pg_class pc, " > > " pg_proc pp1, " > > " pg_proc pp2, " > > " pg_trigger pt1, " > > " pg_trigger pt2, " > > " pg_proc pp, " > > " pg_trigger pt, " > > " pg_class pc1, " > > " pg_namespace pn " > > "WHERE pt.tgrelid = pc.oid " > > "AND pp.oid = pt.tgfoid " > > "AND pt1.tgconstrrelid = pc.oid " > > "AND pp1.oid = pt1.tgfoid " > > "AND pt2.tgfoid = pp2.oid " > > "AND pt2.tgconstrrelid = pc.oid " > > "AND ((pc.relname='%s') " > > "AND (pg_namespace.oid = pc.relnamespace) " > > "AND (pg_namespace.nspname = '%s') " > > "AND (pp.proname LIKE '%%ins') " > > "AND (pp1.proname LIKE '%%upd') " > > "AND (pp2.proname LIKE '%%del') " > > "AND (pt1.tgrelid=pt.tgconstrrelid) " > > "AND (pt1.tgconstrname=pt.tgconstrname) " > > "AND (pt2.tgrelid=pt.tgconstrrelid) " > > "AND (pt2.tgconstrname=pt.tgconstrname) " > > "AND (pt.tgconstrrelid=pc1.oid) " > > "AND (pc1.relnamespace=pn.oid))", > > fk_table_needed, schema_needed); > > } > > > [ Type application/ms-tnef treated as attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: info.c =================================================================== RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v retrieving revision 1.90 diff -c -c -r1.90 info.c *** info.c 2 Jan 2003 15:33:19 -0000 1.90 --- info.c 2 Jan 2003 22:22:43 -0000 *************** *** 1668,1674 **** " from pg_namespace u, pg_class c, pg_attribute a, pg_type t" " where u.oid = c.relnamespace" " and (not a.attisdropped)" ! " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)", "a.atttypmod"); else sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid" --- 1668,1674 ---- " from pg_namespace u, pg_class c, pg_attribute a, pg_type t" " where u.oid = c.relnamespace" " and (not a.attisdropped)" ! " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)", "a.atttypmod"); else sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid" *************** *** 3087,3093 **** if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select OID from pg_class where relname = '%s' and pg_namespace.oid = relnamespace and pg_namespace.nspname= '%s'", serverTableName, serverSchemaName); else sprintf(query, "select OID from pg_class where relname = '%s'", serverTableName); if (res = CC_send_query(conn, query, NULL, CLEAR_RESULT_ON_ABORT), res) --- 3087,3093 ---- if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select OID from pg_class, pg_namespace where relname = '%s' and pg_namespace.oid = relnamespaceand pg_namespace.nspname = '%s'", serverTableName, serverSchemaName); else sprintf(query, "select OID from pg_class where relname = '%s'", serverTableName); if (res = CC_send_query(conn, query, NULL, CLEAR_RESULT_ON_ABORT), res) *************** *** 3154,3160 **** if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select attrelid, attnum from pg_class, pg_attribute " "where relname = '%s' and attrelid = pg_class.oid " "and (not attisdropped) " "and attname = '%s' and pg_namespace.oid = relnamespace and pg_namespace.nspname = '%s'", serverTableName,serverColumnName, serverSchemaName); --- 3154,3160 ---- if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select attrelid, attnum from pg_class, pg_attribute, pg_namespace " "where relname = '%s' and attrelid = pg_class.oid " "and (not attisdropped) " "and attname = '%s' and pg_namespace.oid = relnamespace and pg_namespace.nspname = '%s'", serverTableName,serverColumnName, serverSchemaName); *************** *** 3449,3456 **** "AND pt2.tgfoid = pp2.oid " "AND pt2.tgconstrrelid = pc.oid " "AND ((pc.relname='%s') " ! "AND (pg_namespace.oid = pc.relnamespace) " ! "AND (pg_namespace.nspname = '%s') " "AND (pp.proname LIKE '%%ins') " "AND (pp1.proname LIKE '%%upd') " "AND (pp2.proname LIKE '%%del') " --- 3449,3456 ---- "AND pt2.tgfoid = pp2.oid " "AND pt2.tgconstrrelid = pc.oid " "AND ((pc.relname='%s') " ! "AND (pn.oid = pc.relnamespace) " ! "AND (pn.nspname = '%s') " "AND (pp.proname LIKE '%%ins') " "AND (pp1.proname LIKE '%%upd') " "AND (pp2.proname LIKE '%%del') " *************** *** 3826,3833 **** " AND pc2.oid = pt.tgrelid " " AND (" " (pc.relname='%s') " ! " AND (pg_namespace.oid = pc.relnamespace) " ! " AND (pg_namespace.nspname = '%s') " " AND (pp.proname Like '%%upd') " " AND (pp1.proname Like '%%del')" " AND (pt1.tgrelid = pt.tgconstrrelid) " --- 3826,3833 ---- " AND pc2.oid = pt.tgrelid " " AND (" " (pc.relname='%s') " ! " AND (pn.oid = pc.relnamespace) " ! " AND (pn.nspname = '%s') " " AND (pp.proname Like '%%upd') " " AND (pp1.proname Like '%%del')" " AND (pt1.tgrelid = pt.tgconstrrelid) "
> -----Original Message----- > From: Bruce Momjian > > Good catch, patch attached. I found the problems you saw, plus a few > cases where pg_namespace was referenced in the WHERE but not FROM --- > not a big problem, but clearer if we specify it both places. Patch > applied. Unfortunately it doesn't seem a right fix. I would fix it later. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > -----Original Message----- > > From: Bruce Momjian > > > > Good catch, patch attached. I found the problems you saw, plus a few > > cases where pg_namespace was referenced in the WHERE but not FROM --- > > not a big problem, but clearer if we specify it both places. Patch > > applied. > > Unfortunately it doesn't seem a right fix. > I would fix it later. Thanks, Hiroshi. Also, seems someone is having trouble using the info functions in the current ODBC release, probably because of this bug. Can you roll a new release once you fix it? Thanks. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-----Original Message----- From: Jim Holliday > For the ODBC driver psqlodbc.dll version 7.02.0003 there is an error in the info.c. > In 2 places the SQL contains an alias in the FROM clause for the pg_namespace > table and then references the table in the where clause. The below cut from the > info.c file should replace all instances on pg_namespace. in the where clause with > pn. This should be done in both places. Does the code give you a wrong result in the first place ? regards, Hiroshi Inoue
Вложения
Hiroshi Inoue wrote: > -----Original Message----- > From: Jim Holliday > > > For the ODBC driver psqlodbc.dll version 7.02.0003 there is an error in > the info.c. > > In 2 places the SQL contains an alias in the FROM clause for the > pg_namespace > > table and then references the table in the where clause. The below cut > from the > > info.c file should replace all instances on pg_namespace. in the where > clause with > > pn. This should be done in both places. > > Does the code give you a wrong result in the first place ? I see what Hiroshi is saying now --- there was a reference to pg_namespace (in WHERE but not in FROM) _and_ a reference to 'pn' as an alias for pg_namespace (in WHERE and FROM). The attached patch fixes this by creating a pn1 and pn2 alias for pg_namespace in FROM. It also improves some of the alias tags. Applied to ODBC CVS. Can someone run some tests on these? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: info.c =================================================================== RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v retrieving revision 1.91 diff -c -c -r1.91 info.c *** info.c 2 Jan 2003 22:30:13 -0000 1.91 --- info.c 3 Jan 2003 21:13:12 -0000 *************** *** 3423,3500 **** if (conn->schema_support) { schema_strcat(schema_needed, "%.*s", szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName, conn); ! sprintf(tables_query, "SELECT pt.tgargs, " ! " pt.tgnargs, " ! " pt.tgdeferrable, " ! " pt.tginitdeferred, " ! " pp1.proname, " " pp2.proname, " ! " pc.oid, " " pc1.oid, " ! " pc1.relname, " ! " pn.nspname " ! "FROM pg_class pc, " " pg_proc pp1, " " pg_proc pp2, " " pg_trigger pt1, " " pg_trigger pt2, " ! " pg_proc pp, " ! " pg_trigger pt, " ! " pg_class pc1, " ! " pg_namespace pn " ! "WHERE pt.tgrelid = pc.oid " ! "AND pp.oid = pt.tgfoid " ! "AND pt1.tgconstrrelid = pc.oid " "AND pp1.oid = pt1.tgfoid " ! "AND pt2.tgfoid = pp2.oid " ! "AND pt2.tgconstrrelid = pc.oid " ! "AND ((pc.relname='%s') " ! "AND (pn.oid = pc.relnamespace) " ! "AND (pn.nspname = '%s') " ! "AND (pp.proname LIKE '%%ins') " ! "AND (pp1.proname LIKE '%%upd') " ! "AND (pp2.proname LIKE '%%del') " ! "AND (pt1.tgrelid=pt.tgconstrrelid) " ! "AND (pt1.tgconstrname=pt.tgconstrname) " ! "AND (pt2.tgrelid=pt.tgconstrrelid) " ! "AND (pt2.tgconstrname=pt.tgconstrname) " ! "AND (pt.tgconstrrelid=pc1.oid) " ! "AND (pc1.relnamespace=pn.oid))", fk_table_needed, schema_needed); } else ! sprintf(tables_query, "SELECT pt.tgargs, " ! " pt.tgnargs, " ! " pt.tgdeferrable, " ! " pt.tginitdeferred, " ! " pp1.proname, " " pp2.proname, " ! " pc.oid, " " pc1.oid, " ! " pc1.relname " ! "FROM pg_class pc, " " pg_proc pp1, " " pg_proc pp2, " ! " pg_trigger pt1, " " pg_trigger pt2, " ! " pg_proc pp, " ! " pg_trigger pt, " ! " pg_class pc1 " ! "WHERE pt.tgrelid = pc.oid " ! "AND pp.oid = pt.tgfoid " ! "AND pt1.tgconstrrelid = pc.oid " "AND pp1.oid = pt1.tgfoid " ! "AND pt2.tgfoid = pp2.oid " ! "AND pt2.tgconstrrelid = pc.oid " ! "AND ((pc.relname='%s') " ! "AND (pp.proname LIKE '%%ins') " ! "AND (pp1.proname LIKE '%%upd') " ! "AND (pp2.proname LIKE '%%del') " ! "AND (pt1.tgrelid=pt.tgconstrrelid) " ! "AND (pt1.tgconstrname=pt.tgconstrname) " ! "AND (pt2.tgrelid=pt.tgconstrrelid) " ! "AND (pt2.tgconstrname=pt.tgconstrname) " ! "AND (pt.tgconstrrelid=pc1.oid)) ", fk_table_needed); result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query)); --- 3423,3503 ---- if (conn->schema_support) { schema_strcat(schema_needed, "%.*s", szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName, conn); ! sprintf(tables_query, ! "SELECT pt.tgargs, " ! " pt1.tgnargs, " ! " pt1.tgdeferrable, " ! " pt1.tginitdeferred, " " pp2.proname, " ! " pp3.proname, " " pc1.oid, " ! " pc2.oid, " ! " pc2.relname, " ! " pn2.nspname " ! "FROM pg_class pc1, " ! " pg_class pc2, " " pg_proc pp1, " " pg_proc pp2, " + " pg_proc pp3, " " pg_trigger pt1, " " pg_trigger pt2, " ! " pg_trigger pt3, " ! " pg_namespace pn1, " ! " pg_namespace pn2 " ! "WHERE pt1.tgrelid = pc1.oid " "AND pp1.oid = pt1.tgfoid " ! "AND pt2.tgconstrrelid = pc1.oid " ! "AND pp2.oid = pt2.tgfoid " ! "AND pt3.tgfoid = pp3.oid " ! "AND pt3.tgconstrrelid = pc1.oid " ! "AND pc1.relname='%s' " ! "AND pn1.oid = pc1.relnamespace " ! "AND pn1.nspname = '%s' " ! "AND pp1.proname LIKE '%%ins' " ! "AND pp2.proname LIKE '%%upd' " ! "AND pp3.proname LIKE '%%del' " ! "AND pt2.tgrelid=pt1.tgconstrrelid " ! "AND pt2.tgconstrname=pt1.tgconstrname " ! "AND pt3.tgrelid=pt1.tgconstrrelid " ! "AND pt3.tgconstrname=pt1.tgconstrname " ! "AND pt1.tgconstrrelid=pc2.oid " ! "AND pc2.relnamespace=pn2.oid ", fk_table_needed, schema_needed); } else ! sprintf(tables_query, ! "SELECT pt1.tgargs, " ! " pt1.tgnargs, " ! " pt1.tgdeferrable, " ! " pt1.tginitdeferred, " " pp2.proname, " ! " pp3.proname, " " pc1.oid, " ! " pc2.oid, " ! " pc2.relname " ! "FROM pg_class pc1, " ! " pg_class pc2, " " pg_proc pp1, " " pg_proc pp2, " ! " pg_proc pp3, " ! " pg_trigger pt1 " " pg_trigger pt2, " ! " pg_trigger pt3 " ! "WHERE pt1.tgrelid = pc1.oid " "AND pp1.oid = pt1.tgfoid " ! "AND pt2.tgconstrrelid = pc1.oid " ! "AND pp2.oid = pt2.tgfoid " ! "AND pt3.tgfoid = pp3.oid " ! "AND pt3.tgconstrrelid = pc1.oid " ! "AND pc1.relname='%s' " ! "AND pp1.proname LIKE '%%ins' " ! "AND pp2.proname LIKE '%%upd' " ! "AND pp3.proname LIKE '%%del' " ! "AND pt2.tgrelid=pt1.tgconstrrelid " ! "AND pt2.tgconstrname=pt1.tgconstrname " ! "AND pt3.tgrelid=pt1.tgconstrrelid " ! "AND pt3.tgconstrname=pt1.tgconstrname " ! "AND pt1.tgconstrrelid=pc2.oid ", fk_table_needed); result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query)); *************** *** 3798,3873 **** if (conn->schema_support) { schema_strcat(schema_needed, "%.*s", szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, conn); ! sprintf(tables_query, "SELECT pt.tgargs, " ! " pt.tgnargs, " ! " pt.tgdeferrable, " ! " pt.tginitdeferred, " ! " pp.proname, " " pp1.proname, " ! " pc.oid, " " pc1.oid, " ! " pc1.relname, " ! " pn.nspname " ! "FROM pg_class pc, " ! " pg_class pc1, " " pg_class pc2, " ! " pg_proc pp, " " pg_proc pp1, " ! " pg_trigger pt, " " pg_trigger pt1, " " pg_trigger pt2, " ! " pg_namespace pn " ! "WHERE pt.tgconstrrelid = pc.oid " ! " AND pt.tgrelid = pc1.oid " ! " AND pt1.tgfoid = pp1.oid " ! " AND pt1.tgconstrrelid = pc1.oid " " AND pt2.tgconstrrelid = pc2.oid " ! " AND pt2.tgfoid = pp.oid " ! " AND pc2.oid = pt.tgrelid " ! " AND (" ! " (pc.relname='%s') " ! " AND (pn.oid = pc.relnamespace) " ! " AND (pn.nspname = '%s') " ! " AND (pp.proname Like '%%upd') " ! " AND (pp1.proname Like '%%del')" ! " AND (pt1.tgrelid = pt.tgconstrrelid) " ! " AND (pt2.tgrelid = pt.tgconstrrelid) " ! " AND (pn.oid = pc1.relnamespace) " ! " )", pk_table_needed, schema_needed); } else ! sprintf(tables_query, "SELECT pt.tgargs, " ! " pt.tgnargs, " ! " pt.tgdeferrable, " ! " pt.tginitdeferred, " ! " pp.proname, " " pp1.proname, " ! " pc.oid, " " pc1.oid, " ! " pc1.relname " ! "FROM pg_class pc, " ! " pg_class pc1, " " pg_class pc2, " ! " pg_proc pp, " " pg_proc pp1, " ! " pg_trigger pt, " " pg_trigger pt1, " ! " pg_trigger pt2 " ! "WHERE pt.tgconstrrelid = pc.oid " ! " AND pt.tgrelid = pc1.oid " ! " AND pt1.tgfoid = pp1.oid " ! " AND pt1.tgconstrrelid = pc1.oid " " AND pt2.tgconstrrelid = pc2.oid " ! " AND pt2.tgfoid = pp.oid " ! " AND pc2.oid = pt.tgrelid " ! " AND (" ! " (pc.relname='%s') " ! " AND (pp.proname Like '%%upd') " ! " AND (pp1.proname Like '%%del')" ! " AND (pt1.tgrelid = pt.tgconstrrelid) " ! " AND (pt2.tgrelid = pt.tgconstrrelid) " ! " )", pk_table_needed); result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query)); --- 3801,3875 ---- if (conn->schema_support) { schema_strcat(schema_needed, "%.*s", szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, conn); ! sprintf(tables_query, ! "SELECT pt1.tgargs, " ! " pt1.tgnargs, " ! " pt1.tgdeferrable, " ! " pt1.tginitdeferred, " " pp1.proname, " ! " pp2.proname, " " pc1.oid, " ! " pc2.oid, " ! " pc2.relname, " ! " pn2.nspname " ! "FROM pg_class pc1, " " pg_class pc2, " ! " pg_class pc3, " " pg_proc pp1, " ! " pg_proc pp2, " " pg_trigger pt1, " " pg_trigger pt2, " ! " pg_trigger pt3, " ! " pg_namespace pn1, " ! " pg_namespace pn2 " ! "WHERE pt1.tgconstrrelid = pc1.oid " ! " AND pt1.tgrelid = pc2.oid " ! " AND pt2.tgfoid = pp2.oid " " AND pt2.tgconstrrelid = pc2.oid " ! " AND pt3.tgconstrrelid = pc3.oid " ! " AND pt3.tgfoid = pp1.oid " ! " AND pc3.oid = pt1.tgrelid " ! " AND pc1.relname='%s' " ! " AND pn1.oid = pc1.relnamespace " ! " AND pn1.nspname = '%s' " ! " AND pp1.proname LIKE '%%upd' " ! " AND pp2.proname LIKE '%%del'" ! " AND pt2.tgrelid = pt1.tgconstrrelid " ! " AND pt3.tgrelid = pt1.tgconstrrelid " ! " AND pn2.oid = pc2.relnamespace ", pk_table_needed, schema_needed); } else ! sprintf(tables_query, ! "SELECT pt1.tgargs, " ! " pt1.tgnargs, " ! " pt1.tgdeferrable, " ! " pt1.tginitdeferred, " " pp1.proname, " ! " pp2.proname, " " pc1.oid, " ! " pc2.oid, " ! " pc2.relname " ! "FROM pg_class pc1, " " pg_class pc2, " ! " pg_class pc3, " " pg_proc pp1, " ! " pg_proc pp2, " " pg_trigger pt1, " ! " pg_trigger pt2, " ! " pg_trigger pt3 " ! "WHERE pt1.tgconstrrelid = pc1.oid " ! " AND pt1.tgrelid = pc2.oid " ! " AND pt2.tgfoid = pp2.oid " " AND pt2.tgconstrrelid = pc2.oid " ! " AND pt3.tgconstrrelid = pc3.oid " ! " AND pt3.tgfoid = pp1.oid " ! " AND pc3.oid = pt1.tgrelid " ! " AND pc1.relname='%s' " ! " AND pp1.proname Like '%%upd' " ! " AND pp2.proname Like '%%del' " ! " AND pt2.tgrelid = pt1.tgconstrrelid " ! " AND pt3.tgrelid = pt1.tgconstrrelid ", pk_table_needed); result = PGAPI_ExecDirect(htbl_stmt, tables_query, strlen(tables_query));
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The attached patch fixes this by creating a pn1 and pn2 alias for
> pg_namespace in FROM. It also improves some of the alias tags. Applied
> to ODBC CVS. Can someone run some tests on these?
I see another bug here:
The 7.3-and-up versions of these queries should explicitly prefix
"pg_catalog." to the names of all system catalogs (and types and
functions) they reference. This will make them bulletproof against
conflicts against user-defined names, which might appear in front of
the system names in the search path.
regards, tom lane
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > Hiroshi Inoue wrote: > > -----Original Message----- > > From: Jim Holliday > > > > > For the ODBC driver psqlodbc.dll version 7.02.0003 there is > an error in > > the info.c. > > > In 2 places the SQL contains an alias in the FROM clause for the > > pg_namespace > > > table and then references the table in the where clause. The > below cut > > from the > > > info.c file should replace all instances on pg_namespace. in the where > > clause with > > > pn. This should be done in both places. > > > > Does the code give you a wrong result in the first place ? > > I see what Hiroshi is saying now --- there was a reference to > pg_namespace (in WHERE but not in FROM) _and_ a reference to 'pn' as an > alias for pg_namespace (in WHERE and FROM). > > The attached patch fixes this by creating a pn1 and pn2 alias for > pg_namespace in FROM. It also improves some of the alias tags. Applied > to ODBC CVS. Can someone run some tests on these? I've already changed my local source. I would replace your change with my code. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > The attached patch fixes this by creating a pn1 and pn2 alias for > > pg_namespace in FROM. It also improves some of the alias tags. Applied > > to ODBC CVS. Can someone run some tests on these? > > I've already changed my local source. > I would replace your change with my code. No problem. Once you commit, I will go back and redo the small cleanups I did. Thanks. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote:
>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > The attached patch fixes this by creating a pn1 and pn2 alias for
> > pg_namespace in FROM. It also improves some of the alias tags. Applied
> > to ODBC CVS. Can someone run some tests on these?
>
> I see another bug here:
>
> The 7.3-and-up versions of these queries should explicitly prefix
> "pg_catalog." to the names of all system catalogs (and types and
> functions) they reference. This will make them bulletproof against
> conflicts against user-defined names, which might appear in front of
> the system names in the search path.
OK I would change as such when I have a time.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
This patch adds catalog prefixes to system catalog references for the queries that assume schema support. What I did not do was to duplicate other queries that reference system tables that _don't_ assume schema code. Comments? --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > The attached patch fixes this by creating a pn1 and pn2 alias for > > pg_namespace in FROM. It also improves some of the alias tags. Applied > > to ODBC CVS. Can someone run some tests on these? > > I see another bug here: > > The 7.3-and-up versions of these queries should explicitly prefix > "pg_catalog." to the names of all system catalogs (and types and > functions) they reference. This will make them bulletproof against > conflicts against user-defined names, which might appear in front of > the system names in the search path. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ? autom4te.cache Index: info.c =================================================================== RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v retrieving revision 1.95 diff -c -c -r1.95 info.c *** info.c 11 Feb 2003 16:57:49 -0000 1.95 --- info.c 21 Mar 2003 18:33:01 -0000 *************** *** 1270,1282 **** if (conn->schema_support) { /* view is represented by its relkind since 7.1 */ ! strcpy(tables_query, "select relname, nspname, relkind from pg_class, pg_namespace"); strcat(tables_query, " where relkind in ('r', 'v')"); } else if (PG_VERSION_GE(conn, 7.1)) { /* view is represented by its relkind since 7.1 */ ! strcpy(tables_query, "select relname, usename, relkind from pg_class, pg_user"); strcat(tables_query, " where relkind in ('r', 'v')"); } else --- 1270,1284 ---- if (conn->schema_support) { /* view is represented by its relkind since 7.1 */ ! strcpy(tables_query, "select relname, nspname, relkind" ! " from pg_catalog.pg_class, pg_catalog.pg_namespace"); strcat(tables_query, " where relkind in ('r', 'v')"); } else if (PG_VERSION_GE(conn, 7.1)) { /* view is represented by its relkind since 7.1 */ ! strcpy(tables_query, "select relname, usename, relkind" ! " from pg_class, pg_user"); strcat(tables_query, " where relkind in ('r', 'v')"); } else *************** *** 1685,1691 **** if (conn->schema_support) sprintf(columns_query, "select u.nspname, c.relname, a.attname, a.atttypid" ", t.typname, a.attnum, a.attlen, %s, a.attnotnull, c.relhasrules, c.relkind" ! " from pg_namespace u, pg_class c, pg_attribute a, pg_type t" " where u.oid = c.relnamespace" " and (not a.attisdropped)" " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)", --- 1687,1694 ---- if (conn->schema_support) sprintf(columns_query, "select u.nspname, c.relname, a.attname, a.atttypid" ", t.typname, a.attnum, a.attlen, %s, a.attnotnull, c.relhasrules, c.relkind" ! " from pg_catalog.pg_namespace u, pg_catalog.pg_class c," ! " pg_catalog.pg_attribute a, pg_catalog.pg_type t" " where u.oid = c.relnamespace" " and (not a.attisdropped)" " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)", *************** *** 2202,2208 **** if (PG_VERSION_GE(conn, 7.2)) strcat(columns_query, ", c.relhasoids"); if (conn->schema_support) ! strcat(columns_query, " from pg_namespace u, pg_class c where " "u.oid = c.relnamespace"); else strcat(columns_query, " from pg_user u, pg_class c where " --- 2205,2212 ---- if (PG_VERSION_GE(conn, 7.2)) strcat(columns_query, ", c.relhasoids"); if (conn->schema_support) ! strcat(columns_query, " from pg_catalog.pg_namespace u," ! " pg_catalog.pg_class c where " "u.oid = c.relnamespace"); else strcat(columns_query, " from pg_user u, pg_class c where " *************** *** 2562,2568 **** if (conn->schema_support) sprintf(index_query, "select c.relname, i.indkey, i.indisunique" ", i.indisclustered, a.amname, c.relhasrules, n.nspname" ! " from pg_index i, pg_class c, pg_class d, pg_am a, pg_namespace n" " where d.relname = '%s'" " and n.nspname = '%s'" " and n.oid = d.relnamespace" --- 2566,2574 ---- if (conn->schema_support) sprintf(index_query, "select c.relname, i.indkey, i.indisunique" ", i.indisclustered, a.amname, c.relhasrules, n.nspname" ! " from pg_catalog.pg_index i, pg_catalog.pg_class c," ! " pg_catalog.pg_class d, pg_catalog.pg_am a," ! " pg_catalog.pg_namespace n" " where d.relname = '%s'" " and n.nspname = '%s'" " and n.oid = d.relnamespace" *************** *** 2960,2966 **** */ if (conn->schema_support) sprintf(tables_query, "select ta.attname, ia.attnum" ! " from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i, pg_namespace n" " where c.relname = '%s'" " AND n.nspname = '%s'" " AND c.oid = i.indrelid" --- 2966,2974 ---- */ if (conn->schema_support) sprintf(tables_query, "select ta.attname, ia.attnum" ! " from pg_catalog.pg_attribute ta," ! " pg_catalog.pg_attribute ia, pg_catalog.pg_class c," ! " pg_catalog.pg_index i, pg_catalog.pg_namespace n" " where c.relname = '%s'" " AND n.nspname = '%s'" " AND c.oid = i.indrelid" *************** *** 2990,2996 **** */ if (conn->schema_support) sprintf(tables_query, "select ta.attname, ia.attnum" ! " from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i, pg_namespace n" " where c.relname = '%s_pkey'" " AND n.nspname = '%s'" " AND c.oid = i.indexrelid" --- 2998,3006 ---- */ if (conn->schema_support) sprintf(tables_query, "select ta.attname, ia.attnum" ! " from pg_catalog.pg_attribute ta," ! " pg_catalog.pg_attribute ia, pg_catalog.pg_class c," ! " pg_catalog.pg_index i, pg_catalog.pg_namespace n" " where c.relname = '%s_pkey'" " AND n.nspname = '%s'" " AND c.oid = i.indexrelid" *************** *** 3125,3131 **** if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select OID from pg_class, pg_namespace where relname = '%s' and pg_namespace.oid = relnamespaceand pg_namespace.nspname = '%s'", serverTableName, serverSchemaName); else sprintf(query, "select OID from pg_class where relname = '%s'", serverTableName); if (res = CC_send_query(conn, query, NULL, CLEAR_RESULT_ON_ABORT), res) --- 3135,3144 ---- if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select OID from pg_catalog.pg_class," ! " pg_catalog.pg_namespace" ! " where relname = '%s' and pg_namespace.oid = relnamespace and" ! " pg_namespace.nspname = '%s'", serverTableName, serverSchemaName); else sprintf(query, "select OID from pg_class where relname = '%s'", serverTableName); if (res = CC_send_query(conn, query, NULL, CLEAR_RESULT_ON_ABORT), res) *************** *** 3192,3201 **** if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select attrelid, attnum from pg_class, pg_attribute, pg_namespace " "where relname = '%s' and attrelid = pg_class.oid " "and (not attisdropped) " ! "and attname = '%s' and pg_namespace.oid = relnamespace and pg_namespace.nspname = '%s'", serverTableName,serverColumnName, serverSchemaName); else sprintf(query, "select attrelid, attnum from pg_class, pg_attribute " "where relname = '%s' and attrelid = pg_class.oid " --- 3205,3216 ---- if (!bError && continueExec) { if (conn->schema_support) ! sprintf(query, "select attrelid, attnum from pg_catalog.pg_class," ! " pg_catalog.pg_attribute, pg_catalog.pg_namespace " "where relname = '%s' and attrelid = pg_class.oid " "and (not attisdropped) " ! "and attname = '%s' and pg_namespace.oid = relnamespace and" ! " pg_namespace.nspname = '%s'", serverTableName, serverColumnName, serverSchemaName); else sprintf(query, "select attrelid, attnum from pg_class, pg_attribute " "where relname = '%s' and attrelid = pg_class.oid " *************** *** 3465,3471 **** mylog("%s: entering Foreign Key Case #2", func); if (conn->schema_support) { ! schema_strcat(schema_needed, "%.*s", szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName, conn); sprintf(tables_query, "SELECT pt.tgargs, " " pt.tgnargs, " " pt.tgdeferrable, " --- 3480,3486 ---- mylog("%s: entering Foreign Key Case #2", func); if (conn->schema_support) { ! schema_strcat(schema_needed, "%.*s", szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName, conn); sprintf(tables_query, "SELECT pt.tgargs, " " pt.tgnargs, " " pt.tgdeferrable, " *************** *** 3476,3491 **** " pc1.oid, " " pc1.relname, " " pn.nspname " ! "FROM pg_class pc, " ! " pg_proc pp1, " ! " pg_proc pp2, " ! " pg_trigger pt1, " ! " pg_trigger pt2, " ! " pg_proc pp, " ! " pg_trigger pt, " ! " pg_class pc1, " ! " pg_namespace pn " ! " pg_namespace pn1 " "WHERE pt.tgrelid = pc.oid " "AND pp.oid = pt.tgfoid " "AND pt1.tgconstrrelid = pc.oid " --- 3491,3506 ---- " pc1.oid, " " pc1.relname, " " pn.nspname " ! "FROM pg_catalog.pg_class pc, " ! " pg_catalog.pg_proc pp1, " ! " pg_catalog.pg_proc pp2, " ! " pg_catalog.pg_trigger pt1, " ! " pg_catalog.pg_trigger pt2, " ! " pg_catalog.pg_proc pp, " ! " pg_catalog.pg_trigger pt, " ! " pg_catalog.pg_class pc1, " ! " pg_catalog.pg_namespace pn " ! " pg_catalog.pg_namespace pn1 " "WHERE pt.tgrelid = pc.oid " "AND pp.oid = pt.tgfoid " "AND pt1.tgconstrrelid = pc.oid " *************** *** 3841,3847 **** { if (conn->schema_support) { ! schema_strcat(schema_needed, "%.*s", szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, conn); sprintf(tables_query, "SELECT pt.tgargs, " " pt.tgnargs, " " pt.tgdeferrable, " --- 3856,3862 ---- { if (conn->schema_support) { ! schema_strcat(schema_needed, "%.*s", szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, conn); sprintf(tables_query, "SELECT pt.tgargs, " " pt.tgnargs, " " pt.tgdeferrable, " *************** *** 3852,3867 **** " pc1.oid, " " pc1.relname, " " pn.nspname " ! "FROM pg_class pc, " ! " pg_class pc1, " ! " pg_class pc2, " ! " pg_proc pp, " ! " pg_proc pp1, " ! " pg_trigger pt, " ! " pg_trigger pt1, " ! " pg_trigger pt2, " ! " pg_namespace pn " ! " pg_namespace pn1 " "WHERE pt.tgconstrrelid = pc.oid " " AND pt.tgrelid = pc1.oid " " AND pt1.tgfoid = pp1.oid " --- 3867,3882 ---- " pc1.oid, " " pc1.relname, " " pn.nspname " ! "FROM pg_catalog.pg_class pc, " ! " pg_catalog.pg_class pc1, " ! " pg_catalog.pg_class pc2, " ! " pg_catalog.pg_proc pp, " ! " pg_catalog.pg_proc pp1, " ! " pg_catalog.pg_trigger pt, " ! " pg_catalog.pg_trigger pt1, " ! " pg_catalog.pg_trigger pt2, " ! " pg_catalog.pg_namespace pn " ! " pg_catalog.pg_namespace pn1 " "WHERE pt.tgconstrrelid = pc.oid " " AND pt.tgrelid = pc1.oid " " AND pt1.tgfoid = pp1.oid " *************** *** 4197,4203 **** { strcpy(proc_query, "select proname, proretset, prorettype, " "pronargs, proargtypes, nspname from " ! "pg_namespace, pg_proc where " "pg_proc.pronamespace = pg_namespace.oid " "and (not proretset)"); schema_strcat(proc_query, " and nspname like '%.*s'", szProcOwner, cbProcOwner, szProcName, cbProcName, conn); --- 4212,4218 ---- { strcpy(proc_query, "select proname, proretset, prorettype, " "pronargs, proargtypes, nspname from " ! "pg_catalog.pg_namespace, pg_catalog.pg_proc where " "pg_proc.pronamespace = pg_namespace.oid " "and (not proretset)"); schema_strcat(proc_query, " and nspname like '%.*s'", szProcOwner, cbProcOwner, szProcName, cbProcName, conn); *************** *** 4334,4340 **** QR_add_tuple(res, row); while (isdigit(*params)) params++; ! } } QR_Destructor(tres); /* --- 4349,4355 ---- QR_add_tuple(res, row); while (isdigit(*params)) params++; ! } } QR_Destructor(tres); /* *************** *** 4387,4393 **** " proname as " "PROCEDURE_NAME" ", '' as " "NUM_INPUT_PARAMS" "," " '' as " "NUM_OUTPUT_PARAMS" ", '' as " "NUM_RESULT_SETS" "," " '' as " "REMARKS" "," ! " case when prorettype = 0 then 1::int2 else 2::int2 end as " "PROCEDURE_TYPE" " from pg_namespace,pg_proc" " where pg_proc.pronamespace = pg_namespace.oid"); schema_strcat(proc_query, " and nspname like '%.*s'", szProcOwner, cbProcOwner, szProcName, cbProcName, conn); my_strcat(proc_query, " and proname like '%.*s'", szProcName, cbProcName); --- 4402,4410 ---- " proname as " "PROCEDURE_NAME" ", '' as " "NUM_INPUT_PARAMS" "," " '' as " "NUM_OUTPUT_PARAMS" ", '' as " "NUM_RESULT_SETS" "," " '' as " "REMARKS" "," ! " case when prorettype = 0 then 1::int2 else 2::int2 end" ! " as " "PROCEDURE_TYPE" " from pg_catalog.pg_namespace," ! " pg_catalog.pg_proc" " where pg_proc.pronamespace = pg_namespace.oid"); schema_strcat(proc_query, " and nspname like '%.*s'", szProcOwner, cbProcOwner, szProcName, cbProcName, conn); my_strcat(proc_query, " and proname like '%.*s'", szProcName, cbProcName); *************** *** 4446,4452 **** } } return addcnt; ! } static void useracl_upd(char (*useracl)[ACLMAX], QResultClass *allures, const char *user, const char *auth) { --- 4463,4469 ---- } } return addcnt; ! } static void useracl_upd(char (*useracl)[ACLMAX], QResultClass *allures, const char *user, const char *auth) { *************** *** 4527,4539 **** stmt->rowset_start = -1; stmt->current_col = -1; if (conn->schema_support) ! strncpy_null(proc_query, "select relname, usename, relacl, nspname from pg_namespace, pg_class , pg_user where",sizeof(proc_query)); ! else ! strncpy_null(proc_query, "select relname, usename, relacl from pg_class , pg_user where", sizeof(proc_query)); if ((flag & PODBC_NOT_SEARCH_PATTERN) != 0) { if (conn->schema_support) ! { schema_strcat(proc_query, " nspname = '%.*s' and", szTableOwner, cbTableOwner, szTableName, cbTableName, conn); } my_strcat(proc_query, " relname = '%.*s' and", szTableName, cbTableName); --- 4544,4559 ---- stmt->rowset_start = -1; stmt->current_col = -1; if (conn->schema_support) ! strncpy_null(proc_query, "select relname, usename, relacl, nspname" ! " from pg_catalog.pg_namespace, pg_catalog.pg_class ," ! " pg_catalog.pg_user where", sizeof(proc_query)); ! else ! strncpy_null(proc_query, "select relname, usename, relacl" ! " from pg_class , pg_user where", sizeof(proc_query)); if ((flag & PODBC_NOT_SEARCH_PATTERN) != 0) { if (conn->schema_support) ! { schema_strcat(proc_query, " nspname = '%.*s' and", szTableOwner, cbTableOwner, szTableName, cbTableName, conn); } my_strcat(proc_query, " relname = '%.*s' and", szTableName, cbTableName); *************** *** 4552,4559 **** my_strcat(proc_query, " relname like '%.*s' and", esc_table_name, escTbnamelen); } if (conn->schema_support) ! strcat(proc_query, " pg_namespace.oid = relnamespace and"); ! strcat(proc_query, " pg_user.usesysid = relowner"); if (res = CC_send_query(conn, proc_query, NULL, CLEAR_RESULT_ON_ABORT), !res) { SC_set_error(stmt, STMT_EXEC_ERROR, "PGAPI_TablePrivileges query error"); --- 4572,4579 ---- my_strcat(proc_query, " relname like '%.*s' and", esc_table_name, escTbnamelen); } if (conn->schema_support) ! strcat(proc_query, " pg_namespace.oid = relnamespace and"); ! strcat(proc_query, " pg_user.usesysid = relowner"); if (res = CC_send_query(conn, proc_query, NULL, CLEAR_RESULT_ON_ABORT), !res) { SC_set_error(stmt, STMT_EXEC_ERROR, "PGAPI_TablePrivileges query error");