pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER STATEMENT getting blocked
4+ messages / 3 participants
[nested] [flat]
* ALTER STATEMENT getting blocked
@ 2023-01-19 17:30 aditya desai <admad123@gmail.com>
2023-01-19 17:45 ` Re: ALTER STATEMENT getting blocked Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: aditya desai @ 2023-01-19 17:30 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
We have a Postgres 11.16 DB which is continuously connected to informatica
and data gets read from it continuously.
When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
on the table mentioned by process above.
Is there any way to ALTER the table concurrently without getting blocked?
Any parameter or option? Can someone give a specific command?
Regards,
Aditya.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER STATEMENT getting blocked
2023-01-19 17:30 ALTER STATEMENT getting blocked aditya desai <admad123@gmail.com>
@ 2023-01-19 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-19 21:06 ` Re: ALTER STATEMENT getting blocked MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-01-19 17:45 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
aditya desai <admad123@gmail.com> writes:
> We have a Postgres 11.16 DB which is continuously connected to informatica
> and data gets read from it continuously.
> When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
> on the table mentioned by process above.
> Is there any way to ALTER the table concurrently without getting blocked?
> Any parameter or option? Can someone give a specific command?
ALTER TABLE requires exclusive lock to do that, so it will queue up
behind any existing table locks --- but then new lock requests will
queue up behind its request. So this'd only happen if your existing
reading transactions don't terminate. Very long-running transactions
are unfriendly to other transactions for lots of reasons including
this one; see if you can fix your application to avoid that. Or
manually cancel the blocking transaction(s) after the ALTER begins
waiting.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER STATEMENT getting blocked
2023-01-19 17:30 ALTER STATEMENT getting blocked aditya desai <admad123@gmail.com>
2023-01-19 17:45 ` Re: ALTER STATEMENT getting blocked Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-19 21:06 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-01-22 11:29 ` Re: ALTER STATEMENT getting blocked aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: MichaelDBA @ 2023-01-19 21:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: aditya desai <admad123@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Do something like this to get it without being behind other
transactions...You either get in and get your work done or try again
DO language plpgsql $$
BEGIN
FOR get_lock IN 1 .. 100 LOOP
BEGIN
ALTER TABLE mytable <do something>;
EXIT;
END;
END LOOP;
END;
$$;
Tom Lane wrote on 1/19/2023 12:45 PM:
> aditya desai <admad123@gmail.com> writes:
>> We have a Postgres 11.16 DB which is continuously connected to informatica
>> and data gets read from it continuously.
>> When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
>> on the table mentioned by process above.
>> Is there any way to ALTER the table concurrently without getting blocked?
>> Any parameter or option? Can someone give a specific command?
> ALTER TABLE requires exclusive lock to do that, so it will queue up
> behind any existing table locks --- but then new lock requests will
> queue up behind its request. So this'd only happen if your existing
> reading transactions don't terminate. Very long-running transactions
> are unfriendly to other transactions for lots of reasons including
> this one; see if you can fix your application to avoid that. Or
> manually cancel the blocking transaction(s) after the ALTER begins
> waiting.
>
> regards, tom lane
>
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../5687dadc-ffd7-458c-563d-441674fd9b04@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER STATEMENT getting blocked
2023-01-19 17:30 ALTER STATEMENT getting blocked aditya desai <admad123@gmail.com>
2023-01-19 17:45 ` Re: ALTER STATEMENT getting blocked Tom Lane <tgl@sss.pgh.pa.us>
2023-01-19 21:06 ` Re: ALTER STATEMENT getting blocked MichaelDBA <MichaelDBA@sqlexec.com>
@ 2023-01-22 11:29 ` aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: aditya desai @ 2023-01-22 11:29 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Thanks All. Let me check this and get back to you.
On Fri, Jan 20, 2023 at 2:36 AM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Do something like this to get it without being behind other
> transactions...You either get in and get your work done or try again
>
> DO language plpgsql $$
> BEGIN
> FOR get_lock IN 1 .. 100 LOOP
> BEGIN
> ALTER TABLE mytable <do something>;
> EXIT;
> END;
> END LOOP;
> END;
> $$;
>
>
>
> Tom Lane wrote on 1/19/2023 12:45 PM:
>
> aditya desai <admad123@gmail.com> <admad123@gmail.com> writes:
>
> We have a Postgres 11.16 DB which is continuously connected to informatica
> and data gets read from it continuously.
>
> When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
> on the table mentioned by process above.
>
> Is there any way to ALTER the table concurrently without getting blocked?
> Any parameter or option? Can someone give a specific command?
>
> ALTER TABLE requires exclusive lock to do that, so it will queue up
> behind any existing table locks --- but then new lock requests will
> queue up behind its request. So this'd only happen if your existing
> reading transactions don't terminate. Very long-running transactions
> are unfriendly to other transactions for lots of reasons including
> this one; see if you can fix your application to avoid that. Or
> manually cancel the blocking transaction(s) after the ALTER begins
> waiting.
>
> regards, tom lane
>
>
>
>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba@sqlexec.com <michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
>
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../CAN0SRDFWZ5Ptnufz=Rw3BK5VgO4uXpXpGKBfsBJacZNgBHx-DQ@mail.gmail.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-01-22 11:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-19 17:30 ALTER STATEMENT getting blocked aditya desai <admad123@gmail.com>
2023-01-19 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-19 21:06 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-01-22 11:29 ` aditya desai <admad123@gmail.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