Thread: Closing inactive connections OR user connections limits
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.
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
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?
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 > >
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
Attachment
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
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: >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 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
Attachment
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 > > >
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)
>
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
>>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)
>>
>>
>>
>
>
>
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
>
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.
Am Mittwoch, 20. November 2002 20:09 schrieb RenX SalomXo:
> 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
did you add a "CHECK" constraint, or did you use "NOT NULL" in the table definition? For the not null case it's easy:
update pg_attribute
set attnotnull=false
where attname='yourattributename'
and attrelid=(select oid from pg_class where relname='mytable' and relkind='r');
If you added a check constraint, you have to remove the corresponding row from pg_relcheck
and decrement the attribute "relchecks" in pg_class for the table, but I won't do it without having
a backup at hand.
Backup your data first.
Regards,
Mario Weilguni
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/