Re: [HACKERS] fix for pg_upgrade
OK, works now with the recent update. Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p5059777.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
Great, thanks! -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4856336.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
Hi Bruce, here is the file you asked for: http://postgresql.1045698.n5.nabble.com/file/n4850735/pg_upgrade_logfile.txt pg_upgrade_logfile.txt I guess you are not addressing me here, right? > The server will need to > be started with -b and this will disable autovacuum. Can someone on > Windows try this? Thanks panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4850735.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
Here are all generated log files. I just removed all other DBs except gnucash (which includes the accounts table), but the issue also emerges with other DBs. Upgraded the 9.1 instance to the new build (9.1.1.) as well but this apparently did not change anything. PG versions are (including generated logs): PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 64-bit: http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.0.zip pg_upgrade_9.1.0.zip PostgreSQL 9.1.1, compiled by Visual C++ build 1500, 64-bit: http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.1.zip pg_upgrade_9.1.1.zip I hope that is what you meant with "pg_upgrade log file". Regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4848829.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
Hi Bruce, here is the whole dump (old DB): http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt Regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4844725.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
Hi Bruce, on the old DB I've got 465783 as oid whereas on the new one it is 16505. is not in the dump file (old db), even 16385 (i guess this is a typo here) or 16505 are not. The only line in which 465783 could be found is Is that enough information or should I send the whole dump? That's a bit of work as I have to expunge some sensitive schema data, or is there a meaningful way to just do the dump for a single db? Thanks & regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4843289.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
OK, i started once again: I hope the following is the correct way of querying the table corresponding to a relid: -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4838427.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] fix for pg_upgrade
Hi, just tried to upgrade from 9.0 to 9.1 and got this error during pg_upgrade : Mismatch of relation id: database "xyz", old relid 465783, new relid 16494 It seems, I get this error on every table as I got it on another table (which I did not need and deleted) before as well. Schmemas seem to be migrated but the content is missing. I am using Windows 7 64bit (both PG servers are 64 bit as well), everthing on the same machine. Any ideas? Thanks & regards panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4798957.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] [PERFORM] Hash Anti Join performance degradation
I'd like to thank you all for getting this analyzed, especially Tom! Your rigor is pretty impressive. Seems like otherwise it'd impossible to maintain a DBS, though. In the end, I know a lot more of postgres internals and that this idiosyncrasy (from a user perspective) could happen again. I guess it is my first time where I actually encountered an unexpected worst case scenario like this... Seems it is up to me know to be a bit more creative with query optimzation. And in the end, it'll turn out to require an architectural change... As the only thing to achieve is in fact to obtain the last id (currently still with the constraint that it has to happen in an isolated subquery), i wonder whether this requirement (obtaining the last id) is worth a special technique/instrumentation/strategy ( lacking a good word here), given the fact that this data has a full logical ordering (in this case even total) and the use case is quite common I guess. Some ideas from an earlier post: panam wrote: > > ... > This also made me wonder how the internal plan is carried out. Is the > engine able to leverage the fact that a part/range of the rows ["/index > entries"] is totally or partially ordered on disk, e.g. using some kind of > binary search or even "nearest neighbor"-search in that section (i.e. a > special "micro-plan" or algorithm)? Or is the speed-up "just" because > related data is usually "nearby" and most of the standard algorithms work > best with clustered data? > If the first is not the case, would that be a potential point for > improvement? Maybe it would even be more efficient, if there were some > sort of constraints that guarantee "ordered row" sections on the disk, > i.e. preventing the addition of a row that had an index value in between > two row values of an already ordered/clustered section. In the simplest > case, it would start with the "first" row and end with the "last" row (on > the time of doing the equivalent of "cluster"). So there would be a small > list saying rows with id x - rows with id y are guaranteed to be ordered > on disk (by id for example) now and for all times. > Maybe I am completely off the mark but what's your conclusion? To much effort for small scenarios? Nothing that should be handled on a DB level? A try to battle the laws of thermodynamics with small technical dodge? Thanks again panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-PERFORM-Hash-Anti-Join-performance-degradation-tp4443803p4446629.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] [PERFORM] Hash Anti Join performance degradation
Tom Lane-2 wrote: > > It looks like it ought to be an O(N^2) > situation, so the improvement should be noticeable but not amazing. > Hm, the performance was reasonable again when doing a cluster... So I believe this should be more a technical than an algorithmical/complexity issue. Maybe it is the way the hashtable is built and that order makes a difference in that case? In short: Why is clustered data not affected? Regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/Re-PERFORM-Hash-Anti-Join-performance-degradation-tp4443803p4445123.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
