Re: [HACKERS] Implementation of global temporary tables?

2015-07-22 Thread Robert Haas
On Wed, Jul 15, 2015 at 11:52 AM, Simon Riggs si...@2ndquadrant.com wrote: On 15 July 2015 at 16:44, Andres Freund and...@anarazel.de wrote: On 2015-07-15 16:36:12 +0100, Simon Riggs wrote: On 15 July 2015 at 16:28, Andres Freund and...@anarazel.de wrote: I think that's generally a fair

Re: [HACKERS] Implementation of global temporary tables?

2015-07-20 Thread Andres Freund
On 2015-07-20 15:33:32 +1200, Gavin Flower wrote: Would it be difficult to add the ability for one user to share the contents with a list of named other users (roles)? No need. That feature is called unlogged tables and grants. Doing this for temporary tables would be horrible. They live in

Re: [HACKERS] Implementation of global temporary tables?

2015-07-20 Thread Alvaro Herrera
Pavel Stehule wrote: 2015-07-20 5:33 GMT+02:00 Gavin Flower gavinflo...@archidevsys.co.nz: Would it be difficult to add the ability for one user to share the contents with a list of named other users (roles)? Probably it is possible, but not for temporary data - short data are in

Re: [HACKERS] Implementation of global temporary tables?

2015-07-20 Thread Pavel Stehule
2015-07-20 11:07 GMT+02:00 Alvaro Herrera alvhe...@2ndquadrant.com: Pavel Stehule wrote: 2015-07-20 5:33 GMT+02:00 Gavin Flower gavinflo...@archidevsys.co.nz: Would it be difficult to add the ability for one user to share the contents with a list of named other users (roles)?

Re: [HACKERS] Implementation of global temporary tables?

2015-07-20 Thread Zhaomo Yang
Just to be clear, the idea of a global temp table is that the table def is available to all users, but the data is private to each session? The table def is visible to all sessions and persistent, but the data is private to each session and temporary. Thanks, Zhaomo

Re: [HACKERS] Implementation of global temporary tables?

2015-07-19 Thread Josh Berkus
Pavel, All: Just to be clear, the idea of a global temp table is that the table def is available to all users, but the data is private to each session? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] Implementation of global temporary tables?

2015-07-19 Thread Pavel Stehule
2015-07-19 21:39 GMT+02:00 Josh Berkus j...@agliodbs.com: Pavel, All: Just to be clear, the idea of a global temp table is that the table def is available to all users, but the data is private to each session? yes. Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com

Re: [HACKERS] Implementation of global temporary tables?

2015-07-19 Thread Gavin Flower
On 20/07/15 15:00, Pavel Stehule wrote: 2015-07-19 21:39 GMT+02:00 Josh Berkus j...@agliodbs.com mailto:j...@agliodbs.com: Pavel, All: Just to be clear, the idea of a global temp table is that the table def is available to all users, but the data is private to each session?

Re: [HACKERS] Implementation of global temporary tables?

2015-07-19 Thread Pavel Stehule
2015-07-20 5:33 GMT+02:00 Gavin Flower gavinflo...@archidevsys.co.nz: On 20/07/15 15:00, Pavel Stehule wrote: 2015-07-19 21:39 GMT+02:00 Josh Berkus j...@agliodbs.com mailto: j...@agliodbs.com: Pavel, All: Just to be clear, the idea of a global temp table is that the table

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Andrew Dunstan
On 07/15/2015 07:58 AM, Simon Riggs wrote: For me the design summary is this * CREATE GLOBAL TEMP TABLE creates catalog entries like a normal table but with different relkind * When we see a request to INSERT, DEL, UPD, SEL from the temp table, if it does not exist we create it as a TEMP

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Simon Riggs
On 14 July 2015 at 23:20, Jim Nasby jim.na...@bluetreble.com wrote: On 7/9/15 12:45 AM, Pavel Stehule wrote: 2015-07-09 7:32 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu mailto:zhy...@cs.ucsd.edu: I am not sure, if it is not useless work. I don't understand why an implementation

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Pavel Stehule
2015-07-15 13:58 GMT+02:00 Simon Riggs si...@2ndquadrant.com: On 14 July 2015 at 23:20, Jim Nasby jim.na...@bluetreble.com wrote: On 7/9/15 12:45 AM, Pavel Stehule wrote: 2015-07-09 7:32 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu mailto:zhy...@cs.ucsd.edu: I am not sure, if it is not

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Simon Riggs
On 15 July 2015 at 13:26, Andrew Dunstan and...@dunslane.net wrote: On 07/15/2015 07:58 AM, Simon Riggs wrote: For me the design summary is this * CREATE GLOBAL TEMP TABLE creates catalog entries like a normal table but with different relkind * When we see a request to INSERT, DEL, UPD,

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Pavel Stehule
2015-07-15 15:53 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu: there is other question - what is effect of ALTER TABLE of global temp table on instances of this table in active sessions? As I said, we need to first agree on the behaviors of the existing commands. I can think of two options

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Simon Riggs
On 15 July 2015 at 16:44, Andres Freund and...@anarazel.de wrote: On 2015-07-15 16:36:12 +0100, Simon Riggs wrote: On 15 July 2015 at 16:28, Andres Freund and...@anarazel.de wrote: I think that's generally a fair point. But here we're discussing to add a fair amount of wrinkles with the

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Pavel Stehule
2015-07-15 15:21 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu: Sounds fine in general. I'm a bit curious to know what are the locking implications of vivifying the table on access. The locking implications depend on how we interpret the existing commands in the context of global temp tables

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Zhaomo Yang
there is other question - what is effect of ALTER TABLE of global temp table on instances of this table in active sessions? As I said, we need to first agree on the behaviors of the existing commands. I can think of two options now for ALTER TABLE: 1) only allow ALTER TABLE when there is no

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Simon Riggs
On 15 July 2015 at 15:57, Andres Freund and...@anarazel.de wrote: On 2015-07-15 16:52:49 +0200, Andres Freund wrote: Why do we need to create that copy? We can just use the relfilenode in all backends by having the backendid in the filename? Yes, there's a some amount of additional code

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Zhaomo Yang
Sounds fine in general. I'm a bit curious to know what are the locking implications of vivifying the table on access. The locking implications depend on how we interpret the existing commands in the context of global temp tables and I think we should discuss and agree on the behaviors of the

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Andres Freund
On 2015-07-15 16:24:52 +0100, Simon Riggs wrote: It may be possible to do this, though I'm sure there's a wrinkle somewhere. But there doesn't seem to be a need to overload the main feature request with additional requirements. Doing that is just scope creep that prevents us getting features

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Simon Riggs
On 15 July 2015 at 16:28, Andres Freund and...@anarazel.de wrote: On 2015-07-15 16:24:52 +0100, Simon Riggs wrote: It may be possible to do this, though I'm sure there's a wrinkle somewhere. But there doesn't seem to be a need to overload the main feature request with additional

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Tom Lane
Andres Freund and...@anarazel.de writes: On 2015-07-15 16:24:52 +0100, Simon Riggs wrote: It may be possible to do this, though I'm sure there's a wrinkle somewhere. But there doesn't seem to be a need to overload the main feature request with additional requirements. Doing that is just scope

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On 15 July 2015 at 16:28, Andres Freund and...@anarazel.de wrote: I think that's generally a fair point. But here we're discussing to add a fair amount of wrinkles with the copy approach. The fact alone that the oid is different will have some ugly

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Andres Freund
On 2015-07-15 16:36:12 +0100, Simon Riggs wrote: On 15 July 2015 at 16:28, Andres Freund and...@anarazel.de wrote: I think that's generally a fair point. But here we're discussing to add a fair amount of wrinkles with the copy approach. The fact alone that the oid is different will have

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Andres Freund
On 2015-07-15 12:58:51 +0100, Simon Riggs wrote: On 14 July 2015 at 23:20, Jim Nasby jim.na...@bluetreble.com wrote: Pavel's original description of how to do this seem valid, and from the link Tom agreed in 2009. For me the design summary is this * CREATE GLOBAL TEMP TABLE creates

Re: [HACKERS] Implementation of global temporary tables?

2015-07-15 Thread Andres Freund
On 2015-07-15 16:52:49 +0200, Andres Freund wrote: Why do we need to create that copy? We can just use the relfilenode in all backends by having the backendid in the filename? Yes, there's a some amount of additional code needed, but it's not that much? I actually think it might end up being

Re: [HACKERS] Implementation of global temporary tables?

2015-07-14 Thread Jim Nasby
On 7/9/15 12:45 AM, Pavel Stehule wrote: 2015-07-09 7:32 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu mailto:zhy...@cs.ucsd.edu: I am not sure, if it is not useless work. I don't understand why an implementation taking approach 2.a would be useless. As I said, its performance will

Re: [HACKERS] Implementation of global temporary tables?

2015-07-09 Thread Zhaomo Yang
I am not sure, if it is not useless work. I don't understand why an implementation taking approach 2.a would be useless. As I said, its performance will be no worse than current temp tables and it will provide a lot of convenience to users who need to create temp tables in every session.

Re: [HACKERS] Implementation of global temporary tables?

2015-07-08 Thread Pavel Stehule
Hi 2015-07-08 9:08 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu: more global temp tables are little bit comfortable for developers, I'd like to emphasize this point. This feature does much more than saving a developer from issuing a CREATE TEMP TABLE statement in every session. Here are two

Re: [HACKERS] Implementation of global temporary tables?

2015-07-08 Thread Zhaomo Yang
more global temp tables are little bit comfortable for developers, I'd like to emphasize this point. This feature does much more than saving a developer from issuing a CREATE TEMP TABLE statement in every session. Here are two common use cases and I'm sure there are more. (1) Imagine in a web

Re: [HACKERS] Implementation of global temporary tables?

2015-07-08 Thread Pavel Stehule
2015-07-09 7:32 GMT+02:00 Zhaomo Yang zhy...@cs.ucsd.edu: I am not sure, if it is not useless work. I don't understand why an implementation taking approach 2.a would be useless. As I said, its performance will be no worse than current temp tables and it will provide a lot of convenience

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Atri Sharma
Some was changed from 2009: * We have updatable CTE * We have unlogged tables Opened questions: 1. Name and conformance with standard of this feature - because we doesn't support modules, a mapping ANSI - PG should not be trivial 2. Implementation I see three possible ways how to

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 11:51 GMT+01:00 Atri Sharma atri.j...@gmail.com: Some was changed from 2009: * We have updatable CTE * We have unlogged tables Opened questions: 1. Name and conformance with standard of this feature - because we doesn't support modules, a mapping ANSI - PG should not be

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Atri Sharma
1. Main catalogue will be stable. 2. There is not necessary to implement new storage and it can helps with transaction support. The amount of complexity that'd be involved to store catalog data in a separate relation around the caches and accesses would be significant. I don't think

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 13:36 GMT+01:00 Atri Sharma atri.j...@gmail.com: 1. Main catalogue will be stable. 2. There is not necessary to implement new storage and it can helps with transaction support. The amount of complexity that'd be involved to store catalog data in a separate relation around the

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Atri Sharma
On Mon, Feb 2, 2015 at 6:34 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2015-02-02 13:36 GMT+01:00 Atri Sharma atri.j...@gmail.com: 1. Main catalogue will be stable. 2. There is not necessary to implement new storage and it can helps with transaction support. The amount of

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 13:15 GMT+01:00 Andres Freund and...@2ndquadrant.com: On 2015-02-02 12:24:44 +0100, Pavel Stehule wrote: 2015-02-02 12:04 GMT+01:00 Andres Freund and...@2ndquadrant.com: 2.b - using unlogged tables for holding statistics, relfilenode, and all necessary data I can't

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 12:04 GMT+01:00 Andres Freund and...@2ndquadrant.com: Hi, On 2015-02-02 11:15:22 +0100, Pavel Stehule wrote: Six years ago we did discuss about global temporary tables - persistent schema, ephemeral data. http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Andres Freund
On 2015-02-02 12:24:44 +0100, Pavel Stehule wrote: 2015-02-02 12:04 GMT+01:00 Andres Freund and...@2ndquadrant.com: 2.b - using unlogged tables for holding statistics, relfilenode, and all necessary data I can't follow why that'd achieve anything? 1. Main catalogue will be stable.

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 11:15 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com: Hello Six years ago we did discuss about global temporary tables - persistent schema, ephemeral data. http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html I am thinking so some reasons why implement this

Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Andres Freund
Hi, On 2015-02-02 11:15:22 +0100, Pavel Stehule wrote: Six years ago we did discuss about global temporary tables - persistent schema, ephemeral data. http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html I am thinking so some reasons why implement this feature are valid: