Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Wed, 2009-05-27 at 19:53 -0400, Douglas Alan wrote: > We have a very large table (150 million rows) where the rows are not > in a random order. Some common queries will have millions of results, > and this of course is slow. For an interactive interface to the > database, we want to put a lim

Re: [GENERAL] How to speed up the first-time-searching in pgsql?

zxo102 ouyang wrote: Hi there, Thanks for your suggestions. I do have an application running on the machine all the time. In fact, the application keeps writing real-time monitoring data into the database. Based on my understanding of your messages, I can't do anything to speed up the first-time-

Re: [GENERAL] Postgres registry access using java

Anirban Pal wrote: > Can any body tell me how to fetch directory path for lib or > data directory for postgres installation in windows using > registry. Solution using Java language will be highly solicited. Java does not give you access to the Windows registry as this is not portable to other

Re: [GENERAL] Postgres registry access using java

Thanks Josh for your kind reply. In the pg_settings table all reference to the folder is with reference to data directory. Like "config_file" "C:/Program Files/PostgreSQL/8.3/data/postgresql.conf""data_directory" "C:/Program Files/PostgreSQL/8.3/data""hba_file" "C:/Program Files/PostgreSQL/8.

Re: [GENERAL] Postgres registry access using java

Thanks Josh for your kind reply. In the pg_settings table all reference to the folder is with reference to data directory. Like "config_file" "C:/Program Files/PostgreSQL/8.3/data/postgresql.conf""data_directory" "C:/Program Files/PostgreSQL/8.3/data""hba_file" "C:/Program Files/PostgreSQ

Re: [GENERAL] How to Install Postgres 8.3 in Solaris 10 as a service

@Merlin, @Emanuel Thank you for your reply. It will surely solve my problem. Dear all members, I have been working on postgres (windows), for last 8 months. Recently, a client requrement requires me to install postgres in solaris 10. What I did is this. Untar the deliverables from postgres

Re: [GENERAL] Postgres registry access using java

@ Laurenz Albe, All 1st of all thanks for your kind reply. But I can access registry values using java. The problem I am facing is, in case of postgres settings informations are kept under a subdirectory named after the product key. I am able to access from the registry if the path is unique

Re: [GENERAL] Multidimensional array definition in composite type appears parsed as string

Tom Lane-2 wrote: > > It sounds like you are using some code that mistakenly thinks that > double quotes have a semantic meaning here. They do not. They are just > there to delimit members of the row value, not to tell you what type the > members are. > Note: quoted text abridged per mailing

Re: [GENERAL] Postgres registry access using java

On Thu, May 28, 2009 at 10:33 AM, Anirban Pal wrote: > @  Laurenz Albe, All > >  1st of all thanks for your kind reply. But I can access registry values > using java. The problem I am facing is, in case of postgres settings > informations are kept under a subdirectory named after the product key.

Re: [GENERAL] Postgres registry access using java

Anirban Pal wrote: > 1st of all thanks for your kind reply. But I can access registry values > using java. Ok, then you have some additional native code installed :^) Does the documentation of this add-on suggest a way to search and traverse Windoes registry keys? >> Can any body tell me how

Re: [GENERAL] Postgres registry access using java

Anirban Pal wrote: > In the pg_settings table, all reference to folder is with reference to data > directory. > No reference to postgres instalation > base directory, i mean where postgres have been installed. That's true, you won't find that in the database. Yurs, Laurenz Albe -- Sent via p

Re: [GENERAL] composite type and domain

2009/5/27 Scott Bailey : > Who said anything about the application level? can you give an example please ? -- GJ -- 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] Postgres registry access using java

On 2009-05-27, Anirban Pal wrote: > This is a multi-part message in MIME format. > > --=_NextPart_000_0041_01C9DECE.11E417C0 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Hi all, > > Can any body tell me how to fetch directory path f

Re: [GENERAL] \dt+ on 8.4

now I know it boils down to pg_relation_size at the end of the day, cos that's what psql uses. But please tell me guys, that I am not the only one who would expect true size of table in \dt+ ? At the end of the day, postgresql 'cheats' behind scenes with toast, and avid user shouldn't really need t

[GENERAL] ERROR: could not find array type for data type numeric[]

Hi, I want to execute the following query in my Postgresql 8.3 but gives error: ERROR: could not find array type for data type numeric[] When i googled, no result. SELECT * FROM ( SELECT * FROM( SEL

[GENERAL] ubuntu 9.04 and auto-start

Hi, I have installed postgres 8.3 and it runs if I manually start postgres. That is to say I '/etc/init.d/postgresql start' as root. Runs perfectly. But if I restart the computer postgres does not auto-start. I've done a little research but did not see anything that jumped off the page to tell

[GENERAL] a modo texto

Hola, tengo un problema , tengo que montar una base de datos hecha en postgres en ubuntu a modo texto, alguna idea?? Saludos JL -- Participe en Universidad 2010, del 8 al 12 de febrero del 2010 La Habana, Cuba http://www.universidad2010.cu http://www.universidad2010.com

Re: [GENERAL] Postgres Clustering

We didn't have much luck with Continuent. They had to make multiple code level changes to get their product to work correctly with our app on PG 8.2. We never did get it successfully implemented. At this point I'm stuck with WAL shipping as I can't find anything that fits my constraints. Thanks

[GENERAL] Debugger install

Hello All, I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to install pldebugger (edb-debugger) to write complex stored procedures. The problem is i didn't use the source code to install the database and the instructions found in the documentation is using postgres source co

Re: [GENERAL] ubuntu 9.04 and auto-start

update-rc.d On Thu, May 28, 2009 at 7:25 AM, johnf wrote: > Hi, > I have installed postgres 8.3 and it runs if I manually start postgres.  That > is to say I '/etc/init.d/postgresql start' as root. Runs perfectly. > > But if I restart the computer postgres does not auto-start.  I've done a > litt

Re: [GENERAL] ubuntu 9.04 and auto-start

Also, sysvconfig tools like RH uses are available: http://www.cyberciti.biz/tips/how-to-controlling-access-to-linux-services.html -- 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] What is the right way to deal with a table with rows that are not in a random order?

Douglas Alan writes: > As I said, in my original post, Postgres's approach would be completely > reasonable in this case,* if* the rows that it was looking for were > sprinkled randomly throughout the table. But they're *not* in this case -- > they're all at the end. There's been some talk of pe

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Wed, May 27, 2009 at 8:43 PM, Douglas Alan wrote: > On Wed, May 27, 2009 at 8:54 PM, Jeff Davis wrote: > >> >> If you're putting a LIMIT on it, why does it return millions of results? > > It doesn't return millions of results with the LIMIT on it.  It just does a > sequential scan of the table

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

Note that in the OPs case I'd probably try testing things like turning off seqscan, or lowering random_page_cost. I'd also look at clustering on the index for the field you're selecting on. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

[Fwd: Re: [GENERAL] a modo texto]

۝ú~Ë­­ª}ï­<ë~÷Ó_6çOtÓmi salva es base.backup, con el pg_dump que hagoyo personalizo al ususario postgres, sudo su postgres, luego psql y despues quegracias> 2009/5/28 : >> Hola, tengo un problema , tengo que montar una base de datos hecha en >> postgres en ubuntu a modo texto, alguna idea?

Re: [GENERAL] Multidimensional array definition in composite type appears parsed as string

miller_2555 writes: > I appreciate the clarification on the output. Given the assignment appears > correct, what is the appropriate method to access the elements of the > multidimensional array? I think what you're missing is the distinction between slice and simple element access, ie instead of

[GENERAL] How to pass parameters into a sql script ?

We are unable to pass parameters into any sql script. Could anyone look at the test below and give any suggestions? PostgreSQL version is 8.2.7, running on Linux. Test Script $ cat chk_param.sql select ||:p_date::char(8)||; select count(*) from prod_debit_payments_unappl

[GENERAL] modo texto

El servidor de postgres esta en windows, y quiero pasar una base de datos� a un servidor en linux con modo texto, segun voy entendiendo primero tengo que hacer pg_dump Base , a mi base de datos en windows, lo hago y no me muestra nada , no se si lo hizo o no , en caso de hacerlo genera algun f

Re: [GENERAL] modo texto

ßnõ~Ë­­ª{çm8ӎ6×_6çOtÓhi, sorry my englishI need to copy a data base from windows to linux, how can I save my data base from windows with pg_dump, and where the file is??and after how can I create the data base in linux without graphic enviroment , just text mode, with commandsThank> Olá, > > e

Re: [GENERAL] modo texto

On Thu, May 28, 2009 at 4:24 PM, wrote: > hi, sorry my english > I need to copy a data base from windows to linux, how can I save my data > base from windows with pg_dump, and where the file is?? > and after how can I create the data base in linux without graphic enviroment > , just text mode

Re: [GENERAL] modo texto

On Thu, 2009-05-28 at 16:43 +0100, Grzegorz Jaśkiewicz wrote: > On Thu, May 28, 2009 at 4:24 PM, wrote: > > hi, sorry my english > > I need to copy a data base from windows to linux, how can I save my data > > base from windows with pg_dump, and where the file is?? > > and after how can I cre

Re: [GENERAL] Multidimensional array definition in composite type appears parsed as string -- SOLVED

Tom Lane-2 wrote: > > miller_2555 writes: >> I appreciate the clarification on the output. Given the assignment >> appears >> correct, what is the appropriate method to access the elements of the >> multidimensional array? > > I think what you're missing is the distinction between slice and si

[GENERAL] Continuent (was: Postgres Clustering)

Hmmm. Anyone out there have the Continuent solution working with PostgreSQL? If so, what release? We're at 8.3 right now. thanks, -Alan p.s. I'm continuing the cross-post because that is the way I started this thread. Future threads will not be cross-posted. On Thu, May 28, 2009 at 9:34 AM, S

Re: [GENERAL] Postgres registry access using java

Anirban Pal wrote: Thanks Josh for your kind reply. In the pg_settings table all reference to the folder is with reference to data directory. Like "config_file" "C:/Program Files/PostgreSQL/8.3/data/postgresql.conf" "data_directory" "C:/Program Files/PostgreSQL/8.3/data" "hba_file" "C:/P

Re: [GENERAL] How to speed up the first-time-searching in pgsql?

On Thu, May 28, 2009 01:23, Richard Huxton wrote: > zxo102 ouyang wrote: >> Hi there, >> Thanks for your suggestions. I do have an application running on the >> machine >> all the time. In fact, the application keeps writing real-time >> monitoring >> data into the database. Based on my understandi

Re: [GENERAL] Multidimensional array definition in composite type appears parsed as string -- SOLVED

miller_2555 writes: > I had assumed that the whole slice of a sub-array would have been returned > as a 1-D array by accessing an element of the "outer" array, but that does > not appear the case. No, it's not. The semantics are constrained here by the fact that we don't consider 1-D and 2-D ar

Re: [GENERAL] Postgres registry access using java

In the pg_settings table, all reference to folder is with reference to data directory. Like "config_file" "C:/Program Files/PostgreSQL/8.3/data/postgresql.conf""data_directory" "C:/Program Files/PostgreSQL/8.3/data""hba_file" "C:/Program Files/PostgreSQL/8.3/data/pg_hba.conf""ident_file"

[GENERAL] configurar el hba

ãmõ~Ë­­ªtßmøïùÛ_6çOtÓ como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que׎<~Ë­­ªtßmøïùÛ_6çOtÓ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

[GENERAL] configurar el hba

׎<~Ë­­ªuóžwëùÛ_6çOtÓ como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to yo

[GENERAL] plperl booleans

When I use spi_prepare and spi_exec_prepared on a query that selects a boolean value, it comes back as the text 't' or 'f', which in perl both evaluate to true, so I have to either use perl to solve that or cast to integer in the query, both of which options seem less than elegant. Is this a bug o

[GENERAL] configurar el hba

como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que -- Participe en Universidad 2010, del 8 al 12 de febrero del 2010 La Habana, Cuba http://www.universidad2010.cu http://www.universidad2010.com

Re: [GENERAL] ubuntu 9.04 and auto-start

On Thursday 28 May 2009 07:28:04 am Scott Marlowe wrote: > update-rc.d > > On Thu, May 28, 2009 at 7:25 AM, johnf wrote: > > Hi, > > I have installed postgres 8.3 and it runs if I manually start postgres. > >  That is to say I '/etc/init.d/postgresql start' as root. Runs perfectly. > > > > But if

[GENERAL] PL-Debugger installation problem

Hello All, I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to install pldebugger (edb-debugger) to write complex stored procedures. The problem is i didn't use the source code to install the database and the instructions found in the documentation is using postgres source co

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 4:14 AM, Simon Riggs wrote: > Partition the table, then scan the correct partition. If I do that, will Postgres figure out the "right thing" to do if the parent table is queried instead?  Also, what are the performance implications then for doing queries that span all the

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 10:24 AM, Scott Marlowe wrote: >  OTOH, if you've got it all sussed out, then ignore the request for more > information. I don't *know* if I have it "all sussed out", but I *do* know why Postgres is doing what it is doing in this particular case. It's assuming that the

Re: [GENERAL] How to pass parameters into a sql script ?

2009/5/28 Atul Chojar : > We are unable to pass parameters into any sql script. Could anyone look at > the test below and give any suggestions? PostgreSQL version is 8.2.7, > running on Linux. > > > > Test Script > > > > $ cat chk_param.sql > > select ||:p_date::char(8)||; > > sele

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 10:41 AM, Scott Marlowe wrote: > Note that in the OPs case I'd probably try testing things like turning > off seqscan, or lowering random_page_cost. I'd also look at > clustering on the index for the field you're selecting on. I'm worried that turning off seqscan would d

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 1:45 PM, Douglas Alan wrote: > On Thu, May 28, 2009 at 10:41 AM, Scott Marlowe > wrote: > >> Note that in the OPs case I'd probably try testing things like turning >> off seqscan, or lowering random_page_cost.  I'd also look at >> clustering on the index for the field you'

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 1:12 PM, Douglas Alan wrote: > On Thu, May 28, 2009 at 10:24 AM, Scott Marlowe > wrote: > >>  OTOH, if you've got it all sussed out, then ignore the request for more >> information. > > I don't *know* if I have it "all sussed out", but I *do* know why > Postgres is doing

Re: [GENERAL] ubuntu 9.04 and auto-start

johnf wrote: I have installed postgres 8.3 and it runs if I manually start postgres. That is to say I '/etc/init.d/postgresql start' as root. Runs perfectly. But if I restart the computer postgres does not auto-start. Did you install the package named postgresql-8.3? Because it con

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

ORM should'nt be a factor as you have many configureable properties available in hibernate.cfg.xml or hibernate.properties hibernate.connection.driver_classjdbc driver classhibernate.connection.urljdbc URLhibernate.connection.usernamedatabase userhibernate.connection.passworddatabase user pass

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, 2009-05-28 at 15:03 -0400, Douglas Alan wrote: > The application in question is a kind of data warehousing thing (of > astronomical stars), and there's an ORM in the middle, so it's not > easy for us to hand-tune how individual queries are specified. > Instead, we have to structure the da

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 5:52 PM, Simon Riggs wrote: > > On Thu, 2009-05-28 at 15:03 -0400, Douglas Alan wrote: > > > The application in question is a kind of data warehousing thing (of > > astronomical stars), and there's an ORM in the middle, so it's not > > easy for us to hand-tune how individu

[GENERAL] pg_stats.avg_width differs by a factor of 4 on different machines

Hi list We are using the PostgreSQL pg_stats view to estimate file sizes for some geodata exports. However, the following query gives us totally different results on different servers: select avg_width from pg_stats where tablename='some_geodata' and attname = > 'GEOMETRY'; > PostgreSQL 8.3.7 on

[GENERAL] Please remove me from the list!

Please, remove me from the list! Marcelo Giovane

Re: [GENERAL] pg_stats.avg_width differs by a factor of 4 on different machines

Craig de Stigter writes: > We are using the PostgreSQL pg_stats view to estimate file sizes for some > geodata exports. However, the following query gives us totally different > results on different servers: > select avg_width from pg_stats where tablename='some_geodata' and attname = > 'GEOMETRY

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, 2009-05-28 at 15:12 -0400, Douglas Alan wrote: > The most obvious solution would be an option to tell Postgres not to > assume that the value is evenly distributed throughout the table, and > to take account of the fact that the data in question might very well > be clustered at the very en

Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed

Emanuel Calvo Franco wrote: > Hi community, > > I'm trying to compile pl/java sources for 8.4 beta1 (for a test) but > it gives me 20 errors at the end: Which compiler and JDK are you using? ... and no, there isn't really any way to ignore the lib version; it wouldn't work, because 8.3 and 8.4 a

Re: [GENERAL] vista failed to install postgresql

douglas wrote: > the binary , wouldn't start service, install ended then and there . Did you check the event viewer to see what the service startup error was? Did you look at the log file PostgreSQL created to see if there were any errors produced? Do you have a virus scanner installed? If so,

Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed

On Wed, 27 May 2009, Emanuel Calvo Franco wrote: Hi community, I'm trying to compile pl/java sources for 8.4 beta1 (for a test) but it gives me 20 errors at the end: To build against 8.4 you need pljava from CVS. Also pljava can only be built with the 1.4 or 1.5 JDK, not with the 1.6 vers

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

Scott Marlowe wrote: > Douglas Alan wrote: >> I'm worried that turning off seqscan would distort other queries. >> (Remember, I don't have control of the individual queries.  The >> user of the application can specify all sorts of queries, and >> there's an ORM in the middle.) > You are aware y

[GENERAL] Debugging pgsql function date error

I have a long plpgsql function which takes a date as one of its arguments but I am receiving an error for another date! I'm using 8.3 on Debian. CREATE OR REPLACE FUNCTION fn_alert01_maker(integer, integer, integer, integer, date, integer) RETURNS SETOF alert_info AS $$ DECLARE userid

Re: [GENERAL] Please remove me from the list!

Look into the headers of any email message from the list and look for the "List-Unsubscribe" line On Thu, May 28, 2009 at 2:26 PM, Marcelo Giovane wrote: > Please, remove me from the list! > > Marcelo Giovane > -- “Mother Nature doesn’t do bailouts.” - Glenn Prickett -- Sent via pgs

Re: [GENERAL] Please remove me from the list!

"Marcelo Giovane" wrote: > > Please, remove me from the list! Look in the message headers: List-Unsubscribe: -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make ch

Re: [GENERAL] Please remove me from the list!

Marcelo Giovane wrote: Please, remove me from the list! Marcelo Giovane You may remove yourself. At the bottom of *every* message sent from the mail list server are these instructions: Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscriptio

Re: [GENERAL] Please remove me from the list!

Marcelo Giovane wrote: Please, remove me from the list! Generally, when you find your way into a location, you are expected to find your way out... -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety.

Re: [GENERAL] Debugging pgsql function date error

On Thursday 28 May 2009 5:36:10 pm Rory Campbell-Lange wrote: > I have a long plpgsql function which takes a date as one of its > arguments but I am receiving an error for another date! I'm using 8.3 on > Debian. > > CREATE OR REPLACE FUNCTION > fn_alert01_maker(integer, integer, integer, integ

Re: [GENERAL] composite type and domain

Grzegorz Jaśkiewicz wrote: 2009/5/27 Scott Bailey : Who said anything about the application level? can you give an example please ? Did you read the article I sent you earlier? I'm doing almost the exact same thing you are doing save the bytea field. I create a timespan composite type t

Re: [GENERAL] Transaction settings: nowait

Dude, all what u wrote make sense. Only your missing one thing, consider the following scenario that u already reply to: >> For. example: I forget to make commit, or rollback on exception then all >> resources I used (updated) is locked. >Yes - that's an application bug. Even if it's applicati

Re: [GENERAL] Transaction settings: nowait

On Thu, May 28, 2009 at 9:52 PM, Yasser Idris wrote: > > Dude, all what u wrote make sense. Only your missing one thing, consider the > following scenario that u already reply to: > >>> For. example: I forget to make commit, or rollback on exception then all >>> resources I used (updated) is locke

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

On Thu, May 28, 2009 at 6:45 PM, Douglas Alan wrote: > Scott Marlowe wrote: > >> Douglas Alan wrote: > >>> I'm worried that turning off seqscan would distort other queries. >>> (Remember, I don't have control of the individual queries.  The >>> user of the application can specify all sorts of que