Re: Table DDL scripts generated by pgAdmin incorrectly show the constraint name using the implicit index name (on Greenplum).

Thanks Guillaume.
On which version of PostegreSQL have you done the test ?

If your test, as I suppose, has been done on a version of PostgreSQL more recent than 8.2.15, than this bug could only arise with older versions of PostgreSQL, and it have already been fixed (I found several references in the change log of pgAdmin regarding constraints).

Another possibility is that the pg_catalog of PostgreSQL has changed since version 8.2.15, or that Greenplum uses the pg_catalog in a different way from PostgreSQL regarding the primary key contraints.

Hi,

2015-03-07 22:01 GMT+01:00 Danilo Fortunato <danilo.fortunato.2@gmail.com>:

I use pgAdmin III 1.20.0 on a Greenplum database 4.2.6.3, which is based on PostgreSQL 8.2.15.
I'm aware that this version of PostgreSQL is no more supported by pgAdmin.

I noticed that the DDL scripts generated by pgAdmin for the tables, incorrectly show the constraint name using the implicit index name instead.
I don't know if the same bug exist using pgAdmin with PostgreSQL, but this could be easily verified.

Steps to reproduce the problem:

1. Create a table with a primary key constraint:

CREATE TABLE public.table_t
(
  col_a character varying(50) NOT NULL,
  col_b integer NOT NULL,
  col_c integer,
  CONSTRAINT pk_table_t PRIMARY KEY (col_a, col_b)
)
DISTRIBUTED BY (col_a, col_b);

(DISTRIBUTED BY is a Greenplum option, used to indicate the distribution key of the table)

2. During the creation of the table, the following message is shown:

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_t_pkey" for table "table_t"

Indeed, besides the constraint pk_table_t, an index called table_t_pkey (<table_name>_pkey) is automatically created.

3. Now let's see the DDL script generated by pgAdmin for this table.
pgAdmin shows the constraint using the implicit index name instead of the constraint name:

CREATE TABLE public.table_t
(
  col_a character varying(50) NOT NULL,
  col_b integer NOT NULL,
  col_c integer,
  CONSTRAINT table_t_pkey PRIMARY KEY (col_a, col_b)
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (col_a, col_b);

This issue was present also on pgAdmin 1.16.1 and 1.18.1.

The DDL script generated by Aginity Workbench shows the constraint with the correct name:

CREATE TABLE public.table_t
(
        col_a CHARACTER VARYING(50) NOT NULL,
        col_b INTEGER NOT NULL,
        col_c INTEGER
)
DISTRIBUTED BY (col_a, col_b);

ALTER TABLE public.table_t
ADD CONSTRAINT pk_table_t
PRIMARY KEY (col_a, col_b);

Well, that works for me. I have the constraint name, not the index name. Though I'm not using Greenplum, I don't see anything Greenplum specific for the constraints on pgAdmin's source code.

--

Guillaume.

  http://blog.guillaume.lelarge.info

  http://www.dalibo.com