Обсуждение: bug error message constraint "con_item_id_costelement" for table "costs" does not exist SQL state: 42704

Hello  PostgreSQL Developers

Ran into a bug or issue with the documentation  where creating a TEMP
table fails to create all the constraints and Upsert fails on check
constraint

The documentation states "create temp table mytable (including all) "
is suppose to create all the constraints
https://www.postgresql.org/docs/9.5/static/sql-createtable.html

below is the  the code to duplicate this issue.

I was able to work around the issue by adding the needed constraint
below the create temp command.

select version() result: "PostgreSQL 9.5.0, compiled by Visual C++
build 1800, 64-bit"

---code to create error.

CREATE TABLE xmag.costs
(
  cost_id serial primary key,
  cost_item_id integer NOT NULL,
  cost_costelem_id integer NOT NULL,
  cost_lowlevel boolean NOT NULL DEFAULT false,
  cost_stdcost numeric(24,8) NOT NULL DEFAULT 0,
  cost_posted date,
  cost_actcost numeric(24,8) NOT NULL DEFAULT 0,
  cost_updated date,
  CONSTRAINT con_item_id_costelement UNIQUE (cost_item_id,
cost_costelem_id)
);

insert into xmag.costs values
(12083,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25'),
(12084,12211,3,FALSE,6.27000000,'2014-03-25',6.27000000,'2014-03-25'),
(12085,12212,3,FALSE,35.36000000,'2014-03-25',35.36000000,'2014-03-25'),
(12088,12215,3,FALSE,31.50000000,'2013-10-25',31.50000000,'2014-07-31'),
(12089,12216,3,FALSE,0.64000000,'2013-06-27',0.64000000,'2014-07-31'),
(12090,12217,3,FALSE,0.00000000,'2013-06-27',0.00000000,'2014-07-31');
create temp table costs ( like xmag.costs including all  );
--commented out to show bug
--ALTER TABLE public.itemcost
--ADD CONSTRAINT con_item_id_itemcostelement UNIQUE(itemcost_item_id,
itemcost_costelem_id);

insert into costs (select * from costs);
Insert into costs
    values

(default,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25')
    on conflict on constraint con_item_id_costelement
    do update set cost_actcost = matcost, cost_updated = now()::Date,
        cost_posted =  now()::date,
        cost_stdcost = 10
        where cost_item_id= 12210 and costs.cost_costelem_id = 3;

On 7/28/16, joel.traf@magwerks.com <joel.traf@magwerks.com> wrote:
> Hello  PostgreSQL Developers
>
> Ran into a bug or issue with the documentation  where creating a TEMP
> table fails to create all the constraints and Upsert fails on check
> constraint
>
> The documentation states "create temp table mytable (including all) "
> is suppose to create all the constraints
> https://www.postgresql.org/docs/9.5/static/sql-createtable.html
>
> below is the  the code to duplicate this issue.
>
> I was able to work around the issue by adding the needed constraint
> below the create temp command.
>
> select version() result: "PostgreSQL 9.5.0, compiled by Visual C++
> build 1800, 64-bit"
>
> ---code to create error.
>
> CREATE TABLE xmag.costs
> (
>   cost_id serial primary key,
>   cost_item_id integer NOT NULL,
>   cost_costelem_id integer NOT NULL,
>   cost_lowlevel boolean NOT NULL DEFAULT false,
>   cost_stdcost numeric(24,8) NOT NULL DEFAULT 0,
>   cost_posted date,
>   cost_actcost numeric(24,8) NOT NULL DEFAULT 0,
>   cost_updated date,
>   CONSTRAINT con_item_id_costelement UNIQUE (cost_item_id,
> cost_costelem_id)
> );
>
> insert into xmag.costs values
> (12083,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25'),
> (12084,12211,3,FALSE,6.27000000,'2014-03-25',6.27000000,'2014-03-25'),
> (12085,12212,3,FALSE,35.36000000,'2014-03-25',35.36000000,'2014-03-25'),
> (12088,12215,3,FALSE,31.50000000,'2013-10-25',31.50000000,'2014-07-31'),
> (12089,12216,3,FALSE,0.64000000,'2013-06-27',0.64000000,'2014-07-31'),
> (12090,12217,3,FALSE,0.00000000,'2013-06-27',0.00000000,'2014-07-31');
> create temp table costs ( like xmag.costs including all  );
> --commented out to show bug
> --ALTER TABLE public.itemcost
> --ADD CONSTRAINT con_item_id_itemcostelement UNIQUE(itemcost_item_id,
> itemcost_costelem_id);

You have there columns which are present neither in xmag.costs nor in costs.
CREATE TABLE ... (LIKE ...)
creates indexes and constraints with names as if they weren't given
(i.e. by default), so your
constraint "con_item_id_costelement" becomes
"costs_cost_item_id_cost_costelem_id_key"

> insert into costs (select * from costs);

The command above does nothing since "costs" has just been creates and
has nothing.

> Insert into costs
>     values
>
> (default,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25')
>     on conflict on constraint con_item_id_costelement

Here you can use columns of index, e.g:
ON CONFLICT (cost_item_id, cost_costelem_id) DO UPDATE

>     do update set cost_actcost = matcost, cost_updated = now()::Date,
>         cost_posted =  now()::date,
>         cost_stdcost = 10
>         where cost_item_id= 12210 and costs.cost_costelem_id = 3;
>

--
Best regards,
Vitaly Burovoy

Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:
> On 7/28/16, joel.traf@magwerks.com <joel.traf@magwerks.com> wrote:
>> The documentation states "create temp table mytable (including all) "
>> is suppose to create all the constraints

> CREATE TABLE ... (LIKE ...)
> creates indexes and constraints with names as if they weren't given
> (i.e. by default), so your
> constraint "con_item_id_costelement" becomes
> "costs_cost_item_id_cost_costelem_id_key"

There's a comment in generateClonedIndexStmt about that:

     * We don't try to preserve the name of the source index; instead, just
     * let DefineIndex() choose a reasonable name.

However, this isn't documented anywhere user-visible AFAICS, and it
probably should be.

It's rather annoying that LIKE doesn't duplicate names of constraints,
especially now that we've invented ON CONFLICT and thereby made constraint
names something that would be explicitly referenced in DML code.  However,
I'm afraid that doing so is harder than it looks because index names have
to be unique within a schema.  If we tried to copy constraint names we'd
get a failure anytime the new table is in the same schema as the LIKE
source table.

            regards, tom lane

Hi Vitaly

In the code below i changed things around shorten names and created
code to create the error.  I failed to rename the constraint to match
all the other changes before submitting the bug.

When i ran into this bug it took a bit of digging because several
plpgsql functions are being called and this is just one table that a
temp table is created to run "what if's" based on user input.

Once the transaction ends it discards all the temp tables which are
clones of real data.

Also ran into another potential bug that i have not written code to
create the error   Run the below  command inside a PLpgsql function it
fails stating the table can not be altered as its in use in another
transaction.  its a temp table created by the function ;-/

 create temp table costs ( like xmag.costs including all  );
ALTER TABLE public.itemcost
ADD CONSTRAINT con_item_id_itemcostelement UNIQUE(itemcost_item_id,
itemcost_costelem_id);
The solution was to move the  Temp Table creation to the client side
before all the plpgsql functions are called.
I went to using Upsert command because it  simplified code and to
speed  up a function that uses it.

To get the Upsert to work i had to add this constraint, to a table
that has 5 other constraints that are not included in the below code.


Thanks
On 7/28/2016 at 11:43 AM, "Vitaly Burovoy"  wrote:On 7/28/16,
joel.traf@magwerks.com  wrote:
> Hello  PostgreSQL Developers
>
> Ran into a bug or issue with the documentation  where creating a
TEMP
> table fails to create all the constraints and Upsert fails on check
> constraint
>
> The documentation states "create temp table mytable (including all)
"
> is suppose to create all the constraints
> https://www.postgresql.org/docs/9.5/static/sql-createtable.html
>
> below is the  the code to duplicate this issue.
>
> I was able to work around the issue by adding the needed constraint
> below the create temp command.
>
> select version() result: "PostgreSQL 9.5.0, compiled by Visual C++
> build 1800, 64-bit"
>
> ---code to create error.
>
> CREATE TABLE xmag.costs
> (
>   cost_id serial primary key,
>   cost_item_id integer NOT NULL,
>   cost_costelem_id integer NOT NULL,
>   cost_lowlevel boolean NOT NULL DEFAULT false,
>   cost_stdcost numeric(24,8) NOT NULL DEFAULT 0,
>   cost_posted date,
>   cost_actcost numeric(24,8) NOT NULL DEFAULT 0,
>   cost_updated date,
>   CONSTRAINT con_item_id_costelement UNIQUE (cost_item_id,
> cost_costelem_id)
> );
>
> insert into xmag.costs values
>
(12083,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25'),
>
(12084,12211,3,FALSE,6.27000000,'2014-03-25',6.27000000,'2014-03-25'),
>
(12085,12212,3,FALSE,35.36000000,'2014-03-25',35.36000000,'2014-03-25'),
>
(12088,12215,3,FALSE,31.50000000,'2013-10-25',31.50000000,'2014-07-31'),
>
(12089,12216,3,FALSE,0.64000000,'2013-06-27',0.64000000,'2014-07-31'),
>
(12090,12217,3,FALSE,0.00000000,'2013-06-27',0.00000000,'2014-07-31');
> create temp table costs ( like xmag.costs including all  );
> --commented out to show bug
> --ALTER TABLE public.itemcost
> --ADD CONSTRAINT con_item_id_itemcostelement
UNIQUE(itemcost_item_id,
> itemcost_costelem_id);

You have there columns which are present neither in xmag.costs nor in
costs.
CREATE TABLE ... (LIKE ...)
creates indexes and constraints with names as if they weren't given
(i.e. by default), so your
constraint "con_item_id_costelement" becomes
"costs_cost_item_id_cost_costelem_id_key"

> insert into costs (select * from costs);

The command above does nothing since "costs" has just been creates and
has nothing.

> Insert into costs
>     values
>
>
(default,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25')
>     on conflict on constraint con_item_id_costelement

Here you can use columns of index, e.g:
ON CONFLICT (cost_item_id, cost_costelem_id) DO UPDATE

>     do update set cost_actcost = matcost, cost_updated =
now()::Date,
>         cost_posted =  now()::date,
>         cost_stdcost = 10
>         where cost_item_id= 12210 and costs.cost_costelem_id = 3;
>

--
Best regards,
Vitaly Burovoy

Hi Tom

Thanks for the answer that explains things.

My i humbly suggest adding notes to the create  table clone ( original
including all).  Stating the limitation on constraints not duplicating
an identical name and code that depends on the "constraint name" aka
Upsert will fail.

Also suggest adding  the same comment to the Insert on conflict
stating.  Tables that are cloned with (including all ) the constraint
is not automatically recreated with the same name so the constraint
needs to be manually re-created with that specific name for the temp
table.
Additionally could throw a notice that when a table is cloned with the
copy indexes or constraints,  stating names are not preserved and may
cause issues with Upsert on conflict.

again thanks
On 7/28/2016 at 12:05 PM, "Tom Lane"  wrote:Vitaly Burovoy  writes:
> On 7/28/16, joel.traf@magwerks.com  wrote:
>> The documentation states "create temp table mytable (including all)
"
>> is suppose to create all the constraints

> CREATE TABLE ... (LIKE ...)
> creates indexes and constraints with names as if they weren't given
> (i.e. by default), so your
> constraint "con_item_id_costelement" becomes
> "costs_cost_item_id_cost_costelem_id_key"

There's a comment in generateClonedIndexStmt about that:

  * We don't try to preserve the name of the source index; instead,
just
  * let DefineIndex() choose a reasonable name.

However, this isn't documented anywhere user-visible AFAICS, and it
probably should be.

It's rather annoying that LIKE doesn't duplicate names of constraints,
especially now that we've invented ON CONFLICT and thereby made
constraint
names something that would be explicitly referenced in DML code.
However,
I'm afraid that doing so is harder than it looks because index names
have
to be unique within a schema.  If we tried to copy constraint names
we'd
get a failure anytime the new table is in the same schema as the LIKE
source table.

 regards, tom lane

joel.traf@magwerks.com writes:
> My i humbly suggest adding notes to the create  table clone ( original
> including all).

Done at
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=46b773d4fe0f0c880a1073cb5366efa02efa8ef8

> Also suggest adding  the same comment to the Insert on conflict
> stating.  Tables that are cloned with (including all ) the constraint
> is not automatically recreated with the same name so the constraint
> needs to be manually re-created with that specific name for the temp
> table.

I think that's overkill, considering that LIKE is such a minor feature
that it's not even mentioned anywhere except this one part of the
CREATE TABLE man page.

> Additionally could throw a notice that when a table is cloned with the
> copy indexes or constraints,  stating names are not preserved and may
> cause issues with Upsert on conflict.

And that's right around the bend.  I realize you're annoyed at the moment,
but nannying NOTICE messages are something nobody likes for long.

            regards, tom lane