Обсуждение: Signal 11
I'm fairly new to postgres, we are using postgresql-7.2.3 on Linux Red-Hat 8.0 I am having a problem with the shared buffers setting. Whenever I set the shared buffers above 96 I start getting Segmentation Faults when performing queries. However, if I leave the shared buffers set to 96 or lower the system performs flawless. I can start up postgres with up to 128Mb of shared buffers with no problems. It just erratically faults. Got any ideas? Thanks! Eric L. Blevins
From: "Andrew Sullivan" <andrew@libertyrms.info> To: "Eric L. Blevins" <eblevins@insight.rr.com> Sent: Thursday, October 31, 2002 10:33 AM Subject: Re: [ADMIN] Signal 11 > > Have you checked your physical memory for flaws? > Yes, I have checked the memory, it is fine. We had so much trouble we replaced the innards of the server. New PS, MB, Processor, and RAM Still have the exact same problem. Could this possibly be related to some kernel setting? Eric L. Blevins
"Eric L. Blevins" <eblevins@insight.rr.com> writes:
> From: "Andrew Sullivan" <andrew@libertyrms.info>
>> Have you checked your physical memory for flaws?
>>
> Yes, I have checked the memory, it is fine.
I'd suggest checking harder ;-). I think an unreliable section of
memory is by far the most probable explanation. However, if you want
to dig for a software reason, try collecting stack backtraces from
the crashed processes' core dumps to see if there's any pattern.
regards, tom lane
FYI, our "signal 11" problem was caused by faulty cache memory on the RAID controller. -----Original Message----- From: Eric L. Blevins [mailto:eblevins@insight.rr.com] Sent: Thursday, October 31, 2002 11:16 To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Signal 11 From: "Andrew Sullivan" <andrew@libertyrms.info> To: "Eric L. Blevins" <eblevins@insight.rr.com> Sent: Thursday, October 31, 2002 10:33 AM Subject: Re: [ADMIN] Signal 11 > > Have you checked your physical memory for flaws? > Yes, I have checked the memory, it is fine. We had so much trouble we replaced the innards of the server. New PS, MB, Processor, and RAM Still have the exact same problem. Could this possibly be related to some kernel setting? Eric L. Blevins ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
This might sound silly, but I am trying something basic, and having
trouble. What I want to do is unload selected rows from a table, and load
them somewhere else.
pg_dump works great if you want the whole table. I suppose I could from
one table, dump into a temp, and then pg_dump it, but that seems silly.
I noticed in the pg_dump unloaded looking rows, with a:
COPY "table" FROM stdin;
before the rows.
So, I ran psql, did a \o to capture the output,
did a select * from table where x=y
whacked the first row in the output file, and added the COPY statement.
then did a psql dbname <file-with-rows.sql
The first column loaded, but the rest of the columns did not. I noticed
there were | delimiters in my unloaded one, and none in a pg_dump file.
This seems way too klunky, so I must be going about it all wrong. In
Informix-land I would just do a unload then a load. What is the postgresql
equivalent?
Suggestions?
Thanks,
Naomi
----------------------------------------------------------------------------
----------------------------------
Naomi Walker
Eldorado Computing, Inc
Chief Information Officer
nwalker@eldocomp.com
602-604-3100 x242
----- Original Message ----- From: "Eric L. Blevins" <eblevins@insight.rr.com> To: <pgsql-admin@postgresql.org> Sent: Thursday, October 31, 2002 11:15 AM Subject: Re: [ADMIN] Signal 11 > From: "Andrew Sullivan" <andrew@libertyrms.info> > To: "Eric L. Blevins" <eblevins@insight.rr.com> > Sent: Thursday, October 31, 2002 10:33 AM > Subject: Re: [ADMIN] Signal 11 > > > > > > Have you checked your physical memory for flaws? > > > Yes, I have checked the memory, it is fine. > We had so much trouble we replaced the innards of the server. > New PS, MB, Processor, and RAM > Still have the exact same problem. We got it working finally 1. We installed a kernel made for large memory machines from an RPM 2. in /etc/sysctl.conf we added the following 3 lines kernel.shmall = 805306368 kernel.shmmax = 805306368 kernel.shmmni = 8192 kernel.sem = 250 32000 100 128 3. In postgresql.conf we set the shared_buffers to 50000 She is working flawless and very fast now. Just the performance we needed! Eric L. Blevins www.wifimaps.com
> So, I ran psql, did a \o to capture the output, > did a select * from table where x=y > whacked the first row in the output file, and added the COPY statement. test_db=> \pset tuples_only -- does whacking for you > This seems way too klunky, so I must be going about it all wrong. In > Informix-land I would just do a unload then a load. What is the postgresql > equivalent? It is possible. Delimiters can be used while restoring the data file. Null string specification can also be specified. See, test_db=> \h COPY for more details. In addition you should also do some find and replace in the data file before restoring it to another database. They are, s/\s*|\s*//g s/^\s*//g Now, i bet you can able to restore. regards, bhuvaneswaran
> >It is possible. Delimiters can be used while restoring the data file. >Null string specification can also be specified. See, >test_db=> \h COPY >for more details. The problem with COPY, I think, is that I cannot use a WHERE statement. I'd like to just unload certain rows from a table. If I wanted the whole table, i'll do a pg_dump or copy. >In addition you should also do some find and replace in the data file >before restoring it to another database. They are, >s/\s*|\s*//g >s/^\s*//g Yes, sed is a fine tool, but this seems like such a fundamental need, i'm surprised there is not better method. Again, i'd like a way to easily unload some selected rows (select * table where foo=X) from a table, and save them, or load them in another cluster, etc. Short of writing them to a temp table, then pg_dumping, or some klunk-oid mething, I do not see a clean way. Back to the maddening crowd. Anyone at least agreed this is needed? Could we add "where" clauses to COPY? That would be perfect. ---------------------------------------------------------------------------- ---------------------------------- Naomi Walker Eldorado Computing, Inc Chief Information Officer nwalker@eldocomp.com 602-604-3100 x242
Yes, agreed, it would be nice to have WHERE as part of COPY. In fact, I
like Informix's solution, which merges data in/out with INSERT/SELECT:
UNLOAD TO '/tmp/x'
SELECT *
FROM tab;
and
LOAD FROM '/tmp/x'
INSERT INTO tab;
It is tough to beat this flexibility.
---------------------------------------------------------------------------
Naomi Walker wrote:
>
> >
> >It is possible. Delimiters can be used while restoring the data file.
> >Null string specification can also be specified. See,
> >test_db=> \h COPY
> >for more details.
>
> The problem with COPY, I think, is that I cannot use a WHERE
> statement. I'd like to just unload certain rows from a table. If I wanted
> the whole table, i'll do a pg_dump or copy.
>
>
> >In addition you should also do some find and replace in the data file
> >before restoring it to another database. They are,
> >s/\s*|\s*//g
> >s/^\s*//g
>
> Yes, sed is a fine tool, but this seems like such a fundamental need, i'm
> surprised there is not better method.
>
> Again, i'd like a way to easily unload some selected rows (select * table
> where foo=X) from a table, and save them, or load them in another cluster,
> etc. Short of writing them to a temp table, then pg_dumping, or some
> klunk-oid mething, I do not see a clean way.
>
> Back to the maddening crowd. Anyone at least agreed this is needed? Could
> we add "where" clauses to COPY? That would be perfect.
>
> ----------------------------------------------------------------------------
> ----------------------------------
> Naomi Walker
> Eldorado Computing, Inc
> Chief Information Officer
> nwalker@eldocomp.com
> 602-604-3100 x242
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Exactly. My Informix background is showing. Is there a way to officially get this put on a to do list? I'd be most appreciative of the value this adds. Slightly off subject, is there a way to read from one cluster and insert into a database in another cluster (local or remote)? I think I read this is coming in the future. In Informix-land, we used to do this through a named pipe. It was quite handy. At 10:39 PM 11/1/02 -0500, Bruce Momjian wrote: >Yes, agreed, it would be nice to have WHERE as part of COPY. In fact, I >like Informix's solution, which merges data in/out with INSERT/SELECT: > > UNLOAD TO '/tmp/x' > SELECT * > FROM tab; > >and > > LOAD FROM '/tmp/x' > INSERT INTO tab; > >It is tough to beat this flexibility. > >--------------------------------------------------------------------------- > >Naomi Walker wrote: > > > > > The problem with COPY, I think, is that I cannot use a WHERE > > statement. I'd like to just unload certain rows from a table. If I > wanted > > the whole table, i'll do a pg_dump or copy. > > > > > Again, i'd like a way to easily unload some selected rows (select * table > > where foo=X) from a table, and save them, or load them in another cluster, > > etc. Short of writing them to a temp table, then pg_dumping, or some > > klunk-oid mething, I do not see a clean way. > > > > Back to the maddening crowd. Anyone at least agreed this is > needed? Could > > we add "where" clauses to COPY? That would be perfect. > >
We do have /contrib/dblink in 7.3 that allows cross-db query access. We don't have any pass-data-between-db's capability that I know of. --------------------------------------------------------------------------- Naomi Walker wrote: > Exactly. My Informix background is showing. Is there a way to officially > get this put on a to do list? I'd be most appreciative of the value this adds. > > Slightly off subject, is there a way to read from one cluster and insert > into a database in another cluster (local or remote)? I think I read this > is coming in the future. In Informix-land, we used to do this through a > named pipe. It was quite handy. > > At 10:39 PM 11/1/02 -0500, Bruce Momjian wrote: > > >Yes, agreed, it would be nice to have WHERE as part of COPY. In fact, I > >like Informix's solution, which merges data in/out with INSERT/SELECT: > > > > UNLOAD TO '/tmp/x' > > SELECT * > > FROM tab; > > > >and > > > > LOAD FROM '/tmp/x' > > INSERT INTO tab; > > > >It is tough to beat this flexibility. > > > >--------------------------------------------------------------------------- > > > >Naomi Walker wrote: > > > > > > > > The problem with COPY, I think, is that I cannot use a WHERE > > > statement. I'd like to just unload certain rows from a table. If I > > wanted > > > the whole table, i'll do a pg_dump or copy. > > > > > > > > Again, i'd like a way to easily unload some selected rows (select * table > > > where foo=X) from a table, and save them, or load them in another cluster, > > > etc. Short of writing them to a temp table, then pg_dumping, or some > > > klunk-oid mething, I do not see a clean way. > > > > > > Back to the maddening crowd. Anyone at least agreed this is > > needed? Could > > > we add "where" clauses to COPY? That would be perfect. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Can we add unload/load functionality to the list, please? > > >Yes, agreed, it would be nice to have WHERE as part of COPY. In fact, I > > >like Informix's solution, which merges data in/out with INSERT/SELECT: > > > > > > UNLOAD TO '/tmp/x' > > > SELECT * > > > FROM tab; > > > > > >and > > > > > > LOAD FROM '/tmp/x' > > > INSERT INTO tab;
Naomi Walker wrote: > Can we add unload/load functionality to the list, please? > > > > > > >Yes, agreed, it would be nice to have WHERE as part of COPY. In fact, I > > > >like Informix's solution, which merges data in/out with INSERT/SELECT: > > > > > > > > UNLOAD TO '/tmp/x' > > > > SELECT * > > > > FROM tab; > > > > > > > >and > > > > > > > > LOAD FROM '/tmp/x' > > > > INSERT INTO tab; I have thought of that. We would probably join COPY to INSERT/SELECT rather than use LOAD/UNLOAD. The problem is that the COPY code reads right out of the table, rather than reading from the executor. This change would require that. You can do it now by using TEMP tables to do the COPY. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073