Re: Implementing RESET CONNECTION ...

We have implemented a patch which can be used by connection pools for
instance.
RESECT CONNECTION cleans up a backend so that it can be reused.
Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
transactions, prepared statements and GUCs are cleaned up.
I hope we have not missed important per-backend information.

test=# BEGIN;
BEGIN
test=# RESET CONNECTION;
RESET
test=# COMMIT;
WARNING: there is no transaction in progress
COMMIT
test=# PREPARE myplan(int, int) AS SELECT $1 + $2;
PREPARE
test=# RESET CONNECTION;
RESET
test=# EXECUTE myplan(1, 2);
ERROR: prepared statement "myplan" does not exist
test=#
test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class;
DECLARE CURSOR
test=# FETCH NEXT FROM mycur;
relname
---------
views
(1 row)

test=# RESET CONNECTION;
RESET
test=# FETCH NEXT FROM mycur;
ERROR: cursor "mycur" does not exist
test=# CREATE TEMP TABLE mytmp (id int4);
CREATE TABLE
test=# RESET CONNECTION;
RESET
test=# INSERT INTO mytmp VALUES (10);
ERROR: relation "mytmp" does not exist

All regression tests passed.
It would be nice if we had this in 8.1.

Best regards,

Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at

On Thu, 30 Dec 2004, [ISO-8859-1] Hans-Jrgen Schnig wrote:

> We have implemented a patch which can be used by connection pools for
> instance. RESECT CONNECTION cleans up a backend so that it can be
> reused. Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
> transactions, prepared statements and GUCs are cleaned up. I hope we
> have not missed important per-backend information.
>

From the JDBC driver's perspective this doesn't meet the needs I'd like to
see in a connection reset. In the initial connection setup a number of
GUC variables are tweaked to what the JDBC driver desires (DateStyle,
client_encoding). When resetting we'd want to reset to this point, not
the default values. Perhaps some kind of MARK command, kind of like a
savepoint to rollback to would better specify this.

Also I don't like the idea of cleaning up prepared statements. While it
doesn't do so now, the JDBC driver would like to do statement pooling at
some point. This means the same underlying server prepared statement can
be reused transparently from multiple callers. In a connection pool where
a connection is grabbed and returned for virtually each sql execution this
is key to getting the performance boost from prepared statements. We
don't want to have to reprepare on each connection and we don't want them
to disappear from underneath us, because the prepared statements are
generated transparently by the JDBC driver, not directly by a user
statement.

Kris Jurka

Kris,

I have seen that the JDBC driver is doing some GUC settings.
However, this does not prevent you from bad users who change GUCs for
some reason. It might very well happen that a user sets the DateStyle to
some different value temporarily. A different program would in this case
behave RANDOMLY depending on the connection assigned by the pool
The basic idea is that all GUCs are cleaned up because they might have
been changed.
Personally I expect a new connection to be clean.

The same applies to prepared statements - different programs (let's say
websites) might give plans the same name and this would lead to RANDOM
conflicts (depending on which connection you get from the pool).
However, they still might share the same connection pool.

As far as prepared statements are concerned: Maybe a tablefunction
pg_list_prepared_plans() might make sense - you could use that for your
purpose (the same applies to cursors).
Actually I was thinking of including DEALLOCATE ALL into this plan so
that just prepared plans can be deallocated as well.

I don't think a mark would make too much sense.
If partial resets are really desirable it is better to add RESET
CURSORS, RESET PREPARED STATEMENTS, ...
Fell free to add code :).

Best regards,

Hans

Kris Jurka wrote:

>On Thu, 30 Dec 2004, [ISO-8859-1] Hans-J�rgen Sch�nig wrote:
>
>
>
>>We have implemented a patch which can be used by connection pools for
>>instance. RESECT CONNECTION cleans up a backend so that it can be
>>reused. Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
>>transactions, prepared statements and GUCs are cleaned up. I hope we
>>have not missed important per-backend information.
>>
>>
>>
>
>From the JDBC driver's perspective this doesn't meet the needs I'd like to
>see in a connection reset. In the initial connection setup a number of
>GUC variables are tweaked to what the JDBC driver desires (DateStyle,
>client_encoding). When resetting we'd want to reset to this point, not
>the default values. Perhaps some kind of MARK command, kind of like a
>savepoint to rollback to would better specify this.
>
>Also I don't like the idea of cleaning up prepared statements. While it
>doesn't do so now, the JDBC driver would like to do statement pooling at
>some point. This means the same underlying server prepared statement can
>be reused transparently from multiple callers. In a connection pool where
>a connection is grabbed and returned for virtually each sql execution this
>is key to getting the performance boost from prepared statements. We
>
>
>don't want to have to reprepare on each connection and we don't want them
>to disappear from underneath us, because the prepared statements are
>generated transparently by the JDBC driver, not directly by a user
>statement.
>
>Kris Jurka
>
>

On Mon, 3 Jan 2005, [UTF-8] Hans-Jürgen Schönig wrote:

> I have seen that the JDBC driver is doing some GUC settings.
> However, this does not prevent you from bad users who change GUCs for
> some reason.

Actually it does. The V3 protocol provides a ParameterStatus message that
notifies us when certain GUC parameters are modified. If someone changes
the DateStyle underneath us, we throw an Exception and destroy the
connection.

> The same applies to prepared statements - different programs (let's say
> websites) might give plans the same name and this would lead to RANDOM
> conflicts (depending on which connection you get from the pool).
> However, they still might share the same connection pool.

Let me explain a little more how this works from the JDBC driver's
perspective. The API for getting a PreparedStatement object is:

PreparedStatement pstmt = Connection.prepareStatement(String sql);

The sql string may have placeholders to indicate where parameters go.
From this API the JDBC driver can do one of three things with the
PreparedStatement object when it is executed.

1) It can do the parameter substituition directly on the driver side and
send a simple query to the server.

2) It can use an unnamed statement to execute the query sending the
parameters separately.

3) It can use a named statement to execute the query sending the
parameters separately.

We are really only interested in the third case here, because this is the
only one that leaves a permanent server state. The namespace for protocol
executed named statements is shared with sql executed PREPARE commands, so
this is applicable to the RESET command you've implemented.

Note that the user has never provided a name for this named statement.
The JDBC driver uses S_N where N is an incrementing number per connection,
so there will be no conflicts. What we'd like the driver to eventually do
is detect the following condition:

PreparedStatement ps1 = conn.prepareStatement(sql);
PreparedStatement ps2 = conn.prepareStatement(sql);

Since both PreparedStatements are derived from the same sql string it
would be nice if they could use the same underlying S_N server named
statement instead of creating two identical ones. Now consider this in a
connection pool:

Connection conn;
PreparedStatement ps;

conn = pool.getConnection();
ps = conn.prepareStatement(sql);
conn.close();

conn = pool.getConnection();
ps = conn.prepareStatement(sql);
conn.close();

This situation is slightly different because we may or may not have gotten
the same connection back, but we don't really care. We only want to know
if whatever connection we currently have has already seen and prepared the
sql string we are looking for. If we add the RESET you've implemented
then it will never have a pre-prepared statement for us to use, so we'll
have to create a new one every time.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> On Thu, 30 Dec 2004, [ISO-8859-1] Hans-Jrgen Schnig wrote:
>> We have implemented a patch which can be used by connection pools for
>> instance. RESECT CONNECTION cleans up a backend so that it can be
>> reused. Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
>> transactions, prepared statements and GUCs are cleaned up. I hope we
>> have not missed important per-backend information.

> From the JDBC driver's perspective this doesn't meet the needs I'd like to
> see in a connection reset. In the initial connection setup a number of
> GUC variables are tweaked to what the JDBC driver desires (DateStyle,
> client_encoding). When resetting we'd want to reset to this point, not
> the default values.

I haven't looked at the proposed patch, but I would've expected that it
duplicates the existing RESET ALL behavior for GUC settings. And that
already works the way you want. Values taken from the client connection
request packet establish the session defaults, ie, what RESET goes to.

> Also I don't like the idea of cleaning up prepared statements.

Hmm. This seems a bit eye-of-the-beholder-ish, ie you could make a
legitimate argument either way. Maybe the RESET CONNECTION command
should have an option whether to zap prepared statements or not?
Is there anything else falling in the category of "debatable"?

regards, tom lane

Tom Lane wrote:
> Kris Jurka <books(at)ejurka(dot)com> writes:
>
>>Also I don't like the idea of cleaning up prepared statements.
>
>
> Hmm. This seems a bit eye-of-the-beholder-ish, ie you could make a
> legitimate argument either way. Maybe the RESET CONNECTION command
> should have an option whether to zap prepared statements or not?

That doesn't really help the JDBC driver case. The problem is that there
are prepared statements that have been set up by the driver invisibly to
the user. Zapping them will make the driver break, and it's too easy for
the user code to do a full RESET CONNECTION and accidently zap them.

Yes, you can break the JDBC driver currently by doing explicit
DEALLOCATEs of its prepared statements -- but you have to do that quite
deliberately so it's less of a problem.

Having notification of either prepared statement deallocation or
connection reset (a la ParameterStatus for GUC changes) would help the
driver to recover from this case.

-O

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> Tom Lane wrote:
>> Maybe the RESET CONNECTION command
>> should have an option whether to zap prepared statements or not?

> That doesn't really help the JDBC driver case. The problem is that there
> are prepared statements that have been set up by the driver invisibly to
> the user. Zapping them will make the driver break, and it's too easy for
> the user code to do a full RESET CONNECTION and accidently zap them.

Fair point, but you could make the same argument against *any* side
effect of RESET CONNECTION. You're just complaining about PREPARE
because you can see immediately where that breaks JDBC. Anything that
any driver does to set up per-connection state the way it wants will
be equally vulnerable.

> Having notification of either prepared statement deallocation or
> connection reset (a la ParameterStatus for GUC changes) would help the
> driver to recover from this case.

I'm inclined to think that we'd have to add a protocol message that
reports RESET CONNECTION to really answer objections of this type.
That seems to bring the thing into the category of "stuff that forces
a protocol version bump" :-(

Perhaps RESET CONNECTION should be a protocol-level operation instead
of a SQL command? That would prevent user-level code from causing it
without the driver knowing.

regards, tom lane

Tom Lane wrote:

> Fair point, but you could make the same argument against *any* side
> effect of RESET CONNECTION. You're just complaining about PREPARE
> because you can see immediately where that breaks JDBC. Anything that
> any driver does to set up per-connection state the way it wants will
> be equally vulnerable.

Yes, exactly.

> Perhaps RESET CONNECTION should be a protocol-level operation instead
> of a SQL command? That would prevent user-level code from causing it
> without the driver knowing.

I just suggested as much in another email (our emails crossed).

-O

On Mon, 2005-01-03 at 20:27 -0500, Tom Lane wrote:

> I'm inclined to think that we'd have to add a protocol message that
> reports RESET CONNECTION to really answer objections of this type.
> That seems to bring the thing into the category of "stuff that forces
> a protocol version bump" :-(
>
> Perhaps RESET CONNECTION should be a protocol-level operation instead
> of a SQL command? That would prevent user-level code from causing it
> without the driver knowing.

I still don't see a big difference between DEALLOCATE and RESET -- both
can break the JDBC driver. I'm not sure if we need prevent bad usage of
PG tools (JDBC in this case). The DEALLOCATE/RESET usage is under user's
full control and everything can be described in docs.

I think each PG command returns some status. For example in libpq it's
possible check by PQcmdStatus(). I think JDBC can checks this status (by
own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
RESETED" it can deallocate internal stuff. This solution doesn't require
touch the protocol.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>

I completely agree with Karel. I think it is a bad idea to change the
protocol for such a minor feature - i tend to call it overkill.
I want to add one point to this discussion: There is not just JDBC -
other connection pools or clients might want different behaviour (which
can from my point of view only lead to a complete reset).

If the JDBC driver prefers different behaviour (maybe for prepared
statements) we should discuss further options for RESET.
Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
(cleaning GUCS only) and RESET some_guc.
Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
Personally I think this is not a good idea.

Regards,

Hans

Karel Zak wrote:
> On Mon, 2005-01-03 at 20:27 -0500, Tom Lane wrote:
>
>
>>I'm inclined to think that we'd have to add a protocol message that
>>reports RESET CONNECTION to really answer objections of this type.
>>That seems to bring the thing into the category of "stuff that forces
>>a protocol version bump" :-(
>>
>>Perhaps RESET CONNECTION should be a protocol-level operation instead
>>of a SQL command? That would prevent user-level code from causing it
>>without the driver knowing.
>
>
> I still don't see a big difference between DEALLOCATE and RESET -- both
> can break the JDBC driver. I'm not sure if we need prevent bad usage of
> PG tools (JDBC in this case). The DEALLOCATE/RESET usage is under user's
> full control and everything can be described in docs.
>
> I think each PG command returns some status. For example in libpq it's
> possible check by PQcmdStatus(). I think JDBC can checks this status (by
> own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
> RESETED" it can deallocate internal stuff. This solution doesn't require
> touch the protocol.
>
> Karel
>

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at

On Tue, 4 Jan 2005, [ISO-8859-1] Hans-Jrgen Schnig wrote:

> I completely agree with Karel. I think it is a bad idea to change the
> protocol for such a minor feature - i tend to call it overkill.

I agree. I don't think it's imperative to prevent or detect this
condition. The only real caller of this should be the driver itself. If
the end user does call this and breaks things I wouldn't consider it our
problem. Making this available at the protocol level only would certainly
solve that, but it's not really compelling to make a protocol level jump.

> I want to add one point to this discussion: There is not just JDBC -
> other connection pools or clients might want different behaviour (which
> can from my point of view only lead to a complete reset).

Right, I am speaking from the JDBC driver perspective, but I think any
higher level interface should desire to do statement pooling, which will
have this problem. You have not stated what client interface you are
targetting, but I believe anything written to a higher level than libpq
will need to be aware of this. Perhaps -patches isn't the right place to
solicit input for this.

Kris Jurka

Karel Zak wrote:

> I still don't see a big difference between DEALLOCATE and RESET -- both
> can break the JDBC driver.

You have to go out of your way to break the driver via DEALLOCATE,
explicitly finding a statement name that the driver is using. There's
also a reasonably simple fix: make the protocol-level and
PREPARE/DEALLOCATE namespaces separate. There's been some discussion
about doing this in the past.

In contrast RESET CONNECTION, by design, resets many things without
needing to explicitly list them. The user could easily reset connection
state that a driver is relying on without realizing it.

> I think each PG command returns some status. For example in libpq it's
> possible check by PQcmdStatus(). I think JDBC can checks this status (by
> own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
> RESETED" it can deallocate internal stuff. This solution doesn't require
> touch the protocol.

That could work. It's a bit ugly, though, as currently drivers don't
need to parse command status strings (unless they want an insert OID)

-O

Hans-Jürgen Schönig wrote:

> If the JDBC driver prefers different behaviour (maybe for prepared
> statements) we should discuss further options for RESET.
> Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
> (cleaning GUCS only) and RESET some_guc.
> Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
> Personally I think this is not a good idea.

It doesn't help, either, if user code can still issue RESET CONNECTION.
(the scenario is user code, not the driver itself, originating the RESET..)

-O

On Wed, 2005-01-05 at 01:33 +1300, Oliver Jowett wrote:
> Karel Zak wrote:
> > I think each PG command returns some status. For example in libpq it's
> > possible check by PQcmdStatus(). I think JDBC can checks this status (by
> > own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
> > RESETED" it can deallocate internal stuff. This solution doesn't require
> > touch the protocol.
>
> That could work. It's a bit ugly, though, as currently drivers don't
> need to parse command status strings (unless they want an insert OID)

I think command status is common and nice feedback for client. I think
it's more simple change something in JDBC than change protocol that is
shared between more tools.

We need some common way how detect on client what's happen on server --
a way that doesn't mean change protocol always when we add some
feature/command to backend. The command status is possible use for this.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>

(cc'ing -hackers)

Karel Zak wrote:

> I think command status is common and nice feedback for client. I think
> it's more simple change something in JDBC than change protocol that is
> shared between more tools.

There is a bit of a queue of changes that would be nice to have but
require a protocol version change. If we're going to change the protocol
for any of those we might as well handle RESET CONNECTION cleanly too.

> We need some common way how detect on client what's happen on server --
> a way that doesn't mean change protocol always when we add some
> feature/command to backend. The command status is possible use for this.

Command status only works if commands are directly executed. If you can
execute the command indirectly, e.g. via a PL, then you'll miss the
notification. Making RESET a top-level-only command isn't unreasonable,
but using command status won't work as a general approach for notifying
clients.

We have a mechanism for GUC changes that uses a separate message
(ParameterStatus). Perhaps that should be generalized to report
different sorts of connection-related changes.

-O

Hans-Jrgen Schnig wrote:
> I completely agree with Karel. I think it is a bad idea to change the
> protocol for such a minor feature - i tend to call it overkill.
> I want to add one point to this discussion: There is not just JDBC -
> other connection pools or clients might want different behaviour (which
> can from my point of view only lead to a complete reset).
>
> If the JDBC driver prefers different behaviour (maybe for prepared
> statements) we should discuss further options for RESET.
> Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
> (cleaning GUCS only) and RESET some_guc.
> Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
> Personally I think this is not a good idea.

I think autocommit is a good example for comparison. One big problem
was that some users had autocommit in their server configs on startup
and that caused scripts to fail. I don't imagine anyone would add RESET
CONNECTION in their startup script.

However, I can imagine someone doing RESET CONNECTION from JDBC and the
interface should continue working. Should we add something like SET
CONNECTION that would set the reset values for RESET CONNECTION? JDBC
could then use SET CONNECTION and then any RESET CONNECTION would reset
back to that point. RESET has a similar capability where when you RESET
you reset to the connection defaults, not to the defaults from
postgresql.conf.

Also, let me mention PHP uses connection pooling and wants to use RESET
CONNECTION too.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Hans-J�rgen Sch�nig wrote:
>
>>I completely agree with Karel. I think it is a bad idea to change the
>>protocol for such a minor feature - i tend to call it overkill.
>>I want to add one point to this discussion: There is not just JDBC -
>>other connection pools or clients might want different behaviour (which
>>can from my point of view only lead to a complete reset).
>>
>>If the JDBC driver prefers different behaviour (maybe for prepared
>>statements) we should discuss further options for RESET.
>>Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
>>(cleaning GUCS only) and RESET some_guc.
>>Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
>>Personally I think this is not a good idea.
>
>
> I think autocommit is a good example for comparison. One big problem
> was that some users had autocommit in their server configs on startup
> and that caused scripts to fail. I don't imagine anyone would add RESET
> CONNECTION in their startup script.
>
> However, I can imagine someone doing RESET CONNECTION from JDBC and the
> interface should continue working. Should we add something like SET
> CONNECTION that would set the reset values for RESET CONNECTION? JDBC
> could then use SET CONNECTION and then any RESET CONNECTION would reset
> back to that point. RESET has a similar capability where when you RESET
> you reset to the connection defaults, not to the defaults from
> postgresql.conf.
>
> Also, let me mention PHP uses connection pooling and wants to use RESET
> CONNECTION too.
>

Shouldn't these be default values set in postgresql.conf rather than
copied settings inside the backend?

There is always a way to modify a GUC or to change the SET CONNECTION
value (maybe a SELECT statement "SELECT change_value"). I have seen
people doing that millions of time (I know, it is a bad idea).

Personally I'd expect a fully cleanup backend rather than a half-cleaned
up backend. Setting two or three parameters when JDBC provides a
recycled connection should not be a problem.

As far as prepared plans are concerned: There used to be a discussion
about storing prepared plans accross connections (I recall even seeing
some working code by Joe doing exactly that). It was rejected due to
issues related to plan stability. This is exactly the same scenario now
- keeping prepared plans actually means storing them accross "connection
boundaries" (from a logical point of view it is a new connection - even
is the backend is an old one).

If we want the behaviour proposed by JDBC we shouldn't call it RESET
CONNECTION - maybe RESET STATUS or something like that). To me it is a
totally different thing.

Best regards,

Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Hans-Jrgen Schnig wrote:
>> I completely agree with Karel. I think it is a bad idea to change the
>> protocol for such a minor feature - i tend to call it overkill.

> I think autocommit is a good example for comparison.

Indeed, it is an *excellent* example for comparison. The real problem
with autocommit was that it changed the interface semantics without
making that change sufficiently visible at all levels.

If we try to pretend that RESET CONNECTION isn't a protocol change
then we will silently break code that needs to know about it. Which is
pretty much exactly what happened with autocommit.

> Should we add something like SET
> CONNECTION that would set the reset values for RESET CONNECTION?

This is an even bigger compatibility-breaker, as now anyone who can
issue SET CONNECTION can not only break code layers that were trying to
track backend state, he can break code layers that thought they knew
what RESET CONNECTION would accomplish. I definitely recommend against
this idea.

regards, tom lane

What did we decide on RESET CONNECTION. Do we want an SQL command or
something only the protocol can do?

---------------------------------------------------------------------------

Oliver Jowett wrote:
> (cc'ing -hackers)
>
> Karel Zak wrote:
>
> > I think command status is common and nice feedback for client. I think
> > it's more simple change something in JDBC than change protocol that is
> > shared between more tools.
>
> There is a bit of a queue of changes that would be nice to have but
> require a protocol version change. If we're going to change the protocol
> for any of those we might as well handle RESET CONNECTION cleanly too.
>
> > We need some common way how detect on client what's happen on server --
> > a way that doesn't mean change protocol always when we add some
> > feature/command to backend. The command status is possible use for this.
>
> Command status only works if commands are directly executed. If you can
> execute the command indirectly, e.g. via a PL, then you'll miss the
> notification. Making RESET a top-level-only command isn't unreasonable,
> but using command status won't work as a general approach for notifying
> clients.
>
> We have a mechanism for GUC changes that uses a separate message
> (ParameterStatus). Perhaps that should be generalized to report
> different sorts of connection-related changes.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

What would be absolutely ideal is a reset connection command, plus some
way of knowing via the protocol if it's needed or not.

Chris

Bruce Momjian wrote:
> What did we decide on RESET CONNECTION. Do we want an SQL command or
> something only the protocol can do?
>
> ---------------------------------------------------------------------------
>
> Oliver Jowett wrote:
>
>>(cc'ing -hackers)
>>
>>Karel Zak wrote:
>>
>>
>>>I think command status is common and nice feedback for client. I think
>>>it's more simple change something in JDBC than change protocol that is
>>>shared between more tools.
>>
>>There is a bit of a queue of changes that would be nice to have but
>>require a protocol version change. If we're going to change the protocol
>>for any of those we might as well handle RESET CONNECTION cleanly too.
>>
>>
>>>We need some common way how detect on client what's happen on server --
>>>a way that doesn't mean change protocol always when we add some
>>>feature/command to backend. The command status is possible use for this.
>>
>>Command status only works if commands are directly executed. If you can
>>execute the command indirectly, e.g. via a PL, then you'll miss the
>>notification. Making RESET a top-level-only command isn't unreasonable,
>>but using command status won't work as a general approach for notifying
>>clients.
>>
>>We have a mechanism for GUC changes that uses a separate message
>>(ParameterStatus). Perhaps that should be generalized to report
>>different sorts of connection-related changes.
>>
>>-O
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>
>

Christopher Kings-Lynne wrote:
> What would be absolutely ideal is a reset connection command, plus some
> way of knowing via the protocol if it's needed or not.

And a way of notifying the client that a reset has happened.

-O

Attached patch applied, with slight modifications. Thanks.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> We have implemented a patch which can be used by connection pools for
> instance.
> RESECT CONNECTION cleans up a backend so that it can be reused.
> Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
> transactions, prepared statements and GUCs are cleaned up.
> I hope we have not missed important per-backend information.
>
> test=# BEGIN;
> BEGIN
> test=# RESET CONNECTION;
> RESET
> test=# COMMIT;
> WARNING: there is no transaction in progress
> COMMIT
> test=# PREPARE myplan(int, int) AS SELECT $1 + $2;
> PREPARE
> test=# RESET CONNECTION;
> RESET
> test=# EXECUTE myplan(1, 2);
> ERROR: prepared statement "myplan" does not exist
> test=#
> test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class;
> DECLARE CURSOR
> test=# FETCH NEXT FROM mycur;
> relname
> ---------
> views
> (1 row)
>
> test=# RESET CONNECTION;
> RESET
> test=# FETCH NEXT FROM mycur;
> ERROR: cursor "mycur" does not exist
> test=# CREATE TEMP TABLE mytmp (id int4);
> CREATE TABLE
> test=# RESET CONNECTION;
> RESET
> test=# INSERT INTO mytmp VALUES (10);
> ERROR: relation "mytmp" does not exist
>
>
> All regression tests passed.
> It would be nice if we had this in 8.1.
>
> Best regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Schoengrabern 134, A-2020 Hollabrunn, Austria
> Tel: +43/660/816 40 77
> www.cybertec.at, www.postgresql.at
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

I have backed out the patch until there is more discussion.

I now see that the CC list had an incorrect entry for the patches list,
so I am unsure if others have seen this patch thoroughly.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> We have implemented a patch which can be used by connection pools for
> instance.
> RESECT CONNECTION cleans up a backend so that it can be reused.
> Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
> transactions, prepared statements and GUCs are cleaned up.
> I hope we have not missed important per-backend information.
>
> test=# BEGIN;
> BEGIN
> test=# RESET CONNECTION;
> RESET
> test=# COMMIT;
> WARNING: there is no transaction in progress
> COMMIT
> test=# PREPARE myplan(int, int) AS SELECT $1 + $2;
> PREPARE
> test=# RESET CONNECTION;
> RESET
> test=# EXECUTE myplan(1, 2);
> ERROR: prepared statement "myplan" does not exist
> test=#
> test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class;
> DECLARE CURSOR
> test=# FETCH NEXT FROM mycur;
> relname
> ---------
> views
> (1 row)
>
> test=# RESET CONNECTION;
> RESET
> test=# FETCH NEXT FROM mycur;
> ERROR: cursor "mycur" does not exist
> test=# CREATE TEMP TABLE mytmp (id int4);
> CREATE TABLE
> test=# RESET CONNECTION;
> RESET
> test=# INSERT INTO mytmp VALUES (10);
> ERROR: relation "mytmp" does not exist
>
>
> All regression tests passed.
> It would be nice if we had this in 8.1.
>
> Best regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Schoengrabern 134, A-2020 Hollabrunn, Austria
> Tel: +43/660/816 40 77
> www.cybertec.at, www.postgresql.at
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

OK, what would people like done with this patch? Our TODO list has:

* -Add RESET CONNECTION command to reset all session state

This would include resetting of all variables (RESET ALL), dropping of
temporary tables, removing any NOTIFYs, cursors, open transactions,
prepared queries, currval()s, etc. This could be used for connection
pooling. We could also change RESET ALL to have this functionality.
The difficult of this features is allowing RESET ALL to not affect
changes made by the interface driver for its internal use. One idea
is for this to be a protocol-only feature. Another approach is to
notify the protocol when a RESET CONNECTION command is used.

This patch does everything except reset currval(), but the big missing
item is that it doesn't handle the protocol issues outlined in the TODO
item. However, there also has been very little discussion on exactly
how the protocol stuff would work.

Should we add it for 8.2 and see if we get any problem reports?

---------------------------------------------------------------------------

> I have backed out the patch until there is more discussion.
>
> I now see that the CC list had an incorrect entry for the patches list,
> so I am unsure if others have seen this patch thoroughly.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> We have implemented a patch which can be used by connection pools for
> instance.
> RESECT CONNECTION cleans up a backend so that it can be reused.
> Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
> transactions, prepared statements and GUCs are cleaned up.
> I hope we have not missed important per-backend information.
>
> test=# BEGIN;
> BEGIN
> test=# RESET CONNECTION;
> RESET
> test=# COMMIT;
> WARNING: there is no transaction in progress
> COMMIT
> test=# PREPARE myplan(int, int) AS SELECT $1 + $2;
> PREPARE
> test=# RESET CONNECTION;
> RESET
> test=# EXECUTE myplan(1, 2);
> ERROR: prepared statement "myplan" does not exist
> test=#
> test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class;
> DECLARE CURSOR
> test=# FETCH NEXT FROM mycur;
> relname
> ---------
> views
> (1 row)
>
> test=# RESET CONNECTION;
> RESET
> test=# FETCH NEXT FROM mycur;
> ERROR: cursor "mycur" does not exist
> test=# CREATE TEMP TABLE mytmp (id int4);
> CREATE TABLE
> test=# RESET CONNECTION;
> RESET
> test=# INSERT INTO mytmp VALUES (10);
> ERROR: relation "mytmp" does not exist
>
>
> All regression tests passed.
> It would be nice if we had this in 8.1.
>
> Best regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Schoengrabern 134, A-2020 Hollabrunn, Austria
> Tel: +43/660/816 40 77
> www.cybertec.at, www.postgresql.at
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

To complete the connection pooling for multiple users, it would be great
to have a protocol level option to change roles semi-permanently (to
reduce permissions). RESET SESSION AUTHORIZATION would then bounce back to
that (new, set) role until another protocol-level role "rollback". This
would allow completely reusable connections per database while maintaining
a real sandbox for each connection.

On Tue, April 25, 2006 10:19 am, Bruce Momjian wrote:
>

> OK, what would people like done with this patch? Our TODO list has:
>
>
> * -Add RESET CONNECTION command to reset all session state
>
>
> This would include resetting of all variables (RESET ALL), dropping of
> temporary tables, removing any NOTIFYs, cursors, open transactions,
> prepared queries, currval()s, etc. This could be used for connection
> pooling. We could also change RESET ALL to have this functionality. The
> difficult of this features is allowing RESET ALL to not affect changes
> made by the interface driver for its internal use. One idea is for this
> to be a protocol-only feature. Another approach is to notify the protocol
> when a RESET CONNECTION command is used.
>
> This patch does everything except reset currval(), but the big missing
> item is that it doesn't handle the protocol issues outlined in the TODO
> item. However, there also has been very little discussion on exactly how
> the protocol stuff would work.
>
> Should we add it for 8.2 and see if we get any problem reports?

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Should we add it for 8.2 and see if we get any problem reports?

No. I don't believe this can work without a far more invasive patch
than this is. To point out just one problem, what of cached plans in
plpgsql functions? Those can't be carried across a genuine connection
reset (permissions and search path are two reasons why not). And the
protocol issues are not something you can just ignore, because the
command does break reasonable driver-level expectations.

regards, tom lane