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

Typed table DDL loose ends

From: Noah Misch <noah(at)leadboat(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Typed table DDL loose ends
Date: 2011-04-10 01:57:28
Message-ID: 20110410015728.GA10162@tornado.leadboat.com (view raw or flat)
Thread:
Lists: pgsql-hackers
While looking at the typed table/pg_upgrade problem, I ran into a few smaller
problems in the area.  I'm not envisioning a need for much code shift to fix
them, but there are a few points of policy.

* Table row types used in typed tables vs. ALTER TABLE
As previously noted:
  CREATE TABLE t ();
  CREATE TABLE is_a OF t;
  ALTER TABLE t ADD c int;
  \d is_a
  -- No columns

At first I thought we should just forbid the use of table row types in CREATE
TABLE OF.  However, we've been quite systematic about not distinguishing between
table row types and CREATE TYPE AS types; I've only found a distinction in ALTER
TABLE/ALTER TYPE, where we direct you to the other command.  It would be nice to
preserve this heritage.  That doesn't look particularly difficult; it may
actually yield a net code reduction.

There is a minor policy question: when should ALTER TABLE behave like ALTER TYPE
... RESTRICT, if ever?  Would using the inheritance recursion decision (driven
by ONLY, *, and sql_inheritance) be sufficiently reasonable, or do we need a
distinct signal?  I can't envision a case where you'd want to recurse to
inheritance children but error on typed table children (YMMV).  ALTER TABLE DROP
COLUMN currently uses RESTRICT/CASCADE in a completely different sense, so any
syntactic signal would need to step around that.

* Inheriting from a typed table blocks further type DDL
  CREATE TYPE t AS (x int);
  CREATE TABLE parent OF t;
  CREATE TABLE child () INHERITS (parent);
  ALTER TYPE t ADD ATTRIBUTE y int CASCADE;
  -- ERROR:  column must be added to child tables too
We ought to just set INH_YES on the downstream command in ATTypedTableRecursion.
If we get to that point, the user did choose ALTER TYPE CASCADE; it seems fair
to assume he'd want inheritance recursion rather than a later error.

* Users can CREATE TABLE OF on a type they don't own
This in turns blocks the owner's ability to alter the table/type.  However, we
already have this hazard with composite-type columns.  A TODO to address this
broadly seems in order, but it's not a 9.1 issue.

* Can create a permanent table using a temp table row type
  CREATE TEMP TABLE tempt ();
  CREATE TABLE permt OF tempt; -- silently dropped on session exit
Looks easy to fix, with no policy questions.

Does any of this appear incorrect or unreasonable?

Thanks,
nm

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2011-04-10 02:05:52
Subject: Re: using a lot of maintenance_work_mem
Previous:From: Josh BerkusDate: 2011-04-10 00:08:59
Subject: Re: Bug in pg_hba.conf or pg_basebackup concerning replication connections

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