Thread: [HACKERS] Packages: Again

-hackers,

I know we have talked about this before but today it was impressed upon 
me rather firmly. I presented a Webinar: Postgres for Oracle People. The 
attendees were 90% pl/pgsql developers. 330 people registered for an 
event that was only allowed to host 100 people. The webinar went on for 
2 hours. (it was only scheduled for one hour, that is how interactive it 
was)

By far the tagline of this webinar from attendees was, "We can not port 
without packages"

So this is a reality. If we want tried and true Oracle developers to 
port to PostgreSQL, we must provide some level of package capability.

There are some that would say we don't need them. You are right, we 
don't need them. We should however want them if we want to continue to 
stomp through the business sector and continue growth.

I use this post to inspire conversation on how we can get this done.

Sincerely,

JD

-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

On Wed, Jan 11, 2017 at 10:57:58AM -0800, Joshua Drake wrote:
> -hackers,
> 
> I know we have talked about this before but today it was impressed upon me
> rather firmly. I presented a Webinar: Postgres for Oracle People. The
> attendees were 90% pl/pgsql developers. 330 people registered for an event
> that was only allowed to host 100 people. The webinar went on for 2 hours.
> (it was only scheduled for one hour, that is how interactive it was)
> 
> By far the tagline of this webinar from attendees was, "We can not port
> without packages"
> 
> So this is a reality. If we want tried and true Oracle developers to port to
> PostgreSQL, we must provide some level of package capability.
> 
> There are some that would say we don't need them. You are right, we don't
> need them. We should however want them if we want to continue to stomp
> through the business sector and continue growth.
> 
> I use this post to inspire conversation on how we can get this done.

So, we have a TODO item with links:
Add features of Oracle-style packages    A package would be a schema with session-local variables,public/private
functions,and initialization functions. It is alsopossible to implement these capabilities in any schema and not use
aseparate"packages" syntax at all.        proposal for PL packages for 8.3.        proposal: schema PL session
variables       proposal: session server side variables 
 

Is there anything that needs updating there, or it is just a question of
getting someone to implement it?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

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

On Wed, Jan 11, 2017 at 08:32:53PM +0100, Pavel Stehule wrote:
> Now I work on migration about 500K rows - and it is terrible work. It is 20
> years old project - lot of code is not clean, It is hard to migrate, it is hard
> to clean. Sure, there is not one line of tests.
> 
> If we miss some, then it is modern robust tool for migration - big thanks to
> ora2pg maintainers and developers - without it, there is nothing free.

I think we need to focus on things that _can't_ be done first, rather
than things that require porting, e.g. until we had savepoints, you
couldn't migrate an application that needed it.  It wasn't a question of
porting --- there was just no way to port it.

Those _missing_ pieces should be a priority.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

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

> We have a schemas instead - the PostgreSQL schema is close to Oracle
> packages.

Yes, a schema is a kind of a "namespace"-level package. Pg also has 
extensions, which is a group things put together, which may also 
contribute to packaging.

> What we cannot to substitute are package variables, now - see my proposal
> for session variables.

I would like also to point out here that Pg has dynamic text session 
variables with a horrible syntax, aka user-defined GUCs. They can be the 
basis for more useful variables if extended with privacy/some access 
control, typing, better syntax, possibly some kind of persistent 
declarations, and so on.

> [...]

Good luck with your migration...

-- 
Fabien.

> I think we need to focus on things that _can't_ be done first, rather
> than things that require porting, e.g. until we had savepoints, you
> couldn't migrate an application that needed it.  It wasn't a question of
> porting --- there was just no way to port it.
>
> Those _missing_ pieces should be a priority.

Nested/autonomous transactions? Do they occur often in PL/SQL code?

-- 
Fabien.

Fabien,

* Fabien COELHO (coelho@cri.ensmp.fr) wrote:
> >I think we need to focus on things that _can't_ be done first, rather
> >than things that require porting, e.g. until we had savepoints, you
> >couldn't migrate an application that needed it.  It wasn't a question of
> >porting --- there was just no way to port it.
> >
> >Those _missing_ pieces should be a priority.
>
> Nested/autonomous transactions? Do they occur often in PL/SQL code?

Yes.

Thanks!

Stephen

On Wed, Jan 11, 2017 at 08:56:23PM +0100, Fabien COELHO wrote:
> 
> >I think we need to focus on things that _can't_ be done first, rather
> >than things that require porting, e.g. until we had savepoints, you
> >couldn't migrate an application that needed it.  It wasn't a question of
> >porting --- there was just no way to port it.
> >
> >Those _missing_ pieces should be a priority.
> 
> Nested/autonomous transactions? Do they occur often in PL/SQL code?

Yes, they do based on the number of "I can't port from Oracle"
complaints we used to get, perhaps related to exceptions.  Once we had
them, the complaints of that type disappeared.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

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

On Wed, Jan 11, 2017 at 03:08:58PM -0500, Bruce Momjian wrote:
> On Wed, Jan 11, 2017 at 08:56:23PM +0100, Fabien COELHO wrote:
> > 
> > >I think we need to focus on things that _can't_ be done first, rather
> > >than things that require porting, e.g. until we had savepoints, you
> > >couldn't migrate an application that needed it.  It wasn't a question of
> > >porting --- there was just no way to port it.
> > >
> > >Those _missing_ pieces should be a priority.
> > 
> > Nested/autonomous transactions? Do they occur often in PL/SQL code?
> 
> Yes, they do based on the number of "I can't port from Oracle"
> complaints we used to get, perhaps related to exceptions.  Once we had
> them, the complaints of that type disappeared.

Oh, I was talking about savepoints/nested-transactions, not autonomous
transactions.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

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

On 01/11/2017 11:32 AM, Pavel Stehule wrote:
>
>
> We have a schemas instead - the PostgreSQL schema is close to Oracle
> packages.

No. It isn't.

A Package is essentially a class with dependencies. It has nothing to do 
with schemas outside of being named qualified. For example:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/packages.htm#i4362

JD

-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

On Wed, Jan 11, 2017 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>> We have a schemas instead - the PostgreSQL schema is close to Oracle
>> packages.
>
> No. It isn't.

I'm gonna say "yeah, it is".

And that's all I will say about this topic.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2017-01-11 19:57 GMT+01:00 Joshua D. Drake <jd@commandprompt.com>:

-hackers,

I know we have talked about this before but today it was impressed upon me rather firmly. I presented a Webinar: Postgres for Oracle People. The attendees were 90% pl/pgsql developers. 330 people registered for an event that was only allowed to host 100 people. The webinar went on for 2 hours. (it was only scheduled for one hour, that is how interactive it was)

By far the tagline of this webinar from attendees was, "We can not port without packages"

So this is a reality. If we want tried and true Oracle developers to port to PostgreSQL, we must provide some level of package capability.

There are some that would say we don't need them. You are right, we don't need them. We should however want them if we want to continue to stomp through the business sector and continue growth.

We have a schemas instead - the PostgreSQL schema is close to Oracle packages.

What we cannot to substitute are package variables, now - see my proposal for session variables.

Now I am working on migration some large Oracle project - I see more significant issues

1. no good tools - ora2pg do lot of work, but the PL/SQL -> PL/pgSQL migration support is basic

2. some things in Postgres are different - boolean type, enum types, date type, OUT parameters ..

3. some things are really different - NULL versus empty string

4. there are not good tools for postprocessing PL/pgSQL beautifier (formatter), SQL formatter

5. The developers still using Oracle outer joins - there are not 100% automatic migration

6. missing some common patterns for deployment, tests for really big set of code.

Now I work on migration about 500K rows - and it is terrible work. It is 20 years old project - lot of code is not clean, It is hard to migrate, it is hard to clean. Sure, there is not one line of tests.

If we miss some, then it is modern robust tool for migration - big thanks to ora2pg maintainers and developers - without it, there is nothing free.
 

Regards

Pavel

Hi,

I'm currently working on release 19.0 of Ora2Pg, I hope to get it out this weekend. This release has a major rewrite of the pl/psql rewriter. Some of the issues you've reported to me Pavel are already solved in this branch, some other have been fixed after your reports. The rewriter has no more limitation in rewriting function call like decode(), previous version was failing to rewrite function call when an other function or sub select was called inside.

Ora2Pg has always used schema to replace package and I think it is the good equivalent to Oracle's package, we don't need more. The only thing that is missing are global variables. Release 19.0 of Ora2Pg will try to address this problem by exporting global variables declared into the package as PostgreSQL user defined custom variable and replace all call to these variables in the plpgsql code by

     current_setting('schema_name.var_name')::var_type

and all affectation of these variables by

    SELECT / PERFORM set_config('schema_name.var_name', var_value, false);

This works great but the only difference with Oracle's global variables is that they are visible outside the schema where, in Oracle, they are only visible in the package scope when declared in the package body. Perhaps we can work on having these custom variables visible only in a particular schema or some other mechanism that made them accessible from the plpgsql code only. Ora2Pg doesn't propose any solution to cursors declared as global variable yet.

Ora2Pg can fully rewrite Oracle's function with autonomous transaction using a wrapper with a call to dblink or pg_background if you enable it. It's just works perfectly.

About Oracle's OUTER JOIN notation, (+), next release of Ora2Pg will be able to rewrite simple form of RIGHT OUTER JOIN, LEFT OUTER JOIN will comes soon. Note that there is no automatic tool to rewrite the Oracle outer join syntax, but you can use TOAD to convert the queries into ANSI syntax unfortunately query per query and manually. Next version will also add better support to export Oracle Text Search indexes using pg_trgm, unaccent and FTS.

Including a SQL and plpgsql code beautifier is also in my todo list, probably available in next major version 20.

In my opinion, Ora2Pg can do more automatic works but it need some more developments. I would like to give all my time to improve this project and give you a better tool but this is not really possible for the moment and unfortunately my spare time is not extensible. I'm doing my best to get out more releases, your reports/feedbacks help me a lot to add more automatic migration code. I don't think it is possible to have a 100% automatic migration because there will always be some things that need manual rewrite, like point 3, I don't think we want stuff like NULL equal EMPTY. There is also tons of external modules like DBMS_* that can be compared to extension, but if every one share is work on migration perhaps we can save more of time.

Regards,

-- 
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org

On 01/11/2017 04:12 PM, Craig Ringer wrote:

> What aspects / features of packages were the key issues?

Unfortunately we didn't get too far into it because the webinar was 
about Postgres specifically. That said, I have been doing some followup. 
Here is some of it:

because packages[1]

o break the dependency chain (no cascading invalidations when you 
install a new package body -- if you have procedures that call 
procedures -- compiling one will invalidate your database)

o support encapsulation -- I will be allowed to write MODULAR, easy to 
understand code -- rather then MONOLITHIC, non-understandable procedures

o increase my namespace measurably. package names have to be unique in a 
schema, but I can have many procedures across packages with the same 
name without colliding

o support overloading

o support session variables when you need them

o promote overall good coding techniques, stuff that lets you write code 
that is modular, understandable, logically grouped together....

Note: I am not arguing the technical merits here. My goal is 100%, how 
do we get Oracle folks a true Open Source Oracle alternative.

As I get more from people, I will post.

Sincerely,

JD

1. 
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537



-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

On 01/12/2017 03:35 PM, Craig Ringer wrote:
>

>
> So far that's all "that'd be nice, but isn't a technical barrier" stuff.
>
> Package variables for example. When _do_ you _need_ them? For what? (I'm
> aware of some uses but "when you need them" helps us not at all).
>

Well my answer would be, "because we want Oracle people to have an easy 
time migrating". I know that isn't the -hackers answer but clearly there 
is a demand for them. I would note that EDB Advanced server supports 
them, exactly because there is a demand for them.

Again, I am not making the technical argument here. I don't have the 
time to research it. I am making a usability argument for a potentially 
huge portion of database users to allow PostgreSQL to be more attractive 
to them, argument.

I also received this today:

"""
Well, packages make programming much easier. Not only do you keep 
related procedures together, you can also have private package variables 
and the package initialization. Also, packages are units of security, so 
you can grant permissions on the package to the entire group of users 
and if you later modify the package and add a function, you don't need 
to grant it separately.
"""

Sincerely,

JD



-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

On 13 January 2017 at 16:49, Wolfgang Wilhelm <wolfgang20121964@yahoo.de> wrote:
> - Devs just don't want to change (some) code. Everybody seems to have code
> with huge technical debt which is best not to be touched. This code should
> have an easy "move code from Oracle to PostgreSQL", best case by not forcing
> the devs to look at this scary code.

That's kind of offsetting their technical debt onto us, though, and by
extension other PostgreSQL users.

Support compatibility stuff we don't need, that doesn't really benefit
other users, just so they can do less porting and cleanup work.

I'm 100% for implementing _useful_ features based on users' migration
needs from other DBMSes. Even ones that aren't that useful, but come
at low cost to us. But introducing whole new systems to make porting a
little easier does not thrill me.

Also, that's where EDB's market is, and I don't personally feel any
desire to push community PostgreSQL in the Oracle compatibility
direction. Porting tools, sure. Useful features, sure. Direct
compatibility, meh.

I guess what I'm saying is that if someone wants PostgreSQL to have
packages, they need to have:

* A design that can fit in with PostgreSQL
* Solid benefits beyond "makes life easier for Oracle users" to
justify each feature/change
* Funding/time to make it happen

So far, I haven't seen anyone with one of those, let alone all three.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Wolfgang Wilhelm wrote
> - The more difficult a database change including rewriting of code will
> get the less likely you'll find something paying for it. In my case there
> is a list of reasons from the customer _not_ to switch from Oracle to
> PostgreSQL. Besides more obvious reasons like APEX applications on the
> list there are things like "complicated PL/SQL code e.g. ... packages..."
> (whatever complicated is). Lots of the other reasons on that list begin to
> blur because of the changes of the recent versions or the near future like
> parallelisation or working on partitions.
> Of course there are some questions about style, maintainability... But
> this would be another post.

We are a similar shop: mostly Oracle and increasingly more Postgres.

But we essentially stopped (or are in the process of) using packages
altogether - /because/ of maintainability. If a package contains more then
just a single procedure it's impossible for two devs to work on different
procedures because the package body still needs to be a *single* source file
(which sometimes means: a single file with 10 or 20 procedures). Wherever we
have the chance we started migrating packages into standalone procedures.

Which is a bit cumbersome given Oracle's limit on 30 characters for
identifiers - but it still increases maintainability. And one of the
advantages given for packages was the increase in namespace availability
which is much easier with Postgres anyway.

Just my 0.02€






--
View this message in context: http://postgresql.nabble.com/Packages-Again-tp5938583p5938892.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

OK, I’ll bite…

> On Jan 13, 2017, at 10:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> I have not clean feeling from this - I am pretty sure so I am afraid schizophrenic  between MODULES, SCHEMAS. Nested
schemasincrease complexity of searching complexity and breaks a logic database.schema.object 
Yes my proposal to nest schemata is “radical” and this community is not falling into that camp.
But there is nothing holy about database.schema.object.attribute
.
>
> Currently almost all in PostgreSQL PL design is primitive, but that means pretty simple too.
We are having > 30,000 functions with a total of millions of lines of code.

You are describing a self fulfilling prophecy here.
As long as the community codebase only caters to its existing users you will not see a change in the usage pattern of
thebase. 

> It is hard to see a advantages of this proposal.

At least I tried :-)

Serge

> On Jan 13, 2017, at 11:11 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> With Postgres we should to think much more about other PL - there is not only PL/pgSQL. So any what we create should
beavailable for any PL. Our PLpgSQL is based on total different technology design - so some benefits of sharing
compiledcode across databases has not too value in Postgres. 
Let me stress one last point:
MODULE’s are 100% orthogonal to PLpgSQL as implement by SFDC and also orthogonal to SQL PL as implemented by DB2.
Modules can (and do for us) contain C-functions of example.
Similarly when the community provides provides server side session variables I have no doubt they will integrate with
MODULE’swith very little work. 

It’s a DDL and name resolution game, predominantly

Cheers
Serge

On Fri, Jan 13, 2017 at 12:35 PM, Serge Rielau <serge@rielau.com> wrote:

> Yes my proposal to nest schemata is “radical” and this community
> is not falling into that camp.
> But there is nothing holy about database.schema.object.attribute

It is mandated by the U.S. and international SQL standard documents.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jan 13, 2017 at 3:44 PM, Serge Rielau <serge@rielau.com> wrote:
> And sometimes the community DOES go its own way rather than implementing the standard. For example by rejecting the
MERGEstatement in favor of another syntax and semantic.
 

That's total nonsense.

MERGE isn't UPSERT, and isn't even in competition with UPSERT as a
feature. I've written reams of text explaining why this is so in
precise detail, with reference to the implementations of all other
major systems [1][2]. The general consensus is that we might one day
have both UPSERT and MERGE, just like Teradata. They really are that
different that that would be perfectly reasonable. Any application
that uses MERGE and assumes UPSERT-like guarantees should be assumed
broken. We didn't diverge from the SQL standard on a whim. This was
discussed, on and off, for over a year.

[1] https://wiki.postgresql.org/wiki/UPSERT#MERGE_disadvantages
[2] https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com
-- 
Peter Geoghegan

On Fri, Jan 13, 2017 at 8:56 PM, Serge Rielau <serge@rielau.com> wrote:
>> That's total nonsense.
>>
>> MERGE isn't UPSERT….
>
> Peter,
> you are misreading what I wrote. I did not allege that PostgreSQL did the wrong thing. And you are essentially
confirmingthat there was debate and MERGE deemed to be not what was wanted. So PG, with reason, went with something not
inthe standard. 
>
> That is precisely my point!

I'm sorry for being so blunt. That was unnecessary. I thought that you
were citing that as a negative counterexample, rather than a neutral
or positive one.

Still, it's true that MERGE has very little overlap with UPSERT, both
as specified by the standard, and as implemented in practice by both
SQL Server and Oracle. The Oracle docs introduce MERGE with a
statement that is something along the lines of "MERGE is a way to
combine INSERT, UPDATE, and DELETE into one convenient DML statement".
MERGE is most compelling when performing bulk loading. That being the
case, in my mind MERGE remains something that we really haven't turned
our back on at all.

--
Peter Geoghegan

On 1/13/17 10:56 PM, Serge Rielau wrote:
> But what irks me in this debate is that any reasoned and detailed
> argumentation of value of the principle itself is shut down with
> un-reasoned and un-detailed one-liners.
> “I’m not convinced” is not an argument.
> Counterpoints require content. Something starting with “because …”

+1.

I really can't fathom how someone can flatly say that a nested namespace 
is a dumb idea. Your filesystem does this. So does plpgsql. I could 
absolutely make use of nested "schemas" (or make it some other feature 
if "nested schema" offends you so much).

I agree that a nested namespace might violate ANSI (depending on if you 
overload schema/module), and that it might be hard to do with how 
Postgres currently works. And those may be reason enough not to attempt 
the effort. But those have *nothing* to do with how useful such a 
feature would be to users.

This is similar to the 10+ years users would ask for "DDL triggers" and 
get jumped all over because of how hard it would be to actually put a 
trigger on a catalog table. Thankfully someone that knew the code AND 
understood the user desire came up with the notion of event triggers 
that put hooks into every individual DDL command. Users got what they 
wanted, without any need to put "real triggers" on catalog tables.

FWIW, what I wish for in this area is:

- SOME kind of nested namespace for mulitple kinds of objects (not just 
functions)
- A way to mark those namespaces (and possibly other objects) as private.
- A way to reference extensions from other extensions and deal with 
extensions being moved to a different schema (or namespace).
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan <pg@heroku.com> wrote:
> MERGE isn't UPSERT, and isn't even in competition with UPSERT as a
> feature. I've written reams of text explaining why this is so in
> precise detail, ...

No matter how much text you write, I doubt that I will ever believe
that statement.

Leaving that aside, Serge is entirely right about the underlying
principle.  We extend the standard all the time.  The only policy we
have is that we try pretty hard not to adopt standard syntax with
non-standard semantics.  If we pick our own semantics we pick our own
syntax, too, so as not to get in the way of later attempts to
implement the standard syntax with the standard semantics.

Of course, the other principle here is that the people who write the
code get a large share in deciding what ultimately happens.  If
somebody submits an actual patch in this area, fine.  If this is just
an argument about what would be better for someone else to implement,
it's mostly a waste of time.  Anybody who does work in this area is
likely to have their own strong opinion on what should be implemented
- and they are likely to implement precisely that thing.  That doesn't
guarantee acceptance, but it gets you further than bikeshedding from
the sidelines.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/17/2017 09:26 AM, Robert Haas wrote:
> On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan <pg@heroku.com> wrote:
>> MERGE isn't UPSERT, and isn't even in competition with UPSERT as a
>> feature. I've written reams of text explaining why this is so in
>> precise detail, ...


Hello,

This is the webinar that started this whole thread (well the original 
thread, not this weird MERGE/UPSERT stuff):

https://www.commandprompt.com/blog/postgresql_for_oracle_people/

Thank you to everyone that responded. You will see in this Webinar that 
at least from the Oracle people perspective, PostgreSQL is not an option 
unless it has packages.

The other item that people bring up a few times is Oracle Forms but as 
that is actually external (although dependent) on Oracle, I don't see 
that as our responsibility.

Sincerely,

JD


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

On 3 February 2017 at 14:27, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> 2017-01-20 17:01 GMT+01:00 Joshua D. Drake <jd@commandprompt.com>:
>>
>> On 01/17/2017 09:26 AM, Robert Haas wrote:
>>>
>>> On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan <pg@heroku.com> wrote:
>>>>
>>>> MERGE isn't UPSERT, and isn't even in competition with UPSERT as a
>>>> feature. I've written reams of text explaining why this is so in
>>>> precise detail, ...
>>
>>
>>
>> Hello,
>>
>> This is the webinar that started this whole thread (well the original
>> thread, not this weird MERGE/UPSERT stuff):
>>
>> https://www.commandprompt.com/blog/postgresql_for_oracle_people/
>>
>> Thank you to everyone that responded. You will see in this Webinar that at
>> least from the Oracle people perspective, PostgreSQL is not an option unless
>> it has packages.
>>
>> The other item that people bring up a few times is Oracle Forms but as
>> that is actually external (although dependent) on Oracle, I don't see that
>> as our responsibility.
>
>
> DB2 propose using schemas instead packages
>
> https://www.ibm.com/developerworks/data/library/techarticle/dm-0711zubiri/
>
> Now I am working with Oracle application - and I try to understand to Oracle
> developers - often pattern is using "Oracle schema" as database - and then
> the packages has sense. But there is not a mapping "Oracle schema" =
> "PostgreSQL schema" - and packages is a redundant concept in Postgres (and
> in all db, where the schema are like namaspace - MSSQL, DB2, MySQL).

It sounds like we could benefit from a documentation section
"packages" that describes how to get package-like behaviour (minus the
pre-compiled updates) from Pg using schemas and, once added, secure
variables.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

>
> Still I little bit afraid about nesting - Postgres allows function overloading with specific mechanism of selecting
calledfunction. Sometimes it is problematic now, and the this structure is flat.  
>
> I like a idea of more close relation between function and schema. This means implicit setting of SEARCH_PATH to
functionschema. It is simply but powerful idea.  
>
> CREATE FUNCTION mod.func()
> AS $$ $$ MODULE VISIBILITY
>
> can be dynamically executed like
> CREATE FUNCTION mod.fun()
> AS $$
>   SET SEARCH_PATH TO "mod,$SEARCH_PATH";
>   ..
> $$;
Ah, yes.
It is my understanding that PG treats functions more like macros.
That is the search_path for queries inside the function is not fixed to the one in effect when the function was
defined.
This does have advantages in some cases, but it hurts in this case.
What you are describing is syntax to force that in some form or other.
This is actually not hard to do at all.
PG already records the search path in the function cache (I think) and plan source cache (I know), just not in pg_proc.
If PG supported this functionality it would improve the mapping.

> I understand well so missing nested structures is big complication when you do port from environment where this
functionalityis used. But the nesting means usually more complex solution - and in these days I don't believe so it is
necessary.PLpgSQL doesn't allow nested functions - it is strongly reduced against original PL/SQL - and it is visible
onlywhen you do migration from Oracle.  
I’m not talking about nested PLpgSQL function definition. That is indeed rather advanced and while I have seen it (and
its’ssupported in DB2 of that reason) I would not consider it high priority. 
Multiple packages in different schema are common however because applications use schemas for versioning. That’s why
flatteningthe package into a schema as supported today does not work in these cases. 

Cheers
Serge

On Fri, Feb  3, 2017 at 07:59:26AM +0100, Pavel Stehule wrote:
> It should be documented and presented (who is read a documentation? :-))
> 
> It is not only PostgreSQL issue, same issue has to have any other databases.
> The Oracle architecture is very specific and often question is, how to map
> Oracle database to PostgreSQL. A common questions - how schema should be used,
> where schema should be used, where database should be used. What is practical
> limit of size of PostgreSQL catalogue.

I did write a blog entry on this topic:
http://momjian.us/main/blogs/pgblog/2012.html#April_23_2012

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

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