[PATCHES] Add chklocale.c to msvc build
Hi, attached patch adds chklocale.c to pgportfiles in Mkvcbuild perl module (not really worth the .diff :)) This fixes MSVC build (see buildfarm errors with unresolved reference for _pg_get_encoding_from_locale). -- Regards Petr Jelinek (PJMODOS) Index: Mkvcbuild.pm === RCS file: /projects/cvsroot/pgsql/src/tools/msvc/Mkvcbuild.pm,v retrieving revision 1.17 diff -c -r1.17 Mkvcbuild.pm *** Mkvcbuild.pm27 Sep 2007 19:53:44 - 1.17 --- Mkvcbuild.pm29 Sep 2007 05:28:16 - *** *** 44,50 our @pgportfiles = qw( crypt.c fseeko.c getrusage.c inet_aton.c random.c srandom.c ! unsetenv.c getaddrinfo.c gettimeofday.c kill.c open.c rand.c snprintf.c strlcat.c strlcpy.c copydir.c dirmod.c exec.c noblock.c path.c pipe.c pgsleep.c pgstrcasecmp.c qsort.c qsort_arg.c sprompt.c thread.c getopt.c getopt_long.c dirent.c rint.c win32error.c); --- 44,50 our @pgportfiles = qw( crypt.c fseeko.c getrusage.c inet_aton.c random.c srandom.c ! unsetenv.c getaddrinfo.c gettimeofday.c chklocale.c kill.c open.c rand.c snprintf.c strlcat.c strlcpy.c copydir.c dirmod.c exec.c noblock.c path.c pipe.c pgsleep.c pgstrcasecmp.c qsort.c qsort_arg.c sprompt.c thread.c getopt.c getopt_long.c dirent.c rint.c win32error.c); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] ALTER TYPE RENAME
Hi,
I noticed we don't have ALTER TYPE foo RENAME TO bar command which would
be handy for me especially for enum types.
So I wrote this little patch (including very brief doc) which adds above
syntax. It basically just does some checks and calls existing TypeRename
function which is used for renaming table rowtype now.
I hope I haven't missed anything, but I am unsure about two things.
First, this patch allows renaming base types which I don't know if it's
desired. And second we might want to throw error when renaming rowtype
(there is check in AlterTypeOwner for this but not in AlterTypeNamespace
so I don't know).
--
Regards
Petr Jelinek (PJMODOS)
Index: doc/src/sgml/ref/alter_type.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_type.sgml,v
retrieving revision 1.4
diff -c -r1.4 alter_type.sgml
*** doc/src/sgml/ref/alter_type.sgml16 Sep 2006 00:30:16 - 1.4
--- doc/src/sgml/ref/alter_type.sgml29 Sep 2007 05:43:14 -
***
*** 26,31
--- 26,32
synopsis
ALTER TYPE replaceable class=PARAMETERname/replaceable OWNER TO
replaceable class=PARAMETERnew_owner/replaceable
ALTER TYPE replaceable class=PARAMETERname/replaceable SET SCHEMA
replaceable class=PARAMETERnew_schema/replaceable
+ ALTER TYPE replaceable class=PARAMETERname/replaceable RENAME TO
replaceable class=PARAMETERnew_name/replaceable
/synopsis
/refsynopsisdiv
***
*** 83,88
--- 84,98
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=PARAMETERnew_name/replaceable/term
+ listitem
+para
+ The new name for the type.
+/para
+ /listitem
+ /varlistentry
+
/variablelist
/para
/refsect1
Index: src/backend/commands/alter.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/alter.c,v
retrieving revision 1.25
diff -c -r1.25 alter.c
*** src/backend/commands/alter.c21 Aug 2007 01:11:14 - 1.25
--- src/backend/commands/alter.c29 Sep 2007 05:12:31 -
***
*** 154,159
--- 154,164
RenameTSConfiguration(stmt-object, stmt-newname);
break;
+ case OBJECT_TYPE:
+ case OBJECT_DOMAIN:
+ RenameType(stmt-object, stmt-newname);
+ break;
+
default:
elog(ERROR, unrecognized rename stmt type: %d,
(int) stmt-renameType);
Index: src/backend/commands/typecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.107
diff -c -r1.107 typecmds.c
*** src/backend/commands/typecmds.c 4 Sep 2007 16:41:42 - 1.107
--- src/backend/commands/typecmds.c 29 Sep 2007 05:11:22 -
***
*** 2514,2519
--- 2514,2567
}
/*
+ * Execute ALTER TYPE RENAME
+ */
+ void
+ RenameType(List *names, const char *newTypeName)
+ {
+ TypeName *typename;
+ Oid typeOid;
+ Relationrel;
+ HeapTuple tup;
+ Form_pg_type typTup;
+
+ /* Make a TypeName so we can use standard type lookup machinery */
+ typename = makeTypeNameFromNameList(names);
+ typeOid = typenameTypeId(NULL, typename);
+
+ /* Look up the type in the type table */
+ rel = heap_open(TypeRelationId, RowExclusiveLock);
+
+ tup = SearchSysCacheCopy(TYPEOID,
+
ObjectIdGetDatum(typeOid),
+0, 0, 0);
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, cache lookup failed for type %u, typeOid);
+ typTup = (Form_pg_type) GETSTRUCT(tup);
+
+ /* check permissions on type */
+ if (!pg_type_ownercheck(typeOid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE,
+ format_type_be(typeOid));
+
+ /* don't allow direct alteration of array types */
+ if (OidIsValid(typTup-typelem)
+ get_array_type(typTup-typelem) == typeOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+errmsg(cannot alter array type %s,
+ format_type_be(typeOid)),
+errhint(You can alter type %s, which will
alter the array type as well.,
+
format_type_be(typTup-typelem;
+
+ /* and do the work */
+ TypeRename(typeOid, newTypeName, typTup-typnamespace);
+
+ /* Clean up */
+ heap_close(rel
Re: [PATCHES] ALTER TYPE RENAME
Tom Lane wrote: BTW, another issue this brings up is whether we should reject regression=# create type footyp as (f2 int); CREATE TYPE regression=# alter table footyp rename to foobar; ALTER TABLE Currently, since there's no ALTER TYPE RENAME command, this is useful functionality and I wouldn't want to forbid it. But if we provide ALTER TYPE RENAME then consistency would suggest requiring people to use that for composite types. I assume ALTER TYPE RENAME should rename associated relation too, then. -- Regards Petr Jelinek (PJMODOS) ---(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] ALTER TYPE RENAME
Tom Lane wrote:
Hm, I'm not entirely sure if you got the point or not. For either
relations or composite types, there is both a pg_class entry and a
pg_type entry, and their names *must* stay in sync. We could allow
people to rename both entries using either ALTER TABLE or ALTER TYPE,
but the general consensus seems to be that ALTER TYPE should be used
for composite types and ALTER TABLE for tables/views/etc. The fact
that there's a pg_class entry for a composite type is really an
implementation detail that would best not be exposed to users, so
enforcing the use of the appropriate command seems reasonable to me.
regards, tom lane
Yes, that's exactly what I meant (my language skills are not best).
Anyway, I am sending second version of the patch. Changes are:
- renamed TypeRename function to RenameTypeInternal and changed its
header comment
- throw error when using ALTER TYPE to rename rowtype
- split function renamerel to RenameRelation and RenameRelationInternal
where RenameRelation does permission checks and stuff and also checks if
it's not used for composite types and RenameRelationInternal does the
actual rename. And I also did a little cleanup in those functions
(removed unused code and changed some hardcoded relkind types to globaly
predefined constants)
- RenameType now calls RenameRelationInternal for composite types
(which calls RenameTypeInternal automatically)
Any other comments ?
--
Regards
Petr Jelinek (PJMODOS)
Index: doc/src/sgml/ref/alter_type.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_type.sgml,v
retrieving revision 1.4
diff -c -r1.4 alter_type.sgml
*** doc/src/sgml/ref/alter_type.sgml16 Sep 2006 00:30:16 - 1.4
--- doc/src/sgml/ref/alter_type.sgml29 Sep 2007 05:43:14 -
***
*** 26,31
--- 26,32
synopsis
ALTER TYPE replaceable class=PARAMETERname/replaceable OWNER TO
replaceable class=PARAMETERnew_owner/replaceable
ALTER TYPE replaceable class=PARAMETERname/replaceable SET SCHEMA
replaceable class=PARAMETERnew_schema/replaceable
+ ALTER TYPE replaceable class=PARAMETERname/replaceable RNAME TO
replaceable class=PARAMETERnew_name/replaceable
/synopsis
/refsynopsisdiv
***
*** 83,88
--- 84,98
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=PARAMETERnew_name/replaceable/term
+ listitem
+para
+ The new name for the type.
+/para
+ /listitem
+ /varlistentry
+
/variablelist
/para
/refsect1
Index: src/backend/catalog/pg_type.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/pg_type.c,v
retrieving revision 1.113
diff -c -r1.113 pg_type.c
*** src/backend/catalog/pg_type.c 12 May 2007 00:54:59 - 1.113
--- src/backend/catalog/pg_type.c 30 Sep 2007 04:20:03 -
***
*** 552,566
}
/*
! * TypeRename
*This renames a type, as well as any associated array type.
*
! * Note: this isn't intended to be a user-exposed function; it doesn't check
! * permissions etc. (Perhaps TypeRenameInternal would be a better name.)
! * Currently this is only used for renaming table rowtypes.
*/
void
! TypeRename(Oid typeOid, const char *newTypeName, Oid typeNamespace)
{
Relationpg_type_desc;
HeapTuple tuple;
--- 552,567
}
/*
! * RenameTypeInternal
*This renames a type, as well as any associated array type.
*
! * Caller must have already checked privileges.
! *
! * Currently this is used for renaming table rowtypes and for
! * ALTER TYPE RENAME TO command.
*/
void
! RenameTypeInternal(Oid typeOid, const char *newTypeName, Oid typeNamespace)
{
Relationpg_type_desc;
HeapTuple tuple;
***
*** 606,612
{
char *arrname = makeArrayTypeName(newTypeName, typeNamespace);
! TypeRename(arrayOid, arrname, typeNamespace);
pfree(arrname);
}
}
--- 607,613
{
char *arrname = makeArrayTypeName(newTypeName, typeNamespace);
! RenameTypeInternal(arrayOid, arrname, typeNamespace);
pfree(arrname);
}
}
***
*** 706,712
newname = makeArrayTypeName(typeName, typeNamespace);
/* Apply the rename */
! TypeRename(typeOid, newname, typeNamespace);
/*
* We must bump the command counter so that any subsequent use of
--- 707,713
newname = makeArrayTypeName(typeName, typeNamespace);
/* Apply the rename */
! RenameTypeInternal(typeOid, newname, typeNamespace);
/*
* We must bump the command counter so that any subsequent use of
Index
Re: [PATCHES] [HACKERS] Proposed patch to getaddrinfo.c to support
Andrew Dunstan wrote: I suspected we'd forgotten something. The attached small patch appears to be what's required (at least on loris). make check failed but not for any apparent ipv6 reason. More importantly, we correctly set HAVE_IPV6 and HAVE_STRUCT_ADDRINFO. Well this is what I ment with those proposed changes - I haven't sent patch, just said whats needed - always define HAVE_IPV6 and HAVE_STRUCT_ADDRINFO and include ws2tcpip.h, if you do just that include like your patch did, you'll break building on W2k (and like I said I don't know how to make HAVE_IPV6 and HAVE_STRUCT_ADDRINFO always defined under windows because I am not familiar with configure and thats why I haven't sent patch). -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Proposed patch to getaddrinfo.c to support
Andrew Dunstan wrote: The patch I sent should be exactly what is required to have HAVE_IPV6 and HAVE_STRUCT_ADDRINFO defined on windows. That should be true regardless of which windows you are building on - the headers should be the same. Oh, if that include makes HAVE_IPV6 defined than it should be ok, I guess I just misunderstood your mail about your changes to HAVE_IPV6 check in configure under windows. Sorry for misinformation. [making...] Yes it actually builds, make check has nine failures for me but that has nothing to do with IPv6 (looks like postgres doesn't like my locale because with initdb --no-locale it passes without prob). So I hope IPv6 episode is finally over :) -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] force C locale for temp regression installations
Tom Lane weote: If you're speaking of the current buildfarm results from loris, I'm unconvinced that that's a locale problem --- the opr_sanity results in particular shouldn't be locale-sensitive. It is locale problem, I have 9 failures (like loris) with my locale and 0 failures with --no-locale Output from make cheks are at http://pjmodos.parba.cz/pgsql/locale/ and http://pjmodos.parba.cz/pgsql/no-locale/ (it's same as loris, I posted it just as evidence that --no-locale fixes all failures) -- Regards Petr Jelinek (PJMODOS) ---(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
Re: [PATCHES] [HACKERS] Proposed patch to getaddrinfo.c to support
Dave Page wrote:
It, or some related patch appears to have broken the build on buildfarm member
snake.
I haven't had time to investigate.
/D
Atached patch fixes it and also adds proper gai_strerror for windows.
(It's patch against CVS *after* Chucks patch was aplied)
--
Regards
Petr Jelinek (PJMODOS)
Index: src/include/getaddrinfo.h
===
RCS file: /projects/cvsroot/pgsql/src/include/getaddrinfo.h,v
retrieving revision 1.15
diff -c -r1.15 getaddrinfo.h
*** src/include/getaddrinfo.h 27 Jul 2005 12:44:10 - 1.15
--- src/include/getaddrinfo.h 25 Aug 2005 09:39:09 -
***
*** 30,35
--- 30,46
/* Various macros that ought to be in netdb.h, but might not be */
+ #ifdef WIN32
+ #define EAI_AGAIN WSATRY_AGAIN
+ #define EAI_BADFLAGS WSAEINVAL
+ #define EAI_FAIL WSANO_RECOVERY
+ #define EAI_FAMILYWSAEAFNOSUPPORT
+ #define EAI_MEMORYWSA_NOT_ENOUGH_MEMORY
+ #define EAI_NODATAWSANO_DATA
+ #define EAI_NONAMEWSAHOST_NOT_FOUND
+ #define EAI_SERVICE WSATYPE_NOT_FOUND
+ #define EAI_SOCKTYPE WSAESOCKTNOSUPPORT
+ #else
#ifndef EAI_FAIL
#define EAI_BADFLAGS (-1)
#define EAI_NONAME(-2)
***
*** 40,46
#define EAI_SERVICE (-8)
#define EAI_MEMORY(-10)
#define EAI_SYSTEM(-11)
! #endif
#ifndef AI_PASSIVE
#define AI_PASSIVE0x0001
--- 51,58
#define EAI_SERVICE (-8)
#define EAI_MEMORY(-10)
#define EAI_SYSTEM(-11)
! #endif /* !EAI_FAIL */
! #endif /* !WIN32 */
#ifndef AI_PASSIVE
#define AI_PASSIVE0x0001
Index: src/port/getaddrinfo.c
===
RCS file: /projects/cvsroot/pgsql/src/port/getaddrinfo.c,v
retrieving revision 1.18
diff -c -r1.18 getaddrinfo.c
*** src/port/getaddrinfo.c 24 Aug 2005 22:13:23 - 1.18
--- src/port/getaddrinfo.c 25 Aug 2005 09:39:25 -
***
*** 104,110
/* We found a dll, so now get the addresses of the routines */
getaddrinfo_ptr = GetProcAddress(hLibrary, getaddrinfo);
! freeaddrinfo_ptr = GetProcAddress(hLibrary, freeaddrinfo);
getnameinfo_ptr = GetProcAddress(hLibrary, getnameinfo);
/*
--- 104,110
/* We found a dll, so now get the addresses of the routines */
getaddrinfo_ptr = GetProcAddress(hLibrary, getaddrinfo);
! freeaddrinfo_ptr = (freeaddrinfo_ptr_t)GetProcAddress(hLibrary,
freeaddrinfo);
getnameinfo_ptr = GetProcAddress(hLibrary, getnameinfo);
/*
***
*** 277,283
*/
if (haveNativeWindowsIPv6routines())
{
! (*freeaddrinfo_ptr) (node, service, hintp, res);
return;
}
#endif
--- 277,283
*/
if (haveNativeWindowsIPv6routines())
{
! (*freeaddrinfo_ptr) (res);
return;
}
#endif
***
*** 292,298
const char *
gai_strerror(int errcode)
{
! #ifdef HAVE_HSTRERROR
int hcode;
switch (errcode)
--- 292,298
const char *
gai_strerror(int errcode)
{
! #ifdefHAVE_HSTRERROR
int hcode;
switch (errcode)
***
*** 318,323
--- 318,336
return Unknown host;
case EAI_AGAIN:
return Host name lookup failure;
+ /* Errors below are probably WIN32 only */
+ case EAI_BADFLAGS:
+ return Invalid argument;
+ case EAI_FAMILY:
+ return Address family not supported;
+ case EAI_MEMORY:
+ return Not enough memory;
+ case EAI_NODATA:
+ return No host data of that type was found;
+ case EAI_SERVICE:
+ return Class type not found;
+ case EAI_SOCKTYPE:
+ return Socket type not supported;
case EAI_FAIL:
default:
return Unknown server error;
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Proposed patch to getaddrinfo.c to support
Andrew Dunstan wrote: I thought this had been tested. I should have tested it myself. Apologies. Right, I thought I tested it, well maybe it was my version dunno but surely it was my mistake. Anyway, with Petr's extra patch I get a clean build, but make check fails with a postmaster bind failure and a pgsql failure, both with and without IPv6 installed, on my Xp-PRO SP1 box. When IPv6 is installed it complains about an unknown family 23 (which is Windows-speak for AF_INET6). So, not quite there yet. [I did make check only in W2K because I don't have direct access to XP machine now] No thats not windows error thats postgres error (look at pqcomm.c), which means HAVE_IPV6 is not defined. I think it should be made that HAVE_IPV6 and HAVE_STRUCT_ADDRINFO is always defined under windows (and also #include ws2tcpip.h in getaddrinfo.h otherwise it won't build) but I am not familiar with build system so somebody else will have to do it (I am not familiar with whole configure thingy at all). With those changes it should finally work. -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Proposed patch to getaddrinfo.c to support
Tom Lane wrote: Possibly, but that's apparently not the only problem. I'm looking at the first buildfarm result with this patch, http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lorisdt=2005-08-25%2018:56:02 The interesting part is the postmaster log at the bottom: LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 55678? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets Apparently, access to IPv4 sockets isn't working either (and the No error isn't very helpful; would seem we're not reading the right status value). /me kicks brother out of winXP machine to see whats going on Yep those changes proposed in my previous email fixes IPv4 too. LOG: database system was shut down at 2005-08-26 00:05:51 [removed unreadable chars :)] LOG: checkpoint record is at 0/390CE0 LOG: redo record is at 0/390CE0; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 562; next OID: 10791 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484144, limited by database postgres Looks ok, i don't know what IPv4 has to do with all of this though. -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] per user/database connections limit again
Peter Eisentraut wrote: GUC supports per-user/per-db values. We already had discussion here about GUC for this and we agreed that catalog change is better than new GUC variable in this case. -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] per user/database connections limit again
Here is promised documentation.
Be warned that both my writing skills and my english are far from good :)
--
Regards
Petr Jelinek (PJMODOS)
Index: doc/src/sgml/catalogs.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.109
diff -c -r2.109 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 26 Jul 2005 16:38:25 - 2.109
--- doc/src/sgml/catalogs.sgml 30 Jul 2005 18:48:10 -
***
*** 1019,1024
--- 1019,1035
/row
row
+ entrystructfieldrolconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+For roles that can login this sets maximum amount of concurrent
+connections this role can make. Default value (-1) means
+unlimited connections, zero (0) means role can't login.
+ /entry
+ /row
+
+ row
entrystructfieldrolpassword/structfield/entry
entrytypetext/type/entry
entry/entry
***
*** 1922,1927
--- 1933,1949
/row
row
+ entrystructfielddatconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+Sets maximum amount of concurrent connections that can be made
+to this database. Default value (-1) means unlimited connections,
+zero (0) means that database isn't accepting connections.
+ /entry
+ /row
+
+ row
entrystructfielddatlastsysoid/structfield/entry
entrytypeoid/type/entry
entry/entry
***
*** 4812,4817
--- 4834,4850
/row
row
+ entrystructfieldrolconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+For roles that can login this sets maximum amount of concurrent
+connections this role can make. Default value (-1) means
+unlimited connections, zero (0) means role can't login.
+ /entry
+ /row
+
+ row
entrystructfieldrolpassword/structfield/entry
entrytypetext/type/entry
entry/entry
***
*** 5094,5099
--- 5127,5143
/row
row
+ entrystructfielduseconnlimit/structfield/entry
+ entrytypeint4/type/entry
+ entry/entry
+ entry
+This sets maximum amount of concurrent connections this user can make.
+Default value (-1) means unlimited connections,
+zero (0) means user can't login.
+ /entry
+ /row
+
+ row
entrystructfieldpasswd/structfield/entry
entrytypetext/type/entry
entry/entry
Index: doc/src/sgml/ref/alter_database.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_database.sgml,v
retrieving revision 1.15
diff -c -r1.15 alter_database.sgml
*** doc/src/sgml/ref/alter_database.sgml5 Jan 2005 14:22:39 -
1.15
--- doc/src/sgml/ref/alter_database.sgml30 Jul 2005 18:48:11 -
***
*** 23,28
--- 23,34
ALTER DATABASE replaceable class=PARAMETERname/replaceable SET
replaceableparameter/replaceable { TO | = } {
replaceablevalue/replaceable | DEFAULT }
ALTER DATABASE replaceable class=PARAMETERname/replaceable RESET
replaceableparameter/replaceable
+ ALTER DATABASE replaceable class=PARAMETERname/replaceable [ [ WITH ]
replaceable class=PARAMETERoption/replaceable [ ... ] ]
+
+ where replaceable class=PARAMETERoption/replaceable can be:
+
+ CONNECTION LIMIT replaceable class=PARAMETERconnlimit/replaceable
+
ALTER DATABASE replaceable class=PARAMETERname/replaceable RENAME TO
replaceablenewname/replaceable
ALTER DATABASE replaceable class=PARAMETERname/replaceable OWNER TO
replaceablenew_owner/replaceable
***
*** 51,57
/para
para
!The third form changes the name of the database. Only the database
owner or a superuser can rename a database; non-superuser owners must
also have the
literalCREATEDB/literal privilege. The current database cannot
--- 57,68
/para
para
!The third form changes certain per-database settings. (See below for
!details.) Only database owner or superuser can change these settings.
! /para
!
! para
!The fourth form changes the name of the database. Only the database
owner or a superuser can rename a database; non-superuser owners must
also have the
literalCREATEDB/literal privilege. The current database cannot
***
*** 60,66
/para
para
!The fourth form changes the owner of the database. Only a superuser
can change the database's owner.
/para
/refsect1
--- 71,77
/para
para
!The fifth form changes the owner of the database. Only a superuser
can change the database's owner.
/para
/refsect1
Re: [PATCHES] per user/database connections limit again
Bruce Momjian wrote: I removed your use of the pg_auth flat file. By the time you have the PROC entry to do your lookups, you might as well just use the system cache. There is a race condition in the code because we set our PROC entry before we check for other entries. If there is one connection left and two backends do this at the same time, they would both fail, while one should fail and the other succeed. Without a lock, I see no way to avoid it so I just commented it in the code. Yeah my working version was doing this too but I wanted to avoid lock on PROC array and that race condition and because pg_auth is loaded anyway I used it. Also, I felt that zero should mean allow no/zero connections, rather than representing unlimited connections. I used -1 for unlimited. We can either document the use of -1, or add syntax to allow NO CONNECTION LIMIT, or something like that. Right, maybe we could remove datallowconn from pg_database (in future) if we can achieve same thing using datconnlimit = 0 ? The patch requires a catalog version update when applied. Yes, thanks for your work on this patch, I will write documentation for it in next few days. -- Regards Petr Jelinek (PJMODOS) ---(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
Re: [PATCHES] per user/database connections limit again
Bruce Momjian wrote: The new syntax for this command is CREATE/ALTER DATABASE/USER: | MAX CONNECTIONS Iconst This adds 'max' as a keyword, though at a fairly unreserved level, I think. Should we use the syntax LIMIT CONNECTIONS so we don't have to add MAX as a keyword at all? Yeah I have no problem with LIMIT CONNECTIONS, will you change it or should I do it ? btw where has new keyword to be added to not be added at a fairly unreserved level ? (MAX is also added to keywords.c in that patch) -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] per user/database connections limit again
Bruce Momjian napsal(a): I am ready to apply this patch. Would you make the additional changes you suggested? Is there any way to see the limits except to query pg_authid? Yes I will - pg_dump is already done (I attached it because it should be aplied with orginal patch), documentation depends partly on roles doc so it will prolly have to wait. I also added limit to pg_roles and pg_shadow views when I was patching pg_dump so you can get it from them. -- Regards Petr Jelinek (PJMODOS) Index: src/backend/catalog/system_views.sql === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v retrieving revision 1.16 diff -c -r1.16 system_views.sql *** src/backend/catalog/system_views.sql28 Jun 2005 05:08:52 - 1.16 --- src/backend/catalog/system_views.sql24 Jul 2005 12:22:08 - *** *** 14,19 --- 14,20 rolcreatedb, rolcatupdate, rolcanlogin, + rolmaxconn, ''::text as rolpassword, rolvaliduntil, rolconfig *** *** 26,31 --- 27,33 rolcreatedb AS usecreatedb, rolsuper AS usesuper, rolcatupdate AS usecatupd, + rolmaxconn AS usemaxconn, rolpassword AS passwd, rolvaliduntil::abstime AS valuntil, rolconfig AS useconfig Index: src/bin/pg_dump/pg_dumpall.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v retrieving revision 1.64 diff -c -r1.64 pg_dumpall.c *** src/bin/pg_dump/pg_dumpall.c18 Jul 2005 19:12:09 - 1.64 --- src/bin/pg_dump/pg_dumpall.c24 Jul 2005 12:22:35 - *** *** 394,409 PGresult *res; int i; ! if (server_version = 70100) res = executeQuery(conn, SELECT usename, usesysid, passwd, usecreatedb, ! usesuper, valuntil, (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner FROM pg_shadow); else res = executeQuery(conn, SELECT usename, usesysid, passwd, usecreatedb, ! usesuper, valuntil, (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner FROM pg_shadow); --- 394,415 PGresult *res; int i; ! if (server_version = 80100) ! res = executeQuery(conn, ! SELECT usename, usesysid, passwd, usecreatedb, ! usesuper, valuntil, usemaxconn, ! (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner ! FROM pg_shadow); ! else if (server_version = 70100) res = executeQuery(conn, SELECT usename, usesysid, passwd, usecreatedb, ! usesuper, valuntil, '0' AS usemaxconn, (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner FROM pg_shadow); else res = executeQuery(conn, SELECT usename, usesysid, passwd, usecreatedb, ! usesuper, valuntil, '0' AS usemaxconn, (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner FROM pg_shadow); *** *** 453,458 --- 459,468 appendPQExpBuffer(buf, VALID UNTIL '%s', PQgetvalue(res, i, 5)); + if (strcmp(PQgetvalue(res, i, 6), 0) != 0) + appendPQExpBuffer(buf, MAX CONNECTIONS '%s', + PQgetvalue(res, i, 6)); + appendPQExpBuffer(buf, ;\n); printf(%s, buf-data); *** *** 612,623 printf(--\n-- Database creation\n--\n\n); ! if (server_version = 8) res = executeQuery(conn
Re: [PATCHES] per user/database connections limit again
Alvaro Herrera wrote: I was wondering if there was some way to defer the user check till a later time, when the pg_authid relation could be checked? Not sure if that's a good idea, but it may help reduce the impact of the change, and thus chances that it'd be rejected. Well It can, but it would mean one more lock on procarray and I didn't want that and like I said, MyDatabaseId is read from flatfile too. Auth flatfile is used only on two other places which I also patched so I don't see this as a problem (it's used in hba.c to check role membership and in crypt.c for password verification) -- Regards Petr Jelinek (PJMODOS) ---(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
Re: [PATCHES] per user/database connections limit again
Stephen Frost wrote:
This should almost certainly be a pg_database_ownercheck() call instead.
Right there wasn't pg_database_ownercheck at the time I was writing it,
fixed
The rest needs to be updated for roles, but looks like it should be
pretty easy to do. Much of it just needs to be repatched, the parts
that do need to be changed look to be pretty simple changes.
Done.
I believe the use of SessionUserId is probably correct in this patch.
This does mean that this patch will only be for canlogin roles, but that
seems like it's probably correct. Handling roles w/ members would
require much more thought.
I don't think that having max connection for roles w/ members is doable
because you can have 5 roles which has 1 user as member and each role
has different number of max conections and there is no right way to
decide what to do.
New version which works with roles is attached (diffed against cvs),
everything else is mostly same.
I also had to readd roleid to flatfiles because I need it in
InitProcess() function.
--
Regards
Petr Jelinek (PJMODOS)
Index: src/backend/commands/dbcommands.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.164
diff -c -r1.164 dbcommands.c
*** src/backend/commands/dbcommands.c 30 Jun 2005 00:00:50 - 1.164
--- src/backend/commands/dbcommands.c 3 Jul 2005 22:47:39 -
***
*** 53,60
/* non-export function prototypes */
static bool get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
! int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
! Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId
*dbFrozenXidP,
Oid *dbTablespace);
static bool have_createdb_privilege(void);
--- 53,60
/* non-export function prototypes */
static bool get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
! int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
! bool *dbAllowConnP, Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId
*dbFrozenXidP,
Oid *dbTablespace);
static bool have_createdb_privilege(void);
***
*** 74,79
--- 74,80
int src_encoding;
boolsrc_istemplate;
boolsrc_allowconn;
+ int src_maxconn;
Oid src_lastsysoid;
TransactionId src_vacuumxid;
TransactionId src_frozenxid;
***
*** 91,100
--- 92,103
DefElem*downer = NULL;
DefElem*dtemplate = NULL;
DefElem*dencoding = NULL;
+ DefElem*dmaxconn = NULL;
char *dbname = stmt-dbname;
char *dbowner = NULL;
const char *dbtemplate = NULL;
int encoding = -1;
+ int dbmaxconn = -1;
#ifndef WIN32
charbuf[2 * MAXPGPATH + 100];
***
*** 140,145
--- 143,156
errmsg(conflicting or
redundant options)));
dencoding = defel;
}
+ else if (strcmp(defel-defname, maxconnections) == 0)
+ {
+ if (dmaxconn)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg(conflicting or
redundant options)));
+ dmaxconn = defel;
+ }
else if (strcmp(defel-defname, location) == 0)
{
ereport(WARNING,
***
*** 185,190
--- 196,203
elog(ERROR, unrecognized node type: %d,
nodeTag(dencoding-arg));
}
+ if (dmaxconn dmaxconn-arg)
+ dbmaxconn = intVal(dmaxconn-arg);
/* obtain OID of proposed owner */
if (dbowner)
***
*** 218,224
* idea, so accept possibility of race to create. We will check again
* after we grab the exclusive lock.
*/
! if (get_db_info(dbname, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_DATABASE),
--- 231,237
* idea, so accept possibility of race to create. We will check again
* after we grab the exclusive lock.
*/
! if (get_db_info(dbname, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode
Re: [PATCHES] [PATCH] fix for pg_stat_database (numbackends is always
Petr Jelinek wrote: This patch fixes pg_stat_database which is broken since this commit: http://archives.postgresql.org/pgsql-committers/2005-05/msg00126.php Neilc forgot to add new db entry inicialization to pgstat_recv_bestart() when he removed it from pgstat_add_backend() function, which causes numbackends in pg_stat_database table to be allways zero. This is my first patch for postgres so I hope I did it right. Please ignore this email - it was sent when I wasn't subscribed and we already had discrussion here about this prob. -- Regards Petr Jelinek (PJMODOS)
[PATCHES] per user/database connections limit again
Hi,
I attached second try of per-database and per-user connection limit for
your review.
This time I am using information stored in ProcArray to get number of
connections - I modified PGPROC struct to also include userid.
Limits for user and database are stored in catalog tables. This aproach
led to implementation of universal ALTER DATABASE query (I followed
ALTER USER ad ALTER DATABASE ... RENAME implementatons). So queries for
setting maximum connections look like this: CREATE|ALTER DATABASE|USER
name MAX CONNECTIONS = 20;
Maximum connections defaults to zero which means unlimited (limited by
global maximum only) and isn't enforced for superusers.
The actual check for maximum conenctions is done in ReverifyMyDatabase
for database and InitializeSessionUser for user because we don't have
information from system catalog before so we don't know how many
connections are allowed.
Patch includes only changes to backend, I will make pg_dump, ecpg and
documentation patches once this is completed and accepted by team.
Diff is made against cvs from today morning GMT (apply with -p1 if you
want to test it) - cvs is down now so I can't make diff against repository.
--
Regards
Petr Jelinek (PJMODOS)
diff -Nacr my-cvs/src/backend/commands/dbcommands.c
my-aproach2/src/backend/commands/dbcommands.c
*** my-cvs/src/backend/commands/dbcommands.cSun Jun 26 00:47:30 2005
--- my-aproach2/src/backend/commands/dbcommands.c Tue Jun 28 11:26:08 2005
***
*** 53,60
/* non-export function prototypes */
static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
! int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
! Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId
*dbFrozenXidP,
Oid *dbTablespace);
static bool have_createdb_privilege(void);
--- 53,60
/* non-export function prototypes */
static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
! int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
! bool *dbAllowConnP, Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId
*dbFrozenXidP,
Oid *dbTablespace);
static bool have_createdb_privilege(void);
***
*** 74,79
--- 74,80
int src_encoding;
boolsrc_istemplate;
boolsrc_allowconn;
+ int src_maxconn;
Oid src_lastsysoid;
TransactionId src_vacuumxid;
TransactionId src_frozenxid;
***
*** 91,100
--- 92,103
DefElem*downer = NULL;
DefElem*dtemplate = NULL;
DefElem*dencoding = NULL;
+ DefElem*dmaxconn = NULL;
char *dbname = stmt-dbname;
char *dbowner = NULL;
const char *dbtemplate = NULL;
int encoding = -1;
+ int dbmaxconn = -1;
#ifndef WIN32
charbuf[2 * MAXPGPATH + 100];
***
*** 140,145
--- 143,156
errmsg(conflicting or
redundant options)));
dencoding = defel;
}
+ else if (strcmp(defel-defname, maxconnections) == 0)
+ {
+ if (dmaxconn)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg(conflicting or
redundant options)));
+ dmaxconn = defel;
+ }
else if (strcmp(defel-defname, location) == 0)
{
ereport(WARNING,
***
*** 185,190
--- 196,203
elog(ERROR, unrecognized node type: %d,
nodeTag(dencoding-arg));
}
+ if (dmaxconn dmaxconn-arg)
+ dbmaxconn = intVal(dmaxconn-arg);
/* obtain sysid of proposed owner */
if (dbowner)
***
*** 218,224
* idea, so accept possibility of race to create. We will check again
* after we grab the exclusive lock.
*/
! if (get_db_info(dbname, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_DATABASE),
--- 231,237
* idea, so accept possibility of race to create. We will check again
* after we grab the exclusive lock.
*/
! if (get_db_info(dbname, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL))
ereport(ERROR
