[HACKERS] pgindent run
I have run pgindent on the C files and run pgjindent on the jdbc files as requested by the jdbc list. You can package up beta now. I will update the HISTORY file tomorrow with recent changes. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] TOra
Just noticed this QT software: http://www.globecom.net/tora/ It's a very lovely administrative tool for Oracle. I wonder if anyone would be interested in porting it to Postgres? Don't think many of the funky administrative functions can be acheived remotely in Postgres yet tho? Chris ---(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] "Triggered data change violation", once again
Tom Lane wrote: > > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> I think all we need to do to implement things correctly is to consider a > >> previous event only if both xmin and cmin of the old tuple match the > >> current xact & command IDs, rather than considering it on the basis of > >> xmin alone. > > > Are there any things that might update the command ID during the execution > > of the statement from inside functions that are being run? > > Functions can run new commands that get new command ID numbers within > the current transaction --- but on return from the function, the current > command number is restored. I believe rows inserted by such a function > would look "in the future" to us at the outer command, and would be > ignored. I'm suspicious if this is reasonable. If those changes are ignored when are taken into account ? ISTM deferred constraints has to see the latest tuples and take the changes into account. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposed new create command, CREATE OPERATOR CLASS
Bill Studenmund <[EMAIL PROTECTED]> writes: > [ revised proposal for CREATE OPERATOR CLASS syntax ] I don't like the idea of writing a bunch of consecutive commas (and having to count them correctly) for cases where we're inserting noncontigous amopstrategy or amprocnum numbers. Perhaps the syntax for the elements of the lists could be [ integer ] operator [ ( argtype, argtype ) ] [ RECHECK ] [ integer ] funcname ( argtypes ) where if the integer is given, it is the strategy/procnum for this entry, and if it's not given then it defaults to 1 for the first item and previous-entry's-number-plus-one for later items. Or just require the integer all the time. That seems a lot less mistake-prone, really. Concision is not a virtue in the case of a command as specialized as this. Is there really anything wrong with CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree WITH 1 ||<, 2 ||<=, 3 ||=, 4 ||>=, 5 ||> AND 1 complex_abs_cmp(complex, complex); (One could imagine adding system catalogs that give symbolic names to the strategy/procnum numbers for each access method, and then allowing names instead of integers in this command. I'm not sure whether GiST has sufficiently well-defined strategy numbers to make that work, but even if not, I like this better than a positional approach to figuring out which operator is which.) > I decided to change that to an operator followed by "needs_recheck" to > indicate a recheck is needed. "needs_recheck" is not handled as a keyword, > but as an IDENT which is examined at parse time. Ugh. Make it a keyword. As long as it can be a TokenId there is no downside to doing so, and doing it that way eliminates interesting issues about case folding etc. (Did you know that case folding rules are slightly different for keywords and identifiers?) I still like RECHECK better than NEEDS_RECHECK, but that's a minor quibble. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] storing binary data
> > I get the impression that the > > SELECT query it is based on is cached (ie. a cached query plan). > > Nope. If there's something in the docs that makes you think so, > point out so I can fix it ;-) Hmmm...I could have sworn that you mentioned in passing something about cached query plans and VIEWs - I must have been in dream land. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] storing binary data
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > What exactly is the advantage in using VIEWs? A level of logical indirection between the application and the physical data schema. There are no performance benefits. > I get the impression that the > SELECT query it is based on is cached (ie. a cached query plan). Nope. If there's something in the docs that makes you think so, point out so I can fix it ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] storing binary data
Quick question - I couldn't find this in the docs: What exactly is the advantage in using VIEWs? I get the impression that the SELECT query it is based on is cached (ie. a cached query plan). But, is this cached between db restarts, between connections, etc. Is it cached upon the first use of the view for a db instance for a particular connection, etc? Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane > Sent: Thursday, 25 October 2001 1:00 AM > To: Joe Conway > Cc: Jason Orendorff; [EMAIL PROTECTED] > Subject: Re: [HACKERS] storing binary data > > > Joe Conway <[EMAIL PROTECTED]> writes: > > I'll take a shot at improving the documentation for bytea. I'm hoping > > documentation patches are accepted during beta though ;-) > > Of course. The only limitation we place during beta is "no new features > added". I plan to spend a good deal of time on the docs during beta > 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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Can't cast bigint to smallint?
At 19:41 24/10/01 -0400, Tom Lane wrote: >We just had one earlier this week, so I suppose another wouldn't make >all that much difference. Comments? My pref would be for the initdb; the current situation may break (other) existing apps. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed new create command, CREATE OPERATOR CLASS
Bill Studenmund <[EMAIL PROTECTED]> writes: > I'd like to propose a new command, CREATE OPERATOR CLASS. Seems like a good idea. > operator spec is either an operator or an operator followed by the keyword > "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck > should be set to true for this operator. This is bogus, since REPEATABLE is a very poor description of the meaning of amopreqcheck; to the extent that it matches the meaning at all, it's backwards. Don't pick a keyword for this solely on the basis of what you can find that's already reserved by SQL99. Given the restricted syntax, the keyword could be a TokenId anyway, so it's not really reserved; accordingly there's no need to limit ourselves to what SQL99 says we can reserve. Perhaps use "RECHECK"? That would fit the field more closely... > I agree that I think it is rare that anything will set "REPEATABLE", but > the point of this effort is to keep folks from mucking around with the > system tables manually, so we should support making any reasonable entry > in pg_amop. Then you'd better add support for specifying an opckeytype, too. BTW these things are not all that rare; there are examples right now in contrib. > CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING > btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp; This syntax is obviously insufficient to identify the procedures, since it doesn't show argument lists (and we do allow overloading). Less obviously, it's not sufficient to identify the operators either. I think you're implicitly assuming that only binary operators on the specified type will ever be members of index opclasses. That does not seem like a good assumption to wire into the syntax. Perhaps borrow the syntax used for DROP OPERATOR, which is ugly but not ambiguous: operator (type, type) operator (type, NONE) operator (NONE, type) We could allow an operator without any parenthesized args to imply a binary op on the specified type, which would certainly be the most common case. BTW, is there any need to support filling nonconsecutive amopstrategy or amprocnum slots? This syntax can't do that. GiST seems to have a pretty loose idea of what set of strategy numbers you can have, so there might possibly be a future need for that. Also, it might be better to use a syntax in the style of CREATE OPERATOR, with a list of param = value notations, because that's more easily extensible if we change the opclass stuff again. CREATE OPERATOR CLASS classname ( basetype = complex, default, operator1 = ||< , ... proc1 = complex_abs_cmp ); However, specifying the proc arglists in this style would be awfully tedious :-(. I can't think of anything better than proc1arg1 = complex, proc1arg2 = complex, ... which is mighty ugly. 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] "Triggered data change violation", once again
On Wed, 24 Oct 2001, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> I think all we need to do to implement things correctly is to consider a > >> previous event only if both xmin and cmin of the old tuple match the > >> current xact & command IDs, rather than considering it on the basis of > >> xmin alone. > > > Are there any things that might update the command ID during the execution > > of the statement from inside functions that are being run? > > Functions can run new commands that get new command ID numbers within > the current transaction --- but on return from the function, the current > command number is restored. I believe rows inserted by such a function > would look "in the future" to us at the outer command, and would be > ignored. > > Actually, now that I think about it, the MVCC rules are that tuples with > xmin = currentxact are not visible unless they have cmin < currentcmd. > Not equal to. This seems to render the entire "triggered data change" > test moot --- I rather suspect that we cannot have such a condition > as old tuple cmin = currentcmd at all, and so we could just yank all > that code entirely. I'm not sure if this sequence would be an example of something that would be disallowed, but if I do something like: Make a plpgsql function that does update table1 set key=1 where key=2; Make that an after update trigger on table1 Put a key=1 row into table1 Update table1 to set key to 2 I end up with a 1 in the table. I'm not sure, but I think that such a case would be possible through the fk stuff with triggers that modify the primary key table (right now it might "work" due to the problems of checking intermediate states). Wouldn't this be the kind of thing the "triggered data change" is supposed to prevent? I may be just misunderstanding the intent of the spec. ---(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] Proposed new create command, CREATE OPERATOR CLASS
On Wed, 24 Oct 2001, Tom Lane wrote: > Bill Studenmund <[EMAIL PROTECTED]> writes: > > I'd like to propose a new command, CREATE OPERATOR CLASS. > > Seems like a good idea. > > > operator spec is either an operator or an operator followed by the keyword > > "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck > > should be set to true for this operator. > > This is bogus, since REPEATABLE is a very poor description of the > meaning of amopreqcheck; to the extent that it matches the meaning > at all, it's backwards. Don't pick a keyword for this solely on the > basis of what you can find that's already reserved by SQL99. > > Given the restricted syntax, the keyword could be a TokenId anyway, > so it's not really reserved; accordingly there's no need to limit > ourselves to what SQL99 says we can reserve. > > Perhaps use "RECHECK"? That would fit the field more closely... I was writing a note saying that as this one came in. Yes, it's now a TokenId, and I look for the text "needs_recheck". > > I agree that I think it is rare that anything will set "REPEATABLE", but > > the point of this effort is to keep folks from mucking around with the > > system tables manually, so we should support making any reasonable entry > > in pg_amop. > > Then you'd better add support for specifying an opckeytype, too. BTW > these things are not all that rare; there are examples right now in > contrib. Yep, I noticed that. > > CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING > > btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp; > > This syntax is obviously insufficient to identify the procedures, since > it doesn't show argument lists (and we do allow overloading). Less So then funcname(type list) [, funcname(type list)] would be the way to go? > obviously, it's not sufficient to identify the operators either. I > think you're implicitly assuming that only binary operators on the > specified type will ever be members of index opclasses. That does not > seem like a good assumption to wire into the syntax. Perhaps borrow Well, the requirement of binarity is something which is explicit in our example documentation, and so that's why I used it. > the syntax used for DROP OPERATOR, which is ugly but not ambiguous: > > operator (type, type) > operator (type, NONE) > operator (NONE, type) > > We could allow an operator without any parenthesized args to imply a > binary op on the specified type, which would certainly be the most > common case. Do any of the access methods really support using non-binary operators? > BTW, is there any need to support filling nonconsecutive amopstrategy or > amprocnum slots? This syntax can't do that. GiST seems to have a > pretty loose idea of what set of strategy numbers you can have, so > there might possibly be a future need for that. I can add support for skipping operators, if needed. A comma followed by a comma would indicate a null name. Oh gross. I just looked at contrib/intarray, and it defines two entries in pg_amop for amopstrategy number 20. They do happen to be commutators of each other. Look for the @@ and ~~ operators. Wait a second, how can you do that? Doesn't that violate pg_amop_opc_strategy_index ? It's supposed to make pairs of amopclaid and amopstrategy be unique. Confused > Also, it might be better to use a syntax in the style of CREATE > OPERATOR, with a list of param = value notations, because that's > more easily extensible if we change the opclass stuff again. > > CREATE OPERATOR CLASS classname ( > basetype = complex, > default, > operator1 = ||< , > ... > proc1 = complex_abs_cmp ); > > However, specifying the proc arglists in this style would be awfully > tedious :-(. I can't think of anything better than > > proc1arg1 = complex, > proc1arg2 = complex, > ... > > which is mighty ugly. Which is why I didn't use it. :-) If we can't make the other syntax work, then we can go with a DefineStmt type syntax. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] New default ignored by pre-exising insert rulesets.
Arguile <[EMAIL PROTECTED]> writes:
> If a table field is altered to add a default, the default value is
> bypassed by pre-existing rules.
Yeah, this problem has been known for awhile (to me at least). The
difficulty is that default values are added to INSERTs by the parser,
which is before rule creation and expansion. So the saved info about
the rule already has all the defaults it's gonna get. What's worse,
it won't track changes in existing defaults (though I'm not sure we
support altering defaults, anyway). If I do
regression=# create table foo (f1 int default 1, f2 int default 2);
CREATE
regression=# create view v1 as select * from foo;
CREATE
regression=# create rule v1i as on insert to v1 do instead
regression-# insert into foo values(new.f1);
CREATE
regression=# select pg_get_ruledef('v1i');
pg_get_ruledef
CREATE RULE v1i AS ON INSERT TO v1 DO INSTEAD INSERT INTO foo (f1, f2) VALUES
(new.f1, 2);
(1 row)
then I can see that the defaults have crept into what's stored for the
rule.
I believe the best fix for this is to move default-insertion out of the
parser and do it during planning, instead --- probably at the same
place that manipulates the insert's targetlist to match the column
ordering of the table. A possible objection is that default expressions
wouldn't be subject to rule manipulation, but we don't have any feature
that requires that anyway.
Comments anyone?
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] "Triggered data change violation", once again
Stephan Szabo <[EMAIL PROTECTED]> writes: >> I think all we need to do to implement things correctly is to consider a >> previous event only if both xmin and cmin of the old tuple match the >> current xact & command IDs, rather than considering it on the basis of >> xmin alone. > Are there any things that might update the command ID during the execution > of the statement from inside functions that are being run? Functions can run new commands that get new command ID numbers within the current transaction --- but on return from the function, the current command number is restored. I believe rows inserted by such a function would look "in the future" to us at the outer command, and would be ignored. Actually, now that I think about it, the MVCC rules are that tuples with xmin = currentxact are not visible unless they have cmin < currentcmd. Not equal to. This seems to render the entire "triggered data change" test moot --- I rather suspect that we cannot have such a condition as old tuple cmin = currentcmd at all, and so we could just yank all that code entirely. 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] "Triggered data change violation", once again
On Wed, 24 Oct 2001, Tom Lane wrote: > The only reason we do this is to enforce the "triggered data change > violation" restriction of the spec. However, I think we've > misinterpreted the spec. The code prevents an RI referenced value from > being changed more than once in a transaction, but what the spec > actually says is thou shalt not change it more than once per > *statement*. We have discussed this several times in the past and > I think people have agreed that the current behavior is wrong, > but nothing's been done about it. > > I think all we need to do to implement things correctly is to consider a > previous event only if both xmin and cmin of the old tuple match the > current xact & command IDs, rather than considering it on the basis of > xmin alone. Are there any things that might update the command ID during the execution of the statement from inside functions that are being run? I really don't understand the details of how that works (which is the biggest reason I haven't yet tackled some of the big remaining broken stuff in the referential actions, because AFAICT we need to be able to update a row that matched at the beginning of the statement, not the ones that match at the time the triggers run). ---(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] Can't cast bigint to smallint?
At 16:09 24/10/01 -0400, Tom Lane wrote: > >Huh? There's never been a cast from int8 to int2. I checked 7.0 and >7.1, they both complain as well: > Is this a policy decision, or just a case where noone has had a chance to do it? >Where exactly is pg_dump failing? > The problem in in the code to re-enable triggers: ...reltriggers = (select Count(*) So perhaps this version now has Count returning a bigint rather than an int? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Using an SMP machine to make multiple indices on
Tom Lane wrote: > > "Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > > In the end, I changed DefineIndex() to not call IndexesAreActive(). > > I saw that. But is it a good solution? If someone has deactivated > indexes on a user table (ie turned off relhasindex), then creating a > new index would activate them again, which would probably be bad. I apolgize my neglect of reconsidering the activte/deactivate stuff for indexes. Probably it is no longer needed now(since 7.1). Reindex under postmaster for user tables has been available from the first. I didn't write a documentation about it inten- tionally in 7.0 though it was my neglect also in 7.1 sorry. In 7.0 REINDEX set relhasindex to false first to tell all backends that the indexes are unavailable because we wasn't able to recreate indexes safely in case of abort. Note that relhasindex was set immediately(out of transactional control) in 7.0 and acruiring a lock for the pg_class tuple was very critical. Since 7.1 we are able to recreate indexes safely under postmaster and REINDEX doesn't set relhasindex to false for user tables. Though REINDEX deactivates the indexes of system tables the deactivation is done under transactional control and other backends never see the deactivated relhasindex. > > I have realized that this code is wrong anyway, because it doesn't > acquire ShareLock on the relation until far too late; all the setup > processing is done with no lock at all :-(. LockClassinfoForUpdate > provided a little bit of security against concurrent schema changes, > though not enough. > > Also, I'm now a little worried about whether concurrent index creations > will actually work. Both CREATE INDEX operations will try to update > the pg_class tuple to set relhasindex true. Yes but there's a big difference. It's at the end of the creation not at the beginning. Also note that UpdateStats() updates pg_class tuple in case of B-trees etc before updating relhasindex. I'm suspicios if we should update Stats under the transactional control. Since they use > simple_heap_update for that, the second one is likely to fail > because simple_heap_update doesn't handle concurrent updates. > > I think what we probably want is > > 1. Acquire ShareLock at the very start. > > 2. Check for indexes present but relhasindex = false, >if so complain. > > 3. Build the index. > > 4. Update pg_class tuple, being prepared for concurrent >updates (ie, do NOT use simple_heap_update here). > > I still don't see any value in LockClassinfoForUpdate, however. ISTM to rely on completely the lock for the corresponding relation is a little misplaced. For example ALTER TABLE OWNER doesn't acquire any lock on the table but it seems natural to me. UPDATE pg_class set .. doesn't acquire any lock on the correspoding relations of the target pg_class tuples but it seems natural to me, regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] schema support, was Package support for Postgres
On Tue, Oct 23, 2001 at 08:43:32AM -0700, Bill Studenmund wrote: > > And there's the fact that schemas were wanted for 7.2, and didn't happen. > Withouth external adgitation, will they happen for 7.3? Given the size of > the job, I understand why they didn't happen (the package changes so far > represent over 3 months of full-time programming). We've got some momentum > now, I'd say let's run with it. :-) > I feel much better about my unsucessfully attempt at a naive schema implementation, last Christmas holidays: I had no where _near_ 3 months of time in on that. ;-) Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed new create command, CREATE OPERATOR CLASS
Bill Studenmund <[EMAIL PROTECTED]> writes: > Do any of the access methods really support using non-binary operators? Whether they do today is not the question. The issue is whether they could --- and they certainly could. > Oh gross. I just looked at contrib/intarray, and it defines two entries in > pg_amop for amopstrategy number 20. They do happen to be commutators of > each other. Look for the @@ and ~~ operators. > Wait a second, how can you do that? Doesn't that violate > pg_amop_opc_strategy_index ? It sure does, but running the script shows that the second insert doesn't try to insert any rows. There's no entry in the temp table for ~~ because its left and right operands are not the types the SELECT/INTO is looking for. This is evidently a bug in the script. Oleg? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed new create command, CREATE OPERATOR CLASS
On Tue, 23 Oct 2001, Bill Studenmund wrote: > Here's the syntax I'd like to propose: > > CREATE OPERATOR CLASS [DEFAULT] FOR TYPE USING method> WITH AND Hmmm.. Teach me to read the docs. :-) There's no way to set opckeytype. So hwo about: CREATE OPERATOR CLASS [DEFAULT] FOR TYPE [AS ] USING WITH AND With AS present, the opckeytype column gets set to that type name's oid. > New keywords are "CLASS" (SQL99 reserved word) and "REPEATABLE" (SQL99 > non-reserved word, see below for usage). > > is the class's name, and is the type to be indexed. > is the assosciated access method from pg_am (btree, rtree, > hash, gist). > > The presence of [DEFAULT] indicates that this operator class shold be made > the default operator class for the type. > > is a comma-delimited list of operator specs. An > operator spec is either an operator or an operator followed by the keyword > "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck > should be set to true for this operator. Each item in this list will > generate an entry in pg_amop. I decided to change that to an operator followed by "needs_recheck" to indicate a recheck is needed. "needs_recheck" is not handled as a keyword, but as an IDENT which is examined at parse time. > is a comma-seperated list of functions used to > assist the index method. Each item in this list will generate an item in > pg_amproc. > > I agree that I think it is rare that anything will set "REPEATABLE", but > the point of this effort is to keep folks from mucking around with the > system tables manually, so we should support making any reasonable entry > in pg_amop. Take care, Bill ---(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] [GENERAL] CVS server stumbling?
Yes, I have seen this too today. > Is something amiss with the CVS server? I'm running an strace to watch > a cvs update and it's forbiddingly slow. It zooms along until it slams > into a brick wall for minutes, sometimes 10+ minutes, then it flys on. > > David > > > > ---(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) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] pgindent run
OK, I see my email got through to the list. Running pgindent now and will commit changes. -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Can't cast bigint to smallint?
Philip Warner <[EMAIL PROTECTED]> writes: > At 16:09 24/10/01 -0400, Tom Lane wrote: >> Huh? There's never been a cast from int8 to int2. I checked 7.0 and >> 7.1, they both complain as well: > Is this a policy decision, or just a case where noone has had a chance to > do it? Just a missing feature. The code additions would be trivial --- but would require an initdb to add the catalog entries. I'm loath to do it so close to beta. >> Where exactly is pg_dump failing? > The problem in in the code to re-enable triggers: > ...reltriggers = (select Count(*) > So perhaps this version now has Count returning a bigint rather than an int? Yes, that's what changed. Perhaps change the code to look like (select count(*)::integer ... On the other hand, that's no answer for people trying to load existing dump files into 7.2. Perhaps we should just do another catalog update and not worry about it. We just had one earlier this week, so I suppose another wouldn't make all that much difference. Comments? 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])
[HACKERS] "Triggered data change violation", once again
I have been looking at the way that deferred triggers slow down when the same row is updated multiple times within a transaction. The problem appears to be entirely due to calling deferredTriggerGetPreviousEvent() to find the trigger list entry for the previous update of the row: we do a linear search, so the behavior is roughly O(N^2) when there are N updated rows. The only reason we do this is to enforce the "triggered data change violation" restriction of the spec. However, I think we've misinterpreted the spec. The code prevents an RI referenced value from being changed more than once in a transaction, but what the spec actually says is thou shalt not change it more than once per *statement*. We have discussed this several times in the past and I think people have agreed that the current behavior is wrong, but nothing's been done about it. I think all we need to do to implement things correctly is to consider a previous event only if both xmin and cmin of the old tuple match the current xact & command IDs, rather than considering it on the basis of xmin alone. Aside from being correct, this will make a significant difference in performance. If we were doing it per spec then deferredTriggerGetPreviousEvent would never be called in typical operations, and so its speed wouldn't be an issue. Moreover, if we do it per spec then completed trigger event records could be removed from the trigger list at end of statement, rather than keeping them till end of transaction, which'd save memory space. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
Bill Studenmund writes: > > Our current CREATE OR REPLACE FUNCTION perserves the OID of the > > function. Is there similar functionality you need where a simple > > DROP (ignore the error), CREATE will not work? > > If possible, it's nice to not have commands whose error codes you ignore. > That way if you see an error, you know you need to do something about it. Technically, it's not an error, it's an "exception condition". This might make you feel better when consciously ignoring it. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Index of a table is not used (in any case)
mlw writes: > The "not using index" was very frustrating to understand. The stock answer, > "did you vacuum?" just isn't enough. There has to be some explanation (in the > FAQ or something) about the indexed key distribution in your data. Most "not using index" questions seem to be related to a misunderstanding of users to the effect that "if there is an index it must be used, not matter what the query", which is of course far from reality. Add to that the (related) category of inquiries from people that think the index ought to be used but don't have any actual timings to show, you have a lot of people that just need to be educated. Of course the question "did you vacuum" (better, did you analyze) is annoying, just as the requirement to analyze is annoying in the first place, but unless someone designs a better query planner it will have to do. The reason why we always ask that question first is that people invariantly have not analyzed. A seasoned developer can often tell from the EXPLAIN output whether ANALYZE has been done, but users cannot. Perhaps something can be done in this area, but I'm not exactly sure what. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Compiling on Solaris with Sun compiler
Lee Kindness writes: > After a simple './configure' on a stock Solaris 2.6 box the > compilation of interfaces/ecpg/lib/execute.c fails due to the macro > definition of 'gettext' to ''. This macro is invoked on the prototype > of gettext() in libintl.h (included via locale.h). This should be fixed now. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Proposed new create command, CREATE OPERATOR CLASS
I'd like to propose a new command, CREATE OPERATOR CLASS. Its purpose is to create a named operator class, so that you can create new types of index ops. Also, its inclusion would remove the section of the documentation where we tell people how to manually manipulate the system tables. Since schema support is going to change some of the details of the system tables in important ways, I think it's better to move away from manual updates. The command is basically an instrumentation of the documentation on how to add new operator classes. Here's the syntax I'd like to propose: CREATE OPERATOR CLASS [DEFAULT] FOR TYPE USING WITH AND New keywords are "CLASS" (SQL99 reserved word) and "REPEATABLE" (SQL99 non-reserved word, see below for usage). is the class's name, and is the type to be indexed. is the assosciated access method from pg_am (btree, rtree, hash, gist). The presence of [DEFAULT] indicates that this operator class shold be made the default operator class for the type. is a comma-delimited list of operator specs. An operator spec is either an operator or an operator followed by the keyword "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck should be set to true for this operator. Each item in this list will generate an entry in pg_amop. is a comma-seperated list of functions used to assist the index method. Each item in this list will generate an item in pg_amproc. I agree that I think it is rare that anything will set "REPEATABLE", but the point of this effort is to keep folks from mucking around with the system tables manually, so we should support making any reasonable entry in pg_amop. Here's an example based on the programmer's guide. We've created the type "complex", and have comparison functions complex_abs_lt, complex_abs_le, complex_abs_eq, complex_abs_gt, complex_abs_ge. Then let us have created operators "||<", "||<=", "||=", "||>", "||>=" based on them. We also have the complex_abs_cmp helper function. To create the operator class, the command would be: CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp; Among other things, complex_abs_ops would be the default operator class for the complex type after this command. An example using REPEATABLE would be: CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree with ||< REPEATABLE, ||<=, ||=, ||>=, ||> REPEATABLE and complex_abs_cmp; Note: I don't think the above command will create a correct operator class, it just shows how to add REPEATABLE. The alternative to "REPEATABLE" would be something like "hit_needs_recheck" after the operator. Suggestions? Thoughts? Take care, Bill ---(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] schema support, was Package support for Postgres
On Wed, 24 Oct 2001, Peter Eisentraut wrote: > Bill Studenmund writes: > > > So I am a "naive" programmer because I mention intent above? > > No. Sorry, that's the way it came across. As you've said that was not your intent, please disregard my response; I was responding to something you did not mean. > > So if we have INFORMATION_SCHEMA with the right vies in it, we are fine > > doing whatever we want. > > I think some interpretation of the SQL standard can be used to prove that > a new schema should not contain any objects. So you're going to have to > stick to the two predefined schemas to put the system catalogs in. Then > again, other interpretations may be used to prove other things. But to me > the intent of the standard is clear that system catalogs are meant to go > into the defintion schema, and I don't see a reason why this could not be > so. I had been thining that we could have the built-in objects (functions, types, operators, etc.) in whatever was the "default.master" package, but it looks like SQL99 doesn't like that. You're right that built-in things have to be in a different schema than user-added things. Section 10.4 contains text: ii) If RN contains a SN, then Case: 1) If SN is INFORMATION_SCHEMA, then the single candidate routine of RI is the built-in function identified by . Actually 4.24 is more exact. It defines a built-in function as a routine which is returned from teh query: SELECT DISTINCT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = INFORMATION_SCHEMA Actually, since we have to have an INFORMATION_SCHEMA, and "INFORMATION_SCHEMA" gets thrown around a lot, I think it'd be easiest to make "INFORMATION_SCHEMA" the schema containing built-in things. Otherwise (among other things) we have to replace DEFINTION_SCHEMA with INFORMATION_SCHEMA in the above-defined view (and in a lot of other places). Thoughts? > > I stil think we can't do that, since someone other than the schema owner > > can add a package to a schema. :-) Or at least that's the assumption I'm > > running on; we allow users other than PGUID to create functions (and > > operators and aggregates and types) in the default (whatever it will be > > called) schema, so why shouldn't they be allowed to add packages? > > Because SQL says so. All objects in a schema belong to the owner of the > schema. In simple setups you have one schema per user with identical > names. This has well-established use patterns in other SQL RDBMS. Then implimenting schemas will cause a backwards-incompatabile change regarding who can add/own functions (and operators and ..). Mainly because when we introduce schemas, all SQL transactions will have to be performed in the context of *some* schema. I think "DEFAULT" was the name you mentioned for when there was no schema matching the username. As "DEFAULT" (or whatever we call it) will be made by the PG super user (it will actually be added as part of initdb), then that means that only the super user will own functions. That's not how things are now, and imposing that on upgrading users will likely cause pain. Think about a dump/restore upgrade from 7.2 to 7.3. Right now users other than PGUID can own functions (and triggers, etc.). When you do the restore, though, since your dump had no schema support, it all goes into DEFAULT. Which will be owned by PGUID. So now we either have a schema with things owned by a user other than the schema owner, or we have a broken restore. Or we have to special case the DEFAULT schema. Which strikes me as a bad thing to do. For now, I'd suggest letting users other than a schema owner own things in a schema, and later on add controls over who can add things to a schema. Then when you do a "CREATE SCHEMA" command, you will implicitly be adding restrictions prohibiting someone other than the owner from adding things (including packages/subschemas). > I agree that this might not be what everyone would want, but it seems > extensible. However, I feel we're trying to design too many things at > once. Let's do schemas first the way they're in the SQL standard, and > then we can try to tack on ownership or subschemas or package issues. Well, the packages changes can easily be turned into schema support for functions and aggregates, so we are part way there. Also, the packages changes illustrate how to make system-wide internal schema changes of the type adding SQL schemas will need. Plus, packages as they are now are useful w/o schema support. And there's the fact that schemas were wanted for 7.2, and didn't happen. Withouth external adgitation, will they happen for 7.3? Given the size of the job, I understand why they didn't happen (the package changes so far represent over 3 months of full-time programming). We've got some momentum now, I'd say let's run with it. :-) Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTEC
Re: [HACKERS] join instruction
"Zenon" <[EMAIL PROTECTED]> writes: > left outer join intructionworking or not on POSTGRES 7 It works in 7.1 or later. 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] schema support, was Package support for Postgres
Bill Studenmund writes: > > > Why? Operators are used differently than functions. > > > > I don't think so. Operators are a syntacticaly convenience for functions. > > That's what they always have been and that's what they should stay. > > How does what you say disagree with what I said? > > Operators certainly have a lot more structure to them than a function call > does. That's why you give the restriction and join functions, and you hand > them commutation and negation operators. These are just hints to the optimizer; they don't affect the invocation interface. > So I am a "naive" programmer because I mention intent above? No. > So if we have INFORMATION_SCHEMA with the right vies in it, we are fine > doing whatever we want. I think some interpretation of the SQL standard can be used to prove that a new schema should not contain any objects. So you're going to have to stick to the two predefined schemas to put the system catalogs in. Then again, other interpretations may be used to prove other things. But to me the intent of the standard is clear that system catalogs are meant to go into the defintion schema, and I don't see a reason why this could not be so. > > Blech, I meant "you can replace the owner column with the schema column". > > That's actually what I thought you said. :-) > > I stil think we can't do that, since someone other than the schema owner > can add a package to a schema. :-) Or at least that's the assumption I'm > running on; we allow users other than PGUID to create functions (and > operators and aggregates and types) in the default (whatever it will be > called) schema, so why shouldn't they be allowed to add packages? Because SQL says so. All objects in a schema belong to the owner of the schema. In simple setups you have one schema per user with identical names. This has well-established use patterns in other SQL RDBMS. I agree that this might not be what everyone would want, but it seems extensible. However, I feel we're trying to design too many things at once. Let's do schemas first the way they're in the SQL standard, and then we can try to tack on ownership or subschemas or package issues. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Can't cast bigint to smallint?
Philip Warner <[EMAIL PROTECTED]> writes: > The minor featurette seems to have crept into current sources; it is > probably the cause of pg_dump being unable to reinstate disabled triggers. Huh? There's never been a cast from int8 to int2. I checked 7.0 and 7.1, they both complain as well: test71=# select 8::int8::int2; ERROR: Cannot cast type 'int8' to 'int2' Where exactly is pg_dump failing? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] storing binary data
Jason Orendorff writes: > Hi. I was surprised to discover today that postgres's > character types don't support zero bytes. That is, > Postgres isn't 8-bit clean. Why is that? PostgreSQL is 8-bit clean. The character types don't support zero bytes because the character types store characters, not bytes. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Using an SMP machine to make multiple indices on the same
"Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > In the end, I changed DefineIndex() to not call IndexesAreActive(). I saw that. But is it a good solution? If someone has deactivated indexes on a user table (ie turned off relhasindex), then creating a new index would activate them again, which would probably be bad. I have realized that this code is wrong anyway, because it doesn't acquire ShareLock on the relation until far too late; all the setup processing is done with no lock at all :-(. LockClassinfoForUpdate provided a little bit of security against concurrent schema changes, though not enough. Also, I'm now a little worried about whether concurrent index creations will actually work. Both CREATE INDEX operations will try to update the pg_class tuple to set relhasindex true. Since they use simple_heap_update for that, the second one is likely to fail because simple_heap_update doesn't handle concurrent updates. I think what we probably want is 1. Acquire ShareLock at the very start. 2. Check for indexes present but relhasindex = false, if so complain. 3. Build the index. 4. Update pg_class tuple, being prepared for concurrent updates (ie, do NOT use simple_heap_update here). I still don't see any value in LockClassinfoForUpdate, however. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] pgindent run
I have been asked to run pgindent in preparation for beta starting tomorrow. In this run, I will also reformat the jdbc files as agreed to by the jdbc list. I don't have much time to wait before starting the pgindent run. I hope people don't have outstanding patches sitting around. -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] copying a large object?
"mario" <[EMAIL PROTECTED]> writes: > And another question regarding large objects, as I see the objects are > organized in units of 2048 bytes each. Can I somehow set this to a higher > value like 8k or 32k (I use 32k pages). Then you've already got larger units, because the code is #define LOBLKSIZE(BLCKSZ / 4) I don't believe it'd be a good idea to try to make it larger than that, though you're free to experiment... 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] Index of a table is not used (in any case)
mlw <[EMAIL PROTECTED]> writes: > ... Postgres' statistics are pretty poor too, a relative few very > populous entries in a table will make it virtually impossible for the > cost based optimizer (CBO) to use an index. Have you looked at development sources lately? 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] PQconnectStart() and -EINTR
David Ford <[EMAIL PROTECTED]> writes: > I've got a bit of a problem. I added a fast SIGALRM handler in my > project to do various maintenance and this broke PQconnectStart(). It'd probably be reasonable to just retry the connect() call if it fails with EINTR. If that works for you, send a patch... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index on wide column
Antonio Sergio de Mello e Souza <[EMAIL PROTECTED]> writes: > I need to perform a tree traversal on a big table (millions of rows). > To avoid recursive queries, one for each non-leaf node, this table has, > in addition to its 70 columns, a VARCHAR(3) column that is used > exclusively to sort the rows with the required order. The actual content > length in that column is expected to be, on average, much less than the > declared limit and the text will be composed of digits and letters only. Are there any entries that will actually approach 3 chars? > Please, are there any restrictions about using such a wide column to > order a table? No. > Can an index on that column help? btree indexes can't cope with index entries wider than 1/3 page, so you'd probably find that building a btree index fails, if there really are 30k-wide entries in the column. This limit is squishy because the entries can be TOAST-compressed, but you're not likely to get 12:1 compression. You could improve matters by increasing BLXKSZ to 32K, however; then you'd only need 3:1 compression, which might work depending on how repetitive the column data is. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] autoconf taking forever?
bpalmer <[EMAIL PROTECTED]> writes: > I am running 2.13 (even on a clean checkout of 7.1.3) and the autoconf > takes forever. However, m4 is the process that's running forever, so I > have no doubs that the problem is there. What version do you use that > works? GNU m4 ... the version I have here is $ m4 --version GNU m4 1.4 regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] join instruction
HELP left outer join intructionworking or not on POSTGRES 7 Zenon Karol ---(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] storing binary data
Joe Conway <[EMAIL PROTECTED]> writes: > I'll take a shot at improving the documentation for bytea. I'm hoping > documentation patches are accepted during beta though ;-) Of course. The only limitation we place during beta is "no new features added". I plan to spend a good deal of time on the docs during beta 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
[HACKERS] Make a copy of a large object
I'm working on an application where it is necessary to make copies of large
objects, and now I wonder if it is safe
to use this (symbolic, somewhat PHP like) code. Say I've a LOB with OID=1234
$oid = db_exec("select lo_create()")
db_exec("delete from pg_largeobject where loid=$oid")
db_exec("insert into pg_largeobject select $oid, pageno, data from
pg_largeobject where loid=1234")
is this a safe way to accomplish this?
And another question regarding large objects, as I see the objects are
organized in units of 2048 bytes each. Can I somehow set this to a higher
value like 8k or 32k (I use 32k pages).
I'm using the latest 7.2 cvs version.
Hope someone of you can help me, thanks!
Best regards,
Mario Weilguni
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] check for disk space
Hi all, I was just looking for the code which checks for the memory available on machine before writing the data. Any related information will be appreciated. Thanks, KKG _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(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] PQconnectStart() and -EINTR
David Ford <[EMAIL PROTECTED]> writes: > I've got a bit of a problem. I added a fast SIGALRM handler in my project to > do various maintenance and this broke PQconnectStart(). > > > Oct 23 21:56:36 james BlueList: connectDBStart() -- connect() failed: > Interrupted system call ^IIs the postmaster running (with -i) at > 'archives.blue-labs.org' ^Iand accepting connections on TCP/IP port 5432? > > > PQstatus() returns CONNECTION_BAD, how can I reenter the connection cycle or > delay, more like, how do I differentiate between an actual failure to connect > and an interruption by signal? My alarm timer happens much too frequently for > this code to make a connection and unfortunately I can't disable the alarm > because it's used for bean counting and other maintenance. Sounds like something in libpq needs to check for EINTR and reissue the connect() call (or select()/poll() if it's a nonblocking connect()). -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(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] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001, Bruce Momjian wrote: > > If possible, it's nice to not have commands whose error codes you ignore. > > That way if you see an error, you know you need to do something about it. > > Folks, is this a valid reason for adding OR REPLACE to all CREATE object > commands? Sounds good to me. :-) Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Using an SMP machine to make multiple indices on the same
> -Original Message- > From: Hiroshi Inoue > > Tom Lane wrote: > > > > Martin Weinberg <[EMAIL PROTECTED]> writes: > > > Yes, I understand locking the table, but empirically, two index > > > creations will not run simultaneously on the same table. > > > > Hmm, on trying it you are right. The second index creation blocks here: > > > > #6 0x1718e0 in XactLockTableWait (xid=17334) at lmgr.c:344 > > #7 0x9e530 in heap_mark4update (relation=0xc1be62f8, tuple=0x7b03b7f0, > > buffer=0x7b03b828) at heapam.c:1686 > > #8 0xcb410 in LockClassinfoForUpdate (relid=387785, rtup=0x7b03b7f0, > > buffer=0x7b03b828, confirmCommitted=0 '\000') at index.c:1131 > > #9 0xcb534 in IndexesAreActive (relid=387785, > confirmCommitted=1 '\001') > > at index.c:1176 > > #10 0xf0f04 in DefineIndex (heapRelationName=0x400aab20 "tenk1", > > indexRelationName=0x400aab00 "anotherj", > accessMethodName=0x59f48 "btree", > > attributeList=0x400aab80, unique=0, primary=0, predicate=0x0, > > rangetable=0x0) at indexcmds.c:133 > > #11 0x17e118 in ProcessUtility (parsetree=0x400aaba0, dest=Remote) > > at utility.c:905 > > > > Essentially it's trying to do a SELECT FOR UPDATE on the pg_class tuple > > of the relation before it starts building the index. > > > > I have opined before that LockClassinfoForUpdate is a mistake that > > shouldn't exist at all, since acquiring the proper lock on the relation > > ought to be sufficient. > > As I've already mentioned many times I never agree with you. > > > I see no need for locking the pg_class tuple, > > and certainly none for doing so at the beginning of the operation rather > > than the end. > > > > Hiroshi, I think you defended it last time; any comments? > > Hmm the excluive row level lock by FOR UPDATE is too strong > in this case. OK I would change IndexesAreActive() to not > acquire a lock on the pg_class tuple for user tables because > reindex doesn't need to handle relhasindex for user tables > since 7.1. In the end, I changed DefineIndex() to not call IndexesAreActive(). regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Can't cast bigint to smallint?
The minor featurette seems to have crept into current sources; it is probably the cause of pg_dump being unable to reinstate disabled triggers. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] between?
On Wed, 24 Oct 2001, Thomas Lockhart wrote: > > It seems Sybase has dropped the BETWEEN search condition. I thought > > it was part of SQL92, has it been dropped from the spec since then or > > wasn't it ever in there? > > It is documented in every SQL book I have and I see it in our SQL99 > docs. Are you *sure* Sybase dropped it? If so, then it presumably is > mentioned in the release notes. What do they say about it?? One of the guys here said he saw it in the release notes, but I just tried it and it worked. I'm gonna have to find what he was looking at. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Index on wide column
Hi all, I need to perform a tree traversal on a big table (millions of rows). To avoid recursive queries, one for each non-leaf node, this table has, in addition to its 70 columns, a VARCHAR(3) column that is used exclusively to sort the rows with the required order. The actual content length in that column is expected to be, on average, much less than the declared limit and the text will be composed of digits and letters only. Please, are there any restrictions about using such a wide column to order a table? Can an index on that column help? I'm running PostgreSQL 7.1.2, on Linux 2.2.16, compiled with options: --prefix=/usr/local/pgsql --enable-locale --enable-multibyte Regards, Antonio Sergio ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] check disk space
Hi all, I was just looking for the code which checks for the memory available on machine before writing the data. Any related information will be appreciated. Thanks, KKG _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] storing binary data
... > I'll take a shot at improving the documentation for bytea. I'm hoping > documentation patches are accepted during beta though ;-) Always. At least up until a week or so before release, when we need to firm up the docs and work on final cleanup etc. There are several announcements leading up to that point, so it will not be a suprise. - Thomas ---(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] between?
> It seems Sybase has dropped the BETWEEN search condition. I thought > it was part of SQL92, has it been dropped from the spec since then or > wasn't it ever in there? It is documented in every SQL book I have and I see it in our SQL99 docs. Are you *sure* Sybase dropped it? If so, then it presumably is mentioned in the release notes. What do they say about it?? - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index of a table is not used (in any case)
Zeugswetter Andreas SB SD wrote: > > > > > *very* slow, due to seq scan on > > > > 20 million entries, which is a test setup up to now) > > > > > > Perennial first question: did you VACUUM ANALYZE? > > > > Can there, or could there, be a notion of "rule based" optimization of > > queries in PostgreSQL? The "not using index" problem is probably the > most > > common and most misunderstood problem. > > There is a (sort of) rule based behavior in PostgreSQL, > the down side of the current implementation is, that certain > other commands than ANALYZE (e.g. "create index") partly update > optimizer statistics. This is bad behavior, since then only part > of the statistics are accurate. Statistics always have to be seen > in context to other table's and other index'es statistics. > > Thus, currently the rule based optimizer only works if you create > the indexes on empty tables (before loading data), which obviously > has downsides. Else you have no choice but to ANALYZE frequently. > > I have tried hard to fight for this pseudo rule based behavior, > but was only partly successful in convincing core. My opinion is, > that (unless runtime statistics are kept) no other command than > ANALYZE should be allowed to touch optimizer relevant statistics > (maybe unless explicitly told to). Perhaps there could be an extension to ANALYZE, i.e. ANALYZE RULEBASED tablename that would restore or recalculate the state that a table would be if all indexes were created from scratch? The "not using index" was very frustrating to understand. The stock answer, "did you vacuum?" just isn't enough. There has to be some explanation (in the FAQ or something) about the indexed key distribution in your data. Postgres' statistics are pretty poor too, a relative few very populous entries in a table will make it virtually impossible for the cost based optimizer (CBO) to use an index. At my site we have lots of tables that have many duplicate items in an index. It is a music based site and has a huge amount of "Various Artists" entries. No matter what we do, there is NO way to get Postgres to use the index from the query alone. We have over 20 thousand artists, but 5 "Various Artists" or "Soundtrack" entries change the statistics so much that they exclude an index scan. We have to run the system with sequential scan disabled. Running with seq disabled eliminates the usefulness of the CBO because when it is a justified table scan, it does an index scan. I have approached this windmill before and a bit regretful at bringing it up again, but it is important, very important. There needs to be a way to direct the optimizer about how to optimize the query. Using "set foo=bar" prior to a query is not acceptable. Web sites use persistent connections to the databases and since "set" can not be restored, you override global settings for the session, or have to code, in the web page, the proper default setting. The result is either that different web processes will behave differently depending on the order in which they execute queries, or you have to have your DBA write web pages. A syntax like: select * from table where /* enable_seqscan = false */ key = 'value'; Would be great in that you could tune the optimizer as long as the settings were for the clause directly following the directive, without affecting the state of the session or transaction. For instance: select id from t1, t2 where /* enable_seqscan = false */ t1.key = 'value' and t2.key = 'test' and t1.id = t2.id; The where "t1.key = 'value'" condition would be prohibited from using a sequntial scan, while the "t2.key = 'test'" would use it if it made sense. Is this possible? ---(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] between?
It seems Sybase has dropped the BETWEEN search condition. I thought it was part of SQL92, has it been dropped from the spec since then or wasn't it ever in there? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] copying a large object?
I'm working on an application where it is necessary to make copies of large
objects, and now I wonder if it is safe
to use this (symbolic, somewhat PHP like) code. Say I've a LOB with OID=1234
$oid = db_exec("select lo_create()")
db_exec("delete from pg_largeobject where loid=$oid")
db_exec("insert into pg_largeobject select $oid, pageno, data from
pg_largeobject where loid=1234")
is this a safe way to accomplish this?
And another question regarding large objects, as I see the objects are
organized in units of 2048 bytes each. Can I somehow set this to a higher
value like 8k or 32k (I use 32k pages).
I'm using the latest 7.2 cvs version.
Hope someone of you can help me, thanks!
Best regards,
Mario Weilguni
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index of a table is not used (in any case)
> > > *very* slow, due to seq scan on > > > 20 million entries, which is a test setup up to now) > > > > Perennial first question: did you VACUUM ANALYZE? > > Can there, or could there, be a notion of "rule based" optimization of > queries in PostgreSQL? The "not using index" problem is probably the most > common and most misunderstood problem. There is a (sort of) rule based behavior in PostgreSQL, the down side of the current implementation is, that certain other commands than ANALYZE (e.g. "create index") partly update optimizer statistics. This is bad behavior, since then only part of the statistics are accurate. Statistics always have to be seen in context to other table's and other index'es statistics. Thus, currently the rule based optimizer only works if you create the indexes on empty tables (before loading data), which obviously has downsides. Else you have no choice but to ANALYZE frequently. I have tried hard to fight for this pseudo rule based behavior, but was only partly successful in convincing core. My opinion is, that (unless runtime statistics are kept) no other command than ANALYZE should be allowed to touch optimizer relevant statistics (maybe unless explicitly told to). Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Using an SMP machine to make multiple indices on the same
Tom Lane wrote: > > Martin Weinberg <[EMAIL PROTECTED]> writes: > > Yes, I understand locking the table, but empirically, two index > > creations will not run simultaneously on the same table. > > Hmm, on trying it you are right. The second index creation blocks here: > > #6 0x1718e0 in XactLockTableWait (xid=17334) at lmgr.c:344 > #7 0x9e530 in heap_mark4update (relation=0xc1be62f8, tuple=0x7b03b7f0, > buffer=0x7b03b828) at heapam.c:1686 > #8 0xcb410 in LockClassinfoForUpdate (relid=387785, rtup=0x7b03b7f0, > buffer=0x7b03b828, confirmCommitted=0 '\000') at index.c:1131 > #9 0xcb534 in IndexesAreActive (relid=387785, confirmCommitted=1 '\001') > at index.c:1176 > #10 0xf0f04 in DefineIndex (heapRelationName=0x400aab20 "tenk1", > indexRelationName=0x400aab00 "anotherj", accessMethodName=0x59f48 "btree", > attributeList=0x400aab80, unique=0, primary=0, predicate=0x0, > rangetable=0x0) at indexcmds.c:133 > #11 0x17e118 in ProcessUtility (parsetree=0x400aaba0, dest=Remote) > at utility.c:905 > > Essentially it's trying to do a SELECT FOR UPDATE on the pg_class tuple > of the relation before it starts building the index. > > I have opined before that LockClassinfoForUpdate is a mistake that > shouldn't exist at all, since acquiring the proper lock on the relation > ought to be sufficient. As I've already mentioned many times I never agree with you. > I see no need for locking the pg_class tuple, > and certainly none for doing so at the beginning of the operation rather > than the end. > > Hiroshi, I think you defended it last time; any comments? Hmm the excluive row level lock by FOR UPDATE is too strong in this case. OK I would change IndexesAreActive() to not acquire a lock on the pg_class tuple for user tables because reindex doesn't need to handle relhasindex for user tables since 7.1. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Database corruption?
> >> Um, Vadim? Still of the opinion that elog(STOP) is a good > >> idea here? That's two people now for whom that decision has > >> turned localized corruption into complete database failure. > >> I don't think it's a good tradeoff. > > > One is able to use pg_resetxlog so I don't see point in > > removing elog(STOP) there. What do you think? > > Well, pg_resetxlog would get around the symptom, but at the cost of > possibly losing updates that are further along in the xlog than the > update for the corrupted page. (I'm assuming that the problem here > is a page with a corrupt LSN.) I think it's better to treat flush On restart, entire content of all modified after last checkpoint pages should be restored from WAL. In Denis case it looks like newly allocated for update page was somehow corrupted before heapam.c:2235 (7.1.2 src) and so there was no XLOG_HEAP_INIT_PAGE flag in WAL record => page content was not initialized on restart. Denis reported system crash - very likely due to memory problem. > request past end of log as a DEBUG or NOTICE condition and keep going. > Sure, it indicates badness somewhere, but we should try to have some > robustness in the face of that badness. I do not see any reason why > XLOG has to declare defeat and go home because of this condition. Ok - what about setting some flag there on restart and abort restart after all records from WAL applied? So DBA will have choice either to run pg_resetxlog after that and try to dump data or restore from old backup. I still object just NOTICE there - easy to miss it. And in normal processing mode I'd leave elog(STOP) there. Vadim P.S. Further discussions will be in hackers-list, sorry. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] storing binary data
>> + What I really need is a binary *short* object type. >> I have heard rumors of a legendary "bytea" type that might >> help me, but it doesn't appear to be documented anywhere, >> so I hesitate to use it. >> > > It's real and it's not going away. It is pretty poorly documented > and doesn't have a wide variety of functions ... but hey, you can help > improve that situation. This is an open source project after all ;-) > > regards, tom lane I'll take a shot at improving the documentation for bytea. I'm hoping documentation patches are accepted during beta though ;-) Also, FWIW, 7.2 includes bytea support for LIKE, NOT LIKE, LIKE ESCAPE, ||, trim(), substring(), position(), length(), indexing, and various comparators. Joe ---(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] PQconnectStart() and -EINTR
I've got a bit of a problem. I added a fast SIGALRM handler in my
project to do various maintenance and this broke PQconnectStart().
Oct 23 21:56:36 james BlueList: connectDBStart() -- connect() failed:
Interrupted system call ^IIs the postmaster running (with -i) at
'archives.blue-labs.org' ^Iand accepting connections on TCP/IP port 5432?
PQstatus() returns CONNECTION_BAD, how can I reenter the connection
cycle or delay, more like, how do I differentiate between an actual
failure to connect and an interruption by signal? My alarm timer
happens much too frequently for this code to make a connection and
unfortunately I can't disable the alarm because it's used for bean
counting and other maintenance.
Thanks,
David
Code snippet:
...
/*
* play some tricks now, use async connect mode to find if the server
* is alive. once we've figured that out, disconnect and immediately
* reconnect in blocking mode. this mitigates the annoying hangs from
* using PQconnectdb which has no support for a timeout.
*/
conn=PQconnectStart(cstr);
if(!conn) {
dlog(_LOG_debug, "SQL conn is NULL, aborting");
return NULL;
}
do {
c++;
pgstat=PQstatus(conn);
switch (pgstat) {
case CONNECTION_STARTED:
dlog(_LOG_debug, "Connecting to SQL server...");
break;
case CONNECTION_MADE:
case CONNECTION_OK:
dlog(_LOG_debug, "Connected to SQL server in asynchronous
mode...");
break;
case CONNECTION_BAD:
dlog(_LOG_debug, PQerrorMessage(conn));
if(conn)
PQfinish(conn);
dlog(_LOG_warning, "failed to connect to server");
return NULL;
break;
default:
dlog(_LOG_debug, "pg conx state = %i", pgstat);
break;
}
if(pgstat==CONNECTION_MADE||CONNECTION_OK)
break;
if(c>15) {
if(conn)
PQfinish(conn);
dlog(_LOG_warning, "failed to connect to server, timed out");
return NULL;
}
req.tv_sec=1;
req.tv_nsec=0;
sleep(&req);
} while(1);
/*
* close it and reopen it in normal blocking mode
*/
PQfinish(conn);
conn=PQconnectdb(cstr);
...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] LOCK ROW SHARE MODE
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > In the LOCK TABLE docs it documents the SELECT...FOR UPDATE as follows: > ROW SHARE MODE > Note: Automatically acquired by SELECT...FOR UPDATE. While it is a shared > lock, may be upgraded later to a ROW EXCLUSIVE lock. > Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock modes. > However, if I begin a transaction in one window and SELECT...FOR UPDATE a > row, then begin a transaction in another window and SELECT ... FOR UPDATE > the same row, the second SELECT..FOR UPDATE blocks until the first > transactions is committed or rolled back. > So, shouldn't this mean that the ROW SHARE mode should in fact be documented > to conflict with itself??? And with this behaviour is it really a shared > lock? I don't get it! ROW SHARE is a table-level lock mode. SELECT FOR UPDATE grabs ROW SHARE lock on the table, *plus* an exclusive-write lock on the selected row(s). The latter is what's conflicting for you. I think the code is okay, but the documentation could use some work... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
Bruce Momjian wrote: > > > > > Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These > > > > features are needed for pgAdmin II (we could also provide a patch for > > > > PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for > > > > pseudo-modification solutions (which is definitely not a good solution). > > > > > > Our current CREATE OR REPLACE FUNCTION perserves the OID of the > > > function. Is there similar functionality you need where a simple > > > DROP (ignore the error), CREATE will not work? > > > > If possible, it's nice to not have commands whose error codes you ignore. > > That way if you see an error, you know you need to do something about it. > > Folks, is this a valid reason for adding OR REPLACE to all CREATE object > commands? Well, Oracle has CREATE OR REPLACE for: Views Functions Procedures Triggers Types Packages but not for (at least 8.0.5): Tables Indexes Sequences At first glance, I'm not sure why Oracle doesn't allow for the replacement of the non-"compiled" objects. Perhaps the complexities involved in enforcing RI was too much. The *major* advantage to allowing a REPLACE in Oracle is to preserve permissions granted to various users and groups (aka ROLES). Oracle automatically recompiles views, functions, procedures, etc. if their underlying dependencies change: SQL> CREATE TABLE employees (key integer, salary float); Table created. SQL> CREATE VIEW salaries AS SELECT * FROM employees WHERE salary < 15000; View created. SQL> SELECT * FROM salaries; no rows selected SQL> DROP TABLE employees; Table dropped. SQL> SELECT * FROM salaries; SELECT * FROM salaries * ERROR at line 1: ORA-04063: view "MASCARM.SALARIES" has errors SQL> CREATE TABLE employees (key integer, salary float); Table created. SQL> SELECT * FROM salaries; no rows selected So it seems to me that the major reason is to preserve GRANT/REVOKE privileges issues against the object in question. FWIW, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
> Not until we do the necessary legwork. I spent a good deal of time over > the past week making the various PL modules react to replacement of > pg_proc entries by CREATE OR REPLACE FUNCTION (cf. complaint from Peter > a week or so back). CREATE OR REPLACE VIEW implies updating cached > query plans, and I'm not sure what CREATE OR REPLACE TRIGGER implies. > But I am pretty sure it's not a trivial question. > > In short: put it on the todo list, but note that there are some > implications... That's all I needed to know. -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] To Postgres Devs : Wouldn't changing the select limit
> Bruce Momjian writes: > > > I recommend tips when they are one line in length, have a high > > probability of being accurate, and are common mistakes. Anything longer > > and we should point to a specific section in the docs. > > I would put "when porting from MySQL" into that category. I would too except when we implement the feature backwards and then remove it. -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001, Bruce Momjian wrote: > > Dear all, > > > > Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in > > PostgreSQL 7.2? Probably not, it's rather late in the cycle (isn't beta imminent?). Oh, I'd vote for "OR REPLACE" as there's already an opt_or_replace non-terminal in the parser. Adding an optional "OR DROP" might displease yacc, and also follows in the same vein as what we have for CREATE FUNCTION. > > Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These > > features are needed for pgAdmin II (we could also provide a patch for > > PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for > > pseudo-modification solutions (which is definitely not a good solution). > > Our current CREATE OR REPLACE FUNCTION perserves the OID of the > function. Is there similar functionality you need where a simple > DROP (ignore the error), CREATE will not work? If possible, it's nice to not have commands whose error codes you ignore. That way if you see an error, you know you need to do something about it. Take care, Bill ---(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] CREATE OR REPLACE VIEW/TRIGGER
Bruce Momjian <[EMAIL PROTECTED]> writes: > Our current CREATE OR REPLACE FUNCTION perserves the OID of the > function. Is there similar functionality you need where a simple > DROP (ignore the error), CREATE will not work? >> >> If possible, it's nice to not have commands whose error codes you ignore. >> That way if you see an error, you know you need to do something about it. > Folks, is this a valid reason for adding OR REPLACE to all CREATE object > commands? Not until we do the necessary legwork. I spent a good deal of time over the past week making the various PL modules react to replacement of pg_proc entries by CREATE OR REPLACE FUNCTION (cf. complaint from Peter a week or so back). CREATE OR REPLACE VIEW implies updating cached query plans, and I'm not sure what CREATE OR REPLACE TRIGGER implies. But I am pretty sure it's not a trivial question. In short: put it on the todo list, but note that there are some implications... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
