Skip to content

Instantly share code, notes, and snippets.

@thgreasi

thgreasi/(PR) PATCH v6 device translation bug fix - filter uuid and modifyid.sql Secret

Last active August 26, 2024 15:11
Show Gist options
  • Save thgreasi/7cf6eba5634e813a7e34a0832724de36 to your computer and use it in GitHub Desktop.
Save thgreasi/7cf6eba5634e813a7e34a0832724de36 to your computer and use it in GitHub Desktop.
Download ZIP
PATCH /v6/device?$filter=uuid nested SELECT $updateid change
Raw
(orignal) PATCH v6 device translation bug fix - filter uuid.sql
Running PATCH /v6/device?$filter=uuid%20eq%20%27f75efcfac0d24e2bb040158434662a4f%27
EXPLAIN (ANALYZE, COSTS, VERBOSE)
-- UPDATE "device"
-- SET "is pinned on-release" = 3072946
SELECT "id"
FROM "device"
WHERE "device"."id" IN ((
SELECT "device"."id"
FROM (
SELECT "device"."created at", "device"."modified at", "device"."id", "device"."actor", "device"."api heartbeat state", "device"."uuid", "device"."device name", "device"."is of-device type", "device"."belongs to-application", "device"."is online", "device"."last connectivity event", "device"."is connected to vpn", "device"."last vpn event", CAST(NULL AS VARCHAR(255)) AS "logs channel", "device"."public address", CAST(NULL AS VARCHAR(255)) AS "vpn address", "device"."ip address", "device"."mac address", "device"."is web accessible", "device"."memory usage", "device"."memory total", "device"."storage block device", "device"."storage usage", "device"."storage total", "device"."cpu usage", "device"."cpu temp", "device"."is undervolted", "device"."cpu id", "device"."is running-release", "device"."download progress", "device"."status", "device"."os version", "device"."os variant", CASE
WHEN NOT (
"device"."is active"
) THEN 'inactive'
WHEN "device"."provisioning state" = 'Post-Provisioning' THEN 'post-provisioning'
WHEN NOT (
"device"."is online"
)
AND "device"."last connectivity event" IS NULL
AND "device"."api heartbeat state" = 'unknown' THEN 'configuring'
WHEN NOT (
"device"."is online"
)
AND "device"."api heartbeat state" IN ('offline', 'unknown') THEN 'offline'
WHEN "device"."download progress" IS NOT NULL
AND "device"."status" = 'Downloading' THEN 'updating'
WHEN "device"."provisioning progress" IS NOT NULL THEN 'configuring'
WHEN EXISTS (
SELECT 1
FROM "image install" AS "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."download progress" IS NOT NULL
AND "image install"."status" = 'Downloading'
) THEN 'updating'
ELSE 'idle'
END AS "overall status", "device"."overall progress", "device"."supervisor version", "device"."provisioning progress", "device"."provisioning state", "device"."api port", "device"."is managed by-service instance", "device"."is pinned on-release" AS "should be running-release", "device"."should be operated by-release", CAST(NULL AS INTEGER) AS "is managed by-device", "device"."update status", "device"."last update status event", "device"."belongs to-user", "device"."latitude", "device"."longitude", "device"."custom latitude", "device"."custom longitude", "device"."location", "device"."is active", "device"."is frozen", "device"."should be managed by-release" AS "should be managed by-supervisor release"
FROM (
SELECT "device"."created at", "device"."modified at", "device"."id", "device"."actor", "device"."api heartbeat state", "device"."uuid", "device"."device name", "device"."is of-device type", "device"."belongs to-application", "device"."is online", "device"."last connectivity event", "device"."is connected to vpn", "device"."last vpn event", "device"."public address", "device"."ip address", "device"."mac address", "device"."memory usage", "device"."memory total", "device"."storage block device", "device"."storage usage", "device"."storage total", "device"."cpu usage", "device"."cpu temp", "device"."is undervolted", "device"."cpu id", "device"."is running-release", "device"."download progress", "device"."status", "device"."os version", "device"."os variant", "device"."supervisor version", "device"."provisioning progress", "device"."provisioning state", "device"."api port", "device"."is managed by-service instance", "device"."is pinned on-release", "device"."should be running-release", "device"."should be operated by-release", "device"."should be managed by-release", "device"."update status", "device"."last update status event", "device"."belongs to-user", "device"."latitude", "device"."longitude", "device"."custom latitude", "device"."custom longitude", "device"."location", "device"."is web accessible", "device"."is active", "device"."is frozen", "device"."overall status", "device"."overall progress"
FROM (
SELECT *, CASE
WHEN "device"."is pinned on-release" IS NOT NULL THEN "device"."is pinned on-release"
ELSE (
SELECT "application"."should be running-release"
FROM "application"
WHERE "application"."id" = "device"."belongs to-application"
)
END AS "should be running-release", (
SELECT (EXISTS (
SELECT 1
FROM "organization" AS "application.organization"
WHERE "application.organization"."id" = "application"."organization"
AND "application.organization"."has past due invoice since-date" IS NOT NULL
AND "application.organization"."has past due invoice since-date" < CURRENT_TIMESTAMP - INTERVAL '60 0:0:0.0'
)
OR ("application.subscription"."is for-plan" IS NULL
OR "application.subscription"."is for-plan" = 1)
AND NOT (
"application"."is public"
AND "application"."is discoverable"
)
AND (
SELECT COUNT(*)
FROM (
SELECT 1
FROM "device" AS "orgs_device"
JOIN "application" AS "orgs_device.application" ON "orgs_device.application"."id" = "orgs_device"."belongs to-application"
WHERE "application"."organization" = "orgs_device.application"."organization"
AND NOT (
"orgs_device.application"."is public"
AND "orgs_device.application"."is discoverable"
)
LIMIT 11
) AS "limited_orgs_device"
) > 10)
FROM "application" AS "application"
LEFT JOIN "subscription" AS "application.subscription" ON "application"."organization" = "application.subscription"."is for-organization"
AND "application.subscription"."starts on-date" <= CURRENT_TIMESTAMP
AND ("application.subscription"."ends on-date" IS NULL
OR "application.subscription"."ends on-date" > CURRENT_TIMESTAMP)
WHERE "device"."belongs to-application" = "application"."id"
) AS "is frozen", CASE
WHEN NOT (
"device"."is active"
) THEN 'inactive'
WHEN "device"."provisioning state" = 'Post-Provisioning' THEN 'post-provisioning'
WHEN NOT (
"device"."is online"
)
AND "device"."last connectivity event" IS NULL
AND "device"."api heartbeat state" = 'unknown' THEN 'configuring'
WHEN NOT (
"device"."is online"
)
AND "device"."api heartbeat state" IN ('offline', 'unknown') THEN 'disconnected'
WHEN "device"."api heartbeat state" IN ('online', 'timeout')
AND "device"."download progress" IS NOT NULL
AND "device"."status" = 'Downloading' THEN 'updating'
WHEN "device"."provisioning progress" IS NOT NULL THEN 'configuring'
WHEN "device"."api heartbeat state" IN ('online', 'timeout')
AND EXISTS (
SELECT 1
FROM "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."download progress" IS NOT NULL
AND "image install"."status" = 'Downloading'
) THEN 'updating'
WHEN ("device"."api heartbeat state" = 'timeout'
OR "device"."is online"
AND "device"."api heartbeat state" != 'online'
OR NOT (
"device"."is online"
)
AND "device"."api heartbeat state" = 'online'
AND COALESCE((
SELECT "device config variable"."value"
FROM "device config variable"
WHERE "device config variable"."device" = "device"."id"
AND "device config variable"."name" = 'RESIN_SUPERVISOR_VPN_CONTROL'
), (
SELECT "application config variable"."value"
FROM "application config variable"
WHERE "application config variable"."application" = "device"."belongs to-application"
AND "application config variable"."name" = 'RESIN_SUPERVISOR_VPN_CONTROL'
), 'not set') != 'false') THEN 'reduced-functionality'
ELSE 'operational'
END AS "overall status", CASE
WHEN NOT (
"device"."is active"
) THEN NULL
WHEN "device"."provisioning state" = 'Post-Provisioning' THEN "device"."provisioning progress"
WHEN NOT (
"device"."is online"
)
AND "device"."last connectivity event" IS NULL
AND "device"."api heartbeat state" = 'unknown' THEN "device"."provisioning progress"
WHEN NOT (
"device"."is online"
)
AND "device"."api heartbeat state" IN ('offline', 'unknown') THEN NULL
WHEN "device"."download progress" IS NOT NULL
AND "device"."status" = 'Downloading' THEN "device"."download progress"
WHEN "device"."provisioning progress" IS NOT NULL THEN "device"."provisioning progress"
WHEN EXISTS (
SELECT 1
FROM "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."download progress" IS NOT NULL
AND "image install"."status" = 'Downloading'
) THEN (
SELECT CAST(ROUND(AVG(COALESCE("image install"."download progress", 100))) AS INTEGER)
FROM "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."status" != 'deleted'
AND ("image install"."status" = 'Downloading'
OR "image install"."is provided by-release" = COALESCE("device"."is pinned on-release", (
SELECT "application"."should be running-release"
FROM "application"
WHERE "device"."belongs to-application" = "application"."id"
)))
)
ELSE NULL
END AS "overall progress"
FROM "device"
) AS "device"
WHERE EXISTS (
SELECT 1
FROM "application" AS "device.belongs to-application"
WHERE "device"."belongs to-application" = "device.belongs to-application"."id"
AND (EXISTS (
SELECT 1
FROM (
SELECT *, "device.belongs to-application.organization"."has past due invoice since-date" IS NOT NULL
AND "device.belongs to-application.organization"."has past due invoice since-date" < CURRENT_TIMESTAMP - INTERVAL '60 0:0:0.0' AS "is frozen", CASE
WHEN "device.belongs to-application.organization"."is billed with-stripe id" IS NOT NULL THEN 'v2'
WHEN "device.belongs to-application.organization"."billing account code" IS NOT NULL THEN 'v1'
ELSE 'v1'
END AS "is using-billing version"
FROM "organization" AS "device.belongs to-application.organization"
) AS "device.belongs to-application.organization"
WHERE "device.belongs to-application"."organization" = "device.belongs to-application.organization"."id"
AND EXISTS (
SELECT 1
FROM "organization membership" AS "dev.belongs to-application.organization.organization membership"
WHERE "device.belongs to-application.organization"."id" = "dev.belongs to-application.organization.organization membership"."is member of-organization"
AND EXISTS (
SELECT 1
FROM "user" AS "dev.belon to-appl.organization.organization membership.user"
WHERE "dev.belongs to-application.organization.organization membership"."user" = "dev.belon to-appl.organization.organization membership.user"."id"
AND ("dev.belon to-appl.organization.organization membership.user"."actor") IS NOT NULL AND ("dev.belon to-appl.organization.organization membership.user"."actor") = (1052551)
)
AND "dev.belongs to-application.organization.organization membership"."organization membership role" IN (3)
)
)
OR EXISTS (
SELECT 1
FROM "user-is member of-application" AS "device.belongs to-application.user-is member of-application"
WHERE "device.belongs to-application"."id" = "device.belongs to-application.user-is member of-application"."is member of-application"
AND EXISTS (
SELECT 1
FROM "user" AS "dev.belongs to-application.user-is member of-application.user"
WHERE "device.belongs to-application.user-is member of-application"."user" = "dev.belongs to-application.user-is member of-application.user"."id"
AND ("dev.belongs to-application.user-is member of-application.user"."actor") IS NOT NULL AND ("dev.belongs to-application.user-is member of-application.user"."actor") = (1052551)
)
AND "device.belongs to-application.user-is member of-application"."application membership role" IN (2, 3)
)
OR EXISTS (
SELECT 1
FROM "team application access" AS "device.belongs to-application.team application access"
WHERE "device.belongs to-application"."id" = "device.belongs to-application.team application access"."grants access to-application"
AND EXISTS (
SELECT 1
FROM "team" AS "device.belongs to-application.team application access.team"
WHERE "device.belongs to-application.team application access"."team" = "device.belongs to-application.team application access.team"."id"
AND EXISTS (
SELECT 1
FROM "team membership" AS "dev.belon to-appl.team application access.team.team membership"
WHERE "device.belongs to-application.team application access.team"."id" = "dev.belon to-appl.team application access.team.team membership"."is member of-team"
AND EXISTS (
SELECT 1
FROM "user" AS "dev.belon to-appl.tea appl access.team.team membership.user"
WHERE "dev.belon to-appl.team application access.team.team membership"."user" = "dev.belon to-appl.tea appl access.team.team membership.user"."id"
AND ("dev.belon to-appl.tea appl access.team.team membership.user"."actor") IS NOT NULL AND ("dev.belon to-appl.tea appl access.team.team membership.user"."actor") = (1052551)
)
)
)
AND "device.belongs to-application.team application access"."application membership role" IN (2, 3)
))
)
) AS "device"
) AS "device"
WHERE ("device"."uuid") IS NOT NULL AND ("device"."uuid") = ('edf00ed2302584d805211a09025dc9e1')
)) ;
QUERY PLAN
Nested Loop (cost=22.86..24.88 rows=1 width=4) (actual time=0.182..0.185 rows=1 loops=1)
Output: device.id
Inner Unique: true
-> HashAggregate (cost=22.43..22.44 rows=1 width=4) (actual time=0.162..0.164 rows=1 loops=1)
Output: device_1.id
Group Key: device_1.id
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.97..22.43 rows=1 width=4) (actual time=0.161..0.163 rows=1 loops=1)
Output: device_1.id
Inner Unique: true
-> Index Scan using device_uuid_idx on public.device device_1 (cost=0.55..2.57 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1)
Output: device_1."belongs to-application", device_1.id
Index Cond: ((device_1.uuid IS NOT NULL) AND (device_1.uuid = 'edf00ed2302584d805211a09025dc9e1'::text))
-> Index Scan using application_pkey on public.application (cost=0.42..19.85 rows=1 width=4) (actual time=0.145..0.147 rows=1 loops=1)
Output: application.id
Index Cond: (application.id = device_1."belongs to-application")
Filter: ((hashed SubPlan 2) OR (hashed SubPlan 4) OR (hashed SubPlan 6))
SubPlan 2
-> Nested Loop (cost=5.32..5.35 rows=1 width=4) (actual time=0.084..0.129 rows=6 loops=1)
Output: organization.id
Inner Unique: true
-> HashAggregate (cost=4.90..4.91 rows=1 width=4) (actual time=0.070..0.072 rows=6 loops=1)
Output: "organization membership"."is member of-organization"
Group Key: "organization membership"."is member of-organization"
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.84..4.90 rows=1 width=4) (actual time=0.038..0.066 rows=6 loops=1)
Output: "organization membership"."is member of-organization"
-> Index Scan using user_actor_idx on public."user" u (cost=0.42..2.44 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)
Output: u.id
Index Cond: ((u.actor IS NOT NULL) AND (u.actor = 1052551))
-> Index Scan using "organization membership_user_is member of-organization_key" on public."organization membership" (cost=0.42..2.44 rows=1 width=8) (actual time=0.022..0.049 rows=6 loops=1)
Output: "organization membership"."user", "organization membership"."is member of-organization"
Index Cond: ("organization membership"."user" = u.id)
Filter: ("organization membership"."organization membership role" = 3)
Rows Removed by Filter: 23
-> Index Only Scan using organization_pkey on public.organization (cost=0.42..0.44 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=6)
Output: organization.id
Index Cond: (organization.id = "organization membership"."is member of-organization")
Heap Fetches: 0
SubPlan 4
-> Nested Loop (cost=0.71..5.69 rows=1 width=4) (never executed)
Output: "user-is member of-application"."is member of-application"
-> Index Scan using user_actor_idx on public."user" u_1 (cost=0.42..2.44 rows=1 width=4) (never executed)
Output: u_1.id
Index Cond: ((u_1.actor IS NOT NULL) AND (u_1.actor = 1052551))
-> Index Scan using "user-is member of-application_user_is member of-application_key" on public."user-is member of-application" (cost=0.29..3.23 rows=2 width=8) (never executed)
Output: "user-is member of-application"."is member of-application", "user-is member of-application"."user"
Index Cond: ("user-is member of-application"."user" = u_1.id)
Filter: ("user-is member of-application"."application membership role" = ANY ('{2,3}'::integer[]))
SubPlan 6
-> Nested Loop (cost=4.35..4.63 rows=3 width=4) (never executed)
Output: "team application access"."grants access to-application"
-> HashAggregate (cost=4.07..4.08 rows=1 width=8) (never executed)
Output: team.id, "team membership"."is member of-team"
Group Key: team.id
-> Nested Loop (cost=4.03..4.07 rows=1 width=8) (never executed)
Output: team.id, "team membership"."is member of-team"
Inner Unique: true
-> HashAggregate (cost=3.75..3.76 rows=1 width=4) (never executed)
Output: "team membership"."is member of-team"
Group Key: "team membership"."is member of-team"
-> Nested Loop (cost=0.70..3.75 rows=1 width=4) (never executed)
Output: "team membership"."is member of-team"
-> Index Scan using user_actor_idx on public."user" u_2 (cost=0.42..2.44 rows=1 width=4) (never executed)
Output: u_2.id
Index Cond: ((u_2.actor IS NOT NULL) AND (u_2.actor = 1052551))
-> Index Only Scan using "team membership_user_is member of-team_key" on public."team membership" (cost=0.28..1.30 rows=1 width=8) (never executed)
Output: "team membership"."user", "team membership"."is member of-team"
Index Cond: ("team membership"."user" = u_2.id)
Heap Fetches: 0
-> Index Only Scan using team_pkey on public.team (cost=0.28..0.30 rows=1 width=4) (never executed)
Output: team.id
Index Cond: (team.id = "team membership"."is member of-team")
Heap Fetches: 0
-> Index Scan using "team application access_team_grants access to-application_key" on public."team application access" (cost=0.28..0.49 rows=6 width=8) (never executed)
Output: "team application access"."grants access to-application", "team application access".team
Index Cond: ("team application access".team = team.id)
Filter: ("team application access"."application membership role" = ANY ('{2,3}'::integer[]))
-> Index Only Scan using device_pkey on public.device (cost=0.43..2.45 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=1)
Output: device.id
Index Cond: (device.id = device_1.id)
Heap Fetches: 0
Query Identifier: -3325965906352203701
Planning Time: 7.298 ms
Execution Time: 0.386 ms
Raw
(PR) PATCH v6 device translation bug fix - filter uuid and modifyid.sql
Running PATCH /v6/device?$filter=uuid%20eq%20%27f75efcfac0d24e2bb040158434662a4f%27
EXPLAIN (ANALYZE, COSTS, VERBOSE)
-- UPDATE "device"
-- SET "is pinned on-release" = 3072946
SELECT "id"
FROM "device"
WHERE "device"."id" IN ((
SELECT "device"."$modifyid"
FROM (
SELECT "device"."created at", "device"."modified at", "device"."id", "device"."actor", "device"."api heartbeat state", "device"."uuid", "device"."device name", "device"."is of-device type", "device"."belongs to-application", "device"."is online", "device"."last connectivity event", "device"."is connected to vpn", "device"."last vpn event", CAST(NULL AS VARCHAR(255)) AS "logs channel", "device"."public address", CAST(NULL AS VARCHAR(255)) AS "vpn address", "device"."ip address", "device"."mac address", "device"."is web accessible", "device"."memory usage", "device"."memory total", "device"."storage block device", "device"."storage usage", "device"."storage total", "device"."cpu usage", "device"."cpu temp", "device"."is undervolted", "device"."cpu id", "device"."is running-release", "device"."download progress", "device"."status", "device"."os version", "device"."os variant", CASE
WHEN NOT (
"device"."is active"
) THEN 'inactive'
WHEN "device"."provisioning state" = 'Post-Provisioning' THEN 'post-provisioning'
WHEN NOT (
"device"."is online"
)
AND "device"."last connectivity event" IS NULL
AND "device"."api heartbeat state" = 'unknown' THEN 'configuring'
WHEN NOT (
"device"."is online"
)
AND "device"."api heartbeat state" IN ('offline', 'unknown') THEN 'offline'
WHEN "device"."download progress" IS NOT NULL
AND "device"."status" = 'Downloading' THEN 'updating'
WHEN "device"."provisioning progress" IS NOT NULL THEN 'configuring'
WHEN EXISTS (
SELECT 1
FROM "image install" AS "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."download progress" IS NOT NULL
AND "image install"."status" = 'Downloading'
) THEN 'updating'
ELSE 'idle'
END AS "overall status", "device"."overall progress", "device"."supervisor version", "device"."provisioning progress", "device"."provisioning state", "device"."api port", "device"."is managed by-service instance", "device"."is pinned on-release" AS "should be running-release", "device"."should be operated by-release", CAST(NULL AS INTEGER) AS "is managed by-device", "device"."update status", "device"."last update status event", "device"."belongs to-user", "device"."latitude", "device"."longitude", "device"."custom latitude", "device"."custom longitude", "device"."location", "device"."is active", "device"."is frozen", "device"."should be managed by-release" AS "should be managed by-supervisor release", "device"."$modifyid"
FROM (
SELECT "device"."created at", "device"."modified at", "device"."id", "device"."actor", "device"."api heartbeat state", "device"."uuid", "device"."device name", "device"."is of-device type", "device"."belongs to-application", "device"."is online", "device"."last connectivity event", "device"."is connected to vpn", "device"."last vpn event", "device"."public address", "device"."ip address", "device"."mac address", "device"."memory usage", "device"."memory total", "device"."storage block device", "device"."storage usage", "device"."storage total", "device"."cpu usage", "device"."cpu temp", "device"."is undervolted", "device"."cpu id", "device"."is running-release", "device"."download progress", "device"."status", "device"."os version", "device"."os variant", "device"."supervisor version", "device"."provisioning progress", "device"."provisioning state", "device"."api port", "device"."is managed by-service instance", "device"."is pinned on-release", "device"."should be running-release", "device"."should be operated by-release", "device"."should be managed by-release", "device"."update status", "device"."last update status event", "device"."belongs to-user", "device"."latitude", "device"."longitude", "device"."custom latitude", "device"."custom longitude", "device"."location", "device"."is web accessible", "device"."is active", "device"."is frozen", "device"."overall status", "device"."overall progress", "device"."$modifyid"
FROM (
SELECT *, CASE
WHEN "device"."is pinned on-release" IS NOT NULL THEN "device"."is pinned on-release"
ELSE (
SELECT "application"."should be running-release"
FROM "application"
WHERE "application"."id" = "device"."belongs to-application"
)
END AS "should be running-release", (
SELECT (EXISTS (
SELECT 1
FROM "organization" AS "application.organization"
WHERE "application.organization"."id" = "application"."organization"
AND "application.organization"."has past due invoice since-date" IS NOT NULL
AND "application.organization"."has past due invoice since-date" < CURRENT_TIMESTAMP - INTERVAL '60 0:0:0.0'
)
OR ("application.subscription"."is for-plan" IS NULL
OR "application.subscription"."is for-plan" = 1)
AND NOT (
"application"."is public"
AND "application"."is discoverable"
)
AND (
SELECT COUNT(*)
FROM (
SELECT 1
FROM "device" AS "orgs_device"
JOIN "application" AS "orgs_device.application" ON "orgs_device.application"."id" = "orgs_device"."belongs to-application"
WHERE "application"."organization" = "orgs_device.application"."organization"
AND NOT (
"orgs_device.application"."is public"
AND "orgs_device.application"."is discoverable"
)
LIMIT 11
) AS "limited_orgs_device"
) > 10)
FROM "application" AS "application"
LEFT JOIN "subscription" AS "application.subscription" ON "application"."organization" = "application.subscription"."is for-organization"
AND "application.subscription"."starts on-date" <= CURRENT_TIMESTAMP
AND ("application.subscription"."ends on-date" IS NULL
OR "application.subscription"."ends on-date" > CURRENT_TIMESTAMP)
WHERE "device"."belongs to-application" = "application"."id"
) AS "is frozen", CASE
WHEN NOT (
"device"."is active"
) THEN 'inactive'
WHEN "device"."provisioning state" = 'Post-Provisioning' THEN 'post-provisioning'
WHEN NOT (
"device"."is online"
)
AND "device"."last connectivity event" IS NULL
AND "device"."api heartbeat state" = 'unknown' THEN 'configuring'
WHEN NOT (
"device"."is online"
)
AND "device"."api heartbeat state" IN ('offline', 'unknown') THEN 'disconnected'
WHEN "device"."api heartbeat state" IN ('online', 'timeout')
AND "device"."download progress" IS NOT NULL
AND "device"."status" = 'Downloading' THEN 'updating'
WHEN "device"."provisioning progress" IS NOT NULL THEN 'configuring'
WHEN "device"."api heartbeat state" IN ('online', 'timeout')
AND EXISTS (
SELECT 1
FROM "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."download progress" IS NOT NULL
AND "image install"."status" = 'Downloading'
) THEN 'updating'
WHEN ("device"."api heartbeat state" = 'timeout'
OR "device"."is online"
AND "device"."api heartbeat state" != 'online'
OR NOT (
"device"."is online"
)
AND "device"."api heartbeat state" = 'online'
AND COALESCE((
SELECT "device config variable"."value"
FROM "device config variable"
WHERE "device config variable"."device" = "device"."id"
AND "device config variable"."name" = 'RESIN_SUPERVISOR_VPN_CONTROL'
), (
SELECT "application config variable"."value"
FROM "application config variable"
WHERE "application config variable"."application" = "device"."belongs to-application"
AND "application config variable"."name" = 'RESIN_SUPERVISOR_VPN_CONTROL'
), 'not set') != 'false') THEN 'reduced-functionality'
ELSE 'operational'
END AS "overall status", CASE
WHEN NOT (
"device"."is active"
) THEN NULL
WHEN "device"."provisioning state" = 'Post-Provisioning' THEN "device"."provisioning progress"
WHEN NOT (
"device"."is online"
)
AND "device"."last connectivity event" IS NULL
AND "device"."api heartbeat state" = 'unknown' THEN "device"."provisioning progress"
WHEN NOT (
"device"."is online"
)
AND "device"."api heartbeat state" IN ('offline', 'unknown') THEN NULL
WHEN "device"."download progress" IS NOT NULL
AND "device"."status" = 'Downloading' THEN "device"."download progress"
WHEN "device"."provisioning progress" IS NOT NULL THEN "device"."provisioning progress"
WHEN EXISTS (
SELECT 1
FROM "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."download progress" IS NOT NULL
AND "image install"."status" = 'Downloading'
) THEN (
SELECT CAST(ROUND(AVG(COALESCE("image install"."download progress", 100))) AS INTEGER)
FROM "image install"
WHERE "image install"."device" = "device"."id"
AND "image install"."status" != 'deleted'
AND ("image install"."status" = 'Downloading'
OR "image install"."is provided by-release" = COALESCE("device"."is pinned on-release", (
SELECT "application"."should be running-release"
FROM "application"
WHERE "device"."belongs to-application" = "application"."id"
)))
)
ELSE NULL
END AS "overall progress", "device"."id" AS "$modifyid"
FROM "device"
) AS "device"
WHERE EXISTS (
SELECT 1
FROM "application" AS "device.belongs to-application"
WHERE "device"."belongs to-application" = "device.belongs to-application"."id"
AND (EXISTS (
SELECT 1
FROM (
SELECT *, "device.belongs to-application.organization"."has past due invoice since-date" IS NOT NULL
AND "device.belongs to-application.organization"."has past due invoice since-date" < CURRENT_TIMESTAMP - INTERVAL '60 0:0:0.0' AS "is frozen", CASE
WHEN "device.belongs to-application.organization"."is billed with-stripe id" IS NOT NULL THEN 'v2'
WHEN "device.belongs to-application.organization"."billing account code" IS NOT NULL THEN 'v1'
ELSE 'v1'
END AS "is using-billing version"
FROM "organization" AS "device.belongs to-application.organization"
) AS "device.belongs to-application.organization"
WHERE "device.belongs to-application"."organization" = "device.belongs to-application.organization"."id"
AND EXISTS (
SELECT 1
FROM "organization membership" AS "dev.belongs to-application.organization.organization membership"
WHERE "device.belongs to-application.organization"."id" = "dev.belongs to-application.organization.organization membership"."is member of-organization"
AND EXISTS (
SELECT 1
FROM "user" AS "dev.belon to-appl.organization.organization membership.user"
WHERE "dev.belongs to-application.organization.organization membership"."user" = "dev.belon to-appl.organization.organization membership.user"."id"
AND ("dev.belon to-appl.organization.organization membership.user"."actor") IS NOT NULL AND ("dev.belon to-appl.organization.organization membership.user"."actor") = (1052551)
)
AND "dev.belongs to-application.organization.organization membership"."organization membership role" IN (3)
)
)
OR EXISTS (
SELECT 1
FROM "user-is member of-application" AS "device.belongs to-application.user-is member of-application"
WHERE "device.belongs to-application"."id" = "device.belongs to-application.user-is member of-application"."is member of-application"
AND EXISTS (
SELECT 1
FROM "user" AS "dev.belongs to-application.user-is member of-application.user"
WHERE "device.belongs to-application.user-is member of-application"."user" = "dev.belongs to-application.user-is member of-application.user"."id"
AND ("dev.belongs to-application.user-is member of-application.user"."actor") IS NOT NULL AND ("dev.belongs to-application.user-is member of-application.user"."actor") = (1052551)
)
AND "device.belongs to-application.user-is member of-application"."application membership role" IN (2, 3)
)
OR EXISTS (
SELECT 1
FROM "team application access" AS "device.belongs to-application.team application access"
WHERE "device.belongs to-application"."id" = "device.belongs to-application.team application access"."grants access to-application"
AND EXISTS (
SELECT 1
FROM "team" AS "device.belongs to-application.team application access.team"
WHERE "device.belongs to-application.team application access"."team" = "device.belongs to-application.team application access.team"."id"
AND EXISTS (
SELECT 1
FROM "team membership" AS "dev.belon to-appl.team application access.team.team membership"
WHERE "device.belongs to-application.team application access.team"."id" = "dev.belon to-appl.team application access.team.team membership"."is member of-team"
AND EXISTS (
SELECT 1
FROM "user" AS "dev.belon to-appl.tea appl access.team.team membership.user"
WHERE "dev.belon to-appl.team application access.team.team membership"."user" = "dev.belon to-appl.tea appl access.team.team membership.user"."id"
AND ("dev.belon to-appl.tea appl access.team.team membership.user"."actor") IS NOT NULL AND ("dev.belon to-appl.tea appl access.team.team membership.user"."actor") = (1052551)
)
)
)
AND "device.belongs to-application.team application access"."application membership role" IN (2, 3)
))
)
) AS "device"
) AS "device"
WHERE ("device"."uuid") IS NOT NULL AND ("device"."uuid") = ('edf00ed2302584d805211a09025dc9e1')
)) ;
QUERY PLAN
Nested Loop (cost=21.86..23.88 rows=1 width=4) (actual time=0.168..0.170 rows=1 loops=1)
Output: device.id
Inner Unique: true
-> HashAggregate (cost=21.43..21.44 rows=1 width=4) (actual time=0.152..0.154 rows=1 loops=1)
Output: device_1.id
Group Key: device_1.id
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.97..21.43 rows=1 width=4) (actual time=0.151..0.153 rows=1 loops=1)
Output: device_1.id
Inner Unique: true
-> Index Scan using device_uuid_idx on public.device device_1 (cost=0.55..2.57 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=1)
Output: device_1."belongs to-application", device_1.id
Index Cond: ((device_1.uuid IS NOT NULL) AND (device_1.uuid = 'edf00ed2302584d805211a09025dc9e1'::text))
-> Index Scan using application_pkey on public.application (cost=0.42..18.85 rows=1 width=4) (actual time=0.130..0.131 rows=1 loops=1)
Output: application.id
Index Cond: (application.id = device_1."belongs to-application")
Filter: ((hashed SubPlan 2) OR (hashed SubPlan 4) OR (hashed SubPlan 6))
SubPlan 2
-> Nested Loop (cost=5.32..5.35 rows=1 width=4) (actual time=0.095..0.114 rows=6 loops=1)
Output: organization.id
Inner Unique: true
-> HashAggregate (cost=4.90..4.91 rows=1 width=4) (actual time=0.074..0.075 rows=6 loops=1)
Output: "organization membership"."is member of-organization"
Group Key: "organization membership"."is member of-organization"
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.84..4.90 rows=1 width=4) (actual time=0.040..0.070 rows=6 loops=1)
Output: "organization membership"."is member of-organization"
-> Index Scan using user_actor_idx on public."user" u (cost=0.42..2.44 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)
Output: u.id
Index Cond: ((u.actor IS NOT NULL) AND (u.actor = 1052551))
-> Index Scan using "organization membership_user_is member of-organization_key" on public."organization membership" (cost=0.42..2.44 rows=1 width=8) (actual time=0.023..0.053 rows=6 loops=1)
Output: "organization membership"."user", "organization membership"."is member of-organization"
Index Cond: ("organization membership"."user" = u.id)
Filter: ("organization membership"."organization membership role" = 3)
Rows Removed by Filter: 23
-> Index Only Scan using organization_pkey on public.organization (cost=0.42..0.44 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=6)
Output: organization.id
Index Cond: (organization.id = "organization membership"."is member of-organization")
Heap Fetches: 0
SubPlan 4
-> Nested Loop (cost=0.71..5.69 rows=1 width=4) (never executed)
Output: "user-is member of-application"."is member of-application"
-> Index Scan using user_actor_idx on public."user" u_1 (cost=0.42..2.44 rows=1 width=4) (never executed)
Output: u_1.id
Index Cond: ((u_1.actor IS NOT NULL) AND (u_1.actor = 1052551))
-> Index Scan using "user-is member of-application_user_is member of-application_key" on public."user-is member of-application" (cost=0.29..3.23 rows=2 width=8) (never executed)
Output: "user-is member of-application"."is member of-application", "user-is member of-application"."user"
Index Cond: ("user-is member of-application"."user" = u_1.id)
Filter: ("user-is member of-application"."application membership role" = ANY ('{2,3}'::integer[]))
SubPlan 6
-> Nested Loop (cost=4.35..4.63 rows=3 width=4) (never executed)
Output: "team application access"."grants access to-application"
-> HashAggregate (cost=4.07..4.08 rows=1 width=8) (never executed)
Output: team.id, "team membership"."is member of-team"
Group Key: team.id
-> Nested Loop (cost=4.03..4.07 rows=1 width=8) (never executed)
Output: team.id, "team membership"."is member of-team"
Inner Unique: true
-> HashAggregate (cost=3.75..3.76 rows=1 width=4) (never executed)
Output: "team membership"."is member of-team"
Group Key: "team membership"."is member of-team"
-> Nested Loop (cost=0.70..3.75 rows=1 width=4) (never executed)
Output: "team membership"."is member of-team"
-> Index Scan using user_actor_idx on public."user" u_2 (cost=0.42..2.44 rows=1 width=4) (never executed)
Output: u_2.id
Index Cond: ((u_2.actor IS NOT NULL) AND (u_2.actor = 1052551))
-> Index Only Scan using "team membership_user_is member of-team_key" on public."team membership" (cost=0.28..1.30 rows=1 width=8) (never executed)
Output: "team membership"."user", "team membership"."is member of-team"
Index Cond: ("team membership"."user" = u_2.id)
Heap Fetches: 0
-> Index Only Scan using team_pkey on public.team (cost=0.28..0.30 rows=1 width=4) (never executed)
Output: team.id
Index Cond: (team.id = "team membership"."is member of-team")
Heap Fetches: 0
-> Index Scan using "team application access_team_grants access to-application_key" on public."team application access" (cost=0.28..0.49 rows=6 width=8) (never executed)
Output: "team application access"."grants access to-application", "team application access".team
Index Cond: ("team application access".team = team.id)
Filter: ("team application access"."application membership role" = ANY ('{2,3}'::integer[]))
-> Index Only Scan using device_pkey on public.device (cost=0.43..2.45 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)
Output: device.id
Index Cond: (device.id = device_1.id)
Heap Fetches: 0
Query Identifier: -207293138453856581
Planning Time: 7.299 ms
Execution Time: 0.410 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment