[BUGS] BUG #5505: Busted referential integrity with triggers
The following bug has been logged online:
Bug reference: 5505
Logged by: Tommy McDaniel
Email address: tommst...@myway.com
PostgreSQL version: 8.4.4
Operating system: Kubuntu 9.10
Description:Busted referential integrity with triggers
Details:
Let us create a table as follows:
CREATE TABLE table_1 (
field_1 character varying(20) PRIMARY KEY
);
Let us create another table as follows:
CREATE TABLE table_2 (
field_2 character varying(20) PRIMARY KEY REFERENCES table_1 ON UPDATE
CASCADE
);
Let us also create a trigger to disable UPDATEs on table_2:
CREATE FUNCTION cancel_update() RETURNS trigger AS $$
BEGIN
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER cancel_update_trigger BEFORE UPDATE ON table_2
FOR EACH ROW EXECUTE PROCEDURE cancel_update();
Let us now insert some data:
INSERT INTO table_1 VALUES ('val_1');
INSERT INTO table_2 VALUES ('val_1');
It does what we expect:
testdb=# SELECT * FROM table_1;
field_1
-
val_1
(1 row)
testdb=# SELECT * FROM table_2;
field_2
-
val_1
(1 row)
Now we decide to change the value in table_1:
UPDATE table_1 SET field_1 = 'val_2' WHERE field_1 = 'val_1';
Now let's see what values we have in the database:
testdb=# SELECT * FROM table_1;
field_1
-
val_2
(1 row)
testdb=# SELECT * FROM table_2;
field_2
-
val_1
(1 row)
And, we have now broken referential integrity. I expected that ON UPDATE
CASCADE would ignore the trigger. Failing that, I would still expect the
foreign key constraint to be checked and raise an error. Neither appears to
be happening, so we're silently getting busted referential integrity. This
makes me sad.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5506: Error in the grammar of de joins
The following bug has been logged online:
Bug reference: 5506
Logged by: Fernando Cano
Email address: fc...@uniovi.es
PostgreSQL version: 8.4
Operating system: Ubuntu 9.04
Description:Error in the grammar of de joins
Details:
This sentences are valid with your grammar but generate an error.
create table t1 ( id_t1 smallint, name text);
create table t2 ( id_t2 smallint, name text);
select * from natural join using (id) ;
select * from t1 natural cross join t2;
select * from natural cross join using (id) ;
select * from t1 join t2;
select t1 natural left join t2;
I have problems when I explain to my students the sintax of the joins.
I use this sintax, but I'm not sure:
from_item {CROSS | NATURAL join_type } JOIN from_item
from_item join_type JOIN from_item [ ON join_condition | USING ( join_column
[, ...] ) ]
where join_type is:
[ INNER ] | {LEFT |RIGHT | FULL} [ OUTER ]
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5507: missing chunk number 0 for toast value XXXXX in pg_toast_XXXXX
The following bug has been logged online: Bug reference: 5507 Logged by: Shinji Nakajima Email address: sina...@jops.co.jp PostgreSQL version: 8.3.8 Operating system: Red Hat Enterprise Linux Server release 5.3 (Tikanga) Description:missing chunk number 0 for toast value X in pg_toast_X Details: Error message called "missing chunk number" occurred when I did select of the specific column of the specific table. I did not update this record, but was in such a condition suddenly. There seems to be the person that a similar phenomenon occurs."http://www.ruizs.org/archives/138"; I delete a record, and the system restores, but prime cause is unknown. Will this be a bug of the databases? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5505: Busted referential integrity with triggers
"Tommy McDaniel" writes: > Let us also create a trigger to disable UPDATEs on table_2: > ... > And, we have now broken referential integrity. Yup, this is not a bug, it's a feature. Triggers fire on referential-integrity updates. (If they didn't, you could not for example have a logging trigger log RI actions.) If you don't want to break RI, you'd better think more carefully about what your trigger does. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5506: Error in the grammar of de joins
"Fernando Cano" writes: > This sentences are valid with your grammar but generate an error. > create table t1 ( id_t1 smallint, name text); > create table t2 ( id_t2 smallint, name text); > select * from natural join using (id) ; > select * from t1 natural cross join t2; > select * from natural cross join using (id) ; > select * from t1 join t2; > select t1 natural left join t2; Uh, no, they're *not* valid with our grammar --- that's why you're getting errors. They're not valid according to the SQL standard either, so I'm not sure exactly what your point is. > I have problems when I explain to my students the sintax of the joins. The SQL92 standard defines join syntax like this: ::= | | ::= CROSS JOIN ::= [ NATURAL ] [ ] JOIN [ ] ::= | ::= ON ::= USING ::= INNER | [ OUTER ] | UNION ::= LEFT | RIGHT | FULL ::= The restriction that NATURAL can't appear with ON or USING isn't in this syntax diagram, though; it's explained in the text. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5507: missing chunk number 0 for toast value XXXXX in pg_toast_XXXXX
"Shinji Nakajima" writes: > Error message called "missing chunk number" occurred when I did select of > the specific column of the specific table. This might indicate that the toast table's index was corrupted. > I delete a record, and the system restores, but prime cause is unknown. > Will this be a bug of the databases? Perhaps, but there's not a lot we can do without a lot more information... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5507: missing chunk number 0 for toast value XXXXX in pg_toast_XXXXX
"Shinji Nakajima" wrote: > Error message called "missing chunk number" occurred when I did > select of the specific column of the specific table. > I delete a record, and the system restores, but prime cause is > unknown. Will this be a bug of the databases? Errors like this are usually caused by hardware problems. I think the second-most common cause is running in a configuration with fsync = off or full_page_writes = off, and suffering a power outage or OS crash. I would recommend that you check your configuration for these unsafe settings and schedule a check of your hardware and drivers. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: BUG #5507: missing chunk number 0 for toast value XXXXX in pg_toast_XXXXX
On Mon, Jun 14, 2010 at 11:28 AM, Shinji Nakajima wrote: > PostgreSQL version: 8.3.8 > Description: missing chunk number 0 for toast value X in > pg_toast_X > > I delete a record, and the system restores, but prime cause is unknown. > Will this be a bug of the databases? Probably. Or possibly bad hardware. Assuming you didn't manually go in and delete that record from the toast table, which would be a strange thing to do. The problem is it could have happened a long time ago and you just discovered it now. Have you had any other significant events on this machine? Any system crashes or power failures? Any drive crashes or signs of bad memory? In the postgres logs are there any instances of unusual error messages or warnings? -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5505: Busted referential integrity with triggers
I can understand firing the triggers. But what's up with not checking that the foreign key constraint is met? If the user has to manually ensure that values maintain referential integrity, why have foreign keys at all? The whole point of foreign keys is to make the database ensure referential integrity is maintained instead of having to do it manually. Tommy McDaniel -Original Message- From: "Tom Lane" [...@sss.pgh.pa.us] Date: 06/14/2010 08:13 AM To: "Tommy McDaniel" CC: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #5505: Busted referential integrity with triggers "Tommy McDaniel" writes: > Let us also create a trigger to disable UPDATEs on table_2: > ... > And, we have now broken referential integrity. Yup, this is not a bug, it's a feature. Triggers fire on referential-integrity updates. (If they didn't, you could not for example have a logging trigger log RI actions.) If you don't want to break RI, you'd better think more carefully about what your trigger does. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5505: Busted referential integrity with triggers
"Tommy McDaniel" writes: > I can understand firing the triggers. But what's up with not checking that > the foreign key constraint is met? If the user has to manually ensure that > values maintain referential integrity, why have foreign keys at all? The > whole point of foreign keys is to make the database ensure referential > integrity is maintained instead of having to do it manually. [ shrug... ] The database is doing its best. Do you really want us to incur the extra overhead of checking that a trigger didn't screw things up? Exactly how far should that go? For instance, maybe we have to check that the trigger didn't queue a subsequent event that will make the undesired change after we look? I can assure you that far more people would complain about the useless overhead induced by rechecking than will complain about the fact that they can write triggers that will fire on RI updates. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
