Thread: Help with casting and comparing.

Help with casting and comparing.

From
Tzahi Fadida
Date:
Hi,

I need help finding out how to determine if two types are equality compatible
and compare them.

I am using the following call to check for equality between two values:
DatumGetBool(\       FunctionCall2(&(fctx->tupleSetAttEQFunctions[attID]->eq_opr_finfo)\         , lvalue, rvalue))

The structure fctx->tupleSetAttEQFunctions[attID]->eq_opr_finfo currently
holds a reference to an equality function that is proper for the type of
lvalue and rvalue.

Currently i only allow two values only of the same type but i wish to allow
to compare values like "20.2"=?20.2 or 20=?20.0 etc...

The first step is to find out if two attributes are equality and casting
compatible, i.e., if one type can be cast to the other type so they can be
compared. Or, just equality compatible and the casting is done somehow behind
the scene.
Finally, i have to use a function to compare the two values.

--
Regards,
        Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS:  see at
http://members.lycos.co.uk/my2nis/spamwarning.html


Re: Help with casting and comparing.

From
Martijn van Oosterhout
Date:
On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote:
> Hi,
>
> I need help finding out how to determine if two types are equality compatible
> and compare them.

<snip>

> Currently i only allow two values only of the same type but i wish to allow
> to compare values like "20.2"=?20.2 or 20=?20.0 etc...
>
> The first step is to find out if two attributes are equality and casting
> compatible, i.e., if one type can be cast to the other type so they can be
> compared. Or, just equality compatible and the casting is done somehow behind
> the scene.

There's two ways an equality could work. For example, there are
equality operators that take parameters of different types. That's the
easy case. Then you have binary compatable types, and then types with
actual conversion functions.

Fortunatly the backend contains functions that do all this already.
Check out parser/parse_oper.c, in particular oper() and
compatable_oper().

You may have to be prepared to handle a parsetree to do the actual
work.

/* oper() -- search for a binary operator* Given operator name, types of arg1 and arg2, return oper struct.**
IMPORTANT:the returned operator (if any) is only promised to be* coercion-compatible with the input datatypes.  Do not
usethis if* you need an exact- or binary-compatible match; see compatible_oper. 
...etc...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Help with casting and comparing.

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote:
>> I need help finding out how to determine if two types are equality compatible
>> and compare them.

> Fortunatly the backend contains functions that do all this already.
> Check out parser/parse_oper.c, in particular oper() and
> compatable_oper().

Note that this still leaves the question of what operator to search for,
and where to look for it.  The current system doesn't really provide an
adequate way of identifying a suitable equality operator; you kind of
have to take it on faith that people won't have made "=" do unexpected
things (an assumption already violated by some builtin datatypes ...).
We've been moving gradually in the direction of relying on btree
operator classes to give us a better understanding of which operators
really act like equality, but it's far from all done.

The most recent thread about fixing this was
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php
Nothing much has been done since then as far as fixing foreign-key
checks, but you might want to look at the code for interpreting row
value comparisons (make_row_comparison_op in parse_expr.c).
SelectSortFunction in tuplesort.c is another example of looking for
btree info to infer the behavior of an operator.
        regards, tom lane


Re: Help with casting and comparing.

From
Tzahi Fadida
Date:
I looked around in the code and the whole thing looks complex
and prone to breaking my code often, i.e., whenever someone will decide to
change the casting/operators. I thought about just
issuing in SPI_prepare query the proper casting like:
SELECT a0::text,a1::text ...
Casting to equal types (when neccessary) will allow me to just
use regular equality functions.
And perhaps the added benefit is that the casted values are cached? since
i restart cursor scans often(by moving to start not reopening).
The downside is that i noticed that the CTID is removed from the tuple
if a cast occurs. Is there a way to tell postgresql to not remove the
CTID?
The other way, of course is to add CTID as an attribute in the query
but it seems less efficient since i am accessing it repeatedly.


On Wednesday 28 June 2006 18:12, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Wed, Jun 28, 2006 at 03:25:57PM +0300, Tzahi Fadida wrote:
> >> I need help finding out how to determine if two types are equality
> >> compatible and compare them.
> >
> > Fortunatly the backend contains functions that do all this already.
> > Check out parser/parse_oper.c, in particular oper() and
> > compatable_oper().
>
> Note that this still leaves the question of what operator to search for,
> and where to look for it.  The current system doesn't really provide an
> adequate way of identifying a suitable equality operator; you kind of
> have to take it on faith that people won't have made "=" do unexpected
> things (an assumption already violated by some builtin datatypes ...).
> We've been moving gradually in the direction of relying on btree
> operator classes to give us a better understanding of which operators
> really act like equality, but it's far from all done.
>
> The most recent thread about fixing this was
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php
> Nothing much has been done since then as far as fixing foreign-key
> checks, but you might want to look at the code for interpreting row
> value comparisons (make_row_comparison_op in parse_expr.c).
> SelectSortFunction in tuplesort.c is another example of looking for
> btree info to infer the behavior of an operator.
>
>             regards, tom lane

--
Regards,
��������Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS: �see at
http://members.lycos.co.uk/my2nis/spamwarning.html


Re: Help with casting and comparing.

From
Martijn van Oosterhout
Date:
On Thu, Jul 06, 2006 at 07:43:20PM +0300, Tzahi Fadida wrote:
> The downside is that i noticed that the CTID is removed from the tuple
> if a cast occurs. Is there a way to tell postgresql to not remove the
> CTID?

Err, the fact the ctid is removed is really just a side-effect. With no
adjusting of the output, you may just get the actual on-disk tuple. But
as soon as you do some manipulation, you get a new tuple.

> The other way, of course is to add CTID as an attribute in the query
> but it seems less efficient since i am accessing it repeatedly.

If you want the ctid, you have to ask for it.

But this seems a little like premature optimisation. First, make it
work, then make it fast. Once you've got it working you can worry about
performance. Adding an extra column to the output costs very, very
little compared to other things...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Help with casting and comparing.

From
Tzahi Fadida
Date:
On Thursday 06 July 2006 21:55, Martijn van Oosterhout wrote:
> On Thu, Jul 06, 2006 at 07:43:20PM +0300, Tzahi Fadida wrote:
> > The downside is that i noticed that the CTID is removed from the tuple
> > if a cast occurs. Is there a way to tell postgresql to not remove the
> > CTID?
>
> Err, the fact the ctid is removed is really just a side-effect. With no
> adjusting of the output, you may just get the actual on-disk tuple. But
> as soon as you do some manipulation, you get a new tuple.
>
> > The other way, of course is to add CTID as an attribute in the query
> > but it seems less efficient since i am accessing it repeatedly.
>
> If you want the ctid, you have to ask for it.
>
> But this seems a little like premature optimisation. First, make it
> work, then make it fast. Once you've got it working you can worry about
> performance. Adding an extra column to the output costs very, very
> little compared to other things...

It works, i use this technique for index accesses. I am not worried about
getting this to work since i already tried this. I am more worried about
optimization. Well, it is probably the lesser evil of dealing with casting.
P.s. the code is running and can be found here:
http://pgfoundry.org/projects/fulldisjunction/


--
Regards,
��������Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS: �see at
http://members.lycos.co.uk/my2nis/spamwarning.html