Re: [HACKERS] VLDB Features
On Saturday 2007-12-15 02:14, Simon Riggs wrote: On Fri, 2007-12-14 at 18:22 -0500, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: By modifying COPY: COPY IGNORE ERRORS or some such would instruct COPY to drop (and log) rows that contain malformed data. That is, rows with too many or too few columns, rows that result in constraint violations, and rows containing columns where the data type's input function raises an error. The last case is the only thing that would be a bit tricky to implement, I think: you could use PG_TRY() around the InputFunctionCall, but I guess you'd need a subtransaction to ensure that you reset your state correctly after catching an error. Yeah. It's the subtransaction per row that's daunting --- not only the cycles spent for that, but the ensuing limitation to 4G rows imported per COPY. I'd suggest doing everything at block level - wrap each new block of data in a subtransaction - apply data to the table block by block (can still work with FSM). - apply indexes in bulk for each block, unique ones first. That then gives you a limit of more than 500 trillion rows, which should be enough for anyone. Wouldn't it only give you more than 500T rows in the best case? If it hits a bad row it has to back off and roll forward one row and one subtransaction at a time for the failed block. So in the worst case, where there is at least one exception row per block, I think you would still wind up with only a capacity of 4G rows. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgindent issue with EXEC_BACKEND-only typedefs
Alvaro Herrera wrote: Hi, It seems pgindent is not considering EXEC_BACKEND typedefs. For example, static void restore_backend_variables(BackendParameters * param, Port *port); BackendParameters is not considered a typedef. Not sure how serious an issue this is ... I just noticed and thought I would mention it. Yep. The cause is that find_typedefs actually pulls the typedef out of the debugged-enabled binary, and on Unix those functions aren't used by default. This is spelled out in the pgindent/README in CVS. I could just EXEC_BACKEND in the debug build I use but I suppose there are other typedef I am missing as well. Any idea on a more comprehensive solution to finding typedefs? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] VLDB Features
Ühel kenal päeval, L, 2007-12-15 kell 01:12, kirjutas Tom Lane: Josh Berkus [EMAIL PROTECTED] writes: There's no way we can do a transactionless load, then? I'm thinking of the load-into-new-partition which is a single pass/fail operation. Would ignoring individual row errors in for this case still cause these kinds of problems? Given that COPY fires triggers and runs CHECK constraints, there is no part of the system that cannot be exercised during COPY. So I think supposing that we can just deal with some simplified subset of reality is mere folly. But can't we _define_ such a subset, where we can do a transactionless load ? I don't think that most DW/VLDB schemas fire complex triggers or custom data-modifying functions inside CHECK's. Then we could just run the remaining simple CHECK constraints ourselves and not abort on non-check, but just log the rows ? The COPY ... WITH ERRORS TO ... would essentially become a big conditional RULE through which the incoming data is processed. -- Hannu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pgwin32_open returning EINVAL
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: Andrew Dunstan [EMAIL PROTECTED] writes: Interesting. Maybe forever is going a bit too far, but retrying for n seconds or so. I think looping forever is the right thing. Having a fixed timeout just means Postgres will break sometimes instead of all the time. And it introduces non-deterministic behaviour too. Looping forever would be considered broken by a very large fraction of the community. Really? I understood we're talking about having Postgres fail with an error if any of its files are opened by another program such as backup software. So with a 30s limit it means Postgres might or might not fail depending on how long this other software has the file open. That doesn't seem like an improvement. IIRC we have a 30-second timeout in rename() for Windows, and that seems to be working well enough, so I'd be inclined to copy the behavior for this case. I thought it was unlink, and the worst-case there is that we leak a file until some later time. I'm wasn't exactly following that case though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release Note Changes
Pavan Deolasee wrote: One of the improvements of HOT is to truncate a DEAD tuple to its line pointer. A DEAD tuple could be an old version of an updated tuple or a deleted tuple. When a tuple is truncated, the space used by the line pointer can not be reused (until the index entries are removed). But the space used by the actual tuple can be reused for a later update, after the page is defragmented. Note that this defragmentation can happen outside of a VACUUM. This gives us an ability to run VACUUM less frequently on a table. We still need to run VACUUM to remove the line pointer bloat, but may be less frequently for the given percentage of bloat. IMHO this should have a positive effect on performance atleast in an IO bound scenario. I have updated the release note item title to mention DELETE as well: Heap-Only Tuples (acronymHOT/) accelerate space reuse for most commandUPDATE/s and commandDELETE/s -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Document how to turn off disk write cache on popular operating
Magnus Hagander wrote:
Should this mention that you don't need to turn it off at the disk level
if
you use fsync_writethrough?
Uh, I remember we looked at this checkbox before but I don't remember
the details, and I can't find a comment about it. Was the issue that
writethrough always forces through the disk cache? Is that the default
on Win32? Did we comment this somewhere?
If you set it to fsync or fsync_writethrough it will write through the
cache. (fsync is just an alias)
If you set it to OPEN_DATASYNC, it will respond to the checkbox you are
referring to.
OPEN_DATASYNC is the default, IIRC.
OK, docs updated:
On productnameWindows/ if varnamewal_sync_method/ is
literalopen_datasync/ (the default), write caching is disabled by
unchecking literalMy Computer\Open\{select disk
drive}\Properties\Hardware\Properties\Policies\Enable write caching on
the disk/. Also on Windows, literalfsync/ and
literalfsync_writethrough/ never do write caching.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Negative LIMIT and OFFSET?
On Fri, Dec 14, 2007 at 06:42:24PM -0500, Tom Lane wrote: How do people feel about applying this to 8.3, rather than holding it? To me, this is a feature change, and therefore should be held. A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] VLDB Features
Hannu Krosing [EMAIL PROTECTED] writes: But can't we _define_ such a subset, where we can do a transactionless load ? Sure ... but you'll find that it's not large enough to be useful. Once you remove all the interesting consistency checks such as unique indexes and foreign keys, the COPY will tend to go through just fine, and then you're still stuck trying to weed out bad data without very good tools for it. The only errors we could really separate out without subtransaction fencing are extremely trivial ones like too many or too few fields on a line ... which can be caught with a sed script. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Negative LIMIT and OFFSET?
Andrew Sullivan [EMAIL PROTECTED] writes: On Fri, Dec 14, 2007 at 06:42:24PM -0500, Tom Lane wrote: How do people feel about applying this to 8.3, rather than holding it? To me, this is a feature change, and therefore should be held. Well, I wouldn't advocate making it in a minor release, but it's not clear how that translates into saying it can't go into 8.3. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgindent issue with EXEC_BACKEND-only typedefs
Bruce Momjian [EMAIL PROTECTED] writes: Alvaro Herrera wrote: It seems pgindent is not considering EXEC_BACKEND typedefs. Yep. The cause is that find_typedefs actually pulls the typedef out of the debugged-enabled binary, and on Unix those functions aren't used by default. This is spelled out in the pgindent/README in CVS. I could just EXEC_BACKEND in the debug build I use but I suppose there are other typedef I am missing as well. Any idea on a more comprehensive solution to finding typedefs? I guess that explains why plpython.c and most of contrib have similar problems. If you want to do this on the basis of precompiled code, you need to enable every optional feature in your build, and include the PLs and contrib modules not only the core backend. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] test
Andrew Hammond wrote: On Dec 12, 2007 11:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: test Does anybody see any value in having [EMAIL PROTECTED] be an alias for pgsql-hackers? No, but I see some mild irritation in having to modify my rules to tag a second address with the pgsql-hackers label. The bigger problem is that if the header address isn't modified while being renamed, and someone then adds the right address, you now have emails going to both addresses. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pgindent issue with EXEC_BACKEND-only typedefs
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Alvaro Herrera wrote: It seems pgindent is not considering EXEC_BACKEND typedefs. Yep. The cause is that find_typedefs actually pulls the typedef out of the debugged-enabled binary, and on Unix those functions aren't used by default. This is spelled out in the pgindent/README in CVS. I could just EXEC_BACKEND in the debug build I use but I suppose there are other typedef I am missing as well. Any idea on a more comprehensive solution to finding typedefs? I guess that explains why plpython.c and most of contrib have similar problems. If you want to do this on the basis of precompiled code, you need to enable every optional feature in your build, and include the PLs and contrib modules not only the core backend. I do, I think. The problem with plpython is that my operating system's python it too old to compile it. I guess I could upgrade the python. I have added documentation to install /contrib libraries before finding typedefs so that should fix that problem. Does someone want to generate that typedef list in the future? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Negative LIMIT and OFFSET?
Gregory Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: I'm fairly surprised these queries work. Is there some reason why we support this? April Fools Day? Jules Verne? I'm all for fast queries, but zero seems like the lowest value we should support... Huh, I was all set to post an example of a useful application of it but then apparently I'm wrong and it doesn't work: postgres=# select * from generate_series(1,10) offset -1 limit 2; generate_series - 1 2 (2 rows) I'll leave it as an exercise for the reader to guess what I was expecting. So given that that doesn't work I don't see any particular reason to accept negative offsets or limits in 8.4 and on. Since we got LIMIT/OFFSET from MySQL, would someone tell us how MySQL behaves in these cases? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Negative LIMIT and OFFSET?
Bruce Momjian [EMAIL PROTECTED] writes: Since we got LIMIT/OFFSET from MySQL, would someone tell us how MySQL behaves in these cases? Not very well, at least not in mysql 5.0.45: mysql select * from t limit -2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1 mysql select * from t limit 2 offset -2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1 This behavior suggests that they can't even deal with LIMIT/OFFSET values that aren't simple integer literals ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Negative LIMIT and OFFSET?
Tom Lane [EMAIL PROTECTED] writes: mysql select * from t limit 2 offset -2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1 This behavior suggests that they can't even deal with LIMIT/OFFSET values that aren't simple integer literals ... I suppose when they added these features I think they didn't have subqueries, so there wasn't really much useful that could be done with arbitrary expressions here. Being able to do LIMIT 1+1 doesn't actually add anything. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Negative LIMIT and OFFSET?
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: This behavior suggests that they can't even deal with LIMIT/OFFSET values that aren't simple integer literals ... I suppose when they added these features I think they didn't have subqueries, so there wasn't really much useful that could be done with arbitrary expressions here. Being able to do LIMIT 1+1 doesn't actually add anything. Sure. I think our first implementation of LIMIT was similarly constrained. It's just amusing that they haven't moved past that, despite having had the feature first ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] EXPLAIN ANALYZE printing logical and hardware I/O per-node
On Dec 16, 2007 1:03 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram wrote: I was going to say that I'm really only interested in physical I/O. Logical I/O which is satisfied by the kernel cache is only marginally interesting and buffer fetches from Postgres's shared buffer is entirely uninteresting from the point of view of trying to figure out what is slowing down a query. Ok the Physical I/Os are already visible, if you enable log_statement_stats. I think you missed the point. What log_statement_stats shows are not physical I/Os, they're read() system calls. Unfortunately there's no direct way to tell if a read() is satisfied from OS cache or not. Greg's suggestion was about how to do that. Oh OK. Thanks for clarifying.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] VLDB Features
Hi, On Dec 15, 2007 1:14 PM, Tom Lane [EMAIL PROTECTED] wrote: NikhilS [EMAIL PROTECTED] writes: Any errors which occur before doing the heap_insert should not require any recovery according to me. A sufficient (though far from all-encompassing) rejoinder to that is triggers and CHECK constraints can do anything. The overhead of having a subtransaction per row is a very valid concern. But instead of using a per insert or a batch insert substraction, I am thinking that we can start off a subtraction and continue it till we encounter a failure.The moment an error is encountered, since we have the offending (already in heap) tuple around, we can call a simple_heap_delete on the same and commit (instead of aborting) this subtransaction What of failures that occur only at (sub)transaction commit, such as foreign key checks? What if we identify and define a subset where we could do subtransactions based COPY? The following could be supported: * A subset of triggers and CHECK constraints which do not move the tuple around. (Identifying this subset might be an issue though?) * Primary/unique key indexes As Hannu mentioned elsewhere in this thread, there should not be very many instances of complex triggers/CHECKs around? And may be in those instances (and also the foreign key checks case), the behaviour could default to use a per-subtransaction-per-row or even the existing single transaction model? Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
[HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)
Hi, Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. This may not give huge performance benefits, but as you may know, it will help is reducing the disk footprint. Expecting your comments.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
[HACKERS] Requesting clarification on Vacuuming/Freezing behaviour
Hi, I got some time to relook at the index with the snapshot patch. Still i need to complete the freezing of index tuples with the snapshot. a) When i was looking at the code for freezing heap tuples, i found out the following. While freezing we log the complete block(not just the tuples we froze). b) Again after removing the dead tuples and fragmentation, we log the complete block. As i understand, Vacuum is one atomic operation. Why do we need to make this extra protection for Freezing? I think the fix should be very simple. Before logging the Frozen block, we need to check whether there are any dead tuples removed from it.. Please clarify on whether i am missing something important.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
