Re: WIP: generalized index constraints

Hi,

Le 21 août 09 à 06:04, Jeff Davis a écrit :
> There is not much of a problem with backwards compatibility. LIKE is
> shorthand (not stored in catalogs), so it doesn't affect
> pg_dump/restore. And hopefully there aren't a lot of apps out there
> creating tables dynamically using the LIKE syntax.

I for one use this a lot, every time I'm doing partitioning. What I do
is a plpgsql function creating partitions for a given period
(create_parts(date, date) and default interval with
create_parts(date)), and the function will EXECUTE something like this:

CREATE TABLE schema.partition_YYYYMM (
LIKE schema.parent INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING
CONSTRAINTS,
CHECK ( partition check expression )
)
INHERITS( schema.parent );

The reason to do this is that inherits won't care at all about the
indexes, defaults and constraints. The drawback to doing it this way
is the cheer number of NOTICEs you get back at inherits time when PG
is so verbose about finding that child already has all the parents
columns. From 8.3 onwards it's possible to trick the system though:

CREATE FUNCTION ... ()
RETURNS ...
LANGUAGE plpgsql
SET client_min_messages TO warning
AS $$
$$;

Regards,
--
dim