Re: ***SPAM*** Re: random huge delay when recreate a VIEW or FUNCTION

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: catalin(at)plationline(dot)eu, Melvin Davidson <melvin6925(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: ***SPAM*** Re: random huge delay when recreate a VIEW or FUNCTION
Date: 2016-06-17 16:40:46
Message-ID: ef739e3e-1783-6115-79e7-5e112e13d80f@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 06/17/2016 09:01 AM, Catalin Maftei wrote:

Please do not top post:

https://en.wikipedia.org/wiki/Posting_style

> well,
>
> the for your reply,
>
> we have PG on linux ubuntu 14.04 distribution, but pls remember this
> issue started when our DB size increase to 3-5GB, now it is 16GB.

See Merlins post about locking:

https://www.postgresql.org/message-id/CAHyXU0zm5N6WjnPuxc%3Dzx6ihDfkZai3BMS2WiR1%2BituV%3Dm8gFg%40mail.gmail.com

>
> this happend only when we use "*CREATE OR REPLACE"*
>
>
> since I can run a query and get reply all the time in less than 1sec,
> why do you consider "CREATE A VIEW" on the remote server is a bad idea?

It is not necessarily a bad idea. Just that running the command locally
on the server takes the whole intervening network out of the loop and
establishes a base point to work from. Troubleshooting is as much about
eliminating things as finding things.

>
> I have 1Gb connection between my laptop (windows 7/10) and remote server.
>
>
> Best regards,
> Catalin Maftei
> www.plationline.eu
> www.livrarionline.ro
> www.c-solution.biz
>
> Skype: c-solution
> Skype: catalinmaftei
> Mobile: +40723 338 598
>
> On 6/17/2016 2:55 AM, Melvin Davidson wrote:
>>
>>
>> On Wed, Jun 15, 2016 at 12:49 AM, Catalin Maftei
>> <<mailto:catalin(at)plationline(dot)eu>catalin(at)plationline(dot)eu> wrote:
>>
>> hei Adrian,
>>
>> thx for your reply,
>>
>> this is an example:
>>
>> "CREATE OR REPLACE VIEW feedback.get_answers_set AS
>> SELECT f.awb || '/' || r.dulapid as "awb",
>> q.qid,
>> q.question,
>> a.aid,
>> a.answer,
>> q.questionareid,
>> f.stamp_created
>> , c.first_name
>> , l.referinta_expeditor
>> FROM feedback.answers a
>> JOIN feedback.questions q ON a.qid = q.qid
>> JOIN feedback.feedback f ON f.qid = q.qid AND f.aid = a.aid
>> join public.livrari_details ld on ld.awb=f.awb
>> join public.livrari l on l.livrareid = ld.livrareid
>> join public.customers c on l.shipto_custkey = c.custkey
>> join dulap.rezervare r on r.rezid = l.rezervareid;
>>
>> ALTER TABLE feedback.get_answers_set
>> OWNER TO postgres;"
>>
>>
>> I use PGADMIN 1.22.1
>>
>> my server is remote and is replicated Master-Slave.
>>
>>
>> my team report this random delay all the time when we recreate
>> VIEWS and FUNCTIONS.
>>
>>
>>
>> Best regards,
>> Catalin Maftei
>> www.plationline.eu <http://www.plationline.eu>
>> www.livrarionline.ro <http://www.livrarionline.ro>
>> www.c-solution.biz <http://www.c-solution.biz>
>>
>> Skype: catalinmaftei
>> Mobile: +40723 338 598 <tel:%2B40723%20338%20598>
>>
>> On 6/15/2016 7:36 AM, Adrian Klaver wrote:
>>
>> On 06/14/2016 02:59 PM, Catalin Maftei wrote:
>>
>> when I recreate a VIEW or FUNCTION with a small change I get:
>>
>>
>> What are the actual statements you are running?
>>
>> What program are you running them from?
>>
>> Is the server you are running the command against local or remote?
>>
>>
>>
>>
>> Query returned successfully with no result in 03:58 minutes.
>>
>>
>> we have pg 9.4
>>
>> --
>> Best regards,
>> Catalin Maftei
>> www.plationline.eu <http://www.plationline.eu>
>> www.livrarionline.ro <http://www.livrarionline.ro>
>> www.c-solution.biz <http://www.c-solution.biz>
>>
>> Skype: catalinmaftei
>> Mobile: +40723 338 598 <tel:%2B40723%20338%20598>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list
>> (<mailto:pgsql-general(at)postgresql(dot)org>pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>> *You have not provided information as to the O/S of the PostgreSQL
>> server or your PgAdmin remote system (I suspect Windows), but
>> regardless, you
>> are just asking for trouble by doing development over a remote connection.
>> You should verify there is no delay by connecting directly to the
>> PostgreSQL server and testing the CREATE OR REPLACE there. I suspect
>> you will have none or very minimal delay.
>> Hopefully PostgreSQL is on a Linux O/S and you can use Putty for
>> Windows
>> http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
>> <http://www.chiark.greenend.org.uk/%7Esgtatham/putty/download.html> to
>> connect directly
>> and develop.
>> *--
>> *Melvin Davidson*
>> I reserve the right to fantasize. Whether or not you
>> wish to share my fantasy is entirely up to you.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2016-06-17 17:55:28 Re: PostgresSQL and HIPAA compliance
Previous Message Melvin Davidson 2016-06-17 16:30:41 Re: ***SPAM*** Re: random huge delay when recreate a VIEW or FUNCTION