Re: [HACKERS] WIP: Page space reservation (pgupgrade)
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: Attached patch allows to setup storage parameter for space reservation. What is the point of this? We don't need it for 8.3-8.4, we aren't going to back-patch such a thing into 8.2 or before (certainly not before, since reloptions didn't exist before 8.2), and I would hope we put in a more general solution than this for 8.4-to-later preparatory fixes. There is whole idea. By my opinion the space reservation is possible to split into three separate problems: 1) First mandatory is to implement space reservation functionality in the core. It is what this patch tries to do. It prevents to store new data in reserved area. The configuration is implemented with reloptions. I think This part is possible simply backported back to 8.2 and it allows in-place online upgrade form 8.2 to 8.4 (and indirectly from 8.1). There is only limitation for toasted arrays and composite datatypes, which can be solve with offline conversion. How Jim Nasby mentioned we need to add also per tuple space reservation which is not required for V3-V4 upgrade or it is negative, but it is important to have it for 8.4, because who know what will happen int 8.5 development. By my opinion, this functionality is similar to fillfactor and it is independent. And can be committed separately. 2) Second part is pre-upgrade check/preparation process. For 8.2 it has to be store procedure which will work separately (no core changes). In 8.4 it will be implemented into core - new column in pg_class and pg_database. Vacuum adjusting and so on. 3) Last part is pre-upgrade configuration which correctly setup reserved space all non-system relations in database cluster. Thats my idea. Let me know any comments. Thanks Zdenek -- 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] ALTER DATABASE SET TABLESPACE vs crash safety
--On Sonntag, November 09, 2008 18:25:50 -0600 Decibel! [EMAIL PROTECTED] wrote: On Nov 7, 2008, at 9:53 AM, Tom Lane wrote: FWIW, I don't see this patch as being terribly useful in the real world until it can take place in the background, without locking stuff for a huge amount of time. That tells me that we should have a way to move objects to a new tablespace a little bit at a time. My guess is that such a facility would be something that runs in the background over many different transactions. Once everything had been moved, only then would it go and delete the old files. Of course, such a facility is much more complicater than what this patch does. If you don't want to exclusive lock the database you need to track all changes during copying the relations and later merge them into the new ones in the worst case. I don't see how you want to preserve a consistent state of the database otherwise. But it's too late to get that kind of functionality into 8.4. :( So, is there enough demand for this feature to get it into 8.4 and possibly paint ourselves into a corner, or should we just wait until 8.5? This patch is already committed. -- Thanks Bernd -- 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] [I|S]CONST/[I|S]const in gram.y
Michael Meskes [EMAIL PROTECTED] writes: is there a reason why we sometimes use ICONST and SCONST directly in a rule in gram.y yet in other rules use Iconst and Sconst which in turn resolve to ICONST and SCONST? Some rules even use ICONST and Sconst, so there does not be any consistency. Seems like an obvious no-op. If this has no reason I'd like to make all rules use the same symbol which will make gram.y be consequent in its symbol usage and simplify my work to generate the ecpg parser out of an unchanged gram.y at the same time. Which direction are you hoping to go --- remove Iconst/Sconst, or use them everywhere? 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] pg_do_encoding_conversion glitch
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I have a question about the result contract of pg_do_encoding_conversion(). It's poorly designed :-( As near as I can tell, all uses of the function either pass a null-terminated string or special-case the result == src case in such a way that it doesn't matter. However it's certainly not obvious that you have to do that. The calls in contrib/sslinfo might be broken --- not sure how much that module has been tested. Do you have a proposal for a different API, or do you just want to improve the comment for the function? Bear in mind that a lot of the callers do know the string length, and so we shouldn't impose an unnecessary strlen() operation on those cases. 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] WIP: Page space reservation (pgupgrade)
Zdenek Kotala [EMAIL PROTECTED] writes: Tom Lane napsal(a): What is the point of this? We don't need it for 8.3-8.4, we aren't going to back-patch such a thing into 8.2 or before (certainly not before, since reloptions didn't exist before 8.2), and I would hope we put in a more general solution than this for 8.4-to-later preparatory fixes. ... I think This part is possible simply backported back to 8.2 and it allows in-place online upgrade form 8.2 to 8.4 (and indirectly from 8.1). I would never recommend to anyone that they depend on a patch like this for pre-upgrade conversions. It has no way to guarantee that all pages in the database have been fixed. And in any case, I would vote against back-patching such a thing into 8.2, at least not without a whole lot more field testing than it is likely to get. As for planning that there will someday be 8.1-8.2 or even 8.2-8.3 online upgrade, I suggest that you quit wasting your time even thinking about that. It won't happen and expending more cycles on it is mostly going to ensure that the entire upgrade project fails. What we need now is something that works for 8.3-8.4 and that we can extend and maintain for future version updates. 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] Windowing Function Patch Review - Performance Comparison.
2008/11/10 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: I found how to do it, though it's only on the case you gave. Thinking about the planner optimization of the Window nodes (and its attached Sort nodes), we must consider the execution order of more than one node. In the test case we only take care of only one window, but there may be more window/sort node sets, which is too difficult to choose the best execution order including the downer indexscan, mergejoin in subquery and sort-based GROUP BY. So I didn't touch the complicated planner jungle. I rewrote the patch so that only the given bottom window's sort can consider indexscan. Deeper optimizations are over my capability. After more playing around with a few queries and testing some performance of larger tables. I discovered something strange in the plan for this query. david=# explain select date,lag(date,1) over (order by date) from meter_Readings order by date; QUERY PLAN Sort (cost=1038.73..1063.74 rows=10001 width=4) Sort Key: date - Window (cost=0.00..374.27 rows=10001 width=4) - Index Scan using meter_readings_pkey on meter_readings (cost=0.00..299.27 rows=10001 width=4) (4 rows) Is the final sort still required? Is it not already sorted in the window? Oh, I forgot to mention about it. This behavior is also fixed and it works without sort on the window now. I don't remember at all why I did so and there's no comment around that but regression tests showed there is no preblem without it. Regards, -- Hitoshi Harada -- 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] Walsender process patch v1 for Synch Rep
Hi, Simon Thank you for the review. On Fri, Nov 7, 2008 at 5:49 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Wed, 2008-11-05 at 23:17 +0900, Fujii Masao wrote: Authentication --- As pointed out at another thread, for authentication, I defined the database only for replication (named walsender tentatively). walsender database is not pseudo but created by initdb like postgres database, because the user can re-create it easily even if it is lost accidentally. If the startup packet specifies walsender database, a backend declares postmaster working as walsender. Then, the backend is removed from BackendList and managed as background process by postmaster. Replication message - I defined new message type 'R', which means the start of replication. If the message is received, walsender will perform the main routine. Of course, a backend who is not walsender cannot perform the routine. I don't understand why you've done it this way. Can you explain? This stuff about a walsender database sounds like it has a purpose, but I'm not sure what it is. The route I would have taken would be to add walsender and walreceiver as new auxiliary processes. They would start via AuxiliaryProcessMain() in bootstrap/bootstrap.c. ISTM this would be slightly less code as well and not too much change from what you have now. After a quick look, most of the rest of the patch looks correct. I would hope that walsender and walreceiver would start like that. walsender would start at same time as walwriter. walreceiver can start earlier, for later discussion. Yeah, I also add walsender as new auxiliary process at first. But, during coding, I made out that this is more complicated for code and user. First problem : Which process accepts the connection from standby? IMO, *postmaster* should accept it like normal database access. Since we can use the existing connection establishment logic, the change of the code is smaller and it's easier to use. So, I added walsender as a special backend which is forked when standby connects to postmaster. Is there any advantage which walsender or other processes accept the connection from standby? Second problem : What should walsender do after the termination of the connection from standby? should die?, or remain alive and wait for next connection? IMO, we should handle it like normal database access, i.e. exit walsender. This and adding walsender as an auxiliary process seldom meet, I think. Does that answer you? Am I missing something? 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] [WIP] In-place upgrade
Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: I think that's pretty seriously un-desirable. It's not at all uncommon for databases to stick around for a very long time and then jump ahead many versions. I don't think we want to tell people they can't do that. Of course they can do that --- they just have to do it one version at a time. Also, people may be less likely to stick with an old outdated version for years and years if the upgrade process is easier. -- 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] Windowing Function Patch Review - NTILE function
2008/11/9 David Rowley [EMAIL PROTECTED]: I've done a little testing with NTILE(). I think a check should be added to the ntile() function in wfunc.c. david=# select name,salary,ntile(0) over (order by salary) as n from employees; ERROR: floating-point exception DETAIL: An invalid floating-point operation was signaled. This probably means an out-of-range result or an invalid operation, such as division by zero. I tracked that message back to the signal handler in postgres.c :-( simple fix though. Any value less than 1 does not really make sense to me. Maybe we should add something like: if (PG_WINDOW_ARG(0) 1) elog(ERROR, negative or zero ntile argument not allowed); What do you think? Oracle errors out on less than 1, Sybase seems not to have ntile. MSSQL 2008 also errors out on less than 1 David. I am so sorry but I missed this thread. I found in the spec: 1) If NT is the null value, then the result is the null value. 2) If NT is less than or equal to 0 (zero), then an exception condition is raised: data exception ― invalid argument for NTILE function. My patch violates both of two :-( As you point, we must add the value check and also allow null case to return null. will be fixed soon. Regards, -- Hitoshi Harada -- 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] Windowing Function Patch Review - NTH_VALUE
2008/11/9 David Rowley [EMAIL PROTECTED]: I'm having a little trouble understanding the standard for NTH_VALUE(). I would have assumed that NTH_VALUE(name,1) would return the first name in the window. The current patch is using 0 for the first. Hmmm, good point... I haven't thought about it enough, just followed the internal design. The window_seek() in nodeWindow.c, which is an internal API, counts rows from 0 so the external behavior is simlar. Nothing more :-P Giving your comment, actually it seems to me strange that row_number() returns 1 for the first row whereas the ntile(ve, 1) returns the second. If there're no objections I will change it as you told. Regards, -- Hitoshi Harada -- 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 for ISO-8601-Interval Input and output.
Brendan Jurd wrote: On Sat, Nov 8, 2008 at 2:19 AM, Ron Mayer [EMAIL PROTECTED] wrote: Hmmm... Certainly what I had in datatype.sgml was wrong, but I'm now thinking 5.5.4.2.1 and 5.5.4.2.2 would be the most clear? Sorry, I don't understand what you mean by 5.5.4.2.1. In the spec Ah! That 5.5.4.2.1 comes from apparently an old Oct 2000 draft version of the spec titled ISO/FDIS 8601. (For now you can see it here: http://0ape.com/postgres_interval_patches/ISO-FDIS-8601.pdf ) I'll fix all the links to point to the 2004 spec. -- 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] [I|S]CONST/[I|S]const in gram.y
On Mon, Nov 10, 2008 at 08:12:49AM -0500, Tom Lane wrote: Which direction are you hoping to go --- remove Iconst/Sconst, or use them everywhere? For ecpg I don't care, I have to make some changes during translation anyway. Right now my script just sets both to ecpg_iconst. There is a small advantage in keeping Iconst/Sconst as it might save me a line or two in the script. As for the backend I would say that removing Iconst/Sconst make sense. These rules just add an overhead, albeit a very small one. But still this advantage outweighs the very small scripting advantage. So yes, I'd remove Iconst/Sconst. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gram.y=preproc.y
The attached version now is able to generate an ecpg parser without a single change in gram.y. Also included is a perl version of the script created by a2p and fixed by me. Unfortunately a2p did not generate working code, so I guess eventually we have to only work with the perl version. I guess a perl hacker can beautify the script quite a bit. Anyway, it does work, regression tests run through (need to update expected results though). So guys, now's the time to test before it makes it into the archive. :-) Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! ecpg.parser.tgz Description: GNU Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade project status
In the last week community made decision about pg_upgrade project and its implementation. I would like to try summarize this conclusion and I add other topic which should be finished for 8.4. Convert on read has been selected as a good way, because it is not invasive and does not limit fresh database. But, this way needs core modification which allows to do online in-place upgrade. It means no online in-place upgrade to 8.4 will be implemented. Sorry about that, but we need move forward and there is not easy way without core modification to do it. As I mentioned manytimes before there are two major issues with convert on read and one small issue. 1) Data does not fit on the new page. It will be solve by pre-upgrade check which reserve space on each page, before upgrade. 2) No information about TOASTed data in TOAST table. It is problem for example when varlena encoding is changed, or heaptupleheader or array data stucture. Idea is to add column position into each chunk and also replace chunk ID by data offset. 3) Access methods stores shadow data into pages - for example metapages or hash index has bitmap pages, but there is no information on the page about this and page conversion could lost the data. We need mark this pages. Metapage is usually first page and it could be hardwired, but hash bitmap can be everywhere. I suggest to add page flag HAS_EXTRA_CONTENT (give me a better name). IIRC, somebody suggest to replace HASH bitmap pages with FSM? Any idea? I hope that I mention all. Please feel free to add more information or correct me if I wrong. Thanks Zdenek -- 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 wrote: I think double buffering solves the torn page problem but not the lack of wal logging. Alvarro solved the wal logging by deferring the wal logs. But I'm not sure how confident we are that it's logging enough. Right now, it's WAL-logging HeapTupleHeader hint bits (infomask and infomask2), and ItemId (line pointer) flags. Page pd_flags are skipped in the CRC checksum -- this is easy to do because they are in a constant offset in the page and I'm just skipping those bytes in CRC_COMP(). So what I'm missing is: - btree hint bits - bgwriter calls XLogInsert during shutdown, to WAL-log the hint bits of unwritten pages. This causes a PANIC to trigger about concurrent WAL activity during checkpoint. (The easy solution to this problem is just to remove the check; another idea is to flush the buffers before grabbing the final address to watch for at shutdown.) I'm beginning to think just excluding the hint bits would be simpler and safer. If we're double buffering then it might be possible to do that pretty cheaply. Copy the whole buffer with memcpy then loop through the line pointers unsetting the hint bits. Then do the crc. Though that would prevent us from doing zero-copy crc by doing it in the copy. This can probably be made to work, and it solves the problem that bgwriter calls XLogInsert during shutdown. I would create new routines to clear hint bits in all involved modules (heap_resethintbits, btree_%, item_%, page_%), and call them on a copy of the page. The downside to this idea is that we need to create a copy of the page and call those routines when we read the page in, too. -- 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] WIP: Page space reservation (pgupgrade)
Robert Haas napsal(a): Let's suppose, for example, that in 8.5 we decide to change some type that is presently 16 bits to 32 bits, or 8 bits to 16 bits, etc. This will make some tuples bigger, and potentially much bigger, but since it presumably won't be a commonly used data-type, most tuples won't change at all. However, the worst case scenario for how much free space you might need to reserve will be very bad, and therefore a mechanism that allows reserving a fixed amount of free space per page won't be adequate. The problem with datatypes is different story. It is should be easy to manage this problem with keeping the old datatype definition for old tables and for new create new datatype with new OID. You can use ALTER TABLE for converting data from old type to the new one. Zdenek -- 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] Synchronous replication patch v1
Hi, Pavan, On Thu, Nov 6, 2008 at 9:35 PM, Pavan Deolasee [EMAIL PROTECTED] wrote: On Thu, Nov 6, 2008 at 2:12 PM, Fujii Masao [EMAIL PROTECTED] wrote: If the database whose timeline is the same as the primary's exists in the standby, 2)3) getting new online-backup is not necessary. For example, after the standby falls down, the database at that time is applicable to restart it. If I remember correctly, when postgres finishes its recovery, it increments the timeline. If this is true, whenever ACT fails and SBY becomes primary, SBY would increment its timeline. So when the former ACT comes back and joins the replication as SBY, would it need to get a fresh backup before it can join as SBY ? PITR from not online backup is tricky in the first place. We might not be able to support the catch-up without a fresh online backup officially.. Furthermore, there is another problem. Please see the following mail. http://archives.postgresql.org/pgsql-hackers/2008-09/msg00964.php 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] Short CVS question
Dirk Riehle wrote: I have a short CVS question please: How do I go from a particular file revision like pgsql/cvs/pgsql/src/backend/parser/parse_relation.c.1.3 to the complete commit? Also try cvs2cl, which might help you: common_args=--revisions --no-indent --no-wrap --separate-header branch_arg= branch=$(cvs status configure.in | grep 'Sticky Tag' | awk '{print $3}') if [ $branch != (none) ]; then branch_arg=--follow $branch fi if [ ! -f ChangeLog ]; then cvs2cl $common_args $branch_arg else cvs2cl $common_args $branch_arg --accum fi -- 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
[HACKERS] [I|S]CONST/[I|S]const in gram.y
Hi, is there a reason why we sometimes use ICONST and SCONST directly in a rule in gram.y yet in other rules use Iconst and Sconst which in turn resolve to ICONST and SCONST? Some rules even use ICONST and Sconst, so there does not be any consistency. If this has no reason I'd like to make all rules use the same symbol which will make gram.y be consequent in its symbol usage and simplify my work to generate the ecpg parser out of an unchanged gram.y at the same time. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- 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] plperl needs upgrade for Fedora 10
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Otherwise, looks good. OK. Should we backpatch this? Arguably it's a build bug. Yeah, probably. (Also it would be good if someone were to whisper in the ears of the Fedora people that removing ExtUtils::Embed entirely from their Perl packaging is a dumb thing to do. This is a standard Perl module, that one would normally expect to accompany every Perl distribution.) So far as I can tell, Fedora has treated ExtUtils::Embed as a separate RPM for a long time (at least since F-7). The recent change seems to be that it's not required by perl-devel anymore, rather by perl-core (where perl-core is defined as everything in the upstream tarball from perl.org). So possibly it's just a matter of users not getting the word as to what they should install. 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] per-database locale: createdb switches
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Alvaro Herrera wrote: I like Teodor's proposal; I'll see about implementing that. Attached. You missed updating the sgml docs, and personally I'd be inclined to list -l before the individual --lc switches; otherwise it looks fine. Thanks, committed that way. I noticed that --lc-ctype and --lc-collate were forgotten in SGML docs, so I added them too. -- 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] per-database locale: createdb switches
Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Alvaro Herrera wrote: I like Teodor's proposal; I'll see about implementing that. Attached. You missed updating the sgml docs, and personally I'd be inclined to list -l before the individual --lc switches; otherwise it looks fine. Thanks, committed that way. I noticed that --lc-ctype and --lc-collate were forgotten in SGML docs, so I added them too. Should we have a shorthand CREATE DATABASE option like that as well? -- 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] auto_explain contrib moudle
Thank you for reviewing. Jeff Davis [EMAIL PROTECTED] wrote: Another question: what is explain.log_analyze supposed to do? Changing it doesn't seem to have an effect; it always prints out the actual time. That's because explain.log_analyze requires executor instruments, and it's not free. I think we'd better to have an option to avoid the overheads... Oops, I found my bug when force_instrument is turned on. It should be enabled only when (explain_log_min_duration = 0 explain_log_analyze). I send a new patch to fix it. A documentation about explain.log_nested_statements is also added to the sgml file. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center auto-explain.1110.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
[HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1206)
I updated the patch set of SE-PostgreSQL (revision 1206) [1/6] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1206.patch [2/6] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1206.patch [3/6] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1206.patch [4/6] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1206.patch [5/6] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1206.patch [6/6] http://sepgsql.googlecode.com/files/sepostgresql-row_acl-8.4devel-3-r1206.patch Draft of the SE-PostgreSQL documentation is here: http://wiki.postgresql.org/wiki/SEPostgreSQL List of updates: - Patches are rebased to the latest CVS HEAD. - Style of patches are changes by diff -c. - bugfix: {use} permission on user defined function made possibility to leak information without {select} permission. - The wikipage is updated as I promised. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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 for ISO-8601-Interval Input and output.
On Tue, Nov 11, 2008 at 2:36 AM, Ron Mayer [EMAIL PROTECTED] wrote: I updated my web site[1] with the latest version of this patch. I'm just testing this latest version out now. I get the expected result from 'P0001', but oddly enough if I specify only the year and month, it pukes: postgres=# select interval 'P0001-01'; ERROR: invalid input syntax for type interval: P0001-01 LINE 1: select interval 'P0001-01'; I'm attaching a patch to clean up a few more code style issues and fix broken spec references within C code comments in datetime.c. Cheers, BJ iso8601_interval-3.diff 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] gram.y=preproc.y
On Nov 10, 2008, at 8:03 AM, Tom Lane wrote: We should probably standardize on the perl version, ugly or not, because otherwise we'll have a difference in build process between Unix and Windows machines. Personally I don't really care how ugly it is as long as no one has to look at it ;-) ... but if someone wants to beautify the perl script they're surely welcome to do so. I'd be happy to, but I haven't really been following this thread. What does it do, and how do I make sure it continues to work as I refactor it? I'm fine to wait until it's committed, too. 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
[HACKERS] pg_do_encoding_conversion glitch
I have a question about the result contract of pg_do_encoding_conversion(). It can receive non null-terminated string because its arguments are a char array and a byte length. And it only returns a string, so the string should be null-terminated. However, if conversions are not required, the function returns the input string itself even though it might be not null-terminated. I checked usages of pg_do_encoding_conversion() and xml_parse() could cause troubles. Is it a bug? needed to be fixed? [utils/mb/mbutils.c] unsigned char * pg_do_encoding_conversion(unsigned char *src, int len, int src_encoding, int dest_encoding) { ... if (src_encoding == dest_encoding) return src; [utils/adt/xml.c] static xmlDocPtr xml_parse(text *data, XmlOptionType xmloption_arg, bool preserve_whitespace, xmlChar * encoding) { ... len = VARSIZE(data) - VARHDRSZ; /* will be useful later */ string = xml_text2xmlChar(data); utf8string = pg_do_encoding_conversion(string, len, encoding ? xmlChar_to_encoding(encoding) : [It could be UTF8 to UTF8] -- GetDatabaseEncoding(), PG_UTF8); Regards, --- ITAGAKI Takahiro 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] [WIP] In-place upgrade
On Mon, 2008-11-10 at 09:14 -0500, Matthew T. O'Connor wrote: Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: I think that's pretty seriously un-desirable. It's not at all uncommon for databases to stick around for a very long time and then jump ahead many versions. I don't think we want to tell people they can't do that. Of course they can do that --- they just have to do it one version at a time. Also, people may be less likely to stick with an old outdated version for years and years if the upgrade process is easier. Kind of OT but, I don't agree with this. There will always be those who are willing to just upgrade because they can but the smart play is to upgrade because you need to. If anything in place upgrades is just going to remove the last real business and technical barrier to using postgresql for enterprises. Joshua D. Drake -- -- 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] Proposed Patch to Improve Performance of Multi-Batch Hash Join for Skewed Data Sets
On Wed, Nov 05, 2008 at 04:06:11PM -0800, Bryce Cutt wrote: The error is causes by me Asserting against the wrong variable. I never noticed this as I apparently did not have assertions turned on on my development machine. That is fixed now and with the new patch version I have attached all assertions are passing with your query and my test queries. I added another assertion to that section of the code so that it is a bit more vigorous in confirming the hash table partition is correct. It does not change the operation of the code. There are two partition counts. One holds the maximum number of buckets in the hash table and the other counts the number of actual buckets created for hash values. I was incorrectly testing against the second one because that was valid before I started using a hash table to store the buckets. The enable_hashjoin_usestatmcvs flag was valuable for my own research and tests and likely useful for your review but Tom is correct that it can be removed in the final version. - Bryce Cutt Well, this version seems to work as advertised. Skewed data sets tend to hash join more quickly with this turned on, and data sets with deliberately bad statistics don't perform much differently than with the feature turned off. The patch applies cleanly to CVS HEAD. I don't consider myself qualified to do a decent code review. However I noticed that the comments are all done with // instead of /* ... */. That should probably be changed. To those familiar with code review: is there more I should do to review this? - Josh / eggyknap signature.asc Description: Digital signature
Re: [HACKERS] SQL5 budget
[EMAIL PROTECTED] (Jonah H. Harris) writes: On Sun, Nov 9, 2008 at 7:41 PM, Decibel! [EMAIL PROTECTED] wrote: I think you're barking up the wrong tree here; the community can't really do hacking for hire. If you want to pay for something to be implemented (which is great!), you'll need to talk to companies that do Postgres consulting. You can find examples on the website and through google. You could also try posting to pgsql-jobs. I would suggest submitting it to pgsql-jobs. Except that it's not a job offer - it's more of a solicitation for implementation of something, so I'm not sure that's the right place either. Some of the things suggested seem interesting, but many seem (to me) to be overly tied to an all XML all the time view. I really rather have PostgreSQL head towards being more relational (in the 3rd Manifesto sense) than take the all singing, all dancing XML road. -- select 'cbbrowne' || '@' || 'linuxfinances.info'; http://www3.sympatico.ca/cbbrowne/sgml.html Rules of the Evil Overlord #114. I will never accept a challenge from the hero. http://www.eviloverlord.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] [WIP] In-place upgrade
Decibel! napsal(a): Unless I'm mistaken, there are only two cases we care about for additional space: per-page and per-tuple. Yes. And maybe special space indexes could be extended, but it is covered in per-page setting. Those requirements could also vary for different types of pg_class objects. What we need is an API that allows an administrator to tell the database to start setting this space aside. One possibility: We need API or mechanism how in-place upgrade will setup it. It must be done by in-place upgrade. snip relkind: Essentially, heap vs toast, though I suppose it's possible we might need this for sequences. Sequences are converted during catalog upgrade. snip Once we have an API, we need to get users to make use of it. I'm thinking add something like the following to the release notes: To upgrade from a prior version to 8.4, you will need to run some of the following commands, depending on what version you are currently using: snip It is too complicated. At first it depends also on architecture and it is possible to easily compute by in-place upgrade script. What you need is only run script which do all setting for you. You can obtain it from next version (IIRC Oracle do it this way) or we can add this configuration script into previous version during a minor update. OTOH, we might not want to go mucking around with changing the catalog for older versions (I'm not even sure if we can). So perhaps it would be better to store this information in a separate table, or maybe a separate file. That might be best anyway; we generally wouldn't need this information, so it would be nice if it wasn't bloating pg_class all the time. It is why I selected relopt for storing this configuration parameter, which is supported from 8.2 and upgrade from 8.1-8.2 works fine. Zdenek -- 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
Alvaro Herrera [EMAIL PROTECTED] writes: Greg Stark wrote: I'm beginning to think just excluding the hint bits would be simpler and safer. If we're double buffering then it might be possible to do that pretty cheaply. Copy the whole buffer with memcpy then loop through the line pointers unsetting the hint bits. Then do the crc. Though that would prevent us from doing zero-copy crc by doing it in the copy. The downside to this idea is that we need to create a copy of the page and call those routines when we read the page in, too. Ugh. The cost on write was bad enough, but paying it on read is a lot worse ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for ISO-8601-Interval Input and output.
Ron Mayer wrote: Ah! That 5.5.4.2.1 comes from apparently an old Oct 2000 draft version of the spec titled ISO/FDIS 8601. (For now you can see it here: http://0ape.com/postgres_interval_patches/ISO-FDIS-8601.pdf ) I'll fix all the links to point to the 2004 spec. I updated my web site[1] with the latest version of this patch. Main differences since last time * Merged with the IntervalStyle patch as it was checked into CVS. * Fixed references to consistently refer to the same version of the ISO 8601 spec (ISO 8601:2004(E)) [1] http://0ape.com/postgres_interval_patches/ PS: I realize that this patch makes datetime.c a bit longer that it needs to be; and that some of the functions added in this patch can be used by the other interval styles as well. patch 3 that can be found on the same HTML page does this refactoring. -- 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] per-database locale: createdb switches
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Seems to me there's one there already. You're thinking of initdb maybe? I'm talking about createdb. Oh, okay. But how often is someone going to be changing locales during createdb? I think the most common case might well be like Teodor said, where you need to tweak them individually anyway. Frequently, I think. In fact I think creating a database in a different language is going to be more frequent than tweaking the settings individually. I like Teodor's proposal; I'll see about implementing that. -- 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] [I|S]CONST/[I|S]const in gram.y
Michael Meskes [EMAIL PROTECTED] writes: On Mon, Nov 10, 2008 at 08:12:49AM -0500, Tom Lane wrote: Which direction are you hoping to go --- remove Iconst/Sconst, or use them everywhere? For ecpg I don't care, I have to make some changes during translation anyway. Right now my script just sets both to ecpg_iconst. There is a small advantage in keeping Iconst/Sconst as it might save me a line or two in the script. As for the backend I would say that removing Iconst/Sconst make sense. These rules just add an overhead, albeit a very small one. But still this advantage outweighs the very small scripting advantage. So yes, I'd remove Iconst/Sconst. I experimented with doing this and found that things seemed noticeably uglier; for example in createdb_opt_item we have (ignoring the actions) | TEMPLATE opt_equal name | TEMPLATE opt_equal DEFAULT | ENCODING opt_equal Sconst | ENCODING opt_equal Iconst | ENCODING opt_equal DEFAULT | COLLATE opt_equal Sconst | COLLATE opt_equal DEFAULT which'd become | TEMPLATE opt_equal name | TEMPLATE opt_equal DEFAULT | ENCODING opt_equal SCONST | ENCODING opt_equal ICONST | ENCODING opt_equal DEFAULT | COLLATE opt_equal SCONST | COLLATE opt_equal DEFAULT which at least to me seems confusing --- the ICONST and SCONST occurrences look like literal keywords rather than token classes. So I find I have a mild preference for the mixed-case spelling. One possible way to shave the cycles and still have it look nice is to rename the actual tokens produced by scan.l to Iconst and Sconst; but this might be more trouble than it's worth. Thoughts? 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] WIP: Page space reservation (pgupgrade)
On Sun, Nov 09, 2008 at 11:28:50PM -0500, Robert Haas wrote: Well, if that's what it is, I think it's a fairly poor design decision. When I upgrade Oracle, SQL Server, or MySQL, I don't need to plan the amount of free space in my blocks a year or more before an upgrade. In fact, I don't have to plan it at all... it's completely handled by the in-place upgrade. Well, I think the proposal is that you would change the amount of free space in your blocks immediately prior to performing the upgrade, but I still think it's a poor decision to make in-place upgrade dependent on support from the OLD version of the code. ISTM the only reason why people are talking about page reservation is because people don't like the idea of an 8.4 backend being able to read 8.3 tuples without converting the whole page. If there's no hard dealine on the page conversion then you can let the 8.4 vacuum deal with it. Maybe it will take a few runs but it should get there eventually. I think you could probably sell page-reservation as: if you do this then upgrades will happen quicker but you shouldn't rely on people doing it as there are corner cases where it won't work. But if you ask people with multi-TB database whether they'll take a 1% CPU performance hit for never having to dump/restore again, which do you think they'll choose? For an I/O bound database the choice is easy. And if the performance is such a big deal provide two binaries, one to run during the upgrade and one once the upgrade is complete. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] per-database locale: createdb switches
Alvaro Herrera wrote: I like Teodor's proposal; I'll see about implementing that. Attached. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Index: src/bin/scripts/createdb.c === RCS file: /home/alvherre/Code/cvs/pgsql/src/bin/scripts/createdb.c,v retrieving revision 1.27 diff -c -p -r1.27 createdb.c *** src/bin/scripts/createdb.c 23 Sep 2008 09:20:38 - 1.27 --- src/bin/scripts/createdb.c 10 Nov 2008 15:45:09 - *** main(int argc, char *argv[]) *** 34,39 --- 34,40 {encoding, required_argument, NULL, 'E'}, {lc-collate, required_argument, NULL, 1}, {lc-ctype, required_argument, NULL, 2}, + {locale, required_argument, NULL, 'l'}, {NULL, 0, NULL, 0} }; *** main(int argc, char *argv[]) *** 54,59 --- 55,61 char *encoding = NULL; char *lc_collate = NULL; char *lc_ctype = NULL; + char *locale = NULL; PQExpBufferData sql; *** main(int argc, char *argv[]) *** 65,71 handle_help_version_opts(argc, argv, createdb, help); ! while ((c = getopt_long(argc, argv, h:p:U:WeqO:D:T:E:, long_options, optindex)) != -1) { switch (c) { --- 67,73 handle_help_version_opts(argc, argv, createdb, help); ! while ((c = getopt_long(argc, argv, h:p:U:WeqO:D:T:E:l:, long_options, optindex)) != -1) { switch (c) { *** main(int argc, char *argv[]) *** 105,110 --- 107,115 case 2: lc_ctype = optarg; break; + case 'l': + locale = optarg; + break; default: fprintf(stderr, _(Try \%s --help\ for more information.\n), progname); exit(1); *** main(int argc, char *argv[]) *** 129,134 --- 134,157 exit(1); } + if (locale) + { + if (lc_ctype) + { + fprintf(stderr, _(%s: only one of --locale and --lc-ctype can be specified\n), + progname); + exit(1); + } + if (lc_collate) + { + fprintf(stderr, _(%s: only one of --locale and --lc-collate can be specified\n), + progname); + exit(1); + } + lc_ctype = locale; + lc_collate = locale; + } + if (encoding) { if (pg_char_to_encoding(encoding) 0) *** help(const char *progname) *** 226,231 --- 249,255 printf(_( -E, --encoding=ENCODING encoding for the database\n)); printf(_( --lc-collate=LOCALE LC_COLLATE setting for the database\n)); printf(_( --lc-ctype=LOCALELC_CTYPE setting for the database\n)); + printf(_( -l, --locale=LOCALE locale settings for the database\n)); printf(_( -O, --owner=OWNERdatabase user to own the new database\n)); printf(_( -T, --template=TEMPLATE template database to copy\n)); printf(_( -e, --echo show the commands being sent to the server\n)); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-database locale: createdb switches
Alvaro Herrera [EMAIL PROTECTED] writes: Alvaro Herrera wrote: I like Teodor's proposal; I'll see about implementing that. Attached. You missed updating the sgml docs, and personally I'd be inclined to list -l before the individual --lc switches; otherwise it looks fine. 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] pg_dump roles support [Review]
On Thu, Nov 6, 2008 at 8:08 PM, Benedek László [EMAIL PROTECTED] wrote: Hi, Thanks for your review. I created an updated patch according to your notices. 1 - Patch does not apply cleanly on latest git repository, although there is no hunk failed but there are some hunk succeeded messages. Rebased to the current HEAD. 2- Patch contains unnecessary spaces and tabs which makes the patch unnecessarily big. IMHO please read the patch before sending and make sure that patch only contains the changes you intended to send. Yes, there were trailing whitespaces in the original files which were removed by the previous patch. The attached version leaves them as is. 3 - We should follow the coding standards of existing code I tried, of course, but this escaped my observation. 4 - pg_restore gives error wile restoring custom format backup 5 - Do you really want to write this code like this Fixed. I also need some feedback about the role support in pg_restore (not implemented yet). Currently pg_restore sets the role during the restore process according to the TOC entry in the archive. It may also support the --role option (just like pg_dump). If specified it can be used to cancel the effect of the TOC entry and force the emitting of the SET ROLE ... command. With emtpy argument it can be used to omit the SET ROLE even if it is specified in the archieve. What do you think? Now this patch looks OK to me. As for as pg_restore is concern I think we should not add this option into pg_restore. What advantages do you want to get by using SET ROLE in pg_restore? Thank you again. doc/src/sgml/ref/pg_dump.sgml| 16 ++ doc/src/sgml/ref/pg_dumpall.sgml | 15 + src/bin/pg_dump/pg_backup.h |2 + src/bin/pg_dump/pg_backup_archiver.c | 36 +- src/bin/pg_dump/pg_dump.c| 53 ++ src/bin/pg_dump/pg_dumpall.c | 23 ++ 6 files changed, 143 insertions(+), 2 deletions(-) -- Ibrar Ahmed 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] gram.y=preproc.y
Michael Meskes [EMAIL PROTECTED] writes: The attached version now is able to generate an ecpg parser without a single change in gram.y. Sweet. Also included is a perl version of the script created by a2p and fixed by me. Unfortunately a2p did not generate working code, so I guess eventually we have to only work with the perl version. I guess a perl hacker can beautify the script quite a bit. We should probably standardize on the perl version, ugly or not, because otherwise we'll have a difference in build process between Unix and Windows machines. Personally I don't really care how ugly it is as long as no one has to look at it ;-) ... but if someone wants to beautify the perl script they're surely welcome to do so. A few notes: * before committing we need to see the proposed diffs to the Makefiles and the Windows build scripts. * As-is, the first few lines of parse.pl contain an unportable hardwired assumption about where the perl executable is. I'd suggest removing those lines and having the makefile call the script as $(PERL) parse.pl ... * Can we get a $PostgreSQL$ version identifier into each file that will be added to CVS? 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] plperl needs upgrade for Fedora 10
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: What about the MakeMaker dependency? The call to ldopts will fail if MakeMaker is not present, so this will cover it. It's very unlikely to be absent - it's required to build almost every Perl module known to man. I see. I think then the error message should read something like AC_MSG_ERROR([could not determine flags for linking embedded Perl This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not installed.]) Otherwise, looks good. OK. Should we backpatch this? Arguably it's a build bug. (Also it would be good if someone were to whisper in the ears of the Fedora people that removing ExtUtils::Embed entirely from their Perl packaging is a dumb thing to do. This is a standard Perl module, that one would normally expect to accompany every Perl distribution.) 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] Patch for ISO-8601-Interval Input and output.
On Tue, Nov 11, 2008 at 5:51 AM, R Mayer [EMAIL PROTECTED] wrote: Applied and pushed to the website http://0ape.com/postgres_interval_patches/ This latest version works as expected and I don't detect any other issues with the code or documentation ... seems I've run out of things to gripe about! I'm ready to sign off on this patch now and move on to the final cleanup patch. I'll update the commitfest to show this one as ready for committer. Cheers, BJ -- 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] gram.y=preproc.y
David E. Wheeler wrote: On Nov 10, 2008, at 8:03 AM, Tom Lane wrote: We should probably standardize on the perl version, ugly or not, because otherwise we'll have a difference in build process between Unix and Windows machines. Personally I don't really care how ugly it is as long as no one has to look at it ;-) ... but if someone wants to beautify the perl script they're surely welcome to do so. I'd be happy to, but I haven't really been following this thread. What does it do, and how do I make sure it continues to work as I refactor it? I'm fine to wait until it's committed, too. I have had a quick look at it. The perl is more than ugly - it's unmaintainable IMNSHO. It violates perl best practice in many ways, and reflects the age of the a2p utility quite badly. There is no guarantee that the script won't have to be looked at. Rather, the reverse is our experience, so this is a real consideration. I agree that a perl version is much more desirable, but it really requires a hand translation from awk rather than a hacked a2p output. David, how is your awk-fu? If it's any good then I'm happy to leave it to you. Otherwise I will try to make a few hours somewhere to un-uglify this. 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] gram.y=preproc.y
Andrew Dunstan [EMAIL PROTECTED] writes: I have had a quick look at it. The perl is more than ugly - it's unmaintainable IMNSHO. It violates perl best practice in many ways, and reflects the age of the a2p utility quite badly. There is no guarantee that the script won't have to be looked at. Rather, the reverse is our experience, so this is a real consideration. I agree that a perl version is much more desirable, but it really requires a hand translation from awk rather than a hacked a2p output. IMHO awk was the wrong language to begin with, so I'd vote for a fresh implementation with re-thought data structures rather than just cleaning up around the edges. However, I would like any reimplementation to happen after we get this in, not before. As long as we are agreed that a perl script is the appropriate tool, someone can go off in a corner and reimplement without holding up anything else. And it's surely past time that Michael stops having to sync ecpg with the main grammar by hand. 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] WIP: Hash Join-Filter Pruning using Bloom Filters
-Original Message- On Sun, Nov 2, 2008 at 10:49 PM, Jonah H. Harris [EMAIL PROTECTED] wrote: It's effective as-is for a preliminary patch. The GUC code is the least of my worries. Can you provide some figures on the performance impact of the bloom filter? I have tested the Bloom filter patch. It compiles cleanly against HEAD. As indicated, the performance improvements for hash join are good, especially when the build table is filtered with a selection condition. Performance improvements range from a couple of percent up to 20% for multi-batch joins. Note that the bloom filter will slightly slow queries where the filter has no benefit. I have not looked at the actual implementation of the Bloom filter, but will proceed to do that next. One issue to be considered is how the space used for the bloom filter is related to the work_mem allocated to the join. That is, does the bloom filter consume some of the work_mem space or is it treated as additional memory allocated to the join. Experimental Results (in ms) Query Time With FilterTime Without Filter % Faster 1 2,166 2,648 18% 2 1,665 1,772 6% 3 5,308 6,374 17% 4 63,690 75,715 15% 5 87,864 81,552 -8% 6 12,492 11,696 -7% Query 1: (provided by patch author) = 190,000 results CREATE TABLE t1 (id INTEGER PRIMARY KEY, x INTEGER); CREATE TABLE t2 (id INTEGER PRIMARY KEY, x INTEGER); INSERT INTO t1 (SELECT ge, ge % 100 FROM generate_series(1, 100) ge); INSERT INTO t2 (SELECT * FROM t1); VACUUM ANALYZE; SELECT COUNT(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.x 30 AND t2.x 10; The next five queries are on the TPCH 1GB data set. Query 2: (in-memory join with restrictive build filter) = 56,110 results select count(*) from customer, orders where c_custkey = o_custkey and c_nationkey = 10; Query 3: (multi-batch join with less restrictive build filter) = 841,767 results select count(*) from customer, orders where c_custkey = o_custkey and c_nationkey 10; Query 4: (large multi-batch join) = 3,215,402 results select count(*) from orders, lineitem where o_orderkey = l_orderkey and o_totalprice 15; Query 5: (large multi-batch join with no filter - hard case for BLOOM filter) = 6,000,003 results select count(*) from orders, lineitem where o_orderkey = l_orderkey; Query 6: (large probe, in-memory build with no filter - hard case for BLOOM filter) = 6,000,003 results select count(*) from supplier, lineitem where s_suppkey = l_suppkey; All tests were run 4 times and the times were averaged. The initial run time was discarded to deal with buffering issues. -- Dr. Ramon Lawrence University of British Columbia Okanagan -- 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 for ISO-8601-Interval Input and output.
Brendan Jurd [EMAIL PROTECTED] writes: I'm ready to sign off on this patch now and move on to the final cleanup patch. I'll update the commitfest to show this one as ready for committer. OK, I'll pick this one up now. 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] gram.y=preproc.y
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I have had a quick look at it. The perl is more than ugly - it's unmaintainable IMNSHO. It violates perl best practice in many ways, and reflects the age of the a2p utility quite badly. There is no guarantee that the script won't have to be looked at. Rather, the reverse is our experience, so this is a real consideration. I agree that a perl version is much more desirable, but it really requires a hand translation from awk rather than a hacked a2p output. IMHO awk was the wrong language to begin with, so I'd vote for a fresh implementation with re-thought data structures rather than just cleaning up around the edges. That was what I was intending. The awk would just be a guide as to the required logic. However, I would like any reimplementation to happen after we get this in, not before. As long as we are agreed that a perl script is the appropriate tool, someone can go off in a corner and reimplement without holding up anything else. And it's surely past time that Michael stops having to sync ecpg with the main grammar by hand. Sure. No argument at all from me. 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] Patch for ISO-8601-Interval Input and output.
Brendan Jurd wrote: On Tue, Nov 11, 2008 at 2:36 AM, Ron Mayer I get the expected result from 'P0001', but oddly enough if I specify only the year and month, it pukes: postgres=# select interval 'P0001-01'; Indeed. Thanks again. I've fixed this and added regression tests to check the handling of optional fields of the alternative format which my patch has been so very bad at handling. I'm attaching a patch to clean up a few more code style issues and fix broken spec references within C code comments in datetime.c. Applied and pushed to the website http://0ape.com/postgres_interval_patches/ -- 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] SQL5 budget
Chris Browne wrote: [EMAIL PROTECTED] (Jonah H. Harris) writes: On Sun, Nov 9, 2008 at 7:41 PM, Decibel! [EMAIL PROTECTED] wrote: I think you're barking up the wrong tree here; the community can't really do hacking for hire. If you want to pay for something to be implemented (which is great!), you'll need to talk to companies that do Postgres consulting. You can find examples on the website and through google. You could also try posting to pgsql-jobs. I would suggest submitting it to pgsql-jobs. Except that it's not a job offer - it's more of a solicitation for implementation of something, so I'm not sure that's the right place either. No, but that's where the right people would see it. Realistically, this means most likely contracting with 2nd Quadrant, Command Prompt or Peter Eisentraut. However, keep in mind that no hacker can guarentee you that any patch will be accepted, even one based on the SQL standard. BTW, why do you want these features? --Josh -- 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: Updated interval patches (SQL std output, ISO8601 intervals, and interval rounding)
On Sat, Nov 1, 2008 at 3:42 PM, Ron Mayer [EMAIL PROTECTED] wrote: # Patch 3: cleanup.patch Fix rounding inconsistencies and refactor interval input/output code Compile, testing and regression tests all checked out. I've picked up on a few code style issues, fixes attached. If I'm reading the patch correctly, it seems you've renamed two of the functions in datetime.c: * AdjustFractionalSeconds = AdjustFractSeconds * AdjustFractionalDays = AdjustFractDays To be frank, this doesn't really seem worthwhile. It only saves five characters in the name. What was your reason for renaming them? I *was* going to question the inconsistent use of a space between the pointer qualifier and the argument name, for example: static char * AddVerboseIntPart(char * cp, int value, char *units, bool * is_zero, bool *is_before) But then I noticed that there's a lot of this going on in datetime.c, some of it appears to predate your patches. So I guess cleaning this up in your function definitions would be a bit of a bolted-horse, barn-door affair. Unless you felt like cleaning it up throughout the file, it's probably not worth worrying about. There are some very large-scale changes to the regression tests. I'm finding it difficult to grok the nature of the changes from reading a diff. If possible, could you post some quick notes on the purpose/rationale of these changes? Cheers, BJ cleanup-1.diff 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] SQL5 budget
On Mon, 2008-11-10 at 21:13 +0100, Josh Berkus wrote: Chris Browne wrote: No, but that's where the right people would see it. Realistically, this means most likely contracting with 2nd Quadrant, Command Prompt or Peter Eisentraut. I would note that the individual has been hitting *everyone* directly and via list. So either he is quite serious, doesn't understand etiquette or is a spammer. Joshua D. Drake -- -- 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] SQL5 budget
Josh, I would note that the individual has been hitting *everyone* directly and via list. So either he is quite serious, doesn't understand etiquette or is a spammer. I think there is a language barrier at work. --Josh -- 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] Windowing Function Patch Review - ROW_NUMBER without ORDER BY
Hitoshi Harada wrote: The spec says: The ROW_NUMBER function computes the sequential row number, starting with 1 (one) for the first row, of the row within its window partition according to the window ordering of the window. I'm just not sure if we should block this or not. Does anyone see this as a feature? I don't see any reason to take it as a bug. It may be confusing some people but it is consistent enough and not ambiguous. Many users already know if they don't specify ORDER BY clause in a simple regular query they wouldn't receive ordered rows so it will match their senses. Bug was probably the wrong word for me to use. At the time I was thinking it could easily be misused. The last sentence in the above quote seemed to change my mind about this. Perhaps it is slightly unusual but it may come in useful for someone. 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] SQL5 budget
On Mon, 2008-11-10 at 21:21 +0100, Josh Berkus wrote: Josh, I would note that the individual has been hitting *everyone* directly and via list. So either he is quite serious, doesn't understand etiquette or is a spammer. I think there is a language barrier at work. Oh certainly a possibility which is why I gave all three options :) Joshua D. Drake --Josh -- -- 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] [WIP] In-place upgrade
On Nov 9, 2008, at 11:09 PM, Joshua D. Drake wrote: I think it's time for people to stop asking for the moon and realize that if we don't constrain this feature pretty darn tightly, we will have *nothing at all* for 8.4. Again. Gotta go with Tom on this one. The idea that we would somehow upgrade from 8.1 to 8.4 is silly. Yes it will be unfortunate for those running 8.1 but keeping track of multi version like that is going to be entirely too expensive. I agree as well. If we can get the at least the base level stuff in 8.4 so that 8.5 and beyond is in-place upgradable then that is a huge win. If we could support 8.2 or 8.3 or 6.5 :) that would be nice, but I think dealing with everything retroactively will cause our heads to explode and a mountain of awful code to arise. If we say 8.4 and beyond will be upgradable we can toss everything in we think we'll need to deal with it and not worry about the retroactive case (unless someone has a really clever(tm) idea!) This can't be an original problem to solve, too many other databases do it as well. -- Jeff Trout [EMAIL PROTECTED] 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] WIP: Hash Join-Filter Pruning using Bloom Filters
On Mon, Nov 10, 2008 at 2:42 PM, Lawrence, Ramon [EMAIL PROTECTED] wrote: I have tested the Bloom filter patch. It compiles cleanly against HEAD. Thank you for testing this! As indicated, the performance improvements for hash join are good, especially when the build table is filtered with a selection condition. Performance improvements range from a couple of percent up to 20% for multi-batch joins. Note that the bloom filter will slightly slow queries where the filter has no benefit. I have a new patch which does not create a bloom filter unless it sees that the hash join is going to batch. I'll send it along later tonight. I have not looked at the actual implementation of the Bloom filter, but will proceed to do that next. One issue to be considered is how the space used for the bloom filter is related to the work_mem allocated to the join. That is, does the bloom filter consume some of the work_mem space or is it treated as additional memory allocated to the join. Currently it's additional space not accounted for by work_mem. Additionally, it's a good amount more space than is required. This is fixed in the newer patch as well. -- Jonah H. Harris, Senior DBA myYearbook.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] SQL5 budget
Josh Berkus Wrote: I would suggest submitting it to pgsql-jobs. Except that it's not a job offer - it's more of a solicitation for implementation of something, so I'm not sure that's the right place either. No, but that's where the right people would see it. Realistically, this means most likely contracting with 2nd Quadrant, Command Prompt or Peter Eisentraut. However, keep in mind that no hacker can guarentee you that any patch will be accepted, even one based on the SQL standard. BTW, why do you want these features? --Josh Joshua D. Drake wrote: I would note that the individual has been hitting *everyone* directly and via list. So either he is quite serious, doesn't understand etiquette or is a spammer. Joshua D. Drake Apart from the post during a commitfest does this not fit in with the details given here: http://wiki.postgresql.org/wiki/How_to_sponsor_a_feature Or is sponsoring a feature paying money to people that already plan to implement something? 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] gram.y=preproc.y
Tom Lane wrote: Michael Meskes [EMAIL PROTECTED] writes: Also included is a perl version of the script created by a2p and fixed by me. Unfortunately a2p did not generate working code, so I guess eventually we have to only work with the perl version. I guess a perl hacker can beautify the script quite a bit. We should probably standardize on the perl version, ugly or not, because otherwise we'll have a difference in build process between Unix and Windows machines. Personally I don't really care how ugly it is as long as no one has to look at it ;-) ... but if someone wants to beautify the perl script they're surely welcome to do so. +1. A few notes: * before committing we need to see the proposed diffs to the Makefiles and the Windows build scripts. It might be worthwhile to make it as a perl module, called from the commandline in Unix and just included directly in the win32 build scripts. But someone more perly can comment on if that's a prettier way or not:-) //Magnus -- 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] SQL5 budget
On Mon, 2008-11-10 at 20:48 +, David Rowley wrote: Josh Berkus Wrote: I would suggest submitting it to pgsql-jobs. Apart from the post during a commitfest does this not fit in with the details given here: http://wiki.postgresql.org/wiki/How_to_sponsor_a_feature Or is sponsoring a feature paying money to people that already plan to implement something? Actually that is a poorly worded page. It really should be something like, How to submit a patch or How to get your patch committed. Joshua D. Drake 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] gram.y=preproc.y
On Nov 10, 2008, at 11:17 AM, Andrew Dunstan wrote: David, how is your awk-fu? If it's any good then I'm happy to leave it to you. Otherwise I will try to make a few hours somewhere to un- uglify this. My Perl is excellent, but my awk is remedial. What part does awk play? 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] gram.y=preproc.y
David E. Wheeler wrote: On Nov 10, 2008, at 11:17 AM, Andrew Dunstan wrote: David, how is your awk-fu? If it's any good then I'm happy to leave it to you. Otherwise I will try to make a few hours somewhere to un-uglify this. My Perl is excellent, but my awk is remedial. What part does awk play? I know about your perl ;-) The original is in awk. It's the a2p translation to Perl that's horrid. It's OK - I'll do it before long. 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] Re: Updated interval patches (SQL std output, ISO8601 intervals, and interval rounding)
Brendan Jurd [EMAIL PROTECTED] writes: I *was* going to question the inconsistent use of a space between the pointer qualifier and the argument name, for example: ... But then I noticed that there's a lot of this going on in datetime.c, some of it appears to predate your patches. Any inconsistency there is pg_indent's fault (caused by not knowing that some words are typedefs). There's no great value in messing with it manually, because pg_indent will set the spacing to suit itself anyway. 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
Tom Lane [EMAIL PROTECTED] writes: Alvaro Herrera [EMAIL PROTECTED] writes: Greg Stark wrote: I'm beginning to think just excluding the hint bits would be simpler and safer. If we're double buffering then it might be possible to do that pretty cheaply. Copy the whole buffer with memcpy then loop through the line pointers unsetting the hint bits. Then do the crc. Though that would prevent us from doing zero-copy crc by doing it in the copy. The downside to this idea is that we need to create a copy of the page and call those routines when we read the page in, too. oh, good point. Ugh. The cost on write was bad enough, but paying it on read is a lot worse ... I think you could checksum the block including the hint bits then go back and remove them from the checksum. I didn't realize you were handling more than just the heap transaction hint bits though. It would be hard to do it in any kind of abstract away like you were describing. How happy are you with the wal logging entries? Have you done any tests to see how much extra wal traffic it is? Are you sure you always generate enough logs soon enough? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing some DDL Locks to ShareLock
On Sun, 2008-11-09 at 20:18 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Will think some more and report back. If you want to do some more development, here's the portion of the patch as yet unapplied --- will save you extracting it for yourself. Thanks. More thought tells me that we should have a LockRelationForCatalogUpdate() that uses nearly the same design pattern as LockRelationForExtension(). There is no lockmode since we always take the lock in exclusive mode. Callers would grab the catalog update lock, re-read catalog, assemble the new tuple, make in-place update and release lock. Lock is non-transactional and exists only to serialise catalog updates from concurrent DDL operations. We then have the rule that all callers of heap_inplace_update() must already hold the catalog update lock. You like? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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
Gregory Stark wrote: I think you could checksum the block including the hint bits then go back and remove them from the checksum. I'm not sure what you're proposing here. It sounds to me like you are saying that we can read the page, make it available to other users, and then check the CRC. I don't think this works though, because if you do that the possibly-invalid buffer is available to the other readers. I didn't realize you were handling more than just the heap transaction hint bits though. It would be hard to do it in any kind of abstract away like you were describing. Yeah, I also initially thought that there was only a single set of hint bits, but that turned out not to be the case. Right now the nbtree hint bits are the ones missing :-( It's hard to see how to handle those. How happy are you with the wal logging entries? Have you done any tests to see how much extra wal traffic it is? Are you sure you always generate enough logs soon enough? I haven't measured the amount of traffic. They are always generated soon enough: just before calling smgrwrite on the page on FlushBuffer, i.e. just before the page hits disk. I admit it feels a bit dirty to be calling XLogInsert on such low a level. Right now we log all bits for all tuples, even if a single bit changed. It could be more efficient if I could only logs tuples whose hints bits had changed since the last write. This would require setting a bit on every tuple this tuple has an unlogged hint bit (right now there's a bit at the page level). I haven't tried implementing that. -- 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
Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark wrote: I think you could checksum the block including the hint bits then go back and remove them from the checksum. I'm not sure what you're proposing here. It sounds to me like you are saying that we can read the page, make it available to other users, and then check the CRC. I don't think this works though, because if you do that the possibly-invalid buffer is available to the other readers. No, I just meant that you could calculate the CRC by scanning the whole buffer efficiently using one of the good word-wise CRC algorithms, then look at the line pointers to find the hint bits and subtract them out of the CRC. The result should be zero after adjusting for the hint bits. It doesn't solve much though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication 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] gram.y=preproc.y
Andrew Dunstan wrote: David E. Wheeler wrote: On Nov 10, 2008, at 11:17 AM, Andrew Dunstan wrote: David, how is your awk-fu? If it's any good then I'm happy to leave it to you. Otherwise I will try to make a few hours somewhere to un-uglify this. My Perl is excellent, but my awk is remedial. What part does awk play? I know about your perl ;-) The original is in awk. It's the a2p translation to Perl that's horrid. It's OK - I'll do it before long. I know awk well and perl mediocre so I can help. -- Bruce Momjian [EMAIL PROTECTED]http://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] pg_upgrade project status
Zdenek Kotala wrote: In the last week community made decision about pg_upgrade project and its implementation. I would like to try summarize this conclusion and I add other topic which should be finished for 8.4. Convert on read has been selected as a good way, because it is not invasive and does not limit fresh database. But, this way needs core modification which allows to do online in-place upgrade. It means no online in-place upgrade to 8.4 will be implemented. Sorry about that, but we need move forward and there is not easy way without core modification to do it. As I mentioned manytimes before there are two major issues with convert on read and one small issue. 1) Data does not fit on the new page. It will be solve by pre-upgrade check which reserve space on each page, before upgrade. Rather than specifying free space as an amount, I was thinking of having a boolean like 'ready_for_upgrade' and the system internally would know how much free space for each page and tuple. -- Bruce Momjian [EMAIL PROTECTED]http://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] SQL5 budget
David Rowley escribió: Or is sponsoring a feature paying money to people that already plan to implement something? Nobody on their mind would plan to implement the features being proposed here ... I didn't look very far but it seems mainly nonsense. -- 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] SQL5 budget
Joshua D. Drake escribió: On Mon, 2008-11-10 at 20:48 +, David Rowley wrote: Josh Berkus Wrote: I would suggest submitting it to pgsql-jobs. Apart from the post during a commitfest does this not fit in with the details given here: http://wiki.postgresql.org/wiki/How_to_sponsor_a_feature Actually that is a poorly worded page. It really should be something like, How to submit a patch or How to get your patch committed. Yes -- this was reported when the page was created. We already have a page for what you suggest: http://wiki.postgresql.org/wiki/Submitting_a_Patch -- 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] Walsender process patch v1 for Synch Rep
On Mon, 2008-11-10 at 18:22 +0900, Fujii Masao wrote: Yeah, I also add walsender as new auxiliary process at first. But, during coding, I made out that this is more complicated for code and user. First problem : Which process accepts the connection from standby? IMO, *postmaster* should accept it like normal database access. Since we can use the existing connection establishment logic, the change of the code is smaller and it's easier to use. So, I added walsender as a special backend which is forked when standby connects to postmaster. Is there any advantage which walsender or other processes accept the connection from standby? Second problem : What should walsender do after the termination of the connection from standby? should die?, or remain alive and wait for next connection? IMO, we should handle it like normal database access, i.e. exit walsender. This and adding walsender as an auxiliary process seldom meet, I think. Does that answer you? Am I missing something? It's good to see your reasons written down. OK, I think I could like this way around. The walsender database allows us to enforce restrictions in pg_hba.conf. Also avoids needing to run a long running transaction to initiate wal sending feature, if we do it just with user function. I'd like to see a longer README explaining these design aspects though. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Reducing some DDL Locks to ShareLock
Simon Riggs [EMAIL PROTECTED] writes: More thought tells me that we should have a LockRelationForCatalogUpdate() that uses nearly the same design pattern as LockRelationForExtension(). There is no lockmode since we always take the lock in exclusive mode. This works only for updaters that cooperate with the rule, though. The scenario that is bothering me is a manual UPDATE on a pg_class row happening concurrently with an operation that wants to apply a nontransactional update. While that's more or less deprecated, there are still plenty of people out there who might try it (cf the old trick for disabling triggers). I don't mind if one or the other operation fails and rolls back, but silently losing the nontransactional update would be entirely unacceptable, as it would quickly lead to database corruption. The reason I was thinking about heap_lock_tuple is that it might provide a suitable defense against that case. 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] pg_upgrade project status
Bruce Momjian [EMAIL PROTECTED] writes: 1) Data does not fit on the new page. It will be solve by pre-upgrade check which reserve space on each page, before upgrade. Rather than specifying free space as an amount, I was thinking of having a boolean like 'ready_for_upgrade' and the system internally would know how much free space for each page and tuple. I think the original idea was that 8.5 would come with a tool which connected to an 8.4 database and makes sure things are ok. To do so it has to do two things: 1) set some server state so that the server doesn't create any new bad pages and 2) check that all the existing pages and fix any bad pages. The question here is what state does the server need to have to ensure it doesn't create any new bad pages. The simplest option would be a minimum_free_space_per_page guc variable. That would be sufficient if we're worried about expanding the page header. The user wouldn't have to know about these, the tool would set it for him. If we're worried about expanding tuple header overhead then we would need a separate option. If we grow any data type representations then we could still have a problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres 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] pg_upgrade project status
Gregory Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: 1) Data does not fit on the new page. It will be solve by pre-upgrade check which reserve space on each page, before upgrade. Rather than specifying free space as an amount, I was thinking of having a boolean like 'ready_for_upgrade' and the system internally would know how much free space for each page and tuple. I think the original idea was that 8.5 would come with a tool which connected to an 8.4 database and makes sure things are ok. To do so it has to do two things: 1) set some server state so that the server doesn't create any new bad pages and 2) check that all the existing pages and fix any bad pages. The question here is what state does the server need to have to ensure it doesn't create any new bad pages. The simplest option would be a minimum_free_space_per_page guc variable. That would be sufficient if we're worried about expanding the page header. The user wouldn't have to know about these, the tool would set it for him. If we're worried about expanding tuple header overhead then we would need a separate option. If we grow any data type representations then we could still have a problem. Yes, this is why I was thinking it should just be a boolean and the old server will know the requirements, but it does require us to force a minor upgrade on the old server so it has the proper information embedded in the binary. We could force each page to have the required amount of free space but the binary is going to need to not invalidate that as database processing continues before the upgrade-in-place. -- Bruce Momjian [EMAIL PROTECTED]http://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] pg_do_encoding_conversion glitch
Tom Lane [EMAIL PROTECTED] wrote: Do you have a proposal for a different API, or do you just want to improve the comment for the function? Bear in mind that a lot of the callers do know the string length, and so we shouldn't impose an unnecessary strlen() operation on those cases. We already have the following comment, so I think a new comment is not needed. | In the case of no conversion, src is returned. Since Assert() is not available in the case, developers should use the function carefully after all. My patch might be fixed, too... | Solve a problem of LC_TIME of windows | http://archives.postgresql.org/message-id/[EMAIL PROTECTED] Regards, --- ITAGAKI Takahiro 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] Patch for ISO-8601-Interval Input and output.
R Mayer [EMAIL PROTECTED] writes: Applied and pushed to the website http://0ape.com/postgres_interval_patches/ I ran into an interesting failure case: regression=# select interval 'P-1Y-2M3DT-4H-5M-6'; interval --- P-1Y-2M3DT-10H-5M (1 row) This isn't the result I'd expect, and AFAICS the ISO spec does *not* allow any unit markers to be omitted in the format with designators. I think the problem is that the code will accept a value as being alternative format even when it's already read some format-with-designator fields. I propose adding a flag to remember that we've seen a field in the current part (date or time) and rejecting an apparent alternative-format input if the flag is set. 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] WIP: Hash Join-Filter Pruning using Bloom Filters
-Original Message- From: Jonah H. Harris [mailto:[EMAIL PROTECTED] I have a new patch which does not create a bloom filter unless it sees that the hash join is going to batch. I'll send it along later tonight. Currently it's additional space not accounted for by work_mem. Additionally, it's a good amount more space than is required. This is fixed in the newer patch as well. I think that the bloom filter will also improve the performance of in-memory joins as well. The basic trade-off in that case is the time to probe multiple entries in a bucket in the hash table (which currently defaults to 10) versus the cost of building/probing the bloom filter. The bloom filter should win in this case as long as there are tuples in the probe relation that cannot find a match in the build relation. My suggestion would be to keep it enabled for all joins. If possible, it would be valuable to try to estimate what percentage of tuples that the bloom filter filters out. A simple estimate would be to determine the percentage of the build table that is involved in the join. For instance, the good test cases had between 40-90% of the customer relation filtered out and a corresponding percentage of the probe relation, lineitem, was filtered out by the bloom filter. The bad case used all of customer, so the bloom filter stopped no probe tuples. It would be useful for testing to track the number and percentage of probe tuples that the bloom filter prevents a probe for. You may further record which of these tuples were in the in-memory batch and on-disk batches. These statistics may help you get the bloom filter optimized for all cases. -- Ramon Lawrence -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] question for patch Automatically update view
I got a problem when reviewing patch Automatically update view, the code can be built and executed successfully, but when i tried the eaxmple below, it seemed that the patch didn't work create view v_name_account as select customer.name, customer.age, customer.city, depositor.account_number from customer, depositor where customer.name = depositor.name with CHECK OPTION; insert into v_name_account values ('Jim', 23, 'WDC', 'A-100'); ERROR: cannot insert into a view HINT: You need an unconditional ON INSERT DO INSTEAD rule. ^ insert into v_name_account (name, age, city) values ('Jim', 23, 'WDC'); ERROR: cannot insert into a view HINT: You need an unconditional ON INSERT DO INSTEAD rule. Are there any other things i need to do for testing the patch?
Re: [HACKERS] Patch for ISO-8601-Interval Input and output.
R Mayer [EMAIL PROTECTED] writes: Applied and pushed to the website http://0ape.com/postgres_interval_patches/ Applied with nontrivial revisions --- I fear I probably broke your third patch again :-(. There were still a number of parsing bugs, and I also didn't like the lack of error checking around the strtod() call ... 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] pg_do_encoding_conversion glitch
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Do you have a proposal for a different API, or do you just want to improve the comment for the function? Bear in mind that a lot of the callers do know the string length, and so we shouldn't impose an unnecessary strlen() operation on those cases. We already have the following comment, so I think a new comment is not needed. | In the case of no conversion, src is returned. I added a few more lines just to help people out --- in digging around earlier today, there seemed to be a few places that hadn't been very careful about 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
Re: [HACKERS] autovacuum and reloptions
I have a comment about reloptions of autovacuum parameters: I'd like to have an easier way to extract individual parameters. Alvaro Herrera [EMAIL PROTECTED] wrote: Euler Taveira de Oliveira wrote: What did I already do? I refactored reloptions.c to support multiple options. I tried to follow up the same way GUC do (of course, it is much simpler). I'm thinking about removing (replacing?) StdRdOptions 'cause we need a different struct to store reloptions. Suggestions? Interesting. We store reloptions as an array of 'key=value' text, but there is no official way to read each parameter. I always create an user defined function to extract fillfactors, but it would be better if we have a standard method to do that. [brute force way] CREATE FUNCTION pg_fillfactor(reloptions text[], relam OID) RETURNS integer AS $$ SELECT (regexp_matches(array_to_string($1, '/'), 'fillfactor=([0-9]+)'))[1]::integer AS fillfactor UNION ALL SELECT CASE $2 WHEN0 THEN 100 -- heap WHEN 403 THEN 90 -- btree WHEN 405 THEN 70 -- hash WHEN 783 THEN 90 -- gist WHEN 2742 THEN 100 -- gin END LIMIT 1; $$ LANGUAGE sql STABLE; Regards, --- ITAGAKI Takahiro 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
[HACKERS] another question i met during reviewing
Here is another example for testing Automatic update view, seems that check option doesn't work, i am not sure any other things i need to do, or it is a bug for the patch, anyone give me a help? Thanks postgres=# create view old_customer as postgres-# select name, postgres-# age, postgres-# city postgres-# from customer postgres-# where age 30 postgres-# with check option; NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules postgres=# insert into old_customer values ('Kalley', 43, 'Kava'); INSERT 0 1 postgres=# insert into old_customer values ('Kalley', 21, 'loppe'); INSERT 0 1 postgres=# select * from customer; Jim | 25 | WDC Kim | 34 | Pet Lottery | 75 | Polley Kalley | 43 | Kava Kalley | 21 | loppe postgres=# select * from old_customer; Kim | 34 | Pet Lottery | 75 | Polley Kalley | 43 | Kava postgres=# insert into old_customer values ('Mathaa', 20, 'loaa'); INSERT 0 1 postgres=# select * from old_customer; Kim | 34 | Pet Lottery | 75 | Polley Kalley | 43 | Kava
Re: [HACKERS] typedefs for indent
Andrew Dunstan wrote: Alvaro Herrera wrote: Andrew Dunstan wrote: OK, I have spent some time generating and filtering typdefs via objdump on various platforms. I filtered them and Bruce's list to eliminate items not actually found in the sources thus: Did this go anywhere? I'm still trying to get a working objdump for OSX. Automating this is difficult because we need to make sure we get all (or pretty close to all) the typedefs we can get on each platform for various build configurations. I need to run pgindent in a few months. What typedef list am I going to use? -- Bruce Momjian [EMAIL PROTECTED]http://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] Synchronous replication patch v1
Hi, On Thu, Nov 6, 2008 at 3:59 PM, Fujii Masao [EMAIL PROTECTED] wrote: Again, I would expect this to be integrated with server. I would expect code to live in src/postmaster/walreceiver.c, with main logic in a file alongside xlog.c, perhaps xreceive.c. We would start WALReceiver when we enter archive recovery mode - I already have logic for this state change. After that you would be able to use the archive location specified via recovery.conf. OK. I will try to integrate walreceiver into the server. I report the current status of the coding. I'm going to post the next version of the patch tomorrow. Please wait a little longer ;) 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] another question i met during reviewing
On Mon, Nov 10, 2008 at 10:40 PM, Unicron [EMAIL PROTECTED] wrote: Here is another example for testing Automatic update view, seems that check option doesn't work, i am not sure any other things i need to do, or it is a bug for the patch, anyone give me a help? actually that is intentional, there was a lot of problems in original implementation of CHECK OPTION :) http://archives.postgresql.org/message-id/[EMAIL PROTECTED] -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] question for patch Automatically update view
On Mon, Nov 10, 2008 at 8:27 PM, Unicron [EMAIL PROTECTED] wrote: I got a problem when reviewing patch Automatically update view, the code can be built and executed successfully, but when i tried the eaxmple below, it seemed that the patch didn't work AFAIR, this patch only supports SQL92 so only views based on single tables are supported... There isn't a message indicating that the view will not be updatable? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] failed test float8 on mingw
Hi, Trying to review some patches on windows... mingw: 5.1.4; msys: 1.0.10; gcc 3.4.5 This is not current head but this morning snapshot tarball... but i saw this same error with yesterday tarball... I get this failure on regression tests: expected output: SELECT '10e-400'::float8; ! ERROR: 10e-400 is out of range for type double precision ! LINE 1: SELECT '10e-400'::float8; !^ SELECT '-10e-400'::float8; ! ERROR: -10e-400 is out of range for type double precision ! LINE 1: SELECT '-10e-400'::float8; ! actual result output: SELECT '10e-400'::float8; ! float8 ! ! 0 ! (1 row) ! SELECT '-10e-400'::float8; ! float8 ! ! 0 ! (1 row) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] [PATCHES] Solve a problem of LC_TIME of windows.
On Mon, Nov 3, 2008 at 8:41 PM, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Hello, Saito-san: Hiroshi Saito [EMAIL PROTECTED] wrote: next patch is this. I'm reviewing your patch and cleanup some parts: i'm confused, original patch has this signature: + conv_strftime(char *src, size_t len, const char *format, const struct tm *tm) your's has: +strftime_win32(char *dst, size_t dstlen, const char *format, const struct tm *tm you change all src for dst, just a variable name decision but a radical one... why was that (i honestly doesn't understand this patch very well ;)? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers