Prepared Statements

Hi, I am having a hard time pinning down which function creates a prepared statement. Say in some language I create a Prepared Statement and send it off. Before the first time I execute the prepared statement, which function is the one that 'creates' the prepared statement. In other words, which f

Re: Prepared Statements

EXECUTE statements do essentially the > same thing. > > In the server code, there is the plan cache. The plan cache tracks when > a plan needs to be invalidated and the query replanned. The handle to an > entry in the plan cache is a CachedPlanSource, which contains the SQL > orig

Re: Prepared Statements

wing and consider sending any follow-up fresh to the -general list. The behavior would be specific to "your favorite language" so you should ask there. Client-side drivers can do lots of things besides interacting directly with the server. The docs for the server cover what facil

Re: Prepared Statements

the server code, there is the plan cache. The plan cache tracks when a plan needs to be invalidated and the query replanned. The handle to an entry in the plan cache is a CachedPlanSource, which contains the SQL original and enough information to (re-)plan the query as needed. The plan cache ha

Improvements in prepared statements

Hello, some improvements in the prepared statements would facilitate their use from applications: - Use of table and column names in prepared statements. Example: select # from # where # = ?; - Use of arrays in prepared statements. Example: select # from article where id in

cursors with prepared statements

I have developed a patch that allows declaring cursors over prepared statements: DECLARE cursor_name CURSOR FOR prepared_statement_name [ USING param, param, ... ] This is an SQL standard feature. ECPG already supports it (with different internals

log_planner_stats and prepared statements

I have found that log_planner_stats only outputs stats until the generic plan is chosen. For example, if you run the following commands: SET client_min_messages = 'log'; SET log_planner_stats = TRUE; PREPARE e AS SELECT relkind FROM pg_class WHERE relname = $1 ORD

Re: Improvements in prepared statements

Hi po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez napsal: > Hello, some improvements in the prepared statements would facilitate > their use from applications: > > - Use of table and column names in prepared statements. > > Example: select # from # where # = ?; >

Re: Improvements in prepared statements

regards.Alejandro Sánchez. El lun, 01-03-2021 a las 15:31 +0100, Pavel Stehule escribió: > Hi > > po 1. 3. 2021 v 15:20 odesílatel Alejandro Sánchez < > a...@nexttypes.com> napsal: > > Hello, some improvements in the prepared statements would > > facilitate > &

Re: Improvements in prepared statements

wiz&ved=0ahUKEwjp27mTto_vAhXR8uAKHUIjA5U4FBDh1QMIDA&uact=5>. > It would be nice if it was included in plain > > SQL. > > I am very sceptical about it. What benefit do you expect? When you cannot reuse an execution plan, then there is not any benefit of this. Then you don't

Re: Improvements in prepared statements

The benefit is ease of use. One of the great advantages of prepared statements is nothaving to concatenate strings. The use of arrays would also be very useful. query("select " + column1 + "," + column2 from " " + table + " where id in (?), ids); VS

Re: Improvements in prepared statements

po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez napsal: > The benefit is ease of use. One of the great advantages of prepared > statements is not > > having to concatenate strings. The use of arrays would also be very useful. > > > query("select " + column1 +

Re: Improvements in prepared statements

po 1. 3. 2021 v 17:15 odesílatel Pavel Stehule napsal: > > > po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez > napsal: > >> The benefit is ease of use. One of the great advantages of prepared >> statements is not >> >> having to concatenate string

Re: Improvements in prepared statements

m> napsal: > > The benefit is ease of use. One of the great advantages of prepared > > statements is nothaving to concatenate strings. The use of arrays > > would also be very useful. > > query("select " + column1 + "," + column2 from " " + ta

Re: Improvements in prepared statements

; El lun, 01-03-2021 a las 17:15 +0100, Pavel Stehule escribió: > > > > po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez > napsal: > > The benefit is ease of use. O > > ne of the great advantages of prepared statements is not > > having to concatenate strings. The u

Re: Improvements in prepared statements

I have already implemented this in my Java project with some kind of SQL preprocessor. I leave the idea here in case more people are interested and PostgreSQL developers findit convenient to include it. It is just string concatenation but it is a sintactic sugar very useful in any SQL application.

Re: Improvements in prepared statements

napsal: > > po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez < > > a...@nexttypes.com> napsal: > > > The benefit is ease of use. One of the great advantages of > > > prepared statements is nothaving to concatenate strings. The use > > > of arrays would also

Re: Improvements in prepared statements

ent side issue. It is about design of client side API. Pavel > El lun, 01-03-2021 a las 17:21 +0100, Pavel Stehule escribió: > > > > po 1. 3. 2021 v 17:15 odesílatel Pavel Stehule > napsal: > > > > po 1. 3. 2021 v 17:08 odesílatel Alejandro Sánchez > naps

Bug in pgbench prepared statements

Hi, I noticed something that looks like a bug in pgbench when using the prepared protocol. pgbench assumes that all prepared statements are prepared correctly, even if they contain errors (e.g. syntax, column/table doesn't exist, etc.). My test script is just: SELECT one; The output

Re: cursors with prepared statements

On 07/06/18 22:42, Peter Eisentraut wrote: I have developed a patch that allows declaring cursors over prepared statements: DECLARE cursor_name CURSOR FOR prepared_statement_name [ USING param, param, ... ] This is an SQL standard feature. ECPG already

Re: cursors with prepared statements

On 11.07.18 19:07, Heikki Linnakangas wrote: >> One point worth pondering is how to pass the parameters of the prepared >> statements. The actual SQL standard syntax would be >> >> DECLARE cursor_name CURSOR FOR prepared_statement_name; >> OPEN cursor_name

Re: cursors with prepared statements

On Mon, Jul 16, 2018 at 8:56 AM, Peter Eisentraut wrote: >> The attached patch seems to do the trick, of allowing EXECUTE + USING. >> I'm not sure this is worth the trouble, though, since EXECUTE as a plain >> SQL command is a PostgreSQL-extension anyway. > > I think it's a PostgreSQL extension th

Re: cursors with prepared statements

On 16/07/18 15:56, Peter Eisentraut wrote: On 11.07.18 19:07, Heikki Linnakangas wrote: It's confusing, and risks conflicting with future additions to the standard. ECPG supports the actual standard syntax, with OPEN, right? So this wouldn't be consistent with ECPG, either. It would be consist

Re: cursors with prepared statements

On Fri, Jun 8, 2018 at 1:12 AM, Peter Eisentraut wrote: > I have developed a patch that allows declaring cursors over prepared > statements: > > DECLARE cursor_name CURSOR FOR prepared_statement_name >[ USING param, param, ... ] > > Th

Re: cursors with prepared statements

On 6/11/18 09:57, Amit Kapila wrote: > Sounds like a reasonable approach. Have you not considered using a > special OPEN syntax because there are some other forms of problems > with it? There is no OPEN command in direct SQL. Do you mean whether I have considered introducing an OPEN command? Ye

Re: cursors with prepared statements

On Mon, Jun 11, 2018 at 9:56 PM, Peter Eisentraut wrote: > On 6/11/18 09:57, Amit Kapila wrote: >> Sounds like a reasonable approach. Have you not considered using a >> special OPEN syntax because there are some other forms of problems >> with it? > > There is no OPEN command in direct SQL. Do y

Re: cursors with prepared statements

> On Wed, Jul 18, 2018 at 10:27 AM Heikki Linnakangas wrote: > > On 16/07/18 15:56, Peter Eisentraut wrote: > > On 11.07.18 19:07, Heikki Linnakangas wrote: > >> It's confusing, and risks conflicting with future additions to > >> the standard. ECPG supports the actual standard syntax, with OPEN, >

Re: cursors with prepared statements

> On Thu, Nov 22, 2018 at 11:11 AM Dmitry Dolgov <9erthali...@gmail.com> wrote: > > > On Wed, Jul 18, 2018 at 10:27 AM Heikki Linnakangas wrote: > > > > On 16/07/18 15:56, Peter Eisentraut wrote: > > > On 11.07.18 19:07, Heikki Linnakangas wrote: > > >> It's confusing, and risks conflicting with f

Inheritance, invalidations and prepared statements.

Hi hackers, Right now isolation test alter-table-4.spec fails if prepared statement is used: Whats going on: - There are two inherited tables "p" and "c1". - Session 1 starts transactions and drops inheritance - Session 2 prepares and executes statement which selects data from "p".  It is bloc

Re: log_planner_stats and prepared statements

Bruce Momjian writes: > I have found that log_planner_stats only outputs stats until the generic > plan is chosen. For example, if you run the following commands: Uh, well, the planner doesn't get run after that point ... regards, tom lane

Re: log_planner_stats and prepared statements

On Wed, Apr 17, 2019 at 12:04:35AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > I have found that log_planner_stats only outputs stats until the generic > > plan is chosen. For example, if you run the following commands: > > Uh, well, the planner doesn't get run after that point ... Yes,

Re: Deleting prepared statements from libpq.

On Fri, 16 Jun 2023 at 16:26, Craig Ringer wrote: > Nobody's implemented it. > > A patch to add PQclosePrepared and PQsendClosePrepared would be welcome. At > least, I think so... This might have been a pretty old thread. But I just took it upon me to implement these functions (or well I mostly

Re: Deleting prepared statements from libpq.

On Fri, Jun 16, 2023 at 11:28 PM Jelte Fennema wrote: > > On Fri, 16 Jun 2023 at 16:26, Craig Ringer wrote: > > Nobody's implemented it. > > > > A patch to add PQclosePrepared and PQsendClosePrepared would be welcome. At > > least, I think so... > > This might have been a pretty old thread. But

Re: Deleting prepared statements from libpq.

On Sat, 17 Jun 2023 at 15:34, jian he wrote: > I failed to link it. I don't know why. Sorry about that. I attached a new patch that allows linking to the new functions (I forgot to add the functions to exports.txt). This new patch also adds some basic tests for these new functions. v2-0001-Supp

Re: Deleting prepared statements from libpq.

On Sun, Jun 18, 2023 at 7:04 PM Jelte Fennema wrote: > > On Sat, 17 Jun 2023 at 15:34, jian he wrote: > > I failed to link it. I don't know why. > > Sorry about that. I attached a new patch that allows linking to the > new functions (I forgot to add the functions to exports.txt). This new > patch

Re: Deleting prepared statements from libpq.

On Sun, Jun 18, 2023 at 09:23:22PM +0800, jian he wrote: > previously I cannot link it. with > v2-0001-Support-sending-Close-messages-from-libpq.patch. now I can > compile it, link it, but then run time error. > same c program in the first email. > when I run it ./a.out, then error: > ./a.out: symb

Re: Deleting prepared statements from libpq.

On Sun, Jun 18, 2023 at 01:03:57PM +0200, Jelte Fennema wrote: > Sorry about that. I attached a new patch that allows linking to the > new functions (I forgot to add the functions to exports.txt). This new > patch also adds some basic tests for these new functions. I am okay with the arguments abo

Re: Deleting prepared statements from libpq.

now it works. /src/test/modules/libpq_pipeline/libpq_pipeline.c > > /* Now that it's closed we should get an error when describing */ > res = PQdescribePortal(conn, "cursor_one"); > if (PQresultStatus(res) != PGRES_FATAL_ERROR) > pg_fatal("expected COMMAND_OK, got %s", PQresStatus(PQresultStatus(r

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 01:57, Michael Paquier wrote: > +static int > +PQsendClose(PGconn *conn, char close_type, const char *close_target) > > Could it be better for this code path to issue an error if using a > non-supported close_type rather than sending it? Okay, you are > consistent with desc

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 04:52, jian he wrote: > > /* Now that it's closed we should get an error when describing */ > > res = PQdescribePortal(conn, "cursor_one"); > > if (PQresultStatus(res) != PGRES_FATAL_ERROR) > > pg_fatal("expected COMMAND_OK, got %s", PQresStatus(PQresultStatus(res))); > shou

Re: Deleting prepared statements from libpq.

On Mon, 19 Jun 2023 at 11:44, Jelte Fennema wrote: > Done Now with the actual attachment. PS. Another connection pooler (PgCat) now also supports prepared statements, but only using Close not DEALLOCATE: https://postgresml.org/blog/making-postgres-30-percent-faster-in-production F

Re: Deleting prepared statements from libpq.

On Mon, Jun 19, 2023 at 5:50 PM Jelte Fennema wrote: > > On Mon, 19 Jun 2023 at 11:44, Jelte Fennema wrote: > > Done > > Now with the actual attachment. > > PS. Another connection pooler (PgCat) now also supports prepared > statements, but only using Cl

Re: Deleting prepared statements from libpq.

ma wrote: > > > > On Mon, 19 Jun 2023 at 11:44, Jelte Fennema wrote: > > > Done > > > > Now with the actual attachment. > > > > PS. Another connection pooler (PgCat) now also supports prepared > > statements, but only using Close not DEALLOCATE

Re: Deleting prepared statements from libpq.

On Mon, Jun 19, 2023 at 02:49:44PM +0200, Jelte Fennema wrote: > On Mon, 19 Jun 2023 at 14:17, jian he wrote: >> I am not sure the following two following function comments are right > > They were incorrect indeed. Attached is a patch with those two updated. The amount of duplication between

Re: Deleting prepared statements from libpq.

On Tue, 20 Jun 2023 at 06:18, Michael Paquier wrote: > The amount of duplication between the describe and close paths > concerns me a bit. Should PQsendClose() and PQsendDescribe() be > merged into a single routine (say PQsendCommand), that uses a message > type for pqPutMsgStart and a queryclass

Re: Deleting prepared statements from libpq.

On Tue, Jun 20, 2023 at 01:42:13PM +0200, Jelte Fennema wrote: Thanks for updating the patch. > On Tue, 20 Jun 2023 at 06:18, Michael Paquier wrote: >> The amount of duplication between the describe and close paths >> concerns me a bit. Should PQsendClose() and PQsendDescribe() be >> merged int

Re: Deleting prepared statements from libpq.

On Fri, 23 Jun 2023 at 05:59, Michael Paquier wrote: > [...] > res = PQgetResult(conn); > if (res == NULL) > - pg_fatal("expected NULL result"); > + pg_fatal("expected non-NULL result"); > > This should check for the NULL-ness of the result returned for > PQclosePrepared() rath

Re: Deleting prepared statements from libpq.

On Fri, Jun 23, 2023 at 09:39:00AM +0200, Jelte Fennema wrote: > To be clear, it didn't actually change the behaviour. I only changed > the error message, since it said the exact opposite of what it was > expecting. I split this minor fix into its own commit now to clarify > that. I think it would

Re: Deleting prepared statements from libpq.

@Michael is anything else needed from my side? If not, I'll mark the commitfest entry as "Ready For Committer".

Re: Deleting prepared statements from libpq.

On Mon, Jul 03, 2023 at 02:33:55PM +0200, Jelte Fennema wrote: > @Michael is anything else needed from my side? If not, I'll mark the > commitfest entry as "Ready For Committer". Sure, feel free. I was planning to look at and play more with it. -- Michael signature.asc Description: PGP signatur

Re: Deleting prepared statements from libpq.

On Tue, Jul 04, 2023 at 08:28:40AM +0900, Michael Paquier wrote: > Sure, feel free. I was planning to look at and play more with it. Well, done. -- Michael signature.asc Description: PGP signature

Re: Deleting prepared statements from libpq.

On Tue, Jul 04, 2023 at 04:09:43PM +0900, Michael Paquier wrote: > On Tue, Jul 04, 2023 at 08:28:40AM +0900, Michael Paquier wrote: >> Sure, feel free. I was planning to look at and play more with it. > > Well, done. For the sake of completeness, as I forgot to send my notes. + if (PQsendClose

Re: Bug in pgbench prepared statements

On Wed Nov 29, 2023 at 7:38 PM CST, Lev Kokotov wrote: Patch attached, if there is any interest in fixing this small bug. I see prepareCommand() is called one more time in prepareCommandsInPipeline(). Should you also check the return value there? It may also be useful to throw this patch on

Re: Bug in pgbench prepared statements

> I see prepareCommand() is called one more time in > prepareCommandsInPipeline(). Should you also check the return value > there? Yes, good catch. New patch attached. > It may also be useful to throw this patch on the January commitfest if > no one else comes along to review/commit it. First ti

Re: Bug in pgbench prepared statements

On Thu, Nov 30, 2023 at 07:15:54PM -0800, Lev Kokotov wrote: >> I see prepareCommand() is called one more time in >> prepareCommandsInPipeline(). Should you also check the return value >> there? > > Yes, good catch. New patch attached. Agreed that this is not really helpful as it stands >> It ma

Re: Bug in pgbench prepared statements

> The patch you have sent does not apply cleanly on the master branch, > could you rebase please? Attached. PR against master also here , just to make sure it's mergeable . > Wouldn't it > better t

Re: Bug in pgbench prepared statements

On Fri, Dec 01, 2023 at 07:06:40PM -0800, Lev Kokotov wrote: > Attached. PR against master also here > , just to make sure it's > mergeable . Thanks for the updated patch. It looks sensible seen fr

Re: query logging of prepared statements

Sigh, resending to -hackers for real. On Fri, Feb 08, 2019 at 07:29:53AM -0600, Justin Pryzby wrote: > A couple months ago, I implemented prepared statements for PyGreSQL. While > updating our application in advance of their release with that feature, I > noticed that our query logs wer

Re: query logging of prepared statements

"message". This patch resolves that inconsistency by showing it in neither. >From d60957d9cd1108f389dde0125fadee71a96b4229 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 9 Feb 2019 19:20:43 -0500 Subject: [PATCH v2] Avoid repetitive log of PREPARE during EXECUTE of prepared stat

Re: query logging of prepared statements

Hello Justin, On 27.02.2019 21:06, Justin Pryzby wrote: I'm attaching a v2 patch which avoids repeated logging of PREPARE, rather than making such logs conditional on log_error_verbosity=VERBOSE, since log_error_verbosity is documented to control whether these are output: DETAIL/HINT/QUERY/CONTE

Re: query logging of prepared statements

isting output seems duplicative and "denormalized". :) Justin >From c04f2fe815a55babe6a9bdd53421d74fc283094b Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 9 Feb 2019 19:20:43 -0500 Subject: [PATCH v3] Avoid repetitive log of PREPARE during EXECUTE of prepared statements

Re: query logging of prepared statements

On 04.03.2019 21:31, Justin Pryzby wrote: It wasn't intentional. Find attached v3 patch which handles that case, by removing the 2nd call to errdetail_execute() ; since it's otherwise unused, so remove that function entirely. Thank you. Thanks for reviewing. I'm also interested in discussio

MERGE and parsing with prepared statements

We've used INSERT ON CONFLICT for a few years (with partitions as the target). That's also combined with prepared statements, for bulk loading. I was looking to see if we should use MERGE (probably not, but looking anyway). And came across this behavior. I'm not sure if it'

Re: query logging of prepared statements

On 2019-Mar-04, Justin Pryzby wrote: > Thanks for reviewing. I'm also interested in discussion about whether this > change is undesirable for someone else for some reason ? For me, the existing > output seems duplicative and "denormalized". :) Some digging turned up that the function you're re

Re: query logging of prepared statements

On 2019-Apr-04, Alvaro Herrera wrote: > However, turning duration logging off and using log_statement=all, this is > what > I get: > > 2019-04-04 14:58:42.564 -03 [31685] LOG: statement: SET search_path = > testlibpq3 > 2019-04-04 14:58:42.565 -03 [31685] LOG: execute > 2019-04-04 14:58:42.5

Re: query logging of prepared statements

On 2019-Apr-04, Alvaro Herrera wrote: > I think we could improve on this by setting a "logged" flag in the > portal; if the Parse logs the statement, then don't include the > statement in further lines, otherwise do. Also: I think such a flag could help the case of a query that takes long enough

Re: query logging of prepared statements

pecifically handled unnamed statements in my v1 patch, and tested that in 20190215145704.gw30...@telsasoft.com, but for some reason dropped that logic in v2, which was intended to only remove behavior conditional on log_error_verbosity. Previous patches also never logged pqPrepare with named prepared

Re: query logging of prepared statements

workloads with long-running connections. If most statements are just already prepared statements - pretty common in higher throughput apps - the query will suddenly be either far away in the logfile (thereby requiring pretty expensive analysis to figure out the corresponding statement) or even in a dif

Re: query logging of prepared statements

Hi, Thanks both for thinking about this. On Fri, Apr 05, 2019 at 06:16:38PM -0700, Andres Freund wrote: > On 2019-04-04 16:01:26 -0300, Alvaro Herrera wrote: > > Also, if you parse once and bind/execute many times, IMO the statement > > should be logged exactly once. I think you could that with

Re: query logging of prepared statements

On 2019-Apr-07, Justin Pryzby wrote: > [...] Since I've been using log_statement=all, and not > log_min_duration_statement, I don't have a strong opinion about it. Ah, so your plan for this in production is to use the sample-based logging facilities, I see! Did you get Adrien a beer already? --

Re: MERGE and parsing with prepared statements

On 2022-Jul-15, Justin Pryzby wrote: > Should that sentence be removed from MERGE ? Removed On 2022-Jul-18, Justin Pryzby wrote: > On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > > Should that sentence be removed from MERGE ? > > Also, I think these examples should be more sim

Re: MERGE and parsing with prepared statements

On Fri, 12 Aug 2022 at 12:20, Alvaro Herrera wrote: > On 2022-Jul-18, Justin Pryzby wrote: > > > On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > > > Should that sentence be removed from MERGE ? > > > > Also, I think these examples should be more similar. > > Agreed, done. Sorry,

Re: MERGE and parsing with prepared statements

On 2022-Aug-12, Simon Riggs wrote: > Sorry, but I disagree with this chunk in the latest commit, > specifically, changing the MATCHED from after to before the NOT > MATCHED clause. > > The whole point of the second example was to demonstrate that the > order of the MATCHED/NOT MATCHED clauses mad

Re: MERGE and parsing with prepared statements

On Fri, Aug 12, 2022 at 01:53:25PM +0200, Alvaro Herrera wrote: > On 2022-Aug-12, Simon Riggs wrote: > > > Sorry, but I disagree with this chunk in the latest commit, > > specifically, changing the MATCHED from after to before the NOT > > MATCHED clause. 3d895bc84 also moved a semicolon into the

Re: MERGE and parsing with prepared statements

On 2022-Aug-12, Simon Riggs wrote: > Sorry, but I disagree with this chunk in the latest commit, > specifically, changing the MATCHED from after to before the NOT > MATCHED clause. > > The whole point of the second example was to demonstrate that the > order of the MATCHED/NOT MATCHED clauses mad

Re: Re: query logging of prepared statements

Hi Justin, On 3/5/19 2:30 PM, Arthur Zakirov wrote: On 04.03.2019 21:31, Justin Pryzby wrote: It wasn't intentional.  Find attached v3 patch which handles that case, by removing the 2nd call to errdetail_execute() ; since it's otherwise unused, so remove that function entirely. Thank you.

Re: Re: query logging of prepared statements

UC which is scales better than log_* booleans: https://www.postgresql.org/message-id/20190316122422.GR6030%40telsasoft.com If that idea were desirable, there could be a logging_bit to enable verbose logging of prepared statements. Justin

Re: MERGE and parsing with prepared statements

On Thu, 14 Jul 2022, 18:26 Justin Pryzby, wrote: > > We've used INSERT ON CONFLICT for a few years (with partitions as the target). > That's also combined with prepared statements, for bulk loading. > > I was looking to see if we should use MERGE (probably not, but looki

Re: MERGE and parsing with prepared statements

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 i

Re: MERGE and parsing with prepared statements

ems 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). I have no comments on this. Maybe it can be improved, but I don't know how. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/

Re: MERGE and parsing with prepared statements

Alvaro Herrera writes: > On 2022-Jul-15, Justin Pryzby wrote: >> 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 typ

Re: MERGE and parsing with prepared statements

On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera wrote: > On 2022-Jul-15, Justin Pryzby wrote: > > > 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 &

Re: MERGE and parsing with prepared statements

On Fri, Jul 15, 2022 at 12:17:51PM -0700, David G. Johnston wrote: > On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera > wrote: > > On 2022-Jul-15, Justin Pryzby wrote: > > > > > It seems a bit odd that it's impossible to use merge with prepared > > > stat

Re: MERGE and parsing with prepared statements

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). > > > > > > I have no comments on this. Maybe it can be improved, bu

Re: MERGE and parsing with prepared statements

On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby wrote: > > That appears to be copied from the INSERT page. > What does that mean, if not that data types will be resolved as needed ? > Yep, and the system needs to resolve the type at a point where there is no contextual information and so it choos

Re: MERGE and parsing with prepared statements

On Fri, Jul 15, 2022 at 12:59:34PM -0700, David G. Johnston wrote: > On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby wrote: > > > > | If the expression for any column is not of the correct data type, automatic > type conversion will be attempted. > > That appears to be copied from the INSERT page

Re: MERGE and parsing with prepared statements

On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > Should that sentence be removed from MERGE ? Also, I think these examples should be more similar. doc/src/sgml/ref/merge.sgml > > MERGE INTO CustomerAccount CA > USING RecentTransactions T > ON T.CustomerId = CA.CustomerId > WHEN

[doc] adding way to examine the plan type of prepared statements

Hi, Currently, EXPLAIN is the only way to know whether the plan is generic or custom according to the manual of PREPARE. https://www.postgresql.org/docs/devel/sql-prepare.html After commit d05b172, we can also use pg_prepared_statements view to examine the plan types. How about adding t

Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

many prepared statements are used.     The default value of 0 disables this setting, but it is recommended to set this     value to a bit lower than the maximum memory a backend worker thread should reserve     permanently. If the GUC is configured after each save of a

Re: [doc] adding way to examine the plan type of prepared statements

Hi Torikoshi-san, On Thu, Nov 19, 2020 at 3:19 PM torikoshia wrote: > > On 2020-11-18 11:04, torikoshia wrote: > > Hi, > > > > > > Currently, EXPLAIN is the only way to know whether the plan is generic > > or custom according to the manual of PREPARE. > > > > https://www.postgresql.org/docs/dev

Re: [doc] adding way to examine the plan type of prepared statements

EXECUTE name(parameter_values You can see all prepared statements available in the session by querying the pg_prepared_statements - system view. + system view. This view also shows the numbers of generic and custom plans + were chosen. -- 2.18.1

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

On 2019-Aug-18, Daniel Migowski wrote: > >  - Is this a WIP patch or the final patch? Because I can see TODO > > and non-standard comments in the patch. > > Definitely work in progress! The current implementation seems to work for > me, but might be improved, but I wanted some input from the mail

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

Alvaro Herrera writes: > On this patch, beyond the fact that it's causing a crash in the > regression tests as evidenced by the CFbot, we seem to be waiting on the > input of the larger community on whether it's a desired feature or not. > We have Kyotaro's vote for it, but it would be good to get

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

I wrote: > As far as the crash issue is concerned, I notice that right now the > cfbot is showing green for this patch, but that seems to just be because > the behavior is unstable. I see crashes in "make installcheck-parallel" > about 50% of the time with this patch applied. Since, in fact, > th

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

ects > the maximum memory > a backend threads will reserve when many prepared statements > are used. > The default value of 0 disables this setting, but it is > recommended to set this > value to a bit lower than the maximum memory a backend worker > thre

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

sus about the name of GUC? I don't think it is the right name for that. No, it is a proposal. It could also be named plancache_mem or cachedplansource_maxmem or anything else. It was intended to make prepared statements not use up all my mem, but development has shown that it could also be

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

age, which would be too expensive for the purpose. We currently use it only for debug output on critical errors like OOM. > No, it is a proposal. It could also be named plancache_mem or > cachedplansource_maxmem or anything else. It was intended to make > prepared statements not use up all my m

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

Am 19.08.2019 um 05:57 schrieb Kyotaro Horiguchi: At Sun, 18 Aug 2019 09:43:09 +0200, Daniel Migowski wrote in <6e25ca12-9484-8994-a1ee-40fdbe6af...@ikoffice.de> Am 17.08.2019 um 19:10 schrieb Ibrar Ahmed: On Sat, Aug 17, 2019 at 6:58 PM Daniel Migowski mailto:dmigow...@ikoffice.de>> wrote:

Re: Patch: New GUC prepared_statement_limit to limit memory used by prepared statements

On Tue, Sep 03, 2019 at 06:30:32PM -0400, Tom Lane wrote: > Oh ... actually, I bet the problem is that the patch thinks it's okay > to immediately free space in PlanCacheRelCallback and friends, rather > than just marking invalid entries as invalid. Nope, you cannot do that. > You can't tell wheth