[GENERAL] Install Postgresql on Win2000 with Admin Rights
Hello, is there a Way to install PostgreSQL ( 8.3.1 ) on a Win2000 Maschine without Admin Rights? Thanks for your Help, Stefan Sturm -- 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] Checking if Aggregate exists
Erik Jones wrote: On Mar 24, 2008, at 2:18 PM, Josh Trutwin wrote: On Mon, 24 Mar 2008 14:02:02 -0500 Erik Jones [EMAIL PROTECTED] wrote: On Mar 24, 2008, at 1:09 PM, Martijn van Oosterhout wrote: On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote: My code to check if an aggregate exists runs this query: SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = 'foo'::REGPROC; Seems to me you'd rather want the proisagg column in pg_proc and forget about pg_aggregate altogether... Also, the idiom for checking if something is present is normally: SELECT 1 FROM some_table WHERE ...; This way you aren't dealing with errors, if it doesn't exist the query simply doesn't return any results. This one still does return an error though I think because of the cast: select 1 from pg_catalog.pg_aggregate where aggfnoid = 'foo'::regproc; ERROR: function foo does not exist As Martijn pointed out, use pg_proc instead of pg_aggregate: SELECT 1 from pg_proc WHERE proname='foo' AND proisagg IS TRUE; And, as Alvarro pointed out in another reply, you'll probably want to include conditions in your where clause for the argument types. Now I'd go the other way and SELECT count(*) FROM If it ain't there you get 0 returned - no errors. In your function you can test 0 and return true else false or you can return the count and test numerically against the return. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql is slow with larger table even it is in RAM
I have a table with 32 lakh record in it. Table size is nearly 700 MB, and my machine had a 1 GB + 256 MB RAM, i had created the table space in RAM, and then created this table in this RAM. So now everything is in RAM, if i do a count(*) on this table it returns 327600 in 3 seconds, why it is taking 3 seconds ? because am sure that no Disk I/O is happening. ( using vmstat i had confirmed, no disk I/O is happening, swap is also not used ) Any Idea on this ??? I searched a lot in newsgroups ... can't find relevant things ( because everywhere they are speaking about disk access speed, here i don't want to worry about disk access ) If required i will give more information on this.
Re: [GENERAL] Install Postgresql on Win2000 with Admin Rights
Stefan Sturm wrote: Hello, is there a Way to install PostgreSQL ( 8.3.1 ) on a Win2000 Maschine without Admin Rights? Yes, but you cannot use the MSI installer, and you cannot install it as a service (for auto-start). You can download the binaries-no-installer and run the initdb and pg_ctl steps yourself (follow the Unix instructions for those parts) //Magnus -- 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] Running function automatically on (unclean) disconnect
Stumo wrote: Each client retrieves an unprocessed record from the server, marks that it's processing that record, does some processing (this will take some time), and adds some extra data to the server based on the processing. [snip] However, if a client disconnects (which, because of the system I am using, is relatively likely) then the record will still be marked as processing even though nothing is processing it. Just create a timeout for processing - if a worker process will fail or disconnect then a job will time out. Choose jobs for processing from jobs in not-processed or timed-out state. It is much more reliable - will work even when backend or server will die or when a worker will hang. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
Anyway, maybe I spoke too soon :-(. ERROR: operator is not unique: integer || unknown I did, of course, not follow the instructions and just blinding applied them all, but from reading them it doesn't look like the issue here. Does this error mean there are too many operators or not enough? Too many. You might have to remove the anynonarray || text and text || anynonarray operators if you're going to continue to rely on implicit casts to text. Thanks for that. Any chance someone could give me more newbie instructions? :-) I suppose you are talking about anytextcat(anynonarray, text) and textanycat(text, anynonarray) But I can't see anywhere obvious where I can deactivate them... I looked for likely suspects in pg_operator, pg_cast... but I'm not really sure what I'm doing. Anyone? Thanks heaps. Anton -- 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] Install Postgresql on Win2000 with Admin Rights
Hello Stefan Sturm wrote: Hello, is there a Way to install PostgreSQL ( 8.3.1 ) on a Win2000 Maschine without Admin Rights? Yes, but you cannot use the MSI installer, and you cannot install it as a service (for auto-start). You can download the binaries-no- installer and run the initdb and pg_ctl steps yourself (follow the Unix instructions for those parts) //Magnus thanks for your answer. I downloaded the binaries and tried to run inidb. But then I get the message, that he could not find libintl3.dll. But it is in this directory... Any Hints? Thanks, Stefan Sturm -- 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] Install Postgresql on Win2000 with Admin Rights
Stefan Sturm wrote: Hello, i copied the dll files to the system32 directory. That's really NOT a good idea. Win2k and up look in the executable directory for required DLLs. You should never need to modify the system directories, and it's a really bad idea to do so. See: http://msdn2.microsoft.com/en-us/library/ms811694.aspx I'm not speaking from PostgreSQL specific experience here, so maybe there's some odd reason why it might be required for the pgsql server. In general, though, it's a terrible idea to go messing with the windows directory for program installs. -- Craig Ringer -- 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] Install Postgresql on Win2000 with Admin Rights
Hello, i copied the dll files to the system32 directory. But now I get another error: The Programm could not be initialized( 0xcc022 ) Any Hints? Stefan Sturm Stefan Sturm wrote: Hello, is there a Way to install PostgreSQL ( 8.3.1 ) on a Win2000 Maschine without Admin Rights? Yes, but you cannot use the MSI installer, and you cannot install it as a service (for auto-start). You can download the binaries-no- installer and run the initdb and pg_ctl steps yourself (follow the Unix instructions for those parts) //Magnus
Re: [GENERAL] postgresql is slow with larger table even it is in RAM
On 2008-03-25 09:51, sathiya psql wrote: Table size is nearly 700 MB, and my machine had a 1 GB + 256 MB RAM, i had created the table space in RAM, and then created this table in this RAM. So now everything is in RAM, if i do a count(*) on this table it returns 327600 in 3 seconds, why it is taking 3 seconds ? PostgreSQL needs to scan the whole table. And 700MB is a lot of data even when in RAM. Check this: $ dd if=/dev/zero bs=1M count=700 | cat /dev/null 734003200 bytes (734 MB) copied, 1.38732 s, 529 MB/s This command will just copy 700MB of zeros around in RAM, and it takes over 1s to do it on my Pentium D 3,4GHz. PostgreSQL would need to do much more with this data. Maybe you just got bitten by count(*) gotcha: http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7 Maybe you'll get by with an estimate. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] Install Postgresql on Win2000 with Admin Rights
Hello, If you can do that, you obviously have admin rights, so why not just use the MSI installer? When I try to run the Intaller, I get this Message: The Systemadminitrator guidelines issued in order to prevent this installation. That said, this often happens if you have an incorrect version of the MSVC runtime on your system. See the README file in the download directory. I can't istall the runtime, because I can't run the MSI installer. The same problem :-( So, I thnk, there is no chance to install prostgres here :-( Greetings, Stefan Sturm //Magnus Stefan Sturm wrote: Hello, i copied the dll files to the system32 directory. But now I get another error: The Programm could not be initialized( 0xcc022 ) Any Hints? Stefan Sturm Stefan Sturm wrote: Hello, is there a Way to install PostgreSQL ( 8.3.1 ) on a Win2000 Maschine without Admin Rights? Yes, but you cannot use the MSI installer, and you cannot install it as a service (for auto-start). You can download the binaries-no- installer and run the initdb and pg_ctl steps yourself (follow the Unix instructions for those parts) //Magnus -- 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] Install Postgresql on Win2000 with Admin Rights
Hello, I know that :-)It was just a try... Greetings, Stefan Sturm Am 25.03.2008 um 11:28 schrieb Craig Ringer: Stefan Sturm wrote: Hello, i copied the dll files to the system32 directory. That's really NOT a good idea. Win2k and up look in the executable directory for required DLLs. You should never need to modify the system directories, and it's a really bad idea to do so. See: http://msdn2.microsoft.com/en-us/library/ms811694.aspx I'm not speaking from PostgreSQL specific experience here, so maybe there's some odd reason why it might be required for the pgsql server. In general, though, it's a terrible idea to go messing with the windows directory for program installs. -- Craig Ringer -- 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] Install Postgresql on Win2000 with Admin Rights
If you can do that, you obviously have admin rights, so why not just use the MSI installer? That said, this often happens if you have an incorrect version of the MSVC runtime on your system. See the README file in the download directory. //Magnus Stefan Sturm wrote: Hello, i copied the dll files to the system32 directory. But now I get another error: The Programm could not be initialized( 0xcc022 ) Any Hints? Stefan Sturm Stefan Sturm wrote: Hello, is there a Way to install PostgreSQL ( 8.3.1 ) on a Win2000 Maschine without Admin Rights? Yes, but you cannot use the MSI installer, and you cannot install it as a service (for auto-start). You can download the binaries-no-installer and run the initdb and pg_ctl steps yourself (follow the Unix instructions for those parts) //Magnus -- 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] Install Postgresql on Win2000 with Admin Rights
Stefan Sturm wrote: Hello, If you can do that, you obviously have admin rights, so why not just use the MSI installer? When I try to run the Intaller, I get this Message: The Systemadminitrator guidelines issued in order to prevent this installation. Oh. So it's not actually admin rights, it's a group policy restriction. That said, this often happens if you have an incorrect version of the MSVC runtime on your system. See the README file in the download directory. I can't istall the runtime, because I can't run the MSI installer. The same problem :-( So, I thnk, there is no chance to install prostgres here :-( Sure you can, it's just harder to do when your domain admins are trying to prevent you from doing it ;) You could try installing the MSI of the runtime on a different machine, then manually copy the files over to the one you need to run it on. It's not really convenient, but you *are* trying to do something your admins don't want you to do after all ;-) //Magnus -- 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] Install Postgresql on Win2000 with Admin Rights
Stefan Sturm wrote: Hello, If you can do that, you obviously have admin rights, so why not just use the MSI installer? When I try to run the Intaller, I get this Message: The Systemadminitrator guidelines issued in order to prevent this installation. Have you upgraded the Windows Installer engine? Do you have domain or local group policy in place that might be blocking msi installations? Have you examined your event log for more information? Have you gone digging with filemon to see what's *really* going on? That said, this often happens if you have an incorrect version of the MSVC runtime on your system. See the README file in the download directory. I can't istall the runtime, because I can't run the MSI installer. The same problem :-( The MSVC runtime linked to by the README: http://www.microsoft.com/downloads/details.aspx?FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647DisplayLang=en is supported for installation on win2k. It'll require admin access to install, though. Since that's no good for you, you might be able to bundle the required vc2008 runtime DLL(s) in the postgresql executable directory. I've done this for my own apps without problems, but it's possible that if Pg is specifically compiled to use WinSxS it might not work. Using Dependency Walker (depends.exe) I can see that my standard (MSI install) 8.3 binaries want MSVCR80.DLL . They're finding it in my WinSxS directory on my Vista box. It's not at all difficult to either pull MSVCR80.DLL from a machine that already has the file or download it off the 'net. If you put it in the pg bin directory it *should* work, though I don't know enough about the details of Windows DLL registration etc to be sure that's safe. In other words: Try dropping a copy of MSVCR80.DLL into the bin directory of the non-MSI postgresql zip file and see how you go. -- Craig Ringer -- 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] postgresql is slow with larger table even it is in RAM
Please stop reposting your questions to multiple groups. Since all your questions are about performance, please stick to the pgsql-performance list. Posting to pgsql-sql is not really appropriate, and in pgsql-admin you're totally off-topic. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] postgresql is slow with larger table even it is in RAM
ok On Tue, Mar 25, 2008 at 5:33 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Please stop reposting your questions to multiple groups. Since all your questions are about performance, please stick to the pgsql-performance list. Posting to pgsql-sql is not really appropriate, and in pgsql-admin you're totally off-topic. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] Install Postgresql on Win2000 with Admin Rights
Hello Craig and Magnus, thanks for your Help. I downloaded MSVCR80.DLL, but still the same problems: When I simply start init_db from within the bin folder I get the Message, that he can't find libintl3.dll. The errormessage shows the PATH, and this path contains the bin Folder... When I copy libintl3.dll and libiconv2.dll to the system32 folder, I get the Error, that the applcattion could not get initilaized. Seams to be an odd problem. Thanks for your Help, Stefan Sturm Am 25.03.2008 um 12:17 schrieb Craig Ringer: Stefan Sturm wrote: Hello, If you can do that, you obviously have admin rights, so why not just use the MSI installer? When I try to run the Intaller, I get this Message: The Systemadminitrator guidelines issued in order to prevent this installation. Have you upgraded the Windows Installer engine? Do you have domain or local group policy in place that might be blocking msi installations? Have you examined your event log for more information? Have you gone digging with filemon to see what's *really* going on? That said, this often happens if you have an incorrect version of the MSVC runtime on your system. See the README file in the download directory. I can't istall the runtime, because I can't run the MSI installer. The same problem :-( The MSVC runtime linked to by the README: http://www.microsoft.com/downloads/details.aspx?FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647DisplayLang=en is supported for installation on win2k. It'll require admin access to install, though. Since that's no good for you, you might be able to bundle the required vc2008 runtime DLL(s) in the postgresql executable directory. I've done this for my own apps without problems, but it's possible that if Pg is specifically compiled to use WinSxS it might not work. Using Dependency Walker (depends.exe) I can see that my standard (MSI install) 8.3 binaries want MSVCR80.DLL . They're finding it in my WinSxS directory on my Vista box. It's not at all difficult to either pull MSVCR80.DLL from a machine that already has the file or download it off the 'net. If you put it in the pg bin directory it *should* work, though I don't know enough about the details of Windows DLL registration etc to be sure that's safe. In other words: Try dropping a copy of MSVCR80.DLL into the bin directory of the non-MSI postgresql zip file and see how you go. -- Craig Ringer -- 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] Install Postgresql on Win2000 with Admin Rights
Stefan Sturm wrote: Hello Craig and Magnus, thanks for your Help. I downloaded MSVCR80.DLL, but still the same problems: When I simply start init_db from within the bin folder I get the Message, that he can't find libintl3.dll. The errormessage shows the PATH, and this path contains the bin Folder... Have you used Dependency Walker (depends.exe) to examine initdb and see what DLLs it's being linked to? You can get Dependency Walker from http://www.dependencywalker.com/ (it's free). Use the show full paths option in the view menu to see the full DLL locations. Have you tried explicitly setting your PATH environment variable to include the postgresql bin directory? By the way, on mailing list discussions it's generally considered polite to respond below the other person's email, chopping off the bits that are not relevant. You should also respond to individual parts of the email (like separate questions) where that makes more sense. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] return value from SQL statement
Hi, i have a 'insert into' statement and i would like to know if it fails or not. i was thinking to check the resturn value from this statement, but how to do it ? thx. -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] return value from SQL statement
Alain Roger wrote: Hi, i have a 'insert into' statement and i would like to know if it fails or not. i was thinking to check the resturn value from this statement, but how to do it ? The answer depends entirely on how you're using the database. ODBC? JDBC? Perl DBD? Python with psycopg? SQL code read by the psql command? Also, by failure, do you mean encountered an error that terminated the transaction or inserted zero rows ? -- Craig Ringer -- 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] Create user trigger?
Dropping the slony list. On Mar 18, 2008, at 5:32 AM, Glyn Astill wrote: We're setting up 3 servers replicating using slony. I was wondering if it'd be possible for me to create a set of triggers that fire whenever a user is created/dropped/modified on one of the servers that goes and performs the same action the other two servers. There is no support for DDL triggers, even though it's frequently requested. Unfortunately, almost every time it's brought up it degrades into a debate about why we can't add triggers to catalog tables; forgetting that there's other ways to do this. Searching the pgsql-hackers archives should be enlightening. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Feature request/suggestion - CREATE SCHEMA LIKE
On Mar 18, 2008, at 7:17 AM, Joris Dobbelsteen wrote: Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE are very usefull but it would be great to have such a feature on the mid-level too. I mean something CREATE SCHEMA LIKE that would copy all the template schema relations, etc... What do you think about it ? Would it be hard to implement ? Is it worth the effort I believe the CREATE DATABASE was because of the way postgresql creates a database. I thought it just copied the template database (but are not completely sure). You are correct. This is why the database used as a template can't have anyone connected to it; if somebody was, we can't get a consistent filesystem-level copy of the database. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Create user trigger?
Decibel! wrote: Dropping the slony list. On Mar 18, 2008, at 5:32 AM, Glyn Astill wrote: We're setting up 3 servers replicating using slony. I was wondering if it'd be possible for me to create a set of triggers that fire whenever a user is created/dropped/modified on one of the servers that goes and performs the same action the other two servers. There is no support for DDL triggers, even though it's frequently requested. Unfortunately, almost every time it's brought up it degrades into a debate about why we can't add triggers to catalog tables; forgetting that there's other ways to do this. Searching the pgsql-hackers archives should be enlightening. We have a potential patch for 8.4 for this. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] select any table
I have some users that need select any table but they should not be superusers. How can this be done? I need a grant select on dbname to rolename. Jon -- 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 any table
Roberts, Jon wrote: I need a grant select on dbname to rolename. This is a FAQ, though it doesn't actually seem to be in the PostgreSQL FAQ. A Google search, either of the mailing list archives or of the web in general, for: postgresql grant all tables should prove informative. http://www.google.com/search?q=postgresql+grant+all+tables http://www.google.com/search?q=postgresql+grant+all+tables+site%3Aarchives.postgresql.org -- Craig Ringer -- 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 any table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 25 Mar 2008 09:54:20 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: I have some users that need select any table but they should not be superusers. How can this be done? I need a grant select on dbname to rolename. You can't do it with a single command. It is easy enough to write a query to grab the tables and grant select on them though. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH6Rc0ATb/zqfZUUQRAuXYAKCjVwoLHZLasWFGUM4JwOa82yohEgCfRyRo nkLbi/vImp7jA+bqZD1o4Jc= =vFAR -END PGP SIGNATURE- -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
Anton Melser [EMAIL PROTECTED] writes: Too many. You might have to remove the anynonarray || text and text || anynonarray operators if you're going to continue to rely on implicit casts to text. Thanks for that. Any chance someone could give me more newbie instructions? You'd have to do something like DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; since there isn't any higher-level command that will let you delete a built-in operator. I recommend practicing on a scratch database ;-) regards, tom lane -- 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 any table
On Tue, 25 Mar 2008 09:54:20 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: I have some users that need select any table but they should not be superusers. How can this be done? I need a grant select on dbname to rolename. You can't do it with a single command. It is easy enough to write a query to grab the tables and grant select on them though. We are adding tables and schemas all of the time and we need to grant auditors read-only access to the database. Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to sort strings containing a dot?
Hello, I want to sort strings containing a dot but by taking care of this dot like any other character. example : Currently, I get this after the sort : co.aaa co.abb co.cab com co.ment com.enta But I would like to get this : co.aaa co.abb co.cab co.ment com com.enta How I can do this? Thank you -- 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 sort strings containing a dot?
create table t (name varchar);
insert into t values ('co.aaa');
insert into t values ('co.abb');
insert into t values ('co.cab');
insert into t values ('com');
insert into t values ('co.ment');
insert into t values ('com.enta');
select name from t order by replace(name, '.', '');
Jon
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pierre LEBRECH
Sent: Tuesday, March 25, 2008 9:56 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to sort strings containing a dot?
Hello,
I want to sort strings containing a dot but by taking care of this dot
like any other character.
example :
Currently, I get this after the sort :
co.aaa
co.abb
co.cab
com
co.ment
com.enta
But I would like to get this :
co.aaa
co.abb
co.cab
co.ment
com
com.enta
How I can do this?
Thank you
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] return value from SQL statement
under pl/pgsql language i would like to return a function value. e.g : 0 is everything is completed -1 if searched data already exist into table -2 if insert into failed ... later i would like to use this returned value in my PHP (this i know how to do) is it clearer ? A. On Tue, Mar 25, 2008 at 3:02 PM, Craig Ringer [EMAIL PROTECTED] wrote: Alain Roger wrote: Hi, i have a 'insert into' statement and i would like to know if it fails or not. i was thinking to check the resturn value from this statement, but how to do it ? The answer depends entirely on how you're using the database. ODBC? JDBC? Perl DBD? Python with psycopg? SQL code read by the psql command? Also, by failure, do you mean encountered an error that terminated the transaction or inserted zero rows ? -- Craig Ringer -- Alain Windows XP SP2 PostgreSQL 8.2.4 / MS SQL server 2005 Apache 2.2.4 PHP 5.2.4 C# 2005-2008
Re: [GENERAL] return value from SQL statement
On Tue, 25 Mar 2008 16:38:04 +0100 Alain Roger [EMAIL PROTECTED] wrote: under pl/pgsql language i would like to return a function value. e.g : 0 is everything is completed -1 if searched data already exist into table -2 if insert into failed ... later i would like to use this returned value in my PHP (this i know how to do) is it clearer ? A. some code snipplet 8---8---8---8---8---8--- create or replace function somefunc(param1 int, param2 int, out res smallint) as begin ... end; 8---8---8---8---8---8--- res:=-1; 8---8---8---8---8---8--- GET DIAGNOSTICS _rowN = ROW_COUNT; give a look to FOUND Co. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS 8---8---8---8---8---8--- $result=pg_query(select res from somefunc()); $row=pg_fetch_array($result); $res=$row['res']; 8---8---8---8---8---8--- give a look to http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING 8---8---8---8---8---8--- you may even be interested in setof Co. and some clarifying examples: http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended still your question look a bit too broad to give you one answer. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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 sort strings containing a dot?
Pierre LEBRECH [EMAIL PROTECTED] writes: I want to sort strings containing a dot but by taking care of this dot like any other character. I suspect what you really want is C locale, and what you've got is some other locale that uses dictionary sort rules. Try show lc_collate. regards, tom lane -- 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] return value from SQL statement
Alain Roger wrote: under pl/pgsql language i would like to return a function value. e.g : 0 is everything is completed -1 if searched data already exist into table -2 if insert into failed ... Given the table: CREATE TABLE testtable ( blah VARCHAR, CONSTRAINT blah_is_unique UNIQUE(blah) ); You could check uniqueness in your query manually (I assume you know how to do that). You could use a subquery on INSERT and use GET DIAGNOSTICS to find out if it did anything: CREATE OR REPLACE FUNCTION insert_and_return(VARCHAR) RETURNS INTEGER AS $$ DECLARE num_rows_inserted INTEGER; BEGIN -- Inserts blah=$1 into testtable only if a row with blah=$1 does not already exists INSERT INTO testtable (blah) SELECT $1 WHERE NOT EXISTS (SELECT 't' FROM testtable WHERE blah = $1); -- Finds out if we did anything GET DIAGNOSTICS num_rows_inserted := ROW_COUNT; RETURN CASE WHEN num_rows_inserted = 0 THEN -1 ELSE 0 END; END; $$ LANGUAGE 'plpgsql'; You could also just try the insert and trap a unique_violation. This is likely to be useful if you have lots of complex referential integrity constraints, CHECK constraints, etc too. Note, however, that EXCEPTION gets really expensive if you're using it tens of thousands of times in a single transaction. CREATE OR REPLACE FUNCTION insert_and_return2(VARCHAR) RETURNS INTEGER AS $$ BEGIN BEGIN INSERT INTO testtable (blah) VALUES ($1); EXCEPTION WHEN unique_violation THEN RETURN -2; -- add more WHEN clauses here, or more exceptions to the WHEN clause, -- for other conditions you want to trap. END; RETURN 0; END; $$ LANGUAGE 'plpgsql'; See: http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Personally it seems like a bit of a funny thing to be doing, though. Aren't you better off performing the INSERT with a WHERE clause that protects against collisions, constraint exclusions, etc, then using your PHP database interface's diagnosics ( cursor.get_row_count() or whatever it is in PHP ) to see whether the query did anything? -- Craig Ringer -- 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 sort strings containing a dot?
Thanks Jon, but it does not give me what I want.
But, this gave me an idea : I replaced the select statement by this one :
select name from t order by replace(name, '.', 'z');
And this time it works. With 'z', I get 'co' before 'com'. If I set an 'a',
then I get the 'com' before 'co'. Cool!
examples :
dns= select name from t order by replace(name, '.', 'z');
name
--
com
com.enta
co.aaa
co.abb
co.cab
co.ment
(6 lines)
dns= select name from t order by replace(name, '.', 'a');
name
--
co.aaa
co.abb
co.cab
co.ment
com
com.enta
(6 lines)
Thank you.
Roberts, Jon wrote :
create table t (name varchar);
insert into t values ('co.aaa');
insert into t values ('co.abb');
insert into t values ('co.cab');
insert into t values ('com');
insert into t values ('co.ment');
insert into t values ('com.enta');
select name from t order by replace(name, '.', '');
Jon
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pierre LEBRECH
Sent: Tuesday, March 25, 2008 9:56 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to sort strings containing a dot?
Hello,
I want to sort strings containing a dot but by taking care of this dot
like any other character.
example :
Currently, I get this after the sort :
co.aaa
co.abb
co.cab
com
co.ment
com.enta
But I would like to get this :
co.aaa
co.abb
co.cab
co.ment
com
com.enta
How I can do this?
Thank you
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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_ctrl stop problems (psql 8.2.5)
Hello, I am running postgresql 8.2.5 on Solaris 10. When I try to shut down postgres using pg_ctrl -D datafile stop it does not shutdown.Does anyone know why this is, or what files pg_ctrl access to determine the shutdown process. We have tried deleting a pgsql semaphore file located in tmp. This did not help. Any help is appreciated. 757-864-7114 LARC/J.L.Shipman/jshipman [EMAIL PROTECTED]
Re: [GENERAL] How to sort strings containing a dot?
By the way, I have just inserted a duplicate. Then I have run the select
statement with distinct and I got an error.
select distinct name from t order by replace(name, '.', 'a');
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
How one can solve this?
Note : If I remove the distinct word, It works and I get this :
select name from t order by replace(name, '.', 'a');
name
--
co.aaa
co.abb
co.cab
co.ment
co.ment
com
com.enta
(7 lines)
Roberts, Jon wrote :
create table t (name varchar);
insert into t values ('co.aaa');
insert into t values ('co.abb');
insert into t values ('co.cab');
insert into t values ('com');
insert into t values ('co.ment');
insert into t values ('com.enta');
select name from t order by replace(name, '.', '');
Jon
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pierre LEBRECH
Sent: Tuesday, March 25, 2008 9:56 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to sort strings containing a dot?
Hello,
I want to sort strings containing a dot but by taking care of this dot
like any other character.
example :
Currently, I get this after the sort :
co.aaa
co.abb
co.cab
com
co.ment
com.enta
But I would like to get this :
co.aaa
co.abb
co.cab
co.ment
com
com.enta
How I can do this?
Thank you
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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 sort strings containing a dot?
By the way, I have just inserted a duplicate. Then I have run the select statement with distinct and I got an error. How one can solve this? Does this work? select distinct name from ( select name from t order by replace(name, '.', 'a')) as t2 -- 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 sort strings containing a dot?
Thanks Adam. No, It doesn't. But I have found a solution which works : select name from (select distinct name from t) as name order by replace(name, '.', 'a'); name -- co.aaa co.abb co.cab co.ment com com.enta (6 lines) Thanks to all of you. Adam Rich wrote : By the way, I have just inserted a duplicate. Then I have run the select statement with distinct and I got an error. How one can solve this? Does this work? select distinct name from ( select name from t order by replace(name, '.', 'a')) as t2 -- 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 sort strings containing a dot?
select name from t group by name order by replace(name, '.', 'a');
I personally don't use distinct very often as group by is usually more
flexible and quicker to add aggregates to the query.
Jon
-Original Message-
From: Pierre LEBRECH [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2008 11:42 AM
To: Roberts, Jon
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to sort strings containing a dot?
By the way, I have just inserted a duplicate. Then I have run the
select
statement with distinct and I got an error.
select distinct name from t order by replace(name, '.', 'a');
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in
select
list
How one can solve this?
Note : If I remove the distinct word, It works and I get this :
select name from t order by replace(name, '.', 'a');
name
--
co.aaa
co.abb
co.cab
co.ment
co.ment
com
com.enta
(7 lines)
Roberts, Jon wrote :
create table t (name varchar);
insert into t values ('co.aaa');
insert into t values ('co.abb');
insert into t values ('co.cab');
insert into t values ('com');
insert into t values ('co.ment');
insert into t values ('com.enta');
select name from t order by replace(name, '.', '');
Jon
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pierre LEBRECH
Sent: Tuesday, March 25, 2008 9:56 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to sort strings containing a dot?
Hello,
I want to sort strings containing a dot but by taking care of this
dot
like any other character.
example :
Currently, I get this after the sort :
co.aaa
co.abb
co.cab
com
co.ment
com.enta
But I would like to get this :
co.aaa
co.abb
co.cab
co.ment
com
com.enta
How I can do this?
Thank you
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] pg_ctrl stop problems (psql 8.2.5)
On Mar 25, 2008, at 11:49 AM, LARC/J.L.Shipman/jshipman wrote: Hello, I am running postgresql 8.2.5 on Solaris 10. When I try to shut down postgres using pg_ctrl -D datafile stop it does not shutdown.Does anyone know why this is, or what files pg_ctrl access to determine the shutdown process. We have tried deleting a pgsql semaphore file located in tmp. This did not help. Any help is appreciated. Without more information it's hard to do more than guess. First, I'll assume that datafile is actually your data directory. Past that, I'd guess that you still have open connections on your database. pg_ctl accepts a -m flag for the shutdown mode which can be one of 'smart', 'fast', or 'immediate' with smart being the default which will wait for all existing connections to close before actually shutting down the server. If you want to cut those connections off, use fast mode, which will roll back any open transactions and close any existing connections then shut down. Don't use immediate unless you really know what you're doing as it's the equivalent of sending kill -9 to the postmaster. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.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] pg_ctrl stop problems (psql 8.2.5)
LARC/J.L.Shipman/jshipman napsal(a): Hello, I am running postgresql 8.2.5 on Solaris 10. When I try to shut down postgres using pg_ctrl -D datafile stop it does not shutdown.Does anyone know why this is, or what files pg_ctrl access to determine the shutdown process. We have tried deleting a pgsql semaphore file located in tmp. This did not help. Any help is appreciated. Solaris uses SMF for starting and stopping postgreSQL. If postgres was started by SMF then it tries to restart when it fails (or stopeed another way). try svcs postgresql it shows you status. svcs disable postgresql:version_82 stop postgresql. See man postgres_82 for detail. Zdenek -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
You'd have to do something like DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; since there isn't any higher-level command that will let you delete a built-in operator. I recommend practicing on a scratch database ;-) Thanks for the tip, though alas that didn't seem to fix it... select 1 || '/' ERROR: operator is not unique: integer || unknown LINE 1: select 1 || '/' ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. and even select 1 || '/'::text ERROR: operator is not unique: integer || text LINE 1: select 1 || '/'::text ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. Am I in between a rock and a hard place here? Thanks again, Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
Anton Melser [EMAIL PROTECTED] writes: You'd have to do something like DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; since there isn't any higher-level command that will let you delete a built-in operator. I recommend practicing on a scratch database ;-) Thanks for the tip, though alas that didn't seem to fix it... Did you remove the other one too? regards, tom lane -- 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 any table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 25 Mar 2008 10:16:19 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: On Tue, 25 Mar 2008 09:54:20 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: I have some users that need select any table but they should not be superusers. How can this be done? I need a grant select on dbname to rolename. You can't do it with a single command. It is easy enough to write a query to grab the tables and grant select on them though. We are adding tables and schemas all of the time and we need to grant auditors read-only access to the database. O.k. :) but that doesn't change my response. You can't do it with a single command. You can script it. http://pgedit.com/public/sql/acl_admin/index.html http://unf.be/postgresql/postgres_grant_all.perl http://www.archonet.com/pgdocs/grant-all.html There are some links that may help you. Sincerely, Joshua D. Drake Jon - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH6T4TATb/zqfZUUQRAseZAJ4zR3NhBoNbwrbjNaTIdLR0UFOuhwCbBT3D NgEqYHc68KKhWnks5g8vrW0= =7R0w -END PGP SIGNATURE- -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
On 25/03/2008, Tom Lane [EMAIL PROTECTED] wrote: Anton Melser [EMAIL PROTECTED] writes: You'd have to do something like DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; since there isn't any higher-level command that will let you delete a built-in operator. I recommend practicing on a scratch database ;-) Thanks for the tip, though alas that didn't seem to fix it... Did you remove the other one too? Actually, I hadn't even properly deleted the first one (don't know where I did delete it, but it wasn't in the right place!) :-(. This is not my day! The app appears to be working again now. I won't bother you again with this - promised! Thanks a million. Cheers Anton ps for reference... DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; DELETE FROM pg_operator WHERE oprcode = 'textanycat'::regproc; -- 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 sort strings containing a dot?
Pierre LEBRECH wrote: But I have found a solution which works : select name from (select distinct name from t) as name order by replace(name, '.', 'a'); Shouldn't you replace '.' with a character whose collating sequence is strictly less than 'a'? This solution looks to me as though it might not get ,e.g., 'coast' and 'co.st' in the desired order except by chance. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL, clusters and load-balance
When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? Does this mean that if I were to create copies of postgresql.exe beforehand and somehow split traffic to them, traffic could be handled better? Also, if I install postgresql on multiple boxes, how can I load-balance- configure traffic to be split depending on load, at application or webserver level? All this for mostly read-only data. http://www.postgresql.org/docs/8.3/interactive/high-availability.html Cheers, Bill
Re: [GENERAL] PostgreSQL, clusters and load-balance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 25 Mar 2008 14:16:37 -0400 Bill Wordsworth [EMAIL PROTECTED] wrote: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? Does this mean that if I were to create copies of postgresql.exe beforehand and somehow split traffic to them, traffic could be handled better? Ehh no :). Each of the postgresql.exe is a forked connection to a specific database within PostgreSQL. They may be one database (if you only have one) or many. You could benefit from connection pooling but I have no idea if we have a connection pooler that runs on Windows. Also, if I install postgresql on multiple boxes, how can I load-balance- configure traffic to be split depending on load, at application or webserver level? That is a *long* conversation. It entirely depends on your business requirements. All this for mostly read-only data. http://www.postgresql.org/docs/8.3/interactive/high-availability.html That is correct, you can not have multiple write nodes. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH6UMGATb/zqfZUUQRAoT0AJ44/gXekfNVfcjl6FhiO03GnqOj9gCgqYRy cmWK72yX/YHFJMqt/6RIZNY= =kM04 -END PGP SIGNATURE- -- 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] PostgreSQL, clusters and load-balance
Bill Wordsworth wrote on 25.03.2008 19:16: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? No, absolutely not. Each client request is handled by a single postgres process which is spawned by the postmaster upon connection. It has nothing to do with instances or high-availibility Thomas -- 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 any table
On Tue, 25 Mar 2008 09:54:20 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: I have some users that need select any table but they should not be superusers. How can this be done? I need a grant select on dbname to rolename. You can't do it with a single command. It is easy enough to write a query to grab the tables and grant select on them though. We are adding tables and schemas all of the time and we need to grant auditors read-only access to the database. O.k. :) but that doesn't change my response. You can't do it with a single command. You can script it. It would be a nice enhancement to have a select any table privilege or at least grant insert/update/delete/select on schema_name. Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Converting mysql on update to postgres rule
Thanks to everyone who's helped me before. I'm trying to create the following mysql table in postgres: CREATE TABLE visit ( data1 varchar(30) NOT NULL, data2 varchar(30) DEFAULT NULL, data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Thus far, I've been able to get this far: CREATE TABLE visit ( data1 varchar(30) NOT NULL, data2 varchar(30) DEFAULT NULL, data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP); CREATE RULE timestamp_update AS ON UPDATE TO visit DO ALSO UPDATE vist SET data3 = CURRENT_TIMESTAMP; I've tried multiple iterations of how to accomplish this and keep getting stuck. With the one above, postgres yells and says: ERROR: infinite recursion detected in rules for relation visit Any ideas?
Re: [GENERAL] select any table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 25 Mar 2008 13:37:37 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: O.k. :) but that doesn't change my response. You can't do it with a single command. You can script it. It would be a nice enhancement to have a select any table privilege or at least grant insert/update/delete/select on schema_name. Certainly, but it is also a foot gun. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH6UptATb/zqfZUUQRAmhQAJ0bzU5LCF0GU4r3SgihoE22xuqedQCdEOcB w3Im5SsA8Y1I4NZNcx1XNjc= =pOZY -END PGP SIGNATURE- -- 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] Converting mysql on update to postgres rule
Edward Blake escribió: Thanks to everyone who's helped me before. I'm trying to create the following mysql table in postgres: CREATE TABLE visit ( data1 varchar(30) NOT NULL, data2 varchar(30) DEFAULT NULL, data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Thus far, I've been able to get this far: CREATE TABLE visit ( data1 varchar(30) NOT NULL, data2 varchar(30) DEFAULT NULL, data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP); CREATE RULE timestamp_update AS ON UPDATE TO visit DO ALSO UPDATE vist SET data3 = CURRENT_TIMESTAMP; You are in a loop... Use a trigger that put NEW.data3 - CURRENT_TIMESTAMP I've tried multiple iterations of how to accomplish this and keep getting stuck. With the one above, postgres yells and says: ERROR: infinite recursion detected in rules for relation visit Any ideas? smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Converting mysql on update to postgres rule
On Tue, Mar 25, 2008 at 02:51:05PM -0400, Edward Blake wrote: CREATE RULE timestamp_update AS ON UPDATE TO visit Do this with a trigger instead of a rule. I think there's an example in the docs of something very similar, but I haven't checked just now. A -- 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 any table
On 25/03/2008 14:54, Roberts, Jon wrote: I have some users that need select any table but they should not be superusers. How can this be done? I need a grant select on dbname to rolename. PgAdmin (www.pgadmin.org) has a handy Grant Wizard which will do this for you in one go. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- -- 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] Converting mysql on update to postgres rule
You're looking for a trigger, not a rule. On Tue, 25 Mar 2008, Edward Blake wrote: Thanks to everyone who's helped me before. I'm trying to create the following mysql table in postgres: CREATE TABLE visit ( data1 varchar(30) NOT NULL, data2 varchar(30) DEFAULT NULL, data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Thus far, I've been able to get this far: CREATE TABLE visit ( data1 varchar(30) NOT NULL, data2 varchar(30) DEFAULT NULL, data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP); CREATE RULE timestamp_update AS ON UPDATE TO visit DO ALSO UPDATE vist SET data3 = CURRENT_TIMESTAMP; I've tried multiple iterations of how to accomplish this and keep getting stuck. With the one above, postgres yells and says: ERROR: infinite recursion detected in rules for relation visit Any ideas? -- 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] Converting mysql on update to postgres rule
snip | I've tried multiple iterations of how to accomplish this and keep getting stuck. With the one above, postgres yells and says: | ERROR: infinite recursion detected in rules for relation visit | | Any ideas? /snip Look at http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Replication with read-only access to standby DB
Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won¹t work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: ³Oracle Active Data Guard enables a physical standby database to be open for read-only access for reporting, simple or complex queries while changes from the production database are being applied to it. This means any operation that requires up-to-date read-only access can be offloaded to the replica, enhancing and protecting the performance of the production database.² ³All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.² Does anyone know of such a solution for PostgreSQL? Thanks, Keaton
Re: [GENERAL] PostgreSQL, clusters and load-balance
On Tue, Mar 25, 2008 at 2:24 PM, Thomas Kellerer [EMAIL PROTECTED] wrote: Bill Wordsworth wrote on 25.03.2008 19:16: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? No, absolutely not. Each client request is handled by a single postgres process which is spawned by the postmaster upon connection. Thanks Joshua and Thomas. I guess my ignorance is showing :). Anyway, is this spawning being done by postmaster or webserver or both? If postmaster, does an application-level persistent connection request communicate itself directly to the postmaster, and can the postmaster keep track of its spawning? Also, at some crude level, if I were to direct every alternate connection to a different install box of postgresql, won't that help with *some* load-balance? Cheers, Bill
Re: [GENERAL] PostgreSQL Replication with read-only access to standby DB
Keaton Adams wrote: Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won¹t work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: ³Oracle Active Data Guard enables a physical standby database to be open for read-only access for reporting, simple or complex queries while changes from the production database are being applied to it. This means any operation that requires up-to-date read-only access can be offloaded to the replica, enhancing and protecting the performance of the production database.² ³All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.² Does anyone know of such a solution for PostgreSQL? Thanks, Keaton IIRC, it was mentioned previously in one posting that this a TODO for a future version of postgres but not something that's expected soon. Someone please correct me if I'm wrong. -salman -- 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] Need help to migrate pqSQL db 8.0.3 to 8.2.6
Hi Andrej, I am running on Linux, Red Hat 3. I have hard time to use pg_dump command. Can you give me the syntax of pgdump? This is the error I got. [EMAIL PROTECTED] backups]# ls [EMAIL PROTECTED] backups]# pg_dumpall dbfile pg_dumpall: could not connect to database template1: FATAL: role root does not exist [EMAIL PROTECTED] backups]# PGUSER=nssb pg_dumpall dbfile pg_dumpall: query failed: ERROR: permission denied for relation pg_authid pg_dumpall: query was: SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1 Thank you for your help. Tri. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrej Ricnik-Bay Sent: Monday, March 24, 2008 3:58 PM To: Tri Quach Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help to migrate pqSQL db 8.0.3 to 8.2.6 On 25/03/2008, Tri Quach [EMAIL PROTECTED] wrote: Hi All, I installed pqsql 8.2.6 on a new server. I need to migrate the data from pqSQL db 8.0.3 on the old server to 8.2.6 on the new server. Can anyone provide me a document how to migrate? It's part of the package. Read the INSTALL document that comes with postgres, it has an UPGRADE section. In a nutshell you want to use pg_dump (or pg_dumpall, depending on how many databases you have loaded) ... you can either save the files, install the new version and import them, or you can (if you start the newer version on a different port than 5432 and have it use a different base directory) have both versions running at the same time. What OS version are you running PG on? And if you can, use 8.2.7 Thank you for your help. Tri Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general __ This email was scanned by the MessageLabs Security System contracted by the Hawaii Dept Of Education. If you receive suspicious/phish email, forward a copy to [EMAIL PROTECTED] This helps us monitor suspicious/phish email getting thru. You will not receive a response from us, but rest assured the information received will help to build additional protection. For info about this service please visit http://www.messagelabs.com/email __ -- 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] PostgreSQL, clusters and load-balance
Bill Wordsworth escribió: On Tue, Mar 25, 2008 at 2:24 PM, Thomas Kellerer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Bill Wordsworth wrote on 25.03.2008 19:16: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? No, absolutely not. Each client request is handled by a single postgres process which is spawned by the postmaster upon connection. Thanks Joshua and Thomas. I guess my ignorance is showing :). Anyway, is this spawning being done by postmaster or webserver or both? If postmaster, does an application-level persistent connection request communicate itself directly to the postmaster, and can the postmaster keep track of its spawning? Also, at some crude level, if I were to direct every alternate connection to a different install box of postgresql, won't that help with *some* load-balance? Cheers, Bill I dont know the first answer, but maybe you are needing pgpool, check it, I think that is what you are needing... smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PostgreSQL Replication with read-only access to standby DB
On Tue, Mar 25, 2008 at 1:17 PM, salman [EMAIL PROTECTED] wrote: IIRC, it was mentioned previously in one posting that this a TODO for a future version of postgres but not something that's expected soon. Someone please correct me if I'm wrong. This is what I saw on the TODO list: Write-Ahead Log * Allow WAL traffic to be streamed to another server for stand-by replication -- Regards, Richard Broersma Jr. -- 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] PostgreSQL Replication with read-only access to standby DB
But will that stand-by replication provide for a read-only slave? On 3/25/08 2:26 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Mar 25, 2008 at 1:17 PM, salman [EMAIL PROTECTED] wrote: IIRC, it was mentioned previously in one posting that this a TODO for a future version of postgres but not something that's expected soon. Someone please correct me if I'm wrong. This is what I saw on the TODO list: Write-Ahead Log * Allow WAL traffic to be streamed to another server for stand-by replication -- 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] PostgreSQL Replication with read-only access to standby DB
On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: “All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.” Does anyone know of such a solution for PostgreSQL? There has been work in this area, but unfortunately this feature has not been completed yet. Many people would like this feature. I have heard of a strategy used by some PostgreSQL users in which they use something like ZFS (which allows fast filesystem snapshots/clones) to quickly clone the data in a separate area, and then bring up the database on the clone for reporting purposes. This obviously does not work in real time, but it may be useful. It does not require a lot of additional space to do this because of the ZFS copy-on-write implementation. Regards, Jeff Davis -- 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] PostgreSQL Replication with read-only access to standby DB
It is close, but has limitations that will be problematic for our environment, such as: Replicator will not replicate the schema. You must restore your schema to th e slaves from the master before you begin replication. Replicator can only replicate one database. If you have multiple databases y ou can either initialize clusters for each database or move all databases into a si ngle database using schemas/namespaces. It is possible to add and drop columns to replicated tables within Replicato r. This type of change to your table structure will require a full sync and therefore is best done in batch or after hours. Thanks for the reply, Keaton On 3/25/08 2:18 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Mar 25, 2008 at 1:11 PM, Keaton Adams [EMAIL PROTECTED] wrote: Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won't work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: Does anyone know of such a solution for PostgreSQL? I think this does what you want. http://commandprompt.com/products/mammothreplicator/
Re: [GENERAL] Need help to migrate pqSQL db 8.0.3 to 8.2.6
On 26/03/2008, Tri Quach [EMAIL PROTECTED] wrote: Hi Andrej, Hi Tri! I am running on Linux, Red Hat 3. I have hard time to use pg_dump command. Can you give me the syntax of pgdump? Not a syntax-problem; to use pg_dumpall you need to be the postgres (superuser). su - postgres pg_dumpall dbfile If you'd rather run it individually for each DB become the user who owns the respective DB. Thank you for your help. Tri. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- 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] PostgreSQL, clusters and load-balance
Bill Wordsworth wrote on 25.03.2008 21:04: On Tue, Mar 25, 2008 at 2:24 PM, Thomas Kellerer [EMAIL PROTECTED] wrote: Bill Wordsworth wrote on 25.03.2008 19:16: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? No, absolutely not. Each client request is handled by a single postgres process which is spawned by the postmaster upon connection. Thanks Joshua and Thomas. I guess my ignorance is showing :). Anyway, is this spawning being done by postmaster or webserver or both? By the postmaster - for every connection initiated by the client (connection pool in the app server, richt client, ...) Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timezones in 8.2.7
Was there something wrong with the tzdata distributed in 8.2.7? current_timestamp is still using EST, but the date command run from the command line is correctly returning EDT [local]:owl=# select version(), current_timestamp; version | now ---+--- PostgreSQL 8.2.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3) | 2008-03-25 14:33:11.955247-05 (1 row) Time: 0.584 ms [local]:owl=# select now(); now --- 2008-03-25 14:33:24.291317-05 (1 row) -- 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 any table
I'm just curious how would having the ability to grant privileges to a schema be a foot gun? On Tue, Mar 25, 2008 at 2:54 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 25 Mar 2008 13:37:37 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: O.k. :) but that doesn't change my response. You can't do it with a single command. You can script it. It would be a nice enhancement to have a select any table privilege or at least grant insert/update/delete/select on schema_name. Certainly, but it is also a foot gun. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH6UptATb/zqfZUUQRAmhQAJ0bzU5LCF0GU4r3SgihoE22xuqedQCdEOcB w3Im5SsA8Y1I4NZNcx1XNjc= =pOZY -END PGP SIGNATURE- -- 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] PostgreSQL Replication with read-only access to standby DB
On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: “Oracle Active Data Guard enables a physical standby database to be open for read-only access – for reporting, simple or complex queries – while changes from the production database are being applied to it. This means any operation that requires up-to-date read-only access can be offloaded to the replica, enhancing and protecting the performance of the production database.” “All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.” Does anyone know of such a solution for PostgreSQL? Some funding would help that move forwards. If you or others would consider that, it would help, even if just to provide the seed for additional contributors. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- 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] PostgreSQL Replication with read-only access to standby DB
On Tue, Mar 25, 2008 at 3:08 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: Some funding would help that move forwards. If you or others would consider that, it would help, even if just to provide the seed for additional contributors. That is an interesting thought, is it possible to earmark donations for a specific (set of) todo(s)? -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NauckIT PostgreSQL provider working with Mono?
Hello, I was about to start on Forms Authentication with Postgres in .NET on Mono. I found this message from Kevin: http://www.mail-archive.com/[EMAIL PROTECTED]/msg23395.html but didn't find any responses. I was wondering whether anyone got this working, and has any documentation that can help. Thanks
Re: [GENERAL] PostgreSQL Replication with read-only access to standby DB
On Tue, Mar 25, 2008 at 1:11 PM, Keaton Adams [EMAIL PROTECTED] wrote: Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won't work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: Does anyone know of such a solution for PostgreSQL? I think this does what you want. http://commandprompt.com/products/mammothreplicator/ -- Regards, Richard Broersma Jr. -- 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] Timezones in 8.2.7
Joseph S [EMAIL PROTECTED] writes: Was there something wrong with the tzdata distributed in 8.2.7? current_timestamp is still using EST, but the date command run from the command line is correctly returning EDT Works for me ... what have you got TimeZone set to? regards, tom lane -- 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] PostgreSQL Replication with read-only access to standby DB
That is an interesting question. If our organization were to help fund the development of such a feature, would that be something taken into consideration by the development team? -Keaton On 3/25/08 4:32 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Mar 25, 2008 at 3:08 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: Some funding would help that move forwards. If you or others would consider that, it would help, even if just to provide the seed for additional contributors. That is an interesting thought, is it possible to earmark donations for a specific (set of) todo(s)? -- 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] PostgreSQL, clusters and load-balance
Bill Wordsworth wrote: On Tue, Mar 25, 2008 at 2:24 PM, Thomas Kellerer [EMAIL PROTECTED] wrote: Bill Wordsworth wrote on 25.03.2008 19:16: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they similar to Oracle's RAC clusters, except that they are not aware of each other? No, absolutely not. Each client request is handled by a single postgres process which is spawned by the postmaster upon connection. Thanks Joshua and Thomas. I guess my ignorance is showing :). Anyway, is this spawning being done by postmaster or webserver or both? If postmaster, does an application-level persistent connection request communicate itself directly to the postmaster, and can the postmaster keep track of its spawning? In simplified terms - you have one backend postgres process that handles the data storage and caching etc. Then you have one postgres process running for each client connected to the server at any given time. This client process handles all requests to and from the client and talks to the backend process to get the data required for the request. You will have one postgres client connection running for each concurrent db connection required by the web server. With the scripting used for building your web pages - each time you open a connection you start a postgres client process running as you have seen happen. Then when you close the connection the client process for that will finish. If you are using persistant connections - then when you close a connection the web server will keep the client process running and use it again for the next new connection saving time in starting the process up. Also, at some crude level, if I were to direct every alternate connection to a different install box of postgresql, won't that help with *some* load-balance? Cheers, Bill All of these postgres processes will be running on the one machine - this may be the same machine as the web server or a separate one. You can use replication to store the same data on more than one server and use all of them for responding to selects for the web server. Most replication options go for only using one of these servers for updates and the others for selects only. You can then use pooling options such as pgpool (or code it into your scripting if you wish) to distribute your connection requests between these replicated servers. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] PostgreSQL Replication with read-only access to standby DB
Similar case has been already happened. For example, I have propsed to implement WITH RECURSIVE clause and the work is supported by Sumitomo Electric Information Systems Co., Ltd. (http://www.sei-info.co.jp/) and SRA OSS, Inc. Japan (http://www.sraoss.co.jp). -- Tatsuo Ishii SRA OSS, Inc. Japan That is an interesting question. If our organization were to help fund the development of such a feature, would that be something taken into consideration by the development team? -Keaton On 3/25/08 4:32 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Mar 25, 2008 at 3:08 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: Some funding would help that move forwards. If you or others would consider that, it would help, even if just to provide the seed for additional contributors. That is an interesting thought, is it possible to earmark donations for a specific (set of) todo(s)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] table size and storage location
paul rivers-2 wrote:
chuckee wrote:
1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace capture does not exist
You're looking for pg_relation_size('capture') or
pg_total_relation_size('capture'). A tablespace is a named location for
creating objects.
Thanks but I still get the error 'ERROR: relation capture does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!
--
View this message in context:
http://www.nabble.com/table-size-and-storage-location-tp16230697p16249922.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Make MS Access UPDATE PostGre SQL Table
Hi, I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. However, I am now in need of being able to update a PostGre table. The source will be a .csv file. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that. I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure. Thoughts on how to do the entire front end in Access. Or Thoughts on whether to do it from a .net perspective. MSAccess ( 2003 ) PostGreSQL ( 8.1.8 ) Regards, Barry Pettis CSO Atmel Corp
[GENERAL] How to use database?
Hi, How to use a database I have just created in a script that I am executing in Pgadmin3 on Windows? I can not use USE Northwind; or \connect Northwind;? Cheers, Anton -- 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 use database?
On 25/03/2008, Anton Andreev [EMAIL PROTECTED] wrote: Hi, Hi, How to use a database I have just created in a script that I am executing in Pgadmin3 on Windows? I can not use USE Northwind; or \connect Northwind;? Hard to say w/o knowing the script. Does it just create tables, or does it supposedly create a database as well? Cheers, Anton Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- 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] Make MS Access UPDATE PostGre SQL Table
Barry, Per the COPY documentation: *COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). * ...So it's not destructive. If you simply need to append the data in the CSV to a table, then COPY might be all you need: http://www.postgresql.org/docs/8.3/static/sql-copy.html For updates, one strategy might be to use a staging table which you truncate, COPY FROM the file to the staging table, and then use a stored procedure to update your main table from there. I haven't benchmarked this, but I would be shocked if this didn't yield superior performance to a solution which implements the update logic in the front-end. Good luck, Dave On Mon, Mar 24, 2008 at 9:08 AM, Pettis, Barry [EMAIL PROTECTED] wrote: Hi, I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. However, I am now in need of being able to update a PostGre table. The source will be a .csv file. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that. I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure. Thoughts on how to do the entire front end in Access. Or Thoughts on whether to do it from a .net perspective. MSAccess ( 2003 ) PostGreSQL ( 8.1.8 ) Regards, Barry Pettis CSO Atmel Corp
Re: [GENERAL] Make MS Access UPDATE PostGre SQL Table
On Mon, Mar 24, 2008 at 07:08:13AM -0600, Pettis, Barry wrote: I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. If you've linked the tables (i.e. you've got an ODBC data source set up, and not just imported the table definitions and data) then you should be able to write DML queries as normal and MS Access will take care of routing everything correctly (if very sub-optimally at times). I.e. if you've got a table linked as pgtbl, then you can do: UPDATE pgtbl SET value = value + 1 WHERE id 100; in a normal query in Access and it will automatically get turned into an update query hitting whatever table it is in PG. However, I am now in need of being able to update a PostGre table. The source will be a .csv file. That sounds a little confused, you need to get the data into the database before you can do anything like UPDATE it. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that. A COPY in PG will just append the data onto the table you've asked it to. It's exactly the same as running several INSERTs, just a bit faster if you've got thousands of rows. Be aware that Access only knows how to pass through a limited subset of SQL. So if you want to use COPY you're going to need to bypass the linked tables somehow. I tend to use the psql program here, but I suppose you could drive it from DAO or ADO as well. Infact I tend to do most of my development using psql and then move any needed queries into my code in Access after getting them working. Thoughts on how to do the entire front end in Access. I've written GUI code in Access using PG on the backend. It's *much* more robust (and faster) than using Access as the database. I am moving away from using the linked tables feature and tend to use DAO/ADO directly. If you want to allow the users to use the fancy in place table editing then you need to use linked tables though. ADO gives much better error messages, but DAO is needed when you want to set the recordset of anything to be the result of a query. Access is good when you've got lots of other people who already use it and just want to write simple queries and the query builder that Access provides is enough. If neither you or your users are accustomed to Access then I'd probably look elsewhere. Sam -- 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] Make MS Access UPDATE PostGre SQL Table
___ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Pettis, Barry Sent: Monday, March 24, 2008 6:08 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Make MS Access UPDATE PostGre SQL Table Note: Generally, it is best to post in plain text, rather than html. Hi, I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. However, I am now in need of being able to update a PostGre table. The source will be a .csv file. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. Copy is a fast way to insert, that is a bit less flexible than ordinary insert statements: http://www.postgresql.org/docs/8.3/static/sql-copy.html I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that. The COPY FROM command performs an APPEND operation. Do you really want a MERGE statement or something else? Microsoft Access also lacks MERGE. If you want to append, then just perform an insert/select. If you want to merge, then you will need several steps to do it: A. Perform an equal join of the spreadsheet against the PostgreSQL table, updating the PostgreSQL rows B. Perform an equal join of the spreadsheet against the PostgreSQL table, deleting the spreadsheet rows C. Perform an insert into the PostgreSQL table of the remaining spreadsheet rows. Or something along those lines. I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure. Thoughts on how to do the entire front end in Access. 1. Link the PostgreSQL table using an ODBC or OLEDB provider 2. Link the CSV spreadsheet using an ODBC or OLEDB provider or as a Microsoft Excel spreadsheet 3. Query these tables exactly as you would query any other tables. Or Thoughts on whether to do it from a .net perspective. MSAccess ( 2003 ) PostGreSQL ( 8.1.8 ) Regards, Barry Pettis CSO Atmel Corp -- 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] table size and storage location
chuckee wrote:
paul rivers-2 wrote:
chuckee wrote:
1) how do I find out the size, in MB, of a particular table (called
'capture' in this case).
I tried entering the SQL query SELECT (pg_tablespace_size('capture'));
The result was the following:
ERROR: tablespace capture does not exist
You're looking for pg_relation_size('capture') or
pg_total_relation_size('capture'). A tablespace is a named location for
creating objects.
Thanks but I still get the error 'ERROR: relation capture does not exist'
when trying these two alternative functions you mention above. There is
definitely a table called 'capture' in my database!
Is the schema for capture in your search_path? If not, include that in
the function call: function('yourschema.capture'). Otherwise, what
version are you on? I don't know when these functions were added;
perhaps you're stuck doing the math yourself on page counts in pg_class.
Paul
--
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] table size and storage location
On 25/03/2008, chuckee [EMAIL PROTECTED] wrote: Thanks but I still get the error 'ERROR: relation capture does not exist' when trying these two alternative functions you mention above. There is definitely a table called 'capture' in my database! Are you sure you're connected to the right database when running that? -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- 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] table size and storage location
Andrej Ricnik-Bay [EMAIL PROTECTED] writes: On 25/03/2008, chuckee [EMAIL PROTECTED] wrote: Thanks but I still get the error 'ERROR: relation capture does not exist' when trying these two alternative functions you mention above. There is definitely a table called 'capture' in my database! Are you sure you're connected to the right database when running that? Case-sensitivity is the other standard gotcha ... maybe the table is really named Capture or something like that? regards, tom lane -- 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] OLEDB vs ODBC to access postgres
On Mon, Mar 24, 2008 at 10:14 PM, J Ottery [EMAIL PROTECTED] wrote: I am using a grid component (delphi) connected via Postgres ODBC , and via Datasource - ADOTable - ADOConnection to a postgres database. All works well. Tried using the PostgreSQL Native Provider, built a working connection string. It connects OK but I have only read access to the data (in the grid I cannot edit or add any rows). I have also tried several other OLEDB providers but they all exhibit the same behaviour. Why does ODBC work and PostgreSQL Native Provider (or other OLEDB's) not work? Help would be appreciated on this as I have spent a long time researching what to use for my future commercial projects. The oledb driver PostgreSQL doesn't support Delphi (and a lot of other things). My gut feeling is it probably never will...most people who use the ms stack use the odbc or the .net drivers. My personal favorite connection library to Delphi is Zeos...it's a little cranky to set up but extremely fast and powerful. It has a client side sql parser that allows very advanced data binding. merlin -- 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] Make MS Access UPDATE PostGre SQL Table
Pettis, Barry wrote: Hi, I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. However, I am now in need of being able to update a PostGre table. The source will be a .csv file. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that. I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure. Thoughts on how to do the entire front end in Access. Or Thoughts on whether to do it from a .net perspective. Another option is use SQL pass through to append records from ACCESS table into Postgres table that is open in Access. http://support.microsoft.com/kb/303968 http://msdn2.microsoft.com/en-us/library/bb208861.aspx the command would look something like this Insert into PosgresTable (f1, f2, f3,) Select f1, f2, f3 from AccessTable Once you get a handle on SQL pass through in Access that knowledge will make using ADO.NET allot easier. a starter look at ADO.NET http://msdn2.microsoft.com/en-us/library/ms978510.aspx?s=6 Postgres has .net provider http://pgfoundry.org/projects/npgsql
Re: [GENERAL] How to use database?
Anton Andreev wrote: Hi, How to use a database I have just created in a script that I am executing in Pgadmin3 on Windows? If you are in pgAdmin then you don't need the connect command. When you select the db from the side list it connects you and lists the contents of the db. From the query window in pgAdmin (where you can enter any sql statements) which is where I think you are opening your script file, it will have a popup menu that you can choose the db to run the commands on (or the sql script you are opening). I can not use USE Northwind; or \connect Northwind;? I believe you get the USE command from MS SQL Server examples - postgres doesn't have that command. The \connect is a 'shell' command used in psql - the cli based postgres client that comes with postgresql. This would not be recognised in the pgAdmin query window, which would only accept valid SQL statements. I am thinking that if you are writing a script that creates a db then wants to connect to it and create tables etc, you should look at sending it to psql. From a dos prompt you can use something like - psql -U postgres -W -f myscript.sql If you start psql then you can use the psql command \i myscript.sql to run the script. You may need to add full pathnames in these examples. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] PostgreSQL Replication with read-only access to standby DB
I'm in the same boat, looking for master-slave replication for 1 master 2 'standby' read-only servers (one would get promoted to master in case of failure). I recently read about WAL here: http://developer.postgresql.org/pgdocs/postgres/warm-standby.html The standby server is not available for access, since it is continually performing recovery processing. PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby system and then the standby database server. Many such tools exist and are well integrated with other aspects required for successful failover, such as IP address migration. In short there's not much automation magic at the moment and doesn't seem like what you're looking for. Pgpool-II might be the best alternative. I know very little about postgreSQL internals but it would be great if: - WAL files could be applied while the standby server is operational / allow read-only queries - Allow master server to send WAL files to standby servers / * WAL traffic to be streamed to another server - Allow master server to send list of all known standby servers - Allow standby server to check if master server is alive and promote itself as master (would need to ask / make sure other standby servers do not try promote themselves at the same time) Then in my ways, you can use a pool to query the read-only standby servers. As I was writing this out, I thought this would make a great SOC project, but then found it already exists! http://code.google.com/soc/2007/postgres/appinfo.html?csaid=6545828A8197EBC6 Great news, I'd be happy to pitch in any time to help design a solution like this :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Keaton Adams Sent: March 25, 2008 4:29 PM To: Richard Broersma; salman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Replication with read-only access to standby DB But will that stand-by replication provide for a read-only slave? On 3/25/08 2:26 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Mar 25, 2008 at 1:17 PM, salman [EMAIL PROTECTED] wrote: IIRC, it was mentioned previously in one posting that this a TODO for a future version of postgres but not something that's expected soon. Someone please correct me if I'm wrong. This is what I saw on the TODO list: Write-Ahead Log * Allow WAL traffic to be streamed to another server for stand-by replication -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Make MS Access UPDATE PostGre SQL Table
Pettis, Barry wrote: However, I am now in need of being able to update a PostGre table. The source will be a .csv file. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure. Thoughts on how to do the entire front end in Access. From Access I would think you can use the standard Access import commands to import the file just as you would into an Access/SQLServer based table. (I haven't used Access much so don't quote me there) But then if that worked you probably wouldn't be asking here. The other option I would think of is using a script that would copy the file to the server and then run the COPY FROM... command (the file will need to be accessible to the server if your are sending it the command as SQL to execute) The way I would go, is having psql on the client side run it's version of the copy command. (though you may need to install psql on client machines along with the Access runtime) On *nix you would do something like - echo \copy mytable from mylocaldata.csv | psql I'm sure you could do a vb script to do that for you if you wanted to stay in Access. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] PostgreSQL Replication with read-only access to standby DB
On Tue, 25 Mar 2008, Jonathan Bond-Caron wrote: I know very little about postgreSQL internals but it would be great if: - WAL files could be applied while the standby server is operational / allow read-only queries This is the part that requires modifying PostgreSQL, and that progress was made toward by Florian's GSoC project last summer. - Allow master server to send WAL files to standby servers / * WAL traffic to be streamed to another server - Allow master server to send list of all known standby servers - Allow standby server to check if master server is alive and promote itself as master (would need to ask / make sure other standby servers do not try promote themselves at the same time) These parts you could build right now, except that there's not too much value to more than one standby if you're not using them to execute queries against. People who are using the current warm-standby code are already grappling with issues like how to coordinate master/slave failover (including my second favorite acronym, STONITH for shoot the other node in the head). I don't expect handling that sort of thing will ever be integrated into the PostgreSQL database core. What is happening instead is that the appropriate interfaces to allow building higher-level tools are being designed and made available. (My favorite acronym is TLA) -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
