Project

General

Profile

Bug #28932

Host query ends up to PG::SyntaxError: ERROR: subquery has too many columns since Update to Foreman 1.24.2

Added by danielwieberneit please_edit_me 4 months ago. Updated 4 months ago.

Status:
Closed
Priority:
Normal
Category:
Search
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Fixed in Releases:
Found in Releases:

Description

Host query is: factor.tier ~ lxstage

PG::SyntaxError: ERROR: subquery has too many columns LINE 16: ))) AND ((hosts.id in (SELECT "hosts"."id", "hosts"."id" AS ... ^ : SELECT DISTINCT "hosts"."name" AS alias_0, "hosts"."id" FROM "hosts" LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id" AND (hostgroups.id IN (346,285,227,156,367,410,259,166,386,297,228,419,430,318,429,400,432,291,336,426,412,431,148,176,175,169,368,352,294,362,174,433,361,230,409,130,303,414,223,224,147,162,155,417,153,365,257,413,418,231,165,40,355,353,149,157,422,327,261,159,354,256,152,366,371,229,330,415,425,385,173,333,421,273,359,358,1,428,321,324,146,306,424,364,420,168,416,243,427,226,167,218,158,360,363,411,341)) LEFT OUTER JOIN "fact_values" ON "fact_values"."host_id" = "hosts"."id" AND "fact_values"."fact_name_id" = $1 LEFT OUTER JOIN "monitoring_results" ON "monitoring_results"."host_id" = "hosts"."id" LEFT OUTER JOIN "smart_proxies" ON "smart_proxies"."id" = "hosts"."monitoring_proxy_id" AND (smart_proxies.id IN (70,45,81,68,10,79,66,77,42,80,60,78,74,54,71,21,51,72,76,69,63,67)) LEFT OUTER JOIN "compute_resources" ON "compute_resources"."id" = "hosts"."compute_resource_id" AND (compute_resources.id IN (27,36,15,42,4,40,21,41,24,37)) LEFT OUTER JOIN "operatingsystems" ON "operatingsystems"."id" = "hosts"."operatingsystem_id" LEFT OUTER JOIN "environments" ON "environments"."id" = "hosts"."environment_id" AND (environments.id IN (2102,106,2218,2651,1337,2537,1682,2615,2490,1986,2650,1371,1821,2413,2554,1713,972,1387,1445,1364,2517,2306,2305,2609,1891,2617,431,2307,2480,2449,2165,1415,1365,2448,978,1620,1459,2330,1985,107,1741,1355,2576,2640,2419,1171,2321,2582,434,2653,2604,2624,6,975,759,900,2203,1622,2122,2644,1307,1370,1529,2628,2654,2612,2627,82,963,1530,867,1,1461,2652)) LEFT OUTER JOIN "models" ON "models"."id" = "hosts"."model_id" LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id" LEFT OUTER JOIN "tokens" ON "tokens"."host_id" = "hosts"."id" AND "tokens"."type" IN ('Token::Build') WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = $2 AND "hosts"."location_id" = $3 AND (((("hosts"."owner_type" = 'Usergroup') AND ("hosts"."owner_id" = '15')) OR ("hostgroups"."id" = '40') OR ("hostgroups"."id" = '288') OR ( hosts.owner_id IS NOT NULL AND ( ( hosts.owner_type = 'User' AND hosts.owner_id IN (520) ) OR ( hosts.owner_type = 'Usergroup' AND hosts.owner_id IN (NULL) ) ) ))) AND ((hosts.id in (SELECT "hosts"."id", "hosts"."id" AS t0_r0, "hosts"."name" AS t0_r1, "hosts"."last_compile" AS t0_r2, "hosts"."last_report" AS t0_r3, "hosts"."updated_at" AS t0_r4, "hosts"."created_at" AS t0_r5, "hosts"."root_pass" AS t0_r6, "hosts"."architecture_id" AS t0_r7, "hosts"."operatingsystem_id" AS t0_r8, "hosts"."environment_id" AS t0_r9, "hosts"."ptable_id" AS t0_r10, "hosts"."medium_id" AS t0_r11, "hosts"."build" AS t0_r12, "hosts"."comment" AS t0_r13, "hosts"."disk" AS t0_r14, "hosts"."installed_at" AS t0_r15, "hosts"."model_id" AS t0_r16, "hosts"."hostgroup_id" AS t0_r17, "hosts"."owner_id" AS t0_r18, "hosts"."owner_type" AS t0_r19, "hosts"."enabled" AS t0_r20, "hosts"."puppet_ca_proxy_id" AS t0_r21, "hosts"."managed" AS t0_r22, "hosts"."use_image" AS t0_r23, "hosts"."image_file" AS t0_r24, "hosts"."uuid" AS t0_r25, "hosts"."compute_resource_id" AS t0_r26, "hosts"."puppet_proxy_id" AS t0_r27, "hosts"."certname" AS t0_r28, "hosts"."image_id" AS t0_r29, "hosts"."organization_id" AS t0_r30, "hosts"."location_id" AS t0_r31, "hosts"."type" AS t0_r32, "hosts"."otp" AS t0_r33, "hosts"."realm_id" AS t0_r34, "hosts"."compute_profile_id" AS t0_r35, "hosts"."provision_method" AS t0_r36, "hosts"."grub_pass" AS t0_r37, "hosts"."discovery_rule_id" AS t0_r38, "hosts"."expired_on" AS t0_r39, "hosts"."global_status" AS t0_r40, "hosts"."lookup_value_matcher" AS t0_r41, "hosts"."pxe_loader" AS t0_r42, "hosts"."monitoring_proxy_id" AS t0_r43, "hosts"."rescue_mode" AS t0_r44, "hosts"."spacewalk_proxy_id" AS t0_r45, "hosts"."initiated_at" AS t0_r46, "hosts"."build_errors" AS t0_r47, "hostgroups"."id" AS t1_r0, "hostgroups"."name" AS t1_r1, "hostgroups"."created_at" AS t1_r2, "hostgroups"."updated_at" AS t1_r3, "hostgroups"."environment_id" AS t1_r4, "hostgroups"."operatingsystem_id" AS t1_r5, "hostgroups"."architecture_id" AS t1_r6, "hostgroups"."medium_id" AS t1_r7, "hostgroups"."ptable_id" AS t1_r8, "hostgroups"."root_pass" AS t1_r9, "hostgroups"."puppet_ca_proxy_id" AS t1_r10, "hostgroups"."use_image" AS t1_r11, "hostgroups"."image_file" AS t1_r12, "hostgroups"."ancestry" AS t1_r13, "hostgroups"."vm_defaults" AS t1_r14, "hostgroups"."subnet_id" AS t1_r15, "hostgroups"."domain_id" AS t1_r16, "hostgroups"."puppet_proxy_id" AS t1_r17, "hostgroups"."title" AS t1_r18, "hostgroups"."realm_id" AS t1_r19, "hostgroups"."compute_profile_id" AS t1_r20, "hostgroups"."grub_pass" AS t1_r21, "hostgroups"."lookup_value_matcher" AS t1_r22, "hostgroups"."subnet6_id" AS t1_r23, "hostgroups"."pxe_loader" AS t1_r24, "hostgroups"."monitoring_proxy_id" AS t1_r25, "hostgroups"."content_source_id" AS t1_r26, "hostgroups"."content_view_id" AS t1_r27, "hostgroups"."lifecycle_environment_id" AS t1_r28, "hostgroups"."kickstart_repository_id" AS t1_r29, "hostgroups"."spacewalk_proxy_id" AS t1_r30, "hostgroups"."description" AS t1_r31, "hostgroups"."compute_resource_id" AS t1_r32 FROM "hosts" INNER JOIN "fact_values" ON "fact_values"."host_id" = "hosts"."id" INNER JOIN "fact_names" ON "fact_names"."id" = "fact_values"."fact_name_id" LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id" AND (hostgroups.id IN (346,285,227,156,367,410,259,166,386,297,228,419,430,318,429,400,432,291,336,426,412,431,148,176,175,169,368,352,294,362,174,433,361,230,409,130,303,414,223,224,147,162,155,417,153,365,257,413,418,231,165,40,355,353,149,157,422,327,261,159,354,256,152,366,371,229,330,415,425,385,173,333,421,273,359,358,1,428,321,324,146,306,424,364,420,168,416,243,427,226,167,218,158,360,363,411,341)) WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" = 3 AND "hosts"."location_id" = 6 AND (((("hosts"."owner_type" = 'Usergroup') AND ("hosts"."owner_id" = '15')) OR ("hostgroups"."id" = '40') OR ("hostgroups"."id" = '288') OR ( hosts.owner_id IS NOT NULL AND ( ( hosts.owner_type = 'User' AND hosts.owner_id IN (520) ) OR ( hosts.owner_type = 'Usergroup' AND hosts.owner_id IN (NULL) ) ) ))) AND (fact_names.name = 'app_tier') AND (fact_values.value = 'lxdev')))) ORDER BY "hosts"."name" ASC LIMIT $4 OFFSET $5

Associated revisions

Revision 9d110d27 (diff)
Added by danielwieberneit please_edit_me 4 months ago

Fixes #28932 - prevent ERROR: subquery has too many columns

History

#1 Updated by Ondřej Ezr 4 months ago

Could you try to add `.except(:select)` like this:


in_query = Host.joins(:fact_values => :fact_name).except(:select).select(:id).

in `app/models/concerns/hostext/search.rb` line 218.

I believe it could help, if it does, I'll investigate further :)

#2 Updated by danielwieberneit please_edit_me 4 months ago

  • Subject changed from Host query ends up to PG::SyntaxError: ERROR: subquery has too many columns since Update to Foreman to Host query ends up to PG::SyntaxError: ERROR: subquery has too many columns since Update to Foreman 1.24.2

#3 Updated by Ondřej Ezr 4 months ago

  • Found in Releases 1.24.2 added

#4 Updated by Ondřej Ezr 4 months ago

  • Assignee changed from Ondřej Ezr to danielwieberneit please_edit_me

So we found out it is authorizer issue and figured what to do with it. Could you send the PR, please? :)

#5 Updated by The Foreman Bot 4 months ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/7417 added

#6 Updated by The Foreman Bot 4 months ago

  • Fixed in Releases 2.0.0 added

#7 Updated by danielwieberneit please_edit_me 4 months ago

  • Status changed from Ready For Testing to Closed

Also available in: Atom PDF