Thread

1. BUG #18189: Value partitioned tables: Upd ERROR: more than one row returned by a subquery used as an expression

   PG Bug reporting form <noreply@postgresql.org> — 2023-11-10T11:07:20Z

   The following bug has been logged on the website:
   
   Bug reference:      18189
   Logged by:          Hans Buschmann
   Email address:      buschmann@nidsa.net
   PostgreSQL version: 16.1
   Operating system:   Fedora 39 x86-64 64bit, also on Win64
   Description:        
   
   Recently I worked with GAIA astronomical data to import them into my
   self-defined data tables.
   The table layout is quite old (pg11?) and still has some limitations of that
   time.
   
   I adopted the import statement to reflect the new data structure.
   
   On almost every try to import a source file I got the error:
   
   psql:source_to_arrays_ERR.sql:179: ERROR:  more than one row returned by a
   subquery used as an expression
   
   BUT: in my statements there are no subqueries used as an expression!
   
   The little data file GaiaSource_262134-265343_3E.csv successes on the update
   query and produces no error.
   
   The greater data file GaiaSource_131011-132722_1E.csv shows the error.
   
   The error was first detected on pg 16.0 but is also present in 16.1
   
   Steps to reproduce:
   
   1. save all attachments to a directory
   2. unzip the 2 data files
   3. manually execute the steps in steps_for_partition_err.sql (no full
   automation because of filepaths etc.)
   
   Background:
   
   My suspicion of a possible cause comes from the value partitioned tables.
   I have a newer version which uses range partitioned tables and a slight
   adjusted update statement. This succeeds in importing all of these data
   files without error.
   
   I think there is a hidden error in processing this statement, which seems
   generally correct from a users perspective as shown by the little data
   set.
   
   PS: since https://www.postgresql.org/account/submitbug/ does not allow
   attachments, they are put in a followup message!
   
   Thank you for investigating!
   
   Hans Buschmann
   
   

2. Re: BUG #18189: Value partitioned tables: Upd ERROR: more than one row returned by a subquery used as an expression

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-11-10T15:18:55Z

   PG Bug reporting form <noreply@postgresql.org> writes:
   > On almost every try to import a source file I got the error:
   > psql:source_to_arrays_ERR.sql:179: ERROR:  more than one row returned by a
   > subquery used as an expression
   > BUT: in my statements there are no subqueries used as an expression!
   
   It's probably there somewhere ...
   
   > Steps to reproduce:
   > 1. save all attachments to a directory
   
   Sadly, there are no attachments here; I don't think our bug-submission
   webform can handle them.  When you need attachments, I'd suggest just
   mailing the pgsql-bugs list directly without bothering with the webform.
   
   			regards, tom lane
   
   
   
   

3. AW: BUG #18189: Value partitioned tables: Upd ERROR: more than one row returned by a subquery used as an expression

   Hans Buschmann <buschmann@nidsa.net> — 2023-11-11T08:54:40Z

   Hello Tom,
   
   >Sadly, there are no attachments here; I don't think our bug-submission
   >webform can handle them.  When you need attachments, I'd suggest just
   >mailing the pgsql-bugs list directly without bothering with the webform.
   
   The situation seems much worse in this area:
   
   I sent a "reply to all" mail after I received the Bug-reporting mail (WHY after a longer time of clearance??) yesterday at 15:50 CET.
   It contained all 5 attachments just under 10 MB and I received the the copy sent to myself. So I thought all is OK.
   
   Today I noticed,  that this mail doesn't appear in buglist #18189 and totally disappeared for the community !!!
   
   It seems there are mechanisms behind the scenes, that are not documented nor obvious for gentle users trying to ameliorate the software...
   
   Now I resend the attachments in 2 mails
   
   (I repackaged the big file to bz2 format)
   
   Best regards
   
   Hans Buschmann
   

4. Re: AW: BUG #18189: Value partitioned tables: Upd ERROR: more than one row returned by a subquery used as an expression

   Tom Lane <tgl@sss.pgh.pa.us> — 2023-11-11T23:43:26Z

   Hans Buschmann <buschmann@nidsa.net> writes:
   > I sent a "reply to all" mail after I received the Bug-reporting mail (WHY after a longer time of clearance??) yesterday at 15:50 CET.
   > It contained all 5 attachments just under 10 MB and I received the the copy sent to myself. So I thought all is OK.
   > Today I noticed,  that this mail doesn't appear in buglist #18189 and totally disappeared for the community !!!
   > It seems there are mechanisms behind the scenes, that are not documented nor obvious for gentle users trying to ameliorate the software...
   
   I think those messages are just hung up in moderation.  If you're
   not subscribed to pgsql-bugs, whatever you send will be held for
   moderator approval (a sadly-necessary spam defense).
   
   Anyway, the error is coming out of this bit:
   
   ,qoffs as (
   select
    ...
   ,(((((ra -(select ra_base from qsum))*1000000000000)::bigint)>>15)&((1::bigint<<31)-1))::int as ira
    ...
   
   "(select ra_base from qsum)" certainly is a "sub-select used as an
   expression", and I guess the difference between your smaller and
   larger datasets is how many rows the qsum CTE produces.  If I'm
   reading it right, that'll produce one row for each distinct value
   of "(source_id >> 35+12)&((1<<16)-1)".
   
   			regards, tom lane