Thread: how is that possible

how is that possible

From
ohp@pyrenet.fr
Date:
After a typo, I've just noticed the following :

~ 14:58:33: createdb test
CREATE DATABASE
~ 14:58:42: psql test
Welcome to psql 8.1.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms      \h for help with SQL commands      \? for help with psql commands      \g
orterminate with semicolon to execute query      \q to quit
 

test=# create table t1 (i int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
test=# create table t2 (i int references t2 1 on deley te cascade on update cascade
);
CREATE TABLE
test=# insert into t2 values (default);
INSERT 0 1
test=# select * from t1;i
---
(0 rows)

test=# select * from t2;i
---

(1 row)

test=# \q

should'nt the insert fail or have I mised something?
postgresql 812 unixware 7.1.4
-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: how is that possible

From
Alvaro Herrera
Date:
ohp@pyrenet.fr wrote:

> test=# create table t1 (i int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
> CREATE TABLE
> test=# create table t2 (i int references t2 1 on deley te cascade on update cascade
> );

The t2.i column is nullable.  There is no bug here.  Declare NOT NULL if
that's what you want.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: how is that possible

From
Stephan Szabo
Date:
On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote:

> After a typo, I've just noticed the following :
>
> ~ 14:58:33: createdb test
> CREATE DATABASE
> ~ 14:58:42: psql test
> Welcome to psql 8.1.2, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> test=# create table t1 (i int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
> CREATE TABLE
> test=# create table t2 (i int references t2 1 on deley te cascade on update cascade
> );
> CREATE TABLE
> test=# insert into t2 values (default);
> INSERT 0 1
> test=# select * from t1;
>  i
> ---
> (0 rows)
>
> test=# select * from t2;
>  i
> ---
>
> (1 row)
>
> test=# \q
>
> should'nt the insert fail or have I mised something?

Why do you think it should have failed? It looks okay to me.


Re: how is that possible

From
ohp@pyrenet.fr
Date:
My understanding is that null or not, their should have been a foreign key
violation.

Maybe I misunderstood.
On Fri, 10 Feb 2006, Stephan Szabo wrote:

> Date: Fri, 10 Feb 2006 06:48:02 -0800 (PST)
> From: Stephan Szabo <sszabo@megazone.bigpanda.com>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] how is that possible
>
>
> On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote:
>
> > After a typo, I've just noticed the following :
> >
> > ~ 14:58:33: createdb test
> > CREATE DATABASE
> > ~ 14:58:42: psql test
> > Welcome to psql 8.1.2, the PostgreSQL interactive terminal.
> >
> > Type:  \copyright for distribution terms
> >        \h for help with SQL commands
> >        \? for help with psql commands
> >        \g or terminate with semicolon to execute query
> >        \q to quit
> >
> > test=# create table t1 (i int primary key);
> > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
> > CREATE TABLE
> > test=# create table t2 (i int references t2 1 on deley te cascade on update cascade
> > );
> > CREATE TABLE
> > test=# insert into t2 values (default);
> > INSERT 0 1
> > test=# select * from t1;
> >  i
> > ---
> > (0 rows)
> >
> > test=# select * from t2;
> >  i
> > ---
> >
> > (1 row)
> >
> > test=# \q
> >
> > should'nt the insert fail or have I mised something?
>
> Why do you think it should have failed? It looks okay to me.
>
>

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: how is that possible

From
Stephan Szabo
Date:
On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote:

> My understanding is that null or not, their should have been a foreign key
> violation.

Not as far as I can tell. MATCH (without partial or full) returns true if
any column in the row value constructor is NULL. MATCH FULL returns true
if all columns in the row value constructor are NULL and returns false if
it's a mix of NULLs and non-NULLs.


Re: how is that possible

From
ohp@pyrenet.fr
Date:
Many thanks for explaining.
I learned something today...

On Fri, 10 Feb 2006, Stephan Szabo wrote:

> Date: Fri, 10 Feb 2006 08:59:51 -0800 (PST)
> From: Stephan Szabo <sszabo@megazone.bigpanda.com>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] how is that possible
>
>
> On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote:
>
> > My understanding is that null or not, there should have been a foreign
key
> > violation.
>
> Not as far as I can tell. MATCH (without partial or full) returns true if
> any column in the row value constructor is NULL. MATCH FULL returns true
> if all columns in the row value constructor are NULL and returns false if
> it's a mix of NULLs and non-NULLs.
>
>

-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)


Re: how is that possible

From
Gustavo Tonini
Date:
IMHO null values shouldn't verify foreign keys constraints...

Gustavo.

2006/2/10, ohp@pyrenet.fr <ohp@pyrenet.fr>:
> Many thanks for explaining.
> I learned something today...
>
> On Fri, 10 Feb 2006, Stephan Szabo wrote:
>
> > Date: Fri, 10 Feb 2006 08:59:51 -0800 (PST)
> > From: Stephan Szabo <sszabo@megazone.bigpanda.com>
> > To: ohp@pyrenet.fr
> > Cc: pgsql-hackers list <pgsql-hackers@postgresql.org>
> > Subject: Re: [HACKERS] how is that possible
> >
> >
> > On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote:
> >
> > > My understanding is that null or not, there should have been a foreign
> key
> > > violation.
> >
> > Not as far as I can tell. MATCH (without partial or full) returns true if
> > any column in the row value constructor is NULL. MATCH FULL returns true
> > if all columns in the row value constructor are NULL and returns false if
> > it's a mix of NULLs and non-NULLs.
> >
> >
>
> --
> Olivier PRENANT                 Tel: +33-5-61-50-97-00 (Work)
> 15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
> 31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
> FRANCE                          Email: ohp@pyrenet.fr
> ------------------------------------------------------------------------------
> Make your life a dream, make your dream a reality. (St Exupery)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>