Horton

Thread

  1. Temporary tables under hot standby

    Noah Misch <noah@leadboat.com> — 2012-04-25T03:55:15Z

    A key barrier to migrations from trigger-based replication to WAL-based
replication is the lack of temporary tables under hot standby.  I'd like to
close that gap; the changes needed will also reduce the master-side cost of
temporary table usage.  Here is a high-level design for your advice and
comments.  Much of this builds on ideas from past postings noted below.

Our temporary tables are cataloged and filled like permanent tables.  This has
the major advantage of making most code operate on tables with minimal regard
for their relpersistence.  It also brings disadvantages:

1. High catalog turnover in rapid create/drop workloads.  Heavy temporary
   table users often need to REINDEX relation-oriented catalogs.  Hot standby
   cannot assign OIDs or modify system catalogs at all.
2. Consumption of permanent XIDs for DML on the table.  This increases COMMIT
   cost on the master and is a non-starter under hot standby.
3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values
   delay pg_clog truncation and can trigger a wraparound-prevention shutdown.
4. sinval traffic from every CREATE TEMP TABLE et al.
5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.
6. We don't automatically drop temporary tables that existed at the point of a
   crash, because they look much like permanent tables.

To resolve points 2 and 3, let's change the XID values stored in temporary
tables from regular TransactionId to LocalTransactionId.  This entails an lxid
counterpart for clog.c.  Functions like GetCurrentTransactionId() and
HeapTupleSatisfiesVisibility() will take a Relation argument to identify the
XID type.  One open question is whether to add conditional logic to functions
like HeapTupleSatisfiesMVCC() or to have parallel implementations like
HeapTupleSatisfiesMVCCLocal().  I lean toward the latter, perhaps with the
help of some code generation.  I don't think a counterpart for pg_subtrans
will be necessary; the backend knows its own XID tree, and the
TRANSACTION_STATUS_SUB_COMMITTED interlock is superfluous with only one
backend as reader and writer.  I'm also thinking the local clog can live
strictly in memory; a session that retains a temporary table across 2B local
transactions can afford 512 MiB of RAM.  With this change, VACUUM can ignore
relfrozenxid of temporary tables when calculating a new datfrozenxid.  This
change can form an independent patch.

I do not see a clean behind-the-scenes fix for points 1, 4 and 5.  We can
resolve those by adding a new variety of temporary table, one coincidentally
matching the SQL standard's notion of a temporary table.  The developer will
declare it once, after which all sessions observe it as an initially-empty
table whose contents remain local to the session.  Most relation catalog
entries, including all OIDs, are readily sharable among sessions.  The
exceptions are relpages, reltuples, relallvisible, relfrozenxid, and
pg_statistic rows.  I will handle the pg_class columns by introducing new
backend APIs abstracting them.  Those APIs will consult the relcache for
permanent tables and a local-memory hash for temporary tables.  For
statistics, add a new catalog pg_temp_statistic, an inheritance child of
pg_statistic and itself one of these new-variety temporary tables.

Past discussions have raised the issue of interaction between commands like
ALTER TABLE and sessions using the new-variety temporary table.  As a first
cut, let's keep this simple and have ongoing use of the table block operations
requiring AccessExclusiveLock.  Note that you can always just make a new
temporary table with a different name to deploy a change quickly.  Implement
this with a heavyweight lock having a novel life cycle.  When a session first
takes an ordinary relation lock on the table, acquire the longer-term lock and
schedule it for release on transaction abort.  On TRUNCATE, schedule a release
on transaction commit.  Of course, also release the lock at session end.

For new-variety temporary tables, change file names from "relfilenode[_fork]"
to "refilenode[_fork].pid.localnode".  During crash recovery, delete all files
conforming to that pattern for refilenodes of known temporary tables.  This
also lets backends share pg_class.relfilenode.  The "localnode" field would
distinguish multiple generations of a table across VACUUM FULL, CLUSTER, and
TRUNCATE.  We could build on this strategy to safely resolve point 6 for the
existing kind of temporary table, but that entails enough other details to
probably not mix it into the same patch.

A third patch will permit the following commands in read-only transactions,
where they will throw an error if the subject is not a temporary table:

INSERT
UPDATE
DELETE
COPY ... FROM
TRUNCATE
ANALYZE
VACUUM (including VACUUM FULL)
CLUSTER (without USING clause)
REINDEX

I considered whether to instead separate the set of commands allowed in a
read-only transaction from the set allowed under hot standby.  This proposal
is closer to the SQL standard, which explicitly allows INSERT, UPDATE and
DELETE on temporary tables during read-only transactions.  Only the first five
commands are essential; support for the rest could wait for follow-on patches.

Concerning everyone's favorite topic, how to name the new type of table, I
liked Tom's proposal[1] to make CREATE TEMP TABLE retain current behavior and
have CREATE GLOBAL TEMP TABLE and/or CREATE LOCAL TEMP TABLE request the new
SQL-standard variety.  (I'd vote for using CREATE GLOBAL and retaining CREATE
LOCAL for future expansion.)  As he mentions, to get there, we'd ideally start
by producing a warning instead of silently accepting GLOBAL as a noise word.
Should we put such a warning into 9.2?

How important is support for VACUUM on these tables under hot standby?  The
alternative is to fail when a session retains a temporary table across 2B
local transactions.  I do not currently see any challenges sufficient to
motivate not supporting VACUUM, but it might be a useful simplification to
keep in mind.  What about ANALYZE support; how important is the ability to
collect statistics on temporary tables?  Again, I tentatively expect to
support it regardless of the answer.

Key past threads:
http://archives.postgresql.org/message-id/7903.1050417344@sss.pgh.pa.us
http://archives.postgresql.org/message-id/162867790707011431u71e53543x19e64e5bb160b124@mail.gmail.com
http://archives.postgresql.org/message-id/162867790904271344s1ec96d90j6cde295fdcc7806f@mail.gmail.com
http://archives.postgresql.org/message-id/u2o603c8f071004231952i36642ae6u9d6a7eae6eb6ff32@mail.gmail.com
http://archives.postgresql.org/message-id/BANLkTin1Gha0SS77E64jczPfAPn6Oxb8hQ%40mail.gmail.com

Thanks,
nm

[1] http://archives.postgresql.org/message-id/5422.1240936705@sss.pgh.pa.us

  2. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T08:00:16Z

    On Wed, Apr 25, 2012 at 4:55 AM, Noah Misch <noah@leadboat.com> wrote:

> 1. High catalog turnover in rapid create/drop workloads.  Heavy temporary
>   table users often need to REINDEX relation-oriented catalogs.  Hot standby
>   cannot assign OIDs or modify system catalogs at all.
> 4. sinval traffic from every CREATE TEMP TABLE et al.
> 5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.

> I do not see a clean behind-the-scenes fix for points 1, 4 and 5.  We can
> resolve those by adding a new variety of temporary table, one coincidentally
> matching the SQL standard's notion of a temporary table.  The developer will
> declare it once, after which all sessions observe it as an initially-empty
> table whose contents remain local to the session.

The rest of your post is very good and I'm excited. This part doesn't
work for me.

I don't see how introducing a new type of temp table solves this
problem. How would the developer declare this in HS? How would it then
be globally visible without using global OIDs, causing sinval and
using global locks? This feels like a suggestion from somewhere else
grafted onto your proposal. I'm not against introducing a new type of
temp table, I just think it is orthogonal to the OT.

For me, the solutions are
4. Invent a "linval" - an invalidation that always stays local
5. invent a LocalRelationLock that takes AccessExclusiveLock but never
goes to the global lock table.
So those aspects stay completely in local memory.

1. is a little harder. I suggest we have a range of say 16384 OIDs
reserved for use by temporary relations. If that range is used up we
do then move to using real global Ids, though that is code we'll
almost never need, so it would be acceptable to restrict HS sessions
to only ever use 16384 temp tables concurrently in one session.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  3. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T08:04:54Z

    On Wed, Apr 25, 2012 at 4:55 AM, Noah Misch <noah@leadboat.com> wrote:

> A third patch will permit the following commands in read-only transactions,
> where they will throw an error if the subject is not a temporary table:
...
> VACUUM (including VACUUM FULL)
> CLUSTER (without USING clause)
> REINDEX

Those commands have very low user visible effect on temp tables, so I
suggest we implement them as no-op commands in HS. When everything
else is done and dusted, they might be worth returning to, but I'd
hope you won't spend much time on those anytime soon.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  4. Re: Temporary tables under hot standby

    Nicolas Barbier <nicolas.barbier@gmail.com> — 2012-04-25T08:37:16Z

    2012/4/25 Simon Riggs <simon@2ndquadrant.com>:

> On Wed, Apr 25, 2012 at 4:55 AM, Noah Misch <noah@leadboat.com> wrote:
>
>> I do not see a clean behind-the-scenes fix for points 1, 4 and 5.  We can
>> resolve those by adding a new variety of temporary table, one coincidentally
>> matching the SQL standard's notion of a temporary table.  The developer will
>> declare it once, after which all sessions observe it as an initially-empty
>> table whose contents remain local to the session.

[..]

> I don't see how introducing a new type of temp table solves this
> problem. How would the developer declare this in HS? How would it then
> be globally visible without using global OIDs, causing sinval and
> using global locks?

The declarative creation of an “standard-like” temporary table only
happens once (it is part of the schema). Using (e.g. putting stuff in
and executing queries on) such tables can happen on the standby
without the master having to know.

Therefore, I don't see the problem. Just schedule issuing the creation
along with any other schema-changes on the master.

Nicolas

-- 
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?

  5. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T09:10:31Z

    On Wed, Apr 25, 2012 at 9:37 AM, Nicolas Barbier
<nicolas.barbier@gmail.com> wrote:

> The declarative creation of an “standard-like” temporary table only
> happens once (it is part of the schema). Using (e.g. putting stuff in
> and executing queries on) such tables can happen on the standby
> without the master having to know.

So you are saying it is OK to not be able to *create* them on HS, just
*use* pre-defined tables?

That's almost useless IMHO.

Applications expect to be able to do this all in the same transaction
on one session
CREATE TEMP TABLE x;
 ...DML commands...
SELECT ... FROM x;

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  6. Re: Temporary tables under hot standby

    Nicolas Barbier <nicolas.barbier@gmail.com> — 2012-04-25T10:08:23Z

    2012/4/25 Simon Riggs <simon@2ndquadrant.com>:

> So you are saying it is OK to not be able to *create* them on HS, just
> *use* pre-defined tables?
>
> That's almost useless IMHO.
>
> Applications expect to be able to do this all in the same transaction
> on one session
> CREATE TEMP TABLE x;
>  ...DML commands...
> SELECT ... FROM x;

That’s not how standard-like temporary tables work, they are supposed
to be declared beforehand. That makes sense if you consider the schema
and the set of database-using applications as one. I assume that
wanting to define applications independently from the database schema
is the reason of existence for the PG-like temporary transactions.

The way standard-like temporary tables work is exactly why I assume
Noah proposes to implement them: because they work nicely with HS.

Nicolas

-- 
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?

  7. Re: Temporary tables under hot standby

    Nicolas Barbier <nicolas.barbier@gmail.com> — 2012-04-25T10:10:56Z

    2012/4/25 Nicolas Barbier <nicolas.barbier@gmail.com>:

> is the reason of existence for the PG-like temporary transactions.

s/transactions/tables/

Nicolas

-- 
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?

  8. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T11:16:16Z

    On Wed, Apr 25, 2012 at 11:08 AM, Nicolas Barbier
<nicolas.barbier@gmail.com> wrote:
> 2012/4/25 Simon Riggs <simon@2ndquadrant.com>:
>
>> So you are saying it is OK to not be able to *create* them on HS, just
>> *use* pre-defined tables?
>>
>> That's almost useless IMHO.
>>
>> Applications expect to be able to do this all in the same transaction
>> on one session
>> CREATE TEMP TABLE x;
>>  ...DML commands...
>> SELECT ... FROM x;
>
> That’s not how standard-like temporary tables work, they are supposed
> to be declared beforehand. That makes sense if you consider the schema
> and the set of database-using applications as one. I assume that
> wanting to define applications independently from the database schema
> is the reason of existence for the PG-like temporary transactions.
>
> The way standard-like temporary tables work is exactly why I assume
> Noah proposes to implement them: because they work nicely with HS.

Well, following a standard that no other major DBMS has followed is
not great, especially if it leads to a non-useful feature.

Many software products generate CREATE TEMP TABLE statements
dynamically. This design would prevent ALL of them from working, as
well as preventing all current programs from using temp tables in the
currently accepted way, so the whole concept is very regrettably
flawed.

I very much support Noah's work to "make temp tables work on hot
standby", but we must solve the main problem, not just implement "make
a completely new kind of temp table work on hot standby". I have no
objection to "make a new kind of temp table", but that does not solve
the "make temp tables work on hot standby" problem.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  9. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-25T15:49:23Z

    On Tue, Apr 24, 2012 at 11:55 PM, Noah Misch <noah@leadboat.com> wrote:
> A key barrier to migrations from trigger-based replication to WAL-based
> replication is the lack of temporary tables under hot standby.  I'd like to
> close that gap; the changes needed will also reduce the master-side cost of
> temporary table usage.  Here is a high-level design for your advice and
> comments.  Much of this builds on ideas from past postings noted below.
>
> Our temporary tables are cataloged and filled like permanent tables.  This has
> the major advantage of making most code operate on tables with minimal regard
> for their relpersistence.  It also brings disadvantages:
>
> 1. High catalog turnover in rapid create/drop workloads.  Heavy temporary
>   table users often need to REINDEX relation-oriented catalogs.  Hot standby
>   cannot assign OIDs or modify system catalogs at all.
> 2. Consumption of permanent XIDs for DML on the table.  This increases COMMIT
>   cost on the master and is a non-starter under hot standby.
> 3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values
>   delay pg_clog truncation and can trigger a wraparound-prevention shutdown.
> 4. sinval traffic from every CREATE TEMP TABLE et al.
> 5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.
> 6. We don't automatically drop temporary tables that existed at the point of a
>   crash, because they look much like permanent tables.

#6 is already fixed in 9.1.  Temporary tables now have names like
tBACKENDID_RELFILENODE, and are cleaned up during start-up in exactly
the same way you're propose to do it further down.

> To resolve points 2 and 3, let's change the XID values stored in temporary
> tables from regular TransactionId to LocalTransactionId.  This entails an lxid
> counterpart for clog.c.  Functions like GetCurrentTransactionId() and
> HeapTupleSatisfiesVisibility() will take a Relation argument to identify the
> XID type.  One open question is whether to add conditional logic to functions
> like HeapTupleSatisfiesMVCC() or to have parallel implementations like
> HeapTupleSatisfiesMVCCLocal().  I lean toward the latter, perhaps with the
> help of some code generation.  I don't think a counterpart for pg_subtrans
> will be necessary; the backend knows its own XID tree, and the
> TRANSACTION_STATUS_SUB_COMMITTED interlock is superfluous with only one
> backend as reader and writer.  I'm also thinking the local clog can live
> strictly in memory; a session that retains a temporary table across 2B local
> transactions can afford 512 MiB of RAM.  With this change, VACUUM can ignore
> relfrozenxid of temporary tables when calculating a new datfrozenxid.  This
> change can form an independent patch.

Agreed.  If you can pull it off, this will be a nice improvement
regardless of what happens with the rest of this, and it makes sense
to do it as a separate patch.  I don't yet have a strong opinion on
what to do with the HeapTupleSatisfies* functions, but I suspect
you're correct in thinking that separate functions are better.  For
one thing, those functions are very much performance-critical, so
introducing extra branches is something to avoid.

Maybe this is a silly idea, but if you're thinking about creating a
local XID space and a global XID space, it might be a good idea to
also make allowance for an "unlogged" XID space - that is, an XID
space that is global to all backends but need not survive crashes.
This would potentially allow unlogged tables to be used in HS mode.  I
would expect that you probably don't want to go as far as actually
trying to make this work as part of your current project, but maybe as
you're structuring the code it would be worth considering the
possibility that we'll eventually want >2 XID spaces, either for this
or other reasons.

> I do not see a clean behind-the-scenes fix for points 1, 4 and 5.  We can
> resolve those by adding a new variety of temporary table, one coincidentally
> matching the SQL standard's notion of a temporary table.  The developer will
> declare it once, after which all sessions observe it as an initially-empty
> table whose contents remain local to the session.  Most relation catalog
> entries, including all OIDs, are readily sharable among sessions.  The
> exceptions are relpages, reltuples, relallvisible, relfrozenxid, and
> pg_statistic rows.  I will handle the pg_class columns by introducing new
> backend APIs abstracting them.  Those APIs will consult the relcache for
> permanent tables and a local-memory hash for temporary tables.  For
> statistics, add a new catalog pg_temp_statistic, an inheritance child of
> pg_statistic and itself one of these new-variety temporary tables.

With respect to problem #5, I've been wondering if we couldn't just
forget about taking AccessExclusiveLock when first creating a table
(temporary or permanent).  Unless and until the transaction commits,
nobody can see the catalog entry anyway, so nobody else is going to
attempt to take a conflicting lock.  So why bother?  Maybe there's
some reason here that's not obvious to me.  If not, it might be worth
doing on general principle independent of this project.

Problem #4 is a little stickier, but I think also solvable.  Basically
all cross-backend access to temp tables ought to be prohibited anyway,
but it currently isn't, because there's at least one cross-backend
operation that we categorically need to support: DROP.  Separating the
global and local XID spaces would help with that, but not really all
that much, since if a session manages to exit without cleaning up
after itself, we'd like someone to be able to do that later (and maybe
more aggressively than what we do now, which is to only clean things
up after 2B txns or so).  Nevertheless it's currently possible for
backend A to build a relcache entry for backend B's temporary
relation, and it might be that if we prohibited that and maybe a few
other things we could see our way clear to removing most or all of the
sinval traffic here.  Still, it may not really help that much without
a solution to problem #1.

On that note, I had a thought in the pat that it might be possible to
do solve problem #1 by using global temp tables as system catalogs -
that is, for each type of system catalog that relates to table
creation, you'd have a permanent catalog and a global temp catalog.
So if someone wants to create a temporary table of the existing
variety on the standby, you can make all the entries in the
global-temp version of pg_class, pg_attribute, etc.  However, this
seems extremely difficult to manage in general - there's a lot of code
churn involved, and also possible temporary -> permanent dependencies;
for example, the temporary table might have a pg_attrdef entry that
needs to depend on a non-temporary pg_proc entry.  That's tricky to
solve on the master and even trickier to solve in HS operation.  So
I'm inclined to agree with you that it makes more sense to just aim to
support global temp tables in HS mode, and if we want to beat our head
against the brick wall of making regular temp tables work there
eventually, that can be a later project.

I would suggest just not worrying about the statistical stuff for the
first version of the patch.  Assume that it's adequate to have one set
of statistics for all copies of the table, both pg_statistic entries
and the stats-related stuff in pg_class (relpages, reltuples,
relallvisible).  It's not unreasonable to think that the table will be
used in broadly similiar ways across all backends, so in some cases
this might actually give better performance than what you're proposing
to do.  If not, you can solve that problem in a future patch.  This
project is hard enough without throwing that problem onto the pile,
and I really think that if you do throw it on the pile you're going to
be adding a lot of complexity and code churn that isn't really
necessary for a v1.

> Past discussions have raised the issue of interaction between commands like
> ALTER TABLE and sessions using the new-variety temporary table.  As a first
> cut, let's keep this simple and have ongoing use of the table block operations
> requiring AccessExclusiveLock.  Note that you can always just make a new
> temporary table with a different name to deploy a change quickly.  Implement
> this with a heavyweight lock having a novel life cycle.  When a session first
> takes an ordinary relation lock on the table, acquire the longer-term lock and
> schedule it for release on transaction abort.  On TRUNCATE, schedule a release
> on transaction commit.  Of course, also release the lock at session end.

I'm not sure I believe this will work, but maybe I'm just not understanding it.

> For new-variety temporary tables, change file names from "relfilenode[_fork]"
> to "refilenode[_fork].pid.localnode".  During crash recovery, delete all files
> conforming to that pattern for refilenodes of known temporary tables.  This
> also lets backends share pg_class.relfilenode.  The "localnode" field would
> distinguish multiple generations of a table across VACUUM FULL, CLUSTER, and
> TRUNCATE.  We could build on this strategy to safely resolve point 6 for the
> existing kind of temporary table, but that entails enough other details to
> probably not mix it into the same patch.

In lieu of including localnode in the filename, I would suggest that
for a global temp table, we set relfilenode = 0, and let each backend
assign one (and reassign new ones) whenever it feels like it.  The
mapping from reloid -> relfilenode can be kept in backend-local
memory, or you can keep a <backend id, rel oid> -> relfilenode mapping
in a separate relation fork.  The latter would have the additional
advantage of simplifying cleanup and might also be helpful in
detecting when the table is or is not in use by multiple backends.

> How important is support for VACUUM on these tables under hot standby?  The
> alternative is to fail when a session retains a temporary table across 2B
> local transactions.  I do not currently see any challenges sufficient to
> motivate not supporting VACUUM, but it might be a useful simplification to
> keep in mind.  What about ANALYZE support; how important is the ability to
> collect statistics on temporary tables?  Again, I tentatively expect to
> support it regardless of the answer.

I think it's probably pretty important to support VACUUM, because even
ignoring wraparound considerations, not vacuuming tends to cause
performance to suck.  I think ANALYZE is less important for the
reasons stated above.

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

  10. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T16:08:05Z

    On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas <robertmhaas@gmail.com> wrote:

>> How important is support for VACUUM on these tables under hot standby?  The
>> alternative is to fail when a session retains a temporary table across 2B
>> local transactions.  I do not currently see any challenges sufficient to
>> motivate not supporting VACUUM, but it might be a useful simplification to
>> keep in mind.  What about ANALYZE support; how important is the ability to
>> collect statistics on temporary tables?  Again, I tentatively expect to
>> support it regardless of the answer.
>
> I think it's probably pretty important to support VACUUM, because even
> ignoring wraparound considerations, not vacuuming tends to cause
> performance to suck.  I think ANALYZE is less important for the
> reasons stated above.

ANALYZE is essential for temp tables in many cases... not sure what
the "reasons stated above" were, I can't resolve that reference.

I've never seen VACUUM used on a temp table. Perhaps we need it for
edge cases, but either way ISTM to be low priority. If people find
temp tables restrictive they can just use unlogged tables instead.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  11. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-25T16:18:09Z

    On Wed, Apr 25, 2012 at 12:08 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> How important is support for VACUUM on these tables under hot standby?  The
>>> alternative is to fail when a session retains a temporary table across 2B
>>> local transactions.  I do not currently see any challenges sufficient to
>>> motivate not supporting VACUUM, but it might be a useful simplification to
>>> keep in mind.  What about ANALYZE support; how important is the ability to
>>> collect statistics on temporary tables?  Again, I tentatively expect to
>>> support it regardless of the answer.
>>
>> I think it's probably pretty important to support VACUUM, because even
>> ignoring wraparound considerations, not vacuuming tends to cause
>> performance to suck.  I think ANALYZE is less important for the
>> reasons stated above.
>
> ANALYZE is essential for temp tables in many cases... not sure what
> the "reasons stated above" were, I can't resolve that reference.

My theory is that users of a global temp table will have
similar-enough usage patterns that a set of statistics that is good
enough for one user will be good enough for all of them.  That might
not be true in all cases, but I think it will simplify things quite a
bit to assume it true for purposes of an initial implementation.  And
as I noted, in some cases it might be a clear improvement: right now,
after creating a temp table, you've got to analyze it or you'll just
get the default statistics, which figure to be terrible.  Inheriting
the statistics left over from the last guy's analyze figures to be
significantly superior.

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

  12. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-25T16:19:38Z

    On Wed, Apr 25, 2012 at 12:18 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 25, 2012 at 12:08 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>>> How important is support for VACUUM on these tables under hot standby?  The
>>>> alternative is to fail when a session retains a temporary table across 2B
>>>> local transactions.  I do not currently see any challenges sufficient to
>>>> motivate not supporting VACUUM, but it might be a useful simplification to
>>>> keep in mind.  What about ANALYZE support; how important is the ability to
>>>> collect statistics on temporary tables?  Again, I tentatively expect to
>>>> support it regardless of the answer.
>>>
>>> I think it's probably pretty important to support VACUUM, because even
>>> ignoring wraparound considerations, not vacuuming tends to cause
>>> performance to suck.  I think ANALYZE is less important for the
>>> reasons stated above.
>>
>> ANALYZE is essential for temp tables in many cases... not sure what
>> the "reasons stated above" were, I can't resolve that reference.
>
> My theory is that users of a global temp table will have
> similar-enough usage patterns that a set of statistics that is good
> enough for one user will be good enough for all of them.  That might
> not be true in all cases, but I think it will simplify things quite a
> bit to assume it true for purposes of an initial implementation.  And
> as I noted, in some cases it might be a clear improvement: right now,
> after creating a temp table, you've got to analyze it or you'll just
> get the default statistics, which figure to be terrible.  Inheriting
> the statistics left over from the last guy's analyze figures to be
> significantly superior.

Oh, we're talking about different things, and I'm slightly confused.

Yes, we need to support ANALYZE; what we might not need to support, at
least initially, is every user of a global temp table having their own
SEPARATE copy of the table statistics.

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

  13. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T16:30:38Z

    On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:

> Oh, we're talking about different things, and I'm slightly confused.
>
> Yes, we need to support ANALYZE; what we might not need to support, at
> least initially, is every user of a global temp table having their own
> SEPARATE copy of the table statistics.

Yes, we are. Global Temp Tables won't solve the "Works on HS" problem,
so we'd better decide fairly quickly which use case we are addressing,
and why. ISTM Global Temp Tables is more an Oracle compatibility issue
than a problem PostgreSQL users have.

...I have zero basis for deciding whether what you say about Global
Temp Tables is useful or not.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  14. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-25T16:53:02Z

    On Wed, Apr 25, 2012 at 12:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
>> Oh, we're talking about different things, and I'm slightly confused.
>>
>> Yes, we need to support ANALYZE; what we might not need to support, at
>> least initially, is every user of a global temp table having their own
>> SEPARATE copy of the table statistics.
>
> Yes, we are. Global Temp Tables won't solve the "Works on HS" problem,
> so we'd better decide fairly quickly which use case we are addressing,
> and why. ISTM Global Temp Tables is more an Oracle compatibility issue
> than a problem PostgreSQL users have.
>
> ...I have zero basis for deciding whether what you say about Global
> Temp Tables is useful or not.

Well, Noah presented a pretty good outline of how to make global temp
tables work under Hot Standby.  As Noah already said, making regular
temporary tables work under Hot Standby is far more difficult.  I
think he's right.  I'd rather see us get global temp tables working
under HS than insist we have to have regular temp tables working under
HS and ultimately end up with nothing.  Even getting global temp
tables working under HS is probably going to require an entire
development cycle, maybe two.  So raising the bar still higher seems
rather self-defeating to me.  Half a loaf is better than none.

In the interest of full disclosure, I freely admit that global
temporary tables would also be a neat Oracle compatibility feature,
and I do work for a company that sells Oracle compatibility products
based on PostgreSQL, so there are surely some reasons for me to like
that, but AFAICT they aren't all *that* heavily used by most Oracle
users either, which is why I haven't been able to justify doing this
project before now.  The important point here as I see it is that
tables of any flavor require catalog entries, and creating and
destroying catalog entries on a standby server does not seem
tractable, so if we want to have writable tables of any flavor on Hot
Standby sometime in the next year or two, we should pick a design that
doesn't require that.  What Noah has proposed seems to me to be by far
the simplest way of making that happen, so I think his design is
spot-on.

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

  15. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-25T17:31:45Z

    On Wed, Apr 25, 2012 at 5:53 PM, Robert Haas <robertmhaas@gmail.com> wrote:

>> Yes, we are. Global Temp Tables won't solve the "Works on HS" problem,
>> so we'd better decide fairly quickly which use case we are addressing,
>> and why. ISTM Global Temp Tables is more an Oracle compatibility issue
>> than a problem PostgreSQL users have.
>>
> Well, Noah presented a pretty good outline of how to make global temp
> tables work under Hot Standby.  As Noah already said, making regular
> temporary tables work under Hot Standby is far more difficult.  I
> think he's right.  I'd rather see us get global temp tables working
> under HS than insist we have to have regular temp tables working under
> HS and ultimately end up with nothing.  Even getting global temp
> tables working under HS is probably going to require an entire
> development cycle, maybe two.  So raising the bar still higher seems
> rather self-defeating to me.  Half a loaf is better than none.
...
> What Noah has proposed seems to me to be by far
> the simplest way of making that happen, so I think his design is
> spot-on.

Noah's design is spot-on for Global Temp tables, I agree. I have no
objection at all to an implementation of GTTs.

However, it is a fallacy that this is a good solution for using temp
tables on HS. I think the wish to enhance Oracle compatibility is
making some wishful thinking happen with regard to how useful this is
going to be. We need to spend just as much time considering the
utility of our work as we do spending time on the quality of the
implementation, otherwise its just well-implemented shelfware.

I don't think implementing temp tables on HS is more complex than this
proposal, its just orthogonal. There are some common aspects, such as
making local xids work, but that is only needed for a small fraction
of the normal temp table case. So it appears to me that GTTs as
proposed are actually harder to implement and not a stepping stone in
a linear sense. So you could equally argue that the requirement to
bring GTTs into the picture also risks us getting nothing and that the
"half a loaf" idea means GTTs should be excluded. Certainly, trying to
do two orthogonal tasks at once puts both at risk.

So I don't accept the proposition that "GTTs are a useful
implementation route for temp tables on HS", unless we're talking
about a "scenic route".

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  16. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-25T18:34:43Z

    On Wed, Apr 25, 2012 at 1:31 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> However, it is a fallacy that this is a good solution for using temp
> tables on HS. I think the wish to enhance Oracle compatibility is
> making some wishful thinking happen with regard to how useful this is
> going to be. We need to spend just as much time considering the
> utility of our work as we do spending time on the quality of the
> implementation, otherwise its just well-implemented shelfware.

Well, like I say, if you want to use locally-defined temp tables on
HS, you have to somehow solve the problem of catalog entries, and
nothing in your email looks like a proposal for how to do that.  I've
come up with one design, which I sketched in my original response, but
it relies on creating some new system catalogs that are themselves
GTTs, and it's also hideously complicated.  If you or anyone else can
come up with a better design, great, but so far no one has.

It's no skin off my neck if this project gets done in way that
bypasses the need for GTTs; I just don't have a credible proposal for
how to do that, and Noah stated that he doesn't either.

I do agree that what Noah's proposing to implement is shooting at a
pretty narrow target, but I don't think it's so narrow that we
wouldn't commit it if he's willing to do the work to implement it.
All of the infrastructure that he's proposing to create seems to me to
have plausible other uses, so even if the immediate feature doesn't
bring a lot of benefit there's every reason to suppose that it will
pave the way for further improvements down the line.

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

  17. Re: Temporary tables under hot standby

    Merlin Moncure <mmoncure@gmail.com> — 2012-04-25T22:46:33Z

    On Wed, Apr 25, 2012 at 11:53 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 25, 2012 at 12:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>>
>>> Oh, we're talking about different things, and I'm slightly confused.
>>>
>>> Yes, we need to support ANALYZE; what we might not need to support, at
>>> least initially, is every user of a global temp table having their own
>>> SEPARATE copy of the table statistics.
>>
>> Yes, we are. Global Temp Tables won't solve the "Works on HS" problem,
>> so we'd better decide fairly quickly which use case we are addressing,
>> and why. ISTM Global Temp Tables is more an Oracle compatibility issue
>> than a problem PostgreSQL users have.
>>
>> ...I have zero basis for deciding whether what you say about Global
>> Temp Tables is useful or not.
>
> Well, Noah presented a pretty good outline of how to make global temp
> tables work under Hot Standby.  As Noah already said, making regular
> temporary tables work under Hot Standby is far more difficult.  I
> think he's right.  I'd rather see us get global temp tables working
> under HS than insist we have to have regular temp tables working under
> HS and ultimately end up with nothing.  Even getting global temp
> tables working under HS is probably going to require an entire
> development cycle, maybe two.  So raising the bar still higher seems
> rather self-defeating to me.  Half a loaf is better than none.
>
> In the interest of full disclosure, I freely admit that global
> temporary tables would also be a neat Oracle compatibility feature,
> and I do work for a company that sells Oracle compatibility products
> based on PostgreSQL, so there are surely some reasons for me to like
> that, but AFAICT they aren't all *that* heavily used by most Oracle
> users either, which is why I haven't been able to justify doing this
> project before now.

I don't know how GTT play inside the Oracle stack such that they
aren't super popular, but if they work in the standby they will
quickly become a killer feature.  IMNSHO it's annoying but acceptable
to be forced to define them into the permanent schema.  Lack of temp
tables on the standby is a popular question/complaint on irc and in
most cases the proposal would satisfactorily address the problem.

merlin

  18. Re: Temporary tables under hot standby

    Jaime Casanova <jaime@2ndquadrant.com> — 2012-04-25T23:15:19Z

    On Wed, Apr 25, 2012 at 5:46 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> I don't know how GTT play inside the Oracle stack such that they
> aren't super popular, but if they work in the standby they will
> quickly become a killer feature.  IMNSHO it's annoying but acceptable
> to be forced to define them into the permanent schema.  Lack of temp
> tables on the standby is a popular question/complaint on irc and in
> most cases the proposal would satisfactorily address the problem.
>

The problem with using GTT for this is, IMHO, that you need to know
what your temp table will look before hand.

I have seen applications that uses the same name (ie: temp1, t1, tt or
t_temp) for all or almost all temp tables and, of course, all those
have different structures.

I have seen also temp tables created dinamically based in a query
(which has more or less columns based on some criteria).

In any case, this means for being able to use GTT on HS for these
applications, the apps needs to be fixed to ensure all temp tables
have different names through the app, also you need to ensure that all
queries that create temp tables to have a fixed set of columns.

Finally, you will need to modify apps to remove all CREATE TEMP TABLE
because they already exists. And i have not mentioned the problem i
will have if i need different behaviour for ON COMMIT (oh! i just did)

so yes, you can workaround things to make this something usable to fix
the problem of "temp tables in HS" but is not transparent (unless you
come from oracle, most db's uses local temp tables just as postgres
does) and certainly is not an ideal solution... FWIW, no one that i
know will want to do those "fixes" in their app.

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

  19. Re: Temporary tables under hot standby

    Noah Misch <noah@leadboat.com> — 2012-04-26T02:04:11Z

    On Wed, Apr 25, 2012 at 10:10:31AM +0100, Simon Riggs wrote:
> So you are saying it is OK to not be able to *create* them on HS, just
> *use* pre-defined tables?

I estimated that much to cover a worthy portion of the need, yes.

> That's almost useless IMHO.

Based on the range of assessments spanning your "almost useless" to Merlin's
"killer feature", I gather that its utility is exceptionally site-specific.

  20. Re: Temporary tables under hot standby

    Noah Misch <noah@leadboat.com> — 2012-04-26T02:16:11Z

    On Wed, Apr 25, 2012 at 11:49:23AM -0400, Robert Haas wrote:
> On Tue, Apr 24, 2012 at 11:55 PM, Noah Misch <noah@leadboat.com> wrote:
> > Our temporary tables are cataloged and filled like permanent tables. ?This has
> > the major advantage of making most code operate on tables with minimal regard
> > for their relpersistence. ?It also brings disadvantages:
> >
> > 1. High catalog turnover in rapid create/drop workloads. ?Heavy temporary
> > ? table users often need to REINDEX relation-oriented catalogs. ?Hot standby
> > ? cannot assign OIDs or modify system catalogs at all.
> > 2. Consumption of permanent XIDs for DML on the table. ?This increases COMMIT
> > ? cost on the master and is a non-starter under hot standby.
> > 3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values
> > ? delay pg_clog truncation and can trigger a wraparound-prevention shutdown.
> > 4. sinval traffic from every CREATE TEMP TABLE et al.
> > 5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.
> > 6. We don't automatically drop temporary tables that existed at the point of a
> > ? crash, because they look much like permanent tables.
> 
> #6 is already fixed in 9.1.  Temporary tables now have names like
> tBACKENDID_RELFILENODE, and are cleaned up during start-up in exactly
> the same way you're propose to do it further down.

Ah, so it is.  That simplifies things a bit.

> Maybe this is a silly idea, but if you're thinking about creating a
> local XID space and a global XID space, it might be a good idea to
> also make allowance for an "unlogged" XID space - that is, an XID
> space that is global to all backends but need not survive crashes.
> This would potentially allow unlogged tables to be used in HS mode.  I
> would expect that you probably don't want to go as far as actually
> trying to make this work as part of your current project, but maybe as
> you're structuring the code it would be worth considering the
> possibility that we'll eventually want >2 XID spaces, either for this
> or other reasons.

Agreed.  Plenty of the details would change (located in shared memory,
locking, persisted on clean shutdown, etc.), so I'm not sure how much actual
code could remain in common.  If I encounter design decisions where one choice
seems to help cover this other use in the future, I'll keep it in mind.

> With respect to problem #5, I've been wondering if we couldn't just
> forget about taking AccessExclusiveLock when first creating a table
> (temporary or permanent).  Unless and until the transaction commits,
> nobody can see the catalog entry anyway, so nobody else is going to
> attempt to take a conflicting lock.  So why bother?  Maybe there's
> some reason here that's not obvious to me.  If not, it might be worth
> doing on general principle independent of this project.

Sounds safe, offhand.  I do suspect the cost of the lock is peanuts compared
to the cost of inserting catalog entries, though, so I wouldn't anticipate a
measurable improvement from that change in isolation.

> On that note, I had a thought in the pat that it might be possible to
> do solve problem #1 by using global temp tables as system catalogs -
> that is, for each type of system catalog that relates to table
> creation, you'd have a permanent catalog and a global temp catalog.
> So if someone wants to create a temporary table of the existing
> variety on the standby, you can make all the entries in the
> global-temp version of pg_class, pg_attribute, etc.  However, this
> seems extremely difficult to manage in general - there's a lot of code
> churn involved, and also possible temporary -> permanent dependencies;
> for example, the temporary table might have a pg_attrdef entry that
> needs to depend on a non-temporary pg_proc entry.  That's tricky to
> solve on the master and even trickier to solve in HS operation.  So
> I'm inclined to agree with you that it makes more sense to just aim to
> support global temp tables in HS mode, and if we want to beat our head
> against the brick wall of making regular temp tables work there
> eventually, that can be a later project.

Agreed.  I hadn't thought of that dependencies problem.  Interesting.

> > Past discussions have raised the issue of interaction between commands like
> > ALTER TABLE and sessions using the new-variety temporary table. ?As a first
> > cut, let's keep this simple and have ongoing use of the table block operations
> > requiring AccessExclusiveLock. ?Note that you can always just make a new
> > temporary table with a different name to deploy a change quickly. ?Implement
> > this with a heavyweight lock having a novel life cycle. ?When a session first
> > takes an ordinary relation lock on the table, acquire the longer-term lock and
> > schedule it for release on transaction abort. ?On TRUNCATE, schedule a release
> > on transaction commit. ?Of course, also release the lock at session end.
> 
> I'm not sure I believe this will work, but maybe I'm just not understanding it.

Did you have a specific doubt?  I did gloss over all the details, having not
worked them out yet.

> > For new-variety temporary tables, change file names from "relfilenode[_fork]"
> > to "refilenode[_fork].pid.localnode". ?During crash recovery, delete all files
> > conforming to that pattern for refilenodes of known temporary tables. ?This
> > also lets backends share pg_class.relfilenode. ?The "localnode" field would
> > distinguish multiple generations of a table across VACUUM FULL, CLUSTER, and
> > TRUNCATE. ?We could build on this strategy to safely resolve point 6 for the
> > existing kind of temporary table, but that entails enough other details to
> > probably not mix it into the same patch.
> 
> In lieu of including localnode in the filename, I would suggest that
> for a global temp table, we set relfilenode = 0, and let each backend
> assign one (and reassign new ones) whenever it feels like it.

Good call.

Thanks,
nm

  21. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-26T04:57:05Z

    On Wed, Apr 25, 2012 at 10:04 PM, Noah Misch <noah@leadboat.com> wrote:
> Based on the range of assessments spanning your "almost useless" to Merlin's
> "killer feature", I gather that its utility is exceptionally site-specific.

Well said, sir.

It might be worth taking a couple of steps backward, here.  Regardless
of whether one finds the feature as proposed "almost useless" or a
"killer feature", there's no denying that what you're proposing
amounts to three major development projects the net payoff of which
will be the ability to do an extremely limited amount of writing on
the standby server.  Why should we draw the line between GTTs and
LTTs?  What about unlogged tables?  What about, for that matter,
permanent tables?  What about other kinds of SQL objects, such as
functions?  It could be useful to create any of those things on the
standby, and it's going to be extremely difficult if not outright
impossible to make that work, because we're building it on top of a
replication architecture that is oriented around physical replication,
which means that any change that makes the standby anything other than
a byte-for-byte copy of the master is going to be difficult and, in
some sense, a kludge.  I would put this proposal in that category as
well, even though I find it a rather elegant and well-thought-out
kludge.

It is pretty obvious that we could get around all of these problems
easily if we instead did logical replication.  So why not just install
Slony, Bucardo, Londiste, or, if I may presume to toot my employer's
horn just slightly, xDB replication server?  If you use one of those
products, you can not only create temporary tables on your "standby"
servers, but also unlogged tables, permanent tables, and any other
sort of SQL object you might want.  You can also do partial
replication, replication between different major versions of
PostgreSQL, and replication between PostgreSQL and some other
database.  A given node can be the master for some tables and a slave
for other tables, allowing far more deployment flexibility than you
can get with streaming replication; and you have neither replication
conflicts nor the necessity of replicating bloat (and the exclusive
lock that you must take to remove the bloat) between machines.  You
can even do multi-master replication, with the right product choice.
Despite all that, people still love streaming replication, because
it's fast, administratively simple, and very reliable.

So, I can't help wonder if what we're really missing here is a
high-performance, log-based logical replication solution with good
core support.  I'm sure that there will be opposition to that idea
from a variety of people for a variety of reasons, and that is fine.
But I think we need to confront the fact that as long as we stick with
physical replication, any sort of write capability on slaves is going
to be a lot of work and offer only fairly marginal capabilities.  We
can either live with those limitations, or change our approach.
Either way is fine, but I think that hoping the limitations will go
away without a fundamental change in the architecture is just wishful
thinking.

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

  22. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-26T05:01:17Z

    On Wed, Apr 25, 2012 at 10:16 PM, Noah Misch <noah@leadboat.com> wrote:
>> > Past discussions have raised the issue of interaction between commands like
>> > ALTER TABLE and sessions using the new-variety temporary table. ?As a first
>> > cut, let's keep this simple and have ongoing use of the table block operations
>> > requiring AccessExclusiveLock. ?Note that you can always just make a new
>> > temporary table with a different name to deploy a change quickly. ?Implement
>> > this with a heavyweight lock having a novel life cycle. ?When a session first
>> > takes an ordinary relation lock on the table, acquire the longer-term lock and
>> > schedule it for release on transaction abort. ?On TRUNCATE, schedule a release
>> > on transaction commit. ?Of course, also release the lock at session end.
>>
>> I'm not sure I believe this will work, but maybe I'm just not understanding it.
>
> Did you have a specific doubt?  I did gloss over all the details, having not
> worked them out yet.

Not really.  I think the basic idea of keeping the lock for the
lifetime of the session is probably sound, modulo those details.  The
only problem I see is that it would prevent user A from clustering the
table while user B is selecting from the table, which is not a priori
necessary.  It might be useful to work out a solution to that problem
somehow, maybe just by jiggering the required lock levels for certain
operations - perhaps CLUSTER and VACUUM could run with just
RowExclusiveLock when run against a GTT, or something like that.

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

  23. Re: Temporary tables under hot standby

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2012-04-26T06:37:02Z

    On 25.04.2012 18:49, Robert Haas wrote:
> Maybe this is a silly idea, but if you're thinking about creating a
> local XID space and a global XID space, it might be a good idea to
> also make allowance for an "unlogged" XID space - that is, an XID
> space that is global to all backends but need not survive crashes.
> This would potentially allow unlogged tables to be used in HS mode.  I
> would expect that you probably don't want to go as far as actually
> trying to make this work as part of your current project, but maybe as
> you're structuring the code it would be worth considering the
> possibility that we'll eventually want>2 XID spaces, either for this
> or other reasons.

It would also be nice to have a separate XID space for each database in 
the cluster (plus one for global tables). Then one database consuming a 
lot of XIDs would not force anti-xid wraparound in other databases, and 
it would take us one step closer to being able to (physically) 
backup/restore/replicate a single database in a cluster.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

  24. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-26T07:56:40Z

    On Wed, Apr 25, 2012 at 7:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 25, 2012 at 1:31 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> However, it is a fallacy that this is a good solution for using temp
>> tables on HS. I think the wish to enhance Oracle compatibility is
>> making some wishful thinking happen with regard to how useful this is
>> going to be. We need to spend just as much time considering the
>> utility of our work as we do spending time on the quality of the
>> implementation, otherwise its just well-implemented shelfware.
>
> Well, like I say, if you want to use locally-defined temp tables on
> HS, you have to somehow solve the problem of catalog entries, and
> nothing in your email looks like a proposal for how to do that.  I've
> come up with one design, which I sketched in my original response, but
> it relies on creating some new system catalogs that are themselves
> GTTs, and it's also hideously complicated.  If you or anyone else can
> come up with a better design, great, but so far no one has.

Previous discussions had Tom proposing ways of extending catalogs to
allow exactly this. So designs that address that point are already on
record.

A full GTT implementation is not required and the design differed from
that. I don't think "hideously complicated" is accurate, that's just
you're way of saying "and I disagree". Either route is pretty complex
and not much to choose between them, apart from the usefulness of the
end product - GTTs are not that beneficial as a feature in themselves.

The current problems of our temp table approach are
1. Catalog bloat
2. Consumption of permanent XIDs for DML on temp tables.  This increases COMMIT
  cost on the master and is a non-starter under hot standby.
3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values
  delay pg_clog truncation and can trigger a wraparound-prevention shutdown.
4. sinval traffic from every CREATE TEMP TABLE et al.
5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.
6. We don't automatically drop temporary tables that existed at the point of a
  crash, because they look much like permanent tables.2. Cross-backend
access/security
7. Temp tables don't work on HS
8. No Global Temp tables

Implementing GTTs solves (8) and provides some useful tools to solve
other points. Note that GTTs do not themselves solve 1-7 in full,
hence my point that GTTs are an endpoint not a way station. The way
forwards is not to concentrate on GTTs but to provide a set of
facilities that allow all the more basic points 1-6 to be addressed,
in full and then solve (7) and (8).  If we pretend (8) solves (7) as
well, we will make mistakes in implementation that will waste time and
deliver reduced value.

In passing I note that GTTs are required to allow PostgresXC to
support temp tables, since they need a mechanism to makes a single
temp table definition work on multiple nodes with different data in
each.

Simply put, I don't think we should be emphasising things that are
needed for PostgresXC and EDB AS, but not that important for
PostgreSQL users.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  25. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-04-26T08:18:04Z

    On Thu, Apr 26, 2012 at 5:57 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 25, 2012 at 10:04 PM, Noah Misch <noah@leadboat.com> wrote:
>> Based on the range of assessments spanning your "almost useless" to Merlin's
>> "killer feature", I gather that its utility is exceptionally site-specific.
>
> Well said, sir.

Not well said. This is spurious. This is not a balanced point.

100% of all temp table use cases currently define them dynamically. If
we implement a feature that *requires* them to all suddenly be defined
statically, then we're requiring those applications to
Either 1)a rewrite *all* their existing custom code
1)b Don't work with external applications that don't use GTTs (who the
hell does?)
2) have problems if any of their code doesn't allow static definition

Implementing a feature that *requires* those things is madness and
obscuring those crucial points is not balanced or fair.

Merlin is right to say that having GTTs would be a step up from what
we have now, but then so would a different and actually useful
implementation. So GTTs alone are not a killer feature. The killer
feature comes from solving the problems of running temp tables on HS.
I accept GTTs are a feature, but certainly not a killer one.

If we are interested in well-principled software then we must address
*both* the utility and the quality of our implementation. We cannot
just shrug our way through the boring bit of feature design and then
agonise over a well principled implementation. We must recognise that
our wish to get on with the interesting bit gets in the way of
spending sensible time balancing what we do to get the most out of our
work. Noah will enjoy writing this; I just want to make sure he is
enjoying something that will be useful, not just a tick box on the
release notes that will cause further frustration when users find out
the details.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  26. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-26T12:14:04Z

    On Thu, Apr 26, 2012 at 3:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Previous discussions had Tom proposing ways of extending catalogs to
> allow exactly this. So designs that address that point are already on
> record.

Link?

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

  27. Re: Temporary tables under hot standby

    Greg Stark <stark@mit.edu> — 2012-04-26T13:57:59Z

    On Thu, Apr 26, 2012 at 9:18 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Implementing a feature that *requires* those things is madness and
> obscuring those crucial points is not balanced or fair.

I think this whole discussion started the wrong way around. If the
goal of implementing GTTs is to solve a need with replication then
yes, it's kind of weird. But GTTs solve lots of problems with our
existing implementation of temporary tables. Our existing temporary
tables are really normal tables that just get cleaned up automatically
but incur almost all the overhead of a real table including needless
heavyweight DDL in your OLTP application. It's a bad design and
providing GTTs would be providing a nice feature that allows people to
implement much better systems. As a side benefit they would be easy to
support on a standby as well which would be a pretty nice feature.

I do think storing local xids is a bit scary. It might be a dead-end
if we ever want to support having persistent non-local objects in the
standby database. We'll need some way to generate a separate xid space
(along with other feature we don't need for GTTs of course) in that
case and then the ability to store local xids and check snapshots
against them would be kind of useless once we have that.

-- 
greg

  28. Re: Temporary tables under hot standby

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-04-26T14:12:01Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Apr 26, 2012 at 3:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> Previous discussions had Tom proposing ways of extending catalogs to
>> allow exactly this. So designs that address that point are already on
>> record.

> Link?

There was a thread a couple years ago ...
http://archives.postgresql.org/pgsql-hackers/2010-10/msg01203.php
particularly here:
http://archives.postgresql.org/pgsql-hackers/2010-10/msg01448.php

But I have a feeling that that idea is much older.

			regards, tom lane

  29. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-26T14:12:02Z

    On Thu, Apr 26, 2012 at 9:57 AM, Greg Stark <stark@mit.edu> wrote:
> On Thu, Apr 26, 2012 at 9:18 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> Implementing a feature that *requires* those things is madness and
>> obscuring those crucial points is not balanced or fair.
>
> I think this whole discussion started the wrong way around. If the
> goal of implementing GTTs is to solve a need with replication then
> yes, it's kind of weird. But GTTs solve lots of problems with our
> existing implementation of temporary tables. Our existing temporary
> tables are really normal tables that just get cleaned up automatically
> but incur almost all the overhead of a real table including needless
> heavyweight DDL in your OLTP application. It's a bad design and
> providing GTTs would be providing a nice feature that allows people to
> implement much better systems. As a side benefit they would be easy to
> support on a standby as well which would be a pretty nice feature.

+1 to all that.

> I do think storing local xids is a bit scary. It might be a dead-end
> if we ever want to support having persistent non-local objects in the
> standby database. We'll need some way to generate a separate xid space
> (along with other feature we don't need for GTTs of course) in that
> case and then the ability to store local xids and check snapshots
> against them would be kind of useless once we have that.

Yeah, I think the separate XID space thing is pretty complicated.  On
the other hand, there are lots of reasons to think it would be cool if
Noah can pull it off, and I think his chances are better than most.
Aside from the fact that it would improve temp tables, there's the
possibility of doing an XID space per database, as Heikki mentioned,
and the possibility of having an unlogged XID space also, as I
mentioned.  Whether any of these things are feasible and which ones
actually make sense is not totally clear, but I think it's completely
worthwhile for someone to do the legwork to figure it out.

I also think that the idea of having persistent non-local objects on a
Hot Standby database is likely a complete dead-end.  By the time
you've managed to engineer that, it's not going to look much like Hot
Standby as we know it today any more.  Perhaps we will get there
eventually, but I think logical replication is a more likely bet.

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

  30. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-04-26T14:22:03Z

    On Thu, Apr 26, 2012 at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Apr 26, 2012 at 3:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>> Previous discussions had Tom proposing ways of extending catalogs to
>>> allow exactly this. So designs that address that point are already on
>>> record.
>
>> Link?
>
> There was a thread a couple years ago ...
> http://archives.postgresql.org/pgsql-hackers/2010-10/msg01203.php
> particularly here:
> http://archives.postgresql.org/pgsql-hackers/2010-10/msg01448.php
>
> But I have a feeling that that idea is much older.

Yeah, so, basically this is what I already proposed upthread: put the
catalog entries for your temporary tables in system catalogs that are
themselves temporary tables.  One difference is that you suggested
this could be done without GTTs by using LTTs that insert
bootstrapping entries describing themselves when they are initialized
- so you never need to modify the "real" pg_class, etc.  I hadn't
thought of that, and it might be feasible.  I have my doubts about
whether it's the path of least resistance because, as you point out
(and as Noah pointed out, and as I pointed out) any kind of change
that involves having moving some system catalog entries into a
separate set of temporary catalogs is likely to be extremely difficult
for many reasons.  Noah's design is somewhat less powerful, but it
avoids getting sucked down that rathole.

In addition to the already-mentioned issue of temp->perm dependencies,
any such design would break the assumption that every table is
identified by a unique OID, which seems likely to break a whole lot of
things.

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

  31. Re: Temporary tables under hot standby

    Noah Misch <noah@leadboat.com> — 2012-04-29T20:02:58Z

    On Tue, Apr 24, 2012 at 11:55:15PM -0400, Noah Misch wrote:
> Concerning everyone's favorite topic, how to name the new type of table, I
> liked Tom's proposal[1] to make CREATE TEMP TABLE retain current behavior and
> have CREATE GLOBAL TEMP TABLE and/or CREATE LOCAL TEMP TABLE request the new
> SQL-standard variety.  (I'd vote for using CREATE GLOBAL and retaining CREATE
> LOCAL for future expansion.)  As he mentions, to get there, we'd ideally start
> by producing a warning instead of silently accepting GLOBAL as a noise word.
> Should we put such a warning into 9.2?

Here is the change I'd make.

Thanks,
nm

> [1] http://archives.postgresql.org/message-id/5422.1240936705@sss.pgh.pa.us

  32. Re: Temporary tables under hot standby

    Jim Nasby <jim@nasby.net> — 2012-05-01T20:12:20Z

    On 4/25/12 6:16 AM, Simon Riggs wrote:
>> The way standard-like temporary tables work is exactly why I assume
>> >  Noah proposes to implement them: because they work nicely with HS.

> Well, following a standard that no other major DBMS has followed is
> not great, especially if it leads to a non-useful feature.

Actually, Oracle's temp tables are standard-compliant. You must explicitly define them via DDL and they remain in the catalog.
-- 
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

  33. Re: Temporary tables under hot standby

    Jim Nasby <jim@nasby.net> — 2012-05-01T20:22:32Z

    On 4/25/12 6:15 PM, Jaime Casanova wrote:
> On Wed, Apr 25, 2012 at 5:46 PM, Merlin Moncure<mmoncure@gmail.com>  wrote:
>> >
>> >  I don't know how GTT play inside the Oracle stack such that they
>> >  aren't super popular, but if they work in the standby they will
>> >  quickly become a killer feature.  IMNSHO it's annoying but acceptable
>> >  to be forced to define them into the permanent schema.  Lack of temp
>> >  tables on the standby is a popular question/complaint on irc and in
>> >  most cases the proposal would satisfactorily address the problem.
>> >
> The problem with using GTT for this is, IMHO, that you need to know
> what your temp table will look before hand.
>
> I have seen applications that uses the same name (ie: temp1, t1, tt or
> t_temp) for all or almost all temp tables and, of course, all those
> have different structures.

Not to pick on Jaime, but this is the 2nd or 3rd comment I've seen about applications using temp tables.

How many of these applications are actually trying to function on a read-only slave?

ISTM that an *application* would likely not need to modify it's use of temp tables (unless we actually rip out support for how they currently work, but I don't think that's on the table).

Now, reporting may well be a different story... we can't use streaming replication at work because of the lack of temp tables, but GTT's would not really solve our problem either. That said, the places in our application where we use temp tables it would actually be easier for us to use GTT's rather than LTT's.
-- 
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

  34. Re: Temporary tables under hot standby

    Bruce Momjian <bruce@momjian.us> — 2012-05-02T15:14:27Z

    On Thu, Apr 26, 2012 at 08:56:40AM +0100, Simon Riggs wrote:
> A full GTT implementation is not required and the design differed from
> that. I don't think "hideously complicated" is accurate, that's just
> you're way of saying "and I disagree". Either route is pretty complex
> and not much to choose between them, apart from the usefulness of the
> end product - GTTs are not that beneficial as a feature in themselves.
> 
> The current problems of our temp table approach are
> 1. Catalog bloat
> 2. Consumption of permanent XIDs for DML on temp tables.  This increases COMMIT
>   cost on the master and is a non-starter under hot standby.
> 3. autovacuum cannot VACUUM temporary tables, but their relfrozenxid values
>   delay pg_clog truncation and can trigger a wraparound-prevention shutdown.
> 4. sinval traffic from every CREATE TEMP TABLE et al.
> 5. CREATE TEMP TABLE records AccessExclusiveLock in the global lock table.
> 6. We don't automatically drop temporary tables that existed at the point of a
>   crash, because they look much like permanent tables.2. Cross-backend
> access/security
> 7. Temp tables don't work on HS
> 8. No Global Temp tables
> 
> Implementing GTTs solves (8) and provides some useful tools to solve
> other points. Note that GTTs do not themselves solve 1-7 in full,
> hence my point that GTTs are an endpoint not a way station. The way
> forwards is not to concentrate on GTTs but to provide a set of
> facilities that allow all the more basic points 1-6 to be addressed,
> in full and then solve (7) and (8).  If we pretend (8) solves (7) as
> well, we will make mistakes in implementation that will waste time and
> deliver reduced value.
> 
> In passing I note that GTTs are required to allow PostgresXC to
> support temp tables, since they need a mechanism to makes a single
> temp table definition work on multiple nodes with different data in
> each.
> 
> Simply put, I don't think we should be emphasising things that are
> needed for PostgresXC and EDB AS, but not that important for
> PostgreSQL users.

I think if implementing global temporary tables only for hot standby
user (#7), it might be of limited usefulness, but the ability to avoid
system table churn (#1) means global temporary tables would have a wide
usefulness, even without hot standby use.  The idea of sharing optimizer
statistics also has a lot of merit.  

FYI, global temp tables have been on the TODO list long before EDB
employment or PostgresXC.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  35. Re: Temporary tables under hot standby

    Michael Nolan <htfoot@gmail.com> — 2012-05-02T15:39:21Z

    What is the use case for temporary tables on a hot standby server?

Perhaps this is a noobie question, but it seems to me that a hot standby
server's use by* applications* or *users* should be limited to transactions
that don't alter the database in any form.

However, I can see where temporary tables might be needed at the system
level (if not already available) in order to prepare more efficient plans
for some complex read-only queries.
--
Mike Nolan

  36. Re: Temporary tables under hot standby

    Christopher Browne <cbbrowne@gmail.com> — 2012-05-02T15:53:58Z

    On Wed, May 2, 2012 at 11:39 AM, Michael Nolan <htfoot@gmail.com> wrote:
> What is the use case for temporary tables on a hot standby server?

Simple...

We required a "hot standby" server in order to get improved reliability.

But we don't want it to sit there chewing power + money, unused.

We want to *use* it to support our reporting applications.

And the developers used temporary tables to marshal results used in
some of those reports.

There are conflicting senses of "read-only" here...
  - In one strict sense, to generate tuples in temp tables means it's
not "read only" access.
  - But since the users running reports aren't allowed to modify the
data in the application tables that they are querying, how is that
*not* fairly characterized as "read only" access???
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

  37. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-05-02T16:15:38Z

    On Wed, May 2, 2012 at 4:14 PM, Bruce Momjian <bruce@momjian.us> wrote:

> I think if implementing global temporary tables only for hot standby
> user (#7), it might be of limited usefulness, but the ability to avoid
> system table churn (#1) means global temporary tables would have a wide
> usefulness, even without hot standby use.

Yes, many parts of Noah's proposal would be useful for normal running.
And as Jim points out, they are SQL Standard, as well as supported by
Oracle and DB2, so again GTTs would hit that TODO item.

And Noah's proposal does take us more than 50%, maybe 80% of the way
to what I think would be most useful for HS.

So overall, I do encourage the proposal.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  38. Re: Temporary tables under hot standby

    Josh Berkus <josh@agliodbs.com> — 2012-05-03T00:57:19Z

    Michael,

> What is the use case for temporary tables on a hot standby server?
> 
> Perhaps this is a noobie question, but it seems to me that a hot standby
> server's use by* applications* or *users* should be limited to transactions
> that don't alter the database in any form.

A very common use for asynchronous replicas is to offload long-running
reporting jobs onto the replica so that they don't bog down the master.
 However, long-running reporting jobs often require temporary tables,
especially if they use some 3rd-party vendor's reporting tool.  For
example, the average Microstrategy report involves between 1 and 12
temporary tables.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

  39. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-05-03T09:11:56Z

    On Thu, May 3, 2012 at 1:57 AM, Josh Berkus <josh@agliodbs.com> wrote:
> Michael,
>
>> What is the use case for temporary tables on a hot standby server?
>>
>> Perhaps this is a noobie question, but it seems to me that a hot standby
>> server's use by* applications* or *users* should be limited to transactions
>> that don't alter the database in any form.
>
> A very common use for asynchronous replicas is to offload long-running
> reporting jobs onto the replica so that they don't bog down the master.
>  However, long-running reporting jobs often require temporary tables,
> especially if they use some 3rd-party vendor's reporting tool.  For
> example, the average Microstrategy report involves between 1 and 12
> temporary tables.

Many tools and applications choose to use temporary tables. Often this
isn't necessary at all, for example in MicroStrategy it is possible to
ask it to use derived tables instead and thus avoid using temp tables,
so that can still work against Hot Standby.

Derived tables means rewriting the query from
CREATE TEMP TABLE s1 AS <SELECT1>;
SELECT ... FROM s1 WHERE ...

into
SELECT ... FROM (<SELECT1>) AS s1 WHERE

Many apps are easily rewritten in this way and so the lack of temp
tables isn't a total blocker in the way some people think.

If we had Global Temp Tables, users would still need to rewrite their
code, just in a different way, like this...
(on master)
CREATE GLOBAL TEMP TABLE s1 (....);

(on standby)
INSERT INTO s1 <SELECT1>;
SELECT ... FROM s1 WHERE ...
which seems to me to be actually harder than just rewriting as derived
table and isn't an option on Microstrategy etc, hence my observation
that GTTs don't help HS much. What I would like to see, one day, is
for temp tables to work without any changes.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  40. Re: Temporary tables under hot standby

    Merlin Moncure <mmoncure@gmail.com> — 2012-05-03T13:19:01Z

    On Thu, May 3, 2012 at 4:11 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> which seems to me to be actually harder than just rewriting as derived
> table and isn't an option on Microstrategy etc, hence my observation
> that GTTs don't help HS much. What I would like to see, one day, is
> for temp tables to work without any changes.

yes. except (global) temp tables can:
*) be updated with data not derived from permanent tables (think:
session management, etc)
*) outlive a single transaction
*) be indexed independently of the sourcing tables
*) be referred to from multiple queries (you can simulate this with
CTE, but that approach obviously has limits)

of course, temp tables that would 'just work' would be wonderful.

merlin

  41. Re: Temporary tables under hot standby

    Josh Berkus <josh@agliodbs.com> — 2012-05-03T19:06:30Z

    > (on standby)
> INSERT INTO s1 <SELECT1>;
> SELECT ... FROM s1 WHERE ...
> which seems to me to be actually harder than just rewriting as derived
> table and isn't an option on Microstrategy etc, hence my observation
> that GTTs don't help HS much. What I would like to see, one day, is
> for temp tables to work without any changes.

100% agreement.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

  42. Re: Temporary tables under hot standby

    Merlin Moncure <mmoncure@gmail.com> — 2012-05-07T14:04:28Z

    On Tue, Apr 24, 2012 at 10:55 PM, Noah Misch <noah@leadboat.com> wrote:
> A key barrier to migrations from trigger-based replication to WAL-based
> replication is the lack of temporary tables under hot standby.  I'd like to
> close that gap; the changes needed will also reduce the master-side cost of
> temporary table usage.  Here is a high-level design for your advice and
> comments.  Much of this builds on ideas from past postings noted below.

On Mon, May 7, 2012 at 8:52 AM, Michael Nolan <htfoot@gmail.com> wrote:
> To cross-pollinate with another thread, if temporary tables (and
> insert/delete/update transactions to them) are to be supported on a slave,
> will the applications using those temporary tables expect to be able to use
> 'nextval' on inserts to temporary tables as well?

That's a very good question.  I'm moving it  -hackers...how do non
table temporary objects work?  Do you have CREATE GLOBAL TEMPORARY
SEQUENCE?  etc.

My understanding of the current proposal is that the sequence (along
with the rest of the table) has to be defined in the master as a
global temporary table. It seems that it wouldn't be possible or
desirable to serialize sequence fetches between the master and standby
servers, but I'm not sure about that.

merlin

  43. Re: Temporary tables under hot standby

    Noah Misch <noah@leadboat.com> — 2012-05-08T09:01:05Z

    On Mon, May 07, 2012 at 09:04:28AM -0500, Merlin Moncure wrote:
> On Mon, May 7, 2012 at 8:52 AM, Michael Nolan <htfoot@gmail.com> wrote:
> > To cross-pollinate with another thread, if temporary tables (and
> > insert/delete/update transactions to them) are to be supported on a slave,
> > will the applications using those temporary tables expect to be able to use
> > 'nextval' on inserts to temporary tables as well?
> 
> That's a very good question.  I'm moving it  -hackers...how do non
> table temporary objects work?  Do you have CREATE GLOBAL TEMPORARY
> SEQUENCE?  etc.

Probably so, yes.

> My understanding of the current proposal is that the sequence (along
> with the rest of the table) has to be defined in the master as a
> global temporary table.

Yes.

> It seems that it wouldn't be possible or
> desirable to serialize sequence fetches between the master and standby
> servers, but I'm not sure about that.

Like global temporary tables, each session has an independent copy of each
global temporary sequence.  "nextval" would have no interaction with other
local backends, let alone remote ones.

  44. Re: Temporary tables under hot standby

    Robert Haas <robertmhaas@gmail.com> — 2012-06-08T17:26:20Z

    On Sun, Apr 29, 2012 at 4:02 PM, Noah Misch <noah@leadboat.com> wrote:
> On Tue, Apr 24, 2012 at 11:55:15PM -0400, Noah Misch wrote:
>> Concerning everyone's favorite topic, how to name the new type of table, I
>> liked Tom's proposal[1] to make CREATE TEMP TABLE retain current behavior and
>> have CREATE GLOBAL TEMP TABLE and/or CREATE LOCAL TEMP TABLE request the new
>> SQL-standard variety.  (I'd vote for using CREATE GLOBAL and retaining CREATE
>> LOCAL for future expansion.)  As he mentions, to get there, we'd ideally start
>> by producing a warning instead of silently accepting GLOBAL as a noise word.
>> Should we put such a warning into 9.2?
>
> Here is the change I'd make.

This is listed on the open items list.

I haven't ever heard anyone propose to redefine CREATE LOCAL TEMP
TABLE to mean anything different than CREATE TEMP TABLE, so I'm
disinclined to warn about that.

I would be more open to warning people about CREATE GLOBAL TEMP TABLE
- frankly, it's pretty wonky that we allow that but treat GLOBAL as a
noise word in this first place.  But I'm a little disinclined to have
the message speculate about what might happen in future versions of
PostgreSQL.  Such predictions don't have a very good track record of
being accurate.

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

  45. Re: Temporary tables under hot standby

    Simon Riggs <simon@2ndquadrant.com> — 2012-06-08T17:38:19Z

    On 8 June 2012 18:26, Robert Haas <robertmhaas@gmail.com> wrote:

> I would be more open to warning people about CREATE GLOBAL TEMP TABLE
> - frankly, it's pretty wonky that we allow that but treat GLOBAL as a
> noise word in this first place.  But I'm a little disinclined to have
> the message speculate about what might happen in future versions of
> PostgreSQL.  Such predictions don't have a very good track record of
> being accurate.

Agreed.

We should make use of GLOBAL throw an ERROR: feature not yet
implemented, in preparation for what might one day happen. We don't
know the future but we do know the present.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  46. Re: Temporary tables under hot standby

    Kevin Grittner <kevin.grittner@wicourts.gov> — 2012-06-08T18:05:24Z

    Simon Riggs <simon@2ndQuadrant.com> wrote:
> On 8 June 2012 18:26, Robert Haas <robertmhaas@gmail.com> wrote:
> 
>> I would be more open to warning people about CREATE GLOBAL TEMP
>> TABLE - frankly, it's pretty wonky that we allow that but treat
>> GLOBAL as a noise word in this first place.  But I'm a little
>> disinclined to have the message speculate about what might happen
>> in future versions of PostgreSQL.  Such predictions don't have a
>> very good track record of being accurate.
> 
> Agreed.
> 
> We should make use of GLOBAL throw an ERROR: feature not yet
> implemented, in preparation for what might one day happen. We
> don't know the future but we do know the present.
 
+1
 
It has always bothered me that we support GLOBAL there without
coming anywhere near matching the semantics of GTTs.
 
-Kevin

  47. Re: Temporary tables under hot standby

    Noah Misch <noah@leadboat.com> — 2012-06-10T17:24:31Z

    On Fri, Jun 08, 2012 at 01:26:20PM -0400, Robert Haas wrote:
> On Sun, Apr 29, 2012 at 4:02 PM, Noah Misch <noah@leadboat.com> wrote:
> > On Tue, Apr 24, 2012 at 11:55:15PM -0400, Noah Misch wrote:
> >> Concerning everyone's favorite topic, how to name the new type of table, I
> >> liked Tom's proposal[1] to make CREATE TEMP TABLE retain current behavior and
> >> have CREATE GLOBAL TEMP TABLE and/or CREATE LOCAL TEMP TABLE request the new
> >> SQL-standard variety. ?(I'd vote for using CREATE GLOBAL and retaining CREATE
> >> LOCAL for future expansion.) ?As he mentions, to get there, we'd ideally start
> >> by producing a warning instead of silently accepting GLOBAL as a noise word.
> >> Should we put such a warning into 9.2?
> >
> > Here is the change I'd make.
> 
> This is listed on the open items list.
> 
> I haven't ever heard anyone propose to redefine CREATE LOCAL TEMP
> TABLE to mean anything different than CREATE TEMP TABLE, so I'm
> disinclined to warn about that.

From a documentation perspective, it will be awkward to explain (or decline to
explain) that both GLOBAL TEMPORARY and LOCAL TEMPORARY are standard syntaxes
with non-standard behavior, only one of which emits a warning.  That unduly
telegraphs a prediction about which one will change first/ever.  Maybe that's
nonetheless the right pragmatic answer.

> I would be more open to warning people about CREATE GLOBAL TEMP TABLE
> - frankly, it's pretty wonky that we allow that but treat GLOBAL as a
> noise word in this first place.  But I'm a little disinclined to have
> the message speculate about what might happen in future versions of
> PostgreSQL.  Such predictions don't have a very good track record of
> being accurate.

I feel the predictions in question ("This may specify different semantics in
future versions of PostgreSQL." and "This usage is deprecated and may specify
standard-compliant behavior in the future.") were broad enough to mitigate
this concern.  If we ever do change the interpretation of this syntax, to what
could it be other than the standard behavior?  We're not likely to introduce a
different but still-nonstandard behavior for this standard syntax.

I wrote the verbiage that way for the benefit of users encountering the new
warning.  They might reasonably ask, "Why did the PostgreSQL developers create
this work for me?"  No objection to removing the errhint, but I think the
documentation wording should stay.


Concerning whether to make this a WARNING or an ERROR, does anyone still
object to WARNING?

Thanks,
nm

  48. Re: Temporary tables under hot standby

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-06-10T20:48:39Z

    Noah Misch <noah@leadboat.com> writes:
> On Fri, Jun 08, 2012 at 01:26:20PM -0400, Robert Haas wrote:
>> I haven't ever heard anyone propose to redefine CREATE LOCAL TEMP
>> TABLE to mean anything different than CREATE TEMP TABLE, so I'm
>> disinclined to warn about that.

> From a documentation perspective, it will be awkward to explain (or decline to
> explain) that both GLOBAL TEMPORARY and LOCAL TEMPORARY are standard syntaxes
> with non-standard behavior, only one of which emits a warning.

Yeah.  If we're going to touch this at all, I think we should warn about
both, because they are both being interpreted in a non-standards-compliant
fashion.  It's possible that different message texts would be
appropriate, though.

If we create the infrastructure necessary to make GLOBAL TEMP
standards-compliant, it would not be totally unreasonable (IMO) to make
LOCAL TEMP act like GLOBAL TEMP.  It would still be non-compliant, but
closer than it is today.  Moreover, if you argue that the whole session
is one SQL module, it could actually be seen as compliant, in a subsetty
kind of way.  (Or so I think; but I've not read the relevant parts of
the spec very recently either.)

			regards, tom lane