[GENERAL] out of memory error

[Geoffrey Myers]

We have a process that we successfully ran on virtually identical 
databases.  The process completed fine on a machine with 8 gig of 
memory.  The process fails when run on another machine that has 16 gig 
of memory with the following error:


out of memory for query result

How is this possible?

The databases are almost identical.  By that I mean, the database that 
the process completed properly is a dump of the database from the 
machine where it failed.  There is about a week's more data in the 
database where the process failed.  The whole database only takes up 
about 13 gig of disk space.


Any clues would be appreciated.

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

--
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 error

[Sim Zacks]

On 07/03/2011 01:00 PM, Geoffrey Myers wrote:

We have a process that we successfully ran on virtually identical 
databases.  The process completed fine on a machine with 8 gig of 
memory.  The process fails when run on another machine that has 16 gig 
of memory with the following error:


out of memory for query result

How is this possible?


Look at the diff on the postgresql.conf from the two machines.



The databases are almost identical.  By that I mean, the database that 
the process completed properly is a dump of the database from the 
machine where it failed.  There is about a week's more data in the 
database where the process failed.  The whole database only takes up 
about 13 gig of disk space.


Any clues would be appreciated.




--
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 error

[Craig Ringer]

On 3/07/2011 6:00 PM, Geoffrey Myers wrote:


out of memory for query result

How is this possible?


Resource limits?

Do you have a ulimit in place that applies to postgresql? You can check 
by examining the resource limits of a running postgresql backend as 
shown in /proc/$PG_PID where $PG_PID is the process ID of the backend of 
interest.


Check your work_mem in postgresql.conf, too.

--
Craig Ringer

--
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 create "auto-increment" field WITHOUT a sequence object?

[Vincent Veyron]

Le vendredi 01 juillet 2011 à 12:28 +0400, Dmitriy Igrishin a écrit :


> Then I don't clearly understand the existence of locks (the LOCK
> command, SELECT FOR UPDATE clause and so on) if the usage
> of them gives only problems...
> 

Chris already explained why twice :

"you MUST lock on insert to get gapless sequences"

Can't you just :
-create the records with a regular sequence, that will have gaps
-when you want to export, number an additional column from 1 to 10 000
and use that as key
?
-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique


-- 
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 create "auto-increment" field WITHOUT a sequence object?

[Dmitriy Igrishin]

Hey Vincent,

2011/7/3 Vincent Veyron 

> Le vendredi 01 juillet 2011 à 12:28 +0400, Dmitriy Igrishin a écrit :
>
>
> > Then I don't clearly understand the existence of locks (the LOCK
> > command, SELECT FOR UPDATE clause and so on) if the usage
> > of them gives only problems...
> >
>
> Chris already explained why twice :
>
> "you MUST lock on insert to get gapless sequences"
>
Not me :-). The OP must do it. So, what problem here? Deadlocks?
Again, if deadlocks are so dangerous, why the LOCK command exists?


> Can't you just :
> -create the records with a regular sequence, that will have gaps
> -when you want to export, number an additional column from 1 to 10 000
> and use that as key
> ?
>
I don't use any locks explicitly :-)

> --
> Vincent Veyron
> http://marica.fr/
> Logiciel de gestion des sinistres et des contentieux pour le service
> juridique
>
>


-- 
// Dmitriy.

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

[Ireneusz Pluta]

W dniu 2011-06-30 20:20, Dmitry Koterov pisze:
And I need as compact uniq_id generation (with minimum "holes") as it possible - this is a VERY 
important requirement (to export these values into external systems which accepts only IDs limited 
from 1 to 10).


So I cannot use sequences: sequence value is obviously not rolled back, so if I insert 
nextval(...) as uniq_id, I will have large holes (because of often transaction rollbacks) and 
exhaust 10 uniq_ids very fast. How to deal with all this without sequences?


You may use dense_rank() (or even rank()) window function to map your sequence-with-gaps to a 
no-gap-id which will be used for exports.


Consider this:

test=# select uniq_id_with_gaps, dense_rank() over (order by uniq_id_with_gaps) as 
uniq_id_without_gaps from (select generate_series(1, 100, 7) as uniq_id_with_gaps) a;

 uniq_id_with_gaps | uniq_id_without_gaps
---+--
 1 |1
 8 |2
15 |3
22 |4
29 |5
36 |6
43 |7
50 |8
57 |9
64 |   10
71 |   11
78 |   12
85 |   13
92 |   14
99 |   15

--
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 create "auto-increment" field WITHOUT a sequence object?

[Chris Travers]

On Sun, Jul 3, 2011 at 7:25 AM, Ireneusz Pluta  wrote:

> You may use dense_rank() (or even rank()) window function to map your
> sequence-with-gaps to a no-gap-id which will be used for exports.
>

The typical case where gapless numbering comes up is something like this:

In Greece, you go get invoice paper from the tax office which is
numbered in sequence and the government gets a list of the invoice
forms you have purchased.  You then print the invoices on those paper
forms, and must number the invoices sequentially and without gaps.  In
the case of an audit, all paper forms obtained must be accounted for
as must all gaps in numbering.  You MUST be able to connect each
sequential invoice number (internally generated) to each invoice form
(numbered at the tax office).

In this case you really have no choice but to lock some records,
generate a new gapless id, and save/print it.  Naturally this causes
the sorts of problems mentioned.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Select from Java Strings

[Daron Ryan]

  
  
I have strings from
java and need to check which ones are not present in the db. Can
I use a select statement to do this by making it search my
strings as though they are a table?
  

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

[Vincent Veyron]

Le dimanche 03 juillet 2011 à 18:10 +0400, Dmitriy Igrishin a écrit :

> 
> Not me :-). The OP must do it. 

Duh! sorry about that. Indeed, I confused you with Dmitry. 

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique


-- 
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] Select from Java Strings

[David Johnston]

On Jul 3, 2011, at 11:13, Daron Ryan  wrote:

> I have strings from
> java and need to check which ones are not present in the db. Can
> I use a select statement to do this by making it search my
> strings as though they are a table?

There are multiple ways to accomplish your goal, which each have merits and 
issues.  
1. Java for loop and look for each string one at a time
2. Convert your strings into a Postgres array and query all of them at once
3. Insert your strings into a table and execute a query to check them all at 
once

If you want more help than this you should provide more specific details about 
your situation.  Your question seems odd at first reading, especially the part 
where you want to find out which strings are NOT present.

David J

Re: [GENERAL] out of memory error

[Alban Hertroys]

On 3 Jul 2011, at 12:00, Geoffrey Myers wrote:

> We have a process that we successfully ran on virtually identical databases.  
> The process completed fine on a machine with 8 gig of memory.  The process 
> fails when run on another machine that has 16 gig of memory with the 
> following error:
> 
> out of memory for query result

You didn't mention what client you're using, but could it possibly be the 
client that's running out of memory? The fact that it's happening in the query 
result seems to point to the client.

Another thing you might want to check: Does the second server have at least as 
much shared memory configured in the kernel as the first has?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4e109dd612097665720452!



-- 
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 create "auto-increment" field WITHOUT a sequence object?

[Alban Hertroys]

On 3 Jul 2011, at 16:10, Dmitriy Igrishin wrote:

> "you MUST lock on insert to get gapless sequences"
> Not me :-). The OP must do it. So, what problem here? Deadlocks?
> Again, if deadlocks are so dangerous, why the LOCK command exists?

It's not deadlocks, it's concurrent updates that are the trouble. If you don't 
lock, you run the risk for two records being assigned the same number 
concurrently.

With a unique constraint added into the mix (and there should be one) that 
means that one of the transactions will fail the unique constraint check on 
commit.

It's possible to catch that in the client and redo the transaction with a new 
ID, but if that's not acceptable (for example because it matters which 
transaction got the ID first) then you need to lock records.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4e109f6e12092079216178!



-- 
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 create "auto-increment" field WITHOUT a sequence object?

[Dmitriy Igrishin]

Hey Alban,

2011/7/3 Alban Hertroys 

> On 3 Jul 2011, at 16:10, Dmitriy Igrishin wrote:
>
> > "you MUST lock on insert to get gapless sequences"
> > Not me :-). The OP must do it. So, what problem here? Deadlocks?
> > Again, if deadlocks are so dangerous, why the LOCK command exists?
>
> It's not deadlocks, it's concurrent updates that are the trouble. If you
> don't lock, you run the risk for two records being assigned the same number
> concurrently.
>
Thanks for clarify, but I know why resources must be locked when
they are used concurrently :-). See my previous post about SELECT FOR UPDATE
...
and I don't see the problem with it. As well as with the LOCK command.


> With a unique constraint added into the mix (and there should be one) that
> means that one of the transactions will fail the unique constraint check on
> commit.
>
> It's possible to catch that in the client and redo the transaction with a
> new ID, but if that's not acceptable (for example because it matters which
> transaction got the ID first) then you need to lock records.
>
Sure.

>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1257,4e109f6512095013212184!
>
>
>


-- 
// Dmitriy.

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

[Michael Gould]

Hiroshi,

I've what I believe your saying is the one that is in the contrib directory
now should work fine on Window 64

"Hiroshi Saito"  wrote:
> Hi Grace-san.
> 
> It is the same as windows.
> http://winpg.jp/~saito/pg_work/OSSP_win32/
> 
> 1. Deployment of tar.gz
> 2. apply the patch
> 3. configure and build by Linux or MinGW
> 4. config.h and win32.mak are set at the head of a source tree.
> 5. nmake -f win32.mak
> It can be used by 32 bits and 64 bits.
> 
> Regards,
> Hiroshi Saito
> 
> (2011/06/30 22:52), Grace Batumbya wrote:
>> Hey Hiroshi,
>> Do you have a make file to compile for x64 windows?
>> May be if that is made available then ossp-uuid for x64 versions will
>> become standard.
>>
>> *Grace Batumbya*
>> Research Assistant | Seneca CDOT
>> Phone: 416-491-5050 x3548
>> cdot.senecac.on.ca 
>>
>> On 6/29/2011 1:02 PM, Hiroshi Saito wrote:
>>> Hi.
>>>
>>> here is an excuse...
>>> http://archives.postgresql.org/pgsql-general/2011-06/msg00738.php
>>>
>>> Regard,
>>> Hiroshi Saito
>>>
>>> (2011/06/30 1:50), Grace Batumbya wrote:
 Thanks Hiroshi, that solved the problem.
 If you do not mind, how did you go about to build ossp-uuid?

 *Grace Batumbya*
 Research Assistant | Seneca CDOT
 Phone: 416-491-5050 x3548
 cdot.senecac.on.ca 

 On 6/29/2011 12:38 PM, Hiroshi Saito wrote:
> Hi Grace-san.
>
> Is this helpful to you?
> http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/
>
> Regards,
> Hiroshi Saito
>
> (2011/06/30 1:14), Grace Batumbya wrote:
>> The installer for windows for 64bit versions of postgresql doesn't
>> include ossp-uuid.sql.
>> Does anyone know where or how to get this?
>>
>> Thanks
>> --
>> *Grace Batumbya*
>> Research Assistant | Seneca CDOT
>> Phone: 416-491-5050 x3548
>> cdot.senecac.on.ca 
>
>>>
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 

--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax



-- 
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] statically compiling postgres and problem with initdb

[mona attariyan]

Thanks a lot for the details. Hopefully, I'll be able to pull it off.
--Mona

--- On Fri, 7/1/11, Craig Ringer  wrote:

From: Craig Ringer 
Subject: Re: [GENERAL] statically compiling postgres and problem with initdb
To: "Tom Lane" 
Cc: "mona attariyan" , pgsql-general@postgresql.org
Date: Friday, July 1, 2011, 8:13 PM

On 1/07/2011 11:48 PM, Tom Lane wrote:

> It might be easier to rip out the functionality that expects loadable
> libraries to work.  I think you could probably get through initdb if you
> just disabled creation of encoding-conversion functions and text search
> dictionaries (try #ifdef'ing out the relevant sections of initdb.c).

Good point. If all that's needed is basic functionality...

> Of course, you'll end up with a pretty crippled version of PG --- no
> encoding conversions, no text search, no procedural languages --- but
> maybe that's enough for what you want to do.  If it's not, then as Craig
> says, you're looking at some pretty major work to bind those pieces into
> the executable statically.

I had to do something quite similar for Scribus years ago, and it wasn't 
anywhere near as hard as I'd feared. It did have two  really annoying bits, 
though. One was having to prefix each shared library's public symbols with the 
name of the shared library to avoid conflicts and allow me to differentiate 
different implementations of the same public interfaces. The second was 
modifying the build system to link each library to the main executable.

This is from increasingly vague memory, but:

First I created a function pointer list entry struct type that maps function 
names to function pointers.

I then modified the loader code so it prefixed the function names it was 
expecting with the library name. Instead of "funcname" it'd try to resolve 
"libname_funcname".

I went through EVERY SINGLE LIBRARY and prefixed the library name to the names 
of every non-static function. It was ugly, but the alternative would've been a 
horrid token-pasting macro hack that would've still required changing each 
function declaration.

The libraries already had headers. If they hadn't, I would've had to write a 
header for each library that declared prototypes for its functions.

I added a new header file to the main build that included all the library 
headers and declared a global array of function pointer list entries. In the 
associated .c (well, .cxx in Scribus) I defined the array, populating it with 
the library-name-prefixed function names and pointers to each function.

I modified each site where Scribus used dlopen() and dlsym() so it called them 
via a wrapper. The wrapper for dynamic linking was just a trivial header of 
inline wrappers around dlopen() and dlsym(). The static linking replacement for 
dlopen() just returned the input library name char* as void*, and the dlsym() 
replacement cast the void* back to char*, joined the library name and function 
name, looked the result up in the function pointer list, and returned the 
resulting function pointer.

Finally, I had to modify the build process so it produced static libraries 
instead of shared libraries for each add-on, and modify the final application 
linkage so it linked each library. This was made a lot easier by the fact that 
Scribus used CMake instead of autotools; I don't know what it'd be like to try 
to do this with autohell, but I suspect "ugly" would be a start.

--
Craig Ringe

Re: [GENERAL] pg_rman in Windows - is it possible?

[Tomonari Katsumata]

Hi,
> But is there any similar kind of application for Windows Postgresql 
Backup?

>

I don't know any similar kind applications for windows PostgreSQL backup,
although there may be some aplications in the world.

I've checked some tools listed below wiki, I couldn't find it out...
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

regards,

Tomonari Katsumata


(2011/07/01 22:16), AI Rumman wrote:

But is there any similar kind of application for Windows Postgresql Backup?

On Fri, Jul 1, 2011 at 3:39 PM, Tomonari Katsumata<
katsumata.tomon...@po.ntts.co.jp>  wrote:


Hi,

sorry, pg_rman can not use for windows environment.
This is an issue for a long time, but I can not revise it for some reasons.
(If you can make patch for revising them, welcome.)


(2011/07/01 18:18), AI Rumman wrote:


Could anyone please tell me whether I can use pg_rman in my Windows
environment? Or is there any similar type of application for windoes
Postgresql?


regards,
---
Tomonari Katsumata


--
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