Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension

[Arun Rangarajan]

Thanks again, Scott. No success yet though.

I uninstalled Oracle instant client 12.1 using dpkg, downloaded RPMs for
instant client 10.2 and installed them with alien.

I also downloaded oracle_fdw 1.4 and installed it, since I was getting this
error with oracle_fdw 1.5:
---
postgres=# create extension oracle_fdw;
ERROR:  could not load library "/usr/lib/postgresql/9.4/lib/oracle_fdw.so":
libclntsh.so.12.1: cannot open shared object file: No such file or directory
---

Once I installed oracle_fdw 1.4 I got this:

# ldd /usr/lib/postgresql/9.4/lib/oracle_fdw.so
linux-vdso.so.1 =>  (0x7ffc3b5ec000)
libclntsh.so.10.1 => /usr/lib/oracle/10.2.0.3/client64/lib/libclntsh.so.10.1
(0x7f7251471000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f72510e6000)
libnnz10.so => /usr/lib/oracle/10.2.0.3/client64/lib/libnnz10.so
(0x7f7250c42000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f7250a3e000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f72507bc000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x7f725059f000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f7250387000)
/lib64/ld-linux-x86-64.so.2 (0x7f7252a3b000)

But again when I try to load the extension I get the same error and
Postgres crashes. When I look in the latest log file at
/var/lib/postgresql/9.4/main/pg_log I see these lines:

---
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG:  0: server process (PID
20397) was terminated by signal 11: Segmentation fault
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=DETAIL:  Failed process was
running: create extension oracle_fdw;
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOCATION:  LogChildExit,
postmaster.c:3347
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOG:  0: terminating any
other active server processes
t=2016-09-15 16:16:08 PDT d= h= p=25327 a=LOCATION:  HandleChildCrash,
postmaster.c:3068
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] WARNING:
 57P02: terminating connection because of crash of another server process
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] DETAIL:
 The postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] HINT:  In
a moment you should be able to reconnect to the database and repeat your
command.
t=2016-09-15 16:16:08 PDT d=svp2 h=127.0.0.1 p=19672 a=[unknown] LOCATION:
 quickdie, postgres.c:2581
t=2016-09-15 16:16:08 PDT d= h= p=19668 a=WARNING:  57P02: terminating
connection because of crash of another server process
---




On Thu, Sep 15, 2016 at 2:58 PM, Scott Mead  wrote:

>
>
> On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan  > wrote:
>
>> Thanks, Scott.
>>
>> oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/
>>
>> Oracle client version: instantclient 12.1
>>
>
> I've had problems using anything > instant client 10.  Give it a shot.
>
> --Scott
>
>
>
>>
>> /usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so
>> linux-vdso.so.1 =>  (0x7fff50744000)
>> libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
>> (0x7f44769f1000)
>> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f447000)
>> libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so
>> (0x7f4475f4f000)
>> libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so
>> (0x7f4475d0b000)
>> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f4475b07000)
>> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f4475884000)
>> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
>> (0x7f4475668000)
>> libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f447545)
>> librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x7f4475247000)
>> libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x7f4475045000)
>> libclntshcore.so.12.1 => 
>> /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1
>> (0x7f4474af5000)
>> /lib64/ld-linux-x86-64.so.2 (0x7f447990c000)
>>
>>
>>
>> On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead  wrote:
>>
>>>
>>>
>>> On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <
>>> arunrangara...@gmail.com> wrote:
>>>
 I am following the instructions here:
 http://blog.dbi-services.com/connecting-your-postgresql-inst
 ance-to-an-oracle-database/
 to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL
 server.

 ---
 Oracle version: Oracle Database 12c Enterprise Edition Release
 12.1.0.2.0 - 64bit Production, running on Red Hat Linux 7.2

 PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu,
 compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7
 (wheezy).
 ---

 I was able to install sqlplus and connect from PostgreSQL server to
 Oracle server using sqlplus successfully, so 

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

[Patrick B]

>
>
>>
>>
>
> Please have a look on this example Patrick: http://sqlfiddle.com/#!15/
> 1773d/4
>
> Lucas
>
>

This helped a lot... it's working now :)  Thanks guys!!!

Patrick

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

[Chris]

On 16/09/16 08:07, Patrick B wrote:
> 
> 
> A select can make up columns too, not just what you've got in a table,
> so you can:
> 
> select j_id, 'test-1 - comments' as comment from test2 where
> customer_id=88897;
> 
> and then you can simply insert that into your other table (you don't
> need to specify the columns that are getting a default value):
> 
> insert into test1 (j_id, comments)
> select j_id, 'test-1 - comments' as comment from test2 where
> customer_id=88897;
> 
> https://www.postgresql.org/docs/current/static/sql-insert.html
>  has
> more
> info.
> 
> 
> Thanks Chris!
> 
> But the problem is that test2 table has 180 rows with different j_id and
> I need to insert each one of them into test1 table.
> 
> How can I do that? 
> select j_id FROM test2 - will return 180 rows
> 

If your select returns 180 rows, then an `insert into select` query
would insert 180 rows (assuming other constraints like primary / unique
keys are met).

-- 
Postgresql & php tutorials
http://www.designmagick.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] postgres insert + select + values - Pgsql 9.5

[Lucas Possamai]

2016-09-16 10:07 GMT+12:00 Patrick B :

>
>>
>> A select can make up columns too, not just what you've got in a table,
>> so you can:
>>
>> select j_id, 'test-1 - comments' as comment from test2 where
>> customer_id=88897;
>>
>> and then you can simply insert that into your other table (you don't
>> need to specify the columns that are getting a default value):
>>
>> insert into test1 (j_id, comments)
>> select j_id, 'test-1 - comments' as comment from test2 where
>> customer_id=88897;
>>
>> https://www.postgresql.org/docs/current/static/sql-insert.html has more
>> info.
>>
>>
> Thanks Chris!
>
> But the problem is that test2 table has 180 rows with different j_id and I
> need to insert each one of them into test1 table.
>
> How can I do that?
> select j_id FROM test2 - will return 180 rows
>
>

Please have a look on this example Patrick:
http://sqlfiddle.com/#!15/1773d/4

Lucas

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

[Patrick B]

>
>
>
> A select can make up columns too, not just what you've got in a table,
> so you can:
>
> select j_id, 'test-1 - comments' as comment from test2 where
> customer_id=88897;
>
> and then you can simply insert that into your other table (you don't
> need to specify the columns that are getting a default value):
>
> insert into test1 (j_id, comments)
> select j_id, 'test-1 - comments' as comment from test2 where
> customer_id=88897;
>
> https://www.postgresql.org/docs/current/static/sql-insert.html has more
> info.
>
>
Thanks Chris!

But the problem is that test2 table has 180 rows with different j_id and I
need to insert each one of them into test1 table.

How can I do that?
select j_id FROM test2 - will return 180 rows

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

[Chris]

On 16/09/16 07:45, Patrick B wrote:
> Hi guys,
> 
> 
> I got the test1 table with three columns:
> 
> id(BIGINT) - j_id(BIGINT) - comments(CHARACTER VARYING)
> 
> 
> *This needs to be done 180 times:*
> 
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123321,'test-1
> - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123322,'test-1
> - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123323,'test-1
> - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123324,'test-1
> - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123325,'test-1
> - comments');
> 
> 
> *However, I have to get j_id from another table:*
> 
> select j_id from test2 WHERE customer_id = 88897
> 
> 
> 17012651
> 17012601
> 16623374
> 16808986
> 16843879
> 16808794
> 16870199
> 16870488
> 16870443
> 16870403 
> 
> 
> 
> *Example of what I need:*
> 
> INSERT INTO test1 (id,j_id,comments) VALUES
> (default, 17012651,'test-1 - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES
> (default, 17012601,'test-1 - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES
> (default, 16623374,'test-1 - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES
> (default, 16808986,'test-1 - comments');
> INSERT INTO test1 (id,j_id,comments) VALUES
> (default, 16843879,'test-1 - comments');
> 
> 
> etc...
> 
> How can I do that? If the customer_id is the same for all those 180 rows?

A select can make up columns too, not just what you've got in a table,
so you can:

select j_id, 'test-1 - comments' as comment from test2 where
customer_id=88897;

and then you can simply insert that into your other table (you don't
need to specify the columns that are getting a default value):

insert into test1 (j_id, comments)
select j_id, 'test-1 - comments' as comment from test2 where
customer_id=88897;

https://www.postgresql.org/docs/current/static/sql-insert.html has more
info.

-- 
Postgresql & php tutorials
http://www.designmagick.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] Unable to create oracle_fdw (foreign data wrapper) extension

[Scott Mead]

On Thu, Sep 15, 2016 at 4:57 PM, Arun Rangarajan 
wrote:

> Thanks, Scott.
>
> oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/
>
> Oracle client version: instantclient 12.1
>

I've had problems using anything > instant client 10.  Give it a shot.

--Scott



>
> /usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so
> linux-vdso.so.1 =>  (0x7fff50744000)
> libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
> (0x7f44769f1000)
> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f447000)
> libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so
> (0x7f4475f4f000)
> libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so
> (0x7f4475d0b000)
> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f4475b07000)
> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f4475884000)
> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
> (0x7f4475668000)
> libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f447545)
> librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x7f4475247000)
> libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x7f4475045000)
> libclntshcore.so.12.1 => 
> /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1
> (0x7f4474af5000)
> /lib64/ld-linux-x86-64.so.2 (0x7f447990c000)
>
>
>
> On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead  wrote:
>
>>
>>
>> On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <
>> arunrangara...@gmail.com> wrote:
>>
>>> I am following the instructions here:
>>> http://blog.dbi-services.com/connecting-your-postgresql-inst
>>> ance-to-an-oracle-database/
>>> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL
>>> server.
>>>
>>> ---
>>> Oracle version: Oracle Database 12c Enterprise Edition Release
>>> 12.1.0.2.0 - 64bit Production, running on Red Hat Linux 7.2
>>>
>>> PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu,
>>> compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7
>>> (wheezy).
>>> ---
>>>
>>> I was able to install sqlplus and connect from PostgreSQL server to
>>> Oracle server using sqlplus successfully, so connectivity is not a problem.
>>>
>>> But when I try to create the extension, I get the following error:
>>>
>>>
>> ---
>>> postgres=# create extension oracle_fdw;
>>> server closed the connection unexpectedly
>>> This probably means the server terminated abnormally
>>> before or while processing the request.
>>> The connection to the server was lost. Attempting reset: Failed.
>>>
>>
>> Hmm, odd that it's causing a crash.
>>
>>   Which version of the oracle_fdw and which version of the oracle
>> libraries are you linked to?  Make sure to check 'ldd oracle_fdw.so'
>>
>> --Scott
>>
>>
>>
>>> ---
>>>
>>> Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1
>>> and added oracle_fdw to shared_preload_libraries in postgresql.conf like
>>> this:
>>>
>>> shared_preload_libraries = 'oracle_fdw'
>>>
>>> but now I can't restart Postgres:
>>>
>>> ---
>>> # service postgresql restart
>>> [] Restarting PostgreSQL 9.4 database server: main[] The
>>> PostgreSQL server failed to start. Please check the log output:
>>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache
>>> ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:
>>> SearchSysCacheList, syscache.c:1219 ... failed!
>>>  failed!
>>> ---
>>>
>>> Looking into /var/log/postgresql/postgresql-9.4-main.log I only see
>>> these two lines:
>>>
>>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL:  XX000: invalid cache
>>> ID: 41
>>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:  SearchSysCacheList,
>>> syscache.c:1219
>>>
>>> Removing oracle_fdw from shared_preload_libraries allows postgres to be
>>> restarted, so this is the one causing restart to fail.
>>>
>>> How to fix this and get the foreign data wrapper working?
>>>
>>> Thank you.
>>>
>>> ​
>>>
>>
>>
>>
>> --
>> --
>> Scott Mead
>> Sr. Architect
>> *OpenSCG *
>> http://openscg.com
>>
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

[GENERAL] postgres insert + select + values - Pgsql 9.5

[Patrick B]

Hi guys,


I got the test1 table with three columns:

id(BIGINT) - j_id(BIGINT) - comments(CHARACTER VARYING)


*This needs to be done 180 times:*

> INSERT INTO test1 (id,j_id,comments) VALUES (default,123321,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123322,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123323,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123324,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default,123325,'test-1 -
> comments');


*However, I have to get j_id from another table:*

> select j_id from test2 WHERE customer_id = 88897


17012651
> 17012601
> 16623374
> 16808986
> 16843879
> 16808794
> 16870199
> 16870488
> 16870443
> 16870403



*Example of what I need:*

INSERT INTO test1 (id,j_id,comments) VALUES (default, 17012651,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default, 17012601,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default, 16623374,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default, 16808986,'test-1 -
> comments');
> INSERT INTO test1 (id,j_id,comments) VALUES (default, 16843879,'test-1 -
> comments');


etc...

How can I do that? If the customer_id is the same for all those 180 rows?

Cheers
Patrick

Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension

[Arun Rangarajan]

Thanks, Scott.

oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/

Oracle client version: instantclient 12.1

/usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so
linux-vdso.so.1 =>  (0x7fff50744000)
libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
(0x7f44769f1000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f447000)
libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so
(0x7f4475f4f000)
libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so
(0x7f4475d0b000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x7f4475b07000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f4475884000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x7f4475668000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x7f447545)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x7f4475247000)
libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x7f4475045000)
libclntshcore.so.12.1 =>
/usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x7f4474af5000)
/lib64/ld-linux-x86-64.so.2 (0x7f447990c000)



On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead  wrote:

>
>
> On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan  > wrote:
>
>> I am following the instructions here:
>> http://blog.dbi-services.com/connecting-your-postgresql-inst
>> ance-to-an-oracle-database/
>> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL
>> server.
>>
>> ---
>> Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
>> - 64bit Production, running on Red Hat Linux 7.2
>>
>> PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu,
>> compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7
>> (wheezy).
>> ---
>>
>> I was able to install sqlplus and connect from PostgreSQL server to
>> Oracle server using sqlplus successfully, so connectivity is not a problem.
>>
>> But when I try to create the extension, I get the following error:
>>
>>
> ---
>> postgres=# create extension oracle_fdw;
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>>
>
> Hmm, odd that it's causing a crash.
>
>   Which version of the oracle_fdw and which version of the oracle
> libraries are you linked to?  Make sure to check 'ldd oracle_fdw.so'
>
> --Scott
>
>
>
>> ---
>>
>> Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1
>> and added oracle_fdw to shared_preload_libraries in postgresql.conf like
>> this:
>>
>> shared_preload_libraries = 'oracle_fdw'
>>
>> but now I can't restart Postgres:
>>
>> ---
>> # service postgresql restart
>> [] Restarting PostgreSQL 9.4 database server: main[] The
>> PostgreSQL server failed to start. Please check the log output:
>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache
>> ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:
>> SearchSysCacheList, syscache.c:1219 ... failed!
>>  failed!
>> ---
>>
>> Looking into /var/log/postgresql/postgresql-9.4-main.log I only see
>> these two lines:
>>
>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL:  XX000: invalid cache
>> ID: 41
>> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:  SearchSysCacheList,
>> syscache.c:1219
>>
>> Removing oracle_fdw from shared_preload_libraries allows postgres to be
>> restarted, so this is the one causing restart to fail.
>>
>> How to fix this and get the foreign data wrapper working?
>>
>> Thank you.
>>
>> ​
>>
>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
> http://openscg.com
>

Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension

[Scott Mead]

On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan 
wrote:

> I am following the instructions here:
> http://blog.dbi-services.com/connecting-your-postgresql-
> instance-to-an-oracle-database/
> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL server.
>
> ---
> Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
> - 64bit Production, running on Red Hat Linux 7.2
>
> PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled
> by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7 (wheezy).
> ---
>
> I was able to install sqlplus and connect from PostgreSQL server to Oracle
> server using sqlplus successfully, so connectivity is not a problem.
>
> But when I try to create the extension, I get the following error:
>
>
---
> postgres=# create extension oracle_fdw;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>

Hmm, odd that it's causing a crash.

  Which version of the oracle_fdw and which version of the oracle libraries
are you linked to?  Make sure to check 'ldd oracle_fdw.so'

--Scott



> ---
>
> Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1
> and added oracle_fdw to shared_preload_libraries in postgresql.conf like
> this:
>
> shared_preload_libraries = 'oracle_fdw'
>
> but now I can't restart Postgres:
>
> ---
> # service postgresql restart
> [] Restarting PostgreSQL 9.4 database server: main[] The
> PostgreSQL server failed to start. Please check the log output:
> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache
> ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:
> SearchSysCacheList, syscache.c:1219 ... failed!
>  failed!
> ---
>
> Looking into /var/log/postgresql/postgresql-9.4-main.log I only see these
> two lines:
>
> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL:  XX000: invalid cache ID:
> 41
> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:  SearchSysCacheList,
> syscache.c:1219
>
> Removing oracle_fdw from shared_preload_libraries allows postgres to be
> restarted, so this is the one causing restart to fail.
>
> How to fix this and get the foreign data wrapper working?
>
> Thank you.
>
> ​
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] PostgreSQL GIN index not used when ts_query language is fetched from a column

[Jeff Janes]

On Thu, Sep 15, 2016 at 5:46 AM, Jaap Roes  wrote:

> I've got a table that stores some multilingual content:
>
> CREATE TABLE search (
>   content text NOT NULL,
>   language regconfig NOT NULL,
>   fulltext tsvector
> );
> CREATE INDEX search_fulltext ON search USING GIN(fulltext);
>
> INSERT INTO search (language, content) VALUES
>   ('dutch', 'Als achter vliegen vliegen vliegen vliegen vliegen
> vliegen achterna'),
>   ('dutch', 'Langs de koele kali liep een kale koeli met een kilo kali
> op zijn kale koeli-kop.'),
>   ('dutch', 'Moeder sneed zeven scheve sneden brood'),
>   ('english', 'I saw Susie sitting in a shoe shine shop. Where she
> sits she shines, and where she shines she sits.'),
>   ('english', 'How can a clam cram in a clean cream can?'),
>   ('english', 'Can you can a can as a canner can can a can?');
>
> UPDATE search SET fulltext = to_tsvector(language, content);
>
> To make sure I always search in the correct language I use these queries:
>
> SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
> (1 row)
>

I don't think that what you want is possible to do in a clean way.  It
can't know what the language for a row is until it finds the row, but it
can't find the row using an index until it runs to_tsquery, and it can't
run to_tsquery until it knows the language.

You would probably need to run the query once for each language, and filter
out the results which it found under the 'wrong' language.

SELECT * FROM search WHERE fulltext @@ to_tsquery('dutch', 'shine') and
language = 'dutch'::regconfig
union all
SELECT * FROM search WHERE fulltext @@ to_tsquery('english', 'shine') and
language = 'english'::regconfig;

If you have more than two languages, this will become tedious, and perhaps
non-performant.  I am not aware of a more general solution, though.

It might help to build partial indexes on each language.

Cheers,

Jeff

Re: [GENERAL] PgSQL versions supported on ubuntu 16 and debian 8

[John R Pierce]

On 9/15/2016 12:40 AM, MEERA wrote:


Could someone share below information?

- PgSQL versions supported on ubuntu 16
- PgSQL versions supported on debian 8



any current PG version (thats 9.1.latest (likely to be deprecated soon 
when 9.6 releases) to 9.5.latest) can be run on most any version of linux.


'supported' is subject to interpretation.  if you mean 'supported' in 
the business sense, you should talk with your support vendor about what 
/they/ want to support.   the community here (thats volunteer folks like 
me, although I'm not on any of the core teams), will endeavor to offer 
help with any configuration thats not too insane.




--
john r pierce, recycling bits in santa cruz



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

[GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension

[Arun Rangarajan]

I am following the instructions here:
http://blog.dbi-services.com/connecting-your-postgresql-instance-to-an-oracle-database/
to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL server.

---
Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production, running on Red Hat Linux 7.2

PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled
by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7 (wheezy).
---

I was able to install sqlplus and connect from PostgreSQL server to Oracle
server using sqlplus successfully, so connectivity is not a problem.

But when I try to create the extension, I get the following error:

---
postgres=# create extension oracle_fdw;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
---

Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1 and
added oracle_fdw to shared_preload_libraries in postgresql.conf like this:

shared_preload_libraries = 'oracle_fdw'

but now I can't restart Postgres:

---
# service postgresql restart
[] Restarting PostgreSQL 9.4 database server: main[] The PostgreSQL
server failed to start. Please check the log output: t=2016-09-15 11:05:42
PDT d= h= p=23300 a=FATAL: XX000: invalid cache ID[FAILt=2016-09-15
11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList, syscache.c:1219
... failed!
 failed!
---

Looking into /var/log/postgresql/postgresql-9.4-main.log I only see these
two lines:

t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL:  XX000: invalid cache ID:
41
t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:  SearchSysCacheList,
syscache.c:1219

Removing oracle_fdw from shared_preload_libraries allows postgres to be
restarted, so this is the one causing restart to fail.

How to fix this and get the foreign data wrapper working?

Thank you.

​

Re: [GENERAL] PostgreSQL GIN index not used when ts_query language is fetched from a column

[Jaap Roes]

> On 15 Sep 2016, at 14:46, Jaap Roes  wrote:
> 
> I've got a table that stores some multilingual content:
> 
>CREATE TABLE search (
>  content text NOT NULL,
>  language regconfig NOT NULL,
>  fulltext tsvector
>);
>CREATE INDEX search_fulltext ON search USING GIN(fulltext);
...
> To make sure I always search in the correct language I use these queries:
> 
>SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
>(1 row)
> 
>SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'vlieg');
>(1 row)
> 
> Because hardcoding the language doesn't give the correct results:
> 
>SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'shine');
>(0 rows)
> 
>SELECT FROM search WHERE fulltext @@ to_tsquery('english', 'vlieg');
>(0 rows)
> 
> The problem however is that PostgreSQL doesn't use the GIN index when using 
> the first set of queries and instead does a sequential scan:
...
>EXPLAIN ANALYZE SELECT * FROM search WHERE fulltext @@ 
> to_tsquery(language, 'shine’);
> 
>Seq Scan on search  (cost=0.00..17.35 rows=2 width=136) (actual 
> time=0.040..0.044 rows=1 loops=1)
>Filter: (fulltext @@ to_tsquery(language, 'shine'::text))
>Rows Removed by Filter: 5
>Planning time: 0.039 ms
>Execution time: 0.064 ms
> 
> While it does when hardcoding a language:
> 
>EXPLAIN ANALYZE SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 
> 'vlieg');
>Bitmap Heap Scan on search  (cost=12.63..23.66 rows=82 width=0) (actual 
> time=0.044..0.044 rows=1 loops=1)
>  Recheck Cond: (fulltext @@ '''vlieg'''::tsquery)
>  Heap Blocks: exact=1
>->  Bitmap Index Scan on search_fulltext  (cost=0.00..12.61 rows=82 
> width=0) (actual time=0.037..0.037 rows=1 loops=1)
>  Index Cond: (fulltext @@ '''vlieg'''::tsquery)
>Planning time: 0.128 ms
>Execution time: 0.065 ms
> 
> So my question is: Is it at all possible to use a column in the ts_query to 
> use the correct language config and still have Postgres use the GIN index?

So I came up with a solution. Pre-localise the query in a join and use that to 
filter the matches:

SELECT * FROM search s 
INNER JOIN (
SELECT 'dutch'::regconfig AS language, to_tsquery('dutch', 'shine') as q
UNION SELECT 'english'::regconfig AS language, to_tsquery('english', 
'shine') as q
UNION SELECT 'simple'::regconfig AS language, to_tsquery('simple', 
'shine') as q
) q ON (s.language=q.language) 
WHERE fulltext @@ q;

This seems to work, but the query plan looks a bit confusing, so I’m not super 
confident about the correctness:

Nested Loop  (cost=205.44..1327.12 rows=188 width=1590) (actual 
time=3.350..7.010 rows=16 loops=1)
  ->  Unique  (cost=0.08..0.11 rows=3 width=0) (actual time=0.010..0.021 
rows=3 loops=1)
->  Sort  (cost=0.08..0.09 rows=3 width=0) (actual 
time=0.008..0.011 rows=3 loops=1)
  Sort Key: ('dutch'::regconfig), ('''vlieg'''::tsquery)
  Sort Method: quicksort  Memory: 25kB
  ->  Append  (cost=0.00..0.06 rows=3 width=0) (actual 
time=0.001..0.002 rows=3 loops=1)
->  Result  (cost=0.00..0.01 rows=1 width=0) (actual 
time=0.001..0.001 rows=1 loops=1)
->  Result  (cost=0.00..0.01 rows=1 width=0) (actual 
time=0.001..0.001 rows=1 loops=1)
->  Result  (cost=0.00..0.01 rows=1 width=0) (actual 
time=0.000..0.000 rows=1 loops=1)
  ->  Bitmap Heap Scan on search s  (cost=205.36..441.70 rows=63 
width=1554) (actual time=2.310..2.318 rows=5 loops=3)
Recheck Cond: ((fulltext @@ ('''vlieg'''::tsquery)) AND 
((language)::oid = (('dutch'::regconfig))::oid))
Heap Blocks: exact=16
->  BitmapAnd  (cost=205.36..205.36 rows=63 width=0) (actual 
time=2.303..2.303 rows=0 loops=3)
  ->  Bitmap Index Scan on search_fulltext  (cost=0.00..17.41 
rows=188 width=0) (actual time=0.018..0.018 rows=16 loops=3)
Index Cond: (fulltext @@ ('''vlieg'''::tsquery))
  ->  Bitmap Index Scan on search_language  (cost=0.00..187.67 
rows=12539 width=0) (actual time=2.277..2.277 rows=12539 loops=3)
Index Cond: ((language_config)::oid = 
(('dutch'::regconfig))::oid)
Planning time: 0.228 ms
Execution time: 7.058 ms

Is this the way to go? Or is there a better way, I’m eager to find out!



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

[GENERAL] PgSQL versions supported on ubuntu 16 and debian 8

[MEERA]

Hi,

Could someone share below information?

- PgSQL versions supported on ubuntu 16
- PgSQL versions supported on debian 8


-- 
thanks and regards,
Meera R Nair

[GENERAL] pgpoolAdmin - pgmgt.conf.php write denied error

[Ashish Chauhan]

Hi,
Below is my test environment.
2 DB servers: 9.4 postgres
1 PgPool and PgpoolAdmin server:
pgpool - version 3.5.4 (ekieboshi)
pgpoolAdmin - 3.5.3
php - PHP 5.6.25-2+deb.sury.org~xenial+1 (cli)

While setting up pgpoolAdmin I am getting error 
"/var/www/html/pgpoolAdmin-3.5.3/conf/pgmgt.conf.php write denied", I gave full 
access(755) to apache user for pgmgt.conf.php file but still having same issue. 
Below error message from apache2 log.

"[Tue Sep 13 18:25:12.719093 2016] [:error] [pid 19279] [client 
xx.xx.xx.167:57118] PHP Notice:  Use of undefined constant _PGPOOL2_VERSION - 
assumed '_PGPOOL2_VERSION' in /var/www/html/pgpoolAdmin-3.5.3/bootstrap.php on 
line 33, referer: 
http://xx.xx.xx.xx/pgpoolAdmin-3.5.3/install/checkDirectory.php;

Thanks,
Ashish Chauhan

[GENERAL] Testers Needed For New Database Monitoring Platform

[carpenterc17]

Hey guys!
We are looking for active beta participants to try out our new SaaS-Based
Database Monitoring Tool. Our tool will monitor your databases and their
underlying (virtual) infrastructure. If you would like to be a part of the
beta, sign up here: http://www.bluemedora.com/early-access/

We will initially be supporting RDS, MSSQL, Oracle, PostgreSQL, Mongo,
DynamoDB and MySQL (and MariaDB). And then we will add support to SQL Azure,
DB2, Aurora, etc. as the beta progresses.
If you have any questions, feel free to post and I will be happy to answer
them.



--
View this message in context: 
http://postgresql.nabble.com/Testers-Needed-For-New-Database-Monitoring-Platform-tp5921027.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Test letter

[Leonardo M . Ramé]

El 15/09/16 a las 11:57, Alex Sviridov escribió:

Hi all,

I have suspicions that my message don't get to pgsql-general mailing list.

Please, someone, answer this message if this get the mailing list.


Best regards, Alex



Arrived ok.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Test letter

[Martijn Tonies (Upscene Productions)]

It did.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

From: Alex Sviridov 
Sent: Thursday, September 15, 2016 4:57 PM
To: pgsql-general@postgresql.org 
Subject: [GENERAL] Test letter

Hi all,

I have suspicions that my message don't get to pgsql-general mailing list. 

Please, someone, answer this message if this get the mailing list.


Best regards, Alex

[GENERAL] Test letter

[Alex Sviridov]

Hi all,

I have  suspicions that my message don't get to pgsql-general mailing list. 

Please, someone, answer this message if this get the mailing list.


Best regards, Alex

Re: [GENERAL] ERROR: could not read block 4 ...

[Leonardo M . Ramé]

El 15/09/16 a las 11:05, Tom Lane escribió:

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?="  writes:

Hi, I'm getting this error:
2016-09-15 09:35:24 ART [13984-1] ERROR:  could not read block 4 in file
"base/12210/11965": Error de entrada/salida

That looks like a hardware problem ... might want to budget for a new disk
sometime soon, before failures become more prevalent.


1) Is this caused by a bad block?, can fsck fix it?.

yes, and fsck is unlikely to help, though you could try.


2) Is there a way to re-generate template0?.

If you've never modified template1, you could drop template0 and re-create
it from template1.  Otherwise, pg_dumpall/initdb/reload would seem to be
called for.  A cautious person might want to do the latter anyway in case
there's more problems than just this one.

regards, tom lane


Thanks, indeed, I'm moving the Db to another server.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] ERROR: could not read block 4 ...

[Tom Lane]

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?="  writes:
> Hi, I'm getting this error:
> 2016-09-15 09:35:24 ART [13984-1] ERROR:  could not read block 4 in file 
> "base/12210/11965": Error de entrada/salida

That looks like a hardware problem ... might want to budget for a new disk
sometime soon, before failures become more prevalent.

> 1) Is this caused by a bad block?, can fsck fix it?.

yes, and fsck is unlikely to help, though you could try.

> 2) Is there a way to re-generate template0?.

If you've never modified template1, you could drop template0 and re-create
it from template1.  Otherwise, pg_dumpall/initdb/reload would seem to be
called for.  A cautious person might want to do the latter anyway in case
there's more problems than just this one.

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] Request to share information regarding postgresql pg_xlog file.

[Tom Lane]

Yogesh Sharma  writes:
> We are using below postgresql rpm.
> postgresql-8.1.18-2.1

As already noted, that version is *way* obsolete, and full of known bugs.
It's irresponsible to be storing data you care about in such a version.
Having said that ...

> In our system, below error is found and occurring is very frequent.
> CONTEXT:  writing block 53 of relation 1663/16385/280951
> ERROR:  could not open relation 1663/16385/280951: No such file or directory

Evidently, the bgwriter is trying to flush out a dirty buffer belonging to
a table that isn't there on-disk.  I'm not sure I believe the other
respondents suggesting that the filesystem lost the file, especially not
if you're only seeing complaints about this one block in this one
relation.  You could check by seeing whether any pg_class rows have
relfilenode 280951 in whichever DB has OID 16385.  If not, then this
is just a minor bug that somehow a dirty buffer didn't get flushed before
its table was dropped.

While you don't really care about the data in that buffer in such a case,
the bgwriter doesn't know that.  The trick is to get past that and
complete a checkpoint.  You could try just touch-ing the missing file so
that there's something for the bgwriter to write the data into.

If that doesn't work, TBH, I'd suggest kill -9'ing the bgwriter and
letting the thing recover from WAL.  Given that you've built up a
whole lot of WAL since the last successful checkpoint, that will
take quite a while, so it's a last resort ... but it ought to work.

regards, tom lane


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

[GENERAL] PostgreSQL GIN index not used when ts_query language is fetched from a column

[Jaap Roes]

I've got a table that stores some multilingual content:

CREATE TABLE search (
  content text NOT NULL,
  language regconfig NOT NULL,
  fulltext tsvector
);
CREATE INDEX search_fulltext ON search USING GIN(fulltext);

INSERT INTO search (language, content) VALUES  
  ('dutch', 'Als achter vliegen vliegen vliegen vliegen vliegen vliegen 
achterna'),
  ('dutch', 'Langs de koele kali liep een kale koeli met een kilo kali op 
zijn kale koeli-kop.'),
  ('dutch', 'Moeder sneed zeven scheve sneden brood'),
  ('english', 'I saw Susie sitting in a shoe shine shop. Where she sits she 
shines, and where she shines she sits.'),
  ('english', 'How can a clam cram in a clean cream can?'),
  ('english', 'Can you can a can as a canner can can a can?');

UPDATE search SET fulltext = to_tsvector(language, content);

To make sure I always search in the correct language I use these queries:

SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'shine');
(1 row)

SELECT FROM search WHERE fulltext @@ to_tsquery(language, 'vlieg');
(1 row)

Because hardcoding the language doesn't give the correct results:

SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 'shine');
(0 rows)

SELECT FROM search WHERE fulltext @@ to_tsquery('english', 'vlieg');
(0 rows)

The problem however is that PostgreSQL doesn't use the GIN index when using the 
first set of queries and instead does a sequential scan:

(Note: I've disabled scanning using SET enable_seqscan = OFF; for these 
examples because of the low amount of rows)

EXPLAIN ANALYZE SELECT * FROM search WHERE fulltext @@ to_tsquery(language, 
'shine’);

Seq Scan on search  (cost=0.00..17.35 rows=2 width=136) (actual 
time=0.040..0.044 rows=1 loops=1)
Filter: (fulltext @@ to_tsquery(language, 'shine'::text))
Rows Removed by Filter: 5
Planning time: 0.039 ms
Execution time: 0.064 ms

While it does when hardcoding a language:

EXPLAIN ANALYZE SELECT FROM search WHERE fulltext @@ to_tsquery('dutch', 
'vlieg');
Bitmap Heap Scan on search  (cost=12.63..23.66 rows=82 width=0) (actual 
time=0.044..0.044 rows=1 loops=1)
  Recheck Cond: (fulltext @@ '''vlieg'''::tsquery)
  Heap Blocks: exact=1
->  Bitmap Index Scan on search_fulltext  (cost=0.00..12.61 rows=82 
width=0) (actual time=0.037..0.037 rows=1 loops=1)
  Index Cond: (fulltext @@ '''vlieg'''::tsquery)
Planning time: 0.128 ms
Execution time: 0.065 ms

So my question is: Is it at all possible to use a column in the ts_query to use 
the correct language config and still have Postgres use the GIN index?

I’ve tried this on both PostgreSQL 9.4 and 9.5.

Obviously the real table has a lot more rows, so here's the execution plan from 
the real table:

Using a column for language config:

Seq Scan on search  (cost=0.00..8727.25 rows=188 width=0) (actual 
time=0.725..352.307 rows=1689 loops=1)
  Filter: (fulltext @@ to_tsquery(language_config, 'example'::text))
  Rows Removed by Filter: 35928
Planning time: 0.053 ms
Execution time: 352.915 ms

When hardcoding the language:

Bitmap Heap Scan on search  (cost=28.65..4088.92 rows=1633 width=0) (actual 
time=0.514..10.475 rows=1684 loops=1)
  Recheck Cond: (fulltext @@ '''exampl'''::tsquery)
  Heap Blocks: exact=1522  
->  Bitmap Index Scan on search_fulltext  (cost=0.00..28.24 rows=1633 
width=0) (actual time=0.333..0.333 rows=1684 loops=1)
  Index Cond: (fulltext @@ '''exampl'''::tsquery)
Planning time: 0.180 ms
Execution time: 10.564 ms

Note: I previously asked this on stackexhange 
(http://dba.stackexchange.com/questions/149765/postgresql-gin-index-not-used-when-ts-query-language-is-fetched-from-a-column)
 but I’m assuming there are more knowledgeable people on this mailing list ;-)

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

[GENERAL] ERROR: could not read block 4 ...

[Leonardo M . Ramé]

Hi, I'm getting this error:

2016-09-15 09:35:24 ART [13984-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida
2016-09-15 09:35:39 ART [14082-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida
2016-09-15 09:35:54 ART [14170-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida
2016-09-15 09:36:09 ART [20704-1] ERROR:  could not read block 4 in file 
"base/12210/11965": Error de entrada/salida


To find out which database points to it I use:

mydb=# select oid, datname from pg_database;
  oid  |  datname
---+---
 1 | template1
 12210 | template0 <--- Looks like this is the problematic db
 12215 | postgres
 16384 | mydb

Two questions:

1) Is this caused by a bad block?, can fsck fix it?.
2) Is there a way to re-generate template0?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] Request to share information regarding postgresql pg_xlog file.

[Yogesh Sharma]

Dear John,

Thanks for your support.

>as far as how do you determine whats wrong with your file system?
I tried fsck and hardware check using SMART disk info, no issue found with disk 
or filesystem.

>what file system are you using for the volume containing the postgres data 
>directory ?  with RHEL5, you were pretty much limited to EXT3, I guess ?
File system is ext3 and mount with sync type (rw,sync,dirsync,noatime).

>It would probably be a good idea to unmount the volume and fsck it.  also 
>check your system logs for any disk IO errors.
I did that, fsck runs fine no issue.

>is this storage on a raid controller, or using software raid, or just a simple 
>file system on a single disk, or what?
We are using a simple files system which is mirrored at block level.

>desktop/consumer disk drives are notorious for lying about writeback caching, 
>telling the software the data is written when its still in a cache on the 
>drive... if the power fails before the data actually gets written to disk with 
>one of these, you can lose stuff.

Sync mode of file system ensures that, data is continuously flushed on disk as 
soon as write system call initiated on file system.


Thanks,
Yogesh

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, September 15, 2016 1:24 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding postgresql 
pg_xlog file.

On 9/15/2016 12:25 AM, Yogesh Sharma wrote:
Dear John,

Thanks for your support.

Please find below name of rpm.
RPMS/postgresql-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-devel-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-libs-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-python-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-server-8.1.18-2.1.x86_64.rpm

We are using redhat Enterprise Linux 5.8.


ok, those RPM's were built and packaged by Redhat, I believe.   If you have a 
RHEL support contract, you should be able to get help from them.  If you don't, 
you really shouldn't be running RHEL as there's no updates available without 
one.


as far as how do you determine whats wrong with your file system?   I don't 
know how you'd narrow that down, but postgres expected a file to be there, and 
it wasn't. what file system are you using for the volume containing the 
postgres data directory ?  with RHEL5, you were pretty much limited to EXT3, I 
guess ?  It would probably be a good idea to unmount the volume and fsck 
it.  also check your system logs for any disk IO errors.   is this storage on a 
raid controller, or using software raid, or just a simple file system on a 
single disk, or what?desktop/consumer disk drives are notorious for lying 
about writeback caching, telling the software the data is written when its 
still in a cache on the drive... if the power fails before the data actually 
gets written to disk with one of these, you can lose stuff.



--

john r pierce, recycling bits in santa cruz

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

[John R Pierce]

On 9/15/2016 12:53 AM, John R Pierce wrote:





ok, those RPM's were built and packaged by Redhat, I believe.   If you 
have a RHEL support contract, you should be able to get help from 
them.  If you don't, you really shouldn't be running RHEL as there's 
no updates available without one.


wait, the RHEL supplied RPMs have .el5. in the name.  like...

postgresql-8.1.23-10.el5_10.x86_64.rpm 



(from the CentOS 5.11 repository)

so, I'm not sure /where/ your RPMs came from.


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

[John R Pierce]

On 9/15/2016 12:25 AM, Yogesh Sharma wrote:


Dear John,

Thanks for your support.

Please find below name of rpm.

RPMS/postgresql-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-devel-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-libs-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-python-8.1.18-2.1.x86_64.rpm
RPMS/postgresql-server-8.1.18-2.1.x86_64.rpm

We are using redhat Enterprise Linux 5.8.




ok, those RPM's were built and packaged by Redhat, I believe.   If you 
have a RHEL support contract, you should be able to get help from them.  
If you don't, you really shouldn't be running RHEL as there's no updates 
available without one.



as far as how do you determine whats wrong with your file system? I 
don't know how you'd narrow that down, but postgres expected a file to 
be there, and it wasn't. what file system are you using for the 
volume containing the postgres data directory ?  with RHEL5, you were 
pretty much limited to EXT3, I guess ?  It would probably be a good 
idea to unmount the volume and fsck it.  also check your system logs for 
any disk IO errors.   is this storage on a raid controller, or using 
software raid, or just a simple file system on a single disk, or 
what?desktop/consumer disk drives are notorious for lying about 
writeback caching, telling the software the data is written when its 
still in a cache on the drive... if the power fails before the data 
actually gets written to disk with one of these, you can lose stuff.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

[Rob Sargent]

> On Sep 15, 2016, at 1:20 AM, Yogesh Sharma 
>  wrote:
> 
> Dear John and all,
>  
> >8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
> >released in 2009, the final 8.1.23 release was in 2010, after which it was 
> >>dropped.
> Yes, we understood your point.
> But we require some information related to this rpm.
>  
> >These errors suggest disk file corruption, this can occur from unreliable 
> >storage, undetected memory errors, and other such things.
> How we can verify what is actual problem in system?
>  
> Also please share some information related to below.
> we tried to stop the postgresql but it couldn’t stop and timout after 60 sec.
> please confirm below message in postgre logs.
> FATAL:  terminating connection due to administrator command
>  
>  
> Regards,
> Yogesh
>  
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
> Sent: Thursday, September 15, 2016 11:28 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding postgresql 
> pg_xlog file.
>  
> On 9/14/2016 10:09 PM, Yogesh Sharma wrote:
> Thanks for your support and suggestion.
> We are using below postgresql rpm.
> postgresql-8.1.18-2.1
> 
> thats not the full RPM name, thats just the version.
> 
> 8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
> released in 2009, the final 8.1.23 release was in 2010, after which it was 
> dropped.
> 
> current releases are 9.1 (soon to be obsoletted), 9.2, 9.3, 9.4, and 9.5, 
> with 9.6 in release candidate state.
> 
> 
> CONTEXT:  writing block 53 of relation 1663/16385/280951
> ERROR:  could not open relation 1663/16385/280951: No such file or directory
> 
> 
> These errors suggest disk file corruption, this can occur from unreliable 
> storage, undetected memory errors, and other such things.
> 
> 
> 
>  
> 
> -- 
> john r pierce, recycling bits in santa cruz
What operating system is this running on? John is most likely correct: the disk 
is not healthy.  How you deal with that depends on your OS
Are you looking for the rpm for that version? Or do you have some other reason 
for asking about the rpm versus questions about the postgres version
This list requests that you “bottom post” i.e. add your comments to the bottom, 
not the top. (I don’t like it, but that’s the protocol here)

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

[Yogesh Sharma]

Dear John and all,

>8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
>released in 2009, the final 8.1.23 release was in 2010, after which it was 
>>dropped.
Yes, we understood your point.
But we require some information related to this rpm.

>These errors suggest disk file corruption, this can occur from unreliable 
>storage, undetected memory errors, and other such things.
How we can verify what is actual problem in system?

Also please share some information related to below.
we tried to stop the postgresql but it couldn’t stop and timout after 60 sec.
please confirm below message in postgre logs.
FATAL:  terminating connection due to administrator command


Regards,
Yogesh

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, September 15, 2016 11:28 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding postgresql 
pg_xlog file.

On 9/14/2016 10:09 PM, Yogesh Sharma wrote:
Thanks for your support and suggestion.
We are using below postgresql rpm.
postgresql-8.1.18-2.1

thats not the full RPM name, thats just the version.

8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
released in 2009, the final 8.1.23 release was in 2010, after which it was 
dropped.

current releases are 9.1 (soon to be obsoletted), 9.2, 9.3, 9.4, and 9.5, with 
9.6 in release candidate state.


CONTEXT:  writing block 53 of relation 1663/16385/280951
ERROR:  could not open relation 1663/16385/280951: No such file or directory


These errors suggest disk file corruption, this can occur from unreliable 
storage, undetected memory errors, and other such things.





--

john r pierce, recycling bits in santa cruz