Re: [BUGS] BUG #2104: pg_xlog/ trace files not reclaimed by server
Hi Tom, Unfortunately, I had to rebuild the database pretty quickly to get the app it supports back up, and I wiped out the log files. The postmaster would dump core on startup - so I just wiped the database, and recreated the schema (my app can rebuild its state). I didn't save the core file either - but it wasn't not very informative - something like: abort (); failedToStartupChildProcesses (); ... I'll save the logs and core if this happens again. I may have misdiagnosed the cause of the problem, but the pg_xlog/ directory does fill up with files until the disk is full - 8 GB worth - when it enters this unstable state. I thought it might be related to exceeding 2*checkpoint_segments + 1 because of what I read here: http://www.postgresql.org/docs/8.1/static/wal-configuration.html - There will be at least one WAL segment file, and will normally not be more than 2 * checkpoint_segments + 1 files. Each segment file is normally 16 MB (though this size can be altered when building the server). You can use this to estimate space requirements for WAL. Ordinarily, when old log segment files are no longer needed, they are recycled (renamed to become the next segments in the numbered sequence). If, due to a short-term peak of log output rate, there are more than 2 * checkpoint_segments + 1 segment files, the unneeded segment files will be deleted instead of recycled until the system gets back under this limit. - The database ran fine for the last 10 days - seemed to recycle the trace files under pg_xlog/ fine. I was monitoring the pg_xlog/ directory, because I knew that was what grew out of control the last crash. I noticed at the end of the day yesterday that we had grown up to 25 trace files, and I knew my checkpoint-segment was set to '12'. Anyway - I could be completely off here. I uped my checkpoint_segment setting to 30 (still have a 5 minute timeout), and trace files are recycling ok: $ ls -lrt pg_xlog/ total 278872 drwx-- 2 monitor user 4096 Dec 9 04:59 archive_status -rw--- 1 monitor user 16777216 Dec 9 11:00 0001000100C9 -rw--- 1 monitor user 16777216 Dec 9 11:00 0001000100C8 -rw--- 1 monitor user 16777216 Dec 9 11:02 0001000100C7 -rw--- 1 monitor user 16777216 Dec 9 11:05 0001000100B9 -rw--- 1 monitor user 16777216 Dec 9 11:05 0001000100BA -rw--- 1 monitor user 16777216 Dec 9 11:06 0001000100BB -rw--- 1 monitor user 16777216 Dec 9 11:07 0001000100BC -rw--- 1 monitor user 16777216 Dec 9 11:07 0001000100BD -rw--- 1 monitor user 16777216 Dec 9 11:08 0001000100BE -rw--- 1 monitor user 16777216 Dec 9 11:09 0001000100BF -rw--- 1 monitor user 16777216 Dec 9 11:09 0001000100C0 -rw--- 1 monitor user 16777216 Dec 9 11:10 0001000100C1 -rw--- 1 monitor user 16777216 Dec 9 11:11 0001000100C2 -rw--- 1 monitor user 16777216 Dec 9 11:11 0001000100C3 -rw--- 1 monitor user 16777216 Dec 9 11:12 0001000100C4 -rw--- 1 monitor user 16777216 Dec 9 11:13 0001000100C5 -rw--- 1 monitor user 16777216 Dec 9 11:13 0001000100C6 We'll see how that goes. I'll let you know if I get another crash. Cheers, Reuben Tom Lane wrote: "Reuben Pasquini" <[EMAIL PROTECTED]> writes: It appears that when my 8.1 database is forced to generate more than 2*checkpoint_segments + 1 trace files under pg_xlog/, that the database becomes confused and stops recycling the trace files. That's fairly hard to believe, especially since you haven't presented any actual evidence of it. What might have happened is that checkpoints were failing for some reason and so recycling of WAL segments couldn't be performed. Was there anything in the postmaster log about write failures? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault
The following bug has been logged online: Bug reference: 2106 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.0, 8.2devel Operating system: CentOS 4.2 with 2.6.13.4-SMP kernel Description:EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault Details: Hello, while testing the 8.1.0 version of PostgreSQL (we've been using version 7.4.8 for a long time), the EXPLAIN ANALYZE causes a single backend server process to segfault after issuing the following query: SELECT obch_vyrobek.id_vyrobku AS id_vyrobku, obch_vyrobek.id_vyrobce AS id_vyrobce, obch_vyrobce.nazev AS nazev_vyrobce, CASE obch_vyrobek.nazev ILIKE '%aloe%' WHEN true THEN 1 ELSE 0 END AS match_nazev_vyrobku FROM obch_vyrobek LEFT JOIN obch_vyrobce USING (id_vyrobce) WHERE (obch_vyrobek.aktivni AND ((obch_vyrobek.sukl IS NULL AND obch_vyrobek.v_katalogu) OR (obch_vyrobek.sukl IS NOT NULL) OR (obch_vyrobek.odhadovana_dostupnost IS NOT NULL))) AND (obch_vyrobek.nazev ILIKE '%aloe%' OR obch_vyrobek.popis ILIKE '%aloe%' OR coalesce(obch_vyrobek.popis_od_vyrobce,obch_vyrobek.popis) ILIKE '%aloe%' OR obch_vyrobce.nazev ILIKE '%aloe%') ORDER BY otc DESC, typ DESC, odhadovana_dostupnost DESC, obr_m DESC, match_nazev_vyrobku DESC, obch_vyrobek.nazev; The same happens with version 8.2devel. There are no segfaults with such EXPLAIN ANALYZE followed by abovementioned query when using version 7.4.8, even 8.0.4 is ok. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2105: ERROR: relation "comoma" does not exist
The following bug has been logged online: Bug reference: 2105 Logged by: alejandro ramirez Email address: [EMAIL PROTECTED] PostgreSQL version: PostgreSQL 8.1. Operating system: windows 2000 profesional Description:ERROR: relation "comoma" does not exist Details: Hi there! i a new user of PostgreSQL, i develop on severals languajes like as J2SDK/J2EE/SERVLETS/VB/FOXPRO...etc and i use a several RDBM. I try to use PostgreSQL to implement into 2 aplication's (one on the internet and the other in aq model two fase's like a client-server), but i meet a problem when i use a table for a simple "SELECT" or "DELET FROM myTable" the result for this is: "ERROR: relation "comoma" does not exist" (comoma it's a table) but this table exists on my database named "AMS" i looking for a this error on the net, but i don't found any answer... please, do you know what happened with this! for you help, than's a lote! Regards from mexico ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault
"" <[EMAIL PROTECTED]> writes: > Description:EXPLAIN ANALYZE with SELECT query causes a single > backend server process to segfault Could you provide a self-contained test case, please? Guessing at your table schemas does not seem a productive use of time. See http://www.postgresql.org/docs/8.1/static/bug-reporting.html regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2105: ERROR: relation "comoma" does not exist
On Fri, Dec 09, 2005 at 18:09:52 +,
alejandro ramirez <[EMAIL PROTECTED]> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2105
> Logged by: alejandro ramirez
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: PostgreSQL 8.1.
> Operating system: windows 2000 profesional
> Description:ERROR: relation "comoma" does not exist
> Details:
>
> Hi there!
> i a new user of PostgreSQL, i develop on severals languajes like as
> J2SDK/J2EE/SERVLETS/VB/FOXPRO...etc
> and i use a several RDBM. I try to use PostgreSQL to implement into 2
> aplication's (one on the internet and the other in aq model two fase's like
> a client-server), but i meet a problem when i use a table for a simple
> "SELECT" or "DELET FROM myTable" the result for this is: "ERROR: relation
> "comoma" does not exist" (comoma it's a table) but this table exists on my
> database named "AMS"
>
> i looking for a this error on the net, but i don't found any answer...
Did you try to get a list of tables in psql using the \d command?
>
> please, do you know what happened with this!
Most likely you created the table's name with an uppercase character and now
need to quote (") the table name whenever you use it.
The normal rule is to either never (preferred) quote table names or always
quote table names. Doing it sometimes is a recipie from headaches.
>
> for you help, than's a lote!
You shouldn't have reported this as a bug as your first step. This probably
should have been posted to the novice or general lists.
>
> Regards from mexico
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault
On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote: > "" <[EMAIL PROTECTED]> writes: > > Description:EXPLAIN ANALYZE with SELECT query causes a single > > backend server process to segfault > > Could you provide a self-contained test case, please? Here's a test case: CREATE TABLE foo (x integer); EXPLAIN SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y FROM foo ORDER BY y; I get a segfault in 8.1.1 with EXPLAIN but not for the query alone. Here's the stack trace: #0 0x081bfdcf in get_rule_expr (node=0x83ac460, context=0xbfbfd4b0, showimplicit=80 'P') at pg_list.h:82 #1 0x081c0e73 in deparse_expression_pretty (expr=0x83ac460, dpcontext=0x83ac7b0, forceprefix=0 '\0', showimplicit=1 '\001', prettyFlags=32, startIndent=32) at ruleutils.c:1388 #2 0x080ef4a5 in explain_outNode (str=0x83ac648, plan=0x83ac388, planstate=0x83ad1f0, outer_plan=0x0, indent=0, es=0x83ac638) at explain.c:1160 #3 0x080ef90d in ExplainOnePlan (queryDesc=0x83ac5f0, stmt=0x835b708, tstate=0x8305b00) at explain.c:282 #4 0x080efca5 in ExplainOneQuery (query=0x83c7580, stmt=0x835b708, tstate=0x8305b00) at explain.c:214 #5 0x080efdcd in ExplainQuery (stmt=0x835b708, dest=0x83c7530) at explain.c:121 #6 0x0818cb2f in PortalRunUtility (portal=0x83ca018, query=0x835b228, dest=0x83c7530, completionTag=0x0) at pquery.c:987 #7 0x0818ce16 in PortalRun (portal=0x83ca018, count=2147483647, dest=0x835b748, altdest=0x835b748, completionTag=0xbfbfd7b0 "") at pg_list.h:81 #8 0x08189127 in exec_simple_query (query_string=0x835b018 "EXPLAIN\nSELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y\nFROM foo\nORDER BY y;") at postgres.c:1002 #9 0x0818b693 in PostgresMain (argc=4, argv=0x82e4d98, username=0x82e4d78 "mfuhr") at postgres.c:3168 #10 0x081684e6 in ServerLoop () at postmaster.c:2853 #11 0x08169a01 in PostmasterMain (argc=3, argv=0xbfbfecb8) at postmaster.c:943 #12 0x08133612 in main (argc=3, argv=0xbfbfecb8) at main.c:256 -- Michael Fuhr ---(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: [BUGS] BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote: >> Could you provide a self-contained test case, please? > Here's a test case: Thanks, I'll fix this ... but I'm not certain it is the same problem the OP is seeing. The OR join clauses he's got look related to other bugs we've found in 8.1. regards, tom lane ---(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: [BUGS] pgsql8.1: About COPY Command and system clomn oid
"wangshj" <[EMAIL PROTECTED]> wrote > > oidtest=# COPY oidtest (name) WITH OIDS FROM stdin; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself. >>> 54513copyname >>> \. > oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name2' ) ; > INSERT 54513 1 > oidtest=# SELECT oid,name from oidtest ; > oid | name > ---+-- > 54512 | name1 > 54513 | copyname > 54513 | name2 > (3 rows) > > oidtest=# > Then I got two records with same oid(54513). > This is not a bug. You can specify any valid number here as Oids. If you don't want duplicated Oids or strange Oids, then don't specify WITH OIDS clause. Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault
Michael Fuhr <[EMAIL PROTECTED]> writes: > CREATE TABLE foo (x integer); > EXPLAIN > SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y > FROM foo > ORDER BY y; Fixed, but this is a bit too late for 8.1.1. Meanwhile the easy way to avoid the bug is to write the CASE in a less obtuse form, like CASE WHEN x = 1 THEN 1 ELSE 0 END or CASE x WHEN 1 THEN 1 ELSE 0 END regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
