select version()
"PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)"
Today I added 2 new fields to a single row table and populated them with values.
I noticed that the values disappeared after a while and could not figure out why.
After they disappeared a couple more times I started investigating.
I have a daemon that runs on my database server using Listen. It calls a database function when it receives the NOTIFY.
One of the things that this function does is it updates a field in the above-mentioned table using the command:
Update tablename set fieldname=now();
If I run the function straight from psql or pgadmin, it works fine.
When I called NOTIFY the values in the new fields disappeared again.
I tested this a number of different ways.
Finally when I stopped the daemon and restarted it, the values stopped disappearing when NOTIFY was called.
After it restarted I changed the value in one of the fields and called NOTIFY and the value remained changed.
I then added another field to the table and gave it a value and ran NOTIFY and it removed the value.
It can't be an issue of transactions, because fields that existed before the daemon was started are not reverted to any prior state.
The table itself isn't referenced in the code here, it is only called in a function.
If anyone can think of an explanation, I would be happy to hear it.
Below is the Daemon program. It is pretty much copied from the example, with very minor modification.
/*
* testlibpq2.c
* Test of the asynchronous notification interface
*
* Start this program, then from psql in another window do
* NOTIFY TBL2;
* Repeat four times to get this program to exit.
*
* Or, if you want to get fancy, try this:
* populate a database with the following commands
* (provided in src/test/examples/testlibpq2.sql):
*
* CREATE TABLE TBL1 (i int4);
*
* CREATE TABLE TBL2 (i int4);
*
* CREATE RULE r1 AS ON INSERT TO TBL1 DO
* (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
*
* and do this four times:
*
* INSERT INTO TBL1 VALUES (10);
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
#include <unistd.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
PGnotify *notify;
int nnotifies;
pid_t pid, sid;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=postgres and using
* environment variables or defaults for all other connection parameters.
*/
pid = fork();
if (pid < 0) {
exit(EXIT_FAILURE);
} else if (pid > 0) {
exit(EXIT_SUCCESS);
}
sid = setsid();
if (sid < 0) {
exit(EXIT_FAILURE);
}
if (argc > 1){
conninfo = strcat(argv[1], " user = myuser");
fprintf(stderr,conninfo);
}
else
conninfo = "dbname = mydb user = myuser" ;
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Issue LISTEN command to enable notifications from the rule's NOTIFY.
*/
res = PQexec(conn, "LISTEN populateallocation");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* should PQclear PGresult whenever it is no longer needed to avoid memory
* leaks
*/
PQclear(res);
res = PQexec(conn, "LISTEN populaterfqrules");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* should PQclear PGresult whenever it is no longer needed to avoid memory
* leaks
*/
PQclear(res);
/* Quit after four notifies are received. */
nnotifies = 0;
while (nnotifies < 4)
{
/*
* Sleep until something happens on the connection. We use select(2)
* to wait for input, but you could also use poll() or similar
* facilities.
*/
int sock;
fd_set input_mask;
sock = PQsocket(conn);
if (sock < 0)
break; /* shouldn't happen */
FD_ZERO(&input_mask);
FD_SET(sock, &input_mask);
if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
{
fprintf(stderr, "select() failed: %s\n", strerror(errno));
exit_nicely(conn);
}
/* Now check for input */
PQconsumeInput(conn);
while ((notify = PQnotifies(conn)) != NULL)
{
fprintf(stderr,
"ASYNC NOTIFY of '%s' received from backend pid %d\n",
notify->relname, notify->be_pid);
if (strcmp(notify->relname,"populateallocation")==0)
res = PQexec(conn, "select populate_allocated_components()");
else
res = PQexec(conn, "select populate_rfqrules()");
PQfreemem(notify);
}
}
fprintf(stderr, "Done.\n");
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
