Thread: debug a mess
I am trying to figure out where the "NOTICE: current transaction is aborted, queries ignored until end of transaction block" comes from in my code. Basically, this is what it does: CString strQuery = "SELECT datname FROM pg_database WHERE name = 'mydatabase'"; if (NULL == (res = PQexec(pgconn, (CSTR) strQuery))) { ErrorQuit(PQerrorMessage(pgconn)); } It is really very simple code, but each time I try to run it, the postgresql backend gives me the "transaction aborted" error message. I checked the mailing list archive, and Tom once explained that the reason for this error message is the backend had quitted for some reason. Is there anyway to see why the backend aborted the transaction?? It could be really helpful. Thanks Wei
On Tue, 2003-02-25 at 12:41, Wei Weng wrote: > I am trying to figure out where the "NOTICE: current transaction is aborted, > queries ignored until end of transaction block" comes from in my code. > Basically, this is what it does: > > CString strQuery > = "SELECT datname FROM pg_database WHERE name = 'mydatabase'"; > > if (NULL == (res = PQexec(pgconn, (CSTR) strQuery))) > { > ErrorQuit(PQerrorMessage(pgconn)); > } > > It is really very simple code, but each time I try to run it, the postgresql > backend gives me the "transaction aborted" error message. I checked the > mailing list archive, and Tom once explained that the reason for this error > message is the backend had quitted for some reason. Is there anyway to see > why the backend aborted the transaction?? It could be really helpful. > It sounds like you've executed a query or command, and have not called PQclear(result) on the results. Whenever I've gottten that error, not clearing the results has always been the root cause. Make sure result is not NULL first, of course... -- Matthew Vanecek perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);' ******************************************************************************** For 93 million miles, there is nothing between the sun and my shadow except me. I'm always getting in the way of something...
On 25/02/03 14:29 -0600, Matthew Vanecek wrote: > It sounds like you've executed a query or command, and have not called > PQclear(result) on the results. Whenever I've gottten that error, not > clearing the results has always been the root cause. Make sure result > is not NULL first, of course... > > The entire code from PQconnect until PQfinish goes like this: pgconn = PQconnect(init_string); res = PQexec(pgconn, my_query); if (res == NULL) quit; switch( PQresultStatus(res) ) { case ... goto _end; case ... goto _end; ... default ... goto_end; } PQclear(res); res = NULL; _end: if (res) PQclear(res); PQfinish(pgconn); This is pretty much the skeleton of the code, as you can see, I treat res pretty carefully, PQclear it whenever necessary. Thanks Wei
On Tue, Feb 25, 2003 at 03:58:20PM -0500, Wei Weng wrote: > > switch( PQresultStatus(res) ) > { > case ... goto _end; > case ... goto _end; > ... > default ... goto _end; > } Why do you call that label "_end"? AFAIK names starting with underscores are reserved for use by the compiler, the standard library, etc. Never start your identifiers and such with underscores. A name like _end in particular could mean something special to the compiler or linker and screw things up that way. Why not call it bailout or something? > This is pretty much the skeleton of the code, as you can see, I treat res > pretty carefully, PQclear it whenever necessary. And what are the queries you issue? Do you "begin" a transaction? Are there any errors before the one you're looking at? Jeroen
Wei Weng <wweng@kencast.com> writes: > The entire code from PQconnect until PQfinish goes like this: That sure looks harmless. I wonder if there is something failing in the PQconnect() step (like the queries that libpq sends on its own)? I'd suggest enabling query logging --- see postgresql.conf, or just doexport PGOPTIONS="--log_statement=1" before starting your program. (Check Admin Guide first, as this parameter got renamed recently.) Then look in the postmaster log to get a better idea of what's really going on. BTW, I'm pretty sure that PQclear, and most other libpq functions, behave sanely when handed a null pointer. So the test in > if (res) > PQclear(res); is unnecessary. regards, tom lane
On 25/02/03 16:27 -0500, Tom Lane wrote: > Wei Weng <wweng@kencast.com> writes: > > The entire code from PQconnect until PQfinish goes like this: > > That sure looks harmless. I wonder if there is something failing in the > PQconnect() step (like the queries that libpq sends on its own)? > > I'd suggest enabling query logging --- see postgresql.conf, or just do > export PGOPTIONS="--log_statement=1" > before starting your program. (Check Admin Guide first, as this > parameter got renamed recently.) Then look in the postmaster log to get > a better idea of what's really going on.<snip> Where is postmaster log? I found a new file in /var/log/pgsql. I put log_connections = yes and syslog = 2 into my postgresql.conf file and expect something to happen. Restarted postgresql service on my redhat 7.3 box and tried a couple of queries with psql utility. Nothing seemed to have been logged in /var/log/pgsql. Under /var/lib/pgsql/data, there isn't any new files appearing. By the way, how do I check to see what kind of options had been used in my postgresql build? Thanks Wei
Wei Weng <wweng@kencast.com> writes: > Where is postmaster log? I found a new file in /var/log/pgsql. I put > log_connections = yes and syslog = 2 into my postgresql.conf file and expect > something to happen. Restarted postgresql service on my redhat 7.3 box and > tried a couple of queries with psql utility. Nothing seemed to have been > logged in /var/log/pgsql. If you want to use syslog then you'd better be sure that syslogd is configured to do something useful with Postgres messages, and not just drop 'em on the floor. If you want to use stderr, look at the start script. Some distros provide Postgres start scripts that route the postmaster's stderr to /dev/null --- either explicitly, or by using the infamous -S switch. regards, tom lane
On 26/02/03 16:34 -0500, Tom Lane wrote: > Wei Weng <wweng@kencast.com> writes: > > Where is postmaster log? I found a new file in /var/log/pgsql. I put > > log_connections = yes and syslog = 2 into my postgresql.conf file and expect > > something to happen. Restarted postgresql service on my redhat 7.3 box and > > tried a couple of queries with psql utility. Nothing seemed to have been > > logged in /var/log/pgsql. > > If you want to use syslog then you'd better be sure that syslogd is > configured to do something useful with Postgres messages, and not just > drop 'em on the floor. > > If you want to use stderr, look at the start script. Some distros > provide Postgres start scripts that route the postmaster's stderr to > /dev/null --- either explicitly, or by using the infamous -S switch. > Tom: I checked pg_config's output, in my redhat build, syslog is already enabled. So that clears some of my doubt. This is how I started my postmaster. su postgres /usr/bin/pg_ctl -D $PGDATA -l /var/log/pgsql -p /usr/bin/postmaster start > /var/log/pgsql 2>&1 I am trying my best to force the postmaster to write to the /var/log/pgsql file. But it didn't seem to work at all. All there in /var/log/pgsql is a oneline "postmaster successfully started". I checked the manpages, and all they say about log is use -l switch (which I used). Can you provide a little more hints? Thanks! Wei
> Tom: > > I checked pg_config's output, in my redhat build, syslog is already enabled. > So that clears some of my doubt. > > This is how I started my postmaster. > > su postgres > > /usr/bin/pg_ctl -D $PGDATA -l /var/log/pgsql -p /usr/bin/postmaster start > > /var/log/pgsql 2>&1 > > I am trying my best to force the postmaster to write to the /var/log/pgsql > file. But it didn't seem to work at all. All there in /var/log/pgsql is a > oneline "postmaster successfully started". I checked the manpages, and all > they say about log is use -l switch (which I used). > > Can you provide a little more hints? > > Thanks! > Actually, never mind that. :) I changed the setting in postgresql.conf to syslog = 1 and everything now spills out of both standard out (which directs to /var/log/pgsql) and syslog (which I still don't know where it is being stored). Wei