subject:"Re\: \[HACKERS\] PL\/pgSQL 2"


On 09/06/2014 12:47 PM, David G Johnston wrote:

If the language, and the system as a whole, was only used by
perfectionists that do not make errors - and with perfectly clean data -
this adherence to purity would be acceptable.  But the real world is not
that clean and so enhancing the language to meet the needs of the real
world is not crippling the language.  Begin able to state explicitly
that the cardinality of the set I get back must be 1, no more and no
less, doesn't remove the fact that I know I am dealing with a set and
that I simply want to make an assertion as to its properties so that if
a bug 3 layers deep into the application causes something other than 1
row to be affected I know immediately and can invoke the appropriate
action - throw an error.


As I already mentioned in the other thread, those assertions or checks 
do not belong into the PL. If they are desired they should be added to 
the main SQL syntax as COMMAND CONSTRAINT like suggested by Hannu.


Your statement is not limited to PL functions. It is just as valid for 
NORMAL applications.


However, this would be a proprietary extension that is not covered by 
any SQL standard and for that reason alone cannot be the default.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

2014-09-06 Thread David G Johnston

On Sat, Sep 6, 2014 at 12:38 PM, Jan Wieck-3 [via PostgreSQL] <
ml-node+s1045698n5818047...@n5.nabble.com> wrote:

> On 09/06/2014 12:33 PM, Marko Tiikkaja wrote:
>
> > On 2014-09-06 6:31 PM, Jan Wieck wrote:
> >> On 09/06/2014 12:17 PM, Marko Tiikkaja wrote:
> >>> OK, fine.  But that's not what I suggested on the wiki page, and is
> also
> >>> not what I'm arguing for here right now.  What the message you
> referred
> >>> to was about was the condescending attitude where we were told to
> "think
> >>> in terms of sets" (paraphrased), without considering whether that's
> even
> >>> possible to do *all the time*.
> >>
> >> SQL is, by definition, a set oriented language. The name Procedural
> >> Language / pgSQL was supposed to suggest that this language adds some
> >> procedural elements to the PostgreSQL database. I never intended to
> >> create a 100% procedural language. It was from the very beginning, 16
> >> years ago, intended to keep the set orientation when it comes to DML
> >> statements inside of functions.
> >>
> >> No matter how hard you
> >> try to make them special, in my mind they are not.
> >
> > Of course they are.  That's why you have PRIMARY KEYs and UNIQUE
> > constraints.
>
> Then please use those features instead of crippling the language.
>
>
If the language, and the system as a whole, was only used by
perfectionists that do not make errors - and with perfectly clean data -
this adherence to purity would be acceptable.  But the real world is not
that clean and so enhancing the language to meet the needs of the real
world is not crippling the language.  Begin able to state explicitly that
the cardinality of the set I get back must be 1, no more and no less,
doesn't remove the fact that I know I am dealing with a set and that I
simply want to make an assertion as to its properties so that if a bug 3
layers deep into the application causes something other than 1 row to be
affected I know immediately and can invoke the appropriate action - throw
an error.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5818051.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] PL/pgSQL 2


On 09/06/2014 12:33 PM, Marko Tiikkaja wrote:

On 2014-09-06 6:31 PM, Jan Wieck wrote:

On 09/06/2014 12:17 PM, Marko Tiikkaja wrote:

OK, fine.  But that's not what I suggested on the wiki page, and is also
not what I'm arguing for here right now.  What the message you referred
to was about was the condescending attitude where we were told to "think
in terms of sets" (paraphrased), without considering whether that's even
possible to do *all the time*.


SQL is, by definition, a set oriented language. The name Procedural
Language / pgSQL was supposed to suggest that this language adds some
procedural elements to the PostgreSQL database. I never intended to
create a 100% procedural language. It was from the very beginning, 16
years ago, intended to keep the set orientation when it comes to DML
statements inside of functions.

No matter how hard you
try to make them special, in my mind they are not.


Of course they are.  That's why you have PRIMARY KEYs and UNIQUE
constraints.


Then please use those features instead of crippling the language.


Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

2014-09-06 Thread Marko Tiikkaja


On 2014-09-06 6:31 PM, Jan Wieck wrote:

On 09/06/2014 12:17 PM, Marko Tiikkaja wrote:

OK, fine.  But that's not what I suggested on the wiki page, and is also
not what I'm arguing for here right now.  What the message you referred
to was about was the condescending attitude where we were told to "think
in terms of sets" (paraphrased), without considering whether that's even
possible to do *all the time*.


SQL is, by definition, a set oriented language. The name Procedural
Language / pgSQL was supposed to suggest that this language adds some
procedural elements to the PostgreSQL database. I never intended to
create a 100% procedural language. It was from the very beginning, 16
years ago, intended to keep the set orientation when it comes to DML
statements inside of functions.

No matter how hard you
try to make them special, in my mind they are not.


Of course they are.  That's why you have PRIMARY KEYs and UNIQUE 
constraints.



.marko


--
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] PL/pgSQL 2


On 09/06/2014 12:17 PM, Marko Tiikkaja wrote:

OK, fine.  But that's not what I suggested on the wiki page, and is also
not what I'm arguing for here right now.  What the message you referred
to was about was the condescending attitude where we were told to "think
in terms of sets" (paraphrased), without considering whether that's even
possible to do *all the time*.


SQL is, by definition, a set oriented language. The name Procedural 
Language / pgSQL was supposed to suggest that this language adds some 
procedural elements to the PostgreSQL database. I never intended to 
create a 100% procedural language. It was from the very beginning, 16 
years ago, intended to keep the set orientation when it comes to DML 
statements inside of functions.


That means that you will have to think in sets *all the time*. The empty 
set and a set with one element are still sets. No matter how hard you 
try to make them special, in my mind they are not.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

2014-09-06 Thread Marko Tiikkaja


On 2014-09-06 6:06 PM, Jan Wieck wrote:

You can dismiss what we're doing by saying that it doesn't follow the
best practices or we just want an interface for a key-value store or
whatever.  And yes, to some extent, a simple interface for a key-value
store would come in handy.  But we still have the 5-15% (big part of it
being the reporting we need to do) of the code that *doesn't* want that,
*and* we want to use all of the Postgres features where applicable.


The point isn't about best practices.


It got to that point upthread.


The point is that if you want to
ensure that at maximum one row is affected, then qualify it by a unique
set of columns.


And what if you get the set of columns wrong (also consider the presence 
of joins)?  What if someone changes that set of columns?  What if your 
unique indexes have been violated because of a bug in postgres or 
hardware malfunction?  Wouldn't you want the problem to be obvious?



Making PL/pgSQL behave different on UPDATE than SQL to
enforce that by default was simply a misguided design idea.


OK, fine.  But that's not what I suggested on the wiki page, and is also 
not what I'm arguing for here right now.  What the message you referred 
to was about was the condescending attitude where we were told to "think 
in terms of sets" (paraphrased), without considering whether that's even 
possible to do *all the time*.



.marko


--
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] PL/pgSQL 2


On 09/05/2014 10:32 PM, Marko Tiikkaja wrote:

On 2014-09-02 8:52 PM, Kevin Grittner wrote:

Marko Tiikkaja  wrote:


Sounds like in this case you'd only use set-oriented programming
at the end of the transaction, no?


I guess -- more properly I would say "in the final database
transaction for that financial transaction."


Yes, I should have said "financial transaction", but I hit send a bit
too early.


And no, that never
made me wish that plpgsql functions defaulted to throwing errors
for DML statements that affected more than one row.


Fine.  But you should still be able to see the point we're trying to
make.  The number one is special, and it's present everywhere.  If you
want to program defensively, you have to go through a lot of pain right
now.  We're looking for a way to alleviate that pain.  Defaulting to
throwing errors would be one way to do it, but that's not what's being
suggested here anymore.

You can dismiss what we're doing by saying that it doesn't follow the
best practices or we just want an interface for a key-value store or
whatever.  And yes, to some extent, a simple interface for a key-value
store would come in handy.  But we still have the 5-15% (big part of it
being the reporting we need to do) of the code that *doesn't* want that,
*and* we want to use all of the Postgres features where applicable.


The point isn't about best practices. The point is that if you want to 
ensure that at maximum one row is affected, then qualify it by a unique 
set of columns. Making PL/pgSQL behave different on UPDATE than SQL to 
enforce that by default was simply a misguided design idea.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

2014-09-05 Thread Marko Tiikkaja


On 2014-09-02 8:52 PM, Kevin Grittner wrote:

Marko Tiikkaja  wrote:


Sounds like in this case you'd only use set-oriented programming
at the end of the transaction, no?


I guess -- more properly I would say "in the final database
transaction for that financial transaction."


Yes, I should have said "financial transaction", but I hit send a bit 
too early.



And no, that never
made me wish that plpgsql functions defaulted to throwing errors
for DML statements that affected more than one row.


Fine.  But you should still be able to see the point we're trying to 
make.  The number one is special, and it's present everywhere.  If you 
want to program defensively, you have to go through a lot of pain right 
now.  We're looking for a way to alleviate that pain.  Defaulting to 
throwing errors would be one way to do it, but that's not what's being 
suggested here anymore.


You can dismiss what we're doing by saying that it doesn't follow the 
best practices or we just want an interface for a key-value store or 
whatever.  And yes, to some extent, a simple interface for a key-value 
store would come in handy.  But we still have the 5-15% (big part of it 
being the reporting we need to do) of the code that *doesn't* want that, 
*and* we want to use all of the Postgres features where applicable.



.marko


--
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] PL/pgSQL 2

2014-09-05 Thread Andrew Dunstan



On 09/05/2014 12:37 PM, Merlin Moncure wrote:

On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug  wrote:

Cost of hidden IO cast is negative too. If we can change it, then we can
increase a sped.

But the whole power of PL/pgSQL comes from the fact that it allows you to
use the full set of postgres data types and operatores, and that it seamlessly
integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC
as a programming language...

Right, and it's exactly those types and operators that are the cause
of the performance issues.  A compiled pl/pgsql would only get serious
benefit for scenarios involving tons of heavy iteration or funky local
data structure manipulation.  Those scenarios are somewhat rare in
practice for database applications and often better handled in a
another pl should they happen.

plv8 is emerging as the best "non-sql" it's JIT compiled by the plv8
runtime, the javascript language is designed for embedding. and the
json data structure has nice similarities with postgres's arrays and
types.  In fact, if I *were* to attempt pl/pgsql compiling, I'd
probably translate the code to plv8 and hand it off to the llvm
engine.  You'd still have to let postgres handle most of the operator
and cast operations but you could pull some things into the plv8
engine.  Probably, this would be a net loser since plv8 (unlike
plpgsql) has to run everything through SPI.


plpgsql makes extensive use of SPI. Just look at the source code if you 
don't believe me.


plv8 also has a nice "find_function" gadget that lets you find and call 
another plv8 function directly instead of having to use an SPI call.


It has two serious defects in my view, that it inherits from v8.

First, and foremost, it has the old really really horrible Javascript 
scoping rules for variables. This makes it totally unsuitable for 
anything except trivially short functions. There is good news and bad 
news on this front: modern versions of v8 have code to allow proper 
lexical scoping as provided for in the draft ECMASCRIPT6 standard (the 
feature is named "harmony scoping"). Example of command line use:


   andrew@vpncli plv8js]$ d8 --use-strict --harmony
   V8 version 3.14.5.10 [console: readline]
   d8> var i = 10; for (let i = 0; i < 3; i++) { let j = i; for (let i
   = 4; i < 6; i++) { print ("j " + j + " i " + i); } }
   j 0 i 4
   j 0 i 5
   j 1 i 4
   j 1 i 5
   j 2 i 4
   j 2 i 5
   d8> print(i);
   10
   d8> 

The bad news is that neither Hitosho nor I (yet) know how to allow 
setting these flags for the plv8 embedded engine.


The other defect is that its string handling is just awful. It has 
neither multiline strings, not interpolation into strings.


The good news is that the new draft standard addresses these issues too, 
with something called template strings. The bad news is that V8 doesn't 
yet have code to support the feature, AFAICT. The Mozilla people are a 
bit ahead here, and this feature is due in a release of their rhino 
javascript library that will be in Mozilla 34, due out in November, 
AIUI. Let's hope that the V8 guys get their act together on this.


cheers

andrew


--
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] PL/pgSQL 2

2014-09-05 Thread Merlin Moncure

On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug  wrote:
>> Cost of hidden IO cast is negative too. If we can change it, then we can
>> increase a sped.
>
> But the whole power of PL/pgSQL comes from the fact that it allows you to
> use the full set of postgres data types and operatores, and that it seamlessly
> integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC
> as a programming language...

Right, and it's exactly those types and operators that are the cause
of the performance issues.  A compiled pl/pgsql would only get serious
benefit for scenarios involving tons of heavy iteration or funky local
data structure manipulation.  Those scenarios are somewhat rare in
practice for database applications and often better handled in a
another pl should they happen.

plv8 is emerging as the best "non-sql" it's JIT compiled by the plv8
runtime, the javascript language is designed for embedding. and the
json data structure has nice similarities with postgres's arrays and
types.  In fact, if I *were* to attempt pl/pgsql compiling, I'd
probably translate the code to plv8 and hand it off to the llvm
engine.  You'd still have to let postgres handle most of the operator
and cast operations but you could pull some things into the plv8
engine.  Probably, this would be a net loser since plv8 (unlike
plpgsql) has to run everything through SPI.

IMO, what needs to happen first would be for the data type routines to
be pulled out of main library so that client side applications and pls
could link against it allowing for guaranteed sql semantics without
having to call into the backend -- at least the standard types.

merlin

-- 
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] PL/pgSQL 2

2014-09-04 Thread Florian Pflug

On Sep4, 2014, at 20:50 , Pavel Stehule  wrote:
> 2014-09-04 20:31 GMT+02:00 Josh Berkus :
> * The ability to "compile" functions/procedures for faster execution.
> 
> This point is more complex, because bottleneck is not in plpgsql - it is
> terrible fast against noncompiled pcode interpreted PL/SQL and it is
> comparable with PL/SQL - due different design. A expression evaluation is
> slower, partially due using a SQL expression interpret, partially due our
> arrays and strings are immutable, and any composition are slow.

That, in principle, is just an inlining problem, though. Say we translate
PL/pgSQL into LLVM bytecode in the simplest possible way by simply traversing
the parse tree, and emitting calls to the functions that the interpreter calls
now. Now, that alone wouldn't buy much, as you say. But if we additionally
compile (at least parts of) the executor machinery to LLVM bytecode too
(just once, while building postgres), the LLVM optimizer should in principle
be able to inline at least some of these calls, which *could* have considerable
benefit. The hard part would probably be to figure out how to inform the
executor which parts it may consider to be *constant* (i.e. what constitues
the execution *plan*) and which parts can change from one execution to the
next (i.e. the executor state). 

In fact, such an approach would allow all expression evaluations to be
JITed - not only those appearing in PL/pgSQL functions but also in plain SQL.

> Cost of hidden IO cast is negative too. If we can change it, then we can
> increase a sped.

But the whole power of PL/pgSQL comes from the fact that it allows you to
use the full set of postgres data types and operatores, and that it seamlessly
integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC
as a programming language...

best regards,
Florian Pflug

-- 
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] PL/pgSQL 2

2014-09-04 Thread Robert Haas

On Thu, Sep 4, 2014 at 2:31 PM, Josh Berkus  wrote:
> Sadly, what's prevented us from having "packages" already has been the
> insistence of potential feature sponsors that they work *exactly* like
> PL/SQL's packages, which is incompatible with Postgres namespacing.
> Also, we'd want any "package" concept to be usable with external PLs as
> well as PL/pgSQL, which necessitates other Oracle-incompatible changes.

This is not a fun area in which to try to be exactly like Oracle.
Just to take one example, the whole package is created and dumped as a
single object, with all of its contained functions *and their
comments*, including the exact position of those comments, such as
inside the argument list to document what particular arguments are
supposed to do.  We've worked out a (partial) solution to that problem
in Advanced Server, but it's not perfect, and it limits the ability to
implement other features that PostgreSQL users would probably expect,
like being able to add a function to a package after-the-fact.
PostgreSQL has a certain cleanliness of design that comes from doing
things in a way that makes sense from first principles, rather than
the way that other people may have done it.  I'm not prepared to say
that a $184B company made a bad design decision here - it certainly
seems to have worked out for them - but it's not what I would have
picked, and it's not a very good fit for other design decisions we've
made in PostgreSQL already.

All-in-all, I'm pretty happy with our EXTENSION system as a way of
loading code (and SQL function definitions) in a modular way.  It's
not perfect, but it's definitely made my life as a developer easier.
There are some things you can do with an Oracle package but not a
PostgreSQL extension, but there is an awful lot of overlap, too.  I
doubt we'd want to duplicate all that machinery just for compatibility
reasons.

-- 
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] PL/pgSQL 2

2014-09-04 Thread Stephen Frost

* Robert Haas (robertmh...@gmail.com) wrote:
> Second, if you did manage to develop something which was significantly
> more compatible with Oracle than PostgreSQL or PL/pgsql is today,
> you'd probably find that the community wouldn't accept it.

Agreed.  Moving PostgreSQL forward is what the community is interested
in- not duplicating what another database product has for the strict
goal of easing migrations from those databases (be it Oracle or MSSQL or
MySQL).

> To take another example, I've been complaining about the fact
> that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> than any other database I'm aware of for years, probably since before
> I joined EnterpriseDB.  And I still think we're kidding ourselves to
> think that we've got that right when nobody else is doing something
> similar.  I don't think the community should reverse that decision to
> benefit EnterpriseDB, or to be compatible with Oracle: I think the
> community should reverse that decision because it's stupid, and the
> precedent of other systems demonstrates that it is possible to do
> better.  Oracle's handling of reserved words also seems to be
> considerably less irritating than ours, and I'd propose that we
> improve that in PostgreSQL too, if I knew how to do it.
> Unfortunately, I suspect that requires jettisoning bison and rolling
> our own parser generator, and it's hard to argue that would be a good
> investment of effort for the benefit we'd get.

Also agreed on this, though any serious discussion on this would deserve
its own thread.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] PL/pgSQL 2

2014-09-04 Thread Pavel Stehule

2014-09-04 20:31 GMT+02:00 Josh Berkus :

> On 09/04/2014 09:02 AM, Craig Ringer wrote:
> > There are a few things I would like to see, like secure session
> > variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
> > compatibility" seems to be something that comes up before the speaker
> > has really understood what that would mean, and the sheer scope of the
> > endeavour.
> >
> > It's not going from 50% compatible to 80% compatible, it's going from 5%
> > compatible to 7% compatible. The most used 5% maybe, but still...
>
> However, there are users who want certain features from PL/SQL not for
> compatibility but because they're useful.  For example:
>
> * A "package" concept for encapsulation of multiple procedures, session
> variables, etc.
>
> * The ability to "compile" functions/procedures for faster execution.
>

This point is more complex, because bottleneck is not in plpgsql - it is
terrible fast against noncompiled pcode interpreted PL/SQL and it is
comparable with PL/SQL - due different design. A expression evaluation is
slower, partially due using a SQL expression interpret, partially due our
arrays and strings are immutable, and any composition are slow. Cost of
hidden IO cast is negative too. If we can change it, then we can increase a
sped. Almost all from these bottlenecks are out of plpgsql engine. So
compilation of plpgsql is myth and it doesn't help and it doesn't need it.
It doesn't help with speed.

Pavel


>
> * Autonomous transactions
>
> We'd also like to borrow stuff from other DBMSes, such as multisets.
> All of the above are worth implementing, even if it means implementing
> them with different syntax (and mechanics) than PL/SQL.
>
> Sadly, what's prevented us from having "packages" already has been the
> insistence of potential feature sponsors that they work *exactly* like
> PL/SQL's packages, which is incompatible with Postgres namespacing.
> Also, we'd want any "package" concept to be usable with external PLs as
> well as PL/pgSQL, which necessitates other Oracle-incompatible changes.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.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] PL/pgSQL 2

2014-09-04 Thread Josh Berkus

On 09/04/2014 09:02 AM, Craig Ringer wrote:
> There are a few things I would like to see, like secure session
> variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
> compatibility" seems to be something that comes up before the speaker
> has really understood what that would mean, and the sheer scope of the
> endeavour.
> 
> It's not going from 50% compatible to 80% compatible, it's going from 5%
> compatible to 7% compatible. The most used 5% maybe, but still...

However, there are users who want certain features from PL/SQL not for
compatibility but because they're useful.  For example:

* A "package" concept for encapsulation of multiple procedures, session
variables, etc.

* The ability to "compile" functions/procedures for faster execution.

* Autonomous transactions

We'd also like to borrow stuff from other DBMSes, such as multisets.
All of the above are worth implementing, even if it means implementing
them with different syntax (and mechanics) than PL/SQL.

Sadly, what's prevented us from having "packages" already has been the
insistence of potential feature sponsors that they work *exactly* like
PL/SQL's packages, which is incompatible with Postgres namespacing.
Also, we'd want any "package" concept to be usable with external PLs as
well as PL/pgSQL, which necessitates other Oracle-incompatible changes.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] PL/pgSQL 2

2014-09-04 Thread Pavel Stehule

Hi Craig


2014-09-04 17:54 GMT+02:00 Craig Ringer :

> On 09/04/2014 02:48 AM, Robert Haas wrote:
> > To take another example, I've been complaining about the fact
> > that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> > than any other database I'm aware of for years, probably since before
> > I joined EnterpriseDB.
>
> +10
>
> This still drives me nuts, and it's a serious problem for ORM users too.
>
> The idea that we won't accept a 'text' typed input for an 'xml' or
> 'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
> an argument for what problems this solves.
>
> I know why the changes in 8.3 were made, and they're clearly beneficial
> overall, but we need to start putting some more implicit casts from text
> to text-like types in, especially where there's no SQL-standard type
> that users of JDBC etc can easily use in mappings.
>

I don't see a problem in additional casts.

But some missing casts are well - I found lot performance issues based on
using wrong data types - integers, dates in text column.

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] PL/pgSQL 2

2014-09-04 Thread Craig Ringer

On 09/04/2014 06:48 AM, Joshua D. Drake wrote:
> 
> On 09/03/2014 11:48 AM, Robert Haas wrote:
> 
>> Anyway, to get back around to the topic of PL/SQL compatibility
>> specifically, if you care about that issue, pick one thing that exists
>> in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
>> it'll be something that EnterpiseDB has already done something about,
>> in which case, if your patch gets committed, Advanced Server will lose
>> a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
>> something that EnterpriseDB hasn't done anything about, and then
>> everybody comes out strictly ahead.  What I think you shouldn't do
>> (although you're free to ignore me) is continue thinking of Oracle
>> compatibility as one monolithic thing, because it isn't, or to pursue
>> of a course of trying to get the PostgreSQL community to slavishly
>> follow Oracle, because I think you'll fail, and even if you succeed I
>> don't think the results will actually be positive for PostgreSQL.
> 
> Well put Robert.

Indeed, especially with reference to the size and scope of Oracle. Its
XML library alone is huge.

At best it's reasonable to hope for compatibility with a limited subset
of PL/SQL - and really, we're a good way there already, with most of
what's missing being down to missing core server features or things
PostgreSQL just does differently.

True "Oracle compatibility" (for procedures) pretty much requires an
embedded JVM with a rich class library. Since PL/Java seems to be dying
a slow death by neglect and disinterest I don't think it's likely anyone
would be tackling compatibility with the embedded JVM features anytime soon.

There are a few things I would like to see, like secure session
variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
compatibility" seems to be something that comes up before the speaker
has really understood what that would mean, and the sheer scope of the
endeavour.

It's not going from 50% compatible to 80% compatible, it's going from 5%
compatible to 7% compatible. The most used 5% maybe, but still...

-- 
 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] PL/pgSQL 2

2014-09-04 Thread Marko Tiikkaja


On 9/4/14 5:54 PM, Craig Ringer wrote:

On 09/04/2014 02:48 AM, Robert Haas wrote:

To take another example, I've been complaining about the fact
that PostgreSQL 8.3+ requires far more typecasts in stored procedures
than any other database I'm aware of for years, probably since before
I joined EnterpriseDB.


+10

This still drives me nuts, and it's a serious problem for ORM users too.

The idea that we won't accept a 'text' typed input for an 'xml' or
'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
an argument for what problems this solves.


In what context?  Are we talking about parameters which have been cast 
to text, or what?  I don't remember ever having an issue with this, 
though I remember the lack of implicit cast from text to json (or the 
other way round) making a bug more obvious a couple of times.



.marko


--
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] PL/pgSQL 2

2014-09-04 Thread Craig Ringer

On 09/04/2014 02:48 AM, Robert Haas wrote:
> To take another example, I've been complaining about the fact
> that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> than any other database I'm aware of for years, probably since before
> I joined EnterpriseDB.

+10

This still drives me nuts, and it's a serious problem for ORM users too.

The idea that we won't accept a 'text' typed input for an 'xml' or
'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
an argument for what problems this solves.

I know why the changes in 8.3 were made, and they're clearly beneficial
overall, but we need to start putting some more implicit casts from text
to text-like types in, especially where there's no SQL-standard type
that users of JDBC etc can easily use in mappings.

-- 
 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] PL/pgSQL 2

2014-09-04 Thread Joel Jacobson

> On 4 sep 2014, at 15:09, Shaun Thomas  wrote:
>
>> On 09/01/2014 04:04 AM, Joel Jacobson wrote:
>>
>> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
>> row, as that's the most common use-case, and provide alternative syntax
>> to modify multiple or zero rows.
>
> What? No. The whole point of SQL is that it's set-based and can modify 
> multiple rows at once. Hobbling it specifically for functions seems 
> fundamentally flawed. Especially for what we purport to be a core PostgreSQL 
> language.

I've dropped that suggestion and is instead in favour of a keyword
like STRICT, ONE ROW, SINGLETON or [1] like suggested by others. Any
keyword or syntax will do fine, but I would prefer STRICT.

>
>> + Change all warnings into errors
>
> I... what? I could see coming up with a better exception handling mechanism 
> for escalating messages. But you're talking about taking a core element of 
> PostgreSQL (warnings) and simply ripping them out so plpgsql2 loses even that 
> small functionality.

You misunderstood, I meant plpgsql warnings, that you currently can
turn into errors by setting things in the config file. Such as
shadowing of variables.

>
> I'm sure you've put a lot of thought into this, but you're not the only 
> person using plpgsql or any, however ambitious, potential replacement.
>
> --
> Shaun Thomas
> OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> stho...@optionshouse.com
>
> __
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email


-- 
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] PL/pgSQL 2

2014-09-04 Thread Shaun Thomas


On 09/01/2014 04:04 AM, Joel Jacobson wrote:


+ Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
row, as that's the most common use-case, and provide alternative syntax
to modify multiple or zero rows.


What? No. The whole point of SQL is that it's set-based and can modify 
multiple rows at once. Hobbling it specifically for functions seems 
fundamentally flawed. Especially for what we purport to be a core 
PostgreSQL language.



+ Change all warnings into errors


I... what? I could see coming up with a better exception handling 
mechanism for escalating messages. But you're talking about taking a 
core element of PostgreSQL (warnings) and simply ripping them out so 
plpgsql2 loses even that small functionality.


I'm sure you've put a lot of thought into this, but you're not the only 
person using plpgsql or any, however ambitious, potential replacement.


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] PL/pgSQL 2

2014-09-03 Thread Joshua D. Drake



On 09/03/2014 11:48 AM, Robert Haas wrote:


Anyway, to get back around to the topic of PL/SQL compatibility
specifically, if you care about that issue, pick one thing that exists
in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
it'll be something that EnterpiseDB has already done something about,
in which case, if your patch gets committed, Advanced Server will lose
a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
something that EnterpriseDB hasn't done anything about, and then
everybody comes out strictly ahead.  What I think you shouldn't do
(although you're free to ignore me) is continue thinking of Oracle
compatibility as one monolithic thing, because it isn't, or to pursue
of a course of trying to get the PostgreSQL community to slavishly
follow Oracle, because I think you'll fail, and even if you succeed I
don't think the results will actually be positive for PostgreSQL.


Well put Robert.

JD



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2

2014-09-03 Thread Pavel Stehule

2014-09-03 21:01 GMT+02:00 David G Johnston :

> This is more of an SQL request the pl/pgsql but is/has there been thought
> to
> adding the ternary if/then opeator?  Something like:
>
> boolean_exp ?> val_if_true : val_if_false
>
> using "?" by itself would be OK but not ideal - and the addition of the ">"
> doesn't seem hateful...
>
> Sorry if this is deemed off-topic but I just went to write
>
> CASE WHEN boolean_exp THEN val_if_true ELSE val_if_false END
>
> And the fact there is as much standard code as there is custom bothered me
> just as is being discussed on this thread.
>
> I'm going to go write a "ifthen(bool, anyelement, anyelement)" function
> now
>
>
if you use a SQL (SQL macro, then it can be effective)

postgres=# CREATE OR REPLACE FUNCTION if(bool, anyelement, anyelement)
RETURNS anyelement AS $$SELECT CASE WHEN $1 THEN $2 ELSE $3 END $$ LANGUAGE
sql;
CREATE FUNCTION
postgres=# CREATE OR REPLACE FUNCTION fx(text) RETURNS text AS $$ BEGIN
RAISE NOTICE '%', $1; RETURN $1; END$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT if(false, fx('msg1'), fx('msg2'));
NOTICE:  msg2
  if
--
 msg2
(1 row)

postgres=# SELECT if(true, fx('msg1'), fx('msg2'));
NOTICE:  msg1
  if
--
 msg1
(1 row)

Only necessary parameters are evaluated

Pavel



> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817608.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] PL/pgSQL 2

2014-09-03 Thread David G Johnston

This is more of an SQL request the pl/pgsql but is/has there been thought to
adding the ternary if/then opeator?  Something like:

boolean_exp ?> val_if_true : val_if_false

using "?" by itself would be OK but not ideal - and the addition of the ">"
doesn't seem hateful...

Sorry if this is deemed off-topic but I just went to write

CASE WHEN boolean_exp THEN val_if_true ELSE val_if_false END

And the fact there is as much standard code as there is custom bothered me
just as is being discussed on this thread.

I'm going to go write a "ifthen(bool, anyelement, anyelement)" function
now

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817608.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] PL/pgSQL 2

2014-09-03 Thread Robert Haas

On Tue, Sep 2, 2014 at 5:47 PM, Álvaro Hernández Tortosa  wrote:
> Yeah, we differ there. I think having an Oracle compatibility layer in
> PostgreSQL would be the-next-big-thing we could have. Oracle is has orders
> of magnitude bigger user base than postgres has; and having the ability to
> attract them would bring us many many more users which, in turn, would
> benefit us all very significantly.
>
> It would be my #1 priority to do in postgres (but yes, I know -guess-
> how hard and what resources that would require). But dreaming is free :)

There are a number of reasons why this isn't really practical.

First, Oracle compatibility isn't one feature.  The compatibility
"layer" that exists in EnterpriseDB's Advanced Server product consists
of many different changes to many different parts of the system.  A
few of those changes are simple syntax compatibility, where we do the
exact same thing PostgreSQL does but with different syntax, but a lot
of them are functional enhancements.  Even within SPL, there's a whole
bunch of different changes to a whole bunch of different areas, and
most of those are functional enhancements rather than just tweaking
syntax.  So, if you tried to implement a new, Oracle-compatible PL,
you'd find that you don't have one or a small number of changes to
make, but a long series of features ranging from small to very large.
You'd also find that adding a new PL, without changing any other parts
of the server, only bridges a small part of the compatibility gap.

Second, if you did manage to develop something which was significantly
more compatible with Oracle than PostgreSQL or PL/pgsql is today,
you'd probably find that the community wouldn't accept it.  It's
almost misleading to think of Oracle as a database; it's an enormous
software ecosystem with facilities for doing just about everything
under the sun, and many of those things more than one way.  For
example, in 9.4, EnterpriseDB will be releasing a UTL_HTTP package
that contains many of the same interfaces that are present in Oracle.
The interface decisions made by Oracle Corporation are reasonable in
view of their architecture, but I am quite sure that this community
would not want, for example, to return long text values as SETOF
VARCHAR(2000) rather than TEXT, just because Oracle does that.  And
rightly so: I wouldn't want PostgreSQL to follow any other product
that slavishly whether I worked at EnterpriseDB or not.  This kind of
thing crops up over and over again, and it only works to say that
PostgreSQL should choose the Oracle behavior in every case if you
believe that the primary mission of PostgreSQL should be to copy
Oracle, and I don't.  I also don't think it's a bad thing that
Advanced Server makes different decisions than PostgreSQL in some
cases.  A further problem is that, in this particular case, you'd
probably here the argument from PostgreSQL hackers that they really
don't want to be burdened with maintaining an HTTP client in the core
server when the same thing could be done from an extension, and that's
a valid argument, too.  It is also valid for EnterpriseDB to make a
different decision for itself, based on business priorities.

Now, none of that is to say that we wouldn't do well to give a little
more thought to Oracle compatibility than we do.  We've either made or
narrowly avoided a number of decisions over the years which introduced
- or threatened to introduce - minor, pointless incompatibilities with
other database products, Oracle included.  That really doesn't benefit
anyone.  To take another example, I've been complaining about the fact
that PostgreSQL 8.3+ requires far more typecasts in stored procedures
than any other database I'm aware of for years, probably since before
I joined EnterpriseDB.  And I still think we're kidding ourselves to
think that we've got that right when nobody else is doing something
similar.  I don't think the community should reverse that decision to
benefit EnterpriseDB, or to be compatible with Oracle: I think the
community should reverse that decision because it's stupid, and the
precedent of other systems demonstrates that it is possible to do
better.  Oracle's handling of reserved words also seems to be
considerably less irritating than ours, and I'd propose that we
improve that in PostgreSQL too, if I knew how to do it.
Unfortunately, I suspect that requires jettisoning bison and rolling
our own parser generator, and it's hard to argue that would be a good
investment of effort for the benefit we'd get.

Anyway, to get back around to the topic of PL/SQL compatibility
specifically, if you care about that issue, pick one thing that exists
in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
it'll be something that EnterpiseDB has already done something about,
in which case, if your patch gets committed, Advanced Server will lose
a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
something that EnterpriseDB hasn't done anyt

Re: [HACKERS] PL/pgSQL 2

2014-09-03 Thread Álvaro Hernández Tortosa



On 03/09/14 15:24, Joshua D. Drake wrote:


On 09/02/2014 04:01 PM, Álvaro Hernández Tortosa wrote:


 It's not copying. It's easying a path for others to migrate and
come to Postgres.

 I'm interested why you are more interested in MSSQL. My reasons for
being interested in Oracle are:

- It has more users (biggest and above all, the main reason: we could
attract more)
- Postgres is perceived as "similar" to Oracle (so migration is likely
to be easier)

 That's all I want. Grow postgres userbase, attracting Oracle 
users :)


I find that we have more opportunity to replace MSSQL than Oracle. 
Obviously it depends on a lot of things but my goal is as yours, just 
with a different database.



Honestly, I don't care whether MSSQL or Oracle. What I want is to 
attract more users, get Postgres out of where it is and appeal even more 
users. With that regard, Oracle or MSSQL doesn't matter to me.


That's why if you have some time, I'd love to listen to why do you 
think there is more opportunity to replace MSSQL. We may continue that 
privately as is a little bit off-topic.


Thanks!

Álvaro



--
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] PL/pgSQL 2

2014-09-03 Thread Pavel Stehule

2014-09-03 17:05 GMT+02:00 Bruce Momjian :

> On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
> > I am not against to improve a PL/pgSQL. And I repeat, what can be done
> and can
> > be done early:
> >
> > a) ASSERT clause -- with some other modification to allow better static
> analyze
> > of DML statements, and enforces checks in runtime.
> >
> > b) #option or PRAGMA clause with GUC with function scope that enforce
> check on
> > processed rows after any DML statement
>

these two yes


>  >
> > c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> > DIAGNOSTICS rc = ROW_COUNT
>

this is my fresh

some smarty designed asserts can be used for static analyses too.

I am able to analyze plan of DML statements, and I can raise warning if
expected rows is not 1 or if there are not filter over unique index

some

UPDATE ... WHERE id = 1;
ASSERT(PROCESSED_ROW_COUNT = 1);

And I can recheck in plpgsql_check, and it can enforce fast check in runtime

Pavel




>
> All these ideas are being captured somewhere, right?  Where?
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + Everyone has their own god. +
>

Re: [HACKERS] PL/pgSQL 2

2014-09-03 Thread Marko Tiikkaja


On 9/3/14 5:05 PM, Bruce Momjian wrote:

On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:

I am not against to improve a PL/pgSQL. And I repeat, what can be done and can
be done early:

a) ASSERT clause -- with some other modification to allow better static analyze
of DML statements, and enforces checks in runtime.

b) #option or PRAGMA clause with GUC with function scope that enforce check on
processed rows after any DML statement

c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
DIAGNOSTICS rc = ROW_COUNT


All these ideas are being captured somewhere, right?  Where?


I'm working on a wiki page with all these ideas.  Some of them break 
backwards compatibility somewhat blatantly, some of them could be added 
into PL/PgSQL if we're okay with reserving a keyword for the feature. 
All of them we think are necessary.



.marko


--
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] PL/pgSQL 2

2014-09-03 Thread Bruce Momjian

On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
> I am not against to improve a PL/pgSQL. And I repeat, what can be done and can
> be done early:
> 
> a) ASSERT clause -- with some other modification to allow better static 
> analyze
> of DML statements, and enforces checks in runtime.
> 
> b) #option or PRAGMA clause with GUC with function scope that enforce check on
> processed rows after any DML statement
> 
> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> DIAGNOSTICS rc = ROW_COUNT

All these ideas are being captured somewhere, right?  Where?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] PL/pgSQL 2

2014-09-03 Thread Bruce Momjian

On Tue, Sep  2, 2014 at 08:46:36PM -0400, Christopher Browne wrote:
> 3.  Is there anything to be learned from Tutorial D?  That is, Date & Darwen's
> would-be alternative to SQL of their Third Manifesto?

What would a set-oriented-language PL look like, such as APL?  I guess
Perl has arrays, so it is kind-of set-oriented.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] PL/pgSQL 2

2014-09-03 Thread Jan Wieck


On 09/03/2014 03:14 AM, Joel Jacobson wrote:

I'm in favour of Tom's idea. To merely make the plpgsql2 "language" a
way of explicitly saying you want
a specific exact combination of features/beaviour/settings which we
can implemented in plpgsql's existing codebase.

Since it was about 100 posts since Tom's post, maybe it's worth
repeating for those who missed it:


What I would think about is

c) plpgsql and plpgsql2 are the same code base, with a small number
of places that act differently depending on the language version.

We could alternatively get the result by inventing a bunch of pragma
declarations, or some similar notation, that control the behavioral
changes one-at-a-time.  That might even be worth doing anyway, in
case somebody likes some of the ideas and others not so much.  But
I'd see the language version as a convenient shorthand for enabling a
specified collection of pretty-localized incompatible behavior changes.
If they're not pretty localized, there's going to be a barrier to
uptake, very comparable to the python3 analogy mentioned upthread.

   regards, tom lane


I fully agree on this approach. It's maintainable and it will be
useful from day 1.


One can take that approach to another, more generic level. Like GUCs can 
be set on a ROLE base with ALTER USER or ALTER ROLE, PL specific GUCs 
could be set via "ALTER LANGUAGE foo SET ...".


The possibility to CREATE LANGUAGE mybetterpl, pointing to the same PL 
handler function, exists already. And the same mechanism could be used 
by other languages, like PL/Python (for whatever such language might 
need such settings).


This way an application can define the language settings, it needs, by 
simply creating its own language, based on all the possible permutations 
of those PRAGMA/GUC settings.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

2014-09-03 Thread Joel Jacobson

On Wed, Sep 3, 2014 at 3:17 PM, Joshua D. Drake  wrote:
> Well, I don't think PostgreSQL needs its own PL. I mean we already have
> several (what other database has pl/javascript or pl/python?)

PostgreSQL already *have* it's own PL, it's called PL/pgSQL.

> Besides, the idea of this community trying to build its own programming
> language... oh lord ;)

I would agree it's too much of a challenge to invent a brand new
programming language,
I agree that's unrealistic, that's why I'm opting to do as much as
possible in the existing
language, and carefully think about what non-compatible important
changes we simply
cannot make to PL/pgSQL, as they by definition would break
compatibility (which we all
agree is not acceptable), but that *would* be possible with PL/pgSQL 2.

-- 
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] PL/pgSQL 2

2014-09-03 Thread Joshua D. Drake



On 09/02/2014 04:01 PM, Álvaro Hernández Tortosa wrote:


 It's not copying. It's easying a path for others to migrate and
come to Postgres.

 I'm interested why you are more interested in MSSQL. My reasons for
being interested in Oracle are:

- It has more users (biggest and above all, the main reason: we could
attract more)
- Postgres is perceived as "similar" to Oracle (so migration is likely
to be easier)

 That's all I want. Grow postgres userbase, attracting Oracle users :)


I find that we have more opportunity to replace MSSQL than Oracle. 
Obviously it depends on a lot of things but my goal is as yours, just 
with a different database.


JD




 Álvaro





--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2

2014-09-03 Thread Joshua D. Drake



On 09/02/2014 03:50 PM, Jan Wieck wrote:


PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a Ada/COBOL
lookalike.

Instead of trying to mimic what it was or a T-SQL thing instead ...
maybe it is time to come up with a true PostgreSQL specific PL for a
change?

Just for the sake of being something new, and not a copy of some old
opossum, that's rotting like road kill on the side of the highway for a
decade already.



Well, I don't think PostgreSQL needs its own PL. I mean we already have 
several (what other database has pl/javascript or pl/python?)


Besides, the idea of this community trying to build its own programming 
language... oh lord ;)


JD




Jan




--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2

2014-09-03 Thread Joel Jacobson

On Wed, Sep 3, 2014 at 10:07 AM, Pavel Stehule  wrote:
> When you use name plpgsql2 you say, so plpgsql2 is successor plpgsql. It is
> very hard to accept it.  So any other name is not problem for me - like
> plpgsql-safe-subset or something else

plpgsql2 *is* the successor of plpgsql, that's why it has a "2" in the name.
Anything which is very different from plpgsql should instead get a
different name.

For all new users, having a convenient shorthand (plpgsql2) for
enabling what ever the
project think is the best-practice collection of pragmas, is a simple
and efficient way
of helping new users to get the best possible behaviour of the
language, when starting
from scratch.
It also simplifies communication among developers, when they talk
about code written in plpgsql2,
they will all eventually know what they mean, instead of having to
describe what collection of pragmas
they use in their code. That also simplifies code examples, but most
importantly, one does not have
to declare all the pragmas for each function, or worry about the
pragmas in the config file will ever change.

Once we have agreed upon plpgsql2, then it will be a constant, and
never break compatibility,
and that's a good thing. Then we can all write new code according to
the updated specs and
hopefully we will not need a plpgsql3 until year 2030.

-- 
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] PL/pgSQL 2

2014-09-03 Thread Pavel Stehule

2014-09-03 9:14 GMT+02:00 Joel Jacobson :

> On Wed, Sep 3, 2014 at 7:54 AM, Pavel Stehule 
> wrote:
> > I am not against to improve a PL/pgSQL. And I repeat, what can be done
> and
> > can be done early:
> >
> > a) ASSERT clause -- with some other modification to allow better static
> > analyze of DML statements, and enforces checks in runtime.
> >
> > b) #option or PRAGMA clause with GUC with function scope that enforce
> check
> > on processed rows after any DML statement
> >
> > c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> > DIAGNOSTICS rc = ROW_COUNT
> >
> > If you need more, and some users would more, then it job for new language
> > really.
>
> You fail to illustrate *why* it's a job for a new language.
> All improvements suggested above are possible with plpgsql, and *should*
> be improved in plpgsql, that I agree with.
>

ok, super


>
> But the 100% backwards-compatibiity ambition puts hard limits on
> what's possible,
> and if we can accept (100%-X) backwards compatibility where X is a small
> number,
> then so much more ideas are possible, and that's why plpgsql2 is a good
> idea.
>
> Hopefully, most of the plpgsql2 changes can be turned on/off in
> plpgsql with PRAGMA clause with GUC,
> but will be more messy than a good decent default behaviour.
>
> I'm in favour of Tom's idea. To merely make the plpgsql2 "language" a
> way of explicitly saying you want
> a specific exact combination of features/beaviour/settings which we
> can implemented in plpgsql's existing codebase.
>
> Since it was about 100 posts since Tom's post, maybe it's worth
> repeating for those who missed it:
>
> > What I would think about is
> >
> >c) plpgsql and plpgsql2 are the same code base, with a small number
> >of places that act differently depending on the language version.
> >
> >We could alternatively get the result by inventing a bunch of pragma
> >declarations, or some similar notation, that control the behavioral
> >changes one-at-a-time.  That might even be worth doing anyway, in
> >case somebody likes some of the ideas and others not so much.  But
> >I'd see the language version as a convenient shorthand for enabling a
> >specified collection of pretty-localized incompatible behavior changes.
> >If they're not pretty localized, there's going to be a barrier to
> >uptake, very comparable to the python3 analogy mentioned upthread.
> >
> >regards, tom lane
>
> I fully agree on this approach. It's maintainable and it will be
> useful from day 1.
>

I can accept technical solution, but I have hard problem with your vision
of plpgsql future. I afraid so it is too specific with your use case.

When you use name plpgsql2 you say, so plpgsql2 is successor plpgsql. It is
very hard to accept it.  So any other name is not problem for me - like
plpgsql-safe-subset or something else

Pavel

Re: [HACKERS] PL/pgSQL 2

2014-09-03 Thread Joel Jacobson

On Wed, Sep 3, 2014 at 7:54 AM, Pavel Stehule  wrote:
> I am not against to improve a PL/pgSQL. And I repeat, what can be done and
> can be done early:
>
> a) ASSERT clause -- with some other modification to allow better static
> analyze of DML statements, and enforces checks in runtime.
>
> b) #option or PRAGMA clause with GUC with function scope that enforce check
> on processed rows after any DML statement
>
> c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
> DIAGNOSTICS rc = ROW_COUNT
>
> If you need more, and some users would more, then it job for new language
> really.

You fail to illustrate *why* it's a job for a new language.
All improvements suggested above are possible with plpgsql, and *should*
be improved in plpgsql, that I agree with.

But the 100% backwards-compatibiity ambition puts hard limits on
what's possible,
and if we can accept (100%-X) backwards compatibility where X is a small number,
then so much more ideas are possible, and that's why plpgsql2 is a good idea.

Hopefully, most of the plpgsql2 changes can be turned on/off in
plpgsql with PRAGMA clause with GUC,
but will be more messy than a good decent default behaviour.

I'm in favour of Tom's idea. To merely make the plpgsql2 "language" a
way of explicitly saying you want
a specific exact combination of features/beaviour/settings which we
can implemented in plpgsql's existing codebase.

Since it was about 100 posts since Tom's post, maybe it's worth
repeating for those who missed it:

> What I would think about is
>
>c) plpgsql and plpgsql2 are the same code base, with a small number
>of places that act differently depending on the language version.
>
>We could alternatively get the result by inventing a bunch of pragma
>declarations, or some similar notation, that control the behavioral
>changes one-at-a-time.  That might even be worth doing anyway, in
>case somebody likes some of the ideas and others not so much.  But
>I'd see the language version as a convenient shorthand for enabling a
>specified collection of pretty-localized incompatible behavior changes.
>If they're not pretty localized, there's going to be a barrier to
>uptake, very comparable to the python3 analogy mentioned upthread.
>
>regards, tom lane

I fully agree on this approach. It's maintainable and it will be
useful from day 1.

-- 
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] PL/pgSQL 2

2014-09-02 Thread Szymon Guz

On 3 September 2014 01:08, Jan Wieck  wrote:

> On 09/02/2014 06:56 PM, Andrew Dunstan wrote:
>
>> People are free to do what they want, but to my mind that would be a
>> massive waste of resources, and probably imposing a substantial extra
>> maintenance burden on the core committers.
>>
>
> I hear you and agree to some degree.
>
> But at the same time I remember that one of the strengths of Postgres used
> to be to be able to incorporate "new" ideas.
>
> This seems to be one of those cases.
>
> Instead of "fork" plpgsql2, what about designing a completely new
> PL/postgres from scratch? It will only take 3-10 years, but I bet it will
> be worth it after all. And I mean that. No sarcasm.
>
>
And how it would be better then already existing plperl/plpython?

- Szymon

Re: [HACKERS] PL/pgSQL 2

2014-09-03 7:23 GMT+02:00 Joel Jacobson :

> On Wed, Sep 3, 2014 at 7:17 AM, Pavel Stehule 
> wrote:
> > yes, but there is minimal agreement of direction of movement. I am not
> alone
> > who are thinking so your proposal is not good for general usage.
>
> Minimal agreement? That's not true. The other group of users have been
> discussing
> a completely new language, which is a different discussion than the
> one on PL/pgSQL 2.
>
> Just because you think a new language is what we need, doesn't mean
> you automatically
> would think it's not a good idea to improve PL/pgSQL and create PL/pgSQL 2.
>

I am not against to improve a PL/pgSQL. And I repeat, what can be done and
can be done early:

a) ASSERT clause -- with some other modification to allow better static
analyze of DML statements, and enforces checks in runtime.

b) #option or PRAGMA clause with GUC with function scope that enforce check
on processed rows after any DML statement

c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
DIAGNOSTICS rc = ROW_COUNT

If you need more, and some users would more, then it job for new language
really. But there are more questions:

* what will be base for this language? (for one group people any ALGOL like
languages are obsolete and anything what is not HASKELL (or what has not
enough obscure syntax) is not enough modern. I propose a SQL/PSM -- but it
cannot be enough modern for these people - it is ALGOL like (Modula), it
has strong difference between dynamic and embedded SQL. Some people will
propose T-SQL, other PL/SQL. It is really valid question. My opinion is, so
it is not community task - it is good research project, but not community,
because can be terrible hard to find any consensus in this area.

* what will be engine for this languge? PLpgSQL engine is mature and some
from your proposal can be implemented simply, but probably very early you
can find a limits. For example change of typing system needs rewriting 30%
code (or if you would to implement together with current plpgsql 20% new
code). Lot of expectation from new language (like speedup of expression
evaluation or more dynamical access to variables, record fields) is
terrible to build on current engine.

Regards

Pavel

Re: [HACKERS] PL/pgSQL 2

On Wed, Sep 3, 2014 at 7:17 AM, Pavel Stehule  wrote:
> yes, but there is minimal agreement of direction of movement. I am not alone
> who are thinking so your proposal is not good for general usage.

Minimal agreement? That's not true. The other group of users have been
discussing
a completely new language, which is a different discussion than the
one on PL/pgSQL 2.

Just because you think a new language is what we need, doesn't mean
you automatically
would think it's not a good idea to improve PL/pgSQL and create PL/pgSQL 2.

-- 
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] PL/pgSQL 2

On Wed, Sep 3, 2014 at 2:46 AM, Christopher Browne  wrote:
> The notion of hacking features onto plpgsql2 that mostly seem like SQL
> enhancements is a waste of time.

New versions of languages who change too much in a new version are
doomed to fail. There are many such examples in history.
Completely new languages are however not doomed to fail.

So when we are talking about a plpgsql2, the changes should come from
all the collective wisdome we have gained during the 16 years of its
existence.
When we are talking about a completely new language, then of course we
should start from scratch we don't need to base it on any single
language if we don't want to.

-- 
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] PL/pgSQL 2

2014-09-03 7:07 GMT+02:00 Joel Jacobson :

> On Wed, Sep 3, 2014 at 12:19 AM, Josh Berkus  wrote:
> > On 09/01/2014 02:04 AM, Joel Jacobson wrote:
> >> Please share your wish list of things you would want in plpgsql2 which
> >> are not possible to implement in plpgsql because they could possibly
> >> break compatibility.
> >
> > Well, if I were designing a new procedural SQL extension language, I
> > wouldn't base it on the bastard child of ADA and SQL89.  I would come up
> > with something new.  One of the critical features such a new language
> > would have would be the ability to dynamically generate queries
> > *without* using string manipulation and EXECUTE.
> >
> > Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
> > makeover.
>
> That's like if I would say "I want to repaint my house", you would
> reply "You should build a new house instead". :-)
>
> Though, I think I can understand your point of view here:
>
> 1. For a new developer who is starting out a new project from scratch,
> and is looking for a nice PL for PostgreSQL, such a language you
> are describing would be a perfect fit.
>
> 2. For all developers who already have large projects written in PL/pgSQL,
> and
> - don't have that many problems with the language,
> - are extremely productive in the language,
> - love the syntax,
> - trust the language,
> - would never want to get a divorce from the language,
> - but are very keen on *improving* the existing language,
> all such developers would be very interested in PL/pgSQL 2,
> but not so interested in any completely new PL.
>
> I fall into the second category. But I understand you are more interested
> in
> writing completely new projects than improving on your existing code,
> and that's a very valid argument for all such users.
>
> The main benefits I see with making PL/pgSQL 2 almost-compatible with
> PL/pgSQL,
> and by developing it inside the same code base as PL/pgSQL are the
> following:
>
> * Some PL/pgSQL code would compile and run in PL/pgSQL 2 without any
> modifications
> * Most PL/pgSQL code would compile and run in PL/pgSQL 2 with minor
> modifications
> * Most PL/pgSQL users would quickly be productive in the new language
> after reading the "Changes" doc.
> * The existing PL/pgSQL codebase is stable and trusted. If PL/pgSQL 2
> is based on it, we will only have to understand and test the changes.
> * PL/pgSQL was released16 years ago. It has survived time and is still
> The PL for PostgreSQL. In those 16 years we have a learned a lot by
> using the language. It's time for a new version of the language.
>

yes, but there is minimal agreement of direction of movement. I am not
alone who are thinking so your proposal is not good for general usage.

Pavel




>
>
> --
> 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] PL/pgSQL 2

On Wed, Sep 3, 2014 at 12:19 AM, Josh Berkus  wrote:
> On 09/01/2014 02:04 AM, Joel Jacobson wrote:
>> Please share your wish list of things you would want in plpgsql2 which
>> are not possible to implement in plpgsql because they could possibly
>> break compatibility.
>
> Well, if I were designing a new procedural SQL extension language, I
> wouldn't base it on the bastard child of ADA and SQL89.  I would come up
> with something new.  One of the critical features such a new language
> would have would be the ability to dynamically generate queries
> *without* using string manipulation and EXECUTE.
>
> Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
> makeover.

That's like if I would say "I want to repaint my house", you would
reply "You should build a new house instead". :-)

Though, I think I can understand your point of view here:

1. For a new developer who is starting out a new project from scratch,
and is looking for a nice PL for PostgreSQL, such a language you
are describing would be a perfect fit.

2. For all developers who already have large projects written in PL/pgSQL, and
- don't have that many problems with the language,
- are extremely productive in the language,
- love the syntax,
- trust the language,
- would never want to get a divorce from the language,
- but are very keen on *improving* the existing language,
all such developers would be very interested in PL/pgSQL 2,
but not so interested in any completely new PL.

I fall into the second category. But I understand you are more interested in
writing completely new projects than improving on your existing code,
and that's a very valid argument for all such users.

The main benefits I see with making PL/pgSQL 2 almost-compatible with PL/pgSQL,
and by developing it inside the same code base as PL/pgSQL are the following:

* Some PL/pgSQL code would compile and run in PL/pgSQL 2 without any
modifications
* Most PL/pgSQL code would compile and run in PL/pgSQL 2 with minor
modifications
* Most PL/pgSQL users would quickly be productive in the new language
after reading the "Changes" doc.
* The existing PL/pgSQL codebase is stable and trusted. If PL/pgSQL 2
is based on it, we will only have to understand and test the changes.
* PL/pgSQL was released16 years ago. It has survived time and is still
The PL for PostgreSQL. In those 16 years we have a learned a lot by
using the language. It's time for a new version of the language.

-- 
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] PL/pgSQL 2

2014-09-02 Thread Christopher Browne

On Tue, Sep 2, 2014 at 7:08 PM, Jan Wieck  wrote:

> On 09/02/2014 06:56 PM, Andrew Dunstan wrote:
>
>> People are free to do what they want, but to my mind that would be a
>> massive waste of resources, and probably imposing a substantial extra
>> maintenance burden on the core committers.
>>
>
> I hear you and agree to some degree.
>
> But at the same time I remember that one of the strengths of Postgres used
> to be to be able to incorporate "new" ideas.
>
> This seems to be one of those cases.
>
> Instead of "fork" plpgsql2, what about designing a completely new
> PL/postgres from scratch? It will only take 3-10 years, but I bet it will
> be worth it after all. And I mean that. No sarcasm.

Indeed.  That seems like a better approach to me.

The notion of hacking features onto plpgsql2 that mostly seem like SQL
enhancements is a waste of time.

I see a few places that look like they might be worth thinking about as
part of a "new thing"; it's important that they not merely be minor tweaks
of the "old thing" otherwise we might as well just improve pl/pgsql.
 (That's no bad idea, to be sure!)

1.  A classic place where I have leaped to other languages has been to do
sophisticated "string smashing."  Often pl/perl, but when that proved
troublesome to run on AIX, I was perfectly happy to port to pl/tcl.  My
idle waggish thought over the years has been to consider pl/snobol or
pl/icon.  It's conceivable that some improvement to the Postgres built-in
string functions might make this unnecessary.  That's worthy of
exploration.  (And implementing pl/snobol is probably not!  :-))

2.  People have been pointing out that EXECUTE isn't all that wonderful.  I
think it is no coincidence that in all the languages that have an EVAL, it
is considered poor form to make unnecessary use of it.  (True whether we're
talking about Common Lisp or Perl!)  The discussion of using FORMAT() to
generate "execute fodder" fits with this.  It is again quite plausible that
having some improved functions might make it unnecessary to go all the way
to the extent of implementing a new language.

3.  Is there anything to be learned from Tutorial D?  That is, Date &
Darwen's would-be alternative to SQL of their Third Manifesto?

4.  All the existing stored function languages run inside a transaction
context.  I'd sure like it if I could have a platform, running on the
database, which could invoke and control transactions.  A layer that can
open connections, manage transactions, and such ought to be mighty useful.
 That CANNOT be implemented as one of the existing pl/whatevers.

To justify a new language requires having some requirements that cannot be
fulfilled by cleaning up an existing thing.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Re: [HACKERS] PL/pgSQL 2


On 09/02/2014 06:56 PM, Andrew Dunstan wrote:

People are free to do what they want, but to my mind that would be a
massive waste of resources, and probably imposing a substantial extra
maintenance burden on the core committers.


I hear you and agree to some degree.

But at the same time I remember that one of the strengths of Postgres 
used to be to be able to incorporate "new" ideas.


This seems to be one of those cases.

Instead of "fork" plpgsql2, what about designing a completely new 
PL/postgres from scratch? It will only take 3-10 years, but I bet it 
will be worth it after all. And I mean that. No sarcasm.



Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2



On 03/09/14 00:41, Joshua D. Drake wrote:


On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:


 Yeah, we differ there. I think having an Oracle compatibility layer
in PostgreSQL would be the-next-big-thing we could have. Oracle is has
orders of magnitude bigger user base than postgres has; and having the
ability to attract them would bring us many many more users which, in
turn, would benefit us all very significantly.

 It would be my #1 priority to do in postgres (but yes, I know
-guess- how hard and what resources that would require). But dreaming is
free :)


Oracle compatibility certainly has merit, I just don't see it as 
useful for core. I would be far more interested in MSSQL compatibility 
honestly. That said, Postgres itself is a rockstar and I think we can 
make our own case without having to copy others.


It's not copying. It's easying a path for others to migrate and 
come to Postgres.


I'm interested why you are more interested in MSSQL. My reasons for 
being interested in Oracle are:


- It has more users (biggest and above all, the main reason: we could 
attract more)
- Postgres is perceived as "similar" to Oracle (so migration is likely 
to be easier)


That's all I want. Grow postgres userbase, attracting Oracle users :)

Álvaro


--
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] PL/pgSQL 2

2014-09-02 Thread Andrew Dunstan



On 09/02/2014 06:50 PM, Jan Wieck wrote:

On 09/02/2014 06:41 PM, Joshua D. Drake wrote:


On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:

 Yeah, we differ there. I think having an Oracle compatibility 
layer

in PostgreSQL would be the-next-big-thing we could have. Oracle is has
orders of magnitude bigger user base than postgres has; and having the
ability to attract them would bring us many many more users which, in
turn, would benefit us all very significantly.

 It would be my #1 priority to do in postgres (but yes, I know
-guess- how hard and what resources that would require). But 
dreaming is

free :)


Oracle compatibility certainly has merit, I just don't see it as useful
for core. I would be far more interested in MSSQL compatibility
honestly. That said, Postgres itself is a rockstar and I think we can
make our own case without having to copy others.


PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a 
Ada/COBOL lookalike.


Ada yes, COBOL no.



Instead of trying to mimic what it was or a T-SQL thing instead ... 
maybe it is time to come up with a true PostgreSQL specific PL for a 
change?


Just for the sake of being something new, and not a copy of some old 
opossum, that's rotting like road kill on the side of the highway for 
a decade already.








People are free to do what they want, but to my mind that would be a 
massive waste of resources, and probably imposing a substantial extra 
maintenance burden on the core committers.


cheers

andrew


--
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] PL/pgSQL 2


On 09/02/2014 06:41 PM, Joshua D. Drake wrote:


On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:


 Yeah, we differ there. I think having an Oracle compatibility layer
in PostgreSQL would be the-next-big-thing we could have. Oracle is has
orders of magnitude bigger user base than postgres has; and having the
ability to attract them would bring us many many more users which, in
turn, would benefit us all very significantly.

 It would be my #1 priority to do in postgres (but yes, I know
-guess- how hard and what resources that would require). But dreaming is
free :)


Oracle compatibility certainly has merit, I just don't see it as useful
for core. I would be far more interested in MSSQL compatibility
honestly. That said, Postgres itself is a rockstar and I think we can
make our own case without having to copy others.


PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a Ada/COBOL 
lookalike.


Instead of trying to mimic what it was or a T-SQL thing instead ... 
maybe it is time to come up with a true PostgreSQL specific PL for a change?


Just for the sake of being something new, and not a copy of some old 
opossum, that's rotting like road kill on the side of the highway for a 
decade already.



Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2



On 09/02/2014 03:19 PM, Josh Berkus wrote:


On 09/01/2014 02:04 AM, Joel Jacobson wrote:

Please share your wish list of things you would want in plpgsql2 which
are not possible to implement in plpgsql because they could possibly
break compatibility.


Well, if I were designing a new procedural SQL extension language, I
wouldn't base it on the bastard child of ADA and SQL89.  I would come up
with something new.  One of the critical features such a new language
would have would be the ability to dynamically generate queries
*without* using string manipulation and EXECUTE.

Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
makeover.



+1

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2



On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote:


 Yeah, we differ there. I think having an Oracle compatibility layer
in PostgreSQL would be the-next-big-thing we could have. Oracle is has
orders of magnitude bigger user base than postgres has; and having the
ability to attract them would bring us many many more users which, in
turn, would benefit us all very significantly.

 It would be my #1 priority to do in postgres (but yes, I know
-guess- how hard and what resources that would require). But dreaming is
free :)


Oracle compatibility certainly has merit, I just don't see it as useful 
for core. I would be far more interested in MSSQL compatibility 
honestly. That said, Postgres itself is a rockstar and I think we can 
make our own case without having to copy others.


JD




 Álvaro






--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2

2014-09-02 Thread Josh Berkus

On 09/02/2014 03:19 PM, Josh Berkus wrote:
> Well, if I were designing a new procedural SQL extension language, I
> wouldn't base it on the bastard child of ADA and SQL89.  I would come up

Ada, that is.  One is a programming language, the other is the bane of
architects.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] PL/pgSQL 2

2014-09-02 Thread Josh Berkus

On 09/01/2014 02:04 AM, Joel Jacobson wrote:
> Please share your wish list of things you would want in plpgsql2 which
> are not possible to implement in plpgsql because they could possibly
> break compatibility.

Well, if I were designing a new procedural SQL extension language, I
wouldn't base it on the bastard child of ADA and SQL89.  I would come up
with something new.  One of the critical features such a new language
would have would be the ability to dynamically generate queries
*without* using string manipulation and EXECUTE.

Otherwise, improvements to PL/pgSQL amount to the proverbial porcine
makeover.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] PL/pgSQL 2


On 09/02/2014 04:16 PM, Andres Freund wrote:

On 2014-09-02 19:06:02 +0200, Joel Jacobson wrote:

But what do you think about,
STRICT UPDATE ...?


I quite dislike it. An UPDATE isn't less 'strict' (whatever that means)
if updates more than one row. There's some sense in the way it's used
for INTO because it's referring to the INTO. And it's much more obvious
what it could mean there.


For once I completely agree with Andres.


Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2



On 02/09/14 23:34, Joshua D. Drake wrote:


On 09/02/2014 02:11 PM, David Johnston wrote:

On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake mailto:j...@commandprompt.com>>wrote:


On 09/02/2014 09:48 AM, Bruce Momjian wrote:

As a case in point, EDB have spent quite a few man-years on
their Oracle
compatibility layer; and it's still not a terribly exact
match, according
to my colleagues who have looked at it.  So that is a
tarbaby I don't
personally care to touch ... even ignoring the fact that
cutting off
EDB's air supply wouldn't be a good thing for the community
to do.


What any commercial entity and the Community do are mutually
exclusive and we can not and should not determine what features we
will support based on any commercial endeavor.


From where I sit the "mutually exclusive" argument doesn't seem to be
true - and in fact is something I think would be bad if it were.  We
shouldn't be afraid to add features to core that vendors are offering
but at the same time the fact that the Oracle compatibility aspects are
commercial instead of in-core is a plus to help ensure that there are
people making a decent living off PostgreSQL and thus are invested in


Far more people make a very good living off of PostgreSQL than *any* 
commercial variant. I stand by what I said. It is not the 
responsibility or the care of the community what a commercial vendor 
does or does not do with their fork except, possibly to implement the 
open source equivalent where it makes sense or where licensing may not 
be followed. (Read: I don't care about oracle compatibility)



Yeah, we differ there. I think having an Oracle compatibility layer 
in PostgreSQL would be the-next-big-thing we could have. Oracle is has 
orders of magnitude bigger user base than postgres has; and having the 
ability to attract them would bring us many many more users which, in 
turn, would benefit us all very significantly.


It would be my #1 priority to do in postgres (but yes, I know 
-guess- how hard and what resources that would require). But dreaming is 
free :)


Álvaro



--
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] PL/pgSQL 2


On 09/02/2014 09:32 AM, Joel Jacobson wrote:


I think it's much better to make it the default behaviour in plpgsql2
than to add a new syntax to plpgsql,
because then we don't have to argue what to call the keyword or where to put it.


This should in NO CASE be any new syntax to plpgsql or plpgsql2. If you 
cannot get that same syntax to apply to default client queries, then the 
whole idea is bogus because it will confuse developers more than it 
helps you with your particular way of thinking.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2



On 09/02/2014 02:11 PM, David Johnston wrote:

On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake mailto:j...@commandprompt.com>>wrote:


On 09/02/2014 09:48 AM, Bruce Momjian wrote:

As a case in point, EDB have spent quite a few man-years on
their Oracle
compatibility layer; and it's still not a terribly exact
match, according
to my colleagues who have looked at it.  So that is a
tarbaby I don't
personally care to touch ... even ignoring the fact that
cutting off
EDB's air supply wouldn't be a good thing for the community
to do.


What any commercial entity and the Community do are mutually
exclusive and we can not and should not determine what features we
will support based on any commercial endeavor.


From where I sit the "mutually exclusive" argument doesn't seem to be
true - and in fact is something I think would be bad if it were.  We
shouldn't be afraid to add features to core that vendors are offering
but at the same time the fact that the Oracle compatibility aspects are
commercial instead of in-core is a plus to help ensure that there are
people making a decent living off PostgreSQL and thus are invested in


Far more people make a very good living off of PostgreSQL than *any* 
commercial variant. I stand by what I said. It is not the responsibility 
or the care of the community what a commercial vendor does or does not 
do with their fork except, possibly to implement the open source 
equivalent where it makes sense or where licensing may not be followed. 
(Read: I don't care about oracle compatibility)



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2



On 02/09/14 23:11, David Johnston wrote:
On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake >wrote:



On 09/02/2014 09:48 AM, Bruce Momjian wrote:

As a case in point, EDB have spent quite a few man-years
on their Oracle
compatibility layer; and it's still not a terribly exact
match, according
to my colleagues who have looked at it.  So that is a
tarbaby I don't
personally care to touch ... even ignoring the fact that
cutting off
EDB's air supply wouldn't be a good thing for the
community to do.


What any commercial entity and the Community do are mutually
exclusive and we can not and should not determine what features we
will support based on any commercial endeavor.


From where I sit the "mutually exclusive" argument doesn't seem to be 
true - and in fact is something I think would be bad if it were.  We 
shouldn't be afraid to add features to core that vendors are offering 
but at the same time the fact that the Oracle compatibility aspects 
are commercial instead of in-core is a plus to help ensure that there 
are people making a decent living off PostgreSQL and thus are invested 
in its future


Definitely we shouldn't be afraid to add any feature to core, if we 
(as a community) like it and can do it. And for sure, commercial 
versions and consultancy companies need to make a living and we should 
care of them all (that includes myself -my company-, of course). But 
there is plenty of space for all, specially with an Oracle compatibility 
layer. That would attract many many many users to postgres, and we all 
(including EDB, of course) would immediately benefit from it. Of course 
the community too.


Plus, competition is never bad: it's the key to progress. Even if 
it would steal business from EDB, having to "compete" with PostgreSQL 
would foster them to improve and differentiate, becoming better. So I 
don't see any problem there.


Of course, that's only my view :)

Best,

Álvaro

Re: [HACKERS] PL/pgSQL 2


On 09/01/2014 10:41 AM, Joel Jacobson wrote:

On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer  wrote:

Well, the idiom:

  EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;

is not lovely. It works, but it's clumsy.


This is exactly why we need a new language.
All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
plpgsql2, with the most beautiful syntax we can come up with.


You know that you're running into problems with the SPI subsystem on 
that one, no?


Identifiers cannot be parameters in SPI_prepare(). So how do you propose 
to make that "pretty" and "performant"?


Because the moment, your "pretty" language is out there, be sure users 
will kick your behind that whenever they use that "pretty" stuff on 
anything but a toy setup, it spirals their servers into a DOS attack state.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2


On 09/02/2014 12:20 PM, Joel Jacobson wrote:

On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner  wrote:

Joel Jacobson  wrote:


Sorry for being unclear, I didn't mean to suggest the main concern is
updating *all* rows.
The main concern is when you have a rather complex UPDATE WHERE clause,
aiming to update exactly one row. Some of the expressions might be
assertions, to just double-verify the values and to make it stand-out
you are checking those expressions.



These are two different problems which probably need two different
solutions.  Making the default behavior of a set-based command that
it throw an error if the resulting set is not exactly one row
doesn't seem like the right solution to either one of them.


I see your point.
Basically, we have two types of applications where PL/pgSQL is commonly used.
a) OLTP applications where you typically operate on one row for each
UPDATE command.


Your idea of what an OLTP application is seems flawed.


b) Data warehouseing applications where you process multiple rows in
each UPDATE command.


Ditto.


Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 10:27 PM, Merlin Moncure  wrote:
> What is the reasoning for breaking compatibiilty?  Why not improve the
> language that's there?

Because many suggested improvement are not possible without
breaking compatibility, at least in theory. See previous posts in the thread.

> where I would have had to so for computation reasons.  If you *must*
> process things row by row, why not use the facility in the language
> that handle that: "WHERE CURRENT OF".

Again, because I use PL/pgSQL functions for *all* data access,
which means I don't have any prepared statements updating single rows
from my application code outside the database, as my PL/pgSQL code
*is* my application and I don't have any direct table access from outside
the database and my PL/pgSQL functions.

That means even something as simple as updating some column(s) in a table
matching the primary key, is done inside a PL/pgSQL function.

>
>> + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
>> STRICT only works if no rows should be an error, but there is currently no
>> nice way if no rows OR exactly 1 row should be found by the query.
>
> I see the point here, but this is not a fundamental problem with the
> language IMO.

Yes it is a language problem. Show me how to do it in a nice way with PL/pgSQL?
It cannot be done.

>> + Change all warnings into errors
>
> This is an ok idea, but not sure why you have to re-invent pl/pgsql to do it.

> Here are the headaches I see:
>
> * performance: plpsql can be slow for many types of iterative
> processing.  everybody wants the language to run faster but rewriting
> from scratch doesn't seem a good way to do that unless the current
> language structure has some critical performance blocking shortcoming.

Performance is always nice, but that won't require a new language though.

> * lack of non-table data structures (like hashmap, etc).  At present
> pl/plgsql only has arrays to manage temporary non-table data.  this is
> where plpgsql is bad but many of the other languages like pl/v8 etc
> are good.

+1. I've missed hashmaps sometimes. I usually resort to temp tables.

-- 
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] PL/pgSQL 2


On 09/01/2014 09:06 PM, Andrew Dunstan wrote:


On 09/01/2014 08:09 PM, Neil Tiffin wrote:
The docs also tell you how to avoid having to do this, using dollar quoting.




That should be enough alone to suggest postgreSQL start working on a modern, in 
core, fast, fully supported language.  Of course PL/pgSQL works, but so did 
one-line 5k perl programs that nobody likes today.  Everything can be done in 
assembler, but no one suggests that today.  Today, it is all about programmer 
productivity.  PL/pgSQL has a lot of unnecessary stuff that sucks the life out 
of programmer productivity.  And this should be very much a concern of the 
professionals that support PostgreSQL

For example:

DECLARE
declarations
BEGIN
statements
END

This looks a lot like COBOL or Pascal, and today is mostly unnecessary.


It looks like Ada, and that's not an accident. (Nor is it a bad thing.)


First of all it is

[DECLARE
declarations]
BEGIN
statements
END;

Second "statements" includes the whole definition above as a "statement" 
and controls identifier visibility and such compatible to PL/SQL.


You want to lose that? Not such a great idea, IMHO.


Jan


--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 10:16 PM, Andres Freund  wrote:
> On 2014-09-02 19:06:02 +0200, Joel Jacobson wrote:
>> But what do you think about,
>> STRICT UPDATE ...?
>
> I quite dislike it. An UPDATE isn't less 'strict' (whatever that means)
> if updates more than one row. There's some sense in the way it's used
> for INTO because it's referring to the INTO. And it's much more obvious
> what it could mean there.

For those who are familiar with the "INTO STRICT" syntax for SELECT,
I think they will also understand the meaning with UPDATE,
and maybe it could also be considered a plus not having to invent new keywords,
if we can use the ones we already have for other commands.

-- 
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] PL/pgSQL 2

2014-09-02 Thread David Johnston

On Tue, Sep 2, 2014 at 4:48 PM, Joshua D. Drake 
wrote:

>
> On 09/02/2014 09:48 AM, Bruce Momjian wrote:
>
>  As a case in point, EDB have spent quite a few man-years on their Oracle
>>> compatibility layer; and it's still not a terribly exact match, according
>>> to my colleagues who have looked at it.  So that is a tarbaby I don't
>>> personally care to touch ... even ignoring the fact that cutting off
>>> EDB's air supply wouldn't be a good thing for the community to do.
>>>
>>
> What any commercial entity and the Community do are mutually exclusive and
> we can not and should not determine what features we will support based on
> any commercial endeavor.
>
>
From where I sit the "mutually exclusive" argument doesn't seem to be true
- and in fact is something I think would be bad if it were.  We shouldn't
be afraid to add features to core that vendors are offering but at the same
time the fact that the Oracle compatibility aspects are commercial instead
of in-core is a plus to help ensure that there are people making a decent
living off PostgreSQL and thus are invested in its future - and directly
enticed to improve our product in order to get them more converts.  I don't
believe the community wants to compete on that basis nor does necessarily
standardizing the layer and letting the vendors compete on consulting and
implementation services seem a strong investment for the community to make.

There is no way to consider development plans without considering what the
entire eco-system is doing: commercial and community both.  A blanket
statement like above is a good way to make sure you don't get too carried
away with letting commercial vendors provide things that should be in core;
but at the same time the hurdle becomes higher if those features can be had
commercially.

My $0.02

David J.

Re: [HACKERS] PL/pgSQL 2


Without having read the entire thread ...

On 09/01/2014 05:04 AM, Joel Jacobson wrote:

 From the top of my head, these are Things I personally would want to
see in plpgsql2:
+ Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
row, as that's the most common use-case, and provide alternative syntax
to modify multiple or zero rows.


I think this is a completely flawed proposal "by definition". SQL itself 
does not make such assumption and as a SET oriented language, never 
should. A SET is zero or more tuples. Would you also suggest that the 
PostgreSQL backend throw an ERROR if an UPDATE/INSERT/DELETE doesn't 
modify exactly 1 row? If not, explain why there should be a difference 
between SQL executed from the frontend and SQL executed by a PL/pgSQL 
function.


-1 from me.



+ Change all warnings into errors


I suggest being slightly more selective on that one.


Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] PL/pgSQL 2



On 09/02/2014 09:48 AM, Bruce Momjian wrote:


As a case in point, EDB have spent quite a few man-years on their Oracle
compatibility layer; and it's still not a terribly exact match, according
to my colleagues who have looked at it.  So that is a tarbaby I don't
personally care to touch ... even ignoring the fact that cutting off
EDB's air supply wouldn't be a good thing for the community to do.


What any commercial entity and the Community do are mutually exclusive 
and we can not and should not determine what features we will support 
based on any commercial endeavor.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
"If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans."


--
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] PL/pgSQL 2

2014-09-02 Thread Merlin Moncure

On Mon, Sep 1, 2014 at 4:04 AM, Joel Jacobson  wrote:
> Hi,
>
> For those of you who use PL/pgSQL every day, I'm quite certain you all feel
> there are a number of things you would like to change in the language, but
> realize it cannot be achieved without possibly breaking compatibility, at
> least in theory. Even though you own code would survive the change, there
> might be code somewhere in the world which would break. This is of course
> not acceptable and that's why we have the current status quo of development,
> or at least not far away from a status quo.
>
> So instead of continue to adding optional settings to the config file, and
> instead of killing discussions around what can be done by bringing up the
> backwards-compatibility argument, let's instead fork the language and call
> it plpgsql2. Since no code is yet written in plpgsql2, we can start of from
> a clean sheet, and no good ideas need to be killed due to
> backwards-compatibility concerns.

What is the reasoning for breaking compatibiilty?  Why not improve the
language that's there?

> From the top of my head, these are Things I personally would want to see in
> plpgsql2:
> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row,
> as that's the most common use-case, and provide alternative syntax to modify
> multiple or zero rows.

This is the exact wrong thing to do.  It may be the most common use
case for *you*, but it's certainly not for me and is objectively a
step backards; set based processing is generally faster and less error
prone.  Although we have to write loops sometimes (say, for complex
iteration or error handling), I tend to only do so as a last resort.
Windows functions + custom aggregate functions have removed most cases
where I would have had to so for computation reasons.  If you *must*
process things row by row, why not use the facility in the language
that handle that: "WHERE CURRENT OF".

> + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
> STRICT only works if no rows should be an error, but there is currently no
> nice way if no rows OR exactly 1 row should be found by the query.

I see the point here, but this is not a fundamental problem with the
language IMO.

> + Change all warnings into errors

This is an ok idea, but not sure why you have to re-invent pl/pgsql to do it.

> These are small changes, probably possible with just a few hundred lines of
code in total, which also should be the ambition, as larger changes would
> never survive during time as it would require too much efforts to keep up
> with the main project. Secondly, I trust plpgsql mainly because it's being
> used by a lot of people in a lot of production systems, the same would not
> hold true for plpgsql2 for the first years of existence, so we who would use
> it in production systems must understand every single line of code changed
> and feel the risk of possible bugs and their impact are within acceptable
> boundaries.

Here are the headaches I see:

* performance: plpsql can be slow for many types of iterative
processing.  everybody wants the language to run faster but rewriting
from scratch doesn't seem a good way to do that unless the current
language structure has some critical performance blocking shortcoming.

* lack of non-table data structures (like hashmap, etc).  At present
pl/plgsql only has arrays to manage temporary non-table data.  this is
where plpgsql is bad but many of the other languages like pl/v8 etc
are good.

* not enough function-time validation (I think we agree on this).  the
situation has gotten better over the years but it'd be nice to have
stronger checks  raised for runtime code (although, I prefer them as
warnings, not errors).

* can't manage transaction state -- only can create sub transactions

* can't execute until after mvcc snapshot occurs (for example, you
can't change transaction isolation and/or lock tables to prevent
serialization errors)

* weak or non-existent debugging tools

None of the above points except for the transaction management issue
seem to be solved easier in a new language vs an old one.  Non sql
based languages pls (I figure plv8 is the best choice out of many)
would fix the performance issue but you'd have to give up first class
SQL statements to get that.

merlin

-- 
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] PL/pgSQL 2

2014-09-02 Thread Andres Freund

On 2014-09-02 19:06:02 +0200, Joel Jacobson wrote:
> But what do you think about,
> STRICT UPDATE ...?

I quite dislike it. An UPDATE isn't less 'strict' (whatever that means)
if updates more than one row. There's some sense in the way it's used
for INTO because it's referring to the INTO. And it's much more obvious
what it could mean there.

Greetings,

Andres Freund

-- 
 Andres Freund 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] PL/pgSQL 2

On Tue, Sep  2, 2014 at 07:06:02PM +0200, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 7:01 PM, Bruce Momjian  wrote:
> > On Tue, Sep  2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote:
> >> On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian  wrote:
> >> > SINGLETON UPDATE ...?
> >>
> >> Does it come with built-in spell check? :-) It's a bit long to write.
> >> I like STRICT, that maps good to what we already have with SELECT ...
> >> INTO STRICT.
> >
> > Spell checker coming in plpgsql3!  ;-)
> >
> > Anyway, as you have seen, the problem is not creating plpgsql2 --- you
> > could do that yourself on your own and distribute it.  What you want is
> > for the community to develop/maintain it, and as you have also seen,
> > everyone uses plpgsql slightly differently, so if you want the community
> > to maintain it, you are only going to get some of the things you want.
> 
> Very true, I totally agree.
> 
> But what do you think about,
> STRICT UPDATE ...?

Seems fine to me.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] PL/pgSQL 2

2014-09-02 Thread Kevin Grittner

Marko Tiikkaja  wrote:

> Sounds like in this case you'd only use set-oriented programming
> at the end of the transaction, no?

I guess -- more properly I would say "in the final database
transaction for that financial transaction."  And no, that never
made me wish that plpgsql functions defaulted to throwing errors
for DML statements that affected more than one row.

--
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] PL/pgSQL 2

2014-09-02 Thread Marko Tiikkaja




On 2014-09-02 19:33, Kevin Grittner wrote:

Marko Tiikkaja  wrote:


Well, just off the top of my head a normal function invocation could be:
one worker working on a single "order" started by a single end user to
transfer money from one account to another.  And we have *a lot* of code
like this where there isn't a way to write the code in "set-oriented
style" without inventing a time machine.  Which just might be out of the
scope of plpgsql2 (or perhaps as a GUC).


That's almost exactly a situation I was going to use to illustrate
where I *did* want set-oriented behavior.  One hard rule in the
shop in question was that an application is *never* allowed to
leave a database transaction pending while waiting for user input.


Of course the transaction isn't open while waiting for user input.  But 
for the lifetime of the "order", almost all of the code is nearly always 
dealing with one of a number of things, and almost never with more than 
one of anything.



Another was that a financial transaction must be committed to the
database as one database transaction, with ID numbers that were
assigned in commit sequence, with no gaps, to satisfy the auditors.
Just to complete the scope of the issue, each time a database
transaction was run, it got a random connection from a connection
pool, so temporary tables could not be used across transactions.

Think about that for a minute.

What we did was to create permanent work tables with a temporary ID
for a financial transaction in process as part of the primary key.
(The rest of the primary key matched the corresponding "normal"
table.)  The user takes however much time it takes to populate the
financial transaction, which typically affects many tables
including at least two (and sometimes hundreds of) rows in the
TransactionDetail table.  If they cancel out of the entry process
we delete all of the affected rows with one DELETE statement per
table.  If they OK the financial transaction we copy the data from
the work tables to the normal tables with one INSERT and one DELETE
per table -- all in one database transaction.  (The auditor
requirements were satisfied by some explicit locking and SEQUENCE
objects in that final database transaction.)  Fast, clean, and
effective.


Sounds like in this case you'd only use set-oriented programming at the 
end of the transaction, no?   Or was there a separate application 
gathering the details about the transaction, which then just sent all of 
that information in a batch into the database?



.marko


--
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] PL/pgSQL 2

2014-09-02 15:58 GMT+02:00 Marko Tiikkaja :

> On 9/2/14 3:52 PM, Joel Jacobson wrote:
>
>> On Tue, Sep 2, 2014 at 3:41 PM, Heikki Linnakangas
>>  wrote:
>>
>>> On 09/02/2014 04:32 PM, Joel Jacobson wrote:
>>>
 I think it's much better to make it the default behaviour in plpgsql2
 than to add a new syntax to plpgsql,
 because then we don't have to argue what to call the keyword or where to
 put it.

>>>
>>>
>>> Then you'll have to argue what the *other* syntax should look like. And
>>> not
>>> everyone agrees on the default either, see Kevin's email. Designing a new
>>> language is going to be an uphill battle, even more so than enhancing
>>> current plpgsql.
>>>
>>
>> Any ideas on what the *other* syntax could look like?
>>
>
> When I've played around with the idea of fixing PL/PgSQL in my head, what
> I had in mind is that UPDATE and DELETE not affecting exactly one row
> raises an exception, unless PERFORM is used.  PERFORM would set a special
> variable (e.g. ROW_COUNT) which can be consulted after the operation.
>
> For example:
>
> UPDATE foo WHERE bar = 1;  -- must affect exactly one row
> PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows
> IF row_count > 1 THEN
>   RAISE EXCEPTION 'oh no';
> END IF;
>
> This, obviously, requires us to get rid of the requirement for PERFORM
> today, which I see as a win as well.
>

This example is inspiration for me.

one row result can be enforced by some function option.

PERFORM update we don't need, because this code can be moved to other
function where one row result will not be enforced.

If I understand to Joel's motivation, he don't need to mix both styles in
one function.

one row result can be enforced by function option, function setting or by
GUC.

Regards

Pavel


>
>
> .marko
>

Re: [HACKERS] PL/pgSQL 2

2014-09-02 Thread Heikki Linnakangas

On 09/02/2014 07:51 PM, Marko Tiikkaja wrote:

On 9/2/14 6:03 PM, Heikki Linnakangas wrote:

Marko posted a patch to add assertions to PL/pgSQL last year, see
http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a
long thread, but in the end I think everyone was more or less OK with
the syntax "ASSERT ;". I also think that syntax is fine, and
it would be a nice feature, assuming we can avoid reserving the ASSERT
keyword.

Did you really mean to say "more or less OK"? I didn't wade through the
thread, but my recollection is that I was the only one truly OK with it,
some people expressed concerns but appeared undecided, and the rest of
the participants were completely against it.

I didn't pay attention back then, but I just scanned through the thread
in the archives. Peter Eisentraut was fine with the syntax [1]. Pavel
Stehule was OK with "ASSERT ...", although he wanted to be able to
specify a level [2]. Jaime Casanova was OK with it [3]. And you can
count me in too. I didn't see anyone strongly opposed to it.

There was worry about reserving the keyword, and Pavel wanted the
level-feature, and there was discussion on the details of whether it can
be caught with EXCEPTION WHEN OTHERS. Those are important details, but
there was a pretty good consensus on the basic ASSERT syntax by my count.

[1]
http://www.postgresql.org/message-id/1385527300.28256.15.ca...@vanquo.pezone.net)
[2]
http://www.postgresql.org/message-id/CAFj8pRAoO=jms+d30bk6j1prvr04oaxdhya9po7pqy7zsbu...@mail.gmail.com
[3]
http://www.postgresql.org/message-id/cajkuy5g-vcmf65jducnxdsywlr5sjeroqvtugd_+eie+2dd...@mail.gmail.com

- Heikki

--
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] PL/pgSQL 2

2014-09-02 Thread Kevin Grittner

Marko Tiikkaja  wrote:

> Well, just off the top of my head a normal function invocation could be:
> one worker working on a single "order" started by a single end user to
> transfer money from one account to another.  And we have *a lot* of code
> like this where there isn't a way to write the code in "set-oriented
> style" without inventing a time machine.  Which just might be out of the
> scope of plpgsql2 (or perhaps as a GUC).

That's almost exactly a situation I was going to use to illustrate
where I *did* want set-oriented behavior.  One hard rule in the
shop in question was that an application is *never* allowed to
leave a database transaction pending while waiting for user input.
Another was that a financial transaction must be committed to the
database as one database transaction, with ID numbers that were
assigned in commit sequence, with no gaps, to satisfy the auditors.
Just to complete the scope of the issue, each time a database
transaction was run, it got a random connection from a connection
pool, so temporary tables could not be used across transactions.

Think about that for a minute.

What we did was to create permanent work tables with a temporary ID
for a financial transaction in process as part of the primary key.
(The rest of the primary key matched the corresponding "normal"
table.)  The user takes however much time it takes to populate the
financial transaction, which typically affects many tables
including at least two (and sometimes hundreds of) rows in the
TransactionDetail table.  If they cancel out of the entry process
we delete all of the affected rows with one DELETE statement per
table.  If they OK the financial transaction we copy the data from
the work tables to the normal tables with one INSERT and one DELETE
per table -- all in one database transaction.  (The auditor
requirements were satisfied by some explicit locking and SEQUENCE
objects in that final database transaction.)  Fast, clean, and
effective.

--
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] PL/pgSQL 2

2014-09-02 Thread Marko Tiikkaja


On 9/2/14 6:31 PM, Heikki Linnakangas wrote:

On 09/02/2014 07:12 PM, Joel Jacobson wrote:

For me, updating a row, is like setting a variable in a normal language.
No normal language would require two rows to set a variable.
It would be like having to do:
 my $var = 10;
 die unless $var == 10;
in Perl to set a variable.


I don't think most applications are like that. See Kevin's comments
about doing things in a set-oriented way instead of row-by-row. I know
I've changed several procedures from the row-oriented style, looping
over rows with a FOR loop, updating each one individually, to
set-oriented style with a single UPDATE for a bunch of rows. It makes
for more concise code, and performs better. I'm sure there are
counter-examples, and I've also written many UPDATE statements that are
expected to update exactly one row, but I find an ASSERT would be
adequate for that.


Well, just off the top of my head a normal function invocation could be: 
one worker working on a single "order" started by a single end user to 
transfer money from one account to another.  And we have *a lot* of code 
like this where there isn't a way to write the code in "set-oriented 
style" without inventing a time machine.  Which just might be out of the 
scope of plpgsql2 (or perhaps as a GUC).



.marko


--
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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 7:01 PM, Bruce Momjian  wrote:
> On Tue, Sep  2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote:
>> On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian  wrote:
>> > SINGLETON UPDATE ...?
>>
>> Does it come with built-in spell check? :-) It's a bit long to write.
>> I like STRICT, that maps good to what we already have with SELECT ...
>> INTO STRICT.
>
> Spell checker coming in plpgsql3!  ;-)
>
> Anyway, as you have seen, the problem is not creating plpgsql2 --- you
> could do that yourself on your own and distribute it.  What you want is
> for the community to develop/maintain it, and as you have also seen,
> everyone uses plpgsql slightly differently, so if you want the community
> to maintain it, you are only going to get some of the things you want.

Very true, I totally agree.

But what do you think about,
STRICT UPDATE ...?

> Of course, the idea of upgrading plpgsql is long overdue, so it is very
> good we are all talking about it.

I'm very glad you share that opinion. :-)


-- 
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] PL/pgSQL 2

On Tue, Sep  2, 2014 at 06:57:42PM +0200, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian  wrote:
> > SINGLETON UPDATE ...?
> 
> Does it come with built-in spell check? :-) It's a bit long to write.
> I like STRICT, that maps good to what we already have with SELECT ...
> INTO STRICT.

Spell checker coming in plpgsql3!  ;-)

Anyway, as you have seen, the problem is not creating plpgsql2 --- you
could do that yourself on your own and distribute it.  What you want is
for the community to develop/maintain it, and as you have also seen,
everyone uses plpgsql slightly differently, so if you want the community
to maintain it, you are only going to get some of the things you want.

Of course, the idea of upgrading plpgsql is long overdue, so it is very
good we are all talking about it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +

-- 
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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 6:45 PM, Bruce Momjian  wrote:
> SINGLETON UPDATE ...?

Does it come with built-in spell check? :-) It's a bit long to write.
I like STRICT, that maps good to what we already have with SELECT ...
INTO STRICT.


-- 
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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 6:31 PM, Heikki Linnakangas
 wrote:
> I don't think most applications are like that. See Kevin's comments about
> doing things in a set-oriented way instead of row-by-row. I know I've
> changed several procedures from the row-oriented style, looping over rows
> with a FOR loop, updating each one individually, to set-oriented style with
> a single UPDATE for a bunch of rows. It makes for more concise code, and
> performs better. I'm sure there are counter-examples, and I've also written
> many UPDATE statements that are expected to update exactly one row, but I
> find an ASSERT would be adequate for that.

I'm *not* doing FOR-loops with UPDATE of single rows. I typically have
functions which have an input variable, which maps to a primary key in
a table, and the UPDATE is made on that single row. This is a
simplificaiton, but the main point is that the typical use case is
*not* FOR-loops.


-- 
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] PL/pgSQL 2

2014-09-02 Thread Marko Tiikkaja


On 9/2/14 6:03 PM, Heikki Linnakangas wrote:

Marko posted a patch to add assertions to PL/pgSQL last year, see
http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a
long thread, but in the end I think everyone was more or less OK with
the syntax "ASSERT ;". I also think that syntax is fine, and
it would be a nice feature, assuming we can avoid reserving the ASSERT
keyword.


Did you really mean to say "more or less OK"?  I didn't wade through the 
thread, but my recollection is that I was the only one truly OK with it, 
some people expressed concerns but appeared undecided, and the rest of 
the participants were completely against it.



I think that would actually be a good way to enforce the rule that an
UPDATE only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after
the update.


I agree with Joel here; I think a shorter syntax is necessary.


.marko


--
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] PL/pgSQL 2

On Tue, Sep  2, 2014 at 12:40:14AM -0400, Tom Lane wrote:
> Craig Ringer  writes:
> > If someone came up with a convincing PL/SQL compatibility layer then
> > it'd be worth considering adopting - when it was ready. But of course,
> > anyone who does the work for that is quite likely to want to sell it to
> > cashed-up Oracle users looking to save a few hundred grand on per-CPU
> > licensing.
> 
> As a case in point, EDB have spent quite a few man-years on their Oracle
> compatibility layer; and it's still not a terribly exact match, according
> to my colleagues who have looked at it.  So that is a tarbaby I don't
> personally care to touch ... even ignoring the fact that cutting off
> EDB's air supply wouldn't be a good thing for the community to do.

FYI, the docs of what EDB has done are online:

Server:

http://www.enterprisedb.com/docs/en/9.3/eeguide/Table%2520of%2520Contents.htm

Server packages, e.g. DBMS_:

http://www.enterprisedb.com/docs/en/9.3/eeguide/Postgres_Plus_Enterprise_Edition_Guide-52.htm#P14240_790554

Oracle Compatibility Guide:

http://www.enterprisedb.com/docs/en/9.3/oracompat/Table%2520of%2520Contents.htm

PL/SQL, called Stored Procedure Language:

http://www.enterprisedb.com/docs/en/9.3/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-78.htm#P6933_375311

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] PL/pgSQL 2

On Tue, Sep  2, 2014 at 04:24:11PM +0200, Andres Freund wrote:
> On 2014-09-02 10:21:50 -0400, Tom Lane wrote:
> > Marko Tiikkaja  writes:
> > > For example:
> > 
> > > UPDATE foo WHERE bar = 1;  -- must affect exactly one row
> > > PERFORM UPDATE foo WHERE bar = 1; -- can affect any number of rows
> > 
> > FWIW, I agree with the position that this would be a completely wrong
> > thing to do.  UPDATE should work like it does in plain SQL.  If you want
> > a restriction to "exactly one row", that needs to be a modifier.
> > 
> > I take no position on how the modifier should be spelled, though.
> 
> Personally I think 
> ONE ROW UPDATE ...
> reads nicely and SQL-ish. But it's not very expandable to other numbers.

SINGLETON UPDATE ...?


-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] PL/pgSQL 2



On 02/09/14 18:33, Hannu Krosing wrote:

On 09/02/2014 06:27 PM, Joel Jacobson wrote:

On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa  wrote:

 We are definitely worse. This is the problem, we only look to our own
belly bottom (if this expression exists in English). All NoSQL scale
*easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
they don't suck at many many other things, or that some of them may be worse
solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
far away from what we need to compete agains NoSQL in these regards.

So the discussion started out with a desire to improve PL/pgSQL. Now
somehow NoSQL and JSON is discussed in the same thread. Interesting.
Godwin's Law never fails :-)
http://en.wikipedia.org/wiki/Godwin's_law

Not to mention completely unsubstantiated claims about *all* NoSQL
scaling *easily* and *transparently* beyond one node :)



Honestly, this is off-topic and we can argue forever, but 
regardless all do or not, what's sure is that Postgres doesn't have 
horizontal scalability. Period.


And this is what we should look at. And we can't claim we're NoSQL 
until we have (easy, transparent) horizontal scalability.


Best,

Álvaro



--
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] PL/pgSQL 2

2014-09-02 Thread Hannu Krosing

On 09/02/2014 06:27 PM, Joel Jacobson wrote:
> On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa  
> wrote:
>> We are definitely worse. This is the problem, we only look to our own
>> belly bottom (if this expression exists in English). All NoSQL scale
>> *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
>> they don't suck at many many other things, or that some of them may be worse
>> solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
>> far away from what we need to compete agains NoSQL in these regards.
> So the discussion started out with a desire to improve PL/pgSQL. Now
> somehow NoSQL and JSON is discussed in the same thread. Interesting.
> Godwin's Law never fails :-)
> http://en.wikipedia.org/wiki/Godwin's_law
Not to mention completely unsubstantiated claims about *all* NoSQL
scaling *easily* and *transparently* beyond one node :)

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] PL/pgSQL 2

2014-09-02 Thread Heikki Linnakangas


On 09/02/2014 07:12 PM, Joel Jacobson wrote:

On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas
 wrote:

I think that would actually be a good way to enforce the rule that an UPDATE
only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after the
update.


So instead of one line of code, I would need to write two lines of
code at almost *all* places where a currently have an UPDATE. :-(


Right. Doesn't really seem that bad, to be honest. You can put it on the 
same line if you wish.



I think the problem with my perspective is my ambitions. I use
PL/pgSQL not as a secondary language, but it's my primary language for
developing applications.


Sure, a lot of people do that.


For me, updating a row, is like setting a variable in a normal language.
No normal language would require two rows to set a variable.
It would be like having to do:
my $var = 10;
die unless $var == 10;
in Perl to set a variable.


I don't think most applications are like that. See Kevin's comments 
about doing things in a set-oriented way instead of row-by-row. I know 
I've changed several procedures from the row-oriented style, looping 
over rows with a FOR loop, updating each one individually, to 
set-oriented style with a single UPDATE for a bunch of rows. It makes 
for more concise code, and performs better. I'm sure there are 
counter-examples, and I've also written many UPDATE statements that are 
expected to update exactly one row, but I find an ASSERT would be 
adequate for that.


- Heikki


--
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] PL/pgSQL 2



On 02/09/14 18:20, Joel Jacobson wrote:

On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner  wrote:

Joel Jacobson  wrote:


Sorry for being unclear, I didn't mean to suggest the main concern is
updating *all* rows.
The main concern is when you have a rather complex UPDATE WHERE clause,
aiming to update exactly one row. Some of the expressions might be
assertions, to just double-verify the values and to make it stand-out
you are checking those expressions.


These are two different problems which probably need two different
solutions.  Making the default behavior of a set-based command that
it throw an error if the resulting set is not exactly one row
doesn't seem like the right solution to either one of them.

I see your point.
Basically, we have two types of applications where PL/pgSQL is commonly used.
a) OLTP applications where you typically operate on one row for each
UPDATE command.
b) Data warehouseing applications where you process multiple rows in
each UPDATE command.

Both have different desired default behaviours of the different
set-based commands used in PL/pgSQL.
I think both are important enough to motivate a nice syntax for both use-cases.
If we cannot change the default behaviour of UPDATE, then I vote for
the eariler proposed STRICT UPDATE syntax.


I see both use cases, but I think the SQL default are set 
operations. For this particular point, if there would be just a syntax 
change rather than a new language, in the line of the "ONE ROW UPDATE" 
syntax, I'd say "UNIQUE UPDATE". This {ONE ROW, UNIQUE} syntax may also 
enforce having a WHERE clause. I find both better than the STRICT, which 
is not doing what -you may think- it does.


Regards,

Álvaro


--
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] PL/pgSQL 2

2014-09-02 18:03 GMT+02:00 Heikki Linnakangas :

> On 09/02/2014 06:44 PM, Joel Jacobson wrote:
>
>> On Tue, Sep 2, 2014 at 5:08 PM, Kevin Grittner  wrote:
>>
>>> Marko Tiikkaja  wrote:
>>>
 No, but your code can have a bug.

>>>
>>> So the main use case is to allow buggy functions which are deployed
>>> to production without adequate testing to be detected?  Bugs like
>>> not getting the primary key column(s) right?  I think it would be
>>> great to have some way to generate an error if a given statement
>>> doesn't affect exactly one row, but the above is a pretty weak
>>> argument for making it a default behavior.
>>>
>>
>> Instead of writing unit tests for such trivial things as updating one row
>> and testing if it got updated, it's better to make such unit tests
>> asserts instead,
>> which is exactly what we achieve if we provide a syntax to throw an error
>> if
>> not exactly 1 row was affected.
>>
>
> Marko posted a patch to add assertions to PL/pgSQL last year, see
> http://www.postgresql.org/message-id/5234af3f.4000...@joh.to. It was a
> long thread, but in the end I think everyone was more or less OK with the
> syntax "ASSERT ;". I also think that syntax is fine, and it
> would be a nice feature, assuming we can avoid reserving the ASSERT keyword.
>
> I think that would actually be a good way to enforce the rule that an
> UPDATE only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after
> the update.
>

I like it

Regards

Pavel


>
> - Heikki
>
>
>
>
> --
> 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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa  wrote:
> We are definitely worse. This is the problem, we only look to our own
> belly bottom (if this expression exists in English). All NoSQL scale
> *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
> they don't suck at many many other things, or that some of them may be worse
> solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
> far away from what we need to compete agains NoSQL in these regards.

So the discussion started out with a desire to improve PL/pgSQL. Now
somehow NoSQL and JSON is discussed in the same thread. Interesting.
Godwin's Law never fails :-)
http://en.wikipedia.org/wiki/Godwin's_law


-- 
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] PL/pgSQL 2

2014-09-02 Thread Andrew Dunstan



On 09/02/2014 12:12 PM, Joel Jacobson wrote:

On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas
 wrote:

I think that would actually be a good way to enforce the rule that an UPDATE
only updates a single row. Just put a "ASSERT ROW_COUNT=1;" after the
update.

So instead of one line of code, I would need to write two lines of
code at almost *all* places where a currently have an UPDATE. :-(
In that case, I think "RETURNING TRUE INTO STRICT _OK" is less ugly.

I think the problem with my perspective is my ambitions. I use
PL/pgSQL not as a secondary language, but it's my primary language for
developing applications.
For me, updating a row, is like setting a variable in a normal language.
No normal language would require two rows to set a variable.
It would be like having to do:
my $var = 10;
die unless $var == 10;
in Perl to set a variable.






That's really a problem with your perspective. UPDATE is inherently set 
oriented. It's emphatically NOT like setting a single variable.


I must have written tens, possibly hundreds of thousands of lines of 
plpgsql, and this have never ever been a problem for me.


I'd be very opposed to adding some special new plpgsql-only syntax to 
have UPDATE or DELETE error out if they affected more than a single row. 
And as you and others have observed, you can do that now with the 
"RETURNING true INTO STRICT ok" trick.


cheers

andrew


--
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] PL/pgSQL 2

On Tue, Sep 2, 2014 at 6:09 PM, Kevin Grittner  wrote:
> Joel Jacobson  wrote:
>
>> Sorry for being unclear, I didn't mean to suggest the main concern is
>> updating *all* rows.
>> The main concern is when you have a rather complex UPDATE WHERE clause,
>> aiming to update exactly one row. Some of the expressions might be
>> assertions, to just double-verify the values and to make it stand-out
>> you are checking those expressions.
>
>
> These are two different problems which probably need two different
> solutions.  Making the default behavior of a set-based command that
> it throw an error if the resulting set is not exactly one row
> doesn't seem like the right solution to either one of them.

I see your point.
Basically, we have two types of applications where PL/pgSQL is commonly used.
a) OLTP applications where you typically operate on one row for each
UPDATE command.
b) Data warehouseing applications where you process multiple rows in
each UPDATE command.

Both have different desired default behaviours of the different
set-based commands used in PL/pgSQL.
I think both are important enough to motivate a nice syntax for both use-cases.
If we cannot change the default behaviour of UPDATE, then I vote for
the eariler proposed STRICT UPDATE syntax.
That would not protect novice users (like myself a couple of years
ago) who falsly thinks an UPDATE which updated 0 rows would fail.
But at least it would provide them a quite nice syntax to fix that
when shit hits the fan due to their failure.

-- 
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] PL/pgSQL 2



On 02/09/14 17:03, Hannu Krosing wrote:

On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote:


On 02/09/14 11:44, Pavel Stehule wrote:






For 9.4, we have the media already saying "Postgres has
NoSQL capabilities" (which is only partially true). For x.y we
could have the media saying "Postgres adds Oracle compatibility"
(which would be only partially true). But that brings a lot of
users to postgres, and that helps us all.


Partial true can enforce so lot of people will hate postgres too. 
False promises are wrong


Then let's stop talking about postgres being NoSQL. NoSQL is 
basically "schema-less" (really bad name) plus "infinite scalability" 
(which basically means transparent sharding). We fail to provide the 
latter very clearly...
Have you ever tried any of the "real" NoSQL products version of 
"infinite scalability" ?
Yes, and they are absolutely not infinite, and they suck in many 
other places. But they scale beyond one node, transparently, something 
that postgres doesn't. And regardless, this is what people is buying, we 
like it or not.




We are no worse than most if you use just the unstructured part (which 
is what the NoSQL crowd provides) and something like pl/proxy for scaling.


We are definitely worse. This is the problem, we only look to our 
own belly bottom (if this expression exists in English). All NoSQL scale 
*easily*, *transparently* beyond one node. Postgres doesn't. I'm not 
saying they don't suck at many many other things, or that some of them 
may be worse solution than the problem. But despite JSON/JSONB in pg is 
awesome, it's far far away from what we need to compete agains NoSQL in 
these regards.


Ask anyone not in the postgres world to use pl/proxy for scaling 
and they will run away to mongo/whatever. Talk about HA... and the 
discussion is over :( I know how hard these problems are in the general, 
transactional approach that postgres takes, and that NoSQL does this for 
very simple, non-ACID cases, but they do. Hence, we cannot claim NoSQL 
"compliance", just because we have jsonb. Unfortunately :( (Surely we do 
have many other values, but let's not say that we have NoSQL 
capabilities, because we don't while others -better or worse- do).


Regards,


Álvaro

Re: [HACKERS] PL/pgSQL 2