[GENERAL] VACUUMing in general
Is any work being done to streamline/redesign the way PostgreSQL handles updates and/or the way VACUUM works so that it doesn't lock the entire table while it does it's job? It seems that tables that need to be VACUUM'ed the most would typically have fewer acceptable "windows" in which to work.
[GENERAL] Replication options in Postgres
I am setting up a system that processes transactions, and it needs to be highly reliable. Once a transaction happens, it can never be lost. This means that there needs to be real-time off-site replication of data. I'm wondering what's the best way to do this. One thing that might simplify this system is that I _never_ use UPDATE or DELETE. The only thing I ever do with the database is INSERT. So this might make replication a little easier. I think I have a few possibilities: 1. In my PHP code, I have functions like inserttransaction(values...). I could just modify inserttransaction() so that it runs the same query (the INSERT) on two or more DB servers. This would probably work ok. 2. I could write triggers for all my tables, so that when there is an INSERT, the trigger does the same INSERT on the other server. Any ideas for an efficient way to do this? 3. Any other tricks? I don't need mirroring. There will be one master and one or more slaves, and the only thing the slaves will do is store backup data. The most important thing is that I can't lose a single transaction. Thanks, e
[GENERAL] PG vs. Oracle for larger databases
How suitable is PG for doing larger databases? The need I am considering would be a financial database that does maybe up to 100k transactions/day. Obviously, it needs to be very reliable, and have minimal scheduled, and no unscheduled downtime. Should this project be on Oracle or Postgres? thankks, e
[GENERAL] From timestamp to seconds since epoch?
I've read through all the documentation, and I can't find a function
that takes a timestamp and converts iit to seconds since epoch.
Here's what I need to do: I have pairs of timestamps ('2000-07-12
03:33:53+00'), and I want to find out how many seconds are in the
interval between them. I know I can do
select timestamp1 - timestamp2
and I get a result in days, hours, mins, and seconds, but is there a
way to get that whole thing in just seconds? This has to be pretty
efficient, because it's going to get called a lot.
Thanks,
e
[GENERAL] Figured it out (psql and Gnu readline)
With some helpful hints from various people, I figured out how to get this to work, and now my life is more pleasant and meaningful. Situation: Stock installation of OpenBSD 2.7, with PostgreSQL 7.0.2. I installed postgres by downloading the source tar and compiling, not by going through /usr/ports. Anyway, I'm not sure if OpenBSD ships with readline or whatever, so I downloaded the latest readline (4.1) and compiled and installed it. I then did ./configure in postgres, and recompiled psql, and it still didn't have history. Following some sugestions on this group, I figured out the problem. First, there is no history.h with readline 4.1. There's readline/history.h. So I modified Makefile.global to include that file. Also, I had to edit config.h, with several tweaks to various defines. I had to tell it that there is no history.h, but there is a readline/history.h. I had to put in that there is no history function in libreadline, and it must also use libhistory. Etc. After doing all that, it worked beautifully. I often have to do a sequence of slighly varied inserts, etc, and this is much better. Maybe for the next release, there should be better handling of gnu readline 4.1? Or maybe it should come with the package? Just a thought. e -- This message was my two cents worth. Please deposit two cents into my e-gold account by following this link: http://rootworks.com/twocentsworth.cgi?102861 275A B627 1826 D627 ED35 B8DF 7DDE 4428 0F5C 4454
Re: [GENERAL] Figured it out (psql and Gnu readline)
configure is supposed to handle all that for you ... if it failed to find the right location of libreadline and associated includes then the question is why. You sure you ran configure after installing the include files? I just deleted my postgres tree, and then unpacked it again, and ran configure again. libreadline.a and libhistory.a are in /usr/local/lib/. readline.h and history.h are in /usr/local/include/readline/. I then ran ./configure. When I look at config.h, I can see that it failed to detect /usr/local/include/readline/history.h. Also, I see that it did define HAVE_HISTORY_IN_READLINE 1 which is incorrect. I would have to make the change by hand to the file. I think it may be a bug. e
[GENERAL] How to get PL languages to work in Postgres
I'm sure this is a dumb question, but... When I try to create a PL/pgSQL function, even a very simple one like the add_one function in the example, I get this error: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. Do you have any idea how to get it to work? I just compiled and installed Postgres in the normal way. I can see that there is a plpgsql.so in /usr/local/pgsql/lib. Have I missed an installation option? Anything else I should do? Thanks, e
[GENERAL] References on functions?
I'm starting to write functions in plpgsql, and I can do the basic stuff just fine, but the language isn't so well documented. Is there any good tutorial on the language? Or should I switch over to perl? I would rather learn plpgsql than do it in perl, because I would guess that plpgsql is faster and more optimized for working in Postgres. Any source of better docs? Thanks, e -- This message was my two cents worth. Please deposit two cents into my e-gold account by following this link: http://rootworks.com/twocentsworth.cgi?102861 275A B627 1826 D627 ED35 B8DF 7DDE 4428 0F5C 4454
Re: [GENERAL] International Address Format Standard
International addresses can be very strange. To send a package to someone in Anguilla, you address it like this: John Doe 344 444 The Valley, Anguilla where 344 444 is the guy's phone number. In Laos, an address is like this: John Doe Near Wat Luang Prabang, Laos where Wat is the name of the nearest temple. e -- This message was my two cents worth. Please deposit two cents into my e-gold account by following this link: http://rootworks.com/twocentsworth.cgi?102861 275A B627 1826 D627 ED35 B8DF 7DDE 4428 0F5C 4454
