Re: [HACKERS] Block nested loop join
Bramandia Ramadhana wrote: Currently, I am investigating the nested loop join algorithm in nodeNestloop.c. After reading the code, my understanding is that it performs simple nested loop join (not block nested loop join). Is this true? Yep. Does postgresql support block nested loop join? Nope. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] head's linking problem
On 2008-10-09, at 22:57, Alvaro Herrera wrote: Grzegorz Jaskiewicz wrote: On 2008-10-09, at 21:25, Alvaro Herrera wrote: Yeah, my bad. What's the platform? mac os x Hmm, does your link line include -lintl? If not, does it successfully link if you add it? ld: library not found for -lintl (sorry for previous one, I was tired and it was late already here) PGP.sig Description: This is a digitally signed message part
Re: [HACKERS] patch: Allow the UUID type to accept non-standard formats
Robert Haas wrote: While we could perhaps accept only those variant formats which we specifically know someone to be using, it seems likely that people will keep moving those pesky dashes around, and we'll likely end up continuing to add more formats and arguing about which ones are widely enough used to deserve being on the list. So my vote is - as long as they don't put a dash in the middle of a group of four (aka a byte), just let it go. I somewhat disagree with supporting other formats. Reasons include: 1) Reduced error checking. 2) The '-' is not the only character that people have used. ClearCase uses '.' and ':' as punctuation. 3) People already have the option of translating the UUID from their application to a standard format. 4) As you find below, and is probably possible to improve on, a fixed format can be parsed more efficient. Somewhat to my surprise, this implementation appears to be about 2-3% slower than the one it replaces, as measured using a trivial test harness. I would have thought that eliminating a call to strlen() and an extra copy of the data would have actually picked up some speed, but it seems not. Any thoughts on the reason? In any case, I don't believe there's any possible use case where a 2-3% slowdown in uuid_to_string is actually perceptible to the user, since I had to call it 100 million times in a tight loop to measure it. I don't know which implementation was used for the PostgreSQL core, but any hard coded constants would allow for the optimizer to generate instructions that can run in parallel, or that are better aligned to machine words. 2-3% slow down for what gain? It still doesn't handle all cases, and it's less able to check the format for correctness. Cheers, mark -- Mark Mielke <[EMAIL PROTECTED]> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
Tom Lane wrote: Ron Mayer <[EMAIL PROTECTED]> writes: Tom Lane wrote: We could try to do the same in the float case, but I'm a bit worried about finding ourselves showing "1234567.79" ... If I understand the code right [I didn't...] The problem is ... seconds field that includes hours, minutes, seconds, and fractional seconds...Here's an example... regression=# select '1234567890 hours 0.123 sec'::interval; ... 1234567890:00:00.123047 Hmm. That's also an existence proof that we're not too concerned about showing 6 imprecise digits anyway (at least for some 8.3 DateStyles). Doesn't seem like it'd hurt too much if we show them for all the IntervalStyles. Since there's a (somewhat arbitrary) limitation of the hours to 2^31, this is close to the worst possible case. (Hm, maybe someone actually did the math and decided that 2 fractional digits ... Or I guess we could truncate to 2 digits only in the float case; or truncate to 2 digits only if we're using the float case and have large values. But that extra complexity doesn't seem worth it to me - especially since it seems to only affect people who do two non-default things (pick a date/interval style that used to truncate to 2, and --disable-integer-datetimes). I put a patch up at http://0ape.com/postgres_interval_patches that does what I think seems getting reasonable. For better or worse, it depends on the other two interval patches I was working on, but I could make a version that doesn't depend on those as well if people prefer that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches
Andrew Sullivan wrote: In my vision, Apache assigns its contents handler an individual security context based on HTTP authentication, source IP address and so on just before web application invoked. Because web applications works with individual least privilege set, its accesses on filesystem are restricted by the security policy. In a similar way, its accesses on databases are also restricted via SE-PostgreSQL by same policy, by same privilege set. I want to focus on this description, because you appear to be limiting the problem scope tremendously here. We've moved from "general security policy for database system" to "security policy for database system as part of a web-application stack". The "general security policy for database system" is an incorrect term. SELinux does not cover database system only. It covers operating sytem and application managing objects (like database object, X window, ...). Thus, it should be talked as "general security policy for operating system, database system and so on". A web application stack is one of the most benefitical example. Please consider what is contained within web-applications. It accesses objects managed by operating system (like files), objects managed by database system (like tables) concurrently, but existing system does not alllow to manage them under a single unified access control policy. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <[EMAIL PROTECTED]> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches
Andrew Sullivan wrote: On Wed, Oct 08, 2008 at 11:36:19AM +0900, KaiGai Kohei wrote: Yes, unfortunatelly. No one replied to my proposed design: http://marc.info/?l=pgsql-hackers&m=12470930544&w=2 FWIW, I didn't know what to say about that proposal because I still don't know what problems any of this is trying to solve. Please note that the above row-level permission works independently and exclusively with SE-PostgreSQL. It simply applies DAC policy inside RDBMS with per-tuple granuality, and will help fine-graned access control independent from MAC polisy or operating system. 1. Single-policy access control for all objects available in a system. I'm guessing that this is the point of SE-PostgreSQL. It also appears to me that the SE-Linux approach may not have completely defined how these things work in a database context, Its specification got a bit legacy now, so I have a plan to revise it later, as Peter montioned before. Indeed, SELinux does not define detailed behavior in database context and it depends on architects. For example, we can implement column level access control with replacing violated columns by NULL. SE-PostgreSQL does not adopt such approach, but it was an option. Perhaps, someone adopt this approach when he tries to make SE-MySQL. :-) 2. Granular row-level access control based on database-level ACLs. I have formed the impression that there is some support of this sort needed anyway to implement (1), but maybe not. I guess this is the proposal you mention. As I mentioned at first, the row-level ACL does not intend to apply a single unified security policy. It is an extention of existing database-level ACL, and works independently from the philosophy of SE-PostgreSQL. What is the goal here? Simply administrator-defined controls on > data access inside the database? Yes, it is a simple extention of existing ACL system to per-tuple granuality, not something more than. 3. Column-level access controls. This is ruled out of the current discussion in the proposal mentioned above. Surely you need column-level access controls to make (1) work, though? If not, then I'm even more confused than I thought. Now Stephen Frost is working for Column-level permission based on the SQL standard, as a core facility of PostgreSQL. The row-level permission is an optional facility, because we have no standard to be refered. Please note that several major commercial RDBMS also have its optional facility to provide row-level permission, like Oracle Label Security, Oracle Virtual Private Database and DB2 LBAC. 4. Metadata-level access controls. None of the proposals so far seem to provide a complete set of access controls for the system details -- schemas, databases, &c. Such controls are often requested, so I wonder about that. We are already have GRANT/REVOKE on databases, schemaes and so on as a core facility. This optional facility does not need to provide it again. Please understand that I'm not trying to be obstructive, but at the moment I don't understand what the proposals aim to do. I suggest that, without some clear statements of what things are trying to do, and what the intended limitations are, it will always be impossible for anyone to review the implementation of such a big feature and say whether it does what it intends to do. In my opinion, the row-level permission facility is a supplemental facility between core PostgreSQL and SE-PostgreSQL. It does not provide a single unified policy and MAC policy, but enables to row-/column- level access controls with existing core facilities. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <[EMAIL PROTECTED]> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Block nested loop join
Hi all, I am new to postgresql. I am currently doing research to optimize the query performance of RDBMS, specifically postgresql. Hence, I am currently reading out the code to understand the implementation of various query evaluation algorithm in postgresql. Currently, I am investigating the nested loop join algorithm in nodeNestloop.c. After reading the code, my understanding is that it performs simple nested loop join (not block nested loop join). Is this true? Does postgresql support block nested loop join? If it does, where is it? I might miss some buffering/caching mechanism. I appreciate any helps/advice. Regards, Bramandia R.
[HACKERS] patch: Allow the UUID type to accept non-standard formats
The attached patch allows uuid_in() to parse a wider variety of
variant input formats for the UUID data type, per the TODO named in
the subject line.
Original discussion here:
http://archives.postgresql.org/pgsql-hackers/2008-02/msg01214.php
http://archives.postgresql.org/pgsql-hackers/2008-02/msg01264.php
The original discussion left unresolved the question of what variant
input formats to accept. This patch takes the approach of allowing an
optional hyphen after each group of four hex digits. This will allow
4x-4x-4x-4x-4x-4x-4x-4x (the format that originally prompted the
discussion) as well as things like the Coldfusion format:,
8x-4x-4x-16x:
http://livedocs.adobe.com/coldfusion/6.1/htmldocs/functi54.htm
...and then there's this, which seems to be using 8x-8x-8x-8x:
http://lists.xensource.com/archives/html/xen-changelog/2005-11/msg00557.html
While we could perhaps accept only those variant formats which we
specifically know someone to be using, it seems likely that people
will keep moving those pesky dashes around, and we'll likely end up
continuing to add more formats and arguing about which ones are widely
enough used to deserve being on the list. So my vote is - as long as
they don't put a dash in the middle of a group of four (aka a byte),
just let it go.
Somewhat to my surprise, this implementation appears to be about 2-3%
slower than the one it replaces, as measured using a trivial test
harness. I would have thought that eliminating a call to strlen() and
an extra copy of the data would have actually picked up some speed,
but it seems not. Any thoughts on the reason? In any case, I don't
believe there's any possible use case where a 2-3% slowdown in
uuid_to_string is actually perceptible to the user, since I had to
call it 100 million times in a tight loop to measure it.
...Robert
Index: doc/src/sgml/datatype.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/datatype.sgml,v
retrieving revision 1.229
diff -c -r1.229 datatype.sgml
*** doc/src/sgml/datatype.sgml 3 Oct 2008 15:37:18 - 1.229
--- doc/src/sgml/datatype.sgml 10 Oct 2008 02:39:18 -
***
*** 3550,3560
PostgreSQL also accepts the following
alternative forms for input:
use of upper-case digits, the standard format surrounded by
! braces, and omitting the hyphens. Examples are:
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
Output is always in the standard form.
--- 3550,3563
PostgreSQL also accepts the following
alternative forms for input:
use of upper-case digits, the standard format surrounded by
! braces, omitting some or all hyphens, adding a hyphen after any
! group of four digits. Examples are:
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
+ a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
+ {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
Output is always in the standard form.
Index: src/backend/utils/adt/uuid.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/uuid.c,v
retrieving revision 1.7
diff -c -r1.7 uuid.c
*** src/backend/utils/adt/uuid.c 1 Jan 2008 20:31:21 - 1.7
--- src/backend/utils/adt/uuid.c 10 Oct 2008 02:39:19 -
***
*** 74,133
}
/*
! * We allow UUIDs in three input formats: 8x-4x-4x-4x-12x,
! * {8x-4x-4x-4x-12x}, and 32x, where "nx" means n hexadecimal digits
! * (only the first format is used for output). We convert the first
! * two formats into the latter format before further processing.
*/
static void
string_to_uuid(const char *source, pg_uuid_t *uuid)
{
! char hex_buf[32]; /* not NUL terminated */
! int i;
! int src_len;
! src_len = strlen(source);
! if (src_len != 32 && src_len != 36 && src_len != 38)
! goto syntax_error;
!
! if (src_len == 32)
! memcpy(hex_buf, source, src_len);
! else
{
! const char *str = source;
!
! if (src_len == 38)
! {
! if (str[0] != '{' || str[37] != '}')
! goto syntax_error;
!
! str++;/* skip the first character */
! }
!
! if (str[8] != '-' || str[13] != '-' ||
! str[18] != '-' || str[23] != '-')
! goto syntax_error;
!
! memcpy(hex_buf, str, 8);
! memcpy(hex_buf + 8, str + 9, 4);
! memcpy(hex_buf + 12, str + 14, 4);
! memcpy(hex_buf + 16, str + 19, 4);
! memcpy(hex_buf + 20, str + 24, 12);
}
for (i = 0; i < UUID_LEN; i++)
{
char str_buf[3];
! memcpy(str_buf, &hex_buf[i * 2], 2);
if (!isxdigit((unsigned char) str_buf[0]) ||
!isxdigit((unsigned char) str_buf[1]))
goto syntax_error;
str_buf[2] = '\0';
uuid->data[i] = (unsigned char) strtoul(str_buf, NULL, 16);
}
return;
syntax_error:
--- 74,122
}
/*
! * We allow UUIDs as a ser
Re: [HACKERS] avoid create a tablespace in pg_tblspc
On Thu, Oct 9, 2008 at 6:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Jaime Casanova" <[EMAIL PROTECTED]> writes: >> bug report http://archives.postgresql.org/pgsql-bugs/2008-10/msg00037.php >> seems like a reasonably request to me... and one that is simple to >> fulfill, just create a PG_VERSION in the pg_tblspc dir at initdb. > > I don't think it's a reasonable request. If we take this seriously, > we'd have to buy into making sure that no subdirectory of $PGDATA is > ever empty. Which is silly. And what of empty directories elsewhere? > There are any number of ways to shoot yourself in the foot with a bad > choice of tablespace location --- I don't find this one more dangerous > than any other. > well, this one can at least create a tablespace that never can be dropped -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transactions and temp tables
Hi, I am attaching a new patch that deals with the issue of the locks on temporary tables that have been accessed in transactions that have been prepared but not committed. I have added another list that keeps track of temp tables accessed by transactions that are prepared but not committed. The RemoveTempTable callback does not try to acquire locks on these tables. Now postmaster can terminate even with transactions in the prepared state that accessed temp tables. Let me know what you think. manu Tom Lane wrote: Emmanuel Cecchet <[EMAIL PROTECTED]> writes: Ok, so actually I don't see any different behavior between a temp table or a regular table. The locking happens the same way and as long as the commit prepared happens (potentially in another session), the lock is released at commit time which seems to make sense. Right, the problem is that you can't shut down the original backend because it'll try to drop the temp table at exit, and then block on the lock that the prepared xact is holding. From a database management standpoint that is unacceptable --- it means for instance that you can't shut down the database cleanly while such a prepared transaction is pending. The difference from a regular table is that no such automatic action is taken at backend exit for regular tables. The whole business of having restrictions on temp table access is annoying; I wish we could get rid of them not add complexity to enforcing them. The local-buffer-management end of the issue seems readily solvable: we need only decree that PREPARE has to flush out any dirty local buffers (and maybe discard local buffers altogether, not sure). But I've not been able to see a decent solution to the lock behavior. regards, tom lane ### Eclipse Workspace Patch 1.0 #P Postgres-HEAD Index: src/backend/access/heap/heapam.c === RCS file: /root/cvsrepo/pgsql/src/backend/access/heap/heapam.c,v retrieving revision 1.264 diff -u -r1.264 heapam.c --- src/backend/access/heap/heapam.c30 Sep 2008 10:52:10 - 1.264 +++ src/backend/access/heap/heapam.c9 Oct 2008 21:44:04 - @@ -878,7 +878,7 @@ /* Make note that we've accessed a temporary relation */ if (r->rd_istemp) - MyXactAccessedTempRel = true; + enlistRelationIdFor2PCChecks(relationId); pgstat_initstats(r); @@ -926,7 +926,7 @@ /* Make note that we've accessed a temporary relation */ if (r->rd_istemp) - MyXactAccessedTempRel = true; + enlistRelationIdFor2PCChecks(relationId); pgstat_initstats(r); @@ -976,7 +976,7 @@ /* Make note that we've accessed a temporary relation */ if (r->rd_istemp) - MyXactAccessedTempRel = true; + enlistRelationIdFor2PCChecks(relationId); pgstat_initstats(r); Index: src/backend/access/transam/xact.c === RCS file: /root/cvsrepo/pgsql/src/backend/access/transam/xact.c,v retrieving revision 1.265 diff -u -r1.265 xact.c --- src/backend/access/transam/xact.c 11 Aug 2008 11:05:10 - 1.265 +++ src/backend/access/transam/xact.c 9 Oct 2008 21:44:04 - @@ -47,6 +47,7 @@ #include "utils/memutils.h" #include "utils/relcache.h" #include "utils/snapmgr.h" +#include "utils/tqual.h" #include "utils/xml.h" #include "pg_trace.h" @@ -65,13 +66,6 @@ intCommitDelay = 0;/* precommit delay in microseconds */ intCommitSiblings = 5; /* # concurrent xacts needed to sleep */ -/* - * MyXactAccessedTempRel is set when a temporary relation is accessed. - * We don't allow PREPARE TRANSACTION in that case. (This is global - * so that it can be set from heapam.c.) - */ -bool MyXactAccessedTempRel = false; - /* * transaction states - transaction state from server perspective @@ -209,6 +203,13 @@ */ static MemoryContext TransactionAbortContext = NULL; +/* Hash table containing Oids of accessed temporary relations */ +HTAB *accessedTempRel; +/* Hash table containing Oids of accessed temporary relations that have been + * prepared commit but not committed yet + */ +HTAB *preparedTempRel; + /* * List of add-on start- and end-of-xact callbacks */ @@ -250,6 +251,7 @@ SubTransactionId mySubid, SubTransactionId parentSubid); static void CleanupTransaction(void); +static void CleanupAccessedTempRel(void); static void CommitTransaction(void); static TransactionId RecordTransactionAbort(bool isSubXact); static void StartTransaction(void); @@ -624,7 +626,7 @@ /* Propagate new command ID into static snapshots */ SnapshotSetCommandId(currentCommandId); - + /* * Make any
Re: [HACKERS] avoid create a tablespace in pg_tblspc
"Jaime Casanova" <[EMAIL PROTECTED]> writes: > bug report http://archives.postgresql.org/pgsql-bugs/2008-10/msg00037.php > seems like a reasonably request to me... and one that is simple to > fulfill, just create a PG_VERSION in the pg_tblspc dir at initdb. I don't think it's a reasonable request. If we take this seriously, we'd have to buy into making sure that no subdirectory of $PGDATA is ever empty. Which is silly. And what of empty directories elsewhere? There are any number of ways to shoot yourself in the foot with a bad choice of tablespace location --- I don't find this one more dangerous than any other. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum and TOAST tables
Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Yeah, that seems like the best answer. > Seems like this patch fixes it. Um, not for tables that don't have toast tables ... > How far back should be backpatched? Not at all; it's not a bug fix. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
>>> Kenneth Marshall <[EMAIL PROTECTED]> wrote: >>> Even more surprising is the behavior for interval(1) here: >>> [ some context with nonsurprising examples removed ...] >>> ccdev=# select '1 year 2 mons 3 days 04:05:06.64321'::interval(1); >>> interval >>> -- >>> 1 year 2 mons 3 days 04:05:06.60 >>> (1 row) >>> That trailing zero should be considered a bug. > What is not clear to me is how the > decision to stop at the 2nd decimal digit was reached. See this posting and others on the thread: http://archives.postgresql.org/pgsql-hackers/2008-09/msg00999.php The current rules seem to be: (1) If precision is specified, round to that precision. (2) If result has only zeros in the fraction, show no fraction, else show at least two digits in the fraction, adding a trailing zero if needed to get to two digits, but don't show any trailing zeros in the fraction beyond the second position. I think it would be ideal if we could track how many digits of accuracy we have in a value, and show them all, even if that involves trailing zeros. If that's not feasible, let's consistently not show trailing zeros. Rounding .64 to .6 and then showing .60 is just plain wrong. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4devel out of memory
Hah, got it. The reason the leak only manifests on 32-bit is that it
only manifests if int8 is a pass-by-reference datatype. The new API
for amgetbitmap causes a query-lifespan leak of one palloc(sizeof(int8))
per bitmap index scan. The distinguishing feature of your query seems
to be just that it does enough repeated bitmapscans to notice ...
The attached patch cures the leak for me, but I find it a tad ugly.
I'm tempted to think that it would be better if we changed the call
signature for amgetbitmap so that it returned the count through an
"int64 *" output parameter. Thoughts anyone?
regards, tom lane
Index: src/backend/access/index/indexam.c
===
RCS file: /cvsroot/pgsql/src/backend/access/index/indexam.c,v
retrieving revision 1.110
diff -c -r1.110 indexam.c
*** src/backend/access/index/indexam.c 11 Sep 2008 14:01:09 - 1.110
--- src/backend/access/index/indexam.c 9 Oct 2008 22:47:54 -
***
*** 655,660
--- 655,661
{
FmgrInfo *procedure;
int64 ntids;
+ Datum d;
SCAN_CHECKS;
GET_SCAN_PROCEDURE(amgetbitmap);
***
*** 665,673
/*
* have the am's getbitmap proc do all the work.
*/
! ntids = DatumGetInt64(FunctionCall2(procedure,
!
PointerGetDatum(scan),
!
PointerGetDatum(bitmap)));
pgstat_count_index_tuples(scan->indexRelation, ntids);
--- 666,680
/*
* have the am's getbitmap proc do all the work.
*/
! d = FunctionCall2(procedure,
! PointerGetDatum(scan),
! PointerGetDatum(bitmap));
!
! ntids = DatumGetInt64(d);
!
! #ifndef USE_FLOAT8_BYVAL
! pfree(DatumGetPointer(d));
! #endif
pgstat_count_index_tuples(scan->indexRelation, ntids);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] avoid create a tablespace in pg_tblspc
Hi, bug report http://archives.postgresql.org/pgsql-bugs/2008-10/msg00037.php seems like a reasonably request to me... and one that is simple to fulfill, just create a PG_VERSION in the pg_tblspc dir at initdb. if no one objects, here is a one linear patch for that -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 Index: src/bin/initdb/initdb.c === RCS file: /var/lib/postgresql/CVSREPO/pgsql/src/bin/initdb/initdb.c,v retrieving revision 1.162 diff -c -r1.162 initdb.c *** src/bin/initdb/initdb.c 30 Sep 2008 10:52:13 - 1.162 --- src/bin/initdb/initdb.c 9 Oct 2008 20:26:56 - *** *** 3128,3133 --- 3128,3138 bootstrap_template1(short_version); /* + * Disallow use pg_data/pg_tblspc itself as a location for a tablespace + */ + set_short_version(short_version, "pg_tblspc"); + + /* * Make the per-database PG_VERSION for template1 only after init'ing it */ set_short_version(short_version, "base/1"); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Un-break non-NLS builds.
Tom Lane wrote: > Log Message: > --- > Un-break non-NLS builds. Doh! Sorry I didn't realize that :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum and TOAST tables
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > I wonder if we can get away with simply renaming the new toast table and > > index after the data has been copied. > > Yeah, that seems like the best answer. Seems like this patch fixes it. How far back should be backpatched? Given the lack of field complaints I'd say "just to HEAD" but maybe others have different opinions. FWIW the patch applies cleanly (modulo header changes) back to 8.0. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. Index: src/backend/commands/cluster.c === RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/cluster.c,v retrieving revision 1.177 diff -c -p -r1.177 cluster.c *** src/backend/commands/cluster.c 19 Jun 2008 00:46:04 - 1.177 --- src/backend/commands/cluster.c 9 Oct 2008 21:15:55 - *** *** 29,34 --- 29,35 #include "catalog/index.h" #include "catalog/indexing.h" #include "catalog/namespace.h" + #include "catalog/pg_namespace.h" #include "catalog/toasting.h" #include "commands/cluster.h" #include "commands/tablecmds.h" *** rebuild_relation(Relation OldHeap, Oid i *** 568,573 --- 569,576 char NewHeapName[NAMEDATALEN]; TransactionId frozenXid; ObjectAddress object; + char NewToastName[NAMEDATALEN]; + Relation newrel; /* Mark the correct index as clustered */ mark_index_clustered(OldHeap, indexOid); *** rebuild_relation(Relation OldHeap, Oid i *** 622,627 --- 625,645 * because reindex_relation does it. */ reindex_relation(tableOid, false); + + /* + * At this point, everything is kosher except that the toast table's name + * corresponds to the temporary table. The name is irrelevant to + * the backend because it's referenced by OID, but users looking at the + * catalogs could be confused. Rename it to prevent this problem. + * + * Note no lock required on the relation, because we already hold an + * exclusive lock on it. + */ + newrel = relation_open(tableOid, NoLock); + snprintf(NewToastName, NAMEDATALEN, "pg_toast_%u", tableOid); + RenameRelationInternal(newrel->rd_rel->reltoastrelid, NewToastName, + PG_TOAST_NAMESPACE); + relation_close(newrel, NoLock); } /* -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] head's linking problem
thickbook:backend gj$ gcc-4.2 -no-cpp-precomp -no-cpp-precomp -O2 - Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -fwrapv -L../../src/port -Wl,-dead_strip_dylibs access/common/heaptuple.o access/common/ indextuple.o access/common/printtup.o access/common/reloptions.o access/common/scankey.o access/common/tupdesc.o access/gist/gist.o access/gist/gistutil.o access/gist/gistxlog.o access/gist/gistvacuum.o access/gist/gistget.o access/gist/gistscan.o access/gist/gistproc.o access/gist/gistsplit.o access/hash/hash.o access/hash/hashfunc.o access/hash/hashinsert.o access/hash/hashovfl.o access/hash/hashpage.o access/hash/hashscan.o access/hash/hashsearch.o access/hash/hashsort.o access/hash/hashutil.o access/heap/heapam.o access/heap/hio.o access/ heap/pruneheap.o access/heap/rewriteheap.o access/heap/syncscan.o access/heap/tuptoaster.o access/index/genam.o access/index/indexam.o access/nbtree/nbtcompare.o access/nbtree/nbtinsert.o access/nbtree/ nbtpage.o access/nbtree/nbtree.o access/nbtree/nbtsearch.o access/ nbtree/nbtutils.o access/nbtree/nbtsort.o access/nbtree/nbtxlog.o access/transam/clog.o access/transam/transam.o access/transam/varsup.o access/transam/xact.o access/transam/xlog.o access/transam/xlogutils.o access/transam/rmgr.o access/transam/slru.o access/transam/subtrans.o access/transam/multixact.o access/transam/twophase.o access/transam/ twophase_rmgr.o access/gin/ginutil.o access/gin/gininsert.o access/gin/ ginxlog.o access/gin/ginentrypage.o access/gin/gindatapage.o access/ gin/ginbtree.o access/gin/ginscan.o access/gin/ginget.o access/gin/ ginvacuum.o access/gin/ginarrayproc.o access/gin/ginbulk.o bootstrap/ bootparse.o bootstrap/bootstrap.o catalog/catalog.o catalog/ dependency.o catalog/heap.o catalog/index.o catalog/indexing.o catalog/ namespace.o catalog/aclchk.o catalog/pg_aggregate.o catalog/ pg_constraint.o catalog/pg_conversion.o catalog/pg_depend.o catalog/ pg_enum.o catalog/pg_largeobject.o catalog/pg_namespace.o catalog/ pg_operator.o catalog/pg_proc.o catalog/pg_shdepend.o catalog/ pg_type.o catalog/toasting.o parser/analyze.o parser/gram.o parser/ keywords.o parser/parser.o parser/parse_agg.o parser/parse_cte.o parser/parse_clause.o parser/parse_expr.o parser/parse_func.o parser/ parse_node.o parser/parse_oper.o parser/parse_relation.o parser/ parse_type.o parser/parse_coerce.o parser/parse_target.o parser/ parse_utilcmd.o parser/scansup.o commands/aggregatecmds.o commands/ alter.o commands/analyze.o commands/async.o commands/cluster.o commands/comment.o commands/conversioncmds.o commands/copy.o commands/ dbcommands.o commands/define.o commands/discard.o commands/explain.o commands/functioncmds.o commands/indexcmds.o commands/lockcmds.o commands/operatorcmds.o commands/opclasscmds.o commands/portalcmds.o commands/prepare.o commands/proclang.o commands/schemacmds.o commands/ sequence.o commands/tablecmds.o commands/tablespace.o commands/ trigger.o commands/tsearchcmds.o commands/typecmds.o commands/user.o commands/vacuum.o commands/vacuumlazy.o commands/variable.o commands/ view.o executor/execAmi.o executor/execCurrent.o executor/ execGrouping.o executor/execJunk.o executor/execMain.o executor/ execProcnode.o executor/execQual.o executor/execScan.o executor/ execTuples.o executor/execUtils.o executor/functions.o executor/ instrument.o executor/nodeAppend.o executor/nodeAgg.o executor/ nodeBitmapAnd.o executor/nodeBitmapOr.o executor/nodeBitmapHeapscan.o executor/nodeBitmapIndexscan.o executor/nodeHash.o executor/ nodeHashjoin.o executor/nodeIndexscan.o executor/nodeMaterial.o executor/nodeMergejoin.o executor/nodeNestloop.o executor/ nodeFunctionscan.o executor/nodeRecursiveunion.o executor/nodeResult.o executor/nodeSeqscan.o executor/nodeSetOp.o executor/nodeSort.o executor/nodeUnique.o executor/nodeValuesscan.o executor/nodeCtescan.o executor/nodeWorktablescan.o executor/nodeLimit.o executor/nodeGroup.o executor/nodeSubplan.o executor/nodeSubqueryscan.o executor/ nodeTidscan.o executor/tstoreReceiver.o executor/spi.o lib/dllist.o lib/stringinfo.o libpq/be-fsstubs.o libpq/be-secure.o libpq/auth.o libpq/crypt.o libpq/hba.o libpq/ip.o libpq/md5.o libpq/pqcomm.o libpq/ pqformat.o libpq/pqsignal.o main/main.o nodes/nodeFuncs.o nodes/ nodes.o nodes/list.o nodes/bitmapset.o nodes/tidbitmap.o nodes/ copyfuncs.o nodes/equalfuncs.o nodes/makefuncs.o nodes/outfuncs.o nodes/readfuncs.o nodes/print.o nodes/read.o nodes/params.o nodes/ value.o optimizer/geqo/geqo_copy.o optimizer/geqo/geqo_eval.o optimizer/geqo/geqo_main.o optimizer/geqo/geqo_misc.o optimizer/geqo/ geqo_mutation.o optimizer/geqo/geqo_pool.o optimizer/geqo/ geqo_recombination.o optimizer/geqo/geqo_selection.o optimizer/geqo/ geqo_erx.o optimizer/geqo/geqo_pmx.o optimizer/geqo/geqo_cx.o optimizer/geqo/geqo_px.o optimizer/geqo/geqo_ox1.o optimizer/geqo/ geqo_ox2
Re: [HACKERS] head's linking problem
Grzegorz Jaskiewicz wrote: > > On 2008-10-09, at 21:25, Alvaro Herrera wrote: > >> Yeah, my bad. What's the platform? > > mac os x Hmm, does your link line include -lintl? If not, does it successfully link if you add it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
Ron Mayer <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> We could try to do the same in the float case, but I'm a bit worried >> about finding ourselves showing "1234567.79" where it should be >> "1234567.8". > If I understand the code right fsec should mostly be values > between -1 and 1 anyway, because even in the floating point > case seconds are carried in the tm->tm_sec part. The problem is that that's a decomposed representation. In the stored form, there's a floating-point seconds field that includes hours, minutes, seconds, and fractional seconds, and therefore large values of the H/M/S fields degrade the accuracy of the fraction part. Here's an example (testing in 8.3, since HEAD defaults to integer): regression=# select '1234567890 hours 0.123 sec'::interval; interval - 1234567890:00:00.123047 (1 row) Since there's a (somewhat arbitrary) limitation of the hours to 2^31, this is close to the worst possible case. (Hm, maybe someone actually did the math and decided that 2 fractional digits were the most they could promise given that? No, because this code dates from a time when we included days in the same field too ... back then there might have been no accuracy at all in the fraction part.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
Tom Lane wrote:
In the integer-timestamp world we know that the number is exact in
microseconds. We clearly ought to be prepared to display up to six
fractional digits, but suppressing trailing zeroes in that seems
appropriate.
Great.
We could try to do the same in the float case, but I'm a bit worried
about finding ourselves showing "1234567.79" where it should be
"1234567.8".
If I understand the code right fsec should mostly be values
between -1 and 1 anyway, because even in the floating point
case seconds are carried in the tm->tm_sec part.
It looks to me that a double should be plenty to do
microseconds so long as we don't put big numbers into fsec.
printf("%.99f\n",59.11);
59.11426907882 ...
Anyway - I'll try showing up-to-6-digits in both cases and
seeing if I can find a test case that breaks.
I guess this rounding trickiness with rounding explains some of
the bizarre code like this too:
#if 0
/* chop off trailing one to cope with interval rounding */
if (strcmp(str + len - 4, "0001") == 0)
{
len -= 4;
*(str + len) = '\0';
}
#endif
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] Reduce alignment requirements on 64-bit systems.
Hello ITAGAKI-san, I apologize for not replying earlier, I needed to head out to work. On Thu, Oct 9, 2008 at 5:01 AM, ITAGAKI Takahiro <[EMAIL PROTECTED]> wrote: > "Ryan Bradetich" <[EMAIL PROTECTED]> wrote: >> Here is a proof-of-concept patch for reducing the alignment >> requirement for heap tuples on 64-bit systems. >> >> pg_depend 312 kB 296 kB >> pg_description160 kB 152 kB > > 5 percent of gain seems reasonable for me. I agree. I am seeing ~10% gain in some of my tables where the tuple size was 44 bytes but due to the alignment was being padded out to 48 bytes. > Is it possible to apply your improvement for indexes? > I think there are more benefits for small index entries > that size are often 12 or 20 bytes. I am not sure if this improvement will affect indexes or not yet. My current implementation specifically excludes indexes and only affects heap tuples. Now that I have a better understanding of the disk structure for heap tuples, I am planning to look at the index layout in the near future to see if there is some possible gain there as well. >> This would completely eliminate the impact for this >> patch on 32-bit systems, which would not gain any benefit from this patch. > No! There are *also* benefits even on 32-bit systems, because some > of them have 8-byte alignment. (for example, 32-bit Windows) Excellent! I was not aware of that. Thanks for this information. Any ideas on what I should use for this check? I was thinking of using #if SIZEOF_SIZE_T = 8 Guess I should search around for standard solution for this problem. > BTW, there might be a small mistabke on the following lines in patch. > They do the same things ;-) > > *** > *** 1019,1025 toast_flatten_tuple_attribute(Datum value, >new_len += BITMAPLEN(numAttrs); >if (olddata->t_infomask & HEAP_HASOID) >new_len += sizeof(Oid); > ! new_len = MAXALIGN(new_len); >Assert(new_len == olddata->t_hoff); >new_data_len = heap_compute_data_size(tupleDesc, > >toast_values, toast_isnull); > --- 1025,1034 >new_len += BITMAPLEN(numAttrs); >if (olddata->t_infomask & HEAP_HASOID) >new_len += sizeof(Oid); > ! if (olddata->t_infomask & HEAP_INTALIGN) > ! new_len = MAXALIGN(new_len); > ! else > ! new_len = MAXALIGN(new_len); >Assert(new_len == olddata->t_hoff); >new_data_len = heap_compute_data_size(tupleDesc, > >toast_values, toast_isnull); Thanks for that catch! I have this fixed in my local GIT tree now. Thanks for your feedback and review! - Ryan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] head's linking problem
On 2008-10-09, at 21:25, Alvaro Herrera wrote: Grzegorz Jaskiewicz wrote: current cvs head won't compile here, linking problem. I did try make distclean, etc: Undefined symbols: "_dgettext", referenced from: _errmsg_internal in elog.o _elog_finish in elog.o _errcontext in elog.o _errhint in elog.o _errdetail_log in elog.o _errdetail in elog.o _errmsg in elog.o Yeah, my bad. What's the platform? mac os x -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] head's linking problem
Grzegorz Jaskiewicz wrote: > current cvs head won't compile here, linking problem. I did try make > distclean, etc: > Undefined symbols: > "_dgettext", referenced from: > _errmsg_internal in elog.o > _elog_finish in elog.o > _errcontext in elog.o > _errhint in elog.o > _errdetail_log in elog.o > _errdetail in elog.o > _errmsg in elog.o Yeah, my bad. What's the platform? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] head's linking problem
current cvs head won't compile here, linking problem. I did try make distclean, etc: Undefined symbols: "_dgettext", referenced from: _errmsg_internal in elog.o _elog_finish in elog.o _errcontext in elog.o _errhint in elog.o _errdetail_log in elog.o _errdetail in elog.o _errmsg in elog.o -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] plpgsql is not translate-aware
Alvaro Herrera wrote: > Alvaro Herrera wrote: > > > True; committed that way, with a new function in miscinit.c. > > So apparently I broke buildfarm member grebe: Hmm, see bug #2608, http://archives.postgresql.org/pgsql-bugs/2006-09/msg00029.php I don't understand why gettext works and dgettext doesn't ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
On Thu, Oct 09, 2008 at 02:47:24PM -0500, Kevin Grittner wrote: > >>> Kenneth Marshall <[EMAIL PROTECTED]> wrote: > > >>> Even more surprising is the behavior for interval(1) here: > >>> [ some context with nonsurprising examples removed ...] > >>> ccdev=# select '1 year 2 mons 3 days 04:05:06.64321'::interval(1); > >>> interval > >>> -- > >>> 1 year 2 mons 3 days 04:05:06.60 > >>> (1 row) > >>> That trailing zero should be considered a bug. > > > What is not clear to me is how the > > decision to stop at the 2nd decimal digit was reached. > > See this posting and others on the thread: > > http://archives.postgresql.org/pgsql-hackers/2008-09/msg00999.php > > The current rules seem to be: > > (1) If precision is specified, round to that precision. > > (2) If result has only zeros in the fraction, show no fraction, else > show at least two digits in the fraction, adding a trailing zero if > needed to get to two digits, but don't show any trailing zeros in the > fraction beyond the second position. > > I think it would be ideal if we could track how many digits of > accuracy we have in a value, and show them all, even if that involves > trailing zeros. If that's not feasible, let's consistently not show > trailing zeros. Rounding .64 to .6 and then showing .60 is just plain > wrong. > > -Kevin > +1 Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] plpgsql is not translate-aware
Alvaro Herrera wrote: > True; committed that way, with a new function in miscinit.c. So apparently I broke buildfarm member grebe: /opt/prod/gcc-4.1.1/bin/gcc -maix64 -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -I../../../../src/include -I/opt/prod/tcl8.4.13_64_20060901/include -I/opt/freeware/include -c -o elog.o elog.c -MMD -MP -MF .deps/elog.Po elog.c: In function 'errmsg': elog.c:663: warning: implicit declaration of function 'dgettext' elog.c:663: warning: incompatible implicit declaration of built-in function 'dgettext' elog.c: In function 'errmsg_internal': elog.c:689: warning: incompatible implicit declaration of built-in function 'dgettext' elog.c: In function 'errdetail': elog.c:710: warning: incompatible implicit declaration of built-in function 'dgettext' elog.c: In function 'errdetail_log': elog.c:731: warning: incompatible implicit declaration of built-in function 'dgettext' elog.c: In function 'errhint': elog.c:752: warning: incompatible implicit declaration of built-in function 'dgettext' elog.c: In function 'errcontext': elog.c:777: warning: incompatible implicit declaration of built-in function 'dgettext' elog.c: In function 'elog_finish': elog.c:996: warning: incompatible implicit declaration of built-in function 'dgettext' ... /opt/prod/gcc-4.1.1/bin/gcc -maix64 -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -L../../src/port -Wl,-bbigtoc -L/opt/prod/tcl8.4.13_64_20060901/lib -L/opt/freeware/lib -Wl,-blibpath:/opt/rg/data_dba/build-farm/HEAD/inst/lib:/opt/prod/tcl8.4.13_64_20060901/lib:/opt/freeware/lib:/usr/lib:/lib access/common/heaptuple.o access/common/indextuple.o access/common/printtup.o access/common/reloptions.o [...] ld: 0711-317 ERROR: Undefined symbol: .dgettext ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. collect2: ld returned 8 exit status gmake[2]: *** [postgres] Error 1 gmake[2]: Leaving directory `/opt/rg/data_dba/build-farm/HEAD/pgsql.1126634/src/backend' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/opt/rg/data_/build-farm/HEAD/pgsql.1126634/src' gmake: *** [all] Error 2 -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
Kenneth Marshall <[EMAIL PROTECTED]> writes: > There is a difference between the result 0.6 and 0.60 in rounding. > The first is accurate +-0.05 and the second is +-0.005. Certainly, > it does not seem unreasonable that machines can calulate intervals > to the nearest 100th of a second. What is not clear to me is how the > decision to stop at the 2nd decimal digit was reached. Probably by flipping a coin ;-). You have to remember that all this behavior was designed around floating-point intervals, so there's inherent imprecision in there; and the extent depends on the size of the interval which makes it pretty hard to choose a display precision. In the integer-timestamp world we know that the number is exact in microseconds. We clearly ought to be prepared to display up to six fractional digits, but suppressing trailing zeroes in that seems appropriate. We could try to do the same in the float case, but I'm a bit worried about finding ourselves showing "1234567.79" where it should be "1234567.8". regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
On Thu, Oct 09, 2008 at 11:50:17AM -0700, Ron Mayer wrote: > Kevin Grittner wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> wrote: >> Even more surprising is the behavior for interval(1) here: >> [ some context with nonsurprising examples removed ...] >> ccdev=# select '1 year 2 mons 3 days 04:05:06.64321'::interval(1); >> interval >> -- >> 1 year 2 mons 3 days 04:05:06.60 >> (1 row) >> That trailing zero should be considered a bug. > > Is there a consensus that we don't want that trailing zero? > I notice that datetime.c's "TrimTrailingZeros(char *str)" has > the comment: > /* chop off trailing zeros... but leave at least 2 fractional digits */ > that suggests that the trailing zero was intentional, but I > can't find any reasons why 2 fractional disgits were left. > > The same function's also used for timestamps, so if we remove that > trailing zero in both places we'll see some regression differences > where we get > ! | Mon Feb 10 17:32:01.5 1997 PST |1997 |7 | 1 > instead of > ! | Mon Feb 10 17:32:01.50 1997 PST |1997 |7 | 1 > > IMHO we don't want the extra zero for timestamps either. > There is a difference between the result 0.6 and 0.60 in rounding. The first is accurate +-0.05 and the second is +-0.005. Certainly, it does not seem unreasonable that machines can calulate intervals to the nearest 100th of a second. What is not clear to me is how the decision to stop at the 2nd decimal digit was reached. If timestamps are accurate to 1/100th, intervals should be returned to that level of accuracy as well. Trailing digits definitely have meaning. My 2 cents, Ken > > If people agree I'll fold it into the patch dealing with > the other interval rounding eccentricities I have. > > Tom Lane wrote: >> Ron Mayer <[EMAIL PROTECTED]> writes: >>> [some other interval rounding example] >> I don't much like the forced rounding to two digits here, but changing >> that doesn't seem like material for back-patching. Are you going to >> fix that up while working on your other patches? > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 vs HEAD difference in Interval output?
Kevin Grittner wrote: "Kevin Grittner" <[EMAIL PROTECTED]> wrote: Even more surprising is the behavior for interval(1) here: [ some context with nonsurprising examples removed ...] ccdev=# select '1 year 2 mons 3 days 04:05:06.64321'::interval(1); interval -- 1 year 2 mons 3 days 04:05:06.60 (1 row) That trailing zero should be considered a bug. Is there a consensus that we don't want that trailing zero? I notice that datetime.c's "TrimTrailingZeros(char *str)" has the comment: /* chop off trailing zeros... but leave at least 2 fractional digits */ that suggests that the trailing zero was intentional, but I can't find any reasons why 2 fractional disgits were left. The same function's also used for timestamps, so if we remove that trailing zero in both places we'll see some regression differences where we get ! | Mon Feb 10 17:32:01.5 1997 PST |1997 |7 | 1 instead of ! | Mon Feb 10 17:32:01.50 1997 PST |1997 |7 | 1 IMHO we don't want the extra zero for timestamps either. If people agree I'll fold it into the patch dealing with the other interval rounding eccentricities I have. Tom Lane wrote: Ron Mayer <[EMAIL PROTECTED]> writes: [some other interval rounding example] I don't much like the forced rounding to two digits here, but changing that doesn't seem like material for back-patching. Are you going to fix that up while working on your other patches? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] plpgsql is not translate-aware
Peter Eisentraut wrote: > Alvaro Herrera wrote: >> So this'd seem to be the version ready to be applied (plus the needed >> nls.mk files). > > Perhaps repeated code like the following should be refactored to a > common function offered by the backend? True; committed that way, with a new function in miscinit.c. Now we need the new catalogs to be added to the pgtranslations project files; the ball is in your (Peter's) court. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bytea to XML crash fix
[EMAIL PROTECTED] writes: > I discovered a bug (server crash) with the conversion of bytea types to > xml with base64 (and hex) encoding. Crash confirmed here... > The patch against the postgres backend at the end of this email seems to > resolve the problem. I've never touched the postgres source before, and > the patch is simply a copy & paste of code elsewhere in the source-tree > that makes use of bytea types, so the patch may have nasty side-effects. Yeah, the core of the problem is certainly failure to detoast the datum before applying VARDATA etc. But this code is a hack job anyway ... why isn't the special case for bytea an arm of the switch just above, instead of being inserted after an output function call that it renders useless? And is it really necessary to depend on libxml for something as simple as base64 encoding? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] Reduce alignment requirements on 64-bit systems.
Hello Zdenek, On Thu, Oct 9, 2008 at 12:38 AM, Zdenek Kotala <[EMAIL PROTECTED]> wrote: > Ryan Bradetich napsal(a): >> On Wed, Oct 8, 2008 at 10:59 PM, Zdenek Kotala <[EMAIL PROTECTED]> >> wrote: >> I would like to see this patch (or some variant) go in if possible. >> Since the inplace >> upgrades a concern to you, is there anything I can do to help with the >> inplace >> upgrades to help offset the disruption this patch causes you? > > Yaah, wait until 8.5 :-). However, currently there is no clear consensus > which upgrade method is best. I hope that It will clear after Prato > developers meeting. Until this meeting I cannot say more. Heh, understood. :) I believe the proposed CRC patch also affects the heap page layout (adds the pd_checksum field to the PageHeaderData). [1] Just pointing out another patch that could affect you as well. My offer to help still stands. > I overlooked 'd' test. Your idea seems to me reasonable. Maybe, you could > test 'd' alignment only for NOT NULL values. Funny you should mention this. I had just started looking into this optimization to see if I could convince myself it would be safe. My initial conclusion indicates it would be safe, but I have not tested nor verified that yet. Having an independent proposal for this boosts my confidence even more. Thanks! > The problem there is add_item which it is used for indexes as well and they > has IndexTupleHeader structure. I'm not convenience about idea has two > different alignment for items on page. Just to clarify, this patch only affects heap storage when (i.e. the is_heap flag is set). I have not had a chance to analyze or see if I can reduce other storage types yet. > I guess another problem is with MAX_TUPLE_CHUNK_SIZE which uses MAXALIGN for > computing. It seems to me that toast chunk could waste a space anyway. > > And of course you should bump page layout version. Thanks. I will do. > I also suggest create function/macro to compute hoff and replace code with > this function/macro. Great. That is some of the feedback I was looking for. I did not implement it yet, because I wanted to see if the basic implementation was feasible first. Thanks again for your feedback! - Ryan [1] http://archives.postgresql.org/pgsql-hackers/2008-10/msg00070.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE DATABASE vs delayed table unlink
Matthew Wakeling <[EMAIL PROTECTED]> writes: > On Thu, 9 Oct 2008, Tom Lane wrote: >> So I'm mystified >> how Matthew could have seen the expected error and yet had the >> destination tree (or at least large chunks of it) left behind. > Remember I was running 8.3.0, and you mentioned a few changes after that > version which would have made sure the destination tree was cleaned up > properly. Well, there were some fixes for the case of a SIGTERM shutdown, but I still don't see how 8.3.0 (or any PG version for some time back) could report the file-not-found-in-source-tree failure without having passed through the cleanup code. There's some possibility that it tried to clean up and got a failure (which would be reported as a WARNING, which conceivably you didn't note) ... but it's kind of hard to see what failure it could get from deleting files it just created. Is there anything weird about the ownership/permissions on the orphaned directories and files? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches
Hi, On Sat, Sep 27, 2008 at 12:18:45PM +0900, KaiGai Kohei wrote: > > As I repeated several times, SE-PostgreSQL applies the seuciry policy > of SELinux to achieve consistency in access controls. I get this. The problem as I see it is that the SELinux security policy is designed around a very different set of assumptions about concurrency and consistency than any database system has to provide. My admittedly hurried glance at some abstracts in the literature suggests to me that others have looked at the paradoxes that come out of this kind of security policy consistency when you apply them to database systems. I think that clearly stating which of the trade-offs are the ones to be accepted is all that's needed. > In my vision, Apache assigns its contents handler an individual > security context based on HTTP authentication, source IP address > and so on just before web application invoked. > Because web applications works with individual least privilege set, > its accesses on filesystem are restricted by the security policy. > In a similar way, its accesses on databases are also restricted > via SE-PostgreSQL by same policy, by same privilege set. I want to focus on this description, because you appear to be limiting the problem scope tremendously here. We've moved from "general security policy for database system" to "security policy for database system as part of a web-application stack". I suggest that the range of practically available behaviours of the DBMS working as part of a web-application stack is a subset of the practically available behaviours of the DBMS overall. This could be the source of some of the difficulty. For instance, it seems that some scenarios people are worried about are really the sort of scenario relevant to online users of Postgres, rather than to people seeing results filtered through a web application. If we just don't care about the online, interactive users, then maybe some of those concerns go away. (I'll be honest, though, that I have a hard time getting excited about a security system that doesn't really work as advertised outside of a narrow context.) All that said, I think there are definite practical uses for the work you're undertaking, and I want to emphasise that I think the general goal is probably a good one. I am suggesting that some additional work clarifying the specific goals of the work is all that's really needed. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches
On Wed, Oct 08, 2008 at 11:36:19AM +0900, KaiGai Kohei wrote: > Yes, unfortunatelly. > No one replied to my proposed design: > http://marc.info/?l=pgsql-hackers&m=12470930544&w=2 FWIW, I didn't know what to say about that proposal because I still don't know what problems any of this is trying to solve. Perhaps I'm just obtuse (and people should feel free to ignore me, since I'm not volunteering any code anyway); but this entire discussion seems to me to lack clear statements of what the goals of the effort are. I know, "consistent access control"; that still doesn't tell me enough, I think. I haven't given it a great deal of thought, but it seems to me that there are at least a few different goals people have in mind. These are the ones I've thought of, but I don't pretend this is an exhaustive list. It certainly isn't based on a serious review of the literature, which seems to be plentiful: 1. Single-policy access control for all objects available in a system. I'm guessing that this is the point of SE-PostgreSQL. It also appears to me that the SE-Linux approach may not have completely defined how these things work in a database context, but that there has been academic work in this area. I think a clear description of what the costs and benefits of this approach are would go a long way to helping evaluation. For instance, what sort of side channels does this expose? Are there database design techniques that help? &c. 2. Granular row-level access control based on database-level ACLs. I have formed the impression that there is some support of this sort needed anyway to implement (1), but maybe not. I guess this is the proposal you mention. What is the goal here? Simply administrator-defined controls on data access inside the database? Is there a model we're following, or are we making one up? If the latter, are we sure we're solving all the use cases? What are they? What are the problems here: for instance, this has exactly the same sorts of foreign-key issues that swamped the discussion of the SE-PostgreSQL patches, and I don't see that the new proposal addresses any of that. 3. Column-level access controls. This is ruled out of the current discussion in the proposal mentioned above. Surely you need column-level access controls to make (1) work, though? If not, then I'm even more confused than I thought. 4. Metadata-level access controls. None of the proposals so far seem to provide a complete set of access controls for the system details -- schemas, databases, &c. Such controls are often requested, so I wonder about that. Please understand that I'm not trying to be obstructive, but at the moment I don't understand what the proposals aim to do. I suggest that, without some clear statements of what things are trying to do, and what the intended limitations are, it will always be impossible for anyone to review the implementation of such a big feature and say whether it does what it intends to do. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] plpgsql is not translate-aware
Alvaro Herrera wrote:
Alvaro Herrera wrote:
Tom Lane wrote:
No, you could have TEXTDOMAIN be defined as NULL by default, and let
modules redefine it as "foo".
Doh, right.
So this'd seem to be the version ready to be applied (plus the needed
nls.mk files).
Perhaps repeated code like the following should be refactored to a
common function offered by the backend?
> diff -c -p -r1.40 pl_handler.c
*** src/pl/plpgsql/src/pl_handler.c 29 Aug 2008 13:02:33 - 1.40
--- src/pl/plpgsql/src/pl_handler.c 9 Oct 2008 00:51:22 -
*** _PG_init(void)
*** 42,47
--- 42,57
if (inited)
return;
+ #ifdef ENABLE_NLS
+ if (my_exec_path[0] != '\0')
+ {
+ charlocale_path[MAXPGPATH];
+
+ get_locale_path(my_exec_path, locale_path);
+ bindtextdomain(TEXTDOMAIN, locale_path);
+ }
+ #endif
+
plpgsql_HashTableInit();
RegisterXactCallback(plpgsql_xact_cb, NULL);
RegisterSubXactCallback(plpgsql_subxact_cb, NULL);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO item: adding VERBOSE option to CLUSTER
Is anyone working the "CLUSTER: Add VERBOSE option..." TODO item listed on the PostgreSQL Wiki? If not, would it be wise for me to use VERBOSE handling in an existing command (e.g. VACUUM) as a guide while adding VERBOSE to CLUSTER?
Re: [HACKERS] CREATE DATABASE vs delayed table unlink
Heikki Linnakangas <[EMAIL PROTECTED]> writes: Another thought is to ignore ENOENT in copydir. On Wed, 8 Oct 2008, Tom Lane wrote: Yeah, I thought about that too, but it seems extremely dangerous ... I agree. If a file randomly goes missing, that's not an error to ignore, even if you think the only way that could happen is safe. I could be wrong - but couldn't other bad things happen too? If you're copying the files before the checkpoint has completed, couldn't the new database end up with some of the recent changes going missing? Or is that prevented by FlushDatabaseBuffers? Matthew -- Isn't "Microsoft Works" something of a contradiction? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] bytea to XML crash fix
I discovered a bug (server crash) with the conversion of bytea types to
xml with base64 (and hex) encoding.
The patch against the postgres backend at the end of this email seems to
resolve the problem. I've never touched the postgres source before, and
the patch is simply a copy & paste of code elsewhere in the source-tree
that makes use of bytea types, so the patch may have nasty side-effects.
Tested using postgresql 8.3.4 on Ubuntu Hardy (x86)
./configure --prefix=/opt/postgres-8.3.4 --with-libxml
--enable-integer-datetimes
make
sudo make install
sudo -u postgres /opt/postgres-8.3.4/bin/pg_ctl start -D
/etc/postgresql/8.3/main/
/opt/postgres-8.3.4/bin/psql -U postgres test
create schema test;
create table test.foo (
col1 bytea
);
insert into test.foo VALUES(decode('aGVsbG8gd29ybGQK', 'base64'));
test=# select * from test.foo;
col1
-
hello world\012
(1 row)
test=# select query_to_xml('select col1 from test.foo', false, false, '');
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
(Server console output)
2008-10-09 15:09:00 EST LOG: server process (PID 28662) was terminated by
signal 11: Segmentation fault
2008-10-09 15:09:00 EST LOG: terminating any other active server
processes
2008-10-09 15:09:00 EST FATAL: the database system is in recovery mode
2008-10-09 15:09:00 EST LOG: all server processes terminated;
reinitializing
2008-10-09 15:09:00 EST LOG: database system was interrupted; last known
up at 2008-10-09 15:06:19 EST
2008-10-09 15:09:00 EST LOG: database system was not properly shut down;
automatic recovery in progress
2008-10-09 15:09:00 EST LOG: redo starts at 21/F09D8A3C
2008-10-09 15:09:00 EST LOG: record with zero length at 21/F09FAA3C
2008-10-09 15:09:00 EST LOG: redo done at 21/F09FAA10
2008-10-09 15:09:00 EST LOG: last completed transaction was at log time
2008-10-09 15:08:39.153513+10
2008-10-09 15:09:00 EST LOG: autovacuum launcher started
2008-10-09 15:09:00 EST LOG: database system is ready to accept
connections
(syslog)
Oct 9 15:09:00 tr2g606-lx kernel: [22131.779672] postgres[28662]:
segfault at 0849a000 eip b7f22019 esp bff1b2d0 error 4
(backtrace)
Program received signal SIGSEGV, Segmentation fault.
0xb7f22019 in xmlTextWriterWriteBase64 () from /usr/lib/libxml2.so.2
(gdb) bt
#0 0xb7f22019 in xmlTextWriterWriteBase64 () from /usr/lib/libxml2.so.2
#1 0x082955a5 in map_sql_value_to_xml_value ()
#2 0x08299184 in xmlelement ()
#3 0x08175311 in ExecEvalXml ()
#4 0x0816f98f in ExecProject ()
#5 0x08175e17 in ExecScan ()
#6 0x08180ec9 in ExecSeqScan ()
#7 0x0816f36d in ExecProcNode ()
#8 0x0816e51b in ExecutorRun ()
#9 0x08205168 in PortalRunSelect ()
#10 0x0820616a in PortalRun ()
#11 0x08201613 in exec_simple_query ()
#12 0x082024fc in PostgresMain ()
#13 0x081d6fd3 in ServerLoop ()
#14 0x081d7c6d in PostmasterMain ()
#15 0x0818f499 in main ()
Index: src/backend/utils/adt/xml.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.77
diff -c -r1.77 xml.c
*** src/backend/utils/adt/xml.c 16 Sep 2008 00:49:41 - 1.77
--- src/backend/utils/adt/xml.c 9 Oct 2008 06:38:49 -
***
*** 1701,1716
xmlBufferPtr buf;
xmlTextWriterPtr writer;
char *result;
xml_init();
buf = xmlBufferCreate();
writer = xmlNewTextWriterMemory(buf, 0);
if (xmlbinary == XMLBINARY_BASE64)
! xmlTextWriterWriteBase64(writer,
VARDATA(value), 0, VARSIZE(value) - VARHDRSZ);
else
! xmlTextWriterWriteBinHex(writer,
VARDATA(value), 0, VARSIZE(value) - VARHDRSZ);
xmlFreeTextWriter(writer);
result = pstrdup((const char *)
xmlBufferContent(buf));
--- 1701,1722
xmlBufferPtr buf;
xmlTextWriterPtr writer;
char *result;
+ bytea *bstr;
xml_init();
buf = xmlBufferCreate();
writer = xmlNewTextWriterMemory(buf, 0);
+ bstr = DatumGetByteaP(value);
+
if (xmlbinary == XMLBINARY_BASE64)
! xmlTextWriterWriteBase64(writer,
VARDATA(bstr), 0, VARSIZE(bstr) - VARHDRSZ);
else
! xmlTextWriterWriteBinHex(writer,
VARDATA(bstr), 0, VARSIZE(bstr) - VARHDRSZ);
!
! if ((Pointer) bstr != DatumGetPointer(value))
!
Re: [HACKERS] CREATE DATABASE vs delayed table unlink
On Thu, 9 Oct 2008, Tom Lane wrote: So I'm mystified how Matthew could have seen the expected error and yet had the destination tree (or at least large chunks of it) left behind. Remember I was running 8.3.0, and you mentioned a few changes after that version which would have made sure the destination tree was cleaned up properly. [ thinks for a bit... ] We know there were multiple occurrences. Matthew, is it possible that you had other createdb failures that did *not* report "file does not exist"? For instance, a createdb interrupted by a "fast" database shutdown might have left things this way. Well, we didn't have any fast database shutdowns or power failures. I don't think so. Matthew -- Heat is work, and work's a curse. All the heat in the universe, it's going to cool down, because it can't increase, then there'll be no more work, and there'll be perfect peace. -- Michael Flanders -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum and reloptions
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > Alvaro Herrera <[EMAIL PROTECTED]> wrote: >> So I gave up waiting for someone else to do the reloptions patch for >> autovacuum and started work on it myself. > Is it needed to keep backward compatibility? > I'd like to suggest to keep pg_catalog.pg_autovacuum as a system view > even after the options is put into reloptons, and the view to be > updatable using RULEs if possible. Ugh. No. It has been explicitly stated all along that pg_autovacuum was a temporary API and that anyone depending on it could expect future trouble. > But we will not able to do that if the settings will be in reloptions > because ALTER TABLE SET cannot be used with JOINs. Any mechanism that a rule might use to set reloptions would be just as usable in a join as the rule itself ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] About postgresql8.3.3 build in MS VS2005
Magnus Hagander <[EMAIL PROTECTED]> writes: > IIRC, Visual C++ compiles UTF8 source code just fine, so it should work. The problem is that what's in our CVS is Latin-1. I'm not quite sure why the OP's compiler is expecting it to be UTF-8, but it's pretty obvious what's going to happen if it does expect that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WITH RECURSIVE ... simplified syntax?
Josh Berkus <[EMAIL PROTECTED]> writes: > Would it be a worth it for us to implement a non-standard simple syntax > sugar on top of WITH RECURSIVE? Or, at least, something like > CONNECT_BY()? The Oracle syntax only *looks* simple. When you start to study it you realize that it's a horrid, messy kluge. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE DATABASE vs delayed table unlink
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > I committed a patch to do a full-blown checkpoint before the copy. > Annoying to do two checkpoints, but CREATE DATABASE is a pretty > heavy-weight operation anyway. I don't see any other solution at the > moment, at least not one that we could back-patch. Agreed. Patch looks good. I tried to reproduce the issue here using yesterday's CVS HEAD. It is not hard to get the "file does not exist" failure, but so far as I can tell CREATE DATABASE does clean up the target directory before reporting that failure to the user. It is probably possible to interrupt the cleanup, but if that happened then the original error message wouldn't ever get delivered at all. So I'm mystified how Matthew could have seen the expected error and yet had the destination tree (or at least large chunks of it) left behind. [ thinks for a bit... ] We know there were multiple occurrences. Matthew, is it possible that you had other createdb failures that did *not* report "file does not exist"? For instance, a createdb interrupted by a "fast" database shutdown might have left things this way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] Reduce alignment requirements on 64-bit systems.
"Ryan Bradetich" <[EMAIL PROTECTED]> wrote: > Here is a proof-of-concept patch for reducing the alignment > requirement for heap tuples on 64-bit systems. > > pg_depend 312 kB 296 kB > pg_description160 kB 152 kB 5 percent of gain seems reasonable for me. Is it possible to apply your improvement for indexes? I think there are more benefits for small index entries that size are often 12 or 20 bytes. > This would completely eliminate the impact for this > patch on 32-bit systems, which would not gain any benefit from this patch. No! There are *also* benefits even on 32-bit systems, because some of them have 8-byte alignment. (for example, 32-bit Windows) BTW, there might be a small mistabke on the following lines in patch. They do the same things ;-) *** *** 1019,1025 toast_flatten_tuple_attribute(Datum value, new_len += BITMAPLEN(numAttrs); if (olddata->t_infomask & HEAP_HASOID) new_len += sizeof(Oid); ! new_len = MAXALIGN(new_len); Assert(new_len == olddata->t_hoff); new_data_len = heap_compute_data_size(tupleDesc, toast_values, toast_isnull); --- 1025,1034 new_len += BITMAPLEN(numAttrs); if (olddata->t_infomask & HEAP_HASOID) new_len += sizeof(Oid); ! if (olddata->t_infomask & HEAP_INTALIGN) ! new_len = MAXALIGN(new_len); ! else ! new_len = MAXALIGN(new_len); Assert(new_len == olddata->t_hoff); new_data_len = heap_compute_data_size(tupleDesc, toast_values, toast_isnull); Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE DATABASE vs delayed table unlink
Matthew Wakeling wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: Another thought is to ignore ENOENT in copydir. On Wed, 8 Oct 2008, Tom Lane wrote: Yeah, I thought about that too, but it seems extremely dangerous ... I agree. If a file randomly goes missing, that's not an error to ignore, even if you think the only way that could happen is safe. I committed a patch to do a full-blown checkpoint before the copy. Annoying to do two checkpoints, but CREATE DATABASE is a pretty heavy-weight operation anyway. I don't see any other solution at the moment, at least not one that we could back-patch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE DATABASE vs delayed table unlink
Matthew Wakeling wrote: I could be wrong - but couldn't other bad things happen too? If you're copying the files before the checkpoint has completed, couldn't the new database end up with some of the recent changes going missing? Or is that prevented by FlushDatabaseBuffers? FlushDatabaseBuffers prevents that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] auto_explain contrib moudle
Thanks for your reviewing, Alex.
I applied your comments to my patch.
- auto_explain.patch : patch against HEAD
- auto_explain.tgz : contrib module
- autoexplain.sgml : documentation
"Alex Hunsaker" <[EMAIL PROTECTED]> wrote:
> *custom_guc_flags-0828.patch
> My only other concern is the changes to DefineCustom*() to tag the new
> flags param. Now I think anyone who uses Custom gucs will want/should
> be able to set that. I did not see any people in contrib using it but
> did not look on PGfoundry. Do we need to document the change
> somewhere for people who might be using it???
Now it is done with DefineCustomVariable(type, variable) and keep
existing functions as-is for backward compatibility.
Some people will be happy if the functions are documented,
but we need to define 'stable-internal-functions' between
SPI (stable expoted functions) and unstable internal functions.
> *psql_ignore_notices-0828.patch:
> I think this should get dropped.
Hmm, I agree that hiding all messages is not good. I removed it.
If some people need it, we can reconsider it in a separated discussion.
> *auto_explalin.c:
> init_instrument()
> The only "cleaner" way I can
> see is to add a hook for CreateQueryDesc so we can overload
> doInstrument and ExecInitNode will InstrAlloc them all for us.
I wanted to avoid modifying core codes as far as possible,
but I see it was ugly. Now I added 'force_instrument' global
variable as a hook for CreateQueryDesc.
> the only other comment I have is suset_assign() do we really need to
> be a superuser if its all going to LOG ? There was some concern about
> explaining security definer functions right? but surely a regular
> explain on those shows the same thing as this explain? Or what am I
> missing?
Almost logging options in postgres are only for superusers. So I think
auto_explain options should not be modified by non-superusers, too.
If you want to permit usage for users, you can create a security definer
wrapper function to allow it, no?
CREATE FUNCTION set_explain_log_min_duration(text) RETURNS text AS
$$ SELECT set_config('explain.log_min_duration', $1, false); $$
LANGUAGE sql SECURITY DEFINER;
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
auto_explain.patch
Description: Binary data
auto_explain.tgz
Description: Binary data
autoexplain.sgml
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] trigger functions broken?
On Wed, 2008-10-08 at 20:56 +0200, A. Kretschmer wrote: > am Wed, dem 08.10.2008, um 14:29:23 -0400 mailte Alvaro Herrera folgendes: > > Hi, > > > > Trigger functions are supposed to be able to be called only as triggers, > > but apparently the check is not working in CVS HEAD: > > > > alvherre=# create or replace function foo () returns trigger as $$ begin > > perform 1; return new; end; $$ language plpgsql; > > CREATE FUNCTION > > alvherre=# select foo(); > > foo > > - > > > > (1 fila) > > > And? > > The function returns a TRIGGER, not a value. Can you do anything with this TRIGGER value ? pl/python's approach seems saner to me: hannu=# create or replace function foo () returns trigger as $$ return $$ language plpythonu; CREATE FUNCTION hannu=# select foo (); ERROR: trigger functions can only be called as triggers --- Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] Reduce alignment requirements on 64-bit systems.
Ryan Bradetich napsal(a): Hello Zdenek, Hello Ryan, On Wed, Oct 8, 2008 at 10:59 PM, Zdenek Kotala <[EMAIL PROTECTED]> wrote: Just a quick look. At first point. Your change introduces new page layout version. Which is not acceptable from my point of view for 8.4 (it add I would like to see this patch (or some variant) go in if possible. Since the inplace upgrades a concern to you, is there anything I can do to help with the inplace upgrades to help offset the disruption this patch causes you? Yaah, wait until 8.5 :-). However, currently there is no clear consensus which upgrade method is best. I hope that It will clear after Prato developers meeting. Until this meeting I cannot say more. another complexity to inplace upgrade). And I guest that it maybe works fine on 64bits x86 but it will fail on SPARC and other machine which requires aligned data. Did I miss something? My intention was to keep the data aligned so it should work on any platform. The patch checks the user-defined data to see if any column requires the double storage type. If the double storage type is required, it uses the MAXALIGN() macro which preserves the alignment for 64-bit data types. If no columns require the double storage type, then the data will be INTALIGN() which still preserves the alignment requirements. I overlooked 'd' test. Your idea seems to me reasonable. Maybe, you could test 'd' alignment only for NOT NULL values. If I have a complete mis-understanding of this issue, please explain it to me and I will either fix it or withdraw the patch. The problem there is add_item which it is used for indexes as well and they has IndexTupleHeader structure. I'm not convenience about idea has two different alignment for items on page. I guess another problem is with MAX_TUPLE_CHUNK_SIZE which uses MAXALIGN for computing. It seems to me that toast chunk could waste a space anyway. And of course you should bump page layout version. I also suggest create function/macro to compute hoff and replace code with this function/macro. Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
