Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 4:55 AM, Noah Misch n...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 4:55 AM, Noah Misch n...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
2012/4/25 Simon Riggs si...@2ndquadrant.com: On Wed, Apr 25, 2012 at 4:55 AM, Noah Misch n...@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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 9:37 AM, Nicolas Barbier nicolas.barb...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[v9.3] Extra Daemons (Re: [HACKERS] elegant and effective way for running jobs inside a database)
2012/3/10 Simon Riggs si...@2ndquadrant.com: On Fri, Mar 9, 2012 at 6:51 PM, Andrew Dunstan and...@dunslane.net wrote: On 03/09/2012 01:40 PM, Robert Haas wrote: On Fri, Mar 9, 2012 at 12:02 PM, David E. Wheelerda...@justatheory.com wrote: On Mar 9, 2012, at 7:55 AM, Merlin Moncure wrote: 100% agree (having re-read the thread and Alvaro's idea having sunk in). Being able to set up daemon processes side by side with the postmaster would fit the bill nicely. It's pretty interesting to think of all the places you could go with it. pgAgent could use it *right now*. I keep forgetting to restart it after restarting PostgreSQL and finding after a day or so that no jobs have run. That can and should be fixed by teaching pgAgent that failing to connect to the server, or getting disconnected, is not a fatal error, but a reason to sleep and retry. Yeah. It's still not entirely clear to me what a postmaster-controlled daemon is going to be able to do that an external daemon can't. Start and stop at the same time as postmaster, without any pain. It's a considerable convenience to be able to design this aspect once and then have all things linked to the postmaster follow that. It means people will be able to write code that runs on all OS easily, without everybody having similar but slightly different code about starting up, reading parameters, following security rules etc.. Tight integration, with good usability. I tried to implement a patch according to the idea. It allows extensions to register an entry point of the self-managed daemon processes, then postmaster start and stop them according to the normal manner. [kaigai@iwashi patch]$ ps ax | grep postgres 27784 pts/0S 0:00 /usr/local/pgsql/bin/postgres 27786 ?Ss 0:00 postgres: writer process 27787 ?Ss 0:00 postgres: checkpointer process 27788 ?Ss 0:00 postgres: wal writer process 27789 ?Ss 0:00 postgres: autovacuum launcher process 27790 ?Ss 0:00 postgres: stats collector process 27791 ?Ss 0:00 postgres: auth_counter == (*) The auth_counter being included in this patch is just an example of this functionality. It does not have significant meanings. It just logs number of authentication success and fails every intervals. I'm motivated to define an extra daemon that attach shared memory segment of PostgreSQL as a computing server to avoid limitation of number of GPU code that we can load concurrently. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp pgsql-v9.3-extra-daemon.v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [v9.3] Extra Daemons (Re: [HACKERS] elegant and effective way for running jobs inside a database)
On Wed, Apr 25, 2012 at 10:40 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I tried to implement a patch according to the idea. It allows extensions to register an entry point of the self-managed daemon processes, then postmaster start and stop them according to the normal manner. I've got a provisional version of this as well, that I was expecting to submit for 9.3CF1 Best thing is probably to catch up at PGCon on this, so we can merge the proposals and code. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
2012/4/25 Simon Riggs si...@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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
2012/4/25 Nicolas Barbier nicolas.barb...@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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 11:08 AM, Nicolas Barbier nicolas.barb...@gmail.com wrote: 2012/4/25 Simon Riggs si...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.3: summary of corruption detection / checksums / CRCs discussion
On Tue, Apr 24, 2012 at 3:40 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Apr 21, 2012 at 7:08 PM, Greg Stark st...@mit.edu wrote: The earlier consensus was to move all the hint bits to a dedicated area and exclude them from the checksum. I think double-write buffers seem to have become more fashionable but a summary that doesn't describe the former is definitely incomplete. I don't think we ever had any consensus that moving the hint bits around was a good idea. For one thing, they are only hints in one direction. It's OK to clear them by accident, but it's not OK to set them by accident. For two things, it's not exactly clear how we'd rearrange the page to make this work at all: where are those hint bits gonna go, if not in the tuple headers? For three things, index pages have hint-type changes that are not single-bit changes. That link points to the MVCC-safe truncate patch. I don't follow how optimizations in bulk loads are relevant to wal logging hint bit updates. That patch actually has more than one optimization in it, I think, but the basic idea is that if we could figure out a way to set HEAP_XMIN_COMMITTED when loading data into a table created or truncated within the same transaction, the need to set hint bits on first scan of the table would be eliminated. Writing the xmin as FrozenTransactionId would save even more, though it introduces some additional complexity. This would be great but it's only a corner case. A pretty common application flow is to write a large number of records, scan them, update them, scan them again, delete them, etc. in a table that's already established and possibly pretty large. Unfortunately this type of work doesn't get a lot of coverage with the common benchmarks. Also, wouldn't the extra out of band wal traffic from hint bits exacerbate contention issues on the wal insert lock? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Tue, Apr 24, 2012 at 11:55 PM, Noah Misch n...@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
Re: [HACKERS] 9.3: summary of corruption detection / checksums / CRCs discussion
On Tue, Apr 24, 2012 at 8:52 PM, Greg Stark st...@mit.edu wrote: On Tue, Apr 24, 2012 at 9:40 PM, Robert Haas robertmh...@gmail.com wrote: For three things, index pages have hint-type changes that are not single-bit changes. ? Just how big are these? Part of the reason hint bit updates are safe is because one bit definitely absolutely has to be entirely in one page. You can't tear a page in the middle of a bit. In reality the size is much larger, probably 4k and almost certainly at least 512 bytes. But the postgres block layout doesn't really offer much guarantees about the location of anything relative those 512 byte blocks so probably anything larger than a word is unsafe to update. See _bt_killitems. It uses ItemIdMarkDead, which looks like it will turn into a 4-byte store. The main problem with the approach was that we kept finding more hint bits we had forgotten about. Once the coding idiom was established it seems it was a handy hammer for a lot of problems. It is. And I think we shouldn't be lulled into the trap of thinking hint bits are bad. They do cause some problems, but they exist because they solve even worse problems. It's fundamentally pretty useful to be able to cache the results of expensive calculations in data pages, which is what hints allow us to do, and they let us do it without incurring the overhead of WAL-logging. Even if we could find a way of making CLOG access cheap enough that we didn't need HEAP_XMIN/XMAX_COMMITTED, it wouldn't clear the way to getting rid of hinting entirely. I strongly suspect that the btree item-is-dead hinting is actually MORE valuable than the heap hint bits. CLOG probes are expensive, but there is room for optimization there through caching and just because the data set is relatively limited in size. OTOH, the btree hints potentially save you a heap fetch on the next trip through, which potentially means a random I/O into a huge table. That's nothing to sneeze at. It also means that the next index insertion in the page can potentially prune that item away completely, allowing faster space re-use. That's nothing to sneeze at, either. To put that another way, the reason why WAL-logging all hints seems expensive is because NOT WAL-logging hints is a huge performance optimization. If we can come up with an even better performance optimization that also reduces the need to write out hinted pages, then of course we should do that, but we shouldn't hate the optimization we have because it's not as good as the one we wish we had. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove dead ports?
On Wed, Apr 25, 2012 at 12:06 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I have no position on whether those operating systems are dead enough to warrant removing support, but on a related point, I would like it if we could get rid of as many spinlock implementations as are applicable only to platforms that are effectively defunct. I'm suspicious of s_lock.h's support for National Semiconductor 32K, Renesas' M32R, Renesas' SuperH, UNIVEL, SINIX / Reliant UNIX, Nextstep, and Sun3, all of which are either on your list above, or stuff I've never heard of. I have no problem keeping whatever people are still using, but it would be nice to eliminate anything that's actually dead for the reasons you state. The Renesas implementations were added pretty darn recently, so I think there are users for those. The others you mention seem dead to me. On the other hand, exactly how much is it costing us to leave those sections of s_lock.h in there? It's not like we have any plans to redefine the spinlock interfaces. Well, actually, one thing I would like to do is add SpinLockConditionalAcquire(). I haven't quite found a compelling argument for having it yet, but it keeps coming up as I noodle around with different techniques to improve concurrency. I think there are some other things we'll want to add eventually, too. Of course none of that is impossible even if we keep everything, but like Peter said it saves work to not have to worry about ports that are completely defunct. I don't feel super-strongly about it, but OTOH I see little reason to keep the Univel spinlock implementation if we're removing the Univel port. If we ever decide to resupport the platform we can fish all the necessary bits out of git, and in fact it'll be easier if a single commit removes all traces of support rather than having it gradually disappear from the tree a bit at a time. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 12:08 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 12:18 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Apr 25, 2012 at 12:08 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Apr 25, 2012 at 4:49 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove dead ports?
Robert Haas robertmh...@gmail.com writes: ... I don't feel super-strongly about it, but OTOH I see little reason to keep the Univel spinlock implementation if we're removing the Univel port. No, I have no objection to that. I was just questioning the wisdom of removing CPU-specific s_lock sections on the grounds that we haven't heard from any users of that CPU lately. Doesn't mean they are not out there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
Robert Haas robertmh...@gmail.com writes: On Sat, Apr 14, 2012 at 10:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's no particular reason to think that Moore's Law is going to result in an increase in the fractional precision of timing data. It hasn't done so in the past, for sure. Perhaps, but nobody's explained what we gain out of NOT using numeric. It's slow doesn't impress me; selecting from a system view doesn't need to be lightning-fast. Well, how about the code is going to be quite a lot less readable? C can manipulate floats natively, but not numerics. Also, as was pointed out upthread, the underlying data in shared memory is almost certainly never going to be infinite-precision; so using numeric in the API seems to me to be more likely to convey a false impression of exactness than to do anything useful. However, the main thing here is that we need to do *something* here... Agreed, this has got to be pushed forward. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 12:30 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
On Wed, Apr 25, 2012 at 12:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Apr 14, 2012 at 10:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's no particular reason to think that Moore's Law is going to result in an increase in the fractional precision of timing data. It hasn't done so in the past, for sure. Perhaps, but nobody's explained what we gain out of NOT using numeric. It's slow doesn't impress me; selecting from a system view doesn't need to be lightning-fast. Well, how about the code is going to be quite a lot less readable? C can manipulate floats natively, but not numerics. Also, as was pointed out upthread, the underlying data in shared memory is almost certainly never going to be infinite-precision; so using numeric in the API seems to me to be more likely to convey a false impression of exactness than to do anything useful. However, the main thing here is that we need to do *something* here... Agreed, this has got to be pushed forward. In the interest of furthering that goal, I propose that whoever is willing to take the time to clean this up gets to decide what to standardize on, and I'm happy to give you first crack at that if you have the cycles. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 25, 2012 at 12:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: However, the main thing here is that we need to do *something* here... Agreed, this has got to be pushed forward. In the interest of furthering that goal, I propose that whoever is willing to take the time to clean this up gets to decide what to standardize on, and I'm happy to give you first crack at that if you have the cycles. OK. I have just returned from some emergency family business, and have got assorted catching-up to do, but I will try to get to that later this week. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
On Wed, Apr 25, 2012 at 1:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Apr 25, 2012 at 12:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: However, the main thing here is that we need to do *something* here... Agreed, this has got to be pushed forward. In the interest of furthering that goal, I propose that whoever is willing to take the time to clean this up gets to decide what to standardize on, and I'm happy to give you first crack at that if you have the cycles. OK. I have just returned from some emergency family business, and have got assorted catching-up to do, but I will try to get to that later this week. Sounds good to me. You might want to revisit the issue of how the new columns in pg_stat_statements are named, as well. I am not sure I'm happy with that, but neither am I sure that I know what I'd like better. It's not too clear that the timing is specifically for data block reads and writes, for example. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 5:53 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal - urlencode, urldecode support
Hello what do you think about enhancing encode, decode functions for support of mentioned code? Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
On Wed, Apr 25, 2012 at 5:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, as was pointed out upthread, the underlying data in shared memory is almost certainly never going to be infinite-precision; so using numeric in the API seems to me to be more likely to convey a false impression of exactness than to do anything useful. I don't think that follows. The underlyng data will be measured in some metric unit of time like microsecond or nanosecond or something like that. So a base-10 representation will show exactly the precision that the underlying data has. On the other hand a floating point number will show a base-2 approximation that may in fact display with more digits than the underlying data representation has. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
Greg Stark st...@mit.edu writes: On Wed, Apr 25, 2012 at 5:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, as was pointed out upthread, the underlying data in shared memory is almost certainly never going to be infinite-precision; so using numeric in the API seems to me to be more likely to convey a false impression of exactness than to do anything useful. I don't think that follows. The underlyng data will be measured in some metric unit of time like microsecond or nanosecond or something like that. So a base-10 representation will show exactly the precision that the underlying data has. On the other hand a floating point number will show a base-2 approximation that may in fact display with more digits than the underlying data representation has. My point is that the underlying data is going to be stored in a fixed-width representation, and therefore it will have accuracy and/or range limitations that are considerably more severe than use of numeric for output might suggest to the user. In the current pg_stat_statements code, timings are in fact accumulated in float8, and emitting them as something other than float8 is just plain misleading IMHO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - urlencode, urldecode support
On Apr 25, 2012, at 13:54, Pavel Stehule wrote: what do you think about enhancing encode, decode functions for support of mentioned code? Sounds like a great idea for a PGXN module. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - urlencode, urldecode support
2012/4/25 Michael Glaesemann g...@seespotcode.net: On Apr 25, 2012, at 13:54, Pavel Stehule wrote: what do you think about enhancing encode, decode functions for support of mentioned code? Sounds like a great idea for a PGXN module. it is one variant - but with support some web technologies - XML, JSON, I prefer this in core. Urlcode is one the most used code on world now - implementation is simple - and it can be well integrated with decode, encode functions. Regards Pavel Michael Glaesemann grzm seespotcode net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 1:31 PM, Simon Riggs si...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
On Wed, Apr 25, 2012 at 1:58 PM, Greg Stark st...@mit.edu wrote: On Wed, Apr 25, 2012 at 5:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, as was pointed out upthread, the underlying data in shared memory is almost certainly never going to be infinite-precision; so using numeric in the API seems to me to be more likely to convey a false impression of exactness than to do anything useful. I don't think that follows. The underlyng data will be measured in some metric unit of time like microsecond or nanosecond or something like that. So a base-10 representation will show exactly the precision that the underlying data has. On the other hand a floating point number will show a base-2 approximation that may in fact display with more digits than the underlying data representation has. I wholeheartedly agree. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
On Fri, Apr 13, 2012 at 01:29:38PM -0400, Bruce Momjian wrote: On Fri, Apr 13, 2012 at 12:40:09PM -0400, Robert Haas wrote: On Fri, Apr 13, 2012 at 11:23 AM, Bruce Momjian br...@momjian.us wrote: I just talked to Tom about the 9.2 release notes. Do people want me to write the 9.2 release notes? +1. When do you think we will be ready for 9.2 beta? Well, we've got a bunch of open issues, but most of them don't look *too* serious. If everyone dropped what they're doing and worked on them, I think we could be done in 2 weeks, but realistically I think it's likely to take 1-2 months. I was hoping for at least two weeks because I am away Monday to Thursday of next week for EnterpriseDB. I can do it by March 27, or the latest March 30, that is 2-2.5 weeks from now. I had hoped to complete the release notes by March 30, but travel and catching up on old stuff has delayed that. I will start working on them now and have them ready before we start beta. If that is a problem, please let me know. -- Bruce Momjian br...@momjian.ushttp://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
Re: [HACKERS] proposal - urlencode, urldecode support
Pavel Stehule pavel.steh...@gmail.com writes: 2012/4/25 Michael Glaesemann g...@seespotcode.net: Sounds like a great idea for a PGXN module. it is one variant - but with support some web technologies - XML, JSON, I prefer this in core. Urlcode is one the most used code on world now - implementation is simple - and it can be well integrated with decode, encode functions. Embedding that in encode/decode sounds to me like a pretty horrid idea, actually, unless I misunderstand what you are talking about. URL encoding is a text-to-text transformation, no? If so, it doesn't fit into encode/decode, which presume a binary (bytea) decoded form. People would be needing to do entirely bogus text/bytea coercions to use such an implementation. Ergo, this needs to be a separate function, and so the argument for putting it in core seems a bit weak to me. The net field demand for the feature, so far, has been zero. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
Bruce Momjian br...@momjian.us writes: I had hoped to complete the release notes by March 30, but travel and catching up on old stuff has delayed that. I will start working on them now and have them ready before we start beta. If that is a problem, please let me know. Well, we aren't ready for beta today anyway, AFAICT. Would it be reasonable to shoot for wrapping 9.2beta1 next week? (That would mean a wrap on Thursday May 3, if we follow the usual type of release schedule.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
On Wed, Apr 25, 2012 at 2:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: I had hoped to complete the release notes by March 30, but travel and catching up on old stuff has delayed that. I will start working on them now and have them ready before we start beta. If that is a problem, please let me know. Well, we aren't ready for beta today anyway, AFAICT. Would it be reasonable to shoot for wrapping 9.2beta1 next week? (That would mean a wrap on Thursday May 3, if we follow the usual type of release schedule.) I suspect that's a bit aggressive. We have a couple of issues that I think we should fix before beta1: - the pg_stat_statements cleanups we were discussing on the other thread, since that's going to involve changing column names/types - the Hot Standby vs. index-only scans stuff, which needs a WAL format change, and is my top priority as soon as I get unburied - SP-GiST is unsafe for use by hot standby slaves, since I think queries returning wrong answers = bad - http://archives.postgresql.org/message-id/1333124720-sup-6...@alvh.no-ip.org I'm not sure we can commit to a timeline for beta until we know who is taking care of each of those things. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
On Wed, Apr 25, 2012 at 7:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: I had hoped to complete the release notes by March 30, but travel and catching up on old stuff has delayed that. I will start working on them now and have them ready before we start beta. If that is a problem, please let me know. Well, we aren't ready for beta today anyway, AFAICT. Would it be reasonable to shoot for wrapping 9.2beta1 next week? (That would mean a wrap on Thursday May 3, if we follow the usual type of release schedule.) I doubt we'll be able to produce installers that soon - first, we're still building the build VMs for 9.2, and second, right now we're having to update every existing build VM and then the 8.3 through 9.1 installers (which will require more QA than normal because of the changes) due to the recent OpenSSL bug. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - urlencode, urldecode support
On Wed, Apr 25, 2012 at 02:41:19PM -0400, Tom Lane wrote: Pavel Stehule pavel.steh...@gmail.com writes: 2012/4/25 Michael Glaesemann g...@seespotcode.net: Sounds like a great idea for a PGXN module. it is one variant - but with support some web technologies - XML, JSON, I prefer this in core. Urlcode is one the most used code on world now - implementation is simple - and it can be well integrated with decode, encode functions. Embedding that in encode/decode sounds to me like a pretty horrid idea, actually, unless I misunderstand what you are talking about. URL encoding is a text-to-text transformation, no? If so, it doesn't fit into encode/decode, which presume a binary (bytea) decoded form. People would be needing to do entirely bogus text/bytea coercions to use such an implementation. I don't understand the actual proposal here, but urlencoding encodes octets as quoted us-ascii. So, its not really text to text, but bytes to US-ASCII and US-ASCII to bytes. AIUI, a unicode character has no well specified urlencoding. A utf-8 encoded unicode character can be said to have an urlencoding since we can come up a stream of octets to urlencode. Garick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - urlencode, urldecode support
2012/4/25 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2012/4/25 Michael Glaesemann g...@seespotcode.net: Sounds like a great idea for a PGXN module. it is one variant - but with support some web technologies - XML, JSON, I prefer this in core. Urlcode is one the most used code on world now - implementation is simple - and it can be well integrated with decode, encode functions. Embedding that in encode/decode sounds to me like a pretty horrid idea, actually, unless I misunderstand what you are talking about. URL encoding is a text-to-text transformation, no? If so, it doesn't fit into encode/decode, which presume a binary (bytea) decoded form. People would be needing to do entirely bogus text/bytea coercions to use such an implementation. A motivation for this proposal is JSON. I found lot of situation where content of some internet data was was encoded in this code. Ergo, this needs to be a separate function, and so the argument for putting it in core seems a bit weak to me. The net field demand for the feature, so far, has been zero. ook - it can be implemented as independently or as part of convert_from, convert_to function. Regards Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 25, 2012 at 2:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Would it be reasonable to shoot for wrapping 9.2beta1 next week? I suspect that's a bit aggressive. We have a couple of issues that I think we should fix before beta1: - the pg_stat_statements cleanups we were discussing on the other thread, since that's going to involve changing column names/types - the Hot Standby vs. index-only scans stuff, which needs a WAL format change, and is my top priority as soon as I get unburied - SP-GiST is unsafe for use by hot standby slaves, since I think queries returning wrong answers = bad - http://archives.postgresql.org/message-id/1333124720-sup-6...@alvh.no-ip.org I agree that fixing the first two before beta is a necessary thing. I'm not convinced the other two are beta blockers. We should not have a mindset of there must be no known bugs in beta1; we want to parallelize not serialize this process, so that testing has to be able to happen concurrently with fixing non-blocker bugs. I'd say that anything that isn't going to require an initdb to fix, and that most testers are unlikely to hit, ought not be a blocker. I'm not sure we can commit to a timeline for beta until we know who is taking care of each of those things. I already promised to deal with the first one, and you are indicating willingness to deal with the second, so I think we have the blockers covered. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
Dave Page dp...@pgadmin.org writes: On Wed, Apr 25, 2012 at 7:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Would it be reasonable to shoot for wrapping 9.2beta1 next week? (That would mean a wrap on Thursday May 3, if we follow the usual type of release schedule.) I doubt we'll be able to produce installers that soon - first, we're still building the build VMs for 9.2, and second, right now we're having to update every existing build VM and then the 8.3 through 9.1 installers (which will require more QA than normal because of the changes) due to the recent OpenSSL bug. Mmm. How about two weeks from tomorrow, then? If we don't make that, we won't have a beta till after PGCon, which would be a shame. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2 release notes, beta time?
On Wed, Apr 25, 2012 at 8:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Wed, Apr 25, 2012 at 7:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Would it be reasonable to shoot for wrapping 9.2beta1 next week? (That would mean a wrap on Thursday May 3, if we follow the usual type of release schedule.) I doubt we'll be able to produce installers that soon - first, we're still building the build VMs for 9.2, and second, right now we're having to update every existing build VM and then the 8.3 through 9.1 installers (which will require more QA than normal because of the changes) due to the recent OpenSSL bug. Mmm. How about two weeks from tomorrow, then? If we don't make that, we won't have a beta till after PGCon, which would be a shame. That's probably achievable (putting together the build machines is no small task). Worst case; the installers come out a few days later than the source code. It's a first beta, so it wouldn't be the end of the world. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Request to add options to tools/git_changelog
I am again requesting the addition of options to tools/git_changelog so
I can more easily produce the release notes. I asked for this during
9.1 development and it was rejected. I am currently using my own
custom version of the tool, but have to merge community improvements
into the tool every year before I use it.
The attached patch gives you an idea of what I want to add. New options
are:
--details-after Show branch and author info after the commit description
--master-only Show commits made exclusively to the master branch
--oldest-first Show oldest commits first
I know of now to do this with a post-processing script.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
*** /pgdev/git_changelog 2011-03-15 13:47:30.0 -0400
--- /rtmp/pggitlog 2012-04-25 15:45:16.0 -0400
***
*** 43,52
# Might want to make this parameter user-settable.
my $timestamp_slop = 600;
my $post_date = 0;
my $since;
! Getopt::Long::GetOptions('post-date' = \$post_date,
'since=s' = \$since) || usage();
usage() if @ARGV;
my @git = qw(git log --date=iso);
--- 43,62
# Might want to make this parameter user-settable.
my $timestamp_slop = 600;
+ my $details_after = 0;
my $post_date = 0;
+ my $master_only = 0;
+ my $oldest_first = 0;
my $since;
! my @output_buffer;
! my $output_line = '';
!
! Getopt::Long::GetOptions('details-after' = \$details_after,
! 'master-only' = \$master_only,
! 'post-date' = \$post_date,
! 'oldest-first' = \$oldest_first,
'since=s' = \$since) || usage();
+
usage() if @ARGV;
my @git = qw(git log --date=iso);
***
*** 193,211
last if !defined $best_branch;
my $winner =
$all_commits_by_branch{$best_branch}-[$position{$best_branch}];
! printf Author: %s\n, $winner-{'author'};
! foreach my $c (@{$winner-{'commits'}}) {
! printf Branch: %s, $c-{'branch'};
! if (defined $c-{'last_tag'}) {
! printf Release: %s, $c-{'last_tag'};
! }
! printf [%s] %s\n, substr($c-{'commit'}, 0, 9), $c-{'date'};
}
! print Commit-Order-Inversions: $best_inversions\n
! if $best_inversions != 0;
! print \n;
! print $winner-{'message'};
! print \n;
$winner-{'done'} = 1;
for my $branch (@BRANCHES) {
my $leader = $all_commits_by_branch{$branch}-[$position{$branch}];
--- 203,223
last if !defined $best_branch;
my $winner =
$all_commits_by_branch{$best_branch}-[$position{$best_branch}];
!
! # check for master-only
! if (! $master_only || ($winner-{'commits'}[0]-{'branch'} eq 'master'
! @{$winner-{'commits'}} == 1)) {
! output_details($winner) if (! $details_after);
! output_entry(Commit-Order-Inversions: $best_inversions\n)
! if $best_inversions != 0;
! output_entry(\n) if (! $details_after);
! output_entry(%s, $winner-{'message'});
! output_details($winner) if ($details_after);
! output_entry(\n);
! unshift(@output_buffer, $output_line) if ($oldest_first);
! $output_line = '';
}
!
$winner-{'done'} = 1;
for my $branch (@BRANCHES) {
my $leader = $all_commits_by_branch{$branch}-[$position{$branch}];
***
*** 216,221
--- 228,235
}
}
+ print @output_buffer if ($oldest_first);
+
sub push_commit {
my ($c) = @_;
my $ht = hash_commit($c);
***
*** 274,284
return $gm - $tzoffset;
}
sub usage {
print STDERR EOM;
! Usage: git_changelog [--post-date/-p] [--since=SINCE]
! --post-date Show branches made after a commit occurred
! --since Print only commits dated since SINCE
EOM
exit 1;
}
--- 288,323
return $gm - $tzoffset;
}
+ sub output_entry {
+ ($oldest_first) ? ($output_line .= sprintf(shift, @_)) : printf(@_);
+ }
+
+ sub output_details {
+ my $item = shift;
+
+ if ($details_after) {
+ $item-{'author'} =~ m{^(.*?)\s*[^]*$};
+ output_entry((%s)\n, $1);
+ } else {
+ output_entry(Author: %s\n, $item-{'author'});
+ }
+ foreach my $c (@{$item-{'commits'}}) {
+ output_entry(Branch: %s , $c-{'branch'}) if (! $master_only);
+ if (defined $c-{'last_tag'}) {
+ output_entry(Release: %s , $c-{'last_tag'});
+ }
+ output_entry([%s] %s\n, substr($c-{'commit'}, 0, 9), $c-{'date'});
+ }
+ }
+
sub usage {
print STDERR EOM;
! Usage: git_changelog [--details-after/-d] [--master-only/-m] [--oldest-first/-o] [--post-date/-p] [--since=SINCE]
! --details-after Show branch and author info after the commit description
! --master-only Show commits made exclusively to the master branch
! --oldest-first Show oldest commits first
! --post-date Show branches made after a commit occurred
! --since Print only commits dated since SINCE
EOM
exit 1;
}
--
Sent via pgsql-hackers mailing list
[HACKERS] Regression tests for preload extension
I wrote a little extension to store a few small strings in shared memory. It seems to be working fine, and now I would like to write some regression tests; but it's not immediately obvious to me how I can do that. The approach used by, for example, citext doesn't work, because I don't see how to set shared_preload_libraries for the server startup. The existing contrib extensions which preload either seem to do something ad hoc or skip regression tests entirely, so I suspect that is my choice; but I figured I should ask. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Request to add options to tools/git_changelog
On Wed, Apr 25, 2012 at 4:04 PM, Bruce Momjian br...@momjian.us wrote: I am again requesting the addition of options to tools/git_changelog so I can more easily produce the release notes. I asked for this during 9.1 development and it was rejected. I am currently using my own custom version of the tool, but have to merge community improvements into the tool every year before I use it. I surrender. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql omits row count under \x auto
On Mon, Apr 23, 2012 at 12:30 PM, Noah Misch n...@leadboat.com wrote: Looks like the logic in printQuery() needs further treatment. Do you want to propose a patch, or are you hoping someone else is going to address this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
On mån, 2012-04-23 at 22:03 -0400, Robert Haas wrote: Perhaps, but nobody's explained what we gain out of NOT using numeric. So if you want to have possibly different internal and external representations, why not use interval for the external one? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression tests for preload extension
Kevin Grittner kevin.gritt...@wicourts.gov writes: I wrote a little extension to store a few small strings in shared memory. It seems to be working fine, and now I would like to write some regression tests; but it's not immediately obvious to me how I can do that. The approach used by, for example, citext doesn't work, because I don't see how to set shared_preload_libraries for the server startup. The existing contrib extensions which preload either seem to do something ad hoc or skip regression tests entirely, so I suspect that is my choice; but I figured I should ask. Hm. pg_regress.c goes to some trouble to allow you to set session-level options by setting PGOPTIONS in its environment, but that won't work for options that have to be given to the postmaster. Maybe we should invent a pg_regress switch that allows additional switches to be given to the temp postmaster. Of course, this is never gonna work for make installcheck. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Request to add options to tools/git_changelog
Bruce Momjian br...@momjian.us writes: The attached patch gives you an idea of what I want to add. This patch doesn't seem to be against HEAD? --details-after Show branch and author info after the commit description I don't understand the point of that. --master-only Show commits made exclusively to the master branch Agreed, this could be useful. --oldest-first Show oldest commits first This also seems rather useless in comparison to how much it complicates the code. We don't sort release note entries by commit date, so what's it matter? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
Peter Eisentraut pete...@gmx.net writes: On mån, 2012-04-23 at 22:03 -0400, Robert Haas wrote: Perhaps, but nobody's explained what we gain out of NOT using numeric. So if you want to have possibly different internal and external representations, why not use interval for the external one? That doesn't add any usefulness, only extra complication for clients that want to do more arithmetic with the values. Also, as was pointed out earlier, we have a hard-coded restriction to microsecond precision with the default implementation of interval; and it's not hard to foresee the day when that won't do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: add timing of buffer I/O requests
On Wed, Apr 25, 2012 at 5:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On mån, 2012-04-23 at 22:03 -0400, Robert Haas wrote: Perhaps, but nobody's explained what we gain out of NOT using numeric. So if you want to have possibly different internal and external representations, why not use interval for the external one? That doesn't add any usefulness, only extra complication for clients that want to do more arithmetic with the values. Also, as was pointed out earlier, we have a hard-coded restriction to microsecond precision with the default implementation of interval; and it's not hard to foresee the day when that won't do. Agreed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 11:53 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Apr 25, 2012 at 12:30 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Apr 25, 2012 at 5:19 PM, Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 5:46 PM, Merlin Moncure mmonc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap
Tom, I whipped up some INSERT/SELECT tests where I selected into a temporary table as you suggested. The target temporary table and the source table were in cache and I basically disabled things that would cause noise. The source table had 5 integer columns, and was populated with 10 million rows. I tried 3 variations: 1) target has all nullable columns, all set to non null values: the results were the same 2) target has all nullable columns, only the first column is set: the patch was slightly faster 3) target has all non-null columns: the patch maybe was slightly faster, probably not statistically relevant By slightly faster I'm talking on order of 10 nanoseconds per row. I think #2 is explained by the reduction in loop iterations in heap_fill_tuple(). From: Tom Lane t...@sss.pgh.pa.us To: Jameison Martin jameis...@yahoo.com Cc: pgsql-hackers@postgresql.org pgsql-hackers@postgresql.org Sent: Tuesday, April 17, 2012 9:57 PM Subject: Re: [HACKERS] patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap Jameison Martin jameis...@yahoo.com writes: The use-case I'm targeting is a schema that has multiple tables with ~800 columns, most of which have only the first 50 or so values set. 800 columns would require 800 bits in a bitmap which equates to 100 bytes. With 8-byte alignment the row bitmap would take up 104 bytes with the current implementation. If only the first 50 or so columns are actually non-null, then the minimum bitmap size wouldn't need to be more than 8 bytes, which means the proposed change would save 96 bytes. For the data set I have in mind roughly 90% of the rows would fall into the category of needing only 8 bytes for the null bitmap. I can't help thinking that (a) this is an incredibly narrow use-case, and (b) you'd be well advised to rethink your schema design anyway. There are a whole lot of inefficiencies associated with having that many columns; the size of the null bitmap is probably one of the smaller ones. I don't really want to suggest an EAV design, but perhaps some of the columns could be collapsed into arrays, or something like that? What kind of test results would prove that this is a net win (or not a net loss) for typical cases? Are you interested in some insert performance tests? Also, how would you define a typical case (e.g. what kind of data shape)? Hmm, well, most of the tables I've seen have fewer than 64 columns, so that the probability of win is exactly zero. Which would mean that you've got to demonstrate that the added overhead is unmeasurably small. Which maybe you can do, because there's certainly plenty of cycles involved in a tuple insertion, but we need to see the numbers. I'd suggest an INSERT/SELECT into a temp table as probably stressing tuple formation speed the most. Or maybe you could write a C function that just exercises heap_form_tuple followed by heap_freetuple in a tight loop --- if there's no slowdown measurable in that context, then a fortiori we don't have to worry about it in the real world. regards, tom lane
Re: [HACKERS] urgent help required
On 19/04/12 06:23, Josh Berkus wrote: Nagaraj, i am nagaraj, i am newbi in this database world. i required your help. 2 dyas back i formatted one of my client system. which is having postgresql 8.2 database that was having data. but i am not taken backup of the data. 1) how to take the data from the formatted harddisk. ? 2) how many folders or files will be their at base folder. When we install postgresql 8.2? 3) how to identify which folder contain which file. ? 4) If you formatted the hard drive, your data is gone. Well that last point is not entirely true. Depending on the type of format used it may be possible to recover little/some/most (bit of a lottery there) of your data. There are tools out there to help, however if the data is important it is worthwhile a) stopping using the disk(s) immediately and b) getting a computer forensics/data recovery service to help. regards Mark P.s: Judging from the date of this thread the above advice may be too late, sorry. Next time always backup! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 11:49:23AM -0400, Robert Haas wrote: On Tue, Apr 24, 2012 at 11:55 PM, Noah Misch n...@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
Re: [HACKERS] psql omits row count under \x auto
On Wed, Apr 25, 2012 at 04:57:36PM -0400, Robert Haas wrote: On Mon, Apr 23, 2012 at 12:30 PM, Noah Misch n...@leadboat.com wrote: Looks like the logic in printQuery() needs further treatment. Do you want to propose a patch, or are you hoping someone else is going to address this? I figured Peter might have a preference for how to fix it. If not, I can put something together. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/PGSQL bug in handling composite types
Boszormenyi Zoltan z...@cybertec.at writes: we have found a way to make pl/pgsql throw an error for a legitimate use case that works in plain SQL. The fact that it doesn't work the way you thought doesn't make it a bug. plpgsql expects an assignment INTO row_variable to be supplied from one select column per field in the composite variable. So instead of executing 'select (max(id),min(d))::mytype from x1', try executing 'select max(id),min(d) from x1'. If we were to change this, we would break a lot of existing plpgsql code; or, if we tried to support both usages, we would create nasty semantic ambiguities for single-column composites. Possibly the documentation in 39.5.3. Executing a Query with a Single-row Result could be improved though. To my eyes, it does say that this is what happens, but it doesn't really emphasize the point that the SELECT's output has to be exploded not delivered as a single composite column. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 10:04 PM, Noah Misch n...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Wed, Apr 25, 2012 at 10:16 PM, Noah Misch n...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Request to add options to tools/git_changelog
On Wed, Apr 25, 2012 at 05:09:04PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: The attached patch gives you an idea of what I want to add. This patch doesn't seem to be against HEAD? Yes, if people approve, I will work on a current patch against HEAD. --details-after Show branch and author info after the commit description I don't understand the point of that. The release notes have the author at the end of the text. --master-only Show commits made exclusively to the master branch Agreed, this could be useful. --oldest-first Show oldest commits first This also seems rather useless in comparison to how much it complicates the code. We don't sort release note entries by commit date, so what's it matter? It is very hard to read the commit messages newest-first because they are often cummulative, and the order of items of equal weight is oldest-first in the release notes. -- Bruce Momjian br...@momjian.ushttp://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
Re: [HACKERS] Request to add options to tools/git_changelog
Bruce Momjian br...@momjian.us writes: On Wed, Apr 25, 2012 at 05:09:04PM -0400, Tom Lane wrote: --details-after Show branch and author info after the commit description I don't understand the point of that. The release notes have the author at the end of the text. So? The committer is very often not the author, so I'm not seeing that this helps much. Not to mention that the commit message is almost never directly usable as release note text, anyway. --oldest-first Show oldest commits first This also seems rather useless in comparison to how much it complicates the code. We don't sort release note entries by commit date, so what's it matter? It is very hard to read the commit messages newest-first because they are often cummulative, and the order of items of equal weight is oldest-first in the release notes. I'm unpersuaded here, too, not least because I have never heard this oldest first policy before, and it's certainly never been followed in any set of release notes I wrote. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
