Обсуждение: AW: [HACKERS] Another TEMP table trick
>> test=> create table test (x int);>> CREATE>> test=> insert into test values (1);>> INSERT >> test=> create temp tabletest (x int); <-->> CREATE <-->> test=> insert into test values (2);>> INSERT 197871>> test=> select * from test;>> x>> ->> 2>> (1 row)>> >> test=> drop table test;>> DROP>> test=> select * from test;>>x>> ->> 1>> (1 row)>> >> test=> drop table test;>> DROP Do you really think that this should be allowed ? I think table names including temp tables should (at least in combination with the owner) be unique. I think your example above demonstrates how confusing the application code can get. I think it is good, that temp tables are not really inserted into system tables, since this would be substantial overhead. There could be a problem with GUI tools that rely on these rows to format their output (like pgaccess or ODBC --> M$ Access) though. Andreas
> >> test=> create table test (x int); > >> CREATE > >> test=> insert into test values (1); > >> INSERT > >> test=> create temp table test (x int); <-- > >> CREATE <-- > >> test=> insert into test values (2); > >> INSERT 19787 1 > >> test=> select * from test; > >> x > >> - > >> 2 > >> (1 row) > >> > >> test=> drop table test; > >> DROP > >> test=> select * from test; > >> x > >> - > >> 1 > >> (1 row) > >> > >> test=> drop table test; > >> DROP > > Do you really think that this should be allowed ? I think table names > including > temp tables should (at least in combination with the owner) be unique. I > think your > example above demonstrates how confusing the application code can get. I think it should be allowed. Suppose someone has created a non-temp table with a certain name, and you want a temp table with that name. No reason you shouldn't be allowed to do that. Five people can all have temp tables with the same name, so it doesn't matter if there is a non-temp table with that name too. > > I think it is good, that temp tables are not really inserted into system > tables, > since this would be substantial overhead. Not really much overhead. > There could be a problem with GUI tools that rely on these rows > to format their output (like pgaccess or ODBC --> M$ Access) though. Oh, never thought of that. A select of pg_class will return no rows for that table because it is a temp table. -- 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
Bruce Momjian wrote: > > > There could be a problem with GUI tools that rely on these rows > > to format their output (like pgaccess or ODBC --> M$ Access) though. > > Oh, never thought of that. A select of pg_class will return no rows for > that table because it is a temp table. One more reson to move \d from psql to backend maybe with syntax like Oracle's "DESC xxx" unless there is something in ANSI standard for that. Or implement the ANSI system tables (I think there were some ;) and views. Then the front-end tools can be advised to use these (and TEMP TABLES can add rows to other (possibly structure-permanent) TEMP tables that are UNIONed withe real pg_class to give them real values. Or we can even implement just temp _rows_ for tables that exist in a session only (maybe like in independant uncommitted transactions), and add the info for temp tables to pg_class (and friends) as temp rows. ---------------- Hannu
> Bruce Momjian wrote: > > > > > There could be a problem with GUI tools that rely on these rows > > > to format their output (like pgaccess or ODBC --> M$ Access) though. > > > > Oh, never thought of that. A select of pg_class will return no rows for > > that table because it is a temp table. > > One more reson to move \d from psql to backend maybe with syntax like > Oracle's "DESC xxx" unless there is something in ANSI standard for that. > > Or implement the ANSI system tables (I think there were some ;) and > views. > > Then the front-end tools can be advised to use these (and TEMP TABLES > can > add rows to other (possibly structure-permanent) TEMP tables that are > UNIONed > withe real pg_class to give them real values. > > Or we can even implement just temp _rows_ for tables that exist in a > session only (maybe like in independant uncommitted transactions), > and add the info for temp tables to pg_class (and friends) as temp rows. I have thought some more about it, and I now want to create proper pg_class rows for the temp tables. The temp tables are named pg_temp.$pid.$seqno. What I am going to do for the temp table is to add an _extra_ entry in the system cache for the user-supplied name RELNAME lookup. All other lookups of pg_class by oid, and pg_attribute, etc use just the relid, which works without any translation. The advantage is that I can keep the system tables consistent, have less code overhead, and allow things like sequential scans of pg_class see the table, even though it will not be under the user-supplied name. Most interfaces already don't display pg_* tables, so this will be OK. I will add a new relkind for the temp tables. I will also now be able to test in vacuum if the temp table was orphaned after a backend crash, and delete it. I will prevent psql \dS from displaying the temp tables. Should be a few more days. -- 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