Re: [GENERAL] New wrapper library: QUINCE

[Michael Shepanski]

On 20/08/2014 3:51 AM, John R Pierce wrote:


really well designed method of generating really awful nested
queries?whatever.


Sure, if that's what you're into.  As the doco says:

How far you go in the direction of monster queries is up to you,
the application designer. Quince has no opinion, but whatever you
decide, quince allows you to express your choice in manageable C++.

(quince-lib.com/queries_in_c_expressions/rise_of_the_monster_query.html)

Cheers,
--- Michael



--
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] 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 mmonc...@gmail.com:

 On Tue, Aug 12, 2014 at 5:07 AM, Rémi Cura remi.c...@gmail.com wrote:
 
 
 
  2014-08-11 22:48 GMT+02:00 Kevin Grittner kgri...@ymail.com:
 
  Rémi Cura remi.c...@gmail.com 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 be upserted into table_2)
  CTE_9 (upsert into table_2 using custom upsert_function)
  CTE_10 (end of query)
  each of the upserting function is plpgsql and do something like
  pg_advisory_lock(table_number, id of row to 

[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 lori.corb...@jax.org 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 lori.corb...@jax.org 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_prefixPartI 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 adrian.kla...@aklaver.com 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_prefixPartI 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_prefixPartI 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 lori.corb...@jax.org 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 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 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 adrian.kla...@aklaver.com 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_prefixPartI 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 adrian.kla...@aklaver.com 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 lori.corb...@jax.org 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 r...@iol.ie 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 a...@infer.commailto:a...@infer.com
Date: Tuesday, August 19, 2014 at 3:04 PM
To: Joseph Kregloh jkreg...@sproutloud.commailto:jkreg...@sproutloud.com
Cc: WILLIAM MITCHELL b...@publicrelay.commailto:b...@publicrelay.com, 
pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org 
pgsql-general@postgresql.orgmailto: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 
jkreg...@sproutloud.commailto: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 
b...@publicrelay.commailto: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 a...@infer.commailto: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.orgmailto: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 spam_ea...@gmx.net 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 ljw1...@gmail.com 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 ljw1...@gmail.com 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 raghuchenn...@gmail.com wrote:

 On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell r...@iol.ie 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 http://amutu.com/blog


2014-08-20 23:36 GMT+08:00 Tom Lane t...@sss.pgh.pa.us:

 Adrian Klaver adrian.kla...@aklaver.com 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