[HACKERS] Hot standby, misc issues
There's a couple of items on the TODO page (https://wiki.postgresql.org/wiki/Hot_Standby_TODO) that haven't been discussed on-list: In normal operation, a few commands call ForceSyncCommit() to force non-async commit. Should ForceSyncCommit force an XLogFlush() during recovery as well? * Simon says: No, why should it? For the same reason we emit ForceSyncCommit() in normal operation. For example, in DROP DATABASE, we delete all the files belonging to the database, and then commit the transaction. If we crash after all the files have been deleted but before the commit, you have an entry in pg_database without any files. To minimize the window for that, we use ForceSyncCommit() to rush the commit record to disk as quick as possible. We have the same window during recovery, and forcing an XLogFlush() (which updates minRecoveryPoint during recovery) would help to keep it small. This isn't really related to Hot Standby. If you set the PITR target time/xid to between the XLOG_DBASE_DROP record and the COMMIT record, you end up with a zombie pg_database entry. @Heikki: Why is error checking in KnownAssignedXidsRemove() #ifdef'd out?? It's explained in the comment: /* XXX: This can still happen: If a transaction with a subtransaction * that haven't been reported yet aborts, and no WAL records have been * written using the subxid, the abort record will contain that subxid * and we haven't seen it before. */ -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Hot Standby remaining issues
Regarding this item from the wiki page: The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. * Simon says... changed to allow checkpoints to update recoveryLastXTime (Simon DONE) Update recoveryLastXTime at checkpoints doesn't help when the master is completely idle, because we skip checkpoints in that case. It's better than nothing, of course. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Hot Standby remaining issues
On Fri, 2009-12-04 at 10:37 +0200, Heikki Linnakangas wrote: Regarding this item from the wiki page: The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. * Simon says... changed to allow checkpoints to update recoveryLastXTime (Simon DONE) Update recoveryLastXTime at checkpoints doesn't help when the master is completely idle, because we skip checkpoints in that case. It's better than nothing, of course. Not if archive_timeout is set, which it would be in warm standby case. We can do even better than this with SR. -- Simon Riggs www.2ndQuadrant.com -- 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] Hot Standby remaining issues
Simon Riggs wrote: On Fri, 2009-12-04 at 10:37 +0200, Heikki Linnakangas wrote: Regarding this item from the wiki page: The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. * Simon says... changed to allow checkpoints to update recoveryLastXTime (Simon DONE) Update recoveryLastXTime at checkpoints doesn't help when the master is completely idle, because we skip checkpoints in that case. It's better than nothing, of course. Not if archive_timeout is set, which it would be in warm standby case. We can do even better than this with SR. If the system is completely idle, and no WAL is written, we skip xlog switches too, even if archive_timeout is set . It would be pointless to create a stream of WAL files with no content except for the XLOG_SWITCH records. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Syntax for partitioning
On Fri, 2009-12-04 at 11:54 +0900, Itagaki Takahiro wrote: Here is an update partitioning syntax patch. A bug reported by Marko is fixed. I will review and eventually commit this, if appropriate, though it is 3rd in my queue and will probably not be done for at least 2 weeks, possibly 4 weeks. Some brief review comments * SQL:2008 contains PARTITION BY syntax, though in completely different context. A possible alternative would be to use PARTITIONED BY. Please justify either way. Possibly add short section to docs to explain this. * There are relatively few comments in-line. Please can you provide a README section for the code that explains how partitioning works? A reviewer's guide would also be helpful to explain some of the design decisions in particular places. * All of the tests use about 4 partitions, which is the kind of thing that makes me think the test coverage isn't wide enough. More tests please. This would include operations on 0?, 1 and many partitions. We also need more test failures, covering all the dumbass things people will attempt. Also need partitioning by strange datatypes, arrays, timestamps with timezones and stupidly long list values. Read Rob Treat's humorous dissection of earlier partitioning features at PGcon to see what needs to be covered. * Docs. This is looking fairly solid, so please begin working on docs. I won't hold you to this in next few weeks, but we know it needs doing. * It is essential that we have large real-world(ish) performance test results that proves this patch will work in the areas for which it is intended. We need a test with 500 partitions, using at least 10MB partitions to see if there are any scale-related issues. This test case will help set targets for later developments because it will highlight performance issues in planning, DDL and other areas. This doesn't have to be by the patch author, but we need to see evidence that this patch operates in its primary use case. -- Simon Riggs www.2ndQuadrant.com -- 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] Hot standby and removing VACUUM FULL
On Sat, 2009-11-21 at 20:20 +0200, Heikki Linnakangas wrote: VACUUM FULL does a peculiar hack: once it's done moving tuples, and before it truncates the relation, it calls RecordTransactionCommit to mark the transaction as committed in clog and WAL, but the transaction is still kept open in proc array. After it's done with truncating and other cleanup, normal transaction commit performs RecordTransactionCommit again as part of normal commit processing. That causes some headaches for Hot Standby, ie. it needs to know to not release locks yet when it sees the first commit record. At the moment, it simply ignores the first commit record, but that's very dangerous. If failover happens after the standby has seen the truncation record, but not the 2nd commit record for the transaction, all data moved from the truncated part will lost. ... So I guess what I'm asking is: Does anyone see any show-stoppers in removing VACUUM FULL, and does anyone want to step up to the plate and promise to do it before release? I've just reviewed the VACUUM FULL patch to see if it does all we need it to do, i.e. does the removal of HS code match the new VF patch. My answer is it doesn't, we will still have the problem noted above for catalog tables. So we still have a must-fix issue for HS, though that is no barrier to the new VF patch. Requirement is that * we ignore first commit, since it has an identical xid to second commit, so requires a special case to avoid breaking other checks * we musn't truncate until we are certain transaction completes, otherwise we will have a data loss situation (isolated to this particular case only) Proposal: * (In normal running) Just before we issue the first VFI commit we send a new type of WAL message to indicate VFI-in-progress, including the xid * (In HS recovery) When we see first commit record for the VF xid we ignore it, as we did in the original patch * (In HS recovery) When we see relation truncate for the xid we ignore it for now, but defer until after the second commit is processed It ain't that great, but it means all of the hack is isolated to specific HS-only code, which can be turned off if required. -- Simon Riggs www.2ndQuadrant.com -- 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] New VACUUM FULL
On Tue, 2009-12-01 at 01:43 -0800, Jeff Davis wrote: Marking as ready. You're saying its ready, yet there are 3 additional suggestions patches attached here. Please can you resolve these and re-submit a single final patch from author and reviewer? I will review and eventually commit this, if appropriate. It is either 1st or 2nd in my queue, unless someone else grabs it first. Review comments * What happens if you issue VACUUM FULL; which we would expect to use the new method of vacuum on all tables in the database. Won't that just fail with an error when it comes to catalog tables? Sounds to me like we should avoid the error and just silently do an INPLACE on catalog tables. * Such a pivotal change to Postgres needs more test coverage than a single line in regression tests. It might have been OK before, but I think we need a few more combinations here, at least in this release: with, without indexes, empty table, clustered, non-clustered etc and of course a full database VACUUM so that we have the catalog table case covered, plus an explicit catalog table vacuum. -- Simon Riggs www.2ndQuadrant.com -- 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] Hot standby and removing VACUUM FULL
Simon Riggs wrote: I've just reviewed the VACUUM FULL patch to see if it does all we need it to do, i.e. does the removal of HS code match the new VF patch. Oh, good! My answer is it doesn't, we will still have the problem noted above for catalog tables. So we still have a must-fix issue for HS, though that is no barrier to the new VF patch. I think the VACUUM FULL patch will have to address that. Either with the flat-file approach Tom suggested, or by disallowing VACUUM FULL for catalog tables altogether, requiring you to use the to-be-written online tool that uses dummy UPDATEs to move tuples. Requirement is that * we ignore first commit, since it has an identical xid to second commit, so requires a special case to avoid breaking other checks * we musn't truncate until we are certain transaction completes, otherwise we will have a data loss situation (isolated to this particular case only) Proposal: * (In normal running) Just before we issue the first VFI commit we send a new type of WAL message to indicate VFI-in-progress, including the xid * (In HS recovery) When we see first commit record for the VF xid we ignore it, as we did in the original patch * (In HS recovery) When we see relation truncate for the xid we ignore it for now, but defer until after the second commit is processed It ain't that great, but it means all of the hack is isolated to specific HS-only code, which can be turned off if required. Could you just mark the transaction as committed when you see the 1st commit record, but leave the XID in the known-assigned list and not release locks? That would emulate pretty closely what happens in the master. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Hot standby and removing VACUUM FULL
On Fri, 2009-12-04 at 11:22 +0200, Heikki Linnakangas wrote: Could you just mark the transaction as committed when you see the 1st commit record, but leave the XID in the known-assigned list and not release locks? That would emulate pretty closely what happens in the master. OK, works for me. Thanks. I thought of that before and dismissed it, clearly before my morning coffee, but it works because we still hold the lock on the table so nobody can actually read the now visible new rows. Cool. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
On Dec 3, 2009, at 1:53 PM, Jonah H. Harris wrote: On Tue, Dec 1, 2009 at 1:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On Tue, 2009-12-01 at 13:20 -0500, Robert Haas wrote: Does $COMPETITOR offer this feature? My understanding is that MSSQL does. I am not sure about Oracle. Those are the only two I run into (I don't run into MySQL at all). I know others likely compete in the DB2 space. To my knowledge, MySQL, InnoDB, BerkeleyDB, solidDB, Oracle, SQL Server, Sybase, DB2, eXtremeDB, RDB, and Teradata all checksum pages. So... now that the upgrade discussion seems to have died down... was any consensus reached on how to do said checksumming? -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.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] Block-level CRC checks
On Fri, 2009-12-04 at 03:32 -0600, decibel wrote: So... now that the upgrade discussion seems to have died down... was any consensus reached on how to do said checksumming? Possibly. Please can you go through the discussion and pull out a balanced summary of how to proceed? I lost track a while back and I'm sure many others did also. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
Kevin, md5sum of each tuple? As an optional system column (a la oid)? I am mainly an application programmer working with PostgreSQL. And I want to point out an additional usefullness of an md5sum of each tuple: it makes comparing table-contents in replicated / related databases MUCH more feasible. I am in the process of adding a user-space myhash column to all my applications tables, filled by a trigger on insert / update. It really speeds up table comparison across databases; and it is very helpfull in debugging replications. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality -- 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] [GENERAL] Installing PL/pgSQL by default
Tom == Tom Lane t...@sss.pgh.pa.us writes: Andrew Dunstan and...@dunslane.net writes: Before we go too far with this, I'd like to know how we will handle the problems outlined here: http://archives.postgresql.org/pgsql-hackers/2008-02/msg00916.php Tom Hm, I think that's only a problem if we define it to be a Tom problem, and I'm not sure it's necessary to do so. Currently, Tom access to PL languages is controlled by superusers. You are Tom suggesting that if plpgsql is installed by default, then access Tom to it should be controlled by non-superuser DB owners instead. Currently, a non-superuser db owner can install plpgsql, and having installed it, can DROP it or grant/revoke access to it: test= create language plpgsql; CREATE LANGUAGE test= revoke usage on language plpgsql from public; REVOKE test= drop language plpgsql; DROP LANGUAGE The complaint is that if plpgsql is installed by default, then it will be owned by postgres rather than by the db owner, who will then not be able to drop it or use grant/revoke on it. (This only became an issue since the ability for non-superuser DB owners to install languages from pltemplate was added.) -- Andrew (irc:RhodiumToad) -- 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] Windows x64 [repost]
Thanks to suggestion.
I send pathces again by another mailer for the archive.
Sorry to waste resources, below is same content that I send before.
Tsutomu Yamada
SRA OSS, Inc. Japan
#
The following patches support Windows x64.
1) use intptr_t for Datum and pointer macros. (to support Windows LLP64)
almost the same as that post before.
http://archives.postgresql.org/pgsql-hackers/2009-06/threads.php#01364
2) use appropriate macro and datatypes for Windows API.
enables more than 32bits shared memory.
3) Build scripts for MSVC, this came from
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00440.php
add new parameters to config.pl.
You need define platform to x64 for 64bit programs.
-
Windows x64 binary that applied patch and build with MSVS2005 can pass
all regression tests (vcregress.bat).
I was checked where the string converted with %ld is used.
An especially fatal part is not found excluding one of plperl.
But there is still a possibility that elog messages output a incorrect value.
(I thought it is not fatal, ignored these for the present.)
(eg) src/backend/port/win32_shmem.c, line 167
'size' is 'size_t' = 64bit value.
| ereport(FATAL,
| (errmsg(could not create shared memory segment: %lu, GetLastError()),
| errdetail(Failed system call was CreateFileMapping(size=%lu, name=%s).,
| (unsigned long) size, szShareMem)));
The code that becomes a problem of plperl is the following.
The address is converted into the string, and it is used as hash key.
However, there is really little possibility that two address values
become the same low word, and the problem will not occur.
(Of course, it is necessary to fix though the problem doesn't occur.)
--- src/pl/plperl/plperl.c 2009-11-30 18:56:30.0 +0900
+++ /tmp/plperl.c 2009-12-01 18:46:43.0 +0900
@@ -95,7 +95,7 @@
**/
typedef struct plperl_query_desc
{
- charqname[sizeof(long) * 2 + 1];
+ charqname[sizeof(void *) * 2 + 1];
void *plan;
int nargs;
Oid*argtypes;
@@ -2343,7 +2343,8 @@
/
qdesc = (plperl_query_desc *) malloc(sizeof(plperl_query_desc));
MemSet(qdesc, 0, sizeof(plperl_query_desc));
- snprintf(qdesc-qname, sizeof(qdesc-qname), %lx, (long) qdesc);
+ /* XXX: for LLP64, use %p or %ll */
+ snprintf(qdesc-qname, sizeof(qdesc-qname), %p, qdesc);
qdesc-nargs = argc;
qdesc-argtypes = (Oid *) malloc(argc * sizeof(Oid));
qdesc-arginfuncs = (FmgrInfo *) malloc(argc * sizeof(FmgrInfo));
0001-use-uintptr_t-for-Datum.patch
Description: Binary data
0002-fix-for-Windows-x64.patch
Description: Binary data
0003-MSVC-build-scripts-for-Windows-x64.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: [HACKERS] Hot standby and removing VACUUM FULL
On Fri, 2009-12-04 at 11:22 +0200, Heikki Linnakangas wrote: My answer is it doesn't, we will still have the problem noted above for catalog tables. So we still have a must-fix issue for HS, though that is no barrier to the new VF patch. I think the VACUUM FULL patch will have to address that. Either with the flat-file approach Tom suggested, or by disallowing VACUUM FULL for catalog tables altogether, requiring you to use the to-be-written online tool that uses dummy UPDATEs to move tuples. I don't see we need either of those, with the solution below. ISTM premature to remove all traces of VF from code. We may yet need it for some reason, especially if doing so creates complex dependencies on important features. Proposal: * (In normal running) Just before we issue the first VFI commit we send a new type of WAL message to indicate VFI-in-progress, including the xid * (In HS recovery) When we see first commit record for the VF xid we ignore it, as we did in the original patch * (In HS recovery) When we see relation truncate for the xid we ignore it for now, but defer until after the second commit is processed It ain't that great, but it means all of the hack is isolated to specific HS-only code, which can be turned off if required. Could you just mark the transaction as committed when you see the 1st commit record, but leave the XID in the known-assigned list and not release locks? That would emulate pretty closely what happens in the master. So modified proposal looks like this 1. (In normal running) Provide information to HS so it can identify VF commit records. Implement in code either a) Just before we issue the first VFI commit we send a new type of WAL message to indicate VFI-in-progress, including the xid. b) Alter the API of RecordTransactionCommit(), and send the info within the commit record. This was pretty much how we did that before. I prefer (a) now because the ugliness is better isolated. 2. (In HS recovery) When we see first commit record for the VF xid we commit the transaction in clog, yet maintain locks and KnownAssigned xids 3. (In HS recovery) When we see second commit record for the VF xid we skip clog updates but then perform remaining parts of commit. Conceptually this splits a VF commit into two parts. -- Simon Riggs www.2ndQuadrant.com -- 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] First feature patch for plperl - draft [PATCH]
On Thu, Dec 03, 2009 at 04:53:47PM -0800, David E. Wheeler wrote:
On Dec 3, 2009, at 3:30 PM, Tim Bunce wrote:
- New GUC plperl.on_perl_init='...perl...' for admin use.
- New GUC plperl.on_trusted_init='...perl...' for plperl user use.
- New GUC plperl.on_untrusted_init='...perl...' for plperlu user use.
Since there is no documentation yet, how do these work, exactly? Or should I
just wait for the docs?
The perl code in plperl.on_perl_init gets eval'd as soon as an
interpreter is created. That could be at server startup if
shared_preload_libraries is used. plperl.on_perl_init can only be set by
an admin (PGC_SUSET).
The perl code in plperl.on_trusted_init gets eval'd when an interpreter
is initialized into trusted mode, e.g., used for the plperl language.
The perl code is eval'd inside the Safe compartment.
plperl.on_trusted_init can be set by users but it's only useful if set
before the plperl interpreter is first used.
plperl.on_untrusted_init acts like plperl.on_trusted_init but for
plperlu code.
So, if all three were set then, before any perl stored procedure or DO
block is executed, the interpreter would have executed either
on_perl_init and then on_trusted_init (for plperl), or on_perl_init and
then on_untrusted_init (for plperlu).
- END blocks now run at backend exit (fixes bug #5066).
- Stored procedure subs are now given names ($name__$oid).
- More error checking and reporting.
- Warnings no longer have an extra newline in the NOTICE text.
- Various minor optimizations like pre-growing data structures.
Nice.
Thanks.
I'm working on adding tests and documentation now, meanwhile I'd very
much appreciate any feedback on the patch.
Tim.
p.s. Once this patch is complete I plan to work on patches that:
- add quote_literal and quote_identifier functions in C.
I expect you can just use the C versions in PostgreSQL. They're in
utils/builtins.h,
That's my plan. (I've been discussing this and other issues with Andrew
Dunstan via IM.)
along with quote_nullable(), which might also be useful to add.
I was planning to build that behaviour into quote_literal since it fits
naturally into perl's idea of undef and mirrors DBI's quote() method.
So:
quote_literal(undef) = NULL
quote_literal('foo') = 'foo'
- generalize the Safe setup code to enable more control.
Specifically control what gets loaded into the Compartment, what gets
shared with it (e.g. sharing *a *b as a workaround for the sort bug),
and what class to use for Safe (to enable deeper changes if desired via
subclassing). Naturally all this is only possible for admin (via
plperl.on_perl_init).
- formalize namespace usage, moving things out of main::
Nice.
- add a way to perform inter-sub calling (at least for simple cases).
My current plan here is to use an SP::AUTOLOAD to handle loading and
dispatching. So calling SP::some_random_procedure(...) will trigger
SP::AUTOLOAD to try to resolve some_random_procedure to a particular
stored procedure. There are three tricky parts: handling polymorphism (at
least well enough), making autoloading of stored procedures work
inside Safe, making it fast. I think I have reasonable approaches for
those but I won't know for sure till I work on it.
- possibly rewrite _plperl_to_pg_array in C.
Sounds great, Tim. I'm not really qualified to say anything about the
C code, but I'd be happy to try it out once there are docs.
Great. Thanks David.
Tim.
--
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] Hot standby and removing VACUUM FULL
Simon Riggs wrote: ISTM premature to remove all traces of VF from code. We may yet need it for some reason, especially if doing so creates complex dependencies on important features. Well, it's still in the repository. So modified proposal looks like this 1. (In normal running) Provide information to HS so it can identify VF commit records. Implement in code either a) Just before we issue the first VFI commit we send a new type of WAL message to indicate VFI-in-progress, including the xid. b) Alter the API of RecordTransactionCommit(), and send the info within the commit record. This was pretty much how we did that before. I prefer (a) now because the ugliness is better isolated. With a), you need to keep track of the seen VFI-in-progress records, remember to expire the state at a shutdown record etc. And you have to deal with the possibility that a checkpoint happens between the VFI-in-progress record and the commit record; a recovery starting from the checkpoint/running-xacts record must see both records or it will release the locks prematurely. b) seems much simpler to me. 2. (In HS recovery) When we see first commit record for the VF xid we commit the transaction in clog, yet maintain locks and KnownAssigned xids 3. (In HS recovery) When we see second commit record for the VF xid we skip clog updates but then perform remaining parts of commit. I's harmless to set a clog entry as committed twice, so you can treat the 2nd commit record the same as a regular commit record. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Block-level CRC checks
On Fri, Dec 4, 2009 at 9:34 AM, Simon Riggs si...@2ndquadrant.com wrote: Possibly. Please can you go through the discussion and pull out a balanced summary of how to proceed? I lost track a while back and I'm sure many others did also. I summarized the three feasible plans I think I saw; 407d949e0912011713j63045989j67b7b343ef00c...@mail.gmail.com -- 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] Hot standby and removing VACUUM FULL
On Fri, 2009-12-04 at 13:31 +0200, Heikki Linnakangas wrote: b) seems much simpler to me. OK. Least ugly wins, but she ain't pretty. 2. (In HS recovery) When we see first commit record for the VF xid we commit the transaction in clog, yet maintain locks and KnownAssigned xids 3. (In HS recovery) When we see second commit record for the VF xid we skip clog updates but then perform remaining parts of commit. I's harmless to set a clog entry as committed twice, so you can treat the 2nd commit record the same as a regular commit record. Yeh, OK, it is harmless and makes code cleaner. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
decibel wrote: On Dec 3, 2009, at 1:53 PM, Jonah H. Harris wrote: On Tue, Dec 1, 2009 at 1:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On Tue, 2009-12-01 at 13:20 -0500, Robert Haas wrote: Does $COMPETITOR offer this feature? My understanding is that MSSQL does. I am not sure about Oracle. Those are the only two I run into (I don't run into MySQL at all). I know others likely compete in the DB2 space. To my knowledge, MySQL, InnoDB, BerkeleyDB, solidDB, Oracle, SQL Server, Sybase, DB2, eXtremeDB, RDB, and Teradata all checksum pages. So... now that the upgrade discussion seems to have died down... was any consensus reached on how to do said checksumming? I think the hint bit has to be added to the item pointer, by using the offset bits that are already zero, according to Greg Stark. That solution leads to easy programming, no expanding hint bit array, and it is backward compatible so doesn't cause problems for pg_migrator. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your 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] Block-level CRC checks
On Fri, 2009-12-04 at 07:12 -0500, Bruce Momjian wrote: I think the hint bit has to be added to the item pointer, by using the offset bits that are already zero, according to Greg Stark. That solution leads to easy programming, no expanding hint bit array, and it is backward compatible so doesn't cause problems for pg_migrator. Seems like a reasonable way forward. As I pointed out here http://archives.postgresql.org/pgsql-hackers/2009-12/msg00056.php we only need to use 3 bits not 4, but it does limit tuple length to 4096 for all block sizes. (Two different options there for doing that). An added advantage of this approach is that the cachelines for the item pointer array will already be in CPU cache, so there is no additional access time when we set the hint bits when they are moved to their new position. I should also point out that removing 4 bits from the tuple header would allow us to get rid of t_infomask2, reducing tuple length by a further 2 bytes. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Block-level CRC checks
Simon Riggs wrote: On Fri, 2009-12-04 at 07:12 -0500, Bruce Momjian wrote: I think the hint bit has to be added to the item pointer, by using the offset bits that are already zero, according to Greg Stark. That solution leads to easy programming, no expanding hint bit array, and it is backward compatible so doesn't cause problems for pg_migrator. Seems like a reasonable way forward. As I pointed out here http://archives.postgresql.org/pgsql-hackers/2009-12/msg00056.php we only need to use 3 bits not 4, but it does limit tuple length to 4096 for all block sizes. (Two different options there for doing that). An added advantage of this approach is that the cachelines for the item pointer array will already be in CPU cache, so there is no additional access time when we set the hint bits when they are moved to their new position. I should also point out that removing 4 bits from the tuple header would allow us to get rid of t_infomask2, reducing tuple length by a further 2 bytes. Wow, that is a nice win. Does alignment allow us to actually use that space? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your 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] Block-level CRC checks
On Fri, 2009-12-04 at 07:54 -0500, Bruce Momjian wrote: I should also point out that removing 4 bits from the tuple header would allow us to get rid of t_infomask2, reducing tuple length by a further 2 bytes. Wow, that is a nice win. Does alignment allow us to actually use that space? It would mean that tables up to 24 columns wide would still be 24 bytes wide, whereas 8 columns now has to fit in 32 bytes. So in practical terms most tables would benefit in your average database. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
On Fri, 2009-12-04 at 09:52 -0300, Alvaro Herrera wrote: BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? Much as I would like to see those go, no. VF code should remain for some time yet, IMHO. We could remove it, but doing so is not a priority because it buys us nothing in terms of features and its the type of thing we should do at the start of a release cycle, not end. I certainly don't have time to do it, at least. -- Simon Riggs www.2ndQuadrant.com -- 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] Backup history file should be replicated in Streaming Replication?
Hi, On Thu, Nov 26, 2009 at 5:20 PM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Nov 26, 2009 at 5:17 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Yeah, that needs to be addressed regardless of HS, because you can otherwise start up (= fail over to) the standby too early, before the minimum recovery point has been reached. Okey, I address that ASAP. pg_stop_backup deletes the previous backup history file from pg_xlog. So replication of a backup history file would fail if just one new online-backup is caused after the base-backup for the standby is taken. This is too aggressive deletion policy for Streaming Replication, I think. So I'd like to change pg_stop_backup so as to delete only backup history files of four or more generations ago (four is enough?). Thought? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Block-level CRC checks
Simon Riggs wrote: On Fri, 2009-12-04 at 09:52 -0300, Alvaro Herrera wrote: BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? Much as I would like to see those go, no. VF code should remain for some time yet, IMHO. I don't think we need to keep VF code otherwise, but I would leave HEAP_MOVED_IN/OFF support alone for now for in-place upgrade. Otherwise we need a pre-upgrade script or something to scrub them off. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I don't have a problem with the third explanation ;-). The issue here is really planner speed relative to execution speed, and that's not so hardware-sensitive as all that. Yeah, you can plan a 12-join query way faster than ten years ago, but you can execute it way faster too, and that's what drives expectations for planning speed. Flat-planning a 15-way query costs just as much more relative to a 12-way query as it did ten years ago. Even granted that ratio has stayed the same (and I'd think the planning speed would increase slightly faster than the execution speed, no?), the underlying factor is that there is a magic sweet spot at which we start making tradeoffs for the user. Even if a flat 15-way is the same relative to a 12-way, the absolute numbers should be the prime consideration. In other words, if the average flat plan/execute speed for a 13-way on an average box was 15 seconds in 2000, but 2 seconds in 2009, I would presume that it would now be worth it to consider 13 as needing default geqo coverage. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200912040823 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksZDbkACgkQvJuQZxSWSsjjLgCeKIFStyakHzCQljeqtX2Ie5wi 8fgAoM8ZsXHrVWgmM7UsSP6dKGslQVWK =g6ET -END PGP SIGNATURE- -- 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] Block-level CRC checks
On Fri, Dec 4, 2009 at 12:57 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2009-12-04 at 07:54 -0500, Bruce Momjian wrote: I should also point out that removing 4 bits from the tuple header would allow us to get rid of t_infomask2, reducing tuple length by a further 2 bytes. Wow, that is a nice win. Does alignment allow us to actually use that space? It would mean that tables up to 24 columns wide would still be 24 bytes wide, whereas 8 columns now has to fit in 32 bytes. So in practical terms most tables would benefit in your average database. I don't think getting rid of infomask2 wins us 2 bytes so fast. The rest of those two bytes is natts which of course we still need. If we lose vacuum full then the table's open for reducing the width of command id too if we need more bits. If we do that and we moved everything we could to the line pointers including ctid we might just be able to squeeze the tuple overhead down to 16 bytes. That would win 8 bytes per tuple for people with no null columns or with nulls and a total of 9-64 columns but if they have 1-8 columns and any are null it would actually consume more space. But it looks to me like it would be very very tight and require drastic measures -- I think we would be left with something like 11 bits for commandid and no spare bits in the tuple header at all. -- 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] Block-level CRC checks
On Fri, Dec 4, 2009 at 1:35 PM, Greg Stark gsst...@mit.edu wrote: If we lose vacuum full then the table's open for reducing the width of command id too if we need more bits. If we do that and we moved everything we could to the line pointers including ctid we might just be able to squeeze the tuple overhead down to 16 bytes. I'm not sure why I said including ctid. We would have to move everything transactional to the line pointer, including xmin, xmax, ctid, all the hint bits, the updated flags, hot flags, etc. The only things left in the tuple header would be things that have to be there such as HAS_OIDS, HAS_NULLS, natts, hoff, etc. It would be a pretty drastic change, though a fairly logical one. I recall someone actually submitted a patch to separate out the transactional bits anyways a while back, just to save a few bytes in in-memory tuples. If we could save on disk-space usage it would be a lot more compelling. But it doesn't look to me like it really saves enough often enough to be worth so much code churn. -- 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: [CORE] [HACKERS] EOL for 7.4?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Migration is really only half the story, or not even that much. Every time you move to a new Postgres version you have to do extensive work to revalidate your application. If you don't do that you're just asking for trouble. But it can be painful, expensive and disruptive. I know of places where it can take weeks or months of effort. So the less often you have to do it the better. This would be true even if we had had a perfect working inplace upgrade mechanism for years, which as you and Greg point out is not true. I don't agree with this - migration is much more important than you make out. Testing and validation can be a pain, but it can be done concurrently while your main production site is still chugging along and taking orders. At some point, however, migration *will* cause production downtime[1]. This is one of the Achilles' heel of Postgres, and I'm frankly surprised it has taken us this long to get pg_migrator to a somewhat working state. I don't have any clients who don't/can't upgrade because they can't manage the downtime, but I have more than one avoiding upgrade because of revalidation costs. Well, I certainly had many clients who had major problems dealing with the implicit casts removed in 8.3, but there are also some in which the sheer size of the database is a factor as well. I think Robert Treat can probably chime in on some upgrade woes here too. [1] Okay, there are some tricks to work around this or severely minimize the downtime /Bucardo_plug, but it's still a truism that upgrading versions is a pain and nearly always involves production downtime. - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 200912040846 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksZEqMACgkQvJuQZxSWSsgGyACdEyfIqwFsFrt9ZnQ2DNPVYIWP j08AoK+cLC84HSjlIbzJY8Gz/gAa6D74 =AzuV -END PGP SIGNATURE- -- 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] Block-level CRC checks
Greg Stark escribió: On Fri, Dec 4, 2009 at 1:35 PM, Greg Stark gsst...@mit.edu wrote: If we lose vacuum full then the table's open for reducing the width of command id too if we need more bits. If we do that and we moved everything we could to the line pointers including ctid we might just be able to squeeze the tuple overhead down to 16 bytes. I'm not sure why I said including ctid. We would have to move everything transactional to the line pointer, including xmin, xmax, ctid, all the hint bits, the updated flags, hot flags, etc. The only things left in the tuple header would be things that have to be there such as HAS_OIDS, HAS_NULLS, natts, hoff, etc. It would be a pretty drastic change, though a fairly logical one. Do we need XMAX_EXCL_LOCK and XMAX_SHARED_LOCK to be moved? It seems to me that they can stay with the tuple header because they are set by wal-logged operations. Same for XMAX_IS_MULTI. The HASfoo bits are all set on tuple creation, never touched later, so they can stay in the header too. We only need XMIN_COMMITTED, XMIN_INVALID, XMAX_COMMITTED, XMAX_INVALID, HEAP_COMBOCID on the line pointer AFAICS ... oh, and HEAP_HOT_UPDATED and HEAP_ONLY_TUPLE, not sure. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Block-level CRC checks
Heikki Linnakangas escribió: Simon Riggs wrote: On Fri, 2009-12-04 at 09:52 -0300, Alvaro Herrera wrote: BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? Much as I would like to see those go, no. VF code should remain for some time yet, IMHO. I don't think we need to keep VF code otherwise, but I would leave HEAP_MOVED_IN/OFF support alone for now for in-place upgrade. Otherwise we need a pre-upgrade script or something to scrub them off. CRCs are going to need scrubbing anyway, no? Oh, but you're assuming that CRCs are optional, so not everybody would need that, right? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Block-level CRC checks
On Fri, Dec 4, 2009 at 9:48 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Heikki Linnakangas escribió: Simon Riggs wrote: On Fri, 2009-12-04 at 09:52 -0300, Alvaro Herrera wrote: BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? Much as I would like to see those go, no. VF code should remain for some time yet, IMHO. I don't think we need to keep VF code otherwise, but I would leave HEAP_MOVED_IN/OFF support alone for now for in-place upgrade. Otherwise we need a pre-upgrade script or something to scrub them off. CRCs are going to need scrubbing anyway, no? Oh, but you're assuming that CRCs are optional, so not everybody would need that, right? If we can make not only the validity but also the presence of the CRC field optional, it will simplify things greatly for in-place upgrade, I think, because the upgrade won't itself require expanding the page. Turning on the CRC functionality for a particular table may require expanding the page, but that's a different problem. :-) Have we thought about what other things have changed between 8.4 and 8.5 that might cause problems for in-place upgrade? ...Robert -- 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 6:18 AM, Tim Bunce wrote: - generalize the Safe setup code to enable more control. Is there any possible way to enable use strict; for plperl (trusted) modules? I would love to have that feature. Sure does help cut down on bugs and makes things nicer. -- Jeff Trout j...@jefftrout.com http://www.stuarthamm.net/ http://www.dellsmartexitin.com/ -- 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] [GENERAL] Installing PL/pgSQL by default
Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Hm, I think that's only a problem if we define it to be a Tom problem, and I'm not sure it's necessary to do so. The complaint is that if plpgsql is installed by default, then it will be owned by postgres rather than by the db owner, who will then not be able to drop it or use grant/revoke on it. Right, just like every other thing that's pre-installed. If a particular installation wishes to let individual DB owners control this, the superuser can drop plpgsql from template1. It's not apparent to me why we need to allow non-superusers to override the project's decisions about what should be installed by default. 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] Hot Standby remaining issues
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: If the system is completely idle, and no WAL is written, we skip xlog switches too, even if archive_timeout is set . It would be pointless to create a stream of WAL files with no content except for the XLOG_SWITCH records. It's not pointless if you want to monitor that your backup system is healthy. This was previously mentioned a couple years ago: http://archives.postgresql.org/pgsql-general/2007-10/msg01448.php It turns out that it's been working fine under 8.3. Of course, we can always add a crontab job to do some small bogus update to force WAL switches, so it's not the end of the world if we lose the 8.3 behavior; but my preference would be that if a WAL switch interval is specified, the WAL files switch at least that often. -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] Block-level CRC checks
Simon Riggs si...@2ndquadrant.com writes: As I pointed out here http://archives.postgresql.org/pgsql-hackers/2009-12/msg00056.php we only need to use 3 bits not 4, but it does limit tuple length to 4096 for all block sizes. (Two different options there for doing that). Limiting the tuple length is a deal-breaker. 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] Block-level CRC checks
Greg Stark gsst...@mit.edu writes: I'm not sure why I said including ctid. We would have to move everything transactional to the line pointer, including xmin, xmax, ctid, all the hint bits, the updated flags, hot flags, etc. The only things left in the tuple header would be things that have to be there such as HAS_OIDS, HAS_NULLS, natts, hoff, etc. It would be a pretty drastic change, though a fairly logical one. I recall someone actually submitted a patch to separate out the transactional bits anyways a while back, just to save a few bytes in in-memory tuples. If we could save on disk-space usage it would be a lot more compelling. But it doesn't look to me like it really saves enough often enough to be worth so much code churn. It would also break things for indexes, which don't need all that stuff in their line pointers. More to the point, moving the same bits to someplace else on the page doesn't save anything at all. 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
[HACKERS] Upcoming update releases
Just FYI for all: we are planning to wrap 8.4.2 and other back branch update releases next week, for release Monday the 14th. 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] Block-level CRC checks
On Fri, 2009-12-04 at 10:43 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: As I pointed out here http://archives.postgresql.org/pgsql-hackers/2009-12/msg00056.php we only need to use 3 bits not 4, but it does limit tuple length to 4096 for all block sizes. (Two different options there for doing that). Limiting the tuple length is a deal-breaker. If people that use 32kB block sizes exist in practice, I note that because tuples are at least 4 byte aligned that the first 2 bits of the length are always unused. So they're available for those with strangely long tuples, and can be used to signify high order bytes and so max tuple length could be 16384. With tuples that long, it would be better to assume 8-byte minimum alignment, which would put max tuple length back up to 32KB again. None of that need effect people with a standard 8192 byte blocksize. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
Robert Haas robertmh...@gmail.com writes: Have we thought about what other things have changed between 8.4 and 8.5 that might cause problems for in-place upgrade? So far, nothing. We even made Andrew Gierth jump through hoops to keep hstore's on-disk representation upwards compatible. 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] Block-level CRC checks
On Fri, 2009-12-04 at 13:35 +, Greg Stark wrote: I don't think getting rid of infomask2 wins us 2 bytes so fast. The rest of those two bytes is natts which of course we still need. err, yes, OK. -- Simon Riggs www.2ndQuadrant.com -- 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] First feature patch for plperl - draft [PATCH]
Jeff thres...@threshar.is-a-geek.com writes: Is there any possible way to enable use strict; for plperl (trusted) modules? The plperl manual shows a way to do it using some weird syntax or other. It'd sure be nice to be able to use the regular syntax though. 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] operator exclusion constraints
Robert Haas robertmh...@gmail.com writes: On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) Well, unique constraints are tied to the opclass/index machinery too. Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. 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
[HACKERS] PostgreSQL Release Support Policy
After a great deal of discussion in the community, the project's core team have written a policy outlining the support lifecycle for major PostgreSQL releases, which can be found on the wiki with other project policies at http://wiki.postgresql.org/wiki/Policies. We hope this document will help our users plan their deployments more effectively. The PostgreSQL project aims to fully support a major release for five years. After a release falls out of full support, we may (at our committer's discretion) continue to apply further critical fixes to the source code, on a best-effort basis. No formal releases or binary packages will be produced by the project, but the updated source code will be available from our source code control system. This policy will be followed on a best-effort basis. In extreme cases it may not be possible to support a release for the planned lifetime; for example if a serious bug is found that cannot be resolved in a given major version without significant risk to the stability of the code or loss of application compatibility. In such cases, early retirement of a major version may be required. End Of Life (EOL) dates: Version EOL Date PostgreSQL 7.4 July 2010 (extended) PostgreSQL 8.0 July 2010 (extended) PostgreSQL 8.1 November 2010 PostgreSQL 8.2 December 2011 PostgreSQL 8.3 February 2013 PostgreSQL 8.4 July 2014 -- Dave Page PostgreSQL Core Team -- 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] PostgreSQL Release Support Policy
Hi, On Friday 04 December 2009 17:36:00 Dave Page wrote: Version EOL Date PostgreSQL 7.4July 2010 (extended) PostgreSQL 8.0July 2010 (extended) PostgreSQL 8.1November 2010 PostgreSQL 8.2December 2011 PostgreSQL 8.3February 2013 PostgreSQL 8.4July 2014 What about adding the shortened windows EOLs there? Andres -- 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] operator exclusion constraints
On Fri, 2009-12-04 at 11:35 -0500, Tom Lane wrote: Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. Sounds fine to me. Regards, Jeff Davis -- 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 3:18 AM, Tim Bunce wrote:
The perl code in plperl.on_perl_init gets eval'd as soon as an
interpreter is created. That could be at server startup if
shared_preload_libraries is used. plperl.on_perl_init can only be set by
an admin (PGC_SUSET).
Are multiline GUCs allowed in the postgresql.conf file?
The perl code in plperl.on_trusted_init gets eval'd when an interpreter
is initialized into trusted mode, e.g., used for the plperl language.
The perl code is eval'd inside the Safe compartment.
plperl.on_trusted_init can be set by users but it's only useful if set
before the plperl interpreter is first used.
So immediately after connecting would be the place to make sure you do it, IOW.
plperl.on_untrusted_init acts like plperl.on_trusted_init but for
plperlu code.
So, if all three were set then, before any perl stored procedure or DO
block is executed, the interpreter would have executed either
on_perl_init and then on_trusted_init (for plperl), or on_perl_init and
then on_untrusted_init (for plperlu).
Awesome, thanks! This is really a great feature.
along with quote_nullable(), which might also be useful to add.
I was planning to build that behaviour into quote_literal since it fits
naturally into perl's idea of undef and mirrors DBI's quote() method.
So:
quote_literal(undef) = NULL
quote_literal('foo') = 'foo'
Is there an existing `quote_literal()` in PL/Perl? If so, you might not want to
change its behavior.
- generalize the Safe setup code to enable more control.
Specifically control what gets loaded into the Compartment, what gets
shared with it (e.g. sharing *a *b as a workaround for the sort bug),
and what class to use for Safe (to enable deeper changes if desired via
subclassing). Naturally all this is only possible for admin (via
plperl.on_perl_init).
Sounds good.
- formalize namespace usage, moving things out of main::
Nice.
- add a way to perform inter-sub calling (at least for simple cases).
My current plan here is to use an SP::AUTOLOAD to handle loading and
dispatching. So calling SP::some_random_procedure(...) will trigger
SP::AUTOLOAD to try to resolve some_random_procedure to a particular
stored procedure. There are three tricky parts: handling polymorphism (at
least well enough), making autoloading of stored procedures work
inside Safe, making it fast. I think I have reasonable approaches for
those but I won't know for sure till I work on it.
I'm wondering if there might be some way to use some sort of attributes to
identify data types passed to a PL/Perl function called from another PL/Perl
function. Maybe some other functions that identify types, in the case of
ambiguities?
foo(int(1), text('bar'));
? Kind of ugly, but perhaps only to be used if there are ambiguities? Not sure
it's a great idea, mind. Just thinking out loud (so to speak).
Best,
David
--
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] operator exclusion constraints
On Dec 4, 2009, at 11:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) Well, unique constraints are tied to the opclass/index machinery too. Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. Go for it. Membership has its privileges. :-) ...Robert -- 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] New VACUUM FULL
On Fri, 2009-12-04 at 09:20 +, Simon Riggs wrote:
On Tue, 2009-12-01 at 01:43 -0800, Jeff Davis wrote:
Marking as ready.
You're saying its ready, yet there are 3 additional suggestions patches
attached here. Please can you resolve these and re-submit a single final
patch from author and reviewer?
My apologies. At the time, I thought a couple days might matter, and the
changes are in areas that committers tend to editorialize anyway: docs
and a style issue. The only substantial patch was to vacuumdb.c.
Complete patch attached including my edits.
* What happens if you issue VACUUM FULL; which we would expect to use
the new method of vacuum on all tables in the database. Won't that just
fail with an error when it comes to catalog tables? Sounds to me like we
should avoid the error and just silently do an INPLACE on catalog
tables.
That's how it works.
* Such a pivotal change to Postgres needs more test coverage than a
single line in regression tests. It might have been OK before, but I
think we need a few more combinations here, at least in this release:
with, without indexes, empty table, clustered, non-clustered etc and of
course a full database VACUUM so that we have the catalog table case
covered, plus an explicit catalog table vacuum.
It was my impression that the regression tests aren't meant to be
exhaustive, but merely exercise a good portion of the code to help
detect simple breakage. Also, pg_regress isn't good for detecting a lot
of the problems that vacuum might have (how do you even know whether the
vacuum happened in-place or not?).
We could put a VACUUM FULL; and a VACUUM (FULL INPLACE); somewhere,
which will cover a lot of the cases you're talking about. However, that
may be a performance consideration especially for people who develop on
laptops.
In general, though, I think the right place for this is a longer test
suite that is meant to be run less frequently.
Regards,
Jeff Davis
*** a/doc/src/sgml/ref/vacuum.sgml
--- b/doc/src/sgml/ref/vacuum.sgml
***
*** 21,27 PostgreSQL documentation
refsynopsisdiv
synopsis
! VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ replaceable class=PARAMETERtable/replaceable [ (replaceable class=PARAMETERcolumn/replaceable [, ...] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ replaceable class=PARAMETERtable/replaceable ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ replaceable class=PARAMETERtable/replaceable [ (replaceable class=PARAMETERcolumn/replaceable [, ...] ) ] ]
/synopsis
--- 21,27
refsynopsisdiv
synopsis
! VACUUM [ ( { FULL [ INPLACE ] | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ replaceable class=PARAMETERtable/replaceable [ (replaceable class=PARAMETERcolumn/replaceable [, ...] ) ] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ replaceable class=PARAMETERtable/replaceable ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ replaceable class=PARAMETERtable/replaceable [ (replaceable class=PARAMETERcolumn/replaceable [, ...] ) ] ]
/synopsis
***
*** 86,91 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ replaceable class=PARAMETER
--- 86,112
Selects quotefull/quote vacuum, which can reclaim more
space, but takes much longer and exclusively locks the table.
/para
+ para
+ For user tables, all table data and indexes are rewritten. This
+ method requires extra disk space in which to write the new data,
+ and is generally useful when a significant amount of space needs
+ to be reclaimed from within the table.
+ /para
+ para
+ For system tables, all table data and indexes are modified in
+ place to reclaim space. This method may require less disk space
+ for the table data than commandVACUUM FULL/command on a
+ comparable user table, but the indexes will grow which may
+ counteract that benefit. Additionally, the operation is often
+ slower than commandVACUUM FULL/command on a comparable user
+ table.
+ /para
+ para
+ If literalFULL INPLACE/literal is specified, the space is
+ reclaimed in the same manner as a system table, even if it is a
+ user table. Specifying literalINPLACE/literal explicitly is
+ rarely useful.
+ /para
/listitem
/varlistentry
*** a/doc/src/sgml/ref/vacuumdb.sgml
--- b/doc/src/sgml/ref/vacuumdb.sgml
***
*** 24,29 PostgreSQL documentation
--- 24,30
commandvacuumdb/command
arg rep=repeatreplaceableconnection-option/replaceable/arg
grouparg--full/argarg-f/arg/group
+grouparg--inplace/argarg-i/arg/group
grouparg--verbose/argarg-v/arg/group
grouparg--analyze/argarg-z/arg/group
grouparg--freeze/argarg-F/arg/group
***
*** 36,41 PostgreSQL documentation
--- 37,43
arg rep=repeatreplaceableconnection-options/replaceable/arg
grouparg--all/argarg-a/arg/group
Re: [HACKERS] New VACUUM FULL
On Fri, 2009-12-04 at 09:56 -0800, Jeff Davis wrote: We could put a VACUUM FULL; and a VACUUM (FULL INPLACE); somewhere, which will cover a lot of the cases you're talking about. However, that may be a performance consideration especially for people who develop on laptops. Let's check it works before worrying about performance. We can take tests out as well as add them once it becomes obvious its working. -- Simon Riggs www.2ndQuadrant.com -- 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] First feature patch for plperl - draft [PATCH]
David E. Wheeler da...@kineticode.com writes: On Dec 4, 2009, at 3:18 AM, Tim Bunce wrote: The perl code in plperl.on_perl_init gets eval'd as soon as an interpreter is created. That could be at server startup if shared_preload_libraries is used. plperl.on_perl_init can only be set by an admin (PGC_SUSET). Are multiline GUCs allowed in the postgresql.conf file? I don't think so. In any case this seems like an extreme abuse of the concept of a GUC, as well as being a solution in search of a problem, as well as being something that should absolutely not ever happen inside the postmaster process for both reliability and security reasons. I vote a big no on this. 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
[HACKERS] CVS HEAD: Error accessing system column from plpgsql trigger function
With CVS HEAD, I'm getting the following error when I try to access a system column from within trigger (which I'm doing just for debug purposes): create table foo (a int); create or replace function foo_trig_fn() returns trigger as $$ begin raise notice 'In trigger: added %', new.ctid; return new; end $$ language plpgsql; create trigger foo_trig after insert on foo for each row execute procedure foo_trig_fn(); insert into foo values(1); ERROR: attribute number -1 exceeds number of columns 1 As far as I've been able to work out, this started after this commit: http://archives.postgresql.org/pgsql-committers/2009-11/msg00035.php but that's an area of the code I know nothing about. It looks like a change to the parsing code is causing it to use a FieldSelect node for the system attribute, where it didn't used to. - Dean -- 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 10:36 AM, Tom Lane wrote: Are multiline GUCs allowed in the postgresql.conf file? I don't think so. In any case this seems like an extreme abuse of the concept of a GUC, as well as being a solution in search of a problem, as well as being something that should absolutely not ever happen inside the postmaster process for both reliability and security reasons. I vote a big no on this. That's fine. It's relatively simple for an admin to create a Perl module that does everything she wants, call it PGInit or something, and then just make the GUC: plperl.on_perl_init = 'use PGInit;' Best, David -- 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] First feature patch for plperl - draft [PATCH]
Tom Lane wrote: Jeff thres...@threshar.is-a-geek.com writes: Is there any possible way to enable use strict; for plperl (trusted) modules? The plperl manual shows a way to do it using some weird syntax or other. It'd sure be nice to be able to use the regular syntax though. As is documented, all you have to do is have: custom_variable_classes = 'plperl' plperl.use_strict = 'true' in your config. You only need to put the documented BEGIN block in your function body if you want to do use strict mode on a case by case basis. We can't allow an unrestricted use strict; in plperl functions because it invokes an operation (require) that Safe.pm rightly regards as unsafe. cheers andrew -- 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] First feature patch for plperl - draft [PATCH]
David E. Wheeler da...@kineticode.com writes: On Dec 4, 2009, at 10:36 AM, Tom Lane wrote: I vote a big no on this. That's fine. It's relatively simple for an admin to create a Perl module that does everything she wants, call it PGInit or something, and then just make the GUC: plperl.on_perl_init = 'use PGInit;' No, you missed the point: I'm objecting to having any such thing as plperl.on_perl_init, full stop. Aside from the points I already made, it's not even well defined. What is to happen if the admin changes the value when the system is already up? 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 1:44 PM, Andrew Dunstan wrote: As is documented, all you have to do is have: custom_variable_classes = 'plperl' plperl.use_strict = 'true' in your config. You only need to put the documented BEGIN block in your function body if you want to do use strict mode on a case by case basis. We can't allow an unrestricted use strict; in plperl functions because it invokes an operation (require) that Safe.pm rightly regards as unsafe. Yeah, saw that in the manual in the plperl functions arguments page (at the bottom). I think my confusion came up because I'd read the trust/untrusted thing which removes the ability to use use/require. Maybe a blurb or moving that chunk of doc to the trusted/untrusted page might make that tidbit easier to find? -- Jeff Trout j...@jefftrout.com http://www.stuarthamm.net/ http://www.dellsmartexitin.com/ -- 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] First feature patch for plperl - draft [PATCH]
On Fri, Dec 4, 2009 at 1:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: David E. Wheeler da...@kineticode.com writes: On Dec 4, 2009, at 10:36 AM, Tom Lane wrote: I vote a big no on this. That's fine. It's relatively simple for an admin to create a Perl module that does everything she wants, call it PGInit or something, and then just make the GUC: plperl.on_perl_init = 'use PGInit;' No, you missed the point: I'm objecting to having any such thing as plperl.on_perl_init, full stop. Aside from the points I already made, it's not even well defined. What is to happen if the admin changes the value when the system is already up? So, do we look for another way to provide the functionality besides having a GUC, or is the functionality itself bad? ...Robert -- 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] Block-level CRC checks
Robert Haas wrote: On Fri, Dec 4, 2009 at 9:48 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Heikki Linnakangas escribi?: Simon Riggs wrote: On Fri, 2009-12-04 at 09:52 -0300, Alvaro Herrera wrote: BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? Much as I would like to see those go, no. VF code should remain for some time yet, IMHO. I don't think we need to keep VF code otherwise, but I would leave HEAP_MOVED_IN/OFF support alone for now for in-place upgrade. Otherwise we need a pre-upgrade script or something to scrub them off. CRCs are going to need scrubbing anyway, no? ?Oh, but you're assuming that CRCs are optional, so not everybody would need that, right? If we can make not only the validity but also the presence of the CRC field optional, it will simplify things greatly for in-place upgrade, I think, because the upgrade won't itself require expanding the page. Turning on the CRC functionality for a particular table may require expanding the page, but that's a different problem. :-) Well, I am not sure how we would turn the _space_ used for CRC on and off because you would have to rewrite the entire table/database to turn it on, which seems unfortunate. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your 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] First feature patch for plperl - draft [PATCH]
Robert Haas robertmh...@gmail.com writes: So, do we look for another way to provide the functionality besides having a GUC, or is the functionality itself bad? I don't think we want random Perl code running inside the postmaster, no matter what the API to cause it is. I might hold my nose for on load code if it can only run in backends, though I still say that it's a badly designed concept because of the uncertainty about who will run what when. Shlib load time is not an event that ought to be user-visible. 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 10:51 AM, Tom Lane wrote: plperl.on_perl_init = 'use PGInit;' No, you missed the point: I'm objecting to having any such thing as plperl.on_perl_init, full stop. Aside from the points I already made, it's not even well defined. What is to happen if the admin changes the value when the system is already up? Nothing. Hence the init. Best, David -- 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 11:05 AM, Tom Lane wrote: So, do we look for another way to provide the functionality besides having a GUC, or is the functionality itself bad? I don't think we want random Perl code running inside the postmaster, no matter what the API to cause it is. I might hold my nose for on load code if it can only run in backends, though I still say that it's a badly designed concept because of the uncertainty about who will run what when. Shlib load time is not an event that ought to be user-visible. So only the child processes would be allowed to load the code? That could make connections even slower if there's a lot of Perl code to be added, though that's also the issue we have today. I guess I could live with that, though I'd rather have such code shared across processes. If it's a badly designed concept, do you have any ideas that are less bad? Best, David -- 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] First feature patch for plperl - draft [PATCH]
On Fri, Dec 4, 2009 at 2:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So, do we look for another way to provide the functionality besides having a GUC, or is the functionality itself bad? I don't think we want random Perl code running inside the postmaster, no matter what the API to cause it is. I might hold my nose for on load code if it can only run in backends, though I still say that it's a badly designed concept because of the uncertainty about who will run what when. Shlib load time is not an event that ought to be user-visible. I agree that the uncertainty is not a wonderful thing, but e.g. Apache has the same problem with mod_perl, and you just deal with it. I choose to deal with it by doing apachectl graceful every time I change the source code; or you can install Perl modules that check whether the mod-times on the other modules you've loaded have changed and reload them if so. In practice, being able to pre-load the Perl libraries you're going to want to execute is absolutely essential if you don't want performance to be in the toilet. My code base is so large now that it takes 3 or 4 seconds for Apache to pull it all in on my crappy dev box, but it's blazingly fast once it's up and running. Having that be something that happens on the production server only once a week or once a month when I roll out a new release rather than any more frequently is really important. ...Robert -- 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] First feature patch for plperl - draft [PATCH]
On Fri, Dec 04, 2009 at 11:01:42AM -0500, Tom Lane wrote: Jeff thres...@threshar.is-a-geek.com writes: Is there any possible way to enable use strict; for plperl (trusted) modules? The plperl manual shows a way to do it using some weird syntax or other. It'd sure be nice to be able to use the regular syntax though. Finding a solution is definitely on my list. I've spent a little time exploring this already but haven't found a simple solution yet. The neatest would have been overriding CORE::GLOBAL::require but sadly the Safe/Opcode mechanism takes priority over that and forbids compiling code that does a use/require. I may end up re-enabling the require opcode but redirecting it to run some C code in plperl.c (the same 'opcode redirection' technique used by my NYTProf profiler). That C code would only need to throw an exception if the module hasn't been loaded already. Tim. -- 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] Initial refactoring of plperl.c - rebased [PATCH]
On Thu, Dec 03, 2009 at 03:47:21PM -0800, Josh Berkus wrote: Tim, Since there's a commitfest on right now, meaningful feedback on your patch could be delayed. Just so you know. Understood. Thanks Josh. Tim. -- 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] First feature patch for plperl - draft [PATCH]
David E. Wheeler escribió: If it's a badly designed concept, do you have any ideas that are less bad? I'm not sure that we want to duplicate this idea today, but in pltcl there's a pltcl_modules table that is scanned on interpreter init and loads user-defined code. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Block-level CRC checks
On Fri, Dec 4, 2009 at 2:04 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Fri, Dec 4, 2009 at 9:48 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Heikki Linnakangas escribi?: Simon Riggs wrote: On Fri, 2009-12-04 at 09:52 -0300, Alvaro Herrera wrote: BTW with VACUUM FULL removed I assume we're going to get rid of HEAP_MOVED_IN and HEAP_MOVED_OFF too, right? Much as I would like to see those go, no. VF code should remain for some time yet, IMHO. I don't think we need to keep VF code otherwise, but I would leave HEAP_MOVED_IN/OFF support alone for now for in-place upgrade. Otherwise we need a pre-upgrade script or something to scrub them off. CRCs are going to need scrubbing anyway, no? ?Oh, but you're assuming that CRCs are optional, so not everybody would need that, right? If we can make not only the validity but also the presence of the CRC field optional, it will simplify things greatly for in-place upgrade, I think, because the upgrade won't itself require expanding the page. Turning on the CRC functionality for a particular table may require expanding the page, but that's a different problem. :-) Well, I am not sure how we would turn the _space_ used for CRC on and off because you would have to rewrite the entire table/database to turn it on, which seems unfortunate. Well, presumably you're going to have to do some of that work anyway, because even if the space is set aside you're still going to have to read the page in, CRC it, and write it back out. However if the space is not pre-allocated then you also have to deal with moving tuples to other pages. But that problem is going to have to be dealt with somewhere along the line no matter what we do, because if you're upgrading an 8.3 or 8.4 system to 8.5, you need to add that space sometime: either before migration (with a pre-upgrade utility), or after migration (by some sort of page converter/tuple mover), or only when/if enabling the CRC feature. One nice thing about making it the CRC feature's problem to make space on each page is that people who don't want to use CRCs can still use those extra 4 bytes/page for data. That might not be worth the code complexity if we were starting from scratch, but I'm thinking that most of the code complexity is a given if we want to also support in-place upgrade. ...Robert -- 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] Block-level CRC checks
Massa, Harald Armin wrote: I am in the process of adding a user-space myhash column to all my applications tables, filled by a trigger on insert / update. It really speeds up table comparison across databases; and it is very helpfull in debugging replications. Have you seen pg_comparator? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] PostgreSQL Release Support Policy
On Fri, Dec 4, 2009 at 5:27 PM, Andres Freund and...@anarazel.de wrote: Hi, On Friday 04 December 2009 17:36:00 Dave Page wrote: Version EOL Date PostgreSQL 7.4 July 2010 (extended) PostgreSQL 8.0 July 2010 (extended) PostgreSQL 8.1 November 2010 PostgreSQL 8.2 December 2011 PostgreSQL 8.3 February 2013 PostgreSQL 8.4 July 2014 What about adding the shortened windows EOLs there? Sorry - meant to reply to this earlier. I added a footnote about the windows support for = 8.1 -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] First feature patch for plperl - draft [PATCH]
On Fri, Dec 04, 2009 at 02:05:28PM -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: So, do we look for another way to provide the functionality besides having a GUC, or is the functionality itself bad? I don't think we want random Perl code running inside the postmaster, no matter what the API to cause it is. I might hold my nose for on load code if it can only run in backends, though I still say that it's a badly designed concept because of the uncertainty about who will run what when. Robert's comparison with mod_perl is very apt. Preloading code gives dramatic performance gains in production situations where there's a significant codebase and connections are frequent. The docs for plperl.on_perl_init could include a section relating to it's use with shared_preload_libraries. That could document any issues and caveats you feel are important. Tim. -- 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] Block-level CRC checks
Robert Haas wrote: Well, I am not sure how we would turn the _space_ used for CRC on and off because you would have to rewrite the entire table/database to turn it on, which seems unfortunate. Well, presumably you're going to have to do some of that work anyway, because even if the space is set aside you're still going to have to read the page in, CRC it, and write it back out. However if the space is not pre-allocated then you also have to deal with moving tuples to other pages. But that problem is going to have to be dealt with somewhere along the line no matter what we do, because if you're upgrading an 8.3 or 8.4 system to 8.5, you need to add that space sometime: either before migration (with a pre-upgrade utility), or after migration (by some sort of page converter/tuple mover), or only when/if enabling the CRC feature. One nice thing about making it the CRC feature's problem to make space on each page is that people who don't want to use CRCs can still use those extra 4 bytes/page for data. That might not be worth the code complexity if we were starting from scratch, but I'm thinking that most of the code complexity is a given if we want to also support in-place upgrade. My guess is we can find somewhere on a 8.4 heap/index page to add four bytes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your 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] First feature patch for plperl - draft [PATCH]
Le 4 déc. 2009 à 20:40, Tim Bunce a écrit : Robert's comparison with mod_perl is very apt. Preloading code gives dramatic performance gains in production situations where there's a significant codebase and connections are frequent. How far do you go with using a connection pooler such as pgbouncer? -- dim -- 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] add more frame types in window functions (ROWS)
Functionally this patch looks excellent; correct format, applies cleanly, passes regression, and I've been unable to find any issues with the code itself. But I still have a concern over the interface change, so I'm setting this back to waiting on author for now even though it's really a matter for general discussion on -hackers. To take the outstanding issues in descending order of importance: 1) Memory context handling for aggregate calls Having thought about this carefully, I think the solution used in this patch has to be rejected for one specific reason: if you compile existing code (i.e. aggregates that use WindowAggState.wincontext) written for 8.4 against the patched server, the code compiles successfully and appears to run, but leaks memory at runtime. (And I've confirmed that there do exist external modules that would be affected.) If we're going to change the interface in this way, there should, IMO, be enough of a change that old code fails to compile; e.g. by renaming wincontext to partition_context or some equivalent change. But in my opinion we should go further than this: since there's obviously a need for aggregates to be able to get at a suitable memory context, I think we should formalize this and actually define some interface functions for them to use, so that something like if (fcinfo-context IsA(fcinfo-context, AggState)) aggcontext = ((AggState *) fcinfo-context)-aggcontext; else if (fcinfo-context IsA(fcinfo-context, WindowAggState)) aggcontext = ((WindowAggState *) fcinfo-context)-aggcontext; else ereport(...); becomes something like aggcontext = AggGetMemoryContext(fcinfo-context); if (!aggcontext) ereport(...); For completeness, there should be two other functions: one to simply return whether we are in fact being called as an aggregate, and another one to return whether it's safe to scribble on the first argument (while it's currently the case that these two are equivalent, it would be good not to assume that). Comments? This is the most significant issue as I see it. (Also, a function in contrib/tsearch2 that accesses wincontext wasn't updated by the patch.) 2) Keywords I didn't find any discussion of this previously; did I miss it? The patch changes BETWEEN from TYPE_FUNC_NAME_KEYWORD to COL_NAME_KEYWORD, so it's now allowed as a column name (which it previously wasn't), but now not allowed as a function. I get why it can't be a function now, but if it didn't work as a column name before, why does it now? (UNBOUNDED remains an unreserved word, and seems to behave in a reasonable fashion even if used as an upper-level var in the query; the interpretation of a bare UNBOUNDED has the standard behaviour as far as I can see.) 3) Regression tests Testing that views work is OK as far as it goes, but I think that view definition should be left in place rather than dropped (possibly with even more variants) so that the deparse code gets properly tested too. (see the rules test) 4) Deparse output The code is forcing explicit casting on the offset expressions, i.e. the deparsed code looks like ... ROWS BETWEEN 1::bigint PRECEDING AND 1::bigint FOLLOWING ... This looks a bit ugly; is it avoidable? At least for ROWS it should be, I think, since the type is known; even for RANGE, the type would be determined by the sort column. 5) Documentation issues The entry for BETWEEN in the keywords appendix isn't updated. (Wouldn't it make more sense for this to be generated from the keyword list somehow?) Spelling: - current row. In literalROWS/ mode this value means phisical row + current row. In literalROWS/ mode this value means physical row (this error appears twice) The doc could probably do with some wordsmithing but this probably shouldn't block the patch on its own; that's something that could be handled separately I guess. -- Andrew. -- 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] pg_ctl init extension
I attached updated patch and doc patch.
Zdenek
Peter Eisentraut píše v so 21. 11. 2009 v 13:19 +0200:
On lör, 2009-11-14 at 14:50 +0100, Zdenek Kotala wrote:
Peter Eisentraut píše v so 14. 11. 2009 v 10:41 +0200:
On tor, 2009-09-17 at 21:43 +0200, Zdenek Kotala wrote:
Attached patch extends pg_ctl command with init option.
pg_ctl -D /var/lib/postgres [-s] init
This should replace usage of initdb command which has problematic name
as we already discussed several times. Initdb binary will be still
there, but it can be renamed and move into execlib dir in the future.
Patch does not contains documentation changes. They will depends on
decision which database initialization method will be preferred.
OK, let's see. The patch is pretty straightforward, but does anyone
else actually want this? Comments?
Maybe we could ask on general where is more admins. I will send voting
email.
I think this is over now. There was some support, some don't care, but
could make sense, and no one violently objecting, so please finish the
patch up with documentation, and it can go in as far as I'm concerned.
Someone was proposing that pg_ctl initdb be an alias to pg_ctl init.
Perhaps you could add that.
diff -r 2d87758e836b src/bin/pg_ctl/pg_ctl.c
--- a/src/bin/pg_ctl/pg_ctl.c Sun Nov 22 22:06:30 2009 +
+++ b/src/bin/pg_ctl/pg_ctl.c Fri Dec 04 22:13:28 2009 +0100
@@ -57,6 +57,7 @@
typedef enum
{
NO_COMMAND = 0,
+ INIT_COMMAND,
START_COMMAND,
STOP_COMMAND,
RESTART_COMMAND,
@@ -100,6 +101,7 @@
static void do_help(void);
static void set_mode(char *modeopt);
static void set_sig(char *signame);
+static void do_init(void);
static void do_start(void);
static void do_stop(void);
static void do_restart(void);
@@ -615,6 +617,38 @@
}
static void
+do_init(void)
+{
+ char pg_initdb[MAXPGPATH];
+ char cmd[MAXPGPATH];
+ int ret;
+
+ if ((ret = find_other_exec(argv0, initdb, initdb (PostgreSQL) PG_VERSION \n,
+ pg_initdb)) 0)
+ {
+ write_stderr(_(%s: could not find initdb\n),
+ progname);
+ exit(1);
+ }
+
+ if (post_opts == NULL)
+ post_opts = ;
+
+ if (!silent_mode)
+ snprintf(cmd, MAXPGPATH, SYSTEMQUOTE \%s\ %s%s SYSTEMQUOTE,
+ pg_initdb, pgdata_opt, post_opts);
+ else
+ snprintf(cmd, MAXPGPATH, SYSTEMQUOTE \%s\ %s%s \%s\ SYSTEMQUOTE,
+ pg_initdb, pgdata_opt, post_opts, DEVNULL);
+
+ if ( system(cmd) != 0 )
+ {
+ write_stderr(_(%s: database initialization failed.\n), progname);
+ exit(1);
+ }
+}
+
+static void
do_start(void)
{
pgpid_t pid;
@@ -1536,6 +1570,7 @@
printf(_(%s is a utility to start, stop, restart, reload configuration files,\n
report the status of a PostgreSQL server, or signal a PostgreSQL process.\n\n), progname);
printf(_(Usage:\n));
+ printf(_( %s init[db] [-D DATADIR] [-s] [-o \OPTIONS\]\n), progname);
printf(_( %s start [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o \OPTIONS\]\n), progname);
printf(_( %s stop[-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]\n), progname);
printf(_( %s restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]\n
@@ -1568,7 +1603,7 @@
#endif
printf(_( -l, --log FILENAME write (or append) server log to FILENAME\n));
printf(_( -o OPTIONS command line options to pass to postgres\n
- (PostgreSQL server executable)\n));
+ (PostgreSQL server executable) or initdb\n));
printf(_( -p PATH-TO-POSTGRESnormally not necessary\n));
printf(_(\nOptions for stop or restart:\n));
printf(_( -m SHUTDOWN-MODE can be \smart\, \fast\, or \immediate\\n));
@@ -1825,7 +1860,11 @@
exit(1);
}
- if (strcmp(argv[optind], start) == 0)
+ if (strcmp(argv[optind], init) == 0)
+ctl_command = INIT_COMMAND;
+ else if (strcmp(argv[optind], initdb) == 0)
+ctl_command = INIT_COMMAND;
+ else if (strcmp(argv[optind], start) == 0)
ctl_command = START_COMMAND;
else if (strcmp(argv[optind], stop) == 0)
ctl_command = STOP_COMMAND;
@@ -1922,6 +1961,9 @@
switch (ctl_command)
{
+ case INIT_COMMAND:
+ do_init();
+ break;
case STATUS_COMMAND:
do_status();
break;
diff -r 5af127be4a67 doc/src/sgml/config.sgml
--- a/doc/src/sgml/config.sgml Mon Nov 23 09:06:21 2009 +0100
+++ b/doc/src/sgml/config.sgml Fri Dec 04 21:04:34 2009 +0100
@@ -114,8 +52,8 @@
para
One way to set these parameters is to edit the file
filenamepostgresql.conf/indextermprimarypostgresql.conf//,
-which is normally kept in the data directory. (applicationinitdb/
-installs a default copy there.) An example of what this file might look
+which is normally kept in the data directory. (database cluster initialization
+process installs a default copy there.) An example of what this file might look
like is:
programlisting
# This is a comment
@@ -425,7 +363,7 @@
Determines the maximum number
Re: [HACKERS] Block-level CRC checks
A curiosity question regarding torn pages: How does this work on file systems that don't write in-place, but instead always do copy-on-write? My example would be Sun's ZFS file system (In Solaris BSD). Because of its snapshot rollback functionality, it never writes a page in-place, but instead always copies it to another place on disk. How does this affect the corruption caused by a torn write? Can we end up with horrible corruption on this type of filesystem where we wouldn't on normal file systems, where we are writing to a previously zeroed area on disk? Sorry if this is a stupid question... Hopefully somebody can reassure me that this isn't an issue. -- 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] New VACUUM FULL
On Fri, 2009-12-04 at 18:36 +, Simon Riggs wrote: Let's check it works before worrying about performance. We can take tests out as well as add them once it becomes obvious its working. Itagaki-san, perhaps you should add a variety of tests, and then Simon can remove extra tests after he's convinced that it works. I tested a variety of situations during my review, and everything worked as I expected. Regards, Jeff Davis -- 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] First feature patch for plperl - draft [PATCH]
On Dec 4, 2009, at 11:40 AM, Tim Bunce wrote: Robert's comparison with mod_perl is very apt. Preloading code gives dramatic performance gains in production situations where there's a significant codebase and connections are frequent. The docs for plperl.on_perl_init could include a section relating to it's use with shared_preload_libraries. That could document any issues and caveats you feel are important. +1 Tom, what's your objection to Shlib load time being user-visible? Best, David -- 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] First feature patch for plperl - draft [PATCH]
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: So, do we look for another way to provide the functionality besides having a GUC, or is the functionality itself bad? I don't think we want random Perl code running inside the postmaster, no matter what the API to cause it is. I might hold my nose for on load code if it can only run in backends, though I still say that it's a badly designed concept because of the uncertainty about who will run what when. Shlib load time is not an event that ought to be user-visible. But you can load an arbitrary shared lib inside the postmaster and it can do what it likes, so I'm not clear that your caution is actually saving us from much. cheers andrew -- 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] YAML Was: CommitFest status/management
On Wed, Dec 2, 2009 at 4:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ron Mayer rm...@cheapcomplexdevices.com writes: Tom Lane wrote: Hmm. So the argument for it is let's make a machine-readable format more human-readable? I'm not getting the point. People should look at the regular text output. IMHO YAML beats the regular text format for human-readability - at least for people with narrow terminal windows, and for novices. Greg posted examples comparing regular-text vs yaml vs json here: http://archives.postgresql.org/pgsql-hackers/2009-08/msg02090.php Mph. Maybe I've been looking at the traditional format too long, but I don't find the YAML version better --- it's so verbose that you could only see a small fraction of a query at a time. I've been thinking about this a little more and I think I am going to vote (if I get a vote) to reject this patch. I think Andrew hit the crucial point upthread: the latest version of YAML is a superset of JSON, which means that the only possible use cases for this patch are: - people using older YAML parsers that can't handle the latest version (because if they can handle the latest version then they can just use that on the existing JSON format), and - people who want to use the YAML format as a substitute for text format on grounds of readability. The first target doesn't seem worth aiming at. Admittedly the latest version of the YAML 1.2 spec - whose stated goal is JSON-compatibilty - is only two months old, so there may be many YAML users who don't have parsers that are completely JSON-compatible. But presumably this problem will resolve itself over time. With respect to the second one, I am not going to argue that the current text format is ideal in all ways. In particular, on complex plans, I find it difficult to match up the plans for the inner and outer sides of any given node, which may be far enough apart vertically that it's difficult to tell exactly which bits are in the same column. Furthermore, the main output row for each node is wider than I would like, especially when using EXPLAIN ANALYZE. Even on relatively simple plans, I have to maximize my terminal window to forestall wrapping, and on complex plans, wrapping is inevitable even if I do maximize the window. For all of that, in the nearly-two-years I've been reading pgsql-hackers, I can't remember one complaint about the visual presentation of the EXPLAIN output. It's possible that my memory is faulty, but I think I would remember if there had been very many. On top of that, if you did want YAML for easier readability, what aspect of the output is more readable in YAML than it is in text format? The only answer I can think of is that you like having each data element on a separate line, so that the plan is much longer but somewhat narrower. But if that's what you want, the JSON output is almost as good - the only difference is a bit of extra punctuation. ...Robert -- 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] YAML Was: CommitFest status/management
On top of that, if you did want YAML for easier readability, what aspect of the output is more readable in YAML than it is in text format? The only answer I can think of is that you like having each data element on a separate line, so that the plan is much longer but somewhat narrower. But if that's what you want, the JSON output is almost as good - the only difference is a bit of extra punctuation. almost as good ... I agree with Kevin that it's more readable. The whole patch just adds 144 lines. It doesn't look to me like there's significant maintenance burden involved, but of course I need to defer to the more experienced. It's even possible that we could reduce the size of the patch still further if we really looked at it as just a differently punctuated JSON. Having compared the JSON and YAML output formats, I think having YAML as a 2nd human-readable format might be valuable, even though it adds nothing to machine-processing. Again, if there were a sensible way to do YAML as a contrib module, I'd go for that, but there isn't. --Josh Berkus -- 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] New VACUUM FULL
On Dec 4, 2009, at 18:07 , Jeff Davis wrote: On Fri, 2009-12-04 at 18:36 +, Simon Riggs wrote: Let's check it works before worrying about performance. We can take tests out as well as add them once it becomes obvious its working. Itagaki-san, perhaps you should add a variety of tests, and then Simon can remove extra tests after he's convinced that it works. I tested a variety of situations during my review, and everything worked as I expected. Would there be a way for you to package the scenarios you tested into a suite? 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] Block-level CRC checks
On Fri, 2009-12-04 at 14:47 -0800, Chuck McDevitt wrote: A curiosity question regarding torn pages: How does this work on file systems that don't write in-place, but instead always do copy-on-write? My example would be Sun's ZFS file system (In Solaris BSD). Because of its snapshot rollback functionality, it never writes a page in-place, but instead always copies it to another place on disk. How does this affect the corruption caused by a torn write? Can we end up with horrible corruption on this type of filesystem where we wouldn't on normal file systems, where we are writing to a previously zeroed area on disk? Sorry if this is a stupid question... Hopefully somebody can reassure me that this isn't an issue. Think we're still good. Not a stupid question. Hint bits are set while the block is in shared_buffers and setting a hint bit dirties the page, but does not write WAL. Because the page is dirty we re-write the whole block at checkpoint, by bgwriter cleaning or via dirty page eviction. So ZFS is OK, but we do more writing than we want to, sometimes. -- Simon Riggs www.2ndQuadrant.com -- 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] Block-level CRC checks
I am in the process of adding a user-space myhash column to all my applications tables, filled by a trigger on insert / update. It really speeds up table comparison across databases; and it is very helpfull in debugging replications. Have you seen pg_comparator? yes, saw the lightning talk at pgday.eu it also uses md5 hashes, just in an own schema. Guess pg_comparator would profit from an integrated MD5 hash. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality -- 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] YAML Was: CommitFest status/management
On Fri, Dec 4, 2009 at 7:42 PM, Josh Berkus j...@agliodbs.com wrote: On top of that, if you did want YAML for easier readability, what aspect of the output is more readable in YAML than it is in text format? The only answer I can think of is that you like having each data element on a separate line, so that the plan is much longer but somewhat narrower. But if that's what you want, the JSON output is almost as good - the only difference is a bit of extra punctuation. almost as good ... I agree with Kevin that it's more readable. The whole patch just adds 144 lines. It doesn't look to me like there's significant maintenance burden involved, but of course I need to defer to the more experienced. It's even possible that we could reduce the size of the patch still further if we really looked at it as just a differently punctuated JSON. Having compared the JSON and YAML output formats, I think having YAML as a 2nd human-readable format might be valuable, even though it adds nothing to machine-processing. Again, if there were a sensible way to do YAML as a contrib module, I'd go for that, but there isn't. I don't think the maintenance burden is the issue, per se. It's more that I don't like the idea of putting in a bunch of formats that are only trivially different from each other, and if there were ever a case where we should reject a new format because it is too similar to an existing one, this seems to be it. If that's a bad reason for rejecting a new format, then I don't have a second one, but we may end up with a lot of formats - and that WILL be a maintenance burden, especially if we ever want to make non-trivial extensions to the output format. Frankly, just adding new fields (perhaps controlled by new options) is never going to be that big of a deal, but there will certainly come a day when someone wants to do something more novel, like dumping parse-tree representations of expressions or something along the line of Tom Raney's visual explain tool, which dumped out every path the planner considered. I don't really want to be the person who has to tell the person who writes that patch sorry, but we have to reject your patch until it supports every one of our numerous slightly different output formats. One possibility for contrib-module-izing this, and perhaps other output formats that people might like to see, is to write a function that takes the JSON or XML output as input and does the appropriate translation. For something like YAML, whose semantics are so close to JSON, this should be pretty simple. One of the reasons why I was hot to get JSON support into the initial version of machine-readable EXPLAIN is because JSON maps very cleanly onto the type of data structures that are common in scripting languages: everything is lists and hashes, nested inside each other, and text and numeric scalars. So you can read a JSON object into a script written in Perl, PHP, Python, Ruby, JavaScript, and probably half a dozen other languages and get a native object. From there, it's very easy to write the data back out in whatever format you happen to prefer just by walking the data structure. I suspect that a JSON-to-YAML converter in Perl would be less than 50 lines. (The XML format can also be transformed using things like XSLT, but I'm less familiar with those tools.) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Clearing global statistics
Since the pg_stat_bgwriter structure was introduced in 8.3, there's
never been any way to reset its statistics back to 0. A week of
analyzing data from that every day drove me crazy enough to finally fix
this with the attached patch. This implements the TODO item Allow the
clearing of cluster-level statistics, based on previous discussion
ending at http://archives.postgresql.org/pgsql-hackers/2009-03/msg00920.php
Here's the patch in action:
gsmith=# select checkpoints_req,buffers_alloc from pg_stat_bgwriter;
checkpoints_req | buffers_alloc
-+---
1 | 5
gsmith=# select pg_stat_reset_global();
gsmith=# select checkpoints_req,buffers_alloc from pg_stat_bgwriter;
checkpoints_req | buffers_alloc
-+---
0 | 0
Patch is complete including docs, it's basically just pg_stat_reset with
a different clearing mechanism at the very end. My list of potential
questions here are:
-Not sure if this should be named pg_stat_rest_global (to match the way
these are called global stats in the source) or
pg_stat_reset_cluster. Picked the former for V1, not attached to that
decision at all. Might even make sense to use a name that makes it
obvious the pg_stat_bgwriter data is what's targeted.
-I create a new stats message type for this, but just reuse the same
message payload structure as pg_stats_reset rather than add a new
payload structure for no good reason. That's marked with two XXX s in
the code as a questionable design decision. I can implement that too if
there's some reason it's a good idea I don't know yet.
-I grabbed what looked like an appropriate unused OID. I'm never sure
if I did that right or not though, it may need to be renumbered.
Since this whole patch is basically a cut and paste job of code that was
already there, I don't really expect it to need much discussion beyond
these minor points; wouldn't have sent it in the middle of an active
CommitFest if that weren't the case.
--
Greg Smith2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.com
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1f70fd4..2af5696 100644
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
*** postgres: replaceableuser/ replacea
*** 918,923
--- 918,932
(requires superuser privileges)
/entry
/row
+
+ row
+ entryliteralfunctionpg_stat_reset_global/function()/literal/entry
+ entrytypevoid/type/entry
+ entry
+Reset the global statistics counters for the database cluster to
+zero (requires superuser privileges)
+ /entry
+ /row
/tbody
/tgroup
/table
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 2d4fb77..b124a3e 100644
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*** static void pgstat_recv_tabstat(PgStat_M
*** 269,274
--- 269,275
static void pgstat_recv_tabpurge(PgStat_MsgTabpurge *msg, int len);
static void pgstat_recv_dropdb(PgStat_MsgDropdb *msg, int len);
static void pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len);
+ static void pgstat_recv_resetglobalcounter(PgStat_MsgResetcounter *msg, int len);
static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
*** pgstat_reset_counters(void)
*** 1150,1155
--- 1151,1182
pgstat_send(msg, sizeof(msg));
}
+ /* --
+ * pgstat_reset_global_counters() -
+ *
+ * Tell the statistics collector to reset counters for cluster-wide globals.
+ * --
+ */
+ void
+ pgstat_reset_global_counters(void)
+ {
+ /* XXX Since there's no payload needed, reusing the existing reset counter
+ structure rather than defining a new one. Is that OK? */
+ PgStat_MsgResetcounter msg;
+
+ if (pgStatSock 0)
+ return;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg(must be superuser to reset statistics counters)));
+
+ pgstat_setheader(msg.m_hdr, PGSTAT_MTYPE_RESETGLOBALCOUNTER);
+ /* XXX Not actually using this */
+ msg.m_databaseid = MyDatabaseId;
+ pgstat_send(msg, sizeof(msg));
+ }
/* --
* pgstat_report_autovac() -
*** PgstatCollectorMain(int argc, char *argv
*** 2888,2893
--- 2915,2926
len);
break;
+ case PGSTAT_MTYPE_RESETGLOBALCOUNTER:
+ pgstat_recv_resetglobalcounter(
+ (PgStat_MsgResetcounter *) msg,
+ len);
+ break;
+
case PGSTAT_MTYPE_AUTOVAC_START:
pgstat_recv_autovac((PgStat_MsgAutovacStart *) msg, len);
break;
***
Re: [HACKERS] Adding support for SE-Linux security
On Thu, Dec 3, 2009 at 5:23 PM, Josh Berkus j...@agliodbs.com wrote: In words of one syllable: I do not care at all whether the NSA would use Postgres, if they're not willing to come and help us build it. There's several 2-syllable words there. ;-) If we tried to build it without their input, we'd probably not produce what they want anyway. Yeah, the *complete* lack of input/help from the security community aside from the occasional SE Linux good posts we've gotten is troubling. We could end up with a SQL-J. Kaigai, you've said that you could get SELinux folks involved in the patch review. I think it's past time that they were; please solicit them. Actually, we tried that already, in a previous iteration of this discussion. Someone actually materialized and commented on a few things. The problem, as I remember it, was that they didn't know much about PostgreSQL, so we didn't get very far with it. Unfortunately, I can't find the relevant email thread at the moment. In fact, we've tried about everything with these patches. Tom reviewed them, Bruce reviewed them, Peter reviewed them, I reviewed them, Stephen Frost reviewed them, Heikki took at least a brief look at them, and I think there were a few other people, too. The first person who I can recall being relatively happy with any version of this patch was Stephen Frost, commenting on the access control framework that we suggested KaiGai try to separate from the main body of the patch to break it into more managable chunks. That patch was summarily rejected by Tom for what I believe were valid reasons. In other words, in 18 months of trying we've yet to see something that is close to being committable. Contrast that with Hot Standby, which Heikki made a real shot at committing during the first CommitFest to which it was submitted. I think David Fetter summarized it pretty well here - the rest of the thread is worth reading, too. http://archives.postgresql.org/pgsql-hackers/2009-07/msg01159.php I think the only chance of this ever getting committed is if a committer volunteers to take ownership of it, similar to what Heikki has done for Hot Standby and Streaming Replication. Right now, we don't have any volunteers, and even if Tom or Heikki were interested, I suspect it would occupy their entire attention for several CommitFests just as HS and SR have done for Heikki. I suspect the amount of work for SE-PostgreSQL might even be larger than for HS. If we DON'T have a committer who is willing to own this, then I don't think there's a choice other than giving up. ...Robert -- 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] Adding support for SE-Linux security
Robert Haas wrote: Actually, we tried that already, in a previous iteration of this discussion. Someone actually materialized and commented on a few things. The problem, as I remember it, was that they didn't know much about PostgreSQL, so we didn't get very far with it. Unfortunately, I can't find the relevant email thread at the moment. In fact, we've tried about everything with these patches. Tom reviewed them, Bruce reviewed them, Peter reviewed them, I reviewed them, Stephen Frost reviewed them, Heikki took at least a brief look at them, and I think there were a few other people, too. The first person who I can recall being relatively happy with any version of this patch was Stephen Frost, commenting on the access control framework that we suggested KaiGai try to separate from the main body of the patch to break it into more managable chunks. That patch was summarily rejected by Tom for what I believe were valid reasons. In other words, in 18 months of trying we've yet to see something that is close to being committable. Contrast that with Hot Standby, which Heikki made a real shot at committing during the first CommitFest to which it was submitted. I think David Fetter summarized it pretty well here - the rest of the thread is worth reading, too. http://archives.postgresql.org/pgsql-hackers/2009-07/msg01159.php I think the only chance of this ever getting committed is if a committer volunteers to take ownership of it, similar to what Heikki has done for Hot Standby and Streaming Replication. Right now, we don't have any volunteers, and even if Tom or Heikki were interested, I suspect it would occupy their entire attention for several CommitFests just as HS and SR have done for Heikki. I suspect the amount of work for SE-PostgreSQL might even be larger than for HS. If we DON'T have a committer who is willing to own this, then I don't think there's a choice other than giving up. I offered to review it. I was going to mostly review the parts that impacted our existing code, and I wasn't going to be able to do a thorough job of the SE-Linux-specific files. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your 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] add more frame types in window functions (ROWS)
Andrew Gierth and...@tao11.riddles.org.uk writes: If we're going to change the interface in this way, there should, IMO, be enough of a change that old code fails to compile; e.g. by renaming wincontext to partition_context or some equivalent change. Agreed --- if we have to break things, break them obviously not silently. I don't have time right now to think about this issue in detail, but if those are the alternatives I think the choice is clear. Quietly adding a memory leak to code that used to work well is not acceptable. 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] First feature patch for plperl - draft [PATCH]
David E. Wheeler da...@kineticode.com writes: Tom, what's your objection to Shlib load time being user-visible? It's not really designed to be user-visible. Let me give you just two examples: * We call a plperl function for the first time in a session, causing plperl.so to be loaded. Later the transaction fails and is rolled back. If loading plperl.so caused some user-visible things to happen, should those be rolled back? If so, how do we get perl to play along? If not, how do we get postgres to play along? * We call a plperl function for the first time in a session, causing plperl.so to be loaded. This happens in the context of a superuser calling a non-superuser security definer function, or perhaps vice versa. Whose permissions apply to whatever the on_load code tries to do? (Hint: every answer is wrong.) That doesn't even begin to cover the problems with allowing any of this to happen inside the postmaster. Recall that the postmaster does not have any database access. Furthermore, it is a very long established reliability principle around here that the postmaster process should do as little as possible, because every thing that it does creates another opportunity to have a nonrecoverable failure. The postmaster can recover if a child crashes, but the other way round, not so much. 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
