Re: [GENERAL] Postgres shutting down by itself...why?

[Tom Lane]

"Dave Horn" <[EMAIL PROTECTED]> writes:
> I've recently installed Postgres 8.2.x on a RedHat Linux system.  Everything
> seems fine and normal.  I can start and stop the server without errors.
> When the server is running, everything appears normal.  The problem is that
> about every 2 to 3 hours I find the server is just not running anymore.
> I've turned on logging at the info level and up and am not getting any
> messages that is giving me any clue why the server is shutting down.

I've never seen or heard of a case of PG shutting down "by itself".
You should be looking for outside causes.  What else is running
on that system?

If there is absolutely nothing in the server log file mentioning
a shutdown, I would tend to think that something has kill -9'd
the postmaster process.  This has been known to happen on
short-of-memory Linux systems, if you don't disable memory overcommit
(google "OOM kill" for details).

Whether that's it or not, the kernel syslog file would be a good
first stop to check for clues.

Oh, another line of thought: maybe you are launching the postmaster
under restrictive ulimit settings that kill it after not very much
runtime?

regards, tom lane

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

Re: [GENERAL] power failure....

[Tom Lane]

Tom Allison <[EMAIL PROTECTED]> writes:
> 2007-11-30 19:35:20 EST PANIC:  could not locate a valid checkpoint  
> record

Ugh :-(.  pg_resetxlog should get you back into the database, but it's
anybody's guess whether and how badly the contents will be corrupted.
I would recommend trying a dump/reload and also doing any manual
consistency crosschecks you can think of.

> postgres version 8.2 sitting on a Reiser FS on RAID1

Better take a second look at your disk hardware configuration.  At least
in theory, this Can't Happen if your disk hardware is handling fsync
honestly.

regards, tom lane

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

Re: [GENERAL] Porting from FrontBase

[Martin]

In article <[EMAIL PROTECTED]>,
David Fetter <[EMAIL PROTECTED]> wrote:

>FrontBase has a Perl DBI driver
>, so you may be
>able to use DBI-Link .
>
>If you try that approach, let me know how it works out :)

Thanks, that looks interesting. Even if it doesn't work out for
porting, I will have to play with it. ;)

BTW, I ported a few of the tables and one of the queries. The timing
difference is quite amazing:

FrontBase:  13 seconds
Postgresql: 42 msec

There's a lot of incentive to do the port ;)


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

   http://archives.postgresql.org/

[GENERAL] Postgres shutting down by itself...why?

[Dave Horn]

I've recently installed Postgres 8.2.x on a RedHat Linux system.  Everything
seems fine and normal.  I can start and stop the server without errors.
When the server is running, everything appears normal.  The problem is that
about every 2 to 3 hours I find the server is just not running anymore.
I've turned on logging at the info level and up and am not getting any
messages that is giving me any clue why the server is shutting down.

I get the following when I restart after I've discovered its down

LOG:  database system was interrupted at 2007-11-29 14:25:48 CST
LOG:  checkpoint record is at 0/43FBE0
LOG:  redo record is at 0/43FBE0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/837; next OID: 32773
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/43FC28
LOG:  redo is not required
LOG:  database system is ready

The server is not even being used at this point.  I can literally start the
server come back a few hours later and find its not running.

Any assistance, ideas, suggestions would be greatly appreciated.  Thanks
all.


---(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] libeay32.dll and libpq.dll

[seijin]

I'm working on a Windows application that will connect to a Postgresql
database without having through ODBC.

I'm using libpq and testing with a simple PQconnectdb/PQfinish call
but the application fails as soon as PQconnectdb is called.  The error
I get says that libeay32.dll is missing.  I have no idea what this
program is and why it would be required.

I've tested this exact same code before on a PC where I had installed
the Windows ODBC driver for Postgesql and the code worked using libpq
like it should.  However, on this new machine, I get the libeay32.dll
error.

Any thoughts on this?  I don't have to install the ODBC driver, do I?
It would hurt if that is true.

Update... One thing - I found a copy of this file in a directory for
SmartTerm so I tried copying it over to my test application's
directory.  Now when I run it, I get "procedure entry point
X509_STORE_set_flags could not be located in the dynamic link library
libeay32.dll"

I have no idea why this is being required.

Help?

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

Re: [GENERAL] Will PG use composite index to enforce foreign keys?

[Scott Marlowe]

On Nov 29, 2007 10:51 AM, John Burger <[EMAIL PROTECTED]> wrote:
> Hi -
>
> I know that the foreign key machinery will use an index on the
> referring column if one exists.  My question is whether it will use a
> composite index?  For instance:
>
> create table allLemmaSenseMap (
>wordID integer references allLemmas,
>senseIDinteger references allSenses,
>primary key (wordID, senseID)
> );
>
> If I delete something from allLemmas, will the FK check use the PK
> index above?  (I know I should at least have an index on senseID as
> well, because of the other foreign key.)

Yes.  It will

> As a secondary question, is there any way I could have answered this
> myself, using analyze, the system catalogs, etc?  ANALYZE DELETE
> doesn't seem to show the FK checking that must go on behind the scenes.

You could have coded up an example to see if it worked I guess.
Here's a short example:

create table a (i int, j int, info text, primary key (i,j));
create table b (o int, p int, moreinfo text, foreign key (o,p) references a);
insert into a values (1,2,'abc');
insert into b values (1,2,'def');
INSERT 0 1
insert into b values (1,3,'def');
ERROR:  insert or update on table "b" violates foreign key constraint "b_o_fkey"
DETAIL:  Key (o,p)=(1,3) is not present in table "a".
delete from a;
ERROR:  update or delete on table "a" violates foreign key constraint
"b_o_fkey" on table "b"
DETAIL:  Key (i,j)=(1,2) is still referenced from table "b".

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

[GENERAL] power failure....

[Tom Allison]

How do I restart the following with some level of sanity?

2007-11-30 19:35:20 EST LOG:  could not load root certificate file  
"root.crt": no SSL error reported

2007-11-30 19:35:20 EST DETAIL:  Will not verify client certificates.
2007-11-30 19:35:20 EST LOG:  database system was interrupted at  
2007-11-27 09:08:44 EST

2007-11-30 19:35:20 EST LOG:  record with zero length at 0/40808BA0
2007-11-30 19:35:20 EST LOG:  invalid primary checkpoint record
2007-11-30 19:35:20 EST LOG:  record with zero length at 0/407C9628
2007-11-30 19:35:20 EST LOG:  invalid secondary checkpoint record
2007-11-30 19:35:20 EST PANIC:  could not locate a valid checkpoint  
record
2007-11-30 19:35:20 EST LOG:  startup process (PID 8755) was  
terminated by signal 6
2007-11-30 19:35:20 EST LOG:  aborting startup due to startup process  
failure



postgres version 8.2 sitting on a Reiser FS on RAID1

I'm not finding much on the web other than bad news...


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

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

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Greg Smith]

On Fri, 30 Nov 2007, Lincoln Yeoh wrote:

Anecdotal - I have found "smart" raid controllers to fail more often than 
dumb scsi controllers (or even SATA/PATA controllers), and some seem more 
failure prone than semi-decent operating systems.


You'd need to name some names here for this to mean too much.  There are 
plenty of positively miserable RAID controllers out there.  I wouldn't 
trust the cards from Adaptec, Promise, and Highpoint to correctly store a 
database about what's in my pockets.


What's more likely in most places is somebody powering down the server 
abruptly, and then fsync=off could hurt :).


Here you're hitting on the real point.  If a proposed solution adds 
potential for database corruption if someone trips over the server cord, 
it's not really a solution at all.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Greg Smith]

On Fri, 30 Nov 2007, Guido Neitzer wrote:

Actually - In our test if just used with a similar load as pgbench (e.g. 
typical web applications) Mac OS X  10.4.7 performed better then Yellow Dog 
Linux (I was testing with G5 hardware) on the same hardware as soon as more 
than about 90 concurrent clients were simulated.


At this point, that's just an interesting historical note.  Yellow Dog is 
not a particularly good Linux compared with the ones that have gotten 
years worth of performance tuning for Intel/AMD processors.  And you 
really can't extrapolate anything useful today from how it ran on a 
G5--that's two layers of obsolete.  The comparisons that matter now are 
Intel+Mac OS vs. Intel+a popular Linux aimed at servers.


As an unrelated note, I'm curious what you did with pgbench that you 
consider it a reasonable similation of a web application.  The default 
pgbench transaction is very write-heavy, and the read-only option 
available is way too simple to be realistic.  You'd need to pass in custom 
scripts to execute to get something that acted like a web app.  pgbench is 
an unruly tool, and there's many ways to run it that gives results that 
aren't so useful.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---(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] Record variable not behaving as expected (bug?)

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 12:34:57PM -0800, Postgres User wrote:
> Sorry, here's the code:



The code you posted won't work on a blank database, I needed to delete
the test prefix to table2. I've adjusted it to be hopefully what you
did. My output is:
---
CREATE TABLE
CREATE FUNCTION
version
---
 PostgreSQL 8.1.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 
(prerelease) (Debian 4.1.1-21)
(1 row)

 divide

  -1.00
(1 row)

DROP FUNCTION
DROP TABLE
---

Can you try the attached script  on a *nlank* database? It'll give us
more info about your system.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy
CREATE TABLE table2 (
 "s_val" NUMERIC(6,2),
 "e_val" NUMERIC(6,2)
);

CREATE FUNCTION divide () RETURNS numeric AS
$body$
declare
  retval numeric(6,2);
  rec record;
begin
  SELECT * INTO rec FROM table2 LIMIT 0;
  rec.s_val = 100.0;
  rec.e_val = 101.0;

  -- returns correct value w/ casting:
  --retval = ((rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) / 
rec.s_val::numeric(6,2)) * 100;

  -- returns incorrect value, as if fields have invalid datatypes:
  retval = ((rec.s_val - rec.e_val) / rec.s_val) * 100;

  return retval;
end
$body$
LANGUAGE 'plpgsql';

select version();
select divide();
drop function divide();
drop table table2;


signature.asc
Description: Digital signature

Re: [GENERAL] PL/pgSQL and SETOF

[Pavel Stehule]

On 30/11/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Pavel Stehule escribió:
> > On 30/11/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > > Pavel Stehule escribió:
> > > > On 30/11/2007, Cultural Sublimation <[EMAIL PROTECTED]> wrote:
> > >
> > > > > But still on that subject: is my version of get_items2 the simplest
> > > > > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > > > > the SQL version...
> > > >
> > > > no. Your simplest version is historic relict and is available only in
> > > > sql language. I am not sure, maybe in C language too.
> > >
> > > But isn't the case that on 8.3 you would be able to rewrite it more
> > > easily using RETURN QUERY?
> >
> > yes. sure. But it is different question than call SRF function in
> > normal context.
>
> Hmm, you are misunderstanding Cultural or I am.  I think his verbosity
> comment is not about the way the function is called, but rather about
> the function body itself.
>

It's my English. I am sorry. You have true.

Thank you for correction

Pavel

> --
> Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 
> 56.4"
> "Before you were born your parents weren't as boring as they are now. They
> got that way paying your bills, cleaning up your room and listening to you
> tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers
>

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

Re: [GENERAL] PL/pgSQL and SETOF

[Alvaro Herrera]

Pavel Stehule escribió:
> On 30/11/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > Pavel Stehule escribió:
> > > On 30/11/2007, Cultural Sublimation <[EMAIL PROTECTED]> wrote:
> >
> > > > But still on that subject: is my version of get_items2 the simplest
> > > > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > > > the SQL version...
> > >
> > > no. Your simplest version is historic relict and is available only in
> > > sql language. I am not sure, maybe in C language too.
> >
> > But isn't the case that on 8.3 you would be able to rewrite it more
> > easily using RETURN QUERY?
> 
> yes. sure. But it is different question than call SRF function in
> normal context.

Hmm, you are misunderstanding Cultural or I am.  I think his verbosity
comment is not about the way the function is called, but rather about
the function body itself.

-- 
Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers

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

Re: [GENERAL] PL/pgSQL and SETOF

[Pavel Stehule]

On 30/11/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Pavel Stehule escribió:
> > On 30/11/2007, Cultural Sublimation <[EMAIL PROTECTED]> wrote:
>
> > > But still on that subject: is my version of get_items2 the simplest
> > > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > > the SQL version...
> >
> > no. Your simplest version is historic relict and is available only in
> > sql language. I am not sure, maybe in C language too.
>
> But isn't the case that on 8.3 you would be able to rewrite it more
> easily using RETURN QUERY?
>

yes. sure. But it is different question than call SRF function in
normal context.

Pavel


> --
> Alvaro Herrera  Developer, http://www.PostgreSQL.org/
> "Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
>

---(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] HA and Replication - how to choose among all the available solutions

[Pascal Cohen]

Hello

I am facing a probably very common problem. I made a search in the 
recent archives and could find many posts related to my issue. But I did 
not get exactly "the answer" to my question.
How can I chose among the different existing tools to enable High 
Availability and Load-balancing/Replication.
In fact our situation is common. We have a DB that is growing and we 
need to ensure High-availability.

That needs some standby solution.
On the other hand we have few updates and many reads and when we are 
trying some simulations, the number of requests may become too high for 
our server (I mean requests fast - less than 4ms with explain run- 
starts to be logged with a threshold to 200ms) so we have thought to 
replication to balance the request load among several machines. We can 
afford some delay among the DB (which means sync replication is welcome 
but not mandatory and should probably be avoided if we want to improve 
the perfs).

I had mainly a look at several solutions like:
PGCluster - but I could not get some fresh stuff
Slony-I - seems mature and used
PgPool-II - active and interesting project
PostgreSQL-R - was not able to test it.
C-JDBC/Sequoia.

I mainly focused on PgPool-II and Slony which seemed to be active projects.
I also looked at the Log-Shipping section in PostgreSQL documentation to 
ensure high-availability.


Concerning the high-availability issue, I can in fact imagine using 
log-shipping, or Slony-I or PgPool-II in multi-master mode with only one 
server available.
But I don't know how to chose a solution. I don't have much experience 
and can't see precisely the advantages and the drawbacks of each solution.


Concerning Scalability, I think I would need PgPool-II connected in 
front to at least load balance select to the different servers. But I 
can use the "multi-master" solution or rely on Slony to replicate data 
while PgPool balances the select and only sends updates to the Master. 
In fact that last solution makes me more comfortable because I fear that 
multi-master can lead to complex conflict resolution while a single 
master avoids such concern.
With a single master I can't have any data mismatch case I was able to 
simulate with PgPool-II (inserting manually in a DB a duplicate value or 
adding a trigger to simulate an exception).

What is the experience concerning that kind of question?

Mixing both Scalability and High-availability: with PgPool-II running in 
"multi-master" solution, no real issue. With single master it is less 
obvious :(
I had imagined a mixed solution using a Master relying on a warm standby 
using log-shipping and Linux-HB while Replicated slaves would be 
replicated using Slony and load balancing would be managed by PgPool-II. 
But perhaps it is stupid and using only PgPool-II is safe enough.

And why should I chose Slony instead of Log-shipping to update the slaves ?

The hardest in the situation is to make the (or at least a) good choice 
among all the available solutions and to chose a realistic one


That are many questions in this quite long post.
Thanks for any help or advice you could give me.

Pascal


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

Re: [GENERAL] PL/pgSQL and SETOF

[Alvaro Herrera]

Pavel Stehule escribió:
> On 30/11/2007, Cultural Sublimation <[EMAIL PROTECTED]> wrote:

> > But still on that subject: is my version of get_items2 the simplest
> > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > the SQL version...
> 
> no. Your simplest version is historic relict and is available only in
> sql language. I am not sure, maybe in C language too.

But isn't the case that on 8.3 you would be able to rewrite it more
easily using RETURN QUERY?

-- 
Alvaro Herrera  Developer, http://www.PostgreSQL.org/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)

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

Re: [GENERAL] postgresql table inheritance

[Jeff Davis]

On Sat, 2007-12-01 at 04:16 +0800, Lincoln Yeoh wrote:
> The people who try to make a database that maps so well with the 
> objects in a single particular program are solving a very different 
> problem from those of us who use a database partly as a "lingua 
> franca" (or "vehicular language") for many different programs and people.
> 

Replace "in a single particular program" with "in a specific revision of
a specific component of an application" ;)

The reason I say this is because most people don't realize that, by just
making their objects "persist", that all of their data is now very
context sensitive (to specific revisions of specific parts of their
code). Contrast that with storing real world facts, which are both
context insensitive and time insensitive.

I do see your point, but in this context I don't think the two uses are
very different. In the first case you mention, you are using the
database as a lingua franca between the application at time T and the
application at time T + N years (which is, in reality, a different
application); rather than in the second case, where it's a lingua franca
between two different applications at the same time.

> Link.
> 

Did you intend to include a URL?

> One man's impedance mismatch is another man's layer of abstraction or 
> "comms protocol" :).
> 

Good point.

Regards,
Jeff Davis


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

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

Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing

[Guido Neitzer]

On 30.11.2007, at 12:50, Shane Ambler wrote:

I project where the "latest news" page shows the newest entry from  
March 2005 and the install talks only about PostgreSQL 8.0 isn't  
really inspiring confidence ...


Although they aren't the fastest with releases, they really aren't  
as bad as that.


Nicely said ... ;-) Yeah, I was looking at the wrong site. Maybe there  
should be a redirect to the new page to avoid that.


I have last looked at pgcluster back in 2005 when I was trying to find  
an affordable multi master solution an the setup sounded so horrific  
that we spent about 10k EUR to get a different solution that is  
actually working fine now. Setting this up on just two machines was  
about 10 minutes work ...


I hope the setup has changed to the better.

cug

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

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

Sorry, here's the code:

CREATE TABLE table2 (
 "s_val" NUMERIC(6,2),
 "e_val" NUMERIC(6,2)
);

CREATE FUNCTION divide () RETURNS numeric AS
$body$
declare
  retval numeric(6,2);
  rec record;
begin
  SELECT * INTO rec FROM test.table2 LIMIT 0;
  rec.s_val = 100.0;
  rec.e_val = 101.0;

  -- returns correct value w/ casting:
  --retval = ((rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2)) * 100;

  -- returns incorrect value, as if fields have invalid datatypes:
  retval = ((rec.s_val - rec.e_val) / rec.s_val) * 100;

  return retval;
end
$body$
LANGUAGE 'plpgsql';

On Nov 30, 2007 12:31 PM, Postgres User <[EMAIL PROTECTED]> wrote:
> Sure, I'd be glad to provide any additional code or info that I can.
> This issue took me quite awhile to track down from the 200-line
> function that was breaking.
>
> When I execute the function defined above, here's the output:
>
> select divide()
>?column?
> 
>  0
>
> > Seems rather unlikely to affect just windows. Can you post a script
> > that you can run against a blank database that shows the problem.
> > complete with output on your machine.

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

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

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

Sure, I'd be glad to provide any additional code or info that I can.
This issue took me quite awhile to track down from the 200-line
function that was breaking.

When I execute the function defined above, here's what I see returned:

select divide()
?column?

 0

Hopefully someone can test on Windows to validate.


On Nov 30, 2007 8:25 AM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Fri, Nov 30, 2007 at 08:20:30AM -0800, Postgres User wrote:
> > tom- did you test this on wndows?  you can ignore the namespace- i'm
> > using it consistently but removed from the test code to simplify
> >
> > this problem occurs repeatedly in my code.  my guess is that it's a
> > bug in the windows build.
>
> Seems rather unlikely to affect just windows. Can you post a script
> that you can run against a blank database that shows the problem.
> complete with output on your machine.
>
>
> Have a nice day,
> --
> Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> > Those who make peaceful revolution impossible will make violent revolution 
> > inevitable.
> >  -- John F Kennedy
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHUDmUIB7bNG8LQkwRAvNMAKCQl8+bPo3bca/a33T+WVfQ/Ng2yQCdG+H5
> wZyc/alsznWsWck20lheR00=
> =RVrJ
> -END PGP SIGNATURE-
>
>

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

Re: [GENERAL] postgresql table inheritance

[Lincoln Yeoh]

At 03:17 AM 12/1/2007, Jeff Davis wrote:

The impedance mismatch has more to do with the fact that the meaning of
an application's internal data structures changes frequently (through
revisions of the code), while data in a database needs to be consistent
across long periods of time. So, a well-designed database will hold
facts that have meaning in the real world and from which inferences can
be made. Mapping application data structures (which contain context-
sensitive information and implementation artifacts) to real-world facts
is the impedance mismatch.


The people who try to make a database that maps so well with the 
objects in a single particular program are solving a very different 
problem from those of us who use a database partly as a "lingua 
franca" (or "vehicular language") for many different programs and people.


The "impedance" then is unavoidable. It's not going to be easy to 
change a hundred other programs anyway - probably some unknown (till 
they inconveniently stop working because someone decided to "match 
the impedances" with some pet program ;) ).


But anyway, I guess postgresql's "table inheritance" thing isn't 
broken then just misunderstood...


Link.

One man's impedance mismatch is another man's layer of abstraction or 
"comms protocol" :).



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

[GENERAL] Fault Tolerance & Master-master-Replication Solution needed

[Ragnar Heil]

Hi,

I am searching for a  Fault Tolerance & Master-master-Replication
Solution which fulfills these requirements

Req1) Master master replication supported, not only master / slave
replication with only the master being
writable. If you do have multiple slave systems they are only useful
from a backup and standby perspective.
Ad Mo must have a db-connection it can write to.
Req2) A plan is needed for deploying changes on the database structure.
What needs to be done to apply a
change saved into a live db environment?
Req3) Not only a warm-standby-solution is needed which is not scalable.
Load Balancing is wanted for the
future.

Do you have experience with Cybercluster or Continuent uni/cluster?
Sounds very promising.


best regards
Ragnar



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

Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing

[Shane Ambler]

Guido Neitzer wrote:

On 30.11.2007, at 02:34, Usama Dar wrote:


Have you looked at pgCluster


I project where the "latest news" page shows the newest entry from March 
2005 and the install talks only about PostgreSQL 8.0 isn't really 
inspiring confidence ...


Although they aren't the fastest with releases, they really aren't as 
bad as that.


You would be looking at http://pgcluster.projects.postgresql.org/ which 
has been neglected for some time. You can find the most current source 
downloads at pgfoundry http://pgfoundry.org/projects/pgcluster/ which 
has the last release at May 2007. The project home is now at 
http://www.pgcluster.org (but they seem to use pgfoundry to store the 
release tarballs)


Also Cybertec appears to have branched off from pgcluster 
http://www.postgresql.at/english/pr_cybercluster_e.html



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] postgresql table inheritance

[Jeff Davis]

On Fri, 2007-11-30 at 14:33 +, Peter Childs wrote:
> Table Inheritance is table structure ie a child table has all the same
> columns as the old one with some added columns that sore specialist
> items. This feature is used heavily used  in Table Partitioning.
> Perhaps it should be renamed.

I think that was the point of the slashdot post: it creates confusion to
call two separate concepts by the same name.

Regards,
Jeff Davis


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

Re: [GENERAL] postgresql table inheritance

[Jeff Davis]

On Fri, 2007-11-30 at 21:42 +0800, Lincoln Yeoh wrote:
> --- post follows ---
> by Anonymous Coward on Wed Nov 28, '07 03:23 PM (#21509173)
> 
> Speak for your database -- postgresql does.
> 
> Postgresql's "table inheritance" is a flawed concept and has nothing to do
> with the *type system*. Relations contain tuples, and tuples contain
> attributes, which are a name plus a VALUE. Those values are chosen from TYPES
> (sets of possible values). Those types are the TYPE SYSTEM.

"Foundation for Future Database Systems: The Third Manifesto" by C.J.
Date and Hugh Darwen discusses this topic in Appendix E, and a related
topic in Appendix D.

They also propose, in detail with a lot of analysis, how they think that
type inheritance should work in the set of chapters on the Inheritance
Model, or "IM".

Regards,
Jeff Davis


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

[Jeff Davis]

On Fri, 2007-11-30 at 16:06 +, Gregory Stark wrote:
> "Lincoln Yeoh" <[EMAIL PROTECTED]> writes:
> 
> > The correct way to store types and subtypes in the database is to store them
> > in the columns. In other words, choose attribute VALUES from a TYPE SYSTEM.
> > Nothing else in the relational model needs to be changed. Something like
> > this, in hypothetical SQL-like language:
> 
> That's what we call "denormalized" data in the database world. This is why
> there's such a big impedance mismatch between procedural languages and
> relational databases.
> 

CJ Date believes that normalized relations can indeed contain complex
types such as images or other relations.

The argument is that atomicity has no absolute meaning, but only has
meaning in the context of what you're trying to do with it in the
database. A string can obviously be decomposed into its parts, as can a
timestamp, etc., so those aren't exactly atomic, either. [1]

The impedance mismatch has more to do with the fact that the meaning of
an application's internal data structures changes frequently (through
revisions of the code), while data in a database needs to be consistent
across long periods of time. So, a well-designed database will hold
facts that have meaning in the real world and from which inferences can
be made. Mapping application data structures (which contain context-
sensitive information and implementation artifacts) to real-world facts
is the impedance mismatch.

Regards,
Jeff Davis

[1] Paraphrased from "Database in Depth", C.J. Date, pp 29-32


---(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] PL/pgSQL and SETOF

[Pavel Stehule]

On 30/11/2007, Cultural Sublimation <[EMAIL PROTECTED]> wrote:
> > I would guess you're calling it like:
> >
> >   SELECT get_items2();
> >
> > whereas, you should call set returning functions like:
> >
> >   SELECT * FROM get_items2();
>
> Hi,
>
> Yeah, that was the problem -- thanks!
>
> But still on that subject: is my version of get_items2 the simplest
> that is possible in PL/pgSQL?  It seems awfully verbose compared to
> the SQL version...
>

no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.

Pavel



> Regards,
> C.S.
>
>
>
>   
> 
> Never miss a thing.  Make Yahoo your home page.
> http://www.yahoo.com/r/hs
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>

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

   http://archives.postgresql.org/

Re: [GENERAL] PL/pgSQL and SETOF

[Cultural Sublimation]

> I would guess you're calling it like:
> 
>   SELECT get_items2();
> 
> whereas, you should call set returning functions like:
> 
>   SELECT * FROM get_items2();

Hi,

Yeah, that was the problem -- thanks!

But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL?  It seems awfully verbose compared to
the SQL version...

Regards,
C.S.



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

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

Re: [GENERAL] PL/pgSQL and SETOF

[Pavel Stehule]

Hello


> Unfortunately it doesn't work!  Postgresql complains that "set-valued
> function called in context that cannot accept a set".  Anyway, what am
> I doing wrong, and what is the simplest way of translating get_items
> in PL/pgSQL?
>
> Thanks in advance!
> C.S.

try

select * from get_items2()

Regards
Pavel Stehule
>
>
>
>   
> 
> Get easy, one-click access to your favorites.
> Make Yahoo! your homepage.
> http://www.yahoo.com/r/hs
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

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

Re: [GENERAL] PL/pgSQL and SETOF

[Sam Mason]

On Fri, Nov 30, 2007 at 09:09:28AM -0800, Cultural Sublimation wrote:
> Postgresql complains that "set-valued
> function called in context that cannot accept a set".  Anyway, what am
> I doing wrong, and what is the simplest way of translating get_items
> in PL/pgSQL?

I would guess you're calling it like:

  SELECT get_items2();

whereas, you should call set returning functions like:

  SELECT * FROM get_items2();


  Sam

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

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Guido Neitzer]

On 30.11.2007, at 04:48, Wolfgang Keller wrote:


LSI drivers are not available for MacOS X on PowerMacs? Ouch.


The problem is that they suck as they can't to channel bundling for  
higher trough-put to a single disk array.


[not your comment, but referred there]

and Mac OS X, PostgreSQL has enough
performance issues that I feel using those plaforms can only be
justified if platform compatibility is more important than
performance to you.


Actually - In our test if just used with a similar load as pgbench  
(e.g. typical web applications) Mac OS X  10.4.7 performed better then  
Yellow Dog Linux (I was testing with G5 hardware) on the same hardware  
as soon as more than about 90 concurrent clients were simulated.


But okay, don't trust statistics you didn't make up yourself ...

cug

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

[GENERAL] PL/pgSQL and SETOF

[Cultural Sublimation]

Hi,

I am having trouble getting a really simple PL/pgSQL function to work.
I am beginning to wonder if there is not a bug somewhere, or if Postgresql's
type system is not broken.  Anyway, suppose I have the following table and
type defined:

CREATE TABLE items
(
item_id int,
item_name   text,
item_etctext
);

CREATE TYPE simple_item_t AS
(
item_id int,
item_name   text
);


It's easy to create a SQL function that returns a set of simple items:

CREATE FUNCTION get_items ()
RETURNS SETOF simple_item_t
LANGUAGE sql STABLE AS
$$
SELECT item_id, item_name FROM items;
$$;


Now, all I want is to create the equivalent PL/pgSQL function.  Nothing
more, nothing less.  This is the simplest version I can come up with:

CREATE FUNCTION get_items2 ()
RETURNS SETOF simple_item_t
LANGUAGE plpgsql STABLE AS
$$
DECLARE
itemsimple_item_t%ROWTYPE;
BEGIN
FOR item IN SELECT item_id, item_name FROM items
LOOP
RETURN NEXT item;
END LOOP;
RETURN;
END
$$;


Unfortunately it doesn't work!  Postgresql complains that "set-valued
function called in context that cannot accept a set".  Anyway, what am
I doing wrong, and what is the simplest way of translating get_items
in PL/pgSQL?

Thanks in advance!
C.S.



  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs 

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

Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing

[Guido Neitzer]

On 30.11.2007, at 02:34, Usama Dar wrote:


Have you looked at pgCluster


I project where the "latest news" page shows the newest entry from  
March 2005 and the install talks only about PostgreSQL 8.0 isn't  
really inspiring confidence ...


Continuent is very active, but it limits the servers to Linux, it  
seems it doesn't work on BSD or Mac OS X. At least, these are not  
listed on the product page for uni/cluster.


The lack of an integrated multi master clustering solution in  
PostgreSQL is the only real downside I can see. For me it is better to  
have something that is well integrated, functional and supported but  
only fits the needs for about 80% of the people in need for a multi  
master than having nothing and always pointing to very old or poor or  
commercial commercial solutions.


But that is just my personal view on that. I know that a multi master  
cluster is a very complex feature, but you can't always throw more  
(the existing solutions need AFAIK at least four servers to be  
redundant) or bigger hardware or expensive solutions if you only need  
load balancing but your app needs to be able to write to whatever  
server it is connected.


In that respect, I really like the solution in FrontBase, where you  
can do multi master with two servers, schema synchronization is  
included, you can connect or disconnect servers from the cluster at  
any time, it is fully transparent for the application, you just add  
more addresses to the JDBC connection string. This might not fit the  
needs for a couple of users, but it fits for the vast majority.


Personally I can live without a multi master solution for PG at the  
moment as I just use a different product if I need it and live with  
the downsides of said product (cost for Oracle and similar, less  
configuration options and lower performance with FrontBase, other  
problems with other DMBS).


cug


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

  http://archives.postgresql.org/

Re: [GENERAL] Recheck condition

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 11:27:24AM -0500, Josh Harrison wrote:
> Thanks for your reply
> Is there a way to get them not to use the
> heap for intermediate result and go to heap only for final data? This will
> drastically improve the performance but Im not sure if postgres can do that?
> Will creating the index in a different way and/or rewriting the query in a
> different way achieve this result?

I'm trying to imagine what it would take to avoid the heap access after
the index scan I don't think it's possible. It would require that the
bitmaps generated by the bitmap scan have the person_id attached and
then have the bitmap AND operation only happen if the person IDs match.
No such machinary currently exists.

You do have somewhat of a worst case scenario, intersecting 300k
records with 3million records. I'm not sure if there is a good way to
handle that. The only things I can think of is to add the person_id to
the index also so that you can avoid the sort (not sure if first or
last is more helpful). Or perhaps clustering on that index to reduce
disk access...

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] Recheck condition

[Josh Harrison]

>
>
> Or using an IN or EXISTS query:
>
> SELECT person_id
>  FROM person
>  WHERE column1=1
>   AND column2='62'
>   AND person_id IN (
> SELECT person_id
>   FROM person
>  WHERE column1=1
>AND column2='189'
>   )
>
> or
>
> SELECT person_id
>  FROM person AS parent
>  WHERE column1=1
>   AND column2='62'
>   AND EXISTS (
> SELECT 1
>   FROM person
>  WHERE parent.person_id = person_id
>AND column1=1
>AND column2='189'
>   )
>

Thanks for your reply
The query with IN  gave this plan and took 1m19sec to give the result which
is slightly more than the intersect query(40 sec). The other query with
exists takes way long time for results. All these queries does a heap scan
for intermediate results...right? Is there a way to get them not to use the
heap for intermediate result and go to heap only for final data? This will
drastically improve the performance but Im not sure if postgres can do that?
Will creating the index in a different way and/or rewriting the query in a
different way achieve this result?

Thanks
jo

QUERY
PLAN

 
-

 Hash Join  (cost=705823.44..1182434.64 rows=43631 width=4) (actual time=
26443.675..52055.698 rows=140464
loops=1)
   Hash Cond: (public.person.patient_id = public.person.patient_id)

   ->  Bitmap Heap Scan on person  (cost=17886.42..492557.97 rows=381993
width=4) (actual time=442.934..25779.601 rows=327498
loops=1)
 Recheck Cond: ((column1 = 1) AND ((column2)::text =
'62'::text))

 ->  Bitmap Index Scan on person_idx
(cost=0.00..17790.92rows=381993 width=0) (actual time=
403.869..403.869 rows=327498 loops=1)
   Index Cond: ((column1 = 1) AND ((column2)::text =
'62'::text))
   ->  Hash  (cost=687933.35..687933.35 rows=294 width=4) (actual time=
26000.635..26000.635 rows=6568
loops=1)
 ->  HashAggregate  (cost=687930.41..687933.35 rows=294 width=4)
(actual time=25992.971..25996.471 rows=6568
loops=1)
   ->  Bitmap Heap Scan on person
(cost=156754.24..679555.96rows=3349781 width=4) (actual time=
3202.251..23974.389 rows=3429228 loops=1)
 Recheck Cond: ((column1 = 1) AND ((column2)::text =
'189'::text))
 ->  Bitmap Index Scan on person_idx  (cost=
0.00..155916.80 rows=3349781 width=0) (actual
time=3145.912..3145.912rows=3429228 loops=1)
   Index Cond: ((column1 = 1) AND ((column2)::text =
'189'::text))
 Total runtime: 52094.598 ms

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 08:20:30AM -0800, Postgres User wrote:
> tom- did you test this on wndows?  you can ignore the namespace- i'm
> using it consistently but removed from the test code to simplify
> 
> this problem occurs repeatedly in my code.  my guess is that it's a
> bug in the windows build.

Seems rather unlikely to affect just windows. Can you post a script
that you can run against a blank database that shows the problem.
complete with output on your machine.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

tom- did you test this on wndows?  you can ignore the namespace- i'm
using it consistently but removed from the test code to simplify

this problem occurs repeatedly in my code.  my guess is that it's a
bug in the windows build.

On Nov 30, 2007 8:13 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> > On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
> >> However, I have found that my record variable is not assigned proper
> >> field-level datatypes.  As a result, I'm unable to write basic math
> >> calcs in pg/sql without a lot of typecasting.
>
> > What version are you running. On my 8.1.9 test system it returns -1.00
> > as expected.
>
> Works for me too, in all branches back to 8.0.  However, I noticed that
> the test function references "test.table2" not just "table2", which
> makes me wonder if maybe this is picking up some other table2 than the
> OP thinks.  A test.table2 with integer columns would explain the result.
>
>regards, tom lane
>

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

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Tom Lane]

Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
>> However, I have found that my record variable is not assigned proper
>> field-level datatypes.  As a result, I'm unable to write basic math
>> calcs in pg/sql without a lot of typecasting.

> What version are you running. On my 8.1.9 test system it returns -1.00
> as expected.

Works for me too, in all branches back to 8.0.  However, I noticed that
the test function references "test.table2" not just "table2", which
makes me wonder if maybe this is picking up some other table2 than the
OP thinks.  A test.table2 with integer columns would explain the result.

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] postgresql table inheritance

[Gregory Stark]

"Lincoln Yeoh" <[EMAIL PROTECTED]> writes:

> The correct way to store types and subtypes in the database is to store them
> in the columns. In other words, choose attribute VALUES from a TYPE SYSTEM.
> Nothing else in the relational model needs to be changed. Something like
> this, in hypothetical SQL-like language:

That's what we call "denormalized" data in the database world. This is why
there's such a big impedance mismatch between procedural languages and
relational databases.

The natural place to end up in his world would be to have every table have
precisely one column which is some kind of object. Then you access fields and
methods on those objects.

The problem is that then you don't have a relational database since it's
awfully hard to tell the database you have a foreign key from whatever this
method returns here to whatever that method returns there for some record
somewhere... And it's awfully hard to index and join between complex
expressions picking data out from inside objects on both sides, etc.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

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

Re: [GENERAL] FK index q'n

[Alvaro Herrera]

Tom Lane wrote:
> rihad <[EMAIL PROTECTED]> writes:
> > Given this table:
> > CREATE TABLE foo (
> >  id integer primary key,
> >  bar_id integer references bar (id)
> > );
> > and provided that bar.id is itself a PK, do I still need to create an 
> > index on bar_id if often doing queries like:
> > SELECT MIN(id) FROM foo WHERE bar_id IS NULL;
> 
> Now as far as the above-illustrated query goes, no simple index is going
> to help it, because IS NULL is not an indexable operation.

Unless you are on 8.3, that is.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
Y dijo Dios: "Que sea Satanás, para que la gente no me culpe de todo a mí."
"Y que hayan abogados, para que la gente no culpe de todo a Satanás"

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

Re: [GENERAL] Recheck condition

[Gregory Stark]

"Martijn van Oosterhout" <[EMAIL PROTECTED]> writes:

> On Fri, Nov 30, 2007 at 08:21:18AM -0500, Josh Harrison wrote:
>> > > *Query1*
>> > > SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
>> > > INTERSECT
>> > > SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')
>
>> I get the same plan(see below)  with 'sort'  for 'intersect all' operation
>> too. Why is intersect not an effecient way? Is there any other way this
>> query/index can be written/created so that I can get the intersect results
>> in an efficient way?
>
> Set operations are rather inefficient. To find the intersection of two
> arbitrary sets you need to sort them and compare. 

I think all the set operations are implemented this way. It's actually a
pretty clever plan if you're processing two large lists without indexes but,
it would be nice to support a fuller set of plans like we do for other kinds
of queries. For INTERSECT star-schema joins might actually be best.

> A query like you write would be better expressed as a join, something like:
>
> SELECT a.person_id 
> FROM (SELECT person_id  FROM person   WHERE (column1=1 AND column2='62') a,
>  (SELECT person_id  FROM person  WHERE (column1=1 AND column2='189') b
> WHERE a.person_id = b.person_id;
>
> or perhaps:
>
> SELECT a.person_id
> FROM person a, person b
> WHERE a.column1=1 AND a.column2='62'
> AND b.column1=1 AND b.column2='189'
> AND a.person_id = b.person_id;

Or using an IN or EXISTS query:

SELECT person_id 
  FROM person 
 WHERE column1=1
   AND column2='62'
   AND person_id IN (
 SELECT person_id
   FROM person 
  WHERE column1=1 
AND column2='189'
   )

or

SELECT person_id 
  FROM person AS parent
 WHERE column1=1
   AND column2='62'
   AND EXISTS (
 SELECT 1
   FROM person
  WHERE parent.person_id = person_id
AND column1=1 
AND column2='189'
   )

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

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

Re: [GENERAL] FK index q'n

[Tom Lane]

rihad <[EMAIL PROTECTED]> writes:
> Given this table:
> CREATE TABLE foo (
>  id integer primary key,
>  bar_id integer references bar (id)
> );
> and provided that bar.id is itself a PK, do I still need to create an 
> index on bar_id if often doing queries like:
> SELECT MIN(id) FROM foo WHERE bar_id IS NULL;

The FK relationship as such is only a reason to create an index if you
frequently do updates or deletes in table bar.  For such operations, the
database has to check if there are any matching rows in foo, and an
index on foo.bar_id makes that go faster.

Now as far as the above-illustrated query goes, no simple index is going
to help it, because IS NULL is not an indexable operation.  If you are
really concerned about queries of that specific form, you could make a
partial index

create index fooi on foo (id) where bar_id is null;

regards, tom lane

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

Re: [GENERAL] PostgresSQL vs Ingress

[Ivan Sergio Borgonovo]

On Fri, 30 Nov 2007 13:22:31 -
"Greg Sabino Mullane" <[EMAIL PROTECTED]> wrote:

> > --> It provides a feel-good feeling knowing that a big company,
> > after having paid XXX amount on it, the solution will not die in
> > X number of years leaving the customer stranded.
> 
> That's a valid concern. Not sure having a roadmap really correlates 
> with long-term existence, but it's a logical concern for companies.

uh like SCO... you pay XXX and... well the "solution" die.
I thought resilience to management idiocy, financial scandals etc...
was actually a characteristic of mature OS projects.
But yeah clear long-term plans are something to consider, but they
don't look so related with money.

> > 2. Accountability
> > --> Community Owned/Control = no specific person to sue. (and we
> > all know how enterprises are sue-happy
> > --> The want a scapegoat. With Community, who's to be sued?
> > (unless of course they buy from a company such as MySQL or
> > EnterpriseDB, they can most certainly sue them for moolah)

> No, they want someone to call when things go wrong, not someone to
> sue or a scapegoat. Please don't perpetuate this urban myth. No
> companies are suing Oracle and Microsoft because of their products,
> and companies have no expectation of doing so. It might be nice if
> they did, and some theorize it would lead to better and more secure
> products, but the reality is that with software, you are on your
> own. Any company telling you otherwise as a reason not to use open
> source is lying.

It would be curious to see it happening indeed.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

Re: [GENERAL] PostgresSQL vs Ingress

[Tom Lane]

Ow Mun Heng <[EMAIL PROTECTED]> writes:
> Being Corporate owned is not a bad thing(tm) for they present themselves
> with these differences.
> ...
> --> It provides a feel-good feeling knowing that a big company, after
> having paid XXX amount on it, the solution will not die in X number of
> years leaving the customer stranded.

Anyone who thinks that's a reason to feel good is living on some other
planet than I do.  Consider that if the company *does* decide to abandon
the product ... which happens all the time, particularly for products
that aren't market leaders ... you are up the proverbial creek with no
paddle.  You've never seen the code and never will.  With an open-source
product, you at least have the option to hire a couple of programmers
and maintain it yourself, for as long as *you* need it.  (Moreover,
there are probably a few other people in the same position as you, whom
you can cooperate with.  Need I point out that this is exactly how the
current Postgres project came to be, ten-plus years ago?)

regards, tom lane

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

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

Re: [GENERAL] postgresql table inheritance

[Ivan Sergio Borgonovo]

On Fri, 30 Nov 2007 21:42:53 +0800
Lincoln Yeoh <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> Found this post on Slashdot which I found interesting, any comments?

I wrote a memo about inheritance at the bottom of which there are 2
links to good use of the feature:

http://www.webthatworks.it/d1/page/postgresql_inheritance_surprises

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

   http://archives.postgresql.org/

Re: [GENERAL] psql lo_export documentation

[Jorgen Austvik - Sun Norway]

Albe Laurenz wrote:
With experiments I find that lo_export on 8.3 stores the files on the 
database server file system. I also think it would be good if the 
documentation said that you need to be database superuser to use this


I think you are confusing lo_export(), the server function,
and \lo_export, the psql command.


Yes, you are correct. The combination of \lo_import and lo_export() 
confused me. Thank you!


-J
--

Jørgen Austvik, Software Engineering - QA
Sun Microsystems Database Technology Group
begin:vcard
fn;quoted-printable:J=C3=B8rgen Austvik
n;quoted-printable:Austvik;J=C3=B8rgen
org:Sun Microsystems;Database Technology Group
adr:;;Haakon VIII gt. 7b;Trondheim;;NO-7485;Norway
email;internet:[EMAIL PROTECTED]
title:Senior Engineer
tel;work:+47 73 84 21 10 
tel;fax:+47 73 84 21 01
tel;cell:+47 901 97 886
x-mozilla-html:FALSE
url:http://www.sun.com/
version:2.1
end:vcard


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

   http://archives.postgresql.org/

Re: [GENERAL] PostgresSQL vs Ingress

[Ivan Sergio Borgonovo]

On Fri, 30 Nov 2007 13:22:31 -
"Greg Sabino Mullane" <[EMAIL PROTECTED]> wrote:

> > --> It provides a feel-good feeling knowing that a big company,
> > after having paid XXX amount on it, the solution will not die in
> > X number of years leaving the customer stranded.
> 
> That's a valid concern. Not sure having a roadmap really correlates 
> with long-term existence, but it's a logical concern for companies.

uh like SCO... you pay XXX and... well the "solution" die.
I thought resilience to management idiocy, financial scandals etc...
was actually a characteristic of mature OS projects.
But yeah clear long-term plans are something to consider, but they
don't look so related with money.

> > 2. Accountability
> > --> Community Owned/Control = no specific person to sue. (and we
> > all know how enterprises are sue-happy
> > --> The want a scapegoat. With Community, who's to be sued?
> > (unless of course they buy from a company such as MySQL or
> > EnterpriseDB, they can most certainly sue them for moolah)

> No, they want someone to call when things go wrong, not someone to
> sue or a scapegoat. Please don't perpetuate this urban myth. No
> companies are suing Oracle and Microsoft because of their products,
> and companies have no expectation of doing so. It might be nice if
> they did, and some theorize it would lead to better and more secure
> products, but the reality is that with software, you are on your
> own. Any company telling you otherwise as a reason not to use open
> source is lying.

It would be curious to see it happening indeed.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

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

Re: [GENERAL] scrollable cursor in functions

[Pavel Stehule]

Hello

8.1.x doesn't support scrollable cursors in plpgsql. Its supported only in 8.3.

Regards
Pavel Stehule

On 30/11/2007, Cedric Boudin <[EMAIL PROTECTED]> wrote:
> Dear members of the list,
>
> on a server 8.1.9 I try to do this:
> ++
> create or replace function dummy() returns void
> as
> $$
> DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
> BEGIN
>
> --DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
> -- Set up a cursor:
>
>
> -- Fetch the first 5 rows in the cursor liahona:
> FETCH FORWARD 5 FROM liahona;
>
> FETCH PRIOR FROM liahona;
>
> -- Close the cursor and end the transaction:
> CLOSE liahona;
> COMMIT;
>
> end;
> $$ language plpgsql;
> --
> I do get:
> ++
>
> ERROR:  syntax error at or near "CURSOR"
> CONTEXT:  invalid type name "SCROLL CURSOR with hold FOR SELECT * FROM
> album"
> compile of PL/pgSQL function "dummy" near line 1
> --
> If I put the cursor declaration in the begin->end  block it does not matter.
> If I do:
> +++
> BEGIN work;
>
> DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
> -- Set up a cursor:
>
>
> -- Fetch the first 5 rows in the cursor liahona:
> FETCH FORWARD 5 FROM liahona;
>
> FETCH PRIOR FROM liahona;
>
> -- Close the cursor and end the transaction:
> CLOSE liahona;
> COMMIT WORK;
> -
> I do get the expected results. Thus I conclude that the scrollable
> cursors are enabled on the server.
> I've seen some posts in the mailing list archives about some related
> problems like
> *BUG #2970
> -Are scrollable cursor forbidden in *PL/pgSQL? I did not see such a 
> restriction explicitly in the doc.
> -If not, am I doing something wrong?
> -If not, is it a bug?
>
> have a nice day
>
> cedric
>
> **
>
>
> ---(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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] Recheck condition

[Josh Harrison]

>
>
> > > > *Query1*
> > > > SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
> > > > INTERSECT
> > > > SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')
>
> > I get the same plan(see below)  with 'sort'  for 'intersect all'
> operation
> > too. Why is intersect not an effecient way? Is there any other way this
> > query/index can be written/created so that I can get the intersect
> results
> > in an efficient way?
>
> Set operations are rather inefficient. To find the intersection of two
> arbitrary sets you need to sort them and compare. A query like you
> write would be better expressed as a join, something like:
>
> SELECT a.person_id
> FROM (SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
> a,
> (SELECT person_id  FROM person  WHERE (column1=1 AND column2='189') b
> WHERE a.person_id = b.person_id;
>
> or perhaps:
>
> SELECT a.person_id
> FROM person a, person b
> WHERE a.column1=1 AND a.column2='62'
> AND b.column1=1 AND b.column2='189'
> AND a.person_id = b.person_id;
>
> Which will probably generate a merge join...
>

Thanks. But this query seems to be more expensive than using intersect
operator.
This is the explain analyse plan for this query. It took 5 1/2 minutes to
generate this. I also tried to disable the mergejoin and in that case it
uses hash join and still takes more than 3 minutes (intersect took only 40
sec)

QUERY
PLAN

 
-

 Merge Join  (cost=1610648.92..10280119.99 rows=577856095 width=4) (actual
time=30562.630..264534.677 rows=225145385
loops=1)
   Merge Cond: (a.patient_id = b.patient_id)

   ->  Sort  (cost=527974.81..528929.79 rows=381993 width=4) (actual time=
3755.361..3845.134 rows=213435
loops=1)
 Sort Key: a.patient_id

 Sort Method:  quicksort  Memory:
15868kB

 ->  Bitmap Heap Scan on clinical_variable2 a  (cost=
17886.42..492557.97 rows=381993 width=4) (actual
time=315.753..3410.366rows=327498 loops=1)
   Recheck Cond: ((top_parent_service_sys_id = 1) AND
((top_parent_service_code)::text =
'62'::text))
   ->  Bitmap Index Scan on clinical_variable_idx_topserv
(cost=0.00..17790.92 rows=381993 width=0) (actual
time=277.185..277.185rows=327498 loops=1)
 Index Cond: ((top_parent_service_sys_id = 1) AND
((top_parent_service_code)::text =
'62'::text))
   ->  Materialize  (cost=1082674.11..1124546.38 rows=3349781 width=4)
(actual time=26807.248..99885.620 rows=225148250
loops=1)
 ->  Sort  (cost=1082674.11..1091048.57 rows=3349781 width=4)
(actual time=26807.238..30343.870 rows=3429228
loops=1)
   Sort Key: b.patient_id

   Sort Method:  external merge  Disk:
53552kB

   ->  Bitmap Heap Scan on clinical_variable2 b  (cost=
156754.24..679555.96 rows=3349781 width=4) (actual
time=2744.126..20106.160rows=3429228 loops=1)
 Recheck Cond: ((top_parent_service_sys_id = 1) AND
((top_parent_service_code)::text =
'189'::text))
 ->  Bitmap Index Scan on clinical_variable_idx_topserv
(cost=0.00..155916.80 rows=3349781 width=0) (actual
time=2686.456..2686.456rows=3429228 loops=1)
   Index Cond: ((top_parent_service_sys_id = 1) AND
((top_parent_service_code)::text =
'189'::text))
 Total runtime: 324646.035ms


 18 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] a: 0/ms]

Is there any other way you can think of to solve this problem. May be
creating the indexes in a  different way or something?

Thanks
jo

Re: [GENERAL] PostgresSQL vs Ingress

[Andrew Sullivan]

On Fri, Nov 30, 2007 at 01:25:46PM +, Peter Childs wrote:
> 
> In short the current Ingres is related to Postgresql like Xorg is related to
> XFree86 or Ubuntu to Debian but much much much older.

Not quite, according to the programmers who worked on Postgres at UC
Berkeley.  While X.org and Ubuntu both took code from the other project,
Postgres was a completely new project, and there is no code shared between
it and Ingres (except to the extent that some of the same coders may have
touched both projects at some time, and each coder has a style).  The
projects both came from the same research lab, is all. 

A

-- 
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

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

[Peter Childs]

On 30/11/2007, Lincoln Yeoh <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> Found this post on Slashdot which I found interesting, any comments?

--- post follows ---

> by Anonymous Coward on Wed Nov 28, '07 03:23 PM (#21509173)
>
> Speak for your database -- postgresql does.
>
> Postgresql's "table inheritance" is a flawed concept and has nothing to do
> with the *type system*. Relations contain tuples, and tuples contain
> attributes, which are a name plus a VALUE. Those values are chosen from
> TYPES
> (sets of possible values). Those types are the TYPE SYSTEM.
>
> Table inheritence doesn't even make sense. Tables are analogous to
> relations.
> All relations are the same type, the relation type (think "set" or "array"
> to
> make it easier). How can one value of a type (one table) be a subtype of
> another value (another table)? That's like saying, "3" is a subtype of
> "5",
> if your types are integers. What if you use the expression "3+2" Is that
> "5"
> still the subtype of 3? likewise, when you make complex queries with a
> "base"
> table, does the result have any connection with the "sub" table? It's like
> gobbledygook, just mashing words together without any understanding.
> That's
> why the postgresql table inheritance concept doesn't see more widespread
> use.
> Many people quickly discover the limitations (and incorrectly think it's
> just
> "unfinished", when it actually is flawed).
>
> The correct way to store types and subtypes in the database is to store
> them
> in the columns. In other words, choose attribute VALUES from a TYPE
> SYSTEM.
> Nothing else in the relational model needs to be changed. Something like
> this, in hypothetical SQL-like language:
>
>  CREATE TABLE People ( INT id, PERSON_CLASS person )
>
>  p1 = PERSON_CLASS.new(name: "joe", etc)
>
>  p2 = CUSTOMER_CLASS.new(name: "bob", etc) // assume CUSTOMER_CLASS
> subclass of PERSON_CLASS
>
>  INSERT INTO People VALUES (1, p1), (2, p2)
>
>  SELECT person FROM People WHERE person.name = "bob"
>
>  SELECT person, order FROM People JOIN Orders // can't do this in the
> typical "object database"
>
> This is a "solved problem" (see "The Third Manifesto"). It's just a matter
> of
> getting somebody to implement it. But the vendors are clueless, thinking
> object databases are a "different model" and not wanting to confuse
> programmers, and programmers are clueless, not even understanding SQL or
> types and values half the time, so they don't demand anything new from
> vendors... we never move forward.
>
>
> ---(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
>


Seams like two completely different concepts are getting confused. ie that
of Table Inheritance and that of Type Inheritance. They are completely
different concepts.

Table Inheritance is table structure ie a child table has all the same
columns as the old one with some added columns that sore specialist items.
This feature is used heavily used  in Table Partitioning. Perhaps it should
be renamed.

Type Inheritance is adding extra features to types eg

Varchar(5) is a child of text that adds a maximum length limit of 4 and
char(5) is a type of text with a fixed length of 5. But they are all text.
This is a very silly example.

Just thoughts.

Peter.

Re: [GENERAL] PostgresSQL vs Ingress

[Andrew Sullivan]

On Fri, Nov 30, 2007 at 01:22:31PM -, Greg Sabino Mullane wrote:
> or a scapegoat. Please don't perpetuate this urban myth. No companies are 
> suing Oracle and Microsoft because of their products, and companies have 
> no expectation of doing so. It might be nice if they did, and some theorize 

Indeed, by using the product, companies have explicitly given up the right
to sue over it.  This is the main point of the EULA of most products, and is
one of the strangest things about the computer industry.  No other industry
can get away with producing shoddy products that endanger others, and induce
its users to give up the right to sue in case that shoddiness causes
problems.  

The jurisprudence in this area is extremely uneven, too.

But if you think you could sue Oracle Corp -- or even complain on a public
list about how their software ate your data -- and win that fight, I think
you need to have a long talk with your corporate counsel :)

A
-- 
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

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

Re: [GENERAL] psql lo_export documentation

[Albe Laurenz]

Jorgen Austvik wrote:
> 
> The 8.3 psql documentation says this about lo_export:
> 
[...]
>  \lo_export   Note that this is subtly different from the server function
>  lo_export, which acts with the permissions
>  of the user that the database server runs as and on the server's
>  file system.

> Below is an error message I get when I try to use lo_export 
> from client:
> 
> other_database=> SELECT lo_export(16391, '/tmp/file') FROM 
> lotest_stash_values;
> ERROR:  must be superuser to use server-side lo_export()
> HINT:  Anyone can use the client-side lo_export() provided by libpq.
> 
> With experiments I find that lo_export on 8.3 stores the files on the 
> database server file system. I also think it would be good if the 
> documentation said that you need to be database superuser to use this

I think you are confusing lo_export(), the server function,
and \lo_export, the psql command.

See the documentation you quoted above and also
http://www.postgresql.org/docs/8.2/static/lo-funcs.html

The function writes to a file on the database server, while the
psql command writes to a file on the database client.

The behaviour has not changed in 8.3, as far as I know.

Yours,
Laurenz Albe

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

[GENERAL] scrollable cursor in functions

[Cedric Boudin]

Dear members of the list,

on a server 8.1.9 I try to do this:
++
create or replace function dummy() returns void
as
$$
DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
BEGIN

--DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
-- Set up a cursor:


-- Fetch the first 5 rows in the cursor liahona:
FETCH FORWARD 5 FROM liahona;

FETCH PRIOR FROM liahona;

-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT;

end;
$$ language plpgsql;
--
I do get:
++

ERROR:  syntax error at or near "CURSOR"
CONTEXT:  invalid type name "SCROLL CURSOR with hold FOR SELECT * FROM
album"
compile of PL/pgSQL function "dummy" near line 1
--
If I put the cursor declaration in the begin->end  block it does not matter.
If I do:
+++
BEGIN work;

DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
-- Set up a cursor:


-- Fetch the first 5 rows in the cursor liahona:
FETCH FORWARD 5 FROM liahona;

FETCH PRIOR FROM liahona;

-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT WORK;
-
I do get the expected results. Thus I conclude that the scrollable
cursors are enabled on the server.
I've seen some posts in the mailing list archives about some related
problems like
*BUG #2970
-Are scrollable cursor forbidden in *PL/pgSQL? I did not see such a restriction 
explicitly in the doc.
-If not, am I doing something wrong?
-If not, is it a bug?

have a nice day

cedric

**


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

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 09:42:53PM +0800, Lincoln Yeoh wrote:
> Found this post on Slashdot which I found interesting, any comments?

I think this person is slightly confused.

> Table inheritence doesn't even make sense. Tables are analogous to 
> relations.
> All relations are the same type, the relation type (think "set" or "array" 
> to
> make it easier). How can one value of a type (one table) be a subtype of
> another value (another table)? 

Easy, by having the columns of one table be a subset of the columns or
another table. Perhaps someone should point out an example, like a
table with "people" and subtables "employees" and "customers". The
subtables share the columns of the parent tables. This is nothing that
any OO language doesn't do.

> The correct way to store types and subtypes in the database is to store them
> in the columns. In other words, choose attribute VALUES from a TYPE SYSTEM.
> Nothing else in the relational model needs to be changed. Something like
> this, in hypothetical SQL-like language:

His example is a little wierd, but it is possible:

test=# create type foo as (a text, b text);
CREATE TYPE
test=# create table test( id  int4, vals foo );
CREATE TABLE
test=# insert into test values ( 4, ROW('a', 'b'));
INSERT 0 1
test=# select * from test;
 id | vals
+---
  4 | (a,b)
(1 row)

The syntax is different but the ideas are there...

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Lincoln Yeoh]

At 09:09 PM 11/30/2007, Trevor Talbot wrote:


The controller always exists, so it's not moving a point of failure;
if a controller goes you've lost the disk anyway.


Anecdotal - I have found "smart" raid controllers to fail more often 
than dumb scsi controllers (or even SATA/PATA controllers), and some 
seem more failure prone than semi-decent operating systems.


Not recommending people turn fsync off, but the O/S "always" exists, 
if it is that flaky, you might lose data anyway, so pick a better O/S.


What's more likely in most places is somebody powering down the 
server abruptly, and then fsync=off could hurt :).


Regards,
Link.


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

Re: [GENERAL] 1 cluster on several servers

[Shane Ambler]

Willy-Bas Loos wrote:

I'll take that as a "no".
What i mean is to actually run exactly one cluster (no replicated copy) on
more than one server. Of course, if that were possible, why would people
bother with replication..


What you may be thinking of is having several machines running postgres 
and reading/writing to the same "shared" filesystem?
It's not advisable to try something like that. (as a read only system it 
may work fine)


Each machine should run their own copy of postgres and have their own 
copy of the data (which also gives you redundancy) to prevent contention 
and overwriting other servers changes.
To do that you use replication of some sort. How you achieve that 
depends on your needs.


Also think that having ten machines reading and writing to the one hard 
drive (or array of drives) will not help your performance needs in any 
way. The hard drive is the slowest point of the server, you want to add 
more drives to multiply the transfer speeds to reach the performance 
required.


Say you have 10,000 clients requesting data. If you had ten machines 
reading from the same shared drive you wouldn't get better performance 
than if one machine was accessing the drive alone.
If you had ten machines with their own drives and copy of the data then 
you would be multiplying the amount of data sent out by ten.


Slony would be setup with one server that receives the insert and 
updates and copies them to the other servers that would handle selects 
from all your clients. I believe that Slony 2 is suppose to handle 
multiple masters but I don't believe it is available yet.


If you want more than one server to respond to insert and updates then 
maybe PGCluster may be closer to what you are looking for. This is a 
multi-master setup where each server commits any changes before the 
transaction is completed. This will give you each machine having 
identical copies of data to work with.

Cybercluster appears to be a branch from PGCluster.

Bucardo is a project that has just recently been released to the 
community. It supports multi-master replication and was developed by a 
busy online store to meet their needs.


There are several commercial options available from many of the 
companies that also provide postgres support that may fit your needs 
better. EnterpriseDB, Commandprompt, Pervasive, Cybertec, Greenplum are 
the first few that come to mind.


The real question is what you want to achieve - supporting an extremely 
high number of client connections? Redundancy to prevent disaster?




I guess it is irrational to suggest that it would be possible, since each
server would at least need to have it's own copy of the DBMS software etc,
or it would cease to be a separate server.

Maybe "Data Partitioning", as in the documentation link Shane sent, possibly
combined with Slony for the other data per server, would be an option for
me.

Is there an implementation for this in PostgreSQL? It would have to be
something like pgPool (middleware), because: How would the query know on
which server to put it's data? And i guess i would need some Kerberos-like
implementation for my authentication and authorization...

cheers,

WBL


On Nov 29, 2007 1:23 PM, Shane Ambler <[EMAIL PROTECTED]> wrote:


Willy-Bas Loos wrote:

Hi,

Is it possible to run one PostgreSQL cluster on more than one (hardware)
server?

WBL


You would be looking for replication.
Start with
http://www.postgresql.org/docs/8.2/interactive/high-availability.html
to get some idea on what is available for what you wish to achieve.
Some of the projects that add these features are mentioned.




--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz






--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(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] postgresql table inheritance

[Lincoln Yeoh]

Hi,

Found this post on Slashdot which I found interesting, any comments?

--- post follows ---
by Anonymous Coward on Wed Nov 28, '07 03:23 PM (#21509173)

Speak for your database -- postgresql does.

Postgresql's "table inheritance" is a flawed concept and has nothing to do
with the *type system*. Relations contain tuples, and tuples contain
attributes, which are a name plus a VALUE. Those values are chosen from TYPES
(sets of possible values). Those types are the TYPE SYSTEM.

Table inheritence doesn't even make sense. Tables are analogous to relations.
All relations are the same type, the relation type (think "set" or "array" to
make it easier). How can one value of a type (one table) be a subtype of
another value (another table)? That's like saying, "3" is a subtype of "5",
if your types are integers. What if you use the expression "3+2" Is that "5"
still the subtype of 3? likewise, when you make complex queries with a "base"
table, does the result have any connection with the "sub" table? It's like
gobbledygook, just mashing words together without any understanding. That's
why the postgresql table inheritance concept doesn't see more widespread use.
Many people quickly discover the limitations (and incorrectly think it's just
"unfinished", when it actually is flawed).

The correct way to store types and subtypes in the database is to store them
in the columns. In other words, choose attribute VALUES from a TYPE SYSTEM.
Nothing else in the relational model needs to be changed. Something like
this, in hypothetical SQL-like language:

CREATE TABLE People ( INT id, PERSON_CLASS person )

p1 = PERSON_CLASS.new(name: "joe", etc)

p2 = CUSTOMER_CLASS.new(name: "bob", etc) // assume CUSTOMER_CLASS
subclass of PERSON_CLASS

INSERT INTO People VALUES (1, p1), (2, p2)

SELECT person FROM People WHERE person.name = "bob"

SELECT person, order FROM People JOIN Orders // can't do this in the
typical "object database"

This is a "solved problem" (see "The Third Manifesto"). It's just a matter of
getting somebody to implement it. But the vendors are clueless, thinking
object databases are a "different model" and not wanting to confuse
programmers, and programmers are clueless, not even understanding SQL or
types and values half the time, so they don't demand anything new from
vendors... we never move forward.


---(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] PostgresSQL vs Ingress

[Thomas Kellerer]

Peter Childs, 30.11.2007 14:25:
I found ingres website but no mention of a database system so I though 
they were something else that had taken on the name...


http://www.ingres.com/downloads/prod-comm-download.php

Found this using Google ;)

Thomas


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

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

Re: [GENERAL] Recheck condition

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 08:21:18AM -0500, Josh Harrison wrote:
> > > *Query1*
> > > SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
> > > INTERSECT
> > > SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')

> I get the same plan(see below)  with 'sort'  for 'intersect all' operation
> too. Why is intersect not an effecient way? Is there any other way this
> query/index can be written/created so that I can get the intersect results
> in an efficient way?

Set operations are rather inefficient. To find the intersection of two
arbitrary sets you need to sort them and compare. A query like you
write would be better expressed as a join, something like:

SELECT a.person_id 
FROM (SELECT person_id  FROM person   WHERE (column1=1 AND column2='62') a,
 (SELECT person_id  FROM person  WHERE (column1=1 AND column2='189') b
WHERE a.person_id = b.person_id;

or perhaps:

SELECT a.person_id
FROM person a, person b
WHERE a.column1=1 AND a.column2='62'
AND b.column1=1 AND b.column2='189'
AND a.person_id = b.person_id;

Which will probably generate a merge join...

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] PostgresSQL vs Ingress

[Peter Childs]

On 30/11/2007, Alexander Staubo <[EMAIL PROTECTED]> wrote:
>
> On 11/30/07, Peter Childs <[EMAIL PROTECTED]> wrote:
> >  Now We used somthing called Ingres at University (I graduated in 2000)
> but
> > I've not heard anything about it since and google does not return
> anything.
> > So we might be talking about different products?
>
> http://en.wikipedia.org/wiki/Ingres
>
> Alexander.
>

I found ingres website but no mention of a database system so I though they
were something else that had taken on the name...

The website seams to suggest a RAD development tool and middleware and use
other databases underneath.

In short the current Ingres is related to Postgresql like Xorg is related to
XFree86 or Ubuntu to Debian but much much much older.

Wikipeadia also suggests a similar relationship between Posrgresql and SQL
Server!

I guess this is one of the benifits of the BSD License.


Peter.

Re: [GENERAL] Recheck condition

[Josh Harrison]

On Nov 30, 2007 7:55 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

> Josh Harrison escribió:
>
> > Thanks...
> > I have 1 more question in the same line...
> >
> > *Query1*
> > SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
> > INTERSECT
> > SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')
>
> Hmm, I think INTERSECT (and EXCEPT) is pretty stupid in Postgres in
> general.  Maybe INTERSECT ALL could be a bit faster, because it can
> avoid the sort steps.  Make sure you eliminate duplicates if they are a
> concern.


I get the same plan(see below)  with 'sort'  for 'intersect all' operation
too. Why is intersect not an effecient way? Is there any other way this
query/index can be written/created so that I can get the intersect results
in an efficient way?
Thanks
jo

QUERY
PLAN

 
---

 SetOp Intersect All  (cost=1750719.48..1769378.35 rows=373177 width=4)
(actual time=41065.459..45469.038 rows=128562
loops=1)
   ->  Sort  (cost=1750719.48..1760048.92 rows=3731774 width=4) (actual
time=41065.375..44027.342 rows=3756726
loops=1)
 Sort Key: "*SELECT*
1".patient_id

 Sort Method:  external merge  Disk:
73432kB

 ->  Append  (cost=17886.42..1209431.67 rows=3731774 width=4)
(actual time=1445.675..30171.066 rows=3756726
loops=1)
   ->  Subquery Scan "*SELECT* 1"
(cost=17886.42..496377.90rows=381993 width=4) (actual time=
1445.674..8223.061 rows=327498 loops=1)
 ->  Bitmap Heap Scan on person   (cost=
17886.42..492557.97 rows=381993 width=4) (actual
time=1445.670..8021.006rows=327498 loops=1)
   Recheck Cond: ((column1 = 1) AND ((column2)::text
= '62'::text))
   ->  Bitmap Index Scan on person_idx  (cost=
0.00..17790.92 rows=381993 width=0) (actual
time=1440.189..1440.189rows=327498 loops=1)
 Index Cond: ((column1 = 1) AND
((column2)::text =
'62'::text))
   ->  Subquery Scan "*SELECT* 2"
(cost=156754.24..713053.77rows=3349781 width=4) (actual time=
4183.977..20195.276 rows=3429228 loops=1)
 ->  Bitmap Heap Scan on person   (cost=
156754.24..679555.96 rows=3349781 width=4) (actual
time=4183.973..18191.919rows=3429228 loops=1)
   Recheck Cond: ((column1 = 1) AND ((column2)::text
= '189'::text))
   ->  Bitmap Index Scan on person_idx  (cost=
0.00..155916.80 rows=3349781 width=0) (actual
time=4178.644..4178.644rows=3429228 loops=1)
 Index Cond: ((column1 = 1) AND
((column2)::text =
'189'::text))
 Total runtime: 45504.425 ms

[GENERAL] FK index q'n

[rihad]

Given this table:

CREATE TABLE foo (
id integer primary key,
bar_id integer references bar (id)
);
and provided that bar.id is itself a PK, do I still need to create an 
index on bar_id if often doing queries like:

SELECT MIN(id) FROM foo WHERE bar_id IS NULL;

Table foo will contain a static number of rows (from 2,000 to 10,000 -- 
yet undecided) only doing SELECT & UPDATE.


Thanks.

---(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] PostgresSQL vs Ingress

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> --> It provides a feel-good feeling knowing that a big company, after
> having paid XXX amount on it, the solution will not die in X number of
> years leaving the customer stranded.

That's a valid concern. Not sure having a roadmap really correlates 
with long-term existence, but it's a logical concern for companies.

> 2. Accountability
> --> Community Owned/Control = no specific person to sue. (and we all
> know how enterprises are sue-happy
> --> The want a scapegoat. With Community, who's to be sued? (unless of
> course they buy from a company such as MySQL or EnterpriseDB, they can
> most certainly sue them for moolah)

No, they want someone to call when things go wrong, not someone to sue 
or a scapegoat. Please don't perpetuate this urban myth. No companies are 
suing Oracle and Microsoft because of their products, and companies have 
no expectation of doing so. It might be nice if they did, and some theorize 
it would lead to better and more secure products, but the reality is that 
with software, you are on your own. Any company telling you otherwise as 
a reason not to use open source is lying.


- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200711300817
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFHUA4rvJuQZxSWSsgRAxioAKDNTUm9qjQfpEY2AC21RWIUAi5MTwCfWvH0
LalLG2NS42YJcxUdzZESx1Y=
=cfxk
-END PGP SIGNATURE-



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

   http://archives.postgresql.org/

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Trevor Talbot]

On 11/30/07, Wolfgang Keller <[EMAIL PROTECTED]> wrote:

> > For example, if you have an application that needs high
> > database write throughput, to make that work well with PostgreSQL you
> > must have a controller with a battery backed cache.

> Hmm, what would be the difference compared to plenty of RAM and a UPS
> (plus stand-by backup server)? Looks just like moving the "single point
> of failure" to adifferent hardware item, no...?

Well, you want a backup server anyway, for completely different
reasons. It's not relevant to write throughput.

The difference between using a disk controller with a BBC compared to
just turning fsync off and using RAM is that you've introduced an
additional point of failure: the OS itself. You have to trust that the
OS is always going to be able to write the cached data to disk. That
tends to be riskier than relying on a piece of hardware dedicated to
the job, simply because an OS does more, and therefore has more to go
wrong (kernel panic / grey screen / BSOD).

You could make similar arguments about the additional hardware
components in the chain, like the internal power supply. The point is
that the database expects that when it asked for data to hit disk, it
actually got there. A BBC allows a disk controller to lie (reliably),
but turning fsync off allows pretty much everything from the OS down
to lie (somewhat less reliably).

The controller always exists, so it's not moving a point of failure;
if a controller goes you've lost the disk anyway.

The tradeoff is how much trust you're willing to put into various
parts of the system being uninterrupted.

---(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] invalid byte sequence for encoding "UTF8"

[Albe Laurenz]

Glyn Astill wrote:
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.
> 
> We have a migration tool from mertechdata.com to convert these files
> that are in a DataFlex format into out postgres tables.

In which format are the data? Text files? SQL statements?
Something binary?

> Some files convert over okay, and some come up with the error message
> 'invalid byte sequence for encoding "UTF8"'. the files that come up
> with the error are created correctly and so are their index's, but as
> soon as we come to insert the data we get this error.

Well, so you claim, but can you prove it?
Do you use a PostgreSQL utility to import the data?
If yes, which tool? What is the exact command line?

> Does anyone know why we're getting this error message? And uis there
> a way to suppress it, or can we get around it using another format?

By "format" I believe that you mean "encoding".
It does not matter what encoding you use as long as the data can
be represented in it, you tell PostgreSQL what the encoding is, and
the data are correct.

There is no advantage of one encoding over the other in this respect.

> Our migration utility does ask us to select the correct encoding for
> our database, and we select UTF8 but we still get the error. What do
> you guys think? Possibly the migration tools fault?

If PostgreSQL says that the data is not UTF-8, we tend to believe it.

To say more, one would need more information.
Can you identify the string about which PostgreSQL complains?
What does it look like?

> I thought we may be able to get around it using SQL_ASCII encoding -
> but it's ony 7 bit, so would we loose some data? Also our conversion
> utility doesn't have the option to use SQL_ASCII.

If you use SQL_ASCII you may succeed in getting the incorrect data into
the database, but that will not make you happy because the data will
not stop being incorrect just because they are in the database.

Yours,
Laurenz Albe

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

Re: [GENERAL] PostgresSQL vs Ingress

[Alexander Staubo]

On 11/30/07, Peter Childs <[EMAIL PROTECTED]> wrote:
>  Now We used somthing called Ingres at University (I graduated in 2000) but
> I've not heard anything about it since and google does not return anything.
> So we might be talking about different products?

http://en.wikipedia.org/wiki/Ingres

Alexander.

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

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

Re: [GENERAL] PostgresSQL vs Ingress

[Peter Childs]

On 30/11/2007, Ow Mun Heng <[EMAIL PROTECTED]> wrote:
>
> I was browsing the net yesterday after reading through the thread on PG
> vs Informix and I was curious as to Ingress.
>
> Ingress is also an open source RDBM (and DataWarehouseing) and I'm
> wondering if anyone here has anything to say about it. They also offer
> community editions but I've not gone to see how much it differs/offers
> compared to PG.
>
> I've tried to DL the community edition, but upon log-in, I only get a
> blank page. (tried on both firefox and opera)
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>


I think this can be answered easily see
http://www.postgresql.org/about/history.  Ingres  was postgresql about 22
years ago!

Now We used somthing called Ingres at University (I graduated in 2000) but
I've not heard anything about it since and google does not return anything.
So we might be talking about different products?

So I'm slightly confused.

Peter Childs

Re: [GENERAL] Recheck condition

[Alvaro Herrera]

Josh Harrison escribió:

> Thanks...
> I have 1 more question in the same line...
> 
> *Query1*
> SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
> INTERSECT
> SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')

Hmm, I think INTERSECT (and EXCEPT) is pretty stupid in Postgres in
general.  Maybe INTERSECT ALL could be a bit faster, because it can
avoid the sort steps.  Make sure you eliminate duplicates if they are a
concern.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"[PostgreSQL] is a great group; in my opinion it is THE best open source
development communities in existence anywhere."(Lamar Owen)

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

Re: [GENERAL] Recheck condition

[Josh Harrison]

On Nov 29, 2007 8:15 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

> Josh Harrison escribió:
> > >
> > > > For example if I have a table Person with 3 fields
> (name,city_id,age).
> > > And
> > > > the table contains 1000 rows. The table has 2 indexes city_id and
> age
> > > > If I have a query :
> > > > SELECT * FROM PERSON WHERE city_id=5 AND AGE=30
> >
> > OkaySo If I have a query like the above and the query plan shows  a
> > 'recheck condition' and bitmap scan, then does that mean it scans the
> > indexes first to get the intermediate results and goto the heap only for
> the
> > final data?
>
> Yes.
>
> If the table actually contains 1000 rows, the most likely outcome is
> that the bitmaps would not be lossy and therefore no rechecking is
> needed at all.  (Tuple bitmaps become lossy only if they have to store a
> lot of tuples, in which case they forget the idea of storing each tuple,
> and instead "compress" the representation to storing only the page
> numbers where matching tuples are to be found).
>
> Note however, that even if the bitmaps are not lossy, the visit to the
> heap is still required, because the need to check for visibility.
>
Thanks...
I have 1 more question in the same line...

*Query1*
SELECT person_id  FROM person   WHERE (column1=1 AND column2='62')
INTERSECT
SELECT person_id  FROM person  WHERE (column1=1 AND column2='189')

There is an index created as person_idx(column1,column2)

QUERY
PLAN

 
---

 SetOp Intersect  (cost=1750719.48..1769378.35 rows=373177 width=4) (actual
time=42913.626..47247.650 rows=6352
loops=1)
   ->  Sort  (cost=1750719.48..1760048.92 rows=3731774 width=4) (actual
time=42913.537..45838.472 rows=3756726
loops=1)
 Sort Key: "*SELECT*
1".patient_id

 *Sort method: external merge Disk:73432kB *

 ->  Append  (cost=17886.42..1209431.67 rows=3731774 width=4)
(actual time=1474.995..32215.493 rows=3756726
loops=1)
   ->  Subquery Scan "*SELECT* 1"
(cost=17886.42..496377.90rows=381993 width=4) (actual time=
1474.993..4936.240 rows=327498 loops=1)
 ->  Bitmap Heap Scan on person   (cost=
17886.42..492557.97 rows=381993 width=4) (actual
time=1474.990..4735.972rows=327498 loops=1)
   Recheck Cond: ((column1 = 1) AND ((column2)::text
= '62'::text))
   ->  Bitmap Index Scan on person_idx  (cost=
0.00..17790.92 rows=381993 width=0) (actual
time=1469.508..1469.508rows=327498 loops=1)
 Index Cond: ((column1 = 1) AND
((column2)::text =
'62'::text))
   ->  Subquery Scan "*SELECT* 2"
(cost=156754.24..713053.77rows=3349781 width=4) (actual time=
4142.577..25518.305 rows=3429228 loops=1)
 ->  Bitmap Heap Scan on person   (cost=
156754.24..679555.96 rows=3349781 width=4) (actual
time=4142.573..23493.596rows=3429228 loops=1)
   Recheck Cond: ((column1 = 1) AND ((column2)::text
= '189'::text))
   ->  Bitmap Index Scan on person_idx  (cost=
0.00..155916.80 rows=3349781 width=0) (actual
time=4136.948..4136.948rows=3429228 loops=1)
 Index Cond: ((column1 = 1) AND
((column2)::text =
'189'::text))
 Total runtime: 47250.501 ms


**
Question:
In this query Intersection is used. How does postgres handle this? The steps
in the above query are
1.find all tuples that match column1=1 AND column2='62'
2. find all tuples that match column1=1 AND column2='189'
3. Find the intersection of the above 2
Does it go to the heap even to get the intermediate results (1 & 2) ?
or
Does it do the first 2 steps using index and go to the heap for the final
data?

Also what does *Sort method: external merge Disk:73432kB  *mean?  Should I
have to modify this to make this query run faster? Postgres takes 4 times
slower than Oracle to return this query.  Is there a way to make this
faster?

Thanks
jo

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Wolfgang Keller]

Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows
(2000,  XP, Vista etc.) on the same hardware? And Linux to
(Free-/Net-/whatever)  BSD?


Apple hardware gets so expensive for some types of database
configurations that such a comparision doesn't even make a lot of
sense.


So far my experience with the effective price/performance ratio of 
Apple vs. other Hardware for my applications has been pretty good. E.g. 
it was impossible for me to find a similarly priced 
(Linux-/*BSD/Intel/AMD-)equivalent to my PowerMac G5 over here at the 
time when I bought it.


Not to mention the required learning effort for Linux/*BSD compared to 
MacOS X, if I count it in (days x day rate)...



For example, if you have an application that needs high
database write throughput, to make that work well with PostgreSQL you
must have a controller with a battery backed cache.


Hmm, what would be the difference compared to plenty of RAM and a UPS 
(plus stand-by backup server)? Looks just like moving the "single point 
of failure" to adifferent hardware item, no...?



 If I have a PC,
the entry-level solution in that category can be a random sub-$1000
system that runs Linux


Can't find one over here for that price that does all the other things 
that need to be done in a typicle small office (fileserver, 
printserver, mailserver, calendar server,...) similarly well as my old 
G5 PowerMac. To turn this one into a part-time DB server, I'd just plug 
in an eSATA or SAS array (with PCIe adapter) and maybe another few GB 
of RAM (currently 4). Plus a backup tape drive.


My world are environments with not more than at most 10 concurrent 
database clients at any given moment. But those won't want to wait, 
because they need to get actual work done.



plus around $400 for a RAID card with BBC, and
you've got multiple vendors to consider there (3Ware, Areca, LSI
Logic, etc.)


LSI drivers are not available for MacOS X on PowerMacs? Ouch.


Also, in previous generations, the Mach kernel core of Mac OS had
some serious performance issues for database use even in read-heavy
workloads: http://www.anandtech.com/mac/showdoc.aspx?i=2520&p=5


"With the MySQL performance woes now clearly caused by OS X"

Erm, systematic error here: It could also be that the MySQL 
implementation/configuration for the two different OSes was the source 
for the performance difference.


I wouldn't use MySQL anyway, and I'm mostly interested in transaction 
performance (client waiting time until commit).



I'm just wondering whether the performance gain is worth the
learning  effort required for Linux or BSD compared to the Mac.


On both Windows (where you get limitations like not being able to set
a large value for shared_buffers)


My consistent experience with Windows over the last >15 years has been 
that it just won't multitask anymore as soon as one process does 
significant I/O. No matter what hardware you put underneath.



and Mac OS X, PostgreSQL has enough
performance issues that I feel using those plaforms can only be
justified if platform compatibility is more important than
performance to you.


The point is that cost for "installation", "configuration" and 
"administration" must be taken into account. A dedicated individual 
just for that is simply out of question in this world where I live. So 
someone who's already available has to do all that in a (as tiny as 
possible) fraction of his/her worktime. With MacOS X it's feasible, but 
Linux/*BSD? I'm not so sure.


Sincerely,

Wolfgang Keller

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

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

8.2  /  Windows   (a development-only pc)

On Nov 30, 2007 12:50 AM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
> > However, I have found that my record variable is not assigned proper
> > field-level datatypes.  As a result, I'm unable to write basic math
> > calcs in pg/sql without a lot of typecasting.
>
> What version are you running. On my 8.1.9 test system it returns -1.00
> as expected.
>
> Have a nice day,
> --
> Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> > Those who make peaceful revolution impossible will make violent revolution 
> > inevitable.
> >  -- John F Kennedy
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHT87KIB7bNG8LQkwRAuzqAJ9jdaDv/rxz5pG8bdYvO9suxZLGZACeL6BY
> ZMvLJ5nKREBIsBrdk4nE748=
> =/aEm
> -END PGP SIGNATURE-
>
>

---(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] Moving lock file (/tmp/.s.PGSQL.)

[Tomasz Ostrowski]

On Fri, 30 Nov 2007, Madison Kelly wrote:

>   If there a ./configure switch (or config file/command line switch) to 
> tell postgresql to put the lock file '.s.PGSQL..lock' and socket 
> '.s.PGSQL.' in a different directory?

There's no ./configure option, so you should change
DEFAULT_PGSOCKET_DIR in ./src/include/pg_config_manual.h
If every client is linked (preferably dynamically) with libpq, which
is compiled with changed DEFAULT_PGSOCKET_DIR, then this would work
with no special configuration.

Alternatively there's a command line switch "-k" or
"unix_socket_directory" config option for postgres server:
http://www.postgresql.org/docs/8.2/interactive/runtime-config-connection.html

And PGHOST environment variable for clients:
http://www.postgresql.org/docs/8.2/interactive/libpq-envars.html

Regards
Tometzky
-- 
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
  Winnie the Pooh

---(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] Moving lock file (/tmp/.s.PGSQL.)

[A. Kretschmer]

am  Fri, dem 30.11.2007, um  5:22:34 -0500 mailte Madison Kelly folgendes:
> Hi all,
> 
>   If there a ./configure switch (or config file/command line switch) to 
> tell postgresql to put the lock file '.s.PGSQL..lock' and socket 
> '.s.PGSQL.' in a different directory?
> 
> Thanks all!

Option unix_socket_directory in file postgresql.conf.



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

[GENERAL] psql lo_export documentation

[Jorgen Austvik - Sun Norway]

Hi,

The 8.3 psql documentation says this about lo_export:

--8<8<8<8<8<8<--
  
\lo_export class="parameter">loid class="parameter">filename




Reads the large object with OID loid from the database and
writes it to filename. Note that this is
subtly different from the server function
lo_export, which acts with the permissions
of the user that the database server runs as and on the server's
file system.



Use \lo_list to find out the large object's
OID.



  
--8<8<8<8<8<8<--

Below is an error message I get when I try to use lo_export from client:

--8<8<8<8<8<8<--
other_database=> SELECT lo_export(16391, '/tmp/file') FROM 
lotest_stash_values;

ERROR:  must be superuser to use server-side lo_export()
HINT:  Anyone can use the client-side lo_export() provided by libpq.
--8<8<8<8<8<8<--

With experiments I find that lo_export on 8.3 stores the files on the 
database server file system. I also think it would be good if the 
documentation said that you need to be database superuser to use this


Something along the lines of this?

  Note that this acts with the permissions of the user that the
  database server runs as and on the server's file system. Therefore you
  have to be a database superuser to be allowed to use this function.

On 8.2 I get this on client side import, this is fixed in 8.3.
--8<8<8<8<8<8<--
other_database=> INSERT INTO lotest_stash_values (loid) SELECT 
lo_import('/export/home/ja155679/random_data.dat');

ERROR:  must be superuser to use server-side lo_import()
HINT:  Anyone can use the client-side lo_import() provided by libpq.
--8<8<8<8<8<8<--

So, on 8.2 it looks for me like you had to be database superuser to be 
able to run lo_import, and that files would import from the server, and 
that that now is changed in 8.3 so that the files are imported from the 
client.


Do we really want lo_import and lo_export to work on different file systems?

-J
--

Jørgen Austvik, Software Engineering - QA
Sun Microsystems Database Technology Group

begin:vcard
fn;quoted-printable:J=C3=B8rgen Austvik
n;quoted-printable:Austvik;J=C3=B8rgen
org:Sun Microsystems;Database Technology Group
adr:;;Haakon VIII gt. 7b;Trondheim;;NO-7485;Norway
email;internet:[EMAIL PROTECTED]
title:Senior Engineer
tel;work:+47 73 84 21 10 
tel;fax:+47 73 84 21 01
tel;cell:+47 901 97 886
x-mozilla-html:FALSE
url:http://www.sun.com/
version:2.1
end:vcard


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

[GENERAL] Moving lock file (/tmp/.s.PGSQL.)

[Madison Kelly]

Hi all,

  If there a ./configure switch (or config file/command line switch) to 
tell postgresql to put the lock file '.s.PGSQL..lock' and socket 
'.s.PGSQL.' in a different directory?


Thanks all!

Madi

---(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] invalid byte sequence for encoding "UTF8"

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 09:44:36AM +, Glyn Astill wrote:
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.

Your problem is that there is no such thing as "8-bit ASCII". Determine
what encoding the data is actually in and use that.

> Our migration utility does ask us to select the correct encoding for
> our database, and we select UTF8 but we still get the error. What do
> you guys think? Possibly the migration tools fault?

I think they mean to select the correct encoding for the data, what
encoding the database is in isn't relevent. The database can convert
any encoding you want to use to UTF-8 as required.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] pgcrypto functions fail for asymmetric encryption/decryption

[Marko Kreen]

On 11/29/07, Stefan Niantschur <[EMAIL PROTECTED]> wrote:
> I have a table with userids and public keys. I want to write a function
> which does a select and returns the result pgp encrypted.
>
> However, I have some problems:

Could you send the keys you have problems with?  If actual keys
then ofcourse generate temp-keys instead.

Or at least send key parameters (gpg --list-keys output).

Also I need PostgreSQL version, if its compiled with OpenSSL,
then OpenSSL version, your OS and CPU info, just in case.
Compiler + compiler options maybe too.

> SELECT encode(decode((SELECT ens_pubkey FROM
> ens_user)::text,'escape'),'escape'::text)::text;
> -> returns the public key, => ok
>
> SELECT armor(dearmor((SELECT ens_pubkey FROM ens_user)::text));
> -> returns the key in a different format, => problem

You mean it gives fixed header?  Both pgcrypto and gpg ignore
it anyway, so I did not bother guessing it.  But if it really
causes problems (doubtful) it can be fixed by looking at data.

> SELECT
> armor(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT
> ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
> -> returns a pgp-encrypted message which cannot be decrypted by GnuPG,
> => problem

This query does not parse, but if I remove the bytea case it works.

How does GnuPG fail?

> SELECT
> pgp_pub_decrypt(dearmor(armor(pgp_pub_encrypt(armor(pgp_sym_encrypt('geheim'::text,'test'::text)),dearmor((SELECT
> ens_pubkey FROM ens_user WHERE ens_userid =
> 10112)::text,dearmor((SELECT ens_privkey FROM ens_user WHERE
> ens_userid = 10112)::text),'test'::text);
> -> returns 'ERROR: Corrupt data' => problem

Works for me.

> SELECT
> pgp_key_id(pgp_pub_encrypt_bytea(armor(pgp_sym_encrypt('geheim'::text,'test'::text))::bytea,dearmor((SELECT
> ens_pubkey FROM ens_user WHERE ens_userid = 10112)::text)));
> -> returns the correct key id of the deployed public key
>
> So, if I cannot decrypt the message which I have been encrypting with
> the appropriate keys, how can I proceed?
>
> I want to encrypt messages in postgres and decrypt it elsewhere,
> However, the result of the encryption algorithm seems to deliver a
> wrong result. Otherwise I cannot explain why encrypting and immidiately
> decrypting the message fails.
>
> The same proceeding is succesful when using symmetric keys:
> SELECT
> pgp_sym_decrypt((pgp_sym_encrypt('geheim'::text,'test'::text)),'test'::text);
> -> returns 'geheim' which is the encrypted and then again decrypted
> message.
>
> What did I wrong when trying to use asymmetric encryption?

Generally the stuff you try should work, although some of the
dermor(armor()) and pgp_pub_encrypt(pgp_sym_encrypt()) stuff
seem to be excessive.

So either you have found a bug in pgcrypto which is dependant
on public key algo/OS/CPU/OpenSSL/compiler details or you
have some mistake on your own (eg, your private and public key
does not match).

So I need more details to understand your problem.

-- 
marko

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

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

Re: [GENERAL] invalid byte sequence for encoding "UTF8"

[Gregory Stark]

[Generally it's not a good idea to start a new thread by responding to an
existing one, it confuses people and makes it more likely for your question to
be missed.]


"Glyn Astill" <[EMAIL PROTECTED]> writes:

> Hi People,
>
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.

ASCII is a 7-bit encoding. If you have bytes with the high bit set then you
have something else. Can you give any examples of characters with the high bit
set and what you think they represent?

> We have a migration tool from mertechdata.com to convert these files
> that are in a DataFlex format into out postgres tables.
>
> Some files convert over okay, and some come up with the error message
> 'invalid byte sequence for encoding "UTF8"'. the files that come up
> with the error are created correctly and so are their index's, but as
> soon as we come to insert the data we get this error.

This error indicates that you are trying to import data with client_encoding
set to UTF8 but the data isn't actually UTF8 and contains invalid byte
sequences for UTF8.

If your migration toolkit lets you set the client encoding separately from the
server encoding then you can set the client encoding to match your data and
the server encoding to the encoding you want the server to use. 

Otherwise you'll have to recode the data to UTF8 or whatever encoding you want
the data to be. There are tools to do this (such as GNU "recode" for example).


> Are there any more flexible formats we could use? I noticed we have
> Latin 1-10 and ISO formats. Is there any reason why we shouldn't use
> these?

Well there are pros and cons. The 1-byte ISO formats will be more space
efficient and also allow some cpu optimizations so they perform somewhat
better. But if you ever need to store a character which doesn't fit in the
encoding you'll be stuck. Postgres doesn't support using multiple encodings in
the same database (or effectively even in the same initdb cluster).

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

   http://archives.postgresql.org/

Re: [GENERAL] invalid byte sequence for encoding "UTF8"

[Usama Dar]

On 11/30/07, Glyn Astill <[EMAIL PROTECTED]> wrote:
>
> Hi People,
>
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.
>
> We have a migration tool from mertechdata.com to convert these files
> that are in a DataFlex format into out postgres tables.
>
> Some files convert over okay, and some come up with the error message
> 'invalid byte sequence for encoding "UTF8"'. the files that come up
> with the error are created correctly and so are their index's, but as
> soon as we come to insert the data we get this error.
>
> Does anyone know why we're getting this error message? And uis there
> a way to suppress it, or can we get around it using another format?
>
> Our migration utility does ask us to select the correct encoding for
> our database, and we select UTF8 but we still get the error. What do
> you guys think? Possibly the migration tools fault?
>
> I thought we may be able to get around it using SQL_ASCII encoding -
> but it's ony 7 bit, so would we loose some data? Also our conversion
> utility doesn't have the option to use SQL_ASCII.
>
> Are there any more flexible formats we could use? I noticed we have
> Latin 1-10 and ISO formats. Is there any reason why we shouldn't use
> these?
>
> Thanks
> Glyn



Latin1 is a single byte encoding,  i can't think of any reason not to try it
if the characters you have are valid ISO8859 characters. Probably posting
the hex codes of some characters which are failing will help.

>  ___
> Yahoo! Answers - Got a question? Someone out there knows the answer. Try
> it
> now.
> http://uk.answers.yahoo.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
>



-- 
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Dave Page]

Laurent Duperval wrote:

Does this allow creating more connections? At some point, the discussion
became too technical for me, and I no longer could tell if the answer was
for developers of for users.


Yeah, it did become something of an investigation into the problem which 
probably should have been moved to -hackers.


I summarised the info in the FAQ 
http://www.postgresql.org/docs/faqs.FAQ_windows.html#4.4 for user 
consumption, and included a link to the MS Knowledgebase article that 
shows what to tweak in the registry.



I saw other messages dealing with semaphores/connection relations, etc.
But unless I really did not understand the discussion, none of them seemed
to address the issue I was seeing.


Yes, that was all about how we were using threads to manage interprocess 
communications. We found a far more efficient way to do that, but my 
guess is that thats not your problem.



I'm thinking that the Java driver combined with Hibernate may be keeping
handles open for too long, because my tests aren't supposed to maintain
connections open for very long. I also would expect the connections to
either be closed or released once the statements are executed.


That could be an issue with Hibernate or the other code you're running, 
but yes, if it's opening lots of connections and keeping them open that 
could be what's wrong and I would suggest checking the FAQ above.


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

[GENERAL] invalid byte sequence for encoding "UTF8"

[Glyn Astill]

Hi People,

I've setup a postgres 8.2 server and have a database setup with UTF8
encoding. I intend to read some of our legacy data into the table,
this legacy data is in ASCII format, and as far as I know is 8 bit
ASCII.

We have a migration tool from mertechdata.com to convert these files
that are in a DataFlex format into out postgres tables.

Some files convert over okay, and some come up with the error message
'invalid byte sequence for encoding "UTF8"'. the files that come up
with the error are created correctly and so are their index's, but as
soon as we come to insert the data we get this error.

Does anyone know why we're getting this error message? And uis there
a way to suppress it, or can we get around it using another format?

Our migration utility does ask us to select the correct encoding for
our database, and we select UTF8 but we still get the error. What do
you guys think? Possibly the migration tools fault?

I thought we may be able to get around it using SQL_ASCII encoding -
but it's ony 7 bit, so would we loose some data? Also our conversion
utility doesn't have the option to use SQL_ASCII.

Are there any more flexible formats we could use? I noticed we have
Latin 1-10 and ISO formats. Is there any reason why we shouldn't use
these?

Thanks
Glyn


  ___
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.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] Threads limit for postmaster

[Magnus Hagander]

> > > Hi,
> > > I have a problem when a lot of connexions are opened on postgresql.
> > >
> > > When 243 threads have been created by postmaster, the next CreateThread
> > > fails and the following error is written in pglogs : "FATAL:  could not
> > > create sigchld waiter thread: error code 8".
> > >
> > > I need to open more than 300 connexions on my server.
> > > Is there anyone to help me ?
> > >
> > > OS : windows 2003 server x64 edition.
> >
> > This is a known limitation and will be fixed in 8.3. We'll consider
> > backporting this to 8.2 once it has been proven in the field. So if you
> > can, please download the latest beta of 8.3 and give that a try.
> >
> > //Magnus
> 
> 
> 
> Would increasing the Desktop shared memory size, as described in another
> thread , by changing a registry setting would help his case?
> 

I don't think so. This looks like the running out of address-space in the 
postmaster problem, which is a different one.

/Magnus


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

   http://archives.postgresql.org/

Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing

[Usama Dar]

On 11/29/07, Ragnar Heil <[EMAIL PROTECTED]> wrote:
>
> Hi
>
> our customer has got the following requirements:
>
> Req1) Master master replication supported, not only master / slave
> replication with only the master being writable. If you do have multiple
> slave systems they are only useful from a backup and standby
> perspective.  Our Application must have a db-connection it can write to.
>
> Req2) Replication of schema should also be possible, not only data
>
> Req3) Not only a hot-standby-solution is needed. Load Balancing is
> wanted for the future.
>
>
> Currently I am looking at EnterpriseDB but it seems that they dont
> support multiple master-replication
>
> best regards
> Ragnar
>
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>   http://archives.postgresql.org/
>

Have you looked at pgCluster or Continuent's uni/Cluster?

-- 
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: [GENERAL] Threads limit for postmaster

[Usama Dar]

On 11/30/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
>
> Guillaume Pungeot wrote:
> > Hi,
> > I have a problem when a lot of connexions are opened on postgresql.
> >
> > When 243 threads have been created by postmaster, the next CreateThread
> > fails and the following error is written in pglogs : "FATAL:  could not
> > create sigchld waiter thread: error code 8".
> >
> > I need to open more than 300 connexions on my server.
> > Is there anyone to help me ?
> >
> > OS : windows 2003 server x64 edition.
>
> This is a known limitation and will be fixed in 8.3. We'll consider
> backporting this to 8.2 once it has been proven in the field. So if you
> can, please download the latest beta of 8.3 and give that a try.
>
> //Magnus



Would increasing the Desktop shared memory size, as described in another
thread , by changing a registry setting would help his case?



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



-- 
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: [GENERAL] can't createdb with my new user

[Usama Dar]

On 11/28/07, della <[EMAIL PROTECTED]> wrote:
>
> hi, I describe my steps:
>
> 1. installed postgresql 8.2 via apt-get (ubuntu gutsy)
> 2. # sudo -u postgres psql template1
> 3. template1=# CREATE ROLE kokomo WITH LOGIN CREATEDB PASSWORD
> 'kokomo';
> 4. template1=# \q
> 5. # createdb kokomo_dev -U kokomo -W
>
> Here I receive:
> createdb: could not connect to database postgres: FATAL:  Ident
> authentication failed for user "kokomo"
>
> So, given that I do not fail submitting the password for kokomo when
> requested... what could be the problem? Have I to create a system user
> for kokomo?
> If I use pgadmin3 I see the kokomo user with it own encrypted password
> and I can create the DB with kokomo as a owner. So what am I missing
> in the command line?
>
> Thanks for help.



Looks like you have a Database user kokomo but since your authentication
method in pg_hba.conf is set to ident, which means database user is mapped
to an OS user, and you need to be logged in as that OS user on which a DB
user is mapped. By default the mapping is on "sameuser" which means inorder
to logon as kokomo db user you will have to be authenticated /logged in as
kokomo OS user.

Change the pg_hba.conf auth method to trust or password


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




-- 
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: [GENERAL] Record variable not behaving as expected (bug?)

[Martijn van Oosterhout]

On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
> However, I have found that my record variable is not assigned proper
> field-level datatypes.  As a result, I'm unable to write basic math
> calcs in pg/sql without a lot of typecasting.

What version are you running. On my 8.1.9 test system it returns -1.00
as expected.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

[GENERAL] Record variable not behaving as expected (bug?)

[Postgres User]

According to the docs, record variables "take on the actual row
structure of the row they are assigned during a SELECT or FOR
command."

However, I have found that my record variable is not assigned proper
field-level datatypes.  As a result, I'm unable to write basic math
calcs in pg/sql without a lot of typecasting.

When I execute the function below, a basic math statement fails unless
I explicitly typecast the record's field values.  This isn't what I
expected; Postgresql should correctly typecast each field in the
record var automatically at the SELECT statement.
(Note: did not test with a row variable, and I prefer to use the
record datatype)

CREATE TABLE table2 (
  "s_val" NUMERIC(6,2),
  "e_val" NUMERIC(6,2)
);

CREATE FUNCTION divide () RETURNS numeric AS
$body$
declare
   retval numeric(6,2);
   rec record;
begin
   SELECT * INTO rec FROM test.table2 LIMIT 0;
   rec.s_val = 100.0;
   rec.e_val = 101.0;

   -- returns correct value w/ casting:
   --retval = ((rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2)) * 100;

   -- returns incorrect value, as if fields have invalid datatypes:
   retval = ((rec.s_val - rec.e_val) / rec.s_val) * 100;

   return retval;
end
$body$
LANGUAGE 'plpgsql';

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

Re: [GENERAL] Simple math statement - problem

[Postgres User]

The problem turned out to be related to my function..

Given this table:

CREATE TABLE "table2" (
  "s_val" numeric(6,2),
  "e_val" numeric(6,2)
) WITH OIDS;

The following functions of code will set retval = NULL;

declare
   retval numeric(6,2);
   rec record;
begin
   SELECT * INTO rec FROM table2 LIMIT 0;
   rec.s_val = 100;
   rec.e_val = 101;
   retval = (rec.s_val - rec.e_val) / rec.s_val;

   return retval;
end

However, if I explicitly typecast, then it returns the proper value:
retval = (rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) /
rec.s_val::numeric(6,2);

On Nov 29, 2007 9:47 PM, Gregory Williamson
<[EMAIL PROTECTED]> wrote:
>
>
> A quick experiment shows that if either numerator or denominator are
> decimal, that is preserved in the end result. Probably true for basic math
> operations in general.
>
> GW
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Gregory Williamson
> Sent: Thu 11/29/2007 10:37 PM
> To: Postgres User; pgsql-general
> Subject: Re: [GENERAL] Simple math statement - problem
>
> The question:
> >
> > How can I write statements that returns a decimal?
> >
> >
> billing=# select 1/100;
>  ?column?
> --
> 0
> (1 row)
>
> As you said ...
>
> So make everything decimal:
> billing=# select 1.0/100.0;
> ?column?
> 
>  0.0100
>
> Or:
> billing=# select 1::decimal/100::decimal;
> ?column?
> 
>  0.0100
>
> I think that when you use integers you lose precision right out the gate.
> Others can provide better insight I hope ...
>
> HTH,
>
> Greg Williamson
> Senior DBA
> GlobeXplorer LLC, a DigitalGlobe company
>
> Confidentiality Notice: This e-mail message, including any attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> and privileged information and must be protected in accordance with those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by reply e-mail and destroy all copies of the original message.
>
> (My corporate masters made me say this.)
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Gregory Williamson
> Sent: Thu 11/29/2007 10:37 PM
> To: Postgres User; pgsql-general
> Subject: Re: [GENERAL] Simple math statement - problem
>
> The question:
> >
> > How can I write statements that returns a decimal?
> >
> >
> billing=# select 1/100;
>  ?column?
> --
> 0
> (1 row)
>
> As you said ...
>
> So make everything decimal:
> billing=# select 1.0/100.0;
> ?column?
> 
>  0.0100
>
> Or:
> billing=# select 1::decimal/100::decimal;
> ?column?
> 
>  0.0100
>
> I think that when you use integers you lose precision right out the gate.
> Others can provide better insight I hope ...
>
> HTH,
>
> Greg Williamson
> Senior DBA
> GlobeXplorer LLC, a DigitalGlobe company
>
> Confidentiality Notice: This e-mail message, including any attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> and privileged information and must be protected in accordance with those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by reply e-mail and destroy all copies of the original message.
>
> (My corporate masters made me say this.)
>
>
>
>

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