doc/src/sgml/event-trigger.sgml

   1 <!-- doc/src/sgml/event-trigger.sgml -->
   2
   3  <chapter id="event-triggers">
   4   <title>Event Triggers</title>
   5
   6   <indexterm zone="event-triggers">
   7    <primary>event trigger</primary>
   8   </indexterm>
   9
  10   <para>
  11    To supplement the trigger mechanism discussed in <xref linkend="triggers"/>,
  12    <productname>PostgreSQL</productname> also provides event triggers.  Unlike regular
  13    triggers, which are attached to a single table and capture only DML events,
  14    event triggers are global to a particular database and are capable of
  15    capturing DDL events.
  16   </para>
  17
  18   <para>
  19    Like regular triggers, event triggers can be written in any procedural
  20    language that includes event trigger support, or in C, but not in plain
  21    SQL.
  22   </para>
  23
  24   <sect1 id="event-trigger-definition">
  25    <title>Overview of Event Trigger Behavior</title>
  26
  27    <para>
  28      An event trigger fires whenever the event with which it is associated
  29      occurs in the database in which it is defined. Currently, the only
  30      supported events are
  31      <literal>login</literal>,
  32      <literal>ddl_command_start</literal>,
  33      <literal>ddl_command_end</literal>,
  34      <literal>table_rewrite</literal>
  35      and <literal>sql_drop</literal>.
  36      Support for additional events may be added in future releases.
  37    </para>
  38
  39    <para>
  40      The <literal>login</literal> event occurs when an authenticated user logs
  41      into the system. Any bug in a trigger procedure for this event may
  42      prevent successful login to the system. Such bugs may be worked around by
  43      setting <xref linkend="guc-event-triggers"/> to <literal>false</literal>
  44      either in a connection string or configuration file. Alternatively, you can
  45      restart the system in single-user mode (as event triggers are
  46      disabled in this mode). See the <xref linkend="app-postgres"/> reference
  47      page for details about using single-user mode.
  48      The <literal>login</literal> event will also fire on standby servers.
  49      To prevent servers from becoming inaccessible, such triggers must avoid
  50      writing anything to the database when running on a standby.
  51      Also, it's recommended to avoid long-running queries in
  52      <literal>login</literal> event triggers.  Note that, for instance,
  53      canceling a connection in <application>psql</application> will not cancel
  54      the in-progress <literal>login</literal> trigger.
  55    </para>
  56
  57    <para>
  58      The <literal>ddl_command_start</literal> event occurs just before the
  59      execution of a <literal>CREATE</literal>, <literal>ALTER</literal>, <literal>DROP</literal>,
  60      <literal>SECURITY LABEL</literal>,
  61      <literal>COMMENT</literal>, <literal>GRANT</literal> or <literal>REVOKE</literal>
  62      command.  No check whether the affected object exists or doesn't exist is
  63      performed before the event trigger fires.
  64      As an exception, however, this event does not occur for
  65      DDL commands targeting shared objects &mdash; databases, roles, and tablespaces
  66      &mdash; or for commands targeting event triggers themselves.  The event trigger
  67      mechanism does not support these object types.
  68      <literal>ddl_command_start</literal> also occurs just before the execution of a
  69      <literal>SELECT INTO</literal> command, since this is equivalent to
  70      <literal>CREATE TABLE AS</literal>.
  71    </para>
  72
  73    <para>
  74     The <literal>ddl_command_end</literal> event occurs just after the execution of
  75     this same set of commands.  To obtain more details on the <acronym>DDL</acronym>
  76     operations that took place, use the set-returning function
  77     <literal>pg_event_trigger_ddl_commands()</literal> from the
  78     <literal>ddl_command_end</literal> event trigger code (see
  79     <xref linkend="functions-event-triggers"/>).  Note that the trigger fires
  80     after the actions have taken place (but before the transaction commits),
  81     and thus the system catalogs can be read as already changed.
  82    </para>
  83
  84    <para>
  85     The <literal>sql_drop</literal> event occurs just before the
  86     <literal>ddl_command_end</literal> event trigger for any operation that drops
  87     database objects.  To list the objects that have been dropped, use the
  88     set-returning function <literal>pg_event_trigger_dropped_objects()</literal> from the
  89     <literal>sql_drop</literal> event trigger code (see
  90     <xref linkend="functions-event-triggers"/>). Note that
  91     the trigger is executed after the objects have been deleted from the
  92     system catalogs, so it's not possible to look them up anymore.
  93    </para>
  94
  95    <para>
  96     The <literal>table_rewrite</literal> event occurs just before a table is
  97     rewritten by some actions of the commands <literal>ALTER TABLE</literal> and
  98     <literal>ALTER TYPE</literal>.  While other
  99     control statements are available to rewrite a table,
 100     like <literal>CLUSTER</literal> and <literal>VACUUM</literal>,
 101     the <literal>table_rewrite</literal> event is not triggered by them.
 102    </para>
 103
 104    <para>
 105      Event triggers (like other functions) cannot be executed in an aborted
 106      transaction.  Thus, if a DDL command fails with an error, any associated
 107      <literal>ddl_command_end</literal> triggers will not be executed.  Conversely,
 108      if a <literal>ddl_command_start</literal> trigger fails with an error, no
 109      further event triggers will fire, and no attempt will be made to execute
 110      the command itself.  Similarly, if a <literal>ddl_command_end</literal> trigger
 111      fails with an error, the effects of the DDL statement will be rolled
 112      back, just as they would be in any other case where the containing
 113      transaction aborts.
 114    </para>
 115
 116    <para>
 117      For a complete list of commands supported by the event trigger mechanism,
 118      see <xref linkend="event-trigger-matrix"/>.
 119    </para>
 120
 121    <para>
 122      Event triggers are created using the command <xref linkend="sql-createeventtrigger"/>.
 123      In order to create an event trigger, you must first create a function with
 124      the special return type <literal>event_trigger</literal>.  This function
 125      need not (and may not) return a value; the return type serves merely as
 126      a signal that the function is to be invoked as an event trigger.
 127    </para>
 128
 129    <para>
 130      If more than one event trigger is defined for a particular event, they will
 131      fire in alphabetical order by trigger name.
 132    </para>
 133
 134    <para>
 135      A trigger definition can also specify a <literal>WHEN</literal>
 136      condition so that, for example, a <literal>ddl_command_start</literal>
 137      trigger can be fired only for particular commands which the user wishes
 138      to intercept. A common use of such triggers is to restrict the range of
 139      DDL operations which users may perform.
 140    </para>
 141   </sect1>
 142
 143   <sect1 id="event-trigger-matrix">
 144    <title>Event Trigger Firing Matrix</title>
 145
 146    <para>
 147      <xref linkend="event-trigger-by-command-tag"/> lists all commands
 148      for which event triggers are supported.
 149    </para>
 150
 151    <table id="event-trigger-by-command-tag">
 152      <title>Event Trigger Support by Command Tag</title>
 153      <tgroup cols="6">
 154       <colspec colname="col1" colwidth="2*"/>
 155       <colspec colname="col2" colwidth="1*"/>
 156       <colspec colname="col3" colwidth="1*"/>
 157       <colspec colname="col4" colwidth="1*"/>
 158       <colspec colname="col5" colwidth="1*"/>
 159       <colspec colname="col6" colwidth="1*"/>
 160       <thead>
 161        <row>
 162         <entry>Command Tag</entry>
 163         <entry><literal>ddl_&zwsp;command_&zwsp;start</literal></entry>
 164         <entry><literal>ddl_&zwsp;command_&zwsp;end</literal></entry>
 165         <entry><literal>sql_&zwsp;drop</literal></entry>
 166         <entry><literal>table_&zwsp;rewrite</literal></entry>
 167         <entry>Notes</entry>
 168        </row>
 169       </thead>
 170       <tbody>
 171        <row>
 172         <entry align="left"><literal>ALTER AGGREGATE</literal></entry>
 173         <entry align="center"><literal>X</literal></entry>
 174         <entry align="center"><literal>X</literal></entry>
 175         <entry align="center"><literal>-</literal></entry>
 176         <entry align="center"><literal>-</literal></entry>
 177         <entry align="left"></entry>
 178        </row>
 179        <row>
 180         <entry align="left"><literal>ALTER COLLATION</literal></entry>
 181         <entry align="center"><literal>X</literal></entry>
 182         <entry align="center"><literal>X</literal></entry>
 183         <entry align="center"><literal>-</literal></entry>
 184         <entry align="center"><literal>-</literal></entry>
 185         <entry align="left"></entry>
 186        </row>
 187        <row>
 188         <entry align="left"><literal>ALTER CONVERSION</literal></entry>
 189         <entry align="center"><literal>X</literal></entry>
 190         <entry align="center"><literal>X</literal></entry>
 191         <entry align="center"><literal>-</literal></entry>
 192         <entry align="center"><literal>-</literal></entry>
 193         <entry align="left"></entry>
 194        </row>
 195        <row>
 196         <entry align="left"><literal>ALTER DOMAIN</literal></entry>
 197         <entry align="center"><literal>X</literal></entry>
 198         <entry align="center"><literal>X</literal></entry>
 199         <entry align="center"><literal>-</literal></entry>
 200         <entry align="center"><literal>-</literal></entry>
 201         <entry align="left"></entry>
 202        </row>
 203        <row>
 204         <entry align="left"><literal>ALTER DEFAULT PRIVILEGES</literal></entry>
 205         <entry align="center"><literal>X</literal></entry>
 206         <entry align="center"><literal>X</literal></entry>
 207         <entry align="center"><literal>-</literal></entry>
 208         <entry align="center"><literal>-</literal></entry>
 209         <entry align="left"></entry>
 210        </row>
 211        <row>
 212         <entry align="left"><literal>ALTER EXTENSION</literal></entry>
 213         <entry align="center"><literal>X</literal></entry>
 214         <entry align="center"><literal>X</literal></entry>
 215         <entry align="center"><literal>-</literal></entry>
 216         <entry align="center"><literal>-</literal></entry>
 217         <entry align="left"></entry>
 218        </row>
 219        <row>
 220         <entry align="left"><literal>ALTER FOREIGN DATA WRAPPER</literal></entry>
 221         <entry align="center"><literal>X</literal></entry>
 222         <entry align="center"><literal>X</literal></entry>
 223         <entry align="center"><literal>-</literal></entry>
 224         <entry align="center"><literal>-</literal></entry>
 225         <entry align="left"></entry>
 226        </row>
 227        <row>
 228         <entry align="left"><literal>ALTER FOREIGN TABLE</literal></entry>
 229         <entry align="center"><literal>X</literal></entry>
 230         <entry align="center"><literal>X</literal></entry>
 231         <entry align="center"><literal>X</literal></entry>
 232         <entry align="center"><literal>-</literal></entry>
 233         <entry align="left"></entry>
 234        </row>
 235        <row>
 236         <entry align="left"><literal>ALTER FUNCTION</literal></entry>
 237         <entry align="center"><literal>X</literal></entry>
 238         <entry align="center"><literal>X</literal></entry>
 239         <entry align="center"><literal>-</literal></entry>
 240         <entry align="center"><literal>-</literal></entry>
 241         <entry align="left"></entry>
 242        </row>
 243        <row>
 244         <entry align="left"><literal>ALTER LANGUAGE</literal></entry>
 245         <entry align="center"><literal>X</literal></entry>
 246         <entry align="center"><literal>X</literal></entry>
 247         <entry align="center"><literal>-</literal></entry>
 248         <entry align="center"><literal>-</literal></entry>
 249         <entry align="left"></entry>
 250        </row>
 251        <row>
 252         <entry align="left"><literal>ALTER LARGE OBJECT</literal></entry>
 253         <entry align="center"><literal>X</literal></entry>
 254         <entry align="center"><literal>X</literal></entry>
 255         <entry align="center"><literal>-</literal></entry>
 256         <entry align="center"><literal>-</literal></entry>
 257         <entry align="left"></entry>
 258        </row>
 259        <row>
 260         <entry align="left"><literal>ALTER MATERIALIZED VIEW</literal></entry>
 261         <entry align="center"><literal>X</literal></entry>
 262         <entry align="center"><literal>X</literal></entry>
 263         <entry align="center"><literal>-</literal></entry>
 264         <entry align="center"><literal>X</literal></entry>
 265         <entry align="left"></entry>
 266        </row>
 267        <row>
 268         <entry align="left"><literal>ALTER OPERATOR</literal></entry>
 269         <entry align="center"><literal>X</literal></entry>
 270         <entry align="center"><literal>X</literal></entry>
 271         <entry align="center"><literal>-</literal></entry>
 272         <entry align="center"><literal>-</literal></entry>
 273         <entry align="left"></entry>
 274        </row>
 275        <row>
 276         <entry align="left"><literal>ALTER OPERATOR CLASS</literal></entry>
 277         <entry align="center"><literal>X</literal></entry>
 278         <entry align="center"><literal>X</literal></entry>
 279         <entry align="center"><literal>-</literal></entry>
 280         <entry align="center"><literal>-</literal></entry>
 281         <entry align="left"></entry>
 282        </row>
 283        <row>
 284         <entry align="left"><literal>ALTER OPERATOR FAMILY</literal></entry>
 285         <entry align="center"><literal>X</literal></entry>
 286         <entry align="center"><literal>X</literal></entry>
 287         <entry align="center"><literal>-</literal></entry>
 288         <entry align="center"><literal>-</literal></entry>
 289         <entry align="left"></entry>
 290        </row>
 291        <row>
 292         <entry align="left"><literal>ALTER POLICY</literal></entry>
 293         <entry align="center"><literal>X</literal></entry>
 294         <entry align="center"><literal>X</literal></entry>
 295         <entry align="center"><literal>-</literal></entry>
 296         <entry align="center"><literal>-</literal></entry>
 297         <entry align="left"></entry>
 298        </row>
 299        <row>
 300         <entry align="left"><literal>ALTER PROCEDURE</literal></entry>
 301         <entry align="center"><literal>X</literal></entry>
 302         <entry align="center"><literal>X</literal></entry>
 303         <entry align="center"><literal>-</literal></entry>
 304         <entry align="center"><literal>-</literal></entry>
 305         <entry align="left"></entry>
 306        </row>
 307        <row>
 308         <entry align="left"><literal>ALTER PUBLICATION</literal></entry>
 309         <entry align="center"><literal>X</literal></entry>
 310         <entry align="center"><literal>X</literal></entry>
 311         <entry align="center"><literal>-</literal></entry>
 312         <entry align="center"><literal>-</literal></entry>
 313         <entry align="left"></entry>
 314        </row>
 315        <row>
 316         <entry align="left"><literal>ALTER ROUTINE</literal></entry>
 317         <entry align="center"><literal>X</literal></entry>
 318         <entry align="center"><literal>X</literal></entry>
 319         <entry align="center"><literal>-</literal></entry>
 320         <entry align="center"><literal>-</literal></entry>
 321         <entry align="left"></entry>
 322        </row>
 323        <row>
 324         <entry align="left"><literal>ALTER SCHEMA</literal></entry>
 325         <entry align="center"><literal>X</literal></entry>
 326         <entry align="center"><literal>X</literal></entry>
 327         <entry align="center"><literal>-</literal></entry>
 328         <entry align="center"><literal>-</literal></entry>
 329         <entry align="left"></entry>
 330        </row>
 331        <row>
 332         <entry align="left"><literal>ALTER SEQUENCE</literal></entry>
 333         <entry align="center"><literal>X</literal></entry>
 334         <entry align="center"><literal>X</literal></entry>
 335         <entry align="center"><literal>-</literal></entry>
 336         <entry align="center"><literal>-</literal></entry>
 337         <entry align="left"></entry>
 338        </row>
 339        <row>
 340         <entry align="left"><literal>ALTER SERVER</literal></entry>
 341         <entry align="center"><literal>X</literal></entry>
 342         <entry align="center"><literal>X</literal></entry>
 343         <entry align="center"><literal>-</literal></entry>
 344         <entry align="center"><literal>-</literal></entry>
 345         <entry align="left"></entry>
 346        </row>
 347        <row>
 348         <entry align="left"><literal>ALTER STATISTICS</literal></entry>
 349         <entry align="center"><literal>X</literal></entry>
 350         <entry align="center"><literal>X</literal></entry>
 351         <entry align="center"><literal>-</literal></entry>
 352         <entry align="center"><literal>-</literal></entry>
 353         <entry align="left"></entry>
 354        </row>
 355        <row>
 356         <entry align="left"><literal>ALTER SUBSCRIPTION</literal></entry>
 357         <entry align="center"><literal>X</literal></entry>
 358         <entry align="center"><literal>X</literal></entry>
 359         <entry align="center"><literal>-</literal></entry>
 360         <entry align="center"><literal>-</literal></entry>
 361         <entry align="left"></entry>
 362        </row>
 363        <row>
 364         <entry align="left"><literal>ALTER TABLE</literal></entry>
 365         <entry align="center"><literal>X</literal></entry>
 366         <entry align="center"><literal>X</literal></entry>
 367         <entry align="center"><literal>X</literal></entry>
 368         <entry align="center"><literal>X</literal></entry>
 369         <entry align="left"></entry>
 370        </row>
 371        <row>
 372         <entry align="left"><literal>ALTER TEXT SEARCH CONFIGURATION</literal></entry>
 373         <entry align="center"><literal>X</literal></entry>
 374         <entry align="center"><literal>X</literal></entry>
 375         <entry align="center"><literal>-</literal></entry>
 376         <entry align="center"><literal>-</literal></entry>
 377         <entry align="left"></entry>
 378        </row>
 379        <row>
 380         <entry align="left"><literal>ALTER TEXT SEARCH DICTIONARY</literal></entry>
 381         <entry align="center"><literal>X</literal></entry>
 382         <entry align="center"><literal>X</literal></entry>
 383         <entry align="center"><literal>-</literal></entry>
 384         <entry align="center"><literal>-</literal></entry>
 385         <entry align="left"></entry>
 386        </row>
 387        <row>
 388         <entry align="left"><literal>ALTER TEXT SEARCH PARSER</literal></entry>
 389         <entry align="center"><literal>X</literal></entry>
 390         <entry align="center"><literal>X</literal></entry>
 391         <entry align="center"><literal>-</literal></entry>
 392         <entry align="center"><literal>-</literal></entry>
 393         <entry align="left"></entry>
 394        </row>
 395        <row>
 396         <entry align="left"><literal>ALTER TEXT SEARCH TEMPLATE</literal></entry>
 397         <entry align="center"><literal>X</literal></entry>
 398         <entry align="center"><literal>X</literal></entry>
 399         <entry align="center"><literal>-</literal></entry>
 400         <entry align="center"><literal>-</literal></entry>
 401         <entry align="left"></entry>
 402        </row>
 403        <row>
 404         <entry align="left"><literal>ALTER TRIGGER</literal></entry>
 405         <entry align="center"><literal>X</literal></entry>
 406         <entry align="center"><literal>X</literal></entry>
 407         <entry align="center"><literal>-</literal></entry>
 408         <entry align="center"><literal>-</literal></entry>
 409         <entry align="left"></entry>
 410        </row>
 411        <row>
 412         <entry align="left"><literal>ALTER TYPE</literal></entry>
 413         <entry align="center"><literal>X</literal></entry>
 414         <entry align="center"><literal>X</literal></entry>
 415         <entry align="center"><literal>-</literal></entry>
 416         <entry align="center"><literal>X</literal></entry>
 417         <entry align="left"></entry>
 418        </row>
 419        <row>
 420         <entry align="left"><literal>ALTER USER MAPPING</literal></entry>
 421         <entry align="center"><literal>X</literal></entry>
 422         <entry align="center"><literal>X</literal></entry>
 423         <entry align="center"><literal>-</literal></entry>
 424         <entry align="center"><literal>-</literal></entry>
 425         <entry align="left"></entry>
 426        </row>
 427        <row>
 428         <entry align="left"><literal>ALTER VIEW</literal></entry>
 429         <entry align="center"><literal>X</literal></entry>
 430         <entry align="center"><literal>X</literal></entry>
 431         <entry align="center"><literal>-</literal></entry>
 432         <entry align="center"><literal>-</literal></entry>
 433         <entry align="left"></entry>
 434        </row>
 435        <row>
 436         <entry align="left"><literal>COMMENT</literal></entry>
 437         <entry align="center"><literal>X</literal></entry>
 438         <entry align="center"><literal>X</literal></entry>
 439         <entry align="center"><literal>-</literal></entry>
 440         <entry align="center"><literal>-</literal></entry>
 441         <entry align="left">Only for local objects</entry>
 442        </row>
 443        <row>
 444         <entry align="left"><literal>CREATE ACCESS METHOD</literal></entry>
 445         <entry align="center"><literal>X</literal></entry>
 446         <entry align="center"><literal>X</literal></entry>
 447         <entry align="center"><literal>-</literal></entry>
 448         <entry align="center"><literal>-</literal></entry>
 449         <entry align="left"></entry>
 450        </row>
 451        <row>
 452         <entry align="left"><literal>CREATE AGGREGATE</literal></entry>
 453         <entry align="center"><literal>X</literal></entry>
 454         <entry align="center"><literal>X</literal></entry>
 455         <entry align="center"><literal>-</literal></entry>
 456         <entry align="center"><literal>-</literal></entry>
 457         <entry align="left"></entry>
 458        </row>
 459        <row>
 460         <entry align="left"><literal>CREATE CAST</literal></entry>
 461         <entry align="center"><literal>X</literal></entry>
 462         <entry align="center"><literal>X</literal></entry>
 463         <entry align="center"><literal>-</literal></entry>
 464         <entry align="center"><literal>-</literal></entry>
 465         <entry align="left"></entry>
 466        </row>
 467        <row>
 468         <entry align="left"><literal>CREATE COLLATION</literal></entry>
 469         <entry align="center"><literal>X</literal></entry>
 470         <entry align="center"><literal>X</literal></entry>
 471         <entry align="center"><literal>-</literal></entry>
 472         <entry align="center"><literal>-</literal></entry>
 473         <entry align="left"></entry>
 474        </row>
 475        <row>
 476         <entry align="left"><literal>CREATE CONVERSION</literal></entry>
 477         <entry align="center"><literal>X</literal></entry>
 478         <entry align="center"><literal>X</literal></entry>
 479         <entry align="center"><literal>-</literal></entry>
 480         <entry align="center"><literal>-</literal></entry>
 481         <entry align="left"></entry>
 482        </row>
 483        <row>
 484         <entry align="left"><literal>CREATE DOMAIN</literal></entry>
 485         <entry align="center"><literal>X</literal></entry>
 486         <entry align="center"><literal>X</literal></entry>
 487         <entry align="center"><literal>-</literal></entry>
 488         <entry align="center"><literal>-</literal></entry>
 489         <entry align="left"></entry>
 490        </row>
 491        <row>
 492         <entry align="left"><literal>CREATE EXTENSION</literal></entry>
 493         <entry align="center"><literal>X</literal></entry>
 494         <entry align="center"><literal>X</literal></entry>
 495         <entry align="center"><literal>-</literal></entry>
 496         <entry align="center"><literal>-</literal></entry>
 497         <entry align="left"></entry>
 498        </row>
 499        <row>
 500         <entry align="left"><literal>CREATE FOREIGN DATA WRAPPER</literal></entry>
 501         <entry align="center"><literal>X</literal></entry>
 502         <entry align="center"><literal>X</literal></entry>
 503         <entry align="center"><literal>-</literal></entry>
 504         <entry align="center"><literal>-</literal></entry>
 505         <entry align="left"></entry>
 506        </row>
 507        <row>
 508         <entry align="left"><literal>CREATE FOREIGN TABLE</literal></entry>
 509         <entry align="center"><literal>X</literal></entry>
 510         <entry align="center"><literal>X</literal></entry>
 511         <entry align="center"><literal>-</literal></entry>
 512         <entry align="center"><literal>-</literal></entry>
 513         <entry align="left"></entry>
 514        </row>
 515        <row>
 516         <entry align="left"><literal>CREATE FUNCTION</literal></entry>
 517         <entry align="center"><literal>X</literal></entry>
 518         <entry align="center"><literal>X</literal></entry>
 519         <entry align="center"><literal>-</literal></entry>
 520         <entry align="center"><literal>-</literal></entry>
 521         <entry align="left"></entry>
 522        </row>
 523        <row>
 524         <entry align="left"><literal>CREATE INDEX</literal></entry>
 525         <entry align="center"><literal>X</literal></entry>
 526         <entry align="center"><literal>X</literal></entry>
 527         <entry align="center"><literal>-</literal></entry>
 528         <entry align="center"><literal>-</literal></entry>
 529         <entry align="left"></entry>
 530        </row>
 531        <row>
 532         <entry align="left"><literal>CREATE LANGUAGE</literal></entry>
 533         <entry align="center"><literal>X</literal></entry>
 534         <entry align="center"><literal>X</literal></entry>
 535         <entry align="center"><literal>-</literal></entry>
 536         <entry align="center"><literal>-</literal></entry>
 537         <entry align="left"></entry>
 538        </row>
 539        <row>
 540         <entry align="left"><literal>CREATE MATERIALIZED VIEW</literal></entry>
 541         <entry align="center"><literal>X</literal></entry>
 542         <entry align="center"><literal>X</literal></entry>
 543         <entry align="center"><literal>-</literal></entry>
 544         <entry align="center"><literal>-</literal></entry>
 545         <entry align="left"></entry>
 546        </row>
 547        <row>
 548         <entry align="left"><literal>CREATE OPERATOR</literal></entry>
 549         <entry align="center"><literal>X</literal></entry>
 550         <entry align="center"><literal>X</literal></entry>
 551         <entry align="center"><literal>-</literal></entry>
 552         <entry align="center"><literal>-</literal></entry>
 553         <entry align="left"></entry>
 554        </row>
 555        <row>
 556         <entry align="left"><literal>CREATE OPERATOR CLASS</literal></entry>
 557         <entry align="center"><literal>X</literal></entry>
 558         <entry align="center"><literal>X</literal></entry>
 559         <entry align="center"><literal>-</literal></entry>
 560         <entry align="center"><literal>-</literal></entry>
 561         <entry align="left"></entry>
 562        </row>
 563        <row>
 564         <entry align="left"><literal>CREATE OPERATOR FAMILY</literal></entry>
 565         <entry align="center"><literal>X</literal></entry>
 566         <entry align="center"><literal>X</literal></entry>
 567         <entry align="center"><literal>-</literal></entry>
 568         <entry align="center"><literal>-</literal></entry>
 569         <entry align="left"></entry>
 570        </row>
 571        <row>
 572         <entry align="left"><literal>CREATE POLICY</literal></entry>
 573         <entry align="center"><literal>X</literal></entry>
 574         <entry align="center"><literal>X</literal></entry>
 575         <entry align="center"><literal>-</literal></entry>
 576         <entry align="center"><literal>-</literal></entry>
 577         <entry align="left"></entry>
 578        </row>
 579        <row>
 580         <entry align="left"><literal>CREATE PROCEDURE</literal></entry>
 581         <entry align="center"><literal>X</literal></entry>
 582         <entry align="center"><literal>X</literal></entry>
 583         <entry align="center"><literal>-</literal></entry>
 584         <entry align="center"><literal>-</literal></entry>
 585         <entry align="left"></entry>
 586        </row>
 587        <row>
 588         <entry align="left"><literal>CREATE PUBLICATION</literal></entry>
 589         <entry align="center"><literal>X</literal></entry>
 590         <entry align="center"><literal>X</literal></entry>
 591         <entry align="center"><literal>-</literal></entry>
 592         <entry align="center"><literal>-</literal></entry>
 593         <entry align="left"></entry>
 594        </row>
 595        <row>
 596         <entry align="left"><literal>CREATE RULE</literal></entry>
 597         <entry align="center"><literal>X</literal></entry>
 598         <entry align="center"><literal>X</literal></entry>
 599         <entry align="center"><literal>-</literal></entry>
 600         <entry align="center"><literal>-</literal></entry>
 601         <entry align="left"></entry>
 602        </row>
 603        <row>
 604         <entry align="left"><literal>CREATE SCHEMA</literal></entry>
 605         <entry align="center"><literal>X</literal></entry>
 606         <entry align="center"><literal>X</literal></entry>
 607         <entry align="center"><literal>-</literal></entry>
 608         <entry align="center"><literal>-</literal></entry>
 609         <entry align="left"></entry>
 610        </row>
 611        <row>
 612         <entry align="left"><literal>CREATE SEQUENCE</literal></entry>
 613         <entry align="center"><literal>X</literal></entry>
 614         <entry align="center"><literal>X</literal></entry>
 615         <entry align="center"><literal>-</literal></entry>
 616         <entry align="center"><literal>-</literal></entry>
 617         <entry align="left"></entry>
 618        </row>
 619        <row>
 620         <entry align="left"><literal>CREATE SERVER</literal></entry>
 621         <entry align="center"><literal>X</literal></entry>
 622         <entry align="center"><literal>X</literal></entry>
 623         <entry align="center"><literal>-</literal></entry>
 624         <entry align="center"><literal>-</literal></entry>
 625         <entry align="left"></entry>
 626        </row>
 627        <row>
 628         <entry align="left"><literal>CREATE STATISTICS</literal></entry>
 629         <entry align="center"><literal>X</literal></entry>
 630         <entry align="center"><literal>X</literal></entry>
 631         <entry align="center"><literal>-</literal></entry>
 632         <entry align="center"><literal>-</literal></entry>
 633         <entry align="left"></entry>
 634        </row>
 635        <row>
 636         <entry align="left"><literal>CREATE SUBSCRIPTION</literal></entry>
 637         <entry align="center"><literal>X</literal></entry>
 638         <entry align="center"><literal>X</literal></entry>
 639         <entry align="center"><literal>-</literal></entry>
 640         <entry align="center"><literal>-</literal></entry>
 641         <entry align="left"></entry>
 642        </row>
 643        <row>
 644         <entry align="left"><literal>CREATE TABLE</literal></entry>
 645         <entry align="center"><literal>X</literal></entry>
 646         <entry align="center"><literal>X</literal></entry>
 647         <entry align="center"><literal>-</literal></entry>
 648         <entry align="center"><literal>-</literal></entry>
 649         <entry align="left"></entry>
 650        </row>
 651        <row>
 652         <entry align="left"><literal>CREATE TABLE AS</literal></entry>
 653         <entry align="center"><literal>X</literal></entry>
 654         <entry align="center"><literal>X</literal></entry>
 655         <entry align="center"><literal>-</literal></entry>
 656         <entry align="center"><literal>-</literal></entry>
 657         <entry align="left"></entry>
 658        </row>
 659        <row>
 660         <entry align="left"><literal>CREATE TEXT SEARCH CONFIGURATION</literal></entry>
 661         <entry align="center"><literal>X</literal></entry>
 662         <entry align="center"><literal>X</literal></entry>
 663         <entry align="center"><literal>-</literal></entry>
 664         <entry align="center"><literal>-</literal></entry>
 665         <entry align="left"></entry>
 666        </row>
 667        <row>
 668         <entry align="left"><literal>CREATE TEXT SEARCH DICTIONARY</literal></entry>
 669         <entry align="center"><literal>X</literal></entry>
 670         <entry align="center"><literal>X</literal></entry>
 671         <entry align="center"><literal>-</literal></entry>
 672         <entry align="center"><literal>-</literal></entry>
 673         <entry align="left"></entry>
 674        </row>
 675        <row>
 676         <entry align="left"><literal>CREATE TEXT SEARCH PARSER</literal></entry>
 677         <entry align="center"><literal>X</literal></entry>
 678         <entry align="center"><literal>X</literal></entry>
 679         <entry align="center"><literal>-</literal></entry>
 680         <entry align="center"><literal>-</literal></entry>
 681         <entry align="left"></entry>
 682        </row>
 683        <row>
 684         <entry align="left"><literal>CREATE TEXT SEARCH TEMPLATE</literal></entry>
 685         <entry align="center"><literal>X</literal></entry>
 686         <entry align="center"><literal>X</literal></entry>
 687         <entry align="center"><literal>-</literal></entry>
 688         <entry align="center"><literal>-</literal></entry>
 689         <entry align="left"></entry>
 690        </row>
 691        <row>
 692         <entry align="left"><literal>CREATE TRIGGER</literal></entry>
 693         <entry align="center"><literal>X</literal></entry>
 694         <entry align="center"><literal>X</literal></entry>
 695         <entry align="center"><literal>-</literal></entry>
 696         <entry align="center"><literal>-</literal></entry>
 697         <entry align="left"></entry>
 698        </row>
 699        <row>
 700         <entry align="left"><literal>CREATE TYPE</literal></entry>
 701         <entry align="center"><literal>X</literal></entry>
 702         <entry align="center"><literal>X</literal></entry>
 703         <entry align="center"><literal>-</literal></entry>
 704         <entry align="center"><literal>-</literal></entry>
 705         <entry align="left"></entry>
 706        </row>
 707        <row>
 708         <entry align="left"><literal>CREATE USER MAPPING</literal></entry>
 709         <entry align="center"><literal>X</literal></entry>
 710         <entry align="center"><literal>X</literal></entry>
 711         <entry align="center"><literal>-</literal></entry>
 712         <entry align="center"><literal>-</literal></entry>
 713         <entry align="left"></entry>
 714        </row>
 715        <row>
 716         <entry align="left"><literal>CREATE VIEW</literal></entry>
 717         <entry align="center"><literal>X</literal></entry>
 718         <entry align="center"><literal>X</literal></entry>
 719         <entry align="center"><literal>-</literal></entry>
 720         <entry align="center"><literal>-</literal></entry>
 721         <entry align="left"></entry>
 722        </row>
 723        <row>
 724         <entry align="left"><literal>DROP ACCESS METHOD</literal></entry>
 725         <entry align="center"><literal>X</literal></entry>
 726         <entry align="center"><literal>X</literal></entry>
 727         <entry align="center"><literal>X</literal></entry>
 728         <entry align="center"><literal>-</literal></entry>
 729         <entry align="left"></entry>
 730        </row>
 731        <row>
 732         <entry align="left"><literal>DROP AGGREGATE</literal></entry>
 733         <entry align="center"><literal>X</literal></entry>
 734         <entry align="center"><literal>X</literal></entry>
 735         <entry align="center"><literal>X</literal></entry>
 736         <entry align="center"><literal>-</literal></entry>
 737         <entry align="left"></entry>
 738        </row>
 739        <row>
 740         <entry align="left"><literal>DROP CAST</literal></entry>
 741         <entry align="center"><literal>X</literal></entry>
 742         <entry align="center"><literal>X</literal></entry>
 743         <entry align="center"><literal>X</literal></entry>
 744         <entry align="center"><literal>-</literal></entry>
 745         <entry align="left"></entry>
 746        </row>
 747        <row>
 748         <entry align="left"><literal>DROP COLLATION</literal></entry>
 749         <entry align="center"><literal>X</literal></entry>
 750         <entry align="center"><literal>X</literal></entry>
 751         <entry align="center"><literal>X</literal></entry>
 752         <entry align="center"><literal>-</literal></entry>
 753         <entry align="left"></entry>
 754        </row>
 755        <row>
 756         <entry align="left"><literal>DROP CONVERSION</literal></entry>
 757         <entry align="center"><literal>X</literal></entry>
 758         <entry align="center"><literal>X</literal></entry>
 759         <entry align="center"><literal>X</literal></entry>
 760         <entry align="center"><literal>-</literal></entry>
 761         <entry align="left"></entry>
 762        </row>
 763        <row>
 764         <entry align="left"><literal>DROP DOMAIN</literal></entry>
 765         <entry align="center"><literal>X</literal></entry>
 766         <entry align="center"><literal>X</literal></entry>
 767         <entry align="center"><literal>X</literal></entry>
 768         <entry align="center"><literal>-</literal></entry>
 769         <entry align="left"></entry>
 770        </row>
 771        <row>
 772         <entry align="left"><literal>DROP EXTENSION</literal></entry>
 773         <entry align="center"><literal>X</literal></entry>
 774         <entry align="center"><literal>X</literal></entry>
 775         <entry align="center"><literal>X</literal></entry>
 776         <entry align="center"><literal>-</literal></entry>
 777         <entry align="left"></entry>
 778        </row>
 779        <row>
 780         <entry align="left"><literal>DROP FOREIGN DATA WRAPPER</literal></entry>
 781         <entry align="center"><literal>X</literal></entry>
 782         <entry align="center"><literal>X</literal></entry>
 783         <entry align="center"><literal>X</literal></entry>
 784         <entry align="center"><literal>-</literal></entry>
 785         <entry align="left"></entry>
 786        </row>
 787        <row>
 788         <entry align="left"><literal>DROP FOREIGN TABLE</literal></entry>
 789         <entry align="center"><literal>X</literal></entry>
 790         <entry align="center"><literal>X</literal></entry>
 791         <entry align="center"><literal>X</literal></entry>
 792         <entry align="center"><literal>-</literal></entry>
 793         <entry align="left"></entry>
 794        </row>
 795        <row>
 796         <entry align="left"><literal>DROP FUNCTION</literal></entry>
 797         <entry align="center"><literal>X</literal></entry>
 798         <entry align="center"><literal>X</literal></entry>
 799         <entry align="center"><literal>X</literal></entry>
 800         <entry align="center"><literal>-</literal></entry>
 801         <entry align="left"></entry>
 802        </row>
 803        <row>
 804         <entry align="left"><literal>DROP INDEX</literal></entry>
 805         <entry align="center"><literal>X</literal></entry>
 806         <entry align="center"><literal>X</literal></entry>
 807         <entry align="center"><literal>X</literal></entry>
 808         <entry align="center"><literal>-</literal></entry>
 809         <entry align="left"></entry>
 810        </row>
 811        <row>
 812         <entry align="left"><literal>DROP LANGUAGE</literal></entry>
 813         <entry align="center"><literal>X</literal></entry>
 814         <entry align="center"><literal>X</literal></entry>
 815         <entry align="center"><literal>X</literal></entry>
 816         <entry align="center"><literal>-</literal></entry>
 817         <entry align="left"></entry>
 818        </row>
 819        <row>
 820         <entry align="left"><literal>DROP MATERIALIZED VIEW</literal></entry>
 821         <entry align="center"><literal>X</literal></entry>
 822         <entry align="center"><literal>X</literal></entry>
 823         <entry align="center"><literal>X</literal></entry>
 824         <entry align="center"><literal>-</literal></entry>
 825         <entry align="left"></entry>
 826        </row>
 827        <row>
 828         <entry align="left"><literal>DROP OPERATOR</literal></entry>
 829         <entry align="center"><literal>X</literal></entry>
 830         <entry align="center"><literal>X</literal></entry>
 831         <entry align="center"><literal>X</literal></entry>
 832         <entry align="center"><literal>-</literal></entry>
 833         <entry align="left"></entry>
 834        </row>
 835        <row>
 836         <entry align="left"><literal>DROP OPERATOR CLASS</literal></entry>
 837         <entry align="center"><literal>X</literal></entry>
 838         <entry align="center"><literal>X</literal></entry>
 839         <entry align="center"><literal>X</literal></entry>
 840         <entry align="center"><literal>-</literal></entry>
 841         <entry align="left"></entry>
 842        </row>
 843        <row>
 844         <entry align="left"><literal>DROP OPERATOR FAMILY</literal></entry>
 845         <entry align="center"><literal>X</literal></entry>
 846         <entry align="center"><literal>X</literal></entry>
 847         <entry align="center"><literal>X</literal></entry>
 848         <entry align="center"><literal>-</literal></entry>
 849         <entry align="left"></entry>
 850        </row>
 851        <row>
 852         <entry align="left"><literal>DROP OWNED</literal></entry>
 853         <entry align="center"><literal>X</literal></entry>
 854         <entry align="center"><literal>X</literal></entry>
 855         <entry align="center"><literal>X</literal></entry>
 856         <entry align="center"><literal>-</literal></entry>
 857         <entry align="left"></entry>
 858        </row>
 859        <row>
 860         <entry align="left"><literal>DROP POLICY</literal></entry>
 861         <entry align="center"><literal>X</literal></entry>
 862         <entry align="center"><literal>X</literal></entry>
 863         <entry align="center"><literal>X</literal></entry>
 864         <entry align="center"><literal>-</literal></entry>
 865         <entry align="left"></entry>
 866        </row>
 867        <row>
 868         <entry align="left"><literal>DROP PROCEDURE</literal></entry>
 869         <entry align="center"><literal>X</literal></entry>
 870         <entry align="center"><literal>X</literal></entry>
 871         <entry align="center"><literal>X</literal></entry>
 872         <entry align="center"><literal>-</literal></entry>
 873         <entry align="left"></entry>
 874        </row>
 875        <row>
 876         <entry align="left"><literal>DROP PUBLICATION</literal></entry>
 877         <entry align="center"><literal>X</literal></entry>
 878         <entry align="center"><literal>X</literal></entry>
 879         <entry align="center"><literal>X</literal></entry>
 880         <entry align="center"><literal>-</literal></entry>
 881         <entry align="left"></entry>
 882        </row>
 883        <row>
 884         <entry align="left"><literal>DROP ROUTINE</literal></entry>
 885         <entry align="center"><literal>X</literal></entry>
 886         <entry align="center"><literal>X</literal></entry>
 887         <entry align="center"><literal>X</literal></entry>
 888         <entry align="center"><literal>-</literal></entry>
 889         <entry align="left"></entry>
 890        </row>
 891        <row>
 892         <entry align="left"><literal>DROP RULE</literal></entry>
 893         <entry align="center"><literal>X</literal></entry>
 894         <entry align="center"><literal>X</literal></entry>
 895         <entry align="center"><literal>X</literal></entry>
 896         <entry align="center"><literal>-</literal></entry>
 897         <entry align="left"></entry>
 898        </row>
 899        <row>
 900         <entry align="left"><literal>DROP SCHEMA</literal></entry>
 901         <entry align="center"><literal>X</literal></entry>
 902         <entry align="center"><literal>X</literal></entry>
 903         <entry align="center"><literal>X</literal></entry>
 904         <entry align="center"><literal>-</literal></entry>
 905         <entry align="left"></entry>
 906        </row>
 907        <row>
 908         <entry align="left"><literal>DROP SEQUENCE</literal></entry>
 909         <entry align="center"><literal>X</literal></entry>
 910         <entry align="center"><literal>X</literal></entry>
 911         <entry align="center"><literal>X</literal></entry>
 912         <entry align="center"><literal>-</literal></entry>
 913         <entry align="left"></entry>
 914        </row>
 915        <row>
 916         <entry align="left"><literal>DROP SERVER</literal></entry>
 917         <entry align="center"><literal>X</literal></entry>
 918         <entry align="center"><literal>X</literal></entry>
 919         <entry align="center"><literal>X</literal></entry>
 920         <entry align="center"><literal>-</literal></entry>
 921         <entry align="left"></entry>
 922        </row>
 923        <row>
 924         <entry align="left"><literal>DROP STATISTICS</literal></entry>
 925         <entry align="center"><literal>X</literal></entry>
 926         <entry align="center"><literal>X</literal></entry>
 927         <entry align="center"><literal>X</literal></entry>
 928         <entry align="center"><literal>-</literal></entry>
 929         <entry align="left"></entry>
 930        </row>
 931        <row>
 932         <entry align="left"><literal>DROP SUBSCRIPTION</literal></entry>
 933         <entry align="center"><literal>X</literal></entry>
 934         <entry align="center"><literal>X</literal></entry>
 935         <entry align="center"><literal>X</literal></entry>
 936         <entry align="center"><literal>-</literal></entry>
 937         <entry align="left"></entry>
 938        </row>
 939        <row>
 940         <entry align="left"><literal>DROP TABLE</literal></entry>
 941         <entry align="center"><literal>X</literal></entry>
 942         <entry align="center"><literal>X</literal></entry>
 943         <entry align="center"><literal>X</literal></entry>
 944         <entry align="center"><literal>-</literal></entry>
 945         <entry align="left"></entry>
 946        </row>
 947        <row>
 948         <entry align="left"><literal>DROP TEXT SEARCH CONFIGURATION</literal></entry>
 949         <entry align="center"><literal>X</literal></entry>
 950         <entry align="center"><literal>X</literal></entry>
 951         <entry align="center"><literal>X</literal></entry>
 952         <entry align="center"><literal>-</literal></entry>
 953         <entry align="left"></entry>
 954        </row>
 955        <row>
 956         <entry align="left"><literal>DROP TEXT SEARCH DICTIONARY</literal></entry>
 957         <entry align="center"><literal>X</literal></entry>
 958         <entry align="center"><literal>X</literal></entry>
 959         <entry align="center"><literal>X</literal></entry>
 960         <entry align="center"><literal>-</literal></entry>
 961         <entry align="left"></entry>
 962        </row>
 963        <row>
 964         <entry align="left"><literal>DROP TEXT SEARCH PARSER</literal></entry>
 965         <entry align="center"><literal>X</literal></entry>
 966         <entry align="center"><literal>X</literal></entry>
 967         <entry align="center"><literal>X</literal></entry>
 968         <entry align="center"><literal>-</literal></entry>
 969         <entry align="left"></entry>
 970        </row>
 971        <row>
 972         <entry align="left"><literal>DROP TEXT SEARCH TEMPLATE</literal></entry>
 973         <entry align="center"><literal>X</literal></entry>
 974         <entry align="center"><literal>X</literal></entry>
 975         <entry align="center"><literal>X</literal></entry>
 976         <entry align="center"><literal>-</literal></entry>
 977         <entry align="left"></entry>
 978        </row>
 979        <row>
 980         <entry align="left"><literal>DROP TRIGGER</literal></entry>
 981         <entry align="center"><literal>X</literal></entry>
 982         <entry align="center"><literal>X</literal></entry>
 983         <entry align="center"><literal>X</literal></entry>
 984         <entry align="center"><literal>-</literal></entry>
 985         <entry align="left"></entry>
 986        </row>
 987        <row>
 988         <entry align="left"><literal>DROP TYPE</literal></entry>
 989         <entry align="center"><literal>X</literal></entry>
 990         <entry align="center"><literal>X</literal></entry>
 991         <entry align="center"><literal>X</literal></entry>
 992         <entry align="center"><literal>-</literal></entry>
 993         <entry align="left"></entry>
 994        </row>
 995        <row>
 996         <entry align="left"><literal>DROP USER MAPPING</literal></entry>
 997         <entry align="center"><literal>X</literal></entry>
 998         <entry align="center"><literal>X</literal></entry>
 999         <entry align="center"><literal>X</literal></entry>
1000         <entry align="center"><literal>-</literal></entry>
1001         <entry align="left"></entry>
1002        </row>
1003        <row>
1004         <entry align="left"><literal>DROP VIEW</literal></entry>
1005         <entry align="center"><literal>X</literal></entry>
1006         <entry align="center"><literal>X</literal></entry>
1007         <entry align="center"><literal>X</literal></entry>
1008         <entry align="center"><literal>-</literal></entry>
1009         <entry align="left"></entry>
1010        </row>
1011        <row>
1012         <entry align="left"><literal>GRANT</literal></entry>
1013         <entry align="center"><literal>X</literal></entry>
1014         <entry align="center"><literal>X</literal></entry>
1015         <entry align="center"><literal>-</literal></entry>
1016         <entry align="center"><literal>-</literal></entry>
1017         <entry align="left">Only for local objects</entry>
1018        </row>
1019        <row>
1020         <entry align="left"><literal>IMPORT FOREIGN SCHEMA</literal></entry>
1021         <entry align="center"><literal>X</literal></entry>
1022         <entry align="center"><literal>X</literal></entry>
1023         <entry align="center"><literal>-</literal></entry>
1024         <entry align="center"><literal>-</literal></entry>
1025         <entry align="left"></entry>
1026        </row>
1027        <row>
1028         <entry align="left"><literal>REFRESH MATERIALIZED VIEW</literal></entry>
1029         <entry align="center"><literal>X</literal></entry>
1030         <entry align="center"><literal>X</literal></entry>
1031         <entry align="center"><literal>-</literal></entry>
1032         <entry align="center"><literal>-</literal></entry>
1033         <entry align="left"></entry>
1034        </row>
1035        <row>
1036         <entry align="left"><literal>REINDEX</literal></entry>
1037         <entry align="center"><literal>X</literal></entry>
1038         <entry align="center"><literal>X</literal></entry>
1039         <entry align="center"><literal>-</literal></entry>
1040         <entry align="center"><literal>-</literal></entry>
1041         <entry align="left"></entry>
1042        </row>
1043        <row>
1044         <entry align="left"><literal>REVOKE</literal></entry>
1045         <entry align="center"><literal>X</literal></entry>
1046         <entry align="center"><literal>X</literal></entry>
1047         <entry align="center"><literal>-</literal></entry>
1048         <entry align="center"><literal>-</literal></entry>
1049         <entry align="left">Only for local objects</entry>
1050        </row>
1051        <row>
1052         <entry align="left"><literal>SECURITY LABEL</literal></entry>
1053         <entry align="center"><literal>X</literal></entry>
1054         <entry align="center"><literal>X</literal></entry>
1055         <entry align="center"><literal>-</literal></entry>
1056         <entry align="center"><literal>-</literal></entry>
1057         <entry align="left">Only for local objects</entry>
1058        </row>
1059        <row>
1060         <entry align="left"><literal>SELECT INTO</literal></entry>
1061         <entry align="center"><literal>X</literal></entry>
1062         <entry align="center"><literal>X</literal></entry>
1063         <entry align="center"><literal>-</literal></entry>
1064         <entry align="center"><literal>-</literal></entry>
1065         <entry align="left"></entry>
1066        </row>
1067       </tbody>
1068      </tgroup>
1069    </table>
1070   </sect1>
1071
1072   <sect1 id="event-trigger-interface">
1073    <title>Writing Event Trigger Functions in C</title>
1074
1075    <indexterm zone="event-trigger-interface">
1076     <primary>event trigger</primary>
1077     <secondary>in C</secondary>
1078    </indexterm>
1079
1080    <para>
1081     This section describes the low-level details of the interface to an
1082     event trigger function. This information is only needed when writing
1083     event trigger functions in C. If you are using a higher-level language
1084     then these details are handled for you. In most cases you should
1085     consider using a procedural language before writing your event triggers
1086     in C. The documentation of each procedural language explains how to
1087     write an event trigger in that language.
1088    </para>
1089
1090    <para>
1091     Event trigger functions must use the <quote>version 1</quote> function
1092     manager interface.
1093    </para>
1094
1095    <para>
1096     When a function is called by the event trigger manager, it is not passed
1097     any normal arguments, but it is passed a <quote>context</quote> pointer
1098     pointing to a <structname>EventTriggerData</structname> structure. C functions can
1099     check whether they were called from the event trigger manager or not by
1100     executing the macro:
1101 <programlisting>
1102 CALLED_AS_EVENT_TRIGGER(fcinfo)
1103 </programlisting>
1104     which expands to:
1105 <programlisting>
1106 ((fcinfo)-&gt;context != NULL &amp;&amp; IsA((fcinfo)-&gt;context, EventTriggerData))
1107 </programlisting>
1108     If this returns true, then it is safe to cast
1109     <literal>fcinfo-&gt;context</literal> to type <literal>EventTriggerData
1110     *</literal> and make use of the pointed-to
1111     <structname>EventTriggerData</structname> structure.  The function must
1112     <emphasis>not</emphasis> alter the <structname>EventTriggerData</structname>
1113     structure or any of the data it points to.
1114    </para>
1115
1116    <para>
1117     <structname>struct EventTriggerData</structname> is defined in
1118     <filename>commands/event_trigger.h</filename>:
1119
1120 <programlisting>
1121 typedef struct EventTriggerData
1122 {
1123     NodeTag     type;
1124     const char *event;      /* event name */
1125     Node       *parsetree;  /* parse tree */
1126     CommandTag  tag;        /* command tag */
1127 } EventTriggerData;
1128 </programlisting>
1129
1130     where the members are defined as follows:
1131
1132     <variablelist>
1133      <varlistentry>
1134       <term><structfield>type</structfield></term>
1135       <listitem>
1136        <para>
1137         Always <literal>T_EventTriggerData</literal>.
1138        </para>
1139       </listitem>
1140      </varlistentry>
1141
1142      <varlistentry>
1143       <term><structfield>event</structfield></term>
1144       <listitem>
1145        <para>
1146         Describes the event for which the function is called, one of
1147         <literal>"login"</literal>, <literal>"ddl_command_start"</literal>,
1148         <literal>"ddl_command_end"</literal>, <literal>"sql_drop"</literal>,
1149         <literal>"table_rewrite"</literal>.
1150         See <xref linkend="event-trigger-definition"/> for the meaning of these
1151         events.
1152        </para>
1153       </listitem>
1154      </varlistentry>
1155
1156      <varlistentry>
1157       <term><structfield>parsetree</structfield></term>
1158       <listitem>
1159        <para>
1160         A pointer to the parse tree of the command.  Check the PostgreSQL
1161         source code for details.  The parse tree structure is subject to change
1162         without notice.
1163        </para>
1164       </listitem>
1165      </varlistentry>
1166
1167      <varlistentry>
1168       <term><structfield>tag</structfield></term>
1169       <listitem>
1170        <para>
1171         The command tag associated with the event for which the event trigger
1172         is run, for example <literal>"CREATE FUNCTION"</literal>.
1173        </para>
1174       </listitem>
1175      </varlistentry>
1176     </variablelist>
1177    </para>
1178
1179    <para>
1180     An event trigger function must return a <symbol>NULL</symbol> pointer
1181     (<emphasis>not</emphasis> an SQL null value, that is, do not
1182     set <parameter>isNull</parameter> true).
1183    </para>
1184   </sect1>
1185
1186   <sect1 id="event-trigger-example">
1187    <title>A Complete Event Trigger Example</title>
1188
1189    <para>
1190     Here is a very simple example of an event trigger function written in C.
1191     (Examples of triggers written in procedural languages can be found in
1192     the documentation of the procedural languages.)
1193    </para>
1194
1195    <para>
1196     The function <function>noddl</function> raises an exception each time it is called.
1197     The event trigger definition associated the function with
1198     the <literal>ddl_command_start</literal> event.  The effect is that all DDL
1199     commands (with the exceptions mentioned
1200     in <xref linkend="event-trigger-definition"/>) are prevented from running.
1201    </para>
1202
1203    <para>
1204     This is the source code of the trigger function:
1205 <programlisting><![CDATA[
1206 #include "postgres.h"
1207
1208 #include "commands/event_trigger.h"
1209 #include "fmgr.h"
1210
1211 PG_MODULE_MAGIC;
1212
1213 PG_FUNCTION_INFO_V1(noddl);
1214
1215 Datum
1216 noddl(PG_FUNCTION_ARGS)
1217 {
1218     EventTriggerData *trigdata;
1219
1220     if (!CALLED_AS_EVENT_TRIGGER(fcinfo))  /* internal error */
1221         elog(ERROR, "not fired by event trigger manager");
1222
1223     trigdata = (EventTriggerData *) fcinfo->context;
1224
1225     ereport(ERROR,
1226             (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1227              errmsg("command \"%s\" denied",
1228                     GetCommandTagName(trigdata->tag))));
1229
1230     PG_RETURN_NULL();
1231 }
1232 ]]></programlisting>
1233    </para>
1234
1235    <para>
1236     After you have compiled the source code (see <xref linkend="dfunc"/>),
1237     declare the function and the triggers:
1238 <programlisting>
1239 CREATE FUNCTION noddl() RETURNS event_trigger
1240     AS 'noddl' LANGUAGE C;
1241
1242 CREATE EVENT TRIGGER noddl ON ddl_command_start
1243     EXECUTE FUNCTION noddl();
1244 </programlisting>
1245    </para>
1246
1247    <para>
1248     Now you can test the operation of the trigger:
1249 <screen>
1250 =# \dy
1251                      List of event triggers
1252  Name  |       Event       | Owner | Enabled | Function | Tags
1253 -------+-------------------+-------+---------+----------+------
1254  noddl | ddl_command_start | dim   | enabled | noddl    |
1255 (1 row)
1256
1257 =# CREATE TABLE foo(id serial);
1258 ERROR:  command "CREATE TABLE" denied
1259 </screen>
1260    </para>
1261
1262    <para>
1263     In this situation, in order to be able to run some DDL commands when you
1264     need to do so, you have to either drop the event trigger or disable it.  It
1265     can be convenient to disable the trigger for only the duration of a
1266     transaction:
1267 <programlisting>
1268 BEGIN;
1269 ALTER EVENT TRIGGER noddl DISABLE;
1270 CREATE TABLE foo (id serial);
1271 ALTER EVENT TRIGGER noddl ENABLE;
1272 COMMIT;
1273 </programlisting>
1274     (Recall that DDL commands on event triggers themselves are not affected by
1275     event triggers.)
1276    </para>
1277   </sect1>
1278
1279   <sect1 id="event-trigger-table-rewrite-example">
1280    <title>A Table Rewrite Event Trigger Example</title>
1281
1282    <para>
1283     Thanks to the <literal>table_rewrite</literal> event, it is possible to implement
1284     a table rewriting policy only allowing the rewrite in maintenance windows.
1285    </para>
1286
1287    <para>
1288     Here's an example implementing such a policy.
1289 <programlisting>
1290 CREATE OR REPLACE FUNCTION no_rewrite()
1291  RETURNS event_trigger
1292  LANGUAGE plpgsql AS
1293 $$
1294 ---
1295 --- Implement local Table Rewriting policy:
1296 ---   public.foo is not allowed rewriting, ever
1297 ---   other tables are only allowed rewriting between 1am and 6am
1298 ---   unless they have more than 100 blocks
1299 ---
1300 DECLARE
1301   table_oid oid := pg_event_trigger_table_rewrite_oid();
1302   current_hour integer := extract('hour' from current_time);
1303   pages integer;
1304   max_pages integer := 100;
1305 BEGIN
1306   IF pg_event_trigger_table_rewrite_oid() = 'public.foo'::regclass
1307   THEN
1308         RAISE EXCEPTION 'you''re not allowed to rewrite the table %',
1309                         table_oid::regclass;
1310   END IF;
1311
1312   SELECT INTO pages relpages FROM pg_class WHERE oid = table_oid;
1313   IF pages > max_pages
1314   THEN
1315         RAISE EXCEPTION 'rewrites only allowed for table with less than % pages',
1316                         max_pages;
1317   END IF;
1318
1319   IF current_hour NOT BETWEEN 1 AND 6
1320   THEN
1321         RAISE EXCEPTION 'rewrites only allowed between 1am and 6am';
1322   END IF;
1323 END;
1324 $$;
1325
1326 CREATE EVENT TRIGGER no_rewrite_allowed
1327                   ON table_rewrite
1328    EXECUTE FUNCTION no_rewrite();
1329 </programlisting>
1330    </para>
1331  </sect1>
1332
1333   <sect1 id="event-trigger-database-login-example">
1334     <title>A Database Login Event Trigger Example</title>
1335
1336     <para>
1337       The event trigger on the <literal>login</literal> event can be
1338       useful for logging user logins, for verifying the connection and
1339       assigning roles according to current circumstances, or for session
1340       data initialization. It is very important that any event trigger using
1341       the <literal>login</literal> event checks whether or not the database is
1342       in recovery before performing any writes. Writing to a standby server
1343       will make it inaccessible.
1344     </para>
1345
1346     <para>
1347       The following example demonstrates these options.
1348 <programlisting>
1349 -- create test tables and roles
1350 CREATE TABLE user_login_log (
1351   "user" text,
1352   "session_start" timestamp with time zone
1353 );
1354 CREATE ROLE day_worker;
1355 CREATE ROLE night_worker;
1356
1357 -- the example trigger function
1358 CREATE OR REPLACE FUNCTION init_session()
1359   RETURNS event_trigger SECURITY DEFINER
1360   LANGUAGE plpgsql AS
1361 $$
1362 DECLARE
1363   hour integer = EXTRACT('hour' FROM current_time at time zone 'utc');
1364   rec boolean;
1365 BEGIN
1366 -- 1. Forbid logging in between 2AM and 4AM.
1367 IF hour BETWEEN 2 AND 4 THEN
1368   RAISE EXCEPTION 'Login forbidden';
1369 END IF;
1370
1371 -- The checks below cannot be performed on standby servers so
1372 -- ensure the database is not in recovery before we perform any
1373 -- operations.
1374 SELECT pg_is_in_recovery() INTO rec;
1375 IF rec THEN
1376   RETURN;
1377 END IF;
1378
1379 -- 2. Assign some roles. At daytime, grant the day_worker role, else the
1380 -- night_worker role.
1381 IF hour BETWEEN 8 AND 20 THEN
1382   EXECUTE 'REVOKE night_worker FROM ' || quote_ident(session_user);
1383   EXECUTE 'GRANT day_worker TO ' || quote_ident(session_user);
1384 ELSE
1385   EXECUTE 'REVOKE day_worker FROM ' || quote_ident(session_user);
1386   EXECUTE 'GRANT night_worker TO ' || quote_ident(session_user);
1387 END IF;
1388
1389 -- 3. Initialize user session data
1390 CREATE TEMP TABLE session_storage (x float, y integer);
1391 ALTER TABLE session_storage OWNER TO session_user;
1392
1393 -- 4. Log the connection time
1394 INSERT INTO public.user_login_log VALUES (session_user, current_timestamp);
1395
1396 END;
1397 $$;
1398
1399 -- trigger definition
1400 CREATE EVENT TRIGGER init_session
1401   ON login
1402   EXECUTE FUNCTION init_session();
1403 ALTER EVENT TRIGGER init_session ENABLE ALWAYS;
1404 </programlisting>
1405     </para>
1406   </sect1>
1407 </chapter>