pgrounting extension in 11

[Prakash Ramakrishnan]

Hi Team,

Anyone help me to solve the below issue not able to create the pgrouting
extension 11 version in rhel 6.9 OS,


[root@hkgrhepdbp001 ~]# yum install pgrouting_11
Loaded plugins: enabled_repos_upload, package_upload, product-id,
rhnplugin, search-disabled-repos, security, subscription-manager
This system is not registered with RHN Classic or RHN Satellite.
You can use rhn_register to register.
RHN Satellite or RHN Classic support will be disabled.
Setting up Install Process
rhel-6-server-rpms
   | 2.0 kB
00:00
tnc_TNC_Nielsen_tnc_nielsen_rpms
   | 2.1 kB
00:00
No package pgrouting_11 available.
Error: Nothing to do
Uploading Enabled Repositories Report
Loaded plugins: product-id, rhnplugin

==> ./psql -p 5434
psql (11.5)
Type "help" for help.


postgres=# create extension pgrouting;
ERROR:  could not open extension control file
"/usr/pgsql-11/share/extension/pgrouting.control": No such file or directory
postgres=#
postgres=#


-- 
Thanks,
Prakash.R

Re: pgrounting extension in 11

[Adrian Klaver]

On 9/10/19 5:59 AM, Prakash Ramakrishnan wrote:

Hi Team,

Anyone help me to solve the below issue not able to create the pgrouting 
extension 11 version in rhel 6.9 OS,


What repo's are you pulling from RH's or PGDG?

If PGDG then I do not see pgRouting for RH 6.x:

https://yum.postgresql.org/testing/11/redhat/rhel-6-x86_64/repoview/letter_p.group.html




[root@hkgrhepdbp001 ~]# yum install pgrouting_11
Loaded plugins: enabled_repos_upload, package_upload, product-id, 
rhnplugin, search-disabled-repos, security, subscription-manager

This system is not registered with RHN Classic or RHN Satellite.
You can use rhn_register to register.
RHN Satellite or RHN Classic support will be disabled.
Setting up Install Process
rhel-6-server-rpms   
                                                              | 2.0 kB   
   00:00
tnc_TNC_Nielsen_tnc_nielsen_rpms 
                                                              | 2.1 kB   
   00:00

No package pgrouting_11 available.
Error: Nothing to do
Uploading Enabled Repositories Report
Loaded plugins: product-id, rhnplugin

==> ./psql -p 5434
psql (11.5)
Type "help" for help.


postgres=# create extension pgrouting;
ERROR:  could not open extension control file 
"/usr/pgsql-11/share/extension/pgrouting.control": No such file or directory

postgres=#
postgres=#


--
Thanks,
Prakash.R




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

Re: pgrounting extension in 11

[Prakash Ramakrishnan]

Hi Adrian,

I have only these files in pgdg,
[root@hkgrhepdbp001 ~]#  yum list | grep pgrouting
This system is not registered with RHN Classic or RHN Satellite.
You can use rhn_register to register.
RHN Satellite or RHN Classic support will be disabled.
pgrouting_93.x86_642.0.0-1.rhel6 @pgdg93
pgrouting_94.x86_642.0.1-2.rhel6 pgdg94
pgrouting_94-debuginfo.x86_64  2.0.1-2.rhel6 pgdg94
pgrouting_95.x86_642.0.1-1.rhel6 pgdg95
pgrouting_95-debuginfo.x86_64  2.0.1-1.rhel6 pgdg95


On Tue, Sep 10, 2019, 19:16 Adrian Klaver  wrote:

> On 9/10/19 5:59 AM, Prakash Ramakrishnan wrote:
> > Hi Team,
> >
> > Anyone help me to solve the below issue not able to create the pgrouting
> > extension 11 version in rhel 6.9 OS,
>
> What repo's are you pulling from RH's or PGDG?
>
> If PGDG then I do not see pgRouting for RH 6.x:
>
>
> https://yum.postgresql.org/testing/11/redhat/rhel-6-x86_64/repoview/letter_p.group.html
>
> >
> >
> > [root@hkgrhepdbp001 ~]# yum install pgrouting_11
> > Loaded plugins: enabled_repos_upload, package_upload, product-id,
> > rhnplugin, search-disabled-repos, security, subscription-manager
> > This system is not registered with RHN Classic or RHN Satellite.
> > You can use rhn_register to register.
> > RHN Satellite or RHN Classic support will be disabled.
> > Setting up Install Process
> > rhel-6-server-rpms
> >   | 2.0 kB
> >00:00
> > tnc_TNC_Nielsen_tnc_nielsen_rpms
> >   | 2.1 kB
> >00:00
> > No package pgrouting_11 available.
> > Error: Nothing to do
> > Uploading Enabled Repositories Report
> > Loaded plugins: product-id, rhnplugin
> >
> > ==> ./psql -p 5434
> > psql (11.5)
> > Type "help" for help.
> >
> >
> > postgres=# create extension pgrouting;
> > ERROR:  could not open extension control file
> > "/usr/pgsql-11/share/extension/pgrouting.control": No such file or
> directory
> > postgres=#
> > postgres=#
> >
> >
> > --
> > Thanks,
> > Prakash.R
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: pgrounting extension in 11

[Adrian Klaver]

On 9/10/19 6:53 AM, Prakash Ramakrishnan wrote:

Hi Adrian,

I have only these files in pgdg,


That is something you will need to take up with the PGDG RH packagers. 
You can file an issue here:


https://redmine.postgresql.org/projects/pgrpms/issues?set_filter=1

NOTE: You will a need a Postgres community account to get to above link.


[root@hkgrhepdbp001 ~]#  yum list | grep pgrouting
This system is not registered with RHN Classic or RHN Satellite.
You can use rhn_register to register.
RHN Satellite or RHN Classic support will be disabled.
pgrouting_93.x86_64                    2.0.0-1.rhel6         @pgdg93
pgrouting_94.x86_64                    2.0.1-2.rhel6         pgdg94
pgrouting_94-debuginfo.x86_64          2.0.1-2.rhel6         pgdg94
pgrouting_95.x86_64                    2.0.1-1.rhel6         pgdg95
pgrouting_95-debuginfo.x86_64          2.0.1-1.rhel6         pgdg95


On Tue, Sep 10, 2019, 19:16 Adrian Klaver > wrote:


On 9/10/19 5:59 AM, Prakash Ramakrishnan wrote:
 > Hi Team,
 >
 > Anyone help me to solve the below issue not able to create the
pgrouting
 > extension 11 version in rhel 6.9 OS,

What repo's are you pulling from RH's or PGDG?

If PGDG then I do not see pgRouting for RH 6.x:


https://yum.postgresql.org/testing/11/redhat/rhel-6-x86_64/repoview/letter_p.group.html

 >
 >
 > [root@hkgrhepdbp001 ~]# yum install pgrouting_11
 > Loaded plugins: enabled_repos_upload, package_upload, product-id,
 > rhnplugin, search-disabled-repos, security, subscription-manager
 > This system is not registered with RHN Classic or RHN Satellite.
 > You can use rhn_register to register.
 > RHN Satellite or RHN Classic support will be disabled.
 > Setting up Install Process
 > rhel-6-server-rpms
 >                                                               |
2.0 kB
 >    00:00
 > tnc_TNC_Nielsen_tnc_nielsen_rpms
 >                                                               |
2.1 kB
 >    00:00
 > No package pgrouting_11 available.
 > Error: Nothing to do
 > Uploading Enabled Repositories Report
 > Loaded plugins: product-id, rhnplugin
 >
 > ==> ./psql -p 5434
 > psql (11.5)
 > Type "help" for help.
 >
 >
 > postgres=# create extension pgrouting;
 > ERROR:  could not open extension control file
 > "/usr/pgsql-11/share/extension/pgrouting.control": No such file
or directory
 > postgres=#
 > postgres=#
 >
 >
 > --
 > Thanks,
 > Prakash.R
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: pgrounting extension in 11

[Prakash Ramakrishnan]

Thanks, I'll check it out.

On Tue, Sep 10, 2019, 19:49 Adrian Klaver  wrote:

> On 9/10/19 6:53 AM, Prakash Ramakrishnan wrote:
> > Hi Adrian,
> >
> > I have only these files in pgdg,
>
> That is something you will need to take up with the PGDG RH packagers.
> You can file an issue here:
>
> https://redmine.postgresql.org/projects/pgrpms/issues?set_filter=1
>
> NOTE: You will a need a Postgres community account to get to above link.
>
> > [root@hkgrhepdbp001 ~]#  yum list | grep pgrouting
> > This system is not registered with RHN Classic or RHN Satellite.
> > You can use rhn_register to register.
> > RHN Satellite or RHN Classic support will be disabled.
> > pgrouting_93.x86_642.0.0-1.rhel6 @pgdg93
> > pgrouting_94.x86_642.0.1-2.rhel6 pgdg94
> > pgrouting_94-debuginfo.x86_64  2.0.1-2.rhel6 pgdg94
> > pgrouting_95.x86_642.0.1-1.rhel6 pgdg95
> > pgrouting_95-debuginfo.x86_64  2.0.1-1.rhel6 pgdg95
> >
> >
> > On Tue, Sep 10, 2019, 19:16 Adrian Klaver  > > wrote:
> >
> > On 9/10/19 5:59 AM, Prakash Ramakrishnan wrote:
> >  > Hi Team,
> >  >
> >  > Anyone help me to solve the below issue not able to create the
> > pgrouting
> >  > extension 11 version in rhel 6.9 OS,
> >
> > What repo's are you pulling from RH's or PGDG?
> >
> > If PGDG then I do not see pgRouting for RH 6.x:
> >
> >
> https://yum.postgresql.org/testing/11/redhat/rhel-6-x86_64/repoview/letter_p.group.html
> >
> >  >
> >  >
> >  > [root@hkgrhepdbp001 ~]# yum install pgrouting_11
> >  > Loaded plugins: enabled_repos_upload, package_upload, product-id,
> >  > rhnplugin, search-disabled-repos, security, subscription-manager
> >  > This system is not registered with RHN Classic or RHN Satellite.
> >  > You can use rhn_register to register.
> >  > RHN Satellite or RHN Classic support will be disabled.
> >  > Setting up Install Process
> >  > rhel-6-server-rpms
> >  >   |
> > 2.0 kB
> >  >00:00
> >  > tnc_TNC_Nielsen_tnc_nielsen_rpms
> >  >   |
> > 2.1 kB
> >  >00:00
> >  > No package pgrouting_11 available.
> >  > Error: Nothing to do
> >  > Uploading Enabled Repositories Report
> >  > Loaded plugins: product-id, rhnplugin
> >  >
> >  > ==> ./psql -p 5434
> >  > psql (11.5)
> >  > Type "help" for help.
> >  >
> >  >
> >  > postgres=# create extension pgrouting;
> >  > ERROR:  could not open extension control file
> >  > "/usr/pgsql-11/share/extension/pgrouting.control": No such file
> > or directory
> >  > postgres=#
> >  > postgres=#
> >  >
> >  >
> >  > --
> >  > Thanks,
> >  > Prakash.R
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

pg full text search very slow for Chinese characters

[Jimmy Huang]

Hi Team,

Can anyone shed some light on why postgres 11 is extremely slow in my case?

I am making a mirror of zh.wikisource.org and I have downloaded 303049 pages 
and stored them in a postgres 11 database.

My postgres instance is based on docker image postgres:11 and runs on my 
MacBook Pro i7 16GB.

Database schema is as follows

Table pages(id, url, html, downloaded, inserted_at, updated_at) and books(id, 
name, info, preface, text, html, url, parent_id, inserted_at, updated_at, 
info_html, preface_html)

A wikisource web page is downloaded and its html text is inserted into table 
“pages” column “html.
Later, books.{name, info, preface, text, html, info_html, preface_html} are 
extracted from pages.html. The text column of books is a txt version of the 
content of html column of table pages.

On average there are 7635 characters (each characters is 3 bytes long because 
of utf-8 encoding) for text column of table books and I want to add full text 
search to books(text).

I tried pg_trgm and my own customized token parser 
https://github.com/huangjimmy/pg_cjk_parser

To my surprise, postgres 11 is extremely slow when creating a full text index.

I added a column of tsvector type and tried to create an index on that column. 
Pg could not finish creating a GIN index for a long time and I had to cancel 
the execution.
I then tried to create a partial full text index for 500 rows and it took 
postgres 2 to 3 minutes to create the index. Based on this estimation, pg will 
need at least one day to create a full GIN full text search index for 303049 
rows of data. I think this is ridiculous slow.
If I tried to create fts index for books(name) or books(info), it took just 3 
minutes to create the index. However, name and info are extremely short 
compared to books(text).

I switched to Elasticsearch and it turned out that Elasticsearch is extremely 
efficient for my case. It took Elasticsearch 3 hours to index all 303049 rows.

Jimmy Huang
jimmy_hu...@live.com

Re: pg full text search very slow for Chinese characters

[Cory Nemelka]

Well, there is a lot of information we would need to diagnose this.  How
much tuning have you done?, etc.

My advice is pretty simple.  Don't expect performance on a notebook and,
unless you are planning on hosting it on a notebook, use the notebook for
development only .  Test performance on a properly configured and tuned
server.

--cnemelka


On Tue, Sep 10, 2019 at 9:53 AM Jimmy Huang  wrote:

> Hi Team,
>
>
>
> Can anyone shed some light on why postgres 11 is extremely slow in my case?
>
>
>
> I am making a mirror of zh.wikisource.org and I have downloaded 303049
> pages and stored them in a postgres 11 database.
>
>
>
> My postgres instance is based on docker image postgres:11 and runs on my
> MacBook Pro i7 16GB.
>
>
>
> Database schema is as follows
>
>
>
> Table pages(id, url, html, downloaded, inserted_at, updated_at) and
> books(id, name, info, preface, text, html, url, parent_id, inserted_at,
> updated_at, info_html, preface_html)
>
>
>
> A wikisource web page is downloaded and its html text is inserted into
> table “pages” column “html.
>
> Later, books.{name, info, preface, text, html, info_html, preface_html}
> are extracted from pages.html. The text column of books is a txt version of
> the content of html column of table pages.
>
>
>
> On average there are 7635 characters (each characters is 3 bytes long
> because of utf-8 encoding) for text column of table books and I want to add
> full text search to books(text).
>
>
>
> I tried pg_trgm and my own customized token parser
> https://github.com/huangjimmy/pg_cjk_parser
>
>
>
> To my surprise, postgres 11 is extremely slow when creating a full text
> index.
>
>
>
> I added a column of tsvector type and tried to create an index on that
> column. Pg could not finish creating a GIN index for a long time and I had
> to cancel the execution.
>
> I then tried to create a partial full text index for 500 rows and it took
> postgres 2 to 3 minutes to create the index. Based on this estimation, pg
> will need at least one day to create a full GIN full text search index for
> 303049 rows of data. I think this is ridiculous slow.
>
> If I tried to create fts index for books(name) or books(info), it took
> just 3 minutes to create the index. However, name and info are extremely
> short compared to books(text).
>
>
>
> I switched to Elasticsearch and it turned out that Elasticsearch is
> extremely efficient for my case. It took Elasticsearch 3 hours to index all
> 303049 rows.
>
>
>
> Jimmy Huang
>
> jimmy_hu...@live.com
>

Re: pg full text search very slow for Chinese characters

[Michael Lewis]

>My postgres instance is based on docker image postgres:11 and runs on my
MacBook Pro i7 16GB.

How much ram and such did you give to this vm?


>To my surprise, postgres 11 is extremely slow when creating a full text
index. I added a column of tsvector type and tried to create an index on
that column. Pg could not finish creating a GIN index for a long time and I
had to cancel the execution.I then tried to create a partial full text
index for 500 rows and it took postgres 2 to 3 minutes to create the index.


Did you customize any config? maintenance_work_mem specifically would be
relevant to the time to create an index and default value is only 64MB.
Especially if you are running a spinning hard drive and not ssd, then this
could be problematic.

Re: pg full text search very slow for Chinese characters

[Cory Nemelka]

On Tue, Sep 10, 2019 at 10:11 AM Michael Lewis  wrote:

> >My postgres instance is based on docker image postgres:11 and runs on my
> MacBook Pro i7 16GB.
>
> How much ram and such did you give to this vm?
>
>
> >To my surprise, postgres 11 is extremely slow when creating a full text
> index. I added a column of tsvector type and tried to create an index on
> that column. Pg could not finish creating a GIN index for a long time and I
> had to cancel the execution.I then tried to create a partial full text
> index for 500 rows and it took postgres 2 to 3 minutes to create the index.
>
>
> Did you customize any config? maintenance_work_mem specifically would be
> relevant to the time to create an index and default value is only 64MB.
> Especially if you are running a spinning hard drive and not ssd, then this
> could be problematic.
>


I apologize for my top post. :D Won't happen again

Re: pg full text search very slow for Chinese characters

[Jimmy Huang]

It is all default values.
I just check maintenance_work_mem and indeed it is 64MB.

At first I gave 2GB ram to docker and later I increased ram to 4GB.
It did not make much difference when ram increased from 2GB to 4GB.

I will try increasing maintenance_work_mem and see if it helps.

发件人: Michael Lewis 
日期: 2019年9月11日 星期三 上午12:11
收件人: 黄 少君 
抄送: "pgsql-general@lists.postgresql.org" 
主题: Re: pg full text search very slow for Chinese characters

>My postgres instance is based on docker image postgres:11 and runs on my 
>MacBook Pro i7 16GB.

How much ram and such did you give to this vm?


>To my surprise, postgres 11 is extremely slow when creating a full text index. 
>I added a column of tsvector type and tried to create an index on that column. 
>Pg could not finish creating a GIN index for a long time and I had to cancel 
>the execution.I then tried to create a partial full text index for 500 rows 
>and it took postgres 2 to 3 minutes to create the index.


Did you customize any config? maintenance_work_mem specifically would be 
relevant to the time to create an index and default value is only 64MB. 
Especially if you are running a spinning hard drive and not ssd, then this 
could be problematic.

Re: pg full text search very slow for Chinese characters

[Tom Lane]

Jimmy Huang  writes:
> I tried pg_trgm and my own customized token parser 
> https://github.com/huangjimmy/pg_cjk_parser

pg_trgm is going to be fairly useless for indexing text that's mostly
multibyte characters, since its unit of indexable data is just 3 bytes
(not characters).  I don't know of any comparable issue in the core
tsvector logic, though.  The numbers you're quoting do sound quite awful,
but I share Cory's suspicion that it's something about your setup rather
than an inherent Postgres issue.

regards, tom lane

Re: pg full text search very slow for Chinese characters

[Andreas Joseph Krogh]

På tirsdag 10. september 2019 kl. 18:21:45, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Jimmy Huang  writes:
 > I tried pg_trgm and my own customized token parser 
https://github.com/huangjimmy/pg_cjk_parser

 pg_trgm is going to be fairly useless for indexing text that's mostly
 multibyte characters, since its unit of indexable data is just 3 bytes
 (not characters). I don't know of any comparable issue in the core
 tsvector logic, though. The numbers you're quoting do sound quite awful,
 but I share Cory's suspicion that it's something about your setup rather
 than an inherent Postgres issue.

 regards, tom lane We experienced quite awful performance when we hosted the 
DB on virtual servers (~5 years ago) and it turned out we hit the write-cache 
limit (then 8GB), which resulted in ~1MB/s IO thruput. Running iozone might 
help tracing down IO-problems. --
 Andreas Joseph Krogh

Re: kind of a bag of attributes in a DB . . .

[Albretch Mueller]

On 9/7/19, Adrian Klaver  wrote:
> Is the metadata uniform or are you dealing with a variety of different
> data?

 You can expect for all files to have a filename and size, but their
kinds (the metadata describing them) can be really colorful and wild
when it comes to formatting.

 lbrtchx

Recover data from aborted transactions

[Aaron Spike]

I've used PITR on a number of occasions to look back at old data and
recover deleted records. Recently while searching for a missing record I
ran across the following line in the output of pg_xlogdump:

rmgr: Transaction len (rec/tot):200/   226, tx:   26025438, lsn:
> 127/9166CF18, prev 127/9166CE28, desc: ABORT 2019-08-27 15:53:30.468833
> CDT; subxacts: 26025439 26025445 26025765 26025789 26025794 26025798
> 26025799 26025800 26025808 26025809 26025819 26025821 26025823 26025824
> 26025834 26025839 26025845 26025847 26026272 26027349 26027788 26027866
> 26028018 26028036 26028127 26028151 26028256 26028257 26028258 26028259
> 26028260 26028261 26028262 26028263 26028264 26028266 26028275 26028277
> 26028285 26028287 26028336 26028366 26028394 26028397 26028401 26030070
>

(Incidentally, if anyone can recommend resources for learning to read and
understand the output of pg_xlogdump, that would be greatly appreciated.)

I'm pretty sure that the records I'm looking for are part of one of these
aborted transactions. From what I read online, it seems that data from
uncommitted transactions exists in the Write-Ahead Logs. Is there anyway to
access this data?

Aaron Spike


*This electronic communication, including any attached documents, may
contain confidential and/or legally privileged information that is intended
only for use by the recipient(s) named above. If you have received this
communication in error, please notify the sender immediately and delete the
communication and any attachments.Views expressed by the author do not
necessarily represent those of Martin Luther College.*

-- 
This electronic communication, including any attached 
documents, may 
contain confidential and/or legally privileged 
information that is 
intended only for use by the recipient(s) named 
above. If you have 
received this communication in error, please notify 
the sender immediately 
and delete the communication and any 
attachments. Views expressed by the 
author do not necessarily represent 
those of Martin Luther College.

Re: kind of a bag of attributes in a DB . . .

[Adrian Klaver]

On 9/10/19 9:59 AM, Albretch Mueller wrote:

On 9/7/19, Adrian Klaver  wrote:

Is the metadata uniform or are you dealing with a variety of different
data?


  You can expect for all files to have a filename and size, but their
kinds (the metadata describing them) can be really colorful and wild
when it comes to formatting.


If there is no rhyme or reason to the metadata I am not sure how you 
could come up with an efficient search strategy. Seems it would be a 
brute search over everything.




  lbrtchx




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

Re: pg full text search very slow for Chinese characters

[Kyotaro Horiguchi]

Hi.

At Tue, 10 Sep 2019 18:42:26 +0200 (CEST), Andreas Joseph Krogh 
 wrote in 

> På tirsdag 10. september 2019 kl. 18:21:45, skrev Tom Lane 
>  >: Jimmy Huang  writes:
>  > I tried pg_trgm and my own customized token parser 
> https://github.com/huangjimmy/pg_cjk_parser
> 
>  pg_trgm is going to be fairly useless for indexing text that's mostly
>  multibyte characters, since its unit of indexable data is just 3 bytes
>  (not characters). I don't know of any comparable issue in the core
>  tsvector logic, though. The numbers you're quoting do sound quite awful,
>  but I share Cory's suspicion that it's something about your setup rather
>  than an inherent Postgres issue.
> 
>  regards, tom lane We experienced quite awful performance when we hosted the 
> DB on virtual servers (~5 years ago) and it turned out we hit the write-cache 
> limit (then 8GB), which resulted in ~1MB/s IO thruput. Running iozone might 
> help tracing down IO-problems. --
>  Andreas Joseph Krogh

Multibyte characters also quickly bloats index by many many small
buckets for every 3-characters combination of thouhsand of
characters, which makes it useless.

pg_bigm based on bigram/2-gram works better on multibyte
characters.

https://pgbigm.osdn.jp/index_en.html

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center