Re: [PATCHES] [BUGS] Solaris cc compiler on amd: PostgreSQL does not have native
Pierre Girard wrote: Bruce Momjian wrote: I have applied your patch with only minor comment additions. Let us know if additional changes are required. Thanks. Are these flags required to be supplied to configure, or just the ASM file? -Xa -xtarget=opteron -xarch=amd64 OK, I added this to the top of the ASM file as a comment. If i remember right, the problem is that those options are passed to configure but they're not set when compiling the asm file. Here's my configure line since it appears it got lost somewhere. MAKE=gmake CC=/opt/SUNWspro/bin/cc CFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron -xarch=amd64 -xregs=no%frameptr CXX=/opt/SUNWspro/bin/CC CXXFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron -xarch=amd64 -xregs=no%frameptr LDFLAGS=-xtarget=opteron -xarch=amd64 ./configure --without-readline Wow, those are a lot of options. Are they all required? Are the x64-specific? --- I am thinking the port isn't 100% fool-proof yet, but it is _much_ closer than it was, and people can now do testing to improve it. --- Pierre Girard wrote: Bruce Momjian wrote: Interesting. Seems you have a setup that uses its own assembly source file in port/tas/solaris_i386.s, rather than using inline C ASM. There are files for both Sparc and i386 there, and are both used only for the Sun compiler. Does the Sun compiler not support ASM inlining? I don't know but i imagine that if the postgresql team decided to do it that way it's probably not supported. I also modified the function alignment from 4 to 8, patch attached. Please give it a try and report back any error lines. I applied the patch and now the solaris_i386.s file compiles fine with the command: cc -Xa -xtarget=opteron -xarch=amd64 -c solaris_i386.s I made a few changes and was able to compile postgresql completely but i don't think we'll be trying it until after the holidays. I included the small changes i made but i'm sure you can make a much better job than i did. Seems Sun might be interested in geting this working. We don't have an official Sun contact yet for this project. Given this announcement, i'm surprised that you don't have a contact already: http://www.sun.com/smi/Press/sunflash/2005-11/sunflash.20051117.1.html Happy holidays everyone. diff -Naur postgresql-8.1.1/src/Makefile.shlib postgresql-8.1.1.new/src/Makefile.shlib --- postgresql-8.1.1/src/Makefile.shlib Sat Dec 3 15:16:39 2005 +++ postgresql-8.1.1.new/src/Makefile.shlib Fri Dec 23 10:04:58 2005 @@ -193,7 +193,7 @@ ifeq ($(GCC), yes) LINK.shared= $(CC) -shared else -LINK.shared= $(CC) -G +LINK.shared= $(CC) -G $(CFLAGS) endif ifeq ($(with_gnu_ld), yes) LINK.shared+= -Wl,-soname,$(soname) diff -Naur postgresql-8.1.1/src/backend/port/tas/solaris_x86_64.s postgresql-8.1.1.new/src/backend/port/tas/solaris_x86_64.s --- postgresql-8.1.1/src/backend/port/tas/solaris_x86_64.s Wed Dec 31 19:00:00 1969 +++ postgresql-8.1.1.new/src/backend/port/tas/solaris_x86_64.s Fri Dec 23 09:17:13 2005 @@ -0,0 +1,33 @@ +/= +/ tas.s -- test and set lock for solaris_i386 +/= + +.file tas.s +.text +.align 16 +.L1.text: + +.globl tas +tas: +pushq %rbp/save prev base pointer +movq%rsp,%rbp /new base pointer +pushq %rbx/save prev bx +movq8(%rbp),%rbx/load bx with address of lock +movq$255,%rax /put something in ax +xchgb %al,(%rbx) /swap lock value with 0 +cmpb$0,%al /did we get the lock? +jne .Locked +subq%rax,%rax /yes, we got it -- return 0 +jmp .Finish +.align 8 +.Locked: +movq$1,%rax /no, we didn't get it - return 1 +.Finish: +popq%rbx/restore prev bx +movq%rbp,%rsp /restore stack state +popq%rbp +ret /return +.align 8 +.type tas,@function +.size tas,.-tas + diff -Naur postgresql-8.1.1/src/include/storage/s_lock.h postgresql-8.1.1.new/src/include/storage/s_lock.h --- postgresql-8.1.1/src/include/storage/s_lock.h Tue Oct 11 16:41:32 2005 +++ postgresql-8.1.1.new/src/include/storage/s_lock.h Fri Dec 23 09:18:44 2005 @@ -788,7 +788,7
Re: [PATCHES] [BUGS] Solaris cc compiler on amd: PostgreSQL does not have native
Bruce Momjian pgman@candle.pha.pa.us writes: Pierre Girard wrote: MAKE=gmake CC=/opt/SUNWspro/bin/cc CFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron -xarch=amd64 -xregs=no%frameptr CXX=/opt/SUNWspro/bin/CC CXXFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron -xarch=amd64 -xregs=no%frameptr LDFLAGS=-xtarget=opteron -xarch=amd64 ./configure --without-readline Wow, those are a lot of options. Are they all required? Are the x64-specific? The CXX and CXXFLAGS settings are certainly entirely useless for PG. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [BUGS] Solaris cc compiler on amd: PostgreSQL does not have native
Pierre Girard wrote: Bruce Momjian wrote: MAKE=gmake CC=/opt/SUNWspro/bin/cc CFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron -xarch=amd64 -xregs=no%frameptr CXX=/opt/SUNWspro/bin/CC CXXFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron -xarch=amd64 -xregs=no%frameptr LDFLAGS=-xtarget=opteron -xarch=amd64 ./configure --without-readline Wow, those are a lot of options. Are they all required? Are the x64-specific? They're definitely not all required, the ones needed to activate amd specific code compilation are: -xtarget=opteron -xarch=amd64 The other options were taken from a document from sun about optimizing mysql on amd64 found at this address: http://developers.sun.com/solaris/articles/mysql_perf_tune.html I don't know if they would work on a different processor or not. Hopefully they'll make another document with postgresql in mind. Thanks. I have added the URL. --- I am thinking the port isn't 100% fool-proof yet, but it is _much_ closer than it was, and people can now do testing to improve it. --- Pierre Girard wrote: Bruce Momjian wrote: Interesting. Seems you have a setup that uses its own assembly source file in port/tas/solaris_i386.s, rather than using inline C ASM. There are files for both Sparc and i386 there, and are both used only for the Sun compiler. Does the Sun compiler not support ASM inlining? I don't know but i imagine that if the postgresql team decided to do it that way it's probably not supported. I also modified the function alignment from 4 to 8, patch attached. Please give it a try and report back any error lines. I applied the patch and now the solaris_i386.s file compiles fine with the command: cc -Xa -xtarget=opteron -xarch=amd64 -c solaris_i386.s I made a few changes and was able to compile postgresql completely but i don't think we'll be trying it until after the holidays. I included the small changes i made but i'm sure you can make a much better job than i did. Seems Sun might be interested in geting this working. We don't have an official Sun contact yet for this project. Given this announcement, i'm surprised that you don't have a contact already: http://www.sun.com/smi/Press/sunflash/2005-11/sunflash.20051117.1.html Happy holidays everyone. diff -Naur postgresql-8.1.1/src/Makefile.shlib postgresql-8.1.1.new/src/Makefile.shlib --- postgresql-8.1.1/src/Makefile.shlib Sat Dec 3 15:16:39 2005 +++ postgresql-8.1.1.new/src/Makefile.shlib Fri Dec 23 10:04:58 2005 @@ -193,7 +193,7 @@ ifeq ($(GCC), yes) LINK.shared = $(CC) -shared else -LINK.shared = $(CC) -G +LINK.shared = $(CC) -G $(CFLAGS) endif ifeq ($(with_gnu_ld), yes) LINK.shared += -Wl,-soname,$(soname) diff -Naur postgresql-8.1.1/src/backend/port/tas/solaris_x86_64.s postgresql-8.1.1.new/src/backend/port/tas/solaris_x86_64.s --- postgresql-8.1.1/src/backend/port/tas/solaris_x86_64.sWed Dec 31 19:00:00 1969 +++ postgresql-8.1.1.new/src/backend/port/tas/solaris_x86_64.sFri Dec 23 09:17:13 2005 @@ -0,0 +1,33 @@ +/= +/ tas.s -- test and set lock for solaris_i386 +/= + +.file tas.s +.text +.align 16 +.L1.text: + +.globl tas +tas: +pushq %rbp/save prev base pointer +movq%rsp,%rbp /new base pointer +pushq %rbx/save prev bx +movq8(%rbp),%rbx/load bx with address of lock +movq$255,%rax /put something in ax +xchgb %al,(%rbx) /swap lock value with 0 +cmpb$0,%al /did we get the lock? +jne .Locked +subq%rax,%rax /yes, we got it -- return 0 +jmp .Finish +.align 8 +.Locked: +movq$1,%rax /no, we didn't get it - return 1 +.Finish: +popq%rbx/restore prev bx +movq%rbp,%rsp /restore stack state +popq%rbp +ret /return +.align 8 +.type tas,@function +.size tas,.-tas + diff -Naur postgresql-8.1.1/src/include/storage/s_lock.h postgresql-8.1.1.new/src/include/storage/s_lock.h --- postgresql-8.1.1/src/include/storage/s_lock.h Tue Oct 11 16:41:32 2005 +++
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Josh Berkus wrote:
Folks,
Just got tripped up by this:
GRANT SELECT ON table1 TO someuser;
GRANT SELECT ON table1_id_seq TO someuser;
both work
However,
GRANT SELECT ON TABLE table1 TO someuser;
... works, while
GRANT SELECT ON SEQUENCE table1_id_seq TO someuser;
... raises an error.
This is inconsistent. Do people agree with me that the parser should
accept SEQUENCE there, since the optional object name works for all
other objects? Is there some technical reason this is difficult to do?
The following patch allows VIEW and SEQUENCE for GRANT. I didn't add
checks for relkind, figuring it wasn't worth it, right?
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 grant.sgml
*** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 - 1.50
--- doc/src/sgml/ref/grant.sgml 5 Jan 2006 18:18:37 -
***
*** 22,28
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 22,28
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE | VIEW | SEQUENCE ] replaceable
class=PARAMETERtablename/replaceable [, ...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
Index: src/backend/parser/gram.y
===
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.521
diff -c -c -r2.521 gram.y
*** src/backend/parser/gram.y 29 Dec 2005 04:53:18 - 2.521
--- src/backend/parser/gram.y 5 Jan 2006 18:18:41 -
***
*** 3315,3320
--- 3315,3321
n-objs = $1;
$$ = n;
}
+ /* The next three are processed identically. */
| TABLE qualified_name_list
{
PrivTarget *n = makeNode(PrivTarget);
***
*** 3322,3327
--- 3323,3342
n-objs = $2;
$$ = n;
}
+ | VIEW qualified_name_list
+ {
+ PrivTarget *n = makeNode(PrivTarget);
+ n-objtype = ACL_OBJECT_RELATION;
+ n-objs = $2;
+ $$ = n;
+ }
+ | SEQUENCE qualified_name_list
+ {
+ PrivTarget *n = makeNode(PrivTarget);
+ n-objtype = ACL_OBJECT_RELATION;
+ n-objs = $2;
+ $$ = n;
+ }
| FUNCTION function_with_argtypes_list
{
PrivTarget *n = makeNode(PrivTarget);
---(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] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian pgman@candle.pha.pa.us writes: The following patch allows VIEW and SEQUENCE for GRANT. I didn't add checks for relkind, figuring it wasn't worth it, right? The permissions for a sequence aren't the same as they are for a table. We've sort of ignored the point to date, but if we're going to add special syntax for granting on a sequence, I don't think we should continue to ignore it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: The following patch allows VIEW and SEQUENCE for GRANT. I didn't add checks for relkind, figuring it wasn't worth it, right? The permissions for a sequence aren't the same as they are for a table. We've sort of ignored the point to date, but if we're going to add special syntax for granting on a sequence, I don't think we should continue to ignore it. Uh, how are they different? You mean just UPDATE and none of the others do anything? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce, Tom, The permissions for a sequence aren't the same as they are for a table. We've sort of ignored the point to date, but if we're going to add special syntax for granting on a sequence, I don't think we should continue to ignore it. Uh, how are they different? You mean just UPDATE and none of the others do anything? Yes, it would be nice to have real permissions for sequences, specifically USE (which allows nextval() and currval()) and UPDATE (which would allow setval() ). However, I don't know that the added functionality would justify breaking backwards-compatibility. Oh, and Bruce, I can't imagine needing specific relkind so I think that part's fine. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian wrote: The following patch allows VIEW and SEQUENCE for GRANT. I didn't add checks for relkind, figuring it wasn't worth it, right? I think checking the relkind is pretty reasonable, and should require only a few lines of code -- why not do it? -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Summary table trigger example race condition
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html example 36-4 has a race condition in the code that checks to see if a row exists. It should use the code from example 36-1. This patch fixes that. It also adds some commands to show what the summary table output looks like. Unfortunately gamke html is bombing with some kind of library error, so I can't verify that I didn't break the sgml. BTW, should this have gone to -docs instead? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.83 diff -u -r1.83 plpgsql.sgml --- doc/src/sgml/plpgsql.sgml 29 Dec 2005 04:02:32 - 1.83 +++ doc/src/sgml/plpgsql.sgml 5 Jan 2006 21:36:14 - @@ -3017,31 +3017,33 @@ -- There might have been no row with this time_key (e.g new data!). IF (NOT FOUND) THEN -BEGIN -INSERT INTO sales_summary_bytime ( -time_key, -amount_sold, -units_sold, -amount_cost) -VALUES ( -delta_time_key, -delta_amount_sold, -delta_units_sold, -delta_amount_cost - ); -EXCEPTION --- --- Catch race condition when two transactions are adding data --- for a new time_key. --- -WHEN UNIQUE_VIOLATION THEN -UPDATE sales_summary_bytime -SET amount_sold = amount_sold + delta_amount_sold, -units_sold = units_sold + delta_units_sold, -amount_cost = amount_cost + delta_amount_cost -WHERE time_key = delta_time_key; - -END; +lt;lt;insert_updategt;gt; +LOOP +UPDATE sales_summary_bytime +SET amount_sold = amount_sold + delta_amount_sold, +units_sold = units_sold + delta_units_sold, +amount_cost = amount_cost + delta_amount_cost +WHERE time_key = delta_time_key; + +EXIT insert_update WHEN found; + +BEGIN +INSERT INTO sales_summary_bytime ( +time_key, +amount_sold, +units_sold, +amount_cost) +VALUES ( +delta_time_key, +delta_amount_sold, +delta_units_sold, +delta_amount_cost + ); +EXCEPTION +WHEN UNIQUE_VIOLATION THEN +-- do nothing +END; +END LOOP insert_update; END IF; RETURN NULL; @@ -3051,6 +3053,16 @@ CREATE TRIGGER maint_sales_summary_bytime AFTER INSERT OR UPDATE OR DELETE ON sales_fact FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime(); + +INSERT INTO sales_fact VALUES(1,1,1,10,3,15); +INSERT INTO sales_fact VALUES(1,2,1,20,5,35); +INSERT INTO sales_fact VALUES(2,2,1,40,15,135); +INSERT INTO sales_fact VALUES(2,3,1,10,1,13); +SELECT * FROM sales_summary_bytime; +DELETE FROM sales_fact WHERE product_key = 1; +SELECT * FROM sales_summary_bytime; +UPDATE sales_fact SET units_sold = units_sold * 2; +SELECT * FROM sales_summary_bytime; /programlisting /example ---(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] [HACKERS] Inconsistent syntax in GRANT
Josh Berkus josh@agliodbs.com writes: Uh, how are they different? You mean just UPDATE and none of the others do anything? Yes, it would be nice to have real permissions for sequences, specifically USE (which allows nextval() and currval()) and UPDATE (which would allow setval() ). However, I don't know that the added functionality would justify breaking backwards-compatibility. We could maintain backwards compatibility by continuing to accept the old equivalences when you say GRANT ON TABLE. But when you say GRANT ON SEQUENCE, I think it should use sequence-specific privilege keywords, and not allow the privileges that don't mean anything for sequences, like DELETE. I'm not sure offhand what keywords we'd want to use, but now is the time to look at it, *before* it becomes set in stone that GRANT ON SEQUENCE is just another spelling of GRANT ON TABLE. (The subtext of this is that I don't have a lot of use for allowing variant syntaxes that don't actually do anything different ...) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] display and expression of the home directory in Win32
Dear Bruce san.
I receive an inquiry from a user frequently. Where is it about a home
directory in a place? Then, In offer of the initial state of Windows,
the place is hidden and is not visible to a user. I considered what can
be offered this way and that. Then, using pg_config thinks that it is good.
This patch show a user a clear place. Please take this into consideration.
F:\postgresql-8.1.2stable\src\bin\pg_config\Releasepg_config.exe
BINDIR = F:/postgresql-8.1.2stable/src/bin/pg_config/Release
DOCDIR =
INCLUDEDIR =
PKGINCLUDEDIR =
INCLUDEDIR-SERVER =
LIBDIR =
PKGLIBDIR =
LOCALEDIR =
MANDIR =
SHAREDIR =
SYSCONFDIR =
PGXS = /pgxs/src/makefiles/pgxs.mk
HOMEDIR = C:/Documents and Settings/saito/Application Data/postgresql
VERSION = PostgreSQL 8.1.1
dir C:/Documents and Settings/saito/Application Data/postgresql
2005/08/10 11:19DIR .
2005/08/10 11:19DIR ..
2005/08/10 11:19 0 pgpass.conf
2005/01/13 00:00 3,520 postgresql.crt
2005/01/13 00:00 891 postgresql.key
Regards,
Hiroshi Saito
--- src/bin/pg_config/pg_config.c.orig Fri Jan 6 10:16:56 2006
+++ src/bin/pg_config/pg_config.c Fri Jan 6 10:49:23 2006
@@ -29,6 +29,17 @@
static const char *progname;
static char mypath[MAXPGPATH];
+#ifdef WIN32
+#ifdef _WIN32_IE
+#undef _WIN32_IE
+#endif
+#define _WIN32_IE 0x0500
+#ifdef near
+#undef near
+#endif
+#define near
+#include shlobj.h
+#endif
/*
* This function cleans up the paths for use with either cmd.exe or Msys
@@ -47,6 +58,7 @@
#ifdef WIN32
char *ptr;
+#ifndef WIN32_CLIENT_ONLY
if (GetShortPathName(path, path, MAXPGPATH - 1) == 0)
{
/*
@@ -59,7 +71,7 @@
return;
}
}
-
+#endif /* !WIN32_CLIENT_ONLY */
/* Replace '\' with '/' */
for (ptr = path; *ptr; ptr++)
{
@@ -364,6 +376,25 @@
printf(PostgreSQL PG_VERSION \n);
}
+#ifdef WIN32
+static void
+show_homedir(bool all)
+{
+ charhomedir[MAXPGPATH];
+
+ if (all)
+ printf(HOMEDIR = );
+
+ ZeroMemory(homedir, sizeof(homedir));
+ SHGetFolderPath(NULL, CSIDL_APPDATA, NULL, 0, homedir);
+ cleanup_path(homedir);
+#ifdef WIN32_CLIENT_ONLY
+ printf(\%s/postgresql\\n, homedir);
+#else
+ printf(%s/postgresql\n, homedir);
+#endif
+}
+#endif
/*
* Table of known information items
@@ -397,6 +428,9 @@
{--ldflags, show_ldflags},
{--ldflags_sl, show_ldflags_sl},
{--libs, show_libs},
+#ifdef WIN32
+ {--homedir, show_homedir},
+#endif
{--version, show_version},
{NULL, NULL}
};
@@ -431,6 +465,9 @@
printf(_( --ldflags show LDFLAGS value used when
PostgreSQL was built\n));
printf(_( --ldflags_sl show LDFLAGS_SL value used when
PostgreSQL was built\n));
printf(_( --libsshow LIBS value used when PostgreSQL
was built\n));
+#ifdef WIN32
+ printf(_( --homedir show HOME directry of owner user\n));
+#endif
printf(_( --version show the PostgreSQL version\n));
printf(_( --helpshow this help, then exit\n));
printf(_(\nWith no arguments, all known items are shown.\n\n));
---(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] Summary table trigger example race condition
On Fri, Jan 06, 2006 at 02:00:34PM +1300, Mark Kirkwood wrote: However, I think the actual change is not quite right - after running DOH! It would be good if doc/src had a better mechanism for handling code; one that would allow for writing the code natively (so you don't have to worry about translating into lt; and into gt;) and for unit testing the different pieces of code. Anyway, updated patch attached. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.83 diff -c -r1.83 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 29 Dec 2005 04:02:32 - 1.83 --- doc/src/sgml/plpgsql.sgml 6 Jan 2006 03:03:11 - *** *** 3007,3022 END IF; ! -- Update the summary row with the new values. ! UPDATE sales_summary_bytime ! SET amount_sold = amount_sold + delta_amount_sold, ! units_sold = units_sold + delta_units_sold, ! amount_cost = amount_cost + delta_amount_cost ! WHERE time_key = delta_time_key; - -- There might have been no row with this time_key (e.g new data!). - IF (NOT FOUND) THEN BEGIN INSERT INTO sales_summary_bytime ( time_key, --- 3007,3023 END IF; ! -- Insert or update the summary row with the new values. ! lt;lt;insert_updategt;gt; ! LOOP ! UPDATE sales_summary_bytime ! SET amount_sold = amount_sold + delta_amount_sold, ! units_sold = units_sold + delta_units_sold, ! amount_cost = amount_cost + delta_amount_cost ! WHERE time_key = delta_time_key; + EXIT insert_update WHEN found; BEGIN INSERT INTO sales_summary_bytime ( time_key, *** *** 3029,3048 delta_units_sold, delta_amount_cost ); EXCEPTION - -- - -- Catch race condition when two transactions are adding data - -- for a new time_key. - -- WHEN UNIQUE_VIOLATION THEN ! UPDATE sales_summary_bytime ! SET amount_sold = amount_sold + delta_amount_sold, ! units_sold = units_sold + delta_units_sold, ! amount_cost = amount_cost + delta_amount_cost ! WHERE time_key = delta_time_key; ! END; ! END IF; RETURN NULL; END; --- 3030,3044 delta_units_sold, delta_amount_cost ); + + EXIT insert_update; + EXCEPTION WHEN UNIQUE_VIOLATION THEN ! -- do nothing END; ! END LOOP insert_update; ! RETURN NULL; END; *** *** 3051,3056 --- 3047,3062 CREATE TRIGGER maint_sales_summary_bytime AFTER INSERT OR UPDATE OR DELETE ON sales_fact FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime(); + + INSERT INTO sales_fact VALUES(1,1,1,10,3,15); + INSERT INTO sales_fact VALUES(1,2,1,20,5,35); + INSERT INTO sales_fact VALUES(2,2,1,40,15,135); + INSERT INTO sales_fact VALUES(2,3,1,10,1,13); + SELECT * FROM sales_summary_bytime; + DELETE FROM sales_fact WHERE product_key = 1; + SELECT * FROM sales_summary_bytime; + UPDATE sales_fact SET units_sold = units_sold * 2; + SELECT * FROM sales_summary_bytime; /programlisting /example ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Summary table trigger example race condition
Jim C. Nasby wrote: On Fri, Jan 06, 2006 at 02:00:34PM +1300, Mark Kirkwood wrote: However, I think the actual change is not quite right - after running DOH! It would be good if doc/src had a better mechanism for handling code; one that would allow for writing the code natively (so you don't have to worry about translating into lt; and into gt;) and for unit testing the different pieces of code. Yes it would - I usually build the SGML - HTML, then cut the code out of a browser session to test - the pain is waiting for the docs to build. Anyway, updated patch attached. This one is good! Cheers Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On Thu, Jan 05, 2006 at 11:44:24 -0800, Josh Berkus josh@agliodbs.com wrote: Bruce, Tom, The permissions for a sequence aren't the same as they are for a table. We've sort of ignored the point to date, but if we're going to add special syntax for granting on a sequence, I don't think we should continue to ignore it. Uh, how are they different? You mean just UPDATE and none of the others do anything? Yes, it would be nice to have real permissions for sequences, specifically USE (which allows nextval() and currval()) and UPDATE (which would allow setval() ). However, I don't know that the added functionality would justify breaking backwards-compatibility. It might be nice to split nextval and currval access as well. nextval access corresponds to INSERT and currval access to SELECT. ---(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
