[GENERAL] Some Autovacuum Questions

Hi! Some of our clients databases are performing less good after a while. We are using autovacuum to vacuuming and analyzing the tables. After some analyzes by my own it looks like that the tables or table indexes are not analyzed or vacuumed fully or correctly. A count(*) query takes multiple t

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

On Nov 27, 2007 4:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > You didn't happen to note what 9293 was doing did you? It's living > fairly dangerously in any case by trying to acquire exclusive lock > when it already holds a bunch of other lower-level locks; that's a > recipe for deadlock if I eve

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

wrote: > Alvaro Herrera wrote: > > Thomas Chille wrote: > > > On Nov 27, 2007 3:14 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > hat are the column headings? I find this difficult to read. > > > > > > > > Please post the whole of pg_locks

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

On Nov 27, 2007 3:14 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: hat are the column headings? I find this difficult to read. > > Please post the whole of pg_locks. I may be missing something but I > think we're missing part of the picture here. Autovacuum does not seem > to be locking on anyth

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

On Nov 24, 2007 6:20 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > What other indexes does that table have? > > regards, tom lane > Hi, last night it happend again. In the log-snippet u can see all indexes of this table: [9293 / 2007-11-26 21:46:28 CET]CONTEXT: SQL statement

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

i have to wait till monday, then i can provide these lines. thanks, thomas On Nov 23, 2007 1:32 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Thomas Chille escribió: > > > This are the last log entires for these both processes. Over 9 hours > > later, i can see them

[GENERAL] autovacuum process blocks without reporting a deadlock

Hi anybody, I step in just one of our identically customer databases in a kind of a deadlock with Autovacuum involved. One Autovacuum process stuck in the middle of the night and it seemed that it compete with another Select process for an index: [14398 / 2007-11-21 00:52:04 CET]CONTEXT: SQL st

Re: [GENERAL] simple query terminated by signal 11

Thanks for your Tipps! Since it is repeatable in your machine, you can compile a new postgres version with "--enable-cassert" (enable assertions in code) and "--enable-debug" (enable gcc debug support) configuration. Then run it on your data and "bt" the core dump. I try to found out the reas

Re: [GENERAL] simple query terminated by signal 11

, is that i can reproduce the error everytime with the same query. thanks in advonce On 6/20/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote: ""Thomas Chille"" <[EMAIL PROTECTED]> wrote > Hi List, > > i run in to an error while dumping a db. > > after

[GENERAL] simple query terminated by signal 11

Hi List, i run in to an error while dumping a db. after investigating it, i found a possible corrupted table. but i am not sure. and i dont know how i can repair it? could it be a harddrive error? Here are the logs: # all fine: SELECT * FROM hst_sales_report WHERE id = 5078866 [6208 / 2006-06

[GENERAL] initskript after db start; locks on transactions

Hi! I have have two questions: 1. What is the best approach to trigger a service script wich will clean up something in the db after every db (re-)start? Has PG its own mechanism for such things or have i to use my /etc/init.d/postgresql script? 2. Sometime i read something about locks on transa

Re: [GENERAL] 7.3.4 dump/restore error

i forgot to attach the error messages. but now! t On 7/16/05, Thomas Chille <[EMAIL PROTECTED]> wrote: > Hi! > > I dumped from a 7.3.4 server on debian with kernel 2.6 using this command: > > pg_dump -F c -Z 9 -f backoffice.dmp.c backoffice > > > I tried t

[GENERAL] 7.3.4 dump/restore error

Hi! I dumped from a 7.3.4 server on debian with kernel 2.6 using this command: pg_dump -F c -Z 9 -f backoffice.dmp.c backoffice I tried to restore on a similiar system using: pg_restore -vd backoffice backoffice.dmp.c and stepped into an error: ERROR: copy: line 270892, Missing data for c

[GENERAL] solaris 10 vs Linux Debian on SUN Fire v40z

Hi, we are planning to use Postgresql 8.0 on a SUN Fire v40z Server with 4 SingleCore Opterons. It will be later replaced by an DualCore Opterons. Would you prefer using Solaris or Linux? Thanks in Advance, Thomas ---(end of broadcast)--- TIP 2: y

[GENERAL] Trigger Commandline Command from Postgresql

Hi List! What ist the best and easiest way to trigger a commandline command out from the database? We want to start a printjob. I think it could work with pl/tclu but i am not familar with that language. Thanks in Advance, Thomas ---(end of broadcast)

Re: [GENERAL] difficult JOIN

Hi Jim, thanks for your answer! > Hrm. So for a given tour, employee, you want to pair the first record in > plan with the first record in work, and the second record in plan with > the second record in work? Yes you understand me well, thats what i'm trying to achieve. > Doing that will be pre

[GENERAL] difficult JOIN

Hi, i have the following SQL-Problem: We are using 2 tables. The first, called plan, is holding planned working times for employees per tour: plan.id_tour plan.id_employee plan.begin_time plan.end_time The second table 'work' stores the actual worked times for employees per tour: work.id_tour wor

[GENERAL] difficult JOIN

Hi, i have the following SQL-Problem: We are using 2 tables. The first, called plan, is holding planned working times for employees per tour: plan.id_tour plan.id_employee plan.begin_time plan.end_time The second table 'work' stores the actual worked times for employees per tour: work.id_tour work

[GENERAL] speaks psql unicode?

Hi, if i try to import data via SQL-Inserts (exported with pgManager) out from an utf-8 file i get always parse errors in the first line. After switching to ascii and using of SET client_encoding TO 'latin1' i can import all lines, but some unicode-characters are, like expected, damaged. Now my

[GENERAL] Are subselects treated as atomic single commands?

Hi, the docu about the Read Committed Transaction Isolation Level pointed out: "... The point at issue here is whether or not within a single command we see an absolutely consistent view of the database.". Because i dont want to use the Serializable Transaction Isolation Level or table locks if i

[GENERAL] Are subselects treated as atomic single commands?

Hi, the docu about the Read Committed Transaction Isolation Level pointed out: "... The point at issue here is whether or not within a single command we see an absolutely consistent view of the database.". Because i dont want to use the Serializable Transaction Isolation Level or table locks if

Re: [GENERAL] Drop Column with Foreign Key Problem

Hi Tom! Thanx again for your fast help. And its helps indeed. You pointed out the problem correct! I could delete these triggers after scanning the system-tables by hand. Unfortunately this can happen in the futrue again and thatswhy i tried applying the adddepend script on a fresh restored db

[GENERAL] Drop Column with Foreign Key Problem

Hello! I have a Problem. A table with a ForeignKeyRef exits. The Name of the field with this FK-Constraint is 'id_employee' because it is referencing the field 'id_employee' in 'public.sd_employee'. I drop this column of the table with cascade in a script: ALTER TABLE public.sd_messaging_parti

Re: [GENERAL] PLpgSQL-Problem

Hello Tom, thank u for your fast reply! Now I understand that i can not split easily my scripts into logical units but what i not understand is the documention not correct or is my problem not similiar to the example in point 37.4.3 on http://www.postgresql.org/docs/7.4/static/plpgsql-declarati