Tuesday, July 8, 2008

Re: [JDBC] Timestamp without Timezone and differing client / server tzs

Oliver Jowett wrote:
> Ken Johanson wrote:
>
>> Question: with ts without tz storage, should not a timestamp be
>> normalized to the servers implicit TZ (UTC in this case), since that
>> is set in the config's client-tz?
>
> If you don't specify a Calendar to setTimestamp, it's assumed you mean
> the client JVM's default timezone (the same timezone that
> Timestamp.toString() assumes).

.......

> If you want to interpret a Timestamp as being in a particular timezone,
> use the getTimestamp()/setTimestamp() variants that take an explicit
> Calendar.

So, short of passing a calendar to setTimezone, there is no other way to
indicate to the driver that zone-less date/time type values should be
translated to the server's different timezone, is this correct?

k

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

Re: [GENERAL] Getting source code for database objects

Artacus wrote:
> On pgadmin, when you click on a table or function, you get the source
> code (DDL) to create that table or function.
>
> I want to take that and check it into subversion so I have one file for
> each table, function, view, etc. My question is, how do you get that
> source code? I've been playing with pgadmin and wireshark trying to
> figure out what commands or queries it is using to no avail.

Fire up psql -E and do a few \d commands. It'll show you the SQL used
to describe the objects.

--
Alvaro Herrera

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

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

[GENERAL] Feature: FOR UPDATE SKIP LOCKED

I’m been reading up on  FOR UPDATE NOWAIT and it looks like it was added in 8.1.

 

How difficult is it to add FOR UPDATE SKIP LOCKED or something similar? (basically skip locked rows / oracle syntax)

More background here:

http://forge.mysql.com/worklog/task.php?id=3597

 

It would be quite useful to implement a database queue. Although FOR UPDATE NOWAIT and trying again can work as well as other techniques,

just skipping over the locks has its advantages (simplicity and zero wait)

 

 

 

[COMMITTERS] npgsql - Npgsql2: Added support to not add type casts with prepared

Log Message:
-----------

Added support to not add type casts with prepared statements. Previous patch only added this capability for non prepared queries. Now, types without casts have the type written as "unknown" so type inference is done by server backend.

Modified Files:
--------------
Npgsql2/src/Npgsql:
NpgsqlCommand.cs (r1.23 -> r1.24)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/Npgsql/NpgsqlCommand.cs.diff?r1=1.23&r2=1.24)

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

[GENERAL] Getting source code for database objects

On pgadmin, when you click on a table or function, you get the source
code (DDL) to create that table or function.

I want to take that and check it into subversion so I have one file for
each table, function, view, etc. My question is, how do you get that
source code? I've been playing with pgadmin and wireshark trying to
figure out what commands or queries it is using to no avail.

Artacus

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

Re: [GENERAL] array sort for varchar arrays?

> I'm likely overlooking something, but I can't seem to find a function to
> sort a varchar array. Something like "select
> sort('{y,z,x}'::varchar[])" => {'x','y','z'}.

You've got it right. On Pg 8.3.3 I get
SELECT sort(array['z','y','x']) -> {x,y,z}

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

Re: [GENERAL] Quick way to alter a column type?

On Mon, 2008-07-07 at 02:10 -0400, Lew wrote:
> Ow Mun Heng wrote:
> >> I want to change a column type from varchar(4) to varchar(5) or should I
> >> just use text instead.

> The choice of TEXT for the column would seem to be supported in the PG manual,
> which stresses that TEXT and VARCHAR are quite close in performance, if not
> identical. I recommend to constrain the length if it's proper for the data
> domain. That is, if you are 100% absolutely certifiably certain that the
> length will never change again once you set it to 5, that is, if the data
> domain is a set of values that must be no more than 5 characters long, then
> VARCHAR(5) is a good choice. It accurately represents the data.

It was varchar(4) for a _long_ time until there came a need recently to
move it to a 5 characters.
Hence the issue, I've already changed it to varchar() instead to make it
more open (and less problematic in the future)

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

Re: [PERFORM] syslog performance when logging big statements

Tatsuo Ishii <ishii@postgresql.org> writes:
> I'm a little bit worried about cranking up PG_SYSLOG_LIMIT in the back
> branches. Cranking it up will definitely change syslog messages text
> style and might confuse syslog handling scripts(I have no evince that
> such scripts exist though). So I suggest to change PG_SYSLOG_LIMIT
> only in CVS HEAD.

Hmm, good point. It would be an externally visible behavior change,
not just a speedup.

regards, tom lane

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

Re: [PERFORM] syslog performance when logging big statements

> Jeff <threshar@threshar.is-a-geek.com> writes:
> > On Jul 8, 2008, at 8:24 AM, Achilleas Mantzios wrote:
> >> File sizes of about 3M result in actual logging output of ~ 10Mb.
> >> In this case, the INSERT *needs* 20 minutes to return. This is
> >> because the logging through syslog seems to severely slow the system.
> >> If instead, i use stderr, even with logging_collector=on, the same
> >> statement needs 15 seconds to return.
>
> > In syslog.conf is the destination for PG marked with a "-"? (ie -/var/
> > log/pg.log) which tells syslog to not sync after each line logged.
> > That could explain a large chunk of the difference in time.
>
> I experimented with this a bit here. There definitely is an O(N^2)
> penalty from the repeated strchr() calls, but it doesn't really start
> to hurt till 1MB or so statement length. Even with that patched,
> syslog logging pretty much sucks performance-wise. Here are the numbers
> I got on a Fedora 8 workstation, testing the time to log a statement of
> the form SELECT length('123456...lots of data, no newlines...7890');
>
> statement length 1MB 10MB
>
> CVS HEAD 2523ms 215588ms
> + patch to fix repeated strchr 529ms 36734ms
> after turning off syslogd's fsync 569ms 5881ms
> PG_SYSLOG_LIMIT 1024, fsync on 216ms 2532ms
> PG_SYSLOG_LIMIT 1024, no fsync 242ms 2692ms
> For comparison purposes:
> logging statements to stderr 155ms 2042ms
> execute statement without logging 42ms 520ms
>
> This machine is running a cheap IDE drive that caches writes, so
> the lack of difference between fsync off and fsync on is not too
> surprising --- on a machine with server-grade drives there'd be
> a lot more difference. (The fact that there is a difference in
> the 10MB case probably reflects filling the drive's write cache.)
>
> On my old HPUX machine, where fsync really works (and the syslogd
> doesn't seem to allow turning it off), the 1MB case takes
> 195957ms with the strchr patch, 22922ms at PG_SYSLOG_LIMIT=1024.
>
> So there's a fairly clear case to be made for fixing the repeated
> strchr, but I also think that there's a case for jacking up
> PG_SYSLOG_LIMIT. As far as I can tell the current value of 128
> was chosen *very* conservatively without thought for performance:
> http://archives.postgresql.org/pgsql-hackers/2000-05/msg01242.php
>
> At the time we were looking at evidence that the then-current
> Linux syslogd got tummyache with messages over about 1KB:
> http://archives.postgresql.org/pgsql-hackers/2000-05/msg00880.php
>
> Some experimentation with the machines I have handy now says that
>
> Fedora 8: truncates messages at 2KB (including syslog's header)
> HPUX 10.20 (ancient): ditto
> Mac OS X 10.5.3: drops messages if longer than about 1900 bytes
>
> So it appears to me that setting PG_SYSLOG_LIMIT = 1024 would be
> perfectly safe on current systems (and probably old ones too),
> and would give at least a factor of two speedup for logging long
> strings --- more like a factor of 8 if syslogd is fsync'ing.
>
> Comments? Anyone know of systems where this is too high?
> Perhaps we should make that change only in HEAD, not in the
> back branches, or crank it up only to 512 in the back branches?

I'm a little bit worried about cranking up PG_SYSLOG_LIMIT in the back
branches. Cranking it up will definitely change syslog messages text
style and might confuse syslog handling scripts(I have no evince that
such scripts exist though). So I suggest to change PG_SYSLOG_LIMIT
only in CVS HEAD.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Re: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP

David Fetter wrote:
> On Tue, Jul 08, 2008 at 06:01:05PM +0900, Tatsuo Ishii wrote:
> > Here is the patches he made against CVS HEAD (as of today).
>
> The git repository should now match this :)
>
> http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=summary
>
> Apparently, it's easiest to clone via the following URL:
>
> http://git.postgresql.org/git/~davidfetter/postgresql/.git
>
> Is there some git repository I can pull from to make this a little
> less manual?

In fact, I fail to see the point of you providing the repo if the
upstream guys are apparently not using it ...

--
Alvaro Herrera

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

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

Re: [PERFORM] syslog performance when logging big statements

On Tue, 8 Jul 2008, Tom Lane wrote:

> Jeff <threshar@threshar.is-a-geek.com> writes:
>> On Jul 8, 2008, at 8:24 AM, Achilleas Mantzios wrote:
>>> File sizes of about 3M result in actual logging output of ~ 10Mb.
>>> In this case, the INSERT *needs* 20 minutes to return. This is
>>> because the logging through syslog seems to severely slow the system.
>>> If instead, i use stderr, even with logging_collector=on, the same
>>> statement needs 15 seconds to return.
>
>> In syslog.conf is the destination for PG marked with a "-"? (ie -/var/
>> log/pg.log) which tells syslog to not sync after each line logged.
>> That could explain a large chunk of the difference in time.
>
> I experimented with this a bit here. There definitely is an O(N^2)
> penalty from the repeated strchr() calls, but it doesn't really start
> to hurt till 1MB or so statement length. Even with that patched,
> syslog logging pretty much sucks performance-wise. Here are the numbers
> I got on a Fedora 8 workstation, testing the time to log a statement of
> the form SELECT length('123456...lots of data, no newlines...7890');
>
> statement length 1MB 10MB
>
> CVS HEAD 2523ms 215588ms
> + patch to fix repeated strchr 529ms 36734ms
> after turning off syslogd's fsync 569ms 5881ms
> PG_SYSLOG_LIMIT 1024, fsync on 216ms 2532ms
> PG_SYSLOG_LIMIT 1024, no fsync 242ms 2692ms
> For comparison purposes:
> logging statements to stderr 155ms 2042ms
> execute statement without logging 42ms 520ms
>
> This machine is running a cheap IDE drive that caches writes, so
> the lack of difference between fsync off and fsync on is not too
> surprising --- on a machine with server-grade drives there'd be
> a lot more difference. (The fact that there is a difference in
> the 10MB case probably reflects filling the drive's write cache.)
>
> On my old HPUX machine, where fsync really works (and the syslogd
> doesn't seem to allow turning it off), the 1MB case takes
> 195957ms with the strchr patch, 22922ms at PG_SYSLOG_LIMIT=1024.
>
> So there's a fairly clear case to be made for fixing the repeated
> strchr, but I also think that there's a case for jacking up
> PG_SYSLOG_LIMIT. As far as I can tell the current value of 128
> was chosen *very* conservatively without thought for performance:
> http://archives.postgresql.org/pgsql-hackers/2000-05/msg01242.php
>
> At the time we were looking at evidence that the then-current
> Linux syslogd got tummyache with messages over about 1KB:
> http://archives.postgresql.org/pgsql-hackers/2000-05/msg00880.php
>
> Some experimentation with the machines I have handy now says that
>
> Fedora 8: truncates messages at 2KB (including syslog's header)
> HPUX 10.20 (ancient): ditto
> Mac OS X 10.5.3: drops messages if longer than about 1900 bytes
>
> So it appears to me that setting PG_SYSLOG_LIMIT = 1024 would be
> perfectly safe on current systems (and probably old ones too),
> and would give at least a factor of two speedup for logging long
> strings --- more like a factor of 8 if syslogd is fsync'ing.
>
> Comments? Anyone know of systems where this is too high?
> Perhaps we should make that change only in HEAD, not in the
> back branches, or crank it up only to 512 in the back branches?

with linux ext2/ext3 filesystems I have seen similar problems when the
syslog starts getting large. there are several factors here

1. fsync after each write unless you have "-" in syslog.conf (only
available on linux AFAIK)

2. ext2/ext3 tend to be very inefficiant when doing appends to large
files. each write requires that the syslog daemon seek to the end of the
file (becouse something else may have written to the file in the meantime)
and with the small block sizes and chaining of indirect blocks this can
start to be painful when logfiles get up in to the MB range.

note that you see this same problem when you start to get lots of
files in one directory as well. even if you delete a lot of files the
directory itself is still large and this can cause serious performance
problems.

other filesystems are much less sensitive to file (and directory) sizes.

my suggestion would be to first make sure you are doing async writes to
syslog, and then try putting the logfiles on different filesystems to see
how they differ. personally I use XFS most of the time where I expect lots
of files or large files.

David Lang

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

Re: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP

On Tue, Jul 08, 2008 at 06:01:05PM +0900, Tatsuo Ishii wrote:
> Here is the patches he made against CVS HEAD (as of today).

The git repository should now match this :)

http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=summary

Apparently, it's easiest to clone via the following URL:

http://git.postgresql.org/git/~davidfetter/postgresql/.git

Is there some git repository I can pull from to make this a little
less manual?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

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

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

Re: [HACKERS] Identifier case folding notes

=== modified file 'src/backend/access/transam/xlog.c'
--- src/backend/access/transam/xlog.c 2008-03-27 12:10:18 +0000
+++ src/backend/access/transam/xlog.c 2008-03-27 14:15:13 +0000
@@ -4040,6 +4040,9 @@
PGC_INTERNAL, PGC_S_OVERRIDE);
SetConfigOption("lc_ctype", ControlFile->lc_ctype,
PGC_INTERNAL, PGC_S_OVERRIDE);
+ /* Make the fixed case folding visible as GUC variables, too */
+ SetConfigOption("identifier_case_folding", ControlFile->identifierCaseFolding,
+ PGC_INTERNAL, PGC_S_OVERRIDE);
}

void
@@ -4290,6 +4293,10 @@
ControlFile->time = checkPoint.time;
ControlFile->checkPoint = checkPoint.redo;
ControlFile->checkPointCopy = checkPoint;
+
+ /* Set the case folding option */
+ strncpy(ControlFile->identifierCaseFolding, "preserved", 9);
+
/* some additional ControlFile fields are set in WriteControlFile() */

WriteControlFile();

=== modified file 'src/backend/catalog/information_schema.sql'
--- src/backend/catalog/information_schema.sql 2008-03-27 12:10:18 +0000
+++ src/backend/catalog/information_schema.sql 2008-03-27 12:12:15 +0000
@@ -23,7 +23,7 @@
*/

CREATE SCHEMA information_schema;
-GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
+GRANT usage ON SCHEMA information_schema TO public;
SET search_path TO information_schema, public;


@@ -33,7 +33,7 @@

/* Expand any 1-D array into a set with integers 1..N */
CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
- RETURNS SETOF RECORD
+ RETURNS SETOF record
LANGUAGE sql STRICT IMMUTABLE
AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
@@ -214,7 +214,7 @@
CREATE VIEW information_schema_catalog_name AS
SELECT CAST(current_database() AS sql_identifier) AS catalog_name;

-GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
+GRANT SELECT ON information_schema_catalog_name TO public;


/*
@@ -241,9 +241,9 @@
FROM pg_auth_members m
JOIN pg_authid a ON (m.member = a.oid)
JOIN pg_authid b ON (m.roleid = b.oid)
- WHERE pg_has_role(a.oid, 'USAGE');
+ WHERE pg_has_role(a.oid, 'usage');

-GRANT SELECT ON applicable_roles TO PUBLIC;
+GRANT SELECT ON applicable_roles TO public;


/*
@@ -256,7 +256,7 @@
FROM applicable_roles
WHERE is_grantable = 'YES';

-GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
+GRANT SELECT ON administrable_role_authorizations TO public;


/*
@@ -353,7 +353,7 @@
AND a.attnum > 0 AND NOT a.attisdropped
AND c.relkind in ('c');

-GRANT SELECT ON attributes TO PUBLIC;
+GRANT SELECT ON attributes TO public;


/*
@@ -384,9 +384,9 @@
AND d.refobjid = p.oid
AND d.refclassid = 'pg_catalog.pg_proc'::regclass
AND p.pronamespace = np.oid
- AND pg_has_role(p.proowner, 'USAGE');
+ AND pg_has_role(p.proowner, 'usage');

-GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
+GRANT SELECT ON check_constraint_routine_usage TO public;


/*
@@ -404,7 +404,7 @@
LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
- WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
+ WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'usage')
AND con.contype = 'c'

UNION
@@ -422,9 +422,9 @@
AND NOT a.attisdropped
AND a.attnotnull
AND r.relkind = 'r'
- AND pg_has_role(r.relowner, 'USAGE');
+ AND pg_has_role(r.relowner, 'usage');

-GRANT SELECT ON check_constraints TO PUBLIC;
+GRANT SELECT ON check_constraints TO public;


/*
@@ -475,9 +475,9 @@
AND c.relkind IN ('r', 'v')
AND a.attnum > 0
AND NOT a.attisdropped
- AND pg_has_role(t.typowner, 'USAGE');
+ AND pg_has_role(t.typowner, 'usage');

-GRANT SELECT ON column_domain_usage TO PUBLIC;
+GRANT SELECT ON column_domain_usage TO public;


/*
@@ -505,7 +505,7 @@
(
SELECT oid, rolname FROM pg_authid
UNION ALL
- SELECT 0::oid, 'PUBLIC'
+ SELECT 0::oid, 'public'
) AS grantee (oid, rolname),
(SELECT 'SELECT' UNION ALL
SELECT 'INSERT' UNION ALL
@@ -519,11 +519,11 @@
AND c.relkind IN ('r', 'v')
AND aclcontains(c.relacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
- AND (pg_has_role(u_grantor.oid, 'USAGE')
- OR pg_has_role(grantee.oid, 'USAGE')
- OR grantee.rolname = 'PUBLIC');
+ AND (pg_has_role(u_grantor.oid, 'usage')
+ OR pg_has_role(grantee.oid, 'usage')
+ OR grantee.rolname = 'public');

-GRANT SELECT ON column_privileges TO PUBLIC;
+GRANT SELECT ON column_privileges TO public;


/*
@@ -549,9 +549,9 @@
AND a.atttypid = t.oid
AND nc.oid = c.relnamespace
AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
- AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
+ AND pg_has_role(coalesce(bt.typowner, t.typowner), 'usage');

-GRANT SELECT ON column_udt_usage TO PUBLIC;
+GRANT SELECT ON column_udt_usage TO public;


/*
@@ -670,13 +670,13 @@

AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')

- AND (pg_has_role(c.relowner, 'USAGE')
+ AND (pg_has_role(c.relowner, 'usage')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
OR has_table_privilege(c.oid, 'REFERENCES') );

-GRANT SELECT ON columns TO PUBLIC;
+GRANT SELECT ON columns TO public;


/*
@@ -726,9 +726,9 @@

) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)

- WHERE pg_has_role(x.tblowner, 'USAGE');
+ WHERE pg_has_role(x.tblowner, 'usage');

-GRANT SELECT ON constraint_column_usage TO PUBLIC;
+GRANT SELECT ON constraint_column_usage TO public;


/*
@@ -751,9 +751,9 @@
AND ( (c.contype = 'f' AND c.confrelid = r.oid)
OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
AND r.relkind = 'r'
- AND pg_has_role(r.relowner, 'USAGE');
+ AND pg_has_role(r.relowner, 'usage');

-GRANT SELECT ON constraint_table_usage TO PUBLIC;
+GRANT SELECT ON constraint_table_usage TO public;


-- 5.23 DATA_TYPE_PRIVILEGES view appears later.
@@ -796,7 +796,7 @@
AND n.oid = t.typnamespace
AND t.oid = con.contypid;

-GRANT SELECT ON domain_constraints TO PUBLIC;
+GRANT SELECT ON domain_constraints TO public;


/*
@@ -819,9 +819,9 @@
AND t.typbasetype = bt.oid
AND bt.typnamespace = nbt.oid
AND t.typtype = 'd'
- AND pg_has_role(bt.typowner, 'USAGE');
+ AND pg_has_role(bt.typowner, 'usage');

-GRANT SELECT ON domain_udt_usage TO PUBLIC;
+GRANT SELECT ON domain_udt_usage TO public;


/*
@@ -903,7 +903,7 @@
AND bt.typnamespace = nbt.oid
AND t.typtype = 'd';

-GRANT SELECT ON domains TO PUBLIC;
+GRANT SELECT ON domains TO public;


-- 5.28 ELEMENT_TYPES view appears later.
@@ -917,9 +917,9 @@
CREATE VIEW enabled_roles AS
SELECT CAST(a.rolname AS sql_identifier) AS role_name
FROM pg_authid a
- WHERE pg_has_role(a.oid, 'USAGE');
+ WHERE pg_has_role(a.oid, 'usage');

-GRANT SELECT ON enabled_roles TO PUBLIC;
+GRANT SELECT ON enabled_roles TO public;


/*
@@ -963,7 +963,7 @@
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
AND (NOT pg_is_other_temp_schema(nr.oid))
- AND (pg_has_role(r.relowner, 'USAGE')
+ AND (pg_has_role(r.relowner, 'usage')
OR has_table_privilege(r.oid, 'SELECT')
OR has_table_privilege(r.oid, 'INSERT')
OR has_table_privilege(r.oid, 'UPDATE')
@@ -972,7 +972,7 @@
AND a.attnum = (ss.x).x
AND NOT a.attisdropped;

-GRANT SELECT ON key_column_usage TO PUBLIC;
+GRANT SELECT ON key_column_usage TO public;


/*
@@ -1044,11 +1044,11 @@
_pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
FROM pg_namespace n, pg_proc p
WHERE n.oid = p.pronamespace
- AND (pg_has_role(p.proowner, 'USAGE') OR
+ AND (pg_has_role(p.proowner, 'usage') OR
has_function_privilege(p.oid, 'EXECUTE'))) AS ss
WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;

-GRANT SELECT ON parameters TO PUBLIC;
+GRANT SELECT ON parameters TO public;


/*
@@ -1109,9 +1109,9 @@
WHERE c.relkind = 'r'
AND con.contype = 'f'
AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
- AND pg_has_role(c.relowner, 'USAGE');
+ AND pg_has_role(c.relowner, 'usage');

-GRANT SELECT ON referential_constraints TO PUBLIC;
+GRANT SELECT ON referential_constraints TO public;


/*
@@ -1152,7 +1152,7 @@
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));

-GRANT SELECT ON role_column_grants TO PUBLIC;
+GRANT SELECT ON role_column_grants TO public;


/*
@@ -1186,7 +1186,7 @@
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));

-GRANT SELECT ON role_routine_grants TO PUBLIC;
+GRANT SELECT ON role_routine_grants TO public;


/*
@@ -1225,7 +1225,7 @@
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));

-GRANT SELECT ON role_table_grants TO PUBLIC;
+GRANT SELECT ON role_table_grants TO public;


/*
@@ -1241,7 +1241,7 @@
* ROLE_USAGE_GRANTS view
*/

--- See USAGE_PRIVILEGES.
+-- See usage_PRIVILEGES.

CREATE VIEW role_usage_grants AS
SELECT CAST(null AS sql_identifier) AS grantor,
@@ -1250,12 +1250,12 @@
CAST(null AS sql_identifier) AS object_schema,
CAST(null AS sql_identifier) AS object_name,
CAST(null AS character_data) AS object_type,
- CAST('USAGE' AS character_data) AS privilege_type,
+ CAST('usage' AS character_data) AS privilege_type,
CAST(null AS character_data) AS is_grantable

WHERE false;

-GRANT SELECT ON role_usage_grants TO PUBLIC;
+GRANT SELECT ON role_usage_grants TO public;


/*
@@ -1300,17 +1300,17 @@
(
SELECT oid, rolname FROM pg_authid
UNION ALL
- SELECT 0::oid, 'PUBLIC'
+ SELECT 0::oid, 'public'
) AS grantee (oid, rolname)

WHERE p.pronamespace = n.oid
AND aclcontains(p.proacl,
makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
- AND (pg_has_role(u_grantor.oid, 'USAGE')
- OR pg_has_role(grantee.oid, 'USAGE')
- OR grantee.rolname = 'PUBLIC');
+ AND (pg_has_role(u_grantor.oid, 'usage')
+ OR pg_has_role(grantee.oid, 'usage')
+ OR grantee.rolname = 'public');

-GRANT SELECT ON routine_privileges TO PUBLIC;
+GRANT SELECT ON routine_privileges TO public;


/*
@@ -1388,7 +1388,7 @@
CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
AS routine_body,
CAST(
- CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
+ CASE WHEN pg_has_role(p.proowner, 'usage') THEN p.prosrc ELSE null END
AS character_data) AS routine_definition,
CAST(
CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
@@ -1444,10 +1444,10 @@

WHERE n.oid = p.pronamespace AND p.prolang = l.oid
AND p.prorettype = t.oid AND t.typnamespace = nt.oid
- AND (pg_has_role(p.proowner, 'USAGE')
+ AND (pg_has_role(p.proowner, 'usage')
OR has_function_privilege(p.oid, 'EXECUTE'));

-GRANT SELECT ON routines TO PUBLIC;
+GRANT SELECT ON routines TO public;


/*
@@ -1464,9 +1464,9 @@
CAST(null AS sql_identifier) AS default_character_set_name,
CAST(null AS character_data) AS sql_path
FROM pg_namespace n, pg_authid u
- WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
+ WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'usage');

-GRANT SELECT ON schemata TO PUBLIC;
+GRANT SELECT ON schemata TO public;


/*
@@ -1490,11 +1490,11 @@
WHERE c.relnamespace = nc.oid
AND c.relkind = 'S'
AND (NOT pg_is_other_temp_schema(nc.oid))
- AND (pg_has_role(c.relowner, 'USAGE')
+ AND (pg_has_role(c.relowner, 'usage')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'UPDATE') );

-GRANT SELECT ON sequences TO PUBLIC;
+GRANT SELECT ON sequences TO public;


/*
@@ -1514,7 +1514,7 @@

-- Will be filled with external data by initdb.

-GRANT SELECT ON sql_features TO PUBLIC;
+GRANT SELECT ON sql_features TO public;


/*
@@ -1546,7 +1546,7 @@
INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');

-GRANT SELECT ON sql_implementation_info TO PUBLIC;
+GRANT SELECT ON sql_implementation_info TO public;


/*
@@ -1569,7 +1569,7 @@
INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');

-GRANT SELECT ON sql_languages TO PUBLIC;
+GRANT SELECT ON sql_languages TO public;


/*
@@ -1596,7 +1596,7 @@
INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');

-GRANT SELECT ON sql_packages TO PUBLIC;
+GRANT SELECT ON sql_packages TO public;


/*
@@ -1666,7 +1666,7 @@
comments = 'Might be less, depending on character set.'
WHERE supported_value = 63;

-GRANT SELECT ON sql_sizing TO PUBLIC;
+GRANT SELECT ON sql_sizing TO public;


/*
@@ -1686,7 +1686,7 @@
comments character_data
) WITHOUT OIDS;

-GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
+GRANT SELECT ON sql_sizing_profiles TO public;


/*
@@ -1721,7 +1721,7 @@
AND c.conrelid = r.oid
AND r.relkind = 'r'
AND (NOT pg_is_other_temp_schema(nr.oid))
- AND (pg_has_role(r.relowner, 'USAGE')
+ AND (pg_has_role(r.relowner, 'usage')
-- SELECT privilege omitted, per SQL standard
OR has_table_privilege(r.oid, 'INSERT')
OR has_table_privilege(r.oid, 'UPDATE')
@@ -1754,7 +1754,7 @@
AND NOT a.attisdropped
AND r.relkind = 'r'
AND (NOT pg_is_other_temp_schema(nr.oid))
- AND (pg_has_role(r.relowner, 'USAGE')
+ AND (pg_has_role(r.relowner, 'usage')
OR has_table_privilege(r.oid, 'SELECT')
OR has_table_privilege(r.oid, 'INSERT')
OR has_table_privilege(r.oid, 'UPDATE')
@@ -1762,7 +1762,7 @@
OR has_table_privilege(r.oid, 'REFERENCES')
OR has_table_privilege(r.oid, 'TRIGGER') );

-GRANT SELECT ON table_constraints TO PUBLIC;
+GRANT SELECT ON table_constraints TO public;


/*
@@ -1797,7 +1797,7 @@
(
SELECT oid, rolname FROM pg_authid
UNION ALL
- SELECT 0::oid, 'PUBLIC'
+ SELECT 0::oid, 'public'
) AS grantee (oid, rolname),
(SELECT 'SELECT' UNION ALL
SELECT 'DELETE' UNION ALL
@@ -1810,11 +1810,11 @@
AND c.relkind IN ('r', 'v')
AND aclcontains(c.relacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
- AND (pg_has_role(u_grantor.oid, 'USAGE')
- OR pg_has_role(grantee.oid, 'USAGE')
- OR grantee.rolname = 'PUBLIC');
+ AND (pg_has_role(u_grantor.oid, 'usage')
+ OR pg_has_role(grantee.oid, 'usage')
+ OR grantee.rolname = 'public');

-GRANT SELECT ON table_privileges TO PUBLIC;
+GRANT SELECT ON table_privileges TO public;


/*
@@ -1854,7 +1854,7 @@
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r', 'v')
AND (NOT pg_is_other_temp_schema(nc.oid))
- AND (pg_has_role(c.relowner, 'USAGE')
+ AND (pg_has_role(c.relowner, 'usage')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
@@ -1862,7 +1862,7 @@
OR has_table_privilege(c.oid, 'REFERENCES')
OR has_table_privilege(c.oid, 'TRIGGER') );

-GRANT SELECT ON tables TO PUBLIC;
+GRANT SELECT ON tables TO public;


/*
@@ -1899,7 +1899,7 @@
CAST(null AS sql_identifier) AS event_object_column
WHERE false;

-GRANT SELECT ON triggered_update_columns TO PUBLIC;
+GRANT SELECT ON triggered_update_columns TO public;


/*
@@ -1912,7 +1912,7 @@

/*
* 5.66
- * TRIGGER_ROUTINE_USAGE view
+ * TRIGGER_ROUTINE_e view
*/

-- not tracked by PostgreSQL
@@ -1975,7 +1975,7 @@
AND t.tgtype & em.num <> 0
AND NOT t.tgisconstraint
AND (NOT pg_is_other_temp_schema(n.oid))
- AND (pg_has_role(c.relowner, 'USAGE')
+ AND (pg_has_role(c.relowner, 'usage')
-- SELECT privilege omitted, per SQL standard
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
@@ -1983,7 +1983,7 @@
OR has_table_privilege(c.oid, 'REFERENCES')
OR has_table_privilege(c.oid, 'TRIGGER') );

-GRANT SELECT ON triggers TO PUBLIC;
+GRANT SELECT ON triggers TO public;


/*
@@ -2005,12 +2005,12 @@

CREATE VIEW usage_privileges AS
SELECT CAST(u.rolname AS sql_identifier) AS grantor,
- CAST('PUBLIC' AS sql_identifier) AS grantee,
+ CAST('public' AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS object_catalog,
CAST(n.nspname AS sql_identifier) AS object_schema,
CAST(t.typname AS sql_identifier) AS object_name,
CAST('DOMAIN' AS character_data) AS object_type,
- CAST('USAGE' AS character_data) AS privilege_type,
+ CAST('usage' AS character_data) AS privilege_type,
CAST('NO' AS character_data) AS is_grantable

FROM pg_authid u,
@@ -2021,7 +2021,7 @@
AND t.typnamespace = n.oid
AND t.typtype = 'd';

-GRANT SELECT ON usage_privileges TO PUBLIC;
+GRANT SELECT ON usage_privileges TO public;


/*
@@ -2066,9 +2066,9 @@
AND t.relkind IN ('r', 'v')
AND t.oid = a.attrelid
AND dt.refobjsubid = a.attnum
- AND pg_has_role(t.relowner, 'USAGE');
+ AND pg_has_role(t.relowner, 'usage');

-GRANT SELECT ON view_column_usage TO PUBLIC;
+GRANT SELECT ON view_column_usage TO public;


/*
@@ -2099,9 +2099,9 @@
AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
AND dp.refobjid = p.oid
AND p.pronamespace = np.oid
- AND pg_has_role(p.proowner, 'USAGE');
+ AND pg_has_role(p.proowner, 'usage');

-GRANT SELECT ON view_routine_usage TO PUBLIC;
+GRANT SELECT ON view_routine_usage TO public;


/*
@@ -2134,9 +2134,9 @@
AND dt.refobjid = t.oid
AND t.relnamespace = nt.oid
AND t.relkind IN ('r', 'v')
- AND pg_has_role(t.relowner, 'USAGE');
+ AND pg_has_role(t.relowner, 'usage');

-GRANT SELECT ON view_table_usage TO PUBLIC;
+GRANT SELECT ON view_table_usage TO public;


/*
@@ -2150,7 +2150,7 @@
CAST(c.relname AS sql_identifier) AS table_name,

CAST(
- CASE WHEN pg_has_role(c.relowner, 'USAGE')
+ CASE WHEN pg_has_role(c.relowner, 'usage')
THEN pg_get_viewdef(c.oid)
ELSE null END
AS character_data) AS view_definition,
@@ -2173,7 +2173,7 @@
WHERE c.relnamespace = nc.oid
AND c.relkind = 'v'
AND (NOT pg_is_other_temp_schema(nc.oid))
- AND (pg_has_role(c.relowner, 'USAGE')
+ AND (pg_has_role(c.relowner, 'usage')
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
@@ -2181,7 +2181,7 @@
OR has_table_privilege(c.oid, 'REFERENCES')
OR has_table_privilege(c.oid, 'TRIGGER') );

-GRANT SELECT ON views TO PUBLIC;
+GRANT SELECT ON views TO public;


-- The following views have dependencies that force them to appear out of order.
@@ -2211,7 +2211,7 @@
SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
) AS x (objschema, objname, objtype, objdtdid);

-GRANT SELECT ON data_type_privileges TO PUBLIC;
+GRANT SELECT ON data_type_privileges TO public;


/*
@@ -2303,4 +2303,4 @@
( SELECT object_schema, object_name, object_type, dtd_identifier
FROM data_type_privileges );

-GRANT SELECT ON element_types TO PUBLIC;
+GRANT SELECT ON element_types TO public;

=== modified file 'src/backend/catalog/system_views.sql'
--- src/backend/catalog/system_views.sql 2008-03-27 12:10:18 +0000
+++ src/backend/catalog/system_views.sql 2008-03-27 12:12:15 +0000
@@ -184,7 +184,7 @@
ON UPDATE TO pg_settings
DO INSTEAD NOTHING;

-GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+GRANT SELECT, UPDATE ON pg_settings TO public;

CREATE VIEW pg_timezone_abbrevs AS
SELECT * FROM pg_timezone_abbrevs();
@@ -427,7 +427,7 @@
) AS tt
WHERE tt.tokid = parse.tokid
$$
-LANGUAGE SQL STRICT STABLE;
+LANGUAGE sql STRICT STABLE;

COMMENT ON FUNCTION ts_debug(regconfig,text) IS
'debug function for text search configuration';
@@ -443,7 +443,7 @@
$$
SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
$$
-LANGUAGE SQL STRICT STABLE;
+LANGUAGE sql STRICT STABLE;

COMMENT ON FUNCTION ts_debug(text) IS
'debug function for current text search configuration';

=== modified file 'src/backend/parser/scansup.c'
--- src/backend/parser/scansup.c 2008-03-27 12:10:18 +0000
+++ src/backend/parser/scansup.c 2008-03-27 12:12:15 +0000
@@ -20,6 +20,7 @@
#include "parser/scansup.h"
#include "mb/pg_wchar.h"

+char *identifier_case_folding;

/* ----------------
* scanstr
@@ -130,9 +131,25 @@
{
char *result;
int i;
+ int folding;

result = palloc(len + 1);

+ if (identifier_case_folding == NULL)
+ {
+ folding = 0;
+ }
+ else if (strcmp(identifier_case_folding, "preserved") == 0)
+ {
+ folding = 0;
+ }
+ else if (strcmp(identifier_case_folding, "lower") == 0)
+ {
+ folding = 1;
+ }
+ else
+ folding = 2;
+
/*
* SQL99 specifies Unicode-aware case normalization, which we don't yet
* have the infrastructure for. Instead we use tolower() to provide a
@@ -145,11 +162,22 @@
for (i = 0; i < len; i++)
{
unsigned char ch = (unsigned char) ident[i];
+ switch (folding)
+ {
+ case 1:
+ if (ch >= 'A' && ch <= 'Z')
+ ch += 'a' - 'A';
+ else if (IS_HIGHBIT_SET(ch) && isupper(ch))
+ ch = tolower(ch);
+ break;
+ case 2:
+ if (ch >= 'a' && ch <= 'z')
+ ch -= 'a' - 'A';
+ else if (IS_HIGHBIT_SET(ch) && islower(ch))
+ ch = toupper(ch);
+ break;
+ }

- if (ch >= 'A' && ch <= 'Z')
- ch += 'a' - 'A';
- else if (IS_HIGHBIT_SET(ch) && isupper(ch))
- ch = tolower(ch);
result[i] = (char) ch;
}
result[i] = '\0';

=== modified file 'src/backend/snowball/snowball_func.sql.in'
--- src/backend/snowball/snowball_func.sql.in 2008-03-27 12:10:18 +0000
+++ src/backend/snowball/snowball_func.sql.in 2008-03-27 12:12:15 +0000
@@ -2,13 +2,13 @@

SET search_path = pg_catalog;

-CREATE FUNCTION dsnowball_init(INTERNAL)
- RETURNS INTERNAL AS '$libdir/dict_snowball', 'dsnowball_init'
-LANGUAGE C STRICT;
+CREATE FUNCTION dsnowball_init(internal)
+ RETURNS internal AS '$libdir/dict_snowball', 'dsnowball_init'
+LANGUAGE c STRICT;

-CREATE FUNCTION dsnowball_lexize(INTERNAL, INTERNAL, INTERNAL, INTERNAL)
- RETURNS INTERNAL AS '$libdir/dict_snowball', 'dsnowball_lexize'
-LANGUAGE C STRICT;
+CREATE FUNCTION dsnowball_lexize(internal, internal, internal, internal)
+ RETURNS internal AS '$libdir/dict_snowball', 'dsnowball_lexize'
+LANGUAGE c STRICT;

CREATE TEXT SEARCH TEMPLATE snowball
(INIT = dsnowball_init,

=== modified file 'src/backend/utils/adt/ruleutils.c'
--- src/backend/utils/adt/ruleutils.c 2008-03-27 12:10:18 +0000
+++ src/backend/utils/adt/ruleutils.c 2008-03-27 12:12:15 +0000
@@ -40,6 +40,7 @@
#include "parser/parse_func.h"
#include "parser/parse_oper.h"
#include "parser/parsetree.h"
+#include "parser/scansup.h"
#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "rewrite/rewriteSupport.h"
@@ -5185,19 +5186,38 @@
const char *ptr;
char *result;
char *optr;
+ bool foldingLower;
+ bool foldingUpper;
+
+ if (strcmp(identifier_case_folding, "preserved") == 0)
+ {
+ foldingLower = false;
+ foldingUpper = false;
+ }
+ else if (strcmp(identifier_case_folding, "lower") == 0)
+ {
+ foldingLower = true;
+ foldingUpper = false;
+ }
+ else
+ {
+ foldingLower = false;
+ foldingUpper = true;
+ }

/*
* would like to use <ctype.h> macros here, but they might yield unwanted
* locale-specific results...
*/
- safe = ((ident[0] >= 'a' && ident[0] <= 'z') || ident[0] == '_');
+ safe = ((!foldingUpper && ident[0] >= 'a' && ident[0] <= 'z') || ident[0] == '_' || (!foldingLower && ident[0] >= 'A' && ident[0] <= 'Z'));

for (ptr = ident; *ptr; ptr++)
{
char ch = *ptr;

- if ((ch >= 'a' && ch <= 'z') ||
+ if ((!foldingUpper && ch >= 'a' && ch <= 'z') ||
(ch >= '0' && ch <= '9') ||
+ (!foldingLower && ch >= 'A' && ch <= 'Z') ||
(ch == '_'))
{
/* okay */

=== modified file 'src/backend/utils/mb/conversion_procs/Makefile'
--- src/backend/utils/mb/conversion_procs/Makefile 2008-03-27 11:43:42 +0000
+++ src/backend/utils/mb/conversion_procs/Makefile 2008-03-27 14:14:02 +0000
@@ -173,7 +173,7 @@
func=$$1; shift; \
obj=$$1; shift; \
echo "-- $$se --> $$de"; \
- echo "CREATE OR REPLACE FUNCTION $$func (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$$"libdir"/$$obj', '$$func' LANGUAGE C STRICT;"; \
+ echo "CREATE OR REPLACE FUNCTION $$func (integer, integer, cstring, internal, integer) RETURNS void AS '$$"libdir"/$$obj', '$$func' LANGUAGE c strict;"; \
echo "DROP CONVERSION pg_catalog.$$name;"; \
echo "CREATE DEFAULT CONVERSION pg_catalog.$$name FOR '$$se' TO '$$de' FROM $$func;"; \
done > $@

=== modified file 'src/backend/utils/misc/check_guc'
--- src/backend/utils/misc/check_guc 2008-03-27 12:10:18 +0000
+++ src/backend/utils/misc/check_guc 2008-03-27 12:12:15 +0000
@@ -21,7 +21,7 @@
pre_auth_delay role seed server_encoding server_version server_version_int \
session_authorization trace_lock_oidmin trace_lock_table trace_locks trace_lwlocks \
trace_notify trace_userlocks transaction_isolation transaction_read_only \
-zero_damaged_pages"
+zero_damaged_pages identifier_case_folding"

### What options are listed in postgresql.conf.sample, but don't appear
### in guc.c?

=== modified file 'src/backend/utils/misc/guc.c'
--- src/backend/utils/misc/guc.c 2008-03-27 12:10:18 +0000
+++ src/backend/utils/misc/guc.c 2008-03-27 14:13:14 +0000
@@ -2447,6 +2447,16 @@
&TSCurrentConfig,
"pg_catalog.simple", assignTSCurrentConfig, NULL
},
+
+ {
+ {"identifier_case_folding", PGC_INTERNAL, COMPAT_OPTIONS_CLIENT,
+ gettext_noop("Shows the identifier case folding. Options are lower, upper or preserve case."),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ },
+ &identifier_case_folding,
+ NULL, NULL, NULL
+ },

#ifdef USE_SSL
{

=== modified file 'src/bin/initdb/initdb.c'
--- src/bin/initdb/initdb.c 2008-03-27 12:10:18 +0000
+++ src/bin/initdb/initdb.c 2008-03-27 12:12:15 +0000
@@ -1798,8 +1798,8 @@
"UPDATE pg_class "
" SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
" WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
- "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
+ "GRANT usage ON SCHEMA pg_catalog TO public;\n",
+ "GRANT create, usage ON SCHEMA public TO public;\n",
NULL
};

=== modified file 'src/bin/pg_controldata/pg_controldata.c'
--- src/bin/pg_controldata/pg_controldata.c 2008-03-27 12:10:18 +0000
+++ src/bin/pg_controldata/pg_controldata.c 2008-03-27 12:12:15 +0000
@@ -60,7 +60,6 @@
return _("unrecognized status code");
}

-
int
main(int argc, char *argv[])
{
@@ -208,6 +207,8 @@
ControlFile.toast_max_chunk_size);
printf(_("Date/time type storage: %s\n"),
(ControlFile.enableIntTimes ? _("64-bit integers") : _("floating-point numbers")));
+ printf(_("Identifier case folding: %s\n"),
+ ControlFile.identifierCaseFolding);
printf(_("Maximum length of locale name: %u\n"),
ControlFile.localeBuflen);
printf(_("LC_COLLATE: %s\n"),

=== modified file 'src/include/catalog/pg_control.h'
--- src/include/catalog/pg_control.h 2008-03-27 12:10:18 +0000
+++ src/include/catalog/pg_control.h 2008-03-27 12:12:15 +0000
@@ -22,7 +22,7 @@


/* Version identifier for this pg_control format */
-#define PG_CONTROL_VERSION 833
+#define PG_CONTROL_VERSION 834

/*
* Body of CheckPoint XLOG records. This is declared here because we keep
@@ -141,6 +141,9 @@
/* flag indicating internal format of timestamp, interval, time */
uint32 enableIntTimes; /* int64 storage enabled? */

+ /* This data defines the case folding set by the initdb */
+ char identifierCaseFolding[10]; /* what case folding option was used at initdb time? */
+
/* active locales */
uint32 localeBuflen;
char lc_collate[LOCALE_NAME_BUFLEN];

=== modified file 'src/include/parser/scansup.h'
--- src/include/parser/scansup.h 2008-03-27 12:10:18 +0000
+++ src/include/parser/scansup.h 2008-03-27 12:12:15 +0000
@@ -15,6 +15,8 @@
#ifndef SCANSUP_H
#define SCANSUP_H

+extern char *identifier_case_folding;
+
extern char *scanstr(const char *s);

extern char *downcase_truncate_identifier(const char *ident, int len,

=== modified file 'src/test/regress/expected/bit.out'
--- src/test/regress/expected/bit.out 2008-03-27 12:10:18 +0000
+++ src/test/regress/expected/bit.out 2008-03-27 12:12:15 +0000
@@ -107,7 +107,7 @@
(4 rows)

--- Bit operations
-DROP TABLE varbit_table;
+DROP TABLE VARBIT_TABLE;
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
COPY varbit_table FROM stdin;
SELECT a, b, ~a AS "~ a", a & b AS "a & b",

=== modified file 'src/test/regress/expected/float8.out'
--- src/test/regress/expected/float8.out 2008-03-27 12:10:18 +0000
+++ src/test/regress/expected/float8.out 2008-03-27 12:12:15 +0000
@@ -232,7 +232,7 @@
(5 rows)

-- ceil / ceiling
-select ceil(f1) as ceil_f1 from float8_tbl f;
+select ceil(f1) as ceil_f1 from FLOAT8_TBL f;
ceil_f1
----------------------
0
@@ -242,7 +242,7 @@
1
(5 rows)

-select ceiling(f1) as ceiling_f1 from float8_tbl f;
+select ceiling(f1) as ceiling_f1 from FLOAT8_TBL f;
ceiling_f1
----------------------
0
@@ -253,7 +253,7 @@
(5 rows)

-- floor
-select floor(f1) as floor_f1 from float8_tbl f;
+select floor(f1) as floor_f1 from FLOAT8_TBL f;
floor_f1
----------------------
0
@@ -264,7 +264,7 @@
(5 rows)

-- sign
-select sign(f1) as sign_f1 from float8_tbl f;
+select sign(f1) as sign_f1 from FLOAT8_TBL f;
sign_f1
---------
0

=== modified file 'src/test/regress/expected/numeric.out'
--- src/test/regress/expected/numeric.out 2008-03-27 12:10:18 +0000
+++ src/test/regress/expected/numeric.out 2008-03-27 12:12:15 +0000
@@ -646,10 +646,10 @@
(0 rows)

-- ******************************
--- * POWER(10, LN(value)) check
+-- * power(10, LN(value)) check
-- ******************************
DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200))))
+INSERT INTO num_result SELECT id, 0, power(numeric '10', ln(abs(round(val,200))))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected

=== modified file 'src/test/regress/sql/bit.sql'
--- src/test/regress/sql/bit.sql 2008-03-27 12:10:18 +0000
+++ src/test/regress/sql/bit.sql 2008-03-27 12:12:15 +0000
@@ -54,7 +54,7 @@
FROM VARBIT_TABLE;

--- Bit operations
-DROP TABLE varbit_table;
+DROP TABLE VARBIT_TABLE;
CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
COPY varbit_table FROM stdin;
X0F X10
@@ -78,7 +78,7 @@
DROP TABLE varbit_table;

--- Bit operations
-DROP TABLE bit_table;
+DROP TABLE BIT_TABLE;
CREATE TABLE bit_table (a BIT(16), b BIT(16));
COPY bit_table FROM stdin;
X0F00 X1000

=== modified file 'src/test/regress/sql/float8.sql'
--- src/test/regress/sql/float8.sql 2008-03-27 12:10:18 +0000
+++ src/test/regress/sql/float8.sql 2008-03-27 12:12:15 +0000
@@ -87,14 +87,14 @@
FROM FLOAT8_TBL f;

-- ceil / ceiling
-select ceil(f1) as ceil_f1 from float8_tbl f;
-select ceiling(f1) as ceiling_f1 from float8_tbl f;
+select ceil(f1) as ceil_f1 from FLOAT8_TBL f;
+select ceiling(f1) as ceiling_f1 from FLOAT8_TBL f;

-- floor
-select floor(f1) as floor_f1 from float8_tbl f;
+select floor(f1) as floor_f1 from FLOAT8_TBL f;

-- sign
-select sign(f1) as sign_f1 from float8_tbl f;
+select sign(f1) as sign_f1 from FLOAT8_TBL f;

-- square root
SELECT sqrt(float8 '64') AS eight;

=== modified file 'src/test/regress/sql/numeric.sql'
--- src/test/regress/sql/numeric.sql 2008-03-27 12:10:18 +0000
+++ src/test/regress/sql/numeric.sql 2008-03-27 12:12:15 +0000
@@ -591,7 +591,7 @@
-- * Square root check
-- ******************************
DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, SQRT(ABS(val))
+INSERT INTO num_result SELECT id, 0, sqrt(abs(val))
FROM num_data;
SELECT t1.id1, t1.result, t2.expected
FROM num_result t1, num_exp_sqrt t2
@@ -602,7 +602,7 @@
-- * Natural logarithm check
-- ******************************
DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, LN(ABS(val))
+INSERT INTO num_result SELECT id, 0, ln(abs(val))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
@@ -614,7 +614,7 @@
-- * Logarithm base 10 check
-- ******************************
DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val))
+INSERT INTO num_result SELECT id, 0, log(numeric '10', abs(val))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
@@ -623,10 +623,10 @@
AND t1.result != t2.expected;

-- ******************************
--- * POWER(10, LN(value)) check
+-- * power(10, ln(value)) check
-- ******************************
DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200))))
+INSERT INTO num_result SELECT id, 0, power(numeric '10', ln(abs(round(val,200))))
FROM num_data
WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
@@ -638,9 +638,9 @@
-- * miscellaneous checks for things that have been broken in the past...
-- ******************************
-- numeric AVG used to fail on some platforms
-SELECT AVG(val) FROM num_data;
-SELECT STDDEV(val) FROM num_data;
-SELECT VARIANCE(val) FROM num_data;
+SELECT avg(val) FROM num_data;
+SELECT stddev(val) FROM num_data;
+SELECT variance(val) FROM num_data;

-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));

Andrew Dunstan wrote:
> I'm not sure if you've read all the archive history on this. Here are
> the pointers from the TODO list:
>
> http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php
> http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php
> http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php
>
>
> The fact is that we have substantial groups of users who want
> different things:
> . current users who want no change so there is no breakage in existing
> apps
> . users on other DBs who want Spec compliance
> . users on yet other DBs who want case preservation
>
> The last group should not be lightly dismissed - it is quite common
> behaviour on MSSQL as well as on MySQL, so we have some incentive to
> make this possible to encourage migration.
>
> I'm strongly of the opinion therefore that this should be behaviour
> determined at initdb time (can't make it later because of shared
> catalogs). I suspect that we won't be able to do all this by simple
> transformations in the lexer, unlike what we do now. But I do think
> it's worth doing.
>
> cheers
>
> andrew
>
Hi,

as part of the
http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php
thread, I did a reasonable amount of discovery work on making the 3
options andrew presents a reality. As I'm not skilled enough I never
got far enough to make them all work at once. I did however get lower
case and case preservation working.

To make those tow work the catalogs need no changes. Some of the
regressions tests expect case folding, so they did need changing to
operate correctly. I was unable to make the input files to initdb
correctly fold the case of system catalogs for the upper case version.
I'm sure somebody with more experience would not find it as difficult as
I did. Function names tended to be where all the gotchas were. Count()
vs count() vs COUNT() for example.

Once the db was up and running, the issue becomes all the supporting
tools. psql was made to autocomplete with case preservation, I was
going to make pg_dump just quote everything. I then got to the point of
adding a fixed GUC like LC_LOCALE that allows psql to read the case
folding situation and act according. That is where my progress ended.

Attached is what i had worked in. It's a patch against 8.3.1. I know
it's not CVS head, but it is what I was using at the time to experiment.

Regards

Russell

Re: [HACKERS] Windows 64-bit work in progress patch

Here is the work in progress patch described in this thread.

Re: [HACKERS] Identifier case folding notes

"Peter Eisentraut" <peter_e@gmx.net> writes:

> One disadvantage is that one could no longer have objects that have names
> different only by case, but that is probably rare and incredibly stupid and
> can be neglected.

Certainly not if you hope to claim being within a mile of spec -- which seems
like the only point of fiddling with this. Breaking this would take as further
from spec-compliance than we are today.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's PostGIS 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] Identifier case folding notes

Peter Eisentraut wrote:
> I have had some idle thoughts on the issue of identifier case folding. Maybe
> we can collect our ideas and inch closer to a solution sometime. Or we
> determine that it's useless and impossible, but then I can at least collect
> that result in the wiki and point future users to it.
>
> Background: PostgreSQL folds unquoted identifiers to lower case. SQL
> specifies folding to upper case, and most other SQL DBMS do it that way. If
> an application mixes referring to an object using quoted and unquoted
> versions of an identifier, then incompatibilities arise. We have always
> stressed to users that one should refer to an object always unquoted or
> always quoted. While that remains a good suggestion for a number of reasons,
> we have seen occasional complaints that some closed source applications
> violate this rule and therefore cannot be run on PostgreSQL.
>
> A number of solutions have been proposed over time, which I summarize here:
>
> 1. Change the lexer to fold to upper case, as it is supposed to do according
> to the SQL standard. This will break almost everything, because almost all
> built-in objects have lower-case names and thus couldn't be referred to
> anymore except by quoting. Changing the names of all the internal objects to
> upper-case names would involve vast code changes, probably break just as much,
> and make everything uglier. So this approach is unworkable.
>
> 2. Fold to upper case, but not when referring built-in objects. The lexer
> generally doesn't know what a name will refer to, so this is not possible to
> implement, at least without resorting to lots of hard-coding or horrid
> kludges. Also, a behavior like this will probably create all kinds of weird
> inconsistencies, resulting from putting catalog knowledge in the lexer.
>
> 3 and 4 -- Two variants of "ignore case altogether":
>
> 3. Fold all identifiers to lower case, even quoted ones. This would probably
> in fact fix the breakage of many of the above-mentioned problem applications,
> and it would in general be very easy to understand for a user. And it could
> be implemented in about three lines. One disadvantage is that one could no
> longer have objects that have names different only by case, but that is
> probably rare and incredibly stupid and can be neglected. The main
> disadvantage is that the case of identifiers and in particular column labels
> is lost. So applications and programming interfaces that look up result
> columns in a case-sensitive manner would fail. And things like SELECT expr AS
> "Nice Heading" won't work properly anymore.
>
> 4. Compare the "name" data type in a case-insensitive manner. This would
> probably address most problem cases. Again, you can't have objects with names
> different in case only. One condition to implementing this would be that this
> behavior would have be tied down globally at initdb, because it affects system
> indexes and shared catalogs. That might be impractical for some, because
> you'd need different instances for different behaviors, especially when you
> want to host multiple applications or want to port an affected application to
> the native PostgreSQL behavior over time.
>
> 5. One additional approach I thought of is that you swap the case of
> identifiers as you lex them (upper to lower, lower to upper), and then swap
> them back when you send them to the client. This needs a small change in the
> lexer, one for sending the RowDescription, and support in pg_dump and a few
> other places if desired. There will, however, be a number of weird, albeit
> self-imposed, side-effects. I have implemented a little test patch for this.
> It's weird, but it works in basic ways.
>
> Obviously, no solution will ever work completely. And we probably don't want
> such a solution, because it would create two different and incompatible
> PostgreSQL universes. If we are aiming for a solution that would allow most
> affected applications to hobble along, we would probably serve most users.
> Implementing some or all of 3, 4, and 5 would probably achieve that.
>
>


I'm not sure if you've read all the archive history on this. Here are
the pointers from the TODO list:

http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php

http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php

http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php


The fact is that we have substantial groups of users who want different
things:
. current users who want no change so there is no breakage in existing apps
. users on other DBs who want Spec compliance
. users on yet other DBs who want case preservation

The last group should not be lightly dismissed - it is quite common
behaviour on MSSQL as well as on MySQL, so we have some incentive to
make this possible to encourage migration.

I'm strongly of the opinion therefore that this should be behaviour
determined at initdb time (can't make it later because of shared
catalogs). I suspect that we won't be able to do all this by simple
transformations in the lexer, unlike what we do now. But I do think it's
worth doing.

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: [pgadmin-hackers] Enabling SQL text field in the SQL tab of object dialog

Guillaume Lelarge a écrit :
> [...]
> The patch attached fixed some issues:
> * The "Are you sure..." message is displayed only when "Read Only" is
> checked.
> * No button is the default on the "Are you sure" message (which has a
> weird behaviour... hitting Esc will validate the dialog and the user
> will lose his changes... I'm not sure we should keep this).
> * the first issue you had.
> * Sizing problems fixed on Linux with a wxFlexGridSizer.
>
> Remaining issues:
> * Objects' size on Windows (and Max OS X ?).
>

I worked on the remaining issue tonight and it seems I can fix it this
way: I add a wxFlexGridSizer and a few sizers on the .xrc file. It works
well on the Linux and on the Win32 plateforms (I only check with
dlgDatabase.xrc file). If I'm right, this means I need to add these
widgets on each properties' dialog that doesn't already have them (all
but functions and trigger' ones).

If I'm still right, I see two ways to deal with this :

1. I add the wxFlexGridSizer widget on each dialog that needs it, I then
create a patch with my dlgProperty's changes and the .xrc files'
changes. I apply this (when allowed), and I can work later on each xrc
file to add full support of the wxFlexGridSizer.

2. I first fix each dialog with the wxFlexGridSizer to add full support,
and then I can resubmit this patch.

I tend to prefer the first one. Comments?


--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.com

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

[HACKERS] Summary of some postgres portability issues

In trying to port postgres to 64-bit Windows, I've encountered a
number of issues which may (or may not) affect other compilers. If
you followed the other thread, this is mostly a summary with a bit
more details so feel free to ignore it. Some of these may have some
minor effects on other platforms, so they may be of interest (but I
doubt it, since no one has noticed/complained about them yet). This
post contains a small taxonomy of the problems, as well as some
discussion about the work that needs to be done in order to make
postgres portable to LLP64 data model compilers (in case someone else
is interested). I use the standard terms for discussing different
compiler data models, which are explained here:

http://www.unix.org/version2/whatsnew/lp64_wp.html

Throughout this post I will assume sizeof(size_t) == sizeof(void*),
because I doubt you want to support a system where this is not the
case.

When I try to compile postgres, I get 396 warnings. These come from
several different places:

1.) Most of the code involving strings requires a ILP32 or ILP64 to
not generate a warning. This means sizeof(int) == sizeof(size_t) ==
32 or 64, respectively. Something as simple as:

int len;

len = strlen(str);

violates this on LP32, LP64, and LLP64. AFAIK, there really are no
LP32 compilers around anymore, but LP64 is common (LLP64 is MSVC).

None of these warnings are actually problems, since they involve
strings and realistically the problems which "could happen" never
will. Unfortunately, these are actually portability problems, since
you never want to disable narrow cast warnings when supporting
different architectures because some of the warnings could be
important. If these aren't disabled, they will be very annoying and
make it hard to spot real problems (and tempt people to turn off all
such warnings). If they are changed, almost 300 lines will need to be
committed, all of which have the not very exciting form:

int len;

len = (int)strlen(str);

the alternative is changing int to size_t everywhere, which several
have objected to because of bloat. This bloat will only affect LP64
and LLP64, which do not seem to have been the target machines in the
first place. I'd be willing to make the changes to either form, but I
don't know if anyone would be willing to commit them :)

2.) There is a lot of other code involving memory index and offset
calculations being "int". On ILP64, these will be able to work with
buffers > 2 GB. On LP64 or LLP64, they will not. On ILP64,
sizeof(int) == sizeof(size_t), but on the other two sizeof(int) <
sizeof(size_t). Either c.h or postgres.h (I forgot which) defines an
Offset and Index typedef to aid in portability, but they are only
rarely used. Most of the unchecked conversions from size_t to int are
of the string variety (1), but there are a fair amount of these as
well.

None of these warnings are actually problems even on LP64 or LLP64,
unless the buffers involved are > 2 GB. Buffers > 2 GB will work with
no changes on ILP64 only. Whether the problem domain specifies that
they _can't_ (or probably never should) be > 2 GB either way must be
examined on a case by case basis, and I haven't examined that yet.

Thoughts on 1 & 2
==============

I was surprised to see this in the code. The reason is that both of
these issues affect LP64. Problems with LLP64 are expected, because
LLP64 basically means "Microsoft" and therefore support is not usually
a concern of the OSS community. LP64 on the other hand is any x64
machine using gcc, or at least it was several years ago. Has that
changed? Can gcc now be configured to use ILP64 instead?

3.) Some of the code assigns size_t to uint16. These should elicit
warnings on all compilers, but are almost certainly guaranteed to
never be errors because the use of uint16 implies that the developer
clearly knows that this is the maximum size needed in all situations
and the precision loss never matters (here, the size_t variables being
cast were probably not really supposed to be size_t in the first
place, but Size was used in the RHS with no cast, carelessly).

4.) Some of the code assigns size_t to uint32. It's unclear if these
cases are like (2) or like (3), and would need to be examined on a
case by case basis.

Problems for LLP64 compilers
======================

Almost everywhere the keyword "long" appears, is a problem for LLP64
whether its a warning or not. Unfortunately this happens in a very
large number of places. Size is supposed to be used for memory
resident objects, but rarely is. Often (signed) long is used, and its
not immediately clear without fully reading through the program
whether it actually needs to be signed or not (i.e., whether it can
safely be changed to Size or not). Sometimes it does need to be
signed, as allocations can apparently be negative, and this is
required for correct program operation i.e., there are a few:

while !(something_which_should_semantically_never_be_less_than_0 < 0)

The types of problems that are easy to spot raise warnings because
they freely mix Size (which is typedef size_t Size;) and long. This
way, you are alerted to problem. This will not find any problems
where long was used throughout. Note that none of this is
particularly hard to do, it just takes time. Does that mean that
almost every part of postgres that interacts with memory or the Datum
type must be read very carefully to rediscover all the assumptions
behind the code? Unfortunately I would guess yes. This is going to
take much longer than I thought. But it's that or use MySQL, which
crashes whenever I load the data in.

-Ken

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

[JDBC] Patch to allow setting schema/search_path in the connectionURL

Many years ago, Andreas Joseph Krogh submitted a patch to allow the
setting of the default schema in a JDBC connection string:

http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00187.php

It was rejected with some objections:

http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00189.php

I've made a modern version of this patch against the current CVS head
that hopefully addresses the earlier objections on this list. In this
version, however, the parameter is called 'searchpath' instead of 'schema'

I find this functionality useful for a data reporting tool like BIRT:

http://www.eclipse.org/birt/phoenix/
that expects you to provide the JDBC connection parameters but where the
tool will build appropriate SQL queries for report generation - based
on your manipulation of the report designer interface. Our DBA likes to
use postgresql schemas for namespace management. Setting a searchpath
keyword in the JDBC connection parameters allows the BIRT reports to be
easily portable for use with differently-named schemas.

E.g.:

jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema

Incidentally, I noticed that the npgsql developers added similar
functionality to their Posgresql driver in November of last year:

http://archives.postgresql.org/pgsql-committers/2007-11/msg00506.php

Regards,

Scott Langley
--
Systems Analyst/Programmer
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Fred Hutchinson Cancer Research Center
Seattle, Washington
slangley@scharp.org

Re: [HACKERS] gsoc, text search selectivity and dllist enhancments

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <j.urbanski@students.mimuw.edu.pl> writes:
> Do you think it's worthwhile to implement the LC algorithm in C and send
> it out, so others could try it out? Heck, maybe it's worthwhile to
> replace the current compute_minimal_stats() algorithm with LC and see
> how that compares?

Very possibly. I repeat that the current implementation of
compute_minimal_stats is very ad-hoc code and wasn't written with an eye
to high performance. Replacing it with an algorithm that someone
actually thought about might well be worth doing.

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] Identifier case folding notes

Andreas Joseph Krogh <andreak@officenet.no> writes:
> Right. From a user's perspective 4) sounds best. I often run into problems having keywords as column-names:

None of the proposals on the table will remove the need to use quotes in
that case.

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] gsoc, text search selectivity and dllist enhancments

Jan UrbaƄski wrote:
> If you think the Lossy Counting method has potential, I could test it
> somehow. Using my current work I could extract a stream of lexemes as
> ANALYZE sees it and run it through a python implementation of the
> algorithm to see if the result makes sense.

I hacked together a simplistic python implementation and ran it on a
table with 244901 tsvectors, 45624891 lexemes total. I was comparing
results from my current approach with the results I'd get from a Lossy
Counting algorithm.
I experimented with statistics_target set to 10 and 100, and ran pruning
in the LC algorithm every 3, 10 or 100 tsvectors.
The sample size with statistics_target set to 100 was 30000 rows and
that's what the input to the script was - lexemes from these 30000
tsvectors.
I found out that with pruning happening every 10 tsvectors I got
precisely the same results as with the original algorithm (same most
common lexemes, same frequencies). When I tried pruning after every 100
tsvectors the results changed very slightly (they were a tiny bit more
distant from the ones from the original algorithm, and I think a tiny
bit more precise, but I didn't give it much attention).

Bottom line seems to be: the Lossy Counting algorithm gives roughly the
same results as the algorithm used currently and is also possibly faster
(and more scalable wrt. statistics_target).

This should probably get more testing than just running some script 5
times over a fixed set of data, but I had trouble already sucking ~300
MB of tsvectors from one of my production sites, putting it on my laptop
and so on.
Do you think it's worthwhile to implement the LC algorithm in C and send
it out, so others could try it out? Heck, maybe it's worthwhile to
replace the current compute_minimal_stats() algorithm with LC and see
how that compares?

Anyway, I can share the python script if someone would like to do some
more tests (I suppose no-one would, 'cause you first need to apply my
ts_typanalyze patch and then change it some more to extract lexemes from
the sample).

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin


--
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] Identifier case folding notes

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Josh Berkus <josh@agliodbs.com> wrote:
>> Actually, there are a number of *very* popular database tools,
>> particularly
>> in the Java world (such as Netbeans and BIRT) which do mix quoted and
>> unquoted identifiers.

> Do these tools expect an unquoted identifier to be treated according
> to the standard?

Unfortunately, they almost certainly don't. I'd bet long odds that
what they expect is mysql's traditional behavior, which is not even
within hailing distance of being spec compliant. (In a quick test,
it looks like mysql 5.0's default behavior is never to fold case
at all; and then there's the problem that they use the wrong kind
of quotes ...)

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

[COMMITTERS] pgsql: Fix performance bug in write_syslog(): the code to preferentially

Log Message:
-----------
Fix performance bug in write_syslog(): the code to preferentially break the
log message at newlines cost O(N^2) for very long messages with few or no
newlines. For messages in the megabyte range this became the dominant cost.
Per gripe from Achilleas Mantzios.

Patch all the way back, since this is a safe change with no portability
risks. I am also thinking of increasing PG_SYSLOG_LIMIT, but that should
be done separately.

Tags:
----
REL7_4_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/error:
elog.c (r1.125.2.3 -> r1.125.2.4)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c?r1=1.125.2.3&r2=1.125.2.4)

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

[COMMITTERS] pgsql: Fix performance bug in write_syslog(): the code to preferentially

Log Message:
-----------
Fix performance bug in write_syslog(): the code to preferentially break the
log message at newlines cost O(N^2) for very long messages with few or no
newlines. For messages in the megabyte range this became the dominant cost.
Per gripe from Achilleas Mantzios.

Patch all the way back, since this is a safe change with no portability
risks. I am also thinking of increasing PG_SYSLOG_LIMIT, but that should
be done separately.

Tags:
----
REL8_1_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/error:
elog.c (r1.167.2.5 -> r1.167.2.6)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c?r1=1.167.2.5&r2=1.167.2.6)

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

[COMMITTERS] pgsql: Fix performance bug in write_syslog(): the code to preferentially

Log Message:
-----------
Fix performance bug in write_syslog(): the code to preferentially break the
log message at newlines cost O(N^2) for very long messages with few or no
newlines. For messages in the megabyte range this became the dominant cost.
Per gripe from Achilleas Mantzios.

Patch all the way back, since this is a safe change with no portability
risks. I am also thinking of increasing PG_SYSLOG_LIMIT, but that should
be done separately.

Tags:
----
REL8_0_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/error:
elog.c (r1.155.4.7 -> r1.155.4.8)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c?r1=1.155.4.7&r2=1.155.4.8)

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

[COMMITTERS] pgsql: Fix performance bug in write_syslog(): the code to preferentially

Log Message:
-----------
Fix performance bug in write_syslog(): the code to preferentially break the
log message at newlines cost O(N^2) for very long messages with few or no
newlines. For messages in the megabyte range this became the dominant cost.
Per gripe from Achilleas Mantzios.

Patch all the way back, since this is a safe change with no portability
risks. I am also thinking of increasing PG_SYSLOG_LIMIT, but that should
be done separately.

Tags:
----
REL8_2_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/error:
elog.c (r1.178.2.4 -> r1.178.2.5)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c?r1=1.178.2.4&r2=1.178.2.5)

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

[COMMITTERS] pgsql: Fix performance bug in write_syslog(): the code to preferentially

Log Message:
-----------
Fix performance bug in write_syslog(): the code to preferentially break the
log message at newlines cost O(N^2) for very long messages with few or no
newlines. For messages in the megabyte range this became the dominant cost.
Per gripe from Achilleas Mantzios.

Patch all the way back, since this is a safe change with no portability
risks. I am also thinking of increasing PG_SYSLOG_LIMIT, but that should
be done separately.

Tags:
----
REL8_3_STABLE

Modified Files:
--------------
pgsql/src/backend/utils/error:
elog.c (r1.201 -> r1.201.2.1)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c?r1=1.201&r2=1.201.2.1)

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

[COMMITTERS] pgsql: Fix performance bug in write_syslog(): the code to preferentially

Log Message:
-----------
Fix performance bug in write_syslog(): the code to preferentially break the
log message at newlines cost O(N^2) for very long messages with few or no
newlines. For messages in the megabyte range this became the dominant cost.
Per gripe from Achilleas Mantzios.

Patch all the way back, since this is a safe change with no portability
risks. I am also thinking of increasing PG_SYSLOG_LIMIT, but that should
be done separately.

Modified Files:
--------------
pgsql/src/backend/utils/error:
elog.c (r1.203 -> r1.204)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c?r1=1.203&r2=1.204)

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

Re: [SQL] Re: Best practice for long-lived journal tables: bigint or recycling IDs?

Mark Stosberg wrote:
> On Tue, 8 Jul 2008 17:20:13 -0400
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> > 3. Deal with wraparound by ensuring that the applications behave sanely
>
> Wrap-around?
>
> Exceeding the max size of "int" looks more like a brick wall than wrap-around to me:
>
> insert into t values (2147483648);
> ERROR: integer out of range

Hmm, you can alter the sequence so that it wraps around at the point it
reaches INT_MAX. So inserting this number would never actually happen.

alvherre=# create table t (a serial);
NOTICE: CREATE TABLE créera des séquences implicites « t_a_seq » pour la colonne serial « t.a »
CREATE TABLE
alvherre=# alter sequence t_a_seq maxvalue 2147483647;
ALTER SEQUENCE
alvherre=# alter sequence t_a_seq cycle;
ALTER SEQUENCE
alvherre=# select setval('t_a_seq', 2147483645);
setval
------------
2147483645
(1 ligne)

alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# select * from t;
a
------------
2147483646
2147483647
1
2
3
(5 lignes)


--
Alvaro Herrera

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

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

Re: [HACKERS] Identifier case folding notes

>>> Ron Mayer <rm_pg@cheapcomplexdevices.com> wrote:

> +1 for a compile-time option for spec-compliant behavior. Even
> where the spec is stupid (timestamp with time zone literals) it'd
> be nice to have the option; both for feature completeness
> checklists and for teachers who want to teach targeting the spec.

In my world it would be even more important for feature completeness
itself, and for production applications written to the spec for
portability. But, agreed: +1

-Kevin

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

[GENERAL] Happy birthday!

If I'm not mistaken 8th July is official birthday of PostgreSQL. 12
years, great!

(i'm a bit late here in Russia, but it's OK for US ;-) )

Kudos PostgreSQL!

--
Best regards,
Nikolay

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

[GENERAL] array sort for varchar arrays?

I'm likely overlooking something, but I can't seem to find a function to sort a varchar array.  Something like "select sort('{y,z,x}'::varchar[])" => {'x','y','z'}.

Re: [HACKERS] Identifier case folding notes

Tom Lane wrote:
> What I think would perhaps be worth investigating is a compile-time
> (or at latest initdb-time) option that flips the case folding behavior
> to SQL-spec-compliant and also changes all the built-in catalog entries
> to upper case. We would then have a solution we could offer to people
> who really need to run apps that depend on SQL-spec case folding ...
> and if the upper case hurts their eyes, or breaks some other apps that
> they wish they could run in the same DB, well it's their problem.

+1 for a compile-time option for spec-compliant behavior. Even
where the spec is stupid (timestamp with time zone literals) it'd
be nice to have the option; both for feature completeness
checklists and for teachers who want to teach targeting the spec.

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

[GENERAL] plpgsql - or operator?

If I have an expression (a or b)a where a=TRUE and b=FALSE, why is b evaluated?  Any true operand before an or operator means the entire expression is true …

 

---------------------------- EXAMPLE

create or replace function pinsusers() returns trigger as

$$

declare msg varchar;

begin

            --THROWS EXCEPTION when ((TG_OP = 'INSERT') is TRUE “record old is not assigned yet”

            msg = ((TG_OP = 'INSERT') or (new.password<>old.password));

            raise exception '%',msg;

            return new;

end

$$ language plpgsql;

------------------------------------------------------

 

TY J