date:20060124


Carl Conard wrote:

I've successfully installed Postgres 8.1.2 on WS 2003 R2 on a Lenovo
(IBM) ThinkPad.  I used the default installation options and everything
seems peachy keen for a single user (using localhost).  
 
However, when we started performance testing Postgres (vs. MySQL) using

a 3rd party tool (I forget the name) running on the server, Postgres
would drop connections after about the 10th or 11th virtual user (of 20)
hit the DB.  The remaining user processes would remain visible in
Window's Task Manager and just "hang."


How are you connecting?
What do you mean by "drop connections"?
What do you mean by "hang" - does the server have problems shutting them 
down if you tell it to?

What do the logs show?
What changes have you made to your configuration files?

--
  Richard Huxton
  Archonet Ltd

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

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

Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.

2006-01-24 Thread R, Rajesh (STSD)




Its not a macro.I meant that the code generated by 
AC_REPLACE_FUNCS([getaddrinfo]) by configure.in for "configure"does not have 
"#include ". Hence function is not detected(unresolved 
getaddrinfo).Hence  I thought AC_TRY_LINK could give test program 
instead of AC_REPLACE_FUNCS taking one.$ diff -r configure.in 
configure.in.new918a919> AC_MSG_CHECKING([for 
getaddrinfo])920c921,926<   
AC_REPLACE_FUNCS([getaddrinfo])--->  AC_TRY_LINK([#include 
 #include 
],> 
[char (*f)();f=getaddrinfo;],>   ac_cv_func_getaddrinfo=yes, 
ac_cv_func_getaddrinfo=no)> if test x"$ac_cv_func_getaddrinfo" = xyes; 
then>   AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the 
getaddrinfo function])> fi923a930> 
AC_MSG_RESULT([$ac_cv_func_getaddrinfo])Regards,  Rajesh R--This 
space intentionally left non-blank.-Original 
Message-From: Tom Lane [mailto:[EMAIL PROTECTED]]Sent: Tuesday, 
January 17, 2006 8:34 PMTo: R, Rajesh (STSD)Cc: 
pgsql-hackers@postgresql.orgSubject: Re: [HACKERS] [GENERAL] [PATCH] Better 
way to check for getaddrinfo function."R, Rajesh (STSD)" 
<[EMAIL PROTECTED]> writes:> But the bottomline is the default test 
does not include  in> the test code.That's 
odd.  Is getaddrinfo a macro on Tru64?  If so, the appropriate patch 
would probably make the test look more like the tests for finite() and 
friends:dnl Cannot use AC_CHECK_FUNC because finite may be a macro 
AC_MSG_CHECKING(for finite) AC_TRY_LINK([ #include  double 
glob_double; 
],    [return 
finite(glob_double) ? 0 : 
1;],    
[AC_DEFINE(HAVE_FINITE, 1, [Define to 1 if you have finite().]) 
AC_MSG_RESULT(yes)],    
[AC_MSG_RESULT(no)])    
    
    regards, tom 
lane

Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.

2006-01-24 Thread Martijn van Oosterhout

On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) wrote:
> Its not a macro.
> I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by
> configure.in for "configure"
> does not have "#include ". Hence function is not
> detected(unresolved getaddrinfo).
> Hence  I thought AC_TRY_LINK could give test program instead of
> AC_REPLACE_FUNCS taking one.

But if it isn't a macro, why do you need the header file? In C it's
perfectly legal to declare the symbol yourself and try to link and it
should work *unless* it's normally a macro.

We're still missing some necessary understanding here...

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


signature.asc
Description: Digital signature

Re: [GENERAL] FATAL: terminating connection due to administrator


surabhi.ahuja wrote:

The exact message i saw is this:

LOG:  received fast shutdown request LOG:  aborting any active
transactions FATAL:  terminating connection due to administrator
command

so does this mean that someone is trying to stop postmaster by
sending it a kill signal?


Someone or something. It can be Linux's out-of-memory facility picking 
processes to kill. Google "oom killer" for discussion. It would need to 
be sent from the postgres user or root though (or a process running as 
them).



i also have these questions:

1. many times i have seen two instances of postmaster running. how
does that happen and how to prevent it from happening?


Shouldn't (unless you have two installations of course). You shouldn't 
have to do anything to prevent it from happening. Try and capture 
details of the processes with "ps aux" and "pstree" if you can.



2. into the logfile (which i specify at the time of starting
postmaster), i want to add timestamps, for each log/warning etc. How
do i do that?


See the manuals for details - run-time environment / error reporting and 
logging.

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

--
  Richard Huxton
  Archonet Ltd

---(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] Quoted NULLs with COPY FROM


George Pavlov wrote:

Did you try NULL AS ''?


yes i did. that is the default and does not change the outcome (same
errors about trying to insert an empty string into a numeric field.


Well there's your problem. Quotes tend to imply a text field. Assuming 
you don't want to write a short Perl script to pre-process the file the 
simplest way would be to import into a temporary table with text columns 
rather than numeric, then copy from that into the real table (with CASE 
or two queries).


--
  Richard Huxton
  Archonet Ltd

---(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] Does this look ethical to you?

> -Original Message-
> From: Magnus Hagander [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 09:05
> To: Tony Caduto; George Pavlov
> Cc: Bruce Momjian; pgsql-general@postgresql.org; Dave Page
> Subject: RE: [GENERAL] Does this look ethical to you?
> 
> > George Pavlov wrote:
> > > they have the same kind of page setup for pg Admin:
> > > 
> > > http://pgsql.navicat.com/PG_Admin/index.php
> > > 
> > > this one renders...
> > > 
> > > both pages seem to tell robots not to cache them, so can't view a 
> > > cached view on google.
> > 
> > 
> > At least PG Admin is free software and doing that is not 
> > really hurting anyone since (at least I don't think) no one 
> > is making money off of PG Admin III.

There most certainly are companies making money from pgAdmin, including
at least 2 of the most well known large PostgreSQL companies. In
addition to potentially hurting them, you are trading off our long
established name, which to add insult to injury you haven't even spelt
or formatted correctly (it is, and has always been pgAdmin)!

> > I do mention my product as a PG Admin III alternative but I 
> > would never use Navicat or EMS PG Manager by name anywhere on 
> > my site. I use words like "Other Companies" etc.

If it helps at all, we ask our supporters to follow this guideline when
setting up keywords etc on software directory type sites:

"These should reflect any aspect of what pgAdmin does, and what it works
with. Under no circumstances should you include competitors company
or product names in keywords. "

I would appreciate it if you would hold yourself to a similar standard
and stop using pgAdmin's name to promote your product.

Regards, Dave.

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

Re: [GENERAL] Updating rows (automatically) that are selected

2006-01-24 Thread Bruno Wolff III

On Wed, Jan 18, 2006 at 14:26:28 -0700,
  Assad Jarrahian <[EMAIL PROTECTED]> wrote:
> Hello,
>   I have a table called X
> it contains 3 fields of importance(amongst others).
> time (timestamp), snooze (int) , inbox (int)
> 
> The time column sometimes has null values.
> What I want is that every time a row is selected, if the time is a
> null value, it is set to now() and  snooze is set to 0 and inbox to 1
> 
> How would I go about doing this. I looked into Creating Rules, but I
> am not sure if this will work.

Are you talking about updating the underlying roles or just returning
modified information?

I don't think this is going to be easy to do in the case where you update
the underlying rows. Normally you update data like that using triggers
rather than rules, but you can't trigger on selects.

Maybe you should be changing the data when you store it, rather than the
first time you read it back?

If you don't want to update the underlying data, then using a view with a
CASE statement is probably the easiest soltuion.

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

Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function.

2006-01-24 Thread R, Rajesh (STSD)




sorry. It is a macro.so, would it be better to 
check for the macroas suggested by Tom or go with this patch$ diff -r configure.in configure.in.new918a919> 
AC_MSG_CHECKING([for getaddrinfo])920c921,926<   
AC_REPLACE_FUNCS([getaddrinfo])--->  AC_TRY_LINK([#include 
 #include 
],> 
[char (*f)();f=getaddrinfo;],>   ac_cv_func_getaddrinfo=yes, 
ac_cv_func_getaddrinfo=no)> if test x"$ac_cv_func_getaddrinfo" = xyes; 
then>   AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the 
getaddrinfo function])> fi923a930> 
AC_MSG_RESULT([$ac_cv_func_getaddrinfo])
I guess, instead of adding seperate code for macro 
checking as suggested by Tom, this might serve dual purpose.Thanks,Rajesh R--This 
space intentionally left non-blank.-Original 
Message-From: Martijn van Oosterhout [mailto:kleptog@svana.org]Sent: Tuesday, 
January 24, 2006 2:46 PMTo: R, Rajesh (STSD)Cc: Tom Lane; 
pgsql-hackers@postgresql.org; pgsql-general@postgresql.orgSubject: Re: 
[HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo 
function.On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) 
wrote:> Its not a macro.> I meant that the code generated by 
AC_REPLACE_FUNCS([getaddrinfo]) by> configure.in for "configure"> 
does not have "#include ". Hence function is not> 
detected(unresolved getaddrinfo).> Hence  I thought AC_TRY_LINK 
could give test program instead of> AC_REPLACE_FUNCS taking 
one.But if it isn't a macro, why do you need the header file? In C it's 
perfectly legal to declare the symbol yourself and try to link and it should 
work *unless* it's normally a macro.We're still missing some necessary 
understanding here...Have a nice day,--Martijn van 
Oosterhout      http://svana.org/kleptog/> Patent. 
n. Genius is 5% inspiration and 95% perspiration. A patent is> a tool for 
doing 5% of the work and then sitting around waiting for> someone else to 
do the other 95% so you can sue them.

Re: [GENERAL] Isolation level in a function

2006-01-24 Thread Bruno Wolff III

On Thu, Jan 19, 2006 at 02:05:41 -0800,
  bgolda <[EMAIL PROTECTED]> wrote:
> Hello, this is my first post, please don't shoot...
> 
> I was just experimenting with transactions (PG 8.1), and there is
> something which puzzles me. If i write 'SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE;' in my function, it breaks. Error informs me, that it was
> executed after some query, while it is a first command in the function
> after declares and begin!

Because there is already a transaction which the function is executing in
and for which a query has been started (e.g. the one that calls the function).

> However, it seems to work perfectly well if I change in the same place
> the system variable, responsible for the transaction level. I used
> set_config, if I remember well. The function is only for a DBA task, so
> it seems to be all right to use set_config, isn't it?
> 
> And my questions are:
> 1) Why in the first case it is not possible? Is it an error or am I
> just doing something wrong? Should have set something before?
> 2) Are there any downsides of the mentioned solution (changing
> transaction_isolation value), except of the fact that it is an
> administration command and should not be used :)?
> 
> Thanks,
> 
> _-_-_-_
> Bart Golda
> 
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

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

[GENERAL] execution plan : Oracle vs PostgreSQL

2006-01-24 Thread FERREIRA, William (VALTECH)


hi,

i have a database storing XML documents.
The main table contains the nodes of the document, the other tables contain 
data for each node (depending on the node's type : ELE, Text, PI, ...)

My test document has 115000 nodes.
the export of the document(extracting all informations from database and 
writing XML file on disk) takes 30s with Oracle and 5mn with Postgresql.
The tests have been made on different systems
- Sun systems :
- solaris8 : 16 cpu and 64Gb RAM
- solaris8 : 2cpu and 8Gb RAM
- Windows Systems :
- WinNT : 1 cpu(PIV) and 1Gb RAM
- WinXP : 1 cpu(centrino) and 512 RAM

the times are always the same, except with the centrino for which it takes 1 
min.

So i don't understand such differences.

here is my main query (on the main table for getting the children of a node) 
and the execution plan for PostgreSQL and Oracle :

-Query :
SELECT   *
FROM xdb_child c1
   WHERE c1.doc_id = 100
 AND c1.ele_id = 2589
 AND c1.isremoved = 0
 AND c1.evolution =
(SELECT
MAX (evolution)
   FROM xdb_child c2
  WHERE c2.doc_id = c1.doc_id
AND c2.ele_id = c1.ele_id
AND c2.evolution <= 0
AND c2.child_id = c1.child_id
AND c2.child_class = c1.child_class) ORDER BY c1.evolution, 
c1.indx

-Postgresql plan :
"Sort  (cost=33.19..33.20 rows=1 width=28)"
"  Sort Key: evolution, indx"
"  ->  Index Scan using index_xdb_child_1 on xdb_child c1  (cost=0.00..33.18 
rows=1 width=28)"
"Index Cond: ((ele_id = 2589) AND (doc_id = 100) AND (isremoved = 0))"
"Filter: (evolution = (subplan))"
"SubPlan"
"  ->  Result  (cost=4.34..4.35 rows=1 width=0)"
"InitPlan"
"  ->  Limit  (cost=0.00..4.34 rows=1 width=4)"
"->  Index Scan Backward using index_xdb_child_2 on 
xdb_child c2  (cost=0.00..4.34 rows=1 width=4)"
"  Index Cond: ((ele_id = $1) AND (doc_id = $0) AND 
(child_id = $2) AND (child_class = $3) AND (evolution <= 0))"
"  Filter: (evolution IS NOT NULL)"

-Oracle plan (cost 14):
Operation   Object Name RowsBytes   CostObject Node In/Out  
PStart  PStop

SELECT STATEMENT Optimizer Mode=CHOOSE  1   14  

  SORT ORDER BY 1   4 K 14  
TABLE ACCESS BY INDEX ROWID XDB_CHILD   1   4 K 4   

  INDEX RANGE SCAN  INDEX_XDB_CHILD_1   1   3   

SORT AGGREGATE  1   65  

  FIRST ROW 1   65  3   

INDEX RANGE SCAN (MIN/MAX)  INDEX_XDB_CHILD_2   8 M 
3

Regards

William


This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.

---(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] NOT HAVING clause?

2006-01-24 Thread Alban Hertroys

This is sort of a feature request, I suppose. I solved my problem, but 
"NOT HAVING" seems to match better with the desired result or the way 
you phrase the question in your mind, if that makes any sense...


I was hoping to write a query rather short by using a "NOT HAVING" 
clause. The documentation didn't specify that, and trying it resulted in 
a syntax error indeed...


My data consists of a series of images related to an object. There 
should be at least one image per object with sort_order = 1. I want to 
find all objects that don't match this criterium.


I have these tables (clipped a bit):
CREATE TABLE image (
image_idSERIAL  PRIMARY KEY,
object_id   INTEGER NOT NULL REFERENCES object MATCH FULL,
sort_order  SMALLINT NOT NULL DEFAULT 1
);

CREATE TABLE object (
object_id   SERIAL PRIMARY KEY,
nameTEXT NOT NULL
);

This is what I want, but isn't a valid query:

SELECT object_id
  FROM image
 GROUP BY object_id
 NOT HAVING sort_order = 1;

It is wonderfully short, one of the reasons I like this.

I could write this as:

SELECT object_id
  FROM object
 WHERE NOT EXISTS (
SELECT object_id
  FROM image
 WHERE sort_order = 1
   AND object_id = object.object_id
  );

Though this does give the right results, I would have liked to be able 
to use NOT HAVING. Or is there a way using HAVING that would give the 
same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the 
same thing.


What is the general opinion on this from the developers? Did I just have 
one of those wild and ridiculous ideas? :P


Regards,

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

//Showing your Vision to the World//

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

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

Re: [GENERAL] NOT HAVING clause?

2006-01-24 Thread Michael Glaesemann



On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:

Though this does give the right results, I would have liked to be  
able to use NOT HAVING. Or is there a way using HAVING that would  
give the same results? I'm quite sure HAVING sort_order <> 1  
doesn't mean the same thing.


Why are you so sure? It seems to me that NOT HAVING sort_order = 1   
and HAVING sort_order <> 1 would mean semantically the same thing.  
Can you show that HAVING sort_order <> 1 gives incorrect results?


Michael Glaesemann
grzm myrealbox com




---(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] NOT HAVING clause?

2006-01-24 Thread Pandurangan R S

Hi,

SELECT object_id
  FROM image
 GROUP BY object_id
 NOT HAVING sort_order = 1;

After changing the "NOT HAVING" to "HAVING" the error message was
"column "sort_order" must appear in the GROUP BY clause or be used in
an aggregate function"

The postgres document says "SELECT list and HAVING clause can only
reference table columns from within aggregate functions"

IMHO, I feel that it is not just postgres which cant do, but no other
database because i dont see any meaningful way of fetching rows for
the query given above.

On 1/24/06, Alban Hertroys <[EMAIL PROTECTED]> wrote:
> This is sort of a feature request, I suppose. I solved my problem, but
> "NOT HAVING" seems to match better with the desired result or the way
> you phrase the question in your mind, if that makes any sense...
>
> I was hoping to write a query rather short by using a "NOT HAVING"
> clause. The documentation didn't specify that, and trying it resulted in
> a syntax error indeed...
>
> My data consists of a series of images related to an object. There
> should be at least one image per object with sort_order = 1. I want to
> find all objects that don't match this criterium.
>
> I have these tables (clipped a bit):
> CREATE TABLE image (
> image_idSERIAL  PRIMARY KEY,
> object_id   INTEGER NOT NULL REFERENCES object MATCH FULL,
> sort_order  SMALLINT NOT NULL DEFAULT 1
> );
>
> CREATE TABLE object (
> object_id   SERIAL PRIMARY KEY,
> nameTEXT NOT NULL
> );
>
> This is what I want, but isn't a valid query:
>
> SELECT object_id
>FROM image
>   GROUP BY object_id
>   NOT HAVING sort_order = 1;
>
> It is wonderfully short, one of the reasons I like this.
>
> I could write this as:
>
> SELECT object_id
>FROM object
>   WHERE NOT EXISTS (
> SELECT object_id
>   FROM image
>  WHERE sort_order = 1
>AND object_id = object.object_id
>);
>
> Though this does give the right results, I would have liked to be able
> to use NOT HAVING. Or is there a way using HAVING that would give the
> same results? I'm quite sure HAVING sort_order <> 1 doesn't mean the
> same thing.
>
> What is the general opinion on this from the developers? Did I just have
> one of those wild and ridiculous ideas? :P
>
> Regards,
>
> --
> Alban Hertroys
> [EMAIL PROTECTED]
>
> magproductions b.v.
>
> T: ++31(0)534346874
> F: ++31(0)534346876
> M:
> I: www.magproductions.nl
> A: Postbus 416
> 7500 AK Enschede
>
> //Showing your Vision to the World//
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>

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

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

Re: [GENERAL] NOT HAVING clause?

2006-01-24 Thread Alban Hertroys


Michael Glaesemann wrote:


On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:

Though this does give the right results, I would have liked to be  
able to use NOT HAVING. Or is there a way using HAVING that would  
give the same results? I'm quite sure HAVING sort_order <> 1  doesn't 
mean the same thing.


Why are you so sure? It seems to me that NOT HAVING sort_order = 1   and 
HAVING sort_order <> 1 would mean semantically the same thing.  Can you 
show that HAVING sort_order <> 1 gives incorrect results?


There's a difference in meaning. By NOT HAVING sort_order = 1 I mean 
there is no record in the grouped records that has sort_order = 1. In 
contrast HAVING sort_order <> 1 means there is a record in the group 
with a sort_order other than 1, even if there's also a sort_order = 1 in 
the grouped records.


To illustrate, say we have sort_orders 1,2,3,4,5:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result true

If we'd have 2,3,4,5:
- NOT HAVING sort_order = 1 would result true
- HAVING sort_order <> 1 would result true

If we'd have 1 only:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result false

But it seems HAVING can't be applied to columns not in the group by or 
an aggregate. No idea why that might be...


--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

//Showing your Vision to the World//

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

[GENERAL] Problems with pgsql 8.0.4 & freebsd 6: partially solved

2006-01-24 Thread Vittorio

I have been using a postgresql 8.0.4 server under freebsd 5.4 like a 
charm for a long time .
Yesterday, I upgraded freebsd from 5.4 to 6 via 
the CD (the iso was downloaded from ftp.freebsd.org).

Restating 
postgresql it happened:

# /usr/local/etc/rc.d/010.pgsql.sh start
--: 
not found
FATAL:  XX000: failed to initialize lc_messages to ""
LOCATION:  InitializeGUCOptions, guc.c:3699
#

And the server was down

After many attempts I discovered that the problem was connected to the
"setenv LC_ALL it_IT.ISO_8859-1" I had introduced in /usr/local/pgsql/.
cshrc
which corrupted something in the system and, even removing the 
line, 010.pgsql.sh went on failing to start the server

Having 
pg_dumped the data before the upgrading to 6, I have solved the problem 
by
1) eliminating the /usr/local/pgsql/data directory (BY THE WAY, 
remember to backup the pgsql *.conf files somewhere!!);
2) deinstalling 
the postgresql 8.0.4 server by means of "make deinstall";
3) removing 
the pgsql user;
4) reinstalling the postgresql 8.0.4 server;
5) copying 
the *.conf files under the data directory
6) pg_restoring the data

Now, of course I have left unmodified the .cshrc file (take notice that 
any attempt of modyfying the locale corrupts the postgresql server and 
you should start with a new reinstallation! I failed both adding a line 
"setenv LC_ALL it_IT.ISO_8859-1" and "setenv LC_ALL it_IT.ISO8859-1")

Now it all works even though an inoffensive warning comes up

# 
/usr/local/etc/rc.d/010.pgsql.sh start
--: not found

Is there anything 
else I could do?

Ciao
Vittorio

  



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

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

Re: [GENERAL] NOT HAVING clause?

Alban,

what you want is to put the "sort_order <> 1" in the WHERE clause, not
in the HAVING clause. Then it will do what you want.

Cheers,
Csaba.


On Tue, 2006-01-24 at 13:51, Alban Hertroys wrote:
> Michael Glaesemann wrote:
> > 
> > On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:
> > 
> >> Though this does give the right results, I would have liked to be  
> >> able to use NOT HAVING. Or is there a way using HAVING that would  
> >> give the same results? I'm quite sure HAVING sort_order <> 1  doesn't 
> >> mean the same thing.
> > 
> > Why are you so sure? It seems to me that NOT HAVING sort_order = 1   and 
> > HAVING sort_order <> 1 would mean semantically the same thing.  Can you 
> > show that HAVING sort_order <> 1 gives incorrect results?
> 
> There's a difference in meaning. By NOT HAVING sort_order = 1 I mean 
> there is no record in the grouped records that has sort_order = 1. In 
> contrast HAVING sort_order <> 1 means there is a record in the group 
> with a sort_order other than 1, even if there's also a sort_order = 1 in 
> the grouped records.
> 
> To illustrate, say we have sort_orders 1,2,3,4,5:
> - NOT HAVING sort_order = 1 would result false
> - HAVING sort_order <> 1 would result true
> 
> If we'd have 2,3,4,5:
> - NOT HAVING sort_order = 1 would result true
> - HAVING sort_order <> 1 would result true
> 
> If we'd have 1 only:
> - NOT HAVING sort_order = 1 would result false
> - HAVING sort_order <> 1 would result false
> 
> But it seems HAVING can't be applied to columns not in the group by or 
> an aggregate. No idea why that might be...


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

Re: [GENERAL] NOT HAVING clause?


Alban Hertroys wrote:

Michael Glaesemann wrote:


On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:

Though this does give the right results, I would have liked to be  
able to use NOT HAVING. Or is there a way using HAVING that would  
give the same results? I'm quite sure HAVING sort_order <> 1  doesn't 
mean the same thing.


Why are you so sure? It seems to me that NOT HAVING sort_order = 1   
and HAVING sort_order <> 1 would mean semantically the same thing.  
Can you show that HAVING sort_order <> 1 gives incorrect results?


There's a difference in meaning. By NOT HAVING sort_order = 1 I mean 
there is no record in the grouped records that has sort_order = 1. In 
contrast HAVING sort_order <> 1 means there is a record in the group 
with a sort_order other than 1, even if there's also a sort_order = 1 in 
the grouped records.


No, you're wrong in both cases there (or would be if NOT HAVING was legal).

You're mixing up WHERE and HAVING. The WHERE clause applies to the 
individual rows before GROUP BY. The HAVING applies to the output of the 
GROUP BY stage.


So, you can refer to HAVING MAX(sort_order) > 10 for example, but not 
HAVING sort_order of anything (because you don't group by it or apply an 
aggregate function to it).


But it seems HAVING can't be applied to columns not in the group by or 
an aggregate. No idea why that might be...


See above. You're not the only person to be confused by HAVING. I'd have 
left it out altogether and relied on doing the aggregation in a 
sub-query and applying another WHERE to its output.


--
  Richard Huxton
  Archonet Ltd

---(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] NOT HAVING clause?

2006-01-24 Thread Alban Hertroys


Richard Huxton wrote:

Alban Hertroys wrote:
You're mixing up WHERE and HAVING. The WHERE clause applies to the 
individual rows before GROUP BY. The HAVING applies to the output of the 
GROUP BY stage.


Ah, of course, now it makes sense. Combined with Csaba's reply my 
original problem has vaporized. Thank you guys :)


--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

//Showing your Vision to the World//

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

  http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?


Dave Page wrote:


There most certainly are companies making money from pgAdmin, including
at least 2 of the most well known large PostgreSQL companies. In
addition to potentially hurting them, you are trading off our long
established name, which to add insult to injury you haven't even spelt
or formatted correctly (it is, and has always been pgAdmin)!


Hi Dave,
Those companies simply bundle pgAdmin III, they don't sell it, there is 
a big difference.  I know for a fact the ones you are talking about and 
the version they bundle have no changes over the stock one at all.


I don't think mentioning a product as a alternative to pgAdmin III is 
wrong since pgAdmin III has such a big advantage being distributed with 
the Windows version of Postgresql.  It almost has monopoly written on it 
since the user is not given any idea that there is anything else 
available. At least you are not forced to install it, but still a 
HUGE,HUGE advantage over any other competing product.


pgAdmin does not play fair either, if you want to talk fair maybe a link 
should be placed in the windows pgAdmin installer informing users of 
other comercially available options.


Thanks for the heads up on the spelling, I was not aware it was spelled 
like that.



Later,

Tony







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

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

  http://archives.postgresql.org

Re: [GENERAL] NOT HAVING clause?

2006-01-24 Thread Will Glynn


Alban Hertroys wrote:


Michael Glaesemann wrote:



On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:

Though this does give the right results, I would have liked to be  
able to use NOT HAVING. Or is there a way using HAVING that would  
give the same results? I'm quite sure HAVING sort_order <> 1  
doesn't mean the same thing.



Why are you so sure? It seems to me that NOT HAVING sort_order = 1   
and HAVING sort_order <> 1 would mean semantically the same thing.  
Can you show that HAVING sort_order <> 1 gives incorrect results?



There's a difference in meaning. By NOT HAVING sort_order = 1 I mean 
there is no record in the grouped records that has sort_order = 1. In 
contrast HAVING sort_order <> 1 means there is a record in the group 
with a sort_order other than 1, even if there's also a sort_order = 1 
in the grouped records.


To illustrate, say we have sort_orders 1,2,3,4,5:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result true

If we'd have 2,3,4,5:
- NOT HAVING sort_order = 1 would result true
- HAVING sort_order <> 1 would result true

If we'd have 1 only:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result false



You might try:

SELECT some_column
 FROM some_table
 GROUP BY some_column
 HAVING SUM(CASE WHEN sort_order=1 THEN 1 ELSE 0 END) = 0;

That is, "get me values for some_column from some_table; grouping by 
some_column, include only groups where the number of grouped records 
having sort_order=1 is zero."


--Will Glynn
Freedom Healthcare

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

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

Re: [GENERAL] NOT HAVING clause?

2006-01-24 Thread Will Glynn


Alban Hertroys wrote:


Richard Huxton wrote:


Alban Hertroys wrote:
You're mixing up WHERE and HAVING. The WHERE clause applies to the 
individual rows before GROUP BY. The HAVING applies to the output of 
the GROUP BY stage.



Ah, of course, now it makes sense. Combined with Csaba's reply my 
original problem has vaporized. Thank you guys :)



Csaba's response is incorrect:


Alban,

what you want is to put the "sort_order <> 1" in the WHERE clause, not
in the HAVING clause. Then it will do what you want.

Cheers,
Csaba.

If you do that, the query reads "give me unique values for some_column 
from some_table, ignoring individual records that have sort_order=1".


To illustrate, say we have sort_orders 2,3,4,5:
- NOT HAVING sort_order = 1 would result true
- HAVING sort_order <> 1 would result true
- WHERE sort_order <> 1 would result true for all records

If we'd have 1 only:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result false
- WHERE sort_order <> 1 would result false

If we'd have 1,2,3,4,5:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result true
- WHERE sort_order <> 1 would result true for records 2,3,4,5, returning 
some_column anyway, which is not what you want


This can be done with an aggregate, a sub-select, or a JOIN -- there's 
no way to do this using only a single-table WHERE.


--Will Glynn
Freedom Healthcare

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

Re: [GENERAL] NOT HAVING clause?

You're right, but only if there's no GROUP BY. As soon as you use a
GROUP BY _and_ the mentioned WHERE clause, the result will be what the
OP wanted... or you could use SELECT DISTINCT for what he wanted.

On Tue, 2006-01-24 at 15:02, Will Glynn wrote:
> Alban Hertroys wrote:
> 
> > Richard Huxton wrote:
> >
> >> Alban Hertroys wrote:
> >> You're mixing up WHERE and HAVING. The WHERE clause applies to the 
> >> individual rows before GROUP BY. The HAVING applies to the output of 
> >> the GROUP BY stage.
> >
> >
> > Ah, of course, now it makes sense. Combined with Csaba's reply my 
> > original problem has vaporized. Thank you guys :)
> 
> 
> Csaba's response is incorrect:
> 
> >Alban,
> >
> >what you want is to put the "sort_order <> 1" in the WHERE clause, not
> >in the HAVING clause. Then it will do what you want.
> >
> >Cheers,
> >Csaba.
> >
> If you do that, the query reads "give me unique values for some_column 
> from some_table, ignoring individual records that have sort_order=1".
> 
> To illustrate, say we have sort_orders 2,3,4,5:
> - NOT HAVING sort_order = 1 would result true
> - HAVING sort_order <> 1 would result true
> - WHERE sort_order <> 1 would result true for all records
> 
> If we'd have 1 only:
> - NOT HAVING sort_order = 1 would result false
> - HAVING sort_order <> 1 would result false
> - WHERE sort_order <> 1 would result false
> 
> If we'd have 1,2,3,4,5:
> - NOT HAVING sort_order = 1 would result false
> - HAVING sort_order <> 1 would result true
> - WHERE sort_order <> 1 would result true for records 2,3,4,5, returning 
> some_column anyway, which is not what you want
> 
> This can be done with an aggregate, a sub-select, or a JOIN -- there's 
> no way to do this using only a single-table WHERE.
> 
> --Will Glynn
> Freedom Healthcare
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend


---(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] NOT HAVING clause?

OK, I really didn't think it through, GROUP BY or DISTINCT won't help
here. Sorry for the noise.

Cheers,
Csaba.

On Tue, 2006-01-24 at 15:11, Csaba Nagy wrote:
> You're right, but only if there's no GROUP BY. As soon as you use a
> GROUP BY _and_ the mentioned WHERE clause, the result will be what the
> OP wanted... or you could use SELECT DISTINCT for what he wanted.
> 
> On Tue, 2006-01-24 at 15:02, Will Glynn wrote:
> > Alban Hertroys wrote:
> > 
> > > Richard Huxton wrote:
> > >
> > >> Alban Hertroys wrote:
> > >> You're mixing up WHERE and HAVING. The WHERE clause applies to the 
> > >> individual rows before GROUP BY. The HAVING applies to the output of 
> > >> the GROUP BY stage.
> > >
> > >
> > > Ah, of course, now it makes sense. Combined with Csaba's reply my 
> > > original problem has vaporized. Thank you guys :)
> > 
> > 
> > Csaba's response is incorrect:
> > 
> > >Alban,
> > >
> > >what you want is to put the "sort_order <> 1" in the WHERE clause, not
> > >in the HAVING clause. Then it will do what you want.
> > >
> > >Cheers,
> > >Csaba.
> > >
> > If you do that, the query reads "give me unique values for some_column 
> > from some_table, ignoring individual records that have sort_order=1".
> > 
> > To illustrate, say we have sort_orders 2,3,4,5:
> > - NOT HAVING sort_order = 1 would result true
> > - HAVING sort_order <> 1 would result true
> > - WHERE sort_order <> 1 would result true for all records
> > 
> > If we'd have 1 only:
> > - NOT HAVING sort_order = 1 would result false
> > - HAVING sort_order <> 1 would result false
> > - WHERE sort_order <> 1 would result false
> > 
> > If we'd have 1,2,3,4,5:
> > - NOT HAVING sort_order = 1 would result false
> > - HAVING sort_order <> 1 would result true
> > - WHERE sort_order <> 1 would result true for records 2,3,4,5, returning 
> > some_column anyway, which is not what you want
> > 
> > This can be done with an aggregate, a sub-select, or a JOIN -- there's 
> > no way to do this using only a single-table WHERE.
> > 
> > --Will Glynn
> > Freedom Healthcare
> > 
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
> 
> 
> ---(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


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

   http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?

 

> -Original Message-
> From: Tony Caduto [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 13:56
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Does this look ethical to you?
> 
> Those companies simply bundle pgAdmin III, they don't sell 
> it, there is 
> a big difference.  I know for a fact the ones you are talking 
> about and 
> the version they bundle have no changes over the stock one at all.

One of the two has extensively modified it. I don't know about the
other.

Whether or not the sell it seperately is not the point though - they do
sell it as part of the product suite they offer.

> I don't think mentioning a product as a alternative to pgAdmin III is 
> wrong since pgAdmin III has such a big advantage being 
> distributed with 
> the Windows version of Postgresql.  It almost has monopoly 
> written on it 
> since the user is not given any idea that there is anything else 
> available. At least you are not forced to install it, but still a 
> HUGE,HUGE advantage over any other competing product.

It's open source and is certainly the best option a present to bundle
with the installer. If you want to open source PG Lightning Admin, and
add the missing features that pgAdmin has such as Slony support, job
scheduling, graphical explain and UI translations into a number of
different languages, then I'm sure Magnus and I would be happy to look
at including it.

> pgAdmin does not play fair either, if you want to talk fair 
> maybe a link 
> should be placed in the windows pgAdmin installer informing users of 
> other comercially available options.

By that token, every Linux distribution would have to tell users about
MS Windows whilst it was installing. Does the fact that they don't mean
they aren't playing fair? Should the PostgreSQL distributions also
mention Oracle or DB2?

Regards, Dave

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

Re: [GENERAL] Does this look ethical to you?

2006-01-24 Thread Magnus Hagander

> > There most certainly are companies making money from pgAdmin, 
> > including at least 2 of the most well known large PostgreSQL 
> > companies. In addition to potentially hurting them, you are trading 
> > off our long established name, which to add insult to injury you 
> > haven't even spelt or formatted correctly (it is, and has 
> always been pgAdmin)!
> 
> Hi Dave,
> Those companies simply bundle pgAdmin III, they don't sell 
> it, there is a big difference.  I know for a fact the ones 
> you are talking about and the version they bundle have no 
> changes over the stock one at all.

Then your facts are wrong. At least one of the ones Dave is talking
about has done extensive modifications for it. And sell it as part of a
commercial product. (I would assume that's what he's talking about. If
not, this is a different company, but it's still there)


> I don't think mentioning a product as a alternative to 
> pgAdmin III is wrong since pgAdmin III has such a big 
> advantage being distributed with the Windows version of 
> Postgresql.  It almost has monopoly written on it since the 
> user is not given any idea that there is anything else 
> available. At least you are not forced to install it, but 
> still a HUGE,HUGE advantage over any other competing product.

We (pginstaller hat goes on) don't know of any competing products. We
will be happy to consider bundling any competing product, including PG
Lightning Admin. One of the most important things in order to be
distributed as part of an open source product is that the parts are open
source. If PGLA (or a lite version if necessary) is available under an
OSS license, we'll definitly consider bundling it.
(We have considered bundling phpPgAdmin, but haven't found a good way to
do it without dragging in a huge load of dependencies)

If that's not acceptable, there is nothing preventing you from
delivering a "Lightning PostgreSQL". All the parts that are in the
installer are open source, it's just a matter of making the modification
to put in pg lightning admin there. You might need to do something about
the GPL stuff, don't know for sure. The BSD parts definitly permit it.

But to be clear, I don't think it's wrong to say that PGLA is an
alternative to pgAdmin. Because that's what it is. It's just  a matter
of *how* it's done.


> pgAdmin does not play fair either, if you want to talk fair 
> maybe a link should be placed in the windows pgAdmin 
> installer informing users of other comercially available options.

You're kidding, right? Should we also mention in the PostgreSQL
installer that you should perhaps look at Oracle or DB2? And I don't see
most Linux distributions informing the users that Windows might be an
alternative.

We *do* put up links to commercial management software on the website
(PostgreSQL website, because that's where it applies), and we post news
about it. IMHO, that's about as far as "the project" should go when it
comes to "free marketing for commercial software".


//Magnus

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

Re: [GENERAL] Does this look ethical to you?




There most certainly are companies making money from pgAdmin, including
at least 2 of the most well known large PostgreSQL companies. In
addition to potentially hurting them, you are trading off our long
established name, which to add insult to injury you haven't even spelt
or formatted correctly (it is, and has always been pgAdmin)!


here is my keywords:

Postgresql,pg,admin,GUI,alternative,PSQL,pgsql,gui,postgres

I removed the III.

Not that it would ever have hurt pgAdmin III, but to show good faith I 
changed them in my shareware pad file and on my homepage.


No how about getting a link or something in the win32 Postgresql 
installer back to the commercial products page:

http://www.postgresql.org/download/commercial

Thanks,


--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
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] Does this look ethical to you?

 

> -Original Message-
> From: Magnus Hagander [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 14:22
> To: Tony Caduto; Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Does this look ethical to you?
> 
> 
> > pgAdmin does not play fair either, if you want to talk fair 
> > maybe a link should be placed in the windows pgAdmin 
> > installer informing users of other comercially available options.
> 
> You're kidding, right? Should we also mention in the PostgreSQL
> installer that you should perhaps look at Oracle or DB2? And 
> I don't see
> most Linux distributions informing the users that Windows might be an
> alternative.

Deja-vu...

> We *do* put up links to commercial management software on the website
> (PostgreSQL website, because that's where it applies), and we 
> post news
> about it. IMHO, that's about as far as "the project" should go when it
> comes to "free marketing for commercial software".

Agreed. And as I'm sure Tony will recall, not only do we offer that
service, it was actually me that created the page on the site originally
for Lightning Admin which was the first product listed!

Regards, Dave.

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

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

[GENERAL] PostgreSQL best practices?

2006-01-24 Thread Leonard Soetedjo

Hi,

Is there any documentation or wiki on PostgreSQL best practices?  For example:

- run vacuum regularly and how to judge the frequency of running vacuum
- when to use rule or trigger
- etc.

What I realized is that I joined PostgreSQL's mailing list and kept some of 
the advise that I feel might come in handy in my email client.  However, 
sometimes it is very tedious search through my archived emails and read 
through all the emails in the thread.  You can call me lazy, but I think it's 
good to have a set of best practices in using PostgreSQL, especially for the 
beginners (unless of course I don't know that there is an existing ones).

Thanks :)


Regards,

Leonard Soetedjo

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

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

Re: [GENERAL] Does this look ethical to you?

2006-01-24 Thread Devrim GUNDUZ

Hi,

On Tue, 2006-01-24 at 08:24 -0600, Tony Caduto wrote:
> No how about getting a link or something in the win32 Postgresql 
> installer back to the commercial products page:
> http://www.postgresql.org/download/commercial

Are you kidding?

-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



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

   http://archives.postgresql.org

Re: [GENERAL] Initdb panic: invalid record offset at 0/0 creating template1]

Agnes Bocchino <[EMAIL PROTECTED]> writes:
> Do you have some explanation or/and tips on how to build a successfull
> rpm on ia64, with icc,
> perhaps we shoud not please tell us

Perhaps you should use gcc?

I don't personally have the time or interest to dig into this.
Considering that PG works fine on several other 64-bit platforms,
it seems unlikely (though not impossible of course) that this is our
bug.

What could be happening is that the RPM packaging involves tools that
aren't compatible with icc.  I think you already found that out with
regard to brp-strip, and there may be some more-subtle problems too.
You might try forgetting about RPM entirely and just building from
the source tarball with "configure CC=icc" plus whatever other options
you want.

regards, tom lane

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

Re: [GENERAL] Does this look ethical to you?

 

> -Original Message-
> From: Tony Caduto [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 14:25
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Does this look ethical to you?
> 
> 
> > There most certainly are companies making money from 
> pgAdmin, including
> > at least 2 of the most well known large PostgreSQL companies. In
> > addition to potentially hurting them, you are trading off our long
> > established name, which to add insult to injury you haven't 
> even spelt
> > or formatted correctly (it is, and has always been pgAdmin)!
> 
> here is my keywords:
> 
> Postgresql,pg,admin,GUI,alternative,PSQL,pgsql,gui,postgres
> 
> I removed the III.

Thank you, however I'm more concerned with:

"PGLA has many advanced features not found in pgAdmin III,".

Aside from it being slightly misleading (not only are there not many
'advanced' things PGLA can do that pgAdmin can't, there are a similar
number that pgAdmin can, that PGLA can't), it is still attempting to
sell your product on our name. I would therefore ask that you either:

- Not mention pgAdmin at all, or
- Mention not only pgAdmin, but EDB Studio, Navicat, EMS PostgreSQL
Manager and other comparable products *as well*.

> No how about getting a link or something in the win32 Postgresql 
> installer back to the commercial products page:
> http://www.postgresql.org/download/commercial

Why? It is an Open Source package, distributed for free, at the personal
expense of numerous people including myself. Why should we advertise
your or anyone elses commercial products for free?

Regards, Dave

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

Re: [GENERAL] FATAL: terminating connection due to administrator command

Richard Huxton  writes:
> surabhi.ahuja wrote:
>> so does this mean that someone is trying to stop postmaster by
>> sending it a kill signal?

> Someone or something. It can be Linux's out-of-memory facility picking 
> processes to kill. Google "oom killer" for discussion.

No, because the OOM killer invariably uses "kill -9".  "Fast shutdown"
means that something sent the postmaster a SIGINT.

If you launch the postmaster manually and are not careful to make it
dissociate from your terminal, then typing ^C at some unrelated program
later would be enough to make this happen ...

>> 1. many times i have seen two instances of postmaster running. how
>> does that happen and how to prevent it from happening?

> Shouldn't (unless you have two installations of course).

Perhaps he's not understanding the difference between the postmaster and
its child processes?  I don't believe he's actually got two postmasters
running (unless maybe in separate directories with separate ports, which
is hardly likely to be a setup one would create by accident).  There are
*very* extensive safety interlocks in place to prevent that.

regards, tom lane

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

Re: [GENERAL] Does this look ethical to you?




You're kidding, right? Should we also mention in the PostgreSQL
installer that you should perhaps look at Oracle or DB2?



No I am not kidding, it's more akin to MS bundling a web browser and a 
media player. That would be a more appropiate example.  MS does it to 
destroy competitors. I understand that it is done with pgAdmin as a 
convenience thing, but it has the side effect of hurting the 3rd party 
market.  A couple of years ago there was no 3rd party admin tools for 
Postgresql, pgAdmin was all that was available, but that has now changed 
and that change should be looked at.


The fact of the matter is pgAdmin III is bundled in a manner that does 
not let the user know there are alternatives available plain and simple.


Because of the way pgAdmin is bundled it is effectivly destroying the 
third party admin market which should be a good thing for Postgresql not

viewed as a bad thing.

Look at Mysql, they never had a bundled admin tool until recently and it 
 is the most popular psuedo open source

DB on the market.

People are always going to use pgAdmin because it is free, you will 
never have a problem getting users, but it should not be at the expense 
of the third party market.


I am just raising a valid concern, no need for flames.


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

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

[GENERAL] user defined function

I want to implement a UDF that can accept a parameter which is a tuple of any table, and returns the number of NULL attributes in this tuple. Different tables may have different schemas. How can I implement this function?  Thanks.
andrew

Re: [GENERAL] Does this look ethical to you?

 

> -Original Message-
> From: Tony Caduto [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 14:57
> To: Dave Page
> Cc: Magnus Hagander; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Does this look ethical to you?
> 
> 
> No I am not kidding, it's more akin to MS bundling a web 
> browser and a 
> media player. That would be a more appropiate example.  MS does it to 
> destroy competitors. 

People don't usually complain about their inclusion - they tend to
complain about not being able to get rid of them. And most people that I
know well actually would rather they were there to save the hassle of
getting something else.

pgAdmin is easily removed, or may not be installed in the first place if
you prefer.

> I understand that it is done with pgAdmin as a 
> convenience thing, but it has the side effect of hurting the 
> 3rd party 
> market.  A couple of years ago there was no 3rd party admin tools for 
> Postgresql, pgAdmin was all that was available, but that has 
> now changed 
> and that change should be looked at.

What's that got to do with including a link in the pgAdmin installer
which is what you asked for?

> The fact of the matter is pgAdmin III is bundled in a manner 
> that does 
> not let the user know there are alternatives available plain 
> and simple.
>
> Look at Mysql, they never had a bundled admin tool until 
> recently and it 
>   is the most popular psuedo open source
> DB on the market.

MySQL are irrelevant.

More relevant are the commercial databases that are comparable in
functionality to PostgreSQL, such as Oracle, DB2 and SQL Server. All of
which bundle admin tools, and none of which advertise third party tools
to my knowledge, or have ever been accused of unfair practices by doing
so.

As I said before, if you want to produce an OSS version of PGLA, then we
will happily consider including it in the installer.

We *will not* be advertising or bundling commercial products in
pgInstaller.

> People are always going to use pgAdmin because it is free, you will 
> never have a problem getting users, but it should not be at 
> the expense 
> of the third party market.

Maybe you should contact Marc about advertising on postgresql.org, or
consider Google Ads. Either will get your far wider exposure on the
PostgreSQL website that is almost certainly visited by the vast majority
of PostgreSQL users.

> I am just raising a valid concern, no need for flames.

I don't flame, I blacklist.

Regards, Dave

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

Re: [GENERAL] Does this look ethical to you?


Thank you, however I'm more concerned with:

"PGLA has many advanced features not found in pgAdmin III,".

Aside from it being slightly misleading (not only are there not many
'advanced' things PGLA can do that pgAdmin can't, there are a similar
number that pgAdmin can, that PGLA can't), it is still attempting to
sell your product on our name. I would therefore ask that you either:

- Not mention pgAdmin at all, or
- Mention not only pgAdmin, but EDB Studio, Navicat, EMS PostgreSQL
Manager and other comparable products *as well*.


I changed it to say PGLA has many advanced GUI features not found in 
other admin tools.


I play fair Dave, and expect the same from you.



Because the installer is not letting it be known that there are 
alternatives available, I have had many people tell me they had no idea 
there where was anything else available.


It does not matter that pgAdmin is open source, and letting users know 
about alternatives is not free advertising, free advertising would be 
you paying for my Google addwords account.


You guys are doing the same thing as Microsoft did with Internet 
Explorer, let's include it so our browser/admin tool is all the user 
knows about or sees when they install the OS, or in this case the SQL 
server.


A link or blurb should be mentioned that there are other admin tools 
available or pgAdmin should not be installed either.


I am not saying you put specifics about mine or anyone elses product 
commercial or open source, but I think links back to the commercial and 
open source pages on the postgresql site would be a fair thing to do.


You are not letting the user make a choice about which admin tool to use 
or even try...


Just my opinion on the whole fair competition thing,

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

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

Re: [GENERAL] Does this look ethical to you?

 

> -Original Message-
> From: Tony Caduto [mailto:[EMAIL PROTECTED] 
> Sent: 24 January 2006 15:24
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Does this look ethical to you?
> 
> I changed it to say PGLA has many advanced GUI features not found in 
> other admin tools.

Thank you.

Regards, Dave

---(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] user defined function

Yl Zhou <[EMAIL PROTECTED]> writes:
> I want to implement a UDF that can accept a parameter which is a tuple of
> any table, and returns the number of NULL attributes in this tuple.
> Different tables may have different schemas. How can I implement this
> function?  Thanks.

You could do that in C, but none of the available PLs support it.

regards, tom lane

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

[GENERAL]

2006-01-24 Thread Jimmy Rowe


UNSUBSCRIBE


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

Re: [GENERAL] NOT HAVING clause?

2006-01-24 Thread Stephan Szabo

On Tue, 24 Jan 2006, Alban Hertroys wrote:

> This is sort of a feature request, I suppose. I solved my problem, but
> "NOT HAVING" seems to match better with the desired result or the way
> you phrase the question in your mind, if that makes any sense...

One problem is that HAVING really works on entire groups at a time
(including aggregated data for the group) not on pieces of the group.

However, I think one might be able to fake it with an array accumulating
aggregate like the one from
http://www.postgresql.org/docs/current/static/xaggr.html

and a query like:

SELECT object_id FROM image GROUP BY object_id HAVING
NOT(1 = ANY(array_accum(sort_order))).

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

   http://archives.postgresql.org

Re: [GENERAL] Problems with pgsql 8.0.4 & freebsd 6: partially solved

Vittorio <[EMAIL PROTECTED]> writes:
> FATAL:  XX000: failed to initialize lc_messages to ""
> LOCATION:  InitializeGUCOptions, guc.c:3699

We've seen that reported before, on more than one platform (I recall
hearing about it on OS X, for instance).  But no one's ever been able
to explain what causes it.  It's interesting to hear that a full
database reinstall made it go away for you, but that was certainly
overkill.  Do you have the time to experiment with the locale settings
and see if you can identify the difference between the cases where it
happens and those where it doesn't?

> After many attempts I discovered that the problem was connected to the
> "setenv LC_ALL it_IT.ISO_8859-1" I had introduced in /usr/local/pgsql/.cshrc

Do you have any other LC_xxx variables set?  How about LANG?  Is that
string still a valid locale name on your updated platform?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] user defined function

2006-01-24 Thread Gevik

check

http://archives.postgresql.org/pgsql-novice/2005-02/msg00227.php


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

   http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?

2006-01-24 Thread Devrim GUNDUZ

Hi,

On Tue, 2006-01-24 at 07:56 -0600, Tony Caduto wrote:
> I don't think mentioning a product as a alternative to pgAdmin III is 
> wrong since pgAdmin III has such a big advantage being distributed
> with 
> the Windows version of Postgresql.  It almost has monopoly written on
> it 
> since the user is not given any idea that there is anything else 
> available. At least you are not forced to install it, but still a 
> HUGE,HUGE advantage over any other competing product.

IMHO pgAdmin does not compete with other "products", since it is a
community-driven GUI for PostgreSQL. Look what you wrote yesterday:

On Mon, 2006-01-23 at 20:52 -0600, Tony Caduto wrote:
> I am kind of ticked off that they are hijacking my product name this
> way.

PGLA is *your* *product*, but pgAdmin is community software. As stated
before, I think many people will be glad if you distribute PGLA (or a
part of it) as OSS. 

So pgAdmin is apart from many other GUIs since it is OSS and has
excellent features like Slony, GE, etc. It deserves to be distributed
with Windows Installer, unless another Open Source GUI which may beat
pgAdmin with its features appears on the stage. Also it is available on
Linux and some *nix systems, which I think is one of the best features.

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.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] RAID 5 and postgresql

2006-01-24 Thread Sander Steffann

Hi,

> How about software RAID?
> 
> Linux software RAID appears to perform better than most RAID 
> controllers except perhaps those that can do read interleaving
> for RAID1 (I believe some 3ware controllers can do it). Linux
> RAID mirroring doesn't do read interleaving, only read
> balancing, which may not be so good for a single sequential
> read, but pretty good for concurrent sequential reads - each 
> drive in a mirror set can handle one sequential read.

Don't forget the battery backed cache for write performance. And because the
controller doesn't know about the RAID array booting can become a problem
when your first drive breaks.

> I find many of these RAID controllers fail significantly more 
> than basic SCSI controllers (which hardly ever fail). And the
> support under Linux for such controllers can be a bit patchy
> sometimes - you want to be able to easily know if a drive has
> died.
>
> It just seems strange to pay a fair bit for something that 
> doesn't perform well and is less reliable.

The Dell OpenManage tools can help you with that, and if the controller
fails (never happened yet here) you can just call Dell support, and within 4
hours they bring you a new one. (I had some strange problems with a server,
and Dell replaced the mainboard and memory very quickly)

I still choose the Dell LSI-based PERC4/Di where possible.
- Sander


---(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] Does this look ethical to you?

2006-01-24 Thread Peter Wilson

Tony Caduto wrote:

Thank you, however I'm more concerned with:

"PGLA has many advanced features not found in pgAdmin III,".

Aside from it being slightly misleading (not only are there not many
'advanced' things PGLA can do that pgAdmin can't, there are a similar
number that pgAdmin can, that PGLA can't), it is still attempting to
sell your product on our name. I would therefore ask that you either:

- Not mention pgAdmin at all, or
- Mention not only pgAdmin, but EDB Studio, Navicat, EMS PostgreSQL
Manager and other comparable products *as well*.

I changed it to say PGLA has many advanced GUI features not found in
other admin tools.

I play fair Dave, and expect the same from you.

Because the installer is not letting it be known that there are
alternatives available, I have had many people tell me they had no idea
there where was anything else available.

It does not matter that pgAdmin is open source, and letting users know
about alternatives is not free advertising, free advertising would be
you paying for my Google addwords account.

You guys are doing the same thing as Microsoft did with Internet
Explorer, let's include it so our browser/admin tool is all the user
knows about or sees when they install the OS, or in this case the SQL
server.

A link or blurb should be mentioned that there are other admin tools
available or pgAdmin should not be installed either.

I am not saying you put specifics about mine or anyone elses product
commercial or open source, but I think links back to the commercial and
open source pages on the postgresql site would be a fair thing to do.

You are not letting the user make a choice about which admin tool to use
or even try...

Just my opinion on the whole fair competition thing,

I'm not sure I understand some of these arguments, and I don't know the
history, so as an uninformed third party who can't resist adding my
tupennyw'th...

a) pgAdmin happens to be an admin tool that undercuts other tools in terms of
price (free) and for some features

b) It has negotiated a distribution channel with partner organisations - something that any other organisation presumably is free to do. You just have
to have the right proposition (OSS) to entice that partner to work with you.

c) If you would like pgAdmin to mention that there are more expensive alternative products - would your product before completing a sale recommend
that people go take a look at pgAdmin first and see whether that might be a better alternative?

Just out of interest - which product came first? I've been aware of pgAdmin for a long time - if it was there first you'd have to look closely at
whether there was a commercial business case for trying to get into that market with a broadly similar product.

A valid business case would obviously include making sure there were suitable accessible channels to market and sufficient funds to finance those
channels. OSS projects don't often have a cash generating base to fund those channels so they are always at a disadvantage to commercial ventures.

Personally I'd be a little uneasy trying to build a commercial product that piggybacks on an OSS product simply because if it's something useful and
important, as opposed to niche, then someone will add an OSS version and, if they do their work properly, destroy my market.

Pete
--
http://www.whitebeam.org
http://www.yellowhawk.co.uk
-

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

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

Re: [GENERAL] user defined function


Tom Lane wrote:

Yl Zhou <[EMAIL PROTECTED]> writes:

I want to implement a UDF that can accept a parameter which is a tuple of
any table, and returns the number of NULL attributes in this tuple.
Different tables may have different schemas. How can I implement this
function?  Thanks.


You could do that in C, but none of the available PLs support it.


How would you define the signature for the function? One parameter of 
type anyelement?


--
  Richard Huxton
  Archonet Ltd

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

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

[GENERAL]

2006-01-24 Thread Rick Gigger

Every once in a while I've noticed the number of processes I've got  
running jumps up a little higher than normal.  So I check it out and  
realize that I'm building up a bunch of processes that just aren't  
completing.  About half of the one's not completing say "SELECT  
waiting".  I'm not doing anything that I am aware of that could be  
blocking anything.  I am only doing regular vacuums.  I am not  
explicitly locking anything that I know of.  Is there a way to figure  
out what each of these processes are waiting on?


---(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] Does this look ethical to you?


Magnus Hagander wrote:
 > We (pginstaller hat goes on) don't know of any competing products. We

will be happy to consider bundling any competing product, including PG
Lightning Admin. One of the most important things in order to be
distributed as part of an open source product is that the parts are open
source. If PGLA (or a lite version if necessary) is available under an
OSS license, we'll definitly consider bundling it.
(We have considered bundling phpPgAdmin, but haven't found a good way to
do it without dragging in a huge load of dependencies)


I don't wan't to be bundled, I just want it to be known that there are 
alternatives available.


Postgresql is free, so what is the big deal about letting users know 
about alternative admin tools comercial or open source?  It's not like 
it would put pgAdmin or Postgresql out of business(it could only help).


It's a shame you don't see how the bundling of pgAdmin(in the current 
way) is hurting the 3rd party community.


All that would be needed is a installer section at the end saying 
something like:
In addition to pgAdmin III there are other opensource and commercial 
admin products available, you can get more information here to postgresql home page>.


I don't see that as being detrimental to anyone and would certainly be 
in fair play.


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

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

Re: [GENERAL] Does this look ethical to you?

2006-01-24 Thread Magnus Hagander

>  the personal expense of numerous people including myself. Why 
> should we advertise your or anyone elses commercial products 
> for free?>
> 
> Because the installer is not letting it be known that there 
> are alternatives available, I have had many people tell me 
> they had no idea there where was anything else available.
> 
> It does not matter that pgAdmin is open source,

Yes, it does. That is *exactly* what matters.
If pgAdmin wasn't open source, it wouldn't be in there.


> and letting 
> users know about alternatives is not free advertising, free 
> advertising would be you paying for my Google addwords account.

There we definitly disagree. I would consider it free advertising. Not
as direct, but still.


> You guys are doing the same thing as Microsoft did with 
> Internet Explorer, let's include it so our browser/admin tool 
> is all the user knows about or sees when they install the OS, 
> or in this case the SQL server.

I don't think we do, and frankly, I resent that comment.

As an admin in a network that is a big user of Microsoft products, the
bundling of IE and Media Player is a *good thing to me as a customer*.
The same thing applies to me as an "end user at home". The thing that is
bad is the inability to replace them with something else, and the
inability to get rid of them if I want to. Neither of which applies to
pgAdmin - it's trivial to get rid of it.


> A link or blurb should be mentioned that there are other 
> admin tools available or pgAdmin should not be installed either.

I'd find it reasonable to add a blurb somewhere about "note that there
are other tools and addons available. See for example
http://www.postgresql.org/download"; or something like that. That
certainly provides some value to the end user, and there are also
several other OSS projects that would like that exposure. (Exact wording
is of course up for discussion)

Does this fulfill your wish for a link/blurb?


//Magnus

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

Re: [GENERAL] user defined function

Richard Huxton  writes:
> Tom Lane wrote:
>> You could do that in C, but none of the available PLs support it.

> How would you define the signature for the function? One parameter of 
> type anyelement?

Type RECORD would be a better choice --- ANYELEMENT allows scalar types
which is not what you want here.  (You could probably still do it with
a function declared that way, but it'd have to take extra steps to
defend itself against being passed, say, an integer.)

If you're looking for a coding model, stripping down record_out() to
just count nulls should get you there.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL]

Rick Gigger <[EMAIL PROTECTED]> writes:
> Every once in a while I've noticed the number of processes I've got  
> running jumps up a little higher than normal.  So I check it out and  
> realize that I'm building up a bunch of processes that just aren't  
> completing.  About half of the one's not completing say "SELECT  
> waiting".  I'm not doing anything that I am aware of that could be  
> blocking anything.  I am only doing regular vacuums.  I am not  
> explicitly locking anything that I know of.  Is there a way to figure  
> out what each of these processes are waiting on?

Look at the pg_locks view to see what locks they are waiting for and
who's got conflicting locks.

regards, tom lane

---(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] Does this look ethical to you?

Yes, that is all I am asking.  Sorry about the MS reference, but I 
needed to make a point :-)




I'd find it reasonable to add a blurb somewhere about "note that there
are other tools and addons available. See for example
http://www.postgresql.org/download"; or something like that. That
certainly provides some value to the end user, and there are also
several other OSS projects that would like that exposure. (Exact wording
is of course up for discussion)

Does this fulfill your wish for a link/blurb?



Thanks for hearing me out,

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

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

  http://archives.postgresql.org

Re: [GENERAL] Does this look ethical to you?


Devrim GUNDUZ wrote:




I am kind of ticked off that they are hijacking my product name this
way.



Yep, but they were using direct links, and I changed any verbage I had 
immediatly when Dave asked me to.


If navicat had mentioned my product in their web page I really wouldn't 
care, but they had links like this: 
http://www.navicat.com/pg_lighting_admin/index.html


That looks like they are selling PG Lighting Admin.

Big difference.

I also never stated that my product or any other should be bundled with 
the win32 installer, I just meant that it should be made known to the 
user that other options for administration are available.  I also never 
stated I thought direct links should be made from the installer to any 
of my home pages.


I am thinking more of the big picture and something that would benefit 
all third party tool vendors commercial or open source.


Doing something like that in no way could hurt pgAdmin III, since there 
is nothing to hurt(it's free after all).


I think we have beat this into the ground, lets drop it now.

I think everyone is or will be happy :-)


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

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

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

[GENERAL] Access a temporary table of another session - is it possible ?

Hi all,

As the subject says, I would like to access a temporary table of another
session, while being the postgres superuser. Is this possible somehow ?
The problem is that I lost the terminal of the other session and I
really would like to see the results (even if partial) of some long
running operations... pg_stats_activity still shows the query running
against that table, so I still have a chance that the table actually
exists.

Thanks,
Csaba.



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

Re: [GENERAL] Does this look ethical to you?

2006-01-24 Thread Marc G. Fournier


On Tue, 24 Jan 2006, Tony Caduto wrote:

You are not letting the user make a choice about which admin tool to use 
or even try...


My  understanding was that pgInstaller gave you the option to install 
pgAdmin III, it isn't a *requirement* to install it, like it is with your 
analogy of M$+IE ...



Just my opinion on the whole fair competition thing,


but, we aren't competing against anyone ... you are competing against us, 
which puts the onous on your to do the marketing, not us ...



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

---(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] Does this look ethical to you?

2006-01-24 Thread Joshua D. Drake



I don't wan't to be bundled, I just want it to be known that there are 
alternatives available.




I don't think it is reasonable to expect the OSS project within the OSS 
installer to mention commercial alternatives IF

they are shipping an already available OSS product.

What I mean is that the OSS Win32 Installer is shipping PgAdmin why 
should they mention your product? If I were
to suggest that the Win32 Installer should mention Replicator there 
would be an uproar, rightfully so.


Postgresql is free, so what is the big deal about letting users know 
about alternative admin tools comercial or open source?  It's not like 
it would put pgAdmin or Postgresql out of business(it could only help).
PostgreSQL is free and the PostgreSQL.Org project does let people know 
about other tools on the website.


It's a shame you don't see how the bundling of pgAdmin(in the current 
way) is hurting the 3rd party community.


You are welcome to create your own Win32 Installer. I don't see it as 
hurting the 3rd party community. I see it as helping the PostgreSQL 
community.




I don't see that as being detrimental to anyone and would certainly be 
in fair play.


Fair play? It could be argued that the fact that you don't open source 
your product means that you don't believe in fair play. I don't 
personally buy

that argument but I assure you it will be made if this thread continues.

The PostgreSQL community does a very good job of giving the commercial 
counter parts their due.


Sincerely,

Joshua D. Drake






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

Re: [GENERAL] Quoted NULLs with COPY FROM (and pgAdmin export data options)

2006-01-24 Thread George Pavlov

> Quotes tend to imply a text field. 

I think you meant to say "quotes imply a non-null text field". And, yes,
I am quite aware of that. The point of the thread was to see if there is
any way of avoiding/overriding that assumption.

> Assuming 
> you don't want to write a short Perl script to pre-process 
> the file the 
> simplest way would be to import into a temporary table with 
> text columns 
> rather than numeric, then copy from that into the real table 
> (with CASE  or two queries).

Yes, both are valid alternatives. The point was to see if I could skip
those steps.

This is actually turning into a bit of a pgAdmin issue: pgAdmin lets you
export data in a format that seems to be unimportable back into the same
table. If in the "Export data to file" form you check "all columns"
under "Quoting" you will get quotes around your numeric NULLs that you
will be unable to import back using COPY without resorting to
preprocessing of some sort (unless someone tells me how COPY can use
quoted NULLs).

George

---(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] user defined function

Do you mean this function? Seems I cannot get much information from it.../* * record_out        - output routine for pseudo-type RECORD. */Datumrecord_out(PG_FUNCTION_ARGS){    elog(ERROR, "Cannot display a value of type %s", "RECORD");
    PG_RETURN_VOID();            /* keep compiler quiet */}On 1/24/06, Tom Lane <[EMAIL PROTECTED]
> wrote:Richard Huxton  writes:> Tom Lane wrote:>> You could do that in C, but none of the available PLs support it.> How would you define the signature for the function? One parameter of> type anyelement?
Type RECORD would be a better choice --- ANYELEMENT allows scalar typeswhich is not what you want here.  (You could probably still do it witha function declared that way, but it'd have to take extra steps to
defend itself against being passed, say, an integer.)If you're looking for a coding model, stripping down record_out() tojust count nulls should get you there.regards, tom lane

Re: [GENERAL] Access a temporary table of another session - is it possible ?

Csaba Nagy <[EMAIL PROTECTED]> writes:
> As the subject says, I would like to access a temporary table of another
> session, while being the postgres superuser. Is this possible somehow ?

No.  It is not a matter of permissions, it is a matter of physically not
having access to the data: buffers for temp tables are private to
individual backends, not shared, for performance reasons.

regards, tom lane

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

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

Re: [GENERAL] user defined function

Yl Zhou <[EMAIL PROTECTED]> writes:
> Do you mean this function? Seems I cannot get much information from it...

That would appear to be Postgres 7.3 :-(

You need a considerably newer version of Postgres if you want to do much
of anything useful with unspecified-type records.  8.0 has most of that
functionality but I think 8.1 added some things.

regards, tom lane

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

Re: [GENERAL] user defined function

But I have to use 7.3 due to some limitations. Can I do it in 7.3? On 1/24/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Yl Zhou <[EMAIL PROTECTED]> writes:
> Do you mean this function? Seems I cannot get much information from it...That would appear to be Postgres 7.3 :-(You need a considerably newer version of Postgres if you want to do muchof anything useful with unspecified-type records.  
8.0 has most of thatfunctionality but I think 8.1 added some things.regards, tom lane

Re: [GENERAL] Backup and Restore mechanism in Postgres

2006-01-24 Thread Brian A. Seklecki


On Tue, 20 Sep 2005, Lincoln Yeoh wrote:


At 10:00 AM 9/20/2005 -0400, Vivek Khera wrote:



On Sep 14, 2005, at 9:45 AM, vinita bansal wrote:


I have a 4 proc. AMD Opteron machine with 32 GB RAM and ~400GB HDD


Just curious what ever came of this?

Also, were you reading the DB and writing the dump file from the same file 
system?  Different partitions on the same disk?  The 400GB is presumably a 
RAID1+0 or RAID5?


If that's the case then, I would highly recommend having a separate 
physical drive/file system for writing backups to (from which your actual 
backup software should be pointed).  Maybe even put it on a different 
channel on the same controller, or a different controller alltogether..


Obviously, the biggest thing missing from a lot of PostgreSQL 
documentation is practicle information for large deployments, including 
strategy and system design requirements.  All in due-time I suppose.


~BAS



Any reason why Postgresql would only get 2.8MB/sec for dumps or slower?

Regards,
Link.


---(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



l8*
-lava

x.25 - minix - bitnet - plan9 - 110 bps - ASR 33 - base8

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

[GENERAL] Constraint that compares and limits field values

2006-01-24 Thread MargaretGillon


I have a table that I am using to hold
keys for M:M relationships.   I have six fields that can hold the
keys and I do this because I validate the key with a foreign key constraint.
Fields evevid1, evevid2 hold keys from the event table, evreid1, evreid2
hold keys from the resource table, etc. The 0 works with the FK constraints
because in each table being referenced I have a record with id = 0 that
is empty.  Each row should only have two foreign key values and the
other key field values are 0.

How do I put a constraint on the Insert
/ Update to make sure that only two fields out of the six have a value
> 0 in them.

I want to make sure that one of these
three fields has a value > 0 and the other two are 0.
 evevid1 int4 
 evenid1 int4 
 evreid1 int4 

Same here. I want to make sure that
one of these three fields has a value > 0 and the other two are 0.
 evevid2 int4
 evenid2 int4
 evreid2 int4 
 
Please reply to me as well as the list
because I get the digest version and it might take a while to get the reply
there.

Thank you,
*** *** *** *** *** *** *** *** ***
*** *** *** *** *** *** *** *** *** *** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

This e-mail message and any attachment(s) are for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential information
which may be privileged or otherwise protected from disclosure.  Any
unauthorized review, use, disclosure or distribution is prohibited.  If
you are not the intended recipient(s), please contact the sender by reply
email and destroy the original message and any copies of the message as
well as any attachment(s) to the original message.

Re: [GENERAL] user defined function

2006-01-24 Thread Thomas Hallgren

For what it's worth, the next release of PL/Java has support for both RECORD parameters and 
SETOF RECORD return types. The adventurous can try out the current CVS HEAD.


Regards,
Thomas Hallgren

Tom Lane wrote:

Yl Zhou <[EMAIL PROTECTED]> writes:

I want to implement a UDF that can accept a parameter which is a tuple of
any table, and returns the number of NULL attributes in this tuple.
Different tables may have different schemas. How can I implement this
function?  Thanks.


You could do that in C, but none of the available PLs support it.

regards, tom lane

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




---(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] user defined function

Can anyone tell me whether 7.3 supports unspecified record types or not? 
On 1/24/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Yl Zhou <[EMAIL PROTECTED]> writes:> Do you mean this function? Seems I cannot get much information from it...That would appear to be Postgres 7.3 :-(
You need a considerably newer version of Postgres if you want to do muchof anything useful with unspecified-type records.  8.0 has most of thatfunctionality but I think 8.1 added some things.regards, tom lane

Re: [GENERAL] Problems with pgsql 8.0.4 & freebsd 6: partially solved

2006-01-24 Thread Vivek Khera



On Jan 24, 2006, at 7:47 AM, Vittorio wrote:


Yesterday, I upgraded freebsd from 5.4 to 6 via
the CD (the iso was downloaded from ftp.freebsd.org).


I've done this many times with no problems.

However.

You serve yourself very well by recompiling all your ports or  
reinstalling the packages to be the ones built against 6.0.  This is  
especially necessary for libraries and programs that link against  
those libraries and those programs that dynamically load extensions  
(such as postgres).  Otherwise you're forcing two copies of libc to  
load at the same time in some cases and that causes no end of confusion.




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] user defined function

Yl Zhou <[EMAIL PROTECTED]> writes:
> But I have to use 7.3 due to some limitations. Can I do it in 7.3?

Probably, but I forget how (and I can guarantee that it will break
when you do move to 8.0 or later, because we changed the internal
representation of rowtype arguments).  You'd be *much* better off to
spend your time fixing whatever it is that's keeping you on 7.3.

regards, tom lane

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

Re: [GENERAL] user defined function

2006-01-24 Thread Scott Marlowe

On Tue, 2006-01-24 at 14:38, Tom Lane wrote:
> Yl Zhou <[EMAIL PROTECTED]> writes:
> > But I have to use 7.3 due to some limitations. Can I do it in 7.3?
> 
> Probably, but I forget how (and I can guarantee that it will break
> when you do move to 8.0 or later, because we changed the internal
> representation of rowtype arguments).  You'd be *much* better off to
> spend your time fixing whatever it is that's keeping you on 7.3.

For some reason I'm remember 7.4 as being the first version that let you
do this.  Not for certain.  I didn't run 7.3 in production though, so I
might have missed it if it could do this.

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

[GENERAL] Interpreting pg_locks; looking for deadlock

2006-01-24 Thread jao


I have a postgresql 7.4.8 database which has the same table
declarations in several schemas. My application accesses each schema
from a single thread; there is never more than one thread accessing a
schema at a time.

To try increasing concurrency, I've tried using multiple threads per
schema. The application quickly locked up, and I suspect deadlock, but
pg_locks doesn't seem to show deadlock.

My pg_locks query is as follows:

   selectns.nspname as "schema",
   c.relname as "table",
   L.transaction,
   L.pid,
   L.mode,
   L.granted
   from pg_locks L, pg_class c, pg_namespace ns
   where L.relation = c.oid
   and   c.relnamespace = ns.oid
   and   ns.nspowner >= 100

The output looks something ilke this:

 schema  |  table  | transaction |  pid  |   mode  
 | granted
--+-+-+---+--+-

schema_1 | idx_e   | | 24058 | AccessShareLock  | t
schema_1 | d   | | 24084 | AccessShareLock  | t
schema_1 | d   | | 24084 | RowExclusiveLock | t
schema_1 | e   | | 24084 | AccessShareLock  | t
schema_1 | e   | | 24084 | RowExclusiveLock | t
schema_1 | e   | | 24008 | AccessShareLock  | t
schema_1 | e   | | 24008 | RowExclusiveLock | t
schema_1 | idx_e   | | 24081 | AccessShareLock  | t
schema_1 | m   | | 24065 | ShareUpdateExclusiveLock | t
schema_1 | idx_e   | | 24091 | AccessShareLock  | t
schema_1 | m   | | 24065 | ShareUpdateExclusiveLock | t
schema_1 | idx_e   | | 24008 | AccessShareLock  | t
schema_1 | idx_e   | | 24059 | AccessShareLock  | t
schema_1 | idx_e   | | 24071 | AccessShareLock  | t
schema_1 | idx_e   | | 24037 | AccessShareLock  | t

d, e, and m are tables, idx_e is an index on the table e.

What puzzles me is that there are no 'f' entries in the granted
column. (This is partial output, but the complete output has no f
entries.)

If this is deadlock, then why don't I see granted = 'f'? And if it
isn't deadlock, then why do so many backend processes appear to be
stuck, e.g. (ps output):

 24057 ?S< 0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting
 24058 ?S< 0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting
 24059 ?S< 0:01 postgres: risdba ris 127.0.0.1 UPDATE waiting


Jack Orenstein



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

[GENERAL] FATAL: invalid frontend message type 47

2006-01-24 Thread Sterpu Victor


After a commit I receive this message:
"FATAL:  invalid frontend message type 47", and rollback.



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

Re: [GENERAL] FATAL: invalid frontend message type 47

2006-01-24 Thread Michael Fuhr

On Tue, Jan 24, 2006 at 11:50:39PM -0500, Sterpu Victor wrote:
> After a commit I receive this message:
> "FATAL:  invalid frontend message type 47", and rollback.

The client seems to have violated the communications protocol.  What
client interface are you using?  What version of PostgreSQL (both
client and server versions if they're different)?  What platform(s)?
How are you connecting to the database (Unix socket, TCP socket,
SSL over TCP, etc.)?  How consistently do you get this error?

-- 
Michael Fuhr

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

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

Re: [GENERAL] Are indexes used with LIKE?

2006-01-24 Thread Michael Fuhr

On Mon, Jan 23, 2006 at 08:00:01PM +0100, Kovcs Pter wrote:
> Are indexes on VARCHAR columns used with the LIKE operator, and if so, 
> how efficiently are they used?
> 
> I can imagine that using indexes can be easy with the starting literal 
> characters up to the first percent sign such as in:
> 
> LIKE 'ZOE%QQWE%'
> 
> But, after the first % sign, things can get more difficult.

The planner can use an index on the starting literal characters;
how "difficult" the query becomes after that depends on how
discriminating those initial characters are.  If values matching
the initial characters comprise a small fraction of the table then
the query will probably use an index and be fast, but if they
comprise a large fraction of the table, or if the search string
starts with a wildcard, then you'll get a sequential scan, which
might be slow.

> The reason I am asking is that we are thinking about discriminating 
> between rows of a table based on a VARCHAR column containing various 
> one-character "flags". We could then use the LIKE operator for 
> formulating filter conditions.

Have you considered putting each flag in a separate column and
indexing those columns?  If you're using 8.1 the planner would
probably use bitmap index scans and come up with a fast plan
regardless of which columns you restrict on.  And performance issues
aside, some people would consider that a better design.  However,
a disadvantage might be that your queries would be more complex.

-- 
Michael Fuhr

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

Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2

2006-01-24 Thread Carl Conard

Connections are through localhost.  We've also connected via a client
machine through a router to insure it is not something on the server.  

By drop connections, I mean Task Manager is showing additional
postgres.exe tasks after the completion of the test.  Also, when we try
to drop the DB to reset for another test, PGAdmin reports connections.
Finally, of the 20 virtual users, any where from 2 to 12 or so will
successfully complete the test (by adding information to the DB via our
PHP app).

The postmaster can be shut down manually with no issues after the test.
However, upon rebooting the machine, I get IIS Helper Failed messages.
I don't know if this is related or not.

Logs don't really show anything.  At least nothing I can find.  

The only changes to the config file was to enable the logging at verbose
and info levels.  I can send the file if you'd like to see it. 

I've noticed a number of issues about beta releases dealing with sockets
and such.  I haven't found anything indicating they were fixed or if
there are work arounds.

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 24, 2006 3:09 AM
To: Carl Conard
Cc: Postgres User; Magnus Hagander; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Installing Postgres 8.1 on Windows Server 2003 R2

Carl Conard wrote:
> I've successfully installed Postgres 8.1.2 on WS 2003 R2 on a Lenovo
> (IBM) ThinkPad.  I used the default installation options and
everything
> seems peachy keen for a single user (using localhost).  
>  
> However, when we started performance testing Postgres (vs. MySQL)
using
> a 3rd party tool (I forget the name) running on the server, Postgres
> would drop connections after about the 10th or 11th virtual user (of
20)
> hit the DB.  The remaining user processes would remain visible in
> Window's Task Manager and just "hang."

How are you connecting?
What do you mean by "drop connections"?
What do you mean by "hang" - does the server have problems shutting them

down if you tell it to?
What do the logs show?
What changes have you made to your configuration files?

-- 
   Richard Huxton
   Archonet Ltd

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

[GENERAL] pgxml

2006-01-24 Thread [EMAIL PROTECTED]

I’ am looking for pgxml module for postgres 7.2.1, someone can help me 
to find it?

Thanks




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

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

Re: [GENERAL] NOT HAVING clause?

2006-01-24 Thread Andrew - Supernews

On 2006-01-24, Will Glynn <[EMAIL PROTECTED]> wrote:
> You might try:
>
> SELECT some_column
>   FROM some_table
>   GROUP BY some_column
>   HAVING SUM(CASE WHEN sort_order=1 THEN 1 ELSE 0 END) = 0;

SELECT some_column
  FROM some_table
 GROUP BY some_column
HAVING every(sort_order <> 1);

every() is in 8.1 at least (can't recall when it was introduced); it's the
same as bool_and(), i.e. an aggregate that returns true only if all inputs
are true. Why isn't there a corresponding any(), I wonder? (bool_or does
exist)

It should be possible to define bool_and() or every() in the usual way as
a custom aggregate even on versions that don't have them builtin.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

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

[GENERAL] Setting expire date on insert/modify

2006-01-24 Thread Foster, Stephen

I'm having a problem converting a simple date routing to PLPGSQL.  I
know the error has to be something stupid but I'm missing it.  The error
is in the "ExpireDate := (date 'StartDate' + integer 'NumOfDays');" line
in the procedure below.  I didn't understand why I had to convert the
date to a string and back to a date but ok.  Below is the error I'm
getting a test and please tell me where I'm going wrong.  Also I include
the trigger procedure, trigger and insert.  Please remember that I'm a
MS-SQL guy migrating to PostgreSQL.

ERROR:  invalid input syntax for type date: "StartDate"
CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
'NumOfDays')"
PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment


CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
ExpireDate timestamptz; -- Date the Banner Ad will expire.
StartDate char(10); -- Date the Banner Ad was created or
renewed
NumOfDays char(10); -- Number of Dates the Ad will be in
place.
BEGIN
StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD');
NumOfDays := to_char(NEW.bannerad_term, '9');
ExpireDate := (date 'StartDate' + integer 'NumOfDays');
IF (TG_OP = 'UPDATE') THEN
UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = OLD.bannerad_id;
ELSIF (TG_OP = 'INSERT') THEN
UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = NEW.bannerad_id;
END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER "Trg_BannerAd_Ads_InsMod" BEFORE INSERT OR UPDATE
   ON bannerad_ads FOR EACH ROW
   EXECUTE PROCEDURE public."TF_BannerAd_Ads_InsMod"();

insert into bannerad_ads
(bannerad_href,bannerad_alttext,bannerad_filename,bannerad_creationdate,
bannerad_term) values ('http://www.equilt.com','ElectricQuilt Southern
Music','EQMBannerAd4.gif','2006-01-20 01:00:00-05',18250);


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006
 


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

Re: [GENERAL] Setting expire date on insert/modify

2006-01-24 Thread Michael Glaesemann



On Jan 25, 2006, at 9:45 , Foster, Stephen wrote:


ERROR:  invalid input syntax for type date: "StartDate"
CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
'NumOfDays')"
PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment


Why are you single-quoting StartDate and NumOfDays? Single-quotes are  
generally used for string literals (e.g., text and varchar types). If  
you are trying to preserve the case of the variable name, you need to  
(always) use double-quotes, including when you define them.


Hope this helps a bit. I haven't looked through all of the code, so  
there may be other bugs lurking, but this is definitely one of the  
problems.


Michael Glaesemann
grzm myrealbox com




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

Re: [GENERAL] Setting expire date on insert/modify

2006-01-24 Thread Foster, Stephen

Michael, I tried that line in the trigger procedure with double quotes,
single quotes and without.  The only way it would save was with single
quotes and that is why you saw it that way.  I know it has to be some
sort of stupid syntax error but since I'm new to PostgreSQL (as far as
this level of coding) I have no idea what my error is.

If I try to save without which was what I did in the first place I
receive this using "ExpireDate := (date StartDate + integer NumOfDays);"

ERROR:  syntax error at or near "$1" at character 16
QUERY:  SELECT  (date  $1  + integer  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod"
near line 8

Full procedure again:

CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
ExpireDate timestamptz; -- Date the Banner Ad will expire.
StartDate char(10); -- Date the Banner Ad was created or
renewed
NumOfDays char(10); -- Number of Dates the Ad will be in
place.
BEGIN
StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD');
NumOfDays := to_char(NEW.bannerad_term, '9');
ExpireDate := (date StartDate + integer NumOfDays);
IF (TG_OP = 'UPDATE') THEN
UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = OLD.bannerad_id;
ELSIF (TG_OP = 'INSERT') THEN
UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = NEW.bannerad_id;
END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

The hard bits I'm getting easily but I keep getting hung up over simple
junk.

Thanks for the help,

Lee Foster/

-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 24, 2006 6:58 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting expire date on insert/modify

On Jan 25, 2006, at 9:45 , Foster, Stephen wrote:

> ERROR:  invalid input syntax for type date: "StartDate"
> CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
> 'NumOfDays')"
> PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment

Why are you single-quoting StartDate and NumOfDays? Single-quotes are  
generally used for string literals (e.g., text and varchar types). If  
you are trying to preserve the case of the variable name, you need to  
(always) use double-quotes, including when you define them.

Hope this helps a bit. I haven't looked through all of the code, so  
there may be other bugs lurking, but this is definitely one of the  
problems.

Michael Glaesemann
grzm myrealbox com

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006

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

[GENERAL] Postgresql Segfault in 8.1

2006-01-24 Thread Benjamin Smith

I'm running PostgreSQL 8.1 on CentOS 4.2, Dual proc Athlon 64 w/4 GB RAM. 

I'm trying to get a PHP app to work, but the failure happens when the command 
is copy/pasted into pgsql. Trying to run a large insert statement, and I get: 

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
# 

in /var/log/messages, I see 

Jan 24 17:00:04 kepler kernel: postmaster[26185]: segfault at 2516d728 
rip 0043c82c rsp 007fbfffddd0 error 4

The insert statement is long, but doesn't seem to violate anything strange - 
no weird characters, and all the fields have been properly escaped with 
pg_escape(). 

What information do you need to help figure this out? 

-Ben 
-- 
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

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

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

Re: [GENERAL] Setting expire date on insert/modify

2006-01-24 Thread Michael Glaesemann



On Jan 25, 2006, at 10:12 , Foster, Stephen wrote:


If I try to save without which was what I did in the first place I
receive this using "ExpireDate := (date StartDate + integer  
NumOfDays);"


ERROR:  syntax error at or near "$1" at character 16
QUERY:  SELECT  (date  $1  + integer  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod"
near line 8


The  common ways of casting in PostgreSQL are some_value::some_type,  
e.g., "ExpireDate"::date, or using CAST, e.g., CAST ("ExpireDate" as  
date). In the general case, some_type some_value will not work.


The docs can also be very helpful. I myself just looked up the syntax  
of the CAST expression (I generally use the double-colon method).


See if that makes a difference.

Michael Glaesemann
grzm myrealbox com




---(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] Setting expire date on insert/modify

2006-01-24 Thread Stephan Szabo

On Tue, 24 Jan 2006, Foster, Stephen wrote:

> CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
>   RETURNS "trigger" AS
> $BODY$
> DECLARE
>   ExpireDate timestamptz; -- Date the Banner Ad will expire.
>   StartDate char(10); -- Date the Banner Ad was created or
> renewed
>   NumOfDays char(10); -- Number of Dates the Ad will be in
> place.
> BEGIN
>   StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD');
>   NumOfDays := to_char(NEW.bannerad_term, '9');
>   ExpireDate := (date 'StartDate' + integer 'NumOfDays');

I think something like:

ExpireDate := CAST(NEW.bannerad_creationdate as Date) + NEW.bannerad_term;

may work for you.


---(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 Segfault in 8.1

2006-01-24 Thread Doug McNaught

Benjamin Smith <[EMAIL PROTECTED]> writes:

> in /var/log/messages, I see 
>
> Jan 24 17:00:04 kepler kernel: postmaster[26185]: segfault at 
> 2516d728 
> rip 0043c82c rsp 007fbfffddd0 error 4
>
> The insert statement is long, but doesn't seem to violate anything strange - 
> no weird characters, and all the fields have been properly escaped with 
> pg_escape(). 
>
> What information do you need to help figure this out? 

Reproduce it with gdb attached to the backend process and post the
backtrace...  You may need to recompile PG with debugging symbols to
get the most info.

-Doug

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

Re: [GENERAL] Setting expire date on insert/modify

2006-01-24 Thread Foster, Stephen

Thanks guys it finally worked correctly.  Just in case someone else get
hung on this type of thing here is the working trigger function.

CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
ExpireDate timestamptz; -- Date the Banner Ad will expire.
BEGIN
NEW.bannerad_expiredate := (NEW.bannerad_creationdate::date +
NEW.bannerad_term::int4);
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephan Szabo
Sent: Tuesday, January 24, 2006 7:18 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting expire date on insert/modify

On Tue, 24 Jan 2006, Foster, Stephen wrote:

> CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
>   RETURNS "trigger" AS
> $BODY$
> DECLARE
>   ExpireDate timestamptz; -- Date the Banner Ad will expire.
>   StartDate char(10); -- Date the Banner Ad was created or
> renewed
>   NumOfDays char(10); -- Number of Dates the Ad will be in
> place.
> BEGIN
>   StartDate := to_char(NEW.bannerad_creationdate, '-MM-DD');
>   NumOfDays := to_char(NEW.bannerad_term, '9');
>   ExpireDate := (date 'StartDate' + integer 'NumOfDays');

I think something like:

ExpireDate := CAST(NEW.bannerad_creationdate as Date) +
NEW.bannerad_term;

may work for you.


---(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

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006
 


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

Re: [GENERAL] pgxml

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> Iâ am looking for pgxml module for postgres 7.2.1, someone can help me 
> to find it?

*Please* tell me you are not still using PG 7.2.1.

There are seven subsequent releases in the 7.2 series, each containing
fixes for very serious bugs --- see
http://developer.postgresql.org/docs/postgres/release-7-2-8.html
and following pages.

Furthermore, 7.2.* has officially been abandoned as unsupported: there
are numerous known bugs in 7.2.8 that aren't going to be fixed, ever.

Rather than looking for pgxml for 7.2, you desperately need to be
spending your time on updating to some non-stone-age version of
Postgres.

regards, tom lane

---(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] Setting expire date on insert/modify

"Foster, Stephen" <[EMAIL PROTECTED]> writes:
> Michael, I tried that line in the trigger procedure with double quotes,
> single quotes and without.  The only way it would save was with single
> quotes and that is why you saw it that way.

Just for reference, the thing that was tripping you up (or one thing
that was tripping you up at least) was trying to extrapolate the syntax

typename 'literal value'

to situations where you didn't mean an actual literal constant.  This
syntax is something that's forced on us by the SQL standard, but we do
not support it for anything but literal constants (which is all that the
standard defines it for, either).  In the examples you gave, you wanted
to coerce the value of a variable of one type to some other type, and so
you should have written either

CAST( variablename AS typename )
or
variablename :: typename

the former being the SQL-spec syntax for a runtime type conversion
and the latter being a traditional Postgres abbreviation.

regards, tom lane

---(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: hardware checks (was Re: [GENERAL] invalid memory alloc request

2006-01-24 Thread Bruce Momjian

Greg Stark wrote:
> Tom Lane <[EMAIL PROTECTED]> writes:
> 
> > Janning Vygen <[EMAIL PROTECTED]> writes:
> > > one more question: You mentioned standard disk and memory checks. Can you 
> > > point to some link where i can find more about it or which software do 
> > > you 
> > > mean? I guess i have to start looking at it.
> > 
> > The stuff I've heard recommended is memtest86 for memory checks and
> > badblocks for disk checks.  But perhaps someone on the list has better
> > ideas.
> 
> I second memtest86, though even the author says memory errors can be tricksy
> things. Sometimes a large compile finds memory errors that even memtest86
> doesn't find (the symptom is gcc crashing).
> 
> However I fear using badblocks alone is pretty useless these days. Modern IDE
> drives detect bad blocks and remap them to other locations. If you just use
> badblocks you'll see mysterious errors that disappear or might not see any
> errors at all. You need to use tools like smartctl to query the drive's SMART
> firmware about errors. It's not easy to interpret but if you watch the numbers
> for a while you can tell if a drive is going bad and continually remapping bad
> blocks. badblocks is useful still as a way of ensuring that every block is
> read and written to, but then you have to look at the SMART data to see what
> happened.

It is my experience the SCSI drive controllers will beep if they have a
bad block that can't be read cleanly.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

Re: [GENERAL] Postgresql Segfault in 8.1