problem with foreign key constrain violation
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
Hi MonetDB people,
again I'm confused. Please have a look below. Maybe my SQL language knowledge is not sufficient, but I'm expecting one row for the last statement. Any idea? Btw, I'm using Feb2013-sp1.
sql>\d auth_user CREATE TABLE "sys"."auth_user" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_5989", "password" VARCHAR(128) NOT NULL, "last_login" TIMESTAMP NOT NULL, "is_superuser" BOOLEAN NOT NULL, "username" VARCHAR(30) NOT NULL, "first_name" VARCHAR(30) NOT NULL, "last_name" VARCHAR(30) NOT NULL, "email" VARCHAR(75) NOT NULL, "is_staff" BOOLEAN NOT NULL, "is_active" BOOLEAN NOT NULL, "date_joined" TIMESTAMP NOT NULL, CONSTRAINT "auth_user_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "auth_user_username_unique" UNIQUE ("username") );
sql>SELECT id,username FROM auth_user; +------+----------+ | id | username | +======+==========+ | 8 | joe | +------+----------+ 1 tuple (1.183ms)
sql>SELECT id,username FROM auth_user WHERE username='joe'; +------+----------+ | id | username | +======+==========+ | 8 | joe | | 8 | joe | +------+----------+ 2 tuples (0.907ms)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On 2013-04-23 14:30, Gijs Molenaar wrote:
Hi MonetDB people,
again I'm confused. Please have a look below. Maybe my SQL language knowledge is not sufficient, but I'm expecting one row for the last statement. Any idea? Btw, I'm using Feb2013-sp1.
Can you try Feb2013-SP2 from http://dev.monetdb.org/downloads/testing/? I can't quickly reproduce the problem.
sql>\d auth_user CREATE TABLE "sys"."auth_user" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_5989", "password" VARCHAR(128) NOT NULL, "last_login" TIMESTAMP NOT NULL, "is_superuser" BOOLEAN NOT NULL, "username" VARCHAR(30) NOT NULL, "first_name" VARCHAR(30) NOT NULL, "last_name" VARCHAR(30) NOT NULL, "email" VARCHAR(75) NOT NULL, "is_staff" BOOLEAN NOT NULL, "is_active" BOOLEAN NOT NULL, "date_joined" TIMESTAMP NOT NULL, CONSTRAINT "auth_user_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "auth_user_username_unique" UNIQUE ("username") );
sql>SELECT id,username FROM auth_user; +------+----------+ | id | username | +======+==========+ | 8 | joe | +------+----------+ 1 tuple (1.183ms)
sql>SELECT id,username FROM auth_user WHERE username='joe'; +------+----------+ | id | username | +======+==========+ | 8 | joe | | 8 | joe | +------+----------+ 2 tuples (0.907ms)
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender
On 23/04/13 15:11, Sjoerd Mullender wrote:
On 2013-04-23 14:30, Gijs Molenaar wrote:
Hi MonetDB people,
again I'm confused. Please have a look below. Maybe my SQL language knowledge is not sufficient, but I'm expecting one row for the last statement. Any idea? Btw, I'm using Feb2013-sp1.
Can you try Feb2013-SP2 from http://dev.monetdb.org/downloads/testing/? I can't quickly reproduce the problem.
This did not solve the problem. The same as for the other issue, I can't replicate the problem in isolation also.
sql>\d auth_user CREATE TABLE "sys"."auth_user" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_5989", "password" VARCHAR(128) NOT NULL, "last_login" TIMESTAMP NOT NULL, "is_superuser" BOOLEAN NOT NULL, "username" VARCHAR(30) NOT NULL, "first_name" VARCHAR(30) NOT NULL, "last_name" VARCHAR(30) NOT NULL, "email" VARCHAR(75) NOT NULL, "is_staff" BOOLEAN NOT NULL, "is_active" BOOLEAN NOT NULL, "date_joined" TIMESTAMP NOT NULL, CONSTRAINT "auth_user_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "auth_user_username_unique" UNIQUE ("username") );
sql>SELECT id,username FROM auth_user; +------+----------+ | id | username | +======+==========+ | 8 | joe | +------+----------+ 1 tuple (1.183ms)
sql>SELECT id,username FROM auth_user WHERE username='joe'; +------+----------+ | id | username | +======+==========+ | 8 | joe | | 8 | joe | +------+----------+ 2 tuples (0.907ms)
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Please file a bug report with all information / definitions / scripts / data required to reproduce the problem.
Thanks!
Stefan
On Tue, Apr 23, 2013 at 12:51:08PM +0200, Gijs Molenaar wrote:
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Stefan,
I've tried, but it is a bit hard/currently impossible to reproduce these problem in isolation. I'm running the Django test suite with MonetDB as a backend, which does a huge amount of operations on the database. Then after a while this kind of problems start to pop up. I can give you instructions on how to run the django/djonet test suite if you want.
- Gijs
On 23/04/13 15:07, Stefan Manegold wrote:
Please file a bug report with all information / definitions / scripts / data required to reproduce the problem.
Thanks!
Stefan
On Tue, Apr 23, 2013 at 12:51:08PM +0200, Gijs Molenaar wrote:
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
On Tue, Apr 23, 2013 at 04:43:53PM +0200, Gijs Molenaar wrote:
Hi Stefan,
I've tried, but it is a bit hard/currently impossible to reproduce these problem in isolation. I'm running the Django test suite with MonetDB as a backend, which does a huge amount of operations on the database. Then after a while this kind of problems start to pop up. I can give you instructions on how to run the django/djonet test suite if you want.
Gijs, Stefan
I found a bug in concurrent use which may very well also be your problem. The easiest way to reproduce a problem (related to this bug), is to start a mclient and do a start transaction. Then run your scripts which give problems (should have updates in it). This will cause problems. Fix will be checked into Feb2013 today..
Niels
- Gijs
On 23/04/13 15:07, Stefan Manegold wrote:
Please file a bug report with all information / definitions / scripts / data required to reproduce the problem.
Thanks!
Stefan
On Tue, Apr 23, 2013 at 12:51:08PM +0200, Gijs Molenaar wrote:
Hi MonetDB,
I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong?
sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") );
sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms)
sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms)
sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Gijs Molenaar -
Niels Nes -
Sjoerd Mullender -
Stefan Manegold