Re: [GENERAL] young guy wanting (Postgres DBA) ammo

On 11/1/07, Kevin Hunter <[EMAIL PROTECTED]> wrote: > Hiya list, > > A friend recently told me that, among other things, the institutions for > which he works tend to choose MySQL or MSSQL over Postgres because the > latter requires a dedicated DBA while the former do not. When they do > spring fo

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

Kevin Hunter wrote: At 12:35a -0400 on 02 Nov 2007, Tom Lane wrote: Kevin Hunter <[EMAIL PROTECTED]> writes: However, I'm not a DBA and only minimally know what's involved in doing the job, so I don't have "ammo" to defend (or agree?) with my friend when he says that "Postgres requires a DBA an

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

Kevin Hunter <[EMAIL PROTECTED]> writes: > At 12:35a -0400 on 02 Nov 2007, Tom Lane wrote: >> He's full of it ... mysql is not any easier to run or tune. > I expected as much, but would you give me something more than "Because > Tom says so!" Good enough for me, but not for a > non-Postgres-indoc

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

At 12:35a -0400 on 02 Nov 2007, Tom Lane wrote: > Kevin Hunter <[EMAIL PROTECTED]> writes: >> However, I'm not a DBA and only minimally know what's involved in doing >> the job, so I don't have "ammo" to defend (or agree?) with my friend >> when he says that "Postgres requires a DBA and MySQL doesn

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

Kevin Hunter <[EMAIL PROTECTED]> writes: > However, I'm not a DBA and only minimally know what's involved in doing > the job, so I don't have "ammo" to defend (or agree?) with my friend > when he says that "Postgres requires a DBA and MySQL doesn't so that's > why they choose the latter. He's full

[GENERAL] young guy wanting (Postgres DBA) ammo

Hiya list, A friend recently told me that, among other things, the institutions for which he works tend to choose MySQL or MSSQL over Postgres because the latter requires a dedicated DBA while the former do not. When they do spring for a DBA, they go with Oracle. As a developer/end-user in/of th

Re: [GENERAL] Improve Search

On 11/1/07, carter ck <[EMAIL PROTECTED]> wrote: > > Hi all, > > Has anyone come across a solution for faster and powerful search stored > procedure in a table that contains more than 500K of records? > > I am currently trying to work around with it. All helps are appreciated. Are you using the t

Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

On Thu, 2007-11-01 at 21:22 -0400, Tom Lane wrote: > Ow Mun Heng <[EMAIL PROTECTED]> writes: > > OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that > > I presume. > > Probably a lot more, and it'll bloat your indexes while it's at it. > Do you have a *reason* to run a vacuum

Re: [GENERAL] getting list of tables from command line

On Thu, 2007-11-01 at 20:25 +0100, hubert depesz lubaczewski wrote: > On Wed, Oct 31, 2007 at 08:01:41AM -0700, Craig White wrote: > > my script looks like this... > > (all I want is to get a list of the tables into a text file pg_tables) > > everybody else showed some ways, but i'll ask a questio

[GENERAL] Ignore just testing

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Testing some changes on our end, please ignore - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/

[GENERAL] Improve Search

Hi all, Has anyone come across a solution for faster and powerful search stored procedure in a table that contains more than 500K of records? I am currently trying to work around with it. All helps are appreciated. Thanks. _ Exp

[GENERAL] XML database

I have a large set of XML files (representing about 18M records) that I would like to load into postgres. I have been loading the records into relational tables. Is this the best way to go? I am particularly interested in full-text searches of a subset of the elements. I am on 8.3Beta. Th

Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

Ow Mun Heng <[EMAIL PROTECTED]> writes: > OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that > I presume. Probably a lot more, and it'll bloat your indexes while it's at it. Do you have a *reason* to run a vacuum full? I'd suggest using contrib/pgstattuple to get a fix on how

Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

On Thu, 2007-11-01 at 20:56 -0400, Bill Moran wrote: > Ow Mun Heng <[EMAIL PROTECTED]> wrote: > > > > I just ran a vacuum verbose on the entire DB and this came out. > > > > number of page slots needed (274144) exceeds max_fsm_pages (153600) > > > > Hence, I've changed the max to 400,000 (pulle

Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

Ow Mun Heng <[EMAIL PROTECTED]> wrote: > > I just ran a vacuum verbose on the entire DB and this came out. > > number of page slots needed (274144) exceeds max_fsm_pages (153600) > > Hence, I've changed the max to 400,000 (pulled it straight out of the > air). How does one calculate what's the n

[GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

I just ran a vacuum verbose on the entire DB and this came out. number of page slots needed (274144) exceeds max_fsm_pages (153600) Hence, I've changed the max to 400,000 (pulled it straight out of the air). How does one calculate what's the number needed anyway? Another question is, based on w

Re: [GENERAL] posgresql-8.2 startup problem

novice <[EMAIL PROTECTED]> writes: > I just installed postgres-8.2 using the command line > apt-get install postgres-8.2 and it seems the installation went ok, > but when it tries to start postgres , it fails with this error message > below... > (by the way I am running ubuntu fiesty and on an amaz

Re: [GENERAL] Number to Words Conversion

yogesh wrote: > Hello Frnds, > > Is there any way to Convert the Number to its equivalent String > ( Words) > > e.g. 10 to TEN. Urgent require the Answer.. We have cash_words: test=> SELECT cash_words('100'); cash_words

Re: [GENERAL] Number to Words Conversion

yogesh wrote: Hello Frnds, Is there any way to Convert the Number to its equivalent String ( Words) e.g. 10 to TEN. Urgent require the Answer.. (column sizes just an example) CREATE TABLE integer_string ( number SMALLINT NOT NULL, word VARCHAR(24) NOT NULL ); brian ---

Re: [GENERAL] select random order by random

Gregory Stark <[EMAIL PROTECTED]> writes: > So I guess having the parser do this substitution kind of makes sense > if you're thinking about things the way the spec does. It doesn't make > much sense if you're thinking the way Postgres does of having > arbitrary expressions there independent of wha

[GENERAL] Storing float array

HI. I want to store an array of floats size about 50,000. My first Idea was to store them in a column 'float[]', but using JDBC and java it takes minutes to convert the data and store it. My next try was to use a column BYTEA and store the data as 'byte[]' and that worked. Inspecting the data. L

[GENERAL] posgresql-8.2 startup problem

Hi guys, I just installed postgres-8.2 using the command line apt-get install postgres-8.2 and it seems the installation went ok, but when it tries to start postgres , it fails with this error message below... (by the way I am running ubuntu fiesty and on an amazon ec2-image). Any ideas what is wr

[GENERAL] Number to Words Conversion

Hello Frnds, Is there any way to Convert the Number to its equivalent String ( Words) e.g. 10 to TEN. Urgent require the Answer.. Regards, Yogesh Arora ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http:/

[GENERAL] Need Refrences

Hi All, Im looking for some case studies or reference sites or benchmarks for Solaris/Linux and Postgresql and application level benchmarks like JBOS/Tomcat with Postgres as backend. It would be great if anybody could help me. Regards Anand Kumar. begin:vcard fn:Anand Kumar M n:Meenakshi Su

Re: [GENERAL] select random order by random

"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On 11/1/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: >> On Thu, Nov 01, 2007 at 02:22:58PM -0400, Tom Lane wrote: >> > > SELECT random()FROM generate_series(1, 10) ORDER BY random(); >> > > SELECT random() AS foo FROM generate_series(1

Re: [GENERAL] Populating large DB from Perl script

Em Thursday 01 November 2007 16:57:36 Kynn Jones escreveu: > > But it occurred to me that this is a generic enough problem, and that > I'm probably re-inventing a thoroughly invented wheel. Are there > standard techniques or resources or Pg capabilities to deal with this > sort of situation? You

Re: [GENERAL] (Never?) Kill Postmaster?

"Scott Marlowe" <[EMAIL PROTECTED]> writes: > Tom, is it possible the backend was doing something that couldn't be > immediately interrupted, like a long wait on IO or something? Sherlock Holmes said that theorizing in advance of the data is a capital mistake... What we can be reasonably certain

Re: [GENERAL] Scrolling cursors in PL/PgSQL

"Dawid Kuroczko" <[EMAIL PROTECTED]> writes: > Is there a way to scroll a cursor from within PL/PgSQL? FWIW, in 8.3 plpgsql has full FETCH/MOVE support --- that might or might not help you, depending on what your timescale for deploying this application is ... regards, tom

Re: [GENERAL] select random order by random

Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > Something twigged telling me that in fact the latter expression is not > in standard SQL but a (very common) extension. A is clearly > indicated to be a with no indication anywhere that > column aliases are allowed here (though that may be in t

Re: [GENERAL] Scrolling cursors in PL/PgSQL

On 01/11/2007, Dawid Kuroczko <[EMAIL PROTECTED]> wrote: > Hello. > > Is there a way to scroll a cursor from within PL/PgSQL? > > I tried EXECUTE, but: > > ERROR: cannot manipulate cursors directly in PL/pgSQL > HINT: Use PL/pgSQL's cursor features instead. > > The idea would be that PL/pgsql fun

Re: [GENERAL] Scrolling cursors in PL/PgSQL

On 01/11/2007 20:19, Dawid Kuroczko wrote: > Any other idea how to efficiently solve such a problem? How about creating a temporary table within your function and using that instead? Ray. --- Raymond O'Donnell, Director of Music, G

Re: [GENERAL] select random order by random

On 11/1/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Thu, Nov 01, 2007 at 02:22:58PM -0400, Tom Lane wrote: > > > SELECT random()FROM generate_series(1, 10) ORDER BY random(); > > > SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo; > > > > (BTW, this is not the

Re: [GENERAL] Solaris 10, mod_auth_pgsql2

Yup, this was in thanks ! Jeff. On 1-Nov-07, at 2:27 PM, Tom Lane wrote: Jeff MacDonald <[EMAIL PROTECTED]> writes: sandbox# /opt/csw/apache2/sbin/apxs -i -a -c -l /opt/csw/postgresql/ I think you want -I not -l in front of that include path ... regards, tom lane

[GENERAL] Populating large DB from Perl script

Hi. This is a recurrent problem that I have not been able to find a good solution for. I have large database that needs to be built from scratch roughly once every month. I use a Perl script to do this. The tables are very large, so I avoid as much as possible using in-memory data structures,

Re: [GENERAL] select random order by random

On Thu, Nov 01, 2007 at 02:22:58PM -0400, Tom Lane wrote: > > SELECT random()FROM generate_series(1, 10) ORDER BY random(); > > SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo; > > (BTW, this is not the planner's fault; the collapsing of the two > targetlist entries into on

Re: [GENERAL] getting list of tables from command line

On Wed, Oct 31, 2007 at 08:01:41AM -0700, Craig White wrote: > my script looks like this... > (all I want is to get a list of the tables into a text file pg_tables) everybody else showed some ways, but i'll ask a question: i hope you're not treating it as a backup? bacause when you do it that way

Re: [GENERAL] (Never?) Kill Postmaster?

On 10/31/07, Christian Schröder <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > >> Ok, you wrote "Postgres will recover automatically", but could this take > >> several minutes? > >> > > > > Yeah, potentially. I don't suppose you have any idea how long it'd been > > since your last checkpoint, but

[GENERAL] Scrolling cursors in PL/PgSQL

Hello. Is there a way to scroll a cursor from within PL/PgSQL? I tried EXECUTE, but: ERROR: cannot manipulate cursors directly in PL/pgSQL HINT: Use PL/pgSQL's cursor features instead. The idea would be that PL/pgsql function would look through (all) query results, then rewind the cursor and

Re: [GENERAL] select random order by random

On Thursday 01 November 2007 17:16, Scott Marlowe wrote: > > > I was very surprised when I executed such SQL query (under PostgreSQL > > > 8.2): > > > select random() from generate_series(1, 10) order by random(); > > > > (...) > My guess is that it was recognized by the planner as the same functi

Re: [GENERAL] select random order by random

On Thursday 01 November 2007 17:08, brian wrote: > > I was very surprised when I executed such SQL query (under PostgreSQL > > 8.2): select random() from generate_series(1, 10) order by random(); > > > > I don't understand - why the result is like that? It seems like in each > > row both random()s

Re: [GENERAL] Solaris 10, mod_auth_pgsql2

Jeff MacDonald <[EMAIL PROTECTED]> writes: > sandbox# /opt/csw/apache2/sbin/apxs -i -a -c -l /opt/csw/postgresql/ I think you want -I not -l in front of that include path ... regards, tom lane ---(end of broadcast)--- TIP 5

Re: [GENERAL] select random order by random

Richard Huxton <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> This does strike me as wrong. random() is marked volatile and the planner >> ought not collapse multiple calls into one. > I think I agree with the earlier poster. Surely these two queries should > be equivalent? > SELECT rand

Re: [GENERAL] select random order by random

On Thu, Nov 01, 2007 at 04:49:16PM +, Richard Huxton wrote: > Gregory Stark wrote: > >This does strike me as wrong. random() is marked volatile and the planner > >ought not collapse multiple calls into one. > > I think I agree with the earlier poster. Surely these two queries should > be equ

[GENERAL] test

Re: [GENERAL] =?UTF-8?Q?select_random_order_by_random?=

[EMAIL PROTECTED] ("=?UTF-8?Q?piotr=5Fsobolewski?=") writes: > I was very surprised when I executed such SQL query (under PostgreSQL 8.2): > select random() from generate_series(1, 10) order by random(); > > I thought I would receive ten random numbers in random order. But I received > ten random

Re: [GENERAL] select random order by random

Gregory Stark wrote: "Scott Marlowe" <[EMAIL PROTECTED]> writes: I think that Piotr expected the random() to be evaluated in both places separately. My guess is that it was recognized by the planner as the same function and evaluated once per row only. If you try this: select random() from g

Re: [GENERAL] select random order by random

"Scott Marlowe" <[EMAIL PROTECTED]> writes: > I think that Piotr expected the random() to be evaluated in both > places separately. > > My guess is that it was recognized by the planner as the same function > and evaluated once per row only. > > If you try this: > > select random() from generate_s

Re: [GENERAL] select random order by random

On 11/1/07, Lee Keel <[EMAIL PROTECTED]> wrote: > > Dear sirs, > > > > I was very surprised when I executed such SQL query (under PostgreSQL > > 8.2): > > select random() from generate_series(1, 10) order by random(); > > > > I thought I would receive ten random numbers in random order. But I > > r

Re: [GENERAL] select random order by random

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of piotr_sobolewski > Sent: Thursday, November 01, 2007 9:25 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] select random order by random > > Dear sirs, > > I was very surprised

Re: [GENERAL] select random order by random

piotr_sobolewski wrote: Dear sirs, I was very surprised when I executed such SQL query (under PostgreSQL 8.2): select random() from generate_series(1, 10) order by random(); I thought I would receive ten random numbers in random order. But I received ten random numbers sorted numerically:

[GENERAL] Solaris 10, mod_auth_pgsql2

Greetings. I'm trying to compile mod_auth_pgsql2 for Solaris 10, I have the blastwave packages installed for libpq, apache2 and apache2_devel I'm following this how to http://archives.postgresql.org/pgsql-performance/2007-10/msg00076.php However, when I run it i get this sandbox# /opt/csw/

Re: [GENERAL] AutoVacuum Behaviour Question

Bruce Momjian wrote: No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation Can somebody explain this one to me? because of our auditing technique, we have many LONG lived temp tables.(one per pooled

[GENERAL] select random order by random

Dear sirs, I was very surprised when I executed such SQL query (under PostgreSQL 8.2): select random() from generate_series(1, 10) order by random(); I thought I would receive ten random numbers in random order. But I received ten random numbers sorted numerically: random

[GENERAL] anybody attending FOSS.in 2007 ??

Hello, http://foss.in/2007 , the most happening Open Source event in India is taking place in Bangalore between Dec4th and Dec8th. Is anybody from the postgres community planning to attend ? I thought it would be a good opportunity to get together and talk about Postgres and if we have some go

Re: [GENERAL] If Postgresql DLL files causing any kind of conflict for two different versions of Postgresql installed on one machine

dhiraj bhosale wrote: > Hi, > > I would like to know if there is any kind of conflict or limitation > occur between postgresql dll files for two different setups(different > versions) in different data directories and running on two serepate > ports. And if it has any conflict can anyone put some

[GENERAL] Attaching information about users

What is the proper way to attach additional information about users of a database. That is to say I am using their DB login as their application ID, and I need to have one or more tables which remember preferences and other application level things, but the key is their current userid and I want

[GENERAL] If Postgresql DLL files causing any kind of conflict for two different versions of Postgresql installed on one machine

Hi, I would like to know if there is any kind of conflict or limitation occur between postgresql dll files for two different setups(different versions) in different data directories and running on two serepate ports. And if it has any conflict can anyone put some more details regarding the same

Re: [GENERAL] day of week

Hi, I have records with date column. Is there a way I can get which day of week this date is? http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT t.v. ---(end of broadcast)--- TIP 4: Have you search

Re: [GENERAL] day of week

am Wed, dem 31.10.2007, um 16:34:44 +0200 mailte Anton Andreev folgendes: > Hi, > > I have records with date column. Is there a way I can get which day of > week this date is? Yes, no problem. select extract (dow from date). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1

Re: [GENERAL] day of week

Anton Andreev wrote on 31.10.2007 15:34: Hi, I have records with date column. Is there a way I can get which day of week this date is? What about the extract() function with the dow parameter? Tho

Re: [GENERAL] day of week

On 31/10/2007, Anton Andreev <[EMAIL PROTECTED]> wrote: > Hi, > > I have records with date column. Is there a way I can get which day of > week this date is? > > Cheers, > Anton Hello http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html EXTRACT or function date_part Regards Pav

[GENERAL] day of week

Hi, I have records with date column. Is there a way I can get which day of week this date is? Cheers, Anton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining

Re: [GENERAL] how can I shut off "more"?

2007/10/31, Gauthier, Dave <[EMAIL PROTECTED]>: > When I run a query, and the number of lines exceeds what the screen can > hold, the results seem to get piped into "more" (or "less"). How can I > turn that off and just have everything stream out without stopping? I tried > –echo-all, but that d

[GENERAL] REQ: pgagent to send out alert/emails if error occurs

Hi All, Thanks to Dave Page's instruction, I'm not a pretty happy camper with pgadmin's pgagent. It provides a graphical interface to for the NON-nix inclined people. (otherwise a sh script would have done nicely) Anyway.. I've got a request and I'm not sure if this has been talked about or (