Closing inactive connections OR user connections limits

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

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:

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?

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.

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

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".

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?

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:

Does anybody could tell me how to drop a constraint on a column where no name was provided to the constraint?
How does Pg name constraints?

Thanks
--
Ren� Salom�o
Ibiz Tecnologia -- www.ibiz.com.br

do a \d tablename

for the name of the contraint.
say its $1
the do

psql> alter table <tablename> drop contstraint "$1" RESTRICT;

Does anybody could tell me how to drop a constraint on a column where no name was provided to
the constraint? How does Pg name constraints?

Thanks
--
Ren� Salom�o
Ibiz Tecnologia -- www.ibiz.com.br

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

http://archives.postgresql.org

-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

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.

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

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

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

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? :-)

Oliver Elphick <olly@lfix.co.uk> writes:

On Wed, 2002-11-20 at 18:36, Neil Conway wrote:

+         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?

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

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:

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.

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>

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