Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] possible bug with inheritance?

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [GENERAL] possible bug with inheritance?
Date: 2010-02-16 15:36:02
Message-ID: 201002161536.o1GFa2708291@momjian.us (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-generalpgsql-hackers
Bruce Momjian wrote:
> For primary key, there is no enforcement of the primary key, e.g.:
> 
> 	test=> CREATE TABLE parent (name TEXT);
> 	CREATE TABLE
> 	test=> CREATE TABLE child (age INT) inherits (parent) ;
> 	CREATE TABLE
> 	test=> ALTER TABLE parent ADD primary KEY (name);
> 	NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> 	"parent_pkey" for table "parent"
> 	ALTER TABLE
> 	test=> INSERT INTO parent (name) VALUES ('a');
> 	INSERT 0 1
> 	test=> INSERT INTO child (name) VALUES ('a');
> 	INSERT 0 1
> 	test=> SELECT * FROM parent;
> 	 name
> 	------
> 	 a
> 	 a
> 	(2 rows)
> 
> So, it seems like this is the ugly truth of our inheritance limitations
> with primary key, and unless we can fix the primary key issues with
> inheritance, our current behavior is the more predictable we can hope for.	

[  Thread moved to hackers because this might be a valid bug. ]

Summary:  ALTER TABLE SET NOT NULL on a parent table is passed to the
child, while ALTER TABLE ADD PRIMARY KEY is not, particularly the NOT
NULL part of the PRIMARY KEY specification.

OK, now I understand what you are getting at --- the following returns a
NULL value from the parent:

	test=> CREATE TABLE parent (name text);
	CREATE TABLE
	test=> CREATE TABLE child (age int) INHERITS (parent) ;
	CREATE TABLE
	test=> ALTER TABLE parent ADD PRIMARY KEY (name);
	NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
	"parent_pkey" for table "parent"
	ALTER TABLE
	test=> INSERT INTO child (name) VALUES (null);
	INSERT 0 1
	test=> \pset null '(null)'
	Null display is "(null)".
	test=> SELECT * FROM parent;
	  name
	--------
	 (null)
	(1 row)

while the parent has a NOT NULL specification:

	test=> \d parent
	   Table "public.parent"
	 Column | Type | Modifiers
	--------+------+-----------
	 name   | text | not null
	Indexes:
	    "parent_pkey" PRIMARY KEY, btree (name)
	Number of child tables: 1 (Use \d+ to list them.)

That does seem like something that should be fixed.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-02-16 15:38:40
Subject: Re: LISTEN/NOTIFY and notification timing guarantees
Previous:From: Bruce MomjianDate: 2010-02-16 15:24:01
Subject: Re: possible bug with inheritance?

pgsql-general by date

Next:From: Tom LaneDate: 2010-02-16 15:45:20
Subject: Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion
Previous:From: Bruce MomjianDate: 2010-02-16 15:24:01
Subject: Re: possible bug with inheritance?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group