Re: [GENERAL] Foreign keys question (performance)
On Sun, Dec 4, 2011 at 7:41 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. I have a problem. Here's my table designs. The problem is that if Table 1 (stores) has a foreign key reference from another child table (stores_registered), then when I update Table 1, it throws an error that referential intergrity is being violate because Table 2 depends on Table 1. However, if I update Table 2 first, it tells me that the fkey in Table 1 doesn't exist (of course). Any ideas? What am I missing? How do updates work in terms of CASCADE? Thanks! mydb=# \d stores Table public.stores Column |Type |Modifiers -+-+- strid | character varying(35) | not null plc | text| not null user_registered | boolean | private_key | character varying(6)| default NULL::character varying modify_date | timestamp without time zone | default now() ip | bigint | plc_md5 | text| Indexes: idx_stores_pkey PRIMARY KEY, btree (strid) idx_stores_ip_plc UNIQUE, btree (ip, plc_md5) idx_stores_modify_date btree (modify_date) idx_stores_plcmd5 btree (plc_md5) Check constraints: stores_strid_check CHECK (strid::text ~ '[-.~a-z0-9_]'::text) Referenced by: TABLE stores_registered CONSTRAINT fk_stores_registered FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE TABLE stores_stats CONSTRAINT fk_stats FOREIGN KEY (strid) REFERENCES stores(strid) ON DELETE CASCADE TABLE interesting CONSTRAINT interesting_strid_fkey FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE mydb=# \d stores_registered Column|Type |Modifiers --+-+- strid| character varying(35) | not null plc | text| not null user_id | character varying(30) | not null modify_date | timestamp without time zone | default now() plc_md5 | text| Indexes: idx_stores_registered_pkey PRIMARY KEY, btree (strid) idx_stores_registered_userid_plc UNIQUE, btree (user_id, plc_md5) Check constraints: stores_strid_check CHECK (strid::text ~ '[-.~a-z0-9_]'::text) stores_plc_check CHECK (plc ''::text) Foreign-key constraints: fk_stores_registered FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE stores_registered_users_fkey FOREIGN KEY (user_id) REFERENCES users(id) MATCH FULL ON DELETE CASCADE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign keys question (performance)
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Phoenix Kiula Sent: Tuesday, December 06, 2011 11:46 AM To: Alban Hertroys Cc: PG-General Mailing List Subject: Re: [GENERAL] Foreign keys question (performance) On Sun, Dec 4, 2011 at 7:41 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. I have a problem. Here's my table designs. The problem is that if Table 1 (stores) has a foreign key reference from another child table (stores_registered), then when I update Table 1, it throws an error that referential intergrity is being violate because Table 2 depends on Table 1. However, if I update Table 2 first, it tells me that the fkey in Table 1 doesn't exist (of course). Any ideas? What am I missing? How do updates work in terms of CASCADE? Thanks! mydb=# \d stores Table public.stores Column |Type |Modifiers -+-+ -+-+- strid | character varying(35) | not null plc | text| not null user_registered | boolean | private_key | character varying(6)| default NULL::character varying modify_date | timestamp without time zone | default now() ip | bigint | plc_md5 | text| Indexes: idx_stores_pkey PRIMARY KEY, btree (strid) idx_stores_ip_plc UNIQUE, btree (ip, plc_md5) idx_stores_modify_date btree (modify_date) idx_stores_plcmd5 btree (plc_md5) Check constraints: stores_strid_check CHECK (strid::text ~ '[-.~a-z0-9_]'::text) Referenced by: TABLE stores_registered CONSTRAINT fk_stores_registered FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE TABLE stores_stats CONSTRAINT fk_stats FOREIGN KEY (strid) REFERENCES stores(strid) ON DELETE CASCADE TABLE interesting CONSTRAINT interesting_strid_fkey FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE mydb=# \d stores_registered Column|Type |Modifiers --+-+--- --+-+-- strid| character varying(35) | not null plc | text| not null user_id | character varying(30) | not null modify_date | timestamp without time zone | default now() plc_md5 | text| Indexes: idx_stores_registered_pkey PRIMARY KEY, btree (strid) idx_stores_registered_userid_plc UNIQUE, btree (user_id, plc_md5) Check constraints: stores_strid_check CHECK (strid::text ~ '[-.~a-z0-9_]'::text) stores_plc_check CHECK (plc ''::text) Foreign-key constraints: fk_stores_registered FOREIGN KEY (strid) REFERENCES stores(strid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE stores_registered_users_fkey FOREIGN KEY (user_id) REFERENCES users(id) MATCH FULL ON DELETE CASCADE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- If I am reading this right your issue is not stores_registered but stores_stats - the later is missing the ON UPDATE CASCADE modifier to its foreign key. With ON UPDATE CASCADE when you change the primary key all related foreign keys have their values changed as well. With this enabled you do not need to directly modify table2 but instead you let the system do its thing when you update table1. I believe you have the logic figured out but in this case (and maybe the error message is simply unclear - you never did provide your UPDATE statement nor your error message) I think it is the missing ON UPDATE CASCADE on stores_stats that is your issue. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign keys question (performance)
On 12/06/2011 08:45 AM, Phoenix Kiula wrote: On Sun, Dec 4, 2011 at 7:41 PM, Phoenix Kiulaphoenix.ki...@gmail.com wrote: On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroysharam...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: I have a problem. Here's my table designs. The problem is that if Table 1 (stores) has a foreign key reference from another child table (stores_registered), then when I update Table 1, it throws an error that referential intergrity is being violate because Table 2 depends on Table 1. However, if I update Table 2 first, it tells me that the fkey in Table 1 doesn't exist (of course). Any ideas? What am I missing? How do updates work in terms of CASCADE? What is the actual error message? Thanks! -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign keys question (performance)
Hi. I have a foreign key as such: ALTER TABLE child_table ADD CONSTRAINT fk_child FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL ON DELETE CASCADE ; Questions: 1. Is MATCH FULL adding any value here? If the foreign key is just on an id column, what purpose does it serve? Without it, the results would be the same? Does it affect performance or should I leave it be? (Note that the id is a alphanumeric value) 2. More importantly, in this case basically the child_table cannot have any keys that the parent_table doesn't have either. Will INSERTs and UPDATEs to the parent_table be slower? Or will the foreign key check happen only when INSERT or UPDATE happen to the child_table? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign keys question (performance)
On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: Hi. I have a foreign key as such: ALTER TABLE child_table ADD CONSTRAINT fk_child FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL ON DELETE CASCADE ; Questions: 1. Is MATCH FULL adding any value here? If the foreign key is just on an id column, what purpose does it serve? Without it, the results would be the same? Does it affect performance or should I leave it be? (Note that the id is a alphanumeric value) Nope, it is not. As I understand it, it only does something on multi-column foreign keys where parts of the key are NULL. To quote the documentation: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented. I can't say much on the impact on performance, but I'd expect that to be negligible in this case: With the MATCH FULL in place, it will need to check whether any of your columns are NULL, but that's only a single column in your case. 2. More importantly, in this case basically the child_table cannot have any keys that the parent_table doesn't have either. Will INSERTs and UPDATEs to the parent_table be slower? Or will the foreign key check happen only when INSERT or UPDATE happen to the child_table? INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign keys question (performance)
On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. Thanks Albert. Very useful. I had ON DELETE...ALSO DELETE rules earlier and in some cases they let some keys go by in associated tables. Hope foreign key constraint is more reliable! PK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign Keys Question
First of all, hi. I'm new to this mailing list. I searched this on the net, but I didn't get any usable answers... So here's my problem: I have 2 schemas. One is called SITE, one SITE_forum. What I wanted, is to separate the forum from the whole Site db, so I can put them on different servers if I encounter performance problems or anything. So I started the SITE_forum schema, made all my tables, and realized, that I don't want to create the table users in the schema SITE_forum, because I want to use the exact same users than in the schema SITE. One possibility would be, to create 2 identic tables on both schemas, but that really really is not what I'd like to do. So I thought I could make a foreign key on a different Schema (db), and use the same table And well, thats where I started to search if this is possible ... and, in fact my main question is: Is this a good idea? I have no idea if this will be fast enough, or if I will have lots of problems afterward Could anyone help me with this ? Thanks in advance, Matthias Loitsch -- THEK Matthias Loitsch www.studiothek.com/ pgpPyT3vJZehW.pgp Description: PGP signature
Re: [GENERAL] Foreign Keys Question
On Wed, Apr 13, 2005 at 13:54:05 +0200, Matthias Loitsch [EMAIL PROTECTED] wrote: So I thought I could make a foreign key on a different Schema (db), and use the same table And well, thats where I started to search if this is possible ... and, in fact my main question is: Is this a good idea? I have no idea if this will be fast enough, or if I will have lots of problems afterward Could anyone help me with this ? Schemas are just a name space, so using foreign keys accross schemas shouldn't be a problem. However, it you later put the contents of the schema on other server or even in another database, then you are going to need to make a copy of the data as you can't make foreign key references outside of the current database. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Foreign Keys Question
Will inherits helps you ? create table SITE_forum.t1 () inherits (SITE.t); Oleg On Wed, 13 Apr 2005, Matthias Loitsch wrote: First of all, hi. I'm new to this mailing list. I searched this on the net, but I didn't get any usable answers... So here's my problem: I have 2 schemas. One is called SITE, one SITE_forum. What I wanted, is to separate the forum from the whole Site db, so I can put them on different servers if I encounter performance problems or anything. So I started the SITE_forum schema, made all my tables, and realized, that I don't want to create the table users in the schema SITE_forum, because I want to use the exact same users than in the schema SITE. One possibility would be, to create 2 identic tables on both schemas, but that really really is not what I'd like to do. So I thought I could make a foreign key on a different Schema (db), and use the same table And well, thats where I started to search if this is possible ... and, in fact my main question is: Is this a good idea? I have no idea if this will be fast enough, or if I will have lots of problems afterward Could anyone help me with this ? Thanks in advance, Matthias Loitsch Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
