[GENERAL] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL

2013-12-06 Thread Sameer Kumar
Hi,

I am trying to do a custom build (and generate binary and source RPM)
package for PostgreSQL.

I know community already has a RPM package, but I am trying to do a custom
build.

I am using attached SPEC file. But I am not able to get binary rpm.
rpmbuild always gives me source rpm only. Is there anything wrong with my
spec file?

Towards end of the process I get below messages:

Checking for unpackaged file(s): /usr/lib/rpm/check-files
 /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64
 Wrote: /root/rpmbuild/SRPMS/PostgreSQL-9.3-1.src.rpm
 Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.m3q9Du
 + umask 022
 + cd /root/rpmbuild/BUILD
 + /bin/rm -rf /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64
 + exit 0


The file I am using is very basic (since I am just starting with the
process). Does community maintain an rpm SPEC file for PostgreSQL? Can I
get access to it?


Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350 %2B65%208110%200350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
image002.jpg

postgresql.spec
Description: Binary data

-- 
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] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL

2013-12-06 Thread Albe Laurenz
Sameer Kumar wrote:
 I am trying to do a custom build (and generate binary and source RPM) package 
 for PostgreSQL.
 
 I know community already has a RPM package, but I am trying to do a custom 
 build.
 
 I am using attached SPEC file. But I am not able to get binary rpm. rpmbuild 
 always gives me source
 rpm only. Is there anything wrong with my spec file?

It is incomplete.

There should be a %prep, %build, %install and %files section at least.

 Towards end of the process I get below messages:
 
 
 
   Checking for unpackaged file(s): /usr/lib/rpm/check-files
 /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64
   Wrote: /root/rpmbuild/SRPMS/PostgreSQL-9.3-1.src.rpm
   Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.m3q9Du
   + umask 022
   + cd /root/rpmbuild/BUILD
   + /bin/rm -rf /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64
   + exit 0
 
 
 
 The file I am using is very basic (since I am just starting with the 
 process). Does community maintain
 an rpm SPEC file for PostgreSQL? Can I get access to it?

Start looking here:
http://yum.postgresql.org/srpms/9.3/redhat/rhel-6-x86_64/repoview/postgresql93.html

The source RPMs contain the spec file.

Yours,
Laurenz Albe

-- 
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] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL

2013-12-06 Thread Devrim GÜNDÜZ

Hi,

On Fri, 2013-12-06 at 10:30 +, Albe Laurenz wrote:
 Start looking here:
 http://yum.postgresql.org/srpms/9.3/redhat/rhel-6-x86_64/repoview/postgresql93.html
 
 The source RPMs contain the spec file.

Also, http://svn.pgrpms.org/browser/rpm/redhat/9.3/postgresql/ is the
SVN repo for 9.3 spec files and patches.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part


Re: [GENERAL] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL

2013-12-06 Thread Michael Paquier
That


On Fri, Dec 6, 2013 at 7:08 PM, Sameer Kumar sameer.ku...@ashnik.comwrote:

 Hi,

 I am trying to do a custom build (and generate binary and source RPM)
 package for PostgreSQL.

 I know community already has a RPM package, but I am trying to do a custom
 build.

 I am using attached SPEC file. But I am not able to get binary rpm.
 rpmbuild always gives me source rpm only. Is there anything wrong with my
 spec file?

After a quick glance at your spec, I am not seing any %files section.

The file I am using is very basic (since I am just starting with the
 process). Does community maintain an rpm SPEC file for PostgreSQL?

Yes, here:
http://svn.pgrpms.org/repo/rpm/redhat/

Can I get access to it?

Use for example this command to create a git repository of that:
git svn clone http://svn.pgrpms.org/repo/rpm/redhat
Or a simple svn command...

If I were you, I'd simply take the community spec and modify it for my
needs, it is already really complete and contains automatic management of
for example translation files, smth that is always a pain to maintain
manually.

Regards,
-- 
Michael


Re: [GENERAL] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL

2013-12-06 Thread Sameer Kumar
On Fri, Dec 6, 2013 at 6:30 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:

 Sameer Kumar wrote:
  I am trying to do a custom build (and generate binary and source RPM)
 package for PostgreSQL.
 
  I know community already has a RPM package, but I am trying to do a
 custom build.
 
  I am using attached SPEC file. But I am not able to get binary rpm.
 rpmbuild always gives me source
  rpm only. Is there anything wrong with my spec file?

 It is incomplete.

 There should be a %prep, %build, %install and %files section at least.


I thought %files is non-mandatory.




  Towards end of the process I get below messages:
 
 
 
Checking for unpackaged file(s): /usr/lib/rpm/check-files
  /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64
Wrote: /root/rpmbuild/SRPMS/PostgreSQL-9.3-1.src.rpm
Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.m3q9Du
+ umask 022
+ cd /root/rpmbuild/BUILD
+ /bin/rm -rf
 /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64
+ exit 0
 
 
 
  The file I am using is very basic (since I am just starting with the
 process). Does community maintain
  an rpm SPEC file for PostgreSQL? Can I get access to it?

 Start looking here:

 http://yum.postgresql.org/srpms/9.3/redhat/rhel-6-x86_64/repoview/postgresql93.html

 The source RPMs contain the spec file.


Thanks! this will help





Re: [GENERAL] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL

2013-12-06 Thread Sameer Kumar
Thanks everyone for helping.

If I were you, I'd simply take the community spec and modify it for my
 needs, it is already really complete and contains automatic management of
 for example translation files, smth that is always a pain to maintain
 manually.


I agree and that is why I asked for it :)



Cheers
Sameer


[GENERAL] pgadmin III query

2013-12-06 Thread Peter Kroon
When you click on a table in the Object browser you'll see in the SQL
pane the sql that is needed to create that table.

Which function can I call to get that SQL?

Best,
Peter


Re: [GENERAL] pgadmin III query

2013-12-06 Thread Ashesh Vashi
There is no ready available function to generate the reverse engineered
query.
pgAdmin III generates it from the metadata (table information) available.


On Fri, Dec 6, 2013 at 4:17 PM, Peter Kroon plakr...@gmail.com wrote:

 When you click on a table in the Object browser you'll see in the SQL
 pane the sql that is needed to create that table.

 Which function can I call to get that SQL?

 Best,
 Peter




-- 
--

Thanks  Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Companyhttp://www.enterprisedb.com



*http://www.linkedin.com/in/asheshvashi*http://www.linkedin.com/in/asheshvashi


[GENERAL] postgresql or xquery?

2013-12-06 Thread e-letter
Readers,

Admittedly, a biased place to ask (!), but for a new database project,
how best to evaluate whether postgresql or xquery should be used.

As a novice of postgresql and xml, am not sure what is most
appropriate for a new database that will be tested initially on a
local computer, then transferred to a web server at some point in the
future.

For now, just reading about the two technologies before deciding. It
seems that an established system would be: data entered to various sql
tables and stored on server; php used to query table and deliver data,
for example as xhtml and css; user views query results as a table of
values in a web page. What does xquery do as a better advantage (if at
all)?


-- 
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] pgadmin III query

2013-12-06 Thread Jov
use pg_dump -s can get the DDL SQL.

jov
在 2013-12-6 下午6:50,Peter Kroon plakr...@gmail.com写道:

 When you click on a table in the Object browser you'll see in the SQL
 pane the sql that is needed to create that table.

 Which function can I call to get that SQL?

 Best,
 Peter



Re: [GENERAL] pgadmin III query

2013-12-06 Thread Ian Lawrence Barwick
2013/12/6 Peter Kroon plakr...@gmail.com:
 When you click on a table in the Object browser you'll see in the SQL
 pane the sql that is needed to create that table.

 Which function can I call to get that SQL?

You can use the pg_dump command line function for this:

  pg_dump -s -t name_of_table name_of_database

Regards

Ian Barwick


-- 
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] postgresql or xquery?

2013-12-06 Thread vincent elschot


On 06-12-13 11:54, e-letter wrote:

Readers,

Admittedly, a biased place to ask (!), but for a new database project,
how best to evaluate whether postgresql or xquery should be used.

As a novice of postgresql and xml, am not sure what is most
appropriate for a new database that will be tested initially on a
local computer, then transferred to a web server at some point in the
future.

For now, just reading about the two technologies before deciding. It
seems that an established system would be: data entered to various sql
tables and stored on server; php used to query table and deliver data,
for example as xhtml and css; user views query results as a table of
values in a web page. What does xquery do as a better advantage (if at
all)?



Which is will be the better choice depends on what your requirements are.
Even MySQL can store data in a table and return it :)

Do you have any particular things you want to do? Do you have estimates 
about
the size of the database and how many users this database will have to 
serve?



--
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] pgadmin III query

2013-12-06 Thread Peter Kroon
Thanks, but i need a non command line option.


2013/12/6 Ian Lawrence Barwick barw...@gmail.com

 2013/12/6 Peter Kroon plakr...@gmail.com:
  When you click on a table in the Object browser you'll see in the SQL
  pane the sql that is needed to create that table.
 
  Which function can I call to get that SQL?

 You can use the pg_dump command line function for this:

   pg_dump -s -t name_of_table name_of_database

 Regards

 Ian Barwick



Re: [GENERAL] Similarity search for sentences

2013-12-06 Thread Janek Sendrowski
Hi,
thanks for your Answers.
 
@Rémi Cura
You suggest a kind of Full Text Search.  I already had a try with the tsearch2 
extension.
The issue is to realize the similarity search. I have to use many OR statements 
with a low set of arguments.
That significantly slows the FTS down.
 
@Kevin Grittner
I used my own trigger to store the tsvector of the sentences and I created a 
usual gist Index on them.
What kind of functional Index would you suggest. Like i already told Rémi, I 
have to to use many OR statements with a low set of arguments, which heavy 
damages the perfance.
Do you have a better idea?
I usually used a query like this:
 
The tiger is the largest cat species[http://en.wikipedia.org/wiki/Felidae], 
reaching a total body length of up to 3.3 m  and weighing up to 306 kg.
--
totsvector:
'3.3':16 '306':22 'bodi':11 'cat':6 'kg':23 'largest':5 'length':12 'm':17 
'reach':8 'speci':7 'tiger':2 'total':10 'weigh':19
(1 row)
 
SELECT * FROM tablename WHERE vector @@ to_tsquery('speci  tiger  total  
weigh') AND vector @@ to_tsquery('largest  length  m  reach')  AND vector @@ 
to_tsquery('3.3  306  bodi  cat  kg');

And thats very slow
 
I didn't know that the pg_trgm Module provides KNN search.
 
Janek Sendrowski
 
 
 


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


[GENERAL] Testing an extension without installing it

2013-12-06 Thread Florian Weimer
Is it possible to test an C extension module (.so file) without 
installing a SHAREDIR/extension/extension_name.control file?


My test suite already runs initdb and the database as a non-postgres 
user, but I don't see a way to override the extension control file location.


--
Florian Weimer / Red Hat Product Security Team


--
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] Similarity search for sentences

2013-12-06 Thread Kevin Grittner
Janek Sendrowski jane...@web.de wrote:

 I didn't know that the pg_trgm Module provides KNN search

It does, although my own experience shows that it tends to be more
appropriate for name searches or similar smaller columns than for
big text columns.  Using the war_and_peace table from another
thread:

test=# CREATE INDEX war_and_peace_linetext_trgm2 ON war_and_peace
using gist (linetext gist_trgm_ops);
CREATE INDEX
test=# VACUUM ANALYZE war_and_peace;
VACUUM
test=# -- Use a KNN search for the words.
EXPLAIN ANALYZE
SELECT * FROM war_and_peace
  ORDER BY 'ladies gentlemen provinces distance' - linetext
  LIMIT 10;
  QUERY 
PLAN  
---
 Limit  (cost=0.28..2.74 rows=10 width=116) (actual time=42.157..42.246 rows=10 
loops=1)
   -  Index Scan using war_and_peace_linetext_trgm2 on war_and_peace  
(cost=0.28..16016.42 rows=65007 width=116) (actual time=42.155..42.243 rows=10 
loops=1)
 Order By: (linetext - 'ladies gentlemen provinces distance'::text)
 Total runtime: 42.716 ms
(4 rows)

As you can see, it pulls the requested number of rows from the
index in the order of distance (inverse similarity).  It is,
however, not as fast as a tsearch2 search (43 ms instead of a
fraction of a ms), and it doesn't give you options to use AND/OR as
you might like.  It is still a lot faster than scanning the whole
table and applying the test to each row.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Testing an extension without installing it

2013-12-06 Thread Tom Lane
Florian Weimer fwei...@redhat.com writes:
 Is it possible to test an C extension module (.so file) without 
 installing a SHAREDIR/extension/extension_name.control file?

Well, you could just ignore the extension mechanism: do some
manual CREATE FUNCTION commands and test the functionality
through those.

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] [PERFORM] Similarity search with the tsearch2 extension

2013-12-06 Thread Janek Sendrowski
Sorry, I used AND-statements instead of OR-statement in the example.
I notices that gin is much faster than gist, but I don't know why.

The query gets slow, because there are many non-stop words which appear very 
often in my sentences, like in 3% of all the sentences.
Do you think it could be worth it to filter the words, which appears that often 
and declare them as stop-words.
How would you split a sentence with let's say 10 non stop words to provide a 
performed similarity search?
 
There's still the problem with very short sentences. An partiel index on them 
with the trigram search might be the solution.
The pg_trgm module is far to slow for bigger setences, like you showed.
 
I thought I'll build a few partiel indexes on the string length, to enhance the 
performance.
Do you know some more improvements?
 
Janek Sendrowki


-- 
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] Fwd: Help!Why CPU Usage and LoadAverage Jump up Suddenly

2013-12-06 Thread 吕晓旭
there are several reason drive us to build ourselves rpm, the most important is 
we want to install it in a directory self definition
and another reason is setting CFLAGS(add -mavx for example, high light at 
previous ).
because, without this compile parameter, response time is suffering on dell 
machine 

according to the system monitor chart shown on cacti, i don't think IO is 
heavy, no matter cpu io wait and Reads/Writes - sda.
there is no monitor items showing strange behavior of IO system, and no 
different between low concurrency and heavy concurrency,
and no different between two environments.

I find something with top : CPU usage of each postgres process is not so high 
when low concurrency, the highest one is about 50%.
but when concurrency gradually increased, some postgres process cpu usage reach 
100%, and keep a moment. and at these high concurrency period runq-sz(with sar 
-q) is very long, sometime 50 process waiting the CPU time.
so is there something like spin-lock keep the CPU time?


some configurations on the both system:
shared_buffers = 8192MB
work_mem =256MB
maintenance_work_mem = 160MB
full_page_writes = off
wal_buffers = 10MB
wal_keep_segments = 150


hardware configurations list below(both environment with raid 10):
* perfect one:
$ sudo hwconfig
Summary:HP DL360 G7, 1 x Xeon E5645 2.40GHz, 94.4GB / 96GB 1333MHz DDR3
System:HP ProLiant DL360 G7
Processors:1 (of 2) x Xeon E5645 2.40GHz 133MHz FSB (HT enabled, 6 cores, 
24 threads)
Memory:94.4GB / 96GB 1333MHz DDR3 == 6 x 16GB, 12 x empty
Disk:cciss/c0d0 (cciss0): 1.2TB (48%) RAID-10 == 4 x HP-EG0600FBLSH
Disk-Control:cciss0: Hewlett-Packard Company Smart Array G6 controllers, FW 
5.14, Cache on 256MB/768MB (R/W)
Chipset:Intel 82801JIB (ICH10)
Network:eth0 (bnx2): Broadcom NetXtreme II BCM5709 Gigabit, 
e4:11:5b:ed:12:1c, 1000Mb/s full-duplex
Network:eth1 (bnx2): Broadcom NetXtreme II BCM5709 Gigabit, 
e4:11:5b:ed:12:1e, no carrier
Network:eth2 (bnx2): Broadcom NetXtreme II BCM5709 Gigabit, 
e4:11:5b:ed:12:58, no carrier
Network:eth3 (bnx2): Broadcom NetXtreme II BCM5709 Gigabit, 
e4:11:5b:ed:12:5a, no carrier
OS:CentOS 5.6 (Final), Linux 2.6.18-238.19.1.el5 x86_64, 64-bit
BIOS:HP P68 12/02/2012
Hostname:l-interdb3.f.cn1



* the one performances so bad:
$ sudo hwconfig
hwconfig: warning: could not run MegaCli
Summary:Dell R620, 1 x Xeon E5-2630 0 2.30GHz, 62.9GB / 64GB 1600MHz 
DDR3
System: Dell PowerEdge R620 (Dell 0D2D5F)
Processors: 1 (of 2) x Xeon E5-2630 0 2.30GHz 7200MHz FSB (HT enabled, 6 
cores, 24 threads)
Memory: 62.9GB / 64GB 1600MHz DDR3 == 8 x 8GB, 16 x empty
Disk:   sda (scsi6): 1.2TB (29%) JBOD == 1 x DELL-PERC-H710P
Disk-Control:   ahci0: Intel Patsburg 6-Port SATA AHCI Controller
Disk-Control:   megaraid_sas0: LSI Logic / Symbios Logic MegaRAID SAS 2208 
[Thunderbolt]
Network:em1 (tg3): Broadcom NetXtreme BCM5720 Gigabit PCIe, 
e0:db:55:1f:9b:d8, 1000Mb/s full-duplex
Network:em2 (tg3): Broadcom NetXtreme BCM5720 Gigabit PCIe, 
e0:db:55:1f:9b:d9, no carrier
Network:em3 (tg3): Broadcom NetXtreme BCM5720 Gigabit PCIe, 
e0:db:55:1f:9b:da, no carrier
Network:em4 (tg3): Broadcom NetXtreme BCM5720 Gigabit PCIe, 
e0:db:55:1f:9b:db, no carrier
OS: CentOS 6.2 (Final), Linux 3.2.34-1.el6.x86_64 x86_64, 64-bit
BIOS:   Dell 1.4.8 10/25/2012
Hostname:   l-interdb11.f.cn1





2013/12/6 John R Pierce pie...@hogranch.com
 On 12/5/2013 12:46 AM, 吕晓旭 wrote:
 We find so weird problem on our productive PostgreSQL system. And I 
 don't know how could I do to resolve this problem.
 We deployed PostgreSQL 9.2.4 on two system environments,  and the 
 performances between them are absolutely different. one of them it's 
 perfect, and the other one lets me down, CPU Usage and LoadAverage Jumped up 
 Suddenly when concurrency smoothly rising up, simultaneously, average 
 response time become unacceptable.
 
 I'm curious why you built your own postgres instead of using the 
 yum.postgresql.com repository versions?
 
 and I second the suggestion, IO performance is likely a major factor here.
  also, you don't give your postgresql.conf tuning settings, file systems 
 configurations, hardware storage configurations, etc.
 
 -- 
 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



Re: [GENERAL] Fwd: Help!Why CPU Usage and LoadAverage Jump up Suddenly

2013-12-06 Thread John R Pierce

On 12/6/2013 11:04 AM, 吕晓旭 wrote:
I find something with top : CPU usage of each postgres process is not 
so high when low concurrency, the highest one is about 50%.
but when concurrency gradually increased, some postgres process cpu 
usage reach 100%, and keep a moment. and at these high concurrency 
period runq-sz(with sar -q) is very long, sometime 50 process waiting 
the CPU time.

so is there something like spin-lock keep the CPU time?


some configurations on the both system:
shared_buffers = 8192MB
work_mem =256MB
maintenance_work_mem = 160MB
full_page_writes = off
wal_buffers = 10MB
wal_keep_segments = 150


50 concurrent active queries with work_mem = 256MB would use like 13GB 
ram, or possibly more if the queries are complex and require multiple 
work_mems.   I see you have 64GB ram on the 'slow' system, so that 
/shouldn't/ be an issue, unless you end up with much higher concurrent 
query counts.


seems to be a lot of variables here, one system is running CentOS 5.x 
and the other CentOS 6.x, if I remember correctly from your first post.  
different memory sizes.  different disk controllers. both CentOS 
versions are quite out of date (5.7 and 6.5 are both direct inplace 
upgrades).I also recall CentOS 6.2 had some file system bugs 
relating to XFS, but I dunno if that applies here. h, kernel 3.2.34 
?  thats not the centos 6.2 kernel, where is it from ?


btw, those CPUs have 12 threads, not 24



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