Re: [GENERAL] Data visibility

[Rafal Pietrak]

On Sun, 2006-10-15 at 18:16 -0400, Tom Lane wrote:
> Rafal Pietrak <[EMAIL PROTECTED]> writes:
> > Hmm. I tried that, But I'm stuck with finding a way to propagate the
> > 'intermediate data' between BEFORE/AFTER triggers, *outside* of a TABLE
> > structure. That data is easily accesable inside the BEFORE TRIGGER as
> > simple variable.
> 
> Um ... what data do you think is visible to the BEFORE trigger but not
> the AFTER trigger?  They both see both states of the row, if that's what
> you're wondering.

No, no. I don't 'think' the data is lost between BEFORE and AFTER. I
*make* them disapear as I cannot find any place to pass them over.
The MASTSTER table does not contain columns for them ... and will not.
But in the BEFORE trigger function, I have them as local variables.

Are there ways to have my private 'virtual placeholder'? - like *NEW*
and *OLD* are? Placeholders which are outside of any table, and
available just inside the INSERT transaction, but throuout *all* it's
triggers.

Taking reference from other languages, I might think of some sort of
static variables:
CREATE FUNCTION piko() $$ DECLARE priv RECORD STATIC; BEGIN 
END;
... or
CREATE FUNCTION piko(priv RECORD STATIC) 

so that another function used for another trigger on that same table
will have access to the same record, which would retain data throught
the transactoin?

Just ideas. But may be there are currently tools to have such
functionality?

But if nothing like this is available, I think I'll need an additional
table, identical to AUX - sort of AUX shadow, but without foreign key
constraint to MASTER, so that I can put my data there in BEFORE, then
copy those into the AUX in AFTER, and purge the aux-shadow on exit from
AFTER.

In any case, discussion helps. Thenx.

-- 
-R

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

Re: [GENERAL] BEGIN WORK READ ONLY;

[David Fetter]

On Sun, Oct 15, 2006 at 11:39:20AM +0200, Peter Eisentraut wrote:
> David Fetter wrote:
> > It would be handy for things like pgpool and Continuent, which
> > could reliably distinguish up front the difference between a
> > transaction that can write and one that can safely be sliced up
> > and dispatched to read-only databases.
> 
> Yes, I think that would be the use case.  I wish someone were to
> implement that.

I think you meant "would" rather than "were to." ;)

I've brought it up with the pgpool people :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

[GENERAL] techdocs.2 how long has this be around?

[Richard Broersma Jr]

Is this new? 
Who ever spent the time to do this, thanks for the effort.  Having the content 
organized this way
makes it easy to find specific reading material.

Regards,

Richard Broersma Jr. 

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

Re: [GENERAL] postgres' web site malfunctional ?

[Gregory S. Williamson]

Thanks muchly!  Content is back now, formatting is whack, but I can use it 
again.

G

-Original Message-
From:   Joshua D. Drake [mailto:[EMAIL PROTECTED]
Sent:   Sun 10/15/2006 8:27 PM
To: Bill Hawes
Cc: Gregory S. Williamson; pgsql-general@postgresql.org; PostgreSQL WWW
Subject:Re: [GENERAL] postgres' web site malfunctional ?

Bill Hawes wrote:
>> Gregory S. Williamson wrote:
>> Then I go to  I get a blank page 
>> ?!? No bytes ... has lost my marbles, my browser (FireFox 
>> 1.5.0.7) lost its electrons, or ??
>>
> 
> 
> http://www.postgresql.org/download/ works, as does /support, /developer,
> etc. but nothing is formatted properly.
> 

The web team has been notified. It is being worked on.

Joshua D. Drake



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


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4532fa1183449119242804&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4532fa1183449119242804!
---






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

Re: [GENERAL] postgres' web site malfunctional ?

[Joshua D. Drake]

Bill Hawes wrote:
>> Gregory S. Williamson wrote:
>> Then I go to  I get a blank page 
>> ?!? No bytes ... has lost my marbles, my browser (FireFox 
>> 1.5.0.7) lost its electrons, or ??
>>
> 
> 
> http://www.postgresql.org/download/ works, as does /support, /developer,
> etc. but nothing is formatted properly.
> 

The web team has been notified. It is being worked on.

Joshua D. Drake



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


-- 

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


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

   http://archives.postgresql.org/

Re: [GENERAL] postgres' web site malfunctional ?

[Bill Hawes]

> Gregory S. Williamson wrote:
> Then I go to  I get a blank page 
> ?!? No bytes ... has lost my marbles, my browser (FireFox 
> 1.5.0.7) lost its electrons, or ??
> 


http://www.postgresql.org/download/ works, as does /support, /developer,
etc. but nothing is formatted properly.

Bill Hawes


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

Re: [GENERAL] postgres' web site malfunctional ?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/15/06 21:35, Chris wrote:
> Gregory S. Williamson wrote:
>> Nope, neither Firefox nor IE 6.0 get anything from this site.
> 
> Most bizarre.. it's broken for me now.
> 
> I can ping the server but the webserver seems to be "broken" (I can
> telnet to port 80 but can't get a page back).

$ wget www.postgresql.org
- --21:45:10--  http://www.postgresql.org/
   => `index.html'
Resolving www.postgresql.org... 80.179.151.210, 212.247.200.180,
66.98.251.159
Connecting to www.postgresql.org|80.179.151.210|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 0 [text/html]

[ <=>  ] 0
- --.--K/s

21:45:10 (0.00 B/s) - `index.html' saved [0/0]

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFMvJaS9HxQb37XmcRAmOHAJ97STau4A78OWSu1XRZzkkUJG/yhACfcAeH
itBEw5ImWSXIfDrmy7OhNoo=
=yvWw
-END PGP SIGNATURE-

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

Re: [GENERAL] postgres' web site malfunctional ?

[Chris]

Gregory S. Williamson wrote:

Nope, neither Firefox nor IE 6.0 get anything from this site.


Most bizarre.. it's broken for me now.

I can ping the server but the webserver seems to be "broken" (I can 
telnet to port 80 but can't get a page back).


--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] postgres' web site malfunctional ?

[Gregory S. Williamson]

Nope, neither Firefox nor IE 6.0 get anything from this site.

:-(

G


-Original Message-
From:   Chris [mailto:[EMAIL PROTECTED]
Sent:   Sun 10/15/2006 6:53 PM
To: Gregory S. Williamson
Cc: pgsql-general@postgresql.org
Subject:Re: [GENERAL] postgres' web site malfunctional ?

Gregory S. Williamson wrote:
> Then I go to  I get a blank page ?!? No bytes ... 
> has lost my marbles, my browser (FireFox 1.5.0.7) lost its electrons, or ??
> 
> Thanks,

I get stuff now.. maybe you got it in the middle of a reboot or something?

-- 
Postgresql & php tutorials
http://www.designmagick.com/


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4532e47875401470421014&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4532e47875401470421014!
---






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

   http://archives.postgresql.org/

Re: [GENERAL] postgres' web site malfunctional ?

[Chris]

Gregory S. Williamson wrote:

Then I go to  I get a blank page ?!? No bytes ... 
has lost my marbles, my browser (FireFox 1.5.0.7) lost its electrons, or ??

Thanks,


I get stuff now.. maybe you got it in the middle of a reboot or something?

--
Postgresql & php tutorials
http://www.designmagick.com/

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

  http://archives.postgresql.org/

[GENERAL] postgres' web site malfunctional ?

[Gregory S. Williamson]

Then I go to  I get a blank page ?!? No bytes ... 
has lost my marbles, my browser (FireFox 1.5.0.7) lost its electrons, or ??

Thanks,

Greg Williamson
DBA
GlobeXplorer LLC


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

Re: [GENERAL] Versioning/updating schema

[Jan Cruz]

On 10/14/06, Jan Cruz <[EMAIL PROTECTED]> wrote:
Thank you...I supposed I'll try this one if it could suits my needs.It's really hard to maintain views and functions updates.I have downloaded and read the instruction for pgdiff but I am not familiar with aol_server and it's kinda troublesome for a tool.
I also did check apg_diff and so far it doesn't support diff for functions and the parser is still buggy.Oh well I supposed I really need one badly or maybe I should create one :D

Re: [GENERAL] Same-page UPDATEs in bloated tables

[Ian Dowse]

In message <[EMAIL PROTECTED]>, Tom Lane writes:
>Alvaro Herrera <[EMAIL PROTECTED]> writes:
>> Using the same page for an updated tuple is generally a useful
>> optimization, so I don't think you have much hopes for having it
>> disabled.
>
>Especially not since there's no very reasonable way for anything as
>low-level as heap_update to know that "the table is way beyond its
>nominal size".  What's nominal size anyway?

Thanks - yes, I don't know enough about PostgreSQL internals to
suggest a good approach, but thought there might be a small change
possible that would cause the table size to eventually recover
itself without manual intervention. For example even if the same-page
optimisation was only disabled on the very last page and for 1 in
10 updates then the table size would start to shrink. Even better
would be a way for this to happen more aggressively when the table
is very sparsely populated relative to the target fill factor.

Just to explain a bit more about the original access pattern, we
were only performing UPDATEs (no INSERT/DELETEs), so the reason
that the table gets bloated to begin with is that vacuuming is
ineffective during long transactions such as backups. In our
particular case, full vacuuming is not an acceptable option due to
the exclusive locking, so to handle the rare table bloat problems
we currently have to just switch all our UPDATEs to use slower
INSERT/DELETE operations instead.

>Actually, the recent thinking in this area has been to try to *increase*
>the usage of same-page UPDATE, so as to prevent table bloat in the first
>place ...

If you mean some kind of in-place update, then that would solve the
problem we were seeing too if it can prevent table bloat of high
UPDATE churn tables during long trasactions such as backups.

Thanks,

Ian

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

   http://archives.postgresql.org/

Re: [GENERAL] SPAM: Re: [SLE] SPAM: AMD Dual core "clock speed"

[Susemail]

On Sunday 15 October 2006 10:57, Susemail wrote:
> On Saturday 14 October 2006 07:19, Anders Johansson wrote:
> > On Thu, 2006-10-12 at 18:28 -1000, Susemail wrote:
> > > With respect to the AMD Athlon(tm) 64 X2 Dual Core Processor 4800+ are
> > > they taking two cpu's  (cores) running at 1 Ghz - summing their clock
> > > speeds and calling it a 2.4 Ghz processor?
> >
> > No, your processors are running in "dynamic" mode, which means they
> > adapt their frequency to how you use the system. If you run something
> > CPU intensive, they will (or should) go to max speed.
> >
> > If you want them running at max speed always, set this in YaST's
> > powersave module (change from Dynamic to Performance I think it's
> > called)
>
> It is called Performance.  I had set it to that some months ago when I
> bought the machine.  I'm going to load the linux_frequency_driver-1.60.01
> now from AMD to see if that helps.
>
> Jerome
>
> > On the command line you can use "powersave -f", to make a temporary
> > change to max CPU power

This worked: Clock: 2411 MHz.  My current kernel (linux-2.6.13-15.12) comes 
with cpu-freq documentation so I assume the driver is already loaded.  Now on 
to the Nvidia driver.

Thanks,
Jerome

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

Re: [GENERAL] Data visibility

[Tom Lane]

Rafal Pietrak <[EMAIL PROTECTED]> writes:
> Hmm. I tried that, But I'm stuck with finding a way to propagate the
> 'intermediate data' between BEFORE/AFTER triggers, *outside* of a TABLE
> structure. That data is easily accesable inside the BEFORE TRIGGER as
> simple variable.

Um ... what data do you think is visible to the BEFORE trigger but not
the AFTER trigger?  They both see both states of the row, if that's what
you're wondering.

regards, tom lane

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

Re: [GENERAL] Data visibility

[Rafal Pietrak]

On Sun, 2006-10-15 at 15:15 -0400, Tom Lane wrote:
> Well, of course not: it's a BEFORE trigger, so the row insertion hasn't
> actually happened yet.  I think you need to split this operation into a
> BEFORE trigger that changes the ID, and an AFTER trigger that propagates
> the data into the other table.

Hmm. I tried that, But I'm stuck with finding a way to propagate the
'intermediate data' between BEFORE/AFTER triggers, *outside* of a TABLE
structure. That data is easily accesable inside the BEFORE TRIGGER as
simple variable.

But, would it work if I drop foreign key constraint on AUX table in
BEFORE INSERT trigger function, and resotre it back in AFTER INSERT? ...
simply rising an error (thus aborting INSERT) if the later fails? Are
there scenarios, leading to foreign key consistancy loss if I do that?

Ideas?

-- 
-R

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

   http://archives.postgresql.org/

Re: [GENERAL] Same-page UPDATEs in bloated tables

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Using the same page for an updated tuple is generally a useful
> optimization, so I don't think you have much hopes for having it
> disabled.

Especially not since there's no very reasonable way for anything as
low-level as heap_update to know that "the table is way beyond its
nominal size".  What's nominal size anyway?

Actually, the recent thinking in this area has been to try to *increase*
the usage of same-page UPDATE, so as to prevent table bloat in the first
place ...

regards, tom lane

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

Re: [GENERAL] Data visibility

[Tom Lane]

Rafal Pietrak <[EMAIL PROTECTED]> writes:
> CREATE TABLE master (id int not null unique, info text, );
> CREATE TABLE aux (master int references master(id), info text, ...);
> CREATE FUNCTION adjust() RETURNS "trigger" AS $$ BEGIN 
>   new.id := 1000-old.id; 
>   INSERT INTO aux (master, info) VALUES (new.id, 'hello world');
>   RETURN new;
> END $$ LANGUAGE plpgsql;
> CREATE TRIGGER pico BEFORE INSERT ON master FOR EACH ROW EXECUTE
> PROCEDURE adjust();

> But in my trigger function "adjust()", executed within a transaction
> opened by "INSERT INTO master", the 'fresh' data (set by "new.id :=
> value") isn't visible to other commands (like: INSERT INTO aux...).

Well, of course not: it's a BEFORE trigger, so the row insertion hasn't
actually happened yet.  I think you need to split this operation into a
BEFORE trigger that changes the ID, and an AFTER trigger that propagates
the data into the other table.

regards, tom lane

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

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

Re: [GENERAL] Same-page UPDATEs in bloated tables

[Alvaro Herrera]

Ian Dowse wrote:

> I've been seeing an issue with 8.1.4 that seems to be caused by the
> way UPDATE operations prefer to place the new row version in the
> same page as the original row. The issue is specific to UPDATEs;
> it does not occur when each UPDATE is replaced by a DELETE/INSERT
> pair. The problem can prevent a temporarily bloated table from ever
> returning to its normal size even though all rows are frequently
> changing and regular vacuuming is taking place.
> 
> A simple way to demonstrate the issue is to insert 10001 rows into
> an empty table and delete the first 1 rows. Now, repeatedly
> performing (lazy) vacuums and UPDATEs will never result in the table
> size shrinking:

Yeah.  This scenario is one of those for which "popular knowledge"
("common wisdom"? "Postgres folklore"?) tells you to do a VACUUM FULL or
equivalent (e.g. CLUSTER).

Using the same page for an updated tuple is generally a useful
optimization, so I don't think you have much hopes for having it
disabled.  The INSERT+DELETE equivalent doesn't have the opportunity to
use that optimization though, which is why it has to go to the FSM and
thus get a different page to do the INSERT on.

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

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

Re: [GENERAL] Data visibility

[Rafal Pietrak]

On Sun, 2006-10-15 at 20:01 +0200, Rafal Pietrak wrote:
>   new.id := 1000-old.id; 

Sory, correction. 

Of cource, this ID update looks more like the following (OLD.* isn't
valid at this point):

new.id := 1000 - new.id; 

-- 
-R

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

[GENERAL] Data visibility

[Rafal Pietrak]

Hi,

I'm trying to write a trigger function, that would update an
'associated' TEBLE on INSERT to master table:

CREATE TABLE master (id int not null unique, info text, );
CREATE TABLE aux (master int references master(id), info text, ...);
CREATE FUNCTION adjust() RETURNS "trigger" AS $$ BEGIN 
new.id := 1000-old.id; 
INSERT INTO aux (master, info) VALUES (new.id, 'hello world');
RETURN new;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER pico BEFORE INSERT ON master FOR EACH ROW EXECUTE
PROCEDURE adjust();

Note, that the trigger function ADJUST() *computs* the ID value for
MASTER. The value computed complies to all MASTER table constraints, so
it should be usable as foreing key for the insert statement that
follows, but it isn't - I get foreign key reference violation fault at
that point.

Now, normaly (e.g. in SQL sequence as typed into the psql command line
utility), when I:
BEGIN;
INSERT INTO master (id,...)...;
SELECT * from master;
ROLLBACK;
SELECT * from master;

... the first SELECT above shows the newly inserted values, despite the
fact, that the transaction didn't commit, yet. And when the transaction
rolls back, the data automagically disapear (second SELECT). I
understand this is normal.

But in my trigger function "adjust()", executed within a transaction
opened by "INSERT INTO master", the 'fresh' data (set by "new.id :=
value") isn't visible to other commands (like: INSERT INTO aux...).

Is this intentional?

Theoretically: would it violate corrent database application coding
style/standards (SQL standard?), if the new.* data was in fact visible
for statements inside such transaction, like the INSERT AUX above?

BTW: May be there is other solution for my "INSERT ... AUX" which I
cannot see myself? One thing, though: I cannot have TRIGGER AFTER INSERT
do the job of putting the correct data into AUX TABLE, since that table
takes intermediate data used during MASTER.ID computation, and it is
required, that those intermediate data never-ever get into the MASTER
table itself (or any other table apart from the AUX table - where they
must reside. It's one of the reasons for AUX table to exist).
-- 
-R

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

   http://archives.postgresql.org/

Re: [GENERAL] Aggregate in Correlated SubQuery

[Niederland]

Sorry working to late at night Query works just a typo
the following works.

 WHERE
   Lead.CreationDate = (SELECT MIN(LL.CreationDate) FROM Lead AS LL
 WHERE LL.ProspectID = Lead.ProspectID)

sorry for the previous post,
Roger

Niederland wrote:
> Before postgresql 8.1.5, I could do the following to find the first
> lead that created a prospect in my application.
>
> SELECT
>   Lead.LeadID,
>   Prospect.ProspectID
> FROM
>   Prospect INNER JOIN Lead USING (ProspectID)
> WHERE
>   Lead.CreationDate = (SELECT MIN(Lead.CreationDate) FROM Lead AS LL
> WHERE LL.ProspectID = Lead.ProspectID)
>
> With 8.1.5 this now generates:
> ERROR: aggregates not allowed in WHERE clause
> SQL state: 42803
>
> Shouldn't aggregate functions still work within correlated subQueries?
> I saw in the 8.1.5 release notes that this was disabled due to crashes
> this caused in 8.1.X versions for Postgresql.  I have never experienced
> this with queries similar to the above.


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

[GENERAL] Same-page UPDATEs in bloated tables

[Ian Dowse]

Hi,

I've been seeing an issue with 8.1.4 that seems to be caused by the
way UPDATE operations prefer to place the new row version in the
same page as the original row. The issue is specific to UPDATEs;
it does not occur when each UPDATE is replaced by a DELETE/INSERT
pair. The problem can prevent a temporarily bloated table from ever
returning to its normal size even though all rows are frequently
changing and regular vacuuming is taking place.

A simple way to demonstrate the issue is to insert 10001 rows into
an empty table and delete the first 1 rows. Now, repeatedly
performing (lazy) vacuums and UPDATEs will never result in the table
size shrinking:

x=# SELECT * FROM foo;
  id   | val  
---+--
 10001 | foo2
(1 row)

x=# VACUUM ANALYSE foo;
VACUUM
x=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'foo';
 relpages | reltuples 
--+---
  527 | 1
(1 row)

x=# UPDATE foo SET val = 'foo3' WHERE id = 10001;
UPDATE 1
x=# VACUUM ANALYSE foo;
VACUUM
x=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'foo';
 relpages | reltuples 
--+---
  527 | 1
(1 row)

However, using DELETE/INSERT instead causes the vacuum to immediately
shrink the table:

x=# DELETE from foo WHERE id = 10001;
DELETE 1
x=# INSERT INTO foo(val) VALUES('foo4');
INSERT 0 1
x=# VACUUM ANALYSE foo;
VACUUM
x=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'foo';
 relpages | reltuples 
--+---
1 | 1
(1 row)

(Note that the above is just intended as a simple example to
demonstrate the effect; the actual case where this behaviour was
observed involved a high-churn table that never emptied but where
every row was updated at least a few times per day and (lazy)
vacuuming was performed approx every 10 minutes. The table would
occasionally become bloated, e.g. while performing a slow backup
of the database, and it would never recover its original size if
the rows were updated with UPDATE operations, but would quickly
return to its original size if DELETE/INSERT pairs were used instead).

This behaviour seemed fairly surprising given that UPDATEs are
supposed to be quite similar to INSERT/DELETE pairs in PostgreSQL.
Would it be possible to come up with some logic (in heap_update()?)
that could avoid using the same page if the page offset is way
beyond the nominal size of the table? Maybe some appropriate
statistics could be recorded by vacuum/analyse?

Ian

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

   http://archives.postgresql.org/

[GENERAL] Aggregate in Correlated SubQuery

[Niederland]

Before postgresql 8.1.5, I could do the following to find the first
lead that created a prospect in my application.

SELECT
  Lead.LeadID,
  Prospect.ProspectID
FROM
  Prospect INNER JOIN Lead USING (ProspectID)
WHERE
  Lead.CreationDate = (SELECT MIN(Lead.CreationDate) FROM Lead AS LL
WHERE LL.ProspectID = Lead.ProspectID)

With 8.1.5 this now generates:
ERROR: aggregates not allowed in WHERE clause
SQL state: 42803

Shouldn't aggregate functions still work within correlated subQueries?
I saw in the 8.1.5 release notes that this was disabled due to crashes
this caused in 8.1.X versions for Postgresql.  I have never experienced
this with queries similar to the above.


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

   http://archives.postgresql.org/

Re: [GENERAL] more anti-postgresql FUD

[Tom Lane]

Andrew Sullivan <[EMAIL PROTECTED]> writes:
> On Fri, Oct 13, 2006 at 01:35:51PM -0400, Tom Lane wrote:
>> looked reasonably robust --- ie, both safe and not full of unsupportable
>> assumptions about knowing exactly where everything actually is on the
>> disk platter.  It'd still be interesting if anyone gets a new idea...

> Might it be the case that WAL is the one area where, for Postgres,
> the cost of using raw disk could conceivably be worth the benefit? 

Raw disk wouldn't do much of anything to increase my comfort factor...

In practice, the answer these days for anyone who's remotely serious
is "get a battery-backed write cache", so I'm not sure how tense we
need to be about devising application-level workarounds.  BBWC was
rare and expensive the last time we discussed this seriously, but
it's not so much anymore.

regards, tom lane

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

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

Re: [GENERAL] more anti-postgresql FUD

[Alvaro Herrera]

Merlin Moncure wrote:
> On 10/14/06, Chris Mair <[EMAIL PROTECTED]> wrote:
> >The interesting part is the graph that shows updates / sec real time
> >vs. running total of updates:
> >http://www.1006.org/misc/20061014_pgupdates_bench/results.png
> 
> one small thing: the variances inside the trendline are caused by
> using integer timestamps...each slanted line is one second.  The blue
> line has a very slight wobble which is the effects of the vacuum..its
> very slight.  Actually in this test it would probably be good to
> vacuum extremely often, like every 100 records or so.

I was thinking what would happen if you used 8.2 for this test and had a
process continuously vacuuming the table, i.e. start a new vacuum as
soon as the previous one finished, with a reasonable vacuum_delay
setting (not sure what would qualify as reasonable; probably needs its
own set of tests to determine the sweet spot).

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

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

Re: [GENERAL] Triggers

[Tom Lane]

Chris Mair <[EMAIL PROTECTED]> writes:
>> Why you should write a function first and then the trigger, which must
>> call that function?

> ... there's not just PL/PGSQL: you might want
> to define a function in C or Perl and then have a trigger call it.

Right, that's the real reason: this approach doesn't constrain which
PL you can write your triggers in.

regards, tom lane

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

Re: [GENERAL] How to make a copy of schema

[A. Kretschmer]

am  Sun, dem 15.10.2006, um 19:10:53 +0300 mailte Andrus folgendes:
> I have a five company Postgres 8.1 database.
> 
> Each company data is stored in a different schema named Company1, Company2, 
> ..., Company5
> There is also public schema which contains common data.
> 
> Now customer wants to add sixth company to database.
> 
> In need to add some routine to my application which can create schema copy ?

You can make a pg_dump with option -n Company5, replace in the dump
Company5 with Company6 and restore this.

Or, make the dump, rename schema Company5 to Company6 and restore then
the dump.

Be careful with things like sequences!


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

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

Re: [GENERAL] more anti-postgresql FUD

[Merlin Moncure]

On 10/14/06, Chris Mair <[EMAIL PROTECTED]> wrote:

The interesting part is the graph that shows updates / sec real time
vs. running total of updates:
http://www.1006.org/misc/20061014_pgupdates_bench/results.png


one small thing: the variances inside the trendline are caused by
using integer timestamps...each slanted line is one second.  The blue
line has a very slight wobble which is the effects of the vacuum..its
very slight.  Actually in this test it would probably be good to
vacuum extremely often, like every 100 records or so.

merlin

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

[GENERAL] How to make a copy of schema

[Andrus]

I have a five company Postgres 8.1 database.

Each company data is stored in a different schema named Company1, Company2, 
..., Company5
There is also public schema which contains common data.

Now customer wants to add sixth company to database.

In need to add some routine to my application which can create schema copy ?

How to make a copy of schema Company5 so that schema called Company6 is 
created ?
Is there any sample code available ?

Andrus



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

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

Re: [GENERAL] Triggers

[Chris Mair]

> Why you should write a function first and then the trigger, which must
> call that function?
> 
> What are the advantages/disadvantages of that? Where can I find more
> information?

The PG way seems very natural to me:
you can write functions that do something and then have many triggers
call that same function.

Also there's not just PL/PGSQL: you might want
to define a function in C or Perl and then have a trigger call it.

Bye, Chris.


-- 

Chris Mair
http://www.1006.org


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

   http://archives.postgresql.org/

[GENERAL] Triggers

[Germán Hüttemann Arza]

Hello, I have this doubt since I started using PostgreSQL, a few months ago.Why triggers are defined that way? I mean, in others DBMS you simply write:CREATE TRIGGER  ... bla bla bla
BEGIN  END;Why you should write a function first and then the trigger, which must call that function?What are the advantages/disadvantages of that? Where can I find more information?
-- Germán Hüttemann Arza

Re: [GENERAL] BEGIN WORK READ ONLY;

[Peter Eisentraut]

David Fetter wrote:
> It would be handy for things like pgpool and Continuent, which could
> reliably distinguish up front the difference between a transaction
> that can write and one that can safely be sliced up and dispatched to
> read-only databases.

Yes, I think that would be the use case.  I wish someone were to 
implement that.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

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

Re: [GENERAL] Aggregate in Correlated SubQuery

[Andrew - Supernews]

On 2006-10-15, Niederland <[EMAIL PROTECTED]> wrote:
> Before postgresql 8.1.5, I could do the following to find the first
> lead that created a prospect in my application.
>
> SELECT
>   Lead.LeadID,
>   Prospect.ProspectID
> FROM
>   Prospect INNER JOIN Lead USING (ProspectID)
> WHERE
>   Lead.CreationDate = (SELECT MIN(Lead.CreationDate) FROM Lead AS LL
> WHERE LL.ProspectID = Lead.ProspectID)

I think that was always wrong, and that what you wanted was
MIN(LL.CreationDate).

The aggregate in the subquery must be an aggregate over the subquery's
rows, not over the outer query.

(Though there are ways to do this query without the subquery at all)

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

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

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