Re: [GENERAL] Sequences/defaults and pg_dump

2006-02-08 Thread Joachim Wieland
On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on a serial column, but we haven't gotten around to enforcing that yet. TODO has: * %Disallow changing default expression of a SERIAL column This should

Re: [GENERAL] Is there a way to limit CPU usage per user

2006-02-08 Thread Richard Huxton
Luki Rustianto wrote: Hi All, Is there a way to limit user's CPU resource specially on SELECT query ? I hava a table with a lot of rows inside, if one sloopy DB users do a SELECT * FROM bigtable then CPU resource will go near 99% and this action will surely affect the other database

Re: [GENERAL] futex lockup?

2006-02-08 Thread Martijn van Oosterhout
On Wed, Feb 08, 2006 at 04:22:30PM +1000, Rob Newton wrote: Hi, I wrote a program using pgsql's ESQL/C. Occasionally it blocks forever on a system call futex(), and I'm wondering if pgsql's libraries use this futex call?? (because my code doesn't use it). futexes are Fast Userspace

Re: [GENERAL] alter superuser...

2006-02-08 Thread Martijn van Oosterhout
On Wed, Feb 08, 2006 at 10:42:41AM +0300, Sergey Karin wrote: Hi, List! I alter user postgres, that was a superuser in my db cluster. And now it is not a superuser. There are no superusers in my cluster. Yes, I am stupid :) But are there any abilities to restore superuser excluding

[GENERAL] loading pg_description ... FATAL: duplicate key violates unique constraint pg_description_o_c_o_index

2006-02-08 Thread surabhi.ahuja
i am trying to install postgresql-8.0.1 i am now trying to do /usr/local/pgsql/bin/initdb -D data/ however this is what i get: The files belonging to this database system will be owned by user "surabi".This user must also own the server process. The database cluster will be initialized with

Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates

2006-02-08 Thread Richard Huxton
surabhi.ahuja wrote: i am trying to install postgresql-8.0.1 Why? The most recent release for 8.0 is 8.0.6 and you're clearly compiling from source, so why have you deliberately chosen an old release? i am now trying to do /usr/local/pgsql/bin/initdb -D data/ I'd specify an absolute path

[GENERAL] Postgre replication/clustering on 7.4 and RHEL AS 4

2006-02-08 Thread Tareq Tajkeh
Hi all, I am trying to cluster/replicate PgSQL 7.4 in Active/Passive archeticture (fail over configuration) but i am lost, any body can give me some guidlines?! Aprreciate your help tajkeh

Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates unique constraint pg_description_o_c_o_index

2006-02-08 Thread surabhi.ahuja
Title: Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates unique constraint pg_description_o_c_o_index actually i had a version existing earlier. guess it was installed from an rpm.. so ther are binaries like postmasterinside /usr/bin itself and after i installed

Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates

2006-02-08 Thread Richard Huxton
surabhi.ahuja wrote: actually i had a version existing earlier. guess it was installed from an rpm.. so ther are binaries like postmaster inside /usr/bin itself and after i installed /8.0.1, ther are binaries even in /usr/local/pgsql/bin. how should i uninstall the whole thing and begin a

[GENERAL] Problem with sequence table

2006-02-08 Thread David Goodenough
I have a DB (PostgreSQL of course) which has in the definition of one of its tables that the default is nextval(public.rr_id_seq'::text). When I look in the sequence I see that the last_value column is 40, but the largest value in the relevant column is 45. I tried using the SQL update command

Re: [GENERAL] Problem with sequence table

2006-02-08 Thread Richard Huxton
David Goodenough wrote: I have a DB (PostgreSQL of course) which has in the definition of one of its tables that the default is nextval(public.rr_id_seq'::text). When I look in the sequence I see that the last_value column is 40, but the largest value in the relevant column is 45. I tried

Re: [GENERAL] Sequences/defaults and pg_dump

2006-02-08 Thread Bruce Momjian
Joachim Wieland wrote: On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on a serial column, but we haven't gotten around to enforcing that yet. TODO has: * %Disallow changing default expression of a

[GENERAL] Confirming the autovacuum daemon is running

2006-02-08 Thread Karl O. Pinc
Hi, What is the proper way to confirm that the autovacuum daemon is running? I just turned autovacuuming on and don't notice another process with ps. Thanks. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein

[GENERAL] plruby

2006-02-08 Thread Mage
Hello, is there any planned date when plruby will be officially included to postgresql source (and documentation)? Mage ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

[GENERAL] Problem using ODBC from .NET framework

2006-02-08 Thread Shelby Cain
Hi all. I having an issue with the 8.01.01.02 ODBC driver that is installed via the 8.1.x Windows installer. Whenever there is an error on the connection (eg: trying to execute a sql statement that has a syntax error as an example) the System.Data.OdbcConnection object throws an exception -

Re: [GENERAL] Syncing Databases Weekly

2006-02-08 Thread Vivek Khera
On Feb 7, 2006, at 1:34 PM, Brad Nicholson wrote: Slony's log shipping is another option. But that requires at least one regular replica to exist as well. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] standard normal cumulative distribution function

2006-02-08 Thread Klein Balázs
Thanks for posting this. I looked at - this function, but I work on a Windows so it seemed too difficult for me, - looked at the R language, but installing a new language just for this function seemed like an overkill, - I also looked at the pgnumeric project that has a function for this - but

[GENERAL] wal copies for high availability

2006-02-08 Thread Heinrich Streit
Hi,Our IT budget is not so much and even so I´m trying to set up a Postgresql high availability solution for our business. My managers gave me the following statements that I must follow: . the system could be out of service no more than 2 hours. last 5 minutes of work could be lostThe

Re: [GENERAL] B-tree performance improvements in 8.x

2006-02-08 Thread Dick Kniep
On Wednesday 08 February 2006 06:18, Tom Lane wrote: Dick Kniep [EMAIL PROTECTED] writes: Does this also affect if you have many NULL values in the key? So testing Not is NULL would also be affected? IS NOT NULL isn't an indexable operation, so your question doesn't really apply :-( Does

[GENERAL] Create a new database from JDBC?

2006-02-08 Thread Dan Armbrust
How do I make a connection to a Postgresql database that (potentially) has 0 databases from jdbc, so that I can issue a create database command? In that _other_ database, I can make a jdbc connection to an address like this: jdbc:mysql://servername/ And then issue a Create database foo

Re: [GENERAL] Create a new database from JDBC?

2006-02-08 Thread Joshua D. Drake
Dan Armbrust wrote: How do I make a connection to a Postgresql database that (potentially) has 0 databases from jdbc, so that I can issue a create database command? In that _other_ database, I can make a jdbc connection to an address like this: jdbc:mysql://servername/ And then issue a

Re: [GENERAL] plruby

2006-02-08 Thread Michael Fuhr
On Wed, Feb 08, 2006 at 04:02:19PM +0100, Mage wrote: is there any planned date when plruby will be officially included to postgresql source (and documentation)? The topic was discussed about six months ago but the discussion moved away from PL/Ruby.

Re: [GENERAL] B-tree performance improvements in 8.x

2006-02-08 Thread Greg Stark
Dick Kniep [EMAIL PROTECTED] writes: Does this mean that if you have a table that has many rows, and 95% of the rows contain a NULL value for a field, that indexing will be useless, because it will always do a tablescan? Any time you have 95% of the rows of the table with the same value

Re: [GENERAL] Why pg_hba not in table?

2006-02-08 Thread Philippe Ferreira
And you can change pg_hba.conf on the fly, so you don't have to restart a 24/7 database because you locked the superuser out. If your back were against the wall, you could probably hand-edit the flat-file version of the permission file enough to let yourself in without shutting down the

Re: [GENERAL] Sequences/defaults and pg_dump

2006-02-08 Thread Joachim Wieland
On Wed, Feb 08, 2006 at 09:03:54AM -0500, Bruce Momjian wrote: Joachim Wieland wrote: On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on a serial column, but we haven't gotten around to enforcing that yet.

Re: [GENERAL] wal copies for high availability

2006-02-08 Thread Philippe Ferreira
Hi, Another cheap alternative is diarly (00:00) stop the master database and make a copy of the $PGDATA directory to the slave and during the day make regularly (5 x 5 minutes) copies of the current wal file to the slave too. To recover the system we need only to apply all the logs created

Re: [GENERAL] wal copies for high availability

2006-02-08 Thread Heinrich Streit
Slony is a good choice... but the ERP owner will charge us a lot to include primary keys or unique not null columns all over the system.Philippe Ferreira [EMAIL PROTECTED] escreveu: Hi, Another cheap alternative is diarly (00:00) stop the master database and make a copy of the $PGDATA

Re: [GENERAL] wal copies for high availability

2006-02-08 Thread Rick Gigger
Hi, Our IT budget is not so much and even so I´m trying to set up a Postgresql high availability solution for our business. My managers gave me the following statements that I must follow: . the system could be out of service no more than 2 hours . last 5 minutes of work could be lost The

Re: [GENERAL] B-tree performance improvements in 8.x

2006-02-08 Thread Martijn van Oosterhout
On Wed, Feb 08, 2006 at 06:02:08PM +0100, Dick Kniep wrote: On Wednesday 08 February 2006 06:18, Tom Lane wrote: Dick Kniep [EMAIL PROTECTED] writes: Does this also affect if you have many NULL values in the key? So testing Not is NULL would also be affected? IS NOT NULL isn't an

Re: [GENERAL] Create a new database from JDBC?

2006-02-08 Thread Dave Page
On 8/2/06 17:26, Joshua D. Drake [EMAIL PROTECTED] wrote: Certainly not what I want, since that database doesn't exist. Is there a system database I could always count on being available that I could connect to? template1 but only if you allow it from pg_hba.conf. The postgres database

Re: [GENERAL] Is there a way to limit CPU usage per user

2006-02-08 Thread Merlin Moncure
Is there a way to limit user's CPU resource specially on SELECT query ? I hava a table with a lot of rows inside, if one sloopy DB users do a SELECT * FROM bigtable then CPU resource will go near 99% and this action will surely affect the other database performance ... Yes, the answer is

[GENERAL] Insert into other database

2006-02-08 Thread Rodolfo Campos
Hi Fellows, I'm wondering if in postgres we can insert a tuple into a table that is outside of a database (I'm supossed to be connected to database1 and want to insert a register into a table in database2). This question is because I want to update a tables into one database from a trigger

Re: [GENERAL] Insert into other database

2006-02-08 Thread Michael Fuhr
On Wed, Feb 08, 2006 at 05:59:19PM -0400, Rodolfo Campos wrote: I'm wondering if in postgres we can insert a tuple into a table that is outside of a database (I'm supossed to be connected to database1 and want to insert a register into a table in database2). You can do this with

Re: [GENERAL] Insert into other database

2006-02-08 Thread James Harper
Hi Fellows, I'm wondering if in postgres we can insert a tuple into a table that is outside of a database (I'm supossed to be connected to database1 and want to insert a register into a table in database2). This question is because I want to update a tables into one database from a

[GENERAL] owner as namespace?

2006-02-08 Thread James Harper
Under Microsoft SQL, a fully qualified table name is db.owner.tablename (well actually, linked_server.db.owner.table, but we won't go there :) Under Postgres, you can create schemas to simulate different databases within the one actual database, but as far as I can tell, the owner does not

Re: [GENERAL] owner as namespace?

2006-02-08 Thread Martijn van Oosterhout
On Thu, Feb 09, 2006 at 09:37:35AM +1100, James Harper wrote: Under Microsoft SQL, a fully qualified table name is db.owner.tablename (well actually, linked_server.db.owner.table, but we won't go there :) Under Postgres, you can create schemas to simulate different databases within the one

Re: [GENERAL] Insert into other database

2006-02-08 Thread Michael Fuhr
[Please copy the mailing list on replies.] On Wed, Feb 08, 2006 at 06:57:11PM -0400, Rodolfo Campos wrote: When I tried to connect using libpq I got errors too. But this time the error is trying to register the function in postgresql, the RDBMS tells me that the function PQconnectdb is

Re: [GENERAL] Insert into other database

2006-02-08 Thread Rodolfo Campos
Thanks Michael, I'll check it out. I forgot to say to you that I'm using postgresql 7.4 that's why I can't use pl/perl. Greetings, Rodolfo. On 2/8/06, Michael Fuhr [EMAIL PROTECTED] wrote: [Please copy the mailing list on replies.]On Wed, Feb 08, 2006 at 06:57:11PM -0400, Rodolfo Campos wrote:

Re: [GENERAL] owner as namespace?

2006-02-08 Thread Tom Lane
Martijn van Oosterhout kleptog@svana.org writes: IIRC, the idea of a schema came from Oracle where the schema is the username. If you create a schema with the same name as the user what you describe above works. ie mydb.james.myview is in the james schema in the mydb database. Actually that

Re: [GENERAL] Confirming the autovacuum daemon is running

2006-02-08 Thread Tom Lane
Karl O. Pinc [EMAIL PROTECTED] writes: What is the proper way to confirm that the autovacuum daemon is running? I just turned autovacuuming on and don't notice another process with ps. The autovac process doesn't run continuously, so you wouldn't necessarily see it in ps. (The postmaster

Re: [GENERAL] owner as namespace?

2006-02-08 Thread James Harper
Martijn van Oosterhout kleptog@svana.org writes: IIRC, the idea of a schema came from Oracle where the schema is the username. If you create a schema with the same name as the user what you describe above works. ie mydb.james.myview is in the james schema in the mydb database.

Re: [GENERAL] Confirming the autovacuum daemon is running

2006-02-08 Thread Karl O. Pinc
On 02/08/2006 06:06:25 PM, Tom Lane wrote: Karl O. Pinc [EMAIL PROTECTED] writes: What is the proper way to confirm that the autovacuum daemon is running? The autovac process doesn't run continuously, so you wouldn't necessarily see it in ps. Thanks. I suppose then if I really wanted to I

[GENERAL] Request to have VACUUM ignore cost based limits

2006-02-08 Thread Karl O. Pinc
Hi, Just had a situation where a database was reloaded and needed to be vacuum analyzed before it could be used. I believe the cost-based vacuum delay slowed this down considerably. (I could be wrong, but there was darn little load on the system...) It would have been nice to have an option to

Re: [GENERAL] Insert into other database

2006-02-08 Thread Rodolfo Campos
Fellows, I've restarted postgres after installing postgresql-dev package on my sarge debian box and that's it, I can connect to other database using libpq. Here is my code for anyone interested in this issue. I compiled the source code using: gcc -fpic -shared -lpq

[GENERAL] What's faster?

2006-02-08 Thread Silas Justiniano
Hello all! I'm performing a query that returns me hundreds of records... but I need cut them in pages that have 15 items! (using PHP) So, is it faster: select blablabal from _complex_query if (count($result) 15) show_pages; show_only_15_rows($result); or: select count(*) from

Re: [GENERAL] Request to have VACUUM ignore cost based limits

2006-02-08 Thread Tom Lane
Karl O. Pinc [EMAIL PROTECTED] writes: It would have been nice to have an option to SQL's VACUUM that would ignore the cost-based delays so as to bring that database back to life as rapidly as possible. (Likewise the vacuumdb shell command.) What's wrong with SET?

Re: [GENERAL] What's faster?

2006-02-08 Thread Chris
Hi, Second option. For the first option, if your query returns say 10,000 rows then php has to actually fetch 10,000 rows from the database before anything else happens even though you're only displaying 15. Silas Justiniano wrote: Hello all! I'm performing a query that returns me

Re: [GENERAL] Sequences/defaults and pg_dump

2006-02-08 Thread Bruce Momjian
Joachim Wieland wrote: On Wed, Feb 08, 2006 at 09:03:54AM -0500, Bruce Momjian wrote: Joachim Wieland wrote: On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote: The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on a serial column, but we haven't gotten

Re: [GENERAL] Request to have VACUUM ignore cost based limits

2006-02-08 Thread Karl O. Pinc
On 02/08/2006 09:46:46 PM, Tom Lane wrote: Karl O. Pinc [EMAIL PROTECTED] writes: It would have been nice to have an option to SQL's VACUUM that would ignore the cost-based delays so as to bring that database back to life as rapidly as possible. (Likewise the vacuumdb shell command.)

Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates unique constraint pg_description_o_c_o_index

2006-02-08 Thread surabhi.ahuja
Title: Re: [GENERAL] loading pg_description ... FATAL: duplicate key violates unique constraint pg_description_o_c_o_index thank u so much for the help. ok i ll tell u how i arrived at this problem. this i was using just for testing purpose on my local m/c i had deletedrows in a table,

[GENERAL] r trim of characters other than space

2006-02-08 Thread surabhi.ahuja
Hi hi i have varchar type fiels in a table. i want to make the following check, if it is having carets in the end, then those carets be removed. so if i get a string like abc def i should be able to get abc def how should i do this. thanks, regards Surabhi

Re: [GENERAL] r trim of characters other than space

2006-02-08 Thread A. Kretschmer
am 09.02.2006, um 11:54:43 +0530 mailte surabhi.ahuja folgendes: Hi hi i have varchar type fiels in a table. i want to make the following check, if it is having carets in the end, then those carets be removed. so if i get a string like abc def i should be able to get abc

Re: [GENERAL] r trim of characters other than space

2006-02-08 Thread Tom Lane
surabhi.ahuja [EMAIL PROTECTED] writes: i want to make the following check, if it is having carets in the end, then those carets be removed. so if i get a string like abc def i should be able to get abc def Per SQL spec: regression=# select trim(trailing '^' from 'abc def'); rtrim