[GENERAL] could not reattach to shared memory
Hi there, I run a long transaction with a lot of data, and after a while I got the messages: -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] could not reattach to shared memory
Sorry, for the unfinished text, So, I run a long transaction with a lot of data, and after a while I got the messages: NOTICE: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter "max_fsm_relations". LOG: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter "max_fsm_relations". STATEMENT: VACUUM FATAL: could not reattach to shared memory (key=324, addr=021A): 487 And the process stops. Why ? What to do ? TIA, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] out of shared memory - find temporary tables
Hi there, I got "out of shared memory" error. Searching on postgresql forums, I found this it occurs probably because of intensive use of temporary tables in one transaction. I'm locking in pg_locks table, and I found some rows with the following modes: "ShareLock", "AccessExclusiveLock", "ExclusiveLock", "AccessShareLock", and "RowExclusiveLock" with many counts (especially "AccessExclusiveLock" and "AccessShareLock"), but the oid and relname is empty. I suppose there are related to some temporary tables. How can I find what are the storage procedures which create these temporary tables in my code ? TIA, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] repeated log "$libdir/plugins/plugin_debugger.dll"
Hi there, I have "PostgreSQL 8.3.5, compiled by Visual C++ build 1400" installed on Windows XP. The log files contain the following repeated message: db=, user= LOG: loaded library "$libdir/plugins/plugin_debugger.dll" Unfortunately it fills a lot of my log file. Please tell me how to remove it from the log file. TIA, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] remove log header
Hi there, On Windows, I run a script batch file with psql, and I get any log line with the following pattern: psql://:4: NOTICE: I'd like to remove the log header before NOTICE. What should I do ? Thanks, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] on error logs the whole multiline script
Hi, I have "PostgreSQL 8.3.5, compiled by Visual C++ build 1400", and I found when I run a script and an error occurs, all the script content is logged. My previous postgres version "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" logged just the function name stack where the error occured. Is there any configuration setting to disable script content logging on error, and to enable just the error and the function stack ? TIA, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY problem on -- strings
Sorry, my fault that I run the script in the query window of pgAdmin, not in the system console. I check it again in the system console and it works well. Thanks, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hidden errors calling a volatile function inside a stable function
Hi,
I have "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)" on Windows OS , but I experienced the same problem on
"PostgreSQL 8.3.5, compiled by Visual C++ build 1400" too.
I attach the demo database here, to be available to test according with the
following scenario.
I found in a STABLE function, for instance "TEST_0"(), it is not allowed to
use INSERT statement. Trying this will give me the error:
ERROR: INSERT is not allowed in a non-volatile function
Same behavior is for DELETE statement (e.g. "TEST_1"()).
If I set the function to VOLATILE (as "TEST_2"() ), it works very well.
I replace DELETE and INSERT statements with a volatile function call, in
"TEST_3"(), and I call it. It works well too.
Finally I set the function as STABLE, not VOLATILE (see "TEST_4"()).
First I call:
DELETE FROM "A";
Then another call:
SELECT "TEST_4"();
I get no rosen errors, but the results are wrong (see the log results),
because "TEST_4"() doesn't see the changes made by the called function.
I find this behaviour it is very dangerous because it is completely hidden.
What do you say ?
TIA,
Sabin
begin 666 081120_DEMO_01.backup
M4$=$35 !"@`$" $!`0`9`!(`#0`4``H`; ``
M``<```!$14U/7S Q``4X+C(N- `%."XR+C0`#P!'
[EMAIL PROTECTED] ``0```# `" ```$5.0T]$24Y'[EMAIL PROTECTED]/1$E.
M1P`>4T54(&-L:65N=%]E;F-O9&EN9R ]("=55$8X)SL*```!`0``
M``$!`0$%9F%L`0``0U)%051%($953D-424].(")415-47S
B*"D@
M4D5455).4R!I;G1E9V5R"B @("!!4R D) T*0D5'24X-"@E204E312!.3U1)
M0T4@)[EMAIL PROTECTED])SL-"@E204E312!.3U1)0T4@)S$@
M+2 E)[EMAIL PROTECTED]"!%6$E35%,H(%-%3$5#5"!T0`*
M0T].4U1204E.5 !(04Q415(@5$%"[EMAIL PROTECTED],62 B02(*(" @($%$
M1"!#3TY35%)!24Y4(")!7W!K97DB([EMAIL PROTECTED]("@B0V]L7S$B*3L*
M`#8```!!3%1%4B!404),12!/3DQ9('!U8FQI8RXB02(@1%)/4"!#3TY35%)!
M24Y4(")!7W!K97DB.PH!`0`&<'5B;&EC" ```'!Ohttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY problem on -- strings
> Is that the *first* error message you got? > Yes it is. In fact I made a mistake in the first email, so instead: INSERT INTO "A" ( "Col1", "Col2" ) VALUES (2, '-- any text' ); please change with: INSERT INTO "A" ( "Col1", "Col2" ) VALUES (1, '-- any text' ); However I suppose this doesn't change the problem :(. Regards, Sabin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY problem on -- strings
Hi,
I have "PostgreSQL 8.3.5, compiled by Visual C++ build 1400" on Windows OS.
I try to use the COPY command to optimize the backup/restore performance,
but I found a problem. I reproduce it below.
I create a database with a simple table:
CREATE TABLE "A"
(
"Col1" integer NOT NULL,
"Col2" character varying NOT NULL,
CONSTRAINT "A_pkey" PRIMARY KEY ("Col1")
)
WITH (OIDS=FALSE);
ALTER TABLE "A" OWNER TO postgres;
I populate the table with the statement:
INSERT INTO "A" ( "Col1", "Col2" )
VALUES (2, '-- any text' );
I backup the database plain with the command:
pg_dump.exe -U postgres -F p -v -f "backup_plain.sql" "DemoDB"
I create a new database, and I run the script. But it rise me the error:
ERROR: syntax error at or near "1"
LINE 49: 1 -- any text
I look for the error line and I saw how pg_dump created the script
statement:
COPY "A" ("Col1", "Col2") FROM stdin;
1 -- any text
\.
Please let me know if this is a bug on pg_dump, or on the COPY syntax
design, or am I wrong ?
Do you know an workaround to make my backup/restore process available by
COPY, not by INSERT commands ?
TIA,
Sabin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] statements of an unfinished transaction
Hi there, I'd like to find the sessions that provide unclosed transactions (i.e. marked as in transaction). Is any way to find the SQL statements that belong to such a transaction, or the transaction time start, or any other helpful data ? TIA, Sabin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] memory optimization
>> >> So, what is better from the postgres memory point of view: to use >> temporary >> objects, or to use common variables ? > >A temp table might take *slightly* more room than variables... > >> Can you suggest me other point of views to be taken into consideration in >> my >> case ? > >Code maintenance. I can't think of anyway to replace a temp table with >variables that isn't a complete nightmare. With some conversion procedures that is even easiest to do it ;) Regards, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] memory optimization
Hi there, I have a procedure which uses temporary objects (table and sequence). I tried to optimize it, using common variables (array and long varchar) instead. I didn't found any difference in performance, but I'd like to choose the best option from other points of view. One of them is the memory. So, what is better from the postgres memory point of view: to use temporary objects, or to use common variables ? Can you suggest me other point of views to be taken into consideration in my case ? TIA, Sabin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] statistics on CRUD operations
""Simon Riggs"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Mon, 2007-06-18 at 12:35 +0300, Sabin Coanda wrote: > >> Is somewhere a system table providing statistic counters of CRUD >> operations >> against custom databases ? > > pg_stat_user_tables > > http://www.postgresql.org/docs/8.2/static/monitoring-stats.html#MONITORING-STATS-VIEWS > That's exactly what I need, but I found the signification of the columns is not trivial, so I'd appreciate very much some more details about them, please. Regards, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] statistics on CRUD operations
Hi there, Is somewhere a system table providing statistic counters of CRUD operations against custom databases ? TIA, Sabin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
