Re: [GENERAL] How to enumerate/drop user sessions in windows
Hi, On 31.01.2006 10:33, Howard Cole wrote: Now when the Database server is running on a linux server, I can run ps and pick the sessions that I want to kill. However how can I do this on windows? Is there a psql command to kill sessions? As this is a bit hard to accomplish with Windows' own task manager, get SysInternals free Process Explorer from here http://www.sysinternals.com/Utilities/ProcessExplorer.html and after starting it, use Find - Find Handle (CTRL + F) and search for postgres:. This will give you a similar output like ps aux|grep postgres: on Linux. If you then select on of the shown processes it will select the right process in the main window, where you can right click and select Kill process (DEL). Hope that helps. Regards, Oliver ---(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] Are rules transaction safe?
Hi all,
I have a question regarding rules on views. Are the commands inside a ON
INSERT (or UPDATE) DO INSTEAD (implicit) transactions for postgres? I
tried to put BEGIN; and COMMIT; around the commands in a ON ... DO
INSTEAD ( ) block, but keep getting a syntax error.
The following example might explain what I'm trying to find out:
-
Two tables, a and b. b is referencing a via a_id...
CREATE TABLE a
(
id serial,
foo varchar(255),
CONSTRAINT aid PRIMARY KEY (id)
);
CREATE TABLE b
(
id serial,
a_id int4 not null,
foo varchar(255),
CONSTRAINT bid PRIMARY KEY (id),
CONSTRAINT bfk FOREIGN KEY (a_id)
REFERENCES a (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
...a view ab for a combination of the before mentioned tables...
CREATE OR REPLACE VIEW ab AS
SELECT a.id AS main_id, a.foo AS from_a, b.foo AS from_b
FROM a, b
WHERE a.id = b.a_id;
...and a rule ab_insert...
CREATE OR REPLACE RULE ab_insert AS ON INSERT TO ab DO INSTEAD (
INSERT INTO a (foo) VALUES (new.from_a);
INSERT INTO b (foo,a_id) VALUES (new.from_b,currval('a_id_seq'));
);
-
As you can see, the ab_insert rule inserts into a first and than
takes the current value of a.id's sequence to set the reference a_id
in b.
Can I assume that this will always work as expected or is it possible
that in a multi-user scenario two or more concurrent inserts on the view
will lead to undesirable results?
As mentioned in the beginning, putting BEGIN; and COMMIT; didn't work.
Is this kind of creating a relation between two or more tables and
relying on a sequence generally a good practice?
Regards,
Oliver
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Are rules transaction safe?
Hi Doug,
On 27.01.2006 21:01, Doug McNaught wrote:
Oliver Fürst [EMAIL PROTECTED] writes:
I have a question regarding rules on views. Are the commands inside a
ON INSERT (or UPDATE) DO INSTEAD (implicit) transactions for postgres?
I tried to put BEGIN; and COMMIT; around the commands in a ON ... DO
INSTEAD ( ) block, but keep getting a syntax error.
Everything that happens in Postgres is inside either an implicit or
explicit transaction, so you can't do BEGIN/COMMIT inside rules or
functions.
You might be able to use savepoints, depending on what you're actually
trying to do.
Actually I'm just worried that something like the ON INSERT ... DO
INSTEAD rule on a view (as stated in my example)...
CREATE OR REPLACE RULE ab_insert AS ON INSERT TO ab DO INSTEAD (
INSERT INTO a (foo) VALUES (new.from_a);
INSERT INTO b (foo,a_id) VALUES (new.from_b,currval('a_id_seq'));
);
...could yield unexpected results. Imagine two overlapping inserts on
the view in a heavy load situation. (C1, C2 - two concurrent connections):
C1 - INSERT INTO ab (from_a,from_b) VALUES ('foo','bar');
C2 - INSERT INTO ab (from_a,from_b) VALUES ('hello','world');
...should translates to...
C1 - INSERT INTO a (foo) VALUES ('foo'); -- id == 1
C1 - INSERT INTO b (foo,a_id) VALUES ('bar',1);
C2 - INSERT INTO a (foo) VALUES ('hello'); -- id == 2
C2 - INSERT INTO b (foo,a_id) VALUES ('world',2);
...but could translate to...
C1 - INSERT INTO a (foo) VALUES ('foo'); -- id == 1
C2 - INSERT INTO a (foo) VALUES ('hello'); -- id == 2
C1 - INSERT INTO b (foo,a_id) VALUES ('bar',2);
C2 - INSERT INTO b (foo,a_id) VALUES ('world',2);
Basically I'm worried that the whole relying on the last value of a
sequence isn't such a great idea.
(By the way, did I pick the wrong mailing list for that topic and should
I move to pgsql-sql?)
Regards,
Oliver
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Are rules transaction safe?
Hi Doug, thanks for your answers so far. I think I should try to discuss that matter in [pgsql-sql] instead. On 27.01.2006 21:21, Doug McNaught wrote: Oliver Fürst [EMAIL PROTECTED] writes: Basically I'm worried that the whole relying on the last value of a sequence isn't such a great idea. 'currval()' is specifically written to Do The Right Thing. See the docs. My problem isn't answered in the manual, otherwise I wouldn't have asked. I know that sequences are working correct for transactions (either implicit or explicit). But is nowhere stated if (multiple) commands inside a rule are treated as an implicit transaction as a whole. Regards, Oliver ---(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
