Re: [HACKERS] Packages: Again

[Bruce Momjian]

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  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 +


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-02-03 23:24 GMT+01:00 Serge Rielau :

>
> >
> > Still I little bit afraid about nesting - Postgres allows function
> overloading with specific mechanism of selecting called function. 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 function schema. 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.
>

Only SQL functions can be used like macros - It is not possible with PLpgSQL


> 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.
>

yes. This "functions pined to schema" should not be necessary implemented
with injection to SEARCH_PATH. We can introduce some internal flag, so the
explicit change of SEARCH_PATH doesn't break it. But this behave should be
allowed for specially marked functions. It can ensure so functions from
same schema is preferred without any setting of SEARCH_PATH. What can have
a security benefit.


> 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 functionality is 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 only when 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’s supported 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 flattening the package
> into a schema as supported today does not work in these cases.
>

I used nested functions just like example.

Any recursive structure can be flatted/unfolded.

>
> Cheers
> Serge

Re: [HACKERS] Packages: Again

[Serge Rielau]

> 
> Still I little bit afraid about nesting - Postgres allows function 
> overloading with specific mechanism of selecting called function. 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 function schema. 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 functionality is 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 only when 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’s supported 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 flattening the package into a schema as 
supported today does not work in these cases.

Cheers
Serge

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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-02-03 18:30 GMT+01:00 Serge Rielau :

> > DB2 propose using schemas instead packages
>> >
>> > https://www.ibm.com/developerworks/data/library/
>> techarticle/dm-0711zubiri/
>
> That article by Adriana is 6 years ago and was written actually while we
> implemented MODULE’s for DB2 9.7. So yes, when you don’t have modules,
> schemata are the way to go in the same way as when all you have is a hammer
> everything is a nail.
> We considered MODULEs an absolute must to get functional equivalency to
> Oracle PL/SQL packages. Also they wouldn’t take up so much space in the
> standard if they would be deemed to provide no function...
>

The DB2 Modules is not exactly ANSI SQL modules (but it's maybe better -
the ANSI concept is maybe obsolete) - and if I remember the SQL/PSM the
modules related part is few percent only.



> > 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).
>
> I have never heard the claim that database in Oracle matches schema in
> other DBMS.
> In my experience Oracle is well in line on the schema front with the
> exception of the one-to-one relationship between schema and user.
>
> The database-is-really-a-schema mapping is something we (at DB2)
> traditionally associated with Sybase and SQL Server migrations where we saw
> plenty of small databases with cross database queries.
>
> Having said all that I think schemata are quite powerful in Postgres, not
> least because of the clean usage of search_path for all object resolution
> and schema  being independent of user. They get us a fair ways.
> The main gap remains the inability to do any sort of nesting.
> To have two “package-like-things” with the same name.
>

Still I little bit afraid about nesting - Postgres allows function
overloading with specific mechanism of selecting called function. 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 function schema. 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";
  ..
$$;

It is simple when schema are not nested. But when we allow nested, then
implementation will be significantly harder - is possible to see inside
nested schema, or see to outer schema, or visibility is flat, and nested
objects should be qualified every time?


>
> I’m not going to repeat myself on that one and bore everyone.
> My thinking on modules is someone reflected here:
> https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/
> module?lang=en
>
>
I understand well so missing nested structures is big complication when you
do port from environment where this functionality is 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 only when you do
migration from Oracle.

My fresh experience from porting some old school Oracle application to
Postgres. Now, probably the biggest problem is small detail - empty string
is NULL in Oracle.

Regards

Pavel



> Cheers
> Serge
>

Re: [HACKERS] Packages: Again

[Serge Rielau]

> DB2 propose using schemas instead packages
>
> https://www.ibm.com/developerworks/data/library/techarticle/dm-0711zubiri/ 
> [https://www.ibm.com/developerworks/data/library/techarticle/dm-0711zubiri/] 
> That article by Adriana is 6 years ago and was written actually while we 
> implemented MODULE’s for DB2 9.7. So yes, when you don’t have modules, 
> schemata are the way to go in the same way as when all you have is a hammer 
> everything is a nail. We considered MODULEs an absolute must to get 
> functional equivalency to Oracle PL/SQL packages. Also they wouldn’t take up 
> so much space in the standard if they would be deemed to provide no 
> function... > 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). I have 
> never heard the claim that database in Oracle matches schema in other DBMS. 
> In my experience Oracle is well in line on the schema front with the 
> exception of the one-to-one relationship between schema and user.
The database-is-really-a-schema mapping is something we (at DB2) traditionally 
associated with Sybase and SQL Server migrations where we saw plenty of small 
databases with cross database queries.
Having said all that I think schemata are quite powerful in Postgres, not least 
because of the clean usage of search_path for all object resolution and schema 
being independent of user. They get us a fair ways. The main gap remains the 
inability to do any sort of nesting. To have two “package-like-things” with the 
same name. I’m not going to repeat myself on that one and bore everyone. My 
thinking on modules is someone reflected here: 
https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/module?lang=en
Cheers Serge

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-02-03 7:34 GMT+01:00 Craig Ringer :

> On 3 February 2017 at 14:27, Pavel Stehule 
> wrote:
> >
> >
> > 2017-01-20 17:01 GMT+01:00 Joshua D. Drake :
> >>
> >> On 01/17/2017 09:26 AM, Robert Haas wrote:
> >>>
> >>> On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan 
> 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.
>

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.


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

Re: [HACKERS] Packages: Again

[Craig Ringer]

On 3 February 2017 at 14:27, Pavel Stehule  wrote:
>
>
> 2017-01-20 17:01 GMT+01:00 Joshua D. Drake :
>>
>> On 01/17/2017 09:26 AM, Robert Haas wrote:
>>>
>>> On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan  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


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-20 17:01 GMT+01:00 Joshua D. Drake :

> On 01/17/2017 09:26 AM, Robert Haas wrote:
>
>> On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan  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).

Regards

Pavel


>
> 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.
>

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-20 17:01 GMT+01:00 Joshua D. Drake :

> On 01/17/2017 09:26 AM, Robert Haas wrote:
>
>> On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan  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.


I see there request on package functions, that can be realised with our
schemas - schema function is +/- equal - and better support for package
variables - there is a patch for schema session variables.

Regards

Pavel


>
>
> 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.
>

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

On 01/17/2017 09:26 AM, Robert Haas wrote:

On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan  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.


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

Re: [HACKERS] Packages: Again

[Robert Haas]

On Fri, Jan 13, 2017 at 7:24 PM, Peter Geoghegan  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


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

Re: [HACKERS] Packages: Again

[Jim Nasby]

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)


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

Re: [HACKERS] Packages: Again

[Peter Geoghegan]

On Fri, Jan 13, 2017 at 8:56 PM, Serge Rielau  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 confirming that there was debate and 
> MERGE deemed to be not what was wanted. So PG, with reason, went with 
> something not in the 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


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

Re: [HACKERS] Packages: Again

[Serge Rielau]

On Fri, Jan 13, 2017 at 4:24 PM, Peter Geoghegan  wrote:
On Fri, Jan 13, 2017 at 3:44 PM, Serge Rielau  wrote:
> And sometimes the community DOES go its own way rather than implementing the 
> standard. For example by rejecting the MERGE statement in favor of another 
> syntax and semantic.

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 confirming that 
there was debate and MERGE deemed to be not what was wanted. So PG, with 
reason, went with something not in the standard.
That is precisely my point!
Packages are not modules are not nested schemata either. And the argument that 
nested schemata are a no-go because of the standard is invalid for the same 
reason discarding an option other than MERGE because that’s the only thing in 
the standard was invalid.
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 …”
If the community does not believe that there is value in a more refined 
grouping of objects than a schema the discussion in DOA. If there is consensus 
that there is value one can debate about the best semantics and language 
covering it.
Cheers Serge

Re: [HACKERS] Packages: Again

[Peter Geoghegan]

On Fri, Jan 13, 2017 at 3:44 PM, Serge Rielau  wrote:
> And sometimes the community DOES go its own way rather than implementing the 
> standard. For example by rejecting the MERGE statement 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_fcvo5xm7hrel3...@mail.gmail.com
-- 
Peter Geoghegan


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

Re: [HACKERS] Packages: Again

[Serge Rielau]

On Fri, Jan 13, 2017 at 2:46 PM, Kevin Grittner  wrote:
On Fri, Jan 13, 2017 at 12:35 PM, Serge Rielau  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. Compliance 
to the standard does not prohibit extensions to the standard. That is, in fact, 
how the standard is progressed.
The SQL/PSM standard introduced another “dot”: database.schema.module.object
And sometimes the community DOES go its own way rather than implementing the 
standard. For example by rejecting the MERGE statement in favor of another 
syntax and semantic.
Cheers Serge

Re: [HACKERS] Packages: Again

[Kevin Grittner]

On Fri, Jan 13, 2017 at 12:35 PM, Serge Rielau  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


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-13 22:16 GMT+01:00 Serge Rielau :

> On Fri, Jan 13, 2017 at 12:45 PM, Pavel Stehule 
> wrote:
>
> show patch and show a advantages against schema, please.
>
> I have tried to describe the advantage.
> If the community doesn’t agree, that’s fine.
> I do not see how expending the effort of back porting a patch (and getting
> clearance for that from my employer) will enhance my argument.
>

This theme is opened, and any discussion is welcome - it is not simple
task, to design any solution that be natural in PostgreSQL environment

Regards

Pavel


>
> Cheers
> Serge
>
>
>

Re: [HACKERS] Packages: Again

[Serge Rielau]

On Fri, Jan 13, 2017 at 12:45 PM, Pavel Stehule  
wrote: show patch and show a advantages against schema, please. I have tried to 
describe the advantage. If the community doesn’t agree, that’s fine. I do not 
see how expending the effort of back porting a patch (and getting clearance for 
that from my employer) will enhance my argument.
Cheers Serge

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-13 20:38 GMT+01:00 Serge Rielau :

>
> > On Jan 13, 2017, at 11:11 AM, Pavel Stehule 
> wrote:
> >
> > With Postgres we should to think much more about other PL - there is not
> only PL/pgSQL. So any what we create should be available for any PL. Our
> PLpgSQL is based on total different technology design - so some benefits of
> sharing compiled code 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’s with very
> little work.
>
> It’s a DDL and name resolution game, predominantly
>

show patch and show a advantages against schema, please.

Regards

Pavel


> Cheers
> Serge

Re: [HACKERS] Packages: Again

[Serge Rielau]

> On Jan 13, 2017, at 11:11 AM, Pavel Stehule  wrote:
> 
> With Postgres we should to think much more about other PL - there is not only 
> PL/pgSQL. So any what we create should be available for any PL. Our PLpgSQL 
> is based on total different technology design - so some benefits of sharing 
> compiled code 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’s with very little work.

It’s a DDL and name resolution game, predominantly

Cheers
Serge

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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-13 19:35 GMT+01:00 Serge Rielau :

>
> > On Jan 13, 2017, at 10:23 AM, Pavel Stehule 
> wrote:
> >
> > I have not clean feeling from this - I am pretty sure so I am afraid
> schizophrenic  between MODULES, SCHEMAS. Nested schemas increase 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
>

sure not - but lot of logic in SQL parser and PLpgSQL parser is based on it.


> .
> >
> > 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.
>

I understand so your working life is pretty hard :).


>
> 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 the base.
>
> > It is hard to see a advantages of this proposal.
>
> At least I tried :-)
>

I would be careful to translate a Oracle concept to PostgreSQL - The
packages is Ada language concept - and there has clean role. In PL/SQL
Oracle used packages like we used schema because Oracle has different
concept of terms "database", of term "schema".  The packages in Postgres is
more redundant than in Oracle. More the packages and related features are
probably most difficult PL/SQL feature. Lot of people don't understand well
- and it is not surprise for me, because PL/SQL is really hard mix of two
very different worlds.

I agree so there is some gap - there is nothing like package variables,
package constants.  Can be nice to fill it.

With Postgres we should to think much more about other PL - there is not
only PL/pgSQL. So any what we create should be available for any PL. Our
PLpgSQL is based on total different technology design - so some benefits of
sharing compiled code across databases has not too value in Postgres.

Maybe I am starting be old :) - I don't believe so stronger tools helps do
things better - like Java - some applications are pretty good, some are the
big heap of shit - and due strong language this heap is sometimes pretty
big :)

If you need nested schemas, maybe you do some scary things, that should be
better solved in application server.

So I am not 100% against, but really I am sceptic if it is good idea. We
don't design Postgres as platform for migration legacy Oracle code - on
second hand we should not to create artificial breaks against this
migrations.

Regards

Pavel





> Serge
>
>
>
>

Re: [HACKERS] Packages: Again

[Serge Rielau]

> On Jan 13, 2017, at 10:23 AM, Pavel Stehule  wrote:
> 
> I have not clean feeling from this - I am pretty sure so I am afraid 
> schizophrenic  between MODULES, SCHEMAS. Nested schemas increase 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 the base.

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

At least I tried :-)

Serge





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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-13 18:35 GMT+01:00 Serge Rielau :

>
>> * 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.
>>
> OK, I’ll bite…
>
> * In SFDC’s extension of PostgreSQL we nest namespaces.
>   This was done before my time here, but its very stable. It's easy to
> keep merged and not that much code.
>   To make the special semantics of these nested namespaces evident however
> we leaned on the SQL/PSM standard and call them MODULE’s.
>   Unlike the standard our MODULEs share the namespace (no pun intended)
> with regular schemata which seems practical and limits confusion when
> referencing
>   a module without schema qualification.
>
>   We did extend upon the standard with ALTER MODULE .. ADD [FUNCTION |
> TYPE | …] syntax.
>   Just like few users create a new schema with tables in one statement,
> no-one actually creates a module with content in one statement (unless, as
> in Oracle they have to).
>   This was done before my time as well, but parallels what we implemented
> did in DB2 for the reasons described earlier in this thread.
>   You want to be able to modify members of a module separately.
>
>   Starting with a blank slate I do wonder whether simply allowing nesting
> of namespaces would be sufficient to achieve the vast majority of the goal.
>   I.e. CREATE SCHEMA .
>   The rest… follows trivially :-)
>
> * Benefits:
>   a) The files on my computer are organized in directories that have more
> than one level of nesting.
>I simply can’t imagine having thousands or tens of thousands of
> objects lying around and only one coarse way of subdividing them.
>   This is compounded by the desire you version. I want to the same
> names for objects across multiple concurrently present versions of the
> schema.
>If I consume the schema for the version the entire schema for a
> version becomes a flat jumple.
>   b) Access control
>   By putting things that belong together actually together in an
> explicit way I can achieve scoping without having to resort to permissions.
>   I can simply postulate that all objects in a module are private
> unless they are published.
>   Access control happens at the module level.
>  This is no different than library management on your OS.
>  You don’t chmod the individual entry points!
>  c) Scoping
>  Similar to the above, but more related to search path.
>  Within a module I can be assured that any unqualified references will
> first resolve within the module.
>  No mucking with the search path by anyone will cause me to execute
> the wrong function, resolve to the wrong type etc.
>
>   Simply put: As long as we agree that users want to implement substantial
> server side logic the conclusion that standard programming
>   abstractions such as classes and member functions are a boon seems to be
> obvious.
>
>   Note that I have been careful not to tie modules too strongly to
> specific types. Conceptually I see nothing from with a module, table, view,
> etc.
>   It’s just a bit more “far out” since there is AFAIK no precedence.
>
> * IFF our existing efforts (fast defaults and executor runtime
> improvements) to work with the community are successful I would happily
> lobby
>   to at least port our module code to the community codebase. We can take
> it from there.
>

I have not clean feeling from this - I am pretty sure so I am afraid
schizophrenic  between MODULES, SCHEMAS. Nested schemas increase complexity
of searching complexity and breaks a logic database.schema.object.

Currently almost all in PostgreSQL PL design is primitive, but that means
pretty simple too.

It is hard to see a advantages of this proposal.

Regards

Pavel


> Cheers
> Serge Rielau
> Salesforce.com 
>
>
>
>

Re: [HACKERS] Packages: Again

[Serge Rielau]

> 
> * 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.
OK, I’ll bite…

* In SFDC’s extension of PostgreSQL we nest namespaces.
  This was done before my time here, but its very stable. It's easy to keep 
merged and not that much code.
  To make the special semantics of these nested namespaces evident however we 
leaned on the SQL/PSM standard and call them MODULE’s.
  Unlike the standard our MODULEs share the namespace (no pun intended) with 
regular schemata which seems practical and limits confusion when referencing 
  a module without schema qualification.
  
  We did extend upon the standard with ALTER MODULE .. ADD [FUNCTION | TYPE | 
…] syntax.
  Just like few users create a new schema with tables in one statement, no-one 
actually creates a module with content in one statement (unless, as in Oracle 
they have to).
  This was done before my time as well, but parallels what we implemented did 
in DB2 for the reasons described earlier in this thread.
  You want to be able to modify members of a module separately.

  Starting with a blank slate I do wonder whether simply allowing nesting of 
namespaces would be sufficient to achieve the vast majority of the goal.
  I.e. CREATE SCHEMA .
  The rest… follows trivially :-)

* Benefits:
  a) The files on my computer are organized in directories that have more than 
one level of nesting.
   I simply can’t imagine having thousands or tens of thousands of objects 
lying around and only one coarse way of subdividing them.
  This is compounded by the desire you version. I want to the same names 
for objects across multiple concurrently present versions of the schema.
   If I consume the schema for the version the entire schema for a version 
becomes a flat jumple.
  b) Access control
  By putting things that belong together actually together in an explicit 
way I can achieve scoping without having to resort to permissions.
  I can simply postulate that all objects in a module are private unless 
they are published.
  Access control happens at the module level.
 This is no different than library management on your OS.
 You don’t chmod the individual entry points!
 c) Scoping
 Similar to the above, but more related to search path.
 Within a module I can be assured that any unqualified references will 
first resolve within the module.
 No mucking with the search path by anyone will cause me to execute the 
wrong function, resolve to the wrong type etc.  

  Simply put: As long as we agree that users want to implement substantial 
server side logic the conclusion that standard programming 
  abstractions such as classes and member functions are a boon seems to be 
obvious.

  Note that I have been careful not to tie modules too strongly to specific 
types. Conceptually I see nothing from with a module, table, view, etc.
  It’s just a bit more “far out” since there is AFAIK no precedence. 

* IFF our existing efforts (fast defaults and executor runtime improvements) to 
work with the community are successful I would happily lobby 
  to at least port our module code to the community codebase. We can take it 
from there.

Cheers
Serge Rielau
Salesforce.com


 

Re: WG: [HACKERS] Packages: Again

[Craig Ringer]

On 13 Jan. 2017 19:16, "Thomas Kellerer" <


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.


I was wondering where the namespace thing came from. Sure,
packagename_funcname I'd cumbersome but it's not exactly hard and we've
been doing it in C since forever.

I'd assumed it was an issue in the opposite direction. PG identifiers being
too short. But it sounds like instead it's people not realising they can do
this.

Re: WG: [HACKERS] Packages: Again

[Thomas Kellerer]

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.


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

Re: [HACKERS] Packages: Again

[Wolfgang Wilhelm]

Hello again,
well, I didn't want to tell you to match in PostgreSQL the technical debt. If I 
made it look so, sorry for my bad english.
No, a "free clone of Oracle" isn't my intention. I don't want to convince 
Oracle evangelists to use PostgreSQL. This is time wasted. But I'd prefer a 
project where the community is thriving and the more members it has the better.
My top point on the list above is making the way away from Oracle smoother and 
that's ways easier when you don't have to argue against arguments like "PG 
doesn't have packages" including that what was mentioned as pro arguments for 
packages by Tom Kyte. This is the best way to stay in a discussion about that 
single topic and have _no_ chance to show them the benefits.

Regards,Wolfgang
 

Pavel Stehule  schrieb am 10:41 Freitag, 13.Januar 
2017:
 

 

2017-01-13 10:11 GMT+01:00 Craig Ringer :

On 13 January 2017 at 16:49, Wolfgang Wilhelm  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.


+1
Regards
Pavel 

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


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




   

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-13 10:11 GMT+01:00 Craig Ringer :

> On 13 January 2017 at 16:49, Wolfgang Wilhelm 
> 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.
>

+1

Regards

Pavel


>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Packages: Again

[Craig Ringer]

On 13 January 2017 at 16:49, Wolfgang Wilhelm  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


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

WG: [HACKERS] Packages: Again

[Wolfgang Wilhelm]

 Hello,
just my 2c on the topic.I work for an IT service provider as developer. Our 
customers are big international companies and they use Oracle a lot. My main 
work is with Oracle enterprise edition. I don't have to care about limitations 
of the smaller versions and don't have experience with users, that is 
enterprises, of that software.

- I wouldn't subscribe to Pavels opinion on the fees. Most Oracle Devs in mid- 
oder large size enterprises don't care about the costs of the database.  They 
even don't know because the purchase department will do the job and they don't 
bandy out how much discount they got from the ridiculous price list. 

- 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.
- 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.

RegardsWolfgang


   

   

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-13 3:07 GMT+01:00 Joshua D. Drake :

> 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.
>

EDB try to emulate Oracle - so some special features are necessary there -
although are redundant to existing PostgreSQL features - packages,
collections, ..


>
> 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.
> """
>

This is possible with our schemas too. I use schemas for package emulation
for Orafce 10 years, and it is working well.

The main problem is in different languages - our "database" <> Oracle
"database", our "schema" <> Oracle "schema"

People doesn't want packages - they want Oracle without any fee


> 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.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

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.


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

Re: [HACKERS] Packages: Again

[Craig Ringer]

On 13 Jan. 2017 00:54, "Joshua D. Drake"  wrote:

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


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).

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

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:0P11_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.


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

Re: [HACKERS] Packages: Again

[Craig Ringer]

On 12 Jan. 2017 02:59, "Joshua D. 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"


What aspects / features of packages were the key issues?

Initialisation?

Variables?

Performance features like pre compilation?

Signing?

Code obfuscation?

...?


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.


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

Re: [HACKERS] Packages: Again

[Gilles Darold]

Le 11/01/2017 à 20:32, Pavel Stehule a écrit :
>
>
> 2017-01-11 19:57 GMT+01:00 Joshua D. Drake  >:
>
> -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 

Re: [HACKERS] Packages: Again

[Robert Haas]

On Wed, Jan 11, 2017 at 3:56 PM, Joshua D. Drake  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


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

Re: [HACKERS] Packages: Again

[Joshua D. Drake]

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.


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-11 21:08 GMT+01:00 Bruce Momjian :

> 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.
>

We have not PL controllable transactions - so some patterns are not
available in our functions.

On second hand - currently all usage of explicit commit/rollback was
related to some Oracle issue (what I know)

1. missing easy debug printing - there was not nothing like RAISE NOTICE -
dbms_output - is poor solution

2. it was workaround for limited transaction size

In 90% it are solutions of issues that are not in Postgres. Can be nice to
have procedures - and it can be benefit for all, but it is not too big gap.
When you use postgres's patterns, then you don't need it - but there are
more work with migration.

Regards

Pavel



>
> --
>   Bruce Momjian  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 +
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Packages: Again

[Bruce Momjian]

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  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 +


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-11 20:56 GMT+01:00 Fabien COELHO :

>
> 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?
>

There is relative well working workaround - ora2pg is able to translate it
to dblink usage.

Sure - native solution can be better - usage pg_background is step forward.

Regards

Pavel


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

Re: [HACKERS] Packages: Again

[Bruce Momjian]

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  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 +


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

Re: [HACKERS] Packages: Again

[Stephen Frost]

Fabien,

* Fabien COELHO (coe...@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


signature.asc
Description: Digital signature

Re: [HACKERS] Packages: Again

[Fabien COELHO]

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.


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-11 20:42 GMT+01:00 Bruce Momjian :

> 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.
>

There are some workarounds for emulation of package variables - and I am
pressing a design of session variables that can be well used like package
variables.

Currently almost all basic functionality (related to PL/SQL) is available
in Postgres. But the migration needs lot of hard manual work.

It is analogy with Cobol systems - some applications are too ugly so more
level emulation is cheaper solution, than migration to some modern :)

Regards

Pavel


>
> --
>   Bruce Momjian  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 +
>

Re: [HACKERS] Packages: Again

[Fabien COELHO]

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.


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

Re: [HACKERS] Packages: Again

[Bruce Momjian]

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  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 +


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

Re: [HACKERS] Packages: Again

[Pavel Stehule]

2017-01-11 19:57 GMT+01:00 Joshua D. Drake :

> -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


>
> 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.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Packages: Again

[Bruce Momjian]

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 also
possible to implement these capabilities in any schema and not use a
separate "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  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 +


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

[HACKERS] Packages: Again

[Joshua D. Drake]

-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.


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