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

[Per-Olov Esgard]

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 <t...@sss.pgh.pa.us>
To:     Per-Olov Esgard <per-olov.esg...@micronic-mydata.com>
Cc:     pgsql-general@postgresql.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.esg...@micronic-mydata.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.