pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange bug
7+ messages / 3 participants
[nested] [flat]
* Strange bug
@ 2005-11-29 14:31 Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:37 ` Re: Strange bug Tom Lane <tgl@sss.pgh.pa.us>
2005-11-29 14:43 ` Re: Strange bug A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-11-29 14:31 UTC (permalink / raw)
To: pgsql-sql
I just noticed that I accidentally got a duplicate id. My
definitions are here:
CREATE TABLE citations (
citation_id INTEGER PRIMARY KEY,
source_fk INTEGER REFERENCES sources (source_id)
);
CREATE TABLE relation_citations (
relation_fk INTEGER REFERENCES relations (relation_id)
) INHERITS (citations);
ALTER TABLE relation_citations
ADD CONSTRAINT source_relation_citation UNIQUE
(source_fk, relation_fk);
CREATE INDEX cit_relation_key ON relation_citations (relation_fk);
CREATE TABLE event_citations (
event_fk INTEGER REFERENCES events (event_id)
) INHERITS (citations);
ALTER TABLE event_citations
ADD CONSTRAINT source_event_citation UNIQUE
(source_fk, event_fk);
CREATE INDEX cit_event_key ON event_citations (event_fk);
And here is the accident:
pgslekt=> insert into relation_citations values (64062,4578,20017);
INSERT 1478990 1
pgslekt=> insert into relation_citations values (64062,4578,20018);
INSERT 1478991 1
I got an error when I transferred the data to my Web database running
MySQL:
ERROR 1062 at line 19839 in file: 'ss_relation_citations.sql': Duplicate
entry '64062' for key 1
How can this happen?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Strange bug
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
@ 2005-11-29 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-29 14:52 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2005-11-29 14:37 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: pgsql-sql
"Leif B. Kristensen" <leif@solumslekt.org> writes:
> I just noticed that I accidentally got a duplicate id. My
> definitions are here:
> CREATE TABLE citations (
> citation_id INTEGER PRIMARY KEY,
> source_fk INTEGER REFERENCES sources (source_id)
> );
> CREATE TABLE relation_citations (
> relation_fk INTEGER REFERENCES relations (relation_id)
> ) INHERITS (citations);
relation_citations doesn't have a primary key. See
http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
particularly the "caveats" section.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Strange bug
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:37 ` Re: Strange bug Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-11-29 14:52 ` Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 16:01 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 7+ messages in thread
From: Leif B. Kristensen @ 2005-11-29 14:52 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 29 November 2005 15:37, Tom Lane wrote:
>relation_citations doesn't have a primary key. See
>http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
>particularly the "caveats" section.
Uh-oh. That's my first 'gotcha' in PostgreSQL.
I added the following constraints:
ALTER TABLE relation_citations
ADD CONSTRAINT unique_relation_citation_id UNIQUE (citation_id);
ALTER TABLE event_citations
ADD CONSTRAINT unique_event_citation_id UNIQUE (citation_id);
And I hope that will be sufficient.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Strange bug
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:37 ` Re: Strange bug Tom Lane <tgl@sss.pgh.pa.us>
2005-11-29 14:52 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
@ 2005-11-29 16:01 ` Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 16:47 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 7+ messages in thread
From: Leif B. Kristensen @ 2005-11-29 16:01 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 29 November 2005 15:52, Leif B. Kristensen wrote:
>Uh-oh. That's my first 'gotcha' in PostgreSQL.
>
>I added the following constraints:
I probably should drop both the inheritance and the citation_id
altogether, and operate with two separate tables:
CREATE TABLE relation_citations (
relation_fk INTEGER REFERENCES relations (relation_id),
source_fk INTEGER REFERENCES sources (source_id),
CONSTRAINT PRIMARY KEY (relation_fk, source_fk)
);
CREATE TABLE event_citations (
event_fk INTEGER REFERENCES events (event_id),
source_fk INTEGER REFERENCES sources (source_id),
CONSTRAINT PRIMARY KEY (event_fk, source_fk)
);
Is there an easy and non-disruptive way to do this?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Strange bug
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:37 ` Re: Strange bug Tom Lane <tgl@sss.pgh.pa.us>
2005-11-29 14:52 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 16:01 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
@ 2005-11-29 16:47 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-11-29 16:47 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 29 November 2005 17:01, Leif B. Kristensen wrote:
>Is there an easy and non-disruptive way to do this?
For the record, I just did the following:
pgslekt=> create table rel_cits (
pgslekt(> relation_fk integer references relations (relation_id),
pgslekt(> source_fk integer references sources (source_id),
pgslekt(> PRIMARY KEY (relation_fk, source_fk)
pgslekt(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"rel_cits_pkey" for table "rel_cits"
CREATE TABLE
pgslekt=> insert into rel_cits (select relation_fk, source_fk from
relation_citations);
INSERT 0 19837
pgslekt=> create table event_cits (
pgslekt(> event_fk integer references events (event_id),
pgslekt(> source_fk integer references sources (source_id),
pgslekt(> PRIMARY KEY (event_fk, source_fk)
pgslekt(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"event_cits_pkey" for table "event_cits"
CREATE TABLE
pgslekt=> insert into event_cits (select event_fk, source_fk from
event_citations);
INSERT 0 29139
pgslekt=> drop table event_citations cascade;
NOTICE: drop cascades to rule _RETURN on view event_notes
NOTICE: drop cascades to view event_notes
DROP TABLE
pgslekt=> drop table relation_citations cascade;
NOTICE: drop cascades to rule _RETURN on view relation_notes
NOTICE: drop cascades to view relation_notes
DROP TABLE
pgslekt=> drop table citations;
DROP TABLE
pgslekt=> alter table rel_cits rename to relation_citations;
ALTER TABLE
pgslekt=> alter table event_cits rename to event_citations;
ALTER TABLE
pgslekt=> \i views_and_functions.sql
Seems simple enough :-)
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Strange bug
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
@ 2005-11-29 14:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-11-29 14:57 ` Re: Strange bug Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2005-11-29 14:43 UTC (permalink / raw)
To: pgsql-sql
am 29.11.2005, um 15:31:30 +0100 mailte Leif B. Kristensen folgendes:
> CREATE TABLE citations (
> citation_id INTEGER PRIMARY KEY,
> source_fk INTEGER REFERENCES sources (source_id)
> );
>
> CREATE TABLE relation_citations (
> relation_fk INTEGER REFERENCES relations (relation_id)
> ) INHERITS (citations);
You are using inheritation in PostgreSQL.
>
> ALTER TABLE relation_citations
> ADD CONSTRAINT source_relation_citation UNIQUE
> (source_fk, relation_fk);
> CREATE INDEX cit_relation_key ON relation_citations (relation_fk);
>
> And here is the accident:
>
> pgslekt=> insert into relation_citations values (64062,4578,20017);
> INSERT 1478990 1
> pgslekt=> insert into relation_citations values (64062,4578,20018);
> INSERT 1478991 1
Right, the uniq contraints are on (source_fk, relation_fk).
No problem.
>
> I got an error when I transferred the data to my Web database running
> MySQL:
MySQL is a other RDBMS. You can't expect that all features from
PostgreSQL are working with MySQL.
>
> ERROR 1062 at line 19839 in file: 'ss_relation_citations.sql': Duplicate
> entry '64062' for key 1
My guess: MySQL can't handle inheritance.
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Strange bug
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:43 ` Re: Strange bug A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2005-11-29 14:57 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-11-29 14:57 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 29 November 2005 15:43, A. Kretschmer wrote:
>> I got an error when I transferred the data to my Web database
>> running MySQL:
>
>MySQL is a other RDBMS. You can't expect that all features from
>PostgreSQL are working with MySQL.
>
>> ERROR 1062 at line 19839 in file: 'ss_relation_citations.sql':
>> Duplicate entry '64062' for key 1
>
>My guess: MySQL can't handle inheritance.
No, it doesn't. Therefore, I have a quite different table structure in
the MySQL database, and it caught the error because of the very fact
that the citation_id is a genuine primary key in the receiving MySQL
table.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-11-29 16:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-29 14:31 Strange bug Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-29 14:52 ` Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 16:01 ` Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 16:47 ` Leif B. Kristensen <leif@solumslekt.org>
2005-11-29 14:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-11-29 14:57 ` Leif B. Kristensen <leif@solumslekt.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox