Re: [HACKERS] WIP patch: convert SQL-language functions to return tuplestores
Le vendredi 31 octobre 2008, Tom Lane a écrit : With the attached patch, SQL functions support returning the results of INSERT/UPDATE/DELETE RETURNING clauses. Thanks for your work and for having considered user whining in-passing! :) -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Simple postgresql.conf wizard
On Sun, 2008-11-02 at 17:10 -0800, Josh Berkus wrote: Greg Smith wrote: -Stepping back a bit from this particular code, is something in Python like this ever going to be appropriate to ship as a contrib module? I think python should be as good as any relatively widespread language. Unless we require all contrib to be in C. There seems to be a bit more traction in this community for using Perl for such things; I might do a Perl port of this one day but that's not going to happen soon. The advantage of python over perl is that anybody who knows at least one programming language is able to learn basic python in an afternoon. If you do a python version, others will write versions in other languages. Yeah, if python is not accepted as contrib, then it can probably be rewritten in C once it has stabilized enough. I personally don't really care; Perl's main advantage is that it's pre-installed on more OSes than Python is. I think most (if not all) modern OS's standard setup includes both perl and python. Except of course windows which probably includes neither. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Simple postgresql.conf wizard
On Sat, 2008-11-01 at 22:02 -0400, Greg Smith wrote: Possible feedback topics: -Setting the next round of values requires asking the user for some input before making recommendations. Is it worth building a curses-based interface to updating the values? That would be really helpful for people with only ssh access to their server, but barring something like WCurses I'm not sure it would help on Windows. -How about a GUI one with Python's Tkinter interface? Now Windows isn't a problem, but people using ssh aren't going to be as happy. You could have it in two parts - an agent running on the server, started over ssh and GUI interface running on users workstation which talk to said agent. And I'd suggest you use wxPython for GUI part if you want a relatively modern look. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Where to point CommitFestOpen?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Somehow, prevent infinite loops doesn't seem like justification for refuse to deal with a situation that the software creates automatically. They ought to be willing to burrow more than one level ... see any Unix kernel's treatment of symlinks for behavior that has actually stood the test of usability over time. Having faced similar problems in another wiki some month ago, I wrote the attached script to automate some tasks in a wiki. Maybe it will be of use. Unfortunately I wrote it for a german wiki, some of the special pages are named differently. Hence to use it in the Postgres-Wiki, something needs to be done probably. (Not much though). In particular it includes a function to execute a redirect in all pages referencing a redirect page, i.e. change the links within all incoming pages. Regards, Jens-W. Schicke -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkOxRYACgkQzhchXT4RR5CJQQCghUUCLO+e0QtZOTD7sALCPv0p masAn1FfB786qM9QIbQXlOokK+4R7x7I =HWF8 -END PGP SIGNATURE- #!/usr/bin/perl use strict; use warnings; use MediaWiki; use Data::Dumper; use LWP; use LWP::UserAgent; my $wiki = MediaWiki-new() or die Wiki init failed; my $lwp = LWP::UserAgent-new(); $lwp-agent(Drahflow's Wiki Bot); my $WIKINAME = $ARGV[0] or die usage: ./bot.pl Wiki; my $conf; if($WIKINAME eq AK) { $conf = { 'wiki' = { 'host' = 'wiki.vorratsdatenspeicherung.de', 'path' = '/' }, 'bot' = { 'user' = 'Drahflow\'s Bot', 'pass' = 'secret' }, }; } else { die Unknown wiki: $WIKINAME; } $wiki-setup($conf); $wiki-login(); while(my $command = STDIN) { chomp $command; last if($command eq q or $command eq quit); eval { dumpContent($1) if($command =~ /^DUMP ([^|]*)$/); execTest() if($command eq 'TEST'); cleanupRedirect($1, $2) if($command =~ /^CREDIR ([^|]*)\|?(del)?$/); checkout($1) if($command =~ /^MVOUT ([^|]*)$/); checkin($1, $2) if($command =~ /^MVIN ([^|]*)\|?([^|]*)$/); copyout($1) if($command =~ /^GET ([^|]*)$/); checkToDoUsage() if($command =~ /^QTODO$/); checkLanguageSync() if($command =~ /^QLANG$/); moveCategory($1, $2) if($command =~ /^CMV ([^|]*)\|?([^|]*)$/); addCategories($1, $2) if($command =~ /^CADD (.*)\|\|(.*)$/); }; print STDERR $@ if $@; } $wiki-logout(); sub loadSure { my ($name, $mode) = @_; die no mode given unless $mode; my $page = $wiki-get($name, $mode); unless($page and $page-load()) { die could not load $name; } print Page $name loaded.\n; return $page; } sub loadCategorySure { my ($name) = @_; unless($name =~ /Kategorie:|Category:/) { die category name must be given with prefix; } my $req = HTTP::Request-new( 'GET' = 'http://' . $conf-{'wiki'}-{'host'} . '/' . $name); my $res = $lwp-request($req); if(not $res-is_success()) { die could not load $name; } my ($subcatsPart) = $res-content() =~ /\nh2Unterkategorien(.*?)\nh2/s; my ($articlesPart) = $res-content() =~ /\nh2Seiten in der Kategorie(.*?)\nVon/s; my $subcats = []; my $articles = []; while(defined $subcatsPart and $subcatsPart =~ s/.*?a href=\/([^]+) title=([^]+)//) { push @$subcats, $2; } while(defined $articlesPart and $articlesPart =~ s/.*?a href=\/([^]+) title=([^]+)//) { push @$articles, $2; } print Category $name loaded.\n; return $articles, $subcats; } sub saveSure { my ($page, $summary, $minor) = @_; if($page-{'title'} =~ /Ortsgruppe/) { askConfirmation(Page . $page-{'title'} . looks like it should be left alone); } die no summary given unless $summary; if($minor) { $page-{'minor'} = 1; } else { $page-{'minor'} = 0; } $page-{'summary'} = $summary; unless($page-save()) { die could not save . $page-{'title'}; } print Page . $page-{'title'} . saved.\n; return $page; } sub askConfirmation { my ($message) = @_; while(1) { print == $message, continue [N/y]\n; my $answer = STDIN; chomp $answer; if($answer eq '' or $answer eq 'n') { die User confirmation failed.; } if($answer eq 'y') { last; } } } sub dumpContent { my ($name) = @_; die no name given unless $name; my $page = loadSure($name, r); my $text = $page-content(); print Dumper($text); } sub execTest { my $page = loadSure('Benutzer:Drahflow/Sandkasten', rw); my $text = $page-content(); print Dumper($text); $page-{'content'} .= 'Minimaler Testlauf'; saveSure($page, 'Testing [[Benutzer:Drahflow]]\'s Bot'); } sub cleanupRedirect { my ($name, $del) = @_; die no name given unless $name; my @incoming; my $page = loadSure(Spezial:Linkliste/$name, r); my $content = $page-content(); while($content =~ s!a href=/([^]+) title=([^]+)\2/a[^]*span class=mw-whatlinkshere-tools!!) { my ($url, $title) = ($1, $2); push @incoming, $title; } print Incoming links:\n; print Dumper([EMAIL PROTECTED]); $page =
[HACKERS] pg_typeof() patch review
Hi, Brendan Jurd submitted a patch to add a pg_typeof() builtin function: http://archives.postgresql.org/pgsql-patches/2008-09/msg00029.php I've reviewed the patch and it looks fine. An updated version is attached, having made these changes: 1) Rebased to current CVS head 2) func.sgml: clarifying that the function returns an OID rather than a string 3) catversion.h: updated catalog version with today's date 4) pg_proc.h: placed the new entry in numerical order (Note: Does it matter how new pg_proc OIDs are assigned? I assume any available OID - 826 in this case - is as good as any other?) 5) polymorphism.sql/polymorphism.out: added regression test for the new function I hope the attached patch is formatted ok - this is how it came from Mercurial. I applied it using patch -p 1. This is my first review, so I welcome your feedback on whether I'm doing it right. Regards, ... kurt diff -r 4b92d79506ba doc/src/sgml/func.sgml --- a/doc/src/sgml/func.sgmlMon Nov 03 01:17:08 2008 + +++ b/doc/src/sgml/func.sgmlMon Nov 03 00:13:50 2008 -0800 @@ -11592,6 +11592,10 @@ /indexterm indexterm +primarypg_typeof/primary + /indexterm + + indexterm primarypg_get_keywords/primary /indexterm @@ -11660,6 +11664,11 @@ entryliteralfunctionformat_type/function(parametertype_oid/parameter, parametertypemod/)/literal/entry entrytypetext/type/entry entryget SQL name of a data type/entry + /row + row + entryliteralfunctionpg_typeof/function(parameterany/parameter)/literal/entry + entrytyperegtype/type/entry + entryget the data type of any value/entry /row row entryliteralfunctionpg_get_keywords/function()/literal/entry @@ -11774,6 +11783,12 @@ functionformat_type/function returns the SQL name of a data type that is identified by its type OID and possibly a type modifier. Pass NULL for the type modifier if no specific modifier is known. + /para + + para + functionpg_typeof/function returns the OID of the data type of any + value which is passed to it as an argument. This can be helpful for + troubleshooting or dynamically constructing SQL queries. /para para diff -r 4b92d79506ba src/backend/utils/adt/misc.c --- a/src/backend/utils/adt/misc.c Mon Nov 03 01:17:08 2008 + +++ b/src/backend/utils/adt/misc.c Mon Nov 03 00:13:51 2008 -0800 @@ -35,6 +35,15 @@ #define atooid(x) ((Oid) strtoul((x), NULL, 10)) + +/* + * Return the type of the argument. + */ +Datum +pg_typeof(PG_FUNCTION_ARGS) +{ + PG_RETURN_OID(get_fn_expr_argtype(fcinfo-flinfo, 0)); +} /* * current_database() diff -r 4b92d79506ba src/include/catalog/catversion.h --- a/src/include/catalog/catversion.h Mon Nov 03 01:17:08 2008 + +++ b/src/include/catalog/catversion.h Mon Nov 03 00:13:51 2008 -0800 @@ -53,6 +53,6 @@ */ /* mmddN */ -#define CATALOG_VERSION_NO 200810311 +#define CATALOG_VERSION_NO 200811021 #endif diff -r 4b92d79506ba src/include/catalog/pg_proc.h --- a/src/include/catalog/pg_proc.h Mon Nov 03 01:17:08 2008 + +++ b/src/include/catalog/pg_proc.h Mon Nov 03 00:13:51 2008 -0800 @@ -1085,6 +1085,9 @@ DESCR(greater-than-or-equal); /* OIDS 800 - 899 */ + +DATA(insert OID = 826 ( pg_typeof PGNSP PGUID 12 1 0 0 f f f f i 1 2206 2276 _null_ _null_ _null_ pg_typeof _null_ _null_ _null_ )); +DESCR(returns the type of the argument); DATA(insert OID = 846 ( cash_mul_flt4PGNSP PGUID 12 1 0 0 f f t f i 2 790 790 700 _null_ _null_ _null_ cash_mul_flt4 _null_ _null_ _null_ )); DESCR(multiply); diff -r 4b92d79506ba src/include/utils/builtins.h --- a/src/include/utils/builtins.h Mon Nov 03 01:17:08 2008 + +++ b/src/include/utils/builtins.h Mon Nov 03 00:13:51 2008 -0800 @@ -395,6 +395,7 @@ extern Datum pg_ls_dir(PG_FUNCTION_ARGS); /* misc.c */ +extern Datum pg_typeof(PG_FUNCTION_ARGS); extern Datum current_database(PG_FUNCTION_ARGS); extern Datum current_query(PG_FUNCTION_ARGS); extern Datum pg_cancel_backend(PG_FUNCTION_ARGS); diff -r 4b92d79506ba src/test/regress/expected/polymorphism.out --- a/src/test/regress/expected/polymorphism.outMon Nov 03 01:17:08 2008 + +++ b/src/test/regress/expected/polymorphism.outMon Nov 03 00:13:51 2008 -0800 @@ -688,7 +688,6 @@ (1 row) -drop function concat(text, anyarray); -- mix variadic with anyelement create function formarray(anyelement, variadic anyarray) returns anyarray as $$ select array_prepend($1, $2); @@ -720,4 +719,52 @@ LINE 1: select formarray(1, variadic array['x'::text]); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. +-- test pg_typeof() function +select pg_typeof(null), -- unknown + pg_typeof(0), -- integer + pg_typeof(0.0),--
Re: [HACKERS] WIP: Column-level Privileges
Hello Stephen, Stephen Frost wrote: This has been fixed in the attached patch. Cool, thanks. If you could work on the documentation, that'd be great! I'll give it a try. Regards Markus Wanner -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1168)
Bruce Momjian wrote: KaiGai Kohei wrote: I've updated my patches, it contains a few bugfixes. [1/6] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1168.patch [2/6] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1168.patch [3/6] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1168.patch [4/6] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1168.patch [5/6] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1168.patch [6/6] http://sepgsql.googlecode.com/files/sepostgresql-row_acl-8.4devel-3-r1168.patch The comprehensive documentation for SE-PostgreSQL is here: http://wiki.postgresql.org/wiki/SEPostgreSQL (it is now under reworking.) List of updates: - Patches are rebased to the latest CVS HEAD. - bugfix: permission checks are ignored for per statement trigger functions - bugfix: per-statement trigger function ignored trusted function configuration - bugfix: not a proper permission check on lo_export(xxx, '/dev/null') Request for Comments: - The 4th patch is actually needed? It can be replaced by wiki page. - Do you think anything remained towards the final CommitFest? - Do you have any reviewing comment? Most of patches are unchanged from the previous vesion. If you can comment anything, I can fix them without waiting for the final commit fest. I just looked over the patch. This new version with row-level SQL security has certainly reduced the SE-Linux-specific part, which is good. It was interesting how you implemented SQL-level column-level permissions: CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) SECURITY_CONTEXT = 'system_u:object_r:sepgsql_secret_table_t' ); I am unclear how that will behave with the column-level permissions patch someone is working on. I am wondering if your approach is clearer than the other patch because it gives a consistent right policy for rows and columns. The column-level permissions in SE-PostgreSQL works independently and orthogonally from the upcoming column-level permissions by Stephen Frost. When the SE-PostgreSQL is enabled, both of facilities have to allow the client to access required columns. In the above case, the credit column has sepgsql_secret_table_t type, but rest of columns inherits the type of customer table which allows non-administrative users to access in the default security policy. If the given query contains the credit column, SE-PostgreSQL checks privileges of client to access columns labeled as sepgsql_secret_table_t, then it raises an error to abort the current transaction if the security policy does not allow it. There is a possibility that column-level ACLs are set via newer GRANT/REVOKE statement. In this case, the core PostgreSQL checks them, and raises an error if violated. I was wondering why you mention the NSA (U.S. National Security Agency) in the patch? +# NSA SELinux support The original author of SELinux is NSA. There is no more meanings than a caption of the option. I'll fix it, if necessary. The size of the patch is still larger but I don't see any way to reduce it: 1275 sepostgresql-docs-8.4devel-3-r1168.patch 625 sepostgresql-pg_dump-8.4devel-3-r1168.patch 829 sepostgresql-policy-8.4devel-3-r1168.patch 1736 sepostgresql-row_acl-8.4devel-3-r1168.patch 10847 sepostgresql-sepgsql-8.4devel-3-r1168.patch 1567 sepostgresql-tests-8.4devel-3-r1168.patch 16879 total I thought the sepostgresql-docs can be replaced by the pointing to the wiki page, how do you think the idea? Thanks, -- 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] Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.
Bruce Momjian wrote: Russell Smith wrote: Alvaro Herrera wrote: PoolSnoopy wrote: ***PUSH*** this bug is really some annoyance if you use automatic build environments. I'm using phpunit to run tests and as soon as postgres is involved the php cli environment segfaults at the end. this can be worked around by disabling ssl but it would be great if the underlying bug got fixed. This is PHP's bug, isn't it? Why are you complaining here No, this is a problem with the callback/exit functions used by PostgreSQL. We setup callback functions when we use SSL, if somebody else uses SSL we can create a problem. I thought my original report was detailed enough to explain where the problem is coming from. Excerpt from original report; This is part of a comment from the php bug comment history; *[12 Nov 2007 2:45pm UTC] sam at zoy dot org* Hello, I did read the sources and studied them, and I can confirm that it is a matter of callback jumping to an invalid address. libpq's init_ssl_system() installs callbacks by calling CRYPTO_set_id_callback() and CRYPTO_set_locking_callback(). This function is called each time initialize_SSL() is called (for instance through the PHP pg_connect() function) and does not keep a reference counter, so libpq's destroy_SSL() has no way to know that it should call a destroy_ssl_system() function, and there is no such function anyway. So the callbacks are never removed. But then, upon cleanup, PHP calls zend_shutdown() which properly unloads pgsql.so and therefore the unused libpq. Finally, the zend_shutdown procedure calls zm_shutdown_curl() which in turn calls curl_global_cleanup() which leads to an ERR_free_strings() call and eventually a CRYPTO_lock() call. CRYPTO_lock() checks whether there are any callbacks to call, finds one (the one installed by libpg), calls it, and crashes because libpq was unloaded and hence the callback is no longer in mapped memory. -- Basically postgresql doesn't cancel the callbacks to itself when the pg connection is shut down. So if the libpq library is unloaded before other libraries that use SSL you get a crash as described above. PHP has suggested the fix is to keep a reference counter in libpq so knows when to remove the callbacks. This is a complicated bug, but without real evidence there is no way to go to back to PHP and say it's their fault. Their analysis is relatively comprehensive compared to the feedback that's been posted here so far. I'm not sure how best to setup an environment to replicate the bug in a way I can debug it. And even if I get to the point of nailing it down, I'll just be back asking questions about how you would fix it because I know very little about SSL. All that said, a quick poke in the source of PostgreSQL says that fe-secure.c sets callbacks using CRYPTO_set_xx_callback(...). These are only set in the threaded version it appears. Which is pretty much default in all the installations I encounter. My google research indicated we need to call CRYPTO_set_xx_callback(NULL) when we exit. but that's not done. One idea for a fix is to add a counter to the initialize_ssl function and when destory_ssl is called, decrement the counter. If it reaches 0 then call CRYPT_set_xx_callback(NULL) to remove the callbacks. This is a windows SSL thread that crashes iexplore and testifies to the same problem http://www.mail-archive.com/[EMAIL PROTECTED]/msg53869.html Sorry for the delay in addressing this bug report. Your analysis of this problem is right on target. When the SSL callbacks were implemented for threaded libpq, there was never any thought on the effect of unloading libpq while the callbacks were still registered. The attached patch unregisters the callback on the close of the last libpq connection. Fortunately we require PQfinish() even if the connection request failed, meaning there should be proper accounting of the number of open connections with the method used in this patch. We do leak some memory for every load/unload of libpq, but the leaks extend beyond the SSL code to the rest of libpq so I didn't attempt to address that in this patch (and no one has complained about it). I also could have implemented a function to unload the SSL callbacks. It would have to have been called before libpq was unloaded, but I considered it inconvenient and unlikely to be adopted by applications using libpq in the short-term. I don't see why destroy_ssl_system sets up it's own mutex (that's also called init_mutex). I think it'd make more sense to make the mutex created in init_ssl_system() visible to the destroy function, and make use of that one instead. You'll need to somehow interlock against these two functions running on different threads after all. Also, the code for destroying/unlinking appears to never be called.. The callchain ends in pqsecure_destroy(), which is never called. //Magnus -- Sent via
Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith wrote: On Sun, 2 Nov 2008, Josh Berkus wrote: I'd start with command-line switches, e.g. config --memory=32GB --type=DW --size=500GB --connections=20 That seems reasonable, I think I'll push a fancier UI on the backburner then and just spec out an options interface like this one. I think in initial versions we should just get the info from the admin. I've explored the issues around getting OS-independant accurate system stats, and they are many. I'm aware how thorny a perfect solution is here. One thing that's nice about Python is that there are two interfaces for getting system information built-in, the os.sysconf I used already and the the distutils.sysconfig one, aimed more at C-level module writers. Far as I've been able to tell it's not that hard to get something that works on most platforms to auto-detect memory and architecture, and I've realized the latter is kind of important because it determines how big you can make some things on 32-bit platforms. After some digging I see there isn't any good interface built-in for Windows, but it's not hard to ask a DDL for the information. I think it's reasonable to try and detect total memory+bit width, allow overriding that, and if detection fails and nothing was specified to error out. Should make a good balance of reasonable automatic behavior in a lot of cases, while still allowing the admin to adjust. There's a completely valid use-case for that even not considering detection failures, where you're building a config file on a system other than the one it's being deployed onto. Let me know if you need help to write whatever's needed to detect that stuff on Windows. It shouldn't be too hard, as long as you can live with a couple of lines of win32-specific code.. :-) //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] Simple postgresql.conf wizard
Hannu Krosing wrote: If you do a python version, others will write versions in other languages. Yeah, if python is not accepted as contrib, then it can probably be rewritten in C once it has stabilized enough. It could. The question is if it makes sense to write something like this in C, really ;) It might get slightly more portable, at the expense of a lot more work. I see no reason why we should on principle reject a python based program from contrib. We already have stuff there in shellscript which is actually *less* portable... As long as it's not a core utility needed to get postgresql working, I think it's fine. I personally don't really care; Perl's main advantage is that it's pre-installed on more OSes than Python is. I think most (if not all) modern OS's standard setup includes both perl and python. Except of course windows which probably includes neither. Windows ships with neither of the two languages (and you *really* don't want to write it in vbscript or jscript which is what it does ship with - other than .BAT). But they both have easy installers you can use to get it in there - I don't see this as any difference between the two. And I'll second the comment that I think most reasonably modern platforms will ship with both of them. AFAIK, many of the newer linuxen use python based stuff as part of the core installer functionality, for example. //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] Block level concurrency during recovery
On Thu, 2008-10-23 at 09:57 +0100, Simon Riggs wrote: On Thu, 2008-10-23 at 09:09 +0300, Heikki Linnakangas wrote: However, we require that in b-tree vacuum, you take a cleanup lock on *every* leaf page of the index, not only those that you modify. That's a problem, because there's no trace of such pages in the WAL. OK, good. Thanks for the second opinion. I'm glad you said that, cos I felt sure anybody reading the patch would say what the hell does this bit do?. Now I can add it. Heikki, When we discussed this before, I was glad that you'd mentioned that aspect since it allowed me to say if two of us think that then it must be true. I didn't include that in the final patch because it felt wrong. I didn't have a rational explanation for that then, just a bad feeling. So, after lots of sleep, here's my rational explanation of why we do *not* need that during hot standby queries: VACUUM with a btree index proceeds like this: 1. Scan table 2. Remove rows from btree identified in (1) 3. Remove rows from heap identified in (1) The purpose of the additional locking requirements during (2) for btrees is to ensure that we do not fail to find the rows identified in (1), because the rows can move after (1) and during (2) because of block splits. Requoting verbatim from the README: The tricky part of this is to avoid missing any deletable tuples in the presence of concurrent page splits: a page split could easily move some tuples from a page not yet passed over by the sequential scan to a lower-numbered page already passed over. In recovery there are no concurrent page splits and the WAL records represent already successfully identified deletable tuples. On a standby server the rows will not move other than via WAL records. So there is no possibility that a WAL record will fail to find the row it was looking for. On the master we were looking for a tuple that pointed to a htid, whereas in WAL replay we look directly at the index tuple via its tid, not via the htid it points to. Therefore we do not need the additional locking. That seems logical to me, so I will leave that out. Any alternative views? -- 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] pre-MED
On Mon, Nov 03, 2008 at 10:02:24AM -0300, Alvaro Herrera wrote: David Fetter wrote: However, there are little lacunæ like this: SELECT * FROM show_qual() s(a) WHERE A NOT IN ( SELECT 'foo' UNION ALL SELECT 'bar' ); a (NOT (hashed subplan)) This little lacuna seems to say that if you pass the wrong query to dblink, it will fail to work, possibly with a very obscure error message; it will be very difficult to debug, and also very difficult (if not impossible) to workaround. I understand that a full implementation of SQL/MED will involve just about every part of PostgreSQL, but we've got a case of le mieux est l'enemi du bien with this contrived example, which I included so people could be fully informed when using this very helpful new feature. Left out are the very large benefits that, for example, dblink gets for free. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] don't use MAKE_PTR/OFFSET for shmem pointers
Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: Since we require every process to map the shared memory region to the same address, we don't need the MAKE_PTR/OFFSET code that was needed when that was not the case. This patch makes shared memory pointers just like regular pointers. Applied with minor editorialization --- mainly, I converted a couple of void * pointer declarations to more specific types, since it seems to me the main point of this is to not use nonspecific pointers unnecessarily. We now have two very similar doubly-linked list implementations. Should we take one of them out? -- 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] pre-MED
Alvaro Herrera [EMAIL PROTECTED] writes: David Fetter wrote: However, there are little lacunæ like this: SELECT * FROM show_qual() s(a) WHERE A NOT IN ( SELECT 'foo' UNION ALL SELECT 'bar' ); a (NOT (hashed subplan)) This little lacuna seems to say that if you pass the wrong query to dblink, it will fail to work, possibly with a very obscure error message; it will be very difficult to debug, and also very difficult (if not impossible) to workaround. Yeah. The problem here is that the portions of ruleutils.c that deal with executable plan trees (as opposed to parser output) have never been designed or intended to do more than support EXPLAIN --- which means that human readable has been considered not only sufficient but more important than 100% correct and mechanically re-parseable. The proposed patch raises the bar enormously. I'm not even real sure what all the issues would be, but I'm pretty sure that subplans are just the tip of the iceberg. Occurrences of Params ($n) would definitely be another failure case, and I also wonder what would happen with join clauses (containing references to the outer side of a join). 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] pre-MED
David Fetter wrote: I understand that a full implementation of SQL/MED will involve just about every part of PostgreSQL, but we've got a case of le mieux est l'enemi du bien with this contrived example, which I included so people could be fully informed when using this very helpful new feature. Left out are the very large benefits that, for example, dblink gets for free. That's all fine and dandy, as long as the deparser is able to notice when it's going to emit an invalid qualification, and spits out NULL instead (does your code handle that case?). If it cannot, then we would cause a serious regression -- dblink fails to work just because the query happens to use a construct that does not lend itself to proper deparsing. -- 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] plperl needs upgrade for Fedora 10
Pavel Stehule wrote: Hello I am testing PostgreSQL on Federa 10. There is Perl 5.10. After successful compilation I got error CREATE LANGUAGE plperl; ERROR: could not oad library /plperl.so: ... undefined symbol: boot_DynaLoader Regards Pavel Stehule Please send the build log for plperl also, and you configure settings. I have previously built against perl 5.10 quite happily. 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] Block level concurrency during recovery
Simon Riggs [EMAIL PROTECTED] writes: VACUUM with a btree index proceeds like this: 1. Scan table 2. Remove rows from btree identified in (1) 3. Remove rows from heap identified in (1) The purpose of the additional locking requirements during (2) for btrees is to ensure that we do not fail to find the rows identified in (1), because the rows can move after (1) and during (2) because of block splits. No, you are missing the point. One purpose of the additional locking requirements is to ensure that there is not a concurrent process that has read a btree index entry just before you removed it but arrives at the heap page only after you removed the heap entry (and, perhaps, replaced it with some other row that doesn't match the index entry at all). This is clearly still a risk in a hot-standby environment. 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] pre-MED
David Fetter [EMAIL PROTECTED] writes: Left out are the very large benefits that, for example, dblink gets for free. I take it that works reliably isn't a property you think dblink needs to have. 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] Simple postgresql.conf wizard
Hannu Krosing escreveu: On Sat, 2008-11-01 at 22:02 -0400, Greg Smith wrote: Possible feedback topics: -Setting the next round of values requires asking the user for some input before making recommendations. Is it worth building a curses-based interface to updating the values? That would be really helpful for people with only ssh access to their server, but barring something like WCurses I'm not sure it would help on Windows. -How about a GUI one with Python's Tkinter interface? Now Windows isn't a problem, but people using ssh aren't going to be as happy. You could have it in two parts - an agent running on the server, started over ssh and GUI interface running on users workstation which talk to said agent. And I'd suggest you use wxPython for GUI part if you want a relatively modern look. Or the GUI could be a part of pgAdmin. -- []s Dickson S. Guedes Administrador de Banco de Dados Projeto Colmeia - Florianópolis, SC (48) 3322-1185, ramal: 26 http://planeta.postgresql.org.br http://makeall.wordpress.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] pre-MED
I wrote: ... I'm not even real sure what all the issues would be, but I'm pretty sure that subplans are just the tip of the iceberg. Another issue is that ruleutils responds to column aliasing, as indeed it must to generate correct output for rules: regression=# explain select * from tenk1 a where a.unique1 = 42; QUERY PLAN --- Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..8.27 rows=1 width=244) Index Cond: (unique1 = 42) (2 rows) regression=# explain select * from tenk1 a(foo) where a.foo = 42; QUERY PLAN --- Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..8.27 rows=1 width=244) Index Cond: (foo = 42) (2 rows) This would be a bit of a problem for dblink or similar functions, which would have no way at all to know how the column names that they are dealing in got renamed. 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] pre-MED
David Fetter wrote: However, there are little lacunæ like this: SELECT * FROM show_qual() s(a) WHERE A NOT IN ( SELECT 'foo' UNION ALL SELECT 'bar' ); a (NOT (hashed subplan)) This little lacuna seems to say that if you pass the wrong query to dblink, it will fail to work, possibly with a very obscure error message; it will be very difficult to debug, and also very difficult (if not impossible) to workaround. -- 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] Simple postgresql.conf wizard
On 2008-11-02, at 02:02, Greg Smith wrote: Possible feedback topics: -Setting the next round of values requires asking the user for some input before making recommendations. Is it worth building a curses- based interface to updating the values? That would be really helpful for people with only ssh access to their server, but barring something like WCurses I'm not sure it would help on Windows. -How about a GUI one with Python's Tkinter interface? Now Windows isn't a problem, but people using ssh aren't going to be as happy. create interface , and module/library - that doesn't 1:1 reflect GUI - and you'll be able to create interface in any language/toolkit. Idea's great, I certainly know many ppl would love to see something like that. Cos so far, whole 'adjust setting in postgresql.conf' thing is a guess work. It would be even more good to put in some work into auto tuning of pgsql itself, with manual override in postgresql.conf :) (kinda like mssql). -- 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] How to parse Datum
There are several macros like DatumGetInt and so on. See postgres.h for details. Zdenek Zhe He napsal(a): I met a problem with parsing Datum, is there any good way to parse this kind of value? Because I want to get the value in an attribute of a table. In TupleTableSlot, there is a tts_values which is Datum type. Does anyone know how to parse it? Best Regards, Zhe HE TEL: (001) 646-789-3008 Address:965 Amsterdam Avenue, New York, NY 10025 Master Student, CS Dept. Columbia University www.columbia.edu/~zh2132 --- 07 Alumni Bachelor of Eng, BUPT www.bupt.edu.cn -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/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] PostgreSQL + Replicator developer meeting 10/28
Hannu Krosing wrote: On Tue, 2008-10-28 at 22:37 -0300, Alvaro Herrera wrote: If you mean that we're duplicating the effort that's already going elsewhere, my opinion is yes, we are. duplicating the effort is not always a bad thing. I was mostly suggesting to watch discussions and dig around in materials and/or asking people who have been working on these same issues. And of course to _think_ deeply about design before writing lots of duplicate code which ends up being an often inferior implementation of something that already exists, ( see: http://thedailywtf.com/Articles/The_Complicator_0x27_s_Gloves.aspx ) ;-) Are you asking whether the Replicator implementation contains gloves' replacement? Yes, we are. In fact, a lot of the time I've spent on Replicator code has been removing those (which were written by the original Replicator developers) and just using already existing gloves. For example we removed dealing TCP sockets directly; we now use libpq, to which we only had to add a single support function. I was a heavy user (at Skype) at some point and have helped in fixing some. But in the end we could not figure out how to make it robust and extracted the good stuff for pgQ and wrote our own replication based on that, which we could make perform and be robust when changing conf. A lot of the implementation effort has gone into the queue implementation. Personally I am not happy with it yet. Perhaps we should take a look at pgQ and remove our own implementation ... Do you transmit changes to and apply changes on slave as binary or as SQL statements ? We have a binary protocol. We haven't worked out the details yet but it certainly won't be SQL statements. Do slaves also have to be modified just to receive changes ? Modify in what form? Obviously they must be capable of decoding the binary protocol ... I think the hairy part will be getting the order of commands _exactly_ right (like Hot Standby again), but if you are similar to Slony/pgQ/Londiste in that you just transfer logical changes, not physical page-level changes, then the DDL locking on master may be enough to guarantee the right order. That is assuming that you already can guarantee right (commit time) order on slaves. this is not the same as transaction start order, which may give wrong/inconsistent data states. Yeah, we transfer logical changes, not physical. We enqueue transaction data at commit time (in commit order). I think being able to modify the Postgres commit source code was the only reason we could do it. -- 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 concurrency during recovery
On Mon, 2008-11-03 at 10:07 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: VACUUM with a btree index proceeds like this: 1. Scan table 2. Remove rows from btree identified in (1) 3. Remove rows from heap identified in (1) The purpose of the additional locking requirements during (2) for btrees is to ensure that we do not fail to find the rows identified in (1), because the rows can move after (1) and during (2) because of block splits. No, you are missing the point. One purpose of the additional locking requirements is to ensure that there is not a concurrent process that has read a btree index entry just before you removed it but arrives at the heap page only after you removed the heap entry (and, perhaps, replaced it with some other row that doesn't match the index entry at all). This is clearly still a risk in a hot-standby environment. OK, I think I get it now. Thanks for putting me straight. So I will implement the locking-every-page approach discussed upthread. So I will just keep note of the blocks touched exactly in that order and store the info accordingly onto the WAL records. Are you happy with my optimisation that if a page needs to be read in, we just skip it and pretend we did read-pin-unpin on it? I would implement that as a new ReadBuffer mode (in Heikki's new API terminology). If you know/can see any other missing correctness requirements please let me know. I've not had trouble understanding any of the other correctness requirements, but I'll leave it to review to judge whether I've implemented them all correctly. -- 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 concurrency during recovery
Simon Riggs [EMAIL PROTECTED] writes: Are you happy with my optimisation that if a page needs to be read in, we just skip it and pretend we did read-pin-unpin on it? If it's not in buffers then it cannot be pinned, so I concur that that is safe. 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
Euler Taveira de Oliveira wrote: Alvaro Herrera escreveu: So I gave up waiting for someone else to do the reloptions patch for autovacuum and started work on it myself. What I soon discovered is that on first blush it seems a lot easier than I had expected. Sorry about that. :( I was swamped with PGCon Brasil and then I took some days to rest. I'm expecting to finish it before next CF. So did this go anywhere? -- 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] don't use MAKE_PTR/OFFSET for shmem pointers
Alvaro Herrera [EMAIL PROTECTED] writes: We now have two very similar doubly-linked list implementations. Should we take one of them out? If you're thinking of dllist, it has considerably different implementation assumptions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgstat delay in autovacuum
Hi, With the new request-based pgstat reading mechanism, it is easy to fix an autovacuum bug. Basically for an autovac worker the delay is reduced, so that the data is very fresh. Patch attached. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. *** src/backend/postmaster/autovacuum.c 2 Nov 2008 21:24:52 - 1.85 --- src/backend/postmaster/autovacuum.c 3 Nov 2008 16:39:10 - *** *** 2149,2156 * It could have changed if something else processed the table while * we weren't looking. * ! * FIXME we ignore the possibility that the table was finished being ! * vacuumed in the last 500ms (PGSTAT_STAT_INTERVAL). This is a bug. */ MemoryContextSwitchTo(AutovacMemCxt); tab = table_recheck_autovac(relid, table_toast_map); --- 2149,2157 * It could have changed if something else processed the table while * we weren't looking. * ! * Note: we have a special case in pgstat code to ensure that the stats ! * we read are as up-to-date as possible, to avoid the problem that ! * somebody just finished vacuuming this table. */ MemoryContextSwitchTo(AutovacMemCxt); tab = table_recheck_autovac(relid, table_toast_map); *** src/backend/postmaster/pgstat.c 3 Nov 2008 01:17:08 - 1.182 --- src/backend/postmaster/pgstat.c 3 Nov 2008 16:37:13 - *** *** 3389,3396 * PGSTAT_STAT_INTERVAL; and we don't want to lie to the collector about * what our cutoff time really is. */ ! min_ts = TimestampTzPlusMilliseconds(GetCurrentTimestamp(), ! -PGSTAT_STAT_INTERVAL); /* * Loop until fresh enough stats file is available or we ran out of time. --- 3389,3400 * PGSTAT_STAT_INTERVAL; and we don't want to lie to the collector about * what our cutoff time really is. */ ! if (IsAutoVacuumWorkerProcess()) ! min_ts = TimestampTzPlusMilliseconds(GetCurrentTimestamp(), ! -PGSTAT_RETRY_DELAY); ! else ! min_ts = TimestampTzPlusMilliseconds(GetCurrentTimestamp(), ! -PGSTAT_STAT_INTERVAL); /* * Loop until fresh enough stats file is available or we ran out of time. -- 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] pgstat delay in autovacuum
Alvaro Herrera [EMAIL PROTECTED] writes: With the new request-based pgstat reading mechanism, it is easy to fix an autovacuum bug. Basically for an autovac worker the delay is reduced, so that the data is very fresh. Patch attached. It would be nice if the patch didn't render the adjacent comment a lie. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plperl needs upgrade for Fedora 10
Hello I am testing PostgreSQL on Federa 10. There is Perl 5.10. After successful compilation I got error CREATE LANGUAGE plperl; ERROR: could not oad library /plperl.so: ... undefined symbol: boot_DynaLoader Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new correlation metric
On Mon, 2008-11-03 at 18:33 +1100, Brendan Jurd wrote: If I'm grokking the thread, it looks like Tom suggested a substantial change in the approach (targetting per-index correlation rather than per-column) [1], and although you agreed with the spirit of his suggestion[2], there hasn't been a new version of the patch submitted in response. Yes, that's correct. Due to the substantial changes from the original version, I don't think our patch counts as being finished before November 1st. We're continuing to work on it, but I think that the earliest we'll be ready for review is at the end of the week. We don't want to hold anything up, so feel free to move on to another patch. If you still have time to review when we have a better patch, we'd appreciate your feedback even if it's too late for 8.4. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith wrote: One of the long-terms goals I'm working toward is wrapping a wizard interface around the tuning guidelines described by http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server now that those have gone through a fair amount of peer review. Attached is a first simple version of such a wizard, implemented in Python. Right now what it does is look the amount of memory in your system and adjust shared_buffers and effective_cache_size. So if we started with this stock configuration: Thats really nice. I have been playing with that idea also (and in python) but not really having time to do more then some simple tests. So its nice to see we have something to base development on. Thx for starting! Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] pg_typeof() patch review
On Nov 3, 2008, at 1:28 AM, Kurt Harriman wrote: 2) func.sgml: clarifying that the function returns an OID rather than a string Actually, it returns a regtype, no? 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] Simple postgresql.conf wizard
On Mon, 3 Nov 2008, Hannu Krosing wrote: And I'd suggest you use wxPython for GUI part if you want a relatively modern look. wxPython is GPL licensed and not popular enough to be available on a lot of systems already. I've spent enough time fighting with installing wxWidgets (for pgAdmin) to know that I really don't want to push that install onto end-users, and due to the license it's impractical to just bundle in some situations. There's no need for a fancy look here anyway, the only one I ever envisioned was a simple grid showing the recommendations the user could update before accepting. In general here, if it doesn't ship with the stock Python, there would have to be a really, really compelling reason to use any external library that adds more dependencies. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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_typeof() patch review
David E. Wheeler escribió: On Nov 3, 2008, at 1:28 AM, Kurt Harriman wrote: 2) func.sgml: clarifying that the function returns an OID rather than a string Actually, it returns a regtype, no? Yes -- regtype, which is an OID alias type. -- 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] pg_typeof() patch review
David E. Wheeler [EMAIL PROTECTED] writes: On Nov 3, 2008, at 1:28 AM, Kurt Harriman wrote: 2) func.sgml: clarifying that the function returns an OID rather than a string Actually, it returns a regtype, no? I thought the description was good, because it emphasizes that the result is-a OID; the table entry says regtype but people might not realize that that means they can use it as, eg, something to compare to pg_attribute.atttypid. 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_typeof() patch review
On Nov 3, 2008, at 10:02 AM, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: On Nov 3, 2008, at 1:28 AM, Kurt Harriman wrote: 2) func.sgml: clarifying that the function returns an OID rather than a string Actually, it returns a regtype, no? I thought the description was good, because it emphasizes that the result is-a OID; the table entry says regtype but people might not realize that that means they can use it as, eg, something to compare to pg_attribute.atttypid. Well, as someone who was until recently unfamiliar with regtypes, and who thinks of an OID as essentially just a number, I would find it very useful if the description indicated that, as a regtype, the return value could be used as either an OID or as string. Otherwise, I'd find the description kind of confusing (in one place it says it returns a regtype, whatever *that* is, and in one place it says an OID). Just thinking at this from the point of view of a relative newbiew… Thanks, 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] pg_typeof() patch review
David E. Wheeler escribió: Well, as someone who was until recently unfamiliar with regtypes, and who thinks of an OID as essentially just a number, I would find it very useful if the description indicated that, as a regtype, the return value could be used as either an OID or as string. Otherwise, I'd find the description kind of confusing (in one place it says it returns a regtype, whatever *that* is, and in one place it says an OID). Give this a read http://www.postgresql.org/docs/8.3/static/datatype-oid.html Maybe we should link to this page in the pg_typeof() description. Also, perhaps this page needs more examples. -- 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] pg_typeof() patch review
Kurt Harriman [EMAIL PROTECTED] writes: Brendan Jurd submitted a patch to add a pg_typeof() builtin function: http://archives.postgresql.org/pgsql-patches/2008-09/msg00029.php I've reviewed the patch and it looks fine. An updated version is attached, having made these changes: Applied, thanks. 3) catversion.h: updated catalog version with today's date Actually, best practice is simply to remind the committer in the text of the message that a catversion bump is required. Including that in the patch isn't very helpful for two reasons: it's unlikely to be the right date by the time the patch is applied, and it's *extremely* likely to result in a merge failure due to some other patch having gone in first. 4) pg_proc.h: placed the new entry in numerical order (Note: Does it matter how new pg_proc OIDs are assigned? I assume any available OID - 826 in this case - is as good as any other?) I put it beside pg_get_keywords since that was where it was in the docs and source code, and chose a free OID as close as I could get to that. There's not any real solid policy about manual OID assignment. In this case the only consideration I can think of is to try to avoid creating a merge conflict with other pending patches. Best chance at that (if you only need one OID) is to make sure you've sucked up a lone OID rather than a member of a block of free OIDs. 5) polymorphism.sql/polymorphism.out: added regression test for the new function I thought the test was overkill for a one-liner function, and simplified it a bit. I agree that no test at all might have been too little. I hope the attached patch is formatted ok - this is how it came from Mercurial. I applied it using patch -p 1. It worked fine, thanks. I do tend to find -c format more readable than -u, but in a case like this where it's all additions that doesn't make much difference. 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_typeof() patch review
On Nov 3, 2008, at 10:52 AM, Alvaro Herrera wrote: Give this a read http://www.postgresql.org/docs/8.3/static/datatype-oid.html Yeah. Maybe we should link to this page in the pg_typeof() description. Also, perhaps this page needs more examples. Yes, both of those would help a lot, I think. 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] Autovacuum and OldestXmin
Alvaro Herrera wrote: Simon Riggs wrote: [Also there is a comment saying this is a bug in autovacuum.c Are we thinking to go production with that phrase in the code?] Yeah, well, it's only a comment ;-) The problem is that a worker can decide that a table needs to be vacuumed, if another worker has finished vacuuming it in the last 500 ms. I proposed a mechanism to close the hole but it was too much of a hassle. I just committed a patch that should fix this problem. -- 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] Windowing Function Patch Review - Performance Comparison.
2008/11/2 David Rowley [EMAIL PROTECTED]: Hitoshi Harada Wrote: 2008/11/2 David Rowley [EMAIL PROTECTED]: Obervations: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled enable_seqscan but the planner still failed to choose index_scan. Is there any reason for this? Perhaps I'm missing something. Hitoshi, can you take a look at this? Ah, good point. Maybe it's because I haven't paid attention to choose index_scan for upper sort node. I just put the sort node whatever the downer node is, so it might be needed to sink the information down to scan choice process that we use sort node upper. Could someone point me out how to do it, or which part of the existing code would be a good guide? I know you need to wait for an answer about this, so I'd like to delay any further performance tests until that's sorted out as it should affect performance of larger tables quite a bit. 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. Attach is a delta patch against the last one. Also see the git diff: http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=commitdiff;h=bbba638f721a7e1d11cb3ee6af3bc1d7d3c11aa8;hp=48b73ee574779a14a3c36d373d8544d59a5b8b46 Regards, -- Hitoshi Harada window_functions.delta.patch.20081103 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] pgstat delay in autovacuum
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: With the new request-based pgstat reading mechanism, it is easy to fix an autovacuum bug. Basically for an autovac worker the delay is reduced, so that the data is very fresh. Patch attached. It would be nice if the patch didn't render the adjacent comment a lie. Yep, thanks -- fixed and committed. -- 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] pg_typeof() patch review
David E. Wheeler [EMAIL PROTECTED] writes: On Nov 3, 2008, at 10:52 AM, Alvaro Herrera wrote: Maybe we should link to this page in the pg_typeof() description. Also, perhaps this page needs more examples. Yes, both of those would help a lot, I think. Feel free to send in a docs patch ... 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] Re: [COMMITTERS] pgsql: Rework subtransaction commit protocol for hot standby.
Simon Riggs wrote: Even better idea: just use the InRecovery flag. Patch enclosed. Applied, thanks. -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1168)
KaiGai Kohei wrote: I just looked over the patch. This new version with row-level SQL security has certainly reduced the SE-Linux-specific part, which is good. It was interesting how you implemented SQL-level column-level permissions: CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) SECURITY_CONTEXT = 'system_u:object_r:sepgsql_secret_table_t' ); I am unclear how that will behave with the column-level permissions patch someone is working on. I am wondering if your approach is clearer than the other patch because it gives a consistent right policy for rows and columns. The column-level permissions in SE-PostgreSQL works independently and orthogonally from the upcoming column-level permissions by Stephen Frost. When the SE-PostgreSQL is enabled, both of facilities have to allow the client to access required columns. In the above case, the credit column has sepgsql_secret_table_t type, but rest of columns inherits the type of customer table which allows non-administrative users to access in the default security policy. If the given query contains the credit column, SE-PostgreSQL checks privileges of client to access columns labeled as sepgsql_secret_table_t, then it raises an error to abort the current transaction if the security policy does not allow it. There is a possibility that column-level ACLs are set via newer GRANT/REVOKE statement. In this case, the core PostgreSQL checks them, and raises an error if violated. OK. I am wondering if we _want_ two ways to set column permisions, especially since I think there will be only one way to set row-level permissions. I was wondering why you mention the NSA (U.S. National Security Agency) in the patch? +# NSA SELinux support The original author of SELinux is NSA. There is no more meanings than a caption of the option. I'll fix it, if necessary. Yes, please remove; the NSA suggests to me that this is an NSA-only feature, which it is not; it was just originally designed for them. The size of the patch is still larger but I don't see any way to reduce it: 1275 sepostgresql-docs-8.4devel-3-r1168.patch 625 sepostgresql-pg_dump-8.4devel-3-r1168.patch 829 sepostgresql-policy-8.4devel-3-r1168.patch 1736 sepostgresql-row_acl-8.4devel-3-r1168.patch 10847 sepostgresql-sepgsql-8.4devel-3-r1168.patch 1567 sepostgresql-tests-8.4devel-3-r1168.patch 16879 total I thought the sepostgresql-docs can be replaced by the pointing to the wiki page, how do you think the idea? No, I docs for using the tarball should be in the main documentation, even if they are not compile-enabled by default. The new patch affects the main Postgres backend code much less, which is a great improvement. -- 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] Fixes for psql describeOneTableDetails
Kris Jurka [EMAIL PROTECTED] writes: Attached are two and a half fixes for problems in psql's describeOneTableDetails function. Applied, thanks. 3) When describing a sequence it queries the sequence by name and was not schema qualifying it which is necessary when describing objects not in the search path. This is still busted because it does not correctly quote the identifiers. I fixed the quoting problem by using fmtId(), which seems to have been sucked into psql awhile back. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Grant proposal
Hi all, I'm new in list I have seen this on the todo list GRANT/REVOKE Allow GRANT/REVOKE permissions to be applied to all schema objects with one command The proposed syntax is: GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW TABLES IN public TO phpuser; is there anyone who is working on it? if yes I would like to join him, otherwise I would like working on it. Regards, Enrico -- That's one small step for man; one giant leap for mankind www.enricopirozzi.info [EMAIL PROTECTED] Skype sscotty71 -- 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
Big thanks for review. Robert Haas napsal(a): I tried to apply this patch to CVS HEAD and it blew up all over the place. It doesn't seem to be intended to apply against CVS HEAD; for example, I don't have backend/access/heap/htup.c at all, so can't apply changes to that file. You need to apply also two other patches: which are located here: http://wiki.postgresql.org/wiki/CommitFestInProgress#Upgrade-in-place_and_related_issues I moved one related patch from another category here to correct place. The problem is that it is difficult to keep it in sync with head, because they change a lot of things. It the reason why I put all also into GIT repository, but ... I was able to clone the GIT repository with the following command... git clone http://git.postgresql.org/git/~davidfetter/upgrade_in_place/.git ...but now I'm confused, because I don't see the changes from the diff reflected in the resulting tree. As you can see, I am not a git wizard. Any help would be appreciated. I'm GIT newbie I use mercurial for development and I manually applied changes into GIT. I asked David Fetter with help how to get back the correct clone. In meantime you can download a tarball. http://git.postgresql.org/?p=~davidfetter/upgrade_in_place/.git;a=snapshot;h=c72bafada59ed278ffac59657c913bc375f77808;sf=tgz It should contains every think including yesterdays improvements (delete, insert, update works - inser/update only on table without index). Here are a few initial thoughts based mostly on reading the diff: In the minor nit department, I don't really like the idea of PageHeaderData_04, SizeOfPageHeaderData04, PageLayoutIsValid_04, etc. I think the latest version should just be PageHeaderData and SizeOfPageHeaderData, and previous versions should be, e.g. PageHeaderDataV3. It looks to me like this would cut a few hunks out of this and maybe make it a bit easier to understand what is going on. At any rate, if we are going to stick with an explicit version number in both versions, it should be marked in a consistent way, not _04 sometimes and just 04 other times. My suggestion is e.g. V4 but YMMV. Yeah, it is most difficult part :-) find correct names for it. I think that each version of structure should have version suffix including lastone. And of cource the last one we should have a general name without suffix - see example: typedef struct PageHeaderData_04 { ...} PageHeaderData_04 typedef struct PageHeaderData_03 { ...} PageHeaderData_03 typedef PageHeaderData_04 PageHeaderData This allows you exactly specify version on places where you need it and keep general name where version is not relevant. How suffix should looks it another question. I prefer to have 04 not only 4. What's about PageHeaderData_V04? By the way what YMMV means? The changes to nodeIndexscan.c and nodeSeqscan.c are worrisome to me. It looks like the added code is (nearly?) identical in both places, so probably it needs to be refactored to avoid code duplication. I'm also a bit skeptical about the idea of doing the tuple conversion here. Why here rather than ExecStoreTuple()? If you decide to convert the tuple, you can palloc the new one, pfree the old one if ShouldFree is set, and reset shouldFree to true. Good point. I thought about it as a one variant. And if I look it close now it is really much better place. It should fix a problem why REINDEX does not work. I will move it. I am pretty skeptical of the idea that all of the HeapTuple* functions can just be conditionalized on the page version and everything will Just Work. It seems like that is too low a level to be worrying about such things. Even if it happens to work for the changes between V3 and V4, what happens when V5 or V6 is changed in such a way that the answer to HeapTupleIsWhatever is neither Yes nor No, but rather Maybe or Seven? The performance hit also sounds painful. I don't have a better idea right now though... OK. Currently it works (or I hope that it works). If somebody in a future invent some special change, i think in most (maybe all) cases there will be possible mapping. The speed is key point. When I check it last time I go 1% performance drop in fresh database. I think 1% is good price for in-place online upgrade. I think it's going to be absolutely imperative to begin vacuuming away old V3 pages as quickly as possible after the upgrade. If you go with the approach of converting the tuple in, or just before, ExecStoreTuple, then you're going to introduce a lot of overhead when working with V3 pages. I think that's fine. You should plan to do your in-place upgrade at 1AM on Christmas morning (or whenever your load hits rock bottom...) and immediately start converting the database, starting with your most important and smallest tables. In fact, I would look whenever possible for ways to make the V4 case a fast-path and just accept that the system is going to labor a bit when dealing with V3 stuff. Any
[HACKERS] Second thoughts about pg_typeof
I wonder if pg_typeof() should be marked stable instead of immutable? Its results certainly depend on the contents of system catalogs ... 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] Second thoughts about pg_typeof
Tom Lane wrote: I wonder if pg_typeof() should be marked stable instead of immutable? Its results certainly depend on the contents of system catalogs ... makes sense. 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] PageGetTempPage cleanup
Zdenek Kotala [EMAIL PROTECTED] writes: I attach patch which cleans up code around PageGetTempPage. These changes were discussed here: http://archives.postgresql.org/pgsql-hackers/2008-08/msg00102.php Applied with a minor change: instead of inventing Page PageGetTempPage(Page page, bool copy) I split it into two functions Page PageGetTempPage(Page page) Page PageGetTempPageCopy(Page page) I don't see any advantage to having the single function, because it doesn't seem like any calling code path would be likely to want both behaviors depending on some condition. Moreover, the way you had it meant that we'd be replacing Page PageGetTempPage(Page page, Size specialSize); with Page PageGetTempPage(Page page, bool copy); which seems risky to me. If someone failed to update code that was meant to call the old API, they'd get no warning about it --- at least not in any C compiler I'm familiar with. Changing the number of arguments guarantees a compile error for un-updated code. 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] [SQL] reliable lock inside stored procedure (SOLVED)
=?ISO-8859-1?Q?Sebastian_B=F6hm?= [EMAIL PROTECTED] writes: Am 03.11.2008 um 12:06 schrieb Richard Huxton: It's not possible to have a LOCK statement outside of a transaction. It's just not meaningful to have a transaction that only has a LOCK statement in it. as postgres does not warn you about this, this may lead to not so easy to spot bugs. That's a good point. We throw error for DECLARE CURSOR outside a transaction block, since it's obviously a mistake. I wonder whether we shouldn't equally throw error for LOCK outside a transaction block. I can sort of imagine some corner cases where lock-and-immediately-release would be the intended behavior, but that sure seems a whole lot less probable than it being user error. And you could always throw BEGIN/COMMIT into the command if that really was what you wanted. Objections anyone? 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] Simple postgresql.conf wizard
On Mon, 2008-11-03 at 12:42 -0500, Greg Smith wrote: On Mon, 3 Nov 2008, Hannu Krosing wrote: And I'd suggest you use wxPython for GUI part if you want a relatively modern look. wxPython is GPL licensed and not popular enough to be available on a lot of systems already. Wikipedia says thus about wxPython -- Being a wrapper, wxPython uses the same free software licence used by wxWidgets (LGPL) —which is approved by Free Software Foundation and Open Source Initiative. and wxWindows itself seems to be even more relaxed -- http://www.wxwidgets.org/about/newlicen.htm wxWidgets is currently licensed under the wxWindows Licence pending approval of the wxWidgets Licence which will be identical apart from the name. The wxWindows Licence is essentially the L-GPL (Library General Public Licence), with an exception stating that derived works in binary form may be distributed on the user's own terms. This is a solution that satisfies those who wish to produce GPL'ed software using wxWidgets, and also those producing proprietary software. I've spent enough time fighting with installing wxWidgets (for pgAdmin) to know that I really don't want to push that install onto end-users, and due to the license it's impractical to just bundle in some situations. I don't think that GPL forbids bundling. There's no need for a fancy look here anyway, the only one I ever envisioned was a simple grid showing the recommendations the user could update before accepting. In general here, if it doesn't ship with the stock Python, there would have to be a really, really compelling reason to use any external library that adds more dependencies. I think that though tkInter is kind-of included wit the standard python distribution, it is not always installed by default, or more exactly, tcl/tk is often not installed. In that regard I think that using curses is safest bet. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby v5 patch assertion failure
On Mon, 2008-11-03 at 06:41 +, Simon Riggs wrote: On Mon, 2008-11-03 at 12:16 +1300, Mark Kirkwood wrote: Trying out a few different scenarios I ran across this: CONTEXT: xlog redo update: rel 1663/16384/16397; tid 9614/62; new 158828/59 DEBUG: start recovery xid = 7002 lsn = 0/6F012EE4 CONTEXT: xlog redo update: rel 1663/16384/16397; tid 9614/62; new 158828/59 TRAP: FailedAssertion(!(!((UnobservedXids[index]) != ((TransactionId) 0))), File: procarray.c, Line: 2037) OK, thanks Mark. I'll start looking at it now. It's nice to know the exact line something fails on. I'd instrumented the whole of the UnobservedXids code to trap failures. I've had a couple of errors in that already during development. But what to do about it? I'm thinking the best way to handle this is just to simplify this part of the code some, rather than continue tweaking it. The code attempts to optimise the removal of UnobservedXids, but that feels now like a premature optimisation. So I can probably drop ~100 lines of code. I'm now adding the btree logic also, as well as updating the patch to current head. So I'll return with an updated patch as soon as all that's done and I've run a complete re-test. -- 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] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
On 2008-10-31, Gianni Ciolli [EMAIL PROTECTED] wrote: following the useful feedback that we received from this list, we would like to submit the patch for Bitmap Indexes for the november CommitFest (joint work of me with Gabriele Bartolini, starting from Gavin Sherry's patch). I skimmed through this on the plane -- I say skimmed because it had to be pretty quick before the battery ran out :( I have some first reactions but I admit these are pretty trivial detail points. I'm still trying to get a good feel for the overall structure which I fear is where any substantial feedback would come from. Firstly, there are a lot of pieces of #ifdef NOTUSED or #if 0 code which seem to be remnants of Gavin's code which are no longer relevant. That's pretty trivial for a committer to strip out but if you cut another patch it would be appreciated if you removed all that crud. Secondly the locking seems to be a bit overoptimistic. I'm pretty sure you have to take an exclusive lock on an index page any time you make any data modifications in index pages -- even if you're just setting a bit and not moving any data around. If two processes set two bits in the same word one can get lost in the race condition. There are a lot of comments in the code which imply that vacuuming is not implemented but in fact from what I can see it is -- sort of. It does rewrite the bitmap in bmbulkdelete but it doesn't have to rebuild the index from scratch. Are the comments out of date or am i misunderstanding them or the code? How complete is the vacuum implementation? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: Allow the UUID type to accept non-standard formats
Robert Haas wrote: The attached patch allows uuid_in() to parse a wider variety of variant input formats for the UUID data type, per the TODO named in the subject line. I have committed your patch. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Get Date value from Datum
Hi, I met some problem with get date type value from a table. I have a Datum which stores this value as an integer and I want to get its date value. Is there anyway that I can print it out? Best Regards, Zhe HE TEL: (001) 646-789-3008 Address:965 Amsterdam Avenue, New York, NY 10025 Master Student, CS Dept. Columbia University www.columbia.edu/~zh2132 --- 07 Alumni Bachelor of Eng, BUPT www.bupt.edu.cn
Re: [HACKERS] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
On Mon, 2008-11-03 at 17:37 -0500, Greg Stark wrote: Secondly the locking seems to be a bit overoptimistic. I'm pretty sure you have to take an exclusive lock on an index page any time you make any data modifications in index pages -- even if you're just setting a bit and not moving any data around. If two processes set two bits in the same word one can get lost in the race condition. I looked at that aspect of the patch specifically a few weeks back while checking for possible issues with Hot Standby. IIRC the patch is fairly careful with locking and uses Exclusive locks extensively throughout. I looked at both the theory and the implementation. Unless Gianni changed something in that regard recently, I don't understand that comment at all. Probably need to provide specific examples of your concerns. There are a lot of comments in the code which imply that vacuuming is not implemented but in fact from what I can see it is -- sort of. It does rewrite the bitmap in bmbulkdelete but it doesn't have to rebuild the index from scratch. Are the comments out of date or am i misunderstanding them or the code? How complete is the vacuum implementation? As I understood it, complete. I think the objective was minimal change away from Gavin's original. But it sounds like you found some out of date comments. Extensive docs have been added as a README, mainly because it was pretty hard to understand without them. -- 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] Get Date value from Datum
What do you want to print? The integer value or the date string like 29-01-2008. Use the date_out function to convert your value to date. --Imad On Tue, Nov 4, 2008 at 10:19 AM, Zhe He [EMAIL PROTECTED] wrote: Hi, I met some problem with get date type value from a table. I have a Datum which stores this value as an integer and I want to get its date value. Is there anyway that I can print it out? Best Regards, Zhe HE TEL: (001) 646-789-3008 Address:965 Amsterdam Avenue, New York, NY 10025 Master Student, CS Dept. Columbia University www.columbia.edu/~zh2132 --- 07 Alumni Bachelor of Eng, BUPT www.bupt.edu.cn -- 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] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
On Mon, 2008-11-03 at 23:28 +, Simon Riggs wrote: On Mon, 2008-11-03 at 17:37 -0500, Greg Stark wrote: Secondly the locking seems to be a bit overoptimistic. I'm pretty sure you have to take an exclusive lock on an index page any time you make any data modifications in index pages -- even if you're just setting a bit and not moving any data around. If two processes set two bits in the same word one can get lost in the race condition. I looked at that aspect of the patch specifically a few weeks back while checking for possible issues with Hot Standby. IIRC the patch is fairly careful with locking and uses Exclusive locks extensively throughout. I looked at both the theory and the implementation. Unless Gianni changed something in that regard recently, I don't understand that comment at all. Probably need to provide specific examples of your concerns. Just went through patch and checked all occurrences of BM_READ. I don't see any out of place: there's only 11 calls that use it. Note that there are multiple data structures in the index, just like GIN, so you need to look at which structure is being locked for each operation. -- 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] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
I looked at that aspect of the patch specifically a few weeks back while checking for possible issues with Hot Standby. IIRC the patch is fairly careful with locking and uses Exclusive locks extensively throughout. I looked at both the theory and the implementation. Unless Gianni changed something in that regard recently, I don't understand that comment at all. Probably need to provide specific examples of your concerns. The major thing there is to get the modifications right. There is no much sense in reviewing wrong code against locking issues. I wish to focus on the performance aspect of the patch, however, it turned out there are major issues with functionality: the index stores wrong tids inside :( I really would love to fix that issue and have a chance to validate the performance. Unfortunately, I have spent more than a day with almost void success. I have two testcases for which the index fails to get the correct result: Testcase 1 (I guess there is a conflict between _bitmap_formitem and mergewords): Basically I create a table with all the rows equal to 1 besides 19-th, which is 0. create table t1 as select case when i=19 then 0 else 1 end as i from generate_series(1,20) as s(i) create index t1ix on t1 using bitmap (i) where i = 0; set enable_seqscan=off; select ctid,i From t1 where i=0; -- no rows selected. Debug shows index suggests ctid==(0,35) instead of (0,19). 35==16+16+3. Testcase 2 create table t2 as select i, 0 j from generate_series(1,1000) as s(i); update t2 set j=1 where i in (5, 230) create index t2ix on t2 using bitmap(j) where j=1; set enable_seqscan=off; select ctid, i, j from t2 where j=1; -- no rows selected. Debug shows index suggests ctids==(0,97) and (0,98) instead of (4,97) and (4,98) -- it loses page number somewhere on the way. Both testcases reveal defects in index creation. Regards, Vladimir Sitnikov
Re: [HACKERS] Transactions and temp tables
Emmanuel Cecchet wrote: Here is the latest patch and the regression tests for the temp tables and 2PC issue. This fails: postgres=# begin; BEGIN postgres=# CREATE TEMPORARY TABLE temp1 (id int4); CREATE TABLE postgres=# PREPARE TRANSACTION 'foo'; PREPARE TRANSACTION postgres=# CREATE TEMPORARY TABLE temp2 (id int4); ERROR: cannot insert into frozen hashtable accessed temp tables I don't understand the bookkeeping of accessed and prepared temp tables in general. What's it for? The comments on preparedTempRel says that it keeps track of accessed temporary relations that have been prepared commit but not committed yet. That's never going to work as a backend-private hash table, because there's no way to remove entries from it when the prepared transaction is committed or rolled back from another backend. What's the purpose of checking that a table is empty on prepare? I think I'd feel more comfortable with the approach of only accepting PREPARE TRANSACTIOn if the accessed temp tables have been created and destroyed in the same transaction, to avoid possibly surprising behavior when a temp table is kept locked by a prepared transaction and you try to drop it later in the sesssion, but the patch allows more than that. I guess accessing an existing ON COMMIT DELETE ROWS temp table would also be OK, but checking that there's no visible rows in the table doesn't achieve that. I don't think you can just ignore prepared temp relations in findDependentObjects to avoid the lockup at backend exit. It's also used for DROP CASCADE, for example. -- 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] Enable pl/python to return records based on multiple OUT params
Hannu Krosing [EMAIL PROTECTED] writes: On Sat, 2008-11-01 at 06:13 +0200, Hannu Krosing wrote: This version is quite rough, though passes tests here. I will clean it up more during commitfest. probably still more things to do The status of this patch isn't clear --- are you still working on it? There certainly appear to be a lot of debug leftovers that need to be removed, error messages to clean up, etc. 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
Michael Meskes [EMAIL PROTECTED] writes: I finally have a working version. Please have a look at the attached files including a changed version of gram.y and and automatically created version of preproc.y. To recreate just run awk -f parse.awk gram.y preproc.y. Ping? I've been expecting a followup that actually has the attachments ... (You might need to compress the files if the message comes to more than 100-some KB. Also, given that preproc.y can be generated so easily, it could just be left out.) 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: Automatic view update rules
--On Donnerstag, Oktober 30, 2008 21:24:08 +0100 Bernd Helmle [EMAIL PROTECTED] wrote: Note that i'm still working on this (for example, RETURNING is missing yet), As always, discussion welcome ;) This new version implements RETURNING support for implicit view update rules and does some further cleanups. -- Thanks Bernd view_update.patch.bz2 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] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
On Mon, 2008-11-03 at 16:53 -0700, Vladimir Sitnikov wrote: The major thing there is to get the modifications right. There is no much sense in reviewing wrong code against locking issues. I didn't say there were no other bugs, nor would I know, only that I had reviewed the locking issues specifically because of the possible effects on Hot Standby. I haven't seen any problems that would be caused by locking. In general, all aspects of code needs to be checked, especially if there are bugs, else how would you resolve them? -- 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] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
BTW: is there a framework to test recovery related features? The only idea I could take from the top of my head is to comment out all the page writes and leave only WAL logging. Then crash database at random and verify if the index still performs as expected. Regards, Vladimir Sitnikov
Re: [HACKERS] Grant proposal
On Tue, Nov 4, 2008 at 6:12 AM, Enrico Pirozzi [EMAIL PROTECTED] wrote: Hi all, I'm new in list Hi Enrico, Just a heads-up, the Postgres hackers have just started on the final commitfest (patch review phase) for the 8.4 development cycle, so at the moment everyone is busy reviewing patches that have already been submitted. So, don't take it personally if nobody gets back to you on this for a while. =) 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] [SQL] reliable lock inside stored procedure (SOLVED)
That's a good point. We throw error for DECLARE CURSOR outside a transaction block, since it's obviously a mistake. I wonder whether we shouldn't equally throw error for LOCK outside a transaction block. I can sort of imagine some corner cases where lock-and-immediately-release would be the intended behavior, but that sure seems a whole lot less probable than it being user error. And you could always throw BEGIN/COMMIT into the command if that really was what you wanted. Objections anyone? No, I've been bitten by this myself. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new correlation metric
On Tue, Nov 4, 2008 at 4:21 AM, Jeff Davis [EMAIL PROTECTED] wrote: We don't want to hold anything up, so feel free to move on to another patch. If you still have time to review when we have a better patch, we'd appreciate your feedback even if it's too late for 8.4. No worries, thanks Jeff. I've moved your patch to Returned with Feedback in the commitfest for now. I'd be happy to do an initial review of the next version of the patch whenever it is ready. 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] [SQL] reliable lock inside stored procedure (SOLVED)
Robert Haas [EMAIL PROTECTED] writes: That's a good point. We throw error for DECLARE CURSOR outside a transaction block, since it's obviously a mistake. I wonder whether we shouldn't equally throw error for LOCK outside a transaction block. Objections anyone? No, I've been bitten by this myself. OK, done in CVS HEAD. 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] In-place upgrade
You need to apply also two other patches: which are located here: http://wiki.postgresql.org/wiki/CommitFestInProgress#Upgrade-in-place_and_related_issues I moved one related patch from another category here to correct place. Just to confirm, which two? http://git.postgresql.org/?p=~davidfetter/upgrade_in_place/.git;a=snapshot;h=c72bafada59ed278ffac59657c913bc375f77808;sf=tgz It should contains every think including yesterdays improvements (delete, insert, update works - inser/update only on table without index). Wow, sounds like great improvements. I understand your difficulties in keeping up with HEAD, but I hope we can figure out some solution, because right now I have a diff (that I can't apply) and a tarball (that I can't diff) and that is not ideal for reviewing. Yeah, it is most difficult part :-) find correct names for it. I think that each version of structure should have version suffix including lastone. And of cource the last one we should have a general name without suffix - see example: typedef struct PageHeaderData_04 { ...} PageHeaderData_04 typedef struct PageHeaderData_03 { ...} PageHeaderData_03 typedef PageHeaderData_04 PageHeaderData This allows you exactly specify version on places where you need it and keep general name where version is not relevant. That doesn't make sense to me. If PageHeaderData and PageHeaderData_04 are the same type, how do you decide which one to use in any particular place in the code? How suffix should looks it another question. I prefer to have 04 not only 4. What's about PageHeaderData_V04? I prefer V as a delimiter rather than _ because that makes it more clear that the number which follows is a version number, but I think _V is overkill. However, I don't really want to argue the point; I'm just throwing in my $0.02 and I am sure others will have their own views as well. By the way what YMMV means? Your Mileage May Vary. http://www.urbandictionary.com/define.php?term=YMMV I am pretty skeptical of the idea that all of the HeapTuple* functions can just be conditionalized on the page version and everything will Just Work. It seems like that is too low a level to be worrying about such things. Even if it happens to work for the changes between V3 and V4, what happens when V5 or V6 is changed in such a way that the answer to HeapTupleIsWhatever is neither Yes nor No, but rather Maybe or Seven? The performance hit also sounds painful. I don't have a better idea right now though... OK. Currently it works (or I hope that it works). If somebody in a future invent some special change, i think in most (maybe all) cases there will be possible mapping. The speed is key point. When I check it last time I go 1% performance drop in fresh database. I think 1% is good price for in-place online upgrade. I think that's arguable and something that needs to be more broadly discussed. I wouldn't be keen to pay a 1% performance drop for this feature, because it's not a feature I really need. Sure, in-place upgrade would be nice to have, but for me, dump and reload isn't a huge problem. It's a lot better than the 5% number you quoted previously, but I'm not sure whether it is good enough, I would feel more comfortable if the feature could be completely disabled via compile-time defines. Then you could build the system either with or without in-place upgrade, according to your needs. But I don't think that's very practical with HeapTuple* as functions. You could conditionalize away the switch, but the function call overhead would remain. To get rid of that, you'd need some enormous, fragile hack that I don't even want to contemplate. Really, what I'd ideally like to see here is a system where the V3 code is in essence error-recovery code. Everything should be V4-only unless you detect a V3 page, and then you error out (if in-place upgrade is not enabled) or jump to the appropriate V3-aware code (if in-place upgrade is enabled). In theory, with a system like this, it seems like the overhead for V4 ought to be no more than the cost of checking the page version on each page read, which is a cheap sanity check we'd be willing to pay for anyway, and trivial in cost. But I think we probably need some input from -core on this topic as well. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas [EMAIL PROTECTED] writes: Really, what I'd ideally like to see here is a system where the V3 code is in essence error-recovery code. Everything should be V4-only unless you detect a V3 page, and then you error out (if in-place upgrade is not enabled) or jump to the appropriate V3-aware code (if in-place upgrade is enabled). In theory, with a system like this, it seems like the overhead for V4 ought to be no more than the cost of checking the page version on each page read, which is a cheap sanity check we'd be willing to pay for anyway, and trivial in cost. We already do check the page version on read-in --- see PageHeaderIsValid. But I think we probably need some input from -core on this topic as well. I concur that I don't want to see this patch adding more than the absolute unavoidable minimum of overhead for data that meets the current layout definition. I'm disturbed by the proposal to stick overhead into tuple header access, for example. 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] [PATCHES] Solve a problem of LC_TIME of windows.
Hello, Saito-san: Hiroshi Saito [EMAIL PROTECTED] wrote: next patch is this. I'm reviewing your patch and cleanup some parts: - Avoid casting to LPWSTR. - Use pre-defined MAX_L10N_DATA instead of STRLEN_MAX. I'll send a new version. BTW, we convert strings multiple times in the function. Windows mbcs - UTF16 - UTF8 - server_encoding If we have 100% compatible encoding with Windows, we could skip UTF16 and UTF8 conversions. i.e, buflen = strftime(buffer); result = pg_do_encoding_conversion(buffer, buflen, GetPlatformEncoding(), GetDatabaseEncoding()); Is it possible to implement GetPlatformEncoding() ? I think it is also needed to treat non-ascii file path in COPY, LOAD, archive_command and so on. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center strftime_win32.patch Description: Binary data result_sjis.txt 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] [WIP] In-place upgrade
We already do check the page version on read-in --- see PageHeaderIsValid. Right, but the only place this is called is in ReadBuffer_common, which doesn't seem like a suitable place to deal with the possibility of a V3 page since you don't yet know what you plan to do with it. I'm not quite sure what the right solution to that problem is... But I think we probably need some input from -core on this topic as well. I concur that I don't want to see this patch adding more than the absolute unavoidable minimum of overhead for data that meets the current layout definition. I'm disturbed by the proposal to stick overhead into tuple header access, for example. ...but it seems like we both agree that conditionalizing heap tuple header access on page version is not the right answer. Based on that, I'm going to move the htup and bufpage API clean up patch to Returned with feedback and continue reviewing the remainder of these patches. As I'm looking at this, I'm realizing another problem - there is a lot of code that looks like this: void HeapTupleSetXmax(HeapTuple tuple, TransactionId xmax) { switch(tuple-t_ver) { case 4 : tuple-t_data-t_choice.t_heap.t_xmax = xmax; break; case 3 : TPH03(tuple)-t_choice.t_heap.t_xmax = xmax; break; default: elog(PANIC, HeapTupleSetXmax is not supported.); } } TPH03 is a macro that is casting tuple-t_data to HeapTupleHeader_03. Unless I'm missing something, that means that given an arbitrary pointer to HeapTuple, there is absolutely no guarantee that tuple-t_data-t_choice actually points to that field at all. It will if tuple-t_ver happens to be 4 OR if HeapTupleHeader and HeapTupleHeader_03 happen to agree on where t_choice is; otherwise it points to some other member of HeapTupleHeader_03, or off the end of the structure. To me that seems unacceptably fragile, because it means the compiler can't warn us that we're using a pointer inappropriately. If we truly want to be safe here then we need to create an opaque HeapTupleHeader structure that contains only those elements that HeapTupleHeader_03 and HeapTupleHeader_04 have in common, and cast BOTH of them after checking the version. That way if somone writes a function that attempts to deference a HeapTupleHeader without going through the API, it will fail to compile rather than mostly working but possibly failing on a V3 page. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1168)
Bruce Momjian wrote: KaiGai Kohei wrote: I just looked over the patch. This new version with row-level SQL security has certainly reduced the SE-Linux-specific part, which is good. It was interesting how you implemented SQL-level column-level permissions: CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) SECURITY_CONTEXT = 'system_u:object_r:sepgsql_secret_table_t' ); I am unclear how that will behave with the column-level permissions patch someone is working on. I am wondering if your approach is clearer than the other patch because it gives a consistent right policy for rows and columns. The column-level permissions in SE-PostgreSQL works independently and orthogonally from the upcoming column-level permissions by Stephen Frost. When the SE-PostgreSQL is enabled, both of facilities have to allow the client to access required columns. In the above case, the credit column has sepgsql_secret_table_t type, but rest of columns inherits the type of customer table which allows non-administrative users to access in the default security policy. If the given query contains the credit column, SE-PostgreSQL checks privileges of client to access columns labeled as sepgsql_secret_table_t, then it raises an error to abort the current transaction if the security policy does not allow it. There is a possibility that column-level ACLs are set via newer GRANT/REVOKE statement. In this case, the core PostgreSQL checks them, and raises an error if violated. OK. I am wondering if we _want_ two ways to set column permisions, especially since I think there will be only one way to set row-level permissions. I think we should not see the feature from only the viewpoint of granularity in access controls. The both of new security features (sepgsql and rowacl) are enhanced security features, but the Stephen's efforts is one of the core features based on SQL-standard and enabled in the default. Please pay mention that any given queries have to be checked by the core facility, and can be checked by the enhanced one if enabled. The PGACE security framework enables us to implement various kind of enhanced security features, and has two guest facilities now. They can have its own security model and granularities as a part of its design. The one has its granularities with some of overlaps on tables/columns/functions, and the other also has its granularity without overlaps because its purpose is supplement of the core security facilities. So, it is not a strange design there is only one way to set row-level permissions, because the current SQL-standard does not have its specifications and no core facilities are here. If the future version of PostgreSQL got a newer row-level permissions defined within SQL-standard, I think there should be two ways to set row-level ones for both of the core and enhanced. I was wondering why you mention the NSA (U.S. National Security Agency) in the patch? +# NSA SELinux support The original author of SELinux is NSA. There is no more meanings than a caption of the option. I'll fix it, if necessary. Yes, please remove; the NSA suggests to me that this is an NSA-only feature, which it is not; it was just originally designed for them. OK, I modified the caption from NSA SELinux to SELinux. http://code.google.com/p/sepgsql/source/detail?r=1170 The size of the patch is still larger but I don't see any way to reduce it: 1275 sepostgresql-docs-8.4devel-3-r1168.patch 625 sepostgresql-pg_dump-8.4devel-3-r1168.patch 829 sepostgresql-policy-8.4devel-3-r1168.patch 1736 sepostgresql-row_acl-8.4devel-3-r1168.patch 10847 sepostgresql-sepgsql-8.4devel-3-r1168.patch 1567 sepostgresql-tests-8.4devel-3-r1168.patch 16879 total I thought the sepostgresql-docs can be replaced by the pointing to the wiki page, how do you think the idea? No, I docs for using the tarball should be in the main documentation, even if they are not compile-enabled by default. The new patch affects the main Postgres backend code much less, which is a great improvement. OK, I could understand it. 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] Transactions and temp tables
Hi Heikki, Emmanuel Cecchet wrote: Here is the latest patch and the regression tests for the temp tables and 2PC issue. This fails: postgres=# begin; BEGIN postgres=# CREATE TEMPORARY TABLE temp1 (id int4); CREATE TABLE postgres=# PREPARE TRANSACTION 'foo'; PREPARE TRANSACTION postgres=# CREATE TEMPORARY TABLE temp2 (id int4); ERROR: cannot insert into frozen hashtable accessed temp tables I will address that. I don't understand the bookkeeping of accessed and prepared temp tables in general. What's it for? Right now (in 8.3) the bookkeeping prevents a transaction that has used a temp table to prepare commit. As you mentioned earlier (http://archives.postgresql.org/pgsql-hackers/2008-02/msg01277.php) we should be able to allow CREATE+DROP in the same transaction. The comments on preparedTempRel says that it keeps track of accessed temporary relations that have been prepared commit but not committed yet. That's never going to work as a backend-private hash table, because there's no way to remove entries from it when the prepared transaction is committed or rolled back from another backend. It does not really matter since we only allow empty temp tables at prepared time. And the transaction can only be prepared locally. If the transaction is committed or rolled back from another backend, the only thing that can happen is that tables that were created in the transaction will remain in the list. They will be ignored at the next prepare since the relation will not exist anymore. Once again, the tables remaining in the list after prepare are empty. What's the purpose of checking that a table is empty on prepare? I think I'd feel more comfortable with the approach of only accepting PREPARE TRANSACTIOn if the accessed temp tables have been created and destroyed in the same transaction, to avoid possibly surprising behavior when a temp table is kept locked by a prepared transaction and you try to drop it later in the sesssion, but the patch allows more than that. I guess accessing an existing ON COMMIT DELETE ROWS temp table would also be OK, Yes, I was trying to allow also ON COMMIT DROP and ON COMMIT DELETE ROW. An empty temp table at PREPARE time would be similar to an ON COMMIT DELETE ROW table. but checking that there's no visible rows in the table doesn't achieve that. If the relation exist but contains no row, is it possible that the table is not empty? What would I need to do to ensure that the table is empty? I don't think you can just ignore prepared temp relations in findDependentObjects to avoid the lockup at backend exit. It's also used for DROP CASCADE, for example. Do you mean that it will break the DROP CASCADE behavior in general, or that would break the behavior for master/child temp tables? By the way, does Postgres support child temp tables? Thanks for the feedback. I will address the problem of the frozen hash list but let me know what you think of the other potential issues. Emmanuel -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: [EMAIL PROTECTED] Skype: emmanuel_cecchet -- 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] Extending pg_class info + more flexible TOAST chunk size
Zdenek, It seems like there is general agreement that this patch needs some changes before being considered for application. Is that correct? http://archives.postgresql.org/pgsql-hackers/2008-11/msg00049.php Are you planning to send a new version for this CommitFest, or is this 8.5 material at this point? Thanks, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pre-MED
On Fri, Oct 31, 2008 at 07:48, David Fetter [EMAIL PROTECTED] wrote: Please find attached a patch which works in PL/Perl, the work having been done by Andrew (RhodiumToad) Gierth. It's not clear to me how this would be generally surface-able to SQL, though. Any ideas? I was assigned to review this. A few comments beside the ones Alavaro and Tom pointed out... select * from dblink('select * from foo'); segfaults due to rsi in dblink_record_internal() never being initialized. You probably know that though and are just trying to show how dblink could use it... select * from dblink('select * from foo where..') s(a) where ...; seems like it will fail (due to the 2 where clauses) with a strange error message (though its hard to tell without documentation :)) Why the where clause and not table/joins/target list etc? Maybe instead of having this for PL's we should just keep the part that composes the qualifiers into strings (rsinfo_get_qual_str()) so that only contrib modules can use it? And for now dblink will just be the only user? Or what exactly is the use case for PL languages to have it? SQL/MED ? I started following http://wiki.postgresql.org/wiki/Reviewing_a_Patch. But it felt useless until you make Tom and Alavaro happy :) Or at least I have a patch that I can play with :) -- 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] pre-MED
On Thu, Oct 30, 2008 at 05:16, Tom Lane [EMAIL PROTECTED] wrote: David Fetter [EMAIL PROTECTED] writes: On Wed, Oct 29, 2008 at 10:23:36PM -0400, Tom Lane wrote: I would argue that it's already designed wrong if there's need for PL-specific implementation effort. I'm not sure how else to do this. The current implementation returns char *, which doesn't translate uniformly into the PLs. Surely they all have a way to call a SQL function that returns text. Sure but when you call that function you get *that* functions qualifier. And unless there already is a way to grab the parent query qualifiers, the PL specific hacks seem not so bad and very similar to how we have to build trigger arguments for every PL already. -- 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
On Thu, Oct 9, 2008 at 03:06, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Thanks for your reviewing, Alex. I applied your comments to my patch. Sorry for the late reply! Somehow I missed this, saw it on the commit fest wiki :) *custom_guc_flags-0828.patch My only other concern is the changes to DefineCustom*() to tag the new flags param. Now I think anyone who uses Custom gucs will want/should be able to set that. I did not see any people in contrib using it but did not look on PGfoundry. Do we need to document the change somewhere for people who might be using it??? Now it is done with DefineCustomVariable(type, variable) and keep existing functions as-is for backward compatibility. Ok that seems better... Some people will be happy if the functions are documented, but we need to define 'stable-internal-functions' between SPI (stable expoted functions) and unstable internal functions. Right, thats why I was asking :) *auto_explalin.c: init_instrument() The only cleaner way I can see is to add a hook for CreateQueryDesc so we can overload doInstrument and ExecInitNode will InstrAlloc them all for us. I wanted to avoid modifying core codes as far as possible, but I see it was ugly. Now I added 'force_instrument' global variable as a hook for CreateQueryDesc. Yeah, well if we are not to worried about it getting out of sync when people add new node/scan types what you had before was probably ok. I was just trying to stimulate my own and maybe others brains who are on the list that might have better ideas. But at least now the commiter has 2 options here :) the only other comment I have is suset_assign() do we really need to be a superuser if its all going to LOG ? There was some concern about explaining security definer functions right? but surely a regular explain on those shows the same thing as this explain? Or what am I missing? Almost logging options in postgres are only for superusers. So I think auto_explain options should not be modified by non-superusers, too. Ok thanks that makes sense. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Stack trace
Hi all, Is there any way to print out the stack trace of the current location? I am looking for something like print_stack_trace(); that I can insert in arbitrary location in the code. Thank you, Regards, Bramandia R.
Re: [HACKERS] Stack trace
Bramandia Ramadhana wrote: Is there any way to print out the stack trace of the current location? Not sure if Postgres has something in the utils for that. You can use backtrace() in glibc. Solaris 9 libc has printstack(). Not sure what's available for Windows. I am looking for something like print_stack_trace(); that I can insert in arbitrary location in the code. Some references: http://www.tlug.org.za/wiki/index.php/Obtaining_a_stack_trace_in_C_upon_SIGSEGV http://www.delorie.com/gnu/docs/glibc/libc_665.html manu -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: [EMAIL PROTECTED] Skype: emmanuel_cecchet -- 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 SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
On Thu, Sep 18, 2008 at 6:03 AM, Ron Mayer [EMAIL PROTECTED] wrote: The attached patch (1) adds a new GUC called IntervalStyle that decouples interval output from the DateStyle GUC, and (2) adds a new interval style that will match the SQL standards for interval literals when given interval data that meets the sql standard (year-month or date-time only; and no mixed sign). Hi Ron, I've been assigned to do an initial review of your interval patches. I'm going to be reviewing them one at a time, starting with this one (the introduction of the new IntervalStyle GUC). I grabbed the latest version of the patch from the URL posted up on the CF wiki page: http://0ape.com/postgres_interval_patches/stdintervaloutput.patch Nice site you've got set up for the patches, BTW. It certainly makes it all a lot more approachable. On with the review then ... The patch applied cleanly to the latest version of HEAD in the git repository. I was able to build both postgres and the documentation without complaint on x86_64 gentoo. When I ran the regression tests, I got one failure in the new interval tests. Looks like the nonstandard extended format gets a bit confused when the seconds are negative: *** /home/direvus/src/postgres/src/test/regress/expected/interval.out Tue Nov 4 14:46:34 2008 --- /home/direvus/src/postgres/src/test/regress/results/interval.out Tue Nov 4 15:19:53 2008 *** *** 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789 (1 row) --- 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:-6.789 (1 row) Otherwise, the feature seemed to behave as advertised. I tried throwing a few bizarre intervals at it, but didn't manage to break anything. The C code has some small stylistic inconsistencies; in some cases the spaces around binary operators are missing (e.g., (fsec0)). See src/backend/utils/adt/datetime.c lines 3691, 3694, 3697, 3729-3731. There are also a lot of function calls missing the space after the argument separator (e.g., sprintf(cp,%d %d:%02d:,mday,hour,min)). Apart from not merging well with the style of the surrounding code, I respectfully suggest that omitting the spaces really does make the code harder to read. The new documentation is good in terms of content, but there are some minor stylistic and spelling cleanups I would suggest. The standard is referred to variously as SQL standard, SQL-standard and SQL Standard in the patch. The surrounding documentation seems to use SQL standard, so that's probably the way to go. These sentences in datatype.sgml are a bit awkward: The postgres style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to ISO. The postgres_verbose style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to SQL. As far as I know, outputed isn't a word, and singling out 8.3 in particular is a bit misleading, since the statement applies to earlier versions as well. I would go with something more along the lines of: The postgres style will output intervals matching those output by PostgreSQL prior to version 8.4, with the DateStyle parameter set to ISO. Likewise in config.sgml, the patch has: The value postgres will output intervals in a format that matches what old releases had output when the DateStyle was set to 'ISO'. The value postgres_verbose will output intervals in a format that matches what old releases had output when the DateStyle was set to 'SQL'. I don't think old releases is specific enough. Most folks reading the documentation aren't going to know what is meant by old. Better to be precise. Again I would suggest phrasing like ... releases prior to 8.4, with the DateStyle set to That's all the feedback I have for the moment. I hope you found my comments helpful. I'll be setting the status of this patch to Returned with Feedback and wait for your reponses before I move forward with reviewing the other patches. 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
[HACKERS] Hot standby v5 patch - restarted replica changes to warm standby mode
While doing some tests yesterday I ran into the situation where the standby database would appear to go back into 'warm' mode after it was restarted. The set of steps to reproduce the behaviour is: 1/ Setup master and replica with replica using pg_standby 2/ Initialize pgbench schema with size 100 in database 'postgres' 3/ Connect to replica, then disconnect (this step is not necessary I *think* - just for checking that connection works at this point!) 4/ Shutdown and restart the replica - there is no database has now reached consistent state message in the log, and you cannot connect Again this is head from 2nd Nov with v5 patch applied on Freebsd 7.1-Prerelease. The log fragment is: LOG: restored log file 00010068 from archive DEBUG: executing restore command pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5439 /data0/pgarchive/8.4 00010069 pg_xlog/RECOVERYXLOG 00010060 2standby.log DEBUG: forked new backend, pid=2981 socket=7 FATAL: the database system is starting up DEBUG: proc_exit(1) DEBUG: shmem_exit(1) DEBUG: exit(1) DEBUG: reaping dead processes DEBUG: server process (PID 2981) exited with exit code 1 regards Mark -- 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] Grant proposal
Just a heads-up, the Postgres hackers have just started on the final commitfest (patch review phase) for the 8.4 development cycle, so at the moment everyone is busy reviewing patches that have already been submitted. Yes I know that :) So, don't take it personally if nobody gets back to you on this for a while. =) Cheers, BJ That's no problem, so I begin to working on it Regards, Enrico -- That's one small step for man; one giant leap for mankind www.enricopirozzi.info [EMAIL PROTECTED] Skype sscotty71 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Hot standby v5 patch - Databases created post backup remain inaccessible + replica SIGSEGV when coming out of standby
On Tue, 2008-11-04 at 18:33 +1300, Mark Kirkwood wrote: Another corner case: 1/ Setup master and replica with replica using pg_standby 2/ Create a new database (I used 'bench') 3/ Initialize the pgbench schema of size 100 in 'bench' (just to ensure the logs with the db creation get archived) 3/ Attempt to connect to 'bench' on the replica Head from 2nd Nov with v5 patch applied on Freebsd 7.1-Prerelease as usual Case acknowledged. -- 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] plperl needs upgrade for Fedora 10
postgres=# select version(); version -- PostgreSQL 8.3.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20080917 (Red Hat 4.3.2-4) (1 row) postgres=# CREATE LANGUAGE plperlu; ERROR: could not load library /usr/local/pgsql8.3/lib/plperl.so: /usr/local/pgsql8.3/lib/plperl.so: undefined symbol: boot_DynaLoader postgres=# Regards Pavel Stehule 2008/11/3 Andrew Dunstan [EMAIL PROTECTED]: Pavel Stehule wrote: Hello I am testing PostgreSQL on Federa 10. There is Perl 5.10. After successful compilation I got error CREATE LANGUAGE plperl; ERROR: could not oad library /plperl.so: ... undefined symbol: boot_DynaLoader Regards Pavel Stehule Please send the build log for plperl also, and you configure settings. I have previously built against perl 5.10 quite happily. cheers andrew Makefile.global.gz Description: GNU Zip compressed data configure.log.gz Description: GNU Zip compressed data makeall.log.gz Description: GNU Zip compressed data makeinstall.log.gz Description: GNU Zip compressed 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] Re: Hot standby v5 patch - restarted replica changes to warm standby mode
On Tue, 2008-11-04 at 18:33 +1300, Mark Kirkwood wrote: While doing some tests yesterday I ran into the situation where the standby database would appear to go back into 'warm' mode after it was restarted. The set of steps to reproduce the behaviour is: 1/ Setup master and replica with replica using pg_standby 2/ Initialize pgbench schema with size 100 in database 'postgres' 3/ Connect to replica, then disconnect (this step is not necessary I *think* - just for checking that connection works at this point!) 4/ Shutdown and restart the replica - there is no database has now reached consistent state message in the log, and you cannot connect How did you shutdown the database? Fast? Immediate mode acts just as it does on an unpatched server. Can you give more details of exactly what you did? Thanks. Not saying there isn't a problem, just don't understand what happened. Not being able to connect after a restart is a design feature, to protect you from running potentially invalid queries. -- 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
[HACKERS] Hot standby v5 patch - Databases created post backup remain inaccessible + replica SIGSEGV when coming out of standby
Another corner case: 1/ Setup master and replica with replica using pg_standby 2/ Create a new database (I used 'bench') 3/ Initialize the pgbench schema of size 100 in 'bench' (just to ensure the logs with the db creation get archived) 3/ Attempt to connect to 'bench' on the replica Head from 2nd Nov with v5 patch applied on Freebsd 7.1-Prerelease as usual postgres=# \l List of databases Name| Owner | Encoding | Collation | Ctype | Access Privileges ---+--+---+---+---+- bench | postgres | SQL_ASCII | C | C | postgres | postgres | SQL_ASCII | C | C | template0 | postgres | SQL_ASCII | C | C | {=c/postgres,postgres=CTc/postgres} template1 | postgres | SQL_ASCII | C | C | {=c/postgres,postgres=CTc/postgres} (4 rows) postgres=# \c bench FATAL: database bench does not exist Previous connection kept Not sure if this is related at all, but if the replica is then instructed to finish recovery via touching its trigger file, then we get: DEBUG: executing restore command pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5439 /data0/pgarchive/8.4 0001.history pg_xlog/RECOVERYHISTORY 2standby.log DEBUG: could not restore file 0001.history from archive: return code 0 DEBUG: moving last restored xlog to pg_xlog/00020068 LOG: archive recovery complete DEBUG: Clear UnobservedXids LOG: clearing recovery locks DEBUG: reaping dead processes LOG: startup process (PID 4254) was terminated by signal 11: Segmentation fault LOG: aborting startup due to startup process failure DEBUG: proc_exit(1) DEBUG: shmem_exit(1) DEBUG: exit(1) Using gdb: #0 RelationClearRecoveryLocks () at inval.c:1702 1702xl_rel_lock *lock = (xl_rel_lock *) lfirst(l); (gdb) bt #0 RelationClearRecoveryLocks () at inval.c:1702 #1 0x080d3849 in StartupXLOG () at xlog.c:5959 #2 0x080f1680 in AuxiliaryProcessMain (argc=2, argv=0xbfbfe6e8) at bootstrap.c:421 #3 0x08214d4d in StartChildProcess (type=StartupProcess) at postmaster.c:4104 #4 0x0821725b in PostmasterMain (argc=1, argv=0xbfbfec50) at postmaster.c:1034 #5 0x081bfa7b in main (argc=1, argv=0xbfbfec50) at main.c:188 regards Mark -- 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] Bitmap Indexes patch (was Re: Bitmap Indexes: request for feedback)
On Mon, 2008-11-03 at 23:28 +, Simon Riggs wrote: On Mon, 2008-11-03 at 17:37 -0500, Greg Stark wrote: There are a lot of comments in the code which imply that vacuuming is not implemented but in fact from what I can see it is -- sort of. It does rewrite the bitmap in bmbulkdelete but it doesn't have to rebuild the index from scratch. Are the comments out of date or am i misunderstanding them or the code? How complete is the vacuum implementation? As I understood it, complete. Looking at the code, it looks like my understanding was complete-ly wrong and your comments seem accurate. -- 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] Get Date value from Datum
On Tue, Nov 4, 2008 at 12:30 AM, imad [EMAIL PROTECTED] wrote: What do you want to print? The integer value or the date string like 29-01-2008. Use the date_out function to convert your value to date. --Imad On Tue, Nov 4, 2008 at 10:19 AM, Zhe He [EMAIL PROTECTED] wrote: Hi, I met some problem with get date type value from a table. I have a Datum which stores this value as an integer and I want to get its date value. Is there anyway that I can print it out? Maybe inspecting the code of debugtup also helps, see src/backend/access/common/printtup.c (http://git.postgresql.org/?p=postgresql.git;a=blob;f=src/backend/access/common/printtup.c#l504) --Hannes -- 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] Enable pl/python to return records based on multiple OUT params
On Mon, 2008-11-03 at 19:07 -0500, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: On Sat, 2008-11-01 at 06:13 +0200, Hannu Krosing wrote: This version is quite rough, though passes tests here. I will clean it up more during commitfest. probably still more things to do The status of this patch isn't clear --- are you still working on it? There certainly appear to be a lot of debug leftovers that need to be removed, error messages to clean up, etc. It passes all existing regression tests and works fine for correct use, but even the code currently in CVS crashes the backend for this py=# create or replace function add_any(in i1 anyelement, in i2 anyelement, out t text) language plpythonu as $$ return i1 + i2 $$; CREATE FUNCTION py=# select * from add_any(1,2); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! Though it is a somewhat separate problem from current patch I'd like to do something about it before having it all committed, as the fix must touch the very same places than this patch. I think it takes two-tree days to figure out proper way to fix it. I'd like it to just accept ANY* and do the right thing but I may end up just rejecting ANY* on both IN and OUT args. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers