Following tests were made in linux server running pg 7.4 beta 2.

I have been playing with temporary tables a little bit and noticed some
interesting things.  I'm not sure if this is a part of the standard
canon or not but I thought it worth mentioning.  Sorry if I'm bleating
out the obvious!

According to the docs, all temporary tables are local and scoped to the
current backend.  AFAICT, this principle can be violated in two ways,
one expected and one not.  The first and obvious way is to make a query
vs. pg_class and you can see temporary tables from other users.  This is
expected and IMO a useful property of temporary tables.

The other and more interesting way is to manually jump into the
temporary schema (eg. pg_temp_x) that hosts a temp table constructed by
another backend.  While this is unlikely to happen in a normal setting,
the server does allow it.  Following this, backend X can both see and
manipulate a temporary table set up by backend Y, including table schema
manipulation. 

Both backends can insert records into the table but each can only see
the records they inserted.  However, changes to table structure (e.g.
alter table) are visible to both backends.  Interestingly, if backend X
does a drop column on a temp table, this shows up as ..pg.droppped.1...
in a psql \d on backend Y.  Following this, a count(*) from backend Y
counts the records from backend X even though the records are not
visible.

Also, Is the prohibition of using schemas for temp tables a SQL spec
requirement or a technical consideration?


Regards,
Merlin

Tue, 16 Sep 2003 14:37:32 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
observations about temporary tables and schemas
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> The other and more interesting way is to manually jump into the
> temporary schema (eg. pg_temp_x) that hosts a temp table constructed by
> another backend.  While this is unlikely to happen in a normal setting,
> the server does allow it.  Following this, backend X can both see and
> manipulate a temporary table set up by backend Y, including table schema
> manipulation. 

Not unless backend X is run by a superuser.  There are privilege
restrictions against this for ordinary users.  The other behaviors you
note are consequences of the current implementation and might change
in future.

> Also, Is the prohibition of using schemas for temp tables a SQL spec
> requirement or a technical consideration?

The SQL spec's notion of temp tables is only tenuously related to ours
in the first place :-(.  However, the spec appears to require that
references to temp tables be unqualified names, so AFAICT it's not
expected that applications can reference more than one schema's worth
of temp tables anyway.

			regards, tom lane

Tue, 16 Sep 2003 14:55:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: observations about temporary tables and schemas 


On Tue, 16 Sep 2003, Merlin Moncure wrote:

> I have been playing with temporary tables a little bit and noticed some
> interesting things.

Something else I've noticed about temp tables is that you are prohibited
from having a permanent table contain a foreign key reference to a temp
table, but you are allowed to reference a permanent table from a temp
table.  The triggers don't work correctly when the table is
modified by another backend:

Backend 1:
CREATE TABLE t1(a int PRIMARY KEY);
CREATE TEMP TABLE t2(a int REFERENCES t1 ON DELETE CASCADE);

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);

Backend 2:
DELETE FROM t1;

Backend 1:
SELECT * FROM t2 WHERE a NOT IN (SELECT a FROM t1);

After some further investigation this problem can also be generated by two
temp tables:

BEGIN;
CREATE TEMP TABLE t3 (a int PRIMARY KEY) ON COMMIT DELETE ROWS;
CREATE TEMP TABLE t4 (a int REFERENCES t3 ON DELETE CASCADE);
INSERT INTO t3 VALUES(1);
INSERT INTO t4 VALUES(1);
COMMIT;
SELECT * FROM t4 WHERE a NOT IN (SELECT a FROM t3);

Kris Jurka


Tue, 16 Sep 2003 19:17:43 -0400 (EDT)
Kris Jurka <books@ejurka.com>
Re: observations about temporary tables and schemas
Kris Jurka <books@ejurka.com> writes:
> ... you are allowed to reference a permanent table from a temp
> table.  The triggers don't work correctly when the table is
> modified by another backend:

Hmm, yeah.  That worked when we put in the temp-vs-permanent check in
foreign key creation, but it doesn't work anymore because temp table
pages are now kept in per-backend local buffers; so there's no guarantee
that another backend can see recent changes to the contents of a temp
table.

I think we have two choices: disallow foreign-key references from temp
tables to permanent tables, or take out the optimization of storing
temp table pages in private memory.  (That would leave the whole "local
buffer manager" module as dead code, I think.)  I'm kinda leaning
towards the first; does anyone feel that it's a valuable feature to keep?

> After some further investigation this problem can also be generated by two
> temp tables:

That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
simply does an unconditional heap_truncate without bothering to run any
deletion triggers.  We could make it apply the same checks TRUNCATE
TABLE does, whereupon you'd get some sort of "can't truncate table"
error when you try to set up a foreign key reference to it.  That could
be extended to disallowing the FK reference in the first place, perhaps.
Or we could turn it into a "DELETE FROM temptable", which would be a lot
slower but would "do the right thing".  Comments?

BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
referenced by foreign keys, but this is really not a correct/complete
test.  What about user-defined deletion triggers?  Arguably it should
refuse to truncate if there are any ON DELETE triggers at all.

			regards, tom lane

Wed, 17 Sep 2003 10:26:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: observations about temporary tables and schemas 
On Wed, 17 Sep 2003, Tom Lane wrote:

> Kris Jurka <books@ejurka.com> writes:
> > ... you are allowed to reference a permanent table from a temp
> > table.  The triggers don't work correctly when the table is
> > modified by another backend:
>
> I think we have two choices: disallow foreign-key references from temp
> tables to permanent tables, or take out the optimization of storing
> temp table pages in private memory.  (That would leave the whole "local
> buffer manager" module as dead code, I think.)  I'm kinda leaning
> towards the first; does anyone feel that it's a valuable feature to keep?

I think the first is probably better all in all.

> > After some further investigation this problem can also be generated by two
> > temp tables:
>
> That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
> simply does an unconditional heap_truncate without bothering to run any
> deletion triggers.  We could make it apply the same checks TRUNCATE
> TABLE does, whereupon you'd get some sort of "can't truncate table"
> error when you try to set up a foreign key reference to it.  That could
> be extended to disallowing the FK reference in the first place, perhaps.
> Or we could turn it into a "DELETE FROM temptable", which would be a lot
> slower but would "do the right thing".  Comments?

Since it's documented as doing a truncate, I think disallowing non-self
referential FK constraints is probably a good idea.  I'm not sure that
doing all the work on commit to make the table rows delete individually
for the trigger/foreign key case is really worth it.

> BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
> referenced by foreign keys, but this is really not a correct/complete
> test.  What about user-defined deletion triggers?  Arguably it should
> refuse to truncate if there are any ON DELETE triggers at all.

Oracle doesn't seem to list those as being a problem (it explicitly lists
the foreign key constraint). It looks like they have a separate action
type for truncate so you can make before/after truncate triggers.


Wed, 17 Sep 2003 08:08:00 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: observations about temporary tables and schemas 
Tom Lane wrote:
> I think we have two choices: disallow foreign-key references from temp
> tables to permanent tables, or take out the optimization of storing
> temp table pages in private memory.  (That would leave the whole "local
> buffer manager" module as dead code, I think.)  I'm kinda leaning
> towards the first; does anyone feel that it's a valuable feature to keep?

My vote is for remove this feature.


Regards
Gaetano Mendola




Thu, 18 Sep 2003 14:36:46 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: observations about temporary tables and schemas
There are many interfaces that doesn't support canceling a long query other
than killing the application or killing the backend using the server
operational system.

I've searched the docs, faqs and list archives; seen a lot of questions but
no answers.

So I hacked a small pggetpid and pgkillpid C functions, and now I can learn
the backend's PID, start the query in a new thread, and kill the backend
using another connection if I want. Its ugly, I know, but worked wonderfully
with ODBC, the Delphi developers here and the users are very happy.

Questions are:
1) is there a better way to do that?
2) is it safe to kill the backend this way (other than an user killing the
wrong postgres owned proccess)?
3) is this useful for anyone else?

Regards,
--
Paulo Scardine



Thu, 18 Sep 2003 15:04:32 -0300
"Paulo Scardine" <paulos@cimed.ind.br>
Killing the backend to cancel a long waiting query
"Paulo Scardine" <paulos@cimed.ind.br> writes:
> So I hacked a small pggetpid and pgkillpid C functions, and now I can learn
> the backend's PID, start the query in a new thread, and kill the backend
> using another connection if I want.

I trust when you say "kill", you really mean "send SIGINT" ...

			regards, tom lane

Thu, 18 Sep 2003 15:08:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Killing the backend to cancel a long waiting query 
----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> ...
> I trust when you say "kill", you really mean "send SIGINT" ...
> ...

I'm sending a SIGTERM. Would SIGINT be more appropriate?

Thank you,
--
Paulo Scardine


Thu, 18 Sep 2003 16:19:48 -0300
"Paulo Scardine" <paulos@cimed.ind.br>
Re: Killing the backend to cancel a long waiting query 
"Paulo Scardine" <paulos@cimed.ind.br> writes:
>> I trust when you say "kill", you really mean "send SIGINT" ...

> I'm sending a SIGTERM. Would SIGINT be more appropriate?

Yes --- that would actually cancel the query, not cause the backend to
shut down.

			regards, tom lane

Thu, 18 Sep 2003 15:54:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Killing the backend to cancel a long waiting query 
Paulo Scardine wrote:
> 
> There are many interfaces that doesn't support canceling a long query other
> than killing the application or killing the backend using the server
> operational system.
> 
> I've searched the docs, faqs and list archives; seen a lot of questions but
> no answers.
> 
> So I hacked a small pggetpid and pgkillpid C functions, and now I can learn
> the backend's PID, start the query in a new thread, and kill the backend
> using another connection if I want. Its ugly, I know, but worked wonderfully
> with ODBC, the Delphi developers here and the users are very happy.
> 
> Questions are:
> 1) is there a better way to do that?

Call SQLCancel().

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Fri, 19 Sep 2003 08:46:13 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: [HACKERS] Killing the backend to cancel a long waiting query
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Wed, 17 Sep 2003, Tom Lane wrote:
>> I think we have two choices: disallow foreign-key references from temp
>> tables to permanent tables, or take out the optimization of storing
>> temp table pages in private memory.

> I think the first is probably better all in all.

Done.

>> That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
>> simply does an unconditional heap_truncate without bothering to run any
>> deletion triggers.  We could make it apply the same checks TRUNCATE
>> TABLE does, whereupon you'd get some sort of "can't truncate table"
>> error when you try to set up a foreign key reference to it.  That could
>> be extended to disallowing the FK reference in the first place, perhaps.
>> Or we could turn it into a "DELETE FROM temptable", which would be a lot
>> slower but would "do the right thing".  Comments?

> Since it's documented as doing a truncate, I think disallowing non-self
> referential FK constraints is probably a good idea.  I'm not sure that
> doing all the work on commit to make the table rows delete individually
> for the trigger/foreign key case is really worth it.

Okay, I have made it do this.  It's sort of an indirect thing: when you
try to set up the reference, the end-of-transaction ON COMMIT DELETE
ROWS barfs:

regression=# create temp table t1 (f1 int primary key) on commit delete rows;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
regression=# create temp table t2(f1 int references t1);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "t2" references "t1" via foreign key constraint "$1".

I'm not sure if it's worth trying to improve the error message by
detecting the conflict more directly (ie, checking for ON COMMIT DELETE
ROWS status when creating the FK in the first place).

>> BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
>> referenced by foreign keys, but this is really not a correct/complete
>> test.  What about user-defined deletion triggers?  Arguably it should
>> refuse to truncate if there are any ON DELETE triggers at all.

> Oracle doesn't seem to list those as being a problem (it explicitly lists
> the foreign key constraint). It looks like they have a separate action
> type for truncate so you can make before/after truncate triggers.

Okay, I just documented the existing behavior.

			regards, tom lane

Fri, 19 Sep 2003 17:10:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: observations about temporary tables and schemas 
Tom Lane wrote:
> "Paulo Scardine" <paulos@cimed.ind.br> writes:
> >> I trust when you say "kill", you really mean "send SIGINT" ...
> 
> > I'm sending a SIGTERM. Would SIGINT be more appropriate?
> 
> Yes --- that would actually cancel the query, not cause the backend to
> shut down.

Ahh...this is very useful information.

Wouldn't it be useful, though, to implement a "KILL" or "CANCEL" SQL
command that takes a backend ID as its argument (and, of course, does
the appropriate checks of whether you're a superuser or the owner of
the backend) and sends the appropriate signal to the target backend?

That would make it possible for users to kill their own runaway queries
without having to run as whatever user PG is running as.


-- 
Kevin Brown					      kevin@sysexperts.com

Sat, 20 Sep 2003 22:42:24 -0700
Kevin Brown <kevin@sysexperts.com>
Re: Killing the backend to cancel a long waiting query
> Wouldn't it be useful, though, to implement a "KILL" or "CANCEL" SQL
> command that takes a backend ID as its argument (and, of course, does
> the appropriate checks of whether you're a superuser or the owner of
> the backend) and sends the appropriate signal to the target backend?
>
> That would make it possible for users to kill their own runaway queries
> without having to run as whatever user PG is running as.


It would be awesome for phpPGAdmin as well.  eg. Superusers would be able
to cancel sql queries that gumby users are running, etc.

Chris



Sun, 21 Sep 2003 14:25:16 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Killing the backend to cancel a long waiting query
It's rumoured that Christopher Kings-Lynne once said:
>> Wouldn't it be useful, though, to implement a "KILL" or "CANCEL" SQL
>> command that takes a backend ID as its argument (and, of course, does
>> the appropriate checks of whether you're a superuser or the owner of
>> the backend) and sends the appropriate signal to the target backend?
>>
>> That would make it possible for users to kill their own runaway
>> queries without having to run as whatever user PG is running as.
>
>
> It would be awesome for phpPGAdmin as well.  eg. Superusers would be
> able to cancel sql queries that gumby users are running, etc.

I'll second that for pgAdmin. I have times in the past where it would have
been useful on my own systems as well.
Regards, Dave.



Sun, 21 Sep 2003 11:26:58 +0100 (BST)
"Dave Page" <dpage@vale-housing.co.uk>
Re: Killing the backend to cancel a long waiting query
I can implement it as C functions, I think.
Would be nice to have something like:

Test=# select pg_list_backends();
  pid  | conn_id  |   user   | database  | time |     host      | status
-------+----------+----------+-----------+------+---------------+--------
  4724 | 35445134 | marcelo  | test      | 0:34 | 192.168.2.212 | select
 18737 | 15412337 | postgres | template1 | 0:00 | 192.168.1.65  | idle
 18693 | 11448964 | postgres | test      | 0:00 | 127.0.0.1     | idle
(3 rows)

Test=# select pg_stopquery_pid(4724);
 pg_stopquery_pid
------------------
                0

Is it worth?

--
Paulo Scardine

----- Original Message ----- 
From: "Dave Page" <dpage@vale-housing.co.uk>
> > It would be awesome for phpPGAdmin as well.  eg. Superusers would be
> > able to cancel sql queries that gumby users are running, etc.
>
> I'll second that for pgAdmin. I have times in the past where it would have
> been useful on my own systems as well.
> Regards, Dave.


Mon, 22 Sep 2003 14:53:45 -0300
"Paulo Scardine" <paulos@cimed.ind.br>
Re: Killing the backend to cancel a long waiting query
On Mon, 2003-09-22 at 13:53, Paulo Scardine wrote:
> I can implement it as C functions, I think.
> Would be nice to have something like:
> 
> Test=# select pg_list_backends();
>   pid  | conn_id  |   user   | database  | time |     host      | status
> -------+----------+----------+-----------+------+---------------+--------
>   4724 | 35445134 | marcelo  | test      | 0:34 | 192.168.2.212 | select
>  18737 | 15412337 | postgres | template1 | 0:00 | 192.168.1.65  | idle
>  18693 | 11448964 | postgres | test      | 0:00 | 127.0.0.1     | idle
> (3 rows)
> 
> Test=# select pg_stopquery_pid(4724);
>  pg_stopquery_pid
> ------------------
>                 0
> 
> Is it worth?

Yes, but you may need a way to ensure that the query canceled is the
same one that you really want to cancel. (imagine come connection
pooling and bad luck mixed together so that the long running query
actually ends but another query fires up between the time you lookup the
long running query and the time you issue the kill...). maybe
transaction id as well as pid for arguments?

Robert Treat 
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


22 Sep 2003 14:29:31 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: Killing the backend to cancel a long waiting query
I do not know how to do that. I learned that I can send a SIGINT to a
backend to cancel a query, but I dont know how to kill just one transaction.
I have only "pg_getpid" and "pg_killpid" as interfaces to system functions
"getpid" and "kill".

BTW, what information can I get about the current running backends on the
internal structures? Or do I have to ask the OS?

Thank you,
--
Paulo Scardine

----- Original Message ----- 
From: "Robert Treat" <xzilla@users.sourceforge.net>
Subject: Re: [HACKERS] Killing the backend to cancel a long waiting query
>
> Yes, but you may need a way to ensure that the query canceled is the
> same one that you really want to cancel. (imagine come connection
> pooling and bad luck mixed together so that the long running query
> actually ends but another query fires up between the time you lookup the
> long running query and the time you issue the kill...). maybe
> transaction id as well as pid for arguments?
>


Mon, 22 Sep 2003 16:02:57 -0300
"Paulo Scardine" <paulos@cimed.ind.br>
Re: Killing the backend to cancel a long waiting query