From: | Joe Abbate <jma(at)freedomcircle(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | regclass and search_path |
Date: | 2011-03-17 17:27:10 |
Message-ID: | 4D82446E.1070601@freedomcircle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I'm using the autodoc regression database available at
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2011-03-17 18:18:25 | Re: PG COPY from version 8 to 9 issue with timezonetz |
Previous Message | Brent Gulanowski | 2011-03-17 17:10:49 | PG COPY from version 8 to 9 issue with timezonetz |