Обсуждение: Postgres 6.5.1 Error

Morning,

Our Crossover project seems to have presented another test production error.

Last I wrote was how does one get data from one database to another from
within Postgres.

This time I have data that is actually causing postgres to go into a
cascade failure that actually takes down the server.

What we did was take data from an Access97 application. We exported the
tables to a postgres database. We have done this for another Access97
application and have had no probles with it. 

During testing and development of a web application everything worked ok.

When we went into beta test we had one agency that when ever they accessed
their data it would cause postgres to go into a cascade failure and take
the server down. IF I can catch it in time I can kill the process. 


Tech Specs:

Hardware- PII 300 with 128 mg ram         6 gig ide hard drive         2 gig ide hard drive

Software- Apache 1.3.6 Web server         PostgreSQL 6.5.1          Linux 2.2.6         FPK Pascal 0.99.12

FPK pascal uses the libpq library created by the PostgreSQL distribution. I
can also achieve the same results with perl.

Unfortunately, since this is a state agency, I cannot release the full
records. I can say there appears to be control characters in the data.
Specifically 5, 8, and 9. These appear to be unicode characters.

I need to do one of the following:

a> filter the data
b> determine if this was a malicious act or an accident
c> prevent the server from crashing
d> find out if this only affects one agency or if there are    others we
haven't encountered yet. 

Thanks,

-Rob




************

Robert Hiltibidal <rob@y2k.state.il.us> writes:
> When we went into beta test we had one agency that when ever they accessed
> their data it would cause postgres to go into a cascade failure and take
> the server down. IF I can catch it in time I can kill the process. 

"Cascade failure" doesn't convey anything to me ... can you be more
specific?  What error messages do you find in the postmaster log, if
any?

> Unfortunately, since this is a state agency, I cannot release the full
> records. I can say there appears to be control characters in the data.

Can you show us the queries being used?
        regards, tom lane

************

Howdy,

>"Cascade failure" doesn't convey anything to me ... can you be more
>specific?  What error messages do you find in the postmaster log, if
>any?
>

I define a cascade failure as one application tying up so much processr
time and/or memory nothing else can work. Ultimately the server locks up if
I am unable to kill the process in time.

There are o errors listed in the postgres log. 


The query is dynamically generated. It works for 62 of the 63 agencies we
track. here is the pascal source code for generating the query:

BuffCode := 'Select distinct
propertyname,address,city,state,zip,recnum,agencyname from tblInfra where
agencycode = ';
SqlQuery := StrAlloc(Strlen(BuffCode) + StrLen(AgencyCode) + StrLen(Sort)+1);
StrPCopy(SqlQuery,BuffCode);   StrCat(SqlQuery,'''');   StrCat(SqlQuery,AgencyCode);   StrCat(SqlQuery,'''');
StrCat(SqlQuery,'order by ');   StrCat(SqlQuery,Sort);   StrCat(SqlQuery,',recnum ');   Results :=
DataEase(DBName,SqlQuery);
 

So the query ends up being:

(Using DOT as anample)

SQLquery = Select distinct
propertyname,address,city,state,zip,recnum,agencyname from
tblInfra where agencycode = 'DOT' order by propertyname,recnum 

This works for all the other state agencies to date.

-Rob


************

Robert Hiltibidal <rob@y2k.state.il.us> writes:
> When we went into beta test we had one agency that when ever they accessed
> their data it would cause postgres to go into a cascade failure and take
> the server down. IF I can catch it in time I can kill the process. 

"Cascade failure" doesn't convey anything to me ... can you be more
specific?  What error messages do you find in the postmaster log, if
any?

> Unfortunately, since this is a state agency, I cannot release the full
> records. I can say there appears to be control characters in the data.

Can you show us the queries being used?
        regards, tom lane

************

Howdy,

>"Cascade failure" doesn't convey anything to me ... can you be more
>specific?  What error messages do you find in the postmaster log, if
>any?
>

I define a cascade failure as one application tying up so much processr
time and/or memory nothing else can work. Ultimately the server locks up if
I am unable to kill the process in time.

There are o errors listed in the postgres log. 


The query is dynamically generated. It works for 62 of the 63 agencies we
track. here is the pascal source code for generating the query:

BuffCode := 'Select distinct
propertyname,address,city,state,zip,recnum,agencyname from tblInfra where
agencycode = ';
SqlQuery := StrAlloc(Strlen(BuffCode) + StrLen(AgencyCode) + StrLen(Sort)+1);
StrPCopy(SqlQuery,BuffCode);   StrCat(SqlQuery,'''');   StrCat(SqlQuery,AgencyCode);   StrCat(SqlQuery,'''');
StrCat(SqlQuery,'order by ');   StrCat(SqlQuery,Sort);   StrCat(SqlQuery,',recnum ');   Results :=
DataEase(DBName,SqlQuery);
 

So the query ends up being:

(Using DOT as anample)

SQLquery = Select distinct
propertyname,address,city,state,zip,recnum,agencyname from
tblInfra where agencycode = 'DOT' order by propertyname,recnum 

This works for all the other state agencies to date.

-Rob


************