Thread: pg_operator.oprcode in 9.2rc1
Hello hackers, I've been testing Pyrseas against 9.2rc1. A test that does a CREATE OPERATOR is giving a small difference. Specifically, the test issues the statement: CREATE OPERATOR + (PROCEDURE = upper, RIGHTARG = text); Pyrseas then queries the pg_operator catalog to map the procedure for output. Selecting oprcode in 8.4, 9.0, and 9.1, always returns 'upper', but in 9.2rc1, the value is 'pg_catalog.upper'. It does not matter whether pg_catalog is on the search_path or not. I looked over the release notes but I couldn't find any reference to this possible change in behavior. I'd like to confirm whether the schema-qualified procedure name is a permanent change or an unintended side effect of something else. Thanks. Joe
Joe Abbate <jma@freedomcircle.com> writes:
> Hello hackers,
> I've been testing Pyrseas against 9.2rc1. A test that does a CREATE
> OPERATOR is giving a small difference. Specifically, the test issues
> the statement:
> CREATE OPERATOR + (PROCEDURE = upper, RIGHTARG = text);
> Pyrseas then queries the pg_operator catalog to map the procedure for
> output. Selecting oprcode in 8.4, 9.0, and 9.1, always returns 'upper',
> but in 9.2rc1, the value is 'pg_catalog.upper'. It does not matter
> whether pg_catalog is on the search_path or not.
The reason for the difference is that in 9.2 there's more than one
pg_catalog.upper():
regression=# \df upper List of functions Schema | Name | Result data type | Argument data
types| Type
------------+-------+------------------+---------------------+--------pg_catalog | upper | anyelement | anyrange
| normalpg_catalog | upper | text | text | normal
(2 rows)
whereas prior versions had only upper(text). The regproc output
function isn't allowed to print argument types, which is what would be
needed to disambiguate altogether, but it does what it can to warn you
that "upper" alone is not a unique name by schema-qualifying the name.
This is not new behavior in 9.2, it just happens to occur for upper()
when it did not before. If you're expecting regproc to always return
unique unqualified names then your code is broken anyway, since such
a requirement cannot be met when the function is overloaded.
regards, tom lane
Hello Tom, On 30/08/12 12:27, Tom Lane wrote: > The reason for the difference is that in 9.2 there's more than one > pg_catalog.upper(): > > regression=# \df upper > List of functions > Schema | Name | Result data type | Argument data types | Type > ------------+-------+------------------+---------------------+-------- > pg_catalog | upper | anyelement | anyrange | normal > pg_catalog | upper | text | text | normal > (2 rows) > > whereas prior versions had only upper(text). The regproc output > function isn't allowed to print argument types, which is what would be > needed to disambiguate altogether, but it does what it can to warn you > that "upper" alone is not a unique name by schema-qualifying the name. > > This is not new behavior in 9.2, it just happens to occur for upper() > when it did not before. If you're expecting regproc to always return > unique unqualified names then your code is broken anyway, since such > a requirement cannot be met when the function is overloaded. Hmmm ... Well, I'm just doing the same thing as pg_dump, which in 9.2rc1 still outputs the same as before, namely: SET search_path = public, pg_catalog; -- -- Name: +; Type: OPERATOR; Schema: public; Owner: - -- CREATE OPERATOR + ( PROCEDURE = upper, RIGHTARG = text ); What's somewhat confusing is that the documentation (and \d pg_operator) states oprcode (as well as oprrest and oprjoin) are of type 'regproc' and that it references a pg_proc.oid. Does the catalog actually store an OID, i.e., the OID of pg_catalog.upper(text), or something else? Best regards, Joe
Joe Abbate <jma@freedomcircle.com> writes:
> On 30/08/12 12:27, Tom Lane wrote:
>> The reason for the difference is that in 9.2 there's more than one
>> pg_catalog.upper():
> Hmmm ... Well, I'm just doing the same thing as pg_dump, which in 9.2rc1
> still outputs the same as before, namely:
Well, evidently you're *not* doing the same thing as pg_dump.
A look at pg_dump says that what it does is to cast the column to
regprocedure, and then strip the argument types from that printout.
Perhaps some experimentation would be illuminating:
regression=# select 'upper'::regproc;
ERROR: more than one function named "upper"
LINE 1: select 'upper'::regproc; ^
regression=# select 'upper(text)'::regprocedure;regprocedure
--------------upper(text)
(1 row)
regression=# select 'upper(text)'::regprocedure::oid;oid
-----871
(1 row)
regression=# select 871::regprocedure;regprocedure
--------------upper(text)
(1 row)
regression=# select 871::regproc; regproc
------------------pg_catalog.upper
(1 row)
> What's somewhat confusing is that the documentation (and \d pg_operator)
> states oprcode (as well as oprrest and oprjoin) are of type 'regproc'
> and that it references a pg_proc.oid. Does the catalog actually store
> an OID, i.e., the OID of pg_catalog.upper(text), or something else?
What's physically in there is an OID (and so the casts above are no-ops
at the representational level). What we're discussing is the behavior
of the output function for the regproc or regprocedure types.
regards, tom lane
Hello Tom, On 30/08/12 13:23, Tom Lane wrote: > Joe Abbate <jma@freedomcircle.com> writes: >> Hmmm ... Well, I'm just doing the same thing as pg_dump, which in 9.2rc1 >> still outputs the same as before, namely: > > Well, evidently you're *not* doing the same thing as pg_dump. I meant that the Pyrseas dbtoyaml's output is essentially the same as pg_dump, e.g., schema public: operator +(NONE, text): procedure: upper Therefore, if psql doesn't have problem restoring the operator from the pg_dump output, neither should yamltodb have problem generating the SQL to recreate the operator. The above YAML (with or without the schema qualification) does generate the correct SQL and pg_operator.oprcode ends up with the correct OID. So at least for this test case, dbtoyam/yamltodb is not broken (but I'll have to do something about the unittest difference). > What's physically in there is an OID (and so the casts above are no-ops > at the representational level). What we're discussing is the behavior > of the output function for the regproc or regprocedure types. Yes, I suspected that an OID was stored. What I'd still quibble with is the use of the ambiguous regproc in pg_operator (also pg_type) and the still-ambiguous schema-qualified proc name. I guess it's not feasible (at least, short term), but it'd be preferable to store a "raw" OID and let the user cast to regprocedure (or change the 'regproc' to 'regprocedure'). Best regards, Joe
Joe Abbate <jma@freedomcircle.com> writes:
> Yes, I suspected that an OID was stored. What I'd still quibble with is
> the use of the ambiguous regproc in pg_operator (also pg_type) and the
> still-ambiguous schema-qualified proc name. I guess it's not feasible
> (at least, short term), but it'd be preferable to store a "raw" OID and
> let the user cast to regprocedure (or change the 'regproc' to
> 'regprocedure').
Yeah, ideally those columns would be regprocedure. There are
bootstrapping problems involved though with populating the initial
contents of the catalogs during initdb --- the regprocedure input
function doesn't work in that environment. (It might be possible to
hack something for pg_operator, but the circularity is rather
fundamental for loading pg_type, since the input function would need to
consult pg_type to make sense of argument types.)
In the meantime I'd suggest casting the columns to regprocedure when
querying, if you want unambiguous results. That's what pg_dump does.
Or you can cast to OID if you like numbers.
regards, tom lane