Re: [HACKERS] Recovery from multi trouble
Tom Lane wrote: OKADA Satoshi [EMAIL PROTECTED] writes: The loss of log was simulated by deleting the latest xlog file. What does that have to do with reality? Postgres is very careful not to use an xlog file until it's been fully metadata-synced. You might as well complain that PG doesn't recover after rm -rf / ... In this case(postmaster abnormal end ,and log is lost), I understand that database cannot recover normally. Though a database cannot recover normally, postmaster does not output a clear message showing this situation. I think that it is a problem. Thanks, OKADA Satoshi ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 05:37 To: Christopher Kings-Lynne Cc: Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug; Dave Page Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption Christopher Kings-Lynne [EMAIL PROTECTED] writes: So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? Is it different to normal md5? How is this helpful to the phpPgAdmin project? It would be better to export an API that is (a) less random (why one input null-terminated and the other not?) and (b) less tightly tied to MD5 --- the fact that the caller knows how long the result must be is the main problem here. Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. Changing the API is likely to cause fun on Windows for new apps that find an old libpq.dll. Perhaps at this point it should become libpq82.dll? Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
On Mon, Dec 19, 2005 at 08:51:23AM -, Dave Page wrote: Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. Changing the API is likely to cause fun on Windows for new apps that find an old libpq.dll. Perhaps at this point it should become libpq82.dll? Hmm? Libpq already has a version number, I beleive it's upto 4.1 right now. So if any number is used, it should be that. And secondly, there have already been new functions added to the API without changing the library name so why should that happen here? In windows the trend seems to be to upgrade a library if the one on the system is too old. If programs are really worried about it, they should lookup the function dynamically rather than statically... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpOULLaTaXXq.pgp Description: PGP signature
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 08:59 To: Dave Page Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption On Mon, Dec 19, 2005 at 08:51:23AM -, Dave Page wrote: Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. Changing the API is likely to cause fun on Windows for new apps that find an old libpq.dll. Perhaps at this point it should become libpq82.dll? Hmm? Libpq already has a version number, I beleive it's upto 4.1 right now. So if any number is used, it should be that. Good point And secondly, there have already been new functions added to the API without changing the library name so why should that happen here? Because I suspect Tom hasn't suffered from 'dll hell' as a non-Windows user, and because noone else noticed. In windows the trend seems to be to upgrade a library if the one on the system is too old. Yes, however it's possible that there might be multiple copies of a dll on a single system. The search order for the DLLs has changed over the years and over different Windows versions though, so it's not infeasible for an app to upgrade one copy, but then load a different one when it runs. It shouldn't affect pgAdmin, psqlODBC or pgInstaller because we keep the DLLs local to the .exe's which is always first in the search path, but other apps might suffer. If programs are really worried about it, they should lookup the function dynamically rather than statically... For the sake of a simple name change? Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
On Mon, Dec 19, 2005 at 09:16:19AM -, Dave Page wrote: Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. If programs are really worried about it, they should lookup the function dynamically rather than statically... For the sake of a simple name change? The function as stated above doesn't exist yet, so we're adding a new function, not changing the name of one. The function that started the thread is not even exported by libpq so changing that shouldn't affect anybody. Besides, this whole discussion is moot until someone writes such a function. As for Windows DLL hell, I don't know a lot about that, but if that's such a problem, why didn't the original creators of the windows port stick the version number in there from the start. On UNIX, libpq is half versioned (the library is, but not the symbols) so I would have thought copying that idea would have been obvious. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpkcwhBckftT.pgp Description: PGP signature
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 09:38 To: Dave Page Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption On Mon, Dec 19, 2005 at 09:16:19AM -, Dave Page wrote: Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. If programs are really worried about it, they should lookup the function dynamically rather than statically... For the sake of a simple name change? The function as stated above doesn't exist yet, so we're adding a new function, not changing the name of one. The function that started the thread is not even exported by libpq so changing that shouldn't affect anybody. Besides, this whole discussion is moot until someone writes such a function. You missunderstand me - we were asked to start using the function in third party apps and I pointed out that it wasn't exported so we couldn't. Tom suggested exporting an API friendly version. As for the name, I meant the DLL name, not the function name. As for Windows DLL hell, I don't know a lot about that, but if that's such a problem, why didn't the original creators of the windows port stick the version number in there from the start. On UNIX, libpq is half versioned (the library is, but not the symbols) so I would have thought copying that idea would have been obvious. Because we simply didn't think of it at the time, and it's something that has irked me ever since. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
On Mon, Dec 19, 2005 at 10:32:03AM -, Dave Page wrote: As for Windows DLL hell, I don't know a lot about that, but if that's such a problem, why didn't the original creators of the windows port stick the version number in there from the start. On UNIX, libpq is half versioned (the library is, but not the symbols) so I would have thought copying that idea would have been obvious. Because we simply didn't think of it at the time, and it's something that has irked me ever since. In that case, I agree. I've always thought a lot of problem in windows could be solved if they systematically added a version number to every library (like in UNIX). Are there any reasons why we shouldn't change the libname with every release like for UNIX? I can't think of any, but you never know... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpzFNufC0E5d.pgp Description: PGP signature
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 10:42 To: Dave Page Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption In that case, I agree. I've always thought a lot of problem in windows could be solved if they systematically added a version number to every library (like in UNIX). Are there any reasons why we shouldn't change the libname with every release like for UNIX? I can't think of any, but you never know... Not that I can think of. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: Which qsort is used
On Fri, Dec 16, 2005 at 10:43:58PM -0800, Dann Corbit wrote: I am actually quite impressed with the excellence of Bentley's sort out of the box. It's definitely the best library implementation of a sort I have seen. I'm not sure whether we have a conclusion here, but I do have one question: is there a significant difference in the number of times the comparison routines are called? Comparisons in PostgreSQL are fairly expensive given the fmgr overhead and when comparing tuples it's even worse. We don't want to accedently pick a routine that saves data shuffling by adding extra comparisons. The stats at [1] don't say. They try to factor in CPU cost but they seem to use unrealistically small values. I would think a number around 50 (or higher) would be more representative. [1] http://www.cs.toronto.edu/~zhouqq/postgresql/sort/sort.html Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpWGyYf8ywud.pgp Description: PGP signature
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
As for Windows DLL hell, I don't know a lot about that, but if that's such a problem, why didn't the original creators of the windows port stick the version number in there from the start. On UNIX, libpq is half versioned (the library is, but not the symbols) so I would have thought copying that idea would have been obvious. Because we simply didn't think of it at the time, and it's something that has irked me ever since. In that case, I agree. I've always thought a lot of problem in windows could be solved if they systematically added a version number to every library (like in UNIX). Are there any reasons why we shouldn't change the libname with every release like for UNIX? I can't think of any, but you never know... Yes. If FooApp is compiled against 8.0, it will then be unable to run if you upgrade libpq to 8.1. IIRC on Unix it will fall forward to the new version if it's just a minor version upgrade (correct me if I'm wrong). On windows, it will break with an ugly dialog box. Which is why DLL renames are usually only done for backwards incompatible changes. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
On Mon, Dec 19, 2005 at 01:07:26PM +0100, Magnus Hagander wrote: If FooApp is compiled against 8.0, it will then be unable to run if you upgrade libpq to 8.1. IIRC on Unix it will fall forward to the new version if it's just a minor version upgrade (correct me if I'm wrong). On windows, it will break with an ugly dialog box. Which is why DLL renames are usually only done for backwards incompatible changes. Not quite, in UNIX you have a SONAME which is the file you search for at runtime. This might end up being symlinked to a different version than the one you linked against. The argument for the name change is that then you can have both the old version and the new versions installed at the same time. So when you upgrade to 8.1, you don't actually remove the old libpq but keep both around. Then programs using either will continue to work. On UNIX you don't actually waste any diskspace because you can symlink them together. So it's only an issue if you have a policy of removing old versions of libpq on upgrades... I'm not sure what's best practice on windows in this area. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpg7GOrTdwNo.pgp Description: PGP signature
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Martijn van Oosterhout wrote: So it's only an issue if you have a policy of removing old versions of libpq on upgrades... I'm not sure what's best practice on windows in this area. When removing the application (in this case: pgsql), you'd remove that old lib as well if it's the only app using it. If you have another application installed, the deinstaller should observe this, and keep the version. I'm voting +1 for lib name versions. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 12:07 To: Martijn van Oosterhout; Dave Page Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug Subject: RE: [HACKERS] [pgadmin-hackers] Client-side password encryption Yes. If FooApp is compiled against 8.0, it will then be unable to run if you upgrade libpq to 8.1. IIRC on Unix it will fall forward to the new version if it's just a minor version upgrade (correct me if I'm wrong). On windows, it will break with an ugly dialog box. Which is why DLL renames are usually only done for backwards incompatible changes. So each app ships with it's required version of libpq, thus preventing any issues, including problems caused by finding an older dll with a different API. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Yes. If FooApp is compiled against 8.0, it will then be unable to run if you upgrade libpq to 8.1. IIRC on Unix it will fall forward to the new version if it's just a minor version upgrade (correct me if I'm wrong). On windows, it will break with an ugly dialog box. Which is why DLL renames are usually only done for backwards incompatible changes. So each app ships with it's required version of libpq, thus preventing any issues, including problems caused by finding an older dll with a different API. It makes life easier for us. Only then we can be almost certain that all apps will ship with 8.0.0, 8.1.0 etc, and nobody will get any minor version upgrades. But yeah, the easiest for us is certainly to push that out to the app vendor. Not really a problem for me, just wanted to point out the scenario. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 14:50 To: Dave Page; Martijn van Oosterhout Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug Subject: RE: [HACKERS] [pgadmin-hackers] Client-side password encryption Yes. If FooApp is compiled against 8.0, it will then be unable to run if you upgrade libpq to 8.1. IIRC on Unix it will fall forward to the new version if it's just a minor version upgrade (correct me if I'm wrong). On windows, it will break with an ugly dialog box. Which is why DLL renames are usually only done for backwards incompatible changes. So each app ships with it's required version of libpq, thus preventing any issues, including problems caused by finding an older dll with a different API. It makes life easier for us. Only then we can be almost certain that all apps will ship with 8.0.0, 8.1.0 etc, and nobody will get any minor version upgrades. Why? I'm not advocating that the dll name change with revisions, only major or minor version changes or if the API changes (which should never happen from revision to revision (yes, I know...)), depending on which numbering scheme is used. Regards, Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Martijn van Oosterhout kleptog@svana.org writes: Are there any reasons why we shouldn't change the libname with every release like for UNIX? I can't think of any, but you never know... Surely that cure is far worse than the disease. You'd be trading a might-break risk (app using new function will fail if used with old library) for a guaranteed-to-break risk (*every* app fails if used with *any* library version other than what it was built against). The Unix version of the idea is considerably more flexible than what would happen on Windows. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 15:00 To: Martijn van Oosterhout Cc: Dave Page; Christopher Kings-Lynne; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption Martijn van Oosterhout kleptog@svana.org writes: Are there any reasons why we shouldn't change the libname with every release like for UNIX? I can't think of any, but you never know... Surely that cure is far worse than the disease. You'd be trading a might-break risk (app using new function will fail if used with old library) for a guaranteed-to-break risk (*every* app fails if used with *any* library version other than what it was built against). If it's changed to include the so version, or PG version in the filename (eg. Libpq41.dll, or libpq82.dll) then all we require is that a vendor ship the appropriate version with his app. If it installs in a shared location, it's guaranteed to only be upgraded by a point release because the windows installers have no convention for including version numbers in the filenames and will only upgrade a file of the name name, and with an older version number (from the version resource). Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: Which qsort is used
Martin, On 12/19/05 3:37 AM, Martijn van Oosterhout kleptog@svana.org wrote: I'm not sure whether we have a conclusion here, but I do have one question: is there a significant difference in the number of times the comparison routines are called? Comparisons in PostgreSQL are fairly expensive given the fmgr overhead and when comparing tuples it's even worse. It would be interesting to note the comparison count of the different routines. Something that really grabbed me about the results though is that the relative performance of the routines dramatically shifted when the indirect references in the comparators went in. The first test I did sorted an array of int4 - these tests that Qingqing did sorted arrays using an indirect pointer list, at which point the same distributions performed very differently. I suspect that it is the number of comparisons that caused this, and further that the indirection has disabled the compiler optimizations for memory prefetch and other things that it could normally recognize. Given the usage pattern in Postgres, where sorted things are a mix of strings and intrinsic types, I'm not sure those optimizations could be done by one routine. I haven't verified this, but it certainly seems that the NetBSD routine is the overall winner for the type of use that Postgres has (sorting the using a pointer list). - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Peter Eisentraut said: So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? Well, these changes have broken the windows build, so something needs to change.I don't see a reason in principle not to expose our routine, given that its name means it is unlikely to conflict with anything else. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Automatic function replanning
On Sat, Dec 17, 2005 at 01:07:10PM -0500, Bruce Momjian wrote: Jim C. Nasby wrote: Is cardinality the only thing we'd need to worry about? My idea was actually to track the amount of work normally required by a stored query plan, and if a query uses that plan but requires a very different amount of work it's a good indication that we either need to replan or store multiple plans for that query. Though if we're certain that cardinality is the only thing that could make a cached plan go bad it would certainly simplify things greatly. This gets into another area of re-optimizing when the executor finds that the actual tables don't match the optimizer estimates. I think we decided that was too hard/risky, but invalidating the plan might help, though I am thinking re-planning might just generate the same plan as before. I think something would need to have happened since the last planning, like ANALYZE or something. Well, in the stored plan case, presumably what's changed is one of the bound parameters. And if we want to be sophisticated about it, we won't just throw out the old plan; rather we'll try and figure out what parameter it is that's wanting a different plan. Updated TODO: * Flush cached query plans when the dependent objects change, when the cardinality of parameters changes dramatically, or when new ANALYZE statistics are available Probably worth pointing to this therad in the TODO... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Trouble building 8.1.1 on Tru64 UNIX 5.1
While building 8.1.1 on Tru64 UNIX 5.1: gmake[5]: Leaving directory `/opt/build/postgresql-8.1.1/src/port' cc -O2 -ieee -msym -readonly_strings -pthread --thread-safe -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I./../include -I. -I../../../../src/include -I/opt/TWWfsw/gettext014/include -I/opt/TWWfsw/readline50/include -I/opt/TWWfsw/libopenssl097/include -I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/tcl84/include -I/opt/TWWfsw/tk84/include -DMAJOR_VERSION=4 -DMINOR_VERSION=1 -DPATCHLEVEL=1 -c -o preproc.o preproc.c cc: Error: /usr/include/arpa/nameser_compat.h, line 154: Invalid declarator. (declarator) } HEADER; --^ The problem is that /usr/include/arpa/nameser_compat.h defines a struct named HEADER. This conflicts with the use of preproc.y in src/interfaces/ecpg/preproc/preproc.y. What should it be renamed to? -- albert chin ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Are there any reasons why we shouldn't change the libname with every release like for UNIX? I can't think of any, but you never know... Surely that cure is far worse than the disease. You'd be trading a might-break risk (app using new function will fail if used with old library) for a guaranteed-to-break risk (*every* app fails if used with *any* library version other than what it was built against). The Unix version of the idea is considerably more flexible than what would happen on Windows. Different from Unix distros, win32 apps will always bring all their required libraries with them, so it's totally under control of the developer/packager. There's no such thing as prerequisite packages for win32 installs, new lib names will *not* break other apps when installed because older ones stay untouched. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] free Sun T2000 for PostgreSQL community?
This would be handy for testing high-concurrency workloads. http://blogs.sun.com/roller/page/jonathan/20051218 -Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Trouble building 8.1.1 on Tru64 UNIX 5.1
Albert Chin [EMAIL PROTECTED] writes: The problem is that /usr/include/arpa/nameser_compat.h defines a struct named HEADER. This conflicts with the use of preproc.y in src/interfaces/ecpg/preproc/preproc.y. What should it be renamed to? Perhaps a more relevant question is why ecpg/preproc is including that header. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Trouble building 8.1.1 on Tru64 UNIX 5.1
On Mon, Dec 19, 2005 at 05:59:12PM -0500, Tom Lane wrote: Albert Chin [EMAIL PROTECTED] writes: The problem is that /usr/include/arpa/nameser_compat.h defines a struct named HEADER. This conflicts with the use of preproc.y in src/interfaces/ecpg/preproc/preproc.y. What should it be renamed to? Perhaps a more relevant question is why ecpg/preproc is including that header. #include netdb.h with -D_REENTRANT includes it. preproc.c: #include postgres_fe.h #include c.h #include port.h #include netdb.h ADD has the same problem. -- albert chin ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Lock issue when trying to vacuum db
Hi, I have a database that had a large table in it. I dropped the table, but when I try to full vacuum the db, it just freezes indefinitely. There are shared locks held on this that I can't identify. I've tried bouncing this instance and ran some queries immediately after starting up. The results are below. I've selected from pg_locks and pg_stat_activity when I started the instance and then again after I started the vacuum command. Any advice would be appreciated. Thanks a lot. Jess See query results below. Queries after starting the server before running vacuum scratch02= select * from pg_locks ; locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted ---+--+--+--+---+---+--- --+---+--+-+--+-+- relation |16389 |16721 | | | | | | |3969 | | AccessShareLock | t relation |16389 |16721 | | | | | | |1620 | | AccessShareLock | t transactionid | | | | | 70546 | | | | 70546 | 9762 | ExclusiveLock | t transactionid | | | | | 3969 | | | |3969 | | ExclusiveLock | t transactionid | | | | | 1620 | | | |1620 | | ExclusiveLock | t relation |16389 |10342 | | | | | | | 70546 | 9762 | AccessShareLock | t (6 rows) scratch02= select * from pg_Stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start | backend_start | client_addr | client_port ---+---+-+--+-+---+- --+---+-+--- -- 16389 | scratch02 |9762 |16384 | jbalint | IDLE| 2005-12-19 18:24:52.900749-05 | 2005-12-19 18:24:16.901981-05 | | -1 (1 row) Queries after starting starting the vacuum Notice the first lock isn't granted, which is keeping the vacuum from doing anything scratch02= select * from pg_locks ; locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid |mode | granted ---+--+--+--+---+---+--- --+---+--+-+--+-+- relation |16389 |16721 | | | | | | | 70610 | 9764 | AccessExclusiveLock | f relation |16389 |16721 | | | | | | |3969 | | AccessShareLock | t relation |16389 |16721 | | | | | | |1620 | | AccessShareLock | t relation |16389 |10342 | | | | | | | 70611 | 9762 | AccessShareLock | t transactionid | | | | | 3969 | | | |3969 | | ExclusiveLock | t transactionid | | | | | 1620 | | | |1620 | | ExclusiveLock | t transactionid | | | | | 70611 | | | | 70611 | 9762 | ExclusiveLock | t transactionid | | | | | 70610 | | | | 70610 | 9764 | ExclusiveLock | t (8 rows) scratch02= select * from pg_Stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start | backend_start | client_addr | client_port ---+---+-+--+-+---+- --+---+-+--- -- 16389 | scratch02 |9764 |16384 | jbalint | VACUUM full ; | 2005-12-19 18:25:24.748624-05 | 2005-12-19 18:25:14.743367-05 | | -1 16389 | scratch02 |9762 |16384 | jbalint | IDLE| 2005-12-19 18:25:32.011666-05 | 2005-12-19 18:24:16.901981-05 | | -1 (2 rows) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner
Re: [HACKERS] Trouble building 8.1.1 on Tru64 UNIX 5.1
Albert Chin [EMAIL PROTECTED] writes: On Mon, Dec 19, 2005 at 05:59:12PM -0500, Tom Lane wrote: Perhaps a more relevant question is why ecpg/preproc is including that header. #include netdb.h with -D_REENTRANT includes it. preproc.c: #include postgres_fe.h #include c.h #include port.h #include netdb.h Well, port.h is certainly doing a fine job of polluting the namespace. Maybe we should pull out the stuff that depends on netdb.h and pwd.h into some other header that isn't going to get included so widely. ADD has the same problem. There's no way that we are going to be able to dodge every single symbol that any random system header on any random platform might define --- especially when you get into the less-well-standardized headers like these. We have to think smaller in terms of what headers we include everywhere. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Trouble building 8.1.1 on Tru64 UNIX 5.1
On Mon, Dec 19, 2005 at 06:34:38PM -0500, Tom Lane wrote: Albert Chin [EMAIL PROTECTED] writes: On Mon, Dec 19, 2005 at 05:59:12PM -0500, Tom Lane wrote: Perhaps a more relevant question is why ecpg/preproc is including that header. #include netdb.h with -D_REENTRANT includes it. preproc.c: #include postgres_fe.h #include c.h #include port.h #include netdb.h Well, port.h is certainly doing a fine job of polluting the namespace. Maybe we should pull out the stuff that depends on netdb.h and pwd.h into some other header that isn't going to get included so widely. ADD has the same problem. There's no way that we are going to be able to dodge every single symbol that any random system header on any random platform might define --- especially when you get into the less-well-standardized headers like these. We have to think smaller in terms of what headers we include everywhere. Well, we've built on most versions of Solaris, HP-UX, AIX, Tru64 UNIX, Redhat Linux, and IRIX and this is the only symbol conflict we ran into. So, it's not a big problem. -- albert chin ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
By the way, I've already implemented this in phpPgAdmin trivially using the md5() function. I can't be bothered using a C library function :D Chris Dave Page wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 05:37 To: Christopher Kings-Lynne Cc: Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug; Dave Page Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption Christopher Kings-Lynne [EMAIL PROTECTED] writes: So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? Is it different to normal md5? How is this helpful to the phpPgAdmin project? It would be better to export an API that is (a) less random (why one input null-terminated and the other not?) and (b) less tightly tied to MD5 --- the fact that the caller knows how long the result must be is the main problem here. Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. Changing the API is likely to cause fun on Windows for new apps that find an old libpq.dll. Perhaps at this point it should become libpq82.dll? Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Christopher Kings-Lynne wrote: By the way, I've already implemented this in phpPgAdmin trivially using the md5() function. I can't be bothered using a C library function :D IIRC the whole point of this exercise was to avoid passing the password to the server in the first place. Unless you are talking about a PHP md5() password of course ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
I've already implemented this in phpPgAdmin trivially using the md5() function. I can't be bothered using a C library function :D IIRC the whole point of this exercise was to avoid passing the password to the server in the first place. Unless you are talking about a PHP md5() password of course ... Yes... However of course in phpPgAdmin the password has already been sent cleartext to the webserver from your browser, and the database connection password parameter is still sent in the clear so... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Lock issue when trying to vacuum db
Jess Balint [EMAIL PROTECTED] writes: Hi, I have a database that had a large table in it. I dropped the table, but when I try to full vacuum the db, it just freezes indefinitely. There are shared locks held on this that I can't identify. I've tried bouncing this instance and ran some queries immediately after starting up. AFAIK, the only way for a lock to survive a database restart is a prepared transaction. Are you running 8.1, and if so what does pg_prepared_xacts show? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] localization problem (and solution)
Here is a test case for a previously reported bug (see http://archives.postgresql.org/pgsql-general/2005-11/msg01235.php): initdb using es_MX.ISO-8859-1, start postgres using es_MX.UTF-8 and execute: create procedural language plperl; create or replace function foo() returns int as 'return 1' language 'plperl'; create table persona (nombre text check (nombre ~ '^[[:upper:]][[:lower:]]*([-''. [:alpha:]]+)?$'::text)); copy persona (nombre) from stdin; José \. It will error out saying: ERROR: new row for relation persona violates check constraint persona_nombre_check CONTEXT: COPY persona, line 1: José Commenting the creation of the plperl function (or moving it after the copy command) this script runs without errors. Also applying this patch solves the problem: *** src/backend/access/transam/xlog.c~ 2005-11-22 12:23:05.0 -0600 --- src/backend/access/transam/xlog.c 2005-12-19 20:34:22.0 -0600 *** *** 3626,3631 --- 3626,3632 which is not recognized by setlocale()., ControlFile-lc_collate), errhint(It looks like you need to initdb or install locale support.))); + setenv(LC_COLLATE, ControlFile-lc_collate, 1); if (setlocale(LC_CTYPE, ControlFile-lc_ctype) == NULL) ereport(FATAL, (errmsg(database files are incompatible with operating system), *** *** 3633,3638 --- 3634,3640 which is not recognized by setlocale()., ControlFile-lc_ctype), errhint(It looks like you need to initdb or install locale support.))); + setenv(LC_CTYPE, ControlFile-lc_ctype, 1); /* Make the fixed locale settings visible as GUC variables, too */ SetConfigOption(lc_collate, ControlFile-lc_collate, Some fprintf's around the regex code shows that someone is changing the localization parameters by those found in the enviroment, at least for the LC_CTYPE and LC_COLLATE categories, and plperl seems to be the culprit. Needless to say that this bug might lead to index corruption beside other problems. It also explains some very wired (and very difficult to reproduce) anomalies I have seen. Regards, Manuel. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] localization problem (and solution)
Manuel Sugawara masm@fciencias.unam.mx writes: Some fprintf's around the regex code shows that someone is changing the localization parameters by those found in the enviroment, at least for the LC_CTYPE and LC_COLLATE categories, and plperl seems to be the culprit. Indeed. Please file a bug with the Perl people asking what right libperl has to fool with the localization environment of its host application. (Your proposed fix seems entirely useless ... maybe we could fix it by resetting the LC_FOO variables after every call to libperl, but I bet that would break libperl instead.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lo function changed in PostgreSQL 8.1.1
From contrib/lo I found that it has something difference between old and new version of PostgreSQL. And I'm sure that I already tick on Large Object (lo) option when I install. How can I manage on difference function? :::New Version::: DOMAIN lo AS pg_catalog.oid; FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS 'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE; FUNCTION lo_manage() RETURNS pg_catalog.trigger AS '$libdir/lo' LANGUAGE C; :::Old Version::: FUNCTION lo_in(cstring) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; FUNCTION lo_out(lo) RETURNS cstring AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; TYPE lo ( INTERNALLENGTH = 4, EXTERNALLENGTH = variable, INPUT = lo_in, OUTPUT = lo_out); FUNCTION lo_oid(lo) RETURNS oid AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; FUNCTION oid(lo) RETURNS oid AS '$libdir/lo', 'lo_oid' LANGUAGE C IMMUTABLE STRICT; CAST (lo as oid) WITH FUNCTION oid(lo) AS IMPLICIT; FUNCTION lo(oid) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; CAST (oid as lo) WITH FUNCTION lo(oid) AS IMPLICIT; FUNCTION lo_manage() RETURNS trigger AS '$libdir/lo' LANGUAGE C; Tom Lane [EMAIL PROTECTED] 12/14/2005 22:06:33 "Premsun Choltanwanich" [EMAIL PROTECTED] writes: I think I use contrib/lo type for manage lo. What is wrong?You need to use the 8.1 version of contrib/lo, not any previous version.You might find that the easiest way to manage this is to create an emptydatabase, load the 8.1 contrib/lo definitions by running lo.sql, thenrestoring from your pg_dump backup. You'll see some complaints aboutduplicate function definitions but these can be ignored.regards, tom lane
Re: [HACKERS] Lock issue when trying to vacuum db
That was it. There were two in there. I rolled 'em back and everything is smooth now. Thanks a lot. Jess -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, December 19, 2005 10:03 PM To: Jess Balint Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Lock issue when trying to vacuum db Jess Balint [EMAIL PROTECTED] writes: Hi, I have a database that had a large table in it. I dropped the table, but when I try to full vacuum the db, it just freezes indefinitely. There are shared locks held on this that I can't identify. I've tried bouncing this instance and ran some queries immediately after starting up. AFAIK, the only way for a lock to survive a database restart is a prepared transaction. Are you running 8.1, and if so what does pg_prepared_xacts show? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] localization problem (and solution)
Tom Lane [EMAIL PROTECTED] writes: (Your proposed fix seems entirely useless ... While there are reasons to argue that's Perl fault, IMO, an environment that reflects the current state of the host program is a good compromise, and behave environment-consistent is also a good compromise for libperl (I think some applications of libperl will get really upset if this compromise is broken by the library.) Regards, Manuel. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
