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

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@plationline.eu>catalin@plationline.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@postgresql.org>pgsql-general@postgresql.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.klaver@aklaver.com