Bug 1106552

Summary:	Restore the image_templates_images constraint [blocked on enhancement bug 1106547 - move to PG 9.5]
Product:	[oVirt] ovirt-engine	Reporter:	Allon Mureinik <amureini>
Component:	BLL.Storage	Assignee:	Eyal Shenitzky <eshenitz>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Avihai <aefrat>
Severity:	medium	Docs Contact:
Priority:	unspecified
Version:	3.5.0	CC:	aefrat, amureini, bugs, laravot, lpeer, rbalakri, Rhev-m-bugs, scohen, srevivo, ylavi
Target Milestone:	ovirt-4.2.0	Flags:	rule-engine: ovirt-4.2+ rule-engine: planning_ack+ rule-engine: devel_ack+ ratamir: testing_ack+
Target Release:	4.2.0
Hardware:	Unspecified
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2017-12-20 11:06:41 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	Storage	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:	1106547
Bug Blocks:

Description Allon Mureinik 2014-06-09 14:57:34 UTC

Description of problem:
Due to a limitation in Postgres <= 9.3, the constraint image_templates_images was removed in order to solve bug 1077715.

This BZ is to track the need to restore it once RHEVM's database will be upgraded to 9.3 (tracked in bug 1106547).

Version-Release number of selected component (if applicable):
3.5.0

How reproducible:
100%

Steps to Reproduce:
1. echo '\d images' | psql -U engine -d engine

Actual results:
                                    Table "public.images"
     Column     |           Type           |                     Modifiers                      
----------------+--------------------------+----------------------------------------------------
 image_guid     | uuid                     | not null
 creation_date  | timestamp with time zone | not null
 size           | bigint                   | not null
 it_guid        | uuid                     | not null
 parentid       | uuid                     | 
 imagestatus    | integer                  | default 0
 lastmodified   | timestamp with time zone | 
 vm_snapshot_id | uuid                     | 
 volume_type    | integer                  | not null default 2
 volume_format  | integer                  | not null default 4
 image_group_id | uuid                     | 
 _create_date   | timestamp with time zone | default ('now'::text)::timestamp without time zone
 _update_date   | timestamp with time zone | 
 active         | boolean                  | not null default false
Indexes:
    "pk_images" PRIMARY KEY, btree (image_guid)
Foreign-key constraints:
    "image_templates_images" FOREIGN KEY (it_guid) REFERENCES images(image_guid)
Referenced by:
    TABLE "disk_image_dynamic" CONSTRAINT "fk_disk_image_dynamic_images" FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE
    TABLE "image_storage_domain_map" CONSTRAINT "fk_image_storage_domain_map_images" FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE

Expected results:
                                    Table "public.images"
     Column     |           Type           |                     Modifiers                      
----------------+--------------------------+----------------------------------------------------
 image_guid     | uuid                     | not null
 creation_date  | timestamp with time zone | not null
 size           | bigint                   | not null
 it_guid        | uuid                     | not null
 parentid       | uuid                     | 
 imagestatus    | integer                  | default 0
 lastmodified   | timestamp with time zone | 
 vm_snapshot_id | uuid                     | 
 volume_type    | integer                  | not null default 2
 volume_format  | integer                  | not null default 4
 image_group_id | uuid                     | 
 _create_date   | timestamp with time zone | default ('now'::text)::timestamp without time zone
 _update_date   | timestamp with time zone | 
 active         | boolean                  | not null default false
Indexes:
    "pk_images" PRIMARY KEY, btree (image_guid)
Foreign-key constraints:
    "image_templates_images" FOREIGN KEY (it_guid) REFERENCES images(image_guid)
Referenced by:
    TABLE "disk_image_dynamic" CONSTRAINT "fk_disk_image_dynamic_images" FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE
    TABLE "image_storage_domain_map" CONSTRAINT "fk_image_storage_domain_map_images" FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE
    TABLE "images" CONSTRAINT "image_templates_images" FOREIGN KEY (it_guid) REFERENCES images(image_guid)

Additional info:

Comment 1 Allon Mureinik 2016-01-20 17:01:48 UTC

I'm pushing this out since bug 1106547 won't be available in 4.0's timeline.

Comment 2 Red Hat Bugzilla Rules Engine 2017-01-09 09:15:19 UTC

This request has been proposed for two releases. This is invalid flag usage. The ovirt-future release flag has been cleared. If you wish to change the release flag, you must clear one release flag and then set the other release flag to ?.

Comment 3 Avihai 2017-10-15 08:32:02 UTC

Hi Alon ,

This is what I get , it looks close to expected results with some diff's .

Please confirm this is OK/NOT.

psql (9.5.7)
ovirt-engine-4.2.0-0.0.master.20170925172449.git9d850a9


output :
-bash-4.2$ echo '\d images' | psql -U postgres -d engine
                                         Table "public.images"
        Column         |           Type           |                     Modifiers                      
-----------------------+--------------------------+----------------------------------------------------
 image_guid            | uuid                     | not null
 creation_date         | timestamp with time zone | not null
 size                  | bigint                   | not null
 it_guid               | uuid                     | not null
 parentid              | uuid                     | 
 imagestatus           | integer                  | default 0
 lastmodified          | timestamp with time zone | 
 vm_snapshot_id        | uuid                     | 
 volume_type           | integer                  | not null default 2
 volume_format         | integer                  | not null default 4
 image_group_id        | uuid                     | 
 _create_date          | timestamp with time zone | default ('now'::text)::timestamp without time zone
 _update_date          | timestamp with time zone | 
 active                | boolean                  | not null default false
 volume_classification | smallint                 | 
 qcow_compat           | integer                  | default 0
Indexes:
    "pk_images" PRIMARY KEY, btree (image_guid)
    "idx_images_images_group_id" btree (image_group_id)
Referenced by:
    TABLE "image_storage_domain_map" CONSTRAINT "fk_image_storage_domain_map_images" FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE
Triggers:
    delete_disk_image_dynamic_for_image BEFORE DELETE ON images FOR EACH ROW EXECUTE PROCEDURE fn_image_deleted()

Comment 4 Allon Mureinik 2017-10-15 14:41:44 UTC

(In reply to Avihai from comment #3)
> Hi Alon ,
> 
> This is what I get , it looks close to expected results with some diff's .
> 
> Please confirm this is OK/NOT.
This is not OK - the image_templates_images constraint is still missing.

BUT:

> 
> psql (9.5.7)
> ovirt-engine-4.2.0-0.0.master.20170925172449.git9d850a9
This is a build from Sep 25th - the BZ was only moved to MODIFIED on Sep 28. Can you retest this with a newer build please?

Thanks!

Comment 5 Avihai 2017-10-15 15:26:56 UTC

(In reply to Allon Mureinik from comment #4)
> (In reply to Avihai from comment #3)
> > Hi Alon ,
> > 
> > This is what I get , it looks close to expected results with some diff's .
> > 
> > Please confirm this is OK/NOT.
> This is not OK - the image_templates_images constraint is still missing.
> 
> BUT:
> 
> > 
> > psql (9.5.7)
> > ovirt-engine-4.2.0-0.0.master.20170925172449.git9d850a9
> This is a build from Sep 25th - the BZ was only moved to MODIFIED on Sep 28.
> Can you retest this with a newer build please?
> 
> Thanks!

Output from latest ovirt-engine (13/10) , is this OK ? 

Output:
-bash-4.2$ echo '\d images' | psql -U postgres -d engine
                                         Table "public.images"
        Column         |           Type           |                     Modifiers                      
-----------------------+--------------------------+----------------------------------------------------
 image_guid            | uuid                     | not null
 creation_date         | timestamp with time zone | not null
 size                  | bigint                   | not null
 it_guid               | uuid                     | not null
 parentid              | uuid                     | 
 imagestatus           | integer                  | default 0
 lastmodified          | timestamp with time zone | 
 vm_snapshot_id        | uuid                     | 
 volume_type           | integer                  | not null default 2
 volume_format         | integer                  | not null default 4
 image_group_id        | uuid                     | 
 _create_date          | timestamp with time zone | default ('now'::text)::timestamp without time zone
 _update_date          | timestamp with time zone | 
 active                | boolean                  | not null default false
 volume_classification | smallint                 | 
 qcow_compat           | integer                  | default 0
Indexes:
    "pk_images" PRIMARY KEY, btree (image_guid)
    "idx_images_images_group_id" btree (image_group_id)
    "idx_images_it_guid" btree (it_guid)
Foreign-key constraints:
    "fk_image_templates_images" FOREIGN KEY (it_guid) REFERENCES images(image_guid)
Referenced by:
    TABLE "image_storage_domain_map" CONSTRAINT "fk_image_storage_domain_map_images" FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE
    TABLE "images" CONSTRAINT "fk_image_templates_images" FOREIGN KEY (it_guid) REFERENCES images(image_guid)
Triggers:
    delete_disk_image_dynamic_for_image BEFORE DELETE ON images FOR EACH ROW EXECUTE PROCEDURE fn_image_deleted()

Comment 6 Avihai 2017-10-16 05:39:45 UTC

Look good as the image_templates_images constraint is there but here its called "fk_image_templates_images" & not "image_templates_images" as in original bug description.

I assume its the same desired functionality with a different name but please verify this is so & I'll verify the bug

Comment 7 Allon Mureinik 2017-10-16 13:28:49 UTC

(In reply to Avihai from comment #6)
> Look good as the image_templates_images constraint is there but here its
> called "fk_image_templates_images" & not "image_templates_images" as in
> original bug description.
> 
> I assume its the same desired functionality with a different name but please
> verify this is so & I'll verify the bug

Yes, the name is indeed inconsequential. I think this can be verified.

Thanks!

Comment 8 Avihai 2017-10-16 14:07:40 UTC

verified on engine build 4.2.0-0.0.master.20171013142622.git15e767c.el7.centos

Comment 9 Sandro Bonazzola 2017-12-20 11:06:41 UTC

This bugzilla is included in oVirt 4.2.0 release, published on Dec 20th 2017.

Since the problem described in this bug report should be
resolved in oVirt 4.2.0 release, published on Dec 20th 2017, it has been closed with a resolution of CURRENT RELEASE.

If the solution does not work for you, please open a new bug report.