Re: [BUGS] BUG #2419: could not reattach to shared memory
On Mon, 2006-05-08 at 08:31 -0400, Alvaro Herrera wrote: > > 2006-05-07 23:44:20 10.10.12.100(4018)PANIC: 42501: could not write to log > > file 0, segment 90 at offset 2998272, length 8192: Permission denied This is a pg_xlog error, so it looks like you have a whole-system issue, not just isolated tables. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] BUG #2428: ERROR: out of memory, running INSERT SELECT statement
The following bug has been logged online:
Bug reference: 2428
Logged by: Casey Duncan
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system: Debian Linux (2.6.13.1-20050914 #2 SMP) 2xOpteron 8GB
RAM
Description:ERROR: out of memory, running INSERT SELECT statement
Details:
I filed this a few days back, but I came up with some more detail. I started
with a clean 8.1.3 installation (no databases), imported a production
snapshot and ran part of a large upgrade script on it.
The server has this config:
shared_buffers = 2
max_prepared_transactions = 200
work_mem = 8192 # 8 Mb
maintenance_work_mem = 131072 # 128 Mb
max_fsm_pages = 5
wal_buffers = 64
The part of the db being upgraded has this schema to start with:
-- Begin Schema
SET client_encoding = 'UTF8';
SET default_with_oids = false;
CREATE TABLE ll (
ll_id integer DEFAULT nextval(('ll_id_seq'::text)::regclass) NOT NULL,
username text,
"password" text,
expiration_date timestamp without time zone,
state text NOT NULL,
billing_frequency text,
alert_code text,
auto_renew boolean DEFAULT true NOT NULL,
email_opt_in boolean DEFAULT false NOT NULL,
date_created timestamp without time zone DEFAULT now(),
web_name text,
birth_year integer,
gender text,
zipcode text
);
CREATE SEQUENCE ll_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
CREATE TABLE ll_to_ss (
ll_id integer NOT NULL,
ss_id integer NOT NULL,
time_added timestamp without time zone DEFAULT now()
);
CREATE TABLE ss (
ss_id integer DEFAULT nextval(('ss_id_seq'::text)::regclass) NOT NULL,
name character varying(64) NOT NULL,
creator_id integer NOT NULL,
ll_count integer DEFAULT 0 NOT NULL,
initial_mm_id character varying(20),
CONSTRAINT ss_name CHECK (((name)::text <> ''::text))
);
CREATE SEQUENCE ss_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE ONLY ll
ADD CONSTRAINT ll_pkey PRIMARY KEY (ll_id);
ALTER TABLE ONLY ll_to_ss
ADD CONSTRAINT ll_to_ss_pkey PRIMARY KEY (ll_id, ss_id);
ALTER TABLE ll_to_ss CLUSTER ON ll_to_ss_pkey;
ALTER TABLE ONLY ll
ADD CONSTRAINT ll_username_key UNIQUE (username);
ALTER TABLE ONLY ll
ADD CONSTRAINT ll_web_name_key UNIQUE (web_name);
ALTER TABLE ONLY ss
ADD CONSTRAINT ss_pkey PRIMARY KEY (ss_id);
CREATE INDEX ll_expiration_date_idx ON ll USING btree (expiration_date);
CREATE INDEX ll_to_ss_ss_id_idx ON ll_to_ss USING btree (ss_id);
CREATE INDEX ss_creator_id_initial_mm_id_idx ON ss USING btree (creator_id,
initial_mm_id);
CREATE INDEX ss_ll_count_idx ON ss USING btree (ll_count);
ALTER TABLE ONLY ss
ADD CONSTRAINT "$1" FOREIGN KEY (creator_id) REFERENCES ll(ll_id);
ALTER TABLE ONLY ll_to_ss
ADD CONSTRAINT "$1" FOREIGN KEY (ll_id) REFERENCES ll(ll_id);
ALTER TABLE ONLY ll_to_ss
ADD CONSTRAINT "$2" FOREIGN KEY (ss_id) REFERENCES ss(ss_id) ON DELETE
RESTRICT;
-- End of Schema
Here is the upgrade script that causes the memory error:
BEGIN; --Upgrade script
ALTER TABLE ss RENAME COLUMN creator_id TO ll_id;
DROP INDEX ss_creator_id_initial_mm_id_idx;
CREATE INDEX ss_ll_id_initial_mm_id ON
ss (ll_id, initial_mm_id);
DROP INDEX ss_ll_count_idx;
ALTER TABLE ss ALTER COLUMN ss_id DROP DEFAULT;
ALTER TABLE ss ALTER COLUMN ll_id DROP NOT NULL;
ALTER TABLE ss DROP COLUMN ll_count;
ALTER TABLE ss ADD COLUMN shared_ss_id BIGINT;
ALTER TABLE ss ADD COLUMN time_added TIMESTAMP;
ALTER TABLE ss ADD COLUMN shared_creator_id BIGINT;
ALTER TABLE ss ADD CONSTRAINT ss_shared_chk CHECK
((shared_ss_id != ss_id) AND (shared_creator_id != ll_id));
-- Update ss table in place for "original" sss
UPDATE ss SET time_added = lts.time_added
FROM ll_to_ss AS lts
WHERE ss.ll_id = lts.ll_id;
-- Add content to ss table for shared sss
INSERT INTO ss
(ss_id, name, ll_id, shared_ss_id, time_added,
shared_creator_id)
SELECT nextval('ss_id_seq'), s.name, lts.ll_id, s.ss_id,
lts.time_added, s.ll_id
FROM ss AS s, ll_to_ss AS lts
WHERE lts.ll_id != s.ll_id;
DROP TABLE ll_to_ss CASCADE;
CREATE FUNCTION write_error_trigf() RETURNS trigger AS '
BEGIN
RAISE EXCEPTION ''Writes not allowed to this table on this node'';
END;
' LANGUAGE plpgsql;
END; --Upgrade script
In the database being upgraded, the "ll" table has 8740364 rows, the "ss"
table has 18953787 rows and the "ll_to_ss" has 19233345 rows. The script
runs fine on an empty database. Running the script on the populated database
results in the following error (from the server log):
TopMemoryContext: 61976 total in 6 blocks; 5936 free (11 chunks); 56040
used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328
used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: -1268785152 total in 372 blocks; 12672 free (372
chunks); -1268797824 used
MessageContext: 24576 total in 2 blocks; 1152 free
[BUGS] BUG #2426: perl function that returns setof composite type
The following bug has been logged online:
Bug reference: 2426
Logged by: Chana Slutzkin
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system: FreeBSD 6.1
Description:perl function that returns setof composite type
Details:
The code below works fine on version 8.0.3.
However, on version 8.1.3, the last query returns
the following error:
set-valued function called in context that cannot accept a set
Hence there does not seem to be an way to use
a perl function that returns 'setof composite'
on non-constant input.
--
DROP TABLE num CASCADE;
CREATE TABLE num (n int);
COPY num FROM stdin;
1
2
3
\.
CREATE OR REPLACE FUNCTION sqlget(num) RETURNS SETOF num AS $$
SELECT $1.n
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION perlget(num) RETURNS SETOF num AS $$
return [{n=>$_[0]->{n}}];
$$ LANGUAGE plperl;
SELECT (sqlget(num.*)).* FROM num;
SELECT (perlget(num.*)).* FROM num;
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #2419: could not reattach to shared memory
Hi, Thank you for your replies. I accept that the "Permission denied" problem does suggest that the DB error may be caused by the OS somehow. There is no problem with the permissions/ownership of the files because the Postgres account created and owns and those files; this rules out any sort of security problem. It is possible that the file is inaccessible through some other reason and that Postgres is merely reporting that it can’t access the file and, that it ‘could’ be caused by a permissions problem rather than it ‘is’ a permissions problem. The error log information in this case isn’t really very useful since it doesn’t accurately report the real cause of the error. The OS doesn’t report any other errors and there are no other systems problem or file access problems; the only problem lies within the database. To try and reproduce the problem on another machine, I did a new install of the same version of Postgres (8.1.3) and dump/restored the database onto this new server. So far it has been running with the same load and activity for almost 30 hours and the problem has not surfaced. In theory, Postgres and the database are identical and therefore, the fact that it doesn’t error in the same way does confirm this is an OS problem (assuming the problem doesn’t occur at some point in the future). The two servers are identical hardware and have the same version of OS, Windows Server 2003 SP1. None of this helps me because I still have a production server on which I can’t run the database since I can’t debug the error. Any suggestions? Thank you for your assistance. Andy
[BUGS] BUG #2429: Explain does not report object's schema
The following bug has been logged online: Bug reference: 2429 Logged by: Cristiano da Cunha Duarte Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Debian GNU Linux Description:Explain does not report object's schema Details: 1) PROBLEM: Explain command does not report the schema of objects, so when using objects having the same name but in different schemas, they will apear as being the same object. 2) HOW TO REPRODUCE: EXPLAIN SELECT * FROM schema1.mytable, schema2.mytable WHERE 1=0 3) WHAT IS THE CURRENT BEHAVIOR: QUERY PLAN --- Nested Loop (cost=10.66..500630.90 rows=24422640 width=1498) -> Seq Scan on mytable (cost=0.00..12167.44 rows=407044 width=264) -> Materialize (cost=10.66..11.26 rows=60 width=1234) -> Seq Scan on mytable (cost=0.00..10.60 rows=60 width=1234) (4 records) 3) WHAT SHOULD BE EXPECTED: QUERY PLAN --- Nested Loop (cost=10.66..500630.90 rows=24422640 width=1498) -> Seq Scan on schema2.mytable (cost=0.00..12167.44 rows=407044 width=264) -> Materialize (cost=10.66..11.26 rows=60 width=1234) -> Seq Scan on schema1.mytable (cost=0.00..10.60 rows=60 width=1234) (4 records) 4) ADDITIONAL COMMENTS: I am developing a snapshot project(Pg::snapshots http://cunha17.theicy.net/personal/postgresql/snapshots.en_us.php) for postgresql. It currently has refresh (complete, force, fast), snapshot logs, dblinks, etc. It's 99% complete, everything works fine, except the refresh fast, since I need to discover which objects were involved in a SELECT statement. And that's how I got into this bug. With the current EXPLAIN implementation, I can't tell the difference between the two and thus I can't get the list of involved objects correctly, so I can't get the snapshot log list, and so on. IMHO, the schema name will add correctness to the EXPLAIN command output. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2419: could not reattach to shared memory
"Andy" <[EMAIL PROTECTED]> writes: > To try and reproduce the problem on another machine, I did a new install of > the same version of Postgres (8.1.3) and dump/restored the database onto > this new server. So far it has been running with the same load and activity > for almost 30 hours and the problem has not surfaced. In theory, Postgres > and the database are identical and therefore, the fact that it doesn't error > in the same way does confirm this is an OS problem (assuming the problem > doesn't occur at some point in the future). The two servers are identical > hardware and have the same version of OS, Windows Server 2003 SP1. We've seen reports of intermittent permission failures on Windows being caused by broken antivirus software. What security software have you got on those machines, and does the failure go away if you remove it? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2429: Explain does not report object's schema
"Cristiano da Cunha Duarte" <[EMAIL PROTECTED]> writes: > Explain command does not report the schema of objects, This is intentional. Most error messages don't mention objects' schemas either, as it would usually just be clutter. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2429: Explain does not report object's schema
Hi Tom, Tom Lane wrote: >> Explain command does not report the schema of objects, > > This is intentional. Most error messages don't mention objects' schemas > either, as it would usually just be clutter. Oracle's EXPLAIN PLAN generate lots of information including the operation, search columns, schema(owner) and object name. In PostgreSQL, the error message when you issue a select statement from an unexistent table, reports the schema too: SELECT * FROM public.unexistent; ERROR: relation "public.unexistent" does not exist In this case the schema name is clutter, since we are dealing with only one table, but when you have (or may have) many tables with the same exact name, you must have a way to distinguish one to another. This problem is much more significant with the EXPLAIN command since we are reporting the execution plan of postgresql. It may be difficult with the current output to distinguish between tables with the same name in order to optimize the query. I just think that there should be a way to uniquely identify the target table on the EXPLAIN output, that's why I don't think that a way to fix an ambiguous output is clutter. Regards, Cristiano Duarte ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2428: ERROR: out of memory, running INSERT SELECT statement
"Casey Duncan" <[EMAIL PROTECTED]> writes: > CREATE FUNCTION write_error_trigf() RETURNS trigger AS ' > BEGIN > RAISE EXCEPTION ''Writes not allowed to this table on this node''; > END; > ' LANGUAGE plpgsql; > END; --Upgrade script You did not show how this function is being used, but I'm wondering if it is an AFTER trigger on inserts into ss? If so, the reason for the out-of-memory failure might be accumulation of pending trigger event records. Without wishing to defend our lack of ability to spill trigger events to disk, you probably wouldn't be happy with the performance if it did work :-(. Consider making the trigger BEFORE instead of AFTER, so that there's not a need to remember a ton of pending trigger firings. I don't see any reason why this trigger needs to be AFTER. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
