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

[Per-Olov Esgard <Per-Olov.Esgard@xxxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx>
To:      
 Per-Olov Esgard <Per-Olov.Esgard@xxxxxxxxxxxxxxxxxxx>
Cc:      
 pgsql-general@xxxxxxxxxxxxxx
Date:      
 05/26/2011 03:39 PM
Subject:    
   Re: 
Is there any problem with pg_notify and memory consumption?

---

Per-Olov Esgard <Per-Olov.Esgard@xxxxxxxxxxxxxxxxxxx>
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.