16 parameter limit - Mailing list pgsql-sql
From | John Proctor |
---|---|
Subject | 16 parameter limit |
Date | |
Msg-id | 200204030211.g332BLa15946@slxmail01.prium.net Whole thread Raw |
Responses |
Re: 16 parameter limit
("Josh Berkus" <josh@agliodbs.com>)
Re: 16 parameter limit (Bruce Momjian <pgman@candle.pha.pa.us>) |
List | pgsql-sql |
There was a message posted in March regarding this. Bruce replied that this issue did not come up often. However, I think there is more to it than that. I think one reason that it does not come up is because most Oracle DBAs are not going to dig through mailing lists and take the time to post questions. Once they discover that PL/pgSQL != PL/SQL they just move on. I think that the limitations of PL/pgSQL is a huge factor in people not being able to use Postgres instead of Oracle. My company is quite small, but we have several very large insurance companies for clients that we develop web based applications for. Currently I have 5 schemas totaling about 1500 tables and about as many stored procedures and functions. The applications do not even have any permissions on a single table. All selects are done on views and all inserts/updates/deletes are done through stored procedures. Our procs have many parameters, one per column or more. Most of the app developers do not even know that much about the schema. They just know the exposed procedural interface. Other issues similar to this with regards to PL/SQL are the need for packages and the ability to declare cursors ahead of time, like in a package so that they can be shared and opened when needed. This also makes much cleaner code since the select statement for many cursors clouds the code where it is used if it is inline like PL/pgSQL. Named parameters would also be nice and at least allowing the use of giving names to parameters in the declarations instead of $1, $2, etc. Also, the inablity to trap database "exceptions" is too limiting. In Oracle, we trap every single exception, start an autonomous transacation, log the exception to an exception table along with the procedure name, the operation being performed and some marker to mke it easy to locate the offending statement. This also allows us to recover, which is very important for imports and data loads. I work with many other Oracle DBAs and I think many have interest in Postgres, but also know that without a procedural language on par with PL/SQL that it is not possible to switch. All of the Oracle shops that I know of are very big on PL/SQL and write almost all business logic and table interfaces in it. It also seems that Microsoft SQLServer shops are moving in the same direction now that the procedural support for it is getting much better. I am not complaining about Postgres at all. I think it is fantastic and I enjoy using it for personal projects. However, I think it might be a bit misleading to assume that lack of posts regarding the limits of PL/pgSQL equate to it being adequate for most large applications. It is the number one reason that I could not use Postgres in 4 large insurance companies. John Proctor