On Mon, 6 Nov 2023 at 15:54, Tom Lane <t...@sss.pgh.pa.us> wrote: > Isaac Morland <isaac.morl...@gmail.com> writes: > > I still think the right default is that CREATE FUNCTION stores the > > search_path in effect when it runs with the function, and that is the > > search_path used to run the function (and don't "BEGIN ATOMIC" functions > > partially work this way already?). > > I don't see how that would possibly fly. Yeah, that behavior is > often what you want, but not always; we would break some peoples' > applications with that rule. >
The behaviour I want is just “SET search_path FROM CURRENT". I agree there is a backward compatibility issue; if somebody has a schema creation/update script with function definitions with no "SET search_path" they would suddenly start getting the search_path from definition time rather than the caller's search_path. I don't like adding GUCs but a single one specifying whether no search_path specification means "FROM CURRENT" or the current behaviour (new explicit syntax "FROM CALLER"?) would I think address the backward compatibility issue. This would allow a script to specify at the top which convention it is using; a typical old script could be adapted to a new database by adding a single line at the top to get the old behaviour. Also, one place where it's clearly NOT what you want is while > restoring a pg_dump script. And we don't have any way that we could > bootstrap ourselves out of breaking everything for everybody during > their next upgrade --- even if you insist that people use a newer > pg_dump, where is it going to find the info in an existing database? > A function with a stored search_path will have a "SET search_path" clause in the pg_dump output, so for these functions pg_dump would be unaffected by my preferred way of doing things. Already I don't believe pg_dump ever puts "SET search_path FROM CURRENT" in its output; it puts the actual search_path. A bigger problem is with existing functions that use the caller's search_path; these would need to specify "FROM CALLER" explicitly; but the new GUC could come into this. In effect a pg_dump created by an old version is an old script which would need the appropriate setting at the top. But all this is premature if there is still disagreement on the proper default behaviour. To me it is absolutely clear that the right default, in the absence of an installed base with backward compatibility concerns, is "SET search_path FROM CURRENT". This is how substantially all programming languages work: it is quite unusual in modern programming languages to have the meaning of a procedure definition depend on which modules the caller has imported. The tricky bit is dealing smoothly with the installed base. But some of the discussion here makes me think that people have a different attitude about stored procedures.
