Re: [GENERAL] Foreign Keys as first class citizens at design time?
Hi, I agree with each of the points you've made. The idea here is meant as an extension of what is already available. So yes, this is intended to answer the questions of the designer's original model. The consideration being that you design your database and the underlying logic of your decision are already built in. Then when querying that database to perform mundane day to day business tasks your query set is simple and easy to build. I don't see why this should detract from the idea of a free form query being built using the existing tools to answer a new question which may be entirely unrelated to the original models purpose. This just couldn't work independently of the current SQL feature set. Additionally, this something which has not really been touched on, this allows a form of iterative structure in a one line query. On Fri, 14 Aug 2015 03:52:28 +0100, David G. Johnston david.g.johns...@gmail.com wrote: On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer stephen.fey...@btinternet.com wrote: When we design databases, invariably, normally we design the queries at the same time. Well this may be true to an extent well implemented models have the ability to answer questions (queries) the original designer never thought of or that were not important at the time. As for the rest - invest in a good graphical query builder (or write one if the existing choices are insufficient). David J. -- Kind regards Stephen Feyrer
Re: [GENERAL] Foreign Keys as first class citizens at design time?
On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
adrian.kla...@aklaver.com wrote:
On 08/13/2015 05:03 PM, Stephen Feyrer wrote:
Hi,
This is probably not an original question merely one which I haven't
been able to find an answer for.
Basically, the question is why is there not an equivalent foreign key
concept to match the primary key we all already know an love?
How this would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.
Therefore as an example:
{system:{primary-key:1,child-key:,date:20150421,directory-name:Bucket
List,user-attribute:bucket.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam
Alternatively linking two user tables - profiles and contacts
profiles
PK-profiles
user-name
real-name
age
gender
region
contacts
PK-contacts
FK-profiles
phone
email
icq
home-page
Getting the user-name and email would look something like:
SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN
When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.
The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily
coded.
To me this looks like a good idea.
What happens if you have more then one child table with the same field?
So:
contacts
FK-profiles
email
vendors
FK-profiles
email
In that case the result table would look something like:
SELECT email FROM profiles WITH-IMPLICIT-JOIN
'contacts-email','vendors-email'
Or to follow a reverse semantic:
SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)
This would give you
'age','region','email'
Whereas:
For a simple vendors table which might look like:
brand
market
email
rating
SELECT brand, region, email FROM vendors WITH-IMPLICIT-JOIN (like a right
join)
'brand','region','email'
One point I would like to make clear, is that the foreign key linking
should be a design choice.
--
Kind regards
Stephen Feyrer
--
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 as first class citizens at design time?
On 08/13/2015 05:03 PM, Stephen Feyrer wrote:
Hi,
This is probably not an original question merely one which I haven't
been able to find an answer for.
Basically, the question is why is there not an equivalent foreign key
concept to match the primary key we all already know an love?
How this would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.
Therefore as an example:
{system:{primary-key:1,child-key:,date:20150421,directory-name:Bucket
List,user-attribute:bucket.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam
Alternatively linking two user tables - profiles and contacts
profiles
PK-profiles
user-name
real-name
age
gender
region
contacts
PK-contacts
FK-profiles
phone
email
icq
home-page
Getting the user-name and email would look something like:
SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN
When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.
The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily coded.
To me this looks like a good idea.
What happens if you have more then one child table with the same field?
So:
contacts
FK-profiles
email
vendors
FK-profiles
email
--
Kind regards
Stephen Feyrer
--
Adrian Klaver
adrian.kla...@aklaver.com
--
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 as first class citizens at design time?
On 08/13/2015 05:40 PM, Stephen Feyrer wrote:
On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
adrian.kla...@aklaver.com wrote:
On 08/13/2015 05:03 PM, Stephen Feyrer wrote:
Hi,
This is probably not an original question merely one which I haven't
been able to find an answer for.
Basically, the question is why is there not an equivalent foreign key
concept to match the primary key we all already know an love?
How this would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.
Therefore as an example:
{system:{primary-key:1,child-key:,date:20150421,directory-name:Bucket
List,user-attribute:bucket.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam
Alternatively linking two user tables - profiles and contacts
profiles
PK-profiles
user-name
real-name
age
gender
region
contacts
PK-contacts
FK-profiles
phone
email
icq
home-page
Getting the user-name and email would look something like:
SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN
When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.
The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily
coded.
To me this looks like a good idea.
What happens if you have more then one child table with the same field?
So:
contacts
FK-profiles
email
vendors
FK-profiles
email
In that case the result table would look something like:
SELECT email FROM profiles WITH-IMPLICIT-JOIN
'contacts-email','vendors-email'
So what if you want to use a different alias?
What if you only wanted the contacts email and not the vendors?
I see the example below, but now you are changing direction for what I
consider no good reason.
Or to follow a reverse semantic:
SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)
The problem that I see is the current method is self-documenting whereas
implicit joins means you have to 'know' what is implied. This means some
other mechanism to discover what is implied. Seems more complicated then
the present situation.
This would give you
'age','region','email'
Whereas:
For a simple vendors table which might look like:
brand
market
email
rating
SELECT brand, region, email FROM vendors WITH-IMPLICIT-JOIN (like a
right join)
'brand','region','email'
One point I would like to make clear, is that the foreign key linking
should be a design choice.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
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 as first class citizens at design time?
On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer stephen.fey...@btinternet.com wrote: When we design databases, invariably, normally we design the queries at the same time. Well this may be true to an extent well implemented models have the ability to answer questions (queries) the original designer never thought of or that were not important at the time. As for the rest - invest in a good graphical query builder (or write one if the existing choices are insufficient). David J.
Re: [GENERAL] Foreign Keys as first class citizens at design time?
On Fri, 14 Aug 2015 01:58:29 +0100, Adrian Klaver
adrian.kla...@aklaver.com wrote:
On 08/13/2015 05:40 PM, Stephen Feyrer wrote:
On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
adrian.kla...@aklaver.com wrote:
On 08/13/2015 05:03 PM, Stephen Feyrer wrote:
Hi,
This is probably not an original question merely one which I haven't
been able to find an answer for.
Basically, the question is why is there not an equivalent foreign key
concept to match the primary key we all already know an love?
How this would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.
Therefore as an example:
{system:{primary-key:1,child-key:,date:20150421,directory-name:Bucket
List,user-attribute:bucket.l...@example.com},
{primary-key:2,child-key:,date:20150421,directory-name:Supernova,user-attribute:supern...@example.com},
{primary-key:3,child-key:5,date:20150422,directory-name:Transactional,user-attribute:transactio...@transaction.org},
{primary-key:4,child-key:,date:20150503,directory-name:Spam,user-attribute:allmys...@lovesspam.com},
{primary-key:5,child-key:,date:20150506,directory-name:Relational,user-attribute:relatio...@transaction.org}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam
Alternatively linking two user tables - profiles and contacts
profiles
PK-profiles
user-name
real-name
age
gender
region
contacts
PK-contacts
FK-profiles
phone
email
icq
home-page
Getting the user-name and email would look something like:
SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN
When building our databases we already put a lot of work in
normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with
all
that referential integrity in mind.
The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily
coded.
To me this looks like a good idea.
What happens if you have more then one child table with the same field?
So:
contacts
FK-profiles
email
vendors
FK-profiles
email
In that case the result table would look something like:
SELECT email FROM profiles WITH-IMPLICIT-JOIN
'contacts-email','vendors-email'
So what if you want to use a different alias?
That is a good question, the point of making foreign keys links into
another table is an attempt to reduce verbosity. Admittedly with that
reduction you can lose expressiveness. Given that you would know the
semantics of the naming scheme you could use:
SELECT contacts-email AS Econtacts, vendor-email AS 'Evendor' FROM
profiles WITH-IMPLICIT-JOIN
This syntax is not far removed from the regular syntax anyway. In other
words, if I'd thought to use the tablename.field nomenclature in the first
place you probably wouldn't have asked that question (I think). The
regular method should not magically disappear just because you've got a
new tool in your box of tricks.
SELECT contacts.email AS Econtacts other fields AS Econtacts FROM
profies JOIN contacts...
What if you only wanted the contacts email and not the vendors?
As I see it, there are two possible ways this might work example would
give the contacts email only.
SELECT contacts-email FROM profiles WITH-IMPLICIT-JOIN
or
SELECT contacts-email AS Econtacts, vendor-email AS '' FROM profiles
WITH-IMPLICIT-JOIN
I prefer the former example as it is less verbose but retains the
specificity. Some might argue the latter is more readable others that it
is more confusing, I'd say both.
I see the example below, but now you are changing direction for what I
consider no good reason.
No, the reason for the change of direction is that there is a join
happening, in the case of the implicit join the table holding the foreign
key is to the right so it would look like a right join.
Or to follow a reverse semantic:
SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)
The problem that I see is the current method is self-documenting whereas
implicit joins means you have to 'know' what is implied. This means some
other mechanism to discover what is implied. Seems more complicated then
the present situation.
Once you understand the semantics (should they ever exist beyond this
discussion) of this system it will be self documenting also. You'll be
able to see that a foreign key one table is the primary key in another.
Admittedly this wasn't well represented in my example but that was my
first attempt at
Re: [GENERAL] foreign keys to foreign tables
Thanks Will! I had been considering setting up replication (using SymmetricDS - which we already use between other databases in our environment), but decided for this one check it was too much trouble. I may change my mind on that point again after all if I end up with a lot of dependencies like this or run into performance issues. On Mon, Jun 22, 2015 at 1:06 PM, William Dunn dunn...@gmail.com wrote: Hello Rick, As I understand it you are correct. Oracle/DB2/Postgres and I think the SQL Standards to not implement constraints against tables on foreign servers. Although it would be possible to develop the DBMS to handle such constraints in a heterogeneous distributed environment it would be unwise because of the poor performance and reliability of data sent over networks so DBMSs do not implement it. You would, as you suspected, have to use stored procedures to emulate some of the functionality of a foreign key but definitely think twice about the performance bottlenecks you would introduce. A more clever thing to do is use Slony, BDR, or triggers to replicate the foreign table and create the constraint against the local copy. In some other DBMSs the clever thing to do is create a materialized view and constraints against the materialized view (which achieves the same) but Postgres does not yet support such constraints against materialized views. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Mon, Jun 22, 2015 at 12:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rick Otten rottenwindf...@gmail.com writes: Hello pgsql-general, I'd like to set up a foreign key constraint to a foreign table from a local table. ie, I have a column in a local table that I'd like to ensure has a value in the foreign table. alter mytable add column some_column_id uuid references myforeigntable(some_column_id) ; Unfortunately I get a not a table error when I try this. ERROR: referenced relation myforeigntable is not a table I'm thinking I'll have to write a function that checks for existance of the ids in the foreign table, and then put a CHECK constraint on using that function, but I thought I'd as first if there was a better way. What's going to happen when the foreign server decides to delete some rows from its table? regards, tom lane -- 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 to foreign tables
Rick Otten rottenwindf...@gmail.com writes: Hello pgsql-general, I'd like to set up a foreign key constraint to a foreign table from a local table. ie, I have a column in a local table that I'd like to ensure has a value in the foreign table. alter mytable add column some_column_id uuid references myforeigntable(some_column_id) ; Unfortunately I get a not a table error when I try this. ERROR: referenced relation myforeigntable is not a table I'm thinking I'll have to write a function that checks for existance of the ids in the foreign table, and then put a CHECK constraint on using that function, but I thought I'd as first if there was a better way. What's going to happen when the foreign server decides to delete some rows from its table? regards, tom lane -- 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 to foreign tables
Hello Rick, As I understand it you are correct. Oracle/DB2/Postgres and I think the SQL Standards to not implement constraints against tables on foreign servers. Although it would be possible to develop the DBMS to handle such constraints in a heterogeneous distributed environment it would be unwise because of the poor performance and reliability of data sent over networks so DBMSs do not implement it. You would, as you suspected, have to use stored procedures to emulate some of the functionality of a foreign key but definitely think twice about the performance bottlenecks you would introduce. A more clever thing to do is use Slony, BDR, or triggers to replicate the foreign table and create the constraint against the local copy. In some other DBMSs the clever thing to do is create a materialized view and constraints against the materialized view (which achieves the same) but Postgres does not yet support such constraints against materialized views. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Mon, Jun 22, 2015 at 12:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rick Otten rottenwindf...@gmail.com writes: Hello pgsql-general, I'd like to set up a foreign key constraint to a foreign table from a local table. ie, I have a column in a local table that I'd like to ensure has a value in the foreign table. alter mytable add column some_column_id uuid references myforeigntable(some_column_id) ; Unfortunately I get a not a table error when I try this. ERROR: referenced relation myforeigntable is not a table I'm thinking I'll have to write a function that checks for existance of the ids in the foreign table, and then put a CHECK constraint on using that function, but I thought I'd as first if there was a better way. What's going to happen when the foreign server decides to delete some rows from its table? regards, tom lane -- 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 to foreign tables
Obviously the server will be able to delete those rows because it will be completely unaware of this dependency. So it is the implied reverse constraint (of sorts) that can't be enforced which makes an FK based definition impossible. For my particular use case, this shouldn't be a problem. The foreign table is a reference table which does not typically experience deletes. I'll go with a function for now. Since this happens to be a PostgreSQL-PostgreSQL mapping I'll also consider mapping my table back the other way and then putting a delete trigger on the foreign reference table to either cascade or stop the delete once I decide which I'd rather do. Thanks for the help! On Mon, Jun 22, 2015 at 12:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rick Otten rottenwindf...@gmail.com writes: Hello pgsql-general, I'd like to set up a foreign key constraint to a foreign table from a local table. ie, I have a column in a local table that I'd like to ensure has a value in the foreign table. alter mytable add column some_column_id uuid references myforeigntable(some_column_id) ; Unfortunately I get a not a table error when I try this. ERROR: referenced relation myforeigntable is not a table I'm thinking I'll have to write a function that checks for existance of the ids in the foreign table, and then put a CHECK constraint on using that function, but I thought I'd as first if there was a better way. What's going to happen when the foreign server decides to delete some rows from its table? regards, tom lane
Re: [GENERAL] Foreign keys
On 12/18/2013 11:02 AM, Dean Gibson (DB Administrator) wrote: I have general question about FOREIGN KEYs: 1. Suppose I have table A with primary key X, and another table B with field Y. 2. When I 'ALTER TABLE B ADD FOREIGN KEY( Y ) REFERENCES A ON UPDATE CASCADE ON DELETE CASCADE', that clearly spends some time building a separate index. Since there is already a unique index on X, presumably (?) the index being built is on Y. 3. However, the PostgreSQL documentation seems to indicate that it's a good idea to also separately create an index on Y. 4. Why, and why is the FOREIGN KEY index different from the ones on X and Y in any way but trivial? 5. If I need the separate index on Y, should it be built before or after the FOREIGN KEY constraint? -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. Perhaps you wanthttp://www.postgresql.org/docs/9.3/static/sql-altertable.html add table_constraint_using_index
Re: [GENERAL] Foreign keys
Dean Gibson (DB Administrator) postgre...@ultimeth.com wrote: I have general question about FOREIGN KEYs: 1. Suppose I have table A with primary key X, and another table B with field Y. 2. When I 'ALTER TABLE B ADD FOREIGN KEY( Y ) REFERENCES A ON UPDATE CASCADE ON DELETE CASCADE', that clearly spends some time building a separate index. No it doesn't. If you are observing activity at that time, it is probably from validating that the constraint is initially valid. 3. However, the PostgreSQL documentation seems to indicate that it's a good idea to also separately create an index on Y. It *often* is, but there are various reasons you might not want such an index, which is why its creation is not automatic. 5. If I need the separate index on Y, should it be built before or after the FOREIGN KEY constraint? In some cases it may allow faster initial validation of the constraint; if I wanted the index I would probably build it before adding the constraint. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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
On 2013-12-18 10:41, Kevin Grittner wrote: Dean Gibson (DB Administrator) postgre...@ultimeth.com wrote: ... that clearly spends some time building a separate index. No it doesn't. If you are observing activity at that time, it is probably from validating that the constraint is initially valid. Ah ha! That's what's consuming the time! Thanks! 5. If I need the separate index on Y, should it be built before or after the FOREIGN KEY constraint? In some cases it may allow faster initial validation of the constraint; if I wanted the index I would probably build it before adding the constraint. Again, that's what I needed to know! Thanks again! -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. -- 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: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
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
Re: [GENERAL] Foreign Keys and Deadlocks
Hi David, On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: So, aside from removing the PKs do i have any other options? Sure you have: order the inserts by primary key inside each transaction. Then you will not get deadlocks, but inserting the same key again will fail of course (but that's the purpose of the primary key, right ?) Ordering inserts/updates by the columns which cause locks is the first thing to do to avoid dead-locks... Cheers, Csaba. -- 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 and Deadlocks
On Thu, Nov 10, 2011 at 09:09:06AM +0100, Csaba Nagy wrote: - Hi David, - - On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: - So, aside from removing the PKs do i have any other options? - - Sure you have: order the inserts by primary key inside each transaction. - Then you will not get deadlocks, but inserting the same key again will - fail of course (but that's the purpose of the primary key, right ?) - - Ordering inserts/updates by the columns which cause locks is the first - thing to do to avoid dead-locks... - - Cheers, - Csaba. ah, hmmm. i'm not sure if that's an option based on how the program works but I'll forward the suggestion onto the devleoper. 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 and Deadlocks
Excerpts from David Kerr's message of vie nov 04 13:01:29 -0300 2011: I did more digging and found some good discussions on the subject in general, but most of the examples out there contain explicit updates (which is why i was confused) but it looks like it's being addressed. http://justatheory.com/computers/databases/postgresql/fk-locks-project.html http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg158205.html http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ Attached is the script to reproduce it with only inserts (for postarities sake) Actually, your script as presented has nothing to do with foreign keys. The cause for the lock and the deadlock is not in the tuple lock code, but in the primary key uniqueness check. You can duplicate your issue with a single one-column table: Session one: alvherre=# create table pk (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «pk_pkey» para la tabla «pk» CREATE TABLE alvherre=# begin; BEGIN alvherre=# insert into pk values (1); INSERT 0 1 Session two: alvherre=# begin; BEGIN alvherre=# insert into pk values (2); INSERT 0 1 alvherre=# insert into pk values (1); blocks Now go back to session one and alvherre=# insert into pk values (2); ERROR: se ha detectado un deadlock DETALLE: El proceso 17430 espera ShareLock en transacción 710; bloqueado por proceso 17495. El proceso 17495 espera ShareLock en transacción 709; bloqueado por proceso 17430. SUGERENCIA: Vea el registro del servidor para obtener detalles de las consultas. This case is not helped by the patch I'm working on. As far as I can see, if you got rid of the PK in table a in your example script, things should work just fine. There is no way to cause FK-induced deadlocks with only inserts in 8.1 and later. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 and Deadlocks
On Wed, Nov 09, 2011 at 11:11:23AM -0300, Alvaro Herrera wrote: - - Excerpts from David Kerr's message of vie nov 04 13:01:29 -0300 2011: - - I did more digging and found some good discussions on the subject in general, but - most of the examples out there contain explicit updates (which is why i was confused) - but it looks like it's being addressed. - - - http://justatheory.com/computers/databases/postgresql/fk-locks-project.html - http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg158205.html - http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ - - Attached is the script to reproduce it with only inserts (for postarities sake) - - Actually, your script as presented has nothing to do with foreign keys. - The cause for the lock and the deadlock is not in the tuple lock code, - but in the primary key uniqueness check. You can duplicate your issue - with a single one-column table: - - Session one: - - alvherre=# create table pk (a int primary key); - NOTICE: CREATE TABLE / PRIMARY KEY creará el Ãndice implÃcito «pk_pkey» para la tabla «pk» - CREATE TABLE - alvherre=# begin; - BEGIN - alvherre=# insert into pk values (1); - INSERT 0 1 - - Session two: - - alvherre=# begin; - BEGIN - alvherre=# insert into pk values (2); - INSERT 0 1 - alvherre=# insert into pk values (1); - blocks - - Now go back to session one and - - alvherre=# insert into pk values (2); - ERROR: se ha detectado un deadlock - DETALLE: El proceso 17430 espera ShareLock en transacción 710; bloqueado por proceso 17495. - El proceso 17495 espera ShareLock en transacción 709; bloqueado por proceso 17430. - SUGERENCIA: Vea el registro del servidor para obtener detalles de las consultas. - - - This case is not helped by the patch I'm working on. As far as I can - see, if you got rid of the PK in table a in your example script, things - should work just fine. There is no way to cause FK-induced deadlocks - with only inserts in 8.1 and later. Ok, well that's good to know. I had planned on testing my script w/o FKs but it slipped my mind. So, aside from removing the PKs do i have any other options? (we use Hibernate and i don't think that I'll be able to removet he Primary Keys, and a serial primary key probably isn't great for this table because it's sort of a staging area (so it gets written to and wiped out frequently) Would you consider this a problem in Pg or is it unavoidable? Thanks Dave -- 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 and Deadlocks
Excerpts from David Kerr's message of mié nov 09 14:52:01 -0300 2011: On Wed, Nov 09, 2011 at 11:11:23AM -0300, Alvaro Herrera wrote: - This case is not helped by the patch I'm working on. As far as I can - see, if you got rid of the PK in table a in your example script, things - should work just fine. There is no way to cause FK-induced deadlocks - with only inserts in 8.1 and later. Ok, well that's good to know. I had planned on testing my script w/o FKs but it slipped my mind. So, aside from removing the PKs do i have any other options? (we use Hibernate and i don't think that I'll be able to removet he Primary Keys, and a serial primary key probably isn't great for this table because it's sort of a staging area (so it gets written to and wiped out frequently) Not sure about that. Would you consider this a problem in Pg or is it unavoidable? Well, you have to ensure that only one copy of two or more concurrent insertions of a given PK value will survive. Otherwise the unique constraint would be violated. This is currently implemented with sleeps in the second inserter, which waits until the first transaction is closed. Note that there is some code to support deferred uniqueness checks, which might help, but I don't know if it can be applied to primary keys. I'd recommend reading the manual on that subject. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 and Deadlocks
Hi David, On Thu, 2011-11-03 at 15:30 -0700, David Kerr wrote: I suspect that it has to be a transaction, and that further up in the TX is an update to one of the reference tables in each TX. This is your cause - updating the referenced table in the same transaction. That will want an exclusive lock on the row, but the shared lock taken by the foreign key check (in another process) is conflicting, and will deadlock when the other process will also want to update some row in the referenced table which is locked by a foreign key check in this process. While the lock on the referenced row was changed to be a shared lock instead of an exclusive lock as in older postgres versions (see http://archives.postgresql.org/pgsql-general/2002-11/msg00397.php for the original problem, which is relaxed now), the lock is still too strong and the deadlock problem remains. A solution is not trivial at all, and involves only locking the row for changes of the referenced columns (which postgres can't do currently). While getting rid of the foreign key will solve your problem, I think it's not the best solution - you can perhaps design a way to not update the referenced tables in the same transaction. Here we adopted a different solution - we run a patched postgres which skips that lock altogether, which means a partially broken foreign key code which mostly works but can leave orphans. I will not recommend to do that though - the reasons we did it that way is that it was the path of least resistance as the application was also running on other DBs (which were the primary DB at that time) and there was no way to make extensive changes to the application code. If I were to change the code, I would have separated the updated fields from the parent table to yet another child table, and have the parent table never updated. That will still have some potential for deadlock (if you don't order the inserts/updates properly) but much less. Cheers, Csaba. -- 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 and Deadlocks
On Thu, Nov 03, 2011 at 03:30:20PM -0700, David Kerr wrote: - Howdy, - - We have a process that's deadlocking frequently. It's basically multiple threads inserting data into a single table. - - That table has FK constraints to 3 other tables. - - I understand how an FK check will cause a sharelock to be acquired on the reference table and in some instances that - leads to or at least participates in a deadlock. - - I don't think that's the case here, (or at least not the entire case) but I could use some assistance in helping - to convince my developers of that ;). They'd like to just remove the FK and be done with it. [snip] So it appears that I'm the big dummy, and that you can deadlock with just inserts. I did more digging and found some good discussions on the subject in general, but most of the examples out there contain explicit updates (which is why i was confused) but it looks like it's being addressed. http://justatheory.com/computers/databases/postgresql/fk-locks-project.html http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg158205.html http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ Attached is the script to reproduce it with only inserts (for postarities sake) drop table a; drop table b; drop table c; drop table d; create table b ( bref int, description text); alter table b add primary key (bref); create table c ( cref int, description text); alter table c add primary key (cref); create table d ( dref int, description text); alter table d add primary key (dref); create table a ( bref int, cref int, dref int, description text); alter table a add primary key (bref, cref); alter table a add foreign key (bref) REFERENCES b(bref); alter table a add foreign key (cref) REFERENCES c(cref); alter table a add foreign key (dref) REFERENCES d(dref); insert into b values (1,'hello'); insert into b values (2,'hello2'); insert into b values (3,'hello3'); insert into b values (4,'hello4'); insert into c values (1,'hello'); insert into c values (2,'hello2'); insert into c values (3,'hello3'); insert into c values (4,'hello4'); insert into d values (1,'hello'); insert into d values (2,'hello2'); insert into d values (3,'hello3'); insert into d values (4,'hello4'); Fire up 2 psqls #SESSION1 ## STEP1 begin; insert into a values (1,1,1,'hello'); ##STEP3 insert into a values (1,2,1,'hello2'); #SESSION2 ## STEP2 begin; insert into a values (1,2,1,'hello2'); ## STEP4 insert into a values (1,1,1,'hello'); You'll get: ERROR: deadlock detected DETAIL: Process 8382 waits for ShareLock on transaction 7222455; blocked by process 6981. Process 6981 waits for ShareLock on transaction 7222456; blocked by process 8382. HINT: See server log for query details -- 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 and inheritance problem
On 12/08/10 18.59, Edoardo Panfili wrote: hi, I am in some trouble with my tables defined using inheritance, This is a semplified test case: --- create table sub1( name1 text) inherits(father); create table sub2( name2 text) inherits(father); create table other (description text, id integer); -- I know, the contraints is not checked in sub1 and sub2 ALTER TABLE father ADD UNIQUE(id); ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id); insert into sub1 (id,name1) VALUES(1,'row1 in sub1'); insert into sub2 (id,name2) VALUES(2,'row1 in sub2'); select * from father; id 1 2 I can't insert data in other table: - test=# insert into other(id,description) VALUES(1,'test'); ERROR: insert or update on table other violates foreign key constraint other_id_fkey DETAIL: Key (id)=(1) is not present in table father. - Is there a way to do this thing? Or I must remove the foreign key constraint? trigger solution, it seems ok but I am still searching for a declarative one. CREATE OR REPLACE FUNCTION insert_veto() RETURNS trigger AS $BODY$ DECLARE present boolean; BEGIN present := exists (select * from father where id=NEW.id) ; IF present THEN return NULL; ELSE RETURN NEW; END IF; END $BODY$ LANGUAGE 'plpgsql' CREATE TRIGGER veto BEFORE INSERT OR UPDATE ON other FOR EACH ROW EXECUTE PROCEDURE insert_veto(); Edoardo -- 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 and inheritance problem
Edoardo Panfili edoa...@aspix.it writes: On 12/08/10 18.59, Edoardo Panfili wrote: I am in some trouble with my tables defined using inheritance, No, foreign keys do not play very nicely with inheritance. There is some explanation in the manual, in the Caveats subsection under Inheritance --- see bottom of this page: http://www.postgresql.org/docs/8.4/static/ddl-inherit.html regards, tom lane -- 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 and inheritance problem
On 12/08/10 20.44, Tom Lane wrote: Edoardo Panfiliedoa...@aspix.it writes: On 12/08/10 18.59, Edoardo Panfili wrote: I am in some trouble with my tables defined using inheritance, No, foreign keys do not play very nicely with inheritance. There is some explanation in the manual, in the Caveats subsection under Inheritance --- see bottom of this page: http://www.postgresql.org/docs/8.4/static/ddl-inherit.html thank you, I must read with more attenction the page. I stop the search for a declarative solution, triggers or no check. thank you again Edoardo -- 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 and permissions oddity
On 07/08/10 01:13, Joshua Tolley wrote: Is there some justification for this behavior that I should know already? It seemed awfully strange when some folkds here stumbled on it: [snip] The key point seems to be that the owner of the referenced table has no permissions on the table, although the referencing user does. Presumably the underlying trigger functions are executing as the owner of the table. This would make sense in the (more common) case that you want to reference a table you don't necessarily have full read access for (e.g. member-id vs the whole row including address/phone). You should be able to track the table's OID from pg_class through to tgrelid on pg_trigger and then tdfoid to the relevant OIDs in pg_proc. The functions are all named as RI_FKey_xxx. Hmm - not sure if they execute as the table owner or the creator of the constraint. You could justify either, but of course they're frequently the same (as in your case). -- Richard Huxton Archonet Ltd -- 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 and permissions oddity
On Sat, Aug 07, 2010 at 08:34:12AM +0100, Richard Huxton wrote: On 07/08/10 01:13, Joshua Tolley wrote: Is there some justification for this behavior that I should know already? It seemed awfully strange when some folkds here stumbled on it: [snip] The key point seems to be that the owner of the referenced table has no permissions on the table, although the referencing user does. Presumably the underlying trigger functions are executing as the owner of the table. This would make sense in the (more common) case that you want to reference a table you don't necessarily have full read access for (e.g. member-id vs the whole row including address/phone). Yeah, that appears to be what's happening, based on the code. It's certainly confusing to look at, and I'm not sure it couldn't be described a bug. I'll continue to ponder that. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] Foreign keys causing conflicts leading toserialization failures
Tom Lane wrote: This is what I am wondering. Whether it is done this way due to expecation/standard, or as an implementation side effect. In the latter case it is fixable. I don't see how this could break a standard. Actually, I think it does, because we went to great lengths to cause this case to error out. It would be much simpler, code-wise, if the RI checks just always used a current snapshot and didn't worry about whether serializability had been violated. (Albe's description of the implementation is largely fiction, but the conclusion is accurate: we throw error if the referenced PK row has been updated since the serializable transaction started. The exact nature of the update is not considered.) I am aware that I know nothing of the implementation and only can describe the behaviour... Of course a serializable transaction cannot just use the current index entry for verifying referential integrity, because then it might not behave consistently with the transaction snapshot. What I mean is: if the serializable transaction went out of its way to check if the update it wants to make is both consistent with its snapshot and the current index row, it should not violate anything to allow that update. The index entry would not be changed in that case. Yours, Laurenz Albe -- 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 causing conflicts leading to serialization failures
Peter Schuller wrote: Using PostgreSQL 8.2, I have atable one of whose columns reference a column in othertable. I see serialization failures as a result of *inserts* to atable in the context of: 'SELECT 1 FROM ONLY othertable x WHERE otherid = $1 FOR SHARE OF x ' in 'INSERT INTO atable (otherid, col2, col3) VALUES (.., .., ..)' A SELECT ... FROM othertable ... FOR SHARE won't conflict with a concurrent update on atable. Do I guess right that there was also an UPDATE on the row in othertable? You may have been misled by a message like: ERROR: could not serialize access due to concurrent update CONTEXT: SQL statement SELECT 1 FROM ONLY othertable x WHERE otherid = $1 FOR SHARE OF x This message will be displayed although the statement that causes the conflict is actually the UPDATE, perhaps because this was the first statement to acquire a lock on that row in this transaction. What most likely happens is the following: Serializable transaction 2 starts and SELECTs something. Transaction 1 starts and gets a RowShareLock on a row of othertable with the SELECT ... FOR SHARE Transaction 1 UPDATEs the row and now holds a RowExclusiveLock on the table row and the index row as well. The latter is necessary because that not yet committed UPDATE has also changed the index (even if the indexed columns did not change, the index will point to a new row now). Transaction 1 COMMITs. A new table row and a new index row are visible. Transaction 2 now issues an INSERT on atable. This requires a RowShareLock on the index row of the index on othertable that is referenced by the foreign key constraint. But the corresponding index row has changed since the transaction began, hence the error. Transaction 2 needs the index entry to verify that the foreign key constraint is satisfied. It cannot perform the check on the old version of the index row as this might introduce inconsistencies. Being serializable, it must not use the new index entry. One could argue that, as long as the old index entry and the new index entry have the same values, the transaction could safely proceed. I guess it is for ease of implementation, design or performance reasons that this is not done. Your idea of cell level locking will probably not work with PostgreSQL: since any change in a data row will cause a new row to be created, there would be no advantage. Yours, Laurenz Albe -- 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 causing conflicts leading to serialization failures
A SELECT ... FROM othertable ... FOR SHARE won't conflict with a concurrent update on atable. Do I guess right that there was also an UPDATE on the row in othertable? Yes, that was what I meant to convey. Sorry if I was not clear. The point was that an INSERT to atable conflicted with an update to othertable, as a result of the foreign key constraint. This message will be displayed although the statement that causes the conflict is actually the UPDATE, perhaps because this was the first statement to acquire a lock on that row in this transaction. I surmised from the SELECT that it had to be an internally generated SELECT used to enforce referential integrity, because the only statements made in the transaction in question were three INSERT:s. (So in this particular case isolation could simply be dropped to a lower level, but I find this interesting generally because I don't like generating conflicts that are not real conflicts in the application domain. And this is an actual implicit conflict at the SQL level, which is even more subtle than the more typical cases like value increments expressed as updates. As I said in this case the isolation be dropped, but in other cases it might trigger a desire to drop the enforced referential integrity instead - which is not good.) [snip] Transaction 2 now issues an INSERT on atable. This requires a RowShareLock on the index row of the index on othertable that is referenced by the foreign key constraint. But the corresponding index row has changed since the transaction began, hence the error. Yes, this matches my theory. Transaction 2 needs the index entry to verify that the foreign key constraint is satisfied. It cannot perform the check on the old version of the index row as this might introduce inconsistencies. Being serializable, it must not use the new index entry. Yes. One could argue that, as long as the old index entry and the new index entry have the same values, the transaction could safely proceed. Yes. :) Or alternatively, the fact that it was never updated could be tracked. I guess you might argue that if one, for example, deleted the row and re-created one with another id, that this would in fact break referential integrity. Same for updating the relevant column. But barring implementation reasons, it seems clear that if the row was not dropped and the relevant column was not touched, the ideal implementation would allow the INSERT to complete even in a serializable transaction. I guess it is for ease of implementation, design or performance reasons that this is not done. This is what I am wondering. Whether it is done this way due to expecation/standard, or as an implementation side effect. In the latter case it is fixable. Your idea of cell level locking will probably not work with PostgreSQL: since any change in a data row will cause a new row to be created, there would be no advantage. I didn't think of that. I can certainly see that update-by-tuple-duplication makes it difficult to implement this case optimally. Thanks, -- / Peter Schuller PGP userID: 0xE9758B7D or 'Peter Schuller [EMAIL PROTECTED]' Key retrieval: Send an E-Mail to [EMAIL PROTECTED] E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org pgpW2l4EpI6xP.pgp Description: PGP signature
Re: [GENERAL] Foreign keys causing conflicts leading toserialization failures
Peter Schuller wrote: [about a serialization error caused by a foreign key constraint] Transaction 2 now issues an INSERT on atable. This requires a RowShareLock on the index row of the index on othertable that is referenced by the foreign key constraint. But the corresponding index row has changed since the transaction began, hence the error. Transaction 2 needs the index entry to verify that the foreign key constraint is satisfied. It cannot perform the check on the old version of the index row as this might introduce inconsistencies. Being serializable, it must not use the new index entry. One could argue that, as long as the old index entry and the new index entry have the same values, the transaction could safely proceed. Yes. :) Or alternatively, the fact that it was never updated could be tracked. I guess you might argue that if one, for example, deleted the row and re-created one with another id, that this would in fact break referential integrity. Same for updating the relevant column. Well, the index entry *was* changed because it now points somewhere else in table othertable. But barring implementation reasons, it seems clear that if the row was not dropped and the relevant column was not touched, the ideal implementation would allow the INSERT to complete even in a serializable transaction. I guess it is for ease of implementation, design or performance reasons that this is not done. This is what I am wondering. Whether it is done this way due to expecation/standard, or as an implementation side effect. In the latter case it is fixable. I don't see how this could break a standard. Maybe somebody who knows more than me knows the answer :^) Yours, Laurenz Albe -- 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 causing conflicts leading toserialization failures
Albe Laurenz [EMAIL PROTECTED] writes: Peter Schuller wrote: This is what I am wondering. Whether it is done this way due to expecation/standard, or as an implementation side effect. In the latter case it is fixable. I don't see how this could break a standard. Actually, I think it does, because we went to great lengths to cause this case to error out. It would be much simpler, code-wise, if the RI checks just always used a current snapshot and didn't worry about whether serializability had been violated. (Albe's description of the implementation is largely fiction, but the conclusion is accurate: we throw error if the referenced PK row has been updated since the serializable transaction started. The exact nature of the update is not considered.) regards, tom lane -- 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 to inherited tables
On 3/20/08, Erik Jones [EMAIL PROTECTED] wrote: I think he's talking about foreign keys from a partitioned table, i.e. a parent and all of its child tables, to another table. That would, at first, sound simple, but scenarios like this make it tricky as something to be handled automatically in a simple way: Well, I was actually talking about foreign keys /to/ a partitioned table -- table A points to partitioned table B, which has childs C and D. The foreign key will only be checked in table B, and not tables C and D. Say you have table A that references table B. You then partition table A. Say this carries down the references to table B to each child of table A. You then partition table B. How do you know, or rather how does Postgres know, how to change those foreign keys? It's entirely possible that the partitioning scheme on table B doesn't match that of table C. One solution (and, probably the most sane that I can think of) is to NOT explicitly carry the foreign keys down to the child tables and, instead, to have the actual foreign key checks follow inheritance chain. However, with just that most people probably wouldn't want that as that could seriously kill performance of even simple write queries. Following that up with making foreign key checks constraint exclusion aware could help there but, at this point, you can probably see why a sane implementation of this probably wouldn't be considered low hanging fruit. For practical workarounds, you can use triggers on your child tables to implement referential integrity checks customized to your particular setup. Yeah I was thinking about a bunch of triggers too, but was wondering whether there were any other elegant solutions for this. The foreign keys are actually already guaranteed by my application logic, so I'm starting to wonder whether this is becoming more trouble to implement than it's worth. Too bad this isn't supported by PostgreSQL (yet). -- Leon Mergen http://www.solatis.com -- 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 to inherited tables
Leon Mergen wrote:
Hello,
I was wondering, I'm reading that there is no support for foreign keys
to inherited (child) tables -- are there any plans on supporting these
in the (near) future, and/or are there any practical workarounds for
this ?
This has worked well for me:
CREATE TABLE child_table (
...
) INHERITS (parent_table);
ALTER TABLE child_table ALTER COLUMN id SET DEFAULT
nextval('parent_table_id_seq');
CREATE UNIQUE INDEX child_table_pk ON child_table (id);
Note that it's not necessary to declare an id column for the child.
b
--
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 to inherited tables
On Mar 19, 2008, at 10:42 PM, brian wrote:
Leon Mergen wrote:
Hello,
I was wondering, I'm reading that there is no support for foreign
keys
to inherited (child) tables -- are there any plans on supporting
these
in the (near) future, and/or are there any practical workarounds for
this ?
This has worked well for me:
CREATE TABLE child_table (
...
) INHERITS (parent_table);
ALTER TABLE child_table ALTER COLUMN id SET DEFAULT
nextval('parent_table_id_seq');
CREATE UNIQUE INDEX child_table_pk ON child_table (id);
Note that it's not necessary to declare an id column for the child.
I think he's talking about foreign keys from a partitioned table,
i.e. a parent and all of its child tables, to another table. That
would, at first, sound simple, but scenarios like this make it tricky
as something to be handled automatically in a simple way:
Say you have table A that references table B. You then partition
table A. Say this carries down the references to table B to each
child of table A. You then partition table B. How do you know, or
rather how does Postgres know, how to change those foreign keys?
It's entirely possible that the partitioning scheme on table B doesn't
match that of table C.
One solution (and, probably the most sane that I can think of) is to
NOT explicitly carry the foreign keys down to the child tables and,
instead, to have the actual foreign key checks follow inheritance
chain. However, with just that most people probably wouldn't want
that as that could seriously kill performance of even simple write
queries. Following that up with making foreign key checks constraint
exclusion aware could help there but, at this point, you can probably
see why a sane implementation of this probably wouldn't be considered
low hanging fruit.
For practical workarounds, you can use triggers on your child tables
to implement referential integrity checks customized to your
particular setup.
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate market in style.
Visit us online at http://www.myemma.com
--
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 and inheritance
On Mon, 2007-11-19 at 14:36 -0500, Kynn Jones wrote: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? I would probably do something like: CREATE TABLE A (a_id INT PRIMARY KEY, a_attr text); CREATE TABLE B (b_id INT PRIMARY KEY, a_id int references A(a_id) UNIQUE, b_attr text); CREATE TABLE T (t_id INT PRIMARY KEY, a_id int references A(a_id), t_attr text); I can't tell whether you mean that every A has many T or vice versa, but minor modification will make it work in the opposite direction. To look at all A objects, you just look in table A. You can do A NATURAL JOIN T to realize the many-to-one relationship from A to T. You can do A NATURAL JOIN B to see all B objects (which have a_attr since they are a special case of A). This is a normal relational design that is very flexible and doesn't require the PostgreSQL-specific INHERITANCE feature. You don't need to use natrual joins of course, it was just easier for this example. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and inheritance
Kynn Jones wrote: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? It seems that inheritance is precisely what you want. WRT yout table T you should be able to join to B in the same way you would join to A. But perhaps you should give an example of both B T (and maybe A). brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Foreign keys and inheritance
Kynn Jones escribió: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? It would be the right tool if the FKs worked :-( Sadly, they don't. alvherre=# create table foo (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo cCREATE TABLE alvherre=# create table bar (a int not null references foo); CREATE TABLE alvherre=# create table baz () inherits (foo); CREATE TABLE alvherre=# insert into baz values (1); INSERT 0 1 alvherre=# select * from foo; a --- 1 (1 row) alvherre=# insert into bar values (1); ERROR: insert or update on table bar violates foreign key constraint bar_a_fkey DETAIL: Key (a)=(1) is not present in table foo. This is a Postgres shortcoming, but I don't think there's anybody working on fixing it, so don't hold your breath. Uniqueness also fails in inheritance: for example alvherre=# insert into foo values (1); INSERT 0 1 alvherre=# select * from foo; a --- 1 1 (2 rows) (Note that column is the PK) -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ Investigación es lo que hago cuando no sé lo que estoy haciendo (Wernher von Braun) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and inheritance
On Mon, 2007-11-19 at 17:19 -0300, Alvaro Herrera wrote: Kynn Jones escribió: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? It would be the right tool if the FKs worked :-( Sadly, they don't. I don't think it's that bad of a situation. It would be great if PostgreSQL did support keys across tables, but it's not necessary for a good design in his case. The difference between using inheritance and just using multiple tables (like the alternative that I suggested) is the difference between vertically partitioning and horizontally partitioning. Both seem like good choices to me. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] foreign keys and memory consumption
On Tue, 2007-10-09 at 11:28 +0200, Jan Poslusny wrote: pg 8.2.4 on Fedora Core 6 x86-64, mostly default postgres.conf just shared memory buffers increased to 256M. 1GB RAM. I attempt to insert ~200k rows into table in one transaction from psql console, calling stored function of plperlu language, which inserts row by row via spi_exec_prepared. If table contains some foreign key (id_parent int4 not null references this_table(id) on delete cascade), memory allocated for processing child process grows and grows (roughly, 1GB for first 100k rows is consumed) and is not released neither after successful end of transaction nor after Ctrl-C. If table does not contain that foreign key, consumed memory does not grow in this way - all is O.K. I do not know if I am missing something or if it is necessary or if it is memory leak. If somebody will be interesting, I can provide my postgres.conf and I can write script isolating and demonstrating this phenomena. It's a known side effect of the way FKs work currently. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and indexes
2007/6/5, Marc Compte [EMAIL PROTECTED]: Dear list, This might be too basic for a question but I just couldn't find the answer so far. Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? FK is just a constraint, you wil have to create indexes manually if you need them. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign keys and indexes
On þri, 2007-06-05 at 11:49 +0200, Marc Compte wrote: Does PostgreSQL create an implicit index also for foreign keys? no or must I create it explicitly? if you want one, yes. not everyone wants an index on all their foreign keys, but they can be useful in some circumstances. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Foreign keys and indexes
On Tue, Jun 05, 2007 at 11:49:20AM +0200, Marc Compte wrote: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? PostgreSQL doesn't create an index on the referencing column(s) of a foreign key constraint; if you want an index then you'll need to create it yourself. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign keys and indexes
Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? No, you foreign keys are not automatically indexed. They only way they would be is if the FK is part of a composite unique or primary key. So you will probably have to create your one indexes on FKs. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Foreign keys and indexes
am Tue, dem 05.06.2007, um 11:49:20 +0200 mailte Marc Compte folgendes: Dear list, This might be too basic for a question but I just couldn't find the answer so far. Does PostgreSQL create an implicit index also for foreign keys? No, only for primary keys to enforce the uniqueness. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign keys and indexes
Marc Compte [EMAIL PROTECTED] writes: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? It won't allow you to create a foreign key that points to a column without a unique index on it. postgres=# create table b (i integer references a(i)); ERROR: there is no unique constraint matching given keys for referenced table a However if you ever update or delete the referenced records then it also helps performance to have an index on the referencing column which Postgres doesn't enforce. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Foreign keys and indexes
Thanks to everyone for the prompt reply :) Good thing about answers is when they raise up new questiosn, so you can keep on learning all the time. This one answer, for instance, brings me another question. Does having a composite primary mean the system will create an individual index on each of the fields? or is the index created on the composition only? For instance, in the implementation of a N:M relationship, declaring the primary as (foreign1, foreign2) will create two indexes? or just one? Thanks again Marc Compte En/na Richard Broersma Jr ha escrit: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? No, you foreign keys are not automatically indexed. They only way they would be is if the FK is part of a composite unique or primary key. So you will probably have to create your one indexes on FKs. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and indexes
Marc Compte wrote: Thanks to everyone for the prompt reply :) Good thing about answers is when they raise up new questiosn, so you can keep on learning all the time. This one answer, for instance, brings me another question. Does having a composite primary mean the system will create an individual index on each of the fields? or is the index created on the composition only? For instance, in the implementation of a N:M relationship, declaring the primary as (foreign1, foreign2) will create two indexes? or just one? Just one (and please don't top post. :) ) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign keys and indexes
On Tue, 2007-06-05 at 17:07 +0200, Marc Compte wrote: For instance, in the implementation of a N:M relationship, declaring the primary as (foreign1, foreign2) will create two indexes? or just one? Just one -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Foreign keys, table inheritance, and TRUNCATE
Florian Weimer wrote: Here's something I've just noticed: CREATE TABLE foo (f INTEGER PRIMARY KEY); INSERT INTO foo VALUES (1); CREATE TABLE bar (b INTEGER REFERENCES foo); CREATE TABLE bar1 () INHERITS (bar); INSERT INTO bar1 VALUES (1); This is quite correct: No, it isn't; try leaving the first INSERT out: alvherre=# CREATE TABLE foo (f INTEGER PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE alvherre=# CREATE TABLE bar (b INTEGER REFERENCES foo); CREATE TABLE alvherre=# CREATE TABLE bar1 () INHERITS (bar); CREATE TABLE alvherre=# INSERT INTO bar1 VALUES (1); INSERT 0 1 alvherre=# select * from bar; b --- 1 (1 fila) alvherre=# select * from foo; f --- (0 filas) There is a bug here, but it's not in TRUNCATE. FKs don't work with inheritance. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Foreign keys and slow insert
On Wed, 8 Jun 2005, Dan Black wrote:
I read in documentation that primary key doesn't require additional indexes
but I could find nothing about foreign keys.
Do I need to create additional indexes when I create foreign keys?
Example:
create table master
{
master_id INT4,
master_name VARCHAR(64),
CONSTRAINT master_pkey PRIMARY KEY (master_id)
}
create table slave
{
slave_id INT4,
slave_name VARCHAR(64),
master_id INT4,
CONSTRAINT slave_pkey PRIMARY KEY (slave_id),
CONSTRAINT slave_fkey_master_id FOREIGN KEY (master_id) REFERENCES master
(master_id) ON UPDATE CASCADE ON DELETE RESTRICT
}
Do I need to create index
CREATE INDEX my_index
ON slave
USING btree
(master_id);
?
Generally you want to do so to speed up lookups when master changes.
However, if master is basically write once, almost never update or delete,
then you may not need one.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Foreign keys and slow insert
Dan Black wrote: I read in documentation that primary key doesn't require additional indexes but I could find nothing about foreign keys. Do I need to create additional indexes when I create foreign keys? Example: create table master create table slave Do I need to create index CREATE INDEX my_index ON slave USING btree (master_id); Yes. The primary key uses a UNIQUE INDEX to enforce uniqueness, so you get the index for free. The foreign-key has no such constraint of course. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Foreign keys and slow insert
I've observed that inserts into slave table became slower when I use foreign key than without one. Can it be related to foreign key? And I am interested how much performance of database with foreign keys can be different from performance of database without foreign keys? In other words, how much performance decrease on using foreign keys? Thanks :-)-- Verba volent, scripta manentMy ISP- http://www.netbynet.ru
Re: [GENERAL] Foreign keys and slow insert
On Wed, 2005-06-08 at 12:39, Dan Black wrote: I've observed that inserts into slave table became slower when I use foreign key than without one. Can it be related to foreign key? And I am interested how much performance of database with foreign keys can be different from performance of database without foreign keys? In other words, how much performance decrease on using foreign keys? The problem you're seeing is usually caused by adding records to a table set that starts out empty, and the planner uses seq scans, and as it grows, should switch to random seeks, but doesn't know to, because no one has bothered to analyze said tables. Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so often to help that. On the other hand, foreign keys are never zero cost, so even the most efficient implementation is gonna be slower than not using them. Data coherency costs, either up front (i.e. in the database doing it) or in the back (i.e. hiring 20 summer interns to go through your data and find the parts that are bad...) :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Foreign keys and slow insert
I think 21 interns will be enough :)2005/6/8, Scott Marlowe [EMAIL PROTECTED]: On Wed, 2005-06-08 at 12:39, Dan Black wrote: I've observed that inserts into slave table became slower when I use foreign key than without one. Can it be related to foreign key? And I am interested how muchperformance of database with foreign keys can be different from performance of database without foreign keys? In other words, how much performance decrease on using foreign keys?The problem you're seeing is usually caused by adding records to a table set that starts out empty, and the planner uses seq scans, and as itgrows, should switch to random seeks, but doesn't know to, because noone has bothered to analyze said tables.Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so often to help that.On the other hand, foreign keys are never zero cost, so even the mostefficient implementation is gonna be slower than not using them.Datacoherency costs, either up front (i.e. in the database doing it) or in the back (i.e. hiring 20 summer interns to go through your data and findthe parts that are bad...):)-- Verba volent, scripta manentMy ISP- http://www.netbynet.ru
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])
Re: [GENERAL] Foreign keys and pg_user table
Well, it's not a table! It's a view: dbs=# \d pg_user View pg_catalog.pg_user Column| Type | Modifiers -+-+--- usename | name| usesysid| integer | usecreatedb | boolean | usesuper| boolean | usecatupd | boolean | passwd | text| valuntil| abstime | useconfig | text[] | View definition: SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, ''::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow; So, you really want to use the pg_shadow table. C G wrote: but I get told that ...pg_user is not a table. Is there another way of doing what I want? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Foreign keys
On Thu, Jun 26, 2003 at 02:12:22PM +0100, Richard Huxton wrote: snip It looks like he has a centralised address table with customer_addresses linking customer to address. Likewise for supplier. His problem was he wanted to remove address details when nothing referred to them any more. I'd run an garbage collection over the db from time to time. It should be an easy test to select (or delete) all address rows which ID doesnt exist anywhere else. You can easily put this in an function. cu -- - Enrico Weigelt== metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 smsgate: [EMAIL PROTECTED] - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign keys
On Thu, Jun 26, 2003 at 12:00:07 +0100, Matt Browne [EMAIL PROTECTED] wrote: Other tables also reference records in the address table, using a similar sort of scheme. I have foreign keys set up so that if, for example, a record in customer is deleted, the corresponding records in the customer_addresses table are also removed. However, I can't find a way of ensuring records in the address table are deleted too, given that lots of different tables will reference address.id. What I'd like is for records in the address table to be automatically deleted at the end of each transaction if nothing references them any more. Is there any way to achieve this? You need to write custom triggers. Any time you delete an address id from a referencing table you need to check if the referenced id no longer has any references. Any time you insert (or update the primary key) a record in the address table you need to check that it is referenced. You will want this latter check to be deferable. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Foreign keys
Matt Browne wrote: Hello! I have a question regarding foreign keys and general garbage collection of data... If anyone could provide assistance, it'd be much appreciated! Basically, we have a fairly complex database, with many tables (customers, etc) that need to reference addresses that are contained in a generic address table. So: customer [table] id serial other fields customer_addresses [table] -- customer_id integer address_id integer supplier [table] id serial other fields supplier_addresses [table] -- supplier_id integer address_id integer address [table] --- id serial other fields Other tables also reference records in the address table, using a similar sort of scheme. I have foreign keys set up so that if, for example, a record in customer is deleted, the corresponding records in the customer_addresses table are also removed. However, I can't find a way of ensuring records in the address table are deleted too, given that lots of different tables will reference address.id. What I'd like is for records in the address table to be automatically deleted at the end of each transaction if nothing references them any more. Is there any way to achieve this? User defined triggers. I would set up a separate address-reference-count table, holding the address_id and a refcount (since this will get updated quite often and has a smaller footprint this way). For each reference of address you setup a trigger that increases or decreases the refcount for the address, and when it drops to zero, object terminated. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Foreign keys
Hello again - This problem has now been resolved, using triggers. A big thank you to everyone who reponded! I'd buy you all a beer if... Er... This list was a bar. Cheers! -- Matt Browne [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys
On Thursday 26 Jun 2003 1:40 pm, Rich Shepard wrote: Matt Browne wrote: Basically, we have a fairly complex database, with many tables (customers, etc) that need to reference addresses that are contained in a generic address table. So: customer_addresses [table] supplier_addresses [table] address [table] I've stumbled late onto this thread so I may have missed something important. However, I need to ask: are you keeping the same address in two different tables? That is, are customer_addresses records duplicated in address, and the same for supplier_addresses? If so, you've violated a normalization rule in your schema and it's no wonder that you can't delete all the addresses you want. It looks like he has a centralised address table with customer_addresses linking customer to address. Likewise for supplier. His problem was he wanted to remove address details when nothing referred to them any more. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Foreign keys
On Mon, 27 Aug 2001, Neal Lindsay wrote: If I create a columnA in a tableA that REFERENCES tableB(columnB) in another table, where column B is not null, does that imply a NOT NULL on my columnA? In other words, does it ensure that the value of A is in the set of values for B, or that the value of A is in the Bs unless A is null? The latter. The constraint is satisfied by either a NULL or a value in B. (Note: For multiple column keys it gets more involved depending on the match type). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Foreign Keys
How can you get a listing of foreign keys to a table? We haven't figure out a good way yet. The pg_depend discussion on hackers may lead to a solution if we evern implement it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys?
It was a little bit late when I wrote that, and so I probably should have been a little more specific. I don't know if you would notice a performance difference between the joined tables query and and the non-joined version for such simple tables. I might have to spend a bit of time today loading a test database with sufficient data to test it, because now I am curious. However, I know that if your tables are more involved than the trivial ones that I included that it can make a big difference. This is especially true if you want to join a table to several lookup tables. In those cases it is a serious performance win to have the data in the master table and simply use the lookup tables to guarantee that valid data is entered. By the time you have a query that looks like this: SELECT users.name, states.name, institutions.name, divisions.name, trucks.id from users, states, institutions, divisions, trucks WHERE users.state = states.id AND users.institution = institutions.id AND users.division = divisions.id AND users.truck = trucks.id AND users.id = 'MYID'; PostgreSQL is going to wish that you had put more of that information in the users table. A view might make the query easier to type, but it won't undo the performance penalty of multiple joins. At least that is how I understand it. I might be wrong, however, I never have pretended to be a SQL guru, but I certainly noticed a performance difference when I switched from a table with multiple joins to one with more of the information directly in the table (it still referenced primary keys in another table, they just were varchar primary keys and not ints). Jason --- Richard Huxton [EMAIL PROTECTED] wrote: From: Jason Earl [EMAIL PROTECTED] However, if you are going to do a lot of joins on your user table along the lines of: SELECT user.name, object.description FROM user, object WHERE user.number = object.owner; Then you might be better off simplifying just a bit to give you something like: CREATE TABLE user ( name VARCHAR(400) PRIMARY KEY ); CREATE TABLE object ( owner VARCHAR(400) REFERENCES user NOT NULL, description VARCHAR(200) ); That would save you having to join the table to find the user.name at the expense of using more hard drive space. I'm curious - are you speaking from a performance viewpoint here, or just about simplifying queries (in which case I'd just slap a view on top)? - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign Keys to Non-primary keys?
On Thu, 21 Jun 2001 [EMAIL PROTECTED] wrote: Is it possible to have a foreign key to a non-primary key (also meaning non-unique and therefore non-indexed) column i a table? Generally no. It's not allowed by the spec, so as of 7.1, we prevent it. Admittedly, you could pull the rug out from underneath it by dropping the index, though. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] foreign keys constraints, depending on each other
Am Montag, 11. Juni 2001 10:25 schrieb Marc SCHAEFER: I would remove the father and mother references, and add a is_married relation; as a table, with a UNIQUE(father_id), UNIQUE(mother_id) constraint (a person can be only married once). Is not true, at least not in some arabic countries. -- === Mario Weilguni KPNQwest Austria GmbH Senior Engineer Web Solutions Nikolaiplatz 4 tel: +43-316-813824 8020 graz, austria fax: +43-316-813824-26 http://www.kpnqwest.at e-mail: [EMAIL PROTECTED] === ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] foreign keys constraints, depending on each other
On Sun, 10 Jun 2001 [EMAIL PROTECTED] wrote: I was just creating this little database for demonstrating the use of foreign keys constraints. I was about the create 3 tables, namely mother, father and child. Mother has a foreign key pointing at father ( id ), and father has a foreign key pointing at mother ( id ). Child has one pointer to mother ( id ) and one pointer to father ( id ). How can I prevent the error message from occurring? You don't put the constraint at table creation time. The table referenced by the references has to exist. Use ALTER TABLE to add the constraint after creating table father. I tried: BEGIN; SET CONSTRAINTS ALL DEFERRED; INSERT INTO mother (fatherID, name) VALUES ( 1, 'mamma' ) ; INSERT INTO father (motherID, name) VALUES ( 1, 'pappa' ) ; INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 1') ; INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 2') ; COMMIT; ...which did not work. Still it complains about key referenced from mother not found in father. Ah, that's because you didn't define the constraints DEFERRABLE. SET CONSTRAINTS ALL DEFERRED only changes the state of deferrable constraints. If you don't specify a time, it's INITIALLY IMMEDIATE. If it's initially immediate, it's NOT DEFERRABLE unless DEFERRABLE is explicitly given. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Foreign keys/unique values and views
On Thu, 22 Mar 2001, Richard Huxton wrote: I have to admit I've never tried referencing a view with a foreign key. I don't know if it's possible and I have to admit the idea makes me uncomfortable. Can't give a good reason why, but I'd apply constraints at the table level. if one can reference a table with a foreign key it makes possible to reference inheritance hierarchies. If I create a view from the parent with CREATE VIEW name SELECT * FROM parent; (in v.7.1) it should contain the entries from the children as well. Now if I could reference this view I could simulate referencing parent and child tables easily. What I have in mind is a hierarchy of persons all inheriting from a table called person. Different kinds of persons have different attributes but all have an id and name. What I want is that other tables could reference these persons easily. - Einar Karttunen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Foreign keys/unique values and views
On Thu, Mar 22, 2001 at 06:33:16PM -0500, Jan Wieck wrote: Einar Karttunen wrote: How do you put a UNIQUE constraint on the entire inheritance hierarchie? Easy. You make a unique index that covers an entire inheritance hierarchy. If lots of table inherit a field "id" from a single table the you can put a unique index on that field. All you need then is an index whose keys reference not tuple IDs but (table ID, tuple ID) pairs. Wow. I'm just thinking of how vacuum would deal with this. Probably not very well. But it would give inheritance hierarchies very efficient lookups rather than the implicit unioning done currently. Martijn ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys
You can reconstruct the information out of the triggers that are created in pg_trigger. It's not easy to parse however. There are three triggers created for each fk constraint, one on the fk table, two on the pk table. You can get the tables, columns constrained and match type from the tgargs. The referential actions are determined by the function oids of the pk table triggers (do something like: select pg_proc.proname from pg_trigger,pg_proc where pg_trigger.tgfoid=pg_proc.oid; to get the function names which will look like: RI_FKey_action_(upd|del) for update or delete, action is the action defined. On Sat, 17 Feb 2001, MMM wrote: Sorry for my bad English I'd like to know how I can see the foreign keys for a table. Is there a system table where I can do a Select?; Thank you.
Re: [GENERAL] Foreign Keys
On Thu, Feb 01, 2001 at 07:59:16PM -0500, No Name wrote: I have PostgreSQL 6.5, and I can't get foreign keys to work! What seems to be the problem? Your database is too purple. -- Adam Haberlach|A cat spends her life conflicted between a [EMAIL PROTECTED] |deep, passionate, and profound desire for http://www.newsnipple.com |fish and an equally deep, passionate, and '88 EX500 |profound desire to avoid getting wet.
Re: [GENERAL] foreign keys - script
But wouldn't the dependencies be there whether I compile from scratch or use an RPM? What the main issue that stumped was the libc.6.so dependency. According to epm -a -q, it is installed. I guess I can blow out 6.5 and download the 7.0 source and reinstall a compiled version. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Tom Lane" [EMAIL PROTECTED] To: "Adam Lang" [EMAIL PROTECTED] Cc: "Stephan Szabo" [EMAIL PROTECTED]; "PGSQL General" [EMAIL PROTECTED] Sent: Tuesday, August 29, 2000 6:47 PM Subject: Re: [GENERAL] foreign keys - script "Adam Lang" [EMAIL PROTECTED] writes: As for using 7.02, I had tried installing it from scratch, but had dependency problems. I attempted an "upgrade" just now, and here is the list of dependencies failures I had: Seems like grabbing the source distribution and doing "configure; make; make install" would be a lot easier ;-) regards, tom lane
Re: [GENERAL] foreign keys - script
"Adam Lang" [EMAIL PROTECTED] writes: But wouldn't the dependencies be there whether I compile from scratch or use an RPM? But if you compile from source, it will use whatever libc you have installed. regards, tom lane
Re: [GENERAL] foreign keys
*** Stephan Szabo [EMAIL PROTECTED] [Sunday, 06.August.2000, 14:26 -0700]: Actually, you should only be seeing one constraint out on the referencing table and two out of the referenced one, but yes, fundamentally it only is dumping the constraint triggers for the table you are dumping at the moment. ok, but let's talk about number of constraints. I think that the correct number (for my meaning of full foreign key) is 4. 2 for both tables: referenced: UPDATE contrains AND DELETE constraint referencing: INSERT constraint AND UPDATE constraint am I right? What is correct (mean: most simple) way of dupicating table with all FK ? Umm, possibly taking the dump of the table you want and a schema only dump of the referenced table and removing the bits you don't need. Or, turn the constraint triggers into alter table add constraint statements (although you'd then have to only get one alter table add constraint in case you were on the referenced table - and that could still get you in trouble depending on what precisely you're doing -- if the table was the referenced table of a fk constraint, would you necessarily want to alter the table that was referencing it?). [.rs.] my english is not so good for such complicated sentences :) what i need/try to accomplish is to full dump/recreate/modify of table with all needed (applied) constraints. Primiary I thought only about referencing table but now I know that "prescription" should also mention operation on referenced table. In short: 1. how to full duplicate/modify table (referencing) 2. how to full duplicate/modify table (referenced) It should take care of fact that it should at start DESTROY table and all constraints (on both tables!!!) and than recreate it from scratch - this is needed to satisfy the modify case (someone may need to changee FK schema to sth different). How can I manipulate existing unnamed (created automaticly by foreign key) constraints on tables in PSQL tool ? -- radoslaw.stachowiak.http://alter.pl/
Re: [GENERAL] foreign keys
*** Bruce Momjian [EMAIL PROTECTED] [Saturday, 05.August.2000, 19:39 -0400]: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? What is correct (mean: most simple) way of dupicating table with all FK ? Sorry, I don't know. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] foreign keys
*** Bruce Momjian [EMAIL PROTECTED] [Saturday, 05.August.2000, 19:39 -0400]: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? What is correct (mean: most simple) way of dupicating table with all FK ? -- radoslaw.stachowiak.http://alter.pl/
Re: [GENERAL] foreign keys
On Sun, 6 Aug 2000, Radoslaw Stachowiak wrote: *** Bruce Momjian [EMAIL PROTECTED] [Saturday, 05.August.2000, 19:39 -0400]: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? Actually, you should only be seeing one constraint out on the referencing table and two out of the referenced one, but yes, fundamentally it only is dumping the constraint triggers for the table you are dumping at the moment. What is correct (mean: most simple) way of dupicating table with all FK ? Umm, possibly taking the dump of the table you want and a schema only dump of the referenced table and removing the bits you don't need. Or, turn the constraint triggers into alter table add constraint statements (although you'd then have to only get one alter table add constraint in case you were on the referenced table - and that could still get you in trouble depending on what precisely you're doing -- if the table was the referenced table of a fk constraint, would you necessarily want to alter the table that was referencing it?).
Re: [GENERAL] foreign keys
On Sat, 5 Aug 2000, Bruce Momjian wrote: Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. Argh, that's annoying. Is there a way to CREATE TABLE, getting the schema from another table? :o Ian
Re: [GENERAL] foreign keys
Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Foreign Keys in PostgreSQL
On Thu, 15 Jul 1999, [iso-8859-1] Simeó wrote: How can I implement foreign Keys with postgres? thanks. pgsql/contrib/spi has refint.c. you'd want to compile that and execute refint.sql. it also comes with documentation. refit is, for now, the way postgres handles foreign keys. two functions are involved, check_foreign_key() and check_primary_key(). --- Howie [EMAIL PROTECTED] URL: http://www.toodarkpark.org "The distance between insanity and genius is measured only by success."
Re: [HACKERS] Re: [GENERAL] Foreign Keys: check_primary_function
Try this create table FOOS( opaque_id int4 NOT NULL PRIMARY KEY, name text NOT NULL, bars int4 NOT NULL, bazs int4 NOT NULL ); or this create table FOOS( opaque_id int4 NOT NULL, name text NOT NULL, bars int4 NOT NULL, bazs int4 NOT NULL, CONSTRAINT opaque_key PRIMARY KEY (opaque_id) ); -Margarita On Thu, 8 Apr 1999, Todd Graham Lewis wrote: My inference based on this email, namely that foreign keys are in some at-least-minimal way supported or will be soon, is given the lie on my compile today out of CVS: tlewis= create table FOOS( opaque_id int4 NOT NULL, name text NOT NULL, bars int4 NOT NULL, bazs int4 NOT NULL) PRIMARY KEY (opaque_id); ERROR: parser: parse error at or near "primary" tlewis= \h create table Command: create table Description: create a new table Syntax: CREATE [TEMP] TABLE class_name (attr1 type1 [DEFAULT expression] [NOT NULL], ...attrN [[CONSTRAINT name] CHECK condition1, ...conditionN] ) [INHERITS (class_name1, ...class_nameN) ; So, Brunhilda, what's the magin incantation? Do I need some tail of Newt? 8^)
Re: [GENERAL] Foreign Keys: check_primary_function
Applied.
Hi All,
I've changed the check_primary_key() function code to allow for either
the "automatic insert key rule" or "dependent insert key rule".
Previously it restricted the addtion of a child entry if the
corresponding parent entry was not there. Now if the option is
"automatic" it will add an entry in the parent too ( it will be
successful if there are no no-null fields in the parent apart from the
primary key).
The way to use it now is:
/*
* check_primary_key () -- check that key in tuple being
inserted/updated
* references existing tuple in "primary" table.
* Though it's called without args You have to specify referenced
* table/keys while creating trigger: key field names in triggered
table,
* referenced table name, referenced key field names,type of action
[automatic|dependent]:
* EXECUTE PROCEDURE
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2',
'[automatic|dependent]').
*/
I am attaching the new ../contrib/spi/refint.c file which will do this.
I will be glad to help in case of any problems.
- Anand.
/*
* refint.c --set of functions to define referential integrity
*constraints using general triggers.
*/
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
#include ctype.h/* tolower () */
HeapTuple check_primary_key(void);
HeapTuple check_foreign_key(void);
typedef struct
{
char *ident;
int nplans;
void **splan;
} EPlan;
static EPlan *FPlans = NULL;
static intnFPlans = 0;
static EPlan *PPlans = NULL;
static intnPPlans = 0;
static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
/*
* check_primary_key () -- check that key in tuple being inserted/updated
* references existing tuple in "primary" table.
* Though it's called without args You have to specify referenced
* table/keys while creating trigger: key field names in triggered table,
* referenced table name, referenced key field names,type of action
[automatic|dependent]:
* EXECUTE PROCEDURE
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1',
'Pkey2','[automatic|dependent]').
*/
HeapTuple /* have to return HeapTuple to
Executor */
check_primary_key()
{
Trigger*trigger;/* to get trigger name */
int nargs; /* # of args specified in
CREATE TRIGGER */
char **args; /* arguments: column names and table
name */
int nkeys; /* # of key columns (=
(nargs-1) / 2) */
Datum *kvals; /* key values */
char *relname;/* referenced relation name */
char *action; /* action on insert or update*/
Relationrel;/* triggered relation */
HeapTuple tuple = NULL; /* tuple to return */
TupleDesc tupdesc;/* tuple description */
EPlan *plan; /* prepared plan */
Oid*argtypes = NULL;/* key types to prepare execution plan */
boolisnull; /* to know is some column NULL or not
*/
charident[2 * NAMEDATALEN]; /* to identify myself */
int ret;
int i;
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CurrentTriggerData)
elog(ERROR, "check_primary_key: triggers are not initialized");
/* Should be called for ROW trigger */
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData-tg_event))
elog(ERROR, "check_primary_key: can't process STATEMENT events");
/* If INSERTion then must check Tuple to being inserted */
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData-tg_event))
tuple = CurrentTriggerData-tg_trigtuple;
/* Not should be called for DELETE */
else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData-tg_event))
elog(ERROR, "check_primary_key: can't process DELETE events");
/* If UPDATion the must check new Tuple, not old one */
else
tuple = CurrentTriggerData-tg_newtuple;
trigger = CurrentTriggerData-tg_trigger;
nargs = trigger-tgnargs;
args = trigger-tgargs;
if ((nargs-1) % 2 != 1) /* odd number of arguments! */
elog(ERROR, "check_primary_key: even number of arguments should be
specified");
nkeys = (nargs-1) / 2;
action=args[nargs -1];
if (strcmp(action,"automatic") strcmp(action,"dependent"))
