Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 10:26 PM, Diego Schulz <[EMAIL PROTECTED]> wrote: > > > On Fri, Nov 21, 2008 at 9:50 AM, Ciprian Dorin Craciun > <[EMAIL PROTECTED]> wrote: >> >>Currently I'm benchmarking the following storage solutions for this: >>* Hypertable (http://www.hypertable.org/) -- which

[GENERAL] pgAdmin error

2008-11-21 Thread David
I am trying to use pgAdmin 1.8.4 to edit the pg_hba.conf file on a PostgreSQL 8.3 database running on Ubuntu 8.10. I get the following error message: An error has occurred: ERROR: absolute path not allowed CONTEXT: SQL function "pg_file_length" statement 1 then... Backend Access Configuration

Re: [GENERAL] Reg: Custom Data Types in PostgreSQL

2008-11-21 Thread Merlin Moncure
On Fri, Nov 21, 2008 at 11:18 PM, Raj K <[EMAIL PROTECTED]> wrote: > Hi all, > I am not an expert in DB. So please excuse, if the question is stupid. > > In PostgreSQL, we do support custom data types - say enum type. > --> CREATE TYPE testtype AS ENUM {'test1', 'test2'}; > Now, I w

[GENERAL] Reg: Custom Data Types in PostgreSQL

2008-11-21 Thread Raj K
Hi all, I am not an expert in DB. So please excuse, if the question is stupid. In PostgreSQL, we do support custom data types - say enum type. --> CREATE TYPE testtype AS ENUM {'test1', 'test2'}; Now, I was wondering whether we should be using a different table or the enum itself f

Re: [GENERAL] referring to a different database from a trigger

2008-11-21 Thread pwillis
Scott Marlowe wrote: On Fri, Nov 21, 2008 at 4:43 PM, pw <[EMAIL PROTECTED]> wrote: Scott Marlowe wrote: On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <[EMAIL PROTECTED]> wrote: Note that there are no built in transactional symantics in such situations. You got to roll your own. And they m

Re: [GENERAL] Interval Format

2008-11-21 Thread Zagato
Hi.. thanks for the answer well this work in the test sql, but this needs to rewrite all the SQL in my PHP application :-p Jejej... can i set a deatul time year in postgres.conf or with a single SET ... ? Thanks.. Alan Acosta On Fri, Nov 21, 2008 at 8:56 PM, Ron Mayer <[EMAIL PROTECTED]>wrot

Re: [GENERAL] Interval Format

2008-11-21 Thread Ron Mayer
Zagato wrote: I have som SQL that in 8.0.3 do: # SELECT '32 hours'::INTERVAL; interval - @ 1 day 8 hours And in 8.3.5 do: seg_veh2=# SELECT '@ 32 hours'::INTERVAL; interval @ 32 hours Why i unable to get my old style of interval, i really need to see the da

[GENERAL] Interval Format

2008-11-21 Thread Zagato
Hi everyone... im looking for some help with the interval format between two diferents versions of postgres I have instaled in my old server postgres 8.0.3 and in my new one postgres 8.3.5... Everything in both looks works nice but i notice a little difference that is taking my crazy.. I have s

Re: [GENERAL] referring to a different database from a trigger

2008-11-21 Thread Scott Marlowe
On Fri, Nov 21, 2008 at 4:43 PM, pw <[EMAIL PROTECTED]> wrote: > Scott Marlowe wrote: >> >> On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <[EMAIL PROTECTED]> wrote: >> >> Note that there are no built in transactional symantics in such >> situations. You got to roll your own. And they may not wo

Re: [GENERAL] referring to a different database from a trigger

2008-11-21 Thread pw
Scott Marlowe wrote: On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <[EMAIL PROTECTED]> wrote: Note that there are no built in transactional symantics in such situations. You got to roll your own. And they may not work. Yeah, that was what I was hoping for. ie:(query between databases)

Re: [GENERAL] referring to a different database from a trigger

2008-11-21 Thread Scott Marlowe
On Fri, Nov 21, 2008 at 3:31 PM, ries van Twisk <[EMAIL PROTECTED]> wrote: > > On Nov 21, 2008, at 5:26 PM, pw wrote: > >> Hello, >> >> Is there a syntax for querying another database >> from a trigger in the current database? >> >> Thanks for any info, > > Generally we would say DBLink or DBI-Link

Re: [GENERAL] Password and Installation

2008-11-21 Thread Dave Page
On Fri, Nov 21, 2008 at 10:30 PM, Andrew Maeng <[EMAIL PROTECTED]> wrote: > Hi, > > I recently uninstalled PostgreSQL, and now am attempting to reinstall it on > a Windows Vista OS. However, I don't remember the password that was used to > install PostgreSQL before, and am prompted with "The passwo

Re: [GENERAL] referring to a different database from a trigger

2008-11-21 Thread ries van Twisk
On Nov 21, 2008, at 5:26 PM, pw wrote: Hello, Is there a syntax for querying another database from a trigger in the current database? Thanks for any info, P Generally we would say DBLink or DBI-Link Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) T

[GENERAL] Password and Installation

2008-11-21 Thread Andrew Maeng
Hi, I recently uninstalled PostgreSQL, and now am attempting to reinstall it on a Windows Vista OS. However, I don't remember the password that was used to install PostgreSQL before, and am prompted with "The password specified was incorrect. Please enter the correct password for the postgres

[GENERAL] referring to a different database from a trigger

2008-11-21 Thread pw
Hello, Is there a syntax for querying another database from a trigger in the current database? Thanks for any info, P -- 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] Virtualization VMWare ESX and Win2k3 as a Guest OS

2008-11-21 Thread Scott Marlowe
On Fri, Nov 21, 2008 at 1:58 PM, imageguy <[EMAIL PROTECTED]> wrote: > >> Have you tried to duplicate it w a native install of w2k3? > > This customer had been working with pg2.9 on win2k3 native for > several months without issue. Hardware company upgraded them to a new > server and used VMWare

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Steve Crawford
Bruce Momjian wrote: brian wrote: Tom Lane wrote: Perhaps it's just subjective: we're all getting older. Which, as "Dr. A" (aka Isaac Asimov) pointed out in "The Sensuous Dirty Old Man", beats the alternative. I thought about that, which is scary in itself. :-( But I don't think

Re: [GENERAL] Reversing transactions on a large scale

2008-11-21 Thread snacktime
On Thu, Nov 20, 2008 at 4:06 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Thu, Nov 20, 2008 at 4:36 PM, snacktime <[EMAIL PROTECTED]> wrote: >> Right now we are running mysql as that is what was there when I >> entered the scene. We might switch to postgres, but I'm not sure if >> postgres ma

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Bruce Momjian
Steve Crawford wrote: > Bruce Momjian wrote: > > brian wrote: > > > >> Tom Lane wrote: > >> > >> Perhaps it's just subjective: we're all getting older. > >> > Which, as "Dr. A" (aka Isaac Asimov) pointed out in "The Sensuous Dirty > Old Man", beats the alternative. > > I thought about

Re: [GENERAL] Virtualization VMWare ESX and Win2k3 as a Guest OS

2008-11-21 Thread imageguy
> Have you tried to duplicate it w a native install of w2k3? This customer had been working with pg2.9 on win2k3 native for several months without issue. Hardware company upgraded them to a new server and used VMWare to copy the server and an instance ESX and then move the entire server to a ne

Re: [GENERAL] compiling libpq.dll with Borland C++, is it possible?

2008-11-21 Thread Martin Gainty
think you'll have to contact phillipe kahn for that library any library that says 'runtime' comes from the OS anything else you'll have to create from whatever any packages that come with the OS' compiler Martin Gainty __ Disclaimer and confidentia

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Bruce Momjian
brian wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> > >> I am finding the email traffic > >> almost impossible to continue tracking, so something different is > >> happening, but it seems it is not volume-related. > > > > Yes, my perception also is that it's getting

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Diego Schulz
On Fri, Nov 21, 2008 at 9:50 AM, Ciprian Dorin Craciun < [EMAIL PROTECTED]> wrote: > >Currently I'm benchmarking the following storage solutions for this: >* Hypertable (http://www.hypertable.org/) -- which has good insert > rate (about 250k inserts / s), but slow read rate (about 150k rea

Re: [GENERAL] compiling libpq.dll with Borland C++, is it possible?

2008-11-21 Thread Magnus Hagander
Alvaro Herrera wrote: > Tony Caduto wrote: >> Hi, >> I am trying to compile my own copy of libpq.dll using bcc32.exe, the >> docs say it is possible, but I get a error when it tries to compile >> dirent.c > > How hard would it be to set up a buildfarm member that exercises the > Borland compile

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 8:41 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Fri, 21 Nov 2008, Sam Mason wrote: > >> It's not quite what you're asking for; but have you checked out any >> of the databases that have resulted from the StreamSQL research? > > A streaming database approach is in fact id

Re: [GENERAL] Virtualization VMWare ESX and Win2k3 as a Guest OS

2008-11-21 Thread Scott Marlowe
On Fri, Nov 21, 2008 at 12:00 PM, imageguy <[EMAIL PROTECTED]> wrote: > Does anyone have any suggestions for running PG2.9 on win2k3 running > as a guest on VMware ESX ? Have you tried to duplicate it w a native install of w2k3? > Currently the system is shutting down and transactions are termina

Re: [GENERAL] converter pgplsql funcion

2008-11-21 Thread Jonah H. Harris
On Fri, Nov 21, 2008 at 7:37 AM, paulo matadr <[EMAIL PROTECTED]> wrote: > I work with oracle and have poor experience in pg/plsql. > anybody can help me with translate from pl/sql in pg/plsql in code > below: See OraToPg: You can download it here: http://pgfoundry.org/projects/ora2pg/ You ca

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Ron Mayer
Tom Lane wrote: Ron Mayer <[EMAIL PROTECTED]> writes: Joshua D. Drake wrote: Its because we eliminated the -patches mailing list. That's part of it. I've added -patches to the graph at http://0ape.com/postgres_mailinglist_size/ as well as a graph of hackers+patches combined; and it still lo

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Matthew T. O'Connor
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Its because we eliminated the -patches mailing list. Yeah, I think this is most probably explained by repeat postings of successive versions of large patches. Still, Ron might be on to something. I had not considered messa

[GENERAL] Virtualization VMWare ESX and Win2k3 as a Guest OS

2008-11-21 Thread imageguy
Does anyone have any suggestions for running PG2.9 on win2k3 running as a guest on VMware ESX ? Currently the system is shutting down and transactions are terminating with the messages indicating there is not available disk space, when in fact there is plenty of disk space available. Google tells

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Alvaro Herrera
Richard Huxton wrote: > Some of the EXPLAINs on the performance list are practically impossible > to read unless you've got the time to cut+paste and fix line-endings. Maybe we should start recommending people to post those via http://explain-analyze.info/ -- Alvaro Herrera

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Greg Smith
On Fri, 21 Nov 2008, Sam Mason wrote: It's not quite what you're asking for; but have you checked out any of the databases that have resulted from the StreamSQL research? A streaming database approach is in fact ideally suited to handling this particular problem. Looking at the original requ

Res: [GENERAL] converter pgplsql funcion

2008-11-21 Thread paulo matadr
Result -- Executando consulta: create or replace function clean_string(_p_dado character varying) returns character varying as $$ declare _v_clean_string character varying := _p_dado; _c character varying; begin for _c in select caracter from caracters loop _v_clean_string := btrim(_c_c

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 7:45 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Fri, 21 Nov 2008, Tom Lane wrote: > >> Not sure if it applies to your real use-case, but if you can try doing >> the COPY from a local file instead of across the network link, it >> might go faster. > > The fact that the in

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Richard Huxton
Adrian Klaver wrote: >> Yes, my perception also is that it's getting harder and harder to keep >> up with the list traffic; so something is happening that a simple >> volume count doesn't capture. > > I am still relatively new to Postgres, but my impression is that the > questions > have gotten

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 7:42 PM, Ciprian Dorin Craciun <[EMAIL PROTECTED]> wrote: > On Fri, Nov 21, 2008 at 7:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> "Ciprian Dorin Craciun" <[EMAIL PROTECTED]> writes: >>> On Fri, Nov 21, 2008 at 6:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote: Not sure if i

Re: [GENERAL] Prepared statement already exists

2008-11-21 Thread Daniel Verite
Alvaro Herrera wrote: In this case, why not just prepare all the needed statements at the first use of the session by the pool software? In theory yes, but I can't imagine how it could be done in practice. The pool software is typically a middleware and the application isn't even awa

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Greg Smith
On Fri, 21 Nov 2008, Tom Lane wrote: Not sure if it applies to your real use-case, but if you can try doing the COPY from a local file instead of across the network link, it might go faster. The fact that the inserts are reported as fast initially but slow as the table and index size grow mea

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 7:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Ciprian Dorin Craciun" <[EMAIL PROTECTED]> writes: >> On Fri, Nov 21, 2008 at 6:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >>> Not sure if it applies to your real use-case, but if you can try doing >>> the COPY from a local fil

Re: [GENERAL] Packaging problem: using myspell dictionaries for tsearch2

2008-11-21 Thread Tom Lane
Martin Pitt <[EMAIL PROTECTED]> writes: > - Reportedly PostgreSQL expects those myspell files to be encoded in >the server encoding. This is incorrect, at least as of 8.3 --- they are supposed to be utf-8 always. regards, tom lane -- Sent via pgsql-general mailing l

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Tom Lane
Ron Mayer <[EMAIL PROTECTED]> writes: > Joshua D. Drake wrote: >> Its because we eliminated the -patches mailing list. > That's part of it. I've added -patches to the graph at > http://0ape.com/postgres_mailinglist_size/ as well as > a graph of hackers+patches combined; and it still looks > like

[GENERAL] Packaging problem: using myspell dictionaries for tsearch2

2008-11-21 Thread Martin Pitt
Hello all, for providing proper English stemming support in searches (tsearch2 on PostgreSQL 8.3), tsearch needs the British/American myspell dictionaries. However, this system currently seems to be very inconvenient to packagers like me (I'm responsible for the Debian and Ubuntu packages of Postg

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Ron Mayer
Joshua D. Drake wrote: On Fri, 2008-11-21 at 08:18 -0800, Ron Mayer wrote: Bruce Momjian wrote: Tom Lane wrote: ... harder to keep up with the list traffic; so something is happening that a simple volume count doesn't capture. If measured in "bytes of the gzipped mbox" it ... Its because we

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Tom Lane
"Ciprian Dorin Craciun" <[EMAIL PROTECTED]> writes: > On Fri, Nov 21, 2008 at 6:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> Not sure if it applies to your real use-case, but if you can try doing >> the COPY from a local file instead of across the network link, it >> might go faster. Also, as alr

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > On Fri, 2008-11-21 at 08:18 -0800, Ron Mayer wrote: >> If measured in "bytes of the gzipped mbox" it looks like there's a >> *huge* increase of volume on Hackers in the past 3 months - well >> over twice the historical levels; and maybe 4X 2002-2006.

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 6:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Ciprian Dorin Craciun" <[EMAIL PROTECTED]> writes: >> In short the data is inserted by using COPY sds_benchmark_data >> from STDIN, in batches of 500 thousand data points. > > Not sure if it applies to your real use-case, b

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
Thank's for your info! Please see below... On Fri, Nov 21, 2008 at 4:14 PM, Rafael Martinez <[EMAIL PROTECTED]> wrote: > Ciprian Dorin Craciun wrote: > [] >> >> So what can I do / how could I optimize the use of Postgres for this >> usage? >> > > Hello, here you have some com

Re: [GENERAL] Prepared statement already exists

2008-11-21 Thread Alvaro Herrera
Daniel Verite wrote: > Also contrary to prepared statements, maybe that cache would be shared > between connections, and that would be excellent, since it fits the > typical usage pattern of websites: a high-throughput of a small set of > low-latency queries, fired from pooled connections.

Re: Res: [GENERAL] converter pgplsql funcion

2008-11-21 Thread Richard Huxton
paulo matadr wrote: > > -- Executando consulta: > create or replace function clean_string(_p_dado character varying) ^^^ clea[n]_string > ** Erro ** > > ERROR: function clear_string(character varying) does not exist ^^^ clea[r]

Re: [GENERAL] Prepared statement already exists

2008-11-21 Thread Daniel Verite
WireSpot wrote: So it would eliminate the possibility of clashes, but do nothing for statement reuse. Agreed. What would make it all the way better was if the database would do that last step for you as well: automatically recognize statements that do the same thing and return the a

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Joshua D. Drake
On Fri, 2008-11-21 at 08:18 -0800, Ron Mayer wrote: > Bruce Momjian wrote: > > Tom Lane wrote: > >> Bruce Momjian <[EMAIL PROTECTED]> writes: > >>> I also was confused by its flatness. I am finding the email traffic > >>> almost impossible to continue tracking, so something different is > >>> happ

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Adrian Klaver
On Thursday 20 November 2008 7:59:31 pm Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> So, to a first approximation, the PG list traffic has been constant > >> since 2000. Not the result I expected. > > > > I also was confused by its flatness. I am finding th

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Ron Mayer
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: I also was confused by its flatness. I am finding the email traffic almost impossible to continue tracking, so something different is happening, but it seems it is not volume-related. Yes, my perception also is tha

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Alvaro Herrera
Sam Mason wrote: > the following has links to more: > > http://markmail.org/search/?q=list:org.postgresql Wow, the spanish list is the 3rd in traffic after hackers and general! -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting,

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Tom Lane
"Ciprian Dorin Craciun" <[EMAIL PROTECTED]> writes: > In short the data is inserted by using COPY sds_benchmark_data > from STDIN, in batches of 500 thousand data points. Not sure if it applies to your real use-case, but if you can try doing the COPY from a local file instead of across the net

Re: [GENERAL] converter pgplsql funcion

2008-11-21 Thread Pavel Stehule
2008/11/21 paulo matadr <[EMAIL PROTECTED]>: > not working.. i try to explain my dought > my idea is: > 1- create a table for storage special caracters, > create table caracteres( > caracter character varying > ); > insert into caracteres values(':'); > insert into caracteres values(';'); > insert

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Nikolas Everett
You might want to look into how OpenNMS uses RRDTool. It is able to handle a huge number of nodes by queuing inserts into the RRDs and using JRobin. I'm not sure if it is a great solution for what you are looking for, but I've found its performance scales quite well. I'm getting well over 500 up

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Joshua D. Drake
On Fri, 2008-11-21 at 10:43 -0300, Alvaro Herrera wrote: > Tom Lane wrote: > Markmail shows some graphs. The one on the "main page" gives the > traffic for all the lists: > http://pgsql.markmail.org/ > > If you search for "pgsql-general" you get a graph for that list: > http://pgsql.markmail.org

Res: [GENERAL] converter pgplsql funcion

2008-11-21 Thread paulo matadr
not working.. i try to explain my dought my idea is: 1- create a table for storage special caracters, create table caracteres( caracter character varying ); insert into caracteres values(':'); insert into caracteres values(';'); insert into caracteres values('<'); insert into caracteres values('=')

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 3:29 PM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: > see, I am affraid of the part when it says "randomly", because you probably > used random(), which isn't the fastest thing on earth :) I can assure you this is not the problem... The other storage engines work qu

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Sam Mason
On Fri, Nov 21, 2008 at 02:50:45PM +0200, Ciprian Dorin Craciun wrote: > Currently I'm benchmarking the following storage solutions for this: > * Hypertable (http://www.hypertable.org/) -- which has good insert > rate (about 250k inserts / s), but slow read rate (about 150k reads / > s); (t

[Fwd: Re: [GENERAL] return MAX and when it happened]

2008-11-21 Thread Scara Maccai
Can someone answer me? Or do I have to ask this in the hackers list? I don't get from the docs: do I have to call get_call_result_type(fcinfo, NULL, &tupdesc) every time? I mean: the only example I've found about returning Composite Types talks about returning sets as well (34.9.10. Returni

Re: [GENERAL] Returning schema name with table name

2008-11-21 Thread Pavel Stehule
2008/11/21 Andrus <[EMAIL PROTECTED]>: > SELECT oid, relname::char(35) as Table_Name, > pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as > Total_Table_Size > FROM pg_class > where pg_total_relation_size(oid)/(1024*1024)>0 > ORDER BY pg_total_relation_size(oid) desc > add SELECT n.

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Sam Mason
On Thu, Nov 20, 2008 at 10:59:31PM -0500, Tom Lane wrote: > Yes, my perception also is that it's getting harder and harder to keep > up with the list traffic; so something is happening that a simple > volume count doesn't capture. > > Does anyone have the data to break it down per mailing list? T

Re: [GENERAL] Prepared statement already exists

2008-11-21 Thread Sam Mason
On Fri, Nov 21, 2008 at 09:55:11AM +0200, WireSpot wrote: > What would make it all the way better was if the database would do > that last step for you as well: automatically recognize statements > that do the same thing and return the already existing handle. This is somewhat difficult; things to

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Rafael Martinez
Ciprian Dorin Craciun wrote: [] > > So what can I do / how could I optimize the use of Postgres for this > usage? > Hello, here you have some comments that will probably help you to get more from this test machine .. > > * test machine: Linux (Ubuntu 8.04 x64), IBM x37

[GENERAL] Returning schema name with table name

2008-11-21 Thread Andrus
SELECT oid, relname::char(35) as Table_Name, pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as Total_Table_Size FROM pg_class where pg_total_relation_size(oid)/(1024*1024)>0 ORDER BY pg_total_relation_size(oid) desc returns table names with size greater than 1 MB How to modify

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Alvaro Herrera
Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > I know that my email (I am pretty sure I am subscribed to at least as > > many lists as you) has been on a steady incline, especially through > > -general and -hackers. > > I would have said the same, which is why I find it notewo

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Grzegorz Jaśkiewicz
see, I am affraid of the part when it says "randomly", because you probably used random(), which isn't the fastest thing on earth :)

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 3:18 PM, Grzegorz Jaśkiewicz <[EMAIL PROTECTED]> wrote: > you'll have to provide us with some sort of test-case to get some answers, > please. (set of scripts, queries, etc). Bellow is the content of my original post. Inside I mention exactly the may the benchmark was c

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Grzegorz Jaśkiewicz
you'll have to provide us with some sort of test-case to get some answers, please. (set of scripts, queries, etc).

Re: [GENERAL] converter pgplsql funcion

2008-11-21 Thread Pavel Stehule
Hello create or replace function clean_string(_p_dado varchar) returns varchar as $$ declare _v_clean_string varchar := _p_dado; _c varchar; begin for _c in select caracter from caracters loop _v_clean_string := replace(_c_clean_string, _c); end loop; return _c; end; $$ language plpg

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
On Fri, Nov 21, 2008 at 2:55 PM, Gerhard Heift <[EMAIL PROTECTED]> wrote: > On Fri, Nov 21, 2008 at 02:50:45PM +0200, Ciprian Dorin Craciun wrote: >> Hello all! >> >> I would like to ask some advice about the following problem >> (related to the Dehems project: http://www.dehems.eu/ ): >>

Re: [GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Gerhard Heift
On Fri, Nov 21, 2008 at 02:50:45PM +0200, Ciprian Dorin Craciun wrote: > Hello all! > > I would like to ask some advice about the following problem > (related to the Dehems project: http://www.dehems.eu/ ): > * there are some clients; (the clients are in fact house holds;) > * each

[GENERAL] Using Postgres to store high volume streams of sensor readings

2008-11-21 Thread Ciprian Dorin Craciun
Hello all! I would like to ask some advice about the following problem (related to the Dehems project: http://www.dehems.eu/ ): * there are some clients; (the clients are in fact house holds;) * each device has a number of sensors (about 10), and not all the clients have the same s

Re: [GENERAL] inherit table and its data

2008-11-21 Thread Christian Schröder
Dilyan Berkovski wrote: I am using PostgreSQL 8.2, and I am interested in creating a table B that inherits table A, but with all it's data! create table B {a int} inherits A, just adds the structure of table A, not its data. PostgreSQL's inheritance works the other way around: If table B inh

[GENERAL] converter pgplsql funcion

2008-11-21 Thread paulo matadr
I work with oracle and have poor experience in pg/plsql. anybody can help me with translate from pl/sql in pg/plsql in code below: CREATE OR REPLACE FUNCTION clean_string(p_dado varchar2) RETURN varchar2 IS v_clean_string varchar(4000); BEGIN v_clean_string := p_dado; for r in (sel

Re: [GENERAL] [pgsql-general] cant find postgres executable after initdb

2008-11-21 Thread Willy-Bas Loos
Correction, this was all on Windows Server 2003 Web Edition. I can't believe i have to reboot the whole sever just to restart the database cluster. What is it that the installer does differently from initdb alone? -- "Patriotism is the conviction that your country is superior to all others because

[GENERAL] long vacuum full, gin index and unusually long delete

2008-11-21 Thread Ivan Sergio Borgonovo
It does look that on the box I had the gin index problem I'm having other "strange" behaviours... VACUUM FULL may take too long and suddenly a delete that took around 2 min now seems it is never ending even if I just vacuumed full and restarted the server. I'm wondering if too high maintenance_wor

Re: [GENERAL] where in (select array)

2008-11-21 Thread Marcus Engene
Richard Huxton wrote: I imagine it's cheaper disk & dump wise to do the array thing in this and some other similar usages, and therefore it would be nice to have a non-ugly usage pattern. Don't imagine, test. And then factor in the cost of fiddling around with arrays when you need to acces

Re: [GENERAL]transaction isolation level in plpgsql function

2008-11-21 Thread Grzegorz Jaśkiewicz
whatever calls the function is responsible for transaction level change, because SELECT BLA(); already by default is wrapped by begin;end; - and you can only change transaction level right after BEGIN; although , I feel your pain, it is not possible.

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Magnus Hagander
Magnus Hagander wrote: > Tom Lane wrote: >> Bruce Momjian <[EMAIL PROTECTED]> writes: >>> Tom Lane wrote: So, to a first approximation, the PG list traffic has been constant since 2000. Not the result I expected. >>> I also was confused by its flatness. I am finding the email traffic >>

Re: [GENERAL]transaction isolation level in plpgsql function

2008-11-21 Thread Pavan Deolasee
On Fri, Nov 21, 2008 at 1:19 PM, Sergey Moroz <[EMAIL PROTECTED]> wrote: > Is there any way to set transaction isolation level inside plpgsql > function? In my case I have no control of transaction before function is > started. > > I don't think there can be any. You are already inside a transacti

Re: [GENERAL] where in (select array)

2008-11-21 Thread Richard Huxton
Marcus Engene wrote: > So with memcached I care less about saving a few mS in select latency > and more about postponing other approaching problems like having the > dbdump manageble. Right now it's a 100MB gzipped dump, which is very > manageable, so where it's possible I'd like to keep the data c

Re: [GENERAL] Postgres mail list traffic over time

2008-11-21 Thread Gregory Williamson
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> So, to a first approximation, the PG list traffic has been constant > >> since 2000. Not the result I expected. > > > I also was confused by its flatness. I am finding the email traffic > > almost impossible to