Re: [GENERAL] query performance

[Cox, Brian]

> [ scratches head... ]  Your example command works as expected for me.

> [ rereads thread... ]  Oh, you're running 8.1.  I think you have to
> do the command as a superuser to get that output in 8.1.  Later versions
> are less picky.

Yes, with the right incantations, the FSM information does appear. The perils
of being a bit behind the times, I guess.

Thanks for your help,
Brian

Re: [GENERAL] query question really cant give a summary here so read the body ;-)

[Joshua D. Drake]

Erik Jones wrote:


Next time I'll hold your hand a bit more, but yesterday I was very far
out of it (I'm not exactly 100% today either) with a bad head cold.
Now, should we have more exchanges to determine who can use the most
flowery of speech or should we talk pgsql and schema changes?


Perhaps his db-fu has yet to bud?


/me throws on some compost.

Joshua D. Drake

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

Re: [GENERAL] query question really cant give a summary here so read the body ;-)

[Erik Jones]

On Apr 24, 2008, at 5:43 PM, Scott Marlowe wrote:

On Thu, Apr 24, 2008 at 3:43 PM, Rhys Stewart  
<[EMAIL PROTECTED]> wrote:


type). That being said, I would appreciate that any further  
questions I have
not be responded to by single line emails extolling the virtues of  
properly

designed schemata, normalization or the like.


Well, I would appreciate getting shorter replies that are to the point
and don't rely on standing on soap boxes and using exercises in
polemics to make their point, but I probably won't get that.

The fact is, if your data is in that format, then the schema is
working against you, and everything you do is going to be much harder
than changing your schema to something that makes some more sense.

Next time I'll hold your hand a bit more, but yesterday I was very far
out of it (I'm not exactly 100% today either) with a bad head cold.
Now, should we have more exchanges to determine who can use the most
flowery of speech or should we talk pgsql and schema changes?


Perhaps his db-fu has yet to bud?

Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

Re: [GENERAL] query performance

[Tom Lane]

Brian Cox <[EMAIL PROTECTED]> writes:
> I've already posted the tail of this output previously.
> I conclude that these lines are not in this file. Where
> did they go?

[ scratches head... ]  Your example command works as expected for me.

[ rereads thread... ]  Oh, you're running 8.1.  I think you have to
do the command as a superuser to get that output in 8.1.  Later versions
are less picky.

regards, tom lane

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

Re: [GENERAL] Confused by result of pg_catalog.format_type()

[Erik Jones]

On Apr 24, 2008, at 5:10 PM, Tom Lane wrote:


Erik Jones <[EMAIL PROTECTED]> writes:

Ah, pg_catalog.format_type(prorettype, null) = '"trigger"', thanks.


It's probably fair to ask what it is you want to accomplish here,
because comparing format_type's output to a constant seems awfully
fragile.  Aside from the quotes (which I believe 8.3 won't emit in
this particular case) the output can vary depending on search_path
and perhaps other factors.

If you're trying to identify trigger functions I'd suggest

where prorettype = 'pg_catalog.trigger'::pg_catalog.regtype

as being the most bulletproof formulation, and probably faster too.


Ah, thank you for the advice!  I'm writing python unittest assertions  
for testing db state -- extremely useful for testing some custom  
client db tools I'm writing for partitioning and migrations (both data  
and schema).  I'd known about, and used, the regclass oid type, but  
had only learned about it from seeing someone else's example.  I  
should've read the chapter on system information function a little  
more thoroughly :)  My assertion methods are about to become a lot  
shorter...


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

Re: [GENERAL] query performance

[Brian Cox]

Tom Lane [EMAIL PROTECTED] wrote:


At the very end ... you're looking for these messages:

ereport(elevel,
(errmsg("free space map contains %d pages in %d relations",
storedPages, numRels),
errdetail("A total of %.0f page slots are in use (including 
overhead).\n"

  "%.0f page slots are required to track all free space.\n"
  "Current limits are:  %d page slots, %d relations, using %.0f 
kB.",

  Min(needed, MaxFSMPages),
  needed,
  MaxFSMPages, MaxFSMRelations,
  (double) FreeSpaceShmemSize() / 1024.0)));

if (numRels == MaxFSMRelations)
ereport(elevel,
(errmsg("max_fsm_relations(%d) equals the number of 
relations checked",

MaxFSMRelations),
 errhint("You have at least %d relations.  "
 "Consider increasing the configuration 
parameter \"max_fsm_relations\".",

 numRels)));
else if (needed > MaxFSMPages)
ereport(elevel,
(errmsg("number of page slots needed (%.0f) exceeds 
max_fsm_pages (%d)",

needed, MaxFSMPages),
 errhint("Consider increasing the configuration 
parameter \"max_fsm_pages\" "

 "to a value over %.0f.", needed)));

regards, tom lane



The following greps of the vacuum verbose output return no lines:

fgrep -i fsm
fgrep 'free space'
fgrep 'page slots'
fgrep 'relations'

I've already posted the tail of this output previously.
I conclude that these lines are not in this file. Where
did they go?

Thanks,
Brian

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

Re: [GENERAL] query performance

[Tom Lane]

Brian Cox <[EMAIL PROTECTED]> writes:
> Any hints as to where the FSM info is in this file?

At the very end ... you're looking for these messages:

ereport(elevel,
(errmsg("free space map contains %d pages in %d relations",
storedPages, numRels),
errdetail("A total of %.0f page slots are in use (including overhead).\n"
  "%.0f page slots are required to track all free space.\n"
  "Current limits are:  %d page slots, %d relations, using %.0f kB.",
  Min(needed, MaxFSMPages),
  needed,
  MaxFSMPages, MaxFSMRelations,
  (double) FreeSpaceShmemSize() / 1024.0)));

if (numRels == MaxFSMRelations)
ereport(elevel,
(errmsg("max_fsm_relations(%d) equals the number of relations 
checked",
MaxFSMRelations),
 errhint("You have at least %d relations.  "
 "Consider increasing the configuration parameter 
\"max_fsm_relations\".",
 numRels)));
else if (needed > MaxFSMPages)
ereport(elevel,
(errmsg("number of page slots needed (%.0f) exceeds 
max_fsm_pages (%d)",
needed, MaxFSMPages),
 errhint("Consider increasing the configuration parameter 
\"max_fsm_pages\" "
 "to a value over %.0f.", needed)));

regards, tom lane

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

Re: [GENERAL] query performance

[Brian Cox]

Scott Marlowe [EMAIL PROTECTED] wrote:

There's bits spread throughout the file, but the summary is at the bottom.


Here's a tail of the 'vacuum verbose' output:

INFO:  vacuuming "pg_toast.pg_toast_797619965"
INFO:  index "pg_toast_797619965_index" now contains 0 row versions in 1 
pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_797619965": found 0 removable, 0 nonremovable row 
versions in 0 pages

DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

I don't see anything that looks like a "summary".

Thanks,
Brian




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

Re: [GENERAL] query performance

[Scott Marlowe]

On Thu, Apr 24, 2008 at 6:23 PM, Brian Cox <[EMAIL PROTECTED]> wrote:
> Tom Lane [EMAIL PROTECTED] wrote:
>
> > You need a database-wide vacuum verbose (not just 1 table) to get that
> > output ...
> >
>
>  I ran:
>
>  > pgsql -U admin -d cemdb -c 'vacuum verbose' > /tmp/pgvac.log 2>&1
>
>  the output file has 2593 lines and, while I haven't looked at all of them,
> a:
>
>  > fgrep -i fsm /tmp/pgvac.log
>
>  returns no lines.
>
>  Any hints as to where the FSM info is in this file?

There's bits spread throughout the file, but the summary is at the bottom.

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

Re: [GENERAL] query performance

[Brian Cox]

Tom Lane [EMAIL PROTECTED] wrote:

You need a database-wide vacuum verbose (not just 1 table) to get that
output ...


I ran:

> pgsql -U admin -d cemdb -c 'vacuum verbose' > /tmp/pgvac.log 2>&1

the output file has 2593 lines and, while I haven't looked at all of 
them, a:


> fgrep -i fsm /tmp/pgvac.log

returns no lines.

Any hints as to where the FSM info is in this file?

Thanks,
Brian


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

Re: [GENERAL] query performance

[Tom Lane]

Brian Cox <[EMAIL PROTECTED]> writes:
> I read in another thread that vacuum verbose would tell me how much FSM 
> is needed, but I ran it and didn't see any output about this.

You need a database-wide vacuum verbose (not just 1 table) to get that
output ...

regards, tom lane

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

Re: [GENERAL] query performance

[Brian Cox]

Tom Lane [EMAIL PROTECTED] wrote:

I suspect that your FSM settings are too low, causing free space found
by VACUUM to be forgotten about.


I read in another thread that vacuum verbose would tell me how much FSM 
is needed, but I ran it and didn't see any output about this. What is 
the way to determine how much FSM is needed (other than wait for hints 
in the log).


  You might also need to consider

vacuuming more than once a day (there's a tradeoff between how often
you vacuum and how much FSM space you need).


What is the trade-off? Anyway to predict how much more (presumably) FSM 
is needed if you vacuum more often?


Thanks,
Brian

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

Re: [GENERAL] query question really cant give a summary here so read the body ;-)

[Scott Marlowe]

On Thu, Apr 24, 2008 at 3:43 PM, Rhys Stewart <[EMAIL PROTECTED]> wrote:

> type). That being said, I would appreciate that any further questions I have
> not be responded to by single line emails extolling the virtues of properly
> designed schemata, normalization or the like.

Well, I would appreciate getting shorter replies that are to the point
and don't rely on standing on soap boxes and using exercises in
polemics to make their point, but I probably won't get that.

The fact is, if your data is in that format, then the schema is
working against you, and everything you do is going to be much harder
than changing your schema to something that makes some more sense.

Next time I'll hold your hand a bit more, but yesterday I was very far
out of it (I'm not exactly 100% today either) with a bad head cold.
Now, should we have more exchanges to determine who can use the most
flowery of speech or should we talk pgsql and schema changes?

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

Re: [GENERAL] Confused by result of pg_catalog.format_type()

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> Ah, pg_catalog.format_type(prorettype, null) = '"trigger"', thanks.

It's probably fair to ask what it is you want to accomplish here,
because comparing format_type's output to a constant seems awfully
fragile.  Aside from the quotes (which I believe 8.3 won't emit in
this particular case) the output can vary depending on search_path
and perhaps other factors.

If you're trying to identify trigger functions I'd suggest

where prorettype = 'pg_catalog.trigger'::pg_catalog.regtype

as being the most bulletproof formulation, and probably faster too.

regards, tom lane

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

Re: [GENERAL] Confused by result of pg_catalog.format_type()

[Erik Jones]

On Apr 24, 2008, at 4:58 PM, Tom Lane wrote:


Erik Jones <[EMAIL PROTECTED]> writes:

What am I missing?


The double quotes in the function result ...


Ah, pg_catalog.format_type(prorettype, null) = '"trigger"', thanks.

Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

Re: [GENERAL] Confused by result of pg_catalog.format_type()

[Tom Lane]

Erik Jones <[EMAIL PROTECTED]> writes:
> What am I missing?

The double quotes in the function result ...

regards, tom lane

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

[GENERAL] Confused by result of pg_catalog.format_type()

[Erik Jones]

Here's an example:

pagila=# select pg_catalog.format_type(prorettype, NULL) from pg_proc  
where proname='foo_ins_trig';


format_type
 -
"trigger"
(1 row)

Time: 3.212 ms
pagila=# SELECT 1
 FROM pg_proc p
 WHERE p.proname='foo_ins_trig'
AND pg_catalog.format_type(p.prorettype, NULL) = 
'trigger';

 ?column?
--
(0 rows)

Time: 0.736 ms
pagila=#

What am I missing?

Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

Re: [GENERAL] query question really cant give a summary here so read the body ;-)

[Rhys Stewart]

While I thank you for your time in reading and responding,  This world is not ideal at any level, be it the lack of financial
equity, the petty prejudices that permeate societies on a whole, increasing
gas and food prices worldwide (I've officially parked my car and am taking
the bus until gas goes down.as if that is going to happen), the apparent
negligence of the atrocities in and around Sudan which continue to be
ignored by world powers and the fact that last night I was terrorized by
mosquitoes because my mosquito repellent is depleted.  Data and databases
are by no means exempt. One does not always have the luxury of designing a
schema from scratch. Some people, myself included, do not use databases in
the traditional fashion (traditional being part of a application of some
type). That being said, I would appreciate that any further questions I have
not be responded to by single line emails extolling the virtues of properly
designed schemata, normalization or the like.

Yeah, I would have loved to have been able to have a schema to rethink, in
fact i just got some data and need to extract some similar values in a
column, but thanks for your time!!


Rhys

Peace & Love| Live Long & Prosper

On Wed, Apr 23, 2008 at 1:37 PM, Scott Marlowe <[EMAIL PROTECTED]>
wrote:

> I'd say you need to rethink your schema.
>
> On Wed, Apr 23, 2008 at 12:11 PM, Rhys Stewart <[EMAIL PROTECTED]>
> wrote:
> > Hi all,
> >
> > have the following table
> >
> > aid| bid
> > --
> > 1|5
> > 2|6
> > 3|7
> > 4|9
> > 5|1
> > 6|2
> > 7|3
> > 8|10
> > 9|4
> > 10  |8
> > both aid & bid represent the same data in another table, but the table
> has
> > duplicate data and i did a self-join to get the id's out. The question is
> > how do i get a distinct listing between both columns
> >
> > like
> > aid | bid
> > ---
> > 1|5
> > 2|6
> > 3|7
> > 4|9
> > 10|8
> >
> > Have been racking my brain for the past hourany suggestions?
> >
> > Thanks
> > Rhys
> >
>

Re: [GENERAL] How to modify ENUM datatypes?

[Andreas 'ads' Scherbaum]

On Thu, 24 Apr 2008 14:02:07 -0400 Merlin Moncure wrote:

> On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> >  The first time I encountered them, I thought enums were a filthy,
> >  ill-conceived answer to a problem that didn't exist, implemented by people
> >  who didn't understand relational databases.  With considerably more
> >  experience under my belt than then, I say now that my original estimation
> >  was too kind.
> 
> I think you're being a little too hard on enums here.

No, i don't think, Andrew is too hard here.

As said before: in some special cases enum makes sense, like you said:


> I was actually in the anti-enum camp until it was demonstrated to me
> (and in my own testing) that using enum for natural ordering vs. fielding the
> ordering of the type out to a join is can be a huge win in such cases
> where it is important.

But the problem are really not this special cases, the real problem are
all the people who don't understand about enum and just try to use it
because "it seems to fit best" - unless they run into problems.


So we have a good tool in your hands, we just have to tell/teach the
people, how to use it and especially when not to use it.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

Re: [GENERAL] How to modify ENUM datatypes?

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Yep, updated:
> > * Allow adding/removing enumerated values to an existing enumerated
> >   data
> 
> Renaming an existing value might be interesting too (and would be far
> easier than either of the above).

TODO updated:

* Allow adding/renaming/removing enumerated values to an existing
  enumerated data type

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] Rapidly decaying performance repopulating a large table

[David Wilson]

Thanks for the help from everyone on this. Further investigation with
the suggested statistics and correlating that with some IO graphs
pretty much nailed the problem down to checkpoint IO holding things
up, and tuning the checkpoint segments and completion target (128 and
0.9 seemed to be the best tradeoff for me) pretty much cleared things
up.

All told, the run time of this regeneration pass was less than half of
what it was the last time I ran one, despite involving more total
data. Much appreciated.

-- 
- David T. Wilson
[EMAIL PROTECTED]

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

Re: [GENERAL] How to modify ENUM datatypes?

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Yep, updated:
>   * Allow adding/removing enumerated values to an existing enumerated
> data

Renaming an existing value might be interesting too (and would be far
easier than either of the above).

regards, tom lane

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

[GENERAL] Problems with postgresql 8.3 installation

[x asasaxax]

Hi,

  I´m using windows vista 64 bits. And i tried to install the postgresql
8.3, but it show this error: "cannot run initdb 1!". I don´t know whats
going on... help-me please


Thanks

Re: [GENERAL] How to modify ENUM datatypes?

[Bruce Momjian]

Matthew T. O'Connor wrote:
> Bruce Momjian wrote:
> > Matthew T. O'Connor wrote:
> >> D. Dante Lorenso wrote:
> >>> Or, here's another way to look at it ... make it easier to modify ENUM 
> >>> datatypes because we all know that you will eventually need that 
> >>> feature whether you males, females, and unknowns think so or not. 
> >> +1
> > 
> > Added to TODO:
> > 
> > * Allow adding enumerated values to an existing enumerated data
> >   type
> 
> And removing values if possible (fail if values are in use?).

Yep, updated:

* Allow adding/removing enumerated values to an existing enumerated
  data

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] How to modify ENUM datatypes?

[D. Dante Lorenso]

Alvaro Herrera wrote:

D. Dante Lorenso wrote:
Or, here's another way to look at it ... make it easier to modify ENUM  
datatypes because we all know that you will eventually need that feature  
whether you males, females, and unknowns think so or not.

Agreed.  Let's keep in mind that the current ENUM implementation is
completely new.


Here's a use case that I doubt too many are aware of ... what's unique 
about ENUM is that the data for the ENUM becomes part of the schema of a 
database.


This is actually something I like very much.  I have systems where the 
DEV and PROD databases are separate and must be synchronized when 
pushing out new features.  I currently use non-free tools to synchronize 
just the schemas for both databases.


Often if a new row is added to a lookup table, that row doesn't make it 
to the QA or PROD databases because it's part of the data of a table and 
is not part of the schema.  For data (like ENUM data) that should be 
consistent across databases, it helps if it gets deployed with the 
schema so that lookups will succeed properly.


-- Dante



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

Re: [GENERAL] How to modify ENUM datatypes?

[Matthew T. O'Connor]

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

D. Dante Lorenso wrote:
Or, here's another way to look at it ... make it easier to modify ENUM 
datatypes because we all know that you will eventually need that 
feature whether you males, females, and unknowns think so or not. 

+1


Added to TODO:

* Allow adding enumerated values to an existing enumerated data
  type


And removing values if possible (fail if values are in use?).


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

Re: [GENERAL] How to modify ENUM datatypes?

[Chris Browne]

[EMAIL PROTECTED] (Andrew Sullivan) writes:
> On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote:
>> 
>> Absolutely true. Which is odd, because this example is trotted out 
>> whenever there's a thread about ENUMs.
>
> I don't think it's odd at all.  In my view, the people who think enums are a
> good datatype for databases are exactly the sorts who'd think that their
> data is as static as this poor understanding of the vagaries of individuals'
> sex (gender is a different problem, given its association with social roles)
> would suggest.
>
> The world moves around in unexpected ways.  Your data model needs to
> accommodate itself to the world, because the alternative is not going to
> happen.

By the same token, a limited model, at least in this area, frequently
is sufficient to cover the set of ways in which legal systems
recognize and consider gender when constructing
 legislation.

And it's not evident that the simplification is a dramatic
oversimplification that causes a great deal of legal failures to the
extent to which it mandates that every system *needs* to track sex in
a more detailed fashion than (male, female, unknown, n/a).

I'm not sure, for instance, that I actually know what the word
"phenotype" means, and there's reason to imagine I might be somewhat
more "generally literate" than average.  If I'm not sure, there seems
little reason to expect that people with varying levels of
comprehension necessarily be able to choose from a more elaborate set
of options with accuracy.

After spending literal billions of dollars on security efforts, it
doesn't appear that security infrastructures that have *enormous*
incentive have had much luck successfully identifying who is a
terrorist and who is not; they consistently have extraordinary levels
of "Type II" (false positive) reporting errors.
-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/linuxdistributions.html
"There is no  reason anyone would want a computer  in their home".  
-- Ken Olson, Pres. and founder of Digital Equipment Corp.  1977

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

Re: [GENERAL] Adding notes against fields

[Chris Browne]

[EMAIL PROTECTED] (Oliver Helm) writes:

> Hello,
>
>
>
> I was wondering if if is possible to add a note against a field on a 
> postgresql table?  
>
>
>
> For example when running "\d tablename" i would like to have and additional 
> column called 'notes' which i could add to by altering the table.  As the 
> note would
> be field specific, not row specific i would rather not have to do it with an 
> additional column in the table!
>
>
>
> I have tried searching though the documentation, but so far have no found 
> anything.  Can anyone suggest whether this is in fact possible, and point me 
> in the right
> direction?

Have you taken a look at the COMMENT statement?

[EMAIL PROTECTED]:5432=# \h comment   
Command: COMMENT
Description: define or change the comment of an object
Syntax:
COMMENT ON
{
  TABLE object_name |
  COLUMN table_name.column_name |
  AGGREGATE agg_name (agg_type [, ...] ) |
  CAST (sourcetype AS targettype) |
  CONSTRAINT constraint_name ON table_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  FUNCTION func_name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  OPERATOR op (leftoperand_type, rightoperand_type) |
  OPERATOR CLASS object_name USING index_method |
  [ PROCEDURAL ] LANGUAGE object_name |
  ROLE object_name |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  TABLESPACE object_name |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name
} IS 'text'

-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/sap.html
"Much of this software was user-friendly, meaning that it was intended
for users who did not know anything about computers, and furthermore
had absolutely no intention whatsoever of learning."
-- A. S. Tanenbaum, "Modern Operating Systems, ch 1.2.4"

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

[GENERAL] Multiple Database Strategy

[Howard Cole]

I run several databases on a windows server all using the same cluster. 
Each database is backed up separately using pg_dump - but the size of 
the databases is leading me to think that the WAL backup strategy will 
reduce my backup bandwidth. The flaw in this is that the WAL backup is 
not confined to a single database but the entire cluster so that I have 
to restore all the database in the cluster to a point in time, rather 
than just a single database.


One method where I can still use the WAL backup is to put each database 
into its own cluster. So I can restore each database individually.


Anyway, this leads me to the question: Has anyone tried splitting 
multiple database (10+) into clusters and what are the memory and 
processor overheads in comparrison to more connections in a single cluster?


Thanks.
Howard.
www.selestial.com

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

Re: [GENERAL] How to modify ENUM datatypes?

[Tino Wildenhain]

Merlin Moncure wrote:

On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote:

 The first time I encountered them, I thought enums were a filthy,
 ill-conceived answer to a problem that didn't exist, implemented by people
 who didn't understand relational databases.  With considerably more
 experience under my belt than then, I say now that my original estimation
 was too kind.


I think you're being a little too hard on enums here.  I was actually
in the anti-enum camp until it was demonstrated to me (and in my own
testing) that using enum for natural ordering vs. fielding the
ordering of the type out to a join is can be a huge win in such cases
where it is important.  Relational theory is all well and good, but in
practical terms things like record size, index size, and query
performance are important.


Uhm. Sorry what? Can you demonstrate this particular use?
When I first saw discussion about enumns I kinda hoped they
will be implemented as kind of macro to really map to a table.
But here you go. I'm still looking for a good example to
demonstrate the usefullness of enums (same for arrays for that
matter)

Cheers
Tino

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

Re: [GENERAL] I think this is a BUG?

[Alban Hertroys]

On Apr 24, 2008, at 10:11 AM, Kaloyan Iliev wrote:

regbgrgr=# ALTER TABLE test ADD COLUMN not_null INT NOT NULL ;
ERROR:  column "id" contains null values

==EXAMPLE2 
==

Example2:
In this case the postgress fill the NOT NULL column ID from the  
sequence.


What sequence? You never told it you wanted one. A PRIMARY KEY  
doesn't automatically add a sequence nor does a NOT NULL constraint,  
the serial type does that but you defined the column as type int, not  
as type serial.


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4810d219927662597012045!



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

Re: [GENERAL] Adding notes against fields

[Alvaro Herrera]

Joshua D. Drake wrote:
> On Thu, 24 Apr 2008 19:04:34 +0100
> Richard Huxton <[EMAIL PROTECTED]> wrote:
> 
> > Oliver Helm wrote:
> > > Hello,
> > > 
> > > I was wondering if if is possible to add a note against a field on
> > > a postgresql table?
> > 
> > You can add comments: COMMENT ON  IS 'text' - see 
> > manuals for details.
> 
> However, COMMENTS don't append. 

But you can add one to the new column.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [GENERAL] Adding notes against fields

[Joshua D. Drake]

On Thu, 24 Apr 2008 19:04:34 +0100
Richard Huxton <[EMAIL PROTECTED]> wrote:

> Oliver Helm wrote:
> > Hello,
> > 
> > I was wondering if if is possible to add a note against a field on
> > a postgresql table?
> 
> You can add comments: COMMENT ON  IS 'text' - see 
> manuals for details.
> 

However, COMMENTS don't append. 

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



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

Re: [GENERAL] Adding notes against fields

[Richard Huxton]

Oliver Helm wrote:

Hello,

I was wondering if if is possible to add a note against a field on a 
postgresql table?


You can add comments: COMMENT ON  IS 'text' - see 
manuals for details.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] How to modify ENUM datatypes?

[Merlin Moncure]

On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
>  The first time I encountered them, I thought enums were a filthy,
>  ill-conceived answer to a problem that didn't exist, implemented by people
>  who didn't understand relational databases.  With considerably more
>  experience under my belt than then, I say now that my original estimation
>  was too kind.

I think you're being a little too hard on enums here.  I was actually
in the anti-enum camp until it was demonstrated to me (and in my own
testing) that using enum for natural ordering vs. fielding the
ordering of the type out to a join is can be a huge win in such cases
where it is important.  Relational theory is all well and good, but in
practical terms things like record size, index size, and query
performance are important.

I'll admit that if computers were infinitely fast, I'd probably use
enums less, although I still like them for things like male/female.  I
think they have a place.

The difficulty of manipulating enums is simply a shortcoming of the
implementation which could presumably be solved at some later point.

merlin

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

[GENERAL] Adding notes against fields

[Oliver Helm]

Hello,

I was wondering if if is possible to add a note against a field on a  
postgresql table?


For example when running "\d tablename" i would like to have and  
additional column called 'notes' which i could add to by altering the  
table.  As the note would be field specific, not row specific i would  
rather not have to do it with an additional column in the table!


I have tried searching though the documentation, but so far have no  
found anything.  Can anyone suggest whether this is in fact possible,  
and point me in the right direction?


Many Thanks!

- Oliver

Re: [GENERAL] Which Python library - psycopg2 or pygresql?

[Harald Armin Massa]

Micah,

psycopg2 has a license extensions which allows basically to use
psycopg2 binaries without distributing source code as long as there
are no modifications to the psycopg2 C code

best wishes

Harald



-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

Re: [GENERAL] Stored procedures in C

[Emiliano Moscato]

Guys, it has nothing to do with my question :D
I don't know why Martin answer to me anything about Cobol.
I just was looking how to query database in a stored procedure in C. Some
people pointed me to SPI documentation (at chapter 41 of oficial Postgres
documentation) that is being useful.
Thanks for worry about it :)

Emiliano

2008/4/24 Merlin Moncure <[EMAIL PROTECTED]>:

> On Wed, Apr 23, 2008 at 6:38 PM, Martin Gainty <[EMAIL PROTECTED]>
> wrote:
> >
> >
> > Emiliano and Mike
> >
> > The real challenge is trying to determine what a datatype is in
> cobol..for
> > that matter what is stack variable or heap in Cobol?
> > In the end you're better off writing this mess (preferably in Java)..
> > unless of course you need the billable hours for
> > the first rewrite to C
> > then later rewrite to Java
>
> (have no idea how this relates to the OP's original question).  If you
> are trying to port a cobol app to postgres, your best bet is to go
> through the client interface, libpq.  If you had to do it on the
> server side, I would stick to cobol environments that are C ABI
> compatible.  Writing general purpose data procedures in C is just not
> a very good idea most of the time...it's difficult and dangerous...C
> SPI has great uses, it's just not for everything.
>
> I personally think cobol is better suited for data processing type
> problems than java.  Mapping cobol data types to SQL is not terribly
> difficult.  cobol is notoriously difficult to port to another
> langauges...probably cheaper to connect it to the database via ISAM
> wrapper if the app is over a certain size.  Many modern cobol
> environments support external data sources through various
> techniques...extfh for example.  AcuCobol (crypticly) allows linking a
> ISAM emulation layer directly to the cobol runtime, one approach I've
> used in the past.
>
> merlin
>



-- 
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: [GENERAL] Which Python library - psycopg2 or pygresql?

[Micah Yoder]

On Tuesday 15 April 2008 10:27:14 am Dawid Kuroczko wrote:
> Whch would you suggest?
> How do they differ?

Sorry to bring this back up (I try to keep up with this list but it's hard!), 
but isn't licensing a concern?

If I understand correctly, pygresql is BSD-licensed, but depends on MX which 
is GPL *incompatible* whereas psycopg is GPL.

If that is the case, which one you choose depends strictly on the license of 
your project.  If GPL, use psychopg.  If anything else, use pygresql.

Is that accurate?

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

Re: [GENERAL] How to modify ENUM datatypes?

[Scott Marlowe]

On Thu, Apr 24, 2008 at 10:22 AM, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
>
>  Otherwise, don't use enums. They should be marked (like char(), IMO) in the
>  manual as, "Warning: you probably don't want to use this datatype.  Go think
>  some more."

Good point.  I think enums are kind like arrays.  Most of the time
they're a bad idea.  sometimes they're an ok idea, and very very
rarely, they are exactly what you need to solve a certain problem.

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

Re: [GENERAL] How to modify ENUM datatypes?

[Andrew Sullivan]

On Thu, Apr 24, 2008 at 11:04:10AM -0500, D. Dante Lorenso wrote:
> 
> Or, here's another way to look at it ... make it easier to modify ENUM 
> datatypes because we all know that you will eventually need that feature 
> whether you males, females, and unknowns think so or not.

Well, heck, why don't you just store your data in one infinitely wide table
with 10,000 nullable fields?  Then you can complain about the storage or
performance implications.

If you are devoted to an enum datatype, then you don't want to normalise
your data further.  With an incredibly small number of exceptions, I'd argue
that means one of two things: either it doesn't need normalisation, in
which case a relational database is the wrong tool for this job; or else it
does need normalisation, but you don't understand how relational databases
work well enough to do it properly.  [Later: on re-reading this paragraph, I
thought of a third possibility: that you're violating the first rule of
optimisation.]

The first time I encountered them, I thought enums were a filthy,
ill-conceived answer to a problem that didn't exist, implemented by people
who didn't understand relational databases.  With considerably more
experience under my belt than then, I say now that my original estimation
was too kind.  

I do see what someone says upthread: if you have a special case where the
storage saving adequately offsets the maintenance cost, this might be worth
it.  But if you've really analysed your case that carefully, and understand
the costs (i.e. you won't carp later when changing is expensive) you already
know what you're doing.

Otherwise, don't use enums. They should be marked (like char(), IMO) in the
manual as, "Warning: you probably don't want to use this datatype.  Go think
some more."

A

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

Re: [GENERAL] How to modify ENUM datatypes?

[Bruce Momjian]

Matthew T. O'Connor wrote:
> D. Dante Lorenso wrote:
> > Andrew Sullivan wrote:
> >> I don't think it's odd at all.  In my view, the people who think 
> >> enums are a
> >> good datatype for databases are exactly the sorts who'd think that their
> >> data is as static as this poor understanding of the vagaries of 
> >> individuals'
> >> sex (gender is a different problem, given its association with social 
> >> roles)
> >> would suggest.
> >
> > Or, here's another way to look at it ... make it easier to modify ENUM 
> > datatypes because we all know that you will eventually need that 
> > feature whether you males, females, and unknowns think so or not. 
> 
> +1

Added to TODO:

* Allow adding enumerated values to an existing enumerated data
  type

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] How to modify ENUM datatypes?

[Alvaro Herrera]

D. Dante Lorenso wrote:

> Or, here's another way to look at it ... make it easier to modify ENUM  
> datatypes because we all know that you will eventually need that feature  
> whether you males, females, and unknowns think so or not.

Agreed.  Let's keep in mind that the current ENUM implementation is
completely new.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] How to modify ENUM datatypes?

[Matthew T. O'Connor]

D. Dante Lorenso wrote:

Andrew Sullivan wrote:
I don't think it's odd at all.  In my view, the people who think 
enums are a

good datatype for databases are exactly the sorts who'd think that their
data is as static as this poor understanding of the vagaries of 
individuals'
sex (gender is a different problem, given its association with social 
roles)

would suggest.


Or, here's another way to look at it ... make it easier to modify ENUM 
datatypes because we all know that you will eventually need that 
feature whether you males, females, and unknowns think so or not. 


+1


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

Re: [GENERAL] How to modify ENUM datatypes?

[D. Dante Lorenso]

Andrew Sullivan wrote:

On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote:
Absolutely true. Which is odd, because this example is trotted out 
whenever there's a thread about ENUMs.

I don't think it's odd at all.  In my view, the people who think enums are a
good datatype for databases are exactly the sorts who'd think that their
data is as static as this poor understanding of the vagaries of individuals'
sex (gender is a different problem, given its association with social roles)
would suggest.


Or, here's another way to look at it ... make it easier to modify ENUM 
datatypes because we all know that you will eventually need that feature 
whether you males, females, and unknowns think so or not.


-- Dante

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

Re: [GENERAL] How to modify ENUM datatypes?

[Scott Marlowe]

On Thu, Apr 24, 2008 at 9:39 AM, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote:
>  >
>  > Absolutely true. Which is odd, because this example is trotted out
>  > whenever there's a thread about ENUMs.
>
>  I don't think it's odd at all.  In my view, the people who think enums are a
>  good datatype for databases are exactly the sorts who'd think that their
>  data is as static as this poor understanding of the vagaries of individuals'
>  sex (gender is a different problem, given its association with social roles)
>  would suggest.
>
>  The world moves around in unexpected ways.  Your data model needs to
>  accommodate itself to the world, because the alternative is not going to
>  happen.

Mostly true, but there are systems where certain parts really do tend
to be static over very long periods, and for those, I might admit to
ENUM being an answer.  for instance, in the USAF, all units being
worked on in our avionics shop were in one of three categories,
Awaiting Maintenance, Awaiting Parts, or In Work.  They were
abbreviated AWM, AWP, and INW.

That was back in the 1980s.  I'm willing to bet it hasn't changed
since then.  For those types of problems, enums make a certain amount
of sense, especially if you're tracking thousands of line units being
worked on every hour of every day across the US.  the small saving in
space adds up fast.  But that's an artificially constructed set of
choices.

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

Re: [GENERAL] How to modify ENUM datatypes?

[Andrew Sullivan]

On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote:
> 
> Absolutely true. Which is odd, because this example is trotted out 
> whenever there's a thread about ENUMs.

I don't think it's odd at all.  In my view, the people who think enums are a
good datatype for databases are exactly the sorts who'd think that their
data is as static as this poor understanding of the vagaries of individuals'
sex (gender is a different problem, given its association with social roles)
would suggest.

The world moves around in unexpected ways.  Your data model needs to
accommodate itself to the world, because the alternative is not going to
happen.

A

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

Re: [HACKERS] [GENERAL] I think this is a BUG?

[Brendan Jurd]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Thu, Apr 24, 2008 at 7:13 PM, Richard Huxton  wrote:
> Kaloyan Iliev wrote:
> > r=# CREATE TABLE test( a text, b int);
> > CREATE TABLE
> > r=# INSERT INTO test VALUES ('test',1);
> > INSERT 0 1
> > r=#  ALTER TABLE test ADD COLUMN id INT NOT NULL PRIMARY KEY;
> > NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "test_pkey" for table "test"
> > ALTER TABLE
> > r=# SELECT * FROM test WHERE id is null;
> >  a   | b | id
> > --+---+
> > test | 1 |
> >
>
>  Well that's clearly broken (seems to do the same in 8.3 too). I've cc-ed
> the hackers list so they can investigate further. Presumably the "not null"
> test is being missed somehow when the column is initially created.
>

Confirmed on HEAD.

I think I know why this is happening.  When ALTER TABLE ... ADD COLUMN
... PRIMARY KEY is transformed, you end up with ADD COLUMN, followed
by an ADD INDEX.

transformIndexConstraint sets the is_not_null flag on the ColumnDefs
associated with the primary key.  That works great in a CREATE TABLE
context, but in ADD COLUMN, when we haven't created the column yet,
this means that the column is created with attnotnull set to true,
which tricks DefineIndex into thinking that the column already has a
NOT NULL constraint.

So the NOT NULL constraint never gets added and hence the check for
NULL values never occurs, which leaves you with a column which is
bogusly marked "NOT NULL".

I'm currently working on a solution for this, and I've thought of a
couple different general approaches:

 1. Teach transformIndexConstraint not to set ->is_not_null for
primary keys on columns added with ALTER TABLE.  This way, defineIndex
will add the NOT NULL constraint as normal while defining the primary
key.  We could try scanning for columns to see whether they already
exist, or rig up some kind of communication path between
transformAlterTableStmt and transformIndexConstraint ...

 2. Delay the logic in transformAlterTableStmt which pulls ADD COLUMN
... NOT NULL into a separate command, so that it occurs *after* we've
called transformIndexConstraints.  That way, transformAlterTableStmt
will pick up on the fact that transformIndexConstraint has set the
column's is_not_null field, and create the AT_SetNotNull command
pre-emptively, which means that defineIndex doesn't have any extra
work to do ...

 3. Force defineIndex and ATExecSetNotNull to add the NOT NULL
constraint, even if the column already has attnotnull = true.

Of these two, I'm leaning towards 2 because it seems less convoluted
than 1 and less clumsy/wasteful than 3.  However, I'm keen to hear
what others have to say about it.

Cheers,
BJ
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFIEKdq5YBsbHkuyV0RAvOuAJ9b63xqPcomtTDQYLeL8P2W1+rEBQCfWZFy
rL3Wld2xIc5bOEPnSSiEbbE=
=VTFo
-END PGP SIGNATURE-

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

Re: [GENERAL] WAL shipping with archive_timeout & pg_switch_xlog()

[Tom Lane]

wstrzalka <[EMAIL PROTECTED]> writes:
> archive_timeout is used for WAL shipping to standby server in my case
> (are there any other reasons?), but WAL is switched by the timeout
> even if there are no changes on the server.

This is intentional.  Some people consider the arrival of a new WAL file
to be a good heartbeat monitor, and they want to see one go by every
so many seconds whether the database is busy or not.

regards, tom lane

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

Re: [GENERAL] plpgsql and logical expression evaluation

[Tom Lane]

wstrzalka <[EMAIL PROTECTED]> writes:
> So - does it mean that the whole IF-ELSE-ENDIF is not parsed at once -
> but lazy-parsed when the control reaches it, while the IF condition is
> parsed as a single expression and therefore I get error in this case?

Right, for a suitable definition of "parsed".  There is some trivial
syntax checking that happens early (eg, you'll see complaints for
mismatched parentheses) but actually trying to determine the meaning
of an expression happens when control first gets there.

regards, tom lane

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

Re: [GENERAL] error connecting to database: could not open relation

[Scott Marlowe]

On Wed, Apr 23, 2008 at 10:44 PM, "PontoSI - Consultoria, Informática
e Serviços LDA" <[EMAIL PROTECTED]> wrote:
>
>  Hi,
>  I've had a server crash on a machine running FreeBSD 6 and PG 8.2.5. The
> database was running at the time of the crash, and probably there was some
> lost data. When I try to start PG in single mode (and width -P) he complains
> that he "could not open relation with OID 2661". Because the file is
> missing, I've tried to create a zeroed file with the size of a page too see
> if we would eat it so I can dump the data, but without success. I've also
> tried pgfsck without any luck.
>  Is there any way to get around the missing files and dump directly the
> values stored inside the actual table files?

You're getting a lot of useful on how to get your database up and
running again.  I'll leave that to the folks you're chatting with.
What you need to do next is figure out how it happened.  On a machine
with solid disk hardware and a properly configured OS this should NOT
ever happen.

The most common cause of this is an IDA / SATA drive with its write
cache enabled in write back mode.  There are other possibilities.

A properly configured database server should be able to survive having
the power cord yanked out in the middle of the busiest period of the
day.

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

Re: [GENERAL] error connecting to database: could not open relation

[Tom Lane]

Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> Ok, 2661 is definitly pg_cast_source_target_index on my system so the
> -P should have caused postgres to ignore it.

That was my first thought too.  However, if the pg_internal.init file
were missing/broken then the thing would try to rebuild it, and I think
that would involve opening most system indexes.  Try inserting a correct
pg_internal.init file --- ideally from a recent backup, but if you
haven't got one then it most likely will work to copy it from another DB
in the same installation, or in extremis do an initdb in a temporary
directory and take the file from there.

Of course, if the data loss extended to more than just index(es) you're
still hosed ...

regards, tom lane

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

Re: [GENERAL] Stored procedures in C

[Merlin Moncure]

On Wed, Apr 23, 2008 at 6:38 PM, Martin Gainty <[EMAIL PROTECTED]> wrote:
>
>
> Emiliano and Mike
>
> The real challenge is trying to determine what a datatype is in cobol..for
> that matter what is stack variable or heap in Cobol?
> In the end you're better off writing this mess (preferably in Java)..
> unless of course you need the billable hours for
> the first rewrite to C
> then later rewrite to Java

(have no idea how this relates to the OP's original question).  If you
are trying to port a cobol app to postgres, your best bet is to go
through the client interface, libpq.  If you had to do it on the
server side, I would stick to cobol environments that are C ABI
compatible.  Writing general purpose data procedures in C is just not
a very good idea most of the time...it's difficult and dangerous...C
SPI has great uses, it's just not for everything.

I personally think cobol is better suited for data processing type
problems than java.  Mapping cobol data types to SQL is not terribly
difficult.  cobol is notoriously difficult to port to another
langauges...probably cheaper to connect it to the database via ISAM
wrapper if the app is over a certain size.  Many modern cobol
environments support external data sources through various
techniques...extfh for example.  AcuCobol (crypticly) allows linking a
ISAM emulation layer directly to the cobol runtime, one approach I've
used in the past.

merlin

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

Re: [GENERAL] Best backup setup

[Erik Jones]

On Apr 23, 2008, at 10:03 AM, Gabor Siklos wrote:

I need to back up our database off-site for disaster recovery. If I  
just back up the entire database data directory (i.e. /var/lib/pgsql/ 
data) will I be able to restore from there? Or should I instead just  
dump the data, using pg_dump, and back up the dump?


The advantage of the first method would be that I would not have to  
wait for pg_dump (it takes quite long on our 60G+ database) and  
would just be able to configure the backup agent to monitor the data  
directory and do differential backups of the files there every hour  
or so.


Your suggestions are much appreciated!



Are you repeating this question for which you previously received  
answers?  Or, is the list server repeating messages?


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




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

Re: [GENERAL] initdb in 8.3

[Tom Lane]

"Christopher Condit" <[EMAIL PROTECTED]> writes:
> Although, once you've built the index with varchar_pattern_ops index,
> the following two (essentially equivalent) queries will run at vastly
> different speeds:
> select * from A where A.value like 'Nacho';
> select * from A where A.value = 'Nacho';

> Seems that the optimizer should catch this...

Yeah, it's on the to-do list ...
http://archives.postgresql.org/pgsql-hackers/2008-02/msg01003.php

regards, tom lane

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

Re: [GENERAL] initdb in 8.3

[Christopher Condit]

Ahhh - I See. Thanks, Craig.
Although, once you've built the index with varchar_pattern_ops index,
the following two (essentially equivalent) queries will run at vastly
different speeds:
select * from A where A.value like 'Nacho';
select * from A where A.value = 'Nacho';

Seems that the optimizer should catch this...
C

-Original Message-
From: Craig Ringer [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 24, 2008 01:20
To: Christopher Condit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] initdb in 8.3

Christopher Condit wrote:
> I have a question related to this issue:
> Now that the locale has changed, it seems that the planner no longer
> wants to use the indexes for running LIKE queries on varchar columns
> unless I specify varchar_pattern_ops when creating the index. And if I
> create the index with  varchar_pattern_ops, then the planner won't use
> it for = queries.
> 
> What's the correct solution to this problem (when using UTF-8 and
> lc_collate and lc_ctype are both 1252)? Do I need to create two
indexes?

http://www.postgresql.org/docs/faqs.FAQ.html#item4.6
http://www.postgresql.org/docs/8.3/static/indexes-opclass.html

It should really also be mentioned in the section on the LIKE operator.

By the way, when I tried to leave a comment on the pattern matching
operator doc page I found that after logging in I was presented with a
blank comments form again. When I re-entered my comment and submitted I
got an error indicating that '' is not a valid NUMERIC. Going back to
the original comments form on the pattern matching operator doc page
(having logged in) and submitting there works fine.

--
Craig Ringer

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

Re: [GENERAL] Stored procedures in C

[Martin Gainty]

Good Morning Emiliano-

since postgres is written in 'C'
and Most of us on this list have programmed in C ..although my experience was 
'used in last millenia'

if we reference contrib/query/tsearch2/query.c

when you see  statements such as
PG_FUNCTION_INFO_V1(tsquery_in);

you are calling a header file at \include\server\fmgr\fmgr.h which is 
macro-subbing at 
specifically:

#define PG_FUNCTION_INFO_V1(funcname) \
extern Pg_finfo_record * CppConcat(pg_finfo_,funcname) (void); \
Pg_finfo_record * \
CppConcat(pg_finfo_,funcname) (void) \
{ \
 static Pg_finfo_record my_finfo = { 1 }; \
 return &my_finfo; \
} \
extern int no_such_variable

In any case please feel free to ping us for any questions you might have
It sounds like you have quite a challenge ahead of you

Buena Suerte
Martin-
  - Original Message - 
  From: Emiliano Moscato 
  To: Andrej Ricnik-Bay 
  Cc: pgsql-general@postgresql.org 
  Sent: Thursday, April 24, 2008 8:44 AM
  Subject: Re: [GENERAL] Stored procedures in C


  Thanks a lot for the response Andrej!

  One of these texts was known for me. But all of them are VERY basic. No one 
explains how to do a query and manage results :(
  The only one that manage querys is the source code placed at 
contrib/tablefunc in the Postgres distribution. But is VERY hard to follow for 
me :( I would like to find an intermediate step.
  Thanks again

  Emiliano


  2008/4/23 Andrej Ricnik-Bay <[EMAIL PROTECTED]>:

On 24/04/2008, Emiliano Moscato <[EMAIL PROTECTED]> wrote:
> I have to do some stuff writing stored procedures for Postgres in C. I saw
> the oficial documentation but it was hard for me to find out how to do a
> simple function, let's call it "query()" , that receives a string and uses
> this string to do a query and return the results. Has anyone some 
examples?


You don't mentioned where else you looked - did you come across these?

http://linuxgazette.net/139/peterson.html

http://www.faqs.org/docs/ppbook/x15284.htm


http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html




> Thanks in advance...
> Regards,
>
> Emiliano


Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes 
concise.

http://www.american.edu/econ/notes/htmlmail.htm




  -- 
  mOsKi
  "No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo 

Re: [GENERAL] Stored procedures in C

[Emiliano Moscato]

Thanks a lot for the response Andrej!

One of these texts was known for me. But all of them are VERY basic. No one
explains how to do a query and manage results :(
The only one that manage querys is the source code placed at
contrib/tablefunc in the Postgres distribution. But is VERY hard to follow
for me :( I would like to find an intermediate step.
Thanks again

Emiliano

2008/4/23 Andrej Ricnik-Bay <[EMAIL PROTECTED]>:

> On 24/04/2008, Emiliano Moscato <[EMAIL PROTECTED]> wrote:
> > I have to do some stuff writing stored procedures for Postgres in C. I
> saw
> > the oficial documentation but it was hard for me to find out how to do a
> > simple function, let's call it "query()" , that receives a string and
> uses
> > this string to do a query and return the results. Has anyone some
> examples?
>
> You don't mentioned where else you looked - did you come across these?
>
> http://linuxgazette.net/139/peterson.html
>
> http://www.faqs.org/docs/ppbook/x15284.htm
>
>
> http://www.observercentral.net/~selkovjr/postgres/tutorial/html/exttut-getstart.html
>
>
>
> > Thanks in advance...
> > Regards,
> >
> > Emiliano
>
> Cheers,
> Andrej
>
>
> --
> Please don't top post, and don't use HTML e-Mail :}  Make your quotes
> concise.
>
> http://www.american.edu/econ/notes/htmlmail.htm
>



-- 
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

[GENERAL] PAM + Password authentication

[Bohdan Linda]

Hello,

Can the PGSQL database be configured that it performs authentication
against PAM and if fails the it tries against internal mechanizm?

I would like to migrate to PAM, but I do not want to promote some users to
system wide.

Till now I am able to do one or the other way.

Thank you,
Bohdan 

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

Re: [GENERAL] I think this is a BUG?

[Richard Huxton]

Kaloyan Iliev wrote:

Hi,

I find something very interesting which I think is a bug and I want to 
discuss it.

---
Here is the example1:
1.I create a table without PK;
2.Insert 1 row;
3.I ADD PK;
4.When I select all ID's are with NULL values, but the column is NOT NULL;
5.But If I try to create a regular NOT NULL  column the postgres stops 
me(as it should) with ERROR "ERROR:  column "id" contains null values".


PostgreSQL 8.2.7 on amd64-portbld-freebsd6.3, compiled by GCC cc (GCC) 
3.4.6 [FreeBSD] 20060305



r=# CREATE TABLE test( a text, b int);
CREATE TABLE
r=# INSERT INTO test VALUES ('test',1);
INSERT 0 1
r=#  ALTER TABLE test ADD COLUMN id INT NOT NULL PRIMARY KEY;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
"test_pkey" for table "test"

ALTER TABLE
r=# SELECT * FROM test WHERE id is null;
 a   | b | id
--+---+
test | 1 |


Well that's clearly broken (seems to do the same in 8.3 too). I've cc-ed 
the hackers list so they can investigate further. Presumably the "not 
null" test is being missed somehow when the column is initially created.



r=# ALTER TABLE test ADD COLUMN not_null int NOT NULL;
ERROR:  column "not_null" contains null values

My question is why didn't PG create the sequence and fill the values in 
the first example.


Not sure what you mean here.


And why creates an NOT NULL column with null values in it!


Because it hasn't got any other value to put in it. Try:
ALTER TABLE test ADD COLUMN id3 integer NOT NULL default 0;

--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] I think this is a BUG?

[Kaloyan Iliev]

Hi,

I find something very interesting which I think is a bug and I want to 
discuss it.

---
Here is the example1:
1.I create a table without PK;
2.Insert 1 row;
3.I ADD PK;
4.When I select all ID's are with NULL values, but the column is NOT NULL;
5.But If I try to create a regular NOT NULL  column the postgres stops 
me(as it should) with ERROR "ERROR:  column "id" contains null values".


I think that PG should create squence and set it as default, fill the 
rows as it does in example2 from the sequence and then make the column 
NOT NULL;


r=# begin;
BEGIN
r=# SELECT version();
version
-
PostgreSQL 8.2.7 on amd64-portbld-freebsd6.3, compiled by GCC cc (GCC) 
3.4.6 [FreeBSD] 20060305

(1 row)

r=# CREATE TABLE test( a text, b int);
CREATE TABLE
r=# INSERT INTO test VALUES ('test',1);
INSERT 0 1
r=#  ALTER TABLE test ADD COLUMN id INT NOT NULL PRIMARY KEY;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
"test_pkey" for table "test"

ALTER TABLE
r=# SELECT * FROM test WHERE id is null;
 a   | b | id
--+---+
test | 1 |
(1 row)

r=# \d test;
Table "public.test"
Column |  Type   | Modifiers
+-+---
a  | text|
b  | integer |
id | integer | not null
Indexes:
   "test_pkey" PRIMARY KEY, btree (id)

regbgrgr=# ALTER TABLE test ADD COLUMN not_null INT NOT NULL ;
ERROR:  column "id" contains null values

==EXAMPLE2==
Example2:
In this case the postgress fill the NOT NULL column ID from the sequence.

r=# begin;
BEGIN
r=# SELECT version();
version
-
PostgreSQL 8.2.7 on amd64-portbld-freebsd6.3, compiled by GCC cc (GCC) 
3.4.6 [FreeBSD] 20060305

(1 row)

r=# CREATE TABLE test( a text, b int);
CREATE TABLE
r=# INSERT INTO test VALUES ('test',1);
INSERT 0 1
regbgrgr=# SELECT * from test;
 a   | b
--+---
test | 1
(1 row)

r=# CREATE SEQUENCE test_id_seq;
CREATE SEQUENCE
r=# ALTER TABLE test ADD COLUMN id INT NOT NULL PRIMARY KEY default 
nextval('test_id_seq'::regclass);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
"test_pkey" for table "test"

ALTER TABLE
r=# SELECT * from test;
 a   | b | id
--+---+
test | 1 |  1
(1 row)

r=# \d test
Table "public.test"
Column |  Type   | Modifiers
+-+---
a  | text|
b  | integer |
id | integer | not null default nextval('test_id_seq'::regclass)
Indexes:
   "test_pkey" PRIMARY KEY, btree (id)


r=# ALTER TABLE test ADD COLUMN not_null int NOT NULL;
ERROR:  column "not_null" contains null values

My question is why didn't PG create the sequence and fill the values in 
the first example.

And why creates an NOT NULL column with null values in it!


Best Regards,
 Kaloyan Iliev

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

Re: [GENERAL] How to modify ENUM datatypes?

[Andreas 'ads' Scherbaum]

On Wed, 23 Apr 2008 17:18:12 -0600 Scott Marlowe wrote:

> I would put it that gender is not so easily defined, which makes it a
> poor choice for enum.

That's why my original statement had the additional note about special
cases.

If you write an address book you normally don't want to add information
like transgender - just 'male', 'female', 'unknown' seems enough here.
If you define a medical database, you have to extend the ENUM values a
bit, but even then you know the possible values in advance before
creating the ENUM - just that you have some more choices than in your
address book.

It all depends on the use case, just like ENUM itself.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

Re: [GENERAL] Backup setup

[Tomasz Ostrowski]

On 2008-04-23 17:22, Terry Lee Tucker wrote:
> On Wednesday 23 April 2008 11:14, Gabor Siklos wrote:
>> The advantage of the first method would be that I would not have to wait
>> for pg_dump (it takes quite long on our 60G+ database) and would just be
>> able to configure the backup agent to monitor the data directory and do
>> differential backups of the files there every hour or so.
> 
> I would use pg_dump. It will ensure that you get a complete set of data and 
> not something half written.

I'd doing a "pg_dump -b -F t" and then compute differences between
previous and current backup using program "rdiff" from package
"librsync". Then difference file is compressed, encrypted and shipped
offsite nightly.

But my database is much smaller than yours. But it works well for a 6GB
backup files on entry level server hardware.

Pozdrawiam
Tometzky
-- 
Best of prhn - najzabawniejsze teksty polskiego UseNet-u
http://prhn.dnsalias.org/
  Chaos zawsze pokonuje porządek, gdyż jest lepiej zorganizowany.
  [ Terry Pratchett ]

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

Re: [GENERAL] error connecting to database: could not open relation

[Martijn van Oosterhout]

On Thu, Apr 24, 2008 at 08:17:15AM +0100, "PontoSI - Consultoria, Informática e 
Serviços LDA" wrote:
> 
> %/usr/local/bin/postgres --single -P -D /usr/local/pgsql/data/ 
> FATAL:  XX000: could not open relation with OID 2661
> LOCATION:  relation_open, heapam.c:700
> %
> 
>  is the name of the database.

Ok, 2661 is definitly pg_cast_source_target_index on my system so the
-P should have caused postgres to ignore it. I'm out of ideas so I'm
sending it to the list to see if anyone else has any.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature