Re: [HACKERS] draft RFC: concept for partial, wal-based replication
On Monday 30 November 2009 03:57:11 Itagaki Takahiro wrote: Boszormenyi Zoltan z...@cybertec.at wrote: we tried to discuss on a lower level what should be needed for a partial replication based on streaming replication. We need to discuss a partial recovery before the partial replication. If you do the filtering on the sending side you dont actually need partial recover in the sense that you filter in the rmgr or similar. Or do I miss something? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
Andres Freund and...@anarazel.de wrote: We need to discuss a partial recovery before the partial replication. If you do the filtering on the sending side you dont actually need partial recover in the sense that you filter in the rmgr or similar. Or do I miss something? Sorry, I didn't explain well. I just suggested the order of development. I think paritial recovery is easier than partition replication because we don't need to think network nor multiple clients in recovery. Also, this feature can be developed on the Streming Replication, but it is under development -- the code is not fixed. So, I'd suggest to start the development from independent parts from Streaming Replication. I believe the partial replication will require modules developed for the partial recovery in the future. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is isolation level 'Serializable' in pg not same as 'serializable' in SQL-92?
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
Andres Freund wrote: On Monday 30 November 2009 03:57:11 Itagaki Takahiro wrote: Boszormenyi Zoltan z...@cybertec.at wrote: we tried to discuss on a lower level what should be needed for a partial replication based on streaming replication. We need to discuss a partial recovery before the partial replication. If you do the filtering on the sending side you dont actually need partial recover in the sense that you filter in the rmgr or similar. Or do I miss something? the question is if filtering on the sending side is actually the right thing to do. It increases the overhead and the complexity on the master, especially if you think about different (partial) replication agreements for different slaves and it might also be hard to integrate with the planned sync/async modes. On the other hand if you filter on the master you might be able to avoid a lot of network traffic du to filtered wal records. I think for a first step it might make more sense to look into doing the filtering on the receiving side and look into actual integration with SR at a later stage. 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] pg_read_file() and non-ascii input file
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: pg_read_file() takes byte-offset and length as arguments, but we don't check the result text with pg_verify_mbstr(). Should pg_read_file() return bytea instead of text or adding some codes to verify the input? Only superusers are allowed to use the function, but it is still dangerous. Here is a patch to modify the result type of pg_read_file to bytea. I think it is a possibly-security hole -- it might be safe because only supersusers can use the function, but it is still dangerous. We can still use the function to read a text file: SELECT convert_from(pg_read_file(...), 'encoding') If we want to keep backward compatibility, the issue can be fixed by adding pg_verifymbstr() to the function. We can also have the binary version in another name, like pg_read_binary_file(). Which solution is better? Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pg_read_file_as_bytea.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
On Monday 30 November 2009 10:32:50 Stefan Kaltenbrunner wrote: Andres Freund wrote: On Monday 30 November 2009 03:57:11 Itagaki Takahiro wrote: Boszormenyi Zoltan z...@cybertec.at wrote: we tried to discuss on a lower level what should be needed for a partial replication based on streaming replication. We need to discuss a partial recovery before the partial replication. If you do the filtering on the sending side you dont actually need partial recover in the sense that you filter in the rmgr or similar. Or do I miss something? the question is if filtering on the sending side is actually the right thing to do. It increases the overhead and the complexity on the master, especially if you think about different (partial) replication agreements for different slaves and it might also be hard to integrate with the planned sync/async modes. On the other hand if you filter on the master you might be able to avoid a lot of network traffic du to filtered wal records. I think for a first step it might make more sense to look into doing the filtering on the receiving side and look into actual integration with SR at a later stage. I think filtering on the receiving side is harder by many degrees because you don't have an up 2 date copy of the catalog. I cant think of a design that does not impose severe constraints on catalog and especially replication settings to implement on the receiving side. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
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. -- 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] Aggregate ORDER BY patch
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 -- Andrew (irc:RhodiumToad) aorder-20091130.patch.gz Description: aggregate order by 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] draft RFC: concept for partial, wal-based replication
On Nov 30, 2009, at 1:55 AM, Craig Ringer cr...@postnewspapers.com.au wrote: Boszormenyi Zoltan wrote: c. splitting wal into different replication sets Just a side note: in addition to its use for partial replication, this might have potential for performance-prioritizing databases or tablespaces. Being able to separate WAL logging so that different DBs, tablespaces, etc went to different sets of WAL logs would allow a DBA to give some databases or tablespaces dedicated WAL logging space on faster storage. If partial recovery is implemented, it might also permit less important databases to be logged to fast-but-unsafe storage such as a non-BBU disk controller with write cache enabled, without putting more important databases in the same cluster in danger. The danger here is that if we make crash recovery more complex, we'll introduce subtle bugs that will only be discovered after someone's data is toast. ...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] Patch: Remove gcc dependency in definition of inline functions
Marko Kreen wrote: On 11/29/09, Tom Lane t...@sss.pgh.pa.us wrote: Kurt Harriman harri...@acm.org writes: (Does anybody still use a C compiler that doesn't support inline functions?) +1 for modern C. The question isn't so much that, it's whether the compiler supports inline functions with the same behavior as gcc. At minimum that would require * not generating extra copies of the function * not whining about unreferenced static functions How many compilers have you tested this patch against? Which ones does it actually offer any benefit for? Those are not correctness problems. Compilers with non-optimal or missing 'inline' do belong together with compilers without working int64. We may spend some effort to be able to compile on them, but they should not affect our coding style. 'static inline' is superior to macros because of type-safety and side-effects avoidance. I'd suggest event removing any HAVE_INLINE ifdefs and let autoconf undef the 'inline' if needed. Less duplicated code to maintain. The existence of compilers in active use without working 'inline' seems quite hypothetical these days. I thought one problem was that inline is a suggestion that the compiler can ignore, while macros have to be implemented as specified. -- 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
[HACKERS] Feature request: permissions change history for auditing
Hi, As far as I am aware, there is no way to tell when a user/role was granted permissions or had permissions revoked, or who made these changes. I'm wondering if it would be useful for security auditing to maintain a history of permissions changes only accessible to superusers? Thanks Thom
Re: [HACKERS] Feature request: permissions change history for auditing
--- On Mon, 30/11/09, Thom Brown thombr...@gmail.com wrote: As far as I am aware, there is no way to tell when a user/role was granted permissions or had permissions revoked, or who made these changes. I'm wondering if it would be useful for security auditing to maintain a history of permissions changes only accessible to superusers? I'd have thought you could keep track of this in the logs by setting log_statement = ddl ? I'm pretty sure this is a feature that's not wanted, but the ability to add triggers to these sorts of events would surely make more sense than a specific auditing capability. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Hi Jeff, the current patch suffers from what Heikki recently spotted: If one backend is putting notifications in the queue and meanwhile another backend executes LISTEN and commits, then this listening backend committed earlier and is supposed to receive the notifications of the notifying backend - even though its transaction started later. I have a new version that deals with this problem but I need to clean it up a bit. I am planning to post it this week. On Mon, Nov 30, 2009 at 6:15 AM, Jeff Davis pg...@j-davis.com wrote: * Why don't we read all notifications into backend-local memory at every opportunity? It looks like sometimes it's only reading the committed ones, and I don't see the advantage of leaving it in the SLRU. Exactly because of the problem above we cannot do it. Once the notification is removed from the queue, then no other backend can execute a LISTEN anymore because there is no way for it to get that information. Also we'd need to read _all_ notifications, not only the committed ones because we don't know what our backend will LISTEN to in the future. On the other hand, reading uncommitted notifications guarantees that we can send an unlimited number of notifications (limited by main memory) and that we don't run into a full queue in this example: Queue length: 1000 3 notifying backends, 400 notifications to be sent by each backend. If all of them send their notifications at the same time, we risk that all three run into a full queue... We could still preserve that behavior on the cost that we allow LISTEN to block until the queue is within its limits again. * When the queue is full, the inserter tries to signal the listening backends, and tries to make room in the queue. * Backends read the notifications when signaled, or when inserting (in case the inserting backend is also the one preventing the queue from shrinking). Exactly, but it doesn't solve the problem described above. :-( ISTM that we have two options: a) allow LISTEN to block if the queue is full - NOTIFY will never fail (but block as well) and will eventually succeed b) NOTIFY could fail and make the transaction roll back - LISTEN always succeeds immediately Again: This is corner-case behavior and only happens after some hundreds of gigabytes of notifications have been put to the queue and have not yet been processed by all listening backends. I like a) better, but b) is easier to implement... I haven't looked at everything yet, but this seems like it's in reasonable shape from a high level. Joachim, can you clean the patch up, include docs, and fix the tests? If so, I'll do a full review. As soon as everybody is fine with the approach, I will work on the docs patch. Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On Fri, 2008-10-17 at 12:26 -0300, Alvaro Herrera wrote: So this discussion died with no solution arising to the hint-bit-setting-invalidates-the-CRC problem. Apparently the only solution in sight is to WAL-log hint bits. Simon opines it would be horrible from a performance standpoint to WAL-log every hint bit set, and I think we all agree with that. So we need to find an alternative mechanism to WAL log hint bits. It occurred to me that maybe we don't need to WAL-log the CRC checks. Proposal * We reserve enough space on a disk block for a CRC check. When a dirty block is written to disk we calculate and annotate the CRC value, though this is *not* WAL logged. * In normal running we re-check the CRC when we read the block back into shared_buffers. * In recovery we will overwrite the last image of a block from WAL, so we ignore the block CRC check, since the WAL record was already CRC checked. If full_page_writes = off, we ignore and zero the block's CRC for any block touched during recovery. We do those things because the block CRC in the WAL is likely to be different to that on disk, due to hints. * We also re-check the CRC on a block immediately before we dirty the block (for any reason). This minimises the possibility of in-memory data corruption for blocks. So in the typical case all blocks moving from disk - memory and from clean - dirty are CRC checked. So in the case where we have full_page_writes = on then we have a good CRC every time. In the full_page_writes = off case we are exposed only on the blocks that changed during last checkpoint cycle and only if we crash. That seems good because most databases are up 99% of the time, so any corruptions are likely to occur in normal running, not as a result of crashes. This would be a run-time option. Like it? -- 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] Feature request: permissions change history for auditing
2009/11/30 Glyn Astill glynast...@yahoo.co.uk --- On Mon, 30/11/09, Thom Brown thombr...@gmail.com wrote: As far as I am aware, there is no way to tell when a user/role was granted permissions or had permissions revoked, or who made these changes. I'm wondering if it would be useful for security auditing to maintain a history of permissions changes only accessible to superusers? I'd have thought you could keep track of this in the logs by setting log_statement = ddl ? I'm pretty sure this is a feature that's not wanted, but the ability to add triggers to these sorts of events would surely make more sense than a specific auditing capability. I concede your suggestion of the ddl log output. I guess that could then be filtered to obtain the necessary information. Thanks Thom
Re: [HACKERS] Feature request: permissions change history for auditing
Thom Brown wrote: 2009/11/30 Glyn Astill glynast...@yahoo.co.uk mailto:glynast...@yahoo.co.uk --- On Mon, 30/11/09, Thom Brown thombr...@gmail.com mailto:thombr...@gmail.com wrote: As far as I am aware, there is no way to tell when a user/role was granted permissions or had permissions revoked, or who made these changes. I'm wondering if it would be useful for security auditing to maintain a history of permissions changes only accessible to superusers? I'd have thought you could keep track of this in the logs by setting log_statement = ddl ? I'm pretty sure this is a feature that's not wanted, but the ability to add triggers to these sorts of events would surely make more sense than a specific auditing capability. I concede your suggestion of the ddl log output. I guess that could then be filtered to obtain the necessary information. This could probably be defeated by making the permissions changes in a stored function. Or even a DO block, I suspect, unless you had log_statement = all set. I do agree with Glyn, though, that making provision for auditing one particular event is not desirable. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: Remove gcc dependency in definition of inline functions
On mån, 2009-11-30 at 07:06 -0500, Bruce Momjian wrote: I thought one problem was that inline is a suggestion that the compiler can ignore, while macros have to be implemented as specified. Sure, but one could argue that a compiler that doesn't support inline usefully is probably not the sort of compiler that you use for compiling performance-relevant software anyway. We can support such systems in a degraded way for historical value and evaluation purposes as long as it's pretty much free, like we support systems without working int8. -- Sent 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
Peter Eisentraut wrote: On m?n, 2009-11-30 at 07:06 -0500, Bruce Momjian wrote: I thought one problem was that inline is a suggestion that the compiler can ignore, while macros have to be implemented as specified. Sure, but one could argue that a compiler that doesn't support inline usefully is probably not the sort of compiler that you use for compiling performance-relevant software anyway. We can support such systems in a degraded way for historical value and evaluation purposes as long as it's pretty much free, like we support systems without working int8. The issue is that many compilers will take inline as a suggestion and decide if it is worth-while to inline it --- I don't think it is inlined unconditionally by any modern compilers. Right now we think we are better at deciding what should be inlined than the compiler --- of course, we might be wrong, and it would be good to performance test this. -- 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] draft RFC: concept for partial, wal-based replication
Craig Ringer cr...@postnewspapers.com.au writes: Just a side note: in addition to its use for partial replication, this might have potential for performance-prioritizing databases or tablespaces. Being able to separate WAL logging so that different DBs, tablespaces, etc went to different sets of WAL logs would allow a DBA to give some databases or tablespaces dedicated WAL logging space on faster storage. I don't think this can possibly work without introducing data corruption issues. What happens when a transaction touches tables in different tablespaces? You can't apply the changes out-of-order. 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 11/30/09, Bruce Momjian br...@momjian.us wrote: Peter Eisentraut wrote: On m?n, 2009-11-30 at 07:06 -0500, Bruce Momjian wrote: I thought one problem was that inline is a suggestion that the compiler can ignore, while macros have to be implemented as specified. Sure, but one could argue that a compiler that doesn't support inline usefully is probably not the sort of compiler that you use for compiling performance-relevant software anyway. We can support such systems in a degraded way for historical value and evaluation purposes as long as it's pretty much free, like we support systems without working int8. The issue is that many compilers will take inline as a suggestion and decide if it is worth-while to inline it --- I don't think it is inlined unconditionally by any modern compilers. Right now we think we are better at deciding what should be inlined than the compiler --- of course, we might be wrong, and it would be good to performance test this. 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. I'm not suggesting converting all existing macros to inline. This can happen slowly, and where it brings benefit (short but multi-arg are probably most worthwhile to convert). Also new macros are better done as inlines. About uninlining - usually if the compiler decides to uninline, it is probably right anyway. The prime example would be Linux kernel where the 'inline' is used quite a lot as go-faster-magic-dust on totally random functions. (In .c files, not talking about headers) Most compilers (gcc, vc, icc) support also force-inlining, which is not taken as hint but command. If you want to be on safe side, you could define 'inline' as force-inline on compilers that support that. -- 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] OpenSSL key renegotiation with patched openssl
Tom Lane wrote: Dave Cramer p...@fastcrypt.com writes: Recently openssl has been patched to not renegotiate keys. http://www.links.org/?p=780 After a certain amount of data has gone through a postgresql connection the server will attempt to switch session keys. What is the workaround (if any ) to avoid this in postgresql ? Install the updated openssl library. Why are you bugging us about an openssl patch? regards, tom lane After applying the updated openssl library slony dies, presumably because the server requests a new session key Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Empty dictionary file when creating text search dictionary
Found this a couple of weeks back and just re-tested against head: CREATE TEXT SEARCH DICTIONARY with an empty thesaurus file will crasch the backend. To reproduce: $ echo $(pg_config --sharedir)/tsearch_data/thesaurus_empty.ths Then use this thesaurus to create a text search dictionary: CREATE TEXT SEARCH DICTIONARY thesaurus_astro ( TEMPLATE = thesaurus, DictFile = thesaurus_empty, Dictionary = english_stem ); It doesn't matter if the file is compleatly empty or just have comments. The result is the same. Add a word: $ echo foo: bar $(pg_config --sharedir)/tsearch_data/thesaurus_empty.ths Creating the dictionary will now work. Sorry I have no patch to attach. Regards, roppert PS. I happend to send this mail from a wrong, non-subscribed, address earlier so if it turns up duplicated I apologize. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Initial refactoring of plperl.c [PATCH]
On Sat, Nov 28, 2009 at 09:35:10AM -0500, Andrew Dunstan wrote: Tim Bunce wrote: - Changed MULTIPLICITY check from runtime to compiletime. No loads the large Config module. ISTM the trouble with this is that it assumes that the library that we compile with is the same as the library loaded at runtime. But there is no guarantee of that at all. It only assumes that the library that we compile with has the same 'architecture' (archname) the library loaded at runtime--and that's a fundamental assumption of the libperl binary API. There is no guarantee of binary compatibility in the perl API between multiplicity and non-multiplicity builds. That's clearly indicated by 'multi' being included in the archname. It happens to work at the moment only because certain parts of the API haven't been used yet. For example, the patch I'm working on at the moment adds: PL_exit_flags |= PERL_EXIT_DESTRUCT_END; as part of a fix for bug #5066. PL_exit_flags expands to very different (and binary incompatible) code for a perl built with multiplicity compared to without. (Similarly, another change I'd like to make, given the time, is to enable use of PERL_NO_GET_CONTEXT. That would reduce the juggling of global interpreter variables and eliminate the need for functions like restore_context(). In return it'll eliminate almost all the hidden calls to pthread_getspecific() for perls built with threads enabled.) In summary, changing between multiplicity and non-multiplicity libperls after building postgresql isn't safe or supported. Tim. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Empty dictionary file when creating text search dictionary
=?ISO-8859-1?Q?Robert_Gravsj=F6?= rob...@blogg.se writes: Found this a couple of weeks back and just re-tested against head: CREATE TEXT SEARCH DICTIONARY with an empty thesaurus file will crasch the backend. Fixed, thanks for the report! 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] OpenSSL key renegotiation with patched openssl
On Fri, Nov 27, 2009 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Tom Lane wrote: The discussion I saw suggested that you need such a patch at both ends. and likely requires a restart of both postgresql and slony afterwards... Actually, after looking through the available info about this: https://svn.resiprocate.org/rep/ietf-drafts/ekr/draft-rescorla-tls-renegotiate.txt I think my comment above is wrong. It is useful to patch the *server*-side library to reject a renegotiation request. Applying that patch on the client side, however, is useless and simply breaks things. regards, tom lane I've looked at the available patches for openssl, and so far can only see that ssl3_renegotiate returns 0 if a renegotiation is requested, which would cause pg to throw an error. Is there another patch that fixes this ? I would have expected openssl to simply ignore this request if renegotiation is removed from the library ? Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
On Nov 30, 2009, at 10:32 AM, Stefan Kaltenbrunner wrote: Andres Freund wrote: On Monday 30 November 2009 03:57:11 Itagaki Takahiro wrote: Boszormenyi Zoltan z...@cybertec.at wrote: we tried to discuss on a lower level what should be needed for a partial replication based on streaming replication. We need to discuss a partial recovery before the partial replication. If you do the filtering on the sending side you dont actually need partial recover in the sense that you filter in the rmgr or similar. Or do I miss something? the question is if filtering on the sending side is actually the right thing to do. It increases the overhead and the complexity on the master, especially if you think about different (partial) replication agreements for different slaves and it might also be hard to integrate with the planned sync/async modes. On the other hand if you filter on the master you might be able to avoid a lot of network traffic du to filtered wal records. I think for a first step it might make more sense to look into doing the filtering on the receiving side and look into actual integration with SR at a later stage. Stefan hello ... one problem with not-filtering on the master is that you will end up with a lot of complexity if you start adding new tables to a replica because you just cannot add tables as easy as when you are doing stuff on the slave. the procedure seems ways more complex. in addition to that you are sending WAL which has to be discarded anyway. we thought about filtering outside the master a lot but to me it did not sound like good plan. regards, hans -- Cybertec Schönig Schönig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
Just a side note: in addition to its use for partial replication, this might have potential for performance-prioritizing databases or tablespaces. hello ... this is an absolutely non-starter. the WAL is designed to be hyper ordered and hyper critical. once you fuck up order you will end up with a total disaster. WAL has to be applied in perfect order without skipping depending objects and so on. any concept which tries to get around those fundamental law is either broken. hans -- Cybertec Schönig Schönig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent 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
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... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Cost of sort/order by not estimated by the query planner
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/ -- Sent 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?
Magnus Hagander wrote:
2009/11/24 Tom Lane t...@sss.pgh.pa.us:
Magnus Hagander mag...@hagander.net writes:
ISTM that it should be as simple as the attached patch. Seems to work
for me :-) But I'm no autoconf guru, so maybe I missed something?
This patch sort of begs the question what about
enable-thread-safety-force?
That looks even more like a wart now than it did before.
Agreed. But how about we try it piece-by-piece, which is we start with
this to see if it actually hits any of our bf platforms?
Attached is a complete patch to enable threading of client libraries by
default --- I think it is time (threading was added to PG 7.4 in 2003).
I think we can guarantee that this will turn some build farm members
red. How do we pass --disable-thread-safety to those hosts?
The patch also removes --enable-thread-safety-force, which was added in
2004 for a platform that didn't have a thread-safe getpwuid():
http://archives.postgresql.org/pgsql-hackers/2004-07/msg00485.php
I think we can just tell people they have to upgrade their operating
systems if they want threading on those old platforms (or wait for
complaints).
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: configure
===
RCS file: /cvsroot/pgsql/configure,v
retrieving revision 1.659
diff -c -c -r1.659 configure
*** configure 30 Nov 2009 16:50:37 - 1.659
--- configure 30 Nov 2009 17:12:22 -
***
*** 823,829
enable_depend
enable_cassert
enable_thread_safety
- enable_thread_safety_force
with_tcl
with_tclconfig
with_perl
--- 823,828
***
*** 1497,1505
--enable-dtrace build with DTrace support
--enable-depend turn on automatic dependency tracking
--enable-cassertenable assertion checks (for debugging)
! --enable-thread-safety make client libraries thread-safe
! --enable-thread-safety-force
! force thread-safety despite thread test failure
--disable-float4-byval disable float4 passed by value
--disable-float8-byval disable float8 passed by value
--disable-largefile omit support for large files
--- 1496,1502
--enable-dtrace build with DTrace support
--enable-depend turn on automatic dependency tracking
--enable-cassertenable assertion checks (for debugging)
! --disable-thread-safety make client libraries thread-safe
--disable-float4-byval disable float4 passed by value
--disable-float8-byval disable float8 passed by value
--disable-largefile omit support for large files
***
*** 4859,4892
#
{ $as_echo $as_me:$LINENO: checking allow thread-safe client libraries 5
$as_echo_n checking allow thread-safe client libraries... 6; }
- if test $PORTNAME != win32; then
-
-
- # Check whether --enable-thread-safety was given.
- if test ${enable_thread_safety+set} = set; then
- enableval=$enable_thread_safety;
- case $enableval in
- yes)
- :
- ;;
- no)
- :
- ;;
- *)
- { { $as_echo $as_me:$LINENO: error: no argument expected for --enable-thread-safety option 5
- $as_echo $as_me: error: no argument expected for --enable-thread-safety option 2;}
-{ (exit 1); exit 1; }; }
- ;;
- esac
-
- else
- enable_thread_safety=no
-
- fi
-
-
- else
- # Win32 should always use threads
# Check whether --enable-thread-safety was given.
--- 4856,4861
***
*** 4912,4953
fi
- fi
-
-
-
- # Check whether --enable-thread-safety-force was given.
- if test ${enable_thread_safety_force+set} = set; then
- enableval=$enable_thread_safety_force;
- case $enableval in
- yes)
- :
- ;;
- no)
- :
- ;;
- *)
- { { $as_echo $as_me:$LINENO: error: no argument expected for --enable-thread-safety-force option 5
- $as_echo $as_me: error: no argument expected for --enable-thread-safety-force option 2;}
-{ (exit 1); exit 1; }; }
- ;;
- esac
-
- else
- enable_thread_safety_force=no
-
- fi
-
-
- if test $enable_thread_safety = yes -o \
- $enable_thread_safety_force = yes; then
- enable_thread_safety=yes # for 'force'
-
- cat confdefs.h \_ACEOF
- #define ENABLE_THREAD_SAFETY 1
- _ACEOF
-
- fi
{ $as_echo $as_me:$LINENO: result: $enable_thread_safety 5
$as_echo $enable_thread_safety 6; }
--- 4881,4886
***
*** 21316,21325
if test $PTHREAD_CC != $CC; then
{ { $as_echo $as_me:$LINENO: error:
PostgreSQL does not support platforms that require a special compiler
! for thread safety. 5
$as_echo $as_me: error:
PostgreSQL does not support platforms that require a special compiler
! for thread safety. 2;}
{ (exit 1); exit 1; }; }
fi
---
Re: [HACKERS] [PATCH] Add solaris path for docbook COLLATEINDEX
Zdenek Kotala wrote: collateindex.pl is stored in /usr/share/sgml/docbook/. Attached fix modify docbook.m4 to find correct path. It would be nice also backported the fix back at least to 8.2. I am not happy looking in a directory _above_ a specified directory by default: [$DOCBOOKSTYLE/bin $DOCBOOKSTYLE/.. $PATH]) That seems possibly unsafe. I suggest you just add it to the PATH for Solaris builds. -- 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] is isolation level 'Serializable' in pg not same as 'serializable' in SQL-92?
2009/11/30 张茂森 maosen.zh...@alibaba-inc.com: pgsql-hackers is not the right place for user questions; try pgsql-general or pgsql-novice. The answer to your question is in the documentation. You can find it here: http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY ...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] Initial refactoring of plperl.c [PATCH]
Tim Bunce wrote: In summary, changing between multiplicity and non-multiplicity libperls after building postgresql isn't safe or supported. OK, good. Are you adding a check at load time that the library loaded is what we expect? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] hstore documentation update
Applied. Thanks. --- David E. Wheeler wrote: From: David E. Wheeler da...@justatheory.com As I threatened when I reviewed hstore in the last two commit fests, I've finally seen may way to edit the documentation. This is mostly word-smithing, making sure that all ``s are encoded, making sure that various text is properly tagged with `type` and `literal` tags, plus an extra note or two. I submit this patch for the next CommitFest (though I don't know how much CFing is needed for a pure documenation patch). Best, David --- doc/src/sgml/hstore.sgml | 190 +- 1 files changed, 102 insertions(+), 88 deletions(-) diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml index f237be7..fcff6e3 100644 *** a/doc/src/sgml/hstore.sgml --- b/doc/src/sgml/hstore.sgml *** *** 8,69 /indexterm para ! This module implements a data type typehstore/ for storing sets of ! (key,value) pairs within a single productnamePostgreSQL/ data field. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are ! arbitrary text strings. /para sect2 titletypehstore/ External Representation/title para !The text representation of an typehstore/ value includes zero !or more replaceablekey/ literal=gt;/ replaceablevalue/ !items, separated by commas. For example: programlisting ! k = v ! foo = bar, baz = whatever ! 1-a = anything at all /programlisting !The order of the items is not considered significant (and may not be !reproduced on output). Whitespace between items or around the !literal=gt;/ sign is ignored. Use double quotes if a key or !value includes whitespace, comma, literal=/ or literalgt;/. !To include a double quote or a backslash in a key or value, precede !it with another backslash. /para para !A value (but not a key) can be a SQL NULL. This is represented as programlisting ! key = NULL /programlisting !The literalNULL/ keyword is not case-sensitive. Again, use !double quotes if you want the string literalnull/ to be treated !as an ordinary data value. /para note para !Keep in mind that the above format, when used to input hstore values, !applies emphasisbefore/ any required quoting or escaping. If you !are passing an hstore literal via a parameter, then no additional !processing is needed. If you are passing it as a quoted literal !constant, then any single-quote characters and (depending on the !setting of varnamestandard_conforming_strings/) backslash characters !need to be escaped correctly. See xref linkend=sql-syntax-strings. /para /note para !Double quotes are always used to surround key and value !strings on output, even when this is not strictly necessary. /para /sect2 --- 8,83 /indexterm para ! This module implements the typehstore/ data type for storing sets of ! key/value pairs within a single productnamePostgreSQL/ value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are ! simply text strings. /para sect2 titletypehstore/ External Representation/title para ! !The text representation of an typehstore/, used for input and output, !includes zero or more replaceablekey/ literal=gt;/ !replaceablevalue/ pairs separated by commas. Some examples: programlisting ! k =gt; v ! foo =gt; bar, baz =gt; whatever ! 1-a =gt; anything at all /programlisting !The order of the pairs is not significant (and may not be reproduced on !output). Whitespace between pairs or around the literal=gt;/ sign is !ignored. Double-quote keys and values that include whitespace, commas, !literal=/s or literalgt;/s. To include a double quote or a !backslash in a key or value, escape it with a backslash. /para para !Each key in an typehstore/ is unique. If you declare an typehstore/ !with duplicate keys, only one will be stored in the typehstore/ and !there is no guarantee as to which will be kept: programlisting ! % select 'a=gt;1,a=gt;2'::hstore; ! hstore ! -- ! a=gt;1 /programlisting + /para ! para !A value (but not a key) can be an SQL literalNULL/. For example: ! !programlisting ! key =gt; NULL !/programlisting ! !The literalNULL/ keyword is case-insensitive. Double-quote the !literalNULL/ to treat it as the ordinary string NULL. /para note para
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
On Monday 30 November 2009 17:46:45 Hans-Jürgen Schönig wrote: On Nov 30, 2009, at 10:32 AM, Stefan Kaltenbrunner wrote: the question is if filtering on the sending side is actually the right thing to do. It increases the overhead and the complexity on the master, especially if you think about different (partial) replication agreements for different slaves and it might also be hard to integrate with the planned sync/async modes. On the other hand if you filter on the master you might be able to avoid a lot of network traffic du to filtered wal records. I think for a first step it might make more sense to look into doing the filtering on the receiving side and look into actual integration with SR at a later stage. one problem with not-filtering on the master is that you will end up with a lot of complexity if you start adding new tables to a replica because you just cannot add tables as easy as when you are doing stuff on the slave. the procedure seems ways more complex. in addition to that you are sending WAL which has to be discarded anyway. we thought about filtering outside the master a lot but to me it did not sound like good plan. One possibility for the far future would be to allow filtering on a slave as well: master full replication --- primary slave --- split --- slaves Possibly doing only catalog recovery on the primary slave. In my opinion thats heaps more complex and not better in all situation. So I would probably write it down as a nice idea but not more. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] hstore documentation update
On Dec 1, 2009, at 2:56 AM, Bruce Momjian wrote: Applied. Thanks. Thanks, I'll remove it from the next CF list, then. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] hstore documentation update
On Dec 1, 2009, at 3:01 AM, David E. Wheeler wrote: On Dec 1, 2009, at 2:56 AM, Bruce Momjian wrote: Applied. Thanks. Thanks, I'll remove it from the next CF list, then. Oh, you already marked it as committed. Thanks! David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A thought about regex versus multibyte character sets
We've had many complaints about the fact that the regex functions are not bright about locale-dependent operations in multibyte character sets, especially case-insensitive matching. The reason for this, as was discussed in this thread http://archives.postgresql.org/pgsql-hackers/2008-12/msg00433.php is that we'd need to use the wctype.h functions, but those expect the platform's wchar_t representation, whereas the regex stuff works on pg_wchar_t which might have a different character set mapping. I just spent a bit of time considering what we might do to fix this. The idea mentioned in the above thread was to switch over to using wchar_t in the regex code, but that seems to have a number of problems. One showstopper is that on some platforms wchar_t is only 16 bits and can't represent the full range of Unicode characters. I don't want to fix case-folding only to break regexes for other uses. However, it strikes me that we might be overstating the size of the mismatch between wchar_t and pg_wchar_t representations. In particular, for Unicode-based locales it seems virtually certain that every platform would use Unicode code points for the wchar_t representation, and that is also our representation in pg_wchar_t. I therefore propose the following idea: if the database encoding is UTF8, allow the regc_locale.c functions to call the wctype.h functions, assuming that wchar_t and pg_wchar_t share the same representation. On platforms where wchar_t is only 16 bits, we can do this up to U+ and be stupid about code points above that. I think this will solve at least 99% of the problem for a fairly small amount of work. It does not do anything for non-UTF8 multibyte encodings, but so far as I can see the only such encodings are Far Eastern ones, in which the present ASCII-only behavior is probably good enough --- concepts like case don't apply to their non-ASCII characters anyhow. (Well, there's also MULE_INTERNAL, but I don't believe anyone runs their DB in that.) However, not being a native user of any non-ASCII character set, I might be missing something big here. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On Mon, 2009-11-30 at 13:21 +, Simon Riggs wrote: On Fri, 2008-10-17 at 12:26 -0300, Alvaro Herrera wrote: So this discussion died with no solution arising to the hint-bit-setting-invalidates-the-CRC problem. Apparently the only solution in sight is to WAL-log hint bits. Simon opines it would be horrible from a performance standpoint to WAL-log every hint bit set, and I think we all agree with that. So we need to find an alternative mechanism to WAL log hint bits. It occurred to me that maybe we don't need to WAL-log the CRC checks. Proposal * We reserve enough space on a disk block for a CRC check. When a dirty block is written to disk we calculate and annotate the CRC value, though this is *not* WAL logged. * In normal running we re-check the CRC when we read the block back into shared_buffers. * In recovery we will overwrite the last image of a block from WAL, so we ignore the block CRC check, since the WAL record was already CRC checked. If full_page_writes = off, we ignore and zero the block's CRC for any block touched during recovery. We do those things because the block CRC in the WAL is likely to be different to that on disk, due to hints. * We also re-check the CRC on a block immediately before we dirty the block (for any reason). This minimises the possibility of in-memory data corruption for blocks. So in the typical case all blocks moving from disk - memory and from clean - dirty are CRC checked. So in the case where we have full_page_writes = on then we have a good CRC every time. In the full_page_writes = off case we are exposed only on the blocks that changed during last checkpoint cycle and only if we crash. That seems good because most databases are up 99% of the time, so any corruptions are likely to occur in normal running, not as a result of crashes. This would be a run-time option. Like it? Just FYI, Alvaro is out of town and our of email access (almost exclusively). It may take him another week or so to get back to this. Joshua D. Drake -- Simon Riggs www.2ndQuadrant.com -- 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] Deleted WAL files held open by backends in Linux
Tom Lane t...@sss.pgh.pa.us wrote: It seemed strange that the only backends which were holding open deleted WAL files were ones where the connection was established with a login which has no write permissions. A backend would never open a WAL file unless it had to write a WAL record, so I'm having a hard time believing that these were totally read-only transactions. Can you give specifics? Here's some information captured before the original post: MILWAUKEE-PG:~ # lsof | grep deleted COMMAND PIDUSER FD TYPE DEVICE SIZE NODE NAME postgres 13105ccsa 10u REG8,4 16777216 268442394 /var/pgsql/data/cc/pg_xlog/000100E000BB (deleted) postgres 30500ccsa4u REG8,4 16777216 268442626 /var/pgsql/data/cc/pg_xlog/000100E2003F (deleted) postgres 30501ccsa5u REG8,4 16777216 283509014 /var/pgsql/data/cc/pg_xlog/000100E20016 (deleted) MILWAUKEE-PG:~ # ps auxf|grep ^ccsa USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND ccsa 30490 0.0 0.0 290988 8608 ?SOct24 0:13 /usr/local/pgsql-8.3.7/bin/postgres -D /var/pgsql/data/cc ccsa 30491 0.0 0.0 14524 1056 ?Ss Oct24 0:10 \_ postgres: logger process ccsa 30493 0.0 1.0 291876 268760 ? Ss Oct24 1:36 \_ postgres: writer process ccsa 30494 0.0 0.0 291120 1452 ?Ss Oct24 0:01 \_ postgres: wal writer process ccsa 30495 0.0 0.0 291552 1564 ?Ss Oct24 0:00 \_ postgres: autovacuum launcher process ccsa 30496 0.0 0.0 14612 952 ?Ss Oct24 0:01 \_ postgres: archiver process last was 000100E2005B ccsa 30497 0.0 0.0 15280 1576 ?Ss Oct24 6:37 \_ postgres: stats collector process ccsa 30500 0.0 0.9 292448 231844 ? Ss Oct24 10:42 \_ postgres: viewer cc 127.0.0.1(36846) idle ccsa 30501 0.0 0.8 292496 209428 ? Ss Oct24 10:28 \_ postgres: viewer cc 165.219.78.11(53940) idle ccsa 3107 0.0 0.0 292508 5836 ?Ss Nov09 0:32 \_ postgres: cc cc 127.0.0.1(40442) idle ccsa 3113 0.0 1.1 305304 286280 ? Ss Nov09 6:51 \_ postgres: cc cc 127.0.0.1(49969) idle ccsa 3119 0.0 0.0 292508 5836 ?Ss Nov09 0:29 \_ postgres: cc cc 127.0.0.1(54127) idle ccsa 13105 0.0 0.0 293396 23852 ?Ss Nov23 0:08 \_ postgres: viewer cc 165.219.90.178(51481) idle ccsa 1485 0.8 1.1 312400 291508 ? Ss Nov24 14:18 \_ postgres: cc cc 127.0.0.1(42692) idle ccsa 10752 0.3 1.1 312712 290856 ? Ss Nov24 4:54 \_ postgres: cc cc 127.0.0.1(45119) idle ccsa 10908 1.3 1.1 313120 292836 ? Ss Nov24 20:03 \_ postgres: cc cc 127.0.0.1(42065) idle ccsa 25099 0.7 1.1 312864 285048 ? Ss 14:02 1:03 \_ postgres: cc cc 127.0.0.1(50361) idle ccsa 26571 0.0 0.3 298912 89528 ?Ss 14:29 0:06 \_ postgres: cc cc 127.0.0.1(52213) idle ccsa 26809 0.1 0.3 298940 88816 ?Ss 14:33 0:06 \_ postgres: cc cc 127.0.0.1(59145) idle ccsa 26812 0.6 1.1 310060 274192 ? Ss 14:33 0:44 \_ postgres: cc cc 127.0.0.1(59962) idle MILWAUKEE-PG:~ # ll /var/pgsql/data/cc/pg_xlog/ total 1671172 -rw--- 1 ccsa users 253 2009-11-20 21:29 000100EA.1810.backup -rw--- 1 ccsa users 16777216 2009-11-25 16:10 000100E2005B -rw--- 1 ccsa users 16777216 2009-11-25 16:22 000100E2005C -rw--- 1 ccsa users 16777216 2009-11-25 04:15 000100E2005D -rw--- 1 ccsa users 16777216 2009-11-25 05:15 000100E2005E -rw--- 1 ccsa users 16777216 2009-11-25 06:15 000100E2005F -rw--- 1 ccsa users 16777216 2009-11-25 07:15 000100E20060 -rw--- 1 ccsa users 16777216 2009-11-25 07:42 000100E20061 [...] -rw--- 1 ccsa users 16777216 2009-11-25 15:34 000100E200BB -rw--- 1 ccsa users 16777216 2009-11-25 15:37 000100E200BC -rw--- 1 ccsa users 16777216 2009-11-25 15:44 000100E200BD -rw--- 1 ccsa users 16777216 2009-11-25 15:50 000100E200BE -rw--- 1 ccsa users 16777216 2009-11-25 15:57 000100E200BF -rw--- 1 ccsa users 16777216 2009-11-25 16:04 000100E200C0 drwx-- 2 ccsa users 94 2009-11-25 16:12 archive_status You will note that the connections logged in as viewer (and only those) are holding open a deleted WAL file. This user has not been granted anything except SELECT permissions to any tables. In addition, immediately after creating the database, we ran: REVOKE CREATE ON DATABASE cc FROM public; REVOKE CREATE ON SCHEMA public FROM public; Two of these connections are from software publishers, which only issue SELECT statements against three tables (to which they only have SELECT permission). The other is
[HACKERS] lexeme ordering in tsvector
It seems like the ordering of lexemes in tsvector has changed from 8.3
to 8.4.
For example in 8.3.1,
postgres=# select to_tsvector('english', 'quit everytime');
to_tsvector
---
'quit':1 'everytim':2
The lexemes are arranged by length and then by string comparison.
In postgres 8.4.1,
select to_tsvector('english', 'quit everytime');
to_tsvector
---
'everytim':2 'quit':1
they are arranged by strncmp and then by length.
I looked in tsvector_op.c, in the function tsCompareString, first memcmp
and then length comparison is done.
Was this change in ordering deliberate?
Wouldn't length comparison be cheaper than memcmp?
-Sushant.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lexeme ordering in tsvector
Sushant Sinha sushant...@gmail.com writes: Was this change in ordering deliberate? Yes. Wouldn't length comparison be cheaper than memcmp? It's not just about cheapest anymore, it also has to support prefix operations. 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] Writeable CTE patch
Tom Lane wrote: 1. I thought we'd agreed at http://archives.postgresql.org/pgsql-hackers/2009-10/msg00558.php that the patch should support WITH on DML statements, eg with (some-query) insert into foo ... This might not take much more than grammar additions, but it's definitely lacking at the moment. Ok, I added these. One thing that really does have to draw an error is that AFAIR the current rule feature doesn't enforce that a rewritten query produce the same type of output that the original would have. We just ship off whatever the results are to the client, and let it sort everything out. In a DML WITH query, though, I think we do have to insist that the rewritten query(s) still produce the same RETURNING rowtype as before. Agreed. 3. I'm pretty unimpressed with the code added to ExecutePlan. It knows way more than it ought to about CTEs, and yet I don't think it's doing the right things anyway --- in particular, won't it run the leader CTE more than once if one CTE references another? Yes. Are you suggesting something more intelligent to avoid scanning the CTE more than once or..? I think it would be better if the PlannedStmt representation just told ExecutePlan what to do, rather than having all these heuristics inside ExecutePlan. Yup, seems like a better choice. (BTW, I also think it would work better if you had the CommandCounterIncrement at the bottom of the loop, after the subquery execution not before it. But I'm not sure it's safe for ExecutePlan to be modifying the snapshot it's handed anyway.) Agreed. I'm a bit lost here with the snapshot business; is doing this work in ExecutePlan() out of the question or is it just that what I'm doing is wrong? 4. As previously noted, the changes to avoid using es_result_relation_info are broken and need to be dropped from the patch. Done. I kept the logic for result relations to allow nested ModifyTable nodes, but I don't think it ever did the right thing with EvalPlanQual() and nested nodes. I'll have think about that. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deleted WAL files held open by backends in Linux
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: A backend would never open a WAL file unless it had to write a WAL record, so I'm having a hard time believing that these were totally read-only transactions. Can you give specifics? You will note that the connections logged in as viewer (and only those) are holding open a deleted WAL file. This user has not been granted anything except SELECT permissions to any tables. You sure it's not creating any temp tables? You didn't mention revoking TEMP privilege. I can think of one code path that could result in a genuinely read-only session having to write WAL: if it's forced to flush dirty buffers in order to read in other pages, and such a buffer was dirtied by as-yet-uncommitted transactions, it might have to flush WAL to be allowed to write the dirty buffer. But I think you'd have had to dial back the bgwriter to the point of uselessness before this would be a common occurrence. At a minimum, we should add the extra 16MB per connection that might be taken on the WAL file system to the calculations people should do when sizing that, just in case someone is trying to cut that thin while planning on using a lot of connections. In the first place, this is a complete non-issue except on Windows --- on other platforms we can rename and recycle the files even if they're being held open. I rather doubt anyone would think they could predict a Windows machine's disk usage that accurately anyway. In the second place, for each backend to be holding open a different dead WAL file strains the limits of credulity. Telling people to assume 16MB * max_connections would be a gross overestimate. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add solaris path for docbook COLLATEINDEX
Bruce Momjian píše v po 30. 11. 2009 v 12:32 -0500: Zdenek Kotala wrote: collateindex.pl is stored in /usr/share/sgml/docbook/. Attached fix modify docbook.m4 to find correct path. It would be nice also backported the fix back at least to 8.2. I am not happy looking in a directory _above_ a specified directory by default: [$DOCBOOKSTYLE/bin $DOCBOOKSTYLE/.. $PATH]) That seems possibly unsafe. I suggest you just add it to the PATH for Solaris builds. I'm not sure if it is unsafer that searching in $PATH. Anyway I don't think that your proposed solution is good way. I'm able to do it but how many other people can? Who know where collateindex.pl stays on Solaris? configure is here to make live easier for people. If we know how to do it automatically we should do it. If you think that $DOCBOOKSTYLE/.. is not good than I propose to use /usr/share/sgml/docbook/ directly. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Writeable CTE patch
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: Tom Lane wrote: (BTW, I also think it would work better if you had the CommandCounterIncrement at the bottom of the loop, after the subquery execution not before it. But I'm not sure it's safe for ExecutePlan to be modifying the snapshot it's handed anyway.) Agreed. I'm a bit lost here with the snapshot business; is doing this work in ExecutePlan() out of the question or is it just that what I'm doing is wrong? I think it's not a good idea for ExecutePlan to be scribbling on the executor's input, and the provided snapshot is definitely an input. It might accidentally fail to fail in the present system, but it would always be a hazard. The only thing that I'd be comfortable with is copying the snap and modifying the copy. This might be okay from a performance standpoint if it's done at the bottom of the loop (ie, only when you actually have at least one writable CTE). It would be altogether cleaner though if the CommandCounterIncrement responsibility were in the same place it is now, ie the caller of the executor. Which could be possible if we restructure the rewriter/planner output as a list of Queries instead of just one. I'm not currently sure how hard that would be, though; it might not be a practical answer. 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] Writeable CTE patch
Tom Lane wrote: It would be altogether cleaner though if the CommandCounterIncrement responsibility were in the same place it is now, ie the caller of the executor. Which could be possible if we restructure the rewriter/planner output as a list of Queries instead of just one. I'm not currently sure how hard that would be, though; it might not be a practical answer. I'm trying to avoid doing this, at least for now. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add solaris path for docbook COLLATEINDEX
On mån, 2009-11-30 at 19:53 +0100, Zdenek Kotala wrote: Bruce Momjian píše v po 30. 11. 2009 v 12:32 -0500: I am not happy looking in a directory _above_ a specified directory by default: [$DOCBOOKSTYLE/bin $DOCBOOKSTYLE/.. $PATH]) That seems possibly unsafe. I suggest you just add it to the PATH for Solaris builds. I'm not sure if it is unsafer that searching in $PATH. Anyway I don't think that your proposed solution is good way. I'm able to do it but how many other people can? Who know where collateindex.pl stays on Solaris? configure is here to make live easier for people. If we know how to do it automatically we should do it. Note that $DOCBOOKSTYLE/bin corresponds to the location of the file in an unpacked source archive and $PATH is of course where programs normally go. The $DOCBOOKSTYLE/.. location makes no sense at all, because that basically says that the installer intentionally moved the file, but to a completely nonstandard location. If you think that $DOCBOOKSTYLE/.. is not good than I propose to use /usr/share/sgml/docbook/ directly. That would be the less ugly solution. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deleted WAL files held open by backends in Linux
Tom Lane t...@sss.pgh.pa.us wrote: You sure it's not creating any temp tables? You didn't mention revoking TEMP privilege. They have not been revoked, but I am sure the software publisher doesn't explicitly create any, and I'd be very surprised if the monitoring software did. The tables are small enough that it's hard to believe that the 50MB work_mem would spill to disk, either (if that matters). I can think of one code path that could result in a genuinely read-only session having to write WAL: if it's forced to flush dirty buffers in order to read in other pages, and such a buffer was dirtied by as-yet-uncommitted transactions, it might have to flush WAL to be allowed to write the dirty buffer. But I think you'd have had to dial back the bgwriter to the point of uselessness before this would be a common occurrence. #bgwriter_delay = 200ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 4.0 At a minimum, we should add the extra 16MB per connection that might be taken on the WAL file system to the calculations people should do when sizing that, just in case someone is trying to cut that thin while planning on using a lot of connections. In the first place, this is a complete non-issue except on Windows --- on other platforms we can rename and recycle the files even if they're being held open. I rather doubt anyone would think they could predict a Windows machine's disk usage that accurately anyway. In the second place, for each backend to be holding open a different dead WAL file strains the limits of credulity. Telling people to assume 16MB * max_connections would be a gross overestimate. Pretty much every read only JDBC connection seems to be holding open a deleted WAL file on my Linux box, but it would take pretty pessimal timing for each connection to be holding open a different one -- I see that many connections share a deleted WAL file. project-db:~ # cat /proc/version Linux version 2.6.16.60-0.39.3-smp (ge...@buildhost) (gcc version 4.1.2 20070115 (SUSE Linux)) #1 SMP Mon May 11 11:46:34 UTC 2009 project-db:~ # lsof | grep deleted postgres 2189 ccefcirsa 20u REG8,3 16777216 1610613340 /var/pgsql/data/ccefcir/pg_xlog/0001000A0010 (deleted) postgres 2195 ccefcirsa 43u REG8,3 16777216 1610613340 /var/pgsql/data/ccefcir/pg_xlog/0001000A0010 (deleted) postgres 2511 jdashcirsa1u CHR 136,5 7 /dev/pts/5 (deleted) postgres 2511 jdashcirsa2u CHR 136,5 7 /dev/pts/5 (deleted) postgres 2514 jdashcirsa3u REG8,3 16777216 1610631538 /var/pgsql/data/jdashcir/pg_xlog/000100110076 (deleted) postgres 2812 ccsa3u REG8,3 16777216 3763808807 /var/pgsql/data/epayment/pg_xlog/0002009B0094 (deleted) postgres 3647 ccefsa1u CHR 136,5 7 /dev/pts/5 (deleted) postgres 3647 ccefsa2u CHR 136,5 7 /dev/pts/5 (deleted) postgres 3650 ccefsa3u REG8,3 16777216 669441097 /var/pgsql/data/ccef/pg_xlog/0002004B00C5 (deleted) postgres 4266 milwaukeeifsa3u REG8,3 16777216 2708846433 /var/pgsql/data/milwaukeeif/pg_xlog/0002006B0014 (deleted) postgres 4270 juryscansa3u REG8,3 16777216 1073742165 /var/pgsql/data/juryscan/pg_xlog/0002001A00DB (deleted) postgres 6100 juryscansa 41u REG8,3 16777216 1073742165 /var/pgsql/data/juryscan/pg_xlog/0002001A00DB (deleted) postgres 6105 juryscansa 35u REG8,3 16777216 1073742167 /var/pgsql/data/juryscan/pg_xlog/0002001A007E (deleted) postgres 6113 juryscansa 43u REG8,3 16777216 1073742166 /var/pgsql/data/juryscan/pg_xlog/0002001A001D (deleted) postgres 6119 juryscansa 59u REG8,3 16777216 1073742165 /var/pgsql/data/juryscan/pg_xlog/0002001A00DB (deleted) postgres 6121 juryscansa 62u REG8,3 16777216 1073742165 /var/pgsql/data/juryscan/pg_xlog/0002001A00DB (deleted) postgres 6126 juryscansa 33u REG8,3 16777216 1073742167 /var/pgsql/data/juryscan/pg_xlog/0002001A007E (deleted) postgres 7997 washrptcirsa 13u REG8,3 16777216 2688967689 /var/pgsql/data/washrptcir/pg_xlog/0001002B00E4 (deleted) postgres 8170 washrptcirsa 23u
Re: [HACKERS] Deleted WAL files held open by backends in Linux
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: You sure it's not creating any temp tables? You didn't mention revoking TEMP privilege. They have not been revoked, but I am sure the software publisher doesn't explicitly create any, and I'd be very surprised if the monitoring software did. The tables are small enough that it's hard to believe that the 50MB work_mem would spill to disk, either (if that matters). It's not about the size of a temp table, because writes to the temp table itself aren't WAL-logged. However, the system catalog entries for a temp table *are* WAL-logged. Pretty much every read only JDBC connection seems to be holding open a deleted WAL file on my Linux box, but it would take pretty pessimal timing for each connection to be holding open a different one -- I see that many connections share a deleted WAL file. This still seems a bit improbable to me. There has to be something causing those sessions to touch WAL, and the dirty-buffer scenario doesn't seem reliable enough. [ thinks... ] How about SELECT FOR UPDATE or SELECT FOR SHARE? Those cause WAL writes. 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] Deleted WAL files held open by backends in Linux
Tom Lane t...@sss.pgh.pa.us wrote: It's not about the size of a temp table, because writes to the temp table itself aren't WAL-logged. However, the system catalog entries for a temp table *are* WAL-logged. Definitely not issuing any CREATE TEMP statements of any kind, unless the JDBC driver is doing that under the covers. Pretty much every read only JDBC connection seems to be holding open a deleted WAL file on my Linux box, but it would take pretty pessimal timing for each connection to be holding open a different one -- I see that many connections share a deleted WAL file. This still seems a bit improbable to me. There has to be something causing those sessions to touch WAL, and the dirty-buffer scenario doesn't seem reliable enough. [ thinks... ] How about SELECT FOR UPDATE or SELECT FOR SHARE? Those cause WAL writes. Definitely not. Probably best not to worry about it until I can play around with some Java test code to see what it takes to cause the connection to open the WAL. I'll post when I've had a chance to try that. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add solaris path for docbook COLLATEINDEX
Peter Eisentraut píše v po 30. 11. 2009 v 21:27 +0200: On mån, 2009-11-30 at 19:53 +0100, Zdenek Kotala wrote: Bruce Momjian píše v po 30. 11. 2009 v 12:32 -0500: I am not happy looking in a directory _above_ a specified directory by default: [$DOCBOOKSTYLE/bin $DOCBOOKSTYLE/.. $PATH]) That seems possibly unsafe. I suggest you just add it to the PATH for Solaris builds. I'm not sure if it is unsafer that searching in $PATH. Anyway I don't think that your proposed solution is good way. I'm able to do it but how many other people can? Who know where collateindex.pl stays on Solaris? configure is here to make live easier for people. If we know how to do it automatically we should do it. Note that $DOCBOOKSTYLE/bin corresponds to the location of the file in an unpacked source archive and $PATH is of course where programs normally go. The $DOCBOOKSTYLE/.. location makes no sense at all, because that basically says that the installer intentionally moved the file, but to a completely nonstandard location. I'm not sgml//docbook guru. Do you think that Solaris location of collateindex.pl is wrong? Does exist any recommendation for this? I could log a bug, but I need some link with recommendation. If you think that $DOCBOOKSTYLE/.. is not good than I propose to use /usr/share/sgml/docbook/ directly. That would be the less ugly solution. Patch attached. thanks Zdenek diff -r 2d87758e836b config/docbook.m4 --- a/config/docbook.m4 Sun Nov 22 22:06:30 2009 + +++ b/config/docbook.m4 Mon Nov 30 20:55:15 2009 +0100 @@ -93,7 +93,7 @@ [AC_REQUIRE([PGAC_PATH_DOCBOOK_STYLESHEETS])dnl if test -n $DOCBOOKSTYLE; then AC_PATH_PROGS(COLLATEINDEX, collateindex.pl, [], -[$DOCBOOKSTYLE/bin $PATH]) +[$DOCBOOKSTYLE/bin /usr/share/sgml/docbook $PATH]) else AC_PATH_PROGS(COLLATEINDEX, collateindex.pl) fi])# PGAC_PATH_COLLATEINDEX -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs chapters in our docs
Chris Browne wrote: Wikis have a habit of getting out of date in ways that make them even more difficult to rectify, because the data is frequently structured in a way that doesn't make it particularly easy to pull it out and transform it into other forms. The standard way to backup a Mediawiki install is to export to XML: http://meta.wikimedia.org/wiki/Help:Export At which point you can transform it as easily as any other structured document and then re-import. Given that the pages on the PostgreSQL wiki about CVS and Git have been the most up to date resources on those topics available since shortly after their respective creation dates, I'm not sure what one could criticize about them as an information source in this area. -- 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] New VACUUM FULL
Itagaki Takahiro wrote: Done. (vacuum-full_20091130.patch) Is this ready for a committer now? Not sure whether Jeff intends to re-review here or not, given that the suggestions and their fixes were pretty straightforward. It looks pretty solid at this point to me. -- 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] Initial refactoring of plperl.c [PATCH]
On Mon, Nov 30, 2009 at 12:50:41PM -0500, Andrew Dunstan wrote: Tim Bunce wrote: In summary, changing between multiplicity and non-multiplicity libperls after building postgresql isn't safe or supported. OK, good. Are you adding a check at load time that the library loaded is what we expect? I won't think there's a need. The load will fail with undefined symbols (or rather it will once the PL_exit_flags |= PERL_EXIT_DESTRUCT_END change in the next patch gets added). Tim. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
Jeff Davis wrote: COPY target FROM FUNCTION foo() WITH RECORDS; In what format would the records be? What was your intended internal format for this form to process? -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com
Re: [HACKERS] Block-level CRC checks
Simon Riggs wrote: Proposal * We reserve enough space on a disk block for a CRC check. When a dirty block is written to disk we calculate and annotate the CRC value, though this is *not* WAL logged. Imagine this: 1. A hint bit is set. It is not WAL-logged, but the page is dirtied. 2. The buffer is flushed out of the buffer cache to the OS. A new CRC is calculated and stored on the page. 3. Half of the page is flushed to disk (aka torn page problem). The CRC made it to disk but the flipped hint bit didn't. You now have a page with incorrect CRC on disk. -- 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] Application name patch - v4
Le 30 nov. 2009 à 00:25, Tom Lane a écrit : The thing is that the libpq API treats application_name as a *property of the connection*. Oh. Yeah. We could add a third keyword, say SET DEFAULT, that would have the behavior of setting the value in a fashion that would persist across resets. I'm not sure that DEFAULT is exactly le mot juste here, but agreeing on a keyword would probably be the hardest part of making it happen. I vaguely remember you explaining how hard it would be to be able to predict the value we RESET to as soon as we add this or that possibility. That's very vague, sorry, but only leaves a bad impression on the keyword choice (bikeshedding, I should open a club). So what about SET CONNECTION application_name TO 'whatever'? 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] Block-level CRC checks
On Mon, 2009-11-30 at 22:27 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Proposal * We reserve enough space on a disk block for a CRC check. When a dirty block is written to disk we calculate and annotate the CRC value, though this is *not* WAL logged. Imagine this: 1. A hint bit is set. It is not WAL-logged, but the page is dirtied. 2. The buffer is flushed out of the buffer cache to the OS. A new CRC is calculated and stored on the page. 3. Half of the page is flushed to disk (aka torn page problem). The CRC made it to disk but the flipped hint bit didn't. You now have a page with incorrect CRC on disk. You've written that as if you are spotting a problem. It sounds to me that this is exactly the situation we would like to detect and this is a perfect way of doing that. What do you see is the purpose here apart from spotting corruptions? Do we think error rates are so low we can recover the corruption by doing something clever with the CRC? I envisage most corruptions as being unrecoverable except from backup/WAL/replicated servers. It's been a long day, so perhaps I've misunderstood. -- 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] OpenSSL key renegotiation with patched openssl
2009/11/27 Tom Lane t...@sss.pgh.pa.us: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Tom Lane wrote: The discussion I saw suggested that you need such a patch at both ends. and likely requires a restart of both postgresql and slony afterwards... Actually, after looking through the available info about this: https://svn.resiprocate.org/rep/ietf-drafts/ekr/draft-rescorla-tls-renegotiate.txt I think my comment above is wrong. It is useful to patch the *server*-side library to reject a renegotiation request. Applying that patch on the client side, however, is useless and simply breaks things. I haven't looked into the details but - is there a point for us to remove the requests for renegotiation completely? Will this help those that *haven't* upgraded their openssl library? I realize it's not necessarily our bug to fix, but if we can help.. :) If a patched version of openssl ignores the renegotiation anyway, there's nothing lost if we turn it off in postgresql, is there? -- 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] Deleted WAL files held open by backends in Linux
Tom Lane t...@sss.pgh.pa.us wrote: There has to be something causing those sessions to touch WAL, and the dirty-buffer scenario doesn't seem reliable enough. This is seeming fairly likely to be the cause, though. It may be a combination of the nightly VACUUM FREEZE ANALYZE we typically do on every database and the monitoring software, which would directly run a couple queries each five seconds or so, and would also exercise the software publishers on a similar schedule. My smoking gun is that the monitoring software was restarted last night after the time that the nightly vacuums would have completed, and its connections are not showing as having a deleted WAL file open today. I was unable to directly cause the a backend to open a WAL file by connecting through JDBC and running the same kind of queries used by the software publisher, which does tend to point to some indirect mechanism, like the dirty buffer flushing. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Application name patch - v4
On Mon, Nov 30, 2009 at 4:11 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Le 30 nov. 2009 à 00:25, Tom Lane a écrit : The thing is that the libpq API treats application_name as a *property of the connection*. Oh. Yeah. We could add a third keyword, say SET DEFAULT, that would have the behavior of setting the value in a fashion that would persist across resets. I'm not sure that DEFAULT is exactly le mot juste here, but agreeing on a keyword would probably be the hardest part of making it happen. I vaguely remember you explaining how hard it would be to be able to predict the value we RESET to as soon as we add this or that possibility. That's very vague, sorry, but only leaves a bad impression on the keyword choice (bikeshedding, I should open a club). So what about SET CONNECTION application_name TO 'whatever'? I still don't really understand why we wouldn't want RESET ALL to reset the application name. In what circumstances would you want the application name to stay the same across a RESET ALL? ...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] OpenSSL key renegotiation with patched openssl
Magnus Hagander mag...@hagander.net writes: I haven't looked into the details but - is there a point for us to remove the requests for renegotiation completely? The periodic renegotiations are a recommended security measure. Fixing one hole by introducing a different attack vector doesn't seem to me to be an improvement. Also, when would we undo it? At least with the current situation, there is an incentive for people to get a corrected version of openssl as soon as possible (not patched, since what this patch does is break essential functionality; but actually fixed). 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] New VACUUM FULL
On Mon, 2009-11-30 at 15:10 -0500, Greg Smith wrote: Itagaki Takahiro wrote: Done. (vacuum-full_20091130.patch) Is this ready for a committer now? Not sure whether Jeff intends to re-review here or not, given that the suggestions and their fixes were pretty straightforward. It looks pretty solid at this point to me. The code is in good shape. I was going to take another stab at the documentation (which needs some rewording after the changes), and maybe look at vacuumdb again, as well. Nothing major, so expect it to be ready for committer tonight. Of course, a committer can take a look at it sooner, if they have time. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] draft RFC: concept for partial, wal-based replication
On 30/11/2009 11:07 PM, Tom Lane wrote: Craig Ringercr...@postnewspapers.com.au writes: Just a side note: in addition to its use for partial replication, this might have potential for performance-prioritizing databases or tablespaces. Being able to separate WAL logging so that different DBs, tablespaces, etc went to different sets of WAL logs would allow a DBA to give some databases or tablespaces dedicated WAL logging space on faster storage. I don't think this can possibly work without introducing data corruption issues. What happens when a transaction touches tables in different tablespaces? You can't apply the changes out-of-order. Argh, good point, and one that should've been blindingly obvious. At a database level something like that may still be handy, though I haven't the foggiest how one would handle the shared system catalogs. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Simon Riggs si...@2ndquadrant.com [091130 16:28]: You've written that as if you are spotting a problem. It sounds to me that this is exactly the situation we would like to detect and this is a perfect way of doing that. What do you see is the purpose here apart from spotting corruptions? Do we think error rates are so low we can recover the corruption by doing something clever with the CRC? I envisage most corruptions as being unrecoverable except from backup/WAL/replicated servers. It's been a long day, so perhaps I've misunderstood. No, I believe the torn-page problem is exactly the thing that made the checksum talks stall out last time... The torn page isn't currently a problem on only-hint-bit-dirty writes, because if you get half-old/half-new, the only changes is the hint bit - no big loss, the data is still the same. But, with a form of check-sums, when you read it it next time, is it corrupt? According to the check-sum, yes, but in reality, the *data* is still valid, just that the check sum is/isn't correctly matching the half-changed hint bits... And then many not-so-really-attractive workarounds where thrown around, with nothing nice falling into place... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v4
Le 30 nov. 2009 à 22:38, Robert Haas a écrit : I still don't really understand why we wouldn't want RESET ALL to reset the application name. In what circumstances would you want the application name to stay the same across a RESET ALL? I can't see any use case, but SET/RESET is tied to SESSION whereas application_name is a CONNECTION property. So it's a hard sell that reseting the session will change connection properties. 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] Application name patch - v4
On Mon, Nov 30, 2009 at 4:54 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Le 30 nov. 2009 à 22:38, Robert Haas a écrit : I still don't really understand why we wouldn't want RESET ALL to reset the application name. In what circumstances would you want the application name to stay the same across a RESET ALL? I can't see any use case, but SET/RESET is tied to SESSION whereas application_name is a CONNECTION property. So it's a hard sell that reseting the session will change connection properties. Is there any technical difference between a connection property and a session property? If so, what is it? ISTM that the only time you're likely going to use RESET ALL is in a connection pooling environment, and that if you're in a connection pooling environment you probably want to reset the application name along with everything else. I might be wrong, but that's how it seems to me at first blush. ...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 Mon, 2009-11-30 at 16:49 -0500, Aidan Van Dyk wrote: * Simon Riggs si...@2ndquadrant.com [091130 16:28]: You've written that as if you are spotting a problem. It sounds to me that this is exactly the situation we would like to detect and this is a perfect way of doing that. What do you see is the purpose here apart from spotting corruptions? Do we think error rates are so low we can recover the corruption by doing something clever with the CRC? I envisage most corruptions as being unrecoverable except from backup/WAL/replicated servers. It's been a long day, so perhaps I've misunderstood. No, I believe the torn-page problem is exactly the thing that made the checksum talks stall out last time... The torn page isn't currently a problem on only-hint-bit-dirty writes, because if you get half-old/half-new, the only changes is the hint bit - no big loss, the data is still the same. But, with a form of check-sums, when you read it it next time, is it corrupt? According to the check-sum, yes, but in reality, the *data* is still valid, just that the check sum is/isn't correctly matching the half-changed hint bits... A good argument, but we're missing some proportion. There are at most 240 hint bits in an 8192 byte block. So that is less than 0.5% of the data block where a single bit error would not corrupt data, and 0% of the data block where a 2+ bit error would not corrupt data. Put it another way, more than 99.5% of possible errors would cause data loss, so I would at least like the option of being told about them. The other perspective is that these errors are unlikely to be caused by cosmic rays and other quantum effects, they are more likely to be caused by hardware errors. Hardware errors are frequently repeatable, so one bank of memory or one section of DRAM is damaged and will give errors. If we don't report an error, the next error from that piece of hardware is almost certain to cause data loss, so even a false positive result should be treated as a good indicator of a true positive detection result in the future. If protection against data loss really does need to be so invasive that we need to WAL-log all changes, then lets make it a table-level option. If people want to pay the price, we should at least give them the option of doing so. We can think of ways of optimising it later. Since I was the one who opposed this on the basis of performance, I want to rescind that objection and say lets make it an option for those that wish to trade performance for some visibility of possible data loss errors. -- 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] Application name patch - v4
Robert Haas wrote: On Mon, Nov 30, 2009 at 4:54 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Le 30 nov. 2009 ? 22:38, Robert Haas a ?crit : I still don't really understand why we wouldn't want RESET ALL to reset the application name. ?In what circumstances would you want the application name to stay the same across a RESET ALL? I can't see any use case, but SET/RESET is tied to SESSION whereas application_name is a CONNECTION property. So it's a hard sell that reseting the session will change connection properties. Is there any technical difference between a connection property and a session property? If so, what is it? ISTM that the only time you're likely going to use RESET ALL is in a connection pooling environment, and that if you're in a connection pooling environment you probably want to reset the application name along with everything else. I might be wrong, but that's how it seems to me at first blush. Uh, what does it mean to reset the application name? Are you resetting it to what it was before the session started, or to a blank string? -- 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] Application name patch - v4
Robert Haas robertmh...@gmail.com writes: On Mon, Nov 30, 2009 at 4:54 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Le 30 nov. 2009 à 22:38, Robert Haas a écrit : I still don't really understand why we wouldn't want RESET ALL to reset the application name. In what circumstances would you want the application name to stay the same across a RESET ALL? I can't see any use case, but SET/RESET is tied to SESSION whereas application_name is a CONNECTION property. So it's a hard sell that reseting the session will change connection properties. Is there any technical difference between a connection property and a session property? If so, what is it? The point is that every other thing you can set in a libpq connection string is persistent throughout the connection. For the ones that you can change at all, such as client_encoding, *RESET ALL actually resets it to what was specified in the connection string*. It does not satisfy the POLA for application_name to behave differently. I think the argument about poolers expecting something different is hogwash. A pooler would want RESET ALL to revert the connection state to what it was at establishment. That would include whatever application name the pooler would have specified when it started the connection, I should think. The only reason we're even having this discussion is that libpq isn't able to make application_name work exactly like its other connection parameters because of the backwards-compatibility issue. Maybe we should think a bit harder about that. Or else give up having libpq manage it like a connection parameter. 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] Application name patch - v4
On Tuesday 01 December 2009 01:11:13 Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Nov 30, 2009 at 4:54 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Le 30 nov. 2009 à 22:38, Robert Haas a écrit : I still don't really understand why we wouldn't want RESET ALL to reset the application name. In what circumstances would you want the application name to stay the same across a RESET ALL? I can't see any use case, but SET/RESET is tied to SESSION whereas application_name is a CONNECTION property. So it's a hard sell that reseting the session will change connection properties. Is there any technical difference between a connection property and a session property? If so, what is it? I think the argument about poolers expecting something different is hogwash. A pooler would want RESET ALL to revert the connection state to what it was at establishment. That would include whatever application name the pooler would have specified when it started the connection, I should think. Actually I think the poolers make a good case for a SET variant which emulates connection set variables... RESET ALL in a connection pooler does different things than RESET ALL outside of one. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
Simon Riggs si...@2ndquadrant.com writes: On Mon, 2009-11-30 at 16:49 -0500, Aidan Van Dyk wrote: No, I believe the torn-page problem is exactly the thing that made the checksum talks stall out last time... The torn page isn't currently a problem on only-hint-bit-dirty writes, because if you get half-old/half-new, the only changes is the hint bit - no big loss, the data is still the same. A good argument, but we're missing some proportion. No, I think you are. The problem with the described behavior is exactly that it converts a non-problem into a problem --- a big problem, in fact: uncorrectable data loss. Loss of hint bits is expected and tolerated in the current system design. But a block with bad CRC is not going to have any automated recovery path. So the difficulty is that in the name of improving system reliability by detecting infrequent corruption events, we'd be decreasing system reliability by *creating* infrequent corruption events, added onto whatever events we were hoping to detect. There is no strong argument you can make that this isn't a net loss --- you'd need to pull some error-rate numbers out of the air to even try to make the argument, and in any case the fact remains that more data gets lost with the CRC than without it. The only thing the CRC is really buying is giving the PG project a more plausible argument for blaming data loss on somebody else; it's not helping the user whose data got lost. It's hard to justify the amount of work and performance hit we'd take to obtain a feature like that. 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] ProcessUtility_hook
I have applied this patch, with only a minor wording improvement: Specify literalon/ to track DDL commands, which excludes commandSELECT/, ^^ Thanks. --- Itagaki Takahiro wrote: Euler Taveira de Oliveira eu...@timbira.com wrote: The functionality is divided in two parts. The first part is a hook in the utility module. The idea is capture the commands that doesn't pass through executor. I'm afraid that that hook will be used only for capturing non-DML queries. If so, why don't we hack the tcop/postgres.c and grab those queries from the same point we log statements? DDLs can be used from user defined functions. It has the same reason why we have executor hooks instead of tcop hooks. The second part is to use that hook to capture non-DML commands for pg_stat_statements module. - I fixed a bug that it should handle only isTopLevel command. - A new parameter pg_stat_statements.track_ddl (boolean) is added to enable or disable the feature. Do we need to have rows = 0 for non-DML commands? Maybe NULL could be an appropriate value. Yes, but it requires additional management to handle 0 and NULL separately. I don't think it is worth doing. The PREPARE command stopped to count the number of rows. Should we count the rows in EXECUTE command or in the PREPARE command? It requires major rewrites of EXECUTE command to pass the number of affected rows to caller. I doubt it is worth fixing because almost all drivers use protocol-level prepared statements instead of PREPARE+EXECUTE. The other command that doesn't count properly is COPY. Could you fix that? I added codes for it. I'm concerned about storing some commands that expose passwords like CREATE ROLE foo PASSWORD 'secret'; I know that the queries are only showed to superusers but maybe we should add this information to documentation or provide a mechanism to exclude those commands. I think there is no additonal problem there because we can see the 'secret' command using pg_stat_activity even now. I don't know if it is worth the trouble adding some code to capture VACUUM and ANALYZE commands called inside autovacuum. I'd like to exclude VACUUM and ANALYZE by autovacuum because pg_stat_statements should not filled with those commands. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center [ Attachment, skipping... ] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Deleted WAL files held open by backends in Linux
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: There has to be something causing those sessions to touch WAL, and the dirty-buffer scenario doesn't seem reliable enough. This is seeming fairly likely to be the cause, though. It may be a combination of the nightly VACUUM FREEZE ANALYZE we typically do on every database and the monitoring software, which would directly run a couple queries each five seconds or so, and would also exercise the software publishers on a similar schedule. My smoking gun is that the monitoring software was restarted last night after the time that the nightly vacuums would have completed, and its connections are not showing as having a deleted WAL file open today. Hmm. If the read-only sessions are long-lived then you could expect that the probability of having flushed a dirty block (and hence had to write some WAL) increases over time and eventually approaches 1. How old were the sessions you were looking at? If we think this is worth doing something about (I'm not convinced yet) then the answer would be to forcibly close a backend's open WAL file in some reasonably seldom-used code path. One possibility that comes to mind is to do it in ProcessCatchupEvent(), which will be invoked in approximately the right circumstances: a backend that is sitting idle for a long time within an otherwise busy system. That wouldn't be a 100% solution, because if the backend is handling a steady stream of queries it will likely never run ProcessCatchupEvent(). But the places that would be 100% (like transaction commit) would probably entail too much of a performance hit from repeatedly opening and closing WAL files. 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] ProcessUtility_hook
Bruce Momjian br...@momjian.us writes: I have applied this patch, with only a minor wording improvement: Uh, we weren't even done reviewing this were we? 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] ProcessUtility_hook
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have applied this patch, with only a minor wording improvement: Uh, we weren't even done reviewing this were we? Uh, I am new to this commitfest wiki thing, but it did have a review by Euler Taveira de Oliveira: https://commitfest.postgresql.org/action/patch_view?id=196 and the author replied. Is there more that needs to be done? -- 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] ProcessUtility_hook
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Uh, we weren't even done reviewing this were we? Uh, I am new to this commitfest wiki thing, but it did have a review by Euler Taveira de Oliveira: https://commitfest.postgresql.org/action/patch_view?id=196 and the author replied. Is there more that needs to be done? It wasn't marked Ready For Committer, so presumably the reviewer wasn't done with it. I know I hadn't looked at it at all, because I was waiting for the commitfest review process to finish. 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] ProcessUtility_hook
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Uh, we weren't even done reviewing this were we? Uh, I am new to this commitfest wiki thing, but it did have a review by Euler Taveira de Oliveira: https://commitfest.postgresql.org/action/patch_view?id=196 and the author replied. Is there more that needs to be done? It wasn't marked Ready For Committer, so presumably the reviewer wasn't done with it. I know I hadn't looked at it at all, because I was waiting for the commitfest review process to finish. So, if someone writes a patch, and it is reviewed, and the patch author updates the patch and replies, it still should be reviewed again before being committed? I was unclear on that. The updated patch only appeared today, so maybe it was ready, but the commit fest manager has to indicate that in the status before I review/apply it? Should I revert the patch? So there is nothing for me to do to help? The only two patches I see as ready for committer are HS and SR; not going to touch those. ;-) Also, we are two weeks into the commit fest and we have more unapplied patches than applied ones. -- 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] ProcessUtility_hook
I wrote: It wasn't marked Ready For Committer, so presumably the reviewer wasn't done with it. I know I hadn't looked at it at all, because I was waiting for the commitfest review process to finish. ... 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. 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? 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. 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. 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] ProcessUtility_hook
Bruce Momjian br...@momjian.us writes: So, if someone writes a patch, and it is reviewed, and the patch author updates the patch and replies, it still should be reviewed again before being committed? Well, that's for the reviewer to say --- if the update satisfies his concerns, he should sign off on it, if not not. I've tried to avoid pre-empting that process. Also, we are two weeks into the commit fest and we have more unapplied patches than applied ones. Yup. Lots of unfinished reviews out there. Robert spent a good deal of effort in the last two fests trying to light fires under reviewers; do you want to take up that cudgel? I think wholesale commits of things that haven't finished review is mostly going to send a signal that the review process doesn't matter, which is *not* the signal I think we should send. 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] ProcessUtility_hook
OK, reverted and placed back in Needs Review status. --- Tom Lane wrote: I wrote: It wasn't marked Ready For Committer, so presumably the reviewer wasn't done with it. I know I hadn't looked at it at all, because I was waiting for the commitfest review process to finish. ... 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. 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? 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. 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. 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] ProcessUtility_hook
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: So, if someone writes a patch, and it is reviewed, and the patch author updates the patch and replies, it still should be reviewed again before being committed? Well, that's for the reviewer to say --- if the update satisfies his concerns, he should sign off on it, if not not. I've tried to avoid pre-empting that process. OK, so the reviewer knows he has to reply to the author's comments, OK. Also, we are two weeks into the commit fest and we have more unapplied patches than applied ones. Yup. Lots of unfinished reviews out there. Robert spent a good deal of effort in the last two fests trying to light fires under reviewers; do you want to take up that cudgel? I think wholesale commits of things I am afraid I am then duplicating work the commit fest manager is doing, and if someone is bugged by me and the CF manager, they might get upset. that haven't finished review is mostly going to send a signal that the review process doesn't matter, which is *not* the signal I think we should send. True. Maybe I am best focusing on open issues like the threading and psql -1 patches I worked on today. There is certainly enough of that stuff to keep me busy. I thought I could help with the commit fest load, but now I am unsure. That non-commit-fest stuff has to be done too so maybe managing that will help. -- 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] A thought about regex versus multibyte character sets
I wrote:
I therefore propose the following idea: if the database encoding is
UTF8, allow the regc_locale.c functions to call the wctype.h
functions, assuming that wchar_t and pg_wchar_t share the same
representation. On platforms where wchar_t is only 16 bits, we can do
this up to U+ and be stupid about code points above that.
Or to be concrete, how about the attached? It seems to do what's
wanted, but I'm hardly the best-qualified person to test it.
regards, tom lane
Index: src/backend/regex/regc_locale.c
===
RCS file: /cvsroot/pgsql/src/backend/regex/regc_locale.c,v
retrieving revision 1.9
diff -c -r1.9 regc_locale.c
*** src/backend/regex/regc_locale.c 14 Feb 2008 17:33:37 - 1.9
--- src/backend/regex/regc_locale.c 1 Dec 2009 03:04:29 -
***
*** 349,415
}
};
/*
! * some ctype functions with non-ascii-char guard
*/
static int
pg_wc_isdigit(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isdigit((unsigned char) c));
}
static int
pg_wc_isalpha(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isalpha((unsigned char) c));
}
static int
pg_wc_isalnum(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isalnum((unsigned char) c));
}
static int
pg_wc_isupper(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isupper((unsigned char) c));
}
static int
pg_wc_islower(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX islower((unsigned char) c));
}
static int
pg_wc_isgraph(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isgraph((unsigned char) c));
}
static int
pg_wc_isprint(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isprint((unsigned char) c));
}
static int
pg_wc_ispunct(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX ispunct((unsigned char) c));
}
static int
pg_wc_isspace(pg_wchar c)
{
! return (c = 0 c = UCHAR_MAX isspace((unsigned char) c));
}
static pg_wchar
pg_wc_toupper(pg_wchar c)
{
! if (c = 0 c = UCHAR_MAX)
return toupper((unsigned char) c);
return c;
}
--- 349,500
}
};
+
/*
! * ctype functions adapted to work on pg_wchar (a/k/a chr)
! *
! * When working in UTF8 encoding, we use the wctype.h functions if
! * available. This assumes that every platform uses Unicode codepoints
! * directly as the wchar_t representation of Unicode. On some platforms
! * wchar_t is only 16 bits wide, so we have to punt for codepoints 0x.
! *
! * In all other encodings, we use the ctype.h functions for pg_wchar
! * values up to 255, and punt for values above that. This is only 100%
! * correct in single-byte encodings such as LATINn. However, non-Unicode
! * multibyte encodings are mostly Far Eastern character sets for which the
! * properties being tested here aren't relevant for higher code values anyway.
! *
! * NB: the coding here assumes pg_wchar is an unsigned type.
*/
+
static int
pg_wc_isdigit(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswdigit((wint_t) c);
! }
! #endif
! return (c = (pg_wchar) UCHAR_MAX isdigit((unsigned char) c));
}
static int
pg_wc_isalpha(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswalpha((wint_t) c);
! }
! #endif
! return (c = (pg_wchar) UCHAR_MAX isalpha((unsigned char) c));
}
static int
pg_wc_isalnum(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswalnum((wint_t) c);
! }
! #endif
! return (c = (pg_wchar) UCHAR_MAX isalnum((unsigned char) c));
}
static int
pg_wc_isupper(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswupper((wint_t) c);
! }
! #endif
! return (c = (pg_wchar) UCHAR_MAX isupper((unsigned char) c));
}
static int
pg_wc_islower(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswlower((wint_t) c);
! }
! #endif
! return (c = (pg_wchar) UCHAR_MAX islower((unsigned char) c));
}
static int
pg_wc_isgraph(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswgraph((wint_t) c);
! }
! #endif
! return (c = (pg_wchar) UCHAR_MAX isgraph((unsigned char) c));
}
static int
pg_wc_isprint(pg_wchar c)
{
! #ifdef USE_WIDE_UPPER_LOWER
! if (GetDatabaseEncoding() == PG_UTF8)
! {
! if (sizeof(wchar_t) = 4 || c = (pg_wchar) 0x)
! return iswprint((wint_t) c);
! }
! #endif
! return
Re: [HACKERS] CommitFest status/management
Bruce Momjian wrote: So, if someone writes a patch, and it is reviewed, and the patch author updates the patch and replies, it still should be reviewed again before being committed? That's generally how things were expected to happen--to at least double-check that the proposed fixes match what was expected. I don't think it was spelled out very well in the past though. Also, we are two weeks into the commit fest and we have more unapplied patches than applied ones. Considering that one of those was a holiday week with a lot of schedule disruption proceeding it, I don't know how much faster things could have moved. There were large chunks of the reviewer volunteers who wanted only jobs they could finish before the holiday, and others who weren't available at all until afterwards. And I don't know about every else, but I took all four days off and started today behind by several hundred list messages. I was planning to start nagging again tomorrow, hoping that most would be caught up from any holiday time off too by then. Right now, of the 16 patches listed in Needs Review besides the ECPG ones Michael is working through, the breakdown is like this: Not reviewed at all yet: 6 Reviewed once, updated, waiting for re-review: 10 So the bulk of the problem for keeping the pipeline moving is in these re-reviews holding up transitions to Ready for committer. I've had some discussion with Robert about how to better distinguish in the management app when the reviewer has work they're expected to do vs. when they think they're done with things. We're converging toward a more clear set of written guidelines to provide for managing future CommitFests as part of that, right now there's a few too many fuzzy parts for my liking. If the need here is to speed up how fast things are fed to committers, we can certainly do that. The current process still favors having reviewers do as much as possible first, as shown by all the stuff sitting in the re-review queue. The work we're waiting on them for could be done by the committers instead if we want to shorten the whole process a bit. I don't think that's really what you want 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] CommitFest status/management
Greg Smith wrote: If the need here is to speed up how fast things are fed to committers, we can certainly do that. The current process still favors having reviewers do as much as possible first, as shown by all the stuff sitting in the re-review queue. The work we're waiting on them for could be done by the committers instead if we want to shorten the whole process a bit. I don't think that's really what you want though. As I have observed before, I think we need some infrastructure to help committers claim items, so we don't duplicate work. Right now the only items marked ready for reviewer are Streaming Replication and Hot Standby, which I imagine Heiki will be handling. I'm going to look at the YAML format for EXPLAIN patch shortly. 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] SE-PgSQL patch review
KaiGai Kohei wrote: In summary, it was similar approach with what I already proposed in the CF#2, but rejected. During the first commit-fest of v8.5 development cycle, Stephen Frost suggested to rework the default PG's access controls to host other optional security features, not only the default one. Then, I submitted a large patch titled as Reworks for Access Controls, but it contained 3.5KL of changeset on the core routines, and 4KL of new codes into src/backend/security/* except for documentations and testcases. Then, this approach was rejected (not returned with feedback) due to the scale and complexity. After the fest, we discussed the direction to implement SE-PgSQL. Basically, it needs to keep the changeset small, and the rest of features (such as row-level granurality, access control decision cache, ...) shoule be added step-by-step consistently, according to the suggestion in the v8.4 development cycle. Heikki Linnakangas also suggested we need developer documentation which introduces SE-PgSQL compliant permission checks and specification of security hooks, after the reworks are rejected. So, I boldly removed most of the features from SE-PgSQL except for its core functionalities, and added developer documentation (README) and widespread source code comments to introduce the implementations instead. In the result, the current proposal is near to naked one. - No access controls except for database, schema, table and column. - No row-level granularity in access controls. - No access control decision chache. - No security OID within HeapTupleHeader. I believe the current patch is designed according to the past suggestions. Agreed. The patch is exactly what I was hoping to see: o only covers existing Postgres ACLs o has both user and developer documentation o includes regression tests o main code impact is minimal Now, if this is applied, we might then move forward with implementing SE-Linux specific features like mandatory access control (MAC) and row-level security. In terms of review, the patch is 13k lines, but most of that is documentation, se-linux-specific files, system catalog adjustments, and regression tests. Also, I attended KaiGai's talk in Tokyo where he explained that managing permission at the operating system level, the web server level (via .htaccess and htpasswd), and at the database level is confusing, and having a single permission system has benefits. The number of revisions and adjustments KaiGai has done since the original SE-PostgreSQL patch is amazing and certainly gives me confidence that he will be around to help in case there are any problems in the future. So, one big problem is that no one has agreed to review it, partly or probably because few developers understand the SE-Linux API, and many people who have used SE-Linux have been confused by it. I think I could review this if I could team up with someone to help me, ideally someone on instant message (IM) and perhaps using SE-Linux. I think the big question is whether this feature (mappming SE-Linux permissions to existing Postgres permissions) has an acceptable code impact. Of course we might be adding things later, but at this stage is this something we can apply? -- 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] CommitFest status/management
Andrew Dunstan and...@dunslane.net writes: As I have observed before, I think we need some infrastructure to help committers claim items, so we don't duplicate work. Robert acknowledged the need for a claimed by committer field in the fest application, but he hasn't got round to it yet. In the meantime I've been adding a Taking this one... type of comment to an entry I want to claim. I'm going to look at the YAML format for EXPLAIN patch shortly. Do we have consensus yet that we want YAML? It seemed, well, yet another format without all that much advantage over what's there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest status/management
On Nov 30, 2009, at 8:08 PM, Tom Lane wrote: I'm going to look at the YAML format for EXPLAIN patch shortly. Do we have consensus yet that we want YAML? It seemed, well, yet another format without all that much advantage over what's there. Legibility++ David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest status/management
Tom Lane wrote: I'm going to look at the YAML format for EXPLAIN patch shortly. Do we have consensus yet that we want YAML? It seemed, well, yet another format without all that much advantage over what's there. I think you and I are the two main skeptics ;-) But we seem to be rather in the minority, and it's not terribly invasive from what I have seen so far. 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] SE-PgSQL patch review
On Mon, Nov 30, 2009 at 11:03:08PM -0500, Bruce Momjian wrote: KaiGai Kohei wrote: In summary, it was similar approach with what I already proposed in the CF#2, but rejected. During the first commit-fest of v8.5 development cycle, Stephen Frost suggested to rework the default PG's access controls to host other optional security features, not only the default one. Then, I submitted a large patch titled as Reworks for Access Controls, but it contained 3.5KL of changeset on the core routines, and 4KL of new codes into src/backend/security/* except for documentations and testcases. Then, this approach was rejected (not returned with feedback) due to the scale and complexity. After the fest, we discussed the direction to implement SE-PgSQL. Basically, it needs to keep the changeset small, and the rest of features (such as row-level granurality, access control decision cache, ...) shoule be added step-by-step consistently, according to the suggestion in the v8.4 development cycle. Heikki Linnakangas also suggested we need developer documentation which introduces SE-PgSQL compliant permission checks and specification of security hooks, after the reworks are rejected. So, I boldly removed most of the features from SE-PgSQL except for its core functionalities, and added developer documentation (README) and widespread source code comments to introduce the implementations instead. In the result, the current proposal is near to naked one. - No access controls except for database, schema, table and column. - No row-level granularity in access controls. - No access control decision chache. - No security OID within HeapTupleHeader. I believe the current patch is designed according to the past suggestions. Agreed. The patch is exactly what I was hoping to see: o only covers existing Postgres ACLs o has both user and developer documentation o includes regression tests o main code impact is minimal This patch addresses points 1-3 of Andrew Sullivan's post here: http://archives.postgresql.org/pgsql-hackers/2008-10/msg00388.php Left out is point 4, namely whether it's possible to map metadata access controls can do this completely, and if so, whether this patch implements such a mapping. 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. All that aside, there is an excellent economic reason why a proprietary product might need to follow a dead-end technology, namely increasing shareholder value due to one or more large, long-term deals. PostgreSQL doesn't have this motive, although some of the proprietary forks may well. Can we see about Andrew Sullivan's point 4? Or is it more important to address the do we want to question first? Whatever order we take them in, we do need to address both. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PgSQL patch review
Bruce Momjian wrote: KaiGai Kohei wrote: In summary, it was similar approach with what I already proposed in the CF#2, but rejected. During the first commit-fest of v8.5 development cycle, Stephen Frost suggested to rework the default PG's access controls to host other optional security features, not only the default one. Then, I submitted a large patch titled as Reworks for Access Controls, but it contained 3.5KL of changeset on the core routines, and 4KL of new codes into src/backend/security/* except for documentations and testcases. Then, this approach was rejected (not returned with feedback) due to the scale and complexity. After the fest, we discussed the direction to implement SE-PgSQL. Basically, it needs to keep the changeset small, and the rest of features (such as row-level granurality, access control decision cache, ...) shoule be added step-by-step consistently, according to the suggestion in the v8.4 development cycle. Heikki Linnakangas also suggested we need developer documentation which introduces SE-PgSQL compliant permission checks and specification of security hooks, after the reworks are rejected. So, I boldly removed most of the features from SE-PgSQL except for its core functionalities, and added developer documentation (README) and widespread source code comments to introduce the implementations instead. In the result, the current proposal is near to naked one. - No access controls except for database, schema, table and column. - No row-level granularity in access controls. - No access control decision chache. - No security OID within HeapTupleHeader. I believe the current patch is designed according to the past suggestions. Agreed. The patch is exactly what I was hoping to see: o only covers existing Postgres ACLs o has both user and developer documentation o includes regression tests o main code impact is minimal Now, if this is applied, we might then move forward with implementing SE-Linux specific features like mandatory access control (MAC) and row-level security. In terms of review, the patch is 13k lines, but most of that is documentation, se-linux-specific files, system catalog adjustments, and regression tests. Also, I attended KaiGai's talk in Tokyo where he explained that managing permission at the operating system level, the web server level (via .htaccess and htpasswd), and at the database level is confusing, and having a single permission system has benefits. The number of revisions and adjustments KaiGai has done since the original SE-PostgreSQL patch is amazing and certainly gives me confidence that he will be around to help in case there are any problems in the future. So, one big problem is that no one has agreed to review it, partly or probably because few developers understand the SE-Linux API, and many people who have used SE-Linux have been confused by it. I think I could review this if I could team up with someone to help me, ideally someone on instant message (IM) and perhaps using SE-Linux. I think some of SELinux developers (including me) can help you to review the code. They are maintaining both of the kernel and libraries (APIs). I'll call for them to help reviewing. BTW, I can use skype messanger in my home, and IRC may be available in office. I think the big question is whether this feature (mappming SE-Linux permissions to existing Postgres permissions) has an acceptable code impact. Of course we might be adding things later, but at this stage is this something we can apply? It needs to deploy a set of hooks on the strategic points of the core PostgreSQL codes, and rest of steps (such as computing a default security context and making an access control decision) are done in the SE-PgSQL specific files. These hooks are designed not to prevent anything when SE-PgSQL is disabled. 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] SE-PgSQL patch review
David Fetter wrote: On Mon, Nov 30, 2009 at 11:03:08PM -0500, Bruce Momjian wrote: KaiGai Kohei wrote: In summary, it was similar approach with what I already proposed in the CF#2, but rejected. During the first commit-fest of v8.5 development cycle, Stephen Frost suggested to rework the default PG's access controls to host other optional security features, not only the default one. Then, I submitted a large patch titled as Reworks for Access Controls, but it contained 3.5KL of changeset on the core routines, and 4KL of new codes into src/backend/security/* except for documentations and testcases. Then, this approach was rejected (not returned with feedback) due to the scale and complexity. After the fest, we discussed the direction to implement SE-PgSQL. Basically, it needs to keep the changeset small, and the rest of features (such as row-level granurality, access control decision cache, ...) shoule be added step-by-step consistently, according to the suggestion in the v8.4 development cycle. Heikki Linnakangas also suggested we need developer documentation which introduces SE-PgSQL compliant permission checks and specification of security hooks, after the reworks are rejected. So, I boldly removed most of the features from SE-PgSQL except for its core functionalities, and added developer documentation (README) and widespread source code comments to introduce the implementations instead. In the result, the current proposal is near to naked one. - No access controls except for database, schema, table and column. - No row-level granularity in access controls. - No access control decision chache. - No security OID within HeapTupleHeader. I believe the current patch is designed according to the past suggestions. Agreed. The patch is exactly what I was hoping to see: o only covers existing Postgres ACLs o has both user and developer documentation o includes regression tests o main code impact is minimal This patch addresses points 1-3 of Andrew Sullivan's post here: http://archives.postgresql.org/pgsql-hackers/2008-10/msg00388.php Left out is point 4, namely whether it's possible to map metadata access controls can do this completely, and if so, whether this patch implements such a mapping. I'm not clear what means the metadata level access controls here. If you talk about permissions when we create/alter/drop a certain database obejct, the existing PG model also checks its permission. These operations are equivalent to modify metadata of database objects. And, SE-PgSQL also checks its permissions on modification of metadata. We can refer the metadata of the database object using SELECT on the system catalogs typically. In this case, we need row-level granularity because individual tuples mean metadata of the database object. So, we don't implement permission checks of references to metadata in this version. I introduced it on the README file as follows: | o getattr (not implemented yet) | | It is checked when a client read properties of database object. | | Typically, it is required on scanning database objects within system catalogs, | such as SELECT * FROM pg_class. Because this check requires row-level access | control facilities, it is not implemented yet in this version. | | Note that no need to check this permission, unless fetched properties are | consumed internally without returning to clients. For example, catcache stuff | provides system internal stuff an easy way to refer system catalog. It is used | to implement backend routines, and fetched properties are not returned to the | client in normal way, so we don't check this permission here. 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. All that aside, there is an excellent economic reason why a proprietary product might need to follow a dead-end technology, namely increasing shareholder value due to one or more large, long-term deals. PostgreSQL doesn't have this motive, although some of the proprietary forks may well. Can we see about Andrew Sullivan's point 4? Or is it more important to address the do we want to question first? Whatever order we take them in, we do need to address both. As Bruce mentioned, our big motivation is improvement of web-system's security. However, most of security burden tend to concentrate to the quality of web applications, because it tends to share privileges on OS/DB for all the user's requests. In other word, correctness of the system depends on whether every applications are bug-free, or not. But we know it is difficult to keep them bug-free. :( Nowadays, we can often see a web server which host multiple tenants in a single daemon. For example, one is assigned to the division-X, and the other is assigned to the division-Y, ... In this case, it is worthful to prevent a (buggy)
[HACKERS] Recover deleted records using
Hi Everyone, Where/What source can I modify to retrieve deleted records using HeapTupleSatisfiesVisibility() I am using postgres 8.1.11 source The database has not yet been vacuumed. Regards, Allan -- Sent 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?
On mån, 2009-11-30 at 12:21 -0500, Bruce Momjian wrote: ! for thread safety; use --disable-thread-safety to disable threading.]) --disable-thread-safety does not disable threading, it disables thread safety. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add solaris path for docbook COLLATEINDEX
On mån, 2009-11-30 at 20:57 +0100, Zdenek Kotala wrote: I'm not sgml//docbook guru. Do you think that Solaris location of collateindex.pl is wrong? Does exist any recommendation for this? I could log a bug, but I need some link with recommendation. It's a normal program, so you install it where you would normally install programs. Like /usr/bin/ or something similar. -- Sent 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 Mon, 2009-11-30 at 20:02 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Mon, 2009-11-30 at 16:49 -0500, Aidan Van Dyk wrote: No, I believe the torn-page problem is exactly the thing that made the checksum talks stall out last time... The torn page isn't currently a problem on only-hint-bit-dirty writes, because if you get half-old/half-new, the only changes is the hint bit - no big loss, the data is still the same. A good argument, but we're missing some proportion. No, I think you are. The problem with the described behavior is exactly that it converts a non-problem into a problem --- a big problem, in fact: uncorrectable data loss. Loss of hint bits is expected and tolerated in the current system design. But a block with bad CRC is not going to have any automated recovery path. So the difficulty is that in the name of improving system reliability by detecting infrequent corruption events, we'd be decreasing system reliability by *creating* infrequent corruption events, added onto whatever events we were hoping to detect. There is no strong argument you can make that this isn't a net loss --- you'd need to pull some error-rate numbers out of the air to even try to make the argument, and in any case the fact remains that more data gets lost with the CRC than without it. The only thing the CRC is really buying is giving the PG project a more plausible argument for blaming data loss on somebody else; it's not helping the user whose data got lost. It's hard to justify the amount of work and performance hit we'd take to obtain a feature like that. I think there is a clear justification for an additional option. There is no creation of corruption events. This scheme detects corruption events that *have* occurred. Now I understand that we previously would have recovered seamlessly from such events, but they were corruption events nonetheless and I think they need to be reported. (For why, see Conclusion #2, below). The frequency of such events against other corruption events is important here. You are right that there is effectively one new *type* of corruption event but without error-rate numbers you can't say that this shows substantially more data gets lost with the CRC than without it. So let me say this again: the argument that inaction is a safe response here relies upon error-rate numbers going in your favour. You don't persuade us of one argument purely by observing that the alternate proposition requires a certain threshold error-rate - both propositions do. So its a straight: what is the error-rate? discussion and ISTM that there is good evidence of what that is. --- So, what is the probability of single-bit errors effecting hint bits? The hint bits can occupy any portion of the block, so their positions are random. They occupy less than 0.5% of the block, so they must account for a very small proportion of hardware-induced errors. Since most reasonable servers use Error Correcting Memory, I would expect not to see a high level of single bit errors, even though we know they are occurring in the underlying hardware (Conclusion #1, Schroeder et al, 2009) What is the chance that a correctable corruption event is in no way linked to another non-correctable event later? We would need to argue that corruptions are a purely stochastic process in all cases, yet again, there is evidence of both a clear and strong linkage from correctable to non-correctable errors. (Conclusion #2 and Conclusion #7, Schroeder et al, 2009). Schroeder et al http://www.cs.toronto.edu/~bianca/papers/sigmetrics09.pdf (thanks Greg!) Based on that paper, ISTM that ignorable hint bit corruptions are likely to account for a very small proportion of all corruptions, and of those, 70-80% would show up as a non-ignorable corruptions within a month anyway. So the immediate effect on reliability is tiny, if any. The effect on detection is huge, which eventually produces significantly higher relability overall. The only thing the CRC is really buying is giving the PG project a more plausible argument for blaming data loss on somebody else; it's not helping the user whose data got lost. This isn't about blame, its about detection. If we know something has happened we can do something about it. Experienced people know that hardware goes wrong, they just want to be told so they can fix it. I blocked development of a particular proposal earlier for performance reasons, but did not intend to block progress completely. It seems likely the checks will cause a performance hit. So make them an option. -- 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
