[GENERAL] poor cpu utilization on dual cpu box

[Simon Sadedin]

Folks,
 
I’m hoping someone can give me some pointers to resolving an issue with postgres and it’s ability to utilize multiple CPUs effectively.
 
The issue is that no matter how much query load we throw at our server it seems almost impossible to get it to utilize more than 50% cpu on a dual-cpu box.  For a single connection we can use all of one CPU, but multiple connections fail to increase the overall utilization (although they do cause it to spread across CPUs).
 
The platform is a dual CPU 2.8Ghz P4 Xeon Intel box (hyperthreading disabled)  running a fairly standard Redhat 9 distribution.  We are using postgres on this platform with a moderate sized data set (some hundreds of megs of data).  The tests perform no updates and simply hit the server with a single large complex query via a multithreaded java/jdbc client.  To avoid network distortion we run the client on the localhost (its cpu load is minimal).   We are running with shared buffers large enough to hold the entire database and sort memory of 64m, should easily be enough to prevent sorting to disk.  
 
At this point I’ve tried everything I can think of to diagnose this - checking the pg_locks table indicates that even under heavy load there are no ungranted locks, so it would appear not to be a locking issue.  Vmstat/iostat show no excessive figures for network or io waits.  The only outlandish figure is that context switches which spike up to 250,000/sec (seems large).  By all indications, postgres is waiting internally as if it is somehow singlethreaded.  However the documentation clearly indicates this should not be so.
 
Can anyone give me some pointers as to why postgres would be doing this?   Is postgres really multi-process capable or are the processes ultimately waiting on each other to run queries or access shared memory?
 
On a second note, has anyone got some tips on how to profile postgres in this kind of situation?  I have tried using gprof, but because postgres spawns its processes dynamically I always end up profiling the postmaster (not very useful).
 
Thanking in advance for any help!
 
Cheers,
 
Simon.
 
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

Re: [GENERAL] unexpected query behavior with UTF text

[Indra Heckenbach]

Hi,

Thanks for the response.  I actually have tried to re initdb with 
locale=C and I got the same results.  If my locale is ignoring Ja chars, 
how can I change that?  I expected that UTF-8 would consider all 
characters, being a universal encoding.  A colleague also tried  the 
same test with a different server (locale also set to utf8).


Text = depends on strcoll(), which is locale-sensitive.  It sure appears
that your locale is designed to ignore japanese characters :-(
 

I found a related issue on the mailing list, where locale setting was 
causing something similar.  However, my locale is set to "en_US.UTF-8", 
which is the solution proposed to the other problem.
   

We have heard before that RH9's default locale setting is seriously
broken.  This seems to be additional evidence for that opinion.  I'd
recommend re-initdb'ing in locale C.
Also, you say "your locale", but how certain are you that that is the
database's locale, and not just the oe in your own user environment?
It'd be a good idea to use pg_controldata to check the database settings.
 

I tried to mannually initialize it, and my initdb.i18n exports 
LANG="en_US.UTF-8".  Is there any other way I can check?

thanks,
Indra
			regards, tom lane

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



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

[GENERAL] SCSI vs. IDE performance test

[Ron Johnson]

http://hardware.devchannel.org/hardwarechannel/03/10/20/1953249.shtml?tid=20&tid=38&tid=49

-- 
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA

I can't make you have an abortion, but you can *make* me pay
child support for 18 years? However, if I want the child (and
all the expenses that entails) for the *rest*of*my*life*, and you
don't want it for 9 months, tough luck???


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

Re: [GENERAL] poor cpu utilization on dual cpu box

[Andreas Fromm]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

It is probably not really a pg issue that you don't get your CPUs fully
used. I would guess that one CPU saturates the memory bus or the disk
bandwidth depending on where your data is. This is the typical behavior
for memory bounded applications where you don't get the full performanco
out of your box and the only thing you can do is to improve your
application to reuse data which is in the caches, which is something you
probably won't be able to do, due to the nature of databse applications.

Regards


Andreas Fromm


Simon Sadedin wrote:
> Folks,
>
>
>
> Im hoping someone can give me some pointers to resolving an issue with
> postgres and its ability to utilize multiple CPUs effectively.
>
>
>
> The issue is that no matter how much query load we throw at our server
> it seems almost impossible to get it to utilize more than 50% cpu on a
> dual-cpu box.  For a single connection we can use all of one CPU, but
> multiple connections fail to increase the overall utilization (although
> they do cause it to spread across CPUs).
>
>
>
> The platform is a dual CPU 2.8Ghz P4 Xeon Intel box (hyperthreading
> disabled)  running a fairly standard Redhat 9 distribution.  We are
> using postgres on this platform with a moderate sized data set (some
> hundreds of megs of data).  The tests perform no updates and simply hit
> the server with a single large complex query via a multithreaded
> java/jdbc client.  To avoid network distortion we run the client on the
> localhost (its cpu load is minimal).   We are running with shared
> buffers large enough to hold the entire database and sort memory of 64m,
> should easily be enough to prevent sorting to disk.
>
>
>
> At this point Ive tried everything I can think of to diagnose this -
> checking the pg_locks table indicates that even under heavy load there
> are no ungranted locks, so it would appear not to be a locking issue.
> Vmstat/iostat show no excessive figures for network or io waits.  The
> only outlandish figure is that context switches which spike up to
> 250,000/sec (seems large).  By all indications, postgres is waiting
> internally as if it is somehow singlethreaded.  However the
> documentation clearly indicates this should not be so.
>
>
>
> Can anyone give me some pointers as to why postgres would be doing
> this?   Is postgres really multi-process capable or are the processes
> ultimately waiting on each other to run queries or access shared memory?
>
>
>
> On a second note, has anyone got some tips on how to profile postgres in
> this kind of situation?  I have tried using gprof, but because postgres
> spawns its processes dynamically I always end up profiling the
> postmaster (not very useful).
>
>
>
> Thanking in advance for any help!
>
>
>
> Cheers,
>
>
>
> Simon.
>
>
>
> 
> Do you Yahoo!?
> The New Yahoo! Shopping
>

> - with improved product search

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/lqjpPkvkZVZzNY0RAtw/AKDHhIQSAhOVN/+OMIjeChpwky80BwCgqSOu
MhEDKjU23Rb4TONykGzM8wQ=
=IRJU
-END PGP SIGNATURE-


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

Re: [GENERAL] plpgsql extract epoch problem

[Darren Ferguson]

Sorry found the answer
I was taking the docs to literal
It should be extract(epoch from _test)

Sorry for the email
Darren
Darren Ferguson wrote:

Hey all

I am trying to extract the epoch from a timestamp variable in plpgsql.
The function is as follows:
create or replace function test() returns integer as '
declare
 _test timestamp with time zone;
 _f integer;
begin
 _test := current_timestamp;
 _f := extract(epoch from timestamp _test);
 return _f;
end;' language 'plpgsql';
Any ideas why this will not work

The error is as follows:
pts=> select test();
WARNING:  Error occurred while executing PL/pgSQL function test
WARNING:  line 6 at assignment
ERROR:  parser: parse error at or near "$1" at character 40
And that is the _test variable

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


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

[GENERAL] Prepared Statements

[Eric Dahnke]

Does anyone know the exact definition of a Prepared Statement? 

 String sql = "SELECT count(id) FROM customer";
 executeQuery(sql);

I assume the sql string is a prepared statement? Semantic debate here. -
Thx 


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

Re: [GENERAL] plpgsql extract epoch problem

[Alvaro Herrera]

On Wed, Oct 22, 2003 at 12:50:22PM -0400, Darren Ferguson wrote:

> create or replace function test() returns integer as '
> declare
>  _test timestamp with time zone;
>  _f integer;
> begin
>  _test := current_timestamp;
>  _f := extract(epoch from timestamp _test);

Why not simply
_f := extract(epoch from _test);
?

>  return _f;
> end;' language 'plpgsql';

-- 
Alvaro Herrera ()
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboración de civilizaciones dentre de él no son, por desgracia,
nada idílicas" (Ijon Tichy)

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

Re: [GENERAL]

[Fernando Nasser]

Andrew Overholt wrote:
djee
Bcc: 
Subject: Re: [PORTS] [GENERAL] Redhat RPMs
Reply-To: Andrew Overholt <[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>

Lamar Owen once said:

On Friday 10 October 2003 08:52 pm, Christopher Browne wrote:

Oops! [EMAIL PROTECTED] ("Nigel J. Andrews") was seen 
spray-painting on a wall:

I've not looked at many RPMs but I must say that the few I have have
never been relocatable. Can the postgresql RPMs not be made
relocatable?

I tried this once but had many issues with initdb and a few others IIRC.
The locations of data directories and such was being hard-coded in them at
compile time and I had to do a lot of sed-ing in the specfile to make it
work.  I know I accomplished that but ended up hitting a wall with
something ... I'll look in my old mail to remind myself what it was.
If we go with the Debian proposal of a specific package name for each 
version that has a different catalog version (needs pg_dump + 
pg_restore) and multiple versions installed we can basically eliminate 
this.  The paths provided to configure in each of these packages will 
have a different base directory, which includes the version, so each 
package will be a 'normal' package, not a relocatable one.



--
Fernando Nasser
Red Hat Canada Ltd. E-Mail:  [EMAIL PROTECTED]
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] [Erserver-general] alternate eRServer build

[Andrew Sullivan]

On Wed, Oct 22, 2003 at 02:43:54PM -0400, Andrew Rawnsley wrote:
> 
> This is to announce an alternate fork/build of the eRServer 1.2 code 
> base, provided to the community for testing purposes. If
> everything works out fine, and the Powers That Be approve, we can work 
> on moving some of it to the main distribution. This is a
> first release, so be warned.

The patches are also in the "patch" area (assuming they're the same)
of the erserver pages.

I'm a little leery of applying these, because as they are, they will
almost certainly break pre-7.3 systems.  What are people's feelings
about that?  Should we just accept that, as 7.2 is now officially
deprecated, we need not support it?

I'm cc:ing this to the pgsql-general list, because I think several
people who are trying out the code are not on this list.

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


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

Re: [GENERAL] Identifier max length

[Dennis Gearon]

Bruce Momjian wrote:

Birahim FALL wrote:
 

Hi, Certainly a stupid question : What is the max length for
identifiers.  A test shows me 63 for a table name.  I just
imagine it's the same for constraint name, view name etc ...
but I'm not sure.  Or where can I get this info, because it's
not in the Postgresl docs !  Thanks
   

Yep, all 63 limit.  Not sure where it is documented.

 

The lengths can be found in 7.3.2 manauls at: (kinda haphazard)
--
Tutorial - Section 1.3 pg 3
Reference - Section CREATE OPERATOR pg 2 of section
Reference - Section LISTEN pg 2 of section
Reference - Section NOTIFY pg 2 of section
Admin - Appendix A- Section 3.1 - pg 108
Admin - Appendix A- Section 3.3.5 - pg 111
User - Section 1.1.1 pg 2
User - Section 1.1.1 pg 5
User - Section 5.3 pg 58








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

Re: [GENERAL] [Erserver-general] alternate eRServer build

[Andrew Rawnsley]

On Wednesday, October 22, 2003, at 03:03 PM, Andrew Sullivan wrote:

On Wed, Oct 22, 2003 at 02:43:54PM -0400, Andrew Rawnsley wrote:
This is to announce an alternate fork/build of the eRServer 1.2 code
base, provided to the community for testing purposes. If
everything works out fine, and the Powers That Be approve, we can work
on moving some of it to the main distribution. This is a
first release, so be warned.
The patches are also in the "patch" area (assuming they're the same)
of the erserver pages.
I'm a little leery of applying these, because as they are, they will
almost certainly break pre-7.3 systems.  What are people's feelings
about that?  Should we just accept that, as 7.2 is now officially
deprecated, we need not support it?
I wouldn't recommend applying them yet, either. I would like to let 
people kick the stuff around for a while. I
can't claim exhaustive testing on it, and don't want to embarrass 
myself too much...

Actually, it won't break pre-7.3 systems. I managed to pack it all into 
one distribution with a setup option for no schema support.
I have tested it against 7.3.4 and 7.2.4. Even 7.3.4 replicating to 
7.2.4.


I'm cc:ing this to the pgsql-general list, because I think several
people who are trying out the code are not on this list.
A

--

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110
___
Erserver-general mailing list
[EMAIL PROTECTED]
http://gborg.postgresql.org/mailman/listinfo/erserver-general


Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Trigger

[scott.marlowe]

On Wed, 22 Oct 2003, Peter Childs wrote:

>   Is it possible to deferr a trigger until commit, Or to have the
> trigger not occur if the transaction is rolled back? Like transaction.
>   I think its possible since constraints use triggers and if so why
> is this a standard feature.
>   Also is there anyway of seeing what triggers exsist and what they
> do? (psql \ or the like)

A trigger inside a transaction should automagically roll back should the 
transaction fail, shouldn't it?


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

[GENERAL] plpgsql extract epoch problem

[Darren Ferguson]

Hey all

I am trying to extract the epoch from a timestamp variable in plpgsql.
The function is as follows:
create or replace function test() returns integer as '
declare
 _test timestamp with time zone;
 _f integer;
begin
 _test := current_timestamp;
 _f := extract(epoch from timestamp _test);
 return _f;
end;' language 'plpgsql';
Any ideas why this will not work

The error is as follows:
pts=> select test();
WARNING:  Error occurred while executing PL/pgSQL function test
WARNING:  line 6 at assignment
ERROR:  parser: parse error at or near "$1" at character 40
And that is the _test variable

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

[GENERAL] ANNOUNCE: Bricolage 1.6.7

[David Wheeler]

I'm pleased to announce the release of Bricolage 1.6.7. This maintenance
release addresses a few issues discovered since the release of version
1.6.6. Some of the more important changes include:
*   Fixed "bric_soap" to accept a "--server" argument starting with
"https", which is more friendly to an SSI environment.
*   The PostgreSQL admin username and password arguments were 
reversed
during "make upgrade".

*   Added partial index to speed queries against the job table, and 
thus
to speed distribution.

*   Updated slug RegExen. They were a bit too strict, and should be
better now, allowing dots, dashes, and underscores.
*   Inactive alert types no longer trigger the sending of alerts.

*   Fixed "element_data_id" parameter to
Bric::Biz::Asset::Business::Parts::Tile::Data to actually work.
See below for a complete list of the changes.

  http://sourceforge.net/project/shownotes.php?release_id=192775

ABOUT BRICOLAGE

Bricolage is a full-featured, enterprise-class content management and
publishing system. It offers a browser-based interface for ease-of use,
a full-fledged templating system with complete HTML::Mason and
HTML::Template support for flexibility, and many other features. It
operates in an Apache/mod_perl environment, and uses the PostgreSQL
RDBMS for its repository. A comprehensive, actively-developed open
source CMS, Bricolage has been hailed as "Most Impressive" in 2002 by
eWeek.
Learn more about Bricolage and download it from the Bricolage home page,
http://bricolage.cc/.
Enjoy!

David

--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED]  ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
   Jabber: [EMAIL PROTECTED]
Kineticode. Setting knowledge in motion.[sm]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Trigger

[Peter Eisentraut]

scott.marlowe writes:

> A trigger inside a transaction should automagically roll back should the
> transaction fail, shouldn't it?

The database actions that the trigger possibly executed are rolled back
just like any other database actions.  But if your trigger does something
external to the database, you have a problem (that borders on
unsolvable).

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

   http://archives.postgresql.org

Re: [GENERAL] database files

[jearl]

Gail Zacharias <[EMAIL PROTECTED]> writes:

> I am investigating the possibility of using pgsql as the database in
> an application.  I have some unusual requirements that I'd like to
> ask you all about.  I apologize in advance if my terminology is a
> little "off", I'm not familiar with pgsql (yet).

I think your terminology is fine.

> I need to be able to move the database files, as normal user-visible
> files, between machines. I.e. given a database on machine A, I want
> to be able to copy either a single file (ideally) or a single
> directory (less ideal but still ok) to, say, a zip drive, bring it
> over to another machine (with pgsql also installed), start up my
> application and have it access the copied database through pgsql.

The way to do this with PostgreSQL is to make a backup of the database
and then load it into the other machine.  For example on the master
database you would do:

pg_dumpall --clean --verbose > backup.sql

You would then put that backup.sql file on your zip disk or whatever
and carry it to your new machine where you would do something like:

psql -U postgres template1 -f backup.sql

> Is this sort of thing possible?  Is a database stored in a single
> file or multiple files?  Can the location of the file(s) be
> controlled?  Are the files accessible and consistent while pgsql is
> running? I assume not all the time, but is there a reliable way to
> make them accessible (i.e. copyable) and consistent short of
> shutting down pgsql?

Databases are stored in multiple files in a directory plus the log
files and whatnot are stored in another part of the directory
structure.  It is theoretically possible to shutdown your postmaster
and then copy the files to another location, but I wouldn't recommend
it.  pg_dumpall works well, and it is far more fullproof.

> Is the file format of the pgsql database files compatible between
> OS's?  E.g. could I take some database files from Linux and use them
> on Windows?

I don't know the answer to that, but I would be interested in finding
out.  My theory is that file formats and other arcana are far better
left to Tom Lane and the rest of the PostgreSQL hackers.  This is
especially true considering the fact that on disk formats change
between versions.

Did I mention that pg_dumpall will solve your problem handily?

> Thanks in advance for any advice,

Jason

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

Re: [GENERAL] database files

[Tom Lane]

[EMAIL PROTECTED] writes:
> Gail Zacharias <[EMAIL PROTECTED]> writes:
>> Is the file format of the pgsql database files compatible between
>> OS's?  E.g. could I take some database files from Linux and use them
>> on Windows?

> I don't know the answer to that, but I would be interested in finding
> out.

I don't think there are any OS dependencies per se, but there are
certainly hardware dependencies (forget moving between Intel and PPC
without a dump/reload, for example).  And there are compiler
dependencies, so theoretically you could run into problems even for
two different systems on the same hardware platform.

I concur with the recommendation to use pg_dump scripts as the vehicle
for moving data.

regards, tom lane

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

[GENERAL] ANNOUNCE: Bricolage 1.6.7

[David Wheeler]

I'm pleased to announce the release of Bricolage 1.6.7. This maintenance
release addresses a few issues discovered since the release of version
1.6.6. Some of the more important changes include:
*   Fixed "bric_soap" to accept a "--server" argument starting with
"https", which is more friendly to an SSI environment.
*   The PostgreSQL admin username and password arguments were 
reversed
during "make upgrade".

*   Added partial index to speed queries against the job table, and 
thus
to speed distribution.

*   Updated slug RegExen. They were a bit too strict, and should be
better now, allowing dots, dashes, and underscores.
*   Inactive alert types no longer trigger the sending of alerts.

*   Fixed "element_data_id" parameter to
Bric::Biz::Asset::Business::Parts::Tile::Data to actually work.
See below for a complete list of the changes.

  http://sourceforge.net/project/shownotes.php?release_id=192775

ABOUT BRICOLAGE

Bricolage is a full-featured, enterprise-class content management and
publishing system. It offers a browser-based interface for ease-of use,
a full-fledged templating system with complete HTML::Mason and
HTML::Template support for flexibility, and many other features. It
operates in an Apache/mod_perl environment, and uses the PostgreSQL
RDBMS for its repository. A comprehensive, actively-developed open
source CMS, Bricolage has been hailed as "Most Impressive" in 2002 by
eWeek.
Learn more about Bricolage and download it from the Bricolage home page,
http://bricolage.cc/.
Enjoy!

David

--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED]  ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
   Jabber: [EMAIL PROTECTED]
Kineticode. Setting knowledge in motion.[sm]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] ANNOUNCE: Bricolage-Devel 1.7.0

[David Wheeler]

It give me great pleasure to announce the release of Bricolage-Devel 
1.7.0,
the first development release for what will eventually become Bricolage
1.8.0. In addition to all of the bug fixes included in the 1.6.x series,
this version of the open-source content management system adds a number 
of
significant new features. The most significant changes include:

*   Added multisite support. Now all stories, media, output 
channels,
templates, categories, and workflows may be associated with
different sites, and even have the same names in different 
sites.
This simplifies the management of multiple Web sites with 
Bricolage.
Story type and media type elements may be shared between sites.
Funded by Portugal Telecom Multimedia.

*   Added document aliasing. Stories and media in a site may now be
aliased and published in another site, as long as the elements 
on
which they are based are shared between sites. Control over the
content of aliased documents remains in the original site, thus
ensuring the editorial integrity of the document for that site.
Funded by Portugal Telecom Multimedia.

*   Added "$burner->sdisplay_element" method to Bric::Util::Burner. 
This
is a "sprintf"-style version of "$burner->display_element".

*   Added the "YEAR_SPAN_BEFORE" and "YEAR_SPAN_AFTER" 
bricolage.conf
directives. These directives enable control how many years 
before
and after the current year to display in the list of years in 
the
date and time select widget. The default values are 10 for each,
meaning that if the current year is 2003, then the date span 
will be
from 1993 to 2013.

*   Added "Email" action, which can be used to email the files 
generated
by a publish to one or more email addresses. Funded by ETonline.

*   Callbacks were moved from Mason components to modules based on
Params::Callback and managed by MasonX::Interp::WithCallbacks. 
This
makes the UI layer more responsive and enhances maintainability.

*   Optimized performance of URI uniqueness checks by adding 
database
tables to do the job, rather than constructing the URIs for all
other documents in the same categories as the document being
checked. This was the last major bottleneck affecting SOAP
performance, as well as document editing in general. Funded by
Kineticode.

*   Added "output_channel_id" parameter to the "list()" methods of 
Story
and Media to enable querying for documents in output channels 
other
than the primary output channel.

*   Added Keyword Management interface to centrally manage keywords.

*   Added HTML::Mason Custom tags support, allowing template 
developers
to write code blocks that are context sensitive.

*   Added new page extension support to the burner, which allows
template developers to set string extensions to use for 
successive
file names, rather than the traditional use of numeric file name
extensions for successive file names.

*   Added "Text to search" option in the Advanced search of Media 
and
Stories to search for documents based on the contents of their
field.

*   All preview links are now generated by a single widget. This 
widget
adds the story or media URI to the "title" attribute of the 
link tag
(which is modern browsers will automatically work as a roll-over
tooltip), makes the story or media URI copyable (by relying on
JavaScript to actually open a new window for the preview), and
manages selecting an output channel in which to preview a story.

*   Made User Group Permissions UI wieldy with larger numbers of 
users
by adding a select list to choose which type of Permission to 
look
at.

*   Added "contrib_id" parameter to the "list()" methods of
Bric::Biz::Asset::Business::Story and
Bric::Biz::Asset::Business::Media to return a list of story or 
media
documents associated with a given contributor.

*   Switched Bric::Util::CharTrans from using Text::Iconv to Encode,
thus removing the dependency on a C library (libiconv). Note 
that
this has changed the API of Bric::Util::CharTrans. Its 
"to_utf8()"
and "from_utf8()" methods now always convert the argument 
passed in
in place. They did this before for references, but now they do 
it
for plain strings, as well. Also note that use of character
translation also now requires Perl 5.8.0 or later.

*   Added MediaType, Site, and Keyword SOAP modules.

*   Added "element" attribute to Bric::Util::Burner so that
"$burner->get_element" should always return the element 
currently
being burned.

*   Added a "throw_error()" method to Bric::Util::Burner so that
template developers ca

[GENERAL] Indexes and querys

[Josué Maldonado]

Hello list,

I have a table with an index in a foreing key field (int4), if I do 
select from pedido where prvdfk=3, explain says is using seq scan to 
access it. I noticed all my querys runs in that way, except in the 
querys where the PK field (unique type index) is included in the where 
clause.

Is this the rigth way that postgresql works ?, is there something I 
should check in my configuration?

According to select version() this is the my one:
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 
20020903 (Red Hat Linux 8.0 3.2-7)

Thanks in advance

--
Josué Maldonado.


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

[GENERAL] Adding fields on the fly

[Gail Zacharias]

I am investigating the possibility of using pgsql as the database in an application.  
I have some unusual requirements that I'd like to ask you all about.  I apologize in 
advance if my terminology is a little "off", I'm not familiar with pgsql (yet).

My first requirement is that I need to be able to add new columns to any database 
table on the fly.  By this I mean that I need this to happen interactively -- a user 
will drag some UI widget from here to there and boom! my application will request a 
new column to be added to a table, and wait for it to happen before giving visual 
feedback to the user.  My questions are:

Is this even possible? Is ALTER TABLE supported?
If so, what performance characteristics can I expect?  Compared to, say, inserting a 
record, can I expect this to be 10 times slower? 1000 times slower?
What does the performance depend on?  Number of rows? Number of columns? Number of 
tables? anything else?


I'll send my other question in a separate message to keep the threads separate.

Thanks in advance,

Gail Zacharias


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

Re: [GENERAL] Adding fields on the fly

[Peter Eisentraut]

Gail Zacharias writes:

> Is this even possible? Is ALTER TABLE supported?

Yes. (RTFM)

> If so, what performance characteristics can I expect?  Compared to, say,
> inserting a record, can I expect this to be 10 times slower? 1000 times
> slower? What does the performance depend on?  Number of rows? Number of
> columns? Number of tables? anything else?

Adding a column runs in constant time.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [GENERAL] plpgsql: return multiple result sets or temp table

[Jeff Eckermann]

--- Oksana Yasynska <[EMAIL PROTECTED]> wrote:
> Jeff,
> 
> thank you for the time and suggestion.
> I'm also trying to use SETOF custom_type as a
> solution

I don't believe that works in 7.2.  But if you do make
it work, be sure to tell me how ;-)
That will work with 7.3, but I am assuming that, as
your first message said, you cannot upgrade the
server.
Best of luck.

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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

   http://archives.postgresql.org

Re: [GENERAL] Identifier max length

[Bruce Momjian]

Birahim FALL wrote:
> Hi, Certainly a stupid question : What is the max length for
> identifiers.  A test shows me 63 for a table name.  I just
> imagine it's the same for constraint name, view name etc ...
> but I'm not sure.  Or where can I get this info, because it's
> not in the Postgresl docs !  Thanks

Yep, all 63 limit.  Not sure where it is documented.

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

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

[GENERAL] database files

[Gail Zacharias]

I am investigating the possibility of using pgsql as the database in an application.  
I have some unusual requirements that I'd like to ask you all about.  I apologize in 
advance if my terminology is a little "off", I'm not familiar with pgsql (yet).

I need to be able to move the database files, as normal user-visible files, between 
machines. I.e. given a database on machine A, I want to be able to copy either a 
single file (ideally) or a single directory (less ideal but still ok) to, say, a zip 
drive, bring it over to another machine (with pgsql also installed), start up my 
application and have it access the copied database through pgsql.

Is this sort of thing possible?  Is a database stored in a single file or multiple 
files?  Can the location of the file(s) be controlled?  Are the files accessible and 
consistent while pgsql is running? I assume not all the time, but is there a reliable 
way to make them accessible (i.e. copyable) and consistent short of shutting down 
pgsql?

Is the file format of the pgsql database files compatible between OS's?  E.g. could I 
take some database files from Linux and use them on Windows?

Thanks in advance for any advice,

Gail Zacharias


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

Re: [GENERAL] database files

[Dann Corbit]

> -Original Message-
> From: Gail Zacharias [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, October 22, 2003 12:42 PM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] database files
> 
> 
> I am investigating the possibility of using pgsql as the 
> database in an application.  I have some unusual requirements 
> that I'd like to ask you all about.  I apologize in advance 
> if my terminology is a little "off", I'm not familiar with 
> pgsql (yet).
> 
> I need to be able to move the database files, as normal 
> user-visible files, between machines. I.e. given a database 
> on machine A, I want to be able to copy either a single file 
> (ideally) or a single directory (less ideal but still ok) to, 
> say, a zip drive, bring it over to another machine (with 
> pgsql also installed), start up my application and have it 
> access the copied database through pgsql.
> 
> Is this sort of thing possible?  Is a database stored in a 
> single file or multiple files?  Can the location of the 
> file(s) be controlled?  Are the files accessible and 
> consistent while pgsql is running? I assume not all the time, 
> but is there a reliable way to make them accessible (i.e. 
> copyable) and consistent short of shutting down pgsql?
> 
> Is the file format of the pgsql database files compatible 
> between OS's?  E.g. could I take some database files from 
> Linux and use them on Windows?

The generic way to accomplish what you want is with the COPY command.
http://developer.postgresql.org/docs/postgres/sql-copy.html

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

Re: [GENERAL] Indexes and querys

[Peter Eisentraut]

Josué Maldonado writes:

> I have a table with an index in a foreing key field (int4), if I do
> select from pedido where prvdfk=3, explain says is using seq scan to
> access it. I noticed all my querys runs in that way, except in the
> querys where the PK field (unique type index) is included in the where
> clause.

PostgreSQL will choose to use an index when it thinks it will be faster,
but this is not always the case.  If you want to learn more about how
indexes work or how to evaluate query performance, please read the
documentation.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

   http://archives.postgresql.org

Re: [GENERAL] Same conditions, different planning?

[Doug McNaught]

Pedro Alves <[EMAIL PROTECTED]> writes:

> As a final attempt I tryed a pg_dumpall; initdb; psql -f  . It
> seems to work as expected (!)

It may have been a case of index bloat--did you ever REINDEX?  That
might have fixed it without a dump/reload.

7.4 has fixes for the index bloat problem.

-Doug

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

[GENERAL] Atrybutes ???

[Grendel]

Hello !!!
I have store some attrybutes to database. Attrybutes are set of integer values. 
{1,5,7} is valid set, but {1,3,1} is not valid set

I have to build compare relation i.e.
{1,5,7} is equivalent to {5,7,1} or {1,7,5} etc.
but 
{1,5,7} is not equivalent to {5,1} or {1,4,2}

What's best solution: arrays, master detail relation, something else...

Thnaks in advance
Grendel



___
NOCC, http://nocc.sourceforge.net



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

[GENERAL] Identifier max length

[Birahim FALL]

Title: Message



Hi,
Certainly a stupid 
question : What is the max length for identifiers.
A test shows me 63 
for a table name.
I just imagine it's 
the same for constraint name, view name etc ... but I'm not 
sure.
Or where can I get 
this info, because it's not in the Postgresl docs !
Thanks

[GENERAL] PostgreSQL v7.4 Beta5 Available for Testing

[Marc G. Fournier]

On Oct 3rd, we released beta4 in the hopes that it would be the final beta
before going into the "Release" phase of development ... due to several
bug fixes over the past 3 weeks, we've decided to release a Beta5 in order
to flesh out any bugs that may have been introduced ...

Beta5 contains several fixes that require an initdb from Beta4, with a
full ChangeLog available in the source directory ... most of what went
into Beta5 was documentation, but with the initdb requirement, we felt
that a RC would be pre-mature.

With suitable testing, and few bugs reported, we hope to get an RC1 out
the door within the next 7 to 10 days, leading up to a full release of
v7.4 ...

... so, if you haven't tested her out yet, please do so ...



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

   http://archives.postgresql.org

Re: [GENERAL] Postgreqsl & Package

[Shridhar Daithankar]

On Wednesday 22 October 2003 17:11, Birahim FALL wrote:
> Thanks, Shridhar,
> I've read something like that in my search this morning!
> But doing that you can have only one package in the scope of a schema.
> Then in a multipackage project these packages cannot access directly to
> the same objects.
> In you eg. We could have a table a.people, and it is not in a scope
> accessible to b.test1 without a grant statement.

Yes that is correct. In fact you could look at it as additional security 
feature. i.e. functions from a package can only be accessed by a certain 
users..:-)

Besides there is only one grant you need per schema, that is usage. Shouldn't 
be a such a big problem, isn't it?

I would be surprised if oracle packages did not provide this feature.

> More info is welcomed,

Check schema search path

http://developer.postgresql.org/docs/postgres/sql-set.html

I just checked if I could create nested schemas. That would be way too cool.. 
but it is not supported..:-(

HTH

 Shridhar


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

Re: [GENERAL] Postgreqsl & Package

[Shridhar Daithankar]

On Wednesday 22 October 2003 16:41, Birahim FALL wrote:
> Hi,
> I was an oracle dev/admin and I'm quite new to postgresql.
> Postgresql is great, but something bothers me.
> It seems that there's no concept of PACKAGE in PL/pgSQL as in Oracle
> PL/SQL.
> Is is definitely that? or did I missed something? or is it planned for a
> future version?

You can use scemas instead of packages.  You still might miss few details 
w.r.t oracle package but in general that should fill in the gap pretty 
nicely.

Check the following

test=# create schema a;
CREATE SCHEMA
test=# create schema b;
CREATE SCHEMA
test=# create function a.test1() returns boolean as 'begin
test'# return true;
test'# end;' language plpgsql;
CREATE FUNCTION
test=# create function b.test1() returns boolean as 'begin
test'# return false;
test'# end;' language plpgsql;
CREATE FUNCTION
test=# select a.test1();
 test1
---
 t
(1 row)

test=# select b.test1();
 test1
---
 f
(1 row)

HTH

 Shridhar


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