Re: [GENERAL] Find similar records (compare tsvectors)

[Patrick Dung]

Thanks.
smlar is fast and quite good.I need find tuning on the search result. 

 On Saturday, March 7, 2015 12:07 AM, Oleg Bartunov  
wrote:
   

 On Fri, Mar 6, 2015 at 5:05 PM, Patrick Dung  wrote:
> Resend.
>
> How to quickly compare the similarity of two tsvector?
>

check http://www.sai.msu.su/~megera/postgres/talks/pgcon-2012.pdf


>
> On Monday, March 2, 2015 11:01 PM, Patrick Dung 
> wrote:
>
>
> Hello,
>
> I had a database with articles or attachment stored in bytea format.
> I also had a trigger: it insert/update the tsv column when a record is
> added/updated.
> The tsv column had a GIN index.
> With this setting, I can do very fast keyword search on the tsv.
>
> Suppose I had a specific record (id=10).
> How to list similar records based on ranking?
> In that case, I had to compare a tsvector with another tsvector.
>
> I had this SQL which make the original tsv as a text and then to tsquery,
> Then I can compare a tsv and a tsquery.
> SELECT ts_rank(i.tsv, replace(strip(original.tsv)::text, ' ', '|')::tsquery)
> as similarity, i.company, i.industry, i.post_timestamp, i.id FROM items i,
> (SELECT tsv, id FROM items WHERE id=10) AS original WHERE i.id !=
> original.id ORDER BY similarity;
>
> items table:
> id bigint
> company varchar
> industry varchar
> description varchar
> post_timestamp timestamp
> attachment bytea
> tsv tsvector
>
> The problem is that this is very slow.
> Any comment?
>
> Thank and regards,
> Patrick
>
>


-- 
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] Find similar records (compare tsvectors)

[Patrick Dung]

Resend.
How to quickly compare the similarity of two tsvector?
 

 On Monday, March 2, 2015 11:01 PM, Patrick Dung  
wrote:
   

 Hello,
I had a database with articles or attachment stored in bytea format.I also had 
a trigger: it insert/update the tsv column when a record is added/updated.The 
tsv column had a GIN index.With this setting, I can do very fast keyword search 
on the tsv.
Suppose I had a specific record (id=10).How to list similar records based 
on ranking?In that case, I had to compare a tsvector with another tsvector.
I had this SQL which make the original tsv as a text and then to tsquery, Then 
I can compare a tsv and a tsquery.
SELECT ts_rank(i.tsv, replace(strip(original.tsv)::text, ' ', '|')::tsquery) as 
similarity, i.company, i.industry, i.post_timestamp, i.id FROM items i, (SELECT 
tsv, id FROM items WHERE id=10) AS original WHERE i.id != original.id ORDER 
BY similarity;
items table:id bigint
company varchar
industry varchardescription varcharpost_timestamp timestampattachment bytea
tsv tsvector

The problem is that this is very slow.Any comment?
Thank and regards,Patrick

[GENERAL] Find similar records (compare tsvectors)

[Patrick Dung]

Hello,
I had a database with articles or attachment stored in bytea format.I also had 
a trigger: it insert/update the tsv column when a record is added/updated.The 
tsv column had a GIN index.With this setting, I can do very fast keyword search 
on the tsv.
Suppose I had a specific record (id=10).How to list similar records based 
on ranking?In that case, I had to compare a tsvector with another tsvector.
I had this SQL which make the original tsv as a text and then to tsquery, Then 
I can compare a tsv and a tsquery.
SELECT ts_rank(i.tsv, replace(strip(original.tsv)::text, ' ', '|')::tsquery) as 
similarity, i.company, i.industry, i.post_timestamp, i.id FROM items i, (SELECT 
tsv, id FROM items WHERE id=10) AS original WHERE i.id != original.id ORDER 
BY similarity;
items table:id bigint
company varchar
industry varchardescription varcharpost_timestamp timestampattachment bytea
tsv tsvector

The problem is that this is very slow.Any comment?
Thank and regards,Patrick

Re: [GENERAL] Question about gin index not used on a tsv column

[Patrick Dung]

Hello all,

I have encounter problems:
1)While testing, I found that in a particular case, LIMIT 1 is slower (gin 
index is not used).

2) It is about constraint exclusion.

The query is ordered by (DESC) post_timestamp.
In human way of thinking, it only need to search from the 2014 partition table 
and only need to provide one record. The explain statement show that the 2013 
partition table is also scanned.


So for PostgreSQL in the current version(s), the query has to match the 
constraint of the partition in order to take advantage of the constraint 
exclusion, right?



jobs=> explain analyze SELECT * FROM jobs WHERE tsv @@ to_tsquery('english', 
'BARCODE') ORDER BY post_timestamp DESC;
QUERY PLAN
---
 Sort  (cost=5165.49..5170.49 rows=2002 width=397) (actual time=3.067..3.101 
rows=321 loops=1)
   Sort Key: jobs.post_timestamp
   Sort Method: quicksort  Memory: 202kB
   ->  Append  (cost=0.00..5055.70 rows=2002 width=397) (actual
 time=2.090..2.849 rows=321 loops=1)
 ->  Seq Scan on jobs  (cost=0.00..0.00 rows=1 width=390) (actual 
time=0.001..0.001 rows=0 loops=1)
   Filter: (tsv @@ '''barcod'''::tsquery)
 ->  Bitmap Heap Scan on jobs_2014p  (cost=1337.46..3986.36 rows=1479 
width=384) (actual time=2.088..2.434 rows=218 loops=1)
   Recheck Cond: (tsv @@ '''barcod'''::tsquery)
   ->  Bitmap Index Scan on jobs_2014p_tsv_gin_idx  
(cost=0.00..1337.09 rows=1479 width=0) (actual time=2.053..2.053 rows=218
 loops=1)
 Index Cond: (tsv @@ '''barcod'''::tsquery)
 ->  Bitmap Heap Scan on jobs_2013p  (cost=126.05..1069.34 rows=522 
width=432) (actual time=0.236..0.377 rows=103 loops=1)
   Recheck Cond: (tsv @@ '''barcod'''::tsquery)
   ->  Bitmap Index Scan on jobs_2013p_tsv_gin_idx  
(cost=0.00..125.92 rows=522 width=0) (actual time=0.220..0.220 rows=103 loops=1)
 Index Cond: (tsv @@
 '''barcod'''::tsquery)
 Total runtime: 3.173 ms
(15 rows)

jobs=> explain analyze SELECT * FROM jobs WHERE tsv @@ to_tsquery('english', 
'BARCODE') ORDER BY post_timestamp DESC LIMIT 1;
   
QUERY PLAN


-
 Limit  (cost=0.99..55.43 rows=1 width=397) (actual time=19.992..19.993 rows=1 
loops=1)
   ->  Merge Append  (cost=0.99..109001.45 rows=2002 width=397) (actual 
time=19.991..19.991 rows=1 loops=1)
 Sort Key: jobs.post_timestamp
 ->  Index Scan Backward using jobs_post_timestamp_idx on jobs  
(cost=0.12..4.23 rows=1 width=390) (actual time=0.004..0.004 rows=0 loops=1)
   Filter: (tsv @@ '''barcod'''::tsquery)
 ->  Index Scan Backward using jobs_2014p_post_timestamp_idx on 
jobs_2014p  (cost=0.42..79205.48 rows=1479 width=384) (actual time=6.845..6.845 
rows=1 loops=1)
   Filter: (tsv @@ '''barcod'''::tsquery)
   Rows Removed by Filter: 718
 ->  Index Scan Backward using jobs_2013p_post_timestamp_idx on 
jobs_2013p  (cost=0.42..29754.98 rows=522 width=432) (actual 
time=13.138..13.138 rows=1 loops=1)
   Filter: (tsv @@ '''barcod'''::tsquery)
   Rows Removed by Filter: 1664
 Total runtime: 20.051 ms
(12 rows)


Thanks and regards,
Patrick



On Wednesday, September 3, 2014 11:03 AM, Patrick Dung 
 wrote:
 


Thanks for reply, David.

I have searched internet and changed one parameter cpu_tuple_cost from 0.01 to 
0.08. I would see if it helped.
I found the problem occurred randomly.


For tsv, I thought if there is an index already built, postgresql should try to 
make use of it because I think for most of the time it would be faster than 
full table scan.

Thanks and regards,
Patrick



On Wednesday, September 3, 2014 3:00 AM, David G Johnston 
 wrote:
 


Patrick Dung-2 wrote
> Hello Postgresql users,
> 
> In my setting, I found that sometimes the query does not use the gin index
> built for a tsv column.
> 
> Attached file provide more info (with explain analyze).

So the difference between the first and third queries shown is the fact that
the data is now in-memory when the third query is run; in both plans only
sequential scans are used.

The difference between the first and second queries, where the index is
used, is therefore at

Re: [GENERAL] Question about gin index not used on a tsv column

[Patrick Dung]

Thanks for reply, David.

I have searched internet and changed one parameter cpu_tuple_cost from 0.01 to 
0.08. I would see if it helped.
I found the problem occurred randomly.


For tsv, I thought if there is an index already built, postgresql should try to 
make use of it because I think for most of the time it would be faster than 
full table scan.

Thanks and regards,
Patrick



On Wednesday, September 3, 2014 3:00 AM, David G Johnston 
 wrote:
 


Patrick Dung-2 wrote
> Hello Postgresql users,
> 
> In my setting, I found that sometimes the query does not use the gin index
> built for a tsv column.
> 
> Attached file provide more info (with explain analyze).

So the difference between the first and third queries shown is the fact that
the data is now in-memory when the third query is run; in both plans only
sequential scans are used.

The difference between the first and second queries, where the index is
used, is therefore at least partially - if not wholly - due to those same
caching effects.

On a reasonably small table it is not that unreasonable for the planner to
choose a single retrieve-and-filter sequential scan as opposed to a
piecemeal lookup-and-retrieve index scan.  

Your example is not controlled enough to prove that the planner has made an
incorrect decision.  The estimated costs of 34.5k vs 35.3k are extremely
small and since the 34.5k is the result of changing random_page_cost you
cannot directly compare them anyway.

You need to use "enable_seqscan" and related configuration parameters so
that you can force the planner to choose different plans without changing
the underlying costs.

http://www.postgresql.org/docs/9.3/static/runtime-config-query.html

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Question-about-gin-index-not-used-on-a-tsv-column-tp5817433p5817438.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

[GENERAL] Question about gin index not used on a tsv column

[Patrick Dung]

Hello Postgresql users,

In my setting, I found that sometimes the query does not use the gin index 
built for a tsv column.

Attached file provide more info (with explain analyze).

Thanks and regards,
Patrick
jobs=> explain analyze SELECT company,industry FROM jobs WHERE tsv @@ 
to_tsquery('english', 'travel');
QUERY PLAN
---
 Append  (cost=0.00..35337.69 rows=61625 width=67) (actual 
time=0.122..59909.736 rows=61114 loops=1)
   ->  Seq Scan on jobs  (cost=0.00..0.00 rows=1 width=64) (actual 
time=0.001..0.001 rows=0 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
   ->  Seq Scan on jobs_2014p  (cost=0.00..25629.21 rows=46735 width=64) 
(actual time=0.121..47191.053 rows=46142 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
 Rows Removed by Filter: 243194
   ->  Seq Scan on jobs_2013p  (cost=0.00..9708.48 rows=14889 width=75) (actual 
time=5.325..12708.878 rows=14972 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
 Rows Removed by Filter: 86893
 Total runtime: 59917.092 ms
(10 rows)

jobs=> set random_page_cost = 2;
SET
jobs=> explain analyze SELECT company,industry FROM jobs WHERE tsv @@ 
to_tsquery('english', 'travel');
 QUERY PLAN

 Append  (cost=0.00..34676.86 rows=61625 width=67) (actual 
time=280.185..2151.618 rows=61117 loops=1)
   ->  Seq Scan on jobs  (cost=0.00..0.00 rows=1 width=64) (actual 
time=0.001..0.001 rows=0 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
   ->  Bitmap Heap Scan on jobs_2014p  (cost=2352.20..24968.38 rows=46735 
width=64) (actual time=280.184..1784.566 rows=46143 loops=1)
 Recheck Cond: (tsv @@ '''travel'''::tsquery)
 ->  Bitmap Index Scan on jobs_2014p_tsv_gin_idx  (cost=0.00..2340.51 
rows=46735 width=0) (actual time=277.210..277.210 rows=46143 loops=1)
   Index Cond: (tsv @@ '''travel'''::tsquery)
   ->  Seq Scan on jobs_2013p  (cost=0.00..9708.48 rows=14889 width=75) (actual 
time=0.069..361.839 rows=14974 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
 Rows Removed by Filter: 86908
 Total runtime: 2154.907 ms
(11 rows)

jobs=> set random_page_cost = 4;
SET
jobs=> explain analyze SELECT company,industry FROM jobs WHERE tsv @@ 
to_tsquery('english', 'travel');
QUERY PLAN
--
 Append  (cost=0.00..35337.69 rows=61625 width=67) (actual time=0.229..3462.236 
rows=61117 loops=1)
   ->  Seq Scan on jobs  (cost=0.00..0.00 rows=1 width=64) (actual 
time=0.001..0.001 rows=0 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
   ->  Seq Scan on jobs_2014p  (cost=0.00..25629.21 rows=46735 width=64) 
(actual time=0.228..2898.271 rows=46143 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
 Rows Removed by Filter: 243213
   ->  Seq Scan on jobs_2013p  (cost=0.00..9708.48 rows=14889 width=75) (actual 
time=44.810..556.103 rows=14974 loops=1)
 Filter: (tsv @@ '''travel'''::tsquery)
 Rows Removed by Filter: 86912
 Total runtime: 3468.134 ms
(10 rows)

-- 
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] How to make use of partitioned table for faster query?

[Patrick Dung]

Thanks for reply.

The constraint is like:


  ADD CONSTRAINT attandence_2014p_record_timestamp_check CHECK 
(record_timestamp >= '2014-01-01 00:00:00'::timestamp without time zone AND 
record_timestamp < '2015-01-01 00:00:00'::timestamp without time zone);

Let us assume it is a complete year (Jan-Dec) instead of school year.
I thought the data in table partition 2014 can check with the table partition 
2014. It do not need to check with other partitions. Same for other partitions.



On Saturday, August 30, 2014 12:52 PM, John R Pierce  
wrote:
 


On 8/29/2014 9:38 PM, Patrick Dung wrote:

Suppose the table 'attendance' is very large:
>id bigint
>student_name varchar
>
late boolean
>
>record_timestamp timestamp
>
>
>
>The table is already partitioned by year (attendance_2012p, attendance_2013p, 
>...).
>I would like to count the number of lates by year.
>
>
>Instead of specifying the partition tables name:
>select count(*) from attendance_2012p where student_name="Student A" and 
>late='true';
select count(*) from attendance_2013p where student_name="Student A" and 
late='true';
>select count(*) from attendance_2014p where student_name="Student
  A" and late='true';
>...
>
>
>Is it possible to query the master table attendance), and the query could make 
>use of the partitioned table for faster query?

select student_name as student,extract(year from record_timestamp)
as year, count(*) as count_lates from attendance where late group by
1,2;

now, if your partitioning is by school year, that will be somewhat
trickier.   what are your partitioning expression ?

as far as faster, well, your query has to read from all of the
tables.   there won't be any speedup from partition pruning...






-- 
john r pierce  37N 122W
somewhere on the middle of the left coast

[GENERAL] How to make use of partitioned table for faster query?

[Patrick Dung]

Hello Postgresql users,

Suppose the table 'attendance' is very large:
id bigint
student_name varchar
late boolean

record_timestamp timestamp


The table is already partitioned by year (attendance_2012p, attendance_2013p, 
...).
I would like to count the number of lates by year.

Instead of specifying the partition tables name:
select count(*) from attendance_2012p where student_name="Student A" and 
late='true';select count(*) from attendance_2013p where student_name="Student 
A" and late='true';
select count(*) from attendance_2014p where student_name="Student A" and 
late='true';
...


Is it possible to query the master table attendance), and the query could make 
use of the partitioned table for faster query?

Thanks and regards,
Patrick

Re: [GENERAL] Is there a function to save schema history internally?

[Patrick Dung]

Hi Adrian,

Thanks for the info.

Thanks and regards,
Patrick



On Saturday, August 30, 2014 5:28 AM, Adrian Klaver  
wrote:
 


On 08/29/2014 11:23 AM, Patrick Dung wrote:
> Hello Postgresql users,
>
> Is there a function to save schema history internally?
> By keeping the schema history inside the DB, we can keep track of what
> and when is changed in the schema.
>
> While searching google. It seems it is a limitation with the audit trigger:
> https://wiki.postgresql.org/wiki/Audit_trigger#Limitation:_Cannot_audit_DDL

Well going forward, 9.4+,  there are EVENT TRIGGERS

http://www.postgresql.org/docs/9.4/static/event-triggers.html

For a recent thread on what is possible or not see:

http://www.postgresql.org/message-id/20ee50f73664e744af948f0106fe6dfa585a7...@seambx01.sea.corp.int.untd.com



>
> Thanks and regards,
> Patrick


-- 
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

[GENERAL] Is there a function to save schema history internally?

[Patrick Dung]

Hello Postgresql users,

Is there a function to save schema history internally?
By keeping the schema history inside the DB, we can keep track of what and when 
is changed in the schema.

While searching google. It seems it is a limitation with the audit trigger:

https://wiki.postgresql.org/wiki/Audit_trigger#Limitation:_Cannot_audit_DDL


Thanks and regards,
Patrick

[GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

[Patrick Dung]

Hi Postgresql users,

I have a master table with two partition table (food_2013p, food_2014p).

I found that when I use SELECT + 'now' constant, constraint exclusion works, 
(it skipped the 2013 partition).

EXPLAIN ANALYZE
 SELECT *
   FROM food
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND 
food.post_timestamp <= 'now'
  ORDER BY food.post_timestamp  DESC
 LIMIT 30;

But when I put the query inside view, 'now' is converted to the timestamp when 
I run the create view.
So the view becomes:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date 
AND food.post_timestamp <= '2014-08-21 13:38:29.642347'::timestamp without time 
zone


This is not dynamic.
When I use now(), the query will scan other partition tables. I know this is a 
restriction of partition on non-immutable function.

Would it be possible or a feature request to take advantage of the partition 
table with query like this?


Thanks and regards,
Patrick

Re: [GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

[Patrick Dung]

Hi Ken,

Thanks for reply.

1.

The problem is that using 'now' in VIEW, the resulting VIEW will hard code the 
current timestamp.
It is not dynamic.
If I use write the view like this:
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::timestamp 
without time zone
 AND food.post_timestamp <= 'now'::timestamp without time zone

The VIEW will be created like this:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval) AND 
food.post_timestamp <= '2014-08-21 17:32:21.787179'::timestamp without time zone



2.

now() is dynamic but it scan all the partitioned tables.

Thanks and regards,
Patrick



On Thursday, August 21, 2014 4:27 PM, Ken Tanzer  wrote:
 




EXPLAIN ANALYZE
> SELECT *
>   FROM food
> WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND 
>food.post_timestamp <= 'now'
>  ORDER BY food.post_timestamp  DESC
> LIMIT 30;

I think the problem is that you're using 'now'::date in your first example, 
which gets frozen.  You can use now() or current_timestamp or current_date to 
get dynamic results.

CREATE  VIEW test_now AS SELECT current_timestamp as current_ts, now() as 
now_function,'now'::timestamp AS now_literal;

(wait a couple of seconds)

SELECT * FROM test_now;

          current_ts           |         now_function          |        
now_literal         
---+---+
 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:25:54.147004-07 | 2014-08-21 
01:18:22.207073
(1 row)

You'll see that the last column is frozen while the other two stay current.

Cheers,
Ken



-- 

AGENCY Software  

A Free Software data system
By and for non-profits
http://agency-software.org/
https://agency-software.org/demo/client

ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

[Patrick Dung]

Hi all,

I got a typo in last mail, below is the updated one, with changes in bold fonts.

The method is inspired by Date LastDay - PostgreSQL wiki

  
          
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL 
Depends on Nothing by Scott Bailey 'Artacus'   
View on wiki.postgresql.org Preview by Yahoo  
  

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

2. 

CREATE VIEW v_recent_food AS
 SELECT *
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 
mon'::interval)::date
  AND food.post_timestamp <= now_function_ts_notz()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;


While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,Patrick




On Thursday, August 21, 2014 4:01 PM, Patrick Dung  
wrote:
 


An update, inspired by Date LastDay - PostgreSQL wiki

  
          
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL 
Depends on Nothing by Scott Bailey 'Artacus'   
View on wiki.postgresql.org Preview by Yahoo  
  

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;


2. Create my View like this:
CREATE OR REPLACE VIEW v_recent_items AS 
 SELECT * 
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 
mon'::interval)::timestamp without time zone AND food.post_timestamp <= 
now()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;

While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,
Patrick



On Thursday, August 21, 2014 3:21 PM, Patrick Dung  
wrote:
 


Resent. As I could not see my mail in the mailing list after about two hours.



On Thursday, August 21, 2014 1:43 PM, Patrick Dung  
wrote:
 


Hi Postgresql users,

I have a master table with two partition table (food_2013p, food_2014p).

I found that when I use SELECT + 'now' constant, constraint exclusion works, 
(it skipped the 2013 partition).

EXPLAIN ANALYZE
 SELECT *
   FROM food
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND 
food.post_timestamp <= 'now'
  ORDER BY food.post_timestamp  DESC
 LIMIT 30;

But when I put the query inside view, 'now' is converted to the timestamp when 
I run the create view.
So the view becomes:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date 
AND food.post_timestamp <= '2014-08-21 13:38:29.642347'::timestamp without time 
zone


This is not dynamic.
When I use now(), the query will scan other partition tables. I know this is a 
restriction of partition on non-immutable function.

Would it be possible or a feature request to take advantage of the partition 
table with query like this?


Thanks and regards,
Patrick

Re: [GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

[Patrick Dung]

An update, inspired by Date LastDay - PostgreSQL wiki

  
          
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL 
Depends on Nothing by Scott Bailey 'Artacus'   
View on wiki.postgresql.org Preview by Yahoo  
  

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;


2. Create my View like this:
CREATE OR REPLACE VIEW v_recent_items AS 
 SELECT * 
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 
mon'::interval)::timestamp without time zone AND food.post_timestamp <= 
now()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;

While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,
Patrick



On Thursday, August 21, 2014 3:21 PM, Patrick Dung  
wrote:
 


Resent. As I could not see my mail in the mailing list after about two hours.



On Thursday, August 21, 2014 1:43 PM, Patrick Dung  
wrote:
 


Hi Postgresql users,

I have a master table with two partition table (food_2013p, food_2014p).

I found that when I use SELECT + 'now' constant, constraint exclusion works, 
(it skipped the 2013 partition).

EXPLAIN ANALYZE
 SELECT *
   FROM food
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND 
food.post_timestamp <= 'now'
  ORDER BY food.post_timestamp  DESC
 LIMIT 30;

But when I put the query inside view, 'now' is converted to the timestamp when 
I run the create view.
So the view becomes:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date 
AND food.post_timestamp <= '2014-08-21 13:38:29.642347'::timestamp without time 
zone


This is not dynamic.
When I use now(), the query will scan other partition tables. I know this is a 
restriction of partition on non-immutable function.

Would it be possible or a feature request to take advantage of the partition 
table with query like this?


Thanks and regards,
Patrick

Re: [GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

[Patrick Dung]

Resent. As I could not see my mail in the mailing list after about two hours.



On Thursday, August 21, 2014 1:43 PM, Patrick Dung  
wrote:
 


Hi Postgresql users,

I have a master table with two partition table (food_2013p, food_2014p).

I found that when I use SELECT + 'now' constant, constraint exclusion works, 
(it skipped the 2013 partition).

EXPLAIN ANALYZE
 SELECT *
   FROM food
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND 
food.post_timestamp <= 'now'
  ORDER BY food.post_timestamp  DESC
 LIMIT 30;

But when I put the query inside view, 'now' is converted to the timestamp when 
I run the create view.
So the view becomes:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date 
AND food.post_timestamp <= '2014-08-21 13:38:29.642347'::timestamp without time 
zone


This is not dynamic.
When I use now(), the query will scan other partition tables. I know this is a 
restriction of partition on non-immutable function.

Would it be possible or a feature request to take advantage of the partition 
table with query like this?


Thanks and regards,
Patrick

Re: [GENERAL] PostgreSQL on AIX platform

[Patrick Dung]

Hi Payal,

I haven't tried Postgresql on AIX.
This web site provides binary and if you like to build yourself, it provided 
build instruction too.
http://www.perzl.org/aix/
http://www.perzl.org/aix/index.php?n=Main.Instructions

Thanks and regards,
Patrick



On Saturday, August 9, 2014 6:28 AM, Payal Shah  wrote:
 


 
Hello,
 
Can you please confirm if PostgreSQL 9.2.4 is supported on AIX 7 platform? In 
the following URL, it mentions support for AIX 6 (but not AIX 7) - 
http://www.postgresql.org/docs/9.2/static/installation-platform-notes.html
 
As I understand from a different topic on this forum is that, we would need to 
download source code and compile on AIX using IBM XL C compiler. Can someone 
provide steps on how to do that?
 
Thanks,
Payal Shah
This email and any files transmitted with it are confidential, proprietary and 
intended solely for the individual or entity to whom they are addressed. If you 
have received this email in error please delete it immediately.

Re: [GENERAL] Trigger function cannot reference field name with capital letter

[Patrick Dung]

Thanks all for the help.

BTW, letter casing is just a preference.
Some people liked to use all small caps, some liked to use all big caps.
I sometimes found that mixed case is more meaningful for the filed (eg. 
serialnumber vs serialNumber)

What is your preference or suggestion?



On Thursday, August 14, 2014 12:18 PM, John R Pierce  
wrote:
 


On 8/13/2014 9:13 PM, John R Pierce wrote:
>
> SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01 
> 00:00:00'

oops.

SELECT * from tbl1 where new."postTimestamp" > timestamp '2014-01-01 
00:00:00'

I meant.  I should proof what I write, hah!



-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast



-- 
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 function cannot reference field name with capital letter

[Patrick Dung]

Hello PGSQL users,


I have a field called postTimestamp.
The trigger function could not reference it.
When I change my field to post_timestamp. I can reference it from the tigger 
function.
Version is 9.3.5. Any comment?

< 2014-08-14 00:23:32.717 HKT >ERROR:  post "new" has no field "posttimestamp"
< 2014-08-14 00:23:32.717 HKT >CONTEXT:  SQL statement "SELECT * from tbl1
    where NEW.posttimestamp > "2014-01-01 00:00:00" )"
    PL/pgSQL function test_trigger() line 9 at assignment
< 2014-08-14 00:23:32.717 HKT >STATEMENT:  INSERT INTO public.tbl1("vendor", 
url, "postTimestamp", product, "type", "itemID") VALUES ('vendor1'::text, 
'http://example.org'::text, '2014-01-01 01:01:01'::timestamp without time zone, 
'product1'::text, 'food'::text, '1'::bigint)
< 2014-08-14 00:32:39.708 HKT >ERROR:  syntax error at or near "SELECT" at 
character 314


Thanks and regards,
Patrick

Re: [GENERAL] Curious question about physical files to store database

[Patrick Dung]

On Monday, November 4, 2013 10:09 PM, Albe Laurenz  
wrote:
 
Patrick Dung wrote:

> As I have seen, some database created or pre-allocate large physical files on 
> the file system to as
> the backend of the database tablespace.
> 
> For Postgresql, I have observed that it created several files in the base and 
> global directory.
> 
> It may be by design, what is the pros and cons of this behavior?

You are a bit unspecific; are you talking about Oracle?

The disk layout is of course by design.

Oracle uses large container files and keeps its data in those.
As far as I know, this is to bypass file system functionality.
Oracle usually recommends direct I/O and bypasses file system
functionality (caching etc.) as much as possible.

I guess one reason for this is that, historically, file system
implementations incurred more overhead than they do now and had
all sorts of other problems with larger amounts of data.
These days, filesystems perform much better, so this is no longer
necessary, but Oracle is quite old software.

Another reason may be Oracle's quest to rule the world, and the
storage layer is part of that.  Lately, Oracle tries to get everybody
to use ASM, their storage layer, which completely bypasses
file system functionality.

PostgreSQL, on the other hand, does not have the resources or
intentions to write a better file system and actually uses
file system capabilities like caching to improve performance.

PostgreSQL keeps what Oracle calls segments as individual files
in a directory structure.

Yours,
Laurenz Albe


--

I have seen some databases product that allocate small number of large files.

Please correct me if I am wrong:

MSSQL (one file is the data and another file for the transaction log)
MySQL with InnoDB
Oracle
DB2

Thanks and regards,
Patrick

[GENERAL] Curious question about physical files to store database

[Patrick Dung]

As I have seen, some database created or pre-allocate large physical files on 
the file system to as the backend of the database tablespace.

For Postgresql, I have observed that it created several files in the base and 
global directory.

It may be by design, what is the pros and cons of this behavior?

Thanks and regards,
Patrick

Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

[Patrick Dung]

>
>On Friday, October 25, 2013 3:12 PM, Achilleas Mantzios 
> wrote:
>On 23/10/2013 16:44, Tom Lane wrote:
>> Patrick Dung  writes:
>>> By default, FreeBSD ports does not build postgresql with icu 
>>> (http://www.icu-project.org/).
>> Postgres does not have any option to use ICU, default or otherwise.
>> Nor is it likely to happen in future, judging from previous discussions
>> of the idea.
>
>Hi Tom, Patrick
>FreeBSD indeed has a config option to build with ICU, just
># /usr/ports/databases/postgresql93-server
># make config
>and you will be able to see this.
>The relevant README is here : 
>http://people.freebsd.org/~girgen/postgresql-icu/README.html
>Patrick also you may build postgresql by hand and apply the patch manually 
>from : /usr/ports/databases/postgresql93-server
>Although being in a non-english speaking company, i have not tried this 
>neither at work or at home.
>Hope that helps.
>

Hi Achilleas,

Sorry I have hit to send button too fast in the last mail...

Yes, I know FreeBSD has a specific patch to use ICU on Postgresql.
And officially Postgresql, do not come with ICU patch natively.

Thanks.

Patrick

Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

[Patrick Dung]

On Wednesday, October 23, 2013 10:00 PM, Patrick Dung 
 wrote:
 
> On Wednesday, October 23, 2013 9:45 PM, Tom Lane  wrote:
> Patrick Dung  writes:
> 
>  By default, FreeBSD ports does not build postgresql with icu 
>(http://www.icu-project.org/
> ).
> 
> Postgres does not have any option to use ICU, default or otherwise.
> Nor is it likely to happen in future, judging from previous discussions
> of the idea.
> 
> regards, tom lane
> 

OK, now I understand that FreeBSD case, they have a specific patch to use icu.
The default PostgreSQL does use ICU.
Thanks for pointing that out.

Thanks,

Patrick

Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

[Patrick Dung]

> On Wednesday, October 23, 2013 9:45 PM, Tom Lane  wrote:
> Patrick Dung  writes:
> 
>  By default, FreeBSD ports does not build postgresql with icu 
>(http://www.icu-project.org/
> ).
> 
> Postgres does not have any option to use ICU, default or otherwise.
> Nor is it likely to happen in future, judging from previous discussions
> of the idea.
> 
> regards, tom lane
> 

OK, now I understand that FreeBSD case, they have a specific patch to use icu.
The default PostgreSQL does use ICU.
Thanks for pointing that out.

Thanks,
Patrick

[GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu

[Patrick Dung]

Hi all,

By default, FreeBSD ports does not build postgresql with icu 
(http://www.icu-project.org/).


Some questions:

1) It is necessary that icu should be used on FreeBSD?
I have heard that FreeBSD's locale (glibc) has sorting problem with Postgresql 
UTF8 DB.
reference: http://wiki.postgresql.org/wiki/Todo:ICU


2) After searching in google, it was said that using ICU is faster (in terms of 
sorting in locale).

3) Any other pros and cons?

Thanks,
Patrick

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

Noted and thanks.

I can see that it should be updated in the devel manual page:
http://www.postgresql.org/docs/devel/static/pgupgrade.html


Thanks,
Patrick




 From: Bruce Momjian 
To: Patrick Dung  
Cc: Stephen Frost ; "pgsql-general@postgresql.org" 
; Ivan Voras ; Tom Lane 
 
Sent: Saturday, October 5, 2013 10:19 PM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

On Sat, Sep 14, 2013 at 01:54:40AM +0800, Patrick Dung wrote:

> 1. In the past, I have an impression that it requires double of the database
> size.
> Because the manual present in a way that it 'must' need to hold the old and 
> new
> database cluster.
> But it does not mention the benefit of using hard links to save disk space and
> speed.
> I think the documentation could put a note at the beginning for new users.
> 
> 2. Also I think the documentation should provide more info for users that use
> packages.
> Most likely the system would do dependency checking and may refuse two install
> two versions at the same time.
> So uses need to install the new version in another location.
> More documentation should be provided for this part (e.g for users using Linux
> rpm/deb or FreeBSD ports).
> 
> 3. But the way, if users is using Windows, is the link option still works?

I have applied the attached documentation addition to mention that link
mode uses less disk space, and that junction points are used on Windows.
Backpatched to 9.3.

-- 
  Bruce Momjian          http://momjian.us
  EnterpriseDB                            http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: [GENERAL] the new checksum feature

[Patrick Dung]

Hi Torsten,

According to Postgresql 9.3 Wiki:
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Data_Checksums

The checksum feature should be enabled during initdb.


Regards,
Patrick Dung




 From: Torsten Förtsch 
To: pgsql-general@postgresql.org 
Sent: Thursday, September 19, 2013 5:32 PM
Subject: [GENERAL] the new checksum feature
 

Hi,

is it possible to turn on checksums in an existing database? Or do I
have to dump/initdb/restore?

Thanks,
Torsten


-- 
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] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

>

>Symbolic links being used.
>It also creates batch file that could be used to delete old cluster after 
>upgrade.
>It's all in the docs.
>
>Regards,
>Igor Neyman

I see, it should be this step:

Delete old cluster

Once you are satisfied with the upgrade, you can delete the old cluster's data 
directories by running the script mentioned when pg_upgrade completes. You can 
also delete the old installation directories (e.g. bin, share).

Thanks,
Patrick Dung

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

Oh sorry, it is typo. It should be Igor Neyman.
It was 3AM in my timezone and I was sleepy.




 From: Ivan Voras 
To: Patrick Dung  
Cc: Stephen Frost ; "pgsql-general@postgresql.org" 
; Tom Lane  
Sent: Saturday, September 14, 2013 4:08 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

On 13 September 2013 21:44, Patrick Dung  wrote:

> Ivan Voras has replied that the link method work fine in Windows on another
> thread.

That would be very surprising since I don't run Windows servers :)

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

 From: Igor Neyman 
To: Stephen Frost ; Patrick Dung  
Cc: "pgsql-general@postgresql.org" ; Ivan Voras 
; Tom Lane  
Sent: Saturday, September 14, 2013 2:14 AM
Subject: RE: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

Hi Igor,

>> -Original Message-
>> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
>> ow...@postgresql.org] On Behalf Of Stephen Frost
>> Sent: Friday, September 13, 2013 2:06 PM
>> To: Patrick Dung
>> Cc: pgsql-general@postgresql.org; Ivan Voras; Tom Lane
>> Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
>>
>>
>> > 3. But the way, if users is using Windows, is the link option still works?
>>
>> Don't think so, but not sure.  pg_upgrade could be made to work in a truely
>> "in-place" method if there's demand for it and someone wants to work on it.
>> It'd clearly be a bit more *dangerous*, of course..
>>
>> Thanks,
>>
>> Stephen
>
>Like I said in the other message, actually in-place upgrade using symbolic 
>links work quite fine under Windows.
>I tested it carefully many times, and used it even more upgrading production 
>systems.
>I don't feel it's *dangerous*, especially considering that my whole upgrade 
>process always starts with backing up existing cluster.
>

For Windows, is it using symbolic links or hard links for the upgrade?
If symbolic links is used, would users have difficultly when deleting the old 
cluster?

Thanks,
Patrick

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

 From: Stephen Frost 
To: Patrick Dung  
Cc: "pgsql-general@postgresql.org" ; Ivan Voras 
; Tom Lane  
Sent: Saturday, September 14, 2013 2:05 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

Hi Stephen,

>Patrick,
>
>* Patrick Dung (patrick_...@yahoo.com.hk) wrote:
>> I think the documentation could put a note at the beginning for new users.
>
>Yes, probably true.  Feel free to propose specific improvements.

As pg_upgrade already has a fast and disk saving method (using link) for major 
upgrade, I think it deserve to be mention the pros and cons (if any) in the 
documentation. I think others users on the list had impression (look at the 
previous mail on this thread) that pg_upgrade 'must' require double storage 
space for major upgrade.

>
>> 2. Also I think the documentation should provide more info for users that 
>> use packages.
>> Most likely the system would do dependency checking and may refuse two 
>> install two versions at the same time.
>> So uses need to install the new version in another location.
>> More documentation should be provided for this part (e.g for users using 
>> Linux rpm/deb or FreeBSD ports).
>
>This should really be up to the packaging systems to handle as it
>depends on which OS and which packages you're using..
>

For FreeBSD, this was discussed on the mailing list one year ago.
http://lists.freebsd.org/pipermail/freebsd-ports/2012-September/078543.html

>> 3. But the way, if users is using Windows, is the link option still works?
>
>Don't think so, but not sure.  pg_upgrade could be made to work in a
>truely "in-place" method if there's demand for it and someone wants to
>work on it.  It'd clearly be a bit more *dangerous*, of course..
>
>    Thanks,
>
>    Stephen

Ivan Voras has replied that the link method work fine in Windows on another 
thread.

Thanks,
Patrick

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

From: Stephen Frost 
To: Patrick Dung  
Cc: "pgsql-general@postgresql.org" ; Ivan Voras 
; Tom Lane ; Stephen Frost 
 
Sent: Saturday, September 14, 2013 1:13 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 


On Friday, September 13, 2013, Patrick Dung  wrote:
>What?  That's absolutely *not* required for pg_upgrade to work.  In
>>general, I would recommend that you make a copy of the database, but
>>it's certainly not required.
>
>I mean the old version and new version would need to take up disk space on the 
>server.
>Thus roughly doubled the disk space used.
>

>And I'm telling you that pg_upgrade does NOT require that. It has a mode which 
>allows an in-place upgrade (using hard links) that only >requires a bit of 
>extra disk space- certainly no where near double on a database of any size. 

Thanks to Stephen for pointing out using link with pg_upgrade.

I read the pg_upgrade section again: 
http://www.postgresql.org/docs/9.3/static/pgupgrade.html

1. In the past, I have an impression that it requires double of the database 
size.
Because the manual present in a way that it 'must' need to hold the old and new 
database cluster.
But it does not mention the benefit of using hard links to save disk space and 
speed.
I think the documentation could put a note at the beginning for new users.

2. Also I think the documentation should provide more info for users that use 
packages.
Most likely the system would do dependency checking and may refuse two install 
two versions at the same time.
So uses need to install the new version in another location.
More documentation should be provided for this part (e.g for users using Linux 
rpm/deb or FreeBSD ports).

3. But the way, if users is using Windows, is the link option still works?

Thanks,
Patrick

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

 From: Thomas Kellerer 
To: pgsql-general@postgresql.org 
Sent: Saturday, September 14, 2013 12:27 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

Patrick Dung wrote on 13.09.2013 18:17:
>> The problem of pg_upgrade is that it needed to hold two set of databases 
>> data in the server.
>> This is not be desirable (very slow) or possible (space limitation) for 
>> database with huge data.
>>
>> For example, if the old version is already using over 50% of the mount point.
>> The new database may not have enough disk space for the upgrading.

> I think if you use the --link parameter, you don't need additional disk space 
> (or only little).

Thanks for pointing out.

For small or medium sized database, I think file based snapshot (like ZFS) 
could create backup of the old database quickly.
Also it can rollback quickly.

Thanks,
Patrick

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

 From: Stephen Frost 
To: Patrick Dung  
Cc: "pgsql-general@postgresql.org" ; Ivan Voras 
; Tom Lane  
Sent: Saturday, September 14, 2013 12:43 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

* Patrick Dung (patrick_...@yahoo.com.hk) wrote:
>> The problem of pg_upgrade is that it needed to hold two set of databases 
>> data in the server.

>What?  That's absolutely *not* required for pg_upgrade to work.  In
>general, I would recommend that you make a copy of the database, but
>it's certainly not required.

I mean the old version and new version would need to take up disk space on the 
server.
Thus roughly doubled the disk space used.

Thanks,
Patrick

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

 From: Tom Lane 
To: Stephen Frost  
Cc: Ivan Voras ; pgsql-general@postgresql.org 
Sent: Friday, September 13, 2013 9:58 PM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

>> * Ivan Voras (ivo...@freebsd.org) wrote:
>>> If I read the documentation correctly
>>> (http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs
>>> oldbindir and newbindir arguments pointing to the directories of
>>> PostgreSQL executables for the old and new versions, making it basically
>>> unusable for upgrading systems which are maintained with packages
>>> instead of individually compiling & installing custom versions of
>>> PostgreSQL, right? (except possibly Debian which may allow multiple pg
>>> versions to be installed, I haven't tried it).
>
>> Uhm, don't basically all Debian-based and RedHat-based distributions
>> support having multiple major versions installed concurrently?  It's a
>> pretty reasonable thing to need and, imv anyway, all packaging of PG
>> should support it.
>
>In Red Hat's own packaging, you should temporarily install the
>postgresql-upgrade RPM, which contains pg_upgrade as well as a copy
>of the previous-generation postmaster.  If you use Devrim's packages,
>I think he more nearly follows the Debian approach.  Either way, if
>a packager has failed to allow pg_upgrade to be usable within his
>package set(s), it's a packaging error that you should complain
>about.
>
>

The problem of pg_upgrade is that it needed to hold two set of databases data 
in the server.
This is not be desirable (very slow) or possible (space limitation) for 
database with huge data.

For example, if the old version is already using over 50% of the mount point.
The new database may not have enough disk space for the upgrading.

Please correct me if I am wrong.

Thanks,
Patrick

[GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

While reading some manual of PostgreSQL and MySQL (eg. 
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html).

I have found that MySQL has stated many incompatibilities and know issues (a 
long list) in the MySQL version 5.0, 5.1, 5.5, 5.6 and 5.7.

For PostgreSQL, it seems I can't find the list (it just say see the Appendix E 
/ release notes).
I think it is a plus for PostgreSQL if it has few incompatibilities between 
major versions.

By the way, for in-place major version upgrade (not dumping DB and import 
again), MySQL is doing a better job in here.

Please share your thought, thanks.

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

[Patrick Dung]

From: Thomas Kellerer 
To: pgsql-general@postgresql.org 
Sent: Friday, September 13, 2013 12:58 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
 

> There is such a list in the release notes:
>
>http://www.postgresql.org/docs/current/static/release-9-3.html#AEN114132

>    Version 9.3 contains a number of changes that may affect compatibility 
>with previous releases.
>   Observe the following incompatibilities:

>And I think that section has been there for every major release (sometimes 
>even for minor releases).

Thanks for pointing out. I really miss the compatibility list in the release 
notes.

Regarding in place upgrade of PostgreSQL, they are mentioned in the todo list 
and wiki:
http://wiki.postgresql.org/wiki/Todo
http://wiki.postgresql.org/wiki/In-place_upgrade

Thanks,
Patrick