Re: [GENERAL] 9.6 beta2 win-x64 download links still point to beta1

[Bruce Momjian]

On Fri, Jun 24, 2016 at 07:44:17AM +0200, Thomas Kellerer wrote:
> Hello,
> 
> the Beta2 downloads on 
> 
>   http://www.enterprisedb.com/products-services-training/pgdownload
>   http://www.enterprisedb.com/products-services-training/pgbindownload
> 
> still lead to Beta1 for the Windows 64bit builds. 
> 
> All others properly link to beta1

This looks fixed now.

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

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
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] questions about how to implement a gist index

[Oleg Bartunov]

On Tue, Jun 28, 2016 at 12:44 AM, Riccardo Vianello
 wrote:
> Hi all,
>
> I'm trying to contribute some improvements to the implementation of a gist
> index that is part of an open source project and it would be really nice if
> anyone could help me answer some questions.
>
> I would like to use different data structures to represent the internal and
> leaf entries. I think I have found several examples in the postgresql source
> code where a variable length array is used to pack different data
> consistently with the value of a "type" attribute. Is this the suggested way
> to proceed, or would it be also possible to use two actually different data
> structures? I at first considered this latter approach, but some erroneous
> behavior suggested that maybe I am not fully clear about which data
> structure should be produced or expected by some parts of the
> implementation.
>
> Could you please also help me understand the difference (if any) between
> using the GIST_LEAF macro or the leafkey attribute of the GISTENTRY data
> structure?

Yes, this is confused.

GIST_LEAF is TRUE if key is in leaf page.

bool leafkey points if TRUE that key contains value from heap.

>
> Thank you very much in advance,
>
> Riccardo
>
>


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

[GENERAL] questions about how to implement a gist index

[Riccardo Vianello]

Hi all,

I'm trying to contribute some improvements to the implementation of a gist
index that is part of an open source project and it would be really nice if
anyone could help me answer some questions.

I would like to use different data structures to represent the internal and
leaf entries. I think I have found several examples in the postgresql
source code where a variable length array is used to pack different data
consistently with the value of a "type" attribute. Is this the suggested
way to proceed, or would it be also possible to use two actually different
data structures? I at first considered this latter approach, but some
erroneous behavior suggested that maybe I am not fully clear about which
data structure should be produced or expected by some parts of the
implementation.

Could you please also help me understand the difference (if any) between
using the GIST_LEAF macro or the leafkey attribute of the GISTENTRY data
structure?

Thank you very much in advance,

Riccardo

Re: [GENERAL] Transaction serialization

[Dusan Milanov]

Thank you Thomas, this was exactly what I was looking for.


On 24.6.2016 0:57, Thomas Munro wrote:

On Fri, Jun 24, 2016 at 4:13 AM, Dusan Milanov  wrote:

Hi,

A question about transactions: does postgres report serialization errors
before a transaction is committed? Obviously, it does on commit, but how
about previous statements? Can there be an ERRCODE_T_R_SERIALIZATION_FAILURE
error as a response to anything else but the commit?

Yes.  See src/test/isolation/expected/project-manager.out, which shows
how to reach this error:

postgres=# UPDATE person SET is_project_manager = false WHERE person_id = 1;
ERROR:  could not serialize access due to read/write dependencies
among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.





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

[GENERAL] Different query plans using different roles

[Charles]

I have a situation where the same query generates two different plans
depending on which user executes it. Two of the tables do have row-level
security enabled, the policy is roughly
POLICY "policy_qai_U0002_7900_0" FOR SELECT
  TO qai_U0002
  USING (true)

Here is the plan when the owner executes it
 Nested Loop  (cost=1.30..1078.74 rows=1 width=24)
   ->  Nested Loop  (cost=0.72..1070.34 rows=1 width=30)
 ->  Index Scan using vw_secmap_eid_idx1 on vw_secmap map
 (cost=0.29..982.62 rows=3 width=12)
   Index Cond: (eid = ANY
('{150689,150721,150761,150824,150827,150973,151050,151140,151165,151354,151356,151411,151646,152098,152194,152204,152355,152517,152671,152947,153062,153272,153430,153575,153585,153608,153614,153732,154102,154183,154195,154373,154472,154608,154647,154666,154864,154951,154996,155021,155062,155066,155073,155078,155132,155156,155274,155299,155328,155345,155401,155937,156046,156179,156230,156344,156415,156578,156653,156797,156812,157027,157056,157184,157508,157708,157713,157736,157888,157905,158022,158032,158132,158439,158479,158484,158518,158586,158590,158635,158654,158901,158973,159049,159099,159138,159198,159204,159262,159324,159383,159458,159689,159902,160330,160424,160428,160484,160537,160569,160678,160769,161003,161234,161433,161441,161685,161718,161758,161824,161840,161866,168093,168114,168176,168180,168372,168398,168409,168423,168498,168542,168633,168839,168868,168871,169196,169501,169625,169680,169854,170036,170166,170212,170221,170322,170618,170743,170751,170816,170839,171164,171169,172079,172168,172199,172246,172457,172598,173018,173149,173269,173354,173378,173455,173535,173743,173746,173828,173861,174022,174143,174197,174290,174504,174753,174961,175006,175072,175138,175143,175201,175225,175295,175386,175457,175523,175613,175732,175775,175783,175798,175856,176009,176046,176136,176212,176223,176393,176616,176752,177019,177140,177157,177168,177325,177346,177350,177632,177746,177787,177840,177848,177910,178004,178130,178150,178216,178322,178331,178373,178517,178542,178545,178553,178594,178702,178786,179523,179618,179726,179730,179732,179785,179846,180214,180266,180457,180493,180567,180650,180663,180721,180738,180885,180910,180935,180967,180974,181174,181175,181183,181517,181721,181912,181951,182022,182103,182119,182220,182239,182332,182588,182589,182629,182655}'::bigint[]))
 ->  Index Scan using wsddata_code_freq_value__idx on wsddata r
 (cost=0.43..29.14 rows=10 width=18)
   Index Cond: ((code = map.vencode) AND (value_ >= '2015-04-30
00:00:00'::timestamp without time zone) AND (value_ <= '2016-04-30
00:00:00'::timestamp without time zone))
   ->  Index Scan using wsndata_pkey on wsndata n  (cost=0.57..8.38 rows=1
width=18)
 Index Cond: ((code = r.code) AND (item = 3501) AND ((freq)::text =
'A'::text) AND (year_ = r.year_) AND (seq = r.seq))
 Filter: ((r.freq)::text = (CASE freq WHEN 'R'::text THEN
'Q'::character varying ELSE freq END)::text)
(9 rows)

However, when "qai_U0002" executes the query it gets this plan:
Nested Loop  (cost=11.22..22751803.43 rows=1 width=24)
   Join Filter: ((r.code = n.code) AND (r.year_ = n.year_) AND (r.seq =
n.seq) AND ((r.freq)::text = (CASE n.freq WHEN 'R'::text THEN
'Q'::character varying ELSE n.freq END)::text))
   ->  Hash Join  (cost=11.22..266330.48 rows=1 width=30)
 Hash Cond: (r.code = map.vencode)
 ->  Bitmap Heap Scan on wsddata r  (cost=117010.56..260781.62
rows=294289 width=18)
   Recheck Cond: ((value_ >= '2015-04-30 00:00:00'::timestamp
without time zone) AND (value_ <= '2016-04-30 00:00:00'::timestamp without
time zone) AND (item = 5905))
   ->  Bitmap Index Scan on wsddata_code_freq_value__idx
 (cost=0.00..116936.99 rows=294289 width=0)
 Index Cond: ((value_ >= '2015-04-30
00:00:00'::timestamp without time zone) AND (value_ <= '2016-04-30
00:00:00'::timestamp without time zone))
 ->  

[GENERAL] Válasz: [GENERAL] dblink authentication failed

[Attila Kiss]

Yes, I missed that. Thank you, it solved the problem. And I had to edit the 
/etc/hosts file too, to make it sure to find the right host. 





 Eredeti üzenet 
Feladó: Adrian Klaver 
Dátum:2016.06.27. 15:28 (GMT+01:00) 
Címzett: Attila Kiss 
Másolatot kap: pgsql-general@postgresql.org 
Tárgy: Re: [GENERAL] dblink authentication failed 

On 06/27/2016 03:58 AM, Attila Kiss wrote:
> I've got some additional info about the problem.
>
> After several times trying to connect to the remote database, I tried to
> connect to the local database with dblink. I edit the .pgpass file like
> this:
>
> 127.0.0.1:5432:*:postgres:password
>
> 192.168.1.40:5432:*:myusername:mypassword
>
>
>
> Then I tried to connect from local sql server:
>
> postgres=# select dblink_connect_u('con1','hostaddr=127.0.0.1 port=5432
> dbname=postgres user=postgres');
>
> I get the same error like before. But when I edit the .pgpass and change
> 127.0.0.1 to 'localhost', it works even if I try it with the exactly
> same connection string in dblink(hostaddr=127.0.0.1). I don't know what
> is going on in the background, maybe some IP address<->hostname problem?

https://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-CONNSTRING

hostaddr

...


"Note that authentication is likely to fail if host is not the name of 
the server at network address hostaddr. Also, note that host rather than 
hostaddr is used to identify the connection in ~/.pgpass (see Section 
31.15)."




>
>
>
>
>
>
> -eredeti Üzenet-
> *Feladó:*    Kiss Attila 
> *Elküldött:*    Hétfő 2016.06.27 09:42
> *Tárgy:*    [GENERAL] dblink authentication failed
> *Címzett:*    pgsql-general@postgresql.org;
>
> Hello,
>
> I have some problem with using dblink_connect_u() in my function.
> When I provide the password in the connection string, there is no
> problem, but when I don’t, it says:
> ERROR:  could not establish connection
>
> DETAIL:  fe_sendauth: no password supplied
>
> However the .pgpass file in the path /var/lib/postgres/.pgpass does
> contain the proper password.
> I have tried to connect to the remote server with psql from bash
> with the following command:
> # psql –h 192.168.1.40 –U myuser –w remote_db_name
>
> it works fine.
>
> My .pgpass file does have the line:
> 192.168.1.40:5432:*:myuser:mypassword
>
> It seems the psql finds the proper password in the .pgpass file but
> dblink doesn’t.
>
> The local and the remote server are running on Ubuntu server 14.04,
> both of them are PostgreSQL 9.4.
>
> The .pgpass file properties look this:
>
> # ls –la ./.pgpass
> -rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass
>
>
> Any ideas are welcome.
>
> Regards,
>
> Attila
>
>
> 
>
>     Mentes a vírusoktól. www.avast.com
> 
>
>
>


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


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

Re: [GENERAL] Corrupted Dabatabase

[Pau Marc Muñoz Torres]

hello again

 to cancel the execution of the \di i used cntl+c, the output for the cat
 /proc/mounts returns

/dev/mapper/ddf1_4c534920202020201055471147111e78p1 /home
ext3 rw,data=ordered 0 0

i when i say i was indexing a table, i mean that i was executing a "create
index"  command

p

Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/


2016-06-27 16:21 GMT+02:00 Sterpu Victor :

> You probably have a HDD problem.
> Try a "cat /proc/mounts" and see if partition is mounted read only.
>
> -- Original Message --
> From: "Pau Marc Muñoz Torres" 
> To: "pgsql general" 
> Sent: 27/6/2016 4:28:35 PM
> Subject: [GENERAL] Corrupted Dabatabase
>
>
> Hello Everydody,
>
>  My database is corrupted, each time i try to list all the tables in the
> database  i have got the following message
>
> hla2db=> \d
> ERROR:  could not open relation 1663/16385/1255: Read-only file system
>
> I read that my best option is to use my backup. Before using my backup i
> would like to remove all databases in postgresql and create everything
> again from scratch. Unfortunatlly i can't see the list of databases,
> althought i am able to create databse
>
>
>
>  can you suggest what to do? is it a good idea to reinstall postgresql?
>
>
> Pau Marc Muñoz Torres
> skype: pau_marc
> http://www.linkedin.com/in/paumarc
> http://www.researchgate.net/profile/Pau_Marc_Torres3/info/
>
>

Re: [GENERAL] Corrupted Dabatabase

[Adrian Klaver]

On 06/27/2016 07:23 AM, Pau Marc Muñoz Torres wrote:

Please reply to list also.
Ccing list


i did a cancel + c

  i suppose this is the problem


I routinely do that with no problems.

Also elaborate on what you mean by 'indexing the database'?




p

Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/


2016-06-27 16:09 GMT+02:00 Adrian Klaver >:

On 06/27/2016 06:52 AM, Pau Marc Muñoz Torres wrote:

hi

 thank you for your quick answer,


 I  am working with linux. here you have the output of uname --all



Linux XXX at 2.6.18-274.3.1.el5 #1 SMP Tue Sep 6 20:13:52 EDT 2011
x86_64 x86_64 x86_64 GNU/Linux

And the filesystem is ext3

 i was able to work with the database until last week when i was
indexing the database. the problem came when i tried to cancel a
\di command


How did you cancel the command?

Also elaborate on what you mean by 'indexing the database'?


 as far as i know nothing  changed since the last time  i was
working
last week.

p


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/


2016-06-27 15:38 GMT+02:00 Alex Ignatov

>>:


On 27.06.2016 16:28, Pau Marc Muñoz Torres wrote:

Hello Everydody,

 My database is corrupted, each time i try to list all
the tables
in the database  i have got the following message

hla2db=> \d
ERROR:  could not open relation 1663/16385/1255:
Read-only file system

I read that my best option is to use my backup. Before
using my
backup i would like to remove all databases in
postgresql and
create everything again from scratch. Unfortunatlly i
can't see
the list of databases, althought i am able to create databse



 can you suggest what to do? is it a good idea to reinstall
postgresql?


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/


May be you filesystem is mounted read-only after power loss
or some
OS crash?

What OS do you have   - WIndows or some kind of Unix
(Linux,  etc).
Also what  file system  Postgresql data dir is using?

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




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





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


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

Re: [GENERAL] Corrupted Dabatabase

[Sterpu Victor]

You probably have a HDD problem.
Try a "cat /proc/mounts" and see if partition is mounted read only.

-- Original Message --
From: "Pau Marc Muñoz Torres" 
To: "pgsql general" 
Sent: 27/6/2016 4:28:35 PM
Subject: [GENERAL] Corrupted Dabatabase


Hello Everydody,

 My database is corrupted, each time i try to list all the tables in 
the database  i have got the following message


hla2db=> \d
ERROR:  could not open relation 1663/16385/1255: Read-only file system

I read that my best option is to use my backup. Before using my backup 
i would like to remove all databases in postgresql and create 
everything again from scratch. Unfortunatlly i can't see the list of 
databases, althought i am able to create databse




 can you suggest what to do? is it a good idea to reinstall postgresql?


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/

Re: [GENERAL] Corrupted Dabatabase

[Adrian Klaver]

On 06/27/2016 06:52 AM, Pau Marc Muñoz Torres wrote:

hi

 thank you for your quick answer,


 I  am working with linux. here you have the output of uname --all



Linux XXX at 2.6.18-274.3.1.el5 #1 SMP Tue Sep 6 20:13:52 EDT 2011
x86_64 x86_64 x86_64 GNU/Linux

And the filesystem is ext3

 i was able to work with the database until last week when i was
indexing the database. the problem came when i tried to cancel a \di command


How did you cancel the command?

Also elaborate on what you mean by 'indexing the database'?



 as far as i know nothing  changed since the last time  i was working
last week.

p


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/


2016-06-27 15:38 GMT+02:00 Alex Ignatov >:


On 27.06.2016 16:28, Pau Marc Muñoz Torres wrote:

Hello Everydody,

 My database is corrupted, each time i try to list all the tables
in the database  i have got the following message

hla2db=> \d
ERROR:  could not open relation 1663/16385/1255: Read-only file system

I read that my best option is to use my backup. Before using my
backup i would like to remove all databases in postgresql and
create everything again from scratch. Unfortunatlly i can't see
the list of databases, althought i am able to create databse



 can you suggest what to do? is it a good idea to reinstall
postgresql?


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/



May be you filesystem is mounted read-only after power loss or some
OS crash?

What OS do you have   - WIndows or some kind of Unix (Linux,  etc).
Also what  file system  Postgresql data dir is using?

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company





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


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

Re: [GENERAL] Corrupted Dabatabase

[Larry Rosenman]

On 2016-06-27 08:52, Pau Marc Muñoz Torres wrote:

> hi  
> 
> thank you for your quick answer,
> 
> I  am working with linux. here you have the output of uname --all 
> 
> Linux XXX at 2.6.18-274.3.1.el5 #1 SMP Tue Sep 6 20:13:52 EDT 2011 x86_64 
> x86_64 x86_64 GNU/Linux 
> 
> And the filesystem is ext3 
> 
> i was able to work with the database until last week when i was indexing the 
> database. the problem came when i tried to cancel a \di command 
> 
> as far as i know nothing  changed since the last time  i was working last 
> week.

Have you run fsck on the filesystem?  Is there any messages in
/var/log/messages? 

-- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: l...@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

Re: [GENERAL] Corrupted Dabatabase

[Pau Marc Muñoz Torres]

hi

 thank you for your quick answer,


 I  am working with linux. here you have the output of uname --all



Linux XXX at 2.6.18-274.3.1.el5 #1 SMP Tue Sep 6 20:13:52 EDT 2011 x86_64
x86_64 x86_64 GNU/Linux

And the filesystem is ext3

 i was able to work with the database until last week when i was indexing
the database. the problem came when i tried to cancel a \di command

 as far as i know nothing  changed since the last time  i was working last
week.

p


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/


2016-06-27 15:38 GMT+02:00 Alex Ignatov :

>
> On 27.06.2016 16:28, Pau Marc Muñoz Torres wrote:
>
> Hello Everydody,
>
>  My database is corrupted, each time i try to list all the tables in the
> database  i have got the following message
>
> hla2db=> \d
> ERROR:  could not open relation 1663/16385/1255: Read-only file system
>
> I read that my best option is to use my backup. Before using my backup i
> would like to remove all databases in postgresql and create everything
> again from scratch. Unfortunatlly i can't see the list of databases,
> althought i am able to create databse
>
>
>
>  can you suggest what to do? is it a good idea to reinstall postgresql?
>
>
> Pau Marc Muñoz Torres
> skype: pau_marc
> http://www.linkedin.com/in/paumarc
> http://www.researchgate.net/profile/Pau_Marc_Torres3/info/
>
>
> May be you filesystem is mounted read-only after power loss or some OS
> crash?
>
> What OS do you have   - WIndows or some kind of Unix (Linux,  etc). Also
> what  file system  Postgresql data dir is using?
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

Re: [GENERAL] Corrupted Dabatabase

[Alex Ignatov]

On 27.06.2016 16:28, Pau Marc Muñoz Torres wrote:

Hello Everydody,

 My database is corrupted, each time i try to list all the tables in 
the database  i have got the following message


hla2db=> \d
ERROR:  could not open relation 1663/16385/1255: Read-only file system

I read that my best option is to use my backup. Before using my backup 
i would like to remove all databases in postgresql and create 
everything again from scratch. Unfortunatlly i can't see the list of 
databases, althought i am able to create databse




 can you suggest what to do? is it a good idea to reinstall postgresql?


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/



May be you filesystem is mounted read-only after power loss or some OS 
crash?


What OS do you have   - WIndows or some kind of Unix (Linux, etc). Also 
what  file system  Postgresql data dir is using?


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [GENERAL] Corrupted Dabatabase

[Tim Clarke]

The issue sounds more like your server than just Postgres - search the
net for "read only file system" for your OS.

Tim Clarke

On 27/06/16 14:28, Pau Marc Muñoz Torres wrote:
> Hello Everydody, 
>
>  My database is corrupted, each time i try to list all the tables in
> the database  i have got the following message
>
> hla2db=> \d
> ERROR:  could not open relation 1663/16385/1255: Read-only file system
>
> I read that my best option is to use my backup. Before using my backup
> i would like to remove all databases in postgresql and create
> everything again from scratch. Unfortunatlly i can't see the list of
> databases, althought i am able to create databse
>
>
>
>  can you suggest what to do? is it a good idea to reinstall postgresql?
>
>
> Pau Marc Muñoz Torres
> skype: pau_marc
> http://www.linkedin.com/in/paumarc
> http://www.researchgate.net/profile/Pau_Marc_Torres3/info/
>




smime.p7s
Description: S/MIME Cryptographic Signature

[GENERAL] Corrupted Dabatabase

[Pau Marc Muñoz Torres]

Hello Everydody,

 My database is corrupted, each time i try to list all the tables in the
database  i have got the following message

hla2db=> \d
ERROR:  could not open relation 1663/16385/1255: Read-only file system

I read that my best option is to use my backup. Before using my backup i
would like to remove all databases in postgresql and create everything
again from scratch. Unfortunatlly i can't see the list of databases,
althought i am able to create databse



 can you suggest what to do? is it a good idea to reinstall postgresql?


Pau Marc Muñoz Torres
skype: pau_marc
http://www.linkedin.com/in/paumarc
http://www.researchgate.net/profile/Pau_Marc_Torres3/info/

Re: [GENERAL] dblink authentication failed

[Adrian Klaver]

On 06/27/2016 03:58 AM, Attila Kiss wrote:

I've got some additional info about the problem.

After several times trying to connect to the remote database, I tried to
connect to the local database with dblink. I edit the .pgpass file like
this:

127.0.0.1:5432:*:postgres:password

192.168.1.40:5432:*:myusername:mypassword



Then I tried to connect from local sql server:

postgres=# select dblink_connect_u('con1','hostaddr=127.0.0.1 port=5432
dbname=postgres user=postgres');

I get the same error like before. But when I edit the .pgpass and change
127.0.0.1 to 'localhost', it works even if I try it with the exactly
same connection string in dblink(hostaddr=127.0.0.1). I don't know what
is going on in the background, maybe some IP address<->hostname problem?


https://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-CONNSTRING

hostaddr

...


"Note that authentication is likely to fail if host is not the name of 
the server at network address hostaddr. Also, note that host rather than 
hostaddr is used to identify the connection in ~/.pgpass (see Section 
31.15)."












-eredeti Üzenet-
*Feladó:*   Kiss Attila 
*Elküldött:*Hétfő 2016.06.27 09:42
*Tárgy:*[GENERAL] dblink authentication failed
*Címzett:*  pgsql-general@postgresql.org;

Hello,

I have some problem with using dblink_connect_u() in my function.
When I provide the password in the connection string, there is no
problem, but when I don’t, it says:
ERROR:  could not establish connection

DETAIL:  fe_sendauth: no password supplied

However the .pgpass file in the path /var/lib/postgres/.pgpass does
contain the proper password.
I have tried to connect to the remote server with psql from bash
with the following command:
# psql –h 192.168.1.40 –U myuser –w remote_db_name

it works fine.

My .pgpass file does have the line:
192.168.1.40:5432:*:myuser:mypassword

It seems the psql finds the proper password in the .pgpass file but
dblink doesn’t.

The local and the remote server are running on Ubuntu server 14.04,
both of them are PostgreSQL 9.4.

The .pgpass file properties look this:

# ls –la ./.pgpass
-rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass


Any ideas are welcome.

Regards,

Attila




Mentes a vírusoktól. www.avast.com







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


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

Re: [GENERAL] Updated RUM?

[Andreas Joseph Krogh]

På mandag 27. juni 2016 kl. 13:20:03, skrev Oleg Bartunov >:
    On Fri, Jun 24, 2016 at 2:36 PM, Andreas Joseph Krogh > wrote: Hi.
 
@PostgresPro; Will there be any updates to the rum git-repo any time soon?
 
Sure.
  
 
I understand you're working hard on this, care to share some vision/roadmap? 
I'm specifically interested in if RUM will support the same datatypes as GIN 
supports (arrays, JSON etc.), and what opclasses you plan to implement (for 
equalty and sorting). Will there be some kind of btree_rum like there is for 
btree_gin, to add RUM-support for the types there exists Btree-support for?
 
We are testing fts+timestamp combination right now.  The challenge is to have 
posting list/tree sorted by addinfo (timestamp) not by item pointer. It works 
already, but we are not quite satisfied with generated WAL traffic and are 
researching some options in improving posting tree building and generic wal.
  
We have in mind some sort of generalization like anyarray, but don't have any 
real ideas.



 
Cool!
 
Will RUM support having ts_vector and bigint in the same index, like GIN does 
(utilizine btree_gin), then sorting by some 3rd timestamp-column?
 
This will enable queries like:
 
create table delivery( id BIGSERIAL primary key, fts_all TSVECTOR not null, 
folder_idBIGINT NOT NULL, sent TIMESTAMP not null, message varchar not null ); 
 
SELECT del.id  , del.sent FROM delivery del WHERE del.fts_all 
@@to_tsquery('simple', 'hi:*') AND del.folder_id = ANY(ARRAY[2,3]::BIGINT[]) 
ORDER BYdel.sent DESC LIMIT 101 OFFSET 0; Having this query use one RUM-index 
with optimized sorting by timestamp would be extremely useful.

 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Updated RUM?

[Oleg Bartunov]

On Fri, Jun 24, 2016 at 2:36 PM, Andreas Joseph Krogh 
wrote:

> Hi.
>
> @PostgresPro; Will there be any updates to the rum git-repo any time soon?
>

Sure.


>
> I understand you're working hard on this, care to share some
> vision/roadmap? I'm specifically interested in if RUM will support the same
> datatypes as GIN supports (arrays, JSON etc.), and what opclasses you plan
> to implement (for equalty and sorting). Will there be some kind of
> btree_rum like there is for btree_gin, to add RUM-support for the types
> there exists Btree-support for?
>

We are testing fts+timestamp combination right now.  The challenge is to
have posting list/tree sorted by addinfo (timestamp) not by item pointer.
It works already, but we are not quite satisfied with generated WAL traffic
and are researching some options in improving posting tree building and
generic wal.

We have in mind some sort of generalization like anyarray, but don't have
any real ideas.



>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>

Re: [GENERAL] dblink authentication failed

[Attila Kiss]

I've got some additional info about the problem.

After several times trying to connect to the remote database, I tried to 
connect to the local database with dblink. I edit the .pgpass file like this:

127.0.0.1:5432:*:postgres:password

192.168.1.40:5432:*:myusername:mypassword

 
Then I tried to connect from local sql server:

postgres=# select dblink_connect_u('con1','hostaddr=127.0.0.1 port=5432 
dbname=postgres user=postgres');

I get the same error like before. But when I edit the .pgpass and change 
127.0.0.1 to 'localhost', it works even if I try it with the exactly same 
connection string in dblink(hostaddr=127.0.0.1). I don't know what is going on 
in the background, maybe some IP address<->hostname problem?

 

 
-eredeti Üzenet-
Feladó:Kiss Attila 
Elküldött:Hétfő 2016.06.27 09:42
Tárgy:[GENERAL] dblink authentication failed
Címzett:pgsql-general@postgresql.org; 
 
Hello,

I have some problem with using dblink_connect_u() in my function. 
When I provide the password in the connection string, there is no problem, but 
when I don’t, it says:
ERROR:  could not establish connection

DETAIL:  fe_sendauth: no password supplied

However the .pgpass file in the path /var/lib/postgres/.pgpass does contain the 
proper password.
I have tried to connect to the remote server with psql from bash with the 
following command:
# psql –h 192.168.1.40 –U myuser –w remote_db_name

it works fine.

My .pgpass file does have the line:
192.168.1.40:5432:*:myuser:mypassword

It seems the psql finds the proper password in the .pgpass file but dblink 
doesn’t.

The local and the remote server are running on Ubuntu server 14.04, both of 
them are PostgreSQL 9.4.

The .pgpass file properties look this:

# ls –la ./.pgpass
-rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass


Any ideas are welcome.

Regards,

Attila


 
Mentes a vírusoktól. www.avast.com 
 

Re: [GENERAL] dblink authentication failed

[Attila Kiss]

I just wrote '#' to show it is command line, but actually I ran the psql as 
user postgres, just like the SQL server process is running. So if it was 
deceptive, I am sorry. 
 
 

 
-eredeti Üzenet-
Feladó:Francisco Olarte 
Elküldött:Hétfő 2016.06.27 11:23
Tárgy:Re: [GENERAL] dblink authentication failed
Címzett:Attila Kiss ; 
Másolatot kap:pgsql-general@postgresql.org; 
Hello:

On Mon, Jun 27, 2016 at 9:38 AM, Kiss Attila  wrote:
I'm not expert on dblink but, even supposing it uses libpq and its files:

> # psql –h 192.168.1.40 –U myuser –w remote_db_name
...
> # ls –la ./.pgpass
> -rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass

This '#' seems to indicate you run those commands as root, while the
server typically runs as postgres. Have you checked the commands work
when issued as the server user?

Francisco Olarte.


-- 
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] dblink authentication failed

[Francisco Olarte]

Hello:

On Mon, Jun 27, 2016 at 9:38 AM, Kiss Attila  wrote:
I'm not expert on dblink but, even supposing it uses libpq and its files:

> # psql –h 192.168.1.40 –U myuser –w remote_db_name
...
> # ls –la ./.pgpass
> -rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass

This '#' seems to indicate you run those commands as root, while the
server typically runs as postgres. Have you checked the commands work
when issued as the server user?

Francisco Olarte.


-- 
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] dblink authentication failed

[Scott Mead]

> On Jun 27, 2016, at 03:38, Kiss Attila  wrote:
> 
> Hello,
> I have some problem with using dblink_connect_u() in my function. 
> When I provide the password in the connection string, there is no problem, 
> but when I don’t, it says:
> ERROR:  could not establish connection
> DETAIL:  fe_sendauth: no password supplied
> However the .pgpass file in the path /var/lib/postgres/.pgpass does contain 
> the proper password.
> I have tried to connect to the remote server with psql from bash with the 
> following command:
> # psql –h 192.168.1.40 –U myuser –w remote_db_name
> it works fine.
> My .pgpass file does have the line:
> 192.168.1.40:5432:*:myuser:mypassword
> It seems the psql finds the proper password in the .pgpass file but dblink 
> doesn’t.

When dblink makes the connection,  the client is actually the dbserver itself. 
This means that your .pgpass file needs to be setup in the database server as 
the user running the postgres process. 

> The local and the remote server are running on Ubuntu server 14.04, both of 
> them are PostgreSQL 9.4.
> The .pgpass file properties look this:
> # ls –la ./.pgpass
> -rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass
> 
> Any ideas are welcome.
> Regards,
> Attila
> 
>   Mentes a vírusoktól. www.avast.com

[GENERAL] dblink authentication failed

[Kiss Attila]

Hello,

I have some problem with using dblink_connect_u() in my function.
When I provide the password in the connection string, there is no problem,
but when I don't, it says:
ERROR:  could not establish connection

DETAIL:  fe_sendauth: no password supplied

However the .pgpass file in the path /var/lib/postgres/.pgpass does contain
the proper password.
I have tried to connect to the remote server with psql from bash with the
following command:
# psql -h 192.168.1.40 -U myuser -w remote_db_name

it works fine.

My .pgpass file does have the line:
192.168.1.40:5432:*:myuser:mypassword

It seems the psql finds the proper password in the .pgpass file but dblink
doesn't.

The local and the remote server are running on Ubuntu server 14.04, both of
them are PostgreSQL 9.4.

The .pgpass file properties look this:

# ls -la ./.pgpass
-rw--- 1 postgres postgres 193 jún   24 13:54 ./.pgpass


Any ideas are welcome.

Regards,

Attila



---
Ezt az e-mailt az Avast víruskereső szoftver átvizsgálta.
https://www.avast.com/antivirus