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 1oCPam-0002tb-NY for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Jul 2022 17:59:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oCPak-0006KJ-Gp for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Jul 2022 17:59:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCPak-0006KA-3l for pgsql-hackers@lists.postgresql.org; Fri, 15 Jul 2022 17:59:06 +0000 Received: from mail-qk1-x731.google.com ([2607:f8b0:4864:20::731]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oCPad-0001tF-0l for pgsql-hackers@postgresql.org; Fri, 15 Jul 2022 17:59:05 +0000 Received: by mail-qk1-x731.google.com with SMTP id o21so4122528qkm.10 for ; Fri, 15 Jul 2022 10:58:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=FYi2NR3pO8XRX9kvBw+i/fCHIqT0k+HD5yi2pM0fisw=; b=f5dFHDFH0uvV5dFP5A7PvJzqycVGj987VvCYGFd5kA2z/sQKkBSG5+pARkrGMbkKSB Brsl/fv+LvYyR4KgFd/nHQTeL93Uz9LMtdbiJhQ49XDhnr6GFQA6SuJ5Rj5ir9MWJq1C iCS7qvPssr/regmwBiib6zaGpJTvrjlVn+9PExkjWNs/TrRAI+sKZZxL0Wh2/tTXZTEU D6alTlDCfkkY/ltNv4W8GjZOtUTO/iylle+VkjocqyYVteNEHzzidibtlfz31E2cYzxA E8EtYKO6kc7EKAzW9zudtCT2dvdokRnPTgIOj+o6NbUcmbuNiTmUoAfmzWCtUk6dO372 /aDg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=FYi2NR3pO8XRX9kvBw+i/fCHIqT0k+HD5yi2pM0fisw=; b=B6geenfcqpPyWQytjVxdm6M+9x9O1wZ4MkjrOaWdTSxO/fEIKr6XVjTBDSUllTISlv MvrrCTESZZCApdv38taHUkQ3/8G0QHjqFIVJa5pAthBzfdfsfJiwkQhU8+JbXEWfyicD 71V3j3FpzbxKF/rb4TavhIr+BvANWcT5s9LFbRNMw/CtFFOxJbYNgbpQ6V+TZat71Z4C g2m9OJRxdPG3JgLDBj7igjeZJbTe6YW1CHrwn1l2CETUfIp5XEPhmwNS1a37Gt5JTmXC +ojTY83i3rKqnOjEptn9KxYBMckyE6e91H9Z7deD6s++KuoahbieQvedcKtiMJjStqRH R0FA== X-Gm-Message-State: AJIora84wgN1da8cFZEjcSp+IPQiCBQF+epF0lEAZCqhOjTX+cDiF5X6 aeW50PQoBbGIPTk8KVhtZZZCiA== X-Google-Smtp-Source: AGRyM1ui0XyxFLH8/tIChILYm5UFQ7GfQApZDMwH35sc4YdnglFg2iVeRB7L1UfybLFG/ZincxdMzQ== X-Received: by 2002:a05:620a:712:b0:6b5:d977:bebb with SMTP id 18-20020a05620a071200b006b5d977bebbmr19447qkc.552.1657907936367; Fri, 15 Jul 2022 10:58:56 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id u12-20020a05620a454c00b006a716fed4d6sm4657175qkp.50.2022.07.15.10.58.55 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 15 Jul 2022 10:58:55 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id BC17E800B00; Fri, 15 Jul 2022 12:58:54 -0500 (CDT) Date: Fri, 15 Jul 2022 12:58:54 -0500 From: Justin Pryzby To: Matthias van de Meent Cc: Alvaro Herrera , pgsql-hackers@postgresql.org Subject: Re: MERGE and parsing with prepared statements Message-ID: <20220715175854.GM18011@telsasoft.com> References: <20220714162618.GH18011@telsasoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote: > On Thu, 14 Jul 2022, 18:26 Justin Pryzby, wrote: > > > > Why is $1 construed to be of type text ? > > The select statement that generates the row type of T `(select $1 CID, > $2 TxV) AS T` does not put type bounds on the input parameters, so it > remains `unknown` for the scope of that subselect. Once stored into > the row type, the type of that column defaults to text, as a row type > should not have 'unknown'-typed columns. Thanks for looking into it. I see now that the same thing can happen with "ON CONFLICT" if used with a subselect. PREPARE p AS INSERT INTO t SELECT a FROM (SELECT $1 AS a)a ON CONFLICT (i) DO UPDATE SET i=excluded.i; ERROR: column "i" is of type integer but expression is of type text It seems a bit odd that it's impossible to use merge with prepared statements without specifically casting the source types (which I did now to continue my experiment). -- Justin