[PATCHES] Patch for %Allow per-database permissions to be set via GRANT
This patch implements the TODO Item: %Allow per-database permissions to be set via GRANT Implementation details: 1. A privilege ACL_CONNECT has been added to the ACL bits 2. The ACL_CONNECT can be recognized by character c in pg_database/dataacl 3. The patch implements: GRANT CONNECTION ON DATABASE mydatabase TO myuser REVOKE CONNECTION ON DATABASE mydatabase FROM myuser 4. The initial condition ACL=NULL is treated as default ACL=ACL_CREATE_TEMP | ACL_CONNECT providing backward compatibility with the current pg_hba.conf Notes: As discussed : A database owner WITHOUT SUPERUSER privileges can lock himself out from connecting to his database. Try: #psql -U user1 -d user1 Revoke connection on database user1 from public; Revoke connection on database user1 from user1; In this case no warning will be shown to the user informing he/she is possibly locked out. This behavior is discussed in the hackers list. The solution for a possible lockout would be to connect as a superuser and GRANT CONNECTION ON DATABASE user1 TO anyuser or public The implementation is best used for systems not wishing to change pg_hba.conf frequently. In that case a simple host record can be added to pg_hba.conf, providing from witch network the server is allowed to be connected from and the database connection privilege can be granted or revoked from withing SQL. e.g. CREATE USER user1 LOGIN; CREATE USER user2 LOGIN; CREATE DATABASE user1 OWNER user1; REVOKE CONNECTION ON DATABASE user1 FROM PUBLIC; GRANT CONNECTION,CREATE ON DATABASE user1 TO user2; SELECT datname,datacl FROM pg_catalog.pg_database; The patch can be downloaded from: http://www.xs4all.nl/~gevik/patch/patch-0.7.diff Many thanks to Tom Lane and Alvaro Herrera for their insight and coaching. Regards, Gevik. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Patch for %Allow per-database permissions to be set
On Sun, 2006-04-30 at 15:29 -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Documentation added, patch attached and applied. Thanks. I just got around to reading this patch. Why is the syntax GRANT CONNECTION and not GRANT CONNECT? Privilege names are generally verbs not nouns. Unless someone can point to a good reason for CONNECTION, I'm going to change it. The main reason for this was because, in the beginning when I was gathering information for developing this patch, I read something about not introducing a new reserved word. So I used CONNECTION as the first relevant word I could find in the token list from gram.y. Later on we did not discussed anything about the *CONNECT* or *CONNECTION Regards, Gevik. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Patch for UUID datatype (beta)
Folks, The following patch implements the UUID datatype. I would like to send this beta patch to see if I still am on the right track. Please send your comments. Description of UUID: - The type is called uuid. - btree and hash indexes are supported. - uuid array is supported. - uuid text i/o is supported. - uuid binary i/o is supported. - uuid_to_text and text_to_uuid casting is supported. - uuid_to_varchar and varchar_to_uuid casting is supported. - the = = = operators are supported. Please note that some of these operators mathematically have no meaning and are only good for sorting. - new_guid() function is supported. This function is based on V4 random uuid value. It generated 16 random bytes with uuid 'variant' and 'version'. It is not guaranteed to produce unique values according to the docs but I have inserted 6 million records and it did not create any duplicates :) - the uuid datatype supports 3 input formats: 1. ---- 2. 3. {----} - the uuid datatype supports the defined output format by RFC: ---- Areas yet in development and testing: - uuid array indexing. - testing with joins (merge,hash,gin) - new_guid() fail proof testing - performance testing - testing with internal storage and compression. - regression test addition - proper documentation - overall sanity testing/checking Please note that I consider this a beta patch. You can download it from: http://www.truesoftware.net/pgsql/uuid/patch-0.1/ Regards, Gevik. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Patch for UUID datatype (beta)
Completely agreed. I can remove the function from the patch. The temptation was just too high not to include the new_guid() in the patch :) On Mon, 2006-09-18 at 10:33 -0400, Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Isn't guaranteed uniqueness the very attribute that's expected? AFAIK there's a commonly accepted algorithm providing this. Anyone who thinks UUIDs are guaranteed unique has been drinking too much of the kool-aid. They're at best probably unique. Some generator algorithms might make it more probable than others, but you simply cannot guarantee it for UUIDs generated on noncommunicating machines. One of the big reasons that I'm hesitant to put a UUID generation function into core is the knowledge that none of them are or can be perfect ... so people might need different ones depending on local conditions. I'm inclined to think that a reasonable setup would put the datatype (with input, output, comparison and indexing support) into core, but provide a generation function as a contrib module, making it easily replaceable. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Patch for UUID datatype (beta)
If you have trouble with duplicate OIDs Please use patch-0.2 for testing. I have changed the OIDs to 5000 range. You can download it from: http://www.truesoftware.net/pgsql/uuid/patch-0.2/ On Mon, 2006-09-18 at 01:00 +0200, Gevik Babakhani wrote: Folks, The following patch implements the UUID datatype. I would like to send this beta patch to see if I still am on the right track. Please send your comments. Description of UUID: - The type is called uuid. - btree and hash indexes are supported. - uuid array is supported. - uuid text i/o is supported. - uuid binary i/o is supported. - uuid_to_text and text_to_uuid casting is supported. - uuid_to_varchar and varchar_to_uuid casting is supported. - the = = = operators are supported. Please note that some of these operators mathematically have no meaning and are only good for sorting. - new_guid() function is supported. This function is based on V4 random uuid value. It generated 16 random bytes with uuid 'variant' and 'version'. It is not guaranteed to produce unique values according to the docs but I have inserted 6 million records and it did not create any duplicates :) - the uuid datatype supports 3 input formats: 1. ---- 2. 3. {----} - the uuid datatype supports the defined output format by RFC: ---- Areas yet in development and testing: - uuid array indexing. - testing with joins (merge,hash,gin) - new_guid() fail proof testing - performance testing - testing with internal storage and compression. - regression test addition - proper documentation - overall sanity testing/checking Please note that I consider this a beta patch. You can download it from: http://www.truesoftware.net/pgsql/uuid/patch-0.1/ Regards, Gevik. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] new patch for uuid datatype
Folks, I would like to submit an updated patch for the uuid datatype. I have removed the new_guid() function assuming we want a generator function in the contrib. I also have included a regression test and added the default copyright header for the new files. If this patch gets accepted then I can start working on the documentation. Regards, Gevik. *** ./backend/utils/adt/Makefile.orig 2006-09-19 12:05:41.0 +0200 --- ./backend/utils/adt/Makefile 2006-09-19 12:06:47.0 +0200 *** *** 15,21 endif endif ! OBJS = acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \ cash.o char.o date.o datetime.o datum.o domains.o \ float.o format_type.o \ geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \ --- 15,21 endif endif ! OBJS = uuid.o acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \ cash.o char.o date.o datetime.o datum.o domains.o \ float.o format_type.o \ geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \ *** ./include/catalog/pg_amop.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_amop.h 2006-09-19 12:06:47.0 +0200 *** *** 594,599 --- 594,601 DATA(insert ( 2232 0 1 f 2334 )); /* aclitem_ops */ DATA(insert ( 2235 0 1 f 974 )); + /* uuid */ + DATA(insert ( 2868 0 1 f 2866 )); /* * gist box_ops *** *** 886,889 --- 888,898 DATA(insert ( 2780 0 3 t 2752 )); DATA(insert ( 2780 0 4 t 1070 )); + /* btree uuid */ + DATA(insert ( 2873 0 1 f 2869 )); + DATA(insert ( 2873 0 2 f 2871 )); + DATA(insert ( 2873 0 3 f 2866 )); + DATA(insert ( 2873 0 4 f 2872)); + DATA(insert ( 2873 0 5 f 2870 )); + #endif /* PG_AMOP_H */ *** ./include/catalog/pg_amproc.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_amproc.h 2006-09-19 12:06:47.0 +0200 *** *** 125,130 --- 125,132 DATA(insert ( 2233 0 1 380 )); DATA(insert ( 2234 0 1 381 )); DATA(insert ( 27890 1 2794 )); + /*uuid*/ + DATA(insert ( 28730 1 2863 )); /* hash */ *** *** 161,166 --- 163,171 DATA(insert ( 2231 0 1 456 )); DATA(insert ( 2232 0 1 455 )); DATA(insert ( 2235 0 1 329 )); + /* uuid */ + DATA(insert ( 2868 0 1 2874 )); + /* gist */ *** ./include/catalog/pg_cast.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_cast.h 2006-09-19 12:06:47.0 +0200 *** *** 196,201 --- 196,206 /* Cross-category casts between int4 and char */ DATA(insert ( 18 23 77 e )); DATA(insert ( 23 18 78 e )); + /* uuid */ + DATA(insert ( 25 2854 2876 a )); + DATA(insert ( 2854 25 2877 a )); + DATA(insert ( 1043 2854 2878 a )); + DATA(insert ( 2854 1043 2879 a )); /* * Datetime category *** ./include/catalog/pg_opclass.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_opclass.h 2006-09-19 12:06:47.0 +0200 *** *** 208,211 --- 208,216 DATA(insert OID = 2779 ( 2742 _reltime_ops PGNSP PGUID 1024 t 703 )); DATA(insert OID = 2780 ( 2742 _tinterval_ops PGNSP PGUID 1025 t 704 )); + /* uuid */ + DATA(insert OID = 2873 ( 403 uuid_ops PGNSP PGUID 2854 t 0 )); + DATA(insert OID = 2868 ( 405 uuid_ops PGNSP PGUID 2854 t 0 )); + + #endif /* PG_OPCLASS_H */ *** ./include/catalog/pg_operator.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_operator.h 2006-09-19 12:06:47.0 +0200 *** *** 883,888 --- 883,898 DATA(insert OID = 2751 ( @ PGNSP PGUID b f 2277 2277 16 2752 0 0 0 0 0 arraycontains contsel contjoinsel )); DATA(insert OID = 2752 ( ~ PGNSP PGUID b f 2277 2277 16 2751 0 0 0 0 0 arraycontained contsel contjoinsel )); + /* uuid operators */ + + DATA(insert OID = 2866 ( = PGNSP PGUID b t 2854 2854 16 2866 2867 2869 2869 2869 2870 uuid_eq eqsel eqjoinsel )); + DATA(insert OID = 2867 ( PGNSP PGUID b f 2854 2854 16 2867 2866 0 000uuid_ne neqsel neqjoinsel )); + DATA(insert OID = 2869 ( PGNSP PGUID b f 2854 2854 16 2870 2872 0 0 0 0uuid_lt scalarltsel scalarltjoinsel )); + DATA(insert OID = 2870 ( PGNSP PGUID b f 2854 2854 16 2869 2871 0 0 00uuid_gt scalargtsel scalargtjoinsel )); + DATA(insert OID = 2871 ( = PGNSP PGUID b f 2854 2854 16 2872 2870 0 0 0 0uuid_le scalarltsel scalarltjoinsel )); + DATA(insert OID = 2872 ( = PGNSP PGUID b f 2854 2854 16 2871 2869 0 0 0 0uuid_ge scalargtsel scalargtjoinsel )); + + /* * function prototypes *** ./include/catalog/pg_proc.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_proc.h 2006-09-19 12:37:38.0 +0200 *** *** 3940,3945 --- 3940,3980 DATA(insert OID = 2749 ( arraycontained PGNSP PGUID 12 f f t f i 2 16 2277 2277 _null_ _null_ _null_ arraycontained - _null_ )); DESCR(anyarray contained); + /* uuid */ +
[pgsql-patches] guid/uuid datatype
Hi, While ago (sep-2006) I sent a patch for the UUID datatype, Did anyone have time to review it yet? Here it is again :) Regards, Gevik *** ./backend/utils/adt/Makefile.orig 2006-09-19 12:05:41.0 +0200 --- ./backend/utils/adt/Makefile 2006-09-19 12:06:47.0 +0200 *** *** 15,21 endif endif ! OBJS = acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \ cash.o char.o date.o datetime.o datum.o domains.o \ float.o format_type.o \ geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \ --- 15,21 endif endif ! OBJS = uuid.o acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \ cash.o char.o date.o datetime.o datum.o domains.o \ float.o format_type.o \ geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \ *** ./include/catalog/pg_amop.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_amop.h 2006-09-19 12:06:47.0 +0200 *** *** 594,599 --- 594,601 DATA(insert ( 2232 0 1 f 2334 )); /* aclitem_ops */ DATA(insert ( 2235 0 1 f 974 )); + /* uuid */ + DATA(insert ( 2868 0 1 f 2866 )); /* * gist box_ops *** *** 886,889 --- 888,898 DATA(insert ( 2780 0 3 t 2752 )); DATA(insert ( 2780 0 4 t 1070 )); + /* btree uuid */ + DATA(insert ( 2873 0 1 f 2869 )); + DATA(insert ( 2873 0 2 f 2871 )); + DATA(insert ( 2873 0 3 f 2866 )); + DATA(insert ( 2873 0 4 f 2872)); + DATA(insert ( 2873 0 5 f 2870 )); + #endif /* PG_AMOP_H */ *** ./include/catalog/pg_amproc.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_amproc.h 2006-09-19 12:06:47.0 +0200 *** *** 125,130 --- 125,132 DATA(insert ( 2233 0 1 380 )); DATA(insert ( 2234 0 1 381 )); DATA(insert ( 27890 1 2794 )); + /*uuid*/ + DATA(insert ( 28730 1 2863 )); /* hash */ *** *** 161,166 --- 163,171 DATA(insert ( 2231 0 1 456 )); DATA(insert ( 2232 0 1 455 )); DATA(insert ( 2235 0 1 329 )); + /* uuid */ + DATA(insert ( 2868 0 1 2874 )); + /* gist */ *** ./include/catalog/pg_cast.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_cast.h 2006-09-19 12:06:47.0 +0200 *** *** 196,201 --- 196,206 /* Cross-category casts between int4 and char */ DATA(insert ( 18 23 77 e )); DATA(insert ( 23 18 78 e )); + /* uuid */ + DATA(insert ( 25 2854 2876 a )); + DATA(insert ( 2854 25 2877 a )); + DATA(insert ( 1043 2854 2878 a )); + DATA(insert ( 2854 1043 2879 a )); /* * Datetime category *** ./include/catalog/pg_opclass.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_opclass.h 2006-09-19 12:06:47.0 +0200 *** *** 208,211 --- 208,216 DATA(insert OID = 2779 ( 2742 _reltime_ops PGNSP PGUID 1024 t 703 )); DATA(insert OID = 2780 ( 2742 _tinterval_ops PGNSP PGUID 1025 t 704 )); + /* uuid */ + DATA(insert OID = 2873 ( 403 uuid_ops PGNSP PGUID 2854 t 0 )); + DATA(insert OID = 2868 ( 405 uuid_ops PGNSP PGUID 2854 t 0 )); + + #endif /* PG_OPCLASS_H */ *** ./include/catalog/pg_operator.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_operator.h 2006-09-19 12:06:47.0 +0200 *** *** 883,888 --- 883,898 DATA(insert OID = 2751 ( @ PGNSP PGUID b f 2277 2277 16 2752 0 0 0 0 0 arraycontains contsel contjoinsel )); DATA(insert OID = 2752 ( ~ PGNSP PGUID b f 2277 2277 16 2751 0 0 0 0 0 arraycontained contsel contjoinsel )); + /* uuid operators */ + + DATA(insert OID = 2866 ( = PGNSP PGUID b t 2854 2854 16 2866 2867 2869 2869 2869 2870 uuid_eq eqsel eqjoinsel )); + DATA(insert OID = 2867 ( PGNSP PGUID b f 2854 2854 16 2867 2866 0 000uuid_ne neqsel neqjoinsel )); + DATA(insert OID = 2869 ( PGNSP PGUID b f 2854 2854 16 2870 2872 0 0 0 0uuid_lt scalarltsel scalarltjoinsel )); + DATA(insert OID = 2870 ( PGNSP PGUID b f 2854 2854 16 2869 2871 0 0 00uuid_gt scalargtsel scalargtjoinsel )); + DATA(insert OID = 2871 ( = PGNSP PGUID b f 2854 2854 16 2872 2870 0 0 0 0uuid_le scalarltsel scalarltjoinsel )); + DATA(insert OID = 2872 ( = PGNSP PGUID b f 2854 2854 16 2871 2869 0 0 0 0uuid_ge scalargtsel scalargtjoinsel )); + + /* * function prototypes *** ./include/catalog/pg_proc.h.orig 2006-09-19 12:05:39.0 +0200 --- ./include/catalog/pg_proc.h 2006-09-19 12:37:38.0 +0200 *** *** 3940,3945 --- 3940,3980 DATA(insert OID = 2749 ( arraycontained PGNSP PGUID 12 f f t f i 2 16 2277 2277 _null_ _null_ _null_ arraycontained - _null_ )); DESCR(anyarray contained); + /* uuid */ + DATA(insert OID = 2855 ( uuid_inPGNSP PGUID 12 f f t f i 1 2854 2275 _null_ _null_ _null_ uuid_in - _null_ )); + DESCR(I/O); + DATA(insert OID = 2856 ( uuid_out PGNSP PGUID 12 f f t f i 1 2275 2854
Re: [pgsql-patches] guid/uuid datatype
I confess I haven't followed the discussion around this patch, but is there a compelling reason to include this in the backend proper, rather than contrib/? AFAIK, It is/was part of the TODO for the core. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-patches] guid/uuid datatype
I'd be willing to accept a core uuid type sans generator function, but is that really all that useful? This is also a point I remember from the last discussions. To not to include the generator in the core. The generation of the uuid is then going to be on the client side. The uuid type is very useful, especially when migrating from other systems to pg (ms-pg or syb-pg). Regards, Gevik. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-patches] guid/uuid datatype
But does it really help if you don't have the generator? I don't use UUIDs much myself, but I think in all cases I've seen that use the uuid type in SQL Server they're also using the generator function. Those that just store UUIDs in the database often just uses varchar - in order to be more portable, I guess. There could be many algorithms to generate a guid. I guess we will get into a big debate on that, which is not much useful i guess (seeing the posts last year). In most cases I have seen the guid is generated by the client. In case of M$ Sql is also can be generated on the server but, in our case we generate the guids ourselves because with our algorithm we can trace the guid back to where it exactly was originated. (app requirement) One thing is for sure, having varchar to store guids ( varchar(32) ) is not that efficient. Regards, Gevik. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-patches] guid/uuid datatype
I already suggested a few things you could improve in the patch. If this discussion concludes that the patch should be included in the core backend and you submit a revised patch, I'd be happy to review and apply it. So.. do we agree for uuid to be included in the core? If so.. I will change the assigned OIDs in the patch to match the current source-tree and update the code with the suggestions provided by Neil. Are we okay on this? Regards, Gevik. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-patches] guid/uuid datatype
I thought the consensus was to provide the only atatype initially and look into providing the generator functions later or via an external project (pgfoundry or contrib/). This was my understanding too... to include the uuid in the core and let the actual value be generated elsewhere...(client or separate project)... I do not think having a uuid datatype as contrib module separately will do us much good. All professional dbs support this as built in. So why shouldn't we... Regards, Gevik. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[pgsql-patches] uuid patch 2.0 (8.3devel)
Folks, Hereby the version 2.0 of the uuid datatype patch with modifications commented by Neil. - the uuid.h has been cleaned. the declarations have been moved to uuid.c - the text_uuid() and varchar_uuid() have been refactored. - all uuid explicit functions are moved to uuid.c and made local. * this patch has been tested on 8.3devel, the snapshot of 25-Jan-07 * this patch uses 28 new oids. I have assigned the oids from 2950. If you need to change the oids, do not do this manually. I have a script that does that. Just provide me 28 unused oids and I will generate a new patch. Please provide comments. Regards, Gevik ### # Patch created by PostgreSQL Patch Generator 1.0 # Written by Gevik Babakhani 2007 (BSD) # # Apply this patch: # cd ./mydir.../pgsql/ # patch -p0 this.patch.file.diff # # Date created: Thu, 25 Jan 2007 22:21:21 +0100 # # New files: #./src/backend/utils/adt/uuid.c #./src/include/utils/uuid.h #./src/test/regress/expected/uuid.out #./src/test/regress/sql/uuid.sql # Modified files: #./src/backend/utils/adt/Makefile #./src/include/catalog/pg_amop.h #./src/include/catalog/pg_amproc.h #./src/include/catalog/pg_cast.h #./src/include/catalog/pg_opclass.h #./src/include/catalog/pg_operator.h #./src/include/catalog/pg_opfamily.h #./src/include/catalog/pg_proc.h #./src/include/catalog/pg_type.h #./src/include/utils/builtins.h #./src/test/regress/parallel_schedule #./src/test/regress/serial_schedule ## *** ./src/backend/utils/adt/Makefile.orig 2007-01-24 16:35:05.0 +0100 --- ./src/backend/utils/adt/Makefile 2007-01-24 16:37:12.0 +0100 *** *** 15,21 endif endif ! OBJS = acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \ cash.o char.o date.o datetime.o datum.o domains.o \ float.o format_type.o \ geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \ --- 15,21 endif endif ! OBJS = uuid.o acl.o arrayfuncs.o array_userfuncs.o arrayutils.o bool.o \ cash.o char.o date.o datetime.o datum.o domains.o \ float.o format_type.o \ geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \ *** ./src/backend/utils/adt/uuid.c.orig 1970-01-01 01:00:00.0 +0100 --- ./src/backend/utils/adt/uuid.c 2007-01-24 17:54:54.0 +0100 *** *** 0 --- 1,271 + /*- + * + * uuid.h + * Header file for the SQL datatypes UUID. + * + * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group + * + *- + */ + + #include postgres.h + #include access/hash.h + #include libpq/pqformat.h + #include utils/builtins.h + #include utils/uuid.h + + /* declarations */ + + static void create_uuiddata_from_string(const char* source,unsigned char *data); + static bool parse_uuid_string(const char* fmt,const char* source,unsigned char* data); + static void create_string_form_uuid_data(const char* fmt,const char *data,char *uuid_str); + static int32 uuid_internal_cmp(uuid_t *arg1,uuid_t *arg2); + static Datum cast_text_to_uuid(text *input); + + /* + * function handles input for the uuid datatype + */ + Datum uuid_in(PG_FUNCTION_ARGS) + { + uuid_t *uuid; + uint8 data[UUID_LEN]; + + char *uuid_str = PG_GETARG_CSTRING(0); + + create_uuiddata_from_string(uuid_str,data); + + uuid = (uuid_t *) palloc(sizeof(uuid_t)); + memcpy(uuid-data,data,UUID_LEN); + + PG_RETURN_UUID_P(uuid); + } + + /* + * function handles output for the uuid datatype + */ + Datum uuid_out(PG_FUNCTION_ARGS) + { + uuid_t *uuid = (uuid_t *) PG_GETARG_POINTER(0); + + char *uuid_str; + + uuid_str = (char *)palloc(PRINT_SIZE); + create_string_form_uuid_data(UUID_FMT1,uuid-data,uuid_str); + + PG_RETURN_CSTRING(uuid_str); + } + + /* uuid binary receive handler */ + Datum uuid_recv(PG_FUNCTION_ARGS) + { + uuid_t *uuid; + StringInfo buffer = (StringInfo) PG_GETARG_POINTER(0); + + uuid = (uuid_t *)palloc(UUID_LEN); + memcpy(uuid-data,pq_getmsgbytes(buffer,UUID_LEN),UUID_LEN); + PG_RETURN_POINTER(uuid); + } + + /* uuid binary send handler */ + Datum uuid_send(PG_FUNCTION_ARGS) + { + uuid_t *uuid = PG_GETARG_UUID_P(0); + StringInfoData buffer; + + pq_begintypsend(buffer); + pq_sendbytes(buffer,uuid-data,UUID_LEN); + PG_RETURN_BYTEA_P(pq_endtypsend(buffer)); + } + + /* handler for operator */ + Datum uuid_lt(PG_FUNCTION_ARGS) + { + bool result; + uuid_t *arg1 = PG_GETARG_UUID_P(0); + uuid_t *arg2 = PG_GETARG_UUID_P(1); + + result = uuid_internal_cmp(arg1,arg2) 0
[pgsql-patches] uuid patch 3.0 (8.3devel)
Folks, As commented by Peter, I have done some re-styling. Some additional tests and format checking have been added to this patch. Put your file at the end of the OBJS variable (or in some sort of sensible order). Done. Put your file at the end of the tests (or in some sort of sensible order). Done. Refrain from self-evident comments, such as + /* + * function handles input for the uuid datatype + */ + Datum uuid_in(PG_FUNCTION_ARGS) You can probably delete all comments in your patch by that criterion. Some changed, but not all has been removed for readability reasons. This sort of super-verbose coding might be alright, but it gets tiring when done systematically for no reason: + result = DirectFunctionCall1(textin, uuid_str); + return result; Some changed, but not all. The uuid.c file claims it is uuid.h. Changed. Move the stuff from builtins.h to uuid.h. Not changed. please see: http://archives.postgresql.org/pgsql-patches/2007-01/msg00362.php Move the stuff from uuid.h that is not needed anywhere else to uuid.c. Done. No // comments. Done. Don't number the tests. We might want to insert something later and that would mess everything up. Done. Capitalize the SQL test scripts as in other files. Done. Remove gratuitous whitespace changes (there are many). Done. AFAICS Also remove the whitespace at the end of lines. Done. AFAICS Make some reasonable effort to align the catalog entries for readability. Done. Any more comments? Regards, Gevik. ### # Patch created by PostgreSQL Patch Generator 1.0 # Written by Gevik Babakhani 2007 (BSD) # # Apply this patch: # cd ./mydir.../pgsql/ # patch -p0 this.patch.file.diff # # Date created: Fri, 26 Jan 2007 12:55:29 +0100 # # New files: #./src/backend/utils/adt/uuid.c #./src/include/utils/uuid.h #./src/test/regress/expected/uuid.out #./src/test/regress/sql/uuid.sql # Modified files: #./src/backend/utils/adt/Makefile #./src/include/catalog/pg_amop.h #./src/include/catalog/pg_amproc.h #./src/include/catalog/pg_cast.h #./src/include/catalog/pg_opclass.h #./src/include/catalog/pg_operator.h #./src/include/catalog/pg_opfamily.h #./src/include/catalog/pg_proc.h #./src/include/catalog/pg_type.h #./src/include/utils/builtins.h #./src/test/regress/parallel_schedule #./src/test/regress/serial_schedule ## *** ./src/backend/utils/adt/Makefile.orig 2007-01-24 16:35:05.0 +0100 --- ./src/backend/utils/adt/Makefile 2007-01-26 08:12:27.0 +0100 *** *** 25,31 tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \ network.o mac.o inet_net_ntop.o inet_net_pton.o \ ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \ ! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o xml.o like.o: like.c like_match.c --- 25,32 tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \ network.o mac.o inet_net_ntop.o inet_net_pton.o \ ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \ ! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o xml.o \ ! uuid.o like.o: like.c like_match.c *** ./src/backend/utils/adt/uuid.c.orig 1970-01-01 01:00:00.0 +0100 --- ./src/backend/utils/adt/uuid.c 2007-01-26 11:25:12.0 +0100 *** *** 0 --- 1,298 + /*- + * + * uuid.c + * Functions for the built-in type uuid. + * + * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + *- + */ + + #include postgres.h + #include access/hash.h + #include libpq/pqformat.h + #include utils/builtins.h + #include utils/uuid.h + + /* Accepted GUID formats */ + + /* this one is the default output format */ + #define UUID_FMT1 %02hhx%02hhx%02hhx%02hhx-%02hhx%02hhx-%02hhx%02hhx-%02hhx%02hhx-%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx + #define UUID_FMT2 {%02hhx%02hhx%02hhx%02hhx-%02hhx%02hhx-%02hhx%02hhx-%02hhx%02hhx-%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx} + #define UUID_FMT3 %02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx%02hhx + + /* these are uuid styles coresponding to above to check the actual uuid string + for format validity */ + #define UUID_CHK_FMT1 ---- + #define UUID_CHK_FMT2 {----} + #define UUID_CHK_FMT3 + + /* output length */ + #define
Re: [pgsql-patches] uuid patch 3.0 (8.3devel)
uuid.c header is missing $PostgreSQL$ line, so is uuid.h, copyright notice in the latter seems wrong too. I left this part because it is not clear to me what to put there. Is the following OK? * IDENTIFICATION * $PostgreSQL$ * *-- */ Generally I like to put local var = PG_GETARG() declarations first in a function, not randomly mixed in with other declarations of local variables. Think of them as part of the function header. (Someday we might try to process them with some automatic script, too... so the less random stylistic variation, the better.) Moved all possible var = PG_GETARG() to the first line in the functions Please drop the conversions to/from varchar; text is sufficient. Do you also mean to also remove the casts to/from varchar? (also the catalog entries?) Pay some attention to a logical ordering of the functions in uuid.c, eg why is uuid_internal_cmp intermixed with unrelated functions rather than with the ones that call it? I have relocated all the *helper* functions to not to intermix with *catalog* functions uuid.c contains some functions that are declared static and then defined without, please clean this up, and make sure it's not exporting anything it doesn't have to. Done. Don't put the uuid test at randomly inconsistent places in parallel_schedule and serial_schedule The regression test is probably expending a lot more cycles than are justified, eg what exactly is the point of the last part that inserts 32K random-data records? If it ever did show a failure we'd be unable to reproduce it, so please at least lose the use of now() and random(). I have removed this test because the validity test above already does the job. for(a = 0; a != fmtlen; a++) OK, this is nitpicky, but there is not a single other C program in the world that wouldn't have written that with in place of !=. This coding is unusual and fragile. Damn my old programming lessons :) (I probably have written crappy for-loops in the past decade) Still haven't fixed all the // comments. Done. The patch still has some random whitespace changes... particularly objectionable are the insertions of blank lines far away from any intended change, eg at the head of various catalog header files. This should never have happened, But it is fixed now Don't forget catversion bump, also double-check for duplicate_oids. catversion bump? please explain, Do you mean to change the catalog version? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Noted. Thank you. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: Friday, November 02, 2007 4:19 PM To: Gevik Babakhani Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name. Gevik Babakhani wrote: Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. I am sending this patch to check if I am on the right track. So please take a look at this if possible. Step 1: don't use c++ style comments like this: + //TODO: Check here C89 is basically our standard. gcc -std=c89 will check that it complies. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. I am sending this patch to check if I am on the right track. So please take a look at this if possible. What does this patch do? As discussed in thread: http://archives.postgresql.org/pgsql-hackers/2007-10/msg01490.php, this patch adds an additional parameter (char **argnames) to pg_parse_and_rewrite and pg_analyze_and_rewrite and ParseState. When transformColumnRef is about to report an error for a non existing column,a final match is performed to see if the non existing column is a parameter name. (argnames) If true, then a new node is created by transformParamRef NOTE: - This patch is created using MSVC++ ! - Nothing is done yet for polymorphic arguments. My test where: create table tbl1(id serial,field1 integer,field2 varchar); insert into tbl1 (field1,field2) values(11,''); insert into tbl1 (field1,field2) values(22,''); create or replace function func1(par1 integer,par2 integer,par3 varchar) returns setof record as $$ select par1::text, par2, par1+par2, par2+par1, par1+field1, (field1+par2)::varchar, par3, field2 || ' ' || par3 from tbl1; $$ language sql; select func1(2,4,''); select * from func1(5,16,'') as (a text ,b int ,c int, e int, f int,g varchar,h varchar,i text); results: (2,4,6,6,13,15,, ) (2,4,6,6,24,26,, ) And 5;16;21;21;16;27;; 5;16;21;21;27;38;; Regards, Gevik func-name-args-v0.1.patch Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hi, what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. Please explain. Perhaps I am wrong, but plpgsql handles arsgument names before it passes the query to be executed. Please see: plpgsql/pl_comp.c/do_compile(...)/line: 393 Regards, Gevik. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] V0.2 patch for TODO Item: SQL-language reference parameters by name.
Hello All, This patch implements a (generic) callback functionality in the parser. The mechanism can be used to send callback messages from within the parser to external functions. I would like to know your opinion about the following: In previous discussion Tom referred to: One point here is that it would be good to be able to qualify the argument names with the function name, for example create function myfunc(x int) ... select ... from t where t.x = myfunc.x The above is possible but I think qualifying the argument names with the function name can be cumbersome when one has to provide the function name multiple times. For example: (where clause) create or replace function sp_item_get_by_type_or_category(p_type integer,p_category integer) returns setof item_view as $$ select . from item_view i inner join tblcategory c on i.catid = c.catid inner join tbltype t on i.typeid = t.typeid where c.catid = sp_item_get_by_type_or_category.p_category or t.typeid = sp_item_get_by_type_or_categor.p_type; $$ language sql; Perhaps we could use the word this instead of the entire function name For example: where c.catid = this.p_category or t.typeid = this.p_type; Any thoughts? Regards, Gevik PLEASE NOTE: - This patch in created with MSVC++ - Resolving the argnames is not yet implemented correctly due above. - Two files have been added parse_callback.h and .c How does it work: To setup callback; ParserCallbackContext sqlcallbackcontext; /* attaching parser callback handler*/ sqlcallbackcontext.context = T_ParsingFunctionBody; sqlcallbackcontext.ctxarg = tuple; sqlcallbackcontext.callback = sql_parser_callback_handler; sqlcallbackcontext.previous = parser_callback_context_stack; parser_callback_context_stack = sqlcallbackcontext; parser_callback_context_stack = sqlcallbackcontext.previous; To call the callback handler from within the parser: ParserCallbackContextArgs args; args.pstate = pstate; args.input = (Node *)cref; args.action = A_ResolveAmbigColumnRef; parser_do_callback(args); To handle the callback: if(context == T_ParsingFunctionBody) { switch(action) { case A_ResolveAmbigColumnRef: } } *** I:\pgdev\pgsql\src\backend\catalog\pg_proc.c.orig 2007-09-03 02:39:14.0 +0200 --- I:\pgdev\pgsql\src\backend\catalog\pg_proc.c2007-11-03 12:29:13.832352000 +0100 *** *** 33,45 #include utils/builtins.h #include utils/lsyscache.h #include utils/syscache.h ! Datum fmgr_internal_validator(PG_FUNCTION_ARGS); Datum fmgr_c_validator(PG_FUNCTION_ARGS); Datum fmgr_sql_validator(PG_FUNCTION_ARGS); static void sql_function_parse_error_callback(void *arg); static int match_prosrc_to_query(const char *prosrc, const char *queryText, int cursorpos); static bool match_prosrc_to_literal(const char *prosrc, const char *literal, --- 33,47 #include utils/builtins.h #include utils/lsyscache.h #include utils/syscache.h ! #include parser/parse_callback.h ! #include parser/parse_expr.h Datum fmgr_internal_validator(PG_FUNCTION_ARGS); Datum fmgr_c_validator(PG_FUNCTION_ARGS); Datum fmgr_sql_validator(PG_FUNCTION_ARGS); static void sql_function_parse_error_callback(void *arg); + static void sql_parser_callback_handler(ParserCallbackContextArgs *args); static int match_prosrc_to_query(const char *prosrc, const char *queryText, int cursorpos); static bool match_prosrc_to_literal(const char *prosrc, const char *literal, *** *** 531,536 --- 533,539 Datum tmp; char *prosrc; ErrorContextCallback sqlerrcontext; + ParserCallbackContext sqlcallbackcontext; boolhaspolyarg; int i; *** *** 586,591 --- 589,601 sqlerrcontext.previous = error_context_stack; error_context_stack = sqlerrcontext; + /* attaching parser callback handler*/ + sqlcallbackcontext.context = T_ParsingFunctionBody; + sqlcallbackcontext.ctxarg = tuple; + sqlcallbackcontext.callback = sql_parser_callback_handler; + sqlcallbackcontext.previous = parser_callback_context_stack; + parser_callback_context_stack = sqlcallbackcontext; + /* * We can't do full prechecking of the function definition if there * are any polymorphic input types, because actual datatypes of *** ***
Re: [PATCHES] V0.2 patch for TODO Item: SQL-language referenceparameters by name.
I think a prefix of ':' would be good, as it's already a standard, kinda. Anybody who names a database object :foo deserves whatever happens to them :P I for one like something less cryptic than ':' besids going with ':' means extra hack in gram.y (Ones we get to implement packages I prefer to have this.arg and global.arg than ':' and '::' but I guess that's another discussion.) Regards, Gevik. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] V0.2 patch for TODO Item: SQL-language referenceparameters by name.
So where do we go from here? a. function name.arg name b. this.arg name c. ':'argname d. just argname option a,b and d are easy to implement. option d would be least clear and readable considering sql functions can be long and have multiple arguments. option c is more difficult because gram.y has to be modified to understand ':'identifier as parameter but not a target_list item. option a and b would make the source more readable but extra documentation has to be provided to describe how to refer arguments by name. Regards, Gevik Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gregory Stark Sent: Saturday, November 03, 2007 6:22 PM To: David Fetter Cc: Tom Lane; Gevik Babakhani; pgsql-patches@postgresql.org Subject: Re: [PATCHES] V0.2 patch for TODO Item: SQL-language referenceparameters by name. David Fetter [EMAIL PROTECTED] writes: What I mean by kinda is that it's a standard way of handling parameters in Oracle and in DBI. That's a good reason *not* to use them for other purposes. Users trying to create procedures through DBI or other interfaces like it will run into problems when the driver misinterprets the parameters. I think it would be a very bad idea to require that people use the function name in parameters, I think were talking about only allowing it to disambiguate if the name is shadowed by a variable in an inner scope. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] V0.2 patch for TODO Item: SQL-language referenceparameters by name.
Gevik Babakhani [EMAIL PROTECTED] writes: So where do we go from here? a. function name.arg name b. this.arg name c. ':'argname d. just argname We must support both a and d. Then a and d it is :) Regards, Gevik Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] proposed patch for function parameters name refs
This patch implements the function parameter referencing by name as discussed before. Please find the version 1.0 of this patch at http://www.postgresql.nl/gevik/ The zip archive contains a combined and separate patches to make reviewing easier. How this patch is implemented: As discussed before, a generic parser callback functionality has been implemented to create hooks from within the parser. The mechanism of handling refnames acts only when the parser is in context of parsing a function AND all of the previous attempts to recognize an unknown refname (also after implicit RTE) has failed. This way the match for a possible function parameter name is executed as a last measure. notes: - a new error code has been added: ERRCODE_UNDEFINED_FUNCTION_PARAMETER_NAME - when a given refname is unrecognized after all matches, one of the two following error messages is shown: a) there is no parameter %s defined for function %s b) there is no parameter %s.%s defined for function %s or this \n is an invalid reference to FROM-clause entry for table \%s\ - 2 new files have been added parse_callback.h and .c - the patch also contains test for regression - the main logic is implemented in transformColumnRef/ case 1 and case 2 - patch is created using VC++ 2005, tested on Win32 and RH4 Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] V1.1 patch for TODO Item: SQL-language reference parameters by name.
This is a minor update to 1.0 (corrected some typo here and there). Please see: http://archives.postgresql.org/pgsql-patches/2007-11/msg00253.php A zip file containing separated patches for each file is available on http://www.postgresql.nl/gevik/ Regards, Gevik Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl patch-1.1-combined.patch Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
Per today's -hackers discussion, add a GUC variable to allow clients to disable the new synchronized-scanning behavior, and make pg_dump disable sync scans so that it will reliably preserve row ordering. This is a pretty trivial patch, but seeing how late we are in the 8.3 release cycle, I thought I'd better post it for comment anyway. +1 I liked the synchronized_sequential_scans idea myself. But otherwise, sure. this will save some hackwork I have to do for a import/export project I am doing. +1, please Regards, Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Fix for 8.3 MSVC locale (Was [HACKERS] NLS on MSVC strikes back!)
Hereby a patch that fixes NLS support on PG 8.3 compiled with MSVC. There problem: NLS support does not work on PG 8.3 compiled with MSVC. I encountered this bug when I was trying to show localized months and days using TO_CHAR. The main reason for this problem is because Gettext on Windows does not respond to LC_MESSAGES environment variable. Changing this variable should trigger Gettext to load a different messages catalog which unfortunately does not work on Windows. Gettext uses the locale of the current thread in execution to determine which message catalog should be loaded. This is all discussed in: http://archives.postgresql.org/pgsql-hackers/2008-02/msg00485.php How it is fixed: Changing the LC_MESSAGES is done in pg_locale.c::pg_perm_setlocale(int category, const char *locale). In order to force Gettext to load a messages catalog we have to call WIN32API::SetThreadLocale(unsigned long locale_id) (You probably see it coming) Our input parameter for locale in pg_perm_setlocale is a string and there is no unified way in Windows to translate a locale string to a locale_id for SetThreadLocale to use. Therefore we will use a static table of values to anticipate the locale name: pg_win32_locale_database::{0x041b,{sk,sk-SK,sk_SK,Slovak_Slovakia}} Given any of sk,sk-SK,sk_SK the 0x041b is returned for SetThreadLocale as input parameter. (We are not quite done yet) Gettext, internally uses a hack to force itself to reload. This hidden feature is also explained in GetText docs. By incrementing a Gettext internal variable (_nl_msg_cat_cntr) we force Gettext to reload on the next LC_MESSAGES-MSGID query. Tests: - Tested on Win XP MSVC (VC++ 8.0) - Just a routine make check test on my Linux box - MINGW is not tested. (I do not have the installation) Fix notes: - Gettext behaves oddly when the env. variable LANGUAGE is set before starting PG - The locale names in the static table are case sensitive. 'nl_NL' != 'NL_NL' - At this moment I only have included locale names we actually support in PG installation. - Where are the JP locale .po and .mo files? These are not in sources! - Even though there are 20+ locales in PG installation, does not mean those are complete. Try TMMonth,TMDay on nl_NL. You will get English names :) (My fault, hee hee, I haven't completed nl.po translations yet.) TODO: - Provide/complete the day and month names for all supported locales. - Create docs for supported locale names on Windows. (Values of the static table) Regards, Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl 1.pg_locale.c.patch Description: Binary data Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for details. Gevik=# set lc_messages to 'sv_SE'; SET Gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates --- Torsdag Februari 2008 Fredag Februari 2008 L÷rdag Februari 2008 S÷ndag Februari 2008 MÏndag Februari 2008 Tisdag Februari 2008 Onsdag Februari 2008 (7 rows) Gevik=# set lc_messages to 'de_DE'; SET Gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Donnerstag Februar 2008 Freitag Februar 2008 Samstag Februar 2008 Sonntag Februar 2008 Montag Februar 2008 Dienstag Februar 2008 Mittwoch Februar 2008 (7 rows) Gevik=# set lc_messages to 'English_United_States'; SET Gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Thursday February 2008 Friday February 2008 Saturday February 2008 Sunday February 2008 Monday February 2008 Tuesday February 2008 Wednesday February 2008 (7 rows) Gevik=# set lc_messages to 'tr-TR'; SET Gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates -- Persembe Subat 2008 Cuma Subat 2008 Cumartesi Subat 2008 Pazar Subat 2008 Pazartesi Subat 2008 Sali Subat 2008 âarsamba Subat 2008 (7 rows) Gevik=# set lc_messages to 'fr'; SET Gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates --- Jeudi FÎvrier 2008 Vendredi FÎvrier 2008 Samedi FÎvrier 2008 Dimanche FÎvrier 2008 Lundi FÎvrier 2008 Mardi FÎvrier 2008 Mercredi FÎvrier 2008
Re: [PATCHES] Fix for 8.3 MSVC locale (Was [HACKERS] NLS on MSVC strikes back!)
Thank you. Is there any reason why JP locale files are not in normal installation? -Original Message- From: Hiroshi Saito [mailto:[EMAIL PROTECTED] Sent: Thursday, February 14, 2008 8:00 PM To: Magnus Hagander; Gevik Babakhani Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] Fix for 8.3 MSVC locale (Was [HACKERS] NLS on MSVC strikes back!) Hi. Sorry, I don't understand the point of this patch. However, reality was confirmed. I use initdb -E UTF-8 --no-locale Gevik-san patch apply it. (So, ja is not contained.) http://winpg.jp/~saito/pg_work/LC_MESSAGE_CHECK/Gevik_afterpatch.png Were you changeful before and after the correction? P.S) I was thinking about the improvement of other relations. Regards, Hiroshi Saito - Original Message - From: Magnus Hagander [EMAIL PROTECTED] Gevik Babakhani wrote: Hereby a patch that fixes NLS support on PG 8.3 compiled with MSVC. Haven't looked into the details of the patch yet, will do so. But the first thing I notice - you say this is only for MSVC, right? But the patch will also change the behaviour for the mingw build. Since you say you haven't tested on it, does the documentation imply that it would work on mingw, or is this likely to break that build? Perhaps it should be made conditional on MSVC only, and not on WIN32? //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Fix for 8.3 MSVC locale (Was [HACKERS] NLS on MSVC strikes back!)
We have a directive called WIN32_ONLY_COMPILER that's used for this. It'll pick up MSVC and Borland C++ which normally behave at least almost the same. I am installing mingw to test the patch there. Chances are it will break because mingw does __declspec(dllimport) differently than msvc Thanks. //Magnus Humm... I was expecting it to break... but it compiled just fine :) Here are the steps I took.. On a Win 2003 VM (VMWare): 1. Installed MinGW-5.1.3.exe 2. Installed MSYS-1.0.10.exe 3. Installed msysDTK-1.0.1.exe 4. Installed gettext-0.14.4.exe into C:\MinGW 4. Downloaded sources tarbal 5. ./configure --prefix=/home/gevik/build --without-zlib --enable-nls 6. make check, every thing was OK and 114 tests passed :) 7. make install,initdb...createdbetc..etc.. 8. set LC_MESSAGES and tested. See attachment :) The patch works both for MSVC and MINGW. Regards, Gevik. C:\msys\1.0\home\gevik\build\binpsql Welcome to psql 8.3.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for details. gevik=# set lc_messages to 'sv_SE'; SET gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates --- Torsdag Februari 2008 Fredag Februari 2008 Lrdag Februari 2008 Sndag Februari 2008 Mndag Februari 2008 Tisdag Februari 2008 Onsdag Februari 2008 (7 rows) gevik=# set lc_messages to 'de_DE'; SET gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Donnerstag Februar 2008 Freitag Februar 2008 Samstag Februar 2008 Sonntag Februar 2008 Montag Februar 2008 Dienstag Februar 2008 Mittwoch Februar 2008 (7 rows) gevik=# set lc_messages to 'English_United_States'; SET gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Thursday February 2008 Friday February 2008 Saturday February 2008 Sunday February 2008 Monday February 2008 Tuesday February 2008 Wednesday February 2008 (7 rows) gevik=# set lc_messages to 'tr-TR'; SET gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates -- Persembe Subat 2008 Cuma Subat 2008 Cumartesi Subat 2008 Pazar Subat 2008 Pazartesi Subat 2008 Sali Subat 2008 arsamba Subat 2008 (7 rows) gevik=# set lc_messages to 'fr'; SET gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates -- Jeudi Fvrier 2008 Vendredi Fvrier 2008 Samedi Fvrier 2008 Dimanche Fvrier 2008 Lundi Fvrier 2008 Mardi Fvrier 2008 Mercredi Fvrier 2008 (7 rows) gevik=# set lc_messages to 'Spanish_Spain'; SET gevik=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates --- Jueves Febrero 2008 Viernes Febrero 2008 Sbado Febrero 2008 Domingo Febrero 2008 Lunes Febrero 2008 Martes Febrero 2008 Mircoles Febrero 2008 (7 rows) gevik=# ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Fix for 8.3 MSVC locale (Was [HACKERS] NLS on MSVCstrikes back!)
Hmm, interestingly you lost the diacritics here. The output is mangled for Saturday and Wednesday, which should read Sábado and Miércoles respectively. It is not good that the system allows you to output invalidly encoded data. What happens if you try setting lc_messages to Spanish_Spain.65001 instead? This is because of the codepage dosbox had when I rand the test. I ran the same test in PGAdmin and correct values are presented. Ideally, it should be an error to set lc_messages to a value that's not compatible with the current encoding. Do we do that currently elsewhere? Not that I know of. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Fix for compiler warnings on MSVC
this is a fix for the compiler warnings on MSVC due differences in declaration and implementation of _yconv in strfime.c. I have testing this patch on: - MSVC 8 - gcc version 3.4.6 20060404 (Red Hat 3.4.6-3) Regards, Gevik. 2.strftime.c.patch Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Fix for compiler warnings on MSVC
Argh for delays in the mailinglist delivery and my reading :-) I've just applied one identical and one very similar patch to yours for these two issues. But thanks anyway :-) Thank you :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] lc_time and localized dates
Magnus, Please look at this patch before you check my last patch about the locale. It seems that Euler's solution also fixes the windows locale bug that I was trying to fix. Replacing the lc_messages with lc_time when using to_char seems to be the correct direction. I have compiled and tested Euler's patch on MSVC and got the correct results. Euler, I think your patch is correct for the most part except you should not force the first letter of day/month names to uppercase hence this is not grammatically correct for some languages. After a quick glimpse of your cache mechanism, I think we can use this to solve some other TO_CHAR/TO_DATE items. Regards, Gevik. Resuts: Gevik=# set lc_time to 'Dutch, Netherlands'; SET Gevik=# select to_char(now() + '8 months'::interval, 'TMDay Day dy, DD TMMon Month TMmonth '); to_char - Zondag Sundaysun, 26 Okt October oktober 2008 (1 row) Gevik=# -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Euler Taveira de Oliveira Sent: Monday, February 25, 2008 5:53 AM To: PostgreSQL-patches Subject: [PATCHES] lc_time and localized dates Hi, Attached is a patch that replaces the lc_messages with lc_time when using to_char in translation mode (TM) [1]. It doesn't change the output behaviour. Per discussion [2], it's using some cache mechanism so we don't need to call setlocale() all the time. Some issues: (i) some locales don't capitalize the first letter. I'm using pg_toupper() to do the job but I'm afraid it's not appropriated. I'm using it 'cause i'm too lazy to move localized_str_toupper(). Any suggestions? (ii) it didn't address the problem spotted at [3][4]. IMHO, it's ok for a non-superuser to set lc_time. Opinions? euler=# show lc_time; lc_time - pt_BR (1 registro) euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth '); to_char Segunda Mondaymon, 25 Fev February fevereiro 2008 (1 registro) euler=# set lc_time to 'es_ES'; SET euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth '); to_char Lunes Mondaymon, 25 Feb February febrero 2008 (1 registro) euler=# set lc_time to 'de_DE'; SET euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth '); to_char - Montag Mondaymon, 25 Feb February februar 2008 (1 registro) euler=# set lc_time to 'fr_FR'; SET euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth '); to_char Lundi Mondaymon, 25 Fév February février 2008 (1 registro) euler=# set lc_time to 'C'; SET euler=# select to_char(now(), 'TMDay Day dy, DD TMMon Month TMmonth '); to_char -- Monday Mondaymon, 25 Feb February february 2008 (1 registro) [1] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00539.php [2] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00693.php [3] http://archives.postgresql.org/pgsql-hackers/2007-10/msg00214.php [4] http://archives.postgresql.org/pgsql-hackers/2006-11/msg00692.php -- Euler Taveira de Oliveira http://www.timbira.com/ l10ntochar.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] lc_time and localized dates
No. [Looking at the code...] I think it only affects the LC_MESSAGES 'cause setlocale(LC_MESSAGES) don't work on Windows. In order to make setlocale(LC_MESSAGES) affect on windows some additional steps must be taken. I don't go deep in that now. I have a fix with description etc. etc. Is it ok to output TMDay as 'Dinsdag' ? No. you don't write dinsdag with a D in Dutch. Looking into code for a couple of days now and the recent discussions, I must say that there is more broken in locale to_char/to_date (Windows) than we can see at first glance. I am going to report my finding a.s.a.p. Then we can discuss about a fix. Regards, Gevik ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] lc_time and localized dates
This is what you get when you copy a proprietary, poorly specified interface. The to_char functions are supposed to be Oracle-compatible, so we need to check what Oracle does. Whether that is useful in practice is a bit secondary. I'm beginning to think, if you want formatting functions that are more sane, stable, and standardized, export sprintf and strftime to PostgreSQL. Perhaps this is good time to think about (I'm being careful to say redesign) a review both regarding date/time formatting and locale handling for these functions in general. Regards, Gevik ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] lc_time and localized dates
But as Peter remarks nearby, this discussion is wasted anyway, because there is only one correct answer: whatever Oracle does with these cases is what to_char() should do. ++1 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] V1.1 patch for TODO Item: SQL-language reference parameters by name.
Thank you for your comments. I will start working on a new version and send a patch for review when ready. Regards, Gevik. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2008 9:49 PM To: Gevik Babakhani Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] V1.1 patch for TODO Item: SQL-language reference parameters by name. Gevik Babakhani [EMAIL PROTECTED] writes: This is a minor update to 1.0 (corrected some typo here and there). Please see: http://archives.postgresql.org/pgsql-patches/2007-11/msg00253.php I looked this over a bit and feel that it's nowhere near ready to apply. The main problem is that the callback mechanism is very awkwardly designed. In the first place, I don't see a need for a stack: if you're parsing a statement in a function body, there is only one function that could possibly be supplying parameter names. Having to manipulate a global variable to change the stack is expensive (you are lacking PG_TRY blocks that would be needed to restore the stack after error). But the real problem is that unconditionally calling every handler on the stack means you need strange rules to detect which handler will or has already handled the situation, plus you've got extremely ad-hoc structs that pass information in both directions since you've not provided any other way for a handler to return information. Also, once you've built the callback mechanism, why in the world would you funnel all the callbacks into a single handler that you then place inside the parser? The point of this exercise is to let code that is *outside* the main parser have some say over how names are resolved. And it shouldn't be necessary to expand code or enums known to the main parser to add a new use of the feature. I think a better design would rely on a callback function typedef'd this way: Node * (*Parser_Callback_Func) (Node *node, void *callback_args) where the node argument is an untransformed ColumnRef or ParamRef node that the regular parser isn't able to resolve, and the void * argument is some opaque state data that gets passed through the parser from the original caller. The charter of the function is to return a transformed node (most likely a Param, but it could be any legal expression tree) if it can make sense of the node, or NULL if it doesn't have a referent for the name. Rather than using a global stack I'd just make the function pointer and the callback_args be new parameters to parse_analyze(). They could then be stashed in ParseState till needed. I believe that we could use this mechanism to replace both the p_value_substitute kluge for domain CHECK expressions, and the parser's current handling of $n parameters. It'd be nice to get those hacks out of the core parser --- in particular parse_analyze_varparams should go away in favor of using two different callback functions depending on whether the set of param types is frozen or not. SQL function parameters, and someday plpgsql local variables, would be next. There are a number of other things I don't like here, notably ERRCODE_UNDEFINED_FUNCTION_PARAMETER_NAME ... if it's undefined, how do you know it's a parameter name? I'd just leave the error responses alone. And please find some less horrid solution around addImplicitRTE/warnAutoRange. If you have to refactor to get the callback to be executed at the right time then do so, but don't add parameters that fundamentally change the behavior of a function and then not bother to document them. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches