[HACKERS] Missing files on Postgres8.0.4 Win32 Installation
Hi all, I installed postgres 8.0.4 on a win32 box and I found out: libpq-fe.h and libpqdll.lib are missing. Is that normal? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Missing files on Postgres8.0.4 Win32 Installation
Hi all, I installed postgres 8.0.4 on a win32 box and I found out: libpq-fe.h and libpqdll.lib are missing. Is that normal? This sounds like you didn't include the development files. They are not installed by defauly, you have to select it on the feature selection screen. //Magnus ---(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] Possible issue with win32 installer(8.1beta 3)...
Hi, I just installed the win32 8.1beta 3 installer on the same PC as my client, and I selected not to install PGAdmin III for obvious reasons and the installer seemed to remove my existing libpq.dll, I went to start up Delphi, and got tons of errors saying could not load libpq.dll did a search for libpq.dll and sure enough the installer blew it away. I am pretty sure it did not have this behavior before, but I could be wrong. The installer needs to have a option to install the client libs seperately if PGAdmin III is not selected for installation OR leave the currently installed libs alone OR prompt the user that it is going to replace them or remove them. (PG Admin III is not the only admin program around you know) I would normally just have a copy of libpq.dll in the same dir as my exe, but with the latest XP service packs this does not work anymore, not to mention libpq.dll seems to have several dependencies that also must be in system32 i.e. libpq.dll seems to have these dependencies hard coded to system32, because if I remove the dependencies from system32 and put them the same dir as my exe, I get libpq errors saying it can't find libiconv-2.dll libintl-2.dll even though they are in the same dir. Can't these two dependencies be staticly linked into libpq.dll to ease deployment issues? If libpq.dll was all self contained, I could rename it to something just my app could use and then I would not have to worry about the server setup blowing away libpq.dll. Who would care if it made the dll bigger? you have to deploy all the files anyway. (I don't know any thing about C, so I can't try myself) Thanks, -- Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Vacuum only a Schema ?
Hi, What about only vacuum a schema ? Is it a stupid idea or is it plannified in futur release ... ? What about also give the ability to vacuum all tables except some big one ? Regards, -- Hervé Piedvache ---(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] PostgreSQL roadmap for 8.2 and beyond.
Neil Conway wrote: On Sun, 2005-16-10 at 01:20 -0700, Kevin McArthur wrote: Don't forget insert/update returning. Omar Kilani has a patch for this: http://archives.postgresql.org/pgsql-patches/2005-07/msg00568.php I would like to see it get into 8.2 Yes, this is in the 8.2 patches queue: http://momjian.postgresql.org/cgi-bin/pgpatches_hold -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] slow IN() clause for many cases
On Fri, 2005-10-14 at 19:09 -0400, Tom Lane wrote: I wrote: I'm thinking that IN should be converted to a ScalarArrayOpExpr, ie x = ANY (ARRAY[val1,val2,val3,val4,...]) Actually, there is one little thing in the way of doing this: it'll fail if any of the IN-list elements are NULL, because we have not got support for arrays with null elements. So we'd have to fix that first. You'd also need to consider how this effects partial indexes and constraint exclusion. Not much of an issue, but an extra case to handle in the predicate proving code. = = = Just had a case where using an IN list was quicker than using a join because it allowed an index lookup to occur. There is also some clear mileage in transforming this type of query to a more plannable form: select * from bigtable where word IN ( select word from customer_word where customer = 6) i.e. where the values for the IN clause are evaluated at run time, rather than at plan time. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Possible issue with win32 installer(8.1beta 3)...
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto Sent: 17 October 2005 06:43 To: pgsql-hackers@postgresql.org Subject: [HACKERS] Possible issue with win32 installer(8.1beta 3)... Hi, I just installed the win32 8.1beta 3 installer on the same PC as my client, and I selected not to install PGAdmin III for obvious reasons and the installer seemed to remove my existing libpq.dll, I went to start up Delphi, and got tons of errors saying could not load libpq.dll did a search for libpq.dll and sure enough the installer blew it away. I am pretty sure it did not have this behavior before, but I could be wrong. The 8.1 installer is completely stand-alone and doesn't install any shared libraries. It certainly won't delete anything that was already there - that would require us to actively write code to look for and and remove files, something we purposefully haven't done. The installer needs to have a option to install the client libs seperately if PGAdmin III is not selected for installation OR leave the currently installed libs alone OR prompt the user that it is going to replace them or remove them. (PG Admin III is not the only admin program around you know) The client libraries are installed as a required core component, regardless of whether you select pgAdmin. It needs to be done this way because other components such as psqlodbc, pgoledb and even the contrib module dblink require libpq. I would normally just have a copy of libpq.dll in the same dir as my exe, but with the latest XP service packs this does not work anymore, It should do - in fact we package 8.1 in exactly that way after extensive testing on XP SP2 as well as 2K3 SP1 (not to mention older platforms). not to mention libpq.dll seems to have several dependencies that also must be in system32 i.e. libpq.dll seems to have these dependencies hard coded to system32, because if I remove the dependencies from system32 and put them the same dir as my exe, I get libpq errors saying it can't find libiconv-2.dll libintl-2.dll even though they are in the same dir. When we build the installer it's tested on a number of clean installations of Windows before release. Just to be sure, I just double checked it on a clean Windows 2K3 Enterprise Server, with SP1 + patches that I happen to be configuring at the moment. All the libraries are found, even though none are in any shared locations (they're all in C:\Program Files\PostgreSQL-8.1-beta3\bin). For info, libpq requires libiconv-2.dll libintl-2.dll libeay32.dll ssleay32.dll Comerr32.dll krb5_32.dll Can't these two dependencies be staticly linked into libpq.dll to ease deployment issues? If libpq.dll was all self contained, I could rename it to something just my app could use and then I would not have to worry about the server setup blowing away libpq.dll. Who would care if it made the dll bigger? you have to deploy all the files anyway. (I don't know any thing about C, so I can't try myself) *If* each of those sub project were easily compilable in static mode on Windows then that would probably be possible, given a few extra hours in the day. As it is, most have very limited support for Windows, and often finding a way to build a normal dynamic version is hard enough. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
I fail to see how this solves the problem of getting auto generated keys. AFAIKS, the protocol needs to be tweaked to return at a minimum the currval for the first serial in the row, but more correctly all of the modified currval's for an insert if we had that then we could correctly implement getAutoGeneratedKeys for jdbc. Dave On 17-Oct-05, at 8:33 AM, Bruce Momjian wrote: Neil Conway wrote: On Sun, 2005-16-10 at 01:20 -0700, Kevin McArthur wrote: Don't forget insert/update returning. Omar Kilani has a patch for this: http://archives.postgresql.org/pgsql-patches/2005-07/msg00568.php I would like to see it get into 8.2 Yes, this is in the 8.2 patches queue: http://momjian.postgresql.org/cgi-bin/pgpatches_hold -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] MemoryContext and NodeTags
I'm rewriting parts of PL/Java to be more secure. One of the areas where I'd like to improve things concerns ownership of allocated structures. Many structures, such as TupleDesc, HeapTuple, ErrorData, etc. can be copied into another MemoryContext for safe keeping. PL/Java uses this when creating Java wrappers for such objects. Prior to the rewrite, I maintained mappings from pointers to Java wrappers in a hash table. Now, I instead have a special MemoryContext that can hold a reference to the Java wrapper in the chunk header. Both simpler and more efficient (I also have wet dreams about a future MemoryContext that allocates shared memory). But, at present, and because of this macro: /* * MemoryContextIsValid *True iff memory context is valid. * * Add new context types to the set accepted by this macro. */ #define MemoryContextIsValid(context) \ ((context) != NULL \ (IsA((context), AllocSetContext))) I have to cheat and claim that this MemoryContext has the NodeType of T_AllocSetContext. I have a proposal: The NodeTag T_MemoryContext has the value of 600 and the next occupied entry is T_Value which is 650. - Reserve half of that range for PostgreSQL specific contexts (today you only use one), and the other half for custom contexts. - Change the above macro to consider values between 601 and 649 as valid tags. The likelihood of an invalid context hitting that range is second to none. - Accept patches to nodes/nodes.h for new custom tags (properly motivated of course). What do you think? Would a patch that implements this proposal and adds a T_PLJavaContext NodeTag be accepted? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible issue with win32 installer(8.1beta 3)...
Dave Page wrote: I would normally just have a copy of libpq.dll in the same dir as my exe, but with the latest XP service packs this does not work anymore, It should do - in fact we package 8.1 in exactly that way after extensive testing on XP SP2 as well as 2K3 SP1 (not to mention older platforms). If this were true it would break a huge number of apps, including buildfarm, which relies on DLLs in the .exe directory being found. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MemoryContext and NodeTags
Thomas Hallgren [EMAIL PROTECTED] writes: I have a proposal: The NodeTag T_MemoryContext has the value of 600 and the next occupied entry is T_Value which is 650. - Reserve half of that range for PostgreSQL specific contexts (today you only use one), and the other half for custom contexts. OK. - Accept patches to nodes/nodes.h for new custom tags (properly motivated of course). No. Define 'em yourself. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MemoryContext and NodeTags
[EMAIL PROTECTED] wrote: Thomas Hallgren [EMAIL PROTECTED] writes: I have a proposal: The NodeTag T_MemoryContext has the value of 600 and the next occupied entry is T_Value which is 650. - Reserve half of that range for PostgreSQL specific contexts (today you only use one), and the other half for custom contexts. OK. - Accept patches to nodes/nodes.h for new custom tags (properly motivated of course). No. Define 'em yourself. OK, I can do that. But I have a couple of reasons why I think that it would be a good idea to get my definitions into node.h: - If more module authors want to do similar things, they would not risk defining overlapping tags. - The NodeTag is an enum. Code that defines tags that are supposed to fit in becomes ugly. - The IsA macro can be used. - You (PostgreSQL core) want full control over the tags. If all tags are in nodes.h, you can move tags to other number ranges without creating a hassle for people like me. Regards, Thomas Hallgren. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] MemoryContext and NodeTags
Thomas Hallgren [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: No. Define 'em yourself. OK, I can do that. But I have a couple of reasons why I think that it would be a good idea to get my definitions into node.h: - If more module authors want to do similar things, they would not risk defining overlapping tags. Only for those module authors who manage to get their tags accepted; and even for them, only for PG versions later than when they start working. Not much of an extension mechanism, is it? - The NodeTag is an enum. Code that defines tags that are supposed to fit in becomes ugly. I don't see anyone trying to switch over MemoryContext tags, so this is really pretty irrelevant. AFAICS it should work just fine to do #define T_FooNode ((NodeTag) (T_FirstPrivateNode + 1)) - The IsA macro can be used. Still can AFAICS --- that macro knows nothing about the enum, just about the convention that Foo and T_Foo are related names. - You (PostgreSQL core) want full control over the tags. If all tags are in nodes.h, you can move tags to other number ranges without creating a hassle for people like me. As long as you define your tag as T_Something + N, that still holds. regards, tom lane ---(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
[HACKERS] More problems with the win32 installer for 8.1 beta3
Hi all, I sent out a message about this before, but for reasons beyond my control, I could not continue that thread. Anyway, not only does the installer blow away libpq.dll, it also removes all the Open SSL dlls, this is even more troubling because LOTS of other apps depend on OpenSSL. This morning when I got to work I downloaded beta3 and uninstalled 8.03, then installed it (beta3) and of course did not select PG Admin III, then did a search for libpq.dll and it was gone, not sure if uninstalling 8.03 got rid of it or if installing 8.1 did the deed. I then went ahead and used a remote control app that uses open SSL, then I get Can't load libeay32.dll So in conclusion either the installer or uninstaller is blowing away system DLLS without even asking me if I want to keep them, this is very bad behavior. Thanks, Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MemoryContext and NodeTags
[EMAIL PROTECTED] wrote: Not much of an extension mechanism, is it? Yes it is. If you are a module writer and want to define your own tag, the chances are pretty hight that you would look for available tags in the current CVS HEAD before you go ahead, thus avoiding any collision. First come, first served. You will of course need #ifdef's for backward compatibility but at some point in time, they can be removed (as I now do with a lot of stuff for 7.x and custom variable classes as I'm dropping the 7.x support). If you don't get your tag accepted then you're on your own of course. Then again, if the core team has reservations to accepting your tag there's a bigger problem somewhere. I don't see anyone trying to switch over MemoryContext tags, so this is really pretty irrelevant. AFAICS it should work just fine to do #define T_FooNode ((NodeTag) (T_FirstPrivateNode + 1)) I don't see how #define rectifies ugliness. It's horrible in the debugger and it screws up code-completion etc. in any IDE. Not being able to use a switch is a minor problem. - You (PostgreSQL core) want full control over the tags. If all tags are in nodes.h, you can move tags to other number ranges without creating a hassle for people like me. As long as you define your tag as T_Something + N, that still holds. No, that's false. Assume a range is full and you need to expand it. Someone within that range has to move. Everyone uses the same T_Something... Regards, Thomas Hallgren ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] More problems with the win32 installer for 8.1 beta3
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto Sent: 17 October 2005 15:53 To: pgsql-hackers@postgresql.org Subject: [HACKERS] More problems with the win32 installer for 8.1 beta3 Hi all, I sent out a message about this before, but for reasons beyond my control, I could not continue that thread. Anyway, not only does the installer blow away libpq.dll, it also removes all the Open SSL dlls, this is even more troubling because LOTS of other apps depend on OpenSSL. This morning when I got to work I downloaded beta3 and uninstalled 8.03, then installed it (beta3) and of course did not select PG Admin III, then did a search for libpq.dll and it was gone, not sure if uninstalling 8.03 got rid of it or if installing 8.1 did the deed. I then went ahead and used a remote control app that uses open SSL, then I get Can't load libeay32.dll So in conclusion either the installer or uninstaller is blowing away system DLLS without even asking me if I want to keep them, this is very bad behavior. Now that's different from what you said earlier. Uninstalling 8.0 will remove any DLLs it installed, unless their reference count in non-zero in which case it should leave them. Did your remote control program increment the reference count for the ssl libs? Regardless of the answer to that, it's the Windows installer that removes files, so this particular issue should be directed at Microsoft. It should also be noted that the SSL libraries prior to 0.9.8 contained no versioning info, so could easily be overwritten by other progams (Crystal Reports is a commonly noted example because they use a very old incompatible version that shows up very quickly). We now use 0.9.8 which does have proper version info. Regards, Dave. ---(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] Possible issue with win32 installer(8.1beta 3)...
I would normally just have a copy of libpq.dll in the same dir as my exe, but with the latest XP service packs this does not work anymore, It should do - in fact we package 8.1 in exactly that way after extensive testing on XP SP2 as well as 2K3 SP1 (not to mention older platforms). If this were true it would break a huge number of apps, including buildfarm, which relies on DLLs in the .exe directory being found. The difference in the latest servicepacks is that no longer is the *current directory* searched first for DLL files. However, *the directory of the executable* is still searched. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Something wrong after file system level backup
The situation is like that: [19:24] Press_Enter Who can help with file system level backup? [19:24] KL Press_Enter: shut down postgres, tar the data dir [19:25] KL Press_Enter: apparently filesystem snapshots might work as well [19:25] KL Press_Enter: you're supposed to use pg_dumpall [19:25] Press_Enter KL, my question is some deeper =) I`ve backup /var/lib/pgsql/data, then reinstall Postres (the same version) and then copy all data back. But psql dosn`t see any tables [19:26] KL Press_Enter: exact same version? [19:26] Press_Enter KL, yes [19:26] KL Press_Enter: did you stop postgres while you backed it up? [19:26] Press_Enter KL, yes [19:27] KL Press_Enter: then there shouldn't be any problem... [19:27] KL Press_Enter: try select * from information_schema.tables; [19:29] Press_Enter KL, ERROR: Namespace information_schema does not exist [19:29] KL Press_Enter: what pgsql version? [19:30] Press_Enter KL, 7.3.4 [19:30] KL Press_Enter: ah sorry [19:30] KL Press_Enter: try select * from pg_tables; (or pg_table) [19:28] Press_Enter ERROR: Namespace information_schema does not exist [19:28] Press_Enter ERROR: Namespace information_schema does not exist [19:33] Press_Enter only rows from schemaname pg_catalog (29 rows) [19:33] KL hmm [19:33] KL are you sure you're in the right db? [19:34] KL because if something was wrong with your backup i wouldn't expect ANYTHING to work [19:34] KL not end up with a fresh clean database [19:34] KL you didn't re-run initdb did you? [19:34] Press_Enter After installation i only copy all the /var/lib/pgsql/data [19:34] Press_Enter And didn`t did initdb [19:35] KL it's a mystery to me [19:35] KL did you install from packages? [19:35] Press_Enter yes [19:36] KL mayeb the packages did their own initdb somewhere [19:36] Press_Enter in /var/lib/pgsql/data i see manually all the data, but psql doesn`t [19:36] KL and then when you ran pgsql it used a fresh database INSTEAD of looking at your restored dir? [19:36] KL ie. pgsql isn't even using /var/lib/pgsql/data [19:36] KL you can test that by stopping postgresql [19:36] KL then running postmaster -D /var/lib/pgsql/data [19:37] KL then in another window see if you can connect [19:37] KL then just ctrl-c the postmaster when you're done [19:39] Press_Enter [19:37] KL then in another window see if you can connect - yes, i can connect, but it`s the same situation [19:39] KL i have no idea then [19:39] KL you'll have to email the developers list [19:39] KL pgsql-hackers@postgresql.org [19:40] KL if you subscribe first from the pgsql website then you won't have to wait for your post to be approved [19:40] Press_Enter ok, thx If in two words - i have file system level backup and i can see manually all the data in it, but PostreSQL doesn`t. What can i do? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] More problems with the win32 installer for 8.1 beta3
Hi all, I sent out a message about this before, but for reasons beyond my control, I could not continue that thread. Anyway, not only does the installer blow away libpq.dll, it also removes all the Open SSL dlls, this is even more troubling because LOTS of other apps depend on OpenSSL. This morning when I got to work I downloaded beta3 and uninstalled 8.03, then installed it (beta3) and of course did not select PG Admin III, then did a search for libpq.dll and it was gone, not sure if uninstalling 8.03 got rid of it or if installing 8.1 did the deed. I then went ahead and used a remote control app that uses open SSL, then I get Can't load libeay32.dll So in conclusion either the installer or uninstaller is blowing away system DLLS without even asking me if I want to keep them, this is very bad behavior. The uninstall will indeed remove the files. The install will not touch them. OpenSSL libraries should never have gone in SYSTEM32, because they contain no versioning information. If they did, they could be dealt with in a better way in the installer. Right now we're more or less at the mercy of Windows Instlaler, which will remove the files that it originally installed unless someone else registered in the MSI database that they were using it. Your solution to this is to copy the openssl DLL files to each applications binary directory. It may suck, but that's how you'll have to do it :( //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
I've not actually looked in the source but i presume that insert returning would work internally similar to a select in an update_after or insert_after trigger. If so then it should not care that an entry is a sequence or otherwise, it should care what the actual data in the table is. It must work on the actual data to properly work with defaults that are a product of a function that is not a serial. Eg a uniqueidentifier's newid() func. I could be wrong. But insert returning without picking up column defaults would be extremely useless. getAutoGeneratedKeys in jdbc seems like it would be a very useful interface, so if this patch doesnt support implementing this, someone should fix that. (I'd check/fix it myself, but i don't have much of a clue when it comes to c programming) Kevin McArthur - Original Message - From: Dave Cramer [EMAIL PROTECTED] To: Bruce Momjian pgman@candle.pha.pa.us Cc: Neil Conway [EMAIL PROTECTED]; PostgreSQL-development pgsql-hackers@postgresql.org Sent: Monday, October 17, 2005 6:12 AM Subject: Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond. I fail to see how this solves the problem of getting auto generated keys. AFAIKS, the protocol needs to be tweaked to return at a minimum the currval for the first serial in the row, but more correctly all of the modified currval's for an insert if we had that then we could correctly implement getAutoGeneratedKeys for jdbc. Dave On 17-Oct-05, at 8:33 AM, Bruce Momjian wrote: Neil Conway wrote: On Sun, 2005-16-10 at 01:20 -0700, Kevin McArthur wrote: Don't forget insert/update returning. Omar Kilani has a patch for this: http://archives.postgresql.org/pgsql-patches/2005-07/msg00568.php I would like to see it get into 8.2 Yes, this is in the 8.2 patches queue: http://momjian.postgresql.org/cgi-bin/pgpatches_hold -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] PostgreSQL roadmap for 8.2 and beyond.
On Mon, Oct 17, 2005 at 09:12:35AM -0400, Dave Cramer wrote:
I fail to see how this solves the problem of getting auto generated
keys.
AFAIKS, the protocol needs to be tweaked to return at a minimum the
currval for the first serial in the row, but more correctly all of
the modified currval's for an insert
In what sense? It seems to do exactly what you want. The example in the
documentation is:
INSERT INTO films (title) VALUES ('Yojimbo') RETURNING film_id;
film_id
-
123
The protocol allows you to return a result set for any command already
so I don't think there's any protocol changes at all. You don't even
need to know the name of the sequence which is something I wasn't even
hoping for. Well done...
if we had that then we could correctly implement getAutoGeneratedKeys
for jdbc.
There is a function now to return the sequence associated with a table
so I think this would be quite straightforward actually, assuming you
know the table being operated on.
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.
pgpRKb2spaOwp.pgp
Description: PGP signature
Re: [HACKERS] dynamic loading of .so (originally from pgsql-general)
Tom, My project, Veil, steals some of this shared memory for itself. I wan't aware of the slop factor and was pleased that it just worked. I guess I should have been asking questions of this group. Sorry. I would like Veil to be a good citizen and place a legitimate request for its shared memory (probably about 16K normally). Veil is loaded as a shared library, which I would assume means that it is not present during database startup, making contributing to the sizing calculation and racing the lockmgr a little tricky. I see a number of possibilities: - Have a GUC to allocate shmem space for add-ons - Have add-ons register themselves and provide hooks for sizing and initial space allocation - Let add-ons race with the lockmgr for the slop (ie leave as it is) Thoughts? I would be happy to work on this and provide whatever patches are necessary. Thanks. __ Marc Munro On Mon, 2005-10-17 at 10:34 -0300, [EMAIL PROTECTED] wrote: Date: Mon, 17 Oct 2005 00:42:17 -0400 From: Tom Lane [EMAIL PROTECTED] To: Douglas McNaught [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], pgsql-general@postgresql.org pgsql-general@postgresql.org Subject: Re: dynamic loading of .so Message-ID: [EMAIL PROTECTED] Douglas McNaught [EMAIL PROTECTED] writes: [EMAIL PROTECTED] writes: are there any way to make them global for all the instances? Put them in shared memory. This probably isn't trival, as all the shared memory is allocated up front and used for existing purposes (at least, as I understand it). There's a slop factor of 100KB or so in the shared memory size calculation, which means that an add-on library that requests space soon enough could probably get away with allocating up to a few KB without causing any problems. (The slop is not totally useless, since for instance the lock manager might eat it up if more locks get requested than expected.) In the long run it might be interesting to add hooks that allow preloaded libraries to contribute to the shmem sizing calculation and then to snarf up the space they've requested before it can get eaten by the lockmgr. regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Possible issue with win32 installer(8.1beta 3)...
Magnus Hagander wrote: If this were true it would break a huge number of apps, including buildfarm, which relies on DLLs in the .exe directory being found. The difference in the latest servicepacks is that no longer is the *current directory* searched first for DLL files. However, *the directory of the executable* is still searched. That's very different from what was stated, and makes far more sense. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] libpq's pollution of application namespace
On Sun, Oct 16, 2005 at 06:21:37PM -0400, Tom Lane wrote:
I find that libpq.so exports the following symbols that have neither
PQ, pq, pg, nor lo_ as a prefix:
snip
It'd be nicer if we could filter out all exported symbols that don't
appear in exports.txt, but I don't know any portable way to do that.
With GNU LD it is trivial, using the --version-script command. If you
use AWK to create the script from exports.txt like so:
awk 'BEGIN { print { global: } { if( $1 != # ) {print $1,;} } END {
print local: *; }; }' exports.txt exports.version
And then add -Wl,--version-script,exports.version to the link
command, viola, stray symbols removed. Given we already have a
configure test for GNU ld, it wouldn't be too hard to make it work for
them. For windows it already uses exports.txt. What other linkers do we
need to support?
Another possibility would be to use strip like so:
strip -w -K PQ* -K pq* -K pg* -K lo_* -K *PQ* -o output.so
But then, that may be a GNU strip extention... And it doesn't follow
the exports file then.
Recent gcc versions support visibility directives in the source code but
that's a lot more work (although doing it in the code would produce a
more efficient library). And not portable to other compilers either...
Hope this helps,
--
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.
pgpBC6uDuG1Xx.pgp
Description: PGP signature
Re: [HACKERS] libpq's pollution of application namespace
Martijn van Oosterhout kleptog@svana.org writes: What other linkers do we need to support? All the non-GNU ones. (I'm already desperately unhappy about the thin representation of non-GNU toolchains in the build farm...) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Something wrong after file system level backup
[EMAIL PROTECTED] writes: If in two words - i have file system level backup and i can see manually all the data in it, but PostreSQL doesn`t. What can i do? You messed up the backup/restore somehow: the system is running with a transaction counter much less than what it was when you took the backup (in fact, less than what it was when you created the missing tables). So all the catalog rows appear to be in the future. You sure you backed up *all* of $PGDATA, including pg_xlog, pg_global, and so on? 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] More problems with the win32 installer for 8.1 beta3
Magnus Hagander wrote: Hi all, I sent out a message about this before, but for reasons beyond my control, I could not continue that thread. Anyway, not only does the installer blow away libpq.dll, it also removes all the Open SSL dlls, this is even more troubling because LOTS of other apps depend on OpenSSL. This morning when I got to work I downloaded beta3 and uninstalled 8.03, then installed it (beta3) and of course did not select PG Admin III, then did a search for libpq.dll and it was gone, not sure if uninstalling 8.03 got rid of it or if installing 8.1 did the deed. I then went ahead and used a remote control app that uses open SSL, then I get Can't load libeay32.dll So in conclusion either the installer or uninstaller is blowing away system DLLS without even asking me if I want to keep them, this is very bad behavior. The uninstall will indeed remove the files. The install will not touch them. OpenSSL libraries should never have gone in SYSTEM32, because they contain no versioning information. If they did, they could be dealt with in a better way in the installer. Right now we're more or less at the mercy of Windows Instlaler, which will remove the files that it originally installed unless someone else registered in the MSI database that they were using it. Your solution to this is to copy the openssl DLL files to each applications binary directory. It may suck, but that's how you'll have to do it :( I actually tried that with libpq and it didn't really work because of the libintl-2.dll and libiconv-2.dll dependencies. I removed libpq.dll,libintl-2.dll and libiconv-2.dll from the system32 dir, I placed all three in my applications directory and when I loaded libpq.dll it complained that it could not find libintl-2.dll and libiconv-2.dll, I can only conclude that paths to libintl-2.dll and libiconv-2.dll are hard coded in libpq.dll somewhere. Is there anyway to get a libpq that does not depend on libintl-2.dll and libiconv-2.dll? (short me spinning my wheels for hours trying to figure out C code). Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq's pollution of application namespace
Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: What other linkers do we need to support? All the non-GNU ones. (I'm already desperately unhappy about the thin representation of non-GNU toolchains in the build farm...) Me too. If you provide a list of the most important platforms/toolsets missing I will see if I can talk some people into donating resources. HPUX is a glaring hole although I know you have that covered personally. cheers cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Martijn van Oosterhout kleptog@svana.org writes:
On Mon, Oct 17, 2005 at 09:12:35AM -0400, Dave Cramer wrote:
AFAIKS, the protocol needs to be tweaked to return at a minimum the
currval for the first serial in the row, but more correctly all of
the modified currval's for an insert
In what sense? It seems to do exactly what you want. The example in the
documentation is:
INSERT INTO films (title) VALUES ('Yojimbo') RETURNING film_id;
What Dave wants is for INSERT to automagically return any autogenerated
keys, *without* any explicit RETURNING clause.
I don't think that's a reasonable request, however: it amounts to a
request to break the protocol and impose possibly-useless overhead on
everyone's inserts, in order to save the JDBC driver some work in
analyzing table metadata.
regards, tom lane
---(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] libpq's pollution of application namespace
On Mon, Oct 17, 2005 at 12:20:06PM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: What other linkers do we need to support? All the non-GNU ones. (I'm already desperately unhappy about the thin representation of non-GNU toolchains in the build farm...) Ok, so it's a matter of research and testing. HPUX for example don't have a version map and doesn't have the strip options either, but allows you to specify: +hideallsymbols +e sym +e sym You can dump them all into a file and pull it in with -c filename I can see a list of supported platforms [1], but not a list of supported compilers/linkers. If it's just a matter of reasearching the command-line options that can be done fairly easily, if anyone's interested... Have a nice day, [1] http://www.postgresql.org/docs/8.0/interactive/supported-platforms.html -- 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. pgpGJ0bA4SfS9.pgp Description: PGP signature
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
On 17-Oct-05, at 12:43 PM, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
On Mon, Oct 17, 2005 at 09:12:35AM -0400, Dave Cramer wrote:
AFAIKS, the protocol needs to be tweaked to return at a minimum the
currval for the first serial in the row, but more correctly all of
the modified currval's for an insert
In what sense? It seems to do exactly what you want. The example
in the
documentation is:
INSERT INTO films (title) VALUES ('Yojimbo') RETURNING film_id;
What Dave wants is for INSERT to automagically return any
autogenerated
keys, *without* any explicit RETURNING clause.
Yes, this is the essence of what would be required.
I don't think that's a reasonable request, however: it amounts to a
request to break the protocol and impose possibly-useless overhead on
everyone's inserts, in order to save the JDBC driver some work in
analyzing table metadata.
The JDBC problem at hand is there is a method which allows one to
retrieve the
autogenerated keys from an insert. I can understand Tom's argument
here. It should
be possible for the driver to build a query from the meta data.
On the other hand given that all of the serial increments are stored
in the session is it possible to
get the results of the last insert on the session ? If we can avoid
the extra query so much
the better, but either way is better than what we have ?
Dave
regards, tom lane
---(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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] libpq's pollution of application namespace
Martijn van Oosterhout kleptog@svana.org writes: I can see a list of supported platforms [1], but not a list of supported compilers/linkers. If it's just a matter of reasearching the command-line options that can be done fairly easily, if anyone's interested... (a) This problem is really not worth the trouble. (b) I dislike portability approaches that try to enumerate supported cases, rather than being general in the first place. Especially when we can be pretty certain that this area is so unstandardized that *no* toolchain you haven't specifically coded a case for will work. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] libpq's pollution of application namespace
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: (I'm already desperately unhappy about the thin representation of non-GNU toolchains in the build farm...) Me too. If you provide a list of the most important platforms/toolsets missing I will see if I can talk some people into donating resources. Well, one way to attack it is to look at the current supported-platforms list and try to get buildfarm representation for everything not covered already. http://developer.postgresql.org/docs/postgres/supported-platforms.html I don't think we need more buildfarms running more random distros of Linux ;-) --- unless they are running non-gcc compilers. People should be encouraged to test with non-gcc compilers if they have any available. We seem to be short on buildfarm representation for AIX, HPUX, Solaris (particularly older variants), Tru64; it'd be nice to cover all the hardware platforms each of these runs on. For that matter, we're a bit thin on the unusual-hardware ports of the *BSDen. 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] PostgreSQL roadmap for 8.2 and beyond.
Dave Cramer wrote: On the other hand given that all of the serial increments are stored in the session is it possible to get the results of the last insert on the session ? If we can avoid the extra query so much the better, but either way is better than what we have ? Would that not be the new lastval() function ? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
On Mon, Oct 17, 2005 at 01:32:22PM -0400, Dave Cramer wrote: The JDBC problem at hand is there is a method which allows one to retrieve the autogenerated keys from an insert. I can understand Tom's argument here. It should be possible for the driver to build a query from the meta data. On the other hand given that all of the serial increments are stored in the session is it possible to get the results of the last insert on the session ? If we can avoid the extra query so much the better, but either way is better than what we have ? ISTM them that the RETURNING patch as given solves your problem nicely. You don't even have to know the name of the sequence, just the name of the primary key column. When you see an INSERT append RETURNING colname and you have your answer. 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. pgp2xWNoxNeKW.pgp Description: PGP signature
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
On 17-Oct-05, at 1:43 PM, Martijn van Oosterhout wrote: On Mon, Oct 17, 2005 at 01:32:22PM -0400, Dave Cramer wrote: The JDBC problem at hand is there is a method which allows one to retrieve the autogenerated keys from an insert. I can understand Tom's argument here. It should be possible for the driver to build a query from the meta data. On the other hand given that all of the serial increments are stored in the session is it possible to get the results of the last insert on the session ? If we can avoid the extra query so much the better, but either way is better than what we have ? ISTM them that the RETURNING patch as given solves your problem nicely. You don't even have to know the name of the sequence, just the name of the primary key column. When you see an INSERT append RETURNING colname and you have your answer. Well, the driver still needs to know ahead of time which columns are going to be autogenerated. Dave 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. ---(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] libpq's pollution of application namespace
On Mon, Oct 17, 2005 at 01:32:47PM -0400, Tom Lane wrote: (a) This problem is really not worth the trouble. (b) I dislike portability approaches that try to enumerate supported cases, rather than being general in the first place. Especially when we can be pretty certain that this area is so unstandardized that *no* toolchain you haven't specifically coded a case for will work. Well, cleaning up your exported namespace is recommended as it also affects the loading time of applications. I'm just wondering given that libpq can be pulled into any unsuspecting application via PAM (libpam-pgsql) or NSS (libnss-pgsql1), we should be at least trying to cut down the exported symbols. Changing the names to something less likely to conflict is good. I just think it may be worthwhile to solve it for the common platform (gcc) and worry about the others later (if ever). BTW, I think you missed: promote_v4_to_v6_addr promote_v4_to_v6_mask -- 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. pgpTsyDDBip2K.pgp Description: PGP signature
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
It would be useful if you could toggle SQL statement logging without restarting PostgreSQL and additionally if you could turn on selective SQL logging. Additionally, it would be great if you could log SQL statements to a separate file from the main log and in such a way that the statements are seperated in an easily parsed form. The runtime toggling of statement logging would also be most useful if you could toggle it outside of a particular session. This is where the selective SQL logging would be useful I think. Some selectable options that could be useful: - by database - by user - by IP address My current understanding, is that statements can be currently selectively logged by: - whether they cause an error (log_min_error_statement) - whether they exceed a minimum amount of time (log_min_duration_statement) - ddl and/or data modifications (log_statement) I have no idea on how feasible this is. If it is feasible and nobody else is interested in doing it but it would be accepted if somebody would write it, I would be willing to start investigating how to do it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of karen hill Sent: Friday, October 14, 2005 9:57 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] PostgreSQL roadmap for 8.2 and beyond. Autovacuum is getting put into the 8.1 release which is awesome. A lot of us are wondering now that PostgreSQL has all the features that many of us need, what are the features being planned for future releases? What do you see for 8.2 and beyond? What type of features are you devs planning for 9.0? It would be good if you could put up a place on your site so we mortals can drool over up-coming postgresql features. __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Patrick Bakker [EMAIL PROTECTED] writes: It would be useful if you could toggle SQL statement logging without restarting PostgreSQL and additionally if you could turn on selective SQL logging. Additionally, it would be great if you could log SQL statements to a separate file from the main log and in such a way that the statements are seperated in an easily parsed form. The runtime toggling of statement logging would also be most useful if you could toggle it outside of a particular session. This is where the selective SQL logging would be useful I think. Some selectable options that could be useful: - by database - by user - by IP address You do realize that the majority of that can be done today? You need to study the available mechanism for setting GUC parameters a little more. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
Dave Cramer [EMAIL PROTECTED] writes: On 17-Oct-05, at 1:43 PM, Martijn van Oosterhout wrote: ISTM them that the RETURNING patch as given solves your problem nicely. Well, the driver still needs to know ahead of time which columns are going to be autogenerated. Possibly you could define that as all columns that are not specified in the INSERT command and have column default expressions. The first is knowable with very little parsing of the command, and the second can be learned from the catalog metadata that you're probably reading anyway. If the JDBC spec has a more specific definition of autogenerated than has a default, then what is it? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] More problems with the win32 installer for 8.1 beta3
-Original Message- From: [EMAIL PROTECTED] on behalf of Tony Caduto Sent: Mon 10/17/2005 5:43 PM Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] More problems with the win32 installer for 8.1 beta3 Is there anyway to get a libpq that does not depend on libintl-2.dll and libiconv-2.dll? (short me spinning my wheels for hours trying to figure out C code). Recompile it without NLS support - but please don't distribute such a build as you may end up with us telling people not to use your product if you inadvertantly end up breaking psqlODBC or something similar. I really think you have something else wrong however - there is no reason the gettext, ssl or kerberos libs shouldn't be found if they and libpq are in the same directory as your executable. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bug 1473, pthread python on FreeBSD
On Thu, Oct 13, 2005 at 04:40:39PM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: http://archives.postgresql.org/pgsql-bugs/2005-02/msg00135.php I think it may have been a bit early to disable pthread python support (http://archives.postgresql.org/pgsql-hackers/2005-09/msg01136.php), as Python was working fine on buildfarm member platypus. Maybe it's only an issue with 4.x machines? I have reenabled threaded python builds on FreeBSD5 (now disabled only for FreeBSD4 and OpenBSD). Great, --with-python back in config for platypus. -- 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
Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches
On Wed, Oct 12, 2005 at 03:07:23PM -0400, Emil Briggs wrote: where the number of padding locks is determined by how many lock structures fit within a 128 byte cache line. This isn't exactly elegant coding, but it provides a useful improvement on an 8-way SMP box when run on 8.0 base. OK, lets be brutal: this looks pretty darn stupid. But it does follow the CPU optimization handbook advice and I did see a noticeable improvement in performance and a reduction in context switching. I'm not in a position to try this again now on 8.1beta, but I'd welcome a performance test result from anybody that is. I'll supply a patch against 8.1beta for anyone wanting to test this. I don't have an 8 way available right now but I can run tests on a 4 way Opteron if that would be helpful. Likewise I can test on a 4 way opteron running open solaris. -- 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] slower merge join on sorted data chosen over nested loop
On Mon, Oct 10, 2005 at 09:10:38PM -0400, Tom Lane wrote: I recall thinking about changing the formula to more accurately count the number of pages touched; but I desisted when I realized that it would drastically increase the cost estimates for index searches, and that's surely the wrong direction to be going in. We really can't do that until we have some usable infrastructure to allow estimating the probability that those pages are already in cache. In the meantime, the tweaks under discussion here amount to assuming that the metapage and all upper pages are always in cache. The current cost estimate to fetch a single tuple via indexscan is basically random_page_cost + 2, plus some near-negligible cpu costs. Not counting the metapage would take that down to random_page_cost + 1. This would definitely move the goalposts, particularly for people who run with smaller-than-default random_page_cost, but I'm not sure if it's enough to solve the problem. Also, all this is really just a sideshow; I think the main problem for join estimation is that because we cost an inner-indexscan nestloop as taking N times the cost of one execution of the inner scan, we fail to account for cacheing effects in the table itself as well as the index. That would cut into the random_page_cost part of the cost estimate as well as the index cost. For all the reasons I've cited, it's pretty hard to justify reducing the estimate for an indexscan standing on its own --- but in the context of a nestloop join, it's easier to make a case. One thing I noticed the last time I looked at all of this was that index correlation seems to be severely mis-weighted in scan calculations. http://archives.postgresql.org/pgsql-performance/2005-04/msg00669.php has more info on this. I suspect that until that issue is addressed other changes to the cost estimates won't make any useful difference. -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL roadmap for 8.2 and beyond.
/me has started working on hierarchical query support that I'd like to get into 8.2.On 10/17/05, Tom Lane [EMAIL PROTECTED] wrote:Dave Cramer [EMAIL PROTECTED] writes: On 17-Oct-05, at 1:43 PM, Martijn van Oosterhout wrote: ISTM them that the RETURNING patch as given solves your problem nicely. Well, the driver still needs to know ahead of time which columns are going to be autogenerated.Possibly you could define that as all columns that are not specified inthe INSERT command and have column default expressions.The first isknowable with very little parsing of the command, and the second can be learned from the catalog metadata that you're probably reading anyway.If the JDBC spec has a more specific definition of autogenerated thanhas a default, then what is it?regards, tom lane---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster-- Respectfully,Jonah H. Harris, Database Internals Architect EnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] slower merge join on sorted data chosen over
On Tue, Oct 11, 2005 at 10:58:58AM +0100, Simon Riggs wrote: On Mon, 2005-10-10 at 15:14 -0500, Kevin Grittner wrote: We are looking at doing much more with PostgreSQL over the next two years, and it seems likely that this issue will come up again where it is more of a problem. It sounded like there was some agreement on HOW this was to be fixed, yet I don't see any mention of doing it in the TODO list. Is there any sort of estimate for how much programming work would be involved? The main work here is actually performance testing, not programming. The cost model is built around an understanding of the timings and costs involved in the execution. Once we have timings to cover a sufficiently large range of cases, we can derive the cost model. Once derived, we can program it. Discussing improvements to the cost model without test results is never likely to convince people. Everybody knows the cost models can be improved, the only question is in what cases? and in what ways? So deriving the cost model needs lots of trustworthy test results that can be assessed and discussed, so we know how to improve things. [...and I don't mean 5 minutes with pg_bench...] Detailed analysis such as that is time consuming and also needs to be done in a sufficiently reproducible manner that we can rely on it. Your help would be greatly appreciated in that area. You and your team clearly have an eye for the fine detail of these issues. ...IIRC there is a TODO item relating to that. Perhaps we should put a more general call out on the TODO list towards detailed, complete, accurate and reproducible performance test results? I touched on some of this in http://archives.postgresql.org/pgsql-performance/2005-05/msg00336.php: In terms of a testing system, here's what I'm thinking of. For each cost estimate, there will be a number of input variables we want to vary, and then check to see how changes in them effect run time. Using index scan as a simple example, 1st order variables will likely be index and table size (especially in relation to cache size), and correlation. So, we need some kind of a test harness that can vary these variables (prefferably one at a time), and run a test case after each change. It would then need to store the timing info, possibly along with other information (such as explain output). If I'm the one to write this it'll end up in perl, since that's the only language I know that would be able to accomplish this. DBT seems to be a reasonable test database to do this testing with, especially since it would provide a ready means to provide external load. Of course, this work can be done by hand, but it's a very slow, tedeous process. It's also rather error-prone. There's been some discussion on the osdl-dbt mailing lists about providing a front-end that would allow for scheduling tests and storing results in a database where you could data-mine more easily than you currently can (currently everything's just stored as files on a disk somewhere). ISTM that having that would make this kind of testing much easier to do. But I've also been working with dbt quite a bit recently, so it's my hammer that makes everything performance related look like a nail... -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] slower merge join on sorted data chosen over
On Mon, 2005-10-17 at 14:55 -0500, Jim C. Nasby wrote: On Tue, Oct 11, 2005 at 10:58:58AM +0100, Simon Riggs wrote: On Mon, 2005-10-10 at 15:14 -0500, Kevin Grittner wrote: We are looking at doing much more with PostgreSQL over the next two years, and it seems likely that this issue will come up again where it is more of a problem. It sounded like there was some agreement on HOW this was to be fixed, yet I don't see any mention of doing it in the TODO list. Is there any sort of estimate for how much programming work would be involved? The main work here is actually performance testing, not programming. The cost model is built around an understanding of the timings and costs involved in the execution. Once we have timings to cover a sufficiently large range of cases, we can derive the cost model. Once derived, we can program it. Discussing improvements to the cost model without test results is never likely to convince people. Everybody knows the cost models can be improved, the only question is in what cases? and in what ways? So deriving the cost model needs lots of trustworthy test results that can be assessed and discussed, so we know how to improve things. [...and I don't mean 5 minutes with pg_bench...] ... DBT seems to be a reasonable test database I was discussing finding the cost equations to use within the optimizer based upon a series of exploratory tests using varying data. That is different to using the same database with varying parameters. Both sound interesting, but it is the former that, IMHO, would be the more important. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] A costing analysis tool
On Fri, Oct 14, 2005 at 05:14:43PM +0200, Martijn van Oosterhout wrote: On Thu, Oct 13, 2005 at 05:39:32PM -0500, Kevin Grittner wrote: That said, there's certainly overlap between your effort and what I'm going to be developing. Do you have anything from your work which might save me some time? Not really. I got stuck in the query design phase. I didn't even generate any tables :( There's a chance that I may be able to talk my client into putting a web app guy on this for a few days to make it pretty. You misunderstand. I don't think a pretty website is seriously needed. Just a consistant output format with the results in a tarball complete with information about the system itself that people can upload. Then anyone can download a few and combine them as they want. Just an ftp server, maybe gforge or whatever. Well, it would be best if inbound results ended up in a database that people could query against. That would make it very easy for people to look for results they're interested in. Of course that doesn't preclude making the raw results available too. BTW, this is something I'm also very interested in. -- 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 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] A costing analysis tool
On Sun, Oct 16, 2005 at 02:23:41PM -0700, Josh Berkus wrote: Greg, Or something like that. It might require breaking random_page_cost into two or three different parameters that would normally have the same cost but aren't handled the same, like random_heap_cost, random_leaf_cost, and random_nonleaf_cost. Gods forbid. People don't know how to use random_page_cost as it is; how are they going to handle having 5 different parameters? Well, until we try it we won't know if there's actually need for this or not... -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] A costing analysis tool
On Fri, Oct 14, 2005 at 02:37:37PM -0400, Tom Lane wrote: Kevin Grittner [EMAIL PROTECTED] writes: I propose capturing only three values from the output of explain analyze, and saving it with many columns of context information. You really have to capture the rowcounts (est and actual) too. Otherwise you can't tell if it's a costing problem or a statistics problem. More generally, I think that depending entirely on EXPLAIN ANALYZE numbers is a bad idea, because the overhead of EXPLAIN ANALYZE is both significant and variable depending on the plan structure. The numbers that I think we must capture are the top-level EXPLAIN cost and the actual runtime of the query (*without* EXPLAIN). Those are the things we would like to get to track closely. EXPLAIN ANALYZE is incredibly valuable as context for such numbers, but it's not the thing we actually wish to optimize. The problem with that is that we lose all data on per-node costs versus what the planner thought should happen. ISTM it would be better to run all 3 scenarios: explain, explain analyze, and select count(*). As for the caching issue that raises, I don't buy into the theory that all testing should be done with nothing in the cache, because it's entirely unrealistic in most cases. I think what makes a lot more sense is to do two runs, clearing the cache and swapping the order of analyze and count(*) between the two. That would give us a complete set of data: not only would we know how things break down at a node-by-note level, we'd also know how caching affected things. Given some clever data-mining, one could probably even produce cost estimates for the overhead of explain analyze which could be factored into further analysis. Of course the one downside is this doubles the amount of time it takes for a test to run... -- 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
Re: [HACKERS] A costing analysis tool
On Sat, Oct 15, 2005 at 04:04:52PM +0200, Martijn van Oosterhout wrote: On Fri, Oct 14, 2005 at 03:34:43PM -0500, Kevin Grittner wrote: Of course, if running with EXPLAIN ANALYZE significantly distorts the run time, the whole effort is doomed at the outset. Can you quantify the distortion you mention? Do you know To do the calculations for EXPLAIN ANALYZE, PostgreSQL will call gettimeofday() once (or possibly twice) for every iteration of every node in the execution plan. This is usually (but not always) a kernel call so if there are a lot of rows being processed compared with the amount of other calculations happening, the results are distorted. This is unfortunate because EXPLAIN ANALYZE is an immensly useful tool, as far as it goes. I've pondered if some kind of userspace timing mechanism could be introduced (possibly using builtin CPU cycle counters) to reduce the cost. It does, however, remain a cost. Given that you can see how many times gettimeday() was called, you may be able to correct the error. I havn't tried that though. DTrace (http://www.opensolaris.org/os/community/dtrace/) is another possibility, althought AFAIK it's only available on OpenSolaris right now. But I've also heard that BSD guys are pretty stoked about it, so it might become a standard across multiple OSes. -- 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 6: explain analyze is your friend
Re: [HACKERS] A costing analysis tool
On Fri, Oct 14, 2005 at 03:34:43PM -0500, Kevin Grittner wrote:
of the two times as a reliability factor. Unfortunately, that
means doubling the number of cache flushes, which is likely
to be the most time-consuming part of running the tests. On
the bright side, we would capture the top level runtimes you
want.
Actually, if you shut down the database and run this bit of code with a
high enough number you should have a nicely cleaned cache.
int main(int argc, char *argv[]) {
if (!calloc(atoi(argv[1]), 1024*1024)) { printf(Error allocating
memory.\n); }
}
Running that on a dual Opteron (842's, I think) gives:
[EMAIL PROTECTED]:35]~:10time ./a.out 3300
3.142u 8.940s 0:40.62 29.7% 5+4302498k 0+0io 2pf+0w
That was on http://stats.distributed.net and resulted in about 100MB
being paged to disk. With 3000 it only took 20 seconds, but might not
have cleared 100% of memory.
--
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 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Do all accesses to shared buffer headers through
On Wed, Oct 12, 2005 at 10:46:14PM -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Does any of this need to be backpatched? No --- we didn't have any per-buffer spinlocks before 8.1. It's possible that at some point we'll need to start thinking about applying volatile-pointer coding rules to data structures protected by LWLocks. This could only become an issue if the compiler (a) inlines LWLockAcquire/Release, and (b) tries to rearrange loads and stores around the LWLock code. I would like to think that the latter is impossible even with inlining, principally because the compiler can't ignore the kernel calls that may occur within the LWLock routines; those should be treated as external function calls and hence sequence points, no matter how aggressive the compiler gets. But we'll see. Sorry if I'm just confused here, but don't LWLocks protect data structures susceptible to corruption? And if that's the case don't we need to be sure that the compiler can't optimize around them? Or will this only result in things like buffers not getting un-pinned? -- 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of
On Thu, 2005-10-13 at 21:20 -0600, Robert Creager wrote: When grilled further on (Thu, 13 Oct 2005 22:44:54 -0400), Tom Lane [EMAIL PROTECTED] confessed: Robert Creager [EMAIL PROTECTED] writes: I've been having this problem since trying to upgrade from 7.4.1 to 8.03, and now 8.1. Can you put together a test case that other people could use to reproduce it? I can try. The data size isn't big, but the scripts that run against it are, and are dependent on our development enviornment. What I'll have to do is pull out the db portion of the app and see if I can simplify it - might work. The app is a test system that runs against our big storage libraries. Is there anything I might be able to do (without the test case) that would help figure out what's happening? Please try this patch and see if it reduces the CS storm: http://archives.postgresql.org/pgsql-patches/2005-10/msg00091.php Do you have access to another similar machine to do comparative testing? Do you have access to another machine with different CPU arch? It would be good to firmly isolate this to a CPU architecture interaction issue. Are the Xeons particularly old models? How new is the server? Thanks, Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] re:tsearch2 problem
Thanks VERY VERY much for Teodor Sigaev help,and my next problem is which is more suitable for index chinese words, TSearch2 OR Lucene? My matessaid to me that Lucene is more effifent because of its way of "Inverse Order OF Idexing",and "The GiST Tree is well for larting lan but doesn't for chinese lan(also ineffifent for Japanese lan)"。 I only know that GiST is also "Inverse Order OF Idexing",but of course it is a tree NOT as lucene does What is your suggestion?Which is More suit for Chinese(Janpanese) ,TSearch2 OR Lucene?(Besides other components) 想要一个快n倍的免费邮箱吗？ 126 专 业 电 子 邮 局 ―― 全 球 领 先 的 中 文 邮 箱 服 商 带 你 进 入 极 速 之 旅
