Re: [HACKERS] XML index support
On Fri, 27 Jun 2008, Jean-Michel Pour? wrote: Dear friends, You may know me as I worked a long time ago on pgAdmin I with Dave. ЪЪAs this is an XML related question and XML is quite new, I am posting on hackers ML. If any solution is being developped, please inform us. We would like to develop a free REST database (real-estate standard) based on a PostgreSQL schema. This is a free solution for free data also, released under BSD or GPL license. We are charity. This is supposed to a "killer application", so we need to drive down queries to 3ms to 5ms to allow hundreds of simultaneous queries. Php will probably be part of the package. What is in your opinion the best way to achive this : * develop a traditional databe and implement materialized views in PL OR * implement the new XML type with some index (GIST) designed for PostgreSQL. Which one? OR * write triggers behind XML tables to pullulate a traditionnal database, add indexes and query traditionnal database. Hmm, why do you need XML here ? I'd use standard relational approach with our contrib/hstore module for storing specific content, which is a probable reason you want XML. Table inheritance would be also useful. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XML index support
Josh Berkus <[EMAIL PROTECTED]> writes: > Well, anything based on XML data is going to be pretty slow. There's just no > way to extra data from an XML field without lots of parsing. I thought there'd been some discussion of storing XML data values in some kind of pre-parsed format? I agree that's got about nothing to do with indexing, though. 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] XML index support
Jean-Michel, > As this is an XML related question and XML is quite new, I am posting > on hackers ML. If any solution is being developped, please inform us. How is it XML-related? > We would like to develop a free REST database (real-estate standard) > based on a PostgreSQL schema. This is a free solution for free data > also, released under BSD or GPL license. We are charity. > > This is supposed to a "killer application", so we need to drive down > queries to 3ms to 5ms to allow hundreds of simultaneous queries. Well, anything based on XML data is going to be pretty slow. There's just no way to extra data from an XML field without lots of parsing. I'd tend to think it would be much faster to store the data conventially, and just generate XML in response to requests ... either inside or outside PostgreSQL. Of course, I'd need a lot more detail to make a serious assessment. Mind you, we'd be thrilled to have you implement a special XML index type. I don't think it's going to solve your problem, though. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
"Andrew Hammond" <[EMAIL PROTECTED]> writes: > (I thought this line was interesting) > Jun 27 15:54:31 qadb2 postgres[92519]: [44-1] PANIC: could not open > relation 1663/16386/679439393: No such file or directory > I googled to find out what the numbers 1663/16386/679439393 from the > PANIC message mean, but no luck. tablespaceOID/databaseOID/relfilenode. Looks like just some random user table. Not clear why this would be a crash, *especially* since WAL recovery is generally willing to create nonexistent files. Is this reproducible? > (On Thursday night) > vacuumdb: vacuuming of database "adecndb" failed: ERROR: could not > write block 209610 of relation 1663/16386/236356665: No space left on > device > CONTEXT: writing block 209610 of relation 1663/16386/236356665 That's pretty frickin' odd as well, because as a rule we make sure that backing store exists for each table page before we open it up for normal writing. Do you have a way to find out what relation 1663/16386/236356665 is? What filesystem is this database sitting on? > Now, the first message is very strange since we have monitoring on the > file system used by the database and it's been hovering at about 18% > space used for the last month. So I can't figure out why we'd get "No > space left on device", assuming the device is actually the disk (which > seems reasonable given the context) and not shared memory. Yeah, this is definitely a case of ENOSPC being returned by write(), If you're sure the disk wasn't out of space, maybe some per-user quota was getting in the way? 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] Vacuuming leaked temp tables (once again)
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > Is there an easy way for an Admin clean-up the lost temp tables that > autovacuum is complaining about? It seems like it could be along time > and a lot of log messages between when they are first orphaned and and > finally dropped due to anti-wraparound protection. Drop the particular temp schema, maybe? The log message should probably make sure to specify the schema name. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PATCH: CITEXT 2.0
Howdy, [N.B.: I tried to send this a while ago but it didn't get delivered, I'm assuming because, with the uncompressed patch, the email was too big for -hackers. So this is a re-send with the patch gzip'd. Sorry for any duplication]. Please find attached a patch adding a locale-aware, case-insensitive text type, called citext, as a contrib module. A few notes: * I had originally called it lctext, as it's not a true case- insensitive type, but just converts strings to lowercase before comparing them. I changed it to citext at Tom Lane's suggestion, to ease compatibility for users of the original citext module on pgFoundry. * Differences from the original citext are: + Locale-aware lowercasing of strings, rather than just lowercasing ASCII characters. + No implicit casts from text to citext except via assignment. + A few more functions overloaded + Works with 8.3 and CVS head * Many thanks to whoever added str_tolower() to formatting.c. If I had known about that, I could have saved myself a lot of grief! My original implementation for 8.3.1 had copied a lot of code from oracle_compat.c to get things working. With this patch, I've eliminated a whole lot of code, as I can now just call str_tolower(). So thank you for that! I'll probably keep my original in my personal Subversion repository, but don't now about releasing it if it will be accepted as a contrib module for 8.4. * All comparisons simply convert the strings to be compared to lowercase using str_tolower(). I've made no other optimizations, though I'm sure someone with more experience with collations and such could add them. * The regression test uses a new module I've created, now on pgFoundry, called pgtap. It should just work. sql/citext.sql adds plpgsql to the database and then includes pgtap.sql, which has the test functions in it. * I wrote the tests assuming a collation of en_US.UTF-8. I expect it'd work with most West European languages, and maybe all languages other than the C locale, but I'm not sure. YMMV. If there's a way to generalize it and still be able to test the locale awareness, that would be great. What locales do the build farm servers use? * In the documentation, I've pitched this type as a replacement for the use of LOWER() in ad-hoc queries, while also stipulating that this is not a "true" case-insensitive text type, and is furthermore less efficient than just TEXT (though I'm sure more efficient than ad-hock LOWER()s). I've also mentioned a few other caveats, including casts for TEXT that don't work for citext and non-case-insensitive matching in replace(), regexp_replace(), and a few others. * I wrote all the code here myself, but of course used the original citext implementation (which is case-insensitive only for ASCII characters) for inspiration and guidance. Thanks to Donald Fraser for that original implementation. I've compiled the CVS checkout, run its regressions, then built and installed the citext module (hence my discovery of the deprecation of wstring_lower and the addition of str_tolower -- should the declaration of the former be removed from formatting.c?), and all tests passed as of an hour ago. I of course welcome feedback, advice, insults, commiserations, and just about any mode of comment on this patch. Please let me know if I need to provide any additional information. Best, David citext.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Tom Lane writes: Alvaro Herrera <[EMAIL PROTECTED]> writes: Tom Lane wrote: We might have to rearrange the logic a bit to make that happen (I'm not sure what order things get tested in), but a log message does seem like a good idea. I'd go for logging anytime an orphaned table is seen, and dropping once it's past the anti-wraparound horizon. I don't think this requires much of a rearrangement -- see autovacuum.c 1921ff. So everyone is happy with the concept of doing it as above? If so, I'll work on it this weekend sometime. I think it is the most reasonable thing to do. Regarding the log messages about orphaned tables, it would be nice if you could add a hint/detail message explaining how to cleanup those tables. If that's possible. Best Regards Michael Paesold -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Tom Lane wrote: We might have to rearrange the logic a bit to make that happen (I'm not sure what order things get tested in), but a log message does seem like a good idea. I'd go for logging anytime an orphaned table is seen, and dropping once it's past the anti-wraparound horizon. Is there an easy way for an Admin clean-up the lost temp tables that autovacuum is complaining about? It seems like it could be along time and a lot of log messages between when they are first orphaned and and finally dropped due to anti-wraparound protection. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
On Wed, Jun 25, 2008 at 9:57 AM, Andrew Hammond <[EMAIL PROTECTED]> wrote: > > On Wed, Jun 25, 2008 at 2:58 AM, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: >> >> Andrew Hammond wrote: >>> >>> I found this error message in my log files repeatedly: >>> >>> Error: failed to re-find parent key in "ledgerdetail_2008_03_idx2" for >>> deletion target page 64767 >>> >>> I though "hmm, that index looks broken. I'd better re-create it." So, I >>> dropped the index and then tried to create a new one to replace it. Which >>> completely locked up the backend that was running the CREATE TABLE. I ran >>> truss against the backend in question and it didn't register anything >>> (except signals 2 and 15 when I tried to cancel the query and kill the >>> backend respectively). I eventually had to restart the database to get the >>> CREATE INDEX process to go away (well, to release that big nasty lock). >> >> What kind of an index is it? Does "SELECT COUNT(*) from " work? > > After the restart I did a count(*) and it worked. A little under 13m rows. > So, sequential scans seem to work. > >>> >>> posting here in case there's interest in gathering some forensic data or a >>> clever suggetion about how I can recover this situation or even some ideas >>> about what's causing it. >> >> Anyway, the current plan is to drop the table and reload it from backup. I'm >> >> Yes, please take a filesystem-level backup right away to retain the evidence. > > Well, I've already burned our downtime allowance for this month, but we do a > regular PITR type backup which hopefully will be sufficient to replicate the > problem. > >> >> Could you connect to the hung backend with gdb and get a stacktrace? > > The backend is no longer hung (two restarts later). I'll try to reproduce > this problem on my workstation (same binary, same OS, libraries etc) using > the PITR dump. > > Andrew I tried to restore the PITR backup and it failed. Jun 27 15:54:30 qadb2 postgres[92517]: [1-1] DEBUG: postmaster: PostmasterMain: initial environ dump: Jun 27 15:54:30 qadb2 postgres[92517]: [2-1] DEBUG: - Jun 27 15:54:30 qadb2 postgres[92517]: [3-1] DEBUG: USER=pgsql Jun 27 15:54:30 qadb2 postgres[92517]: [4-1] DEBUG: MAIL=/var/mail/pgsql Jun 27 15:54:30 qadb2 postgres[92517]: [5-1] DEBUG: LD_LIBRARY_PATH=:/usr/local/adecn/lib Jun 27 15:54:30 qadb2 postgres[92517]: [6-1] DEBUG: HOME=/usr/local/pgsql Jun 27 15:54:30 qadb2 postgres[92517]: [7-1] DEBUG: PGLIB=/usr/local/lib Jun 27 15:54:30 qadb2 postgres[92517]: [8-1] DEBUG: PS1=[QA2] [EMAIL PROTECTED]:\w\$ Jun 27 15:54:30 qadb2 postgres[92517]: [9-1] DEBUG: BLOCKSIZE=K Jun 27 15:54:30 qadb2 postgres[92517]: [10-1] DEBUG:TERM=xterm Jun 27 15:54:30 qadb2 postgres[92517]: [11-1] DEBUG: PGSYSCONFDIR=/usr/local/etc/postgresql Jun 27 15:54:30 qadb2 postgres[92517]: [12-1] DEBUG: PGLOCALEDIR=/usr/local/share/locale Jun 27 15:54:30 qadb2 postgres[92517]: [13-1] DEBUG: Jun 27 15:54:30 qadb2 postgres[92517]: [13-2] PATH=/usr/local/adecn/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/X11R6/bin Jun 27 15:54:30 qadb2 postgres[92517]: [13-3] :/usr/local/pgsql/bin Jun 27 15:54:30 qadb2 postgres[92517]: [14-1] DEBUG: ADECN_HOME=/usr/local/adecn Jun 27 15:54:30 qadb2 postgres[92517]: [15-1] DEBUG:SHELL=/bin/sh Jun 27 15:54:30 qadb2 postgres[92517]: [16-1] DEBUG: Jun 27 15:54:30 qadb2 postgres[92517]: [16-2] CLASSPATH= (deleted a bunch of lines) Jun 27 15:54:30 qadb2 postgres[92517]: [17-1] DEBUG: PYTHONPATH=/usr/local/adecn/python:/usr/local/adecn/lib/python:/usr/local/adecn/api/client/python Jun 27 15:54:30 qadb2 postgres[92517]: [18-1] DEBUG:FTP_PASSIVE_MODE=YES Jun 27 15:54:30 qadb2 postgres[92517]: [19-1] DEBUG: PGDATA=/var/db/adecn/adecndb Jun 27 15:54:30 qadb2 postgres[92517]: [20-1] DEBUG:LC_COLLATE=C Jun 27 15:54:30 qadb2 postgres[92517]: [21-1] DEBUG:LC_CTYPE=C Jun 27 15:54:30 qadb2 postgres[92517]: [22-1] DEBUG:LC_MESSAGES=C Jun 27 15:54:30 qadb2 postgres[92517]: [23-1] DEBUG:LC_MONETARY=C Jun 27 15:54:30 qadb2 postgres[92517]: [24-1] DEBUG:LC_NUMERIC=C Jun 27 15:54:30 qadb2 postgres[92517]: [25-1] DEBUG:LC_TIME=C Jun 27 15:54:30 qadb2 postgres[92517]: [26-1] DEBUG: - Jun 27 15:54:30 qadb2 postgres[92518]: [27-1] DEBUG: invoking IpcMemoryCreate(size=92938240) Jun 27 15:54:30 qadb2 postgres[92518]: [28-1] DEBUG: max_safe_fds = 983, usable_fds = 1000, already_open = 7 Jun 27 15:54:30 qadb2 postgres[92519]: [29-1] LOG: database system was interrupted at 2008-06-25 03:01:02 PDT Jun 27 15:54:30 qadb2 postgres[92519]: [30-1] LOG: starting archive recovery Jun 27 15:54:30 qadb2 postgres[92519]: [31-1] LOG: restore_command = "cp -p /usr/tmp/2008-06-25_wals/%f %p" Jun 27 15:54:30 qadb2 postgres[92519]: [32-1] DEBUG: executing restore command "cp -p /usr/tmp/2008-06-25_wals/0001.history pg_xlog/RECOVERYHISTORY" Jun 27 15:54:30 q
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> We might have to rearrange the logic a bit to make that happen (I'm not >> sure what order things get tested in), but a log message does seem like >> a good idea. I'd go for logging anytime an orphaned table is seen, >> and dropping once it's past the anti-wraparound horizon. > I don't think this requires much of a rearrangement -- see autovacuum.c > 1921ff. So everyone is happy with the concept of doing it as above? If so, I'll work on it this weekend sometime. 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] Table inheritance surprise
Simon Riggs <[EMAIL PROTECTED]> writes: > On Thu, 2008-06-26 at 18:53 -0400, Bruce Momjian wrote: >> TODO has: >> >> o Allow inherited tables to inherit indexes, UNIQUE constraints, >> and primary/foreign keys But that TODO item is about inheritance, which has approximately zip to do with CREATE TABLE LIKE. > INCLUDING CONSTRAINTS is non-standard so maybe we can just make > INCLUDING CONSTRAINTS also include foreign keys. In a green field that would probably make sense, but it'd create a significant backward compatibility problem to do it now. Undoubtedly there are apps depending on the current behavior of LIKE INCLUDING CONSTRAINTS. (If there are not, then the feature is useless and we should take it out instead of extending it.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] VirtualXactLockTableInsert
Simon Riggs <[EMAIL PROTECTED]> writes: > In cases where we know we will assign a real xid, can we just skip the > assignment of the virtual xid completely? Even if we could do this I doubt it would be a good idea. It'd destroy the invariant that all transactions have a vxid, which at the very least would create naming problems. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] XML index support
Dear friends, You may know me as I worked a long time ago on pgAdmin I with Dave. As this is an XML related question and XML is quite new, I am posting on hackers ML. We would like to develop a free REST database (real-estate standard) based on a PostgreSQL schema. This is a free solution for free data also, released under BSD or GPL license. We are charity. This is supposed to a "killer application", so we need to drive down queries to 3ms to 5ms to allow hundreds of simultaneous queries. Php will probably be part of the package. What is in your opinion the best way to achive this : * develop a traditional databe and implement materialized views in PL OR * implement the new XML type with some index (GIST) designed for PostgreSQL. Which one? OR * write triggers behind XML tables to pullulate a traditionnal database, add indexes and query traditionnal database. Kind regards, Jean-Michel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] XML index support
Dear friends, You may know me as I worked a long time ago on pgAdmin I with Dave. As this is an XML related question and XML is quite new, I am posting on hackers ML. If any solution is being developped, please inform us. We would like to develop a free REST database (real-estate standard) based on a PostgreSQL schema. This is a free solution for free data also, released under BSD or GPL license. We are charity. This is supposed to a "killer application", so we need to drive down queries to 3ms to 5ms to allow hundreds of simultaneous queries. Php will probably be part of the package. What is in your opinion the best way to achive this : * develop a traditional databe and implement materialized views in PL OR * implement the new XML type with some index (GIST) designed for PostgreSQL. Which one? OR * write triggers behind XML tables to pullulate a traditionnal database, add indexes and query traditionnal database. Kind regards, Jean-Michel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Join Removal/ Vertical Partitioning
Simon Riggs <[EMAIL PROTECTED]> writes: > On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote: >> It might be possible to treat "ignore the RHS" as a join strategy and >> try to apply it while forming join relations, which would be late enough >> to have all the needed info available. > Oh, actually have a join node that is a no-op, with a path cost of zero? Not even that: just return the best path(s) for the LHS as the paths for the joinrel. 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] gsoc, text search selectivity and dllist enhancments
Jan, > Hm... someone apparently added this mail to the wiki pag independently > of me, so there were two duplicate entries. I found the second > description better, so I removed my original entry and left the other > one. Yeah, I've been going through -hackers and -patches and adding stuff to the wiki page. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gsoc, text search selectivity and dllist enhancments
Jan Urbański wrote: I'll add the first one to the commit fest page, and I'm sending it to -hackers with congratulations on the decision to ditch -patches ;) Hm... someone apparently added this mail to the wiki pag independently of me, so there were two duplicate entries. I found the second description better, so I removed my original entry and left the other one. -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Latest on CITEXT 2.0
On Jun 26, 2008, at 13:59, Tom Lane wrote: "David E. Wheeler" <[EMAIL PROTECTED]> writes: So, are your certain about this? See Turkish --- in that locale i and I are not an upper/lower pair, instead they pair with some non-ASCII letters. There are likely other cases but that's the counterexample I remember. Perfect, thank you. I was able to add a failing test and make it pass by removing the string length optimization. For future reference of anyone reading the list, this page has a great description of the problem: http://www.i18nguy.com/unicode/turkish-i18n.html 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] Vacuuming leaked temp tables (once again)
Heikki Linnakangas wrote: Or we could have autovacuum just drop orphaned temp tables, *if* they have gotten old enough to need anti-wraparound vacuuming. While I'm still uncomfortable with having autovac drop anything, at least this would avoid the worst cases of "gee I really needed that data to investigate the crash". The main attractions of this idea are avoiding the corrupt-index issue and not doing vacuuming work that's 99.99% sure to be useless. That sounds a lot simpler and better to me. Yeah, when I read the original this one struck me as almost a no-brainer choice. 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] Vacuuming leaked temp tables (once again)
Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > > Could autovacuum emit log messages as soon as it sees such tables and start > > dropping them at some point later? > > We might have to rearrange the logic a bit to make that happen (I'm not > sure what order things get tested in), but a log message does seem like > a good idea. I'd go for logging anytime an orphaned table is seen, > and dropping once it's past the anti-wraparound horizon. I don't think this requires much of a rearrangement -- see autovacuum.c 1921ff. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Richard Huxton wrote: Richard Huxton wrote: At present it means you can't reliably do: DROP DATABASE foo; pg_restore --create foo.dump I'd then have to either hand edit the dumpall dump or wade through a bunch of errors checking that none of them were relevant. Actually, I'm not sure pg_dumpall does them either. [snip] Am I doing something stupid here? OK - so to get the ALTER DATABASE commands I need to dump the schema for the entire cluster. Is that really desired behaviour? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Richard Huxton wrote: At present it means you can't reliably do: DROP DATABASE foo; pg_restore --create foo.dump I'd then have to either hand edit the dumpall dump or wade through a bunch of errors checking that none of them were relevant. Actually, I'm not sure pg_dumpall does them either. tracker=> SELECT name,setting,source FROM pg_settings WHERE name = 'DateStyle'; name| setting | source ---+--+-- DateStyle | SQL, DMY | database (1 row) pg_dumpall -U postgres -p 5483 -g > tracker.global.schema pg_dump -U postgres -p 5483 --schema-only > tracker.schema grep -i datestyle tracker*schema That's with 8.3.3 Am I doing something stupid here? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Stephen Frost wrote: > * Tom Lane ([EMAIL PROTECTED]) wrote: > > The only solution proposed in that thread was to auto-delete temp > > tables at postmaster restart; which I opposed on the grounds that > > throwing away data right after a crash was a terrible idea from a > > forensic standpoint. > > Why not just rename the files out of the way, and nuke the entries from > the catalog? Something like "filename.crash" or similar that an admin > can have scripts in place to check for and who could then go handle as > appropriate (remove, investigate, etc). This was my thought too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Stephen Frost <[EMAIL PROTECTED]> writes: > * Tom Lane ([EMAIL PROTECTED]) wrote: >> The only solution proposed in that thread was to auto-delete temp >> tables at postmaster restart; which I opposed on the grounds that >> throwing away data right after a crash was a terrible idea from a >> forensic standpoint. > Why not just rename the files out of the way, and nuke the entries from > the catalog? It's usually tough to make any sense of the contents of a table if you don't have the catalog entries. Anyway, that approach would put the onus on the admin to clean things up eventually, which isn't all that appealing. Bear in mind that temp table contents are subject to summary deletion during normal operation anyway. What I opposed back in January was deleting them *immediately* after a crash, but that doesn't mean I'm in favor of keeping them indefinitely. 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] Vacuuming leaked temp tables (once again)
* Tom Lane ([EMAIL PROTECTED]) wrote: > The only solution proposed in that thread was to auto-delete temp > tables at postmaster restart; which I opposed on the grounds that > throwing away data right after a crash was a terrible idea from a > forensic standpoint. Why not just rename the files out of the way, and nuke the entries from the catalog? Something like "filename.crash" or similar that an admin can have scripts in place to check for and who could then go handle as appropriate (remove, investigate, etc). If there's data in the catalog that you think might be bad to lose, then include it in some kind of format in a "filename.crash.catalog" or similar file. Maybe also spit out a warning or error or something on backend start when this rename is done, and on subsequent starts if the file remains. What I really don't like is keeping something that is likely useless and possibly deadly to a backend if corrupt & accessed sitting around. I'm also not a big fan of keeping what is essentially 'garbage' around in the catalog which could just lead to confusion later if someone's looking at "what actual temporary tables should there be" and seeing others that they wouldn't expect. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Gregory Stark <[EMAIL PROTECTED]> writes: > Could autovacuum emit log messages as soon as it sees such tables and start > dropping them at some point later? We might have to rearrange the logic a bit to make that happen (I'm not sure what order things get tested in), but a log message does seem like a good idea. I'd go for logging anytime an orphaned table is seen, and dropping once it's past the anti-wraparound horizon. 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] MSVC 2003 compile error with pg8.3.3
Hi Jeff-san. Thanks!! The version which you use is supported officially. http://www.postgresql.org/docs/8.3/static/install-win32-libpq.html To build the libpq client library using Visual Studio 7.1 or later. However, adjustment may be necessity. I will propose patch.! Regards, Hiroshi Saito - Original Message - From: "Jeff McKenna" <[EMAIL PROTECTED]> Hiroshi-san, "All win32 parts have been built!" That last file that you sent worked with MSVC 2003, and libpq.dll was successfully built. Thank you very much for fixing this for me. Will this be committed to CVS head for others? --- Jeff McKenna FOSS4G Consulting and Training Services http://www.gatewaygeomatics.com/ On 27-Jun-08, at 11:55 AM, Hiroshi Saito wrote: Ooops, I am sorry so that it may be spam. ... I have noticed, although there were few conditions. pass the VC6,VC7,VC71 Regards, Hiroshi Saito - Original Message - From: "Hiroshi Saito" <[EMAIL PROTECTED] > Hi Jeff-san. Uga,,, Ok. Please try this. Thanks your perseverance. !! Regards, Hiroshi Saito - Original Message - From: "Jeff McKenna" <[EMAIL PROTECTED] > Hello Hiroshi-san, Your new win32.mak causes the following error with MSVC 2003: Creating library .\Release\libpqdll.lib and object .\Release \libpqdll.exp mt -manifest .\Release\libpq.dll.manifest -outputresource:. \Release\libp q.dll;2 Microsoft (R) Manifest Tool version 6.0.4071.0 Copyright (c) Microsoft Corporation 2004. All rights reserved. .\Release\libpq.dll.manifest:general error c1010070:Failed to load and parse the manifest. The system cannot find the file specified. NMAKE : fatal error U1077: 'mt' : return code '0x1f' Stop. NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\ VC7\BIN\nmake.exe"' : return code '0x2' Stop. --- Jeff McKenna FOSS4G Consulting and Training Services http://www.gatewaygeomatics.com/ On 26-Jun-08, at 11:28 PM, Hiroshi Saito wrote: Hi Jeff-san. Would you replace this with src/interfaces/libpq/win32.mak and try it? I checked that there was no problem in construction of VC2005 by this change. Then, I think if you solve a problem and it should apply. Regards, Hiroshi Saito -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
Tom Lane wrote: Another issue is that leftover temp tables would be significantly more likely to be self-inconsistent than normal tables, since operations on them are not WAL-logged and it's entirely likely that the owning backend crashed with some dirty pages not written out from its local buffers. AFAICS this shouldn't be any big problem for vacuuming the table proper, since heap pages are pretty independent, at least at the level understood by plain vacuum. There's the torn-page problem as well. Highly improbable, but it seems possible to me to have an inconsistent heap page with for example broken redirecting line pointers or something like that, that would cause crashes or assertion failures on vacuum. Or we could have autovacuum just drop orphaned temp tables, *if* they have gotten old enough to need anti-wraparound vacuuming. While I'm still uncomfortable with having autovac drop anything, at least this would avoid the worst cases of "gee I really needed that data to investigate the crash". The main attractions of this idea are avoiding the corrupt-index issue and not doing vacuuming work that's 99.99% sure to be useless. That sounds a lot simpler and better to me. -- 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] Planner creating ineffective plans on LEFT OUTER joins
Tom Lane wrote: Simon Riggs <[EMAIL PROTECTED]> writes: On Thu, 2008-06-26 at 12:36 -0400, Robert Haas wrote: It's my understanding that the philosophy of the PGDG in the past has been to avoid putting any kind of hints into the system, focusing rather an improving the planning of queries. It's not a specific hint, its a general goal setting. Right. There are definitely places where we've made engineering judgements to not attempt a particular type of optimization because it'd be too expensive compared to the typical payoff. Simon's idea has some merit for providing a framework to deal with that type of situation. However, just adding a GUC variable isn't going to make anything happen --- we'd need some concrete plans about what we'd do with it. If the planner provided some facility to control how much effort it puts into planning, we could even tune that knob automatically with something like plan = plan_query(effort=0); if (estimated_execution_cost > triviality_threshold) plan = plan_query(estimated_execution_cost * effort_by_cost_ratio); regards, Forian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MSVC 2003 compile error with pg8.3.3
Hiroshi-san, "All win32 parts have been built!" That last file that you sent worked with MSVC 2003, and libpq.dll was successfully built. Thank you very much for fixing this for me. Will this be committed to CVS head for others? --- Jeff McKenna FOSS4G Consulting and Training Services http://www.gatewaygeomatics.com/ On 27-Jun-08, at 11:55 AM, Hiroshi Saito wrote: Ooops, I am sorry so that it may be spam. ... I have noticed, although there were few conditions. pass the VC6,VC7,VC71 Regards, Hiroshi Saito - Original Message - From: "Hiroshi Saito" <[EMAIL PROTECTED] > Hi Jeff-san. Uga,,, Ok. Please try this. Thanks your perseverance. !! Regards, Hiroshi Saito - Original Message - From: "Jeff McKenna" <[EMAIL PROTECTED] > Hello Hiroshi-san, Your new win32.mak causes the following error with MSVC 2003: Creating library .\Release\libpqdll.lib and object .\Release \libpqdll.exp mt -manifest .\Release\libpq.dll.manifest -outputresource:. \Release\libp q.dll;2 Microsoft (R) Manifest Tool version 6.0.4071.0 Copyright (c) Microsoft Corporation 2004. All rights reserved. .\Release\libpq.dll.manifest:general error c1010070:Failed to load and parse the manifest. The system cannot find the file specified. NMAKE : fatal error U1077: 'mt' : return code '0x1f' Stop. NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\ VC7\BIN\nmake.exe"' : return code '0x2' Stop. --- Jeff McKenna FOSS4G Consulting and Training Services http://www.gatewaygeomatics.com/ On 26-Jun-08, at 11:28 PM, Hiroshi Saito wrote: Hi Jeff-san. Would you replace this with src/interfaces/libpq/win32.mak and try it? I checked that there was no problem in construction of VC2005 by this change. Then, I think if you solve a problem and it should apply. Regards, Hiroshi Saito -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuuming leaked temp tables (once again)
"Tom Lane" <[EMAIL PROTECTED]> writes: > The main attractions of this idea are avoiding the corrupt-index issue and > not doing vacuuming work that's 99.99% sure to be useless. It does seem strange to me to vacuum a table you're pretty sure is useless *and* quite likely corrupt. Could autovacuum emit log messages as soon as it sees such tables and start dropping them at some point later? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Hint Bits and Write I/O
Gregory Stark wrote: I'm also a bit concerned that *how many hint bits* isn't enough information to determine how important it is to write out the page. Agreed, that doesn't seem like a very good metric to me either. Or how many *unhinted* xmin/xmax values were found? If HTSV can hint xmin for a tuple but finds xmax still in progress perhaps that's a good sign it's not worth dirtying the page? I like that thought. Overall, I feel that we should never dirty when setting a hint bit, just set the separate buffer flag to indicate that hint bits have been set. The decision to dirty and write out, or not, should be delayed until we're about to write/replace the buffer. That is, in bgwriter. How about this strategy: 1. First of all, before writing a dirty buffer, scan all tuples on the page and set all hint bits that can be set. This will hopefully save us from having to dirty the page again in the future, when another tuple on the page is accessed. This has been proposed before, and IIRC Tom has argued that it's a modularity violation for bgwriter to access the contents of pages like that, but I'm sure we can find a way to do it safely. 2. When bgwriter encounters a page that's marked as "hint bits dirty", write it only if *all* hint bits on the page has been, or can be, set. Dirtying a page before that point doesn't seem worthwhile, as the next access to the tuple that doesn't have all the hint bits set will have to dirty the page again. Actually, I'd like to see some benchmarks on an even simpler strategy: just never dirty a page just because a hint bit has been set. It might work surprisingly well in practice: If a database is I/O bound, we don't care about the extra CPU work or lock congestion of checking the clog. If it's CPU bound, the active pages that matter are in the buffer cache, and so are the hint bits for those pages. -- 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] MSVC 2003 compile error with pg8.3.3
Ooops, I am sorry so that it may be spam. ...
I have noticed, although there were few conditions.
pass the VC6,VC7,VC71
Regards,
Hiroshi Saito
- Original Message -
From: "Hiroshi Saito" <[EMAIL PROTECTED]>
Hi Jeff-san.
Uga,,, Ok.
Please try this.
Thanks your perseverance. !!
Regards,
Hiroshi Saito
- Original Message -
From: "Jeff McKenna" <[EMAIL PROTECTED]>
Hello Hiroshi-san,
Your new win32.mak causes the following error with MSVC 2003:
Creating library .\Release\libpqdll.lib and object .\Release
\libpqdll.exp
mt -manifest .\Release\libpq.dll.manifest -outputresource:.
\Release\libp
q.dll;2
Microsoft (R) Manifest Tool version 6.0.4071.0
Copyright (c) Microsoft Corporation 2004.
All rights reserved.
.\Release\libpq.dll.manifest:general error c1010070:Failed to load and
parse the
manifest. The system cannot find the file specified.
NMAKE : fatal error U1077: 'mt' : return code '0x1f'
Stop.
NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual
Studio .NET 2003\
VC7\BIN\nmake.exe"' : return code '0x2'
Stop.
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/
On 26-Jun-08, at 11:28 PM, Hiroshi Saito wrote:
Hi Jeff-san.
Would you replace this with src/interfaces/libpq/win32.mak and try
it? I checked that there was no problem in construction of VC2005 by
this change. Then, I think if you solve a problem and it should apply.
Regards,
Hiroshi Saito
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
# Makefile for Microsoft Visual C++ 7.1-8.0
# Will build a static library libpq(d).lib
#and a dynamic library libpq(d).dll with import library libpq(d)dll.lib
# USE_SSL=1 will compile with OpenSSL
# USE_KFW=1 will compile with kfw(kerberos for Windows)
# DEBUG=1 compiles with debugging symbols
# ENABLE_THREAD_SAFETY=1 compiles with threading enabled
ENABLE_THREAD_SAFETY=1
# CPU="i386" or CPU environment of nmake.exe (AMD64 or IA64)
!IF ("$(CPU)" == "")||("$(CPU)" == "i386")
CPU=i386
!MESSAGE Building the Win32 static library...
!MESSAGE
!ELSEIF ("$(CPU)" == "IA64")||("$(CPU)" == "AMD64")
ADD_DEFINES=/D "WIN64" /Wp64 /GS
ADD_SECLIB=bufferoverflowU.lib
!MESSAGE Building the Win64 static library...
!MESSAGE
!ELSE
!MESSAGE Please check a CPU=$(CPU) ?
!MESSAGE CPU=i386 or AMD64 or IA64
!ERROR Make aborted.
!ENDIF
!IFDEF DEBUG
OPT=/Od /Zi /MDd
LOPT=/DEBUG
DEBUGDEF=/D _DEBUG
OUTFILENAME=libpqd
!ELSE
OPT=/O2 /MD
LOPT=
DEBUGDEF=/D NDEBUG
OUTFILENAME=libpq
!ENDIF
!IF "$(SSL_INC)" == ""
SSL_INC=C:\OpenSSL\include
!MESSAGE Using default OpenSSL Include directory: $(SSL_INC)
!ENDIF
!IF "$(SSL_LIB_PATH)" == ""
SSL_LIB_PATH=C:\OpenSSL\lib\VC
!MESSAGE Using default OpenSSL Library directory: $(SSL_LIB_PATH)
!ENDIF
!IF "$(KFW_INC)" == ""
KFW_INC=C:\kfw-2.6.5\inc
!MESSAGE Using default Kerberos Include directory: $(KFW_INC)
!ENDIF
!IF "$(KFW_LIB_PATH)" == ""
KFW_LIB_PATH=C:\kfw-2.6.5\lib\$(CPU)
!MESSAGE Using default Kerberos Library directory: $(KFW_LIB_PATH)
!ENDIF
!IF "$(OS)" == "Windows_NT"
NULL=
!ELSE
NULL=nul
!ENDIF
CPP=cl.exe
RSC=rc.exe
!IFDEF DEBUG
OUTDIR=.\Debug
INTDIR=.\Debug
CPP_OBJS=.\Debug/
!ELSE
OUTDIR=.\Release
INTDIR=.\Release
CPP_OBJS=.\Release/
!ENDIF
ALL : config "$(OUTDIR)\$(OUTFILENAME).lib" "$(OUTDIR)\$(OUTFILENAME).dll"
CLEAN :
[EMAIL PROTECTED] "$(INTDIR)\getaddrinfo.obj"
[EMAIL PROTECTED] "$(INTDIR)\pgstrcasecmp.obj"
[EMAIL PROTECTED] "$(INTDIR)\thread.obj"
[EMAIL PROTECTED] "$(INTDIR)\inet_aton.obj"
[EMAIL PROTECTED] "$(INTDIR)\crypt.obj"
[EMAIL PROTECTED] "$(INTDIR)\noblock.obj"
[EMAIL PROTECTED] "$(INTDIR)\md5.obj"
[EMAIL PROTECTED] "$(INTDIR)\ip.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-auth.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-protocol2.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-protocol3.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-connect.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-exec.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-lobj.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-misc.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-print.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-secure.obj"
[EMAIL PROTECTED] "$(INTDIR)\pqexpbuffer.obj"
[EMAIL PROTECTED] "$(INTDIR)\pqsignal.obj"
[EMAIL PROTECTED] "$(INTDIR)\win32.obj"
[EMAIL PROTECTED] "$(INTDIR)\wchar.obj"
[EMAIL PROTECTED] "$(INTDIR)\encnames.obj"
[EMAIL PROTECTED] "$(INTDIR)\pthread-win32.obj"
[EMAIL PROTECTED] "$(INTDIR)\snprintf.obj"
[EMAIL PROTECTED] "$(INTDIR)\strlcpy.obj"
[EMAIL PROTECTED] "$(INTDIR)\dir
[HACKERS] Vacuuming leaked temp tables (once again)
This thread http://archives.postgresql.org/pgsql-hackers/2008-01/msg00134.php kind of wandered off into the weeds after identifying a semi-related bug in CLUSTER, but the original problem still remains: if a backend crashes after creating some temp tables, the tables remain present. Such tables will get recycled next time someone reuses the same pg_temp_NNN schema. But if the failed backend had been occupying an unusually high-numbered BackendId slot, then its pg_temp_NNN schema might go unused for a long time --- long enough for the temp tables to pose an xid-wraparound problem. There's another report of this issue today in pgsql-general. The only solution proposed in that thread was to auto-delete temp tables at postmaster restart; which I opposed on the grounds that throwing away data right after a crash was a terrible idea from a forensic standpoint. I still think that, but I had another idea about how to cope with the situation. It's reasonably easy to tell (by looking into the sinval state) whether a given BackendId slot is actually in use, so we could detect whether a temp table actually belongs to a live backend or not. What I'm thinking is we should adjust autovacuum so that it will apply anti-wraparound vacuuming operations even to temp tables, if they belong to pg_temp schemas that belong to inactive BackendId slots. This'd fix the wraparound issue without any risk of discarding data that someone might want back. Note that this should be safe even if someone claims the pg_temp_NNN schema and tries to drop the old temp table while we're vacuuming it. Operations on temp tables take the normal types of locks, so that will get interlocked properly. A small hole in this idea is that the BackendId slot might be occupied by some new backend that actually hasn't created any temp tables yet (hence not "taken possession" of the pg_temp_NNN schema). We could fix that by making each backend's has-temp-tables state globally visible. However, I'm inclined to think it's not really an issue, because you wouldn't get into trouble unless this was always the case over many repeated autovacuum visits to the table, which seems pretty improbable. Another issue is that leftover temp tables would be significantly more likely to be self-inconsistent than normal tables, since operations on them are not WAL-logged and it's entirely likely that the owning backend crashed with some dirty pages not written out from its local buffers. AFAICS this shouldn't be any big problem for vacuuming the table proper, since heap pages are pretty independent, at least at the level understood by plain vacuum. There is a risk that indexes would be corrupt enough to make vacuum error out, thus preventing the xid wraparound cleanup from completing. But that leaves us no worse off than we are now, and at least there would be signs of distress in the postmaster log for the DBA to see. Or we could have autovacuum just drop orphaned temp tables, *if* they have gotten old enough to need anti-wraparound vacuuming. While I'm still uncomfortable with having autovac drop anything, at least this would avoid the worst cases of "gee I really needed that data to investigate the crash". The main attractions of this idea are avoiding the corrupt-index issue and not doing vacuuming work that's 99.99% sure to be useless. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] VirtualXactLockTableInsert
Simon Riggs wrote: When we move from having a virtual xid to having a real xid I don't see any attempt to re-arrange the lock queues. Surely if there are people waiting on the virtual xid, they must be moved across to wait on the actual xid? Otherwise the locking queue will not be respected because we have two things on which people might queue. Anybody explain that? Locks on real xids serve a different purpose than locks on virtual xids. Locks on real xids are used to wait for transaction who touched a certain tuple (in which case they certainly must have acquired a real xid) to end. Locks on vxids on the other hand are used to wait for the ending of transactions which either hold a certain lock or use a snapshot with a xmin earlier than some point in time. indexcmds.c is the only place where VirtualXactLockTableWait() is used - the concurrent index creation needs to wait for all transactions to end which either might not know about the index (after phase 1 and 2), or who might still see tuples not included in the index (before marking the index valid). In cases where we know we will assign a real xid, can we just skip the assignment of the virtual xid completely? For example, where an implicit transaction is started by a DML statement. Otherwise we have to wait for 2 lock table inserts, not just one. A more general solution would be to get rid of vxid locks completly. This is possible if we figure out a way to handle the first two waiting phases or concurrent index builds in another way. One idea I had for approaching this was to extend the lock manager by adding some sort of WaitForCurrentLockHolders(LockTag) function. I felt (and still feel) feel I didn't understand the locking code well enough to start hacking it though, and Tom didn't like the idea either. His argument was that it wasn't clear how deadlock detection would cope with such a facility IIRC. regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER DATABASE vs pg_dump
Tom Lane wrote: Richard Huxton <[EMAIL PROTECTED]> writes: Is it desirable that pg_dump doesn't dump config settings set via ALTER DATABASE? Well, it's intentional anyway: that's handled by pg_dumpall. The basic design is that anything that can be seen from "outside" a specific database is handled on the pg_dumpall side. Well, global settings and per-user settings are clearly global. I'm not sure that per-database settings are "logically" global, although I'll accept that's how they're stored. At present it means you can't reliably do: DROP DATABASE foo; pg_restore --create foo.dump I'd then have to either hand edit the dumpall dump or wade through a bunch of errors checking that none of them were relevant. I just got bitten by a DateStyle not being restored on my test DB You could also get bitten by not having restored users or tablespaces that the dump depends on, so I'm not sure there's a strong argument here for refactoring the responsibility. Yep, but that will give you a "no such role" error when you try to restore. This is a situation where you can restore without errors and end up with different behaviour: dd/mm/ vs mm/dd/ or text-search stop-words changing. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MSVC 2003 compile error with pg8.3.3
Hi Jeff-san.
Uga,,, Ok.
Please try this.
Thanks your perseverance. !!
Regards,
Hiroshi Saito
- Original Message -
From: "Jeff McKenna" <[EMAIL PROTECTED]>
Hello Hiroshi-san,
Your new win32.mak causes the following error with MSVC 2003:
Creating library .\Release\libpqdll.lib and object .\Release
\libpqdll.exp
mt -manifest .\Release\libpq.dll.manifest -outputresource:.
\Release\libp
q.dll;2
Microsoft (R) Manifest Tool version 6.0.4071.0
Copyright (c) Microsoft Corporation 2004.
All rights reserved.
.\Release\libpq.dll.manifest:general error c1010070:Failed to load and
parse the
manifest. The system cannot find the file specified.
NMAKE : fatal error U1077: 'mt' : return code '0x1f'
Stop.
NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual
Studio .NET 2003\
VC7\BIN\nmake.exe"' : return code '0x2'
Stop.
---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/
On 26-Jun-08, at 11:28 PM, Hiroshi Saito wrote:
Hi Jeff-san.
Would you replace this with src/interfaces/libpq/win32.mak and try
it? I checked that there was no problem in construction of VC2005 by
this change. Then, I think if you solve a problem and it should apply.
Regards,
Hiroshi Saito
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
# Makefile for Microsoft Visual C++ 7.1-8.0
# Will build a static library libpq(d).lib
#and a dynamic library libpq(d).dll with import library libpq(d)dll.lib
# USE_SSL=1 will compile with OpenSSL
# USE_KFW=1 will compile with kfw(kerberos for Windows)
# DEBUG=1 compiles with debugging symbols
# ENABLE_THREAD_SAFETY=1 compiles with threading enabled
ENABLE_THREAD_SAFETY=1
# CPU="i386" or CPU environment of nmake.exe (AMD64 or IA64)
!IF ("$(CPU)" == "")||("$(CPU)" == "i386")
CPU=i386
!MESSAGE Building the Win32 static library...
!MESSAGE
!ELSEIF ("$(CPU)" == "IA64")||("$(CPU)" == "AMD64")
ADD_DEFINES=/D "WIN64" /Wp64 /GS
ADD_SECLIB=bufferoverflowU.lib
!MESSAGE Building the Win64 static library...
!MESSAGE
!ELSE
!MESSAGE Please check a CPU=$(CPU) ?
!MESSAGE CPU=i386 or AMD64 or IA64
!ERROR Make aborted.
!ENDIF
!IFDEF DEBUG
OPT=/Od /Zi /MDd
LOPT=/DEBUG
DEBUGDEF=/D _DEBUG
OUTFILENAME=libpqd
!ELSE
OPT=/O2 /MD
LOPT=
DEBUGDEF=/D NDEBUG
OUTFILENAME=libpq
!ENDIF
!IF "$(SSL_INC)" == ""
SSL_INC=C:\OpenSSL\include
!MESSAGE Using default OpenSSL Include directory: $(SSL_INC)
!ENDIF
!IF "$(SSL_LIB_PATH)" == ""
SSL_LIB_PATH=C:\OpenSSL\lib\VC
!MESSAGE Using default OpenSSL Library directory: $(SSL_LIB_PATH)
!ENDIF
!IF "$(KFW_INC)" == ""
KFW_INC=C:\kfw-2.6.5\inc
!MESSAGE Using default Kerberos Include directory: $(KFW_INC)
!ENDIF
!IF "$(KFW_LIB_PATH)" == ""
KFW_LIB_PATH=C:\kfw-2.6.5\lib\$(CPU)
!MESSAGE Using default Kerberos Library directory: $(KFW_LIB_PATH)
!ENDIF
!IF "$(OS)" == "Windows_NT"
NULL=
!ELSE
NULL=nul
!ENDIF
CPP=cl.exe
RSC=rc.exe
!IFDEF DEBUG
OUTDIR=.\Debug
INTDIR=.\Debug
CPP_OBJS=.\Debug/
!ELSE
OUTDIR=.\Release
INTDIR=.\Release
CPP_OBJS=.\Release/
!ENDIF
ALL : config "$(OUTDIR)\$(OUTFILENAME).lib" "$(OUTDIR)\$(OUTFILENAME).dll"
CLEAN :
[EMAIL PROTECTED] "$(INTDIR)\getaddrinfo.obj"
[EMAIL PROTECTED] "$(INTDIR)\pgstrcasecmp.obj"
[EMAIL PROTECTED] "$(INTDIR)\thread.obj"
[EMAIL PROTECTED] "$(INTDIR)\inet_aton.obj"
[EMAIL PROTECTED] "$(INTDIR)\crypt.obj"
[EMAIL PROTECTED] "$(INTDIR)\noblock.obj"
[EMAIL PROTECTED] "$(INTDIR)\md5.obj"
[EMAIL PROTECTED] "$(INTDIR)\ip.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-auth.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-protocol2.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-protocol3.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-connect.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-exec.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-lobj.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-misc.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-print.obj"
[EMAIL PROTECTED] "$(INTDIR)\fe-secure.obj"
[EMAIL PROTECTED] "$(INTDIR)\pqexpbuffer.obj"
[EMAIL PROTECTED] "$(INTDIR)\pqsignal.obj"
[EMAIL PROTECTED] "$(INTDIR)\win32.obj"
[EMAIL PROTECTED] "$(INTDIR)\wchar.obj"
[EMAIL PROTECTED] "$(INTDIR)\encnames.obj"
[EMAIL PROTECTED] "$(INTDIR)\pthread-win32.obj"
[EMAIL PROTECTED] "$(INTDIR)\snprintf.obj"
[EMAIL PROTECTED] "$(INTDIR)\strlcpy.obj"
[EMAIL PROTECTED] "$(INTDIR)\dirent.obj"
[EMAIL PROTECTED] "$(INTDIR)\dirmod.obj"
[EMAIL PROTECTED] "$(INTDIR)\pgsleep.obj"
[EMAIL PROTECTED] "$(INTDIR)\open.obj"
[EMAIL PROTECTED] "$(INTDIR)\win32error.obj"
[EMAIL PROTECTED] "$(OUTDIR)\$(OUTFILENAME).lib"
[EMAIL PROTECTED] "$(OUTDIR)\$(OUTFILENAME)dll.lib"
[EMAIL PROTECTED] "$(OUTDIR)\libpq.res"
[EMAIL PROTECTED] "$(OUTDIR)\$(OUTFILENAME).dll"
[EMAIL PROTECTED] "$(OUTDIR)\$(O
Re: [HACKERS] Hint Bits and Write I/O
On Fri, 2008-06-27 at 15:36 +0100, Gregory Stark wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > The default and minimum value for this parameter is 1, so very similar to > > existing behaviour. Expected settings would be 2-5, possibly as high as 20, > > though those are just educated guesses. So the maximum is set arbitrarily as > > 100. > > Not a fan of arbitrary constants. ISTM this should just have a maximum of > MaxHeapTuplesPerPage. > > I'm not really happy with having this parameter at all. It's not something a > DBA can understand or have any hope of setting intelligently. I assume this is > a temporary measure until we have a better understanding of what real-world > factors affect the right values for this knob? Yes, its a guess at what sort of control we'll need. > > Temp buffers are never dirtied by hint bit setting. Most temp tables are > > written in a single command, so that re-accessing clog for temp tuples > > is seldom costly. This also changes current behaviour. > > I'm not sure I agree with this logic and it doesn't seem like temporary tables > are an important enough case to start coming up with special cases which may > help or may hurt. Most people use temporary tables the way you describe but > I'm sure there's someone out there using temporary tables in a radically > different fashion. Thanks for your comments. The patch splits into two parts: * the machinery to *not* dirty a page when we set hints * behaviour modifications now that we can tell the difference between dirty and hinted pages Nobody has yet come up with any comments about the first half, which is good. The second part is clearly where much debate will occur. I'm going to literally split the patch into two, so we can get the machinery into CVS and then fiddle and argue over the second part over next few months. > I'm also a bit concerned that *how many hint bits* isn't enough information to > determine how important it is to write out the page. What about how old the > oldest transaction is which was hinted? Or how many *unhinted* xmin/xmax > values were found? If HTSV can hint xmin for a tuple but finds xmax still in > progress perhaps that's a good sign it's not worth dirtying the page? Sounds interesting. We can track anything and everything really, but we do need to come to a firm dirty/not decision at some point. If you can develop those ideas a bit more by Monday, I'll try to put them in the patch. (I'm away until then now). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MSVC 2003 compile error with pg8.3.3
Hello Hiroshi-san, Your new win32.mak causes the following error with MSVC 2003: Creating library .\Release\libpqdll.lib and object .\Release \libpqdll.exp mt -manifest .\Release\libpq.dll.manifest -outputresource:. \Release\libp q.dll;2 Microsoft (R) Manifest Tool version 6.0.4071.0 Copyright (c) Microsoft Corporation 2004. All rights reserved. .\Release\libpq.dll.manifest:general error c1010070:Failed to load and parse the manifest. The system cannot find the file specified. NMAKE : fatal error U1077: 'mt' : return code '0x1f' Stop. NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\ VC7\BIN\nmake.exe"' : return code '0x2' Stop. --- Jeff McKenna FOSS4G Consulting and Training Services http://www.gatewaygeomatics.com/ On 26-Jun-08, at 11:28 PM, Hiroshi Saito wrote: Hi Jeff-san. Would you replace this with src/interfaces/libpq/win32.mak and try it? I checked that there was no problem in construction of VC2005 by this change. Then, I think if you solve a problem and it should apply. Regards, Hiroshi Saito -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hint Bits and Write I/O
On Fri, 2008-06-27 at 15:25 +0100, Gregory Stark wrote: > "Alvaro Herrera" <[EMAIL PROTECTED]> writes: > > > If only VACUUM is going to set "flexible" to off, maybe it's better to > > leave the APIs as they are and have a global that's set by VACUUM only > > (and reset in a PG_CATCH block). > > Ugh. Perhaps it would be simpler to have a wrapper function HTSV() macro which > passes flexible=true to HTSV_internal(). Then vacuum can call HTSV_internal(). > > I'm not sure what the performance tradeoff is between having an extra argument > to HTSV and having HTSV check a global which messes with optimizations. Doing this doesn't actually reduce the size of the patch much, as it turns out, so I suggest we don't do this. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER DATABASE vs pg_dump
Richard Huxton <[EMAIL PROTECTED]> writes: > Is it desirable that pg_dump doesn't dump config settings set via ALTER > DATABASE? Well, it's intentional anyway: that's handled by pg_dumpall. The basic design is that anything that can be seen from "outside" a specific database is handled on the pg_dumpall side. > I just got bitten by a DateStyle not being restored on my test DB You could also get bitten by not having restored users or tablespaces that the dump depends on, so I'm not sure there's a strong argument here for refactoring the responsibility. 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] ecpg generated files ignorable?
On Fri, Jun 27, 2008 at 08:56:11AM -0400, Alvaro Herrera wrote: > There are three -- two of them are .cvsignore'd. Should we just > .cvsignore the third one, or remove it from the build, or ...? I'd say so. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hint Bits and Write I/O
"Simon Riggs" <[EMAIL PROTECTED]> writes: > The default and minimum value for this parameter is 1, so very similar to > existing behaviour. Expected settings would be 2-5, possibly as high as 20, > though those are just educated guesses. So the maximum is set arbitrarily as > 100. Not a fan of arbitrary constants. ISTM this should just have a maximum of MaxHeapTuplesPerPage. I'm not really happy with having this parameter at all. It's not something a DBA can understand or have any hope of setting intelligently. I assume this is a temporary measure until we have a better understanding of what real-world factors affect the right values for this knob? > Temp buffers are never dirtied by hint bit setting. Most temp tables are > written in a single command, so that re-accessing clog for temp tuples > is seldom costly. This also changes current behaviour. I'm not sure I agree with this logic and it doesn't seem like temporary tables are an important enough case to start coming up with special cases which may help or may hurt. Most people use temporary tables the way you describe but I'm sure there's someone out there using temporary tables in a radically different fashion. I'm also a bit concerned that *how many hint bits* isn't enough information to determine how important it is to write out the page. What about how old the oldest transaction is which was hinted? Or how many *unhinted* xmin/xmax values were found? If HTSV can hint xmin for a tuple but finds xmax still in progress perhaps that's a good sign it's not worth dirtying the page? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hint Bits and Write I/O
"Alvaro Herrera" <[EMAIL PROTECTED]> writes: > If only VACUUM is going to set "flexible" to off, maybe it's better to > leave the APIs as they are and have a global that's set by VACUUM only > (and reset in a PG_CATCH block). Ugh. Perhaps it would be simpler to have a wrapper function HTSV() macro which passes flexible=true to HTSV_internal(). Then vacuum can call HTSV_internal(). I'm not sure what the performance tradeoff is between having an extra argument to HTSV and having HTSV check a global which messes with optimizations. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER DATABASE vs pg_dump
Is it desirable that pg_dump doesn't dump config settings set via ALTER DATABASE? http://archives.postgresql.org/pgsql-novice/2008-04/msg00016.php I just got bitten by a DateStyle not being restored on my test DB (I usually set it client-side in the app). I could see someone without my steel trap of a mind letting something like this slip through. Obvious problem settings would be: datestyle, locale, default-text-search Is this a deliberate behaviour of pg_dump or just an unscratched itch? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ecpg generated files ignorable?
Michael Meskes wrote: > On Wed, Jun 18, 2008 at 09:26:24PM -0400, Tom Lane wrote: > > Makefile.shlib builds these in the distprep action, so I suppose > > they're supposed to be there. libpq .cvsignore's its equivalent > > files, so I'd agree with doing that. It looks like there should be > > three such files in each directory, though, not just one? There are three -- two of them are .cvsignore'd. Should we just .cvsignore the third one, or remove it from the build, or ...? libpq has the three of them in .cvsignore so I guess this is what ecpg should do as well. > Well, in my source tree I have two, one for MS VC++ and one for Borland > C++ Builder. And yes, I can build a third one for MS VC++ as well by > just issuing the corresponding make call. However, I have no idea > whether we need both, the only differ in the lib name: > --- libecpgddll.def 2008-06-20 12:33:29.0 +0200 > +++ libecpgdll.def 2008-06-20 12:33:16.0 +0200 > @@ -1,5 +1,5 @@ > ; DEF file for MS VC++ > -LIBRARY LIBECPGD > +LIBRARY LIBECPG > EXPORTS >ECPGallocate_desc@ 1 >ECPGconnect @ 2 > > Maybe someone with more Windows knowledge can explain this? Magnus? The weird thing is that the three files are generated for me unconditionally (of course, I didn't issue a specific make call). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table inheritance surprise
On Thu, 2008-06-26 at 18:53 -0400, Bruce Momjian wrote: > David Fetter wrote: > > Folks, > > > > When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't > > include foreign key constraints (8.3.1). I believe this is surprising > > behavior, but maybe not a bug, so I'd like to propose another bit of > > syntactic sugar, namely > > > > LIKE [INCLUDING FOREIGN KEYS] > > > > which would do what it looks like it does. > > > > What say? > > TODO has: > > o Allow inherited tables to inherit indexes, UNIQUE constraints, > and primary/foreign keys INCLUDING CONSTRAINTS is non-standard so maybe we can just make INCLUDING CONSTRAINTS also include foreign keys. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] VirtualXactLockTableInsert
When we move from having a virtual xid to having a real xid I don't see any attempt to re-arrange the lock queues. Surely if there are people waiting on the virtual xid, they must be moved across to wait on the actual xid? Otherwise the locking queue will not be respected because we have two things on which people might queue. Anybody explain that? In cases where we know we will assign a real xid, can we just skip the assignment of the virtual xid completely? For example, where an implicit transaction is started by a DML statement. Otherwise we have to wait for 2 lock table inserts, not just one. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Posting to hackers and patches lists
Bruce Momjian wrote: Tom Lane wrote: ... * no permanent archive of the submitted patch * reviewer won't know if the submitter changes the patch after he downloads a copy, and in fact nobody will ever know unless the submitter takes the time to compare the eventual commit to what he thinks the patch is This requires the patch submitter to send an email every time they update the URL. The problem with no archive is a problem though. It works for me because I am around to supply versions but I see your point --- perhaps we could make the system have a stable URL but allow for versioning access. Maybe email is a fine interface, of course. What about having tickets? Track for example or something like that and the submitter feeling an itch to scratch just uploads it to a ticket. This way you know the reason for a patch and can even have a little discussion as well as a link to the revision where it got incorporated. Couldn't be cleaner I think... The link to the ticket is also rather stable and you can communicate in mailinglist about it. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Join Removal/ Vertical Partitioning
On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > We can check for removal of a rel by > > > 1. inspecting the target list for the query to see if there are rels > > that do not provide any attributes. (We might also use equivalence > > classes to recode the targetlist to minimise the numbers of tables > > touched, but I think that might be overkill). > > More to the point, it would be wrong. Equivalence classes do not imply > that two values considered equivalent are equal for all purposes, and > since we don't know what the client is going to do with the returned > data, we can't substitute some other value for the one requested. > > > So some thoughts on where to attempt this would be very useful. > > The hard part of this is figuring out where to do the work. As you say, > doing it during prepjointree seems the nicest from an abstract code > structure point of view, but it requires a lot of information that is > not derived until later. > It might be possible to treat "ignore the RHS" as a join strategy and > try to apply it while forming join relations, which would be late enough > to have all the needed info available. Oh, actually have a join node that is a no-op, with a path cost of zero? So we end up with an EXPLAIN like this: QUERY PLAN --- Join Removed (cost=0.00..8.27 rows=1 width=4) -> Index Scan using class_pkey on class c (cost=0.00..8.27 rows=1 width=8) Index Cond: (pk = 6) -> No Operation on subclass sc (cost=0.00..0.00 rows=0 width=0) (4 rows) That really does help, I think. The code allows us to say a join is impossible, but not very easily to say a join doesn't exist. I'll try it this way first. Maybe we'll see other ways as we go. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Building PostgreSQL 8.3.1 on OpenVMS 8.3 AXP
On Sat, 2008-03-22 at 22:40 +0200, Mihai Criveti wrote: > I am trying to build PostgreSQL 8.3.1 on OpenVMS 8.3 Alpha, patched to > UPDATE v6.0 ECO: > DEC AXPVMS VMS83A_UPDATE V6.0Patch Install Val > 14-MAR-2008 > > Using the HP C compilers: > HP C Version 7.3 for OpenVMS Alpha Systems > HP C++ Version V7.3 for OpenVMS Alpha Systems Did you ever get this to work? I notice we don't have an OpenVMS port on the build farm, so a successful build report would be interesting. Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gsoc, text search selectivity and dllist enhancments
Hi,
attached are two patches against HEAD. The smaller one is meant to be
commited - it adds some functions that manipulate double-linked lists,
namely inserting a new cell after or before another cell and swapping
two adjacent cells. It felt like being back in the first year of
studies. I hope I didn't mess those pointers up.
The gzipped one is WIP for my GSoC project. I've reworked the algorithm
for determing most common lexemes. The goal was to avoid scanning
through all currently kept lexemes in each iteration of the loop that
processes all lexemes from sample tsvectors. Heikki suggested to
introduce a hashtable, so I did that. It works, passes regression tests
and correctly identifies most common lexemes in my toy test database.
Of course it's all quite useless without a selectivity operator that
could use those statistics. I'm sending it in to maybe get some feedback
during the commit fest.
The second patch depends on the first, and also on the one I sent eariler:
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
I'll add the first one to the commit fest page, and I'm sending it to
-hackers with congratulations on the decision to ditch -patches ;)
Cheers,
Jan
--
Jan Urbanski
GPG key ID: E583D7D2
ouden estin
diff --git a/src/backend/lib/dllist.c b/src/backend/lib/dllist.c
index b771fce..79c402e 100644
*** a/src/backend/lib/dllist.c
--- b/src/backend/lib/dllist.c
***
*** 212,214
--- 212,336
l->dll_head = e;
/* We need not check dll_tail, since there must have been > 1 entry */
}
+
+ /* Insert a node after the given target node. */
+ void
+ DLAddAfter(Dlelem *e, Dlelem *target)
+ {
+ Dllist *l = target->dle_list;
+
+ e->dle_list = l;
+ e->dle_prev = target;
+ e->dle_next = target->dle_next;
+
+ if (l->dll_tail != target)
+ {
+ /* Target is not the tail */
+ Assert(target->dle_next != NULL);
+ target->dle_next->dle_prev = e;
+ }
+ else
+ {
+ /* Target is the tail */
+ Assert(target->dle_next == NULL);
+ l->dll_tail = e;
+ }
+ target->dle_next = e;
+ return;
+ }
+
+ /* Insert a node before the given target node. */
+ void
+ DLAddBefore(Dlelem *e, Dlelem *target)
+ {
+ Dllist *l = target->dle_list;
+
+ e->dle_list = l;
+ e->dle_prev = target->dle_prev;
+ e->dle_next = target;
+
+ if (l->dll_head != target)
+ {
+ /* Target is not the head */
+ Assert(target->dle_prev != NULL);
+ target->dle_prev->dle_next = e;
+ }
+ else
+ {
+ /* Target is the head */
+ Assert(target->dle_prev == NULL);
+ l->dll_head = e;
+ }
+ target->dle_prev = e;
+ return;
+ }
+
+ /* Swap a node with its successor */
+ void
+ DLSwapWithNext(Dlelem *e)
+ {
+ Dllist *l = e->dle_list;
+ Dlelem *tmp;
+
+ Assert(e->dle_next != NULL);
+
+ tmp = e->dle_next;
+ e->dle_next = tmp->dle_next;
+ if (l->dll_tail != tmp)
+ {
+ Assert(tmp->dle_next != NULL);
+ tmp->dle_next->dle_prev = e;
+ }
+ else
+ {
+ l->dll_tail = e;
+ }
+ if (l->dll_head != e)
+ {
+ Assert(e->dle_prev != NULL);
+ e->dle_prev->dle_next = tmp;
+ }
+ else
+ {
+ l->dll_head = tmp;
+ }
+ tmp->dle_prev = e->dle_prev;
+ e->dle_prev = tmp;
+ tmp->dle_next = e;
+ return;
+ }
+
+ /* Swap a node with its predecessor */
+ void
+ DLSwapWithPrevious(Dlelem *e)
+ {
+ Dllist *l = e->dle_list;
+ Dlelem *tmp;
+
+ Assert(e->dle_prev != NULL);
+
+ tmp = e->dle_prev;
+ e->dle_prev = tmp->dle_prev;
+ if (l->dll_head != tmp)
+ {
+ Assert(tmp->dle_prev != NULL);
+ tmp->dle_prev->dle_next = e;
+ }
+ else
+ {
+ l->dll_head = e;
+ }
+ if (l->dll_tail != e)
+ {
+ Assert(e->dle_next != NULL);
+ e->dle_next->dle_prev = tmp;
+ }
+ else
+ {
+ l->dll_tail = tmp;
+ }
+ tmp->dle_next = e->dle_next;
+ e->dle_next = tmp;
+ tmp->dle_prev = e;
+ return;
+ }
diff --git a/src/include/lib/dllist.h b/src/include/lib/dllist.h
index d754b03..597e71f 100644
*** a/src/include/lib/dllist.h
--- b/src/include/lib/dllist.h
***
*** 72,77
--- 72,81
extern Dlelem *DLRemHead(Dllist *list); /* remove and return the head */
extern Dlelem *DLRemTail(Dllist *list);
extern void DLMoveToFront(Dlelem *e); /* move node to front of its list */
+ extern void DLAddAfter(Dlelem *e, Dlelem *target); /* add node after another
*/
+ extern void DLAddBefore(Dlelem *e, Dlelem *target); /* same, but add before */
+ e
