Re: [GENERAL] Postgres mystery
Hi Guys Thanks again for your responses. You were all RIGHT .. again! a null column was returned, which bombed out the stored procedure. This was resolved using ur advice. Kind Regards,Shaun Clements B.Com (Hons) IST Software Developer Relyant Group ITBusiness ConneXion (Pty) LtdAs service provider for: Relyant Office: +27 (0)31 3674722 Mobile: +27 (0)84 6166777 Fax: +27 (0)31 3055289 Email: [EMAIL PROTECTED] Web Site: www.bcx.co.za" Obstacles are what we see when we take our eyes off the goal "
[GENERAL] Empty date
Hi ,
Thanks your previous help. I have a new question
: how to store emptyvalue to date field ? ( I always get error
message, in vain use any format)In my client be empty value for date
fields (' . . ') , and Iwould like to use
NULL values and empty values also.
Thanks : Zoltan
Re: [GENERAL] Empty date
Szmutku Zoltán wrote:
Hi ,
Thanks your previous help. I have a new question : how to store
empty value to date field ? ( I always get error message, in vain use
any format ) In my client be empty value for date fields ('. .
') , and I would like to use NULL values and empty values also.
How canbe a valid date?
You might want to reconsider your design.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] Drivers for Other Languages
Hi, How difficult is it to write a driver for pgsql (via network or UNIX domain sockets) for an as yet unsupported language? Specifically, I'd like a driver for JavaScript, for use with Mozilla JSLib/XPCShell. I presume there isn't one already, so I guess I'll have to write one. So, where can I find the specification for the protocol that I am going to have to talk to the socket? Many thanks. Gordan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Drivers for Other Languages
On Wed, 30 Mar 2005 12:07:06 +0100, Gordan Bobic [EMAIL PROTECTED] wrote: Hi, How difficult is it to write a driver for pgsql (via network or UNIX domain sockets) for an as yet unsupported language? Specifically, I'd like a driver for JavaScript, for use with Mozilla JSLib/XPCShell. I presume there isn't one already, so I guess I'll have to write one. So, where can I find the specification for the protocol that I am going to have to talk to the socket? Actually, there is an SQL XPCOM extention for mozilla. A search for sql at mozilla.org turned up http://www.mozilla.org/projects/sql/ . It turns out that this extention *only* supports postgres as of now, with plans for Someone-elsesSQL ;) support later. Many thanks. Gordan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Drivers for Other Languages
Mike Rylander wrote: On Wed, 30 Mar 2005 12:07:06 +0100, Gordan Bobic [EMAIL PROTECTED] wrote: Hi, How difficult is it to write a driver for pgsql (via network or UNIX domain sockets) for an as yet unsupported language? Specifically, I'd like a driver for JavaScript, for use with Mozilla JSLib/XPCShell. I presume there isn't one already, so I guess I'll have to write one. So, where can I find the specification for the protocol that I am going to have to talk to the socket? Actually, there is an SQL XPCOM extention for mozilla. A search for sql at mozilla.org turned up http://www.mozilla.org/projects/sql/ . Wow! Thanks! That's exactly what I was looking for. :-) It turns out that this extention *only* supports postgres as of now, with plans for Someone-elsesSQL ;) support later. Why would I want to use that? ;-) Gordan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Postgres plpgsql
Title: [GENERAL] Postgres plpgsql Im finding a problem with updating in PGPLSQL Can anyone find anything wrong with this code below. It appears Postgres isnt picking up a change in the dynamic first column. So the same column is being updated. . snip EXECUTE ''UPDATE TABLENAME SET ''||quote_ident(variable1)||''=''||quote_literal(Variable2)||'' where columnname1 =''||quote_literal(variable3)||'' and columnname2 = ''||quote_literal(variable4)||'' and month = 07 and year = 2004''; /snip There is clearly a need for a debugging facility. Kind Regards, Shaun Clements
Re: [GENERAL] Postgres plpgsql
Shaun Clements wrote: Im finding a problem with updating in PGPLSQL Can anyone find anything wrong with this code below. It appears Postgres isnt picking up a change in the dynamic first column. So the same column is being updated. . snip EXECUTE ''UPDATE TABLENAME SET ''||quote_ident(variable1)||''=''||quote_literal(Variable2)||'' where columnname1 =''||quote_literal(variable3)||'' and columnname2 = ''||quote_literal(variable4)||'' and month = 07 and year = 2004''; /snip There is clearly a need for a debugging facility. You're not wrong there. However, you can use RAISE NOTICE - something like RAISE NOTICE ''my vars = % / %'',variable1,variable2; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] plperl doesn't release memory
Title: RE: [GENERAL] plperl doesn't release memory i have a similar problem i'm running PostgreSQL on a PIV with 1GO and Windows 2000 NT i have a large database and a big traitment taking more than 4 hours. during the first hour postgresql use as much memory as virtual memory and i find this strange (growing to more 800MB) and during the execution i get : out of memory Failed on request of size 56 and at the end, postgresql use 300 MB of memory and more than 2GB of virtual memory does this problem can be resolve by tuning postgresql settings ? here are my parameters : shared_buffers = 1000 work_mem = 131072 maintenance_work_mem = 131072 max_stack_depth = 4096 i tried work_mem with 512MB and 2MB and i get the same error... i read all the post, but i don't know how i can configure perl on Windows... thanks in advance Will -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Dan Sugalski Envoyé : vendredi 25 mars 2005 19:34 À : Greg Stark; pgsql-general@postgresql.org Objet : Re: [GENERAL] plperl doesn't release memory At 6:58 PM -0500 3/24/05, Greg Stark wrote: Dan Sugalski [EMAIL PROTECTED] writes: Anyway, if perl's using its own memory allocator you'll want to rebuild it to not do that. You would need to do that if you wanted to use a debugging malloc. But there's no particular reason to think that you should need to do this just to work properly. Two mallocs can work fine alongside each other. They each call mmap or sbrk to allocate new pages and they each manage the pages they've received. They won't have any idea why the allocator seems to be skipping pages, but they should be careful not to touch those pages. Perl will only use a single allocator, so there's not a huge issue there. It's either the external allocator or the internal one, which is for the best since you certainly don't want to be handing back memory to the wrong allocator. That way lies madness and unpleasant core files. The bigger issue is that perl's memory allocation system, the one you get if you build perl with usemymalloc set to yes, never releases memory back to the system -- once the internal allocator gets a chunk of memory from the system it's held for the duration of the process. This is the right answer in many circumstances, and the allocator's pretty nicely tuned to perl's normal allocation patterns, it's just not really the right thing in a persistent server situation where memory usage bounces up and down. It can happen with the system allocator too, though it's less likely. One of those engineering tradeoff things, and not much to be done about it really. -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 8: explain analyze is your friend This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message.
Re: [GENERAL] Postgres plpgsql
Title: RE: [GENERAL] Postgres plpgsql Hi Richard Thanks for your help. Im stuck on this statement. Am I missing the '' ??? snip PERFORM distinct COLUMN1 FROM TABLE where COLUMN1 = || quote_literal(VARIABLE1)'' ||'' AND COLUMN2 = ||quote_literal(VARIABLE2)||; IF NOT FOUND THEN /snip The statement is failing to perform the check in the function. Kind Regards, Shaun Clements -Original Message- From: Richard Huxton [mailto:dev@archonet.com] Sent: 30 March 2005 03:56 PM To: Shaun Clements Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres plpgsql Shaun Clements wrote: Im finding a problem with updating in PGPLSQL Can anyone find anything wrong with this code below. It appears Postgres isnt picking up a change in the dynamic first column. So the same column is being updated. . snip EXECUTE ''UPDATE TABLENAME SET ''||quote_ident(variable1)||''=''||quote_literal(Variable2)||'' where columnname1 =''||quote_literal(variable3)||'' and columnname2 = ''||quote_literal(variable4)||'' and month = 07 and year = 2004''; /snip There is clearly a need for a debugging facility. You're not wrong there. However, you can use RAISE NOTICE - something like RAISE NOTICE ''my vars = % / %'',variable1,variable2; -- Richard Huxton Archonet Ltd
Re: [GENERAL] Postgres mystery
Michael Fuhr wrote:
On Wed, Mar 30, 2005 at 09:11:09AM +0200, Shaun Clements wrote:
Can anyone tell me what the problem is here:
I am inserting into a table via a stored procedure, to a few columns within
the table and postgres is throwing a
CANNOT EXECUTE NULL QUERY.
EXECUTE ''INSERT INTO table (column1, column2, column3,
''||quote_ident(column4)||'') values
(''||quote_literal(RECORDNAME.column1)||'',''||quote_literal(RECORDNAME.colu
mn2)||'',stringvalue,''||quote_literal(RECORDNAME.column2)||'')'';
One of the operands to || is probably NULL, so the entire INSERT
string ends up being NULL. Example:
SELECT 'abc' || 'def';
?column?
--
abcdef
(1 row)
SELECT 'abc' || NULL;
?column?
--
(1 row)
Looks like you need to check for NULL or use COALESCE to convert
NULL to something else.
That something else you ought to do appears in the documentation User
Comments at
http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Zombie processes
My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this query and determined that the query planner seemed to be causing problems with this query. By calling set enable_nestloop = off before executing our query we were able to get the execution time down to under 2 minutes, which is acceptable for our purposes. Thanks for your response- Bjorn -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 1:31 AM To: Peterson, Bjorn Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zombie processes Peterson, Bjorn wrote: I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the query returns appropriate values after about 30 seconds when executed from a psql console. However, when running this query from a Java application on the same machine through the postgres JDBC driver, the Java app hangs on st.executeQuery(). The query shows up in pg_stat_activity for about 3 minutes then goes away, but the process referenced by the pg_stat_activity remains active and consumes 50% of the CPU resources indefinitely until I kill it off. I let it run for over an hour yesterday. I can reproduce this every time I run this query. Two things you should start with: 1. Turn query-logging on in your postgresql.conf so we can see exactly what is happening 2. See if you can simulate this using PREPARE/EXECUTE for the query. I'm guessing that the Java side of things is producing a prepared query and substituting parameters in. That might produce a different plan. First thing is to turn query logging on though, that way you can check exactly what is being executed. -- Richard Huxton Archonet Ltd This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Postgres Processing Help !!!!
Hello to all, I have a problem with the use of temporary tables to have if somebody has an idea. Handling an application that it requires to process 6,000 registries Parents and the processing of each one requires to process N registries Children; Each teacher is sent to process in an own session (you do not synchronize) creating temporary tables and unloading them after which he finishes this session and he initiates with another one. The problem this in which the first registries soon process relatively fast but the response time grows exponentially and finally a considered process of 2 hours takes 14. And but it is that the about data base that when single initiating weighed 600 Mg turns to 12 GB. and after applying to him to a VACUUM and a REINDEX returns to 600 Mb. Somebody has an idea of because?. _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] libpq usage in Samba
I'm trying to figure out why Samba is failing at high loads if using Postgres as a backend (7.3.9 in my setup). On startup in makes one connection only against the database which is shared among all the Samba processes. In turn, each issue a PQexec() over the same connection (a SELECT, actually). Is that a safe use? I would think it is not, but not really sure. I mean, does libpq multiplex queries and handle concurrency correctly in a such a scenario? Thanks. Fernando. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Ayuda con procesamiento en Postgres !!!!
Hola a todos, tengo un problema con el uso de tablas temporales haber si alguien tiene una idea. Manejo una aplicacion que requiere procesar 6.000 registros Padres y el procesamiento de cada uno requiere procesar N registros Hijos; Cada maestro es enviado a procesar en una sesion propia (no simultaneas) creando tablas temporales y descargandolas luego de que termina dicha sesion e inicia con otro. Basicamente el problema esta en que los primeros registros los procesa relativamente rapido pero luego el tiempo de respuesta crece exponencialmente y finalmente un proceso estimado de 2 horas toma 14. Y lo pero es que la base de datos que al iniciar solo pesaba unos 600 Mg se convierte a 12 GB. y luego de aplicarle un VACUUM y un REINDEX regresa a 600 Mb. Alguien tiene una idea del porque?. _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgres plpgsql
On Mar 30, 2005, at 9:34 AM, Shaun Clements wrote: PERFORM distinct COLUMN1 FROM TABLE where COLUMN1 = || quote_literal(VARIABLE1)'' ||'' AND COLUMN2 = ||quote_literal(VARIABLE2)||; The first one you posted looked correct. This line above seems to have problems. I think you want this: PERFORM distinct COLUMN1 FROM TABLE where COLUMN1 = || quote_literal(VARIABLE1) || '' AND COLUMN2 = '' || || quote_literal(VARIABLE2) || ; Generally PERFORM is used for side effects only. I don't see why you would use PERFORM unless this line will change the database somehow. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] libpq usage in Samba
Fernando Schapachnik [EMAIL PROTECTED] writes: I'm trying to figure out why Samba is failing at high loads if using Postgres as a backend (7.3.9 in my setup). On startup in makes one connection only against the database which is shared among all the Samba processes. In turn, each issue a PQexec() over the same connection (a SELECT, actually). Is that a safe use? I would think it is not, but not really sure. I mean, does libpq multiplex queries and handle concurrency correctly in a such a scenario? No, it doesn't. You need to figure out a way to have each Samba process open its own connection. -Doug ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Zombie processes
Richard- The prepare/execute problem would make sense to me, except that all of the parameters in the query are integers and are being appended to the actual query string (for various reasons) prior to calling prepareStatement() rather than being set as parameters. The planner should have everything right from the start. Does this seem to you to be a java caveat, or is it an area where the query planner could be improved? Thanks- Bjorn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peterson, Bjorn Sent: Wednesday, March 30, 2005 8:25 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zombie processes My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this query and determined that the query planner seemed to be causing problems with this query. By calling set enable_nestloop = off before executing our query we were able to get the execution time down to under 2 minutes, which is acceptable for our purposes. Thanks for your response- Bjorn -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 1:31 AM To: Peterson, Bjorn Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zombie processes Peterson, Bjorn wrote: I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the query returns appropriate values after about 30 seconds when executed from a psql console. However, when running this query from a Java application on the same machine through the postgres JDBC driver, the Java app hangs on st.executeQuery(). The query shows up in pg_stat_activity for about 3 minutes then goes away, but the process referenced by the pg_stat_activity remains active and consumes 50% of the CPU resources indefinitely until I kill it off. I let it run for over an hour yesterday. I can reproduce this every time I run this query. Two things you should start with: 1. Turn query-logging on in your postgresql.conf so we can see exactly what is happening 2. See if you can simulate this using PREPARE/EXECUTE for the query. I'm guessing that the Java side of things is producing a prepared query and substituting parameters in. That might produce a different plan. First thing is to turn query logging on though, that way you can check exactly what is being executed. -- Richard Huxton Archonet Ltd This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plperl doesn't release memory
As I understand it, a single execution of a pl/perl function will not be affected by the perl memory issue, so I don't think that is your problem. My guess is that you are reading a large query into perl, so the whole thing will be kept in memory (and you can't use more memory than you have). For a large query, this can be a huge amount of memory indeed. You could use another language like plpgsql that can support cursors/looping over query results or, in plperl you could use DBI (not spi_exec_query) and loop over query results. Hope this helps, Sean On Mar 30, 2005, at 9:33 AM, FERREIRA William (COFRAMI) wrote: i have a similar problem i'm running PostgreSQL on a PIV with 1GO and Windows 2000 NT i have a large database and a big traitment taking more than 4 hours. during the first hour postgresql use as much memory as virtual memory and i find this strange (growing to more 800MB) and during the execution i get : out of memory Failed on request of size 56 and at the end, postgresql use 300 MB of memory and more than 2GB of virtual memory does this problem can be resolve by tuning postgresql settings ? here are my parameters : shared_buffers = 1000 work_mem = 131072 maintenance_work_mem = 131072 max_stack_depth = 4096 i tried work_mem with 512MB and 2MB and i get the same error... i read all the post, but i don't know how i can configure perl on Windows... thanks in advance Will -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] la part de Dan Sugalski Envoyé : vendredi 25 mars 2005 19:34 À : Greg Stark; pgsql-general@postgresql.org Objet : Re: [GENERAL] plperl doesn't release memory At 6:58 PM -0500 3/24/05, Greg Stark wrote: Dan Sugalski [EMAIL PROTECTED] writes: Anyway, if perl's using its own memory allocator you'll want to rebuild it to not do that. You would need to do that if you wanted to use a debugging malloc. But there's no particular reason to think that you should need to do this just to work properly. Two mallocs can work fine alongside each other. They each call mmap or sbrk to allocate new pages and they each manage the pages they've received. They won't have any idea why the allocator seems to be skipping pages, but they should be careful not to touch those pages. Perl will only use a single allocator, so there's not a huge issue there. It's either the external allocator or the internal one, which is for the best since you certainly don't want to be handing back memory to the wrong allocator. That way lies madness and unpleasant core files. The bigger issue is that perl's memory allocation system, the one you get if you build perl with usemymalloc set to yes, never releases memory back to the system -- once the internal allocator gets a chunk of memory from the system it's held for the duration of the process. This is the right answer in many circumstances, and the allocator's pretty nicely tuned to perl's normal allocation patterns, it's just not really the right thing in a persistent server situation where memory usage bounces up and down. It can happen with the system allocator too, though it's less likely. One of those engineering tradeoff things, and not much to be done about it really. -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 8: explain analyze is your friend This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Upgrade data
On Wed, 2005-03-30 at 01:33, Richard Huxton wrote: josue wrote: Hello list, I need to upgrade my dbs from 743 to 801, current data size is around 5GB, I've tried this way: ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981 but is too slow, any idea or suggestion to properly upgrade my dbs, I also have blobs stored there. Try pg_dump followed by pg_restore - both using version 8.0.1. If that's still too slow, the only other option is to dump/restore and then setup replication between the old and new databases to bring your new system up to date. Slony can be used for this. Actually, if you're gonna use slony, the only thing you need to dump and restore to the target database is the schema, slony will move all the data for you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Zombie processes
Peterson, Bjorn wrote: My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this query and determined that the query planner seemed to be causing problems with this query. By calling set enable_nestloop = off before executing our query we were able to get the execution time down to under 2 minutes, which is acceptable for our purposes. Tampering with the enable_xxx settings is NOT recommended for production systems. You might be better off posting the EXPLAIN ANALYSE details to the performance list. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Upgrade data
Scott Marlowe wrote: On Wed, 2005-03-30 at 01:33, Richard Huxton wrote: josue wrote: Hello list, I need to upgrade my dbs from 743 to 801, current data size is around 5GB, I've tried this way: ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981 but is too slow, any idea or suggestion to properly upgrade my dbs, I also have blobs stored there. Try pg_dump followed by pg_restore - both using version 8.0.1. If that's still too slow, the only other option is to dump/restore and then setup replication between the old and new databases to bring your new system up to date. Slony can be used for this. Actually, if you're gonna use slony, the only thing you need to dump and restore to the target database is the schema, slony will move all the data for you. Thanks for the anwser, currently I'm using pg_dump to create a tar file and I'll restore it with pg_restore -- Sinceramente, Josué Maldonado. ... Cuando era joven leía casi siempre para aprender; hoy, a veces, leo para olvidar. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Is the FTP server down?
I get time out errors from all mirrors when trying to download 8.0 win binaries. Apologies if this is the wrong forum for this. thanks. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] limited disk space
We need to run a server (7.4.5, Solaris 9/Intel) in an environment with a defined limit on disk size. We know basically the set of data we will be working with, and can size the disk space accordingly, but there will be a fair amount of update churn in the data. We are running autovacuum, but we still seem to be running out of disk space in our long running tests. The testers claim that the disk usage is not going down with a VACUUM FULL, but I have not verified that independently. Given that our "real" dataset is fairly fixed, and the growth in the database size is due to updates, I'm wondering if there is a way that I can allocate enough disk space at the outset to allow the database to have a large enough "working set" of free pages so that once it reaches a certain threshold it doesn't have to grow the database files anymore. I'm also wondering how WAL settings may affect the disk usage. It's not an option to place the logs on a separate device in this case, so I imagine I want to limit the size there, too. Is anybody running postgres in a similar constrained environment, or are there any general tips on controlling disk usage that somebody could point me to? Thanks. - DAP--David Parker Tazz Networks (401) 709-5130
Re: [GENERAL] Oracle Migration. Don't Care about the Corbomite Maneuver, Spock.
On Wed, Mar 30, 2005 at 00:35:25 -, Mohan, Ross [EMAIL PROTECTED] wrote: Any pointers? The online documentation is very good. For generic Postrges questions, that is probably your best resource. There may be better places to get answers about questions on converting from Oracle to Postgres. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Catch of ERROR in PLPGSQL
Hi All Does anyone know if Postgres supports Catching of errors, from an EXECUTE, for Procedural Language Postgres. Thanks in advance Kind Regards,Shaun Clements
Re: [GENERAL] Catch of ERROR in PLPGSQL
Shaun Clements wrote: Hi All Does anyone know if Postgres supports Catching of errors, from an EXECUTE, for Procedural Language Postgres. In version 8, yes. See the manuals (35.7.5. Trapping Errors) for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Catch of ERROR in PLPGSQL
Try a Begin ... block to watch ... Exception When error_code then ... What to do ... End; block, it works to me, it is better explained in the manual.. - Original Message - From: Richard Huxton dev@archonet.com To: Shaun Clements [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, March 30, 2005 11:44 AM Subject: Re: [GENERAL] Catch of ERROR in PLPGSQL Shaun Clements wrote: Hi All Does anyone know if Postgres supports Catching of errors, from an EXECUTE, for Procedural Language Postgres. In version 8, yes. See the manuals (35.7.5. Trapping Errors) for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] limited disk space
David Parker wrote: Is anybody running postgres in a similar constrained environment, or are there any general tips on controlling disk usage that somebody could point me to? PostgreSQL is not particularly tuned to such scenarios. The only chance you have to control disk usage is to vacuum and checkpoint a lot. There is no general use only X bytes control, nor a combination of controls that amount to such. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Upgrade data
I did a pg_dumpall and this sentence to restore it back ./psql template1 -U postgres -p 9981 /home2/tmp/dbtest.tar and got this error cannot allocate memory for output buffer dbtest.tar is around 1.7 Gb, could anyone please bring some help to update my 7.4 db to 8.0 Thanks in advance, -- Sinceramente, Josué Maldonado. ... Toda la justicia social descansa en estos dos axiomas: El robo es punible y el producto del robo es sagrado. Anatole France. ( Jacques-Anatole Thibault ) Escritor francés. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] what's wrong with postgres 8
Hi, I have a 64 bit Linux box with 32GB RAM and disk space approx 450 GB. I am running a benchmark using filesystem type ext3 with data=writeback both on Postgres 7 and 8. Time taken for benchmark run is very less compared to the one on Postgres 8. I am esentially trying to get performance improvement by using data=writeback. If I do not use data=writeback both for Postgres 7 and 8, I am getting almost the same numbers for benchmark run. Why is this happening. why data=writeback is giving good results for Postgres7 and not for Postgres 8?? I am using the following settings both for Postgres 7 and 8: shared_buffers = 3000 work_mem = 10 maintenance_work_mem = 10 max_fsm_pages = 20 bgwriter_percent = 0 wal_buffers = 64 checkpoint_segments = 256 checkpoint_timeout = 1800 effective_cache_size = 25 random_page_cost = 2 stats_start_collector = true stats_command_string = true stats_row_level = true Also, I would like to know - if using a Postgres 7 database dump to restore database on Postgres 8 can cause this - using Postgres 7 jdbc driver for Postgres 8 can lead to a time performance degradation (I am using postgres 7 driver now since using Postgres 8 driver I am getting an error java.sql.SQLException: ERROR: operator does not exist: smallint = boolean. FYI: I have runstats on all tables both for Postgres 7 and 8 after doing a restore. Regards, Vinita Bansal _ News, views and gossip. http://www.msn.co.in/Cinema/ Get it all at MSN Cinema! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Oracle Migration. Don't Care about the Corbomite Maneuver, Spock.
BW - Thanksnow that it's downloaded and printing..i see what you mean. And I agree. I am utterly STUNNED and happy to see PG uses GA for query optimization; that, by itself, just makes this whole process that much more fun... - RM -Original Message- From: Bruno Wolff III [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 12:36 PM To: Mohan, Ross Cc: pgsql-general@postgresql.org Subject: Re: Oracle Migration. Don't Care about the Corbomite Maneuver, Spock. On Wed, Mar 30, 2005 at 00:35:25 -, Mohan, Ross [EMAIL PROTECTED] wrote: Any pointers? The online documentation is very good. For generic Postrges questions, that is probably your best resource. There may be better places to get answers about questions on converting from Oracle to Postgres. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is the FTP server down?
I get time out errors from all mirrors when trying to download 8.0 win binaries. Apologies if this is the wrong forum for this. I just tried a couple, and they all seem to be working. Perhaps you have a firewall that's blocking you out of ftp connections? //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Restore to a database with another name?
Hi All, I want to move my development database (7.2.3) to my production server but the production server database has a different name. What is the procedure for restoring a database pg_dump to another machine with a different database name? Thanks in advance, Phil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Restore to a database with another name?
On Wed, 2005-03-30 at 14:28, phil campaigne wrote: Hi All, I want to move my development database (7.2.3) to my production server but the production server database has a different name. What is the procedure for restoring a database pg_dump to another machine with a different database name? pg_dump -h source_host source_dbname |psql -h dest_host dest_dbname ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [ODBC] delphi access question?
On 30 Mar 2005 at 15:15, Joel Fradkin wrote: I can run a sql statement ok, even in sql builder I see all the fields. But the returned result set appears to be missing some of the fields. Probably a silly question, but do you have all the fields SELECTed in the SQL statement in the TQuery (or whatever you're using)? If so, are you using persistent fields? If you are, have you tried deleting them all from the fields editor and rebuilding them (right- click on the empty fields editor and click on Add all fields)? --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Debugging deadlocks
On Sun, Mar 27, 2005 at 01:37:44AM -0700, Michael Fuhr wrote: [] The current implementation supports only exclusive row-level locks (SELECT FOR UPDATE), but I think Alvaro might be working on shared row-level locks for a future release. Hmm ... are you saying that SELECT FOR UPDATE exquires an exclusive lock on the row in question in the sense that it conflicts with other *readers* trying to access that row? The documentation would appear to say otherwise: -- snip -- Row-level locks do not affect data querying; they block writers to the same row only. To acquire a row-level lock on a row without actually modifying the row, select the row with SELECT FOR UPDATE. -- snap -- (from: http://www.vitavoom.com/postgresql-docs/explicit-locking.html) and -- snip -- A row-level lock on a specific row is automatically acquired when the row is updated (or deleted or marked for update). The lock is held until the transaction commits or rolls back. Row-level locks do not affect data querying; they block writers to the same row only. -- snap -- (from: http://www.postgresql.org/docs/7.4/static/explicit-locking.html) but then I don't understand the original poster's problem at all, since his queries are only *reading* the referenced tables?? Regards, Frank ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Restore to a database with another name?
Scott Marlowe wrote: On Wed, 2005-03-30 at 14:28, phil campaigne wrote: Hi All, I want to move my development database (7.2.3) to my production server but the production server database has a different name. What is the procedure for restoring a database pg_dump to another machine with a different database name? pg_dump -h source_host source_dbname |psql -h dest_host dest_dbname Hi Scott, Unfortunately my production machine is remote and I must transfer it via FTP. How would that change your suggestion? Thanks, Phil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Restore to a database with another name?
On Wed, 30 Mar 2005 15:58:02 -0500, phil campaigne [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Wed, 2005-03-30 at 14:28, phil campaigne wrote: Hi All, I want to move my development database (7.2.3) to my production server but the production server database has a different name. What is the procedure for restoring a database pg_dump to another machine with a different database name? pg_dump -h source_host source_dbname |psql -h dest_host dest_dbname Hi Scott, Unfortunately my production machine is remote and I must transfer it via FTP. How would that change your suggestion? You break it into two separate commands at the pipe, and use pg_restore to import it back in on the target box. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Debugging deadlocks
On Wed, Mar 30, 2005 at 10:59:52PM +0200, [EMAIL PROTECTED] wrote: On Sun, Mar 27, 2005 at 01:37:44AM -0700, Michael Fuhr wrote: The current implementation supports only exclusive row-level locks (SELECT FOR UPDATE), but I think Alvaro might be working on shared row-level locks for a future release. Hmm ... are you saying that SELECT FOR UPDATE exquires an exclusive lock on the row in question in the sense that it conflicts with other *readers* trying to access that row? The documentation would appear to say otherwise: I'm saying that foreign key checks use SELECT FOR UPDATE to ensure that the referenced key doesn't change while the transaction is pending, and that SELECT FOR UPDATE conflicts with other SELECT FOR UPDATE queries. Therefore, if concurrent transactions insert records into a table that has a non-deferred foreign key constraint, and if the foreign key values are the same, then one of the transactions will block. Example: CREATE TABLE foo ( fooid integer PRIMARY KEY ); CREATE TABLE bar ( barid serial PRIMARY KEY, fooid integer NOT NULL REFERENCES foo ); INSERT INTO foo (fooid) VALUES (1); If we now have two transactions that both insert records into bar with the same value for fooid, then one of the transactions will block: T1: BEGIN; T2: BEGIN; T1: INSERT INTO bar (fooid) VALUES (1); T2: INSERT INTO bar (fooid) VALUES (1); -- blocks Transaction T2 blocks because both transactions have done something like SELECT 1 FROM foo WHERE fooid = 1 FOR UPDATE to ensure that the referenced key can't change until the transaction completes. So even though both transactions are only reading the referenced table (foo), one has blocked the other. Note that SELECT queries without FOR UPDATE won't block -- it's other FOR UPDATE queries that block, even though they're only reading. I think Alvaro is working on a new locking mechanism that will allow transactions to prevent a record from being modified without blocking other transactions doing the same. Alvaro (or somebody else), please correct me if I'm mistaken, but that's what I've understood from discussions elsewhere. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Restore to a database with another name?
Lonni J Friedman wrote: On Wed, 30 Mar 2005 15:58:02 -0500, phil campaigne [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Wed, 2005-03-30 at 14:28, phil campaigne wrote: Hi All, I want to move my development database (7.2.3) to my production server but the production server database has a different name. What is the procedure for restoring a database pg_dump to another machine with a different database name? pg_dump -h source_host source_dbname |psql -h dest_host dest_dbname Hi Scott, Unfortunately my production machine is remote and I must transfer it via FTP. How would that change your suggestion? You break it into two separate commands at the pipe, and use pg_restore to import it back in on the target box. Lonni, Before I try that, I should tell you that I used pg_dump to create the backup. I read in the manual that psql client restores text files from pg_dump. Should I still try pg_restore? thanks, Phil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Restore to a database with another name?
On Wed, 30 Mar 2005 16:24:54 -0500, phil campaigne [EMAIL PROTECTED] wrote: Lonni J Friedman wrote: On Wed, 30 Mar 2005 15:58:02 -0500, phil campaigne [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Wed, 2005-03-30 at 14:28, phil campaigne wrote: Hi All, I want to move my development database (7.2.3) to my production server but the production server database has a different name. What is the procedure for restoring a database pg_dump to another machine with a different database name? pg_dump -h source_host source_dbname |psql -h dest_host dest_dbname Hi Scott, Unfortunately my production machine is remote and I must transfer it via FTP. How would that change your suggestion? You break it into two separate commands at the pipe, and use pg_restore to import it back in on the target box. Lonni, Before I try that, I should tell you that I used pg_dump to create the backup. I read in the manual that psql client restores text files from pg_dump. Should I still try pg_restore? I believe that either will work for text file dumps. I've just always used pg_restore. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Debugging deadlocks
On Wed, Mar 30, 2005 at 02:30:58PM -0700, Michael Fuhr wrote: I think Alvaro is working on a new locking mechanism that will allow transactions to prevent a record from being modified without blocking other transactions doing the same. Yeah, and it does work. (I posted the patch two days ago.) alvherre=# create table a (a serial primary key); NOTICE: CREATE TABLE creará una secuencia implícita «a_a_seq» para la columna serial «a.a» NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «a_pkey» para la tabla «a» CREATE TABLE alvherre=# create table b (a int references a); CREATE TABLE alvherre=# insert into a default values; INSERT 0 1 alvherre=# insert into a default values; INSERT 0 1 alvherre=# begin; BEGIN alvherre=# insert into b values (1); INSERT 0 1 Session 2: alvherre=# begin; BEGIN alvherre=# insert into b values (2); INSERT 0 1 alvherre=# insert into b values (1); INSERT 0 1 Session 1: lvherre=# insert into b values (2); INSERT 0 1 alvherre=# commit; COMMIT Session 2: alvherre=# commit; COMMIT You'll notice if you do that on any released version it will deadlock ... Now this can't be applied right away because it's easy to run out of memory (shared memory for the lock table). Say, a delete or update that touches 1 tuples does not work. I'm currently working on a proposal to allow the lock table to spill to disk ... -- Alvaro Herrera ([EMAIL PROTECTED]) La persona que no quería pecar / estaba obligada a sentarse en duras y empinadas sillas/ desprovistas, por cierto de blandos atenuantes (Patricio Vogel) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Debugging deadlocks
Alvaro Herrera [EMAIL PROTECTED] writes: Now this can't be applied right away because it's easy to run out of memory (shared memory for the lock table). Say, a delete or update that touches 1 tuples does not work. I'm currently working on a proposal to allow the lock table to spill to disk ... Is that true even if I'm updating/deleting 1,000 tuples that all reference the same foreign key? It seems like that should only need a single lock per (sub)transaction_id per referenced foreign key. How is this handled currently? Is your patch any worse than the current behaviour? -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] database monitor
When I say database monitor I mean a program that will keep an eye on the database showin what's appening (with transactions, users, memory, etc...) on the database helping the database administrator doing is job. My problem is to know exactly the most importante things what this program must watch to monitorize the database performance. Edson Carvalho __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Triggers: using table's primary key value to update another field
I'm trying to write a generic function that will use the primary key field value to insert/update another field in the same table (see code below). What I'm having trouble with is figuring out how to grab the primary key value of the current row. I tried tacking on a var with the pkey row name to NEW, but that doesn't work (didn't think it would). Is there a way to do this? CREATE FUNCTION add_parent_id () RETURNS TRIGGER AS ' DECLARE pkey TEXT; BEGIN SELECT INTO pkey column_name FROM information_schema.constraint_column_usage WHERE table_name = TG_RELNAME AND constraint_name ILIKE ''%_pkey''; IF NEW.parent_id IS NULL THEN SELECT NEW.pkey INTO NEW.parent_id; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Database monitor (again)
Sorry but perhaps it is a database monitorizer that makes the monitorization on the databases. It's my english Edson Carvalho __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Debugging deadlocks
On Mar 30, 2005, at 4:47 PM, Alvaro Herrera wrote: Now this can't be applied right away because it's easy to run out of memory (shared memory for the lock table). Say, a delete or update that touches 1 tuples does not work. I'm currently working on a proposal to allow the lock table to spill to disk ... That would be most excellent, as I have deadlocks from transactions that do upwards of 100k inserts selecting data from other tables. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Empty date
In my client be empty value for date fields ('. . ') , and I would
like to use NULL values and empty values also.
What is your reason to put empty value ('') as alternative of null value?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] autopackage and Postgresql?
Does anyone know if there is a autopackage for Postgresql? http://www.osnews.com/story.php?news_id=10155 for a article www.autopackage.org for home page I tried and it works really well, and it installed a binary version of inkscape on my Gentoo box without issue. T ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Fw: [GENERAL] Empty date
Hi , Thanks :-(( My english knowledge is big handicap for exact explanation.., but For example : I have a table, that store a production progression . All progression schema is predefined respectively for all different product . Example: Record Step Name Date (default = null) 1. Order 2005.01.10 2. Order confirmation2005.01.11 3. Cutting 2005.01.12 4. Painting . 5. Burning .NULL. When starting a next step , then the date field change to empty ( from null ). The operator see the progression status in the screen. The client program display empty value as 'in the pipeline' and display null values as ' remaining steps' . This is an example, but I use this way often (one field, in field a standard or two or three special values - more explanation ) . In this way I can display a plus information without a plus field. Hmmm. My own stupidity..., but no problem ere now. Thanks again : Zoltan - Original Message - From: Richard Huxton dev@archonet.com To: Szmutku Zoltán [EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 1:01 PM Subject: Re: [GENERAL] Empty date Szmutku Zoltán wrote: Hi, Valid or invalid ... indifferent . MySql can store '. . ' or '.00.00' values to date field. Formerly I use Mysql , and now change to Postgres (single reason : the faster, record level transactions in the Postgre ) . My client classes (VFP 6) use empty date values and NULL values for different signify. If the Postgre not handling the empty date , then more complicated rewrite my older classes to Postgre for me. If you don't want to store dates, you could always use a text-field. Can I ask what the different meanings are for null vs empty date fields? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plperl doesn't release memory
Title: RE: [GENERAL] plperl doesn't release memory Hi, I work with William. In fact, we have already done the procedure in pl/pgsql but it is too slow and we use array which are native in perl. The procedure is recursive, and use request on postgreSQL. According to the evolution of memory use, it seems that no memory is free. I think that comes from the fact we have a recursive procedure. The execution of the procedure take 3 hours and finishes already by an out of memory. Can we oblige pl/perl to free memory for variable ? Or can we configure postgresql to accept this rise in load ? Or another idea ? When the procedure crash, postgresql use 280 MB of memory and 2 Go of virtual memory and on the server we have a message Windows try to increase virtual memory. Thanks in advance, Nicolas Giroire. -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Sean Davis Envoyé : mercredi 30 mars 2005 17:01 À : FERREIRA William (COFRAMI) Cc : Postgresql-General list Objet : Re: [GENERAL] plperl doesn't release memory As I understand it, a single execution of a pl/perl function will not be affected by the perl memory issue, so I don't think that is your problem. My guess is that you are reading a large query into perl, so the whole thing will be kept in memory (and you can't use more memory than you have). For a large query, this can be a huge amount of memory indeed. You could use another language like plpgsql that can support cursors/looping over query results or, in plperl you could use DBI (not spi_exec_query) and loop over query results. Hope this helps, Sean On Mar 30, 2005, at 9:33 AM, FERREIRA William (COFRAMI) wrote: i have a similar problem i'm running PostgreSQL on a PIV with 1GO and Windows 2000 NT i have a large database and a big traitment taking more than 4 hours. during the first hour postgresql use as much memory as virtual memory and i find this strange (growing to more 800MB) and during the execution i get : out of memory Failed on request of size 56 and at the end, postgresql use 300 MB of memory and more than 2GB of virtual memory does this problem can be resolve by tuning postgresql settings ? here are my parameters : shared_buffers = 1000 work_mem = 131072 maintenance_work_mem = 131072 max_stack_depth = 4096 i tried work_mem with 512MB and 2MB and i get the same error... i read all the post, but i don't know how i can configure perl on Windows... thanks in advance Will -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Dan Sugalski Envoyé : vendredi 25 mars 2005 19:34 À : Greg Stark; pgsql-general@postgresql.org Objet : Re: [GENERAL] plperl doesn't release memory At 6:58 PM -0500 3/24/05, Greg Stark wrote: Dan Sugalski [EMAIL PROTECTED] writes: Anyway, if perl's using its own memory allocator you'll want to rebuild it to not do that. You would need to do that if you wanted to use a debugging malloc. But there's no particular reason to think that you should need to do this just to work properly. Two mallocs can work fine alongside each other. They each call mmap or sbrk to allocate new pages and they each manage the pages they've received. They won't have any idea why the allocator seems to be skipping pages, but they should be careful not to touch those pages. Perl will only use a single allocator, so there's not a huge issue there. It's either the external allocator or the internal one, which is for the best since you certainly don't want to be handing back memory to the wrong allocator. That way lies madness and unpleasant core files. The bigger issue is that perl's memory allocation system, the one you get if you build perl with usemymalloc set to yes, never releases memory back to the system -- once the internal allocator gets a chunk of memory from the system it's held for the duration of the process. This is the right answer in many circumstances, and the allocator's pretty nicely tuned to perl's normal allocation patterns, it's just not really the right thing in a persistent server situation where memory usage bounces up and down. It can happen with the system allocator too, though it's less likely. One of those engineering tradeoff things, and not much to be done about it really. -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 8: explain analyze is your friend This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. ---(end of
Re: [GENERAL] Days in month query
Or
select date_part('day', date_trunc('month', '01/10/04') + '1
month'::interval - '1 day'::interval) as days;
or
select date_part('day', to_date('mon', 'Jan') + '1 month'::interval - '1
day'::interval) as days;
Arthur Hoogervorst wrote:
Hi,
Something like this?
SELECT date_part('day',
(date_part('year', '01/10/04' :: date) || '-' ||
date_part('month', '01/10/04' :: date) || '-01') ::date
+ '1 month'::interval
- '1 day'::interval) AS days;
Regards,
Arthur
On Wed, 30 Mar 2005 16:45:43 -0700, Mark Fox [EMAIL PROTECTED] wrote:
Greetings,
Thanks Dan, but I searched for, and scoured, that page before asking
my question. It helped with some of the details, but not on the
general approach. I'll try to restate my problem in a better way:
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't figured it out yet.
Mark
On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit [EMAIL PROTECTED] wrote:
The online documentation has a search function. It would lead you to
this:
http://www.postgresql.org/docs/8.0/static/functions-datetime.html
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Fox
Sent: Wednesday, March 30, 2005 3:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Days in month query
Greetings,
This is more of an SQL question, but since each database server seems
to use it's own syntax for handling dates...
Is there a way to query for the days in a month? For example,
querying for the days in January of this year? Listing the days
between two dates would be useful as well.
I'm sure I saw a query like this somewhere, but I can't track it down.
Just to be clear, there were no tables involved. Just a SELECT
statement that returned all the days in a given month.
Basically, I have a table of events and I'd like to generate a
histogram of how many events occur on the days of a particular month.
What I do now is create a temporary table, fill it with the
appropriate days, and then do a cross join and summation to generate
what I need. This works, but seems messy to me.
Mark
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Catch of ERROR in PLPGSQL
Shaun Clements wrote: Thanks for your response. I unfortunately have to use 7.3. Will try the Exception wrap. It does appear in the manual. No it doesn't. Version 7.3 doesn't have any programmatic exception handling. Perhaps you are reading the wrong version of the manuals? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Upgrade data
josue [EMAIL PROTECTED] writes: I did a pg_dumpall and this sentence to restore it back ./psql template1 -U postgres -p 9981 /home2/tmp/dbtest.tar You need to use 'pg_restore' for tar format dumps. -Doug ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Days in month query
Greetings, This is more of an SQL question, but since each database server seems to use it's own syntax for handling dates... Is there a way to query for the days in a month? For example, querying for the days in January of this year? Listing the days between two dates would be useful as well. I'm sure I saw a query like this somewhere, but I can't track it down. Just to be clear, there were no tables involved. Just a SELECT statement that returned all the days in a given month. Basically, I have a table of events and I'd like to generate a histogram of how many events occur on the days of a particular month. What I do now is create a temporary table, fill it with the appropriate days, and then do a cross join and summation to generate what I need. This works, but seems messy to me. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Days in month query
The online documentation has a search function. It would lead you to this: http://www.postgresql.org/docs/8.0/static/functions-datetime.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Fox Sent: Wednesday, March 30, 2005 3:07 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Days in month query Greetings, This is more of an SQL question, but since each database server seems to use it's own syntax for handling dates... Is there a way to query for the days in a month? For example, querying for the days in January of this year? Listing the days between two dates would be useful as well. I'm sure I saw a query like this somewhere, but I can't track it down. Just to be clear, there were no tables involved. Just a SELECT statement that returned all the days in a given month. Basically, I have a table of events and I'd like to generate a histogram of how many events occur on the days of a particular month. What I do now is create a temporary table, fill it with the appropriate days, and then do a cross join and summation to generate what I need. This works, but seems messy to me. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Debugging deadlocks
On Wed, Mar 30, 2005 at 05:41:04PM -0500, Greg Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Now this can't be applied right away because it's easy to run out of memory (shared memory for the lock table). Say, a delete or update that touches 1 tuples does not work. I'm currently working on a proposal to allow the lock table to spill to disk ... Is that true even if I'm updating/deleting 1,000 tuples that all reference the same foreign key? It seems like that should only need a single lock per (sub)transaction_id per referenced foreign key. Well, in that case you need 1000 PROCLOCK objects, all pointing to the same LOCK object. But it still uses shared memory. How is this handled currently? Is your patch any worse than the current behaviour? With my patch it's useless without a provision to spill the lock table. The current situation is that we don't use the lock table to lock tuples; instead we mark them on disk, in the tuple itself. So we can't really mark a tuple more than once (because we have only one bit to mark); that's why we limit tuple locking to exclusive locking (there's no way to mark a tuple with more than one shared lock). With my patch we need a lot of memory for each tuple locked. This needs to be shared memory. Since shared memory is limited, we can't grab an arbitrary number of locks simultaneously. Thus, deleting a whole table can fail. You haven't ever seen Postgres failing in a DELETE FROM table, have you? -- Alvaro Herrera ([EMAIL PROTECTED]) Java is clearly an example of a money oriented programming (A. Stepanov) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Database monitoring
I´m a estudante and I am making a project where I am developing a database monitor, PostgreSQL is one of the database's that my aplication will monotorize. I have only started working with postgreSQL recently. I would like to know what do you think it is importante and useful to be monitorized in a postgreSQL database. Tank you very much Edson Carvalho Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Days in month query
Greetings, Thanks Dan, but I searched for, and scoured, that page before asking my question. It helped with some of the details, but not on the general approach. I'll try to restate my problem in a better way: What I want is SELECT statement that references no tables but returns the days in a given month. I'm now thinking that I might be able to come up with something using an IN clause and using EXTRACT, but haven't figured it out yet. Mark On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit [EMAIL PROTECTED] wrote: The online documentation has a search function. It would lead you to this: http://www.postgresql.org/docs/8.0/static/functions-datetime.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Fox Sent: Wednesday, March 30, 2005 3:07 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Days in month query Greetings, This is more of an SQL question, but since each database server seems to use it's own syntax for handling dates... Is there a way to query for the days in a month? For example, querying for the days in January of this year? Listing the days between two dates would be useful as well. I'm sure I saw a query like this somewhere, but I can't track it down. Just to be clear, there were no tables involved. Just a SELECT statement that returned all the days in a given month. Basically, I have a table of events and I'd like to generate a histogram of how many events occur on the days of a particular month. What I do now is create a temporary table, fill it with the appropriate days, and then do a cross join and summation to generate what I need. This works, but seems messy to me. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Ayuda con procesamiento en Postgres !!!!
On Wed, 30 Mar 2005, Carlos Roberto Chamorro Mostac wrote: Hola a todos Replied to the user offlist.. Isn't there a spanish list? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Days in month query
How about create type with create function? Make an array type to hold the 12 different month day counts. Give the function year and month as input. Use this to figure out if it is a leap year: (year % 4 == 0 (year % 100 != 0 || year % 400 == 0)) If it is a leap year, then add 1 to days if month is 2. -Original Message- From: Mark Fox [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 3:46 PM To: Dann Corbit Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Days in month query Greetings, Thanks Dan, but I searched for, and scoured, that page before asking my question. It helped with some of the details, but not on the general approach. I'll try to restate my problem in a better way: What I want is SELECT statement that references no tables but returns the days in a given month. I'm now thinking that I might be able to come up with something using an IN clause and using EXTRACT, but haven't figured it out yet. Mark On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit [EMAIL PROTECTED] wrote: The online documentation has a search function. It would lead you to this: http://www.postgresql.org/docs/8.0/static/functions-datetime.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Fox Sent: Wednesday, March 30, 2005 3:07 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Days in month query Greetings, This is more of an SQL question, but since each database server seems to use it's own syntax for handling dates... Is there a way to query for the days in a month? For example, querying for the days in January of this year? Listing the days between two dates would be useful as well. I'm sure I saw a query like this somewhere, but I can't track it down. Just to be clear, there were no tables involved. Just a SELECT statement that returned all the days in a given month. Basically, I have a table of events and I'd like to generate a histogram of how many events occur on the days of a particular month. What I do now is create a temporary table, fill it with the appropriate days, and then do a cross join and summation to generate what I need. This works, but seems messy to me. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Days in month query
Hi,
Something like this?
SELECT date_part('day',
(date_part('year', '01/10/04' :: date) || '-' ||
date_part('month', '01/10/04' :: date) || '-01') ::date
+ '1 month'::interval
- '1 day'::interval) AS days;
Regards,
Arthur
On Wed, 30 Mar 2005 16:45:43 -0700, Mark Fox [EMAIL PROTECTED] wrote:
Greetings,
Thanks Dan, but I searched for, and scoured, that page before asking
my question. It helped with some of the details, but not on the
general approach. I'll try to restate my problem in a better way:
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't figured it out yet.
Mark
On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit [EMAIL PROTECTED] wrote:
The online documentation has a search function. It would lead you to
this:
http://www.postgresql.org/docs/8.0/static/functions-datetime.html
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Fox
Sent: Wednesday, March 30, 2005 3:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Days in month query
Greetings,
This is more of an SQL question, but since each database server seems
to use it's own syntax for handling dates...
Is there a way to query for the days in a month? For example,
querying for the days in January of this year? Listing the days
between two dates would be useful as well.
I'm sure I saw a query like this somewhere, but I can't track it down.
Just to be clear, there were no tables involved. Just a SELECT
statement that returned all the days in a given month.
Basically, I have a table of events and I'd like to generate a
histogram of how many events occur on the days of a particular month.
What I do now is create a temporary table, fill it with the
appropriate days, and then do a cross join and summation to generate
what I need. This works, but seems messy to me.
Mark
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Days in month query
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't figured it out yet.
I have a 'last_day' function (duplicating what the equivalent Oracle
function does), from that you can extract the number of days in the month.
Here's my 'last_day' function:
create or replace function public.last_day(date)
returns date as
'
DECLARE
this_day alias for $1;
declare wk_day date;
BEGIN
wk_day := date_trunc(''month'', this_day) + interval ''1 month''
- interval ''1 day'';
return wk_day;
END
' language 'plpgsql';
--
Mike Nolan
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Ayuda con procesamiento en Postgres !!!!
On Wed, Mar 30, 2005 at 07:14:22PM -0500, Francisco Reyes wrote: On Wed, 30 Mar 2005, Carlos Roberto Chamorro Mostac wrote: Hola a todos Replied to the user offlist.. Isn't there a spanish list? Yeah, there is: pgsql-es-ayuda on the same server. -- Alvaro Herrera ([EMAIL PROTECTED]) A wizard is never late, Frodo Baggins, nor is he early. He arrives precisely when he means to. (Gandalf, en LoTR FoTR) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Database monitoring
When you say a database monitor, do you mean a transaction monitor or something else? This might be related: http://sourceforge.net/projects/starccm/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edson Vilhena de Carvalho Sent: Wednesday, March 30, 2005 3:33 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Database monitoring I´m a estudante and I am making a project where I am developing a database monitor, PostgreSQL is one of the database's that my aplication will monotorize. I have only started working with postgreSQL recently. I would like to know what do you think it is importante and useful to be monitorized in a postgreSQL database. Tank you very much Edson Carvalho Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
