Thread: Incomprehensible behaviour of a foreign key.
I'm completely baffled by this thing, the work it is for is extremely urgent and this is currently a show stopper. My minimal test script showing the problem is attached and the output is shown below. There is no other connection to the db, indeed I have been stopping and starting the backend itself before each of my test runs this morning and once again the shown output is obtained after doing that and opening this one and only this one connection to the db. I'd really appreciate an explanation, since this test is based on queries extracted from the db log, is only one specific example of this sort of operation from many in the driving program and most significantly it seems I can't even write sql statments hardcoding these values as the test script shows they still get the ref. int. error. If necessary I will absolutely turn on auto commit after each statement in order to get this block of code to run but once again the test script shows that this will make sod all difference since it's the completely empty table before the transaction even starts that is causing the problem. **** Start by showing the problem table is empt _before_ the transaction starts select * from site_membership; id | site_id | group_id ----+---------+---------- (0 rows) begin; BEGIN **** Move some other references out of the way update sections set group_id = 207 where exists (select 1 from groups g where (g.principal_user_id = 144 or g.name = Press Office ) and g.id <> 207 and group_id = g.id ) ; UPDATE 12 **** Show what we will be trying to delete select * from groups where exists (select 1 from groups g where (g.principal_user_id = 144 or g.name = Press Office ) and g.id <> 207 and groups.id = g.id ) ; id | active | site_id | principal_user_id | name | summary -----+--------+---------+-------------------+--------------+--------- 173 | t | | 113 | Press Office | 206 | t | | 140 | Press Office | 211 | t | | 153 | Press Office | (3 rows) **** Attempt the delete ... **** ...and watch the empty table from the start cause a ref. int. failure! delete from groups where exists (select 1 from groups g where (g.principal_user_id = 144 or g.name = Press Office ) and g.id <> 207 and groups.id = g.id ) ; psql:/tmp/aa2.sql:101: ERROR: $2 referential integrity violation - key in groups still referenced from site_membership -- Nigel J. Andrews
Attachment
As usual I forgot to include the version number. It's 7.3.3 On Sun, 20 Jul 2003, Nigel J. Andrews wrote: > > > I'm completely baffled by this thing, the work it is for is extremely urgent > and this is currently a show stopper. My minimal test script showing the > problem is attached and the output is shown below. > > There is no other connection to the db, indeed I have been stopping and > starting the backend itself before each of my test runs this morning and once > again the shown output is obtained after doing that and opening this one and > only this one connection to the db. > > I'd really appreciate an explanation, since this test is based on queries > extracted from the db log, is only one specific example of this sort of > operation from many in the driving program and most significantly it seems I > can't even write sql statments hardcoding these values as the test script shows > they still get the ref. int. error. > > If necessary I will absolutely turn on auto commit after each statement in > order to get this block of code to run but once again the test script shows > that this will make sod all difference since it's the completely empty table > before the transaction even starts that is causing the problem. > > > > **** Start by showing the problem table is empt _before_ the transaction starts > select * from site_membership; > id | site_id | group_id > ----+---------+---------- > (0 rows) > > begin; > BEGIN > **** Move some other references out of the way > update sections set group_id = 207 > where > exists (select 1 > from groups g > where > (g.principal_user_id = 144 or g.name = Press Office ) > and > g.id <> 207 > and > group_id = g.id > ) > ; > UPDATE 12 > **** Show what we will be trying to delete > select * from groups > where > exists (select 1 > from groups g > where > (g.principal_user_id = 144 or g.name = Press Office ) > and > g.id <> 207 > and > groups.id = g.id > ) > ; > id | active | site_id | principal_user_id | name | summary > -----+--------+---------+-------------------+--------------+--------- > 173 | t | | 113 | Press Office | > 206 | t | | 140 | Press Office | > 211 | t | | 153 | Press Office | > (3 rows) > > **** Attempt the delete ... > **** ...and watch the empty table from the start cause a ref. int. failure! > delete from groups > where > exists (select 1 > from groups g > where > (g.principal_user_id = 144 or g.name = Press Office ) > and > g.id <> 207 > and > groups.id = g.id > ) > ; > psql:/tmp/aa2.sql:101: ERROR: $2 referential integrity violation - key in groups still referenced from site_membership > > > -- Nigel J. Andrews Telephone: +44 (0) 208 941 1136
В Вск, 20.07.2003, в 15:15, Nigel J. Andrews пишет: > As usual I forgot to include the version number. It's 7.3.3 Table schemas will be helpful, too. -- Markus Bertheau. Berlin, Berlin. Germany.
On Sun, 20 Jul 2003, Nigel J. Andrews wrote: > I'm completely baffled by this thing, the work it is for is extremely urgent > and this is currently a show stopper. My minimal test script showing the > problem is attached and the output is shown below. We're going to need a real test script that includes the table schema and preferably a set of made up data since the test script doesn't tell us enough to trace the actual problem since it's not runable without the schema.
On 20 Jul 2003, Markus Bertheau wrote: > Ð ÐÑк, 20.07.2003, в 15:15, Nigel J. Andrews пиÑеÑ: > > As usual I forgot to include the version number. It's 7.3.3 > > Table schemas will be helpful, too. All tables are in the one and only schema listed in the users search path and the three tables and the relevent columns used in the test script are: create table groups ( id serial primary key ,name text ,principal_user_id int references anothertable(id) ,... ) without oids; create table sections ( id serial primary key ,group_id int references groups(id) ,... ) without oids; create table site_membership ( id serial unique ,group_id int references groups(id) ,site_id int references someothertable(id) ,primary key(site_id,group_id) ) without oids; Hope that's enough to give you the idea of the linkages. -- Nigel Andrews
On Sun, 20 Jul 2003, Stephan Szabo wrote: > > On Sun, 20 Jul 2003, Nigel J. Andrews wrote: > > > I'm completely baffled by this thing, the work it is for is extremely urgent > > and this is currently a show stopper. My minimal test script showing the > > problem is attached and the output is shown below. > > We're going to need a real test script that includes the table schema and > preferably a set of made up data since the test script doesn't tell us > enough to trace the actual problem since it's not runable without the > schema. > Yes, I realise that a standalone test script would be preferable unfortunately I can not invest the time in generating such a beast, especially as it would probably work, when I need to produce a solution for this. To have the project pulled because I've thrown an hour at trying to reproduce this in a completely clean db instead of getting the project closer to working in that time is really not an option I can consider at the moment. I've just posted a brief description of the tables involved, isn't that sufficient to be able to say whether there is something strange happening and perhaps suggest some things to try? Nigel Andrews ...in a progressively bigger and bigger panic.
On 20/07/2003 14:15 Nigel J. Andrews wrote: > > As usual I forgot to include the version number. It's 7.3.3 > > > > I'd really appreciate an explanation, since this test is based on > queries > > extracted from the db log, is only one specific example of this sort of > > operation from many in the driving program and most significantly it > seems I > > can't even write sql statments hardcoding these values as the test > script shows > > they still get the ref. int. error. A bit more detail about the tables might be helpful, constraints, triggers etc... How about doing a select of site_membership immediately before the delete. What does that show? Have you got a trigger somewhere that would insert a record into site_membership? This could cause the RI failure but, without the transaction being committed, the inserted record could be discarded and the table would still appear empty after the error. I'm very much clutching at straws here and am probably way off but without more details, wild guesses are the best I can do. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
On Sun, 20 Jul 2003, Paul Thomas wrote: > > On 20/07/2003 14:15 Nigel J. Andrews wrote: > > > > As usual I forgot to include the version number. It's 7.3.3 > > > > > > I'd really appreciate an explanation, since this test is based on > > queries > > > extracted from the db log, is only one specific example of this sort of > > > operation from many in the driving program and most significantly it > > seems I > > > can't even write sql statments hardcoding these values as the test > > script shows > > > they still get the ref. int. error. > > A bit more detail about the tables might be helpful, constraints, triggers > etc... How about doing a select of site_membership immediately before the > delete. What does that show? Have you got a trigger somewhere that would > insert a record into site_membership? This could cause the RI failure but, > without the transaction being committed, the inserted record could be > discarded and the table would still appear empty after the error. I'm very > much clutching at straws here and am probably way off but without more > details, wild guesses are the best I can do. I understand, and that's all I'm hoping for at this stage. No, there's no triggers on any of these tables. In the test script site_membership is still empty immediately before the delete. Nigel Andrews
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> **** Attempt the delete ...
> **** ...and watch the empty table from the start cause a ref. int. failure!
Bizarre. If the database is not too huge, I would ask you to please
make a tarball backup of the whole $PGDATA directory (while the
postmaster is stopped of course) before you go any further. That way
we can get back to this state if needed for bug investigation.
With backup in hand, please try "VACUUM FULL VERBOSE site_membership"
and see what it has to say about rows in site_membership. If it shows
that any were deleted, is the problem fixed?
regards, tom lane
Just an uninformed wild guess but you seem to be in desperate search for straws: > create table site_membership ( > id serial unique > ,group_id int references groups(id) > ,site_id int references someothertable(id) > ,primary key(site_id,group_id) > ) without oids; Does using id as primary key and removing the multi-key primary key constraint on (site_id, group_id) help any ? Unlikely but you never know... Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, 2003-07-20 at 07:34, Nigel J. Andrews wrote: > I'm completely baffled by this thing, the work it is for is extremely urgent > and this is currently a show stopper. My minimal test script showing the > problem is attached and the output is shown below. [[snip] > > > **** Start by showing the problem table is empt _before_ the transaction starts > select * from site_membership; > id | site_id | group_id > ----+---------+---------- > (0 rows) [snip] > psql:/tmp/aa2.sql:101: ERROR: $2 referential integrity violation - key in groups still referenced from site_membership Is there actually a FK referring to these tables? Can you drop it and see if your txn works, then recreate it, or even recreate site_membership? What if the FK doesn't like NULLs, which it would get, since site_membership is empty? Maybe this would help, presuming all fields are scalar: INSERT INTO SITE_MEMBERSHIP VALUES (-1, -1, -1); -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
On Sun, 20 Jul 2003, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > **** Attempt the delete ...
> > **** ...and watch the empty table from the start cause a ref. int. failure!
>
> Bizarre. If the database is not too huge, I would ask you to please
> make a tarball backup of the whole $PGDATA directory (while the
> postmaster is stopped of course) before you go any further. That way
> we can get back to this state if needed for bug investigation.
>
> With backup in hand, please try "VACUUM FULL VERBOSE site_membership"
> and see what it has to say about rows in site_membership. If it shows
> that any were deleted, is the problem fixed?
>
> regards, tom lane
>
Test script output up to the transaction start:
**** Start by showing the problem table is empt _before_ the transaction starts
select * from site_membership;
id | site_id | group_id
----+---------+----------
(0 rows)
psql:/tmp/aa2.sql:8: INFO: --Relation ttacms.site_membership--
psql:/tmp/aa2.sql:8: INFO: Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 0: Vac 82, Keep/VTL 0/0, UnUsed 0, MinLen
0,MaxLen 0; Re-using: Free/Avail. Space 7844/0; EndEmpty/Avail. Pages 1/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:/tmp/aa2.sql:8: INFO: Index site_membership_pkey: Pages 2; Tuples 0: Deleted 82.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:/tmp/aa2.sql:8: INFO: Rel site_membership: Pages: 1 --> 0.
VACUUM
...
The table had 82 tuples in it until a deleted them with delete from
site_membership earlier in the session.
The db is about 100MB I'd guess but it's not really important to make the back
up as this problem is happening in a script that loads from a dump and does
some stuff to move the data into another schema and the problem consistently
arises during this process.
--
Nigel J. Andrews
Sorry, I forgot to mention that this had no effect when it came to stopping the error message. On Sun, 20 Jul 2003, Nigel J. Andrews wrote: > On Sun, 20 Jul 2003, Tom Lane wrote: > > > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > > > **** Attempt the delete ... > > > **** ...and watch the empty table from the start cause a ref. int. failure! > > > > Bizarre. If the database is not too huge, I would ask you to please > > make a tarball backup of the whole $PGDATA directory (while the > > postmaster is stopped of course) before you go any further. That way > > we can get back to this state if needed for bug investigation. > > > > With backup in hand, please try "VACUUM FULL VERBOSE site_membership" > > and see what it has to say about rows in site_membership. If it shows > > that any were deleted, is the problem fixed? > > > > regards, tom lane > > > > Test script output up to the transaction start: > > **** Start by showing the problem table is empt _before_ the transaction starts > select * from site_membership; > id | site_id | group_id > ----+---------+---------- > (0 rows) > > psql:/tmp/aa2.sql:8: INFO: --Relation ttacms.site_membership-- > psql:/tmp/aa2.sql:8: INFO: Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 0: Vac 82, Keep/VTL 0/0, UnUsed 0, MinLen0, MaxLen 0; Re-using: Free/Avail. Space 7844/0; EndEmpty/Avail. Pages 1/0. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > psql:/tmp/aa2.sql:8: INFO: Index site_membership_pkey: Pages 2; Tuples 0: Deleted 82. > CPU 0.00s/0.00u sec elapsed 0.00 sec. > psql:/tmp/aa2.sql:8: INFO: Rel site_membership: Pages: 1 --> 0. > VACUUM > ... > > > The table had 82 tuples in it until a deleted them with delete from > site_membership earlier in the session. I forgot to mention that this had no effect when it came to stopping the error message. > > The db is about 100MB I'd guess but it's not really important to make the back > up as this problem is happening in a script that loads from a dump and does > some stuff to move the data into another schema and the problem consistently > arises during this process. > > > -- Nigel J. Andrews
On Sun, 20 Jul 2003, Nigel J. Andrews wrote: > Blah, blah, blah. > Further hair pulling and trying many many things and I finally discovered why this foreign key constraint problem was so weird, I looked up all pg_class entries with that name immediately before the breaking statement. Turns out I had another table of the same name and design in another schema. I even knew about that but because an earlier part of the process was moving that data out of that extra table, which had inadvertently been created in the wrong schema, and placing it into the correct place I had forgotten about it. That despite the fact that I even looked at that portion of code this afternoon and merely noted what it was doing, i.e. it's aim, completely missing the fact that the drop table statement was commented out. I won't say panic over because it isn't for me but it's pretty clear that I'm not kicking some corner case bug in postgresql. Thanks for everyone's input. Spectacular response times as usual. -- Nigel J. Andrews
How can you have two tables with the same name in one database ?? How do you differentiate them when you use it in queries ?? > X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org > Date: Sun, 20 Jul 2003 21:23:12 +0100 (BST) > From: "Nigel J. Andrews" <nandrews@investsystems.co.uk> > X-Sender: nandrews@ponder.fairway2k.co.uk > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Incomprehensible behaviour of a foreign key. > X-Virus-Scanned: by amavisd-new at postgresql.org > > > > On Sun, 20 Jul 2003, Nigel J. Andrews wrote: > > > Blah, blah, blah. > > > > Further hair pulling and trying many many things and I finally discovered why > this foreign key constraint problem was so weird, I looked up all pg_class > entries with that name immediately before the breaking statement. Turns out I > had another table of the same name and design in another schema. > > I even knew about that but because an earlier part of the process was moving > that data out of that extra table, which had inadvertently been created in the > wrong schema, and placing it into the correct place I had forgotten about it. > That despite the fact that I even looked at that portion of code this afternoon > and merely noted what it was doing, i.e. it's aim, completely missing the fact > that the drop table statement was commented out. > > I won't say panic over because it isn't for me but it's pretty clear that I'm > not kicking some corner case bug in postgresql. Thanks for everyone's > input. Spectacular response times as usual. > > > -- > Nigel J. Andrews > > > ---------------------------(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
On Mon, 21 Jul 2003, Kathy Zhu wrote:
> How can you have two tables with the same name in one database ??
> How do you differentiate them when you use it in queries ??
In different schemas. For example:
create schema first;
create schema second;
create table first.atable ( id serial primary key, value text );
create table second.atable ( id serial primary key, value text );
insert into first.atable (value) values ('this is in first schema');
insert into second.atable (value) values ('this is in second schema');
select * from second.atable;
select * from atable;
ERROR (possibly)
and then there is the search path:
set search_path to second, first;
select * from atable;
Gives: value == 'this is second schema'
Hope that helps.
--
Nigel J. Andrews