Re: [HACKERS] How to REINDEX in high volume environments?
On 28 Sep 2002 at 12:18, Tom Lane wrote: Justin Clift [EMAIL PROTECTED] writes: Shridhar Daithankar wrote: Looks like we should have a subdirectory in database directory which stores index. That was my first thought also, but an alternative/additional approach would be this (not sure if it's workable): See the tablespaces TODO item. I'm not excited about building half-baked versions of tablespaces before we get around to doing the real thing ... I wen thr. the messages posted regarding tablespaces. It looks like Tablesspaces should provide 1. Managability 2. Performance tuning 3. Better Administration.. Creating a directory for each object or object type would allow to do same thing. Why directory? 1. You can mount it someplace else. 2. You can symlink it without worrying about postgresql creating new files instead of symlink while drop/recreate. Whether to choose directory or tablespaces? I say directory. Why? 1. PostgreSQL philosphy has always been using facilities provided by OS and not to duplicate that work. Tablespaces directly violates that. Directory mounting does not. 2. Tablespaces combines objects on them, adding a layer of abstraction. and then come ideas like vacuuming a tablespace. Frankly given what vacuum does, I can't imagine what vacuuming tablespace would exactly do. 3. Tablespace would be a single file or structure of directories? How do we configure it? What tuning option do we provide? Basically table spaces I feel is a layer of abstraction that can be avoided if we layout the DB in a directory tree with sufficient levels. That would be easy to deal with as configuration and maitainance delegated to OS and it would be flexible enough to. Anyway if we have a directory per object/object type, how much different it's going to be from a table space? Frankly I am wary of table spaces because I have seen them in oracle and not eaxctly convinced that's the best way of doing things. If we introdude word tablespace, users will be expecting all those idiocies like taking a table space offline/online, adding data files aka pre-claiming space etc. All these are responsibilities of OS. Let OS handle it. PostgreSQL should just create a file structure which would grow as and when required. The issue looks similimar to having raw disk I/O. Oracle might have good reason to do it but are we sure postgresql needs this? Just another policy decision waiting.. Here are some links I found in archive. Would like to know more about this issue.. http://candle.pha.pa.us/mhonarc/todo.detail/tablespaces/msg6.html http://candle.pha.pa.us/mhonarc/todo.detail/tablespaces/msg7.html Just a thought.. Bye Shridhar -- The sooner our happiness together begins, the longer it will last. -- Miramanee, The Paradise Syndrome, stardate 4842.6 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How to REINDEX in high volume environments?
On 29 Sep 2002 at 0:43, Justin Clift wrote: Shridhar Daithankar wrote: The reason that I was thinking of having a different path per index would be for high volume situations like this: /dev/dsk1 : /pgdata - data here /dev/dsk2 : /pgindexes1 - some indexes here /dev/dsk3 : /pgindexes2 - some ultra-high volume activity here I would say this would look better.. /pgdata -indexes --index1 ---indexfiles --index2 ---indexfiles Where index1 and index2 are two different indexes. Just like each table gets it's own directory, each index gets it's own directory as well. So the admin would/can tune on per object basis rather than worrying about creating right group of objects and then tuning about that group. If required throwing per database transaction log there as well might prove a good idea. It would insulate one db from load of other, as far as I/O is concerned.. This possiblity is not lost with this scheme but it just gets something simpler IMO.. Just illustration of my another post on hackers on this topic.. Bye Shridhar -- You're too beautiful to ignore. Too much woman.-- Kirk to Yeoman Rand, The Enemy Within, stardate unknown ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] How to REINDEX in high volume environments?
Am Samstag, 28. September 2002 10:17 schrieb Shridhar Daithankar: (snip) I have to disagree.. Completely.. This is like turning PG-Metadata into registry... And what happens when index starts splitting when it grows beyond 1GB in size? Putting indexes into a separate subdirectoy and mount/link that directory on a device that is on a separate SCSI channel is what I can think of as last drop of performance out of it.. (snip) I think a good approach would be the introduction of tablespaces like oracle has, and assigning locations to that tablespace. Best regards, Mario Weilguni ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.2.3?
On Sun, 2002-09-29 at 07:19, Lamar Owen wrote: On Saturday 28 September 2002 09:23 pm, Bruce Momjian wrote: Justin Clift wrote: Alvaro Herrera wrote: I agree with Lamar that upgrading is a very difficult process right As a simple for the user approach, would it be too-difficult-to-bother-with to add to the postmaster an ability to start up with the data files from the previous version, for it to recognise an old data format automatically, then for it to do the conversion process of the old data format to the new one before going any further? Sounds like a pain to create initially, but nifty in the end. Yes, we could, but if we are going to do that, we may as well just automate the dump/reload. Automating the dump/reload is fraught with pitfalls. Been there; done that; got the t-shirt. The dump from the old version many times requires hand-editing for cases where the complexity is above a certain threshold. The 7.2-7.3 threshold is just a little lower than normal. Our whole approach to the system catalog is wrong for what Justin (and many others would like to see). With MySQL, for instance, one can migrate on a table-by-table basis from one table type to another. As older table types are continuously supported, one can upgrade each table in turn as you need the featureset supported by that tabletype. The initial Postgres design had a notion of StorageManager's, which should make this very easy indeed, if it had been kept working . IIRC the black box nature of storage manager interface was broken at latest when adding WAL (if it had really been there in the first place). -- Hannu ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.2.3?
On Sun, 2002-09-29 at 09:47, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: What would that converter need: [snip] I think that should be enough for converting table files. I'd like to experiment with something like this when I have some free time. Maybe next year... It's difficult to say anything convincing on this topic without a specific conversion requirement in mind. Localized conversions like 7.3's tuple header change could be done on a page-by-page basis as you suggest. (In fact, one reason I insisted on putting in a page header version number was to leave the door open for such a converter, if someone wants to do one.) But one likely future format change for user data is combining parent and child tables into a single physical table, per recent inheritance thread. (I'm not yet convinced that that's feasible or desirable, I'm just using it as an example of a possible conversion requirement.) You can't very well do that page-by-page; it'd require a completely different approach. I started to think about possible upgrade strategy for this scenario and came up with a whole new way for the whole storage : We could extend our current way of 1G split files for inheritance, so that each inherited table is in its own (set of) physical files which represent a (set of) 1G segment(s) for the logical file definition of all parent. This would even work for both single and multiple inheritance ! In this case the indexes (which enforce the uniquenaess and are required for RI) would see the thing as a single file and can use plain TIDs. The process of mapping from TID.PAGENR to actual file will happen below the level visible to executor. It would also naturally cluster similar tuples. Aa an extra bonus migration can be done only by changing system catalogs and recreating indexes. It will limit the size of inherited structure to at most 16K different tables (max unsigned int/pagesize), but I don't think this will be a real limit anytime soon. - Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Does setof record in plpgsql work well in 7.3?
Hi, all Does 7.3 support SETOF RECORD in plpgsql ? As far as I test it, a function using it in plpgsql always seems to return no row. On the other hand, a sql function returns correct rows. If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise an error rather than return 0 rows message. Am I misunderstanding how to use? -- CREATE TABLE test (a integer, b text); INSERT INTO test VALUES(1, 'function1'); INSERT INTO test VALUES(2, 'function2'); INSERT INTO test VALUES(1, 'function11'); INSERT INTO test VALUES(2, 'function22'); CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; END LOOP; RETURN rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); NOTICE: a = 1, b = function1 NOTICE: a = 1, b = function11 a | b ---+--- (0 rows) CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' SELECT * FROM test WHERE a = $1; ' LANGUAGE 'sql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | function1 1 | function11 (2 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does setof record in plpgsql work well in 7.3?
CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; RETURN NEXT rec; END LOOP; RETURN null; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); Note the use of the RETURN NEXT rec line in the body of the for loop, and also the RETURN null at the end. It is also possible to create typed returns, so in this case, in the declare body, the following would be valid. DECLARE rec test%ROWTYPE; The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF test ... One can also create your own return type in the following manner. create type my_return_type as ( foo integer, bar text ); Now, the declare block has the following:- DECLARE rec my_return_type%ROWTYPE The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF my_return_type ... Regards, Grant Finnemore Masaru Sugawara wrote: Hi, all Does 7.3 support SETOF RECORD in plpgsql ? As far as I test it, a function using it in plpgsql always seems to return no row. On the other hand, a sql function returns correct rows. If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise an error rather than return 0 rows message. Am I misunderstanding how to use? -- CREATE TABLE test (a integer, b text); INSERT INTO test VALUES(1, 'function1'); INSERT INTO test VALUES(2, 'function2'); INSERT INTO test VALUES(1, 'function11'); INSERT INTO test VALUES(2, 'function22'); CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; END LOOP; RETURN rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); NOTICE: a = 1, b = function1 NOTICE: a = 1, b = function11 a | b ---+--- (0 rows) CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' SELECT * FROM test WHERE a = $1; ' LANGUAGE 'sql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | function1 1 | function11 (2 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane kirjutas P, 29.09.2002 kell 04:00: Alvaro Herrera [EMAIL PROTECTED] writes: I have this almost ready. The thing I don't have quite clear yet is what to do with attislocal. IMHO it should not be touched in any case, but Hannu thinks that for symmetry it should be reset in some cases. I'd propose that ADD ONLY would pull topmost attislocal up (reset it from the (grand)child) whereas plain ADD would leave attislocal alone. The use of ONLY with this meaning is for the symmetry with DROP ONLY. My feeling would be to leave it alone in all cases. If I have create table p (f1 int); create table c (f2 text) inherits (p); I would find it quite surprising if I could destroy c.f2 by adding and then dropping p.f2. This should depend on weather you drop ONLY Or are you also be surprised by this behaviour of DELETE CASCADE :) hannu=# create table c(i int); CREATE TABLE hannu=# insert into c values(1); INSERT 41595 1 hannu=# insert into c values(2); INSERT 41596 1 hannu=# create table p (pk int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'p_pkey' for table 'p' CREATE TABLE hannu=# insert into p values(1); INSERT 41601 1 hannu=# insert into p values(2); INSERT 41602 1 hannu=# alter table c add constraint fk foreign key (i) hannu-# references p on delete cascade; NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ALTER TABLE hannu=# delete from p where pk=2; DELETE 1 hannu=# select * from c; i --- 1 (1 row) Surprise: Where did i=2 go ?? What you are proposing is IMHO equivalent to making FOREIGN KEYs ON DELETE CASCADE behaviour dependant on weather the foreign key was created initially or added afterwards. Also, what do you mean by conflicts on defaults? I don't think the parent should take into consideration what the defaults are for its children. Same for constraints. Well, the rules will probably have to be different for this case than they are when creating a child below an existing parent. In particular, if the ADD COLUMN operation is trying to create constraints (including a simple NOT NULL), I'm inclined to fail rather than merge if the existing child column does not already have matching constraints. It would seem surprising to me that creating a parent column in this way could allow the formerly free-standing child column to suddenly have constraints it didn't have before. Also, you'd have to scan the child rows to see whether they all meet the constraint, which would be slow. For example, if you wanted to do alter table p add column f2 text not null; in the above example, I think it is reasonable to insist that you first do alter table c alter column f2 set not null; To this I strongly agree. - Hannu ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] hacker help: PHP-4.2.3 patch to allow restriction of
Jim Mercer writes: the reasoning for this is that postmaster has no ability to differentiate between incoming sessions, and as such, storing the list in the server makes no sense, the server won't know how to apply the list. Right, but libpq also has no concept of what you call incoming session. PHP is the first interface that came up with that notion. If we have more clients requesting that kind of support, we can think about it, but for now you should think of putting it into PHP first. well, for my purposes, it is _databases_ i'm more concerned about. OK, so *you* put local sameuser ... into pg_hba.conf and be done. The rest of the user community can decide for themselves. This is especially important since with the arrival of schemas there is a whole new way to manage multiple users on a server, which other users might be interested in. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_config : postgresql.conf adjustments?
Justin Clift writes: Would it be beneficial for us to extend pg_config to update the postgresql.conf file? That has nothing to do with pg_config's functions. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_config : postgresql.conf adjustments?
Peter Eisentraut wrote: Justin Clift writes: Would it be beneficial for us to extend pg_config to update the postgresql.conf file? That has nothing to do with pg_config's functions. At present, sure. Was thinking a tool for command line changes of postgresql.conf parameters would be useful, then thought about what such a tool would be named. pg_cfg was a thought, as was pg_config. However we already have a pg_config. At present it's purpose is in the realm of reporting the installation configuration of PostgreSQL. Was thinking that adding the ability to do more than report stuff, but also to make changes isn't that bad an idea. ? Regards and best wishes, Justin Clift -- Peter Eisentraut [EMAIL PROTECTED] -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] making use of large TLB pages
Neil Conway wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is TLB Linux-only? Well, the TLB is a feature of the CPU, so no. Many modern processors support large TLB pages in some fashion. However, the specific API for using large TLB pages differs between operating systems. The API I'm planning to implement is the one provided by recent versions of Linux (2.5.38+). I've only looked briefly at enabling the usage of large pages on other operating systems. On Solaris, we already use large pages (due to using Intimate Shared Memory). On HPUX, you apparently need call chattr on the executable for it to use large pages. AFAIK the BSDs don't support large pages for user-land apps -- if I'm incorrect, let me know. Why use it and non SysV memory? It's faster, at least in theory. I posted these links at the start of the thread: http://lwn.net/Articles/6535/ http://lwn.net/Articles/10293/ Is it a lot of code? I haven't implemented it yet, so I'm not sure. However, I don't think it will be a lot of code. OK, personally, I would like to see an actual speedup of PostgreSQL queries before I would apply such a OS-specific, version-specific patch. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.2.3?
Hannu Krosing [EMAIL PROTECTED] writes: The initial Postgres design had a notion of StorageManager's, which should make this very easy indeed, if it had been kept working . But the storage manager interface was never built to hide issues like tuple representation --- storage managers just deal in raw pages. I doubt it would have helped in the least for anything we've been concerned about. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Hannu Krosing [EMAIL PROTECTED] writes: I'd propose that ADD ONLY would pull topmost attislocal up (reset it from the (grand)child) whereas plain ADD would leave attislocal alone. ADD ONLY? There is no such animal as ADD ONLY, and cannot be because it implies making a parent inconsistent with its children. (Yes, I know that the code takes that combination right now, but erroring out instead is on the must fix before release list. Ditto for RENAME ONLY.) The use of ONLY with this meaning is for the symmetry with DROP ONLY. But it's not a symmetrical situation. The children must contain every column in the parent; the reverse is not true. Some asymmetry in the commands is therefore unavoidable. I would find it quite surprising if I could destroy c.f2 by adding and then dropping p.f2. This should depend on weather you drop ONLY I disagree. Your analogy to a CASCADE foreign key is bad, because the foreign key constraint is attached to the column that might lose data. Thus you (presumably) know when you create the constraint what you are risking. Losing existing child data because of manipulations done only on the parent --- perhaps not even remembering that there is a conflicting child column --- strikes me as dangerous. It seems like an indirect, action at a distance behavior. Here is another scenario: suppose p has many children, but only c42 has a column f2. If I alter table p add column f2, now p and all the c's will have f2. Suppose I realize that was a mistake. Can I undo it with alter table p drop column f2? Yes, under my proposal; no, under yours. In yours, the only way would be to do a DROP ONLY on p and then retail DROPs on each of the other children. This would be tedious and error-prone. If some random subset of the children had f2, it'd be even worse --- it would be difficult even to identify which children had f2 before the ADD operation. IMHO this is a good example of why attislocal is useful. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_config : postgresql.conf adjustments?
Justin Clift [EMAIL PROTECTED] writes: Would it be beneficial for us to extend pg_config to update the postgresql.conf file? This seems far outside pg_config's charter. It is a simple information reporter that can be run by anybody. Making it able to mess with (or even look at) postgresql.conf introduces a host of permissions problems and logistical issues. I don't really see what's wrong with using a text editor anyway ;-) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] making use of large TLB pages
Bruce Momjian [EMAIL PROTECTED] writes: OK, personally, I would like to see an actual speedup of PostgreSQL queries before I would apply such a OS-specific, version-specific patch. Don't be silly. A performance improvement is a performance improvement. According to your logic, using assembly-optimized locking primitives shouldn't be done unless we've exhausted every possible optimization in every other part of the system (a process which will likely never be finished). If the optimization was for some obscure UNIX variant and/or an obscure processor, I would agree that it wouldn't be worth the bother. But given that (a) Linux on IA32 is likely our most popular platform [1] (b) In theory, this will help performance where we need it most, IMHO (high-end systems using large shared buffers) I think it's at least worth implementing -- if it doesn't provide a noticeable performance improvement, then we don't need to merge it. Cheers, Neil [1] It's worth noting that the huge tlb patch currently works in IA64, SPARC, and may well be ported to additional architectures in the future. -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Do we want a CVS branch now?
Bruce Momjian [EMAIL PROTECTED] writes: Marc, I know we said branch after beta2 but I think we need another week or two before we can start using that branch effectively. Even if we started using it, like adding PITR, the code would drift so much that the double-patching would start to fail when applied. Another problem is that with all the open issues, we still really need to focus on 7.3, not on 7.4 development. I don't want to see massive patches like PITR or the Windows-port stuff coming in just yet, because we don't have the bandwidth to review them now. Can the branch be undone, or can we not use it and just apply a mega-patch later to make it match HEAD? AFAIK there's no convenient way to undo the branch creation. I concur with treating HEAD as the active 7.3 area for the next week or so and then doing a bulk merge into the REL7_3 branch, so as to avoid the labor of individual double-patches. Marc previously proposed releasing beta3 in about a week --- will that be a good time to open HEAD for 7.4 work, or will we need to delay still longer? (I'm not sure yet, myself.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] making use of large TLB pages
Neil Conway [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: OK, personally, I would like to see an actual speedup of PostgreSQL queries before I would apply such a OS-specific, version-specific patch. Don't be silly. A performance improvement is a performance improvement. No, Bruce was saying that he wanted to see demonstrable improvement *due to this specific change* before committing to support a platform-specific API. I agree with him, actually. If you do the TLB code and can't measure any meaningful performance improvement when using it vs. when not, I'd not be excited about cluttering the distribution with it. I think it's at least worth implementing -- if it doesn't provide a noticeable performance improvement, then we don't need to merge it. You're on the same page, you just don't realize it... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Does setof record in plpgsql work well in 7.3?
On Sun, 29 Sep 2002 13:42:43 +0200 Grant Finnemore [EMAIL PROTECTED] wrote: Note the use of the RETURN NEXT rec line in the body of the for loop, and also the RETURN null at the end. It is also possible to create typed returns, so in this case, in the declare body, the following would be valid. DECLARE rec test%ROWTYPE; The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF test ... Thank you for your useful info. the previous function turned out to work correctly by using RETURN NEXT rec. And, I found out that plpgsql was able to nest one. -- for example CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec1 record; rec2 record; rec3 record; BEGIN SELECT INTO rec1 max(a) AS max_a FROM test; FOR rec2 IN SELECT * FROM test WHERE a = $1 LOOP SELECT INTO rec3 * FROM (SELECT 1::integer AS a, ''test''::text AS b) AS t; RETURN NEXT rec3; rec2.a = rec2.a + rec3.a + rec1.max_a; RETURN NEXT rec2; END LOOP; RETURN NEXT rec3; RETURN; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | test 5 | function1 1 | test 5 | function11 1 | test (5 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Do we want a CVS branch now?
Tom Lane wrote: snip Marc previously proposed releasing beta3 in about a week --- will that be a good time to open HEAD for 7.4 work, or will we need to delay still longer? (I'm not sure yet, myself.) Perhaps it's too early to be able to effectively say when a real+effective branch is likely to be really needed? Stuff still feels a bit too chaotic. Regards and best wishes, Justin Clift regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_config : postgresql.conf adjustments?
On Sun, 29 Sep 2002, Tom Lane wrote: Justin Clift [EMAIL PROTECTED] writes: Would it be beneficial for us to extend pg_config to update the postgresql.conf file? This seems far outside pg_config's charter. It is a simple information reporter that can be run by anybody. Making it able to mess with (or even look at) postgresql.conf introduces a host of permissions problems and logistical issues. I don't really see what's wrong with using a text editor anyway ;-) Obviously he wants a tool that allows setting parameters from a shell script or something for use within pg_autotune. I don't see why it is bad to have a tool to do this; if someone can use it (and modify postgresql.conf) obviously he has permission to read (and write) postgresql.conf. -- Alvaro Herrera (alvherre[@]dcc.uchile.cl) Hoy es el primer dia del resto de mi vida ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
On Sun, 29 Sep 2002, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: I'd propose that ADD ONLY would pull topmost attislocal up (reset it from the (grand)child) whereas plain ADD would leave attislocal alone. ADD ONLY? There is no such animal as ADD ONLY, and cannot be because it implies making a parent inconsistent with its children. (Yes, I know that the code takes that combination right now, but erroring out instead is on the must fix before release list. Ditto for RENAME ONLY.) I'm leaving right now and can't participate in the whole discussion, but I implemented ADD ONLY as a way to add the column only in the parent (all children should already have to column, errors if at least one doesn't or is different atttype), while ADD adds the column to children that don't have it and merges where already exist; it errors if children have different atttype etc. Should I rip the ADD ONLY part out? -- Alvaro Herrera (alvherre[@]dcc.uchile.cl) Pido que me den el Nobel por razones humanitarias (Nicanor Parra) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [ODBC] [s.hetze@linux-ag.de: PostgreSQL integration Visual Basic, SQLProcedureColumns]
On Fri, Sep 27, 2002 at 09:53:02AM -0700, Joe Conway wrote: It is in 7.3. If the return tuple definition is fixed: instead of: exec sp_myproc() go do select * from sp_myproc(); That's a great feature to have. If the return tuple definition is *not* fixed: do select * from sp_myproc() as table_alias([col definition list]); Does this help any? Can he try the 7.3 beta? Unfortunately no. They are not willing to use a beta so they are appearantly switching to SAP DB. Sorry. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_config : postgresql.conf adjustments?
Alvaro Herrera wrote: Obviously he wants a tool that allows setting parameters from a shell script or something for use within pg_autotune. I don't see why it is bad to have a tool to do this; if someone can use it (and modify postgresql.conf) obviously he has permission to read (and write) postgresql.conf. But, if that's the case, why not just: 1. send e.g. set sort_mem=8192 as an SQL statement for runtime changeable parameters 2. use e.g. pg_ctl restart -D $PGDATA -o '--shared_buffers=1' for those parameters requiring a restart Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_config : postgresql.conf adjustments?
Joe Conway wrote: Alvaro Herrera wrote: Obviously he wants a tool that allows setting parameters from a shell script or something for use within pg_autotune. I don't see why it is bad to have a tool to do this; if someone can use it (and modify postgresql.conf) obviously he has permission to read (and write) postgresql.conf. But, if that's the case, why not just: 1. send e.g. set sort_mem=8192 as an SQL statement for runtime changeable parameters 2. use e.g. pg_ctl restart -D $PGDATA -o '--shared_buffers=1' for those parameters requiring a restart Doesn't allow for scriptable permanent changes, only runtime ones. Was just trying to think of a optimal end user solution. Totally hadn't thought of the 'set sort_mem=xxx' option either, but it might work for the next version of pg_autotune (am going to have to re-write it anyway). :) Regards and best wishes, Justin Clift Joe -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
On Sun, 2002-09-29 at 19:57, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: I'd propose that ADD ONLY would pull topmost attislocal up (reset it from the (grand)child) whereas plain ADD would leave attislocal alone. ADD ONLY? There is no such animal as ADD ONLY, and cannot be because it implies making a parent inconsistent with its children. I meant ADD ONLY to be the exact opposite of DROP ONLY - it adds parent column and removes attislocal from children. Simple ADD would _not_ remove attislocal from children with matching column. The use of ONLY with this meaning is for the symmetry with DROP ONLY. But it's not a symmetrical situation. The children must contain every column in the parent; the reverse is not true. Some asymmetry in the commands is therefore unavoidable. Perhaps some mirror command then: DROP ONLY -- ADD ALL ? I would find it quite surprising if I could destroy c.f2 by adding and then dropping p.f2. This should depend on weather you drop ONLY I disagree. Your analogy to a CASCADE foreign key is bad, because the foreign key constraint is attached to the column that might lose data. Thus you (presumably) know when you create the constraint what you are risking. Losing existing child data because of manipulations done only on the parent --- perhaps not even remembering that there is a conflicting child column --- strikes me as dangerous. It seems like an indirect, action at a distance behavior. What about warning the user and making him use FORCE in ambiguous cases (like when some children don't have that column) ? Here is another scenario: suppose p has many children, but only c42 has a column f2. If I alter table p add column f2, now p and all the c's will have f2. Suppose I realize that was a mistake. Can I undo it with alter table p drop column f2? Yes, under my proposal; no, under yours. YES under mine, unless you did alter table ONLY p add column f2 , which would have removed the local definition from children. In yours, the only way would be to do a DROP ONLY on p and then retail DROPs on each of the other children. This would be tedious and error-prone. If some random subset of the children had f2, it'd be even worse --- it would be difficult even to identify which children had f2 before the ADD operation. Your proposal and mine are the same in case ONLY is not given. The option ADD ONLY is proposed just to make it easy to undo a DROP ONLY. Under your proposal I see no easy way to undo DROP ONLY (for example to do DROP instead). IMHO this is a good example of why attislocal is useful. I don't doubt usefulness of attislocal, I just want to make sure it is used in a consistent manner. - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Web site
-Original Message- From: CoL [mailto:[EMAIL PROTECTED]] Sent: 24 September 2002 13:23 To: [EMAIL PROTECTED] Subject: Re: [HACKERS] Web site Hi, So, why not just redirect people to one of the mirrors listed? This could be done based on IP (yes it is inaccurate but it is close enough and has the same net effect: pushing people off the main web server) or it could be done by simply redirecting to a random mirror. I think it would be stupid, I am, who wants to decide where to go. If I feel that .co.uk is better than others I'll chose that, and bookmark if I want. (random??? brbrbrbrbr) :) I think it's safe to say we will *not* be doing this... Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.2.3?
On Sun, 2002-09-29 at 19:28, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: The initial Postgres design had a notion of StorageManager's, which should make this very easy indeed, if it had been kept working . But the storage manager interface was never built to hide issues like tuple representation --- storage managers just deal in raw pages. I had an impression that SM was meant to be a little higher-level. IIRC the original Berkeley Postgres had at one point a storage manager for write-once storage on CDWr jukeboxes. the README in src/backend/storage/smgr still contains mentions about Sony jukebox drivers. http://www.ndim.edrc.cmu.edu/postgres95/www/pglite1.html also claims this: Version 3 appeared in 1991 and added support for multiple storage managers, an improved query executor and a rewritten rewrite rule system. For the most part, releases since then have focused on portability and reliability. I doubt it would have helped in the least for anything we've been concerned about. Yes, it seems that we do not have a SM in the semse I hoped. Still, if we could use a clean SM interface over old page format, then the tuple conversion could be done there. That of course would need the storage manager to be aware of old/new tuple structures ;( - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
On 29 Sep 2002, Hannu Krosing wrote: On Sun, 2002-09-29 at 19:57, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: I'd propose that ADD ONLY would pull topmost attislocal up (reset it from the (grand)child) whereas plain ADD would leave attislocal alone. ADD ONLY? There is no such animal as ADD ONLY, and cannot be because it implies making a parent inconsistent with its children. I meant ADD ONLY to be the exact opposite of DROP ONLY - it adds parent column and removes attislocal from children. Simple ADD would _not_ remove attislocal from children with matching column. Consistency requires that it be exactly the opposite. When you ADD ONLY, you want only in the local table, so children still have a local definition; OTOH, when you ADD (recursively) you want all children to get non-local status. Suppose CREATE TABLE p (f1 int); CREATE TABLE c (f2 int) INHERITS (p); c.f2.attislocal = true Now, ALTER TABLE ONLY p ADD COLUMN f2 int should leavy c.f2.attislocal alone, while ALTER TABLE p ADD COLUMN f2 int should reset it. This is the opposite of your proposal, and I don't think it exists in Tom's proposal. I think this is also consistent with the fact that ONLY requires the column to exist in all children, while non-ONLY creates it where it doesn't exist, and merges (resetting attislocal if set -- it could be inherited from some other parent) where it exists. -- Alvaro Herrera (alvherre[@]dcc.uchile.cl) Nunca se desea ardientemente lo que solo se desea por razon (F. Alexandre) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] psqlODBC *nix Makefile (new 7.3 open item?)
Hi, Now that the ODBC driver has moved from the main distro to http://gborg.postgresql.org/project/psqlodbc/, we can no longer use the main build system under *nix. Can someone who knows make better than I (which is probably the vast majority of you!) knock up a makefile so the driver will build standalone on *nix systems please? There should be no dependencies on any of the rest of the code - certainly there isn't for the Win32 build. There are changes to support 7.3 so this is fairly urgent... (maybe it should be added to the open items list Bruce?). Thanks, Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Do we want a CVS branch now?
Peter Eisentraut wrote: Bruce Momjian writes: I don't think we want a branch for 7.4 yet. We still have lots of open issues and the branch will require double-patching. Merge the changes on the 7.3 branch into the 7.4 branch after 7.3 is released. Yes, there is something to be said for this idea. We can single-patch into 7.3 and make one mega-patch to bring 7.4 up to 7.3. I think that will work _if_ 7.4 doesn't drift too much, and even then, I just need to spend some time manually doing it. However, there is the danger that 7.4 changes will not hit all the areas coming in from the 7.3 patch. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql/contrib/rserv ApplySnapshot.in CleanLog. ...
Peter, the author is questioning why his Makefile changes were wrong. Would you elaborate? --- pgman wrote: Done. --- Peter Eisentraut wrote: Please revert the Makefile part of this patch. Bruce Momjian - CVS writes: CVSROOT: /cvsroot Module name: pgsql Changes by: [EMAIL PROTECTED] 02/03/06 15:41:38 Modified files: contrib/rserv : ApplySnapshot.in CleanLog.in GetSyncID.in Makefile MasterSync.in PrepareSnapshot.in Replicate.in Log message: This simple patch fixes broken Makefile, broken ApplySnapshot and makes all utilities honour --verbose command line option. -- Yours, Alexey V. Borzov, Webmaster of RDW.ru -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Intel Itanium, TLB
I read a good article about the problem Intel is having with the 64-bit Itanium. I think there are some good leasons in the article: http://www.nytimes.com/2002/09/29/technology/circuits/29CHIP.html There is also a Slashdot discussion about the article: http://slashdot.org/article.pl?sid=02/09/29/1752204mode=nestedtid=118 Also, here is an article describing the x86 4MB page sizes used by the Linux TLB code: http://www.rcollins.org/ddj/May96/May96.html x86 usually uses two levels of directory/page tables, while the 4MB version uses only the page directory. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CVS split problems
can you create a project on gborg under 'server modules' for this? On Sun, 29 Sep 2002, Bruce Momjian wrote: Marc, I am still seeing these errors. Would you please fix it? --- Bruce Momjian wrote: I am getting errors when doing a checkout, related to Marc's splitting up the CVS tree into modules: C pgsql/contrib/earthdistance/Makefile cvs checkout: move away pgsql/contrib/earthdistance/README.earthdistance; it is in the way C pgsql/contrib/earthdistance/README.earthdistance cvs checkout: move away pgsql/contrib/earthdistance/earthdistance.c; it is in the way I get this from a CVS checkout every time. Can someone fix it? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] making use of large TLB pages
Neil, I agree with Bruce and Tom. AFAIK and in my experience I don't think it will be a significantly measurable increase. Not only that, but the portability issue itself tends to make it less desireable. I recently ported SAP DB and the coinciding DevTools over to OpenBSD and learned again first-hand what a pain in the ass having platform-specific code is. I guess it's up to you, Neil. If you want to spend the time trying to implement it, and it does prove to have a significant performance increase I'd say maybe. IMHO, I just think that time could be better spent improving the current system rather than trying to add to it in a singular way. Sorry if my comments are out-of-line on this one but it has been a thread for some time I'm just kinda tired of reading theory vs proof. Since you are so set on trying to implement this, I'm just wondering what documentation has tested evidence of measurable increases in similar situations? I just like arguments to be backed by proof... and I'm sure there is documentation on this somewhere. -Jonah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian Sent: Sunday, September 29, 2002 3:30 PM To: Tom Lane Cc: Neil Conway; PostgreSQL Hackers Subject: Re: [HACKERS] making use of large TLB pages Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: OK, personally, I would like to see an actual speedup of PostgreSQL queries before I would apply such a OS-specific, version-specific patch. Don't be silly. A performance improvement is a performance improvement. No, Bruce was saying that he wanted to see demonstrable improvement *due to this specific change* before committing to support a platform-specific API. I agree with him, actually. If you do the TLB code and can't measure any meaningful performance improvement when using it vs. when not, I'd not be excited about cluttering the distribution with it. I think it's at least worth implementing -- if it doesn't provide a noticeable performance improvement, then we don't need to merge it. You're on the same page, you just don't realize it... I see what he thought I said, I just can't figure out how he read it that way. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] making use of large TLB pages
Jonah H. Harris [EMAIL PROTECTED] writes: I agree with Bruce and Tom. AFAIK Bruce and Tom (and myself) agree that this is a good idea, provided it makes a noticeable performance difference (and if it doesn't, it's not worth applying). AFAIK and in my experience I don't think it will be a significantly measurable increase. Can you elaborate on this experience? Not only that, but the portability issue itself tends to make it less desireable. Well, that's obvious: code that improves PostgreSQL on *all* platforms is clearly superior to code that only improves it on a couple. That's not to say that the latter code is absolutely without merit, however. Sorry if my comments are out-of-line on this one but it has been a thread for some time I'm just kinda tired of reading theory vs proof. Well, ISTM the easiest way to get some proof is to implement it and benchmark the results. IMHO any claims about performance prior to that are mostly hand waving. Since you are so set on trying to implement this, I'm just wondering what documentation has tested evidence of measurable increases in similar situations? (/me wonders if people bother reading the threads they reply to) http://lwn.net/Articles/10293/ According to the HP guys, Oracle saw an 8% performance improvement in TPC-C when they started using large pages. To be perfectly honest, I really have no idea if that will translate into an 8% performance gain for PostgreSQL, or whether the performance gain only applies if you're using a machine with 16GB of RAM, or whether the speedup from large pages is really just a correction of some Oracle deficiency that we don't suffer from, etc. However, I do think it's worth finding out. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Alvaro Herrera [EMAIL PROTECTED] writes: I implemented ADD ONLY as a way to add the column only in the parent (all children should already have to column, errors if at least one doesn't or is different atttype), while ADD adds the column to children that don't have it and merges where already exist; it errors if children have different atttype etc. I fail to see the value in such a distinction. The end state is the same in both cases, no? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CVS split problems
Marc G. Fournier wrote: can you create a project on gborg under 'server modules' for this? Uh, I don't see the logic in moving earthdistance out of /contrib. It uses /cube, which is in contrib. I didn't think we were moving loadable modules out to gborg yet, and I didn't think we were doing that during beta. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Do we want a CVS branch now?
Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian writes: I don't think we want a branch for 7.4 yet. We still have lots of open issues and the branch will require double-patching. Merge the changes on the 7.3 branch into the 7.4 branch after 7.3 is released. Why is that better than the other direction? We can't afford to allow much divergence between the two branches so long as we are engaged in wholesale double-patching, so I think it really comes down to the same thing in the end: we are not ready for 7.4 development to start in earnest, whether there's a CVS branch for it or not. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
