Inherited tables and NOT NULL (pg 7.2.1)

Inherited tables and NOT NULL (pg 7.2.1)

Post by Luke Pasco » Sat, 25 Jan 2003 10:54:49



I'm playing around with inherited tables to see if they're suitable for a
task I'm about to begin.

I want to create 6 tables, all with the same columns except 1. They will
_always_ be this way, ie. any new column added will be added to all. Sounds
perfect for inherited tables right?

Well I was trying out adding a new column to my "Parent" table, I wanted a
not null, defaulted, integer column, so I did:

temp=> ALTER TABLE Parent ADD ddd INT;
ALTER
temp=> UPDATE Parent SET ddd = 0;
UPDATE 2
temp=> ALTER TABLE Parent ALTER COLUMN ddd SET DEFAULT 0;
ALTER
temp=> ALTER TABLE Parent ADD CONSTRAINT ddd_nn CHECK (ddd IS NOT NULL);
ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint ddd_nn

Why can't I add this check to the Parent table?

========================================
Luke Pascoe
Senior Developer / Systems administrator
KMG (NZ) Limited. http://www.kmg.co.nz
Mobile: (021) 303019

========================================

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

Inherited tables and NOT NULL (pg 7.2.1)

Post by Magnus Naeslund(f » Sat, 25 Jan 2003 13:10:55



> temp=> ALTER TABLE Parent ADD CONSTRAINT ddd_nn CHECK (ddd IS NOT
> NULL); ERROR:  AlterTableAddConstraint: rejected due to CHECK
> constraint ddd_nn

> Why can't I add this check to the Parent table?

Try "CHECK (NOT (ddd IS NULL))" or "CHECK (NOT ddd IS NULL)" instead...
It might be some interpretation differances between your head and pgsql
:)
I know i have these types of constraints myself, i can dig up the
specifics if the above doesn't work.

Cheers
Magnus

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

Inherited tables and NOT NULL (pg 7.2.1)

Post by Tom La » Sat, 25 Jan 2003 15:25:05



> Well I was trying out adding a new column to my "Parent" table, I wanted a
> not null, defaulted, integer column, so I did:
> temp=> ALTER TABLE Parent ADD ddd INT;
> ALTER
> temp=> UPDATE Parent SET ddd = 0;
> UPDATE 2
> temp=> ALTER TABLE Parent ALTER COLUMN ddd SET DEFAULT 0;
> ALTER
> temp=> ALTER TABLE Parent ADD CONSTRAINT ddd_nn CHECK (ddd IS NOT NULL);
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint ddd_nn

[ scratches head ... ]  That looks like it should work.  The ALTER ADD
COLUMN should have recursively added the column to all the child tables
too, and the UPDATE should have recursively hit all the children.  So
there should be no rows left anywhere where the constraint could fail.

Could you provide a complete example?  There must be something odd about
the parent or child table schemas, which you have not showed us.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

Inherited tables and NOT NULL (pg 7.2.1)

Post by Luke Pasco » Wed, 29 Jan 2003 05:20:29


Quote:> Could you provide a complete example?  There must be something odd about
> the parent or child table schemas, which you have not showed us.

Wierd, I had dropped the temporary database I was playing with last week, so
I tried to reproduce the problem...I still can't add the CHECK but I'm
getting a different error. Full log follows:

temp=> CREATE TABLE parent (
temp(>         aaa INT NOT NULL
temp(>         );
CREATE
temp=> CREATE TABLE child (
temp(>         bbb INT NOT NULL
temp(>         ) INHERITS (parent);
CREATE
temp=> INSERT INTO child (aaa, bbb) VALUES (111, 111);
INSERT 70027 1
temp=> SELECT * FROM child;
 aaa | bbb
-----+-----
 111 | 111
(1 row)

temp=> SELECT * FROM parent;
 aaa
-----
 111
(1 row)

temp=> ALTER TABLE parent ADD ccc INT;
ALTER
temp=> UPDATE parent SET ccc = 0;
UPDATE 1
temp=> ALTER TABLE parent ALTER COLUMN ccc SET DEFAULT 0;
ALTER
temp=> ALTER TABLE parent ADD CONSTRAINT ccc_nn CHECK (ccc IS NOT NULL);
ERROR:  cache lookup of attribute 3 in relation 70023 failed
temp=>

I'm pretty sure that's exactly what I was doing before, very simple tables
to test ideas.

----- Original Message -----



Sent: Friday, January 24, 2003 6:41 PM
Subject: Re: [GENERAL] Inherited tables and NOT NULL (pg 7.2.1)


> > Well I was trying out adding a new column to my "Parent" table, I wanted
a
> > not null, defaulted, integer column, so I did:

> > temp=> ALTER TABLE Parent ADD ddd INT;
> > ALTER
> > temp=> UPDATE Parent SET ddd = 0;
> > UPDATE 2
> > temp=> ALTER TABLE Parent ALTER COLUMN ddd SET DEFAULT 0;
> > ALTER
> > temp=> ALTER TABLE Parent ADD CONSTRAINT ddd_nn CHECK (ddd IS NOT NULL);
> > ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint ddd_nn

> [ scratches head ... ]  That looks like it should work.  The ALTER ADD
> COLUMN should have recursively added the column to all the child tables
> too, and the UPDATE should have recursively hit all the children.  So
> there should be no rows left anywhere where the constraint could fail.

> Could you provide a complete example?  There must be something odd about
> the parent or child table schemas, which you have not showed us.

> regards, tom lane

---------------------------(end of broadcast)---------------------------


 
 
 

Inherited tables and NOT NULL (pg 7.2.1)

Post by Tom La » Wed, 29 Jan 2003 06:11:23



> temp=> ALTER TABLE parent ADD CONSTRAINT ccc_nn CHECK (ccc IS NOT NULL);
> ERROR:  cache lookup of attribute 3 in relation 70023 failed

What version did you say you were running?  It works fine for me in 7.3.
I can believe this has been broken in the past, though.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------

 
 
 

Inherited tables and NOT NULL (pg 7.2.1)

Post by Luke Pasco » Wed, 29 Jan 2003 06:15:30


7.2.1 - I guess I should upgrade/build, but I've been made slack by apt :-)
----- Original Message -----



Sent: Tuesday, January 28, 2003 10:09 AM
Subject: Re: [GENERAL] Inherited tables and NOT NULL (pg 7.2.1)


> > temp=> ALTER TABLE parent ADD CONSTRAINT ccc_nn CHECK (ccc IS NOT NULL);
> > ERROR:  cache lookup of attribute 3 in relation 70023 failed

> What version did you say you were running?  It works fine for me in 7.3.
> I can believe this has been broken in the past, though.

> regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

1. Inherited tables vs non-inherited tables

I've been trying an inherited-table schema for my database and seem to
be getting a performance hit. The old table looked like this:

CREATE TABLE center_out (
    subject                     text,
    arm                         char,
    target                      int4,
    rep                         int4,
    success                     int2,       -- end of primary key
    exp_date                    date,
    exp_time                    time,  
    inter_target_radius         int4,
    target_radius               int4);

Since the fields subject, arm, target, and rep appeared in just about
every other table as the primary key, I made it an inherited table for
the new schema:

CREATE TABLE center_out (
        subject    text,
        arm        char,
        target     int2,
        rep        int4,
        success    int2    
        );

CREATE UNIQUE INDEX pkcenter_out ON center_out (subject, arm, target,
rep, success);

CREATE TABLE center_out_behavior (
    exp_date                     date,
    exp_time                     time,
    inter_target_radius          int2,
    target_radius                int2
) INHERITS (center_out);

However, queries such as "SELECT DISTINCT subject FROM center_out"
seem to take 2-3 times longer in the new schema. Does this make sense?
I was hoping that the inherited information would lead to an
easier-to-follow, smaller, and faster database since there wouldn't be
redundant information across tables.

Thanks.
-Tony

 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

2. SQL language standards

3. not null column during CTAS(not null definition did not come across)

4. Setting decimal places in export

5. Building perl mods pg:PG or DBD:PG on non

6. DDQ Insert & Update And DTSLookups

7. HOWTO: Declare table field having Default Value - NULL or NOT NULL

8. Need SQL-function

9. How to change from Null to Not Null in Table Specs

10. Building perl mods pg:PG or DBD:PG on non-PostgreSQLable machines

11. How to change the table column from NULL to NOT NULL

12. NOT NULL vs NOT NULL