Re: [SQL] foreign key problems
Ok, so I made some changes (manual "inheritance" of PK and FK constraints), but nevertheless I get still the same dumb error. I made a dump of the DB via pg_dump, it is available at http://de.geocities.com/bartkozo/dump.tgz DRecord was dropped, the columns moved into DObject. I still have no idea why foreign keys work on other tables and do not on this one. I have three users, superaspiramus, aspiramus and aspiramusadmin. The first is the owner of the db. After loading, the contents of the DObject table are: aspiramus=> select * from DObject; who | insteadof | privilege | createdat | objectid | revision | aname | description | objecttype | status -+---+---++--+-- +---+-++ -2 | |-1 | 2005-01-05 09:38:11.906+01 | 34 | 1 | a | a | 1 | 67 -2 | |-1 | 2005-01-05 09:37:27.625+01 | 35 | 0 | b | b | 1 | 61 -2 | |-2 | 2005-01-05 09:37:27.625+01 | 33 | 0 | name | description | 13 |301 (3 riadkov) when trying this: aspiramus=> insert into AAttachment (createdby, privilege, insteadof, objectid, bodyid) values (-2,-2,null,33,33); I get: ERROR: insert or update on table "aattachment" violates foreign key constraint "aattachment_objectid_fkey" DETAIL: Key (objectid)=(33) is not present in table "dobject". I think the problem is not in front of the computer, but maybe I am wrong. If so, I would appreciate some help as to where I am wrong. I can bypass this problem by dropping the FK constraints from objectID and bodyID in AAttachment, but it is not too kosher. Thanks for your assistance Zoltan > > On Tue, 4 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote: > > > if I create the tables that are in the attached file, I can't insert > > rows into the AAttachment table, even though the rows in DObject with > > the given primary key exist (PgSQL 8.0 rc1 complains about > > (ownerid)=(insert the number here) not available in DObject. The same > > happens with attribute bodyid). > > It looks like the set of table definitions you gave in the attached file > do not actually work as given, and there's no example data. Running on > 8.0 beta3 I was able to insert data that allowed me to place a row into > AAttachment (and in fact I actually can insert some data that probably > should not be allowed). I'm not sure if that's based on my basically null > definition of DRecord or not however. > > In general, however, foreign keys and inheritance don't mix (neither do > primary keys or unique constraints). In general, they'll only currently > work for the table they're in specifically and only for the table > mentioned specifically. So, for example, the references constraints in > AObjAssociation are not inherited by AAttachment. Inheritance really needs > someone to champion getting all of these deficiencies fixed. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > RAMMSTEIN, 22.02.2005 o 20,00, Bratislava Incheba, Info: 0904 666 363, http://www.xl.sk ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] foreign key problems
On Wed, 5 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote: > Ok, so I made some changes (manual "inheritance" of PK and FK > constraints), but nevertheless I get still the same dumb error. I made > a dump of the DB via pg_dump, it is available at > > http://de.geocities.com/bartkozo/dump.tgz > > DRecord was dropped, the columns moved into DObject. I still have no > idea why foreign keys work on other tables and do not on this one. > > I have three users, superaspiramus, aspiramus and aspiramusadmin. The > first is the owner of the db. > > After loading, the contents of the DObject table are: > > aspiramus=> select * from DObject; Do select * from ONLY DObject. The constraint currently only goes to DObject, records that are actually in DObject and not any tables derived from it. This is one of the deficiencies I was trying to refer to in the previous message. As a note, your primary keys will also not work to prevent duplicates of objectid between subclasses of DObject currently, and I'm not sure whether that matters to you: insert into dmessage (who, privilege, objectid, objecttype, status, aname,sender,receiver) values (-2,-2,33,13,301,'name',-2,-2); select objectid from dobject; objectid -- 34 35 33 33 (4 rows) Inheritance needs alot of work. :( ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] foreign key problems
On Wed, 5 Jan 2005, Stephan Szabo wrote: > > On Wed, 5 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote: > > > Ok, so I made some changes (manual "inheritance" of PK and FK > > constraints), but nevertheless I get still the same dumb error. I made > > a dump of the DB via pg_dump, it is available at > > > > http://de.geocities.com/bartkozo/dump.tgz > > > > DRecord was dropped, the columns moved into DObject. I still have no > > idea why foreign keys work on other tables and do not on this one. > > > > I have three users, superaspiramus, aspiramus and aspiramusadmin. The > > first is the owner of the db. > > > > After loading, the contents of the DObject table are: > > > > aspiramus=> select * from DObject; > > Do select * from ONLY DObject. > > The constraint currently only goes to DObject, records that are actually > in DObject and not any tables derived from it. This is one of the > deficiencies I was trying to refer to in the previous message. As a note, > your primary keys will also not work to prevent duplicates of objectid > between subclasses of DObject currently, and I'm not sure whether that > matters to you: > > insert into dmessage (who, privilege, objectid, objecttype, status, > aname,sender,receiver) values (-2,-2,33,13,301,'name',-2,-2); > > select objectid from dobject; > objectid > -- >34 >35 >33 >33 > (4 rows) > > Inheritance needs alot of work. :( I forgot to mention that this has come up in the past, and some people have sent messages about workarounds. I believe one involved pulling the canonical key values out into a separate table that is managed by triggers with foreign keys between each of the tables in the hierarchy to the key table. Thus, a unique constraint on that key table would effectively span all tables with such triggers and foreign keys to that table might work. However, that doesn't work if you also have foreign keys to a derived table from which other tables might be inherited. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [JDBC] Calling a table in another database from plpgsql
On Wed, 5 Jan 2005, Dave Cramer wrote: > Kris is correct, this is a jdbc list, however to satisfy your curiosity, > you can't access data from another database. > You certainly can with dblink which is what his question was about. I didn't answer him because I remembered a recent off-topic question from him which I responded to with an answer and a suggestion of the appropriate list. As long as answers keep coming the hint will not be picked up. Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Calling a table residing in another database from Plpgsql
am Tue, dem 04.01.2005, um 21:13:46 -0800 mailte [EMAIL PROTECTED] folgendes: > Hi, > > I have function called test1() in database 1. I need to input the value > from this function into another table which is in database2. > > How can i achieve this task in plpgsql. I went through some documentation > and found out that it is possible through dblink. > > I infact downloaded the dblink package in contrib folder. But still when i > tried to use dblink in the following manner: You must run the dblink.sql, not only download. Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Calling a table residing in another database from Plpgsql
[EMAIL PROTECTED] wrote:
SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);
I am getting an error as "ERROR: function dblink("unknown", "unknown")
does not exist"
Have you installed the dblink functions into your database? See
README.dblink.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] plpsql function problem
Hi, I'm writing a plpgsql function. There is an insert in it (let's say to table1). This insert causes to call a trigger function which inserts some info to an other table (let's say table2): function . insert into table1 ... --> trigger function (runs after insert) . insert into table2 ... . select from table2 ... not found . This info looks to be unavailable inside the function. Can it be made availabel or must this whole thing reorgenized? Daniel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] plpsql function problem
[EMAIL PROTECTED] writes: > function >. >insert into table1 ... --> trigger function (runs after insert) > . > insert into table2 ... >. >select from table2 ... not found >. > This info looks to be unavailable inside the function. Can it be made > availabel or must this whole thing reorgenized? If it's an AFTER trigger, the trigger is currently not fired until the end of the entire command (ie, whatever interactive command called your function). So the SELECT isn't seeing the result because it hasn't happened yet. This will change in PG 8.0. regards, tom lane ---(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
[SQL] undigested email
Hello, According to my profile, I am subscribed for daily ~digest~. Yet, I recieve multiple ~individual~ emails every day. Am I doing something wrong? TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] vararg plpsql function
Hi, Is it possible to create vararg (like in C) functions in language plpgsql somehow? Daniel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
