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

2015-03-08 12:51 GMT+01:00 Danilo Fortunato <danilo(dot)fortunato(dot)2(at)gmail(dot)com>:

> Thanks Guillaume.
> On which version of PostegreSQL have you done the test ?
>
>
9.4. Kinda like a reflex to always use the latest :)

Should have tried the 8.2, sorry.

> 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.
>
>
So I tried with 8.2. It still works for me. Might be a GreenPlum specific
issue. Not something I could work on though.

> Hi,
>
> 2015-03-07 22:01 GMT+01:00 Danilo Fortunato <danilo(dot)fortunato(dot)2(at)gmail(dot)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
>
>
>

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com