Persistence problem

On 12 May 2010, at 18:08, I. B. wrote:

Hello.

I have a problem. I've created several types and functions in C language and implemented them successfully. Or at least I thought so... When I insert, select, update data, everything works fine, but in that session only. As soon as I close psql and start it agan, the data is corrupted. Is there some common mistake I might have made? Code is pretty big, so I don't think it would do any good if I put it here...

That sounds like what the documentation warns you about if you modify a structure directly from postgres without using palloc.

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,4beadfeb10411880534263!

That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:

mytype * result;
mytype * realResult;
result = createType(...);
realResult = (mytype *)palloc(mytype->length);
mempcy (realResult, result, result->length);

It didn't help. Is that enough?

Subject: Re: [GENERAL] Persistence problem
From: dalroi@solfertje.student.utwente.nl
Date: Wed, 12 May 2010 19:05:36 +0200
CC: pgsql-general@postgresql.org
To: i.bre@live.com

On 12 May 2010, at 18:08, I. B. wrote:

Hello.

I have a problem. I've created several types and functions in C language and implemented them successfully. Or at least I thought so... When I insert, select, update data, everything works fine, but in that session only. As soon as I close psql and start it agan, the data is corrupted. Is there some common mistake I might have made? Code is pretty big, so I don't think it would do any good if I put it here...

That sounds like what the documentation warns you about if you modify a structure directly from postgres without using palloc.

Alban Hertroys

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

!DSPAM:1077,4beadfe710418634015713!

_________________________________________________________________
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
https://signup.live.com/signup.aspx?id=60969

On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote:

That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:

mytype * result;
mytype * realResult;
result = createType(...);
realResult = (mytype *)palloc(mytype->length);
mempcy (realResult, result, result->length);

Did you define the type properly at SQL level? Is it a varlena type or
fixed length? Did you return it properly (as Datum)?

You're going to need to post more information before we can help you
usefully.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle

I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
mPoint *result;

result = (mPoint *) create_mPoint(str);

if (result == NULL) {
Log ("mpoint_in: reporting error for invalid input syntax for mPoint");
ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for mPoint: \"%s\"", str)));
}

PG_RETURN_POINTER(result);
}

In SQL the internallength for the type is set to VARIABLE. The type mpoint is complex. It is a structure containing other structures.

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

typedef mapping_t mPoint;

typedef struct {
timeint_t interval;
double x1, x0, y1, y0;
} uPoint;

typedef upoint_t uPoint;

typedef struct {
time_T start, end;
short int LC, RC;
} timeint_t;

typedef struct {
double time;
short int infinity;
} time_T;

The function create_mPoint (char *str) creates the type. It is pretty complex and I don't think it's smart to post it here. Anyway, it creates dinamycally array of uPoints for units. During that period, I've used palloc. At the end I set the variable length:

result->length = sizeof(int4) + sizeof(int) + result->noOfUnits * sizeof(uPoint);

My first guess was that I don't have all the data directly next to each other in the memory. Then I've tried to add this in the function mpoint_in, after I get the result from create_mPoint:

mPoint *finalResult;
finalResult = (mPoint *)palloc(sizeof(mPoint));
finalResult->units = (uPoint *)palloc(result->noOfUnits * sizeof(uPoint));
memcpy(finalResult->units, result->units, result->noOfUnits * sizeof(uPoint));
finalResult->noOfUnits = result->noOfUnits;
finalResult->length = result->length;
PG_RETURN_POINTER(finalResult);

When I call ToString (I've made it myself), I get the same content for both result and finalResult. However, unlike the first case when I return result and get normal data in that session which dissapears in next one, this time I get corrupted data even in the first session.

More code? More explanation? Or someone has an idea?

Thank you very much.
Ivan

Date: Wed, 12 May 2010 19:45:26 +0200
From: kleptog@svana.org
To: i.bre@live.com
CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Persistence problem

On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote:

That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:

mytype * result;
mytype * realResult;
result = createType(...);
realResult = (mytype *)palloc(mytype->length);
mempcy (realResult, result, result->length);

Did you define the type properly at SQL level? Is it a varlena type or
fixed length? Did you return it properly (as Datum)?

You're going to need to post more information before we can help you
usefully.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle

_________________________________________________________________
Hotmail: Trusted email with powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969

UPDATE:

When I do this:

realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);

I get a right result in the same session, but corrupted in the next one.
I've also found one place in create_mPoint where I used realloc. But why would it matter if I copied the memory into a new variable?

Ivan

From: i.bre@live.com
To: kleptog@svana.org
CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Persistence problem
Date: Thu, 13 May 2010 12:04:56 +0200

I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
mPoint *result;

result = (mPoint *) create_mPoint(str);

if (result == NULL) {
Log ("mpoint_in: reporting error for invalid input syntax for mPoint");
ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for mPoint: \"%s\"", str)));
}

PG_RETURN_POINTER(result);
}

In SQL the internallength for the type is set to VARIABLE. The type mpoint is complex. It is a structure containing other structures.

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

typedef mapping_t mPoint;

typedef struct {
timeint_t interval;
double x1, x0, y1, y0;
} uPoint;

typedef upoint_t uPoint;

typedef struct {
time_T start, end;
short int LC, RC;
} timeint_t;

typedef struct {
double time;
short int infinity;
} time_T;

The function create_mPoint (char *str) creates the type. It is pretty complex and I don't think it's smart to post it here. Anyway, it creates dinamycally array of uPoints for units. During that period, I've used palloc. At the end I set the variable length:

result->length = sizeof(int4) + sizeof(int) + result->noOfUnits * sizeof(uPoint);

My first guess was that I don't have all the data directly next to each other in the memory. Then I've tried to add this in the function mpoint_in, after I get the result from create_mPoint:

mPoint *finalResult;
finalResult = (mPoint *)palloc(sizeof(mPoint));
finalResult->units = (uPoint *)palloc(result->noOfUnits * sizeof(uPoint));
memcpy(finalResult->units, result->units, result->noOfUnits * sizeof(uPoint));
finalResult->noOfUnits = result->noOfUnits;
finalResult->length = result->length;
PG_RETURN_POINTER(finalResult);

When I call ToString (I've made it myself), I get the same content for both result and finalResult. However, unlike the first case when I return result and get normal data in that session which dissapears in next one, this time I get corrupted data even in the first session.

More code? More explanation? Or someone has an idea?

Thank you very much.
Ivan

Date: Wed, 12 May 2010 19:45:26 +0200
From: kleptog@svana.org
To: i.bre@live.com
CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Persistence problem

On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote:

That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:

mytype * result;
mytype * realResult;
result = createType(...);
realResult = (mytype *)palloc(mytype->length);
mempcy (realResult, result, result->length);

Did you define the type properly at SQL level? Is it a varlena type or
fixed length? Did you return it properly (as Datum)?

You're going to need to post more information before we can help you
usefully.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle

Hotmail: Trusted email with powerful SPAM protection. Sign up now.
_________________________________________________________________
Hotmail: Trusted email with powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969

On Thu, May 13, 2010 at 12:04:56PM +0200, I. B. wrote:

I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:

<snip>

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

This is not the correct way to handle varlena types. You can create the
datum that way, but if PostgreSQL decides to compress it (as it may
when writing to disk) you won't be able to read it back. Notably, the
"length" part of a varlena type is not always 4 bytes.

Make sure you have fully understood this page:
http://www.postgresql.org/docs/8.4/static/xfunc-c.html
it has a number of examples dealing with variable length types. You
MUST use the VARDATA/VARATT/etc macros to construct and read your data.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle

Thanks for the reply. However, I don't really understand how it works... Can you help? How should this function look like? Can I still create the type the same way I did for now and add something like this:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
mPoint *result;
mPoint *finalResult;

result = (mPoint *) create_mPoint(str);

if (result == NULL) {
Log ("mpoint_in: reporting error for invalid input syntax for mPoint");
ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for mPoint: \"%s\"", str)));
}

finalResult = (mPoint *)palloc(VARSIZE(result));
memset (finalResult, 0, VARSIZE(result));
SET_VARSIZE(finalResult, VARSIZE(result));
memcpy((void *) VARDATA(finalResult),
(void *) VARDATA(result),
VARSIZE(result) - VARHDRSZ);

PG_RETURN_POINTER(finalResult);
}

I should still return Datum, right? At least version 1 calling convetion says that. And should I change something on the SQL side?

I would appreciate any help.
Thanks.
Ivan

Date: Thu, 13 May 2010 12:42:09 +0200
From: kleptog@svana.org
To: i.bre@live.com
CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Persistence problem

On Thu, May 13, 2010 at 12:04:56PM +0200, I. B. wrote:

I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:

<snip>

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

This is not the correct way to handle varlena types. You can create the
datum that way, but if PostgreSQL decides to compress it (as it may
when writing to disk) you won't be able to read it back. Notably, the
"length" part of a varlena type is not always 4 bytes.

Make sure you have fully understood this page:
http://www.postgresql.org/docs/8.4/static/xfunc-c.html
it has a number of examples dealing with variable length types. You
MUST use the VARDATA/VARATT/etc macros to construct and read your data.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle

_________________________________________________________________
Hotmail: Free, trusted and rich email service.
https://signup.live.com/signup.aspx?id=60969

"I. B." <i.bre@live.com> writes:

When I do this:
realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);
I get a right result in the same session, but corrupted in the next
one.

I'm guessing a bit here, but I think what is happening is this:

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

You're storing the above-named struct on disk, right? And the "units"
pointer is pointing to an array that's somewhere else in memory? As
long as the somewhere-else array survives, it will seem like everything
is okay. But in a new session, that data in memory will certainly not
be there anymore.

You can't use pointers in data structures that are to be stored on disk.
The array data needs to be "in line" in the data structure, and
accounted for in the length word.

Martin's advice about using VARSIZE/VARDATA is good too. Depending on
which PG version you're using, you might be able to get along without
that so long as you haven't marked the data type toastable (by using
a non-PLAIN storage option in CREATE TYPE). But unless that array is
always pretty darn small, you're going to want to allow this type to
be toasted.

regards, tom lane

Hello all,

I have a hard situation in hands. my autovacuum does not seem to be able
to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away; I turn it back
on and it goes back to 50% iowait; my IO system is nothing special at all;

besides turning autovacuum off and running vacuum by hand once in a while,
what else can I do to get out of this situation ?

bellow some logs

I'm seriously considering turning off autovacuum for good; but I'dd like
to get input concerning other approaches... I mean... if I don't turn it
of, how can I be sure this will not happen again... we ship products with
PG inside... I must be absolutelly sure this will not ever happen in any of
our costumers. I'm a bit confuse... sorry :) !

Joao

============================================
2010-05-13 20:55:00 WEST DEBUG: server process (PID 6197) exited with
exit code 0
2010-05-13 20:55:00 WEST DEBUG: forked new backend, pid=6573 socket=6
2010-05-13 20:57:41 WEST ERROR: failed to re-find parent key in
"timeslots_strs_var_ts_key"
2010-05-13 20:58:42 WEST LOG: autovacuum: processing database
"postgres"
2010-05-13 20:59:35 WEST DEBUG: checkpoint starting
2010-05-13 20:59:35 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 20:59:42 WEST LOG: autovacuum: processing database
"template1"
2010-05-13 21:00:00 WEST DEBUG: server process (PID 6573) exited with
exit code 0
2010-05-13 21:00:00 WEST DEBUG: forked new backend, pid=6846 socket=6
2010-05-13 21:01:00 WEST LOG: autovacuum: processing database "egbert"
2010-05-13 21:01:00 WEST DEBUG: autovac: will VACUUM ANALYZE timeslots
2010-05-13 21:01:00 WEST DEBUG: vacuuming "public.timeslots"
2010-05-13 21:02:39 WEST DEBUG: index "timeslots_timestamp_index" now
contains 3971156 row versions in 15338 pages
2010-05-13 21:02:39 WEST DETAIL: 351 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.67s/1.28u sec elapsed 71.06 sec.
2010-05-13 21:03:51 WEST DEBUG: index "timeslots_timeslot_index" now
contains 3971159 row versions in 18724 pages
2010-05-13 21:03:51 WEST DETAIL: 351 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.86s/1.31u sec elapsed 71.65 sec.
2010-05-13 21:04:35 WEST DEBUG: checkpoint starting
2010-05-13 21:04:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:04:58 WEST DEBUG: index "timeslots_var_index" now
contains 3971167 row versions in 23857 pages
2010-05-13 21:04:58 WEST DETAIL: 351 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.34s/1.36u sec elapsed 67.49 sec.
2010-05-13 21:05:00 WEST DEBUG: server process (PID 6846) exited with
exit code 0
2010-05-13 21:05:01 WEST DEBUG: forked new backend, pid=7319 socket=6
2010-05-13 21:09:35 WEST DEBUG: checkpoint starting
2010-05-13 21:09:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:10:08 WEST DEBUG: server process (PID 7319) exited with
exit code 0
2010-05-13 21:10:08 WEST DEBUG: forked new backend, pid=7845 socket=6
2010-05-13 21:14:35 WEST DEBUG: checkpoint starting
2010-05-13 21:14:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:15:00 WEST DEBUG: server process (PID 7845) exited with
exit code 0
2010-05-13 21:15:00 WEST DEBUG: forked new backend, pid=8214 socket=6
2010-05-13 21:19:35 WEST DEBUG: checkpoint starting
2010-05-13 21:19:41 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:20:00 WEST DEBUG: server process (PID 8214) exited with
exit code 0
2010-05-13 21:20:00 WEST DEBUG: forked new backend, pid=8521 socket=6
2010-05-13 21:24:35 WEST DEBUG: checkpoint starting
2010-05-13 21:24:38 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:25:00 WEST DEBUG: server process (PID 8521) exited with
exit code 0
2010-05-13 21:25:00 WEST DEBUG: forked new backend, pid=8919 socket=6
2010-05-13 21:29:37 WEST DEBUG: checkpoint starting
2010-05-13 21:29:40 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:30:00 WEST DEBUG: server process (PID 8919) exited with
exit code 0
2010-05-13 21:30:00 WEST DEBUG: forked new backend, pid=9239 socket=6
2010-05-13 21:34:35 WEST DEBUG: checkpoint starting
2010-05-13 21:34:38 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:35:00 WEST DEBUG: server process (PID 9239) exited with
exit code 0
2010-05-13 21:35:00 WEST DEBUG: forked new backend, pid=9672 socket=6
2010-05-13 21:39:35 WEST DEBUG: checkpoint starting
2010-05-13 21:39:36 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:40:00 WEST DEBUG: server process (PID 9672) exited with
exit code 0
2010-05-13 21:40:00 WEST DEBUG: forked new backend, pid=9979 socket=6
2010-05-13 21:44:35 WEST DEBUG: checkpoint starting
2010-05-13 21:44:36 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:44:59 WEST ERROR: failed to re-find parent key in
"timeslots_strs_var_ts_key"
2010-05-13 21:45:00 WEST DEBUG: server process (PID 9979) exited with
exit code 0
2010-05-13 21:45:00 WEST DEBUG: forked new backend, pid=10340 socket=6
2010-05-13 21:46:00 WEST LOG: autovacuum: processing database
"postgres"
2010-05-13 21:47:00 WEST LOG: autovacuum: processing database
"template1"
2010-05-13 21:48:01 WEST LOG: autovacuum: processing database "egbert"
2010-05-13 21:48:01 WEST DEBUG: autovac: will VACUUM ANALYZE timeslots
2010-05-13 21:48:01 WEST DEBUG: vacuuming "public.timeslots"
2010-05-13 21:49:30 WEST DEBUG: index "timeslots_timestamp_index" now
contains 3971435 row versions in 15340 pages
2010-05-13 21:49:30 WEST DETAIL: 405 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.78s/1.23u sec elapsed 63.81 sec.
2010-05-13 21:49:35 WEST DEBUG: checkpoint starting
2010-05-13 21:49:37 WEST DEBUG: removing file "pg_subtrans/00AE"
2010-05-13 21:49:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:50:00 WEST DEBUG: server process (PID 10340) exited with
exit code 0
2010-05-13 21:50:01 WEST DEBUG: forked new backend, pid=10642 socket=6
2010-05-13 21:50:47 WEST DEBUG: index "timeslots_timeslot_index" now
contains 3971451 row versions in 18726 pages
2010-05-13 21:50:47 WEST DETAIL: 405 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.88s/1.32u sec elapsed 76.99 sec.
2010-05-13 21:51:56 WEST DEBUG: index "timeslots_var_index" now
contains 3971471 row versions in 23861 pages
2010-05-13 21:51:56 WEST DETAIL: 405 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.10s/1.40u sec elapsed 69.38 sec.
2010-05-13 21:54:35 WEST DEBUG: checkpoint starting
2010-05-13 21:54:36 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:55:00 WEST DEBUG: server process (PID 10642) exited with
exit code 0
2010-05-13 21:55:00 WEST DEBUG: forked new backend, pid=11061 socket=6
2010-05-13 21:59:35 WEST DEBUG: checkpoint starting
2010-05-13 21:59:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:00:00 WEST DEBUG: server process (PID 11061) exited with
exit code 0
2010-05-13 22:00:01 WEST DEBUG: forked new backend, pid=11398 socket=6
2010-05-13 22:04:35 WEST DEBUG: checkpoint starting
2010-05-13 22:04:36 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:05:00 WEST DEBUG: forked new backend, pid=11725 socket=6
2010-05-13 22:05:00 WEST DEBUG: server process (PID 11398) exited with
exit code 0
2010-05-13 22:09:35 WEST DEBUG: checkpoint starting
2010-05-13 22:09:39 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:10:00 WEST DEBUG: server process (PID 11725) exited with
exit code 0
2010-05-13 22:10:00 WEST DEBUG: forked new backend, pid=12073 socket=6
2010-05-13 22:14:35 WEST DEBUG: checkpoint starting
2010-05-13 22:14:39 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:15:00 WEST DEBUG: forked new backend, pid=12328 socket=6
2010-05-13 22:15:00 WEST DEBUG: server process (PID 12073) exited with
exit code 0
2010-05-13 22:19:35 WEST DEBUG: checkpoint starting
2010-05-13 22:19:38 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:20:00 WEST DEBUG: server process (PID 12328) exited with
exit code 0
2010-05-13 22:20:00 WEST DEBUG: forked new backend, pid=12601 socket=6
2010-05-13 22:24:35 WEST DEBUG: checkpoint starting
2010-05-13 22:24:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:25:02 WEST DEBUG: server process (PID 12601) exited with
exit code 0
2010-05-13 22:25:02 WEST DEBUG: forked new backend, pid=12864 socket=6
2010-05-13 22:29:35 WEST DEBUG: checkpoint starting
2010-05-13 22:29:36 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:30:00 WEST DEBUG: server process (PID 12864) exited with
exit code 0
2010-05-13 22:30:00 WEST DEBUG: forked new backend, pid=13109 socket=6
2010-05-13 22:31:29 WEST ERROR: failed to re-find parent key in
"timeslots_strs_var_ts_key"
2010-05-13 22:32:29 WEST LOG: autovacuum: processing database
"postgres"
2010-05-13 22:33:29 WEST LOG: autovacuum: processing database
"template1"
2010-05-13 22:34:29 WEST LOG: autovacuum: processing database "egbert"
2010-05-13 22:34:29 WEST DEBUG: autovac: will VACUUM ANALYZE timeslots
2010-05-13 22:34:29 WEST DEBUG: vacuuming "public.timeslots"
2010-05-13 22:34:35 WEST DEBUG: checkpoint starting
2010-05-13 22:34:36 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:35:00 WEST DEBUG: server process (PID 13109) exited with
exit code 0
2010-05-13 22:35:00 WEST DEBUG: forked new backend, pid=13329 socket=6
2010-05-13 22:36:06 WEST DEBUG: index "timeslots_timestamp_index" now
contains 3971652 row versions in 15341 pages
2010-05-13 22:36:06 WEST DETAIL: 321 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.81s/1.24u sec elapsed 72.89 sec.
2010-05-13 22:37:16 WEST DEBUG: index "timeslots_timeslot_index" now
contains 3971652 row versions in 18727 pages
2010-05-13 22:37:16 WEST DETAIL: 321 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.97s/1.35u sec elapsed 69.51 sec.
2010-05-13 22:38:27 WEST DEBUG: index "timeslots_var_index" now
contains 3971656 row versions in 23861 pages
2010-05-13 22:38:27 WEST DETAIL: 321 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.39s/1.43u sec elapsed 70.76 sec.
2010-05-13 22:39:35 WEST DEBUG: checkpoint starting
2010-05-13 22:39:38 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:40:00 WEST DEBUG: server process (PID 13329) exited with
exit code 0
2010-05-13 22:40:00 WEST DEBUG: forked new backend, pid=13549 socket=6
2010-05-13 22:44:36 WEST DEBUG: checkpoint starting
2010-05-13 22:44:45 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:45:00 WEST DEBUG: server process (PID 13549) exited with
exit code 0
2010-05-13 22:45:00 WEST DEBUG: forked new backend, pid=13918 socket=6
2010-05-13 22:49:35 WEST DEBUG: checkpoint starting
2010-05-13 22:49:38 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:50:01 WEST DEBUG: server process (PID 13918) exited with
exit code 0
2010-05-13 22:50:01 WEST DEBUG: forked new backend, pid=14218 socket=6
2010-05-13 22:50:22 WEST DEBUG: forked new backend, pid=14278 socket=6
2010-05-13 22:51:11 WEST DEBUG: server process (PID 14278) exited with
exit code 0
2010-05-13 22:51:20 WEST DEBUG: forked new backend, pid=14368 socket=6
2010-05-13 22:51:22 WEST DEBUG: server process (PID 14368) exited with
exit code 0
2010-05-13 22:52:52 WEST DEBUG: forked new backend, pid=14516 socket=6
2010-05-13 22:52:52 WEST DEBUG: server process (PID 14516) exited with
exit code 0
2010-05-13 22:54:35 WEST DEBUG: checkpoint starting
2010-05-13 22:54:37 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:55:00 WEST DEBUG: server process (PID 14218) exited with
exit code 0
2010-05-13 22:55:00 WEST DEBUG: forked new backend, pid=14775 socket=6
2010-05-13 22:59:35 WEST DEBUG: checkpoint starting
2010-05-13 22:59:40 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 23:00:01 WEST DEBUG: server process (PID 14775) exited with
exit code 0
2010-05-13 23:00:01 WEST DEBUG: forked new backend, pid=15443 socket=6
2010-05-13 23:04:35 WEST DEBUG: checkpoint starting
2010-05-13 23:04:43 WEST DEBUG: checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 23:05:00 WEST DEBUG: forked new backend, pid=16028 socket=6
2010-05-13 23:05:00 WEST DEBUG: server process (PID 15443) exited with
exit code 0

Joao Ferreira <joao.miguel.c.ferreira@gmail.com> writes:

I have a hard situation in hands. my autovacuum does not seem to be able
to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away; I turn it back
on and it goes back to 50% iowait; my IO system is nothing special at all;

Indeed, it sounds like you don't have enough I/O capacity for the size
of database you are running.

Turning off autovacuum is extremely unlikely to be a good solution;
it might reduce the bandwidth demand but it will just hasten the
database's bloating into unusability.

2010-05-13 21:44:59 WEST ERROR: failed to re-find parent key in
"timeslots_strs_var_ts_key"

You seem to have some worse problems than disk bandwidth, though :-(.
The above is not good at all. What PG version is this? If not recent,
update, then reindex that index.

regards, tom lane

On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.com> wrote:

Hello all,

I have a hard situation in hands. my autovacuum does not seem to be able
to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away; I turn it back
on and it goes back to 50% iowait; my IO system is nothing special at all;

besides turning autovacuum off and running vacuum by hand once in a while,
what else can I do to get out of this situation ?

bellow some logs

I'm seriously considering turning off autovacuum for good; but I'dd like
to get input concerning other approaches... I mean... if I don't turn it
of, how can I be sure this will not happen again... we ship products with
PG inside... I must be absolutelly sure this will not ever happen in any of
our costumers. I'm a bit confuse... sorry :) !

Have you considered tuning autovacuum to not use less IO so that it
has no serious impact on other running pg processes? it's pretty easy
to do, just don't go crazy (i.e. move autovacuum_vacuum_cost_delay
from 10 to 20 or 30 ms, not 2000ms)

On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.com> wrote:

Hello all,

I have a hard situation in hands. my autovacuum does not seem to be able
to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away; I turn it

back

on and it goes back to 50% iowait; my IO system is nothing special at

all;

besides turning autovacuum off and running vacuum by hand once in a

while,

what else can I do to get out of this situation ?

bellow some logs

I'm seriously considering turning off autovacuum for good; but I'dd like
to get input concerning other approaches... I mean... if I don't turn it
of, how can I be sure this will not happen again... we ship products with
PG inside... I must be absolutelly sure this will not ever happen in any

of

our costumers. I'm a bit confuse... sorry :) !

Have you considered tuning autovacuum to not use less IO so that it
has no serious impact on other running pg processes? it's pretty easy
to do, just don't go crazy (i.e. move autovacuum_vacuum_cost_delay
from 10 to 20 or 30 ms, not 2000ms)

+ 1 here, start with a 20ms delay, your vacuums make take a bit longer to
run, but they'll have less impact on I/O.

Just curious, what is your log_min_messages setting? I notice that you had
'DEBUG' in your logs, I'm guessing that you've just cranked up to DEBUG for
your testing.... make sure that you leave that 'warning' or 'notice' for
production, leaving those logs at DEBUG will also chew up I/O and get in the
way of things like autovacuum.

What version of Postgres are you using? The visibility map in 8.4 should
lower the amount of I/O that you're stuck with (in *most* cases) with
vacuum. Although you'll still need a full table scan to avoid xid wrap, you
should get away with only vacuuming changed blocks in the general case.

On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.com> wrote:

Hello all,

I have a hard situation in hands. my autovacuum does not seem to be able
to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away; I turn it

back

on and it goes back to 50% iowait; my IO system is nothing special at

all;

besides turning autovacuum off and running vacuum by hand once in a

while,

what else can I do to get out of this situation ?

bellow some logs

I'm seriously considering turning off autovacuum for good; but I'dd like
to get input concerning other approaches... I mean... if I don't turn it
of, how can I be sure this will not happen again... we ship products with
PG inside... I must be absolutelly sure this will not ever happen in any

of

our costumers. I'm a bit confuse... sorry :) !

Have you considered tuning autovacuum to not use less IO so that it
has no serious impact on other running pg processes? it's pretty easy
to do, just don't go crazy (i.e. move autovacuum_vacuum_cost_delay
from 10 to 20 or 30 ms, not 2000ms)

+ 1 here, start with a 20ms delay, your vacuums make take a bit longer to
run, but they'll have less impact on I/O.

Just curious, what is your log_min_messages setting? I notice that you had
'DEBUG' in your logs, I'm guessing that you've just cranked up to DEBUG for
your testing.... make sure that you leave that 'warning' or 'notice' for
production, leaving those logs at DEBUG will also chew up I/O and get in the
way of things like autovacuum.

What version of Postgres are you using? The visibility map in 8.4 should
lower the amount of I/O that you're stuck with (in*most* cases) with vacuum.
Although you'll still need a full table scan to avoid xid wrap, you should
get away with only vacuuming changed blocks in the general case.

--
Scott Mead
EnterpriseDB
The Enterprise Postgres Company
www.enterprisedb.com

Show quoted text

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

Thanks for the reply.
Why is that somewhere else in the memory if I reserve enough memory with palloc and copy the complete memory from the previously created type into that new object?

realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);

OK, I suppose I should use the VARDATA and VARSIZE. But I really can't make it to work. I don't think I understood well enough how it works. This is what I'd like to do. I would like to create the type as I already did, and when it's created I would like to copy its memory into a new object. So, if I have:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
mPoint *result = (mPoint *) create_mPoint(str);

mPoint *final result = ... //copy the memory from result

PG_RETURN_POINTER(result);
}

Datum mpoint_out(PG_FUNCTION_ARGS)
{
mPoint * input = (mPoint *) PG_GETARG_POINTER(0);
char * result = ToString (input, MPOINT);
PG_RETURN_CSTRING(result);
}

Can you please help me to make this? How to copy the memory in a good way? Should I somehow change the type mPoint? Should I also change the SQL side?

CREATE TYPE mpoint (
internallength = VARIABLE,
input = mpoint_in,
output = mpoint_out
);

Please help, it would mean a lot.

Thanks,
Ivan

To: i.bre@live.com
CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Persistence problem
Date: Thu, 13 May 2010 15:08:58 -0400
From: tgl@sss.pgh.pa.us

"I. B." <i.bre@live.com> writes:

When I do this:
realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);
I get a right result in the same session, but corrupted in the next
one.

I'm guessing a bit here, but I think what is happening is this:

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

You're storing the above-named struct on disk, right? And the "units"
pointer is pointing to an array that's somewhere else in memory? As
long as the somewhere-else array survives, it will seem like everything
is okay. But in a new session, that data in memory will certainly not
be there anymore.

You can't use pointers in data structures that are to be stored on disk.
The array data needs to be "in line" in the data structure, and
accounted for in the length word.

Martin's advice about using VARSIZE/VARDATA is good too. Depending on
which PG version you're using, you might be able to get along without
that so long as you haven't marked the data type toastable (by using
a non-PLAIN storage option in CREATE TYPE). But unless that array is
always pretty darn small, you're going to want to allow this type to
be toasted.

regards, tom lane

_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969

I still have the same problem. Whatever I've tried didn't work out.
It seems like VARSIZE is wrong. It's less than it should be. It seems like it's not counting on the size of units array, although it changes depending on the number of units.
This is one of the things I've tried:

Datum mbool_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
mBool *result;
mBool *finalResult;
int size;

result = (mBool *) create_mConst(str, MBOOL);

size = sizeof(int4) + sizeof(int) + result->noOfUnits*sizeof(uBool);
finalResult = (mBool *) palloc(size);
memcpy(finalResult, result, size);
SET_VARSIZE(finalResult, size);

PG_RETURN_POINTER(finalResult);
}

Datum mbool_out(PG_FUNCTION_ARGS)
{
mBool * input = (mBool *) PG_GETARG_POINTER(0);
mBool * safeCopy;
char * result;

safeCopy = (mBool *) palloc(VARSIZE(input));
SET_VARSIZE(safeCopy, VARSIZE(input));
memcpy((void *) VARDATA(safeCopy),
(void *) VARDATA(input),
VARSIZE(input));

result = ToString (safeCopy, MBOOL);

PG_RETURN_CSTRING(result);
}

How to fix this? The one who helps can count on the best Croatian dark beer shipped directly to his place. ;)

Ivan

From: i.bre@live.com
To: tgl@sss.pgh.pa.us
CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Persistence problem
Date: Fri, 14 May 2010 14:35:34 +0200

Thanks for the reply.
Why is that somewhere else in the memory if I reserve enough memory with palloc and copy the complete memory from the previously created type into that new object?

realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);

OK, I suppose I should use the VARDATA and VARSIZE. But I really can't make it to work. I don't think I understood well enough how it works. This is what I'd like to do. I would like to create the type as I already did, and when it's created I would like to copy its memory into a new object. So, if I have:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
mPoint *result = (mPoint *) create_mPoint(str);

mPoint *final result = ... //copy the memory from result

PG_RETURN_POINTER(result);
}

Datum mpoint_out(PG_FUNCTION_ARGS)
{
mPoint * input = (mPoint *) PG_GETARG_POINTER(0);
char * result = ToString (input, MPOINT);
PG_RETURN_CSTRING(result);
}

Can you please help me to make this? How to copy the memory in a good way? Should I somehow change the type mPoint? Should I also change the SQL side?

CREATE TYPE mpoint (
internallength = VARIABLE,
input = mpoint_in,
output = mpoint_out
);

Please help, it would mean a lot.

Thanks,
Ivan

To: i.bre@live.com
CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Persistence problem
Date: Thu, 13 May 2010 15:08:58 -0400
From: tgl@sss.pgh.pa.us

"I. B." <i.bre@live.com> writes:

When I do this:
realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);
I get a right result in the same session, but corrupted in the next
one.

I'm guessing a bit here, but I think what is happening is this:

typedef struct {
int4 length;
int noOfUnits;
void *units; // this is later casted to uPoint *
} mapping_t;

You're storing the above-named struct on disk, right? And the "units"
pointer is pointing to an array that's somewhere else in memory? As
long as the somewhere-else array survives, it will seem like everything
is okay. But in a new session, that data in memory will certainly not
be there anymore.

You can't use pointers in data structures that are to be stored on disk.
The array data needs to be "in line" in the data structure, and
accounted for in the length word.

Martin's advice about using VARSIZE/VARDATA is good too. Depending on
which PG version you're using, you might be able to get along without
that so long as you haven't marked the data type toastable (by using
a non-PLAIN storage option in CREATE TYPE). But unless that array is
always pretty darn small, you're going to want to allow this type to
be toasted.

regards, tom lane

Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up now.
_________________________________________________________________
Hotmail: Trusted email with powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969

"I. B." <i.bre@live.com> writes:

How to fix this?

As long as you keep on showing us wrappers, and not the code that
actually does the work, we're going to remain in the dark. What
you have shown us just copies data from point A to point B, and
it looks like it would be fine if the source data conforms to
PG's expectations. But the problem is presumably that the source
data *doesn't* conform to those expectations.

regards, tom lane

OK, here is the part of the code.

typedef struct {
int4 length;
int noOfUnits;
void *units;
} mapping_t;

typedef struct {
timeint_t interval;
double x1, x0, y1, y0;
// fx(t) = x1*t+x0, fy(t) = y1*t+y0
} upoint_t;

typedef struct {
time_T start, end;
short int LC, RC; // left or right closed
} timeint_t;

typedef struct {
double time;
short int infinity;
} time_T;

typedef upoint_t uPoint;
typedef mapping_t mPoint;

mPoint * create_mPoint(char *str) {
/*
*
* str: ((start end x1 x0 y1 y0),(start end x1 x0 y1 y0),...)
*
*/

char *unit = NULL;
mPoint * result = NULL;
uPoint * units = NULL;
uPoint * singleUnit;
int stringLength;
char *strTemp;
int i;

if (str == NULL) {
return NULL;
}

stringLength = strlen(str);

if (stringLength < 2 || str[0] != '(' || str[stringLength-1] != ')') {
return NULL;
} else {
str = &str[1];
strTemp = (char *) palloc ((stringLength-1)*sizeof(char));
strncpy(strTemp, str, stringLength-2);
strTemp[stringLength-2] = '\0';
str = strTemp;
};

// allocating memory
result = (mPoint *) palloc(sizeof(mPoint));

result->noOfUnits = 0;
unit = strtok(str, ",");

while (unit != NULL) {
Log ("create_mPoint: str units:");
Log2 ("\tunit:", unit);

result->noOfUnits++;

singleUnit = create_uPoint(unit);

if (singleUnit == NULL) {
return NULL;
}

units = (uPoint *) realloc(units, result->noOfUnits * sizeof(uPoint)); // EXPLAINED AT THE END OF THE POST
units[result->noOfUnits - 1] = * singleUnit;

unit = strtok(NULL, ",");
};

result->units = units; // EXPLAINED AT THE END OF THE POST

if (SortUnits(result, MPOINT) == NULL) {
return NULL;
};

result->length = sizeof(int4) + sizeof(int) + result->noOfUnits * sizeof(uPoint);

//pfree(singleUnit);
//pfree(strTemp);
//pfree(unit);

Log ("create_mPoint: moving type created");

return result;
};

uPoint * create_uPoint(char *str) {

double startTime, endTime;

double x0, x1, y0, y1;

timeint_t * interval;

uPoint *result;

if (sscanf(str, " ( %lf %lf %lf %lf %lf %lf )", &startTime, &endTime, &x1, &x0, &y1, &y0) != 6) {

return NULL;

}

// allocate memory

result = (uPoint *) palloc(sizeof(uPoint));

result->x0 = x0;

result->x1 = x1;

result->y0 = y0;

result->y1 = y1;

// insert interval

interval = (timeint_t *) createTimeInterval(startTime, endTime);

if (interval == NULL) {

return NULL;

};

result->interval = *interval;

Log ("create_uPoint: uPoint (unit type) created");

return result;

};

timeint_t * createTimeInterval(double startTime, double endTime) {
timeint_t * interval = (timeint_t *) palloc(sizeof(timeint_t));

Log ("createTimeInterval: entering function");

if (startTime > endTime) {
return NULL;
};

interval->start.time = startTime;
interval->end.time = endTime;

if (startTime < 0) {
interval->LC = 0;
interval->start.infinity = 1;
}
else {
interval->LC = 1;
interval->start.infinity = 0;
}
if (endTime < 0) {
interval->RC = 0;
interval->end.infinity = 1;
}
else {
interval->RC = 1;
interval->end.infinity = 1;
}

return interval;
};

The part where I've used realloc and set result->units=units,
I've tried to replace it with palloc in the way to create the array
units and then do this:

result->units = (uPoint *) palloc(result->noOfUnits * sizeof(uPoint));
memcpy(result->units, units, result->noOfUnits * sizeof(uPoint));

I've also tried to reserve the whole memory with palloc:

result = (mPoint *)palloc(sizeof(int4) + sizeof(int) + noOfUnits*sizeof(uPoint));
result->noOfUnits = noOfUnits;
result->length = sizeof(int4) + sizeof(int) + noOfUnits*sizeof(uPoint);
memcpy(result->units, units, result->noOfUnits * sizeof(uPoint));

I've then tried to palloc both result and result->units... I just don't know how I should do it. :-/
I hope you understand the problem better now.

Thanks,
Ivan

To: i.bre@live.com
CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Persistence problem
Date: Fri, 14 May 2010 17:32:49 -0400
From: tgl@sss.pgh.pa.us

"I. B." <i.bre@live.com> writes:

How to fix this?

As long as you keep on showing us wrappers, and not the code that
actually does the work, we're going to remain in the dark. What
you have shown us just copies data from point A to point B, and
it looks like it would be fine if the source data conforms to
PG's expectations. But the problem is presumably that the source
data *doesn't* conform to those expectations.

regards, tom lane

_________________________________________________________________
Hotmail: Free, trusted and rich email service.
https://signup.live.com/signup.aspx?id=60969

"I. B." <i.bre@live.com> writes:

OK, here is the part of the code.

Well, as suspected, you're doing this

typedef struct {
void *units;
} mapping_t;

and this

units = (uPoint *) realloc(units, result->noOfUnits * sizeof(uPoint)); // EXPLAINED AT THE END OF THE POST

which means that the array isn't contiguous with the mPoint struct.
You can certainly do that and then rearrange things to make it so
afterwards, but you're not doing so now. Personally though I'd avoid
having two different representations. You'd be better off with

typedef struct {
int4 length;
int noOfUnits;
uPoint units[1]; /* actually, a variable length array */
} mPoint;

and then allocating or reallocating the result struct with a size
calculation like this:

offsetof(mPoint, units) + noOfUnits * sizeof(uPoint)

BTW, realloc (as opposed to repalloc) doesn't seem like a tremendously
good idea here. You are leaking that memory for the life of the session.

regards, tom lane

Hello guys,

thx for your inputs. I consider you suggestions valid.

We have hundreds or thousands of unreachable and unmaintained PG
instalations. I'm totally unable to experiment in each of them. Usage
profile can range from 100 rows per hour to 1000, 10.000, 50.000...
sustained... for several days... or even forever... CPU's and IO
subsystem also varies.. from cheap IO to fast enterprise grade hardware.

But I did try vacuum_cost_delay and the effect is as expected: IOWAIT
reduces :) but vacuum time increases even more :( but it still does not
leave the processor alone; autovaccum is still not able to finish it's
job... and I can't wait forever.

We are using pg 8.1.4, and yes we a) reindex b) vacuum full and c)
reindex again once a week up to once a month; this weekly/monthly
maintenance script has been quite effective as a workaround for the
"can't find parent index" bug... we can eliminate heavy bloats just by
running the script... but it also plays an important role in keeping
database correctly indexed (reindex) and responsive.

My experience with this scenario tells me (I may be wrong) that I don't
really need autovacuum as long as I keep those periodic maintenance
operations running (tipically Saturdays or Sundays during the night)

For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just
live with it. And learn to work around the issues it might bring me.
Additionally I can not change the cheap storage we ship in some lower
end versions of our product.

So, I'm still considering turning off autovacuum.

any thoughts ?

I really appreciate the discussion. thx a lot.

Cheers
Joao

Show quoted text

On Fri, 2010-05-14 at 06:47 -0400, Scott Mead wrote:

On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe
<scott.marlowe@gmail.com> wrote:
On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.com> wrote:

Hello all,

I have a hard situation in hands. my autovacuum does not

seem to be able

to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away;

I turn it back

on and it goes back to 50% iowait; my IO system is nothing

special at all;

besides turning autovacuum off and running vacuum by hand

once in a while,

what else can I do to get out of this situation ?

bellow some logs

I'm seriously considering turning off autovacuum for good;

but I'dd like

to get input concerning other approaches... I mean... if I

don't turn it

of, how can I be sure this will not happen again... we ship

products with

PG inside... I must be absolutelly sure this will not ever

happen in any of

our costumers. I'm a bit confuse... sorry :) !

Have you considered tuning autovacuum to not use less IO so
that it
has no serious impact on other running pg processes? it's
pretty easy
to do, just don't go crazy (i.e. move
autovacuum_vacuum_cost_delay
from 10 to 20 or 30 ms, not 2000ms)

+ 1 here, start with a 20ms delay, your vacuums make take a bit longer
to run, but they'll have less impact on I/O.

Just curious, what is your log_min_messages setting? I notice that you
had 'DEBUG' in your logs, I'm guessing that you've just cranked up to
DEBUG for your testing.... make sure that you leave that 'warning' or
'notice' for production, leaving those logs at DEBUG will also chew up
I/O and get in the way of things like autovacuum.

What version of Postgres are you using? The visibility map in 8.4
should lower the amount of I/O that you're stuck with (inmost cases)
with vacuum. Although you'll still need a full table scan to avoid
xid wrap, you should get away with only vacuuming changed blocks in
the general case.

--
Scott Mead
EnterpriseDB
The Enterprise Postgres Company
www.enterprisedb.com

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