Re: check_function_bodies not doing much

I'll take a look at it. Thanks for the recommendation. On Tue, Aug 7, 2018 at 7:22 PM Pavel Stehule wrote: > > > 2018-08-08 0:02 GMT+02:00 Marcelo Lacerda : > >> That's a whole different nightmare that I'm expecting. >> > > > >> >> "Yep I double-checked all my functions to see if any would break

Re: check_function_bodies not doing much

2018-08-08 0:02 GMT+02:00 Marcelo Lacerda : > That's a whole different nightmare that I'm expecting. > > > "Yep I double-checked all my functions to see if any would break if I > change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and > everything is ok." > > *1 month later* > >

Re: check_function_bodies not doing much

That's a whole different nightmare that I'm expecting. "Yep I double-checked all my functions to see if any would break if I change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and everything is ok." *1 month later* "Why is this error log for this application that I wrote one yea

Re: check_function_bodies not doing much

On Tue, Aug 7, 2018 at 2:31 PM Tom Lane wrote: > > Marcelo Lacerda writes: > > I was trying to get postgres to warn me that I'm referencing a table that > > it doesn't exists inside a function so I was told on the IRC to check the > > setting "check_function_bodies", however when I use it in a pl

Re: check_function_bodies not doing much

"David G. Johnston" writes: > On Tue, Aug 7, 2018 at 12:31 PM, Tom Lane wrote: >> Yes. It's supposed to be a syntax check, not a check that the function >> would work when executed. (Depending on the particular PL you're using, >> which you didn't mention, it might be a pretty weak syntax check

Re: check_function_bodies not doing much

On Tue, Aug 7, 2018 at 12:31 PM, Tom Lane wrote: > Marcelo Lacerda writes: > > I was trying to get postgres to warn me that I'm referencing a table that > > it doesn't exists inside a function so I was told on the IRC to check the > > setting "check_function_bodies", however when I use it in a p

Re: check_function_bodies not doing much

Marcelo Lacerda writes: > I was trying to get postgres to warn me that I'm referencing a table that > it doesn't exists inside a function so I was told on the IRC to check the > setting "check_function_bodies", however when I use it in a plpgsql > function it doesn't actually check if the tables i

Re: check_function_bodies not doing much

Hi 2018-08-07 21:17 GMT+02:00 Marcelo Lacerda : > I was trying to get postgres to warn me that I'm referencing a table that > it doesn't exists inside a function so I was told on the IRC to check the > setting "check_function_bodies", however when I use it in a plpgsql > function it doesn't actua

Re: Instead trigger on a view to update base tables ?

On Tue, Aug 7, 2018 at 12:09 PM, Day, David wrote: > EXECUTE format(' UPDATE admin.user SET (%I) = ( SELECT %I FROM $1 ) WHERE > id = $2)', USER_SETTING, USER_SETTING ) > USING NEW, NEW.id; > > When this executes my exception handler generates "err syntax error at or > near

check_function_bodies not doing much

I was trying to get postgres to warn me that I'm referencing a table that it doesn't exists inside a function so I was told on the IRC to check the setting "check_function_bodies", however when I use it in a plpgsql function it doesn't actually check if the tables in the body exist. Is this the cor

Instead trigger on a view to update base tables ?

In a view of three joined tables, I install a INSTEAD OF trigger fx on the view. The fx contains a list of felds/columns variable associated to each base tables. When an update operation occurs, I am successfully generating the target list of colums altered on Each base table. ( comparin

Re: Immutable function WAY slower than Stable function?

Ken Tanzer writes: > I've wondered in the past about Immutable functions, and particularly the > scope & lifetime of "forever" in "guaranteed to return the same results > given the same arguments forever." I assume that's "for all users and all > sessions," but either in theory or in practice is

Re: Immutable function WAY slower than Stable function?

> On Aug 7, 2018, at 11:42, Ken Tanzer wrote: > I assume that's "for all users and all sessions," but either in theory or in > practice is there a limit to how long a stale value might persist? And, if > you were to drop and recreate a function with the same name & parameters, > would it sta

Re: Immutable function WAY slower than Stable function?

On Tue, Aug 7, 2018 at 9:10 AM Tom Lane wrote: > I was confused about that too, so I set up a simple test case similar > to Ken's and poked into it a bit, and what I found out is that nested > SQL functions are just about completely broken performance-wise, > unless one or the other gets inlined

Re: Immutable function WAY slower than Stable function?

On Aug 7, 2018, at 9:10 , Tom Lane wrote: > > I've had a to-do item to rewrite and improve the SQL function cache > mechanism for a long time, but I hadn't thought it was high priority. > Maybe it should be. Personally, I would love this. Far too few developers dig into doing things in the data

Re: Immutable function WAY slower than Stable function?

Ken Tanzer writes: > On Mon, Aug 6, 2018 at 7:42 PM Tom Lane wrote: >> The reason that EXPLAIN VERBOSE >> is helpful here is that you can see whether the function got inlined >> or not: do you see a call to the function, or a representation of >> its body? > There is the difference in the two ou

Re: Immutable function WAY slower than Stable function?

On 08/07/2018 12:38 AM, Ken Tanzer wrote: On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer > wrote: Whoops, scratch that previous explain and query.  I accidentally left in a hard-coded client_id from earlier testing.  The correct query is: EXPLAIN (VERBOSE,ANALYZE,B

Re: Immutable function WAY slower than Stable function?

Ken Tanzer wrote: > > Buth functions don't get inlined. > > OK, I clearly don't understand the output lines. What does it mean then that > the stable > output line shows the underlying (two-argument) function, while the immutable > one shows > the convenience function? > > Output: client_id,

Re: Immutable function WAY slower than Stable function?

On Tue, Aug 7, 2018 at 12:05 AM Ken Tanzer wrote: > I'm definitely not understanding why or how auto-explain would help here. > (Also, don't overlook the fact that both si_stable and si_imm have the > exact same definition (except for stable v. immutable), and are calling the > same function (sta

Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 11:05 PM Laurenz Albe wrote: > Ken Tanzer wrote: > > On Mon, Aug 6, 2018 at 4:11 PM Tom Lane wrote: > > > Ken Tanzer writes: > > > > spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT > client_id,si_imm(client_id) FROM tbl_residence_own; > >