Thread: Creation of temporary tables on read-only standby servers

Creation of temporary tables on read-only standby servers

From
Bruce Momjian
Date:
Currently it isn't possible to create temporary tables on read-only
standby servers, and I don't see it listed on the TODO list.  Can I add
it?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Creation of temporary tables on read-only standby servers

From
Cédric Villemain
Date:
2010/10/18 Bruce Momjian <bruce@momjian.us>:
> Currently it isn't possible to create temporary tables on read-only
> standby servers, and I don't see it listed on the TODO list.  Can I add
> it?

Yes, still probably something we partially visit with global temp
table thread from Robert Haas...

Having non-WALed global temporay tables on the slave part of a SR
replication ...I like that.

>
> --
>  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + It's impossible for everything to be true. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Currently it isn't possible to create temporary tables on read-only
> standby servers, and I don't see it listed on the TODO list.  Can I add
> it?

Not unless you have some credible concept for how it might ever be
implemented.  You can't create temp tables because you can't modify
system catalogs, and if you did somehow create them you couldn't put
anything in them because you can't generate XIDs on a slave ... much
less commit them.  We have talked about ways that temp tables might be
created without touching the "real" system catalogs, but the XID issue
seems a complete showstopper.
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Andres Freund
Date:
On Monday 18 October 2010 20:06:01 Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Currently it isn't possible to create temporary tables on read-only
> > standby servers, and I don't see it listed on the TODO list.  Can I add
> > it?
> 
> Not unless you have some credible concept for how it might ever be
> implemented.  You can't create temp tables because you can't modify
> system catalogs, and if you did somehow create them you couldn't put
> anything in them because you can't generate XIDs on a slave ... much
> less commit them.  We have talked about ways that temp tables might be
> created without touching the "real" system catalogs, but the XID issue
> seems a complete showstopper.
Hm. Wouldnt it be possible to use virtual xids for that purpose? They are 
never seen outside of that session anyway...

Andres


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Andres Freund <andres@anarazel.de> writes:
> On Monday 18 October 2010 20:06:01 Tom Lane wrote:
>> Not unless you have some credible concept for how it might ever be
>> implemented.  You can't create temp tables because you can't modify
>> system catalogs, and if you did somehow create them you couldn't put
>> anything in them because you can't generate XIDs on a slave ... much
>> less commit them.  We have talked about ways that temp tables might be
>> created without touching the "real" system catalogs, but the XID issue
>> seems a complete showstopper.

> Hm. Wouldnt it be possible to use virtual xids for that purpose? They are 
> never seen outside of that session anyway...

Well, maybe, but then you need infrastructure to track whether VXIDs
committed or aborted.
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Robert Haas
Date:
On Mon, Oct 18, 2010 at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andres Freund <andres@anarazel.de> writes:
>> On Monday 18 October 2010 20:06:01 Tom Lane wrote:
>>> Not unless you have some credible concept for how it might ever be
>>> implemented.  You can't create temp tables because you can't modify
>>> system catalogs, and if you did somehow create them you couldn't put
>>> anything in them because you can't generate XIDs on a slave ... much
>>> less commit them.  We have talked about ways that temp tables might be
>>> created without touching the "real" system catalogs, but the XID issue
>>> seems a complete showstopper.
>
>> Hm. Wouldnt it be possible to use virtual xids for that purpose? They are
>> never seen outside of that session anyway...
>
> Well, maybe, but then you need infrastructure to track whether VXIDs
> committed or aborted.

Seems like this would wreak havoc with the HeapTupleSatisfies* functions.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Oct 18, 2010 at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Andres Freund <andres@anarazel.de> writes:
>>> Hm. Wouldnt it be possible to use virtual xids for that purpose? They are
>>> never seen outside of that session anyway...
>> 
>> Well, maybe, but then you need infrastructure to track whether VXIDs
>> committed or aborted.

> Seems like this would wreak havoc with the HeapTupleSatisfies* functions.

Yeah, it would be messy all over.  This reminds me of last week's
discussion about mysql-style storage engines --- by the time you made
this work, you'd have something darn close to a separate storage engine
for temp tables.  It'd need its own parallel infrastructure covering
everything to do with tuple visibility determination.

It'd be kinda cool if we had it, but the work required to get there
seems far out of proportion to the benefits ...
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Robert Haas
Date:
On Mon, Oct 18, 2010 at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, Oct 18, 2010 at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Andres Freund <andres@anarazel.de> writes:
>>>> Hm. Wouldnt it be possible to use virtual xids for that purpose? They are
>>>> never seen outside of that session anyway...
>>>
>>> Well, maybe, but then you need infrastructure to track whether VXIDs
>>> committed or aborted.
>
>> Seems like this would wreak havoc with the HeapTupleSatisfies* functions.
>
> Yeah, it would be messy all over.  This reminds me of last week's
> discussion about mysql-style storage engines --- by the time you made
> this work, you'd have something darn close to a separate storage engine
> for temp tables.  It'd need its own parallel infrastructure covering
> everything to do with tuple visibility determination.
>
> It'd be kinda cool if we had it, but the work required to get there
> seems far out of proportion to the benefits ...

I agree.  I think that's backing into the problem from the wrong end.
The limiting factor here is that we require the entire cluster to be
replicated.  If you could replicate individual tables/schemas, then
this problem disappears.  Of course, that's not easy either, but if
you're going to solve a really hard problem, you might as well pick
that one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Creation of temporary tables on read-only standby servers

From
Greg Stark
Date:
On Mon, Oct 18, 2010 at 1:27 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> It'd be kinda cool if we had it, but the work required to get there
>> seems far out of proportion to the benefits ...
>
> I agree.  I think that's backing into the problem from the wrong end.
> The limiting factor here is that we require the entire cluster to be
> replicated.  If you could replicate individual tables/schemas, then
> this problem disappears.  Of course, that's not easy either, but if
> you're going to solve a really hard problem, you might as well pick
> that one.

That seems like an orthogonal issue. You can have a replica with fewer
objects -- which requires ignoring parts of the logs -- or more
objects -- which requires being able to do local modifications and
have local transaction states independent of the master. They're both
potentially useful features and neither replaces the need for the
other.

Simon talked about filtering the transaction logs a while back and got
a lot of pushback. But the more uses we put the slaves to the more it
will come up. I figure we'll eventually do something for this though
we might want more experience with the current setup before we dive
into it.

Adding extra objects in the slaves sounds massively harder. The idea
of using temp tables might be a useful simplification because in the
general case what you want is a separate set of xids with a separate
clog and wal. That sounds like it would be a lot more complex.

Another possibility though is to use the MED stuff. If we could
support creating tables locally that were actually hosted by a
separate database then you could do updates, inserts, etc against
those tables since they're actually happening in the remote database.
Alternately you set up your slave to be the target of remote tables in
a read-write data warehouse database. But this approach has
limitations of its own until our MED implementation is a lot more
powerful than it is today.


--
greg


Re: Creation of temporary tables on read-only standby servers

From
Robert Haas
Date:
On Mon, Oct 18, 2010 at 6:05 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Mon, Oct 18, 2010 at 1:27 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> It'd be kinda cool if we had it, but the work required to get there
>>> seems far out of proportion to the benefits ...
>>
>> I agree.  I think that's backing into the problem from the wrong end.
>> The limiting factor here is that we require the entire cluster to be
>> replicated.  If you could replicate individual tables/schemas, then
>> this problem disappears.  Of course, that's not easy either, but if
>> you're going to solve a really hard problem, you might as well pick
>> that one.
>
> That seems like an orthogonal issue. You can have a replica with fewer
> objects -- which requires ignoring parts of the logs -- or more
> objects -- which requires being able to do local modifications and
> have local transaction states independent of the master. They're both
> potentially useful features and neither replaces the need for the
> other.
>
> Simon talked about filtering the transaction logs a while back and got
> a lot of pushback. But the more uses we put the slaves to the more it
> will come up. I figure we'll eventually do something for this though
> we might want more experience with the current setup before we dive
> into it.
>
> Adding extra objects in the slaves sounds massively harder. The idea
> of using temp tables might be a useful simplification because in the
> general case what you want is a separate set of xids with a separate
> clog and wal. That sounds like it would be a lot more complex.

Well, temp tables really want a separate set of XIDs with a separate
CLOG, too.  Admittedly, they don't necessarily need WAL, if you can
make them work without catalog entries, but that's not so easy either.

> Another possibility though is to use the MED stuff. If we could
> support creating tables locally that were actually hosted by a
> separate database then you could do updates, inserts, etc against
> those tables since they're actually happening in the remote database.
> Alternately you set up your slave to be the target of remote tables in
> a read-write data warehouse database.  But this approach has
> limitations of its own until our MED implementation is a lot more
> powerful than it is today.

Agreed on all points.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Creation of temporary tables on read-only standby servers

From
Martijn van Oosterhout
Date:
On Tue, Oct 19, 2010 at 02:52:01PM -0400, Robert Haas wrote:
> Well, temp tables really want a separate set of XIDs with a separate
> CLOG, too.  Admittedly, they don't necessarily need WAL, if you can
> make them work without catalog entries, but that's not so easy either.

At one point there was the idea to have a sort of permanent temporary
tables which would have a pg_class entry but each session would have
its own copy. Replicated slaves would then also be able to use this
construction.

Doesn't help with the XIDs though.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
>                                       - Charles de Gaulle

Re: Creation of temporary tables on read-only standby servers

From
Robert Haas
Date:
On Tue, Oct 19, 2010 at 3:01 PM, Martijn van Oosterhout
<kleptog@svana.org> wrote:
> On Tue, Oct 19, 2010 at 02:52:01PM -0400, Robert Haas wrote:
>> Well, temp tables really want a separate set of XIDs with a separate
>> CLOG, too.  Admittedly, they don't necessarily need WAL, if you can
>> make them work without catalog entries, but that's not so easy either.
>
> At one point there was the idea to have a sort of permanent temporary
> tables which would have a pg_class entry but each session would have
> its own copy. Replicated slaves would then also be able to use this
> construction.
>
> Doesn't help with the XIDs though.

Hmm... yeah, I think I was the one who proposed that, actually.  :-)

The trick is that it would require us to have two pg_class tables, two
pg_attribute tables, two pg_attrdef tables, etc.: in each case, one
permanent and one temporary.  I am not sure how complex that will turn
out to be.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Creation of temporary tables on read-only standby servers

From
Greg Stark
Date:
On Tue, Oct 19, 2010 at 12:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> The trick is that it would require us to have two pg_class tables, two
> pg_attribute tables, two pg_attrdef tables, etc.: in each case, one
> permanent and one temporary.  I am not sure how complex that will turn
> out to be.

Tom suggested using inheritance for this.

I find it strange to try constructing catalog tables to represent
these local definitions which never need to be read by any other
backend and in any case are 1:1 copies of the global catalog entries.

It seems to me simpler and more direct to just nail relcache
entries for these objects into memory and manipulate them directly.
They can be constructed from the global catalog tables and then
tweaked to point to the backend local temporary tables.


--
greg


Re: Creation of temporary tables on read-only standby servers

From
Pavel Stehule
Date:
2010/10/19 Greg Stark <gsstark@mit.edu>:
> On Tue, Oct 19, 2010 at 12:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> The trick is that it would require us to have two pg_class tables, two
>> pg_attribute tables, two pg_attrdef tables, etc.: in each case, one
>> permanent and one temporary.  I am not sure how complex that will turn
>> out to be.
>
> Tom suggested using inheritance for this.
>
> I find it strange to try constructing catalog tables to represent
> these local definitions which never need to be read by any other
> backend and in any case are 1:1 copies of the global catalog entries.
>
> It seems to me simpler and more direct to just nail relcache
> entries for these objects into memory and manipulate them directly.
> They can be constructed from the global catalog tables and then
> tweaked to point to the backend local temporary tables.
>

+1

I had very ugly implementation of global temp tables based just on
relcache. The "only" one problem was with refresh of relcache. But
it's not too easy - for real using it's necessary to overwrite -
statistics, indexes, access statistics.

I had a idea to modify a data pages cache for support a permanent (and
only memory) pages. Then we can have a temporal tuples together with
standard tuples in one system table. This can be similar to memory
tables in mysql and can be interesting in cooperation with mmap - very
fast access to some tables or pre readed tables.

Regards

Pavel

> --
> greg
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> On Tue, Oct 19, 2010 at 12:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> The trick is that it would require us to have two pg_class tables, two
>> pg_attribute tables, two pg_attrdef tables, etc.: in each case, one
>> permanent and one temporary. �I am not sure how complex that will turn
>> out to be.

> Tom suggested using inheritance for this.

> I find it strange to try constructing catalog tables to represent
> these local definitions which never need to be read by any other
> backend and in any case are 1:1 copies of the global catalog entries.

> It seems to me simpler and more direct to just nail relcache
> entries for these objects into memory and manipulate them directly.

Relcache entries alone are not gonna work.  There is way too much stuff
that assumes that tables are correctly represented in the system
catalogs.

It's possible that you could make it work if you created the child
catalogs and immediately filled them with suitable entries describing
the child catalogs themselves.  Bootstrapping that might be a bit of fun
though.

The larger issue in all this is that there's so much code that supposes
that it just has to scan a particular catalog when it wants an entry,
and isn't going to go looking for child tables of the catalog.  That's
possibly fixable but is not likely to be easy, unless you can somehow
hide it within systable_beginscan and related routines.
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Greg Stark
Date:
On Tue, Oct 19, 2010 at 3:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Relcache entries alone are not gonna work.  There is way too much stuff
> that assumes that tables are correctly represented in the system
> catalogs.
>

Well we're talking about multiple things now. In the global temporary
table case they *are* properly represented in the system catalogs.
Except for their local state such as the actual relfilenode all the
structural attributes are going to be accurate.

In the case of tables created locally on a slave, well, that's more complicated.

--
greg


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> On Tue, Oct 19, 2010 at 3:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Relcache entries alone are not gonna work. �There is way too much stuff
>> that assumes that tables are correctly represented in the system
>> catalogs.

> Well we're talking about multiple things now. In the global temporary
> table case they *are* properly represented in the system catalogs.
> Except for their local state such as the actual relfilenode all the
> structural attributes are going to be accurate.

... and relpages and reltuples ... it's really not going to be that easy
to have a table that isn't described in pg_class.  Which the structure
you're describing isn't.  There might be a template for it in pg_class,
but that's something entirely different.

> In the case of tables created locally on a slave, well, that's more
> complicated.

I think they're more alike than you think.  If we had the infrastructure
to do local temp tables this way, it'd be pretty easy to use that to
instantiate per-backend copies of global temp tables.  (The global
entities would be templates, not actual tables.)
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Greg Stark
Date:
On Tue, Oct 19, 2010 at 4:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ... and relpages and reltuples ...

Those are hardly very convincing examples. Neither are values that
need to be updated in transactions and neither relates to the
definition of the table. They could be moved from pg_class to some
internal data structure without imposing any real problems.

I guess what I'm saying is that mixing the low-level data about the
storage with the high-level schema information in a single table is
what painted us into this corner. If we separate those two then we
have a natural separation between the data that can be read-only
global for all temporary tables and the data that's read-write and
stored in memory.

Now that I've written that though I wonder what happens when you
modify the schema. Do you have to wait unti all transactions are done
with their temporary copies of the table?

> it's really not going to be that easy
> to have a table that isn't described in pg_class.  Which the structure
> you're describing isn't.  There might be a template for it in pg_class,
> but that's something entirely different.
>
>> In the case of tables created locally on a slave, well, that's more
>> complicated.
>
> I think they're more alike than you think.  If we had the infrastructure
> to do local temp tables this way, it'd be pretty easy to use that to
> instantiate per-backend copies of global temp tables.  (The global
> entities would be templates, not actual tables.)

Sure, but I think the idea was that you should be able to create
temporary tables from whole cloth on the slave. Since they're local to
the backend they never have to be stored on disk so logically from the
user's point of view it seems like it should be possible.


--
greg


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> On Tue, Oct 19, 2010 at 4:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think they're more alike than you think. �If we had the infrastructure
>> to do local temp tables this way, it'd be pretty easy to use that to
>> instantiate per-backend copies of global temp tables. �(The global
>> entities would be templates, not actual tables.)

> Sure, but I think the idea was that you should be able to create
> temporary tables from whole cloth on the slave. Since they're local to
> the backend they never have to be stored on disk so logically from the
> user's point of view it seems like it should be possible.

The way I'm envisioning this working is that you instantiate temporary
child tables of all the system catalogs that are needed to describe
tables.  Being system catalogs, their schemas never change, so you don't
have a problem there.  Then you use these children to store the catalog
entries describing user temp tables.  Whether those temp tables are
instantiations of spec-style global temp tables, or our current flavor
of local temp tables, won't matter.

I think it's pointless to speculate about whether we might have divvied
up the meta-information about tables differently if we'd foreseen
wanting to do this.  It is what it is, and there is *way* too much code
depending on it, both inside the backend and in clients.  Any
reimplementation of temp tables will still have to expose largely the
same catalog information that exists for tables now.  We can probably
get away with marginal changes like redefining relfilenode, but we can't
avoid providing catalog entries that describe the schema and statistics
of a temp table.
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Greg Stark
Date:
On Wed, Oct 20, 2010 at 8:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think it's pointless to speculate about whether we might have divvied
> up the meta-information about tables differently if we'd foreseen
> wanting to do this.  It is what it is, and there is *way* too much code
> depending on it, both inside the backend and in clients.  Any
> reimplementation of temp tables will still have to expose largely the
> same catalog information that exists for tables now.  We can probably
> get away with marginal changes like redefining relfilenode, but we can't
> avoid providing catalog entries that describe the schema and statistics
> of a temp table.

I agree about the schema -- that's the whole point of the catalog tables.

I felt like the statistics were pretty marginal to begin with. There
may be a large number of places but there's no complex structure of
relationships to other tables or complex data structures going on
here. Surely they can all be coded to look up the relpages from
somewhere else just as easily?

But I'm not about to start working on this area so my judgement on how
much work that would be isn't very important here.

And your point that if we have a complete local copy of the entire
catalog schema then we can create temporary tables from whole cloth on
a read-only database
just as easily is attractive.

--
greg


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> On Wed, Oct 20, 2010 at 8:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think it's pointless to speculate about whether we might have divvied
>> up the meta-information about tables differently if we'd foreseen
>> wanting to do this. �It is what it is, and there is *way* too much code
>> depending on it, both inside the backend and in clients. �Any
>> reimplementation of temp tables will still have to expose largely the
>> same catalog information that exists for tables now. �We can probably
>> get away with marginal changes like redefining relfilenode, but we can't
>> avoid providing catalog entries that describe the schema and statistics
>> of a temp table.

> I agree about the schema -- that's the whole point of the catalog tables.

> I felt like the statistics were pretty marginal to begin with.

I'm thinking more of pg_statistic than the stuff in pg_class --- I agree
that we could probably kluge some other approach for relpages and
reltuples, but that doesn't scale to the real statistics.
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Bruce Momjian
Date:
Greg Stark wrote:
> On Tue, Oct 19, 2010 at 12:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> > The trick is that it would require us to have two pg_class tables, two
> > pg_attribute tables, two pg_attrdef tables, etc.: in each case, one
> > permanent and one temporary. ?I am not sure how complex that will turn
> > out to be.
> 
> Tom suggested using inheritance for this.
> 
> I find it strange to try constructing catalog tables to represent
> these local definitions which never need to be read by any other
> backend and in any case are 1:1 copies of the global catalog entries.
> 
> It seems to me simpler and more direct to just nail relcache
> entries for these objects into memory and manipulate them directly.
> They can be constructed from the global catalog tables and then
> tweaked to point to the backend local temporary tables.

Funny, but that is how I implemented temporary tables in 1999 and lasted
until 2002 when schema support was added.  It actually worked because
all the lookups go through the syscache.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Creation of temporary tables on read-only standby servers

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Currently it isn't possible to create temporary tables on read-only
> > standby servers, and I don't see it listed on the TODO list.  Can I add
> > it?
> 
> Not unless you have some credible concept for how it might ever be
> implemented.  You can't create temp tables because you can't modify
> system catalogs, and if you did somehow create them you couldn't put
> anything in them because you can't generate XIDs on a slave ... much
> less commit them.  We have talked about ways that temp tables might be
> created without touching the "real" system catalogs, but the XID issue
> seems a complete showstopper.

So, this is one of those odd cases where we know people are going to ask
for a feature (temp tables on slaves), but we are not ready to put it on
our TODO list.  Where do we document that this isn't going to happen? 
In "Features we don't want"?  That title doesn't really match. 
"Features we don't know how to do" doesn't sound good.  ;-)

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Creation of temporary tables on read-only standby servers

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Greg Stark wrote:
>> It seems to me simpler and more direct to just nail relcache
>> entries for these objects into memory and manipulate them directly.
>> They can be constructed from the global catalog tables and then
>> tweaked to point to the backend local temporary tables.

> Funny, but that is how I implemented temporary tables in 1999 and lasted
> until 2002 when schema support was added.  It actually worked because
> all the lookups go through the syscache.

... and as I recall, we got rid of it principally because the temp
tables weren't visible to ordinary catalog lookups, thus breaking
all sorts of client-side logic.
        regards, tom lane


Re: Creation of temporary tables on read-only standby servers

From
Greg Stark
Date:
On Thu, Oct 21, 2010 at 7:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ... and as I recall, we got rid of it principally because the temp
> tables weren't visible to ordinary catalog lookups, thus breaking
> all sorts of client-side logic.
>

Though that wouldn't be the case if the catalogs held a template.....

Anyways I think this horse has been beaten to death. Whoever
implements it will look at the pros and cons and decide which way
they'll go.

-- 
greg


Re: Creation of temporary tables on read-only standby servers

From
Jim Nasby
Date:
On Oct 19, 2010, at 2:01 PM, Martijn van Oosterhout wrote:
> On Tue, Oct 19, 2010 at 02:52:01PM -0400, Robert Haas wrote:
>> Well, temp tables really want a separate set of XIDs with a separate
>> CLOG, too.  Admittedly, they don't necessarily need WAL, if you can
>> make them work without catalog entries, but that's not so easy either.
>
> At one point there was the idea to have a sort of permanent temporary
> tables which would have a pg_class entry but each session would have
> its own copy. Replicated slaves would then also be able to use this
> construction.
>
> Doesn't help with the XIDs though.

I think we're trying to boil the ocean here...

There are a lot of downsides to temp tables touching the catalog. This feature is probably important enough to justify
evenif it doesn't help with replication. 

Of course, once we have global temporary tables the next step is to look at other ways they can be optimized. Certainly
theydon't need to WAL log. That's another step closer to supporting them in replication. 

The journey of 1000 miles starts with a single step...

Also, global temp tables would definitely help us at work, which means I can convince TPTB to spend money on this. So
ifanyone is serious about working on them contact me off-list. I doubt we could fund the entire effort, but we could
certainlyput a dent in it. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net




Re: Creation of temporary tables on read-only standby servers

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Greg Stark wrote:
> >> It seems to me simpler and more direct to just nail relcache
> >> entries for these objects into memory and manipulate them directly.
> >> They can be constructed from the global catalog tables and then
> >> tweaked to point to the backend local temporary tables.
> 
> > Funny, but that is how I implemented temporary tables in 1999 and lasted
> > until 2002 when schema support was added.  It actually worked because
> > all the lookups go through the syscache.
> 
> ... and as I recall, we got rid of it principally because the temp
> tables weren't visible to ordinary catalog lookups, thus breaking
> all sorts of client-side logic.

Yes, I felt lucky the breakage was so minimal.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +