Re: Is there any problem with pg_notify and memory consumption? - Mailing list pgsql-general
| From | Per-Olov Esgard |
|---|---|
| Subject | Re: Is there any problem with pg_notify and memory consumption? |
| Date | |
| Msg-id | OFBA9EA017.3CD4A6F1-ONC125789C.0057FD88-C125789C.00598B1E@micronic-mydata.com Whole thread Raw |
| In response to | Re: Is there any problem with pg_notify and memory consumption? (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Is there any problem with pg_notify and memory consumption?
(Merlin Moncure <mmoncure@gmail.com>)
Re: Is there any problem with pg_notify and memory consumption? (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
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@sss.pgh.pa.us>
To: Per-Olov Esgard <Per-Olov.Esgard@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.Esgard@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.
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@sss.pgh.pa.us>
To: Per-Olov Esgard <Per-Olov.Esgard@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.Esgard@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.
pgsql-general by date: