Re: [PATCHES] [BUGS] Bug in byteaout code in all PostgreSQL versions
Joe Conway has an updated version of this he will be applying shortly. Thanks. Joe, please make sure you CC this person once your patch is applied. --- Sergey N. Yatskevich wrote: Hello! bytea-test.cxx -- contains detailed description of the bug and test plan and code. varlena.c.diff -- patch for PostgreSQL 7.5devel src/backend/utils/adt/varlena.c#byteaout fe-exec.c.diff -- patch (optional) for PostgreSQL 7.5devel src/interface/libpq/fe-exec.c#PQescapeBytea test.data -- contains test data :-)) -- Sergey N. Yatskevich [EMAIL PROTECTED] GosNIIAS [ Attachment, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] ---(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 -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] clock_timestamp() and transaction_timestamp() function
Wang Mike wrote:
add clock_timestamp() and transaction_timestamp() function
see TODO list get more
-
diff -u -r ../cvs/pgsql/src/backend/utils/adt/timestamp.c
../pgsql/src/backend/utils/adt/timestamp.c
--- ../cvs/pgsql/src/backend/utils/adt/timestamp.c2003-07-18
21:59:07.0 +0800
+++ ../pgsql/src/backend/utils/adt/timestamp.c2003-07-18
22:45:03.0 +0800
@@ -28,6 +28,7 @@
#include miscadmin.h
#include utils/array.h
#include utils/builtins.h
+#include utils/nabstime.h
/*
* gcc's -ffast-math switch breaks routines that expect exact results from
@@ -835,6 +836,9 @@
return TRUE;
}/* EncodeSpecialTimestamp() */
+/* see pg_proc.h function now() [line 1602]
+ * deleted by Xiongjian Wang (Mike Wang)
+ * Email: [EMAIL PROTECTED]
Datum
now(PG_FUNCTION_ARGS)
{
@@ -848,6 +852,56 @@
PG_RETURN_TIMESTAMPTZ(result);
}
+*/
+
+/*
+ * Function transaction_timestamp() return current transaction timestamp.
+ * Added by Xiongjian Wang (Mike Wang)
+ * Email: [EMAIL PROTECTED]
+*/
+Datum
+transaction_timestamp(PG_FUNCTION_ARGS)
+{
+ TimestampTz result;
+ AbsoluteTime sec;
+ int usec;
+
+ sec = GetCurrentTransactionStartTimeUsec(usec);
+
+ result = AbsoluteTimeUsecToTimestampTz(sec, usec);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}/* transaction_timestamp() */
+
+/*
+ * Function clock_timestamp() return current clock timestamp.
+ * Added by Xiongjian Wang (Mike Wang)
+ * Email: [EMAIL PROTECTED]
+*/
+Datum
+clock_timestamp(PG_FUNCTION_ARGS)
+{
+ TimestampTz result;
+ AbsoluteTime sec;
+ int usec;
+
+ sec = GetCurrentAbsoluteTimeUsec(usec);
+
+ result = AbsoluteTimeUsecToTimestampTz(sec, usec);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}/* clock_timestamp() */
+
+/* Please tell me what is statement_timestamp() ?
+ * Email: [EMAIL PROTECTED]
+Datum
+statement_timestamp(PG_FUNCTION_ARGS)
+{
+ TimestampTz result;
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+*/
void
dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
diff -u -r ../cvs/pgsql/src/include/catalog/pg_proc.h
../pgsql/src/include/catalog/pg_proc.h
--- ../cvs/pgsql/src/include/catalog/pg_proc.h2003-07-01
08:04:38.0 +0800
+++ ../pgsql/src/include/catalog/pg_proc.h2003-07-18 21:47:15.0
+0800
@@ -1598,7 +1598,9 @@
DESCR(convert date and time with time zone to timestamp with time zone);
DATA(insert OID = 1298 ( timetzdate_plPGNSP PGUID 14 f f t f i 2 1184
1266 1082 select ($2 + $1) - _null_ ));
DESCR(convert time with time zone and date to timestamp with time zone);
-DATA(insert OID = 1299 ( now PGNSP PGUID 12 f f t f s 0
1184 now
- _null_ ));
+
+/* Function Now() only a alias of transaction_timestamp() */
+DATA(insert OID = 1299 (now PGNSP PGUID 12 f f t f s 0 1184
transaction_timestamp - _null_ ));
DESCR(current transaction time);
/* OIDS 1300 - 1399 */
@@ -3406,6 +3408,12 @@
DESCR(I/O);
+DATA(insert OID = 2510 (transaction_timestamp PGNSP PGUID 12 f f t f s 0
1184 transaction_timestamp - _null_ ));
+DESCR(current transaction time);
+
+DATA(insert OID = 2511 (clock_timestamp PGNSP PGUID 12 f f t f v 0 1184
clock_timestamp - _null_ ));
+DESCR(current clock time);
+
/*
* Symbolic values for provolatile column: these indicate whether the
result
* of a function is dependent *only* on the values of its explicit
arguments,
diff -u -r ../cvs/pgsql/src/include/utils/timestamp.h
../pgsql/src/include/utils/timestamp.h
--- ../cvs/pgsql/src/include/utils/timestamp.h2003-05-13
07:08:52.0 +0800
+++ ../pgsql/src/include/utils/timestamp.h2003-07-18 21:24:12.0
+0800
@@ -231,7 +231,9 @@
extern Datum timestamptz_trunc(PG_FUNCTION_ARGS);
extern Datum timestamptz_part(PG_FUNCTION_ARGS);
-extern Datum now(PG_FUNCTION_ARGS);
+/* extern Datum now(PG_FUNCTION_ARGS); */
+extern Datum transaction_timestamp(PG_FUNCTION_ARGS);
+extern Datum transaction_timestamp(PG_FUNCTION_ARGS);
/* Internal routines (not fmgr-callable) */
_
?? MSN Messenger: http://messenger.msn.com/cn
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
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 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] clock_timestamp() and transaction_timestamp() function
Bruce Momjian writes: We have started 7.5 development so we can now work with you to complete this item. Your question was what does statement_timestamp() show. That has been discussed and it is a little tricky. The idea is that is should be start of a single statement that arrives from the user or is requestd by a client. However, this gets muddled because SPI can issue queries and plpgsql functions can too. Are these supposed to be user-visible functions? What are they supposed to do? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] clock_timestamp() and transaction_timestamp() function
Peter Eisentraut wrote: Bruce Momjian writes: We have started 7.5 development so we can now work with you to complete this item. Your question was what does statement_timestamp() show. That has been discussed and it is a little tricky. The idea is that is should be start of a single statement that arrives from the user or is requestd by a client. However, this gets muddled because SPI can issue queries and plpgsql functions can too. Are these supposed to be user-visible functions? What are they supposed to do? Yes, user-visible. They give additional current timestamp information. transaction_timestamp() is the same as current_timestamp, clock_timestamp is the same as gettimeofday(), and statement_timestamp is a new one that shows statement start time. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] clock_timestamp() and transaction_timestamp() function
Bruce Momjian writes: Yes, user-visible. They give additional current timestamp information. transaction_timestamp() is the same as current_timestamp, clock_timestamp is the same as gettimeofday(), and statement_timestamp is a new one that shows statement start time. And what would be the point of that? -- Peter Eisentraut [EMAIL PROTECTED] ---(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] export FUNC_MAX_ARGS as a read-only GUC variable (was:
Tom Lane writes: One could make a good case that INDEX_MAX_KEYS should be exported along with FUNC_MAX_ARGS, rather than letting people write client code that assumes they are the same. You can determine these values by looking into the system catalogs. I was intending to propose that we also export the following as read-only variables: * NAMEDATALEN And this as well. * BLCKSZ Why would anyone be interested in that? * integer-vs-float datetime flag Here we should really decide on one representation in the near term. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Problem with dblink
Joe Conway [EMAIL PROTECTED] writes:
Tom Lane wrote:
(More generally, I wonder if AtEOXact_SPI oughtn't be fixed to emit
a WARNING if the SPI stack isn't empty, except in the error case.
Neglecting SPI_finish is analogous to a resource leak, and we have
code in place to warn about other sorts of leaks.)
Is the attached what you had in mind?
Approximately. A couple minor stylistic gripes:
1. AFAIR, all the other at-end-of-xact routines that take a flag telling
them if it's commit or abort define the flag as isCommit. Having the
reverse convention for this one routine is confusing and a recipe for
errors, so please make it be isCommit too.
2. The initial comment for AtEOXact_SPI:
* Clean up SPI state at transaction commit or abort (we don't care which).
is now incorrect and needs to be updated (at least get rid of the
parenthetical note).
! if (_SPI_stack != NULL)
! {
free(_SPI_stack);
+ if (!isAbort)
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING),
+ errmsg(freeing non-empty SPI stack),
+ errhint(Check for missing \SPI_finish\
calls)));
+ }
+
While this isn't necessarily wrong, what would probably be a stronger
test is to complain if either _SPI_connected or _SPI_curid is not -1.
For one thing that would catch missing SPI_pop(). (Jan, any comment
about that?)
regards, tom lane
---(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] clock_timestamp() and transaction_timestamp() function
Peter Eisentraut wrote: Bruce Momjian writes: Yes, user-visible. They give additional current timestamp information. transaction_timestamp() is the same as current_timestamp, clock_timestamp is the same as gettimeofday(), and statement_timestamp is a new one that shows statement start time. And what would be the point of that? The goal was to give a unified API to the various time measurements: [clock|statement|transaction]_timestamp -- 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 7: don't forget to increase your free space map settings
Re: [PATCHES] export FUNC_MAX_ARGS as a read-only GUC variable (was:
Peter Eisentraut wrote: Tom Lane writes: One could make a good case that INDEX_MAX_KEYS should be exported along with FUNC_MAX_ARGS, rather than letting people write client code that assumes they are the same. You can determine these values by looking into the system catalogs. How, count? Seems we should give an easy API. I was intending to propose that we also export the following as read-only variables: * NAMEDATALEN And this as well. Again, why not make it easy. * BLCKSZ Why would anyone be interested in that? Performance/admin tools might need this --- you need it to get the disk size based on the number of pages recorded in pg_class. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] GUC descriptions of SHOW
Seems there are three GUC variables that are defined as Shows ...
while you can actually set them with SET.
This applied patch changes the wording from Show to Set.
--
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
Index: src/backend/utils/misc/guc.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.172
diff -c -c -r1.172 guc.c
*** src/backend/utils/misc/guc.c29 Nov 2003 19:52:03 - 1.172
--- src/backend/utils/misc/guc.c1 Dec 2003 03:50:41 -
***
*** 808,814
},
{
{transaction_read_only, PGC_USERSET, CLIENT_CONN_STATEMENT,
! gettext_noop(Shows the current transaction's read-only
status.),
NULL,
GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
--- 808,814
},
{
{transaction_read_only, PGC_USERSET, CLIENT_CONN_STATEMENT,
! gettext_noop(Sets the current transaction's read-only
status.),
NULL,
GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
***
*** 1532,1538
{
/* Not for general use --- used by SET SESSION AUTHORIZATION */
{session_authorization, PGC_USERSET, UNGROUPED,
! gettext_noop(Shows the session user name.),
NULL,
GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
--- 1532,1538
{
/* Not for general use --- used by SET SESSION AUTHORIZATION */
{session_authorization, PGC_USERSET, UNGROUPED,
! gettext_noop(Sets the session user name.),
NULL,
GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
***
*** 1572,1578
{
{transaction_isolation, PGC_USERSET, CLIENT_CONN_STATEMENT,
! gettext_noop(Shows the current transaction's isolation
level.),
NULL,
GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
--- 1572,1578
{
{transaction_isolation, PGC_USERSET, CLIENT_CONN_STATEMENT,
! gettext_noop(Sets the current transaction's isolation
level.),
NULL,
GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] Problem with dblink
Tom Lane wrote:
1. AFAIR, all the other at-end-of-xact routines that take a flag telling
them if it's commit or abort define the flag as isCommit. Having the
reverse convention for this one routine is confusing and a recipe for
errors, so please make it be isCommit too.
Done.
2. The initial comment for AtEOXact_SPI:
* Clean up SPI state at transaction commit or abort (we don't care which).
is now incorrect and needs to be updated (at least get rid of the
parenthetical note).
Done.
! if (_SPI_stack != NULL)
! {
free(_SPI_stack);
+ if (!isAbort)
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING),
+ errmsg(freeing non-empty SPI stack),
+ errhint(Check for missing \SPI_finish\ calls)));
+ }
+
While this isn't necessarily wrong, what would probably be a stronger
test is to complain if either _SPI_connected or _SPI_curid is not -1.
For one thing that would catch missing SPI_pop(). (Jan, any comment
about that?)
Based on some simple testing, a warning for (_SPI_connected != -1) seems
redundant with (_SPI_stack != NULL) -- i.e. I just get both warnings
when SPI_finish() isn't called. And commenting out SPI_pop() causes a
SPI_finish failed error, at least in the one place it's used
(pl_exec.c), so we never get to AtEOXact_SPI(). So for the moment at
least I left that last section the same.
OK to commit?
Joe
Index: src/backend/access/transam/xact.c
===
RCS file: /cvsroot/pgsql-server/src/backend/access/transam/xact.c,v
retrieving revision 1.157
diff -c -r1.157 xact.c
*** src/backend/access/transam/xact.c 29 Nov 2003 19:51:40 - 1.157
--- src/backend/access/transam/xact.c 1 Dec 2003 03:50:40 -
***
*** 977,983
CallEOXactCallbacks(true);
AtEOXact_GUC(true);
! AtEOXact_SPI();
AtEOXact_gist();
AtEOXact_hash();
AtEOXact_nbtree();
--- 977,983
CallEOXactCallbacks(true);
AtEOXact_GUC(true);
! AtEOXact_SPI(true);
AtEOXact_gist();
AtEOXact_hash();
AtEOXact_nbtree();
***
*** 1087,1093
CallEOXactCallbacks(false);
AtEOXact_GUC(false);
! AtEOXact_SPI();
AtEOXact_gist();
AtEOXact_hash();
AtEOXact_nbtree();
--- 1087,1093
CallEOXactCallbacks(false);
AtEOXact_GUC(false);
! AtEOXact_SPI(false);
AtEOXact_gist();
AtEOXact_hash();
AtEOXact_nbtree();
Index: src/backend/executor/spi.c
===
RCS file: /cvsroot/pgsql-server/src/backend/executor/spi.c,v
retrieving revision 1.108
diff -c -r1.108 spi.c
*** src/backend/executor/spi.c 29 Nov 2003 19:51:48 - 1.108
--- src/backend/executor/spi.c 1 Dec 2003 03:50:40 -
***
*** 174,191
}
/*
! * Clean up SPI state at transaction commit or abort (we don't care which).
*/
void
! AtEOXact_SPI(void)
{
/*
* Note that memory contexts belonging to SPI stack entries will be
* freed automatically, so we can ignore them here. We just need to
* restore our static variables to initial state.
*/
! if (_SPI_stack != NULL) /* there was abort */
free(_SPI_stack);
_SPI_current = _SPI_stack = NULL;
_SPI_connected = _SPI_curid = -1;
SPI_processed = 0;
--- 174,199
}
/*
! * Clean up SPI state at transaction commit or abort.
*/
void
! AtEOXact_SPI(bool isCommit)
{
/*
* Note that memory contexts belonging to SPI stack entries will be
* freed automatically, so we can ignore them here. We just need to
* restore our static variables to initial state.
*/
! if (_SPI_stack != NULL)
! {
free(_SPI_stack);
+ if (isCommit)
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING),
+errmsg(freeing non-empty SPI stack),
+errhint(Check for missing \SPI_finish\
calls)));
+ }
+
_SPI_current = _SPI_stack = NULL;
_SPI_connected = _SPI_curid = -1;
SPI_processed = 0;
Index: src/include/executor/spi.h
===
RCS file: /cvsroot/pgsql-server/src/include/executor/spi.h,v
retrieving revision 1.40
diff -c -r1.40 spi.h
*** src/include/executor/spi.h 29 Nov 2003 22:41:01 - 1.40
--- src/include/executor/spi.h 1 Dec 2003 03:50:43 -
***
*** 116,121
extern void SPI_cursor_move(Portal portal, bool forward, int count);
extern void SPI_cursor_close(Portal portal);
! extern void AtEOXact_SPI(void);
#endif /* SPI_H */
--- 116,121
extern void SPI_cursor_move(Portal portal, bool forward, int count);
extern void
Re: [PATCHES] Numeric version of factorial()
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Gavin Sherry wrote: On Thu, 31 Jul 2003, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: What are your feelings about numeric argument vs. int4/int8 arguments? Actually I think it'd be fine to take int8. We'd not be able to cope with any larger input anyway, and the inner loop could be noticeably faster if the control logic just deals with int. We could leave the factorial(numeric) case open for a future implementation that uses gamma, if anyone gets hot to do it. Attached is a revised patch based on your Tom's comments. It removes int[248]fac(), modifies regression tests (which referenced int4fac()), and implements a much cleaned numeric_fac(). regards, tom lane Thanks, Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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 8: explain analyze is your friend
Re: [PATCHES] ISO 8601 Time Intervals of the format with time-unit
Is this ready for application? It looks good to me. However, there is an Open issues section. --- Ron Mayer wrote: Short summary: This patch allows ISO 8601 time intervals using the format with time-unit designators to specify postgresql intervals. Below I have (A) What these time intervals are, (B) What I modified to support them, (C) Issues with intervals I want to bring up, and (D) a patch supporting them. It's helpful to me. Any feedback is appreciated. If you did want to consider including it, let me know what to clean up. If not, I thought I'd just put it here if anyone else finds it useful too. Thanks for your time, Ron Mayer Longer: (A) What these intervals are. ISO 8601, the standard from which PostgreSQL gets some of it's time syntax, also has a specification for time-intervals. In particular, section 5.5.4.2 has a Representation of time-interval by duration only which I believe maps nicely to ISO intervals. Compared to the ISO 8601 time interval specification, the postgresql interval syntax is quite verbose. For example: Postgresql interval: ISO8601 Interval --- '1 year 6 months''P1Y6M' '3 hours 25 minutes 42 seconds' 'PT3H25M42S' Yeah, it's uglier, but it sure is short which can make for quicker typing and shorter scripts, and if for some strange reason you had an application using this format it's nice not to have to translate. The syntax is as follows: Basic extended format: PnYnMnDTnHnMnS PnW Where everything before the T is a date-part and everything after is a time-part. W is for weeks. In the date-part, Y=Year, M=Month, D=Day In the time-part, H=Hour, M=Minute, S=Second Much more info can be found from the draft standard ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF The final standard's only available for $$$ so I didn't look at it. Some other sites imply that this part didn't change from the last draft to the standard. (B) This change was made by adding two functions to datetime.c next to where DecodeInterval parses the normal interval syntax. A total of 313 lines were added, including comments and sgml docs. Of these only 136 are actual code, the rest, comments, whitespace, etc. One new function DecodeISO8601Interval follows the style of DecodeInterval below it, and trys to strictly follow the ISO syntax. If it doesn't match, it'll return -1 and the old syntax will be checked as before. The first test (first character of the first field must be 'P', and second character must be 'T' or '\0') should be fast so I don't think this will impact performance of existing code. The second function (adjust_fval) is just a small helper-function to remove some of the cutpaste style that DecodeInterval used. It seems to work. === betadb=# select 'P1M15DT12H30M7S'::interval; interval 1 mon 15 days 12:30:07 (1 row) betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval; interval 1 mon 15 days 12:30:07 (1 row) = (C) Open issues with intervals, and questions I'd like to ask. 1. DecodeInterval seems to have a hardcoded '.' for specifying fractional times. ISO 8601 states that both '.' and ',' are ok, but of these, the comma is the preferred sign. In DecodeISO8601Interval I loosened the test to allow both but left it as it was in DecodeInterval. Should both be changed to make them more consistant? 2. In DecodeInterval, fractional weeks and fractional months can produce seconds; but fractional years can not (rounded to months). I didn't understand the reasoning for this, so I left it the same, and followed the same convention for ISO intervals. Should I change this? 3. I could save a bunch of copy-paste-lines-of-code from the pre-existing DecodeInterval by calling the adjust_fval helper function. The tradeoff is a few extra function-calls when decoding an interval. However I didn't want to risk changes to the existing part unless you guys encourage me to do so. (D) The patch. Index: doc/src/sgml/datatype.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v retrieving
Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with time-unit
And another open issues email. --- Ron Mayer wrote: Tom wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Yes, but by the same token iso8601 isn't specific enough either. ISO 8601 gives more specific names. ISO 8601 Basic Format: P2Y10M15DT10H20M30S ISO 8601 Alternative Format: P00021015T102030 ISO 8601 Extended Format: P0002-10-15T10:20:30 In a way, the Extended Format is kinda nice, since it?s almost human readable. I could put in both the basic and extended ones, and call the dateformats ?iso8601basic? and ?iso8601extended?. The negative is that to do ?iso8601basic? right, I?d also have to tweak the ?date? and ?time? parts of the code too. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] WIP: unique hash indexes
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Neil Conway wrote: Attached is a patch that implements unique hash indexes. Regression tests and docs have been updated (with the exception of src/backend/access/hash/README). This patches implements an improved version of the concurrency changes I outlined on -hackers a couple days ago: the separate lock for right-of-insertion into a bucket chain has been replaced by making use of existing lmgr lockmodes to get the behavior we need. I also took the opportunity to refactor the hash index code in a couple places by moving duplicated code into separate functions, fixed some typos in the README file, etc. I've tested this a little bit (it survives 100,000 single-user pgbench transactions, and 10 concurrent clients doing 1000 transactions each), but I need to do more testing in the future. As far as I know, the unique hash index code is functionally complete, but don't add this to the patch queue (it is obviously for 7.5, anyway), since I'm about to get started on some related hash index work, for which I'll submit a mega-patch containing everything. I just posted this here so that anyone who's interested can take a look at it -- comments are welcome. Thanks to Tom Lane, who was very helpful when I asked him code questions, including suggesting the improved concurrency design I mentioned above. Cheers, Neil [ Attachment, skipping... ] ---(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 -- 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 4: Don't 'kill -9' the postmaster
Re: [PATCHES] introduce default_use_oids
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Neil Conway wrote: On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote: On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway [EMAIL PROTECTED] wrote: This patch adds a new GUC var, default_use_oids Shouldn't it be honoured by CREATE TABLE AS SELECT ... ? Good catch. I've attached an updated patch. I also included a few improvements to the docs. I think it would be a good idea to extend CREATE TABLE AS to allow WITH OIDS and WITHOUT OIDS to be specified, so that it provides a better option for people who need OIDs in their CREATE TABLE AS-generated tables than manually setting the default_use_oids GUC var (CREATE TABLE AS should also accept ON COMMIT ..., for that matter). But implementing this will require changing the internal representation of CREATE TABLE AS to be something more than just a wrapper over SelectStmt, I believe. I haven't made this change in the attached patch, but I'll probably do it before 7.5 is released. Any comments? -Neil [ Attachment, skipping... ] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Fwd: [PATCHES] Make psql use all pretty print options]
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---
Christopher Kings-Lynne wrote:
Resubmission of patch (for 7.4).
I fixed the problems I was having what I chatted to you Bruce, I've
tested it well and it shouldn't be a problem to apply for 7.4. It looks
really nice with the pretty print stuff!
Chris
Original Message
Subject: [PATCHES] Make psql use all pretty print options
Date: Mon, 29 Sep 2003 12:31:18 +0800 (WST)
From: Christopher Kings-Lynne [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Hi,
This patch finishes off the work that I did with making view
definitions use pretty printing.
It does:
* Pretty check constraints
* Pretty index predicates
* Pretty rule definitions
* Uppercases PRIMARY KEY and UNIQUE to be consistent with CHECK and
FOREIGN KEY
* View rules are improved to match table rules:
View public.v
Column | Type | Modifiers
--+-+---
?column? | integer |
View definition:
SELECT 1;
Rules:
r1 AS
ON INSERT TO v DO INSTEAD NOTHING
r2 AS
ON INSERT TO v DO INSTEAD NOTHING
Chris
Index: describe.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.85
diff -c -r1.85 describe.c
*** describe.c7 Sep 2003 03:43:53 - 1.85
--- describe.c29 Sep 2003 04:24:56 -
***
*** 857,863
printfPQExpBuffer(buf,
SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n
! pg_catalog.pg_get_expr(i.indpred,
i.indrelid)\n
FROM pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n
WHERE i.indexrelid = c.oid AND
c.oid = '%s' AND c.relam = a.oid\n
AND i.indrelid = c2.oid,
--- 857,863
printfPQExpBuffer(buf,
SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n
! pg_catalog.pg_get_expr(i.indpred,
i.indrelid, true)\n
FROM pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n
WHERE i.indexrelid = c.oid AND
c.oid = '%s' AND c.relam = a.oid\n
AND i.indrelid = c2.oid,
***
*** 880,888
char *indpred = PQgetvalue(result, 0, 4);
if (strcmp(indisprimary, t) == 0)
! printfPQExpBuffer(tmpbuf, _(primary key, ));
else if (strcmp(indisunique, t) == 0)
! printfPQExpBuffer(tmpbuf, _(unique, ));
else
resetPQExpBuffer(tmpbuf);
appendPQExpBuffer(tmpbuf, %s, , indamname);
--- 880,888
char *indpred = PQgetvalue(result, 0, 4);
if (strcmp(indisprimary, t) == 0)
! printfPQExpBuffer(tmpbuf, _(PRIMARY KEY, ));
else if (strcmp(indisunique, t) == 0)
! printfPQExpBuffer(tmpbuf, _(UNIQUE, ));
else
resetPQExpBuffer(tmpbuf);
appendPQExpBuffer(tmpbuf, %s, , indamname);
***
*** 892,898
schemaname, indtable);
if (strlen(indpred))
! appendPQExpBuffer(tmpbuf, , predicate %s, indpred);
footers = xmalloczero(2 * sizeof(*footers));
footers[0] = xstrdup(tmpbuf.data);
--- 892,898
schemaname, indtable);
if (strlen(indpred))
! appendPQExpBuffer(tmpbuf, , predicate (%s),
indpred);
footers = xmalloczero(2 * sizeof(*footers));
footers[0] = xstrdup(tmpbuf.data);
***
*** 911,917
if (tableinfo.hasrules)
{
printfPQExpBuffer(buf,
! SELECT r.rulename\n
FROM pg_catalog.pg_rewrite
r\n
WHERE r.ev_class = '%s' AND r.rulename !=
'_RETURN',
Re: [PATCHES] minor psql cleanup
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Neil Conway wrote: This patch reduces some unsightly #ifdefs, and fixes two typos in comments in the psql code. This doesn't make any functional change, so feel free to save it for 7.5 -Neil [ Attachment, skipping... ] ---(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 -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Make pg_dump dump conversions
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Christopher Kings-Lynne wrote: Just noticed a bug in the previous version of the patch. Would fail against pre-7.3 postgres. Fixed in this new version. Just discard the old. Chris Christopher Kings-Lynne wrote: Save this for 7.5. Nails a TODO item. Chris ? GNUmakefile ? config.log ? config.status ? src/Makefile.global ? src/backend/postgres ? src/backend/access/common/.deps ? src/backend/access/gist/.deps ? src/backend/access/hash/.deps ? src/backend/access/heap/.deps ? src/backend/access/index/.deps ? src/backend/access/nbtree/.deps ? src/backend/access/rtree/.deps ? src/backend/access/transam/.deps ? src/backend/bootstrap/.deps ? src/backend/catalog/.deps ? src/backend/catalog/postgres.bki ? src/backend/catalog/postgres.description ? src/backend/commands/.deps ? src/backend/executor/.deps ? src/backend/lib/.deps ? src/backend/libpq/.deps ? src/backend/main/.deps ? src/backend/nodes/.deps ? src/backend/optimizer/geqo/.deps ? src/backend/optimizer/path/.deps ? src/backend/optimizer/plan/.deps ? src/backend/optimizer/prep/.deps ? src/backend/optimizer/util/.deps ? src/backend/parser/.deps ? src/backend/port/.deps ? src/backend/postmaster/.deps ? src/backend/regex/.deps ? src/backend/rewrite/.deps ? src/backend/storage/buffer/.deps ? src/backend/storage/file/.deps ? src/backend/storage/freespace/.deps ? src/backend/storage/ipc/.deps ? src/backend/storage/large_object/.deps ? src/backend/storage/lmgr/.deps ? src/backend/storage/page/.deps ? src/backend/storage/smgr/.deps ? src/backend/tcop/.deps ? src/backend/utils/.deps ? src/backend/utils/adt/.deps ? src/backend/utils/cache/.deps ? src/backend/utils/error/.deps ? src/backend/utils/fmgr/.deps ? src/backend/utils/hash/.deps ? src/backend/utils/init/.deps ? src/backend/utils/mb/.deps ? src/backend/utils/mb/conversion_procs/conversion_create.sql ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0 ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0 ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0 ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0 ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0 ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0 ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0 ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0 ? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps ?
Re: [PATCHES] WIP: unique hash indexes
Bruce Momjian [EMAIL PROTECTED] writes: I will try to apply it within the next 48 hours. My impression was that Neil didn't think this was ready yet: As far as I know, the unique hash index code is functionally complete, but don't add this to the patch queue (it is obviously for 7.5, anyway), since I'm about to get started on some related hash index work, for which I'll submit a mega-patch containing everything. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] WIP: unique hash indexes
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I will try to apply it within the next 48 hours. My impression was that Neil didn't think this was ready yet: As far as I know, the unique hash index code is functionally complete, but don't add this to the patch queue (it is obviously for 7.5, anyway), since I'm about to get started on some related hash index work, for which I'll submit a mega-patch containing everything. Oops, sorry. -- 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
Re: [PATCHES] WIP: unique hash indexes
Patch removed. --- Neil Conway wrote: Attached is a patch that implements unique hash indexes. Regression tests and docs have been updated (with the exception of src/backend/access/hash/README). This patches implements an improved version of the concurrency changes I outlined on -hackers a couple days ago: the separate lock for right-of-insertion into a bucket chain has been replaced by making use of existing lmgr lockmodes to get the behavior we need. I also took the opportunity to refactor the hash index code in a couple places by moving duplicated code into separate functions, fixed some typos in the README file, etc. I've tested this a little bit (it survives 100,000 single-user pgbench transactions, and 10 concurrent clients doing 1000 transactions each), but I need to do more testing in the future. As far as I know, the unique hash index code is functionally complete, but don't add this to the patch queue (it is obviously for 7.5, anyway), since I'm about to get started on some related hash index work, for which I'll submit a mega-patch containing everything. I just posted this here so that anyone who's interested can take a look at it -- comments are welcome. Thanks to Tom Lane, who was very helpful when I asked him code questions, including suggesting the improved concurrency design I mentioned above. Cheers, Neil [ Attachment, skipping... ] ---(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 -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Make pg_dump dump conversions
Bruce Momjian [EMAIL PROTECTED] writes: I will try to apply it within the next 48 hours. This one's applied already, no? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] export FUNC_MAX_ARGS as a read-only GUC variable
Tom Lane wrote: One could make a good case that INDEX_MAX_KEYS should be exported along with FUNC_MAX_ARGS, rather than letting people write client code that assumes they are the same. I was intending to propose that we also export the following as read-only variables: * NAMEDATALEN * BLCKSZ * integer-vs-float datetime flag OK, the attached includes the above -- result looks like: regression=# select * from pg_settings where category like 'Compile%'; -[ RECORD 1 ]-- name | block_size setting| 8192 category | Compiled-in Options short_desc | Shows size of a disk block extra_desc | context| internal vartype| integer source | default min_val| 8192 max_val| 8192 -[ RECORD 2 ]-- name | func_max_args setting| 32 category | Compiled-in Options short_desc | Shows the maximum number of function arguments extra_desc | context| internal vartype| integer source | default min_val| 32 max_val| 32 -[ RECORD 3 ]-- name | index_max_keys setting| 32 category | Compiled-in Options short_desc | Shows the maximum number of index keys extra_desc | context| internal vartype| integer source | default min_val| 32 max_val| 32 -[ RECORD 4 ]-- name | integer_datetimes setting| on category | Compiled-in Options short_desc | Datetimes are integer based extra_desc | context| internal vartype| bool source | default min_val| max_val| -[ RECORD 5 ]-- name | name_data_len setting| 63 category | Compiled-in Options short_desc | Shows the maximum identifier length extra_desc | context| internal vartype| integer source | default min_val| 63 max_val| 63 If it's there it should be separate. I think also there was some feeling it should be called extra_desc not long_desc. Done. Also added category which displays config_group_names[conf-group] Please set the GUC_NOT_IN_SAMPLE and GUC_DISALLOW_IN_FILE flag bits on each of these variables, too. I know we are not using these flags for anything yet, but we should try to get them right... Done. I'll update the docs once I'm sure we're done iterating on these changes. Any further comments? Joe Index: src/backend/catalog/system_views.sql === RCS file: /cvsroot/pgsql-server/src/backend/catalog/system_views.sql,v retrieving revision 1.3 diff -c -r1.3 system_views.sql *** src/backend/catalog/system_views.sql29 Nov 2003 22:39:40 - 1.3 --- src/backend/catalog/system_views.sql1 Dec 2003 05:49:21 - *** *** 260,267 CREATE VIEW pg_settings AS SELECT * FROM pg_show_all_settings() AS A ! (name text, setting text, context text, vartype text, ! source text, min_val text, max_val text); CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings --- 260,267 CREATE VIEW pg_settings AS SELECT * FROM pg_show_all_settings() AS A ! (name text, setting text, category text, short_desc text, extra_desc text, ! context text, vartype text, source text, min_val text, max_val text); CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings Index: src/backend/utils/misc/guc.c === RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.173 diff -c -r1.173 guc.c *** src/backend/utils/misc/guc.c1 Dec 2003 03:55:21 - 1.173 --- src/backend/utils/misc/guc.c1 Dec 2003 05:49:23 - *** *** 156,162 static char *session_authorization_string; static char *timezone_string; static char *XactIsoLevel_string; ! /* Macros for freeing malloc'd pointers only if appropriate to do so */ /* Some of these tests are probably redundant, but be safe ... */ --- 156,166 static char *session_authorization_string; static char *timezone_string; static char *XactIsoLevel_string; ! static intfunc_max_args; ! static intindex_max_keys; ! static intname_data_len; ! static intblock_size; ! static bool integer_datetimes; /* Macros for freeing malloc'd pointers only if appropriate to do so */ /* Some of these tests are probably redundant, but be safe ... */ *** *** 302,307 --- 306,313 gettext_noop(Version and Platform Compatibility / Other Platforms and Clients), /* DEVELOPER_OPTIONS */ gettext_noop(Developer Options), + /* COMPILE_OPTIONS */ + gettext_noop(Compiled-in Options), /* help_config wants this array to be null-terminated */ NULL }; *** *** 832,837 --- 838,857 true, NULL,
Re: [PATCHES] export FUNC_MAX_ARGS as a read-only GUC variable
Joe Conway [EMAIL PROTECTED] writes: name | name_data_len setting| 63 short_desc | Shows the maximum identifier length Defining the value as NAMEDATALEN-1 is reasonable (I was thinking of suggesting that myself), but it seems like a recipe for confusion to use name_data_len to refer to NAMEDATALEN-1. Perhaps the GUC variable name should be max_name_len or some such. Also, should func_max_args and index_max_keys become max_func_args and max_index_keys? I'm not all that concerned about the names personally, but I want to forestall any temptation for Bruce to start renaming these values later, as he's felt free to do in the past ;-). My expectation is that the names of these GUC variables will get embedded into client-side code fairly quickly, and so it will not do to fool around with the names later. We must decide what the naming convention is and then stick to it. regards, tom lane ---(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] introduce default_use_oids
Bruce Momjian writes: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. This parameter ought to be called default_with_oids, to reflect the actual effect. --- Neil Conway wrote: On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote: On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway [EMAIL PROTECTED] wrote: This patch adds a new GUC var, default_use_oids Shouldn't it be honoured by CREATE TABLE AS SELECT ... ? Good catch. I've attached an updated patch. I also included a few improvements to the docs. I think it would be a good idea to extend CREATE TABLE AS to allow WITH OIDS and WITHOUT OIDS to be specified, so that it provides a better option for people who need OIDs in their CREATE TABLE AS-generated tables than manually setting the default_use_oids GUC var (CREATE TABLE AS should also accept ON COMMIT ..., for that matter). But implementing this will require changing the internal representation of CREATE TABLE AS to be something more than just a wrapper over SelectStmt, I believe. I haven't made this change in the attached patch, but I'll probably do it before 7.5 is released. Any comments? -Neil [ Attachment, skipping... ] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] export FUNC_MAX_ARGS as a read-only GUC variable
Tom Lane wrote: Perhaps the GUC variable name should be max_name_len or some such. Also, should func_max_args and index_max_keys become max_func_args and max_index_keys? That sounds good to me: -[ RECORD 3 ]-- name | max_func_args setting| 32 -[ RECORD 4 ]-- name | max_index_keys setting| 32 -[ RECORD 5 ]-- name | max_name_len setting| 63 I'll finish up the docs and commit this tomorrow, barring strong complaints. It will require an initdb -- should I hold off for other pending changes also requiring initdb? Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] export FUNC_MAX_ARGS as a read-only GUC variable
Joe Conway [EMAIL PROTECTED] writes: It will require an initdb -- should I hold off for other pending changes also requiring initdb? No, there's no particular reason to avoid initdbs during development cycles. That's why we have catversion in the first place ... regards, tom lane ---(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] [HACKERS] Index creation takes for ever
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---
Manfred Koizar wrote:
On Mon, 01 Sep 2003 08:46:09 -0400, Tom Lane [EMAIL PROTECTED]
wrote:
[EMAIL PROTECTED] writes:
it took 69 minutes to finish, 75% of this time was devoted to create 2
indexes on varchar(2) with value being 'O', 'N' or null;
I still say it's either strcoll or qsort's fault.
If qsort is to blame, then maybe this patch could help. It sorts
equal key values on item pointer. And if it doesn't help index
creation speed, at least the resulting index has better correlation.
Test script:
CREATE TABLE t (i int NOT NULL, t text NOT NULL);
INSERT INTO t VALUES (1, 'lajshdflasjhdflajhsdfljhasdlfjhasdf');
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t VALUES (100, 's,dmfa.,smdn.famsndfamdnsbfmansdbf');
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
ANALYZE t;
CREATE INDEX t_i ON t(i);
SET enable_seqscan = 0;
SELECT ctid FROM t WHERE i=100 LIMIT 10;
Result without patch:
ctid
--
(153,14)
(306,23)
(305,80)
(152,91)
(76,68)
(38,34)
(153,34)
(305,50)
(9,62)
(305,40)
(10 rows)
Result with patch:
ctid
(0,5)
(0,10)
(0,15)
(0,20)
(0,25)
(0,30)
(0,35)
(0,40)
(0,45)
(0,50)
(10 rows)
For testing purposes I have made a second patch that provides a
boolean GUC variable sort_index. It is available here:
http://www.pivot.at/pg/23.test-IdxTupleSort.diff
Servus
Manfred
diff -ruN ../base/src/backend/utils/sort/tuplesort.c
src/backend/utils/sort/tuplesort.c
--- ../base/src/backend/utils/sort/tuplesort.c2003-08-17 21:58:06.0
+0200
+++ src/backend/utils/sort/tuplesort.c2003-09-05 10:04:22.0 +0200
@@ -2071,6 +2071,33 @@
(errcode(ERRCODE_UNIQUE_VIOLATION),
errmsg(could not create unique index),
errdetail(Table contains duplicated values.)));
+ else
+ {
+ /*
+ * If key values are equal, we sort on ItemPointer. This might help
+ * for some bad qsort implementation having performance problems
+ * with many equal items. OTOH I wouldn't trust such a weak qsort
+ * to handle pre-sorted sequences very well ...
+ *
+ * Anyway, this code doesn't hurt much, and it helps produce indices
+ * with better index correlation which is a good thing per se.
+ */
+ ItemPointer tid1 = tuple1-t_tid;
+ ItemPointer tid2 = tuple2-t_tid;
+ BlockNumber blk1 = ItemPointerGetBlockNumber(tid1);
+ BlockNumber blk2 = ItemPointerGetBlockNumber(tid2);
+
+ if (blk1 != blk2)
+ return (blk1 blk2) ? -1 : 1;
+ else
+ {
+ OffsetNumber pos1 = ItemPointerGetOffsetNumber(tid1);
+ OffsetNumber pos2 = ItemPointerGetOffsetNumber(tid2);
+
+ if (pos1 != pos2)
+ return (pos1 pos2) ? -1 : 1;
+ }
+ }
return 0;
}
---(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 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] Index creation takes for ever
Bruce Momjian [EMAIL PROTECTED] writes: If qsort is to blame, then maybe this patch could help. It sorts equal key values on item pointer. And if it doesn't help index creation speed, at least the resulting index has better correlation. I will try to apply it within the next 48 hours. I think this is a *very* dubious idea. It introduces a low-level implementation dependency into our sort behavior on the strength of no more than an unfounded speculation that some platform's broken qsort might run faster. Even if the speculation were proven true, I'd be hesistant to apply it. regards, tom lane ---(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
