Re: [HACKERS] Database file compatability
Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] There are no platforms for which ALIGNOF_SHORT is different from 2. I don't think there are any platforms we care about where ALIGNOF_INT is different from 4. The cases of interest are ALIGNOF_DOUBLE, ALIGNOF_LONG, ALIGNOF_LONG_LONG_INT (note that MAXIMUM_ALIGNOF is just the largest of these). In practice long int is the same type as either int or long long int, so ALIGNOF_LONG isn't a distinct case either. What it comes down to is that MAXIMUM_ALIGNOF is sufficient to tell the difference between the platforms we need to deal with. If you have a counterexample, tell us about it. (1) Yes, ALIGNOF_SHORT is always 2. (2) There is a possible sequence like this: ALIGNOF_LONG4 ALIGNOF_DOUBLE 8 MAXIMUM_ALIGNOF 8 vs. ALIGNOF_LONG8 ALIGNOF_DOUBLE 8 MAXIMUM_ALIGNOF 8 Eg. http://developers.sun.com/prodtech/cc/articles/about_amd64_abi.html http://devrsrc1.external.hp.com/STK/wellbehavedrestrict.html So we should at least check ALIGNOF_LONG as well. (3) There are some machines with sizeof(int) equals to 64, if my memory saves, which might imply that ALIGNOF_INT equals to 8. So conservatively, we'd better check ALIGNOF_INT, ALIGNOF_LONG and MAXIMUM_ALIGNOF. Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open items list for 8.1
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: 28 September 2005 00:50 To: Tom Lane Cc: Bruce Momjian; PostgreSQL-development; Neil Conway Subject: Re: [HACKERS] Open items list for 8.1 IMHO, changes like this *should not* have been allowed during beta, period ... even during feature freeze, it would have been questionable :( Agreed. It's not like they weren't discussed to death prior to then as well. Whilst I'm not so wed to the changes to the others, pg_cancel_backend() should certainly not be changed on whim - I know for a fact there are people for whom this will cause problems. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] effective SELECT from child tables
Maybe new constraint_exclusion staff could help to exclude non-matching tables from inheritance query ? Yes, that's exactly what it's for. Your testing is welcome. Download 8.1 and try it today. Great, I'm developing on 8.1b2 now... But could you be more particular about the solution ? Only the way I can think of is to add relname field into parent table, add BEFORE INSERT trigger to each child that will set it appropriately and CHECK (relname=here goes table name). It works in this case.. More than that.. I can create indexes on each table with WHERE relname!=table name, then they are used with DELETE/UPDATE WHERE relname IN(..). But the whole idea to adding an extra field, trigger and duplicating table name multiple times.. Feels a bit stinky to me.. Could you suggest another path ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] effective SELECT from child tables
Let table A be inherited by A1, A2, A3. How to select from A records where actual relations are A1, A2 ? Why not just select directly from the child tables? I can't get excited about optimizing the case you propose. Because WHERE concrete_class IN (a,b,c) is much more convenient and flexible way of forming select then manually split request into many unions. Also, this query runs on top of abstract class, so inheritance really assists me here. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Making pgxs builds work with a relocated installation
-Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED] Sent: 28 September 2005 06:37 To: Tom Lane Cc: Magnus Hagander; Dave Page; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Making pgxs builds work with a relocated installation Tom Lane wrote: Yuck. Anyone have another idea on coping with space-containing pathnames? Switch to scons. You heard it here first! Oooh, that looks nice at first glance... /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] execution of prepared statements leaks memory
Hi, I'm not sure whether I've stumbled over a bug or a feature, so I'm curious if someone might explain that. The statement below declares a stored procedure: CREATE LANGUAGE PLPGSQL; DROP TABLE memleak_test; CREATE TABLE memleak_test (id serial); CREATE OR REPLACE FUNCTION memleak(INTEGER) RETURNS INTEGER AS $$ DECLARE c ALIAS FOR $1; i INTEGER; BEGIN EXECUTE 'PREPARE leak AS INSERT into memleak_test values (1);'; FOR i IN 1..c LOOP EXECUTE 'EXECUTE leak;'; END LOOP; EXECUTE 'DEALLOCATE leak;'; RETURN 0; END; $$ LANGUAGE PLPGSQL; and that procedure, if being called repeatedly, say, as 'SELECT memleak(3)' causes postmaster to eat memory rather noticeably, one my machine, for example, first call to memleak(3) eats 100M, second 50M, and the subsequent calls leak 1-3M per call. The same leak occurs when I've tried the same code writte on plperl, so it doesn't seem that the effect is related to plpgsql. -- Sincerely, Dmitry Karasik ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Making pgxs builds work with a relocated installation
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 27 September 2005 18:57 To: Magnus Hagander Cc: Dave Page; Peter Eisentraut; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Making pgxs builds work with a relocated installation Magnus Hagander [EMAIL PROTECTED] writes: Using GetShortPathName() will break on any system that has disabled short filename generatino, which IIRC is a recommended best practice both for performance and for security in legacy apps. I don't know what it does, but probably it will just return the same long path again. Yuck. Anyone have another idea on coping with space-containing pathnames? I suppose we could try to quote the path variables properly in all the makefiles, but that sure seems like a painful proposition. Actually it seems to work quite nicely - on Windows 2000 with short names disabled it generates paths like: C:\PROGRA~1\PostgreSQL\8.1-beta2\bin Ie, it still fixes the spaces, but leaves the long bits, umm, long. With short names enabled (on XP), you get: C:\PROGRA~1\POSTGR~1\827E4~1.1-B\bin Which is truly hideous, but works as expected in cmd.exe and Msys. Patch attached that does this, and doubles up on the backslashes to keep msys/make happy. Cmd.exe doesn't seem to care about the double backslashes. Regards, Dave pg_config c.diff Description: pg_config c.diff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] A Better External Sort?
I can't help wondering how a couple thousand context switches per second would affect the attempt to load disk info into the L1 and L2 caches. That's pretty much the low end of what I see when the server is under any significant load. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Patching dblink.c to avoid warning about open transaction
Joe Conway wrote: Bruce Momjian wrote: [ Joe, would you review this? ] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. The patch itself is pretty simple, but I'm unclear on the use case. Jonathan, can you elaborate a bit? Thanks, Joe p.s. I'm on a business trip in Asia again, so my responses may be delayed a bit. Hi Joe, We are using the dblink module on Sensor servers to provide summarization services to a Central server. Sensors are in the business of populating certain Postgres databases, and the Central is in the business of populating a summary Postgres database. The issue in our situation is that the Central server does an explicit BEGIN transaction some time before it calls the dblink_open() routine. On the Sensors, we were getting many there is already a transaction in progress warnings, and overflowing our log storage. Is this patch the right way to go about this? Thanks, Jonathan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL overall design
On 9/27/05, Andrew Dunstan [EMAIL PROTECTED] wrote: [ -performance removed ]Gnanavel S wrote:Need a clarification in copydir.c file of src/port directory,In the following snippet the destination directory is created first then the source directory is read. Suppose if I don't have permission to read the source, even then the destination directory would be created. I just want to know whether there is any reason for doing so? Under what circumstances do you imagine this will happen, since thepostmaster user owns all the files and directories? Understood. But can you explain why it is done in that way as what I said seems to be standard way of doing it (correct me if I'm wrong).-- with regards,S.Gnanavel
Re: [HACKERS] PostgreSQL overall design
On 9/27/05, Jonah H. Harris [EMAIL PROTECTED] wrote: Were you looking for a call graph? Yes. I want to know the list and sequence of files involved during a call. On 9/27/05, Abhijit Menon-Sen [EMAIL PROTECTED] wrote: At 2005-09-27 15:20:05 +0530, [EMAIL PROTECTED] wrote: Can anyone please tell/point me where I can get the postgresql system layout (I've an interest to contribute). http://www.postgresql.org/developer/codingAnd, in particular: http://www.postgresql.org/docs/faqs.FAQ_DEV.html -- ams---(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-- Respectfully,Jonah H. Harris, Database Internals Architect EnterpriseDB Corporationhttp://www.enterprisedb.com/ -- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [HACKERS] [PERFORM] A Better External Sort?
From: Josh Berkus josh@agliodbs.com ent: Sep 27, 2005 12:15 PM To: Ron Peacetree [EMAIL PROTECTED] Subject: Re: [HACKERS] [PERFORM] A Better External Sort? I've somehow missed part of this thread, which is a shame since this is an area of primary concern for me. Your suggested algorithm seems to be designed to relieve I/O load by making more use of the CPU. (if I followed it correctly). The goal is to minimize all IO load. Not just HD IO load, but also RAM IO load. Particularly random access IO load of any type (for instance: the pointer chasing problem). In addition, the design replaces explicit data or explicit key manipulation with the creation of a smaller, far more CPU and IO efficient data structure (essentially a CPU cache friendly Btree index) of the sorted order of the data. That Btree can be used to generate a physical reordering of the data in one pass, but that's the weakest use for it. The more powerful uses involve allowing the Btree to persist and using it for more efficient re-searches or combining it with other such Btrees (either as a step in task distribution across multiple CPUs or as a more efficient way to do things like joins by manipulating these Btrees rather than the actual records.) However, that's not PostgreSQL's problem; currently for us external sort is a *CPU-bound* operation, half of which is value comparisons. (oprofiles available if anyone cares) So we need to look, instead, at algorithms which make better use of work_mem to lower CPU activity, possibly even at the expense of I/O. I suspect that even the highly efficient sorting code we have is suffering more pessimal CPU IO behavior than what I'm presenting. Jim Gray's external sorting contest web site points out that memory IO has become a serious problem for most of the contest entries. Also, I'll bet the current code manipulates more data. Finally, there's the possibilty of reusing the product of this work to a degree and in ways that we can't with our current sorting code. Now all we need is resources and time to create a prototype. Since I'm not likely to have either any time soon, I'm hoping that I'll be able to explain this well enough that others can test it. *sigh* I _never_ have enough time or resources any more... Ron ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] PostgreSQL overall design
On 9/27/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Have you read the developers FAQ? Thanks Bruce. I'm going through that. --- Gnanavel S wrote: Hi All, Can anyone please tell/point me where I can get the postgresql system layout (I've an interest to contribute). I would also like to know the files involved for performing each task ( for eg when doing a select operation what is exactly happening in postgres along with the files). I was wandering inside the source for a while and I couldn't get a start point to go with. Need a clarification in copydir.c file of src/port directory, In the following snippet the destination directory is created first then the source directory is read. Suppose if I don't have permission to read the source, even then the destination directory would be created. I just want to know whether there is any reason for doing so? if (mkdir(todir, S_IRUSR | S_IWUSR | S_IXUSR) != 0) ereport(ERROR, (errcode_for_file_access(), errmsg(could not create directory \%s\: %m, todir))); xldir = AllocateDir(fromdir); if (xldir == NULL) ereport(ERROR, (errcode_for_file_access(), errmsg(could not open directory \%s\: %m, fromdir))); -- with thanks regards, S.Gnanavel Satyam Computer Services Ltd.--Bruce Momjian|http://candle.pha.pa.uspgman@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-- with regards,S.Gnanavel
Re: [HACKERS] Making pgxs builds work with a relocated installation
On Wed, Sep 28, 2005 at 10:05:48AM +0100, Dave Page wrote: Tom Lane wrote: Yuck. Anyone have another idea on coping with space-containing pathnames? Switch to scons. You heard it here first! Oooh, that looks nice at first glance... The only question is, do we want to force a Python dependency for building Postgres? I doubt it will be less portable than what we have now, but it will be extra burden for the users. Scons certainly seems nice anyway ... -- Alvaro Herrerahttp://www.advogato.org/person/alvherre El destino baraja y nosotros jugamos (A. Schopenhauer) ---(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] effective SELECT from child tables
On Tue, Sep 27, 2005 at 09:30:55PM +0400, Ilia Kantor wrote: Let table A be inherited by A1, A2, A3. How to select from A records where actual relations are A1, A2 ? If A1 and A2 will be naturally together, where compared to A, or A3, why not introduce an intermediate table? A would be inherited by A12, and A3. A12 would be inherited by A1, and A2. You can do the UNION yourself, as well. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Making pgxs builds work with a relocated installation
Alvaro Herrera [EMAIL PROTECTED] writes: The only question is, do we want to force a Python dependency for building Postgres? That's not happening. Especially not now that Dave found the other solution does work ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] writes: I think the enhanced pgbench is quite usefull and I would like to include in 8.1. Or should I keep it for 8.2? Well, we've traditionally been laxer about contrib than the core software, so the fact that we're past feature freeze isn't sufficient reason to say no. I'm inclined to say yes because I think this might make a handy debugging tool --- for instance, Janning Vygen's current problem with temp tables might be reproducible using a pgbench script, without having to write any new software. Have you thought about ripping out all the original pgbench code and instead having a default script that duplicates the original behavior? Also it seems like it'd be useful if there could be more than one script, so as to test situations where the clients aren't all doing exactly the same thing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Database file compatability
Qingqing Zhou [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote in message There is a possible sequence like this: ALIGNOF_LONG4 ALIGNOF_DOUBLE 8 MAXIMUM_ALIGNOF 8 vs. ALIGNOF_LONG8 ALIGNOF_DOUBLE 8 MAXIMUM_ALIGNOF 8 So we should at least check ALIGNOF_LONG as well. No, we don't need to, because we do not really care about ALIGNOF_LONG per se. We don't use long as an on-disk datatype, precisely because we don't know what size it is. We use int32 and int64. The former has align 4 on all machines AFAIK, and the latter has MAXIMUM_ALIGNOF. There are some machines with sizeof(int) equals to 64, if my memory saves, which might imply that ALIGNOF_INT equals to 8. If there were such a machine, Postgres wouldn't run on it anyway, and a lot of other software too. There'd be no way to have both int16 and int32 types (short could cover only one of them). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
What I am primarily worried about in your patch is the exposure of
::regclass as a recommended way of doing things. I know we can
discourage its us later, but once people start using something, it is
hard to change.
Why shouldn't it be a recommended way of doing things? It is certainly
far better than the existing text-argument way.
::regclass just seems too low-level to be something we should recommend.
We have tried to move away from :: casts in the default clauses. What
really concerns me is that for the most common case, where you want oid
binding at object creation time, you need to use ::regclass, while
ideally we would have the ::regclass behavior be the default. (I don't
have problems with people seeing ::regclass after the object is created,
and I think it does help clarify that is it not a string that is stored.
Right now, we have three cases, SERIAL, DEFAULT with no-schema seqname,
and DEFAULT with schema-specified seqname. If we just do regclass
internally for SERIAL, we don't have any user-visible change, except for
the psql \d display of the default.
The second case already works because there is no class name.
No, it really wouldn't work at all. It's unsafe if the user changes
the search path for example, and it certainly doesn't handle any of the
renaming or change-of-schema cases.
True, but the rename of the schema case would work.
It is
only the last one where recommending regclass helps, but is it worth
improving sequence/schema renaming by exposing and recommending a
::regclass syntax that will go away as soon as we fix this properly?
Please explain what you think a proper fix is. I think this patch is
a proper fix. I see no better alternative that we might implement
later.
The only other thing that's been discussed is the SQL2003 syntax
NEXT VALUE FOR sequencename
but this is in fact just syntactic sugar for something functionally
equivalent to nextval('sequencename'::regclass). It cannot completely
replace all uses of the nextval function, because only a constant table
name can appear.
Hmm ... given the proposed patch, it would indeed take only a few more
lines in gram.y to support the NEXT VALUE FOR syntax ...
Yes, I was thinking of something cleaner-looking. I have no trouble
fixing ALTER SCHEMA RENAME, but I would like it to be something that is
well thought out that will last unchanged from release to release,
rather than something hastily done. Just the fact you are considering
making ::regclass the default for nextval() in a later release means it
isn't a long-term solution, in terms of syntax that the user has to use
_now_, but not later.
--
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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Database file compatability
Qingqing Zhou [EMAIL PROTECTED] wrote Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] There are no platforms for which ALIGNOF_SHORT is different from 2. I don't think there are any platforms we care about where ALIGNOF_INT is different from 4. The cases of interest are ALIGNOF_DOUBLE, ALIGNOF_LONG, ALIGNOF_LONG_LONG_INT (note that MAXIMUM_ALIGNOF is just the largest of these). In practice long int is the same type as either int or long long int, so ALIGNOF_LONG isn't a distinct case either. What it comes down to is that MAXIMUM_ALIGNOF is sufficient to tell the difference between the platforms we need to deal with. If you have a counterexample, tell us about it. (1) Yes, ALIGNOF_SHORT is always 2. (2) There is a possible sequence like this: ALIGNOF_LONG4 ALIGNOF_DOUBLE 8 MAXIMUM_ALIGNOF 8 vs. ALIGNOF_LONG8 ALIGNOF_DOUBLE 8 MAXIMUM_ALIGNOF 8 Eg. http://developers.sun.com/prodtech/cc/articles/about_amd64_abi.html http://devrsrc1.external.hp.com/STK/wellbehavedrestrict.html So we should at least check ALIGNOF_LONG as well. (3) There are some machines with sizeof(int) equals to 64, if my memory saves, which might imply that ALIGNOF_INT equals to 8. sizeof(int) maybe 8, but not 64. And the configure option `--enable-integer-datetimes' may affect the data layout. So conservatively, we'd better check ALIGNOF_INT, ALIGNOF_LONG and MAXIMUM_ALIGNOF. Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] writes: I think the enhanced pgbench is quite usefull and I would like to include in 8.1. Or should I keep it for 8.2? Well, we've traditionally been laxer about contrib than the core software, so the fact that we're past feature freeze isn't sufficient reason to say no. I'm inclined to say yes because I think this might make a handy debugging tool --- for instance, Janning Vygen's current problem with temp tables might be reproducible using a pgbench script, without having to write any new software. Ok. I will commit patches and will continue to work on below. Have you thought about ripping out all the original pgbench code and instead having a default script that duplicates the original behavior? No, but seems an idea. There are few things I need to think about: 1) There are three pgbench's default behaviors: TPC-B like(default), -N and -S. So we need three scripts. 2) it requires a location where those scripts reside. $prefix/etc seems appropriate? Also it seems like it'd be useful if there could be more than one script, so as to test situations where the clients aren't all doing exactly the same thing. How do you assign different scripts to different clients? -- SRA OSS, Inc. Japan Tatsuo Ishii ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote:
It is
only the last one where recommending regclass helps, but is it worth
improving sequence/schema renaming by exposing and recommending a
::regclass syntax that will go away as soon as we fix this properly?
Please explain what you think a proper fix is. I think this patch is
a proper fix. I see no better alternative that we might implement
later.
The only other thing that's been discussed is the SQL2003 syntax
NEXT VALUE FOR sequencename
but this is in fact just syntactic sugar for something functionally
equivalent to nextval('sequencename'::regclass). It cannot completely
replace all uses of the nextval function, because only a constant table
name can appear.
Hmm ... given the proposed patch, it would indeed take only a few more
lines in gram.y to support the NEXT VALUE FOR syntax ...
Just to follow up, I agree we can't totally replace all instances of
nextval() with regclass because regclass requires a constant string, but
I would like to have the regclass behavior with simple syntax and
require people who want late binding of the sequence name to use some
extra syntax, like ::text or something. This seems like the only way
sequence naming will be sustainable from release to release. Saying
use ::regclass over and over again, when 99% of users should be using
it for nextval in default clauses, is going to get very tiring.
The other question is whether we should be playing with this at all
during beta. Should we just disable ALTER SCHEMA RENAME and return to
this during 8.2? I am worried these side missions will delay our final
release of 8.1.
--
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 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] \d on database with a lot of tables is slow
Jim C. Nasby [EMAIL PROTECTED] writes: Sorry, remembered it wrong. It's 'Did not find any relation named', which appears to be in bin/psql/describe.c. It does occur when trying to do a \d on a specific table. Hmm, no immediate ideas. You haven't provided a lot of context about this --- when it happens, is it repeatable? Are they giving an exact table name or a pattern to \d? Is a schema name included in what they give to \d? What PG version are they running exactly? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] writes: 2) it requires a location where those scripts reside. $prefix/etc seems appropriate? I was thinking of just embedding the default scripts as constants in the program text. A little bit ugly but saves a lot of headache with needing to find installed files. Also it seems like it'd be useful if there could be more than one script, so as to test situations where the clients aren't all doing exactly the same thing. How do you assign different scripts to different clients? I'd be happy with either round-robin or random selection of a new script each time a thread finishes a script. 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] [PATCHES] Proposed patch for sequence-renaming problems
Bruce Momjian pgman@candle.pha.pa.us writes: Just to follow up, I agree we can't totally replace all instances of nextval() with regclass because regclass requires a constant string, but I would like to have the regclass behavior with simple syntax and require people who want late binding of the sequence name to use some extra syntax, like ::text or something. That would require a considerably more invasive change, AFAICS: remove the text-input version of nextval() and introduce an implicit coercion from text to regclass to avoid breaking existing dumps. I'd prefer not to mess with that during beta, because there'd be nontrivial risk of breaking existing behaviors. Because the proposed patch just adds on new functions and doesn't change the behavior of existing DEFAULT clauses, I don't think it can break anything. However, we could certainly add the NEXT VALUE FOR syntax if that will satisfy your concern about syntax. The other question is whether we should be playing with this at all during beta. Should we just disable ALTER SCHEMA RENAME and return to this during 8.2? I am worried these side missions will delay our final release of 8.1. I'm prepared to argue that this is a bug fix, not only for ALTER SCHEMA RENAME but for some very long-standing problems with renaming of sequences. As I said before, you are seriously mistaken to consider that disabling ALTER SCHEMA RENAME would eliminate the problem. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Database file compatability
William ZHANG [EMAIL PROTECTED] wrote sizeof(int) maybe 8, but not 64. And the configure option `--enable-integer-datetimes' may affect the data layout. Yes, typo. This has been checked by ControlFileData.enableIntTimes. Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Just to follow up, I agree we can't totally replace all instances of nextval() with regclass because regclass requires a constant string, but I would like to have the regclass behavior with simple syntax and require people who want late binding of the sequence name to use some extra syntax, like ::text or something. That would require a considerably more invasive change, AFAICS: remove the text-input version of nextval() and introduce an implicit coercion from text to regclass to avoid breaking existing dumps. I'd prefer not to mess with that during beta, because there'd be nontrivial risk of breaking existing behaviors. Because the proposed patch just adds on new functions and doesn't change the behavior of existing DEFAULT clauses, I don't think it can break anything. However, we could certainly add the NEXT VALUE FOR syntax if that will satisfy your concern about syntax. I am personally fine with use ::regclass internally, especially for SERIAL. It is documenting its use (and recommending it) that has me concerned. We are placing additional burdens on users --- burdens that will not exist in 8.2 when we have more time to fix it right. Is it worth telling users to use ::regclass in their code for 8.1 just to fix this, and then telling them in 8.2 it is not necessary to use this? The other question is whether we should be playing with this at all during beta. Should we just disable ALTER SCHEMA RENAME and return to this during 8.2? I am worried these side missions will delay our final release of 8.1. I'm prepared to argue that this is a bug fix, not only for ALTER SCHEMA RENAME but for some very long-standing problems with renaming of sequences. As I said before, you are seriously mistaken to consider that disabling ALTER SCHEMA RENAME would eliminate the problem. If it was that bad, we should have fixed it during development, not during beta. The only reason it is getting attention now is because it is triggered more by a new feature we are adding, a feature we can easily remove. I know we both don't want to open up the entire TODO list for fixing during beta, especially fixing that isn't 100% complete and who's user-visible behavior will change in the next major release. Now, if we use ::regclass internally for just SERIAL, and don't document its use for sequences (or at last minimize its visibility), or we add NEXT VALUE FOR support and tell everyone to use that, that is fine with me because it is probably the best way for users to use this in defaults for all future releases. Am I correct that NEXT VALUE FOR is behavior which will be feature-complete and will be the recommended way to use sequences in defaults in all future releases? -- 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 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] unchecked malloc
Sibtay Abbas [EMAIL PROTECTED] writes: in pl_comp.c, plpgsql_build_variable takes a pointer to a PLpgSQL_type structure, which is always a malloc'ed instance(since we always use plpgsql_build_datatype function). As of current sources it's palloc'd, and should be released if the function is ever recompiled, so I see no strong reason to worry. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I still think we shouldn't be hashing this out during beta, but ... We're looking at ways to fix some bugs. It's never been the case that our first-resort response to a bug is pull out features. True, but your first guess was that none of this could be fixed in 8.2, then you proposed a 50% fix that was user-visible. Given those options, I do prefer removal of a minor feature. What would the final nextval() behavior be? ::regclass binding? How would late binding be done? What syntax? If I were prepared to say all that today, I would have just done it ;-) The more I think about it, the more I think that two sets of function names might not be such an awful idea. next_value(), curr_value(), and set_value() seem like they'd work well enough. Then we'd just say that nextval and friends are deprecated except when you need late binding, and we'd be done. I don't like the val/value distinction (the added ue means what?). Perhaps next_seq/curr_seq/set_seq would work more cleanly. I never liked that the function names had no reference to sequence in them. Didn't next_val() come from Oracle? Does it make sense to make new non-Oracle compatible commands for this, especially since Oracle probably does early binding? What would make more sense perhaps would be for next_val to do early binding, and a new function do late binding, perhaps next_val_str(). -- 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 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] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote: However, we could certainly add the NEXT VALUE FOR syntax if that will satisfy your concern about syntax. Since the NEXT VALUE FOR syntax has a special meaning, would it be better to support the oracle-style syntax sequence.nextval for now (and use the ::regclass for this)? I am not sure how easy that is considering schema.sequence.nextval. Just a thought. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Michael Paesold wrote: Tom Lane wrote: However, we could certainly add the NEXT VALUE FOR syntax if that will satisfy your concern about syntax. Since the NEXT VALUE FOR syntax has a special meaning, would it be better to support the oracle-style syntax sequence.nextval for now (and use the ::regclass for this)? I am not sure how easy that is considering schema.sequence.nextval. Yes, that is the direction I thought we were going. -- 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Bruce Momjian pgman@candle.pha.pa.us writes: Michael Paesold wrote: Since the NEXT VALUE FOR syntax has a special meaning, would it be better to support the oracle-style syntax sequence.nextval for now (and use the ::regclass for this)? I am not sure how easy that is considering schema.sequence.nextval. Yes, that is the direction I thought we were going. We are further away than ever from being able to support that: regression=# select seq.nextval; ERROR: missing FROM-clause entry for table seq Given that that proposal has been on the TODO list for years, and no one has ever offered any workable way to implement it, I think waiting until a way appears is equivalent to saying none of this will ever get fixed. I'm not prepared to accept fix it in 8.2 unless you can present an implementation plan that can fix it in 8.2, and use the Oracle syntax isn't a plan. Moreover, providing a regclass-based nextval function doesn't foreclose us from supporting the Oracle syntax if someone does have a bright idea about it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: The more I think about it, the more I think that two sets of function names might not be such an awful idea. next_value(), curr_value(), and set_value() seem like they'd work well enough. Then we'd just say that nextval and friends are deprecated except when you need late binding, and we'd be done. I don't like the val/value distinction (the added ue means what?). Perhaps next_seq/curr_seq/set_seq would work more cleanly. I never liked that the function names had no reference to sequence in them. That doesn't really respond to the means what? question --- which of nextval and next_seq is the early binding form, and how do you remember? For that matter, how do you even remember that they're related? Still, I have no strong objection to those names, and am happy to go with them if that will resolve the discussion. Didn't next_val() come from Oracle? Does it make sense to make new non-Oracle compatible commands for this, especially since Oracle probably does early binding? What would make more sense perhaps would be for next_val to do early binding, and a new function do late binding, perhaps next_val_str(). We already have the function to do late binding, namely nextval(text). I see no percentage in inventing some random new name for a function that's been there forever --- unless the new name adheres to some standard, which these don't. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Tom Lane wrote:
The more I think about it, the more I think that two sets of function
names might not be such an awful idea. next_value(), curr_value(), and
set_value() seem like they'd work well enough. Then we'd just say that
nextval and friends are deprecated except when you need late binding,
and we'd be done.
I don't like the val/value distinction (the added ue means what?).
Perhaps next_seq/curr_seq/set_seq would work more cleanly. I never
liked that the function names had no reference to sequence in them.
That doesn't really respond to the means what? question --- which of
nextval and next_seq is the early binding form, and how do you
remember? For that matter, how do you even remember that they're
related? Still, I have no strong objection to those names, and am happy
to go with them if that will resolve the discussion.
Didn't next_val() come from Oracle? Does it make sense to make new
non-Oracle compatible commands for this, especially since Oracle
probably does early binding? What would make more sense perhaps would
be for next_val to do early binding, and a new function do late binding,
perhaps next_val_str().
We already have the function to do late binding, namely nextval(text).
I see no percentage in inventing some random new name for a function
that's been there forever --- unless the new name adheres to some
standard, which these don't.
I am thinking we need to have nextval('') do early binding and have
nextval(''::text) (or some other name) do late binding. The fact is
that 99% of users would prefer early binding, is my guess.
Also, when there is no standard, Oracle is the standard, and for Oracle,
nextval is early binding.
--
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
Re: [HACKERS] effective SELECT from child tables
On Wed, 2005-09-28 at 12:13 +0400, Ilia Kantor wrote:
Let table A be inherited by A1, A2, A3.
How to select from A records where actual relations are A1, A2 ?
Why not just select directly from the child tables? I can't get excited
about optimizing the case you propose.
Because WHERE concrete_class IN (a,b,c) is much more convenient and
flexible way of forming select then manually split request into many unions.
Also, this query runs on top of abstract class, so inheritance really
assists me here.
If you treat the sub-class Discriminator as a data item rather than some
additional syntax for class membership then you will find this works
very straightforwardly for you.
Include the Discriminator as a column in A and it will be inherited by
all A1, A2, A3.
e.g. concrete_class char(1) not null
then setup constraints on each table like so
ALTER TABLE A1 ADD CHECK (concrete_class = 'A')
ALTER TABLE A2 ADD CHECK (concrete_class = 'B')
ALTER TABLE A3 ADD CHECK (concrete_class = 'C')
then when you run a query like
SELECT * FROM A WHERE concrete_class IN ('A','B')
you will find that table A3, which corresponds to concrete_class C has
been excluded from your query.
Presumably A, B, C are all mutually exclusive, so the end result will be
the same as if you had used a UNION ALL set query.
This will add 1 byte per row in your superclass... and requires no
index. You can even add this as a DEFAULT value for each child table, so
the actual column concrete_class need not be mentioned in an INSERT
statement.
(I've got plans to add an ABSTRACT keyword to tables to follow along the
same set of OO terminology in describing this situation. In next
release, not 8.1)
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] Added documentation about caching, reliability
I have added a section to the top of the WAL docs explaining caching and reliability issues: http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html I also renamed the chapter Reilability rather than WAL. -- 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 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] Added documentation about caching, reliability
Hey Bruce, One grammar issue... otherwise great work. they can be moved to another computer with similar hardware and all committed transaction will remain intact. should be they can be moved to another computer with similar hardware and all committed transactions will remain intact.On 9/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote:I have added a section to the top of the WAL docs explaining caching and reliability issues:http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.htmlI also renamed the chapter Reilability rather than WAL. --Bruce Momjian|http://candle.pha.pa.uspgman@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 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-- Respectfully,Jonah H. Harris, Database Internals Architect EnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] Added documentation about caching, reliability
Thanks, fixed. --- Jonah H. Harris wrote: Hey Bruce, One grammar issue... otherwise great work. they can be moved to another computer with similar hardware and all committed transaction will remain intact. should be they can be moved to another computer with similar hardware and all committed transactions will remain intact. On 9/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote: I have added a section to the top of the WAL docs explaining caching and reliability issues: http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html I also renamed the chapter Reilability rather than WAL. -- 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 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 -- Respectfully, Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation http://www.enterprisedb.com/ -- 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 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] [PATCHES] Proposed patch for sequence-renaming problems
Bruce Momjian pgman@candle.pha.pa.us writes:
I am thinking we need to have nextval('') do early binding and have
nextval(''::text) (or some other name) do late binding.
You can't have that in exactly that form, because text is invariably
the preferred resolution of unknown-type literals, and we certainly
dare not tinker with that rule. There is therefore no way that the
above two syntaxes are going to act differently. If we were willing to
change the name of the existing nextval functionality, we could have,
say,
nextval(regclass)
nextval_late(text)
where the latter is the new spelling of the existing function.
To make this work without breaking existing dumps (which will all say
nextval('foo'::text) it'd be necessary to introduce an implicit cast
from text to regclass. That scares me a fair amount --- cross category
implicit casts are generally evil. However, it might be OK given that
there are so few functions that take regclass arguments.
This still wouldn't put us in a place where existing dumps are
automatically fixed up during import. We'd parse the expressions as
nextval('foo'::text::regclass), which will work but it's effectively
still late-binding --- the actual constant is just text not regclass.
There's no way to fold it down to 'foo'::regclass automatically because
(1) we don't do constant-folding before storing expressions, and (2)
even if we did, the text to regclass cast cannot be marked immutable
(it's only stable). So people would still have to go through and change
their schemas by hand to get to the early-binding behavior.
Given all that, it seems the better part of valor to leave nextval()
as-is: there's too much risk and too little reward in that path. The
next best alternative is to use some other name than nextval for the
early-binding form, and to encourage people to move to the new name.
Same amount of manual schema-updating, much less risk of breaking existing
code due to unforeseen side-effects, much less confusion about what does
which.
BTW, I've gone back to thinking that next_value is the best alternative
spelling, because it calls to mind the SQL standard syntax NEXT VALUE
FOR (which I assume we'll want to support eventually).
Also, when there is no standard, Oracle is the standard, and for Oracle,
nextval is early binding.
Oracle does not spell nextval in any of these ways, so that argument
carries little weight. If we were using exactly the Oracle syntax, then
sure, but we're not. Also, we have to put some weight on backward
compatibility with our own past practice.
So on the whole I like leaving nextval() as-is and introducing a
separate function next_value(regclass).
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] effective SELECT from child tables
If you treat the sub-class Discriminator as a data item rather than some additional syntax for class membership then you will find this works very straightforwardly for you. Your suggestion is essentially the same as mine.. There exists tableoid, pretty much suited to tell between tables in the case of inheritance.. I can't see a real need to add a special class classifier to each table.. This solution is a workaround. It will work, just can't make myself love it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Found small issue with OUT params
Hi,
consider this function:
CREATE OR REPLACE FUNCTION FIND_USER_SOCKET_BYNAME (
IN IN_USERNAME VARCHAR,
OUT OUT_SOCKET_ADDRESS INTEGER)
AS
$BODY$
BEGIN
select socket_address from userdata where fullname = in_username into
out_socket_address;
if out_socket_address is null then
out_socket_address = 0 ;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function which I would expect
if I called it like this:
select FIND_USER_SOCKET_BYNAME('juser');
If I add another OUT value then the value comes back with the name of
the out param(plus the temp one I added) as expected.
It's easy enough to work around, but was not as expected.
Thanks,
Tony Caduto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
Tony Caduto [EMAIL PROTECTED] writes:
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function
This is intentional, for compatibility with the pre-existing behavior
with functions in FROM. A function that isn't returning a record is
effectively declared as
FROM foo(...) AS foo(foo)
while a function that does return a record type gives you
FROM foo(...) AS foo(col1, col2)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [DOCS] Added documentation about caching, reliability
On Wed, Sep 28, 2005 at 02:26:24PM -0400, Bruce Momjian wrote: I have added a section to the top of the WAL docs explaining caching and reliability issues: http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html I also renamed the chapter Reilability rather than WAL. While you are re-titling the docs, maybe the sections on that chapter could be titled Benefits of Write-Ahead Log instead of Benefits of WAL, etc. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34 La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos... (Yo, hablando de sueños eróticos) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] effective SELECT from child tables
On Wed, Sep 28, 2005 at 11:00:46PM +0400, Ilia Kantor wrote: Your suggestion is essentially the same as mine.. There exists tableoid, pretty much suited to tell between tables in the case of inheritance.. I can't see a real need to add a special class classifier to each table.. This solution is a workaround. It will work, just can't make myself love it. I wonder if it would be possible to tweak the constraints exclusion code so that if it sees something of the form tableoid = X to exclude other tables... You know, assume each table has a constraint tableoid = OID. Still, it is a fairly unusual feature. -- 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. pgp13avInTZnX.pgp Description: PGP signature
Re: [HACKERS] Open items list for 8.1
On Tue, 27 Sep 2005, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: fix ALTER SCHEMA RENAME for sequence dependency, or remove feature I've posted a proposed patch to fix this. The patch requires an initdb (to add new sequence functions), so if we do that we may as well also fix the 32/64bit risk mentioned here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg01241.php Also, the floor seems open to discuss whether or not to revert the file access functions to their pre-beta2 APIs. I've got mixed feelings about that myself, but you can certainly make a case that the current definitions are not enough cleaner than what was there before to justify changing. This seems particularly true for pg_cancel_backend(), which already was in the core in 8.0. I am thinking we should keep things as they are now. The problem isn't whether or not they should be changed, the problem is that they were changed *during* beta AND *against* the direction that discussion on these changes went ... pre-beta would have been more acceptable, but pre-feature freeze would have been much preferred ... but *post-beta*, this should never have happened unless it created a critical bug, which I have seen no arguments that it did ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Vacuum questions...
On T, 2005-09-27 at 17:57 -0500, Jim C. Nasby wrote: On Tue, Sep 27, 2005 at 02:47:46PM -0400, Jan Wieck wrote: On 9/24/2005 8:17 PM, Jim C. Nasby wrote: Would it be difficult to vacuum as part of a dump? The reasoning behind this is that you have to read the table to do the dump anyway, I think aside from what's been said so far, it would be rather difficult anyway. pg_dump relies on MVCC and requires to run in one transaction to see a consistent snapshot while vacuum jiggles around with transactions in some rather non-standard way. Is this true even if they were in different connections? My (vague) understanding of the vacuum process is that it first vacuums indexes, and then vacuums the heap. actually (lazy) vacuum does this 1) scan heap, collect ctids of rows to remove 2) clean indexes 3) clean heap Since we don't dump indexes, there's nothing for backup to do while those are vacuumed, so my idea is: pg_dump: foreach (table) spawn vacuum wait for vacuum to hit heap start copy wait for analyze to finish next; probably the first heap scan of vacuum would go faster than dump as it does not have to write out anything, and the second scan ( nr 3 in above list ) would be either faster or slower, as it has to lock each page and rearrange tuples there. so it would be very hard to synchronize vacuum with either of them. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Open items list for 8.1
On Wed, 2005-28-09 at 18:35 -0300, Marc G. Fournier wrote: The problem isn't whether or not they should be changed, the problem is that they were changed *during* beta AND *against* the direction that discussion on these changes went I'm not sure what you mean: what is the direction that discusson on these changes went? (If you're referring to complete vs. total, that hardly constitutes a change in direction.) ... pre-beta would have been more acceptable, but pre-feature freeze would have been much preferred I think there is an argument to be made for reverting pg_cancel_backend, since that function was released with 8.0. Personally I'm sceptical that there are very many people using that function in scripts (particularly using it in such a way that their scripts will break if the return type is changed). Since we've already made the change, I don't really see the point in reverting it, but I don't mind if someone wants to do it. As for the other changes, I think there is absolutely no reason to revert them. Since when is making changes to the signatures of new functions forbidden during the beta period? AFAIK we don't make guarantees of backward compatibility during the beta period, nor would it be sensible to do so. We had the opportunity to fix some poor API choices, and since an initdb was already required I think making these changes for beta2 was quite reasonable. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] execution of prepared statements leaks memory
Dmitry Karasik [EMAIL PROTECTED] writes: I'm not sure whether I've stumbled over a bug or a feature, so I'm curious if someone might explain that. It's a bug --- _SPI_execute_plan isn't handling the possibility that a utility command could create a tupletable that could be passed back to the caller. So the tuptable gets leaked. I'll try to fix this for 8.1, not sure if it'd be wise to try to back-patch. 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] Found small issue with OUT params
Tom Lane wrote:
Tony Caduto [EMAIL PROTECTED] writes:
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function
This is intentional, for compatibility with the pre-existing behavior
with functions in FROM. A function that isn't returning a record is
effectively declared as
FROM foo(...) AS foo(foo)
while a function that does return a record type gives you
FROM foo(...) AS foo(col1, col2)
regards, tom lane
Tom,
Please don't take this the wrong way, but don't you think even if a
single param is declared as OUT it should return the name of the OUT param?
If the function has no OUT params and uses the return keyword it should
return the name of the function, if it has one or many out params it
should return even a single column as the name of the OUT param.
It seems inconsistant that just because I have one OUT param declared it
does not return the name I specified for that param.
Isn't it possible to detect that the function has a single OUT param
declared and if a OUT param is declared return that name?
I am bringing this up because people coming over from Oracle or MS SQL
server will notice something like this.
Thanks,
Tony Caduto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] enhanced pgbench
Tatsuo Ishii [EMAIL PROTECTED] writes: 2) it requires a location where those scripts reside. $prefix/etc seems appropriate? I was thinking of just embedding the default scripts as constants in the program text. A little bit ugly but saves a lot of headache with needing to find installed files. I see. Also it seems like it'd be useful if there could be more than one script, so as to test situations where the clients aren't all doing exactly the same thing. How do you assign different scripts to different clients? I'd be happy with either round-robin or random selection of a new script each time a thread finishes a script. Ok. -- SRA OSS, Inc. Japan Tatsuo Ishii ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] postgresql clustering
Daniel Duvall wrote: I've looked at PostgreSQL and EnterpriseDB, but I can't find anything definitive as far as clustering capabilities. What kinds of projects are there for clustering PgSQL, and are any of them mature enough for commercial apps? As you well know clustering means all and nothing at the same time. We do have a commercial failover cluster for provided by Redhat, with postgres running on it. The Postgres is installed on both nodes and the data are stored on SAN, only one instance of postgres run at time in one of two nodes. In last 2 years we had a failure and the service relocation worked as expected. Consider also that applications shall have a good behaviour like try to close the current connection and retry to open a new one for a while Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] postgresql clustering
Gaetano Mendola wrote: Daniel Duvall wrote: I've looked at PostgreSQL and EnterpriseDB, but I can't find anything definitive as far as clustering capabilities. What kinds of projects are there for clustering PgSQL, and are any of them mature enough for commercial apps? Are you looking for clustering or replication? There are two very popular replication solutions: Slony-I and Mammoth Replicator. Slony-I is an external replication solution, Mammoth Replicator is a complete PostgreSQL + Replication solution. Sincerely, Joshua D. Drake As you well know clustering means all and nothing at the same time. We do have a commercial failover cluster for provided by Redhat, with postgres running on it. The Postgres is installed on both nodes and the data are stored on SAN, only one instance of postgres run at time in one of two nodes. In last 2 years we had a failure and the service relocation worked as expected. Consider also that applications shall have a good behaviour like try to close the current connection and retry to open a new one for a while Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open items list for 8.1
Marc G. Fournier wrote: On Tue, 27 Sep 2005, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: fix ALTER SCHEMA RENAME for sequence dependency, or remove feature I've posted a proposed patch to fix this. The patch requires an initdb (to add new sequence functions), so if we do that we may as well also fix the 32/64bit risk mentioned here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg01241.php Also, the floor seems open to discuss whether or not to revert the file access functions to their pre-beta2 APIs. I've got mixed feelings about that myself, but you can certainly make a case that the current definitions are not enough cleaner than what was there before to justify changing. This seems particularly true for pg_cancel_backend(), which already was in the core in 8.0. I am thinking we should keep things as they are now. The problem isn't whether or not they should be changed, the problem is that they were changed *during* beta AND *against* the direction that discussion on these changes went ... pre-beta would have been more acceptable, but pre-feature freeze would have been much preferred ... but *post-beta*, this should never have happened unless it created a critical bug, which I have seen no arguments that it did ... It was done quickly to complete it for beta2. Neil talked to Tom and me about it before he made the change. Obviously we all guessed wrong on this one. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
I am thinking we need to have nextval('') do early binding and have
nextval(''::text) (or some other name) do late binding.
You can't have that in exactly that form, because text is invariably
the preferred resolution of unknown-type literals, and we certainly
dare not tinker with that rule. There is therefore no way that the
above two syntaxes are going to act differently. If we were willing to
change the name of the existing nextval functionality, we could have,
say,
nextval(regclass)
nextval_late(text)
This is the first proposal I like. 99% of users think that nextval() is
doing early binding (or never thought of it), so I think moving to that
syntax is a win. Is late/dynamic/string/virtual the right suffix?
where the latter is the new spelling of the existing function.
To make this work without breaking existing dumps (which will all say
nextval('foo'::text) it'd be necessary to introduce an implicit cast
from text to regclass. That scares me a fair amount --- cross category
implicit casts are generally evil. However, it might be OK given that
there are so few functions that take regclass arguments.
This still wouldn't put us in a place where existing dumps are
automatically fixed up during import. We'd parse the expressions as
nextval('foo'::text::regclass), which will work but it's effectively
still late-binding --- the actual constant is just text not regclass.
There's no way to fold it down to 'foo'::regclass automatically because
(1) we don't do constant-folding before storing expressions, and (2)
even if we did, the text to regclass cast cannot be marked immutable
(it's only stable). So people would still have to go through and change
their schemas by hand to get to the early-binding behavior.
I am thinking we should hard-code something in the backend so if the
function oid is nextval/currval/setval, we strip off any text casting
internally. These functions are already pretty special in their usage
so I don't see a problem in fixing it this way.
Ideally we could do a test in the grammar and strip off the ::text
there.
Given all that, it seems the better part of valor to leave nextval()
as-is: there's too much risk and too little reward in that path. The
next best alternative is to use some other name than nextval for the
early-binding form, and to encourage people to move to the new name.
Same amount of manual schema-updating, much less risk of breaking existing
code due to unforeseen side-effects, much less confusion about what does
which.
BTW, I've gone back to thinking that next_value is the best alternative
spelling, because it calls to mind the SQL standard syntax NEXT VALUE
FOR (which I assume we'll want to support eventually).
True, but it doesn't have the standard behavior. Would we change that
when we add NEXT VALUE?
Also, when there is no standard, Oracle is the standard, and for Oracle,
nextval is early binding.
Oracle does not spell nextval in any of these ways, so that argument
carries little weight. If we were using exactly the Oracle syntax, then
sure, but we're not. Also, we have to put some weight on backward
compatibility with our own past practice.
So on the whole I like leaving nextval() as-is and introducing a
separate function next_value(regclass).
I disagree. nextval() is too embedded in people's thinking to make them
change when we have the ability to have it do the _right_ _thing_, and
give a dynamic alternative for those you need it.
Also, Oracle does use nextval as my_docs_seq.nextval so the use of that
keyword is a good policy to continue.
--
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 6: explain analyze is your friend
Re: [HACKERS] Found small issue with OUT params
Tony Caduto [EMAIL PROTECTED] writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because create function foo (in x int, out y float) is supposed to have the same external behavior as create function foo (in x int) returns float. I agree it's a bit of a judgment call, but I do not see a case for changing it. 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] [DOCS] Added documentation about caching, reliability
Alvaro Herrera wrote: On Wed, Sep 28, 2005 at 02:26:24PM -0400, Bruce Momjian wrote: I have added a section to the top of the WAL docs explaining caching and reliability issues: http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html I also renamed the chapter Reilability rather than WAL. While you are re-titling the docs, maybe the sections on that chapter could be titled Benefits of Write-Ahead Log instead of Benefits of WAL, etc. The chapter sections are: Table of Contents 26.1. Write-Ahead Logging (WAL) 26.2. Benefits of WAL 26.3. WAL Configuration 26.4. WAL Internals Are you suggesting we spell out Write-Ahead Logging for all section titles? -- 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 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] [DOCS] Added documentation about caching, reliability
IMHO, spelling out each one is redundant... the first one Write-Ahead Logging (WAL) seems to define WAL for use later in the text. On 9/28/05, Bruce Momjian pgman@candle.pha.pa.us wrote: Alvaro Herrera wrote: On Wed, Sep 28, 2005 at 02:26:24PM -0400, Bruce Momjian wrote: I have added a section to the top of the WAL docs explaining caching and reliability issues: http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html I also renamed the chapter Reilability rather than WAL. While you are re-titling the docs, maybe the sections on that chapter could be titled Benefits of Write-Ahead Log instead of Benefits of WAL, etc.The chapter sections are: Table of Contents26.1. Write-Ahead Logging (WAL)26.2. Benefits of WAL26.3. WAL Configuration26.4. WAL InternalsAre you suggesting we spell out Write-Ahead Logging for all section titles?--Bruce Momjian|http://candle.pha.pa.uspgman@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 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-- Respectfully,Jonah H. Harris, Database Internals Architect EnterpriseDB Corporationhttp://www.enterprisedb.com/
Re: [HACKERS] Found small issue with OUT params
Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because create function foo (in x int, out y float) is supposed to have the same external behavior as create function foo (in x int) returns float. I agree it's a bit of a judgment call, but I do not see a case for changing it. I am agreeing with the poster that use of OUT should always print the out parameter name. Is there a downside to doing that? Seems it gives people an option. -- 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 6: explain analyze is your friend
Re: [HACKERS] Open items list for 8.1
Bruce Momjian pgman@candle.pha.pa.us writes: It was done quickly to complete it for beta2. Neil talked to Tom and me about it before he made the change. Obviously we all guessed wrong on this one. Personally I had forgotten that pg_cancel_backend was in the previous release and so there was a backwards-compatibility issue to consider. There's no doubt that a boolean return value is cleaner than an int return value, but we don't ordinarily make non-backward-compatible changes just because they're cleaner. Comparable case: timeofday() is still returning text not timestamptz after all these years, even though that is *obviously* the wrong API, and even though we could probably change it without a huge risk of breaking things. As for the total-vs-complete function name business, I do personally like total better, but the time to have been making that argument was back during the original discussion, which itself went on way too long. Renaming it now with relatively little discussion was definitely a violation of our normal development process. I'll take my fair share of the blame for this, because I encouraged Neil to do it without stopping to think that the names had already been hashed over extensively. But it was the wrong way to proceed. In short, yeah, I think we should revert. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Just to summarize, I am arguing from a usability perspective, because I
believe the simplest API is one that will last for many releases and not
have to be redesigned, nor require too much adjustment from our users.
---
Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
I am thinking we need to have nextval('') do early binding and have
nextval(''::text) (or some other name) do late binding.
You can't have that in exactly that form, because text is invariably
the preferred resolution of unknown-type literals, and we certainly
dare not tinker with that rule. There is therefore no way that the
above two syntaxes are going to act differently. If we were willing to
change the name of the existing nextval functionality, we could have,
say,
nextval(regclass)
nextval_late(text)
This is the first proposal I like. 99% of users think that nextval() is
doing early binding (or never thought of it), so I think moving to that
syntax is a win. Is late/dynamic/string/virtual the right suffix?
where the latter is the new spelling of the existing function.
To make this work without breaking existing dumps (which will all say
nextval('foo'::text) it'd be necessary to introduce an implicit cast
from text to regclass. That scares me a fair amount --- cross category
implicit casts are generally evil. However, it might be OK given that
there are so few functions that take regclass arguments.
This still wouldn't put us in a place where existing dumps are
automatically fixed up during import. We'd parse the expressions as
nextval('foo'::text::regclass), which will work but it's effectively
still late-binding --- the actual constant is just text not regclass.
There's no way to fold it down to 'foo'::regclass automatically because
(1) we don't do constant-folding before storing expressions, and (2)
even if we did, the text to regclass cast cannot be marked immutable
(it's only stable). So people would still have to go through and change
their schemas by hand to get to the early-binding behavior.
I am thinking we should hard-code something in the backend so if the
function oid is nextval/currval/setval, we strip off any text casting
internally. These functions are already pretty special in their usage
so I don't see a problem in fixing it this way.
Ideally we could do a test in the grammar and strip off the ::text
there.
Given all that, it seems the better part of valor to leave nextval()
as-is: there's too much risk and too little reward in that path. The
next best alternative is to use some other name than nextval for the
early-binding form, and to encourage people to move to the new name.
Same amount of manual schema-updating, much less risk of breaking existing
code due to unforeseen side-effects, much less confusion about what does
which.
BTW, I've gone back to thinking that next_value is the best alternative
spelling, because it calls to mind the SQL standard syntax NEXT VALUE
FOR (which I assume we'll want to support eventually).
True, but it doesn't have the standard behavior. Would we change that
when we add NEXT VALUE?
Also, when there is no standard, Oracle is the standard, and for Oracle,
nextval is early binding.
Oracle does not spell nextval in any of these ways, so that argument
carries little weight. If we were using exactly the Oracle syntax, then
sure, but we're not. Also, we have to put some weight on backward
compatibility with our own past practice.
So on the whole I like leaving nextval() as-is and introducing a
separate function next_value(regclass).
I disagree. nextval() is too embedded in people's thinking to make them
change when we have the ability to have it do the _right_ _thing_, and
give a dynamic alternative for those you need it.
Also, Oracle does use nextval as my_docs_seq.nextval so the use of that
keyword is a good policy to continue.
--
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 6: explain analyze is your friend
--
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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [DOCS] Added documentation about caching, reliability
On Wed, Sep 28, 2005 at 10:08:02PM -0400, Bruce Momjian wrote: The chapter sections are: Table of Contents 26.1. Write-Ahead Logging (WAL) 26.2. Benefits of WAL 26.3. WAL Configuration 26.4. WAL Internals Are you suggesting we spell out Write-Ahead Logging for all section titles? Is it considered good style to use acronyms in titles? I wouldn't do it. Maybe it could be left as 26.1. Write-Ahead Logging 26.2. Benefits of Write-Ahead Logging 26.3. WAL Configuration 26.4. WAL Internals because the former two talk about WAL in a general manner, while the latter two are about our implementation. I also wouldn't expand the acronym in a title. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 17.7, W 73º 14' 26.8 Thou shalt study thy libraries and strive not to reinvent them without cause, that thy code may be short and readable and thy days pleasant and productive. (7th Commandment for C Programmers) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [DOCS] Added documentation about caching, reliability
Alvaro Herrera wrote: On Wed, Sep 28, 2005 at 10:08:02PM -0400, Bruce Momjian wrote: The chapter sections are: Table of Contents 26.1. Write-Ahead Logging (WAL) 26.2. Benefits of WAL 26.3. WAL Configuration 26.4. WAL Internals Are you suggesting we spell out Write-Ahead Logging for all section titles? Is it considered good style to use acronyms in titles? I wouldn't do it. Maybe it could be left as 26.1. Write-Ahead Logging 26.2. Benefits of Write-Ahead Logging I modified 26.2 as you suggested. 26.3. WAL Configuration 26.4. WAL Internals because the former two talk about WAL in a general manner, while the latter two are about our implementation. I also wouldn't expand the acronym in a title. I think we do need to show the acronym in the title if use that acronym in later titles. -- 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
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Bruce Momjian pgman@candle.pha.pa.us writes: I am thinking we should hard-code something in the backend so if the function oid is nextval/currval/setval, we strip off any text casting internally. NO. No bloody way ... that is far dirtier than any other proposal that's been made in this thread. I don't even want to think about what strange corner-case semantics that might create. So on the whole I like leaving nextval() as-is and introducing a separate function next_value(regclass). I disagree. nextval() is too embedded in people's thinking to make them change Why? And what's your evidence for this? You could equally well argue that the fact that nextval takes a text argument is too embedded to change. when we have the ability to have it do the _right_ _thing_, We have no ability to make it do what you think is the right thing, at least not without introducing kluges that are certain to come back to haunt us. 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] [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am thinking we should hard-code something in the backend so if the function oid is nextval/currval/setval, we strip off any text casting internally. NO. No bloody way ... that is far dirtier than any other proposal that's been made in this thread. I don't even want to think about what strange corner-case semantics that might create. Well, it would be if ((oid == xxx || oid == yyy) cast_exists) remove cast; Seems safe to me. So on the whole I like leaving nextval() as-is and introducing a separate function next_value(regclass). I disagree. nextval() is too embedded in people's thinking to make them change Why? And what's your evidence for this? You could equally well argue that the fact that nextval takes a text argument is too embedded to change. 99% of people using nextval think (or don't care) that it is early binding. I see no reason to re-educate people just to keep nextval() as late binding. when we have the ability to have it do the _right_ _thing_, We have no ability to make it do what you think is the right thing, at least not without introducing kluges that are certain to come back to haunt us. Well, then, let's leave it all for 8.2 where we can discuss/test and come up with a plan. -- 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
Re: [HACKERS] [PATCHES] Proposed patch for sequence-renaming problems
Also, why is the nextval ::text casting output by pg_dump anyway? --- Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am thinking we should hard-code something in the backend so if the function oid is nextval/currval/setval, we strip off any text casting internally. NO. No bloody way ... that is far dirtier than any other proposal that's been made in this thread. I don't even want to think about what strange corner-case semantics that might create. So on the whole I like leaving nextval() as-is and introducing a separate function next_value(regclass). I disagree. nextval() is too embedded in people's thinking to make them change Why? And what's your evidence for this? You could equally well argue that the fact that nextval takes a text argument is too embedded to change. when we have the ability to have it do the _right_ _thing_, We have no ability to make it do what you think is the right thing, at least not without introducing kluges that are certain to come back to haunt us. regards, tom lane -- 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 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] Open items list
Here is the open item list: PostgreSQL 8.1 Open Items = Current version at http://candle.pha.pa.us/cgi-bin/pgopenitems or from http://www.postgresql.org/developer/beta. Bugs fix pg_dump --clean for roles Is there a way to fix this or do we remove --clean? foreign trigger timing issue Who is working on this? fix ALTER SCHEMA RENAME for sequence dependency, or remove In discussion currently. improve spinlock performance What is the timeframe for completion of this? fix semantic issues of granted permissions in roles Same as above. fix pgxs for spaces in file names I assume we have found a solution and are just waiting for a patch to be applied. Questions - consider O_SYNC as default when O_DIRECT exists /contrib move to pgfoundry pgindent? make sure bitmap scan optimizer settings are reasonable Documentation - -- 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 5: don't forget to increase your free space map settings
[HACKERS] pg_total_relation_size() could not open relation with OID X
Here's a test case for a pg_total_relation_size() failure:
test= CREATE TABLE foo (id integer);
CREATE TABLE
test= SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
---+-
26235 | 26235
(1 row)
test= SELECT pg_total_relation_size('foo');
pg_total_relation_size
0
(1 row)
test= TRUNCATE foo;
TRUNCATE TABLE
test= SELECT oid, relfilenode FROM pg_class WHERE relname = 'foo';
oid | relfilenode
---+-
26235 | 26237
(1 row)
test= SELECT pg_total_relation_size('foo');
ERROR: could not open relation with OID 26237
test= SELECT pg_total_relation_size(26235);
ERROR: could not open relation with OID 26237
test= SELECT pg_relation_size('foo');
pg_relation_size
--
0
(1 row)
--
Michael Fuhr
---(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] [PERFORM] A Better External Sort?
On Wed, 2005-09-28 at 12:03 -0400, Ron Peacetree wrote: From: Jeffrey W. Baker [EMAIL PROTECTED] Sent: Sep 27, 2005 1:26 PM To: Ron Peacetree [EMAIL PROTECTED] Subject: Re: [HACKERS] [PERFORM] A Better External Sort? On Tue, 2005-09-27 at 13:15 -0400, Ron Peacetree wrote: That Btree can be used to generate a physical reordering of the data in one pass, but that's the weakest use for it. The more powerful uses involve allowing the Btree to persist and using it for more efficient re-searches or combining it with other such Btrees (either as a step in task distribution across multiple CPUs or as a more efficient way to do things like joins by manipulating these Btrees rather than the actual records.) Maybe you could describe some concrete use cases. I can see what you are getting at, and I can imagine some advantageous uses, but I'd like to know what you are thinking. Specifically I'd like to see some cases where this would beat sequential scan. I'm thinking that in your example of a terabyte table with a column having only two values, all the queries I can think of would be better served with a sequential scan. In my original example, a sequential scan of the 1TB of 2KB or 4KB records, = 250M or 500M records of data, being sorted on a binary value key will take ~1000x more time than reading in the ~1GB Btree I described that used a Key+RID (plus node pointers) representation of the data. You are engaging in a length and verbose exercise in mental masturbation, because you have not yet given a concrete example of a query where this stuff would come in handy. A common, general-purpose case would be the best. We can all see that the method you describe might be a good way to sort a very large dataset with some known properties, which would be fine if you are trying to break the terasort benchmark. But that's not what we're doing here. We are designing and operating relational databases. So please explain the application. Your main example seems to focus on a large table where a key column has constrained values. This case is interesting in proportion to the number of possible values. If I have billions of rows, each having one of only two values, I can think of a trivial and very fast method of returning the table sorted by that key: make two sequential passes, returning the first value on the first pass and the second value on the second pass. This will be faster than the method you propose. I think an important aspect you have failed to address is how much of the heap you must visit after the sort is complete. If you are returning every tuple in the heap then the optimal plan will be very different from the case when you needn't. -jwb PS: Whatever mailer you use doesn't understand or respect threading nor attribution. Out of respect for the list's readers, please try a mailer that supports these 30-year-old fundamentals of electronic mail. ---(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
