pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed7.1beta4 bug creating a certain table
5+ messages / 4 participants
[nested] [flat]
* 7.1beta4 bug creating a certain table
@ 2001-01-29 21:07 Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 2 replies; 5+ messages in thread
From: Kovacs Zoltan @ 2001-01-29 21:07 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; pgsql-sql
I cannot create a certain table in 7.1beta4. With 7.0.2 there was not any
problem.
------------------------------------------------------------------------------
CREATE TABLE pakolas_cikktetel (
pakolas int4 not null,
cikk int4 not null,
minoseg int4 not null,
sorszam int4 check (sorszam > 0),
helyrol int4,
helyre int4,
mennyi numeric(14,4) not null ,
lezarva bool default 'f',
primary key (pakolas, cikk, minoseg, sorszam),
unique (pakolas, cikk, minoseg, helyrol, helyre));
CREATE TABLE keszlet_bevetel (
keszletnovekedes int4 not null primary key,
pakolas int4 not null,
cikk int4 not null,
minoseg int4 NOT NULL,
foreign key (pakolas, cikk, minoseg)
references pakolas_cikktetel(pakolas, cikk, minoseg));
------------------------------------------------------------------------------
Here I get the following error:
ERROR: UNIQUE constraint matching given keys for referenced table
"pakolas_cikktetel" not found
(The second table will not be created.)
By the way, is it possible generating patches between beta releases and
putting on the ftp server?
TIA, Zoltan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.1beta4 bug creating a certain table
@ 2001-01-29 22:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2001-01-29 22:32 UTC (permalink / raw)
To: Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> CREATE TABLE pakolas_cikktetel (
> pakolas int4 not null,
> cikk int4 not null,
> minoseg int4 not null,
> sorszam int4 check (sorszam > 0),
> helyrol int4,
> helyre int4,
> mennyi numeric(14,4) not null ,
> lezarva bool default 'f',
> primary key (pakolas, cikk, minoseg, sorszam),
> unique (pakolas, cikk, minoseg, helyrol, helyre));
> CREATE TABLE keszlet_bevetel (
> keszletnovekedes int4 not null primary key,
> pakolas int4 not null,
> cikk int4 not null,
> minoseg int4 NOT NULL,
> foreign key (pakolas, cikk, minoseg)
> references pakolas_cikktetel(pakolas, cikk, minoseg));
> ERROR: UNIQUE constraint matching given keys for referenced table
> "pakolas_cikktetel" not found
It appears to me that this is correct, since there is no constraint
on the first column that says that those three columns form a unique
key *by themselves*. I believe there were bugs in the code that checked
for this error before ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.1beta4 bug creating a certain table
@ 2001-01-30 16:55 Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Kovacs Zoltan @ 2001-01-30 16:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql; ltibor@mail.tiszanet.hu
On Mon, 29 Jan 2001, Tom Lane wrote:
> Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> > CREATE TABLE pakolas_cikktetel (
> > pakolas int4 not null,
> > cikk int4 not null,
> > minoseg int4 not null,
> > sorszam int4 check (sorszam > 0),
> > helyrol int4,
> > helyre int4,
> > mennyi numeric(14,4) not null ,
> > lezarva bool default 'f',
> > primary key (pakolas, cikk, minoseg, sorszam),
> > unique (pakolas, cikk, minoseg, helyrol, helyre));
>
> > CREATE TABLE keszlet_bevetel (
> > keszletnovekedes int4 not null primary key,
> > pakolas int4 not null,
> > cikk int4 not null,
> > minoseg int4 NOT NULL,
> > foreign key (pakolas, cikk, minoseg)
> > references pakolas_cikktetel(pakolas, cikk, minoseg));
>
> > ERROR: UNIQUE constraint matching given keys for referenced table
> > "pakolas_cikktetel" not found
>
> It appears to me that this is correct, since there is no constraint
> on the first column that says that those three columns form a unique
> key *by themselves*. I believe there were bugs in the code that checked
> for this error before ...
>
> regards, tom lane
>
I don't know the exact SQL definition whether my declaration is correct or
not. But, checking our model, we realized that our implementation is not
correct. So I should rewrite these definitions. Thanks! :-)
But, if this declaration is not correct in the sense of SQL standards, I
can imagine that PostgreSQL may allow such declarations. (A NOTICE may be
given instead of an ERROR.) Or, are there any drawbacks of allowing this?
Zoltan
--
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 7.1beta4 bug creating a certain table
@ 2001-01-30 18:19 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-01-30 18:19 UTC (permalink / raw)
To: Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; pgsql-sql; ltibor@mail.tiszanet.hu
On Tue, 30 Jan 2001, Kovacs Zoltan wrote:
> > It appears to me that this is correct, since there is no constraint
> > on the first column that says that those three columns form a unique
> > key *by themselves*. I believe there were bugs in the code that checked
> > for this error before ...
> >
> > regards, tom lane
> >
> I don't know the exact SQL definition whether my declaration is correct or
> not. But, checking our model, we realized that our implementation is not
> correct. So I should rewrite these definitions. Thanks! :-)
>
> But, if this declaration is not correct in the sense of SQL standards, I
> can imagine that PostgreSQL may allow such declarations. (A NOTICE may be
> given instead of an ERROR.) Or, are there any drawbacks of allowing this?
Yes. The columns *must* be unique because the logic that the foreign keys
are defined to use doesn't really make sense for match unspecified and
match full if they can have duplicates.
You'd need logic like that for match partial for all of the cases for it
to really make sense. For example, if you can have two pk rows with key
values (1,2) and a fk row (1,2) and you update one of the two pk rows to
(2,3) what happens on an on update cascade? Match partial still
"requires" that the keys be unique but since fk rows can already match
more than one pk row it's probably not as big a deal. Of course, match
partial is a real pain because you need to keep stuff around about what
*other* rows were modified during the statement while you're running the
trigger.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] 7.1beta4 bug creating a certain table
@ 2001-01-30 21:36 Najm Hashmi <najm@mondo-live.com>
parent: Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
1 sibling, 0 replies; 5+ messages in thread
From: Najm Hashmi @ 2001-01-30 21:36 UTC (permalink / raw)
To: Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql
Hi, I ran the same code on 7.1beta 3 and it works... I don't have beta4
installed...
newdb=# CREATE TABLE pakolas_cikktetel (
newdb(# pakolas int4 not null,
newdb(# cikk int4 not null,
newdb(# minoseg int4 not null,
newdb(# sorszam int4 check (sorszam > 0),
newdb(# helyrol int4,
newdb(# helyre int4,
newdb(# mennyi numeric(14,4) not null ,
newdb(# lezarva bool default 'f',
newdb(# primary key (pakolas, cikk, minoseg, sorszam),
newdb(# unique (pakolas, cikk, minoseg, helyrol, helyre));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'pakolas_cikktetel_pkey' for table 'pakolas_cikktetel'
NOTICE: CREATE TABLE/UNIQUE will create implicit index
'pakolas_cikktetel_pakolas_key' for table 'pakolas_cikktetel'
CREATE
Najm
Kovacs Zoltan wrote:
> I cannot create a certain table in 7.1beta4. With 7.0.2 there was not any
> problem.
>
> ------------------------------------------------------------------------------
> CREATE TABLE pakolas_cikktetel (
> pakolas int4 not null,
> cikk int4 not null,
> minoseg int4 not null,
> sorszam int4 check (sorszam > 0),
> helyrol int4,
> helyre int4,
> mennyi numeric(14,4) not null ,
> lezarva bool default 'f',
> primary key (pakolas, cikk, minoseg, sorszam),
> unique (pakolas, cikk, minoseg, helyrol, helyre));
>
> CREATE TABLE keszlet_bevetel (
> keszletnovekedes int4 not null primary key,
> pakolas int4 not null,
> cikk int4 not null,
> minoseg int4 NOT NULL,
> foreign key (pakolas, cikk, minoseg)
> references pakolas_cikktetel(pakolas, cikk, minoseg));
> ------------------------------------------------------------------------------
>
> Here I get the following error:
>
> ERROR: UNIQUE constraint matching given keys for referenced table
> "pakolas_cikktetel" not found
>
> (The second table will not be created.)
>
> By the way, is it possible generating patches between beta releases and
> putting on the ftp server?
>
> TIA, Zoltan
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-01-30 21:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-29 21:07 7.1beta4 bug creating a certain table Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
2001-01-29 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-30 16:55 ` Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
2001-01-30 18:19 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-30 21:36 ` Najm Hashmi <najm@mondo-live.com>
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