Re: Ora2pg Delta Migration: Oracle to PostgreSQL

[Muhammad Ikram]

Hi,

Not related to Ora2PG but in the past I have used the EDB Migration toolkit
for such scenarios. Filterprops option can help in specifying filter (where
clause).

Regards,
Ikram


On Fri, May 3, 2024 at 8:12 AM Ron Johnson  wrote:

> On Thu, May 2, 2024 at 8:28 PM Amit Sharma  wrote:
>
>> Hello,
>>
>> Has anyone tried delta/incremental data migration for Oracle to
>> PostgreSQL using Ora2pg? Or what are the best options to run delta
>> migration for Oracle to PostgreSQL?
>>
>
> What do the ora2pg docs say about whether or not that feature is
> implemented?  (It wasn't when I last used it in 2022.)
>
>


-- 
Muhammad Ikram

Re: Ora2pg Delta Migration: Oracle to PostgreSQL

[Avinash Vallarapu]

Hi,

Has anyone tried delta/incremental data migration for Oracle to PostgreSQL
> using Ora2pg? Or what are the best options to run delta migration for
> Oracle to PostgreSQL?


There are a few ways of dealing with it when using Ora2Pg.

*Option 1 : *Ora2Pg configuration file allows us to pass the
conditions(predicates) to be applied to a table while migrating the data
from a table. So, if there is a way to filter the data since the last full
load, you can pass the condition to the configuration file, for each table.
This is some work for sure but it works great if you are able to identify
those conditions such as creation_date or last_updated_date, let's say.

*Option2 :* Ora2Pg is currently CDC ready. What this means is that, Ora2Pg
can provide the SCN at which the table copy has been initiated.
Technically, we provide the SCN to the CDC tools or custom CDC techniques
to continue replication since that SCN.

*Option 2.1 :* Leverage Debezium, an Open Source solution to perform
continuous replication from Oracle to PostgreSQL from the SCN produced by
Ora2Pg for that table.

So, it is all about Ora2Pg + Debezium

It might be initially challenging, but you will get there.

Regards,
Avi Vallarapu,
CEO,
HexaCluster Corp.






On Fri, May 3, 2024 at 3:25 AM Muhammad Ikram  wrote:

> Hi,
>
> Not related to Ora2PG but in the past I have used the EDB Migration
> toolkit for such scenarios. Filterprops option can help in specifying
> filter (where clause).
>
> Regards,
> Ikram
>
>
> On Fri, May 3, 2024 at 8:12 AM Ron Johnson 
> wrote:
>
>> On Thu, May 2, 2024 at 8:28 PM Amit Sharma  wrote:
>>
>>> Hello,
>>>
>>> Has anyone tried delta/incremental data migration for Oracle to
>>> PostgreSQL using Ora2pg? Or what are the best options to run delta
>>> migration for Oracle to PostgreSQL?
>>>
>>
>> What do the ora2pg docs say about whether or not that feature is
>> implemented?  (It wasn't when I last used it in 2022.)
>>
>>
>
>
> --
> Muhammad Ikram
>
>

Identity column data type difference in PostgreSQL and Oracle

[Muhammad Ikram]

Hello,

Tables which have an identity column in Oracle when migrated to PostgreSQL,
the data type of Identity column is changed to bigint from number by the
tools. This causes the size of column to be reduced to max value supported
by bigint which is way lower than the oracle  number max.
Secondly one has to change referencing columns data type as well.

What should be a better strategy for such transformations ?

Sample tables

CREATE TABLE Sales (
*transaction_id* NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1
MAXVALUE  INCREMENT BY 1 START WITH 1  NOCYCLE
customer VARCHAR2(100),
transaction_date DATE,
store_id NUMBER
);

CREATE TABLE Sales_Details (
*transaction_id* NUMBER,
item VARCHAR2(100),
quantity NUMBER,
price NUMBER,
CONSTRAINT fk_transaction_id FOREIGN KEY (transaction_id) REFERENCES
Sales(transaction_id)
);

--
Regards,
Muhammad Ikram

Re: Identity column data type difference in PostgreSQL and Oracle

[Peter Eisentraut]

On 03.05.24 12:57, Muhammad Ikram wrote:
Tables which have an identity column in Oracle when migrated to 
PostgreSQL, the data type of Identity column is changed to bigint from 
number by the tools. This causes the size of column to be reduced to max 
value supported by bigint which is way lower than the oracle  number max.


Sequences in PostgreSQL are limited to bigint, there is nothing that can 
be changed about that.  But you are approximately the first person to 
complain that the range of ids provided by bigint is too small, so maybe 
it's worth reconsidering whether it would work for you.  If it really is 
too small, maybe a uuid would work.  But yes, you'll need to make some 
adjustments to the rest of your database or application code if you make 
such changes.

Re: Identity column data type difference in PostgreSQL and Oracle

[Muhammad Ikram]

Thanks for the advice.


On Fri, 3 May 2024 at 16:09, Peter Eisentraut  wrote:

> On 03.05.24 12:57, Muhammad Ikram wrote:
> > Tables which have an identity column in Oracle when migrated to
> > PostgreSQL, the data type of Identity column is changed to bigint from
> > number by the tools. This causes the size of column to be reduced to max
> > value supported by bigint which is way lower than the oracle  number max.
>
> Sequences in PostgreSQL are limited to bigint, there is nothing that can
> be changed about that.  But you are approximately the first person to
> complain that the range of ids provided by bigint is too small, so maybe
> it's worth reconsidering whether it would work for you.  If it really is
> too small, maybe a uuid would work.  But yes, you'll need to make some
> adjustments to the rest of your database or application code if you make
> such changes.
>
>

Re: Ora2pg Delta Migration: Oracle to PostgreSQL

[Amit Sharma]

Thanks Avinash. Appriciate the help!!

Regards
Amit


On Fri, May 3, 2024 at 5:38 AM Avinash Vallarapu <
avinash.vallar...@gmail.com> wrote:

> Hi,
>
> Has anyone tried delta/incremental data migration for Oracle to PostgreSQL
>> using Ora2pg? Or what are the best options to run delta migration for
>> Oracle to PostgreSQL?
>
>
> There are a few ways of dealing with it when using Ora2Pg.
>
> *Option 1 : *Ora2Pg configuration file allows us to pass the
> conditions(predicates) to be applied to a table while migrating the data
> from a table. So, if there is a way to filter the data since the last full
> load, you can pass the condition to the configuration file, for each table.
> This is some work for sure but it works great if you are able to identify
> those conditions such as creation_date or last_updated_date, let's say.
>
> *Option2 :* Ora2Pg is currently CDC ready. What this means is that,
> Ora2Pg can provide the SCN at which the table copy has been initiated.
> Technically, we provide the SCN to the CDC tools or custom CDC techniques
> to continue replication since that SCN.
>
> *Option 2.1 :* Leverage Debezium, an Open Source solution to perform
> continuous replication from Oracle to PostgreSQL from the SCN produced by
> Ora2Pg for that table.
>
> So, it is all about Ora2Pg + Debezium
>
> It might be initially challenging, but you will get there.
>
> Regards,
> Avi Vallarapu,
> CEO,
> HexaCluster Corp.
>
>
>
>
>
>
> On Fri, May 3, 2024 at 3:25 AM Muhammad Ikram  wrote:
>
>> Hi,
>>
>> Not related to Ora2PG but in the past I have used the EDB Migration
>> toolkit for such scenarios. Filterprops option can help in specifying
>> filter (where clause).
>>
>> Regards,
>> Ikram
>>
>>
>> On Fri, May 3, 2024 at 8:12 AM Ron Johnson 
>> wrote:
>>
>>> On Thu, May 2, 2024 at 8:28 PM Amit Sharma  wrote:
>>>
 Hello,

 Has anyone tried delta/incremental data migration for Oracle to
 PostgreSQL using Ora2pg? Or what are the best options to run delta
 migration for Oracle to PostgreSQL?

>>>
>>> What do the ora2pg docs say about whether or not that feature is
>>> implemented?  (It wasn't when I last used it in 2022.)
>>>
>>>
>>
>>
>> --
>> Muhammad Ikram
>>
>>
>

\dt shows table but \d says the table doesn't exist ?

[David Gauthier]

psql (15.3, server 14.5) on linux

Someone else's DB which I've been asked to look at.

\dt gives many tables, here are just 3...

 public | some_idIds   | table |
cron_user
 public | WarningIds   | table |
cron_user
 public | cpf_inv_driverIds| table |
cron_user

but \d public.some_idIds gives..

Did not find any relation named "public.some_idIds".

Soo... what am I missing ?
owner is "cron_user".  \dt shows cron_user is the owner of the table.

Re: \dt shows table but \d says the table doesn't exist ?

[Magnus Hagander]

On Fri, May 3, 2024 at 10:58 PM David Gauthier 
wrote:

> psql (15.3, server 14.5) on linux
>
> Someone else's DB which I've been asked to look at.
>
> \dt gives many tables, here are just 3...
>
>  public | some_idIds   | table |
> cron_user
>  public | WarningIds   | table |
> cron_user
>  public | cpf_inv_driverIds| table |
> cron_user
>
> but \d public.some_idIds gives..
>
> Did not find any relation named "public.some_idIds".
>


Looks like you might need a \d "some_idIds" (include the quotes) since it
has an uppercase characters?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: \dt shows table but \d says the table doesn't exist ?

[Adrian Klaver]

On 5/3/24 13:58, David Gauthier wrote:

psql (15.3, server 14.5) on linux

Someone else's DB which I've been asked to look at.

\dt gives many tables, here are just 3...

  public | some_idIds                                       | table | 
cron_user
  public | WarningIds                                       | table | 
cron_user
  public | cpf_inv_driverIds                                | table | 
cron_user


but \d public.some_idIds gives..

Did not find any relation named "public.some_idIds".

Soo... what am I missing ?


Try:

select relname, length(relname) from pg_class where relname ilike 
'%some_idIds%';


Best guess there are hidden characters.


owner is "cron_user".  \dt shows cron_user is the owner of the table.




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

Re: \dt shows table but \d says the table doesn't exist ?

[Adrian Klaver]

On 5/3/24 14:06, Magnus Hagander wrote:



On Fri, May 3, 2024 at 10:58 PM David Gauthier > wrote:


psql (15.3, server 14.5) on linux

Someone else's DB which I've been asked to look at.

\dt gives many tables, here are just 3...

  public | some_idIds                                       | table
| cron_user
  public | WarningIds                                       | table
| cron_user
  public | cpf_inv_driverIds                                | table
| cron_user

but \d public.some_idIds gives..

Did not find any relation named "public.some_idIds".



Looks like you might need a \d "some_idIds" (include the quotes) since 
it has an uppercase characters?


This:

"Did not find any relation named "public.some_idIds"."

to me indicates it did look for the properly cased name.



--
  Magnus Hagander
  Me: https://www.hagander.net/ 
  Work: https://www.redpill-linpro.com/ 


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

Re: \dt shows table but \d says the table doesn't exist ?

[Magnus Hagander]

On Fri, May 3, 2024 at 11:08 PM Adrian Klaver 
wrote:

> On 5/3/24 14:06, Magnus Hagander wrote:
> >
> >
> > On Fri, May 3, 2024 at 10:58 PM David Gauthier  > > wrote:
> >
> > psql (15.3, server 14.5) on linux
> >
> > Someone else's DB which I've been asked to look at.
> >
> > \dt gives many tables, here are just 3...
> >
> >   public | some_idIds   | table
> > | cron_user
> >   public | WarningIds   | table
> > | cron_user
> >   public | cpf_inv_driverIds| table
> > | cron_user
> >
> > but \d public.some_idIds gives..
> >
> > Did not find any relation named "public.some_idIds".
> >
> >
> >
> > Looks like you might need a \d "some_idIds" (include the quotes) since
> > it has an uppercase characters?
>
> This:
>
> "Did not find any relation named "public.some_idIds"."
>
> to me indicates it did look for the properly cased name.
>

That is arguably a really bad error message, because it puts those quotes
there whether needed or not. if you put the quotes in there, you get:

Did not find any relation named "public."some_idIds"".

--
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: \dt shows table but \d says the table doesn't exist ?

[David G. Johnston]

On Fri, May 3, 2024 at 2:08 PM Adrian Klaver 
wrote:

> On 5/3/24 14:06, Magnus Hagander wrote:
> >
> >
> > On Fri, May 3, 2024 at 10:58 PM David Gauthier  > > wrote:
> >
> > psql (15.3, server 14.5) on linux
> >
> > Someone else's DB which I've been asked to look at.
> >
> > \dt gives many tables, here are just 3...
> >
> >   public | some_idIds   | table
>
> >
> > Looks like you might need a \d "some_idIds" (include the quotes) since
> > it has an uppercase characters?
>
> This:
>
> "Did not find any relation named "public.some_idIds"."
>
> to me indicates it did look for the properly cased name.
>
>
More likely there is confusion between an upper case I "eye" and a lower
case l "el".

David J.

Re: \dt shows table but \d says the table doesn't exist ?

[Tom Lane]

Adrian Klaver  writes:
> On 5/3/24 14:06, Magnus Hagander wrote:
>> Looks like you might need a \d "some_idIds" (include the quotes) since 
>> it has an uppercase characters?

> This:
> "Did not find any relation named "public.some_idIds"."
> to me indicates it did look for the properly cased name.

No, that message just regurgitates what you typed.  Magnus is
correct that the pattern will be case-folded if not quoted.
You can check with --echo-hidden (-E):

postgres=# \d public.some_idIds
/ QUERY */
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(some_idids)$' COLLATE 
pg_catalog.default
  AND n.nspname OPERATOR(pg_catalog.~) '^(public)$' COLLATE pg_catalog.default
ORDER BY 2, 3;
//

Did not find any relation named "public.some_idIds".

So it is in fact looking for public.some_idids.

regards, tom lane

Re: \dt shows table but \d says the table doesn't exist ?

[Tom Lane]

Magnus Hagander  writes:
> On Fri, May 3, 2024 at 11:08 PM Adrian Klaver 
> wrote:
>> This:
>> "Did not find any relation named "public.some_idIds"."
>> to me indicates it did look for the properly cased name.

> That is arguably a really bad error message, because it puts those quotes
> there whether needed or not. if you put the quotes in there, you get:
> Did not find any relation named "public."some_idIds"".

This is one of the places where it's unfortunate that our English-text
rule for quoting a string to set it off from the rest of the error
message collides with SQL's rule for quoting an identifier.  Leaving
out the outer quotes would be contrary to our style guide, but having
them there can be confusing too to people who know SQL well.

It'd be better if we could show the transformed search string, but
since it's been marked up to be a regex I fear that'd introduce
even more confusion than it solves.

regards, tom lane

Re: \dt shows table but \d says the table doesn't exist ?

[Isaac Morland]

On Fri, 3 May 2024 at 17:28, Tom Lane  wrote:

This is one of the places where it's unfortunate that our English-text
> rule for quoting a string to set it off from the rest of the error
> message collides with SQL's rule for quoting an identifier.  Leaving
> out the outer quotes would be contrary to our style guide, but having
> them there can be confusing too to people who know SQL well.


I'm not sure if this is a serious suggestion or not: enclose the name of
the table, as you would type it in psql, between curly quotes.

So for example:

“table_name”
“"TableNameWithUpperCaseLetters"”

In a context where the curly quotes and surrounding message could be in a
non-monospaced typeface and the table name (including double quote marks if
needed) in a monospaced typeface this would be more natural.

How to Build Postgres in a Portable / Relocatable fashion?

[AJ ONeal]

Back in the good old days there was official 1st-party support for Linux:
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Those binaries could be tar xvf​'d and used without any fuss or muss, from any 
location, on any distro.

I'm the core maintainer of https://webinstall.dev/ and I'm thinking to break 
our "official builds only" rule because, well, I haven't been able to find any 
way to get up-to-date builds from an official channel - and apt​ is always 
half-a-decade out-of-date (part of the reason for Webi, but Webi is far worse 
in this case, stuck at v10).

Could I get some help on how to do that?
Are the old build processes documented somewhere? Or are there some scripts in 
a far corner of the Internet that could still do that?

Or what options might I need to pass to ./configure to get it to build with 
relative locations?
I'm not a C developer, and I'm not familiar with C build tools beyond 
./configure; make; sudo make install​.

I'd really appreciate some direction on this. Thanks.

AJ ONeal

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Adrian Klaver]

On 5/3/24 15:57, AJ ONeal wrote:

Back in the good old days there was official 1st-party support for Linux:


Still there.

The below is for the EDB installer, which at this point is only  for 
MacOS and Windows as Unixen platforms have there own packaging. This 
was/is a third party site.



https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 



Those binaries could be |tar xvf|​'d and used without any fuss or muss, 
from any location, on any distro.


I'm the core maintainer of https://webinstall.dev/ 
 and I'm thinking to break our "official builds 
only" rule because, well, I haven't been able to find any way to get 
up-to-date builds from an official channel - and |apt|​ is always 
half-a-decade out-of-date (part of the reason for Webi, but Webi is far 
worse in this case, stuck at v10).


I don't see that. Using PGDG repo on Ubuntu 22.04:

apt list --installed | grep postgres

postgresql-14/jammy-pgdg,now 14.11-1.pgdg22.04+1 amd64 [installed]
postgresql-15-pgtap/jammy-pgdg,now 1.3.3-1.pgdg22.04+1 all [installed]
postgresql-15/jammy-pgdg,now 15.6-1.pgdg22.04+1 amd64 [installed]
postgresql-16-pgtap/jammy-pgdg,now 1.3.3-1.pgdg22.04+1 all [installed]
postgresql-16-unit/jammy-pgdg,now 7.8-1.pgdg22.04+1 amd64 [installed]
postgresql-16/jammy-pgdg,now 16.2-1.pgdg22.04+1 amd64 [installed,automatic]
postgresql-client-14/jammy-pgdg,now 14.11-1.pgdg22.04+1 amd64 
[installed,automatic]
postgresql-client-15/jammy-pgdg,now 15.6-1.pgdg22.04+1 amd64 
[installed,automatic]
postgresql-client-16/jammy-pgdg,now 16.2-1.pgdg22.04+1 amd64 
[installed,automatic]
postgresql-client-common/jammy-pgdg,now 259.pgdg22.04+1 all 
[installed,automatic]

postgresql-common/jammy-pgdg,now 259.pgdg22.04+1 all [installed,automatic]
postgresql-plpython3-14/jammy-pgdg,now 14.11-1.pgdg22.04+1 amd64 [installed]
postgresql-plpython3-15/jammy-pgdg,now 15.6-1.pgdg22.04+1 amd64 [installed]
postgresql-plpython3-16/jammy-pgdg,now 16.2-1.pgdg22.04+1 amd64 [installed]
postgresql-server-dev-16/jammy-pgdg,now 16.2-1.pgdg22.04+1 amd64 [installed]
postgresql/jammy-pgdg,now 16+259.pgdg22.04+1 all [installed]



Could I get some help on how to do that?
Are the old build processes documented somewhere? Or are there some 
scripts in a far corner of the Internet that could still do that?


As to Apt build scripts:

https://wiki.postgresql.org/wiki/Apt/RepoDocs



Or what options might I need to pass to ./configure to get it to build 
with relative locations?
I'm not a C developer, and I'm not familiar with C build tools beyond 
|./configure; make; sudo make install|​.


I'd really appreciate some direction on this. Thanks.

AJ ONeal


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

Re: How to Build Postgres in a Portable / Relocatable fashion?

[AJ ONeal]

> > Back in the good old days there was official 1st-party support for Linux:
> This
> was/is a third party site.

Oh. I thought that EDB was *the* Postgres company like MySQL AB was *the* MySQL 
company. My mistake.

> and `apt`​ is always half-a-decade out-of-date

I misspoke. I meant the OS repos more than apt itself.

> As to Apt build scripts:

I was just using apt as an example. I don't actually want to deal with apt or 
.debs.

What I want to create (and provide) is a portable tarball that has most of all 
what it needs in the tarball and will look for relevant libraries relative to 
itself. Something that Just Works™ *almost* anywhere (Ubuntu, Debian, RedHat, 
Suse... maybe even Alpine).

Any idea how to do that?

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Tom Lane]

AJ ONeal  writes:
> What I want to create (and provide) is a portable tarball that has
> most of all what it needs in the tarball and will look for relevant
> libraries relative to itself.

See

https://www.postgresql.org/docs/current/installation.html

particularly the discussion of installation relocatability under

https://www.postgresql.org/docs/current/install-make.html#CONFIGURE-OPTIONS-LOCATIONS

In short, you have to specify an installation path, but as long
as you don't muck with the relative locations of the subdirectories
it should be possible to move the whole tree to a different
installation location.

> Something that Just Works™ *almost* anywhere (Ubuntu, Debian, RedHat, Suse... 
> maybe even Alpine).

That's a little harder, mainly because the shared libraries for
dependencies like openssl etc may not be 100% compatible across
all those platforms.  Different versions, different build options,
yadda yadda.

Maybe you should be thinking in terms of a docker container
or the like?

regards, tom lane

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Bruce Momjian]

On Fri, May  3, 2024 at 11:27:12PM +, AJ ONeal wrote:
> > > Back in the good old days there was official 1st-party support for Linux:
> > This
> > was/is a third party site.
> 
> Oh. I thought that EDB was *the* Postgres company like MySQL AB was *the* 
> MySQL company. My mistake.

How did you come to that conclusion?  Is there something we could do to
avoid this assumption.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.

Re: How to Build Postgres in a Portable / Relocatable fashion?

[AJ ONeal]

> > What I want to create (and provide) is a portable tarball that has
> > most of all what it needs in the tarball and will look for relevant
> > libraries relative to itself.
> 
> 
> See
> 
> https://www.postgresql.org/docs/current/installation.html
> 
> particularly the discussion of installation relocatability under
> 
> https://www.postgresql.org/docs/current/install-make.html#CONFIGURE-OPTIONS-LOCATIONS

It seems like the first bit of magic I need is:
`--prefix=/doesntexist/postgres`

Including 'postgres' as part of the name puts it in "relative mode", 
so-to-speak.

Thank you!

> > Something that Just Works™ almost anywhere (Ubuntu, Debian, RedHat, Suse... 
> > maybe even Alpine).
> 
> 
> That's a little harder, mainly because the shared libraries for
> dependencies like openssl etc may not be 100% compatible across
> all those platforms. Different versions, different build options,
> yadda yadda.

I'd like to build as much static as possible (or reasonable). Is there a way to 
tell it "just include openssl"?

> Maybe you should be thinking in terms of a docker container
> or the like?

I really hate Docker. The idea of "we can't ship your computer to the client, 
so we'll just ship... a clone of you computer to the client" offends my 
sensibilities.

Or rather, I like the opportunity that LXC provides in the right context, but I 
don't like how it's become a replacement for shared knowledge within a group - 
i.e. "no one can remember the wisdom of the ancients, so if this fragile system 
that Ted built ever fails or needs a change no one knows how he did it or what 
the options mean, but don't worry, it's all encapsulated in a very complex 
Dockerfile that no one understands and depends on hundreds of layers of 
images... but it's not like someone would ever leftpad any of them... What 
could go wrong?"

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Adrian Klaver]

On 5/3/24 17:07, Bruce Momjian wrote:

On Fri, May  3, 2024 at 11:27:12PM +, AJ ONeal wrote:

Back in the good old days there was official 1st-party support for Linux:

This
was/is a third party site.


Oh. I thought that EDB was *the* Postgres company like MySQL AB was *the* MySQL 
company. My mistake.


How did you come to that conclusion?  Is there something we could do to
avoid this assumption.




From here:

https://www.enterprisedb.com/

#1 IN POSTGRES
THE MOST ADMIRED, DESIRED, & USED DATABASE.

is not helpful.

Though I would say the bigger issue is here:

https://www.postgresql.org/download/

Where you have

Packages and Installers

and then further down

3rd party distributions

It tends to imply that the 'Packages and Installers' are not third 
party, when they are to some degree or another.



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

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Bruce Momjian]

On Fri, May  3, 2024 at 05:29:34PM -0700, Adrian Klaver wrote:
> From here:
> 
> https://www.enterprisedb.com/
> 
> #1 IN POSTGRES
> THE MOST ADMIRED, DESIRED, & USED DATABASE.
> 
> is not helpful.
> 
> Though I would say the bigger issue is here:
> 
> https://www.postgresql.org/download/
> 
> Where you have
> 
> Packages and Installers
> 
> and then further down
> 
> 3rd party distributions
> 
> It tends to imply that the 'Packages and Installers' are not third party,
> when they are to some degree or another.

I think it is that way because we don't have any guarantees or regular
communication with "3rd party distributions".

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Adrian Klaver]

On 5/3/24 17:35, Bruce Momjian wrote:

On Fri, May  3, 2024 at 05:29:34PM -0700, Adrian Klaver wrote:

 From here:

https://www.enterprisedb.com/

#1 IN POSTGRES
THE MOST ADMIRED, DESIRED, & USED DATABASE.

is not helpful.

Though I would say the bigger issue is here:

https://www.postgresql.org/download/

Where you have

Packages and Installers

and then further down

3rd party distributions

It tends to imply that the 'Packages and Installers' are not third party,
when they are to some degree or another.


I think it is that way because we don't have any guarantees or regular
communication with "3rd party distributions".



I would say it is not clear enough that Core is responsible for the 
source releases, anything above that is some other groups 
responsibility. This becomes apparent when you have to tell people that 
fixing packaging issues requires reaching out to said groups through 
means that are not entirely clear.


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

Re: How to Build Postgres in a Portable / Relocatable fashion?

[Tom Lane]

AJ ONeal  writes:
> It seems like the first bit of magic I need is:
> `--prefix=/doesntexist/postgres`

No, you want an actual path so that "make install" will succeed.
The point is that then you can tar up that installation tree
(relative to its top) and untar it somewhere else.

> I'd like to build as much static as possible (or reasonable). Is there a way 
> to tell it "just include openssl"?

No, and are you really sure you want that?  Then it's on you to
rebuild and redistribute, pronto, every time openssl issues a security
fix.  There's a reason why most Linux distros forbid static linking of
libraries from different sources.

regards, tom lane

Re: Question regarding how databases support atomicity

[Siddharth Jain]

On Fri, May 3, 2024 at 8:00 PM Siddharth Jain  wrote:

> I am trying to sharpen my understanding of databases. Let's say there is
> an operation foo as part of the public API that internally translates to
> more than 1 operation - I am sure there are examples like this in postgres.
> So to do foo we have to do following in order in all or none fashion:
>
> 1. Step 1
> 2. Step 2
> 3. Step 3
>
> The way I understand this is that if there is a failure in-between, we
> start undoing and reverting the previous operations one by one. But what if
> there is a failure and we are not able to revert an operation. How is that
> situation handled? e.g., something failed when we tried to do Step 3. now
> we revert Step 2 and succeed. but when we try to revert step 1 we fail.
> what happens now? To me, it seems its impossible to guarantee true
> atomicity in general.
>
> S.
>

Re: Question regarding how databases support atomicity

[David G. Johnston]

On Friday, May 3, 2024, Siddharth Jain  wrote:

>
>
> On Fri, May 3, 2024 at 8:00 PM Siddharth Jain  wrote:
>
>> I am trying to sharpen my understanding of databases. Let's say there is
>> an operation foo as part of the public API that internally translates to
>> more than 1 operation - I am sure there are examples like this in postgres.
>> So to do foo we have to do following in order in all or none fashion:
>>
>> 1. Step 1
>> 2. Step 2
>> 3. Step 3
>>
>> The way I understand this is that if there is a failure in-between, we
>> start undoing and reverting the previous operations one by one.
>>
>
Not in PostgreSQL.  All work performed is considered provisional until a
commit succeeds.  At which point all provisional work, which had been
tagged with the same transaction identifier, becomes reality to the rest of
the system, by virtue of marking the transaction live.  If the commit never
happens, either because of error, rollback, or session end, the transaction
ends up being left unalive and eventually is cleaned up.

You need to ensure a “begin” happens before Step 1 and a “commit” after
Step 3.

David J.

Re: Question regarding how databases support atomicity

[David G. Johnston]

On Friday, May 3, 2024, David G. Johnston 
wrote:

> On Friday, May 3, 2024, Siddharth Jain  wrote:
>
>>
>>
>> On Fri, May 3, 2024 at 8:00 PM Siddharth Jain  wrote:
>>
>>>
>>>
>>> The way I understand this is that if there is a failure in-between, we
>>> start undoing and reverting the previous operations one by one.
>>>
>>
> You need to ensure a “begin” happens before Step 1 and a “commit” after
> Step 3.
>

As described in our documentation.

https://www.postgresql.org/docs/current/tutorial-transactions.html

David J.

Re: Question regarding how databases support atomicity

[Christophe Pettus]

> On May 3, 2024, at 20:02, Siddharth Jain  wrote:
> 
> 
> The way I understand this is that if there is a failure in-between, we start 
> undoing and reverting the previous operations one by one. But what if there 
> is a failure and we are not able to revert an operation. How is that 
> situation handled? e.g., something failed when we tried to do Step 3. now we 
> revert Step 2 and succeed. but when we try to revert step 1 we fail. what 
> happens now? To me, it seems its impossible to guarantee true atomicity in 
> general.

PostgreSQL does not "undo" operations as such.  When modifications are made to 
the database, those modifications (inserts, updates, deletes) are marked with 
the ID of the transaction that made them.  A COMMIT or ROLLBACK in PostgreSQL 
just notes if those modifications are now "permanent" (if the transaction 
committed) or "invisible" (if the transaction rolled back).  This technique in 
general is called Multi-Version Concurrency Control.  Here's a good 
presentation that describes how it works in PostgreSQL:

https://momjian.us/main/writings/pgsql/mvcc.pdf

Re: Question regarding how databases support atomicity

[Tom Lane]

"David G. Johnston"  writes:
> On Friday, May 3, 2024, Siddharth Jain  wrote:
>> The way I understand this is that if there is a failure in-between, we
>>> start undoing and reverting the previous operations one by one.

> Not in PostgreSQL.  All work performed is considered provisional until a
> commit succeeds.  At which point all provisional work, which had been
> tagged with the same transaction identifier, becomes reality to the rest of
> the system, by virtue of marking the transaction live.

Right.  We don't use UNDO; instead, we use multiple versions of
database rows (MVCC).  A transaction doesn't modify the contents
of existing rows, but just marks them as provisionally outdated, and
then inserts new row versions that are marked provisionally inserted.
Other transactions ignore the outdate markings and the uncommitted new
rows, until the transaction commits, at which time the new versions
become live and the old ones become dead.  If the transaction never
does commit -- either through ROLLBACK or a crash -- then the old row
versions remain live and the new ones are dead.  In either case, we
don't have a consistency or correctness problem, but we do have dead
rows that must eventually get vacuumed away to prevent indefinite
storage bloat.  That can be done by background housekeeping processes
though (a/k/a autovacuum).

I believe Oracle, for one, actually does use UNDO.  I don't know
what they do about failure-to-UNDO.

regards, tom lane

Re: \dt shows table but \d says the table doesn't exist ?

[jian he]

On Sat, May 4, 2024 at 5:15 AM Tom Lane  wrote:
>
> Adrian Klaver  writes:
> > On 5/3/24 14:06, Magnus Hagander wrote:
> >> Looks like you might need a \d "some_idIds" (include the quotes) since
> >> it has an uppercase characters?
>
> > This:
> > "Did not find any relation named "public.some_idIds"."
> > to me indicates it did look for the properly cased name.
>
> No, that message just regurgitates what you typed.  Magnus is
> correct that the pattern will be case-folded if not quoted.
> You can check with --echo-hidden (-E):
>
> postgres=# \d public.some_idIds
> / QUERY */
> SELECT c.oid,
>   n.nspname,
>   c.relname
> FROM pg_catalog.pg_class c
>  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relname OPERATOR(pg_catalog.~) '^(some_idids)$' COLLATE 
> pg_catalog.default
>   AND n.nspname OPERATOR(pg_catalog.~) '^(public)$' COLLATE pg_catalog.default
> ORDER BY 2, 3;
> //
>
> Did not find any relation named "public.some_idIds".
>
> So it is in fact looking for public.some_idids.
>

make it case insensitive?
like
> WHERE c.relname OPERATOR(pg_catalog.~*) '^(some_idids)$' COLLATE 
> pg_catalog.default

we already transformed `\d SOME_IDIDS` to `\d some_idids`, in some way
it looks case-insensitive.

Re: \dt shows table but \d says the table doesn't exist ?

[Tom Lane]

jian he  writes:
> make it case insensitive?

That would just move the problem around; that is, now we'd have people
complaining because they'd asked for "\d foo" and were getting results
for tables Foo and FOO.

By and large, I'd expect people using mixed-case table names to get
accustomed pretty quickly to the fact that they have to double-quote
those names in SQL.  I don't see why it's a surprise that that is also
true in \d commands.

regards, tom lane

Re: \dt shows table but \d says the table doesn't exist ?

[David G. Johnston]

On Friday, May 3, 2024, Tom Lane  wrote:
>
>
> By and large, I'd expect people using mixed-case table names to get
> accustomed pretty quickly to the fact that they have to double-quote
> those names in SQL.  I don't see why it's a surprise that that is also
> true in \d commands.
>
>
Every day the number of people increases who get mixed-case names in their
DB because their client language preserves case and doesn’t require quoting.

And it isn’t like any sane person is using case to distinguish names in
their DB and so would be at risk of seeing multiple unwanted matches for
any given pattern.

I don’t think name pattern matching during object lookup is a good tool to
wield to try and convince people that using camel case is a bad idea and
they should use snake case instead.  If they don’t write enough raw SQL to
be annoyed by their choice more power to them, making \d more accessible
for them is a win and snake case people won’t notice or care.

David J.

Re: \dt shows table but \d says the table doesn't exist ?

[Tom Lane]

"David G. Johnston"  writes:
> On Friday, May 3, 2024, Tom Lane  wrote:
>> By and large, I'd expect people using mixed-case table names to get
>> accustomed pretty quickly to the fact that they have to double-quote
>> those names in SQL.  I don't see why it's a surprise that that is also
>> true in \d commands.

> Every day the number of people increases who get mixed-case names in their
> DB because their client language preserves case and doesn’t require quoting.

And?  If they access the DB exclusively through their client language,
then yeah maybe they'll never know the difference.  But if they are
also using psql or other direct-SQL-access tools, they will learn
the quoting rules PDQ.  There's still no reason that \d should be
inconsistent with SQL.

regards, tom lane

Re: \dt shows table but \d says the table doesn't exist ?

[Adrian Klaver]

On 5/3/24 21:06, David G. Johnston wrote:
On Friday, May 3, 2024, Tom Lane > wrote:



By and large, I'd expect people using mixed-case table names to get
accustomed pretty quickly to the fact that they have to double-quote
those names in SQL.  I don't see why it's a surprise that that is also
true in \d commands.


Every day the number of people increases who get mixed-case names in 
their DB because their client language preserves case and doesn’t 
require quoting.


In a sense they do by making quoting the default, which people find out 
when they stray from the language/ORM/framework and get pointed at:


https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS



And it isn’t like any sane person is using case to distinguish names in 
their DB and so would be at risk of seeing multiple unwanted matches for 
any given pattern.


Have you met people?



I don’t think name pattern matching during object lookup is a good tool 
to wield to try and convince people that using camel case is a bad idea 
and they should use snake case instead.  If they don’t write enough raw 
SQL to be annoyed by their choice more power to them, making \d more 
accessible for them is a win and snake case people won’t notice or care.


David J.


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

Re: \dt shows table but \d says the table doesn't exist ?

[David G. Johnston]

On Friday, May 3, 2024, Adrian Klaver  wrote:

>
> Have you met people?
>

I really don’t care enough to try and actually make converts here.  It
would have been a perfectly justifiable design choice to make our “pattern”
matching case-insensitive by default, probably with a case-sensitive mode
and no need to hack up quoting rules that are unique to it.  It’s a find
feature and search benefits from case-insensitivity.  There isn’t anything
so compelling about the current behavior that it seems like the superior
choice.  But maybe you are right and I just lack sufficient real-world
experience to see things differently.

I also get not wanting to change behavior at this point though I’d welcome
a modifier like “*” (like the ~* operator) to enable case-insensitive
matching.

David J.

Re: \dt shows table but \d says the table doesn't exist ?

[Tom Lane]

"David G. Johnston"  writes:
> ... I’d welcome
> a modifier like “*” (like the ~* operator) to enable case-insensitive
> matching.

We could talk about that idea, certainly.  I'm afraid it's the sort
of edge case that would mainly be useful to newbies who haven't
read the docs closely enough to know the option exists, let alone
that it will help them.  But maybe there's more use-case than
I'm thinking of.

regards, tom lane