Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) && (is it an acceptable idea to build index on Flash Disk)

2008-03-25 Thread mx
>
> On Tue, 25 Mar 2008, mx wrote:
>
> > The atom unit of flash is page(512~2048byte typically). Page are
> > organized into blocks, typically of 32 or 64 pages. All read write and
> > write operations happen at page granularity, but erase operations happen
> > at block granularity.
>
> You made a subtle switch here I wanted to emphasise.  Your original
> message suggested flash is an increasingly important storage mechanism
> because flash devices like SSD drives are going to be more popular in the
> future; that is true.  However, what you're describing is something more
> like how flash is used in raw embedded systems applications.  The kinds of
> SSD drives that are becoming popular for database use abstract away all of
> this low-level block mess and hide it with approaches like sophisticated
> write-leveling algorithms.
>
Maybe I gives too many detailed about raw flash devices.
In fact, what I want to show is the asymmetric speed of read and write.
Any flash devices including SSD have such a characteristic.
For a sumsung 64G SSD PATA IDE 2.5,maximum Squential Read is 57MB/s,
while maximum sequential Read is 38MB/s according the product datasheet.

Anyway, in the eyes performance of outside, write is more expensive than read.
Some strategy of trade read for write may be considered.
So, the asymmetric speed of read and write make it is still valuable
to do some work on SSD.

>You don't (and possibly can't) even know what
> the underlying structure is like.  And even if you did, the fact that
> there's a always a regular operating system and filesystem underneath
> PostgreSQL writes will make it undertain the writes are only touching the
> tiny portion of flash you want to target anyway.  They may write a whole
> OS block regardless.

Yeah, you're right. This is the most confused thing. I wish my thesis
work is  independent of low level flash device. But it's very hard in
fact, just as what you said.

-- 
Have a good day;-)
Best Regards,
Xiao Meng

━
Data and Knowledge Engineering Research Center,CS&T
Harbin Institute of Technology, Harbin, China
Gtalk: [EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
Blog: http://xiaomeng.yo2.cn

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


Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) && (is it an acceptable idea to build index on Flash Disk)

2008-03-25 Thread mx
Thank you for all of your advices.
I think you're right. I should be more realistic. There are so many
work to do if I want to do some work on Flash disk. It's too difficult
to complete the task only in a summer. Obviously, It's not an
appropriate project idea for GSoC anyway.
Maybe I'll do it in the future after I've done enough work according
to my theis work.

So, I finally decide to focus on the project idea of improving hash
index now. It's more valuable , and also challenging.

Any suggestion about the project idea of improving hash index?

-- 
Have a good day;-)
Best Regards,
Xiao Meng

━
Data and Knowledge Engineering Research Center,CS&T
Harbin Institute of Technology, Harbin, Heilongjiang, China
Gtalk: [EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
Blog: http://xiaomeng.yo2.cn

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


Re: [HACKERS] Integer datetimes

2008-03-25 Thread Zdenek Kotala

Tom Lane napsal(a):

Zdenek Kotala <[EMAIL PROTECTED]> writes:

Tom Lane napsal(a):

This is not happening, at least not without 100 times more work than
anyone has shown willingness to put into the issue.



I understand your arguments, but it is important for in-place upgrade.


No, it is not, you merely need to be sure the new version is configured
the same as the old ... which is a requirement anyway.


It depends. For example, currently postgreSQL allows only collation setting per 
database, but somebody will develop collation per database or column. After that 
the situation will be different. Upgrade have to transfer collation from control 
file to catalog and so on. Configuration will be different but there will be 
possible transition.


Probably all packager will delivery 8.4 and newer with integer datetime, but 
people who will use in-place upgrade they will not use official package. I think 
it is not good idea.


Zdenek

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


Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) && (is it an acceptable idea to build index on Flash Disk)

2008-03-25 Thread Zeugswetter Andreas OSB SD
> So, I finally decide to focus on the project idea of improving hash
> index now. It's more valuable , and also challenging.
> 
> Any suggestion about the project idea of improving hash index?

Imho one thing to look into is the storage. I do not see any real value
in storing the key itself (especially longer keys) in the hash buckets.
Instead store the hash value only (or not even that) and mark the index
lossy (recheck the key in the heap).

Andreas

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


Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers

2008-03-25 Thread Russell Smith

Tom Lane wrote:

Russell Smith <[EMAIL PROTECTED]> writes:
  
As an attempt at a first PostgreSQL patch, I'd like to see if I can do 
anything about this issue.



Trying that as a first patch is a recipe for failure... the short answer
is that no one can think of a solution that will be generally acceptable.

regards, tom lane
  
What makes this change particularly challenging?  The fact that nobody 
has agreed on how it should work, or the actual coding?


regards

Russell

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


Re: [HACKERS] Minor bug in src/port/rint.c

2008-03-25 Thread Alvaro Herrera
Tom Lane wrote:

> The proper wording of this item is
> 
>   * Find a correct rint() substitute on Windows

Fixed.

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

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


Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers

2008-03-25 Thread Andrew Dunstan



Russell Smith wrote:

Tom Lane wrote:

Russell Smith <[EMAIL PROTECTED]> writes:
 
As an attempt at a first PostgreSQL patch, I'd like to see if I can 
do anything about this issue.



Trying that as a first patch is a recipe for failure... the short answer
is that no one can think of a solution that will be generally 
acceptable.


regards, tom lane
  
What makes this change particularly challenging?  The fact that nobody 
has agreed on how it should work, or the actual coding?





The widespread code impact of any change is a strong indicator against 
doing this as a first patch. But newbie or not, as Tom says, nobody 
should be starting to design, let alone cut code, until the actual 
desired behaviour is agreed.


cheers

andrew

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


Re: [HACKERS] [GSoC] (Is it OK to choose items without % mark in theToDoList) && (is it an acceptable idea to build index on Flash Disk)

2008-03-25 Thread Kenneth Marshall
On Tue, Mar 25, 2008 at 01:46:51PM +0100, Zeugswetter Andreas OSB SD wrote:
> > So, I finally decide to focus on the project idea of improving hash
> > index now. It's more valuable , and also challenging.
> > 
> > Any suggestion about the project idea of improving hash index?
> 
> Imho one thing to look into is the storage. I do not see any real value
> in storing the key itself (especially longer keys) in the hash buckets.
> Instead store the hash value only (or not even that) and mark the index
> lossy (recheck the key in the heap).
> 
> Andreas
> 

Meng,

I had started a thread on the hackers mailing list about improving
the hash index in PostgreSQL. You can look through it for some of
the ideas that were suggested. The first one is to replace the storage
of the key values in the index with the hash of the key values instead.
This can leverage the lossy index heap re-check code that is already in
the database. Neil Conway had posted a patch doing this with an old
version of PostgreSQL. My coding skills are a bit rusty and my job has
kept me from making much progress towards this. Anyway, please take
a look at the hash index thread in hackers. It starts with:

http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php

Let me know what you think?

Cheers,
Ken Marshall

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Decibel!

On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
I'm trying to write a version of the MIN aggregate for values of  
RECORD
type.  I'm somewhat stuck on getting type information about the  
argument
out, I can determine how many attributes it's got but I can't seem  
to do
any better than that.  Does anyone have any good pointers into the  
code

for places to help me understand what's happening?

The reason for doing this is mainly because I think it'd be nicer  
to be

doing things like:

  SELECT i, (MIN((j,k))).k
  FROM tbl
  GROUP BY i;


How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ?


instead of:

  SELECT DISTINCT ON (i) i, k
  FROM tbl
  ORDER BY i,j,k;

Which as far as I can tell should produce identical results, except  
the
first has cleaner semantics.  It also allows you to combine MIN and  
MAX
in the same query, giving the value of k for the smallest and  
largest j
in this example--requiring two queries if it was done using the  
DISTINCT

ON method.


I don't see how min(record) even allows for that.

I'm not saying that min/avg/max/etc(RECORD) wouldn't be useful; I'm  
just failing to see the use in these examples.

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828




smime.p7s
Description: S/MIME cryptographic signature


[HACKERS] FETCH FIRST

2008-03-25 Thread Simon Riggs
SQL200n specifies a new command similar to our LIMIT

FETCH { FIRST | NEXT } n { ROW | ROWS } ONLY

and also that the OFFSET command can have an optional suffix

{ ROW | ROWS }

Seems like a % project for the TODO list

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


[HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Simon Riggs
SQL200n specifies a new qualifier on a TRUNCATE command

TRUNCATE TABLE foo
[ CONTINUE IDENTITY | RESTART IDENTITY ]

CONTINUE IDENTITY is the default and does nothing, like now.

RESTART IDENTITY will reset the SERIAL sequences back to the original
start value.

Seems like a % project for the TODO list

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


Re: [HACKERS] FETCH FIRST

2008-03-25 Thread Bruce Momjian
Simon Riggs wrote:
> SQL200n specifies a new command similar to our LIMIT
> 
> FETCH { FIRST | NEXT } n { ROW | ROWS } ONLY
> 
> and also that the OFFSET command can have an optional suffix
> 
> { ROW | ROWS }
> 
> Seems like a % project for the TODO list

Where does this FETCH appear in a SELECT?

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] postgresql in FreeBSD jails: proposal

2008-03-25 Thread Alvaro Herrera
Bruce Momjian wrote:
> 
> Added to TODO:
> 
> * Improve detection of shared memory segments being used by other
>   FreeBSD jails
> 
>   http://archives.postgresql.org/pgsql-hackers/2008-01/msg00656.php

There's a bit more than that to it -- see
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00673.php

In short, it's not just a FreeBSD issue, but something a bit more
general.

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

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


Re: [HACKERS] [pgsql-www] New email list for emergency communications

2008-03-25 Thread Andrew Sullivan
On Mon, Mar 24, 2008 at 06:39:25PM -0400, Andrew Dunstan wrote:

> perusing a mailbox again. We have unfortunately been badly underprepared 
> for this.

Surely that there is an emerging consensus to that effect means that it's
not as unfortunate as it might be?  I seem to recall the original
announcement suggesting this was an experiment.  I wouldn't expect the first
couple rounds to go without a hitch; as long as there is procedural
improvement the next time, that's a good thing, right?

A


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


Re: [HACKERS] FETCH FIRST

2008-03-25 Thread Simon Riggs
On Tue, 2008-03-25 at 10:45 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > SQL200n specifies a new command similar to our LIMIT
> > 
> > FETCH { FIRST | NEXT } n { ROW | ROWS } ONLY
> > 
> > and also that the OFFSET command can have an optional suffix
> > 
> > { ROW | ROWS }
> > 
> > Seems like a % project for the TODO list
> 
> Where does this FETCH appear in a SELECT?

It's supposed to go *after* the OFFSET clause in a query, but making it
a simple synonym for LIMIT seems most sensible.

It's also allowed as part of the WITH clause (that we don't yet have).

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


[HACKERS] Images in PostgreSQL

2008-03-25 Thread
Hi all,

Does anybody know if is it possible to store images in Postgre? If it is
possible which data type should I use?

Thanks in advance,

--
R&D | Alea Management & Technology Solutions


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


Re: [HACKERS] Images in PostgreSQL

2008-03-25 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tue, 25 Mar 2008 12:06:09 -0300 (BRT)
r&[EMAIL PROTECTED] wrote:

> Hi all,
> 
> Does anybody know if is it possible to store images in Postgre? If it
> is possible which data type should I use?

There are two ways to do it:

http://www.postgresql.org/docs/8.3/static/datatype-binary.html
http://www.postgresql.org/docs/8.3/static/lo-funcs.html

Sincerely,

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH6RmiATb/zqfZUUQRAhsOAJ9dDzb47p2n2hnIInZhhgXdT66TagCfYi2t
emUwHcomGeBzHMx0miHieD0=
=6bdP
-END PGP SIGNATURE-

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


[HACKERS] HP-UX 11.11 PostgreSQL 8.3.1 - psql core dumps - Unresolved symbol: tgetent

2008-03-25 Thread Mihai Criveti
I've compiled PostgreSQL 8.3.1 using gcc 4.2.3 on HP-UX 11i v1, and all went
well. PostgreSQL works and everything, except for psql client:

$ ldd /usr/local/pgsql/bin/psql
 =>
/usr/lib/libc.2 =>  /usr/lib/libc.2
/usr/lib/libdld.2 =>/usr/lib/libdld.2
/usr/lib/libc.2 =>  /usr/lib/libc.2
/usr/lib/libm.2 =>  /usr/lib/libm.2
/usr/local/lib/libreadline.sl =>
/usr/local/lib/libreadline.sl
/usr/local/lib/libz.sl =>   /usr/local/lib/libz.sl
/usr/lib/libxnet.2 =>   /usr/lib/libxnet.2
/usr/lib/libxti.2 =>/usr/lib/libxti.2
../../../src/interfaces/libpq/libpq.sl.5 =>
/usr/local/pgsql/lib/libpq.sl.5

$ /usr/local/pgsql/bin/psql
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

/usr/lib/dld.sl: Unresolved symbol: tgetent (code)  from
/usr/local/lib/libreadline.sl
LOG:  unexpected EOF on client connection
ABORT instruction (core dumped)


# swlist | grep readline
  readline  5.2readline

$ file /usr/local/lib/libreadline.sl
   /usr/local/lib/libreadline.sl:  PA-RISC2.0 shared library


As you can see, the older version of psql (7.4.2) works fine:

$ psql
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help on internal slash commands
   \g or terminate with semicolon to execute query
   \q to quit


$ psql -l
List of databases
   Name|  Owner   | Encoding
---+--+---
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(3 rows)

$ /usr/local/pgsql/bin/createdb test
$ psql -l
List of databases
   Name|  Owner   | Encoding
---+--+---
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 test  | postgres | SQL_ASCII
(4 rows)



If needed, I can attach a core dump.

Regards, cmihai

--
Criveti Mihai
http://unixsadm.blogspot.com


Re: [HACKERS] Images in PostgreSQL

2008-03-25 Thread Albert Cervera i Areny
A Dimarts 25 Març 2008, r&[EMAIL PROTECTED] va escriure:
> Hi all,
>
> Does anybody know if is it possible to store images in Postgre? If it is
> possible which data type should I use?
>

Yes it's possible, you can use BYTEA for this, for example. But please, this 
kind of questions should go to users mailing list. hackers is for people 
working on developing PostgreSQL itself.

> Thanks in advance,
>
> --
> R&D | Alea Management & Technology Solutions



-- 
Albert Cervera i Areny
http://www.NaN-tic.com

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


Re: [HACKERS] TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers

2008-03-25 Thread Tom Lane
Russell Smith <[EMAIL PROTECTED]> writes:
> What makes this change particularly challenging?  The fact that nobody 
> has agreed on how it should work, or the actual coding?

Getting the right design is definitely challenging.  The coding might or
might not be, depending on your design ...

regards, tom lane

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


Re: [HACKERS] FETCH FIRST

2008-03-25 Thread Tom Lane
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Tue, 2008-03-25 at 10:45 -0400, Bruce Momjian wrote:
>> Where does this FETCH appear in a SELECT?

> It's supposed to go *after* the OFFSET clause in a query, but making it
> a simple synonym for LIMIT seems most sensible.

Huh?  OFFSET isn't in standard SQL either.  Are you sure you're not
confusing FETCH-from-a-cursor with SELECT?

regards, tom lane

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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Tom Lane
Simon Riggs <[EMAIL PROTECTED]> writes:
> SQL200n specifies a new qualifier on a TRUNCATE command
> TRUNCATE TABLE foo
>   [ CONTINUE IDENTITY | RESTART IDENTITY ]

> CONTINUE IDENTITY is the default and does nothing, like now.

> RESTART IDENTITY will reset the SERIAL sequences back to the original
> start value.

> Seems like a % project for the TODO list

Seems like copying syntax from a *draft* standard is a bit premature,
especially when the amount of functionality added is nil.

regards, tom lane

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


Re: [HACKERS] [pgsql-www] New email list for emergency communications

2008-03-25 Thread Tom Lane
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> On Mon, Mar 24, 2008 at 06:39:25PM -0400, Andrew Dunstan wrote:
>> perusing a mailbox again. We have unfortunately been badly underprepared 
>> for this.

> Surely that there is an emerging consensus to that effect means that it's
> not as unfortunate as it might be?  I seem to recall the original
> announcement suggesting this was an experiment.  I wouldn't expect the first
> couple rounds to go without a hitch; as long as there is procedural
> improvement the next time, that's a good thing, right?

Yeah, we expected to have glitches.  I think we now have a much better
idea what sort of status-tracking support we need for future fests.

regards, tom lane

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


Re: [HACKERS] HP-UX 11.11 PostgreSQL 8.3.1 - psql core dumps - Unresolved symbol: tgetent

2008-03-25 Thread Martijn van Oosterhout
On Tue, Mar 25, 2008 at 04:54:59PM +0200, Mihai Criveti wrote:
> I've compiled PostgreSQL 8.3.1 using gcc 4.2.3 on HP-UX 11i v1, and all went
> well. PostgreSQL works and everything, except for psql client:
> 
> $ /usr/local/pgsql/bin/psql
> Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
> 
> Type:  \copyright for distribution terms
>\h for help with SQL commands
>\? for help with psql commands
>\g or terminate with semicolon to execute query
>\q to quit
> 
> /usr/lib/dld.sl: Unresolved symbol: tgetent (code)  from
> /usr/local/lib/libreadline.sl

tgetent is from curses/ncurses. When readline is built it either takes
a system version or a builtin one. Evidently it thinks there ought to
be a system version somewhere. In any case it looks like your readline
library uses curses but doesn't actually link to it, which is bad.

You can probably work around it by adding -lcurses to the psql link
line, but fixing readline would be better.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature


Re: [HACKERS] HP-UX 11.11 PostgreSQL 8.3.1 - psql core dumps - Unresolved symbol: tgetent

2008-03-25 Thread Tom Lane
"Mihai Criveti" <[EMAIL PROTECTED]> writes:
> I've compiled PostgreSQL 8.3.1 using gcc 4.2.3 on HP-UX 11i v1, and all went
> well. PostgreSQL works and everything, except for psql client:

> /usr/lib/dld.sl: Unresolved symbol: tgetent (code)  from
> /usr/local/lib/libreadline.sl

Kinda looks like your libreadline is broken.

> As you can see, the older version of psql (7.4.2) works fine:

Maybe that was built without readline support?

regards, tom lane

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


Re: [HACKERS] FETCH FIRST

2008-03-25 Thread Simon Riggs
On Tue, 2008-03-25 at 11:47 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Tue, 2008-03-25 at 10:45 -0400, Bruce Momjian wrote:
> >> Where does this FETCH appear in a SELECT?
> 
> > It's supposed to go *after* the OFFSET clause in a query, but making it
> > a simple synonym for LIMIT seems most sensible.
> 
> Huh?  OFFSET isn't in standard SQL either. 

Tis now.

>  Are you sure you're not
> confusing FETCH-from-a-cursor with SELECT?

Quite sure.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Simon Riggs
On Tue, 2008-03-25 at 11:48 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > SQL200n specifies a new qualifier on a TRUNCATE command
> > TRUNCATE TABLE foo
> > [ CONTINUE IDENTITY | RESTART IDENTITY ]
> 
> > CONTINUE IDENTITY is the default and does nothing, like now.
> 
> > RESTART IDENTITY will reset the SERIAL sequences back to the original
> > start value.
> 
> > Seems like a % project for the TODO list
> 
> Seems like copying syntax from a *draft* standard is a bit premature,
> especially when the amount of functionality added is nil.

It's at the final yes-or-no vote stage. Seems unlikely to be "no" to me,
and it would be good to be seen to be proactive on standards support.

The added functionality in this case isn't nil.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Steve Crawford

Simon Riggs wrote:

RESTART IDENTITY will reset the SERIAL sequences back to the original
start value.
  

Assuming this feature were to be added

In cases where the same sequence has been used across multiple tables, 
what will be the appropriate response when a user attempts to TRUNCATE 
one of those tables with RESTART IDENTITY?


Cheers,
Steve


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


[HACKERS] GiST opclass and varlena

2008-03-25 Thread Dimitri Fontaine
Hi,

I'm trying to code a GiST opclass to index prefix searches (select ... from t 
where t.prefix @> query), now using a prefix_range datatype. This datatype is 
a varlena one, and storing it to disk and indexing it with BTrees work ok, 
but I'm failing to have my GiST opclass working, here's the problem:

postgres=# create index idx_prefix on ranges using gist(prefix 
gist_prefix_range_ops);
NOTICE:  gpr_picksplit(): entryvec->n= 234 maxoff= 232 l= 176 r=  56 l+r= 232 
unionL='01[0-7]' unionR='01[4-7]'
NOTICE:  gpr_picksplit(): v->spl_ldatum='01[0-7]' v->spl_rdatum='01[4-7]'
ERROR:  invalid memory alloc request size 3049878020

The code is available at pgfoundry here:
  http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/

The previous support for prefixes as text is still there (but will get 
deprecated soon --- or so I hope), and the new datatype and its usage not 
well commented nor documented currenlty. If this show up as a requirement to 
get your attention, please state it and I'll work on documenting prefix_range 
first.

I'm looking for some help on how to resolve the shown index creation problem, 
which I think is related to how I give data to GiST in its spl_ldatum and 
spl_rdatum from the user defined picksplit() method, lines 1101 and 1102 in 
prefix.c (version 1.26).

Regards,
-- 
dim


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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Simon Riggs
On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote:
> Simon Riggs wrote:
> > RESTART IDENTITY will reset the SERIAL sequences back to the original
> > start value.
> >   
> Assuming this feature were to be added
> 
> In cases where the same sequence has been used across multiple tables, 
> what will be the appropriate response when a user attempts to TRUNCATE 
> one of those tables with RESTART IDENTITY?

Well, I'm suggesting it as a TODO item, based on the standard. It would
be for whoever took this up to unravel that.

Since that's a weak answer, I'd say it should only reset sequences that
have been placed there automatically through the use of SERIAL or
BIGSERIAL datatypes.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


[HACKERS] partial dump of patch queue to wiki

2008-03-25 Thread Alvaro Herrera
Hi,

I created a page for our current commitfest:

http://wiki.postgresql.org/wiki/CommitFest:March

Not all patches are there yet.  I only added the latest version of each
patch.  I'll continue after lunch.

I also created one for the next commitfest:

http://wiki.postgresql.org/wiki/CommitFest:May

HTH

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

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Sam Mason
On Mon, Mar 24, 2008 at 05:27:04PM -0500, Decibel! wrote:
> On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
> >  SELECT i, (MIN((j,k))).k
> >  FROM tbl
> >  GROUP BY i;
> 
> How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ?

Because I want the value of k associated with the minimum value of j.
For example, if I have data looking like:

  i  j  k
  1  3  7
  1  4  8
  2  5  10
  2  6  9

I want to get this out:

  i  k
  1  7
  2  10

I would get this if I used the DISTINCT ON or if MIN was valid over
records.  With your code I'd get this:

  i  k
  1  7
  2  9

> I'm not saying that min/avg/max/etc(RECORD) wouldn't be useful; 

AVG wouldn't work, because it relies on treating it's parameter as a
numeric field over which summation and division are valid operations.
MIN/MAX just relies on there being a (total) ordering operator available
and with PG there pretty much always is.

> I'm just failing to see the use in these examples.

Did the example above make things any clearer?


I've also just realised that PG's current handling of NULLs inside
records is also going to cause problems.  The main problem seems to be
that the IS NULL operator isn't consistent with comparison operators.
For example:

  (1,NULL) IS NULL  --> FALSE
  (1,NULL) = (1,NULL)   --> NULL

I'm not sure if it's just my intuition is off, or whether there is an
invariant (e.g. a comparison returns NULL if-and-only-if either side
evaluate TRUE to IS NULL) that's being broken.


Thanks,
  Sam

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


Re: [HACKERS] Text <-> C string

2008-03-25 Thread Tom Lane
I've been working some more on Brendan Jurd's patch to simplify text <->
C string conversions.  It seems we have consensus on the names for the
base operations:

extern text *cstring_to_text(const char *s);
extern char *text_to_cstring(const text *t);

Brendan's patch also included "cstring_text_limit(const char *s, int len)"
which was defined as copying Min(len, strlen(s)) bytes.  I didn't find
this to be particularly useful.  In the first place, all potential
callers are passing the exact desired length, so the strlen() call is
just a waste of cycles.  In the second place, at least some callers pass
text that is not embedded in a known-to-be-null-terminated string (it
could be a section of a text datum instead); which means there is a
nonzero chance of the strlen running off the end of memory and dumping
core.  So I propose instead

extern text *cstring_to_text_with_len(const char *s, int len);

which just takes the given length as gospel.  Brendan had also proposed
"text_to_cstring_limit(const text *t, int len)" with similar Min()
semantics, but what this was doing was replacing copies into
limited-size local buffers with a palloc.  If we did that we might
as well just use text_to_cstring.  What I think is more useful is
a strlcpy()-like function that copies into a caller-supplied buffer
of limited size.  For lack of a better idea I propose defining it
*exactly* like strlcpy:

extern size_t textlcpy(char *dst, const text *src, size_t siz);

I've also found that there are lots and lots of places where the
text end of the conversion needs to be a Datum not a text *,
so it seems worthwhile to introduce a couple of macros to minimize
notation in that case:

#define CStringGetTextDatum(s) PointerGetDatum(cstring_to_text(s))
#define TextDatumGetCString(d) text_to_cstring((text *) DatumGetPointer(d))

Lastly, the originally submitted text-to-something functions would
work correctly on plain and 1-byte-header datums, but not on
compressed or toasted-out-of-line datums.  There are a whole lot of
places where that's not good enough.  Rather than expecting the caller
to use the right detoasting macro everywhere, it seems best to make
these functions cope with any variant.  That also avoids memory
leakage by allowing the intermediate copy to be pfree'd.  (I had
suggested that the pfree might be pointless, but I reconsidered ---
if the text object is large enough to be compressed or toasted,
we're talking about at least several K, so it's worth not leaking.)

In short, the infrastructure I'm currently testing is the above
definitions with the attached implementation.  Last call for
objections ...

regards, tom lane


/*
 * cstring_to_text
 *
 * Create a text value from a null-terminated C string.
 *
 * The new text value is freshly palloc'd with a full-size VARHDR.
 */
text *
cstring_to_text(const char *s)
{
return cstring_to_text_with_len(s, strlen(s));
}

/*
 * cstring_to_text_with_len
 *
 * Same as cstring_to_text except the caller specifies the string length;
 * the string need not be null_terminated.
 */
text *
cstring_to_text_with_len(const char *s, int len)
{
text   *result = (text *) palloc(len + VARHDRSZ);

SET_VARSIZE(result, len + VARHDRSZ);
memcpy(VARDATA(result), s, len);

return result;
}

/*
 * text_to_cstring
 *
 * Create a palloc'd, null-terminated C string from a text value.
 *
 * We support being passed a compressed or toasted text value.
 * This is a bit bogus since such values shouldn't really be referred to as
 * "text *", but it seems useful for robustness.  If we didn't handle that
 * case here, we'd need another routine that did, anyway.
 */
char *
text_to_cstring(const text *t)
{
char   *result;
text   *tunpacked = pg_detoast_datum_packed((struct varlena *) t);
int len = VARSIZE_ANY_EXHDR(tunpacked);

result = (char *) palloc(len + 1);
memcpy(result, VARDATA_ANY(tunpacked), len);
result[len] = '\0';

if (tunpacked != t)
pfree(tunpacked);

return result;
}

/*
 * textlcpy --- exactly like strlcpy(), except source is a text value.
 *
 * Copy src to string dst of size siz.  At most siz-1 characters
 * will be copied.  Always NUL terminates (unless siz == 0).
 * Returns strlen(src); if retval >= siz, truncation occurred.
 *
 * We support being passed a compressed or toasted text value.
 * This is a bit bogus since such values shouldn't really be referred to as
 * "text *", but it seems useful for robustness.  If we didn't handle that
 * case here, we'd need another routine that did, anyway.
 */
size_t
textlcpy(char *dst, const text *src, size_t siz)
{
text   *srcunpacked = pg_detoast_datum_packed((struct varlena *) 
src);
size_t  srclen = VARSIZE_ANY_EXHDR(srcunpacked);

if (siz > 0)
{
siz--;
if (siz >= srclen)

Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Zoltan Boszormenyi

Simon Riggs írta:

On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote:
  

Simon Riggs wrote:


RESTART IDENTITY will reset the SERIAL sequences back to the original
start value.
  
  

Assuming this feature were to be added

In cases where the same sequence has been used across multiple tables, 
what will be the appropriate response when a user attempts to TRUNCATE 
one of those tables with RESTART IDENTITY?



Well, I'm suggesting it as a TODO item, based on the standard. It would
be for whoever took this up to unravel that.

Since that's a weak answer, I'd say it should only reset sequences that
have been placed there automatically through the use of SERIAL or
BIGSERIAL datatypes.
  


All of them? PostgreSQL allow multiple SERIALs to be present,
the standard allows only one IDENTITY column in a table.
And what about this case below?

CREATE TABLE t1 (id1 serial, ...);
ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE;

or the equivalent

CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE;
CREATE TABLE t1 (id1 serial, ...);
ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1;

PostgreSQL doesn't keep the START WITH information.
But it should to perform a "restart" on the sequence,
using the minval in this case wouldn't be correct.

--
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/



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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Bruce Momjian
Zoltan Boszormenyi wrote:
> All of them? PostgreSQL allow multiple SERIALs to be present,
> the standard allows only one IDENTITY column in a table.
> And what about this case below?
> 
> CREATE TABLE t1 (id1 serial, ...);
> ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE;
> 
> or the equivalent
> 
> CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE;
> CREATE TABLE t1 (id1 serial, ...);
> ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1;
> 
> PostgreSQL doesn't keep the START WITH information.
> But it should to perform a "restart" on the sequence,
> using the minval in this case wouldn't be correct.

I do think we need to wait for the standard to be accepted before adding
them to the TODO list as standard-compliant additions, especially
because no one is asking for the syntax yet.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Gregory Stark
"Simon Riggs" <[EMAIL PROTECTED]> writes:

> SQL200n specifies a new qualifier on a TRUNCATE command
>
> TRUNCATE TABLE foo
>   [ CONTINUE IDENTITY | RESTART IDENTITY ]
>
> CONTINUE IDENTITY is the default and does nothing, like now.
>
> RESTART IDENTITY will reset the SERIAL sequences back to the original
> start value.
>
> Seems like a % project for the TODO list

I think we need SQL standard IDENTITY columns before we can consider adding
SQL standard CONTINUE IDENTITY or RESTART IDENTITY clauses. 

The reason the last attempt to add them petered out was precisely because they
*don't* exactly line up with the semantics of sequences so I don't imagine
attempting to shoehorn sequences into these clauses is likely to pan out.


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Gregory Stark
"Sam Mason" <[EMAIL PROTECTED]> writes:

> On Mon, Mar 24, 2008 at 05:27:04PM -0500, Decibel! wrote:
>> On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
>> >  SELECT i, (MIN((j,k))).k
>> >  FROM tbl
>> >  GROUP BY i;
>> 
>> How is that any better than SELECT i, min(k) FROM tbl GROUP BY i ?
>
> Because I want the value of k associated with the minimum value of j.
> For example, if I have data looking like:

I have nothing against having min(record) and it does seem like it would let
you do this at least for reasonably simple cases.

But I'm more eager to see full OLAP window functions which would let you do
this and a whole lot else as well.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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


Re: [HACKERS] TRUNCATE TABLE with IDENTITY

2008-03-25 Thread Zoltan Boszormenyi

Zoltan Boszormenyi írta:

Simon Riggs írta:

On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote:
 

Simon Riggs wrote:
   

RESTART IDENTITY will reset the SERIAL sequences back to the original
start value.


Assuming this feature were to be added

In cases where the same sequence has been used across multiple 
tables, what will be the appropriate response when a user attempts 
to TRUNCATE one of those tables with RESTART IDENTITY?



Well, I'm suggesting it as a TODO item, based on the standard. It would
be for whoever took this up to unravel that.

Since that's a weak answer, I'd say it should only reset sequences that
have been placed there automatically through the use of SERIAL or
BIGSERIAL datatypes.
  


All of them? PostgreSQL allow multiple SERIALs to be present,
the standard allows only one IDENTITY column in a table.
And what about this case below?

CREATE TABLE t1 (id1 serial, ...);
ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE;

or the equivalent

CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE;
CREATE TABLE t1 (id1 serial, ...);


of course
CREATE TABLE t1 (id1 integer, ...);


ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1;

PostgreSQL doesn't keep the START WITH information.
But it should to perform a "restart" on the sequence,
using the minval in this case wouldn't be correct.




--
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/



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


Re: [HACKERS] [pgsql-www] New email list for emergency communications

2008-03-25 Thread Andrew Dunstan



Tom Lane wrote:

Andrew Sullivan <[EMAIL PROTECTED]> writes:
  

On Mon, Mar 24, 2008 at 06:39:25PM -0400, Andrew Dunstan wrote:

perusing a mailbox again. We have unfortunately been badly underprepared 
for this.
  


  

Surely that there is an emerging consensus to that effect means that it's
not as unfortunate as it might be?  I seem to recall the original
announcement suggesting this was an experiment.  I wouldn't expect the first
couple rounds to go without a hitch; as long as there is procedural
improvement the next time, that's a good thing, right?



Yeah, we expected to have glitches.  I think we now have a much better
idea what sort of status-tracking support we need for future fests.


  


Yes. I'm not meaning to whine, sorry if it comes over like that. It 
looks to me like we need a sort of prep phase for a commit-fest, so the 
people switching into commit-fest mode when it starts can do so with 
little friction.


cheers

andrew

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


Re: [HACKERS] partial dump of patch queue to wiki

2008-03-25 Thread Gregory Stark
"Alvaro Herrera" <[EMAIL PROTECTED]> writes:

> Hi,
>
> I created a page for our current commitfest:
>
> http://wiki.postgresql.org/wiki/CommitFest:March
>
> Not all patches are there yet.  I only added the latest version of each
> patch.  I'll continue after lunch.
>
> I also created one for the next commitfest:
>
> http://wiki.postgresql.org/wiki/CommitFest:May

Hm, at the same time as you were doing this I wrote a perl script to dump
Bruce's mail box into a table. The results are at:

http://wiki.postgresql.org/wiki/CommitFest:Bruce

I think the next step is to manually go through them and remove the comments,
replacing them with a single "status" cell. (And sending mail to the author
and -hackers with the meat of the review).

(Note that the "author" is just the author of the first message Bruce saved --
in some cases that's not the right person. And the "reviewer" is just the
author of the last comment.)

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

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


Re: [HACKERS] [pgsql-www] New email list for emergency communications

2008-03-25 Thread Bruce Momjian
Andrew Dunstan wrote:
> >> Surely that there is an emerging consensus to that effect means that it's
> >> not as unfortunate as it might be?  I seem to recall the original
> >> announcement suggesting this was an experiment.  I wouldn't expect the 
> >> first
> >> couple rounds to go without a hitch; as long as there is procedural
> >> improvement the next time, that's a good thing, right?
> >> 
> >
> > Yeah, we expected to have glitches.  I think we now have a much better
> > idea what sort of status-tracking support we need for future fests.
> >
> > 
> >   
> 
> Yes. I'm not meaning to whine, sorry if it comes over like that. It 
> looks to me like we need a sort of prep phase for a commit-fest, so the 
> people switching into commit-fest mode when it starts can do so with 
> little friction.

Yea, I think we are still learning, and polishing our tools.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


[HACKERS] PG East and Washington DC tour

2008-03-25 Thread Bruce Momjian
Is anyone on hackers interested in touring Washington on Monday after PG
East?  Magnus and I are planning to go.  Please email me privately for
details.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] GiST opclass and varlena

2008-03-25 Thread Dragan Zubac

Hello

Not so familiar with all this math :) ,but here's my solution,which I 
must admit keep things going at the moment:


db=> \d prefix
 Table "public.prefix"
 Column  |  Type   |  Modifiers 
--+-+-

id   | bigint  | not null default nextval('prefix_id_seq'::regclass)
prefix   | text|
operator | integer |
Indexes:
   "prefix_pkey" PRIMARY KEY, btree (id)
   "prefix_index" UNIQUE, btree (prefix)


and we're using a procedure to match prefices (longest prefix 
match),with simething like:


...

-- CHECK PREFIX START

while tmp_length <= char_length(d_number) loop

-- take the number and try to find it in prefix table
-- if not found,decrease it by removing last number
-- and try again

   tmp_dest_number := substring (d_number from 1 for tmp_length);

   select into operator_temp operator from prefix
   where prefix=tmp_dest_number;


   if not found then
tmp_length := tmp_length + 1;
   else

-- if we have a match with some prefix
-- take the operator from that row

   operatorfound := true;
   operator_out := operator_temp;
   exit;
   end if; 


end loop;

-- CHECK PREFIX STOP



only 'semantic' problem You might have with this approach is that number 
is like 16511x,which belongs to some Hawaii island operator :),but 
the problem is that all You have in Your prefix table is 16xx,which 
You mark to belong to operator USA Something. In that case,Your system 
will think of 16511xx number as it belongs to USA Something operator 
and not Hawaii island operator :( Only solution to this is to always 
have up-to-date prefix table,and populate it even with the prefices Your 
system does not support,because Your system then reject that number and 
it will have a good/precise reason why he did it :)


Some poorly  measurement showed some 60-80 matching/sec with this 
algorithm of matching prefices and a couple of concurrent database 
connections.


Sincerely

Dragan

Dimitri Fontaine wrote:

Hi,

I'm trying to code a GiST opclass to index prefix searches (select ... from t 
where t.prefix @> query), now using a prefix_range datatype. This datatype is 
a varlena one, and storing it to disk and indexing it with BTrees work ok, 
but I'm failing to have my GiST opclass working, here's the problem:


postgres=# create index idx_prefix on ranges using gist(prefix 
gist_prefix_range_ops);
NOTICE:  gpr_picksplit(): entryvec->n= 234 maxoff= 232 l= 176 r=  56 l+r= 232 
unionL='01[0-7]' unionR='01[4-7]'

NOTICE:  gpr_picksplit(): v->spl_ldatum='01[0-7]' v->spl_rdatum='01[4-7]'
ERROR:  invalid memory alloc request size 3049878020

The code is available at pgfoundry here:
  http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/

The previous support for prefixes as text is still there (but will get 
deprecated soon --- or so I hope), and the new datatype and its usage not 
well commented nor documented currenlty. If this show up as a requirement to 
get your attention, please state it and I'll work on documenting prefix_range 
first.


I'm looking for some help on how to resolve the shown index creation problem, 
which I think is related to how I give data to GiST in its spl_ldatum and 
spl_rdatum from the user defined picksplit() method, lines 1101 and 1102 in 
prefix.c (version 1.26).


Regards,
  



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


Re: [HACKERS] partial dump of patch queue to wiki

2008-03-25 Thread Alvaro Herrera
Gregory Stark wrote:

> Hm, at the same time as you were doing this I wrote a perl script to dump
> Bruce's mail box into a table. The results are at:

Heh.  I should have guessed.


> http://wiki.postgresql.org/wiki/CommitFest:Bruce

It is a lot harder to trawl though ...  I think it's easier to finish my
version than get the weed out of yours -- one reason my list is so much
shorter is that there was a huge lot of stuff in the patch queue that
has no actual patch; and there are multiple versions of certain patches.

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

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


Re: [HACKERS] Text <-> C string

2008-03-25 Thread Brendan Jurd
On 26/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
>  Brendan's patch also included "cstring_text_limit(const char *s, int len)"
>  which was defined as copying Min(len, strlen(s)) bytes.  I didn't find
>  this to be particularly useful.  In the first place, all potential
>  callers are passing the exact desired length, so the strlen() call is
>  just a waste of cycles.  In the second place, at least some callers pass
>  text that is not embedded in a known-to-be-null-terminated string (it
>  could be a section of a text datum instead); which means there is a
>  nonzero chance of the strlen running off the end of memory and dumping
>  core.  So I propose instead
>
>  extern text *cstring_to_text_with_len(const char *s, int len);
>

That all makes sense to me.  I think the new name is good.  It's
pretty long, but I'm not seeing a shorter name that accurately
describes the function.

>  which just takes the given length as gospel.  Brendan had also proposed
>  "text_to_cstring_limit(const text *t, int len)" with similar Min()
>  semantics, but what this was doing was replacing copies into
>  limited-size local buffers with a palloc.  If we did that we might
>  as well just use text_to_cstring.  What I think is more useful is
>  a strlcpy()-like function that copies into a caller-supplied buffer
>  of limited size.  For lack of a better idea I propose defining it
>  *exactly* like strlcpy:
>
>  extern size_t textlcpy(char *dst, const text *src, size_t siz);
>

I'm all for providing a function with this behaviour, but is
textlcpy() a bit ambiguous?  It's not clear from the name whether the
function copies text -> text, text -> cstring or cstring -> text.  In
fact, if I didn't already know better I'd probably assume that the
function copied text -> text with length, in the same way strlcpy
copies string -> string.

A text_to_cstring_with_len() or text_to_cstring_limit() might be more
to the point, and more consistent with the other functions in the
family.

On the other hand, maybe some difference in naming would help make it
obvious to callers that, unlike its siblings, textlcpy() takes the
destination string as an argument rather than returning it.
text_to_cstring_lcpy()?

>  I've also found that there are lots and lots of places where the
>  text end of the conversion needs to be a Datum not a text *,
>  so it seems worthwhile to introduce a couple of macros to minimize
>  notation in that case:
>
>  #define CStringGetTextDatum(s) PointerGetDatum(cstring_to_text(s))
>  #define TextDatumGetCString(d) text_to_cstring((text *) DatumGetPointer(d))
>

Yes, I recall coming across a number of sites where these macros would
come in handy.

>  Lastly, the originally submitted text-to-something functions would
>  work correctly on plain and 1-byte-header datums, but not on
>  compressed or toasted-out-of-line datums.  There are a whole lot of
>  places where that's not good enough.  Rather than expecting the caller
>  to use the right detoasting macro everywhere, it seems best to make
>  these functions cope with any variant.  That also avoids memory
>  leakage by allowing the intermediate copy to be pfree'd.  (I had
>  suggested that the pfree might be pointless, but I reconsidered ---
>  if the text object is large enough to be compressed or toasted,
>  we're talking about at least several K, so it's worth not leaking.)
>

Excellent.  My patch didn't contemplate dealing with
compressed/toasted datums because, quite frankly, I didn't know *how*
to deal with them correctly.  Much to learn about varlenas, I still
have.

Cheers,
BJ

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


Re: [HACKERS] Text <-> C string

2008-03-25 Thread Tom Lane
"Brendan Jurd" <[EMAIL PROTECTED]> writes:
> On 26/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
>> ... What I think is more useful is
>> a strlcpy()-like function that copies into a caller-supplied buffer
>> of limited size.  For lack of a better idea I propose defining it
>> *exactly* like strlcpy:
>> 
>> extern size_t textlcpy(char *dst, const text *src, size_t siz);

> I'm all for providing a function with this behaviour, but is
> textlcpy() a bit ambiguous?

Fair enough, I'm not wedded to that name.  Search-and-replace is
still easy enough at this point ...

> A text_to_cstring_with_len() or text_to_cstring_limit() might be more
> to the point, and more consistent with the other functions in the
> family.

Hmm.  The thing that's bothering me is that the length is the size
of the *destination*, which is not like cstring_to_text_with_len,
so using a closely similar name might be confusing.  Of those two
I'd go with text_to_cstring_limit.  Another thought that comes to
mind is

void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len)

Anyone have other ideas?

regards, tom lane

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


Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.

2008-03-25 Thread Kurt Roeckx
On Mon, Mar 24, 2008 at 05:59:33PM -0400, Tom Lane wrote:
> Kurt Roeckx <[EMAIL PROTECTED]> writes:
> > I did try the patch.  It fails just the same way.
> 
> Hmph.  So we still don't know why 8.2 and 8.3 behave differently ...
> [ pokes around ... ] Hah, maybe this is it:
> 
> http://archives.postgresql.org/pgsql-committers/2007-03/msg00292.php

This patch atleast solves the problems with 8.2.


Kurt


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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Sam Mason
On Mar 25, 2008, at 4:43PM, Gregory Stark wrote:
> On Mar 20, 2008, at 2:23 PM, Sam Mason wrote:
> >  SELECT i, (MIN((j,k))).k
> >  FROM tbl
> >  GROUP BY i;
> 
> I have nothing against having min(record) and it does seem like it would let
> you do this at least for reasonably simple cases.

The main reason for this was that I've needed min(record) a few times
before and thought it would be reasonably easy to code.

> But I'm more eager to see full OLAP window functions which would let you do
> this and a whole lot else as well.

I've never used window functions before so don't think about them when
solving my problems.  If they were available I'd probably start using
them.  From the small bit of reading that I've done around them, they
seem very imperative in nature.  I'm not sure if this is a good or a bad
thing.

In a database that did support them, how would I write my query with
them?  My first naive attempt was this:

  SELECT i, MIN(k) OVER (PARTITION BY j)
  FROM tbl
  GROUP BY i;

This is obviously wrong, but I don't see how to get to where I need to
be.


  Sam

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


Re: [HACKERS] Text <-> C string

2008-03-25 Thread Bruce Momjian
Tom Lane wrote:
> > A text_to_cstring_with_len() or text_to_cstring_limit() might be more
> > to the point, and more consistent with the other functions in the
> > family.
> 
> Hmm.  The thing that's bothering me is that the length is the size
> of the *destination*, which is not like cstring_to_text_with_len,
> so using a closely similar name might be confusing.  Of those two
> I'd go with text_to_cstring_limit.  Another thought that comes to
> mind is
> 
> void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len)

I think I like buffer.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] Text <-> C string

2008-03-25 Thread Alvaro Herrera
Tom Lane escribió:
> "Brendan Jurd" <[EMAIL PROTECTED]> writes:

> > A text_to_cstring_with_len() or text_to_cstring_limit() might be more
> > to the point, and more consistent with the other functions in the
> > family.
> 
> Hmm.  The thing that's bothering me is that the length is the size
> of the *destination*, which is not like cstring_to_text_with_len,
> so using a closely similar name might be confusing.  Of those two
> I'd go with text_to_cstring_limit.  Another thought that comes to
> mind is
> 
> void text_to_cstring_buffer(const text *src, char *dst, size_t dst_len)

text_to_cstring_buffer seems okay.  I did wonder for a bit whether it
should be 

void text_to_cstring_buffer(const text *src, char *buf, size_t buf_len)

but then the src/dst pair seems better than src/buf.

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

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


Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.

2008-03-25 Thread Tom Lane
Kurt Roeckx <[EMAIL PROTECTED]> writes:
> On Mon, Mar 24, 2008 at 05:59:33PM -0400, Tom Lane wrote:
>> http://archives.postgresql.org/pgsql-committers/2007-03/msg00292.php

> This patch atleast solves the problems with 8.2.

Excellent, I'll go back-patch that and we can see what else there is.
You still have panda set up to build with gcc 4.3, correct?

regards, tom lane

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


Re: [HACKERS] Wiki patch queue

2008-03-25 Thread Alvaro Herrera
Ok, AFAICT it is complete:

http://wiki.postgresql.org/wiki/CommitFest:March

It is a reasonably short page, so it's really easy to search for things
you might want to work on for this commit fest.

I also added the patches submitted on March 2008 to the May commitfest
page.

Patch submitters: please have a look at the current commitfest page and
check for possible nuisances.

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

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


Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.

2008-03-25 Thread Kurt Roeckx
On Tue, Mar 25, 2008 at 02:11:30PM -0400, Tom Lane wrote:
> Kurt Roeckx <[EMAIL PROTECTED]> writes:
> > On Mon, Mar 24, 2008 at 05:59:33PM -0400, Tom Lane wrote:
> >> http://archives.postgresql.org/pgsql-committers/2007-03/msg00292.php
> 
> > This patch atleast solves the problems with 8.2.
> 
> Excellent, I'll go back-patch that and we can see what else there is.
> You still have panda set up to build with gcc 4.3, correct?

Yes, I just disabled some cron jobs for those that failed.


kurt


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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Gregory Stark
"Sam Mason" <[EMAIL PROTECTED]> writes:

>   SELECT i, MIN(k) OVER (PARTITION BY j)
>   FROM tbl
>   GROUP BY i;
>
> This is obviously wrong, but I don't see how to get to where I need to
> be.

I'm not entirely sure myself. I think it might involve RANK OVER j though.

I suspect it will look more like the DISTINCT ON solution than the min(record)
solution.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

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


Re: [HACKERS] Text <-> C string

2008-03-25 Thread Pavel Stehule
>
>  extern text *cstring_to_text_with_len(const char *s, int len);

buffer_to_text ???

Regards
Pavel Stehule

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


Re: [HACKERS] Wiki patch queue

2008-03-25 Thread Pavel Stehule
Hello

there is some noises about my patches :(

I sent EXECUTE USING - it's important (against to SQL injection and
faster dynamic SQL), this patch is linger time in queue.

variadic function, named params exist only as WIP and I see it for
next commit fest. I'll send new version in next months.

Regards
Pavel Stehule



On 25/03/2008, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Ok, AFAICT it is complete:
>
>  http://wiki.postgresql.org/wiki/CommitFest:March
>
>  It is a reasonably short page, so it's really easy to search for things
>  you might want to work on for this commit fest.
>
>  I also added the patches submitted on March 2008 to the May commitfest
>  page.
>
>  Patch submitters: please have a look at the current commitfest page and
>  check for possible nuisances.
>
>  --
>  Alvaro Herrerahttp://www.CommandPrompt.com/
>  The PostgreSQL Company - Command Prompt, Inc.
>
>
>  --
>  Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-hackers
>

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Sam Mason
On Tue, Mar 25, 2008 at 06:58:06PM +, Gregory Stark wrote:
> "Sam Mason" <[EMAIL PROTECTED]> writes:
> >   SELECT i, MIN(k) OVER (PARTITION BY j)
> >   FROM tbl
> >   GROUP BY i;
> >
> > This is obviously wrong, but I don't see how to get to where I need to
> > be.
> 
> I'm not entirely sure myself. I think it might involve RANK OVER j though.

The main thing I wanted to avoid was an explosion of sub-queries that
you get with DISTINCT ON style queries.  For example, with record style
syntax, I can do:

  SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb
  FROM tbl
  GROUP BY i;

whereas using DISTINCT ON I'd have to do:

  SELECT a.i, a.k AS ka, b.k as kb
  FROM (
SELECT DISTINCT ON (i) i, k
FROM tbl
ORDER BY i, j) a, (
SELECT DISTINCT ON (i) i, k
FROM tbl
ORDER BY i, mycode(j)) b
  WHERE a.i = b.i;

Which gets unmanageable quickly.  Any idea how window functions would
cope with this sort of complexity?  Or is this what you meant by:

> I suspect it will look more like the DISTINCT ON solution than the min(record)
> solution.


Thanks,
  Sam

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


Re: [HACKERS] Idea for minor tstore optimization

2008-03-25 Thread Neil Conway
On Sat, 2008-03-22 at 21:24 -0400, Tom Lane wrote:
> Oh, wait, that's just a -patches entry; it doesn't look like Neil
> ever committed it.  Neil, how come?

Sorry, slipped through the cracks -- I've now committed the patch.

-Neil



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


Re: [HACKERS] Proposal for Null Bitmap Optimization(for Trailing NULLs)

2008-03-25 Thread Gokulakannan Somasundaram
Hi,
As said, i am attaching the performance test results and the same patch
in this thread works with the latest CVS head.
Actually, i am seeing a slight performance improvement with the patch, which
i think might be either because of noise/ lesser pages. i ran it with the
default settings.  i have tested only inserts and selects, because that's
where the code change has happened.

Regarding Tom's comments
As far as the changes are concerned, the patch changes the following
functions
a) heap_fill_tuple
b) nocachegetattr
c) heap_form_tuple
d) index_form_tuple
e) nocache_index_getattr
f) changed the macros index_getattr, IndexTupleSize, IndexTupleDSize
g) Introduced a new macro IndexTupleActualSize


The patch introduces the following changes to the storage of tuples
1) If there are only trailing nulls, it doesn't store the null bitmap
2) If there are non-trailing nulls and trailing nulls, it stores the
null-bitmap only till the last non-null value. so it decreases the storage
requirement of null bitmap. This is expected to have only very few use-cases
3) It doesn't store the null-bitmap for trailing nulls in indexes also

The functions mentioned in d), e), f), g) are required for the functionality
of index null-bitmap handling. I suppose, we can't handle it with only
heap_form_tuple. Please correct me, if i am wrong..

For having the functionality 2), we have to touch the heap_fill_tuple. i
have done the trick, by asking it to use the passed number of attributes,
instead of taking it from tupdesc.  Again please advice me on how to
implement this with only heap_form_tuple.

Looking forward for comments/suggestions.

Thanks,
Gokul.


Trailing null - results.ods
Description: application/vnd.oasis.opendocument.spreadsheet

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


Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.

2008-03-25 Thread Tom Lane
Kurt Roeckx <[EMAIL PROTECTED]> writes:
> On Tue, Mar 25, 2008 at 02:11:30PM -0400, Tom Lane wrote:
>> Excellent, I'll go back-patch that and we can see what else there is.
>> You still have panda set up to build with gcc 4.3, correct?

> Yes, I just disabled some cron jobs for those that failed.

Patches committed, please re-enable the back branches so we can
see what happens.

regards, tom lane

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Gregory Stark
"Sam Mason" <[EMAIL PROTECTED]> writes:

> On Tue, Mar 25, 2008 at 06:58:06PM +, Gregory Stark wrote:
> The main thing I wanted to avoid was an explosion of sub-queries that
> you get with DISTINCT ON style queries.  For example, with record style
> syntax, I can do:
>
>   SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb
>   FROM tbl
>   GROUP BY i;
>
> whereas using DISTINCT ON I'd have to do:
...
> Which gets unmanageable quickly.  Any idea how window functions would
> cope with this sort of complexity?  Or is this what you meant by:
>
>> I suspect it will look more like the DISTINCT ON solution than the 
>> min(record)
>> solution.

The flip side is that if you want to get several fields based on min(j) the
min(record) approach requires you to write that expression several times (and
the database to calculate it several times).

I think the window functions might (assuming an ideal implementation) get the
best of both worlds. You would be able to do something with multiple
partitions so you could ask of a few columns where rank over j = 1 and a few
more columns where rank over k = 1.

But, uh, I'm not sure. I'll have to sit down with the spec and see if that's
true. Furthermore it may be wishful thinking to hope that the implementation
will do anything special with the special case where you're only selecting
records where rank = 1.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Sam Mason
On Tue, Mar 25, 2008 at 07:54:17PM +, Gregory Stark wrote:
> "Sam Mason" <[EMAIL PROTECTED]> writes:
> > SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb
> > FROM tbl
> > GROUP BY i;
> 
> The flip side is that if you want to get several fields based on min(j) the
> min(record) approach requires you to write that expression several times (and
> the database to calculate it several times).

No.  My demos have only used one column because that's the smallest
useful demo.

  SELECT i, r.k, r.l
  FROM (
SELECT i, MIN((j,k,l)) AS r
FROM tbl
GROUP BY i) x;

The reason for the sub-select is only because SQL doesn't provide any
other way to name expressions.  Hum, or at least this should work...
There doesn't seem to be any nice way of getting fields out of a record!

If I really want to do this, it's going to turn into quite an overhaul
of record handling in PG.  It would also remove the nice syntactic trick
that a.b identifies the field "b" from table "a", and s.a.b means that
the above is in schema "s".

> I think the window functions might (assuming an ideal implementation) get the
> best of both worlds. You would be able to do something with multiple
> partitions so you could ask of a few columns where rank over j = 1 and a few
> more columns where rank over k = 1.
>
> But, uh, I'm not sure. I'll have to sit down with the spec and see if that's
> true. Furthermore it may be wishful thinking to hope that the implementation
> will do anything special with the special case where you're only selecting
> records where rank = 1.

I don't really understand what you're saying above.  Optimisation is
another can of worms that shouldn't be opened until we know how this
sort of thing is going to be used.


  Sam

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


Re: [HACKERS] GiST opclass and varlena

2008-03-25 Thread Dimitri Fontaine
Le Tuesday 25 March 2008 17:57:11 Dragan Zubac, vous avez écrit :
> and we're using a procedure to match prefices (longest prefix
> match),with simething like:
>
> while tmp_length <= char_length(d_number) loop
>
> -- take the number and try to find it in prefix table
> -- if not found,decrease it by removing last number
> -- and try again
[...]
> Some poorly  measurement showed some 60-80 matching/sec with this
> algorithm of matching prefices and a couple of concurrent database
> connections.

With the GiST index support we're hoping to get millisecond response time 
order (that mean something like 1000 matching per sec, best case), and with a 
query that simple :
SELECT operator 
  FROM prefix 
 WHERE prefix @> '16511xx' 
  ORDER BY len(prefix) DESC
 LIMIT 1;

For this you have to create a dedicated index, first version of the code has 
this version:
  http://prefix.projects.postgresql.org/README.html
  CREATE INDEX idx_prefix ON prefix USING GIST(prefix gist_prefix_ops);

New version I'm trying to make work looks like this:
  CREATE TABLE prefix(id serial, prefix prefix_range, operator integer);
  CREATE INDEX idx_prefix ON prefix USING GIST(prefix gist_prefix_range_ops);

Former usage shows performance of 2 to 5 ms answer time on lower setups 
(meaning sth like 200 reqs/s already), latter one is aiming at 1000 req/s as 
said before, but does not work at all at the moment... The goal is to be able 
to use the search from an AFTER INSERT TRIGGER to materialize some calling 
stats and prices, etc. So it has to be that fast.

Please consider trying the code if you're interrested, it's been tested with 
PostgreSQL versions 8.2 and 8.3, and former version is working fine with text 
type prefixes, and should offer you some speedups already.

Hope this helps clarifying the goals and context,
-- 
dim


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


[HACKERS] advancing snapshot's xmin

2008-03-25 Thread Alvaro Herrera
Hi,

I've finished (hopefully) the code to handle a current list of open
snapshots in a transaction.  I'm now wondering how to put it to good use
;-)  I'm not posting it yet -- first I want to get some feedback on the
previous patch I posted,
http://archives.postgresql.org/pgsql-patches/2008-03/msg00245.php


I think the important change here is switching the semantics of
MyProc->xmin.  Currently, it is "the minimum of Xmin and Xid, across all
backends, at the moment the current transaction fetches its serializable
snapshot".  The first important bit is that it is computed only once:
when the serializable snapshot is taken.

So ISTM the important change is that we will have to update MyProc->xmin
more frequently than that.  I'm thinking in keeping enough local state
so that we can detect at what time the earliest open snapshot is
unregistered; when that happens, we can recalculate MyProc->xmin based
on the snapshots we have and the Xid/Xmin of remote backends (which
could have also been updating their own xmins).

There is one hole here: contention on ProcArrayLock.  Basically, for
simple transactions we will need to update MyProc after every command.
It has been reported that ProcArrayLock is the most contended lock for
some loads; this would only add to that, and heavily I think.  Perhaps
we could restructure the locking here somehow to avoid this problem, but
it is complex enough already that it may not even be possible.

Another idea is to throttle the updating of Xmin so it only happens once
in a while, but it's difficult to find a useful criterion and avoid
falling into the trap that we just neglected to update it before a large
command.


Thoughts?

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

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


[HACKERS] Hash Join Optimization

2008-03-25 Thread Gokulakannan Somasundaram
Hi,
   I had a chance to go through the Hash join code of Postgresql and had the
following thoughts.

- Currently postgres takes the heaptuple from the slot and creates and
minimal_tuple and copies it into the temp file.

I think the creation of minimal_tuple in the middle is a overhead which can
be avoided by creating a mem-map and directly creating the minimal_tuple in
the mem-map. Since Hash join is used mainly to join huge tables, this might
benefit those warehouse customers of postgres.

Am i missing something???

Thanks,
Gokul.


Re: [HACKERS] gcc 4.3 breaks ContribCheck in 8.2 and older.

2008-03-25 Thread Andrew Dunstan



Tom Lane wrote:

Kurt Roeckx <[EMAIL PROTECTED]> writes:
  

On Tue, Mar 25, 2008 at 02:11:30PM -0400, Tom Lane wrote:


Excellent, I'll go back-patch that and we can see what else there is.
You still have panda set up to build with gcc 4.3, correct?
  


  

Yes, I just disabled some cron jobs for those that failed.



Patches committed, please re-enable the back branches so we can
see what happens.


  


I have tested this back as far as 8.0, and all seems OK.

cheers

andrew

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


Re: [HACKERS] advancing snapshot's xmin

2008-03-25 Thread Simon Riggs
On Tue, 2008-03-25 at 17:26 -0300, Alvaro Herrera wrote:

> I've finished (hopefully) the code to handle a current list of open
> snapshots in a transaction.  I'm now wondering how to put it to good use
> ;-)  I'm not posting it yet -- first I want to get some feedback on the
> previous patch I posted,
> http://archives.postgresql.org/pgsql-patches/2008-03/msg00245.php

As I said before, it looks fine. In your words, it "just moves code
around", so there's not much to complain about.

> I think the important change here is switching the semantics of
> MyProc->xmin.  Currently, it is "the minimum of Xmin and Xid, across all
> backends, at the moment the current transaction fetches its serializable
> snapshot".  The first important bit is that it is computed only once:
> when the serializable snapshot is taken.

Yes, I see that as necessary. So the refactoring makes sense, since
we'll be adding lots of stuff in that area and its good to keep it
isolated.

> So ISTM the important change is that we will have to update MyProc->xmin
> more frequently than that.  I'm thinking in keeping enough local state
> so that we can detect at what time the earliest open snapshot is
> unregistered; when that happens, we can recalculate MyProc->xmin based
> on the snapshots we have and the Xid/Xmin of remote backends (which
> could have also been updating their own xmins).
> 
> There is one hole here: contention on ProcArrayLock.  Basically, for
> simple transactions we will need to update MyProc after every command.
> It has been reported that ProcArrayLock is the most contended lock for
> some loads; this would only add to that, and heavily I think.  Perhaps
> we could restructure the locking here somehow to avoid this problem, but
> it is complex enough already that it may not even be possible.

I don't see that this would be a contention problem.

We are already careful to read the xmin just once during
GetSnapshotData(). We advance it while holding only a LW_SHARED lock
during a serializable snapshot, so not sure why we wouldn't advance it
at other times also without contention issues. Why does anyone else know
or care whether we're taking a serializable snapshot or not?

The issue is whether we agree that is correct to do so. If we're
advancing it in the circumstances you say, then yes I agree it is.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk


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


Re: [HACKERS] advancing snapshot's xmin

2008-03-25 Thread Neil Conway
On Tue, 2008-03-25 at 17:26 -0300, Alvaro Herrera wrote:
> There is one hole here: contention on ProcArrayLock.  Basically, for
> simple transactions we will need to update MyProc after every command.

If we're just updating MyProc->xmin, we only need to acquire
ProcArrayLock in shared mode, right?

> Another idea is to throttle the updating of Xmin so it only happens once
> in a while, but it's difficult to find a useful criterion and avoid
> falling into the trap that we just neglected to update it before a large
> command.

Using LWLockConditionalAcquire() might help also.

-Neil



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


Re: [PATCHES] [HACKERS] Text <-> C string

2008-03-25 Thread Tom Lane
"Brendan Jurd" <[EMAIL PROTECTED]> writes:
> As discussed on -hackers, I'm trying to get rid of some redundant code
> by creating a widely useful set of functions to convert between text
> and C string in the backend.

Applied with revisions --- the functions were modified as per recent
discussion, and I fixed a lot more potential call sites.

There are no textout/textin calls left, but I may have missed some
places that were doing it the hard way with direct palloc/memcpy
manipulations.  It might be worth trolling all the VARDATA() references
to see if any more are easily replaceable.

I notice in particular that xfunc.sgml contains sample C functions to
copy and concatenate text.  While these aren't directly replaceable
with the new functions, I wonder whether we ought to change the examples
to make them less certain to break if we ever change text's
representation.

regards, tom lane

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


Re: [HACKERS] advancing snapshot's xmin

2008-03-25 Thread Heikki Linnakangas

Neil Conway wrote:

On Tue, 2008-03-25 at 17:26 -0300, Alvaro Herrera wrote:

There is one hole here: contention on ProcArrayLock.  Basically, for
simple transactions we will need to update MyProc after every command.


If we're just updating MyProc->xmin, we only need to acquire
ProcArrayLock in shared mode, right?


In fact, do you need a lock at all? We already assume that 
reading/writing a TransactionId is atomic in many places. We acquire 
ProcArrayLock at the end of transaction when we clear MyProc->xid, to 
ensure that we don't exit the set of running transactions while someone 
else is taking a snapshot, but AFAICS that's not necessary when we just 
advance MyProc->xmin.


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

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Gregory Stark
"Sam Mason" <[EMAIL PROTECTED]> writes:

> The reason for the sub-select is only because SQL doesn't provide any
> other way to name expressions.  Hum, or at least this should work...
> There doesn't seem to be any nice way of getting fields out of a record!
>
> If I really want to do this, it's going to turn into quite an overhaul
> of record handling in PG.  It would also remove the nice syntactic trick
> that a.b identifies the field "b" from table "a", and s.a.b means that
> the above is in schema "s".

Yeah, to disambiguate it you have to use (r).i


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

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


[HACKERS] Script binaries renaming

2008-03-25 Thread Bruce Momjian
Where are we on this?  Tom thinks we don't want this.  TODO has:

  * Prefix command-line utilities like createuser with 'pg_'
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php

See for reference:

http://momjian.us/mhonarc/patches/[EMAIL PROTECTED]

One idea is to keep the existing commands and just add pg_* (or pg*) to
additional versions, with the idea that the original versions will be
removed some day.

---


Zdenek Kotala wrote:
> I attach complete patch which renames following binaries
> 
> createdb createlang createuser dropdb droplang dropuser clusterdb 
> vacuumdb reindexdb
> 
> to
> 
> pg_createdb pg_createlang pg_createuser pg_dropdb pg_droplang 
> pg_dropuser pg_clusterdb pg_vacuumdb pg_reindexdb
> 
> Symlinks (or copy on win32) are created for backward compatibility.
> 
> This renaming was discussed there:
> 
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00145.php
> 
> I create also separate unified patch for documentation.
> 
>   Zdenek

[ application/x-gzip is not supported, skipping... ]

> 
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
> 
> http://www.postgresql.org/about/donate

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] writing a MIN(RECORD) aggregate

2008-03-25 Thread Sam Mason
On Wed, Mar 26, 2008 at 01:03:18AM +, Gregory Stark wrote:
> "Sam Mason" <[EMAIL PROTECTED]> writes:
> > The reason for the sub-select is only because SQL doesn't provide any
> > other way to name expressions.  Hum, or at least this should work...
> > There doesn't seem to be any nice way of getting fields out of a record!
> 
> Yeah, to disambiguate it you have to use (r).i

OK, that sort of makes sense.  The next problem is that PG doesn't
remember the column names:

  SELECT (ROW(i)).i FROM (SELECT 1) x(i);

Results in PG saying it doesn't know where "i" is inside the row, which
seems a little strange.  I think it's this detail that accounts for
my problems in trying to get this all working before.  This seems to
suggest that there are two record-like data structures in PG, one for
the records returned as part of the SELECT list and another that I'm
using here.

As a side case, would it be nice if:

  SELECT (SELECT 1 AS a, 2 AS b);

resulted in a record with two members?


  Sam

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


[HACKERS] Bogosity in contrib/xml2/Makefile

2008-03-25 Thread Tom Lane
Whilst fooling with bug #4058 I noticed that xml2's .c files were being
compiled without -g or any of the various warning flags we normally use.
I saw this:

gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include -D_GNU_SOURCE 
-I/usr/include/libxml2   -c -o xpath.o xpath.c

when I expected something like this:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g 
-fpic -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o 
xpath.o xpath.c

The reason is apparently this line in its Makefile:

override CFLAGS += $(shell xml2-config --cflags)

It seems the "override" locks down the value so that the subsequent
assignment in Makefile.global does nothing.  I didn't try the PGXS
case but I imagine it doesn't do the right thing either.

Now, in HEAD and 8.3 I think we could just remove this line, because
configure knows how to pull the needed -I and -L flags out of
xml2-config's output and stick them into appropriate flag variables
(neither of which is CFLAGS btw...).  I am not sure what to do in older
branches though --- there doesn't seem to be any real nice solution.

Even though xml2 is deprecated and may go away for 8.4, I think this is
important to fix in the back branches.  Failing to use the -f flags for
instance could be resulting in outright wrong code, and we'd be unlikely
to notice since there's no regression test at all for this module.

Thoughts?

regards, tom lane

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


Re: [HACKERS] stored procedure stats in collector

2008-03-25 Thread Bruce Momjian

This has been saved for the next commit-fest:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---

Martin Pihlak wrote:
> Howdy,
> 
> Here's an updated version of the function stats patch. The biggest change is
> that by default only procedural language functions are counted. The GUC 
> variable
> stats_function_level now takes 3 values: on, off and all. The latter also
> counts SQL and C language functions, "on" means only to count procedural
> language functions. The decision is now made in fmgr_info_cxt_security()
> Trigger functions are now also counted.
> 
> Sample output:
> 
> select procname, calls, total_time, total_cpu, self_time, self_cpu
> from pg_stat_user_functions order by self_cpu desc limit 5;
> 
>procname  | calls | total_time | total_cpu | self_time | self_cpu
> +---++---+---+--
>   next_batch | 32765 |  27139 |  8574 | 27139 | 8574
>   fetch_batch_events |  3636 |   9252 |  5622 |  3771 | 2717
>   batch_event_sql|  3636 |   5454 |  2888 |  3910 | 1962
>   finish_batch   |  3420 |   3215 |  1475 |  3215 | 1475
>   batch_event_tables |  3636 |   1448 |   865 |  1434 |  858
> (5 rows)
> 
> This is still 8.2 only (tested on 8.2.4 and 8.2.5), has seen some production
> usage here at Skype (about a month on reasonably busy boxes). So far so good.
> 
> Couple of issues:
> - sometimes self_cpu > self_time - currently blaming it on Linux version of
> gettimeofday().
> - dropped functions are not purged from stats, might bloat the stats file for
> some usage patterns.
> 
> PS. Would something like this be a canditate for 8.4 inclusion (if polished 
> up)?
> 
> Regards,
> Martin
> 

[ application/x-gzip is not supported, skipping... ]

> 
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
> 
> http://www.postgresql.org/about/donate

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] advancing snapshot's xmin

2008-03-25 Thread Tom Lane
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Neil Conway wrote:
>> If we're just updating MyProc->xmin, we only need to acquire
>> ProcArrayLock in shared mode, right?

> In fact, do you need a lock at all?

I think you probably do.  GetSnapshotData needs to be confident that the
global xmin it computes is <= the xmin that any other backend might be
about to store into its MyProc->xmin; how can you ensure that if there's
no locking happening?

Now the way I'd been envisioning this would work is that whenever the
number of active snapshots goes to zero, we clear MyProc->xmin, and
that probably could be done without a lock.  Then the next time we do 
GetSnapshotData, it would compute and store a new MyProc->xmin
(this would be the same activity that we currently think of as "setting
the serializable snapshot").  So you don't need any more locking than
already exists.

regards, tom lane

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


Re: [HACKERS] Reducing Transaction Start/End Contention

2008-03-25 Thread Bruce Momjian

Added to TODO:

> * Consider transaction start/end performance improvements
>
>   http://archives.postgresql.org/pgsql-hackers/2007-07/msg00948.php
>   http://archives.postgresql.org/pgsql-hackers/2008-03/msg00361.php


---

Simon Riggs wrote:
> Jignesh Shah's scalability testing on Solaris has revealed further
> tuning opportunities surrounding the start and end of a transaction.
> Tuning that should be especially important since async commit is likely
> to allow much higher transaction rates than were previously possible.
> 
> There is strong contention on the ProcArrayLock in Exclusive mode, with
> the top path being CommitTransaction(). This becomes clear as the number
> of connections increases, but it seems likely that the contention can be
> caused in a range of other circumstances. My thoughts on the causes of
> this contention are that the following 3 tasks contend with each other
> in the following way:
> 
> CommitTransaction(): takes ProcArrayLock Exclusive
> but only needs access to one ProcArray element
> 
> waits for
> 
> GetSnapshotData():ProcArrayLock Shared
> ReadNewTransactionId():XidGenLock Shared
> 
> which waits for
> 
> GetNextTransactionId()
> takes XidGenLock Exclusive
> ExtendCLOG(): takes ClogControlLock Exclusive, WALInsertLock Exclusive
> two possible place where I/O is required
> ExtendSubtrans(): takes SubtransControlLock()
> one possible place where I/O is required
> Avoids lock on ProcArrayLock: atomically updates one ProcArray element
> 
> 
> or more simply:
> 
> CommitTransaction() -- i.e. once per transaction
> waits for
> GetSnapshotData() -- i.e. once per SQL statement
> which waits for
> GetNextTransactionId() -- i.e. once per transaction
> 
> This gives some goals for scalability improvements and some proposals.
> (1) and (2) are proposals for 8.3 tuning, the others are directions for
> further research.
> 
> 
> Goal: Reduce total time that GetSnapshotData() waits for
> GetNextTransactionId()
> 
> 1. Increase size of Clog-specific BLCKSZ
> Clog currently uses BLCKSZ to define the size of clog buffers. This can
> be changed to use CLOG_BLCKSZ, which would then be set to 32768.
> This will naturally increase the amount of memory allocated to the clog,
> so we need not alter CLOG_BUFFERS above 8 if we do this (as previously
> suggested, with successful results). This will also reduce the number of
> ExtendClog() calls, which will probably reduce the overall contention
> also.
> 
> 2. Perform ExtendClog() as a background activity
> Background process can look at the next transactionid once each cycle
> without holding any lock. If the xid is almost at the point where a new
> clog page would be allocated, then it will allocate one prior to the new
> page being absolutely required. Doing this as a background task would
> mean that we do not need to hold the XidGenLock in exclusive mode while
> we do this, which means that GetSnapshotData() and CommitTransaction()
> would also be less likely to block. Also, if any clog writes need to be
> performed when the page is moved forwards this would also be performed
> in the background.
> 
> 3. Consider whether ProcArrayLock should use a new queued-shared lock
> mode that puts a maximum wait time on ExclusiveLock requests. It would
> be fairly hard to implement this well as a timer, but it might be
> possible to place a limit on queue length. i.e. allow Share locks to be
> granted immediately if a Shared holder already exists, but only if there
> is a queue of no more than N exclusive mode requests queued. This might
> prevent the worst cases of exclusive lock starvation. 
> 
> 4. Since shared locks are currently queued behind exclusive requests
> when they cannot be immediately satisfied, it might be worth
> reconsidering the way LWLockRelease works also. When we wake up the
> queue we only wake the Shared requests that are adjacent to the head of
> the queue. Instead we could wake *all* waiting Shared requestors.
> 
> e.g. with a lock queue like this:
> (HEAD)S<-S<-X<-S<-X<-S<-X<-S
> Currently we would wake the 1st and 2nd waiters only. 
> 
> If we were to wake the 3rd, 5th and 7th waiters also, then the queue
> would reduce in length very quickly, if we assume generally uniform
> service times. (If the head of the queue is X, then we wake only that
> one process and I'm not proposing we change that). That would mean queue
> jumping right? Well thats what already happens in other circumstances,
> so there cannot be anything intrinsically wrong with allowing it, the
> only question is: would it help? 
> 
> We need not wake the whole queue, there may be some generally more
> beneficial heuristic. The reason for considering this is not to speed up
> Shared requests but to reduce the queue length and thus the waiting time
> for the Xclusive requestors. Each time a Shared request is dequeued, we
> effectively re-enable queue jumping, so a Shared request ar

Re: [HACKERS] Script binaries renaming

2008-03-25 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Where are we on this?  Tom thinks we don't want this.  TODO has:
>   * Prefix command-line utilities like createuser with 'pg_'
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php

It wasn't just me; quite a few people were dubious about it when the
patch was submitted.  See the thread here:
http://archives.postgresql.org/pgsql-patches/2007-07/msg00055.php

> One idea is to keep the existing commands and just add pg_* (or pg*) to
> additional versions, with the idea that the original versions will be
> removed some day.

AFAICS the only argument for doing this is to eliminate confusion and
potential conflicts, which means that we get no benefit at all until we
actually do remove the old names.  So if we're going to do this, we have
to make a commitment that we're going to remove the old names within the
reasonably foreseeable future (say, about two releases out).

Are we really prepared to break everyone's scripts for this?

regards, tom lane

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


Re: [HACKERS] pl/pgsql Plan Invalidation and search_path

2008-03-25 Thread Merlin Moncure
On Mon, Mar 24, 2008 at 9:48 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
>  Add to pl/pgsql TODO:
>
> o Consider invalidating the cache or keeping seperate cached
>   copies when search_path changes
>
>   http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php

IMO, Tom's idea, namely to keep separate cache plans for various
search_path settings, is a much stronger proposal and should probably
get the 'todo'.

merlin

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


Re: [HACKERS] pl/pgsql Plan Invalidation and search_path

2008-03-25 Thread Bruce Momjian
Merlin Moncure wrote:
> On Mon, Mar 24, 2008 at 9:48 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> >
> >  Add to pl/pgsql TODO:
> >
> > o Consider invalidating the cache or keeping seperate cached
> >   copies when search_path changes
> >
> >   http://archives.postgresql.org/pgsql-hackers/2008-01/msg01009.php
> 
> IMO, Tom's idea, namely to keep separate cache plans for various
> search_path settings, is a much stronger proposal and should probably
> get the 'todo'.

Done:

 o Consider keeping seperate cached copies when search_path changes

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] [PATCHES] Add function for quote_qualified_identifier?

2008-03-25 Thread Bruce Momjian
Brendan Jurd wrote:
> On 9/29/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> > I think we need more than one person's request to add this function.
> 
> Well, I don't expect it would get requested.  Most DBAs would likely
> look for the function in the docs, see it's not there and then just
> implement it themselves.  Obviously it's not critical.  But
> anticipating those little requirements and providing for them is one
> of the things that makes a piece of software a pleasure to use.
> "Batteries included" and all that.
> 
> Anyway, I seem to be flogging a horse which, if not dead, is surely
> mortally wounded.  If quote_qualified_ident isn't desired, perhaps you
> can still use the regression test I included for quote_ident in the
> patch.  The test is functional as a standalone item, and seems to fill
> a gap.

I think we did our best to find requests for this feature.  If they ever
pop up in the future we can always recover this patch from the archives.
Sorry.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] Script binaries renaming

2008-03-25 Thread Bruce Momjian
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Where are we on this?  Tom thinks we don't want this.  TODO has:
> >   * Prefix command-line utilities like createuser with 'pg_'
> > http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php
> 
> It wasn't just me; quite a few people were dubious about it when the
> patch was submitted.  See the thread here:
> http://archives.postgresql.org/pgsql-patches/2007-07/msg00055.php

True.

> > One idea is to keep the existing commands and just add pg_* (or pg*) to
> > additional versions, with the idea that the original versions will be
> > removed some day.
> 
> AFAICS the only argument for doing this is to eliminate confusion and
> potential conflicts, which means that we get no benefit at all until we
> actually do remove the old names.  So if we're going to do this, we have
> to make a commitment that we're going to remove the old names within the
> reasonably foreseeable future (say, about two releases out).
> 
> Are we really prepared to break everyone's scripts for this?

Uh, I think it is hard to make a case that 'createuser' is an
appropriate name for a Postgres utility.  On the other hand, we haven't
had many complaints about it, which is kind of odd.

I feel people can always symlink in the old names if they still want
them after we remove the old names.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

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


Re: [HACKERS] Script binaries renaming

2008-03-25 Thread Marc G. Fournier
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1



- --On Tuesday, March 25, 2008 22:51:53 -0400 Bruce Momjian <[EMAIL PROTECTED]> 
wrote:

> Uh, I think it is hard to make a case that 'createuser' is an
> appropriate name for a Postgres utility.  On the other hand, we haven't
> had many complaints about it, which is kind of odd.

If nobody has ever complained, what is the reason for the change?  How many ppl 
are going to complain because the commands they are used to "suddenly stop 
existing"?

- -- 
Marc G. FournierHub.Org Hosting Solutions S.A. (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.4 (FreeBSD)

iD8DBQFH6dPA4QvfyHIvDvMRAj2AAKDQ2r2L8ztHDeUhBBSD10VwbttXugCgksd8
g8Tq27/AorIuM1Yo8nh1vbc=
=JnjX
-END PGP SIGNATURE-


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