Thread: getIndexInfo() not returning operator classes
Hello, when I call getIndexInfo() it correctly returns the definition for function based indexes, but it does not return operator classed. An index: create index idx_foo on foo(bar varchar_pattern_ops); will show up as "bar" only in the definition column of the result set. This makes it a bit confusing if you have another index like: create index idx_foo_2 on foo(bar); Which will show two identical index definitions even though they are different. I tested this with PG 8.1 and the 8.1 build 404 driver on Windows 2000 Regards Thomas
Hi All, Is there anyway of getting all the IN Parameters of a particular Stored Procedure in JAVA. Thks
On Thu, 10 Nov 2005, Thomas Kellerer wrote: > when I call getIndexInfo() it correctly returns the definition for function > based indexes, but it does not return operator classed. > > An index: create index idx_foo on foo(bar varchar_pattern_ops); > > will show up as "bar" only in the definition column of the result set. I'm not sure where you'd like to see the opclass displayed in the results of getIndexInfo. Also consider that every indexed column has a opclass, the default or a user specified one. Kris Jurka
Kris Jurka wrote on 10.11.2005 08:00: >> when I call getIndexInfo() it correctly returns the definition for >> function based indexes, but it does not return operator classed. >> >> An index: create index idx_foo on foo(bar varchar_pattern_ops); >> >> will show up as "bar" only in the definition column of the result set. > > > I'm not sure where you'd like to see the opclass displayed in the > results of getIndexInfo. Also consider that every indexed column has a > opclass, the default or a user specified one. > for the above mentioned index, I would expect the "DEFINITION" field of the result set to contain "bar varchar_pattern_ops". Unless I misunderstood the operator class and it does not apply to a column but to the whole index. In that case, yes there isn't an apropriate place to put this information. Thanks for your answer Thomas
On Thu, 10 Nov 2005, Thomas Kellerer wrote: > for the above mentioned index, I would expect the "DEFINITION" field of the > result set to contain "bar varchar_pattern_ops". Unless I misunderstood the > operator class and it does not apply to a column but to the whole index. In > that case, yes there isn't an apropriate place to put this information. > Opclasses are per column, not for the whole index. I don't see any DEFINITION column though. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean) Kris Jurka
Kris Jurka wrote on 10.11.2005 18:48: >> for the above mentioned index, I would expect the "DEFINITION" field >> of the result set to contain "bar varchar_pattern_ops". Unless I >> misunderstood the operator class and it does not apply to a column but >> to the whole index. In that case, yes there isn't an apropriate place >> to put this information. >> > > Opclasses are per column, not for the whole index. I don't see any > DEFINITION column though. > > http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean) > Sorry, I meant COLUMN_NAME (internally I'm collecting all values from COLUMN_NAME into a field definition, that's where the error comes from). That column will e.g. contain something like "lower(name)" for a function based index, so I don't see a reason why it couldn't contain "bar varchar_pattern_ops" In both cases it's not the column's name any longer Regards Thomas
On Thu, 10 Nov 2005, Thomas Kellerer wrote: > Sorry, I meant COLUMN_NAME (internally I'm collecting all values from > COLUMN_NAME into a field definition, that's where the error comes from). > Are you sure you want to be using getIndexInfo for this instead of something more pg specific like the pg_get_indexdef function? Aside from this very specific problem (overlapping indexes with different opclasses), I don't think I'd ever want to see the opclass in the column name. Certainly not if I was using the default opclass, and probably not if I was using a non-default opclass, but that was the only index on that column. > That column will e.g. contain something like "lower(name)" for a > function based index, so I don't see a reason why it couldn't contain > "bar varchar_pattern_ops" In both cases it's not the column's name any > longer > In the function case the indexed data is not the column data, so putting the column's name is misleading. In the opclass case the indexed data is really the column's data so the column name is accurate. The fact that it is indexed differently for different operations upon it is not terribly relevent to the column name at least. I personally don't think adding opclass to the column_name is a good idea, but you're welcome to try and sway me with a convincing argument or popular support. Kris Jurka
Kris Jurka wrote on 10.11.2005 19:39: > Are you sure you want to be using getIndexInfo for this instead of > something more pg specific like the pg_get_indexdef function? > > I personally don't think adding opclass to the column_name is a good > idea, but you're welcome to try and sway me with a convincing argument > or popular support. The reason I'm asking: I'm maintaining a JDBC based SQL client which is also displaying index information (including the SQL to re-create them) Currently the information returned by the driver is not enough to fully re-create those index definitions. So I'll have to maintain Postgres specific code for this, which is not a big deal (for displaying the DDL I now use pg_indexes which contains the correct SQL) It surely isn't a big thing, and probably doesn't affect 99% of the users, but still I thought I bring this topic up. Thanks for your time. Thomas
Kris Jurka <books@ejurka.com> writes:
> In the function case the indexed data is not the column data, so putting
> the column's name is misleading.
There's another argument here, which is that the JDBC spec (presumably)
says that that field is a column name, full stop. Now an app that's
using functional indexes is already outside the spec, so returning
something that's not just a name seems acceptable in that case, and
arguably it's the proper name for the index's data anyway. But cramming
an opclass name in there seems to me to violate the spirit of the spec.
regards, tom lane
On Thu, 10 Nov 2005, Srivats wrote: > Is there anyway of getting all the IN Parameters of a particular Stored > Procedure in JAVA. > As was suggested earlier, see DatabaseMetaData.getProcedureColumns: http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getProcedureColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String) Although the postgresql driver hasn't been updated to handle the new out parameters in 8.1. Kris Jurka
Kris, What new out parameters are you referring to ? Dave On 10-Nov-05, at 11:20 AM, Kris Jurka wrote: > > > On Thu, 10 Nov 2005, Srivats wrote: > >> Is there anyway of getting all the IN Parameters of a particular >> Stored Procedure in JAVA. >> > > As was suggested earlier, see DatabaseMetaData.getProcedureColumns: > > http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ > DatabaseMetaData.html#getProcedureColumns(java.lang.String,% > 20java.lang.String,%20java.lang.String,%20java.lang.String) > > Although the postgresql driver hasn't been updated to handle the > new out parameters in 8.1. > > Kris Jurka > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Thu, 10 Nov 2005, Dave Cramer wrote: > What new out parameters are you referring to ? > I'm speaking specifically in the context of DatabaseMetaData.getProcedureColumns()'s COLUMN_TYPE column. Kris Jurka