What do I wrong?

Dear All,

There is a big one for the weekend to think on it... ;-)

I have two problems I can't solve since a week now.
The config is Debian linux 2.1.121, Postgres 6.4.2, Apache.
There is a very simple application running on it with small tables (a couple
of MB altogether), communicating via HTTP with libpq calls.
(There is already a similar app. working perfectly since a year now on this
config. There are no differences in the settings except this one is on port
5431)

Now there are two strange things:

1: When trying to
 BEGIN; CREATE TABLE ....;  CREATE TABLE ....; CREATE TABLE ....; ... END;
 there are two cases that can happen: (no error messages, no stuck
processes, the most verbosest log files are the same except PID-s)
 a: the tables (28 altogether + indices) are created properly (rare)
 b: although the files for the tables/indices are created in the
data/base/dbname directory, they are not appearing in the database.
  When try to create table manually - error msg because the file is exists
with length 0. I must dump, destroydb, createdb, psql -d to work again.
  Another strange thing, that right after it there is an INSERT for two
tables, and that two table "geting exist" but the others don't, not to
mention that the INSERT-s won't get into the tables, although there is no
error message.
  After it there is a Table does not exist error message for INSERT.
  If I SELECT * , or INSERT for more tables they usually(!) shows up but the
others don't.
  If right after the CREATE-s I post a VACUUM; all the tables are created
properly, but the INSERT-s still won't get into the tables.

 Can it be a faulty template database? Or C prog? (I went through the code
but it seems ok. (that's so simple, can't be wrong...)

2: When I try to VACUUM; while an INSERT or UPDATE is running, I get a
couple of error messages (like discussed earlier in pgsql-general), and
these processes gets stuck.
Should I try to upgrade Postgres?
Or should I run VACUUM another way?

Tried (and tired :-(
BEGIN; VACUUM; END;
lock table within that transaction
only VACUUM one table at a time,
BEGIN; LOCK table; VACUUM table; END; - just the same

both of them resulting in stucked processes. (then I have to DROP index,
restart postmaster...)

How should I use VACUUM properly?

Or the two problem has a common root?



Any idea, suggestions?


Thanks a lot,

László Schulteisz
system engineer

Ps.
The libpq calls are following in this order:

 res=PQexec(conn, 'BEGIN;');
 PQclear (res);
 res=PQexec(conn, 'do something' );
 PQclear (res);
 res=PQexec(conn,'END;');
 PQclear (res);

Is it OK?

If there is something I don't want to process anymore whithin it, I simply
closes the connection. Or must I ROLLBACK before it? (I think no, because
the log file shows AbortTransaction in those cases...).

Ps2. I promise I check the C for the 4th time, also... ;-)