Re: [GENERAL] interesting trigger behaviour in 8.3
On Tue, Jul 29, 2008 at 7:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Ivan Zolotukhin" <[EMAIL PROTECTED]> writes: >> In pseudo code it looks like the following. There are 2 tables, empty >> abstract_table with 3 columns (id, col1, col2) and many tables (e.g. >> inherited_table1_with_data) that inherit abstract_table. >> Constraint_exclusion is set up on id column and works perfectly. So >> we've got update like this > >> UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345; > > I bet it does not *really* look like that, but has a parameterized > WHERE clause. As per the fine manual: > >Constraint exclusion only works when the query's WHERE clause >contains constants. A parameterized query will not be optimized, >since the planner cannot know which partitions the parameter value >might select at run time. For the same reason, "stable" functions >such as CURRENT_DATE must be avoided. Thank you Tom for your remark. I just missed this point from the docs. -- Regards, Ivan -- 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] interesting trigger behaviour in 8.3
"Ivan Zolotukhin" <[EMAIL PROTECTED]> writes: > In pseudo code it looks like the following. There are 2 tables, empty > abstract_table with 3 columns (id, col1, col2) and many tables (e.g. > inherited_table1_with_data) that inherit abstract_table. > Constraint_exclusion is set up on id column and works perfectly. So > we've got update like this > UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345; I bet it does not *really* look like that, but has a parameterized WHERE clause. As per the fine manual: Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, "stable" functions such as CURRENT_DATE must be avoided. regards, tom lane -- 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] interesting trigger behaviour in 8.3
On Tue, 2008-07-29 at 19:25 +0400, Ivan Zolotukhin wrote: > Any clues? Can anybody suggest how to debug this? Is it possible to > get an explain of the query within the trigger? I bet it's the difference between prepared/not prepared plans. The trigger prepares the plan without considering the actual parameter values, on the psql prompt you give the parameter values explicitly in the sql. Try to use the PREPARE command to prepare the plan on the psql prompt, and EXPLAIN EXECUTE it to see how it works in the trigger... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
