> Interesting to note that SELECT * FROM table_with_zero_cols does not > complain of anything. > > postgres=# select * from test1; > -- > (0 rows) > > This I believe result of the fact that we allow user to drop all columns of > a table. > > On a side note, Postgres allows me to do this (which I don't think is a bug > or useless): I inserted some rows into a table, and then dropped the > columns. The resulting table has no columns, but live rows. > > postgres=# select * from test_0_col_table ; > -- > (200000 rows)
Yeah.
alvherre=# create table foo (); CREATE TABLE alvherre=# insert into foo default values; INSERT 0 1 alvherre=# insert into foo default values; INSERT 0 1 alvherre=# insert into foo default values; INSERT 0 1 alvherre=# insert into foo default values; INSERT 0 1 alvherre=# insert into foo select * from foo; INSERT 0 4 alvherre=# insert into foo select * from foo; INSERT 0 8 alvherre=# insert into foo select * from foo; INSERT 0 16 alvherre=# insert into foo select * from foo; INSERT 0 32 alvherre=# insert into foo select * from foo; INSERT 0 64 alvherre=# select count(*) from foo; count ------- 128 (1 fila)
alvherre=# select * from foo; -- (128 filas)
If you examine the ctid system column you can even see that those empty rows consume some storage space.
I was trying to build a case and propose that we issue a TRUNCATE on the table after the last column is dropped. But then realized that the rows may have become invisible, but they can be brought back to visibility by simply adding a new column. These rows with get the new column's DEFAULT value (NULL by default), and then the result of a SELECT * will show all the rows again.