Re: [HACKERS] Pre-allocated free space for row
On K, 2005-08-31 at 12:23 -0400, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: My wild guess is that deleting all index pointers for a removed index is more-or-less the same cost as creating new ones for inserted/updated page. Only if you are willing to make the removal process recalculate the index keys from looking at the deleted tuple. This opens up a ton of gotchas for user-defined index functions, particularly for doing it in the bgwriter which is not really capable of running transactions. Would it be OK in non-functional index case ? Removing index entries also requires writing WAL log records, which is something we probably want to minimize in the bgwriter to avoid contention issues. but the WAL log records have to be written at some point anyway, so this should not increase the general load. It is often more agreeable to take a continuous up-to-2X performance hit than an unpredictable hit at unknown (or even at a known) time. Well, you can have that sort of tradeoff today, by running autovacuum continuously with the right delay parameters. The only vacuum optimization idea I've heard that makes any sense to me is the one about keeping a bitmap of changed pages so that vacuum need not read in pages that have not changed since last time. Everything else is just shuffling the same work around, and in most cases doing it less efficiently than we do now and in more performance-critical places. Not really, I was aiming at the case where the old and new *index* entries are also on the same page (quite likely after an update of a non-index field, or only one of the indexed fields). I this case we are possibly shuffling around the CPU work, but we have a good chance of avoiding I/O work. This is similar to placing the updated heap tuple on the same page as old one to avoid extra page writes. Another interesting idea is to have a counter in heap tuple for index entries pointing to this tuple, so that instead of setting the too-old- to-be-visible bit, we could just remove the index entry, and decrease that counter, and remove the counter when it's zero. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
-Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: 01 September 2005 03:31 To: William ZHANG Cc: Dave Page; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Call for 7.5 feature completion We currently have nmake files for the client libraries, And even those are a royal pain to maintain, never mind an entire set. Besides, I'm sure what William really wants is not nmake files, but VC++ Project files - but then which version do we keep? It's not like we could say that everyone should be using VS2005, so all commits would have to be VC++ 6.0 or earlier compatible, otherwise someone is bound to complain. I agree with Andrew though - maintaining VC++ project files or nmake files is just not practical - especially given that most of our developers are not Windows users. Regards, Dave. ---(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: [HACKERS] On hardcoded type aliases and typmod for user types
On Wed, Aug 31, 2005 at 05:14:13PM -0400, Tom Lane wrote: That strikes me as an unnecessary reduction in flexibility. As long as we make the hardwired type names translate to qualified names (same as they do now) we don't have to assume any such thing. Ack, there's fortunatly only a handful of those. The point about character sets is a bit distressing; here we are designing a new general-purpose mechanism and we can already see cases it doesn't handle. Can we fix that? Err, well. My thought was a certain group of type-suffix options would be permitted (only zero or one at a time), for example: WITH TIME ZONE WITHOUT TIME ZONE CHARACTER SET xxx And have the grammer accept these after any type. For example, the type NUMERIC WITH TIME ZONE would be syntactically valid but the code would then reject it. You have a issue then because the typmod function should then be able to return a completely different type because the system looked up timestamp and now the function determines that with that option, it should actually be timestamptz. As for the specific mechanism, well, my options were (in the TYPE declaration statement: TYPMODFUNC = function( intarray [, sometype] ) RETURNS int32 or intarray This restricts the arguments between the brackets to integers, is this reasonable? The sometype would be something to handle the suffix options. (Text pair? option,value). Returning an intarray if a new type is allowed. TYPMODFUNC = function( recordtype [, sometype ] ) RETURNS int32 or intarray The record type could then indicate what's supported, except you can't pass a variable number of arguments (for NUMERIC). How about fill up from the front, leave NULLs for all the unfilled ones. The STRICT flag could tell if all fields need to be filled (ugh). TYPMODFUNC = function( internal [, sometype ] ) RETURNS int32 or intarray Simply pass the (Node*) from the parser and let the function sort it out itself. Except now they have to be written in C. Is this unreasonable, it's called fairly early on, all the issues with no valid snapshot apply here and you can't defer the evaluation till later. I'm not sure how to choose, they all handle the current situation fine but what do we want to allow users to do in the future? Is the SQL standard likely to come up with SOMETYPE(ident) as a declaration, in which case we need the second or third options. Ident can be converted to a constant string for these purposes. And then there's output to consider, currently timestamp etc have special cases. But if you're going to allow CHARACTER SET xxx to apply to any type, you need a way to reconstruct the values for output. Requireing the user the provide an inverse function is one (possibly unreliable) way. Storing the arguments directly is another. And is one int32 typmod sufficient? This character set per column has been talked about for a while, but where was the information going to be stored? There's several issues to be sorted out yet, I fear. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgplhXwyrOAOR.pgp Description: PGP signature
Re: [HACKERS] On hardcoded type aliases and typmod for user types
On N, 2005-09-01 at 09:26 +0200, Martijn van Oosterhout wrote: On Wed, Aug 31, 2005 at 05:14:13PM -0400, Tom Lane wrote: That strikes me as an unnecessary reduction in flexibility. As long as we make the hardwired type names translate to qualified names (same as they do now) we don't have to assume any such thing. Ack, there's fortunatly only a handful of those. The point about character sets is a bit distressing; here we are designing a new general-purpose mechanism and we can already see cases it doesn't handle. Can we fix that? Err, well. My thought was a certain group of type-suffix options would be permitted (only zero or one at a time), for example: WITH TIME ZONE WITHOUT TIME ZONE CHARACTER SET xxx And have the grammer accept these after any type. Maybe make the last one WITH CHARACTER SET xxx and promote WITH to a real keyword. It seems a good idea to have WITH as a real keyword anyway, as at least ANSI/ISO syntax for recursive queries seem to require it too. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] On hardcoded type aliases and typmod for user types
On Thu, 1 Sep 2005, Martijn van Oosterhout wrote: Err, well. My thought was a certain group of type-suffix options would be permitted (only zero or one at a time), for example: WITH TIME ZONE WITHOUT TIME ZONE CHARACTER SET xxx String types have 3 modifiers, the length, the charset and the collation. The syntax of these are defined by the standard so at least that syntax ought to be allowed (even if there are more work to actually do anything with charset and collation info). -- /Dennis Björklund ---(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: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
On Wed, 2005-08-31 at 22:21 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2005-08-31 at 19:24 -0400, Tom Lane wrote: If you don't remove any tuples, you don't scan the indexes anyway IIRC. No. Even if you remove *zero* tuples, an index is still scanned twice. Once to not delete the rows and once to not delete the pages. Yeah? Well, that could probably be improved with a less intrusive fix, that is, one that does it automatically instead of involving the user. I really really do not like proposals to introduce still another kind of VACUUM. We have too many already; any casual glance through the archives will show that most PG users don't have a grip on when to use VACUUM FULL vs VACUUM. Throwing in some more types will make that problem exponentially worse. I'll post my proposal for changing that, so we can see the two alternatives. I'm easy either way at the moment. Best Regards, Simon Riggs ---(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: [HACKERS] On hardcoded type aliases and typmod for user types
On Thu, Sep 01, 2005 at 11:18:04AM +0200, Dennis Bjorklund wrote: String types have 3 modifiers, the length, the charset and the collation. The syntax of these are defined by the standard so at least that syntax ought to be allowed (even if there are more work to actually do anything with charset and collation info). From a quick browse in google it looks like: CHAR(4) COLLATE xxx CHARACTER SET yyy is valid syntax. So I guess that becomes 0 or more modifiers. And a single int32 is not going to cut it. More fields (typmod2, typmod3)? Fields explicitly for this purpose (typcollate and typcharset), array of int32? Which would make the second argument to the typmod function ARRAY OF (textpair) or some such. If the function doesn't accept that then this is a quick indicator that no options are allowed. Quick thing, should 'mytype' and 'mytype()' be considered the same and should they default to typmod -1? Currently '()' is not even accepted. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpKBsVJBWalY.pgp Description: PGP signature
Re: [HACKERS] On hardcoded type aliases and typmod for user types
On Thu, Sep 01, 2005 at 11:12:26AM +0300, Hannu Krosing wrote: Maybe make the last one WITH CHARACTER SET xxx and promote WITH to a real keyword. It seems a good idea to have WITH as a real keyword anyway, as at least ANSI/ISO syntax for recursive queries seem to require it too. Sorry, CHARACTER SET is defined by SQL standard. I don't understand what it is there for though, I thought the point of UNICODE/UTF-8 was to get rid of all this crap. I also can't find the bit that explains what should happen if two strings of different character sets are concatinated. The only thing I can think this useful for is default input/output charset, overriding client_encoding, and internally everything is still UNICODE. The COLLATE stuff is neat, if we can get it work. Maybe CHARSET is a roundabout way to specify the COLLATE order? Incidently, I just downloaded the SQL99 spec and am slightly confused by some of the things they'd added. Am I the only one? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpExRZavzpkf.pgp Description: PGP signature
Re: [HACKERS] Pre-allocated free space for row
My wild guess is that deleting all index pointers for a removed index is more-or-less the same cost as creating new ones for inserted/updated page. Only if you are willing to make the removal process recalculate the index keys from looking at the deleted tuple. The bgwriter could update all columns of dead heap tuples in heap pages to NULL and thus also gain free space without the need to touch the indexes. The slot would stay used but it would need less space. Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pre-allocated free space for row
Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] writes: The bgwriter could update all columns of dead heap tuples in heap pages to NULL and thus also gain free space without the need to touch the indexes. The slot would stay used but it would need less space. Not unless it's running a transaction (consider TOAST updates). 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: [HACKERS] On hardcoded type aliases and typmod for user types
Martijn van Oosterhout kleptog@svana.org writes: TYPMODFUNC =3D function( internal [, sometype ] ) RETURNS int32 or intar= ray Simply pass the (Node*) from the parser and let the function sort it out itself. Except now they have to be written in C. Is this unreasonable, Nope. You're not going to be writing any interesting datatypes without using C, anyway. I'd go with this one to minimize flexibility problems. I'd be inclined to stick with the convention that internally typmod is a single int32; that is really wired into way too many APIs to consider changing. varchar could do something like using 24 bits for the length and 8 bits for an encoded indication of the charset. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO item: set proper permissions on non-system schemas
Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: There's considerable feeling that that TODO item is bogus anyway. The issue that I've seen is that currently, allowing non-superusers to create databases in a useful manner requires all sorts of hoop-jumping to allow the database owner to end up owning the public schema. The part of this that hasn't been justified to my satisfaction is *why* the database owner should own the public schema. He doesn't get to own, say, the integer plus operator. There is some merit in the thought that the DB owner should be able to grant and revoke access on the public schema, but that no longer requires ownership, only membership in an appropriate role. (Another wart that could do with looking into is that such a non-superuser database owner can't prevent xid wrap in his database regardless of how often he vacuums it.) The DB owner shouldn't really be responsible for vacuuming anyway. 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: [HACKERS] broken configure, broken makefile?
Gavin Sherry [EMAIL PROTECTED] writes: And this is the reason why it is old. You're asking for pain if you're using 7.0.3 and this is just the start. There are several good reasons why the community stopped supporting versions before 7.2, which we did quite some time ago. Don't expect a lot of sympathy here when 7.0 eats your data. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I agree. I think an good position of freezer is on bgwriter. My idea is: 1. Just before bgwriter writes an dirty page in LRU order, 2. Freeze tuples in the page and repair fragmentation. 3. (Replace the fsm page that has least freespace.) 4. Flush the page. This is a bad idea. The bgwriter isn't the place to be doing freezing, because there is no reasonable way for it to guarantee that all old tuples in a table (or any larger unit) have been frozen. So you'd still need VACUUM to ensure no wraparound. Plus, you can't do such changes without emitting an XLOG record, which is something we don't want happening in the bgwriter's inner loop. Even more to the point, you can't do such changes without getting a superexclusive lock on the page (not only locked, but no one else has it pinned), which is a real nonstarter for the bgwriter, both for performance and possible deadlock issues. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Pre-allocated free space for row
The bgwriter could update all columns of dead heap tuples in heap pages to NULL and thus also gain free space without the need to touch the indexes. The slot would stay used but it would need less space. Not unless it's running a transaction (consider TOAST updates). Ok, you could leave all toast pointers and the toast table as is. Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO item: set proper permissions on non-system schemas
On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: There's considerable feeling that that TODO item is bogus anyway. The issue that I've seen is that currently, allowing non-superusers to create databases in a useful manner requires all sorts of hoop-jumping to allow the database owner to end up owning the public schema. The part of this that hasn't been justified to my satisfaction is *why* the database owner should own the public schema. He should certainly be able to drop it, in addition to being able to control access to it. There is some merit in the thought that the DB owner should be able to grant and revoke access on the public schema, but that no longer requires ownership, only membership in an appropriate role. How would that work without superuser intervention, given that the ownership of public would be the same in all databases regardless of who created them? (Another wart that could do with looking into is that such a non-superuser database owner can't prevent xid wrap in his database regardless of how often he vacuums it.) The DB owner shouldn't really be responsible for vacuuming anyway. Debatable. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
Josh Berkus josh@agliodbs.com writes: So, will per-table XID tracking allow us to avoid *ever* vacuuming some tables? If your definition of ever is less than a billion transactions, sure. (As Simon points out, with time-partitioned data sets that could often be arranged, so it's not a completely silly response.) If not, what could? The only possibility for more-than-a-billion is widening XIDs to 64 bits, which would come with its own set of performance penalties. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
On Thu, Sep 01, 2005 at 04:21:58AM -, Andrew - Supernews wrote: On 2005-09-01, Alvaro Herrera [EMAIL PROTECTED] wrote: On Thu, Sep 01, 2005 at 01:57:02AM +0100, Simon Riggs wrote: If you're using autovacuum then the problem is already taken care of. autovacuum will respond only to UPDATEs and DELETEs. In the scenario I outline, these will *never* occur on the largest tables. A VACUUM would still eventually be required to freeze long lived tuples and this would not be performed by autovacuum. Hum, I don't understand -- if you don't want to vacuum the table, why run vacuum at all? You can (as of 8.1) disable autovacuum for specific tables. The exception is that you are forced to run a database-wide VACUUM once in a while (every billion-and-so), but this will hopefully disappear in 8.2 too, Wishful thinking, or do you have a concrete plan to achieve it? We talked about it during the autovacuum discussions just before feature freeze. There is a vague plan which I intend to study eventually. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com On the other flipper, one wrong move and we're Fatal Exceptions (T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Wed, Aug 31, 2005 at 09:14:42PM -0700, Josh Berkus wrote: One thing that comes to mind is that this makes somewhat easier to build a tool to write pre-built tables, for bulk-loading purposes. You just construct the binary file with the HEAP_FROZEN bit set, and then attach the file to a dummy table. (Then again, you can do it today, using a Xmin of FrozenTransactionId. I wonder why the Bizgres people isn't advocating a tool to do that. It is very hard to do with user-defined types, but for BI/DW you mostly don't need those, do you?) Hmmm ... can you expand on this a little? We'd discussed frozen partitions but hadn't thought to get around to them for a while, expecting the kind of issues which Tom just raised. What issues did he raise on this? What I'm saying is that you can write a heap file, on which the tuples would all have xmin=FrozenTransactionId, xmax=Invalid, and the corresponding bits set in the infomask. This ensures that no matter the state of the server, you can plug the file in and all tuples will be valid. The only problem is figuring out how to lay the data in the tuples themselves, w.r.t endianness and such. This is platform-dependent, so you have to write code to do it correctly. In absence of user-defined types, this should not be _too_ hard to do. Of course, such a program would in general also be Postgres-version-dependent. Note that this is a very different business from skipping the Xmin and Cmin from the tuple header -- in fact, there's no relation to that at all. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com FOO MANE PADME HUM ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] broken configure, broken makefile?
On Thursday 01 September 2005 09:15, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] writes:
And this is the reason why it is old. You're asking for pain if you're
using 7.0.3 and this is just the start.
There are several good reasons why the community stopped supporting
versions before 7.2, which we did quite some time ago. Don't expect
a lot of sympathy here when 7.0 eats your data.
Easy there guys! I'm in the process of building a new demo server for
phppgadmin. We use that for demos and testing, especially on these older
versions. On the current server we support all the way back to 7.0 (on
slackware 8 iirc). I fully agree that you don't want to be using these older
versions if you can avoid it, but just a week or so ago we got a bug report
from someone using 7.1, so those people are out there, and if we can help
them I don't see anything wrong with that.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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
[HACKERS] 8.1beta, Subtle bug in COPY in Solaris systems
Hello,
First, I'll show the warnings seen when compiling postgres on
SunOS 5.6 with gcc 3.2.1
copy.c: In function `GetDecimalFromHex':
copy.c:2660: warning: subscript has type `char'
copy.c: In function `CopyReadAttributesText':
copy.c:2805: warning: subscript has type `char'
copy.c:2813: warning: subscript has type `char'
Actually this warnings are caused by the isdigit function.
On Solaris systems, isdigit is organized as an array lookup, so all the
arguments should be casted to unsigned char.
2660c2660
if (isdigit(hex))
---
if (isdigit((unsigned char)hex))
2805c2805
if (isxdigit(hexchar))
---
if (isxdigit((unsigned
char)hexchar))
2813c2813
if
(isxdigit(hexchar))
---
if
(isxdigit((unsigned char)hexchar))
Actually that problem cause not only warnings but real bugs too,
exploiting that problem. (when the char 128 and is not casted to
unsigned, on solaris there will be a negative indices of arrays)
For example on SunOS (or any Solaris):
test=# CREATE TABLE test0 (xx char(2));
CREATE TABLE
test=# copy test0 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
\x3п
\.
test=# select length(xx) from test0;
length
1
(1 row)
But on NOT Solaris:
test=# CREATE TABLE test0 (xx char(2));
CREATE TABLE
test=# copy test0 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
\x3п
\.
test=# select length(xx) from test0;
length
2
(1 row)
I'm not sure that everybody will see that code properly due to encoding
differences. But the idea is just feed postgres with \x3 and one
character with the code 128.
Regards,
Sergey
*
Sergey E. Koposov
Max-Planck Institut fuer Astronomie
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]
--- src/backend/commands/copy.c.orig2005-09-01 15:07:01.0 +0200
+++ src/backend/commands/copy.c 2005-09-01 15:08:45.0 +0200
@@ -2657,7 +2657,7 @@
static int
GetDecimalFromHex(char hex)
{
- if (isdigit(hex))
+ if (isdigit((unsigned char)hex))
return hex - '0';
else
return tolower(hex) - 'a' + 10;
@@ -2802,7 +2802,7 @@
{
char hexchar = *cur_ptr;
- if (isxdigit(hexchar))
+ if (isxdigit((unsigned
char)hexchar))
{
int val =
GetDecimalFromHex(hexchar);
@@ -2810,7 +2810,7 @@
if (cur_ptr
line_end_ptr)
{
hexchar
= *cur_ptr;
- if
(isxdigit(hexchar))
+ if
(isxdigit((unsigned char)hexchar))
{
cur_ptr++;
val = (val 4) + GetDecimalFromHex(hexchar);
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
- Original Message - From: Dave Page dpage@vale-housing.co.uk To: Andrew Dunstan [EMAIL PROTECTED]; William ZHANG [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Sent: Thursday, September 01, 2005 3:21 PM Subject: RE: [HACKERS] Call for 7.5 feature completion And even those are a royal pain to maintain, never mind an entire set. Besides, I'm sure what William really wants is not nmake files, but VC++ Project files - but then which version do we keep? It's not like we could say that everyone should be using VS2005, so all commits would have to be VC++ 6.0 or earlier compatible, otherwise someone is bound to complain. You are right. What I want is VC++ projects(*.dsp, *.dsw). Once the project files is created, the maintance work is simply add/remove some new/deleted source files (*.c only) from the dsps. And I think VC++ 6.0 is ok, it is power enough and not so big for pgsql's development. And latter versions of VC++ can automatically convert 6.0's project files. There are also a VC++7 to VC++6 project converter on www.codeproject.com. I agree with Andrew though - maintaining VC++ project files or nmake files is just not practical - especially given that most of our developers are not Windows users. I am expecting more and more Windows users to join us. According to Andrew's advice, I will try to start a project on pgfoundry to provide the VC++ project files. Regards, Dave. ---(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: [HACKERS] TODO item: set proper permissions on non-system schemas
Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: There is some merit in the thought that the DB owner should be able to grant and revoke access on the public schema, but that no longer requires ownership, only membership in an appropriate role. How would that work without superuser intervention, given that the ownership of public would be the same in all databases regardless of who created them? Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. 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: [HACKERS] broken configure, broken makefile?
On Thu, Sep 01, 2005 at 10:15:19AM -0400, Robert Treat wrote: On Thursday 01 September 2005 09:15, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: And this is the reason why it is old. You're asking for pain if you're using 7.0.3 and this is just the start. There are several good reasons why the community stopped supporting versions before 7.2, which we did quite some time ago. Don't expect a lot of sympathy here when 7.0 eats your data. Easy there guys! I'm in the process of building a new demo server for phppgadmin. We use that for demos and testing, especially on these older versions. On the current server we support all the way back to 7.0 (on slackware 8 iirc). I fully agree that you don't want to be using these older versions if you can avoid it, but just a week or so ago we got a bug report from someone using 7.1, so those people are out there, and if we can help them I don't see anything wrong with that. The best help you can give these people is to get them onto a system released since the end of the Clinton administration. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
- Original Message - From: Andrew Dunstan [EMAIL PROTECTED] To: Dave Page dpage@vale-housing.co.uk Cc: William ZHANG [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Sent: Wednesday, August 31, 2005 10:24 PM Subject: Re: [HACKERS] Call for 7.5 feature completion Dave Page wrote: * Compile with MSVC on Win32 platforms. MySQL support it. So what? It would take a major amount of work, with no useful benefits. ... and you can compile all the client and library stuff with MSVC - just not the server nor extensions. But the audience for compiling those is far smaller. I think the most popular method to build a project on Win32 is using MSVC or Intel C. Intel C can be integrated with MSVC's IDE to help developers increase their productivity. Actually I have tried to make the backend of pgsql-8.0.3 build with MSVC 6.0, and it works well. Should I polish it and send it as a patch? Having been a Win32 developer for several years, I think it is more convenient to use MSVC's IDE than CL.exe with NMAKE.exe. Although I do not like Microsoft very much, and like to use MinGW or Cygwin to do some small tests, MSVC is more suitable for native Win32 development. If pgsql want to be the first class citizen on Windows, and want to compete with MySQL, I think supporting MSVC is important. I beleive there will be many contributions from the Win32 world. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1beta, Subtle bug in COPY in Solaris systems
Sergey E. Koposov [EMAIL PROTECTED] writes: 2660c2660 if (isdigit(hex)) --- if (isdigit((unsigned char)hex)) Sigh. We keep fixing these, and they keep creeping back in. I wish there were a way to get some more-mainstream compiler to warn about passing chars to the ctype.h functions. Thanks for the report. You only saw the three? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO item: set proper permissions on non-system schemas
On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: There is some merit in the thought that the DB owner should be able to grant and revoke access on the public schema, but that no longer requires ownership, only membership in an appropriate role. How would that work without superuser intervention, given that the ownership of public would be the same in all databases regardless of who created them? Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. Only if all db owners are equivalent. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Alvaro Herrera [EMAIL PROTECTED] writes: What I'm saying is that you can write a heap file, on which the tuples would all have xmin=FrozenTransactionId, xmax=Invalid, and the corresponding bits set in the infomask. This ensures that no matter the state of the server, you can plug the file in and all tuples will be valid. The only problem is figuring out how to lay the data in the tuples themselves, w.r.t endianness and such. This is platform-dependent, so you have to write code to do it correctly. In absence of user-defined types, this should not be _too_ hard to do. Of course, such a program would in general also be Postgres-version-dependent. Of course, it's fair to ask whether such a program would be any faster than binary-mode COPY by the time you got done ... or enough faster to justify your effort, anyway. THe only fundamental disadvantage that COPY labors under is having to write WAL records. It might be interesting to do something similar to the recent hacks for CREATE TABLE AS, so that a COPY into a table just created in the current transaction would skip writing WAL and instead fsync the table at the end. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Wed, 2005-08-31 at 22:25 -0400, Alvaro Herrera wrote: On Thu, Sep 01, 2005 at 10:45:44AM +0900, ITAGAKI Takahiro wrote: Hi, I think it would be a waste to retain xmin and cmin for frozen tuples because their values represent only 'visible for all transactions'. Additionally, most tuples in database can be frozen potentially. I think this is an interesting idea. Agreed, especially since it would avoid the need to vacuum altogether. I was thinking that when the tuple needs to be obsoleted it would need to grow to accomodate the Xmax, but you are not actually proposing to remove that, so it seems sensible. In fact, it is perfectly reasonable to remove Xmin and Cmin, because after the tuple is frozen, the Xmin never changes again. It's a good idea, but the Xmin is set to FrozenTransactionId, which is how we know it is frozen, so how can we remove Xmin? The way to do this is surely by using a row version id that is different for this format. Getting 8 or 16 bytes per row back would be a very useful gain. Now, one thing of note is that you need to compress the page in order to actually be able to use the just-freed space. VACUUM could do that, but maybe it would be better to do it on-line -- the freezing process is going to have to write the page regardless. I wonder if with your patch the page is compressed on the same VACUUM execution that freezes the tuple? Only if you do a FULL, which is currently incompatible with a FREEZE. There's no point in compressing a block if you can't also redistribute rows between blocks to fill up the spaces, so another reason why it has to be a FULL. Unless you do this at load time, which is why I guess you mention One thing that comes to mind is that this makes somewhat easier to build a tool to write pre-built tables, for bulk-loading purposes. You just construct the binary file with the HEAP_FROZEN bit set, and then attach the file to a dummy table. (Then again, you can do it today, using a Xmin of FrozenTransactionId. I wonder why the Bizgres people isn't advocating a tool to do that. It is very hard to do with user-defined types, but for BI/DW you mostly don't need those, do you?) Loading a table using COPY with frozen bits set was suggested in May, so yeh... it was suggested. At that time it was rejected, since earlier transactions would then be able to see rows they ought not be able to see. Thinking some more about this, this is only the inverse situation of a TRUNCATE. With truncate we remove tuples that ought to still be visible to pre-existing transactions. So there shouldn't really be an issue with loading pre-frozen tuples - as long as you accept the consequences for row visibility. Externally writing blocks is possible, but it bypasses a lot of other features. My current preference would be to have bulk_heap_insert() function to add a whole page at a time rather than inserting rows one at at a time. The main objective for a load is to make it disk bound; once we've achieved that by some further tuning, writing an external file would cost around the same as writing it internally from the DBMS. Oracle (direct path loader) and Teradata (Fastload) load data in complete blocks using a reduced code pathway, so I guess I was just following on, but I'm genuinely open to further persuasion if there is a better way. Having a table marked as INSERT ONLY would allow us to save 8 bytes/row, loading it pre-frozen (in some way) would save another 8 bytes/row and allow us to permanently avoid VACUUMing the table. That would be even better when we have per-table XID wrap avoidance. Best Regards, Simon Riggs ---(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: [HACKERS] Call for 7.5 feature completion
William wrote: You are right. What I want is VC++ projects(*.dsp, *.dsw). Once the project files is created, the maintance work is simply add/remove some new/deleted source files (*.c only) from the dsps. And I think VC++ 6.0 is ok, it is power enough and not so big for pgsql's development. And latter versions of VC++ can automatically convert 6.0's project files. There are also a VC++7 to VC++6 project converter on www.codeproject.com. You might be surprised to know that this has been already done. Back in the 7.2 cycle there was a win32 build floating around that compiled and built inside of visual studio 6. I think Jan Wieck was one of the people involved in the effort. That would be a good place to start looking. Merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] On hardcoded type aliases and typmod for user types
On Thu, Sep 01, 2005 at 08:50:27AM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Simply pass the (Node*) from the parser and let the function sort it out itself. Except now they have to be written in C. Is this unreasonable, Nope. You're not going to be writing any interesting datatypes without using C, anyway. I'd go with this one to minimize flexibility problems. Ack I'd be inclined to stick with the convention that internally typmod is a single int32; that is really wired into way too many APIs to consider changing. varchar could do something like using 24 bits for the length and 8 bits for an encoded indication of the charset. With the unfortunate effect that strings are limited to 16Mb instead of 1Gb. Not sure if people will be happy with that one. For my locale experiments I used my taggedtypes module to embed the locale into the data itself, I imagine something similar could be used. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpdGAU0Ug6IH.pgp Description: PGP signature
Re: [HACKERS] TODO item: set proper permissions on non-system schemas
Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. Only if all db owners are equivalent. If you don't want some of them to have control over their public schemas, you don't grant them membership in this group. (Which, by the way, is *more* flexible than a hack to reassign the schema ownership to the DB owner automatically.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Version number in psql banner
A release or two ago we added the version number to the psql welcome banner. I noticed that quite a few people interpret that as the server version. Somehow, the explicit display of the version numbers leads them to make inferences that they would otherwise not bother about. Has anyone else experienced that? I suppose there was a reason we added the version number there, but I can't recall it. Could we make that more clear? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Using multi-locale support in glibc
Browsing the glibc stuff for locales I noticed that glibc does actually allow you to specify the collation order to strcoll and friends. The feature is however marked with: Attention: all these functions are *not* standardized in any form. This is a proof-of-concept implementation. They do however work fine. I used my taggedtypes module to create a type that binds the collation order to the text strings and the results can be seen below. 1. Is something supported by glibc usable for us (re portability to non-glibc platforms)? 2. Should we be trying to use an interface that's specifically marked as unstable? 3. What's the plan to support multiple collate orders? There was a message about it last year but I don't see much progress. 4. It makes some things more difficult. For example, my database is UNICODE and until I specified a UTF8 locale it didn't come out right. AFAIK the only easy way to determine if something is UTF8 compatable is to use locale -k charmap. The C interface is hidden. It should be possible to compile a list of locales and allow only ones matching the database. Or automatically convert the strings, the conversion functions exist. 5. Maybe we should evaluate the interface and give feedback to the glibc developers to see if it can be made more stable. If you want to have a look to see what's available, use: rgrep -3 locale_t /usr/include/ |less Have a nice day, PS. The code to test this can be found at: http://svana.org/kleptog/pgsql/taggedtypes.html --- TEST OUTPUT --- test=# select strings from taggedtypes.locale_test order by locale_text( strings, 'C' ); strings - Test2 Tést1 Tëst1 test1 tèst2 (5 rows) test=# select strings from taggedtypes.locale_test order by locale_text( strings, 'en_US' ); strings - Tëst1 Tést1 tèst2 test1 Test2 (5 rows) test=# select strings from taggedtypes.locale_test order by locale_text( strings, 'nl_NL' ); ERROR: Locale 'nl_NL' not supported by library test=# select strings from taggedtypes.locale_test order by locale_text( strings, 'en_AU.UTF-8' ); strings - test1 Tést1 Tëst1 Test2 tèst2 (5 rows) -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpCaa0baPW34.pgp Description: PGP signature
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Thu, Sep 01, 2005 at 11:08:36AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: What I'm saying is that you can write a heap file, on which the tuples would all have xmin=FrozenTransactionId, xmax=Invalid, and the corresponding bits set in the infomask. This ensures that no matter the state of the server, you can plug the file in and all tuples will be valid. The only problem is figuring out how to lay the data in the tuples themselves, w.r.t endianness and such. This is platform-dependent, so you have to write code to do it correctly. In absence of user-defined types, this should not be _too_ hard to do. Of course, such a program would in general also be Postgres-version-dependent. Of course, it's fair to ask whether such a program would be any faster than binary-mode COPY by the time you got done ... or enough faster to justify your effort, anyway. It may not be faster generating the data in the first place, but you don't have to vacuum the table, nor you are subject to hint bits changing, resulting in more unnecessary I/O. This can't be avoided with COPY, because there's always the chance that it will fail partway through, so you can't write frozen tuples. With an external program, you can just dump the invalid line somewhere else and continue with the rest. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Just treat us the way you want to be treated + some extra allowance for ignorance.(Michael Brusser) ---(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: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
On Thu, 2005-09-01 at 10:29 +0100, Simon Riggs wrote: On Wed, 2005-08-31 at 22:21 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2005-08-31 at 19:24 -0400, Tom Lane wrote: If you don't remove any tuples, you don't scan the indexes anyway IIRC. No. Even if you remove *zero* tuples, an index is still scanned twice. Once to not delete the rows and once to not delete the pages. Yeah? Well, that could probably be improved with a less intrusive fix, that is, one that does it automatically instead of involving the user. I really really do not like proposals to introduce still another kind of VACUUM. We have too many already; any casual glance through the archives will show that most PG users don't have a grip on when to use VACUUM FULL vs VACUUM. Throwing in some more types will make that problem exponentially worse. I'll post my proposal for changing that, so we can see the two alternatives. I'm easy either way at the moment. Currently, VACUUM will always scan each index twice, even if there are no tuples to remove from the index. Once in index_bulk_delete() and once in index_vacuum_cleanup() (at least for the nbtree and rtree AMs). My first proposal is to add an extra parameter onto the index_bulk_delete() call - ntuples. If ntuples == 0 then btbulkdelete() will avoid scanning and return immediately. If a scan occurs, then we keep track of how many tuples have been marked deleted and stop the scan when we have reached this number. This approach means that two use cases will be optimised: 1) where the VACUUM does not remove any tuples 2) where all the deletions are on the LHS of the index, as would be the case in a frequently updated table like Shipments where an index had either a monotonically increasing key or a time based key. Optimising index_vacuum_cleanup() is more complex. btvacuumcleanup() marks pages deleted OR adds pages already deleted onto the freelist. Non-FULL VACUUM never does both at the same time for MVCC reasons, so it takes two VACUUMs to actually move a page back onto the freelist. So performing a scan of the index during index_vacuum_cleanup() does actually have a purpose when there are no tuples deleted during the vacuum because it might be the subsequent VACUUM coming back later to freelist the deleted pages. (This is interesting, because autovacuum knows nothing of the deleted pages and may not trigger a second vacuum, even though one would be beneficial). index_vacuum_cleanup() knows how many rows are to be removed because it is already part of the info structure passed between index_bulk_delete() and index_vacuum_cleanup(). There are two options: 1) avoid the scan when there are no dead tuples, and ignore the possibility that a VACUUM might be doing a follow-up scan to remove previously deleted tuples. 2) close the loophole by recording whether a previous VACUUM had set any pages as deleted, or not. If the index was clean, then we would skip the scan. 1) seems the best option since 2) is a lot of work for less gain. If all of that seems OK, I'll code a patch. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1beta, Subtle bug in COPY in Solaris systems
On Thu, 1 Sep 2005, Tom Lane wrote: Sergey E. Koposov [EMAIL PROTECTED] writes: 2660c2660 if (isdigit(hex)) --- if (isdigit((unsigned char)hex)) Sigh. We keep fixing these, and they keep creeping back in. I wish there were a way to get some more-mainstream compiler to warn about passing chars to the ctype.h functions. Thanks for the report. You only saw the three? In fact, I saw two other warnings, but they should not cause any problems (at least on my understanding) : gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-strict-aliasing -DFRONTEND -I. -I../../../src/interfaces/libpq -I../../../src/include -I/systools/include -c -o psqlscan.o psqlscan.c In file included from ../../../src/include/c.h:53, from ../../../src/include/postgres_fe.h:21, from psqlscan.l:40: ../../../src/include/pg_config.h:659:1: warning: _FILE_OFFSET_BITS redefined In file included from /systools/lib/gcc-lib/sparc-sun-solaris2.7/3.2.1/include/stdio.h:36, from psqlscan.c:13: /usr/include/sys/feature_tests.h:96:1: warning: this is the location of the previous definition gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-strict-aliasing -Wno-error -I./../include -I. -I../../../../src/include -I/systools/include -DMAJOR_VERSION=4 -DMINOR_VERSION=1 -DPATCHLEVEL=1 -c -o preproc.o preproc.c In file included from preproc.y:6412: pgc.c: In function `yylex': pgc.c:1504: warning: label `find_rule' defined but not used preproc.y: At top level: pgc.c:3565: warning: `yy_flex_realloc' defined but not used With Best Regards, Sergey * Sergey E. Koposov Max-Planck Institut fuer Astronomie Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Alvaro, What issues did he raise on this? On having no Xmin. What I'm saying is that you can write a heap file, on which the tuples would all have xmin=FrozenTransactionId, xmax=Invalid, and the corresponding bits set in the infomask. This ensures that no matter the state of the server, you can plug the file in and all tuples will be valid. The only problem is figuring out how to lay the data in the tuples themselves, w.r.t endianness and such. This is platform-dependent, so you have to write code to do it correctly. In absence of user-defined types, this should not be _too_ hard to do. Of course, such a program would in general also be Postgres-version-dependent. So, bulk loading by file generation? So the idea is that you would generate a properly formatted PostgreSQL table file, and then in one transaction create the table and attach it? Seems like this would have the additional limitation of being useful only for loading new partitions/new tables. However, it would have some significant advantages for bulk loading ... chiefly that the data page generation and associated computations could be done *off* the database server. This might help considerably in getting around the 100mb/s data computation ceiling we're hitting ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] broken configure, broken makefile?
You current problem has to do with multiline gcc --version. You could
backport the fix for that, or you could wrap gcc with a script so it
only reports only line for --version.
I have 7.1.3 running here, I patched configure as attached. Very ugly,
but it works.
Easy there guys! I'm in the process of building a new demo server for
phppgadmin. We use that for demos and testing, especially on these older
versions. On the current server we support all the way back to 7.0 (on
slackware 8 iirc). I fully agree that you don't want to be using these older
versions if you can avoid it, but just a week or so ago we got a bug report
from someone using 7.1, so those people are out there, and if we can help
them I don't see anything wrong with that.
Yeah -- I'm supporting a company running 7.1.3, which has several
reliability problems and has suffered from corruption, but has had a
long way to upgrading. Fortunately all the problems have convinced them
of the importance of the upgrade. Previously they had so much trouble,
and the system was performing so horribly, that they had asked help from
IBM to migrate to DB2. Someone managed to convince them to upgrade Pg
instead, and got me to help with that -- in the meantime, we have to
keep the 7.1.3 server running.
--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
La naturaleza, tan fr?gil, tan expuesta a la muerte... y tan viva
Index: configure
===
RCS file: /home/alvherre/cvs/pgsql/configure,v
retrieving revision 1.127.2.2
diff -c -r1.127.2.2 configure
*** configure 13 Aug 2001 20:09:03 - 1.127.2.2
--- configure 9 Mar 2005 18:14:37 -
***
*** 1617,1623
# Create compiler version string
if test x$GCC = xyes ; then
! cc_string=GCC `${CC} --version`
else
cc_string=$CC
fi
--- 1617,1623
# Create compiler version string
if test x$GCC = xyes ; then
! cc_string=GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-10)
else
cc_string=$CC
fi
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] On hardcoded type aliases and typmod for user types
Martijn van Oosterhout kleptog@svana.org writes: On Thu, Sep 01, 2005 at 08:50:27AM -0400, Tom Lane wrote: varchar could do something like using 24 bits for the length and 8 bits for an encoded indication of the charset. With the unfortunate effect that strings are limited to 16Mb instead of 1Gb. No, only that you can't declare a specific maxlength for a varchar that exceeds 16Mb ... and guess what, there is already a smaller limit than that, on the grounds that if a user writes varchar(10) he needs a cluebat anyway. I think you might have momentarily confused typmod with the varlena length word of an individual value. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] PG_PAGE_LAYOUT_VERSION - Should be Documented as 3?
doc/src/sgml/storage.sgml says: The last 2 bytes of the page header, structfieldpd_pagesize_version/structfield, store both the page size and a version indicator. Beginning with productnamePostgreSQL/productname 8.0 the version number is 2; productnamePostgreSQL/productname 7.3 and 7.4 used version number 1; prior releases used version number 0. But src/include/storage/bufpage.h says: /* * Page layout version number 0 is for pre-7.3 Postgres releases. * Releases 7.3 and 7.4 use 1, denoting a new HeapTupleHeader layout. * Release 8.0 changed the HeapTupleHeader layout again. * Release 8.1 redefined HeapTupleHeader infomask bits. */ #define PG_PAGE_LAYOUT_VERSION 3 So, should the attached be applied? Index: storage.sgml === RCS file: /var/local/pgcvs/pgsql/doc/src/sgml/storage.sgml,v retrieving revision 1.6 diff -c -r1.6 storage.sgml *** storage.sgml 28 Apr 2005 21:47:09 - 1.6 --- storage.sgml 1 Sep 2005 15:32:35 - *** *** 437,443 The last 2 bytes of the page header, structfieldpd_pagesize_version/structfield, store both the page size and a version indicator. Beginning with ! productnamePostgreSQL/productname 8.0 the version number is 2; productnamePostgreSQL/productname 7.3 and 7.4 used version number 1; prior releases used version number 0. (The basic page layout and header format has not changed in these versions, --- 437,444 The last 2 bytes of the page header, structfieldpd_pagesize_version/structfield, store both the page size and a version indicator. Beginning with ! productnamePostgreSQL/productname 8.1 the version number is 3; ! productnamePostgreSQL/productname 8.0 used version number 2; productnamePostgreSQL/productname 7.3 and 7.4 used version number 1; prior releases used version number 0. (The basic page layout and header format has not changed in these versions, ---(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: [HACKERS] TODO item: set proper permissions on non-system schemas
On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: Andrew - Supernews [EMAIL PROTECTED] writes: On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. Only if all db owners are equivalent. If you don't want some of them to have control over their public schemas, you don't grant them membership in this group. What if you don't want them to have control over _each other's_ public schemas? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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: [HACKERS] TODO item: set proper permissions on non-system schemas
Tom Lane wrote: Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. Won't that suddenly grant the owner of foo_db dbadmin rights in bar_db? That seems to violate the principle of least surprise. 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
[HACKERS] upgrade path / versioning roles
I think we still recommend using *new* pg_dump to dump *old* server when upgrading. If one tries that with today's pg_dump (8.1beta1) against a 8.1devel server of 6 May, i.e., predating roles, one gets: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: relation pg_catalog.pg_roles does not exist pg_dump: The command was: SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM pg_namespace Cheers, Patrick ---(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: [HACKERS] Remove xmin and cmin from frozen tuples
Tom, THe only fundamental disadvantage that COPY labors under is having to write WAL records. It might be interesting to do something similar to the recent hacks for CREATE TABLE AS, so that a COPY into a table just created in the current transaction would skip writing WAL and instead fsync the table at the end. Yes, I thought we discussed doing this for empty tables -- it would be, per our tests, a +10% to +30% boost to COPY. But there was some problem the patch? -- Josh Berkus Aglio Database Solutions San Francisco ---(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: [HACKERS] TODO item: set proper permissions on non-system schemas
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. Won't that suddenly grant the owner of foo_db dbadmin rights in bar_db? That seems to violate the principle of least surprise. I'm assuming here that the various dbowners aren't even allowed to connect to each others' databases. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Call for 7.5 feature completion
On Thu, Sep 01, 2005 at 09:17:38AM +0800, William ZHANG wrote: Dave Page wrote: * Compile with MSVC on Win32 platforms. MySQL support it. So what? It would take a major amount of work, with no useful benefits. ... and you can compile all the client and library stuff with MSVC - just not the server nor extensions. But the audience for compiling those is far smaller. I think the most popular method to build a project on Win32 is using MSVC or Intel C. Intel C can be integrated with MSVC's IDE to help developers increase their productivity. Actually I have tried to make the backend of pgsql-8.0.3 build with MSVC 6.0, and it works well. Should I polish it and send it as a patch? Having been a Win32 developer for several years, I think it is more convenient to use MSVC's IDE than CL.exe with NMAKE.exe. Although I do not like Microsoft very much, and like to use MinGW or Cygwin to do some small tests, MSVC is more suitable for native Win32 development. If pgsql want to be the first class citizen on Windows, and want to compete with MySQL, I think supporting MSVC is important. I beleive there will be many contributions from the Win32 world. I think supporting MSVC is important, certainly (though I think that supporting the Intel compiler is even better, as the only compelling reason, IMO, to switch for the server end is generated code quality). But that's very different from supporting visual studio. I've been doing cross-platform development on a big codebase for years, and the idea of trying to use the proprietary build environments on each platform, and expecting to keep them sufficiently in-sync that the end result is actually comparable on each platform is laughable. And that's on a much smaller, simpler codebase than PG with a much smaller, more integrated development team. I use gmake or cons everywhere. On Windows I run them under cygwin and have them call the MSVC commandline compiler. It all works fine. And it doesn't stop me from using Visual Studio to edit the code, run the debugger or anything like that. On OS X I can use XCode. On Solaris I use the Forte environment. On Linux I use emacs and gcc. And that's all on the same codebase with the same makefile checked out from the same CVS repository. Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Version number in psql banner
On Thu, Sep 01, 2005 at 05:30:34PM +0200, Peter Eisentraut wrote: A release or two ago we added the version number to the psql welcome banner. I noticed that quite a few people interpret that as the server version. Somehow, the explicit display of the version numbers leads them to make inferences that they would otherwise not bother about. Has anyone else experienced that? I suppose there was a reason we added the version number there, but I can't recall it. Could we make that more clear? I think the rationale for not adding the server version is that you could tell people to do select version(), so it would be unneeded verbosity, but certainly a lot of people doesn't even know they can do that. I think by far the easiest and clearest is to show both psql's version and the server version. Not the whole version() string, as that is too verbose -- just the version number. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Cuando miro a alguien, m?s me atrae c?mo cambia que qui?n es (J. Binoche) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Version number in psql banner
Peter, A release or two ago we added the version number to the psql welcome banner. I noticed that quite a few people interpret that as the server version. Somehow, the explicit display of the version numbers leads them to make inferences that they would otherwise not bother about. Has anyone else experienced that? I suppose there was a reason we added the version number there, but I can't recall it. Could we make that more clear? Well, Bruce just drafted a patch to warn when the PSQL version and the server version don't match up, because of PSQL incompatibilities. However, that won't help for minor versions. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Version number in psql banner
On Thursday 01 September 2005 08:30, Peter Eisentraut wrote: A release or two ago we added the version number to the psql welcome banner. I noticed that quite a few people interpret that as the server version. Somehow, the explicit display of the version numbers leads them to make inferences that they would otherwise not bother about. Has anyone else experienced that? I suppose there was a reason we added the version number there, but I can't recall it. Could we make that more clear? Yes I've seen the same frustration from users, I think the banner should say something along the lines of psql client version foo connected to server bar version sfoo -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(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: [HACKERS] Remove xmin and cmin from frozen tuples
On Thu, Sep 01, 2005 at 09:20:48AM -0700, Josh Berkus wrote: What I'm saying is that you can write a heap file, on which the tuples would all have xmin=FrozenTransactionId, xmax=Invalid, and the corresponding bits set in the infomask. This ensures that no matter the state of the server, you can plug the file in and all tuples will be valid. So, bulk loading by file generation? So the idea is that you would generate a properly formatted PostgreSQL table file, and then in one transaction create the table and attach it? Exactly. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Changing the world ... one keyboard at a time! (www.DVzine.org) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PG_PAGE_LAYOUT_VERSION - Should be Documented as 3?
On Thu, Sep 01, 2005 at 03:36:12PM +, Matt Miller wrote: But src/include/storage/bufpage.h says: /* * Page layout version number 0 is for pre-7.3 Postgres releases. * Releases 7.3 and 7.4 use 1, denoting a new HeapTupleHeader layout. * Release 8.0 changed the HeapTupleHeader layout again. * Release 8.1 redefined HeapTupleHeader infomask bits. */ #define PG_PAGE_LAYOUT_VERSION 3 So, should the attached be applied? Also it would be nice to include a patch to mention that piece of documentation in the comment, so when we increment the version number again we remember to update the docs. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com Thou shalt check the array bounds of all strings (indeed, all arrays), for surely where thou typest foo someone someday shall type supercalifragilisticexpialidocious (5th Commandment for C programmers) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remove xmin and cmin from frozen tuples
On Thu, Sep 01, 2005 at 04:34:09PM +0100, Simon Riggs wrote: On Wed, 2005-08-31 at 22:25 -0400, Alvaro Herrera wrote: I was thinking that when the tuple needs to be obsoleted it would need to grow to accomodate the Xmax, but you are not actually proposing to remove that, so it seems sensible. In fact, it is perfectly reasonable to remove Xmin and Cmin, because after the tuple is frozen, the Xmin never changes again. It's a good idea, but the Xmin is set to FrozenTransactionId, which is how we know it is frozen, so how can we remove Xmin? The way to do this is surely by using a row version id that is different for this format. Per Takahiro's patch, you don't need to set the Xmin to FrozenTransactionId -- what you do instead is set a bit in the infomask. Now, one thing of note is that you need to compress the page in order to actually be able to use the just-freed space. VACUUM could do that, but maybe it would be better to do it on-line -- the freezing process is going to have to write the page regardless. I wonder if with your patch the page is compressed on the same VACUUM execution that freezes the tuple? Only if you do a FULL, which is currently incompatible with a FREEZE. Well, if we are going to mess with what FREEZE is doing, we can as well make it compress the page. Note that to compress the page you don't need to touch the indexes. I don't remember the exact reason why FULL is incompatible with FREEZE, but AFAIR it's not fundamentally unsolvable (just very hard.) There's no point in compressing a block if you can't also redistribute rows between blocks to fill up the spaces, so another reason why it has to be a FULL. That's a good point. One thing that comes to mind is that this makes somewhat easier to build a tool to write pre-built tables, for bulk-loading purposes. You just construct the binary file with the HEAP_FROZEN bit set, and then attach the file to a dummy table. Loading a table using COPY with frozen bits set was suggested in May, so yeh... it was suggested. I'm not proposing to use COPY for that. It has loads of problems, which is why the patch was rejected. Using an external program is a different matter. Externally writing blocks is possible, but it bypasses a lot of other features. Like what? I don't really care for this feature, mind you -- I was merely mentioning the idea as it crossed my mind. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com The problem with the facetime model is not just that it's demoralizing, but that the people pretending to work interrupt the ones actually working. (Paul Graham) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Josh Berkus josh@agliodbs.com writes: THe only fundamental disadvantage that COPY labors under is having to write WAL records. It might be interesting to do something similar to the recent hacks for CREATE TABLE AS, so that a COPY into a table just created in the current transaction would skip writing WAL and instead fsync the table at the end. Yes, I thought we discussed doing this for empty tables -- it would be, per our tests, a +10% to +30% boost to COPY. But there was some problem the patch? I have seen no such patch AFAIR. 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: [HACKERS] Minimally avoiding Transaction Wraparound in VLDBs
Simon Riggs [EMAIL PROTECTED] writes: My first proposal is to add an extra parameter onto the index_bulk_delete() call - ntuples. If ntuples == 0 then btbulkdelete() will avoid scanning and return immediately. If a scan occurs, then we keep track of how many tuples have been marked deleted and stop the scan when we have reached this number. This seems reasonable. I think you overstate the value of an early stop, but it can't hurt given the minimal cost of making the check. 1) avoid the scan when there are no dead tuples, and ignore the possibility that a VACUUM might be doing a follow-up scan to remove previously deleted tuples. I think this is a pretty bad idea. Your analysis assumes that the two scans are equally expensive, which is about as far from true as is possible. In the first place, the first scan is in index order while the second is in physical order (hence can benefit from readahead). In the second place, the first scan iterates over each item on each page, where the second does not. So what you're proposing is to install an unavoidable disk space bloat mechanism in order to save only a small part of the cost of VACUUM. (What I'd really like to see is a way to do the first scan in physical order, but we'd need a different locking approach AFAICS --- see the notes in nbtree/README.) Keeping track of whether there's any work pending for the second pass doesn't seem too unreasonable to me. It was left undone in the original coding on the KISS principle, but it could certainly be done. I'd still like to see some demonstration first that this part of index vacuuming is a significant cost compared to the heap and first-pass index costs. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] upgrade path / versioning roles
Patrick Welche [EMAIL PROTECTED] writes: I think we still recommend using *new* pg_dump to dump *old* server when upgrading. If one tries that with today's pg_dump (8.1beta1) against a 8.1devel server of 6 May, i.e., predating roles, one gets: [ shrug... ] The current code is expecting that 8.1 means what it means today. For dumping from a between-releases snapshot, you'd better use the pg_dump from the same snapshot. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO item: set proper permissions on non-system schemas
On 2005-09-01, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Change the ownership of public in template1 to be a dbadmin group. Grant membership in dbadmin to all the DB owners. End of problem. Won't that suddenly grant the owner of foo_db dbadmin rights in bar_db? That seems to violate the principle of least surprise. I'm assuming here that the various dbowners aren't even allowed to connect to each others' databases. Which implies either that you limit each dbowner to one db (in which case why give them createdb privilege in the first place) or that you require superuser intervention to modify pg_hba for each database created. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PG_PAGE_LAYOUT_VERSION - Should be Documented as 3?
Matt Miller [EMAIL PROTECTED] writes: doc/src/sgml/storage.sgml says: There's a number of things not updated yet in that file :-( I believe it hasn't heard of pg_twophase either. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Using multi-locale support in glibc
Martijn van Oosterhout kleptog@svana.org writes: 1. Is something supported by glibc usable for us (re portability to non-glibc platforms)? Nope. Sorry. 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: [HACKERS] upgrade path / versioning roles
Patrick Welche [EMAIL PROTECTED] writes: I tried the fix mentioned in the earlier message to encourage validation. Now dumping this fixed database, and loadinging it into the new database gives: ALTER FUNCTION psql:./huge.db:4403: ERROR: function plpgsql_validator(oid) does not exist CREATE FUNCTION ALTER FUNCTION psql:./huge.db:4517: ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. Ah, right, *that's* why it's a good idea to have the dependency from the language to the function ;-) ... else there's no guarantee pg_dump will dump them in the right order. If you want you could add a suitable pg_depend row. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remove xmin and cmin from frozen tuples
Alvaro Herrera [EMAIL PROTECTED] writes: On Thu, Sep 01, 2005 at 04:34:09PM +0100, Simon Riggs wrote: On Wed, 2005-08-31 at 22:25 -0400, Alvaro Herrera wrote: Now, one thing of note is that you need to compress the page in order to actually be able to use the just-freed space. VACUUM could do that, but maybe it would be better to do it on-line -- the freezing process is going to have to write the page regardless. I wonder if with your patch the page is compressed on the same VACUUM execution that freezes the tuple? Only if you do a FULL, which is currently incompatible with a FREEZE. Well, if we are going to mess with what FREEZE is doing, we can as well make it compress the page. Anyone looked at the code lately??? PageRepairFragmentation is part of any kind of vacuum. As long as you don't reassign tuple IDs (which it doesn't) there's no impact on indexes. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Version number in psql banner
Darcy Buskermolen [EMAIL PROTECTED] writes: Yes I've seen the same frustration from users, I think the banner should say something along the lines of psql client version foo connected to server bar version sfoo That seems overly verbose, particularly in the normal case where the versions are indeed the same. I could live with seeing a display like that when the versions are different. The other question is what counts as different --- do we want to complain like this if the minor versions are different? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] rename constraint behavior for duplicate names?
I'm starting to get into PostgreSQL development by implementing: %Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME from the TODO. I've been looking through the code from CommentConstraint and ATExecDropConstraint and they error out on duplicate constraint names for a relation. However, ADD CONSTRAINT's code checks for duplicates and errors out, so would the stuff in comment/drop be useless checks then? And I would not have to worry about duplicate constraint names for my rename code? Allan Wang ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] GRANT/roles problem: grant is shown as from login role
Consider the following example:
regression=# create user u1;
CREATE ROLE
regression=# create user u2;
CREATE ROLE
regression=# create user u3;
CREATE ROLE
regression=# grant u1 to u2;
GRANT ROLE
regression=# \c - u1
You are now connected as new user u1.
regression= create table t1(f1 int);
CREATE TABLE
regression= grant select on t1 to u3;
GRANT
regression= \c - u2
You are now connected as new user u2.
regression= grant update on t1 to u3;
GRANT
regression= \z t1
Access privileges for database regression
Schema | Name | Type |Access privileges
+--+---+-
public | t1 | table | {u1=arwdRxt/u1,u3=r/u1,u3=w/u2}
(1 row)
It's correct that u2 can grant privileges as if he were u1, but I think
that the privileges need to be shown as granted *by* u1. We learned
this lesson some time ago in connection with grants issued by
superusers. Given the above configuration, u1 (or other members of his
role) cannot revoke the privileges granted by u2, which is surely
undesirable since u2 had no independent right to grant those privileges.
I seem to recall that there were some other bad consequences stemming
from having rights appearing in an ACL that could not be traced via
GRANT OPTIONs to the actual object owner.
I think this means that pg_class_ownercheck and related routines can't
simply return yes, you have this privilege ... they need to show which
role you have the privilege as. And what happens if you actually have
the privilege via multiple paths --- which one gets chosen? Or imagine
that you do GRANT SELECT,UPDATE ON ... and you have grant options for
SELECT via one role, for UPDATE via another.
This is looking a bit messy. Maybe for GRANT/REVOKE, we have to insist
that privileges do not inherit, you have to actually be SET ROLE'd to
whatever role has the authority to do the grant. I haven't figured out
how the SQL spec avoids this problem, considering that they do have the
concept of rights inheriting for roles.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
On Mon, 29 Aug 2005, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: # ALTER TABLE table ALTER COLUMN field1 type boolean; ERROR: column field1 cannot be cast to type pg_catalog.bool Should this not work? No, because there's no built-in cast from smallint to bool. You could do something like ... type boolean using case when field1=0 then false else true end; 'k, I just took a read through the CREATE CAST man page, and don't think I can use that for this, but is there some way I can create a cast for this, so that we don't have to go through the complete application and change VALUES ( 0 ); to VALUES ( '0' ); ... Again, from reading the man page, I'm guessing not, but just want to make sure that I haven't missed anything ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Marc G. Fournier [EMAIL PROTECTED] writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took a read through the CREATE CAST man page, and don't think I can use that for this, Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using multi-locale support in glibc
On Thu, Sep 01, 2005 at 01:46:00PM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: 1. Is something supported by glibc usable for us (re portability to non-glibc platforms)? Nope. Sorry. Do we have some platforms that don't have any multi-language support? I mean, we don't have a complete thread library but a wrapper around the ones used on the platform. Couldn't we make a similar wrapper that used glibc if it was available, windows native if it's available, etc... That way we conform to the platform rather than a version of the unicode collating set that postgresql happens to ship with it. For example, Windows doesn't use standard Unicode sorting rules, do we care if people come complaining that postgresql sorts different from their app? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgprsJeImdmfb.pgp Description: PGP signature
Re: [HACKERS] rename constraint behavior for duplicate names?
Allan Wang [EMAIL PROTECTED] writes: I've been looking through the code from CommentConstraint and ATExecDropConstraint and they error out on duplicate constraint names for a relation. However, ADD CONSTRAINT's code checks for duplicates and errors out, so would the stuff in comment/drop be useless checks then? And I would not have to worry about duplicate constraint names for my rename code? Hmm ... there seems to be a certain amount of version skew here. Awhile back (experimentation says it was up through 7.2) we would allow multiple foreign key constraints with the same name, and with a name duplicating a check constraint ... but not AFAICS duplicate check constraint names. I think these various bits of code probably need to be brought into agreement about what the plan is. If we are going to enforce constraint name uniqueness then there ought to be a unique index guaranteeing it (which in turn would allow simplification of the lookup code). Note however that it's customary to check for duplication and issue a specific error message for it --- unique key violation isn't considered a friendly error message. The index should just serve as a backstop in case of race conditions or other unforeseen problems. It strikes me BTW that having pg_constraint cover both table and domain constraints was probably a dumb idea, and that normalization principles would suggest splitting it into one table for each purpose. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took a read through the CREATE CAST man page, and don't think I can use that for this, Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. That probably won't help him much with values(0): andrew=# create function ibool(smallint) returns boolean language sql as $$ select $1 0 $$; CREATE FUNCTION andrew=# create cast (smallint as boolean) with function ibool(smallint) as implicit; CREATE CAST andrew=# insert into foobool values(0); ERROR: column x is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. Is there a way to make the builtin int to bool cast implicit? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. That probably won't help him much with values(0): If I remember the context of the original request, it wasn't about that anyway, but about dealing with an existing smallint column. Is there a way to make the builtin int to bool cast implicit? I think you'd have to go and hack the pg_cast entry ... but that cast is new in 8.1 anyway, so it doesn't apply to Marc's problem (yet). If we want to make it cover that specific scenario, changing it to AS ASSIGNMENT would be sufficient; I don't think it needs to be IMPLICIT. (I generally find cross-type-category implicit casts to be dangerous.) 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: [HACKERS] upgrade path / versioning roles
On Thu, Sep 01, 2005 at 02:31:15PM -0400, Tom Lane wrote: Patrick Welche [EMAIL PROTECTED] writes: I think we still recommend using *new* pg_dump to dump *old* server when upgrading. If one tries that with today's pg_dump (8.1beta1) against a 8.1devel server of 6 May, i.e., predating roles, one gets: [ shrug... ] The current code is expecting that 8.1 means what it means today. For dumping from a between-releases snapshot, you'd better use the pg_dump from the same snapshot. OK - that way around just gets the NOTICE psql:./huge.db:11: NOTICE: SYSID can no longer be specified - fair enough. BTW there was an earlier thread about plpgsql validation. The aforementioned 8.1devel database has a long history and createlang was run against an early 7 incarnation, with pg_dumpall - new database all the way to the present. I tried the fix mentioned in the earlier message to encourage validation. Now dumping this fixed database, and loadinging it into the new database gives: ALTER FUNCTION psql:./huge.db:4403: ERROR: function plpgsql_validator(oid) does not exist CREATE FUNCTION ALTER FUNCTION psql:./huge.db:4517: ERROR: language plpgsql does not exist HINT: You need to use createlang to load the language into the database. now to unfix the fix... Cheers, Patrick ---(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: [HACKERS] Using multi-locale support in glibc
Martijn van Oosterhout kleptog@svana.org writes: Do we have some platforms that don't have any multi-language support? I mean, we don't have a complete thread library but a wrapper around the ones used on the platform. Couldn't we make a similar wrapper that used glibc if it was available, windows native if it's available, etc... That way we conform to the platform rather than a version of the unicode collating set that postgresql happens to ship with it. That seems likely to be the worst of all possible worlds :-(. As to the first point, our problem with the standard locale support is that (a) it doesn't conveniently/cheaply support use of multiple locales per program, and (b) it fails to expose (portably) information that we need such as the character set assumed by a locale setting. A wrapper around that might hide the convenience problem, but not the performance problem and definitely not the hidden-information problem. As to the second point, our experience with similar issues in the timezone library says that platform-dependent behavior is the last thing we want. I think we're going to end up doing just what we did with timezones, ie, create our own library --- hopefully based on someone else's work rather than rolled from scratch, but we'll feel free to whack the API around until we like it. No one's quite had the stomach to do that yet though ... in part I suppose we're hoping a good library will drop into our laps. (The reason thread support is a poor analogy is that we don't actually care about threads; we only support them to the extent the platform wants us to. The requirements for locale and timezones are driven in the other direction, ie, we need more than most platforms are willing to give.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] rename constraint behavior for duplicate names?
On Thu, 2005-09-01 at 17:16 -0400, Tom Lane wrote: Allan Wang [EMAIL PROTECTED] writes: I've been looking through the code from CommentConstraint and ATExecDropConstraint and they error out on duplicate constraint names for a relation. However, ADD CONSTRAINT's code checks for duplicates and errors out, so would the stuff in comment/drop be useless checks then? And I would not have to worry about duplicate constraint names for my rename code? Note however that it's customary to check for duplication and issue a specific error message for it --- unique key violation isn't considered a friendly error message. The index should just serve as a backstop in case of race conditions or other unforeseen problems. Alright, I see why the checks are still needed. The unique index should be on relname, conname right? Also looking into DROP CONSTRAINT's code, it gives a notice about multiple constraint names dropped when RemoveRelConstraints(rel, conname) returns 1. This check isn't needed anymore right? Also RemoveRelConstraints can be simplified to assume only one row will need removing, and be turned into a void function? Allan Wang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] rename constraint behavior for duplicate names?
Allan Wang [EMAIL PROTECTED] writes: Alright, I see why the checks are still needed. The unique index should be on relname, conname right? Also looking into DROP CONSTRAINT's code, it gives a notice about multiple constraint names dropped when RemoveRelConstraints(rel, conname) returns 1. This check isn't needed anymore right? Also RemoveRelConstraints can be simplified to assume only one row will need removing, and be turned into a void function? Not unless you want to break the quiet option for ATExecDropConstraint. 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: [HACKERS] rename constraint behavior for duplicate names?
Allan Wang [EMAIL PROTECTED] writes: (It would still be used for suppressing the not-exists error) Right, which is why RemoveRelConstraints has to tell if it removed anything. The API could be changed, but not to returns void. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] rename constraint behavior for duplicate names?
On Thu, 2005-09-01 at 17:55 -0400, Tom Lane wrote: Allan Wang [EMAIL PROTECTED] writes: Alright, I see why the checks are still needed. The unique index should be on relname, conname right? Also looking into DROP CONSTRAINT's code, it gives a notice about multiple constraint names dropped when RemoveRelConstraints(rel, conname) returns 1. This check isn't needed anymore right? Also RemoveRelConstraints can be simplified to assume only one row will need removing, and be turned into a void function? Not unless you want to break the quiet option for ATExecDropConstraint. Is the quiet option supposed to work anyway other than suppressing the not exists error? Since there can't be multiple constraint names the notice is never executed anyway. Otherwise I don't see how it would break. (It would still be used for suppressing the not-exists error) Allan Wang ---(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: [HACKERS] Version number in psql banner
On Thu, 2005-09-01 at 14:53 -0400, Tom Lane wrote: Darcy Buskermolen [EMAIL PROTECTED] writes: Yes I've seen the same frustration from users, I think the banner should say something along the lines of psql client version foo connected to server bar version sfoo That seems overly verbose, particularly in the normal case where the versions are indeed the same. I could live with seeing a display like that when the versions are different. Thats the way Oracle sql*plus has worked for years, so its fairly acceptable to a great many people without too many questions. The other question is what counts as different --- do we want to complain like this if the minor versions are different? Which is much harder to define and could itself have a bug in it, so I prefer the verbose message its much better to know for certain every time. Best Regards, Simon Riggs ---(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: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
[ redirected to -hackers, where it's actually on topic ] Matt Miller [EMAIL PROTECTED] writes: [redirected from -patches] On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote: This fundamentally breaks the entire backend. You do not have the option to continue processing after elog(ERROR); Okay, I think I'm beginning to see the naivete of that patch's simplistic attempt to decouple backend error handling from transaction management. But I still haven't found a way to meet my original need: On Wed, 2005-08-03 at 19:58 +, Matt Miller wrote: The benefit is that [PL/pgSQL] exception handling can be used as a program flow control technique, without invoking transaction management mechanisms. This also adds additional means to enhanced Oracle PL/SQL compatibility. Basically I'd like my Pl/pgSQL code to be able to utilize the try/catch paradigm of error handling without the overhead of subtransactions and without the effect of a rollback. If I catch the exception then everything should be fine as far as the transaction is concerned. The reason you aren't going to be able to manage this in the current state of plpgsql is that plpgsql doesn't really have any interesting computational ability of its own. It can't even do 2+2 without calling the main executor --- and recovering from elog(ERROR) without a transaction rollback is not part of the executor's contract. So while you could theoretically make a try/catch construct within plpgsql that doesn't have subtransaction semantics, there'd basically be no way to do anything useful within it. You might take a look at the other PLs such as plperl; those have behavior much closer to what you are looking for, since their computational engine is separate from the SQL engine. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
On Thu, 1 Sep 2005, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. That probably won't help him much with values(0): If I remember the context of the original request, it wasn't about that anyway, but about dealing with an existing smallint column. Nope, actually, the original was to just convert an existing table from using smallint-boolean, but what I'm looking at with the CREATE CAST is to avoid reducing the # of changes that I have to make to the existing application, so being able to auto-cast 0-'f' on an INSERT/UPDATE would help wtih that ... The app still needs to be fixed, but this would allow for the initial change to be made a bit easier ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
On Thu, 2005-09-01 at 18:28 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: Basically I'd like my Pl/pgSQL code to be able to utilize the try/catch paradigm of error handling without the overhead of subtransactions [Pl/pgSQL] can't even do 2+2 without calling the main executor --- and recovering from elog(ERROR) without a transaction rollback is not part of the executor's contract. Okay, so that's the crux regarding PL/pgSQL. You might take a look at the other PLs such as plperl That would defeat my goal of not rewriting all my Oracle code. If I were fool enough to plan an attack on the main executor's exception handling to try and disarm it of its subtransaction semantics, where would I start? Where would I end? What would I do in between? Can New Orleans be rebuilt above sea level? Seriously, though, I'm willing to devote considerable time to this. Rewriting all my Oracle code function-by-function could be painful, and I would end up dragging other people around this company into it. I'm still trying to hold on to my fantasy that I can hack Postgres (and contrib/ora2pg) into submission. In the end I'm hoping that the move from Oracle will be made easier for others. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Version number in psql banner
On Thu, 1 Sep 2005, Darcy Buskermolen wrote: Yes I've seen the same frustration from users, I think the banner should say something along the lines of psql client version foo connected to server bar version sfoo I second this, I think that something like psl client version 8.03 connected to server db01 running PostgreSQL version 8.1 would be real swell. Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL: EXCEPTION NOSAVEPOINT
Matt Miller [EMAIL PROTECTED] writes: If I were fool enough to plan an attack on the main executor's exception handling to try and disarm it of its subtransaction semantics, where would I start? Where would I end? What would I do in between? Can New Orleans be rebuilt above sea level? In general I don't think it even makes sense to think of making executor rollback non-transactional. If the executor was running, say, an INSERT or UPDATE that had already made some database changes before failing, you certainly don't want those partial results to be seen as good. ISTM what you are after is to provide some computational capability in plpgsql that is restricted from doing any database access, and therefore isn't subject to the above complaint. I'm not sure about a good way to do this. Seems like your choices are to re-invent the wheel by implementing a separate expression evaluator inside plpgsql, or to try to create a locked-down, limited-capability option in execQual.c. (The main problem with the latter route is to do it without adding any overhead for normal execution, as otherwise you'll probably get shot down on that basis.) In either case it's not immediately obvious how you tell what is safe to allow --- Postgres' model that everything is embedded within black-box functions doesn't help you here. The IMMUTABLE/STABLE/VOLATILE marking of functions is close to what you want, but not close enough. Also, it might be possible to make the restriction read-only database access instead of no database access; this would certainly fit a lot better with the existing function volatility categories, but then you still have the problem that aborting the executor is just not a low-overhead control path. And you would have to go through just about all of the existing subxact cleanup, such as releasing locks and buffer pins acquired within the failing query. 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: [HACKERS] Pre-allocated free space for row
Bruce Momjian pgman@candle.pha.pa.us writes: I assume that for a vacuum that only hit pages indicated in the bitmap, it would still be necessary to do an index scan to remove the heap pointers in the index, right? Given the current vacuum technology, yes. However, bearing in mind that indexes should generally be much smaller than their tables, cutting down the table traversal is certainly the first-order problem. (See also discussion with Simon from today.) 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: [HACKERS] Pre-allocated free space for row
Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: My wild guess is that deleting all index pointers for a removed index is more-or-less the same cost as creating new ones for inserted/updated page. Only if you are willing to make the removal process recalculate the index keys from looking at the deleted tuple. This opens up a ton of gotchas for user-defined index functions, particularly for doing it in the bgwriter which is not really capable of running transactions. Removing index entries also requires writing WAL log records, which is something we probably want to minimize in the bgwriter to avoid contention issues. It is often more agreeable to take a continuous up-to-2X performance hit than an unpredictable hit at unknown (or even at a known) time. Well, you can have that sort of tradeoff today, by running autovacuum continuously with the right delay parameters. The only vacuum optimization idea I've heard that makes any sense to me is the one about keeping a bitmap of changed pages so that vacuum need not read in pages that have not changed since last time. Everything else is just shuffling the same work around, and in most cases doing it less efficiently than we do now and in more performance-critical places. I assume that for a vacuum that only hit pages indicated in the bitmap, it would still be necessary to do an index scan to remove the heap pointers in the index, right? I have added the last sentence to the TODO entry: * Create a bitmap of pages that need vacuuming Instead of sequentially scanning the entire table, have the background writer or some other process record pages that have expired rows, then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the bitmap would probably be invalidated. One complexity is that index entries still have to be vacuumed, and doing this without an index scan (by using the heap values to find the index entry) might be slow and unreliable, especially for user-defined index functions. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Avoid using swap in a cluster
Hi. In a cluster, is there any way to use the main memory of the other nodes instead of the swap? If I have a query with many sub-queries and a lot of data, I can easily fill all the memory in a node. The point is: is there any way to continue using the main memory from other nodes in the same query instead of the swap? Thank you, Ricardo.
[HACKERS] Two different defs of MAX_TUPLES_PER_PAGE
Hi, I found two different definitions of MAX_TUPLES_PER_PAGE. Which is reasonable? Or do they have another meaning? backend/commands/vacuumlazy.c #define MAX_TUPLES_PER_PAGE ((int) (BLCKSZ / sizeof(HeapTupleHeaderData))) backend/nodes/tidbitmap.c #define MAX_TUPLES_PER_PAGE ((BLCKSZ - 1) / MAXALIGN(offsetof(HeapTupleHeaderData, t_bits) + sizeof(ItemIdData)) + 1) --- ITAGAKI Takahiro NTT Cyber Space Laboratories ---(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: Additional background daemon (was: [HACKERS] Remove xmin and cmin from frozen tuples)
Jim C. Nasby [EMAIL PROTECTED] writes: On Thu, Sep 01, 2005 at 09:22:35AM -0400, Tom Lane wrote: This is a bad idea. The bgwriter isn't the place to be doing freezing, So is this something that another daemon could handle? Possibly, but I'd be inclined to think of it as autovacuum's problem. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Two different defs of MAX_TUPLES_PER_PAGE
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found two different definitions of MAX_TUPLES_PER_PAGE. Which is reasonable? Or do they have another meaning? Hmm, I think those were both my fault at different times :-(. Right now I am thinking that they are both not quite right, in particular it ought to be #define MAX_TUPLES_PER_PAGE ((BLCKSZ - 1) / (MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) + sizeof(ItemIdData)) + 1) That is, the heaptuple space is padded to a MAXALIGN boundary, but the itemid that points to it isn't. Comments? (I believe that both modules want a ceiling definition not a floor definition, ie round up any fraction. The -1 / +1 trick is of course just one way to get that.) Also, is this something that should be in a common header file? If so which one? BLCKSZ, HeapTupleHeaderData, and ItemIdData are all defined in different places ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Version number in psql banner
On Thu, Sep 01, 2005 at 11:34:37PM -0500, Jim C. Nasby wrote: As a side note, there's a typo in \?: \c[onnect] [DBNAME|- [USER]] Note the | where there should be a ]. Eh? Looks right to me; the | indicates an alternate, i.e., that you can use either DBNAME or -. I often use - to connect to the same database as a different user. test= \c - postgres You are now connected as new user postgres. test=# -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] typo? was: Version number in psql banner
Am Donnerstag, den 01.09.2005, 23:34 -0500 schrieb Jim C. Nasby: On Thu, Sep 01, 2005 at 11:18:25PM +0100, Simon Riggs wrote: As a side note, there's a typo in \?: \c[onnect] [DBNAME|- [USER]] Note the | where there should be a ]. No ;) The | stands for the alternative. The whole block is optional, where you can either type dbname or a hypen for the database name - to be able to just switch the user. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Two different defs of MAX_TUPLES_PER_PAGE
Tom Lane [EMAIL PROTECTED] wrote: #define MAX_TUPLES_PER_PAGE ((BLCKSZ - 1) / (MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) + sizeof(ItemIdData)) + 1) (I believe that both modules want a ceiling definition not a floor definition, ie round up any fraction. The -1 / +1 trick is of course just one way to get that.) Don't you think about PageHeaderData? Also I guess a floor definition is ok because 'number of tuples' is an integer. How about the following? ((BLCKSZ - offsetof(PageHeaderData, pd_linp)) / (MAXALIGN(offsetof(HeapTupleHeaderData, t_bits)) + sizeof(ItemIdData))) Also, is this something that should be in a common header file? If so which one? BLCKSZ, HeapTupleHeaderData, and ItemIdData are all defined in different places ... Considering include-hierarchy, I think bufpage.h is a good place. --- ITAGAKI Takahiro NTT Cyber Space Laboratories ---(end of broadcast)--- TIP 6: explain analyze is your friend
