Thread: getIndexInfo() not returning operator classes

getIndexInfo() not returning operator classes

From
Thomas Kellerer
Date:
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

Getting param names

From
"Srivats"
Date:
Hi All,

Is there anyway of getting all the IN Parameters of a particular Stored
Procedure in JAVA.

Thks


Re: getIndexInfo() not returning operator classes

From
Kris Jurka
Date:

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

Re: getIndexInfo() not returning operator classes

From
Thomas Kellerer
Date:
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

Re: getIndexInfo() not returning operator classes

From
Kris Jurka
Date:

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

Re: getIndexInfo() not returning operator classes

From
Thomas Kellerer
Date:
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

Re: getIndexInfo() not returning operator classes

From
Kris Jurka
Date:

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

Re: getIndexInfo() not returning operator classes

From
Thomas Kellerer
Date:
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

Re: getIndexInfo() not returning operator classes

From
Tom Lane
Date:
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

Re: Getting param names

From
Kris Jurka
Date:

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


Re: Getting param names

From
Dave Cramer
Date:
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
>


Re: Getting param names

From
Kris Jurka
Date:

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

unsubscribe

From
solar
Date: