Re: [HACKERS] failed: make install prefix=/foo/bar
On Mon, Aug 06, 2001 at 06:00:40PM +0200, Peter Eisentraut wrote: > Karel Zak writes: > > > $ make install prefix=/home/PG_DEVEL/X/ > > > cp: cannot create regular file > > /usr/lib/postgresql/share/locale/de/LC_MESSAGES/# > > I have fixed this. Note, however, that this probably won't do what you > want anyway, because the compiled-in path references will still use the > prefix you specified to configure. The only uses for "make install > prefix=elsewhere" are some rather specific circumstances where the > location specified for installation will be mapped the location used at > use time, e.g. with symlinks (e.g., using GNU Stow), or with an AFS file > system. If you simply change your mind about the installation prefix you > need to make distclean first and rebuild everything. Yes, you are right. But "make install prefix=..." can be used for package building if you want install all to some temp directory and compress it. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: Re: Notes about int8 sequences
At 07:02 PM 06-08-2001 -0400, Tom Lane wrote: >pseudo-type should generate an int8 instead of int4 column. On >compatibility grounds, it might be better to leave it generating int4, >and invent a second pseudo-type SERIAL8 that is just the same except >for making an int8 column. I'm more worried about changing the datatype >of a user column than I am about changing the output type of nextval(), >so I'd be sort of inclined to have two SERIAL types even if we change >nextval() to int8. Thoughts? serial8 sounds ok to me. I use currval. Cheerio, Link. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] To be 7.1.3 or not to be 7.1.3?
Hi guys, Just wondering if we are going to release a version 7.1.3 or not? Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Planned change in initdb-time OID allocation
Mike Mascari <[EMAIL PROTECTED]> writes: > I was wondering in the past if it would simply be better to have an > .SQL script which is submitted to the template1 database at > post-initdb time with COMMENT ON statements to document built-in > types, functions, system relations, etc. The nice thing about the way it's done now is that the descriptions sit right next to the defining commands in the catalog/*.h files. Having to maintain a separate script file doesn't seem like a win. Almost certainly, the descriptions would be poorly maintained --- ye olde out-of-sight, out-of-mind problem. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Planned change in initdb-time OID allocation
Tom Lane wrote: > > Presently, we have hand-assigned OIDs running up to about 1950 > (according to the unused_oids script). The range up to 16K is reserved > for hand-assigned OIDs, and the OID counter starts at 16384 at initdb. > A peek in pg_database shows datlastsysoid = 18931 in current sources, so > a total of about 2550 OIDs are machine-assigned during initdb. ... > > There are a couple of possible ways to attack this, but the one that > seems best to me is to allow genbki.sh itself to assign OIDs to DATA > lines that don't have one. It could start at, say, 1, staying well > clear of both the hand-assigned OIDs and the ones that will be generated > on-the-fly by the backend. Then it would know the correct OID to > associate with any DESCR macro. > > Comments, objections? I was wondering in the past if it would simply be better to have an .SQL script which is submitted to the template1 database at post-initdb time with COMMENT ON statements to document built-in types, functions, system relations, etc. I should, after all, be able to issue a "\d+ pg_class" in psql and get a description of the columns. The .SQL script could potentially contain COMMENT ON statements localized to the language in which the database is installed, but it wouldn't care what OIDs were assigned (if any) to the various objects being documented. Mike Mascari [EMAIL PROTECTED] ---(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] Planned change in initdb-time OID allocation
> What bothers me about this scheme is that genbki.sh can only create > pg_description entries for objects with hand-assigned OIDs. It > processes the DESCR() macro by emitting the OID of the last DATA macro, > along with the description text, into a data file that's eventually > copied into pg_description. But if there's no hand-assigned OID it has > to punt --- it doesn't know what OID the object will have. This means > we can't assign initdb-time descriptions to aggregate functions (for > example), since we don't give them hand-assigned OIDs. This was a known problem when I implemented pg_description. Your solution sounds good. -- 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] FTI contrib
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > The latest patch we submitted to the fulltextindex module improved lots of > things but something we could not get to work was the apparently correct use > of the PG_GETARG* macros, etc. > Whenever we used these macros, we always got 0 or NULL as our values. So, > we reverted to the trigger->tgargs array. Trigger functions don't get their arguments the normal way. The GETARG macros don't know anything about trigger arguments... so the original code was correct as it was. I haven't had time to look at your patch, but maybe I should go do that. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Planned change in initdb-time OID allocation
Presently, we have hand-assigned OIDs running up to about 1950
(according to the unused_oids script). The range up to 16K is reserved
for hand-assigned OIDs, and the OID counter starts at 16384 at initdb.
A peek in pg_database shows datlastsysoid = 18931 in current sources, so
a total of about 2550 OIDs are machine-assigned during initdb. The bulk
of these last are in pg_attribute (827 rows) and pg_description (1221
rows).
All the hand-assigned OIDs are established by lines like
DATA(insert OID = 23 ( int4 PGUID 4 10 t b t \054 0 0 int4in int4out int4in
int4out i p _null_ ));
in catalog include files. We also have lines like
DATA(insert OID = 0 ( avg PGUID int4_accumnumeric_avg 23
1231 1700 "{0,0,0}" ));
which do not assign a specific OID to the row --- instead the row will
receive a machine-generated OID (at or above 16k) when it is loaded.
What bothers me about this scheme is that genbki.sh can only create
pg_description entries for objects with hand-assigned OIDs. It
processes the DESCR() macro by emitting the OID of the last DATA macro,
along with the description text, into a data file that's eventually
copied into pg_description. But if there's no hand-assigned OID it has
to punt --- it doesn't know what OID the object will have. This means
we can't assign initdb-time descriptions to aggregate functions (for
example), since we don't give them hand-assigned OIDs.
There are a couple of possible ways to attack this, but the one that
seems best to me is to allow genbki.sh itself to assign OIDs to DATA
lines that don't have one. It could start at, say, 1, staying well
clear of both the hand-assigned OIDs and the ones that will be generated
on-the-fly by the backend. Then it would know the correct OID to
associate with any DESCR macro.
Comments, objections?
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] Re: Notes about int8 sequences
Thomas Lockhart <[EMAIL PROTECTED]> writes: > Hmm. How far away are we from doing SERIAL in a way that you find more > acceptable than the current technique of mucking around internally with > sequences and default values? I'd say "won't happen for 7.2", whereas it seems like changing sequences to use int8 is something that could get done this month. A true SERIAL type is something that we should think about along with per-table OID generation, since they have essentially the same requirement: a lightweight sequence generator. Our present approach of a one-row table to represent a sequence is not sufficiently lightweight, IMHO, either from an implementation or a conceptual viewpoint. (In particular, it requires each sequence to have a unique name taken from the table namespace, whereas for both table OIDs and serial columns I think we'd much prefer the sequences to be anonymous ... or at least in a different namespace. But how do we change that without breaking a lot of existing application code?) Offhand I don't see that adding a SERIAL8 type to the mix (or just changing SERIAL to be int8) would make this any harder or easier. The underlying implementation is exposed just as much as before, but not any more so. > If we do blast ahead with a SERIAL8, then we should consider > implementing a SERIAL4 and then aliasing SERIAL to one or the other (can > be done in the parser as you know). Sure, that'd be a reasonable way to set it up, if we decide to have two SERIAL types. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Possible solution for LIKE optimization
Peter Eisentraut wrote: > > Hiroshi Inoue writes: > > > I'm not familiar with non_ASCII locale. > > Is 'ss' always guaranteed to be LIKE 's%' for example ? > > Yes. LIKE doesn't use any collation rules, since it doesn't do any > collating. > Hmm I see the description like the following in SQL99 though I don't understand the meaning. i) If is not specified, then the collating sequence used for the is determined by Table 3, $B!F!F(BCollating sequence usage for comparisons$B!G!G(B, taking as comparand 1 (one) and as comparand 2. regards, Hiroshi Inoue ---(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] Re: Notes about int8 sequences
"Roderick A. Anderson" <[EMAIL PROTECTED]> writes: > On Mon, 6 Aug 2001, Tom Lane wrote: >> Hmm. That's a possibility. There's some potential for trouble if an >> application is expecting an int4 result from "SELECT nextval()" and >> gets int8 instead, but if we think we could live with that... > I assume there will be the same limitations as you mentioned in your > original message. Ie. some systems don't have an 8-byte-int C datatype > so would still have the 2^31 limit. Check. >> Actually, if we thought we could live with that, my inclination would be >> to blow off int4-based sequences altogether, and just redefine SEQUENCE >> objects as operating on INT8. Interesting thought, eh? > More than interesting ... excellant. Bigger is better, right? Until it breaks your app, yes ;-) One thing that would have to be thought about is whether the SERIAL pseudo-type should generate an int8 instead of int4 column. On compatibility grounds, it might be better to leave it generating int4, and invent a second pseudo-type SERIAL8 that is just the same except for making an int8 column. I'm more worried about changing the datatype of a user column than I am about changing the output type of nextval(), so I'd be sort of inclined to have two SERIAL types even if we change nextval() to int8. Thoughts? 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] Possible solution for LIKE optimization
Giles Lean <[EMAIL PROTECTED]> writes: > Implementations of strxfrm() that I've looked at have had result data > that is three or four times larger than then input string -- quite a > penalty in some situations. Especially so given that we don't have TOAST for indexes, so the indexed value can't exceed about 2700 bytes (for btree and an 8K block size). You are allowed to compress first, so that's not a hard limit, but it could still be a problem. I like the non-locale-aware-opclass idea much better than the original. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Notes about int8 sequences
On Mon, 6 Aug 2001, Tom Lane wrote: > Hmm. That's a possibility. There's some potential for trouble if an > application is expecting an int4 result from "SELECT nextval()" and > gets int8 instead, but if we think we could live with that... I assume there will be the same limitations as you mentioned in your original message. Ie. some systems don't have an 8-byte-int C datatype so would still have the 2^31 limit. > Actually, if we thought we could live with that, my inclination would be > to blow off int4-based sequences altogether, and just redefine SEQUENCE > objects as operating on INT8. Interesting thought, eh? More than interesting ... excellant. Bigger is better, right? Cheers, Rod -- Remove the word 'try' from your vocabulary ... Don't try. Do it or don't do it ... Steers try! Don Aslett ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Possible solution for LIKE optimization
[ I realise the discussion has left strxfrm(), but for the archives if nothing else ... ] Peter Eisentraut <[EMAIL PROTECTED]> wrote: > We'd need to implement the strxfrm() function in SQL and the $<=$ > operator, both of which are trivial. The index would have to be in terms > of strxfrm(). There might be other issues, but they could be solved > algorithmically, I suppose. Implementations of strxfrm() that I've looked at have had result data that is three or four times larger than then input string -- quite a penalty in some situations. Regards, Giles ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: Notes about int8 sequences
Tom Lane wrote:
> * How should one invoke nextval() and friends on such a sequence?
> Directly applying the existing convention, eg, nextval('sequencename'),
> won't work because those functions are declared to return int4. One
I'm not really a hacker, but why couldn't you simply change nextval to return int8 in
all cases? Presumably there is an automatic (and silent) conversion from int8 to int4
where the range fits? The overhead of creating an int8 return value for an old-style
int4 sequence (and converting it back to int4 for the INSERT/UPDATE) seems very small.
I'm missing something obvious again?
Allan.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Notes about int8 sequences
Allan Engelhardt <[EMAIL PROTECTED]> writes: > I'm not really a hacker, but why couldn't you simply change nextval to > return int8 in all cases? Hmm. That's a possibility. There's some potential for trouble if an application is expecting an int4 result from "SELECT nextval()" and gets int8 instead, but if we think we could live with that... Actually, if we thought we could live with that, my inclination would be to blow off int4-based sequences altogether, and just redefine SEQUENCE objects as operating on INT8. Interesting thought, eh? 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] Re: AW: Re: OID wraparound: summary and proposal
Could we modify the Relation structure to hold an Oid counter? So every where Postgres calls "newoid(void)" it gets changed to pass the relation structure it will be associated with, i.e. newoid(Relation *). That way, every relation could have its own counter, AND perhaps its own spinlock. Relations are shared amongst the various processes, correct? If you pass NULL as the relation, you get an OID out of the ShmemVariableCache->nextXid. Am I being overly simplistic? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Use int8 for int4/int2 aggregate accumulators?
This was discussed on pgsql-general a little bit on 21-July, but the discussion died off without reaching a conclusion. I'd like to put out a concrete proposal and see if anyone has objections. 1. SUM() and AVG() for int2 and int4 inputs should accumulate the running sum as an INT8, not a NUMERIC, for speed reasons. INT8 seems large enough to avoid overflow in practical situations. The final output datatype of AVG() will still be NUMERIC, but the final output of SUM() will become INT8 for these two input types. 2. STDDEV() and VARIANCE() for int2 and int4 inputs will continue to use NUMERIC for accuracy and overflow reasons (accumulating sum(x^2) is much more prone to overflow than sum(x)). So will all these aggregates for INT8. 3. As a separate proposal, we could change COUNT()'s running counter and output datatype from INT4 to INT8. This would make it a little slower but effectively overflow-proof. All of these changes are within the latitude that the SQL92 spec affords (it just says that the output values are exact numeric with implementation-defined precision and scale). Issues to consider are: * On machines with no 8-byte-int C datatype, the accumulator would effectively be int4. This would make the behavior no worse than currently for COUNT(), and no worse than it was in 7.0 for SUM() and AVG(), so that doesn't bother me a whole lot. But it would be a new source of cross-platform behavioral differences. * Changing the output datatype of these operations --- especially COUNT --- might affect or even break applications. We got a few complaints, not many, about changing SUM() and AVG() from integer to NUMERIC output in 7.1. Changing SUM() to INT8 isn't likely to hurt anyone who survived that transition. But COUNT() is much more widely used and is more likely to affect people. Should we keep it at INT4 output to avoid compatibility problems? 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] Notes about int8 sequences
I have been thinking about implementing int8-based sequences to go along
with the existing int4-based ones. The amount of code involved doesn't
seem very large, but there are some interesting questions about the API.
Some points for discussion:
* On machines that don't offer an 8-byte-int C datatype, the int8
sequence type would still exist, but it couldn't actually count higher
than 2^31. This is the same as the behavior of our int8 datatype on
such machines.
* What should be the CREATE syntax for such sequences? I lean towards
adding an optional clause to CREATE SEQUENCE, which might be spelled
like "TYPE INT8", "TYPE BIGINT", or just "INT8" or "BIGINT".
* How should one invoke nextval() and friends on such a sequence?
Directly applying the existing convention, eg, nextval('sequencename'),
won't work because those functions are declared to return int4. One
possible answer is to require people to write nextval8('sequencename')
and so forth. This is ugly; it would be nice to allow automatic
overloading of the function name the way we can do for most datatypes.
We have had discussions to the effect that this method of referencing
sequences is ugly and broken, anyway.
Perhaps we could allow people to write nextval(sequencename) and/or
sequencename.nextval, which would expose the sequence object to the
parser so that datatype overloading could occur. I am envisioning
having two archetype sequence objects, one int4 and the other int8,
and making every other sequence object be an inheritance child of one of
these. Then, declaring nextval functions that operate on the two parent
datatypes would work --- at least to the extent that we could do type
resolution to choose which function to apply. I'm not sure yet how to
keep the parser from adding the sequence to the query's join set when
you do something like that :-(. It would be easier to make it work for
the sequencename.nextval notation, I think, but do we want to encourage
people to use that syntax? It's a PostQuel-ism that we may have to
discard in order to support SQL92 schemas.
In any case, can anyone think of cases where it's a good idea to allow
the sequence name to be specified as a string --- for example, because
you want to compute the sequence name at runtime? To support that,
I think we'd have little choice but to accept nextval8('sequencename').
I'd rather move away from the string-based approach, but I don't know
if we can get away with that.
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] Not representable result out of too large range
Peter Eisentraut <[EMAIL PROTECTED]> writes: > We have five different expected files for the int2 and int4 tests because > every system has a different idea on what to print for ERANGE. I'm about > to add another version. Would it make more sense to hard code one wording > and not use strerror here? Kinda sounds like the path of least resistance, doesn't it? I assume you'd do the substitution inside elog(), so it's consistent for all places that might report ERANGE via %m ? 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] Possible solution for LIKE optimization
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane writes: >> On the other hand, LIKE *is* multibyte aware. So the hypothetical >> non-locale-aware comparison operators would need to be aware of >> multibyte character sets even though not aware of locale. And the >> "add one" operator that we postulated for the LIKE index optimization >> needs to be able to increment a multibyte character. > Both of these are not hard if you know how strcmp operates. It would also > be irrelevant whether "add one" to a multibyte character yields another > valid multibyte character. strcmp doesn't care. I imagine we can come up with a definition that makes LIKE optimization work. I was wondering more about Hiroshi's implied question: would such an index have any use for sorting? Or would the induced sort order (on multibyte character sets) look too weird to be of any use? 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] Re: AW: Re: OID wraparound: summary and proposal
mlw <[EMAIL PROTECTED]> writes: > Am I being overly simplistic? Yes. For one thing, Relation structs are *not* shared, nor even persistent (the relcache will happily discard them). For another, you haven't mentioned how we keep the counter up-to-date across system restarts. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [HACKERS] Possible solution for LIKE optimization
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > >> Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Wait, why isn't that the solution in the first place. Let's build the > > index with an opclass that uses plain strcmp comparison. > > > Do we have to make 2 indexes for non_ASCII text field ? > > You would if you want to use indexscans for both LIKE and "x < 'FOO'" > (ie, locale-aware comparisons). And ORDER BY ? > Which is not great, but I think we've > finally seen the light: a locale-sorted index is just plain not useful > for LIKE. I'm not familiar with non_ASCII locale. Is 'ss' always guaranteed to be LIKE 's%' for example ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: AW: [HACKERS] partial index
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: >> Since bid is not in an index the evaluation of usability obviously >> should not be based on index ops ? Actually, now that I think about it, there's no reason that the prover couldn't try a simple equal() on a WHERE clause and predicate clause before moving on to the btree-semantics-based tests. If the clauses are statically identical then one implies the other, no? This would work nicely for clauses like IS [NOT] NULL, and would give us at least a little bit of ability to deal with non-btree operator clauses. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] failed: make install prefix=/foo/bar
Karel Zak writes: > $ make install prefix=/home/PG_DEVEL/X/ > cp: cannot create regular file > /usr/lib/postgresql/share/locale/de/LC_MESSAGES/# I have fixed this. Note, however, that this probably won't do what you want anyway, because the compiled-in path references will still use the prefix you specified to configure. The only uses for "make install prefix=elsewhere" are some rather specific circumstances where the location specified for installation will be mapped the location used at use time, e.g. with symlinks (e.g., using GNU Stow), or with an AFS file system. If you simply change your mind about the installation prefix you need to make distclean first and rebuild everything. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Question about todo item
Tom Lane wrote: > > Jan Wieck <[EMAIL PROTECTED]> writes: > > One of the problems I saw, and that's probably why we don't > > have a proposal yet, is, that the size of the data is > > recorded in the toast reference held in the main tuple. If > > you later open the toast value for writing, you'll change the > > size, but you'd need to change it in the main tuple too, > > what'd require a regular update on the main tuple, what I > > don't think we want to have here. > > Well, in fact, maybe we *should*. I think so too, as we shouldnt do in-place modification in the toast table anyway but give changed pages new trx ids, i.e UPDATE them. it could be somewhat tricky to change just a few pages if there are some inter page pointers in toast-table. If its all done with regular index only then this should pose no problem. > I was thinking last night about > the fact that large objects as they stand are broken from a > permissions-checking point of view: anyone who knows an LO's OID > can read or write it. A LO-style interface for toasted columns must > not be so brain-dead. This says that a SELECT open_toast_object() > should deliver a read-only object reference, and that if you want > to update, you should have to do an UPDATE. > > Now a read-only TOAST LO reference strikes me as no problem. If the > open() function finds that it's been handed a not-toasted value, it > can just save the value verbatim in the open-LO-reference table. > The value is not large, by definition, so this will work fine. > > As for the update side of things, the best idea I can come up with > is a multi-phase operation: open the value with a select, read/write > the reference, store the updated reference with UPDATE. Something > like: > > 1. SELECT writable_toast_reference(column) FROM table WHERE ...; > > (Actually, SELECT FOR UPDATE would be the more common idiom.) > > 2. Read and/or write the LO reference returned by SELECT. Note that > this must be defined to read/write a temporary work area --- if the > transaction aborts in this part, or commits without doing UPDATE, > nothing has happened to the stored value referenced by the main table > row. (I think this happens automatically if we are hacking rows in > a toast table. If we are hacking an in-line value stored in the > LO-reference table, we might at some point decide we need to shove it > out to disk.) but in both inline and toast-table modified pages should have new transaction id's like regular tuples and thus be handled by regular transaction commit/abort mechanics, at least this seema as a postgres way to do it . > 3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...; > > write_toast_reference extracts the toastable column's data or reference > from the LO table, closes the open LO reference (so you can't continue > hacking the data afterwards), and proceeds with a normal UPDATE. > > It would also be pretty straightforward to extend this to the INSERT > case: we just need an "open" function that creates a new, empty object > of a TOASTable type in the LO reference table. Write on this, and > finally invoke write_toast_reference() in the INSERT. > > Kinda grotty, but implementable, and it doesn't require a whole new set > of permissions concepts. Can anyone improve on this? If toast table has the same permissions as the main table and lo_write honours these then we should be ok. --- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: AW: [HACKERS] partial index
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: > Since bid is not in an index the evaluation of usability obviously > should not be based on index ops ? Feel free to reimplement the theorem-prover, taking special care to be able to prove things about operators that you have zero information about the semantics of. The reason the prover works with btree-indexable operators is that it can infer a lot of semantics from the index opclass relationships. This has nothing to do with whether the index itself is btree or not, let alone whether the variables used in the predicate are in the index. It's just a way to do something useful within a reasonable amount of code. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: AW: [HACKERS] partial index
> > test=# create index myindex on accounts(aid) where bid <> 0; > > > Hmm ? Am I reading correctly ? a restriction that is on a field, that > > is not in the index ? Does that make sense ? > > Yes it does, and in fact it's one of the more important applications of > partial indexes. It's the only way that a partial index can be cheaper > to scan than a full index. Ok, yes, sounds great, but then back to Tatsuo's question: Why is the index atestpartial not used (instead DEBUG) ? create table atest (aid int, bid int); create index atestpartial on atest (aid) where bid <> 0; select * from atest where aid=1 and bid <> 0; and instead seq scan for 1 mio rows 2 rows where bid <> 0 Since bid is not in an index the evaluation of usability obviously should not be based on index ops ? Andreas ---(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] Question about todo item
Jan Wieck <[EMAIL PROTECTED]> writes: > One of the problems I saw, and that's probably why we don't > have a proposal yet, is, that the size of the data is > recorded in the toast reference held in the main tuple. If > you later open the toast value for writing, you'll change the > size, but you'd need to change it in the main tuple too, > what'd require a regular update on the main tuple, what I > don't think we want to have here. Well, in fact, maybe we *should*. I was thinking last night about the fact that large objects as they stand are broken from a permissions-checking point of view: anyone who knows an LO's OID can read or write it. A LO-style interface for toasted columns must not be so brain-dead. This says that a SELECT open_toast_object() should deliver a read-only object reference, and that if you want to update, you should have to do an UPDATE. Now a read-only TOAST LO reference strikes me as no problem. If the open() function finds that it's been handed a not-toasted value, it can just save the value verbatim in the open-LO-reference table. The value is not large, by definition, so this will work fine. As for the update side of things, the best idea I can come up with is a multi-phase operation: open the value with a select, read/write the reference, store the updated reference with UPDATE. Something like: 1. SELECT writable_toast_reference(column) FROM table WHERE ...; (Actually, SELECT FOR UPDATE would be the more common idiom.) 2. Read and/or write the LO reference returned by SELECT. Note that this must be defined to read/write a temporary work area --- if the transaction aborts in this part, or commits without doing UPDATE, nothing has happened to the stored value referenced by the main table row. (I think this happens automatically if we are hacking rows in a toast table. If we are hacking an in-line value stored in the LO-reference table, we might at some point decide we need to shove it out to disk.) 3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...; write_toast_reference extracts the toastable column's data or reference from the LO table, closes the open LO reference (so you can't continue hacking the data afterwards), and proceeds with a normal UPDATE. It would also be pretty straightforward to extend this to the INSERT case: we just need an "open" function that creates a new, empty object of a TOASTable type in the LO reference table. Write on this, and finally invoke write_toast_reference() in the INSERT. Kinda grotty, but implementable, and it doesn't require a whole new set of permissions concepts. Can anyone improve on this? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: AW: [HACKERS] Re: OID wraparound: summary and proposal
Zeugswetter Andreas SB SD wrote: > > > It seems to me, I guess and others too, that the OID mechanism should > be on a > > per table basis. That way OIDs are much more likely to be unique, and > TRUNCATE > > on a table should reset it's OID counter to zero. > > Seems to me, that this would be no different than a performance improved > version > of SERIAL. > If you really need OID, you imho want the systemid tableid tupleid > combo. or (systemid.tableid.tupleid.versioninterval) if you want to be able to time-travel --- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: AW: Re: OID wraparound: summary and proposal
mlw wrote: > > Zeugswetter Andreas SB SD wrote: > > > > > It seems to me, I guess and others too, that the OID mechanism should > > be on a > > > per table basis. That way OIDs are much more likely to be unique, and > > TRUNCATE > > > on a table should reset it's OID counter to zero. > > > > Seems to me, that this would be no different than a performance improved > > version of SERIAL. > > If you really need OID, you imho want the systemid tableid tupleid > > combo. having such an global_oid fits nicely with having table-uniqe oids. just do select 'mysite.'||text(tableoid)||'.'||text(oid) as global_oid from mytable; to get it > I don't care about having an OID or ROWID, I care that there is a 2^32 limit to > the current OID strategy and that a quick fix of allowing tables to exist > without OIDs may break some existing software. I was suggesting the OIDs be > managed on a "per table" basis as a better solution. Now that we have tableoid the need of globally unique oid is much diminished. > In reality, a 32 bit OID, even isolated per table, may be too small. Databases > are getting HUGE. 40G disk drives are less than $100 bucks, in a few months 80G > drives will be less than $200, one can put together 200G RAID systems for about > $1000, a terabyte for about $5000. A database that would have needed an > enterprise level system, just 7 years ago, can be run on a $500 desktop today. And my PalmPilot has more memory adn storage and processor power than PDP-11 where UNIX was developed ;) So the real solution will be going to 64-bit OID's and XIDS, just that some platforms (I'd like to know which) dont have a good "long long" implementation yet; -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Question about todo item
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I keep bugging Jan about it, since pre-7.1 and no one has come up with > > an idea. > > Well, if you want an idea: > >BEGIN; > >SELECT open_toast_object(toastable_column) FROM tab WHERE ...; > >-- app checks that it got exactly one result back > >-- app lo_reads and/or lo_writes using ID returned by SELECT > >END; > > Implementation is left as an exercise for the reader ;-). > > Offhand this seems like it would be doable for a column-value that > was actually moved out-of-line by TOAST, since the open_toast_object > function could see and return the TOAST pointer, and then the read/ > write operations just hack on rows in pg_largeobject. The hard part > is how to provide equivalent functionality (transparent to the client > of course) when the particular value you select has *not* been moved > out-of-line. Ideas anyone? TOAST values aren't stored in pg_largeobject. And how do you seek to a position in a compressed and then sliced object? We need a way to force the object over a streaming interface into uncompressed toast slices first. Let me think about it for two days, Okay? The interface lacks imho a mode (r/w/rw/a) argument. Other than that I'd like this part. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Question about todo item
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I agree we should have it, but I thought the problem was that we > > couldn't come up with an API that worked. > > AFAIR, no one's really tried yet. I do not recall any proposals > getting shot down ... One of the problems I saw, and that's probably why we don't have a proposal yet, is, that the size of the data is recorded in the toast reference held in the main tuple. If you later open the toast value for writing, you'll change the size, but you'd need to change it in the main tuple too, what'd require a regular update on the main tuple, what I don't think we want to have here. The other problem is, if you insert a tuple containing a small value (e.g. empty string), it'll not get toasted and you can't force it to get. Later you open it for writing and pump a CD-image into. How do we convert the existing empty text datum into a toast reference in the main tuple? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Possible solution for LIKE optimization
Hiroshi Inoue <[EMAIL PROTECTED]> writes: >> Peter Eisentraut <[EMAIL PROTECTED]> writes: > Wait, why isn't that the solution in the first place. Let's build the > index with an opclass that uses plain strcmp comparison. > Do we have to make 2 indexes for non_ASCII text field ? You would if you want to use indexscans for both LIKE and "x < 'FOO'" (ie, locale-aware comparisons). Which is not great, but I think we've finally seen the light: a locale-sorted index is just plain not useful for LIKE. This discussion has restored my faith in index opclasses; finally we have a real-world application of 'em that we can point to ... 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: AW: [HACKERS] partial index
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: >> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > test=# create index myindex on accounts(aid) where bid <> 0; > Hmm ? Am I reading correctly ? a restriction that is on a field, that > is not in the index ? Does that make sense ? Yes it does, and in fact it's one of the more important applications of partial indexes. It's the only way that a partial index can be cheaper to scan than a full index. Consider: create index foofull on foo (f1); create index foopartial on foo (f1) where f1 < 100; create index foopartial2 on foo (f1) where f2 > 100; Now select * from foo where f1 < 200; cannot use either of the partial indexes, it will have to use foofull or a seqscan. select * from foo where f1 < 50; can use foopartial, but the number of rows retrieved using the index will be just the same as if it used foofull. Cost savings will be marginal at best. select * from foo where f1 < 50 and f2 > 200; can use foopartial2, and since some of the rows have already been filtered from the index on the basis of f2, this will be cheaper than using either of the other indexes. When I was testing the partial-index additions awhile back, at first I thought it was a bug that the planner didn't show a preference for the partial index in a case like #2. But it was right; the indexscan will cover the same number of rows and indexentries with either index. If the partial index is much smaller than the full index, you might save one or two disk reads during the initial btree descent --- but that's all. So a partial index constructed along the lines of foopartial might save work at insert/update time (if it's much smaller than a full index) but it's no better for selecting. The only way that having both full and partial indexes on a column could make sense is if the partial index's predicate mentions another column. See also the previous discussion about using predicates with UNIQUE indexes. 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] failed: make install prefix=/foo/bar
Hi, the NLS is failed for: $ make install prefix=/home/PG_DEVEL/X/ . . [cut] . . for lang in de; do \ /bin/sh ../../../config/install-sh -c -m 644 $lang.mo /usr/lib/postgresql/share/locale/$lang/LC_MESSAGES/postgres.mo || exit 1; \ done cp: cannot create regular file /usr/lib/postgresql/share/locale/de/LC_MESSAGES/# ^^^ use directly prefix from ./configure and ignore the prefix option for 'make'. All other PG stuff are correct for this. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: AW: Re: OID wraparound: summary and proposal
On Mon, 6 Aug 2001, mlw wrote: > Zeugswetter Andreas SB SD wrote: > > > > > It seems to me, I guess and others too, that the OID mechanism should > > be on a > > > per table basis. That way OIDs are much more likely to be unique, and > > TRUNCATE > > > on a table should reset it's OID counter to zero. > > > > Seems to me, that this would be no different than a performance improved > > version > > of SERIAL. > > If you really need OID, you imho want the systemid tableid tupleid > > combo. > > A lot of people seem to use OID, when they really could use XTID. That > > is > > what I wanted to say. > > > > I don't care about having an OID or ROWID, I care that there is a 2^32 limit to > the current OID strategy and that a quick fix of allowing tables to exist > without OIDs may break some existing software. I was suggesting the OIDs be > managed on a "per table" basis as a better solution. Again, what existing software demands per-table OID field? Isn't it what primary keys are for? > In reality, a 32 bit OID, even isolated per table, may be too small. > Databases are getting HUGE. 40G disk drives are less than $100 bucks, > in a few months 80G drives will be less than $200, one can put > together 200G RAID systems for about $1000, a terabyte for about > $5000. A database that would have needed an enterprise level system, > just 7 years ago, can be run on a $500 desktop today. If its too small for you, make a serial8 datatype (or something like this), and use it for your tables. For me, I have tables which have very few fields, and I don't want to waste 4 bytes/row (much less 8) for OID. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: AW: Re: OID wraparound: summary and proposal
Zeugswetter Andreas SB SD wrote: > > > It seems to me, I guess and others too, that the OID mechanism should > be on a > > per table basis. That way OIDs are much more likely to be unique, and > TRUNCATE > > on a table should reset it's OID counter to zero. > > Seems to me, that this would be no different than a performance improved > version > of SERIAL. > If you really need OID, you imho want the systemid tableid tupleid > combo. > A lot of people seem to use OID, when they really could use XTID. That > is > what I wanted to say. > I don't care about having an OID or ROWID, I care that there is a 2^32 limit to the current OID strategy and that a quick fix of allowing tables to exist without OIDs may break some existing software. I was suggesting the OIDs be managed on a "per table" basis as a better solution. In reality, a 32 bit OID, even isolated per table, may be too small. Databases are getting HUGE. 40G disk drives are less than $100 bucks, in a few months 80G drives will be less than $200, one can put together 200G RAID systems for about $1000, a terabyte for about $5000. A database that would have needed an enterprise level system, just 7 years ago, can be run on a $500 desktop today. -- 5-4-3-2-1 Thunderbirds are GO! http://www.mohawksoft.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: AW: [HACKERS] Re: OID wraparound: summary and proposal
On Mon, 6 Aug 2001, mlw wrote: > I think you are focusing too much on "ROWID" and not enough on OID. The issue > at hand is OID. It is a PostgreSQL cluster wide limitation. As data storage > decreases in price, the likelihood of people running into this limitation > increases. I have run into OID problems in my curent project. Geez, 40G 7200 > RPM drives are $120, amazing. Possibly you were using OIDs for what they weren't intended ;) > Tom has proposed being able to remove the OID from tables, to preserve > this resource. I originally thought this was a good idea, but there > are tools and utilities others may want to use in the future that > require OIDs, thus they would have to be re-written or abandoned > altogether. What are these tools? > It seems to me, I guess and others too, that the OID mechanism should be on a > per table basis. That way OIDs are much more likely to be unique, and TRUNCATE > on a table should reset it's OID counter to zero. I disagree. OID as it is now is a mandatory SERIAL that is added to every table. Most tables don't need such a field, those which do, well, they can keep it as it is now (global per-database), or, if you want per-table sequence, just create a SERIAL field explicitly. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RE: AW: [HACKERS] Re: OID wraparound: summary and proposal
> It seems to me, I guess and others too, that the OID mechanism should be on a > per table basis. That way OIDs are much more likely to be unique, and TRUNCATE > on a table should reset it's OID counter to zero. Seems to me, that this would be no different than a performance improved version of SERIAL. If you really need OID, you imho want the systemid tableid tupleid combo. A lot of people seem to use OID, when they really could use XTID. That is what I wanted to say. Andreas ---(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] Re: Vim!
Grant wrote:
>Is it at all possible to use vim to interact with psql to
>provide input?
Why are you asking this on hackers? Please read
http://www.postgresql.org/devel-corner/ ("YOU MUST TRY ELSEWHERE
FIRST")
Yes, psql can call vim. Its in the user documentation. You may
want to read that too.
Regards,
René Pijlman
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: [HACKERS] Re: OID wraparound: summary and proposal
I think you are focusing too much on "ROWID" and not enough on OID. The issue at hand is OID. It is a PostgreSQL cluster wide limitation. As data storage decreases in price, the likelihood of people running into this limitation increases. I have run into OID problems in my curent project. Geez, 40G 7200 RPM drives are $120, amazing. Tom has proposed being able to remove the OID from tables, to preserve this resource. I originally thought this was a good idea, but there are tools and utilities others may want to use in the future that require OIDs, thus they would have to be re-written or abandoned altogether. It seems to me, I guess and others too, that the OID mechanism should be on a per table basis. That way OIDs are much more likely to be unique, and TRUNCATE on a table should reset it's OID counter to zero. Zeugswetter Andreas SB SD wrote: > > > Some other databases have the notion of a ROWID which uniquely > identifies a row > > within a table. OID can be used for that, but it means if you use it, > you must > > limit the size of your whole database system. > > Imho that is getting it all wrong. OID is *not* a suitable substitute > for other > db's ROWID. > > If you take a few extra precautions then you can use XTID in PostgreSQL > instead of other's ROWID. > > We often hear, that it is safer to use ROWID in Oracle and Informix than > in > PostgreSQL. It is only true that the risc of getting at the wrong record > is > lower. Are you going to take chances when manipulating rows ? NO ! > Thus any sensible program working on ROWID's will have builtin > precautions, > like locking the table, or using additional where quals. > > I am still of the opinion, that we should invent an alias ROWID at the > SQL level > for the current XTID. I do not think that it matters what datatype this > ROWID is, > an arbitrary string like xtid is sufficient, it does not need to be an > integer. > > Andreas -- 5-4-3-2-1 Thunderbirds are GO! http://www.mohawksoft.com ---(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
AW: [HACKERS] Name for new VACUUM
> Even more to the point, those typical installations do not want > exclusive-locked VACUUM. Haven't you paid any attention to the user > complaints we've been hearing for the last N years? People want a > nonexclusive VACUUM (or no VACUUM at all, but that's not a choice we can > offer them now.) That *is* what the typical dbadmin will want to run, > and that's why I say it should be the default. I agree. Andreas ---(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] PL/pgSQL: Return multiple rows
Quoting Alex Pilosov <[EMAIL PROTECTED]>: > I'm working on this (not plpgsql specific, though). > > I have most of this done, just need to merge it to the -current and send > in the patch, but I was bogged down by RL :( Problem is that I'd REALLY would like a workaround in 7.1... I have 7.2 from CVS, but is that 'production quality' yet? -- Turbo __ _ Debian GNU Unix _IS_ user friendly - it's just ^/ /(_)_ __ _ ___ __ selective about who its friends are / / | | '_ \| | | \ \/ / Debian Certified Linux Developer _ /// / /__| | | | | |_| |> < Turbo Fredriksson [EMAIL PROTECTED] \\\/ \/_|_| |_|\__,_/_/\_\ Stockholm/Sweden smuggle ammunition congress Uzi munitions Saddam Hussein counter-intelligence quiche Rule Psix South Africa subway [Hello to all my fans in domestic surveillance] spy radar Semtex [See http://www.aclu.org/echelonwatch/index.html for more about this] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: [HACKERS] partial index
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > test=# create index myindex on accounts(aid) where bid <> 0; Hmm ? Am I reading correctly ? a restriction that is on a field, that is not in the index ? Does that make sense ? (aid --> bid) > The original implementation would have refused to let you create a > partial index with such a WHERE clause, since <> isn't a btree-indexable > operator. But that is sad, since it would be a rather important use. Couldn't it be rewritten to: (aid < 0 or aid > 0) ? (I assume you meant aid) Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Possible solution for LIKE optimization
Tom Lane wrote: > > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Wait, why isn't that the solution in the first place. Let's build the > > index with an opclass that uses plain strcmp comparison. > > By George, I think you've got it! All we need is comparison ops and > an opclass that use strcmp, even when USE_LOCALE is defined. Then we > document "here's how you make a LIKE-compatible index in non-ASCII > locales", and away we go. > Do we have to make 2 indexes for non_ASCII text field ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
