=?iso-8859-1?Q?C=E9dric_Dufour?= <cedric.dufour@freesurf.ch> writes:
> Regarding the trigger problem, it is exactly as I have described it in the
> first place:
> IF ( ( TG_OP = 'INSERT' ) OR ( ( TG_OP = 'UPDATE' ) AND ( old.X !=
> new.X ) ) ) THEN
> -- Do actions depending on field X when inserted or when **changed** (thus
> avoiding useless action if field X didn't change)
> END IF;
> --> Error on **insert**: 'record old is unassigned yet'. Am I wrong assuming
> that even though the ( TG_OP = 'INSERT' ) is true and ( TG_OP = 'UPDATE' )
> is false, ( old.X != new.X ) seems to be evaluated ? ( which causes the
> error )
It wouldn't get evaluated if TG_OP = 'INSERT' ... but plpgsql has to
insert all the parameters of the IF expression before it passes the IF
expression off to the main executor. So you're bombing out at the
parameter-interpretation stage. I think you'll have to divide this into
two plpgsql IF statements.
> FROM
> owner
> INNER JOIN
> folder
> ON ( folder.PK = folder.FK_owner )
Surely that join condition is wrong.
> WHERE
> owner.admin_bool OR
> (
> folder.enabled_bool
> AND ( ( folder.enable_date IS NULL ) OR ( folder.enable_date <=
> CURRENT_TIMESTAMP ) )
> AND ( ( folder.Disable_date IS NULL ) OR ( folder.disable_date >
> CURRENT_TIMESTAMP ) )
> item.enabled_bool
> AND ( ( item.enable_date IS NULL ) OR ( item.enable_date <=
> CURRENT_TIMESTAMP ) )
> AND ( ( item.disable_date IS NULL ) OR ( item.disable_date >
> CURRENT_TIMESTAMP ) )
> )
I'm having a hard time making sense of this, since both your examples
contain the same typo --- I imagine there's an AND or OR before
item.enabled_bool, but it's hard to guess which.
However, I suspect the issue is that the planner tries to flatten the
above WHERE into conjunctive normal form, which is normally a good
optimization strategy but perhaps doesn't work real well on this case.
Still that could only affect the boolean-expression evaluation time,
and it's hard to believe that that's a large fraction of the total
join time.
What does EXPLAIN ANALYZE say about the plans for these queries?
And could we see them in typo-free form?
regards, tom lane