Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

[Tony Caduto]

Guillaume Lelarge wrote:

I asked them some time ago. They answered me this :

As for SQL Manager for PostgreSQL - we regret to inform you that the
development and support of Linux editions of EMS software products has
become impossible now that Borland no longer supports Kylix libraries
for Delphi, on which all Linux versions of EMS software were based.
Hence, EMS has made a decision to discontinue Linux versions of its
products. Linux products will not be available for sale or download anymore.


  

Just a FYI, their win32 versions will probably work fine in Linux via WINE.

Lightning Admin does anyway(work via WINE) and they program their 
products with Delphi as well, so I think it would work fine.


Just one word of advice about WINE, make sure you have the core MS true 
type fonts installed or the win32 apps will look funny, especially when 
using editors.


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best option for Postgresql Administration 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Proposed Feature

[Tony Caduto]

Bruce Momjian wrote:

Naz Gassiep wrote:
  

I'm using PG on windows for the first time (as of about 6 minutes ago).
I was thinking that it would be great to have a system tray icon with a
running indicator, kind of like the way Apache2.x for windows has, or
even MSSQL. Perhaps the PG logo with a small white circle with a red
square or a green triangle in the same fashion.
Just a thought.



And what does the icon show or do?

  

That would be pretty easy to do with Delphi.
I could whip something up and donate it to the project with a BSD license.

It could be a green "Play" arrow if the service is running and a red one 
if it is not, then have a few right click options

to start/stop/restart the service.

I was thinking the same thing awhile back, but forgot about it.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] The leanest, meanest Windows installer possible

[Tony Caduto]

Mitchell Vincent wrote:


I've been using PG for years and years but bringing it to the Win32
desktop presents some deployment challenges. Since this software will
be downloaded I am looking for the smallest possible installer that
provides the functionality I'm looking for. 
  

Hi Mitchell,

I created a slick installer to do just what you want with Inno setup.
Nice and easy to use with no MSI/WIX dependencies or complexities.
Get it here, full source included with a BSD license for the parts I wrote.
www.amsoftwaredesign.com/downloads/pg_installer_setup.zip

It's only been tested with 8.1, but should work just fine with 8.2.

This program is AS IS..with no support from AM Software.

Inno Setup is available from here for free:
http://www.jrsoftware.org/isinfo.php

I can't remember exactly, but I think this one comes in at around 8mb.

--
Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL and MySQL
http://www.amsoftwaredesign.com


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] pgTray - win32 tray tool for monitoring PostgreSQL service

[Tony Caduto]

Andrei Kovalevski wrote:

   Hi all!

   Everyone who use PostgreSQL server on Windows knows - it would be 
nice to have some tray management and  monitoring tool for PostgreSQL 
server which is running as NT Service (for example - MS SQL already 
have such tool). I have created a new project on pgfoundry - 
http://pgfoundry.org/projects/pgtray. I'm opened for any ideas how can 
we improve this tool and what features whould be helpful.



Thanks, Andrei.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Have you done any development yet?   I can do something in Delphi in a 
matter of hours and would be able to donate the code as a BSD license.
I have done a similar tray application for Firebird and it would be just 
a matter of changing the service it monitors.


We could also do a control panel applet.

Later,

Tony Caduto
AM Software Design

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Reordering columns, will this ever be simple?

[Tony Caduto]

Gregory Stark wrote:

"novnov" <[EMAIL PROTECTED]> writes:

  
Is there any plan to add such a capability to postgres? 



It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's
said they'll be doing it yet and there are a lot of other more exciting ideas
too.

  
From a admin tool developers perspective the ability to reorder columns 
without manually copying to a new table and all that is pretty exiting :-)


Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Postgresql INSERT speed (how to improve performance)?

[Tony Caduto]

Lim Berger wrote:


INSERTing into MySQL takes 0.0001 seconds per insert query.
INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query.

What can I do to improve this performance? What could be going wrong
to elicit such poor insertion performance from Postgresql?

Thanks.

---(end of broadcast)---
TIP 6: explain analyze is your friend

  



The first thing I would ask is what type of DB engine are you using 
while doing the inserts on MySQL?
The reason MySQL is doing the inserts faster is it does not have 
transaction support if you are using anything other than InnoDB.


With that said you can increase your insert performance by simply using 
a transaction and committing every 1000 rows or so.


If you do this you will see a huge performance increase.

hope that helps.

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of Lightning Admin for PostgreSQL and MySQL



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Postgresql INSERT speed (how to improve performance)?

[Tony Caduto]

Lim Berger wrote:

On 8/14/07, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote:
  

On 8/14/07, Lim Berger <[EMAIL PROTECTED]> wrote:



INSERTing into MySQL takes 0.0001 seconds per insert query.
INSERTing into PgSQL takes 0.871 seconds per (much smaller) insert query.

What can I do to improve this performance? What could be going wrong
to elicit such poor insertion performance from Postgresql?
  

Lim,

Are you sure you are not confusing Seconds VS Milliseconds from one 
query tool to the other?


What tools are you using to show the query times?

As far as I know the psql command line tool shows milliseconds not seconds.

You should probably post some more info like the actual insert query 
used and the table.


I have never seen a insert take that long even a big one, unless you 
have some network latency to the PGSQL host and it's causing the time to

be messed up on the client.

Might also help to let us know exactly how you are timing this stuff.  
Are you connecting remotely via PSQL or are you connecting via SSH and 
running psql or mysql that way?



Later,

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for PostgreSQL and MySQL

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

[Tony Caduto]

Hi,

I was just wondering if anyone could help me out by taking a look to see 
if I missed any important features.


http://www.amsoftwaredesign.com/pg_vs_fb

This comparison is going to be for the benefit of Delphi users.  The 
Delphi community is heavily biased to Firebird.


Please post any comments or suggestions here:

http://www.amsoftwaredesign.com/smf/index.php?topic=138.0

You don't need to register.

Thanks,

Tony

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

[Tony Caduto]

Dmitry Koterov wrote:
One difference in SQL syntax is that FireBird could join stored 
procedures like this:


SELECT b.*
FROM
  get_ids() a
  LEFT JOIN get_data(a.ID ) ON 1=1

(where a.ID  parameter is passed from the previous set as 
a next procedure parameter), but Postgres cannot.





Can't PostgreSQL do that now though?
Functions with out params are treated like tables as of 8.1.

Later,

Tony

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] PostgreSQL vs Firebird feature comparison finished

[Tony Caduto]

Check it out here:

http://www.amsoftwaredesign.com/pg_vs_fb


When comparing in the grid the only major advantage FB has is probably 
BLOB support.
PG only suppports 1 gb while FB supports 32gb.  Bytea is pretty slow as 
well when compared to the FB BLOB support.


The other area is Character sets and collation.  They support it at a 
field level as well as the database.


Other than that I would say PG kicks butt.

If there is any interest I could also add MySQL 5.0 to the mix as the 
third column.



Later,

Tony

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

[Tony Caduto]

Dave Page wrote:

Couple of corrections Tony:

- You don't necessarily need to stop the postmaster to take a filesystem
backup -
http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP.
Obviously that assumes logs will be replayed during recovery.

- The native win32 port will run on FAT32, we just prevent the installer
from initdb'ing on such a partition. You can do it manually however, but
tablespaces won't work.

I'm a little puzzled about why you list multi-threaded architecture as a
feature - on Windows it's a little more efficient of course, but the
multi-process architecture is arguably far more robust, and certainly
used to be more portable (I'm not sure that's still the case for
platforms we actually care about).

Regards, Dave.


  


Thanks  Dave.
Will update ASAP.

I agree with you on the multi-threaded.  I think I will add a note 
saying the the multi-threaded architecture is only advantageous  on Windows.
I have seen instances where the threaded version of Firebird completely 
craps out because one of the threads  has issues.


Will also make a note that it can run on FAT32 with some limitations.

Later,

Tony



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

[Tony Caduto]

Dave Page wrote:

Tony Caduto wrote:
  

Other than that I would say PG kicks butt.



You're just realising that? :-)

  


Ah, I new that around 2004 :-)  I just have to convince Delphi users of 
that :-)



Later,

Tony

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Can someone else verify if this is a bug?

[Tony Caduto]

Hi,
I think there might be a bug in the built in function pg_get_viewdef.

Basically if you have a function in your view SQL like this:

replace(address1, '\r', '')

pg_get_viewdef is returning the view definition with the \r replaced by 
it's ASCII code which causes this:


replace(address1, '
','')

The returned def gets a line break where the \r should be.

I have verified this in Lightning Admin and PG Admin III, just wanted a 
second opinion.


I apologize if this is already known.

Thanks,

Tony





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

[Tony Caduto]

Hi,
Someone mentioned we should put this in the PostgreSQL wiki.

Do you guys think that would be beneficial?  If so, I don't mind the 
work on the list I have done so far going on the wiki.

It would make it a lot easier to add other DBs to the mix.

Later,

Tony

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

[Tony Caduto]

Greg Smith wrote:



This is a really good comparision, focusing on features that I think 
people understand rather than so much on technical trivia.  Someone 
else mentioned moving it onto the Wiki.  Questions that pop into my head:


-Tony, would be you be comfortable with your work being assimilated 
into a larger table that was hosted somewhere else but credited yours 
as a source?



Thanks Greg :-)

I don't have any problem with what I have done so far being assimilated 
in a larger work as long as I get credited as a contributer.


Later,

Tony

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

[Tony Caduto]

Stephen Ince wrote:
Postgres can't be embedded or serverless. Firebird has the embedded 
feature. Most of the databases have this capability (hsqldb, 
derby,oracle,mysql, firebird, and db2). Derby and hsqldb are the only 
free embedded databases for commercial use.




A lot of Firebird users have been saying this as well, but the 
comparison if more for Enterprise use.
Plus if you need a embedded database wouldn't it be better to use one 
built specifically for that purpose?  i.e. SQLite for example.


Good call on the name limit, I remember running into that when porting 
something from MS SQL server to Firebird about 4 years ago.

I will have to check and see if this still applies to version 2.0

Later,

Tony

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Verison 8.3 PL/pgSQL debugger Question

[Tony Caduto]

Hi,
Is there any documentation for developers on how to use the new debugger 
in 8.3?
Specifically on how it works and general guidelines on integration into 
3rd party GUI applications.


thanks,

Tony

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] PLpgsql debugger question

[Tony Caduto]

Hi,
Does anyone know if there is a debugger function that will return the 
line numbers that are executable?


Also, is the debugger code available at pgfoundry the GUI client that 
EnterpriseDB has done or is the module that needs to be installed on the 
server?


As I understand it the debugger functions are included by default in 
8.3, but how do you install for 8.2?


Thanks,

Tony Caduto

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PLpgsql debugger question

[Tony Caduto]

Tom Lane wrote:

Tony Caduto <[EMAIL PROTECTED]> writes:
  
As I understand it the debugger functions are included by default in 
8.3,



That's incorrect.

regards, tom lane

  

Ok, thanks for the info.

Back in Sept the debugger was advertised as a feature of 8.3, so if it's 
not included how is it a feature?
Is it going to be included as a contrib module or something else?  I am 
talking about the server side stuff not the EDB GUI client.


Thanks,

Tony

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PLpgsql debugger question

[Tony Caduto]

brian wrote:


I don't know what you're referring to when you say it was "advertised 
as a feature" but it's not a part of the PG release. You can get it here:




Here ya go:

http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375

From the article:

After nine months of work, the new features in 8.3 will be available at 
www.postgreSQL.org <http://www.postgreSQL.org>. They will include:
A finished PL/pgSQL debugger, a tool for editing PostgreSQL's version of 
the standard SQL data access language in database applications.
Full text search made more accessible by becoming a feature included in 
the system code instead of being an add-on option.
Clustering code from Skype for load balancing and spreading queries to a 
large database across several PostgreSQL systems.



Now you know what I am talking about :-)

Later,

Tony Caduto

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] PLpgsql debugger question

[Tony Caduto]

Joshua D. Drake wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wed, 14 Nov 2007 12:49:37 -0600
Tony Caduto <[EMAIL PROTECTED]> wrote:
  

Here ya go:

http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375


I see nothing incorrect in that article.

Sincerely,

Joshua D. Drake


  
Who said anything was incorrect? It's just a bit misleading (the Info 
Week Article).


It's just after reading that MANY readers would think that if they 
install 8.3, the debugger hooks/whatever would be ready out of the box.


Whoever is doing the release notes may want to have something in there 
about the debugger and the fact that it's not included and has to be 
manually compiled and all that.


In the current release notes for 8.3  it makes NO mention of the debugger.


Later,

Tony Caduto



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PLpgsql debugger question

[Tony Caduto]

Richard Huxton wrote:


So you're saying the finished plpgsql debugger will be available from 
www.postgresql.org ?


"After nine months of work, the new features in 8.3 will be available 
at www.postgreSQL.org. They will include:


* A finished PL/pgSQL debugger"




There is no mention of anything debugger related in the 8.3 beta release 
notes either.


Kind of seems like its not really a feature to me, but what do I know :-)

The article is very misleading with regards to the debugger.

Later,


Tony Caduto

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] PostgreSQL 8.2 and Firebird 1.5 comparative

[Tony Caduto]

I submitted this to digg.

http://digg.com/software/PostgreSQL_8_2_vs_Firebird_1_5_for_Enterprise_Use

Needless to say PGSQL has a lot more yes entries :-)

Please give it a digg if you want.

Thanks,

Tony

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] moving from mysql to postgree

[Tony Caduto]

Pau Marc Munoz Torres wrote:

Hi
 
 I'm moving from mysql to postgresql just now i I'm a bit lost, could 
anyone tell me some place with a comparative between postdresql and 
mysql commands, i think than mostly is the same think but, any way, do 
anything change ?


pau
--

Hi,

You should check out Lightning Admin.  We have a version for both that 
have the same look and feel and could make the transition easier.


You can also ask questions at our forums: 
http://www.amsoftwaredesign.com/smf regarding moving from MySQL to 
PostgreSQL.



Tony Caduto
http://www.amsoftwaredesign.com
Home of Lightning Admin for PostgreSQL and MySQL
Both versions for 24.99 (limited time only).


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] 8.3 vs 8.2 sql compatiblity issue

[Tony Caduto]

Hi,
Just running some queries that have worked from 7.4 through 8.2 and they 
don't seem to work on 8.3.


select case when a.attnum  = any(conkey) then true else false end from 
pg_constraint where contype = 'p' and conrelid = c.oid


This one is puking on  a.attnum  = any(conkey)

returns the following error:

SQL State: 42883
ERROR: operator does not exist: smallint = text
HINT: No operator matches the given name and argument type(s). You might 
need to add explicit type casts.

0 Record(s) Returned



I am actually getting a lot of these operator does not exist errors in 8.3
another one I get is operator does not exist for char=integer

i.e.

attnum = pi.indkey[0])  used to work but fails in 8.3

Ideas?


Thanks,

Tony



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Compiled debugger plug in for windows, any one have it?

[Tony Caduto]

I dont' have a c/c++ compiler on my PCs as I am a Delphi guy.

Anyone have it compiled for win32 and willing to share?

Actually I do have CodeGear C++, but it's unlikely to work with that.

Thanks,

Tony

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] 8.3 vs 8.2 sql compatiblity issue

[Tony Caduto]

Tom Lane wrote:

That's no doubt got something to do with it, but I think Tony is mighty
confused about exactly what is failing.  pg_constraint.conkey is not
text, for instance; it's smallint[] and so the quoted bit should still
work just fine.  I'd suggest trying the query in some client that gives
you an error location pointer, which whatever he's using evidently does
not.

regards, tom lane

  
You are exactly correct, I copied the wrong line in the original 
message, sorry about that, it was this line:


case when a.attnum as text IN( select array_to_string(conkey,',') from 
pg_constraint where


which is fixed by adding a cast:

case when cast(a.attnum as text) IN( select array_to_string(conkey,',') 
from pg_constraint where



I must have missed it in the release notes about the implicit casts not 
working anymore.


It's going to be a huge pain in the ass to go through all the code and 
add explicit casts :-(



Thanks,

Tony


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Question about PostgreSQL from Delphi newsgroups

[Tony Caduto]

Hi,
Someone asked me a question about PostgreSQL on the Delphi newsgroups 
and I was not sure how to answer them:


"Do you know if using PostgreSQL a query or connection can have a 
priority set, so it can run quicker than other queries?
For example, in a POS system the reporting queries should have lower 
priority than the generated invoices insert queries. "




Thanks,

Tony

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Tom thinks it's bad code was 8.3 vs 8.2 sql compatibility issue

[Tony Caduto]

Alvaro Herrera wrote:

Tom Lane wrote:
  

Tony Caduto <[EMAIL PROTECTED]> writes:



  
case when cast(a.attnum as text) IN( select array_to_string(conkey,',') 
from pg_constraint where
  

Surely that's the worst bit of SQL code I've seen in awhile.



Wow, you really are lucky.

  


You guys really should keep such kind words to yourself. 
Not sure how in the hell you can say its bad code when it is just a 
little piece. 
You don't even know what it does.


Again thank you for you kind words of wisdom.


Have a great day.


Tony

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PLpgsql debugger question

[Tony Caduto]

Joshua D. Drake wrote:


Don't get me wrong the debugger is certainly useful but I see no
technical argument (and I am sure you will correct me if I am wrong :))
that deems it needs to be in core.

Joshua D. Drake



  

I would have to disagree.

So if you are using Oracle do you have to go through the hassle of 
finding it, then compiling and installing it?


As far as I know it's included in the core.

Things like the debugger need to just be there with no monkeying around.


Later,

Tony

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] The first dedicated PostgreSQL forum

[Tony Caduto]

On 11/15/2010 5:53 PM, Lee Hachadoorian wrote:

If anyone's interested, I've started accessing the postgres list through
gmane.org (along with several other mailing lists I subscribe to). It's
gives you the choice of reading the list as a threaded archive, a blog,
or through an NNTP newsreader or an RSS feed. Everyone chooses their
preferred interface, the community is not fractured by interface preference.



Honestly those options suck.
The mailing lists etc are fine, but they are kind of old school, people 
coming from other databases
expect a web based forum plain and simple.  To attract more users the 
forums are a GREAT idea.


Stop this ridiculous complaining about interface fracture etc .

I can tell you for SURE that many people who are not OLD SCHOOL hate 
mailing lists,  A web based forum

gives everyday users more of a chance of interacting with the community.

It would be a good idea to stop the bickering and just implement the forums.

Sorry if people don't like my honest answer :-)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Is PostGreSql's Data storage mechanism "inferior"?

[Tony Caduto]

Swaminathan Saikumar wrote:

http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql

Check out the link. I am starting out on a new personal project & had 
zeroed in on PostGreSql with Mono-ASP.NET as ideal for my needs, 
mainly owing to a PostGreSql whitepaper.
Now, I chanced upon the article above. I've pasted the cons as 
mentioned in the article, and would like the community feedback on it, 
especially with regards to the "inferior Data Storage mechanism".


The cons of PostgreSql Hosting
* Performance considerations: Inserts and Updates into the PostgreSql 
database is much slower compared to MySql. PostgreSql hosting thus 
might slow down the display of the web page online.
* BSD license issues: Since PostgreSql comes under the Berkeley 
license scheme, this is again considered to be too open.
* Availability of inferior Data Storage mechanism: PostgreSql uses 
Postgres storage system, which is not considered to be transaction sae 
during PostgreSql hosting.
* Its not far-flung: While MySql hosting and MSSql hosting have deeply 
penetrated into the market, PostgreSql hosting still remains to be 
passive in the database hosting market.
* Non-availability of required assistance for PostgreSql hosting: 
Assistance is being provided via mailing lists. However there is no 
guarantee that the issue faced during PostgreSql hosting would be 
resolved.



Those "cons" are seriously out of date.
They apply to "very" old versions of PostgreSQL and even that is 
stretching it.


The part about the BSD license is bogus. A BSD license is the most 
desirable of any Open Source license and gives you the right to use 
PostgreSQL in your commercial apps without worry.


The part about  inferior Data Storage mechanism is also flat out wrong.  
PostgreSQL uses a MVC system same as Oracle and it also has transaction 
logs,PITR etc.


Inserts and updates will be slightly slower than a Non MVC system, but 
the human eye would not detect any difference in a web page displaying, 
we are talking about miliseconds.  Who does massive amounts of inserts 
and updates from a web page anyway?  I have CMS such as Drupal running 
on both PostgreSQL and MySQL and I can't tell the difference in the 
speed the pages render.


The availability of assistance is also bogus as there are many ways to 
get support if you need it including commercial support companies and 
Enterprise DB, The mailing list is also very active and effective.


The only part that has any truth to it is the far flung part, and MySQL 
is king there, it even dwarfs M$ SQL server.  M$ SQL server is severely 
limited for hosting as well since it ONLY runs on windows and most 
hosting providers run some form of Unix where M$ cannot play at all.


So in conclusion I would not pay attention to this article, it was 
written by someone who really does not know what they are talking about.



Later,

Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL and MySQL


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Lets get the 8.3 Announcement on the front page of Digg

[Tony Caduto]

http://digg.com/programming/PostgreSQL_8_3_has_been_released

I dugg it :-)

Later,

Tony

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Lets get the 8.3 Announcement on the front page of Digg

[Tony Caduto]

At the bottom of that Register article I saw this article:
http://www.regdeveloper.co.uk/2008/01/24/stonebraker_dewitt_mapreduce/

In which it says:

"Ingres inventor and Postgres architect Mike Stonebraker"

So this Stonebraker guy is the Postgres Architect?

Interesting stuff on the Register :-)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Possible tiny issue with pg_dump.exe on windows

[Tony Caduto]

This may apply to Unix versions as well but I have not tried.

Anyway, I am using C# to create a GUI front end to pg_dump and I am 
using the PGPASSWORD environment variable to set the password like this:

System.Environment.SetEnvironmentVariable("PGPASSWORD", myargs.password);

This works fine and pg_dump.exe executes happily. But now here is the issue:

When I pass a database name that does not exist after setting the 
password via the environment variable instead of writing the database 
not found error to standard error, it prompts for the password and 
causes the C# program to lock.  Now the chance of it getting a bad 
database name is slim, but it could happen if a database is deleted and 
the list is not refreshed.  I am not forcing it to display the password 
prompt with -W by the way.


Shouldn't it write the database not found error to standard error 
instead of prompting for the password?


If I purposely pass other bad arguments after setting the password it 
does not display the password prompt in the command window.  I noticed 
this because during development I am showing the process window and for 
production it of course will be hidden.


I notices this on the 8.3 version of pg_dump, but probably applies to 
others as well.


Has anyone else noticed this?



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQLDirect versus Npgsql

[Tony Caduto]

Benjamin Arai wrote:
Has anybody had a good experience going from Npgsql to 
Corelab:PostgreSQLDirect?  I am considering migrating because it 
offers support for COPY but I am wondering about other functionality 
and performance as well.  Any feedback would be greatly appreciated.


Benjamin


The Corelab product does not support SSL connections.  I looked at it, 
but I needed SSL, so I am sticking with Npgsql.


Other than the fact it does not have SSL, it looks very nice and 
includes a dump component, not sure if that is fully managed or just a 
wrapper around pg_dump.exe.


Later,

Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL and MySQL
http://www.amsoftwaredesign.com

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Pains in upgrading to 8.3

[Tony Caduto]

paul rivers wrote:
 
Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me.  
However, unlike the blogger you cite, I read the directions before, 
not after, attempting it.



The blogger has a point about pg_dump and restore, it could be much 
better, for example
the backup process could be part of the server core and instead of 
having a fat client where most of the process is running on the client, 
a API could be
used where the backup is generated on the server and then have options 
where it could be left on the server or transferred to the clients PC.


Using pg_dump remotely is becoming a pain because it's not really 
backwards compatible with earlier releases, so you end up having to have 
multiple copies laying around to use on different server versions.


While Firebird is mostly inferior, it's backup system is much nicer that 
PostgreSQL's system.  Firebird uses a backup API, so if you backup 
remotely there is no fat client needed and it eliminates all the 
dependency issues on the client side.  The client access library 
implements the API and that's it.
You of course could hack something similar on PGSQL by using SSH and 
remotely executing pg_dump on the server, but that does not really help 
on windows servers where SSH is not a common thing.


The backup data is coming back to the client regardless, so why not just 
return it as a result set?


Just my opinion on the matter, no flames please.


Thanks,

Tony



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Pains in upgrading to 8.3

[Tony Caduto]

Magnus Hagander wrote:


For the case of upgrading, it wouldn't work. But there are certainly 
other cases where it would help. Say from your central pgadmin console 
administering 10 servers from 3 different major release trees :-(


It can be done with commandline pg_dump, but it means you have to have 
three different installs on your management or backup or whatever 
machine. Those cases would certainly be easier if you could just call 
a backup API on the server that would feed you the data... (yes, there 
are ways to do it with ssh tunneling and whatever, but that's yet 
another external service that has to be set up and configured)


I'm not saying it's worth the work and potential downsides, just that 
there are clear upsides :-)




Exactly, I didn't necessarily mean the blogger had a point about 
upgrades in general, just that pg_dump had room for improvement.


Hey maybe a backup API is something for the Google Summer of Code thing, 
it would be really nice to have, and make general backups much easier 
from a admin point of view.


Later,

Tony


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] msvcr80.dll and PostgreSQL 8.3 under Windows XP

[Tony Caduto]

Dave Page wrote:


You can avoid this by building your own libpq.dll using mingw/msys if
you like - that will work just fine with a VC++ built server.

  


Hi Dave,
Just some thoughts on the whole libpq.dll thing.

It would be really nice from a client distribution view of things to 
have a libpq.dll that was not so dependency heavy.
For the 8.3 release the total amount of files needed to use things such 
as pg_dump.exe is close to 5mb.


maybe a light version of libpq.dll is needed, just the libpq and the 
open ssl dlls would be ideal, many times all that extra stuff is not 
needed just to run pg_dump.


Didn't there used to be a project on pgfoundry that built the client 
tools separate from the server build?



Just some thoughts,

Later,

Tony




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] date and time

[Tony Caduto]

Alain Roger wrote:

Hi,

i have a stored procedure (a function) in which i must generate a 
date/time stamp.
for that i use "select * from now();" and store the result into a 
column table.


is there a easier way to do that ? i tried to store directly now(); 
result but without success.



Do you mean something like this:

CREATE OR REPLACE FUNCTION "public"."test"()
RETURNS timestamp  AS
$BODY$
DECLARE
mydate_var timestamp;

BEGIN
--store the current timestamp  in a variable
mydate_var = now();

RETURN mydate_var;


END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


Later,

Tony Caduto
AM Software
http://www.amsoftwaredesign.com
Home of Lightning Admin for PostgreSQL and MySQL

-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Getting weird pg_tblspc error, has anyone seen this before?

[Tony Caduto]

I have a client who is running this query(just brings back info about 
the databases on the server):


select
pgd.datname as database,
pdesc.description,
pgr.rolname as owner,
pgt.spcname as tablespace,
pg_size_pretty(pg_database_size(pgd.oid)) as dbsize,
pg_encoding_to_char(encoding) as encoding,
pgd.oid
from pg_database pgd
LEFT JOIN pg_roles pgr on pgr.oid = pgd.datdba
LEFT JOIN pg_description pdesc on pdesc.objoid = pgd.oid
LEFT JOIN pg_tablespace pgt on pgd.dattablespace = pgt.oid
WHERE pgd.datname not like 'template%' ORDER BY pgd.datname;

The exact error is(as reported from PG Admin III):

ERROR: could not open tablespace directory "pg_tblspc": No such file or 
directory

SQL state: 58P01


The user is running on a Mac PC that is booting Windows XP via Bootcamp.

The user also claims that the error only occurs when running the query 
locally i.e. localhost, but when he runs the query from a different PC 
it works and he gets a result set back.  The error occurs regardless of 
the client as well, same error is reported via PG Admin III, PSQL, and LA.


Other queries such as select * from pg_tablespace work fine so I am at a 
loss as why this query gives a pg_tblspc error.


He is running 8.2.6 win32 version on a Mac core 2 system that dual boots 
to XP.


I have been trying for days to reproduce the error on my PCs running the 
client and server on the same PC with no luck, I have tried the same 
version of PGSQL he is running as well as 8.3 with no luck.  I am 
missing some info from the client like what user he is running as and 
what his pg_hba.conf looks like.


If anyone has seen this before please let me know.

Thanks,

Tony Caduto



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting weird pg_tblspc error, has anyone seen this before?

[Tony Caduto]

Tom Lane wrote:

This must be coming from calculate_database_size().  Is
$PGDATA/pg_tblspc actually missing?  If the guy has no custom
tablespaces, I can believe that nothing except pg_database_size()
or pg_tablespace_size() would try to touch that subdirectory, so
he might not otherwise notice that it'd gone missing.
  


Thanks Tom,
I will follow up with the client and have him check if that directory is 
missing.
I think he may have used the Postbooks win32 installer to install his 
server, so it might be that their installer is messed up and not 
creating the directory properly or messing something up with the 
environment vars where it can't find the directory.


Later,

Tony Caduto

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting weird pg_tblspc error, has anyone seen this before?

[Tony Caduto]

Magnus Hagander wrote:

Tony Caduto wrote:
I will follow up with the client and have him check if that directory 
is missing.
I think he may have used the Postbooks win32 installer to install his 
server, so it might be that their installer is messed up and not 
creating the directory properly or messing something up with the 
environment vars where it can't find the directory.


The pg_tblspc directory is created by initdb, not the installer. And 
IIRC doesn't touch any environment variables. It does set the data 
path on the commandline, but if that one is incorrect you shouldn't 
even get that far.


//Magnus


Hi,
I just used the postbooks installer and it did not create the pg_tblspc 
directory, so the issue appears to be caused by a messed up Postbooks 
win32 installer.   Check out this screenshot:  
http://www.milwaukeesoft.com/postbooks_datadir.png


They must be deleting it after the initdb, maybe they figured since it 
was empty they did not need it?


Later,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Getting weird pg_tblspc error, has anyone seen this before?

[Tony Caduto]

Tom Lane wrote:

Bizarre.  I hope you'll tell them to fix that.


  

Agreed :-)

I bet they just took a snapshot of a install dir that had the postmaster 
stopped and used that in their setup.  It probably does not do a initdb 
during the setup.  Why it's missing that directory is a mystery :-)


I did report the issue on the PostBooks sourceforge project forum.


Later,

Tony Caduto

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] edb-debugger, debugging pl/pgsql

[Tony Caduto]

josep porres wrote:


Hi,

I don't know if here is the right place to post this, but anyway ...
Does anybody know if there is any binary dist for win32 edb-debugger 
for pgsql8.3?

If not, do you know which compiler I have to use?
I don't need to install anything on the client side where is my 
pgadmin, right?




You can use the Lightning Admin win32 debugger, very stable and FREE.
From my testing it's more stable on win32 than the others, but that 
will probably be debated since I am biased.


Anyway I encourage you to check it out at:


http://www.amsoftwaredesign.com/debugger_client_announce


Tony Caduto
AM Software Design





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] edb-debugger, debugging pl/pgsql

[Tony Caduto]

josep porres wrote:


2008/4/7, Dave Page <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>:

On Mon, Apr 7, 2008 at 10:21 AM, josep porres <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
> well, when you asked me about where I downloaded it, I
downloaded it from I
> installed it again.
> It seems I can debug with no problems till now.
> However, when I begin to debug and the function parameters
window appears,
>  if I click cancel it freezes.





Josep,

The stand alone Lightning Debugger does not have this issue and if it 
does crash or hang (not likely) it won't take down whatever admin tool 
you are

using.  Oh, and it's FREE.

http://www.amsoftwaredesign.com/debugger_client_announce

Check it out works great on win32 and built with a native compiler with 
a high performance memory manager.



Tony Caduto
AM Software
http://www.amsoftwaredesign.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] edb-debugger, debugging pl/pgsql

[Tony Caduto]

Magnus Hagander wrote:

You know, kinda like PostgreSQL vs Oracle Express ;)


Well, not quite the same since  LA Debugger Client is not crippled in 
some way Like Oracle or MS SQL Express :-)


It's just plain old freeware.

Later,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Number or parameters for functions - limited to 32 ?

[Tony Caduto]

Just a FYI, you can get around  the 32 param limit in older versions of 
postgresql by passing the function a varchar/text array, then use the 
built in array functions to pull the "params" from the passed array.


Something like this(not tested code, just a sample):

CREATE or REPLACE FUNCTION test_func(varchar)
RETURNS void AS
$BODY$
DECLARE
IN_ARRAY text[] ;
ACCOUNTNUMBER_INvarchar;
BEGIN

IN_ARRAY = string_to_array($1,'~^~');  --use a unique delimiter

ACCOUNTNUMBER_IN  = IN_ARRAY[1];

return;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


Later,

Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL and MySQL
http://www.amsoftwaredesign.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 9.0 pg_database datconfig ?

[Tony Caduto]

 Hi,
Just looking around  9.0 and noticed pg_database is missing the 
datconfig field which stored default session info for the database.
Where is this stored now?  I looked in the release notes, but no mention 
of datconfig.


Thanks,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RAISE NOTICE format in pgAdmin

[Tony Caduto]

Bill Todd wrote:
If I have a series of RAISE NOTICE 'xxx' statements in a plpgsql 
function and I call the function from pgAdmin the notice messages are 
concatenated on a single line on the Messages tab. Is there any way to 
get each message to appear on a separate line?


Is there a better way than using RAISE NOTICE to debug functions?

Bill


Bill,
Make sure you are on at least version 8.2 (8.3 is preferred) and use a 
plpgsql debugger.  Later versions of Pgadmin have one built in and a 
stand alone version is available from:
http://www.amsoftwaredesign.com/debugger_client_announce  (built with 
Delphi)


If you are using the win32 version there is a option at the end of the 
installer script to install the debugger part.  On 8.2 or 8.3 you will 
need to install it yourself.

Please see: http://pgfoundry.org/projects/edb-debugger/

Hope that helps.

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of Lightning Admin for PostgreSQL


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] SELECT INTO returns incorrect values

[Tony Caduto]

Bill,
Did you try it like this:

parent_id = 0
category_name = ''
select category, parent_category_id
 from note.category
where category_id = 477 into category_name, parent_id;
raise notice 'curr cat, name, parent id: % % ', category_name, 
parent_id;


I have found in the past that it's a good idea to initialize your vars 
before you use them in PL/pgsql.



Also as a FYI, you don't need to upper case all your text in a function 
(I know you have to do that in Firebird), just use standard case with 
normal capitalization because
PostgreSQL will lowercase everything you send to the server that is not 
in quotes.  It's a lot easier to read without the uppercase.


Later,

Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL
http://www.amsoftwaredesign.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle and Postgresql

[Tony Caduto]

David Fetter wrote:

What they want to have is a huge entity they can blame when everything
goes wrong.  They're not interested in the actual response times or
even in the much more important time-to-fix because once they've
blamed Oracle, they know the responsibility is no longer on their
shoulders.

  
That is only a perceived sense of risk avoidance, if you read the EULA 
etc that ship with Oracle, MS SQL server etc, they are not responsible
for anything that may happen to your data.  Sure management could blame 
them, but that's about it.  They would get the same amount of 
satisfaction from blaming the FOSS community.  No matter what management 
says any blame rests squarely on their shoulders and the people they 
have entrusted to create their corp projects/products when something 
goes wrong.






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to know the password for the user 'postgres'

[Tony Caduto]

Shashank Sahni wrote:


when i posted the question on dspace mailing list..one of the guy 
replied me with this solution..

createuser -h localhost -U postgres -d -A -P dspace
but he said that to execute the above command i must know the password 
for the user postgres..
Edit the pg_hba.conf file and add a entry for the PC you are doing your 
admin from and set it to Trust.
When set to trust you won't need a password, then use the admin tool of 
your choice to change the postgres password to whatever
you want.  Afterwards remember to set it back to MD5 or whatever it was 
prior to you making the change.


Later,

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 8.3 libpq.dll not working on some versions of windows

[Tony Caduto]

Hi,
We have been running into issues where the 8.3.x versions of libpq.dll 
will not load in certain

versions of windows and WINE(does not load at all on wine).

It seems to be hit and miss on Windows XP, mostly seems to affect SP3 
and some SP2 installs of XP.


I have only been able to get around this by installing a much older 
version of libpq.dll. 

And I did have all the dependencies installed along with the DLL, it 
just plain refuses to load.  I also check that there were no

rouge copies of the files in system32.

Thanks,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 8.3 libpq.dll not working on some versions of windows

[Tony Caduto]

Glyn Astill wrote:


Pretty sure I've used most 8.3.x versions here on both sp2 and 3.

How have you chacked you have all the dependencies? (I like depends.exe)

http://www.dependencywalker.com/




  
hmm, the problem seems to be that MSVCR71.DLL has a problem with some 
versions of Kernel32.DLL,

dependency walker reports this:
Error: At least one module has an unresolved import due to a missing 
export function in an implicitly dependent module.

I have all the required files in the same dir as my exe

I did this test on the latest version of wine (libpq.dll always worked 
in wine before), and I was getting the exact same behavior on the
PCs at work.  WINE is not a good test for this I know, but all I have at 
the moment.


I will have to try this on the PCs at work(on Monday) that are having 
the issue.  I know one of them is a really old XP (6 years) that was 
just recently updated to SP3. 


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] compiling libpq.dll with Borland C++, is it possible?

[Tony Caduto]

Hi,
I am trying to compile my own copy of libpq.dll using bcc32.exe, the 
docs say it is possible, but I get a error when it tries to compile dirent.c
Has anyone been able to do this? 



C:\postgresql-8.3.5\src\interfaces\libpq>make -N -DCFG=Release /f bcc32.mak
MAKE Version 5.3  Copyright (c) 1987, 2008 CodeGear
Building the Win32 DLL and Static Library...

Configuration "Release"

   bcc32.exe @MAKE0014.@@@
Borland C++ 5.5.1 for Win32 Copyright (c) 1993, 2000 Borland
..\..\port\dirent.c:
Error E2451 ..\..\port\dirent.c 35: Undefined symbol 
'INVALID_FILE_ATTRIBUTES' in function opendir
Error E2451 ..\..\port\dirent.c 52: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 53: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 59: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 62: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 64: Undefined symbol 'dirname' in 
function opendir
Error E2451 ..\..\port\dirent.c 65: Undefined symbol 'handle' in 
function opendir
Error E2451 ..\..\port\dirent.c 66: Undefined symbol 'ret' in function 
opendir
Error E2451 ..\..\port\dirent.c 67: Undefined symbol 'ret' in function 
opendir
Error E2451 ..\..\port\dirent.c 77: Undefined symbol 'handle' in 
function readdir
Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'handle' in 
function readdir
Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'dirname' in 
function readdir
Error E2451 ..\..\port\dirent.c 80: Undefined symbol 'handle' in 
function readdir
Error E2451 ..\..\port\dirent.c 88: Undefined symbol 'handle' in 
function readdir
Error E2451 ..\..\port\dirent.c 100: Undefined symbol 'ret' in function 
readdir
Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function 
readdir
Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function 
readdir
Error E2451 ..\..\port\dirent.c 103: Undefined symbol 'ret' in function 
readdir
Error E2451 ..\..\port\dirent.c 109: Undefined symbol 'handle' in 
function closedir
Error E2451 ..\..\port\dirent.c 110: Undefined symbol 'handle' in 
function closedir
Error E2451 ..\..\port\dirent.c 111: Undefined symbol 'dirname' in 
function closedir
Error E2228 ..\..\port\dirent.c 111: Too many error or warning messages 
in function closedir

*** 26 errors in Compile ***

** error 1 ** deleting ".\Release\dirent.obj"

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help

[Tony Caduto]

Hi,
Trying to get a libpq.dll that only has SSL deps using these instructions:

http://www.postgresql.org/docs/8.3/interactive/install-win32-libpq.html

It compiles a bit then dies at:

C:\postgresql-8.3.5\src\interfaces\libpq>nmake /f win32.mak

Microsoft (R) Program Maintenance Utility Version 9.00.30729.01
Copyright (C) Microsoft Corporation.  All rights reserved.

Building the Win32 static library...

Using default OpenSSL Include directory: C:\OpenSSL\include
Using default OpenSSL Library directory: C:\OpenSSL\lib\VC
Using default Kerberos Include directory: C:\kfw-2.6.5\inc
Using default Kerberos Library directory: C:\kfw-2.6.5\lib\i386
   link.exe -lib @C:\DOCUME~1\20659\LOCALS~1\Temp\nm1B.tmp
.\Release\win32.obj : fatal error LNK1107: invalid or corrupt file: 
cannot read at 0xB39
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual 
Studio 9.0\VC\BIN\link.exe"' : return code '0x453'

Stop.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help

[Tony Caduto]

Dann Corbit wrote:



How much disk space do you have?

I guess that you are running out of space.

During the link, watch the available disk space.

  


Hi Dan,
Thanks for the reply :-)

I actually got it working.Now the only problem is it still has a 
dependency for

MSVCR90.DLL

Does anyone know if this can be done with mingw so I can eliminate the 
MSVCR90.DLL dependency?
I have it installed but that is even more foreign to me than VS :-)  
(mostly use borland CodeGear tools)


Thanks,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help

[Tony Caduto]

Dann Corbit wrote:

Change from /MD to /MT and it will not use the runtime DLL.
Look under: Properties -> Configuration Properties -> C/C++ -> Code
Generation -> Runtime Library


  


Hi Dann,

I changed that in the win32.mak file and now it gives this error:

   link.exe @C:\DOCUME~1\20659\LOCALS~1\Temp\nmA6.tmp
  Creating library .\Release\libpqdll.lib and object .\Release\libpqdll.exp
LIBCMT.lib(dosmap.obj) : error LNK2005: __dosmaperr already defined in 
libpq.lib(win32error.obj)
.\Release\libpq.dll : fatal error LNK1169: one or more multiply defined 
symbols found
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual 
Studio 9.0\VC\BIN\link.exe"' : return code '0x491'

Stop.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trying Compile libpq.dll with VC++ 9.0, but need help

[Tony Caduto]

Hiroshi Saito wrote:

Hi.

It is very strange

I checked that it was ready in VC9 again. 
http://winpg.jp/~saito/pg_work/WIN32_BUILD_INF/LOG_LIBPQ_WIN32MAK.txt
Furthermore, even psql was checked. 
http://winpg.jp/~saito/pg_work/WIN32_BUILD_INF/psql_win32_pg83.mak

Please move psql_win32_pg83 win32.mak on src\bin\psql.
http://winpg.jp/~saito/pg_work/WIN32_BUILD_INF/LOG_PSQL_WIN32MAK.txt

However, It will be necessary what is bad and to investigate in your 
environment.


I think

1. One proposal nmake -f win32.mak clean

Regards,
Hiroshi Saito

-



Hi,

I will give it another try tomorrow.  It could be something related to 
the fact that I am running on XP 64bit.  I will try and compile it on a 
VM with 32bit XP, and also try to build it with MinGW.  I did try the 
clean and it didn't make any difference.  It compiles fine with the
/MD option, I even got it to compile against the lastest openSSL, though 
I did have to comment out one little line.


Thanks,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Libpq.dll lite is here :-)

[Tony Caduto]

Finally got everything to compile with MinGW.  Thanks to everyone that 
helped me out getting it going.


Here are the download links:

Libpq.dll 8.3.5.8322 with SSL No Zlib (includes the latest openssl dlls 
0.9.8i also built with MinGW)


http://www.milwaukeesoft.com/libpq_mingw_ssl.zip

If you need a really lite deployment there is also:

http://www.milwaukeesoft.com/libpq_mingw_noSSL.zip  (just libpq.dll 
8.3.5.8322 with no other dependencies)


These also have no special MS VC++ runtime requirements, just the 
standard MSVCRT.DLL which means these all work
perfectly on WINE.  The current libpq.dll won't work on wine because of 
the VC++ runtime.


I would like to maintain these for future PostgreSQL releases and if 
anyone is interested in helping or has a suggestion on where to host 
them permanently please let me know.


Later,

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Visual DATA editor for PostgreSQL?

[Tony Caduto]

Dmitry Koterov wrote:

Hello.

Is there a GUI utility to visually edit Postgres DATA (not a database 
schema!), which allows at least:
- insert/update rows using screen windowed forms (possibly ugly 
auto-generated forms, but - still forms)
- insert foreign key references by selecting them from a list (not by 
typing the keys manually)

- work with multi-line text fields (textarea) for TEXT columns

There is a lot of DB development tools around (e.g. I like EMS 
PostgreSQL Manager). But a developer tool is handy for a database 
STRUCTURE editing, and when we need to modify its DATA quickly, these 
tools are not too useful.
Lightning Admin has a form view when editing data and shows text fields 
as multi line in the form view and in the data grid itself.


Later,


Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_restore question (-T and -t)

[Tony Caduto]

Hi,
does anyone know if you can do multiple
-T or -t   (restore named trigger, restore name table) switches?

In the docs for pg_restore it does not specify if it will accept more 
than one, but in the pg_dump docs the -n and -t switches allow multiples.


Thanks,

tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] System table documentation

[Tony Caduto]

Bill Todd wrote:
Where can I find documentation for the system tables? I have not found 
anything in the 8.3.1 documentation. Thanks.


Bill


Hi Bill,

Good to see a Delphi guy here :-)

http://www.postgresql.org/docs/8.3/interactive/catalogs.html


Later,

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for PostgreSQL

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PGSQL or other DB?

[Tony Caduto]

durumdara wrote:


Another important thing that I don't understand (what as I saw) that 
the PostGreSQL is store the databases in one, unseparatable file set, 
in a directory named data.
In another databases, like DBISAM, FireBird, MySQL, the databases are 
separated to another directories/files.


This "one datadir" is seems to be not too good for us. We used DBISAM 
in our clients, and many times when we got some filesystem error, we 
can simply recover the tables - from the files.
When we want to backup or restore one database, we can do it in two 
way: a.) archive all files b.) make sql dump from database.




Postgresql uses a database cluster.  In the data directory each database 
goes into a directory identified by the database OID, and in this 
directory the database resides
in 2 gb chunks(as far as I remember).  You can easily backup the cluster 
by using the file system, you just have to make sure you stop the 
postmaster and then you can backup the entire data dir or individual 
database dir.  You have to restore this to the same version of PG though.
it's the same kind of deal with Firebird, you can't backup a running 
database with the file system and even Firebird must be stopped in order 
to do a file system copy of the database file.


Firebird also stores all it's transaction data in the same file, so you 
end up with HUGE files unless you do a backup and restore to shrink them 
down.  You don't have this problem with PostgreSQL as it stores that 
info in the WAL.


PostgreSQL will give the the best solution for your project hands down, 
just give it a chance and don't worry about what you did with Firebird 
or MySQL.


I have never had a PostgreSQL database or dump file become corrupt, but 
on Firebird it happens fairly often and it's part of the reason why I 
eventually dumped it and moved to PostgreSQL.


Hope that helps you out some.

Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL
http://www.amsoftwaredesign.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PGSQL or other DB?

[Tony Caduto]

durumdara wrote:


 - quick (re)connect - because mod_python basically not store the 
database connections persistently
 


You should consider using a Python connection pool with something like 
Pylons (http://pylonshq.com), I use dbutils:

http://www.webwareforpython.org/DBUtils/Docs/UsersGuide.html

You can scale PostgreSQL and Python web apps really well with that as 
long as you are
not using CGI.  I use Pylons combined with mod_wsgi, but you can use it 
with mod_python as well.



Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL
http://www.amsoftwaredesign.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY questions

[Tony Caduto]

Bill Todd wrote:
Thanks for the suggestion but pgloader appears to be a Linux only 
solution and my environment is Windows. The other problem is that 
there is no documentation that I could find (other than a PDF made 
from slides).


Bill

Bill,
pgloader is a Python app,  It should work on win32 as well.


Later,

Tony Caduto
AM Software Design
htpp://www.amsoftwaredesign.com
Home of Lightning Admin for PostgreSQL


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] is there anyway to get the backends IP address from

[Tony Caduto]

Bruce,
On another note, is there plans to improve the type checking of stored 
functions during the save/compile?
Currently I can pretty much make tons of mistakes (on purpose of course 
:-) and they are not flagged as errors until runtime.
The biggest complaint I see from other DBAs (MS SQL, Oracle) is that 
Postgres does little to no pre-runtime type checking.

Thanks,
Tony Caduto
Bruce Momjian wrote:
Tony Caduto wrote:
 

Hi,
Does anyone know if there is a way to get the backends IP address from 
the PID?
I am using the view pg_stat_activity and it would be nice if it would 
also display the IP address along with the PID.

I can see the IP address when I do a ps -ef but it would be nice to be 
able to get it via a sql command.
   

Added to TODO:
* Add IP address to pg_stat_activity
 


---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] question about 8.1 and stored procedures

[Tony Caduto]

Hi,
I read in a article/interview on http://madpenguin.org/cms/html/62/3677.html
that work was being done on improving/adding support for sql standard 
compliant stored procs/functions

Does anyone know exactly what that means?
Does it mean that Postgres will have stored procs that can have input 
and output params?

Thanks,
Tony
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Question about function body checking and 8.1

[Tony Caduto]

Ok,
here is a example
CREATE OR REPLACE FUNCTION mytest();
RETURNS VOID as
$$
DECLARE
mytestvar varchar;
mytestvar2 integer;
BEGIN
 mytestvarr = 'bla';
 select testfield from nonexistanttable where testfield = 2 
INTO mytestvar2;
 --The table does not exits, yet postgresql does not complain.
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

I also seem to remember that one of the 8.0 betas actually did better 
checking, but then it was gone in the next beta.  I could be wrong on 
that though.

Also if you happen to use PLperl or any of the other ones, do they 
actually do better checking than PLpgsql?   Last time I used a PLperl 
function it didn't do any checking at creation either.

Peter Eisentraut wrote:
compatibility or if the SQL statements are valid.
   

Only for very small values of "no".  Please provide an example.
 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Another help needed on Window client

[Tony Caduto]

You need to set your pg_hba.conf file to allow trusted authentication 
for 127.0.0.1

i.e.
hostall all 127.0.0.1/32  trust

Vernon wrote:
Here is my situation:

C:\Program Files\PostgreSQL\8.0>runas /user:user01
cmd.exe
Enter the password for user01:
Attempting to start cmd.exe as user "\user01" ...
~~~
A new command prompt window is popup. In the new
command prompt window, I have the following:

C:\PROGRA~1\PostgreSQL\8.0>bin\psql template1
Password:
psql: FATAL:  password authentication failed for user
"user01"

Here I type into the same password as in the previous
command and that is the password used during the
installation. 


C:\PROGRA~1\PostgreSQL\8.0>bin\createdb mydb
Password:
createdb: could not connect to database template1:
FATAL:  password authentication failed for user
"user01"

I have tried to find out any document on the subject
(that is the Window client) without success. I am
wondering whether a file contains the readable
authentication information or not. 

Thanks,
Vernon


		
__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
 


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Pgsql 8.0 on Win32 Production env.

[Tony Caduto]

I have been using the win32 version of PG 8.0 on a windows 2003 server since 
sept 2004 and it's for a  financial daily pricing application using apache 
2.5x.

I have a Delphi win32 app that the users use to update the database with data 
from a spreadsheet, the app then updates the tables used for the daily 
pricing web CGI and this happens twice per day. The web page is then hit all 
day long pulling the info from the PG tables.

I ported the app from  MS SQL server 7 and I can tell you it runs just as good 
if not better.

We even ran this whole thing on the early win32 beta's from Aug 04 and never 
had one hicup.

Very impressive is you ask me(PG 8.x).


later,

Tony

On Thursday 24 March 2005 10:31 am, fabrizio ravazzini wrote:
> Hello all, I have to build a web application and for
> this  I have to use one of my client's Windows32
> server or workstation.
> Is postgresql 8.0 native suitable for a production
> environment?
> Thanks for any advice.
> Fabri
>
>
>
>
> ___
> Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam,
> Giochi, Rubrica… Scaricalo ora! http://it.messenger.yahoo.it
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] do I need replication or something else?

[Tony Caduto]

If you are using Delphi you can use the tclientdataset which has a 
Briefcase type system built in.
Or you can use one of the many middleware systems that are available for 
Delphi, all of which will do what you want.
www.remobjects.com
www.astatech.com
etc etc

Most of them work by creating a XML local dataset then applying that XML 
dataset when the client logs back in, it handles the conflict resolution 
etc.

Delphi really is one of THE best ways to develop database apps.
Tony
Scott Marlowe wrote:
On Tue, 2005-03-29 at 16:06, Caleb Simonyi-Gindele wrote:
 

John Burger wrote:
   

If it were me, and someone proposed a model where two-way replication
was needed, I would tell them to rethink their model.  It's broken.
 

I would respectfully disagree that the requirement for two-way 
replication
indicates a broken design.
   

I agree with your disagreement.  This design is present in lots of 
non-RDB systems - CVS, IMAP, PDA syncing,  etc.  It's clearly more 
complicated, but can be made to work, and has been many times.  I 
don't see anything about databases in general, or Postgres 
specifically, that indicates it's a bad idea.

- John D. Burger
 MITRE
 

Yes, we use it successfully with the SQL Server edition of our product. 
Does anyone know if this is available with Postgre?
   

It's important to understand that what you're asking for is MORE than
simple replication, it is replication with ((semi)automatic) conflict
resolution.  If you use a simple replication system to try and do this,
you are likely to wind up with inconsistent data.
Just because SQL Server does it doesn't mean it does it right.  And the
general philosophy of the PostgreSQL team seems to be do it right or
don't bother.
So, what are the chances that you'll have records on your sales folks
machines that have also been updated back at the home office?  What
rules should be applied when conflicts arise?  These are the kinds of
questions you need to answer before jumping feet first into the fire and
getting burnt.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq
 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[GENERAL] autopackage and Postgresql?

[Tony Caduto]

Does anyone know if there is a autopackage for Postgresql?
http://www.osnews.com/story.php?news_id=10155   for a article
www.autopackage.org for home page
I tried and it works really well, and it installed a binary version of 
inkscape on my Gentoo box without issue.

T
 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] PostgreSQL and .NET

[Tony Caduto]

I think he means PL C#
I thought I saw a project started for PL C# or PL Mono on Gborg.
not knowin gmuch about .Net ... what is a 'server-side .Net'?  Like a 
PL/.Net?


Marc G. Fournier   Hub.Org Networking Services 
(http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 
7615664

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Question about format_type function

[Tony Caduto]

The format_type function is great, except for the way it formats varchars,
it always returns Character Varying or whatever, it would be nice if it 
had a option to return a short version of
these names.  i.e.

format_type(a.atttypid, a.atttypmod,true)  would return all short names
format_type(a.atttypid, a.atttypmod,false) would return full names
I don't know of any other system that shows varchars in this way, they 
should just be formated as varchar(50).

If there is another way to do this without messy case statements please 
let me know.

Thanks,
Tony


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Question about format_type function

[Tony Caduto]

Would it not be possible to add another param to the function like I mentioned 
and keep everyone happy?  Users coming from MS SQL server look puzzled when 
they see the huge Character Varying(n) in a admin tool.

I have not programmed in C since 1993, but I know in pascal you can have a 
parameter for a function be set as a const, that way it would remain 
compatible with the current function, then some logic could be added to make 
the long names be short? or even a overloaded function would be ok.

I have got around this by using  a case statment but it would be so nice if I 
could just call that function and have the results look like what users see 
in tools such as MS SQL servers enterprise manager.

Thanks,

Tony

On Sunday 03 April 2005 20:49 pm, Tom Lane wrote:
> Tony Caduto <[EMAIL PROTECTED]> writes:
> > The format_type function is great, except for the way it formats
> > varchars, it always returns Character Varying or whatever,
> > ...
> > I don't know of any other system that shows varchars in this way, they
> > should just be formated as varchar(50).
>
> Hmm ... AFAICS, "character varying(n)" and "varchar(n)" are equally
> valid spellings according to the SQL spec.
>
> I'd personally prefer the shorter spelling too, but I can't see any
> particularly strong argument for changing it.
>
>regards, tom lane
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] pg 8.0.1-r3 killls pgadmin3 and phppgadmin

[Tony Caduto]

Why don't you try PG Lightning Admin and see if you get the same error.
http://www.amsoftwaredesign.com
I have been using it with 8.01 without issues.
T
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] Announcment: PG Lighting Admin Pre-Release 6 available

[Tony Caduto]

Check it out here:
http://www.amsoftwaredesign.com/
This release adds built in SSH tunnel support right from the server 
registration dialog along with the ability to
execute a database dump from the GUI.(restore coming soon).

This release also properly exports text fields with CRLF in them (strips 
them out and replaces with a space)
(pretty hard to import into other systems when a text field causes one 
row to become many :-)

Not to mention lots of other nice features like:
Printing
Tabbed Enterprise Manager
Code completion in the function and query editors, type the name of a 
schema and period and the code completion displays all objects, or hit 
ctrl space to see all the SQL Key words and built in functions and types.
Function version control
Ability to drag and drop object names from the tree view to the function 
and query editors.
Copy field and params names/types to the clipboard
Export all or selected rows from a result set to the clipboard or to a file.
Query editor caches the contents of the editor when you close it, then 
the next time you open the editor for a DB you can see the last things 
you did.

I could go on and on.
PG Lightning Admin has been in development since Nov 2004 and is built 
with Borland Delphi 2005.

You can check it out for free for 30 days and if you like it it will 
only cost you 15 US dollars and even less if you use Euros :-)

Thanks,
AM Software Design
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Plpgsql function with unknown number of args

[Tony Caduto]

you coud pass in criteria as a delimted string, then
pull out each arg something like this
CREATE or REPLACE FUNCTION test_func( varchar)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
IN_ARRAY text[] ;
arg1 varchar;
arg2 varchar;
arg3 varchar
begin
IN_ARRAY = string_to_array($1,'~^~');
arg1= IN_ARRAY[1]
arg2= IN_ARRAY[2]
arg3= IN_ARRAY[3]
Then call the function like this:
select test_func('bla^~^bla~^~yada');
This example does not return anything, but you could build a select from the 
args you passed in then return a cursor.
normally PG is limited to 32 args (unless special compiled to support more), 
but with this technique you can pass in as many as you want.
hope this helps.
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

CREATE FUNCTION myfunc(criteria) RETURNS  AS $$
BEGIN
BEGIN
FOREACH crit IN criteria
	critsql := "b = 'crit' OR "
NEXT crit
END;

PERFORM "SELECT a, b FROM mytable WHERE (" critsql ");"

END;
$$ LANGUAGE plpgsql;
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Plpgsql function with unknown number of args

[Tony Caduto]

You don't have to cast it as anything, just return a refcursor from your 
function.

Say you return a refcursor called return_cursor
select myfunction(your_in_array);
fetch all from return_cursor;
If you are calling from a development environment, you put the return 
value of the fuction (the refcursor name) into a variable, then 
dynamicly build the fetch
all statement from return value of the function.  This must be done in 
the context of a transaction, i.e. both statements must must be executed 
in the same transaction.

The refcursors work really well and are very flexible.
Relyea, Mike wrote:
Thanks for the input.  This looks very promising.  I have one further
question.  My SQL statement is going to pull data from more than one
table in a relatively complex query.  How do I cast the RETURNS portion
of the function?  Again, I can't find what I'm looking for in the docs.
I've included an actual sample SQL statement.  I will only be changing
the first portion of the WHERE clause.
 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Suggestions

[Tony Caduto]

The native win32 version of Postgres will work for you without issues and be 
superior to the other choices you mentioned.
The MSDE runtime version of MS SQL server is crippled and only accepts a few 
conncurrent connections.
Mysql is fast, but lacks things you will probably need such as triggers, stored 
procedures etc etc
Also Mysql is technically not free unless you use it for non commercial apps.
You didn't mention if you are planning on using VB classic or VB.net?
With VB.net you can use the native .net data provider which works well, with 
classic VB you will have to use ODBC or the ADO drivers,
last time I checked the PG ado driver was pretty weak.
Also if this is a new project, I might suggest you use Borland Delphi instead of VB.
You get the same ease of use but you gain the incredible database support it provides and you can use
the native Zeos components to access Postgresql  http://www.zeoslib.net   nothing like this exists for the VB world.  Not to mention you get single exe files 
that do not require a runtime.
There are also very nice commercial components available from www.microolap.com

You can dowload the native win32 version of Postgres 8.x here:
http://wwwmaster.postgresql.org/download/mirrors-ftp?file=win32%2Fpostgresql-8.0.2.zip
Good luck,
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
syed magthum wrote:
Hi
  We are likely to build a desktop application in
VB.Net and we are in a hunt to go for a database.We
need a free version basically if it dosnt meets our
demand then we can go for the developer versions.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Postgresql Windows ODBC

[Tony Caduto]

In my opinion the easiest language for any database development is 
Borland Delphi bar NONE.
You can get the pro version for for 499.99 as a upgrade or a personal 
editon is also available for free or little cost.
You might even be able to get a copy of Delphi 7 on ebay for much less.

For Postgres development I use the Zeos components(www.zeoslib.net) and 
PostgresDAC(www.micoolap.com).

Here is a quick example of how to execute a query with params in Delphi 
with a tdataset compatible query component.

myquery.clear;
myquery.add('select firstname from companyinfo where lastname = :PARAM1');
parambyname('PARAM1').asstring:= 'smith';
myquery.open;
At this point if you have bound the query to a grid you will see the 
data, or you could loop through the result set like this:

myquery.first;
while not myquery.eof do
  begin
showmessage(myquery.fieldbyname('firstname').asstring);  
myquery.next;
  end;

The above code also works on Oracle, MS SQL, Mysql, DB2 etc etc as long 
as you use a tdataset compatible component set.

I have used C#, VB.net and VB classic and NONE of them are as easy and 
intuitive to use for database development as Delphi  NONE.

Delphi 2005 also includes Delphi.net and C# along with native win32 
development in pascal, you can also use the Kylix with the Zeos 
components for development on Linux, and while there has not been a 
update to Kylix in awhile, it still works great.

You can do lots more stuff with Delphi as well, just about anything you 
could do in C or C++ minus device drivers.

Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

?
 
In Windows, what would you say is the easiest language to use which 
can talk to Postgresql? Just curiouse.g. I have Liberty Basic 
which is very easy, but does not easily support ODBC...
 
Is anyone working with some form of Python on Windows.  I downloaded 
Python, but it seems to run only in some DOS window. I mentioned this 
on a Dreamcard/Revolution board, and they said "Oh, you want PYTHON  
CARD."
 
Anyway, I would be interested to learn of an easy language which can 
access Postgresql, together with some decent examples or tutorials to 
get me going.  I have a feeling that Realbasic will do the trick.



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Playing with PostgreSQL and Access VBA

[Tony Caduto]

In a real application that uses a client/server type database you should 
NEVER construct queries that return a million rows.
You have to keep in mind that each record has to be sent accross the 
wire via TCP/IP and it is going to take a lot of time to bring back 1 
million rows and I am sure your network admin would notice increased 
network traffic.

In a real application you would use something like the Delphi Tdataset 
based components which will only bring back the records that can be seen 
on the screen, then when you scroll, it brings back more.

you can manually create something like this using Postgresql handy LIMIT 
and OFFSET sql keywords in your query to build a paging system.

Access is actually a very poor choice for client/server application 
development, like I said before, invest some time learning Delphi, it's 
not difficult at all and you won't have these problems.
You can get a copy of Delphi 7 personal edition and you can use the zeos 
components with it.  http://www.zeoslib.net

Tony
.
 
When I had it loop and add 1000 rows, it was ok...   When I told it to 
add a million rows then after 250,000 the Access application hung 
(but not the whole machine, and not the


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] psqlODBC and Access

[Tony Caduto]

Let's not forget about PG Lightning Admin :-)
http://www.amsoftwaredesign.com
In case you don't like PgAdminIII you may try commercial products like
EMS PostgreSQL Manager
http://sqlmanager.net/products/postgresql/manager
What they call "Visual Database Designer" might just be what you are 
looking for. It's an MS-Access-like, visual display of tables and 
relations. But remember, it's a frontend and not a modelling tool.



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Delphi personal (was Playing with PostgreSQL and Access VBA)

[Tony Caduto]

All you need is the Personal Edition of say Delphi 6 or Delphi 7, and 
even Delphi 2005.
For Delphi 2005 personal edition see this link:
http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake
Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005 
can do, and I bet you can find version 5 on ebay or elseware for dirt cheap.

The personal editions do not include database access components by 
themselves, but you certainly can use third party tools such as Zeos 
(http://www.zeoslib.net) or those from
http://www.microolap.com.
You could even grab a copy of libpq.pas if you want to do direct access 
to PG.

You get what you pay for and there is nothing available for Python or 
WXwidgets that even comes close to Delphi for RAD/GUI database development.
Not to mention all the other stuff you can do with Delphi, it's also a 
great programming language for console apps, services, TCP/IP servers.  
There are even remote control applications built with it ala PC Anywhere.

Plus if you call borland, I am sure they would give you the upgrade 
price if you have a old copy of VB laying around for a competive upgrade.

 
I was very impressed by what one member said regarding Delphi, but, 
when I looked at pricing,... well I would have to lie through my 
teeth to get the cheap academic version, and the personal version 
sounds like it doesnt have the file access abilities for PostgreSQL. 
And the prices for enterprise versions at programmersparadise.com  
like $4000, sort of puts me off. 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[GENERAL] question about about future 8.1 and IN, INOUT, and OUT parameters

[Tony Caduto]

I saw that the recently released pl/java has the ablity to use IN, INOUT, and 
OUT parameters.

Is the syntax going to be exactly like it is in Oracle?

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Playing with PostgreSQL and Access VBA

[Tony Caduto]

I guess I was thinking more of the "big picture"
Your right of course, for someone just fooling around at home or for a very 
small business Access is great, but I am saying from experience that Access is
a poor choice for any kind of large project or corp use.

Access may be a poor choice for a Delphi developer to use.  But for many of 
us, it's an excellent choice.  Everything depends on your situation and on 
your needs.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Serial / auto increment data type

[Tony Caduto]

Yes, that is the way to do it.
I migrated several large Access DB to postgres and most of the tables that 
where done by the
non developers didn't have a primary key defined, so the easiest way was just 
to add a new field to every table and make it a serial.
I believe you would have to do this even if you were using MS SQL server as the 
backend.
Here is my question.  Would I be wise to define each and ever table with 
a serial id, so that I may always be guaranteed something uniqe, to 
satisfy Access.   It seems to me like no harm can be done, since if you 
dont need the serial id, you dont need it, but it is always there if you 
do need it, and it is harder to add such a field down the line, after 
the fact.
 
Thanks!
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Postgresql, Windows, Peer-2-Peer network

[Tony Caduto]

OF COURSE as long as the PC is NT or greater(won't work on windows 95/98).
just make sure you modify the pg_hba.conf file to allow the other PCs to 
access and change the postgresql.conf file to listen on all i.e. *

You could even use PG Lightning Admin with it's built in SSH tunnel 
support, or Putty.exe and have all the other machines connect via 
encrypted SSH, then you could simply leave your server to allow 
connections only from localhost.

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
 
IS IT POSSIBLE to install Postgresql for windows as a service on one 
of the machines (as I did on my home machine, specifing "localhost") 
but then have the other machines access the PG server through the 
internet address of that machine (the quad address)?
 
- Thanks in advance for your help!


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Visual Query Builder

[Tony Caduto]

PG Lightning Admin has one.  It's not free,but very,very inexpensive, 
especially if you purchase with Euros
http://www.amsoftwaredesign.com

Hrishikesh Deshmukh wrote:
Hi All,
I have DB in Postgresql; is there a visual query builder? 

Thanks in advance.
Hrishi
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
 


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Thanks for insights into internal design

[Tony Caduto]

That's fine for a system like access or DBASE, but you should never be making 
queries that large for
a production application.
Access or DBASE or any other local FILE based system will not have any problems 
bringing back 1 million
records because it does not have to bring the records across the wire via 
TCP/IP.
You should alway limit queries by a date range or at least implement a paging 
system.
250,000 to 1 million rows is also going to suck up a huge amount of system 
memory on the client side.
It does not seem like you are really catching on to the concept of a 
client/server based system.
It does not matter if there is a billion rows because you should NEVER be 
letting a end user bring back
the full amount anyway.  Think about it.
Postgresql is not a local file based system like Access or Dbase, you can't use 
the same testing methods
or you will be in for a world of hurt.

You give me valuable insight into the inner workings of such software.  
I am a firm believer in testing everything with very large files.  One 
might spend months developing something, and have it in production for a 
year, and not realize what will happen when their files (tables) grow to 
several million records (rows).  And it take so little effort to create 
large test files.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Is there a bug in the win32 pg_restore (version 8.02) ?

[Tony Caduto]

Hi,
I just tried to restore a db with the win32 pg_restore that shipped with 8.02 
and I get to the part where
it prompts me for the password, and I enter it, then I get the following error:
C:\>pg_restore.exe --ignore-version --verbose --dbname=mandi_test -W -U postgres
 --port=5432 --host=192.168.1.23 "C:\Documents and 
Settings\Administrator\Desktop
\mandi.bak"
pg_restore: connecting to database for restore
Password:
pg_restore: [archiver (db)] connection to database "mandi_test" failed: FATAL:
password authentication failed for user "postgres"
pg_restore: *** aborted because of error
I have the pg_hba.conf for my host set to md5, and if I change it to trust 
then I can do the restore.
update:  I just tried the pg_restore that has a version number of 8.0 and it 
works, definately seems to be
a bug in the 8.02 version (win32)
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] temp tables ORACLE/PGSQL

[Tony Caduto]

This is not entirely correct.   We use temp tables all the time in PLpgsql 
functions and never have to use
execute.  We have found that you have to use EXECUTE only in certain 
circumstances.
 stored procedures that use temporary tables are more
painful to write - you need to use EXECUTE for any SQL that references a 
temporary table - read the Porting From Oracle section of the PostgreSQL 
manual. I'd recommend rereading it several times.
we use this in all our functions that use temp tables, and we use PG 
Lightning Admin, which refreshes the
connection each time you run a query which gets around a lot of issues with 
temp tables.
CREATE or REPLACE FUNCTION public.iftableexists( varchar)
RETURNS pg_catalog.bool AS
$BODY$
DECLARE
 BEGIN
 /* check the table exist in database and is visible*/
 perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid
= c.relnamespace
where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);
 IF FOUND THEN
RETURN TRUE;
 ELSE
RETURN FALSE;
 END IF;
 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] temp tables ORACLE/PGSQL

[Tony Caduto]

We only do the connection "refesh" in the Lightning Admin Query editorfor 
testing our SQL that uses temp tables.
refreshing the connection eliminates the OID does not exist problems.
We put everything into stored procs and use them from Delphi applications and 
still never use execute in
our procs.
I think the key is we use ON COMMIT DELETE ROWS when we create our temp tables, 
we don't ever drop them
until the client disconnects.  using the ON COMMIT DELETE ROWS just re uses the 
the same temp table over
and over again.  You only need to use select if you drop the temp table and 
recreate it multiple times in
the same session.
Here is a example of how we use temp tables:
(NOTE: I ADDED the temp table to this function, you don't actually need it in 
this case becuase
the cursor can just be refereneced against the select statement)
This can be called over and over again from the same connection because the 
temp table is not dropped, it's
just re-used.  when the client does disconnect it gets dropped.
You could also truncate the temp table at the end of the function if you 
wanted, then you don't have
data sitting in the table until the next function call.
CREATE or REPLACE FUNCTION admin.spadm_get_status_list()
RETURNS pg_catalog.refcursor AS
$BODY$
DECLARE
return_cursor refcursor;
BEGIN
return_cursor = 'return_cursor';
IF iftableexists('temp_get_status_list') THEN
 RAISE NOTICE 'temp table already exists';
ELSE
CREATE TEMP TABLE temp_get_status_list
(
STATUS_ID SMALLINT,
DESCRIPTION VARCHAR(50))WITHOUT OIDS ON COMMIT DELETE ROWS;
END IF;
 INSERT INTO temp_get_status_list
 (
  STATUS_ID, DESCRIPTION
 )
 SELECTstatus_id, description
 FROMadmin.admin_status
 ORDER BY 1;
OPEN return_cursor FOR SELECT * FROM temp_get_status_list;
RETURN return_cursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


If you refresh the connection each time you run a query,  maybe you 
don't need to use EXECUTE with temporary tables in stored procedures, 
but who does that in a production database application? Most people want 
to re-use connections for performance reasons.

Dennis
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Postgresql and VBA vs Python

[Tony Caduto]

Like I said before get the personal/standard version of Delphi 6,7 or 
2005, it's 99.99 and you can connect to postgres with it using third 
party component sets like Zeos. (2005 may not be available yet)

The licence varies on the version, I think Delphi 6 personal allows for 
commercial developement if you buy the shrink wrapped version, the 
others may only allow non commercial development.

There is another option you may not be aware of:
http://www.icsharpcode.com/OpenSource/SD/Default.aspx
Totally free and rivals Visual Studio.net and has a visual form 
developer.  Check it out.  You can use it with the Npgsql native .net 
data provider for Postgresql.  Not as easy to develop DB apps as Delphi, 
but a better choice than Access.

You can create a databound form in Delphi just as easily as access and 
you don't need to learn VBA or Python, just Pascal.
Delphi is Pascal (object pascal to be exact) and is easy to learn and 
intuitive.  It was taught at most high schools/universities before Java 
came around

As far as the testing goes I am just try to get across the fact that the 
type of testing you are doing is kind of a waste of time on a 
client/server type system.  Like I said before it's fine for Access or 
Dbase.
Bringing huge results sets (250,000) can bring Oracle to it's knees if 
the conditions are right.

Access is probably doing some kind of internal paging, it's not really 
bringing back 250,000 rows.  Rekal on the other hand probably attempts 
to bring back all them and that's why it hangs. It can take a huge 
amount of time to bring back that many records across the wire, 
especially if you are on simple home network that is probably running at 
10mbits per second in half duplex mode.  Using  a massive amount of rows 
to test your dev tools is flawed because of the fact one may be doing 
paging and the other is not.

 
I would just like to find a free, or low cost basic like language that 
would let easily paint GUI forms in some IDE, and then easily add my 
own script to interact with the Postgresql database. Such an a 
development environment, and such tutorials should be very common and 
easily accessible, since a sql engine is not much use unless you can 
access it.
 
Probably Powerbasic or Realbasic would do what I want, and I would 
feel comfortable with them, but they are rather expensive.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] temp tables ORACLE/PGSQL

[Tony Caduto]

I am assuming you need "session varables" for a web based app right?
For a standard client/server app created in something like VB or Delphi all you 
really need is a single
connection(because most db apps are single threaded), and a temp table will 
stay around until that
connection is closed, and if you use ON COMMIT DELETE ROWS you don't ever need 
to
use execute in functions(that was the whole point).
You are right that the temp tables would not work with HTTP since each request 
is a new connection,
but there are easy ways around that by using a sessions table, which sounds 
like what you did.
For storing web based session variables I use a table in PG like this:
CREATE TABLE public.sessions
(
sessionid integer,
sesstimestamp timestamp,
sessdata text,
CONSTRAINT pk_sessions PRIMARY KEY (sessionid)
);
sessionID is generated from a sequence and stored on the client in a cookie
sesstimestamp is used to expire the session after 20 minutes of no activity
(the timestamp is updated at each new request from the client, so it's not a 
hard 20 minutes)
sessdata can contain as many variables as you like in this form:
username=bsmith
It all works great and I don't need global temp tables :-)  And using a scheme 
like this will work on
any database that supports sequences or some other form of generating a unique 
ID.
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
fisher wrote:
Hi 
Thank You very much.
As I mentioned I need temp tables for storing "sesssion variables".
I plan to write functions to return suitable column value and I need
them to be availabele during whole session. That makes deleteing on
commit not the best solution. For example I want to keep emp_id in one
of columns and
use ps_get_emp_id() function to return it's value in other functions.
All values stored in thie "parameter" temp table are quite stable.

Anyway thank You very much.
fisher
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] ip connection on local p2p network

[Tony Caduto]

add the following to pg_hba.conf
hostall all 192.0.0.0/8  md5
this will let every PC on you network access the PC
or you could add one entry for each host you would like to have access
hostall all 192.0.0.101/32  md5
hostall all 192.0.0.102/32  md5
If you don't want to use a password, change md5 to trust.
finally you must modify the postgresql.conf file like so:
(this is what it looks like by default)
#listen_addresses = 'localhost' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
change it to this:
listen_addresses = '*' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
notice the listen_addresses is uncommented.
That's all you need to do.
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

[EMAIL PROTECTED] wrote:
I just installed the windows postgresql on one of the five machines in 
our office, which happens to be 192.0.0.9 according to ipconfig.   I 
have installed pg explorer on another machine which happens to be 
192.0.0.101     What changes do I have to make in the various .conf 
files for the postgresql server, (and in pg explorer) so that I can 
connect to the server from the other workstation, hence proving to 
myself that I could use postgres on our small office network.  Thanks 
for your help!
 
 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Tuning queries inside a function

[Tony Caduto]

We have functions with upwards of 800 lines  and we simply pull the queries 
out and stick them in the PG Lighting Admin or PG Admin III query editor.  We 
then substitue any vars etc with real values.  Works ok.


> What's the best way to tune the queries inside a user function?
>
> I have a fairly complicated function that may make as many as 10 queries
> on several tables, some of which involve multiple joins.
>
> Further, in the PHP program that needs this function, it can be called
> as many as 400,000 times.  The last time I ran the program in production
> mode, it took 35 hours to complete!  Since then I've done some reworking
> to avoid the function calls about half of the time, that cut the run time
> down to about 16 hours, but that's still longer than I'd like.
>
> I need to find out if the function can be tuned further, but 'explain'
> doesn't really tell much about what's happening inside the function.
>
> Any ideas on how to tune a user function?
> --
> Mike Nolan
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Postgres vs Firebird?

[Tony Caduto]

Hi,
We have a big project here that we originaly did in Firebird 1.0, it worked 
well, though was missing tons
of features like built in functions, temp tables etc etc. We then updated to 
Firebird 1.5 and again it worked good.
We always had issues with the stupid Firbird OAT  (oldest active transaction), 
if the OAT gets stuck your database will start
to get huge as transaction data is stored in the database file itself. Firebird 
does not have the concept of
a tranasction log (at lease a seperate one)
The only way to compact a Firebird DB is to do a backup and restore, and we had 
lots of issues with this when the OAT
got stuck.  You have to constantly monitor the OAT and the OIT (oldest 
interesting transaction)
Anyway soon after our update to FB 1.5 I started playing around with Postgres 
7.2 or 7.3 and man was I impressed.
I could do temp tables had tons of built in functions, there was no weird SQL 
dialects(firebird has 3 SQL dialects)
I could just use any SQL i wanted from functions(firebird has several types of 
SQL PSQL,DSQL etc and you can't use one from
the other.
Firebird does not have a freely available replication system or a GUI admin 
tool(there are third party ones available)
I can tell you from experience Postgresql 8.x is WAY BETTER than Firebird.

Benjamin Smith wrote:
As a long-time user of Postgres, (First started using it at 7.0) I'm reading 
recently that Firebird has been taking off as a database. 

Perhaps this is not the best place to ask this, but is there any compelling 
advantage to using Firebird over Postgres? We have a large database (almost 
100 tables of highly normalized data) heavily loaded with foreign keys and 
other constraints, and our application makes heavy use of transactions. 

I say this as my company's growth has been exponential, showing no sign of 
letting up soon, and I'm reviewing clustering and replication technologies so 
that we can continue to scale as nicely as we have to date with our single 
server. (now with a load avg around .30 typically) 

-Ben 
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]