Re: [PATCHES] Doc-patch: PAM authentication fails for local UNIX users
This is the continuation to the discussion that we had in the hacker's list. http://archives.postgresql.org/pgsql-hackers/2007-08/msg00684.php Here, I like to add some details in 20.2.6. PAM authentication section. http://www.postgresql.org/docs/8.2/interactive/auth-methods.html#AUTH-PAM Can someone review and make changes, if required? Thanks. Eh, those extensions are only valid if you use PAM with a shadow password file, no? You shouldn't need root if you use say PAM-with-LDAP? Also, it strikes me that granting the postgres user read access to the shadow file is probably very poor security practice, and not something I would want to recommend without considerable thought. What we should say, rather, is that PAM auth is likely to fail if your PAM is set up to use the shadow file rather than an auth source such as LDAP which does not require privileged file access. Is this change Ok? *** client-auth.sgml.orig Tue Aug 21 16:52:45 2007 --- client-auth.sgmlTue Aug 21 17:02:52 2007 *** *** 987,992 --- 987,1001 and the ulink url=http://www.sun.com/software/solaris/pam/; systemitem class=osnameSolaris/ PAM Page/ulink. /para + +note + para + If your PAM is set up to use the shadow file, the PAM authentication + is likely to fail for local UNIX users because the postgresql server + is started by a non-root user. However, this is not an issue + when LDAP or other authentication mechanism is used. + /para +/note /sect2 /sect1 ---(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] Doc-patch: PAM authentication fails for local UNIX users
Hi all, This is the continuation to the discussion that we had in the hacker's list. http://archives.postgresql.org/pgsql-hackers/2007-08/msg00684.php Here, I like to add some details in 20.2.6. PAM authentication section. http://www.postgresql.org/docs/8.2/interactive/auth-methods.html#AUTH-PAM Can someone review and make changes, if required? Thanks. *** client-auth.sgml.orig Tue Aug 21 16:52:45 2007 --- client-auth.sgmlTue Aug 21 17:02:52 2007 *** *** 987,992 --- 987,1001 and the ulink url=http://www.sun.com/software/solaris/pam/; systemitem class=osnameSolaris/ PAM Page/ulink. /para + +note + para + The local UNIX user authentication is not permitted, + because the postgres server is started by a non-root user. + In order to enable this functionality, the root user must provide + additional permissions to the postgres user (for reading /etc/shadow file). + /para +/note /sect2 /sect1 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] PAM authentication fails for local UNIX users
Hi all, This is the continuation to the discussion that we had in the hacker's list. http://www.postgresql.org/docs/8.2/interactive/auth-methods.html#AUTH-PAM Here, I like to add some details in 20.2.6. PAM authentication section. Can someone review and make changes, if required? Thanks. *** client-auth.sgml.orig Tue Aug 21 16:52:45 2007 --- client-auth.sgmlTue Aug 21 17:02:52 2007 *** *** 987,992 --- 987,1001 and the ulink url=http://www.sun.com/software/solaris/pam/; systemitem class=osnameSolaris/ PAM Page/ulink. /para + +note + para + The local UNIX user authentication is not permitted, + because the postgres server is started by a non-root user. + In order to enable this functionality, the root user must provide + additional permissions to the postgres user (for reading /etc/shadow file). + /para +/note /sect2 /sect1 Zdenek Kotala wrote: The problem what Dhanaraj tries to address is how to secure solve problem with PAM and local user. Other servers (e.g. sshd) allow to run master under root (with limited privileges) and forked process under normal user. But postgresql requires start as non-root user. It limits to used common pattern. There is important question: Is current requirement to run postgresql under non-root OK? If yes, than we must update PAM documentation to explain this situation which will never works secure. Or if we say No, it is stupid limitation (in case when UID 0 says nothing about user's privileges) then we must start discussion about solution. For now I think we should update the docs. You really can't compare postgres with sshd - ssh connections are in effect autonomous. I suspect the changes involved in allowing us to run as root and then give up privileges safely would be huge, and the gain quite small. I'd rather see an HBA fallback mechanism, which I suspect might overcome most of the problems being encountered here. cheers andrew -- Dhanaraj M x40049/+91-9880244950 Solaris RPE, Bangalore, India http://blogs.sun.com/dhanarajm/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Allow the identifier length to be increased via a
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Dhanaraj M wrote: I am sending the patch for the following TODO item: Allow the identifier length to be increased via a configure option You should use pg_config.h, not mangle postgres_ext.h like that. Or maybe generate postgres_ext.h from an hypotetical postgres_ext.h.in (but I wouldn't do that, really). I'm wondering how this got into the TODO list. It seems rather pointless, and likely to create client compatibility problems (if not, why is NAMEDATALEN exported at all?) Will this TODO item be removed from the list? Or I shall proceed with the suggestions given. Thanks Dhanaraj ---(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] Allow the identifier length to be increased via a configure option
I am sending the patch for the following TODO item: Allow the identifier length to be increased via a configure option During the configuration time, the user can set the identifier length. ./configure.txt --with-identlen=128 However, I am not clear about the requirement. Can somebody review and comment on this? Thanks Dhanaraj PS: The regression test (name.sql) needs some changes. *** configure.in.orig Wed Dec 27 14:56:10 2006 --- configure.inWed Dec 27 14:58:43 2006 *** *** 179,184 --- 179,196 AC_SUBST(default_port) # + # Identifier length ( --with-identlen), default 64 bytes + # + AC_MSG_CHECKING([for identifier length]) + PGAC_ARG_REQ(with, identlen, [ --with-identlen=INT identifier length [[64 bytes]]], + [identlen=$withval], + [identlen=64]) + AC_MSG_RESULT([$identlen]) + + sed 's/#define NAMEDATALEN .*/#define NAMEDATALEN '$identlen'/g' ./src/include/postgres_ext.h ./src/include/postgres_ext.h.tmp + mv ./src/include/postgres_ext.h.tmp ./src/include/postgres_ext.h + + # # Option to disable shared libraries # PGAC_ARG_BOOL(enable, shared, yes, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Patch - Have psql show current values
Hi all,
This patch was discussed a few months ago.
I could not complete this patch at that time.
I hope that the current version of my patch is acceptable.
Patch details:
**
1. Assign a new field called 'Seq Value' for \ds command
2. All the sequence values are '1' initially
3. After executing the query, call AssignSeqValue()
4. This function assigns the respective sequence values back to the
resultset
Please review and comment on this patch.
Thanks
Dhanaraj
Tom Lane wrote:
Dhanaraj M [EMAIL PROTECTED] writes:
However, it was not possible to display the seq. value using this.
Hence, I made a small change in the currval() function, so that it
retrieves the last_value
even if the the value is not cached.
Breaking currval()'s semantics is not an acceptable solution for this.
The best, fully backward compatible solution is for psql to issue
SELECT last_value FROM seq queries to get the values. This might
be a bit tricky to wedge into the structure of describe.c, but I don't
see any fundamental reason why it can't be done.
regards, tom lane
*** src/bin/psql/describe.c.orig Mon Aug 21 11:21:56 2006
--- src/bin/psql/describe.c Thu Aug 24 10:54:59 2006
***
*** 14,19
--- 14,20
#include settings.h
#include print.h
#include variables.h
+ #include libpq-int.h
#include ctype.h
***
*** 38,43
--- 39,45
static bool add_tablespace_footer(char relkind, Oid tablespace, char **footers,
int *count, PQExpBufferData buf, bool newline);
+ static void AssignSeqValue(PGresult *resultset);
/*
* Handlers for various slash commands displaying some sort of list
***
*** 1499,1504
--- 1501,1507
bool showSeq = strchr(tabtypes, 's') != NULL;
bool showSystem = strchr(tabtypes, 'S') != NULL;
+ int64 initialSeqValue = 1;
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
***
*** 1521,1526
--- 1524,1533
_(table), _(view), _(index), _(sequence),
_(special), _(Type), _(Owner));
+ if ((showSeq) (!showTables))
+ appendPQExpBuffer(buf, ,\n INT64_FORMAT as \%s\,
+ initialSeqValue, _(Seq Value));
+
if (showIndexes)
appendPQExpBuffer(buf,
,\n c2.relname as \%s\,
***
*** 1587,1592
--- 1594,1602
myopt.nullPrint = NULL;
myopt.title = _(List of relations);
+ if ((showSeq) (!showTables))
+ AssignSeqValue(res);
+
printQuery(res, myopt, pset.queryFout, pset.logfile);
}
***
*** 1594,1599
--- 1604,1641
return true;
}
+
+ /*
+ * \ds
+ *
+ * Assign the respective sequence value.
+ */
+ static void AssignSeqValue(PGresult *resultset)
+ {
+ int i, rows, nfields;
+ PQExpBufferData buf;
+ PGresult *seqValue;
+
+ rows = PQntuples(resultset);
+ nfields = PQnfields(resultset);
+
+ /*
+ * Execute the select query to get the sequence value for each sequence separately,
+ * by using the retrieved sequence names from the second field of resultset.
+ * Re-assign the respective sequence values to the last field of resultset.
+ */
+ for(i=0; irows; i++)
+ {
+ initPQExpBuffer(buf);
+ printfPQExpBuffer(buf, select last_value from %s, PQgetvalue(resultset, i, 1));
+
+ seqValue = PSQLexec(buf.data, false);
+ termPQExpBuffer(buf);
+ strcpy(resultset-tuples[i][nfields-1].value, seqValue-tuples[0][0].value);
+ PQclear(seqValue);
+ }
+ }
+
/*
* \dD
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] Patch for - Change FETCH/MOVE to use int8
Hi Alvaro
Thanks for your valuable suggestions.
I made the changes as suggested earlier.
Please review again and comment on this.
I like to make changes if it is required.
*** ./src/backend/commands/portalcmds.c.orig Sat Aug 12 23:04:54 2006
--- ./src/backend/commands/portalcmds.c Fri Aug 18 22:52:05 2006
***
*** 176,183
char *completionTag)
{
Portal portal;
! long nprocessed;
/*
* Disallow empty-string cursor name (conflicts with protocol-level
* unnamed portal).
--- 176,183
char *completionTag)
{
Portal portal;
! int64 nprocessed;
/*
* Disallow empty-string cursor name (conflicts with protocol-level
* unnamed portal).
***
*** 209,215
/* Return command status if wanted */
if (completionTag)
! snprintf(completionTag, COMPLETION_TAG_BUFSIZE, %s %ld,
stmt-ismove ? MOVE : FETCH,
nprocessed);
}
--- 209,215
/* Return command status if wanted */
if (completionTag)
! snprintf(completionTag, COMPLETION_TAG_BUFSIZE, %s INT64_FORMAT,
stmt-ismove ? MOVE : FETCH,
nprocessed);
}
*** ./src/backend/executor/spi.c.orig Sat Aug 12 23:04:55 2006
--- ./src/backend/executor/spi.c Fri Aug 18 02:14:20 2006
***
*** 45,51
static void _SPI_error_callback(void *arg);
! static void _SPI_cursor_operation(Portal portal, bool forward, long count,
DestReceiver *dest);
static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
--- 45,51
static void _SPI_error_callback(void *arg);
! static void _SPI_cursor_operation(Portal portal, bool forward, int64 count,
DestReceiver *dest);
static _SPI_plan *_SPI_copy_plan(_SPI_plan *plan, int location);
***
*** 980,986
* Fetch rows in a cursor
*/
void
! SPI_cursor_fetch(Portal portal, bool forward, long count)
{
_SPI_cursor_operation(portal, forward, count,
CreateDestReceiver(DestSPI, NULL));
--- 980,986
* Fetch rows in a cursor
*/
void
! SPI_cursor_fetch(Portal portal, bool forward, int64 count)
{
_SPI_cursor_operation(portal, forward, count,
CreateDestReceiver(DestSPI, NULL));
***
*** 994,1000
* Move in a cursor
*/
void
! SPI_cursor_move(Portal portal, bool forward, long count)
{
_SPI_cursor_operation(portal, forward, count, None_Receiver);
}
--- 994,1000
* Move in a cursor
*/
void
! SPI_cursor_move(Portal portal, bool forward, int64 count)
{
_SPI_cursor_operation(portal, forward, count, None_Receiver);
}
***
*** 1611,1620
* Do a FETCH or MOVE in a cursor
*/
static void
! _SPI_cursor_operation(Portal portal, bool forward, long count,
DestReceiver *dest)
{
! long nfetched;
/* Check that the portal is valid */
if (!PortalIsValid(portal))
--- 1611,1620
* Do a FETCH or MOVE in a cursor
*/
static void
! _SPI_cursor_operation(Portal portal, bool forward, int64 count,
DestReceiver *dest)
{
! int64 nfetched;
/* Check that the portal is valid */
if (!PortalIsValid(portal))
*** ./src/backend/parser/gram.y.orig Fri Aug 18 23:37:43 2006
--- ./src/backend/parser/gram.y Fri Aug 18 01:12:58 2006
***
*** 117,122
--- 117,123
%union
{
intival;
+ int64i64val;
charchr;
char*str;
const char *keyword;
***
*** 323,328
--- 324,330
%type boolean opt_varying opt_timezone
%type ival Iconst SignedIconst
+ %type i64val SignedI64const
%type str Sconst comment_text
%type str RoleId opt_granted_by opt_boolean ColId_or_Sconst
%type list var_list var_list_or_default
***
*** 446,451
--- 448,454
/* Special token types, not actually keywords - see the lex file */
%token str IDENT FCONST SCONST BCONST XCONST Op
%token ival ICONST PARAM
+ %token i64val I64CONST
/* precedence: lowest to highest */
%nonassoc SET/* see relation_expr_opt_alias */
***
*** 3334,3339
--- 3337,3363
n-howMany = $1;
$$ = (Node *)n;
}
+ | ABSOLUTE_P SignedI64const
+ {
+ FetchStmt *n = makeNode(FetchStmt);
+ n-direction = FETCH_ABSOLUTE;
+ n-howMany = $2;
+ $$ = (Node *)n;
+ }
+ | RELATIVE_P SignedI64const
+ {
+ FetchStmt *n = makeNode(FetchStmt);
+ n-direction = FETCH_RELATIVE;
+ n-howMany = $2;
+ $$ = (Node *)n;
+ }
+ | SignedI64const
+ {
+
[PATCHES] Patch for - Change FETCH/MOVE to use int8
This patch is for the following TODO item.
SQL command:
-/Change LIMIT/OFFSET and FETCH/MOVE to use int8
/Since the limit/offset patch is already applied,
this patch is meant for Fetch/Move query.
I have tested the patch and it works for int64 values.
Please verify this.
Thanks
Dhanaraj
/
/
*** ./src/backend/commands/portalcmds.c.orig Sat Aug 12 23:04:54 2006
--- ./src/backend/commands/portalcmds.c Sat Aug 12 23:04:53 2006
***
*** 176,183
char *completionTag)
{
Portal portal;
! long nprocessed;
/*
* Disallow empty-string cursor name (conflicts with protocol-level
* unnamed portal).
--- 176,183
char *completionTag)
{
Portal portal;
! int64 nprocessed;
/*
* Disallow empty-string cursor name (conflicts with protocol-level
* unnamed portal).
***
*** 209,215
/* Return command status if wanted */
if (completionTag)
! snprintf(completionTag, COMPLETION_TAG_BUFSIZE, %s %ld,
stmt-ismove ? MOVE : FETCH,
nprocessed);
}
--- 209,215
/* Return command status if wanted */
if (completionTag)
! snprintf(completionTag, COMPLETION_TAG_BUFSIZE, %s %lld,
stmt-ismove ? MOVE : FETCH,
nprocessed);
}
*** ./src/backend/parser/gram.y.orig Sat Aug 12 23:04:57 2006
--- ./src/backend/parser/gram.y Sun Aug 13 00:06:28 2006
***
*** 116,122
%union
{
! int ival;
charchr;
char*str;
const char *keyword;
--- 116,122
%union
{
! int64ival;
charchr;
char*str;
const char *keyword;
***
*** 1180,1192
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision must not be negative,
! $3)));
if ($3 MAX_INTERVAL_PRECISION)
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision reduced to maximum allowed, %d,
! $3, MAX_INTERVAL_PRECISION)));
$3 = MAX_INTERVAL_PRECISION;
}
--- 1180,1192
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision must not be negative,
! (int)$3)));
if ($3 MAX_INTERVAL_PRECISION)
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision reduced to maximum allowed, %d,
! (int)$3, MAX_INTERVAL_PRECISION)));
$3 = MAX_INTERVAL_PRECISION;
}
***
*** 2620,2626
ICONST
{
char buf[64];
! snprintf(buf, sizeof(buf), %d, $1);
$$ = makeString(pstrdup(buf));
}
| FCONST{ $$ = makeString($1); }
--- 2620,2626
ICONST
{
char buf[64];
! snprintf(buf, sizeof(buf), %d, (int)$1);
$$ = makeString(pstrdup(buf));
}
| FCONST{ $$ = makeString($1); }
***
*** 6281,6293
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision must not be negative,
! $3)));
if ($3 MAX_INTERVAL_PRECISION)
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision reduced to maximum allowed, %d,
! $3, MAX_INTERVAL_PRECISION)));
$3 = MAX_INTERVAL_PRECISION;
}
$$-typmod = INTERVAL_TYPMOD($3, $5);
--- 6281,6293
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision must not be negative,
! (int)$3)));
if ($3 MAX_INTERVAL_PRECISION)
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(INTERVAL(%d) precision reduced to maximum allowed, %d,
! (int)$3, MAX_INTERVAL_PRECISION)));
$3 = MAX_INTERVAL_PRECISION;
}
$$-typmod = INTERVAL_TYPMOD($3, $5);
***
*** 6408,6419
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(NUMERIC precision %d must be between 1 and %d,
! $2, NUMERIC_MAX_PRECISION)));
if ($4 0 || $4 $2)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(NUMERIC scale %d must be between 0 and precision %d,
! $4, $2)));
$$ = (($2 16) | $4) + VARHDRSZ;
}
--- 6408,6419
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(NUMERIC precision %d must be between 1 and %d,
! (int)$2, NUMERIC_MAX_PRECISION)));
if ($4 0 || $4 $2)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(NUMERIC scale %d must be between 0 and precision %d,
! (int)$4, (int)$2)));
$$ = (($2 16) | $4) + VARHDRSZ;
}
***
*** 6423,6429
[Fwd: Re: [PATCHES] Patch for - Change LIMIT/OFFSET to use int8]
I sent this patch already.
Can somebody verify this patch?
Thanks
Dhanaraj
---BeginMessage---
I have made the changes appropriately. The regression tests passed.
Since I do not have enough resources, I could not test for a large number.
It works for a small table. If anybody tests for int8 value, it is
appreciated.
Also, it gives the following error msg, when the input exceeds the int8
limit.
ERROR: bigint out of range
I attach the patch. Pl. check it.
Thanks
Dhanaraj
Tom Lane wrote:
Dhanaraj M [EMAIL PROTECTED] writes:
I attach the patch for the following TODO item.
SQL COMMAND
* Change LIMIT/OFFSET to use int8
This can't possibly be correct. It doesn't even change the field types
in struct LimitState, for example. You've missed half a dozen places
in the planner that would need work, too.
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
*** ./src/backend/executor/nodeLimit.c.orig Tue Jul 11 22:31:51 2006
--- ./src/backend/executor/nodeLimit.c Wed Jul 12 00:46:11 2006
***
*** 23,28
--- 23,29
#include executor/executor.h
#include executor/nodeLimit.h
+ #include catalog/pg_type.h
static void recompute_limits(LimitState *node);
***
*** 226,239
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
- if (node-limitOffset)
- {
- node-offset =
-
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
-
econtext,
-
isNull,
-
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
--- 227,251
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
+ Oid type;
+
+ if (node-limitOffset)
+ {
+ type = ((Const *) node-limitOffset-expr)-consttype;
+
+ if(type == INT8OID)
+ node-offset =
+
DatumGetInt64(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
+ else
+ node-offset =
+
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
***
*** 249,259
if (node-limitCount)
{
node-noCount = false;
! node-count =
!
DatumGetInt32(ExecEvalExprSwitchContext(node-limitCount,
!
econtext,
!
isNull,
!
NULL));
/* Interpret NULL count as no count (LIMIT ALL) */
if (isNull)
node-noCount = true;
--- 261,282
if (node-limitCount)
{
node-noCount = false;
! type = ((Const *) node-limitCount-expr)-consttype;
!
! if(type == INT8OID)
! node-count =
!
DatumGetInt64(ExecEvalExprSwitchContext(node-limitCount,
!
econtext,
!
isNull,
!
NULL));
! else
Re: [PATCHES] Patch for - Change LIMIT/OFFSET to use int8
I have made the changes appropriately. The regression tests passed.
Since I do not have enough resources, I could not test for a large number.
It works for a small table. If anybody tests for int8 value, it is
appreciated.
Also, it gives the following error msg, when the input exceeds the int8
limit.
ERROR: bigint out of range
I attach the patch. Pl. check it.
Thanks
Dhanaraj
Tom Lane wrote:
Dhanaraj M [EMAIL PROTECTED] writes:
I attach the patch for the following TODO item.
SQL COMMAND
* Change LIMIT/OFFSET to use int8
This can't possibly be correct. It doesn't even change the field types
in struct LimitState, for example. You've missed half a dozen places
in the planner that would need work, too.
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
*** ./src/backend/executor/nodeLimit.c.orig Tue Jul 11 22:31:51 2006
--- ./src/backend/executor/nodeLimit.c Wed Jul 12 00:46:11 2006
***
*** 23,28
--- 23,29
#include executor/executor.h
#include executor/nodeLimit.h
+ #include catalog/pg_type.h
static void recompute_limits(LimitState *node);
***
*** 226,239
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
- if (node-limitOffset)
- {
- node-offset =
-
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
-
econtext,
-
isNull,
-
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
--- 227,251
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
+ Oid type;
+
+ if (node-limitOffset)
+ {
+ type = ((Const *) node-limitOffset-expr)-consttype;
+
+ if(type == INT8OID)
+ node-offset =
+
DatumGetInt64(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
+ else
+ node-offset =
+
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
***
*** 249,259
if (node-limitCount)
{
node-noCount = false;
! node-count =
!
DatumGetInt32(ExecEvalExprSwitchContext(node-limitCount,
!
econtext,
!
isNull,
!
NULL));
/* Interpret NULL count as no count (LIMIT ALL) */
if (isNull)
node-noCount = true;
--- 261,282
if (node-limitCount)
{
node-noCount = false;
! type = ((Const *) node-limitCount-expr)-consttype;
!
! if(type == INT8OID)
! node-count =
!
DatumGetInt64(ExecEvalExprSwitchContext(node-limitCount,
!
econtext,
!
isNull,
!
NULL));
! else
! node-count =
!
DatumGetInt32(ExecEvalExprSwitchContext
[PATCHES] Patch for - Change LIMIT/OFFSET to use int8
I attach the patch for the following TODO item.
SQL COMMAND
* Change LIMIT/OFFSET to use int8
It passes all the regression tests and supports int8.
I am waiting for your review.
Thanks
Dhanaraj
*** ./src/backend/executor/nodeLimit.c.orig Sun Jun 25 15:02:46 2006
--- ./src/backend/executor/nodeLimit.c Mon Jun 26 14:31:04 2006
***
*** 23,29
#include executor/executor.h
#include executor/nodeLimit.h
!
static void recompute_limits(LimitState *node);
--- 23,29
#include executor/executor.h
#include executor/nodeLimit.h
! #include catalog/pg_type.h
static void recompute_limits(LimitState *node);
***
*** 226,239
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
if (node-limitOffset)
{
! node-offset =
!
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
econtext,
isNull,
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
--- 226,250
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
+ Oid type;
if (node-limitOffset)
{
! type = ((Const *) node-limitOffset-expr)-consttype;
!
! if(type == INT8OID)
! node-offset =
!
DatumGetInt64(ExecEvalExprSwitchContext(node-limitOffset,
econtext,
isNull,
NULL));
+ else
+ node-offset =
+
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
+
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
***
*** 249,259
if (node-limitCount)
{
node-noCount = false;
! node-count =
!
DatumGetInt32(ExecEvalExprSwitchContext(node-limitCount,
econtext,
isNull,
NULL));
/* Interpret NULL count as no count (LIMIT ALL) */
if (isNull)
node-noCount = true;
--- 260,280
if (node-limitCount)
{
node-noCount = false;
! type = ((Const *) node-limitCount-expr)-consttype;
!
! if(type == INT8OID)
! node-count =
!
DatumGetInt64(ExecEvalExprSwitchContext(node-limitCount,
econtext,
isNull,
NULL));
+ else
+ node-count =
+
DatumGetInt32(ExecEvalExprSwitchContext(node-limitCount,
+
econtext,
+
isNull,
+
NULL));
+
/* Interpret NULL count as no count (LIMIT ALL) */
if (isNull)
node-noCount = true;
*** ./src/backend/parser/parse_clause.c.origSun Jun 25 14:55:42 2006
--- ./src/backend/parser/parse_clause.c Mon Jun 26 14:50:28 2006
***
*** 1092,1098
qual = transformExpr(pstate, clause);
[PATCHES] Patch for - Allow server logs to be remotely read
The patch is attached for the following TODO item:
*Allow server logs to be remotely read.
Steps:
1. When the server starts (**pg_ctl start)**, the path of the postmaster file is stored
2. The user can access the logfile using the following 2 functions:
* pg_file_readlog(int linenumber) - Retrieves the string for the given line number
* **pg_file_countlog() - Retrieves the number of lines existing in the
logfile*
I have implemented this based on the suggestions given in the hackers mailing
list.
If you know a better way, please share it with me. Waiting for your reply.
Thanks
Dhanaraj
*** ./contrib/adminpack/adminpack.c.orig Thu Jun 8 17:04:42 2006
--- ./contrib/adminpack/adminpack.c Thu Jun 8 18:34:37 2006
***
*** 24,30
--- 24,32
#include catalog/pg_type.h
#include funcapi.h
#include utils/datetime.h
+ #include utils/builtins.h
+ #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#ifdef WIN32
***
*** 48,58
--- 50,64
Datum pg_file_rename(PG_FUNCTION_ARGS);
Datum pg_file_unlink(PG_FUNCTION_ARGS);
Datum pg_logdir_ls(PG_FUNCTION_ARGS);
+ Datum pg_file_readlog(PG_FUNCTION_ARGS);
+ Datum pg_file_countlog();
PG_FUNCTION_INFO_V1(pg_file_write);
PG_FUNCTION_INFO_V1(pg_file_rename);
PG_FUNCTION_INFO_V1(pg_file_unlink);
PG_FUNCTION_INFO_V1(pg_logdir_ls);
+ PG_FUNCTION_INFO_V1(pg_file_readlog);
+ PG_FUNCTION_INFO_V1(pg_file_countlog);
typedef struct
{
***
*** 390,392
--- 396,520
FreeDir(fctx-dirdesc);
SRF_RETURN_DONE(funcctx);
}
+
+ /* Retrieves n-th line from the postmaster logfile */
+
+ Datum pg_file_readlog(PG_FUNCTION_ARGS)
+ {
+ char c, buffer[MAXPGPATH];
+ char file_path2[MAXPGPATH], file_path1[MAXPGPATH];
+ int64 linenumber = PG_GETARG_INT64(0);
+ FILE *filePtr1, *filePtr2;
+ int64 count=1;
+ int index=0;
+
+ if(OutputFileName[0])
+ {
+ filePtr1 = fopen(OutputFileName, r);
+ }
+ else
+ {
+ /* Finds the logfile path from postmasterlog_path file */
+ snprintf(file_path1, MAXPGPATH, %s/postmasterlog_path, DataDir);
+ filePtr2 = fopen(file_path1, r);
+ if(filePtr2 != NULL)
+ {
+ memset(file_path2, 0, MAXPGPATH);
+ fread(file_path2, 1, MAXPGPATH, filePtr2);
+ }
+ else
+ {
+ snprintf(buffer, MAXPGPATH, Could not find the logfile path in: %s, file_path1);
+ ereport(ERROR, (errcode_for_file_access(), errmsg(buffer)));
+ }
+
+ fclose(filePtr2);
+ filePtr1 = fopen(file_path2, r);
+ }
+
+ memset(buffer, 0, MAXPGPATH);
+
+ if(filePtr1 == NULL)
+ {
+ snprintf(buffer, MAXPGPATH, Logfile: %s - Not found, file_path2);
+ ereport(ERROR, (errcode_for_file_access(), errmsg(buffer)));
+ }
+
+ /* Reads the logfile and extract n-th line */
+ while( (c= fgetc(filePtr1))!=EOF)
+ {
+ if (linenumber == count)
+ {
+ buffer[index] = c;
+ index++;
+ }
+
+ /* Counts the number of lines */
+ if(c=='\n')
+{
+ count++;
+ }
+ }
+
+ if(count = linenumber)
+ {
+ sprintf(buffer, Given line number does not exist);
+ ereport(ERROR, (errcode_for_file_access(), errmsg(buffer)));
+ }
+
+ fclose(filePtr1);
+ PG_RETURN_TEXT_P(GET_TEXT(buffer));
+ }
+
+ /* Counts the number of lines in the postmaster logfile */
+
+ Datum pg_file_countlog()
+ {
+ char c, buffer[MAXPGPATH];
+ char file_path2[MAXPGPATH], file_path1[MAXPGPATH];
+ FILE *filePtr1, *filePtr2;
+ int64 count=1;
+
+ if(OutputFileName[0])
+ {
+ filePtr1 = fopen(OutputFileName, r);
+ }
+ else
+ {
+ /* Finds the logfile path from postmasterlog_path file */
+ snprintf(file_path1, MAXPGPATH, %s/postmasterlog_path, DataDir);
+ filePtr2 = fopen(file_path1, r);
+ if(filePtr2 != NULL)
+ {
+ memset(file_path2, 0, MAXPGPATH);
+ fread(file_path2, 1, MAXPGPATH, filePtr2);
+ }
+ else
+ {
+ snprintf(buffer, MAXPGPATH, Could not find the logfile path in: %s, file_path1);
+ ereport(ERROR, (errcode_for_file_access(), errmsg(buffer)));
+ }
+
+ fclose(filePtr2);
+ filePtr1 = fopen(file_path2, r);
+ }
+
+ memset(buffer, 0, MAXPGPATH);
+
+ if(filePtr1== NULL)
+ {
+ snprintf(buffer, MAXPGPATH, Logfile: %s - Not found, file_path2);
+ ereport(ERROR, (errcode_for_file_access(), errmsg(buffer)));
+ }
+
+ /* Counts the number of lines */
+ while( (c= fgetc(filePtr1))!=EOF)
+ {
+ if(c=='\n')
+ count++;
+ }
+
+ fclose(filePtr1);
+ PG_RETURN_INT64(count-1);
+ }
+
*** ./contrib/adminpack/adminpack.sql.in.orig Thu Jun 8 17:05:16 2006
--- ./contrib/adminpack/adminpack.sql.in Thu Jun 8 17:06:11 2006
***
*** 24,30
--- 24,37
AS 'MODULE_PATHNAME', 'pg_logdir_ls'
LANGUAGE C VOLATILE STRICT;
+ CREATE FUNCTION pg_catalog.pg_file_readlog(bigint) RETURNS text
+
Re: [PATCHES] Patch for - Allow server logs to be remotely read
Bruce Momjian wrote: Uh, I just added /contrib/adminpack a few weeks ago to CVS, which does this, and more. Sorry I forgot to mark the TODO item as completed. --- 1. int8 pg_catalog.pg_file_read(fname text, data text, append bool) Though the above pg_file_read provides an interface to read the files, the admin has to know the complete file path in order to read from them. This can be simplified. As I have implemented. it does not take the file name as a parameter. It automatically stored the postmaster file path and reads whenever required. 2. As suggested in the mailing list by Tom lane, this feature is implemented in contrib pkg. Hence, this feature is not forced on all. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: current version: [PATCHES] Patch - Have psql show current values
Bruce Momjian wrote:
I am thinking we just add another column to the \d display for sequences
showing the current value.
---
As suggested in the previous mails, I tried to use the following to
display the seq. value.
select last_value from seq.
However, it was not possible to display the seq. value using this.
Hence, I made a small change in the currval() function, so that it
retrieves the last_value
even if the the value is not cached.
I hope this patch will be more suitable for this issue. Pl. look at the
patch.
Thanks
Dhanaraj
*** ./src/backend/commands/sequence.c.orig Tue May 2 14:51:03 2006
--- ./src/backend/commands/sequence.c Tue May 9 13:52:38 2006
***
*** 605,610
--- 605,612
int64 result;
SeqTable elm;
Relation seqrel;
+ Form_pg_sequence seq;
+ Buffer buf;
/* open and AccessShareLock sequence */
init_sequence(relid, elm, seqrel);
***
*** 616,632
errmsg(permission denied for sequence %s,
RelationGetRelationName(seqrel;
! if (elm-increment == 0) /* nextval/read_info were not called */
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg(currval of sequence \%s\ is not yet defined in this session,
! RelationGetRelationName(seqrel;
! result = elm-last;
! relation_close(seqrel, NoLock);
! PG_RETURN_INT64(result);
}
Datum
--- 618,641
errmsg(permission denied for sequence %s,
RelationGetRelationName(seqrel;
! if ((elm-increment != 0) ||(elm-last != elm-cached)) /* some numbers were cached */
! {
! result = elm-last;
! relation_close(seqrel, NoLock);
! PG_RETURN_INT64(result);
! }
! /* lock page' buffer and read tuple if not cached */
! seq = read_info(elm, seqrel, buf);
! result = seq-last_value;
! UnlockReleaseBuffer(buf);
! relation_close(seqrel, NoLock);
! seqtab = elm-next;
! free(elm);
!
! PG_RETURN_INT64(result);
}
Datum
*** ./src/bin/psql/describe.c.orig Thu Apr 27 04:45:45 2006
--- ./src/bin/psql/describe.c Tue May 9 16:26:10 2006
***
*** 1480,1485
--- 1480,1488
_(table), _(view), _(index), _(sequence),
_(special), _(Type), _(Owner));
+ if (showSeq !showTables)
+ appendPQExpBuffer(buf,,\n currval(CAST(c.relname AS pg_catalog.text)) as \%s\,_(value));
+
if (showIndexes)
appendPQExpBuffer(buf,
,\n c2.relname as \%s\,
No differences encountered
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: current version: [PATCHES] Patch - Have psql show current values
Tom Lane wrote: Dhanaraj M [EMAIL PROTECTED] writes: sorry for sending the old version in the previous mail . Here I attach the recent version of the patch file. -- Surely this problem does not require adding any server-side code. Something like select last_value from seq would be more appropriate; and it'd have some hope of working with back-version servers. Also, please use something more helpful than *** as the column header. Your urge to add a footnote to explain it shows that you didn't try hard enough to devise a good header to begin with. [ btw, both fmgroids.h and fmgrtab.c are generated files. Patching them is unnecessary and inappropriate. ] -- The existing functions like lastval, currval dont provide the current sequence value always. They work only if the sequence is already cached (nextval is called atleast once for that sequence). Changing the internals of lastval/currval will give the solution. However, I feel that the functionality change may affect the customers who use the current version. Hence, I am sure that it requires the server side change. There are two options here 1. Modifying the exisitng functions (or) 2. adding new functions Thanks for your review Dhanaraj ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
current version: [PATCHES] Patch - Have psql show current values for a sequence]
---BeginMessage---
sorry for sending the old version in the previous mail . Here I attach
the recent version of the patch file.
Dhanaraj M wrote:
I saw the following in the TODO list.
clients-psql
1. Have psql show current values for a sequence
Hence, this patch displays the current seq. value in a separate column
when \ds is executed.
I attach the patch here. The display format may have to be changed. I
would like to change the display format as you will suggest me.
I am awaiting for the review.
Thanks
Dhanaraj
*** ./src/backend/commands/sequence.c.orig Tue May 2 14:51:03 2006
--- ./src/backend/commands/sequence.c Tue May 2 15:00:19 2006
***
*** 1183,1185
--- 1183,1274
xlrec-node.spcNode, xlrec-node.dbNode, xlrec-node.relNode);
}
+
+ /* Returns the current sequence value even if not available in the cache */
+ Datum
+ retrieveval_oid(PG_FUNCTION_ARGS)
+ {
+
+ Oid relid = PG_GETARG_DATUM(0);
+ int64 result;
+ SeqTableelm;
+ Relationseqrel;
+ Form_pg_sequence seq;
+ Buffer buf;
+
+ /* open and AccessShareLock sequence */
+ init_sequence(relid, elm, seqrel);
+
+ if (pg_class_aclcheck(elm-relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK
+ pg_class_aclcheck(elm-relid, GetUserId(), ACL_USAGE) != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg(permission denied for sequence %s,
+ RelationGetRelationName(seqrel;
+
+
+ if ((elm-increment != 0) ||(elm-last != elm-cached)) /* some numbers were cached */
+ {
+ result = elm-last;
+ relation_close(seqrel, NoLock);
+ PG_RETURN_INT64(result);
+ }
+
+ /* lock page' buffer and read tuple if not cached */
+ seq = read_info(elm, seqrel, buf);
+ result = seq-last_value;
+
+ UnlockReleaseBuffer(buf);
+ relation_close(seqrel, NoLock);
+
+ seqtab = elm-next;
+ free(elm);
+
+ PG_RETURN_INT64(result);
+ }
+
+
+ /* Checks whether the sequence value is already used or not */
+ Datum
+ retrievecheck_oid(PG_FUNCTION_ARGS)
+ {
+
+ Oid relid = PG_GETARG_DATUM(0);
+ SeqTableelm;
+ Relationseqrel;
+ Form_pg_sequence seq;
+ Buffer buf;
+ boolresult;
+
+ /* open and AccessShareLock sequence */
+ init_sequence(relid, elm, seqrel);
+
+ if (pg_class_aclcheck(elm-relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK
+ pg_class_aclcheck(elm-relid, GetUserId(), ACL_USAGE) != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg(permission denied for sequence %s,
+ RelationGetRelationName(seqrel;
+
+
+ if ((elm-increment != 0) ||(elm-last != elm-cached)) /* some numbers were cached */
+ {
+ relation_close(seqrel, NoLock);
+ PG_RETURN_INT64(0);
+ }
+
+ /* lock page' buffer and read tuple */
+ seq = read_info(elm, seqrel, buf);
+ result = seq-is_called;
+
+ UnlockReleaseBuffer(buf);
+ relation_close(seqrel, NoLock);
+
+ seqtab = elm-next;
+ free(elm);
+
+ if (result == true)
+ PG_RETURN_INT64(0);
+ else
+ PG_RETURN_INT64(1);
+ }
*** ./src/backend/utils/fmgroids.h.orig Tue May 2 14:19:25 2006
--- ./src/backend/utils/fmgroids.h Tue May 2 15:11:08 2006
***
*** 901,906
--- 901,908
#define F_NEXTVAL_OID 1574
#define F_CURRVAL_OID 1575
#define F_SETVAL_OID 1576
+ #define F_RETRIEVEVAL_OID 1577
+ #define F_RETRIEVECHECK_OID 1578
#define F_VARBIT_IN 1579
#define F_VARBIT_OUT 1580
#define F_BITEQ 1581
*** ./src/backend/utils/fmgrtab.c.orig Tue May 2 14:19:25 2006
--- ./src/backend/utils/fmgrtab.c Tue May 2 15:11:08 2006
***
*** 952,957
--- 952,959
extern Datum nextval_oid (PG_FUNCTION_ARGS);
extern Datum currval_oid (PG_FUNCTION_ARGS);
extern Datum setval_oid (PG_FUNCTION_ARGS);
+ extern Datum retrieveval_oid (PG_FUNCTION_ARGS);
+ extern Datum retrievecheck_oid (PG_FUNCTION_ARGS);
extern Datum varbit_in (PG_FUNCTION_ARGS);
extern Datum varbit_out (PG_FUNCTION_ARGS);
extern Datum biteq (PG_FUNCTION_ARGS);
***
*** 2661,2666
--- 2663,2670
{ 1574, nextval_oid, 1, true, false, nextval_oid },
{ 1575, currval_oid, 1, true, false, currval_oid },
{ 1576, setval_oid, 2, true, false, setval_oid },
+ { 1577, retrieveval_oid, 1, true, false
[PATCHES] Patch for BUG #2073: Can't drop sequence when created via SERIAL column
Hi
I send the appropriate patch for bug #2073. This fix disallows to change the
default sequence.
I ran the regression test and passed. The bug details are given below.
I am awaiting to answer for any further clarifications.
===
Bug reference: 2073
Logged by: Aaron Dummer
Email address: aaron ( at ) dummer ( dot ) info
PostgreSQL version: 8.0.3
Operating system: Debian Linux
Description:Can't drop sequence when created via SERIAL column
Details:
If I create a table named foo with a column named bar, column type SERIAL,
it auto-generates a sequence named foo_bar_seq. Now if I manually create a
new sequence called custom_seq, and change the default value of foo.bar to
reference the new sequence, I still can't delete the old sequence
(foo_bar_seq).
In other words, from a user's point of view, the foo table is no longer
dependent on the foo_bar_seq, yet the system still sees it as dependent.
I brought this topic up on the #postgresql IRC channel and the behavior was
confirmed by AndrewSN, scampbell_, and mastermind.
Right. We have this TODO item:
* %Disallow changing default expression of a SERIAL column?
which would prevent you from changing the default expression for a
SERIAL column. So the answer is, don't do that, and in the future, we
might prevent it.
--
Bruce Momjian
==
*** ./src/backend/catalog/dependency.c.orig Wed Apr 26 10:54:40 2006
--- ./src/backend/catalog/dependency.c Wed Apr 26 12:09:01 2006
***
*** 1931,1933
--- 1931,2019
ReleaseSysCache(relTup);
}
+
+ /* Recursively travel and search for the default sequence. Finally detach it */
+
+ void performSequenceDefaultDeletion(const ObjectAddress *object,
+ DropBehavior behavior, int deleteFlag)
+ {
+
+ ScanKeyData key[3];
+ int nkeys;
+ SysScanDesc scan;
+ HeapTuple tup;
+ ObjectAddress otherObject;
+ Relation depRel;
+
+ depRel = heap_open(DependRelationId, RowExclusiveLock);
+
+ ScanKeyInit(key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(object-classId));
+ ScanKeyInit(key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(object-objectId));
+ if (object-objectSubId != 0)
+ {
+ScanKeyInit(key[2],
+ Anum_pg_depend_objsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(object-objectSubId));
+ nkeys = 3;
+ }
+ else
+ nkeys = 2;
+
+ scan = systable_beginscan(depRel, DependDependerIndexId, true,
+ SnapshotNow, nkeys, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(tup);
+
+ otherObject.classId = foundDep-refclassid;
+ otherObject.objectId = foundDep-refobjid;
+ otherObject.objectSubId = foundDep-refobjsubid;
+
+ /* Detach the default sequence from the relation */
+ if(deleteFlag == 1)
+ {
+ simple_heap_delete(depRel, tup-t_self);
+ break;
+ }
+
+ switch (foundDep-deptype)
+ {
+ case DEPENDENCY_NORMAL:
+ {
+
+ if(getObjectClass(otherObject) == OCLASS_CLASS)
+ {
+ /* Dont allow to change the default sequence */
+ if(deleteFlag == 2)
+ {
+ systable_endscan(scan);
+ heap_close(depRel, RowExclusiveLock);
+ elog(ERROR, %s is a SERIAL sequence. Can't alter the relation, getObjectDescription(otherObject));
+ return;
+ }
+ else /* Detach the default sequence from the relation */
+ {
+ performSequenceDefaultDeletion(otherObject, behavior, 1);
+ systable_endscan(scan);
+ heap_close(depRel, RowExclusiveLock);
+ return;
+ }
+ }
+ }
+
+ }
+ }
+
+ systable_endscan(scan);
+ heap_close(depRel, RowExclusiveLock);
+
+ }
*** ./src/backend/catalog/heap.c.orig Wed Apr 26 10:59:18 2006
--- ./src/backend/catalog/heap.c Wed Apr 26 12:03:49 2006
***
*** 2136,2138
--- 2136,2185
return result;
}
+
+
+ /* Detach the default sequence
[PATCHES] Patch for #2391: Similar to pattern matching does not operate as documented
Hi
I attach the patch for this bug. I have run the regression test and
passed. please review this and waiting for your reply.
As explained in the mailing list, the parenthesis is appened when '|'
operator is used without parenthesis.
Thanks
Dhanaraj
===
The following bug has been logged online:
Bug reference: 2391
Logged by: Eric Noriega
Email address: noriega ( at ) gwu ( dot ) edu
PostgreSQL version: 7.0.5
Operating system: Linux Fedora core 4
Description:Similar to pattern matching does not operate as
documented
Details:
As far as I can tell, this may be a bug in how the pattern matches.
db=# select 'tab' similar to '(a|b)';
?column?
--
f
db=# select 'tab' similar to 'a|b';
?column?
--
t
The doc says: Like LIKE, the SIMILAR TO operator succeeds only if its
pattern matches the entire string; this is unlike common regular expression
practice, wherein the pattern may match any part of the string.
If the second case is invalid as an expression (not clear in the
docs:Parentheses may be used to group items into a single logical item),
then the statement should fail, or return false, not return true.
=
* From: Tom Lane tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us
* To: Eric Noriega noriega ( at ) gwu ( dot ) edu
* Subject: Re: BUG #2391: Similar to pattern matching does not
operate as documented
* Date: Thu, 13 Apr 2006 12:55:41 -0400
Eric Noriega noriega ( at ) gwu ( dot ) edu writes:
db=# select 'tab' similar to 'a|b';
?column?
--
t
Yeah, this is a bug ... the cause can be seen by looking at the
underlying similar_escape() function, which converts a SIMILAR TO
pattern into a POSIX regex pattern:
regression=# select similar_escape('(a|b)', null);
similar_escape
^(a|b)$
(1 row)
regression=# select similar_escape('a|b', null);
similar_escape
^a|b$
(1 row)
regression=#
I believe that in the second case, ^ and $ bind more tightly than |
per POSIX rules. So we need to put parens around the pattern to
prevent that.
Thanks for the report!
regards, tom lane
=
*** ./src/backend/utils/adt/regexp.c.orig Sun Mar 5 21:28:43 2006
--- ./src/backend/utils/adt/regexp.cWed Apr 19 12:23:30 2006
***
*** 522,527
--- 522,529
elen;
boolafterescape = false;
int nquotes = 0;
+ int index;
+ boolparanFlag = false;
/* This function is not strict, so must test explicitly */
if (PG_ARGISNULL(0))
***
*** 549,559
errhint(Escape string must be empty or one
character.)));
}
/* We need room for ^, $, and up to 2 output bytes per input byte */
! result = (text *) palloc(VARHDRSZ + 2 + 2 * plen);
r = VARDATA(result);
*r++ = '^';
while (plen 0)
{
--- 551,583
errhint(Escape string must be empty or one
character.)));
}
+ /* Add parenthesis */
+ for(index = 0; index plen; index++)
+ {
+ charpchar = *(p+index);
+
+ if (pchar == '' || pchar == '%' || pchar == '_' || pchar ==
'\\' ||
+ pchar == '.' || pchar == '?' || pchar == '{' || pchar ==
'(' || pchar == ')')
+ {
+ paranFlag = false;
+ break;
+ }
+ else if (pchar == '|')
+ paranFlag = true;
+
+ }
+
/* We need room for ^, $, and up to 2 output bytes per input byte */
! if (paranFlag == true)
! result = (text *) palloc(VARHDRSZ + 2 + 2 + 2 * plen);
! else
! result = (text *) palloc(VARHDRSZ + 2 + 2 * plen);
!
r = VARDATA(result);
*r++ = '^';
+ if (paranFlag == true)
+ *r++ = '(';
while (plen 0)
{
***
*** 593,598
--- 617,625
p++, plen--;
}
+ if (paranFlag == true)
+ *r++ = ')';
+
*r++ = '$';
VARATT_SIZEP(result) = r - ((char *) result);
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] Patch for bug #2073 (Can't drop sequence when created
Pl. look at the following code, which is taken from alter_table.sql (regression test) = mydb=# create table anothertab (atcol1 serial8, atcol2 boolean, constraint anothertab_chk check (atcol1 = 3)); NOTICE: CREATE TABLE will create implicit sequence anothertab_atcol1_seq for serial column anothertab.atcol1 CREATE TABLE mydb=# alter table anothertab alter column atcol1 drop default; ALTER TABLE mydb=# \d List of relations Schema | Name | Type | Owner +---+--+-- public | anothertab| table| dm199272 public | anothertab_atcol1_seq | sequence | dm199272 (2 rows) mydb=# drop sequence anothertab_atcol1_seq; ERROR: cannot drop sequence anothertab_atcol1_seq because table anothertab column atcol1 requires it HINT: You may drop table anothertab column atcol1 instead. = Please tell me whether statement-2 is valid or not (as you say that the default sequence should not be changed). Or the default seq. can be dropped and cant be changed. I like to send you the appropriate patch and waiting for your reply. Thanks Dhanaraj Tom Lane wrote: Dhanaraj M - Sun Microsystems [EMAIL PROTECTED] writes: I fixed the above bug. I attach the patch here. Please review and acknowledge me. Bug details BUG #2073: Can't drop sequence when created via SERIAL column That isn't a bug, and this fix is not appropriate. See eg Bruce's response to that bug report: http://archives.postgresql.org/pgsql-bugs/2005-11/msg00304.php I speculated a bit ago (can't find it in the archives at the moment) that we should abandon the hidden dependency altogether, and go back to having SERIAL just create the sequence and the default expression. Another idea that might be worth exploring is to link the sequence to the default expression rather than to the table column itself. But randomly dropping the dependency is not the answer. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
