Re: [GENERAL] copy data from utf16 csv file
On Thu, Oct 27, 2005 at 03:30:43PM -0700, Shane wrote: Can anyone suggest how I can either get these into PG directly or massage the file so as to be compatable? To my knowledge the only Unicode encoding used by Postgres is utf-8. Try 'recode' or 'iconv' on unix-like systems. A better text editor can convert on MS Windows. Peter ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why database is corrupted after re-booting
w_tom wrote: Series mode protector will ignore or avoid THE one and essential component of an effective protection system - single point earth ground. Indeed. And yes, a high end data center should survive a lightning strike (as well as hospital's power systems, etc). Here's a nice article where Suncoast Schools Federal Credit Union's data center survived a direct lightning strike to their 480-V service entrance cable. The article spends a lot of the time talking about the grounding system. http://www.ecpzone.com/article/article.jsp?siteSection=12id=41 Starting from the ground up, the main elements of the [lightning protection] system...include: (1) Three 20-ft x 5/8-in (6-m x 16-mm) copper-clad-steel grounding electrodes [...] The grounding system's resistance to earth as measured by fall-of-potential testing is 4.3 ohms. (2) Another 4/0 copper grounding conductor connects the ground-neutral bus in the service entrance panel to the ground bus in a 480-V distribution panel ... (3) Multiple uninterruptible power supplies (UPSs) (4) Up to seven layers of voltage surge protection High Quality Grounding even the most expensive TVSS you can buy is absolutely useless unless it sees a high-quality, low-resistance ground. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why database is corrupted after re-booting
QUANTUM FIREPALLP LM20.5 is a widely used ATA IDE drive. Where do find information does it implement write caching properly or not ? I don't think the manufacturers bother to make this sort of information available. Is there IDE drive compatibility list for Postgres ? No - for the reason above (amongst oghers). Richard, thank you. Classification of IDE drives into good and bad ones requires knowing *at least one* good and bad model. Can you write one good and bad IDE drive models, please? Knowing those models before buying is huge step forward for perventing database corruption in desktop computers. Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why database is corrupted after re-booting
Andrus wrote: QUANTUM FIREPALLP LM20.5 is a widely used ATA IDE drive. Where do find information does it implement write caching properly or not ? I don't think the manufacturers bother to make this sort of information available. Is there IDE drive compatibility list for Postgres ? No - for the reason above (amongst oghers). Richard, thank you. Classification of IDE drives into good and bad ones requires knowing *at least one* good and bad model. Can you write one good and bad IDE drive models, please? Knowing those models before buying is huge step forward for perventing database corruption in desktop computers. The bottom line is that IDE are desktop drives, not designed for high concurrency. Read this for details: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf -- 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Function written in C, hangs on one machine and not another...
PostgreSQL 7.4 ...
Essentially, I've written a function in C for use with PostgreSQL. The debugger
shows that the program is hanging on the part of the program that is writing
data into it's own STDIN.
[snip]
// Open up and hijack STDIN
int pipe_pair[2];
int pipe_rv = pipe(pipe_pair);
if (pipe_rv != 0)
// Abort! Abort!
{
close(pipe_pair[1]);
pfree(param_1);
pfree(param_2);
PG_RETURN_NULL();
}
int newfd = dup2(pipe_pair[0],STDIN_FILENO);
if (newfd != 0)
// Abort! Abort!
{
close(pipe_pair[1]);
pfree(param_1);
pfree(param_2);
PG_RETURN_NULL();
}
// Write param_1 to hijacked pipe
write(pipe_pair[1], param_1, param_1_len); // Hangs here...
[/snip]
It works on the machine I use for testing from within PostgreSQL, but it
doesn't work on the machine which is the production server. I'd hate for this
to matter, but I ought to disclose that testing machine is a 1-way AMD Box with
a more recent version of the Linux 2.6 kernel, and a more recent version of
libc. The production machine is a 2-way Dell Xeon processor. Same version of
PostgreSQL, compiled with the same flags (except with debugging symbols for the
testing machine). You'd, or at least I would, think simple code like this would
compile and run on multiple platforms...
I can perform the same STDIN hijacking on both machines in a standalone
program, but it fails under PostgreSQL.
I'm completely stumped, and I need YOUR insight! Thank you!!
CGV
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Looking for a command to list schemas
Le 2005 10 26 09:36, Martijn van Oosterhout a ecrit: On Tue, Oct 25, 2005 at 11:25:22AM -0700, Cosmopo wrote: Hello, We are presently experimenting with Postgresql schemas... We used basic commands like \d to list info from all tables/sequence/owner in a db... The schema info was always public since we never created one... \dn list schemas \dt *.* list all tables in all schemas \z *.* list priveledges for all tables in all schemas etc... Is this helpful? Hello, Yes and no... The \dn does not list the schemas... I thinks it's my old Postgresql version (7.3.2 and 7.3.4)... But \z does the trick... It's a sign that it's time to upgrade... 8-) Thanks -- Mark ~~ ---(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] Function written in C, hangs on one machine and not another...
On Fri, Oct 28, 2005 at 06:38:29AM -0700, CG wrote: PostgreSQL 7.4 ... Essentially, I've written a function in C for use with PostgreSQL. The debugger shows that the program is hanging on the part of the program that is writing data into it's own STDIN. Umm, what *are* you trying to do? Is this running in the backend? Firstly, depending on the saize of param_1, the write will block because it can't write all of it (usually PIPE_BUF). Perhaps recent kernel versions have changed to make it so no data is accepted until a reader appears even if the data is smaller than that. Since apparently you want the read to happen in the same process as the write, you've just deadlocked yourself. The write won't happen till someone reads, and the read won't happen because you're stuck writing... Finally, this is insane, why would you want to change STDIN? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgptOLEGoglDs.pgp Description: PGP signature
Re: [GENERAL] Why database is corrupted after re-booting
Of course not counting the Western Digital Raptor SATA drive, which are priced more like SCSI drives also, and have many of the features of a SCSI drive including NCQ Alex On 10/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Andrus wrote: QUANTUM FIREPALLP LM20.5 is a widely used ATA IDE drive. Where do find information does it implement write caching properly or not ? I don't think the manufacturers bother to make this sort of information available. Is there IDE drive compatibility list for Postgres ? No - for the reason above (amongst oghers). Richard, thank you. Classification of IDE drives into good and bad ones requires knowing *at least one* good and bad model. Can you write one good and bad IDE drive models, please? Knowing those models before buying is huge step forward for perventing database corruption in desktop computers. The bottom line is that IDE are desktop drives, not designed for high concurrency. Read this for details: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf -- 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why database is corrupted after re-booting
Alex Turner wrote: Of course not counting the Western Digital Raptor SATA drive, which are priced more like SCSI drives also, and have many of the features of a SCSI drive including NCQ Well, the PDF talks about several aspects of server drives, including concurrency, performance, and reliability. Not cutting corners to save money in these areas are features for server drives. --- Alex On 10/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Andrus wrote: QUANTUM FIREPALLP LM20.5 is a widely used ATA IDE drive. Where do find information does it implement write caching properly or not ? I don't think the manufacturers bother to make this sort of information available. Is there IDE drive compatibility list for Postgres ? No - for the reason above (amongst oghers). Richard, thank you. Classification of IDE drives into good and bad ones requires knowing *at least one* good and bad model. Can you write one good and bad IDE drive models, please? Knowing those models before buying is huge step forward for perventing database corruption in desktop computers. The bottom line is that IDE are desktop drives, not designed for high concurrency. Read this for details: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf -- 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend -- 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why database is corrupted after re-booting
I have read it before - it's a _fantastic_ resource, and I will probably make every junior tech I ever hire read it too. On 10/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Alex Turner wrote: Of course not counting the Western Digital Raptor SATA drive, which are priced more like SCSI drives also, and have many of the features of a SCSI drive including NCQ Well, the PDF talks about several aspects of server drives, including concurrency, performance, and reliability. Not cutting corners to save money in these areas are features for server drives. --- Alex On 10/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Andrus wrote: QUANTUM FIREPALLP LM20.5 is a widely used ATA IDE drive. Where do find information does it implement write caching properly or not ? I don't think the manufacturers bother to make this sort of information available. Is there IDE drive compatibility list for Postgres ? No - for the reason above (amongst oghers). Richard, thank you. Classification of IDE drives into good and bad ones requires knowing *at least one* good and bad model. Can you write one good and bad IDE drive models, please? Knowing those models before buying is huge step forward for perventing database corruption in desktop computers. The bottom line is that IDE are desktop drives, not designed for high concurrency. Read this for details: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf -- 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend -- 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 ---(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
[GENERAL] Learning server programming
I'm looking for information on learning how to write stored procedures and server-side programming in general. The docs provide a reference, of course, but that only tells what one can do, not what one should do. In particular, I'm interested in how server programming fits in with system architecture at large. I have in mind issues such as O/R-mapping when the database isn't just a dumb data store, handling of tasks like authorization in the application or in the db server. Any recommendations where to start reading? Michael -- Michael Schuerig The more it stays the same, mailto:[EMAIL PROTECTED]The less it changes! http://www.schuerig.de/michael/ --Spinal Tap, The Majesty of Rock ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Function written in C, hangs on one machine and not another...
--- Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Oct 28, 2005 at 06:38:29AM -0700, CG wrote: Umm, what *are* you trying to do? Is this running in the backend? Yes, running on the back-end. I'm trying to utilize Adobe's FDF toolkit to parse the FDF files stored in my database. They distirubte a C-Library that can be used to parse FDF files. Firstly, depending on the saize of param_1, the write will block because it can't write all of it (usually PIPE_BUF). Perhaps recent kernel versions have changed to make it so no data is accepted until a reader appears even if the data is smaller than that. Since apparently you want the read to happen in the same process as the write, you've just deadlocked yourself. The write won't happen till someone reads, and the read won't happen because you're stuck writing... So it might be a kernel thing. What is different when the function is called from within PostgreSQL that is different that the function being called in a standalone program? Finally, this is insane, why would you want to change STDIN? Insanity? I agree completely. The major issue is that the FDF Toolkit has only one function for reading in FDF Data: /* FDFOpen: Reads an FDF file into memory. Client should call FDFClose() when the FDF is no longer needed. Parameters: - fileName: Complete pathname (in Host encoding), or - to read from stdin. - howMany: If fileName specifies stdin, then howMany should indicate the number of characters to read. Otherwise, it is unused. In a web server environment, this is available as the value of the CONTENT_LENGTH environment variable. In some servers executing cgi-bin scripts, if the script tries to read stdin until an EOF is reached, the script hangs. Thus this parameter. - pTheFDF: If FDFOpen() returns FDFErcOK, then pTheFDF will point to an FDFDoc, which is needed for most other calls in the API. - Error codes: FDFErcBadParameter, FDFErcFileSysErr, FDFErcBadFDF, FDFErcInternalError */ FDFLIBAPI FDFErc FDFOpen(const char* fileName, ASInt32 howMany, FDFDoc* pTheFDF); There's no other way to load data into the toolkit! (Can you /feel/ the insanity?) Does this give you any more insight into an alternate method of getting this thing done? __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(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: [GENERAL] Learning server programming
Michael Schuerig [EMAIL PROTECTED] schrieb: I'm looking for information on learning how to write stored procedures and server-side programming in general. The docs provide a reference, Falls Du in der Nähe von Dresden wohnst: dort ist morgen Linux-Info-Tag und ein paar Leute der deutschen PG-Szene sind dort. http://linux-info-tag.de Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Connections to DB
Jim C. Nasby wrote: SELECT * FROM pg_stat_activity; Note that if you turn on stats_command_string you'll also be able to see what each connection is doing if you're connected as a superuser. Just as a little FYI, on 8.1 pg_stat_activity will show the IP address and port as well, but only on 8.1. Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why database is corrupted after re-booting
huh never heard of that - I'll hold out testing it for now but thats good info. (how does it know which partition - if there's 2?) Troy H ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] serial number in output
Hi, What function should I use to get a serial number, together with my results, from a query? Ex. Of output I want: 1 ooo pp ij 2 hou joo iu 3 bhi ft yh Basically, I would like to have one column with integers, from 1 onwards, no matter how many tables I join in, or the data that I get back. Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Why database is corrupted after re-booting
One of the many problems with FAT32 was that files on the drive can be deleted if power is lost. This is why FAT was obsoleted by HPFS which in turn was obsoleted by NTFS. Power loss should not cause data loss which is why we stopped using FAT even before Windows 95 was released. Program to convert to NTFS is called convert. But for details, use Windows HELP command. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] tablename.columnname%TYPE in Functions Types
My ref: http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html WHAT AM I MISSING in my ORACLE to PG Port? --1) I write CREATE FUNCTION func1( var_1 INOUT T_TABLE1.C_COL1%TYPE, var_2 IN INTEGER) AS. --2) It works but, NOTICE: type reference T_TABLE1.C_COL1%TYPE converted to character varying --3) It says CREATE OR REPLACE FUNCTION func1(INOUT var_1 varchar, IN var_2 int4) AS... It kinda works but looks static, now I kinda wanted it to STAY %TYPE. ALSO --1) CREATE TYPE type1 AS (tvar_1 T_TABLE1.C_COL1%TYPE, tvar_2 INTEGER); XX ERROR: syntax error at or near % --2) CREATE TYPE type1 AS (tvar_1 T_TABLE1.C_COL1, tvar_2 INTEGER); XX ERROR: schema T_TABLE1 does not exist I don't want to asign it at the ROW level just Column. I got this stuff all over and need a good conversion solution. Any help is appreciated... Thanks Troy ---(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: [GENERAL] Function written in C, hangs on one machine and not another...
CG [EMAIL PROTECTED] writes: Does this give you any more insight into an alternate method of getting this thing done? I would fork(), set up file descriptors appropriately, then have the child call the Adobe library and the parent feed the data to it. Once the document is loaded in the child, do whatever processing you need to, then pass the results back to the parent via stdout or a temporary file. Ugly, but probably the most robust way to do it. Make sure you don't call any PG-internal functions in the child process, as that will confuse things badly. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why database is corrupted after re-booting
Destructive surges seek earth ground. Do you think a protector is
going to stop what 3 miles of non-conductive sky could not? And yet
that is exactly what some protectors manufacturers hope you will
assume.
Effective protectors don't stop, block, or absorb typically
destructive transients. Joules does not mean protection is about
absorbing surge energy. Protectors shunt as even Ben Franklin
demonstrated in 1752. They shunt as protectors do to protect every
telephone switching station and every commercial radio station. As was
demonstrated in 1930s GE and Westinghouse papers. Destructive
transients seek earth ground - either before entering the building
('whole house' protection) or via electronics (surge damage).
What defines effective protection? The protector is only as good as
the protection it connects to. Ineffective protectors hope you never
learn why earth ground is THE one essential component of every
protection system. Protectors are temporary wires to protection - the
single point earth ground. Protectors are only effective when they
create a short (typically 'less than 10 foot') connection to earth.
Series mode protectors (Brickwall, Surgex, Zerosurge) are good
supplemental protection. But again, without that short connection to
earth ground (the shunt mode protector or hardwire connection), then
even series mode protectors are bypassed or overwhelmed. See that
safety ground wire? It bypasses the series mode protector. Meanwhile,
what the series mode protector is doing should already be inside the
electronics.
Series mode protector will ignore or avoid THE one and essential
component of an effective protection system - single point earth
ground. Protection is a building wide system. Its most essential
component is a single point earth ground. All connections to that
earthing must be short - typically 'less than 10 feet'. All incoming
utilities must connect to that protection - either via a protector (ie
AC electric, telephone, communication wires) or via a direct hardwire
(ie cable TV, satellite dish).
Meanwhile, we are only discussing secondary protection. Primary
protection is provided by the utility:
http://www.tvtower.com/fpl.html
To learn about serious protection, maybe start with a benchmark in
this industry - Polyphaser - whose app notes are considered legendary:
http://www.polyphaser.com/ppc_ptd_home.aspx
What does Polyphaser discuss? Their products? No. Polyphaser app
notes discuss the most critical component on a protection system -
single point earth ground.
BTW notice a repeated reference to less than 10 feet. One of many
requirements to reduce wire impedance. Not resistance - impedance.
That means sharp bends, splices, inside metal conduit, etc all can
diminish effective earthing.
What do plug-in UPSes avoid discussing? Earth ground. No earth
ground means no effective protection. So they claim protection -
forgetting to mention they don't protect from the typically destructive
transient. Hoping you will never learn about the most essential
component of a protection system. That would also explain why
ineffective protectors also have too few joules. They claim protection
- forgetting to mention the protection is not effective. Protectors
are only as effective as their earth ground.
Keith C. Perry wrote:
Actually, because I lost several thousands of dollars or equipement a couple
of
years ago, I recommended these brickwall products to a company.
http://brickwall.com/index.htm
We actually never deployed these units (grounding the communications lines
ended
up being a much cheaper solution) but I did talk and engineer at the company
and
apparently they have some hospitals as client that use unitss. I'm won't get
into the technology of how they work since you can read that yourself but I
remember having a warm and fuzzy after my conversation.
I will pull one quote from their web site though...
Unlike MOV's, TRANS-ZORBS and similar shunt based surge protectors that use
elements weighing less than 1/4 ounce, Brick Wall surge protectors can easily
absorb any surge repeatedly with absolutely no degradation.
The important phrase here is ...absorb any surge repeatedly with absolutely
no
degradation.
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function written in C, hangs on one machine and not another...
On Fri, Oct 28, 2005 at 07:24:12AM -0700, CG wrote: So it might be a kernel thing. What is different when the function is called from within PostgreSQL that is different that the function being called in a standalone program? Not entirely sure, but I'm sure the size of the write matters. For example, if your test rpogram, did you check that the write actually wrote everything? Insanity? I agree completely. The major issue is that the FDF Toolkit has only one function for reading in FDF Data: snip Firstly, instead of using stdin, you can pass /dev/fd/file descriptor as the filename (on Linux). This avoids stuffing with stdin. That doesn't solve the blocking problem. To do that you really need multiple threads of execution, so either fork or threads, neither of which are really supported. ISTM the best idea: write the data to disk then read it back. Why be difficult when you can do it easily... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpI50242jyIO.pgp Description: PGP signature
Re: [GENERAL] serial number in output
On 10/28/05, alessandra de gregorio [EMAIL PROTECTED] wrote: What function should I use to get a serial number, together with my results,from a query?Ex. Of output I want:1 ooo ppij2 hou joo iu3 bhi ftyhBasically, I would like to have one column with integers, from 1 onwards, no matter how many tables I join in, or the data that I get back. first of all - this is job for client program to do the numbering, but if you really insist on having this in database (why?) then just use temporary sequence. depesz
Re: [GENERAL] Function written in C, hangs on one machine and not another...
--- Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Oct 28, 2005 at 07:24:12AM -0700, CG wrote: Not entirely sure, but I'm sure the size of the write matters. For example, if your test rpogram, did you check that the write actually wrote everything? There's beginning and ending tokens in the FDF filespec. The toolkit complains if the data isn't all there... Firstly, instead of using stdin, you can pass /dev/fd/file descriptor as the filename (on Linux). This avoids stuffing with stdin. That's a FANTASTIC idea. I'll give it a go. We'll cross our fingers, hold our breath, and hope that the blocking issue evaporates. :) ISTM the best idea: write the data to disk then read it back. Why be difficult when you can do it easily... I was never supposed to have to do this sort of thing. The idea was never to pull individual peices of data out of the FDFs. Now, the bosses say I have to do some usage analysis, and the data is locked up tight in an FDF. I suppose I could write 100+ files to disk and read them back off and then delete them. At the time, that seemed more insane to me than trying to pump data into stdin. I'm not so sure anymore.. :) __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function written in C, hangs on one machine and not another...
--- Douglas McNaught [EMAIL PROTECTED] wrote: CG [EMAIL PROTECTED] writes: Does this give you any more insight into an alternate method of getting this thing done? I would fork(), set up file descriptors appropriately, then have the child call the Adobe library and the parent feed the data to it. Once the document is loaded in the child, do whatever processing you need to, then pass the results back to the parent via stdout or a temporary file. Ugly, but probably the most robust way to do it. Make sure you don't call any PG-internal functions in the child process, as that will confuse things badly. Is it safe for the postgres engine to fork()? Would the child need to close down anything immediately in its main() to avoid corrupting the parent? Dennis Jenkins ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Function written in C, hangs on one machine and not another...
--- CG [EMAIL PROTECTED] wrote: There's no other way to load data into the toolkit! (Can you /feel/ the insanity?) Does this give you any more insight into an alternate method of getting this thing done? Write a completely seperate process to process your FDF stuff. Have this new process expose a communicastions channel (message queues, sockets, shared memory, etc...). Write your PostgreSQL 'C' function to use this channel. You'll get almost complete seperation and the ability to debug each piece independant of the other. You can write stubs for both ends: a fake server for testing the PostgreSQL part, and a fake client for testing the daemon that you wrote. Dennis Jenkins ---(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] Function written in C, hangs on one machine and not another...
Dennis Jenkins [EMAIL PROTECTED] writes: Is it safe for the postgres engine to fork()? Would the child need to close down anything immediately in its main() to avoid corrupting the parent? I *think* (Tom may correct me) that as long as you don't call into the backend code at all in the child process, and don't write to any file descriptors other than (properly set-up) stdin and stdout, you'd be OK. The safest thing to do would be to exec() a separate binary that does the parsing, but that would incur an additional performace penalty. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Function written in C, hangs on one machine and not another...
On Fri, Oct 28, 2005 at 11:59:03AM -0400, Douglas McNaught wrote: Dennis Jenkins [EMAIL PROTECTED] writes: Is it safe for the postgres engine to fork()? Would the child need to close down anything immediately in its main() to avoid corrupting the parent? I *think* (Tom may correct me) that as long as you don't call into the backend code at all in the child process, and don't write to any file descriptors other than (properly set-up) stdin and stdout, you'd be OK. The safest thing to do would be to exec() a separate binary that does the parsing, but that would incur an additional performace penalty. The things that have screwed me up in the past with pulling tricks like this are: 1. Program has registered atexit() handlers. _exit() avoids this. 2. Pending stdio output that gets flushed. The backend doesn't use stdio much so you might be fine here. 3. Signals. Make sure you don't get sent signals that screw state. Might be wise to block them all, or reset them all to default. Truly, exec() is the cleanest way to solve all this, it simply replaces the current process, lock, stock and barrel. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpRaOj7gYw6W.pgp Description: PGP signature
Re: [GENERAL] Function written in C, hangs on one machine and not another...
Martijn van Oosterhout kleptog@svana.org writes: The things that have screwed me up in the past with pulling tricks like this are: 1. Program has registered atexit() handlers. _exit() avoids this. 2. Pending stdio output that gets flushed. The backend doesn't use stdio much so you might be fine here. 3. Signals. Make sure you don't get sent signals that screw state. Might be wise to block them all, or reset them all to default. Truly, exec() is the cleanest way to solve all this, it simply replaces the current process, lock, stock and barrel. Definitely. It would probably also be good to close all file descriptors (except for stdin/etdout/stderr) before exec(), just in case the other binary does something screwy with random file descriptors (which it obviously shouldn't). -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] querying while copying into a table and optimizations
Message-ID: [EMAIL PROTECTED] Date: Fri, 28 Oct 2005 13:22:43 -0500 (CDT) From: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: IMP/PHP IMAP webmail program 2.2.8 X-Originating-IP: 200.34.166.34 Hi This is postgresql 7.4 I am trying to check that postgres is updating a table. I have a pretty large ascii table file (+- 210 Mb) which I am copying into a table with pgsql, but it is taking a long time, and any select query I do to the table returns me 0 rows Is there any way to ask postgres to update the data more frequently or some way to make it end sooner? I am a bit clueless as to what can I do to the configuration files to optimize this copy. Thank you - www.correo.unam.mx UNAMonos Comunicándonos ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] FoxPro in WINE to Postgresql on LINUX?
Andrus wrote:
Can you use Postgres savepoints from VFP ?
sqlexec('ROLLBACK TO mysavepoint') and even sqlexec('ROLLBACK') cause C5
error.
I have no experience w/ savepoints yet. However, I will say that
sometimes ODBC will hijack your commands and alter them so you may want
to try turning off all possible options.
---(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] Function written in C, hangs on one machine and not another...
Thanks to the great suggestions I've at least gotten it to not hang... Martijn's hint about blocking led me to open up those filehandles in a non-blocking mode. It appears that write() will write, at a maximum, only 4096 bytes when it is called from within PostgreSQL. I've tried to push data into it in =4096-byte slugs, but after 4096 bytes it just won't take anymore. Since (I think) using a non-blocking mode could cause problems with thread safety, it's probably a lost cause. I'm new to C, so this may seem extremely naive: I'm not sure how to use exec() to solve this problem. Could you give me a few pointers to get me started? --- Douglas McNaught [EMAIL PROTECTED] wrote: Martijn van Oosterhout kleptog@svana.org writes: The things that have screwed me up in the past with pulling tricks like this are: 1. Program has registered atexit() handlers. _exit() avoids this. 2. Pending stdio output that gets flushed. The backend doesn't use stdio much so you might be fine here. 3. Signals. Make sure you don't get sent signals that screw state. Might be wise to block them all, or reset them all to default. Truly, exec() is the cleanest way to solve all this, it simply replaces the current process, lock, stock and barrel. Definitely. It would probably also be good to close all file descriptors (except for stdin/etdout/stderr) before exec(), just in case the other binary does something screwy with random file descriptors (which it obviously shouldn't). -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(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
[GENERAL] Frequency of Analyze?
I have a rapidly growing database with a very complex schema, and I'm looking to improve performance. It's typical to have 2-4 foreign keys in each table, and there are currently 113 tables, and queries with 5-10 tables with combined inner/outer joins are pretty typical. (I avoid subqueries anywhere I can) So far, my guiding philosophy has been constrain everything with primary keys, unique, foreign keys and the like, relying on the implicit indexes and the query scheduler to handle things, and so far, it's worked very well. The database has grown in total size (calculated by the size of a pg_dump) 25% in the past month, and the growth rate seems to be accellerating. (yikes!) We are buying new equipment now, but I'd still like to optimize as best as possible. A few questions: 1) Any problem with running Analyze hourly via cron? 2) Does Vacuum analyze also do the actions performed by Analyze? 3) What's the best way to handle indexes when only 1 index is used per table in a query, but there are several due to the various constraints on it? Is there a way to benefit from all of these other indexes somehow? Is there a definitive, detailed book for optimizing PG queries? -Ben -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function written in C, hangs on one machine and not another...
CG [EMAIL PROTECTED] writes: Thanks to the great suggestions I've at least gotten it to not hang... Martijn's hint about blocking led me to open up those filehandles in a non-blocking mode. It appears that write() will write, at a maximum, only 4096 bytes when it is called from within PostgreSQL. I've tried to push data into it in =4096-byte slugs, but after 4096 bytes it just won't take anymore. Since (I think) using a non-blocking mode could cause problems with thread safety, it's probably a lost cause. It's not a thread safety issue--it's more that non-blocking I/O is quite complicated to do properly. I'm new to C, so this may seem extremely naive: I'm not sure how to use exec() to solve this problem. Could you give me a few pointers to get me started? The basic scheme would be: * Write a standalone program that reads from stdin, does the processing/analysis using the Adobe library, and writes its results to stdout in some kind of easily-parseable format. * In your backend function, create two pipes (one for input and one for output), call fork(), close the appropriate file descriptors in the parent and child, dup2() the pipe descriptors in the child onto stdin and stdout, then: * In the child process, exec() your standalone program. * In the parent process, write all the data to the output pipe, then read the results back from the input pipe. (This can be problematic in the general case, but in yours it should be OK). * When the child process (your program) finishes, you'll get an EOF on the input descriptor in the parent processs. Close the input and output pipes and do whatever you're supposed to do with the data you read. If the above doesn't make sense to you, you need to read up on Unix programming. There are some good books on it, and I'm sure there's lots of stuff on the web. The fork()/exec() pattern is very standard stuff, but it's a little tricky to get at first. This is not going to be tremendously efficient, but given the crap library you have to deal with, it's the safest way. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] tsearch2 setweight
Hello List I' ve installed tsearch2 and is working perfectly but I get this thing, I want to index more than 4 fields in one tsvector so I think to use the setweight but it can only accept ABCD y need to have 5 letters or more Is there any way to change that? or changing the source code, but I want to know if this affects tsearch or postgres tnks!!! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2 setweight
On Fri, 28 Oct 2005, David Gama Rodr??guez wrote: Hello List I' ve installed tsearch2 and is working perfectly but I get this thing, I want to index more than 4 fields in one tsvector so I think to use the setweight but it can only accept ABCD y need to have 5 letters or more Is there any way to change that? or changing the source code, but I want to know if this affects tsearch or postgres currently, no. Do you really need more than 4 classes, not attributes (different attributes could belong to the same lexem class) ? In principle, it's possible to have more than 4 classes, but then you'll increase storage for tsvector. tnks!!! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Frequency of Analyze?
On Fri, Oct 28, 2005 at 01:47:49PM -0700, Benjamin Smith wrote: 1) Any problem with running Analyze hourly via cron? Probably not. Analyze usually only reads a small portion of the table. 2) Does Vacuum analyze also do the actions performed by Analyze? Yes, it does. 3) What's the best way to handle indexes when only 1 index is used per table in a query, but there are several due to the various constraints on it? Is there a way to benefit from all of these other indexes somehow? Is there a definitive, detailed book for optimizing PG queries? 8.1 can utilize multiple indexes per table. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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: [GENERAL] Map of Postgresql Users (OT)
FYI, here is a map of my PostgreSQL trips, using Google:
http://candle.pha.pa.us/main/writings/map.html
---
Claire McLister wrote:
I took a look at your map, this is great wonderful that you were
already thinking along these lines.
Looks like there are two issues here: (1) Getting the data points for
users/developers maps, and (2) using the most appropriate mapping
technology.
I think we can use the Zeemaps service (http://www.zeemaps.com) for
number (1) above. The way this works is that you create a map and set a
few passwords: (a) moderator, (b) member, and (c) viewer. Using the
moderator password, you can set certain map properties, e.g.,
attributes to maintain with each entry, color legends, etc. With a
member password you can modify entries and their attributes, and with a
viewer password you can just see details without the ability to modify
anything. A casual user can just see the map with the markers and their
names, without access to any details for the entries. Hence
distributing the member password to the group and having each person
add his or her details would be a good way to go. The number of points
for Postgresql users can, of course, go pretty high. So far, we have
seen reasonable performance with up to 500-600 markers. I don't know if
there is an easy way of partitioning the user base into segments that
can fit within this range. Another idea would be to just split the map
after say 500 points.
For the number (2) the choice seems to be between Google Maps vs.
Mapserver and its associated Open Source tools. Having looked at
Brent's follow up emails, it seems that Mapserver and associated
toolkits can give much more flexibility. Plus they have the advantage
that we are not limited by Google's terms, the most annoying of which
is that you cannot save, copy, or distribute the map images created.
So, it seems best to move towards the Open Source alternatives,
specially since they have better images as Brent points out. I've been
looking at some of the Mapserver toolkits, and there seems to be an
overwhelming amount of choice available for what to pick and not. So, I
don't feel qualified to pick the right technologies to use for the base
implementation. If someone can pick the mantle on that, I'd be happy to
work with that person to see how we can exchange information from the
map points stored from (1). If for the time being you feel comfortable
continuing with Google Maps, then there's multiple ways we can share
information developed by (1) through some kind of a web service API.
Claire
On Oct 25, 2005, at 2:33 PM, Robert Treat wrote:
Actually I have already made a basic google map for the website[1]
meant
to replace the old developers map. Check out the blog post at
http://people.planetpostgresql.org/xzilla/index.php?/archives/76-
Maptastic.html
I'd be interested in talking more about getting more content into the
map and getting it in officially; One concern I had was finding a way
to
include information that won't bog down end users with too much data.
(I've noticed large numbers of points tend to do that)
Robert Treat
On Tue, 2005-10-25 at 12:11, Claire McLister wrote:
Thanks.
I looked at the Mapserver maps, and at first glance it seems Google
Maps API provides better map images and more interactive features
(zooming, panning, JS popups, satellite/map views, etc.)
So, what would be the advantage of Mapserver apart from it being based
on Open Source?
We use Postgresql server to store the data. Currently, we do not use
PostGIS as we do not do much geographical queries. Over time we could
move in that direction to offer better geographical query support.
Posting this (or a more detailed map) on the Postgresql web site
sounds
like a great idea. We could even allow people to add markers for their
locations, and even enter more information about themselves to help
postgresql users connect with each other. One potential opportunity is
for users to quickly find consultants in their own area.
How would we initiate this activity?
Best wishes
Claire
On Oct 24, 2005, at 2:28 PM, Brent Wood wrote:
How about using Postgres/PostGIS UMN mapserver for a fully Open
Source
software Postgres based map?
For a map like the one at
http://www.qgis.org/index.php?option=com_wrapperItemid=53
perhaps on the Postgres web site?
---(end of
broadcast)---
TIP 6: explain analyze is your friend
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of
broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if
[GENERAL] problems after pg_resetxlog
Hi, i had a server crash and after that , postgres refused to start, i read on the list that pg_resetxlog could help me, indeed, after i run pg_resetxlog dir_data, postgres started again, but i look a lot of these errors: CONTEXT: writing block 1 of relation 1663/17231/69118230 WARNING: could not write block 1 of 1663/17231/69118230 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 98/E3E83848 is not satisfied --- flushed only to 98/C364344 CONTEXT: writing block 1 of relation 1663/17231/69118230 WARNING: could not write block 1 of 1663/17231/69118230 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 98/E3E83848 is not satisfied --- flushed only to 98/C368658 CONTEXT: writing block 1 of relation 1663/17231/69118230 WARNING: could not write block 1 of 1663/17231/69118230 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 98/E3E83848 is not satisfied --- flushed only to 98/C3717F4 CONTEXT: writing block 1 of relation 1663/17231/69118230 WARNING: could not write block 1 of 1663/17231/69118230 DETAIL: Multiple failures --- write error may be permanent. ERROR: xlog flush request 98/E3E83848 is not satisfied --- flushed only to 98/C38554C CONTEXT: writing block 1 of relation 1663/17231/69118230 WARNING: could not write block 1 of 1663/17231/69118230 DETAIL: Multiple failures --- write error may be permanent. and pg_dump fails with: pg_dump radius radius.out pg_dump: ERROR: xlog flush request 98/D7CFEE64 is not satisfied --- flushed only to 98/CA37000 CONTEXT: writing block 3683 of relation 1663/17231/69127319 pg_dump: SQL command to dump the contents of table starttelephony failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: xlog flush request 98/D7CFEE64 is not satisfied --- flushed only to 98/CA37000 CONTEXT: writing block 3683 of relation 1663/17231/69127319 pg_dump: The command was: COPY public.starttelephony (radacctid, username, realm, nasipaddress, acctstarttime, calledstationid, callingstationid, acctdelaytime, h323gwid, h323callorigin, h323calltype, h323setuptime, h323confid) TO stdout; what can i do? thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problems after pg_resetxlog
Miguel [EMAIL PROTECTED] writes: ERROR: xlog flush request 98/E3E83848 is not satisfied --- flushed only to 98/C364344 what can i do? Increasing the WAL start address might help (see the pg_resetxlog man page). Realize that you're probably going to be dealing with corrupted data, however :-( regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Installation trouble
I'm having difficulty installing 8.0.4. Server is SuSE 8.2 without PG installed. However some client libraries are Yast installed due to dependency reconciliation. I'm doing the standard install (./configure, make, make install) and have created the postgres user and appropriate data directory but when I run: initdb /var/lib/pgsql/data I get: initdb: file /usr/share/postgres.bki does not exist This means you have a corrupted installation or identified the wrong directory with the invocation option -L. Running: initdb -L /usr/local/pgsql/share/ /var/lib/pgsql/data Looks pretty good at first but fails at: creating conversions ... FATAL: could not access file $libdir/ascii_and_mic: No such file or directory Reports from pg_config: --libdir: /usr/lib --bindir: /usr/bin --configure: -blank- --includedir-server: /usr/include/server --includedir: /usr/include --pgxs: /usr/lib/pgxs/src/makefiles/pgxs.mk --pkglibdir: /usr/lib Note, the directories shown for libdir, bindir, includedir and such are not where the files were actually installed. Now I could just start shuffling files around till things work but since I've installed/upgraded many PG installations without this difficulty I suspect there is something basic that I'm missing that is causing all of the trouble. Suggestions? Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problems after pg_resetxlog
Tom Lane wrote: Increasing the WAL start address might help (see the pg_resetxlog man page). Realize that you're probably going to be dealing with corrupted data, however :-( regards, tom lane Yeah, i know, but i only want to be able to finish the dump, i must execute the pg_resetxlog again with the -l flag? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installation trouble
Steve Crawford [EMAIL PROTECTED] writes: Note, the directories shown for libdir, bindir, includedir and such are not where the files were actually installed. Now I could just start shuffling files around till things work but since I've installed/upgraded many PG installations without this difficulty I suspect there is something basic that I'm missing that is causing all of the trouble. Suggestions? I think you have the wrong binaries in your PATH. Set thing up so /usr/local/pgsql/bin comes before the other stuff. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] problems after pg_resetxlog
Miguel wrote: Tom Lane wrote: Increasing the WAL start address might help (see the pg_resetxlog man page). Realize that you're probably going to be dealing with corrupted data, however :-( regards, tom lane Ops, foget the last email, i reread the man, this is what i have to do: shiva2 pg_xlog # ls -l total 180448 -rw--- 1 postgres postgres 16777216 Oct 28 18:05 00010098001A -rw--- 1 postgres postgres 16777216 Oct 28 18:07 00010098001B -rw--- 1 postgres postgres 16777216 Oct 28 18:10 00010098001C -rw--- 1 postgres postgres 16777216 Oct 28 18:10 00010098001D -rw--- 1 postgres postgres 16777216 Oct 28 17:25 00010098001E -rw--- 1 postgres postgres 16777216 Oct 28 17:28 00010098001F -rw--- 1 postgres postgres 16777216 Oct 28 17:37 000100980020 -rw--- 1 postgres postgres 16777216 Oct 28 17:51 000100980021 -rw--- 1 postgres postgres 16777216 Oct 28 17:53 000100980022 -rw--- 1 postgres postgres 16777216 Oct 28 17:46 000100980023 -rw--- 1 postgres postgres 16777216 Oct 28 17:49 000100980024 drwx-- 2 postgres postgres 4096 Apr 22 2005 archive_status So : pg_resetxlog -l 0x01, 0x98, 0x25 dir_data Is this ok? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] newbie question: reading sql commands from script
Thanks to all who answered this question! From: Brent Wood [EMAIL PROTECTED] To: basel novo [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] newbie question: reading sql commands from script Date: Wed, 26 Oct 2005 10:36:34 +1300 (NZDT) On Tue, 25 Oct 2005, basel novo wrote: What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? I have not used mysql, so am not familiar with the source command, but to have postgres run a set of sql statements/queries from a file you can: psql DB -f filename also, to run a single command from a script (or commandline) psql DB -c sql command Cheers, Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] mysql replace in postgreSQL?
In MySQL, I can use the replace statement which either updates the data there or inserts it. Is there a comporable syntax to use in postgreSQL? I need to do an insert and don't want to have to worry about if the data is already there or not...so don't want to see if it there, if so do update if not insert...etc. Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Backup problem...No BIN directory!!!
Ok, so I decide to migrate from postgresql 8.0 to 8.0.4 and, as you do, go to backup my database. On trying to navigate to my bin directory I get an error message saying the directory doesn't exist. So I check the pgsql folder and true enough the directory doesn't exist. So how in the good Lords name can I backup my database in this instance? HELP!!! Uzo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] mysql replace in postgreSQL?
blackwater dev wrote: In MySQL, I can use the replace statement which either updates the data there or inserts it. Is there a comporable syntax to use in postgreSQL? I need to do an insert and don't want to have to worry about if the data is already there or not...so don't want to see if it there, if so do update if not insert...etc. Use a trigger. Sincerely, Joshua D. Drake Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installation trouble
Steve Crawford wrote: I'm having difficulty installing 8.0.4. Server is SuSE 8.2 without PG installed. However some client libraries are Yast installed due to dependency reconciliation. I'm doing the standard install (./configure, make, make install) and have created the postgres user and appropriate data directory but when I run: The below looks to me like you have more then one version installed... try: initdb /var/lib/pgsql/data /usr/local/pgsql/bin/initdb /var/lib/pgsql/data Sincerely, Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Installation trouble -- oops
Steve Crawford wrote: I'm having difficulty installing 8.0.4. Server is SuSE 8.2 without PG installed. However some client libraries are Yast installed due to dependency reconciliation. I'm doing the standard install (./configure, make, make install) and have created the postgres user and appropriate data directory but when I run: Sorry I hit send before I was ready. If you used the exact command above then everything will be in /usr/local/pgsql ... Which means from the below it looks like you have more then one install of PostgreSQL. Try: /usr/local/pgsql/bin/initdb --no-locale -D /var/lib/pgsql/data Sincerely, Joshua D. Drake initdb /var/lib/pgsql/data I get: initdb: file /usr/share/postgres.bki does not exist This means you have a corrupted installation or identified the wrong directory with the invocation option -L. Running: initdb -L /usr/local/pgsql/share/ /var/lib/pgsql/data Looks pretty good at first but fails at: creating conversions ... FATAL: could not access file $libdir/ascii_and_mic: No such file or directory Reports from pg_config: --libdir: /usr/lib --bindir: /usr/bin --configure: -blank- --includedir-server: /usr/include/server --includedir: /usr/include --pgxs: /usr/lib/pgxs/src/makefiles/pgxs.mk --pkglibdir: /usr/lib Note, the directories shown for libdir, bindir, includedir and such are not where the files were actually installed. Now I could just start shuffling files around till things work but since I've installed/upgraded many PG installations without this difficulty I suspect there is something basic that I'm missing that is causing all of the trouble. Suggestions? Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.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
Re: [GENERAL] mysql replace in postgreSQL?
blackwater dev wrote: In MySQL, I can use the replace statement which either updates the data there or inserts it. Is there a comporable syntax to use in postgreSQL? I need to do an insert and don't want to have to worry about if the data is already there or not...so don't want to see if it there, if so do update if not insert...etc. Thanks. In Oracle this is called the MERGE statement, but it not yet in pg. It is on the TODO list, though. ---(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] Why database is corrupted after re-booting
The transistor has existed in homes now for 30 years. That means new homes should be built to withstand direct lightning strikes without damage. Such earthing is not difficult. But it requires the builder to plan for the lightning protection 'system' before the footing are poured. It is an old and well proven technology - called Ufer grounds. They are installed in the footing using materials already inside footings. IOW significant and effective protection systems for residential environments need not be expensive. It simply requires planning by the builders - who currently don't consider such 'systems' until much later - when the electrician arrives. Also essential is that all utilities enter as the same location - the service entrance - so that all make a 'less than 10 foot' connection to that single point and most superior earth ground. Ufer grounds, halo grounds, and other simple techniques cost so little when installed during construction. And yet we still build new homes as if the transistor did not exist. Ron Mayer wrote: Indeed. And yes, a high end data center should survive a lightning strike (as well as hospital's power systems, etc). Here's a nice article where Suncoast Schools Federal Credit Union's data center survived a direct lightning strike to their 480-V service entrance cable. The article spends a lot of the time talking about the grounding system. http://www.ecpzone.com/article/article.jsp?siteSection=12id=41 Starting from the ground up, the main elements of the [lightning protection] system...include: (1) Three 20-ft x 5/8-in (6-m x 16-mm) copper-clad-steel grounding electrodes [...] The grounding system's resistance to earth as measured by fall-of-potential testing is 4.3 ohms. (2) Another 4/0 copper grounding conductor connects the ground-neutral bus in the service entrance panel to the ground bus in a 480-V distribution panel ... (3) Multiple uninterruptible power supplies (UPSs) (4) Up to seven layers of voltage surge protection High Quality Grounding even the most expensive TVSS you can buy is absolutely useless unless it sees a high-quality, low-resistance ground. ---(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
