Thread: COnsidering a move away from Postgres

I work for a college and we use PG currently as our main backend.  We are
currently developing with Java.  We are considering moving away from postgres
for the reasons I am going to list below.  I would appreciate some thoughts
from the Postgres community on way we should or shouldn't leave postgres.

1. Our dev plan involves alot of stored procedures to be used and we have
found the way this is done in PG to be painful. (ie.  To return multiple
record from different tables you have to define a type.  This is a pain to
maintain because if you ever have to change what it returns it cannot be
dropped because of dependencies etc.. In some other databases you can simpley
write a stored proc to return whatever the query inside returns and this is
handled dynamically)

2. Also with stored procs it is painful to return mulitple records. The syntax
is more complicated than some other databases.  (We are currently using
PL/SQL)

3. The tools.  PgAdmin does some things well but it is lacking the features of
some of the other gui tools.  This is not a big deal as we do also have
PgManage which is acceptable except I personally don't like it cause it
doesn't run in Linux and the Linux version is pretty bad.

Thank you for any input and help,

--
Jason Tesser
Developer for NMI
jtesser@nbbc.edu
Eph 2:8-10

Jason Tesser <jtesser@nbbc.edu> writes:
> 1. Our dev plan involves alot of stored procedures to be used and we have
> found the way this is done in PG to be painful. (ie.  To return multiple
> record from different tables you have to define a type.

FWIW, this won't be essential any more in 8.1.  See the examples in the
development documentation:
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

> 2. Also with stored procs it is painful to return mulitple records. The syntax
> is more complicated than some other databases.  (We are currently using
> PL/SQL)

What's so hard about RETURN NEXT?  What would you rather have?

> 3. The tools.  PgAdmin does some things well but it is lacking the features of
> some of the other gui tools.

I'm sure the pgAdmin guys would love having some more help.

            regards, tom lane

HI

On Thursday 30 June 2005 9:20 am, Tom Lane wrote:
> Jason Tesser <jtesser@nbbc.edu> writes:
> > 1. Our dev plan involves alot of stored procedures to be used and we have
> > found the way this is done in PG to be painful. (ie.  To return multiple
> > record from different tables you have to define a type.
>
> FWIW, this won't be essential any more in 8.1.  See the examples in the
> development documentation:
> http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-P
>ARAMETERS
> http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLP
>GSQL-DECLARATION-ALIASES
> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.ht
>ml#PLPGSQL-STATEMENTS-RETURNING

I might be missing it but how does this help me.  What I would like is to be
able to return multiple records from a select statement that return multiple
columns from different tables without having to create a type.  This is why
it is painful for us.  The management of types is bad because as far as I
know there is no alter type and the depencies become a nightmane if you ever
need to change something.


<snip>

--
Jason Tesser
Developer for NMI
jtesser@nbbc.edu
Eph 2:8-10

On Thu, 2005-06-30 at 10:18 -0500, Jason Tesser wrote:
> HI
>
> On Thursday 30 June 2005 9:20 am, Tom Lane wrote:
> > Jason Tesser <jtesser@nbbc.edu> writes:
> > > 1. Our dev plan involves alot of stored procedures to be used and we have
> > > found the way this is done in PG to be painful. (ie.  To return multiple
> > > record from different tables you have to define a type.
> >
> > FWIW, this won't be essential any more in 8.1.  See the examples in the
> > development documentation:
> > http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-P
> >ARAMETERS
> > http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLP
> >GSQL-DECLARATION-ALIASES
> > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.ht
> >ml#PLPGSQL-STATEMENTS-RETURNING
>
> I might be missing it but how does this help me.  What I would like is to be
> able to return multiple records from a select statement that return multiple
> columns from different tables without having to create a type.  This is why
> it is painful for us.  The management of types is bad because as far as I
> know there is no alter type and the depencies become a nightmane if you ever
> need to change something.
>
>
If I understand the new features correctly, rather than:
CREATE FUNCTION foo(i int) RETURNS custom_type AS ....
and custom_type is (int,text,text)
you will be able to do the following instead:
CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ...

As far as hard coding the OUT datatypes, if I understand the docs
correctly you can even:
CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
anyelement) AS ...

No custom type needed .. you specify how the output format in the
argument section itself.

Sven

Can this return multiples?  I thought when you dfined columns dynamically like
your example it only returns one record and I need to be able to return a
set.  Can your example return a set?

On Thursday 30 June 2005 10:58 am, Sven Willenberger wrote:
> If I understand the new features correctly, rather than:
> CREATE FUNCTION foo(i int) RETURNS custom_type AS ....
> and custom_type is (int,text,text)
> you will be able to do the following instead:
> CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ...
>
> As far as hard coding the OUT datatypes, if I understand the docs
> correctly you can even:
> CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
> anyelement) AS ...
>
> No custom type needed .. you specify how the output format in the
> argument section itself.
>
> Sven

--
Jason Tesser
Developer for NMI
jtesser@nbbc.edu
Eph 2:8-10

I've solved this for my case in 7.4 by defining a view with the desired column
layout and the return setof the view. This certainly depends on what you're
trying to accomplish.

On Thursday 30 June 2005 09:21 am, Jason Tesser wrote:
> Can this return multiples?  I thought when you dfined columns dynamically
> like your example it only returns one record and I need to be able to
> return a set.  Can your example return a set?
>
> On Thursday 30 June 2005 10:58 am, Sven Willenberger wrote:
> > If I understand the new features correctly, rather than:
> > CREATE FUNCTION foo(i int) RETURNS custom_type AS ....
> > and custom_type is (int,text,text)
> > you will be able to do the following instead:
> > CREATE FUNCTION foo(IN i int, OUT x int, OUT y text, OUT z text) AS ...
> >
> > As far as hard coding the OUT datatypes, if I understand the docs
> > correctly you can even:
> > CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
> > anyelement) AS ...
> >
> > No custom type needed .. you specify how the output format in the
> > argument section itself.
> >
> > Sven

--
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417

Tom Lane napisał(a):
> Jason Tesser <jtesser@nbbc.edu> writes:
>>3. The tools.  PgAdmin does some things well but it is lacking the features of
>>some of the other gui tools.
> I'm sure the pgAdmin guys would love having some more help.
What about sqlmanager.net - it the best GUI with number of great features ?

See: http://www.sqlmanager.net/en/products/postgresql/manager

ML

There are very nice inexpensive alternatives to PG Admin III.  Because
of it's cross platform nature it is severly lacking in many areas.

Check out PG Lightning Admin at:
http://www.amsoftwaredesign.com

>>3. The tools.  PgAdmin does some things well but it is lacking the features of
>>some of the other gui tools.
>>

Sven Willenberger <sven@dmv.com> writes:
> As far as hard coding the OUT datatypes, if I understand the docs
> correctly you can even:
> CREATE FUNCTION foo(IN i int, OUT x anyelement, OUT y anyelement, OUT z
> anyelement) AS ...

That exact example would not work --- anyelement/anyarray is all about
deducing output parameter types from input parameter types.  So you need
at least one anyelement or anyarray input parameter.  Here's a pretty
stupid example:

regression=# create function sum_n_prod (x anyelement, y anyelement,
regression(# OUT sum anyelement, OUT prod anyelement) as $$
regression$# begin
regression$#   sum := x + y;
regression$#   prod := x * y;
regression$# end$$ language plpgsql;
CREATE FUNCTION

This will work on any data type that has + and * operators.  You can't
tell very easily in psql, but the first of these examples returns two
integers and the second returns two numeric columns:

regression=# select * from sum_n_prod(33,44);
 sum | prod
-----+------
  77 | 1452
(1 row)

regression=# select * from sum_n_prod(33.4,44.7);
 sum  |  prod
------+---------
 78.1 | 1492.98
(1 row)


I'm not entirely clear on exactly what problem Jason is concerned about,
but I don't think anyelement/anyarray will help him much.  I do however
think that the out-parameter facility mostly fixes the specific
complaint of having to invent composite types just to return more than
one column.

            regards, tom lane

Jason Tesser <jtesser@nbbc.edu> writes:
> I might be missing it but how does this help me.  What I would like is to be
> able to return multiple records from a select statement that return multiple
> columns from different tables without having to create a type.

You mean like this?

regression=# create table t1 (f1 int, f2 text);
CREATE TABLE
regression=# insert into t1 values(1, 'one');
INSERT 0 1
regression=# insert into t1 values(2, 'two');
INSERT 0 1
regression=# create table t2 (k1 int, k2 text);
CREATE TABLE
regression=# insert into t2 values(1, 'uno');
INSERT 0 1
regression=# insert into t2 values(2, 'dos');
INSERT 0 1
regression=# create function countem(lim int, out n int, out en text,
regression(# out es text) returns setof record as $$
regression$# declare r record;
regression$# begin
regression$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim loop
regression$#     n := r.f1;
regression$#     en := r.f2;
regression$#     es := r.k2;
regression$#     return next;
regression$#   end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from countem(2);
 n | en  | es
---+-----+-----
 1 | one | uno
 2 | two | dos
(2 rows)


            regards, tom lane

On 6/30/05, Jason Tesser <jtesser@nbbc.edu> wrote:
> I work for a college and we use PG currently as our main backend.  We are
> currently developing with Java.  We are considering moving away from postgres
> for the reasons I am going to list below.  I would appreciate some thoughts
> from the Postgres community on way we should or shouldn't leave postgres.

Out of curiosity, what other backends do you consider and what is their
syntax for such problems.  Don't get me wrong, I don't intend to prove
anything by asking so.  I am just curious what syntax would you prefer,
or in other words, what syntax is most convenient for a person doing
procedural language intense project.  Hopefully it will help PL/pgSQL
develop in a best direction.

So, please post samples of syntax (and a DB-name, I'm curious about
other DBs syntaxes).

   Regards,
     Dawid

That is very similar to what I have been trying to do.  I have 1
question and one problem though.

Question:  DO I have to define every column I am returning as an out
going parameter?

Problem I ran your test and I am getting as error see below

test=# create function countum(lim int, out n int, out en text, out es
text) returns setof record as $$
test$# declare r record;
test$# begin test$# for r in select * from t1 join t2 on f1=k1 where f1
<= lim loop
test$# n := r.f1;
test$# en := r.f2;
test$# es := r.k2;
test$# return next;
test$# end loop; test$# end $$ language plpgsql;
ERROR:  CREATE FUNCTION / OUT parameters are not implemented

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 30, 2005 3:57 PM
To: Jason Tesser
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] COnsidering a move away from Postgres

Jason Tesser <jtesser@nbbc.edu> writes:
> I might be missing it but how does this help me.  What I would like is
to be
> able to return multiple records from a select statement that return
multiple
> columns from different tables without having to create a type.

You mean like this?

regression=# create table t1 (f1 int, f2 text);
CREATE TABLE
regression=# insert into t1 values(1, 'one');
INSERT 0 1
regression=# insert into t1 values(2, 'two');
INSERT 0 1
regression=# create table t2 (k1 int, k2 text);
CREATE TABLE
regression=# insert into t2 values(1, 'uno');
INSERT 0 1
regression=# insert into t2 values(2, 'dos');
INSERT 0 1
regression=# create function countem(lim int, out n int, out en text,
regression(# out es text) returns setof record as $$
regression$# declare r record;
regression$# begin
regression$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim
loop
regression$#     n := r.f1;
regression$#     en := r.f2;
regression$#     es := r.k2;
regression$#     return next;
regression$#   end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from countem(2);
 n | en  | es
---+-----+-----
 1 | one | uno
 2 | two | dos
(2 rows)


            regards, tom lane

OK I am an idiot you are running a cvs build I guess. Which at least
answers the problem.

"Jason Tesser" <JTesser@nbbc.edu> writes:
> Problem I ran your test and I am getting as error see below

This is an 8.1 feature not something that exists in current releases.

            regards, tom lane

Yes I figured it out could I bug one last time about my question :-)
Question:  DO I have to define every column I am returning as an out
going parameter?
You have been helpful Tom and I really do appreciate it.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 30, 2005 5:20 PM
To: Jason Tesser
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] COnsidering a move away from Postgres

"Jason Tesser" <JTesser@nbbc.edu> writes:
> Problem I ran your test and I am getting as error see below

This is an 8.1 feature not something that exists in current releases.

            regards, tom lane

"Jason Tesser" <JTesser@nbbc.edu> writes:
> Question:  DO I have to define every column I am returning as an out
> going parameter?

Well, yes, where else is the system going to get the information?

            regards, tom lane

Tony Caduto wrote:
> There are very nice inexpensive alternatives to PG Admin III.  Because
> of it's cross platform nature it is severly lacking in many areas.
>

In what way does making software cross-plaform cause it to be 'lacking
in many areas'?

> Check out PG Lightning Admin at:
> http://www.amsoftwaredesign.com
>

Did you get chance to have a look at Kylix for compiling PG Lightning
under Linux natively?

http://www.borland.com/us/products/kylix/

--

Russ

> Out of curiosity, what other backends do you consider and what is their
> syntax for such problems.

Most folks that use Oracle's PL/SQL like it.  I have a sneaking suspicion
Oracle used the GNAT parser for Ada as a starting point, but that is pure
conjecture.  Oracle does document that PL/SQL is Ada with SQL extensions.
An enterprising individual could get the source for GNAT from AdaCore (it's
GPL'd) and create a workalike language for PostgreSQL.  I'm not
enterprising.

Rick

pgsql-general-owner@postgresql.org wrote on 06/30/2005 04:03:49 PM:

> On 6/30/05, Jason Tesser <jtesser@nbbc.edu> wrote:
> > I work for a college and we use PG currently as our main backend.  We
are
> > currently developing with Java.  We are considering moving away
> from postgres
> > for the reasons I am going to list below.  I would appreciate some
thoughts
> > from the Postgres community on way we should or shouldn't leave
postgres.
>
> Out of curiosity, what other backends do you consider and what is their
> syntax for such problems.  Don't get me wrong, I don't intend to prove
> anything by asking so.  I am just curious what syntax would you prefer,
> or in other words, what syntax is most convenient for a person doing
> procedural language intense project.  Hopefully it will help PL/pgSQL
> develop in a best direction.
>
> So, please post samples of syntax (and a DB-name, I'm curious about
> other DBs syntaxes).
>
>    Regards,
>      Dawid
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

I have used PL/SQL for years. It's a great language that is easy to pick up and offers lots of ability/promise.  The syntax seems very easy for new people to pick up who might know another language or are just starting out.  Of course the same can be said of Ada code.  It's just very easy to read.