Re: [GENERAL] Postgres won't start. Nothing in the log.

[Markus Wollny]

Hi!

pgsql-general-ow...@postgresql.org wrote:
> Unfortunately there is nothing anywhere telling me what the
> problem is. The log file is empty, there is nothing in the
> /var/log/messages or /var/log/syslog either. The only output
> I get is this.
> 
> * Starting PostgreSQL 8.3 database server
>  * The PostgreSQL server failed to start. Please check the log output.
> What can I do to figure out why it won't start?

Did you check if the directory PostgreSQL wants to write the logfile to
has appropriate rights to allow the user PostgreSQL runs under to write
to it? Another fairly common issue would be a postgresql.conf that is
not readable by the PostgreSQL-user. In case of such an outright failure
to start, it's usually something fairly simple to fix such as file or
directory permissions.

Kind regards

   Markus



Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard 
Umsatzsteuer-Identifikationsnummer: DE 812 575 276



-- 
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] attempted to lock invisible tuple - PG 8.4.1

[Alban Hertroys]

On 5 Oct 2009, at 8:58, Stuart Bishop wrote:


I'm running our products test suite against PostgreSQL 8.4.1. The test
suite runs fine against 8.3.7.

With 8.4.1, some of our tests are failing with the exception
'attempted to lock invisible tuple'. The failures are repeatable -
they crash every time at the same point. They crash no matter if they
are being run in isolation or as part of the larger test suite.

Anyone know what we could be doing that triggers that? Looking at our
statement logs we don't seem to be doing anything unusual. The failing
tests I've checked are running under SERIALIZABLE isolation level, and
the database will have been recreated a few instants ago using
'createdb --template test_template_db'.


A similar issue was discussed just recently here: 
http://archives.postgresql.org/pgsql-general/2009-09/msg01219.php

That issue involved cursors though (and a serializable isolation  
level, but you have that). Do you have any triggers that use cursors  
on the table that the update fails for?



One of the statement logs is at http://paste.ubuntu.com/285983/  - I
can't see anything unusual
going on but it might help diagnose the problem.


Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4ac9bad911688389419940!



--
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] Postgres won't start. Nothing in the log.

[Thom Brown]

2009/10/5 Markus Wollny 

> Hi!
>
> pgsql-general-ow...@postgresql.org wrote:
> > Unfortunately there is nothing anywhere telling me what the
> > problem is. The log file is empty, there is nothing in the
> > /var/log/messages or /var/log/syslog either. The only output
> > I get is this.
> >
> > * Starting PostgreSQL 8.3 database server
> >  * The PostgreSQL server failed to start. Please check the log output.
> > What can I do to figure out why it won't start?
>
>
>
Was it a minor-release upgrade? (i.e. 8.3.1 to 8.3.7 and not 8.2. to 8.3)
If not, you would have needed to do a complete dump and restore of your
data.

To force a log file, try pg_ctl start -l /path/to/logfile

You might as well use /tmp/pg.log for the logfile for diagnosis.  This
should tell you what the problem is.

Thom

Re: [GENERAL] Procedure for feature requests?

[Sam Mason]

On Mon, Oct 05, 2009 at 12:03:38AM +, Tim Landscheidt wrote:
> Sam Mason  wrote:
> >Tim Landscheidt wrote:
> >> "generate_series(A, B, C)" can also
> >> be written as "A + generate_series(0, (C - B) / C) * C"
> >
> > If you can figure out the limit then it seems easy,
> > though I'm not sure how you'd do that.
> 
> What limit?

Sorry, I was calling the second parameter to generate_series the "limit".

Calculating "(C - B) / C" isn't easy for timestamps, whereas it's easy
for dates.  I believe this is why there's a specific version for the
former but not the latter.

> > Hum, now I'll have to see which is "better".
>
> Which of my mails made you think that I was not satisfied
> with PostgreSQL's current performance?

Nothing, it's was my personal interest to see which was faster.

> "generate_series(DATE, DATE)" would just be syntactic sugar,
> and I like sweets.

We all do, but in software it's got to be balanced against the overhead
of maintaining support for these functions.

-- 
  Sam  http://samason.me.uk/

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

[GENERAL] connection failure

[Miklosi Attila]

What does the message below mean?

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

This message appears sometimes after i send a 'BEGIN' to the server and
the processor load of the application goes up to 99%. This happens
usually when the application starts and several threads start sending
queries to save the changes since the last stop. 

The application its locking mechanism (we are aware of that libpq
doesn't have interlocking) has worked fine in the past. This issue has
became serious since we moved to the latest debian lenny distribution
and started to use libpq5.

OS : Debian linux Lenny kernel version 2.6.26-2-686
libpq : libpq5 8.3.7-0lenny

Thank you in advance
Attila Miklosi


-- 
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] connection failure

[Sam Mason]

On Mon, Oct 05, 2009 at 03:06:41PM +0200, Miklosi Attila wrote:
> What does the message below mean?
> 
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.

It means the server disconnected from the client; as it says this can
happen because the server has crashed.  To figure out what really
happened, you want to look at postgres' logs on the server.

-- 
  Sam  http://samason.me.uk/

-- 
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] connection failure

[Allan Kamau]

Could it be that a connection may be invoking a newly recreated
function rewritten in C that it had previously called before it's
recreation?

Allan.

On Mon, Oct 5, 2009 at 3:06 PM, Miklosi Attila  wrote:
>
> What does the message below mean?
>
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
> This message appears sometimes after i send a 'BEGIN' to the server and
> the processor load of the application goes up to 99%. This happens
> usually when the application starts and several threads start sending
> queries to save the changes since the last stop.
>
> The application its locking mechanism (we are aware of that libpq
> doesn't have interlocking) has worked fine in the past. This issue has
> became serious since we moved to the latest debian lenny distribution
> and started to use libpq5.
>
> OS : Debian linux Lenny kernel version 2.6.26-2-686
> libpq : libpq5 8.3.7-0lenny
>
> Thank you in advance
> Attila Miklosi
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Postgres won't start. Nothing in the log.

[Tom Lane]

"Markus Wollny"  writes:
> pgsql-general-ow...@postgresql.org wrote:
>> Unfortunately there is nothing anywhere telling me what the
>> problem is. The log file is empty, there is nothing in the
>> /var/log/messages or /var/log/syslog either. The only output
>> I get is this.
>> 
>> * Starting PostgreSQL 8.3 database server
>> * The PostgreSQL server failed to start. Please check the log output.
>> What can I do to figure out why it won't start?

> Did you check if the directory PostgreSQL wants to write the logfile to
> has appropriate rights to allow the user PostgreSQL runs under to write
> to it? Another fairly common issue would be a postgresql.conf that is
> not readable by the PostgreSQL-user. In case of such an outright failure
> to start, it's usually something fairly simple to fix such as file or
> directory permissions.

As Markus suggests, it's highly unlikely that Postgres is failing
without any message whatsoever.  What is more likely is that the message
is being sent somewhere you're not looking, or there is a permissions
problem or something with where it was told to send it.  Take a look at
the init script you're using to start the server, and see where it sends
the postmaster's stderr.  You need to check that in case of failures
that occur before the configured log destination has taken effect.
(I have seen some scripts that redirect stderr to /dev/null :-( ...
if you have one of those, change it, and file a bug report with the
packager.)

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] object dependency workaround

[Little, Douglas]

Hello,
We're migrating to Greenplum, and in our currently portfolio we base everything 
on views.
In our prior system, we could alter tables and then refresh the views, but in 
PG/greenplum most alters are blocked because of the dependent objects.

I understand that I can walk the pg_depends table to see the linkages, but what 
I really want to know
Is there a way to change the system behavior so that  ALTERs are allowed, 
perhaps invalidating dependent objects

Short of this, is there a recommended framework/functions for spooling off the 
dependent objects, performing the alter and then recreating all of the 
dependent objects?

Thanks  in advance.
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
douglas.lit...@orbitz.com

 [cid:image002.jpg@01CA459B.F991A680]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] Postgres won't start. Nothing in the log.

[Devrim GÜNDÜZ]

On Mon, 2009-10-05 at 17:39 +1300, Tim Uckun wrote:

> Unfortunately there is nothing anywhere telling me what the problem
> is. The log file is empty, there is nothing in the /var/log/messages
> or /var/log/syslog either. The only output I get is this.
> 
> * Starting PostgreSQL 8.3 database server
>  * The PostgreSQL server failed to start. Please check the log output.


Is this Debian/Ubuntu? If so, the logs should be
at /var/log/postgresql/postgresql-8.3-main.log (or so).
-- 
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] How to have ant's task insert special chars appropriately?

[agostonbejo]

Thanks, you were right - actually the problem was somewhere else. During the
build I had the sql files copied over to another location, feeding them to
ant's  -- and it was there actually where the encodings got
mixed up. 

(Needless to say, I was looking at the original file and thought it was the
 or PostGres that messed something up.)

Anyway, after correcly copying over the file, the one with the ISO-8859-1
could be used perfectly. (I suppose the UTF-8 could have as well, but I
didn't try it once this one worked.)


Thanks,
Agoston



Richard Huxton wrote:
> 
> Check again - something isn't right. Take the original ,sql file, save
> it as UTF8 and add a line at the top "set client_encoding=utf8;"
> 
> Run this through psql and it should work fine. If not, then the database
> isn't in utf8 after all.
> 
> Assuming it works, then something in your java setup isn't correct.
> 
> -- 
>   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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/How-to-have-ant%27s-%3Csql%3E-task-insert-special-chars-appropriately--tp25530663p25748884.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Errors regarding transporting database using pg_dump

[Chun-fan Ivan Liao]

I want to transport the database server to a new machine.

The OS and the PostgreSQL version of the old machine: FreeBSD 6.1-RELEASE &
psql 8.1.3
And that of the new machine: FreeBSD 7.2-RELEASE & psql 8.4.0
(Using  "uname -a"  "psql -U pgsql" to check the version)

I used pg_dump to dump the old database out and psql to store the database
into the new server, but the following two errors occurred:

ERROR:  incompatible library "/lib/libc.so.6": missing magic block
HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
AS '/lib/libc.so.7', 'system'
LANGUAGE c STRICT;
ERROR:  function public.system(cstring) does not exist
STATEMENT:  ALTER FUNCTION public."system"(cstring) OWNER TO pgsql;
ERROR:  value too long for type character varying(12)
CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "鷓埥(9debacdd)砆"
STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR", "S_TERM", "COU_CODE",
"CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME", "YEAR", "CREDIT", "COU_CNAME",
"TEA_CODE", "TEA_CNAME", "STU_CNAME", "SCORE", "SCORE_A", "SERNO", "PANOPA",
"UNIT", "TEMP") FROM stdin;

The previous error was it could not find /lib/libc.so.6, so I installed the
port misc/compat6x and copy the libc.so.6 to /lib. If I replaced libc.so.6
with libc.so.7, the same error occured.

I've did some googling. Should I insert the line

PG_MODULE_MAGIC;

into the libc source code and recompile it? But I don't know where the code
resides and what the command is.

The above is about the first error, and for the second one I totally have no
idea. Could somebody please help me out?

Thanks in advance.

Re: [GENERAL] Postgres won't start. Nothing in the log.

[Martin Gainty]

did you install (i should say register) postgres and pg_ctl binaries as 
postgres user
are you executing postgres and pg_ctl binaries as postgres user
?
Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> Subject: Re: [GENERAL] Postgres won't start. Nothing in the log.
> From: dev...@gunduz.org
> To: timuc...@gmail.com
> CC: pgsql-general@postgresql.org
> Date: Mon, 5 Oct 2009 17:16:00 +0300
> 
> On Mon, 2009-10-05 at 17:39 +1300, Tim Uckun wrote:
> 
> > Unfortunately there is nothing anywhere telling me what the problem
> > is. The log file is empty, there is nothing in the /var/log/messages
> > or /var/log/syslog either. The only output I get is this.
> > 
> > * Starting PostgreSQL 8.3 database server
> >  * The PostgreSQL server failed to start. Please check the log output.
> 
> 
> Is this Debian/Ubuntu? If so, the logs should be
> at /var/log/postgresql/postgresql-8.3-main.log (or so).
> -- 
> Devrim GÜNDÜZ, RHCE
> Command Prompt - http://www.CommandPrompt.com 
> devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
> http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz
  
_
Hotmail: Free, trusted and rich email service.
http://clk.atdmt.com/GBL/go/171222984/direct/01/

Re: [GENERAL] Errors regarding transporting database using pg_dump

[Martijn van Oosterhout]

On Mon, Oct 05, 2009 at 06:07:44PM +0800, Chun-fan Ivan Liao wrote:
> I used pg_dump to dump the old database out and psql to store the database
> into the new server, but the following two errors occurred:
> 
> ERROR:  incompatible library "/lib/libc.so.6": missing magic block
> HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
> STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
> AS '/lib/libc.so.7', 'system'
> LANGUAGE c STRICT;

Ok, here someone was cheating by invoking a system library function
directly. That's no longer supported. If you really want this you need
to make a small library with PG_MODULE_MAGIC; and a pg_system() function
that redirects to the real system().

That said, why are you doint this anyway. A better solution may be to
install a trusted language (like plperlu or plpython) and do the system
call from there.

> ERROR:  value too long for type character varying(12)
> CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "??(9debacdd)???"
> STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR", "S_TERM", "COU_CODE",
> "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME", "YEAR", "CREDIT", "COU_CNAME",
> "TEA_CODE", "TEA_CNAME", "STU_CNAME", "SCORE", "SCORE_A", "SERNO", "PANOPA",
> "UNIT", "TEMP") FROM stdin;

Here the column is defined as 12 characters and you're trying to
inserts a 19 character string, which is bad. How you got that out of
pg_dump though I don't know.

Have a nice day,
-- 
Martijn van Oosterhout  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

Re: [GENERAL] attempted to lock invisible tuple - PG 8.4.1

[Stuart Bishop]

On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
 wrote:
> On 5 Oct 2009, at 8:58, Stuart Bishop wrote:
>
>> I'm running our products test suite against PostgreSQL 8.4.1. The test
>> suite runs fine against 8.3.7.
>>
>> With 8.4.1, some of our tests are failing with the exception
>> 'attempted to lock invisible tuple'. The failures are repeatable -
>> they crash every time at the same point. They crash no matter if they
>> are being run in isolation or as part of the larger test suite.
>>
>> Anyone know what we could be doing that triggers that? Looking at our
>> statement logs we don't seem to be doing anything unusual. The failing
>> tests I've checked are running under SERIALIZABLE isolation level, and
>> the database will have been recreated a few instants ago using
>> 'createdb --template test_template_db'.
>
> A similar issue was discussed just recently here:
> http://archives.postgresql.org/pgsql-general/2009-09/msg01219.php
>
> That issue involved cursors though (and a serializable isolation level, but
> you have that). Do you have any triggers that use cursors on the table that
> the update fails for?

There is a trigger on that table, and it is certainly the culprit as
can be seen here (different table, same trigger):

launchpad_dev=# begin;
BEGIN
launchpad_dev=# set transaction_isolation to serializable;
SET
launchpad_dev=# update bug set name='foooz' where id=1;
ERROR:  attempted to lock invisible tuple
launchpad_dev=# abort;
ROLLBACK
launchpad_dev=# alter table bug disable trigger tsvectorupdate;
ALTER TABLE
launchpad_dev=# begin;
BEGIN
launchpad_dev=# set transaction_isolation to serializable;
SET
launchpad_dev=# update bug set name='foooz' where id=1;
UPDATE 1
launchpad_dev=# abort;
ROLLBACK

I haven't had luck reducing this to a test case though. I'll give it
another shot tomorrow. Here are some more details for the audience:


launchpad_dev=# \d bug
Table "public.bug"
 Column |Type |
 Modifiers
+-+
 id | integer | not null
default nextval('bug_id_seq'::regclass)
 datecreated| timestamp without time zone | not null
default timezone('UTC'::text, ('now'::text)::timestamp(6) with time
zone)
 name   | text|
 title  | text| not null
 description| text| not null
 owner  | integer | not null
 duplicateof| integer |
 fti| ts2.tsvector|
 private| boolean | not null default false
 security_related   | boolean | not null default false
 date_last_updated  | timestamp without time zone | not null
default timezone('UTC'::text, now())
 date_made_private  | timestamp without time zone |
 who_made_private   | integer |
 date_last_message  | timestamp without time zone |
 number_of_duplicates   | integer | not null default 0
 message_count  | integer | not null default 0
 users_affected_count   | integer | default 0
 users_unaffected_count | integer | default 0
 hotness| integer | not null default 0
Indexes:
"bug_pkey" PRIMARY KEY, btree (id)
"bug_name_key" UNIQUE, btree (name)
"bug__date_last_message__idx" btree (date_last_message)
"bug__date_last_updated__idx" btree (date_last_updated) CLUSTER
"bug__datecreated__idx" btree (datecreated)
"bug__hotness__idx" btree (hotness)
"bug__users_affected_count__idx" btree (users_affected_count)
"bug__users_unaffected_count__idx" btree (users_unaffected_count)
"bug__who_made_private__idx" btree (who_made_private) WHERE
who_made_private IS NOT NULL
"bug_duplicateof_idx" btree (duplicateof)
"bug_fti" gist (fti)
"bug_owner_idx" btree (owner)
Check constraints:
"notduplicateofself" CHECK (NOT id = duplicateof)
"sane_description" CHECK (ltrim(description) <> ''::text AND
char_length(description) <= 5)
"valid_bug_name" CHECK (valid_bug_name(name))
Foreign-key constraints:
"bug__who_made_private__fk" FOREIGN KEY (who_made_private)
REFERENCES person(id)
"bug_duplicateof_fk" FOREIGN KEY (duplicateof) REFERENCES bug(id)
"bug_owner_fk" FOREIGN KEY (owner) REFERENCES person(id)
Referenced by:
TABLE "bugactivity" CONSTRAINT "$1" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bugsubscription" CONSTRAINT "$2" FOREIGN KEY (bug) REFERENCES bug(id)
TABLE "bug" CONSTRAINT "bug_duplicateof_fk" FOREIGN KEY
(duplicateof) REFERENCES bug(id)
TABLE "bugaffectsperson" CONSTRA

Re: [GENERAL] Errors regarding transporting database using pg_dump

[Tom Lane]

Chun-fan Ivan Liao  writes:
> STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
> AS '/lib/libc.so.7', 'system'
> LANGUAGE c STRICT;

This hack doesn't work any more --- not that it was ever considered
supported or recommended.  If you really need such a thing, I'd
suggest writing a plperlu or pltclu or plpythonu (according to taste)
wrapper around system().  But do you really need it, or is this just
cruft left over in your database from some playing around?  If the
latter, just ignore this error.

> ERROR:  value too long for type character varying(12)
> CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "鷓埥(9debacdd)砆"

This one looks like it might be an encoding issue.  What database
encoding did you have in the old server, and did you reproduce it
in the new one?

[ ... counts characters ... ]  although frankly that looks like it'd be
more than 12 characters by *anyone's* accounting.  Peculiar.  Maybe the
dump file got mangled while being copied over?

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] Limiting the impact of schema additions/poor queries made by clients on production machines

[Joshua Berry]

Our shop uses postgres for a dozen installations. The applications
have some realtime performance requirements, and are just good enough
to function properly. The problem is that the clients (owners of the
production servers) are using the same server/database for
customizations that are causing problems with the performance of our
applications.

Example of clients' customizations:
* Large tables with text datatypes that get cast in the queries
* No primary keys, indexes, FK constraints
* Use of external scripts that use count(*) from table where id = x,
in a loop from the script, to determine how to construct more queries
later in the same script.

The clients consider themselves experts and don't take
suggestions/criticism well. If we just go ahead and try to port/change
the scripts ourselves, the old code can come back, clobbering the
changes that we made!

My question is this: how can we limit the resources to
queries/applications other that what we create and deploy? Are there
any pragmatic options in scenarios like this? We prided ourselves in
having an OSS solution, but it seems that it's become a liability.

We use PG 8.3 running on a range on Linux Distos.

Regards,
-Joshua

P.S. I've cross-posted this on stackoverflow; I would love to hear any
stories or practical advice in this area, but I don't want to clutter
the pgsql-general list with non-pg advice. Here's the link:
http://stackoverflow.com/questions/1520645/how-to-limit-the-effect-of-client-modifications-to-production-systems

-- 
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] attempted to lock invisible tuple - PG 8.4.1

[Alvaro Herrera]

Stuart Bishop wrote:
> On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
>  wrote:

> > A similar issue was discussed just recently here:
> > http://archives.postgresql.org/pgsql-general/2009-09/msg01219.php
> >
> > That issue involved cursors though (and a serializable isolation level, but
> > you have that). Do you have any triggers that use cursors on the table that
> > the update fails for?
> 
> There is a trigger on that table, and it is certainly the culprit as
> can be seen here (different table, same trigger):

I don't think the committed patch touches anything involved in what
you're testing, but if you could grab CVS tip from the 8.4 branch (or
the snapshot from ftp.postgresql.org:/pub/snapshot/stable/8.4 ) and give
it a try, that'd be great.

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

[GENERAL] Looking for interest in an Irish PUG

[Raymond O'Donnell]

Hello all, and apologies for the cross-posting.

I'm looking to see if there is any interest in starting an Irish
PostgreSQL user group.

A couple of people have been in touch privately from time to time,
expressing interest in such a group, so I'm like to see if there are
enough people interested to make it viable.

Please contact me off-list if you'd like to be involved.

Many thanks,

Ray O'Donnell.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
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] Looking for interest in an Irish PUG

[Joshua D. Drake]

On Mon, 2009-10-05 at 17:16 +0100, Raymond O'Donnell wrote:
> Hello all, and apologies for the cross-posting.
> 
> I'm looking to see if there is any interest in starting an Irish
> PostgreSQL user group.
> 
> A couple of people have been in touch privately from time to time,
> expressing interest in such a group, so I'm like to see if there are
> enough people interested to make it viable.
> 
> Please contact me off-list if you'd like to be involved.

You may want to check with pgeu-general.

Joshua D. Drake



> 
> Many thanks,
> 
> Ray O'Donnell.
> 
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
> 
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


-- 
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] Postgres won't start. Nothing in the log.

[Andreas Kretschmer]

Tim Uckun  wrote:

> Unfortunately there is nothing anywhere telling me what the problem
> is. The log file is empty, there is nothing in the /var/log/messages
> or /var/log/syslog either. The only output I get is this.
> 
> * Starting PostgreSQL 8.3 database server
>  * The PostgreSQL server failed to start. Please check the log output.
> 
> 
> What can I do to figure out why it won't start?

I know, from IRC, the problem has been solved, there was no space on the
disk ...

Unfortunately, i haven't logs.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] Limit of bgwriter_lru_maxpages of max. 1000?

[Greg Smith]

On Sun, 4 Oct 2009, Gerhard Wiesinger wrote:


On Fri, 2 Oct 2009, Scott Marlowe wrote:


I found that lowering checkpoint completion target was what helped.
Does that seem counter-intuitive to you?




I set it to 0.0 now.


If you set that to 0.0, the whole checkpoing spreading logic doesn't apply 
like it's supposed to.  I'm not sure what the results you posted mean now. 
If you had it set to 0 and saw a bad spike (which is how I read your 
message), I'd say "yes, that's what happens when you do reduce that 
parameter, so don't do that".  If you meant something else please clarify.


Thanks for the dtrace example, I suggested we add those checkpoint probes 
in there and someone did, but I hadn't seen anybody use them for anything 
yet.



Bug1: usage_count is IHMO not consistent


It's a bit hack-ish, but the changes made to support multiple buffer use 
strategies introduced by the "Make large sequential scans and VACUUMs work 
in a limited-size ring" commit are reasonable even if they're not as 
consistent as we'd like.  Those changes were supported by benchmarks 
proving their utility, which always trump theoretical "that shouldn't work 
better!" claims when profiling performance.


Also, they make sense to me, but I've spent a lot of time staring at 
pg_buffercache output to get a feel for what shows up in there under 
various circumstances.  That's where I'd suggest you go if this doesn't 
seem right to you; run some real database tests and use pg_buffercache to 
see what's inside the cache when you're done.  What's in there and what I 
expected to be in there weren't always the same thing, and it's 
interesting to note how that changes as shared_buffers increases.  I 
consider some time studying that a pre-requisite to analyzing performance 
of this code.



Bug2: Double iteration of buffers
As you can seen in the calling tree below there is double iteration with 
buffers involved. This might be a major performance bottleneck.


Hmmm, this might be a real bug causing scans through the buffer cache to 
go twice as fast as intended.  Since the part you suggest is doubled isn't 
very intensive or called all that often, there's no way it can be a major 
issue though.  That's based on knowing what the code does and how much it 
was called, as well as some confidence that if it were really a *major* 
problem, it would have shown up on the extensive benchmarks done on all 
the code paths you're investigating.


BTW: Are there some tests available how fast a buffer cache hit is and a disk 
cache hit is (not in the buffer cache but in the disk cache)? I'll asked, 
because a lot of locking is involved in the code.


I did some once but didn't find anything particularly interesting about 
the results.  Since you seem to be on a research tear here, it would be 
helpful to have a script to test that out available, I wasn't able to 
release mine and something dtrace based would probably be better than the 
approach I used (I threw a bunch of gettimeofdata calls into the logs and 
post-processed them with a script).



BTW2: Oracle buffercache and background writer strategy is also interesting.


As a rule, we don't post links to other database implementation details 
here, as those can have patented design details we'd prefer not to 
intentionally re-implement.  Much of Oracle's design here doesn't apply 
here anyway, as it was done in the era when all of their writes were 
synchronous.  That required them to worry about doing a good job on some 
things in their background writer that we shrug off and let os writes 
combined with fsync handle instead.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Errors regarding transporting database using pg_dump

[Chun-fan Ivan Liao]

Thank you, Oosterhout and Lane.

The code is not written by me. The previous DB admin is not reachable.
I was just the present-stage DB (newbie) admin, and I never touched
PostgreSQL before :(

On Mon, Oct 5, 2009 at 10:34 PM, Martijn van Oosterhout
 wrote:
>
> On Mon, Oct 05, 2009 at 06:07:44PM +0800, Chun-fan Ivan Liao wrote:
> > I used pg_dump to dump the old database out and psql to store the database
> > into the new server, but the following two errors occurred:
> >
> > ERROR:  incompatible library "/lib/libc.so.6": missing magic block
> > HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
> > STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
> >             AS '/lib/libc.so.7', 'system'
> >             LANGUAGE c STRICT;
>
> Ok, here someone was cheating by invoking a system library function
> directly. That's no longer supported. If you really want this you need
> to make a small library with PG_MODULE_MAGIC; and a pg_system() function
> that redirects to the real system().
>
> That said, why are you doint this anyway. A better solution may be to
> install a trusted language (like plperlu or plpython) and do the system
> call from there.

Could you tell me the exact command I should use? Replace the error
command in the dumped file?

>
> > ERROR:  value too long for type character varying(12)
> > CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "??(9debacdd)???"
> > STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR", "S_TERM", "COU_CODE",
> > "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME", "YEAR", "CREDIT", "COU_CNAME",
> > "TEA_CODE", "TEA_CNAME", "STU_CNAME", "SCORE", "SCORE_A", "SERNO", "PANOPA",
> > "UNIT", "TEMP") FROM stdin;
>
> Here the column is defined as 12 characters and you're trying to
> inserts a 19 character string, which is bad. How you got that out of
> pg_dump though I don't know.
>

I used pg_dump to dump the database again, and the statement and error
was the same also. No idea what to do.

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

[GENERAL]

[233242661002]

NOTE: This e-mail message is subject to the MTN Ghana email disclaimer see 
http://www.mtn.com.gh/disclaimer/

What are the general features and components of PostgreSQL
-- 
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]

[John R Pierce]

233242661...@mtn.com.gh wrote:


What are the general features and components of PostgreSQL



http://www.postgresql.org/docs/current/static/index.html



--
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] Limit of bgwriter_lru_maxpages of max. 1000?

[Gerhard Wiesinger]

On Mon, 5 Oct 2009, Greg Smith wrote:


On Sun, 4 Oct 2009, Gerhard Wiesinger wrote:


On Fri, 2 Oct 2009, Scott Marlowe wrote:


I found that lowering checkpoint completion target was what helped.
Does that seem counter-intuitive to you?




I set it to 0.0 now.


If you set that to 0.0, the whole checkpoing spreading logic doesn't apply 
like it's supposed to.  I'm not sure what the results you posted mean now. If 
you had it set to 0 and saw a bad spike (which is how I read your message), 
I'd say "yes, that's what happens when you do reduce that parameter, so don't 
do that".  If you meant something else please clarify.


I think the problem is, that it is done on checkpoint time (whether spread 
or not). I should have been already be done by bgwriter.




Thanks for the dtrace example, I suggested we add those checkpoint probes in 
there and someone did, but I hadn't seen anybody use them for anything yet.




I think more probes (e.g. on different writing conditions like writing 
from bgwriter or on a checkpoint) would be interesting here.



Bug1: usage_count is IHMO not consistent


It's a bit hack-ish, but the changes made to support multiple buffer use 
strategies introduced by the "Make large sequential scans and VACUUMs work in 
a limited-size ring" commit are reasonable even if they're not as consistent 
as we'd like.  Those changes were supported by benchmarks proving their 
utility, which always trump theoretical "that shouldn't work better!" claims 
when profiling performance.


Also, they make sense to me, but I've spent a lot of time staring at 
pg_buffercache output to get a feel for what shows up in there under various 
circumstances.  That's where I'd suggest you go if this doesn't seem right to 
you; run some real database tests and use pg_buffercache to see what's inside 
the cache when you're done.  What's in there and what I expected to be in 
there weren't always the same thing, and it's interesting to note how that 
changes as shared_buffers increases.  I consider some time studying that a 
pre-requisite to analyzing performance of this code.




I have analyzed pg_buffercache (query every second, see below) in parallel 
to see what happens but I didn't see expected results in some ways with 
the usage_counts. Therefore I analyzed the code and found IHMO the problem 
with the usage_count and buffer reallocation. Since the code change is 
also new (I think it way 05/2009) it might be that you tested before ...


BTW: Is it possible to get everything in pg_class over all databases as 
admin?



Bug2: Double iteration of buffers
As you can seen in the calling tree below there is double iteration with 
buffers involved. This might be a major performance bottleneck.


Hmmm, this might be a real bug causing scans through the buffer cache to go 
twice as fast as intended.


That's not twice O(2*n)=O(n) that's a factor n*n (outer and inner loop 
iteration) which means overall is O(n^2) which is IHMO too much.


 Since the part you suggest is doubled isn't very 
intensive or called all that often, there's no way it can be a major issue 
though.


It is a major issue since it is O(n^2) and not O(n). E.g. with 2GB share 
buffer we have 262144 blocks and 68719476736 calls which is far too much.


 That's based on knowing what the code does and how much it was 
called, as well as some confidence that if it were really a *major* problem, 
it would have shown up on the extensive benchmarks done on all the code paths 
you're investigating.




The problem might be hidden for the following reasons:
1.) Buffers values are too low that even n^2 is low for today's machines
2.) Code is not often called in that way
3.) backend writes out pages so that the code is never executed
4.) ...

BTW: Are there some tests available how fast a buffer cache hit is and a 
disk cache hit is (not in the buffer cache but in the disk cache)? I'll 
asked, because a lot of locking is involved in the code.


I did some once but didn't find anything particularly interesting about the 
results.  Since you seem to be on a research tear here, it would be helpful 
to have a script to test that out available, I wasn't able to release mine 
and something dtrace based would probably be better than the approach I used 
(I threw a bunch of gettimeofdata calls into the logs and post-processed them 
with a script).




Do you have an where one should set tracepoints inside and outside 
PostgreSQL?


BTW2: Oracle buffercache and background writer strategy is also 
interesting.


As a rule, we don't post links to other database implementation details here, 
as those can have patented design details we'd prefer not to intentionally 
re-implement.  Much of Oracle's design here doesn't apply here anyway, as it 
was done in the era when all of their writes were synchronous.  That required 
them to worry about doing a good job on some things in their background 
writer that we shrug off and let os writes combined with fsync handle 
instea

Re: [GENERAL] How useful is the money datatype?

[Guy Rouillier]

Christophe Pettus wrote:


On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote:

There is no reason why PG could not support packed decimal.


Is that not NUMERIC?


No, that is not NUMERIC.  All numeric types are stored as binary 
representations.  Packed decimal is not.  Perhaps an example would 
clarify.  The number 1234 would be represented as follows:


binary: 10011010010
packed decimal: 12 34

Packed decimal needs to be able to represent 10 distinct characters, 
0-9, so it uses half a byte for each.  So a 4 digit number can be 
represented in 2 bytes (for simplicity, I'm ignoring sign.  That takes a 
half byte.)


The IBM implementation provides a corresponding arithmetic library to 
use packed decimal.  These numbers are never converted to binary, so 
there is no loss in precision.


--
Guy Rouillier

--
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 useful is the money datatype?

[Bruce Momjian]

Guy Rouillier wrote:
> Christophe Pettus wrote:
> > 
> > On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote:
> >> There is no reason why PG could not support packed decimal.
> > 
> > Is that not NUMERIC?
> 
> No, that is not NUMERIC.  All numeric types are stored as binary 
> representations.  Packed decimal is not.  Perhaps an example would 
> clarify.  The number 1234 would be represented as follows:

I think you are wrong.  The Postgres documentation say:

 The type numeric can store numbers with up to 1000
 digits of precision and perform calculations exactly. It is
 especially recommended for storing monetary amounts and other
 quantities where exactness is required. However, arithmetic on
 numeric values is very slow compared to the integer
 types, or to the floating-point types described in the next
 section.

Postgres NUMERIC  is packed decimal, base 1000, as I remember.

-- 
  Bruce Momjian  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 useful is the money datatype?

[Christophe Pettus]

A quick check of the source code (src/backend/utils/adt/numeric.c)  
shows it's base 1, each "digit" represented as an int16.  It's not  
strictly speaking BCD, but there's no computational difference.


--
-- Christophe Pettus
   x...@thebuild.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] Limit of bgwriter_lru_maxpages of max. 1000?

[Greg Smith]

On Mon, 5 Oct 2009, Gerhard Wiesinger wrote:

I think the problem is, that it is done on checkpoint time (whether 
spread or not). I should have been already be done by bgwriter.


This is pretty simple:  if you write things before checkpoint time, you'll 
end up re-writing a percentage of the blocks if they're re-dirtied before 
the checkpoint actually happens.  The checkpoint itself is always the most 
efficient time to write something out.  People think that the background 
writer should do more, but it can't without generating more writes than if 
you instead focused on spreading the checkpoints out instead.  This is why 
the only work the BGW does try to do is writing out blocks that it's 
pretty sure are going to be evicted very soon (in the next 200ms, or 
whatever its cycle time is set to), to minimize the potential for 
mistakes.  The design errors a bit on the side of doing too little because 
it is paranoid about not doing wasted work, and that implementation always 
beat one where the background writer was more aggressive in benchmarks.


This is hard for people to accept, but there were three of us running 
independent tests to improve things here by the end of 8.3 development and 
everybody saw similar results as far as the checkpoint spreading approach 
being the right one.  At the time the patch was labeled "load distributed 
checkpoint" and if I had more time today I'd try and find the more 
interesting parts of that discussion to highlight them.


BTW: Is it possible to get everything in pg_class over all databases as 
admin?


Scott's message at 
http://archives.postgresql.org/pgsql-general/2009-09/msg00986.php 
summarizes the problem nicely, and I suggested my workaround for it at 
http://archives.postgresql.org/pgsql-general/2009-09/msg00984.php



Bug2: Double iteration of buffers
As you can seen in the calling tree below there is double iteration with 
buffers involved. This might be a major performance bottleneck.


Hmmm, this might be a real bug causing scans through the buffer cache to go 
twice as fast as intended.


That's not twice O(2*n)=O(n) that's a factor n*n (outer and inner loop 
iteration) which means overall is O(n^2) which is IHMO too much.


I follow what you mean, didn't notice that.  SyncOneBuffer isn't a O(n) 
operation; it's O(1).  So I'd think that the potential bug here turns into 
a O(n) issue then given it's the routine being called n times.


This seems like a job for "dump things to the log file" style debugging. 
If I can reproduce an actual bug here it sounds like a topic for the 
hackers list outside of this discussion.



The problem might be hidden for the following reasons:
1.) Buffers values are too low that even n^2 is low for today's machines
2.) Code is not often called in that way
3.) backend writes out pages so that the code is never executed


(2) was the reason I figured it might have escaped notice.  It's really 
not called that often in a way that would run into the problem you think 
is there.


Do you have an where one should set tracepoints inside and outside 
PostgreSQL?


I think you'd want to instrument BufferAlloc inside bufmgr.c to measure 
what you're after.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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 useful is the money datatype?

[Sam Mason]

On Mon, Oct 05, 2009 at 01:07:16PM -0700, Christophe Pettus wrote:
> A quick check of the source code (src/backend/utils/adt/numeric.c)  
> shows it's base 1, each "digit" represented as an int16.

I was going to note that in my post but thought it was needless detail;
ah well, maybe next time I will! :)

-- 
  Sam  http://samason.me.uk/

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

[GENERAL] Version upgrade with tablespace

[Bill Todd]

I am new PostgreSQL and am about to attempt to upgrade from 8.3 to  
8.4.1. I believe I understand the procedure except for my tablespace. My 
database is in a tablespace named app. When I restore the database dump 
under 8.4.1 I assume that the tablespace will not be created 
automatically. Am  I correct that after installing 8.4.1 I should create 
a tablespace with the same name but a different directory then restore 
the database dump and the database will be placed in the app tablespace?


Bill

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

[GENERAL] Wishlist of PL/Perl Enhancements for PostgreSQL 8.5

[Tim Bunce]

I'm planning to work with Andrew Dunstan on some enhancements to PL/Perl
for PostgreSQL 8.5.

I've written up some initial proposals here:

  
http://blog.timbunce.org/2009/10/05/wishlist-of-plperl-enhancements-for-postgresql-8-5/

I'd welcome any feedback on those, and any other suggestions you may have.
(Either here, on the blog, or direct to me - I'll post a summary.)

Thanks.

Tim.

-- 
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] Limiting the impact of schema additions/poor queries made by clients on production machines

[Scott Marlowe]

On Mon, Oct 5, 2009 at 9:04 AM, Joshua Berry  wrote:
> Our shop uses postgres for a dozen installations. The applications
> have some realtime performance requirements, and are just good enough
> to function properly. The problem is that the clients (owners of the
> production servers) are using the same server/database for
> customizations that are causing problems with the performance of our
> applications.
>
> Example of clients' customizations:
> * Large tables with text datatypes that get cast in the queries
> * No primary keys, indexes, FK constraints
> * Use of external scripts that use count(*) from table where id = x,
> in a loop from the script, to determine how to construct more queries
> later in the same script.

Can you use londiste or slony to make a replicant for them to run
these things on so they don't affect the main production server?

> The clients consider themselves experts and don't take
> suggestions/criticism well. If we just go ahead and try to port/change
> the scripts ourselves, the old code can come back, clobbering the
> changes that we made!

It's pretty obvious that they are not only NOT experts, but also
unprofessional as well.  If they won't cooperate, then I'd suggest
making it clear you're not going to make fixing their mistakes a
priority, and then proceed to give them enough rope to hang themselves
with.

> My question is this: how can we limit the resources to
> queries/applications other that what we create and deploy? Are there
> any pragmatic options in scenarios like this? We prided ourselves in
> having an OSS solution, but it seems that it's become a liability.

Even a Big Commercial DBMS can only hold back the clown patrol for so
long.  If they're good enough at being bad users they can cause
problems.  Besides, these guys sound like they'd demand admin access
to any db you gave them.  You customer is your primary liability, not
your toolset.  you make it more idiot proof, they become better
idiots.  It's an arms race against stupidity, and you can't really win
one of those.

I'd take away their access if you can.  They're idiots.

-- 
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] Limiting the impact of schema additions/poor queries made by clients on production machines

[Steve Atkins]

On Oct 5, 2009, at 8:04 AM, Joshua Berry wrote:


Our shop uses postgres for a dozen installations. The applications
have some realtime performance requirements, and are just good enough
to function properly. The problem is that the clients (owners of the
production servers) are using the same server/database for
customizations that are causing problems with the performance of our
applications.

Example of clients' customizations:
* Large tables with text datatypes that get cast in the queries
* No primary keys, indexes, FK constraints
* Use of external scripts that use count(*) from table where id = x,
in a loop from the script, to determine how to construct more queries
later in the same script.


This sounds very familiar.

Is this just for reporting, or does it interact more closely with
the application?

If it's just reporting, then maybe a slony slave server, just for  
reporting,

would help.

If it interacts more closely with the application then you're not
going to be able protect them from themselves programatically.



The clients consider themselves experts and don't take
suggestions/criticism well. If we just go ahead and try to port/change
the scripts ourselves, the old code can come back, clobbering the
changes that we made!



If personal feedback isn't going to work (and it sounds like it
isn't) then perhaps hacking up pgfouine a little to create a
dashboard showing where the database resource is going.
That way you have a neutral place to point at when they
complain the app is running slowly, and that "neutral" report
can communicate "you, the customer, are the problem because
your queries suck" in a more objective way.



My question is this: how can we limit the resources to
queries/applications other that what we create and deploy? Are there
any pragmatic options in scenarios like this? We prided ourselves in
having an OSS solution, but it seems that it's become a liability.


If the queries are operating on the same data as the production
app I don't see any good way to segment the IO and CPU resources
that's going to be idiot proof, especially of your customers are
going to demand full database access.



We use PG 8.3 running on a range on Linux Distos.


Cheers,
  Steve


--
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] Programming interfaces when using MD5 authentication

[Preston de Guise]

On 02/10/2009, at 23:47 , Tom Lane wrote:


Preston de Guise  writes:

Using Perl to program interfaces to PostgreSQL, and had previously
misunderstood how md5 password authentication worked, so I'm now re-
doing it, but struggling to find out how DBD::Pg might be used to
actually authenticate by passing an md5 of the password instead of  
the

real thing.


You should always pass the cleartext password.  Any md5-ification will
be done by the low-level driver.


Thanks for the clarification Tom.

Cheers,

Preston.

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

[GENERAL] Documentation on standard conforming strings?

[Preston de Guise]

Hi,

I don't yet properly understand the conforming strings implementation  
that came in 8.3 The manual (to me at least) has been a little  
imprecise in describing the implications for correctly inserting data  
with this enabled without getting back a plethora of warnings, or  
having the inserts fail completely.


Is anyone aware of a good "how to" document on conforming strings  
covering such topics as:


- Valid quoting methods and when the various ones should be used
- Criteria for warnings (in some instances it seems you can get  
warnings about them no matter how 'safely' you insert? I.e., I know  
I'm likely to still be doing things wrong...)

(- Any coverage of DBD::Pg's capabilities would be a great bonus)

I realise this is a little vague, but unfortunately, so too is my  
understanding based on what I've read in the documentation thus far...


Cheers,

Preston.

--
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] Version upgrade with tablespace

[Bill Todd]

Ignore that. I now see that I must edit the dump file to change the 
directory for the tablespace before running the restore.


Bill

Bill Todd wrote:
I am new PostgreSQL and am about to attempt to upgrade from 8.3 to  
8.4.1. I believe I understand the procedure except for my tablespace. 
My database is in a tablespace named app. When I restore the database 
dump under 8.4.1 I assume that the tablespace will not be created 
automatically. Am  I correct that after installing 8.4.1 I should 
create a tablespace with the same name but a different directory then 
restore the database dump and the database will be placed in the app 
tablespace?


Bill




--
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] 8.3.6 build error on Debian Lenny

[donald63]

Tom Lane-2 wrote:
> 
> Josh Trutwin  writes:
>> Hi - I'm trying to build 8.3.6 on a box recently upgraded from Sarge
>> to Lenny and I get the following error during compile:
> 
>> make[4]: Entering directory
>> `/backup/source/db/postgresql-8.3.6/src/backend/utils/adt' gcc -O2
>> -Wall -Wmissing-prototypes -Wpointer-arith -Winline
>> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
>> -fwrapv -I../../../../src/include -D_GNU_SOURCE  -I/usr/local/lib  -c
>> -o geo_ops.o geo_ops.c /usr/lib/gcc/i486-linux-gnu/4.3.2/cc1: symbol
>> lookup error: /usr/lib/libmpfr.so.1: undefined symbol:
>> __gmp_get_memory_functions
> 
> [ blink... ]  There's no reason for a compile to be looking into shared
> libraries.  What I think must have happened here is that this reflects a
> bogus function call internally in gcc itself.  Which probably means that
> you have a gcc version that's out-of-sync with your libmpfr.so.1 version
> (whatever the heck that is).  I'd suggest asking around among some
> Debian experts, or just reinstalling instead of upgrading.
> 
>   regards, tom lane
> 

I had exactly same error (missing symbol __gmp_get_memory_functions) 
while compiling libsndfile-1.0.20 
This old message helped a lot. libmpfr was compiled with gcc-4.3.3
and I had updated to gcc-4.4.1

I recompiled mpfr and the error was solved.
(I am still using PostgreSQL-8.3.4 on Scientific
Linux 5.x which I compiled with gcc-4.1.2)
Thank you for this nice mailing list :-)

MPFR is a portable library written in C for arbitrary precision
arithmetic on floating-point numbers. It is based on the GNU MP library.
It aims to extend the class of floating-point numbers provided by the
GNU MP library by a precise semantics. The main differences with the
`mpf' class from GNU MP are:

   * the `mpfr' code is portable, i.e. the result of any operation does
 not depend (or should not) on the machine word size
 `mp_bits_per_limb' (32 or 64 on most machines);

   * the precision in bits can be set exactly to any valid value for
 each variable (including very small precision);

   * `mpfr' provides the four rounding modes from the IEEE 754-1985
 standard.

   In particular, with a precision of 53 bits, `mpfr' should be able to
exactly reproduce all computations with double-precision machine
floating-point numbers (`double' type in C), except the default
exponent range is much wider and subnormal numbers are not implemented
but can be emulated.

-- 
View this message in context: 
http://www.nabble.com/8.3.6-build-error-on-Debian-Lenny-tp22427839p25757187.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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 useful is the money datatype?

[Guy Rouillier]

Bruce Momjian wrote:

Guy Rouillier wrote:

Christophe Pettus wrote:

On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote:

There is no reason why PG could not support packed decimal.

Is that not NUMERIC?
No, that is not NUMERIC.  All numeric types are stored as binary 
representations.  Packed decimal is not.  Perhaps an example would 
clarify.  The number 1234 would be represented as follows:


I think you are wrong.  The Postgres documentation say:


You are correct, I am wrong, as private emails also pointed out.  I 
should read more carefully.  This list is rapidly self-correcting ;). 
Thanks.


The IBM implementation provided language libraries (usually COBOL) that 
also supported packed decimal, so precision was maintained throughout 
the entire application stack.


--
Guy Rouillier

--
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 useful is the money datatype?

[John R Pierce]

Guy Rouillier wrote:
The IBM implementation provided language libraries (usually COBOL) 
that also supported packed decimal, so precision was maintained 
throughout the entire application stack.




IBM 360/370/390/etcetc/Zsystem has BCD op codes in the instruction set 
architecture.   microcoded of course, but they generally ran at the 
memory bandwidth.


but, postgres's base 1 'numeric' datatype is perfectly useful for 
this.  high precision numeric divides are probably fugly, but for normal 
business math, they perform quite nicely.





--
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: column "id" inherits conflicting default values

[Tom Lane]

I wrote:
> I guess we're going to have to rewrite that code to not store the cooked
> defaults in string form.  If they were node trees then equal() would do
> the right thing.

The attached patch should fix this.

regards, tom lane

Index: src/backend/commands/tablecmds.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.288.2.1
diff -c -r1.288.2.1 tablecmds.c
*** src/backend/commands/tablecmds.c	7 Aug 2009 15:28:07 -	1.288.2.1
--- src/backend/commands/tablecmds.c	6 Oct 2009 00:25:31 -
***
*** 486,492 
  			cooked->contype = CONSTR_DEFAULT;
  			cooked->name = NULL;
  			cooked->attnum = attnum;
! 			cooked->expr = stringToNode(colDef->cooked_default);
  			cooked->is_local = true;	/* not used for defaults */
  			cooked->inhcount = 0;		/* ditto */
  			cookedDefaults = lappend(cookedDefaults, cooked);
--- 486,492 
  			cooked->contype = CONSTR_DEFAULT;
  			cooked->name = NULL;
  			cooked->attnum = attnum;
! 			cooked->expr = colDef->cooked_default;
  			cooked->is_local = true;	/* not used for defaults */
  			cooked->inhcount = 0;		/* ditto */
  			cookedDefaults = lappend(cookedDefaults, cooked);
***
*** 1136,1143 
  	List	   *constraints = NIL;
  	int			parentsWithOids = 0;
  	bool		have_bogus_defaults = false;
- 	char	   *bogus_marker = "Bogus!";		/* marks conflicting defaults */
  	int			child_attno;
  
  	/*
  	 * Check for and reject tables with too many columns. We perform this
--- 1136,1143 
  	List	   *constraints = NIL;
  	int			parentsWithOids = 0;
  	bool		have_bogus_defaults = false;
  	int			child_attno;
+ 	static Node	bogus_marker = { 0 };		/* marks conflicting defaults */
  
  	/*
  	 * Check for and reject tables with too many columns. We perform this
***
*** 1321,1327 
  			 */
  			if (attribute->atthasdef)
  			{
! char	   *this_default = NULL;
  AttrDefault *attrdef;
  int			i;
  
--- 1321,1327 
  			 */
  			if (attribute->atthasdef)
  			{
! Node	   *this_default = NULL;
  AttrDefault *attrdef;
  int			i;
  
***
*** 1332,1338 
  {
  	if (attrdef[i].adnum == parent_attno)
  	{
! 		this_default = attrdef[i].adbin;
  		break;
  	}
  }
--- 1332,1338 
  {
  	if (attrdef[i].adnum == parent_attno)
  	{
! 		this_default = stringToNode(attrdef[i].adbin);
  		break;
  	}
  }
***
*** 1350,1359 
   */
  Assert(def->raw_default == NULL);
  if (def->cooked_default == NULL)
! 	def->cooked_default = pstrdup(this_default);
! else if (strcmp(def->cooked_default, this_default) != 0)
  {
! 	def->cooked_default = bogus_marker;
  	have_bogus_defaults = true;
  }
  			}
--- 1350,1359 
   */
  Assert(def->raw_default == NULL);
  if (def->cooked_default == NULL)
! 	def->cooked_default = this_default;
! else if (!equal(def->cooked_default, this_default))
  {
! 	def->cooked_default = &bogus_marker;
  	have_bogus_defaults = true;
  }
  			}
***
*** 1492,1498 
  		{
  			ColumnDef  *def = lfirst(entry);
  
! 			if (def->cooked_default == bogus_marker)
  ereport(ERROR,
  		(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
    errmsg("column \"%s\" inherits conflicting default values",
--- 1492,1498 
  		{
  			ColumnDef  *def = lfirst(entry);
  
! 			if (def->cooked_default == &bogus_marker)
  ereport(ERROR,
  		(errcode(ERRCODE_INVALID_COLUMN_DEFINITION),
    errmsg("column \"%s\" inherits conflicting default values",
Index: src/backend/nodes/copyfuncs.c
===
RCS file: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.432
diff -c -r1.432 copyfuncs.c
*** src/backend/nodes/copyfuncs.c	18 Jun 2009 01:27:02 -	1.432
--- src/backend/nodes/copyfuncs.c	6 Oct 2009 00:25:33 -
***
*** 2073,2079 
  	COPY_SCALAR_FIELD(is_local);
  	COPY_SCALAR_FIELD(is_not_null);
  	COPY_NODE_FIELD(raw_default);
! 	COPY_STRING_FIELD(cooked_default);
  	COPY_NODE_FIELD(constraints);
  
  	return newnode;
--- 2073,2079 
  	COPY_SCALAR_FIELD(is_local);
  	COPY_SCALAR_FIELD(is_not_null);
  	COPY_NODE_FIELD(raw_default);
! 	COPY_NODE_FIELD(cooked_default);
  	COPY_NODE_FIELD(constraints);
  
  	return newnode;
Index: src/backend/nodes/equalfuncs.c
===
RCS file: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.355
diff -c -r1.355 equalfuncs.c
*** src/backend/nodes/equalfuncs.c	18 Jun 2009 01:27:02 -	1.355
--- src/backend/nodes/equalfuncs.c	6 Oct 2009 00:25:33 -
***
*** 2052,2058 
  	COMPARE_SCALAR_FIELD(is_local);
  	COMPARE_SCALAR_FIELD(is_not_null);
  	COMPARE_NODE_FIELD(raw_default);
! 	COMPARE_STRING_FIELD(co

Re: [GENERAL] Errors regarding transporting database using pg_dump

[Adrian Klaver]

On Monday 05 October 2009 10:36:04 am Chun-fan Ivan Liao wrote:
> Thank you, Oosterhout and Lane.
>
> The code is not written by me. The previous DB admin is not reachable.
> I was just the present-stage DB (newbie) admin, and I never touched
> PostgreSQL before :(
>
> On Mon, Oct 5, 2009 at 10:34 PM, Martijn van Oosterhout
>
>  wrote:
> > On Mon, Oct 05, 2009 at 06:07:44PM +0800, Chun-fan Ivan Liao wrote:
> > > I used pg_dump to dump the old database out and psql to store the
> > > database into the new server, but the following two errors occurred:
> > >
> > > ERROR:  incompatible library "/lib/libc.so.6": missing magic block
> > > HINT:  Extension libraries are required to use the PG_MODULE_MAGIC
> > > macro. STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
> > >             AS '/lib/libc.so.7', 'system'
> > >             LANGUAGE c STRICT;
> >
> > Ok, here someone was cheating by invoking a system library function
> > directly. That's no longer supported. If you really want this you need
> > to make a small library with PG_MODULE_MAGIC; and a pg_system() function
> > that redirects to the real system().
> >
> > That said, why are you doint this anyway. A better solution may be to
> > install a trusted language (like plperlu or plpython) and do the system
> > call from there.
>
> Could you tell me the exact command I should use? Replace the error
> command in the dumped file?
>
> > > ERROR:  value too long for type character varying(12)
> > > CONTEXT:  COPY stulist, line 46803, column STU_CNAME:
> > > "??(9debacdd)???" STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR",
> > > "S_TERM", "COU_CODE", "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME",
> > > "YEAR", "CREDIT", "COU_CNAME", "TEA_CODE", "TEA_CNAME", "STU_CNAME",
> > > "SCORE", "SCORE_A", "SERNO", "PANOPA", "UNIT", "TEMP") FROM stdin;
> >
> > Here the column is defined as 12 characters and you're trying to
> > inserts a 19 character string, which is bad. How you got that out of
> > pg_dump though I don't know.
>
> I used pg_dump to dump the database again, and the statement and error
> was the same also. No idea what to do.

First are you using the 8.1 or 8.3 version of pg_dump? It is generally 
recommended to use the new version to dump the old version database. One 
potential solution is to alter the varchar length in the old database before 
you dump so the field will copy over.

-- 
Adrian Klaver
akla...@comcast.net

-- 
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] Errors regarding transporting database using pg_dump

[Greg Stark]

On Mon, Oct 5, 2009 at 7:34 AM, Martijn van Oosterhout
 wrote:
> That said, why are you doint this anyway. A better solution may be to
> install a trusted language (like plperlu or plpython) and do the system
> call from there.
>

If you just want system(3) you might as well use plsh...

-- 
greg

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

[GENERAL] Fatal error on start

[Andrew Bartley]

does anyone know what this is?

Trying to start postgres version:
PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2

2009-10-06 15:17:21 EST 3757  FATAL:  XX000: could not open directory
"pg_tblspc": Unknown error 530
2009-10-06 15:17:21 EST 3757  LOCATION:  ReadDir, fd.c:1460

[GENERAL] Triggers and SQL

[Yadisnel Galvez Velazquez]

How I cant optain de SQL (if is posible) of any STATEMENT in an AFTER Trigger 
Function in C?

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

[GENERAL] numeric field overflow

[Nurzhan Kirbassov]

Good day.

I may be misunderstanding the NUMERIC type description in the manual,
so can anyone please help me with this? Description says:

"The scale of a numeric is the count of decimal digits in the
fractional part, to the right of the decimal point. The precision of a
numeric is the total count of significant digits in the whole number,
that is, the number of digits to both sides of the decimal point. ...
Integers can be considered to have a scale of zero. "

However, I am not able to insert numbers that have number of digits
equal to the precision and the scale equal to 0.

F.E.:

CREATE TABLE test.test
(
  rate numeric(5,1)
)

INSERT INTO test.test VALUES (1)

Generates an error:

ERROR: numeric field overflow
SQL state: 22003
Detail: A field with precision 5, scale 1 must round to an absolute
value less than 10^4.



So, does the precision part of the numeric type really means number of
digits to the left of the decimal point, or what ?

Thanks.

--
Regards,
Nurzhan Kirbassov.

-- 
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] numeric field overflow

[Christophe Pettus]

On Oct 5, 2009, at 11:20 PM, Nurzhan Kirbassov wrote:

So, does the precision part of the numeric type really means number of
digits to the left of the decimal point, or what ?



NUMERIC is behaving as documented.  The way to think of it is when you  
are inserting:


INSERT INTO test.test VALUES (1)

into a NUMERIC(5,1), what you are doing is inserting:

INSERT INTO test.test VALUES (1.0)

1.0 has six significant digits, rather than five, so the insert  
fails.

--
-- Christophe Pettus
   x...@thebuild.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] numeric field overflow

[Scott Marlowe]

On Tue, Oct 6, 2009 at 12:20 AM, Nurzhan Kirbassov  wrote:
> Good day.
>
> I may be misunderstanding the NUMERIC type description in the manual,
> so can anyone please help me with this? Description says:
>
> "The scale of a numeric is the count of decimal digits in the
> fractional part, to the right of the decimal point. The precision of a
> numeric is the total count of significant digits in the whole number,
> that is, the number of digits to both sides of the decimal point. ...
> Integers can be considered to have a scale of zero. "
>
> However, I am not able to insert numbers that have number of digits
> equal to the precision and the scale equal to 0.
>
> F.E.:
>
> CREATE TABLE test.test
> (
>  rate numeric(5,1)
> )

This declares a numeric of 5 digits, with 1 to the right of the decimal point.

>
> INSERT INTO test.test VALUES (1)
>
> Generates an error:

Like it should.  however this:

CREATE TABLE test.test ( rate numeric(5,0));
INSERT INTO test.test VALUES (1);
INSERT 0 1

Works just fine.

-- 
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] attempted to lock invisible tuple - PG 8.4.1

[Stuart Bishop]

On Mon, Oct 5, 2009 at 11:00 PM, Alvaro Herrera  
wrote:

Stuart Bishop wrote:

On Mon, Oct 5, 2009 at 4:22 PM, Alban Hertroys
 wrote:



> A similar issue was discussed just recently here:
> http://archives.postgresql.org/pgsql-general/2009-09/msg01219.php
>
> That issue involved cursors though (and a serializable isolation level, but
> you have that). Do you have any triggers that use cursors on the table that
> the update fails for?

There is a trigger on that table, and it is certainly the culprit as
can be seen here (different table, same trigger):


I don't think the committed patch touches anything involved in what
you're testing, but if you could grab CVS tip from the 8.4 branch (or
the snapshot from ftp.postgresql.org:/pub/snapshot/stable/8.4 ) and give
it a try, that'd be great.


I trigger the same error with a freshly built snapshot.


--
Stuart Bishop 
http://www.stuartbishop.net/



signature.asc
Description: OpenPGP digital signature