Creation of temporary tables on read-only standby servers

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

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

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

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

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

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

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

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

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

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/

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

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

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 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

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

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

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

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

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

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

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. +

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. +

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

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

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 even if 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 they don'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 if anyone is serious about working on them contact me off-list. I doubt we could fund the entire effort, but we could certainly put a dent in it.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

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. +