Re: [GENERAL] pg_advisory_lock problem

[Rémi Cura]

Hey,
just a quick follow-up for archive:

problem solved with pg_try_advisory...
using lock (ROW EXCLUSIVE) does'nt work (same duplicate key value)
using lock (ACCESS EXCLUSIVE) work (no error) but stop the parallelism (one
thread as to wait for another to go on).

The optimal solution would be to slice the big CTE into several successive
temp table creation, using a procedural language allowing control of
transaction.
This would allow to control visibility and use the powerful MVCC features.
(I'm unwilling to do that).

Cheers everybody and many thanks for the help,
Rémi-C


2014-08-12 14:45 GMT+02:00 Merlin Moncure :

> On Tue, Aug 12, 2014 at 5:07 AM, Rémi Cura  wrote:
> >
> >
> >
> > 2014-08-11 22:48 GMT+02:00 Kevin Grittner :
> >
> >> Rémi Cura  wrote:
> >>
> >> > as you (both?) suggested it works using advisory lock used at the
> >> > beginning and end of the transaction. This way there is no upsert
> >> > at all if the element is locked? (I used general advisory lockbut
> >> > in the same way as transactionnal lock)
> >>
> >> This is too vague to comment on.
> >>
> >> > The issue in this case is simple : I have to use about 100k
> >> > advisory locks, which is a big memory requirement for my hardware
> >> > :-(
> >>
> >> ... and that doesn't seem to make any sense.  Either you are not
> >> understanding advisory locks or you are doing something very, very
> >> unusual.
> >>
> >> > Merlin I'm afraid I don't understand what is "vanilla" LOCK
> >> > TABLE.
> >>
> >> See the LOCK TABLE command.
> >>
> >> http://www.postgresql.org/docs/current/interactive/sql-lock.html
> >>
> >>
> http://www.postgresql.org/docs/current/interactive/explicit-locking.html
> >>
> >> > I can't really use a lock table because each query upsert
> >> > sequentially into 3 tables, doing lots of computing between.
> >>
> >> Now *that* I understand.  :-)  It's not an unusual requirement,
> >> but can be a challenge when using snapshot isolation (where writes
> >> don't block reads and reads don't block anything).  There are two
> >> main approaches -- introduce blocking to serialize some of the
> >> operations, or use the SERIALIZABLE transaction isolation level to
> >> ensure that the behavior of all concurrent transactions is
> >> consistent with the behavior you would see if they were run one at
> >> a time.  The latter approach doesn't introduce any new blocking,
> >> but it can cause transactions to get an ERROR with a SQLSTATE of
> >> 40001 at just about any point, so you need to be prepared to
> >> recognize that and retry those transactions from the beginning (not
> >> just the last statement of the transaction), ignoring any data read
> >> during the failed attempt.
> >>
> >> You may want to read the entire chapter on concurrency control:
> >>
> >> http://www.postgresql.org/docs/current/interactive/mvcc.html
> >>
> >> If you are considering using SERIALIZABLE transactions, you should
> >> probably review the examples in the Wiki, to get an idea of how it
> >> behaves in various cases:
> >>
> >> http://wiki.postgresql.org/wiki/SSI
> >>
> >> > I use parallel query to compute faster (load dividing). I guess
> >> > it would be very slow with about 8 parallel queries with locks.
> >>
> >> Well, if you introduce blocking you reduce your parallelism, but if
> >> you use serializable transactions and there are actually a lot of
> >> conflicts you can see poor performance because of the errors
> >> rolling back transactions and the need to retry them from the
> >> start.  The techniques used to implement serializable transactions
> >> in PostgreSQL are basically a refinement of the Optimistic
> >> Concurrency Control (OCC) techniques, but generally with far fewer
> >> retries needed -- the point being that it optimistically assumes
> >> that there will not be a conflict so that concurrency is better,
> >> but has to cancel things if that optimism proves to be unfounded.
> >>
> >> To make related to changes to multiple tables and maintain coherent
> >> data, you probably will need to do one or the other.
> >>
> >> --
> >> Kevin Grittner
> >> EDB: http://www.enterprisedb.com
> >> The Enterprise PostgreSQL Company
> >
> >
> >
> > Hey, thanks for your detailed answer.
> >
> > The particularity here is that I use a big query with CTE instead of a
> more
> > procedural way.
> > I do sophisticated geometric computing using postGIS. I guess it is a
> hack
> > of both postgres and postgis.
> >
> > I explain better the pg_advisory locks uses I have tried.
> >
> > First classic use of pg_advisory, not working :
> > CTE_1 (find what rows will be upserted in table_1)
> > CTE_2 (find what rows will be upserted in table_2)
> > CTE_3 (find what rows will be upserted in table_3)
> > CTE_4 (compute the result to be upserted into table_1)
> > CTE_5 (upsert into table_1 using custom upsert_function)
> > CTE_6 (compute the result to be upserted into table_2)
> > CTE_7 (upsert into table_2 using custom upsert_function)
> > CTE_8 (compute the result to b

[GENERAL] create function : change name of input parameter

[Lori Corbani]

Using Postgres Version 9.0.4:

We are migrating our Sybase stored procedures to Postgres and need to be able 
to drop/replace the SPs, making needed changes to input parameters to fix 
issues/differences between Sybase and Postgres.

However, we keep getting this error when we drop/replace the PG/SP:

'ERROR : cannot change name of input parameter'

The  'create function' documentation states:

'You cannot change the name already assigned to any input parameter (although 
you can add names to parameters that had none before).'

Other than a complete restore of the database from a dump that does *not* 
contain the original SP...is there any other way to override this constraint?

This makes debugging *extremely* difficult.   If you don't have your input 
parameters completely correct the first time...you're hosed.

Many thanks.
Lori


The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.

Re: [GENERAL] create function : change name of input parameter

[Adrian Klaver]

On 08/20/2014 06:25 AM, Lori Corbani wrote:

Using Postgres Version 9.0.4:

We are migrating our Sybase stored procedures to Postgres and need to be
able to drop/replace the SPs, making needed changes to input parameters
to fix issues/differences between Sybase and Postgres.

However, we keep getting this error when we drop/replace the PG/SP:


Are you actually doing DROP and then CREATE OR REPLACE FUNCTION or just 
doing CREATE OR REPLACE FUNCTION ?


The first case will work, the second will not:

test=> CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
test$> BEGIN
test$> tax := subtotal * 0.06;
test$> END;
test$> $$ LANGUAGE plpgsql;
CREATE FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax 
real)

 RETURNS real
 LANGUAGE plpgsql
AS $function$
BEGIN 



tax := subtotal * 0.06; 



END; 



$function$ 




ERROR:  cannot change name of input parameter "subtotal"
HINT:  Use DROP FUNCTION sales_tax(real) first.

test=> drop function sales_tax(real) ;
DROP FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax 
real)

 RETURNS real
 LANGUAGE plpgsql
AS $function$
BEGIN
tax := subtotal * 0.06;
END;
$function$
;
CREATE FUNCTION





‘ERROR : cannot change name of input parameter’

The  ‘create function’ documentation states:

‘You cannot change the name already assigned to any input parameter
(although you can add names to parameters that had none before).’

Other than a complete restore of the database from a dump that does
**not** contain the original SP…is there any other way to override this
constraint?

This makes debugging **extremely** difficult.   If you don’t have your
input parameters completely correct the first time…you’re hosed.

Many thanks.

Lori

The information in this email, including attachments, may be
confidential and is intended solely for the addressee(s). If you believe
you received this email by mistake, please notify the sender by return
email as soon as possible.




--
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: [GENERAL] create function : change name of input parameter

[Alban Hertroys]

On 20 August 2014 15:25, Lori Corbani  wrote:
> The  ‘create function’ documentation states:
>
> ‘You cannot change the name already assigned to any input parameter
> (although you can add names to parameters that had none before).’

Further on in that same paragraph (although I looked at the 9.3 docs) it says:
'To do that, you must drop and recreate the function.'

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


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

Re: [GENERAL] create function : change name of input parameter

[Lori Corbani]

Yes, I am doing a DROP and then a CREATE OR REPLACE

Let me read over your example...



-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, August 20, 2014 9:40 AM
To: Lori Corbani; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 06:25 AM, Lori Corbani wrote:
> Using Postgres Version 9.0.4:
>
> We are migrating our Sybase stored procedures to Postgres and need to
> be able to drop/replace the SPs, making needed changes to input
> parameters to fix issues/differences between Sybase and Postgres.
>
> However, we keep getting this error when we drop/replace the PG/SP:

Are you actually doing DROP and then CREATE OR REPLACE FUNCTION or just doing 
CREATE OR REPLACE FUNCTION ?

The first case will work, the second will not:

test=> CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ test$> BEGIN
test$> tax := subtotal * 0.06;
test$> END;
test$> $$ LANGUAGE plpgsql;
CREATE FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax
real)
  RETURNS real
  LANGUAGE plpgsql
AS $function$
BEGIN


 tax := subtotal * 0.06;


END;


$function$



ERROR:  cannot change name of input parameter "subtotal"
HINT:  Use DROP FUNCTION sales_tax(real) first.

test=> drop function sales_tax(real) ;
DROP FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax
real)
  RETURNS real
  LANGUAGE plpgsql
AS $function$
BEGIN
 tax := subtotal * 0.06;
END;
$function$
;
CREATE FUNCTION



>
> 'ERROR : cannot change name of input parameter'
>
> The  'create function' documentation states:
>
> 'You cannot change the name already assigned to any input parameter
> (although you can add names to parameters that had none before).'
>
> Other than a complete restore of the database from a dump that does
> **not** contain the original SP...is there any other way to override this
> constraint?
>
> This makes debugging **extremely** difficult.   If you don't have your
> input parameters completely correct the first time...you're hosed.
>
> Many thanks.
>
> Lori
>
> The information in this email, including attachments, may be
> confidential and is intended solely for the addressee(s). If you believe
> you received this email by mistake, please notify the sender by return
> email as soon as possible.
>


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

The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.


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

Re: [GENERAL] create function : change name of input parameter

[Lori Corbani]

I *am* definitely dropping the function first.  I still get the same error.

-Original Message-
From: Alban Hertroys [mailto:haram...@gmail.com] 
Sent: Wednesday, August 20, 2014 9:44 AM
To: Lori Corbani
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 20 August 2014 15:25, Lori Corbani  wrote:
> The  ‘create function’ documentation states:
>
> ‘You cannot change the name already assigned to any input parameter 
> (although you can add names to parameters that had none before).’

Further on in that same paragraph (although I looked at the 9.3 docs) it says:
'To do that, you must drop and recreate the function.'

--
If you can't see the forest for the trees, Cut the trees and you'll see there 
is no forest.

The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.

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

Re: [GENERAL] create function : change name of input parameter

[Lori Corbani]

My example:

DROP FUNCTION ACC_setMax();

CREATE OR REPLACE FUNCTION ACC_setMax (
increment int,
prefixPart varchar(30) = 'MGI:'
)
RETURNS VOID AS
\$\$


BEGIN

/* Increment the max MGI Accession Number by @increment */
update ACC_AccessionMax
set maxNumericPart = maxNumericPart + increment
where prefixPart = prefixPart
;


END;
\$\$
LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION ACC_setMax(int,varchar) TO public;

*

When I change the parameter "prefixPart" to "v_prefixPart"I get the error.

prefixPart varchar(30) = 'MGI:'

where prefixPart = prefixPart

changed to

v_ prefixPart varchar(30) = 'MGI:'

where prefixPart = v_prefixPart


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, August 20, 2014 9:40 AM
To: Lori Corbani; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 06:25 AM, Lori Corbani wrote:
> Using Postgres Version 9.0.4:
>
> We are migrating our Sybase stored procedures to Postgres and need to
> be able to drop/replace the SPs, making needed changes to input
> parameters to fix issues/differences between Sybase and Postgres.
>
> However, we keep getting this error when we drop/replace the PG/SP:

Are you actually doing DROP and then CREATE OR REPLACE FUNCTION or just doing 
CREATE OR REPLACE FUNCTION ?

The first case will work, the second will not:

test=> CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ test$> BEGIN
test$> tax := subtotal * 0.06;
test$> END;
test$> $$ LANGUAGE plpgsql;
CREATE FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax
real)
  RETURNS real
  LANGUAGE plpgsql
AS $function$
BEGIN


 tax := subtotal * 0.06;


END;


$function$



ERROR:  cannot change name of input parameter "subtotal"
HINT:  Use DROP FUNCTION sales_tax(real) first.

test=> drop function sales_tax(real) ;
DROP FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax
real)
  RETURNS real
  LANGUAGE plpgsql
AS $function$
BEGIN
 tax := subtotal * 0.06;
END;
$function$
;
CREATE FUNCTION



>
> 'ERROR : cannot change name of input parameter'
>
> The  'create function' documentation states:
>
> 'You cannot change the name already assigned to any input parameter
> (although you can add names to parameters that had none before).'
>
> Other than a complete restore of the database from a dump that does
> **not** contain the original SP...is there any other way to override this
> constraint?
>
> This makes debugging **extremely** difficult.   If you don't have your
> input parameters completely correct the first time...you're hosed.
>
> Many thanks.
>
> Lori
>
> The information in this email, including attachments, may be
> confidential and is intended solely for the addressee(s). If you believe
> you received this email by mistake, please notify the sender by return
> email as soon as possible.
>


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

The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.


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

Re: [GENERAL] create function : change name of input parameter

[Adrian Klaver]

On 08/20/2014 06:51 AM, Lori Corbani wrote:


I *am* definitely dropping the function first.  I still get the same error.


Well we need to see the actual sequence to figure this out. Best guess, 
is you have more than one function with that name.





--
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: [GENERAL] create function : change name of input parameter

[Rob Sargent]

Include the types in the drop 

Sent from my iPhone

> On Aug 20, 2014, at 7:59 AM, Adrian Klaver  wrote:
> 
>> On 08/20/2014 06:51 AM, Lori Corbani wrote:
>> 
>> I *am* definitely dropping the function first.  I still get the same error.
> 
> Well we need to see the actual sequence to figure this out. Best guess, is 
> you have more than one function with that name.
> 
> 
> 
> 
> -- 
> 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


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

Re: [GENERAL] create function : change name of input parameter

[Adrian Klaver]

On 08/20/2014 07:00 AM, Lori Corbani wrote:


My example:

DROP FUNCTION ACC_setMax();

CREATE OR REPLACE FUNCTION ACC_setMax (
increment int,
prefixPart varchar(30) = 'MGI:'
)
RETURNS VOID AS
\$\$


BEGIN

/* Increment the max MGI Accession Number by @increment */
update ACC_AccessionMax
set maxNumericPart = maxNumericPart + increment
where prefixPart = prefixPart
;


END;
\$\$
LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION ACC_setMax(int,varchar) TO public;

*

When I change the parameter "prefixPart" to "v_prefixPart"I get the error.

prefixPart varchar(30) = 'MGI:'

where prefixPart = prefixPart

changed to

v_ prefixPart varchar(30) = 'MGI:'

where prefixPart = v_prefixPart



Can we see the actual sequence in psql with the command outputs?

Also what is escaping the $?


--
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: [GENERAL] create function : change name of input parameter

[Adrian Klaver]

On 08/20/2014 07:00 AM, Lori Corbani wrote:


My example:

DROP FUNCTION ACC_setMax();

CREATE OR REPLACE FUNCTION ACC_setMax (
increment int,
prefixPart varchar(30) = 'MGI:'
)
RETURNS VOID AS
\$\$


BEGIN

/* Increment the max MGI Accession Number by @increment */
update ACC_AccessionMax
set maxNumericPart = maxNumericPart + increment
where prefixPart = prefixPart
;


END;
\$\$
LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION ACC_setMax(int,varchar) TO public;

*

When I change the parameter "prefixPart" to "v_prefixPart"I get the error.

prefixPart varchar(30) = 'MGI:'

where prefixPart = prefixPart

changed to

v_ prefixPart varchar(30) = 'MGI:'

where prefixPart = v_prefixPart


It worked here on Postgres 9.3 and un-escaping the $.


--
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: [GENERAL] create function : change name of input parameter

[Tom Lane]

Lori Corbani  writes:
> My example:

> DROP FUNCTION ACC_setMax();

> CREATE OR REPLACE FUNCTION ACC_setMax (
> increment int,
> prefixPart varchar(30) = 'MGI:'
> )
> RETURNS VOID AS
> \$\$

This is not the right thing: you need to include the parameters in the
drop command.  ACC_setMax() is a totally different function from
ACC_setMax(int, varchar).

regards, tom lane


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

[GENERAL] Linux replication to FreeBSD problem

[Jov]

I setup a PG 9.3.5 master on CentOS 6 x86_64,and 2 screaming replicaton
slaves,one on CentOS6 x86_64,the other on FreeBSD 10 amd64.
The replication work fine for a week,But today I find a problem on sql
running on FreeBSD:simple sql use index do not return result.If I disable
the index ,use seqscan,then I can get correct result.

psql (9.3.5)
Type "help" for help.^
xx=> explain analyze verbose select order_id from orders where order_id =
'AAGJfwJYUzD3';
 QUERY PLAN


 Bitmap Heap Scan on public.orders  (cost=4.47..31.06 rows=7 width=17)
(actual time=0.317..0.317 rows=0 loops=1)
   Output: order_id
   Recheck Cond: ((orders.order_id)::text = 'AAGJfwJYUzD3'::text)
   ->  Bitmap Index Scan on orders_order_id_idx  (cost=0.00..4.47 rows=7
width=0) (actual time=0.252..0.252 rows=0 loops=1)
 Index Cond: ((orders.order_id)::text = 'AAGJfwJYUzD3'::text)
 Total runtime: 0.753 ms
(6 rows)

xx=> set enable_indexonlyscan to f;
SET
xx=> set enable_indexscan to f;
SET
xx=> set enable_bitmapscan to f;
SET
forex=> explain analyze verbose select order_id from orders where order_id
= 'AAGJfwJYUzD3';
 QUERY PLAN

-
 Seq Scan on public.orders  (cost=0.00..2185.69 rows=7 width=17) (actual
time=70.003..71.238 rows=1 loops=1)
   Output: order_id
   Filter: ((orders.order_id)::text = 'AAGJfwJYUzD3'::text)
   Rows Removed by Filter: 63481
 Total runtime: 71.379 ms
(5 rows)

So,Is this a un-support replication method?

Jov
blog: http:amutu.com/blog 

Re: [GENERAL] Linux replication to FreeBSD problem

[Adrian Klaver]

On 08/20/2014 07:53 AM, Jov wrote:

I setup a PG 9.3.5 master on CentOS 6 x86_64,and 2 screaming replicaton
slaves,one on CentOS6 x86_64,the other on FreeBSD 10 amd64.
The replication work fine for a week,But today I find a problem on sql
running on FreeBSD:simple sql use index do not return result.If I
disable the index ,use seqscan,then I can get correct result.




So,Is this a un-support replication method?


https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#5_Minutes_to_Simple_Replication

You must have the right setup to make this work:

2 servers with similar operating systems (e.g both Linux 64-bit).

I would say FreeBSD is not similar enough to Linux(CentOS) to make this 
work reliably with binary replication.




Jov
blog: http:amutu.com/blog 



--
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: [GENERAL] Linux replication to FreeBSD problem

[Tom Lane]

Adrian Klaver  writes:
> On 08/20/2014 07:53 AM, Jov wrote:
>> I setup a PG 9.3.5 master on CentOS 6 x86_64,and 2 screaming replicaton
>> slaves,one on CentOS6 x86_64,the other on FreeBSD 10 amd64.
>> The replication work fine for a week,But today I find a problem on sql
>> running on FreeBSD:simple sql use index do not return result.If I
>> disable the index ,use seqscan,then I can get correct result.

> I would say FreeBSD is not similar enough to Linux(CentOS) to make this 
> work reliably with binary replication.

The most likely theory as to the source of the problem is that the locale
names used by the CentOS machine are not recognized by the FreeBSD OS,
and/or imply slightly different sort orderings.  So a text index that's
correctly sorted according to the CentOS machine is not correctly sorted
according to FreeBSD, leading to search failures.

You could probably make this case work reliably if you used C locale on
both systems; the behavior of that is pretty portable.

regards, tom lane


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

Re: [GENERAL] create function : change name of input parameter

[Lori Corbani]

OK...if I manually run this within psql it is fine.

But I have a shell script in which I am wrapping a call to 'psql'...and I guess 
it does not like how things are being passed in when I do it this way.

So, yes, it is working correctly when I copy/paste my script directly into psql.

Will try a different approach to my wrapper.

Many thanks!
Lori



-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, August 20, 2014 10:11 AM
To: Lori Corbani; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 07:00 AM, Lori Corbani wrote:
>
> My example:
>
> DROP FUNCTION ACC_setMax();
>
> CREATE OR REPLACE FUNCTION ACC_setMax ( increment int, prefixPart
> varchar(30) = 'MGI:'
> )
> RETURNS VOID AS
> \$\$
>
>
> BEGIN
>
> /* Increment the max MGI Accession Number by @increment */ update
> ACC_AccessionMax set maxNumericPart = maxNumericPart + increment where
> prefixPart = prefixPart ;
>
>
> END;
> \$\$
> LANGUAGE plpgsql;
>
> GRANT EXECUTE ON FUNCTION ACC_setMax(int,varchar) TO public;
>
> *
>
> When I change the parameter "prefixPart" to "v_prefixPart"I get the error.
>
> prefixPart varchar(30) = 'MGI:'
>
> where prefixPart = prefixPart
>
> changed to
>
> v_ prefixPart varchar(30) = 'MGI:'
>
> where prefixPart = v_prefixPart
>

Can we see the actual sequence in psql with the command outputs?

Also what is escaping the $?


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

The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.


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

Re: [GENERAL] create function : change name of input parameter

[Lori Corbani]

This worked!  Many, many, thanks!

-Original Message-
From: Rob Sargent [mailto:robjsarg...@gmail.com]
Sent: Wednesday, August 20, 2014 10:15 AM
To: Adrian Klaver
Cc: Lori Corbani; Alban Hertroys; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

Include the types in the drop

Sent from my iPhone

> On Aug 20, 2014, at 7:59 AM, Adrian Klaver  wrote:
>
>> On 08/20/2014 06:51 AM, Lori Corbani wrote:
>>
>> I *am* definitely dropping the function first.  I still get the same error.
>
> Well we need to see the actual sequence to figure this out. Best guess, is 
> you have more than one function with that name.
>
>
>
>
> --
> 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

The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.


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

Re: [GENERAL] create function : change name of input parameter

[Adrian Klaver]

On 08/20/2014 08:42 AM, Lori Corbani wrote:


OK...if I manually run this within psql it is fine.

But I have a shell script in which I am wrapping a call to 'psql'...and I guess 
it does not like how things are being passed in when I do it this way.

So, yes, it is working correctly when I copy/paste my script directly into psql.

Will try a different approach to my wrapper.


An additional thing to watch out for is the mixed case in the function 
name. To demonstrate using a table name:


aklaver@test=> create table "CamelCap_Quoted" (id int);
CREATE TABLE
aklaver@test=> create table CamelCap_Not_Quoted (id int);
CREATE TABLE
aklaver@test=> select * from CamelCap_Quoted;
ERROR:  relation "camelcap_quoted" does not exist
LINE 1: select * from CamelCap_Quoted;
  ^
aklaver@test=> select * from "CamelCap_Quoted";
 id 



 



(0 rows) 






aklaver@test=> select * from CamelCap_Not_Quoted; 



 id 



 



(0 rows)


aklaver@test=> \d
 List of relations
 Schema |Name |   Type   |  Owner
+-+--+--
 public | CamelCap_Quoted | table| aklaver
 public | camelcap_not_quoted | table| aklaver


If the name is quoted the case is preserved and you have to quote the 
name to get the same object. A lot of frameworks/ORMS automatically 
quote object names so this something to watch out for. In the unquoted 
case the name is folded to lower case by default. So you can get in a 
situation where you have both the quoted and unquoted name and not be 
working on the object you think you are.




Many thanks!
Lori




--
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: [GENERAL] create function : change name of input parameter

[Lori Corbani]

It worked when I included the parameter list in the DROP statement.

Thank you!
Lori


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Wednesday, August 20, 2014 11:54 AM
To: Lori Corbani; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 08:42 AM, Lori Corbani wrote:
>
> OK...if I manually run this within psql it is fine.
>
> But I have a shell script in which I am wrapping a call to 'psql'...and I 
> guess it does not like how things are being passed in when I do it this way.
>
> So, yes, it is working correctly when I copy/paste my script directly into 
> psql.
>
> Will try a different approach to my wrapper.

An additional thing to watch out for is the mixed case in the function name. To 
demonstrate using a table name:

aklaver@test=> create table "CamelCap_Quoted" (id int); CREATE TABLE 
aklaver@test=> create table CamelCap_Not_Quoted (id int); CREATE TABLE 
aklaver@test=> select * from CamelCap_Quoted;
ERROR:  relation "camelcap_quoted" does not exist LINE 1: select * from 
CamelCap_Quoted;
   ^
aklaver@test=> select * from "CamelCap_Quoted";
  id





(0 rows)





aklaver@test=> select * from CamelCap_Not_Quoted;


  id





(0 rows)


aklaver@test=> \d
  List of relations
  Schema |Name |   Type   |  Owner
+-+--+--
  public | CamelCap_Quoted | table| aklaver
  public | camelcap_not_quoted | table| aklaver


If the name is quoted the case is preserved and you have to quote the name to 
get the same object. A lot of frameworks/ORMS automatically quote object names 
so this something to watch out for. In the unquoted case the name is folded to 
lower case by default. So you can get in a situation where you have both the 
quoted and unquoted name and not be working on the object you think you are.

>
> Many thanks!
> Lori
>


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

The information in this email, including attachments, may be confidential and 
is intended solely for the addressee(s). If you believe you received this email 
by mistake, please notify the sender by return email as soon as possible.


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

Re: [GENERAL] initial auth failure on debian

[TigerNassau]

Even with latest upgrades on debian jessie after a new postgres installation 
typing the following:  "su - postgres" requests a password and trying several 
such as postgres, blank, sudo password - all gives an auth error.   How can 
this be fixed?


Sent from my LG Mobile

Lori Corbani  wrote:

>
>It worked when I included the parameter list in the DROP statement.
>
>Thank you!
>Lori
>
>
>-Original Message-
>From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
>Sent: Wednesday, August 20, 2014 11:54 AM
>To: Lori Corbani; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] create function : change name of input parameter
>
>On 08/20/2014 08:42 AM, Lori Corbani wrote:
>>
>> OK...if I manually run this within psql it is fine.
>>
>> But I have a shell script in which I am wrapping a call to 'psql'...and I 
>> guess it does not like how things are being passed in when I do it this way.
>>
>> So, yes, it is working correctly when I copy/paste my script directly into 
>> psql.
>>
>> Will try a different approach to my wrapper.
>
>An additional thing to watch out for is the mixed case in the function name. 
>To demonstrate using a table name:
>
>aklaver@test=> create table "CamelCap_Quoted" (id int); CREATE TABLE 
>aklaver@test=> create table CamelCap_Not_Quoted (id int); CREATE TABLE 
>aklaver@test=> select * from CamelCap_Quoted;
>ERROR:  relation "camelcap_quoted" does not exist LINE 1: select * from 
>CamelCap_Quoted;
>   ^
>aklaver@test=> select * from "CamelCap_Quoted";
>  id
>
>
>
>
>
>(0 rows)
>
>
>
>
>
>aklaver@test=> select * from CamelCap_Not_Quoted;
>
>
>  id
>
>
>
>
>
>(0 rows)
>
>
>aklaver@test=> \d
>  List of relations
>  Schema |Name |   Type   |  Owner
>+-+--+--
>  public | CamelCap_Quoted | table| aklaver
>  public | camelcap_not_quoted | table| aklaver
>
>
>If the name is quoted the case is preserved and you have to quote the name to 
>get the same object. A lot of frameworks/ORMS automatically quote object names 
>so this something to watch out for. In the unquoted case the name is folded to 
>lower case by default. So you can get in a situation where you have both the 
>quoted and unquoted name and not be working on the object you think you are.
>
>>
>> Many thanks!
>> Lori
>>
>
>
>--
>Adrian Klaver
>adrian.kla...@aklaver.com
>
>The information in this email, including attachments, may be confidential and 
>is intended solely for the addressee(s). If you believe you received this 
>email by mistake, please notify the sender by return email as soon as possible.
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

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

[GENERAL] POWA tool

[Ramesh T]

Hello,

 when i ran  following query,
  postgres=# SELECT * FROM pg_stat_statements;


  ERROR:  relation "pg_stat_statements" does not exist
  LINE 1: SELECT * FROM pg_stat_statements;


i need to install POWA..i got powa.zip
 please let me know how to install POWA.ZIP for my postgres using putty
tool ..

thanks,

Re: [GENERAL] POWA tool

[Raymond O'Donnell]

On 20/08/2014 16:41, Ramesh T wrote:
> Hello,
> 
>  when i ran  following query,
>   postgres=# SELECT * FROM pg_stat_statements;
> 
> 
>   ERROR:  relation "pg_stat_statements" does not exist
>   LINE 1: SELECT * FROM pg_stat_statements;
> 
> 
> i need to install POWA..i got powa.zip
>  please let me know how to install POWA.ZIP for my postgres using putty
> tool ..

What is POWA?

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] initial auth failure on debian

[Adrian Klaver]

On 08/20/2014 09:22 AM, TigerNassau wrote:

Even with latest upgrades on debian jessie after a new postgres installation typing the 
following:  "su - postgres" requests a password and trying several such as 
postgres, blank, sudo password - all gives an auth error.   How can this be fixed?


Here is a good step by step:

http://www.pontikis.net/blog/postgresql-9-debian-7-wheezy

So:

su -l root
su - postgres
psql

or what I do:

sudo -u postgres psql




Sent from my LG Mobile




--
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: [GENERAL] POWA tool

[Thomas Kellerer]

Ramesh T schrieb am 20.08.2014 um 17:41:
> Hello,
> 
>  when i ran  following query,
>   postgres=# SELECT * FROM pg_stat_statements;
> 
> 
>   ERROR:  relation "pg_stat_statements" does not exist
>   LINE 1: SELECT * FROM pg_stat_statements;
> 
> 
> i need to install POWA..i got powa.zip

No, you need to install the extension pg_stat_statements:

http://www.postgresql.org/docs/current/static/pgstatstatements.html
http://www.postgresql.org/docs/current/static/sql-createextension.html




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

Re: [GENERAL] POWA tool

[Adrian Klaver]

On 08/20/2014 08:41 AM, Ramesh T wrote:

Hello,

  when i ran  following query,
   postgres=# SELECT * FROM pg_stat_statements;

   ERROR:  relation "pg_stat_statements" does not exist
   LINE 1: SELECT * FROM pg_stat_statements;


i need to install POWA..i got powa.zip
  please let me know how to install POWA.ZIP for my postgres using putty
tool ..


Have no idea what POWA is.

As to your error:

http://www.postgresql.org/docs/9.3/static/pgstatstatements.html



thanks,




--
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: [GENERAL] POWA tool

[Raghu Ram]

On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell  wrote:

> On 20/08/2014 16:41, Ramesh T wrote:
> > Hello,
> >
> >  when i ran  following query,
> >   postgres=# SELECT * FROM pg_stat_statements;
> >
> >
> >   ERROR:  relation "pg_stat_statements" does not exist
> >   LINE 1: SELECT * FROM pg_stat_statements;
> >
> >
> > i need to install POWA..i got powa.zip
> >  please let me know how to install POWA.ZIP for my postgres using putty
> > tool ..
>

are you referring below Tool ?

PoWA is PostgreSQL Workload Analyzer that gathers performance stats and
provides real-time charts and graph to help monitor and tune your
PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW.

http://www.postgresql.org/about/news/1537/

Thanks & Regards
Raghu Ram

Re: [GENERAL] Best practices for cloning DB servers

[Bill Mitchell]

Andy - I guess that uploading your WAL to S3 at least avoids the turmoil of 
running your database within a VPC that would definitely be an advantage.

I had imagined that replaying the WAL to get caught up from a baseline backup 
would be prohibitively slow versus simply snapshotting, but having the ability 
to go to a specific point in time hasn’t been a concern for us.

Thanks for the reply!
Bill


From: Andy Lau mailto:a...@infer.com>>
Date: Tuesday, August 19, 2014 at 3:04 PM
To: Joseph Kregloh mailto:jkreg...@sproutloud.com>>
Cc: WILLIAM MITCHELL mailto:b...@publicrelay.com>>, 
"pgsql-general@postgresql.org" 
mailto:pgsql-general@postgresql.org>>
Subject: Re: [GENERAL] Best practices for cloning DB servers

Thanks for the responses. Bill - We currently use wal-e to upload our WAL logs 
to S3. We actually don't keep our logs around for that long, so we don't have a 
problem with the size of our logs or snapshots.

I think we're going to go with our current solution, but during our process of 
cloning, point the restore command to the WAL log location of the source 
PostgreSQL, and point the archive command to a different location in S3. We 
already have a wrapper around wal-e that sets the S3 prefix, so it's easy to 
extend it to a separate wrapper for restore vs archive.



On Thu, Aug 14, 2014 at 9:08 PM, Joseph Kregloh 
mailto:jkreg...@sproutloud.com>> wrote:
Why don't you try using Barman? It allows you to take snapshots and do PITR. 
Not to mention you can use it as it's intended purpose as a backup engine.

-Joseph


On Thu, Aug 14, 2014 at 1:53 PM, Bill Mitchell 
mailto:b...@publicrelay.com>> wrote:
We are running our own Postgres server on AWS as well (since amazon RDS doesn't 
support read replicas yet)

In out case, simply having a streaming replication standby works - and we do 
our pg_dump from that -- or simply snapshot the machine and then promote the 
replica to master to use full data set in QA

I would have thought that shipping WAL file into S3 would have been problematic 
- I'd be interested in the size of the data set and the experiences you've had 
with that


Regards
Bill

Sent from my iPhone

> On Aug 14, 2014, at 12:17, "Andy Lau" mailto:a...@infer.com>> 
> wrote:
>
> Hi everyone,
>
> I had a question about some best practices. Our situation is that we want to 
> be able to clone a database server. Our single database server is hosted in 
> AWS, we take EBS snapshots every so often, and upload our WAL logs to S3. We 
> want to be able to start a new server from a snapshot, replay the WAL logs to 
> get to a specific point in time, then start using the database from there. 
> The problem we ran into here was that this exact clone started uploading WAL 
> logs to our S3 archive, mixing them up with the original WAL logs. Since this 
> is effectively a branch off of the original DB, mixing up the logs is very 
> bad. A solution here could be to just point clones to a different location in 
> S3 so they won't collide, but I was wondering if there were any best 
> practices for doing this.
>
> Also would appreciate any advice on cloning DB servers in general. A few of 
> our use cases include restoring to a previous good DB to experiment while 
> keeping the production DB unaffected, and testing Postgres version upgrades 
> (9.1 to 9.3).
>
> Thanks!
> -Andy


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

[GENERAL] Trigger to a queue for sending updates to a cache layer

[Marcus Engene]

Hi,

I'm working with a retail site with tens of millions of products in 
several languages.


For the detail pages, we try to cache in memcached. We also have quite a 
bit of keyword translation lookups (for international queries to solr).


We're thinking of adding a nosql layer that takes the big beating with 
the mundane reads. I don't fancy manually adding code to every possible 
place in the php code that touches the related tables.


What fast trigger solution would you use for this? I'm thinking of 
something that would just push (via fdw?) data to a queue; table name 
and item id. Naturally it should not slow down pg noticable and have 
stellar uptime.


Is the whole concept a stupid idea?

Thanks,
Marcus



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

Re: [GENERAL] POWA tool

[Julien Rouhaud]

On Wed, Aug 20, 2014 at 6:46 PM, Thomas Kellerer  wrote:

> Ramesh T schrieb am 20.08.2014 um 17:41:
> > Hello,
> >
> >  when i ran  following query,
> >   postgres=# SELECT * FROM pg_stat_statements;
> >
> >
> >   ERROR:  relation "pg_stat_statements" does not exist
> >   LINE 1: SELECT * FROM pg_stat_statements;
> >
> >
> > i need to install POWA..i got powa.zip
>
> No, you need to install the extension pg_stat_statements:
>
> http://www.postgresql.org/docs/current/static/pgstatstatements.html
> http://www.postgresql.org/docs/current/static/sql-createextension.html
>


You also need the extensions plpgsql (which should already be installed)
and btree_gist.

When installing the powa extension (CREATE EXTENSION powa;), postgres will
warn you of missing dependancy if any.

You can also refer to the installation documentation (
https://github.com/dalibo/powa/blob/master/README.md).

Regards.
--
Julien Rouhaud
http://www.dalibo.com

[GENERAL] JsonB Gin Index is very large; is there a work around?

[Larry White]

Hi, I'm using 9.4 beta 2.

I ran a test using 4 of the largest Json docs from our production data set.
The four files total to 59.4 MB of raw json, which is compressed by TOAST
to 21 MB, which is great.

The index, though, is 47 MB, bringing the total size of the data in PG to
68 MB. The index was created as:

CREATE INDEX document_payload_idx
  ON document
  USING gin
  (payload jsonb_path_ops);

I recognize that these may be reasonably considered pathological cases. My
questions are:

Is the work that was done to reduce GIN index size in this release?

Is there anyway to index a subset of the data in a JSONB column? I'm
thinking of something like declaring certain paths to be indexed?

Any suggestions would be greatly appreciated.

Re: [GENERAL] JsonB Gin Index is very large; is there a work around?

[Peter Geoghegan]

On Wed, Aug 20, 2014 at 1:53 PM, Larry White  wrote:
> Is there anyway to index a subset of the data in a JSONB column? I'm
> thinking of something like declaring certain paths to be indexed?


Yes. See the expression index example in the jsonb documentation.

-- 
Regards,
Peter Geoghegan


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

Re: [GENERAL] JsonB Gin Index is very large; is there a work around?

[Larry White]

Ok Thank you.

FWIW, the documents (which I can't share) consist mainly of a long list of
integers in the form {"n":"41515920318427252715"}, so they really are
outliers.


On Wed, Aug 20, 2014 at 5:09 PM, Peter Geoghegan <
peter.geoghega...@gmail.com> wrote:

> On Wed, Aug 20, 2014 at 1:53 PM, Larry White  wrote:
> > Is there anyway to index a subset of the data in a JSONB column? I'm
> > thinking of something like declaring certain paths to be indexed?
>
>
> Yes. See the expression index example in the jsonb documentation.
>
> --
> Regards,
> Peter Geoghegan
>

Re: [GENERAL] POWA tool

[Ramesh T]

yes,

in my postgres.conf pg_stat_statements is not their  needs powa is released
19 aug.


On Wed, Aug 20, 2014 at 10:17 PM, Raghu Ram  wrote:

> On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell  wrote:
>
>> On 20/08/2014 16:41, Ramesh T wrote:
>> > Hello,
>> >
>> >  when i ran  following query,
>> >   postgres=# SELECT * FROM pg_stat_statements;
>> >
>> >
>> >   ERROR:  relation "pg_stat_statements" does not exist
>> >   LINE 1: SELECT * FROM pg_stat_statements;
>> >
>> >
>> > i need to install POWA..i got powa.zip
>> >  please let me know how to install POWA.ZIP for my postgres using putty
>> > tool ..
>>
>
> are you referring below Tool ?
>
> PoWA is PostgreSQL Workload Analyzer that gathers performance stats and
> provides real-time charts and graph to help monitor and tune your
> PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW.
>
> http://www.postgresql.org/about/news/1537/
>
> Thanks & Regards
> Raghu Ram
>

[GENERAL] Query planner question

[Soni M]

Hi Everyone,

I have this query :

select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and t.ticket_number = tb.ticket_number
and tb.parse_date > ('2014-07-31');

Execution plan: http://explain.depesz.com/s/YAak

Indexes on ticket :
"ticket_pkey" PRIMARY KEY, btree (ticket_id) CLUSTER
"ticket_by_latest_transmission" btree (latest_transmission_id)
"ticket_by_ticket_number" btree (ticket_number)

This query only returns some portions of rows from ticket table.
The question is, Why does postgres need to get all the rows from ticket
table in order to complete this query?
Can't postgres use indexes to get only needed rows on ticket table?

I try set seqscan to off, but still index scan try to get all rows on
ticket table.
Here's the execution plan : http://explain.depesz.com/s/abH2

Thanks

-- 
Regards,

Soni Maula Harriz

Re: [GENERAL] Query planner question

[David G Johnston]

Soni M wrote
> Hi Everyone,
> 
> I have this query :
> 
> select t.ticket_id ,
> tb.transmission_id
> from ticket t,
> transmission_base tb
> where t.latest_transmission_id = tb.transmission_id
> and t.ticket_number = tb.ticket_number
> and tb.parse_date > ('2014-07-31');
> 
> Execution plan: http://explain.depesz.com/s/YAak
> 
> Indexes on ticket :
> "ticket_pkey" PRIMARY KEY, btree (ticket_id) CLUSTER
> "ticket_by_latest_transmission" btree (latest_transmission_id)
> "ticket_by_ticket_number" btree (ticket_number)
> 
> This query only returns some portions of rows from ticket table.
> The question is, Why does postgres need to get all the rows from ticket
> table in order to complete this query?
> Can't postgres use indexes to get only needed rows on ticket table?
> 
> I try set seqscan to off, but still index scan try to get all rows on
> ticket table.
> Here's the execution plan : http://explain.depesz.com/s/abH2

Short answer: you haven't defined "(latest_transmission_id, ticket_number)"
as being a foreign key onto the transmission_base table yet you seem to want
it to act like one.

Because of this failure the planner considers the following:

Nested Looping over 380,000 records is going to suck so it tries some
advanced "merge/join" techniques to try and speed things up.  In any such
alternative the entire ticket table needs to be considered since there is no
constraint provided for that table - the only constraint in on
transmission_base and it rightly is using an index to find records matching
the where clause.

Since ticket_number and latest_transmission_id are found in separate indexes
I do not believe the planner can make use of an Index Only scan to fulfill
the join so each index lookup would require a corresponding heap lookup
which means extra work compared to just sequentially scanning the heap in
the first place.  Since it is going to hit the entire thing in either case
the sequential scan is the logical choice for it to make.

Others will correct any factual mistakes I may have made - I am theorizing
here and do not understand the planner sufficient well to be 100% certain
that an FK definition will solve the problem.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Query-planner-question-tp5815659p5815661.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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: [GENERAL] Linux replication to FreeBSD problem

[Jov]

Yes,it is locale problem.
I do some more testing,and find that in my DB locale which is
zh_CN.UTF-8,the indexes on FreeBSD slave can works if the indexed data is
lower case ascii,it can't find data contain upper case.

Explicit set the column collate to "C" can solve the problem.
I will recreate all the index with collate "C".

Thanks very much!

Jov
blog: http:amutu.com/blog 


2014-08-20 23:36 GMT+08:00 Tom Lane :

> Adrian Klaver  writes:
> > On 08/20/2014 07:53 AM, Jov wrote:
> >> I setup a PG 9.3.5 master on CentOS 6 x86_64,and 2 screaming replicaton
> >> slaves,one on CentOS6 x86_64,the other on FreeBSD 10 amd64.
> >> The replication work fine for a week,But today I find a problem on sql
> >> running on FreeBSD:simple sql use index do not return result.If I
> >> disable the index ,use seqscan,then I can get correct result.
>
> > I would say FreeBSD is not similar enough to Linux(CentOS) to make this
> > work reliably with binary replication.
>
> The most likely theory as to the source of the problem is that the locale
> names used by the CentOS machine are not recognized by the FreeBSD OS,
> and/or imply slightly different sort orderings.  So a text index that's
> correctly sorted according to the CentOS machine is not correctly sorted
> according to FreeBSD, leading to search failures.
>
> You could probably make this case work reliably if you used C locale on
> both systems; the behavior of that is pretty portable.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>