Re: [PATCHES] WIP patch - INSERT-able log statements
On 2/20/07, Tom Lane [EMAIL PROTECTED] wrote: Of course, the other side of that coin is that syslog is known to drop messages altogether under sufficient load. (At least on some platforms; dunno about yours.) Yes I know. That's one of the reason why I asked for the bahaviour of 7.4 log_duration back in 8.2. It's a good compromise which allows us not to lose lines and have a good level of information (at least, the best we can have). Async IO helps. Moreover we use syslog to send the log lines via UDP so we know that it's not perfect. But it works nice most of the time. We know that we can't log every query (we use a combination of log_duration and log_min_duration_statement - I patched 8.1 for that) because if we do so we lose a lot of lines and queries are not consistent but we can't do it locally with stderr anyway due to I/O. This method has been reliable for more than a year and our daily reports are consistent. -- Guillaume ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
On 2/20/07, Hannu Krosing [EMAIL PROTECTED] wrote: Ühel kenal päeval, T, 2007-02-20 kell 12:08, kirjutas Pavan Deolasee: What do you do, if there are no live tuples on the page ? will this un-HOTify the root and free all other tuples in HOT chain ? Yes. The HOT-updated status of the root and all intermediate tuples is cleared and their respective ctid pointers are made point to themselves. The index entry will be marked LP_DELETE as with the normal case. VACUUM can subsequently reclaimed these tuples, along with the index entry. The intermediate heap-only tuples are removed from the HOT-update chain. The HOT-updated status of these tuples is cleared and their respective t_ctid are made point to themselves. These tuples are not reachable now and ready for vacuuming. Does this mean, that they are now indistinguishable from ordinary tuples ? No. HEAP_ONLY_TUPLE flag is still set on these tuples. So you can distinguish those tuples. Maybe they could be freed right away instead of changing HOT-updated status and ctid ? Yeah, thats a good idea. I am thinking of setting LP_DELETE flag on them while pruning. The tuple then can be reused for next in-page HOT-update. When we run out space for update-within-the-block, we traverse through all the line pointers looking for LP_DELETEd items. If any of these items have space large enough to store the new tuple, that item is reused. Does anyone see any issue with doing this ? Also, any suggestions about doing it in a better way ? IIRC the size is determined by the next tuple pointer, so you can store new data without changing tuple pointer only if they are exactly the same size. There is a lp_len field in the line pointer to store the length of the tuple. ISTM that we can reduce that while reusing the line pointer. But that would create a permanent hole in the page. we are more concerned about the large tables, the chances of being able to upgrade the exclusive lock to vacuum-strength lock are high. Comments ? I'm not sure about the we are more concerned about the large tables part. I see it more as a device for high-update tables. This may not always be the same as large, so there should be some fallbacks for case where you can't get the lock. Maybe just give up and move to another page ? Oh, yes. I agree. The fallback option of doing COLD update always exists. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
[PATCHES] New version of IDENTITY/GENERATED
Hi,
I started working on my previous patch, encouraged
by the fact that it became a wishlist item for 8.3. :-)
The changes in this version are:
- Refreshed to almost current (5 days old)
CVS version of 8.3 devel
- The original SERIAL pseudo type is left alone,
you _have to_ spell out GENERATED
{ ALWAYS | BY DEFAULT} AS IDENTITY
to get an identity column.
- The action-at-a-distance behaviour is actually working
for the IDENTITY/GENERATED columns on INSERT
so the DEFAULT value is generated for them
after all the regular columns were validated via
ExecConstraints(). This way, if the validation fails,
the sequence isn't inflated.
- Test case is updated to reflect the above.
- Documentation is updated, Identity columns have a new
subsection now.
- Dropped my pg_dump changes, as the altered sequence is
also dumped in 8.2, thanks to Tom Lane.
I am considering the following:
- Since the IDENTITY is a new feature (plain old SERIAL
behaves the same as always) I will restore the SQL:2003
confromant check that there can be only one identity column
in a table at any time.
- I read somewhere (but couldn't find it now in SQL:2003)
that CHECK constraints cannot be defined for GENERATED
(and IDENTITY?) columns. Maybe it was in the latest draft,
I have to look at it... Anyway, I have to implement checks
to disallow CHECKs for such columns.
- Introduce an ALTER TABLE SET|DROP IDENTITY so
a serial can be upgraded to an identity. This way, an identity
column can be built by hand and pg_dump will need it, too.
SET IDENTITY will either have to issue an error if CHECKs
defined for such columns or automatically drop every such
constraints.
And I have a question, too. Is there a way to use ExecEvalExpr*()
so values from a given tuples are used for current row? E.g.
at present, UPDATE table SET f1 = f1 + 1, f2 = f1 + 1;
sets both fields' new value to (f1 value before UPDATE) + 1.
For a GENERATED column, value _after_ UPDATE
is needed, so
CREATE TABLE table (
f1 INTEGER,
f2 INTEGER GENERATED ALWAYS AS (f1 + 1));
and no matter which one of the following is used:
UPDATE table SET f1 = f1 + 1;
or
UPDATE table SET f1 = f1 + 1, f2 = default;
the f2 current value = f1 current value + 1 is always maintained.
Best regards,
Zoltán Böszörményi
psql-serial-30.diff.gz
Description: Unix tar archive
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PATCHES] correct format for date, time, timestamp for XML functionality
Hello,
this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to
USE_ISO_DATESTYLE. Differences are for timestamp:
ISO: -mm-dd hh24:mi:ss
XSD: -mm-ddThh24:mi:ss
I found one link about this topic:
http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0
Regards
Pavel Stehule
_
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
*** ./src/backend/utils/adt/datetime.c.orig 2007-02-19 21:46:54.0 +0100
--- ./src/backend/utils/adt/datetime.c 2007-02-19 22:06:20.0 +0100
***
*** 3188,3193
--- 3188,3194
switch (style)
{
case USE_ISO_DATES:
+ case USE_XSD_DATES:
/* compatible with ISO date formats */
if (tm-tm_year 0)
sprintf(str, %04d-%02d-%02d,
***
*** 3278,3283
--- 3279,3285
* SQL - mm/dd/ hh:mm:ss.ss tz
* ISO - -mm-dd hh:mm:ss+/-tz
* German - dd.mm. hh:mm:ss tz
+ * XSD - -mm-ddThh:mm:ss.ss+/-tz
* Variants (affects order of month and day for Postgres and SQL styles):
* US - mm/dd/
* European - dd/mm/
***
*** 3296,3306
switch (style)
{
case USE_ISO_DATES:
/* Compatible with ISO-8601 date formats */
! sprintf(str, %04d-%02d-%02d %02d:%02d,
(tm-tm_year 0) ? tm-tm_year : -(tm-tm_year - 1),
tm-tm_mon, tm-tm_mday, tm-tm_hour, tm-tm_min);
/*
* Print fractional seconds if any. The field widths here should
--- 3298,3315
switch (style)
{
case USE_ISO_DATES:
+ case USE_XSD_DATES:
/* Compatible with ISO-8601 date formats */
! if (style == USE_ISO_DATES)
! sprintf(str, %04d-%02d-%02d %02d:%02d,
(tm-tm_year 0) ? tm-tm_year : -(tm-tm_year - 1),
tm-tm_mon, tm-tm_mday, tm-tm_hour, tm-tm_min);
+ else
+ sprintf(str, %04d-%02d-%02dT%02d:%02d,
+ (tm-tm_year 0) ? tm-tm_year : -(tm-tm_year - 1),
+ tm-tm_mon, tm-tm_mday, tm-tm_hour, tm-tm_min);
+
/*
* Print fractional seconds if any. The field widths here should
*** ./src/backend/utils/adt/xml.c.orig 2007-02-19 19:37:27.0 +0100
--- ./src/backend/utils/adt/xml.c 2007-02-19 22:33:11.0 +0100
***
*** 65,73
#include utils/builtins.h
#include utils/lsyscache.h
#include utils/memutils.h
#include utils/xml.h
-
#ifdef USE_LIBXML
static StringInfo xml_err_buf = NULL;
--- 65,74
#include utils/builtins.h
#include utils/lsyscache.h
#include utils/memutils.h
+ #include utils/date.h
+ #include utils/datetime.h
#include utils/xml.h
#ifdef USE_LIBXML
static StringInfo xml_err_buf = NULL;
***
*** 1513,1526
bool isvarlena;
char *p, *str;
! if (type == BOOLOID)
{
! if (DatumGetBool(value))
! return true;
! else
! return false;
! }
getTypeOutputInfo(type, typeOut, isvarlena);
str = OidOutputFunctionCall(typeOut, value);
--- 1514,1595
bool isvarlena;
char *p, *str;
! /* xsd format doesn't depend on current settings */
! switch (type)
{
! case BOOLOID:
! if (DatumGetBool(value))
! return true;
! else
! return false;
! case DATEOID:
! {
! struct pg_tm tt,
! *tm = tt;
! charbuf[MAXDATELEN + 1];
! DateADT date = DatumGetDateADT(value);
!
! j2date(date + POSTGRES_EPOCH_JDATE,
! (tm-tm_year), (tm-tm_mon), (tm-tm_mday));
!
! EncodeDateOnly(tm, USE_XSD_DATES, buf);
! return pstrdup(buf);
! }
!
! case TIMEOID:
! /* datestyle hasn't affect on time formating */
! break;
!
! case TIMESTAMPOID:
! {
! Timestamp timestamp = DatumGetTimestamp(value);
! struct pg_tm tt,
! *tm = tt;
! fsec_t fsec;
! char *tzn = NULL;
! charbuf[MAXDATELEN + 1];
!
! /* xsd doesn't support infinite values */
! if (TIMESTAMP_NOT_FINITE(timestamp))
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
! errmsg(timestamp out of range)));
! else if (timestamp2tm(timestamp, NULL, tm, fsec, NULL, NULL) == 0)
! EncodeDateTime(tm, fsec, NULL, tzn, USE_XSD_DATES, buf);
! else
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
Pavan Deolasee wrote: When following a HOT-update chain from the index fetch, if we notice that the root tuple is dead and it is HOT-updated, we try to prune the chain to the smallest possible length. To do that, the share lock is upgraded to an exclusive lock and the tuple chain is followed till we find a live/recently-dead tuple. At that point, the root t_ctid is made point to that tuple. In order I assume you meant recently-dead here, rather than live/recently-dead, because we aren't going to change live ctids, right? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
Pavan Deolasee [EMAIL PROTECTED] writes: ... Yes. The HOT-updated status of the root and all intermediate tuples is cleared and their respective ctid pointers are made point to themselves. Doesn't that destroy the knowledge that they form a tuple chain? While it might be that no one cares any longer, it would seem more reasonable to leave 'em chained together. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Pavel Stehule [EMAIL PROTECTED] writes: this patch ensures independency datetime fields on current datestyle setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to USE_ISO_DATESTYLE. Differences are for timestamp: ISO: -mm-dd hh24:mi:ss XSD: -mm-ddThh24:mi:ss Why is that a good idea? Even if some standard out there mandates the 'T', I'd bet lunch that the other format is a whole lot more portable. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Pavel Stehule [EMAIL PROTECTED] writes: this patch ensures independency datetime fields on current datestyle setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to USE_ISO_DATESTYLE. Differences are for timestamp: ISO: -mm-dd hh24:mi:ss XSD: -mm-ddThh24:mi:ss Why is that a good idea? Even if some standard out there mandates the 'T', I'd bet lunch that the other format is a whole lot more portable. if you use xsd schema then situation is clear. I have to respect it, because xsd:datetime is well defined and SQL/XML expect respecting xsd. If I don't use xsd schema, then I teoreticly can put date in any format. Sample: we use german format, but everybody with good mind doesn't use it for xml, because then he cannot use xml validation based on xsd schema. xsd format use Oracle 10g: http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0 9x used ISO format, which is (however) invalid. http://books.xmlschemata.org/relaxng/ch19-77049.html Currently without this patch PostgreSQL generate invalalid xml documents. That is all. My patch doesn't protect any output. Simply use cast to text, or to_char fce. nice a day Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
On 2/20/07, Bruce Momjian [EMAIL PROTECTED] wrote: Pavan Deolasee wrote: When following a HOT-update chain from the index fetch, if we notice that the root tuple is dead and it is HOT-updated, we try to prune the chain to the smallest possible length. To do that, the share lock is upgraded to an exclusive lock and the tuple chain is followed till we find a live/recently-dead tuple. At that point, the root t_ctid is made point to that tuple. In order I assume you meant recently-dead here, rather than live/recently-dead, because we aren't going to change live ctids, right? No, I meant live or recently-dead (in fact, anything other than HEAPTUPLE_DEAD or HEAPTUPLE_DEAD_CHAIN). We are not changing the tids here, but only pruning the HOT-update chain. After pruning, the root-t_ctid points to the oldest tuple that might be visible to any backend. The live tuples are still identified by their original tid and index reachable from the root tuple. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
Pavan Deolasee wrote: On 2/20/07, Bruce Momjian [EMAIL PROTECTED] wrote: Pavan Deolasee wrote: When following a HOT-update chain from the index fetch, if we notice that the root tuple is dead and it is HOT-updated, we try to prune the chain to the smallest possible length. To do that, the share lock is upgraded to an exclusive lock and the tuple chain is followed till we find a live/recently-dead tuple. At that point, the root t_ctid is made point to that tuple. In order I assume you meant recently-dead here, rather than live/recently-dead, because we aren't going to change live ctids, right? No, I meant live or recently-dead (in fact, anything other than HEAPTUPLE_DEAD or HEAPTUPLE_DEAD_CHAIN). We are not changing the tids here, but only pruning the HOT-update chain. After pruning, the root-t_ctid points to the oldest tuple that might be visible to any backend. The live tuples are still identified by their original tid and index reachable from the root tuple. I am confused. Where is the root-t_ctid stored? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
Bruce Momjian [EMAIL PROTECTED] writes: Pavan Deolasee wrote: When following a HOT-update chain from the index fetch, if we notice that the root tuple is dead and it is HOT-updated, we try to prune the chain to the smallest possible length. To do that, the share lock is upgraded to an exclusive lock and the tuple chain is followed till we find a live/recently-dead tuple. At that point, the root t_ctid is made point to that tuple. In order I assume you meant recently-dead here, rather than live/recently-dead, because we aren't going to change live ctids, right? Recently dead means still live to somebody, so those tids better not change either. But I don't think that's what he meant. I'm more worried about the deadlock possibilities inherent in trying to upgrade a buffer lock. We do not have deadlock detection for LWLocks. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Pavan Deolasee wrote: When following a HOT-update chain from the index fetch, if we notice that the root tuple is dead and it is HOT-updated, we try to prune the chain to the smallest possible length. To do that, the share lock is upgraded to an exclusive lock and the tuple chain is followed till we find a live/recently-dead tuple. At that point, the root t_ctid is made point to that tuple. In order I assume you meant recently-dead here, rather than live/recently-dead, because we aren't going to change live ctids, right? Recently dead means still live to somebody, so those tids better not change either. But I don't think that's what he meant. I'm more worried about the deadlock possibilities inherent in trying to upgrade a buffer lock. We do not have deadlock detection for LWLocks. I am guessing he is going to have to release the lock, then ask for an exclusive one. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
On 2/20/07, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: ... Yes. The HOT-updated status of the root and all intermediate tuples is cleared and their respective ctid pointers are made point to themselves. Doesn't that destroy the knowledge that they form a tuple chain? While it might be that no one cares any longer, it would seem more reasonable to leave 'em chained together. I see your point, but as you mentioned do we really care ? The chain needs to be broken so that the intermediate DEAD tuples can be vacuumed. We can't vacuum them normally because they could be a part of live HOT-update chain. Resetting the HOT-updated status of the root tuple helps to mark the index entry LP_DELETE once the entire HOT-update chain is dead. Also, if we decide to reuse the heap-only tuples without even vacuuming, breaking the chain is a better option since we then guarantee no references to the heap-only DEAD tuples. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
On 2/20/07, Bruce Momjian [EMAIL PROTECTED] wrote: Tom Lane wrote: Recently dead means still live to somebody, so those tids better not change either. But I don't think that's what he meant. I'm more worried about the deadlock possibilities inherent in trying to upgrade a buffer lock. We do not have deadlock detection for LWLocks. I am guessing he is going to have to release the lock, then ask for an exclusive one. Yes, thats what is done. Since we try to prune the HOT-update chain even in the SELECT path, we upgrade the lock only if we are sure that there is atleast one tuple that can be removed from the chain or the root needs to be fixed (broken ctid chain for some reason). Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Pavel Stehule wrote: Hello, this patch ensures independency datetime fields on current datestyle setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to USE_ISO_DATESTYLE. Differences are for timestamp: ISO: -mm-dd hh24:mi:ss XSD: -mm-ddThh24:mi:ss Pavel, I agree that we should have some support for XSD date style so that we can produce validatable XML documents. In fact I had to make just such a transformation on data pulled from Postgres recently in application code to get a document to validate. However, I have not seen this topic discussed on -hackers. The way we work is that ideas about features should be discussed there before you submit a patch. For one thing, -hackers has a somewhat wider set of readers than -patches. Also, you might well get good ideas about any likely difficulties. Just lobbing a patch for an undiscussed feature over the wall like this is not good practice. You should get signoff on the idea before you start coding, even for fairly small changes. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] HOT WIP Patch - version 2
On Tue, Feb 20, 2007 at 08:31:45PM +0530, Pavan Deolasee wrote: I see your point, but as you mentioned do we really care ? The chain needs to be broken so that the intermediate DEAD tuples can be vacuumed. We can't vacuum them normally because they could be a part of live HOT-update chain. Resetting the HOT-updated status of the root tuple helps to mark the index entry LP_DELETE once the entire HOT-update chain is dead. ... For some reason this paragraph raised a query in my mind. Will we be able to toggle this new hot update code at configure time, so that we can measure what sort of effect this change has once it is complete? Even if only during the early testing cycles for the next release, I think it would be useful. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Pavel Stehule wrote: Pavel Stehule wrote: Hello, this patch ensures independency datetime fields on current datestyle setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to USE_ISO_DATESTYLE. Differences are for timestamp: ISO: -mm-dd hh24:mi:ss XSD: -mm-ddThh24:mi:ss Pavel, I agree that we should have some support for XSD date style so that we can produce validatable XML documents. In fact I had to make just such a transformation on data pulled from Postgres recently in application code to get a document to validate. However, I have not seen this topic discussed on -hackers. The way we work is that ideas about features should be discussed there before you submit a patch. For one thing, -hackers has a somewhat wider set of readers than -patches. Also, you might well get good ideas about any likely difficulties. Just lobbing a patch for an undiscussed feature over the wall like this is not good practice. You should get signoff on the idea before you start coding, even for fairly small changes. I am sorry. I reported this two times before. This patch is related only for xml functionality. XSD datestyle is only one internal constant. There are no new datestyle (I hope so can be usefull). My patch is small bug fix like Peter's patch for boolean datatype. Generating invalid xml is bug not feature, no? Primary I had to send this patch to Peter. I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? If there was previous email I apologise, as I didn't find it when I looked. Perhaps in such cases you could include a ref to the archive URL. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
On Tue, Feb 20, 2007 at 04:32:28PM +0100, Pavel Stehule wrote: I am sorry. I reported this two times before. This patch is related only for xml functionality. XSD datestyle is only one internal constant. There are no new datestyle (I hope so can be usefull). My patch is small bug fix like Peter's patch for boolean datatype. Generating invalid xml is bug not feature, no? If it's just for XSD, perhaps it should be a part of the XML output functionality instead of being a global datestyle? //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? what? ofcourse you cannot garant validity against any schema. But mapping functions are standardised and expect xsd. And I what I can meet protocols based on xml, they respect xsd everywhere. I repeat. When XML functions don't produce XML schema, then all is possible. But using xsd standard is safe way (like ISO format in SQL world). With nested XML schema whitch is related to xsd there isn't any different way. The best solution is validation XML before output to client. pg have to produce everytime valid xml If there was previous email I apologise, as I didn't find it when I looked. Perhaps in such cases you could include a ref to the archive URL. cheers andrew _ With tax season right around the corner, make sure to follow these few simple tips. http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=HMFebtagline ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
On Tue, Feb 20, 2007 at 04:32:28PM +0100, Pavel Stehule wrote: I am sorry. I reported this two times before. This patch is related only for xml functionality. XSD datestyle is only one internal constant. There are no new datestyle (I hope so can be usefull). My patch is small bug fix like Peter's patch for boolean datatype. Generating invalid xml is bug not feature, no? If it's just for XSD, perhaps it should be a part of the XML output functionality instead of being a global datestyle? I share code and I needed one safe enum value. That's all. There isn't new global datestyle. I didn't want to duplicate code from timestamp.c and date.c. Pavel _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan: I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? That is the xmlschema part of table_to_xmlschema() et al. recently discussed. That entire functionality hinges on producing output that validates against XML Schema schemas, and so we cannot pick the data type formats outselves. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Peter Eisentraut wrote: Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan: I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? That is the xmlschema part of table_to_xmlschema() et al. recently discussed. That entire functionality hinges on producing output that validates against XML Schema schemas, and so we cannot pick the data type formats outselves. Then why would we use a setting to govern this? Should we not simply ensure that we always output timestamps in XML using the correct ISO8601 format? cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
From: Andrew Dunstan [EMAIL PROTECTED] To: Peter Eisentraut [EMAIL PROTECTED] CC: Pavel Stehule [EMAIL PROTECTED], pgsql-patches@postgresql.org Subject: Re: [PATCHES] correct format for date, time, timestamp for XML functionality Date: Tue, 20 Feb 2007 11:37:31 -0500 Peter Eisentraut wrote: Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan: I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? That is the xmlschema part of table_to_xmlschema() et al. recently discussed. That entire functionality hinges on producing output that validates against XML Schema schemas, and so we cannot pick the data type formats outselves. Then why would we use a setting to govern this? Should we not simply ensure that we always output timestamps in XML using the correct ISO8601 format? xsd knows datetime type. You can inherit it, and then you have to respect it. You can do own type, but you lost information, and any general scripts don't understand. I don't know why xsd doesn't iso format, but its simply fact. Please read this discussion: http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0 cheers andrew _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Fast CLUSTER
Simon Riggs wrote:
I've used the heap_sync() API call to improve performance of CLUSTER by
avoiding WAL when archive_command is not set.
Cool. I noticed that the SGML seems broken here:
--- 908,925
will perform more slowly when varnamearchive_command/varname
is set, as a result of their needing to write large amounts of WAL.
This applies to the following commands:
! itemizedlist
! listitemparacommandCREATE TABLE AS
SELECT/command/para/listitem
! listitemparacommandCREATE INDEX/command/para/listitem
! listitemparacommandALTER TABLE SET
TABLESPACE/command/para/listitem
! listitemparacommandCLUSTER/command/para/listitem
! listitemparacommandCOPY/command, when it is executed after one
of
! these commands, yet in the same transaction:
! itemizedlist
!listitemparacommandCREATE TABLE/command/para/listitem
!listitemparacommandTRUNCATE/command/para/listitem
! /itemizedlist
! /itemizedlist
/sect2
You need to close the listitem and para opened in the COPY mention.
+
+ static void
+ heap_sync_relation(Relation rel)
+ {
+ if (!rel-rd_istemp)
No comment in this function?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Pavel Stehule wrote: xsd knows datetime type. You can inherit it, and then you have to respect it. You can do own type, but you lost information, and any general scripts don't understand. I don't know why xsd doesn't iso format, but its simply fact. Please read this discussion: http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0 Ok, I've re-read the patch, and now understand what it's doing. Sorry for the noise. cheers andrew ---(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] Warning about LISTEN names
I'll save the full rant for my blog :), but wanted to submit this documentation patch for this listen gotcha that's been bugging me for a while. I'd like to see LISTEN and NOTIFY changed to use a simple text string, but until then, I think we should probably warn about the chopping off of the left-hand part. Index: listen.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/listen.sgml,v retrieving revision 1.22 diff -c -r1.22 listen.sgml *** listen.sgml 16 Sep 2006 00:30:19 - 1.22 --- listen.sgml 20 Feb 2007 18:18:15 - *** *** 33,38 --- 33,44 class=PARAMETERname/replaceable. If the current session is already registered as a listener for this notification condition, nothing is done. +Note that because replaceable class=PARAMETERname/replaceable +is a relation name, all but the last section will be dropped if the +name has any dots in it: LISTEN employee, LISTEN newyork.employee, +and LISTEN unitedstates.newyork.employee all register the name +employee. Users needing to separate words should use an underscore +instead of a dot. /para para ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Warning about LISTEN names
Greg Sabino Mullane [EMAIL PROTECTED] writes: I'll save the full rant for my blog :), but wanted to submit this documentation patch for this listen gotcha that's been bugging me for a while. I'd like to see LISTEN and NOTIFY changed to use a simple text string, but until then, I think we should probably warn about the chopping off of the left-hand part. Let's change it to a plain ColId, so you get a syntax error if you try that. 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
Re: [PATCHES] Warning about LISTEN names
Greg Sabino Mullane said: I'll save the full rant for my blog :), but wanted to submit this documentation patch for this listen gotcha that's been bugging me for a while. Why not just change LISTEN, NOTIFY, and UNLISTEN to only accept an unqualified identifier? -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Re: [BUGS] BUG #2942: information_schema.element_types: documentation error
Thanks. I have updated the documentation with the attached patch, and backpatched it to 8.2.X. --- Kirill Simonov wrote: The following bug has been logged online: Bug reference: 2942 Logged by: Kirill Simonov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Linux Description:information_schema.element_types: documentation error Details: The page http://www.postgresql.org/docs/8.2/interactive/infoschema-element-types.html contains obsolete information. The example query SELECT c.column_name, c.data_type, e.data_type AS element_type FROM information_schema.columns c LEFT JOIN information_schema.element_types e ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier) = (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.array_type_identifier)) WHERE c.table_schema = '...' AND c.table_name = '...' ORDER BY c.ordinal_position; fails with ERROR: column e.array_type_identifier does not exist. The fix is to replace 'e.array_type_identifier' with e'dtd_identifier'. The column array_type_identifier described in the Table 32-17 does not exists. The column dtd_identifier, which is described as This is currently not useful., should have he description of the former array_type_identifier column. ---(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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/information_schema.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v retrieving revision 1.31 diff -c -c -r1.31 information_schema.sgml *** doc/src/sgml/information_schema.sgml 1 Feb 2007 00:28:17 - 1.31 --- doc/src/sgml/information_schema.sgml 20 Feb 2007 18:39:34 - *** *** 1876,1882 SELECT c.column_name, c.data_type, e.data_type AS element_type FROM information_schema.columns c LEFT JOIN information_schema.element_types e ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier) != (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.array_type_identifier)) WHERE c.table_schema = '...' AND c.table_name = '...' ORDER BY c.ordinal_position; /programlisting --- 1876,1882 SELECT c.column_name, c.data_type, e.data_type AS element_type FROM information_schema.columns c LEFT JOIN information_schema.element_types e ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier) != (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.dtd_identifier)) WHERE c.table_schema = '...' AND c.table_name = '...' ORDER BY c.ordinal_position; /programlisting *** *** 1936,1948 /row row ! entryliteralarray_type_identifier/literal/entry entrytypesql_identifier/type/entry entry The identifier of the data type descriptor of the array being !described. Use this to join with the !literaldtd_identifier/literal columns of other information !schema views. /entry /row --- 1936,1946 /row row ! entryliteraldtd_identifier/literal/entry entrytypesql_identifier/type/entry entry The identifier of the data type descriptor of the array being !described /entry /row *** *** 2097,2109 entryAlways null, because arrays always have unlimited maximum cardinality in productnamePostgreSQL//entry /row - row - entryliteraldtd_identifier/literal/entry - entrytypesql_identifier/type/entry - entry -An identifier of the data type descriptor of the element. This -is currently not useful. - /entry /row /tbody /tgroup --- 2095,2100 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Warning about LISTEN names
Tom Lane wrote: Greg Sabino Mullane [EMAIL PROTECTED] writes: I'll save the full rant for my blog :), but wanted to submit this documentation patch for this listen gotcha that's been bugging me for a while. I'd like to see LISTEN and NOTIFY changed to use a simple text string, but until then, I think we should probably warn about the chopping off of the left-hand part. Let's change it to a plain ColId, so you get a syntax error if you try that. Makes sense. I'm still going to try to get notification payloads done for 8.3, which will remove any requirement of catalog support and do it all in shared memory. Should we perhaps support a variant that allows a string as opposed to an identifier as the name? LISTEN 'really_really_really_really_really_really_really_really_really_really_really_really_long_name' ; Or is that just silly? ;-) cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Warning about LISTEN names
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Let's change it to a plain ColId, so you get a syntax error if you try that. Should we perhaps support a variant that allows a string as opposed to an identifier as the name? I think that'd just confuse matters. You can double-quote a string that you want to use that isn't otherwise a valid identifier. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] pg_standby Error cleanup
Please find attached a patch which provides for logging in the event that -k
is unable to clean up an old WAL file. Also make the failed to remove file
error message consistant for the trigger file.
--
Darcy Buskermolen
Command Prompt, Inc.
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997
http://www.commandprompt.com/
? pg_standby.c.patch
Index: pg_standby.c
===
RCS file: /projects/cvsroot/pgsql/contrib/pg_standby/pg_standby.c,v
retrieving revision 1.2
diff -u -c -r1.2 pg_standby.c
*** pg_standby.c 10 Feb 2007 19:52:45 - 1.2
--- pg_standby.c 20 Feb 2007 19:58:04 -
***
*** 264,273
#else
snprintf(WALFilePath, MAXPGPATH, %s/%s, archiveLocation, xlde-d_name);
#endif
- rc = unlink(WALFilePath);
if (debug)
! fprintf(stderr, \npg_standby: removed \%s\\n, WALFilePath);
}
}
}
--- 264,278
#else
snprintf(WALFilePath, MAXPGPATH, %s/%s, archiveLocation, xlde-d_name);
#endif
if (debug)
! fprintf(stderr, \npg_standby: removing \%s\\n, WALFilePath);
!
! rc = unlink(WALFilePath);
! if (rc !=0 )
! fprintf(stderr, \npg_standby: ERROR failed to remove \%s\ because %s\n, WALFilePath, strerror(errno));
!
!
}
}
}
***
*** 315,321
rc = unlink(triggerPath);
if (rc != 0)
{
! fprintf(stderr, \n ERROR: unable to remove \%s\, rc=%d, triggerPath, rc);
fflush(stderr);
exit(rc);
}
--- 320,326
rc = unlink(triggerPath);
if (rc != 0)
{
! fprintf(stderr, \n ERROR: unable to remove \%s\, because %s, triggerPath, strerror(errno));
fflush(stderr);
exit(rc);
}
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PATCHES] [pgsql-patches] pltcl/plython fixes for spi_prepare types
Uh, I haven't seen this applied yet. --- Andrew Dunstan wrote: Here's a patch along the same lines as the fix for plperl committed earlier today, that allows passing type aliases to spi_prepare as well as types named in pg_type. It also removes the mention of the previous limitation in the pltcl docs. Unlike the plperl and pltcl cases, I didn't use the simpler form that Tom suggested for plpython, as that code wants to get hold of the HeapTuple. If anyone wants to tidy that up some, feel free. Also, some regression tests from those with more tcl-fu or python-fu that I have would be nice. I'll apply this in a day or two unless there's an objection. cheers andrew ---(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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [pgsql-patches] Ctid chain following enhancement
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Pavan Deolasee wrote: On 1/28/07, Tom Lane [EMAIL PROTECTED] wrote: OTOH it might be cleaner to refactor things that way, if we were going to apply this. Here is a revised patch which includes refactoring of heap_get_latest_tid(), as per Tom's suggestion. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] ---(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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [pgsql-patches] [HACKERS] less privileged pl install
The most recent version of this patch has been added. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Jeremy Drake wrote: On Thu, 25 Jan 2007, Jeremy Drake wrote: On Thu, 25 Jan 2007, Jeremy Drake wrote: I think that an ALTER LANGUAGE OWNER TO is the proper response to these things, and unless I hear otherwise I will attempt to add this to my patch. Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME TO for the owner, which I missed before. I would appreciate someone with more knowledge of the permissions infrastructure to take a look at it since I am fairly new to it and may not fully understand its intricacies. I have refactored the owner checking of languages in the same manner as it is for other owned objects. I have changed to using standard permissions error messages (aclcheck_error) for the language permissions errors. I consider this patch ready for review, assuming the permissions rules outlined by Tom Lane on -hackers are valid. For reference, here are the rules that this patch is intended to implement: On Wed, 24 Jan 2007, Tom Lane wrote: In detail, it'd look something like: * For an untrusted language: must be superuser to either create or use the language (no change from current rules). Ownership of the pg_language entry is really irrelevant, as is its ACL. * For a trusted language: * if pg_pltemplate.something is ON: either a superuser or the current DB's owner can CREATE the language. In either case the pg_language entry will be marked as owned by the DB owner (pg_database.datdba), which means that subsequently he (or a superuser) can grant or deny USAGE within his DB. * if pg_pltemplate.something is OFF: must be superuser to CREATE the language; subsequently it will be owned by you, so only you or another superuser can grant or deny USAGE (same behavior as currently). The only difference from this is, that when superuser is required, the owner of the language is not the superuser who created it, but BOOTSTRAP_SUPERUSERID. This is because my interpretation was that the same behavior as currently took precedence. The current behavior in cvs is that languages have no owner, and for purposes where one would be needed it is assumed to be BOOTSTRAP_SUPERUSERID. Is this valid, or should I instead set the owner to GetUserId() in those cases? -- Academic politics is the most vicious and bitter form of politics, because the stakes are so low. -- Wallace Sayre Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [pgsql-patches] pltcl/plython fixes for spi_prepare types
Oops.! That one got away. I'll work on it tonight. cheers andrew Bruce Momjian wrote: Uh, I haven't seen this applied yet. --- Andrew Dunstan wrote: Here's a patch along the same lines as the fix for plperl committed earlier today, that allows passing type aliases to spi_prepare as well as types named in pg_type. It also removes the mention of the previous limitation in the pltcl docs. Unlike the plperl and pltcl cases, I didn't use the simpler form that Tom suggested for plpython, as that code wants to get hold of the HeapTuple. If anyone wants to tidy that up some, feel free. Also, some regression tests from those with more tcl-fu or python-fu that I have would be nice. I'll apply this in a day or two unless there's an objection. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [pgsql-patches] scrollable cursor support for plpgsql
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Pavel Stehule wrote: Hello this patch contains ansi sql scrollable cursors's support for plpgsql. Add three function to SPI and plpgsql scrollable cursor sup. is first test app of this functionality. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] guc patch: Make variables fall back to default values
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Joachim Wieland wrote: Attached is the long-awaited guc patch that makes values fall back to their default values when they got removed (or commented) from the configuration file. This has always been a source of confusion. There are three not-so-obvious cases that I'd like to comment: First one: In the configuration file you have: seq_page_cost = 3 (the default for this option is 1) You start the database and issue SET seq_page_cost TO 4. Then you remove the seq_page_cost definition from the configuration file and send SIGHUP. If you now do a RESET seq_page_cost it will fall back to 1 and not to 3. Second one: You have custom_variable_classes = foo You start a transaction and do SET foo.bar to 4. Now you remove the custom_variable_classes definition and it falls back to being empty. Hence all foo.* variables become invalid. You cannot COMMIT the transaction and COMMIT results in a transaction abort. Third one: In the configuration file you have custom_variable_classes = foo foo.bar = 3 You start a transaction and do SET foo.bar to 4. Then you remove the definition of foo.bar but you keep the custom_variable_classes definition. COMMITting the transaction succeeds but since foo.bar does not exist in the configuration file anymore, your SET command is considered to define a new variable and executing RESET foo.bar does not change the variable (without any change to the configuration file it would remove your setting and restore the setting from the configuration file for foo.bar). Everything else should be quite straightforward. It is also intended that if you have changed (or commented) a variable in the configuration file that cannot be applied (because a parameter can only be changed at server start) you will get this message every time you send a SIGHUP. That way you can see if your configuration file matches your current server configuration. Comments welcome, Joachim [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] patch adding new regexp functions
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Jeremy Drake wrote: On Sun, 18 Feb 2007, Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: I will rename the functions regexp_split_to_(table|array) and I will add an optional limit parameter to the regexp_split_to_table function, for consistency and to avoid ordering concerns with LIMIT. I'd go the other way: get rid of the limit option all 'round. It seems like fairly useless complexity. OK, here is what is hopefully the final version of this patch. I have renamed the functions as above, and removed the optional limit parameter. If there are no further complaints, this should be ready to apply. -- It is the business of little minds to shrink. -- Carl Sandburg Content-Description: [ Attachment, skipping... ] ---(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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [pgsql-patches] O_DIRECT support for Windows
Are there any performance numbers on this? --- ITAGAKI Takahiro wrote: The attached is a patch to define O_DIRECT by ourselves on Windows, and to map O_DIRECT to FILE_FLAG_NO_BUFFERING. There will be a consistency in our support between Windows and other OSes that have O_DIRECT. Also, there is the following comment that says, I read, we should do so. | handle other flags? (eg FILE_FLAG_NO_BUFFERING/FILE_FLAG_WRITE_THROUGH) Is this worth doing? Do we need more performance reports for the change? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] xpath_array with namespaces support
As a result of discussion with Peter, I provide modified patch for
xpath_array() with namespaces support.
The signature is:
_xml xpath_array(text xpathQuery, xml xmlValue[, _text namespacesBindings])
The third argument is 2-dimensional array defining bindings for
namespaces. Simple examples:
xmltest=# SELECT xpath_array('//text()', 'local:data
xmlns:local=http://127.0.0.1;local:piece id=1number
one/local:piecelocal:piece id=2 //local:data');
xpath_array
{number one}
(1 row)
xmltest=# SELECT xpath_array('//loc:piece/@id', 'local:data
xmlns:local=http://127.0.0.1;local:piece id=1number
one/local:piecelocal:piece id=2 //local:data',
ARRAY[ARRAY['loc'], ARRAY['http://127.0.0.1']]);
xpath_array
-
{1,2}
(1 row)
Thoughts regarding other XPath functions were exposed a couple of days
ago: http://archives.postgresql.org/pgsql-patches/2007-02/msg00373.php
If there is no objections, we could call the function provided in this
patch as xpath() or xmlpath() (the latter is similar to SQL/XML
functions).
Also, maybe someone can suggest better approach for passing namespace
bindings (more convenient than ARRAY[ARRAY[...], ARRAY[...]])?
--
Best regards,
Nikolay
Index: src/backend/utils/adt/xml.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.31
diff -u -r1.31 xml.c
--- src/backend/utils/adt/xml.c 16 Feb 2007 18:37:43 - 1.31
+++ src/backend/utils/adt/xml.c 20 Feb 2007 23:20:54 -
@@ -47,6 +47,8 @@
#include libxml/uri.h
#include libxml/xmlerror.h
#include libxml/xmlwriter.h
+#include libxml/xpath.h
+#include libxml/xpathInternals.h
#endif /* USE_LIBXML */
#include catalog/namespace.h
@@ -65,6 +67,7 @@
#include utils/builtins.h
#include utils/lsyscache.h
#include utils/memutils.h
+#include access/tupmacs.h
#include utils/xml.h
@@ -86,6 +89,7 @@
static int parse_xml_decl(const xmlChar *str, size_t *lenp, xmlChar **version, xmlChar **encoding, int *standalone);
static bool print_xml_decl(StringInfo buf, const xmlChar *version, pg_enc encoding, int standalone);
static xmlDocPtr xml_parse(text *data, XmlOptionType xmloption_arg, bool preserve_whitespace, xmlChar *encoding);
+static text *xml_xmlnodetotext(xmlNodePtr cur);
#endif /* USE_LIBXML */
@@ -1463,7 +1467,6 @@
return buf.data;
}
-
/*
* Map SQL value to XML value; see SQL/XML:2003 section 9.16.
*/
@@ -2334,3 +2337,238 @@
else
appendStringInfoString(result, /row\n\n);
}
+
+
+/*
+ * XPath related functions
+ */
+
+/*
+ * Convert XML node to text (return only value, it's not dumping)
+ */
+text *
+xml_xmlnodetotext(xmlNodePtr cur)
+{
+ xmlChar *str;
+ text *result;
+ size_t len;
+
+ str = xmlXPathCastNodeToString(cur);
+ len = strlen((char *) str);
+ result = (text *) palloc(len + VARHDRSZ);
+ VARATT_SIZEP(result) = len + VARHDRSZ;
+ memcpy(VARDATA(result), str, len);
+
+ return result;
+}
+
+/*
+ * Evaluate XPath expression and return array of XML values.
+ * As we have no support of XQuery sequences yet, this functions seems
+ * to be the most useful one (array of XML functions plays a role of
+ * some kind of substritution for XQuery sequences).
+
+ * Workaround here: we parse XML data in different way to allow XPath for
+ * fragments (see XPath for fragment TODO comment inside).
+ */
+Datum
+xpath_array(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+ ArrayBuildState *astate = NULL;
+ xmlParserCtxtPtr ctxt = NULL;
+ xmlDocPtr doc = NULL;
+ xmlXPathContextPtr xpathctx = NULL;
+ xmlXPathCompExprPtr xpathcomp = NULL;
+ xmlXPathObjectPtr xpathobj = NULL;
+ int32len, xpath_len;
+ xmlChar*string, *xpath_expr;
+ boolres_is_null = FALSE;
+ int i;
+ xmltype*data = PG_GETARG_XML_P(1);
+ text*xpath_expr_text = PG_GETARG_TEXT_P(0);
+ ArrayType *namespaces;
+ int *dims, ndims, ns_count = 0, bitmask = 1;
+ char*ptr;
+ bits8*bitmap;
+ char**ns_names = NULL, **ns_uris = NULL;
+ int16typlen;
+ booltypbyval;
+ chartypalign;
+
+ /* Namespace mappings passed as text[].
+ * Assume that 2-dimensional array has been passed,
+ * the 1st subarray is array of names, the 2nd -- array of URIs,
+ * example: ARRAY[ARRAY['myns', 'myns2'], ARRAY['http://example.com', 'http://example2.com']].
+ */
+ if (!PG_ARGISNULL(2))
+ {
+ namespaces = PG_GETARG_ARRAYTYPE_P(2);
+ ndims = ARR_NDIM(namespaces);
+ dims = ARR_DIMS(namespaces);
+
+ /* Sanity check */
+ if (ndims != 2)
+ ereport(ERROR, (errmsg(invalid array passed for namespace mappings),
+ errdetail(Only 2-dimensional array may be used for namespace mappings.)));
+
+ Assert(ARR_ELEMTYPE(namespaces) == TEXTOID);
+
+ ns_count = ArrayGetNItems(ndims, dims) / 2;
+ get_typlenbyvalalign(ARR_ELEMTYPE(namespaces),
+ typlen, typbyval, typalign);
+ ns_names = (char **) palloc(ns_count * sizeof(char *));
+ ns_uris = (char **) palloc(ns_count * sizeof(char *));
+ ptr =
Re: [PATCHES] [pgsql-patches] [HACKERS] less privileged pl install
On Tue, 20 Feb 2007, Bruce Momjian wrote: The most recent version of this patch has been added. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. Cool, I was going to bring this up again once the regexp patch got in. There is one thing in this patch I was not sure on, and that is in AlterLanguageOwner what should the second parameter of heap_close be? I have RowExclusiveLock in the patch, but I am not sure that is correct. It would be good if someone more knowledgeable about such things checked on this when applying it... The latest version of the patch is currently at http://momjian.us/mhonarc/patches/msg00014.html --- Jeremy Drake wrote: On Thu, 25 Jan 2007, Jeremy Drake wrote: On Thu, 25 Jan 2007, Jeremy Drake wrote: I think that an ALTER LANGUAGE OWNER TO is the proper response to these things, and unless I hear otherwise I will attempt to add this to my patch. Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME TO for the owner, which I missed before. I would appreciate someone with more knowledge of the permissions infrastructure to take a look at it since I am fairly new to it and may not fully understand its intricacies. I have refactored the owner checking of languages in the same manner as it is for other owned objects. I have changed to using standard permissions error messages (aclcheck_error) for the language permissions errors. I consider this patch ready for review, assuming the permissions rules outlined by Tom Lane on -hackers are valid. For reference, here are the rules that this patch is intended to implement: On Wed, 24 Jan 2007, Tom Lane wrote: In detail, it'd look something like: * For an untrusted language: must be superuser to either create or use the language (no change from current rules). Ownership of the pg_language entry is really irrelevant, as is its ACL. * For a trusted language: * if pg_pltemplate.something is ON: either a superuser or the current DB's owner can CREATE the language. In either case the pg_language entry will be marked as owned by the DB owner (pg_database.datdba), which means that subsequently he (or a superuser) can grant or deny USAGE within his DB. * if pg_pltemplate.something is OFF: must be superuser to CREATE the language; subsequently it will be owned by you, so only you or another superuser can grant or deny USAGE (same behavior as currently). The only difference from this is, that when superuser is required, the owner of the language is not the superuser who created it, but BOOTSTRAP_SUPERUSERID. This is because my interpretation was that the same behavior as currently took precedence. The current behavior in cvs is that languages have no owner, and for purposes where one would be needed it is assumed to be BOOTSTRAP_SUPERUSERID. Is this valid, or should I instead set the owner to GetUserId() in those cases? -- Academic politics is the most vicious and bitter form of politics, because the stakes are so low. -- Wallace Sayre Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- A UNIX saleslady, Lenore, Enjoys work, but she likes the beach more. She found a good way To combine work and play: She sells C shells by the seashore. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] BLCKSZ fun facts
I have implemented your ideas for checking BLCKSZ = 1024, and having
initdb adjust shared buffers checks based on BLCKSZ.
Patch attached and applied.
---
Peter Eisentraut wrote:
The smallest BLCKSZ that you can compile is 256. But ...
The smallest BLCKSZ that actually works is 1024, because of this code in
guc.c:
case GUC_UNIT_BLOCKS:
val /= (BLCKSZ / 1024);
Maybe it's worth adding an #error here to prevent smaller sizes being
used?
The smallest BLCKSZ that passes the regression tests is 4096. With
smaller settings your get half a dozen ordering differences, which
seems OK.
The shared memory configuration code in initdb doesn't know about
BLCKSZ, so with smaller sizes you get less shared buffers. Maybe that
is worth fixing sometime.
Aside from that my pgbench testing clearly shows that block sizes larger
than 2048 become progressively slower. Go figure.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/initdb/initdb.c
===
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.133
diff -c -c -r1.133 initdb.c
*** src/bin/initdb/initdb.c 16 Feb 2007 02:10:07 - 1.133
--- src/bin/initdb/initdb.c 20 Feb 2007 23:46:19 -
***
*** 1208,1214
for (i = 0; i bufslen; i++)
{
! test_buffs = trial_bufs[i];
if (test_buffs = ok_buffers)
{
test_buffs = ok_buffers;
--- 1208,1215
for (i = 0; i bufslen; i++)
{
! /* Use same amount of memory, independent of BLCKSZ */
! test_buffs = (trial_bufs[i] * 8192) / BLCKSZ;
if (test_buffs = ok_buffers)
{
test_buffs = ok_buffers;
Index: src/include/pg_config_manual.h
===
RCS file: /cvsroot/pgsql/src/include/pg_config_manual.h,v
retrieving revision 1.24
diff -c -c -r1.24 pg_config_manual.h
*** src/include/pg_config_manual.h 6 Feb 2007 09:16:08 - 1.24
--- src/include/pg_config_manual.h 20 Feb 2007 23:46:19 -
***
*** 25,30
--- 25,34
*/
#define BLCKSZ 8192
+ #if BLCKSZ 1024
+ #error BLCKSZ must be = 1024
+ #endif
+
/*
* RELSEG_SIZE is the maximum number of blocks allowed in one disk
* file. Thus, the maximum size of a single file is RELSEG_SIZE *
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] WIP patch - INSERT-able log statements
Ok. Summarizing the key changes required on my patch, based on the discussions so far are : - The log_destination will include a new option 'sql'. This can be given with other combinations of stderr, syslog or eventlog. - The sql logs will be written in log_directory in a file log_filename.SQL - The log output will be in COPY format and will include the following information, irrespective of the log_line_prefix setting. ( timestamp_with_milliseconds, timestamp, username, databasename, sessionid, host_and_port, host, proc_id, command_tag, session_start, transaction_id, error_severity, SQL_State_Code, statement/error_message); Anything else missing ? ? Rgds, Arul Shaji On Tue, 20 Feb 2007 19:03, Guillaume Smet wrote: On 2/20/07, Tom Lane [EMAIL PROTECTED] wrote: Of course, the other side of that coin is that syslog is known to drop messages altogether under sufficient load. (At least on some platforms; dunno about yours.) Yes I know. That's one of the reason why I asked for the bahaviour of 7.4 log_duration back in 8.2. It's a good compromise which allows us not to lose lines and have a good level of information (at least, the best we can have). Async IO helps. Moreover we use syslog to send the log lines via UDP so we know that it's not perfect. But it works nice most of the time. We know that we can't log every query (we use a combination of log_duration and log_min_duration_statement - I patched 8.1 for that) because if we do so we lose a lot of lines and queries are not consistent but we can't do it locally with stderr anyway due to I/O. This method has been reliable for more than a year and our daily reports are consistent. This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] WIP patch - INSERT-able log statements
FAST PostgreSQL [EMAIL PROTECTED] writes: - The log output will be in COPY format and will include the following information, irrespective of the log_line_prefix setting. ( timestamp_with_milliseconds, timestamp, username, databasename, sessionid, host_and_port, host, proc_id, command_tag, session_start, transaction_id, error_severity, SQL_State_Code, statement/error_message); How exactly are you fitting the message structure (primary/detail/context lines) into this? It looks like your proposal loses that structure ... A smaller problem is that this forces people to incur a gettimeofday call for every message logged; depending on your hardware that can be a pretty nasty overhead. Some people might find some of the other columns not worth their weight, either. Is it worth providing a knob to determine the set of columns emitted? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP patch - INSERT-able log statements
On Wed, 21 Feb 2007 12:08, Tom Lane wrote: FAST PostgreSQL [EMAIL PROTECTED] writes: - The log output will be in COPY format and will include the following information, irrespective of the log_line_prefix setting. ( timestamp_with_milliseconds, timestamp, username, databasename, sessionid, host_and_port, host, proc_id, command_tag, session_start, transaction_id, error_severity, SQL_State_Code, statement/error_message); How exactly are you fitting the message structure (primary/detail/context lines) into this? It looks like your proposal loses that structure ... Sorry, didn't understand Can you please elaborate ? A smaller problem is that this forces people to incur a gettimeofday call for every message logged; depending on your hardware that can be a pretty nasty overhead. Some people might find some of the other columns not worth their weight, either. Is it worth providing a knob to determine the set of columns emitted? Totally agree. My original patch infact uses log_line_prefix. So the user can fill in the columns he wants by turning on appropriate settings in log_line_prefix. The columns which he hasn't turned on will be output as NULL in the sql outptut. But I can also see merit in others' request that it is ideal to have all possible info in the sql log, so that once the log is loaded into the table, it can be queried, sub-tabled, created view to analyze it in whatever way. Rgds, Arul Shaji regards, tom lane This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [pgsql-patches] pltcl/plython fixes for spi_prepare types
now applied. cheers andrew Bruce Momjian wrote: Uh, I haven't seen this applied yet. --- Andrew Dunstan wrote: Here's a patch along the same lines as the fix for plperl committed earlier today, that allows passing type aliases to spi_prepare as well as types named in pg_type. It also removes the mention of the previous limitation in the pltcl docs. Unlike the plperl and pltcl cases, I didn't use the simpler form that Tom suggested for plpython, as that code wants to get hold of the HeapTuple. If anyone wants to tidy that up some, feel free. Also, some regression tests from those with more tcl-fu or python-fu that I have would be nice. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] WIP patch - INSERT-able log statements
On Tue, 20 Feb 2007, Tom Lane wrote: A smaller problem is that this forces people to incur a gettimeofday call for every message logged I'm stumped trying to think of an application that would require importing the logs into a database to analyze them, but not need the timestamp. I'd expect it to be the primary key on the data. Is it worth providing a knob to determine the set of columns emitted? Myself and Guillaume felt that having the format be standardized had significant value from a downstream application perspective; it would be nice to know that everyone can work together to write one simple tool chain to process these things and it would work everywhere. The current level of log output customization is part of what makes log analysis tools so much of a pain. How about this as a simple way to proceed: have the patch include everything, as Arul already planned. When it's done, do some benchmarking with it turned on or off. If it really seems like a drag, then consider a GUC addition to trim it down. Why optimize prematurely? It's not like this will be on by default. My guess is that the person sophisticated to analyze their logs probably has an installation that can support the overhead. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP patch - INSERT-able log statements
On Wed, 21 Feb 2007 14:59, Greg Smith wrote: On Tue, 20 Feb 2007, Tom Lane wrote: A smaller problem is that this forces people to incur a gettimeofday call for every message logged I'm stumped trying to think of an application that would require importing the logs into a database to analyze them, but not need the timestamp. I'd expect it to be the primary key on the data. Is it worth providing a knob to determine the set of columns emitted? Myself and Guillaume felt that having the format be standardized had significant value from a downstream application perspective; it would be Come to think of it, this may not be ideal after all. As we are triggering the sql output in log_destination, if the user gives 'syslog,sql' as options he is going to get two different looking logs (in terms of contents) depending upon his settings. But if we take the settings from log_line_prefix then the log contents are the same, plus it gives the user flexibility to control what he wants. If an user wants everything he only has to fill the log_line_prefix completely. Also, for a meaningful sql log output we may need to tell the user not to turn on verbose or print_plan or statistics etc... With a uniform log output it will be clear in that sense.. What he sets in .conf is what he gets, both in syslog and sql log. This may not be an optimization. Only an option which is there if any optimization is necessary. I am happy to implement it either way though. My requirement is same as yours. I want some sort of sql logging, pronto. Rgds, Arul Shaji nice to know that everyone can work together to write one simple tool chain to process these things and it would work everywhere. The current level of log output customization is part of what makes log analysis tools so much of a pain. How about this as a simple way to proceed: have the patch include everything, as Arul already planned. When it's done, do some benchmarking with it turned on or off. If it really seems like a drag, then consider a GUC addition to trim it down. Why optimize prematurely? It's not like this will be on by default. My guess is that the person sophisticated to analyze their logs probably has an installation that can support the overhead. This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you. If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
