Re: [GENERAL] insert explanation
Simon Drabble wrote:
>
> On Thu, 14 Oct 1999, Chairudin Sentosa Harjo wrote:
>
> > Dear all,
> >
> > mydb=> create table rtext (rtext varchar(10));
> > CREATE
> > mydb=> insert into rtext values ('hello');
> > INSERT 17681 1
> >
> > What do "17681" and "1" mean?
> >
> > Could someone help me to understand this please?
> >
> > Regards,
> > Chai
>
> 17681: oid (object ID) of the inserted row
> 1: number of rows inserted.
>
Why does it start from 17681?
This is a fresh new table, shouldn't it start from 1?
Regards,
Chai
Re: [GENERAL] insert explanation
On Thu, 14 Oct 1999, Chairudin Sentosa Harjo wrote:
> Dear all,
>
> mydb=> create table rtext (rtext varchar(10));
> CREATE
> mydb=> insert into rtext values ('hello');
> INSERT 17681 1
>
> What do "17681" and "1" mean?
>
> Could someone help me to understand this please?
>
> Regards,
> Chai
17681: oid (object ID) of the inserted row
1: number of rows inserted.
Simon.
--
"Linux - open doors, not windows."
Simon Drabble It's like karma for your brain.
[EMAIL PROTECTED]
[GENERAL] FATAL 1: palloc failure: memory exhausted
Hi, Sorry, RTFM Have a nice day JohnH
[GENERAL] FATAL 1: palloc failure: memory exhausted
Hi, My php3 interface to postgres6.4 on BSDI 3.0 intel machine says: Warning: PostgresSQL query failed: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. in /usr/home/jrh/public_html/cam/cam.php3 on line 667 My logfile says: FATAL 1: palloc failure: memory exhausted If I try to vacuum analyze verbose I get: FATAL 1: VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2 pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. Advice? Especially about reading up on memory issues? Thanks, JohnH
[GENERAL] insert explanation
Dear all,
mydb=> create table rtext (rtext varchar(10));
CREATE
mydb=> insert into rtext values ('hello');
INSERT 17681 1
What do "17681" and "1" mean?
Could someone help me to understand this please?
Regards,
Chai
[GENERAL] Can you write into a view?
Hello, I'm a new into the postgres's world, and I was seeing the User's Guide from V.6.4 and I saw that you can write into a View. In the new versions can you? If not, have any body any idea of when it would be reality? Thanks Fernando Dougnac = __ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
Re: [GENERAL] stored procedure revisited
datamart is important for web. That is why HISTORICALLY, mySQL is so popular. BTW, I withdraw the opinion on mySQL, IMHO, it is too limited, no mention its not-generous-enough license. IF I have time, I will do it myself *sigh*. >From: Yin-So Chen <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: Re: [GENERAL] stored procedure revisited >Date: Wed, 13 Oct 1999 15:52:28 -0700 > >amy cheng wrote: > > > > C is good, and in a sense, for OSS we should encourage more C >"scripting" > > and "hacking" than script scripting. (perl and PL/pgSQL actually is >"bad" in > > this sense). Because IF everybody use C, the use and development will > > inherently related and the dev. speed will > > accelate exponentially. However, C/C++ is difficult (I use > > both C and perl, so I know it). Also, as GOOD excuse, C/C++ > > is not safe. So, we need PL SP. > >Well, not everyone in this world can work in the C level (I certainly >included myself here), and talking about languages is getting awefully >close to advocacy :) But just think this way though, if C is the route >to go, then why not assembly? When you have an answer of why not, you >also have an answer for C as well :) But OTOH, that's why C programmers >have nothing to fear about all the VB programmers out there... Because >there are jobs only C is appropriate. I am sure you all know this so >ignore my mumbling :) > > > > > However, I would like to see data warehouse (or more moderately and > > accurately data mart) support also -- the point: the priority? > > > >So, what is the priority? I will argue that SP is a higher priority >than data warehousing. The reason? More people would benefit from SP >than from data warehousing. Moreover, SP will also draw database >administrator's mind-share for PG. Who's going to work with the >database? Administrators & application developers, mostly. And if >there are features which most administrators or developers would >consider lacking, it would be a reason for them to look elsewhere. >W/out them pitching for PG, would PG compete well against commercial >databases? SPI is great and all, but there is a reason why a PL is also >developed. Since the PL is here, then SP is the next logical step :) > >Regards, > >yin-so chen > > > __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] stored procedure revisited
amy cheng wrote: > > C is good, and in a sense, for OSS we should encourage more C "scripting" > and "hacking" than script scripting. (perl and PL/pgSQL actually is "bad" in > this sense). Because IF everybody use C, the use and development will > inherently related and the dev. speed will > accelate exponentially. However, C/C++ is difficult (I use > both C and perl, so I know it). Also, as GOOD excuse, C/C++ > is not safe. So, we need PL SP. Well, not everyone in this world can work in the C level (I certainly included myself here), and talking about languages is getting awefully close to advocacy :) But just think this way though, if C is the route to go, then why not assembly? When you have an answer of why not, you also have an answer for C as well :) But OTOH, that's why C programmers have nothing to fear about all the VB programmers out there... Because there are jobs only C is appropriate. I am sure you all know this so ignore my mumbling :) > > However, I would like to see data warehouse (or more moderately and > accurately data mart) support also -- the point: the priority? > So, what is the priority? I will argue that SP is a higher priority than data warehousing. The reason? More people would benefit from SP than from data warehousing. Moreover, SP will also draw database administrator's mind-share for PG. Who's going to work with the database? Administrators & application developers, mostly. And if there are features which most administrators or developers would consider lacking, it would be a reason for them to look elsewhere. W/out them pitching for PG, would PG compete well against commercial databases? SPI is great and all, but there is a reason why a PL is also developed. Since the PL is here, then SP is the next logical step :) Regards, yin-so chen
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgresuser'spassword?
> Bruce Momjian wrote: > > > There is a todo item for the postgres user to have a password by default. > > > I'm not sure though how that would be done. Probably in initdb. (?) > > > > We could enabled it as part of initdb. Prompt them for it there, and > > assign it. Seems like there should be one on that account espeically. > > Also, allow a command line option to set the password for those who need > to automate things (like us RedHat people...). This is, I assume, for > the postgres user INSIDE the initial database structure, as opposed to > the postgres user on the OS. > > Since, under the RedHat installation, the initdb likely will happen > during initial system startup, having a prompt for a password at that > point is IMHO not good. Having a default password (in the initdb'd > pg_shadow) would be better. > > If this is about the OS userame 'postgres', ignore that. The RPM > installation already creates him, and makes it impossible to directly > log in as 'postgres' -- until root changes his password. No, this is about the pgsql-supplied postgres password. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's password?
Hi, followin this thread, I think It would be useful to allow user to connect to database he owned (created) without password even if pg_hba.conf is configured with password requirement to this database. Or owner of database could maintain list of users/groups whom he granted trusted connection. After user connects usual grant priviliges could works. Currently it's a pain to work with authentification system - I have to input my password every time I use psql and moreover I had to specify it in perl scripts I developed. Sometimes it's not easy to maintain secure file permissions espec. if several developers share common work. Any user (even not postgres user) could use stealed password to connects to your database. In my proposal, security is rely on local login security. You already passed password control. There are another checks like priviliges. You write your scripts without hardcoded passwords ! Of course this could be just an option in case you need "paranoic" security. Having more granulated privilege types as Mysql does would only make my proposal more secure. You're allowed to connect, but owner of database could restrict you even list of tables, indices et. all. Regards, Oleg PS. I didn't find any plans to improve authen. in TODO On Wed, 13 Oct 1999, Peter Eisentraut wrote: > Date: Wed, 13 Oct 1999 21:56:15 +0200 (CEST) > From: Peter Eisentraut <[EMAIL PROTECTED]> > To: Lincoln Yeoh <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED], [EMAIL PROTECTED] > Subject: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's >password? > > On Oct 13, Lincoln Yeoh mentioned: > > > Then I have problems logging in as ANY user. Couldn't figure out what the > > default password for the postgres user was. Only after some messing around > > I found that I could log on as the postgres user with the password \N. Not > > obvious, at least to me. > > There is a todo item for the postgres user to have a password by default. > I'm not sure though how that would be done. Probably in initdb. (?) > > > I only guessed it after looking at the pg_pwd file and noticing a \N there. > > Is this where the passwords are stored? By the way should they be stored in > > the clear and in a 666 permissions file? How about hashing them with some > > salt? > > I had this on my personal things-to-consider-working-on list but I don't > see an official todo item. I am personally not sure why this is not done > but authentication and security are not most people's specialty around here. > (including me) > > > 1) There is no obvious way to specify the password for users when you > > create a user using the supplied shell script createuser. One has to resort > > to psql and stuff. > > Aah. Another misguided user. Some people are of the opinion that using the > createuser scripts is a bad idea because it gives you the wrong impression > of how things work. (All createuser does is call psql.) Of course, we > could somehow put a password prompt in there, I'll put that on the above > mentioned list. > > > 2) Neither is there an obvious and easy way to change the user's password. > > alter user joe with password "foo"; > > I'm not sure how obvious it is but it's certainly easy. > > > 3) You can specify a password for a user by using pg_passwd and stick it > > into a separate password file, but then there really is no link between > > createuser and pg_passwd. > > This shows how bad the idea of the scripts was in the first place. > > > I find the bundled scripts and their associated documentation make things > > very nonintuitive when one switches from a blind trust postgres to an > > authenticated postgres. > > So that would put your vote in the "drop altogether" column? Voting is > still in progress! > > -Peter > > -- > Peter Eisentraut Sernanders vaeg 10:115 > [EMAIL PROTECTED] 75262 Uppsala > http://yi.org/peter-e/Sweden > > > > _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgresuser's password?
> On Oct 13, Lincoln Yeoh mentioned: > > > Then I have problems logging in as ANY user. Couldn't figure out what the > > default password for the postgres user was. Only after some messing around > > I found that I could log on as the postgres user with the password \N. Not > > obvious, at least to me. > > There is a todo item for the postgres user to have a password by default. > I'm not sure though how that would be done. Probably in initdb. (?) We could enabled it as part of initdb. Prompt them for it there, and assign it. Seems like there should be one on that account espeically. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] stored procedure revisited
1) EOF is an app server that complys corba/tkom/ejb, or it is another
"standard"? if the latter, it should/will die! (I wish tkom die
also, seems impossble now though :-(
2) app tier is good, but if SP is forbidden, the cost is that you have to
write or buy more for things that comes with dbms for free! Also, less
performance. The bottom line is: for some thing that is naturally relational
(instead of OO), it is for both dev. cost and performance that they'd better
stay in dbms. I understand commercial dbms' SP also motivated by evil
locking desire, but there are strong legitimate reasons also. conclusion:
app and sp should co-exist.
3) of course, that is for single dbms, if you need coupled multiple
ones ("distributed db"), app tier is the choice;
4) cos (& IF) all dbms' SP are similar, SP can improve portability,
comparing with clever but irregular work-arounds. remember: change
db is a big decision, "dynamic" db change is a luxuary.
5)as for datamart, seems that mysql is a better choice. seems to me that Pg
is optimized for transactional, mysql is and will be more optimized for
datamart -- just opinions, I'll do more research/testing on this.
>From: Howie <[EMAIL PROTECTED]>
>Reply-To: Howie <[EMAIL PROTECTED]>
>To: amy cheng <[EMAIL PROTECTED]>
>CC: [EMAIL PROTECTED]
>Subject: Re: [GENERAL] stored procedure revisited
>Date: Wed, 13 Oct 1999 17:29:29 + (GMT)
>
>actually, one would hope that the system has its db independence in the
>application layer rather than the database layer. for instance, using
>something like NeXT's Enterprise Objects Framework to fetch rows from the
>db and translate the rows into objects, you only deal with the objects.
>The whole datastore, at this point, becomes irrelevant since you rarely
>deal with the underlying SQL -- EOF takes care of all that for you.
>Instead, you say "hey, i want all the objects that have their personName
>ivar equal to Amy" ( "personName = 'Amy'" ). I'm fairly positive that
>Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc ) does the same
>sort of thing.
>
>keeping inserts/selects/etc in stored procedures would still require a
>rewrite of all the stored procedures when moving to another db vendor,
>which may or may not be a large problem depending on that vendor's
>imeplementation of stored procedures and SQL in general. granted, you
>wouldnt have to completely gut the application and rewrite the whole
>bloody thing, but since your app is already going to have some of it
>rewritten ( cant use an OCI call on postgresql ), i think it'd made more
>sense to abstract things further by putting all the logic into your
>objects, EOF or Entity Javabeans, rather than in the db.
>
>so now lets talk code reuse. both options would give you about the same
>level of code reuse, but in two completely different ways. stored
>procedures ( and company/DBA policy ) pretty much force the user to take
>advantage of them rather than doing raw inserts, selects, etc on the
>underlying tables. EOF forces you to deal with the objects rather than
>sql. either way, all of your business logic is in one location. by
>using a higher-level language, however, you wouldnt have to deal with
>tedious pl/sql-ish programming. one could also argue that having 20+
>different stored procedures is really no better than memorizing the
>business logic and duplicating that in the application, bypassing the
>procedures altogether. if you have to deal with developing on one dbms
>and deploying on another dbms, EOF starts to look even more beautiful --
>since your logic is in the objects, not the db, nothing will have to be
>ported to the new dbms. in fact, all you really need to do is change the
>EOModel; all of your code can remain in binary form.
>
>'problems' with EOF-ish approaches include having to distribute your
>framework ( think library ) along with your app, which youd have to do
>anyway seeing that your objects are in that framework/package. stored
>procedures wouldnt have to be shared outside of the dbms ( obviously ).
>personally, i find it a LOT easier to deal with EOF objects rather than a
>potentially large PL/SQL ( or PL/pgSQL ) procedure.
>
>what'd be interesting is to compare the use of stored procedures to EOF or
>EOF-ish alternatives, using the same data & schema, ofcourse. NeXT/Apple
>has a sample db, sample data, and examples of how one can use EOF's
>features to augment/replace stored procedures in the dbms.
>
>(java)
>public void validateForDelete() throws EOValidation.Exception {
>if( !isPaid() )
>{
> throw new EOValidation.Exception("You can't remove an unpaid fee");
>}
>
>super.validateForDelete();
>}
>
>(objective-c)
>- (NSException *)validateForDelete
>{
>if( ![self isPaid] )
> return [NSException validationExceptionWithFormat:@"You can't remove
>an unpaid fee"];
>return [super validateForDelete];
>}
>
>and yes, i do realize that not everyone has the option of using
>EOF/Javabeans... nobody'
Re: [GENERAL] stored procedure revisited
Peter Mount wrote: > > Well, for me it would allow the current kludge that the JDBC driver uses > for PreparedStatement. Having SP would allow that class to temporarily > store the procedure, then only the data would need to be transfered to the > backend. This would improve the majority of JDBC useage enormously. > Definitely. For JDBC/ODBC camp, SP is a strong feature to have. Although for the PrepareStatement it would mean the system needs to allow "temporary" stored procedure in the database, and is PG's security mechanism set up for that? Regards, yin-so chen
Re: [GENERAL] How do I activate and change the postgres user'spassword?
On Oct 13, Lincoln Yeoh mentioned: > Then I have problems logging in as ANY user. Couldn't figure out what the > default password for the postgres user was. Only after some messing around > I found that I could log on as the postgres user with the password \N. Not > obvious, at least to me. There is a todo item for the postgres user to have a password by default. I'm not sure though how that would be done. Probably in initdb. (?) > I only guessed it after looking at the pg_pwd file and noticing a \N there. > Is this where the passwords are stored? By the way should they be stored in > the clear and in a 666 permissions file? How about hashing them with some > salt? I had this on my personal things-to-consider-working-on list but I don't see an official todo item. I am personally not sure why this is not done but authentication and security are not most people's specialty around here. (including me) > 1) There is no obvious way to specify the password for users when you > create a user using the supplied shell script createuser. One has to resort > to psql and stuff. Aah. Another misguided user. Some people are of the opinion that using the createuser scripts is a bad idea because it gives you the wrong impression of how things work. (All createuser does is call psql.) Of course, we could somehow put a password prompt in there, I'll put that on the above mentioned list. > 2) Neither is there an obvious and easy way to change the user's password. alter user joe with password "foo"; I'm not sure how obvious it is but it's certainly easy. > 3) You can specify a password for a user by using pg_passwd and stick it > into a separate password file, but then there really is no link between > createuser and pg_passwd. This shows how bad the idea of the scripts was in the first place. > I find the bundled scripts and their associated documentation make things > very nonintuitive when one switches from a blind trust postgres to an > authenticated postgres. So that would put your vote in the "drop altogether" column? Voting is still in progress! -Peter -- Peter Eisentraut Sernanders vaeg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] stored procedure revisited
Howie wrote: > > actually, one would hope that the system has its db independence in the > application layer rather than the database layer. for instance, using > something like NeXT's Enterprise Objects Framework to fetch rows from the > db and translate the rows into objects, you only deal with the objects. > The whole datastore, at this point, becomes irrelevant since you rarely > deal with the underlying SQL -- EOF takes care of all that for you. > Instead, you say "hey, i want all the objects that have their personName > ivar equal to Amy" ( "personName = 'Amy'" ). I'm fairly positive that > Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc ) does the same > sort of thing. > Well, you are approaching from an application developer's point of view. You want to have database independence so you can move back and forth between all the flavors of databases out there. ODBC was meant to address this issue, and we all know there are still limitations. However, even then database independence doesn't make the discussion of stored procedure irrelevant. Given a better tool to do something, wouldn't you use it? If you have to deal with databases ranging from Access to Oracle, are you going to make your application based on Access capability, since it doesn't have a PL? More than likely you are going to design one version for Access & another for Oracle... >From business's POV, application layer is _not_that_ important because production databases (especially OLTP databases) are seldomly moved. Even today the commercial vendors finding themselves supporting the legacy versions. In this sense the capability of the database itself becomes that much more important. For the database administrators, the ability underneath the application layer is very important indeed. Certainly SP offers a lot of horsepower :) Regards, yin-so chen
Re: [GENERAL] questing using array
You might want to look into the contrib/array directory which has _some_
helpers with arrays.
But in general, using arrays in cases like yours is a bad idea because,
a) It has nothing to do with relational database design
b) Arrays were not designed for this kind of stuff, so you won't get very
far.
Regarding a), redesign your table like this
name varchar(20)
ageid int4
and make the records like this:
test 1
test 2
test 3
test 4
and then you can use a simple select to find your answer. (Yes, this looks
like you're storing more data, but that is how things are supposed to
work.)
Regarding b), arrays were created mostly for geometric objects. Of course
you still could ask questions like "give me a polygon that has some
coordinate that has a 20 in it", but it's unlikely and specialty functions
exist to deal with questions you would usually have.
-Peter
On Oct 12, Kevin Heflin mentioned:
>
> Just trying to get a handle on how to work with an array as a datatype.
>
> For exampel I set up a table:
>
> name varchar (20),
> ageids int4[]
>
>
> Made an INSERT like:
>
> insert into TABLENAME (name, ageids) values ('test', '{1, 2, 3, 4}');
>
>
> What I haven't been able to figure out is how to do a select where one of
> the ageids = a particular number.
>
>
> I'd like to do something like
> select * from tablename where ( any ageids = 3 )
>
> just don't know the syntax.. if this is even possible. Any suggestions
> would be appreciated.
--
Peter Eisentraut Sernanders vaeg 10:115
[EMAIL PROTECTED] 75262 Uppsala
http://yi.org/peter-e/Sweden
[GENERAL] mail-list administration
would it be possible to add a Reply-to header to all outgoing messages ?
Re: [GENERAL] Connect PostgreSQL 6.0 Server with php4b
Moin,
thank you for your reply but the problem remain. The fallowing line is
in my pg_hba.conf:
host moon192.168.153.0 255.255.255.0 trust
The hole network with the given number has access to the database moon.
The Webserver has the IP 192.168.153.9. But the no connection is
possible. I've tried an explicit line in pg_hba.conf for the webserver
but without any success.
Any other hints? :-)
Thanks
Matthias
On Sun, Oct 10, 1999 at 04:51:24PM -0300, Charles Tassell wrote:
> Your problem is probably in the /usr/local/pgsql/data/pg_hba.conf file.
> That file lists what machines are allowed to connect to your Postgres
> server and what sort of authentication they have to provide. If the web
> server and the Postgres server are on the same machine, you should have
> these two lines in that file:
> localalltrust
> host all 127.0.0.1255.255.255.255 trust
>
> If they are on seperate machines, you will want to set up something like:
> host all web.server.ip 255.255.255.255 crypt
>
> and set up accounts/passwords for your PHP scripts, then use this sort of
> thing to connect to the DB:
> $dbCon = pg_PConnect("host=postgres.server.address user=username
> password=password dbname=database.to.connect.to");
>
>
> At 06:45 AM 10/9/99, Matthias Teege wrote:
> >Moin,
> >
> >i have an PostgreSQL 6.0 Server wich I would query with php4b. I have
> >problems to make the connection because off php gives me the following
> >error message:
> >
> >Warning: Unable to connect to PostgresSQL server: Failed to
> >authenticate client as Postgres user 'nobody' using authentication
> >scheme 131072. in /usr/local/share/apache/htdocs/matthias/hellodb.php
> >on line 2
> >An error occured.
> >
> >Were is the Problem?
> >
> >Many thanks
> >Matthias
> >
> >
> >
>
>
--
Matthias Teege -- [EMAIL PROTECTED] -- http://emugs.de
make world not war
PGP-Key auf Anfrage
Re: [GENERAL] stored procedure revisited
On Tue, 12 Oct 1999, Yin-So Chen wrote: [snip] > > I don't know how SP is implemented since none of the commercial RDBMS > publishes their sources, but they've all claimed that SP saves parsing > time and saves query plan time (it's generated once and stored). Need > some database experts to verify this point :) And like you said, it's > one of the most powerful tools available for database implementation. I > want the ability simply because of its conceptual abstraction, even if > w/out any of the performance benefit. > > Come on, everybody, speak out your thought on this matter :) Well, for me it would allow the current kludge that the JDBC driver uses for PreparedStatement. Having SP would allow that class to temporarily store the procedure, then only the data would need to be transfered to the backend. This would improve the majority of JDBC useage enormously. Peter -- Peter T Mount [EMAIL PROTECTED] Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres Java PDF Generator: http://www.retep.org.uk/pdf
Re: [GENERAL] stored procedure revisited
On Wed, 13 Oct 1999, amy cheng wrote:
>
> >fact that it doesn't do something that most, if not all, commercially
> >available db systems do can work against us,
> i.e., portability and upgradability: imagine you want to change that
> M$ system into Pg, or, I hate to say this, but somehow if your
> success is so big that you can not live with Pg, you need go to O ect.
> then, true SP will make things really easy (just systax change, you may even
> just use our open source facility -- I'm sure there will be, since PL/pgSQL
> are so close to other PL). In my own case, when I begin to use PL/pgSQL, I
> put some thinking on the second aspect, I bet
> others also did that. A true SP will make it more inviting.
actually, one would hope that the system has its db independence in the
application layer rather than the database layer. for instance, using
something like NeXT's Enterprise Objects Framework to fetch rows from the
db and translate the rows into objects, you only deal with the objects.
The whole datastore, at this point, becomes irrelevant since you rarely
deal with the underlying SQL -- EOF takes care of all that for you.
Instead, you say "hey, i want all the objects that have their personName
ivar equal to Amy" ( "personName = 'Amy'" ). I'm fairly positive that
Sun's Java equivalent of EOF ( 'Entity Javabeans', iirc ) does the same
sort of thing.
keeping inserts/selects/etc in stored procedures would still require a
rewrite of all the stored procedures when moving to another db vendor,
which may or may not be a large problem depending on that vendor's
imeplementation of stored procedures and SQL in general. granted, you
wouldnt have to completely gut the application and rewrite the whole
bloody thing, but since your app is already going to have some of it
rewritten ( cant use an OCI call on postgresql ), i think it'd made more
sense to abstract things further by putting all the logic into your
objects, EOF or Entity Javabeans, rather than in the db.
so now lets talk code reuse. both options would give you about the same
level of code reuse, but in two completely different ways. stored
procedures ( and company/DBA policy ) pretty much force the user to take
advantage of them rather than doing raw inserts, selects, etc on the
underlying tables. EOF forces you to deal with the objects rather than
sql. either way, all of your business logic is in one location. by
using a higher-level language, however, you wouldnt have to deal with
tedious pl/sql-ish programming. one could also argue that having 20+
different stored procedures is really no better than memorizing the
business logic and duplicating that in the application, bypassing the
procedures altogether. if you have to deal with developing on one dbms
and deploying on another dbms, EOF starts to look even more beautiful --
since your logic is in the objects, not the db, nothing will have to be
ported to the new dbms. in fact, all you really need to do is change the
EOModel; all of your code can remain in binary form.
'problems' with EOF-ish approaches include having to distribute your
framework ( think library ) along with your app, which youd have to do
anyway seeing that your objects are in that framework/package. stored
procedures wouldnt have to be shared outside of the dbms ( obviously ).
personally, i find it a LOT easier to deal with EOF objects rather than a
potentially large PL/SQL ( or PL/pgSQL ) procedure.
what'd be interesting is to compare the use of stored procedures to EOF or
EOF-ish alternatives, using the same data & schema, ofcourse. NeXT/Apple
has a sample db, sample data, and examples of how one can use EOF's
features to augment/replace stored procedures in the dbms.
(java)
public void validateForDelete() throws EOValidation.Exception {
if( !isPaid() )
{
throw new EOValidation.Exception("You can't remove an unpaid fee");
}
super.validateForDelete();
}
(objective-c)
- (NSException *)validateForDelete
{
if( ![self isPaid] )
return [NSException validationExceptionWithFormat:@"You can't remove an unpaid
fee"];
return [super validateForDelete];
}
and yes, i do realize that not everyone has the option of using
EOF/Javabeans... nobody's perfect :)
> [SNIP]
> However, I would like to see data warehouse (or more moderately and
> accurately data mart) support also -- the point: the priority?
so either (A) work on implementing tablespaces or (B) donate some money to
postgresql, inc.
---
Howie <[EMAIL PROTECTED]> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."
