obj_unique_identifier(oid)
Hi all!
I was a bit frustrated there was no function to generate a unique
identifier for any oid.
Instead of complaining, I decided to automate the process as far as possible. :)
The result is a simple perl function to automatically generate a
function for each regclass able to generate a unique text identifier.
The function obj_unique_identifier(oid) will return a unique name for _any_ oid.
I have looked at the unique constraints for each system_catalog to
make sure all identifiers are unique.
Source code:
perl script to generate .sql file:
https://github.com/gluefinance/pov/blob/master/sql/schema/pov/functions/obj_unique_identifier.pl
output from perl script:
https://github.com/gluefinance/pov/blob/master/sql/schema/pov/functions/obj_unique_identifier.sql
I would highly appreicate feedback on the structure of the identifier.
It must be composed in a way which will guarantee uniqueness.
Example:
glue=# select obj_unique_identifier(refobjid) from pg_depend order by
random() limit 10;
obj_unique_identifier
------------------------------------------------------------------------------------
pg_proc.pg_catalog.iso8859_1_to_utf8(integer, integer, cstring,
internal, integer)
pg_operator.pg_catalog.float8.pg_catalog.float8.pg_catalog.-
pg_operator.pg_catalog.money.pg_catalog.int4.pg_catalog.*
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._time.pg_catalog._time.4
pg_operator.pg_catalog.int2.pg_catalog.int4.pg_catalog.-
pg_class.pg_catalog.pg_statio_sys_sequences
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bool.pg_catalog._bool.1
pg_class.pg_catalog.pg_stat_all_indexes
pg_class.pg_catalog.pg_type
pg_proc.pg_catalog.pg_stat_get_function_time(oid)
(10 rows)
--
Best regards,
Joel Jacobson
Glue Finance
Joel Jacobson <joel@gluefinance.com> writes:
The function obj_unique_identifier(oid) will return a unique name for _any_ oid.
Surely this is broken by design? You can *not* assume that the same OID
isn't in use for different things in different system catalogs. They're
only guaranteed unique within a catalog. That's the main reason why
pg_depend has to include the classid.
regards, tom lane
Sent from my iPhone
On 7 jan 2011, at 20:46, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joel Jacobson <joel@gluefinance.com> writes:
The function obj_unique_identifier(oid) will return a unique name for _any_ oid.
Surely this is broken by design? You can *not* assume that the same OID
isn't in use for different things in different system catalogs. They're
only guaranteed unique within a catalog. That's the main reason why
pg_depend has to include the classid.regards, tom lane
Correct. That is why the regclass name (classid) is included in the unique name.
The function should take both classid and oid as input. I'll fix.
Sent from my iPhone
On 7 jan 2011, at 20:59, Joel Jacobson <joel@gluefinance.com> wrote:
Show quoted text
Sent from my iPhone
On 7 jan 2011, at 20:46, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joel Jacobson <joel@gluefinance.com> writes:
The function obj_unique_identifier(oid) will return a unique name for _any_ oid.
Surely this is broken by design? You can *not* assume that the same OID
isn't in use for different things in different system catalogs. They're
only guaranteed unique within a catalog. That's the main reason why
pg_depend has to include the classid.regards, tom lane
Correct. That is why the regclass name (classid) is included in the unique name.
On Jan 7, 2011, at 1:46 PM, Tom Lane wrote:
Joel Jacobson <joel@gluefinance.com> writes:
The function obj_unique_identifier(oid) will return a unique name for _any_ oid.
Surely this is broken by design? You can *not* assume that the same OID
isn't in use for different things in different system catalogs. They're
only guaranteed unique within a catalog. That's the main reason why
pg_depend has to include the classid.
BTW, if you're looking at making pg_depnd easier to use, see http://archives.postgresql.org/message-id/1290000774-sup-2218@alvh.no-ip.org
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
2011/1/7 Jim Nasby <jim@nasby.net>:
BTW, if you're looking at making pg_depnd easier to use, see http://archives.postgresql.org/message-id/1290000774-sup-2218@alvh.no-ip.org
I guess there are more than one ways to do it, C, sql, plperl, plpgsql. :)
I guess at least one of the methods should be provided in the vanilla distro. :)
--
Best regards,
Joel Jacobson
Glue Finance
On Sat, Jan 8, 2011 at 1:59 AM, Joel Jacobson <joel@gluefinance.com> wrote:
2011/1/7 Jim Nasby <jim@nasby.net>:
BTW, if you're looking at making pg_depnd easier to use, see http://archives.postgresql.org/message-id/1290000774-sup-2218@alvh.no-ip.org
I guess there are more than one ways to do it, C, sql, plperl, plpgsql. :)
I guess at least one of the methods should be provided in the vanilla distro. :)
I guess the point is that if this gets committed as a core function
written in C, we don't need any other implementations. But I don't
recall ever seeing a commit for that one go by...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
I guess the point is that if this gets committed as a core function
written in C, we don't need any other implementations. But I don't
recall ever seeing a commit for that one go by...
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
2011/1/8 Dimitri Fontaine <dimitri@2ndquadrant.fr>:
Nice! Has the patch been accepted and will be made available in future
versions of pg?
Also, why return NULL for pinned objects? They can also be described
using a unique identifier.
(+ /* for "pinned" items in pg_depend, return null */)
It is useful to describe such objects to be able to diff different
versions of pg, i.e. comparing which pinned objects exists, doing so
can tell you the odds for an application depending on certain pinned
objects being compatible with a specific version of the database.
--
Best regards,
Joel Jacobson
Glue Finance
On Sat, Jan 8, 2011 at 14:05, Joel Jacobson <joel@gluefinance.com> wrote:
2011/1/8 Dimitri Fontaine <dimitri@2ndquadrant.fr>:
Nice! Has the patch been accepted and will be made available in future
versions of pg?
Yes. Once things are committed to the main repository, they are only
backed out if someone finds a major issue with them that is not
fixable (ina reasonable timeframe). That almost never happens. We
don't keep unapproved patches or development branches in the main
repository - those are all in the personal repositories of the
developers.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
2011/1/8 Magnus Hagander <magnus@hagander.net>:
Yes. Once things are committed to the main repository, they are only
backed out if someone finds a major issue with them that is not
fixable (ina reasonable timeframe). That almost never happens. We
don't keep unapproved patches or development branches in the main
repository - those are all in the personal repositories of the
developers.
Thanks for clarifying.
I found a bug in the pg_catalog.pg_describe_object function.
The query below should not return any rows, because if it does, then
there are oids with non-unique descriptions.
While the description is good enough for a human to interpret, it
cannot be used in an application as a unique identifier unless it is
really unique.
WITH
all_objects AS (
SELECT classid, objid, objsubid FROM pg_depend
UNION
SELECT refclassid, refobjid, refobjsubid FROM pg_depend
)
SELECT pg_catalog.pg_describe_object(classid,objid,objsubid)
FROM all_objects
GROUP BY pg_catalog.pg_describe_object(classid,objid,objsubid)
HAVING COUNT(*) > 1
pg_describe_object
----------------------------------------------------------------------------------------------------------------------------------------
function 2 ginarrayextract(anyarray,internal) of operator family
array_ops for access method gin
function 4 ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
function 3 ginqueryarrayextract(anyarray,internal,smallint,internal,internal)
of operator family array_ops for access method gin
function 1 network_cmp(inet,inet) of operator family array_ops for
access method gin
function 1 bttextcmp(text,text) of operator family array_ops for
access method gin
(5 rows)
There are 94 objects such objects:
classid | objid | objsubid |
obj_unique_identifier |
pg_describe_object
---------+-------+----------+--------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------
2603 | 10606 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._text.pg_catalog._text.1
| function 1 bttextcmp(text,text) of operator family
array_ops for access method gin
2603 | 10610 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varchar.pg_catalog._varchar.1
| function 1 bttextcmp(text,text) of operator family array_ops
for access method gin
2603 | 10650 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._inet.pg_catalog._inet.1
| function 1 network_cmp(inet,inet) of operator family
array_ops for access method gin
2603 | 10654 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._cidr.pg_catalog._cidr.1
| function 1 network_cmp(inet,inet) of operator family
array_ops for access method gin
2603 | 10631 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bytea.pg_catalog._bytea.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10671 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._macaddr.pg_catalog._macaddr.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10667 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._interval.pg_catalog._interval.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10675 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._name.pg_catalog._name.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10719 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._tinterval.pg_catalog._tinterval.2
| function 2 ginarrayextract(anyarray,internal) of operator family
array_ops for access method gin
2603 | 10607 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._text.pg_catalog._text.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10611 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varchar.pg_catalog._varchar.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10655 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._cidr.pg_catalog._cidr.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10707 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamp.pg_catalog._timestamp.2
| function 2 ginarrayextract(anyarray,internal) of operator family
array_ops for access method gin
2603 | 10711 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._money.pg_catalog._money.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10663 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int8.pg_catalog._int8.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10635 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._char.pg_catalog._char.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10703 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varbit.pg_catalog._varbit.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10627 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bpchar.pg_catalog._bpchar.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10695 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamptz.pg_catalog._timestamptz.2
| function 2 ginarrayextract(anyarray,internal) of operator family
array_ops for access method gin
2603 | 10603 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int4.pg_catalog._int4.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10683 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._oid.pg_catalog._oid.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10715 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._reltime.pg_catalog._reltime.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10699 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timetz.pg_catalog._timetz.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10615 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._abstime.pg_catalog._abstime.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10623 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bool.pg_catalog._bool.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10639 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._date.pg_catalog._date.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10691 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._time.pg_catalog._time.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10687 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._oidvector.pg_catalog._oidvector.2
| function 2 ginarrayextract(anyarray,internal) of operator family
array_ops for access method gin
2603 | 10659 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int2.pg_catalog._int2.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10647 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._float8.pg_catalog._float8.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10643 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._float4.pg_catalog._float4.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10651 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._inet.pg_catalog._inet.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10679 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._numeric.pg_catalog._numeric.2
| function 2 ginarrayextract(anyarray,internal) of operator
family array_ops for access method gin
2603 | 10619 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bit.pg_catalog._bit.2
| function 2 ginarrayextract(anyarray,internal) of
operator family array_ops for access method gin
2603 | 10660 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int2.pg_catalog._int2.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10696 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamptz.pg_catalog._timestamptz.3
| function 3 ginqueryarrayextract(anyarray,internal,smallint,internal,internal)
of operator family array_ops for access method gin
2603 | 10648 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._float8.pg_catalog._float8.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10604 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int4.pg_catalog._int4.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10712 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._money.pg_catalog._money.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10664 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int8.pg_catalog._int8.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10652 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._inet.pg_catalog._inet.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10608 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._text.pg_catalog._text.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10636 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._char.pg_catalog._char.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10644 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._float4.pg_catalog._float4.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10612 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varchar.pg_catalog._varchar.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10672 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._macaddr.pg_catalog._macaddr.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10620 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bit.pg_catalog._bit.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10624 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bool.pg_catalog._bool.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10704 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varbit.pg_catalog._varbit.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10616 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._abstime.pg_catalog._abstime.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10656 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._cidr.pg_catalog._cidr.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10680 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._numeric.pg_catalog._numeric.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10716 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._reltime.pg_catalog._reltime.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10668 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._interval.pg_catalog._interval.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10720 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._tinterval.pg_catalog._tinterval.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10692 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._time.pg_catalog._time.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10676 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._name.pg_catalog._name.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10700 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timetz.pg_catalog._timetz.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10628 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bpchar.pg_catalog._bpchar.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10684 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._oid.pg_catalog._oid.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10640 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._date.pg_catalog._date.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10632 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bytea.pg_catalog._bytea.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10708 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamp.pg_catalog._timestamp.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10688 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._oidvector.pg_catalog._oidvector.3
| function 3
ginqueryarrayextract(anyarray,internal,smallint,internal,internal) of
operator family array_ops for access method gin
2603 | 10609 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._text.pg_catalog._text.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10657 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._cidr.pg_catalog._cidr.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10717 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._reltime.pg_catalog._reltime.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10649 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._float8.pg_catalog._float8.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10713 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._money.pg_catalog._money.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10693 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._time.pg_catalog._time.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10669 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._interval.pg_catalog._interval.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10629 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bpchar.pg_catalog._bpchar.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10709 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamp.pg_catalog._timestamp.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10617 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._abstime.pg_catalog._abstime.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10665 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int8.pg_catalog._int8.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10641 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._date.pg_catalog._date.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10605 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int4.pg_catalog._int4.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10689 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._oidvector.pg_catalog._oidvector.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10721 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._tinterval.pg_catalog._tinterval.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10625 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bool.pg_catalog._bool.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10681 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._numeric.pg_catalog._numeric.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10621 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bit.pg_catalog._bit.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10701 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timetz.pg_catalog._timetz.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10697 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._timestamptz.pg_catalog._timestamptz.4
| function 4 ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10673 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._macaddr.pg_catalog._macaddr.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10645 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._float4.pg_catalog._float4.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10661 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._int2.pg_catalog._int2.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10613 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varchar.pg_catalog._varchar.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10653 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._inet.pg_catalog._inet.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10633 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bytea.pg_catalog._bytea.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10705 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._varbit.pg_catalog._varbit.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10685 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._oid.pg_catalog._oid.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10677 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._name.pg_catalog._name.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
2603 | 10637 | 0 |
pg_amproc.gin.pg_catalog.array_ops.pg_catalog._char.pg_catalog._char.4
| function 4
ginarrayconsistent(internal,smallint,anyarray,integer,internal,internal)
of operator family array_ops for access method gin
(94 rows)
On Sat, Jan 8, 2011 at 12:41 PM, Joel Jacobson <joel@gluefinance.com> wrote:
The query below should not return any rows, because if it does, then
there are oids with non-unique descriptions.
I don't think your analysis is correct. Each entry in pg_depend
represents the fact that one object depends on another object, and an
object could easily depend on more than one other object, or be
depended upon by more than one other object, or depend on one object
and be depended on by another.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
2011/1/8 Robert Haas <robertmhaas@gmail.com>:
I don't think your analysis is correct. Each entry in pg_depend
represents the fact that one object depends on another object, and an
object could easily depend on more than one other object, or be
depended upon by more than one other object, or depend on one object
and be depended on by another.
What does that have to do with this?
Two different oids represents two different objects, right?
Two different objects should have two different descriptions, right?
Otherwise I cannot see how one can argue the description being unique.
The pg_describe_object returns unique descriptions for all object
types, except for the 5 types I unexpectedly found.
On Sat, 2011-01-08 at 22:21 +0100, Joel Jacobson wrote:
2011/1/8 Robert Haas <robertmhaas@gmail.com>:
I don't think your analysis is correct. Each entry in pg_depend
represents the fact that one object depends on another object, and an
object could easily depend on more than one other object, or be
depended upon by more than one other object, or depend on one object
and be depended on by another.What does that have to do with this?
Two different oids represents two different objects, right?
Two different objects should have two different descriptions, right?
Otherwise I cannot see how one can argue the description being unique.The pg_describe_object returns unique descriptions for all object
types, except for the 5 types I unexpectedly found.
I can confirm it has nothing to do with pg_depend, and that it seems to
be a bug with that descriptions do not seem to care about different
amproclefttype and amprocrighttype.
SELECT array_agg(oid), array_agg(amproclefttype) FROM pg_amproc GROUP BY
pg_catalog.pg_describe_object(2603,oid,0) HAVING count(*) > 1;
One example row produced by that query.
array_agg | array_agg
---------------+-------------
{10608,10612} | {1009,1015}
(1 row)
Regards,
Andreas Karlsson
On Sat, Jan 8, 2011 at 4:21 PM, Joel Jacobson <joel@gluefinance.com> wrote:
2011/1/8 Robert Haas <robertmhaas@gmail.com>:
I don't think your analysis is correct. Each entry in pg_depend
represents the fact that one object depends on another object, and an
object could easily depend on more than one other object, or be
depended upon by more than one other object, or depend on one object
and be depended on by another.What does that have to do with this?
Oops. I misread your query. I thought the duplicates were because
you were feeding pg_describe_object the same classoid, objoid,
objsubid pair more than once, but I see now that's not the case (UNION
!= UNION ALL).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Here is a patch, but I am not sure I am not sure if I like my idea for
format. What do you think?
SELECT pg_describe_object('pg_amproc'::regclass,oid,0)
FROM pg_amproc WHERE oid IN (10608,10612);
pg_describe_object
----------------------------------------------------------------------------------------------------------------------------------
function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (text[],text[])
function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (character varying[],character varying[])
(2 rows)
Andreas
Attachments:
fix-amproctypes-in-get-object-description.patchtext/x-patch; charset=UTF-8; name=fix-amproctypes-in-get-object-description.patchDownload+6-6
2011/1/9 Andreas Karlsson <andreas@proxel.se>:
Here is a patch, but I am not sure I am not sure if I like my idea for
format. What do you think?SELECT pg_describe_object('pg_amproc'::regclass,oid,0)
FROM pg_amproc WHERE oid IN (10608,10612);
pg_describe_object
----------------------------------------------------------------------------------------------------------------------------------
function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (text[],text[])
function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (character varying[],character varying[])
(2 rows)
Looks great! Many thanks for fixing the bug!
Andreas
--
Best regards,
Joel Jacobson
Glue Finance
E: jj@gluefinance.com
T: +46 70 360 38 01
Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden
Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden
2011/1/9 Robert Haas <robertmhaas@gmail.com>:
Oops. I misread your query. I thought the duplicates were because
you were feeding pg_describe_object the same classoid, objoid,
objsubid pair more than once, but I see now that's not the case (UNION
!= UNION ALL).
Ah, I see, yes, the query should actually be UNION, it would produce
the same result, but perhaps it would be a bit faster.
--
Best regards,
Joel Jacobson
Glue Finance
On Sat, Jan 8, 2011 at 8:02 PM, Joel Jacobson <joel@gluefinance.com> wrote:
2011/1/9 Robert Haas <robertmhaas@gmail.com>:
Oops. I misread your query. I thought the duplicates were because
you were feeding pg_describe_object the same classoid, objoid,
objsubid pair more than once, but I see now that's not the case (UNION
!= UNION ALL).Ah, I see, yes, the query should actually be UNION, it would produce
the same result, but perhaps it would be a bit faster.
You did use UNION - I think if you used UNION ALL you'd get spurious
results. But maybe I'm still confused.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Here is the bug-fix patch again with a description of the context so I
can add it to the commit fest.
Joel Jacobson discovered a bug in the function pg_describe_object where
it does not produce unique identifiers for some entries in pg_amproc.
This patch fixes the bug where when two entries in pg_amproc only differ
in amproclefttype or amprocrighttype the same description will be
produced by pg_describe_object, by simply adding the two fields
(amproclefttype, amprocrighttype) to the description.
== Before patch
SELECT pg_describe_object('pg_amproc'::regclass,oid,0)
FROM pg_amproc WHERE oid IN (10608,10612);
pg_describe_object
------------------------------------------------------------------------------------
function 1 bttextcmp(text,text) of operator family array_ops for access method gin
function 1 bttextcmp(text,text) of operator family array_ops for access method gin
(2 rows)
== After patch
SELECT pg_describe_object('pg_amproc'::regclass,oid,0)
FROM pg_amproc WHERE oid IN (10608,10612);
pg_describe_object
----------------------------------------------------------------------------------------------------------------------------------
function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (text[],text[])
function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (character varying[],character varying[])
(2 rows)
Regards,
Andreas
