[GENERAL] Unable to run this query
I am not able to run this query in Postgres 8.4 and the OS is Linux Centos 5. I have dump of the DB, but in clients place this query is taking lots of time but no errors. Please suggest me what is the case to rectify this error. select s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner join account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner join (select subsno from getexpiringsubs($1,cast($2 as integer),cast($3 as double precision), $4) as (subsno int,expirydt timestamp without time zone,balcpt double precision)) as e on s.subsno=e.subsno where s.status<=15 and d.domno=$5 order by d.domname,s.expirydt,a.actname It is showing the error as ERROR: THERE IS NO PARAMETER $1 LINE 1: ...no inner join (select subsno from getexpiringsubs($1,cast($2.. -- View this message in context: http://old.nabble.com/Unable-to-run-this-query-tp29073430p29073430.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Unable to run this query
2010/7/5 Srikanth Kata : > > I am not able to run this query in Postgres 8.4 and the OS is Linux Centos 5. > > I have dump of the DB, but in clients place this query is taking lots of > time but no errors. > > Please suggest me what is the case to rectify this error. > > select > s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr > as svcdescr from vwsubsmin s > inner join packages p on s.svcno=p.pkgno > inner join account a on a.actno=s.actno > inner join ssgdom d on a.domno=d.domno > inner join (select subsno from getexpiringsubs($1,cast($2 as > integer),cast($3 as double precision), $4) as > (subsno int,expirydt timestamp without time zone,balcpt double precision)) > as e on s.subsno=e.subsno > where s.status<=15 and d.domno=$5 > order by d.domname,s.expirydt,a.actname > > It is showing the error as ERROR: THERE IS NO PARAMETER $1 > LINE 1: ...no inner join (select subsno from getexpiringsubs($1,cast($2.. probably use a wrong API. Your query uses parameters - $1, $2, ... but you are not define these parameters in your code. Regards Pavel Stehule > -- > View this message in context: > http://old.nabble.com/Unable-to-run-this-query-tp29073430p29073430.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- 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] C-Functions using SPI - Missing Magic Block
Hi people,
after a two days break:
I could compile the following code with Visual C++ Express 2010 under Windows
Server 2003 R2:
/* Use 32-bit timer (provided header file uses 64-bit timer, not
* compatible with Windows postgreSQL versions */
#define _USE_32BIT_TIME_T
#define BUILDING_DLL 1
#include "postgres.h"
#include "fmgr.h" /* PG_MODULE_MAGIC */
#include "executor\spi.h" /* SPI - Server Programming Interface */
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(count_person);
__declspec(dllexport)
Datum count_person(PG_FUNCTION_ARGS) {
int ret;
SPI_connect();
ret = SPI_exec("SELECT count(*) FROM person", 0);
SPI_finish();
PG_RETURN_INT32(ret);
}
The steps I did:
- install GnuWin32 (GetText for Windows) and copy libintl.h to
...\PostgreSQL\8.3\include\server\port\win32
- edit "pg_config.h" and replace #define ENABLE_NLS 1 by #undef ENABLE_NLS
- create new empty DLL-Project and add new C++-File
- Rename file into "filename.c"
- in Visual C++ add:
PostgreSQL\8.3\include\server\port\win32
PostgreSQL\8.3\include\server\
PostgreSQL\8.3\bin
postgres.lib
compile as C-Code !!!
see also:
http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html
http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html
http://www.dbforums.com/postgresql/1626445-how-get-conn-ptr-c-lang-external-function.html
But I guess I still did something wrong, because no matter how many rows exist
in my table "person" the result in always 5.
Could this be an data type problem? count(*) returns int8
int8 -> int (C-type) -> PG_RETURN_INT32 ??? Could this cause the problem?
Thanks for any advise, Max.
___
WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und
50,- Euro Geldprämie inklusive! https://freundschaftswerbung.web.de
--
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] C-Functions using SPI - Missing Magic Block
On Mon, Jul 05, 2010 at 10:20:30AM +0200, saitenhe...@web.de wrote:
> Datum count_person(PG_FUNCTION_ARGS) {
> SPI_connect();
> int ret = SPI_exec("SELECT count(*) FROM person", 0);
> SPI_finish();
> PG_RETURN_INT32(ret);
> }
>
> But I guess I still did something wrong, because no matter how many
> rows exist in my table "person" the result in always 5.
5 is actually the value you want to be getting back!
The problem is that you're treating the status value of SPI_exec as the
result of the query. You need to check the result to see if there was
an error and only if it's OK can you call something like SPI_getvalue to
actually get the count out. You could probably steal some code from:
http://developer.postgresql.org/pgdocs/postgres/spi-examples.html
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memory on update of a single column table containg just one row.
Hello Guys, We are trying to migrate from Oracle to Postgres. One of the major requirement of our database is the ability to generate XML feeds and some of our XML files are in the order of 500MB+. We are getting "Out of Memory" errors when doing an update on a table. Here is some detail on the error: update test_text3 set test=test||test The table test_text3 contains only one record, the column test contains a string containing 382,637,520 characters (around 300+ MB) Error Message: ERROR: out of memory DETAIL: Failed on request of size 765275088. The server has 3GB of RAM: total used free sharedbuffers cached Mem: 3115804 8235242292280 0 102488 664224 -/+ buffers/cache: 568123058992 Swap: 5177336 338125143524 I tweaked the memory parameters of the server a bit to the following values, but still no luck. shared_buffers = 768MB effective_cache_size = 2048MB checkpoint_segments 8 checkpoint_completion_target 0.8 work_mem 10MB max_connections 50 wal_buffers 128 This error is consistent and reproducible every time I run that update. I can provide a detailed stack trace if needed. Any help would be highly appreciated. For those who are interested in the background, we are trying to migrate from Oracle to Postgresql. One of the major requirement of our database is the ability to generate XML feeds and some of our XML files are in the order of 500MB+. Considering future scalability we are trying to see how much data can be stored in a "text" column and written to the file system as we found PostgreSQL's COPY command a very efficient way of writing date to a file. Thanks in advance and best regards, Zeeshan This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB
Re: [GENERAL] Out of memory on update of a single column table containg just one row.
On 5 July 2010 11:47, wrote: > Hello Guys, > > > > We are trying to migrate from Oracle to Postgres. One of the major > requirement of our database is the ability to generate XML feeds and some of > our XML files are in the order of 500MB+. > > > > We are getting "Out of Memory" errors when doing an update on a table. > > > > Here is some detail on the error: > > > > update test_text3 set test=test||test > > > > The table test_text3 contains only one record, the column test contains a > string containing 382,637,520 characters (around 300+ MB) > > > > Error Message: > > ERROR: out of memory > > DETAIL: Failed on request of size 765275088. > > > > The server has 3GB of RAM: > > total used free shared buffers cached > > Mem: 3115804 823524 2292280 0 102488 664224 > > -/+ buffers/cache: 56812 3058992 > > Swap: 5177336 33812 5143524 > > > > I tweaked the memory parameters of the server a bit to the following values, > but still no luck. > > shared_buffers = 768MB > > effective_cache_size = 2048MB > > checkpoint_segments 8 > > checkpoint_completion_target 0.8 > > work_mem 10MB > > max_connections 50 > > wal_buffers 128 > > > > This error is consistent and reproducible every time I run that update. I > can provide a detailed stack trace if needed. > > > > Any help would be highly appreciated. > > > > For those who are interested in the background, we are trying to migrate > from Oracle to Postgresql. One of the major requirement of our database is > the ability to generate XML feeds and some of our XML files are in the order > of 500MB+. > > > > Considering future scalability we are trying to see how much data can be > stored in a "text" column and written to the file system as we found > PostgreSQL's COPY command a very efficient way of writing date to a file. > > > > Thanks in advance and best regards, > > > > > > > > Zeeshan > Hi Zeeshan, Which version of PostgreSQL are you using? And are there any indexes, constraints or triggers on the table you're attempting to insert this data into? As for the maximum size of a text column, I believe it's 1GB. You may find this useful too: http://www.postgresql.org/docs/8.4/static/populate.html Thom -- 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] Out of memory on update of a single column table containg just one row.
> -Original Message- > From: Thom Brown [mailto:thombr...@gmail.com] > Sent: 05 July 2010 12:40 > To: Zeeshan Ghalib > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Out of memory on update of a single column table > containg just one row. > Hi Zeeshan, > > Which version of PostgreSQL are you using? And are there any indexes, > constraints or triggers on the table you're attempting to insert this > data into? > > As for the maximum size of a text column, I believe it's 1GB. > > You may find this useful too: > http://www.postgresql.org/docs/8.4/static/populate.html > > Thom [Zeeshan] Hello Thom, Thanks for your email. PostgreSQL version number is 8.4.4 running on Centos release 5.4 (Final) There are no indexes, constraints or triggers on this table. 1 GB limit is fine, but it is giving the error on 700MB or so. Plus, loading this kid of data will not be a one-time initial import. We will do it, whenever we have to generate the XML and we generate What we are planning to do this is on a regular basis for our XML feed generation. We will put the whole XML into a TEXT column and then use the COPY command to create the file. I am hoping that this is just a configuration problem and once the server is configured properly it will go away. Am I right in my assumption or are these kind of out-of-memory errors common with PostgreSQL? Thanks, Zeeshan This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB -- 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] Out of memory on update of a single column table containg just one row.
Hi,
i tried a simple test:
create temp table _t as select repeat('x',382637520) as test;
update _t set test=test||test;
pg 8.3 32bit fails with
[Error Code: 0, SQL State: 53200] ERROR: out of memory
Detail: Failed on request of size 765275088.
pg 8.4.4 64bit works fine
so upgrade to 64bit
regards
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Windows Installation Problem
I'm trying to install PostgreSQL 8.2 on a Windows 7 machine. I had started with 8.3 but installation stalled and after about 20 minutes I had to eventually kill it. When I try and run the 8.2 install, it just about gets ready and then tells me that another installation is running (this is after a reboot) and won't continue. What do I have to do to convince the installer that I'm NOT running another instance of the install program? Is something written in the registry? len morgan -- 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] Out of memory on update of a single column table containg just one row.
Hello Thom,
Thanks for your quick response.
So, is this there a restriction with 32-bit PostgreSQL, a bug or configuration
issue?
I will most definitely upgrade to 64-bit, because that's what we want anyway.
However, I was curious what is the root cause of this problem?
I am getting a bit worried about this migration, although our database is not
too big (less than 200GB).
Once again, thanks for your help.
Zeeshan
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Thomas Markus
> Sent: 05 July 2010 14:39
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Out of memory on update of a single column table
> containg just one row.
>
> Hi,
>
> i tried a simple test:
> create temp table _t as select repeat('x',382637520) as test;
> update _t set test=test||test;
>
> pg 8.3 32bit fails with
> [Error Code: 0, SQL State: 53200] ERROR: out of memory
>Detail: Failed on request of size 765275088.
>
> pg 8.4.4 64bit works fine
>
> so upgrade to 64bit
>
> regards
> Thomas
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
This e-mail is confidential and should not be used by anyone who is not the
original intended recipient. Global DataPoint Limited does not accept liability
for any statements made which are clearly the sender's own and not expressly
made on behalf of Global DataPoint Limited. No contracts may be concluded on
behalf of Global DataPoint Limited by means of e-mail communication. Global
DataPoint Limited Registered in England and Wales with registered number
3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T
4LB
--
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] Out of memory on update of a single column table containg just one row.
On Mon, Jul 05, 2010 at 01:52:20PM +, zeeshan.gha...@globaldatapoint.com wrote: > So, is this there a restriction with 32-bit PostgreSQL, a bug or > configuration issue? It's a restriction because of the 32bit address space. You've basically got between two and three GB of useful space left and everything has to fit in there. Hence if you've got a 300MB object in memory (i.e. your XML) and you try and combine it with as similar sized object then you'll need 300MB*4 = 1.2GB of free memory in the process's address space, with 600MB of that being consecutive. It's obviously failing to find that and hence the query is failing. A 64bit address space is more than a million times larger and hence this is why that worked. Generally with databases you're expected to be working with lots of small objects (i.e. most a few bytes in length) with a few multi KB ones. Databases are fine with lots of these (i.e. I've got databases with hundreds of millions of rows) but don't work very well when each row is very big. The design assumption is that values are "atomic" and large values normally aren't atomic so would be broken down into smaller pieces when they enter the database. Maybe the large object functionality in PG would suit your needs better, they are designed for larger things like this and don't suffer the same restrictions (i.e. internally they're worked with piecemeal rather than trying to work with the whole thing in one go). They can be a bit of a hassle to work with, so which is "better" is very use case dependent. -- Sam http://samason.me.uk/ p.s. the legalese at the bottom of your emails is probably dissuading a number of people from replying, you're better off dumping it if you can--it serves no useful purpose anyway. -- 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] Out of memory on update of a single column table containg just one row.
Ok, that makes perfect sense. We will upgrade to 64-bit and continue our tests on the new build. By the way, is it safe to go ahead with Centos 5.5 or should we stick to the previous version 5.4? Thank you so much Sam and Thom for your speedy help :) Best regards, Zeeshan > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Sam Mason > Sent: 05 July 2010 15:14 > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Out of memory on update of a single column table > containg just one row. > > On Mon, Jul 05, 2010 at 01:52:20PM +, > zeeshan.gha...@globaldatapoint.com wrote: > > So, is this there a restriction with 32-bit PostgreSQL, a bug or > > configuration issue? > > It's a restriction because of the 32bit address space. You've > basically > got between two and three GB of useful space left and everything has to > fit in there. Hence if you've got a 300MB object in memory (i.e. your > XML) and you try and combine it with as similar sized object then > you'll > need 300MB*4 = 1.2GB of free memory in the process's address space, > with > 600MB of that being consecutive. It's obviously failing to find that > and hence the query is failing. A 64bit address space is more than a > million times larger and hence this is why that worked. > > Generally with databases you're expected to be working with lots of > small objects (i.e. most a few bytes in length) with a few multi KB > ones. Databases are fine with lots of these (i.e. I've got databases > with hundreds of millions of rows) but don't work very well when each > row is very big. The design assumption is that values are "atomic" and > large values normally aren't atomic so would be broken down into > smaller > pieces when they enter the database. > > Maybe the large object functionality in PG would suit your needs > better, > they are designed for larger things like this and don't suffer the same > restrictions (i.e. internally they're worked with piecemeal rather than > trying to work with the whole thing in one go). They can be a bit of a > hassle to work with, so which is "better" is very use case dependent. > > -- > Sam http://samason.me.uk/ > > p.s. the legalese at the bottom of your emails is probably dissuading > a number of people from replying, you're better off dumping it if you > can--it serves no useful purpose anyway. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to remove a for-loop from programming language and put it into the query?
Hello!
Can someone help me to develop a query?
Things are more complex than this example, but with this simple example
I can explain what I need and get an answer.
Table: "diagnose_logs"
Fields:
- id serial
- hardware_id integer
- diag_value integer
- ts timestamp
So I collect many diagnose information from many hardwares.
So, I need to get a report of all diagnostics of all hardware on
december 25th.
(external programming language)
for ($i = 1; $i < 500; $i++) {
// return me the "most recent" diag_value from a hardware_id $i
// at the desired timestamp
runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
23:59:59' and hardware_id = $i order by ts desc limit 1");
}
Currently I have an index on diagnose_logs(ts,hardware_id)
I have 3 milion registers of 500 different hardware_id.
The time to run 500 times this query is long... about 1 minute. When I
need a montly day-by-day report of 500 hardwares, it takes about half an
hour.
can I turn this for-loop into a single query to run in postgres?
Thanks,
Pedro
--
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] how to remove a for-loop from programming language and put it into the query?
On 5 July 2010 15:48, Pedro Zorzenon Neto wrote:
> Hello!
>
> Can someone help me to develop a query?
>
> Things are more complex than this example, but with this simple example
> I can explain what I need and get an answer.
>
> Table: "diagnose_logs"
> Fields:
> - id serial
> - hardware_id integer
> - diag_value integer
> - ts timestamp
>
> So I collect many diagnose information from many hardwares.
>
> So, I need to get a report of all diagnostics of all hardware on
> december 25th.
>
> (external programming language)
> for ($i = 1; $i < 500; $i++) {
> // return me the "most recent" diag_value from a hardware_id $i
> // at the desired timestamp
> runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardware_id = $i order by ts desc limit 1");
> }
>
> Currently I have an index on diagnose_logs(ts,hardware_id)
> I have 3 milion registers of 500 different hardware_id.
>
> The time to run 500 times this query is long... about 1 minute. When I
> need a montly day-by-day report of 500 hardwares, it takes about half an
> hour.
>
> can I turn this for-loop into a single query to run in postgres?
>
> Thanks,
> Pedro
I'm probably misunderstanding the problem, but can't you just do:
SELECT
diag_value
FROM
diagnose_logs
WHERE
ts <= '2009-12-25 23:59:59'
AND
hardware_id BETWEEN 1 AND 500
ORDER BY
ts DESC
LIMIT 1
Regards
Thom
--
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] how to remove a for-loop from programming language and put it into the query?
On Mon, Jul 05, 2010 at 11:48:37AM -0300, Pedro Zorzenon Neto wrote:
> for ($i = 1; $i < 500; $i++) {
> // return me the "most recent" diag_value from a hardware_id $i
> // at the desired timestamp
> runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardware_id = $i order by ts desc limit 1");
> }
>
> can I turn this for-loop into a single query to run in postgres?
You want to be using DISTINCT ON or some sort of WINDOW function.
DISTINCT ON works with older version of PG, but isn't as standards'
conforming. The following should do the trick with DISTINCT ON:
SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts
FROM diagnose_logs
WHERE ts <= '2009-12-25 23:59:59'
ORDER BY hardware_id, ts DESC;
You can obviously put in the normal clauses to limit the hardware_ids to
be things you consider important in the normal ways.
--
Sam http://samason.me.uk/
--
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] how to remove a for-loop from programming language and put it into the query?
> I'm probably misunderstanding the problem, but can't you just do: > > SELECT > diag_value > FROM > diagnose_logs > WHERE > ts <= '2009-12-25 23:59:59' > AND > hardware_id BETWEEN 1 AND 500 > ORDER BY > ts DESC > LIMIT 1 Hi Thom, Yes, I think you misunderstood. An example of a table: hardware_id | ts | diag_value 1 | 2009-12-25 14:00:00 | 43.5 (*) 1 | 2009-12-26 15:00:00 | 43.6 1 | 2009-12-24 13:00:00 | 43.7 2 | 2009-12-24 15:00:00 | 43.8 (*) 2 | 2009-12-24 14:00:00 | 43.9 2 | 2009-12-24 14:16:00 | 43.9 2 | 2009-12-27 14:00:00 | 44.0 I need to get the "most recent" value before "2009-12-25 23:59:59" from every hardware_id. For hardware_id=1, the value would be: 1 | 2009-12-25 14:00:00 | 43.5 for hardware_id=2, the value would be: 2 | 2009-12-24 15:00:00 | 43.8 I need a query that will return me those lines marked with (*) :-) is this possible? Thanks! -- 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] how to remove a for-loop from programming language and put it into the query?
Pedro Zorzenon Neto wrote:
> [...]
> So, I need to get a report of all diagnostics of all hardware on
> december 25th.
> (external programming language)
> for ($i = 1; $i < 500; $i++) {
> // return me the "most recent" diag_value from a hardware_id $i
> // at the desired timestamp
> runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardware_id = $i order by ts desc limit 1");
> }
> Currently I have an index on diagnose_logs(ts,hardware_id)
> I have 3 milion registers of 500 different hardware_id.
> The time to run 500 times this query is long... about 1 minute. When I
> need a montly day-by-day report of 500 hardwares, it takes about half an
> hour.
> can I turn this for-loop into a single query to run in postgres?
Another month, another case for "DISTINCT ON":
| SELECT DISTINCT ON (hardware_id)
| hardware_id, diag_value
| FROM diagnose_logs
| WHERE ts <= '2009-12-25 23:59:59'
| ORDER BY hardware_id, ts DESC;
BTW, I'd prefer "WHERE ts < '2009-12-26'" as otherwise you
don't catch a timestamp '2009-12-25 23:59:59.5' (not to
speak of leap seconds).
Tim
--
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] how to remove a for-loop from programming language and put it into the query?
On 5 July 2010 16:26, Pedro Zorzenon Neto wrote: >> I'm probably misunderstanding the problem, but can't you just do: >> >> SELECT >> diag_value >> FROM >> diagnose_logs >> WHERE >> ts <= '2009-12-25 23:59:59' >> AND >> hardware_id BETWEEN 1 AND 500 >> ORDER BY >> ts DESC >> LIMIT 1 > > Hi Thom, > > Yes, I think you misunderstood. > > An example of a table: > > hardware_id | ts | diag_value > 1 | 2009-12-25 14:00:00 | 43.5 (*) > 1 | 2009-12-26 15:00:00 | 43.6 > 1 | 2009-12-24 13:00:00 | 43.7 > 2 | 2009-12-24 15:00:00 | 43.8 (*) > 2 | 2009-12-24 14:00:00 | 43.9 > 2 | 2009-12-24 14:16:00 | 43.9 > 2 | 2009-12-27 14:00:00 | 44.0 > > I need to get the "most recent" value before "2009-12-25 23:59:59" from > every hardware_id. > > For hardware_id=1, the value would be: > 1 | 2009-12-25 14:00:00 | 43.5 > for hardware_id=2, the value would be: > 2 | 2009-12-24 15:00:00 | 43.8 > > I need a query that will return me those lines marked with (*) :-) is > this possible? > > Thanks! > > D'oh! I completely ignored that "LIMIT 1". okay... what Sam said. Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memory on update of a single column table containg just one row.
Hello Guys, We are trying to migrate from Oracle to Postgres. One of the major requirement of our database is the ability to generate XML feeds and some of our XML files are in the order of 500MB+. We are getting "Out of Memory" errors when doing an update on a table. Here is some detail on the error: update test_text3 set test=test||test The table test_text3 contains only one record, the column test contains a string containing 382,637,520 characters (around 300+ MB) Error Message: ERROR: out of memory DETAIL: Failed on request of size 765275088. The server has 3GB of RAM: total used free sharedbuffers cached Mem: 3115804 8235242292280 0 102488 664224 -/+ buffers/cache: 568123058992 Swap: 5177336 338125143524 I tweaked the memory parameters of the server a bit to the following values, but still no luck. shared_buffers = 768MB effective_cache_size = 2048MB checkpoint_segments 8 checkpoint_completion_target 0.8 work_mem 10MB max_connections 50 wal_buffers 128 This error is consistent and reproducible every time I run that update. I can provide a detailed stack trace if needed. Any help would be highly appreciated. For those who are interested in the background, we are trying to migrate from Oracle to Postgresql. One of the major requirement of our database is the ability to generate XML feeds and some of our XML files are in the order of 500MB+. Considering future scalability we are trying to see how much data can be stored in a "text" column and written to the file system as we found PostgreSQL's COPY command an extremely efficient way of writing date to a file. Thanks in advance and best regards, Zeeshan This e-mail is confidential and should not be used by anyone who is not the original intended recipient. woZZon Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of woZZon Limited. No contracts may be concluded on behalf of woZZon Limited by means of e-mail communication. woZZon Limited Registered in England and Wales with registered number 03926130 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB
Re: [GENERAL] how to remove a for-loop from programming language and put it into the query?
Em 05-07-2010 12:22, Sam Mason escreveu: > > You want to be using DISTINCT ON or some sort of WINDOW function. > DISTINCT ON works with older version of PG, but isn't as standards' > conforming. The following should do the trick with DISTINCT ON: > > SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts > FROM diagnose_logs > WHERE ts <= '2009-12-25 23:59:59' > ORDER BY hardware_id, ts DESC; > > You can obviously put in the normal clauses to limit the hardware_ids to > be things you consider important in the normal ways. Hi Sam! It worked ok! your solution solves what I need. The process time went from 60 to 20 seconds. nice! Can you help me to discover why the "Seq Scan" in explain analyse? I tried to create some indexes to change seq scan to index scan, but couldn't do it. Now the real table and field names... explain analyse select distinct on (callbox_id) callbox_id, ts, imei, temperatura from diag_resultados where ts <= '2010-06-15 00:00:00' order by callbox_id, ts desc; QUERY PLAN - Unique (cost=408118.90..417725.43 rows=406 width=18) (actual time=19608.347..22626.744 rows=458 loops=1) -> Sort (cost=408118.90..412922.17 rows=1921306 width=18) (actual time=19608.345..21503.135 rows=1905941 loops=1) Sort Key: callbox_id, ts -> Seq Scan on diag_resultados (cost=0.00..58795.50 rows=1921306 width=18) (actual time=0.024..4886.113 rows=1905941 loops=1) Filter: (ts <= '2010-06-15 00:00:00-03'::timestamp with time zone) Total runtime: 22762.754 ms Pg is old in this machine. 7.4.17 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Out of memory on update of a single column table containg just one row.
Please ignore this email. I sent this one earlier with the wrong email address and it was sent to moderator. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Zeeshan Ghalib Sent: 05 July 2010 11:12 To: pgsql-general@postgresql.org Subject: [GENERAL] Out of memory on update of a single column table containg just one row. Hello Guys, We are trying to migrate from Oracle to Postgres. One of the major requirement of our database is the ability to generate XML feeds and some of our XML files are in the order of 500MB+. We are getting "Out of Memory" errors when doing an update on a table. Here is some detail on the error: update test_text3 set test=test||test The table test_text3 contains only one record, the column test contains a string containing 382,637,520 characters (around 300+ MB) Error Message: ERROR: out of memory DETAIL: Failed on request of size 765275088. The server has 3GB of RAM: total used free sharedbuffers cached Mem: 3115804 8235242292280 0 102488 664224 -/+ buffers/cache: 568123058992 Swap: 5177336 338125143524 I tweaked the memory parameters of the server a bit to the following values, but still no luck. shared_buffers = 768MB effective_cache_size = 2048MB checkpoint_segments 8 checkpoint_completion_target 0.8 work_mem 10MB max_connections 50 wal_buffers 128 This error is consistent and reproducible every time I run that update. I can provide a detailed stack trace if needed. Any help would be highly appreciated. For those who are interested in the background, we are trying to migrate from Oracle to Postgresql. One of the major requirement of our database is the ability to generate XML feeds and some of our XML files are in the order of 500MB+. Considering future scalability we are trying to see how much data can be stored in a "text" column and written to the file system as we found PostgreSQL's COPY command an extremely efficient way of writing date to a file. Thanks in advance and best regards, Zeeshan This e-mail is confidential and should not be used by anyone who is not the original intended recipient. woZZon Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of woZZon Limited. No contracts may be concluded on behalf of woZZon Limited by means of e-mail communication. woZZon Limited Registered in England and Wales with registered number 03926130 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB
Re: [GENERAL] how to remove a for-loop from programming language and put it into the query?
On Mon, Jul 05, 2010 at 12:44:55PM -0300, Pedro Zorzenon Neto wrote: > Em 05-07-2010 12:22, Sam Mason escreveu: > > SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts > > FROM diagnose_logs > > WHERE ts <= '2009-12-25 23:59:59' > > ORDER BY hardware_id, ts DESC; > > It worked ok! your solution solves what I need. The process time went > from 60 to 20 seconds. nice! Always nice when less code is faster! > Can you help me to discover why the "Seq Scan" in explain analyse? I > tried to create some indexes to change seq scan to index scan, but > couldn't do it. It's because the only way PG knows how to do a DISTINCT ON is to sort the whole table and then pull out the appropriate values. Sorting the whole of a table is generally going to be faster than referring to an index for every row and hence PG won't use an index. I'm not sure if that's changed more recently, but for 7.4 I'm pretty sure that's the case anyway. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OFFTOPIC -- pgpool list is down?????
Do you know if the pgpool list is down???, i sent many emails to the pgpool list but since last week i can't receive any mail from pgpool list , i checktheir web site too, but the last message are of june. -- 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] OFFTOPIC -- pgpool list is down?????
We are aware of this issue. Marc is looking at it. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz 05.Tem.2010 tarihinde 22:26 saatinde, "Edmundo Robles L." > şunları yazdı: Do you know if the pgpool list is down???, i sent many emails to the pgpool list but since last week i can't receive any mail from pgpool list , i checktheir web site too, but the last message are of june. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Manual for small project
Hello. I have to prepare manual to simple database in postgresql 8.4.4, Winxp+sp2. I want to store doc pages in comment field, format html. Is a better idea to store documentation ? thanks for answer -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] moderninzing/upgrading mail list format
I belong to MANY email listservers, probably like all of us. All of them, I am on digest. The CONTENT from all of you contributors is superior, more mature, and more directly helpful than all the other lists. I think it has something to do with the conservative, structured mind set of us Dbase programmers. (Or at least when we discuss database issues ;-) However, I think that the mailing list world has moved beyond what we use, at least for those of us who receive digest mode. The google groups group all the same topics in one block, and uses intra-document html links to get to those blocks. By using the 'subjects contents' table at the top of the digest email, and the back button, it is VERY easy to investigate only the subjects one is intersted in, without having to scan through the whole digest. There are other, better-than-pgsql-mail-program convenience attributes of the google groups email system. I would like to open a conversation about either changing our email to be more like google groups, or a move to google groups. PS, we(Postgresql databsase) don't(doesn't) have any mention on lots of SQL/Database tutorials. Everything is mysql this, mysql that. I just offered to write examples for Postgresql to w3schools SQL section. I'd like to encourage others to look for opportunities to do that, especially for the programming language frameworks. I am always promomting P-sql to the coders of Symfony and referring little issues withi postgres to them. Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php Dennis Gearon -- 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] moderninzing/upgrading mail list format
On Mon, Jul 05, 2010 at 02:43:58PM -0700, Dennis Gearon wrote: > I would like to open a conversation about either changing our email to > be more like google groups, or a move to google groups. You know you can read pg-general in google groups if you want: http://groups.google.com/group/pgsql.general/topics Markmail is also quite good for some things: http://markmail.org/search/?q=list:org.postgresql.pgsql-general -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cgi program doing multiple queries hangs in multiple places
I'm having what I'd have consider a strange problem with PostgreSQL and libpq. The PostgreSQL version is now 8.4.2 running under OpenBSD 4.7 I had the same problem under OpenBSD 4.3 with whatever PostgreSQL version that had. (a couple years older) My program runs something around 20-40 different queries through libpq. It all works, sometimes. Most of the time something stops, not always in the same place. Fairly often running exactly the same thing over again will work or stop in a different place. I wrote the program as CGI, mostly to let the web browser provide a user interface, but it isn't intended as an interactive program so much as a way of generating html pages that get stored and used later. The database is a local copy of the Internet Movie Database (see imdb.org/interfaces) downloaded as tarballs then loaded into a PostgreSQL database with imdbpy. It's quite large, but that's probably irrelevant. I'm running PostgreSQL with default settings in postgresql.conf and pg_hba.conf because I don't know what to set differently. Other than getting lots of "unexpected EOF on client connection" in PostgreSQL's log file there aren't any error messages. Both Apache and the PostgreSQL server are running on the same laptop. Apache isn't chrooted. Most of the time it isn't connected to anything. I put a tarball containing the program source, makefile, html calling page and sample output at: http://oldyashica.webs.com/postgresql/acorey.tar.gz Alan Corey -- 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] how to remove a for-loop from programming language and put it into the query?
> (external programming language)
> for ($i = 1; $i < 500; $i++) {
> // return me the "most recent" diag_value from a hardware_id $i
> // at the desired timestamp
> runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardware_id = $i order by ts desc limit 1");
> }
>
> can I turn this for-loop into a single query to run in postgres?
>
> Thanks,
> Pedro
>
Try:
SELECT diag_value FROM diagnose_logs a where id in
(
SELECT id FROM diagnose_logs b
WHERE a.hardware_id=b.hardware_id
and ts <= '2009-12-25 23:59:59'
and hardware_id between 1 and 500
ORDER BY ts LIMIT 1)
ORDER BY hardware_id;
--
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] moderninzing/upgrading mail list format
Dennis Gearon, 05.07.2010 23:43: I belong to MANY email listservers, probably like all of us. All of them, I am on digest. The CONTENT from all of you contributors is superior, more mature, and more directly helpful than all the other lists. I think it has something to do with the conservative, structured mind set of us Dbase programmers. (Or at least when we discuss database issues ;-) However, I think that the mailing list world has moved beyond what we use, at least for those of us who receive digest mode. The google groups group all the same topics in one block, and uses intra-document html links to get to those blocks. By using the 'subjects contents' table at the top of the digest email, and the back button, it is VERY easy to investigate only the subjects one is intersted in, without having to scan through the whole digest. There are other, better-than-pgsql-mail-program convenience attributes of the google groups email system. I read it through the gmane newsreader, so I get threaded display and can easily "scan" the subjects. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
