Re: [BUGS] foreign key constraint not working when index tablespace

#1

Hi Tom,

Ok. You are right but I am not crazy (yet).

If I create the tables and indexes as user postgres it works like a charm.

But if I create the tables as a mortal user or create them as postgres but in
the schema of user joelk and grant all to user joelk, I can insert data
without the foreign key constraint being respected. Now if I drop the foreign
key constraint and recreate it with a schema prefix in the references section,
the constarint works fine.

alter table joelk.jk_users add FOREIGN KEY (department) REFERENCES
joelk.jk_map(code);

fis=> INSERT INTO joelk.jk_users VALUES( 'fancott', 'Fancott, T.',
'fancott (AT) cs (DOT) concordia.ca', '08', 'Professor' );
ERROR: insert or update on table "jk_users" violates foreign key constraint
"jk_users_department_fkey"
DETAIL: Key (department)=(08) is not present in table "jk_map".

fis=> \d jk_users Table "joelk.jk_users"
Column | Type | Modifiers
------------+------------------------+-----------
username | character varying(8) |
name | character varying(64) | not null
email | character varying(128) |
department | character varying(2) | not null
title | character varying(64) | not null
Indexes:
"jk_users_pkey" PRIMARY KEY, btree (name, department, title)
Foreign-key constraints:
"jk_users_department_fkey" FOREIGN KEY (department) REFERENCES jk_map(code)

Everything also works fine if a mortal user creates the tables and indexes in
the appropriate tablespaces and uses the schema.table(column_name) in the
references section of the foreign key constraint.

Strange.

Not sure if this is relevant - in this database I dropped the public schema
and I had to grant all to the group public so that users could create indexes
in the fis_index tablespace.

fis=> \db+
List of tablespaces
Name | Owner | Location | Access
privileges
------------+----------+-----------------------------------+-----------------------------------
fis | postgres | /local/data/pgsql-8.0/fis |
fis_index | postgres | /local/data/pgsql-8.0/fis_index |
{postgres=C/postgres,=C/postgres}

Thanks for looking into it.

Regards

joel

Tom Lane wrote:

Quote:

Joel Krajden <joelk (AT) cs (DOT) concordia.ca> writes:

If the indexes are created in fis_index, the foreign key constraints in the
user table are ignored on insert and update.

Works for me...

$ mkdir /tmp/fis
$ mkdir /tmp/fis_index
$ psql regression
...
regression=# create tablespace fis location '/tmp/fis';
CREATE TABLESPACE
regression=# create tablespace fis_index location '/tmp/fis_index';
CREATE TABLESPACE
regression=# \i joel.sql
psql:joel.sql:11: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dept_map_pkey" for table "dept_map"
CREATE TABLE
psql:joel.sql:23: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "card_category_map_pkey" for table "card_category_map"
CREATE TABLE
psql:joel.sql:38: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fis_title_map_pkey" for table "fis_title_map"
CREATE TABLE
psql:joel.sql:57: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
regression=# insert into users values('username','name','email','dp','title');
ERROR: insert or update on table "users" violates foreign key constraint "users_department_fkey"
DETAIL: Key (department)=(dp) is not present in table "dept_map".
regression=# insert into dept_map values('dp','desc');
INSERT 0 1
regression=# insert into users values('username','name','email','dp','title');
ERROR: insert or update on table "users" violates foreign key constraint "users_title_fkey"
DETAIL: Key (title)=(title) is not present in table "fis_title_map".
regression=# insert into fis_title_map values('title','cardcat');
ERROR: insert or update on table "fis_title_map" violates foreign key constraint "fis_title_map_card_category_fkey"
DETAIL: Key (card_category)=(cardcat) is not present in table "card_category_map".
regression=# insert into card_category_map values('cardcat','desc');
INSERT 0 1
regression=# insert into fis_title_map values('title','cardcat');
INSERT 0 1
regression=# insert into users values('username','name','email','dp','title');
INSERT 0 1
regression=#

regards, tom lane

--

Quote:

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

I don't think I am crazy and the user who showed me the problem exists - but
damn it neither he or I cannot duplicate the problem. The only thing that
changed is each of us has logged out of our session and logged back in.

The only thing that could have happened is that sometime in the past the
schema search path changed after the user logged in. There was no other table
with a duplicate name in another schema.

Thanks for doing the sanity check.

Joel

Tom Lane wrote:

Quote:

Joel Krajden <joelk (AT) cs (DOT) concordia.ca> writes:

But if I create the tables as a mortal user or create them as postgres
but in the schema of user joelk and grant all to user joelk, I can
insert data without the foreign key constraint being respected. Now if
I drop the foreign key constraint and recreate it with a schema prefix
in the references section, the constarint works fine.

This is even harder to believe than the first report. Could we see a
complete, self-contained test case? A SQL script that demonstrates the
problem from a standing start in an empty database is what I have in mind.

(What I suspect is that you have multiple similarly-named tables in
different schemas and are getting confused by that...)

regards, tom lane

--

Quote:

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] foreign key constraint not working when index tablespace

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] foreign key constraint not working when index tablespace - 03-29-2005 , 04:38 PM

Re: [BUGS] foreign key constraint not working when index tablespace - 03-30-2005 , 03:14 PM