Re: [BUGS] BUG #5767: Memory Leak
On Thu, Nov 25, 2010 at 05:28, Darryl Pye wrote: > > The following bug has been logged online: > > Bug reference: 5767 > Logged by: Darryl Pye > Email address: darryl...@hotmail.com > PostgreSQL version: 9.01 > Operating system: Windows Server 2008 > Description: Memory Leak > Details: > > A single postgres process appears to be constantly growing in memory usage > until it reaches 100% of memory on the machine. > > I have rebooted the server and the problem goes away but then a single > postgres.exe process continues to eat memory until it uses all available > ram then i have to restart again. > > Machine has 4 gig of ram available. > I am able to watch the process grow constantly at approximately 8kb every > second. > > Until it reaches 4gig. > > I have made the following configuration changes from default > shared_buffers = 1GB > wal_buffers = 1MB > effective_cache_size = 2GB What are your work_mem and maintenance_work_mem settings? And which of the postgres processes is it, and what's it doing? You can use Process Explorer to look at the process, and find a handle called something starting with "pgident" - that one will contain a description of the process, just like the title does on Unix. Or you can look up the process id in the pg_stat_activity view to identify it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5770: Foreign key violation after insert
The following bug has been logged online:
Bug reference: 5770
Logged by: Martin Edlman
Email address: edl...@fortech.cz
PostgreSQL version: 9.0.1
Operating system: Scientific Linux 5.5 (RHEL)
Description:Foreign key violation after insert
Details:
Hello,
I have two tables with RI/FK. There is a AFTER INSERT trigger on a master
table (mail_account) which inserts a record to a slave table (amavis_user).
But I get an error message
ERROR: insert or update on table "amavis_user" violates foreign key
constraint "amavis_user_email_fkey"
DETAIL: Key (email)=('t...@mail.com') is not present in table
"mail_account".
I encountered this problem during migration of the database from PgSQL 8.4
(where it works) to PgSQL 9.0.1.
I tried to set the FK constraint DEFERRABLE INITIALLY DEFERRED, I tried to
CREATE CONSTRAINT TRIGGER ... DEFERRABLE INITIALLY DEFERRED, I tried to SET
CONSTRAINTS tmp.amavis_user_email_fkey DEFERRED inside the trigger function
... all combinations - none of these helped.
Is it a bug or am I doing something wrong? It worked in 8.4 as I wrote.
The database migration is stuck on this. Please give me a hint or advice.
Regards, Martin E.
Here are the tables and trigger definitions:
-- trigger function
CREATE OR REPLACE FUNCTION tmp.mail_account_to_amavis_user() RETURNS trigger
AS
$BODY$
DECLARE
prio INTEGER;
BEGIN
IF NEW.username = 'alias' THEN
prio := 3;
ELSE
prio := 6;
END IF;
RAISE NOTICE 'insert into tmp.amavis_user(id, email, priority,
policy_id)
values (%, %, %, 1)',
NEW.id, NEW.email, prio;
SET CONSTRAINTS tmp.amavis_user_email_fkey DEFERRED;
INSERT INTO tmp.amavis_user (id, email, priority, policy_id)
VALUES (NEW.id, quote_literal(NEW.email), prio, 1);
RETURN NEW;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION tmp.mail_account_to_amavis_user() OWNER TO import;
-- mail account table
CREATE TABLE tmp.mail_account
(
id serial NOT NULL,
username character varying(50) NOT NULL,
"password" character varying(50) NOT NULL,
email character varying(255),
uid integer DEFAULT 8,
gid integer DEFAULT 11,
home character varying(100),
CONSTRAINT mail_account_pkey PRIMARY KEY (id),
CONSTRAINT mail_account_email UNIQUE (email)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tmp.mail_account OWNER TO import;
-- trigger to insert a record to amavis_user
CREATE CONSTRAINT TRIGGER amavis_user
AFTER INSERT
ON tmp.mail_account
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE tmp.mail_account_to_amavis_user();
-- table amavis user
CREATE TABLE tmp.amavis_user
(
id serial NOT NULL,
priority integer NOT NULL DEFAULT 7,
policy_id integer,
email character varying(255) NOT NULL,
CONSTRAINT amavis_user_pkey PRIMARY KEY (id),
CONSTRAINT amavis_user_email_fkey FOREIGN KEY (email)
REFERENCES tmp.mail_account (email) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);
-- insert data to mail_account
insert into tmp.mail_account(username,password,email) values
('test','pwd','t...@mail.com')
-- output
-- NOTICE: insert into tmp.amavis_user(id, email, priority, policy_id)
values (15, t...@mail.com, 6, 1)
-- ERROR: insert or update on table "amavis_user" violates foreign key
constraint "amavis_user_email_fkey"
-- DETAIL: Key (email)=('t...@mail.com') is not present in table
"mail_account".
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5768: Inefficiency of large offsets should be mentioned on SELECT documentation page
The following bug has been logged online: Bug reference: 5768 Logged by: Pavel Arnost Email address: pavel.arn...@loutka.cz PostgreSQL version: 9.01 Operating system: N/A Description:Inefficiency of large offsets should be mentioned on SELECT documentation page Details: Inefficiency of large offsets should be mentioned on SELECT documentation page - now it's only on "LIMIT and OFFSET" page. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5769: Problem with SPI_getvalue
The following bug has been logged online: Bug reference: 5769 Logged by: Helmar Spangenberg Email address: hspangenb...@frey.de PostgreSQL version: 9.0.1 Operating system: Linux Description:Problem with SPI_getvalue Details: Referring to the manual, I can pfree the result returned by SPI_getvalue. However the backend crashes as soon as I use pfree. No example uses pfree. Could it be possible, there is a bug in the documentation? Thanks, Helmar -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5769: Problem with SPI_getvalue
On 25.11.2010 13:45, Helmar Spangenberg wrote: Bug reference: 5769 Logged by: Helmar Spangenberg Email address: hspangenb...@frey.de PostgreSQL version: 9.0.1 Operating system: Linux Description:Problem with SPI_getvalue Details: Referring to the manual, I can pfree the result returned by SPI_getvalue. However the backend crashes as soon as I use pfree. No example uses pfree. Could it be possible, there is a bug in the documentation? The return value of SPI_getvalue is certainly palloc()'d, so pfree()ing it should not crash. Perhaps you are trying to pfree() it after the whole memory context has already been destroyed. Or perhaps SPI_getvalue returned a NULL and you're trying to pfree() that. Can't say without more details. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5769: Problem with SPI_getvalue
You are right. In my case I obviously had corrupted the heap somewhere else - now it works as expected. Thanks for your quick answer, Helmar Am Donnerstag, 25. November 2010 16:11:03 schrieb Heikki Linnakangas: > On 25.11.2010 13:45, Helmar Spangenberg wrote: > > Bug reference: 5769 > > Logged by: Helmar Spangenberg > > Email address: hspangenb...@frey.de > > PostgreSQL version: 9.0.1 > > Operating system: Linux > > Description:Problem with SPI_getvalue > > Details: > > > > Referring to the manual, I can pfree the result returned by SPI_getvalue. > > However the backend crashes as soon as I use pfree. > > > > No example uses pfree. Could it be possible, there is a bug in the > > documentation? > > The return value of SPI_getvalue is certainly palloc()'d, so pfree()ing > it should not crash. Perhaps you are trying to pfree() it after the > whole memory context has already been destroyed. Or perhaps SPI_getvalue > returned a NULL and you're trying to pfree() that. Can't say without > more details. > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5769: Problem with SPI_getvalue
"Helmar Spangenberg" writes: > Referring to the manual, I can pfree the result returned by SPI_getvalue. Well, the result is the output of a datatype output function. I think that's palloc'd by all standard datatypes, but maybe you're working with a custom datatype that tries to return a constant string? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5770: Foreign key violation after insert
"Martin Edlman" writes: > I encountered this problem during migration of the database from PgSQL 8.4 > (where it works) to PgSQL 9.0.1. For me, this example fails on both 8.4 and 9.0. It works on both after removing the ill-considered quote_literal call here: > INSERT INTO tmp.amavis_user (id, email, priority, policy_id) > VALUES (NEW.id, quote_literal(NEW.email), prio, 1); Perhaps you tried to migrate away from using EXECUTE at the same time you were converting to 9.0? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5767: Memory Leak
On 11/25/2010 12:28 PM, Darryl Pye wrote: > PostgreSQL version: 9.01 32-bit or 64-bit ? A single postgres process appears to be constantly growing in memory usage until it reaches 100% of memory on the machine. How are you watching it? Task manager? You need to use something that can differentiate between the shared memory block and the per-task private memory. Process Explorer can do that, IIRC. I have rebooted the server and the problem goes away but then a single postgres.exe process continues to eat memory until it uses all available ram then i have to restart again. Are you able to identify which postgres process it is? Is it shown in "SELECT * from pg_stat_activity;" ? Is it one of the postgres processes that appears when PostgreSQL is first started, before any connections are made to the database server? Are you sure you're not looking at totals for all postgres.exe instances? I have made the following configuration changes from default shared_buffers = 1GB wal_buffers = 1MB effective_cache_size = 2GB With those settings, you should expect PostgreSQL to appear to use a bit over 1GB of RAM per postgres.exe, though the vast majority of that 1GB should be shared between all the postgres.exe processes so the total memory of all postgres.exe instances will appear to vastly exceed your machine's RAM. That's just because of the bad accounting for shared memory in most OSes, including windows, and doesn't affect how it runs. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5771: C:\Program Files\PostgreSQL\8.3\Data is not accessible.
The following bug has been logged online: Bug reference: 5771 Logged by: Shafqat Ali Email address: shaf...@k5.com.au PostgreSQL version: 8.3 Operating system: XP Professional SP3 Description:C:\Program Files\PostgreSQL\8.3\Data is not accessible. Details: After installation of PostgrSQL 8.3 was never be able to run Server and C:\Program Files\PostgreSQL\8.3\Data is also not accessible and can not even proper uninstall or re-install Server. I have moved my this hard disk to other machine but still was unable to go inside of (D:\Program Files\PostgreSQL\8.3\Data in other machine) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
