Thread: observations about temporary tables and schemas

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

"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

"Tom Lane" <Tom Lane> writes:
>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.

ISTM that if use of qualified names is disallowed you shouldn't be
allowed to create a temporary table with the same name as an existing
table.  Otherwise you have an unclear (or arbitrary) definition of which
table is used in an unqualified reference.  In this case, I think the
SQL spec was not thought out very well.  In my way of thinking,
temporary tables should behave just like normal tables wrt name
resolution.

Also, it's worth noting that qualified names for temp tables are an
allowed syntax except during the create table statement (although in
normal circumstances you would never know the namespace).  Because of
this, qualified name restriction appears to feel like an arbitrary
exclusion.

I did not see a TODO item regarding global temporary tables...has this
been attempted/done?

Regards,
Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> ... In this case, I think the
> SQL spec was not thought out very well.

Many people think that about a lot of aspects of the spec ;-)

> I did not see a TODO item regarding global temporary tables...has this
> been attempted/done?

We concluded in the last discussion of this subject that our existing
temp table behavior actually approaches the spec's GLOBAL TEMP behavior
more nearly than it does LOCAL TEMP; but in any case, without modules
the difference is moot.  Accordingly, 7.4 will accept either syntax,
and there is no more TODO.  See the archives from a few months back, or
http://developer.postgresql.org/docs/postgres/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY
which distills that discussion.
        regards, tom lane

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

On Tue, 16 Sep 2003, Kris Jurka wrote:
> 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:

I had no idea this was even possible.  IHRTS (I haven't read the spec),
but again here temp tables seem to break rules in strange and unexpected
ways.  From the docs, we know that temp tables are 'dropped' after the
connection is broken.  With RI in mind, (and more generally, triggers)
the nature and execution of that drop is important.

All that being said, using RI in temp tables (especially with regular
tables) is asking for trouble.  The reason I was hoping to keep them in
a special schema is that I use them as a kind of a database enforced
mutex object using the name of the table as the mutex identifier.   By
holding them in a special schema (e.g. locks) that is not in the schema
search path I can bypass name resolution problems that happened when the
'mutex' id is the same name as another table (actually, there are no
problems, it simply isn't allowed!).

Merlin

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

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?

I would hate to lose 'memory optimized' temp tables unless the arguments
for were compelling and there was no other solution.

> 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?

This seems correct from one point of view, but what happens when a
delete trigger fails?  Instead of 'DELETE FROM', what about
'DROP...CASCADE'?

This seems be consistent with other commands.  For example, when a temp
table has domain x and another backend does DROM DOMAIN...CASCADE on x,
x is dropped from the temp table, which 'feels right'.  If the temp
table fails to drop for some reason, the memory could be bulldozed like
it is now.  BTW, domains are another way to bypass the 'isolation
principle' for temp tables.

Merlin

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.

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

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

"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

----- 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

"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

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/

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

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

> 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

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.

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 | select18737 | 15412337 | postgres | template1 | 0:00 | 192.168.1.65  | idle18693 | 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.

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

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?
>