Re: Is there any problem with pg_notify and memory consumption?

This is a silly and simple example but it works. The size of the payload
is approximately the same as the one in my real system.

It is easy to see the difference when using/not using the notify by just
comment out the pg_notify call below.

The client code is a small perl program which goes on forever and just
updates a property in one row of the table.

Regards Per-Olov

Server definitions:

------------------- SQL -----------------------

CREATE TABLE mynames
(
"name" character varying(35),
"num" BIGINT DEFAULT -9223372036854775808 ,
CONSTRAINT myname_exists PRIMARY KEY (name)
);
ALTER TABLE mynames OWNER TO postgres;

CREATE OR REPLACE FUNCTION myinsert(_name character varying(35))
RETURNS void AS
$BODY$
BEGIN
INSERT INTO mynames(name) VALUES (_name);
PERFORM pg_notify('insert', _name);
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION myupdate(_name character varying(35))
RETURNS void AS
$BODY$
BEGIN
UPDATE mynames
SET num = num + 1 WHERE name = _name;
PERFORM pg_notify('update', _name);
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER

------------------- END SQL -----------------------

Client code in perl:

------------------- PERL -----------------------

#!/usr/bin/perl -w

use DBI ;
use strict ;

$| = 1 ; # turn off output buffering

###
### Update user, password and host to your preferences
###
my $handle ;
my $database="test" ;
my $user="donald" ;
my $password="duck" ;
my $host="mickey";

###
### Connect to database
###
$handle = DBI->connect("dbi:Pg:database=$database;host=$host",
$user,
$password) or do die $DBI::errstr ;

###
### insertName
###
sub insertName($ ) {
my $name = shift ;
my $sth = $handle->prepare("SELECT myinsert('$name')") ;
$sth->execute();
}

###
### updateName
###
sub updateName($ ) {
my $name = shift ;
my $sth = $handle->prepare("SELECT myupdate('$name')") ;
$sth->execute();
}

print "Testing notify memory consumption..." ;

$handle->do("DELETE FROM mynames") ;

my $count = 1;
&insertName("Donald Duck");
while ($count == 1) {
&updateName("Donald Duck");
}
$handle->disconnect() ;
print "Done!\n" ;
exit 0 ;

------------------- END PERL -----------------------

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Per-Olov Esgard <Per-Olov(dot)Esgard(at)micronic-mydata(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Date: 05/26/2011 03:39 PM
Subject: Re: [GENERAL] Is there any problem with pg_notify and
memory consumption?

Per-Olov Esgard <Per-Olov(dot)Esgard(at)micronic-mydata(dot)com> writes:
> In my environment which is linux on the server side and both windows
and
> linux on the client side I have noticed that the introduction of
pg_notify
> (with payload) makes the size of the postgres processes on the server
side
> increase much more than before I used the notifiy calls.

If you were to show a self-contained test case, it might be possible to
investigate this report. As-is, it's pretty content free :-(

regards, tom lane

The information contained in this communication and any attachments may be
confidential and privileged, and is for the sole use of the intended
recipient(s). If you are not the intended recipient, you are hereby
formally notified that any unauthorized review, use, disclosure or
distribution of this message is prohibited. Please notify the sender
immediately by replying to this message and destroy all copies of this
message and any attachments. Micronic Mydata is neither liable for the
proper and complete transmission of the information contained in this
communication, nor for any delay in its receipt.