After running script, column names not appearing in pgadmin

Question

Sometimes when I run my Python script which calls shp2pgsqlto upload a new table to the database, when I view this table in pgadmin, it appears with blank column names:

no column names This one has column names this is when it is good Usually when I run the script again it fixes the problem, and pgadmin displays a message about database vacuuming. Honestly the problem is my boss because he takes this as a sign there is something wrong with my code and we can't move forward until he sees the names in pgadmin (by chance when I demonstrated the script it was the 1/10 time that it messed up without the column names).

In postgres is it even possible to have a table without column names?

Here is the vacuum message enter image description here

Here is the output from psql's \d (assume XYZ is the name of the project and the name of the db)

xyz => \d asmithe.intersect
                                     Table "asmithe.intersect"
   Column   |             Type             |                         Modifiers

------------+------------------------------+------------------------------------
------------------------
 gid        | integer                      | not null default nextval('intersect
ion_gid_seq'::regclass)
 fid_xyz_09 | integer                      |
 juris_id   | character varying(2)         |
 xyz_plot   | numeric                      |
 poly_id    | character varying(20)        |
 layer      | character varying(2)         |
 area       | numeric                      |
 perimeter  | numeric                      |
 lid_dist   | integer                      |
 comm       | character varying(252)       |
 cdate      | character varying(30)        |
 sdate      | character varying(30)        |
 edate      | character varying(30)        |
 afsdate    | character varying(30)        |
 afedate    | character varying(30)        |
 capdate    | character varying(30)        |
 salvage    | double precision             |
 pb_harv    | double precision             |
 utotarea   | numeric                      |
 nbacvers   | character varying(24)        |
 totarea    | numeric                      |
 areamoda   | numeric                      |
 areamodb   | numeric                      |
 areamodt   | double precision             |
 areamodv   | numeric                      |
 area_intr  | numeric                      |
 dist_perct | numeric                      |
 id         | double precision             |
 floodid    | double precision             |
 basr       | double precision             |
 floodmaps  | double precision             |
 floodmapm  | double precision             |
 floodcaus  | double precision             |
 burnclas   | double precision             |
 geom       | geometry(MultiPolygon,13862) |
Indexes:
    "intersect_pkey" PRIMARY KEY, btree (gid)

Quitting and restarting usually does fix it.

What is the script? What is the "message about vacuuming"? Does quitting and restarting PgAdmin change anything? Is the script run from within that PgAdmin session, from `psql`, or what? — Craig Ringer, Jun 25 '15 at 04:30

Craig Ringer · Accepted Answer · 2015-06-26T01:43:59.290

2

In postgres is it even possible to have a table without column names?

It is possible to create a table with zero columns:

test=> CREATE TABLE zerocolumns();
CREATE TABLE
test=> \d zerocolumns 
Table "public.zerocolumns"
 Column | Type | Modifiers 
--------+------+-----------

but not a zero-width column name:

test=> CREATE TABLE zerowidthcol("" integer);
ERROR:  zero-length delimited identifier at or near """"
LINE 1: CREATE TABLE zerowidthcol("" integer);

                                  ^

though a column name composed only of a space is permissible:

test=> CREATE TABLE spacecol(" " integer);
CREATE TABLE
test=> \d spacecol 
   Table "public.spacecol"
 Column |  Type   | Modifiers 
--------+---------+-----------
        | integer |

Please show the output from psql's \d command if this happens. With only (heavily edited) screenshots I can't tell you anything more useful.

If I had to guess I'd say it's probably a drawing bug in PgAdmin.

Update: The VACUUM message is normal after big changes to a table. Read the message, it explains what is going on. There is no problem there.

There's nothing wrong with the psql output, and since quitting and restarting PgAdmin fixes it, I'm pretty confident you've hit a PgAdmin bug related to drawing or catalog access. If it happens on the current PgAdmin version and you can reproduce it with a script you can share with the public, please post a report on the pgadmin-support mailing list.

edited Jun 26 '15 at 01:43

answered Jun 25 '15 at 04:29

Craig Ringer

307,061
76
688
778

I updated the question with the information you had requested for. – Celeritas Jun 25 '15 at 20:01
Answer updated. I see nothing to be concerned about here, except perhaps a PgAdmin UI bug. – Craig Ringer Jun 26 '15 at 01:44
fwiw it only ever happens on the one table, and none of my colleagues have seen it before. Thought the table is dropped and recreated frequently using the `-d` option of `psql` so that's probably where the vacuuming is coming from. – Celeritas Jun 26 '15 at 01:54
Yep. Add an `ANALYZE tablename;` to the end of your table reload scripts. What you describe sounds a lot like what could happen if PgAdmin tries to re-read table metadata, fails, and doesn't correctly handle the failure. Try to turn it into a reproducible test case you can report to the pgadmin mailing list. – Craig Ringer Jun 26 '15 at 01:57
My python script doesn't have the ability to run arbitrary SQL commands, like ANALYZE. It spawns a new process when calling `pgsql2shp`, `shp2pgsql` and `psql` and though `pgsql2shp` allows for arbitrary SQL queries to be added through it's arguments, I don't believe `psql` or `pgsql2shp` can. How can I add the ANALYSE command? I already am using `arcpy` but would prefer not to introduce a new library to the project such as `Psycopg2`. – Celeritas Jun 26 '15 at 18:14
Well, shell out to `psql -c 'ANALYZE tablename;'` – Craig Ringer Jun 26 '15 at 20:06

score 2 · Answer 2 · answered Sep 13 '15 at 11:39

The same happened to me in pgAdmin 1.18.1 when running the DDL (i.e. SQL script that drops and recreates all tables). After restarting pgAdmin or refreshing the database it is working again (just refreshing the table is not sufficient). It seems that pgAdmin simply does not auto-refresh table metadata after the tables are replaced.

After running script, column names not appearing in pgadmin

2 Answers2