Re: [GENERAL] Plans for 8.2?
Jeff Trout skrev: Built In Failover/Clustering This won't happen. The community stance, which is a good one is that no single replication solutions fits everyone's needs and therefore we rely out the outside sources. Slony-I, Mammoth Replicator and pgpool being the most popular. Too bad - I think that will keep a lot of potential users from evaluating Pg as a serious alternative. Good or bad, decide for yourself :) Isn't the [expensive db name here]'s replication/failover just an expensive addon? As in if you don't pay for it you don't get it. So we're basically in the same boat as them.. just an add on. we just offer more variety. Not really. The available options for postgresql are simply not as good as what the big databases offer. For some problems the non-transaction master/slave Slony-I is good enough. But to claim it is good enough for all, is like when MySQL claimed nobody really needs transactions. I am a big postgresql fan, and I have several production clusters using DRBD to replicate postgresql databases in an active/failover configuration. But some day I am going to need a cluster that can do active/active, and that day I will be forced to adopt a different database. I will also point out that none of the replication solutions have the same solid reputation as postgresql. As long the postgresql team will not endorse a replication solution, you can not expect people to put the same trust in these solutions as we put into postgresql itself. Oracle do endorse their own replication solution after all. Baldur ---(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] random delays
Hi, I noticed that sometimes we seem to have a problem with simple queries that take a long time to execute. For example select * from config where key='abc' which normally only takes a few milliseconds, but in some rare cases suddenly takes 10-30 seconds. It is a production system, so there are always other queries going on. But the server is not neccessarly loaded when this happens. I can't think of any reasons that there should be a lock on the tables in question. Is there a way to find out what the query is waiting for? We are using postgresql 8.0.3. Thanks, Baldur ---(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] postgresql start/stop/status script
Hi,
I am working on making a postgresql/drbd/heartbeat high availability
cluster. I need a script for heartbeat to start, stop and query the
service. I wrote the following:
pgStart() {
su - pg0 -c cd data ; /mnt/data0/postgresql/bin/pg_ctl start -D
/mnt/data0/postgresql/data -w -o '-i -h 192.168.2.50'
}
pgStop () {
su - pg0 -c cd data ; /mnt/data$user/postgresql/bin/pg_ctl stop
-D /mnt/data0/postgresql/data -m fast -w
}
pgStatus () {
if su - pg0 -c cd data ; /mnt/data0/postgresql/bin/pg_ctl
status -D /mnt/data0/postgresql/data | grep -q postmaster is running
then
echo running
else
echo stopped
fi
}
This works fine. The only problem is that status - it seems to only
check for the existance of the PID file. If the file is there, it
assumes that postgresql is running. In the case of a failover, the PID
file will of course still be there, but it will be stale. The effect is
that heartbeat never starts postgresql because my pgStatus claims it is
already running, even though it is not.
Is there a better way to query the status of postgresql? I would expect
it to at least check that the process in the PID is actually running and
that it is a postgresql process.
I am also confused by the need to specify -h 192.168.2.50 - that is
already in the postgres.conf file, but pg_ctl start ignores it.
Thanks,
Baldur
---(end of broadcast)---
TIP 3: 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] on cascade set null works on not null columns
Hi,
I just noticed that I could do this:
webshop=# create table foo (bar text not null primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for
table foo
CREATE TABLE
webshop=# create table foo2 (bar text not null, foreign key (bar) references
foo(bar) on update cascade on delete set null);
CREATE TABLE
webshop=# insert into foo values ('a');
INSERT 6644065 1
webshop=# insert into foo2 values ('a');
INSERT 6644066 1
webshop=# delete from foo;
ERROR: null value in column bar violates not-null constraint
I would have expected the second create table to fail. This didn't allow me to
violate constraints, but it made the error message unintuitive. You get no
clues to which table is actually preventing me from deleting from 'foo'. This
is in contrast to if I use no action:
webshop=# drop table foo2;
DROP TABLE
webshop=# create table foo2 (bar text not null, foreign key (bar) references
foo(bar) on update cascade on delete no action);
CREATE TABLE
webshop=# insert into foo2 values ('a');
INSERT 6644189 1
webshop=# delete from foo;
ERROR: update or delete on foo violates foreign key constraint $1 on
foo2
DETAIL: Key (bar)=(a) is still referenced from table foo2.
This time I get a useful error message.
Baldur
This message was sent using IMP, the Internet Messaging Program.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] why the need for is null?
Quoting Guy Fraser [EMAIL PROTECTED]: There already is an operator, and it is the ANSI SQL operator IS. Just because IS does not use puctuation characters does not mean it is not an operator. IS is not an operator in postgresql, at least not in the same sense that = is an operator. You can not do \do is while \do = works fine. select coalesce(string_column,'') from some_table ; Will postgresql still make effective use of the indexes if I use a function like coalesce on the column before comparing it? Even if it does, the method I already use is more effective. If you still don't understand, then use MySQL it is messed up and allows weird things like most of what you want to do. 1) I understand the issues involved perfectly. I just happens to have a table where it would be usefull that NULL=NULL is true. It is not so, and therefore I have to use a syntax that is hard to read and I have been made to understand that I will have to accept that. Fine. 2) What kind of crap is that flaming me like this? Do all users that ask a question about why postgresql or the sql standard implements a feature in a specific way, end up being told to switch to mysql? 3) Mysql knows how to compare nulls: mysql select null=null; +---+ | null=null | +---+ | NULL | +---+ 1 row in set (0.01 sec) mysql select null is null; +--+ | null is null | +--+ |1 | +--+ 1 row in set (0.00 sec) Baldur This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] why the need for is null?
Quoting Martijn van Oosterhout [EMAIL PROTECTED]: Annoying, not really. It's actually extremely useful. It's useful having a value which is never equal to anything else, not even itself. If you use it to represent unknown it will work for you. If you try to use it for anything else, it will bite you. I need it to represent empty because the field in question is a foreign key to another table. If it represented unknown the foreign key should block it as it could not possible know if that unknown value was valid. But I can't argue against the SQL standard of course. You could create a new operator, but that means you'll have difficulty moving it to any database that doesn't have that operator (which is most of them). Any commercial database vendor would be happy to make such a feature just for that reason: to lock me in to their database :-). I do not try to stay database neutral, and use lots of other features that will only work in postgresql. If you want it to match perhaps you should forget NULL and use '' (zero length string) instead. Then I need to have a meaningless entry in the foreign table, and fill my code with special cases that filter out that fake entry before showing the data to the user. Besides who said I didn't want to allow the empty string as valid data? This would be even more an issue if the field was a nummeric, where any nummeric value is ok. If I can not use NULL to represent empty or not applicateable I would have to make a special field that tells me if I should ignore the previous field or not. Does not sound reasonable when NULL works fine for just that. The best compromise I found so far is this X=Y or X is null and Y is null construct. Just looks hard to understand and cumpersome for someone which is not expert on this issue. Baldur This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] duplicate primary key entries?
Hi, I just noticed something bad in our database: webshop=# select oid,* from content_loc where id=20488; oid | id | locale | name -+---++-- 9781056 | 20488 | any| Rise Part II 9781058 | 20488 | any| Rise Part II (2 rows) webshop=# \d content_loc Table public.content_loc Column | Type | Modifiers +-+--- id | integer | not null locale | text| not null name | text| Indexes: content_loc_pkey primary key btree (id, locale) Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES content(id) ON UPDATE CASCADE ON DELETE CASCADE, $2 FOREIGN KEY (locale) REFERENCES languages(locale) ON UPDATE CASCADE ON DELETE CASCADE Apparently there are two rows with identical primary keys which should not be possible. Is this a know problem? Can I expect everything to be ok if I just delete the extra entry? I am using the debian packages of postgresql 7.3.4-9. Thanks, Baldur This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
