Horton

Thread

  1. Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-15T18:13:04Z

    Hi,

I guess it's time to start a new thread here. Please find attached
version 16 of the command trigger patch, with augmented documentation
and “magic variable” support (TG_WHEN, TG_OBJECTID and such).

The current version of the patch only supports PLpgSQL, I need to add
support for the other languages in core but I though Thom would like to
be able to play with a new patch before I finish plpython, plperl and
pltcl support.

This patch also includes edits following latest reviews from both Thom,
Andres and Robert, in particular ANY command triggers are now called
from the same place as specific command triggers and receive the same
parameters.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  2. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-15T21:19:32Z

    On 15 March 2012 18:13, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Hi,
>
> I guess it's time to start a new thread here. Please find attached
> version 16 of the command trigger patch, with augmented documentation
> and “magic variable” support (TG_WHEN, TG_OBJECTID and such).
>
> The current version of the patch only supports PLpgSQL, I need to add
> support for the other languages in core but I though Thom would like to
> be able to play with a new patch before I finish plpython, plperl and
> pltcl support.
>
> This patch also includes edits following latest reviews from both Thom,
> Andres and Robert, in particular ANY command triggers are now called
> from the same place as specific command triggers and receive the same
> parameters.

Good to see that ANY COMMAND triggers contain everything the specific
triggers have.  I've completed a complete re-run of all my testing.

Note: incremental patch attached for the following section...

-----START----

The docs have an excessive opening <varlistentry> tag.  The docs also
list ALTER CAST as an option, which it isn't.  There's an old version
of a paragraph included, immediately followed by its revised version.
It begins with "Triggers on ANY command...".

The example given for the abort_any_command function has a typo.  The
RAISE statement should have a comma after the closing single quote
instead of %.

In doc/src/sgml/plpgsql.sgml:

“The command trigger function return's value is not used.”
should be
“The command trigger function’s return value is not used.”

“This example trigger just raise a...”
should be
“This example trigger just raises a...”

The example procedure isn't called correctly in the create command
trigger statement below it.  It refers to it at "any_snitch", but the
function is just named "snitch".  Also the style is inconsistent with
the other trigger functions further up the page, such as putting the
function language last, showing the return type on the same line as
the CREATE FUNCTION line and using upper-case lettering for keywords.


I don’t understand how functions can return a type of “command
trigger”.  This certainly works, but I’ve never seen a type consisting
of more than one word.  Could you explain this for me?  This is also
at odds with the error message in src/backend/commands/cmdtrigger.c:

errmsg("function \"%s\" must return type \"trigger\"",

Should be “command trigger” as a regular trigger can’t be used on
command triggers.

----END----


At this moment in time, CTAS is still outstanding.  Is the plan to try
to get that in for this release, or as an enhancement in 9.3?

I don’t know if this was a problem before that I didn’t spot
(probably), but triggers for both ANY COMMAND and ALTER FOREIGN TABLE
show a command tag of ALTER TABLE for ALTER FOREIGN TABLE statements
where the column is renamed:

thom@test=# ALTER FOREIGN TABLE test.dict2 RENAME COLUMN word TO words;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
TABLE' objectid=16569 schemaname='test' objectname='dict2'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER TABLE'
objectid=16569 schemaname='test' objectname='dict2'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER TABLE'
objectid=16569 schemaname='test' objectname='dict2'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TABLE'
objectid=16569 schemaname='test' objectname='dict2'
ALTER TABLE

I don’t think this is the fault of the trigger code because it
actually says ALTER TABLE at the bottom, suggesting it’s something
already present.  This isn’t the case when adding or dropping columns.
 Any comments?


Altering the properties of a function (such as cost, security definer,
whether it’s stable etc) doesn’t report the function’s OID:

thom@test=# ALTER FUNCTION test.testfunc2() COST 77;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='test' objectname='testfunc2'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER FUNCTION'
objectid=<NULL> schemaname='test' objectname='testfunc2'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER FUNCTION'
objectid=<NULL> schemaname='test' objectname='testfunc2'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER
FUNCTION' objectid=<NULL> schemaname='test' objectname='testfunc2'
ALTER FUNCTION


I get a garbage objectname for AFTER ANY COMMAND triggers on ALTER
TEXT SEARCH DICTIONARY when changing its options.  It doesn’t show it
in the below example because I can’t get it displaying in plain text,
but where the objectname is blank is where I’m seeing unicode a square
containing “0074” 63 times in a row:

thom@test=# ALTER TEXT SEARCH DICTIONARY testnew.test_stem2 (
StopWords = dutch );
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER TEXT
SEARCH DICTIONARY' objectid=16617 schemaname='testnew'
objectname='test_stem2'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='ALTER TEXT SEARCH
DICTIONARY' objectid=16617 schemaname='testnew'
objectname='test_stem2'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='ALTER TEXT SEARCH
DICTIONARY' objectid=16617 schemaname='testnew'
objectname='test_stem2'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER TEXT
SEARCH DICTIONARY' objectid=16617 schemaname='testnew'
objectname='                                                               '
ALTER TEXT SEARCH DICTIONARY


Specific command triggers on ALTER VIEW don’t work at all:

thom@test=# ALTER VIEW view_test OWNER TO test;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='ALTER VIEW'
objectid=16625 schemaname='public' objectname='view_test'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='ALTER VIEW'
objectid=16625 schemaname='public' objectname='view_test'
ALTER VIEW


Command triggers that fire for CREATE RULE show a schema, but DROP
RULE doesn’t.  Which is it?:

thom@test=# CREATE RULE notify_test AS ON UPDATE TO seq_table DO ALSO
NOTIFY test; -- support for testing DROP RULE
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='CREATE
RULE' objectid=<NULL> schemaname='public' objectname='notify_test'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='CREATE RULE'
objectid=<NULL> schemaname='public' objectname='notify_test'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='CREATE RULE'
objectid=16706 schemaname='public' objectname='notify_test'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='CREATE RULE'
objectid=16706 schemaname='public' objectname='notify_test'
CREATE RULE
thom@test=# DROP RULE notify_test ON seq_table;
NOTICE:  Command trigger on any: tg_when='BEFORE' cmd_tag='DROP RULE'
objectid=16706 schemaname='<NULL>' objectname='notify_test'
NOTICE:  Command trigger: tg_when='BEFORE' cmd_tag='DROP RULE'
objectid=16706 schemaname='<NULL>' objectname='notify_test'
NOTICE:  Command trigger: tg_when='AFTER' cmd_tag='DROP RULE'
objectid=<NULL> schemaname='<NULL>' objectname='notify_test'
NOTICE:  Command trigger on any: tg_when='AFTER' cmd_tag='DROP RULE'
objectid=<NULL> schemaname='<NULL>' objectname='notify_test'
DROP RULE

This same behaviour exists for DROP TRIGGER.


Regards

Thom

  3. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-15T21:58:49Z

    Thanks for testing this new version (again).

A quick answer now, I'll send another patch tomorrow.

Thom Brown <thombrown@gmail.com> writes:
> I don’t understand how functions can return a type of “command
> trigger”.  This certainly works, but I’ve never seen a type consisting
> of more than one word.  Could you explain this for me?  This is also

I tricked that in the grammar, the type is called cmdtrigger but I
though it wouldn't be a good choice for the SQL statement.

> at odds with the error message in src/backend/commands/cmdtrigger.c:
>
> errmsg("function \"%s\" must return type \"trigger\"",

I realized I needed another trigger like data type after I had worked
this message, I need to get back on it, thanks.

> At this moment in time, CTAS is still outstanding.  Is the plan to try
> to get that in for this release, or as an enhancement in 9.3?

The plan is to get CTAS as a utility command in 9.2 then update the
command trigger patch to benefit from the new situation. We've been
wondering about making its own commit fest entry for that patch, it's
now clear in my mind, that needs to happen.

Stay tuned, follow up email due tomorrow.
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  4. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-15T22:06:09Z

    Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>> At this moment in time, CTAS is still outstanding.  Is the plan to try
>> to get that in for this release, or as an enhancement in 9.3?
>
> The plan is to get CTAS as a utility command in 9.2 then update the
> command trigger patch to benefit from the new situation. We've been
> wondering about making its own commit fest entry for that patch, it's
> now clear in my mind, that needs to happen.

  https://commitfest.postgresql.org/action/patch_view?id=823

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  5. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-15T22:41:21Z

    On 15 March 2012 22:06, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>>> At this moment in time, CTAS is still outstanding.  Is the plan to try
>>> to get that in for this release, or as an enhancement in 9.3?
>>
>> The plan is to get CTAS as a utility command in 9.2 then update the
>> command trigger patch to benefit from the new situation. We've been
>> wondering about making its own commit fest entry for that patch, it's
>> now clear in my mind, that needs to happen.
>
>  https://commitfest.postgresql.org/action/patch_view?id=823

Looks like the ctas-on-command-triggers-01.patch patch needs re-basing.

Thom

  6. Re: Command Triggers, v16

    Andres Freund <andres@anarazel.de> — 2012-03-16T08:13:53Z

    On Thursday, March 15, 2012 11:41:21 PM Thom Brown wrote:
> On 15 March 2012 22:06, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> > Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> >>> At this moment in time, CTAS is still outstanding.  Is the plan to try
> >>> to get that in for this release, or as an enhancement in 9.3?
> >> 
> >> The plan is to get CTAS as a utility command in 9.2 then update the
> >> command trigger patch to benefit from the new situation. We've been
> >> wondering about making its own commit fest entry for that patch, it's
> >> now clear in my mind, that needs to happen.
> > 
> >  https://commitfest.postgresql.org/action/patch_view?id=823
> 
> Looks like the ctas-on-command-triggers-01.patch patch needs re-basing.
I can do that - but imo the other patch (not based on the command triggers 
stuff) is the relevant for now as this patch ought to be applied before 
command triggers. It doesn't seem to make too much sense to rebase it 
frequently as long as the command triggers patch isn't stable...

Any reason you would prefer it being rebased?

Andres

  7. Re: Command Triggers, v16

    Christian Ullrich <chris@chrullrich.net> — 2012-03-16T08:30:33Z

    * Thom Brown wrote:

> I don’t understand how functions can return a type of “command
> trigger”.  This certainly works, but I’ve never seen a type consisting
> of more than one word.  Could you explain this for me?  This is also

postgres=> with types (name) as
postgres-> (select format_type(oid, NULL) from pg_type)
postgres-> select name from types where name like '% %'
postgres-> and not name like '%[]';
             name
-----------------------------
  double precision
  character varying
  time without time zone
  timestamp without time zone
  timestamp with time zone
  time with time zone
  bit varying
(7 Zeilen)

I think these are all specially handled in the parser.

-- 
Christian Ullrich

  8. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-16T08:30:58Z

    On 16 March 2012 08:13, Andres Freund <andres@anarazel.de> wrote:
> On Thursday, March 15, 2012 11:41:21 PM Thom Brown wrote:
>> Looks like the ctas-on-command-triggers-01.patch patch needs re-basing.
> I can do that - but imo the other patch (not based on the command triggers
> stuff) is the relevant for now as this patch ought to be applied before
> command triggers. It doesn't seem to make too much sense to rebase it
> frequently as long as the command triggers patch isn't stable...
>
> Any reason you would prefer it being rebased?

Using latest Git master without any additional patches, I can't get it to apply:

Hunk #1 FAILED at 16.
Hunk #2 succeeded at 22 (offset -1 lines).
1 out of 2 hunks FAILED -- saving rejects to file
src/include/commands/tablecmds.h.rej

Thom

  9. Re: Command Triggers, v16

    Andres Freund <andres@anarazel.de> — 2012-03-16T08:43:53Z

    Hi,

On Thursday, March 15, 2012 10:58:49 PM Dimitri Fontaine wrote:
> I tricked that in the grammar, the type is called cmdtrigger but I
> though it wouldn't be a good choice for the SQL statement.
Hm. I am decidedly unhappy with that grammar hackery... But then maybe I am 
squeamish.

> + oid  |  typname   | oid  |  proname   
> +------+------------+------+------------
> + 1790 | refcursor  |   46 | textin
> + 3838 | cmdtrigger | 2300 | trigger_in
> +(2 rows)
Hm. Wonder if its a good idea to reuse trigger_in. So far we have duplicated 
functions for that.

> @@ -482,12 +494,21 @@ ListCommandTriggers(CommandContext cmd)
> 
>         switch (form->ctgtype)
>         {
>         
>             case CMD_TRIGGER_FIRED_BEFORE:
> -               cmd->before = lappend_oid(cmd->before, form->ctgfoid);
> +           {
> +               if (list_any_triggers)
> +                   cmd->before_any = lappend_oid(cmd->before_any,
> form->ctgfoid); +               else
> +                   cmd->before = lappend_oid(cmd->before, form->ctgfoid);
> 
>                 break;
> 
> -
> ...
> +       case CMD_TRIGGER_FIRED_BEFORE:
> +       {
> +           whenstr = "BEFORE";
> +
> +           foreach(cell, cmd->before_any)
> +           {
> +               Oid proc = lfirst_oid(cell);
> +
> +               call_cmdtrigger_procedure(cmd, (RegProcedure)proc,
> whenstr); +           }
> +           foreach(cell, cmd->before)
> +           {
> +               Oid proc = lfirst_oid(cell);
> +
> +               call_cmdtrigger_procedure(cmd, (RegProcedure)proc,
> whenstr); +           }
> +           break;
> +       }
This will have the effect of calling triggers outside of alphabetic order. I 
don't think thats a good idea even if one part is ANY and the other a specific 
command.
I don't think there is any reason anymore to separate the two? The only 
callsite seems to look like:

632-		default:
633:			ListCommandTriggers(cmd, true);   /* list ANY command triggers */
634:			ListCommandTriggers(cmd, false);  /* and triggers for this 
command tag */

Andres

  10. Re: Command Triggers, v16

    Andres Freund <andres@anarazel.de> — 2012-03-16T08:45:15Z

    On Friday, March 16, 2012 09:30:58 AM Thom Brown wrote:
> On 16 March 2012 08:13, Andres Freund <andres@anarazel.de> wrote:
> > On Thursday, March 15, 2012 11:41:21 PM Thom Brown wrote:
> >> Looks like the ctas-on-command-triggers-01.patch patch needs re-basing.
> > 
> > I can do that - but imo the other patch (not based on the command
> > triggers stuff) is the relevant for now as this patch ought to be
> > applied before command triggers. It doesn't seem to make too much sense
> > to rebase it frequently as long as the command triggers patch isn't
> > stable...
> > 
> > Any reason you would prefer it being rebased?
> 
> Using latest Git master without any additional patches, I can't get it to
> apply:
> 
> Hunk #1 FAILED at 16.
> Hunk #2 succeeded at 22 (offset -1 lines).
> 1 out of 2 hunks FAILED -- saving rejects to file
> src/include/commands/tablecmds.h.rej
Did you read the paragraph above?

Andres

  11. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-16T08:48:49Z

    On 15 March 2012 21:58, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Thom Brown <thombrown@gmail.com> writes:
>> I don’t understand how functions can return a type of “command
>> trigger”.  This certainly works, but I’ve never seen a type consisting
>> of more than one word.  Could you explain this for me?  This is also
>
> I tricked that in the grammar, the type is called cmdtrigger but I
> though it wouldn't be a good choice for the SQL statement.

Christian sent me a message mentioning that we've pretty much always
had data types consisting of more than one word (e.g.  timestamp
without time zone).  So I completely retract my question as it's
obviously nonsense.

Thom

  12. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-16T08:55:10Z

    On 16 March 2012 08:45, Andres Freund <andres@anarazel.de> wrote:
> On Friday, March 16, 2012 09:30:58 AM Thom Brown wrote:
>> On 16 March 2012 08:13, Andres Freund <andres@anarazel.de> wrote:
>> > On Thursday, March 15, 2012 11:41:21 PM Thom Brown wrote:
>> >> Looks like the ctas-on-command-triggers-01.patch patch needs re-basing.
>> >
>> > I can do that - but imo the other patch (not based on the command
>> > triggers stuff) is the relevant for now as this patch ought to be
>> > applied before command triggers. It doesn't seem to make too much sense
>> > to rebase it frequently as long as the command triggers patch isn't
>> > stable...
>> >
>> > Any reason you would prefer it being rebased?
>>
>> Using latest Git master without any additional patches, I can't get it to
>> apply:
>>
>> Hunk #1 FAILED at 16.
>> Hunk #2 succeeded at 22 (offset -1 lines).
>> 1 out of 2 hunks FAILED -- saving rejects to file
>> src/include/commands/tablecmds.h.rej
> Did you read the paragraph above?

Yes, but I don't think I'm clear on what you mean.  Are you saying I
should use ctas-01.patch instead of ctas-on-command-triggers-01.patch?
 If so, that patch results in me not being able to apply Dimitri's
command triggers patch.  And I thought that patch doesn't actually
cause triggers to fire on CTAS?

Thom

  13. Re: Command Triggers, v16

    Andres Freund <andres@anarazel.de> — 2012-03-16T09:40:17Z

    On Friday, March 16, 2012 09:55:10 AM Thom Brown wrote:
> On 16 March 2012 08:45, Andres Freund <andres@anarazel.de> wrote:
> > On Friday, March 16, 2012 09:30:58 AM Thom Brown wrote:
> >> On 16 March 2012 08:13, Andres Freund <andres@anarazel.de> wrote:
> >> > On Thursday, March 15, 2012 11:41:21 PM Thom Brown wrote:
> >> >> Looks like the ctas-on-command-triggers-01.patch patch needs
> >> >> re-basing.
> >> > 
> >> > I can do that - but imo the other patch (not based on the command
> >> > triggers stuff) is the relevant for now as this patch ought to be
> >> > applied before command triggers. It doesn't seem to make too much
> >> > sense to rebase it frequently as long as the command triggers patch
> >> > isn't stable...
> >> > 
> >> > Any reason you would prefer it being rebased?
> >> 
> >> Using latest Git master without any additional patches, I can't get it
> >> to apply:
> >> 
> >> Hunk #1 FAILED at 16.
> >> Hunk #2 succeeded at 22 (offset -1 lines).
> >> 1 out of 2 hunks FAILED -- saving rejects to file
> >> src/include/commands/tablecmds.h.rej
> > 
> > Did you read the paragraph above?
> 
> Yes, but I don't think I'm clear on what you mean.  Are you saying I
> should use ctas-01.patch instead of ctas-on-command-triggers-01.patch?
>  If so, that patch results in me not being able to apply Dimitri's
> command triggers patch.  And I thought that patch doesn't actually
> cause triggers to fire on CTAS?
Well. Why do you want to apply them concurrently? As far as I understand the 
plan is to get ctas-as-utility merged with master and then let dim rebase the 
ddl trigger patch.
The ctas-as-utility stuff imo is worthy of being applied independently of DDL 
triggers. The current duplication in the code lead to multiple bugs already.

Andres

  14. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-16T09:40:46Z

    Andres Freund <andres@anarazel.de> writes:
> On Thursday, March 15, 2012 10:58:49 PM Dimitri Fontaine wrote:
>> I tricked that in the grammar, the type is called cmdtrigger but I
>> though it wouldn't be a good choice for the SQL statement.
> Hm. I am decidedly unhappy with that grammar hackery... But then maybe I am
> squeamish.

It's easy to remove that hack and get back to having the user visible
type be cmdtrigger, but as this type only serves as a marker for PL
compiling function (validate handler) I though having a user friendly
name was important here.

>> + oid  |  typname   | oid  |  proname
>> +------+------------+------+------------
>> + 1790 | refcursor  |   46 | textin
>> + 3838 | cmdtrigger | 2300 | trigger_in
>> +(2 rows)
> Hm. Wonder if its a good idea to reuse trigger_in. So far we have duplicated
> functions for that.

Again, if we think the use case warrants duplicating code rather than
playing with the type definition, I will do that.

> This will have the effect of calling triggers outside of alphabetic order. I
> don't think thats a good idea even if one part is ANY and the other a specific
> command.
> I don't think there is any reason anymore to separate the two? The only
> callsite seems to look like:

The idea is to have a predictable ordering of command triggers. The code
changed in the patch v16 (you pasted code from git in between v15 and
v16, I cleaned it up) and is now easier to read:

		case CMD_TRIGGER_FIRED_BEFORE:
			whenstr = "BEFORE";
			procs[0] = cmd->before_any;
			procs[1] = cmd->before;
			break;

		case CMD_TRIGGER_FIRED_AFTER:
			whenstr = "AFTER";
			procs[0] = cmd->after;
			procs[1] = cmd->after_any;
			break;

So it's BEFORE ANY then BEFORE command then AFTER command then AFTER
ANY. That's an arbitrary I made and we can easily reconsider. Triggers
are called in alphabetical order in each “slot” here.

In my mind it makes sense to have ANY triggers around the specific
triggers, but it's hard to explain why that feels better.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  15. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-16T11:42:20Z

    Thom Brown <thombrown@gmail.com> writes:
> Note: incremental patch attached for the following section...

Applied, thanks!

> I don’t know if this was a problem before that I didn’t spot
> (probably), but triggers for both ANY COMMAND and ALTER FOREIGN TABLE
> show a command tag of ALTER TABLE for ALTER FOREIGN TABLE statements
> where the column is renamed:
>
> I don’t think this is the fault of the trigger code because it
> actually says ALTER TABLE at the bottom, suggesting it’s something
> already present.  This isn’t the case when adding or dropping columns.
>  Any comments?

We're building command trigger on top of the existing code. From the
grammar we can see that an alter table and an alter foreign table are
processed as the same command.

AlterForeignTableStmt:
	ALTER FOREIGN TABLE relation_expr alter_table_cmds
		{
		AlterTableStmt *n = makeNode(AlterTableStmt);

So while I think we could want to revisit that choice down the road, I
don't think that's for the command triggers patch to do it.

> Altering the properties of a function (such as cost, security definer,
> whether it’s stable etc) doesn’t report the function’s OID:

That's now fixed. I've checked that all the other places where we're
saying objectId = InvalidOid are related to before create or after drop
commands.

> I get a garbage objectname for AFTER ANY COMMAND triggers on ALTER
> TEXT SEARCH DICTIONARY when changing its options.  It doesn’t show it
> in the below example because I can’t get it displaying in plain text,
> but where the objectname is blank is where I’m seeing unicode a square
> containing “0074” 63 times in a row:

I couldn't reproduce, but I've spotted the problem in the source, and
fixed it. I could find a couple other places that should have been using
pstrdup(NameStr(…)) too, and fixed them. I added a test.

> Specific command triggers on ALTER VIEW don’t work at all:

Can't reproduce, and that's already part of the regression tests.

> Command triggers that fire for CREATE RULE show a schema, but DROP
> RULE doesn’t.  Which is it?:

Oh, both RULE and TRIGGER are not qualified, and I was filling the
schemaname with the schema of the relation they refer to in the CREATE
command, and had DROP correctly handling the TRIGGER case.

That's now fixed, schemaname is NULL in all cases here.

You can read the changes here:

  https://github.com/dimitri/postgres/compare/5e8e37922d...144d870162

And I've been attaching an incremental patch too. Next patch revision is
expected later today with support for plpython, plperl and pltcl.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  16. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-16T11:57:23Z

    On 16 March 2012 11:42, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Thom Brown <thombrown@gmail.com> writes:
>> Specific command triggers on ALTER VIEW don’t work at all:
>
> Can't reproduce, and that's already part of the regression tests.

This was a problem my side (a mistake I made previously) as I hadn't
added this particular one into my list of created command triggers.  I
had then seen the triggers fire, but forgot to go back and remove my
statement from the draft email.  Apologies.

> And I've been attaching an incremental patch too. Next patch revision is
> expected later today with support for plpython, plperl and pltcl.

Okay, I shalln't do any more testing until the next patch.  I should
probably have worked on automating my tests more, but never got round
to it.

Thom

  17. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-16T12:07:45Z

    Thom Brown <thombrown@gmail.com> writes:
> Okay, I shalln't do any more testing until the next patch.  I should
> probably have worked on automating my tests more, but never got round
> to it.

  make installcheck :)

That said, your test allow to spot OID problems that we can't add in the
regression tests (OID being too volatile would break them), and I have
to look at how to add regression tests for the other pls support…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  18. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-16T12:22:59Z

    On 16 March 2012 12:07, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Thom Brown <thombrown@gmail.com> writes:
>> Okay, I shalln't do any more testing until the next patch.  I should
>> probably have worked on automating my tests more, but never got round
>> to it.
>
>  make installcheck :)
>
> That said, your test allow to spot OID problems that we can't add in the
> regression tests (OID being too volatile would break them), and I have
> to look at how to add regression tests for the other pls support…

Yes, that's why I don't use the regression tests. :)

Thom

  19. Re: Command Triggers, v16

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-16T13:50:55Z

    Andres Freund <andres@anarazel.de> writes:
> On Thursday, March 15, 2012 10:58:49 PM Dimitri Fontaine wrote:
>> I tricked that in the grammar, the type is called cmdtrigger but I
>> though it wouldn't be a good choice for the SQL statement.

> Hm. I am decidedly unhappy with that grammar hackery... But then maybe I am 
> squeamish.

Multi-word type names are a serious pain in the ass; they require
hackery in a lot of places.  We support the ones that the SQL spec
requires us to, but I will object in the strongest terms to inventing
any that are not required by spec.  I object in even stronger terms to
the incredibly klugy way you did it here.

If you think "cmdtrigger" isn't a good name maybe you should have
picked a different one to start with.

While I'm looking at the grammar ... it also seems like a serious
PITA from a maintenance standpoint that we're now going to have to
adjust the CREATE COMMAND TRIGGER productions every time somebody
thinks of a new SQL command.  Maybe we should drop this whole idea
of specifying which commands a trigger acts on at the SQL level,
and just have one-size-fits-all command triggers.  Or perhaps have
the selection be on the basis of strings that are matched to command
tags, instead of grammar constructs.

			regards, tom lane

  20. Re: Command Triggers, v16

    Andres Freund <andres@anarazel.de> — 2012-03-16T13:57:24Z

    On Friday, March 16, 2012 02:50:55 PM Tom Lane wrote:
> While I'm looking at the grammar ... it also seems like a serious
> PITA from a maintenance standpoint that we're now going to have to
> adjust the CREATE COMMAND TRIGGER productions every time somebody
> thinks of a new SQL command. 
I don't find that argument really convincing. The current state of the patch  
will often require attention to command triggers anyway...

Andres

  21. Re: Command Triggers, v16

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-16T14:01:42Z

    Andres Freund <andres@anarazel.de> writes:
> On Friday, March 16, 2012 02:50:55 PM Tom Lane wrote:
>> While I'm looking at the grammar ... it also seems like a serious
>> PITA from a maintenance standpoint that we're now going to have to
>> adjust the CREATE COMMAND TRIGGER productions every time somebody
>> thinks of a new SQL command. 

> I don't find that argument really convincing.

Well, how about just plain parser size bloat?  Did anyone look at
how much bigger gram.o becomes with this?  Bigger parser -> slower,
for everybody, whether they care about this feature or not.

			regards, tom lane

  22. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-16T15:06:35Z

    Tom Lane <tgl@sss.pgh.pa.us> writes:
> Multi-word type names are a serious pain in the ass; they require
> hackery in a lot of places.  We support the ones that the SQL spec
> requires us to, but I will object in the strongest terms to inventing
> any that are not required by spec.  I object in even stronger terms to
> the incredibly klugy way you did it here.

Ok, it's so klugy that removing the support from the parser is going to
be easy.

> If you think "cmdtrigger" isn't a good name maybe you should have
> picked a different one to start with.

Well, I think it's a good internal name. I'm not too sure about exposing
it, the only reason why it's a good name is because it's a single not
too long word, after all. Not very “SQLish”.

I'm putting cmdtrigger as the user visible name in the next version of
the patch, if you come up with something potentially more user friendly
feel free to suggest.

> While I'm looking at the grammar ... it also seems like a serious
> PITA from a maintenance standpoint that we're now going to have to
> adjust the CREATE COMMAND TRIGGER productions every time somebody
> thinks of a new SQL command.  Maybe we should drop this whole idea
> of specifying which commands a trigger acts on at the SQL level,
> and just have one-size-fits-all command triggers.  Or perhaps have
> the selection be on the basis of strings that are matched to command
> tags, instead of grammar constructs.

The only advantage of doing it this way is giving the user an early
error when he's trying to attach to a non-supported command. I wouldn't
want to remove that list only to find myself adding a list of non
supported commands so that we can still refuse creating the useless
command trigger.

And as Andres said, adding command trigger support to a new command
isn't exactly transparent (it's still mostly mechanical though), so that
does not seems so big a pain to me. Of course I have been having my head
in there for a long time now…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  23. Re: Command Triggers, v16

    Robert Haas <robertmhaas@gmail.com> — 2012-03-16T15:27:57Z

    On Fri, Mar 16, 2012 at 7:42 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
>> I don’t know if this was a problem before that I didn’t spot
>> (probably), but triggers for both ANY COMMAND and ALTER FOREIGN TABLE
>> show a command tag of ALTER TABLE for ALTER FOREIGN TABLE statements
>> where the column is renamed:
>>
>> I don’t think this is the fault of the trigger code because it
>> actually says ALTER TABLE at the bottom, suggesting it’s something
>> already present.  This isn’t the case when adding or dropping columns.
>>  Any comments?
>
> We're building command trigger on top of the existing code. From the
> grammar we can see that an alter table and an alter foreign table are
> processed as the same command.

This seems pretty dicey.  I understand your position that it can't be
the job of the command triggers patch to fix every infelicity of the
backend, but on the flip side, code reuse is a good thing.  We want to
increase, not decrease, the number of places where the same code can
be used to implement multiple commands that do related things; and
there has to be some way to do that without breaking command triggers.
 Moreover, we really don't want the details of how things are handled
internally to be user-visible, because sometimes we refactor things to
improve the quality of our code, and I don't want to get bug reports
when we do that...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  24. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-16T15:37:43Z

    Robert Haas <robertmhaas@gmail.com> writes:
> there has to be some way to do that without breaking command triggers.

Sure, special case the switch branch in utility.c so as to return a
different command tag for ALTER TABLE and ALTER FOREIGN TABLE. For
precedents, see AlterObjectTypeCommandTag(ObjectType objtype) and

	case T_DropStmt:
		switch (((DropStmt *) parsetree)->removeType)

	case T_DefineStmt:
		switch (((DefineStmt *) parsetree)->kind)

So, do we want to do the same thing for ALTER FOREIGN TABLE, and should
I do that in the command triggers patch?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  25. Re: Command Triggers, v16

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-16T16:26:51Z

    Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> If you think "cmdtrigger" isn't a good name maybe you should have
>> picked a different one to start with.

> Well, I think it's a good internal name. I'm not too sure about exposing
> it, the only reason why it's a good name is because it's a single not
> too long word, after all. Not very SQLish.

> I'm putting cmdtrigger as the user visible name in the next version of
> the patch, if you come up with something potentially more user friendly
> feel free to suggest.

How about "commandtrigger" or "command_trigger"?  Typing a few more
characters in this context doesn't seem like a deal-breaker to me.

			regards, tom lane

  26. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-16T19:51:35Z

    On 16 March 2012 16:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> If you think "cmdtrigger" isn't a good name maybe you should have
>>> picked a different one to start with.
>
>> Well, I think it's a good internal name. I'm not too sure about exposing
>> it, the only reason why it's a good name is because it's a single not
>> too long word, after all. Not very “SQLish”.
>
>> I'm putting cmdtrigger as the user visible name in the next version of
>> the patch, if you come up with something potentially more user friendly
>> feel free to suggest.
>
> How about "commandtrigger" or "command_trigger"?  Typing a few more
> characters in this context doesn't seem like a deal-breaker to me.

+1

No objections to either of those suggestions, although I'd lean
towards the one without the underscore, not for any technical reason.
There is a precedent for a type with an underscore in its name
(txid_snapshot) but seems to be the exception.

Thom

  27. Re: Command Triggers, v16

    Andres Freund <andres@2ndquadrant.com> — 2012-03-25T16:15:34Z

    On Friday, March 16, 2012 10:40:46 AM Dimitri Fontaine wrote:
> > This will have the effect of calling triggers outside of alphabetic
> > order. I don't think thats a good idea even if one part is ANY and the
> > other a specific command.
> > I don't think there is any reason anymore to separate the two? The only
> 
> > callsite seems to look like:
> The idea is to have a predictable ordering of command triggers. The code
> changed in the patch v16 (you pasted code from git in between v15 and
> v16, I cleaned it up) and is now easier to read:
> 
>                 case CMD_TRIGGER_FIRED_BEFORE:
>                         whenstr = "BEFORE";
>                         procs[0] = cmd->before_any;
>                         procs[1] = cmd->before;
>                         break;
> 
>                 case CMD_TRIGGER_FIRED_AFTER:
>                         whenstr = "AFTER";
>                         procs[0] = cmd->after;
>                         procs[1] = cmd->after_any;
>                         break;
> 
> So it's BEFORE ANY then BEFORE command then AFTER command then AFTER
> ANY. That's an arbitrary I made and we can easily reconsider. Triggers
> are called in alphabetical order in each “slot” here.
> 
> In my mind it makes sense to have ANY triggers around the specific
> triggers, but it's hard to explain why that feels better.
I still think this would be a mistake. I don't have a hard time imagining 
usecases where a specific trigger should be called before or after an ANY 
trigger because e.g. it wants to return a more specific error or doesn't want 
to check all preconditions already done by the ANY trigger... All that would 
be precluded by enforcing a strict ordering between ANY and specific triggers.
I don't see a use-case that would benefit from the current behaviour...

Andres
-- 
Andres Freund		http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

  28. Re: Command Triggers, v16

    Robert Haas <robertmhaas@gmail.com> — 2012-03-26T12:16:51Z

    On Sun, Mar 25, 2012 at 12:15 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> On Friday, March 16, 2012 10:40:46 AM Dimitri Fontaine wrote:
>> > This will have the effect of calling triggers outside of alphabetic
>> > order. I don't think thats a good idea even if one part is ANY and the
>> > other a specific command.
>> > I don't think there is any reason anymore to separate the two? The only
>>
>> > callsite seems to look like:
>> The idea is to have a predictable ordering of command triggers. The code
>> changed in the patch v16 (you pasted code from git in between v15 and
>> v16, I cleaned it up) and is now easier to read:
>>
>>                 case CMD_TRIGGER_FIRED_BEFORE:
>>                         whenstr = "BEFORE";
>>                         procs[0] = cmd->before_any;
>>                         procs[1] = cmd->before;
>>                         break;
>>
>>                 case CMD_TRIGGER_FIRED_AFTER:
>>                         whenstr = "AFTER";
>>                         procs[0] = cmd->after;
>>                         procs[1] = cmd->after_any;
>>                         break;
>>
>> So it's BEFORE ANY then BEFORE command then AFTER command then AFTER
>> ANY. That's an arbitrary I made and we can easily reconsider. Triggers
>> are called in alphabetical order in each “slot” here.
>>
>> In my mind it makes sense to have ANY triggers around the specific
>> triggers, but it's hard to explain why that feels better.
> I still think this would be a mistake. I don't have a hard time imagining
> usecases where a specific trigger should be called before or after an ANY
> trigger because e.g. it wants to return a more specific error or doesn't want
> to check all preconditions already done by the ANY trigger... All that would
> be precluded by enforcing a strict ordering between ANY and specific triggers.
> I don't see a use-case that would benefit from the current behaviour...

Dimitri's proposed behavior would be advantageous if you have an ANY
trigger that wants to "take over the world" and make sure that nobody
else can run before it.  I think, though, that's not a case we want to
cater to - all of this stuff requires superuser privileges anyway, so
we should assume that the DBA knows what he's doing.  So +1 for making
it strictly alphabetical, as we do with other triggers.  Everything
that can be done under Dimitri's proposal can also be done in that
scheme, but the reverse is not true.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  29. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-26T19:24:46Z

    Robert Haas <robertmhaas@gmail.com> writes:
> Dimitri's proposed behavior would be advantageous if you have an ANY
> trigger that wants to "take over the world" and make sure that nobody
> else can run before it.  I think, though, that's not a case we want to
> cater to - all of this stuff requires superuser privileges anyway, so
> we should assume that the DBA knows what he's doing.  So +1 for making

What about extensions?

One use case would be for londiste/slony/bucardo to rewrite the command
and queue its text, that will be done in C and should probably be done
first. Using an ANY command trigger means that code will run before user
specific code (or likewise after it).

As I said it's not that clear in my head, but when thinking about
command trigger and extensions, it could be better to impose an
arbitrary order here.

> it strictly alphabetical, as we do with other triggers.  Everything
> that can be done under Dimitri's proposal can also be done in that
> scheme, but the reverse is not true.

That's true too. I'm just not sure how much it applies to code installed
by the DBA as opposed to written by the DBA. I'll be happy to edit the
patch to melt both lists if that's the decision, it's not hard to do so.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  30. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-26T19:36:40Z

    Can someone clarify whether this will be reviewed by a committer?
Will there be time to get this reviewed before the commitfest closes?
I get the impression the commitfest closure is fairly imminent.

Thom

  31. Re: Command Triggers, v16

    Robert Haas <robertmhaas@gmail.com> — 2012-03-26T20:05:42Z

    On Mon, Mar 26, 2012 at 3:24 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> One use case would be for londiste/slony/bucardo to rewrite the command
> and queue its text, that will be done in C and should probably be done
> first. Using an ANY command trigger means that code will run before user
> specific code (or likewise after it).

And, if the user wants it to be run first, he or she can do that.  But
suppose he wants to run it first, and also forbid users whose username
starts with the letter b from running the ANALYZE command.  Well,
then, he now wants that trigger to come before the other one, even
though the Slony trigger is for all commands (maybe) and the other
just for ANALYZE (maybe).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  32. Re: Command Triggers, v16

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-26T20:06:36Z

    Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Dimitri's proposed behavior would be advantageous if you have an ANY
>> trigger that wants to "take over the world" and make sure that nobody
>> else can run before it.  I think, though, that's not a case we want to
>> cater to - all of this stuff requires superuser privileges anyway, so
>> we should assume that the DBA knows what he's doing.  So +1 for making

> What about extensions?

> One use case would be for londiste/slony/bucardo to rewrite the command
> and queue its text, that will be done in C and should probably be done
> first. Using an ANY command trigger means that code will run before user
> specific code (or likewise after it).

Unless you intend a restriction that there be only one ANY trigger,
I don't see how that follows.  AFAICS, the only way to guarantee "my
trigger runs first" is to give it a name alphabetically before anything
else in the system.

Also, it strikes me that in most of the trigger ordering cases I've
seen, it's actually more interesting to want to be sure that a
particular trigger runs *last* so that its effects can't be modified
by some other, hypothetically less trusted, trigger.

So I don't think that the mere fact of being an ANY trigger rather than
a command-specific trigger should be taken to mean that a particular
ordering is desirable.  Trigger name order isn't the greatest solution
by any means, but it's more flexible than hard-wiring according to
trigger type.

			regards, tom lane

  33. Re: Command Triggers, v16

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-26T20:08:26Z

    Thom Brown <thombrown@gmail.com> writes:
> Can someone clarify whether this will be reviewed by a committer?
> Will there be time to get this reviewed before the commitfest closes?
> I get the impression the commitfest closure is fairly imminent.

I don't have that impression.  (I wish I did.)

			regards, tom lane

  34. Re: Command Triggers, v16

    Robert Haas <robertmhaas@gmail.com> — 2012-03-26T20:16:24Z

    On Mon, Mar 26, 2012 at 3:36 PM, Thom Brown <thombrown@gmail.com> wrote:
> Can someone clarify whether this will be reviewed by a committer?
> Will there be time to get this reviewed before the commitfest closes?
> I get the impression the commitfest closure is fairly imminent.

Well, I have been holding off for two reasons:

1. It sure seems like there is an awful lot of code churn and design
work going on.

2. I'm not sure which patches Tom is planning to look at or in what
order, so I've been avoiding the ones he seems to be taking an
interest in.

Personally, I am about at the point where I'd like to punt everything
and move on.  As nice as it would be to squeeze a few more things into
9.2, there WILL be a 9.3.  If a few less people had submitted
half-baked code at the last minute and a few more people had helped
with review, we'd be done by now.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  35. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-26T20:18:59Z

    Tom Lane <tgl@sss.pgh.pa.us> writes:
> So I don't think that the mere fact of being an ANY trigger rather than
> a command-specific trigger should be taken to mean that a particular
> ordering is desirable.  Trigger name order isn't the greatest solution
> by any means, but it's more flexible than hard-wiring according to
> trigger type.

That ANY sandwich idea is then dead, I will fix it tomorrow to rather
just handle a single list of BEFORE and AFTER triggers (that's 2 lists
total) ordered by trigger name.

v19 will also integrate latest doc comments from Thom and most from
Andres, I don't know how to fix the plpython specifics he's talking
about.

About the reviewing and the commit fest closing, even if that patch is
big it's a principled implementation: the integration of the facility is
done in the same way in lots of different places, and is not rocket
science either (we removed all the complexity). So I guess it's not
really an herculean job here, just a certain amount of mechanical edits:
we just support too many commands ;)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  36. Re: Command Triggers, v16

    Andres Freund <andres@2ndquadrant.com> — 2012-03-26T20:22:07Z

    On Monday, March 26, 2012 10:18:59 PM Dimitri Fontaine wrote:
>  don't know how to fix the plpython specifics he's talking
> about.
Just copy what is done in the normal trigger handling facility (the decref 
both in the CATCH and in the normal path). Ping me some other way if you need 
help...

Andres
-- 
Andres Freund		http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

  37. Re: Command Triggers, v16

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-26T20:36:30Z

    Robert Haas <robertmhaas@gmail.com> writes:
> 1. It sure seems like there is an awful lot of code churn and design
> work going on.

There has only been minor adjustments for a while now, and they have
been visible because Thom was doing lots of testing for me and it was
way easier for me to publish a new version and have a test result the
next day (thanks again Thom).

> Personally, I am about at the point where I'd like to punt everything
> and move on.  As nice as it would be to squeeze a few more things into
> 9.2, there WILL be a 9.3.  If a few less people had submitted
> half-baked code at the last minute and a few more people had helped
> with review, we'd be done by now.

Well, wait a minute. There's a difference between half-baked and
reacting to a review that changes the goal of a patch. My idea of the
code I wanted to write here is extremely different from what we as a
community decided to be doing. The main part of the code churn has been
answering to review, removing features and cleaning the code afterwards.

The only major design decision that I had to change here has been about
from where to call in the command trigger code in the existing commands
implementation, and it was done before entering this CF, IIRC.

If you want to punt this patch out of 9.2 after all the changes I had to
make for it to be a candidate for 9.2, I think it would be only fair for
you to find a show stopper in my current implementation. The trigger
firing order is about an hour of work, so not a stopper I believe.

And as soon as we're done here, you know I'll put the same hours and
energy into reviewing other people patches :)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  38. Re: Command Triggers, v16

    Thom Brown <thombrown@gmail.com> — 2012-03-26T20:41:59Z

    On 26 March 2012 21:36, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Personally, I am about at the point where I'd like to punt everything
>> and move on.  As nice as it would be to squeeze a few more things into
>> 9.2, there WILL be a 9.3.  If a few less people had submitted
>> half-baked code at the last minute and a few more people had helped
>> with review, we'd be done by now.
>
> Well, wait a minute. There's a difference between half-baked and
> reacting to a review that changes the goal of a patch. My idea of the

I think Robert was referring to patches in general.

Thom

  39. Re: Command Triggers, v16

    Robert Haas <robertmhaas@gmail.com> — 2012-03-26T20:45:28Z

    On Mon, Mar 26, 2012 at 4:36 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Well, wait a minute. There's a difference between half-baked and
> reacting to a review that changes the goal of a patch. My idea of the
> code I wanted to write here is extremely different from what we as a
> community decided to be doing. The main part of the code churn has been
> answering to review, removing features and cleaning the code afterwards.

Sure.  And if this weren't the final CommitFest, we would have bumped
this to the next CommitFest two months ago.  Since it is the final
CommitFest, we're going to go on and on and on.  Already we have
almost as many patches queued up for the next CommitFest as we do
remaining in this one.  So my question is: how long should we keep all
those people waiting for the purpose of squeezing more stuff into 9.2?
 At the beginning of this CommitFest, various people offered time
frames ranging between 6 weeks to 2 months.  We're past that now.  If
you don't think that was a long enough time frame, then how long do
you think we should wait?   It doesn't seem to me to matter very much
whether this got stretched out due to fundamental design deficiencies
or just because it takes a while to beat a major feature into
committable form; surely it's not a shock that this patch wasn't going
to go in overnight.

> If you want to punt this patch out of 9.2 after all the changes I had to
> make for it to be a candidate for 9.2, I think it would be only fair for
> you to find a show stopper in my current implementation. The trigger
> firing order is about an hour of work, so not a stopper I believe.

I don't think there is a show-stopper.  I do think there is probably a
lot more cleaning up, tidying, and adjusting needed.

> And as soon as we're done here, you know I'll put the same hours and
> energy into reviewing other people patches :)

As soon as we're done here, the CommitFest will end, and there won't
be any other people's patches to review.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  40. Re: Command Triggers, v16

    Christopher Browne <cbbrowne@gmail.com> — 2012-03-26T21:28:27Z

    On Mon, Mar 26, 2012 at 4:45 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> As soon as we're done here, the CommitFest will end, and there won't
> be any other people's patches to review.

Hurray?  :-)
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

  41. Re: Command Triggers, v16

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-26T21:36:48Z

    Robert Haas <robertmhaas@gmail.com> writes:
> 2. I'm not sure which patches Tom is planning to look at or in what
> order, so I've been avoiding the ones he seems to be taking an
> interest in.

Well, I think I'm definitely on the hook for the pg_stat_statements,
pgsql_fdw, foreign table stats, and caching-stable-subexpressions
patches, and I should look at the libpq alternate row returning
mechanism because I suspect I was the last one to mess with that libpq
code in any detail.  I don't claim any special insight into the other
stuff on the list.  In particular I've not been paying much attention
to command triggers.

> Personally, I am about at the point where I'd like to punt everything
> and move on.  As nice as it would be to squeeze a few more things into
> 9.2, there WILL be a 9.3.  If a few less people had submitted
> half-baked code at the last minute and a few more people had helped
> with review, we'd be done by now.

The main reason I proposed setting a schedule a few weeks ago was that
I was afraid the commitfest would otherwise end precisely in a "we're
tired out, we're punting everything to 9.3" moment.  Without some
definite goal to work towards, it'll just keep stretching out until
we've had enough.  I'd prefer it end in a more orderly fashion than
that.  The end result will be the same, in the sense that some of the
stuff that's still-not-ready-for-committer is going to get punted,
but people might have a less bad taste in their mouths about why.

			regards, tom lane

  42. Re: Command Triggers, v16

    Robert Haas <robertmhaas@gmail.com> — 2012-03-26T22:16:37Z

    On Mar 26, 2012, at 5:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> 2. I'm not sure which patches Tom is planning to look at or in what
>> order, so I've been avoiding the ones he seems to be taking an
>> interest in.
> 
> Well, I think I'm definitely on the hook for the pg_stat_statements,
> pgsql_fdw, foreign table stats, and caching-stable-subexpressions
> patches, and I should look at the libpq alternate row returning
> mechanism because I suspect I was the last one to mess with that libpq
> code in any detail.  I don't claim any special insight into the other
> stuff on the list.  In particular I've not been paying much attention
> to command triggers.

How long will that all take?

I guess I'll work on command triggers, pg_archivecleanup, and buffer I/O timings next.

> 
>> Personally, I am about at the point where I'd like to punt everything
>> and move on.  As nice as it would be to squeeze a few more things into
>> 9.2, there WILL be a 9.3.  If a few less people had submitted
>> half-baked code at the last minute and a few more people had helped
>> with review, we'd be done by now.
> 
> The main reason I proposed setting a schedule a few weeks ago was that
> I was afraid the commitfest would otherwise end precisely in a "we're
> tired out, we're punting everything to 9.3" moment.  Without some
> definite goal to work towards, it'll just keep stretching out until
> we've had enough.  I'd prefer it end in a more orderly fashion than
> that.  The end result will be the same, in the sense that some of the
> stuff that's still-not-ready-for-committer is going to get punted,
> but people might have a less bad taste in their mouths about why.

Fine. What do you propose, specifically?

...Robert

  43. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-26T23:39:30Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Mar 26, 2012, at 5:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> 2. I'm not sure which patches Tom is planning to look at or in what
>>> order, so I've been avoiding the ones he seems to be taking an
>>> interest in.

>> Well, I think I'm definitely on the hook for the pg_stat_statements,
>> pgsql_fdw, foreign table stats, and caching-stable-subexpressions
>> patches, and I should look at the libpq alternate row returning
>> mechanism because I suspect I was the last one to mess with that libpq
>> code in any detail.

> How long will that all take?

Dunno, but surely at least a day apiece if they're to be pushed to
commit.  On the other hand, considering that none of them is actually
Ready For Committer right now, we possibly shouldn't expect that they'll
all get committed.

>>> Personally, I am about at the point where I'd like to punt everything
>>> and move on.  As nice as it would be to squeeze a few more things into
>>> 9.2, there WILL be a 9.3.  If a few less people had submitted
>>> half-baked code at the last minute and a few more people had helped
>>> with review, we'd be done by now.

>> The main reason I proposed setting a schedule a few weeks ago was that
>> I was afraid the commitfest would otherwise end precisely in a "we're
>> tired out, we're punting everything to 9.3" moment.  Without some
>> definite goal to work towards, it'll just keep stretching out until
>> we've had enough.  I'd prefer it end in a more orderly fashion than
>> that.  The end result will be the same, in the sense that some of the
>> stuff that's still-not-ready-for-committer is going to get punted,
>> but people might have a less bad taste in their mouths about why.

> Fine. What do you propose, specifically?

The end of the month is coming up.  How about we propose to close the
'fest on April 1st?  Anything that's not committable by then goes to
the 9.3 list.  If one week seems too short, how about 2 weeks?

Thom Brown <thom@linux.com> writes:
> This is probably a dumb question but... surely there's more than 2
> committers able to review?

Able and willing might be two different things.  Alvaro, Heikki, and
Magnus have all been looking at stuff, but I think they may be getting
burned out too.

			regards, tom lane

  44. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Magnus Hagander <magnus@hagander.net> — 2012-03-27T04:03:39Z

    On Tue, Mar 27, 2012 at 01:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> Thom Brown <thom@linux.com> writes:
>> This is probably a dumb question but... surely there's more than 2
>> committers able to review?
>
> Able and willing might be two different things.  Alvaro, Heikki, and
> Magnus have all been looking at stuff, but I think they may be getting
> burned out too.

Can't honestly claim I've been burned out by it - more that I feel bad
fo rnot having "done my part" :-O

It's not the CF itself, but a whole lot of other things (non-postgres)
that have been taking my time.

I'll try to get my head around the pg_stat_bgwriter patch - which I
htink actually has the wrong status on the CF page after talking to
Greg about it tonight.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

  45. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Simon Riggs <simon@2ndquadrant.com> — 2012-03-27T07:26:46Z

    On Tue, Mar 27, 2012 at 12:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Thom Brown <thom@linux.com> writes:
>> This is probably a dumb question but... surely there's more than 2
>> committers able to review?
>
> Able and willing might be two different things.  Alvaro, Heikki, and
> Magnus have all been looking at stuff, but I think they may be getting
> burned out too.

If people are keeping score, add myself and Robert also, maybe others
- I've not been watching too closely.

On average there appears to be about 10 patches per active committer
in this CF. Given the complexity of the patches in last CF always
seems to be higher, that is a huge number and represents weeks of
work.

One of the key problems I see is that few people actually get paid to
do this, so its fairly hard to allocate time. I want to make it a
policy of "1 for 1" so if you write a patch you need to review a
patch. That way sponsors are forced to spend money on review time for
stuff they may not care about as a trade for getting reviews on stuff
they do. This would take pressure off the few.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  46. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Robert Haas <robertmhaas@gmail.com> — 2012-03-27T17:36:23Z

    On Mon, Mar 26, 2012 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Fine. What do you propose, specifically?
>
> The end of the month is coming up.  How about we propose to close the
> 'fest on April 1st?  Anything that's not committable by then goes to
> the 9.3 list.  If one week seems too short, how about 2 weeks?

Let's split the difference: how about we close it a week from this
Friday.  That would be April 6, 2012, ten days from today.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  47. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Robert Haas <robertmhaas@gmail.com> — 2012-03-28T13:45:29Z

    On Tue, Mar 27, 2012 at 1:36 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Mar 26, 2012 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Fine. What do you propose, specifically?
>>
>> The end of the month is coming up.  How about we propose to close the
>> 'fest on April 1st?  Anything that's not committable by then goes to
>> the 9.3 list.  If one week seems too short, how about 2 weeks?
>
> Let's split the difference: how about we close it a week from this
> Friday.  That would be April 6, 2012, ten days from today.

Anybody, anybody?  Can we try to get some agreement on this?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  48. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Simon Riggs <simon@2ndquadrant.com> — 2012-03-28T13:57:40Z

    On Wed, Mar 28, 2012 at 2:45 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Mar 27, 2012 at 1:36 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Mar 26, 2012 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Fine. What do you propose, specifically?
>>>
>>> The end of the month is coming up.  How about we propose to close the
>>> 'fest on April 1st?  Anything that's not committable by then goes to
>>> the 9.3 list.  If one week seems too short, how about 2 weeks?
>>
>> Let's split the difference: how about we close it a week from this
>> Friday.  That would be April 6, 2012, ten days from today.
>
> Anybody, anybody?  Can we try to get some agreement on this?

I agree.

I have a few projects still on the table myself, but my main concern
is Alvaro's FK locks patch. Depending on how the bones lie I will
finish up some combination of those by end of next week.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  49. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-03-28T14:20:10Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Mar 27, 2012 at 1:36 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> Let's split the difference: how about we close it a week from this
>> Friday.  That would be April 6, 2012, ten days from today.

> Anybody, anybody?  Can we try to get some agreement on this?

Works for me.

			regards, tom lane

  50. Re: 9.2 commitfest closure (was Command Triggers, v16)

    Noah Misch <noah@leadboat.com> — 2012-03-28T14:44:32Z

    On Wed, Mar 28, 2012 at 09:45:29AM -0400, Robert Haas wrote:
> On Tue, Mar 27, 2012 at 1:36 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> > Let's split the difference: how about we close it a week from this
> > Friday. ?That would be April 6, 2012, ten days from today.
> 
> Anybody, anybody?  Can we try to get some agreement on this?

+1