[HACKERS] Italian version of the PostgreSQL "Advocacy and Marketing" site is ready

[Justin Clift]

Hi everyone,

The Italian translation of the PostgreSQL "Advocacy and Marketing" site,
done by Stefano Reksten <[EMAIL PROTECTED]> is now complete and ready for
public use:

http://advocacy.postgresql.org?lang=it

Thanks heaps Stefano, you've put in a lot of effort and it's really
going to help.  :-)

In addition to this, Stefano has also volunteered to be an Italian
language contact for the PostgreSQL Advocacy and Marketing team.  With
luck we'll gain good PostgreSQL representatives for *all* of the major
languages and get some nifty stuff happening.

:-)

Regards and best wishes,

Justin Clift

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] [HACKERS] Hot Backup

[Bruce Momjian]

Shridhar Daithankar wrote:
> On 7 Oct 2002 at 13:48, Neil Conway wrote:
> 
> > "Sandeep Chadha" <[EMAIL PROTECTED]> writes:
> > > Postgresql has been lacking this all along. I've installed postgres
> > > 7.3b2 and still don't see any archive's flushed to any other
> > > place. Please let me know how is hot backup procedure implemented in
> > > current 7.3 beta(2) release.
> > AFAIK no such hot backup feature has been implemented for 7.3 -- you
> > appear to have been misinformed.
> 
> Is replication an answer to hot backup?

We already allow hot backups using pg_dump.  If you mean point-in-time
recovery, we have a patch for that ready for 7.4.

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

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

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] pgsql 7.2.3 crash

[Laurette Cisneros]

A lot of different things going on but my perl program (whose backend crashed)
was doing a lot of insert into table as select * from another table for a
lot of different tables. I see triggers referenced here and it should be
noted that for one of the tables the triggers were first disabled (update
pg_class) and re-enabled after the inserts are done (or it takes forever).

The pgsql log shows:
...
2002-10-08 15:48:38 [18033]  DEBUG:  recycled transaction log file
005200B1
2002-10-08 15:49:24 [28612]  DEBUG:  server process (pid 16003) was
terminated by signal 11
2002-10-08 15:49:24 [28612]  DEBUG:  terminating any other active server
processes
2002-10-08 18:49:24 [28616]  NOTICE:  Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
...

A core file was found in /base/326602604
and a backtrace shows:
(gdb) bt
#0  DeferredTriggerSaveEvent (relinfo=0x83335f0, event=0, oldtup=0x0,
newtup=0x8348150) at trigger.c:2056
#1  0x080b9d0c in ExecARInsertTriggers (estate=0x8333778,
relinfo=0x83335f0,
trigtuple=0x8348150) at trigger.c:952
#2  0x080c0f23 in ExecAppend (slot=0x8333660, tupleid=0x0,
estate=0x8333778)
at execMain.c:1280
#3  0x080c0dcd in ExecutePlan (estate=0x8333778, plan=0x83336f0,
operation=CMD_INSERT, numberTuples=0, direction=ForwardScanDirection,
destfunc=0x8334278) at execMain.c:1119
#4  0x080c026c in ExecutorRun (queryDesc=0x826fd88, estate=0x8333778,
feature=3,
count=0) at execMain.c:233
#5  0x0810b2d5 in ProcessQuery (parsetree=0x826c500, plan=0x83336f0,
dest=Remote,
completionTag=0xbfffec10 "") at pquery.c:259
#6  0x08109c83 in pg_exec_query_string (
query_string=0x826c168 "insert into jobsequences select * from
rev_000_jobsequences", dest=Remote, parse_context=0x8242cd8) at
postgres.c:811
#7  0x0810abee in PostgresMain (argc=4, argv=0xbfffee40,
username=0x8202d59 "laurette") at postgres.c:1929
#8  0x080f24fe in DoBackend (port=0x8202c28) at postmaster.c:2243
#9  0x080f1e9a in BackendStartup (port=0x8202c28) at postmaster.c:1874
#10 0x080f10e9 in ServerLoop () at postmaster.c:995
#11 0x080f0c56 in PostmasterMain (argc=1, argv=0x81eb398) at
postmaster.c:771
#12 0x080d172b in main (argc=1, argv=0xb7d4) at main.c:206
#13 0x401e7177 in __libc_start_main (main=0x80d15a8 , argc=1,
ubp_av=0xb7d4, init=0x80676ac <_init>, fini=0x81554f0 <_fini>,
rtld_fini=0x4000e184 <_dl_fini>, stack_end=0xb7cc)
at ../sysdeps/generic/libc-start.c:129


Thanks,

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
--
It's 10 o'clock...
Do you know where your bus is?


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

http://archives.postgresql.org

Re: [HACKERS] [GENERAL] Large databases, performance

[Martijn van Oosterhout]

On Mon, Oct 07, 2002 at 05:42:12PM +0200, Zeugswetter Andreas SB SD wrote:
> > Hackers, do you think it's possible to hack together a quick and dirty
> > patch, so that string length is represented by one byte?  IOW can a
> > database be built that doesn't contain any char/varchar/text value
> > longer than 255 characters in the catalog?
> 
> Since he is only using fixchar how about doing a fixchar implemetation, that 
> does not store length at all ? It is the same for every row anyways !

Remember that in Unicode, 1 char != 1 byte. In fact, any encoding that's not
Latin will have a problem. I guess you could put a warning on it: not for
use for asian character sets. So what do you do if someone tries to insert
such a string anyway?

Perhaps a better approach is to vary the number of bytes used for the
length. So one byte for lengths < 64, two bytes for lengths < 16384.
Unfortunatly, two bits in the length are already used (IIRC) for other
things making it a bit more tricky.
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

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

Re: [HACKERS] v7.2.3 - tag'd, packaged ... need it checked ...

[Peter Eisentraut]

Marc G. Fournier writes:

> On Mon, 7 Oct 2002, Peter Eisentraut wrote:
>
> > Marc G. Fournier writes:
> >
> > > Looks good from my end, Peter, I pulled the same docs that I pulled for
> > > v7.2.2, which I hope is okay?
> >
> > Probably not, because the version number needs to be changed and they need
> > to be rebuilt for each release.
>
> should I run the same 'gmake docs' then, as I've been doing for the
> snapshot(s)?

src/tools/RELEASE_CHANGES contains all the places where the version number
needs to be changed.  (Actually, I should eliminate some of these places,
but that won't help now.)  After that you can build the docs using

doc/src$ gmake postgres.tar.gz
doc/src$ mv postgres.tar.gz ..

and copy man.tar.gz from the ftp site (since it doesn't change) to doc/.
After that, 'make dist'.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] Where to call SetQuerySnapshot

[Joe Conway]

Tom Lane wrote:
> I did this and ended up with a rather long list of statement types that
> might need a snapshot:
> 
> elog(DEBUG2, "ProcessUtility");
> 
> /* set snapshot if utility stmt needs one */
> /* XXX maybe cleaner to list those that shouldn't set one? */
> if (IsA(utilityStmt, AlterTableStmt) ||
> IsA(utilityStmt, ClusterStmt) ||
> IsA(utilityStmt, CopyStmt) ||
> IsA(utilityStmt, ExecuteStmt) ||
> IsA(utilityStmt, ExplainStmt) ||
> IsA(utilityStmt, IndexStmt) ||
> IsA(utilityStmt, PrepareStmt) ||
> IsA(utilityStmt, ReindexStmt))
> SetQuerySnapshot();
> 
> (Anything that can call the planner or might create entries in
> functional indexes had better set a snapshot, thus stuff like
> ReindexStmt has the issue.)
> 
> I wonder if we should turn this around, and set a snapshot for all
> utility statements that can't show cause why they don't need one.
> Offhand, TransactionStmt, FetchStmt, and VariableSet/Show/Reset
> might be the only ones that need be excluded.  Comments?

It looks like an exclusion list would be easier to read and maintain.

Joe



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

http://archives.postgresql.org

Re: [HACKERS] Where to call SetQuerySnapshot

[Tom Lane]

I said:
> ...  So I had
> been thinking of pulling it out to postgres.c anyway.  I will do that.

I did this and ended up with a rather long list of statement types that
might need a snapshot:

elog(DEBUG2, "ProcessUtility");

/* set snapshot if utility stmt needs one */
/* XXX maybe cleaner to list those that shouldn't set one? */
if (IsA(utilityStmt, AlterTableStmt) ||
IsA(utilityStmt, ClusterStmt) ||
IsA(utilityStmt, CopyStmt) ||
IsA(utilityStmt, ExecuteStmt) ||
IsA(utilityStmt, ExplainStmt) ||
IsA(utilityStmt, IndexStmt) ||
IsA(utilityStmt, PrepareStmt) ||
IsA(utilityStmt, ReindexStmt))
SetQuerySnapshot();

(Anything that can call the planner or might create entries in
functional indexes had better set a snapshot, thus stuff like
ReindexStmt has the issue.)

I wonder if we should turn this around, and set a snapshot for all
utility statements that can't show cause why they don't need one.
Offhand, TransactionStmt, FetchStmt, and VariableSet/Show/Reset
might be the only ones that need be excluded.  Comments?

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] pg_dump file question

[Neil Conway]

Robert Partyka <[EMAIL PROTECTED]> writes:
> \connect template1
> .
> CREATE USER "foo" WITH SYSID 32 PASSWORD 'bar' NOCREATEDB NOCREATEUSER;
> .
> \connect template1 "foo"
> CREATE DATABASE "foodb" WITH TEMPLATE = template0 ENCODING = 'LATIN2';
> \connect "foodb" "foo"
> 
> I think evryone see why it dont work.

Yes -- it's a known problem with 7.2 that pg_dump can create
self-inconsistent dumps. In 7.3, this specific case has been fixed:
databases are now created using CREATE DATABASE ... WITH
OWNER. However, I'm not sure if there are other, similar problems that
haven't been fixed yet.

Cheers,

Neil

-- 
Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: Table spaces again [was Re: [HACKERS] Threaded Sorting]

[Hans-Jürgen Schönig]

Jim Buttafuoco wrote:

>Is this NOT what I have been after for many months now.  I dropped the 
>tablespace/location idea before 7.2 because that
>didn't seem to be any interest.  Please see my past email's for the SQL commands and 
>on disk directory layout I have
>proposed.  I have a working 7.2 system with tablespaces/locations (what ever you want 
>to call them,  I like locations
>because tablespace are an Oracle thing).  I would like to get this code ported into 
>7.4.
>
>Jim
>
>
>  
>
>>=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes:
>>
>>
>>>how would you handle table spaces?
>>>  
>>>
>>The plan that's been discussed simply defines a tablespace as being a
>>directory somewhere; physical storage of individual tables would remain
>>basically the same, one or more files under the containing directory.
>>
>>The point of this being, of course, that the DBA could create the
>>tablespace directories on different partitions or volumes in order to
>>provide the behavior he wants.
>>
>>In my mind this would be primarily a cleaner, more flexible
>>reimplementation of the existing "database location" feature.
>>
>>  
>>

wow :)
can we have the patch? i'd like to try it with my 7.2.2 :).
is it stable?

how did you implement it precisely? is it as you have proposed it?

Hans


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at , cluster.postgresql.at 
, www.cybertec.at 
, kernel.cybertec.at 



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

Rép. : Re: [GENERAL] [HACKERS] Hot Backup

[Erwan DUROSELLE]

What I understood from the Administrator's guide is:

  - Yes, PostgreSQL provides hot backup: it's the pg_dump utility. It'h hot because 
users can still be connected and work whil pg_dump is running ( though they will be 
slowed down). ( See Administrator's guide ch9)

 -  No, PostgreSQL does NOT provide a way to restore a database up to the last 
commited transaction, with a reapply of the WAL, as Oracle or SQL Server ( and others, 
I guess) do. That would be a VERY good feature. See Administrator's guide ch11

So, with Pg, if you backup your db every night with pg_dump, and your server crashes 
during the day, you will loose up to one day of work.

Am I true?

Erwan


---
Erwan DUROSELLE//SEAFRANCE DSI 
Responsable Bases de Données  //  Databases Manager
Tel: +33 (0)1 55 31 59 70//Fax: +33 (0)1 55 31 85 28
email: [EMAIL PROTECTED]
---


>>> Neil Conway <[EMAIL PROTECTED]> 07/10/2002 19:48 >>>
"Sandeep Chadha" <[EMAIL PROTECTED]> writes:
> Postgresql has been lacking this all along. I've installed postgres
> 7.3b2 and still don't see any archive's flushed to any other
> place. Please let me know how is hot backup procedure implemented in
> current 7.3 beta(2) release.

AFAIK no such hot backup feature has been implemented for 7.3 -- you
appear to have been misinformed.

That said, I agree that would be a good feature to have.

Cheers,

Neil

-- 
Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC


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

http://archives.postgresql.org


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

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] pg_dump file question

[Robert Partyka]

Hi,

My friend just notice me with some thing that make using dump files harder 
to use.

So here we go:
first we make some chaos :)
\c template1 postgres
create user foo with password 'bar' createdb nocreateuser;
\c template1 foo
create database foodb;
\c template1 postgres
alter user foo with nocreatedb;

then we... pg_dumpall -s (nowaday for explain we need only schemas :)

and we try to applay this pg_dumpall file and suprise :)

we have something like that:

\connect template1
.
CREATE USER "foo" WITH SYSID 32 PASSWORD 'bar' NOCREATEDB NOCREATEUSER;
.
\connect template1 "foo"
CREATE DATABASE "foodb" WITH TEMPLATE = template0 ENCODING = 'LATIN2';
\connect "foodb" "foo"

I think evryone see why it dont work.

I think that rebuild of dumping procedure to detect such a problems and 
remake dump file like that

\connect template1
.
CREATE USER "foo" WITH SYSID 32 PASSWORD 'bar' CREATEDB NOCREATEUSER;
.
\connect template1 "foo"
CREATE DATABASE "foodb" WITH TEMPLATE = template0 ENCODING = 'LATIN2';
\connect template1 "postgres"
ALTER USER "foo" WITH NOCREATEDB;
\connect "foodb" "foo"

will solve this problem.

regards
Robert Partyka
[EMAIL PROTECTED]
www.WDG.pl 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] Statistical Analysis, Vacuum, and Selectivity Restriction (PostGIS)(fwd)

[Bruce Momjian]

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

--- Begin Message ---

Bruce,

I've been trying to send this message to the hacker's mail list, but so
far every attempt has failed.  Perhaps you could forward this message
there for me?  

Thanks a lot,
dave



Hackers,

I'm trying to improve the performance of the PostGIS
(http://postgis.refractions.net) GEOMETRY indexing and have a few
questions for everyone (or just tell me I'm doing it all wrong).

Basically, the problem is that I'm currently using a constant number for
the restriction selectivity of the overlap (&&) operator (CREATE
OPERATOR ... RESTRICT=). This has to be quite low to force the index to
be used (the default value didnt use the index very often), but now it
uses it too often.

I'm currently implementing a much smarter method (see the proposal
below) - basically creating a 2D histogram and using that to make a
better guess at how many rows will be returned.

So far, I've looked at how the RESTRICT function is called (in 7.2), but
I've come up against a few problems that I'm not sure what the proper
way to handle is:

1. Getting the histogram to be generated and where to store it.
Ideally, I'd be able to store the statistics in pg_statistic and
'VACUUM ANALAYSE' would have hooks into my datatype's code.
Is this even possible?   Can I put a custom histogram in
pg_statistics?
Can I have VACUUM call my statistical analysis functions?

Alternatively, would I have to store the 2d histogram in a separate
[user] table and have the user explicitly call a function to update
it?

2. How do I access the histogram from my RESTRICT function?
I guess this is asking how do I efficiently do a query from within
a function?  The built-in RESTRICT functions seem to be able to
access the system catalogs directly ("SearchSysCache"), but how does

one access tables more directly (SPI?)?  Is this going to be slow
things down significantly?


Am I doing the right thing?

Here's a summary of the problem and the proposed solution,

dave
-
Several people have reported index selection problems with PostGIS:
sometimes it ops to use the spatial (GiST) index when its inappropriate.

I suggest you also read
http://www.postgresql.org/idocs/index.php?planner-stats.html
since it gives a good introduction to the statistics collected and used
by
the query planner.



Postgresql query planning
-

I'll start with a quick example, then move on to spatial indexing.
Lets start with a simple table:

name  location  age
---
dave  james bay 31
paul  james bay 30
oscar central park  23
chris downtown  22

With some indexes:

CREATE index people_name_idx  on people (name);
CREATE index people_age_idx   on people (age);

We then start to execute some queries:

#1) SELECT * FROM people WHERE location = 'james bay';

Postgresql's only possible query plan is to do a sequential scan of the
table and check to see if location ='james bay' - there isnt an index to

consult.  The sequential scan is simple - it loads each row from the
table
and checks to see if location='james bay'.

Here's what postgresql says about this query:

dblasby=# explain analyse SELECT * FROM people WHERE location = 'james
bay';

NOTICE:  QUERY PLAN:

Seq Scan on people  (cost=0.00..1.05 rows=2 width=25) (actual
time=0.02..0.03 rows=2 loops=1)
Total runtime: 0.07 msec

Note that postgresql is using a "Seq Scan" and predicts 2 result rows.
The
planner is very accurate in this estimate because the statistics
collected
explicitly say that 'james bay' is the most common value in this column
(cf.
pg_stats and pg_statistics tables).

#2) SELECT * FROM people WHERE name ='dave';

Here the query planner has two option - it can do a sequential scan or
it
can use the people_name_idx.  Here's what the query planner says (I
explicitly tell it to use the index in the 2nd run):

dblasby=# explain analyse SELECT * FROM people WHERE name ='dave';
NOTICE:  QUERY PLAN:

Seq Scan on people  (cost=0.00..1.05 rows=1 width=25) (actual
time=0.02..0.03 rows=1 loops=1)
Total runtime: 0.07 msec

dblasby=# set enable_seqscan=off;
dblasby=# explain analyse SELECT * FROM people WHERE name ='dave';
NOTICE:  QUERY PLAN:

Index Scan using people_name_idx on people  (cost=0.00..4.41 rows=1
width=25) (actual time=33.72..33.73 rows=1 loops=1)
Total runtime: 33.82 msec

In this case the sequential scan is faster because it only has to load
one
page from the disk and check 4 rows.  The index scan will have to load
in
the index, perform the scan, then load in the page with the data in it.
On a larger table, the index scan would probably be significantly
faster.

#

Re: [HACKERS] Analysis of ganged WAL writes

[Zeugswetter Andreas SB SD]

> > Can the magic be, that kaio directly writes from user space memory to the 
> > disk ?
> 
> This makes more assumptions about the disk drive's behavior than I think
> are justified...

No, no assumption about the drive, only the kaio implementation, namely, that
the kaio implementation reads the user memory at a latest (for the implementation)
possible time.

> > Since in your case all transactions A-E want the same buffer written,
> > the memory (not it's content) will also be the same.
> 
> But no, it won't: the successive writes will ask to write different
> snapshots of the same buffer.

That is what I meant, yes. Should have better formulated memory location.

> > The problem I can see offhand is how the kaio system can tell which
> > transaction can be safely notified of the write,
> 
> Yup, exactly.  Whose snapshot made it down to (stable) disk storage?

That is something the kaio implementation could prbbly know in our scenario, 
where we iirc only append new records inside the page (don't modify anything 
up front). Worst thing that can happen is that the kaio is too pessimistic 
about what is already on disk (memory already written, but aio call not yet done).

Andreas

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Where to call SetQuerySnapshot

[Tom Lane]

Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> 1. Where is the cleanest place to call SetQuerySnapshot() for utility
>> statements that need it?

> Without looking at it too closely, I would think postgres.c would be best, 
> unless there is a legit reason for a utility statement to *not* want 
> SetQuerySnapshot called.

Actually, there are a number of past threads concerned with whether we
are doing SetQuerySnapshot in the right places --- eg, should it occur
between statements of a plplgsql function?  Right now it doesn't, but
maybe it should.  In any case I have a note that doing SetQuerySnapshot
for COPY OUT in utility.c is a bad idea, because it makes COPY OUT act
differently from any other statement, when used inside a function: it
*will* change the query snapshot, where nothing else does.  So I had
been thinking of pulling it out to postgres.c anyway.  I will do that.

>> 2. Would it be a good idea to change CopyQuerySnapshot to elog(ERROR)
>> instead of silently creating a snapshot when none has been made?

> Is an assert appropriate?

Works for me.

regards, tom lane

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

Re: [HACKERS] Analysis of ganged WAL writes

[Curtis Faith]

> "Curtis Faith" <[EMAIL PROTECTED]> writes:
> > I'm not really worried about doing page-in reads because the
> disks internal
> > buffers should contain most of the blocks surrounding the end
> of the log
> > file. If the successive partial writes exceed a block (which
> they will in
> > heavy use) then most of the time this won't be a problem
> anyway since the
> > disk will gang the full blocks before writing.
>
> You seem to be willing to make quite a large number of assumptions about
> what the disk hardware will do or not do.  I trust you're going to test
> your results on a wide range of hardware before claiming they have any
> general validity ...

Tom, I'm actually an empiricist. I trust nothing that I haven't tested or
read the code for myself. I've found too many instances of bugs or poor
implementations in the O/S to believe without testing.

On the other hand, one has to make some assumptions in order to devise
useful tests.

I'm not necessarily expecting that I'll come up with something that will
help everyone all the time. I'm just hoping that I can come up with
something that will help those using modern hardware, most of the time.

Even if it works, this will probably become one of those flags that need to
be tested as part of the performance analysis for any given system. Or
perhaps ideally, I'll come up with a LogDiskTester that simulates log
output and determines the best settings to use for a given disk and O/S,
optimized for size or speed, heavy inserts, etc.


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

Re: [HACKERS] Analysis of ganged WAL writes

[Tom Lane]

"Curtis Faith" <[EMAIL PROTECTED]> writes:
> I'm not really worried about doing page-in reads because the disks internal
> buffers should contain most of the blocks surrounding the end of the log
> file. If the successive partial writes exceed a block (which they will in
> heavy use) then most of the time this won't be a problem anyway since the
> disk will gang the full blocks before writing.

You seem to be willing to make quite a large number of assumptions about
what the disk hardware will do or not do.  I trust you're going to test
your results on a wide range of hardware before claiming they have any
general validity ...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Analysis of ganged WAL writes

[Curtis Faith]

> "Curtis Faith" <[EMAIL PROTECTED]> writes:
> > Successive writes would write different NON-OVERLAPPING sections of the
> > same log buffer. It wouldn't make sense to send three separate
> copies of
> > the entire block. That could indeed cause problems.
>
> So you're going to undo the code's present property that all writes are
> block-sized?  Aren't you worried about incurring page-in reads because
> the kernel can't know that we don't care about data beyond what we've
> written so far in the block?

Yes, I'll try undoing the current behavior.

I'm not really worried about doing page-in reads because the disks internal
buffers should contain most of the blocks surrounding the end of the log
file. If the successive partial writes exceed a block (which they will in
heavy use) then most of the time this won't be a problem anyway since the
disk will gang the full blocks before writing.

If the inserts are not coming fast enough to fill the log then the disks
cache should contain the data from the last time that block (or the block
before) was written. Disks have become pretty good at this sort of thing
since writing sequentially is a very common scenario.

It may not work, but one doesn't make significant progress without trying
things that might not work.

If it doesn't work, then I'll make certain that commit log records always
fill the buffer they are written too, with variable length commit records
and something to identify the size of the padding used to fill the rest of
the block.


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

http://archives.postgresql.org

Re: [HACKERS] Dirty Buffer Writing [was Proposed LogWriter Scheme]

[Greg Copeland]

Bruce,

Is there remarks along these lines in the performance turning section of
the docs?  Based on what's coming out of this it would seem that
stressing the importance of leaving a notable (rule of thumb here?)
amount for general OS/kernel needs is pretty important.


Greg


On Tue, 2002-10-08 at 09:50, Tom Lane wrote:
> (This is, BTW, one of the reasons for discouraging people from pushing
> Postgres' shared buffer cache up to a large fraction of total RAM;
> starving the kernel of disk buffers is just plain not a good idea.)




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

Re: [HACKERS] Dirty Buffer Writing [was Proposed LogWriter Scheme]

[Tom Lane]

"Curtis Faith" <[EMAIL PROTECTED]> writes:
> Do you not think this is a potential performance problem to be explored?

I agree that there's a problem if the kernel runs short of buffer space.
I am not sure whether that's really an issue in practical situations,
nor whether we can do much about it at the application level if it is
--- but by all means look for solutions if you are concerned.

(This is, BTW, one of the reasons for discouraging people from pushing
Postgres' shared buffer cache up to a large fraction of total RAM;
starving the kernel of disk buffers is just plain not a good idea.)

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Analysis of ganged WAL writes

[Tom Lane]

"Curtis Faith" <[EMAIL PROTECTED]> writes:
> Successive writes would write different NON-OVERLAPPING sections of the
> same log buffer. It wouldn't make sense to send three separate copies of
> the entire block. That could indeed cause problems.

So you're going to undo the code's present property that all writes are
block-sized?  Aren't you worried about incurring page-in reads because
the kernel can't know that we don't care about data beyond what we've
written so far in the block?

regards, tom lane

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

[HACKERS] genetic query optimization

[iafmgc]

Hi,

 First of all, sorry for disturbing you all guys with my email talking about 
this subject again.

 I am a Spanish student and I following a course about Genetic Algorithms. My
teacher suggested me to search for some information of how to optimize things
in a database. Then I thought about PostgreSQL. I have downloaded tons of 
pdfs papers.

 Mainly I have to do a small assigment showing what can be achieved with
PostgreSQL using genetic query optimization.

 So far, I have found a lot of information but with a very high level, and 
what I am looking for is much more simple: I just need some insight of what can 
be done with the query optimizer and a few examples to start with and make out 
a new set of examples or to solve a little bit bigger problem.

 Hope you guys can help me out a little bit, with PDFs, papers or references.

 Many thanks in advance

 Miguel



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

http://archives.postgresql.org

Re: [HACKERS] Analysis of ganged WAL writes

[Curtis Faith]

> > Since in your case all transactions A-E want the same buffer written,
> > the memory (not it's content) will also be the same.
>
> But no, it won't: the successive writes will ask to write different
> snapshots of the same buffer.

Successive writes would write different NON-OVERLAPPING sections of the
same log buffer. It wouldn't make sense to send three separate copies of
the entire block. That could indeed cause problems.

If a separate log writing process was doing all the writing, it could
pre-gang the writes. However, I'm not sure this is necessary. I'll test the
simpler way first.

> > The problem I can see offhand is how the kaio system can tell which
> > transaction can be safely notified of the write,
>
> Yup, exactly.  Whose snapshot made it down to (stable) disk storage?

If you do as above, it can inform the transactions when the blocks get
written to disk since there are no inconsistent writes. If transactions A,
B and C had commits in block 1023, and the aio system writes that block to
the disk, it can notify all three that their transaction write is complete
when that block (or partial block) is written to disk.

It transaction C's write didn't make it into the buffer, I've got to assume
the aio system or the disk cache logic can handle realizing that it didn't
queue that write and therefore not inform transaction C of a completion.

- Curtis


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

Re: [HACKERS] Analysis of ganged WAL writes

[Curtis Faith]

> You example of >1 trx/proc/rev will wok _only_ if no more and no less
> than 1/4 of platter is filled by _other_ log writers.

Not really, if 1/2 the platter has been filled we'll still get in one more
commit in for a given rotation. If more than a rotation's worth of writing
has occurred that means we are bumping into the limit of disk I/O and that
it the limit that we can't do anything about without doing interleaved log
files.

> > The case of bulk inserts is one where I would expect that for
> simple tables
> > we should be able to peg the disks given today's hardware and enough
> > inserting processes.
>
> bulk inserts should probably be chunked at higher level by inserting
> several records inside a single transaction.

Agreed, that's much more efficient. There are plenty of situations where
the inserts and updates are ongoing rather than initial, Shridhar's
real-world test or TPC benchmarks, for example.

- Curtis


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Dirty Buffer Writing [was Proposed LogWriter Scheme]

[Curtis Faith]

> So you think if I try to write a 1 gig file, it will write enough to
> fill up the buffers, then wait while the sync'er writes out a few blocks
> every second, free up some buffers, then write some more?
>
> Take a look at vfs_bio::getnewbuf() on *BSD and you will see that when
> it can't get a buffer, it will async write a dirty buffer to disk.

We've addressed this scenario before, if I recall, the point Greg made
earlier is that buffers getting full means writes become synchronous.

I was trying to point out was that it was very likely that the buffers will
fill even for large buffers and that the writes are going to be driven out
not by efficient ganging but by something approaching LRU flushing, with an
occasional once a second slightly more efficient write of 1/32nd of the
buffers.

Once the buffers get full, all subsequent writes turn into synchronous
writes, since even if the kernel writes asynchronously (meaning it can do
other work), the writing process can't complete, it has to wait until the
buffer has been flushed and is free for the copy. So the relatively poor
implementation (for database inserts at least) of the syncer mechanism will
cost a lot of performance if we get to this synchronous write mode due to a
full buffer. It appears this scenario is much more likely than I had
thought.

Do you not think this is a potential performance problem to be explored?

I'm only pursuing this as hard as I am because I feel like it's deja vu all
over again. I've done this before and found a huge improvement (12X to 20X
for bulk inserts). I'm not necessarily expecting that level of improvement
here but my gut tells me there is more here than seems obvious on the
surface.

> As far as this AIO conversation is concerned, I want to see someone come
> up with some performance improvement that we can only do with AIO.
> Unless I see it, I am not interested in pursuing this thread.

If I come up with something via aio that helps I'd be more than happy if
someone else points out a non-aio way to accomplish the same thing. I'm by
no means married to any particular solutions, I care about getting problems
solved. And I'll stop trying to sell anyone on aio.

- Curtis


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

Re: [HACKERS] Analysis of ganged WAL writes

[Tom Lane]

"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes:
> Can the magic be, that kaio directly writes from user space memory to the 
> disk ?

This makes more assumptions about the disk drive's behavior than I think
are justified...

> Since in your case all transactions A-E want the same buffer written,
> the memory (not it's content) will also be the same.

But no, it won't: the successive writes will ask to write different
snapshots of the same buffer.

> The problem I can see offhand is how the kaio system can tell which
> transaction can be safely notified of the write,

Yup, exactly.  Whose snapshot made it down to (stable) disk storage?

regards, tom lane

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

Re: [HACKERS] Little note to php coders

[Robert Treat]

This is one of the reasons I usually recommend running with magic quotes
on, it provides a bit of insurance for those spots where your data
validation is not up to snuff.

Robert Treat

On Tue, 2002-10-08 at 06:11, Nigel J. Andrews wrote:
> On Tue, 8 Oct 2002, Sir Mordred The Traitor wrote:
> 
> > Check out this link, if you need something to laugh at:
> > http://www.postgresql.org/idocs/index.php?1'
> > 
> > Keeping in mind, that there are bunch of overflows in PostgreSQL(really?),
> > it is
> > very dangerous i guess. Right?
> 
> I'm not sure what list this really fits onto so I've left as hackers.
> 
> The old argument about data validation and whose job it is. However, is there a
> reason why all CGI parameters aren't scanned and rejected if they contain
> any punctuation. I was going to say if they contain anything non alphanumeric
> but then I'm not sure about internationalisation and that test.
> 
> 
> -- 
> Nigel J. Andrews
> 
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly




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

Re: [HACKERS] Analysis of ganged WAL writes

[Greg Copeland]

On Tue, 2002-10-08 at 04:15, Zeugswetter Andreas SB SD wrote:
> Can the magic be, that kaio directly writes from user space memory to the 
> disk ? Since in your case all transactions A-E want the same buffer written,
> the memory (not it's content) will also be the same. This would automatically 
> write the latest possible version of our WAL buffer to disk. 
> 

*Some* implementations allow for zero-copy aio.  That is a savings.  On
heavily used systems, it can be a large savings.

> The problem I can see offhand is how the kaio system can tell which transaction
> can be safely notified of the write, or whether the programmer is actually 
>responsible
> for not changing the buffer until notified of completion ?

That's correct.  The programmer can not change the buffer contents until
notification has completed for that outstanding aio operation.  To do
otherwise results in undefined behavior.  Since some systems do allow
for zero-copy aio operations, requiring the buffers not be modified,
once queued, make a lot of sense.  Of course, even on systems that don't
support zero-copy, changing the buffered data prior to write completion
just seems like a bad idea to me.

Here's a quote from SGI's aio_write man page:
If the buffer pointed to by aiocbp->aio_buf or the control block pointed
to by aiocbp changes or becomes an illegal address prior to asynchronous
I/O completion then the behavior is undefined.  Simultaneous synchronous
operations using the same aiocbp produce undefined results.

And on SunOS we have:
 The aiocbp argument points to an  aiocb  structure.  If  the
 buffer  pointed  to  by aiocbp->aio_buf or the control block
 pointed to by aiocbp becomes an  illegal  address  prior  to
 asynchronous I/O completion, then the behavior is undefined.
and
 For any system action that changes the process memory  space
 while  an  asynchronous  I/O  is  outstanding to the address
 range being changed, the result of that action is undefined.


Greg




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

Re: [HACKERS] v7.2.3 - tag'd, packaged ... need it checked ...

[Marc G. Fournier]

On Mon, 7 Oct 2002, Peter Eisentraut wrote:

> Marc G. Fournier writes:
>
> > Looks good from my end, Peter, I pulled the same docs that I pulled for
> > v7.2.2, which I hope is okay?
>
> Probably not, because the version number needs to be changed and they need
> to be rebuilt for each release.

should I run the same 'gmake docs' then, as I've been doing for the
snapshot(s)?



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

http://archives.postgresql.org

Re: [HACKERS] Little note to php coders

[Sir Mordred The Traitor]

Nice. That little, cute admin :-). 
This is already fixed, and where is 'thanks' i wonder?
I've been talking about sql injection.

How about that in http://www.postgresql.org/mirrors/index.php:
---
Warning: PostgreSQL query failed: ERROR: invalid INET value 'r'
in /usr/local/www/www/mirrors/index.php on line 263
Database update failed, contact the webmaster.

insert into mirrorsites(mirrorhostid,ipaddr,portnum,...) values(..)
--
Insert statement is shortened.








This letter has been delivered unencrypted. We'd like to remind you that
the full protection of e-mail correspondence is provided by S-mail
encryption mechanisms if only both, Sender and Recipient use S-mail.
Register at S-mail.com: http://www.s-mail.com

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Rép

[Martijn van Oosterhout]

On Tue, Oct 08, 2002 at 12:35:22PM +0200, Erwan DUROSELLE wrote:
> What I understood from the Administrator's guide is:
> 
>   - Yes, PostgreSQL provides hot backup: it's the pg_dump utility. It'h
> hot because users can still be connected and work whil pg_dump is running
> ( though they will be slowed down). ( See Administrator's guide ch9)

Correct.

>  -  No, PostgreSQL does NOT provide a way to restore a database up to the
> last commited transaction, with a reapply of the WAL, as Oracle or SQL
> Server ( and others, I guess) do. That would be a VERY good feature. See
> Administrator's guide ch11

Umm, I thought the whole point of WAL was that if the database crashed, the
WAL would provide the info to replay to the last committed transaction.

http://www.postgresql.org/idocs/index.php?wal.html

... because we know that in the event of a crash we will be able to recover
the database using the log: ...

These docs seem to corrobrate this.

> So, with Pg, if you backup your db every night with pg_dump, and your
> server crashes during the day, you will loose up to one day of work.

I've never lost any data with postgres, even if it's crashed, even without
WAL.

-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

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

Re: [HACKERS] Little note to php coders

[Vince Vielhaber]

On Tue, 8 Oct 2002, Sir Mordred The Traitor wrote:

> Check out this link, if you need something to laugh at:
> http://www.postgresql.org/idocs/index.php?1'
>
> Keeping in mind, that there are bunch of overflows in PostgreSQL(really?),
> it is
> very dangerous i guess. Right?

Don't see what you're complaining about.  I get teh 7.2.1 admin guide.

Vince.
-- 
==
Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net
 56K Nationwide Dialup from $16.00/mo at Pop4 Networking
  http://www.camping-usa.com  http://www.cloudninegifts.com
   http://www.meanstreamradio.com   http://www.unknown-artists.com
==




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

http://archives.postgresql.org

Re: [HACKERS] Little note to php coders

[Nigel J. Andrews]

On Tue, 8 Oct 2002, Sir Mordred The Traitor wrote:

> Check out this link, if you need something to laugh at:
> http://www.postgresql.org/idocs/index.php?1'
> 
> Keeping in mind, that there are bunch of overflows in PostgreSQL(really?),
> it is
> very dangerous i guess. Right?

I'm not sure what list this really fits onto so I've left as hackers.

The old argument about data validation and whose job it is. However, is there a
reason why all CGI parameters aren't scanned and rejected if they contain
any punctuation. I was going to say if they contain anything non alphanumeric
but then I'm not sure about internationalisation and that test.


-- 
Nigel J. Andrews


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

[HACKERS] Little note to php coders

[Sir Mordred The Traitor]

Check out this link, if you need something to laugh at:
http://www.postgresql.org/idocs/index.php?1'

Keeping in mind, that there are bunch of overflows in PostgreSQL(really?),
it is
very dangerous i guess. Right?




This letter has been delivered unencrypted. We'd like to remind you that
the full protection of e-mail correspondence is provided by S-mail
encryption mechanisms if only both, Sender and Recipient use S-mail.
Register at S-mail.com: http://www.s-mail.com

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

http://archives.postgresql.org

Re: [HACKERS] Analysis of ganged WAL writes

[Zeugswetter Andreas SB SD]

> ISTM aio_write only improves the picture if there's some magic in-kernel
> processing that makes this same kind of judgment as to when to issue the
> "ganged" write for real, and is able to do it on time because it's in
> the kernel.  I haven't heard anything to make me think that that feature
> actually exists.  AFAIK the kernel isn't much more enlightened about
> physical head positions than we are.

Can the magic be, that kaio directly writes from user space memory to the 
disk ? Since in your case all transactions A-E want the same buffer written,
the memory (not it's content) will also be the same. This would automatically 
write the latest possible version of our WAL buffer to disk. 

The problem I can see offhand is how the kaio system can tell which transaction
can be safely notified of the write, or whether the programmer is actually responsible
for not changing the buffer until notified of completion ?

Andreas

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