Thread: COMMUTATOR doesn't seem to work
Maybe I am using it wrong but I get no error message when I use it. I
have a type called chkpass (a version is in the additional supplied
modules) and I create the equality operator like this:
CREATE OPERATOR = ( PROCEDURE = eq, LEFTARG = chkpass, RIGHTARG = text, COMMUTATOR = =,
NEGATOR = <>
);
This works;
cosmostest=# SELECT 'aaa'::chkpass = 'aaa'; ?column?
---------- t
(1 row)
But...
cosmostest=# SELECT 'aaa' = 'aaa'::chkpass;
ERROR: operator is only a shell: text = chkpass
LINE 1: SELECT 'aaa' = 'aaa'::chkpass;
When I look at the operators I see why, sort of...
public | = | chkpass | text | boolean | public | = | text | chkpass | -
|
So while it created the operator it didn't set a return type.
I don't know if this is a new issue or I simply got lucky and never
tried the opposite test before but I see this in 9.0.4 and 9.1.3.
Am I using the command improperly?
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy@Vex.Net
"D'Arcy Cain" <darcy@druid.net> writes:
> Maybe I am using it wrong but I get no error message when I use it. I
> have a type called chkpass (a version is in the additional supplied
> modules) and I create the equality operator like this:
> CREATE OPERATOR = (
> PROCEDURE = eq,
> LEFTARG = chkpass,
> RIGHTARG = text,
> COMMUTATOR = =,
> NEGATOR = <>
> );
Did you actually create a "text = chkpass" function and operator?
This declaration merely promises that you will provide one eventually.
The system does not have the ability to make one for you.
regards, tom lane
On Jun21, 2012, at 17:46 , D'Arcy Cain wrote: > Maybe I am using it wrong but I get no error message when I use it. I > have a type called chkpass (a version is in the additional supplied > modules) and I create the equality operator like this: > ... > > So while it created the operator it didn't set a return type. > > I don't know if this is a new issue or I simply got lucky and never > tried the opposite test before but I see this in 9.0.4 and 9.1.3. > Am I using the command improperly? COMMUTATOR (and also NEGATOR) only inform the planner/optimizer about the relationship between operators, but you still have to create all of them manually. What you see is the placeholder for that to-be-created operator that CREATE OPERATOR fabricated. It does that because if the COMMUTATOR or NEGATOR was required to already exist, how would you ever be able to create a pair of commuting operators? If you later use CREATE OPERATOR to actually create the COMMUTATOR or NEGATOR you specified previously, it'll simply complete the previously created placeholder. Long story short, you need another CREATE OPERATOR command for the COMMUTATOR (which argument types swapped), and it should in turn name the original operator as it's COMMUTATOR. best regards, Florian Pflug
On 12-06-21 12:18 PM, Tom Lane wrote: > Did you actually create a "text = chkpass" function and operator? > This declaration merely promises that you will provide one eventually. > The system does not have the ability to make one for you. I guess I am missing the point of COMMUTATOR then. The docs say "When you are defining a self-commutative operator, you just do it." It seems you need to do more than "just do it." As far as I can tell I will need to add another C function and another CREATE OPERATOR. Then I can create an operator that takes args (text, chkpass) instead of (chkpass, text). What is the COMMUTATOR for then? Is it just a hint to the planner? -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net
"D'Arcy Cain" <darcy@druid.net> writes:
> On 12-06-21 12:18 PM, Tom Lane wrote:
>> Did you actually create a "text = chkpass" function and operator?
>> This declaration merely promises that you will provide one eventually.
>> The system does not have the ability to make one for you.
> I guess I am missing the point of COMMUTATOR then. The docs say "When
> you are defining a self-commutative operator, you just do it." It
> seems you need to do more than "just do it.
Um, an operator with different types on left and right cannot be its own
commutator.
regards, tom lane
On 12-06-22 12:22 AM, Tom Lane wrote: > Um, an operator with different types on left and right cannot be its own > commutator. Understood. I completely misunderstood the purpose of COMMUTATOR. I thought that it was telling the system that the procedure could be called with the arguments reversed if it could not find a specific procedure. I see now what it is for. So I have my type working now but I had to create a new C function that take the opposite argument order. Seems redundant but I could not see a better way. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net
On Jun22, 2012, at 06:32 , D'Arcy Cain wrote: > So I have my type working now but I had to create a new C function > that take the opposite argument order. Seems redundant but I could > not see a better way. There isn't. Postgres itself contains a huge number of such functions, e.g. for every *lt() (less-than) there's a *gt() (greater-than). best regards, Florian Pflug
On 12-06-22 07:11 AM, Florian Pflug wrote: > On Jun22, 2012, at 06:32 , D'Arcy Cain wrote: >> So I have my type working now but I had to create a new C function >> that take the opposite argument order. Seems redundant but I could >> not see a better way. > > There isn't. Postgres itself contains a huge number of such functions, > e.g. for every *lt() (less-than) there's a *gt() (greater-than). Right but that's not the same thing. Assuming you meant lt/gte and lte/gt those still are not self-commutating (SC). For example, '=' on two ints is SC. The issue here is that the operator is SC but the args are different types. It would be nice if there was a way to automatically generate code that reverses arguments. Maybe such a thing belongs in the CREATE FUNCTION command. Or, I guess this works and keeps from creating a second C function: CREATE OR REPLACE FUNCTION eq(chkpass, text) RETURNS bool STRICT AS 'chkpass.so', 'chkpass_eq' LANGUAGE 'c' RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION eq(text, chkpass) RETURNS bool AS 'select eq($2, $1);' LANGUAGE SQL RETURNS NULL ONNULL INPUT; -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net
"D'Arcy Cain" <darcy@druid.net> writes:
> ... The issue here is that the operator is SC but
> the args are different types.
Well, that's a weird way of defining self-commutating, but ...
> It would be nice if there was a way
> to automatically generate code that reverses arguments. Maybe such
> a thing belongs in the CREATE FUNCTION command.
> Or, I guess this works and keeps from creating a second C function:
> CREATE OR REPLACE FUNCTION eq(chkpass, text)
> RETURNS bool
> STRICT
> AS 'chkpass.so', 'chkpass_eq'
> LANGUAGE 'c'
> RETURNS NULL ON NULL INPUT;
> CREATE OR REPLACE FUNCTION eq(text, chkpass)
> RETURNS bool
> AS 'select eq($2, $1);'
> LANGUAGE SQL
> RETURNS NULL ON NULL INPUT;
The thing is that either of those approaches is hugely more expensive
than just providing a second C function. It costs probably thousands
of cycles to inline that SQL function, each time it's used in a query.
I doubt that an "auto reverse the arguments" facility would be very
much cheaper. You could maybe argue that the aggregated maintenance
and space costs of all the commutator-pair functions are enough to
justify having some such solution instead, but I'm doubtful --- and
even if true, getting from here to there would be painful.
regards, tom lane
On 12-06-22 11:36 AM, Tom Lane wrote:
> "D'Arcy Cain"<darcy@druid.net> writes:
> The thing is that either of those approaches is hugely more expensive
> than just providing a second C function. It costs probably thousands
> of cycles to inline that SQL function, each time it's used in a query.
I assumed itwould be more expensive but didn't know it would be that
much more.
> I doubt that an "auto reverse the arguments" facility would be very
> much cheaper. You could maybe argue that the aggregated maintenance
> and space costs of all the commutator-pair functions are enough to
> justify having some such solution instead, but I'm doubtful --- and
> even if true, getting from here to there would be painful.
And it would only apply to a very specific type of function.
The other idea I had was to just have the second C function call the
first but that didn't work. Here is what I tried.
PG_FUNCTION_INFO_V1(chkpass_eq);
Datum
chkpass_eq(PG_FUNCTION_ARGS)
{ chkpass *a1 = (chkpass *) PG_GETARG_POINTER(0); text *a2 = (text *) PG_GETARG_TEXT_P(1); char
str[9];
strlcpy(str, a2->vl_dat, sizeof(str)); PG_RETURN_BOOL(strcmp(a1->password, crypt(str, a1->password)) == 0);
}
PG_FUNCTION_INFO_V1(chkpass_eq2);
Datum
chkpass_eq2(PG_FUNCTION_ARGS)
{ return chkpass_eq(PG_GETARG_POINTER(1), PG_GETARG_TEXT_P(0));
}
Now in this specific case the function is trivial and writing it twice
is no big deal but in general I hate writing the same code twice. I
suppose I could extract the actual operation out to a third function
and call it from the others. I may do that anyway just for the value
of the example. Or is there a way to do what I tried above?
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy@Vex.Net
On Fri, Jun 22, 2012 at 12:28 PM, D'Arcy Cain <darcy@druid.net> wrote:
>> I doubt that an "auto reverse the arguments" facility would be very
>> much cheaper. You could maybe argue that the aggregated maintenance
>> and space costs of all the commutator-pair functions are enough to
>> justify having some such solution instead, but I'm doubtful --- and
>> even if true, getting from here to there would be painful.
>
>
> And it would only apply to a very specific type of function.
>
> The other idea I had was to just have the second C function call the
> first but that didn't work. Here is what I tried.
>
> PG_FUNCTION_INFO_V1(chkpass_eq);
> Datum
> chkpass_eq(PG_FUNCTION_ARGS)
> {
> chkpass *a1 = (chkpass *) PG_GETARG_POINTER(0);
> text *a2 = (text *) PG_GETARG_TEXT_P(1);
> char str[9];
>
> strlcpy(str, a2->vl_dat, sizeof(str));
> PG_RETURN_BOOL(strcmp(a1->password, crypt(str, a1->password)) == 0);
> }
>
> PG_FUNCTION_INFO_V1(chkpass_eq2);
> Datum
> chkpass_eq2(PG_FUNCTION_ARGS)
> {
> return chkpass_eq(PG_GETARG_POINTER(1), PG_GETARG_TEXT_P(0));
> }
>
> Now in this specific case the function is trivial and writing it twice
> is no big deal but in general I hate writing the same code twice. I
> suppose I could extract the actual operation out to a third function
> and call it from the others. I may do that anyway just for the value
> of the example. Or is there a way to do what I tried above?
I think DirectionFunctionCall2 is what you want.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 12-06-22 07:09 PM, Robert Haas wrote: > I think DirectionFunctionCall2 is what you want. Can you elaborate? I could not find a single hit in Google or the documentation search on the PG site and it does not appear anywhere in the source distribution. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net
"D'Arcy Cain" <darcy@druid.net> writes:
> On 12-06-22 07:09 PM, Robert Haas wrote:
>> I think DirectionFunctionCall2 is what you want.
> Can you elaborate? I could not find a single hit in Google or the
> documentation search on the PG site and it does not appear anywhere
> in the source distribution.
He meant DirectFunctionCall2
regards, tom lane
On 12-06-23 12:17 AM, Tom Lane wrote: > "D'Arcy Cain" <darcy@druid.net> writes: >> On 12-06-22 07:09 PM, Robert Haas wrote: >>> I think DirectionFunctionCall2 is what you want. > >> Can you elaborate? I could not find a single hit in Google or the >> documentation search on the PG site and it does not appear anywhere >> in the source distribution. > > He meant DirectFunctionCall2 Still nothing in the documentation. At least the search box doesn't find it. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net
2012/6/23 D'Arcy Cain <darcy@druid.net>: > On 12-06-23 12:17 AM, Tom Lane wrote: >> >> "D'Arcy Cain" <darcy@druid.net> writes: >>> >>> On 12-06-22 07:09 PM, Robert Haas wrote: >>>> >>>> I think DirectionFunctionCall2 is what you want. >> >> >>> Can you elaborate? I could not find a single hit in Google or the >>> documentation search on the PG site and it does not appear anywhere >>> in the source distribution. >> >> >> He meant DirectFunctionCall2 > > > Still nothing in the documentation. At least the search box doesn't > find it. Try in the search box of postgres doxygen documentation [1].. [1] http://doxygen.postgresql.org/ []s -- Dickson S. Guedes mail/xmpp: guedes@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br
On 12-06-23 08:21 AM, Dickson S. Guedes wrote: >> Still nothing in the documentation. At least the search box doesn't >> find it. > > Try in the search box of postgres doxygen documentation [1].. That's source, not documentation. I already found it in the actual source files but that's not the same thing. For one thing, if it isn't documented then it may not be meant to be exposed to the API. For another, without documentation we can't tell what is guaranteed to work and what is an implementation detail. I know that I can fiddle about and try stuff until it works but I don't work for Microsoft. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net
"D'Arcy Cain" <darcy@druid.net> writes:
> On 12-06-23 08:21 AM, Dickson S. Guedes wrote:
>> Try in the search box of postgres doxygen documentation [1]..
> That's source, not documentation. I already found it in the actual
> source files but that's not the same thing. For one thing, if it
> isn't documented then it may not be meant to be exposed to the API.
> For another, without documentation we can't tell what is guaranteed
> to work and what is an implementation detail.
By and large, most of what you need to know to write C code for PG
is not in the SGML documentation. I haven't seen any volunteers
stepping up to write another thousand or two pages of formal docs,
so that's how it's going to be for the foreseeable future. You need
to be prepared to read the source code and especially to look at
existing examples such as the contrib modules.
As far as the "not guaranteed to work" angle is concerned, it's true,
we could decide to change that. It's not very likely though: we do
try to avoid breaking things that we expect are in use by third-party
code, and anything that is in use by half a dozen contrib modules
certainly qualifies. If you're thinking of doing something that you
don't see lots of precedents for in contrib or the core datatypes,
it would be reasonable to ask about it on -hackers first.
regards, tom lane