pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedClosing inactive connections OR user connections limits
20+ messages / 6 participants
[nested] [flat]
* Closing inactive connections OR user connections limits
@ 2002-11-20 16:52 Francisco Reyes <lists@natserv.com>
0 siblings, 2 replies; 20+ messages in thread
From: Francisco Reyes @ 2002-11-20 16:52 UTC (permalink / raw)
To: pgsql General List <pgsql-general@postgresql.org>
I have read on the archives some type of per user limit that will be
available in 7.3, but what do I do in the meantime with a 7.2.X.
Basically I came this morning to find a developer had a bug in a program
and used up all the connections. Could I have safely killed those
connections?
I had to do a "pgsql stop -m fast" to free the connections, but I had some
pending operations which now I will have to redo, not to mention the
vacuum full failed last night because of the open connections.
In all the archives I read there seems to be a negative view on timeouts.
I think that after 4 hours of no activity, at least in my case, I
definitely want those connections dead.
In particular this is a reporting server and I delete/reload millions of
records daily so anything which stops the vacuum full at night is a big
problem for me.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 17:54 Neil Conway <neilc@samurai.com>
parent: Francisco Reyes <lists@natserv.com>
1 sibling, 1 reply; 20+ messages in thread
From: Neil Conway @ 2002-11-20 17:54 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
Francisco Reyes <lists@natserv.com> writes:
> Basically I came this morning to find a developer had a bug in a program
> and used up all the connections. Could I have safely killed those
> connections?
kill(1) the backends in question.
> In all the archives I read there seems to be a negative view on timeouts.
> I think that after 4 hours of no activity, at least in my case, I
> definitely want those connections dead.
The superuser_reserved_connections feature we added for 7.3 should be
sufficient for this -- if the maintenance tasks are done as the
PostgreSQL superuser, there will be some reserved "slots" for them to
connect to.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 18:35 Medi Montaseri <medi.montaseri@intransa.com>
parent: Francisco Reyes <lists@natserv.com>
1 sibling, 1 reply; 20+ messages in thread
From: Medi Montaseri @ 2002-11-20 18:35 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
In 7.2, you can run the vacuum in parallel with other database activities...
perhaps there is a way of reading from one of the pg_activity* or stats to
learn which connection is execessively idle and then go after it....
Francisco Reyes wrote:
>I have read on the archives some type of per user limit that will be
>available in 7.3, but what do I do in the meantime with a 7.2.X.
>
>Basically I came this morning to find a developer had a bug in a program
>and used up all the connections. Could I have safely killed those
>connections?
>
>I had to do a "pgsql stop -m fast" to free the connections, but I had some
>pending operations which now I will have to redo, not to mention the
>vacuum full failed last night because of the open connections.
>
>In all the archives I read there seems to be a negative view on timeouts.
>I think that after 4 hours of no activity, at least in my case, I
>definitely want those connections dead.
>
>In particular this is a reporting server and I delete/reload millions of
>records daily so anything which stops the vacuum full at night is a big
>problem for me.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: 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
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 18:36 Francisco Reyes <lists@natserv.com>
parent: Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 20+ messages in thread
From: Francisco Reyes @ 2002-11-20 18:36 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
On 20 Nov 2002, Neil Conway wrote:
> The superuser_reserved_connections feature we added for 7.3 should be
> sufficient for this -- if the maintenance tasks are done as the
> PostgreSQL superuser, there will be some reserved "slots" for them to
> connect to.
But wouldn't "vacuum full" still fail?
Also where can I read about these new 7.3 options?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 18:36 Neil Conway <neilc@samurai.com>
parent: Francisco Reyes <lists@natserv.com>
0 siblings, 2 replies; 20+ messages in thread
From: Neil Conway @ 2002-11-20 18:36 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
Not if the connection is made to the database as the PostgreSQL
superuser.
> Also where can I read about these new 7.3 options?
Heh, no where at the moment, as there wasn't isn't any documentation
on this new feature :-) Attached is a patch that adds some (also sent
to -patches).
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Attachments:
reserved_conn_docs-1.patch (text/x-patch, 3.6K) download
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.156
diff -c -r1.156 runtime.sgml
*** doc/src/sgml/runtime.sgml 15 Nov 2002 03:22:30 -0000 1.156
--- doc/src/sgml/runtime.sgml 20 Nov 2002 18:18:01 -0000
***************
*** 1642,1647 ****
--- 1642,1670 ----
</varlistentry>
<varlistentry>
+ <term><varname>SUPERUSER_RESERVED_CONNECTIONS</varname>
+ (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Determines the number of <quote>connection slots</quote> that
+ are reserved for connections by the <productname>PostgreSQL</>
+ superuser. The maximum number of concurrent connections that
+ can be made from any user account is
+ <varname>max_connections</> minus
+ <varname>superuser_reserved_connections</varname>; beyond that
+ point, any additional connections from non-superuser accounts
+ are refused.
+ </para>
+
+ <para>
+ The default value is 2. This must be at least as large as the
+ value of <varname>max_connections</varname>. This parameter
+ can only be set at server start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>MAX_EXPR_DEPTH</varname> (<type>integer</type>)</term>
<listitem>
<para>
***************
*** 2941,2964 ****
</para>
<para>
! With SSL support compiled in, the <productname>PostgreSQL</> server
! can be started with SSL support by setting the parameter
! <varname>ssl</varname> to on in
! <filename>postgresql.conf</filename>. When starting in SSL mode,
! the server will look for the files <filename>server.key</> and
! <filename>server.crt</> in the data directory. These files should
! contain the server private key and certificate respectively. These
! files must be set up correctly before an SSL-enabled server can
! start. If the private key is protected with a passphrase, the
! server will prompt for the passphrase and will not start until it
! has been entered.
</para>
<para>
! The server will listen for both standard and SSL connections on the
! same TCP/IP port, and will negotiate with any connecting client on
! whether to use SSL. See <xref linkend="client-authentication"> about
! how to force the server to only use of SSL for certain connections.
</para>
<para>
--- 2964,2988 ----
</para>
<para>
! With <acronym>SSL</> support compiled in, the
! <productname>PostgreSQL</> server can be started with
! <acronym>SSL</> support by setting the parameter
! <varname>ssl</varname> to on in <filename>postgresql.conf</>. When
! starting in <acronym>SSL</> mode, the server will look for the
! files <filename>server.key</> and <filename>server.crt</> in the
! data directory. These files should contain the server private key
! and certificate respectively. These files must be set up correctly
! before an <acronym>SSL</>-enabled server can start. If the private key is
! protected with a passphrase, the server will prompt for the
! passphrase and will not start until it has been entered.
</para>
<para>
! The server will listen for both standard and <acronym>SSL</>
! connections on the same TCP/IP port, and will negotiate with any
! connecting client on whether to use <acronym>SSL</>. See <xref
! linkend="client-authentication"> about how to force the server to
! only use of <acronym>SSL</> for certain connections.
</para>
<para>
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 18:54 Francisco Reyes <lists@natserv.com>
parent: Medi Montaseri <medi.montaseri@intransa.com>
0 siblings, 2 replies; 20+ messages in thread
From: Francisco Reyes @ 2002-11-20 18:54 UTC (permalink / raw)
To: Medi Montaseri <medi.montaseri@intransa.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
On Wed, 20 Nov 2002, Medi Montaseri wrote:
> In 7.2, you can run the vacuum in parallel with other database activities...
As far as I know this is not the case for "vacuum full".
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 18:56 Francisco Reyes <lists@natserv.com>
parent: Neil Conway <neilc@samurai.com>
1 sibling, 1 reply; 20+ messages in thread
From: Francisco Reyes @ 2002-11-20 18:56 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
On 20 Nov 2002, Neil Conway wrote:
> Francisco Reyes <lists@natserv.com> writes:
> > But wouldn't "vacuum full" still fail?
>
> Not if the connection is made to the database as the PostgreSQL
> superuser.
So this would be new to 7.3. Right?
Right now if there are connections open "vacuum full" will wait from those
connectins to close, even if the vacuum is done from the superuser.
> Heh, no where at the moment, as there wasn't isn't any documentation
> on this new feature :-) Attached is a patch that adds some (also sent
> to -patches).
So there will be a way to reserve connectinos for the super-user, but will
there be a way to limit number of connection per user?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 19:00 Medi Montaseri <medi.montaseri@intransa.com>
parent: Francisco Reyes <lists@natserv.com>
1 sibling, 1 reply; 20+ messages in thread
From: Medi Montaseri @ 2002-11-20 19:00 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
Is this true..... ?
I think from the data integrity point of view, vacuum is more important
than vacuum full.
Is vacuum purges deleted and updated tuples, that is the integrity point
of failure (for
multi-versioning), reclaiming the space is a phsyical issue....
Francisco Reyes wrote:
>On Wed, 20 Nov 2002, Medi Montaseri wrote:
>
>
>
>>In 7.2, you can run the vacuum in parallel with other database activities...
>>
>>
>
>As far as I know this is not the case for "vacuum full".
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 19:32 scott.marlowe <scott.marlowe@ihs.com>
parent: Francisco Reyes <lists@natserv.com>
1 sibling, 0 replies; 20+ messages in thread
From: scott.marlowe @ 2002-11-20 19:32 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: Medi Montaseri <medi.montaseri@intransa.com>; pgsql General List <pgsql-general@postgresql.org>
On Wed, 20 Nov 2002, Francisco Reyes wrote:
> On Wed, 20 Nov 2002, Medi Montaseri wrote:
>
> > In 7.2, you can run the vacuum in parallel with other database activities...
>
> As far as I know this is not the case for "vacuum full".
The thing that blocks vacuum full are pending transactions. If the
connections aren't holding open a transaction the vacuum full will run
just fine.
For most folks, daily / hourly vacuums (not full vacuums) in 7.2 are
all you need, with a weekly or so vacuum full.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 21:15 Neil Conway <neilc@samurai.com>
parent: Francisco Reyes <lists@natserv.com>
0 siblings, 0 replies; 20+ messages in thread
From: Neil Conway @ 2002-11-20 21:15 UTC (permalink / raw)
To: Francisco Reyes <lists@natserv.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
Francisco Reyes <lists@natserv.com> writes:
> On 20 Nov 2002, Neil Conway wrote:
> > Not if the connection is made to the database as the PostgreSQL
> > superuser.
>
> So this would be new to 7.3. Right?
Yes.
> Right now if there are connections open "vacuum full" will wait from those
> connectins to close, even if the vacuum is done from the superuser.
Erm, no -- VACUUM FULL doesn't block on connections, it blocks waiting
to acquire locks. Whether or not a client is allowed to connect to the
database has no relation to the locks the client can acquire once
they've connected.
> > Heh, no where at the moment, as there wasn't isn't any documentation
> > on this new feature :-) Attached is a patch that adds some (also sent
> > to -patches).
>
> So there will be a way to reserve connectinos for the super-user, but will
> there be a way to limit number of connection per user?
Not in 7.3, AFAIK.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 21:33 Neil Conway <neilc@samurai.com>
parent: Medi Montaseri <medi.montaseri@intransa.com>
0 siblings, 2 replies; 20+ messages in thread
From: Neil Conway @ 2002-11-20 21:33 UTC (permalink / raw)
To: Medi Montaseri <medi.montaseri@intransa.com>; +Cc: Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
Medi Montaseri <medi.montaseri@intransa.com> writes:
> I think from the data integrity point of view, vacuum is more
> important than vacuum full.
Why would VACUUM have any effect on data integrity, either positive or
negative?
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections
@ 2002-11-20 22:02 Oliver Elphick <olly@lfix.co.uk>
parent: Neil Conway <neilc@samurai.com>
1 sibling, 1 reply; 20+ messages in thread
From: Oliver Elphick @ 2002-11-20 22:02 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
On Wed, 2002-11-20 at 18:36, Neil Conway wrote:
> Heh, no where at the moment, as there wasn't isn't any documentation
> on this new feature :-) Attached is a patch that adds some (also sent
> to -patches).
...
> + <term><varname>SUPERUSER_RESERVED_CONNECTIONS</varname>
...
> + Determines the number of <quote>connection slots</quote> that
> + are reserved for connections by the <productname>PostgreSQL</>
> + superuser. The maximum number of concurrent connections that
> + can be made from any user account is
^^^^^^^^^^^^^^^^
all user accounts together
> + <varname>max_connections</> minus
> + <varname>superuser_reserved_connections</varname>; beyond that
> + point, any additional connections from non-superuser accounts
> + are refused.
> + </para>
> +
> + <para>
> + The default value is 2. This must be at least as large as the
> + value of <varname>max_connections</varname>. This parameter
At least as large? Surely not? You are saying:
SUPERUSER_RESERVED_CONNECTIONS >= MAX_CONNECTIONS
and
max_user_connections =
MAX_CONNECTIONS - SUPERUSER_RESERVED_CONNECTIONS
Therefore max_user_connections <= 0
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If my people, which are called by my name, shall
humble themselves, and pray, and seek my face, and
turn from their wicked ways; then will I hear from
heaven, and will forgive their sin, and will heal
their land." II Chronicles 7:14
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 22:08 scott.marlowe <scott.marlowe@ihs.com>
parent: Neil Conway <neilc@samurai.com>
1 sibling, 0 replies; 20+ messages in thread
From: scott.marlowe @ 2002-11-20 22:08 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Medi Montaseri <medi.montaseri@intransa.com>; Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
On 20 Nov 2002, Neil Conway wrote:
> Medi Montaseri <medi.montaseri@intransa.com> writes:
> > I think from the data integrity point of view, vacuum is more
> > important than vacuum full.
>
> Why would VACUUM have any effect on data integrity, either positive or
> negative?
Maybe he's thinking of the problems createed when one runs out of disk
space? :-)
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 22:11 Neil Conway <neilc@samurai.com>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 20+ messages in thread
From: Neil Conway @ 2002-11-20 22:11 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
Woops :-) You're completely correct, of course.
New version of the patch is attached (another copy sent to -patches
separately).
Thanks for spotting my mistakes, Oliver!
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Attachments:
reserved_conn_docs-2.patch (text/x-patch, 3.6K) download
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.156
diff -c -r1.156 runtime.sgml
*** doc/src/sgml/runtime.sgml 15 Nov 2002 03:22:30 -0000 1.156
--- doc/src/sgml/runtime.sgml 20 Nov 2002 22:08:12 -0000
***************
*** 1642,1647 ****
--- 1642,1670 ----
</varlistentry>
<varlistentry>
+ <term><varname>SUPERUSER_RESERVED_CONNECTIONS</varname>
+ (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Determines the number of <quote>connection slots</quote> that
+ are reserved for connections by the <productname>PostgreSQL</>
+ superuser. The maximum number of concurrent connections that
+ can be made from all user accounts combined is
+ <varname>max_connections</> minus
+ <varname>superuser_reserved_connections</varname>; beyond that
+ point, any additional connections from non-superuser accounts
+ are refused.
+ </para>
+
+ <para>
+ The default value is 2. This must be less than the value of
+ <varname>max_connections</varname>. This parameter can only be
+ set at server start.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>MAX_EXPR_DEPTH</varname> (<type>integer</type>)</term>
<listitem>
<para>
***************
*** 2941,2964 ****
</para>
<para>
! With SSL support compiled in, the <productname>PostgreSQL</> server
! can be started with SSL support by setting the parameter
! <varname>ssl</varname> to on in
! <filename>postgresql.conf</filename>. When starting in SSL mode,
! the server will look for the files <filename>server.key</> and
! <filename>server.crt</> in the data directory. These files should
! contain the server private key and certificate respectively. These
! files must be set up correctly before an SSL-enabled server can
! start. If the private key is protected with a passphrase, the
! server will prompt for the passphrase and will not start until it
! has been entered.
</para>
<para>
! The server will listen for both standard and SSL connections on the
! same TCP/IP port, and will negotiate with any connecting client on
! whether to use SSL. See <xref linkend="client-authentication"> about
! how to force the server to only use of SSL for certain connections.
</para>
<para>
--- 2964,2988 ----
</para>
<para>
! With <acronym>SSL</> support compiled in, the
! <productname>PostgreSQL</> server can be started with
! <acronym>SSL</> support by setting the parameter
! <varname>ssl</varname> to on in <filename>postgresql.conf</>. When
! starting in <acronym>SSL</> mode, the server will look for the
! files <filename>server.key</> and <filename>server.crt</> in the
! data directory. These files should contain the server private key
! and certificate respectively. These files must be set up correctly
! before an <acronym>SSL</>-enabled server can start. If the private key is
! protected with a passphrase, the server will prompt for the
! passphrase and will not start until it has been entered.
</para>
<para>
! The server will listen for both standard and <acronym>SSL</>
! connections on the same TCP/IP port, and will negotiate with any
! connecting client on whether to use <acronym>SSL</>. See <xref
! linkend="client-authentication"> about how to force the server to
! only use of <acronym>SSL</> for certain connections.
</para>
<para>
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 22:47 Medi Montaseri <medi.montaseri@intransa.com>
parent: Neil Conway <neilc@samurai.com>
1 sibling, 1 reply; 20+ messages in thread
From: Medi Montaseri @ 2002-11-20 22:47 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
Its my understanding that vacuum actually removes tuples that have been
updated or deleted.
Sort of like emptying your trash .... whence a tuple has been removed,
no rollback can set the
state back. If you have logically removed a tuple (not vacuumed yet),
then one can rollback,
but if you vacuum then you can not rollback.
Now suppose transaction A decides to delete some tuples, a vacuum job
comes along and
deletes things (in parallel), trans A decides to rollback....engines who
support parallel
vacuum-ing and transactions such as PG 7.2 better have a way of
protecting themselves
against this....
Correct me if ...
Neil Conway wrote:
>Medi Montaseri <medi.montaseri@intransa.com> writes:
>
>
>>I think from the data integrity point of view, vacuum is more
>>important than vacuum full.
>>
>>
>
>Why would VACUUM have any effect on data integrity, either positive or
>negative?
>
>Cheers,
>
>Neil
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 23:12 scott.marlowe <scott.marlowe@ihs.com>
parent: Medi Montaseri <medi.montaseri@intransa.com>
0 siblings, 2 replies; 20+ messages in thread
From: scott.marlowe @ 2002-11-20 23:12 UTC (permalink / raw)
To: Medi Montaseri <medi.montaseri@intransa.com>; +Cc: Neil Conway <neilc@samurai.com>; Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
On Wed, 20 Nov 2002, Medi Montaseri wrote:
> Its my understanding that vacuum actually removes tuples that have been
> updated or deleted.
> Sort of like emptying your trash .... whence a tuple has been removed,
> no rollback can set the
> state back. If you have logically removed a tuple (not vacuumed yet),
> then one can rollback,
> but if you vacuum then you can not rollback.
>
> Now suppose transaction A decides to delete some tuples, a vacuum job
> comes along and
> deletes things (in parallel), trans A decides to rollback....engines who
> support parallel
> vacuum-ing and transactions such as PG 7.2 better have a way of
> protecting themselves
> against this....
>
> Correct me if ...
Yes, you are wrong. Postgresql's vacuuming does NOT free tuples that are
still in a transaction, hence a full vacuum will hand waiting for the
transaction to complete or roll back. A normal 7.2 vacuum will simply
skip the in transaction tuples.
For proof, try this: (Note A> and B> are used to represent two different
sessions)
A> create table test (a text, id int);
A> insert into test (a,id) values ('abc',123);
A> begin;
A> delete from test where id=123;
B> vacuum;
A> rollback;
A> select * from test;
a | id
--------
abc| 123
Still there.
>
> Neil Conway wrote:
>
> >Medi Montaseri <medi.montaseri@intransa.com> writes:
> >
> >
> >>I think from the data integrity point of view, vacuum is more
> >>important than vacuum full.
> >>
> >>
> >
> >Why would VACUUM have any effect on data integrity, either positive or
> >negative?
> >
> >Cheers,
> >
> >Neil
> >
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections
@ 2002-11-20 23:16 Brian Hirt <bhirt@mobygames.com>
parent: scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 1 reply; 20+ messages in thread
From: Brian Hirt @ 2002-11-20 23:16 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Medi Montaseri <medi.montaseri@intransa.com>; Neil Conway <neilc@samurai.com>; Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
Scott,
You did a rollback on the transaction, so the delete was never commited.
I hope you aren't expecting vacuum to delete uncommited rows from the
database, are you?
--brian
On Wed, 2002-11-20 at 16:12, scott.marlowe wrote:
> On Wed, 20 Nov 2002, Medi Montaseri wrote:
>
> > Its my understanding that vacuum actually removes tuples that have been
> > updated or deleted.
> > Sort of like emptying your trash .... whence a tuple has been removed,
> > no rollback can set the
> > state back. If you have logically removed a tuple (not vacuumed yet),
> > then one can rollback,
> > but if you vacuum then you can not rollback.
> >
> > Now suppose transaction A decides to delete some tuples, a vacuum job
> > comes along and
> > deletes things (in parallel), trans A decides to rollback....engines who
> > support parallel
> > vacuum-ing and transactions such as PG 7.2 better have a way of
> > protecting themselves
> > against this....
> >
> > Correct me if ...
>
> Yes, you are wrong. Postgresql's vacuuming does NOT free tuples that are
> still in a transaction, hence a full vacuum will hand waiting for the
> transaction to complete or roll back. A normal 7.2 vacuum will simply
> skip the in transaction tuples.
>
> For proof, try this: (Note A> and B> are used to represent two different
> sessions)
>
> A> create table test (a text, id int);
> A> insert into test (a,id) values ('abc',123);
> A> begin;
> A> delete from test where id=123;
> B> vacuum;
> A> rollback;
> A> select * from test;
> a | id
> --------
> abc| 123
>
> Still there.
>
>
> >
> > Neil Conway wrote:
> >
> > >Medi Montaseri <medi.montaseri@intransa.com> writes:
> > >
> > >
> > >>I think from the data integrity point of view, vacuum is more
> > >>important than vacuum full.
> > >>
> > >>
> > >
> > >Why would VACUUM have any effect on data integrity, either positive or
> > >negative?
> > >
> > >Cheers,
> > >
> > >Neil
> > >
> > >
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Brian Hirt <bhirt@mobygames.com>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-20 23:53 Medi Montaseri <medi.montaseri@intransa.com>
parent: scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 1 reply; 20+ messages in thread
From: Medi Montaseri @ 2002-11-20 23:53 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Neil Conway <neilc@samurai.com>; Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
scott.marlowe wrote:
>On Wed, 20 Nov 2002, Medi Montaseri wrote:
>
>
>
>>Its my understanding that vacuum actually removes tuples that have been
>>updated or deleted.
>>Sort of like emptying your trash .... whence a tuple has been removed,
>>no rollback can set the
>>state back. If you have logically removed a tuple (not vacuumed yet),
>>then one can rollback,
>>but if you vacuum then you can not rollback.
>>
>>Now suppose transaction A decides to delete some tuples, a vacuum job
>>comes along and
>>deletes things (in parallel), trans A decides to rollback....engines who
>>support parallel
>>vacuum-ing and transactions such as PG 7.2 better have a way of
>>protecting themselves
>>against this....
>>
>>
So I think you have shown that 7.2 achieves this by skiping current
transactions....
Thank you
>>Correct me if ...
>>
>>
>
>Yes, you are wrong. Postgresql's vacuuming does NOT free tuples that are
>still in a transaction, hence a full vacuum will hand waiting for the
>transaction to complete or roll back. A normal 7.2 vacuum will simply
>skip the in transaction tuples.
>
>For proof, try this: (Note A> and B> are used to represent two different
>sessions)
>
>A> create table test (a text, id int);
>A> insert into test (a,id) values ('abc',123);
>A> begin;
>A> delete from test where id=123;
>B> vacuum;
>A> rollback;
>A> select * from test;
>a | id
>--------
>abc| 123
>
>Still there.
>
>
>
>
>>Neil Conway wrote:
>>
>>
>>
>>>Medi Montaseri <medi.montaseri@intransa.com> writes:
>>>
>>>
>>>
>>>
>>>>I think from the data integrity point of view, vacuum is more
>>>>important than vacuum full.
>>>>
>>>>
>>>>
>>>>
>>>Why would VACUUM have any effect on data integrity, either positive or
>>>negative?
>>>
>>>Cheers,
>>>
>>>Neil
>>>
>>>
>>>
>>>
>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>
>
>
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-21 00:00 scott.marlowe <scott.marlowe@ihs.com>
parent: Brian Hirt <bhirt@mobygames.com>
0 siblings, 0 replies; 20+ messages in thread
From: scott.marlowe @ 2002-11-21 00:00 UTC (permalink / raw)
To: Brian Hirt <bhirt@mobygames.com>; +Cc: Medi Montaseri <medi.montaseri@intransa.com>; Neil Conway <neilc@samurai.com>; Francisco Reyes <lists@natserv.com>; pgsql General List <pgsql-general@postgresql.org>
Reread the original message I was replying to. He was worried that if I
was to delete rows then roll back, that a vacuum running while I had
deleted the tuples would prevent the rollback.
I.e. I was proving that you COULD roll back a transaction that had a
vacuum occur during it.
On 20 Nov 2002, Brian Hirt wrote:
> Scott,
>
> You did a rollback on the transaction, so the delete was never commited.
> I hope you aren't expecting vacuum to delete uncommited rows from the
> database, are you?
>
> --brian
>
> On Wed, 2002-11-20 at 16:12, scott.marlowe wrote:
> > On Wed, 20 Nov 2002, Medi Montaseri wrote:
> >
> > > Its my understanding that vacuum actually removes tuples that have been
> > > updated or deleted.
> > > Sort of like emptying your trash .... whence a tuple has been removed,
> > > no rollback can set the
> > > state back. If you have logically removed a tuple (not vacuumed yet),
> > > then one can rollback,
> > > but if you vacuum then you can not rollback.
> > >
> > > Now suppose transaction A decides to delete some tuples, a vacuum job
> > > comes along and
> > > deletes things (in parallel), trans A decides to rollback....engines who
> > > support parallel
> > > vacuum-ing and transactions such as PG 7.2 better have a way of
> > > protecting themselves
> > > against this....
> > >
> > > Correct me if ...
> >
> > Yes, you are wrong. Postgresql's vacuuming does NOT free tuples that are
> > still in a transaction, hence a full vacuum will hand waiting for the
> > transaction to complete or roll back. A normal 7.2 vacuum will simply
> > skip the in transaction tuples.
> >
> > For proof, try this: (Note A> and B> are used to represent two different
> > sessions)
> >
> > A> create table test (a text, id int);
> > A> insert into test (a,id) values ('abc',123);
> > A> begin;
> > A> delete from test where id=123;
> > B> vacuum;
> > A> rollback;
> > A> select * from test;
> > a | id
> > --------
> > abc| 123
> >
> > Still there.
> >
> >
> > >
> > > Neil Conway wrote:
> > >
> > > >Medi Montaseri <medi.montaseri@intransa.com> writes:
> > > >
> > > >
> > > >>I think from the data integrity point of view, vacuum is more
> > > >>important than vacuum full.
> > > >>
> > > >>
> > > >
> > > >Why would VACUUM have any effect on data integrity, either positive or
> > > >negative?
> > > >
> > > >Cheers,
> > > >
> > > >Neil
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Closing inactive connections OR user connections limits
@ 2002-11-21 00:09 scott.marlowe <scott.marlowe@ihs.com>
parent: Medi Montaseri <medi.montaseri@intransa.com>
0 siblings, 0 replies; 20+ messages in thread
From: scott.marlowe @ 2002-11-21 00:09 UTC (permalink / raw)
To: Medi Montaseri <medi.montaseri@intransa.com>; +Cc: pgsql General List <pgsql-general@postgresql.org>
On Wed, 20 Nov 2002, Medi Montaseri wrote:
> So I think you have shown that 7.2 achieves this by skiping current
> transactions....
> Thank you
You're welcome. Having re-read my response, I realize now it sounded
harsh, and I certainly didn't mean it that way.
Usually, when a bug shows up here involving race conditions they're so
rare that I've never run into them. And usually when Postgresql DOES make
a mistake, it's something like permanently making a tuple undeleteable or
non-vacuumable, i.e. it tends to err on the side of caution.
7.2 rocks by the way. It's rock solid for us, and we use it for dozens
and dozens of projects where I work. The Postgresql hackers have what I
call a "NASA space shot" mentality. Quite refreshing in a world of
"weekend drag racer" developers.
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2002-11-21 00:09 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-20 16:52 Closing inactive connections OR user connections limits Francisco Reyes <lists@natserv.com>
2002-11-20 17:54 ` Neil Conway <neilc@samurai.com>
2002-11-20 18:36 ` Francisco Reyes <lists@natserv.com>
2002-11-20 18:36 ` Neil Conway <neilc@samurai.com>
2002-11-20 18:56 ` Francisco Reyes <lists@natserv.com>
2002-11-20 21:15 ` Neil Conway <neilc@samurai.com>
2002-11-20 22:02 ` Re: Closing inactive connections OR user connections Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:11 ` Neil Conway <neilc@samurai.com>
2002-11-20 18:35 ` Medi Montaseri <medi.montaseri@intransa.com>
2002-11-20 18:54 ` Francisco Reyes <lists@natserv.com>
2002-11-20 19:00 ` Medi Montaseri <medi.montaseri@intransa.com>
2002-11-20 21:33 ` Neil Conway <neilc@samurai.com>
2002-11-20 22:08 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-20 22:47 ` Medi Montaseri <medi.montaseri@intransa.com>
2002-11-20 23:12 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-20 23:16 ` Re: Closing inactive connections OR user connections Brian Hirt <bhirt@mobygames.com>
2002-11-21 00:00 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-20 23:53 ` Medi Montaseri <medi.montaseri@intransa.com>
2002-11-21 00:09 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-20 19:32 ` scott.marlowe <scott.marlowe@ihs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox