Thread: temp table oddness?

temp table oddness?

From
Tatsuo Ishii
Date:
I found weird behavior with temp tables.

test=> create table u1(i int);
CREATE
test=> insert into u1 values(1);
INSERT 3408201 1
test=> insert into u1 values(1);
INSERT 3408202 1
test=> create temp table u1(i int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
NOTICE:  trying to delete a reldesc that does not exist.
NOTICE:  trying to delete a reldesc that does not exist.
CREATE

Are these notices normal?

Next I exited the session and start psql again.

test=> 
EOF
[t-ishii@ext16 Chapter3]$ !!
psql test
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.1 on powerpc-unknown-linux-gnu, compiled by gcc egcs-2.90.25 980302 (egcs-1.0.2 prerelease)]
  type \? for help on slash commands  type \q to quit  type \g or terminate with semicolon to execute queryYou are
currentlyconnected to the database: test
 

test=> create temp table u1(i int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
ERROR:  Cannot create unique index. Table contains non-unique values

What's this? I thought temp tables completely mask persistent tables.
---
Tatsuo Ishii



Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> I found weird behavior with temp tables.
> 
> test=> create table u1(i int);
> CREATE
> test=> insert into u1 values(1);
> INSERT 3408201 1
> test=> insert into u1 values(1);
> INSERT 3408202 1
> test=> create temp table u1(i int primary key);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> NOTICE:  trying to delete a reldesc that does not exist.
> NOTICE:  trying to delete a reldesc that does not exist.
> CREATE
> 
> Are these notices normal?

Not normal.  This works:test=> create table u1(i int);CREATEtest=> insert into u1 values(1);INSERT 18697 1test=> insert
intou1 values(1);INSERT 18698 1test=> create temp table u1(i int);CREATEtest=> create unique index i_u1 on
u1(i);CREATE

Backtrace shows:#0  elog (lev=0,     fmt=0x81700e7 "trying to delete a reldesc that does not exist.")    at elog.c:75#1
0x812a1f6 in RelationFlushRelation (relationPtr=0x8043510,     onlyFlushReferenceCountZero=0) at relcache.c:1262#2
0x812a6c8in RelationPurgeLocalRelation (xactCommitted=1 '\001')    at relcache.c:1533#3  0x8086c3f in CommitTransaction
()at xact.c:954#4  0x8086e2c in CommitTransactionCommand () at xact.c:1172#5  0x80ff559 in PostgresMain (argc=4,
argv=0x80475a8,real_argc=4,     real_argv=0x80475a8) at postgres.c:1654#6  0x80b619c in main (argc=4, argv=0x80475a8)
atmain.c:102#7  0x80607fc in __start ()
 

What I don't understand why the PRIMARY is different than creating the
index manually...  OK, got the reason:test=> create table u1(i int);CREATEtest=> insert into u1 values(1);INSERT 18889
1test=>insert into u1 values(1);INSERT 18890 1test=> begin;BEGINtest=> create temp table u1(i int);CREATEtest=> create
uniqueindex i_u1 on u1(i);CREATEtest=> end;NOTICE:  trying to delete a reldesc that does not exist.NOTICE:  trying to
deletea reldesc that does not exist.END
 

The cause is that the index creation is happening in the same
transaction as the create of the temp table.  Any comments on a cause?
Tom Lane's cache changes may address this.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> I found weird behavior with temp tables.
> test=> create table u1(i int);
> CREATE
> test=> insert into u1 values(1);
> INSERT 3408201 1
> test=> insert into u1 values(1);
> INSERT 3408202 1
> test=> create temp table u1(i int primary key);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> NOTICE:  trying to delete a reldesc that does not exist.
> NOTICE:  trying to delete a reldesc that does not exist.
> CREATE

> Are these notices normal?

No --- looks like something wrong with relcache shared-invalidation.
FWIW, they do not occur with the new relcache code I'm currently
testing.  Hope to commit this stuff today.

> Next I exited the session and start psql again.

> test=> create temp table u1(i int primary key);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> ERROR:  Cannot create unique index. Table contains non-unique values

> What's this? I thought temp tables completely mask persistent tables.

I still get this one, however.  Odd.  Apparently, a temp table will
successfully mask a regular table created earlier in the same psql
session, but *not* one that's been created in a different psql session.
Over to you, Bruce...
        regards, tom lane


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
Man, this example has bugs just crawling all over it.

I did
1. create plain table u1, insert "1" twice
2. start new backend
3.
regression=> create temp table u1(i int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
ERROR:  Cannot create unique index. Table contains non-unique values
regression=> drop table u1;
DROP
regression=> \q

Although psql quits cleanly enough, the underlying backend has
coredumped, as you will discover if you have any other active backends.
The dump is an assert failure at

#6  0x15bb1c in ExceptionalCondition (   conditionName=0x2fdcc "!((bool)((void*)(tuple) != ((void *)0)))",
exceptionP=0x40009a58,detail=0x0, fileName=0x7ae4 "\003", lineNumber=1127)   at assert.c:72
 
#7  0x9c4a0 in index_destroy (indexId=150537) at index.c:1127
#8  0x15b8f0 in remove_all_temp_relations () at temprel.c:97
#9  0x113f64 in shmem_exit (code=0) at ipc.c:190
#10 0x113e64 in proc_exit (code=0) at ipc.c:136
#11 0x12244c in PostgresMain (argc=5, argv=0x40003090, real_argc=5,   real_argv=0x7b033324) at postgres.c:1614

Apparently, temp index creation registers the temp index with temprel.c
before the index is filled.  Then, the "duplicate values" error aborts
creation of the index --- but the entry in temprel.c's list is still
there.  When remove_all_temp_relations tries to delete the index,
kaboom.

Although this particular error presumably won't be possible after we
fix the problem that the index is looking at the wrong underlying table,
there are other possible errors in index creation, so I think we gotta
deal with this problem too.

A quick and dirty fix might be to make index_destroy return quietly
if it's asked to destroy a nonexistent index.  A better fix would be
to make remove_all_temp_relations check whether the rel it's trying to
destroy still exists --- this should happen for plain rels as well as
indexes, probably, since heap_destroy_with_catalog doesn't like being
asked to destroy a nonexistent table either.
        regards, tom lane


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
Here's another case that doesn't work too well:

regression=> create table u1(i int);
CREATE
regression=> insert into u1 values(1);
INSERT 150665 1
regression=> insert into u1 values(1);
INSERT 150666 1
regression=> create temp table u1(i int);
CREATE
regression=> create unique index i_u1 on u1(i);
CREATE
regression=> select * from u1;        -- yup, temp table is empty
i
-
(0 rows)

regression=> drop table u1;        -- drop temp table
DROP
regression=> select * from u1;        -- ok, we're back to permanent u1
i
-
1
1
(2 rows)

regression=> begin;
BEGIN
regression=> create temp table u1(i int);
CREATE
regression=> create unique index i_u1 on u1(i);
ERROR:  Cannot create index: 'i_u1' already exists
-- apparently, dropping a temp table doesn't drop its temp indexes?
regression=> end;
END
regression=> select * from u1;
ERROR:  cannot find attribute 1 of relation pg_temp.24335.3
-- oops, what's causing this?  Shouldn't the xact have been rolled back
-- due to error?
regression=> \q
-- backend coredumps on quit


Looks like indexes on temp tables need some serious work :-(
        regards, tom lane


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> Here's another case that doesn't work too well:
> 
> regression=> create table u1(i int);
> CREATE
> regression=> insert into u1 values(1);
> INSERT 150665 1
> regression=> insert into u1 values(1);
> INSERT 150666 1
> regression=> create temp table u1(i int);
> CREATE
> regression=> create unique index i_u1 on u1(i);
> CREATE
> regression=> select * from u1;        -- yup, temp table is empty
> i
> -
> (0 rows)
> 
> regression=> drop table u1;        -- drop temp table
> DROP
> regression=> select * from u1;        -- ok, we're back to permanent u1
> i
> -
> 1
> 1
> (2 rows)

Gee, I was doing so well up to this point.

> 
> regression=> begin;
> BEGIN
> regression=> create temp table u1(i int);
> CREATE
> regression=> create unique index i_u1 on u1(i);
> ERROR:  Cannot create index: 'i_u1' already exists
> -- apparently, dropping a temp table doesn't drop its temp indexes?
> regression=> end;
> END
> regression=> select * from u1;
> ERROR:  cannot find attribute 1 of relation pg_temp.24335.3
> -- oops, what's causing this?  Shouldn't the xact have been rolled back
> -- due to error?
> regression=> \q
> -- backend coredumps on quit
> 
> 
> Looks like indexes on temp tables need some serious work :-(

It is the existance of the temp in transactions that is causing a
problem.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> Man, this example has bugs just crawling all over it.
> 
> I did
> 1. create plain table u1, insert "1" twice
> 2. start new backend
> 3.
> regression=> create temp table u1(i int primary key);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> ERROR:  Cannot create unique index. Table contains non-unique values
> regression=> drop table u1;
> DROP
> regression=> \q

Again, it is because the index is done in the same transaction.

> 
> Although psql quits cleanly enough, the underlying backend has
> coredumped, as you will discover if you have any other active backends.
> The dump is an assert failure at
> 
> #6  0x15bb1c in ExceptionalCondition (
>     conditionName=0x2fdcc "!((bool)((void*)(tuple) != ((void *)0)))",
>     exceptionP=0x40009a58, detail=0x0, fileName=0x7ae4 "\003", lineNumber=1127)
>     at assert.c:72
> #7  0x9c4a0 in index_destroy (indexId=150537) at index.c:1127
> #8  0x15b8f0 in remove_all_temp_relations () at temprel.c:97
> #9  0x113f64 in shmem_exit (code=0) at ipc.c:190
> #10 0x113e64 in proc_exit (code=0) at ipc.c:136
> #11 0x12244c in PostgresMain (argc=5, argv=0x40003090, real_argc=5,
>     real_argv=0x7b033324) at postgres.c:1614
> 
> Apparently, temp index creation registers the temp index with temprel.c
> before the index is filled.  Then, the "duplicate values" error aborts
> creation of the index --- but the entry in temprel.c's list is still
> there.  When remove_all_temp_relations tries to delete the index,
> kaboom.

Yep.  Wouldn't the best way be to have the temp system record the
transaction id used, and to invalidate all temp entries associated with
an aborted transaction.  That is how the cache code works, so it seems
it should be extended to the temp code.

> 
> Although this particular error presumably won't be possible after we
> fix the problem that the index is looking at the wrong underlying table,
> there are other possible errors in index creation, so I think we gotta
> deal with this problem too.
> 
> A quick and dirty fix might be to make index_destroy return quietly
> if it's asked to destroy a nonexistent index.  A better fix would be
> to make remove_all_temp_relations check whether the rel it's trying to
> destroy still exists --- this should happen for plain rels as well as
> indexes, probably, since heap_destroy_with_catalog doesn't like being
> asked to destroy a nonexistent table either.

I say let's leave these alone.  Their aborting on removal of
non-existant stuff helps us see bugs that could be masked by proposed
fix.

The temp table code was very small, and relies on the cache code, and
the fact all relname lookups happen through the cache.  I am suprised it
has worked as well as it has.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Yep.  Wouldn't the best way be to have the temp system record the
> transaction id used, and to invalidate all temp entries associated with
> an aborted transaction.  That is how the cache code works, so it seems
> it should be extended to the temp code.

Yeah, that would work -- add an xact abort cleanup routine that goes
through the temprel list and removes entries added during the current
transaction.

AFAICS this only explains the coredump-at-exit business, though.
I'm particularly baffled by that
ERROR:  cannot find attribute 1 of relation pg_temp.24335.3
in my last example --- do you understand why that's happening?
        regards, tom lane


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Yep.  Wouldn't the best way be to have the temp system record the
> > transaction id used, and to invalidate all temp entries associated with
> > an aborted transaction.  That is how the cache code works, so it seems
> > it should be extended to the temp code.
> 
> Yeah, that would work -- add an xact abort cleanup routine that goes
> through the temprel list and removes entries added during the current
> transaction.
> 
> AFAICS this only explains the coredump-at-exit business, though.
> I'm particularly baffled by that
> ERROR:  cannot find attribute 1 of relation pg_temp.24335.3
> in my last example --- do you understand why that's happening?

Who knows.  Once it gets messed up, anything can happen.   The problem
with indexes created in the same transaction as the temp table still is
a problem, though you say your new cache code fixes that.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Who knows.  Once it gets messed up, anything can happen.   The problem
> with indexes created in the same transaction as the temp table still is
> a problem, though you say your new cache code fixes that.

No, I didn't say that.  The weird "notice" isn't coming out any more,
but I'm still seeing all these other bugs.  It looks to me like there
are problems with ensuring that an index on a temp table is (a) temp
itself, and (b) built against the temp table and not a permanent table
of the same name.

I don't really understand how temp tables are implemented and whether
relcache.c needs to be aware of them --- is there documentation
somewhere?
        regards, tom lane


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Who knows.  Once it gets messed up, anything can happen.   The problem
> > with indexes created in the same transaction as the temp table still is
> > a problem, though you say your new cache code fixes that.
> 
> No, I didn't say that.  The weird "notice" isn't coming out any more,
> but I'm still seeing all these other bugs.  It looks to me like there
> are problems with ensuring that an index on a temp table is (a) temp
> itself, and (b) built against the temp table and not a permanent table
> of the same name.

I thought this worked.  In the regression tests, temp.sql has:
CREATE TABLE temptest(col int);CREATE INDEX i_temptest ON temptest(col);CREATE TEMP TABLE temptest(col int);CREATE
INDEXi_temptest ON temptest(col);DROP INDEX i_temptest;DROP TABLE temptest;DROP INDEX i_temptest;DROP TABLE temptest;
 

and works fine.

> 
> I don't really understand how temp tables are implemented and whether
> relcache.c needs to be aware of them --- is there documentation
> somewhere?

That's a joke, right?  :-)

temprel.c has:

/** This implements temp tables by modifying the relname cache lookups* of pg_class.* When a temp table is created, a
linkedlist of temp table tuples is* stored here.  When a relname cache lookup is done, references to user-named* temp
tablesare converted to the internal temp table names.**/
 

get_temp_rel_by_name() is the workhorse.  You can see the call to it in
ClassNameIndexScan(), which make the cache think the temp rel is a real
relation, and not just a temp one.  Other access to the relation via oid
remain the same.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Yep.  Wouldn't the best way be to have the temp system record the
> > transaction id used, and to invalidate all temp entries associated with
> > an aborted transaction.  That is how the cache code works, so it seems
> > it should be extended to the temp code.
> 
> Yeah, that would work -- add an xact abort cleanup routine that goes
> through the temprel list and removes entries added during the current
> transaction.
> 
> AFAICS this only explains the coredump-at-exit business, though.
> I'm particularly baffled by that
> ERROR:  cannot find attribute 1 of relation pg_temp.24335.3
> in my last example --- do you understand why that's happening?
> 
>             regards, tom lane
> 

I have added temp invalidation code for aborted transactions:

---------------------------------------------------------------------------

Old behavour:test=> begin;BEGINtest=> create temp table test (x int);CREATEtest=> create index i_test on
test(x);CREATEtest=>abort;NOTICE:  trying to delete a reldesc that does not exist.NOTICE:  trying to delete a reldesc
thatdoes not exist.ABORTtest=> create temp table test (x int);ERROR:  Relation 'test' already exists
 

---------------------------------------------------------------------------

New behavour:
test=> begin;BEGINtest=> create temp table test (x int);CREATEtest=> create index i_test on test(x);CREATEtest=>
abort;NOTICE: trying to delete a reldesc that does not exist.NOTICE:  trying to delete a reldesc that does not
exist.ABORTtest=>create temp table test(x int);CREATE
 

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I have added temp invalidation code for aborted transactions:

> New behavour:

>     test=> begin;
>     BEGIN
>     test=> create temp table test (x int);
>     CREATE
>     test=> create index i_test on test(x);
>     CREATE
>     test=> abort;
>     NOTICE:  trying to delete a reldesc that does not exist.
>     NOTICE:  trying to delete a reldesc that does not exist.
>     ABORT
>     test=> create temp table test(x int);
>     CREATE

OK, cool.  I think I know where to fix those "NOTICES", too:
the relcache indexes temp relations by their real names, so
RelationNameGetRelation() ought to substitute the real name before
probing the cache.  As it stands you wind up with two relcache entries
for the temp table, which is bad.  Working on it now.
        regards, tom lane


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> I found weird behavior with temp tables.
> 
> test=> create table u1(i int);
> CREATE
> test=> insert into u1 values(1);
> INSERT 3408201 1
> test=> insert into u1 values(1);
> INSERT 3408202 1
> test=> create temp table u1(i int primary key);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> NOTICE:  trying to delete a reldesc that does not exist.
> NOTICE:  trying to delete a reldesc that does not exist.
> CREATE
> 
> Are these notices normal?

OK, looks fixed.  Tatsuo, please test current cvs tree.  Thanks.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Bruce Momjian
Date:
> > I found weird behavior with temp tables.
> > 
> > test=> create table u1(i int);
> > CREATE
> > test=> insert into u1 values(1);
> > INSERT 3408201 1
> > test=> insert into u1 values(1);
> > INSERT 3408202 1
> > test=> create temp table u1(i int primary key);
> > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> > NOTICE:  trying to delete a reldesc that does not exist.
> > NOTICE:  trying to delete a reldesc that does not exist.
> > CREATE
> > 
> > Are these notices normal?
> 
> OK, looks fixed.  Tatsuo, please test current cvs tree.  Thanks.

Let me add Tom Lane did much of the fixing too.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Tom Lane
Date:
OK, I think that set of issues is solved.  All the temp-table examples
Tatsuo and I gave this morning work with the current sources, and I
think shared invalidation of relcache entries is pretty solid too.

What we have at this point is a set of tightly interwoven changes in
relcache.c, temprel.c, sinval.c, and the syscache stuff.  If we want to
commit these changes into 6.5.*, it's all-or-nothing; I don't think we
can extract just part of the changes.  I'm real hesitant to do that.
These are good fixes, I believe, but I don't yet trust 'em enough to put
into a stable release.  Can we live with the temp table misbehaviors as
"known bugs" for 6.5.* ?

The other thing we'd have to do if we don't back-patch these changes
is remove the FileUnlink call in mdtruncate() in REL6_5, which would
mean vacuum still won't remove excess segment files in 6.5.*.  It would
truncate 'em to zero length, though, so the deficiency isn't horrible
AFAICS.

My inclination is to do that, and leave the other problems as unfixed
bugs for REL6_5.  The alternative would be to back-patch all these
changes and delay 6.5.2 release for a while while people beta-test.
Comments?
        regards, tom lane


Re: [HACKERS] temp table oddness?u

From
Bruce Momjian
Date:
> OK, I think that set of issues is solved.  All the temp-table examples
> Tatsuo and I gave this morning work with the current sources, and I
> think shared invalidation of relcache entries is pretty solid too.
> 
> What we have at this point is a set of tightly interwoven changes in
> relcache.c, temprel.c, sinval.c, and the syscache stuff.  If we want to
> commit these changes into 6.5.*, it's all-or-nothing; I don't think we
> can extract just part of the changes.  I'm real hesitant to do that.
> These are good fixes, I believe, but I don't yet trust 'em enough to put
> into a stable release.  Can we live with the temp table misbehaviors as
> "known bugs" for 6.5.* ?

I have already cast my vote for leaving them out of 6.5.*.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: [HACKERS] temp table oddness?

From
Tatsuo Ishii
Date:
> > I found weird behavior with temp tables.
> > 
> > test=> create table u1(i int);
> > CREATE
> > test=> insert into u1 values(1);
> > INSERT 3408201 1
> > test=> insert into u1 values(1);
> > INSERT 3408202 1
> > test=> create temp table u1(i int primary key);
> > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey' for table 'u1'
> > NOTICE:  trying to delete a reldesc that does not exist.
> > NOTICE:  trying to delete a reldesc that does not exist.
> > CREATE
> > 
> > Are these notices normal?
> 
> OK, looks fixed.  Tatsuo, please test current cvs tree.  Thanks.

Now problems I was complaining have gone. Thanks!
---
Tatsuo Ishii


Re: [HACKERS] temp table oddness?

From
"flo"
Date:
I'm interested in learning how to hack any suggestions how to go about it?
Bruce Momjian <maillist@candle.pha.pa.us> wrote in message
news:199909041457.KAA18485@candle.pha.pa.us...
> > I found weird behavior with temp tables.
> >
> > test=> create table u1(i int);
> > CREATE
> > test=> insert into u1 values(1);
> > INSERT 3408201 1
> > test=> insert into u1 values(1);
> > INSERT 3408202 1
> > test=> create temp table u1(i int primary key);
> > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'u1_pkey'
for table 'u1'
> > NOTICE:  trying to delete a reldesc that does not exist.
> > NOTICE:  trying to delete a reldesc that does not exist.
> > CREATE
> >
> > Are these notices normal?
>
> Not normal.  This works:
>
> test=> create table u1(i int);
> CREATE
> test=> insert into u1 values(1);
> INSERT 18697 1
> test=> insert into u1 values(1);
> INSERT 18698 1
> test=> create temp table u1(i int);
> CREATE
> test=> create unique index i_u1 on u1(i);
> CREATE
>
> Backtrace shows:
>
> #0  elog (lev=0,
>     fmt=0x81700e7 "trying to delete a reldesc that does not exist.")
>     at elog.c:75
> #1  0x812a1f6 in RelationFlushRelation (relationPtr=0x8043510,
>     onlyFlushReferenceCountZero=0) at relcache.c:1262
> #2  0x812a6c8 in RelationPurgeLocalRelation (xactCommitted=1 '\001')
>     at relcache.c:1533
> #3  0x8086c3f in CommitTransaction () at xact.c:954
> #4  0x8086e2c in CommitTransactionCommand () at xact.c:1172
> #5  0x80ff559 in PostgresMain (argc=4, argv=0x80475a8, real_argc=4,
>     real_argv=0x80475a8) at postgres.c:1654
> #6  0x80b619c in main (argc=4, argv=0x80475a8) at main.c:102
> #7  0x80607fc in __start ()
>
> What I don't understand why the PRIMARY is different than creating the
> index manually...  OK, got the reason:
>
> test=> create table u1(i int);
> CREATE
> test=> insert into u1 values(1);
> INSERT 18889 1
> test=> insert into u1 values(1);
> INSERT 18890 1
> test=> begin;
> BEGIN
> test=> create temp table u1(i int);
> CREATE
> test=> create unique index i_u1 on u1(i);
> CREATE
> test=> end;
> NOTICE:  trying to delete a reldesc that does not exist.
> NOTICE:  trying to delete a reldesc that does not exist.
> END
>
> The cause is that the index creation is happening in the same
> transaction as the create of the temp table.  Any comments on a cause?
> Tom Lane's cache changes may address this.
>
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026