Re: [BUGS] overwriting an existing .so while being used crashes the server process
On ons, 2012-05-30 at 23:43 +0200, Tomas Vondra wrote: > On 30.5.2012 23:19, Tom Lane wrote: > > I suspect it depends on how you install the new version of the library, > > too. I would somewhat expect it to work as you're thinking if the > > install consists of "rename old file out of the way, copy new file into > > place, unlink old file" or equivalent. If you are actually > > *overwriting* the file in place, a crash does not seem especially > > surprising --- it would make perfect sense if the kernel expects the > > file to be usable as backing store for the in-memory image, which is not > > exactly unreasonable. IOW, if the in-memory bits we're executing are > > just an mmap'd image of the .so file, changing the .so file could > > entirely be expected to lead to a crash. > > Aha! That might be the culprit - I've just tested that deleting the olf > file and copying new version (thus not overwriting it) did not cause a > crash. Funny. That's one of the reasons why one normally uses "install" rather than "cp" to install files. So this shouldn't be a problem in practice if people use the provided pgxs infrastructure or something similar. GNU cp has the --remove-destination option, which should also work for this purpose. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6650: CPU system time utilization rising few times a day
On Thu, May 31, 2012 at 3:29 AM, Andrzej Krawiec wrote: > Ok, we've managed to do strace -s during such a situation (see > attached file). I have no clue what can it mean. Only errors count is > quite strange. How long was strace -s run for to generate this? > Could this > http://postgresql.1045698.n5.nabble.com/high-CPU-usage-for-stats-collector-in-8-2-td1962590.html > affect our environment? Not sure, but that's a much older version of PostgreSQL than the one you're running, and I think there may have been some improvements meanwhile. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6659: Error when install postgres
On Mon, May 21, 2012 at 10:48 PM, wrote: > The following bug has been logged on the website: > > Bug reference: 6659 > Logged by: Junho Kim > Email address: junho1@lge.com > PostgreSQL version: 9.0.4 > Operating system: Windows XP 32bit ServicePack 3 > Description: > > I run postgresql-9.0.4-1-windows.exe. > But package occur error instead install DB. > > Error message is as follows: > AppName: postgresql-9.0.4.1-windows.exe > AppVer: 1.0.0.0 > ModName: postgresql-9.0.4-1-windows.exe > ModVer: 1.0.0.0 > Offset: 0004df8b I doubt this is broken in general, or we'd have had more complaints, so there must be something different about your system, but I don't know what it is. Did it leave behind any useful logfiles, maybe in your temp directory? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6641: uuid.h warning during configure
On Tue, May 22, 2012 at 4:37 PM, Chris Ruprecht wrote: > Hi Robert, > > I was compiling 9.2beta1 - but that wasn't an option when submitting the > issue. > I can compile the 9.1.x versions just fine. > > Below is a list of the uuid.h files on the system. > It appears, that the file being used is "/usr/local/include/uuid.h" - the > same as with the 9.0.x and 9.1.x compiles. > > I don't think the platform files matter much. I am guessing that the problem is that we're looking for the uuid.h that comes with the OSSP package, and you're uuid.h is something else. You might want to see if there's anything helpful in config.log. Weirdly, there don't seem to be any UUID-related changes to configure.in between 9.1 and 9.2beta1, so I'm not quite sure why you're seeing different behavior. > > > locate uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.6.sdk/System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.6.sdk/usr/include/uuid/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.7.sdk/System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.7.sdk/usr/include/postgresql/server/utils/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.7.sdk/usr/include/uuid/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS5.0.sdk/usr/include/uuid/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator5.0.sdk/System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h > /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator5.0.sdk/usr/include/uuid/uuid.h > /System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h > /usr/include/postgresql/server/utils/uuid.h > /usr/include/uuid/uuid.h > /usr/local/include/postgresql/server/utils/uuid.h > /usr/local/include/uuid.h > /usr/local/source/postgresql-9.1.2/src/include/utils/uuid.h > /usr/local/source/postgresql-9.1.3/src/include/utils/uuid.h > /usr/local/source/postgresql-9.2beta1/src/include/utils/uuid.h > > > best regards, > chris > -- > chris ruprecht > database grunt and bit pusher extraordinaíre > > On May 22, 2012, at 15:58 , Robert Haas wrote: > >> On Tue, May 15, 2012 at 7:28 PM, wrote: >>> The following bug has been logged on the website: >>> >>> Bug reference: 6641 >>> Logged by: Chris Ruprecht >>> Email address: ch...@cdrbill.com >>> PostgreSQL version: 9.1.3 >>> Operating system: Mac OS X 10.7.4 >>> Description: >>> >>> running configure as such: >>> >>> export MACOSX_DEPLOYMENT_TARGET=10.7 >>> export CC=gcc >>> export CPP="gcc -E" >>> export CXX=g++ >>> export CXXFLAGS='-O3 -fno-common -arch x86_64' >>> export CFLAGS='-O3 -fno-common -arch x86_64' >>> export LDFLAGS='-O3 -fno-common -arch x86_64' >>> >>> ./configure \ >>> --prefix=/usr/local \ >>> --enable-integer-datetimes \ >>> --enable-thread-safety \ >>> --with-libxml \ >>> --with-libxslt \ >>> --with-ossp-uuid \ >>> --with-tcl \ >>> --with-perl \ >>> --with-python \ >>> --with-gssapi \ >>> --with-krb5 \ >>> --with-pam \ >>> --with-ldap \ >>> --with-bonjour \ >>> --with-openssl \ >>> --with-zlib >>> >>> During the configuration phase, I get this message: >>> >>> >>> checking uuid.h presence... yes >>> configure: WARNING: uuid.h: present but cannot be compiled >>> configure: WARNING: uuid.h: check for missing prerequisite headers? >>> configure: WARNING: uuid.h: see the Autoconf documentation >>> configure: WARNING: uuid.h: section "Present But Cannot Be Compiled" >>> configure: WARNING: uuid.h: proceeding with the preprocessor's result >>> configure: WARNING: uuid.h: in the future, the compiler will take >>> precedence >>> configure: WARNING: ## ## >>> configure: WARNING: ## Report this to pgsql-bugs@postgresql.org ## >>> configure: WARNING: ## ## >>> checking for uuid.h... yes >> >> How many copies of uuid.h do you have on your machine, and which one >> is being chosen here? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2
On Tue, May 29, 2012 at 10:34:16PM -0400, Bruce Momjian wrote: > My point is that plpython_call_handler is defined in the public and > pg_catalog schema, as are other language handlers. > > In fact, an argument could be made that the bug is really in pg_dump. > When we moved the language handlers into pg_catalog, I don't think any > effort was made to suppress the public schema handlers from being dumped. > Maybe that's where the fix should be. It would allow us to fix all the > languages, not just plpython when using pg_upgrade. I think the big > question is can we uniquely identify them. > > I did see this C comment in pg_dump.c: > > /* >* Determine whether we want to dump definitions for procedural > languages. >* Since the languages themselves don't have schemas, we can't rely on >* the normal schema-based selection mechanism. We choose to dump them >* whenever neither --schema nor --table was given. (Before 8.1, we > used >* the dump flag of the PL's call handler function, but in 8.1 this will >* probably always be false since call handlers are created in > pg_catalog.) >* >* For some backwards compatibility with the older behavior, we forcibly >* dump a PL if its handler function (and validator if any) are in a >* dumpable namespace. That case is not checked here. >* >* Also, if the PL belongs to an extension, we do not use this > heuristic. >* That case isn't checked here either. >*/ > static bool > shouldDumpProcLangs(void) OK, so what do people want me to do on this? Apply my pg_upgrade fix or go for a more general fix that will prevent pg_dump from dumping out these duplicate functions --- it would involve checking for public schema functions who's names and probin match pg_pltemplate entries. Either will fix pg_upgrade. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6671: Killed restore command causes postmaster to exit
The following bug has been logged on the website: Bug reference: 6671 Logged by: Ed Muller Email address: edw...@heroku.com PostgreSQL version: 9.0.7 Operating system: Linux (Ubuntu 10.04) Description: We have this thing where we kill the restore command sometimes, to ensure it's not stuck. This has rarely led to postmaster dying afterwards instead of retrying. Here are some related logs... [CRIMSON] LOG: restored log file "000631B50099" from archive [CRIMSON] FATAL: could not restore file "000631B5009A" from archive: return code 36608 [CRIMSON] LOG: startup process (PID 854) exited with exit code 1 [CRIMSON] LOG: terminating any other active server processes -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6668: hashjoin cost problem
Why is cost_hashjoin estimating 50 billion tuple comparisons for 10K rows of output though? From: Tom Lane To: postgresu...@yahoo.com Cc: pgsql-bugs@postgresql.org Sent: Wednesday, May 30, 2012 10:03 PM Subject: Re: [BUGS] BUG #6668: hashjoin cost problem postgresu...@yahoo.com writes: > create table small(i) as select (g/1000) * 1000 from > generate_series(1,1) g; > create table large(i) as select generate_series(1,1); > It doesn't matter how big the big table is... for this distribution large > table is hashed. I don't think that's wrong. If it hashes the small table, there cannot be less than 1000 entries on each populated hash chain; adding more work_mem doesn't help. The planner is designed to avoid hashing such unfriendly distributions as that. The fact that you can get a somewhat smaller runtime by forcing hashing in the other direction suggests that its cost factors are not quite right for your specific case --- but it's a long way from that observation to deciding that we should change the cost factors for everyone. In any case, the sizes of the tables are not the only determinant of which one should be hashed. regards, tom lane
[BUGS] BUG #6669: unique index w/ multiple columns and NULLs
The following bug has been logged on the website: Bug reference: 6669 Logged by: jose soares Email address: jose.soa...@sferacarta.com PostgreSQL version: 8.4.8 Operating system: x86_64-pc-linux-gnu, debian Description: Hi, I think I have found an error in pg or at least inconsistency, take a look at this. I created an unique index on two columns and pg let me enter repeated values as NULLs (unknown value), When I ask pg to tell me if there are repetitions n this index (with group by), the inconsistency becomes apparent. # create table test(id int, data date, code int); CREATE TABLE # create UNIQUE index unica on test(data,code); CREATE INDEX # \d test Table "public.test" Column | Type | Modifiers +-+--- id | integer | data | date| code | integer | Indexes: "unica" UNIQUE, btree (data, code) # insert into test values(1,current_date); INSERT 0 1 # insert into test values(2,current_date); INSERT 0 1 # insert into test values(3,current_date); INSERT 0 1 sicer_forli=# select current_date,code, count(*) from test group by 1,2; date| code | count +--+--- 31-05-2012 | | 3 (1 row) ps: Oracle don't allows to insert two NULLs in such column. I don't know which of them is SQL Standard, but in this case oracle is not inconsistent. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6641: uuid.h warning during configure
Hi Robert, I did some experimenting. I have uuid.h from the OSSP uuid page, version 1.6.2 installed. Removed /usr/local/include/uuid.h rebuilt and re-installed the package. Same error - but also in 9.1. So I copied /usr/include/uuid/uuid.h to /usr/local/include/uuid.h and the error went away: checking for uuid_export in -lossp-uuid... no checking for uuid_export in -luuid... yes ... checking uuid.h usability... yes checking uuid.h presence... yes checking for uuid.h... yes go figure ... ;). best regards, chris -- chris ruprecht database grunt and bit pusher extraordinaíre On May 31, 2012, at 11:00 , Robert Haas wrote: > On Tue, May 22, 2012 at 4:37 PM, Chris Ruprecht wrote: >> Hi Robert, >> >> I was compiling 9.2beta1 - but that wasn't an option when submitting the >> issue. >> I can compile the 9.1.x versions just fine. >> >> Below is a list of the uuid.h files on the system. >> It appears, that the file being used is "/usr/local/include/uuid.h" - the >> same as with the 9.0.x and 9.1.x compiles. >> >> I don't think the platform files matter much. > > I am guessing that the problem is that we're looking for the uuid.h > that comes with the OSSP package, and you're uuid.h is something else. > You might want to see if there's anything helpful in config.log. > Weirdly, there don't seem to be any UUID-related changes to > configure.in between 9.1 and 9.2beta1, so I'm not quite sure why > you're seeing different behavior. > >> >> >> locate uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.6.sdk/System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.6.sdk/usr/include/uuid/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.7.sdk/System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.7.sdk/usr/include/postgresql/server/utils/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.7.sdk/usr/include/uuid/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS5.0.sdk/usr/include/uuid/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator5.0.sdk/System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h >> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator5.0.sdk/usr/include/uuid/uuid.h >> /System/Library/Frameworks/Kernel.framework/Versions/A/Headers/uuid/uuid.h >> /usr/include/postgresql/server/utils/uuid.h >> /usr/include/uuid/uuid.h >> /usr/local/include/postgresql/server/utils/uuid.h >> /usr/local/include/uuid.h >> /usr/local/source/postgresql-9.1.2/src/include/utils/uuid.h >> /usr/local/source/postgresql-9.1.3/src/include/utils/uuid.h >> /usr/local/source/postgresql-9.2beta1/src/include/utils/uuid.h >> >> >> best regards, >> chris >> -- >> chris ruprecht >> database grunt and bit pusher extraordinaíre >> >> On May 22, 2012, at 15:58 , Robert Haas wrote: >> >>> On Tue, May 15, 2012 at 7:28 PM, wrote: The following bug has been logged on the website: Bug reference: 6641 Logged by: Chris Ruprecht Email address: ch...@cdrbill.com PostgreSQL version: 9.1.3 Operating system: Mac OS X 10.7.4 Description: running configure as such: export MACOSX_DEPLOYMENT_TARGET=10.7 export CC=gcc export CPP="gcc -E" export CXX=g++ export CXXFLAGS='-O3 -fno-common -arch x86_64' export CFLAGS='-O3 -fno-common -arch x86_64' export LDFLAGS='-O3 -fno-common -arch x86_64' ./configure \ --prefix=/usr/local \ --enable-integer-datetimes \ --enable-thread-safety\ --with-libxml \ --with-libxslt\ --with-ossp-uuid \ --with-tcl \ --with-perl \ --with-python \ --with-gssapi \ --with-krb5 \ --with-pam \ --with-ldap \ --with-bonjour \ --with-openssl \ --with-zlib During the configuration phase, I get this message: checking uuid.h presence... yes configure: WARNING: uuid.h: present but cannot be compiled configure: WARNING: uuid.h: check for missing prerequisite headers? configure: WARNING: uuid.h: see the Autoconf documentation configure: WARNING: uuid.h: section "Present But Cannot Be Compiled" configure: WARNING: uuid.h: proceeding with the preprocessor's result configure: WARNING: uuid.h: in the future, the compiler will take precedence configure: WAR
Re: [BUGS] BUG #6650: CPU system time utilization rising few times a day
Ok, we've managed to do strace -s during such a situation (see attached file). I have no clue what can it mean. Only errors count is quite strange. Could this http://postgresql.1045698.n5.nabble.com/high-CPU-usage-for-stats-collector-in-8-2-td1962590.html affect our environment? -- Andrzej Krawiec > > perf can tell you about problems in kernel-space, but I'm not sure it > exists that far back. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company strace_2012-05-31_08_38_44.log Description: Binary data -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6670: Translate small SQL from 8.0 ti 7.3
The following bug has been logged on the website: Bug reference: 6670 Logged by: Roman Email address: byg...@mail.ua PostgreSQL version: Unsupported/Unknown Operating system: Win32 Description: Bruce, tell me PLEASE how make this in 7.3: SELECT t.oid, format_type(t.oid, -1) AS basetype FROM pg_proc p INNER JOIN pg_type t ON t.oid = p.prorettype OR t.oid = ANY (p.proargtypes) OR t.oid = ANY (p.proallargtypes) ANY and oidvector (int2vector)! it's possible ? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2
On 05/31/2012 11:53 AM, Bruce Momjian wrote: On Tue, May 29, 2012 at 10:34:16PM -0400, Bruce Momjian wrote: OK, so what do people want me to do on this? Apply my pg_upgrade fix or go for a more general fix that will prevent pg_dump from dumping out these duplicate functions --- it would involve checking for public schema functions who's names and probin match pg_pltemplate entries. Either will fix pg_upgrade. I would say the pg_dump fix. That one gets rid of the duplicates for everyone, not just those folks using pg_upgrade. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2
Adrian Klaver writes: > On 05/31/2012 11:53 AM, Bruce Momjian wrote: >> On Tue, May 29, 2012 at 10:34:16PM -0400, Bruce Momjian wrote: >> OK, so what do people want me to do on this? Apply my pg_upgrade fix or >> go for a more general fix that will prevent pg_dump from dumping out >> these duplicate functions --- it would involve checking for public >> schema functions who's names and probin match pg_pltemplate entries. >> Either will fix pg_upgrade. > I would say the pg_dump fix. That one gets rid of the duplicates for > everyone, not just those folks using pg_upgrade. Hm, I'm not sure about that. The general charter of pg_dump is to produce a dump that will replicate the state of the database. Editorializing on it in order to make it more likely to reload in a different version of PG seems to violate that charter. I think the current state where pg_upgrade just complains about those functions and tells you to remove them by hand is far safer than creating blind spots in pg_dump. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2
On Thu, May 31, 2012 at 06:24:04PM -0400, Tom Lane wrote: > Adrian Klaver writes: > > On 05/31/2012 11:53 AM, Bruce Momjian wrote: > >> On Tue, May 29, 2012 at 10:34:16PM -0400, Bruce Momjian wrote: > >> OK, so what do people want me to do on this? Apply my pg_upgrade fix or > >> go for a more general fix that will prevent pg_dump from dumping out > >> these duplicate functions --- it would involve checking for public > >> schema functions who's names and probin match pg_pltemplate entries. > >> Either will fix pg_upgrade. > > > I would say the pg_dump fix. That one gets rid of the duplicates for > > everyone, not just those folks using pg_upgrade. > > Hm, I'm not sure about that. The general charter of pg_dump is to > produce a dump that will replicate the state of the database. > Editorializing on it in order to make it more likely to reload in a > different version of PG seems to violate that charter. > > I think the current state where pg_upgrade just complains about those > functions and tells you to remove them by hand is far safer than > creating blind spots in pg_dump. Agreed. I think the big question is whether the 8.1 move of the PL language support functions to pg_catalog should have suppressed dumping the pre-8.1 PL functions in the public schema. Another question is whether having these functions in two schemas presents any possible danger. Users using pg_dumpall and restoring (not using pg_upgrade) will have the plpython functions removed because they will error out, so maybe we should just let the plpython renaming trim those out. However, this doesn't remove the other PL lanauge duplicates. I share Tom's caution on this, but I think we need to make sure we are addressing any possible risk of an isolated pg_upgrade fix, now that we understand the cause. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6668: hashjoin cost problem
Postgres User writes: > Why is cost_hashjoin estimating 50 billion tuple comparisons for 10K rows of > output though? Well, if it hashes the smaller table, there's 100 million rows on the outside, and each of them will probe one hash chain in the hash table. If you're unlucky, each of those probes will hit a populated hash chain with at least 1000 entries, leading to 100 billion comparisons. I think it might derate that worst-case by a factor of 2. Now if you're lucky, a lot of the outer tuples hit unpopulated hash chains and so the number of comparisons is a lot less --- but in non-artificial examples, that's not a very good bet to make. The conservative assumption is that both sides of the join have similar key distributions, so that the more populated hash chains are also more likely to be probed. The cost estimate is therefore designed to discriminate against using an inner relation with a non-flat distribution. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6671: Killed restore command causes postmaster to exit
edw...@heroku.com writes: > We have this thing where we kill the restore command sometimes, to ensure > it's not stuck. Um, what makes you think that's a good idea? > This has rarely led to postmaster dying afterwards instead of retrying. "Rarely"? As I read the code, it will happen every single time. Failure of the restore process is a failure, we do not retry. (The general theory is that if you think you need retries, you should code the restore_command to handle that.) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2
On 05/31/2012 03:30 PM, Bruce Momjian wrote: On Thu, May 31, 2012 at 06:24:04PM -0400, Tom Lane wrote: Hm, I'm not sure about that. The general charter of pg_dump is to produce a dump that will replicate the state of the database. Editorializing on it in order to make it more likely to reload in a different version of PG seems to violate that charter. I think the current state where pg_upgrade just complains about those functions and tells you to remove them by hand is far safer than creating blind spots in pg_dump. Agreed. I think the big question is whether the 8.1 move of the PL language support functions to pg_catalog should have suppressed dumping the pre-8.1 PL functions in the public schema. Another question is whether having these functions in two schemas presents any possible danger. Users using pg_dumpall and restoring (not using pg_upgrade) will have the plpython functions removed because they will error out, so maybe we should just let the plpython renaming trim those out. However, this doesn't remove the other PL lanauge duplicates. I share Tom's caution on this, but I think we need to make sure we are addressing any possible risk of an isolated pg_upgrade fix, now that we understand the cause. There are two different things at work here. One the duplicate functions, two the renaming of the plpython libraries. The first would not have been an issue without the second. I still say for compatibility the plpython libraries should follow a scheme that reflects how the language is being handled in the database: plpython.so -> plpython2.so plpython2.so plpython3.so (optional) Not sure if that is possible, just my 2 cents. In any case the overriding issue would seem to be covered in another thread on --hackers 'pg_upgrade libraries check' In other words determining who is responsible for tracking library changes? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.2 beta1 libxml2 can't be loaded on Windows
On 31 May 2012 14:14, Edmund Horner wrote: > Hello, apologies if this is already in your radar. > > I can successfully install the PostgreSQL 9.2 beta1 binaries on > Windows XP and successfully perform most queries. However I've run > into some trouble with the XML support. > > From a very cursory search I did not see anything changed since 9.1.3 > that could affect this, so maybe it's just a build/packaging issue. > > Cheers, > Edmund. > > > > Version and environment: > > PostgreSQL 9.2 beta1, binaries-only package > Windows XP SP3 32 bit > > Steps: > > 1. Install binaries. > 2. Start server. > 3. Open psql session to postgres database. > 4. Run query: SELECT xml 'bar'; > > Expected result: query returns a single row like: > > xml > > bar > > Actual result: > > postgres=# SELECT xml 'bar'; > ERROR: could not set up XML error handler > LINE 1: select xml 'bar'; > ^ > HINT: This probably indicates that the version of libxml2 being > used is not compatible with the > libxml2 header files that PostgreSQL was built with. > > postgres.exe does find and read part of libxml2.dll both at initial > server startup, and when psql connects for the first time. I tried using an older libxml2.dll. Replace with the one from ftp://ftp.zlatkovic.com/libxml/oldreleases/libxml2-2.6.9.win32.zip (and copying zlib1.dll to zlib.dll), and it works. I note that the previous 9.1.3 binaries used the newer libxml2.dll though (same as the one in ftp://ftp.zlatkovic.com/libxml/libxml2-2.7.8.win32.zip). So I'm guessing the Windows PostgreSQL binaries were built with the old one, but packaged with the new one. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.2 beta1 libxml2 can't be loaded on Windows
On 1 June 2012 12:14, Edmund Horner wrote:
> I tried using an older libxml2.dll. Replace with the one from
> ftp://ftp.zlatkovic.com/libxml/oldreleases/libxml2-2.6.9.win32.zip
> (and copying zlib1.dll to zlib.dll), and it works.
>
> I note that the previous 9.1.3 binaries used the newer libxml2.dll
> though (same as the one in
> ftp://ftp.zlatkovic.com/libxml/libxml2-2.7.8.win32.zip).
>
> So I'm guessing the Windows PostgreSQL binaries were built with the
> old one, but packaged with the new one.
Also, if libxml2.dll is replaced with the 2.6 version as above,
"CREATE EXTENSION xml2" will result in this:
ERROR: could not load library "C:/ehorner/pgsql/lib/pgxml.dll":
The specified procedure could not be found.
Which may imply that that DLL was built with the 2.7 version. With
the 2.7 version in place as originally packaged, we have can load the
extension but not use the functions from it.
postgres=# create extension xml2;
CREATE EXTENSION
postgres=# select xslt_process('', '');
ERROR: could not set up XML error handler
HINT: This probably indicates that the version of libxml2 being
used is not compatible with the
libxml2 header files that PostgreSQL was built with.
I wanted to check whether the packaged libxslt.dll had the same
compatibility situation as libxml2.dll does. Unfortunately it seems
doing so means using both versions of the library at once!
Edmund.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 9.2 beta1 libxml2 can't be loaded on Windows
On 1 June 2012 12:36, Edmund Horner wrote:
> On 1 June 2012 12:14, Edmund Horner wrote:
>> I tried using an older libxml2.dll. Replace with the one from
>> ftp://ftp.zlatkovic.com/libxml/oldreleases/libxml2-2.6.9.win32.zip
>> (and copying zlib1.dll to zlib.dll), and it works.
>>
>> I note that the previous 9.1.3 binaries used the newer libxml2.dll
>> though (same as the one in
>> ftp://ftp.zlatkovic.com/libxml/libxml2-2.7.8.win32.zip).
>>
>> So I'm guessing the Windows PostgreSQL binaries were built with the
>> old one, but packaged with the new one.
>
>
> Also, if libxml2.dll is replaced with the 2.6 version as above,
> "CREATE EXTENSION xml2" will result in this:
>
> ERROR: could not load library "C:/ehorner/pgsql/lib/pgxml.dll":
> The specified procedure could not be found.
>
> Which may imply that that DLL was built with the 2.7 version. With
> the 2.7 version in place as originally packaged, we have can load the
> extension but not use the functions from it.
>
> postgres=# create extension xml2;
> CREATE EXTENSION
>
> postgres=# select xslt_process('', '');
> ERROR: could not set up XML error handler
> HINT: This probably indicates that the version of libxml2 being
> used is not compatible with the
> libxml2 header files that PostgreSQL was built with.
>
> I wanted to check whether the packaged libxslt.dll had the same
> compatibility situation as libxml2.dll does. Unfortunately it seems
> doing so means using both versions of the library at once!
>
> Edmund.
Argh, I forget to mention the packaged pgadmin3.exe (in the same bin
dir as postgres) wants the 2.7 version of the DLL (as packaged).
Edmund.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6672: Memory leaks in dumputils.c
The following bug has been logged on the website: Bug reference: 6672 Logged by: Anna Zaks Email address: zaks.a...@gmail.com PostgreSQL version: 9.1.3 Operating system: MacOSX Description: There are two memory leaks in dumputils (v9.2.0beta1): 1) File: src/bin/scripts/dumputils.c Location: line 604, column 11 Description:Memory is never released; potential leak of memory pointed to by 'aclitems' 2) File: src/bin/scripts/dumputils.c Location: line 793, column 10 Description:Memory is never released; potential leak of memory pointed to by 'eqpos' See detailed error paths in the attached html reports. Issues found by clang static analyzer. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6666: pg_upgrade 9.2beta1 plpython/plpython2
On Thu, May 31, 2012 at 06:30:30PM -0400, Bruce Momjian wrote: > > Hm, I'm not sure about that. The general charter of pg_dump is to > > produce a dump that will replicate the state of the database. > > Editorializing on it in order to make it more likely to reload in a > > different version of PG seems to violate that charter. > > > > I think the current state where pg_upgrade just complains about those > > functions and tells you to remove them by hand is far safer than > > creating blind spots in pg_dump. > > Agreed. I think the big question is whether the 8.1 move of the PL > language support functions to pg_catalog should have suppressed dumping > the pre-8.1 PL functions in the public schema. > > Another question is whether having these functions in two schemas > presents any possible danger. Users using pg_dumpall and restoring (not > using pg_upgrade) will have the plpython functions removed because they > will error out, so maybe we should just let the plpython renaming trim > those out. However, this doesn't remove the other PL lanauge > duplicates. > > I share Tom's caution on this, but I think we need to make sure we are > addressing any possible risk of an isolated pg_upgrade fix, now that we > understand the cause. FYI, this query will show any functions defined in the public schema who's names match pg_pltemplate helper functions: SELECT proname FROM pg_proc JOIN pg_namespace ON (pronamespace = pg_namespace.oid) WHERE proname IN ( SELECT tmplhandler FROM pg_pltemplate UNION SELECT tmplinline FROM pg_pltemplate UNION SELECT tmplvalidator FROM pg_pltemplate ) AND nspname = 'public'; This is normal in pre-8.1 but might indicate orphaned functions in PG 8.1+. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6669: unique index w/ multiple columns and NULLs
jose.soa...@sferacarta.com writes: > I think I have found an error in pg or at least inconsistency, take a look > at this. > I created an unique index on two columns and pg let me enter repeated values > as NULLs (unknown value), This is entirely correct per SQL standard: unique constraints do not reject duplicated rows that include nulls. If you read the standard, unique constraints are defined in terms of UNIQUE predicates, and a UNIQUE predicate for a table T is defined thus: 2) If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the cor- responding column in the other row according to Subclause 8.2, "", then the result of the is true; otherwise, the result of the is false. (SQL92 section 8.9 ) This is why a primary key constraint is defined as requiring both UNIQUE and NOT NULL; you need that to ensure that there are indeed no two indistinguishable rows. (Mind you, I'm not here to defend *why* the standard is written that way. But that is what it says.) > Oracle don't allows to insert two NULLs in such column. Oracle is not exactly the most standards-compliant implementation around. They are well-known to be particularly wrong with respect to NULLs behavior. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6672: Memory leaks in dumputils.c
zaks.a...@gmail.com writes: > The following bug has been logged on the website: > Bug reference: 6672 > Logged by: Anna Zaks > Email address: zaks.a...@gmail.com > PostgreSQL version: 9.1.3 > Operating system: MacOSX > Description: > There are two memory leaks in dumputils (v9.2.0beta1): > 1) > File: src/bin/scripts/dumputils.c > Location: line 604, column 11 > Description:Memory is never released; potential leak of memory > pointed to by 'aclitems' > 2) > File: src/bin/scripts/dumputils.c > Location: line 793, column 10 > Description:Memory is never released; potential leak of memory > pointed to by 'eqpos' This is a remarkably unhelpful report. I do not see any memory allocation occurring on either line 604 or line 793 of dumputils.c, in either 9.2beta1 or 9.1.3. Could you perhaps provide source code extracts rather than line numbers that reference indeterminate versions of files? > See detailed error paths in the attached html reports. There were no html reports attached, and I'd prefer plain text anyway please ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
