[SQL] Mutex via database
Hi folks, i've a dozen of servers processing jobs from a database. Some servers may only run exactly once, otherwise I'll get jobs done twice. Is there any way for implementing an kind of mutex within the database, which is automatically released if the holding process/connection dies ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(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
Re: [SQL] Mutex via database
Hi, Enrico, Enrico Weigelt wrote: > i've a dozen of servers processing jobs from a database. > Some servers may only run exactly once, otherwise I'll get jobs > done twice. > > Is there any way for implementing an kind of mutex within the > database, which is automatically released if the holding > process/connection dies ? Use the "LOCK" sql command, possibly together with transaction isolation set to serializable. Markus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] all server processes terminated; reinitializing
Estou usando PostgreSQL 8.0.2 instalado no linux Fedora FC2. Esta é a messagem gravada no meu "serverlog": WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2005-05-04 11:39:53 BRT LOG: checkpoint record is at 0/6CE514D8 LOG: redo record is at 0/6CE514D8; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1229401; next OID: 2226116 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/6CE51514 LOG: unexpected pageaddr 0/65E5E000 in log file 0, segment 108, offset 15065088 LOG: redo done at 0/6CE5DD24 LOG: database system is ready Como devo proceder?
Re: [SQL] all server processes terminated; reinitializing
Hallo, 2000 Informatica, 2000 Informatica wrote: > Estou usando PostgreSQL 8.0.2 instalado no linux Fedora FC2. > > Esta é a messagem gravada no meu "serverlog": > > WARNING: terminating connection because of crash of another server process > > DETAIL: The postmaster has commanded this server process to roll back > the current transaction and exit, because another server process exited > abnormally and possibly corrupted shared memory. Diese Fehlermeldungen bedeuten, dass ein anderer Prozess des Servers (also eine andere Verbindung) als die, die diese Logmeldungen geschrieben hat, abgestürzt ist. PostgreSQL startet in so einem Fall sicherheitshalber alle Backends neu. Hoffe, das Hilft, Schabi ---(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
Re: [SQL] [ANNOUNCE] pgtop, display PostgreSQL processes in `top' style
On May 2, 2005, at 6:09 PM, Markus Schaber wrote: 1) is it possible to know Pg backend uptime with SQL queries? Or must I look at postmaster.pid file? or even something else? In contrib, there's a function caled backend_pid() defined in misc_utils.sql, it may be helpful for you. markus I wrote a small util called "pgtop" that does a top style listing on PG in terms of cpu. I also have one called pgiomonitor (that is on pgfoundry and mostly done) that does the same but shows you which tables are producing the most IO) http://postgresql.jefftrout.com/pgtop.pl it only runs on linux and it must run on the same box as pg itself. You may want to look at it and see if you can get anything good out of it. queries / second is tricky. You could look at my pgspy utility but it is in C. It can give you queries / second data -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] HELP: aggregating insert rule for multirow inserts.
I'm having a problem with the use of the NEW rowset,
in a rule intended to aggregate across inserts.
I've never really grokked how NEW and OLD really work,
syntactically, other than that they seem to be implicit
in every top-level FROM clause, and any mention elsewhere
gets an error: '42P01: relation "*NEW*" does not exist'.
I've tried different flavours of the UPDATE command,
in the following rule, and they either produce syntax errors
or the wrong results.
Any suggestions much appreciated ...
== CODE
"How many critters are in the zoo, of the 4,5,6...-legged varieties?"
create table critter(name text, legs int);
create table zoostats(legs int, headcount int default 0,
primary key(legs));
create or replace rule critter_counter as
on INSERT to critter do (
insert into zoostats
select distinct new.legs
where new.legs not in (select legs from zoostats);
update zoostats
setheadcount = headcount + (select count(*)) -- "from new"
where new.legs = zoostats.legs
);
insert into critter values('cat',4);
insert into critter values('starfish',5);
insert into critter values('ant',6);
insert into critter values('dog',4);
insert into critter select * from critter; -- double everything.
select * from zoostats;
drop table zoostats cascade;
drop table critter;
== EXPECTED OUTPUT
legs headcount
-
4 4
5 2
6 2
== ACTUAL OUTPUT
legs headcount
-
4 3 -- !?
5 2
6 2
== OTHER ATTEMPT:
This version of the update looks syntactically right to me,
but makes CREATE RULE fail on a syntax error:
...
update zoostats
set headcount = headcount + tally
from (select new.legs, count(new.legs) as tally -- from new !?
group by new.legs) as poll
where poll.legs = zoostats.legs;
ERROR: 'Subquery in FROM may not refer to other relations
of same query level'.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.
---(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
[SQL] accessing multiple database
hi all, im finding some ideas, method, advice, n etc on accessing multiple database.The system that i'm currently develop is real time update and the data reside on few different db at different db site. Tq in advance. Tq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [despammed] [SQL] accessing multiple database
am 05.05.2005, um 10:47:12 +0800 mailte mohammad izwan ibrahim folgendes: > hi all, > > im finding some ideas, method, advice, n etc on accessing multiple > database.The system that i'm currently develop is real time update and > the data reside on few different db at different db site. Tq in > advance. 1. write your own client thats connect to several databases 2. use dblink, this is part of the contrib-distribution Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
