[PATCHES] (8.1) to_timestamp correction (epoch to timestamptz)
Note: This patch is intended for 8.1 (as was the original). I believe the previous patch I submitted to convert Unix epoch to timestamptz contains a bug relating to its use of AT TIME ZONE. Please find attached a corrected patch diffed against HEAD, which includes documentation. The original function was equivalent to CREATE FUNCTION to_timestamp (DOUBLE PRECISION) RETURNS timestamptz LANGUAGE SQL AS ' select ( (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) at time zone \'UTC\' ) '; The AT TIME ZONE 'UTC' removes the time zone from the timestamptz, returning timestamp. However, the function is declared to return timestamptz. The original patch appeared to work, but creating this equivalent function fails as it doesn't return the declared datatype. The corrected function restores the time zone with an additional AT TIME ZONE 'UTC': CREATE FUNCTION to_timestamp (double precision) returns timestamptz language sql as ' select ( (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) at time zone \'UTC\' ) at time zone \'UTC\' '; Michael Glaesemann grzm myrealbox com to_timestamp-20041212.diff Description: application/text ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] regression script/makefile exit failure
Andrew Dunstan wrote: Attached patch has been tested on Cygwin and found to work as expected when initdb fails during make check - I don't have access to a FreeBSD machine to do a test buildfarm run. Patch installed. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: 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] pg_resetxlog as root
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Neil Conway Sent: 12 December 2004 22:57 To: pgsql-patches Subject: [PATCHES] pg_resetxlog as root I suppose a similar fix is needed for Win32? If so, pgwin32_is_admin() would be the natural routine to call, but that is currently in src/backend/port -- we would need to move it to src/port, probably. Comments? Sounds reasonable to me. Regards, Dave. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pg_get_viewdef returns one paren too much
Andreas Pflug [EMAIL PROTECTED] writes: [EMAIL PROTECTED] noticed a problem with pg_get_viewdef in prettyprint mode. Applied in HEAD and 7.4 branches. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] Optional REFERENCES Feature in CREATE TRIGGER Command
Hi,
The attached patch adds the optional REFERENCES syntax in CREATE TRIGGER
statement to make an automatic alias for OLD/NEW record during trigger
setup. The implementation of this new feature makes CREATE TRIGGER
command more compatible to SQL standard, and allows the future
implementation of executing SQL commands in trigger action.
After the implementation, the extended syntax of statement is as follows.
CREATE TRIGGER name BEFORE|AFTER
INSERT|DELETE|UPDATE [OR...] ON tablename
[REFERENCING OLD|NEW [AS] identifier]
[FOR [EACH] ROW|STATEMENT]
EXECUTE PROCEDURE funcname (arguments)
The patch will also update two columns, condition_reference_old_table and
condition_reference_new_table with alias names of the OLD/NEW record in
the Triggers table of the information schema.
$ diff -c src/include/nodes/parsenodes.h.orig src/include/nodes/parsenodes.h
*** src/include/nodes/parsenodes.h.orig 2004-07-15 10:15:08.0 -0400
--- src/include/nodes/parsenodes.h 2004-09-01 18:20:20.0 -0400
***
*** 994,999
--- 994,1000
RangeVar *relation; /* relation trigger is on */
List *funcname; /* qual. name of function to call */
List *args; /* list of (T_String) Values or
NIL */
+ List *tupleref; /* referenced tuple */
boolbefore; /* BEFORE/AFTER */
boolrow;/* ROW/STATEMENT */
charactions[4]; /* 1 to 3 of 'i', 'u', 'd', +
trailing \0 */
$ diff -c src/include/catalog/pg_trigger.h.orig src/include/catalog/pg_trigger.h
*** src/include/catalog/pg_trigger.h.orig 2004-07-15 10:11:46.0
-0400
--- src/include/catalog/pg_trigger.h2004-09-15 16:00:06.0 -0400
***
*** 29,52
*/
CATALOG(pg_trigger)
{
! Oid tgrelid;/* triggered relation */
NameDatatgname; /* trigger' name */
! Oid tgfoid; /* OID of function to
be called */
int2tgtype; /* BEFORE/AFTER
UPDATE/DELETE/INSERT
*
ROW/STATEMENT */
booltgenabled; /* trigger is enabled/disabled
*/
! booltgisconstraint; /* trigger is a RI constraint */
! NameDatatgconstrname; /* RI constraint name */
! Oid tgconstrrelid; /* RI table of foreign key
definition */
/* in the case of ON DELETE or ON UPDATE */
! booltgdeferrable; /* RI trigger is deferrable */
! booltginitdeferred; /* RI trigger is deferred initially */
int2tgnargs;/* # of extra arguments in
tgargs */
! int2vector tgattr; /* UPDATE of attr1, attr2 ... (NI) */
bytea tgargs; /*
first\000second\000tgnargs\000 */
int2tgncols;/* # of columns in tgcols */
bytea tgcols; /* column names */
} FormData_pg_trigger;
/*
--- 29,53
*/
CATALOG(pg_trigger)
{
! Oid tgrelid;/* triggered relation */
NameDatatgname; /* trigger' name */
! Oid tgfoid; /* OID of function to be called
*/
int2tgtype; /* BEFORE/AFTER
UPDATE/DELETE/INSERT
*
ROW/STATEMENT */
booltgenabled; /* trigger is enabled/disabled
*/
! booltgisconstraint; /* trigger is a RI constraint */
! NameDatatgconstrname; /* RI constraint name */
! Oid tgconstrrelid; /* RI table of foreign key
definition */
/* in the case of ON DELETE or ON UPDATE */
! booltgdeferrable; /* RI trigger is deferrable */
! booltginitdeferred; /* RI trigger is deferred
initially */
int2tgnargs;/* # of extra arguments in
tgargs */
! int2vector tgattr; /* UPDATE of attr1, attr2 ...
(NI) */
bytea tgargs; /*
first\000second\000tgnargs\000 */
int2tgncols;/* # of columns in tgcols */
bytea tgcols; /* column names */
+ NameDatatgidentifier; /* referenced tuple */
} FormData_pg_trigger;
/*
***
*** 60,87
*compiler constants for pg_trigger
*
*/
! #define Natts_pg_trigger 15
#define
Re: [PATCHES] Optional REFERENCES Feature in CREATE TRIGGER Command
[EMAIL PROTECTED] writes: The attached patch adds the optional REFERENCES syntax in CREATE TRIGGER statement to make an automatic alias for OLD/NEW record during trigger setup. The implementation of this new feature makes CREATE TRIGGER command more compatible to SQL standard, and allows the future implementation of executing SQL commands in trigger action. I must be missing something, but AFAICS this patch doesn't actually *do* anything useful. It looks to me like you've implemented a write-only addition to the system catalogs. (And not even done a very good job of that --- no documentation, no pg_dump support.) What's the point? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] pt_BR FAQ update
Patch applied. Thanks. --- Euler Taveira de Oliveira wrote: Hi, This little patch change my e-mail address in the FAQ. If it's possible yet, please apply it to HEAD. = Euler Taveira de Oliveira euler[at]yahoo_com_br __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ Content-Description: faq.diff [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 3: 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] default timezone in postgresql.conf
Changes the doc's to reflect what the user will find as default.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Index: runtime.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.295
diff -c -r1.295 runtime.sgml
*** runtime.sgml 5 Dec 2004 20:05:47 - 1.295
--- runtime.sgml 13 Dec 2004 05:39:01 -
***
*** 3150,3157
listitem
para
Sets the time zone for displaying and interpreting time
! stamps. The default is to use whatever the system environment
! specifies as the time zone. See xref
linkend=datatype-datetime for more information.
/para
/listitem
--- 3150,3157
listitem
para
Sets the time zone for displaying and interpreting time
! stamps. The default is 'unknown', which means to use whatever
! the system environment specifies as the time zone. See xref
linkend=datatype-datetime for more information.
/para
/listitem
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[PATCHES] pg_resetxlog as root
We should prevent pg_resetxlog from being run as root: it writes new
files to $PGDATA, so running the tool as root will result in those files
being owned by root, which makes the data directory unusable.
Attached is a trivial patch that makes this change for Unix. I suppose a
similar fix is needed for Win32? If so, pgwin32_is_admin() would be the
natural routine to call, but that is currently in src/backend/port -- we
would need to move it to src/port, probably. Comments?
-Neil
Index: src/bin/pg_resetxlog/pg_resetxlog.c
===
RCS file: /var/lib/cvs/pgsql/src/bin/pg_resetxlog/pg_resetxlog.c,v
retrieving revision 1.25
diff -c -r1.25 pg_resetxlog.c
*** src/bin/pg_resetxlog/pg_resetxlog.c 17 Nov 2004 21:37:47 - 1.25
--- src/bin/pg_resetxlog/pg_resetxlog.c 9 Dec 2004 07:03:20 -
***
*** 181,186
--- 181,200
snprintf(ControlFilePath, MAXPGPATH, %s/global/pg_control, DataDir);
/*
+ * Don't allow pg_resetxlog to be run as root, to avoid
+ * overwriting the ownership of files in the data directory. We
+ * need only check for root -- any other user won't have
+ * sufficient permissions to modify files in the data directory.
+ */
+ if (geteuid() == 0)
+ {
+ fprintf(stderr, _(%s: cannot be run as \root\\n), progname);
+ fprintf(stderr, _(You must run pg_resetxlog
+ as the PostgreSQL superuser.\n));
+ exit(1);
+ }
+
+ /*
* Check for a postmaster lock file --- if there is one, refuse to
* proceed, on grounds we might be interfering with a live
* installation.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] pg_resetxlog as root
On Sun, 2004-12-12 at 23:59 +, Dave Page wrote:
Sounds reasonable to me.
Attached is a patch that implements this.
src/port/backend/win32/security.c is moved to src/port/win32_security.c,
and conditionally added to LIBOBJS. Note that I don't have much
experience with the build system, and less still with the Win32 port, so
please let me know if there's a better way to do this. Also, I don't
have a Windows build environment -- could someone verify whether this
builds on Win32?
-Neil
#
# delete_file src/backend/port/win32/security.c
#
# add_file src/port/win32_security.c
#
# patch src/Makefile.global.in
# from [4183afa6d7d7945d4e29a3aa30a18c312251ed8e]
#to [6a0e08d33676e1c8c17a296653edbbd8afd5a2c2]
#
# patch src/backend/port/win32/Makefile
# from [009b45987d1428cbbd1d0bb78ee55868f9729ca3]
#to [97a5831267b24977fa4f3770d06e0fef7884bf2f]
#
# patch src/bin/pg_resetxlog/pg_resetxlog.c
# from [df43f11496af96317f9f927f84c488ce33492c93]
#to [58ddbddf7765426d60a8fddaaea2034e1e259cfb]
#
# patch src/include/port/win32.h
# from [39b47f7b5c1eaafadaff48cb2c5ccf60ca59]
#to [e346ae59f9fde1a95a46ccf81ba3ca8c50bf8cef]
#
# patch src/port/win32_security.c
# from []
#to [4766ac4547aa1648eaa8596ce31ce12da763a3c8]
#
--- src/Makefile.global.in
+++ src/Makefile.global.in
@@ -362,6 +362,10 @@
LIBOBJS = @LIBOBJS@ dirmod.o exec.o noblock.o path.o pipe.o pgsleep.o pgstrcasecmp.o sprompt.o thread.o
+ifeq ($(PORTNAME),win32)
+LIBOBJS += win32_security.o
+endif
+
ifneq (,$(LIBOBJS))
LIBS := -lpgport $(LIBS)
ifdef PGXS
--- src/backend/port/win32/Makefile
+++ src/backend/port/win32/Makefile
@@ -12,7 +12,7 @@
top_builddir = ../../../..
include $(top_builddir)/src/Makefile.global
-OBJS = sema.o shmem.o timer.o socket.o signal.o security.o error.o
+OBJS = sema.o shmem.o timer.o socket.o signal.o error.o
all: SUBSYS.o
--- src/bin/pg_resetxlog/pg_resetxlog.c
+++ src/bin/pg_resetxlog/pg_resetxlog.c
@@ -181,6 +181,32 @@
snprintf(ControlFilePath, MAXPGPATH, %s/global/pg_control, DataDir);
/*
+ * Don't allow pg_resetxlog to be run as root, to avoid
+ * overwriting the ownership of files in the data directory. We
+ * need only check for root -- any other user won't have
+ * sufficient permissions to modify files in the data directory.
+ */
+#ifdef WIN32
+ if (pgwin32_is_admin())
+ {
+ fprintf(stderr, _(%s: cannot be executed by a user with
+ administrative permissions\n), progname);
+ fprintf(stderr, _(You must run %s as the PostgreSQL superuser.\n),
+progname);
+ exit(1);
+ }
+#else
+ if (geteuid() == 0)
+ {
+ fprintf(stderr, _(%s: cannot be executed by \root\\n),
+progname);
+ fprintf(stderr, _(You must run %s as the PostgreSQL superuser.\n),
+progname);
+ exit(1);
+ }
+#endif
+
+ /*
* Check for a postmaster lock file --- if there is one, refuse to
* proceed, on grounds we might be interfering with a live
* installation.
--- src/include/port/win32.h
+++ src/include/port/win32.h
@@ -145,15 +145,14 @@
const char *pgwin32_socket_strerror(int err);
int pgwin32_waitforsinglesocket(SOCKET s, int what);
-
-/* in backend/port/win32/security.c */
-extern int pgwin32_is_admin(void);
-extern int pgwin32_is_service(void);
#endif
/* in backend/port/win32/error.c */
void _dosmaperr(unsigned long);
+/* in port/win32_security.c */
+extern int pgwin32_is_admin(void);
+extern int pgwin32_is_service(void);
#define WEXITSTATUS(w) (((w) 8) 0xff)
#define WIFEXITED(w) (((w) 0xff) == 0)
--- src/port/win32_security.c
+++ src/port/win32_security.c
@@ -0,0 +1,248 @@
+/*-
+ *
+ * win32_security.c
+ * Microsoft Windows Win32 Security Support Functions
+ *
+ * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * $PostgreSQL: pgsql/src/backend/port/win32/security.c,v 1.7 2004/11/16 19:52:22 tgl Exp $
+ *
+ *-
+ */
+
+#include postgres.h
+
+
+static BOOL pgwin32_get_dynamic_tokeninfo(HANDLE token,
+ TOKEN_INFORMATION_CLASS class, char **InfoBuffer,
+ char *errbuf, int errsize);
+
+/*
+ * Returns nonzero if the current user has administrative privileges,
+ * or zero if not.
+ *
+ * Note: this cannot use ereport() because it's called too early during
+ * startup.
+ */
+int
+pgwin32_is_admin(void)
+{
+ HANDLE AccessToken;
+ char *InfoBuffer = NULL;
+ charerrbuf[256];
+ PTOKEN_GROUPS Groups;
+ PSID AdministratorsSid;
+ PSID PowerUsersSid;
+ SID_IDENTIFIER_AUTHORITY NtAuthority = {SECURITY_NT_AUTHORITY};
+ UINT x;
+ BOOL success;
+
+ if (!OpenProcessToken(GetCurrentProcess(), TOKEN_READ, AccessToken))
+ {
+ write_stderr(could not open process token: error code %d\n,
+ (int) GetLastError());
+ exit(1);
+ }
+
+ if (!pgwin32_get_dynamic_tokeninfo(AccessToken, TokenGroups,
+ InfoBuffer, errbuf, sizeof(errbuf)))
+ {
+
