PostgreSQL JDBC - getIndexInfo() not returning operator classes

Author

getIndexInfo() not returning operator classes

Thomas Kellerer

2005-11-10, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Kris Jurka

2005-11-10, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Thomas Kellerer

2005-11-10, 1:23 pm

Kris Jurka wrote on 10.11.2005 08:00:
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Kris Jurka

2005-11-10, 1:23 pm

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...ml#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang. String,%20boolean,%2
0boolean)

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thomas Kellerer

2005-11-10, 1:23 pm

Kris Jurka wrote on 10.11.2005 18:48:
>
> 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...ml#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang. String,%20boolean,%2
0boolean)
>

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Kris Jurka

2005-11-10, 1:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Thomas Kellerer

2005-11-10, 1:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2005-11-10, 1:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links