date:20080729

Am Monday, 28. July 2008 schrieb Tom Lane:
 But to define such a domain, you'd have to commit to a case-insensitive
 version of a specific collation, no?  citext currently means case
 insensitive version of whatever the database's default collation is.

So in the future, someone using citext with lc_collate = en_US attempting to 
upgrade would then define

CREATE DOMAIN citext AS text COLLATION [EMAIL PROTECTED]

And yes, you would potentially have different definitions of this citext 
domain in different database clusters, depending on what configuration you 
are upgrading from, but I don't see that as a problem.  It is the natural 
thing to do.

-- 
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] Python 2.5 vs the buildfarm

Am Monday, 28. July 2008 schrieb Tom Lane:
 I notice that we now have four buildfarm members failing in the 8.1
 branch, with symptoms indicating that they are running python 2.5,
 which pre-8.2 plpython has known incompatibilities with.  I think
 it's high time we back-patched those compatibility fixes

Why would anyone running PostgreSQL 8.1 in production upgrade their stable 
server to Python 2.5, and remove Python 2.4 in the process?  What is the use 
case, except build farm maintainers can't keep their environments stable?  
Have we had any complaints from the field about this?

-- 
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, oprrest function for text search take 2

2008-07-29 Thread Heikki Linnakangas


Jan Urbański wrote:
Another thing are cstring_to_text_with_len calls. I'm doing them so I 
can use bttextcmp in bsearch(). I think I could come up with a dedicated 
function to return text Datums and WordEntries (read: non-NULL 
terminated strings with a given length).


Just keep them as cstrings and use strcmp. We're only keeping the array 
sorted so that we can binary search it, so we don't need proper 
locale-dependent collation. Note that we already assume that two strings 
('text's) are equal if and only if their binary representations are 
equal (texteq() uses strcmp).


--
  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] patch: Add a separate TRUNCATE permission

Am Sunday, 27. July 2008 schrieb Robert Haas:
 Here's a patch implementing the TODO item Add a separate TRUNCATE
 permission.  Hopefully I found all the bits that needed to be
 modified to make this work.

Looks very good to me.

-- 
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] [WIP] patch - Collation at database level

Am Tuesday, 1. July 2008 schrieb Radek Strnad:
 I'm sending part of the code that I've done and is available for reviewing
 and I'm asking for your comments and some help because I'm new to
 PostgreSQL.

 Proposal: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00857.php

 The code contains changes of parser, pg_collation catalog, parsenodes.h for
 CREATE COLLATION... and DROP COLLATION statements.

This patch is a small start on the way to adding a catalog and some SQL 
statements that add and remove entries from it.  But I don't see any 
collation support here, which is about 99% of the work that is left to do.  
Where are the collations going to come from?  Have the various build and 
distributions issues been thought about?  How are they going to be configured 
(not the SQL syntax, but how will the configuration be applied)?  How are the 
collations going to be applied at run-time?  How are you going to handle 
locale and encoding conflicts?  I also think that the clauses you have 
attached to your CREATE COLLATION statement (case-insensitive, 
accent-insensitive) are an oversimplification of reality.  I suggest you look 
up the Unicode collation algorithm to learn about who collations work in 
practice.

In my opinion, you are starting this project from the wrong end.  I would 
suggest you approach it like this:

- Find some collation implementations
- Patch PostgreSQL to link with them
- Patch PostgreSQL to apply them for comparison
- Implement system catalog to configure them
- Implement SQL statements to manipulate the system catalog

-- 
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] [PATCH] \ef function in psql

2008-07-29 Thread Abhijit Menon-Sen

At 2008-07-23 10:50:03 -0400, [EMAIL PROTECTED] wrote:

  What would the function return? CREATE OR REPLACE FUNCTION ...?
 
 I think I'd go with CREATE FUNCTION for simplicity.

OK, I have a mostly working pg_get_functiondef now, and some questions
about the remaining pieces:

1. Why is prorows a float4? Should I print it that way, i.e. %f?

2. Can I print the contents of proconfig as just SET %s? It seems
   to work for the variables I've tried (e.g. DateStyle=iso), but I
   wonder if they'll always be quoted correctly (i.e., if the split
   on '=' thing pg_dump does is necessary for an 8.4 function).

3. Is there a function I can use from ruleutils.c to do dollar quoting
   of prosrc/probin? If not, and I have to write one, where should it
   live?

4. What exactly is probin? Do I need to worry about it at all?

Thanks.

-- ams

-- 
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] [PATCH] \ef function in psql

2008-07-29 Thread Asko Oja

Marko is talking about types created with CREATE TYPE

CREATE FUNCTION fraud.get_user_status(
i_key_user text
) RETURNS ret_get_user_status AS
$$

Current pg_dump annoyingly removes schem reference from type.

On Wed, Jul 23, 2008 at 6:19 PM, Tom Lane [EMAIL PROTECTED] wrote:

 Marko Kreen [EMAIL PROTECTED] writes:
  [ re pg_get_functiondef ]

  Please make it use full qualified names (schema.name) for both
  function name and result types.  Current search_path juggling
  the pg_dump does is major PITA.

 Qualifying the function name seems like a good idea, but I'd advise
 against tinkering with the datatype references.  It'll be hard to
 do correctly and it will make things very substantially uglier.
 Do you really want to show, eg, pg_catalog.int4 rather than integer?

 If you leave the backend code do what it wants to do here, the only
 way that there would be a problem is if someone changed their
 search_path in between pg_get_functiondef and trying to re-load the
 function definition.  Which certainly ain't gonna happen for \ef,
 and it seems a bit implausible for any other use-case either.

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

Relicensed and downloadable (Re: [HACKERS] Protocol 3, Execute, maxrows to return, impact?)

2008-07-29 Thread Stephen R. van den Berg

Limited offer, get it while it's hot:

http://admin.cuci.nl/pgsql.pike.driver.tar.gz.bin

I relicensed it under BSD now, put up proper copyright notices, and
included the accelleratorclass for amusement (the module works without
the accellerator class, but it's about 10 times slower than libpq then).

For anyone trying to understand Pike, the important points probably are
that:
- ({ 1, 2, 3, 4 }) is an array.
- m = ([key1:13, key2:14, key3:15]) is a mapping, i.e. m-key1
  or m[key1] both evaluate to 13.

The rest is rather similar to C++/Java.

See http://pike.roxen.com/ for more documentation and references,
try git://git.cuci.nl/pike as the source repository,
http://pike.ida.liu.se/docs/ for documentation and tutorials.

The fetch_row() function actually returns an array for each row, where
the individual columns are already parsed and converted into the native
Pike formats for handling.
-- 
Sincerely,
   Stephen R. van den Berg.

People who think they know everything are annoying to those of us who do.

-- 
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] [PATCH] \ef function in psql

2008-07-29 Thread Asko Oja

Not so sure about omitting OR REPLACE. In my experience it is more often
needed than not. Main argument for omitting might be to protect hackers from
carelesse users :)

On Wed, Jul 23, 2008 at 5:50 PM, Tom Lane [EMAIL PROTECTED] wrote:

 Abhijit Menon-Sen [EMAIL PROTECTED] writes:
  At 2008-07-17 18:28:19 -0400, [EMAIL PROTECTED] wrote:
  It wouldn't take a whole lot to convince me that a pg_get_functiondef
  would be useful, although I don't foresee either of those applications
  wanting to use it because of their backward-compatibility constraints.

  What would the function return? CREATE OR REPLACE FUNCTION ...? Would
  that be good enough for everyone who might want to call it?

 I think I'd go with CREATE FUNCTION for simplicity.  It would be easy
 enough for something like \ef to splice in OR REPLACE before shipping
 the command back to the server.

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] [WIP] patch - Collation at database level

2008-07-29 Thread Gregory Stark

Peter Eisentraut [EMAIL PROTECTED] writes:

 In my opinion, you are starting this project from the wrong end.  I would 
 suggest you approach it like this:

 - Find some collation implementations
 - Patch PostgreSQL to link with them

Well I think the feeling is that we may as well start with the lowest common
denominator of libc's collation implementation. It's the only one everyone's
going to have. Later adding compile-time options to use a different library
and different function calls might be useful but a lot of people are unhappy
about the idea of *requiring* a major outside library for this.

 - Patch PostgreSQL to apply them for comparison

Er, yes. Well we do that already but the tricky bit is keeping track of
multiple collations and applying the right one for each comparison.

So we do need the concept of multiple collations and the syntax to select a
collation for each ordering operation.



-- 
  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] patch: Add a separate TRUNCATE permission

2008-07-29 Thread Stephen Frost

* Peter Eisentraut ([EMAIL PROTECTED]) wrote:
 Am Sunday, 27. July 2008 schrieb Robert Haas:
  Here's a patch implementing the TODO item Add a separate TRUNCATE
  permission.  Hopefully I found all the bits that needed to be
  modified to make this work.
 
 Looks very good to me.

We've been through this before, I believe..  The concern is that it
chews up another bit in the acl structure, leaving not a huge number
left.  My suggested approach to fixing this was to split the grantable
bits up from the regular usage bits.  That's, unfortunately, a
non-trivial amount of work, however.

Perhaps just having a plan for how we would increase the number of
available bits would be enough to go ahead and add this option though?
I'd certainly like to see a truncate permission, I wrote a patch for it
myself back in January of 2006.  That thread starts here:

http://archives.postgresql.org/pgsql-patches/2006-01/msg00028.php

I think someone else submitted a patch for it last year too, actually.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Python 2.5 vs the buildfarm

2008-07-29 Thread Greg Sabino Mullane


-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


 I notice that we now have four buildfarm members failing in the 8.1
 branch, with symptoms indicating that they are running python 2.5,
 which pre-8.2 plpython has known incompatibilities with.  I think
 it's high time we back-patched those compatibility fixes

 Why would anyone running PostgreSQL 8.1 in production upgrade their stable
 server to Python 2.5, and remove Python 2.4 in the process?

Because the keep their operating system up to date, and because we still
do not have any sort of in-place upgrade.

 What is the use case, except build farm maintainers can't keep their
 environments stable?

What's not stable about having Python 2.5?

 Have we had any complaints from the field about this?

Probably due to lack of plpython use more than anything else, but I don't
see what the alternative is: hard-code a hack into the buildfarm code?
Keep emailing individual buildfarm members and asking them to make special
exceptions? The buildfarm is meant to test many different combinations of
factors that may or may not be seen in the field, and in this case it is
doing that job admirably.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200807290721
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkiO/bUACgkQvJuQZxSWSsissgCdFVaiZ3AvGTzCChrVa6JAAUAf
TYcAoON6x7YJm8YIJpem7KwaV/D96oSz
=ERo0
-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

Re: [HACKERS] Python 2.5 vs the buildfarm

Am Tuesday, 29. July 2008 schrieb Greg Sabino Mullane:
  Why would anyone running PostgreSQL 8.1 in production upgrade their
  stable server to Python 2.5, and remove Python 2.4 in the process?

 Because the keep their operating system up to date, and because we still
 do not have any sort of in-place upgrade.

And neither does Python.  Someone taking the step from Python 2.4 to 2.5 might 
as well do a major upgrade of PostgreSQL as well.

  What is the use case, except build farm maintainers can't keep their
  environments stable?

 What's not stable about having Python 2.5?

I mean stable to mean does not change (unnecessarily).  When PostgreSQL 
8.1 was released, Python 2.5 was not yet out.  So whoever was installing 
PostgreSQL 8.1 must have done it on a system that had Python 2.4.  Why not 
keep that?

In fact, someone upgrading such a system would have to *rebuild* PostgreSQL.  
Who does that on a production system?

 The buildfarm is meant to test many different combinations of
 factors that may or may not be seen in the field, and in this case it is
 doing that job admirably.

Yes indeed.  The test results say: This combination doesn't work; use some of 
these other alternatives.  Why not leave it at that?

-- 
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 of SE-PostgreSQL patches [try#2]

2008-07-29 Thread KaiGai Kohei


Hello,

The following patches are updated ones of SE-PostgreSQL toward the HEAD of CVS.

[1/4] 
http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r958.patch
[2/4] 
http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r958.patch
[3/4] 
http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r958.patch
[4/4] 
http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r958.patch

By the way,
http://wiki.postgresql.org/wiki/CommitFest:2008-07

| Peter says: checking with Solaris engineers about compatibility with
| Solaris TX; will continue review throughout August


Jon, do you have anything to comment about PGACE security framework?
(Show the src/include/security/pgace.h)

It has been reworked a bit from this April when we had an offline meeting,
but I think its impact is not significant for its portability.

It can provide its guest subsystem (like SE-PostgreSQL) a series of hooks to
make a decision and facilities to manage text represented security attribute
of database objects.
IIUC, we concluded these foundations are also enough to port SolarisTX feature.

If you find out something lacks/conflicts, please tell me.

Thanks,


KaiGai Kohei wrote:

Hi,

I updated the series of patches, as follows:

[1/4] Core facilities of PGACE/SE-PostgreSQL

http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r953.patch 



[2/4] --security-context option of pg_dump/pg_dumpall

http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r953.patch 



[3/4] Default security policy for SE-PostgreSQL

http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r953.patch 



[4/4] Documentation updates

http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r953.patch


List of updates:
* --enable-selinux option of pg_dump/pg_dumpall are replaced
  by --security-context option.
* A new GUC parameter of pgace_security_feature is added to show
  what feature is worked on PGACE security framework.
* pg_ace_dump() hooks are added to src/bin/pg_dump/pg_ace_dump.h
  to abstract security attribute dumping effort.
* An extended syntax of CONTEXT = '...' is replaced by
  SECURITY_CONTEXT = '...'.
* The sources of security policy module are moved from contrib/ to
  src/backend/security/sepgsql/policy.
* The prefix of interfaces in the default security policy are changed
  to sepgsql_*() from sepostgresql_*()
* Using integer value as a condition is replaced as follows:
if (!strcmp(..)) - if (strcmp(...) == 0)
* Two potential bug fixes:
   1. Unconditional Assert() in sepgsql_avc_reclaim().
   2. relkind checks are lacked in sepgsqlSetDefaultContext().

The patch of core facilities is unchanged expect for the new GUC parameters
and above two minor bug fixes.

And I have a question. Is it legal to use a pointer value as a condition,
like `while (!pointer) ...' ?

Thanks for youe reviewing.


KaiGai Kohei wrote:

Thanks for your reviewing.

Peter Eisentraut wrote:

Am Donnerstag, 26. Juni 2008 schrieb KaiGai Kohei:
The following patch set (r926) are updated one toward the latest CVS 
head,

and contains some fixes in security policy and documentation.


OK, I have quickly read through these patches.  They look very nice, 
so I am optimistic we can get through this.


First of all, now would be a good time if someone out there really 
wants to object to this feature in general.  It will probably always 
be a niche feature.  But all the code is hidden behind ifdefs or 
other constructs that a compiler can easily hide away (or we can make 
it so, at least).


Here is a presentation from PGCon on SE-PostgreSQL: 
http://www.pgcon.org/2008/schedule/events/77.en.html


Are there any comments yet from the (Trusted)Solaris people that 
wanted to evaluate this approach for compatibility with their approach?


In this April, I had a face-to-face meeting with Trusted Solaris people
to discuss SE-PostgreSQL and PGACE framework, and concluded that PGACE
framework provides enough facilities for both operating systems.

I modified several hooks from CommitFest:May, however, its fundamental
structures are unchanged.

In general, are we OK with the syntax CONTEXT = '...'?  I would 
rather see something like SECURITY CONTEXT '...'.  There are a lot of 
contexts, after all.


If we change it, I prefer SECURITY_CONTEXT = '...' style, because it 
enables
to represent the left hand with a single token and make PGACE hooks 
simpler.

I also agree the CONTEXT has widespread meanings and to be changed here.

This will also add a system column called security_context.  I think 
that is OK.


Thanks,


In the pg_dump patch:

spelling mistake tuen on/off


I'll fix it soon.


Evil coding style: if (strcmp(SELINUX_SYSATTR_NAME, 
security_sysattr_name)) -- compare the result with 0 please.


OK, I'll fix it and check my implementations in other place.


The above code appears to assume that security_sysattr_name never 
changes, but

then why do we need

Re: [HACKERS] patch: Add a separate TRUNCATE permission

Am Tuesday, 29. July 2008 schrieb Stephen Frost:
 I'd certainly like to see a truncate permission, I wrote a patch for it
 myself back in January of 2006.  That thread starts here:

 http://archives.postgresql.org/pgsql-patches/2006-01/msg00028.php

 I think someone else submitted a patch for it last year too, actually.

Well, that certainly indicates some demand.

I think we should worry about adding more bits when we need them.  It's 
certainly possible to add more bits, so it is not like we need to save the 
ones we have forever.

-- 
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] patch: Add a separate TRUNCATE permission

2008-07-29 Thread Robert Haas

 We've been through this before, I believe..  The concern is that it
 chews up another bit in the acl structure, leaving not a huge number
 left.  My suggested approach to fixing this was to split the grantable
 bits up from the regular usage bits.  That's, unfortunately, a
 non-trivial amount of work, however.

Writing this patch was more of a learn the PostgreSQL source project
for me than a feature that I, personally, have a need for, so I have
no dog in this race other than that, if the feature was actually not
wanted, then it shouldn't be on the TODO list, possibly causing people
to waste time implementing it.  :-)

The question of using up all the bits seems purely speculative to me
at this point.  I agree that we don't want to fritter them away, but
this is the only TODO item proposes using any of those bits.  Tom's
complaint about your patch seems to have been that it uses three of
the five remaining ACL bits; this patch uses only one, and arguably
TRUNCATE is more like a DML command than a utility command (which, as
Tom pointed out, there are certainly too many of to ever allocate a
bit for each one).

I would argue that if we're ever going to start adding permissions for
things like those types of utility command then we ought to create
some separate mechanism for storing permissions that are not likely to
need to be checked very frequently.  Then things like INSERT and
UPDATE that happen often can benefit from a 16-bit field, and things
like ANALYZE and ADD COLUMN that are only executed occasionally can
use a separate, more heavy-weight mechanism.

In any event, however we ultimately decide to implement it, we don't
need to solve this problem now.

 I think someone else submitted a patch for it last year too, actually.

I talked about submitting one last year but didn't actually do it
since it seemed to be the wrong point in the development cycle.

...Robert

-- 
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] patch: Add a separate TRUNCATE permission

2008-07-29 Thread Stephen Frost

* Peter Eisentraut ([EMAIL PROTECTED]) wrote:
 Am Tuesday, 29. July 2008 schrieb Stephen Frost:
  I'd certainly like to see a truncate permission, I wrote a patch for it
  myself back in January of 2006.  That thread starts here:
 
  http://archives.postgresql.org/pgsql-patches/2006-01/msg00028.php
 
  I think someone else submitted a patch for it last year too, actually.
 
 Well, that certainly indicates some demand.
 
 I think we should worry about adding more bits when we need them.  It's 
 certainly possible to add more bits, so it is not like we need to save the 
 ones we have forever.

I would agree with this.  Others?

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] patch: Add a separate TRUNCATE permission

2008-07-29 Thread Stephen Frost

* Robert Haas ([EMAIL PROTECTED]) wrote:
  We've been through this before, I believe..  The concern is that it
  chews up another bit in the acl structure, leaving not a huge number
  left.  My suggested approach to fixing this was to split the grantable
  bits up from the regular usage bits.  That's, unfortunately, a
  non-trivial amount of work, however.
 
 Writing this patch was more of a learn the PostgreSQL source project
 for me than a feature that I, personally, have a need for, so I have
 no dog in this race other than that, if the feature was actually not
 wanted, then it shouldn't be on the TODO list, possibly causing people
 to waste time implementing it.  :-)

Oh, it's wanted, I've been asking after it for 3 years.  The problem is
just in getting people to agree to address it in this way, or if not to
accept some other way to address it (in which case the TODO should be
updated to reflect that).

 The question of using up all the bits seems purely speculative to me
 at this point.  I agree that we don't want to fritter them away, but
 this is the only TODO item proposes using any of those bits.  Tom's
 complaint about your patch seems to have been that it uses three of
 the five remaining ACL bits; this patch uses only one, and arguably
 TRUNCATE is more like a DML command than a utility command (which, as
 Tom pointed out, there are certainly too many of to ever allocate a
 bit for each one).

With the advent of autovacuum, my additional permissions for vacuum and
analyze are much less necessary.  I'm fine with just adding a permission
for truncate.

 I would argue that if we're ever going to start adding permissions for
 things like those types of utility command then we ought to create
 some separate mechanism for storing permissions that are not likely to
 need to be checked very frequently.  Then things like INSERT and
 UPDATE that happen often can benefit from a 16-bit field, and things
 like ANALYZE and ADD COLUMN that are only executed occasionally can
 use a separate, more heavy-weight mechanism.

That's along the same lines as I was proposing, except I would put all
of the grantable options in the not often needed column, which would
give us a full 32-bit field for common permissions.

 In any event, however we ultimately decide to implement it, we don't
 need to solve this problem now.

Agreed.

  I think someone else submitted a patch for it last year too, actually.
 
 I talked about submitting one last year but didn't actually do it
 since it seemed to be the wrong point in the development cycle.

That might have been it, not sure.

Thanks!

Stephen


signature.asc
Description: Digital signature

[HACKERS] pg_regress inputdir

2008-07-29 Thread Jorgen Austvik - Sun Norway


Hi,

with regards to --inputdir, --srcdir, --outputdir and --schedule, 
pg_regress does something like this:


convert files in srcdir/input, and place them in ./sql
convert files in srcdir/output, and place them in ./expected
read schedule from schedule
for each test in schedule:
   read test from inputdir/sql/testname.sql
   read expected result from inputdir/expected/testname.out
   write results to outputdir/results/testname.out

My problem when running pg_regress standalone, is that converted files 
are written to e.g. ./sql, but read from e.g. inputdir/sql, which 
makes the --inputdir parameter pretty unusable if it is set to something 
different from some path leading to cwd.


Illustrated with code:

Writing converted source file (pg_regress.s:493):
snprintf(destfile, MAXPGPATH, %s/%s.%s, dest, prefix, suffix);

(Where dest is sql or expected.)

Reading files (pg_regress_main.c:37+38):
snprintf(infile, sizeof(infile), %s/sql/%s.sql, inputdir, testname);

The attached patch makes pg_regress write converted files to 
inputdir/sql and inputdir/expected, which is one way to make it read 
and write to the same directory. Tested on Solaris x86 with pgsql make 
check and standalone.


-J
--

Jørgen Austvik, Software Engineering - QA
Sun Microsystems Database Group

http://blogs.sun.com/austvik/
http://www.austvik.net/
Index: src/test/regress/pg_regress.c
===
RCS file: /projects/cvsroot/pgsql/src/test/regress/pg_regress.c,v
retrieving revision 1.45
diff -u -r1.45 pg_regress.c
--- src/test/regress/pg_regress.c	17 May 2008 20:02:01 -	1.45
+++ src/test/regress/pg_regress.c	29 Jul 2008 12:36:38 -
@@ -490,7 +490,7 @@
 		/* build the full actual paths to open */
 		snprintf(prefix, strlen(*name) - 6, %s, *name);
 		snprintf(srcfile, MAXPGPATH, %s/%s, indir, *name);
-		snprintf(destfile, MAXPGPATH, %s/%s.%s, dest, prefix, suffix);
+		snprintf(destfile, MAXPGPATH, %s/%s/%s.%s, inputdir, dest, prefix, suffix);
 
 		infile = fopen(srcfile, r);
 		if (!infile)

-- 
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] Python 2.5 vs the buildfarm

2008-07-29 Thread Zdenek Kotala


Peter Eisentraut napsal(a):

Am Tuesday, 29. July 2008 schrieb Greg Sabino Mullane:

Why would anyone running PostgreSQL 8.1 in production upgrade their
stable server to Python 2.5, and remove Python 2.4 in the process?

Because the keep their operating system up to date, and because we still
do not have any sort of in-place upgrade.


And neither does Python.  Someone taking the step from Python 2.4 to 2.5 might 
as well do a major upgrade of PostgreSQL as well.



What is the use case, except build farm maintainers can't keep their
environments stable?

What's not stable about having Python 2.5?


I mean stable to mean does not change (unnecessarily).  When PostgreSQL 
8.1 was released, Python 2.5 was not yet out.  So whoever was installing 
PostgreSQL 8.1 must have done it on a system that had Python 2.4.  Why not 
keep that?


+1 I think there is more important and more logical things for backporting like 
system timezone patch.


Problem what I see there is that buildfarm are not stable. I think stability of 
environment is one of basic requirements for buildfarm server. The major 
advantages is heterogeneity of installation but if everybody update system up to 
the atest version than finally we will get unified servers installation. 
However, many machines are also production machines and they usually need to 
update sometimes.  I think that any SW upgrade should be logged. It helps to 
track issues.


Zdenek


--
Zdenek Kotala  Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql


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

Re: Relicensed and downloadable (Re: [HACKERS] Protocol 3, Execute, maxrows to return, impact?)

2008-07-29 Thread Stephen R. van den Berg

Stephen R. van den Berg wrote:
The fetch_row() function actually returns an array for each row, where
the individual columns are already parsed and converted into the native
Pike formats for handling.

A typical (low level) usage sequence in Pike looks like this:

   object db;
   object q;
   array row;

   db = Sql.pgsql(host,database,user,password);

   q = db-query(SELECT * FROM a);

   while(row = q-fetch_row())
  write(Processing row %O\n,row);

   q = db-query(SELECT * FROM b);

etc.
-- 
Sincerely,
   Stephen R. van den Berg.

People who think they know everything are annoying to those of us who do.

-- 
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] odd output in restore mode

2008-07-29 Thread Heikki Linnakangas


Andrew Dunstan wrote:



Greg Smith wrote:

On Wed, 23 Jul 2008, Kevin Grittner wrote:


In our scripts we handle this by copying to a temp directory on the
same mount point as the archive directory and doing a mv to the
archive location when the copy is successfully completed. I think
that this even works on Windows. Could that just be documented as a
strong recommendation for the archive script?


This is exactly what I always do. I think the way cp is shown in the 
examples promotes what's really a bad practice for lots of reasons, 
this particular problem being just one of them.


I've been working on an improved archive_command shell script that I 
expect to submit for comments and potential inclusion in the 
documentation as a better base for other people to build on. This is 
one of the options for how it can operate. It would be painful but not 
impossible to convert a subset of that script to run under Windows as 
well, at least enough to cover this particular issue.


A Perl script using the (standard) File::Copy module along with the 
builtin function rename() should be moderately portable. It would to be 
nice not to have to maintain two scripts.


It's also not very nice to require a Perl installation on Windows, just 
for a replacement of Copy. Would a simple .bat script work?


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

[HACKERS] Location of ident code

2008-07-29 Thread Magnus Hagander

Does anybody know if there's a reason why the ident code lives in hba.c,
but all other authentication code lives in auth.c? Is it just the fact
that ident supports the pg_ident.conf file, for which the processing is
located there, or is there some deeper reason?

I came across this while looking at extending the usemap stuff to work
with other authentication methods as well, and cleaning up some of the
parameter parsing in pg_hba.conf, per some of the discussions at pgcon.
So if nobody knows any other reasons for it to be there, I'd like to
move the ident stuff over to auth.c for clarity. Any objections to that
in principle?

//Magnus

-- 
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] pg_regress inputdir

2008-07-29 Thread Alvaro Herrera

Jorgen Austvik - Sun Norway wrote:

 The attached patch makes pg_regress write converted files to  
 inputdir/sql and inputdir/expected, which is one way to make it read  
 and write to the same directory. Tested on Solaris x86 with pgsql make  
 check and standalone.

I think this breaks VPATH builds in both letter and spirit.  Why do you
need this anyway?

-- 
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] Python 2.5 vs the buildfarm

Peter Eisentraut [EMAIL PROTECTED] writes:
 Am Tuesday, 29. July 2008 schrieb Greg Sabino Mullane:
 What's not stable about having Python 2.5?

 I mean stable to mean does not change (unnecessarily).

I really don't understand Peter's objection here.  This thread has
already consumed more person-time than I spent on applying the
back-patch.  I note also that, in fact, the code that was wrong was
wrong according to pre-2.5 python as well.  It accidentally failed
to fail on common architectures, but it was certainly doing things
that are undefined according to the C standard.  So in my eyes this
was a bug fix. 

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] patch: Add a separate TRUNCATE permission

Robert Haas [EMAIL PROTECTED] writes:
 The question of using up all the bits seems purely speculative to me
 at this point.  I agree that we don't want to fritter them away, but
 this is the only TODO item proposes using any of those bits.  Tom's
 complaint about your patch seems to have been that it uses three of
 the five remaining ACL bits;

Yeah, exactly, and it also started us down the path of wanting a
separate permission bit for every DDL command.  I don't have a
problem with the idea of just eating one bit for TRUNCATE.  That
would leave us with four free out of sixteen, which hardly seems
like the usage level at which to start sounding alarm bells.

I believe it would be easy and cheap to adjust the representation
of ACLs to support 32 permissions instead of 16; so I won't cry
if we someday push past 16.  Beyond that, though, things get very
much more expensive and complicated (as per the speculations in
this thread).  So what I was really resisting was the notion of
permission per DDL command --- I don't want to go that way.

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] Python 2.5 vs the buildfarm

2008-07-29 Thread Gregory Stark

Tom Lane [EMAIL PROTECTED] writes:

 Peter Eisentraut [EMAIL PROTECTED] writes:
 Am Tuesday, 29. July 2008 schrieb Greg Sabino Mullane:
 What's not stable about having Python 2.5?

 I mean stable to mean does not change (unnecessarily).

 I really don't understand Peter's objection here.  This thread has
 already consumed more person-time than I spent on applying the
 back-patch.  

Well I certainly wouldn't expect us to feel obligated to spend much effort
making 8.1 work with a new Redhat release, for example. We would just say 8.1
is only supported on those systems it was supported on when it was released.

But if you're happy doing the work I can't see any reason to stop you either.

-- 
  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] Do we really want to migrate plproxy and citext into PG core distribution?

Peter Eisentraut [EMAIL PROTECTED] writes:
 Am Monday, 28. July 2008 schrieb Tom Lane:
 But to define such a domain, you'd have to commit to a case-insensitive
 version of a specific collation, no?  citext currently means case
 insensitive version of whatever the database's default collation is.

 So in the future, someone using citext with lc_collate = en_US attempting to 
 upgrade would then define
 CREATE DOMAIN citext AS text COLLATION [EMAIL PROTECTED]
 And yes, you would potentially have different definitions of this citext 
 domain in different database clusters, depending on what configuration you 
 are upgrading from, but I don't see that as a problem.  It is the natural 
 thing to do.

I'm still not completely convinced that this would be smooth in
practice; for instance there might be situations where you wanted to
define citext but didn't know the target database collation.  Still,
I have to concede that it's probably an adequate answer, or at least
close enough that the objection to including citext now doesn't hold up.

It seems that the majority opinion is in favor of including citext
in contrib, so I will go work on that now.

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] Do we really want to migrate plproxy and citext into PG core distribution?


On Jul 28, 2008, at 18:31, Tom Lane wrote:


To upgrade from citext, I expect
that what one will have to do is to alter the column to change its
data type from citext to TEXT + collation.


What I'm wondering is how closely that will match the semantics of the
contrib module ...


By semantics do you mean behavior, in terms of how closely operator  
comparisons will return the same results? I have no idea, personally,  
but it's no worse then TEXT, is it? The use of TEXT and LOWER() being  
what people are doing now?


Best,

David

--
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] Python 2.5 vs the buildfarm

Gregory Stark [EMAIL PROTECTED] writes:
 Tom Lane [EMAIL PROTECTED] writes:
 I really don't understand Peter's objection here.  This thread has
 already consumed more person-time than I spent on applying the
 back-patch.  

 Well I certainly wouldn't expect us to feel obligated to spend much effort
 making 8.1 work with a new Redhat release, for example. We would just say 8.1
 is only supported on those systems it was supported on when it was released.

Well, it would certainly depend on how much effort was involved to make
it work.  In this case, I drew the line at messing with autoconf ;-) ...
otherwise I might've tried to fix 7.4 as well.

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] TABLE-function patch vs plpgsql

2008-07-29 Thread Hannu Krosing

On Thu, 2008-07-17 at 19:13 -0400, Tom Lane wrote:
 I've been working on the TABLE-function patch, and I am coming to the
 conclusion that it's really a bad idea for plpgsql to not associate
 variables with output columns --- that is, I think we should make
 RETURNS TABLE columns semantically just the same as OUT parameters.

I just looked at recent cahnges in pl/python, and found out that RETURNS
TABLE is _NOT_ semantically just the same as OUT parameters, at least at
API level.

Why can't it be ? 

Why is PROARGMODE_TABLE needed at all ?

 4. It's a whole lot easier to explain things if we can just say that
 OUT parameters and TABLE parameters work alike.  This is especially
 true when they actually *are* alike for all the other available PLs.

It would be nice, if they were the same at API level as well.


Hannu


-- 
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] TABLE-function patch vs plpgsql

Hannu Krosing [EMAIL PROTECTED] writes:
 Why is PROARGMODE_TABLE needed at all ?

Personally I would rather not have it, but Pavel insists it's needed
for standards compliance in PL/PSM, where output TABLE columns are not
supposed to have names visible within the function.

One reason to have it is so we can distinguish the correct way to
reverse-list an output parameter (as OUT or as a table result column).
Although we could equally well solve that with an extra bool column in
pg_proc instead of redefining proargmodes, as long as you're willing to
accept the reasonable restriction that you can't mix the two styles of
declaring output parameters.  In principle PL/PSM could look at such a
bool too, so there's more than one way to do it.

The feeling I had about it was that if we were adding
PROARGMODE_VARIADIC in 8.4 then there wasn't any very strong argument
not to add PROARGMODE_TABLE; any code looking at proargmodes is going
to need updates anyway.

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] TABLE-function patch vs plpgsql

2008-07-29 Thread Asko Oja

Tom RETURNS TABLE columns semantically just the same as OUT parameters.

I hope you are not proposing to create another case of crippled OUT
parameters that are quite problematic to use together with inline sql or has
it gotten fixed on the road (we are still using 8.2 on most of our servers).

On Fri, Jul 18, 2008 at 2:13 AM, Tom Lane [EMAIL PROTECTED] wrote:

I've been working on the TABLE-function patch, and I am coming to the
conclusion that it's really a bad idea for plpgsql to not associate
variables with output columns --- that is, I think we should make
RETURNS TABLE columns semantically just the same as OUT parameters.
Here are some reasons:

1. It's ludicrous to argue that standards compliance requires the
behavior-as-submitted. plpgsql is not specified by the SQL standard.

2. Not having the parameter names available means that you don't have
access to their types either, which is a big problem for polymorphic
functions. Read the last couple paragraphs of section 38.3.1:

http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
as well as the following 38.3.2. How would you do those things with
a polymorphic TABLE column?

3. Not treating the parameters as assignable variables makes RETURN NEXT
nearly worthless in a TABLE function. Since they're not assignable,
you can't use the parameterless form of RETURN NEXT (which'd return
the current values of the variables). The only alternative available
is to return a record or row variable; but there's no convenient way
to declare such a variable, since after all the whole point here is
that the function's output rowtype is anonymous.

4. It's a whole lot easier to explain things if we can just say that
OUT parameters and TABLE parameters work alike. This is especially
true when they actually *are* alike for all the other available PLs.

If we insist on the current definition then we are eventually going to
need to kluge up some solutions to #2 and #3, which seems like make-work
to me when we already have smooth solutions to these problems for
OUT parameters.

Comments?

For the archives, here is the patch as I currently have it (with the
no-plpgsql-variables behavior). But unless I hear a good argument
to the contrary, I'm going to change that part before committing.

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] Do we really want to migrate plproxy and citext into PG core distribution?


On Jul 29, 2008, at 08:58, Tom Lane wrote:


It seems that the majority opinion is in favor of including citext
in contrib, so I will go work on that now.


Damn, I was away from mail yesterday; sorry. I have some revisions,  
mostly improving casting compatibility with text. Tom, have you  
committed anything yet? Shall I pull my improved patch together and  
send it today (I was still writing tests and tweaking things to get  
the casts right…).


Thanks,

David
--
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] Python 2.5 vs the buildfarm

2008-07-29 Thread Greg Smith


On Tue, 29 Jul 2008, Peter Eisentraut wrote:

Someone taking the step from Python 2.4 to 2.5 might as well do a major 
upgrade of PostgreSQL as well.


It takes a few seconds to upgrade Python versions, and I know I've 
installed Python 2.5 from source on a production server before while not 
touching anything else (after going through that process on a staging 
duplicate).


How long it takes to upgrade PostgreSQL is proportional to the size of 
your database, and that can easily take far longer than an acceptable 
downtime window.  This is how you can end up companies who are up to date 
on everything else on their server, but still running an old PostgreSQL.


I once watched a company roll out a shiny new server (on the same 
architecture) to improve performance, with the newer Linux distribution 
required to support that hardware.  But they downgraded to an older PG 
version so it could still run against the existing database, on an 
external array, because that was too big to dump and reload before the 
system had to be back up.  As Greg was pointing out, such craziness really 
does happy specifically because there's no good upgrade in place mechanism 
available.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] TABLE-function patch vs plpgsql

2008-07-29 Thread Hannu Krosing

On Tue, 2008-07-29 at 12:46 -0400, Tom Lane wrote:
 Hannu Krosing [EMAIL PROTECTED] writes:
  Why is PROARGMODE_TABLE needed at all ?
 
 Personally I would rather not have it, but Pavel insists it's needed
 for standards compliance in PL/PSM, where output TABLE columns are not
 supposed to have names visible within the function.

Why not just hide the names from PL/PSM ?

The current way seems to just add complexity for no good reason.

 One reason to have it is so we can distinguish the correct way to
 reverse-list an output parameter (as OUT or as a table result column).
 Although we could equally well solve that with an extra bool column in
 pg_proc instead of redefining proargmodes, as long as you're willing to
 accept the reasonable restriction that you can't mix the two styles of
 declaring output parameters. 

Can you mix them with current API ? what would that mean ?

I mean, does the _caller_ ofd the function need to distinguisd OUT and
TABLE returns ?

  In principle PL/PSM could look at such a
 bool too, so there's more than one way to do it.

Yup, I'd prefer that.

 The feeling I had about it was that if we were adding
 PROARGMODE_VARIADIC in 8.4 then there wasn't any very strong argument
 not to add PROARGMODE_TABLE; any code looking at proargmodes is going
 to need updates anyway.

I missed the addition PROARGMODE_VARIADIC too. 

Has it already been added ?

What is it supposed to do ?


Hannu



-- 
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] Python 2.5 vs the buildfarm

2008-07-29 Thread Andrew Dunstan




Tom Lane wrote:

Gregory Stark [EMAIL PROTECTED] writes:
  

Tom Lane [EMAIL PROTECTED] writes:


I really don't understand Peter's objection here.  This thread has
already consumed more person-time than I spent on applying the
back-patch.  
  


  

Well I certainly wouldn't expect us to feel obligated to spend much effort
making 8.1 work with a new Redhat release, for example. We would just say 8.1
is only supported on those systems it was supported on when it was released.



Well, it would certainly depend on how much effort was involved to make
it work.  In this case, I drew the line at messing with autoconf ;-) ...
otherwise I might've tried to fix 7.4 as well.


  


I think your action has been entirely appropriate.

Just to show you how wrong Peter's objection is - yesterday I found 
myself having to build 7.1 so I could recover some data for a client. So 
we occasionally need to build long, long after the release.


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] Do we really want to migrate plproxy and citext into PG core distribution?

David E. Wheeler [EMAIL PROTECTED] writes:
 Damn, I was away from mail yesterday; sorry. I have some revisions,  
 mostly improving casting compatibility with text.

Huh?  What's to improve?

I know that you're still fooling with the regexp functions, but I
figured that could be added later.

 Tom, have you committed anything yet?

No, but I've put a couple of hours into editorialization and don't
want to throw that away.  How about you wait for my commit and then
send a patch revising whatever you want to?

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] Do we really want to migrate plproxy and citext into PG core distribution?


On Jul 29, 2008, at 11:01, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

Damn, I was away from mail yesterday; sorry. I have some revisions,
mostly improving casting compatibility with text.


Huh?  What's to improve?

I know that you're still fooling with the regexp functions, but I
figured that could be added later.


Tom, have you committed anything yet?


No, but I've put a couple of hours into editorialization and don't
want to throw that away.  How about you wait for my commit and then
send a patch revising whatever you want to?


Sure. It's mostly just additional casts and tests. I'd be happy to  
integrate it into your commit.


Thanks,

David


--
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] [PATCH] \ef function in psql

2008-07-29 Thread Robert Haas

+1 for CREATE OR REPLACE

...Robert

-- 
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] Do we really want to migrate plproxy and citext into PG core distribution?

David E. Wheeler [EMAIL PROTECTED] writes:
 Sure. It's mostly just additional casts and tests. I'd be happy to  
 integrate it into your commit.

Okay, it's committed with minor revisions --- the biggest thing I fixed
was the lack of an uninstall script.

I saw what you were talking about in terms of still having some casting
issues: having to put in a quote_literal(citext) alias function seems
like a huge hack, and I notice that cases like

contrib_regression=# select 'a'::text || 'b'::citext;
ERROR:  operator is not unique: text || citext

still don't work even though you put in an alias || operator.

It seems to me that trying to fix these things retail is a losing
proposition.  The reason you need these, instead of having everything
just work like varchar does, is that citext isn't seen as a member
of the string type category, and so the preferred type preference for
text isn't applied.  What we ought to do about that IMHO is make a
way for user-defined types to declare what category they belong to.
This has been foreseen as needed for a *very* long time, but we never
really had a forcing function to make us do it before.

Obviously the solution should involve a new column in pg_type and
a new type property in CREATE TYPE, but what should the representation
be?  A full-on approach would make the type categories be real SQL
objects with their own system catalog and reference them by OID,
but I can't help thinking that that's overkill.

Anyway, debating that is probably material for a separate thread ...

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

[HACKERS] Type Categories for User-Defined Types

2008-07-29 Thread David E . Wheeler


On Jul 29, 2008, at 11:41, Tom Lane wrote:

Okay, it's committed with minor revisions --- the biggest thing I  
fixed

was the lack of an uninstall script.


Great, thanks!

I saw what you were talking about in terms of still having some  
casting

issues: having to put in a quote_literal(citext) alias function seems
like a huge hack,


Yes, and I've been adding more hacks along the lines of:

CREATE OR REPLACE FUNCTION int8(citext)
RETURNS int8
AS 'SELECT int8( $1::text )'
LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext(int8)
RETURNS citext
AS 'SELECT text( $1 )::citext'
LANGUAGE SQL IMMUTABLE STRICT;

CREATE CAST (int8   AS citext) WITH FUNCTION citext(int8) AS ASSIGNMENT;
CREATE CAST (citext AS int8)   WITH FUNCTION int8(citext);

I've been doing this for all the types, writing tests to see how text  
behaves and replicating it with these hack functions. No, it's not  
ideal.



and I notice that cases like

contrib_regression=# select 'a'::text || 'b'::citext;
ERROR:  operator is not unique: text || citext

still don't work even though you put in an alias || operator.


Damn, I didn't even notice that! Can that be fixed?


It seems to me that trying to fix these things retail is a losing
proposition.  The reason you need these, instead of having everything
just work like varchar does, is that citext isn't seen as a member
of the string type category, and so the preferred type preference  
for

text isn't applied.  What we ought to do about that IMHO is make a
way for user-defined types to declare what category they belong to.
This has been foreseen as needed for a *very* long time, but we never
really had a forcing function to make us do it before.


Yes, this would be a *much* nicer way to do it, IMO.


Obviously the solution should involve a new column in pg_type and
a new type property in CREATE TYPE, but what should the representation
be?  A full-on approach would make the type categories be real SQL
objects with their own system catalog and reference them by OID,
but I can't help thinking that that's overkill.


It kinda sounds that way, yeah. What happens with DOMAINs, BTW? Do  
they need to write hacky functions like the above, or are they aware  
of their types because of the types from which they inherit?



Anyway, debating that is probably material for a separate thread ...


Here you go! ;-)

Thanks again for the commit, Tom.

Best,

David


--
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] Do we really want to migrate plproxy and citext into PG core distribution?

2008-07-29 Thread Zdenek Kotala


Tom Lane napsal(a):



Obviously the solution should involve a new column in pg_type and
a new type property in CREATE TYPE, but what should the representation
be?  A full-on approach would make the type categories be real SQL
objects with their own system catalog and reference them by OID,
but I can't help thinking that that's overkill.

Anyway, debating that is probably material for a separate thread ...


The collation support also needs to determine which data type is text/string.

Zdenek


--
Zdenek Kotala  Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql


--
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] TABLE-function patch vs plpgsql

Hannu Krosing [EMAIL PROTECTED] writes:
 On Tue, 2008-07-29 at 12:46 -0400, Tom Lane wrote:
 The feeling I had about it was that if we were adding
 PROARGMODE_VARIADIC in 8.4 then there wasn't any very strong argument
 not to add PROARGMODE_TABLE; any code looking at proargmodes is going
 to need updates anyway.

 I missed the addition PROARGMODE_VARIADIC too. 
 Has it already been added ?
 What is it supposed to do ?

http://archives.postgresql.org/pgsql-committers/2008-07/msg00127.php

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] Python 2.5 vs the buildfarm

2008-07-29 Thread Zdenek Kotala


Tom Lane napsal(a):

Peter Eisentraut [EMAIL PROTECTED] writes:

Am Tuesday, 29. July 2008 schrieb Greg Sabino Mullane:

What's not stable about having Python 2.5?



I mean stable to mean does not change (unnecessarily).


I really don't understand Peter's objection here.  This thread has
already consumed more person-time than I spent on applying the
back-patch.  I note also that, in fact, the code that was wrong was
wrong according to pre-2.5 python as well.  It accidentally failed
to fail on common architectures, but it was certainly doing things
that are undefined according to the C standard.  So in my eyes this
was a bug fix. 


I see. if it is small patch and also fix other problems it seems to me as 
reasonable change.


Zdenek


--
Zdenek Kotala  Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql


--
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] [PATCH] \ef function in psql

2008-07-29 Thread David Fetter

On Tue, Jul 29, 2008 at 02:21:18PM -0400, Robert Haas wrote:
 +1 for CREATE OR REPLACE
 
 ...Robert

+1 for CREATE OR REPLACE from me, too :)

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] Type Categories for User-Defined Types

David E. Wheeler [EMAIL PROTECTED] writes:
 On Jul 29, 2008, at 11:41, Tom Lane wrote:
 and I notice that cases like
 contrib_regression=# select 'a'::text || 'b'::citext;
 ERROR:  operator is not unique: text || citext
 still don't work even though you put in an alias || operator.

 Damn, I didn't even notice that! Can that be fixed?

Given the present infrastructure I think the only way would be with
two more alias operators, text||citext and citext||text.  But that way
madness lies.

 Obviously the solution should involve a new column in pg_type and
 a new type property in CREATE TYPE, but what should the representation
 be?  A full-on approach would make the type categories be real SQL
 objects with their own system catalog and reference them by OID,
 but I can't help thinking that that's overkill.

 It kinda sounds that way, yeah. What happens with DOMAINs, BTW? Do  
 they need to write hacky functions like the above, or are they aware  
 of their types because of the types from which they inherit?

Domains are treated as their base types in general.  Elein has been
complaining about that for years ;-) ... but I think improving it
is unrelated to this issue.

 Anyway, debating that is probably material for a separate thread ...

 Here you go! ;-)

After a quick look to verify my recollection: the only two things
that the system does with type categories are

extern CATEGORY TypeCategory(Oid type);

Returns the category a type belongs to.

extern bool IsPreferredType(CATEGORY category, Oid type);

Detects whether a type is a preferred type in its category (there can
be more than one preferred type in a category, and in fact the
traditional setup is that *every* user-defined type is a preferred
type in the USER_TYPE category).

The categories themselves are pretty much opaque values, except that
parse_func.c has special behavior to prefer STRING_TYPE when in doubt.

So this can fairly obviously be replaced by two new pg_type columns,
say typcategory and typpreferred, where the latter is a bool.
Since the list of categories is pretty short and there's no obvious
reason to extend it a lot, I propose that we just represent typcategory
as a char, using a mapping along the lines of

BITSTRING_TYPE  b
BOOLEAN_TYPEB
DATETIME_TYPE   D
GENERIC_TYPEP (think pseudotype)
GEOMETRIC_TYPE  G
INVALID_TYPE\0 (not allowed in catalog anyway)
NETWORK_TYPEn
NUMERIC_TYPEN
STRING_TYPE S
TIMESPAN_TYPE   T
UNKNOWN_TYPEu
USER_TYPE   U

Users would be allowed to select any single ASCII character as the
category of a user-defined type, should they have a need to make their
own new category.  Of course CREATE TYPE's default is category = U and
preferred = true for backward compatibility reasons.  We could put down
a rule that system-defined categories are always upper or lower case
letters (or even always upper, if we wanted to strain some of the
assignments a bit) so that it's clear what can be used for a
user-defined category.

It might possibly be worth making new categories for arrays, composites,
and enums; they're currently effectively USER_TYPE but that doesn't seem
quite right.  Also, the rules for domains should likely be same
category as base type, never a preferred type instead of the current
behavior where they're user types.  (I think the latter doesn't really
matter now, because we always smash a domain to its base type before
inquiring about categories anyway.  But it might give Elein a bit more
room to maneuver with the functions-on-domains issue.)

A possible objection is that this will make TypeCategory and
IsPreferredType slower than before, since they'll involve a syscache
lookup instead of a simple switch statement.  I don't think this will
be too bad though; all the paths they are used in are full of catalog
lookups anyway, so it's hard to credit that there would be much
percentage slowdown.

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] Type Categories for User-Defined Types


On Jul 29, 2008, at 13:12, Tom Lane wrote:


Damn, I didn't even notice that! Can that be fixed?


Given the present infrastructure I think the only way would be with
two more alias operators, text||citext and citext||text.  But that way
madness lies.


I suppose, then, that you're saying that there are lots of other  
functions for which this sort of thing would need to be done? Because  
two more aliases for this one operator is no big deal, AFAIC.



It kinda sounds that way, yeah. What happens with DOMAINs, BTW? Do
they need to write hacky functions like the above, or are they aware
of their types because of the types from which they inherit?


Domains are treated as their base types in general.  Elein has been
complaining about that for years ;-) ... but I think improving it
is unrelated to this issue.


I see.


After a quick look to verify my recollection: the only two things
that the system does with type categories are

extern CATEGORY TypeCategory(Oid type);

Returns the category a type belongs to.

extern bool IsPreferredType(CATEGORY category, Oid type);

Detects whether a type is a preferred type in its category (there can
be more than one preferred type in a category, and in fact the
traditional setup is that *every* user-defined type is a preferred
type in the USER_TYPE category).


Perhaps tangential: What does it mean for a type to be preferred?


The categories themselves are pretty much opaque values, except that
parse_func.c has special behavior to prefer STRING_TYPE when in doubt.

So this can fairly obviously be replaced by two new pg_type columns,
say typcategory and typpreferred, where the latter is a bool.
Since the list of categories is pretty short and there's no obvious
reason to extend it a lot, I propose that we just represent  
typcategory

as a char, using a mapping along the lines of

BITSTRING_TYPE  b
BOOLEAN_TYPEB
DATETIME_TYPE   D
GENERIC_TYPEP (think pseudotype)
GEOMETRIC_TYPE  G
INVALID_TYPE\0 (not allowed in catalog anyway)
NETWORK_TYPEn
NUMERIC_TYPEN
STRING_TYPE S
TIMESPAN_TYPE   T
UNKNOWN_TYPEu
USER_TYPE   U

Users would be allowed to select any single ASCII character as the
category of a user-defined type, should they have a need to make  
their

own new category.


Wouldn't this then limit them to 52 possible categories? Does that  
matter? Given your suggestion, I'm assuming that a single character is  
somehow more efficient than an enum, yes?



Of course CREATE TYPE's default is category = U and
preferred = true for backward compatibility reasons.  We could put  
down

a rule that system-defined categories are always upper or lower case
letters (or even always upper, if we wanted to strain some of the
assignments a bit) so that it's clear what can be used for a
user-defined category.


Makes sense.

It might possibly be worth making new categories for arrays,  
composites,
and enums; they're currently effectively USER_TYPE but that doesn't  
seem

quite right.  Also, the rules for domains should likely be same
category as base type, never a preferred type instead of the current
behavior where they're user types.  (I think the latter doesn't really
matter now, because we always smash a domain to its base type before
inquiring about categories anyway.  But it might give Elein a bit more
room to maneuver with the functions-on-domains issue.)


Yes, this all sounds like it'd be an important improvement.


A possible objection is that this will make TypeCategory and
IsPreferredType slower than before, since they'll involve a syscache
lookup instead of a simple switch statement.  I don't think this will
be too bad though; all the paths they are used in are full of catalog
lookups anyway, so it's hard to credit that there would be much
percentage slowdown.

Thoughts?


Obviously I don't know much about the internals, but your explanation  
here seems very clear to me. I like it. +1


Thank you, Tom.

Best,

David


--
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] Type Categories for User-Defined Types

David E. Wheeler [EMAIL PROTECTED] writes:
 On Jul 29, 2008, at 13:12, Tom Lane wrote:
 Given the present infrastructure I think the only way would be with
 two more alias operators, text||citext and citext||text.  But that way
 madness lies.

 I suppose, then, that you're saying that there are lots of other  
 functions for which this sort of thing would need to be done? Because  
 two more aliases for this one operator is no big deal, AFAIC.

Well, a rough estimate of the places where implicit coercion to text
might be relevant to resolving ambiguity is

select proname from pg_proc
  where 'text'::regtype = any(proargtypes)
  group by proname having count(*)1;

select oprname from pg_operator
  where oprleft='text'::regtype or oprright='text'::regtype
  group by oprname having count(*) 1;

I count 37 functions and 10 operators as of CVS HEAD.  Perhaps not all
would need to be fixed in practical use, but if you wanted seamless
integration of citext it's quite possible that you'd need alias
functions/operators (maybe more than one) in each of those cases.

[ squint... ]  Actually, this is an underestimate since these queries
aren't finding cases like quote_literal, where there is ambiguity but
only one of the alternatives takes 'text'.  I'm too lazy to work out a
better query though.

 Perhaps tangential: What does it mean for a type to be preferred?

See the ambiguous-function resolution rules in chapter 10 of the fine
manual ...

 Users would be allowed to select any single ASCII character as the
 category of a user-defined type, should they have a need to make  
 their own new category.

 Wouldn't this then limit them to 52 possible categories?

It'd be either 94 - 26 or 94 - 26 - 26 depending on what the policy is
about lower-case letters (and assuming they wanted to stay away from
control characters, which seems like a good idea).  Considering the
world supply of categories up to now has been about ten, it's hard
to imagine that this is really a limitation.

 Does that  
 matter? Given your suggestion, I'm assuming that a single character is  
 somehow more efficient than an enum, yes?

Marginally so; but an enum wouldn't help anyway unless we are prepared
to invent ALTER ENUM.  We'd have to go to an actual new system catalog
if we wanted something noticeably better than the poor-mans-enum
approach, and as I mentioned earlier, that just seems like overkill.
(Besides, we could always add it later if there's suddenly a gold rush
for categories.  The only thing we'd be locking ourselves into, if
we view this as a stopgap implementation, is the need to accept
single-character abbreviations in future, even after the system knows
actual names for categories.)

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] Type Categories for User-Defined Types


On Jul 29, 2008, at 14:00, Tom Lane wrote:


Well, a rough estimate of the places where implicit coercion to text
might be relevant to resolving ambiguity is

select proname from pg_proc
 where 'text'::regtype = any(proargtypes)
 group by proname having count(*)1;

select oprname from pg_operator
 where oprleft='text'::regtype or oprright='text'::regtype
 group by oprname having count(*) 1;

I count 37 functions and 10 operators as of CVS HEAD.  Perhaps not all
would need to be fixed in practical use, but if you wanted seamless
integration of citext it's quite possible that you'd need alias
functions/operators (maybe more than one) in each of those cases.


Well, there are already citext aliases for all of those operators, for  
this very reason. There are citext aliases for a bunch of the  
functions, too (ltrim(), substring(), etc.), so I wouldn't worry about  
adding more. I've added more of them since I last sent a patch, mainly  
for the regexp functions, replace(), strpos(), etc. I'd guess that I'm  
about half-way there already, and there probably are a few I wouldn't  
bother with (like timezone()).


Anyway, would this issue then go away once the type stuff was added  
and citext was specified as TYPE = 'S'?



[ squint... ]  Actually, this is an underestimate since these queries
aren't finding cases like quote_literal, where there is ambiguity but
only one of the alternatives takes 'text'.  I'm too lazy to work out a
better query though.


Thanks.


Perhaps tangential: What does it mean for a type to be preferred?


See the ambiguous-function resolution rules in chapter 10 of the fine
manual ...


I see this:

C. Run through all candidates and keep those that accept preferred  
types (of the input data type's type category) at the most positions  
where type conversion will be required. Keep all candidates if none  
accept preferred types. If only one candidate remains, use it; else  
continue to the next step.


That doesn't exactly explain what preferred means, just that it  
seems to prioritize the resolution of a function a bit. Which, I  
guess, is the point.



Wouldn't this then limit them to 52 possible categories?


It'd be either 94 - 26 or 94 - 26 - 26 depending on what the policy is
about lower-case letters (and assuming they wanted to stay away from
control characters, which seems like a good idea).  Considering the
world supply of categories up to now has been about ten, it's hard
to imagine that this is really a limitation.


Okay.


Does that
matter? Given your suggestion, I'm assuming that a single character  
is

somehow more efficient than an enum, yes?


Marginally so; but an enum wouldn't help anyway unless we are prepared
to invent ALTER ENUM.  We'd have to go to an actual new system catalog
if we wanted something noticeably better than the poor-mans-enum
approach, and as I mentioned earlier, that just seems like overkill.
(Besides, we could always add it later if there's suddenly a gold rush
for categories.  The only thing we'd be locking ourselves into, if
we view this as a stopgap implementation, is the need to accept
single-character abbreviations in future, even after the system knows
actual names for categories.)


Makes sense.

Thanks,

David


--
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] Type Categories for User-Defined Types

David E. Wheeler [EMAIL PROTECTED] writes:
 Well, there are already citext aliases for all of those operators, for  
 this very reason. There are citext aliases for a bunch of the  
 functions, too (ltrim(), substring(), etc.), so I wouldn't worry about  
 adding more. I've added more of them since I last sent a patch, mainly  
 for the regexp functions, replace(), strpos(), etc. I'd guess that I'm  
 about half-way there already, and there probably are a few I wouldn't  
 bother with (like timezone()).

That's exactly what I don't really want to do; if you are adding aliases
*only* to get rid of ambiguity-errors, and not to alter functionality,
then I think you're doing the wrong thing.  Adding more aliases can
easily make the situation worse.

 Anyway, would this issue then go away once the type stuff was added  
 and citext was specified as TYPE = 'S'?

Yeah, that's the point of the proposal.  I think the issue has come up
once or twice before, too, else I'd not be so interested in a general
solution.  (digs in archives ... there was some discussion of this
in connection with unsigned integer types, and I seem to recall older
threads but can't find any right now.)

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] Type Categories for User-Defined Types


On Jul 29, 2008, at 14:33, Tom Lane wrote:

That's exactly what I don't really want to do; if you are adding  
aliases

*only* to get rid of ambiguity-errors, and not to alter functionality,
then I think you're doing the wrong thing.  Adding more aliases can
easily make the situation worse.


Actually, most seem to resolve to text okay. I'm adding aliases to  
change behavior (e.g., case-insensitive matching in replace()). It's  
the bazillion cast functions I'm having to add that are annoying (see  
my previous post with the int8 example).



Anyway, would this issue then go away once the type stuff was added
and citext was specified as TYPE = 'S'?


Yeah, that's the point of the proposal.  I think the issue has come up
once or twice before, too, else I'd not be so interested in a general
solution.  (digs in archives ... there was some discussion of this
in connection with unsigned integer types, and I seem to recall older
threads but can't find any right now.)


No worries, it seems like a really good idea to me, regardless.

Thanks!

David


--
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] about postgres-r setup.

2008-07-29 Thread Markus Wanner


Hello Leiyonghua,

thanks for trying out Postgres-R.

leiyonghua wrote:

NOTICE: Applying for membership in the communication group...
NOTICE: Successfully joined the replication group. Now recovering schema...

so, i dont know what happened, pls give me some advice.


Please compile with '--enable-debug' and '--enable-cassert' and check 
the log.


The schema recovery has not been implemented, yet. So I find it pretty 
suspicious it is hanging there. Although, I must admit I didn't try the 
ensemble interface layer for a while.


Regards

Markus


--
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, oprrest function for text search take 2

2008-07-29 Thread Jan Urbański


Heikki Linnakangas wrote:

Jan Urbański wrote:
Another thing are cstring_to_text_with_len calls. I'm doing them so I 
can use bttextcmp in bsearch(). I think I could come up with a 
dedicated function to return text Datums and WordEntries (read: 
non-NULL terminated strings with a given length).


Just keep them as cstrings and use strcmp. We're only keeping the array 
sorted so that we can binary search it, so we don't need proper 
locale-dependent collation. Note that we already assume that two strings 
('text's) are equal if and only if their binary representations are 
equal (texteq() uses strcmp).


OK, I got rid of cstring-text calls and memory contexts as I went 
through it. The only tiny ugliness is that there's one function used for 
qsort() and another for bsearch(), because I'm sorting an array of texts 
(from pg_statistic) and I'm binary searching for a lexeme (non-NULL 
terminated string with length).

My medicore gprof skills got me:
0.000.22   5/5   OidFunctionCall4 [37]
[38]28.40.000.22   5 tssel [38]
0.000.17   5/5 
get_restriction_variable [40]

0.030.01   5/10  pg_qsort [60]
0.000.00   5/5   get_attstatsslot [139]

Hopefully that says that the qsort() overhead is small compared to 
munging through the planner Node.

Revised version of the patch attached.

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin
diff --git a/src/backend/tsearch/Makefile b/src/backend/tsearch/Makefile
index e20a4a2..ba728eb 100644
*** a/src/backend/tsearch/Makefile
--- b/src/backend/tsearch/Makefile
***
*** 19,25 
  OBJS = ts_locale.o ts_parse.o wparser.o wparser_def.o dict.o \
dict_simple.o dict_synonym.o dict_thesaurus.o \
dict_ispell.o regis.o spell.o \
!   to_tsany.o ts_typanalyze.o ts_utils.o
  
  include $(top_srcdir)/src/backend/common.mk
  
--- 19,25 
  OBJS = ts_locale.o ts_parse.o wparser.o wparser_def.o dict.o \
dict_simple.o dict_synonym.o dict_thesaurus.o \
dict_ispell.o regis.o spell.o \
!   to_tsany.o ts_typanalyze.o ts_selfuncs.o ts_utils.o
  
  include $(top_srcdir)/src/backend/common.mk
  
diff --git a/src/backend/tsearch/ts_selfuncs.c 
b/src/backend/tsearch/ts_selfuncs.c
new file mode 100644
index 000..4deb507
*** /dev/null
--- b/src/backend/tsearch/ts_selfuncs.c
***
*** 0,-1 
--- 1,334 
+ /*-
+  *
+  * ts_selfuncs.c
+  *  Selectivity functions for text search types.
+  *
+  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
+  *
+  *
+  * IDENTIFICATION
+  *  $PostgreSQL$
+  *
+  *-
+  */
+ #include postgres.h
+ 
+ #include miscadmin.h /* for check_stack_depth() */
+ #include utils/memutils.h
+ #include utils/builtins.h
+ #include utils/syscache.h
+ #include utils/lsyscache.h
+ #include utils/selfuncs.h
+ #include catalog/pg_type.h
+ #include catalog/pg_statistic.h
+ #include nodes/nodes.h
+ #include tsearch/ts_type.h
+ 
+ /* lookup table type for binary searching through MCELEMs */
+ typedef struct
+ {
+   Datum   element;
+   float4  frequency;
+ } TextFreq;
+ 
+ /* type of keys for bsearch()ing through an array of TextFreqs  */
+ typedef struct
+ {
+   char*lexeme;
+   int length;
+ } LexemeKey;
+ 
+ static int
+ compare_two_textfreqs(const void *e1, const void *e2);
+ static int
+ compare_lexeme_textfreq(const void *e1, const void *e2);
+ 
+ static Selectivity
+ tsquery_opr_selec(QueryItem *item, char *operand, TextFreq *lookup,
+ int length, float4 minfreq);
+ static Selectivity
+ mcelem_tsquery_selec(TSQuery query, Datum *mcelem, int nmcelem,
+  float4 *numbers, int 
nnumbers);
+ static double
+ tsquerysel(VariableStatData *vardata, Datum constval);
+ 
+ 
+ /* TSQuery traversal function */
+ static Selectivity
+ tsquery_opr_selec(QueryItem *item, char *operand, TextFreq *lookup,
+ int length, float4 minfreq)
+ {
+   LexemeKey   key;
+   TextFreq*searchres;
+   Selectivity s1, s2;
+ 
+   /* since this function recurses, it could be driven to stack overflow */
+   check_stack_depth();
+ 
+   if (item-type == QI_VAL)
+   {
+   QueryOperand *oper = (QueryOperand *) item;
+ 
+   /*
+* Prepare the key for bsearch().
+*/
+   key.lexeme = operand + oper-distance;
+   key.length = oper-length;
+ 
+   searchres = (TextFreq *) bsearch(key, lookup, length,
+   
 sizeof(TextFreq), compare_lexeme_textfreq);
+   if (searchres)
+

Re: [HACKERS] Type Categories for User-Defined Types

I wrote:
 David E. Wheeler [EMAIL PROTECTED] writes:
 Anyway, would this issue then go away once the type stuff was added  
 and citext was specified as TYPE = 'S'?

 Yeah, that's the point of the proposal.

BTW, I did confirm (by temporarily hacking up TypeCategory()) that
causing citext to appear to be of STRING category eliminates the need
for the extra || operator and quote_literal() function that are in
the current citext code.  So the proposed solution really will work.

You might still want to keep the aliases in cases where the point is to
have the function or operator output resolve as citext rather than text.
I'm not sure how many of these cases that's really important for,
though.

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] Type Categories for User-Defined Types


On Jul 29, 2008, at 15:35, Tom Lane wrote:


BTW, I did confirm (by temporarily hacking up TypeCategory()) that
causing citext to appear to be of STRING category eliminates the need
for the extra || operator and quote_literal() function that are in
the current citext code.  So the proposed solution really will work.


Nice! Does it also allow all of the casts to and from text to  
implicitly work, e.g,


SELECT 4::int8::citext = '4' AS t;
SELECT '4'::citext::int8 = 4 AS t;
SELECT 4::bigint::citext = '4' AS t;
SELECT '4'::citext::bigint = 4 AS t;

You might still want to keep the aliases in cases where the point is  
to
have the function or operator output resolve as citext rather than  
text.

I'm not sure how many of these cases that's really important for,
though.


Not many, I should think.

Thanks,

David


--
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] Type Categories for User-Defined Types

David E. Wheeler [EMAIL PROTECTED] writes:
 On Jul 29, 2008, at 15:35, Tom Lane wrote:
 BTW, I did confirm (by temporarily hacking up TypeCategory()) that
 causing citext to appear to be of STRING category eliminates the need
 for the extra || operator and quote_literal() function that are in
 the current citext code.  So the proposed solution really will work.

 Nice! Does it also allow all of the casts to and from text to  
 implicitly work, e.g,

No, but we could think about that.  Do we really want that to work for
any member of the string category?  It seems a bit overly broad to me
...

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] Type Categories for User-Defined Types