Обсуждение: random huge delay when recreate a VIEW or FUNCTION
On Wed, Jun 15, 2016 at 12:36 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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.klaver@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
In addition to Adrian's questions,
What is the O/S you are using?
How much total memory is in the system?
What is the O/S you are using?
How much total memory is in the system?
What are the values for shared_buffers, work_mem & maintenance_work_mem?
What is the actual SQL for the view and/or function change you are doing?
All of these can have a factor in your delay.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Вложения
On Wed, Jun 15, 2016 at 12:49 AM, Catalin Maftei <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
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 reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Fri, Jun 17, 2016 at 12:01 PM, Catalin Maftei <catalin@plationline.eu> 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 Maftei www.plationline.eu www.livrarionline.ro www.c-solution.biz Skype: c-solution Skype: catalinmaftei Mobile: +40723 338 598On 6/17/2016 2:55 AM, Melvin Davidson wrote:On Wed, Jun 15, 2016 at 12:49 AM, Catalin Maftei <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
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.