Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLWTA-0007gI-Uf for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 21:41:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLWT9-0003eO-JP for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 21:41:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLWT9-0003eF-4J for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 21:41:11 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLWT5-0002NV-EA for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 21:41:10 +0000 Received: by mail-pj1-x1030.google.com with SMTP id m11so5854418pji.0 for ; Fri, 27 Jan 2023 13:41:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=rgESs+WvTjGAOBaS30oYSpFjWlL2mFvAB+WkdbEbvp8=; b=o0YYUSVX+C/CzdduCLjhpnNate1DiXD9csvu5wLaYDkrcOaTHMIqOiXbcs6QiXVunk MtNIKgYGGlo681+4Fkun5KkXVaFsri3Y7LQsePKla6tmtENZUSMya2FlA+LJcn7XDTly iiYucPacpyAKlerNzbjNnzaF4AV1imCqXdRdlec4icPr/8vJdyPW4O/Oz+nIGlNduPnt GIX14eB+rPwpWYe696q2I26JUYuIF1Fq+JNo0FlKnm/h+SW5RoDVsOiIJDFgbZApsona pWVmjPd7WXSTbrPfUdzDqU4d1Py/O7owQJNGg2ir0T1HGqcUMtXGPe3D1d0oH2mu9qYY LTCQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=rgESs+WvTjGAOBaS30oYSpFjWlL2mFvAB+WkdbEbvp8=; b=Quq3qJ3zOiOHSpb2dQqHPxsC9DAbHzBYikrZPe2HHfJtdj34uZf0vK3t1TWghHgvbs 8MRuaMzVMx/AZJtkE3oa7gOsDoxwY4ddQYpFMncDg51zcUIbmXG1plP9gAYfUP4IOJiU +7QKZvX74uj6x1AaRg5pCnXsrmyQrE8ZMztLfYNHS4Uz6ZY8XJ2lp0dpDZ9Gd0r4nSwE 58bS/KS5Utijd/Io8eNiFc/yQ88bxMLhjsu2IFNFYff3f+wnqpX0nXcY7VWKaX5/QGvR jR/TLmmyTn/ojYm2hFZLlGvwc1cC7usBFXEjXK2uCCumAwq6TkIzObtkc32UPvQs9a5A VYLw== X-Gm-Message-State: AFqh2kptX6EPF5k1OXHwad0EvcGrg3hhkZKLwHs+15f0LjrMLMR/l7+O BMUvAMePkWm3ayRO3N/7/IObFpnx6RM= X-Google-Smtp-Source: AMrXdXtnxwfwzb/+dA4oL/XmyJw5DUYegjwv71KGdZXCemUTitpKF+n2GkTQ4kpjh80sbYFTtyzM7w== X-Received: by 2002:a05:6a21:998c:b0:a6:713d:4343 with SMTP id ve12-20020a056a21998c00b000a6713d4343mr57841314pzb.5.1674855666158; Fri, 27 Jan 2023 13:41:06 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:fea0:6247:5eca:cde2? ([2601:681:5a00:f790:fea0:6247:5eca:cde2]) by smtp.gmail.com with ESMTPSA id v25-20020a63b959000000b004b1b9e23790sm2729908pgo.92.2023.01.27.13.41.05 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 27 Jan 2023 13:41:05 -0800 (PST) Content-Type: multipart/alternative; boundary="------------CEyij0cL4AzlbOVobHx9zZPT" Message-ID: <4651a0e5-9ef4-f09f-067a-36907e34a1e4@gmail.com> Date: Fri, 27 Jan 2023 14:41:04 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: nextval per counted Content-Language: en-CA To: "David G. Johnston" Cc: "pgsql-generallists.postgresql.org" References: <71aa4ae1-f789-485a-ae58-2acec9aa2df0@gmail.com> <68161326-65af-ee8b-0c9e-be5d8f0b4f7b@gmail.com> From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------CEyij0cL4AzlbOVobHx9zZPT Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/27/23 14:31, David G. Johnston wrote: > On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent wrote: > > On 1/27/23 14:20, David G. Johnston wrote: >> On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent >> wrote: >> >> I'm trying to craft SQL to invoke a sequence nextval once per >> grouped value. >> >> >> This seems like a very unusual usage of nextval/sequences... >> >> with cleanup as ( >> select DISTINCT e.ma , coalesce(e.pa , >> 'fix') as pa from ... >> ), compute as ( >> select ma, pa, nextval(...) from cleanup >> ) >> select * from compute ... -- do whatever else you want >> >> So far I have this: >> >> with husb as( >> select e.ma , count(distinct coalesce(e.pa >> , nextval('egogen')::text)) as mates >> from emp_all_by3 e group by e.ma order by mates >> ) >> select mates, count(*) >> from husb >> group by mates order by mates desc; >> >> Your "order by mates" in the CTE is totally pointless and wasting >> resources. >> >> David J. >> > Yeah, it wasn't when that was the last bit executed... > > Seems asking a sequence for an id isn't too unusual?  Or are they > specifically intended for primary keys? > > > Yes, their design is excellent for surrogate primary keys.  I don't > even know what to call what you are doing but it isn't that.  It seems > like some form of counting but stuff like "row_number" and "count" > perform that function.  I don't think I'd trust using them as a > counter...for that I'd do counting in the query then have a separate > aspect, in user-space, not the system catalogs, that increments a counter. > > David J. > Yeah, in this case I'm actually trying to assign an id in place of a null, but only one id for all null for one ma.  One thought was to simply use the "ma" value but the table constraints are such that that cannot be. TL/DR This is pedigree  ego/ma/pa triplet work.  Set of ego with a mother and missing father can be considered a sibship (one mother/father pair) or a "half-sibship" where each child get assigned a unique father (one mother/N fathers).  In that latter case, the sequence works perfectly.  We don't allow for mother/mother, the analysis tools are not up for that (yet). Thanks for your time, rjs --------------CEyij0cL4AzlbOVobHx9zZPT Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 1/27/23 14:31, David G. Johnston wrote:
On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent <robjsargent@gmail.com> wrote:
On 1/27/23 14:20, David G. Johnston wrote:
On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent <robjsargent@gmail.com> wrote:
I'm trying to craft SQL to invoke a sequence nextval once per grouped value.


This seems like a very unusual usage of nextval/sequences...

with cleanup as (
  select DISTINCT e.ma, coalesce(e.pa, 'fix') as pa from ...
), compute as (
  select ma, pa, nextval(...) from cleanup
)
select * from compute ... -- do whatever else you want

So far I have this:

with husb as(
select e.ma, count(distinct coalesce(e.pa, nextval('egogen')::text)) as mates
from emp_all_by3 e group by e.ma order by mates
)
select mates, count(*)
from husb
group by mates order by mates desc;
Your "order by mates" in the CTE is totally pointless and wasting resources.

David J.

Yeah, it wasn't when that was the last bit executed...

Seems asking a sequence for an id isn't too unusual?  Or are they specifically intended for primary keys?


Yes, their design is excellent for surrogate primary keys.  I don't even know what to call what you are doing but it isn't that.  It seems like some form of counting but stuff like "row_number" and "count" perform that function.  I don't think I'd trust using them as a counter...for that I'd do counting in the query then have a separate aspect, in user-space, not the system catalogs, that increments a counter.

David J.

Yeah, in this case I'm actually trying to assign an id in place of a null, but only one id for all null for one ma.  One thought was to simply use the "ma" value but the table constraints are such that that cannot be. 

TL/DR
This is pedigree  ego/ma/pa triplet work.  Set of ego with a mother and missing father can be considered a sibship (one mother/father pair) or a "half-sibship" where each child get assigned a unique father
(one mother/N fathers).  In that latter case, the sequence works perfectly.  We don't allow for mother/mother, the analysis tools are not up for that (yet).


Thanks for your time,
rjs


--------------CEyij0cL4AzlbOVobHx9zZPT--