Thread

Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-20T12:36:51Z

Why PostgreSQL DOCs needs to show or compare the Oracle way of doing things
?

I understand that on page Porting from Oracle PL/SQL is ok to mention
Oracle, but there are other places where it's not needed. Or, if it's ok to
mention, why not mention SQL Server or MySQL or any other ?

Bug Reporting Guidelines
Especially refrain from merely saying that “This is not what SQL
says/Oracle does.”

LOCK
the PostgreSQL lock modes and the LOCK TABLE syntax are compatible with
those present in Oracle.

SELECT
Applications written for Oracle frequently use a workaround involving the
automatically generated rownum column, which is not available in
PostgreSQL, to implement the effects of these clauses.

ROLLBACK TO SAVEPOINT
The SQL standard specifies that the key word SAVEPOINT is mandatory, but
PostgreSQL and Oracle allow it to be omitted

Data Type Formatting Functions
FM modifies only the next specification, while in Oracle FM affects all
subsequent specifications, and repeated FM modifiers toggle fill mode on
and off.

Data Type Formatting Functions
A sign formatted using SG, PL, or MI is not anchored to the number; for
example, to_char(-12, 'MI9999') produces '-  12' but to_char(-12, 'S9999')
produces '  -12'. (The Oracle implementation does not allow the use of MI
before 9, but rather requires that 9 precede MI.)

regards
Marcos

Re: Why mention to Oracle ?

Tomas Vondra <tomas@vondra.me> — 2024-09-20T14:56:48Z

On 9/20/24 14:36, Marcos Pegoraro wrote:
> Why PostgreSQL DOCs needs to show or compare the Oracle way of doing
> things ?
> 
> I understand that on page Porting from Oracle PL/SQL is ok to mention
> Oracle, but there are other places where it's not needed. Or, if it's ok
> to mention, why not mention SQL Server or MySQL or any other ?
> 

It's not quite clear to me whether your suggestion is to not mention any
other databases ever, or to always mention every existing one. ;-)

I didn't dig into all the places you mention, but I'd bet those places
reference Oracle simply because it was the most common DB people either
migrated from or needed to support in their application next to PG, and
thus were running into problems. The similarity of the interfaces and
SQL dialects also likely played a role. It's less likely to run into
subtle behavior differences e.g. SQL Server when you have to rewrite
T-SQL stuff from scratch anyway.

regards

-- 
Tomas Vondra

Re: Why mention to Oracle ?

Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-20T15:53:49Z

Tomas Vondra <tomas@vondra.me> writes:
> On 9/20/24 14:36, Marcos Pegoraro wrote:
>> Why PostgreSQL DOCs needs to show or compare the Oracle way of doing
>> things ?

> I didn't dig into all the places you mention, but I'd bet those places
> reference Oracle simply because it was the most common DB people either
> migrated from or needed to support in their application next to PG, and
> thus were running into problems. The similarity of the interfaces and
> SQL dialects also likely played a role. It's less likely to run into
> subtle behavior differences e.g. SQL Server when you have to rewrite
> T-SQL stuff from scratch anyway.

As far as the mentions in "Data Type Formatting Functions" go, those
are there because those functions are not in the SQL standard; we
stole the API definitions for them from Oracle, lock stock and barrel.
(Except for the discrepancies that are called out by referencing what
Oracle does differently.)  A number of the other references probably
have similar origins.

			regards, tom lane

Re: Why mention to Oracle ?

David G. Johnston <david.g.johnston@gmail.com> — 2024-09-20T16:18:13Z

On Fri, Sep 20, 2024 at 5:37 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

> Why PostgreSQL DOCs needs to show or compare the Oracle way of doing
> things ?
>
> I understand that on page Porting from Oracle PL/SQL is ok to mention
> Oracle, but there are other places where it's not needed. Or, if it's ok to
> mention, why not mention SQL Server or MySQL or any other ?
>

It would be a boon to the community if someone were to put together a
web/wiki page or mini-app that details this kind of information and, if
considered accurate and relevant enough by the community, link to that more
globally while also remove the random and incomplete references of this
nature from the main documentation.  As it stands the info is at least
relevant, and its incompleteness doesn't cause enough grief, IMO, to
warrant its outright removal absent there existing an alternative.

> Bug Reporting Guidelines
> Especially refrain from merely saying that “This is not what SQL
> says/Oracle does.”
>

I would agree that this admonishment be re-worded.  I suggest:

If referencing some external authority, like the SQL Standard or another
relational database product, mention it, but also include the literal
output values.

David J.

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-20T17:31:47Z

Em sex., 20 de set. de 2024 às 12:53, Tom Lane <tgl@sss.pgh.pa.us> escreveu:

> As far as the mentions in "Data Type Formatting Functions" go, those
> are there because those functions are not in the SQL standard; we
> stole the API definitions for them from Oracle, lock stock and barrel.
> (Except for the discrepancies that are called out by referencing what
> Oracle does differently.)  A number of the other references probably
> have similar origins.
>

All the time we see somebody adding a new function to Postgres core that
exists in Python or GO or MySQL, but none of them are mentioned on DOCS.

I did never use Oracle but I'm almost sure on Oracle DOCS there are no
mentions of Postgres, right ? Why do we need to mention it ?

Regards
Marcos

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-20T17:42:17Z

Em sex., 20 de set. de 2024 às 11:56, Tomas Vondra <tomas@vondra.me>
escreveu:

> It's not quite clear to me whether your suggestion is to not mention any
> other databases ever, or to always mention every existing one. ;-)
>

My suggestion is: Postgres DOCs are written and have to be read by Postgres
users, just that. If you are Oracle user, search for a tutorial on how to
migrate to Postgres or find tools for it, but not in DOCs
Because if you write something for Oracle users, SQL Server users can claim
why there is no "Porting from T-SQL to PL/pgSQL" ?
And MySQL users can do the same, and so on.

Oracle simply because it was the most common DB people either
> migrated from or needed to support in their application next to PG, and
> thus were running into problems.
>

Maybe Oracle was the most common DB which migrated to Postgres, but I'm
not sure this is true for today.

regards
Marcos

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-20T17:48:51Z

Em sex., 20 de set. de 2024 às 13:18, David G. Johnston <
david.g.johnston@gmail.com> escreveu:

> It would be a boon to the community if someone were to put together a
> web/wiki page or mini-app that details this kind of information and, if
> considered accurate and relevant enough by the community, link to that more
> globally while also remove the random and incomplete references of this
> nature from the main documentation.  As it stands the info is at least
> relevant, and its incompleteness doesn't cause enough grief, IMO, to
> warrant its outright removal absent there existing an alternative.
>

Oracle DOCs or MySQL DOCs or any other have these comparisons ? I don't
think so, so why does Postgres have to mention it ?

All these places, and others I didn't find, I think it's correct to say
Postgres' way of doing that, not what is different from Oracle.

regards
Marcos

Re: Why mention to Oracle ?

Tomas Vondra <tomas@vondra.me> — 2024-09-20T18:00:32Z

On 9/20/24 19:31, Marcos Pegoraro wrote:
> Em sex., 20 de set. de 2024 às 12:53, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> escreveu:
> 
>     As far as the mentions in "Data Type Formatting Functions" go, those
>     are there because those functions are not in the SQL standard; we
>     stole the API definitions for them from Oracle, lock stock and barrel.
>     (Except for the discrepancies that are called out by referencing what
>     Oracle does differently.)  A number of the other references probably
>     have similar origins.
> 
> 
> All the time we see somebody adding a new function to Postgres core that
> exists in Python or GO or MySQL, but none of them are mentioned on DOCS.
> 

Which Python/Go/MySQL functions we added to Postgres, for example?

AFAIK we're now adding stuff that is either described by SQL standard,
or stuff that's our own invention. Neither cases would benefit from
explaining how other products behave. That's very different from the
interfaces we copied from Oracle.

> I did never use Oracle but I'm almost sure on Oracle DOCS there are no
> mentions of Postgres, right ? Why do we need to mention it ?
> 

I think Tom already explained that we copied a lot of this stuff from
Oracle, so it makes sense to explain in which cases the behavior
differs. I don't see how removing this would help users, it'd very
clearly make life harder for them.

I'm no fan of Oracle corp myself, but I admit I don't quite understand
why you're upset with the handful of places mentioning the product.

regards

-- 
Tomas Vondra

Re: Why mention to Oracle ?

Tomas Vondra <tomas@vondra.me> — 2024-09-20T18:11:22Z

On 9/20/24 19:48, Marcos Pegoraro wrote:
> Em sex., 20 de set. de 2024 às 13:18, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> escreveu:
> 
>     It would be a boon to the community if someone were to put together
>     a web/wiki page or mini-app that details this kind of information
>     and, if considered accurate and relevant enough by the community,
>     link to that more globally while also remove the random and
>     incomplete references of this nature from the main documentation. 
>     As it stands the info is at least relevant, and its incompleteness
>     doesn't cause enough grief, IMO, to warrant its outright removal
>     absent there existing an alternative.
> 
> 
> Oracle DOCs or MySQL DOCs or any other have these comparisons ? I don't
> think so, so why does Postgres have to mention it ? 
> 

I fail to see why would "entity X does not do A" be a good reason to not
do A ourselves. Commercial companies may have their own reasons not to
mention competing products, and few of those will likely apply to our
project. And maybe they're wrong to not do that, not us.

> All these places, and others I didn't find, I think it's correct to say
> Postgres' way of doing that, not what is different from Oracle.
> 

IMHO it's quite reasonable to say "we do X, but this other product
(which is what we try to mimic) does Y".


regards

-- 
Tomas Vondra

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-20T20:26:49Z

Em sex., 20 de set. de 2024 às 15:11, Tomas Vondra <tomas@vondra.me>
escreveu:

> IMHO it's quite reasonable to say "we do X, but this other product
> (which is what we try to mimic) does Y".
>

Ok, for Data Type Formatting Functions is fine, if they were really copied
from, but the others ...
Bug Reporting Guidelines, LOCK, SELECT, ROLLBACK TO SAVEPOINT and CURSORS

Re: Why mention to Oracle ?

Jonah H. Harris <jonah.harris@gmail.com> — 2024-09-20T21:34:06Z

On Fri, Sep 20, 2024 at 4:27 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

> Em sex., 20 de set. de 2024 às 15:11, Tomas Vondra <tomas@vondra.me>
> escreveu:
>
>> IMHO it's quite reasonable to say "we do X, but this other product
>> (which is what we try to mimic) does Y".
>>
>
> Ok, for Data Type Formatting Functions is fine, if they were really copied
> from, but the others ...
> Bug Reporting Guidelines, LOCK, SELECT, ROLLBACK TO SAVEPOINT and CURSORS
>

Seems to me this has already been answered well multiple times by multiple
people; I’m not sure why this is such an issue, or one that warrants
continued discussion.

By your own admission, you wouldn’t see the value, where others who came
from Oracle would. Additionally, your assumption is incorrect: many Oracle
databases are migrated to Postgres, more-so today than when much of that
was written.

You’re arguing against it being in the docs and talking about how much
better it would be in other more focused content, which does have some
merit. But, at the same time, you’re neither qualified nor volunteering to
write it. As such, getting rid of it here serves what purpose other than
omitting useful information to those it would benefit directly in the
documentation?

Re: Why mention to Oracle ?

Roberto Mello <roberto.mello@gmail.com> — 2024-09-20T22:48:57Z

On Fri, Sep 20, 2024 at 11:43 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

>
> My suggestion is: Postgres DOCs are written and have to be read by
> Postgres users, just that. If you are Oracle user, search for a tutorial on
> how to migrate to Postgres or find tools for it, but not in DOCs
>

As Tomas, Tom and others pointed out, it's simply because it is a common
database people
migrate from and ask for help, and people contributed patches to the
documentation out of their
own need, or to help others.

(Several) years ago I wrote a since-deprecated section of the docs to port
from PL/SQL to
PL/pgSQL because it was needed back then.

Because if you write something for Oracle users, SQL Server users can claim
> why there is no "Porting from T-SQL to PL/pgSQL" ?
> And MySQL users can do the same, and so on.
>

And those users are welcome to contribute patches to the docs explaining
why they think
those additions to our docs would be helpful.

> Maybe Oracle was the most common DB which migrated to Postgres, but I'm
> not sure this is true for today.
>

I don't know about you, but in my experience that is absolutely not true. I
deal with lots of people
and companies migrating from Oracle, or whose staff have experience with
Oracle and need
help adapting that knowledge to Postgres.

Roberto

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-21T16:50:37Z

Em sex., 20 de set. de 2024 às 18:34, Jonah H. Harris <
jonah.harris@gmail.com> escreveu:

> Seems to me this has already been answered well multiple times by multiple
> people; I’m not sure why this is such an issue, or one that warrants
> continued discussion.
>

No, I do not want to continue a discussion about a closed issue, I just
want to improve DOCs, or get it cleaner, just that.

https://www.postgresql.org/docs/current/sql-lock.html
PostgreSQL lock modes and the LOCK TABLE syntax are compatible with those
present in Oracle.

https://www.postgresql.org/docs/current/sql-rollback-to.html
The SQL standard specifies that the key word SAVEPOINT is mandatory, but
PostgreSQL and Oracle allow it to be omitted.

https://www.postgresql.org/docs/current/sql-select.html
Applications written for Oracle frequently use a workaround involving the
automatically generated rownum column, which is not available in
PostgreSQL, to implement the effects of these clauses.

https://www.postgresql.org/docs/current/plpgsql-cursors.html
FOR can be replaced by IS for Oracle compatibility.

So, except for Data Type Formatting, because Postgres mimics Oracle version,
and converting to PL/pgSQL, these other cases, and I don't know if other
exists, the DOC says something that is specific to a database, so users
which come from any other database could ask why not their database
compatibility is shown too. So I think all these cases could be removed.

Em sex., 20 de set. de 2024 às 18:34, Jonah H. Harris <
jonah.harris@gmail.com> escreveu:

> By your own admission, you wouldn’t see the value, where others who came
> from Oracle would. Additionally, your assumption is incorrect: many Oracle
> databases are migrated to Postgres, more-so today than when much of that
> was written.
>

I didn't say no more people are migrating from Oracle, I just say that
maybe migrations are now coming from other databases, like SQL Server,
MySQL, DB2, Firebird, Mongo and many others. So why do you document only
for those which come from Oracle ?
New Postgres users are today 90% coming from Oracle or 10%, I think we
cannot have this number exactly. And if nobody knows, why mention any of
them ?

Thanks for your time and I repeat, I just want to get Postgres DOCs better,
just that.

regards
Marcos

Re: Why mention to Oracle ?

Bruce Momjian <bruce@momjian.us> — 2024-09-21T21:42:18Z

On Sat, Sep 21, 2024 at 01:50:37PM -0300, Marcos Pegoraro wrote:
> I didn't say no more people are migrating from Oracle, I just say that maybe
> migrations are now coming from other databases, like SQL Server, MySQL, DB2,
> Firebird, Mongo and many others. So why do you document only for those which
> come from Oracle ?
> New Postgres users are today 90% coming from Oracle or 10%, I think we cannot
> have this number exactly. And if nobody knows, why mention any of them ?
> 
> Thanks for your time and I repeat, I just want to get Postgres DOCs better,
> just that.

I suggest you explain what changes would make the docs better (meaing
more useful).

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-22T14:09:30Z

Em sáb., 21 de set. de 2024 às 18:42, Bruce Momjian <bruce@momjian.us>
escreveu:

> I suggest you explain what changes would make the docs better (meaing
> more useful).
>

Well, I think I already did this in this discussion.
Tom said that some functions were copied from Oracle, so it is ok to
mention them. I don't think so.
Tomas said that we can mention other vendors, even if others don't do the
same for us. I don't think so again.
But David answered that would be cool if we create a separate
page/wiki/tool which compares, translates or anything like that to other
databases.
So, if we have a "Compatibility/Translation/Feature Comparison/ ... with
other Databases", it would be so cool.
But we don't have this kind of page, so why do we need to mention just one
of them ?
Searching on SGML there are 0 mentions to SQL Server and MySQL, but there
are almost 50 mentions to Oracle.
So this is my point, if you don't do the same for others, why do it for
Oracle ?

And again, I'm not saying that migrations from Oracle are not important,
I'm saying migrations from Oracle have the same importance than from MySQL,
SQL Server, Mongo, ...

regards
Marcos

Re: Why mention to Oracle ?

Roberto Mello <roberto.mello@gmail.com> — 2024-09-22T15:48:38Z

On Sun, Sep 22, 2024 at 8:10 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

> Em sáb., 21 de set. de 2024 às 18:42, Bruce Momjian <bruce@momjian.us>
> escreveu:
>
>> I suggest you explain what changes would make the docs better (meaing
>> more useful).
>>
>
> So, if we have a "Compatibility/Translation/Feature Comparison/ ... with
> other Databases", it would be so cool.
> But we don't have this kind of page, so why do we need to mention just one
> of them ?
>

Because people contributed those.

> Searching on SGML there are 0 mentions to SQL Server and MySQL, but there
> are almost 50 mentions to Oracle.
> So this is my point, if you don't do the same for others, why do it for
> Oracle ?
>

Because people contributed those.

> And again, I'm not saying that migrations from Oracle are not important,
> I'm saying migrations from Oracle have the same importance than from MySQL,
> SQL Server, Mongo, ...
>

Again, different people at different times felt it was important to
contribute patches to the documentation
explaining Oracle differences, or porting, etc. That's why those are in the
current docs.

If you're volunteering to add a MySQL, SQL Server, Mongo, etc porting to
the docs, I'm sure it could be a
nice addition.

Roberto

Re: Why mention to Oracle ?

Marcos Pegoraro <marcos@f10.com.br> — 2024-09-24T15:59:26Z

Em dom., 22 de set. de 2024 às 12:49, Roberto Mello <roberto.mello@gmail.com>
escreveu:

> If you're volunteering to add a MySQL, SQL Server, Mongo, etc porting to
> the docs, I'm sure it could be a
> nice addition.
>

And if we create a page like https://www.postgresql.org/about/featurematrix/
But instead of Postgres versions we have other vendors.
Every feature would have a Postgres way of doing and what differs from his
old database.
Feature PostgreSQL Oracle SQL Server MySQL Firebird
SELECT  N ROWS LIMIT 10   TOP 10   FIRST 10
CONCAT STRINGS 'Name: ' || Name    'Name: ' + Name
REBUILD INDEX REINDEX ALTER INDEX… REBUILD
CURRENT DATE CURRENT_DATE   GETDATE
This is just an example, for sure there would be several tables, for DMLs,
for DDL, for Maintenance ...

What do you think ?

regards
Marcos

Re: Why mention to Oracle ?

Greg Sabino Mullane <htamfids@gmail.com> — 2024-09-25T12:35:35Z

> And if we create a page like
> https://www.postgresql.org/about/featurematrix/
> But instead of Postgres versions we have other vendors.
>

This sounds like something that would fit well on the Postgres wiki:

https://wiki.postgresql.org/

Cheers,
Greg