X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E84D79FA5BA for ; Thu, 8 Jun 2006 03:07:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75182-06 for ; Thu, 8 Jun 2006 03:07:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id 496F29FA3CD for ; Thu, 8 Jun 2006 03:07:46 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id b29so398035pya for ; Wed, 07 Jun 2006 23:07:45 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=uZ7zWvSU9QmLPwB+b2NOr41sx7F0Oy+ZqSfU0xB6gGAnzwDmM2fKEghJt33aEmuUSsiybRlnKlHZW5QOhmKDaH+0FvWwf1nCu0opAgyY2ge47yMpPjLViTSL72VxS4SdK8/TRMqqqB8G8BwmJm/hR7kICumxPMrY6fb7IhEYXlw= Received: by 10.35.106.18 with SMTP id i18mr446175pym; Wed, 07 Jun 2006 23:07:45 -0700 (PDT) Received: by 10.35.8.14 with HTTP; Wed, 7 Jun 2006 23:07:44 -0700 (PDT) Message-ID: <9f2e40a90606072307s7107630eh60e01c4f792fe3a8@mail.gmail.com> Date: Thu, 8 Jun 2006 11:37:44 +0530 From: "soni de" To: "Jim C. Nasby" Subject: Re: Regarding ALTER Command Cc: pgsql-performance@postgresql.org In-Reply-To: <20060607145020.GT45331@pervasive.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15750_2229363.1149746864911" References: <9f2e40a90606070543l1066f162v19fc0887d916e2bb@mail.gmail.com> <20060607145020.GT45331@pervasive.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/83 X-Sequence-Number: 19440 ------=_Part_15750_2229363.1149746864911 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello, We are planning to use latest postgres version. I have one query as below: One more thing I have to mention is that we are using 2 postmasters running on different machines and both are accessing same data directory. (i.e both the machines uses same tables or the databases) In that case if from first machine, continuous INSERT operation on any table are going on and from the second we have to update the same table using ALTER command. Would this create any problem because INSERT and ALTER operations are executed from the two different postmasters but for a same data directory? Would there be any data loss or in this case also ALTER will block all the new accesses to the table? Thanks, Soni On 6/7/06, Jim C. Nasby wrote: > > On Wed, Jun 07, 2006 at 06:13:11PM +0530, soni de wrote: > > Hello, > > > > > > > > We have database on which continueous operations of INSERT, DELETE, > UPDATE > > are going on, In the mean time irrespective of INSERT and UPDATE we want > to > > ALTER some filelds from the table can we do that? > > > > Would the ALTER command on heavily loaded database create any perfomance > > problem? > > > > Is it feasible to do ALTER when lots of INSERT operations are going on? > > The problem you'll run into is that ALTER will grab an exclusive table > lock. If *all* the transactions hitting the table are very short, this > shouldn't be too big of an issue; the ALTER will block all new accesses > to the table while it waits for all the pending ones to complete, but if > all the pending ones complete quickly it shouldn't be a big issue. > > If one of the pending statements takes a long time though... > > > Postgresql version we are using is -- PostgreSQL 7.2.4 > > You very badly need to upgrade. 7.2 is no longer supported, and there > have been over a half-dozen data loss bugs fixed since then. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > ------=_Part_15750_2229363.1149746864911 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hello,
 

We are planning to use latest postgres version. I have one query as below:

 

One more thing I have to mention is that we are using 2 postmasters running on different machines and both are accessing same data directory. ( i.e both the machines uses same tables or the databases)

In that case if from first machine, continuous INSERT operation on any table are going on and from the second we have to update the same table using ALTER command.

Would this create any problem because INSERT and ALTER operations are executed from the two different postmasters but for a same data directory?

Would there be any data loss or in this case also ALTER will block all the new accesses to the table?

 

Thanks,
Soni

 
On 6/7/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
On Wed, Jun 07, 2006 at 06:13:11PM +0530, soni de wrote:
> Hello,
>
>
>
> We have database on which continueous operations of INSERT, DELETE, UPDATE
> are going on, In the mean time irrespective of INSERT and UPDATE we want to
> ALTER some filelds from the table can we do that?
>
> Would the ALTER command on heavily loaded database create any perfomance
> problem?
>
> Is it feasible to do ALTER when lots of INSERT operations are going on?

The problem you'll run into is that ALTER will grab an exclusive table
lock. If *all* the transactions hitting the table are very short, this
shouldn't be too big of an issue; the ALTER will block all new accesses
to the table while it waits for all the pending ones to complete, but if
all the pending ones complete quickly it shouldn't be a big issue.

If one of the pending statements takes a long time though...

> Postgresql version we are using is -- PostgreSQL 7.2.4

You very badly need to upgrade. 7.2 is no longer supported, and there
have been over a half-dozen data loss bugs fixed since then.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

------=_Part_15750_2229363.1149746864911--