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

2016-06-22 Thread Catalin Maftei 

  
  
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.
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?
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 
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
  www.livrarionline.ro
  www.c-solution.biz
  
  Skype: catalinmaftei
  Mobile: +40723 338 598
  
  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
  www.livrarionline.ro
  www.c-solution.biz
  
  Skype: catalinmaftei
  Mobile: +40723 338 598
  
  
  
  



  
  
  
  
  --
  Sent via pgsql-general mailing list (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
to connect directly 
and develop.
  -- 
  
Melvin
Davidson
  I reserve the right to
  fantasize.  Whether or not you

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

2016-06-17 Thread Adrian Klaver 

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
<cata...@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 
www.livrarionline.ro 
www.c-solution.biz 

Skype: catalinmaftei
Mobile: +40723 338 598 

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 
www.livrarionline.ro 
www.c-solution.biz 

Skype: catalinmaftei
Mobile: +40723 338 598 









--
Sent via pgsql-general mailing list
(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
 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2016-06-17 Thread Melvin Davidson 
On Fri, Jun 17, 2016 at 12:01 PM, Catalin Maftei 
wrote:

> 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.
>
> 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?
>
> I have 1Gb connection between my laptop (windows 7/10) and remote server.
>
>
> Best regards,
> Catalin Mafteiwww.plationline.euwww.livrarionline.rowww.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 <
> cata...@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
>> www.livrarionline.ro
>> www.c-solution.biz
>>
>> Skype: catalinmaftei
>> Mobile: +40723 338 598
>>
>> 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
 www.livrarionline.ro
 www.c-solution.biz

 Skype: catalinmaftei
 Mobile: +40723 338 598





>>>
>>>
>>
>>
>> --
>> Sent via pgsql-general mailing list ( 
>> 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
>  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.
>
>
>
I have personally used PgAdmin amd Putty on WIndows against a remote
PostgreSQL server on Ubuntu. I have noticed a large delay when using
PgAdmin versus Putty. Therefore I can only advise you that you are better
off connecting directly with Putty via ssh.

Have you tried what I suggested?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

2016-06-17 Thread Merlin Moncure 
On Thu, Jun 16, 2016 at 6:55 PM, Melvin Davidson  wrote:
 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 to connect 
> directly
> and develop.

I responded on his other thread.  Locks need to be ruled out before
investigating other problems.   Exotic causes of this problem could be
storage problems or weird network issues (especially on windows).  But
locks need to be ruled out first.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2016-06-16 Thread Melvin Davidson 
On Wed, Jun 15, 2016 at 12:49 AM, Catalin Maftei 
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
> www.livrarionline.ro
> www.c-solution.biz
>
> Skype: catalinmaftei
> Mobile: +40723 338 598
>
> 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
>>> www.livrarionline.ro
>>> www.c-solution.biz
>>>
>>> Skype: catalinmaftei
>>> Mobile: +40723 338 598
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
> --
> Sent via pgsql-general mailing list (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
 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.

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

2016-06-16 Thread Catalin Maftei 

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
www.livrarionline.ro
www.c-solution.biz

Skype: catalinmaftei
Mobile: +40723 338 598

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
www.livrarionline.ro
www.c-solution.biz

Skype: catalinmaftei
Mobile: +40723 338 598









<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2016-06-15 Thread Adrian Klaver 

On 06/14/2016 09:49 PM, Catalin Maftei 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.


I do not understand random and all the time? Is there always a delay, 
but the length of time is random or does a delay happen randomly?


So to be clear the team is also using pgAdmin to do the updates?

Have you tried some other tool, say psql, to narrow down where the 
problem might be?


Have you looked at the server logs in time frame the recreate scripts 
are run to see if something else is going on at the same time?






Best regards,
Catalin Maftei
www.plationline.eu
www.livrarionline.ro
www.c-solution.biz

Skype: catalinmaftei
Mobile: +40723 338 598

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
www.livrarionline.ro
www.c-solution.biz

Skype: catalinmaftei
Mobile: +40723 338 598












--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general