Regards to all

If you remember (me), i posted here quite a long time ago some issues 
with PostgreSQL memory consumption related to PGDBE, making PostgreSQL 
to run out of memory quickly while indexing or doing anything with big 
ISAM tables ("big" here means a DbfUpsized-table with more than 500 
records).

Well... first of all, this issue hasn't been fixed yet, but this time, 
it looks that we've found what might be causing it, at least: SET DELETED ON

As our server-side indexing stored procedure no longer works (that was 
our workaround to try to index all the upsized tables while keeping 
PostgreSQL memory footprint under control), we had to go back to 
xBase++'s standard functions for indexing ISAM tables using PGDBE... 
only to see lots of out-of-memory error messages from PostgreSQL. We 
tried to tune some PostgreSQL and PGDBE parameters in the past to reduce 
(or at least try to control) this memory footprint, with no success. So, 
as this was a kind of life-or-death issue to solve ASAP, we took a 
different approach: look into our source code.

This is where SET DELETED ON comes into the game.

Looking again at the indexing process, and as our original source code 
has SET DELETED ON activated BEFORE launching this process, PostgreSQL 
memory footprint increases up to 1.7Gbytes (per session) to then 
abrutply end with a "run out of memory" error message from PostgreSQL 
side. And this while trying to index a 1500-record ISAM table.

After disabling this directive, i.e. SET DELETED OFF, PostgreSQL memory 
footprint is under acceptable limits (up to 200Mbytes after indexing 
almost 100 tables with variable sizes) and the whole indexing procesess 
can be executed in its entirety. This is our "new" workaround for the 
first-use indexing of all ISAM tables right after the DbfUpsize process 
is performed.

Getting things clear, the memory issue doesn't lay on the DbCreateIndex 
functions (and such). Instead, when any table is opened with SET DELETED 
ON (for as the table must be opened first to then index it and then 
close it and release its resources back to the server, at least in 
theory....), PostgreSQL memory footprint increases not in the order of 
tens of Megabytes, but in hundredths. With SET DELETED OFF, PostgreSQL 
memory footprint increases too, but only by 10 or 20 Megs (and maybe 
less) when the emulated workarea opens. This is a more manageable 
scenario where our application would crash in a matter of hours, instead 
of just a couple of minutes. Of course, disabling completely this 
directive is not a feasible solution for us, because those deleted 
records would produce fake reports and calculations and we cannot force 
the user to perform a DbPack for each and every table he/she uses. We 
would have to rewrite a lot of code, and, as far as we're concerned, one 
of the PGDBE's main goals is to perform a seamless migration from DBF to 
a relational database while mantaining the business logic.

Sadly, i don't have a copy of the DBF, Upsize and PRG i sent a long time 
ago to recreate this issue, but any of you can easily find any DBF with 
more than 1000 records to then upsize it to Postgres and finally try to 
browse it or do anything you want with it. Remember to put the SET 
DELETED ON directive BEFORE opening the table to see by yourselves how 
PostgreSQL memory consumption skyrockets. (For a more accurate 
reproduction of this issue, install PostgreSQL 9.1 x86 on a Windows 7 
Box from the installer available on 
http://www.enterprisedb.com/products-services-training/pgdownload#windows and 
don't tune or alter anything from the default PostgreSQL configuration 
-apart from allowed clients, listening addresses and ports-)

Thanks in advance,

Luis Alberto Suarez Cordero
Wimax Software
Bucaramanga, Colombia

Hello Luis,

Thank you for sharing your findings.

Best Regards,
Otto

Luis Alberto Suarez Corde wrote in message 
news:7d814468$77f79414$3b7de@news.alaska-software.com...
>Regards to all
>
>If you remember (me), i posted here quite a long time ago some issues 
>with PostgreSQL memory consumption related to PGDBE, making PostgreSQL 
>to run out of memory quickly while indexing or doing anything with big 
>ISAM tables ("big" here means a DbfUpsized-table with more than 500 
>records).
>
>Well... first of all, this issue hasn't been fixed yet, but this time, 
>it looks that we've found what might be causing it, at least: SET DELETED 
ON
>
>As our server-side indexing stored procedure no longer works (that was 
>our workaround to try to index all the upsized tables while keeping 
>PostgreSQL memory footprint under control), we had to go back to 
>xBase++'s standard functions for indexing ISAM tables using PGDBE... 
>only to see lots of out-of-memory error messages from PostgreSQL. We 
>tried to tune some PostgreSQL and PGDBE parameters in the past to reduce 
>(or at least try to control) this memory footprint, with no success. So, 
>as this was a kind of life-or-death issue to solve ASAP, we took a 
>different approach: look into our source code.
>
>This is where SET DELETED ON comes into the game.
>
>Looking again at the indexing process, and as our original source code 
>has SET DELETED ON activated BEFORE launching this process, PostgreSQL 
>memory footprint increases up to 1.7Gbytes (per session) to then 
>abrutply end with a "run out of memory" error message from PostgreSQL 
>side. And this while trying to index a 1500-record ISAM table.
>
>After disabling this directive, i.e. SET DELETED OFF, PostgreSQL memory 
>footprint is under acceptable limits (up to 200Mbytes after indexing 
>almost 100 tables with variable sizes) and the whole indexing procesess 
>can be executed in its entirety. This is our "new" workaround for the 
>first-use indexing of all ISAM tables right after the DbfUpsize process 
>is performed.
>
>Getting things clear, the memory issue doesn't lay on the DbCreateIndex 
>functions (and such). Instead, when any table is opened with SET DELETED 
>ON (for as the table must be opened first to then index it and then 
>close it and release its resources back to the server, at least in 
>theory....), PostgreSQL memory footprint increases not in the order of 
>tens of Megabytes, but in hundredths. With SET DELETED OFF, PostgreSQL 
>memory footprint increases too, but only by 10 or 20 Megs (and maybe 
>less) when the emulated workarea opens. This is a more manageable 
>scenario where our application would crash in a matter of hours, instead 
>of just a couple of minutes. Of course, disabling completely this 
>directive is not a feasible solution for us, because those deleted 
>records would produce fake reports and calculations and we cannot force 
>the user to perform a DbPack for each and every table he/she uses. We 
>would have to rewrite a lot of code, and, as far as we're concerned, one 
>of the PGDBE's main goals is to perform a seamless migration from DBF to 
>a relational database while mantaining the business logic.
>
>Sadly, i don't have a copy of the DBF, Upsize and PRG i sent a long time 
>ago to recreate this issue, but any of you can easily find any DBF with 
>more than 1000 records to then upsize it to Postgres and finally try to 
>browse it or do anything you want with it. Remember to put the SET 
>DELETED ON directive BEFORE opening the table to see by yourselves how 
>PostgreSQL memory consumption skyrockets. (For a more accurate 
>reproduction of this issue, install PostgreSQL 9.1 x86 on a Windows 7 
>Box from the installer available on 
>http://www.enterprisedb.com/products-services-training/pgdownload#windows 
and 
>don't tune or alter anything from the default PostgreSQL configuration 
>-apart from allowed clients, listening addresses and ports-)
>
>Thanks in advance,
>
>Luis Alberto Suarez Cordero
>Wimax Software
>Bucaramanga, Colombia

Luis,
What do mean by indexing? 
Are you talking about the PostgreSql database indexes or the “Alaska added 
columns”? 
This added columns has no associated index other than the “__record” . 
The ISAM “Index” is data in the added columns in each table why do you need 
to rewrite it?
I am just wandering as I am preparing a project to convert  my data to 
Postgres  I have some tables with 5M records

Deleted records @ SQL: sql servers just physicall delete record and reuse it
for storage asap.

If you really need deleted records for any reason (regulatory for tracing back
events) you should create a TRIGGER, that for seleted tables just copies data
to shadow/archive table for archive purposes.

http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

I do not know why would anyone use client side deleted on/off on if you have
other options available that are way faster and are proven to work.

No wonder you have horrible performance while managing 'deleted' via special
columns (as were special index key columns).

Is there documentation from Alaska why such solution is needed?


Itai Dishon wrote in message
news:4807a673$784b690a$1289dc@news.alaska-software.com...
>Luis Alberto Suarez Corde wrote in message 
>news:7d814468$77f79414$3b7de@news.alaska-software.com...
>>Regards to all
>>
>>If you remember (me), i posted here quite a long time ago some issues 
>>with PostgreSQL memory consumption related to PGDBE, making PostgreSQL 
>>to run out of memory quickly while indexing or doing anything with big 
>>ISAM tables ("big" here means a DbfUpsized-table with more than 500 
>>records).
>>
>>Well... first of all, this issue hasn't been fixed yet, but this time, 
>>it looks that we've found what might be causing it, at least: SET DELETED 
>ON
>>
>>As our server-side indexing stored procedure no longer works (that was 
>>our workaround to try to index all the upsized tables while keeping 
>>PostgreSQL memory footprint under control), we had to go back to 
>>xBase++'s standard functions for indexing ISAM tables using PGDBE... 
>>only to see lots of out-of-memory error messages from PostgreSQL. We 
>>tried to tune some PostgreSQL and PGDBE parameters in the past to reduce 
>>(or at least try to control) this memory footprint, with no success. So, 
>>as this was a kind of life-or-death issue to solve ASAP, we took a 
>>different approach: look into our source code.
>>
>>This is where SET DELETED ON comes into the game.
>>
>>Looking again at the indexing process, and as our original source code 
>>has SET DELETED ON activated BEFORE launching this process, PostgreSQL 
>>memory footprint increases up to 1.7Gbytes (per session) to then 
>>abrutply end with a "run out of memory" error message from PostgreSQL 
>>side. And this while trying to index a 1500-record ISAM table.
>>
>>After disabling this directive, i.e. SET DELETED OFF, PostgreSQL memory 
>>footprint is under acceptable limits (up to 200Mbytes after indexing 
>>almost 100 tables with variable sizes) and the whole indexing procesess 
>>can be executed in its entirety. This is our "new" workaround for the 
>>first-use indexing of all ISAM tables right after the DbfUpsize process 
>>is performed.
>>
>>Getting things clear, the memory issue doesn't lay on the DbCreateIndex 
>>functions (and such). Instead, when any table is opened with SET DELETED 
>>ON (for as the table must be opened first to then index it and then 
>>close it and release its resources back to the server, at least in 
>>theory....), PostgreSQL memory footprint increases not in the order of 
>>tens of Megabytes, but in hundredths. With SET DELETED OFF, PostgreSQL 
>>memory footprint increases too, but only by 10 or 20 Megs (and maybe 
>>less) when the emulated workarea opens. This is a more manageable 
>>scenario where our application would crash in a matter of hours, instead 
>>of just a couple of minutes. Of course, disabling completely this 
>>directive is not a feasible solution for us, because those deleted 
>>records would produce fake reports and calculations and we cannot force 
>>the user to perform a DbPack for each and every table he/she uses. We 
>>would have to rewrite a lot of code, and, as far as we're concerned, one 
>>of the PGDBE's main goals is to perform a seamless migration from DBF to 
>>a relational database while mantaining the business logic.
>>
>>Sadly, i don't have a copy of the DBF, Upsize and PRG i sent a long time 
>>ago to recreate this issue, but any of you can easily find any DBF with 
>>more than 1000 records to then upsize it to Postgres and finally try to 
>>browse it or do anything you want with it. Remember to put the SET 
>>DELETED ON directive BEFORE opening the table to see by yourselves how 
>>PostgreSQL memory consumption skyrockets. (For a more accurate 
>>reproduction of this issue, install PostgreSQL 9.1 x86 on a Windows 7 
>>Box from the installer available on 
>>http://www.enterprisedb.com/products-services-training/pgdownload#windows 
>and 
>>don't tune or alter anything from the default PostgreSQL configuration 
>>-apart from allowed clients, listening addresses and ports-)
>>
>>Thanks in advance,
>>
>>Luis Alberto Suarez Cordero
>>Wimax Software
>>Bucaramanga, Colombia
>
>Luis,
>What do mean by indexing? 
>Are you talking about the PostgreSql database indexes or the “Alaska added 
>columns”? 
>This added columns has no associated index other than the “__record” . 
>The ISAM “Index” is data in the added columns in each table why do you need 
>to rewrite it?
>I am just wandering as I am preparing a project to convert  my data to 
>Postgres  I have some tables with 5M records