Hi all,

Question: will PGStatement.setPrepareThreshold(1) cause server side
prepare to be used already on the first execution, or only after the
second one ?

The problem: I want to force server side prepare at least for some of
the queries on my system, as the query plan is completely bad if the
server knows the parameters when planning (due to null values in IN
list, nothing to be fixed in my application unless I completely rewrite
some parts of it).

I couldn't figure out this from the log files... postgres logs the
queries as <unnamed>, but that doesn't tell me too much.

Is there a way to force server side prepare from the first query ?

Thanks,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Csaba Nagy wrote:
> Hi all,
>
> Question: will PGStatement.setPrepareThreshold(1) cause server side
> prepare to be used already on the first execution, or only after the
> second one ?

It should cause it to be used on the first execution (at least that was
the intent)

The logic looks like:
- On statement creation set count=0
- On each execution:
- If this statement is a PreparedStatement, increment count
- If threshold == 0 or count < threshold, make this execution "oneshot"
- Execute query

"oneshot" queries use the unnamed statement (with one exception: queries
that will be backed by a portal use a named statement)

> I couldn't figure out this from the log files... postgres logs the
> queries as <unnamed>, but that doesn't tell me too much.

If you're seeing <unnamed> then those queries aren't using server-side
prepare (the unnamed statement is also special as it's the trigger for
the planner behaviour that you are trying to avoid..) .. so it seems
that you are not managing to trigger server-side prepare for some
reason. Maybe you are using a plain Statement?

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Oliver,

Thanks for your answer.

> The logic looks like:
> - On statement creation set count=0
> - On each execution:
> - If this statement is a PreparedStatement, increment count
> - If threshold == 0 or count < threshold, make this execution "oneshot"
> - Execute query

OK, I've checked the sources. I'm using postgresql-jdbc-8.1dev-400.
The relevant piece of code looks to me to be in
AbstractJdbc2Statement#execute(Query,ParameterList ,int):

// Only use named statements after we hit the threshold
if (preparedQuery != null)
{
++m_useCount; // We used this statement once more.
if (m_prepareThreshold == 0 || m_useCount <
m_prepareThreshold)
flags |= QueryExecutor.QUERY_ONESHOT;
}

So if preparedQuery is not null for prepared statements, it should work
as you said...

> "oneshot" queries use the unnamed statement (with one exception: queries
> that will be backed by a portal use a named statement)
>
> > I couldn't figure out this from the log files... postgres logs the
> > queries as <unnamed>, but that doesn't tell me too much.
>
> If you're seeing <unnamed> then those queries aren't using server-side
> prepare (the unnamed statement is also special as it's the trigger for
> the planner behaviour that you are trying to avoid..) .. so it seems
> that you are not managing to trigger server-side prepare for some
> reason. Maybe you are using a plain Statement?

No, I definitely use a prepared statement, I have lots of parameters in
the IN clause... that's part of the problem. And I checked again, and it
is logged as <unnamed> in the postgres logs.

So the only remaining suspect is that the threshold is not really set to
1. This is somewhat strange, as I use a connection pool and set it to 1
on each connection, and only set it to 0 on specific statements where I
do want the parameter values to be taken into account (I know, I'll have
to change this, but it was the easiest way to get the system stable
after switching from Oracle to postgres).

I will have to investigate what is the real problem.

Thanks,
Csaba.





---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Csaba,

Actually I was debugging some other code and noticed that it doesn't
really get set, this just confirms my suspicions. I'll try to get
something out in a few hours

Dave
On 3-Aug-06, at 5:17 AM, Csaba Nagy wrote:

> Oliver,
>
> Thanks for your answer.
>
>> The logic looks like:
>> - On statement creation set count=0
>> - On each execution:
>> - If this statement is a PreparedStatement, increment count
>> - If threshold == 0 or count < threshold, make this execution
>> "oneshot"
>> - Execute query
>
> OK, I've checked the sources. I'm using postgresql-jdbc-8.1dev-400.
> The relevant piece of code looks to me to be in
> AbstractJdbc2Statement#execute(Query,ParameterList ,int):
>
> // Only use named statements after we hit the threshold
> if (preparedQuery != null)
> {
> ++m_useCount; // We used this statement once more.
> if (m_prepareThreshold == 0 || m_useCount <
> m_prepareThreshold)
> flags |= QueryExecutor.QUERY_ONESHOT;
> }
>
> So if preparedQuery is not null for prepared statements, it should
> work
> as you said...
>
>> "oneshot" queries use the unnamed statement (with one exception:
>> queries
>> that will be backed by a portal use a named statement)
>>
>>> I couldn't figure out this from the log files... postgres logs the
>>> queries as <unnamed>, but that doesn't tell me too much.
>>
>> If you're seeing <unnamed> then those queries aren't using server-
>> side
>> prepare (the unnamed statement is also special as it's the trigger
>> for
>> the planner behaviour that you are trying to avoid..) .. so it seems
>> that you are not managing to trigger server-side prepare for some
>> reason. Maybe you are using a plain Statement?
>
> No, I definitely use a prepared statement, I have lots of
> parameters in
> the IN clause... that's part of the problem. And I checked again,
> and it
> is logged as <unnamed> in the postgres logs.
>
> So the only remaining suspect is that the threshold is not really
> set to
> 1. This is somewhat strange, as I use a connection pool and set it
> to 1
> on each connection, and only set it to 0 on specific statements
> where I
> do want the parameter values to be taken into account (I know, I'll
> have
> to change this, but it was the easiest way to get the system stable
> after switching from Oracle to postgres).
>
> I will have to investigate what is the real problem.
>
> Thanks,
> Csaba.
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Ok, further investigation shows that it does work

However I'm still puzzled by these backend logs

<test test>LOG: statement: PREPARE S_3 AS INSERT INTO texttable (te)
VALUES ($1)
<test test>LOG: statement: <BIND>
<test test>LOG: statement: EXECUTE <unnamed> [PREPARE: INSERT INTO
texttable (te) VALUES ($1)]

We see the prepare to a named statement, but then the execute is
unnamed ?

Dave

On 3-Aug-06, at 7:53 AM, Dave Cramer wrote:

> Csaba,
>
> Actually I was debugging some other code and noticed that it
> doesn't really get set, this just confirms my suspicions. I'll try
> to get something out in a few hours
>
> Dave
> On 3-Aug-06, at 5:17 AM, Csaba Nagy wrote:
>
>> Oliver,
>>
>> Thanks for your answer.
>>
>>> The logic looks like:
>>> - On statement creation set count=0
>>> - On each execution:
>>> - If this statement is a PreparedStatement, increment count
>>> - If threshold == 0 or count < threshold, make this execution
>>> "oneshot"
>>> - Execute query
>>
>> OK, I've checked the sources. I'm using postgresql-jdbc-8.1dev-400.
>> The relevant piece of code looks to me to be in
>> AbstractJdbc2Statement#execute(Query,ParameterList ,int):
>>
>> // Only use named statements after we hit the threshold
>> if (preparedQuery != null)
>> {
>> ++m_useCount; // We used this statement once more.
>> if (m_prepareThreshold == 0 || m_useCount <
>> m_prepareThreshold)
>> flags |= QueryExecutor.QUERY_ONESHOT;
>> }
>>
>> So if preparedQuery is not null for prepared statements, it should
>> work
>> as you said...
>>
>>> "oneshot" queries use the unnamed statement (with one exception:
>>> queries
>>> that will be backed by a portal use a named statement)
>>>
>>>> I couldn't figure out this from the log files... postgres logs the
>>>> queries as <unnamed>, but that doesn't tell me too much.
>>>
>>> If you're seeing <unnamed> then those queries aren't using server-
>>> side
>>> prepare (the unnamed statement is also special as it's the
>>> trigger for
>>> the planner behaviour that you are trying to avoid..) .. so it seems
>>> that you are not managing to trigger server-side prepare for some
>>> reason. Maybe you are using a plain Statement?
>>
>> No, I definitely use a prepared statement, I have lots of
>> parameters in
>> the IN clause... that's part of the problem. And I checked again,
>> and it
>> is logged as <unnamed> in the postgres logs.
>>
>> So the only remaining suspect is that the threshold is not really
>> set to
>> 1. This is somewhat strange, as I use a connection pool and set it
>> to 1
>> on each connection, and only set it to 0 on specific statements
>> where I
>> do want the parameter values to be taken into account (I know,
>> I'll have
>> to change this, but it was the easiest way to get the system stable
>> after switching from Oracle to postgres).
>>
>> I will have to investigate what is the real problem.
>>
>> Thanks,
>> Csaba.
>>
>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dave Cramer <pg@fastcrypt.com> writes:
> However I'm still puzzled by these backend logs

> <test test>LOG: statement: PREPARE S_3 AS INSERT INTO texttable (te)
> VALUES ($1)
> <test test>LOG: statement: <BIND>
> <test test>LOG: statement: EXECUTE <unnamed> [PREPARE: INSERT INTO
> texttable (te) VALUES ($1)]

> We see the prepare to a named statement, but then the execute is
> unnamed ?

What it's showing you there is the name of the protocol-level portal;
evidently you're using the unnamed portal to execute the INSERT.

This does demonstrate once again that the current approach to logging
parse/bind/execute operations is entirely wrongheaded, because it
deliberately confuses the protocol and SQL levels. I see that Bruce
has changed CVS tip so that the message is

<test test>LOG: statement: [protocol] EXECUTE <unnamed> [PREPARE: INSERT INTO
texttable (te) VALUES ($1)]

but I hardly think that's going to be enough to de-confuse people.
All those brackets just serve to make things *more* confusing IMHO.

What I'd like to see is something like this:

Simple Query produces

LOG: statement: ...statement text here...

Parse produces

LOG: parse statement-name: ...statement text here...

Bind produces

LOG: bind portal-name to statement-name (someday print arguments here)

Execute produces

LOG: execute portal-name: ...statement text here...

No brackets, no pretending that an Execute message is the same thing
as a SQL EXECUTE command or that Parse is the same as PREPARE.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 3-Aug-06, at 3:59 PM, Tom Lane wrote:

> Dave Cramer <pg@fastcrypt.com> writes:
>> However I'm still puzzled by these backend logs
>
>> <test test>LOG: statement: PREPARE S_3 AS INSERT INTO texttable (te)
>> VALUES ($1)
>> <test test>LOG: statement: <BIND>
>> <test test>LOG: statement: EXECUTE <unnamed> [PREPARE: INSERT INTO
>> texttable (te) VALUES ($1)]
>
>> We see the prepare to a named statement, but then the execute is
>> unnamed ?
>
> What it's showing you there is the name of the protocol-level portal;
> evidently you're using the unnamed portal to execute the INSERT.

If that's the case then the driver is not doing what it's supposed to
be doing. It should be using the named portal (S_3) to do the insert.

Dave
>
> This does demonstrate once again that the current approach to logging
> parse/bind/execute operations is entirely wrongheaded, because it
> deliberately confuses the protocol and SQL levels. I see that Bruce
> has changed CVS tip so that the message is
>
> <test test>LOG: statement: [protocol] EXECUTE <unnamed>
> [PREPARE: INSERT INTO
> texttable (te) VALUES ($1)]
>
> but I hardly think that's going to be enough to de-confuse people.
> All those brackets just serve to make things *more* confusing IMHO.
>
> What I'd like to see is something like this:
>
> Simple Query produces
>
> LOG: statement: ...statement text here...
>
> Parse produces
>
> LOG: parse statement-name: ...statement text here...
>
> Bind produces
>
> LOG: bind portal-name to statement-name (someday print arguments
> here)
>
> Execute produces
>
> LOG: execute portal-name: ...statement text here...
>
> No brackets, no pretending that an Execute message is the same thing
> as a SQL EXECUTE command or that Parse is the same as PREPARE.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Cramer wrote:

> If that's the case then the driver is not doing what it's supposed to be
> doing. It should be using the named portal (S_3) to do the insert.

No, the driver is fine. It is using a named statement (S_3) but an
unnamed portal (because it is going to fetch all the data in one go and
doesn't need to retain the portal after execution)

If your query met the conditions for using a portal-based ResultSet,
you'd see it use a named portal as well as a named statement.

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 3-Aug-06, at 6:14 PM, Oliver Jowett wrote:

> Dave Cramer wrote:
>
>> If that's the case then the driver is not doing what it's supposed
>> to be doing. It should be using the named portal (S_3) to do the
>> insert.
>
> No, the driver is fine. It is using a named statement (S_3) but an
> unnamed portal (because it is going to fetch all the data in one go
> and doesn't need to retain the portal after execution)
>
> If your query met the conditions for using a portal-based
> ResultSet, you'd see it use a named portal as well as a named
> statement.

Thanks for clarifying that Oliver, the logs are still misleading in
that they don't name the statement used in the bind message.

--dc--
>
> -O
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Dave Cramer <pg@fastcrypt.com> writes:
> Thanks for clarifying that Oliver, the logs are still misleading in
> that they don't name the statement used in the bind message.

Pretty much exactly my point. The distinction between statement name
and portal name is critical at the protocol level and has no real
counterpart at the SQL level (prepared statement name vs cursor name
is similar but I think not exactly the same namespaces). The logging
code is not getting this right.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly