Re: [GENERAL] The first dedicated PostgreSQL forum
On 11/15/2010 5:53 PM, Lee Hachadoorian wrote: If anyone's interested, I've started accessing the postgres list through gmane.org (along with several other mailing lists I subscribe to). It's gives you the choice of reading the list as a threaded archive, a blog, or through an NNTP newsreader or an RSS feed. Everyone chooses their preferred interface, the community is not fractured by interface preference. Honestly those options suck. The mailing lists etc are fine, but they are kind of old school, people coming from other databases expect a web based forum plain and simple. To attract more users the forums are a GREAT idea. Stop this ridiculous complaining about interface fracture etc . I can tell you for SURE that many people who are not OLD SCHOOL hate mailing lists, A web based forum gives everyday users more of a chance of interacting with the community. It would be a good idea to stop the bickering and just implement the forums. Sorry if people don't like my honest answer :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0 pg_database datconfig ?
Hi, Just looking around 9.0 and noticed pg_database is missing the datconfig field which stored default session info for the database. Where is this stored now? I looked in the release notes, but no mention of datconfig. Thanks, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visual DATA editor for PostgreSQL?
Dmitry Koterov wrote: Hello. Is there a GUI utility to visually edit Postgres DATA (not a database schema!), which allows at least: - insert/update rows using screen windowed forms (possibly ugly auto-generated forms, but - still forms) - insert foreign key references by selecting them from a list (not by typing the keys manually) - work with multi-line text fields (textarea) for TEXT columns There is a lot of DB development tools around (e.g. I like EMS PostgreSQL Manager). But a developer tool is handy for a database STRUCTURE editing, and when we need to modify its DATA quickly, these tools are not too useful. Lightning Admin has a form view when editing data and shows text fields as multi line in the form view and in the data grid itself. Later, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY questions
Bill Todd wrote: Thanks for the suggestion but pgloader appears to be a Linux only solution and my environment is Windows. The other problem is that there is no documentation that I could find (other than a PDF made from slides). Bill Bill, pgloader is a Python app, It should work on win32 as well. Later, Tony Caduto AM Software Design htpp://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGSQL or other DB?
durumdara wrote: Another important thing that I don't understand (what as I saw) that the PostGreSQL is store the databases in one, unseparatable file set, in a directory named data. In another databases, like DBISAM, FireBird, MySQL, the databases are separated to another directories/files. This one datadir is seems to be not too good for us. We used DBISAM in our clients, and many times when we got some filesystem error, we can simply recover the tables - from the files. When we want to backup or restore one database, we can do it in two way: a.) archive all files b.) make sql dump from database. Postgresql uses a database cluster. In the data directory each database goes into a directory identified by the database OID, and in this directory the database resides in 2 gb chunks(as far as I remember). You can easily backup the cluster by using the file system, you just have to make sure you stop the postmaster and then you can backup the entire data dir or individual database dir. You have to restore this to the same version of PG though. it's the same kind of deal with Firebird, you can't backup a running database with the file system and even Firebird must be stopped in order to do a file system copy of the database file. Firebird also stores all it's transaction data in the same file, so you end up with HUGE files unless you do a backup and restore to shrink them down. You don't have this problem with PostgreSQL as it stores that info in the WAL. PostgreSQL will give the the best solution for your project hands down, just give it a chance and don't worry about what you did with Firebird or MySQL. I have never had a PostgreSQL database or dump file become corrupt, but on Firebird it happens fairly often and it's part of the reason why I eventually dumped it and moved to PostgreSQL. Hope that helps you out some. Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGSQL or other DB?
durumdara wrote: - quick (re)connect - because mod_python basically not store the database connections persistently You should consider using a Python connection pool with something like Pylons (http://pylonshq.com), I use dbutils: http://www.webwareforpython.org/DBUtils/Docs/UsersGuide.html You can scale PostgreSQL and Python web apps really well with that as long as you are not using CGI. I use Pylons combined with mod_wsgi, but you can use it with mod_python as well. Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] System table documentation
Bill Todd wrote: Where can I find documentation for the system tables? I have not found anything in the 8.3.1 documentation. Thanks. Bill Hi Bill, Good to see a Delphi guy here :-) http://www.postgresql.org/docs/8.3/interactive/catalogs.html Later, Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for PostgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore question (-T and -t)
Hi, does anyone know if you can do multiple -T or -t (restore named trigger, restore name table) switches? In the docs for pg_restore it does not specify if it will accept more than one, but in the pg_dump docs the -n and -t switches allow multiples. Thanks, tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] compiling libpq.dll with Borland C++, is it possible?
Hi, I am trying to compile my own copy of libpq.dll using bcc32.exe, the docs say it is possible, but I get a error when it tries to compile dirent.c Has anyone been able to do this? C:\postgresql-8.3.5\src\interfaces\libpqmake -N -DCFG=Release /f bcc32.mak MAKE Version 5.3 Copyright (c) 1987, 2008 CodeGear Building the Win32 DLL and Static Library... Configuration Release bcc32.exe @MAKE0014.@@@ Borland C++ 5.5.1 for Win32 Copyright (c) 1993, 2000 Borland ..\..\port\dirent.c: Error E2451 ..\..\port\dirent.c 35: Undefined symbol 'INVALID_FILE_ATTRIBUTES' in function opendir Error E2451 ..\..\port\dirent.c 52: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 53: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 59: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 62: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 64: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 65: Undefined symbol 'handle' in function opendir Error E2451 ..\..\port\dirent.c 66: Undefined symbol 'ret' in function opendir Error E2451 ..\..\port\dirent.c 67: Undefined symbol 'ret' in function opendir Error E2451 ..\..\port\dirent.c 77: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'dirname' in function readdir Error E2451 ..\..\port\dirent.c 80: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 88: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 100: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 103: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 109: Undefined symbol 'handle' in function closedir Error E2451 ..\..\port\dirent.c 110: Undefined symbol 'handle' in function closedir Error E2451 ..\..\port\dirent.c 111: Undefined symbol 'dirname' in function closedir Error E2228 ..\..\port\dirent.c 111: Too many error or warning messages in function closedir *** 26 errors in Compile *** ** error 1 ** deleting .\Release\dirent.obj -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help
Hi, Trying to get a libpq.dll that only has SSL deps using these instructions: http://www.postgresql.org/docs/8.3/interactive/install-win32-libpq.html It compiles a bit then dies at: C:\postgresql-8.3.5\src\interfaces\libpqnmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 9.00.30729.01 Copyright (C) Microsoft Corporation. All rights reserved. Building the Win32 static library... Using default OpenSSL Include directory: C:\OpenSSL\include Using default OpenSSL Library directory: C:\OpenSSL\lib\VC Using default Kerberos Include directory: C:\kfw-2.6.5\inc Using default Kerberos Library directory: C:\kfw-2.6.5\lib\i386 link.exe -lib @C:\DOCUME~1\20659\LOCALS~1\Temp\nm1B.tmp .\Release\win32.obj : fatal error LNK1107: invalid or corrupt file: cannot read at 0xB39 NMAKE : fatal error U1077: 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\BIN\link.exe' : return code '0x453' Stop. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help
Dann Corbit wrote: How much disk space do you have? I guess that you are running out of space. During the link, watch the available disk space. Hi Dan, Thanks for the reply :-) I actually got it working.Now the only problem is it still has a dependency for MSVCR90.DLL Does anyone know if this can be done with mingw so I can eliminate the MSVCR90.DLL dependency? I have it installed but that is even more foreign to me than VS :-) (mostly use borland CodeGear tools) Thanks, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help
Dann Corbit wrote: Change from /MD to /MT and it will not use the runtime DLL. Look under: Properties - Configuration Properties - C/C++ - Code Generation - Runtime Library Hi Dann, I changed that in the win32.mak file and now it gives this error: link.exe @C:\DOCUME~1\20659\LOCALS~1\Temp\nmA6.tmp Creating library .\Release\libpqdll.lib and object .\Release\libpqdll.exp LIBCMT.lib(dosmap.obj) : error LNK2005: __dosmaperr already defined in libpq.lib(win32error.obj) .\Release\libpq.dll : fatal error LNK1169: one or more multiply defined symbols found NMAKE : fatal error U1077: 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\BIN\link.exe' : return code '0x491' Stop. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help
Hiroshi Saito wrote: Hi. It is very strange I checked that it was ready in VC9 again. http://winpg.jp/~saito/pg_work/WIN32_BUILD_INF/LOG_LIBPQ_WIN32MAK.txt Furthermore, even psql was checked. http://winpg.jp/~saito/pg_work/WIN32_BUILD_INF/psql_win32_pg83.mak Please move psql_win32_pg83 win32.mak on src\bin\psql. http://winpg.jp/~saito/pg_work/WIN32_BUILD_INF/LOG_PSQL_WIN32MAK.txt However, It will be necessary what is bad and to investigate in your environment. I think 1. One proposal nmake -f win32.mak clean Regards, Hiroshi Saito - Hi, I will give it another try tomorrow. It could be something related to the fact that I am running on XP 64bit. I will try and compile it on a VM with 32bit XP, and also try to build it with MinGW. I did try the clean and it didn't make any difference. It compiles fine with the /MD option, I even got it to compile against the lastest openSSL, though I did have to comment out one little line. Thanks, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Libpq.dll lite is here :-)
Finally got everything to compile with MinGW. Thanks to everyone that helped me out getting it going. Here are the download links: Libpq.dll 8.3.5.8322 with SSL No Zlib (includes the latest openssl dlls 0.9.8i also built with MinGW) http://www.milwaukeesoft.com/libpq_mingw_ssl.zip If you need a really lite deployment there is also: http://www.milwaukeesoft.com/libpq_mingw_noSSL.zip (just libpq.dll 8.3.5.8322 with no other dependencies) These also have no special MS VC++ runtime requirements, just the standard MSVCRT.DLL which means these all work perfectly on WINE. The current libpq.dll won't work on wine because of the VC++ runtime. I would like to maintain these for future PostgreSQL releases and if anyone is interested in helping or has a suggestion on where to host them permanently please let me know. Later, Tony Caduto AM Software Design http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3 libpq.dll not working on some versions of windows
Glyn Astill wrote: Pretty sure I've used most 8.3.x versions here on both sp2 and 3. How have you chacked you have all the dependencies? (I like depends.exe) http://www.dependencywalker.com/ hmm, the problem seems to be that MSVCR71.DLL has a problem with some versions of Kernel32.DLL, dependency walker reports this: Error: At least one module has an unresolved import due to a missing export function in an implicitly dependent module. I have all the required files in the same dir as my exe I did this test on the latest version of wine (libpq.dll always worked in wine before), and I was getting the exact same behavior on the PCs at work. WINE is not a good test for this I know, but all I have at the moment. I will have to try this on the PCs at work(on Monday) that are having the issue. I know one of them is a really old XP (6 years) that was just recently updated to SP3. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3 libpq.dll not working on some versions of windows
Hi, We have been running into issues where the 8.3.x versions of libpq.dll will not load in certain versions of windows and WINE(does not load at all on wine). It seems to be hit and miss on Windows XP, mostly seems to affect SP3 and some SP2 installs of XP. I have only been able to get around this by installing a much older version of libpq.dll. And I did have all the dependencies installed along with the DLL, it just plain refuses to load. I also check that there were no rouge copies of the files in system32. Thanks, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to know the password for the user 'postgres'
Shashank Sahni wrote: when i posted the question on dspace mailing list..one of the guy replied me with this solution.. createuser -h localhost -U postgres -d -A -P dspace but he said that to execute the above command i must know the password for the user postgres.. Edit the pg_hba.conf file and add a entry for the PC you are doing your admin from and set it to Trust. When set to trust you won't need a password, then use the admin tool of your choice to change the postgres password to whatever you want. Afterwards remember to set it back to MD5 or whatever it was prior to you making the change. Later, Tony Caduto AM Software Design http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT INTO returns incorrect values
Bill, Did you try it like this: parent_id = 0 category_name = '' select category, parent_category_id from note.category where category_id = 477 into category_name, parent_id; raise notice 'curr cat, name, parent id: % % ', category_name, parent_id; I have found in the past that it's a good idea to initialize your vars before you use them in PL/pgsql. Also as a FYI, you don't need to upper case all your text in a function (I know you have to do that in Firebird), just use standard case with normal capitalization because PostgreSQL will lowercase everything you send to the server that is not in quotes. It's a lot easier to read without the uppercase. Later, Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
David Fetter wrote: What they want to have is a huge entity they can blame when everything goes wrong. They're not interested in the actual response times or even in the much more important time-to-fix because once they've blamed Oracle, they know the responsibility is no longer on their shoulders. That is only a perceived sense of risk avoidance, if you read the EULA etc that ship with Oracle, MS SQL server etc, they are not responsible for anything that may happen to your data. Sure management could blame them, but that's about it. They would get the same amount of satisfaction from blaming the FOSS community. No matter what management says any blame rests squarely on their shoulders and the people they have entrusted to create their corp projects/products when something goes wrong. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RAISE NOTICE format in pgAdmin
Bill Todd wrote: If I have a series of RAISE NOTICE 'xxx' statements in a plpgsql function and I call the function from pgAdmin the notice messages are concatenated on a single line on the Messages tab. Is there any way to get each message to appear on a separate line? Is there a better way than using RAISE NOTICE to debug functions? Bill Bill, Make sure you are on at least version 8.2 (8.3 is preferred) and use a plpgsql debugger. Later versions of Pgadmin have one built in and a stand alone version is available from: http://www.amsoftwaredesign.com/debugger_client_announce (built with Delphi) If you are using the win32 version there is a option at the end of the installer script to install the debugger part. On 8.2 or 8.3 you will need to install it yourself. Please see: http://pgfoundry.org/projects/edb-debugger/ Hope that helps. Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Number or parameters for functions - limited to 32 ?
Just a FYI, you can get around the 32 param limit in older versions of postgresql by passing the function a varchar/text array, then use the built in array functions to pull the params from the passed array. Something like this(not tested code, just a sample): CREATE or REPLACE FUNCTION test_func(varchar) RETURNS void AS $BODY$ DECLARE IN_ARRAY text[] ; ACCOUNTNUMBER_INvarchar; BEGIN IN_ARRAY = string_to_array($1,'~^~'); --use a unique delimiter ACCOUNTNUMBER_IN = IN_ARRAY[1]; return; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; Later, Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] edb-debugger, debugging pl/pgsql
josep porres wrote: Hi, I don't know if here is the right place to post this, but anyway ... Does anybody know if there is any binary dist for win32 edb-debugger for pgsql8.3? If not, do you know which compiler I have to use? I don't need to install anything on the client side where is my pgadmin, right? You can use the Lightning Admin win32 debugger, very stable and FREE. From my testing it's more stable on win32 than the others, but that will probably be debated since I am biased. Anyway I encourage you to check it out at: http://www.amsoftwaredesign.com/debugger_client_announce Tony Caduto AM Software Design -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] edb-debugger, debugging pl/pgsql
josep porres wrote: 2008/4/7, Dave Page [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: On Mon, Apr 7, 2008 at 10:21 AM, josep porres [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: well, when you asked me about where I downloaded it, I downloaded it from I installed it again. It seems I can debug with no problems till now. However, when I begin to debug and the function parameters window appears, if I click cancel it freezes. Josep, The stand alone Lightning Debugger does not have this issue and if it does crash or hang (not likely) it won't take down whatever admin tool you are using. Oh, and it's FREE. http://www.amsoftwaredesign.com/debugger_client_announce Check it out works great on win32 and built with a native compiler with a high performance memory manager. Tony Caduto AM Software http://www.amsoftwaredesign.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] edb-debugger, debugging pl/pgsql
Magnus Hagander wrote: You know, kinda like PostgreSQL vs Oracle Express ;) Well, not quite the same since LA Debugger Client is not crippled in some way Like Oracle or MS SQL Express :-) It's just plain old freeware. Later, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting weird pg_tblspc error, has anyone seen this before?
I have a client who is running this query(just brings back info about the databases on the server): select pgd.datname as database, pdesc.description, pgr.rolname as owner, pgt.spcname as tablespace, pg_size_pretty(pg_database_size(pgd.oid)) as dbsize, pg_encoding_to_char(encoding) as encoding, pgd.oid from pg_database pgd LEFT JOIN pg_roles pgr on pgr.oid = pgd.datdba LEFT JOIN pg_description pdesc on pdesc.objoid = pgd.oid LEFT JOIN pg_tablespace pgt on pgd.dattablespace = pgt.oid WHERE pgd.datname not like 'template%' ORDER BY pgd.datname; The exact error is(as reported from PG Admin III): ERROR: could not open tablespace directory pg_tblspc: No such file or directory SQL state: 58P01 The user is running on a Mac PC that is booting Windows XP via Bootcamp. The user also claims that the error only occurs when running the query locally i.e. localhost, but when he runs the query from a different PC it works and he gets a result set back. The error occurs regardless of the client as well, same error is reported via PG Admin III, PSQL, and LA. Other queries such as select * from pg_tablespace work fine so I am at a loss as why this query gives a pg_tblspc error. He is running 8.2.6 win32 version on a Mac core 2 system that dual boots to XP. I have been trying for days to reproduce the error on my PCs running the client and server on the same PC with no luck, I have tried the same version of PGSQL he is running as well as 8.3 with no luck. I am missing some info from the client like what user he is running as and what his pg_hba.conf looks like. If anyone has seen this before please let me know. Thanks, Tony Caduto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting weird pg_tblspc error, has anyone seen this before?
Tom Lane wrote: This must be coming from calculate_database_size(). Is $PGDATA/pg_tblspc actually missing? If the guy has no custom tablespaces, I can believe that nothing except pg_database_size() or pg_tablespace_size() would try to touch that subdirectory, so he might not otherwise notice that it'd gone missing. Thanks Tom, I will follow up with the client and have him check if that directory is missing. I think he may have used the Postbooks win32 installer to install his server, so it might be that their installer is messed up and not creating the directory properly or messing something up with the environment vars where it can't find the directory. Later, Tony Caduto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting weird pg_tblspc error, has anyone seen this before?
Magnus Hagander wrote: Tony Caduto wrote: I will follow up with the client and have him check if that directory is missing. I think he may have used the Postbooks win32 installer to install his server, so it might be that their installer is messed up and not creating the directory properly or messing something up with the environment vars where it can't find the directory. The pg_tblspc directory is created by initdb, not the installer. And IIRC doesn't touch any environment variables. It does set the data path on the commandline, but if that one is incorrect you shouldn't even get that far. //Magnus Hi, I just used the postbooks installer and it did not create the pg_tblspc directory, so the issue appears to be caused by a messed up Postbooks win32 installer. Check out this screenshot: http://www.milwaukeesoft.com/postbooks_datadir.png They must be deleting it after the initdb, maybe they figured since it was empty they did not need it? Later, Tony -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting weird pg_tblspc error, has anyone seen this before?
Tom Lane wrote: Bizarre. I hope you'll tell them to fix that. Agreed :-) I bet they just took a snapshot of a install dir that had the postmaster stopped and used that in their setup. It probably does not do a initdb during the setup. Why it's missing that directory is a mystery :-) I did report the issue on the PostBooks sourceforge project forum. Later, Tony Caduto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date and time
Alain Roger wrote: Hi, i have a stored procedure (a function) in which i must generate a date/time stamp. for that i use select * from now(); and store the result into a column table. is there a easier way to do that ? i tried to store directly now(); result but without success. Do you mean something like this: CREATE OR REPLACE FUNCTION public.test() RETURNS timestamp AS $BODY$ DECLARE mydate_var timestamp; BEGIN --store the current timestamp in a variable mydate_var = now(); RETURN mydate_var; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Later, Tony Caduto AM Software http://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL and MySQL - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] msvcr80.dll and PostgreSQL 8.3 under Windows XP
Dave Page wrote: You can avoid this by building your own libpq.dll using mingw/msys if you like - that will work just fine with a VC++ built server. Hi Dave, Just some thoughts on the whole libpq.dll thing. It would be really nice from a client distribution view of things to have a libpq.dll that was not so dependency heavy. For the 8.3 release the total amount of files needed to use things such as pg_dump.exe is close to 5mb. maybe a light version of libpq.dll is needed, just the libpq and the open ssl dlls would be ideal, many times all that extra stuff is not needed just to run pg_dump. Didn't there used to be a project on pgfoundry that built the client tools separate from the server build? Just some thoughts, Later, Tony ---(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: [GENERAL] Pains in upgrading to 8.3
paul rivers wrote: Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. However, unlike the blogger you cite, I read the directions before, not after, attempting it. The blogger has a point about pg_dump and restore, it could be much better, for example the backup process could be part of the server core and instead of having a fat client where most of the process is running on the client, a API could be used where the backup is generated on the server and then have options where it could be left on the server or transferred to the clients PC. Using pg_dump remotely is becoming a pain because it's not really backwards compatible with earlier releases, so you end up having to have multiple copies laying around to use on different server versions. While Firebird is mostly inferior, it's backup system is much nicer that PostgreSQL's system. Firebird uses a backup API, so if you backup remotely there is no fat client needed and it eliminates all the dependency issues on the client side. The client access library implements the API and that's it. You of course could hack something similar on PGSQL by using SSH and remotely executing pg_dump on the server, but that does not really help on windows servers where SSH is not a common thing. The backup data is coming back to the client regardless, so why not just return it as a result set? Just my opinion on the matter, no flames please. Thanks, Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Pains in upgrading to 8.3
Magnus Hagander wrote: For the case of upgrading, it wouldn't work. But there are certainly other cases where it would help. Say from your central pgadmin console administering 10 servers from 3 different major release trees :-( It can be done with commandline pg_dump, but it means you have to have three different installs on your management or backup or whatever machine. Those cases would certainly be easier if you could just call a backup API on the server that would feed you the data... (yes, there are ways to do it with ssh tunneling and whatever, but that's yet another external service that has to be set up and configured) I'm not saying it's worth the work and potential downsides, just that there are clear upsides :-) Exactly, I didn't necessarily mean the blogger had a point about upgrades in general, just that pg_dump had room for improvement. Hey maybe a backup API is something for the Google Summer of Code thing, it would be really nice to have, and make general backups much easier from a admin point of view. Later, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQLDirect versus Npgsql
Benjamin Arai wrote: Has anybody had a good experience going from Npgsql to Corelab:PostgreSQLDirect? I am considering migrating because it offers support for COPY but I am wondering about other functionality and performance as well. Any feedback would be greatly appreciated. Benjamin The Corelab product does not support SSL connections. I looked at it, but I needed SSL, so I am sticking with Npgsql. Other than the fact it does not have SSL, it looks very nice and includes a dump component, not sure if that is fully managed or just a wrapper around pg_dump.exe. Later, Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(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
[GENERAL] Possible tiny issue with pg_dump.exe on windows
This may apply to Unix versions as well but I have not tried. Anyway, I am using C# to create a GUI front end to pg_dump and I am using the PGPASSWORD environment variable to set the password like this: System.Environment.SetEnvironmentVariable(PGPASSWORD, myargs.password); This works fine and pg_dump.exe executes happily. But now here is the issue: When I pass a database name that does not exist after setting the password via the environment variable instead of writing the database not found error to standard error, it prompts for the password and causes the C# program to lock. Now the chance of it getting a bad database name is slim, but it could happen if a database is deleted and the list is not refreshed. I am not forcing it to display the password prompt with -W by the way. Shouldn't it write the database not found error to standard error instead of prompting for the password? If I purposely pass other bad arguments after setting the password it does not display the password prompt in the command window. I noticed this because during development I am showing the process window and for production it of course will be hidden. I notices this on the 8.3 version of pg_dump, but probably applies to others as well. Has anyone else noticed this? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Lets get the 8.3 Announcement on the front page of Digg
http://digg.com/programming/PostgreSQL_8_3_has_been_released I dugg it :-) Later, Tony ---(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: [GENERAL] Lets get the 8.3 Announcement on the front page of Digg
At the bottom of that Register article I saw this article: http://www.regdeveloper.co.uk/2008/01/24/stonebraker_dewitt_mapreduce/ In which it says: Ingres inventor and Postgres architect Mike Stonebraker So this Stonebraker guy is the Postgres Architect? Interesting stuff on the Register :-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Is PostGreSql's Data storage mechanism inferior?
Swaminathan Saikumar wrote: http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql Check out the link. I am starting out on a new personal project had zeroed in on PostGreSql with Mono-ASP.NET as ideal for my needs, mainly owing to a PostGreSql whitepaper. Now, I chanced upon the article above. I've pasted the cons as mentioned in the article, and would like the community feedback on it, especially with regards to the inferior Data Storage mechanism. The cons of PostgreSql Hosting * Performance considerations: Inserts and Updates into the PostgreSql database is much slower compared to MySql. PostgreSql hosting thus might slow down the display of the web page online. * BSD license issues: Since PostgreSql comes under the Berkeley license scheme, this is again considered to be too open. * Availability of inferior Data Storage mechanism: PostgreSql uses Postgres storage system, which is not considered to be transaction sae during PostgreSql hosting. * Its not far-flung: While MySql hosting and MSSql hosting have deeply penetrated into the market, PostgreSql hosting still remains to be passive in the database hosting market. * Non-availability of required assistance for PostgreSql hosting: Assistance is being provided via mailing lists. However there is no guarantee that the issue faced during PostgreSql hosting would be resolved. Those cons are seriously out of date. They apply to very old versions of PostgreSQL and even that is stretching it. The part about the BSD license is bogus. A BSD license is the most desirable of any Open Source license and gives you the right to use PostgreSQL in your commercial apps without worry. The part about inferior Data Storage mechanism is also flat out wrong. PostgreSQL uses a MVC system same as Oracle and it also has transaction logs,PITR etc. Inserts and updates will be slightly slower than a Non MVC system, but the human eye would not detect any difference in a web page displaying, we are talking about miliseconds. Who does massive amounts of inserts and updates from a web page anyway? I have CMS such as Drupal running on both PostgreSQL and MySQL and I can't tell the difference in the speed the pages render. The availability of assistance is also bogus as there are many ways to get support if you need it including commercial support companies and Enterprise DB, The mailing list is also very active and effective. The only part that has any truth to it is the far flung part, and MySQL is king there, it even dwarfs M$ SQL server. M$ SQL server is severely limited for hosting as well since it ONLY runs on windows and most hosting providers run some form of Unix where M$ cannot play at all. So in conclusion I would not pay attention to this article, it was written by someone who really does not know what they are talking about. Later, Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] PostgreSQL 8.2 and Firebird 1.5 comparative
I submitted this to digg. http://digg.com/software/PostgreSQL_8_2_vs_Firebird_1_5_for_Enterprise_Use Needless to say PGSQL has a lot more yes entries :-) Please give it a digg if you want. Thanks, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] moving from mysql to postgree
Pau Marc Munoz Torres wrote: Hi I'm moving from mysql to postgresql just now i I'm a bit lost, could anyone tell me some place with a comparative between postdresql and mysql commands, i think than mostly is the same think but, any way, do anything change ? pau -- Hi, You should check out Lightning Admin. We have a version for both that have the same look and feel and could make the transition easier. You can also ask questions at our forums: http://www.amsoftwaredesign.com/smf regarding moving from MySQL to PostgreSQL. Tony Caduto http://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL and MySQL Both versions for 24.99 (limited time only). ---(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
[GENERAL] 8.3 vs 8.2 sql compatiblity issue
Hi, Just running some queries that have worked from 7.4 through 8.2 and they don't seem to work on 8.3. select case when a.attnum = any(conkey) then true else false end from pg_constraint where contype = 'p' and conrelid = c.oid This one is puking on a.attnum = any(conkey) returns the following error: SQL State: 42883 ERROR: operator does not exist: smallint = text HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 0 Record(s) Returned I am actually getting a lot of these operator does not exist errors in 8.3 another one I get is operator does not exist for char=integer i.e. attnum = pi.indkey[0]) used to work but fails in 8.3 Ideas? Thanks, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Compiled debugger plug in for windows, any one have it?
I dont' have a c/c++ compiler on my PCs as I am a Delphi guy. Anyone have it compiled for win32 and willing to share? Actually I do have CodeGear C++, but it's unlikely to work with that. Thanks, Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.3 vs 8.2 sql compatiblity issue
Tom Lane wrote: That's no doubt got something to do with it, but I think Tony is mighty confused about exactly what is failing. pg_constraint.conkey is not text, for instance; it's smallint[] and so the quoted bit should still work just fine. I'd suggest trying the query in some client that gives you an error location pointer, which whatever he's using evidently does not. regards, tom lane You are exactly correct, I copied the wrong line in the original message, sorry about that, it was this line: case when a.attnum as text IN( select array_to_string(conkey,',') from pg_constraint where which is fixed by adding a cast: case when cast(a.attnum as text) IN( select array_to_string(conkey,',') from pg_constraint where I must have missed it in the release notes about the implicit casts not working anymore. It's going to be a huge pain in the ass to go through all the code and add explicit casts :-( Thanks, Tony ---(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
[GENERAL] Question about PostgreSQL from Delphi newsgroups
Hi, Someone asked me a question about PostgreSQL on the Delphi newsgroups and I was not sure how to answer them: Do you know if using PostgreSQL a query or connection can have a priority set, so it can run quicker than other queries? For example, in a POS system the reporting queries should have lower priority than the generated invoices insert queries. Thanks, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Tom thinks it's bad code was 8.3 vs 8.2 sql compatibility issue
Alvaro Herrera wrote: Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: case when cast(a.attnum as text) IN( select array_to_string(conkey,',') from pg_constraint where Surely that's the worst bit of SQL code I've seen in awhile. Wow, you really are lucky. You guys really should keep such kind words to yourself. Not sure how in the hell you can say its bad code when it is just a little piece. You don't even know what it does. Again thank you for you kind words of wisdom. Have a great day. Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PLpgsql debugger question
Hi, Does anyone know if there is a debugger function that will return the line numbers that are executable? Also, is the debugger code available at pgfoundry the GUI client that EnterpriseDB has done or is the module that needs to be installed on the server? As I understand it the debugger functions are included by default in 8.3, but how do you install for 8.2? Thanks, Tony Caduto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PLpgsql debugger question
Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: As I understand it the debugger functions are included by default in 8.3, That's incorrect. regards, tom lane Ok, thanks for the info. Back in Sept the debugger was advertised as a feature of 8.3, so if it's not included how is it a feature? Is it going to be included as a contrib module or something else? I am talking about the server side stuff not the EDB GUI client. Thanks, Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PLpgsql debugger question
brian wrote: I don't know what you're referring to when you say it was advertised as a feature but it's not a part of the PG release. You can get it here: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 From the article: After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org http://www.postgreSQL.org. They will include: A finished PL/pgSQL debugger, a tool for editing PostgreSQL's version of the standard SQL data access language in database applications. Full text search made more accessible by becoming a feature included in the system code instead of being an add-on option. Clustering code from Skype for load balancing and spreading queries to a large database across several PostgreSQL systems. Now you know what I am talking about :-) Later, Tony Caduto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PLpgsql debugger question
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 14 Nov 2007 12:49:37 -0600 Tony Caduto [EMAIL PROTECTED] wrote: Here ya go: http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375 I see nothing incorrect in that article. Sincerely, Joshua D. Drake Who said anything was incorrect? It's just a bit misleading (the Info Week Article). It's just after reading that MANY readers would think that if they install 8.3, the debugger hooks/whatever would be ready out of the box. Whoever is doing the release notes may want to have something in there about the debugger and the fact that it's not included and has to be manually compiled and all that. In the current release notes for 8.3 it makes NO mention of the debugger. Later, Tony Caduto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PLpgsql debugger question
Richard Huxton wrote: So you're saying the finished plpgsql debugger will be available from www.postgresql.org ? After nine months of work, the new features in 8.3 will be available at www.postgreSQL.org. They will include: * A finished PL/pgSQL debugger There is no mention of anything debugger related in the 8.3 beta release notes either. Kind of seems like its not really a feature to me, but what do I know :-) The article is very misleading with regards to the debugger. Later, Tony Caduto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Verison 8.3 PL/pgSQL debugger Question
Hi, Is there any documentation for developers on how to use the new debugger in 8.3? Specifically on how it works and general guidelines on integration into 3rd party GUI applications. thanks, Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Stephen Ince wrote: Postgres can't be embedded or serverless. Firebird has the embedded feature. Most of the databases have this capability (hsqldb, derby,oracle,mysql, firebird, and db2). Derby and hsqldb are the only free embedded databases for commercial use. A lot of Firebird users have been saying this as well, but the comparison if more for Enterprise use. Plus if you need a embedded database wouldn't it be better to use one built specifically for that purpose? i.e. SQLite for example. Good call on the name limit, I remember running into that when porting something from MS SQL server to Firebird about 4 years ago. I will have to check and see if this still applies to version 2.0 Later, Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Can someone else verify if this is a bug?
Hi, I think there might be a bug in the built in function pg_get_viewdef. Basically if you have a function in your view SQL like this: replace(address1, '\r', '') pg_get_viewdef is returning the view definition with the \r replaced by it's ASCII code which causes this: replace(address1, ' ','') The returned def gets a line break where the \r should be. I have verified this in Lightning Admin and PG Admin III, just wanted a second opinion. I apologize if this is already known. Thanks, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Hi, Someone mentioned we should put this in the PostgreSQL wiki. Do you guys think that would be beneficial? If so, I don't mind the work on the list I have done so far going on the wiki. It would make it a lot easier to add other DBs to the mix. Later, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Greg Smith wrote: This is a really good comparision, focusing on features that I think people understand rather than so much on technical trivia. Someone else mentioned moving it onto the Wiki. Questions that pop into my head: -Tony, would be you be comfortable with your work being assimilated into a larger table that was hosted somewhere else but credited yours as a source? Thanks Greg :-) I don't have any problem with what I have done so far being assimilated in a larger work as long as I get credited as a contributer. Later, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Dave Page wrote: Couple of corrections Tony: - You don't necessarily need to stop the postmaster to take a filesystem backup - http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP. Obviously that assumes logs will be replayed during recovery. - The native win32 port will run on FAT32, we just prevent the installer from initdb'ing on such a partition. You can do it manually however, but tablespaces won't work. I'm a little puzzled about why you list multi-threaded architecture as a feature - on Windows it's a little more efficient of course, but the multi-process architecture is arguably far more robust, and certainly used to be more portable (I'm not sure that's still the case for platforms we actually care about). Regards, Dave. Thanks Dave. Will update ASAP. I agree with you on the multi-threaded. I think I will add a note saying the the multi-threaded architecture is only advantageous on Windows. I have seen instances where the threaded version of Firebird completely craps out because one of the threads has issues. Will also make a note that it can run on FAT32 with some limitations. Later, Tony ---(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: [GENERAL] PostgreSQL vs Firebird feature comparison finished
Dave Page wrote: Tony Caduto wrote: Other than that I would say PG kicks butt. You're just realising that? :-) Ah, I new that around 2004 :-) I just have to convince Delphi users of that :-) Later, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] PostgreSQL vs Firebird feature comparison finished
Check it out here: http://www.amsoftwaredesign.com/pg_vs_fb When comparing in the grid the only major advantage FB has is probably BLOB support. PG only suppports 1 gb while FB supports 32gb. Bytea is pretty slow as well when compared to the FB BLOB support. The other area is Character sets and collation. They support it at a field level as well as the database. Other than that I would say PG kicks butt. If there is any interest I could also add MySQL 5.0 to the mix as the third column. Later, Tony ---(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
[GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison
Hi, I was just wondering if anyone could help me out by taking a look to see if I missed any important features. http://www.amsoftwaredesign.com/pg_vs_fb This comparison is going to be for the benefit of Delphi users. The Delphi community is heavily biased to Firebird. Please post any comments or suggestions here: http://www.amsoftwaredesign.com/smf/index.php?topic=138.0 You don't need to register. Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison
Dmitry Koterov wrote: One difference in SQL syntax is that FireBird could join stored procedures like this: SELECT b.* FROM get_ids() a LEFT JOIN get_data(a.ID http://a.ID) ON 1=1 (where a.ID http://a.ID parameter is passed from the previous set as a next procedure parameter), but Postgres cannot. Can't PostgreSQL do that now though? Functions with out params are treated like tables as of 8.1. Later, Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql INSERT speed (how to improve performance)?
Lim Berger wrote: INSERTing into MySQL takes 0.0001 seconds per insert query. INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. What can I do to improve this performance? What could be going wrong to elicit such poor insertion performance from Postgresql? Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend The first thing I would ask is what type of DB engine are you using while doing the inserts on MySQL? The reason MySQL is doing the inserts faster is it does not have transaction support if you are using anything other than InnoDB. With that said you can increase your insert performance by simply using a transaction and committing every 1000 rows or so. If you do this you will see a huge performance increase. hope that helps. Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of Lightning Admin for PostgreSQL and MySQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql INSERT speed (how to improve performance)?
Lim Berger wrote: On 8/14/07, Andrej Ricnik-Bay [EMAIL PROTECTED] wrote: On 8/14/07, Lim Berger [EMAIL PROTECTED] wrote: INSERTing into MySQL takes 0.0001 seconds per insert query. INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query. What can I do to improve this performance? What could be going wrong to elicit such poor insertion performance from Postgresql? Lim, Are you sure you are not confusing Seconds VS Milliseconds from one query tool to the other? What tools are you using to show the query times? As far as I know the psql command line tool shows milliseconds not seconds. You should probably post some more info like the actual insert query used and the table. I have never seen a insert take that long even a big one, unless you have some network latency to the PGSQL host and it's causing the time to be messed up on the client. Might also help to let us know exactly how you are timing this stuff. Are you connecting remotely via PSQL or are you connecting via SSH and running psql or mysql that way? Later, Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for PostgreSQL and MySQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Reordering columns, will this ever be simple?
Gregory Stark wrote: novnov [EMAIL PROTECTED] writes: Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. From a admin tool developers perspective the ability to reorder columns without manually copying to a new table and all that is pretty exiting :-) Tony Caduto AM Software Design http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service
Andrei Kovalevski wrote: Hi all! Everyone who use PostgreSQL server on Windows knows - it would be nice to have some tray management and monitoring tool for PostgreSQL server which is running as NT Service (for example - MS SQL already have such tool). I have created a new project on pgfoundry - http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can we improve this tool and what features whould be helpful. Thanks, Andrei. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Have you done any development yet? I can do something in Delphi in a matter of hours and would be able to donate the code as a BSD license. I have done a similar tray application for Firebird and it would be just a matter of changing the service it monitors. We could also do a control panel applet. Later, Tony Caduto AM Software Design ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] The leanest, meanest Windows installer possible
Mitchell Vincent wrote: I've been using PG for years and years but bringing it to the Win32 desktop presents some deployment challenges. Since this software will be downloaded I am looking for the smallest possible installer that provides the functionality I'm looking for. Hi Mitchell, I created a slick installer to do just what you want with Inno setup. Nice and easy to use with no MSI/WIX dependencies or complexities. Get it here, full source included with a BSD license for the parts I wrote. www.amsoftwaredesign.com/downloads/pg_installer_setup.zip It's only been tested with 8.1, but should work just fine with 8.2. This program is AS IS..with no support from AM Software. Inno Setup is available from here for free: http://www.jrsoftware.org/isinfo.php I can't remember exactly, but I think this one comes in at around 8mb. -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(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: [GENERAL] Proposed Feature
Bruce Momjian wrote: Naz Gassiep wrote: I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. And what does the icon show or do? That would be pretty easy to do with Delphi. I could whip something up and donate it to the project with a BSD license. It could be a green Play arrow if the service is running and a red one if it is not, then have a few right click options to start/stop/restart the service. I was thinking the same thing awhile back, but forgot about it. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SQL Manager 2007 for PostgreSQL released
Guillaume Lelarge wrote: I asked them some time ago. They answered me this : As for SQL Manager for PostgreSQL - we regret to inform you that the development and support of Linux editions of EMS software products has become impossible now that Borland no longer supports Kylix libraries for Delphi, on which all Linux versions of EMS software were based. Hence, EMS has made a decision to discontinue Linux versions of its products. Linux products will not be available for sale or download anymore. Just a FYI, their win32 versions will probably work fine in Linux via WINE. Lightning Admin does anyway(work via WINE) and they program their products with Delphi as well, so I think it would work fine. Just one word of advice about WINE, make sure you have the core MS true type fonts installed or the win32 apps will look funny, especially when using editors. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best option for Postgresql Administration ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres data/form entry tool
David Potts wrote: Can any body recommend a generic opensource data entry tool that can be used to make some simple forms for entering data in to postgres? ---(end of broadcast)--- TIP 6: explain analyze is your friend For Desktop applications on win32 Lazarus :http://www.lazarus.freepascal.org/ Delphi Turbo Explorer: http://www.turboexplorer.com (use with ODBC or Zeoslib http://www.zeoslib.net) Sharp Develop: http://www.icsharpcode.com (use with npgsql .net data provider) For web based: PHP and use NVU (http://www.nvu.com) to design the forms. Netbeans IDE There are probably many more -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best option for Postgresql Administration ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is there a shortage of postgresql skilled ops people
The sad reality from what I have observed is that unless more people gain those skills and want to work in ops, it's becoming very hard for me to justify recommending postgresql for enterprise (or larger) scale projects. What do others do and/or experience? I think there are people around, but maybe they don't want to move etc. If a PostgreSQL job where ever to show up in Milwaukee, I would apply for it in a heartbeat. Another thing is this, how hard could it possibly be for a MS SQL DBA or Oracle DBA to pick up using PostgreSQL? I don't think it would take a decent admin of any database to come up to speed in a very short time as long as they were interested in doing so. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql to Delphi
Bob Pawley wrote: Hi I have posed this question to the Delphi list but they don't appear to be able to help. I am attempting to have the information in a PostgreSQL database table trigger a function in Delphi. At present the delphi application is triggered by a mouseup on a TImage file, residing in a notebook, through an alias (DeviceNotebookMouseUpAlias). I want to have the Postgres use this alias to trigger the function. Could someone point me to any literature that would help me with this project? Bob Pawley I would have replied to the Delphi List if I had seen it :-) The best way would be to use the listen/notify that PostgreSQL provides, but only a couple of the Delphi libraries support notifications from PostgreSQL, zeoslib does not work at all, nor will the PGSQL driver that shipped with Kylix. PostgresDAC from Microolap (http://www.microolap.com) will work with notifications and they have pretty decent support. If you prefer Dbexpress you could try the driver from www.vitavoom.com which has support for notifications, but it is a bit pricey compared to the Microolap product. There is also a library for libpq.dll out there if you want to code really low level. And for those out there who don't know what Delphi is, check out http://www.codegear.com It's basically Visual Object Pascal and works very similar to VB version 6, except it has full support for inheritance etc and can program down to the bare metal using inline Assembler if you really need to do that :-) It also has full support for pointers etc so you can pretty much get the performance of C or C++ all in the same package. Using Delphi for database applications is like being in Paradise all the time :-) Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Built with CodeGear Delphi 2007 Your best bet for Postgresql Administration ---(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: [GENERAL] Postgresql to Delphi
Bob Pawley wrote:
Listen/notify implies a dynamic table that is constantly on the
change. Am I correct?
The table I am talking about is completed through one interface then
imported as a relatively static information base for the application
in which I am seeking help.
Yes,
you would have to define a rule on the table and when the table changed
it would send a notification back to who ever is listening.
Why don't you just run a query against the table, then loop through the
result set and then trigger your devices from inside the loop?
myquery.sql.add('select * from mytable where bla = bla;');
myquery.open;
While not myquery.eof do
begin
if myquery.fieldbyname('somefield').asstring = 'something' then
//signal your device.
myquery.next;
end;
I guess that is the best I can come up without knowing more.
Later,
--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] pg_dumpall and version confusion
Joshua D. Drake wrote: other deficiencies let's not add to complexity by having an --output-version. If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. Joshua D. Drake I am afraid that I have to agree with Tom here. Pg_dump has plenty of Well for me that would not be a option. I use object pascal and can't statically link C code into my apps. Doing the statically linked thing would also bloat any resulting binary. I don't think the whole --output-version thing would be a good idea either, but it would be a simple matter to add some logic for the GRANT ON SEQUENCE, which seems to be the only thing that is really causing problems at least between 8.1 and 8.2. The old way of granting permissions on sequences still works on 8.2 right? If so then maybe a switch to disable GRANT ON SEQUENCE would do the trick. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dumpall and version confusion
Tom Lane wrote: We try to make pg_dump cope with prior server releases, but since we lack a time machine we can't go back and teach old versions about subsequent server changes... Tom, How come version 8.2 of pg_dump uses GRANT ON SEQUENCE when dumping a 8.1 or older database? Was it just a oversight? Seems it should be a simple matter to add some logic that says IF version = 8.2 THEN use grant on seq else don't use it. Bruce told me just the opposite of what you said in the above message. Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(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: [GENERAL] pg_dumpall and version confusion
Alvaro Herrera wrote: What's wrong with that? 8.2 will understand the GRANT ON SEQUENCE without a problem. Yes, but 8.1 and earlier wont! If you dump a 8.1 database (with 8.2 pg_dump) and then use that dump to restore to another 8.1 server, any permissions on sequences will fail to restore because the 8.2 pg_dump can't handle NOT using GRANT ON SEQUENCE on the lower version of the server. Would it really be that big of a deal to add some logic to 8.2 pg_dump to say: Hey I am not dumping a 8.2 server, so don't use GRANT ON SEQUENCE? most Admin tools ship with the latest version of pg_dump and restore, so If I attempt to restore that dump via pgAdmin III (or other tools) which is using 8.2 versions of dump and restore it will fail on a 8.1 server. Do you see the point I am trying to make? Should admin tool vendors start shipping every version of pg_dump now? In a earlier message about this same thing Bruce said (I am quoting from memory so it's not exact) that we don't try and make pg_dump backwards compatible and to use the dump that came with whatever version you are dumping from So to me that seemed opposite of what Tom said. If I misquoted anyone I apologize in advance. Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_dumpall and version confusion
Dave Page wrote: This is a problem I've been thinking about on and off recently - and I am starting to come to the conclusion that shipping each version of the utilities is the only way things are likely to work unless someone puts some really significant effort into adding backwards compatibility modes to pg_dump (which I imagine is likely to meet resistance if offered as a patch anyway). I never had a issue before the 8.2 dump and the GRANT ON SEQUENCE. The version differences in PGSQL are nothing compared to what goes on with MySQL. Maybe that would be a good Google summer of code project :-) (make pg_dump more backwards compatible to at least 8.0) Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to write a function that manipulates a set of results
Ashley Moran wrote: It fetches about 20 rows from a table of approx 4 million, then it needs to do further calculations to narrow them down further. Previously we have only written database code for SQL Server, and there we would use a table variable. Apparently temporary tables are not equivalent and not suitable for this. Seems like a temp table with a insert into would work for you. insert into mytemptable (field1,field2) select field1,field2 from sometable where field1 = 5; Then you can query the new temp table anyway you would like while you are still in the function. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] grant on sequence and pg_restore/pg_dump problem
Bruce Momjian wrote: Tony Caduto wrote: Hi, I did a quick search and didn't see anything on this, if I missed it sorry in advance. Anyway, I was doing a restore of a 8.1 database(on a 8.1 server) using the 8.2 pg_restore and it was throwing errors when it was trying to restore the permissions on the sequences. basically the pg_restore was using the grant on sequence against the 8.1 database which of course 8.1 knows nothing about. Is there a switch or something I missed that would allow this to work properly on a 8.1 or lower database? Or do I have to now have 2 versions of dump/restore in order to do this? You can use 8.2 to dump 8.1, but for restore, you should use the same version as the target database. So for admin tool vendors, we need to now ship more than one copy of pg_restore? Wasn't the restore pretty much backwards compatible until now? Certainly pg_restore must know what version of the server it's connecting to, shouldn't it be able to adjust the GRANT ON so on versions 8.2 it does not use the SEQUENCE keyword? Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] grant on sequence and pg_restore/pg_dump problem
Hi, I did a quick search and didn't see anything on this, if I missed it sorry in advance. Anyway, I was doing a restore of a 8.1 database(on a 8.1 server) using the 8.2 pg_restore and it was throwing errors when it was trying to restore the permissions on the sequences. basically the pg_restore was using the grant on sequence against the 8.1 database which of course 8.1 knows nothing about. Is there a switch or something I missed that would allow this to work properly on a 8.1 or lower database? Or do I have to now have 2 versions of dump/restore in order to do this? Thanks, -- Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Password issue revisited
Magnus Hagander wrote: Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. Is there truly such a thing on a windows PC? All it takes is one Virus or Malware to gain access to the PC and anything stored in the user profile is easy picking. The virus and malware creators may not know about the pg_pass file now, but they will eventually. What about having a wallet type system where the user can create a pass phrase to protect a generated key that would get loaded once per session. That is how KDE allows users to store passwords. I work at a large financial institution and if the auditors knew about the pg_pass being plain text, they would pretty much ban it's use. Anytime a password is sitting on a non encrypted file system, regardless of it's permissions it is potentially at risk. -- Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Nice article on Unicode and it's encodings (utf8, utf16 and utf32)
http://developersoven.blogspot.com/ -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
one last thing mysql team doesn't afraid to change behaviours between minor releases, look at this thread That is so true, all the differences between minor versions made creating Lightning Admin for MySQL a pain in the rear... After I did the port I really appreciated how clean PostgreSQL is. -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] CodeGear working on a new DB access layer for Delphi, but.....
It seems they are not going to include support for PostgreSQL (just the big 4), which would be a big mistake if you ask me. Here is the link to the lead developer's blog who is working on this new technology http://blogs.codegear.com/SteveShaughnessy/archive/2007/02/16/31865.aspx?Pending=true Take a peek and add a comment (if you want to) that nicely suggests PostgreSQL support. I know there are not a lot of Delphi developers on this list, but the more stuff that supports PostgreSQL the better right? Later -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Dave Page wrote: What you are saying is that because you don't believe in the pgpass design, you are going to summarily delete them - which I know for absolute sure would *really* annoy some pgAdmin users that I know for a fact have a whole heap of passwords stored in theirs. Doing that would only hurt your products reputation, not mine. Dave, My product is not storing passwords using pgpass without the users knowledge. If pgAdmin III stored it's own passwords in the registry it would be up to the user (as it should be) to use pgpass. If they chose to use pgpass, libpq would override the passwords stored in the registry anyway, which is what pgAdmin III is doing automatically to my application without my or my users consent. pgAdmin III is corrupting the intended use of pgpass. It seems you guys did it as a shortcut so you wouldn't have to write your own password storage code which is not that difficult to do anyway. If you guys are at all interested in doing the right thing you will take this very seriously and find a way to fix it. I know you think I am being a pain, but I am just sticking to my guns on what I KNOW is right. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL/FireBird
RPK wrote: How is FireBird rated when compared with PostgreSQL? I used to be a big time user of Firebird, but then I discovered PostgreSQL and have never looked back. Firebird has only a few built in functions even the simple ones are missing. To get the built in functionality of PostgreSQL's built in functions, you have to use UDFs compiled in C,C++ or Delphi/Kylix/Free Pascal. Many times these UDFs are unstable and can crash the whole server. Firebird also uses a single file for the database that must be referenced like this: 192.168.23.45:c:\program files\data\mydatabase.fdb There is no transaction log, no way to do log shipping etc. This is nice for a embedded system, but not for a robust enterprise class database. They do have the ability to set alias for the connection path, but it's a manual setup process in the conf file. Firebird as of 2.x still does not have temp tables and it is limited to one stored proc language. If need a embedded database Firebird is a good choice, however if you want a serious database that can compete with Oracle or M$ SQL server then look no further than PostgreSQL :-) -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] I might have found a bug on 8.2.1 win32
I had installed the win32 version awhile ago, but I had the pg_hba.conf set to trust. Then I started to test SSL on win32 so I changed it to this: hostall all 127.0.0.1/32 md5 hostall all 192.168.15.131/32 md5 #my pcs adddress And I ensured the service had been restarted after making the change to md5 instead of trust for my PC address. Ok, here is the problem, If I pass in a blank password '' the md5 authentication is not done and I simply go right in with full access. If I pass in a space ' ' the I get the password authentication error. Normally with a blank password I would expect to see the no password supplied error, but that is not happening on win32 it just gives full blown access. Here is the connect string being passed to libpq.dll when I use the blank password, this string is captured from the debugger: hostaddr='10.201.170.131' port='5432' dbname='template1' user='postgres' password='' connect_timeout='15' sslmode=disable I tried the same thing on a Linux server and it does not behave this way, only on win32. I then uninstalled 8.2.1 on the win32 box and completely deleted the data directory and reinstalled and the same behavior prevailed. I know a new connect GRANT was enabled in 8.2, but I though that was in addition to the first checks done in pg_hba.conf. Maybe I am doing something wrong, but it sure doesn't seem that way. Like I said it might be a bug. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Question, I hope stupid, postgres user HAS a password right? Yes, it has a password, I set the password from the installer, and I even reset it after the install was complete. I just tried this from the command line and it let me right in: psql template1 -U postgres Didn't ask for a password or anything, and the localhost entry in pg_hba.conf is also set for md5. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Bill Moran wrote: Will only apply if you connect via loopback networking (which is not psql's default). Try your connect command like this: psql template1 -U postgres -h 127.0.0.1 and see if the results change. Hi Bill, Using the -h 127.0.0.1 does cause the password prompt to fire, however the weird part is in my pg_hba.conf I also have the local entry set to md5 local all all md5 I did some more testing and it seems the password I used during the win32 install is being cached somewhere. If I don't change the password from the one I used during the install it lets me right in, if I change the password to something else I get a password authentication error with a blank password, if I then change the postgres password back to the one I used during install a blank password again lets me right in. I have not done a reboot yet, I will do reboot and see if that clears it up. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Well, there is something weird going on here: If I change the postgres users password to the one I used during install I get this when using psql: C:\Program Files\PostgreSQL\8.2\binpsql template1 -U postgres DEBUG: InitPostgres DEBUG: StartTransaction DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid 1348/1/0, nestlvl: 1, children: DEBUG: CommitTransaction DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid 1348/1/0, nestlvl: 1, children: Welcome to psql 8.2.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. template1=# If I change the password to anything else I get this: C:\Program Files\PostgreSQL\8.2\binpsql template1 -U postgres psql: FATAL: password authentication failed for user postgres I did a search for a password file but it came back empty. Anyone have any ideas on why this is happening? -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Never mind, somehow a application setup a pgpass.conf file for me without my knowledge. Sorry it's not a bug. However I wonder if it might be a good idea if psql would raise a warning after logon that a pgpass.conf file was used for authentication or have it written to the log. I am sure some 3rd party application I was testing created the file when it took my server login information. I would have found out about it right away if it's use would have been written to the log. Thanks everyone for you help. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(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: [GENERAL] I might have found a bug on 8.2.1 win32
Magnus Hagander wrote: Have you been leaving the dark side lately? (pgAdmin does this..) I still have pgAdmin installed :-) it's a good reference tool. I imagine you guys are using it for the pg_restore/pg_dump? There really should be a pg_dump.dll and a pg_restore.dll so a pgpass file would not be needed. The microolap guys have created the dlls and it's what I use now for my dump/restore operations. Is there a way to tell libpq in the connection string or something not to use a pgpass.conf file? I looked in the libpq docs but didn't really see anything. I kind of don't like the way the pgpass file affected all the apps that used libpq, it could be considered a security risk. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Dave Page wrote: Could this be proof of you using pgAdmin ( :-) ) and checking the Store Password option? That'll save it in pgpass.conf in your profile. /D I won't deny I have it installed :-) I don't remember using the stored password option though. Do you also use that for the pg_restore/pg_dump so you don't have to pipe the password? Why don't you just store the password in a file in the same directory as the pgAdmin executable? That way it would not affect other applications. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] I might have found a bug on 8.2.1 win32
Dave Page wrote: I must Be missing the point - why should I work something out with your app for using a documented feature of libpq in pgAdmin? Dave, The whole point is pgAdmin III is storing the password in the pgpass file which is global for every single application that uses it, don't you see the problem? It's clear as crystal pgAdmin III should be storing the password on win32 in the registry like every other win32 program does, or in a ini file, it's the accepted way to do that sort of thing on win32. It just is, and the way you are doing it is not the way it should be done in the win32 world. I was just asking that we work together to find a way our applications would not affect each other, and currently pgAdmin III is the clear violator by using a global feature of libpq to store passwords for its own use without regard for other applications that may be using libpq. I don't think the pgpass file was intended to be used as a password storage method for pgAdmin III, it was meant as a way to allow psql and other command line programs to be easily used from scripts where it would be detrimental to have a password prompt pop up during a scheduled run or maybe I am wrong and it was created just for the use of pgAdmin III but that does not really matter. I am sorry, but I believe the way pgAdmin III is using the pgpass file is TOTALLY WRONG, sorry but it just is. It's global to every single app that uses libpq on the PC, I just don't understand why you don't get it. The way you make it sound is like pgAdmin III does not want to play nice with other applications, and I can do that to. I don't want to delete the pgpass file, but I will so my application gets the same rights as pgAdmin III. I have users who set their test servers to trust and why should I make them store a password when they don't need to? That's what I would have to do, force the user to enter some form of password, so it's not blank or force them to store a password even if the server is set for trust access. Sorry in advance if you don't like what I have to say, but sometimes you can be very stubborn and one way or the highway!!! Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(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
[GENERAL] Any Plans for cross database queries on the same server?
Dblink is nice, but should it really be needed for databases on the same physical server? What would be cool is to allow a double dot notation i.e. database1..schema1.table1 Just a idea. Comments? -- Tony ---(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: [GENERAL] Any Plans for cross database queries on the same server?
Peter Eisentraut wrote: This has been discussed about ten thousand times, and the answer is still no. Why? Seems to me if it was discussed that much it must be a very sought after feature. How come it's not on the TO Do list for the future at least? Is it because of some limitation of the core engine or something? -- Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Any Plans for cross database queries on the same server?
Ron Johnson wrote: be separate databases because they're clearly related data. Just because they are related, doesn't mean that it's always wise to lump it all in the same database. Mainly for scalability and performance reasons. I would tend to agree, there are numerous times being able to do a cross database query without the hassle of DBlink would be extremely handy. I could also see it being valuable in a data warehouse type situation. I know it can be done in M$ SQL server using .. notation and I bet you can do it in DB2 and Oracle. you can even do it in MySQL, in MySQL it's their way of implementing schemas. Considering all these other DBs can do it, doesn't it make sense to at least put it on the radar for Postgresql? Just my 2 cents -- Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Any Plans for cross database queries on the same server?
Mark Walker wrote: It's sort of a matter of taste, but there are lots of people who like to keep there logic on the server or at least within sql statements, so there's probably a good sized market that your not reaching if you ignore it. That is a good point, I and many developers I know like to keep all the business logic on the server in stored procedure and functions and having this ability as a native part of Postgresql would be a huge advantage. DBlink is a decent workaround,but it becomes a pain having to wrap everything with the dblink syntax, plus there is a little bit of overhead involved creating a connection over TCP/IP to run a query on the same server. DBlink is great when you need to connect to a different server though. Later, -- Tony ---(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: [GENERAL] Any Plans for cross database queries on the same server?
David Fetter wrote: That being said, I think it is a dumb feature. If you have data in one database, that requires access to another database within the same cluster. You designed your database incorrectly and should be using schemas. I would have to disagree, it's a feature that has been available on M$ SQL server and the other commercial databases for years. It's hardly a dumb feature. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Default fillfactor question (index types other than btree)
Does anyone know what the default fillfactor is for index types other than btree? I found in the docs that the default for btree is 90, but can't seem to find what it is for the other index types. Thanks in advance, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgresql 64bit question
Hi, I was just wondering if a 32bit client connected to a 64bit server, would it be possible for the 64bit server to return a OID that was over 4 billion to the 32 bit client and possibly cause a range error if the OID value was used in a unsigned 32-bit integer var? Thanks, -- Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] MSSQL/ASP migration
Robert Fitzpatrick wrote: Any docs or other helpful info is welcome, just looking for some advise. One think I would recommend is to make sure when creating the new table structure that you make sure not to use capitalized object names. Because MS SQL server can be case insensitive I have found there is always caps somewhere, and the caps make things in Postgresql a PITA because you have to quote everything in your application code. You could also take a look at Lightning Admin, we have some nice import and export wizards that can import data from any ADO or ODBC source. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Building web sites using a database
Jan Mura wrote: Hello, I would like to ask if there is a genral concept of creating web pages using a database and PHP or another scripting language. What I mean is to store basic entities and relations between pages. Only the certain texts or information regarding every site will differ. But indeex page, search page, result page and so on for every site should look similarly so the application select a structure of a page from DB and add certain text. I am not sure if I am clear. The aim is to create sites quickly because a creator just insert into the database text for every page. And I am looking for some documentation or info how to do this and how to deal with such things. Thanks Jan Mura [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Well, you could always start with something like Drupal: http://www.drupal.com You can create sites very quickly and everything is stored in a Postgresql or MySQL database. I recently converted my website to it and could not be more happy :-) -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
