Re: [GENERAL] Determining table change in an event trigger
On 08/24/2016 12:58 AM, Alvaro Herrera wrote:
> Jonathan Rogers wrote:
>> I am trying to use an event trigger to do something when a column
>> changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
>> TABLE')" to get dropped columns. However, I can't figure out any good
>> way to determine when a column has been added or altered.
>>
>> I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
>> TABLE')" but that gets unwanted events such as disabling triggers on a
>> table. Function pg_event_trigger_ddl_commands() returns rows with column
>> "command" of type "pg_ddl_command" which contains "a complete
>> representation of the command, in internal format." According to the
>> docs, this cannot be output directly, but it can be passed to other
>> functions to obtain different pieces of information about the command.
>> However, I cannot find any other functions which operate on the type
>> pg_ddl_command. Am I missing something? Is the documentation lacking?
>
> Yeah, that type can only be processed by C functions. You'd need to
> write a C function to examine the structure and see whether it matches
> what you need.
>
OK, thanks for the explanation. It seems like the docs should make it
clear that the "other functions" are not included.
--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrog...@emphasys-software.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining table change in an event trigger
I am trying to use an event trigger to do something when a column
changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
TABLE')" to get dropped columns. However, I can't figure out any good
way to determine when a column has been added or altered.
I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
TABLE')" but that gets unwanted events such as disabling triggers on a
table. Function pg_event_trigger_ddl_commands() returns rows with column
"command" of type "pg_ddl_command" which contains "a complete
representation of the command, in internal format." According to the
docs, this cannot be output directly, but it can be passed to other
functions to obtain different pieces of information about the command.
However, I cannot find any other functions which operate on the type
pg_ddl_command. Am I missing something? Is the documentation lacking?
--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrog...@emphasys-software.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining table change in an event trigger
I am trying to use an event trigger to do something when a column
changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
TABLE')" to get dropped columns. However, I can't figure out any good
way to determine when a column has been added or altered.
I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
TABLE')" but that gets unwanted events such as disabling triggers on a
table. Function pg_event_trigger_ddl_commands() returns rows with column
"command" of type "pg_ddl_command" which contains "a complete
representation of the command, in internal format." According to the
docs, this cannot be output directly, but it can be passed to other
functions to obtain different pieces of information about the command.
However, I cannot find any other functions which operate on the type
pg_ddl_command. Am I missing something? Is the documentation lacking?
--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrog...@emphasys-software.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Python prepare example's use of setdefault
On 11/01/2014 12:13 PM, Peter Eisentraut wrote: On 10/15/14 5:58 PM, Jonathan Rogers wrote: BTW, I would rewrite the 9.1 example to be shorter while behaving the same: CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ plan = SD.get(plan) if plan is None: If we're going for shortness, how about if not plan: Sure, that's fine as long as a plan object never looks Falsey. ? SD[plan] = plan = plpy.prepare(SELECT 1) and here maybe plan = SD[plan] = plpy.prepare(SELECT 1) to emphasize the assignment to plan? Yeah, order of assignment shouldn't matter. # rest of function $$ LANGUAGE plpythonu; -- Jonathan Ross Rogers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Python prepare example's use of setdefault
I was just reading the PL/Python docs section 42.7.1 Database Access Functions and saw this example: CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ plan = SD.setdefault(plan, plpy.prepare(SELECT 1)) # rest of function $$ LANGUAGE plpythonu; The above example uses the plpy.prepare() function, reusing the result across function calls uses setdefault(). Unfortunately, since setdefault() is a method on dict objects, the values passed to it must be evaluated before it can be called. Therefore, plpy.prepare() will be called every time usesavedplan() executes whether a result already exists in the SD dict or not. I'm not sure if it's a problem that plpy.prepare() is called every time since the result is discarded if a prepared statement had been cached by a previous execution of usesavedplan(). It seems that some wasted processing will occur, but maybe not enough to matter. The documentation for SPI_prepare() does not clearly state what tasks that function performs other than constructing a prepared statement object. It seems to imply that parsing does occur within SPI_prepare(). It does state that query planning occurs within SPI_execute_plan(). Can anyone clarify what occurs when plpy.prepare() is called? Is it worth using a Python conditional to determine whether to call it rather than using SD.setdefault()? -- Jonathan Ross Rogers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Python prepare example's use of setdefault
On 10/15/2014 05:51 PM, Adrian Klaver wrote: On 10/15/2014 02:39 PM, Jonathan Rogers wrote: I was just reading the PL/Python docs section 42.7.1 Database Access Functions and saw this example: CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ plan = SD.setdefault(plan, plpy.prepare(SELECT 1)) # rest of function $$ LANGUAGE plpythonu; The above example uses the plpy.prepare() function, reusing the result across function calls uses setdefault(). Unfortunately, since setdefault() is a method on dict objects, the values passed to it must be evaluated before it can be called. Therefore, plpy.prepare() will be called every time usesavedplan() executes whether a result already exists in the SD dict or not. I'm not sure if it's a problem that plpy.prepare() is called every time since the result is discarded if a prepared statement had been cached by a previous execution of usesavedplan(). It seems that some wasted processing will occur, but maybe not enough to matter. The documentation for SPI_prepare() does not clearly state what tasks that function performs other than constructing a prepared statement object. It seems to imply that parsing does occur within SPI_prepare(). It does state that query planning occurs within SPI_execute_plan(). Can anyone clarify what occurs when plpy.prepare() is called? Is it worth using a Python conditional to determine whether to call it rather than using SD.setdefault()? Like in the older documentation?: http://www.postgresql.org/docs/9.1/static/plpython-database.html CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ if SD.has_key(plan): plan = SD[plan] else: plan = plpy.prepare(SELECT 1) SD[plan] = plan # rest of function $$ LANGUAGE plpythonu; Exactly. It seems to me that the approach taken by the newer documentation will be less efficient. If so, why was the example changed? BTW, I would rewrite the 9.1 example to be shorter while behaving the same: CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ plan = SD.get(plan) if plan is None: SD[plan] = plan = plpy.prepare(SELECT 1) # rest of function $$ LANGUAGE plpythonu; -- Jonathan Ross Rogers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
