Subject: queries per transaction

From: [email-masked] (Banco de Dados Ambiental)
Date: Tue, 26 May 92 17:40:12 -0700

Hi,

I have some embeded queries envolving appends, replaces and retrieves to three t
   ables in a single transaction. Something like:

...

begin
Postgres Returns : CBEGIN

replace Category(label = "Hidrografia", model = 3, contents = 4, components = 0)
    where Category.id = 1
Postgres Returns : CREPLACE

retrieve portal geoclass(GeoClass.classid) where GeoClass.catid = 1 sort by clas
   sid
Postgres Returns : CRETRIEVE

fetch all in geoclass
Postgres Returns : Pgeoclass

close geoclass
Postgres Returns : CCLOSE

replace GeoClass (label = "barragens", model = 3)where GeoClass.catid = 1 and Ge
   oClass.classid = 5
Postgres Returns : CREPLACE

replace ThematicClass(red = 70, green = 60, blue = 0, style = 0, width = 1, fill
    = 1, iconname = "diag6")where ThematicClass.catid = 1 and ThematicClass.clas
   sid = 5
Postgres Returns : CREPLACE

replace GeoClass (label = "lagos", model = 3)where GeoClass.catid = 1 and GeoCla
   ss.classid = 4
Postgres Returns : CREPLACE

append ThematicClass(red = 0, green = 200, blue = 0, style = 0, width = 1, fill
   = 1, iconname = "diag6")where ThematicClass.catid = 1 and ThematicClass.class
   id = 4
Postgres Returns : CAPPEND

replace GeoClass (label = "rios-principais", model = 3)where GeoClass.catid = 1
   and GeoClass.classid = 1
Postgres Returns : CREPLACE

replace ThematicClass(red = 255, green = 0, blue = 0, style = 0, width = 1, fill
    = 1, iconname = "diag6")where ThematicClass.catid = 1 and ThematicClass.clas
   sid = 1
Postgres Returns : CREPLACE

replace GeoClass (label = "rios-secundarios", model = 3)where GeoClass.catid = 1
    and GeoClass.classid = 2
Postgres Returns : CREPLACE

replace ThematicClass(red = 0, green = 255, blue = 0, style = 0, width = 1, fill
    = 1, iconname = "diag6")where ThematicClass.catid = 1 and ThematicClass.clas
   sid = 2
Postgres Returns : CREPLACE

replace GeoClass (label = "rios-terciarios", model = 3)where GeoClass.catid = 1
   and GeoClass.classid = 3
Postgres Returns : CREPLACE

replace ThematicClass(red = 0, green = 0, blue = 255, style = 0, width = 1, fill
    = 1, iconname = "diag6")where ThematicClass.catid = 1 and ThematicClass.clas
   sid = 3
Postgres Returns : CREPLACE

end
Postgres Returns : CEND
...

But sometimes I get messages like that:

...
replace GeoClass (label = "0-2%", model = 3)where GeoClass.catid = 5 and GeoClas
   s.classid = 1Error: No response from the backend, exiting...
...

Is there any limitation to the number of queries or tables per transaction ?
Is there something I could do related to postgres to gain more stability ?
I am not a unix expert and sometimes is difficult to decide if the error comes f
   rom SUNOS or POSTGRES...

Thanks for any suggestions.

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 28 May 92 09:20:32 PDT

you write:

> replace GeoClass (label = "0-2%", model = 3)where GeoClass.catid = 5 and GeoC
 +las
> I am not a unix expert and sometimes is difficult to decide if the error come
 +s f

>Error: No response from the backend, exiting...

This usually indicates a backend crash.  Check for a core dump in
$POSTGRESHOME/data/base/<dbname> and send us a bug report with a stack trace
(using your favorite debugger as described in the documentation).

The hard wired limit in v3.1 for the number of commands per transaction is
256.  Version 4 will extend this limit to 65535, and it will be more vocal
when you overstep the bounds.

If you are ever in doubt as to whether a problem arises from postgres or
from the underlying os, it is probably a safe bet to assume that postgres is
the culprit.

Jeff Meredith
[email-masked]