Re: [GENERAL] INSERT OR UPDATE

[Andrew - Supernews]

On 2006-01-03, Tony Wasson <[EMAIL PROTECTED]> wrote:
> On 1/2/06, Julian Scarfe <[EMAIL PROTECTED]> wrote:
>> I'm keeping config information for an application in a series of related
>> tables.  I'd like a command that INSERTs data if it's new, or UPDATEs it if
>> the key is duplicated.
>
> A MERGE trigger will do exactly what you are asking for.
>
> http://archives.postgresql.org/pgsql-sql/2004-05/msg00135.php
>
> Send all your data as INSERTS.
> The trigger will run a function to see if the row exists.
> If the row exists -> rewrite it as it as an UPDATE
> If the row does not exist -> leave the INSERT alone

There are some rather serious race conditions in that (it's not taking
anything like a strong enough lock to avoid that - it would need to use
SHARE ROW EXCLUSIVE, not ROW EXCLUSIVE).

In 8.0 on, use the method described here:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

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

---(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] INSERT OR UPDATE

[Tony Wasson]

On 1/2/06, Julian Scarfe <[EMAIL PROTECTED]> wrote:
> I'm keeping config information for an application in a series of related
> tables.  I'd like a command that INSERTs data if it's new, or UPDATEs it if
> the key is duplicated.

A MERGE trigger will do exactly what you are asking for.

http://archives.postgresql.org/pgsql-sql/2004-05/msg00135.php

Send all your data as INSERTS.
The trigger will run a function to see if the row exists.
If the row exists -> rewrite it as it as an UPDATE
If the row does not exist -> leave the INSERT alone

Tony

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

Re: [GENERAL] Ident authentication failed

[Lucky Leavell]

On Mon, 2 Jan 2006, Madison Kelly wrote:

> Lucky Leavell wrote:
> > v8.0.3
> > OS: SuSE 10.0 Pro
> > 
> > I am new to PostgreSQL but fairly familiar with other RDBMS' such as Ingres,
> > Informix and MySQL.
> > 
> > I am attempting to switch from using MySQL to PostgreSQL with postfix. I
> > have created the postfix user using createuser with a password but denying
> > the ability to create databases or other users. However when I attempt to
> > login even using psql:
> > 
> >  psql -W -U postfix
> > 
> > it prompts me for the password and I enter the one that user was created
> > with. The resulting error is:
> > 
> >  psql: FATAL: Ident authentication failed for user 'postfix'
> > 
> > I know I must be missing something simple but I cannot find it in the docs
> > or FAQs. Please point me in the right direction.
> > 
> > Thank you,
> > Lucky
> 
> By default, PostgreSQL won't let you connect unless your system user account
> matches the user account in postgres... You can either 'su' to the 'postfix'
> user or edit your 'pg_hba.conf' file to have the line:
> 
> local all all  trust
> 
> Usually there will already be the line:
> 
> local all all  ident sameuser
> 
> Be aware though that this essentially opens postgres access to anyone with
> shell access. There are a few other ways to deal with them but the PostgreSQL
> docs do a better job at explaining it than I can.
>
I tried your suggestion except substituting "password" for "trust" and 
it seems to work without giving the farm away! 

Many thanks,
Lucky

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

Re: [GENERAL] Ident authentication failed

[Madison Kelly]

Lucky Leavell wrote:

v8.0.3
OS: SuSE 10.0 Pro

I am new to PostgreSQL but fairly familiar with other RDBMS' such as 
Ingres, Informix and MySQL.


I am attempting to switch from using MySQL to PostgreSQL with postfix. I 
have created the postfix user using createuser with a password but denying 
the ability to create databases or other users. However when I attempt to 
login even using psql:


psql -W -U postfix

it prompts me for the password and I enter the one that user was created 
with. The resulting error is:


psql: FATAL: Ident authentication failed for user 'postfix'

I know I must be missing something simple but I cannot find it in the docs 
or FAQs. Please point me in the right direction.


Thank you,
Lucky


By default, PostgreSQL won't let you connect unless your system user 
account matches the user account in postgres... You can either 'su' to 
the 'postfix' user or edit your 'pg_hba.conf' file to have the line:


local all all  trust

Usually there will already be the line:

local all all  ident sameuser

Be aware though that this essentially opens postgres access to anyone 
with shell access. There are a few other ways to deal with them but the 
PostgreSQL docs do a better job at explaining it than I can.


HTH!

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  Madison Kelly (Digimer)
   TLE-BU; The Linux Experience, Back Up
Main Project Page:  http://tle-bu.org
Community Forum:http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

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

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

[GENERAL] Ident authentication failed

[Lucky Leavell]

v8.0.3
OS: SuSE 10.0 Pro

I am new to PostgreSQL but fairly familiar with other RDBMS' such as 
Ingres, Informix and MySQL.

I am attempting to switch from using MySQL to PostgreSQL with postfix. I 
have created the postfix user using createuser with a password but denying 
the ability to create databases or other users. However when I attempt to 
login even using psql:

psql -W -U postfix

it prompts me for the password and I enter the one that user was created 
with. The resulting error is:

psql: FATAL: Ident authentication failed for user 'postfix'

I know I must be missing something simple but I cannot find it in the docs 
or FAQs. Please point me in the right direction.

Thank you,
Lucky

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

   http://archives.postgresql.org

Re: [GENERAL] INSERT OR UPDATE

[Guy Rouillier]

Julian Scarfe wrote:
> I'm keeping config information for an application in a series of
> related tables.  I'd like a command that INSERTs data if it's new, 
< or UPDATEs it if the key is duplicated.

Write a stored procedure called something like InsertUpdateConfigData.
Pick the operation that you think is most likely to occur more often
(the insert or update).  Code that as the initial statement.  Then
create an exception block, and in that exception block catch the error
that would result from the initial statement being executed in the case
where the other one should have been; then execute that other statement.

There is no magic database-provided SQL statement that says "try an
insert and if that fails then try an update."  You have to do that
yourself.

-- 
Guy Rouillier


---(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] inserting many rows

[Tyler MacDonald]

Hi,
You probably want something like "COPY table_name FROM STDIN";
here's an example from a table that defines flags:

COPY auth_flag (id, name, description) FROM stdin;
2   Admin   System Administrator Access
4   New PasswordUser must change password on next login
8   Super Admin Allow this administrator to edit other administrators
\.

(Those are real tabs between the fields, not spaces). See
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html for more info.

One caveat: If any of your columns are sequences, you'll have to
update their values manually after doing the COPY.

Chees,
Tyler


SunWuKung <[EMAIL PROTECTED]> wrote:
> I will need to insert multiple rows into a table from php. 
> The data will come in 'packages' of 50-500 rows (they are responses from 
> different questionnaires). As there will be many people sending their 
> results in at the same time I need an effective method for this.
> 
> What do you suggest is the most effective way to insert this type of 
> data into the db? Issuing multiple inserts from php seems to be a waste 
> of resources. 
> 
> I was thinking of writing the responses into a pg array field with a 
> single insert and than explode the content of that field into rows with 
> a function.
> 
> Could you suggest an efficient aproach?
> Thanks for the help.
> 
> Balazs
> 
> 
> ---(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
> 

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

[GENERAL] INSERT OR UPDATE

[Julian Scarfe]

I'm keeping config information for an application in a series of related 
tables.  I'd like a command that INSERTs data if it's new, or UPDATEs it if 
the key is duplicated.


Copying the config info from one database to another virgin installation is 
easy, of course. I can just use pg_dump on the original db1 to create the 
DDL and run it into the new database db2.


The problem comes when I've made changes in db1 that I want to copy to db2, 
but db2 may itself have additional entries not present in db1.  I'd like to 
update the entries in db2 that have equivalent entries in db1 with their db1 
values, without destroying the rest of the data in db2.


Here's an example:

db1 is created as follows:

create table a (
a_id text PRIMARY KEY,
param_b text,
param_c text
);

create table d (
d_id text PRIMARY KEY,
a_id text references a(a_id) ON UPDATE CASCADE,
param_e text,
param_f text
);

insert into a values ('a1', 'b1', 'c1');
insert into a values ('a2', 'b2', 'c2');

insert into d values ('d1', 'a1', 'e1', 'f1');
insert into d values ('d2', 'a2', 'e2', 'f2');
insert into d values ('d3', 'a1', 'e3', 'f3');

The databases have identical schema:
$ pg_dump -s db1 | psql db2

and provided db2 is empty, I can just copy the contents across:
$ pg_dump -ad db1 > db1.config
$ psql db2 < db1.config

So far so good.

But now db2 gets some additional entries:
db2=# insert into a values ('a3', 'b3', 'c3');
db2=# insert into d values ('d4', 'a1', 'e4', 'f4');
db2=# insert into d values ('d5', 'a2', 'e5', 'f5');

Meanwhile, I make some config changes in db1:
db1=# update a set param_b = 'b1 new' where a_id = 'a1';
db1=# update d set param_e = 'e1 new' where d_id = 'd1';
db1=# update d set param_f = 'f2 new' where d_id = 'd2';

So:
$ pg_dump -ad db1 > db1.config
and db1.config now contains
INSERT INTO a VALUES ('a2', 'b2', 'c2');
INSERT INTO a VALUES ('a1', 'b1 new', 'c1');
INSERT INTO d VALUES ('d3', 'a1', 'e3', 'f3');
INSERT INTO d VALUES ('d1', 'a1', 'e1 new', 'f1');
INSERT INTO d VALUES ('d2', 'a2', 'e2', 'f2 new');

I want to update the data in db2 to reflect the values in db1. I can't 
truncate the tables in db2 because I'd lose the additional entries (a3, d4, 
d5).


But of course when I do...
$ psql db2 < db1.config
... I get ...
ERROR:  duplicate key violates unique constraint "a_pkey"
ERROR:  duplicate key violates unique constraint "d_pkey"
...and the a1, d1, d2 rows are not updated to match db1.

What I'd really like is to be able to do:

INSERT OR UPDATE INTO a VALUES ('a2', 'b2', 'c2');
INSERT OR UPDATE INTO a VALUES ('a1', 'b1 new', 'c1');
INSERT OR UPDATE INTO d VALUES ('d3', 'a1', 'e3', 'f3');
INSERT OR UPDATE INTO d VALUES ('d1', 'a1', 'e1 new', 'f1');
INSERT OR UPDATE INTO d VALUES ('d2', 'a2', 'e2', 'f2 new');

so that the rest of the row is treated as an UPDATE if the primary key is a 
duplicate.


Of course I can write something at the application level to examine each row 
and take appropriate action. But it feels like this may be a commonly 
encountered problem for which there may be a database-level solution.  Am I 
missing something obvious?


Thanks

Julian Scarfe






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

Re: [GENERAL] using PG with Syslog

[Tony Wasson]

On 1/1/06, Tony Caduto <[EMAIL PROTECTED]> wrote:
> Hi,
> Does anyone know of a good resource on how to use Postgresql as a
> destination for Syslogd messages?
>
> I am interested in putting all postfix logs to a table rather than a file.

I set this up by using syslog-ng, djb's supervise and psql. I
originally started here, but I recall using 3 or 4 formulas before I
found something that worked and that I liked.

http://www.campin.net/syslog-ng/faq.html

Here's what I did:

1) Get syslog-ng running

2) Make sure you have a source in syslog-ng.conf:

source src { udp(); unix-stream ("/dev/log"); internal(); };

3) Add a destination in syslog-ng.conf file like so:

destination d_pgsql {
   pipe("/var/run/syslog2pg.pipe"
  template("INSERT INTO logs (host, facility, priority, level, tag, date,
time, program, msg) VALUES ( '$HOST', '$FACILITY',
'$PRIORITY', '$LEVEL', '$TAG',
   '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n"
  )
  template-escape(yes)
  );
};

4) Make a filter in syslog-ng.conf to match exactly what you want in
the database. In this case you just want email, so it is easy.

filter f_filter4 { facility(mail); };

5) Add a log line in syslog-ng.cong to link the source, filter and destination.

log { source(src); filter(f_filter4); destination(d_pgsql); };

6) Make your postgresql database. I also made an insert only user: logfeed.

CREATE DATABASE syslog;
\c syslog

CREATE TABLE logs (
facility character varying(10),
priority character varying(10),
"level" character varying(10),
tag character varying(10),
date date,
"time" time without time zone,
program character varying(15),
msg text,
seq serial NOT NULL,
host inet
);

CREATE USER logfeed;
GRANT INSERT ON logs to logfeed;

7) Make a file called /usr/local/bin/syslog-db.sh. This creates
/var/run/syslog2pg.pipe.

#!/bin/bash
# Can't remember where I got this from -- Tony
PIPE="/var/run/syslog2pg.pipe";
LOG="/var/log/syslog2pg.log";
if [ -e ${PIPE} ]; then
  while [ -e ${PIPE} ]
  do
# Customize your path, username and database name
 /usr/local/pgsql/bin/psql -q -U logfeed syslog < ${PIPE} > $LOG 2>&1
  done
else
   # Recreate the fifo if it gets the wrong permissions, etc
mkfifo /var/run/syslog2pg.pipe
chmod 660 /var/run/syslog2pg.pipe
chown logfeed.logfeed /var/run/syslog2pg.pipe
  echo "ERROR: fifo not created in ${PIPE}. Please create."
  exit 1
fi

8) I setup /usr/local/bin/syslog-db.sh to be supervised by
daemontools. I made a logfeed user and put a file called "run" for
supervise like so:

#!/bin/bash
exec /usr/local/bin/syslog-db.sh >> /var/log/syslog2pg.log 2>&1

9) Once supervise is running, your process should just work...

Hope this helps!

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

[GENERAL] inserting many rows

[SunWuKung]

I will need to insert multiple rows into a table from php. 
The data will come in 'packages' of 50-500 rows (they are responses from 
different questionnaires). As there will be many people sending their 
results in at the same time I need an effective method for this.

What do you suggest is the most effective way to insert this type of 
data into the db? Issuing multiple inserts from php seems to be a waste 
of resources. 

I was thinking of writing the responses into a pg array field with a 
single insert and than explode the content of that field into rows with 
a function.

Could you suggest an efficient aproach?
Thanks for the help.

Balazs


---(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] Query in postgreSQL version Windows

[Michael Fuhr]

On Mon, Jan 02, 2006 at 04:01:56PM -0500, Ardian Xharra (Boxxo) wrote:
> I installed PostgreSQL on windows and when I try to run some
> queries pgadmin says an error.

What version of PostgreSQL?  What's the exact error message?  That
often provides a clue as to what's wrong.  I'll guess that the
Windows box is running 8.1.x and the error is:

ERROR:  missing FROM-clause entry for table "pg_class"

> I know that if I change the query this it will work but is it
> possible to make this quey work in this way

Why don't you want to rewrite the query?

> SELECT atthasdef 
> FROM pg_attribute 
> WHERE attrelid=pg_class.oid AND pg_class.relname='client_overtime' AND 
> attname='id_client_overtime'
> 
> This query works fine in PostgreSQL (Linux-Unix platform)

Another guess: the Linux box is running a version of PostgreSQL
older than 8.1, or if it's running 8.1 then you've enabled
add_missing_from.  As the 8.1 Release Notes mention, that option
is now disabled by default.  Rather than trying to make the query
work as-is, rewrite it to be proper SQL by adding pg_class to the
FROM clause.

-- 
Michael Fuhr

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

[GENERAL] Query in postgreSQL version Windows

[Ardian Xharra \(Boxxo\)]

Hello, there
I installed PostgreSQL on windows and when I try to 
run some queries pgadmin says an error. I know that if I change the query this 
it will work but is it possible to make this quey work in this 
way
 
SELECT atthasdef FROM pg_attribute WHERE 
attrelid=pg_class.oid AND pg_class.relname='client_overtime' AND 
attname='id_client_overtime'
 
This query works fine in PostgreSQL (Linux-Unix 
platform)
 
Thx
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.10/218 - Release Date: 02/01/2006

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

[GENERAL] Query in postgreSQL version Windows

[Ardian Xharra \(Boxxo\)]

Hello, there
I installed PostgreSQL on windows and when I try to 
run some queries pgadmin says an error. I know that if I change the query this 
it will work but is it possible to make this quey work in this 
way
 
SELECT atthasdef FROM pg_attribute WHERE 
attrelid=pg_class.oid AND pg_class.relname='client_overtime' AND 
attname='id_client_overtime'
 
This query works fine in PostgreSQL (Linux-Unix 
platform)
Thx
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.10/218 - Release Date: 02/01/2006

---(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] Need Licensing Information for bundling POSTGRESQL With Softwares

[Christopher Browne]

> We at Codexpert Systems are developing a Commercial MIS system, and are 
> planning to go for POSTGRESQL as the database system. We need
> some clarifications for the same.
>
> 1. What are licenses we need to have to as developers of a software that uses 
> POSTGRESQL?
> 2. What are the licenses a user is to provided with who uses our software 
> that uses POSTGRESQL as Database System?

You should see about reading the license for PostgreSQL.  That should
answer your questions.


-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxfinances.info/info/emacs.html
Profanity sucks. 

---(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] Need Licensing Information for bundling POSTGRESQL

[Joshua D. Drake]

Rajendra Talekar wrote:


Hi,
We at Codexpert Systems are developing a Commercial MIS system, and 
are planning to go for POSTGRESQL as the database system. We need some 
clarifications for the same.
 
1. What are licenses we need to have to as developers of a software 
that uses POSTGRESQL?


Anything you want, PostgreSQL is BSD licensed.

 
2. What are the licenses a user is to provided with who uses our 
software that uses POSTGRESQL as Database System?



Anything you want, PostgreSQL is BSD licensed.

 
Pl Note that our software is a non Open Sorce software. The 
availability of POSTGRESQL for Windows/Linux and other platforms is 
the main reason we are planning to choose POSTGRESQL


This is not a problem, PostgreSQL is BSD licensed.

Jsohua D. Drake

 
- Rajendra Talekar

Chief Developer and Managing Director
Codexpert Systems




--
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, PLperl - http://www.commandprompt.com/


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

Re: [GENERAL] pg_reload_conf() does not unset archive_command

[Tom Lane]

"Andrus" <[EMAIL PROTECTED]> writes:
> Please confirm that it it not possible to set archive_command parameter to 
> unset state from Postgres client.

It works fine for me.  Maybe you got bit by that bug you pointed out
yesterday that there must be a newline after a postgresql.conf entry?

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] pg_dump error codes

[Michael Fuhr]

On Mon, Jan 02, 2006 at 08:45:28AM -0200, alexandre - aldeia digital wrote:
> In my Linux bash backup scripts, I wish to send an e-mail when an error 
> occurs in pg_dump proccess. And if possible, I want to send the error 
> output via e-mail.
> 
> Anybody knows how to capture the output and send this to an e-mail ONLY 
> if an error occurs ?

This is more of a shell scripting question than a PostgreSQL question.
See your shell's documentation and read about I/O redirection and
control structures like "if".

Here's a simple but only minimally-tested example that might give
you some ideas:

#!/bin/sh

dumpout=/tmp/dump.out.$$
dumperr=/tmp/dump.err.$$
erruser=root

trap "rm -f $dumperr $dumpout; exit" 1 2 15

if ! pg_dump "$@" > $dumpout 2> $dumperr
then
rm -f $dumpout
mail -s "Dump errors" $erruser < $dumperr
fi

rm -f $dumperr

-- 
Michael Fuhr

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

Re: [GENERAL] pg_dump error codes

[Jon Jensen]

On Mon, 2 Jan 2006, alexandre - aldeia digital wrote:

In my Linux bash backup scripts, I wish to send an e-mail when an error 
occurs in pg_dump proccess. And if possible, I want to send the error output 
via e-mail.


Anybody knows how to capture the output and send this to an e-mail ONLY if an 
error occurs ?


I wrote a generic bash shell script to handle this kind of thing in cron. 
See the attachment. As long as the program you're running returns an exit 
value of 0 when it succeeds, and a non-zero value when it fails, this will 
work. (Thought it's not documented in pg_dump's man page, it does return 
sane exit values.)


Just do something like:

/path/to/cron-harness pg_dump your-arguments-here

If pg_dump succeeds, nothing will be output. If it fails, all the normal 
output will be returned (and if this is a cron job, mailed to you, by 
default).


Check the arguments to mktemp, which may vary on your platform. Any 
suggestions or improvements are welcome!


HTH,
Jon


--
Jon Jensen
End Point Corporation
http://www.endpoint.com/
Software development with Interchange, Perl, PostgreSQL, Apache, Linux, ...#!/bin/bash

# cron-harness
# by Jon Jensen <[EMAIL PROTECTED]>
# $Id: cron-harness,v 1.3 2005/12/31 15:28:25 jon Exp $
#
# Invocation:
# cron-harness [ -e [EMAIL PROTECTED] ] some-program and its args
#
# Run some program. If it returns an error exit value, either email its
# output somewhere (if the -e option is given) or simply send it to stdout
# (the default); otherwise be silent.

email=
while getopts e: opts
do
if [ "$opts" = e ]; then
email=$OPTARG
elif [ "$opts" = '?' ]; then
echo "cron-harness: Error parsing options" >&2
exit 1
fi
done
shift $(($OPTIND - 1))

if [ -z "$*" ]; then
echo "cron-harness: No command given" >&2
exit 1
fi

outfile=`mktemp -t cron-harness.out.XX`
if [ $? -ne 0 ]; then
echo "cron-harness: Error creating temporary file" >&2
exit 1
fi

exit=0

$* > $outfile 2>&1

if [ $? -ne 0 ]; then
if [ -n "$email" ]; then
hostname=`hostname | sed 's/\..*//'`
mail -s "cron-harness: Error running $1 on $hostname" $email < 
$outfile
else
cat $outfile
fi
exit=2
fi

rm -f $outfile

exit $exit

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

Re: [GENERAL] inherits index

[Scott Ribe]

My understanding is that you will need to set up the indexes on the child
table in order to do any good for queries on the child table. Also, you have
foreign key (an other, like primary key, I think) constraints are not
inherited and have to be repeated on each table.

Think of it like this: table inheritance in PostgreSQL is not much of a
convenience for defining your schema as it doesn't provide much except for
inheritance of columns. To me it's most useful for certain types of queries
over the shared attributes of similar classes of entities, where you can
query the base table rather than constructing clumsy unions over the common
columns (or having one-to-one relationships, making querying the "base"
attributes easy, but requiring joins for everything dealing with "child"
entities).


-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice



---(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] Casting issue with aggregated function

[Michael Fuhr]

On Mon, Jan 02, 2006 at 11:18:53AM +0100, Marc Mamin wrote:
> select sum(c) from
> (
> select sum(25665800) as c
> )foo
> 
>   => 25665800 (numeric)
> 
> I find the cast form int8 to numeric being strange.

It's documented to work that way:

http://www.postgresql.org/docs/8.1/interactive/functions-aggregate.html

The return type for sum() is "bigint for smallint or int arguments,
numeric for bigint arguments, double precision for floating-point
arguments, otherwise the same as the argument data type."  Your
subquery has an integer argument so its sum() returns bigint; you
then feed that bigint to sum() so the result is numeric.  Presumably
the promotions are done to avoid overflow.

-- 
Michael Fuhr

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

Re: [GENERAL] Need Licensing Information for bundling POSTGRESQL With Softwares

[Mike Rylander]

http://www.postgresql.org/about/licence

On 1/2/06, Rajendra Talekar <[EMAIL PROTECTED]> wrote:
> Hi,
> We at Codexpert Systems are developing a Commercial MIS system, and are
> planning to go for POSTGRESQL as the database system. We need some
> clarifications for the same.
>
> 1. What are licenses we need to have to as developers of a software that
> uses POSTGRESQL?
>
> 2. What are the licenses a user is to provided with who uses our software
> that uses POSTGRESQL as Database System?
>
> Pl Note that our software is a non Open Sorce software. The availability of
> POSTGRESQL for Windows/Linux and other platforms is the main reason we are
> planning to choose POSTGRESQL
>
> - Rajendra Talekar
> Chief Developer and Managing Director
> Codexpert Systems


--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

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

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

Re: [GENERAL] Need Licensing Information for bundling POSTGRESQL With

[Bruce Momjian]

Please read our FAQ and the license item in there.

---

Rajendra Talekar wrote:
> Hi,
> We at Codexpert Systems are developing a Commercial MIS system, and are
> planning to go for POSTGRESQL as the database system. We need some
> clarifications for the same.
> 
> 1. What are licenses we need to have to as developers of a software that
> uses POSTGRESQL?
> 
> 2. What are the licenses a user is to provided with who uses our software
> that uses POSTGRESQL as Database System?
> 
> Pl Note that our software is a non Open Sorce software. The availability of
> POSTGRESQL for Windows/Linux and other platforms is the main reason we are
> planning to choose POSTGRESQL
> 
> - Rajendra Talekar
> Chief Developer and Managing Director
> Codexpert Systems

-- 
  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 2: Don't 'kill -9' the postmaster

[GENERAL] Casting issue with aggregated function

[Marc Mamin]

Title: Casting issue with aggregated function








Hi,


I'm facing a cast issue when using sub queries and I 'd like your opinion if this should be submittede as a Bug. 


(Postgres version 8.1.0)




Compare:


A)


select sum(25665800) as c


    => 25665800 (int8)


B)


select sum(c) from

(

select sum(25665800) as c

)foo


    => 25665800 (numeric)



I find the cast form int8 to numeric being strange.





Cheers,


Marc

[GENERAL] Need Licensing Information for bundling POSTGRESQL With Softwares

[Rajendra Talekar]

Hi,
We at Codexpert Systems are developing a Commercial MIS system, and are planning to go for POSTGRESQL as the database system. We need some clarifications for the same.
 
1. What are licenses we need to have to as developers of a software that uses POSTGRESQL?
 
2. What are the licenses a user is to provided with who uses our software that uses POSTGRESQL as Database System?
 
Pl Note that our software is a non Open Sorce software. The availability of POSTGRESQL for Windows/Linux and other platforms is the main reason we are planning to choose POSTGRESQL
 
- Rajendra Talekar
Chief Developer and Managing Director
Codexpert Systems

[GENERAL] pg_dump error codes

[alexandre - aldeia digital]

Hi,

(maybe an idiot question)

In my Linux bash backup scripts, I wish to send an e-mail when an error 
occurs in pg_dump proccess. And if possible, I want to send the error 
output via e-mail.


Anybody knows how to capture the output and send this to an e-mail ONLY 
if an error occurs ?


Thanks

Alexandre

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

Re: [GENERAL] pg_reload_conf() does not unset archive_command

[Andrus]

> This is a known problem - since pg_reload_conf() will ignore the lines 
> with a # (comment), so the old value is kept.

I tried to add a line

archive_command=''

But after pg_reload_conf() postmaster still uses the old value which exists 
in its memory.

Please confirm that it it not possible to set archive_command parameter to 
unset state from Postgres client.

Andrus. 



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

Re: [GENERAL] Visual FoxPro 9 ODBC errors

[Andrus]

"Is anyone else having problems with 9? "

I use VFP 9  + Postgres 8.1 on XP + 105 version of Postgres Unicode ODBC 
driver without problems.

Andrus. 



---(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] POstgreSQL 8.1.X/Lazarus?

[Zlatko Matić]

OK. I get it. I copied libpq74.dll  to system32 and now it works, bu only 
partially, just for tables, not for queries. There is some runtime error for 
queries, but I should ask about it in some Zeos and Lazarus newsgroups. 
Thanks.


- Original Message - 
From: "Tony Caduto" <[EMAIL PROTECTED]>

To: "Zlatko Matić" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, December 29, 2005 6:12 PM
Subject: Re: [GENERAL] POstgreSQL 8.1.X/Lazarus?


Are you running Lazarus on win32 or Linux?(I assume win32 because you 
mentioned a dll)


I have successfully used Zeos with Delphi against a 8.1 server, I even 
used the libpq74.dll that ships with zeos.


I guess you should make sure any versions of libpq you have are in your 
system32 dir.   Windows first checks the system dir, then your app dir for 
dlls. (it used to be the other way around)


Also check out the Zeos forums at:
http://zeosforum.net.ms/

Later,

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



 Is there anybody using lazarus with new POstgreSQL 8.1.X ?
I couldn't connect using Zeos, because it seems zeos work only with old 
versions of PostgreSQL.
Also, I couldn't connect by using TPSQL, because it can't find libpg.dll 
installed (?)...

 Regards,
 Zlatko



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



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

Re: [GENERAL] Postgre sql win32 silent install

[Magnus Hagander]

> Dear list,
> 
>  I'd like to ask you if there is any way to install the 
> PostGre SQL Windows Server silently. I mean by writing a one 
> line command in the command line or sg like this... The whole 
> thing is because I'm writing an installer (a Visual Studio 
> 2003 Setup Project) for an application, which uses PostGre 
> SQL servers, and I don't want the user to install the PostGre 
> SQL separately, that's why I'm interested in any legal and 
> technologically possible way to do it.

See http://pginstaller.projects.postgresql.org/silent.html

(Oh, and it's PostgreSQL or Postgres, not Postgre or Postgre SQL)

//Magnus

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

   http://archives.postgresql.org

[GENERAL] Postgre sql win32 silent install

[Dobóczy Ákos]

Dear list,

 I'd like to ask you if there is any way to install the PostGre SQL Windows
Server silently. I mean by writing a one line command in the command line or
sg like this... The whole thing is because I'm writing an installer (a
Visual Studio 2003 Setup Project) for an application, which uses PostGre SQL
servers, and I don't want the user to install the PostGre SQL separately,
that's why I'm interested in any legal and technologically possible way to
do it.

Regards,
 Dobóczy Ákos
 developer
 _

 c a r n a t i o n

 Dorottya Udvar, Budapest
 1113 Bocskai út 134-146.
 Hungary
 tel +36 1 887 5353 | fax +36 1 887 5350
 http://www.carnationconsulting.com



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

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

[GENERAL] inherits index

[Matthew Peter]

I have a parent table that is inherited by a  couple others... Would it be best to set up an index on the children  tables column that is inherited by the parent, or should I index the  parent column that is inherited by the children? The children tables  are the ones called frequently, not the parent. I just want to help the  planner to use the most effective way by setting it up properly for  parsing, especially if these tables get really big.  Thanks, Matt  
		Yahoo! Photos 
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.