Re: [HACKERS] Creating temp tables inside read only transactions

Jeff Davis wrote:
> On Fri, 2011-07-08 at 23:39 -0700, Darren Duncan wrote:
>> What if you used the context of the calling code and resolve in favor of
>> whatever match is closest to it? The problem is related to general-purpose
>> programming languages.
>>
>> Basically start looking in the lexical context for an "x" and if you find one
>> use that; otherwise, assuming we're talking about referencing code that lives in
>> the database such as a function, look at the innermost schema containing the
>> referencing code and see if it has a direct child named "x"; otherwise go up one
>> level to a parent schema, and so on until you get to the top, and finding none
>> by then say it doesn't exist.
>
> This is an example of where data languages and normal programming
> languages have a crucial difference.
>
> With a data language, you have this problem:
> 1. An application uses a query referencing 'y.z.foo' that resolves to
> internal object with fully-qualified name 'x.y.z'.
> 2. An administrator creates object 'y.z.foo'.
>
> Now, the application breaks all of a sudden.
>
> In a normal prgramming language, if the schema of the two "foo"s are
> different, the compiler could probably catch the error. SQL really has
> no hope of catching it though.
>
> PostgreSQL has this problem now in a couple ways, but it's much easier
> to grasp what you might be conflicting with. If you have multiple nested
> levels to traverse and different queries using different levels of
> qualification, it gets a little more messy and I think a mistake is more
> likely.

Well, my search path suggestion was based on Tom Lane's comment that "the SQL
spec requires us to be able to [support abbreviations]" and I expected it would
be syntactically and semantically backwards compatible with how things work now.

FYI, with Muldis D, being more green fields, there are no search paths in the
general case, and every entity reference is unambiguous because it has to be
fully-qualified.

However, I also support relative references, and in fact require their use for
references within the same database, which carries a number of benefits, at the
cost of being a few characters more verbose than when using a search path. So
introducing new things with the same names in different namespaces won't break
anything there, even if they are "closer". Its essentially like navigating a
Unix filesystem but with "." rather than "/".

So for example, if you had 2 sibling schemas "s1" and "s2", each with 2
functions "f1","f2" and a table "t", then s1.f1 would reference s1.f2 and s1.t
as sch.lib.f2 and sch.data.t respectively, while s1.f1 would refer to the
entities in s2 as sch.par.s2.lib.f1 and sch.par.s2.data.t and such (a function
can also refer to itself anonymously as "rtn" if it's recursive). The "sch" is
like "." in Unix and the "par" is like ".." in Unix. The "data" is for data
tables or views (and "cat" is for catalog tables/views) while "lib" is for
user-defined types, routines, constraints, etc (and "sys" is for built-in types
and routines, but "sys" may be omitted and search paths exist just for
built-ins). Synonyms are also supported.

I don't expect you would adopt relative (fully-qualified) references, because
the syntax isn't in standard SQL (I think), but I did. Unless you like them and
can come up with a syntax that will fit into how SQL does things.

-- Darren Duncan