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

2011-07-12 Thread Florian Pflug
On Jul11, 2011, at 21:49 , David Johnston wrote: 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

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

2011-07-12 Thread Alban Hertroys
On 12 Jul 2011, at 3:25, Chris Travers wrote: 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

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

2011-07-11 Thread Darren Duncan
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

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

2011-07-11 Thread David Johnston
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

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

2011-07-11 Thread Florian Pflug
On Jul8, 2011, at 08:21 , Darren Duncan wrote: Also, the proper way to do temporary tables would be to put them in another database than the main one, where the whole other database has the property of being temporary. FWIW, Microsoft SQL Server does it that way, and as a result temporary

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

2011-07-11 Thread Florian Pflug
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,

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

2011-07-11 Thread Christopher Browne
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org 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

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

2011-07-11 Thread David Johnston
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org 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

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

2011-07-11 Thread Darren Duncan
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 f...@phlo.org wrote: On Jul11, 2011, at 07:08 ,

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

2011-07-11 Thread Chris Travers
On Mon, Jul 11, 2011 at 12:49 PM, David Johnston pol...@yahoo.com wrote: I do not see how recursive queries (really iteration of records) even enters the picture... I agree, FWIW. If the feature was that desirable, we could look at questions of implementation to make recursion either

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

2011-07-10 Thread mike beeper
: [HACKERS] [GENERAL] Creating temp tables inside read only transactions On 08/07/11 18:21, Darren Duncan wrote: Jeff Davis wrote: On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably

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

2011-07-10 Thread Gavin Flower
On 08/07/11 18:21, Darren Duncan wrote: Jeff Davis wrote: On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a

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

2011-07-10 Thread Michael Nolan
On Fri, Jul 8, 2011 at 10:27 PM, Robert Haas robertmh...@gmail.com wrote: But if that's what you want, just don't put your data in different databases in the first place. That's what schemas are for. Sadly, DBAs don't always have the ability to put all their data in one database, even if

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

2011-07-10 Thread Christopher Browne
On Mon, Jul 11, 2011 at 12:01 AM, Michael Nolan htf...@gmail.com wrote: On Fri, Jul 8, 2011 at 10:27 PM, Robert Haas robertmh...@gmail.com wrote: But if that's what you want, just don't put your data in different databases in the first place.  That's what schemas are for. Sadly, DBAs don't

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

2011-07-09 Thread Jeff Davis
On Fri, 2011-07-08 at 12:34 -0700, Darren Duncan wrote: Yes, but that would just be in-memory or in temporary places external to every database. On disk internal to a database there would just be the oid. In fact, another aspect of the database model I defined is that each database is

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

2011-07-09 Thread Darren Duncan
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: If for some reason we needed to have tables that happened to be called x.y.z and a.b.c accessible from a single SQL session, we could allow that much more simply by allowing schemas to be nested. Then we could allow arbitrary numbers of

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

2011-07-09 Thread Jeff Davis
On Fri, 2011-07-08 at 21:04 -0700, Darren Duncan wrote: I think you should make more of an effort to understand how the system works now, and why, before proposing radical redesigns. Well yes, of course. But that will take time and I think I already understand enough about it to make

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

2011-07-09 Thread Jeff Davis
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

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

2011-07-09 Thread Darren Duncan
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

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

2011-07-08 Thread Darren Duncan
Jeff Davis wrote: On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read-only transaction. Hence the error. And

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

2011-07-08 Thread Jeff Davis
On Thu, 2011-07-07 at 23:21 -0700, Darren Duncan wrote: I think an even better way to support this is would be based on Postgres having support for directly using multiple databases within the same SQL session at once, as if namespaces were another level deep, the first level being the

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

2011-07-08 Thread Darren Duncan
Jeff Davis wrote: On Thu, 2011-07-07 at 23:21 -0700, Darren Duncan wrote: I think an even better way to support this is would be based on Postgres having support for directly using multiple databases within the same SQL session at once, as if namespaces were another level deep, the first level

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

2011-07-08 Thread Robert Haas
On Fri, Jul 8, 2011 at 2:21 AM, Darren Duncan dar...@darrenduncan.net wrote: I think an even better way to support this is would be based on Postgres having support for directly using multiple databases within the same SQL session at once, as if namespaces were another level deep, the first

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

2011-07-08 Thread Darren Duncan
Robert Haas wrote: But if that's what you want, just don't put your data in different databases in the first place. That's what schemas are for. If for some reason we needed to have tables that happened to be called x.y.z and a.b.c accessible from a single SQL session, we could allow that much

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

2011-07-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: If for some reason we needed to have tables that happened to be called x.y.z and a.b.c accessible from a single SQL session, we could allow that much more simply by allowing schemas to be nested. Then we could allow arbitrary numbers of levels, not

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

2011-07-07 Thread Darren Duncan
Guillaume Lelarge wrote [on pgsql-general]: On Thu, 2011-07-07 at 16:01 +, mike beeper wrote [on pgsql-general]: I have a function that creates a temp table, populate it with results during intermediate processing, and reads from it at the end. When the transaction is marked as read only,

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

2011-07-07 Thread Jeff Davis
On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read-only transaction. Hence the error. And no, there is no