Thread: Strange bug

Strange bug

From

"Leif B. Kristensen"

Date:

29 November 2005, 13:31:33

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

Re: Strange bug

From

Tom Lane

Date:

29 November 2005, 13:37:43

"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

Re: Strange bug

From

"A. Kretschmer"

Date:

29 November 2005, 13:43:52

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    ===

Re: Strange bug

From

"Leif B. Kristensen"

Date:

29 November 2005, 13:52:55

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

Re: Strange bug

From

"Leif B. Kristensen"

Date:

29 November 2005, 13:57:17

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

Re: Strange bug

From

"Leif B. Kristensen"

Date:

29 November 2005, 15:01:37

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
REFERENCESsources (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

Re: Strange bug

From

"Leif B. Kristensen"

Date:

29 November 2005, 15:47:55

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

Help needed

From

NosyMan

Date:

30 November 2005, 08:19:16

Hi everyone,

I have some kind of strange problem. I want to build general parametrized 
trigger for my database. The parameter sent to the trigger contains a column 
name that trigger should be check.  

CREATE OR REPLACE FUNCTION F_T_IU__check() RETURNS trigger AS $$DECLARE    vt VARCHAR;BEGIN
...........................................................        vt:='constant_value_id';        RAISE EXCEPTION
'aaa=%',NEW.vt;
 
.............................................................

The 'vt' variable contains the column name. In the above statement I want that 
NEW.vt to be interpreted as NEW.constant_value_id. It is possible to do this?

Thanks,
Nosy

Re: Help needed

From

Richard Huxton

Date:

30 November 2005, 09:35:57

NosyMan wrote:
> Hi everyone,
> 
> I have some kind of strange problem. I want to build general parametrized 
> trigger for my database. The parameter sent to the trigger contains a column 
> name that trigger should be check.  

> The 'vt' variable contains the column name. In the above statement I want that 
> NEW.vt to be interpreted as NEW.constant_value_id. It is possible to do this?

Not in pl/pgsql - try one of the more interpreted languages - 
tcl/perl/python etc.

Oh, and try not to reply to a previous message when starting a new 
thread - it can mess with threading in some mail packages.
--   Richard Huxton  Archonet Ltd