Re: [HACKERS] Application name patch - v4
2009/12/2 Tom Lane t...@sss.pgh.pa.us: Dave Page dp...@pgadmin.org writes: On Tue, Dec 1, 2009 at 4:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't think that we need to bump the protocol version. The real alternative here would be that libpq sends a startup packet that includes application_name, and if it gets an error back from that, it starts over without the app name. I looked (briefly) at doing that when we first ran into this suggestion. As you pointed out at the time, it seemed like that would require some fairly ugly hackery in fe-connect.c I've committed a change for this. It turns out not to be quite as ugly as I thought, and in fact quite a bit less code than the other method. The reason it's less intertwined with the other retry logic than I was expecting is that the server only looks at the startup options after it's completed the authentication process. So the failure retry for this amounts to an outer loop around the SSL and protocol-version retries. Logically anyway --- as far as the actual code goes it's another path in the state machine, and just requires a few more lines. I tested it with some simple cases such as password authentication, but it would be good to confirm that it does the right thing in more complex cases like SSL prefer/allow/require and Kerberos auth. Anyone set up to try CVS HEAD against an older server with configurations like that? BTW, it strikes me that it would only be a matter of a couple of lines to persuade older servers to ignore application_name in the startup packet, instead of throwing a tantrum. Obviously we must make libpq work against unpatched older servers, but if we can save a connection cycle (and some bleating in the postmaster log) when talking to an 8.5 application, it might be worth doing: *** src/backend/tcop/postgres.c.orig Thu Jun 18 06:08:08 2009 --- src/backend/tcop/postgres.c Wed Dec 2 00:05:05 2009 *** *** 3159,3164 --- 3159,3168 value = lfirst(gucopts); gucopts = lnext(gucopts); + /* Ignore application_name for compatibility with 8.5 libpq */ + if (strcmp(name, application_name) == 0) + continue; + if (IsSuperuserConfigOption(name)) PendingConfigOption(name, value); else If we patch the back branches like that, anyone who's annoyed by the extra connection cycle just has to update to latest minor release of their server to make it work more smoothly. Comments? regards, tom lane Given that this can probably be considered an *extremely* safe patch :-), I say go for it. It'll certainly make for less error reports around something that's not an error. If the patch was in any way complex I'd object against it, but this clearly isn't... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] add more frame types in window functions (ROWS)
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi As earlier mail, I added aggcontext to WindowAggState. This issue (as detailed in this post): http://archives.postgresql.org/pgsql-hackers/2009-11/msg01871.php is currently the only significant outstanding issue in my review of this patch. I think we need to see more feedback on whether it is acceptable to change the aggregate function API again (and if so, what to do with it) before I can post a final review on this and mark it ready for committer (or not). -- 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] Application name patch - v4
On Wed, Dec 2, 2009 at 8:14 AM, Magnus Hagander mag...@hagander.net wrote: If we patch the back branches like that, anyone who's annoyed by the extra connection cycle just has to update to latest minor release of their server to make it work more smoothly. Comments? regards, tom lane Given that this can probably be considered an *extremely* safe patch :-), I say go for it. It'll certainly make for less error reports around something that's not an error. If the patch was in any way complex I'd object against it, but this clearly isn't... Agreed. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] cannot compile CVS HEAD
Hello I have a problem with compilation: make[4]: Entering directory `/home/pavel/src/pgsql/src/backend/utils/adt' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I../../../../src/include -D_GNU_SOURCE -c -o int.o int.c int.c: In function ‘int4xor’: int.c:1209: error: ‘arg2’ undeclared (first use in this function) int.c:1209: error: (Each undeclared identifier is reported only once int.c:1209: error: for each function it appears in.) int.c:1207: warning: unused variable ‘arg20’ make[4]: *** [int.o] Error 1 regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Windows x64
Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 1, 2009 at 6:25 AM, Tsutomu Yamada tsut...@sraoss.co.jp wrote: Hello. The following patches support Windows x64. 1) use intptr_t for Datum and pointer macros. (to support Windows LLP64) almost the same as that post before. http://archives.postgresql.org/pgsql-hackers/2009-06/threads.php#01364 2) use appropriate macro and datatypes for Windows API. enables more than 32bits shared memory. 3) Build scripts for MSVC, this came from http://archives.postgresql.org/pgsql-hackers/2008-07/msg00440.php add new parameters to config.pl. You need define platform to x64 for 64bit programs. You should add your patch to the currently open commitfest here: https://commitfest.postgresql.org/action/commitfest_view/open And perhaps also review the patch submission guidelines here: http://wiki.postgresql.org/wiki/Submitting_a_Patch Thanks, ...Robert Thanks, I add this patch to the open commitfest. However, archive.postgresql.org has deleted the attachment. (Why? Email sent to the individual, the attachment is included.) Is it too large ? Should I resend them separately or compressing ? wrong mail format ? Should I try another mail software ? -- Tsutomu Yamada SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Windows x64
Tsutomu Yamada wrote: Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 1, 2009 at 6:25 AM, Tsutomu Yamada tsut...@sraoss.co.jp wrote: Hello. The following patches support Windows x64. 1) use intptr_t for Datum and pointer macros. (to support Windows LLP64) almost the same as that post before. http://archives.postgresql.org/pgsql-hackers/2009-06/threads.php#01364 2) use appropriate macro and datatypes for Windows API. enables more than 32bits shared memory. 3) Build scripts for MSVC, this came from http://archives.postgresql.org/pgsql-hackers/2008-07/msg00440.php add new parameters to config.pl. You need define platform to x64 for 64bit programs. You should add your patch to the currently open commitfest here: https://commitfest.postgresql.org/action/commitfest_view/open And perhaps also review the patch submission guidelines here: http://wiki.postgresql.org/wiki/Submitting_a_Patch Thanks, ...Robert Thanks, I add this patch to the open commitfest. However, archive.postgresql.org has deleted the attachment. (Why? Email sent to the individual, the attachment is included.) Is it too large ? Should I resend them separately or compressing ? wrong mail format ? Should I try another mail software ? hmm this looks like a bug in the archive interface - might be related to the fact that it is not looking for attachments after the signature delimiter or such. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cannot compile CVS HEAD
Pavel Stehule wrote: I have a problem with compilation: make[4]: Entering directory `/home/pavel/src/pgsql/src/backend/utils/adt' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I../../../../src/include -D_GNU_SOURCE -c -o int.o int.c int.c: In function ‘int4xor’: int.c:1209: error: ‘arg2’ undeclared (first use in this function) int.c:1209: error: (Each undeclared identifier is reported only once int.c:1209: error: for each function it appears in.) int.c:1207: warning: unused variable ‘arg20’ make[4]: *** [int.o] Error 1 That's bizarre. Works fine for me. Looks like the local variable is called arg20 instead of arg2 in your workspace for some reason. You sure you didn't accidentally fumble that? -- 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] cannot compile CVS HEAD
2009/12/2 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Pavel Stehule wrote: I have a problem with compilation: make[4]: Entering directory `/home/pavel/src/pgsql/src/backend/utils/adt' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I../../../../src/include -D_GNU_SOURCE -c -o int.o int.c int.c: In function ‘int4xor’: int.c:1209: error: ‘arg2’ undeclared (first use in this function) int.c:1209: error: (Each undeclared identifier is reported only once int.c:1209: error: for each function it appears in.) int.c:1207: warning: unused variable ‘arg20’ make[4]: *** [int.o] Error 1 That's bizarre. Works fine for me. Looks like the local variable is called arg20 instead of arg2 in your workspace for some reason. You sure you didn't accidentally fumble that? it is some strange - maybe some hw problem :( Pavel -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
Heikki Linnakangas wrote: Simon Riggs wrote: @@ -654,10 +656,13 @@ LockAcquire(const LOCKTAG *locktag, elog(PANIC, lock table corrupted); } LWLockRelease(partitionLock); -ereport(ERROR, -(errcode(ERRCODE_OUT_OF_MEMORY), - errmsg(out of shared memory), - errhint(You might need to increase max_locks_per_transaction.))); +if (reportLockTableError) +ereport(ERROR, +(errcode(ERRCODE_OUT_OF_MEMORY), + errmsg(out of shared memory), + errhint(You might need to increase max_locks_per_transaction.))); +else +return LOCKACQUIRE_NOT_AVAIL; } locallock-proclock = proclock; That seems dangerous when dontWait==false. Ah, I see now that you're only setting reportLockTableError just before you call LockAcquire, and reset it afterwards. It's safe then, but it should rather be another argument to the function, as how the global variable is really being used. The patch doesn't actually fix the issue it was supposed to fix. If a read-only transaction holds a lot of locks, consuming so much lock space that there's none left for the startup process to hold the lock it wants, it will abort and bring down postmaster. The patch attempts to kill any conflicting lockers, but those are handled fine already (if there's any conflicting locks, LockAcquire will return LOCKACQUIRE_NOT_AVAIL anyway). The problem is with non-conflicting locks using up the lock space. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
On Wed, 2009-12-02 at 12:49 +0200, Heikki Linnakangas wrote: If a read-only transaction holds a lot of locks, consuming so much lock space that there's none left for the startup process to hold the lock it wants, it will abort and bring down postmaster. The patch attempts to kill any conflicting lockers, but those are handled fine already (if there's any conflicting locks, LockAcquire will return LOCKACQUIRE_NOT_AVAIL anyway). The problem is with non-conflicting locks using up the lock space. Oh dear, another nuke 'em all from orbit scenario. Will do. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cost of sort/order by not estimated by the query planner
hummm Adding pgsql-perf :) On Mon, Nov 30, 2009 at 5:54 PM, Laurent Laborde kerdez...@gmail.com wrote: Friendly greetings ! I use postgresql 8.3.6. here is a few info about the table i'm querying : - - select count(*) from _article : 17301610 - select count(*) from _article WHERE (_article.bitfield getbit(0)) : 6729 Here are both request with problems : -- QUERY 1 : Very fast ! - explain SELECT * FROM _article WHERE (_article.bitfield getbit(0)) ORDER BY _article.id ASC LIMIT 500; QUERY PLAN - Limit (cost=66114.13..66115.38 rows=500 width=1114) - Sort (cost=66114.13..66157.37 rows=17296 width=1114) Sort Key: id - Bitmap Heap Scan on _article (cost=138.32..65252.29 rows=17296 width=1114) Recheck Cond: (bitfield B'1'::bit varying) - Bitmap Index Scan on idx_article_bitfield (cost=0.00..134.00 rows=17296 width=0) Index Cond: (bitfield B'1'::bit varying) QUERY 2 : Endless ... (more than 30mn... i stopped the query) - explain SELECT * FROM _article WHERE (_article.bitfield getbit(0)) ORDER BY _article.id ASC LIMIT 5; QUERY PLAN - Limit (cost=0.00..2042.87 rows=5 width=1114) - Index Scan using _article_pkey on _article (cost=0.00..7066684.46 rows=17296 width=1114) Filter: (bitfield B'1'::bit varying) (3 rows) With LIMIT 5 and LIMIT 500, the query plan are differents. Postgresql estimate that it can do a a simple index scan to find only 5 row. With more than LIMIT ~400 it estimate that it's faster to do a more complex plan. and it make sense ! The problem is in the order by, of course. If i remove the order by the LIMIT 5 is faster (0.044 ms) and do an index scan. At limit 500 (without order) it still use an index scan and it is slightly slower. At limit 5000 (without order) it switch to a Bitmap Index Scan + Bitmap Heap Scan and it's slower but acceptable (5.275 ms) Why, with the QUERY 2, postgresql doesn't estimate the cost of the Sort/ORDER BY ? Of course, by ignoring the order, both query plan are right and the choice for thoses differents plans totally make sense. But... if the planner would be kind enough to considerate the cost of the order by, it would certainly choose the Bitmap Index + Bitmap Heap scan for the limit 5. And not an index_scan pkey ! I have set the statistics to 1000 for _article.bitfield, just in case (and ran a vacuum analyze), it doesn't change anything. Is that a bug ? any Idea ? Thank you :) -- Laurent ker2x Laborde Sysadmin DBA at http://www.over-blog.com/ -- Laurent ker2x Laborde Sysadmin DBA at http://www.over-blog.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
Simon Riggs wrote: On Wed, 2009-12-02 at 12:49 +0200, Heikki Linnakangas wrote: If a read-only transaction holds a lot of locks, consuming so much lock space that there's none left for the startup process to hold the lock it wants, it will abort and bring down postmaster. The patch attempts to kill any conflicting lockers, but those are handled fine already (if there's any conflicting locks, LockAcquire will return LOCKACQUIRE_NOT_AVAIL anyway). The problem is with non-conflicting locks using up the lock space. Oh dear, another nuke 'em all from orbit scenario. Will do. Yeah. This case is much like the OOM killer on Linux. Not really nuke 'em all but nuke someone, don't care who.. -- 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] Page-level version upgrade
Hi, As we're talking about crazy ideas... Bruce Momjian br...@momjian.us writes: Well, yea, the idea would be that the 8.5 server would either convert the page to the new format on read (assuming there is enough free space, perhaps requiring a pre-upgrade script), or have the server write the page in the old 8.4 format and not do CRC checks on the page. My guess is the former. We already have had demand for read only tables (some on-disk format optimisation would then be possible). What about having page level read-only restriction, thus allowing the newer server version to operate in read-only mode on the older server version pages, and convert on write by allocating whole new page(s)? Then we go even crazier, with a special recovery mode on the new version able to read older version WAL format, producing older version pages. That sounds like code maintenance hell, but would allow for a $new WAL standby to restore from a $old wal steam, and be read only. Then you sitchover to the slave and it goes out of recovery and creates new pages on writes. How about going this crazy? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python array support
On Fri, Nov 20, 2009 at 12:00:24AM +0200, Peter Eisentraut wrote:
On fre, 2009-11-13 at 18:46 +0300, Teodor Sigaev wrote:
CREATE OR REPLACE FUNCTION incr(stuff int[]) RETURNS int[] AS $$
for x in stuff:
yield x+1
$$
LANGUAGE 'plpythonu';
# select incr(ARRAY[1,2,3]);
ERROR: invalid memory alloc request size 18446744073709551608
CONTEXT: while creating return value
PL/Python function incr
Fixed with additional error check and regression test. (The problem
could be more simply demonstrated by returning any non-sequence from the
function.) Thanks for catching it.
My last email claimed that the regression test needed some additional changes
to its expected output, and further claimed that it had the regression test's
diff attached. As was helpfully pointed out off-list, it actually wasn't
attached. Trying again..
-- Josh
*** /home/josh/devel/pgsrc/pg85/src/pl/plpython/expected/plpython_types.out
2009-12-01 20:39:52.0 -0700
--- /home/josh/devel/pgsrc/pg85/src/pl/plpython/results/plpython_types.out
2009-12-01 20:40:04.0 -0700
***
*** 580,582
--- 580,589
{abc,def}
(1 row)
+ CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$
+ return 5
+ $$ LANGUAGE plpythonu;
+ SELECT * FROM test_type_conversion_array_error();
+ ERROR: PL/Python: return value of function with array return type is not a
Python sequence
+ CONTEXT: while creating return value
+ PL/Python function test_type_conversion_array_error
==
signature.asc
Description: Digital signature
Re: [HACKERS] operator exclusion constraints
On Wed, Dec 2, 2009 at 12:18 AM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2009-12-01 at 23:19 -0500, Robert Haas wrote: For parity with unique constraints, I think that the message: operator exclusion constraint violation detected: %s should be changed to: conflicting key value violates operator exclusion constraint %s Done, and updated tests. In ATAddOperatorExclusionConstraint, streatagy is misspelled. Fixed. Other than that, it looks good to me. Great, thanks for the detailed review! Marked as Ready for Committer. ...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] Page-level version upgrade
On Wed, Dec 2, 2009 at 11:26 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: We already have had demand for read only tables (some on-disk format optimisation would then be possible). What about having page level read-only restriction, thus allowing the newer server version to operate in read-only mode on the older server version pages, and convert on write by allocating whole new page(s)? I'm a bit confused. Read-only tables are tables that the user has said they don't intend to modify. We can throw an error if they try. What you're proposing are pages that the system treats as read-only but what do you propose to do if the user actually does try to update or delete (or lock) a record in those pages? If we want to avoid converting them to new pages we need to be able to at least store an xmax and set the ctid on those tuples. And probably we would need to do other things like set hint bits or set fields in the page header. -- 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] Page-level version upgrade (was: Block-level CRC checks)
David Fetter wrote: Right. There were two basic approaches to handling a patch that would expand when upgraded to the new version --- either allow the system to write the old format, or have a pre-upgrade script that moved tuples so there was guaranteed enough free space in every page for the new format. I think we agreed that the later was better than the former, and it was easy because we don't have any need for that at this time. Plus the script would not rewrite every page, just certain pages that required it. Please forgive me for barging in here, but that approach simply is untenable if it requires that the database be down while those pages are being found, marked, moved around, etc. The data volumes that really concern people who need an in-place upgrade are such that even dd if=$PGDATA of=/dev/null bs=8192 # (or whatever the optimal block size would be) would require *much* more time than such people would accept as a down time window, and while that's a lower bound, it's not a reasonable lower bound on the time. Well, you can say it is unacceptable, but if there are no other options then that is all we can offer. My main point is that we should consider writing old format pages only when we have no choice (page size might expand), and even then, we might decide to have a pre-migration script because the code impact of writing the old format would be too great. This is all hypothetical until we have a real use-case. If this re-jiggering could kick off in the background at start and work on a running PostgreSQL, the whole objection goes away. A problem that arises for any in-place upgrade system we do is that if someone's at 99% storage capacity, we can pretty well guarantee some kind of catastrophic failure. Could we create some way to get an estimate of space needed, given that the system needs to stay up while that's happening? Yea, the database would expand and hopefully have full transaction semantics. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enable-thread-safety defaults?
Bruce Momjian wrote: It would seem like we ought to try the one-liner patch Magnus proposed (ie flip the default) and see what the effects are, before we go with the much larger patch Bruce wrote. OK, done --- let the breakage begin. (I will be monitoring the build farm and will work with Andrew Dunstan on any issues.) OK, only Unixware and OpenBSD went red on the buildfarm with threading enabled, so I have applied the more complete patch to enable thread safety on clients by default, e.g. doc changes. Andrew Dunstan is going to contact those build farm members so they use --disable-thread-safety. He has also agreed to update the buildfarm to detect this new option behavior. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade
Greg Stark gsst...@mit.edu writes: On Wed, Dec 2, 2009 at 11:26 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: We already have had demand for read only tables (some on-disk format optimisation would then be possible). What about having page level read-only restriction, thus allowing the newer server version to operate in read-only mode on the older server version pages, and convert on write by allocating whole new page(s)? I'm a bit confused. Read-only tables are tables that the user has said they don't intend to modify. We can throw an error if they try. What you're proposing are pages that the system treats as read-only but what do you propose to do if the user actually does try to update or delete (or lock) a record in those pages? Well it's still a pretty rough idea, so I'll need help from this forum to get to something concrete enough for someone to be able to implement it... and there you go: If we want to avoid converting them to new pages we need to be able to at least store an xmax and set the ctid on those tuples. And probably we would need to do other things like set hint bits or set fields in the page header. My idea was more that any non read-only access to the page forces a rewrite in the new format, and a deprecation of the ancient page. Maybe like what vacuum would be doing on it as soon as it realises the page contains no visible tuples anymore, but done by the backend at the time of the modification. That makes the first modifications of the page quite costly but allow to somewhat choose when that happens. And still have read only access, so you could test parts of your application on a hot standby running next version. Maybe there's just too much craziness in there now. -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] YAML Was: CommitFest status/management
Josh Berkus wrote: On 11/30/09 8:17 PM, Andrew Dunstan wrote: Do we have consensus yet that we want YAML? It seemed, well, yet another format without all that much advantage over what's there. Well, what's the code count? What dependencies, if any, does it add? The patch itself is quite small. There are no extra external dependencies. YAML and JSON are pretty much interchangeable for our purposes. According to Wikipedia, Both functionally and syntactically, JSON is effectively a subset of YAML. See http://en.wikipedia.org/wiki/JSON#YAML So the YAML parsers should be able to handle the JSON output. The only thing we'd be buying with this patch is making a bit happier some people who prefer reading the YAML syntax. For machine readability we'd be gaining precisely nothing. I guess the question is this: when are we going to say No more output formats. We have enough.? One consideration is this: the more formats we support the dumber the output will be. Already the XML output is arguably dumber than it should be, because XML elements are two-dimensional (they can have property lists (attributes) and child elements) but JSON/YAML nodes are one-dimensional, so we have made some things that one might normally expect to be attributes in XML into child elements. While adding YAML won't impose any additional burden of that kind, because its semantics are so close to those of JSON, other output formats well might. 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] compile error with -DOPTIMIZER_DEBUG
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Jan Urbański wrote: ISTM that there's a superfluous curly brace in print_path (which only gets compiled with -DOPTIMIZER_DEBUG. Thanks, committed. You know, the last couple of times I've touched that code, I've been wondering why we bother to maintain it. Personally I always use pprint() when I'm interested in a printout of a plan tree. Is anyone actually using the printout code in allpaths.c? Maybe Tom Raney's Visual Planner thing? -- 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
[HACKERS] Initial refactoring of plperl.c - rebased [PATCH]
I've attached an update of my previous refactoring of plperl.c.
It's been rebased over the current (git) HEAD and has a few
very minor additions.
Background:
I've started work on the enhancements to plperl I outlined on pg-general
(in the Wishlist of PL/Perl Enhancements for 8.5 thread).
I have a working implementation of those changes, plus some performance
enhancements, that I'm now re-working into a clean set of tested and
polished patches.
This patch is a first step that doesn't add any extra functionality.
It refactors the internals to make adding the extra functionality
easier (and more clearly visible).
Changes in this patch:
- Changed MULTIPLICITY check from runtime to compiletime.
No loads the large Config module.
- Changed plperl_init_interp() to return new interp
and not alter the global interp_state
- Moved plperl_safe_init() call into check_interp().
- Removed plperl_safe_init_done state variable
as interp_state now covers that role.
- Changed plperl_create_sub() to take a plperl_proc_desc argument.
- Simplified return value handling in plperl_create_sub.
- Added a test for the effect of the utf8fix function.
- Changed perl.com link in the docs to perl.org and tweaked
wording to clarify that require, not use, is what's blocked.
- Moved perl code in large multi-line C string literal macros
out to plc_*.pl files.
- Added a test2macro.pl utility to convert the plc_*.pl files to
macros in a perlchunks.h file which is #included
Additions since previous verion:
- Replaced calls to SvPV(val, PL_na) with SvPV_nolen(val)
- Simplifed plperl_safe_init() slightly
- Removed trailing whitespace from new plc_*.pl files.
I'd appreciate any feedback on the patch.
Tim.
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 7eebfba..37114bd 100644
*** a/doc/src/sgml/plperl.sgml
--- b/doc/src/sgml/plperl.sgml
***
*** 14,20
para
PL/Perl is a loadable procedural language that enables you to write
productnamePostgreSQL/productname functions in the
!ulink url=http://www.perl.com;Perl programming language/ulink.
/para
para
--- 14,20
para
PL/Perl is a loadable procedural language that enables you to write
productnamePostgreSQL/productname functions in the
!ulink url=http://www.perl.org;Perl programming language/ulink.
/para
para
*** SELECT * FROM perl_set();
*** 313,319
use strict;
/programlisting
in the function body. But this only works in applicationPL/PerlU/
!functions, since literaluse/ is not a trusted operation. In
applicationPL/Perl/ functions you can instead do:
programlisting
BEGIN { strict-import(); }
--- 313,320
use strict;
/programlisting
in the function body. But this only works in applicationPL/PerlU/
!functions, since the literaluse/ triggers a literalrequire/
!which is not a trusted operation. In
applicationPL/Perl/ functions you can instead do:
programlisting
BEGIN { strict-import(); }
diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile
index a3c3495..8989b14 100644
*** a/src/pl/plperl/GNUmakefile
--- b/src/pl/plperl/GNUmakefile
*** PSQLDIR = $(bindir)
*** 45,50
--- 45,55
include $(top_srcdir)/src/Makefile.shlib
+ plperl.o: perlchunks.h
+
+ perlchunks.h: plc_*.pl
+ $(PERL) text2macro.pl --strip='^(\#.*|\s*)$$' plc_*.pl perlchunks.htmp
+ mv perlchunks.htmp perlchunks.h
all: all-lib
*** submake:
*** 65,71
$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
clean distclean maintainer-clean: clean-lib
! rm -f SPI.c $(OBJS)
rm -rf results
rm -f regression.diffs regression.out
--- 70,76
$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
clean distclean maintainer-clean: clean-lib
! rm -f SPI.c $(OBJS) perlchunks.htmp perlchunks.h
rm -rf results
rm -f regression.diffs regression.out
diff --git a/src/pl/plperl/expected/plperl.out b/src/pl/plperl/expected/plperl.out
index b942739..c1cf7ae 100644
*** a/src/pl/plperl/expected/plperl.out
--- b/src/pl/plperl/expected/plperl.out
*** CONTEXT: PL/Perl anonymous code block
*** 566,568
--- 566,575
DO $$ use Config; $$ LANGUAGE plperl;
ERROR: 'require' trapped by operation mask at line 1.
CONTEXT: PL/Perl anonymous code block
+ --
+ -- Test compilation of unicode regex
+ --
+ CREATE OR REPLACE FUNCTION perl_unicode_regex(text) RETURNS INTEGER AS $$
+ # see http://rt.perl.org/rt3/Ticket/Display.html?id=47576
+ return ($_[0] =~ /\x{263A}|happy/i) ? 1 : 0; # unicode smiley
+ $$ LANGUAGE plperl;
diff --git a/src/pl/plperl/plc_perlboot.pl b/src/pl/plperl/plc_perlboot.pl
index ...d2d5518 .
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 0
--- 1,50
+ SPI::bootstrap();
+ use vars qw(%_SHARED);
+
+ sub ::plperl_warn {
+ (my $msg = shift) =~ s/\(eval \d+\) //g;
+
Re: [HACKERS] Application name patch - v4
Magnus Hagander mag...@hagander.net writes: 2009/12/2 Tom Lane t...@sss.pgh.pa.us: BTW, it strikes me that it would only be a matter of a couple of lines to persuade older servers to ignore application_name in the startup packet, instead of throwing a tantrum. Obviously we must make libpq work against unpatched older servers, but if we can save a connection cycle (and some bleating in the postmaster log) when talking to an 8.5 application, it might be worth doing: Given that this can probably be considered an *extremely* safe patch :-), I say go for it. It'll certainly make for less error reports around something that's not an error. Yeah. I wouldn't even propose this, except that given the new code an unpatched older server will log FATAL: unrecognized configuration parameter application_name anytime it gets a connection from newer libpq. I'm sure we'll get some complaints/bugreports about it if we allow that to be the norm. However, if we backpatch now, there will be relatively few situations in the field where anyone tries to use 8.5 libpq against an unpatched older server. 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] Page-level version upgrade (was: Block-level CRC checks)
On Tue, Dec 1, 2009 at 11:45 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: The key issue, as I think Heikki identified at the time, is to figure out how you're eventually going to get rid of the old pages. ?He proposed running a pre-upgrade utility on each page to reserve the right amount of free space. http://archives.postgresql.org/pgsql-hackers/2008-11/msg00208.php Right. ?There were two basic approaches to handling a patch that would expand when upgraded to the new version --- either allow the system to write the old format, or have a pre-upgrade script that moved tuples so there was guaranteed enough free space in every page for the new format. I think we agreed that the later was better than the former, and it was easy because we don't have any need for that at this time. ?Plus the script would not rewrite every page, just certain pages that required it. While I'm always willing to be proven wrong, I think it's a complete dead-end to believe that it's going to be easier to reserve space for page expansion using the upgrade-from version rather than the upgrade-to version. I am firmly of the belief that the NEW pg version must be able to operate on an unmodified heap migrated from the OLD pg version. After this set of patches was rejected, Zdenek actually Does it need to write the old version, and if it does, it has to carry around the old format structures all over the backend? That was the unclear part. I think it needs partial write support for the old version. If the page is not expanding, then you can probably just replace pages in place. But if the page is expanding, then you need to be able to move individual tuples[1]. Since you want to be up and running while that's happening, I think you probably need to be able to update xmax and probably set hit bints. But you don't need to be able to add tuples to the old page format, and I don't think you need complete vacuum support, since you don't plan to reuse the dead space - you'll just recycle the whole page once the tuples are all dead. As for carrying it around the whole backend, I'm not sure how much of the backend really needs to know. It would only be anything that looks at pages, rather than, say, tuples, but I don't really know how much code that touches. I suppose that's one of the things we need to figure out. [1] Unless, of course, you use a pre-upgrade utility. But this is about how to make it work WITHOUT a pre-upgrade utility. proposed an alternate patch that would have allowed space reservation, and it was rejected precisely because there was no clear certainty that it would solve any hypothetical future problem. True. It was solving a problem we didn't have, yet. Well, that's sort of a circular argument. If you're going to reserve space with a pre-upgrade utility, you're going to need to put the pre-upgrade utility into the version you want to upgrade FROM. If we wanted to be able to use a pre-upgrade utility to upgrade to 8.5, we would have had to put the utility into 8.4. The problem I'm referring to is that there is no guarantee that you would be able predict how much space to reserve. In a case like CRCs, it may be as simple as 4 bytes. But what if, say, we switch to a different compression algorithm for inline toast? Some pages will contract, others will expand, but there's no saying by how much - and therefore no fixed amount of reserved space is guaranteed to be adequate. It's true that we might never want to do that particular thing, but I don't think we can say categorically that we'll NEVER want to do anything that expands pages by an unpredictable amount. So it might be quite complex to figure out how much space to reserve on any given page. If we can find a way to make that the NEW PG version's problem, it's still complicated, but at least it's not complicated stuff that has to be backpatched. Another problem with a pre-upgrade utility is - how do you verify, when you fire up the new cluster, that the pre-upgrade utility has done its thing? If the new PG version requires 4 bytes of space reserved on each page, what happens when you get halfway through upgrading your 1TB database and find a page with only 2 bytes available? There aren't a lot of good options. The old PG version could try to mark the DB in some way to indicate whether it successfully completed, but what if there's a bug and something was missed? Then you have this scenario: 1. Run the pre-upgrade script. 2. pg_migrator. 3. Fire up new version. 4. Discover that pre-upgrade script forgot to reserve enough space on some page. 5. Report a bug. 6. Bug fixed, new version of pre-upgrade script is now available. 7. ??? If all the logic is in the new server, you may still be in hot water when you discover that it can't deal with a particular case. But hopefully the problem would be confined to that page, or that relation, and you could use the rest of your database. And
Re: [HACKERS] YAML Was: CommitFest status/management
Andrew Dunstan and...@dunslane.net writes: YAML and JSON are pretty much interchangeable for our purposes. According to Wikipedia, Both functionally and syntactically, JSON is effectively a subset of YAML. See http://en.wikipedia.org/wiki/JSON#YAML So the YAML parsers should be able to handle the JSON output. The only thing we'd be buying with this patch is making a bit happier some people who prefer reading the YAML syntax. For machine readability we'd be gaining precisely nothing. Hmm. So the argument for it is let's make a machine-readable format more human-readable? I'm not getting the point. People should look at the regular text output. One consideration is this: the more formats we support the dumber the output will be. Already the XML output is arguably dumber than it should be, because XML elements are two-dimensional (they can have property lists (attributes) and child elements) but JSON/YAML nodes are one-dimensional, so we have made some things that one might normally expect to be attributes in XML into child elements. While adding YAML won't impose any additional burden of that kind, because its semantics are so close to those of JSON, other output formats well might. I tend to look at it the other way around: having to support output formats that have significantly different data models is a Good Thing because it forces you to design sufficiently general code mechanisms. If YAML had yet another data model it might actually be a useful exercise to get the code to handle that. However, if it's not teaching us anything we didn't learn from JSON, there's no gain from that viewpoint either. 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] Page-level version upgrade (was: Block-level CRC checks)
On Wed, 2009-12-02 at 10:48 -0500, Robert Haas wrote: Well, that's sort of a circular argument. If you're going to reserve space with a pre-upgrade utility, you're going to need to put the pre-upgrade utility into the version you want to upgrade FROM. If we wanted to be able to use a pre-upgrade utility to upgrade to 8.5, we would have had to put the utility into 8.4. Don't see any need to reserve space at all. If this is really needed, we first run a script to prepare the 8.4 database for conversion to 8.5. The script would move things around if it finds a block that would have difficulty after upgrade. We may be able to do that simple, using fillfactor, or it may need to be more complex. Either way, its still easy to do this when required. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Adding support for SE-Linux security
[ Updated subject ] We have been discussing support for SE-Linux for over a year and now have a minimal patch submitted that maps SE-Linux permissions to existing Postgres permissions: patch: http://momjian.us/tmp/sepgsql-01-lite-8.5devel-r2451.patch email: http://archives.postgresql.org/message-id/4b13856f.1090...@ak.jp.nec.com That patch is the minimum required to support SE-Linux in some form. The majority of the patch is documentation, regression tests, small catalog additions, and SE-Linux-specific C files. It does add hooks into the existing access permission functions. There is no support for row-level permissions or mandatory access control (MAC). These were removed to minimize code impact and might be added later. Tom's email below highlights the lack of mainstream usage of SE-Linux features, though it is supported by most Linux distributions. Tom's opinion is adding support for a minimal set of SE-Linux security isn't worth the code impact. David Fetter felt SE-Linux was mostly a marketing/sales feature, rather than something of general usefulness. Others feel SE-Linux is valid for limited use cases. I understand SE-Linux to be like Kerberos --- Kerberos provides single-signon site authentication, while SE-Linux provides single-signon site security credentials. While Kerberos is not useful for everyone, SE-Linux similarly has limited adoption. Kerberos has proven to be a key technology for sites that need it, and SE-Linux might prove to be similar. If we decide not to support SE-Linux, it is unlikely we will be adding support for any other external security systems because SE-Linux has the widest adoption. I think the big question is whether we are ready to extend Postgres to support additional security infrastructures. --- Tom Lane wrote: KaiGai Kohei kai...@ak.jp.nec.com writes: Joshua D. Drake wrote: I just did a little research and it appears the other two big names in this world (Novel and Ubuntu) are using something called App Armor. As far as I can see, SUSE, Ubuntu and Debian provide SELinux option. But they are more conservative than RedHat/Fedora, because it is not enabled in the default installation. I don't think it is unpreferable decision. Users can choose the option by themself according to requirements in the system. Based on Red Hat's experience, it is a safe bet that not enabling SELinux by default guarantees the feature will remain useless to the average user. As was pointed out upthread (and I can confirm from personal experience), it's taken *years* for Red Hat to develop the security policy to a point where it's even marginally usable by anyone who isn't willing to put up with a great deal of annoyance because they have an extreme need. And that's despite having a well-defined, not too ambitious goal for what it is they are trying to secure: for the most part, RH's default policy doesn't try to lock down anything except network-accessible services. SUSE and the rest of them may have the feature, but they don't have it in a usable form, and won't ever have it without a much larger effort than they're making. Even if we were to accept the SEPostgres patches lock stock and barrel tomorrow, I don't foresee that it will ever get to the point of being useful except to an extremely small group of users who are driven by extreme need. Nobody else is going to have the motivation needed to develop custom security policies, and there simply isn't any chance of anyone developing any generally useful default policy. Red Hat's policy has been trying to cope with cases like which directories should Apache be allowed to read, *given that it's running a Red-Hat-standard configuration*? That's far more circumscribed than any useful database policy would be, because database applications aren't nearly that standardized. If SEPostgres were a small patch that wouldn't need much ongoing effort, I might think it's reasonable to adopt it for the benefit of only a small group of users. However, it's not small, it's not simple, and it will not be low-maintenance. I'm afraid the cost-benefit ratio from the project's perspective is just not reasonable. regards, tom lane -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [Pg-migrator-general] Composite types break pg_migrated tables
On Thu, Aug 6, 2009 at 9:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: The half-formed idea I had was a set of GUC variables: set next_pg_class_oid = 12345; set next_pg_type_oid = 12346; set next_toast_table_oid = ... set next_toast_index_oid = ... and finally it could do CREATE TABLE. CREATE TYPE would only need next_pg_type_oid (except for a composite type). Is this idea still on the table for 8.5? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [Pg-migrator-general] Composite types break pg_migrated tables
Merlin Moncure mmonc...@gmail.com writes: Is this idea still on the table for 8.5? I've forgotten what the problem was? 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: [Pg-migrator-general] Composite types break pg_migrated tables
Merlin Moncure wrote: On Thu, Aug 6, 2009 at 9:28 AM, Tom Lane t...@sss.pgh.pa.us wrote: The half-formed idea I had was a set of GUC variables: set next_pg_class_oid = 12345; set next_pg_type_oid = 12346; set next_toast_table_oid = ... set next_toast_index_oid = ... and finally it could do CREATE TABLE. ?CREATE TYPE would only need next_pg_type_oid (except for a composite type). Is this idea still on the table for 8.5? Well, pg_migrator still has these restrictions that will apply to migrations to 8.5: pg_migrator will not work if a user column is defined as: o a user-defined composite data type o a user-defined array data type o a user-defined enum data type You must drop any such columns and migrate them manually. Having 'next_pg_type_oid' would fix that. The other three settings are already handled by pg_migrator code. Having those three settings would allow me to remove some pg_migrator code once we removed support for migrations to 8.4. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [Pg-migrator-general] Composite types break pg_migrated tables
On Wed, Dec 2, 2009 at 11:28 AM, Bruce Momjian br...@momjian.us wrote: set next_pg_class_oid = 12345; set next_pg_type_oid = 12346; set next_toast_table_oid = ... set next_toast_index_oid = ... and finally it could do CREATE TABLE. ?CREATE TYPE would only need next_pg_type_oid (except for a composite type). Is this idea still on the table for 8.5? Well, pg_migrator still has these restrictions that will apply to migrations to 8.5: pg_migrator will not work if a user column is defined as: o a user-defined composite data type o a user-defined array data type o a user-defined enum data type You must drop any such columns and migrate them manually. Having 'next_pg_type_oid' would fix that. The other three settings are already handled by pg_migrator code. Having those three settings would allow me to remove some pg_migrator code once we removed support for migrations to 8.4. I also have a personal interest for non pg_migrator reasons. The basic problem is that there is no way to make oids consistent between databases which causes headaches for things like migration and direct transfer of data between databases in binary. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
On Wed, 2009-12-02 at 16:41 +, Simon Riggs wrote: On Tue, 2009-12-01 at 20:26 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: commit 02c3eadb766201db084b668daa271db4a900adc9 Author: Simon Riggs sri...@ebony.(none) Date: Sat Nov 28 06:23:33 2009 + Added wal_standby_info GUC to turn RM_STANDBY_ID messages on/off. Various comments added also. This patch makes it unsafe to start hot standby mode from a shutdown checkpoint, because we don't know if wal_standby_info was enabled in the master. Hmm, what happens if someone enables wal_standby_info in postgresql.conf while the server is shutdown. It would still be a valid starting point in that case. I'll just make a note, I think. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
On Wed, Dec 2, 2009 at 11:08 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-12-02 at 10:48 -0500, Robert Haas wrote: Well, that's sort of a circular argument. If you're going to reserve space with a pre-upgrade utility, you're going to need to put the pre-upgrade utility into the version you want to upgrade FROM. If we wanted to be able to use a pre-upgrade utility to upgrade to 8.5, we would have had to put the utility into 8.4. Don't see any need to reserve space at all. If this is really needed, we first run a script to prepare the 8.4 database for conversion to 8.5. The script would move things around if it finds a block that would have difficulty after upgrade. We may be able to do that simple, using fillfactor, or it may need to be more complex. Either way, its still easy to do this when required. I discussed the problems with this, as I see them, in the same email you just quoted. You don't have to agree with my analysis, of course. ...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: [CORE] [HACKERS] EOL for 7.4?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Mark wrote: Doesn't mean that packagers have to make new packages ... I personally think new packages shouldn't be made for anything older then *maybe* 3 releases (8.2, 8.3 and 8.4), but even that I think tends to be a bit excessive ... but doing source tar balls is easy enough ... Andrew wrote: But the issue for me is not what vendors support but how often we ask someone to upgrade if they want to stay on a community supported base. As I remarked before, other things being equal, I think five years is a reasonable interval, and given that many users don't upgrade right on a .0 release, I think a release lifetime of about six years is therefore about right as a target. All of this ignores a huge reason why we have an implicit obligation to support past releases for a long time: our horrible lack of an upgrade option. That's only now starting to get remedied somewhat with pg_migrator, Bucardo, and Slony, but the default way is still to do a dump-and-restore. Until we can make this process take minutes instead of days for large databases, people are going to end up stuck to what version they are on. Knowing they are going to have to do it all over again later is not going to be very confidence inspiring. Again, to emphasize: many people are using 7.4, or 8.0, or 8.1, not because they necessarily want to, but they can't easily afford the downtime to upgrade. Cutting them off arbitrarily early won't win us any friends. Once pg_migrator (or better, in-place upgrades) is working well, we can start setting EOL on versions based on number of years of some other criteria. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200912021218 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksWokQACgkQvJuQZxSWSsg5kACfdd9nZtHSG/KcOAIOGxVZ81/o TUEAniaG4vWo4CY4v+3DlByJ4AZ6JXKP =MyN9 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [CORE] [HACKERS] EOL for 7.4?
On Wed, Dec 2, 2009 at 12:22 PM, Greg Sabino Mullane g...@turnstep.com wrote: Mark wrote: Doesn't mean that packagers have to make new packages ... I personally think new packages shouldn't be made for anything older then *maybe* 3 releases (8.2, 8.3 and 8.4), but even that I think tends to be a bit excessive ... but doing source tar balls is easy enough ... Andrew wrote: But the issue for me is not what vendors support but how often we ask someone to upgrade if they want to stay on a community supported base. As I remarked before, other things being equal, I think five years is a reasonable interval, and given that many users don't upgrade right on a .0 release, I think a release lifetime of about six years is therefore about right as a target. All of this ignores a huge reason why we have an implicit obligation to support past releases for a long time: our horrible lack of an upgrade option. That's only now starting to get remedied somewhat with pg_migrator, Bucardo, and Slony, but the default way is still to do a dump-and-restore. Until we can make this process take minutes instead of days for large databases, people are going to end up stuck to what version they are on. Knowing they are going to have to do it all over again later is not going to be very confidence inspiring. Again, to emphasize: many people are using 7.4, or 8.0, or 8.1, not because they necessarily want to, but they can't easily afford the downtime to upgrade. Cutting them off arbitrarily early won't win us any friends. Once pg_migrator (or better, in-place upgrades) is working well, we can start setting EOL on versions based on number of years of some other criteria. At the moment it doesn't seem likely that pg_migrator is *ever* going to support upgrading from 7.4 or 8.0 or 8.1 to any later version. I'm not saying that's good, but nobody's expressed much interest in making in-place upgrade work even from an 8.2 base, let alone any older version. For that matter, there's been no concerted effort to resolve the limitations of the 8.3 - 8.4 upgrade. It isn't technically impossible for the 8.3 - 8.5 path to be smoother than the current 8.3 - 8.4 path, but nobody seems excited about working on it. ...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] Page-level version upgrade (was: Block-level CRC checks)
Robert Haas wrote: The problem I'm referring to is that there is no guarantee that you would be able predict how much space to reserve. In a case like CRCs, it may be as simple as 4 bytes. But what if, say, we switch to a different compression algorithm for inline toast? Upthread, you made a perfectly sensible suggestion: use the CRC addition as a test case to confirm you can build something useful that allowed slightly more complicated in-place upgrades than are supported now. This requires some new code to do tuple shuffling, communicate reserved space, etc. All things that seem quite sensible to have available, useful steps toward a more comprehensive solution, and an achievable goal you wouldn't even have to argue about. Now, you're wandering us back down the path where we have to solve a migrate TOAST changes level problem in order to make progress. Starting with presuming you have to solve the hardest possible issue around is the documented path to failure here. We've seen multiple such solutions before, and they all had trade-offs deemed unacceptable: either a performance loss for everyone (not just people upgrading), or unbearable code complexity. There's every reason to believe your reinvention of the same techniques will suffer the same fate. When someone has such a change to be made, maybe you could bring this back up again and gain some traction. One of the big lessons I took from the 8.4 development's lack of progress on this class of problem: no work to make upgrades easier will get accepted unless there is such an upgrade on the table that requires it. You need a test case to make sure the upgrade approach a) works as expected, and b) is code you must commit now or in-place upgrade is lost. Anything else will be deferred; I don't think there's any interest in solving a speculative future problem left at this point, given that it will be code we can't even prove will work. Another problem with a pre-upgrade utility is - how do you verify, when you fire up the new cluster, that the pre-upgrade utility has done its thing? Some additional catalog support was suggested to mark what the pre-upgrade utility had processed. I'm sure I could find the messages about again if I had to. If all the logic is in the new server, you may still be in hot water when you discover that it can't deal with a particular case. If you can't design a pre-upgrade script without showstopper bugs, what makes you think the much more complicated code in the new server (which will be carrying around an ugly mess of old and new engine parts) will work as advertised? I think we'll be lucky to get the simplest possible scheme implemented, and that any of these more complicated ones will die under their own weight of their complexity. Also, your logic seems to presume that no backports are possible to the old server. A bug-fix to the pre-upgrade script is a completely reasonable and expected candidate for backporting, because it will be such a targeted piece of code that adjusting it shouldn't impact anything else. The same will not be even remotely true if there's a bug fix needed in a more complicated system that lives in a regularly traversed code path. Having such a tightly targeted chunk of code makes pre-upgrade *more* likely to get bug-fix backports, because you won't be touching code executed by regular users at all. The potential code impact of backporting fixes to the more complicated approaches here is another major obstacle to adopting one of them. That's an issue that we didn't even get to the last time, because showstopper issues popped up first. That problem was looming had work continued down that path though. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
Simon Riggs wrote: Hmm, what happens if someone enables wal_standby_info in postgresql.conf while the server is shutdown. It would still be a valid starting point in that case. Yeah, true. I'll just make a note, I think. Yeah, a manual (or automatic, if you just wait) checkpoint will produce a new checkpoint record showing that it's safe to start standby again. -- 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] YAML Was: CommitFest status/management
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: YAML... Hmm. So the argument for it is let's make a machine-readable format more human-readable? I'm not getting the point. People should look at the regular text output. IMHO YAML beats the regular text format for human-readability - at least for people with narrow terminal windows, and for novices. Greg posted examples comparing regular-text vs yaml vs json here: http://archives.postgresql.org/pgsql-hackers/2009-08/msg02090.php I think it's more human-readable for novices since it explicitly spells out what values refer to startup values vs totals. I think it's more human-readable to me because the current text format frequently wraps for me on even a modestly complex query, and I find scrolling down easier than scrolling both ways. None of the other machine-intended formats seem to suit that purpose well because they're dominated by a lot of markup. That said, though, it's not that big a deal. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
On Wed, Dec 2, 2009 at 1:08 PM, Greg Smith g...@2ndquadrant.com wrote: Robert Haas wrote: The problem I'm referring to is that there is no guarantee that you would be able predict how much space to reserve. In a case like CRCs, it may be as simple as 4 bytes. But what if, say, we switch to a different compression algorithm for inline toast? Upthread, you made a perfectly sensible suggestion: use the CRC addition as a test case to confirm you can build something useful that allowed slightly more complicated in-place upgrades than are supported now. This requires some new code to do tuple shuffling, communicate reserved space, etc. All things that seem quite sensible to have available, useful steps toward a more comprehensive solution, and an achievable goal you wouldn't even have to argue about. Now, you're wandering us back down the path where we have to solve a migrate TOAST changes level problem in order to make progress. Starting with presuming you have to solve the hardest possible issue around is the documented path to failure here. We've seen multiple such solutions before, and they all had trade-offs deemed unacceptable: either a performance loss for everyone (not just people upgrading), or unbearable code complexity. There's every reason to believe your reinvention of the same techniques will suffer the same fate. Just to set the record straight, I don't intend to work on this problem at all (unless paid, of course). And I'm perfectly happy to go with whatever workable solution someone else comes up with. I'm just offering opinions on what I see as the advantages and disadvantages of different approaches, and anyone is working on this is more than free to ignore them. Some additional catalog support was suggested to mark what the pre-upgrade utility had processed. I'm sure I could find the messages about again if I had to. And that's a perfectly sensible solution, except that adding a catalog column to 8.4 at this point would force initdb, so that's a non-starter. I suppose we could shoehorn it into the reloptions. Also, your logic seems to presume that no backports are possible to the old server. The problem on the table at the moment is that the proposed CRC feature will expand every page by a uniform amount - so in this case a fixed-space-per-page reservation utility would be completely adequate. Does anyone think this is a realistic thing to backport to 8.4? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On tis, 2009-12-01 at 19:41 +, Greg Stark wrote: Also, it would require reading back each page as it's written to disk, which is OK for a bunch of single-row writes, but for bulk data loads a significant problem. Not sure what that really means for Postgres. It would just mean reading back the same page of memory from the filesystem cache that we just read. Surely the file system ought to be the place where to solve this. After all, we don't put link-level corruption detection into the libpq protocol either. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] YAML Was: CommitFest status/management
All, If some people want it, and there's no significant maintenance burden associated with YAML output, then why not? Mind you, if it was practical, I'd suggest that YAML ... and all additional Explain formats ... should be a contrib module. Anything other than XML and JSON will be fairly marginal. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On tis, 2009-12-01 at 17:47 -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I also like the idea that we don't need to CRC check the line pointers because any corruption there is going to appear immediately. However, the bad news is that we wouldn't find the corruption until we try to access bad data and might crash. That sounds exactly like the corruption detection system we have now. If you think that behavior is acceptable, we can skip this whole discussion. I think one of the motivations for this CRC business was to detect corruption in the user data. As you say, we already handle corruption in the metadata. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding support for SE-Linux security
Bruce, If we decide not to support SE-Linux, it is unlikely we will be adding support for any other external security systems because SE-Linux has the widest adoption. I think the big question is whether we are ready to extend Postgres to support additional security infrastructures. PostgreSQL is the most security-conscious of the OSS databases, and is widely used by certain groups (security software, military, credit card processing) precisely because of this reputation. These folks, while unlikely to speak up on -hackers, are interested in new/further security features; when I was at the Pentagon 2 years ago several people there from HS were quite interested in SE-Postgres specifically. Further, I've been mentioning SE-Postgres in my DB security talk for the last 18 months and I *always* get a question about it. So while there might not be vocal proponents for innovative/hard-core security frameworks on this list currently, I think it will gain us some new users. Maybe more than we expect. When GIS was introduced to this list ten years ago it was criticized as a marginal feature and huge and intrusive. But today it's probably 40% of our user base, and growing far more rapidly than anything else with Postgres. Maybe SE will be more like Rules than like GIS in the long run, but there's no way for us to know that today. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Aggregate ORDER BY patch
2009/11/30 Andrew Gierth and...@tao11.riddles.org.uk: Updated version of the aggregate order by patch. Includes docs + regression tests all in the same patch. Changes: - removed SortGroupClause.implicit as per review comments, replacing it with separate lists for Aggref.aggorder and Aggref.aggdistinct. - Refactored in order to move the bulk of the new parse code out of ParseFuncOrColumn which was already quite big enough, into parse_agg.c - fixed a bug with incorrect deparse in ruleutils (and added a bunch of regression tests for deparsing and view usage) - added some comments It seems good to me. Everything that was pointed in the previous review was fixed, as well as sufficient comments are added. It applies very cleanly against HEAD and compiles without error/warning. I found only trivial favors such like that a blank line is added around line 595 in the patch, and proj in peraggstate sounds a little weird to me because of surrounding evaldesc and evalslot (evalproj seems better to me). Also catversion update doesn't mean anything for this feature. But these are not what prevent it from review by a committer. So, although I'm going to look more on this patch, I mark this item as Ready for Committer for now. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
Robert Haas wrote: Some additional catalog support was suggested to mark what the pre-upgrade utility had processed. I'm sure I could find the messages about again if I had to. And that's a perfectly sensible solution, except that adding a catalog column to 8.4 at this point would force initdb, so that's a non-starter. I suppose we could shoehorn it into the reloptions. There's no reason the associated catalog support had to ship with the old version. You can always modify the catalog after initdb, but before running the pre-upgrade utility. pg_migrator might make that change for you. The problem on the table at the moment is that the proposed CRC feature will expand every page by a uniform amount - so in this case a fixed-space-per-page reservation utility would be completely adequate. Does anyone think this is a realistic thing to backport to 8.4? I believe the main problem here is making sure that the server doesn't turn around and fill pages right back up again. The logic that needs to show up here has two parts: 1) Don't fill new pages completely up, save the space that will be needed in the new version 2) Find old pages that are filled and free some space on them The pre-upgrade utility we've been talking about does (2), and that's easy to imagine implementing as an add-on module rather than a backport. I don't know how (1) can be done in a way such that it's easily backported to 8.4. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [CORE] [HACKERS] EOL for 7.4?
Dave Page wrote: On Tue, Dec 1, 2009 at 4:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... 8.1 in RHEL5 ... +1 for letting 7.* and 8.0 die whenever no-one's motivated to bother supporting it anymore. Presumably you'll be on the hook until 2014 for 8.1 security patches I can't see the community wanting to support it for that long -1 for letting 8.1 die while someone major still supporting it, even if that means EOLing 8.2 before 8.1. As a PG user, it's confidence inspiring to see a project that can provide 7-years of support on a version. As a Red Hat customer, I'd feel happier if my database were not considered dead by the upstream community. It also feels more in the spirit of open-source to me -- where if one member is willing to put in work (Red Hat/Tom), the benefits are shared back; and in exchange the rest of the community can help with that contribution. I'm for EOLing *at least* 7.4 and 8.0 by January 2011, and I'm certainly not going to argue against doing the same for 8.1. Frankly, I think we could do 7.4 and maybe 8.0 six months earlier. I think the best would be to say 7.4 and 8.0 end in Jan 2011, and 8.1 switches to only high-priority security patches at that date. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
On Wed, Dec 2, 2009 at 1:56 PM, Greg Smith g...@2ndquadrant.com wrote: Robert Haas wrote: Some additional catalog support was suggested to mark what the pre-upgrade utility had processed. I'm sure I could find the messages about again if I had to. And that's a perfectly sensible solution, except that adding a catalog column to 8.4 at this point would force initdb, so that's a non-starter. I suppose we could shoehorn it into the reloptions. There's no reason the associated catalog support had to ship with the old version. You can always modify the catalog after initdb, but before running the pre-upgrade utility. pg_migrator might make that change for you. Uh, really? I don't think that's possible at all. The problem on the table at the moment is that the proposed CRC feature will expand every page by a uniform amount - so in this case a fixed-space-per-page reservation utility would be completely adequate. Does anyone think this is a realistic thing to backport to 8.4? I believe the main problem here is making sure that the server doesn't turn around and fill pages right back up again. The logic that needs to show up here has two parts: 1) Don't fill new pages completely up, save the space that will be needed in the new version 2) Find old pages that are filled and free some space on them The pre-upgrade utility we've been talking about does (2), and that's easy to imagine implementing as an add-on module rather than a backport. I don't know how (1) can be done in a way such that it's easily backported to 8.4. Me neither. ...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] Page-level version upgrade (was: Block-level CRC checks)
Robert Haas wrote: On Wed, Dec 2, 2009 at 1:56 PM, Greg Smith g...@2ndquadrant.com wrote: There's no reason the associated catalog support had to ship with the old version. You can always modify the catalog after initdb, but before running the pre-upgrade utility. pg_migrator might make that change for you. Uh, really? I don't think that's possible at all. Worst case just to get this bootstrapped: you install a new table with the added bits. Old version page upgrader accounts for itself there. pg_migrator dumps that data and then loads it into its new, correct home on the newer version. There's already stuff like that being done anyway--dumping things from the old catalog and inserting into the new one--and if the origin is actually an add-on rather than an original catalog page it doesn't really matter. As long as the new version can see the info it needs in its catalog it doesn't matter how it got to there; that's the one that needs to check the migration status before it can access things outside of the catalog. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com
Re: [HACKERS] Patch: Remove gcc dependency in definition of inline functions
Marko Kreen wrote: Note - my proposal would be to get rid of HAVE_INLINE, which means we are already using inline functions unconditionally on platforms that matter (gcc). Keeping duplicate code for obsolete compilers is pointless. Microsoft C doesn't matter? I seem to remember that when the Win32 version became available it actually increased the number of people trying postgres rather dramatically. Did that count for nothing? -- Sent 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: Remove gcc dependency in definition of inline functions
James Mansion ja...@mansionfamily.plus.com writes: Marko Kreen wrote: Note - my proposal would be to get rid of HAVE_INLINE, which means we are already using inline functions unconditionally on platforms that matter (gcc). Keeping duplicate code for obsolete compilers is pointless. Microsoft C doesn't matter? Breaking compilers that don't have inline at all isn't happening; it wouldn't buy us anything much anyway. The debate here is about how much we can assume about the behavior of compilers that do recognize the keyword. In particular, do they behave sensibly when finding an unreferenced static inline function, which is what would occur in many modules if we allow them to see inline functions in headers. 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: [CORE] [HACKERS] EOL for 7.4?
Robert Haas wrote: Again, to emphasize: many people are using 7.4, or 8.0, or 8.1, not because they necessarily want to, but they can't easily afford the downtime to upgrade. Cutting them off arbitrarily early won't win us any friends. Once pg_migrator (or better, in-place upgrades) is working well, we can start setting EOL on versions based on number of years of some other criteria. At the moment it doesn't seem likely that pg_migrator is *ever* going to support upgrading from 7.4 or 8.0 or 8.1 to any later version. Agreed. I'm not saying that's good, but nobody's expressed much interest in making in-place upgrade work even from an 8.2 base, let alone any older version. For that matter, there's been no concerted effort to resolve the limitations of the 8.3 - 8.4 upgrade. It isn't technically impossible for the 8.3 - 8.5 path to be smoother than the current 8.3 - 8.4 path, but nobody seems excited about working on it. Agreed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] YAML Was: CommitFest status/management
On Wed, 2009-12-02 at 10:45 -0800, Josh Berkus wrote: All, If some people want it, and there's no significant maintenance burden associated with YAML output, then why not? Mind you, if it was practical, I'd suggest that YAML ... and all additional Explain formats ... should be a contrib module. Anything other than XML and JSON will be fairly marginal. That would be my take... have explain kick out XML (or whatever) and then parse it into anything you want. That way it isn't additional burden into core. Joshua D. Drake --Josh Berkus -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent 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] bugfix for int2vectorin
New patch attached: 1. Does not add a new error message (though the pg_atoi's error message is a little goofy looking). 2. Handles int2 overflow cases. 3. oidvectorin does NOT suffer from the same problems as int2vectorin, someone already fixed it. As for the use-case I'm not completely sure... I'm not an end-user, I'm just responding to a bug report. My stance here is that returning an error (even a bad error) on trying to convert data in is better doing something wrong with bogus input. In the first case a user scratches their head, maybe files a bug report, you tell them the correct syntax and they go on. In the second case they input a bunch of data and then start complaining about data corruption, loss of data, etc. and the support case is 100x worse. The amount of code we are talking about here is less than 5 lines of code... Regards, Caleb On 12/1/09 9:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Caleb Welton cwel...@greenplum.com writes: On 12/1/09 7:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Under what circumstances would users (or anyone at all) be putting data into an int2vector? What exactly is your objection to having the int2arrayin parser handle its input conversion reasonably? I'm trying to gauge what the actual use-case is for having a slightly nicer error behavior. The proposed patch adds another translatable error string, which is no skin off my own nose but does create ongoing work for our translation team. And presumably, if we're going to fix this, we ought to fix the about-equally-stupid parsing logic in oidvectorin. While we're at it, should we trouble to detect overflow in int2vectorin? You could spend quite a bit of time and code making these functions more bulletproof, but I'm not convinced it's worth any work. regards, tom lane int2vector.patch Description: int2vector.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
On Wed, Dec 2, 2009 at 2:27 PM, Greg Smith g...@2ndquadrant.com wrote: Robert Haas wrote: On Wed, Dec 2, 2009 at 1:56 PM, Greg Smith g...@2ndquadrant.com wrote: There's no reason the associated catalog support had to ship with the old version. You can always modify the catalog after initdb, but before running the pre-upgrade utility. pg_migrator might make that change for you. Uh, really? I don't think that's possible at all. Worst case just to get this bootstrapped: you install a new table with the added bits. Old version page upgrader accounts for itself there. pg_migrator dumps that data and then loads it into its new, correct home on the newer version. There's already stuff like that being done anyway--dumping things from the old catalog and inserting into the new one--and if the origin is actually an add-on rather than an original catalog page it doesn't really matter. As long as the new version can see the info it needs in its catalog it doesn't matter how it got to there; that's the one that needs to check the migration status before it can access things outside of the catalog. That might work. I think that in order to get a fixed OID for the new catalog you would need to run a backend in bootstrap mode, which might (not sure) require shutting down the database first. But it sounds doable. There remains the issue of whether it is reasonable to think about backpatching such a thing, and whether doing so is easier/better than dealing with page expansion in the new server. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Ragged CSV import
Hi, In sept 2009 there was a discussion of ragged csv import and import requirements. I thought I'd add my requirements to the heap. I'm working a QA solution for a dbms migration project that will import query output from a non PG database into PG, so we can compare query results between PG and the alternative db. The query are supposed to have the same values, but many things can get in the way. My QA queries return a result set (multiple rows/columns) for each query. Copy improvements that would be useful 1. Copy to non-existent table - create it from the data type using most generic datatypes 2. provide column mapping function - from what I understand that the column list on a copy from is the target columns to load. I assume there must be a header record and the same columns in the Data file. The improvements discuss in the forum would be very helpful. Keep up the good work guys. Doug Little Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image002.jpg@01CA735A.7B428B90] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ inline: image002.jpg
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
On Wed, Dec 2, 2009 at 6:34 PM, Robert Haas robertmh...@gmail.com wrote: Also, your logic seems to presume that no backports are possible to the old server. The problem on the table at the moment is that the proposed CRC feature will expand every page by a uniform amount - so in this case a fixed-space-per-page reservation utility would be completely adequate. Does anyone think this is a realistic thing to backport to 8.4? This whole discussion is based on assumptions which do not match my recollection of the old discussion. I would suggest people go back and read the emails but it's clear at least some people have so it seems people get different things out of those old emails. My recollection of Tom and Heikki's suggestions for Zdenek were as follows: 1) When 8.9.0 comes out we also release an 8.8.x which contains a new guc which says to prepare for an 8.9 update. If that guc is set then any new pages are guaranteed to have enough space for 8.9.0 which could be as simple as guaranteeing there are x bytes of free space, in the case of the CRC it's actually *not* a uniform amount of free space if we go with Tom's design of having a variable chunk which moves around but it's still just a simple arithmetic to determine if there's enough free space on the page for a new tuple so it would be simple enough to backport. 2) When you want to prepare a database for upgrade you run the precheck script which first of all makes sure you're running 8.8.x and that the flag is set. Then it checks the free space on every page to ensure it's satisfactory. If not then it can do a noop update to any tuple on the page which the new free space calculation would guarantee would go to a new page. Then you have to wait long enough and vacuum. 3) Then you run pg_migrator which swaps in the new catalog files. 4) Then you shut down and bring up 8.9.0 which on reading any page *immediately* converts it to 8.9.0 format. 5) You would eventually also need some program which processes every page and guarantees to write it back out in the new format. Otherwise there will be pages that you never stop reconverting every time they're read. -- 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] Adding support for SE-Linux security
Josh Berkus j...@agliodbs.com writes: When GIS was introduced to this list ten years ago it was criticized as a marginal feature and huge and intrusive. But today it's probably 40% of our user base, and growing far more rapidly than anything else with Postgres. Maybe SE will be more like Rules than like GIS in the long run, but there's no way for us to know that today. What we do know is that GIS could be, and was, successfully developed outside core Postgres. It didn't need to suck away a major portion of the effort of the core developers. So it's not a very good analogy. In the end this is a debate about what the community should do with its finite development resources. Maybe, if we build this thing, they will come and we'll get so much additional contribution that it'll be a win all around. But somehow, alleged users who won't even decloak enough to tell us they want it don't seem like likely candidates for becoming major contributors. In words of one syllable: I do not care at all whether the NSA would use Postgres, if they're not willing to come and help us build it. If we tried to build it without their input, we'd probably not produce what they want anyway. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [CORE] [HACKERS] EOL for 7.4?
Robert Haas wrote: Again, to emphasize: many people are using 7.4, or 8.0, or 8.1, not because they necessarily want to, but they can't easily afford the downtime to upgrade. Cutting them off arbitrarily early won't win us any friends. Once pg_migrator (or better, in-place upgrades) is working well, we can start setting EOL on versions based on number of years of some other criteria. At the moment it doesn't seem likely that pg_migrator is *ever* going to support upgrading from 7.4 or 8.0 or 8.1 to any later version. I'm not saying that's good, but nobody's expressed much interest in making in-place upgrade work even from an 8.2 base, let alone any older version. For that matter, there's been no concerted effort to resolve the limitations of the 8.3 - 8.4 upgrade. It isn't technically impossible for the 8.3 - 8.5 path to be smoother than the current 8.3 - 8.4 path, but nobody seems excited about working on it. Migration is really only half the story, or not even that much. Every time you move to a new Postgres version you have to do extensive work to revalidate your application. If you don't do that you're just asking for trouble. But it can be painful, expensive and disruptive. I know of places where it can take weeks or months of effort. So the less often you have to do it the better. This would be true even if we had had a perfect working inplace upgrade mechanism for years, which as you and Greg point out is not true. I don't have any clients who don't/can't upgrade because they can't manage the downtime, but I have more than one avoiding upgrade because of revalidation costs. 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] [CORE] EOL for 7.4?
On Wed, Dec 2, 2009 at 7:53 PM, Bruce Momjian br...@momjian.us wrote: At the moment it doesn't seem likely that pg_migrator is *ever* going to support upgrading from 7.4 or 8.0 or 8.1 to any later version. Agreed. The problem is that the development effort to migrate data that was never designed to be migratable is completely out of scale from the benefits. You can solve problems pg_migrator has much more easily and with less damage to the code by putting the hooks into the server rather than making pg_migrator muck about inside the data structures fixing things. For example to deal with the problem of dropped columns we could add hooks to CREATE TABLE to allow pg_migrator to specify the physical order of columns. To do it without modifying the server pg_migrator has to play tricks by running updates against the catalog tables. And there are worse problems than that -- toast tables would require massive amounts of code in the new version to migrate but we found some simple tweaks to the toast format which eliminate the whole problem going forward. -- 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] Page-level version upgrade (was: Block-level CRC checks)
Greg Stark gsst...@mit.edu writes: This whole discussion is based on assumptions which do not match my recollection of the old discussion. I would suggest people go back and read the emails but it's clear at least some people have so it seems people get different things out of those old emails. My recollection of Tom and Heikki's suggestions for Zdenek were as follows: 1) When 8.9.0 comes out we also release an 8.8.x which contains a new guc which says to prepare for an 8.9 update. Yeah, I think the critical point is not to assume that the behavior of the old system is completely set in stone. We can insist that you must update to at least point release .N before beginning the migration process. That gives us a chance to backpatch code that makes adjustments to the behavior of the old server, so long as the backpatch isn't invasive enough to raise stability concerns. 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] YAML Was: CommitFest status/management
2009/12/3 Ron Mayer rm...@cheapcomplexdevices.com: Tom Lane wrote: Hmm. So the argument for it is let's make a machine-readable format more human-readable? I'm not getting the point. People should look at the regular text output. IMHO YAML beats the regular text format for human-readability - at least for people with narrow terminal windows, and for novices. Agreed. Calling the regular text output of EXPLAIN human readable is an exaggeration. 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] YAML Was: CommitFest status/management
Ron Mayer rm...@cheapcomplexdevices.com writes: Tom Lane wrote: Hmm. So the argument for it is let's make a machine-readable format more human-readable? I'm not getting the point. People should look at the regular text output. IMHO YAML beats the regular text format for human-readability - at least for people with narrow terminal windows, and for novices. Greg posted examples comparing regular-text vs yaml vs json here: http://archives.postgresql.org/pgsql-hackers/2009-08/msg02090.php Mph. Maybe I've been looking at the traditional format too long, but I don't find the YAML version better --- it's so verbose that you could only see a small fraction of a query at a time. The main strike against the traditional format IME is exactly what Greg alludes to in that message: it's prone to being rendered totally unreadable by email line-wrapping. However, I'm unconvinced that YAML would be any better; it looks like it's still critically dependent on the location and amount of whitespace in order to be readable. The lines might be a bit shorter on average, but you're still going to hit a narrow window's right margin pretty quick in any complicated plan. In any case, the real killer is email clients that feel no obligation to preserve whitespace layout at all, and this would certainly not look much better after that treatment. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: Remove gcc dependency in definition of inline functions
On 12/2/09, James Mansion ja...@mansionfamily.plus.com wrote: Marko Kreen wrote: Note - my proposal would be to get rid of HAVE_INLINE, which means we are already using inline functions unconditionally on platforms that matter (gcc). Keeping duplicate code for obsolete compilers is pointless. Microsoft C doesn't matter? I seem to remember that when the Win32 version became available it actually increased the number of people trying postgres rather dramatically. Did that count for nothing? The (gcc) above meant the inline functions are already used with gcc. I have no reason to think Microsoft's inlining works worse than gcc's. IOW - if the compiler does not support 'static inline' we should fall back to plain 'static' functions, instead maintaining duplicate macros. Such compilers would take a efficiency hit, but as they are practically non-existent they dont matter. Microsoft C does support inline, so it would not be affected. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page-level version upgrade (was: Block-level CRC checks)
On Wed, Dec 2, 2009 at 3:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: This whole discussion is based on assumptions which do not match my recollection of the old discussion. I would suggest people go back and read the emails but it's clear at least some people have so it seems people get different things out of those old emails. My recollection of Tom and Heikki's suggestions for Zdenek were as follows: 1) When 8.9.0 comes out we also release an 8.8.x which contains a new guc which says to prepare for an 8.9 update. Yeah, I think the critical point is not to assume that the behavior of the old system is completely set in stone. We can insist that you must update to at least point release .N before beginning the migration process. That gives us a chance to backpatch code that makes adjustments to the behavior of the old server, so long as the backpatch isn't invasive enough to raise stability concerns. If we have consensus on that approach, I'm fine with it. I just don't want one of the people who wants this CRC feature to go to a lot of trouble to develop a space reservation system that has to be backpatched to 8.4, and then have the patch rejected as too potentially destabilizing. ...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] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The reason this is a configurable parameter is so that people can tune it to their own needs. I think the current default fits all right with our usual policy of being conservative about hardware requirements. That only makes sense if you adjust it accordingly over time. It's been 12 for a long time - since January 2004 - while hardware has radically improved in that time, which means that either 12 was too high five years ago, is too low now, or is very insensitive to the speed of the hardware. I submit it's probably more of the second option. The postgresql.conf file has been supporting a toaster for a long time now, but we don't seem to recognize that the minimum toaster^H^Hhardware encountered in the wild changes quite a bit from year to year. IMHO. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200912021651 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksW4YMACgkQvJuQZxSWSsg7gACggzmyKkudzomoleil3PYMnB+z j+UAoMhi9yEAi8iPBVnailm8jKTe+z39 =++Jr -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 What about 14? Could we at least raise it to 14? 1/2 :) I doubt we can raise it at all without lying to ourselves about the likely results of so doing. The GEQO planning times are in the low double digits of milliseconds. My apps typically have a budget of at most ~200 ms to plan and execute the query, and I'm not always operating on empty tables. Well, this might be addressed elsewhere, but it's not just the planning time, it's the non-repeatable plans when you hit geqo. That tends to drive my clients mad (as in very confused, and then angry). And the plans that geqo comes up with are seldom very good ones either. So yes, it's an adjustable knob, but I'd rather see it default to 0 or = 14. I'm not sure I agree with the premise that there is a problem in need of fixing. I think we're usually pretty good about fixing things when there is a simple, straightforward fix. When the only real fixes involve writing a lot of code, we tend to be good about fixing them when - and only when - someone is willing and able to write that code. Often that's not the case, but that's an economic problem more than a process problem. And then there are cases (like CRCs) where we can't even figure out what the code would look like, and then we tend to do nothing, but what's the other choice? Obviously you see this issue differently so I'd like to hear more of your thoughts. Well, it's more a matter of consensus on the Right Thing To Do rather than a Simple Matter of Coding. Some of the more interesting conversations over the years has been on what to set the defaults to (random_page_cost anyone?). The conflict is then real world anecdotes versus test-backed, data-driven numbers. It's hard to get real numbers on many things, especially when people are using Postgres on a staggeringly large collection of hardware, database size, activity, etc. There's always a balance to hit the sweet spot for many knobs (both in postgresql.conf and elsewhere) between benefitting the most people while adversely impacting the least number of people. The project has been very, very conservative in this respect, which is why they need people like me who keep pushing in the other direction. Even if I secretly agree with Tom 99% of the time. :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200912021705 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksW5SoACgkQvJuQZxSWSsjmlQCePLKdyrCLpv86tIQtDbazKe+4 l5EAn3KfOy+ySxqhIe9UG2Jtshlb93Up =U7PP -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
Greg Sabino Mullane g...@turnstep.com writes: The reason this is a configurable parameter is so that people can tune it to their own needs. I think the current default fits all right with our usual policy of being conservative about hardware requirements. That only makes sense if you adjust it accordingly over time. It's been 12 for a long time - since January 2004 - while hardware has radically improved in that time, which means that either 12 was too high five years ago, is too low now, or is very insensitive to the speed of the hardware. I submit it's probably more of the second option. I don't have a problem with the third explanation ;-). The issue here is really planner speed relative to execution speed, and that's not so hardware-sensitive as all that. Yeah, you can plan a 12-join query way faster than ten years ago, but you can execute it way faster too, and that's what drives expectations for planning speed. Flat-planning a 15-way query costs just as much more relative to a 12-way query as it did ten years ago. 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] SE-PgSQL patch review
On Wed, Dec 2, 2009 at 3:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Red Hat's policy has been trying to cope with cases like which directories should Apache be allowed to read, *given that it's running a Red-Hat-standard configuration*? That's far more circumscribed than any useful database policy would be, because database applications aren't nearly that standardized. Actually that does sound useful for Redhat packages which themselves use database. So for example if I install my Redhat spam filter it should be able to automatically run createdb and load its schema and start using postgres as a backing store. Currently I think a lot of packages use sqlite by default just because manual intervention is required to set up postgres. So I'm unclear what advantage this has for Redhat and sysadmins over just setting up the database directly but then I'm unclear what the advantage is for SELinux in the first place so I'm probably just not in the target audience for it. But this seems like it would be directly analogous. I suppose an admin would be able to delegate more control to a new admin -- 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] Windows x64
Stefan Kaltenbrunner escribió: Tsutomu Yamada wrote: However, archive.postgresql.org has deleted the attachment. (Why? Email sent to the individual, the attachment is included.) Is it too large ? Should I resend them separately or compressing ? wrong mail format ? Should I try another mail software ? hmm this looks like a bug in the archive interface - might be related to the fact that it is not looking for attachments after the signature delimiter or such. Hmm, it certainly works in other cases. I think the problem is the part delimiter, =-=-= in that email; it's probably confusing MHonarc. -- 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] Adding support for SE-Linux security
Josh Berkus wrote: Bruce, If we decide not to support SE-Linux, it is unlikely we will be adding support for any other external security systems because SE-Linux has the widest adoption. I think the big question is whether we are ready to extend Postgres to support additional security infrastructures. PostgreSQL is the most security-conscious of the OSS databases, and is widely used by certain groups (security software, military, credit card processing) precisely because of this reputation. These folks, while unlikely to speak up on -hackers, are interested in new/further security features; when I was at the Pentagon 2 years ago several people there from HS were quite interested in SE-Postgres specifically. Further, I've been mentioning SE-Postgres in my DB security talk for the last 18 months and I *always* get a question about it. So while there might not be vocal proponents for innovative/hard-core security frameworks on this list currently, I think it will gain us some new users. Maybe more than we expect. Good, I also have gotten many voices, questions and requirements from the viewpoints of enterprise users who make plans to launch their SaaS system typically. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Adding support for SE-Linux security
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: When GIS was introduced to this list ten years ago it was criticized as a marginal feature and huge and intrusive. But today it's probably 40% of our user base, and growing far more rapidly than anything else with Postgres. Maybe SE will be more like Rules than like GIS in the long run, but there's no way for us to know that today. What we do know is that GIS could be, and was, successfully developed outside core Postgres. It didn't need to suck away a major portion of the effort of the core developers. So it's not a very good analogy. In the end this is a debate about what the community should do with its finite development resources. Maybe, if we build this thing, they will come and we'll get so much additional contribution that it'll be a win all around. But somehow, alleged users who won't even decloak enough to tell us they want it don't seem like likely candidates for becoming major contributors. In words of one syllable: I do not care at all whether the NSA would use Postgres, if they're not willing to come and help us build it. If we tried to build it without their input, we'd probably not produce what they want anyway. I don't know any reputations of NSA in US, except for Hollywood often makes them baddie in movies. However, it is the fact SELinux is already an open source software supported by people and corporations in multiple nations including former communist nations, not only USA and its allied nations. Needless to say, NEC is also a supporter to develop and maintain SE-PgSQL feature. We believe it is a necessity feature to construct secure platform for SaaS/Cloud computing, so my corporation has funded to develop SE-PgSQL for more than two years. As I noted before, if you worried about I escape anyware, it is quite incorrect. Now I've been working to develop and integrate SE-PgSQL in full-time. We can also say SELinux community provides a development resource to other OSS communities. For example, the recent version of Xorg has SELinux support in userspace, such as SE-PgSQL, by the developer who originally worked in SELinux community. SE-PgSQL is a similar case. Anyway, I don't think we should build barrier between communities. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Adding support for SE-Linux security
KaiGai Kohei wrote:. Needless to say, NEC is also a supporter to develop and maintain SE-PgSQL feature. We believe it is a necessity feature to construct secure platform for SaaS/Cloud computing, so my corporation has funded to develop SE-PgSQL for more than two years. As I noted before, if you worried about I escape anyware, it is quite incorrect. Now I've been working to develop and integrate SE-PgSQL in full-time. We can also say SELinux community provides a development resource to other OSS communities. For example, the recent version of Xorg has SELinux support in userspace, such as SE-PgSQL, by the developer who originally worked in SELinux community. SE-PgSQL is a similar case. Anyway, I don't think we should build barrier between communities. I think you have been remarkably good about our caution in accepting this. You certainly have my admiration for your patience. What would probably help us a lot would be to know some names of large users who want and will support this. NEC's name is a good start, but if a few other enterprise users spoke up it would help to make the decision a lot easier. My own experience with SE-Linux has been fairly unfortunate - I have tripped over it too many times and years ago adopted a practice of turning it off whenever I could. I suspect many people have similar war stories, and there will thus probably be quite some resistance to a feature I accept could well be of significant use to some classes of users. 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] Adding support for SE-Linux security
KaiGai Kohei wrote: Needless to say, NEC is also a supporter to develop and maintain SE-PgSQL feature. We believe it is a necessity feature to construct secure platform for SaaS/Cloud computing, so my corporation has funded to develop SE-PgSQL for more than two years. Rather than needless to say, I think this is worth elaborating on. Knowing how companies like NEC and their customers see SELinux and SE-PgSQL help their database projects would probably be one of the most compelling stories for getting broader support for the feature. Before googling nec software after seeing you mention this, I knew very little about NEC's software business. I can read some about NEC's software/database business for NEC North America's[1] and NEC Global Services[2] but imagine globally there's even more to it than that. Understanding how SE-PgSQL (and presumably SE-Linux) helps build a better SaaS/Cloud computing platform would probably help many people support this feature more. The cloud computing platforms I see more are ones that isolate a user's data either at a higher application layer (like salesforce) or a lower virtual machine layer (like amazon's elastic cloud). Is a vision of SE-PgSQL to help cloud computing companies sell customers access to a single underlying postgres instance, and share selected data between each other at a row level? Just curious. [1] http://www.necam.com/EntSw/ [2] http://www.necgs.com/partners.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PgSQL patch review
Joshua D. Drake wrote: On Tue, 2009-12-01 at 14:46 -0500, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On Mon, 2009-11-30 at 20:28 -0800, David Fetter wrote: This is totally separate from the really important question of whether SE-Linux has a future, and another about whether, if SE-Linux has a future, PostgreSQL needs to go there. Why would we think that it doesn't? Have you noticed anyone except Red Hat taking it seriously? I just did a little research and it appears the other two big names in this world (Novel and Ubuntu) are using something called App Armor. How much of SE-PgSQL would also complement the App Armor framework? Also, yet another MAC system called Tomoyo from NTT was merged into the linux kernel earlier this year. Is SE-PgSQL orthogonal and/or complimentary to all of those? Since I see MAC features continuing to be added to operating systems, I can certainly imagine they're important to some customers. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PgSQL patch review
Greg Stark wrote: So I'm unclear what advantage this has for Redhat and sysadmins over just setting up the database directly but then I'm unclear what the advantage is for SELinux in the first place so I'm probably just not in the target audience for it. But this seems like it would be directly analogous. I suppose an admin would be able to delegate more control to a new admin We (security engineers) consider to coordinate access controls on whole of the system, not only database, although it might be an irritating wordage for pgsql-hackers. Needless to say, database is one of the most significant software components nowadays, but not all. From the perspective of security administration, we want to restrict user's privileges using a centralized basis, called security policy. The basis needs to be independent from structures of a certain subsystem, such as OS or DBMS. SELinux is well suitable for the requirements in Linux. Note that SELinux is implemented as a feature of OS in fact, but it makes its access control decision based on only a couple of security contexts which is a class-independent identifier. (It is called reference monitor in security region.) It is not a good idea to invent an another framework again for whole of the system security, because many of userspace applications already support SELinux features. It also means stuff to manage security context of resources, not only applying additional access controls based on SELinux policy like SE-PgSQL. For example, ls -Z shows security context of files, mv keeps security context of files, even if user tries to move a file across filesystems, tar --selinux support backup/restore security context of the files, ... There are various kind of options, however, SELinux provides most widespread support on various kind of software components. If we would be Oracle, we may be able to provide whole of the software components. But it is not right way in OSS community. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Adding support for SE-Linux security
Ron Mayer wrote: KaiGai Kohei wrote: Needless to say, NEC is also a supporter to develop and maintain SE-PgSQL feature. We believe it is a necessity feature to construct secure platform for SaaS/Cloud computing, so my corporation has funded to develop SE-PgSQL for more than two years. Rather than needless to say, I think this is worth elaborating on. Knowing how companies like NEC and their customers see SELinux and SE-PgSQL help their database projects would probably be one of the most compelling stories for getting broader support for the feature. Before googling nec software after seeing you mention this, I knew very little about NEC's software business. I can read some about NEC's software/database business for NEC North America's[1] and NEC Global Services[2] but imagine globally there's even more to it than that. I'm talking about our future business, not existing one. Anyway, what is important here is that out corporation makes a decision to contribute to develop and maintain an innovative OSS project rather than what our business works well. Understanding how SE-PgSQL (and presumably SE-Linux) helps build a better SaaS/Cloud computing platform would probably help many people support this feature more. The cloud computing platforms I see more are ones that isolate a user's data either at a higher application layer (like salesforce) or a lower virtual machine layer (like amazon's elastic cloud). Is a vision of SE-PgSQL to help cloud computing companies sell customers access to a single underlying postgres instance, and share selected data between each other at a row level? Just curious. Basically, note than we have no magic-bullets in security region. Any approach has its merits and demerits. It depends on users what should be emphasized. If we tries to separate user's information assets in the application level (like salesforce), the code to be checked and bug-free are much larger than a case when we enforce accesses in OS/RDBMS. It shall make development cost to increase. If we tries to separate user's information assets in the virtual machine layer (like amazon), the worker-hour to maintain each virtual machines larger than a case when we enforce accesses in OS/RDBMS layer. It shall make maintenance cost to increase. If we tries to separate user's information assets in the OS/RDBMS layer, the code to be checked and bug-free are less than application level checks, and all administrator need to do is manage a limited number of instances. The granularity of access controls is not a primary matter. We can separate user's information assets in table level, not only row level. In addition, we cat set up a part of shared tables, unlike virtual machine approach. I don't mean this approach it a magic-bullets, but it can be an option for security-conscious users. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
On Wed, Dec 2, 2009 at 5:08 PM, Greg Sabino Mullane g...@turnstep.com wrote:
What about 14? Could we at least raise it to 14? 1/2 :)
I doubt we can raise it at all without lying to ourselves about the
likely results of so doing. The GEQO planning times are in the low
double digits of milliseconds. My apps typically have a budget of at
most ~200 ms to plan and execute the query, and I'm not always
operating on empty tables.
Well, this might be addressed elsewhere, but it's not just the planning
time, it's the non-repeatable plans when you hit geqo. That tends to
drive my clients mad (as in very confused, and then angry). And the plans
that geqo comes up with are seldom very good ones either. So yes, it's an
adjustable knob, but I'd rather see it default to 0 or = 14.
Actually, I think Tom made some changes for 8.5 that should eliminate
the randomness, if not the badness. Or am I misremembering?
One other thing I'm noticing about the current implementation is that
it seems to spend an entirely excessive amount of brain power
considering the best order in which to execute cross-joins. If I do
X, A JOIN B ON Pab JOIN C ON Pac JOIN D ON Pad JOIN E ON Pae, it looks
to me like join_search_one_level() will try joining X to each of A-E.
That seems fairly pointless; why would I ever want to join X to
anything other than {A B C D E}?
Well, it's more a matter of consensus on the Right Thing To Do rather
than a Simple Matter of Coding. Some of the more interesting conversations
over the years has been on what to set the defaults to (random_page_cost
anyone?). The conflict is then real world anecdotes versus test-backed,
data-driven numbers. It's hard to get real numbers on many things,
especially when people are using Postgres on a staggeringly large collection
of hardware, database size, activity, etc. There's always a balance to
hit the sweet spot for many knobs (both in postgresql.conf and elsewhere)
between benefitting the most people while adversely impacting the least
number of people. The project has been very, very conservative in this
respect, which is why they need people like me who keep pushing in the
other direction. Even if I secretly agree with Tom 99% of the time. :)
Heh. Well, we did raise default_statistics_target quite a bit for
8.4. I suggested raising from_collapse_threshold,
join_collapse_threshold, and geqo_threshold, but diligent
experimenation by Tom and Andres Freund revealed this idea to suck. I
think it's an interesting area for more work to try to eliminate the
suckage, but I don't think we're there yet. I don't think I remember
the last round of debates about random_page_cost and seq_page_cost;
the current values probably are too high for most people, because
typically you've got a lot of stuff cached. We should maybe also
think about raising the default value for work_mem. It's hard for me
to believe that the average Postgres user wants a sort that takes more
than 1MB of memory to spill to disk; there certainly are people who
probably want that, but I doubt there are very many. I believe we've
been using that value for a decade, and memory size has increased a
lot in that time.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposing new logline_prefix escape...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 So... Came across a situation today where I would have liked to know the effective role of a query because of a permission error. When I went to add that to the logline_prefix, I realized that right now all we have is %u which gives you the equivalent of session_user...I think it would be useful to have one for current_user, for situations like mine where a function was changing the effective role. Thoughts? Disagreements? Flames? - -- Jon T Erdman Chief Information Officer Progressive Practice, Inc. P.O. Box 17288 Rochester, NY 14617 www.progressivepractice.com -BEGIN PGP SIGNATURE- iEYEARECAAYFAksXGpUACgkQRAk1+p0GhSG+kQCfej6dcViCC5IR7dgAdps0tZaN UVAAnRQTG+GW/0lMQZt1rFGF9IHAAp6K =0tg4 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] set the cost of an aggregate function
On Mon, Nov 30, 2009 at 11:53 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: 2009/11/30 Jaime Casanova jcasa...@systemguards.com.ec: Hi, why we can't do $subject? it could have any benefit on the planner? seems like while we can set the cost of the state transition function, that cost is not propagated... I thought for sure you must be wrong about this, but I just read the source code and, sure enough, the cost of the transition and final functions are totally ignored. In fact, there's a comment about this in cost_agg(): * Note: ideally we should use the pg_proc.procost costs of each * aggregate's component functions, but for now that seems like an * excessive amount of work. ...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] SE-PgSQL patch review
Ron Mayer wrote: Joshua D. Drake wrote: On Tue, 2009-12-01 at 14:46 -0500, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On Mon, 2009-11-30 at 20:28 -0800, David Fetter wrote: This is totally separate from the really important question of whether SE-Linux has a future, and another about whether, if SE-Linux has a future, PostgreSQL needs to go there. Why would we think that it doesn't? Have you noticed anyone except Red Hat taking it seriously? I just did a little research and it appears the other two big names in this world (Novel and Ubuntu) are using something called App Armor. How much of SE-PgSQL would also complement the App Armor framework? Also, yet another MAC system called Tomoyo from NTT was merged into the linux kernel earlier this year. Is SE-PgSQL orthogonal and/or complimentary to all of those? Since I see MAC features continuing to be added to operating systems, I can certainly imagine they're important to some customers. Yes, nowadays, Linux has three MAC options: Linux, Smack and Tomoyo. And AppArmor is now under discussion to merge it. *In the current state*, our security hooks invoke SE-PgSQL routines directly, unlike LSM framework in Linux, because it is the first option for us, and no need to support multiple options now. (It will simply increase the size of changeset in this stage.) However, when the second option comes in, we can easily enhance the security hooks to support multiple MAC framework. The Smack also needs security label. It will be able to share facilities to manage security context with SE-PgSQL. I've often talked with developers of TOMOYO Linux. They currently give higher priority to upstream all their functionalities into Linux. But it also may be a valueable theme for them. At least, I don't think it requires much different hook points more than SELinux support. AppArmor's access control model is similar to TOMOYO. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposing new logline_prefix escape...
So... Came across a situation today where I would have liked to know the effective role of a query because of a permission error. When I went to add that to the logline_prefix, I realized that right now all we have is %u which gives you the equivalent of session_user...I think it would be useful to have one for current_user, for situations like mine where a function was changing the effective role. Thoughts? Disagreements? Flames? -- Jon T Erdman Chief Information Officer Progressive Practice, Inc. P.O. Box 17288 Rochester, NY 14617 www.progressivepractice.com signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
Robert Haas robertmh...@gmail.com writes:
Actually, I think Tom made some changes for 8.5 that should eliminate
the randomness, if not the badness. Or am I misremembering?
It was mostly Andres' work, see
http://archives.postgresql.org/pgsql-committers/2009-07/msg00148.php
One other thing I'm noticing about the current implementation is that
it seems to spend an entirely excessive amount of brain power
considering the best order in which to execute cross-joins. If I do
X, A JOIN B ON Pab JOIN C ON Pac JOIN D ON Pad JOIN E ON Pae, it looks
to me like join_search_one_level() will try joining X to each of A-E.
That seems fairly pointless; why would I ever want to join X to
anything other than {A B C D E}?
Not sure that a lot of cross joins with no conditions is the case to
design around. Usually queries aren't that devoid of features of
interest, and so different join paths are actually usefully different.
... We should maybe also
think about raising the default value for work_mem. It's hard for me
to believe that the average Postgres user wants a sort that takes more
than 1MB of memory to spill to disk; there certainly are people who
probably want that, but I doubt there are very many. I believe we've
been using that value for a decade, and memory size has increased a
lot in that time.
Maybe. I'll certainly grant that machines have more memory, but is the
average Postgres installation using that to run bigger sorts, or to run
more sorts (either more concurrent queries or more complex queries
containing more sorts)? We know that increasing work_mem too much
can be counterproductive, and much sooner than one might think.
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] CommitFest status/management
On Tue, Dec 1, 2009 at 9:43 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 1, 2009 at 9:42 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: If we went with Bruce's interpretation, we could have a committer field that only appears when the status is Claimed by Committer or Committed and the contents of that field could be displayed in parentheses in the status column, like this: Claimed by Committer (Tom Lane). If we went with Andrew's interpretation, we would need a completely separate column, because there wouldn't be any logical relationship between the status field and the committer field. Any other votes? Tom? I'm happy with Andrew's interpretation --- I just want a separate text field for inserting a committer's name. I don't want any magic behavior of that field. OK, I'll add a separate text field for the committer's name, but for now it won't display on the summary page, just the detail page. Done. ...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] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
On Wed, Dec 2, 2009 at 9:55 PM, Tom Lane t...@sss.pgh.pa.us wrote:
One other thing I'm noticing about the current implementation is that
it seems to spend an entirely excessive amount of brain power
considering the best order in which to execute cross-joins. If I do
X, A JOIN B ON Pab JOIN C ON Pac JOIN D ON Pad JOIN E ON Pae, it looks
to me like join_search_one_level() will try joining X to each of A-E.
That seems fairly pointless; why would I ever want to join X to
anything other than {A B C D E}?
Not sure that a lot of cross joins with no conditions is the case to
design around. Usually queries aren't that devoid of features of
interest, and so different join paths are actually usefully different.
Not sure what you mean. There's already a special-case code path for
cross joins; but I think it's probably considering a lot of silly
paths. Is there a case where it makes sense to do cross joins at some
stage of the process other than last?
... We should maybe also
think about raising the default value for work_mem. It's hard for me
to believe that the average Postgres user wants a sort that takes more
than 1MB of memory to spill to disk; there certainly are people who
probably want that, but I doubt there are very many. I believe we've
been using that value for a decade, and memory size has increased a
lot in that time.
Maybe. I'll certainly grant that machines have more memory, but is the
average Postgres installation using that to run bigger sorts, or to run
more sorts (either more concurrent queries or more complex queries
containing more sorts)? We know that increasing work_mem too much
can be counterproductive, and much sooner than one might think.
A further confounding factor is that work_mem also controls memory
usage for hash tables - whereas the original sort_mem did not - and at
least in my experience it's more common to have multiple hashes in a
query than multiple sorts. It would be nice to have some data on
this rather than just hand-waving, but I'm not sure how to get it.
For default_statistics_target, *_collapse_threshold, and
geqo_threshold, we were able to construct worst-case queries and
benchmark them. I have no idea how to do something comparable for
work_mem.
...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] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
Robert Haas robertmh...@gmail.com writes: Not sure what you mean. There's already a special-case code path for cross joins; but I think it's probably considering a lot of silly paths. Is there a case where it makes sense to do cross joins at some stage of the process other than last? They *are* done last, as a rule, because of the heuristic that prefers to join where there's a join clause. (However I've gotten negative comments about that --- some people think that when joining small detail tables to a big fact table, it'd be better to cross-join the detail tables and then do one multi-clause join to the big table. I'm unconvinced myself but there does seem to be more than one school of thought about it.) 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] CommitFest status/management
Robert Haas wrote: I'm happy with Andrew's interpretation --- I just want a separate text field for inserting a committer's name. I don't want any magic behavior of that field. OK, I'll add a separate text field for the committer's name, but for now it won't display on the summary page, just the detail page. Done. Cool. Thanks. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
On Wed, Dec 2, 2009 at 10:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Not sure what you mean. There's already a special-case code path for cross joins; but I think it's probably considering a lot of silly paths. Is there a case where it makes sense to do cross joins at some stage of the process other than last? They *are* done last, as a rule, because of the heuristic that prefers to join where there's a join clause. Well, when I was testing, I believe I observed that an n-way join with 1 cross join was slower to plan than an n-way join with no cross joins. ISTM that it should actually be faster, because you should plan it like an (n-1)-way join and then do the cross join at the end. (However I've gotten negative comments about that --- some people think that when joining small detail tables to a big fact table, it'd be better to cross-join the detail tables and then do one multi-clause join to the big table. I'm unconvinced myself but there does seem to be more than one school of thought about it.) Sounds weird to me. There might be a sweet spot where that's true (3 or 4 detail tables with 2 or 3 rows each, that aren't too wide?) but even if there is, I bet it's not very big. If someone cares though it should be possible to convince the planner to execute the query that way (using OFFSET 0, maybe) and benchmark it vs. whatever the planner wants to do otherwise. ...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] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
Robert Haas robertmh...@gmail.com writes: Well, when I was testing, I believe I observed that an n-way join with 1 cross join was slower to plan than an n-way join with no cross joins. ISTM that it should actually be faster, because you should plan it like an (n-1)-way join and then do the cross join at the end. It's not entirely clear to me what case you're describing, but I wonder whether this was a flat join problem or restricted by the collapse limits. 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] pgbench: new feature allowing to launch shell commands
Hi, Sorry if you receive this email a second time, Greg, i didn't notice it has not been sent to the hackers ML Thanks for your first review. I tried to work on most of the issues you noticed 1) Needs tab/space formatting cleaned up This one is done, I adapted my environment to the Postgresql formats. I hope there is nothing else more linked to that. 2) Execution of meta-command failed errors are a small but serious problem This error appears (n-1) times by using n threads with the j option. As you said in your previous email there is some thread cleanup when one is disconnected. This error still appears, I don't know yet which part of the code is the origin of that. I needs more investigation. 3) Should consider how :variable interpretation should work in a \[set]shell call It is supported now. I implemented this, I made a test with your pearl script, my own tests and it worked, at least no error appeared :) 4) General code cleanup, including possible refactoring I didn't modify too much the code, I just noticed a couple of variables unnecessary and some definitions not in adequacy with pgbench code. Btw, what I did is included in the patch. 5) Update pgbench docs to cover new calls. I hoped to find time to help with this, it looks like I'm not going to have it in the near future. I tried to update the document writing a couple of lines describing simply the new possible calls setshell and shell. I am not that skilled at sgml though. 6) Should do basic performance regression testing to confirm this patch doesn't impact pgbench results that don't use the new feature. This I'll take care of, I'm not particularly worried about that based on what's been changed so far. Do you have an idea of what kind of tests could be done? I don't have so much experience about common regression tests linked to pgbench. I also added a second file including a couple of scripts written quickly generating numbers based on the gauss and pareto density functions. It cannot be used straightforwardly now, but still it can be a base for something linked to setshell. Regards, Michael Paquier NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center pgbenchshell2.1.patch Description: Binary data pgbenchstats.tar.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
Re: [HACKERS] Cost of sort/order by not estimated by the query planner
2009/12/1 Laurent Laborde kerdez...@gmail.com: The problem is in the order by, of course. If i remove the order by the LIMIT 5 is faster (0.044 ms) and do an index scan. At limit 500 (without order) it still use an index scan and it is slightly slower. At limit 5000 (without order) it switch to a Bitmap Index Scan + Bitmap Heap Scan and it's slower but acceptable (5.275 ms) Why, with the QUERY 2, postgresql doesn't estimate the cost of the Sort/ORDER BY ? Of course, by ignoring the order, both query plan are right and the choice for thoses differents plans totally make sense. It's because the result of IndexScan is already sorted by demanded key, whereas the one of BitmapIndexScan isn't. But I'm not sure why the query lasts more than 30 minutes... Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ProcessUtility_hook
Tom Lane t...@sss.pgh.pa.us wrote: ... and now that I have, I find at least four highly questionable things about it: 1. The placement of the hook. Why is it three lines down in ProcessUtility? It's probably reasonable to have the Assert first, but I don't see why the hook function should have the ability to editorialize on the behavior of everything about ProcessUtility *except* the read-only-xact check. I moved the initialization of completionTag into standard_ProcessUtility. 2. The naming and documentation of the added GUC setting for pg_stat_statements. track_ddl seems pretty bizarre to me because there are many utility statements that no one would call DDL. COPY, for example, is certainly not DDL. Why not call it track_utility? Ok, fixed. 3. The enable-condition test in pgss_ProcessUtility. Is it really appropriate to be gating this by isTopLevel? I should think that the nested_level check in pgss_enabled would be sufficient and more likely to do what's expected. I removed the isTopLevel check. I was worried about auto-generated utility commands; generated sub commands are called with the same query string as the top query. Don't it confuse statistics? 4. The special case for CopyStmt. That's just weird, and it adds a maintenance requirement we don't need. I don't see a really good argument why COPY (alone among utility statements) deserves to have a rowcount tracked by pg_stat_statements, but even if you want that it'd be better to rely on examining the completionTag after the fact. The fact that the tag is COPY is part of the user-visible API for COPY and won't change lightly. The division of labor between ProcessUtility and copy.c is far more volatile, but this patch has injected itself into that. Ok, fixed. I've thought string-based interface is not desirable, but it should be a stable API. COPY and INSERT/UPDATE/DELETE (used by EXECUTE) are counted by pg_stat_statements, but EXECUTE SELECT is impossible. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ProcessUtility_hook_20091203.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
