Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux