Can't locate Ora2Pg.pm while executing export_schema.sh

[Pansara, Jiten]

Hello Team,

I am getting below error while executing export_schema.sh. Any suggestion/help 
is really appreciated.

BEGIN failed--compilation aborted at /c/Strawberry/perl/site/bin/ora2pg line 27.
Can't locate Ora2Pg.pm in @INC (you may need to install the Ora2Pg module) 
(@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl 
/usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl 
/usr/lib/perl5/core_perl /usr/share/perl5/core_perl) at 
/c/Strawberry/perl/site/bin/ora2pg line 27.
BEGIN failed--compilation aborted at /c/Strawberry/perl/site/bin/ora2pg line 27.

If we don't want to use this script, should we execute ora2pg directly to 
migrate the database from Oracle to Postgres?

  *   Ora2pg -c ora2pg.conf

Let me know your views.

Jiten Pansara
Senior Database Analyst
T:  +91 9158149600
E: jiten.pans...@fisglobal.com
FIS | Advancing the way the world Pays, Banks, and Invests 
[cid:image001.png@01D79365.CE59CA90]  
[cid:image002.png@01D79365.CE59CA90]  
[cid:image003.png@01D79365.CE59CA90] 
[A picture containing text, outdoor, sign  Description automatically generated]


The information contained in this message is proprietary and/or confidential. 
If you are not the intended recipient, please: (i) delete the message and all 
copies; (ii) do not disclose, distribute or use the message in any manner; and 
(iii) notify the sender immediately. In addition, please be aware that any 
message addressed to our domain is subject to archiving and review by persons 
other than the intended recipient. Thank you.

Re: postgres disconnects on master after setting up replication

[Dave Cramer]

Dave Cramer
www.postgres.rocks


On Mon, 16 Aug 2021 at 12:32, Andy Hall  wrote:

> thanks for the very clear explanation much appreciated shall take this
> back to the team.
>
> On Mon, 16 Aug 2021 at 17:14, Tom Lane  wrote:
> >
> > Andy Hall  writes:
> > > we have an odd issue where a java app using JDBC which has been
> > > working fine has suddenly started disconnecting after we setup
> > > replication from the database it was connecting to ( which is now the
> > > primary in a replicating pair ) with errors such as the following...
> >
> > > CLIENT SIDE: DBNAME/logs/20210815-200601.20966.log:SessionManagerImpl
> > > WARN 20210816-00:01:00.252 - A session lost connection with the
> > > database. Disabling all sessions
> > > SERVER SIDE: 2021-08-16 01:48:27 BST FATAL: unsupported frontend
> > > protocol 1234.5680: server supports 1.0 to 3.0
> >
> > Hmm.  That "protocol version" is a GSS request:
> >
> > #define NEGOTIATE_GSS_CODE PG_PROTOCOL(1234,5680)
> >
> > So
> >
> > 1. You are using a server version that predates our GSS support.
> >
> > 2. On the other hand, the client knows GSS and is trying to use it.
> > The client should fall back to a non-GSS connection upon receiving
> > this error response.  But ...
> >
> > 3. Something --- it's not very clear what --- is seeing the error
> > and going into an unwarranted panic.
> >
> > I'd try to figure out what's issuing the "Disabling all sessions"
> > message and then filing a complaint with the authors of that.
>

What version of the JDBC driver are you using? There was one version that
unfortunately blindly sent out the GSS request.
That has been rectified in later versions, I believe somewhere around
42.2.20 or so.

Although as Tom mentioned the driver does know how to deal with this,
however there are some cloud providers that chose to just drop the
connection

Dave

Re: postgres disconnects on master after setting up replication

[Andy Hall]

yep we think this is exactly the issue...we have been testing a new
build with a later driver in UAT this morning and no recurrence of the
issue so looking most probable. thanks all for the very prompt and
clear responses it's most appreciated.

On Tue, 17 Aug 2021 at 13:09, Dave Cramer  wrote:
>
>
> Dave Cramer
> www.postgres.rocks
>
>
> On Mon, 16 Aug 2021 at 12:32, Andy Hall  wrote:
>>
>> thanks for the very clear explanation much appreciated shall take this
>> back to the team.
>>
>> On Mon, 16 Aug 2021 at 17:14, Tom Lane  wrote:
>> >
>> > Andy Hall  writes:
>> > > we have an odd issue where a java app using JDBC which has been
>> > > working fine has suddenly started disconnecting after we setup
>> > > replication from the database it was connecting to ( which is now the
>> > > primary in a replicating pair ) with errors such as the following...
>> >
>> > > CLIENT SIDE: DBNAME/logs/20210815-200601.20966.log:SessionManagerImpl
>> > > WARN 20210816-00:01:00.252 - A session lost connection with the
>> > > database. Disabling all sessions
>> > > SERVER SIDE: 2021-08-16 01:48:27 BST FATAL: unsupported frontend
>> > > protocol 1234.5680: server supports 1.0 to 3.0
>> >
>> > Hmm.  That "protocol version" is a GSS request:
>> >
>> > #define NEGOTIATE_GSS_CODE PG_PROTOCOL(1234,5680)
>> >
>> > So
>> >
>> > 1. You are using a server version that predates our GSS support.
>> >
>> > 2. On the other hand, the client knows GSS and is trying to use it.
>> > The client should fall back to a non-GSS connection upon receiving
>> > this error response.  But ...
>> >
>> > 3. Something --- it's not very clear what --- is seeing the error
>> > and going into an unwarranted panic.
>> >
>> > I'd try to figure out what's issuing the "Disabling all sessions"
>> > message and then filing a complaint with the authors of that.
>
>
> What version of the JDBC driver are you using? There was one version that 
> unfortunately blindly sent out the GSS request.
> That has been rectified in later versions, I believe somewhere around 42.2.20 
> or so.
>
> Although as Tom mentioned the driver does know how to deal with this, however 
> there are some cloud providers that chose to just drop the connection
>
> Dave

Re: Using a TRIGGER with window functions.

[Pól Ua Laoínecháin]

Hi all,

> If you want to update many rows after updating N rows,


Thanks to those who helped me with this - I think that Michael Lewis
has given me some good ideas on how I should go about this. Also,
thanks to those who took the time to explain why some of my ideas were
not well grounded (wouldn't be the first time! :-) ).

Rgs,


Pól...

Re: Can't locate Ora2Pg.pm while executing export_schema.sh

[Mladen Gogala]

You should locate Ora2Pg.pm using "find" like this:

[mgogala@umajor ~]$ find /usr/local -name Ora2Pg.pm
/usr/local/share/perl5/5.32/Ora2Pg.pm
[mgogala@umajor ~]$

and then make sure that your PERL5LIB directory contains  the location:

export PERL5LIB=/usr/local/share/perl5/5.32:$PERL5LIB

That should do the trick. On most distributions, 
/usr/local/share/perl5/ is already included into PERL5LIB. 
Are you working on Cygwin? You can do a simple test:


[mgogala@umajor ~]$ perl -e 'use Ora2Pg; print "$Ora2Pg::VERSION\n";'
21.0

This is not related to PostgreSQL, this is a Perl question: 
https://gist.github.com/cgoldberg/4332167


On 8/17/21 3:15 AM, Pansara, Jiten wrote:


Hello Team,

I am getting below error while executing export_schema.sh. Any 
suggestion/help is really appreciated.


BEGIN failed--compilation aborted at 
/c/Strawberry/perl/site/bin/ora2pg line 27.


Can't locate Ora2Pg.pm in @INC (you may need to install the Ora2Pg 
module) (@INC contains: /usr/lib/perl5/site_perl 
/usr/share/perl5/site_perl /usr/lib/perl5/vendor_perl 
/usr/share/perl5/vendor_perl /usr/lib/perl5/core_perl 
/usr/share/perl5/core_perl) at /c/Strawberry/perl/site/bin/ora2pg line 27.


BEGIN failed--compilation aborted at 
/c/Strawberry/perl/site/bin/ora2pg line 27.


If we don’t want to use this script, should we execute ora2pg directly 
to migrate the database from Oracle to Postgres?


  * Ora2pg -c ora2pg.conf

Let me know your views.

*Jiten Pansara*

Senior Database Analyst

*T*:  +91 9158149600
*E*: jiten.pans...@fisglobal.com 
*FIS | Advancing the way the world Pays, Banks, and 
Invests***


A picture containing text, outdoor, sign Description automatically 
generated


**

The information contained in this message is proprietary and/or 
confidential. If you are not the intended recipient, please: (i) 
delete the message and all copies; (ii) do not disclose, distribute or 
use the message in any manner; and (iii) notify the sender 
immediately. In addition, please be aware that any message addressed 
to our domain is subject to archiving and review by persons other than 
the intended recipient. Thank you. 


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Help with my MacOS PostgreSQL 12 installation

[Michael White]

This is my first post so if I’m in the wrong place please help me get to the 
correct list.

Issue:

I installed PostgreSQL on my Mac over a year ago and since upgraded my disk 
drive and OS to Big Sur.  I am a retired Software Engineer and still like to 
program and am learning PostgreSQL and have it working on Linux and FreeBSD.  
In any case I had put it on my Mac laptop and want to use it again but it seems 
that the upgrades I did might have rearranged file locations or they may have 
been lost.  I have been trying to understand what is where and come to a point 
where I need help getting PostgreSQL running again.  I have a file with notes 
on the installation and here it is:

Installation Directory: /Library/PostgreSQL/12
Server Installation Directory: /Library/PostgreSQL/12
Data Directory: /Library/PostgreSQL/12/data
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: postgresql-12
Command Line Tools Installation Directory: /Library/PostgreSQL/12
pgAdmin4 Installation Directory: /Library/PostgreSQL/12/pgAdmin 4
Stack Builder Installation Directory: /Library/PostgreSQL/12

1. PostgreSQL server is not started and I am having trouble figuring out how to 
start it because /Library/PostgreSQL/12 does not exist. (don’t know why)
2. The password for user postgres does not work.  (I can fix that but have not 
yet)
3. Don’t remember how I installed PostgreSQL but looks like with EnterpriseDB 
because of the installation directory.  (I got this from postgresapp.com 
)
4. I looked around the postgres user account directory and it appears that 
PostgreSQL is installed there which confuses me because I can’t find any 
information about that or how to start or access it in that manner.
5. Looking for advice on how to unravel this situation and get my install up 
and running.  I do have 1 database associated with some Java Web Development in 
this implementation.

My macOS is at Big Sur 11.5.2

Would appreciate your kind help.

Re: Help with my MacOS PostgreSQL 12 installation

[Luca Ferrari]

On Tue, Aug 17, 2021 at 6:21 PM Michael White  wrote:
> 1. PostgreSQL server is not started and I am having trouble figuring out how 
> to start it because /Library/PostgreSQL/12 does not exist. (don’t know why)

that *could be* the reason why it does not start!


> 3. Don’t remember how I installed PostgreSQL but looks like with EnterpriseDB 
> because of the installation directory.  (I got this from postgresapp.com)

is there any postgres or psql executable around?
I suspect the upgrade did nuke everything was not installed by apple.

> 4. I looked around the postgres user account directory and it appears that 
> PostgreSQL is installed there which confuses me because I can’t find any 
> information about that or how to start or access it in that manner.

Do you mean is there a data directory under postgres user home
directory? Can you run pg_ctl to start the cluster from within such
directory?

> 5. Looking for advice on how to unravel this situation and get my install up 
> and running.  I do have 1 database associated with some Java Web Development 
> in this implementation.

Any chance you can understand if data under the postgres user has the
size you expect the database to have?
I'm not a mac user, but I suspect the upgrade destroyed your installation.

Luca

Re: Can't locate Ora2Pg.pm while executing export_schema.sh

[Luca Ferrari]

On Tue, Aug 17, 2021 at 4:34 PM Mladen Gogala  wrote:
>
> You should locate Ora2Pg.pm using "find" like this:
>
> [mgogala@umajor ~]$ find /usr/local -name Ora2Pg.pm
> /usr/local/share/perl5/5.32/Ora2Pg.pm

Sounds like he is using strawberry Perl, that means he is on windows.
Things could be different there.

Luca

transpose time-series columnar data

[Edu Gargiulo]

Hi all,

I got a view that returns values in the following format

timestamp  |   name   |value
-
ts1name1   value11
ts1name2   value12
ts1name3   value13
ts2name1   value21
ts2name2   value22
ts2name3   value23
ts3name1   value31
ts3name2   value32
ts3name3   value33

I need to transpose and return one row for a single timestamp and one
column for every name (fixed number of names), something like this

timestamp|   name1  |name2   |name 3
---
ts1  value11value12  value13
ts2  value21value22  value23
ts3  value31value32  value33

Any help would be appreciated

Thanks and sorry for my english

--
Edu

Re: transpose time-series columnar data

[David G. Johnston]

On Tue, Aug 17, 2021 at 11:29 AM Edu Gargiulo  wrote:

>
> I need to transpose and return one row for a single timestamp and one
> column for every name (fixed number of names), something like this
>
>
https://www.postgresql.org/docs/current/tablefunc.html

or

select ..., max(value) filter (where name = 'name1') as name1, max(value)
filter (where name = 'name2') as name2, etc from tbl group by ...

David J.

Re: transpose time-series columnar data

[Cachique]

As suggested by David I would use Tablefunc.

This query should work.


select * from crosstab (
 'select timestamp, name, value from originaltable',
 'select distinct name from originaltable order by name'
 )
as transposedtable (timestamp varchar, name1 varchar, name2 varchar, name3
varchar)

Regards,
Walter


El mar, 17 de ago. de 2021 a la(s) 14:42, David G. Johnston (
david.g.johns...@gmail.com) escribió:

> On Tue, Aug 17, 2021 at 11:29 AM Edu Gargiulo  wrote:
>
>>
>> I need to transpose and return one row for a single timestamp and one
>> column for every name (fixed number of names), something like this
>>
>>
> https://www.postgresql.org/docs/current/tablefunc.html
>
> or
>
> select ..., max(value) filter (where name = 'name1') as name1, max(value)
> filter (where name = 'name2') as name2, etc from tbl group by ...
>
> David J.
>
>

Serious Assistance with PostgreSQL True Infinite Arbitrary Precision Maths.

[A Z]

Dear pgsql-general,

I'm after Infinite Precision Integer (ii) or Infinite Precision Rational (ir)
type and function support for PostgreSQL, as an easy installation
for two new native types.  That are available under free
and open source conditions, for public, private and commercial, free
use.

I am aware of the pgmp project.  While I have contacted it and and am
awaiting their response, it is the case that the present state of pgmp
is not enough for my needs.

PostgreSQL does not have a private doubly linked list available,
and so I can't assemble all this for myself, given that I do not
know enough about PostgreSQL extensions and the C language.

What I am after is True Arbitrary Precision Arithmetic, and not High Precision
Arithmetic.  What I seek would be contingent only upon available OS or Hardware
memory.

I need arithmetic and comparison support on Integer and Rational (Decimal type)
numbers.  +, -, *,/,%, ==, !=, <>,>,<, >=, <=. Other important candidates 
required
include +=, -=, *=, /=, %=.

The trick is, that I also need full classical function support as well.  What I 
need
fairly well is:

cast(ir) returns double precision;
cast(double precision) returns ir;
cast(ir) returns real;
cast(real) return ir;
cast(ir) returns numeric;
cast(numeric) returns ir;
cast(ir) returns decimal;
cast(decimal) returns ir;
cast(ii) returns bigserial;
cast(bigserial) returns ii;
cast(ii) returns serial;
cast(serial) returns ii;
cast(ii) returns smallserial;
cast(smallserial) returns ii;
cast(ii) returns bigint;
cast(bigint) returns ii;
cast(ii) returns integer;
cast(integer) returns ii;
cast(ii) returns smallint;
cast(smallint) returns ii;

cast(text as ii) returns ii;
cast(text as ir) returns ir;
cast(ir as text) returns text;
cast(ii as text) returns text;
cast(ii as ir) returns ir;
cast(ir as ii) returns ii;

sign(ir input) returns ir;
abs(ir input) returns ir;
pi(ii places) returns ir;
e(ii places) returns ir;
power(ir base, ir exponent) returns ir;
sqrt(ir input) returns ir
nroot(ii theroot, ir input) returns ir;
log10(ir input) returns ir;
loge(ir input) returns ir;
log2(ir input) returns ir;
factorial(ii input) returns ii;
degrees(ir input) returns ir;
radians(ir input) returns it;

sind(ir input) returns ir;
cosd(ir input) returns ir;
tand(ir input) returns ir;
asind(ir input) returns ir;
acosd(ir input) returns ir;
atand(ir input) returns ir;

sinr(ir input) returns ir;
cosr(ir input) returns ir;
tanr(ir input) returns ir;
asinr(ir input) returns ir;
acosr(ir input) returns ir;
atanr(ir input) returns ir;

The last two sets of functions are forward and inverse trigonometry functions,
both in degrees and radians.

I also need the update module to install easily.  A windows *.exe and *.msi,
a Linux *.deb, *.rpm or *.bin.

Is there someone or something out there that can get there?

Re: Serious Assistance with PostgreSQL True Infinite Arbitrary Precision Maths.

[Mladen Gogala]

The answer for you is Pl/Python3u. It can use numpy, which supports 
infinite precision arithmetic and converting results to double and back. 
It can also do linked lists. Depending on your age and your preferences, 
you might try Pl/Perl. I learned Perl in 1993 and have never stopped 
loving it. It has several modules for the infinite precision arithmetic. 
My favorite is Math::BigFloat. As for the linked lists, there is 
List::DoubleLinked module on CPAN. In other words, all procedural 
extensions can be used to fulfill your requirements. BTW, one of my 
employers told me that Perl is verboten and that the company has 
standardized on Python. So I had to learn it. It's not too bad if you're 
in that sort of things.


Regards


On 8/17/21 10:49 PM, A Z wrote:

Dear pgsql-general,

I'm after Infinite Precision Integer (ii) or Infinite Precision 
Rational (ir)

type and function support for PostgreSQL, as an easy installation
for two new native types.  That are available under free
and open source conditions, for public, private and commercial, free
use.

I am aware of the pgmp project.  While I have contacted it and and am
awaiting their response, it is the case that the present state of pgmp
is not enough for my needs.

PostgreSQL does not have a private doubly linked list available,
and so I can't assemble all this for myself, given that I do not
know enough about PostgreSQL extensions and the C language.

What I am after is True Arbitrary Precision Arithmetic, and not High 
Precision
Arithmetic.  What I seek would be contingent only upon available OS or 
Hardware

memory.

I need arithmetic and comparison support on Integer and Rational 
(Decimal type)
numbers. * +, -, *,/,%, ==, !=, <>,>,<, >=, <=*. Other important 
candidates required

include *+=, -=, *=, /=, %=.*

The trick is, that I also need full classical function support as 
well.  What I need

fairly well is:

*cast(ir) returns double precision;*
*cast(double precision) returns ir; *
*cast(ir) returns real;*
*cast(real) return ir;*
*cast(ir) returns numeric;*
*cast(numeric) returns ir;*
*cast(ir) returns decimal;*
*cast(decimal) returns ir;*
*cast(ii) returns bigserial;*
*cast(bigserial) returns ii;*
*cast(ii) returns serial;*
*cast(serial) returns ii;*
*cast(ii) returns smallserial;*
*cast(smallserial) returns ii;*
*cast(ii) returns bigint;*
*cast(bigint) returns ii;*
*cast(ii) returns integer;*
*cast(integer) returns ii;*
*cast(ii) returns smallint;*
*cast(smallint) returns ii;*
*
*
*cast(text as ii) returns ii;*
*cast(text as ir) returns ir;*
*cast(ir as text) returns text;*
*cast(ii as text) returns text;*
*cast(ii as ir) returns ir;*
*cast(ir as ii) returns ii;*
*
*
*sign(ir input) returns ir;*
*abs(ir input) returns ir;*
*pi(ii places) returns ir;*
*e(ii places) returns ir;*
*power(ir base, ir exponent) returns ir;*
*sqrt(ir input) returns ir*
*nroot(ii theroot, ir input) returns ir;*
*log10(ir input) returns ir;*
*loge(ir input) returns ir;*
*log2(ir input) returns ir;*
*factorial(ii input) returns ii;*
*degrees(ir input) returns ir;*
*radians(ir input) returns it;*
*
*
*sind(ir input) returns ir;*
*cosd(ir input) returns ir;*
*tand(ir input) returns ir;*
*asind(ir input) returns ir;*
*acosd(ir input) returns ir;*
*atand(ir input) returns ir;*
*
*
*sinr(ir input) returns ir;*
*cosr(ir input) returns ir;*
*tanr(ir input) returns ir;*
*asinr(ir input) returns ir;*
*acosr(ir input) returns ir;*
*atanr(ir input) returns ir;*

The last two sets of functions are forward and inverse trigonometry 
functions,

both in degrees and radians.

I also need the update module to install easily.  A windows *.exe and 
*.msi,

a Linux *.deb, *.rpm or *.bin.

Is there someone or something out there that can get there?


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com