Thread: Server does not reply to Alter Table

Server does not reply to Alter Table

From
Patrick Dunford
Date:
Using pgsql 7.1.3 I have found that on occasions, there is no reply to an
SQL statement like

ALTER TABLE x
    ADD COLUMN y z

The table itself will be inaccessible for at least 10 minutes and/or error
messages will result. The column may or may not be added.

=================================
Patrick Dunford, Christchurch, NZ
http://pdunford.godzone.net.nz/
http://www.trainweb.org/ferrymead/


Commit , Rollback

From
"Yogesh Kansal"
Date:
Hi List ,
Could any one of you help me in finding  out a way by which
1)when i login to postgres "begin transaction " command  automatically
executes .
2)i write commit it should  commit as well as begin the new  transaction .
3) similarly with rollback.

from
yogesh






Re: Commit , Rollback

From
Doug McNaught
Date:
"Yogesh Kansal" <yogesh@davlin.co.in> writes:

> Hi List ,
> Could any one of you help me in finding  out a way by which
> 1)when i login to postgres "begin transaction " command  automatically
> executes .
> 2)i write commit it should  commit as well as begin the new  transaction .
> 3) similarly with rollback.

Do it at the application level.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Server does not reply to Alter Table

From
Tom Lane
Date:
Patrick Dunford <dunfordsoft@clear.net.nz> writes:
> Using pgsql 7.1.3 I have found that on occasions, there is no reply to an
> SQL statement like

> ALTER TABLE x
>     ADD COLUMN y z

You sure it's not just waiting for some other transaction to give up a
lock on the table?  ALTER TABLE ADD COLUMN should be essentially
instantaneous, since it doesn't touch the table data.  But it needs to
get exclusive lock on the table first.

            regards, tom lane

Re: Commit , Rollback

From
"Yogesh Kansal"
Date:
no i want this while quering on the  prompt of psql ... actually i want a
precautionary measure while doing updates , inserts or delete  to a table ..
i want this so that i can roll back the whole transaction  if i make some
mistake ..
thanks for reply.
yogesh

> "Yogesh Kansal" <yogesh@davlin.co.in> writes:
>
> > Hi List ,
> > Could any one of you help me in finding  out a way by which
> > 1)when i login to postgres "begin transaction " command  automatically
> > executes .
> > 2)i write commit it should  commit as well as begin the new  transaction .
> > 3) similarly with rollback.
>
> Do it at the application level.
>
> -Doug
> --
> Let us cross over the river, and rest under the shade of the trees.
>    --T. J. Jackson, 1863
>


--
Walking on water and developing software from a specification are easy if
both are frozen."
-- Edward V. Berard, "Life-Cycle Approaches"


Re: Commit , Rollback

From
Doug McNaught
Date:
"Yogesh Kansal" <yogesh@davlin.co.in> writes:

> no i want this while quering on the  prompt of psql ... actually i want a
> precautionary measure while doing updates , inserts or delete  to a table ..
> i want this so that i can roll back the whole transaction  if i make some
> mistake ..

Well, it can't be done without changing the code.  So change the code,
or live with it and remember to type BEGIN before you start working.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Server does not reply to Alter Table

From
Patrick Dunford
Date:
In article <25074.1012924704@sss.pgh.pa.us> in newsgroup
comp.databases.postgresql.general on Tue, 5 Feb 2002 16:10:06 +0000
(UTC), Tom Lane(tgl@sss.pgh.pa.us) said...
> Patrick Dunford <dunfordsoft@clear.net.nz> writes:
> > Using pgsql 7.1.3 I have found that on occasions, there is no reply to an
> > SQL statement like
>
> > ALTER TABLE x
> >     ADD COLUMN y z
>
> You sure it's not just waiting for some other transaction to give up a
> lock on the table?  ALTER TABLE ADD COLUMN should be essentially
> instantaneous, since it doesn't touch the table data.  But it needs to
> get exclusive lock on the table first.

Well if it doesn't send a message back to the client then there is no way
of knowing this.

I had it do the same thing on two Drop Table requests, eventually it came
back and said the relation does not exist so I knew then it had dropped
the table but not because it had replied to the first request.