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

From: Darren Duncan
Subject: Re: [HACKERS] Creating temp tables inside read only transactions
Date: ,
Msg-id: 4E1B6147.40900@darrenduncan.net
(view: Whole thread, Raw)
In response to: Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston")
List: pgsql-general

Tree view

Creating temp tables inside read only transactions  (mike beeper, )
 Re: Creating temp tables inside read only transactions  (Guillaume Lelarge, )
  Re: Creating temp tables inside read only transactions  (Darren Duncan, )
   Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
    Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Gavin Flower, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Robert Haas, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Tom Lane, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Craig Ringer, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
         Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Craig Ringer, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Michael Nolan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (John R Pierce, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Christopher Browne, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
         Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston", )
         Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
          Re: [HACKERS] Creating temp tables inside read only transactions  (Christopher Browne, )
           Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston", )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Chris Travers, )
             Re: [HACKERS] Creating temp tables inside read only transactions  (Alban Hertroys, )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
 Re: [HACKERS] Creating temp tables inside read only transactions  (mike beeper, )

I will put my support for David Johnston's proposal, in principle, though minor
details of syntax could be changed if using "!" conflicts with something. --
Darren Duncan

David Johnston wrote:
> On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug <> wrote:
>> On Jul11, 2011, at 07:08 , Darren Duncan wrote:
>>> Christopher Browne wrote:
>>>> Vis-a-vis the attempt to do nested naming, that is "ns1.ns2.table1",
>>>> there's a pretty good reason NOT to support that, namely that this
>>>> breaks relational handling of tables.  PostgreSQL is a *relational*
>>>> database system, hence it's preferable for structures to be
>>>> relational, as opposed to hierarchical, which is what any of the
>>>> suggested nestings are.
>
>>> Rather, the argument is that "it was intentional for the structuring of
> table naming to, itself, be relational," and changing that definitely has
> some undesirable characteristics.
>
>>> The need for recursive queries is the most obvious "undesirable", but it's
> not the only undesirable thing, by any means.
>
> I do not see how recursive queries (really iteration of records) even enters
> the picture...
>
> Right now I can emulate a hierarchical schema structure via a naming scheme
> - for example  "schemabase_sub1_sub2_etc".  I am simply looking for a formal
> way to do the above AND also tell the system that I want all schemas under
> "schemabase" to be in the search path.  Heck, I guess just allowing for
> simply pattern matching in "search_path" would be useful in this case
> regardless of the presence of an actual schema hierarchy.  Using "LIKE"
> syntax say: "SET search_path TO schemabase_sub1_%" or something similar.
> The only missing ability becomes a way for graphical tools to represent the
> schema "hierarchy" using a tree-structure with multiple depths.
>
> I can see how adding "." and ".." and relative paths would confuse the issue
> those are not necessary features of a multi-level schema depth.
>
> The above, combined with a different separator for intra-level
> namespace/schema delineation, would allow for an unambiguous way to define
> and use a hierarchical schema with seemingly minimal invasion into the
> current way of doing things. You could almost implement it just by requiring
> a specific character to act as the separator and then construct the actual
> schema using single-level literals and supporting functions that can convert
> them into an hierarchy.  In other words, the schema table would still only
> contain one field with the full "parent!child" as opposed to (schema,
> parent) with (VALUES('parent',null),('child','parent')).
>
> In other words, if we use "!" as the separator, any schema named
> "parent!child"  could be stored and referenced as such but then if you run a
> "getChildren(parent)" function it would return "child" along with any other
> schemas of the form "parent!%".  In this case the "%" sign could maybe only
> match everything except "!" and the "*" symbol could be used to match "!" as
> well.
>
> I could give more examples but I hope the basic idea is obvious.  The main
> thing is that the namespace hierarchy usage is standardized in such a way
> that pgAdmin and other GUI tools can reliably use for display purposes and
> that "search_path" can be constructed in a more compact format so that every
> schema and sub-schema is still absolutely referenced (you can even have the
> SET command resolve search_path at execution time and then remain static
> just like "CREATE VIEW SELECT * FROM table".
>
> David J.
>
>
>
>



pgsql-general by date:

From: Jeff Davis
Date:
Subject: Re: Accidentally truncated pg_type
From: Tim Uckun
Date:
Subject: Unexpected results with joins on dates