Re: Add CREATE support to event triggers - Mailing list pgsql-hackers

Alvaro Herrera escribió:

> I also fixed the sequence OWNED BY problem simply by adding support for
> ALTER SEQUENCE.  Of course, the intention is that all forms of CREATE
> and ALTER are supported, but this one seems reasonable standalone
> because CREATE TABLE uses it internally.

I have been hacking on this on and off.  This afternoon I discovered
that interval typmod output can also be pretty unusual.  Example:

create table a (a interval year to month);

For the column, we get this type spec (note the typmod):
           "coltype": {               "is_array": false,                "schemaname": "pg_catalog",
"typename":"interval",                "typmod": " year to month"           }, 
 

so the whole command output ends up being this:

NOTICE:  expanded: CREATE  TABLE  public.a (a pg_catalog."interval" year to month   )    WITH (oids=OFF)

However, this is not accepted on input:

alvherre=# CREATE  TABLE  public.a (a pg_catalog."interval" year to month   )    WITH (oids=OFF);
ERROR:  syntax error at or near "year"
LÍNEA 1: CREATE  TABLE  public.a (a pg_catalog."interval" year to mon...
        ^
 

I'm not too sure what to do about this yet.  I checked the catalogs and
gram.y, and it seems that interval is the only type that allows such
strange games to be played.  I would hate to be forced to add a kludge
specific to type interval, but that seems to be the only option.  (This
would involve checking the OID of the type in deparse_utility.c, and if
it's INTERVALOID, then omit the schema qualification and quoting on the
type name).

I have also been working on adding ALTER TABLE support.  So far it's
pretty simple; here is an example.  Note I run a single command which
includes a SERIAL column, and on output I get three commands (just like
a serial column on create table).

alvherre=# alter table tt add column b numeric, add column c serial, alter column a set default extract(epoch from
now());
NOTICE:  JSON blob: {   "definition": [       {           "clause": "cache",            "fmt": "CACHE %{value}s",
    "value": "1"       },        {           "clause": "cycle",            "fmt": "%{no}s CYCLE",            "no": "NO"
     },        {           "clause": "increment_by",            "fmt": "INCREMENT BY %{value}s",            "value":
"1"      },        {           "clause": "minvalue",            "fmt": "MINVALUE %{value}s",            "value": "1"
  },        {           "clause": "maxvalue",            "fmt": "MAXVALUE %{value}s",            "value":
"9223372036854775807"      },        {           "clause": "start",            "fmt": "START WITH %{value}s",
"value": "1"       },        {           "clause": "restart",            "fmt": "RESTART %{value}s",
"value":"1"       }   ],    "fmt": "CREATE %{persistence}s SEQUENCE %{identity}D %{definition: }s",    "identity": {
  "objname": "tt_c_seq",        "schemaname": "public"   },    "persistence": ""
 
}
NOTICE:  expanded: CREATE  SEQUENCE public.tt_c_seq CACHE 1 NO CYCLE INCREMENT BY 1 MINVALUE 1 MAXVALUE
9223372036854775807START WITH 1 RESTART 1
 
NOTICE:  JSON blob: {   "fmt": "ALTER TABLE %{identity}D %{subcmds:, }s",    "identity": {       "objname": "tt",
"schemaname": "public"   },    "subcmds": [       {           "definition": {               "collation": {
    "fmt": "COLLATE %{name}D",                    "present": false               },                "coltype": {
         "is_array": false,                    "schemaname": "pg_catalog",                    "typename": "numeric",
               "typmod": ""               },                "default": {                   "fmt": "DEFAULT
%{default}s",                   "present": false               },                "fmt": "%{name}I %{coltype}T
%{default}s%{not_null}s %{collation}s",                "name": "b",                "not_null": "",
"type":"column"           },            "fmt": "ADD COLUMN %{definition}s",            "type": "add column"       },
   {           "definition": {               "collation": {                   "fmt": "COLLATE %{name}D",
   "present": false               },                "coltype": {                   "is_array": false,
"schemaname": "pg_catalog",                    "typename": "int4",                    "typmod": ""               },
          "default": {                   "default": "pg_catalog.nextval('public.tt_c_seq'::pg_catalog.regclass)",
            "fmt": "DEFAULT %{default}s"               },                "fmt": "%{name}I %{coltype}T %{default}s
%{not_null}s%{collation}s",                "name": "c",                "not_null": "",                "type": "column"
        },            "fmt": "ADD COLUMN %{definition}s",            "type": "add column"       },        {
"column":"a",            "definition": "pg_catalog.date_part('epoch'::pg_catalog.text, pg_catalog.now())",
"fmt":"ALTER COLUMN %{column}I SET DEFAULT %{definition}s",            "type": "set default"       }   ]
 
}
NOTICE:  expanded: ALTER TABLE public.tt ADD COLUMN b pg_catalog."numeric"   , ADD COLUMN c pg_catalog.int4 DEFAULT
pg_catalog.nextval('public.tt_c_seq'::pg_catalog.regclass) , ALTER COLUMN a SET DEFAULT
pg_catalog.date_part('epoch'::pg_catalog.text,pg_catalog.now())
 
NOTICE:  JSON blob: {   "definition": [       {           "clause": "owned",            "fmt": "OWNED BY %{owner}D",
       "owner": {               "attrname": "c",                "objname": "tt",                "schemaname": "public"
        }       }   ],    "fmt": "ALTER SEQUENCE %{identity}D %{definition: }s",    "identity": {       "objname":
"tt_c_seq",       "schemaname": "public"   }
 
}
NOTICE:  expanded: ALTER SEQUENCE public.tt_c_seq OWNED BY public.tt.c
ALTER TABLE


Each subcommand is represented separately in a JSON array.  Each element
in the array has a "type" element indicating (broadly) what it's doing;
the "fmt" element has all the details.  So things like replication
systems might decide to replicate some part of the ALTER or not,
depending on the specific type.  (And, of course, they can easily decide
that replica XYZ must not replay the command because the table is not
supposed to exist there; or perhaps it belongs to a replication set that
is not the one the current node is origin for.)


-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services