Re: ALTER STATEMENT getting blocked
Thanks All. Let me check this and get back to you. On Fri, Jan 20, 2023 at 2:36 AM MichaelDBA 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 ; > EXIT; > END; > END LOOP; > END; > $$; > > > > Tom Lane wrote on 1/19/2023 12:45 PM: > > aditya desai 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 > > michael...@sqlexec.com > > 703-600-9343 > > > >
LIKE CLAUSE on VIEWS
Hi, Is there any way to improve performance of LIKE clause on VIEWS. select * From request_vw where upper(status) like '%CAPTURED%' - 28 seconds. select * from request_vw where status='CAPTURED' Application team is reluctant to change queries from the Application side to = instead of LIKE. Also as this is VIEW TRIGRAM nor normal indexes don't get used. Regards, Aditya.
Re: LIKE CLAUSE on VIEWS
Hi Aditya, If you share your view's query and the query you run against the view, it would help all of us to understand better. pg_trgm would be the life saver option for you, of course if you created it on the right column, with the right expression, and by using the right indexing method. It doesn't mean you can't use any index and indexes won't be used because it is a view, well, if you do it right. https://www.postgresql.org/docs/current/pgtrgm.html Best regards. Samed YILDIRIM On Sun, 22 Jan 2023 at 13:34, aditya desai wrote: > Hi, > Is there any way to improve performance of LIKE clause on VIEWS. > > select * From request_vw where upper(status) like '%CAPTURED%' - 28 > seconds. > > select * from request_vw where status='CAPTURED' > > Application team is reluctant to change queries from the Application side > to = instead of LIKE. > > Also as this is VIEW TRIGRAM nor normal indexes don't get used. > > > Regards, > Aditya. >
Re: LIKE CLAUSE on VIEWS
> > > On Sun, 22 Jan 2023 at 13:34, aditya desai wrote: > >> Hi, >> Is there any way to improve performance of LIKE clause on VIEWS. >> >> select * From request_vw where upper(status) like '%CAPTURED%' - 28 >> seconds. >> >> select * from request_vw where status='CAPTURED' >> >> Application team is reluctant to change queries from the Application side >> to = instead of LIKE. >> >> Also as this is VIEW TRIGRAM nor normal indexes don't get used. >> >> >> Regards, >> Aditya. >> > You could try using the `text_pattern_ops` operator class on your index on the `status` column: https://www.postgresql.org/docs/current/indexes-opclass.html
Re: LIKE CLAUSE on VIEWS
On Sun, Jan 22, 2023 at 6:34 AM aditya desai wrote: > Hi, > Is there any way to improve performance of LIKE clause on VIEWS. > > select * From request_vw where upper(status) like '%CAPTURED%' - 28 > seconds. > You would need to have an expression index over upper(status) to support such a query, not an index on status itself. It would probably be better to just use ILIKE rather than upper(), so `status ILIKE '%captured%'`, which can benefit from an index on "status" itself. Also as this is VIEW TRIGRAM nor normal indexes don't get used. > There is no problem in general using trigram indexes (or any other index types) on views. Maybe your view has particular features which inhibit the use of the index, but you haven't given any information which would be useful for assessing that. Did you try an index, or just assume it wouldn't work without trying? Cheers, Jeff >
