Обсуждение: Is SQL silly as an RDBMS<->app interface?

Hi, this is a general RDBMS question, not specific to pg. It occurred to
me while I was trying to design an interface between application and
SQL.

Suppose that the user fills in a complex query form, and you are coding
the application that converts the user's input to a where clause. It may
prove beneficial if you construct a treelike structure like this:

        AND
         |
         +-OR
         |  |
         |  + Condition A
         |  |
         |  + Condition B
         |
         +-OR
            |
            + Condition C
            |
            + AND
               |
               + Condition D
               |
               + Condition E
               |
               + Condition F

This would become

   WHERE (A OR B) AND (C OR (D AND E AND F))

It seems complex at first, but the code will be cleaner, scale better,
and be made portable easier if you are adding nodes and leaves to a tree
as you are scanning the user's input, than if you try to construct a
where clause directly.  After finishing with the tree, it is
straightforward to convert it to a where clause, after which you send
the SQL to the RDBMS.

What will the RDBMS do next? It will parse your SQL statement and
presumably convert it to a tree of conditions. Well, I had that ready in
the first place!

Whether my idea about the tree is good or not, it is true that the
application initially has its data in some data structures suitable for
computers rather than humans; it converts them to SQL, which is suitable
for humans, only so that the SQL will be converted back to structures
suitable for computers. The most obvious example is that integers are
converted to decimal by the application only to be converted back to
binary by the RDBMS.

I understand that SQL is the interface between apps and RDBMS's because
of history, not because it is correct design.  Could you point me to a
link or book or paper that deals with this paradox? Thanks!

On Sun, Jul 13, 2003 at 01:24:12PM +0300, Antonios Christofides wrote:
> <snip>
>
> What will the RDBMS do next? It will parse your SQL statement and
> presumably convert it to a tree of conditions. Well, I had that ready in
> the first place!
>
> <snip>
>
> I understand that SQL is the interface between apps and RDBMS's because
> of history, not because it is correct design.  Could you point me to a
> link or book or paper that deals with this paradox? Thanks!

There is no paradox. SQL is a standard method of describing queries that
most databases understand. It is readable by humans. The structures used
within a database are specific to that database and not usable by humans.
The structures used in your code are different from those a database would
use.

SQL is the conduit that allows one program or person to describe a query to
a server without getting bogged down in meaningless detail. In a way it is
like the 64Kb connections in the phone system; each endpoint can be a
person, answering machine, mobile phone service, etc but they can all talk
to eachother because they can convert to a common standard.

So, SQL may not be the best way of doing it but it is widly used and
well-understood. It transports the meaning in a way independant of the
programs using it.

Hope this helps.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

Antonios Christofides wrote:
> [...]
> suitable for computers. The most obvious example is that integers are
> converted to decimal by the application only to be converted back to
> binary by the RDBMS.

Very good example, indeed. How does the 64bit big-endian DB server use
your frontend's little-endian 32bit integer value? Not to speak of the
binary representation of your expression tree.

You have to do some conversion. By convention this is done by converting
into and from a communication protocol that is possibly different from
the internal representation on at least one side.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On Sun, 2003-07-13 at 05:24, Antonios Christofides wrote:
> Hi, this is a general RDBMS question, not specific to pg. It occurred to
> me while I was trying to design an interface between application and
> SQL.
[snip]
>
> It seems complex at first, but the code will be cleaner, scale better,
> and be made portable easier if you are adding nodes and leaves to a tree
> as you are scanning the user's input, than if you try to construct a
> where clause directly.  After finishing with the tree, it is
> straightforward to convert it to a where clause, after which you send
> the SQL to the RDBMS.
>
> What will the RDBMS do next? It will parse your SQL statement and
> presumably convert it to a tree of conditions. Well, I had that ready in
> the first place!
>
> Whether my idea about the tree is good or not, it is true that the
> application initially has its data in some data structures suitable for
> computers rather than humans; it converts them to SQL, which is suitable
> for humans, only so that the SQL will be converted back to structures
> suitable for computers. The most obvious example is that integers are
> converted to decimal by the application only to be converted back to
> binary by the RDBMS.

When Oracle bought Rdb from Digital, it got DSRI, the Digital
Standard Relatonal Interface.  A direct API into Rdb.  No one
uses it, though.  Why?  It's too complicated.

> I understand that SQL is the interface between apps and RDBMS's because
> of history, not because it is correct design.  Could you point me to a
> link or book or paper that deals with this paradox? Thanks!

Who says SQL's design is incorrect?  SQL became the de facto
standard because it was better than the competing relational
interface languages, not because of Borg-like tactics.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+

> I understand that SQL is the interface between apps and RDBMS's because
> of history, not because it is correct design.  Could you point me to a
> link or book or paper that deals with this paradox? Thanks!

I'm not sure what you mean by 'correct design'.

I think you should go back and read the works of Codd and Date on the
development of relational databases.  One point that was made early on is
that RDBMS theory doesn't guarantee efficiency, but it does guarantee
consistency and accuracy in the results.  (Efficiency is an implementation
issue, though without high-speed computers relational databases would
probably not be practical anyway.)

The same can be said of the SQL standard and any 'natural language' query.
There is always a way to structure a query properly, even though it may be
ugly-looking.  It isn't about how the query looks, it is about being able
to ask the question--ANY question.

Back in the late 60's, when I was a budding CS/EE major, the big hardware
'concept' was associative memory, which was supposed to be more human-like
in terms of how it functioned, though grossly inefficient in terms of its
use of hardware cycles.
--
Mike Nolan

At 01:24 PM 7/13/2003 +0300, Antonios Christofides wrote:

>Whether my idea about the tree is good or not, it is true that the
>application initially has its data in some data structures suitable for
>computers rather than humans; it converts them to SQL, which is suitable
>for humans, only so that the SQL will be converted back to structures
>suitable for computers. The most obvious example is that integers are
>converted to decimal by the application only to be converted back to
>binary by the RDBMS.
>
>I understand that SQL is the interface between apps and RDBMS's because
>of history, not because it is correct design.  Could you point me to a
>link or book or paper that deals with this paradox? Thanks!

Often one man's impedance mismatch is another man's layer of abstraction.

An integer in the app is often not the same thing as an integer in the
database. Have fun when it comes to dates and times.

As SQL is a somewhat human readable/writable API, people can leverage[1]
tons of different tools to use or abuse it. And that's what has been done.

I daresay if someone came up with an ultra-elegant mathematical efficient
DB interface, only a few geniuses would actually read the thesis,
understand it and use it.

Also in most cases I've seen on this list, the bottlenecks are elsewhere
(e.g. I/O, wrong plan, query, db schema) so this sort of thing might be an
unnecessary optimization.

HTH,
Link.

[1] OK so I used the L word.

On Sun, 2003-07-13 at 10:17, nolan@celery.tssi.com wrote:
> > I understand that SQL is the interface between apps and RDBMS's because
> > of history, not because it is correct design.  Could you point me to a
> > link or book or paper that deals with this paradox? Thanks!
>
> I'm not sure what you mean by 'correct design'.
>
> I think you should go back and read the works of Codd and Date on the
> development of relational databases.  One point that was made early on is
> that RDBMS theory doesn't guarantee efficiency, but it does guarantee

SQL is only one possible relational query language.  It didn't
become de facto standard until the mid- to late-80s.

It is an outgrowth of SEQEL (Structured English QuEry Language),
which was IBM's 1st try at a descriptive query language.  DEC
had RDML (Relational Data Manipulation Language) to access it's
RDBMS.  I'm sure that Burroughs, etc, had their own access methods,
too.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+

On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:

> SQL is only one possible relational query language.  It didn't
> become de facto standard until the mid- to late-80s.
>
> It is an outgrowth of SEQEL (Structured English QuEry Language),
> which was IBM's 1st try at a descriptive query language.  DEC
> had RDML (Relational Data Manipulation Language) to access it's
> RDBMS.  I'm sure that Burroughs, etc, had their own access methods,
> too.

Of course, in the context of a PostgreSQL list you can't forget QUEL and
PostQUEL, Ingres and POSTGRES query languages respectively.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)

On Mon, 14 Jul 2003, Alvaro Herrera wrote:

> On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
>
> > SQL is only one possible relational query language.  It didn't
> > become de facto standard until the mid- to late-80s.
> >
> > It is an outgrowth of SEQEL (Structured English QuEry Language),
> > which was IBM's 1st try at a descriptive query language.  DEC
> > had RDML (Relational Data Manipulation Language) to access it's
> > RDBMS.  I'm sure that Burroughs, etc, had their own access methods,
> > too.
>
> Of course, in the context of a PostgreSQL list you can't forget QUEL and
> PostQUEL, Ingres and POSTGRES query languages respectively.
>
>

    SQL is almost the worst standard I've come across. Its the
computer equivalent of VHS. Its not readable by computer or humans. (Enough
Flaming on to why I think this)

    SQL is verbose it often make you repeat your self when its obvious
what you mean.

INSERT INTO a (b,c) SELECT a+4 as b, c*6 as c from a;

    SQL has many different ways of writing the same thing for
different purposes. eg
INSERT INTO a (b,c) VALUES (1,2);
UPDATE a set b=1, c=2 WHERE d=3;

Why not

INSERT INTO a set b=1, c=3;

its certainly more readable and consistent.

Parsing is hard work

No Meta Standard (How do you find out the structure of your table using
pure SQL?

Very difficult to operate with Trees and simple hierarchal data.

I could continue. Still its a language we all love to hate.

Peter Childs

Peter Childs <blue.dragon@blueyonder.co.uk> writes:
>     SQL is almost the worst standard I've come across.

It's certainly got its bad points, but if it's so bad how did it get to
be the de facto standard?  There were plenty of alternatives awhile back.

> No Meta Standard (How do you find out the structure of your table using
> pure SQL?

See INFORMATION_SCHEMA.

            regards, tom lane

On Mon, 2003-07-14 at 04:36, Peter Childs wrote:
> On Mon, 14 Jul 2003, Alvaro Herrera wrote:
>
> > On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
[snip]
>     SQL is almost the worst standard I've come across. Its the
> computer equivalent of VHS. Its not readable by computer or humans. (Enough
> Flaming on to why I think this)
>
>     SQL is verbose it often make you repeat your self when its obvious
> what you mean.
>
> INSERT INTO a (b,c) SELECT a+4 as b, c*6 as c from a;

Without a WHERE clause, would that "just" double the number of
tuples, or recurse forever?

>     SQL has many different ways of writing the same thing for
> different purposes. eg
> INSERT INTO a (b,c) VALUES (1,2);
> UPDATE a set b=1, c=2 WHERE d=3;
>
> Why not
>
> INSERT INTO a set b=1, c=3;
>
> its certainly more readable and consistent.

That's debatable.

If fields b and c are the only fields in the table, you can say:
  INSERT INTO A VALUES (1, 2);
When there's a dozen fields in table A, your method seems that
it would get pretty unwieldy.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+

Hi all:

I am new to PostgreSQL DB, however I have years experience with Oracle 8i
and MS SQL. I am in the process to promot PostgreSQL to my future client,
due to the cost. I am just wondering if overall people feels frustrated with
PostgreSQL or feels happey with it.

I know MySQL is simpiler and cheap. With my years experience with enterprise
level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
stored procedure. Sorry to MySQL lovers.

I have the following questions. Please reply me offline, so the mailling
list won't get flood. Thanks!
1. What is your favorite GUI tool for PostgreSQL?
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
3. What is the biggest challenge you have with PostgreSQL? Administration or
Programming?
4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?

Any suggestion and help are welcome. Thanks!

On 14 Jul 2003 at 15:16, Terence Chang wrote:

> Hi all:
>
> I am new to PostgreSQL DB, however I have years experience with Oracle
> 8i and MS SQL. I am in the process to promot PostgreSQL to my future
> client, due to the cost. I am just wondering if overall people feels
> frustrated with PostgreSQL or feels happey with it.

I've been working with databases for about 15 years, including
Sybase, Oracle, PostgreSQL, and MySQL.  Yes, I'm happy with
PostgreSQL.  No frustration at all.

> I know MySQL is simpiler and cheap. With my years experience with
> enterprise level DB like Oracle and MS SQL, I just don't feel right
> with MySQL. I love stored procedure. Sorry to MySQL lovers.

Stored procedures is one reason I moved http://www.FreshPorts.org/
from MySQL to PostgreSQL.

> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!

If we reply to the list, everyone who asks the same questions as you
will be able to find them in the archives.

> 1. What is your favorite GUI tool for PostgreSQL?

I usually use command line tools for PG work, but do use PGAdmin from
time to time.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

Yes.

> 3. What is the biggest challenge you have with PostgreSQL?
> Administration or Programming?'

Finding more time to spend working with it.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Yes.  Yes.  Friends don't let friends use MySQL.

> 5. How often do your PostgreSQL run into problem or crash? Are most of
> the problem caused by PostgreSQL itself?

I have never seen a PostgreSQL crash.  Most if not all of the
problems have been caused by myself.
--
Dan Langille : http://www.langille.org/

--- Terence Chang <TChang@nqueue.com> wrote:
> I have the following questions. Please reply me
> offline, so the mailling
> list won't get flood. Thanks!

But if they don't read it, they can't correct me!
;-)

> 1. What is your favorite GUI tool for PostgreSQL?
I create apps using MS Access as the GUI front-end.
For administration, I use scripts and the command
line.

> 2. In your organization, do you have someone who
> works as full time
> PostgreSQL DBA?
No. We wear a lot of hats around here.

> 3. What is the biggest challenge you have with
> PostgreSQL? Administration or
> Programming?
Programming -- if I were a Programmer or a DBA, I
could do more.  A solid database, a solid operating
system and a little scripting make day-to-day
administration easy enough.

> 4. Overall, do you like PostgreSQL? Would you
> recommend it over MySQL?
Yes, and yes.  MySQL has a reputation for ease of
administration; however, I moved from MySQL to
PostgreSQL because missing features in MySQL meant
difficult work-arounds for users.  Also, the \help
system in psql is very useful for people learning SQL.
  As an end-user, I found PostgreSQL easier to use
than MySQL.

> 5. How often do your PostgreSQL run into problem or
> crash? Are most of the
> problem caused by PostgreSQL itself?
At work, I haven't had any unplanned down time since I
started using PostgreSQL over 3 years ago.  At home, I
had a hard drive crash; but the databases were
restored from backup files without any problems.
Dependable backups should _not_ be taken for granted!

Best regards,

Andrew Gould

On Mon, 2003-07-14 at 17:44, Dan Langille wrote:
> On 14 Jul 2003 at 15:16, Terence Chang wrote:
>
> > Hi all:
[snip]
>
> Stored procedures is one reason I moved http://www.FreshPorts.org/
> from MySQL to PostgreSQL.

Oh, the shame!  Advertising AOL For Broadband on a FreeBSD ports
site?

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| 4 degrees from Vladimir Putin
+-----------------------------------------------------------+

I have been working with various databases since the 80's.

Terence Chang wrote:
> Hi all:
>
> I am new to PostgreSQL DB, however I have years experience with Oracle 8i
> and MS SQL. I am in the process to promot PostgreSQL to my future client,
> due to the cost. I am just wondering if overall people feels frustrated with
> PostgreSQL or feels happey with it.
>
> I know MySQL is simpiler and cheap. With my years experience with enterprise
> level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
> stored procedure. Sorry to MySQL lovers.
>
> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!
> 1. What is your favorite GUI tool for PostgreSQL?
I use PGAdmin for a quick conversion of Indexes and Tables from MS-SQL.
   /access  But for other items I either use the command line or EMS
PostgreSQL Manager - This is the favorite tool of the developers I have
working on various projects.

We develope web applications and applicationsin Delphi, C++
> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?
No.

> 3. What is the biggest challenge you have with PostgreSQL? Administration or
> Programming?
My biggest challenge is getting funding to convert other projects
completed using MS-SQL to PostgreSQL, but they are coming around.

PostgreSQL has been very easy to program and administer.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Coming from a background where data integrity is a high priority as well
as performance - it's PostgrSQL hands down.

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?
It hasn't for over five years.


>
> Any suggestion and help are welcome. Thanks!
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>

Tim.

On 14 Jul 2003 at 20:55, Ron Johnson wrote:

> On Mon, 2003-07-14 at 17:44, Dan Langille wrote:
> > On 14 Jul 2003 at 15:16, Terence Chang wrote:
> >
> > > Hi all:
> [snip]
> >
> > Stored procedures is one reason I moved http://www.FreshPorts.org/
> > from MySQL to PostgreSQL.
>
> Oh, the shame!  Advertising AOL For Broadband on a FreeBSD ports
> site?

Yep.  I'll take money from almost anyone.
--
Dan Langille : http://www.langille.org/

> 1. What is your favorite GUI tool for PostgreSQL?

Just psql in an emacs window.
Emacs lets me see large result sets, and keep a history of my commands.


> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

Our Oracle DBA is also the DBA for our production PostgreSQL databases.
Developers administer their own databases (some Oracle, some PostgreSQL).


> 3. What is the biggest challenge you have with PostgreSQL?
> Administration or Programming?

  Challenge programming:
   Familiarity in the group with Oracle makes some tasks quicker in Oracle.

  Challenge administrating:
   Remembering when to analyze (especially remembering to stick analyze
   in the middle of big nightly scripts that make large temporary tables).


> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Personally I like it a lot.  Definately over MySQL, and even over Oracle
for anything containing data that don't have complicated replication needs.

My DBA, however, prefers Oracle over PostgreSQL.  But he doesn't have the
budget for Oracle for all our systems.  :-)  I think he prefers PostgreSQL
over MySQL, though.


> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

Never had a crash with PostgreSQL itself.  One problems with a table
where "analyze"'s sampling would generate bad stastics (correlation) for
some tables and make the planner pick slow plans. This was worked around
by reordering data in the table.


> Any suggestion and help are welcome. Thanks!

One suggestion... if you get a lot of off-the-mailing-list responses,
could you post a summary?

   Ron

On Monday 14 Jul 2003 11:16 pm, Terence Chang wrote:
> Hi all:
>
> I am new to PostgreSQL DB, however I have years experience with Oracle 8i
> and MS SQL. I am in the process to promot PostgreSQL to my future client,
> due to the cost. I am just wondering if overall people feels frustrated
> with PostgreSQL or feels happey with it.

Pretty happy - easier to admin remotely the MS-SQL.

> I know MySQL is simpiler and cheap. With my years experience with
> enterprise level DB like Oracle and MS SQL, I just don't feel right with
> MySQL. I love stored procedure. Sorry to MySQL lovers.
>
> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!
> 1. What is your favorite GUI tool for PostgreSQL?

Mostly use psql, otherwise phpPgAdmin or PgAdmin if I'm on a windows box -
I've recently recommended PgAdmin to a client I'm working with and after a
couple of hours use he seems to like it.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

Small devt/support shop, so it's me, and I'm not full time for any particular
client.

> 3. What is the biggest challenge you have with PostgreSQL? Administration
> or Programming?

Hmm - error messages in plpgsql - not always as informative as they might be.
So that would be Programming.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Yes, very pleased, and yes, I'd recommend it over MySQL. I use both, but I've
come to the conclusion that for the sort of custom systems I tend to build /
support it makes sense to use a more sophisticated package and perhaps spend
a few hundred pounds on hardware if it really matters.

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

Never seen a crash on my systems (well, not one that was caused by PG). I'd
not say I've never read about a crash on the mailing lists in the few years
I've been here, but most of them seemed to be hardware related in the end.

--
  Richard Huxton

On 14 Jul 2003 at 15:16, Terence Chang wrote:

> due to the cost. I am just wondering if overall people feels frustrated with
> PostgreSQL or feels happey with it.

Happy; and you can't beat the cost!

> 1. What is your favorite GUI tool for PostgreSQL?

PgAdmin, though I use psql directly on the Postgres machine a lot
too.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

No - just me, and I work part-time both as developer and DBA.

> 3. What is the biggest challenge you have with PostgreSQL? Administration or
> Programming?

Programming; our applications are fairly low-traffic, so the
Postrgres box just sits there and runs itself most of the time.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

I've no experience of MySQL, but I like Postgres a lot - I learnt all
my DBMS stuff on Postgres, and found it a good learning tool as well
as a solid DB backend.

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

It has never crashed, in over four years - all problems I ran into
were caused by myself.

--Ray.

-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------

On 14 Jul 2003 at 15:16, Terence Chang wrote:

> > 1. What is your favorite GUI tool for PostgreSQL?

pgaccess - Mac OS X and Linux (will also run on any Tcl capable OS)

> > 2. In your organization, do you have someone who works as full time
> > PostgreSQL DBA?

No I look after several database servers online and intranet

> > 3. What is the biggest challenge you have with PostgreSQL? Administration or
> > Programming?

Programming - I am self taught so pl/pgsql is a bit of a challenge for
me

> > 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Yes. Yes.

> > 5. How often do your PostgreSQL run into problem or crash? Are most of the
> > problem caused by PostgreSQL itself?

I had a hardware failure and thanks to the list was able to get all my
data back from a 6.x generation database.

Tony Grant
--
www.tgds.net Library management software toolkit,
redhat linux on Sony Vaio C1XD,
Dreamweaver MX with Tomcat and PostgreSQL

On 14/07/2003 23:16 Terence Chang wrote:
> [snip]
> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!
> 1. What is your favorite GUI tool for PostgreSQL?

Most of th time, I find psql to be perfectly adequate for the job. If I
need to look at long and/or wide result sets I'll use pgaccess.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

Me organised? Being an independent software developer, I tend to wear all
the hats.

> 3. What is the biggest challenge you have with PostgreSQL? Administration
> or
> Programming?

All fairly straight forward IME.


> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

100% of the time.
> 5. How often do your PostgreSQL run into problem or crash? Are most of
> the
> problem caused by PostgreSQL itself?

I've never had a problem


--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

On Tue, 15 Jul 2003 13:23:30 +0100 Paul Thomas <paul@tmsl.demon.co.uk> wrote:

>
> On 14/07/2003 23:16 Terence Chang wrote:
> > [snip]
> > I have the following questions. Please reply me offline, so the
> mailling
> > list won't get flood. Thanks!
> > 1. What is your favorite GUI tool for PostgreSQL?

> Most of th time, I find psql to be perfectly adequate for the job. If I
> need to look at long and/or wide result sets I'll use pgaccess.

psql mostly. i've played with dbvisualizer, found it useful, then found
that for one particular combination of versions (postgresql & dbvis) it
wouldn't show me all tables. i have used Access/ODBC occasionally, but i
try no to use M$ products where i can avoid it. pgmanager looks promising,
but i've only been playing with it for a day or two.

> > 2. In your organization, do you have someone who works as full time
> > PostgreSQL DBA?

> Me organised? Being an independent software developer, I tend to wear
> all
> the hats.

ditto.

> > 3. What is the biggest challenge you have with PostgreSQL?
> Administration
> > or
> > Programming?

> All fairly straight forward IME.

i'd have to say learning the performance stuff. i have just started
reading the performance list and have found it most instructive.
i've learned a lot there in a few short days that isn't in any of the
PostgreSQL books that i have. i would think that a really hardcore
PostgreSQL DBA book along the lines of Loney's Oracle DBA handbooks would
be most useful.

> > 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

> 100% of the time.

agreed. MySQL is satisfactory for a narrow set of uses, but has some
significant limitations. and what with the LGPL-GPL licensing change for
MySQL 4.1.x, i am tending to recommend that my clients running MySQL
consider their transistion strategies to some other product.

> > 5. How often do your PostgreSQL run into problem or crash? Are most of
> > the
> > problem caused by PostgreSQL itself?

> I've never had a problem

none here, either. it just works.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

On Tue, 15 Jul 2003 08:42:27 -0400 (EDT) Richard Welty <rwelty@averillpark.net> wrote:

> i've played with dbvisualizer, found it useful, then found
> that for one particular combination of versions (postgresql & dbvis) it
> wouldn't show me all tables.

ok, that didn't really come out right. there was a version of dbvisualizer
which couldn't see all the tables in a particular version of postgresql. i
have since upgraded postgresql to 7.3.3 everywhere but haven't tried
dbvisualizer since (i'm developing on a different system now and haven't
installed dbvisisualizer on it, as psql has been more than adequate.)

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
    Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security

> 1. What is your favorite GUI tool for PostgreSQL?

Tool to do what?  As a DBA I find GUI front ends tend to be either
too restrictive or try to do too much and thus become too complicated.
(Oracle's Enterprise Manager Console is a classic example of feature
creep, or is that feature gallop?)

OTOH, I'm in the process of writing a GPL table-driven web-based table
browser/data entry program in PHP because I can't find one that does
what I want.  I hope to be able to put out a beta of it by September,
assuming my own personal feature gallop is coming down the home
stretch by then. :-)
--
Mike Nolan

> OTOH, I'm in the process of writing a GPL table-driven web-based table
> browser/data entry program in PHP because I can't find one that does
> what I want.  I hope to be able to put out a beta of it by September,
> assuming my own personal feature gallop is coming down the home
> stretch by then. :-)

In what ways is the application you are writing going to be different from
phpPgAdmin?

Chris

> In what ways is the application you are writing going to be different from
> phpPgAdmin?

I see it as more of a web-based application forms development engine
than as a DBA tool.
--
Mike Nolan

> 1. What is your favorite GUI tool for PostgreSQL?

I use psql command-line client from Linux, OSX, and Win32 (via Cygwin)
client systems. The only time I will use a graphical tool is for something
like ERWin to import the schema via ODBC and draw me a pretty picture to
make sure that the picture in my head matches the picture in the database.

> 2. In your organization, do you have someone who
>    works as full time PostgreSQL DBA?

I could only wish that were true - We use both MS SQL and PostgreSQL.
Various team members will monkey with the database based on need although we
have a single point man that all mods must go through. Everyone has a pretty
solid foundation on the basics of database design and implementation but the
technical/infrastructure administration of servers is a skill that we are
lacking.

> 3. What is the biggest challenge you have with
>    PostgreSQL? Administration or Programming?

Given the skill set of my team it doesn't matter what database system we use
but technical/infrastructure administration skill sets are needed.

I do admit that I am more comfortable using the MS SQL tools than I am with
the PostgreSQL tools but that is based more on a lack of experience in a
*nix shell than the quality of the toolset.

> 4. Overall, do you like PostgreSQL? Would you
>    recommend it over MySQL?

From a cost and performance basis I love PostgreSQL. We chose PostgreSQL
three years ago over MySQL because of transactions, views, sub-selects,
triggers and stored procedures. If I were to choose between OSS database
systems today I would have to choose between PostgreSQL and SAP DB (not an
option three years ago). I have not looked at MySQL seriously since I first
chose PostgreSQL although my understanding is that they have made some
strides in the areas that I mentioned above. However, now that they have
taken over the development of SAP DB I don't think that their existing code
base is going to mature any further.

> 5. How often do your PostgreSQL run into problem
>    or crash? Are most of the problem caused by
>    PostgreSQL itself?

The only time my database server has ever gone down was due to hardware
failure.

rjsjr

On Mon, 14 Jul 2003, Terence Chang wrote:

> Hi all:
>
> I am new to PostgreSQL DB, however I have years experience with Oracle 8i
> and MS SQL. I am in the process to promot PostgreSQL to my future client,
> due to the cost. I am just wondering if overall people feels frustrated with
> PostgreSQL or feels happey with it.

The most frustrating thing about postgresql is other people's uninformed
opinions about it.  I.e. a lot of folks here where I work haven't looked
at it since version 6.5 came out, and assume it hasn't changed much since
then.

> I know MySQL is simpiler and cheap. With my years experience with enterprise
> level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
> stored procedure. Sorry to MySQL lovers.

MySQL is also, unfortunately, written by people who don't "get it".  I
like Monty and his crew, really, but things like storing 0000-00-00 as a
valid date, or storing numerics as floats internally point out that MySQL
wasn't designed by database and accounting types, but more by web
developer / C hackers.

> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!

Nah, this list can handle it, and we all like to pitch in and add our two
cents to these kinds of conversations.

> 1. What is your favorite GUI tool for PostgreSQL?

A white board...  :-)

I don't use one.  We are, however, looking at the Postgresql Manager from
EMS.  For our Windows type folks this seems like the closest to the tools
available for MSSQL et. al.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

No, there isn't enough work to be a full time DBA.  I am the default DBA
(i.e. I was stupid enough to say "sure, I'll install it." :-)

Postgresql has a moderately steep learning curve for the first month or
two, then you get the hang of it and have your cron jobs set up to
vacuumdb it and all is good.

> 3. What is the biggest challenge you have with PostgreSQL? Administration or
> Programming?

Administration: scheduled backups, vacuumings, analyzings all need to be
monitored to ensure they are happening.  Upgrades can be a bit painful
sometimes for some folks, but I've never had an issue, since our
production databases are only a few gigabytes total.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

I love it.  It's one of the nicest open source projects going, written by
some of the best coders out there, and has one of the most civil tones on
its mailing lists.  Support is better than any commercial package I've
ever used.

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

I've been running Postgresql since version 6.5.2 or so.  I once caused the
backend to die back then with an unconstrained join on some huge tables.
Since 7.0 came out I have had zero unscheduled downtime.  That's about 4
years of continuous operation, always up.

My primary recommendation if you are looking at running postgresql is to
first test your hardware thouroughly (assume it's bad, prove it's good.)
then test your postgresql install thouroughly to make sure it and your OS
are properly tuned to handle the load you'll be throwing at it.

If you can start small (intranet development first) and move on to bigger
projects, that will give you time to learn the system while its load is
slowly increasing instead of putting something online your first week with
1,000 users.

Postgresql can handle that many users, but you've got to know what you're
doing both with it and the OS you're running it on, as well as how you
write you application.  If you're going to handle thousands of users, look
at connection pooling.

Hi,

First of all, let me say that I am not frustrated with PostgreSQL at all :-)

> 1. What is your favorite GUI tool for PostgreSQL?

None. I mostly use psql, but did some work with dbvisualizer.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

No. I'm freelance and self-taught. I do my best but I don't think I qualify
as a true DBA.

> 3. What is the biggest challenge you have with PostgreSQL? Administration or
> Programming?

Administration is quite straighforward, but stored procedure look rather
daunting and I haven't tried them yet. I'm sure many on the list will say
I'm wrong :-)

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

It's way better than MySQL. My only regret is that so few ISPs have it. I am
often obliged to use MySQL because my clients have an ISP that offers just
that. I have found that ISPs that offer PostgreSQL tend to be more
expensive.

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

I've been using PostgreSQL for about 2 years now and it never crashed once.

Cheers.

--------
Francois

Home page: http://www.monpetitcoin.com/
"We waste our time spending money we don't have to buy things we don't need
to impress people we don't like"

On Monday 14 July 2003 3:16 pm, Terence Chang wrote:

> I am new to PostgreSQL DB, however I have years experience with Oracle 8i
> and MS SQL. I am in the process to promot PostgreSQL to my future client,
> due to the cost. I am just wondering if overall people feels frustrated
> with PostgreSQL or feels happey with it.

General comments:

1) Asking people on this list if they like PostgreSQL is like going to
skid-row and asking the winos if they like Ripple and Night Train.

2) Learn PostgreSQL first, promote it to clients after you are familiar with
its capabliities and limitations and can relate those to the specific
project.

> 1. What is your favorite GUI tool for PostgreSQL?
I almost always just use psql. Our Windoze users seem to like pgadmin.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?
No.

> 3. What is the biggest challenge you have with PostgreSQL? Administration
> or Programming?

Programming.

With a bit of reading and research you will get past the administration
gotchas pretty quickly. I think the top two I see are relying on the default
.conf file (it's set to allow PG to start out-of-the-box on many platforms,
not to run fast on your specific platform) and failing to use vacuum and
vacuum analyze (cron is your friend).

Once you get PG tuned and your maintenance and backup automated then almost
all of the challenges are programming. Not that I'm complaining, mind you -
PG is not particularly difficult to work with. It's just that we do a lot of
development and the PG server just runs which frees us to concentrate on
programming.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Yes/Yes

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?
I don't recall any crashes. I routinely have connections up for months at a
time (not just the OS, not just the PG server but individual client
connections that persist for several months).

Cheers,
Steve

On Mon, 14 Jul 2003 15:16:36 -0700 "Terence Chang" <TChang@nqueue.com>
sat down, thought long and then wrote:

> I know MySQL is simpiler and cheap. With my years experience with
> enterprise level DB like Oracle and MS SQL, I just don't feel right
> with MySQL. I love stored procedure. Sorry to MySQL lovers.

I had to work with MySQL instead of PostgreSQL some years ago (and
unfrequently, still now), so I can immediately compare. I prefer
PostgreSQL. A lot.

>
> I have the following questions. Please reply me offline, so the
> mailling list won't get flood. Thanks!
> 1. What is your favorite GUI tool for PostgreSQL?

I usually use command line psql. It´s fast and cheap and does what
I want it to do. People using MS-Windows use PgAdmin, but older versions
seem to have a drawback in that they connect to all databases they find
on the server, they are attached to, which may cause a problem due to a
low set number of connections - limit. Newer versions of PgAdmin don´t
behave so greedy. You can even use PgAdmin to get data from MS-Excel
into PostgreSQL without big efforts. Some people use MS-Access or other
similiar query tools using ODBC.

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

What for? He would be hanging around all day doing nearly nothing
except changing backup tapes on monday. Install it, tune the
configuration, set up the backup job, let it run.

> 3. What is the biggest challenge you have with PostgreSQL?
> Administration or Programming?

Maybe optimization of the backend. Like other users write, PostgreSQL is
very conservative out of the box. Fiddling here and there, mostly
inside postgresql.conf, can greatly improve performance. For details you
might want to look at http://techdocs.postgresql.org. On the programming
side there is no real challenge, because the SQL-standard is very high
and comfortable (IMHO for example much better than with MSSQL). We
usually use PHP/PEAR or Pearl, sometimes C with Embedded SQL/ECPG. I
never used stored procedures, so I can give no statement to that.

A real challenge is replication. They are working on it, it has very
high priority on the todo-list. If you need replication right now,
either you have to write tools yourself, or have a look at the
PostgreSQL website http://www.postgresql.org, there are several methods,
but none of them works currently for me (I need eager master-master
replication, didn´t find it yet for 7.x.x).

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Yes, a lot.

> 5. How often do your PostgreSQL run into problem or crash? Are most of
> the problem caused by PostgreSQL itself?

I had some kind of crash yesterday in the evening (a segfault in one
frontend, probably caused by a faulty client), the first one in about
7 years. What did the server do: It rolled back running transactions,
closed all client connections due to eventually corrupt shared memory,
restarted itself, applied the Redo-Logs and that was it. No more
problems, no data loss, just going on, as if nothing had happened.

We use 7.1.3 out of the SuSE-Box (SuSE 7.3), and here and there 7.3.2
(SuSE 8.2). The biggest application database at the moment is
approximately 1GB, and up to now we do not notice any loss in
performance (we vacuum it every night). BTW: It was possible to dump the
7.1.3 and read it into the 7.3.1 without any problem, so upgrading or
moving the database is quite easy..


>
> Any suggestion and help are welcome. Thanks!


You´re welcome!
--
Frank Finner

Memory follows memory, memory defeats memory; some things are banished
only into the realms of our rich imaginings  -  but this does not mean
that they do not or cannot or will not exist - they exist! They exist!
                              (M. Moorcock, "The Revenge Of The Rose")

Folks:

I would like to thank for those who answer my questions. My client have
decide to switch from MySQL to PostgreSQL. I have successfully installed
PostgreSQL with Cygwin on my XP laptop. It's a good start. I, however, have
some other questions.

1. I am viewing the database from both PostgreSQL manager and PgAdmin III. I
can see there are three languages (C, Internal, SQL) in PostgreSQL, but not
in PgAdmin III. I login with the same user. Any idea?

2. Do I have to install new language "pl/pgSQL" in order to write functions?

3. When I start PostgreSQL, I found there are at least 4 instance of
postgres.exe running as NT services when there is not connection. Each
single one use different amount of memory. Is that normal?

4. The "stored procedure" term confused me. I did not see any "create
procedure" statement in the document. I used a lot of procedures/packages in
Oracle and MS-SQL. Are there such things in PostgreSQL? How do I do that in
PostgreSQL? Just use "Function"? I could not find examples on how to call a
"function" like "Exec MyFunctionName(......)".

5. I start PostgreSQL with the comman "pg_ctl start -D /var/pgsql/data" I
got the following message: It says the database was not shut down properly?
What I did wrong? I shut down the database with this command "pg_ctl stop -D
/var/pgsql/data -m immediate" when no connection exist.

$ LOG:  database system was interrupted at 2003-07-15 23:59:00 USMST
LOG:  checkpoint record is at 0/8C17D0
LOG:  redo record is at 0/8C17D0; undo record is at 0/0; shutdown FALSE
LOG:  next transaction id: 4428; next oid: 25168
LOG:  database system was not properly shut down; automatic recovery in
progress

LOG:  ReadRecord: record with zero length at 0/8C1810
LOG:  redo is not required
LOG:  database system is ready

6. What kind of hardware configuration would you experts recommend for
application runnng on one stand along machine.
    -- Critical but not 24x7
    -- RedHat 8, 9?
    -- PHP 4
    -- Apache 2
    -- PostgreSQL 7.3.3
    -- ODBC
    -- up to 1500 pages hit and 1000 database hit (select/update/insert) per
minute.
    -- Expect up to 1GB data in the first year
    -- Expect to spend up to US $2000 for hardware (not include monitor)

7. Is there any experienced PostgreSQL DBA in Phoenix, Arizona USA? Please
contact me offline. My client would love to get some help from you on
administration/backup/restore stuff as an extra resource.

Can you image that I got my PostgreSQL setup and running in few hours. Cool!

Thanks! This mailling list is great! You people are great!

Terence Chang

For some of your Cygwin specific questions you may want to look at the ports
mailing list. They will be more knowledgeable there.

> I would like to thank for those who answer my questions.
> My client have decide to switch from MySQL to PostgreSQL.
> I have successfully installed PostgreSQL with Cygwin on
> my XP laptop. It's a good start. I, however, have some
> other questions.
>
> 1. I am viewing the database from both PostgreSQL manager
>    and PgAdmin III. I can see there are three languages
>    (C, Internal, SQL) in PostgreSQL, but not in PgAdmin
>    III. I login with the same user. Any idea?

I don't use PgAdmin so I can't answer your question.

> 2. Do I have to install new language "pl/pgSQL" in order
>    to write functions?

If you are wanting to write them in PL/pgSQL yes. There are several
command-line tools that allow you to do this without getting into the
database itself. You should read the Administrator's Guide PDF document that
can be found ...

Note - if you install any language in template1 all databases that you
create based on template1 from then on out will have those languages
installed by default. If you are like me and create databases based on
template0 you don't get the results you desired...

> 3. When I start PostgreSQL, I found there are at least 4
>    instance of postgres.exe running as NT services when
>    there is not connection. Each single one use different
>    amount of memory. Is that normal?

I haven't used the Cygwin version in a long time but given that there are
multiple pgsql instances running on my Linux box without connections I would
assume that this is the normal mode of operation under Cygwin as well.

> 4. The "stored procedure" term confused me. I did not see
>    any "create procedure" statement in the document. I
>    used a lot of procedures/packages in Oracle and MS-SQL.
>    Are there such things in PostgreSQL? How do I do that
>    in PostgreSQL? Just use "Function"?

Just use function. Same basic mode of operation but different name.

>    I could not find examples on how to call a "function"
>    like "Exec MyFunctionName(......)".

You treat functions as you would

> 5. I start PostgreSQL with the comman "pg_ctl start -D
>    /var/pgsql/data" I got the following message: It says
>    the database was not shut down properly? What I did
>    wrong? I shut down the database with this command
>    "pg_ctl stop -D /var/pgsql/data -m immediate" when no
>    connection exist.
>
>    $LOG: database system was interrupted at 2003-07-15
>          23:59:00 USMST
>    LOG:  checkpoint record is at 0/8C17D0
>    LOG:  redo record is at 0/8C17D0; undo record is at 0/0;
>          shutdown FALSE
>    LOG:  next transaction id: 4428; next oid: 25168
>    LOG:  database system was not properly shut down; automatic
>          recovery in progress
>    LOG:  ReadRecord: record with zero length at 0/8C1810
>    LOG:  redo is not required
>    LOG:  database system is ready

Are you still running under Cygwin? Does that work under Cygwin or do you
need to use the services control panel to start/stop? If you try to start
PostgreSQL and there is a .pid in the data directory then it will tell you
that the system was not properly shut down.

> 6. What kind of hardware configuration would you experts recommend
>    for application runnng on one stand along machine.
>     -- Critical but not 24x7
>     -- RedHat 8, 9?
>     -- PHP 4
>     -- Apache 2
>     -- PostgreSQL 7.3.3
>     -- ODBC
>     -- up to 1500 pages hit and 1000 database hit
>        select/update/insert) per minute.
>     -- Expect up to 1GB data in the first year
>     -- Expect to spend up to US $2000 for hardware (not
>        include monitor)

Hit eBay and you can get a decent dual-Xeon with a gig of RAM and some nice
SCSI drives w/ a RAID card.

> Can you image that I got my PostgreSQL setup and running in
> few hours. Cool!

Having software that "just works" is always nice. Certainly makes my life
easier.

rjsjr

I'm going to skip the questions that I don't know the answer.

----- Original Message -----
From: "Terence Chang" <TChang@nqueue.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, July 16, 2003 9:05 PM
Subject: Install new language - Prev: Re: [GENERAL] Are you frustrated with
PostgreSQL


> Folks:
>
> I would like to thank for those who answer my questions. My client have
> decide to switch from MySQL to PostgreSQL. I have successfully installed
> PostgreSQL with Cygwin on my XP laptop. It's a good start. I, however,have
> some other questions.
>
> 1. I am viewing the database from both PostgreSQL manager and PgAdmin III.
> can see there are three languages (C, Internal, SQL) in PostgreSQL, but
not
> in PgAdmin III. I login with the same user. Any idea?
>


PgAdmin shows only "procedural languages".
If You execute: SELECT * from pg_language;
You'll se the column "lanispl" and PgAdmin shows only those languages where
lanisp is TRUE.

> 2. Do I have to install new language "pl/pgSQL" in order to write
functions?
>
Yes. Do something like this:

CREATE OR REPLACE FUNCTION plpgsql_call_handler() RETURNS LANGUAGE_HANDLER
AS '$_LIBPATH/plpgsql.dll', 'plpgsql_call_handler' LANGUAGE 'c';

  CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

where $_LIBPATH is /lib/postgresql by default (on cygwin)

> 3. When I start PostgreSQL, I found there are at least 4 instance of
> postgres.exe running as NT services when there is not connection. Each
> single one use different amount of memory. Is that normal?
>
Yes, 3.

> 4. The "stored procedure" term confused me. I did not see any "create
> procedure" statement in the document. I used a lot of procedures/packages
>in Oracle and MS-SQL. Are there such things in PostgreSQL? How do I do that
> in PostgreSQL? Just use "Function"?

Diferent name for same thing.
I like to call triggers as "stored procedures" because they are not
returning data to the user, but difference is only how You declare the
function. If You do something like:

CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS ' ....

it is a trigger function, and if You do :

CREATE OR REPLACE FUNCTION foo() RETURNS int8 AS ' ....

it is function that returns data.

Packages are not supported under PostgreSQL.


>I could not find examples on how to call
> a > "function" like "Exec MyFunctionName(......)".
>

SELECT something FROM myFunction(param1,param2) if function returns
composite type or

SELECT myFunction(param1,param2) if function returns single value.

Of course function can return SETOF(array) of simple or SETOF composite
type.

> 5. I start PostgreSQL with the comman "pg_ctl start -D /var/pgsql/data" I
> got the following message: It says the database was not shut down
properly?
> What I did wrong? I shut down the database with this command "pg_ctl
stop -D
> /var/pgsql/data -m immediate" when no connection exist.
>

Under cygwin, You should start and stop postmaster with "NET START
postmaster" and "NET STOP postmaster" if it is running as service.
I it fail to start, in 99% cases responsible is undeleted "postmaster.pid"
file.
It is an old Cygwin problem. Go to /usr/share/data and delete
"postmaster.pid" file. You must log-on as postgres.

> $ LOG:  database system was interrupted at 2003-07-15 23:59:00 USMST
> LOG:  checkpoint record is at 0/8C17D0
> LOG:  redo record is at 0/8C17D0; undo record is at 0/0; shutdown FALSE
> LOG:  next transaction id: 4428; next oid: 25168
> LOG:  database system was not properly shut down; automatic recovery in
> progress
>
> LOG:  ReadRecord: record with zero length at 0/8C1810
> LOG:  redo is not required
> LOG:  database system is ready
>

What causes this ? Simple query, function or something else ? Can You be
more specific.

> Can you image that I got my PostgreSQL setup and running in few hours.
>Cool!

And it is even better under linux !
I must confess, it took me more.

If You have trouble with searching through PostgreSQL documentation, I have
compiled searchable CHM file for 7.3.2.
Feel free to request it, but be prepared on 1.5 MB in Your mailbox.

Regards !

"Terence Chang" <TChang@nqueue.com> writes:
> 5. I start PostgreSQL with the comman "pg_ctl start -D /var/pgsql/data" I
> got the following message: It says the database was not shut down properly?
> What I did wrong? I shut down the database with this command "pg_ctl stop -D
> /var/pgsql/data -m immediate" when no connection exist.

"-m immediate" is the panic button; yes you will get that complaint
when you restart.  My advice: never use "-m immediate".  If you have a
situation where you think you need it (like you smell smoke coming
from the machine), you probably should be yanking the power cord out
of the wall instead.

            regards, tom lane

Glad to hear you are checking out PG Terence.  This is one time where walking
towards the light is good.

I'll answer your questions below.  I own an IT consulting company and do most of
the application development when it comes to projects that are going to call for
a backend database.  My application environment is what I've saw someone once
describe as a "Brighter LAMP"- that is to say,
Linux-Apache-Middleware-PostgreSQL.  My middleware is almost always Perl.

Quoting Terence Chang <TChang@nqueue.com>:

> Hi all:
>
> I am new to PostgreSQL DB, however I have years experience with Oracle 8i
> and MS SQL. I am in the process to promot PostgreSQL to my future client,
> due to the cost. I am just wondering if overall people feels frustrated with
> PostgreSQL or feels happey with it.
>
> I know MySQL is simpiler and cheap. With my years experience with enterprise
> level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
> stored procedure. Sorry to MySQL lovers.
>
> I have the following questions. Please reply me offline, so the mailling
> list won't get flood. Thanks!
> 1. What is your favorite GUI tool for PostgreSQL?

I don't really don't use GUI tools that much but if I do its pgaccess.  When I'm
on Windows (where I try not to go) I've been using pgAdminII.  I've tried out
DBTools and PDAdmin as well

> 2. In your organization, do you have someone who works as full time
> PostgreSQL DBA?

We're actually all consultants so we all do DB work.  Once a project is
deployed, you'd certainly be playing the part of admin but not only admin.

> 3. What is the biggest challenge you have with PostgreSQL? Administration or
> Programming?

Neither for me- I'm been programming for 20 years in various languages but these
days I pretty much stick with Perl.  I don't consider myself a great programmer
so the fact that I have not had "programming" issues to me speaks to the fact
that the documentation for the pl's (pl/pgsql, pl/perl, etc) is good enough to
get you going.  There are always this lists as well.

As far as administration I think you'd have to be more specific because
different systems have different needs when you get into product specifics.
Once of the think I LOVE of PG is that I don't have really do exotic to make
sure if ok.  In all fairness tho, must of our client aren't doing anything that
is specialized enough to warrant a custom deployment.

> 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Without a doubt I'm a PG fan.  Been using since the 6.x days and even back then
eventually I told our consultant we were going to be dumping MySQL from the servers.

My concerns are data integrity & security, scalability, transactability and of
course stored procedures.  The consulting app for my company was home on PG
dates back in the late '90's and other then PG's growing pains as a product,
I've never had a problem with something breaking beyond repair.  I have an
e-communities site called Virtual Vibe (http://www.virtualvibe.net) that I
designed whose EIS is entirely in PG.  That includes the ad schedule and user
authentication (which acutally was the hardest thing I had to do but it was a
Mod_Perl/Apache integration issues).

> 5. How often do your PostgreSQL run into problem or crash? Are most of the
> problem caused by PostgreSQL itself?

I honestly can say I do not remember any times when the database itself
"crashed" and I had to go to tape for the data.  In 5 years I think I've had to
restart PG on 2 different apps.  That I'm going to attribute to me not having
the proper tuning.

Hope this help...

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com