[HACKERS] row-wise comparison question/issue

[Jeremy Drake]

I noticed something odd when trying to use the row-wise comparison
mentioned in the release notes for 8.2 and in the docs
http://developer.postgresql.org/pgdocs/postgres/functions-comparisons.html#ROW-WISE-COMPARISON

This sets up a suitable test:

create type myrowtype AS (a integer, b integer);
create table myrowtypetable (rowval myrowtype);

insert into myrowtypetable select (a, b)::myrowtype from
generate_series(1,5) a, generate_series(1,5) b;

First I get this error:

select rowval < rowval from myrowtypetable ;
ERROR:  operator does not exist: myrowtype < myrowtype
LINE 1: select rowval < rowval from myrowtypetable ;
  ^
HINT:  No operator matches the given name and argument type(s). You may
need to add explicit type casts.

OK, I guess I can live with that.  I did create a new type, and there are
no operators for it...

Now, I can do the following (pointless) query
select ROW((rowval).*) < ROW((rowval).*) from myrowtypetable ;

and I get 25 rows of 'f'.  So far so good.

But if I try to do
select rowval from myrowtypetable ORDER BY ROW((rowval).*);
ERROR:  could not identify an ordering operator for type record
HINT:  Use an explicit ordering operator or modify the query.

or even
select rowval from myrowtypetable ORDER BY ROW((rowval).*) USING <;
ERROR:  operator does not exist: record < record
HINT:  No operator matches the given name and argument type(s). You may
need to add explicit type casts.

I know that that less-than operator exists, because I just used it in the
query that worked above.  It seems that ORDER BY just can't find it for
some reason.

Is it supposed to not work in order by?  That doesn't really make sense to
me why order by should be special for this.



-- 
All extremists should be taken out and shot.

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

Re: [HACKERS] New CRC algorithm: Slicing by 8

[Gurjeet Singh]

On 10/20/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
It doesn't say anything aboutbeing free of patents though.The Sourceforge project referred to in the article (but for which nolink is given) seems to be this one:
http://sourceforge.net/projects/slicing-by-8Yes. I had already mentioned that link in my posts. 
The "files" section contains a zip archive, inside which there are threesource files all of which state that the package, which is (c) IntelCorp. 2004-2006, is released under the BSD license, with this URL:
http://www.opensource.org/licenses/bsd-license.htmlAgain, no mention of patents anywhere.I'll try to get in touch with the author and get the clarification.
Best regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com

Re: [HACKERS] Asynchronous I/O Support

[NikhilS]

Hi, On 10/18/06, Martijn van Oosterhout  wrote:
On Wed, Oct 18, 2006 at 08:04:29PM +1300, Mark Kirkwood wrote:> >"bgwriter doing aysncronous I/O for the dirty buffers that it is> >supposed to sync"> >Another decent use-case?
Good idea, but async i/o is generally poorly supported.
Async i/o is stably supported on most *nix (apart from Linux 2.6.*) plus Windows.  
Guess it would be still worth it, since one fine day 2.6.* will start supporting it properly too. 

Regards,
Nikhils
> Is it worth considering using readv(2) instead?Err, readv allows you to split a single consecutive read into multiple
buffers. Doesn't help at all for reads on widely areas of a file.Have a ncie day,--Martijn van Oosterhout      
http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFFNhtyIB7bNG8LQkwRApNAAJ9mOhEaFqU59HRCCoJS9k9HCZZl5gCdHDWt
FurlswevGH4CWErsjcWmwVk==sQoa-END PGP SIGNATURE--- All the world's a stage, and most of us are desperately unrehearsed.

Re: [HACKERS] adminpack and pg_catalog

[Dave Page]

On 19/10/06 19:37, "Neil Conway" <[EMAIL PROTECTED]> wrote:

> Why does adminpack install functions into pg_catalog? This is
> inconsistent with the rest of the contrib/ packages, not to mention the
> definition of pg_catalog itself (which ought to hold builtin object
> definitions). 

The adminpack was originally written and intended to become builtin
functions, and in fact most of it has. pg_catalog was used to ensure
compatibility in the future, and because it's the only schema we can be sure
we'll find in any given database.

> And as AndrewSN pointed out on IRC, it also breaks
> pg_dump.

It does? In what way? I don't recall ever having any trouble restoring dumps
in the past.

Regards, Dave.


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

Re: [HACKERS] adminpack and pg_catalog

[Jim C. Nasby]

On Thu, Oct 19, 2006 at 02:37:34PM -0400, Neil Conway wrote:
> Why does adminpack install functions into pg_catalog? This is
> inconsistent with the rest of the contrib/ packages, not to mention the
> definition of pg_catalog itself (which ought to hold builtin object
> definitions). And as AndrewSN pointed out on IRC, it also breaks
> pg_dump.

When we wrote newsysviews we faced the same dilema of where to put
things. We ultimately decided on a pg_ schema in the hope that no one
else would use that name (we also prefaced everything in the schema with
pg_, but a big reason for that was potential inclusion into core).
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(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: [HACKERS] Statements with syntax errors are not logged

[Jim C. Nasby]

On Thu, Oct 19, 2006 at 06:32:08PM -0400, Tom Lane wrote:
> So I'm inclined to leave the behavior as-is.  The documentation for
> log_statement already says
> 
>   Note: Statements that generate syntax errors are not logged. Set
>   log_min_error_statement to error to log such statements. 
> 
> We could improve the wording here, perhaps, but ultimately this is a
> documentation issue.

It'd be good to put a note to that effect in postgresql.conf as well.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [HACKERS] New CRC algorithm: Slicing by 8

[Alvaro Herrera]

Tom Lane wrote:
> "Gurjeet Singh" <[EMAIL PROTECTED]> writes:
> >> Please refer to the following link for a new algorithm to calculate
> >> CRC, developed by Intel.
> >> 
> >> http://www.intel.com/technology/magazine/communications/slicing-by-8-0306.htm
> 
> I can't help wondering what the patent status of this algorithm is.
> 
> (This from a guy who still remembers very well how Unisys published the
> LZW algorithm in a manner that made it look like it was free for anyone
> to use...)

This article

http://www.intel.com/technology/magazine/communications/slicing-by-8-0306.htm

seems to imply it's provided "free".  It doesn't say anything about
being free of patents though.

The Sourceforge project referred to in the article (but for which no
link is given) seems to be this one:

http://sourceforge.net/projects/slicing-by-8

The "files" section contains a zip archive, inside which there are three
source files all of which state that the package, which is (c) Intel
Corp. 2004-2006, is released under the BSD license, with this URL:

http://www.opensource.org/licenses/bsd-license.html

Again, no mention of patents anywhere.

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

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

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

Re: [HACKERS] New CRC algorithm: Slicing by 8

[Tom Lane]

"Gurjeet Singh" <[EMAIL PROTECTED]> writes:
>> Please refer to the following link for a new algorithm to calculate
>> CRC, developed by Intel.
>> 
>> http://www.intel.com/technology/magazine/communications/slicing-by-8-0306.htm

I can't help wondering what the patent status of this algorithm is.

(This from a guy who still remembers very well how Unisys published the
LZW algorithm in a manner that made it look like it was free for anyone
to use...)

regards, tom lane

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

Re: [HACKERS] Statements with syntax errors are not logged

[Tom Lane]

I wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
>> Then it should be changed to log *only* successfully executed statements 
>> and explicitly documented as such.

> Well, maybe we should do that.

I fooled around with doing that, and while it's a simple code change,
I realized that it's got a fairly serious drawback: if you get an error
in a parameterized query, there's no way at all to find out via logging
what the parameters were that it failed on.  That seems to negate one
of the main uses of the parameter-value-logging code that we put so much
work into in this cycle.

So I'm inclined to leave the behavior as-is.  The documentation for
log_statement already says

Note: Statements that generate syntax errors are not logged. Set
log_min_error_statement to error to log such statements. 

We could improve the wording here, perhaps, but ultimately this is a
documentation issue.

regards, tom lane

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

Re: [HACKERS] [COMMITTERS] pgsql: Work around reported problem that AIX's getaddrinfo() doesn't

[Tom Lane]

"Rocco Altier" <[EMAIL PROTECTED]> writes:
> This change has broken my AIX 5.2 buildfarm machine (asp/kookaburra),
> but doesn't seem to break the 5.3 member (grebe).

Sigh, count on IBM to make life complicated.

Instead of hacking servname, I suppose we will have to poke the correct
value into sin_port afterwards.  Would you check on the names of the
port fields in the IPv4 and IPv6 cases?  (It might be s6_port or some
such for IPv6.)

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: [HACKERS] [COMMITTERS] pgsql: Work around reported problem that AIX's getaddrinfo() doesn't

[Rocco Altier]

This change has broken my AIX 5.2 buildfarm machine (asp/kookaburra),
but doesn't seem to break the 5.3 member (grebe).

It appears to have problems with stats being off now?

What can I do to help debug the situation?

-rocco

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
> Sent: Thursday, October 19, 2006 1:27 PM
> To: pgsql-committers@postgresql.org
> Subject: [COMMITTERS] pgsql: Work around reported problem 
> that AIX's getaddrinfo() doesn't 
> 
> 
> Log Message:
> ---
> Work around reported problem that AIX's getaddrinfo() doesn't 
> seem to zero
> sin_port in the returned IP address struct when servname is 
> NULL.  This has
> been observed to cause failure to bind the stats collection 
> socket, and
> could perhaps cause other issues too.  Per reports from Brad Nicholson
> and Chris Browne.
> 
> Modified Files:
> --
> pgsql/src/backend/libpq:
> ip.c (r1.36 -> r1.37)
> 
> (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/
libpq/ip.c.diff?r1=1.36&r2=1.37)

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

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

Re: [HACKERS] Granting all tables in schema by one SQL command

[Merlin Moncure]

On 10/19/06, Krycek <[EMAIL PROTECTED]> wrote:

The proposed syntax is:

GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW
TABLES IN public TO phpuser;"



"GRANT SELECT ON NEW TABLES IN public TO phpuser;"?

What does "NEW TABLES" mean in this context?


the point is to allow tables to inherit permissions from the parent
schema.  this is actually imo much more important than multiple table
grant, because it is already trivially easy to do that with a pl/pgsql
function doing 'grant' in dynamic sql over information schema for
example.  There was considerable debate on this topic at the time that
todo was written up.

the 'new tables' bit also raises the difficulty up a notch.

merlin

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

Re: [HACKERS] [PATCHES] oid2name optargs

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> oid2name requires an extern char*optarg to build on win32.

[ looks around... ]  zic.c seems to need it too.

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: [HACKERS] Extending tablespaces

[Gevik Babakhani]

Okay, thank you.

On Thu, 2006-10-19 at 15:56 -0400, Tom Lane wrote:
> Gevik Babakhani <[EMAIL PROTECTED]> writes:
> > Now I am thinking what it would take to give pg the functionality to
> > extend tablespaces automatically.
> 
> It's called LVM ... and no, we are not interested in reimplementing
> filesystem-level functionality ...
> 
>   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
> 


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

   http://archives.postgresql.org

Re: [HACKERS] Beta, RC Time?

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Where are we on releasing beta2 or perhaps going right to an RC1
> release?  Seems it is time for one of them.

I think we need a beta2 now, and perhaps RC1 in a week.  We've done
enough portability hacking recently that some more beta seems indicated.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Granting all tables in schema by one SQL command

[Walter Cruz]

looks to me that NEW TABLES are the tables created AFTER the GRANT :)Is that?[]'s- WalterOn 10/19/06, Krycek <
[EMAIL PROTECTED]> wrote:HelloIm new to PostgreSQL development and I would like to make "introduce"
patch that will satisfied this point of TODO:"%Allow GRANT/REVOKE permissions to be applied to all schema objects withone commandThe proposed syntax is:GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW
TABLES IN public TO phpuser;"My proposal of SQL syntax is:GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }[,...] | ALL [ PRIVILEGES ] }ON ALL TABLES IN schema_name[,...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]for granting all tables/views in schemaandREVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }ON ALL TABLES IN schema_name[,...] FROM { username | GROUP groupname | PUBLIC } [, ...][ CASCADE | RESTRICT ]for revokeing all tables/views in schemaPlease review is it ok.
And I have question about what author of point in TODO list has on mindwhen was writing"GRANT SELECT ON NEW TABLES IN public TO phpuser;"?What does "NEW TABLES" mean in this context?
Thanx for reply.Sorry for my english.--Best RegardsPrzemyslaw Kantyka[EMAIL PROTECTED]---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] Granting all tables in schema by one SQL command

[Krycek]

Hello

Im new to PostgreSQL development and I would like to make "introduce"  
patch that will satisfied this point of TODO:
"%Allow GRANT/REVOKE permissions to be applied to all schema objects with  
one command


The proposed syntax is:

GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW  
TABLES IN public TO phpuser;"


My proposal of SQL syntax is:

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON ALL TABLES IN schema_name[,...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

for granting all tables/views in schema

and

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON ALL TABLES IN schema_name[,...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

for revokeing all tables/views in schema

Please review is it ok.

And I have question about what author of point in TODO list has on mind  
when was writing


"GRANT SELECT ON NEW TABLES IN public TO phpuser;"?

What does "NEW TABLES" mean in this context?

Thanx for reply.

Sorry for my english.

--
Best Regards
Przemyslaw Kantyka
[EMAIL PROTECTED]

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

Re: [HACKERS] Asynchronous I/O Support

[Bruno Wolff III]

On Sun, Oct 15, 2006 at 14:26:12 -0400,
  Neil Conway <[EMAIL PROTECTED]> wrote:
> 
> At least according to [1], kernel AIO on Linux still doesn't work for
> buffered (i.e. non-O_DIRECT) files. There have been patches available
> for quite some time that implement this, but I'm not sure when they are
> likely to get into the mainline kernel.
> 
> -Neil
> 
> [1] http://lse.sourceforge.net/io/aio.html

An improvement is going into 2.6.19 to handle asynchronous vector reads
and writes. This was covered by Linux Weekly News a couple of weeks ago:
http://lwn.net/Articles/201682/

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

   http://archives.postgresql.org

[HACKERS] Beta, RC Time?

[Bruce Momjian]

Where are we on releasing beta2 or perhaps going right to an RC1
release?  Seems it is time for one of them.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

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

   http://archives.postgresql.org

Re: [HACKERS] [PATCHES] Win32 BYTE_ORDER

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
>> Done, but I think you forgot the defs for LITTLE_ENDIAN and 
>> friends ... it seems unlikely that a platform would provide 
>> those and then forget BYTE_ORDER.

> Well, it compiled just fine, so it must've found it somewhere :-)

I think the #if tests probably reduced to 0 == 0 ... so my bet is
it compiled but didn't work.

regards, tom lane

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

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

Re: [HACKERS] [PATCHES] Win32 BYTE_ORDER

[Magnus Hagander]

> > This patch adds a #define for BYTE_ORDER to win32.h if it's 
> not pulled 
> > in elsewhere, as needed by msvc build of pgcrypto. Seems 
> several other 
> > platforms define it in their port file already, but not win32.
> 
> Done, but I think you forgot the defs for LITTLE_ENDIAN and 
> friends ... it seems unlikely that a platform would provide 
> those and then forget BYTE_ORDER.

Well, it compiled just fine, so it must've found it somewhere :-)

//Magnus

---(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: [HACKERS] [PATCHES] earthdistance update for msvc

[Magnus Hagander]

> > A define is needed to expose the M_PI define from the 
> system headers.
> 
> It seems like the other places where we depend on M_PI, we 
> instead have
> 
> #ifndef M_PI
> #define M_PI 3.14159265358979323846
> #endif
> 
> Perhaps that's a better solution?

Oh. I actually did that for my quick-n-dirty fix and thought it would be
rejected so I researched the other one :-) either one is fine by me.

//Magnus

---(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: [HACKERS] [PATCHES] Win32 BYTE_ORDER

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> This patch adds a #define for BYTE_ORDER to win32.h if it's not pulled
> in elsewhere, as needed by msvc build of pgcrypto. Seems several other
> platforms define it in their port file already, but not win32.

Done, but I think you forgot the defs for LITTLE_ENDIAN and friends
... it seems unlikely that a platform would provide those and then
forget BYTE_ORDER.

regards, tom lane

---(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: [HACKERS] [PATCHES] earthdistance update for msvc

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> A define is needed to expose the M_PI define from the system headers.

It seems like the other places where we depend on M_PI, we instead have

#ifndef M_PI
#define M_PI 3.14159265358979323846
#endif

Perhaps that's a better solution?

regards, tom lane

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

Re: [HACKERS] Extending tablespaces

[Tom Lane]

Gevik Babakhani <[EMAIL PROTECTED]> writes:
> Now I am thinking what it would take to give pg the functionality to
> extend tablespaces automatically.

It's called LVM ... and no, we are not interested in reimplementing
filesystem-level functionality ...

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: [HACKERS] [PATCHES] Adminpack build fix

[Magnus Hagander]

> > Attached patch removes a couple of extern definitions from 
> adminpack, 
> > replacing some of them with a #include.
> 
> I've now removed all the local DLLIMPORT-redeclarations I 
> could find in favor of marking the relevant variables in the 
> main header files.

Thanks. All affected projects build with MSVC now - haven't tested if
they work, but they pass the compiler...

//Magnus

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

[HACKERS] Extending tablespaces

[Gevik Babakhani]

Recently I ran into a "disk full" problem with my database. I had to
play with the tablespaces and locations symlinks... until the disk could
be cleaned just enough for the database to continue operation. I had
plenty of space on another disk but found no *easy* way to humbly ask
postgres to continue using other empty disk.

I read the partitioning at
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
but this unfortunately is not an option.

Now I am thinking what it would take to give pg the functionality to
extend tablespaces automatically.

Something like "ALTER TABLESPACE foo ADD LOCATION '/bar'" and then PG
can continue operation without notifying the disk is full...

I yet have to dive more deeply into the storage system to gain more
knowledge. I also realize that implementing something like this will
effect many things like VACUUM, pg_dump, indexing, sorting and the
entire storage system etc..etc..

I very much would like to know the communities opinion and thoughts
about this. Would this even be possible? Or I am just dreaming (again)
and scaring everyone else.

-- 
Regards,
Gevik Babakhani
www.postgresql.nl
www.truesoftware.nl


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

Re: [HACKERS] [PATCHES] Adminpack build fix

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> Attached patch removes a couple of extern definitions from adminpack,
> replacing some of them with a #include.

I've now removed all the local DLLIMPORT-redeclarations I could find
in favor of marking the relevant variables in the main header files.

regards, tom lane

---(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: [HACKERS] Speaking of contrib Makefiles

[Magnus Hagander]

> both cube and seg seems to do some funky stuff with 
> flex/bison, if I'm not entirely mistaken. Cube, for example, 
> uses a rule that builds "cubeparse.o" from "cubescan.c". 
> (which in turnis built from flex/bison files).
> 
> Is there any reason why we don't build a file called 
> "cubescan.o" from "cubescan.c" and thus simplify the rules a bit?

Hmm. I shold recall this one. I'm just reading the makefile wrong - all
it says is that there is a dependency between them. Oops. Time to crawl
back under that rock...

//magnus

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

[HACKERS] Speaking of contrib Makefiles

[Magnus Hagander]

both cube and seg seems to do some funky stuff with flex/bison, if I'm
not entirely mistaken. Cube, for example, uses a rule that builds
"cubeparse.o" from "cubescan.c". (which in turnis built from flex/bison
files).

Is there any reason why we don't build a file called "cubescan.o" from
"cubescan.c" and thus simplify the rules a bit?

//Magnus


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

[HACKERS] adminpack and pg_catalog

[Neil Conway]

Why does adminpack install functions into pg_catalog? This is
inconsistent with the rest of the contrib/ packages, not to mention the
definition of pg_catalog itself (which ought to hold builtin object
definitions). And as AndrewSN pointed out on IRC, it also breaks
pg_dump.

-Neil



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

Re: [HACKERS] CVS repository rsync

[Magnus Hagander]

> > I've set up my laptop to sync down the full cvs repository 
> using rsync 
> > (remember - windows = no cvsup). This works well, except 
> every now and 
> > then (not every time, but definitly often enough to bother me) it 
> > resyncs the entire repository, and not just the files that have had 
> > commits to them.
> > 
> > Anybody have a clue as to why this is happening, and what I can do 
> > about it?
> 
> This, perhaps?:
> 
> --modify-window
>   When comparing two timestamps rsync  treats  the  timestamps  as
>   being  equal if they are within the value of modify_window. This
>   is normally zero, but you may find it useful to set  this  to  a
>   larger  value  in some situations. In particular, when transfer-
>   ring to Windows FAT filesystems  which  cannot  represent  times
>   with a 1 second resolution --modify-window=1 is useful.
> 
> (from rsync man page)

Maybe. But I'm on NTFS, which has 100-naonsecond granularity on times,
so it's much more exact than the server ;-)

//Magnus

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

   http://archives.postgresql.org

Re: [HACKERS] CVS repository rsync

[Steve Crawford]

Magnus Hagander wrote:
> I've set up my laptop to sync down the full cvs repository using rsync
> (remember - windows = no cvsup). This works well, except every now and
> then (not every time, but definitly often enough to bother me) it
> resyncs the entire repository, and not just the files that have had
> commits to them.
> 
> Anybody have a clue as to why this is happening, and what I can do about
> it?
> 
> //Magnus
> 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
> 

This, perhaps?:

--modify-window
  When comparing two timestamps rsync  treats  the  timestamps  as
  being  equal if they are within the value of modify_window. This
  is normally zero, but you may find it useful to set  this  to  a
  larger  value  in some situations. In particular, when transfer-
  ring to Windows FAT filesystems  which  cannot  represent  times
  with a 1 second resolution --modify-window=1 is useful.

(from rsync man page)

Cheers,
Steve


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

Re: [HACKERS] qsort vs MSVC build

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> On Thu, 2006-10-19 at 13:56 -0400, Tom Lane wrote:
>> Is it worth renaming our qsort to pg_qsort to avoid this?  (I'd be
>> inclined to do that via a macro "#define qsort pg_qsort", not by running
>> around and changing all the code.)

> Why not change each call site? I don't think it would hurt to be clear
> about the fact that we're calling our own sorting function, not the
> platform's libc qsort().

I'm concerned about the prospect of someone forgetting to use pg_qsort,
and getting the likely-inferior platform one.

However, the only place where we probably care very much is tuplesort.c,
and that's using qsort_arg now anyway.  So plan C might be to drop
port/qsort.c altogether, and just be sure to use qsort_arg anyplace that
we care about not getting the platform one.

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: [HACKERS] qsort vs MSVC build

[Neil Conway]

On Thu, 2006-10-19 at 13:56 -0400, Tom Lane wrote:
> Is it worth renaming our qsort to pg_qsort to avoid this?  (I'd be
> inclined to do that via a macro "#define qsort pg_qsort", not by running
> around and changing all the code.)

Why not change each call site? I don't think it would hurt to be clear
about the fact that we're calling our own sorting function, not the
platform's libc qsort().

-Neil



---(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: [HACKERS] qsort vs MSVC build

[Magnus Hagander]

> >> Is it worth renaming our qsort to pg_qsort to avoid this?  
> >> (I'd be inclined to do that via a macro "#define qsort 
> pg_qsort", not 
> >> by running around and changing all the code.)
> 
> > Yeah, I think it is ;-) Just make sure it happens before we pull in 
> > stdlib.h, so we don't rename tha tone as well...
> 
> No, we'd want to put the macro after stdlib.h, I should think?
> Specifically in port.h, where we play other similar games.

Uh, that's what I meant, I just wrote it backwards. Sorry.

//Magnus

---(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: [HACKERS] CVS repository rsync

[Magnus Hagander]

> > This works well, except every now and
> > then (not every time, but definitly often enough to bother me) it 
> > resyncs the entire repository, and not just the files that have had 
> > commits to them.
> 
> I haven't noticed this personally, although I might have just 
> missed it.
> Are you sure you're not just noticing the times when a new 
> release has been tagged? (Tagging in CVS requires touching 
> all tagged files.)

Hmm, now that you mention it, at least this time that's probably the
reason. Didn't consider that a tag in a *backbranch* affects all the
files inthe repository for HEAD as well. I guess I'll just keep my eyes
open for next time it happens to see if that happens then as well.

//Magnus

---(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: [HACKERS] qsort vs MSVC build

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
>> Is it worth renaming our qsort to pg_qsort to avoid this?  
>> (I'd be inclined to do that via a macro "#define qsort 
>> pg_qsort", not by running around and changing all the code.)

> Yeah, I think it is ;-) Just make sure it happens before we pull in
> stdlib.h, so we don't rename tha tone as well...

No, we'd want to put the macro after stdlib.h, I should think?
Specifically in port.h, where we play other similar games.

regards, tom lane

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

Re: [HACKERS] CVS repository rsync

[Neil Conway]

On Thu, 2006-10-19 at 19:52 +0200, Magnus Hagander wrote:
> I've set up my laptop to sync down the full cvs repository using rsync
> (remember - windows = no cvsup).

Yeah, I do this as well, and for similar reasons (cvsup is unmaintained
and annoying to build, at least on AMD64/Debian).

> This works well, except every now and
> then (not every time, but definitly often enough to bother me) it
> resyncs the entire repository, and not just the files that have had
> commits to them.

I haven't noticed this personally, although I might have just missed it.
Are you sure you're not just noticing the times when a new release has
been tagged? (Tagging in CVS requires touching all tagged files.)

-Neil



---(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: [HACKERS] qsort vs MSVC build

[Martijn van Oosterhout]

On Thu, Oct 19, 2006 at 01:56:24PM -0400, Tom Lane wrote:
> Is it worth renaming our qsort to pg_qsort to avoid this?  (I'd be
> inclined to do that via a macro "#define qsort pg_qsort", not by running
> around and changing all the code.)

Redefining a function that is defined in POSIX and present on most
systems seems like a bad idea. Not in the least because ELF linking
rules mean that if any library (say libssl) in the backend calls qsort,
they'll get the postgresql one, rather than the C library like they
expect. That seems fragile to me.

The #define would be fine, as long as you make sure it's called after
the system headers, otherwise the problem isn't fixed.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] qsort vs MSVC build

[Magnus Hagander]

> > MSVCRTD.lib(MSVCR80D.dll) : error LNK2005: _qsort already 
> defined in 
> > qsort.obj
> 
> Hmm.  I've been seeing related complaints on Darwin, but they 
> were just warnings (about our qsort conflicting with the one in libc).

Yeah, seems it works in Mingw, but for some reason it's fatal in MSVC.


> Is it worth renaming our qsort to pg_qsort to avoid this?  
> (I'd be inclined to do that via a macro "#define qsort 
> pg_qsort", not by running around and changing all the code.)

Yeah, I think it is ;-) Just make sure it happens before we pull in
stdlib.h, so we don't rename tha tone as well...

//Magnus

---(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: [HACKERS] [PATCHES] Adminpack build fix

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
>> The same redeclaration technique is being used elsewhere 
>> (pg_buffercache and pg_freespacemap it looks like).  Aren't 
>> you getting warnings there too?

> I am - I just started working on getting those done as well.

OK, I guess we gotta play them all honestly.  Will fix.

regards, tom lane

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

Re: [HACKERS] qsort vs MSVC build

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> I just tried a rebuild of the MSVC stuff, and got the following error.
> Any ideas on the best way to fix that?

> 1>.\src\port\qsort.c(53) : warning C4005: 'min' : macro redefinition
> C:\Program Files\Microsoft Visual Studio

This is fixed already in HEAD.

> MSVCRTD.lib(MSVCR80D.dll) : error LNK2005: _qsort already defined in
> qsort.obj

Hmm.  I've been seeing related complaints on Darwin, but they were just
warnings (about our qsort conflicting with the one in libc).

Is it worth renaming our qsort to pg_qsort to avoid this?  (I'd be
inclined to do that via a macro "#define qsort pg_qsort", not by running
around and changing all the code.)

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

[HACKERS] CVS repository rsync

[Magnus Hagander]

I've set up my laptop to sync down the full cvs repository using rsync
(remember - windows = no cvsup). This works well, except every now and
then (not every time, but definitly often enough to bother me) it
resyncs the entire repository, and not just the files that have had
commits to them.

Anybody have a clue as to why this is happening, and what I can do about
it?

//Magnus


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

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

Re: [HACKERS] [PATCHES] Adminpack build fix

[Magnus Hagander]

> >> The reason for redeclaring these in the contrib files is to get 
> >> DLLIMPORT onto them...
> 
> > Interedting - it builds on MSVC without it :-O
> 
> > Anyway. That certainly explains why MSVC is complaining - 
> it's getting 
> > completely different definitions of these variables from the header 
> > and from the actual file.
> 
> The same redeclaration technique is being used elsewhere 
> (pg_buffercache and pg_freespacemap it looks like).  Aren't 
> you getting warnings there too?

I am - I just started working on getting those done as well. (they were
also living on my list of fix-later ones for some other reasons - the
other reasons now having been fixed)

//Magnus

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

[HACKERS] qsort vs MSVC build

[Magnus Hagander]

I just tried a rebuild of the MSVC stuff, and got the following error.
Any ideas on the best way to fix that?

(as you notice, I haven't pulled the very latest cvs so I haven't for
the min() fix that's put in now. Just let me know if the rest is also
fixed ;-))

//Magnus


Build started: Project: postgres, Configuration: Debug|Win32
Compiling...
qsort.c
1>.\src\port\qsort.c(53) : warning C4005: 'min' : macro redefinition
C:\Program Files\Microsoft Visual Studio
8\VC\include\stdlib.h(808) : see previous definition of 'min'
1>.\src\port\qsort.c(114) : warning C4273: 'qsort' : inconsistent dll
linkage
C:\Program Files\Microsoft Visual Studio
8\VC\include\stdlib.h(473) : see previous definition of 'qsort'
Generate DEF file
Not re-generating POSTGRES.DEF, file already exists.
Linking...
MSVCRTD.lib(MSVCR80D.dll) : error LNK2005: _qsort already defined in
qsort.obj
   Creating library Debug\postgres\postgres.lib and object
Debug\postgres\postgr
es.exp
.\Debug\postgres\postgres.exe : fatal error LNK1169: one or more
multiply define
d symbols found
postgres - 2 error(s), 2 warning(s)

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

Re: [HACKERS] [PATCHES] Adminpack build fix

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
>> The reason for redeclaring these in the contrib files is to 
>> get DLLIMPORT onto them...

> Interedting - it builds on MSVC without it :-O

> Anyway. That certainly explains why MSVC is complaining - it's getting
> completely different definitions of these variables from the header and
> from the actual file.

The same redeclaration technique is being used elsewhere (pg_buffercache
and pg_freespacemap it looks like).  Aren't you getting warnings there
too?

regards, tom lane

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

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

Re: [HACKERS] [PATCHES] Adminpack build fix

[Magnus Hagander]

> > Attached patch removes a couple of extern definitions from 
> adminpack, 
> > replacing some of them with a #include. (Cam eup with this 
> because we 
> > got a duplicate definition of DataDir when building with 
> Visual C++).
> 
> That isn't going to work unless we put DLLIMPORT into the 
> main headers.
> The reason for redeclaring these in the contrib files is to 
> get DLLIMPORT onto them...

Interedting - it builds on MSVC without it :-O

Anyway. That certainly explains why MSVC is complaining - it's getting
completely different definitions of these variables from the header and
from the actual file.

What do you think about simply adding DLLIMPORT to the variables in the
main header?

//Magnus

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

Re: [HACKERS] [PATCHES] Contrib Makefiles

[Magnus Hagander]

> > A couple of the Makefiles in contrib don't define OBJS= as 
> standard, 
> > instead they define SRCS= and then a makefile rule for 
> OBJS= that does 
> > a replacement on that.
> 
> > Is there any actual reason for that?
> 
> Can't see one.

Ok. Thanks.


> > If not, could the attached patch
> > please be applied to make them all look the same.
> 
> Done, but what about pgcrypto?  You may not have a choice but 
> to have a special case for that one, though, seeing it builds 
> its list on the fly.

Yeah, it's currently on my list of "not yet done" parts. I'm also
thinking there's a very large probability that I'll have to special-case
it.

//Magnus

---(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: [HACKERS] [PATCHES] Contrib Makefiles

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> A couple of the Makefiles in contrib don't define OBJS= as standard,
> instead they define SRCS= and then a makefile rule for OBJS= that does a
> replacement on that.

> Is there any actual reason for that?

Can't see one.

> If not, could the attached patch
> please be applied to make them all look the same.

Done, but what about pgcrypto?  You may not have a choice but to have a
special case for that one, though, seeing it builds its list on the fly.

regards, tom lane

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

Re: [HACKERS] [PATCHES] Adminpack build fix

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> Attached patch removes a couple of extern definitions from adminpack,
> replacing some of them with a #include. (Cam eup with this because we
> got a duplicate definition of DataDir when building with Visual C++).

That isn't going to work unless we put DLLIMPORT into the main headers.
The reason for redeclaring these in the contrib files is to get
DLLIMPORT onto them...

regards, tom lane

---(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: [HACKERS] Additional stats for Relations

[Jim C. Nasby]

On Thu, Oct 19, 2006 at 11:47:53AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > BTW, if we add these counters we'll be up to 7 stats dealing with vacuum
> > and analyze, and NikhilS has a patch we're finalizing that would add 3
> > more. Right now there's 4 slated to go into pg_stat_* in 8.2, but maybe
> > we should have a separate view for vacuum/FSM statistics?
> 
> I've seen no demonstration of a need for *any* of them, actually,
> and am pretty dubious that we want to add so much collection overhead.
> At least not without a major redesign of the stats reporting mechanism.
> If we just drop in another seven counters, we'll create an immediate 50%
> increase in the stats-file I/O volume, even when no vacuuming is
> happening at all.

Yeah, for stuff like vacuuming the current stats system may not make any
sense. Almost anything dealing with vacuum can really just be put into a
table, because it doesn't happen all that often.

My concern is that there's enough useful data to collect about vacuuming
and the FSM that it should probably get it's own set of tables/views,
rather than piggy-backing on pg_stat_*. But that ship has pretty much
sailed, so we're probably stuck with at least the last_* stuff in
pg_stat_* for the immediate future.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(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: [HACKERS] Additional stats for Relations

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> BTW, if we add these counters we'll be up to 7 stats dealing with vacuum
> and analyze, and NikhilS has a patch we're finalizing that would add 3
> more. Right now there's 4 slated to go into pg_stat_* in 8.2, but maybe
> we should have a separate view for vacuum/FSM statistics?

I've seen no demonstration of a need for *any* of them, actually,
and am pretty dubious that we want to add so much collection overhead.
At least not without a major redesign of the stats reporting mechanism.
If we just drop in another seven counters, we'll create an immediate 50%
increase in the stats-file I/O volume, even when no vacuuming is
happening at all.

regards, tom lane

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

Re: [HACKERS] Statements with syntax errors are not logged

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> To some extent the logging 
>> settings are only meant to capture successfully executed statements

> Then it should be changed to log *only* successfully executed statements 
> and explicitly documented as such.

Well, maybe we should do that.  It'd be relatively easy to handle the
ALL case before parsing and the other cases after in the "simple query"
code path, but I don't see any nice way to make it work in the
parse/bind/execute code path.  As the code stands in HEAD, we don't
normally log parse/bind messages at all, just execute messages, because
there's no way to avoid serious redundancy/log-bloat/confusion otherwise.
Another attractive aspect is that we could merge duration logging with
statement reporting rather than having two separate log messages come
out in some cases.

The other thing we'd want to do is try a bit harder to ensure that
debug_query_string is always valid, so that one could be certain that
the combination of log_min_error_statement and log_statement = ALL
tells all.  bind/execute messages don't currently bother to set
debug_query_string properly, but I think that could be fixed easily.

Comments?

regards, tom lane

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

Re: [HACKERS] Statements with syntax errors are not logged

[tomas]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Thu, Oct 19, 2006 at 04:28:17PM +0200, Peter Eisentraut wrote:
> When setting log_statement = 'all', statements that fail parsing are not
> logged.  For example:

[...]

HA! This one has bitten me just today :-)

The problem was a faulty client sending garbage and PostgreSQL
complaining (rightfully) about bad UTF-8.

But I quicly understood the problem and managed to debug otherwise (the
raised eyebrows didn't last for long ;-)

So I don't know whether it's worth to add complexity for this.

Regards
- -- tomás
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFN5dUBcgs9XrR2kYRAqITAJ4sRSqDS0vkn56UeODJlLEZWgugiwCfY8vO
H70VJ3XRbl9PME1s8HRefHA=
=JcT2
-END PGP SIGNATURE-


---(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: [HACKERS] Additional stats for Relations

[Jim C. Nasby]

On Thu, Oct 19, 2006 at 04:10:46PM +0530, NikhilS wrote:
> Hi Jim,
> 
> On 10/18/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >Also how many times a relation has been vacuumed (which puts all the
> >other numbers in more perspective... good catch Simon). And I think
> >number of pages that could not be added to the FSM would also be
> >extremely valuable.
> 
> 
> By the above, do you mean the number of pages that could not be added to the
> FSM because they had freespace which was less than the threshold for this
> particular relation?

Yes... but... :)

We want to ignore pages that have less than the average request size,
because vacuum will never try and put them in the FSM anyway. We only
care about pages that were dropped because MaxFSMPages was less than
DesiredFSMPages (see freespace.c for more info).

It would also be useful to keep track of what relations have been bumped
out of the FSM (or never got recorded, though I'm not sure if that's
possible) because we've run into the MaxFSMRelations limit.

BTW, if we add these counters we'll be up to 7 stats dealing with vacuum
and analyze, and NikhilS has a patch we're finalizing that would add 3
more. Right now there's 4 slated to go into pg_stat_* in 8.2, but maybe
we should have a separate view for vacuum/FSM statistics?
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [HACKERS] Statements with syntax errors are not logged

[Peter Eisentraut]

Tom Lane wrote:
> The 'mod' and 'ddl' settings obviously can't be handled until after
> basic parsing.  We could create a completely separate code path for
> 'all' but I'm not sure I see the point.

The users are evidently expecting that "log all statements" means to log 
all statements issued by the client.  These implementation details are 
not obvious to a user.

> To some extent the logging 
> settings are only meant to capture successfully executed statements

Then it should be changed to log *only* successfully executed statements 
and explicitly documented as such.

> (eg, you don't get duration for a failed command, either) --- which
> should be OK since failed ones won't change the database state.

I don't think tracking changes to the database state is the only 
motivation for wanting to see all statements.

-- 
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: [HACKERS] Statements with syntax errors are not logged

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> When setting log_statement = 'all', statements that fail parsing are not
> logged.
> Is that intentional?

The 'mod' and 'ddl' settings obviously can't be handled until after
basic parsing.  We could create a completely separate code path for
'all' but I'm not sure I see the point.  To some extent the logging
settings are only meant to capture successfully executed statements
(eg, you don't get duration for a failed command, either) --- which
should be OK since failed ones won't change the database state.
As you say, log_min_error_statement is orthogonal.

regards, tom lane

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

[HACKERS] Statements with syntax errors are not logged

[Peter Eisentraut]

When setting log_statement = 'all', statements that fail parsing are not
logged.  For example:

LOG:  connection received: host=[local]
LOG:  connection authorized: user=peter database=peter
LOG:  statement: select * from pg_class;
LOG:  duration: 19.084 ms
### here a log entry is missing
ERROR:  syntax error at or near "foo" at character 1
### The following shows that post-parser errors are handled correctly.
LOG:  statement: select * from pg_class where reltype = true;
ERROR:  operator does not exist: oid = boolean at character 38
HINT:  No operator matches the given name and argument type(s). You may need to 
add explicit type casts.

Is that intentional?

(This is in 8.1 and 8.2 at least.)

(Yes, I realize there is log_min_error_statement, but that is an orthogonal
feature.)

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

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

Re: [HACKERS] Fwd: New CRC algorithm: Slicing by 8

[Andrew Dunstan]

Andrew Dunstan wrote:
If you have a patch, send it to -hackers. 



I mean -patches of course. Sorry.

cheers

andrew

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

Re: [HACKERS] Fwd: New CRC algorithm: Slicing by 8

[Andrew Dunstan]

Gurjeet Singh wrote:
   

Can someone let us all know what is the limit on the attachments 
sent to the -hackers list?





Generally it's probably best not to send attachments to -hackers at all. 
If you have a patch, send it to -hackers. Or you can publish it 
somewhere on the web and post a link to it (this is one of the things I 
think a developers' wiki is actually useful for).


cheers

andrew

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

[HACKERS] Fwd: New CRC algorithm: Slicing by 8

[Gurjeet Singh]

    Resending... The previous 3 attempt haven't succeeded, probably because of size restrictions; so sending in two parts.    This is part 2; unfortunately, the size of the gzipped file is still a bit big (26 KB), so I have uploaded it to 
http://www.geocities.com/gurjeet79/crc_sb8_test.tar.gz    Please download and test it from there.    Can someone let us all know what is the limit on the attachments sent to the -hackers list?
Best regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
On 10/18/06, Gurjeet Singh <

[EMAIL PROTECTED]> wrote:
Hi all,    Please refer to the following link for a new algorithm to calculate CRC, developed by Intel.
    http://www.intel.com/technology/magazine/communications/slicing-by-8-0306.htm
    Please find attached the patch (pg_crc_sb8_4.diff.gz), that implements this algorithm (originally obtained from 

http://sourceforge.net/projects/slicing-by-8
).    I tested it using pgbench, and found an average improvement of about 15 tps (291 vs. 307 tps) when running with a scaling-factor of 10, number of clients: 10, and 1000 transactions per client (I don't remember these pgbench params exactly; it's been quite a while now).
    The author of the algo says that it works three times faster (theoretically) than the conventional CRC calculation methods, but the tps improvements didn't reflect that; so, to prove the speed, I extracted the source files (both, PG and SB8) and made a saparate project (
test.tar.gz).    The resulting binary's comand format is:    [  [  [  ] ] ]
  algo_name : PG or SB8 or BOTH (just the first char will also do!)
  scaling_factor: the multiple to use when creating the list of buffer blocks.


  block_size    : buffer size (in KBs); default is 4.  list_size : number of data blocks to create for the test; default is 100.
  So the command : a.out p 3 1024 300


   will test the PG's algo over 900 blocks of 1 MB each.    The test.sh script is a wrapper around this binary, to test each of PG and SB8, and both algos simultaneously, three times each. I performed two tests using this script to prove SB8's effectiveness on large and small sized data blocks:
    sh test.sh 1 1024 800 -- 800 blocks of 1 MB each; effective data size 800 MB


    sh test.sh 800 1 1024 -- 800 * 1024 blocks of 1 KB each; effective data size 800 MB    The new algo shined in both test configurations. It performs 3 to 4 times better than regular CRC algo in both types of tests. The results are in the files results_1_1024_800.out and results_800_1_1024.out, respectively.
    To test it yourself, extract all the files from test.tar.gz, and issue the following commands (assuming bash):sh make.sh


sh test.sh 1 1024 800sh test.sh 800 1 1024(Note: you would like to reduce the effective data size on a Windows box. I used 500 MB on windows+MinGW. I tested with these params since I have only 1 GB of RAM, and increasing the data size beyond these numbers caused the memory contents to be spilled over into swap-space/pagefile, and this disk I/O causes a severe distortion in results.)
   The tests were performed on Fedora Core 5 and MinGW on WinXP Professional.    If possible, people should test it on different platforms, so as to ensure that it doesn't perform any worse than older implementation on any supported platform (please post the results, if you do test it). Also, recommendations for a better (than pgbench) benhmark are welcome, so that we can show the improvement when it is used as a part of PG.
Best regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com

Re: [HACKERS] Additional stats for Relations

[NikhilS]

Hi Jim, On 10/18/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
Also how many times a relation has been vacuumed (which puts all theother numbers in more perspective... good catch Simon). And I thinknumber of pages that could not be added to the FSM would also beextremely valuable.

By the above, do you mean the number of pages that could not be added
to the FSM because they had freespace which was less than the threshold
for this particular relation?

Regards,
Nikhils
EnterpriseDB   http://www.enterprisedb.com
 On Wed, Oct 18, 2006 at 11:27:39AM +0530, NikhilS wrote:> Hi,>
> So:> heap_blks_reused (with Jim's semantics), heap_blks_extend,> heap_blks_truncate are the "interesting" stats? Will try to work up a patch> for this.>> Regards,> Nikhils
> EnterpriseDB   http://www.enterprisedb.com> On 10/15/06, Simon Riggs <[EMAIL PROTECTED]> wrote:> >
> >On Sat, 2006-10-14 at 11:32 +0530, NikhilS wrote:> >> >> On 10/13/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:> >> >> >> I'm also not sure if this metric is what you actually want,
> >> since a> >> single page can be returned many times from the FSM even> >> between> >> vacuums. Tracking how many pages for a relation have been put
> >> into the> >> FSM might be more useful...> >>> >> > >> Pages might be put into the FSM, but by this metric don't we get the
> >> actual usage of the pages from the FSM? Agreed a single page can be> >> returned multiple times, but since it serves a new tuple, shouldn't we> >> track it?> >> 
> >> >This makes sense for indexes, but only makes sense for heaps when we> >know that the backend will keep re-accessing the block until it is full> >- so only of interest in steady-state workloads.
> >> >IMHO Jim's proposal makes more sense for general use.> >> >> > heap_blks_extend: The number of times file extend was> >> invoked on the> >> > relation
> >> >Sounds good> >> >> > heap_blks_truncate: The total number of blocks that have> >> been truncated due> >> > to vacuum activity 
e.g.> >> >Sounds good> >> >> > As an addendum to the truncate stats above, we can also have> >> the additional> >> > following stats:
> >> >> >> > heap_blks_maxtruncate: The max block of buffers truncated in> >> one go> >> >> >> > heap_blks_ntruncate: The number of times truncate was called
> >> on this> >> > relation> >> >Those last 2 sound too complex for normal use and ntruncate is most> >likely the same as number of vacuums anyway. Hmmm...Perhaps nvacuums is
> >a more interesting metric? We've got last vacuum date, but no indication> >of how frequently a vacuum has run.> >> >> Do you have a use-case for this info? I can see where it might
> >> be neat> >> to know, but I'm not sure how you'd actually use it in the> >> real world.> >>> >> > >> The use-case according to me is that these stats help prove the
> >> effectiveness of autovacuum/vacuum operations. By varying some autovac> >> guc variables, and doing subsequent (pgbench e.g.) runs, one can find> >> out the optimum values for these variables using these stats.
> >> > >> >This should be useful for tuning space allocation/deallocation. If we> >get this patch in early it should help get feedback on this area.> >
> >--> >  Simon Riggs> >  EnterpriseDB   http://www.enterprisedb.com> >> >>  --> All the world's a stage, and most of us are desperately unrehearsed.
--Jim
Nasby[EMAIL PROTECTED]EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)
-- All the world's a stage, and most of us are desperately unrehearsed.

Re: [HACKERS] pg_internal.init is hazardous to your health

[Heikki Linnakangas]

Simon Riggs wrote:

RelationCacheInitFileInvalidate() is also called on each
FinishPreparedTransaction(). 


It's only called if the prepared transaction invalidated the init file.


If that is called 100% of the time, then we
can skip writing an additional record for prepared transactions by
triggering the removal of pg_internal.init when we see a
XLOG_XACT_COMMIT_PREPARED during replay. 
Not sure whether we need to do that, Heikki? Anyone?

I'm guessing no, but it seems sensible to check.


If you write the WAL record in RelationCacheInitFileInvalidate(true), 
that's enough. No extra handling for prepared transactions is needed.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---(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: [HACKERS] New CRC algorithm: Slicing by 8

[Gurjeet Singh]

Resending... The previous 3 attempt haven't succeeded, probably because of size restrictions; so sending in two parts.On 10/18/06, Gurjeet Singh <
[EMAIL PROTECTED]> wrote:Hi all,    Please refer to the following link for a new algorithm to calculate CRC, developed by Intel.
    http://www.intel.com/technology/magazine/communications/slicing-by-8-0306.htm
    Please find attached the patch (pg_crc_sb8_4.diff.gz), that implements this algorithm (originally obtained from 
http://sourceforge.net/projects/slicing-by-8
).    I tested it using pgbench, and found an average improvement of about 15 tps (291 vs. 307 tps) when running with a scaling-factor of 10, number of clients: 10, and 1000 transactions per client (I don't remember these pgbench params exactly; it's been quite a while now).
    The author of the algo says that it works three times faster (theoretically) than the conventional CRC calculation methods, but the tps improvements didn't reflect that; so, to prove the speed, I extracted the source files (both, PG and SB8) and made a saparate project (
test.tar.gz).    The resulting binary's comand format is:    [  [  [  ] ] ]
  algo_name : PG or SB8 or BOTH (just the first char will also do!)
  scaling_factor: the multiple to use when creating the list of buffer blocks.

  block_size    : buffer size (in KBs); default is 4.  list_size : number of data blocks to create for the test; default is 100.
  So the command : a.out p 3 1024 300

   will test the PG's algo over 900 blocks of 1 MB each.    The test.sh script is a wrapper around this binary, to test each of PG and SB8, and both algos simultaneously, three times each. I performed two tests using this script to prove SB8's effectiveness on large and small sized data blocks:
    sh test.sh 1 1024 800 -- 800 blocks of 1 MB each; effective data size 800 MB

    sh test.sh 800 1 1024 -- 800 * 1024 blocks of 1 KB each; effective data size 800 MB    The new algo shined in both test configurations. It performs 3 to 4 times better than regular CRC algo in both types of tests. The results are in the files results_1_1024_800.out and results_800_1_1024.out, respectively.
    To test it yourself, extract all the files from test.tar.gz, and issue the following commands (assuming bash):sh make.sh

sh test.sh 1 1024 800sh test.sh 800 1 1024(Note: you would like to reduce the effective data size on a Windows box. I used 500 MB on windows+MinGW. I tested with these params since I have only 1 GB of RAM, and increasing the data size beyond these numbers caused the memory contents to be spilled over into swap-space/pagefile, and this disk I/O causes a severe distortion in results.)
   The tests were performed on Fedora Core 5 and MinGW on WinXP Professional.    If possible, people should test it on different platforms, so as to ensure that it doesn't perform any worse than older implementation on any supported platform (please post the results, if you do test it). Also, recommendations for a better (than pgbench) benhmark are welcome, so that we can show the improvement when it is used as a part of PG.
Best regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com

-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com


pg_crc_sb8_4.diff.gz
Description: GNU Zip compressed data

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

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