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

[pgsql-es-ayuda] recomendacion tipo columnas de fecha/hora

Hola,
estoy diseñando una base de datos nueva y para algunas de las tablas (la mayoría en realidad), al
guardar/actualizar los registros quiero que se quede guardada la fecha y la hora para futuras consultas, es
algo que luego ha de verse en todas las consultas, la duda que tengo es utilizo dos columnas fecha date, hora
time? o debería usar un timestamp? si la columna no la voy a ordenar normalmente por timestamp dos columnas me
permitirían no tener que usar un cast de la columna de timestamp a date y time en cada consulta a las tablas
(ya que al usuario no le puedo mostrar el timestamp directamente) pero por otro lado cuando quiera hacer un
order by tendría que convertir las dos columnas a un solo timestamp para el orden así que no tengo muy claro
que hacer con este tema, alguien podría arrojarme un poco de luz con experiencia en postgresql?

Un saludo,
Miguel Angel.
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [GENERAL] predefined animation and pre-recorded sounds in postgresql

On 08/07/2008 19:44, aravind chandu wrote:

> I just need to load pre-defined animations and pre-recorded
> sounds in postgresql.Can you please tell how to do this.I appreciate
> your help.

You need the bytea data type, which lets you store binary data. How you
get it in and out depends on what interface you're using.

There have been various threads - one earlier today, as it happens - on
the respective merits of storing binary files in the database or in the
filesystem; might be worth your while having a trawl through the archives.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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

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?

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

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

On Tue, 8 Jul 2008 17:20:13 -0400
Alvaro Herrera <alvherre@commandprompt.com> wrote:

> Mark Stosberg wrote:
> >
> > Hello,
> >
> > I have some tables that continually collect statistics, and then over time are
> > pruned as the stats are aggregated into more useful formats.
> >
> > For some of these tables, it it is fore-seeable that the associated sequences
> > would be incremented past the max value of the "int" type in the normal course
> > of things.
> >
> > I see two options to prepare for that:
>
> 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

Mark

--
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
mark@summersault.com Summersault, LLC
765-939-9301 ext 202 database driven websites
. . . . . http://www.summersault.com/ . . . . . . . .

--
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
mark@summersault.com Summersault, LLC
765-939-9301 ext 202 database driven websites
. . . . . http://www.summersault.com/ . . . . . . . .

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

Re: [pgus-board] Bylaws etall

On Tue, Jul 8, 2008 at 12:31 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Except for a small typo in:
>
> 8832620_1.DOC
>
> Where it says our terms expire in 2011 (we can just edit that) I am
> happy with them.
>
> Are we good to go?

Did I miss the bylaws being posted to the list?

-selena

--
Selena Deckelmann
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily

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

Re: [HACKERS] Identifier case folding notes

>>> 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. In general, users of those tools reject
PostgreSQL
> as "broken" for our nonstandard behavoir rather than trying to work
around
> it.

Do these tools expect an unquoted identifier to be treated according
to the standard? As I read it, an unquoted identifier should be
treated identically to the same identifier folded to uppercase and
wrapped in quotes, except that it will be guaranteed to be considered
an identifier, rather than possibly considered as a reserved word,
etc.

From our perspective, we're OK with the status quo since we always
quote all identifiers. I don't think any of the suggestions would
bite us (if implemented bug-free) because we also forbid names which
differ only in capitalization. We help out our programmers by letting
them ignore quoting (except identifiers which are reserved words) and
capitalization when they write queries in our tool; we correct the
capitalization and wrap the identifiers in quotes as we generate the
Java query classes. Doing something like that in psql autocompletion
and in other PostgreSQL tools would be a nice feature, if practicable.

-Kevin

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

On Tuesday 08 July 2008 23:04:51 Josh Berkus wrote:
> Tom,
>
> > IMHO, practically the only solid argument for changing from the way
> > we do things now is "to meet the letter of the spec". The various sorts
> > of gamesmanship you list would most definitely not meet the letter of
> > the spec; between that and the inevitability of breaking some apps,
> > I'm inclined to reject them all on sight.
>
> 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. In general, users of those tools reject PostgreSQL
> as "broken" for our nonstandard behavoir rather than trying to work around
> it.
>
> So it's not just a standards issue; this problem really *is* hurting us in
> adoption.
>
> --
> --Josh
>
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco

Right. From a user's perspective 4) sounds best. I often run into problems having keywords as column-names:

andreak=# create table test(user varchar);
ERROR: syntax error at or near "user"
LINE 1: create table test(user varchar);
^
andreak=# create table test("user" varchar);
CREATE TABLE
andreak=# insert into test("USER") values('testuser');
ERROR: column "USER" of relation "test" does not exist
LINE 1: insert into test("USER") values('testuser');
^
andreak=# insert into test(user) values('testuser');
ERROR: syntax error at or near "user"
LINE 1: insert into test(user) values('testuser');
^
andreak=# insert into test("user") values('testuser');
INSERT 0 1

As you know, the only way of referring to the "user"-column is to qoute it in lowercase, which many apps and tools don't do.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager

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

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

Mark Stosberg wrote:
>
> Hello,
>
> I have some tables that continually collect statistics, and then over time are
> pruned as the stats are aggregated into more useful formats.
>
> For some of these tables, it it is fore-seeable that the associated sequences
> would be incremented past the max value of the "int" type in the normal course
> of things.
>
> I see two options to prepare for that:

3. Deal with wraparound by ensuring that the applications behave sanely

--
Alvaro Herrera

http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

[atlpug] Need a little help to load a package

Please have someone call or e-mail.
Bob Thompson
Cell 404-542-6221

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

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

Hello,

I have some tables that continually collect statistics, and then over time are
pruned as the stats are aggregated into more useful formats.

For some of these tables, it it is fore-seeable that the associated sequences
would be incremented past the max value of the "int" type in the normal course
of things.

I see two options to prepare for that:

1. Convert the primary keys to "bigint", which should be good enough "forever".
I suppose there would some minor storage and performance penalty.

2. Reset the sequence at some point. There would no "collisions", because the
older rows would have long been pruned-out. I suppose there is an improbable
edge case in which we restore some old data from tape and then are confused
because some new data has the same IDs, but as I said, these tables are used as
temporary holding locations, not permanent storage.

Both options have some appeal for me. What have others done?

Mark

--
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
mark@summersault.com Summersault, LLC
765-939-9301 ext 202 database driven websites
. . . . . http://www.summersault.com/ . . . . . . . .

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

Tom,

> IMHO, practically the only solid argument for changing from the way
> we do things now is "to meet the letter of the spec". The various sorts
> of gamesmanship you list would most definitely not meet the letter of
> the spec; between that and the inevitability of breaking some apps,
> I'm inclined to reject them all on sight.

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. In general, users of those tools reject PostgreSQL
as "broken" for our nonstandard behavoir rather than trying to work around
it.

So it's not just a standards issue; this problem really *is* hurting us in
adoption.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

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

[pgus-general] Perl/PostgreSQL INSERT/UTF-8 problem

Hi all,

I've got a problem INSERTing a value into my DB via my perl program.
It's a French character 'é', and my DB is set to UTF8, but the error is:

INSERT INTO customer_data (cd_cust_id, cd_variable, cd_value,
added_user, added_date, modified_user, modified_date) VALUES (1,
'CustServiceTypeDisplay_F', 'Résidence', 1, now(), 1, now());

DBD::Pg::db do failed: ERROR: invalid byte sequence for encoding
"UTF8": 0xe97369
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".

When I manually run the INSERT, it works, so I know the problem is in
perl somewhere. Now then, I setup my script with this:

# Setup for UTF-8 mode.
binmode STDOUT, ":utf8:";
$ENV{'PERL_UNICODE'}=1;

When I create my PgSQL connection, I use:

$dbh=DBI->connect($db_connect_string, $$conf{db}{user}, $$conf{db}{pass},
{
RaiseError => 1,
AutoCommit => 1,
pg_enable_utf8 => 1
}
) or die ...;

I push a pile of queries into an array (referenced) and run them like
this:

# Sanity checks stripped for the email
$dbh->begin_work;
foreach my $query (@{$sql})
{
print "Query: [$query]\n";
$dbh->do($query) or $error.=$DBI::errstr.", ";
}
$dbh->commit;

Lastly, my database itself is set to UTF8:

SET client_encoding = 'UTF8';

I've tried knocking out the 'pg_enable_utf8 => 1' line in case I was
dealing with double-encoding, but that didn't help.

Any tips/ideas?

Thanks!

Madi

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

[COMMITTERS] pgbouncer - pgbouncer: remove extra argument for SBuf callback

Log Message:
-----------
remove extra argument for SBuf callback

conserves memory and conforms more with general coding style.

plus few cleanups

Modified Files:
--------------
pgbouncer/include:
client.h (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/include/client.h.diff?r1=1.2&r2=1.3)
sbuf.h (r1.10 -> r1.11)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/include/sbuf.h.diff?r1=1.10&r2=1.11)
server.h (r1.2 -> r1.3)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/include/server.h.diff?r1=1.2&r2=1.3)
pgbouncer/src:
client.c (r1.22 -> r1.23)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/client.c.diff?r1=1.22&r2=1.23)
objects.c (r1.46 -> r1.47)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/objects.c.diff?r1=1.46&r2=1.47)
sbuf.c (r1.31 -> r1.32)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/sbuf.c.diff?r1=1.31&r2=1.32)
server.c (r1.27 -> r1.28)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/server.c.diff?r1=1.27&r2=1.28)
takeover.c (r1.14 -> r1.15)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/takeover.c.diff?r1=1.14&r2=1.15)

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

Re: [pgus-board] Bylaws etall

On Tue, Jul 8, 2008 at 3:47 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> We accepted the changes. We did not approve the bylaws for filing, which
> I am now requesting that we do. So let me rephrase:
>
> Per the changes that DWT (Davis Wright Tremaine) has made our the
> board's request, I would like to now approve the bylaws for filing.

+1

---Michael Brewer
mbrewer@gmail.com

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

Re: [PERFORM] syslog performance when logging big statements

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.

--
Jeff Trout <jeff@jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/

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

[ANNOUNCE] Sign up for PDXPUG PgDay! July 20, 2008

If you're coming to OSCON, why not come a day early & join us for PDXPUG PgDay!

http://spreadsheets.google.com/viewform?key=paoTJ9uEi8vIqumUzwzrCAw&email=true

Please sign up for free admission to the after-party at the Gotham
Tavern, and to reserve a tshirt. We are requesting a $20 donation
that will be given to Software in the Public Interest for attendance.
You can pay with check or cash when you arrive at the conference.

Thank you!

gabrielle
--
PDXPUG: http://pugs.postgresql.org/pdx

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe@postgresql.org