Thread: regclass and search_path

Hi,

I'm using the autodoc regression database available at


http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/autodoc/autodoc/regressdatabase.sql?rev=1.2&content-type=text/x-cvsweb-markup

This has several schemas that have cross-schema foreign key constraints
such as the following:

  autodoc=> \d product.product
                                        Table "product.product"
        Column        |  Type   |                              Modifiers

---------------------+---------+----------------------------------------------------------------------
  product_id          | integer | not null default
nextval('product.product_product_id_seq'::regclass)
  product_code        | text    | not null
  product_description | text    |
Indexes:
     "product_pkey" PRIMARY KEY, btree (product_id)
     "product_product_code_key" UNIQUE, btree (product_code)
Check constraints:
     "product_product_code_check" CHECK (product_code = upper(product_code))
Referenced by:
     TABLE "store.inventory" CONSTRAINT "inventory_product_id_fkey"
FOREIGN KEY (product_id) REFERENCES product.product(product_id) ON
UPDATE CASCADE ON DELETE RESTRICT
     TABLE "warehouse.inventory" CONSTRAINT "inventory_product_id_fkey"
FOREIGN KEY (product_id) REFERENCES product.product(product_id) ON
UPDATE CASCADE ON DELETE RESTRICT

I'm using this to validate a tool I'm building and I get an error on the
following query:

autodoc=> SELECT conname::regclass FROM pg_constraint
autodoc->       WHERE contype = 'u';
ERROR:  relation "product_product_code_key" does not exist

The 8.4 documentation says:

The regclass input converter handles the table lookup according to the
schema path setting, and so it does the "right thing" automatically.

My search path is the default "$user", public and I'm only able to avoid
the error if I set the search_path to cover all the schemas, e.g.,

autodoc=> set search_path to "$user", public, product, store, warehouse;
SET
autodoc=> SELECT conname::regclass FROM pg_constraint
       WHERE contype = 'u';
               conname
------------------------------------
  product_product_code_key
  store_store_code_key
  warehouse_warehouse_code_key
  warehouse_warehouse_supervisor_key
(4 rows)

I would've thought that the "right thing" would have involved prepending
the schema to the constraint name, e.g.,
product.product_product_code_key as is done for the table names in the
\d output. Is this a bug or does regclass only do the "right thing" for
tables and not for constraints?

Joe

Joe Abbate <jma@freedomcircle.com> writes:
> I'm using this to validate a tool I'm building and I get an error on the
> following query:

> autodoc=> SELECT conname::regclass FROM pg_constraint
> autodoc->       WHERE contype = 'u';
> ERROR:  relation "product_product_code_key" does not exist

Ummm ... pg_constraint.conname contains a constraint name, not a table
name, so casting it to regclass is highly likely to fail.  This hasn't
got anything to do with search_path AFAICS, it's just a thinko.

Depending on what it is that you're hoping to do, any of conrelid,
confrelid, or conindid might be what you're after.  All of those columns
would contain pg_class OIDs that could usefully be cast to regclass.

            regards, tom lane

Hi Tom,

On 03/18/2011 12:17 AM, Tom Lane wrote:
> Joe Abbate<jma@freedomcircle.com>  writes:
>> I'm using this to validate a tool I'm building and I get an error on the
>> following query:
>
>> autodoc=>  SELECT conname::regclass FROM pg_constraint
>> autodoc->        WHERE contype = 'u';
>> ERROR:  relation "product_product_code_key" does not exist
>
> Ummm ... pg_constraint.conname contains a constraint name, not a table
> name, so casting it to regclass is highly likely to fail.  This hasn't
> got anything to do with search_path AFAICS, it's just a thinko.
>
> Depending on what it is that you're hoping to do, any of conrelid,
> confrelid, or conindid might be what you're after.  All of those columns
> would contain pg_class OIDs that could usefully be cast to regclass.

Well, the pg_constraint.conname value exists as a relname in pg_class,
and the query works with constraints that don't cross schemas as
autodoc's does (or if you add all necessary schemas to your
search_path). For example,

moviesdb=> alter table film add unique (title);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index
"film_title_key" for table "film"
ALTER TABLE
moviesdb=> SELECT conname::regclass FROM pg_constraint WHERE contype = 'u';
     conname
----------------
  film_title_key
(1 row)

For my immediate needs, the query was actually the target of a NOT IN
subquery of a query against pg_index (trying to exclude tuples of
indexes for UNIQUE constraints) and I've solved that by using conrelid
in the subquery (and indrelid in the main query).  Nevertheless, I think
regclass should probably be smarter and work with anything in pg_class
(regardless of search_path).

Regards,

Joe

HI Tom,

On 03/18/2011 12:42 AM, Joe Abbate wrote:
> For my immediate needs, the query was actually the target of a NOT IN
> subquery of a query against pg_index (trying to exclude tuples of
> indexes for UNIQUE constraints) and I've solved that by using conrelid
> in the subquery (and indrelid in the main query).  Nevertheless, I
> think regclass should probably be smarter and work with anything in
> pg_class (regardless of search_path).

On second thought, conname is just a "name", is not unique and is
lacking schema/namespace info. As you said, a thinko.

Regards,

Joe