[HACKERS] vacuum crash on 6.5.3
Althoug this happens on old 6.5.3, I would like to know if this has been already fixed... Here is the scenario: 1) before vacuum, table A has 8850 tuples. 2) vacuum on table A makes postgres crashed. 3) it crashes at line 1758: Assert(num_moved == checked_moved); I examined variables using gdb. num_moved == 8849, check_moved == 8813, num_tuples == 18. 4) if PostgreSQL is not compiled with assertion, vacuum does not crash. However, after vacuum, the number of tuples descreases from 8850 to 8814!! (I am not sure which number is correct, though) I think this is an important problem since a data loss might happen. Any idea? -- Tatsuo Ishii
Re: [HACKERS] (Updated) Table File Format
"Michael Richards" <[EMAIL PROTECTED]> writes: > Following that I find the 2 word tuple pointers. > The first word appears to be the offset in the page where the tuple can be > found but the MSB has to be stripped off (haven't found it's function in the > source yet). > The second is the transactionid that, if comitted gives this tuple > visibility??? No, offset and length --- there is also a 2-bit flags field. Look at the page and item declarations in src/include/storage/ Someone else was recently working on a bit-level dump tool, but I've forgotten who. regards, tom lane
Re: [HACKERS] pg_options.sample
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > pg_options.sample coming with 7.0.x does not work because: > > 1) it exceeds 4096 bytes while read_pg_options() reads only first 4096 > >bytes of it. > > Oliver Elphick posted a patch for this recently (pghackers 28-Nov) > and noted that it seemed already fixed in 7.1 sources. Thanks for poting it out. > > What should we do now? > > Nothing, I think. If you want to apply Oliver's patch to the > REL7_0_PATCHES branch, go ahead --- but I don't think there'll be > a 7.0.4 release, so it's probably wasted effort. > > If the bug still exists in 7.1 sources, then of course we need to > fix it there... > > regards, tom lane Agreed. -- Tatsuo Ishii
Re: [HACKERS] Bug in FOREIGN KEY
> Bruce Momjian writes: > > > ERROR: triggered data change violation on relation "primarytest2" > > We're getting this report about once every 48 hours, which would make it a > FAQ. (hint, hint) > First time I heard of it. Does anyone know more details? -- 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
RE: [HACKERS] Why vacuum?
On Thu, 14 Dec 2000, Christopher Kings-Lynne wrote: > Plenty of other databases need to be 'vacuumed'. For instance, if you have > an ms access database with 5 MB of data in it, and then delete all the data, > leaving only the forms, etc - you will be left with a 5MB mdb file still! > > If you then run 'Compact Database' (which is another word for 'vacuum'), the > mdb file will be reduced down to 500k... Ooh... Hope MS Access isn't going to be taken seriously as a benchmark here :-). The same is also true of MapInfo, by the way, but I'm not holding that up as a benchmark either ;-). > Chris Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/
RE: [HACKERS] Why vacuum?
> But why? I don't know of other databases that need to be 'vacuum'ed. Do > all others just do it internaly on a regular basis? > > What am I missing here? Plenty of other databases need to be 'vacuumed'. For instance, if you have an ms access database with 5 MB of data in it, and then delete all the data, leaving only the forms, etc - you will be left with a 5MB mdb file still! If you then run 'Compact Database' (which is another word for 'vacuum'), the mdb file will be reduced down to 500k... Chris
Re: [HACKERS] Why vacuum?
On Wed, 13 Dec 2000, bpalmer wrote: > > Yes, postgresql requires vacuum quite often otherwise queries and > > updates start taking ungodly amounts of time to complete. If you're > > having problems because vacuum locks up your tables for too long > > you might want to check out: > > But why? I don't know of other databases that need to be 'vacuum'ed. Do > all others just do it internaly on a regular basis? > > What am I missing here? PgSQL's storage manager is currently such that it doesn't overwrite 'deleted' records, but just keeps appending to the end of the table ... so, for instance, a client of ours whose table had 5 records in it that are updated *alot* grew a table to 64Meg that only contains ~8k worth of data ... vacuum'ng cleans out the cruft and truncates the file ... vadim, for v7.2, is planning on re-writing the storage manager to do proper overwriting of deleted space, which will reduce the requirement for vacuum to almost never ...
Re: [HACKERS] Why vacuum?
> Yes, postgresql requires vacuum quite often otherwise queries and > updates start taking ungodly amounts of time to complete. If you're > having problems because vacuum locks up your tables for too long > you might want to check out: But why? I don't know of other databases that need to be 'vacuum'ed. Do all others just do it internaly on a regular basis? What am I missing here? b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5
Re: [HACKERS] Why vacuum?
* xuyifeng <[EMAIL PROTECTED]> [001213 18:54] wrote: > I have this nasty problem too, in early time, I don't know the problem, but we used >it for a while, > than we found our table growing too fast without insert any record( we use update), >this behaviour > most like M$ MSACCESS database I had used a long time ago which don't reuse deleted >record > space and full fill your hard disk after several hours, the nasty vaccum block any >other users to operate > on table, this is a big problem for a large table, because it will block tooo long >to let other user to run > query. we have a project affected by this problem, and sadly we decide to use >closure source database > - SYBASE on linux, we havn't any other selections. :( > > note that SYBASE and Informix both have 'update statistics' command, but they run it >fast in seconds, > not block any other user, this is pretty. ya, what's good technology! http://people.freebsd.org/~alfred/vacfix/ -Alfred
Re: [HACKERS] Why vacuum?
I have this nasty problem too, in early time, I don't know the problem, but we used it for a while, than we found our table growing too fast without insert any record( we use update), this behaviour most like M$ MSACCESS database I had used a long time ago which don't reuse deleted record space and full fill your hard disk after several hours, the nasty vaccum block any other users to operate on table, this is a big problem for a large table, because it will block tooo long to let other user to run query. we have a project affected by this problem, and sadly we decide to use closure source database - SYBASE on linux, we havn't any other selections. :( note that SYBASE and Informix both have 'update statistics' command, but they run it fast in seconds, not block any other user, this is pretty. ya, what's good technology! XuYifeng - Original Message - From: Martin A. Marques <[EMAIL PROTECTED]> To: bpalmer <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, December 14, 2000 7:08 AM Subject: Re: [HACKERS] Why vacuum? El Mié 13 Dic 2000 16:41, bpalmer escribió: > I noticed the other day that one of my pg databases was slow, so I ran > vacuum on it, which brought a question to mind: why the need? I looked > at my oracle server and we aren't doing anything of the sort (that I can > find), so why does pg need it? Any info? I know nothing about Oracle, but I can tell you that Informix has an update statistics, which I don't know if it's similar to vacuum, but What vacuum does is clean the database from rows that were left during updates and deletes, non the less, the tables get shrincked, so searches get faster. Saludos... :-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
Re: [HACKERS] Bug in ILIKE function?
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > That's odd, because it's in the 7.0.3 documentation... Where? A quick grep doesn't find it there anywhere. regards, tom lane
Re: [HACKERS] pg_options.sample
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > pg_options.sample coming with 7.0.x does not work because: > 1) it exceeds 4096 bytes while read_pg_options() reads only first 4096 >bytes of it. Oliver Elphick posted a patch for this recently (pghackers 28-Nov) and noted that it seemed already fixed in 7.1 sources. > What should we do now? Nothing, I think. If you want to apply Oliver's patch to the REL7_0_PATCHES branch, go ahead --- but I don't think there'll be a 7.0.4 release, so it's probably wasted effort. If the bug still exists in 7.1 sources, then of course we need to fix it there... regards, tom lane
Re: [HACKERS] Why vacuum?
I have this nasty problem too, in early time, I don't know the problem, but we used it for a while, than we found our table growing too fast without insert any record( we use update), this behaviour most like M$ MSACCESS database I had used a long time ago which don't reuse deleted record space and full fill your hard disk after several hours, the nasty vaccum block any other users to operate on table, this is a big problem for a large table, because it will block tooo long to let other user to run query. we have a project affected by this problem, and sadly we decide to use closure source database - SYBASE on linux, we havn't any other selections. :( note that SYBASE and Informix both have 'update statistics' command, but they run it fast in seconds, not block any other user, this is pretty. ya, what's good technology! XuYifeng - Original Message - From: Martin A. Marques <[EMAIL PROTECTED]> To: bpalmer <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, December 14, 2000 7:08 AM Subject: Re: [HACKERS] Why vacuum? El Mié 13 Dic 2000 16:41, bpalmer escribió: > I noticed the other day that one of my pg databases was slow, so I ran > vacuum on it, which brought a question to mind: why the need? I looked > at my oracle server and we aren't doing anything of the sort (that I can > find), so why does pg need it? Any info? I know nothing about Oracle, but I can tell you that Informix has an update statistics, which I don't know if it's similar to vacuum, but What vacuum does is clean the database from rows that were left during updates and deletes, non the less, the tables get shrincked, so searches get faster. Saludos... :-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
Re: [HACKERS] Beta1 starting date?
beta1 was very low key ... it was announced here on the list as "its
packaged, try it out" ... there was no big hype about this one, but there
will be for beta2, which will most likely be after Vadim gets those vacuum
fixes in place, and Tom gets those planner fixes ...
On Thu, 14 Dec 2000, Tatsuo Ishii wrote:
> I seem to miss the announce of beta testing of 7.1. Could someone
> please give me a copy of the announcement?
> --
> Tatsuo Ishii
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] RFC C++ Interface
Thanks for responding. I've made some comments below.
On Wed, 13 Dec 2000, Nathan Myers wrote:
> On Wed, Dec 13, 2000 at 03:16:28PM -0500, Randy Jonasz wrote:
> > On Tue, 12 Dec 2000, Nathan Myers wrote:
> > > On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > > > > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > > > > ... The design is really showing its age.
> > > > Can you suggest areas that should be changed?
> > > As I recall, we were much more fond of operator overloading then than is
> > > considered tasteful or wise today. Also, there was no standard for how
> > > iterators ought to work, then, whereas today one needs unusually good
> > > reasons to depart from the STL style.
> >
> > Interesting comments. I can see using the STL framework for iterating
> > through result sets being one way to go. Would something like:
> >
> > vectortable = pgdb.exec("Select * from foo");
> > vector::iterator row;
> > vector::iterator end = table.end();
> >
> > for( row = table.begin(); row != end; ++row ) {
> > *row >> field1 >> field2;
> > //do something with fields
> > }
> >
> > be along the lines you were thinking?
>
> No. The essence of STL is its iterator interface framework.
> (The containers and algorithms that come with STL should be seen
> merely as examples of how to use the iterators.) A better
> example would be
>
> Postgres::Result_iterator end;
> for (Postgres::Result_iterator it = pgdb.exec("Select * from foo");
>it != end; ++it) {
> int field1;
> string field2;
> *it >> field1 >> field2;
> // do something with fields
> }
>
> (although this still involves overloading ">>").
> The points illustrated above are:
>
The above I like very much although it implies a synchronous connection to
the back end. This can be worked around though by providing both a
synchronous and an asynchronous interface rather than using just one. I
don't see any problems with overloading ">>" or "[]" to obtain the value
of a column.
> 1. Shoehorning the results of a query into an actual STL container is
>probably a Bad Thing. Users who want that can do it themselves
>with std::copy().
On further thought I agree with you here. Returning an iterator to a
result container would be much more efficient than what I originally
proposed.
>
> 2. Lazy extraction of query results is almost always better than
>aggressive extraction. Often you don't actually care about
>the later results, and you may not have room for them anyhow.
>
> Rather than the generic result iterator type illustrated above, with
> conversion to C++ types on extraction via ">>", I would prefer to use
> a templated iterator type so that the body of the loop would look more
> like
>
> // do something with it->field1 and it->field2
>
> or
>
> // do something with it->field1() and it->field2()
>
This creates the problem of having public member variables and/or having a
mechanism to clone enough variables as there were columns returned in an
SQL statement. I much prefer the earlier methods of accessing these
values.
> However, it can be tricky to reconcile compile-time type-safety with
> the entirely runtime-determined result of a "select". Probably you
> could put in conformance checking where the result of exec() gets
> converted to the iterator, and throw an exception if the types don't
> match.
>
> Nathan Myers
> [EMAIL PROTECTED]
>
>
>
Cheers,
Randy Jonasz
Software Engineer
Click2net Inc.
Web: http://www.click2net.com
Phone: (905) 271-3550
"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle
RE: [HACKERS] Bug in ILIKE function?
That's odd, because it's in the 7.0.3 documentation... Chris > -Original Message- > From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 14, 2000 3:29 AM > To: Christopher Kings-Lynne > Cc: Pgsql-Hackers > Subject: Re: [HACKERS] Bug in ILIKE function? > > > Christopher Kings-Lynne writes: > > > I have just tried using the ILIKE function in 7.0.3. > > There is no ILIKE function in 7.0.3. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ >
[HACKERS] pg_options.sample
pg_options.sample coming with 7.0.x does not work because: 1) it exceeds 4096 bytes while read_pg_options() reads only first 4096 bytes of it. 2) it allows spaces around "=" while parese_options() does not. Apparently the sample file was brought in without enough testings when 7.0 was developed. What should we do now? Should we fix pg_options code so that PostgreSQL accepts the sample file? Or just leave it and add a new entry to the FAQ? -- Tatsuo Ishii
[HACKERS] Beta1 starting date?
I seem to miss the announce of beta testing of 7.1. Could someone please give me a copy of the announcement? -- Tatsuo Ishii
Re: [HACKERS] Idea for reducing planning time
Alfred Perlstein <[EMAIL PROTECTED]> writes: > If you're saying that you're OK with the work Vadim has done please > let him know, I'm assuming he hasn't committed out of respect for your > still standing objection. Well, I'm still against committing it now, but I only have one core vote, and I seem to be losing 3:1. I know when to concede ;-) > As far as the work you're proposing, how much of a gain is it over > the current code? 2x? 3x? 20x? :) There's a difference between a > slight performance increase and something too good to pass up. Hard to tell without doing the work. But we already know that extra paths inside the planner pose a combinatorial penalty --- think exponential behavior, not linear speedups... regards, tom lane
[HACKERS] Table File Format
Hi. I need a little help on the format of the postgres tables. I've got this wonderfully corrupted database where just about everything is fubar. I've tried a number of things to get it back using postgres and related tools with no success. It looks like most of the data is there, but there may be a small amount of corruption that's causing all kinds of problems. I've broken down and begin development of a tool to allow examination of the data within the table files. This could actually be useful for recovering and undoing changes (or at least until the row-reuse code goes into production). I've been hacking the file format and trying to find stuff in the source and docs as much as possible, but here goes... a) tuples cannot span multiple pages (yet). b) the data is not platform independant??? Ie the data from a sun looks different from an intel? For every page, I see that the first 2 words are for the end of the tuple pointers and the beginning of the tuple data. What are the next 2 words used for? In all my cases they appear to be set to 0x2000. Following that I find the 2 word tuple pointers. The first is the transactionid that, if comitted gives this tuple visibility??? The second word appears to be the offset in the page where the tuple can be found. Are these tuple pointers always stored in order of last to first? Or should I be loading and sorting them according to offset? Now on to the tuple data... I have my tool to the point where it extracts all the tuple data from the table, but I haven't been able to find the place in the postgres source that explains the format. I assume a tuple contains a number of attributes (referencing pg_attribute). Those not found in the tuple would be assumed to be NULL. Since I'm ignoring transaction ids right now, I'm planning on extracting all the tuple and ordering them by oid so you can see all the comitted and uncomitted changes. I may even make it look good once I've recovered my data... -Michael
RE: [HACKERS] 7.0.3(nofsync) vs 7.1
> I still don't see how dirty reads can solve the RI problems. > If Xact A deletes a PK while Xact B inserts an FK, one of > them will either see the new reference or the PK gone. But > from a transactional POV it depends on if the opposite Xact > finally commits or not to tell if that really happened. > > With dirty read, you only get "maybe my PK is gone" or "maybe > there is a reference". Yes, and so we'll write special function(s) to check was PK really gone/FK inserted or not. This funcs will call XactLockTableWait(t_xmin|t_xmax) for questionable tuple to wait for concurrent transaction commit/rollback. It will work as long as we call RI triggers *after* INSERT/UPDATE/DELETE op, so triggers can see concurrent changes with dirty reads. Vadim
Re: [HACKERS] Idea for reducing planning time
sorry, meant to respond to the original and deleted it too fast ...
Tom, if the difference between 7.0 and 7.1 is such that there is a
performance decrease, *please* apply the fix ... with the boon that OUTER
JOINs will provide, would hate to see us with a performance hit reducing
that impact ...
One thing I would like to suggest for this stage of the beta, though, is
that a little 'peer review' before committing the code might be something
that would help 'ease' implementing stuff like this and Vadim's VACUUM
code ... read through Vadim's code and see if it looks okay to you ... get
Vadim to read through your code/patch and see if it looks okay to him
... it adds a day or two to the commit cycle, but at least you can say it
was reviewed before committed ...
On Wed, 13 Dec 2000, Alfred Perlstein wrote:
> * Tom Lane <[EMAIL PROTECTED]> [001213 15:18] wrote:
> >
> > I'm trying to resist the temptation to make this change right now :-).
> > It's not quite a bug fix --- well, maybe you could call it a performance
> > bug fix --- so I'm kind of thinking it shouldn't be done during beta.
> > OTOH I seem to have lost the argument that Vadim shouldn't commit VACUUM
> > performance improvements during beta, so maybe this should go in too.
> > What do you think?
>
> If you're saying that you're OK with the work Vadim has done please
> let him know, I'm assuming he hasn't committed out of respect for your
> still standing objection.
>
> If you're terribly against it then say so again, I just would rather
> it not happen because you objected rather than missed communication.
>
> As far as the work you're proposing, how much of a gain is it over
> the current code? 2x? 3x? 20x? :) There's a difference between a
> slight performance increase and something too good to pass up.
>
> thanks,
> --
> -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
> "I have the heart of a child; I keep it in a jar on my desk."
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] Idea for reducing planning time
* Tom Lane <[EMAIL PROTECTED]> [001213 15:18] wrote: > > I'm trying to resist the temptation to make this change right now :-). > It's not quite a bug fix --- well, maybe you could call it a performance > bug fix --- so I'm kind of thinking it shouldn't be done during beta. > OTOH I seem to have lost the argument that Vadim shouldn't commit VACUUM > performance improvements during beta, so maybe this should go in too. > What do you think? If you're saying that you're OK with the work Vadim has done please let him know, I'm assuming he hasn't committed out of respect for your still standing objection. If you're terribly against it then say so again, I just would rather it not happen because you objected rather than missed communication. As far as the work you're proposing, how much of a gain is it over the current code? 2x? 3x? 20x? :) There's a difference between a slight performance increase and something too good to pass up. thanks, -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
[HACKERS] (Updated) Table File Format
I need a little help on the format of the postgres tables. I've got this wonderfully corrupted database where just about everything is fubar. I've tried a number of things to get it back using postgres and related tools with no success. It looks like most of the data is there, but there may be a small amount of corruption that's causing all kinds of problems. I've broken down and begin development of a tool to allow examination of the data within the table files. This could actually be useful for recovering and undoing changes (or at least until the row-reuse code goes into production). I've been hacking the file format and trying to find stuff in the source and docs as much as possible, but here goes... a) tuples cannot span multiple pages (yet). b) the data is not platform independant??? Ie the data from a sun looks different from an intel? For every page, I see that the first 2 words are for the end of the tuple pointers and the beginning of the tuple data. What are the next 2 words used for? In all my cases they appear to be set to 0x2000. Following that I find the 2 word tuple pointers. The first word appears to be the offset in the page where the tuple can be found but the MSB has to be stripped off (haven't found it's function in the source yet). The second is the transactionid that, if comitted gives this tuple visibility??? Are these tuple pointers always stored in order of last to first? Or should I be loading and sorting them according to offset? Now on to the tuple data... I have my tool to the point where it extracts all the tuple data from the table, but I haven't been able to find the place in the postgres source that explains the format. I assume a tuple contains a number of attributes (referencing pg_attribute). Those not found in the tuple would be assumed to be NULL. Since I'm ignoring transaction ids right now, I'm planning on extracting all the tuple and ordering them by oid so you can see all the comitted and uncomitted changes. I may even make it look good once I've recovered my data... -Michael
Re: [HACKERS] left join bug?
Got it --- was the proverbial one-line fix --- thanks for the report! regards, tom lane
Re: [HACKERS] Why vacuum?
bpalmer wrote: > > I noticed the other day that one of my pg databases was slow, so I ran > vacuum on it, which brought a question to mind: why the need? I looked > at my oracle server and we aren't doing anything of the sort (that I can > find), so why does pg need it? Any info? Hi, I'm one of the people beeing slightly bitten by the current vacuum behaviour :), so i take the chance to add my suggestions to this question. FWIW, my thought is about a vacuumer process that, in background, scans each table for available blocks (for available I mean a block full of deleted rows whose tid is commited) and fills a cache of those blocks available to the backends. Whenever a backend needs to allocate a new block it looks for a free block in the cache, if it finds any, it can use it, else it proceeds as usual appending the block at the tail. The vacuumer would run with a very low priority, so that it doesn't suck precious CPU and I/O when the load on the machine is high. A small flag on each table would avoid the vacuumer to scan the table if no empty block is found and no tuple has been deleted. Ok, now tell me where this is badly broken :)) Just my .02 euro :) Bye! -- Daniele Orlandi
Re: [HACKERS] Why vacuum?
* Martin A. Marques <[EMAIL PROTECTED]> [001213 15:15] wrote: > El Mié 13 Dic 2000 16:41, bpalmer escribió: > > I noticed the other day that one of my pg databases was slow, so I ran > > vacuum on it, which brought a question to mind: why the need? I looked > > at my oracle server and we aren't doing anything of the sort (that I can > > find), so why does pg need it? Any info? > > I know nothing about Oracle, but I can tell you that Informix has an update > statistics, which I don't know if it's similar to vacuum, but > What vacuum does is clean the database from rows that were left during > updates and deletes, non the less, the tables get shrincked, so searches get > faster. Yes, postgresql requires vacuum quite often otherwise queries and updates start taking ungodly amounts of time to complete. If you're having problems because vacuum locks up your tables for too long you might want to check out: http://people.freebsd.org/~alfred/vacfix/ It has some tarballs that have patches to speed up vacuum depending on how you access your tables you can see up to a 20x reduction in vacuum time. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
[HACKERS] Idea for reducing planning time
I've been looking into Brian Hirt's complaint that 7.0.3 and 7.1 are lots slower than 7.0.2 in planning big joins. The direct cause is that since we now deduce implied equality clauses, the system has more potential join paths than it used to --- for example, given "WHERE a.x = b.y AND b.y = c.z", the old system would not consider joining a to c and then adding b, because it didn't have a joinqual relating a and c. Now it does. There's not a lot to be done about that, but I've been looking to see if we can make any offsetting speedups. While digging around, I've realized that the planner is still carrying around a lot more paths than it really needs to. The rule in add_path() is that we will keep a path if it is cheaper OR differently sorted/ better sorted than any other path for the same rel. But what is not taken into account is whether the sort ordering of a path actually has any potential usefulness. Before saving a path on the grounds that it's got an otherwise unobtainable sort ordering, we should check to see if that sort ordering is really going to be useful for a later mergejoin or for the final output ordering. It turns out we already have code to check that for basic indexscan paths --- see useful_for_mergejoin() and useful_for_ordering() in indxpath.c. But we fail to make the same sort of test on paths for join relations, with the result that we carry along a lot more paths than we could possibly need, and that costs huge amounts of time. An example of what's going on is that given a query with FROM a, b, ... other rels ... WHERE a.w = 1 AND a.x = 2 AND b.y = 3 AND b.z = 4 ... if w,x,y,z all have indexes we will consider indexscans on all four of those indexes. Which is fine. But we will then consider nestloops and mergejoins of a with b that use these four indexscans as the outer path, and therefore yield results that are sorted by w,x,y,z respectively. Those paths will be carried as possible paths for a+b because they offer different sort orders, even if we have no further use for those sort orderings. And then we have a combinatorial blowup in the number of paths considered at higher join levels. We should instead consider that these paths have no useful sort ordering, and throw away all but the cheapest. What I'm thinking of doing is truncating the recorded pathkeys of a path at the first sortkey that's not useful for either a higher-level mergejoin clause or the requested final output sort ordering. Then the logic inside add_path() wouldn't change, but it would only be considering useful pathkeys and not useless ones. I'm trying to resist the temptation to make this change right now :-). It's not quite a bug fix --- well, maybe you could call it a performance bug fix --- so I'm kind of thinking it shouldn't be done during beta. OTOH I seem to have lost the argument that Vadim shouldn't commit VACUUM performance improvements during beta, so maybe this should go in too. What do you think? regards, tom lane
Re: [HACKERS] Why vacuum?
El Mié 13 Dic 2000 16:41, bpalmer escribió: > I noticed the other day that one of my pg databases was slow, so I ran > vacuum on it, which brought a question to mind: why the need? I looked > at my oracle server and we aren't doing anything of the sort (that I can > find), so why does pg need it? Any info? I know nothing about Oracle, but I can tell you that Informix has an update statistics, which I don't know if it's similar to vacuum, but What vacuum does is clean the database from rows that were left during updates and deletes, non the less, the tables get shrincked, so searches get faster. Saludos... :-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
Re: [HACKERS] external function proposal for 7.2
Hannu Krosing wrote: > > mlw wrote: > > > > Let me explain why I think the changes I mentioned are a good thing. > > > > (BTW gateway.mohawksoft.com seems to going to an old IP address that I > > haven't had for years, something is strange.) > > > > So, using the IP address, go to this web site. > > http://216.41.12.226/search.php3 > > > > This is a test page, not a production page. I'll leave it up for a few > > days barring power outages and other such non-sense. > > Does it search from some hidden fields too ? > > When I searched for "allison", I got lot of allisons, but also a lot of > lines with no allison in them, like "The Janet Lawson Quintet: Sunday > Afternoon" Actually, that's a metaphone search. "Lawson" metaphones to "lsn" and allison will also metaphone to "lsn." The metaphone is optional, but works well when at least two words are specified. A search of "costello allison" will find exactly what you want. -- http://www.mohawksoft.com
[HACKERS] Why vacuum?
I noticed the other day that one of my pg databases was slow, so I ran vacuum on it, which brought a question to mind: why the need? I looked at my oracle server and we aren't doing anything of the sort (that I can find), so why does pg need it? Any info? Thanks, - brandon b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5
Re: [HACKERS] external function proposal for 7.2
On Wed, 13 Dec 2000, mlw wrote: > Assuming all my assumptions are correct, (and I can't see how that is > possible ;-), I should also call the Init function at this time. > > The big problem is calling the "Exit" function. I am sure that will not > be easily done, or even doable, but we can dream. Ok, i don't know the complete syntax of the 'load external function' stuff, but how about something like : ... ON LOAD CALL 'init()' on UNLOAD CALL 'fini()' ... when the functions is loaded, you specify a setup function and when it's unloaded( im not actually sure if this exists) call the finish function. sorry, if any of that sounds dumb. -vince (going back to lurk mode) PGP key: http://codex.net/pgp/pgp.asc
Re: [HACKERS] external function proposal for 7.2
mlw wrote: > > Let me explain why I think the changes I mentioned are a good thing. > > (BTW gateway.mohawksoft.com seems to going to an old IP address that I > haven't had for years, something is strange.) > > So, using the IP address, go to this web site. > http://216.41.12.226/search.php3 > > This is a test page, not a production page. I'll leave it up for a few > days barring power outages and other such non-sense. Does it search from some hidden fields too ? When I searched for "allison", I got lot of allisons, but also a lot of lines with no allison in them, like "The Janet Lawson Quintet: Sunday Afternoon" -- Hannu
Re: [HACKERS] RFC C++ Interface
On Wed, Dec 13, 2000 at 03:16:28PM -0500, Randy Jonasz wrote:
> On Tue, 12 Dec 2000, Nathan Myers wrote:
> > On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > > > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > > > ... The design is really showing its age.
> > > Can you suggest areas that should be changed?
> > As I recall, we were much more fond of operator overloading then than is
> > considered tasteful or wise today. Also, there was no standard for how
> > iterators ought to work, then, whereas today one needs unusually good
> > reasons to depart from the STL style.
>
> Interesting comments. I can see using the STL framework for iterating
> through result sets being one way to go. Would something like:
>
> vectortable = pgdb.exec("Select * from foo");
> vector::iterator row;
> vector::iterator end = table.end();
>
> for( row = table.begin(); row != end; ++row ) {
> *row >> field1 >> field2;
> //do something with fields
> }
>
> be along the lines you were thinking?
No. The essence of STL is its iterator interface framework.
(The containers and algorithms that come with STL should be seen
merely as examples of how to use the iterators.) A better
example would be
Postgres::Result_iterator end;
for (Postgres::Result_iterator it = pgdb.exec("Select * from foo");
it != end; ++it) {
int field1;
string field2;
*it >> field1 >> field2;
// do something with fields
}
(although this still involves overloading ">>").
The points illustrated above are:
1. Shoehorning the results of a query into an actual STL container is
probably a Bad Thing. Users who want that can do it themselves
with std::copy().
2. Lazy extraction of query results is almost always better than
aggressive extraction. Often you don't actually care about
the later results, and you may not have room for them anyhow.
Rather than the generic result iterator type illustrated above, with
conversion to C++ types on extraction via ">>", I would prefer to use
a templated iterator type so that the body of the loop would look more
like
// do something with it->field1 and it->field2
or
// do something with it->field1() and it->field2()
However, it can be tricky to reconcile compile-time type-safety with
the entirely runtime-determined result of a "select". Probably you
could put in conformance checking where the result of exec() gets
converted to the iterator, and throw an exception if the types don't
match.
Nathan Myers
[EMAIL PROTECTED]
Re: [HACKERS] DB Algorithm Essay, please help
Database research papers at berkeley are at: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/papers/ On Wednesday 13 December 2000 12:16, DÅ wrote: > Hi! > > My name is Daniel Åkerud, a swedish studen, writing an essay for my exam. > The label will be something like: "Database algorithms". > I know it is a complex task, and will ofcourse, as soon as possible, > specify more preciesly what it will be about. > > I have thoughts about writing about, for example, how searching a > database will go faster by indexing certain columns in a table. > And what makes this same procedure slower by indexing wrong, or > too many. (Correct me if I am wrong). > > I assume that there is a cascade of algorithms inside the code > of a databasemanager. There is no doubt work for me :) > > Do you have any tips of places where I can gather information? > Do you recommend a book in this topic? > > I have plans of investingating some of the code in several of the Open > Source databasemanagers out there. > > Thank you, > I really appreciate your help! > > Daniel Åkerud > SoftwareEngineering, Malmö University. > [EMAIL PROTECTED] > > > Get your FREE web-based e-mail and newsgroup access at: > http://MailAndNews.com > > Create a new mailbox, or access your existing IMAP4 or > POP3 mailbox from anywhere with just a web browser. > -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
[HACKERS] docs
Hi, Where can I find documentation on WAL, TOAST and how to configure the pg_hda.conf file? Saludos... ;-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
Re: [HACKERS] RFC C++ Interface
Interesting comments. I can see using the STL framework for iterating
through result sets being one way to go. Would something like:
vectortable = pgdb.exec("Select * from foo");
vector::iterator row;
vector::iterator end = table.end();
for( row = table.begin(); row != end; ++row ) {
*row >> field1 >> field2;
//do something with fields
}
be along the lines you were thinking?
Cheers,
Randy
On Tue, 12 Dec 2000, Nathan Myers wrote:
> On Tue, Dec 12, 2000 at 05:28:46PM -0500, Bruce Momjian wrote:
> > > On Sun, Dec 10, 2000 at 06:53:11PM -0500, Bruce Momjian wrote:
> > > > > I appreciate your comments and would like to respond to your
> > > > > concerns. The API I sketched in my earlier e-mail is borrowed
> > > > > heavily from Rogue Wave's dbtools.h++ library. I think it can be
> > > > > a very clean and elegant way of accessing a database.
> > > >
> > > > Rogue Wave's API is quite interesting. It would be a challenge to
> > > > implement. If you think you can do it, I think it would be a real
> > > > win, and a real object-oriented API to PostgreSQL.
> > >
> > > I was co-architect of the Rogue Wave Dbtools.h++ interface design
> > > ... The design is really showing its age. SQL92 and SQL3 didn't
> > > exist then, and neither did the STL or the ISO 14882 C++ Language
> > > standard.
> >
> > Can you suggest areas that should be changed?
>
> As I recall, we were much more fond of operator overloading then than is
> considered tasteful or wise today. Also, there was no standard for how
> iterators ought to work, then, whereas today one needs unusually good
> reasons to depart from the STL style.
>
> Nathan Myers
> [EMAIL PROTECTED]
>
>
Randy Jonasz
Software Engineer
Click2net Inc.
Web: http://www.click2net.com
Phone: (905) 271-3550
"You cannot possibly pay a philosopher what he's worth,
but try your best" -- Aristotle
[HACKERS] Problem when deleting a record from a table
I'm trying to delete all the records or only one record from a table but i'm having this message: ERROR: Unable to identify an operator '=' for types 'int4' and 'text' You will have to retype this query using an explicit cast What's this means ??? Thanks Luis Sousa
Re: [HACKERS] Bug in FOREIGN KEY
Bruce Momjian writes: > ERROR: triggered data change violation on relation "primarytest2" We're getting this report about once every 48 hours, which would make it a FAQ. (hint, hint) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Bug in ILIKE function?
Christopher Kings-Lynne writes: > I have just tried using the ILIKE function in 7.0.3. There is no ILIKE function in 7.0.3. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] No postgres on Solaris
I found it in the PostgreSQL Administrator manual under "Managing Kernel Resources". Wade Oberpriller > > Hi, > I have been using Postgres-7.0.2 on Solaris 8 for the past few months, and > was about to upgrade to 7.1-test, and after following carefully the docs, I > get this: > > postgres@ultra31:~ > /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data > IpcSemaphoreCreate: semget(key=5432004, num=17, 03600) failed: No space left > on > device > > This error does *not* mean that you have run out of disk space. > > It occurs either because system limit for the maximum number of > semaphore sets (SEMMNI), or the system wide maximum number of > semaphores (SEMMNS), would be exceeded. You need to raise the > respective kernel parameter. Look into the PostgreSQL documentation > for details. > > postgres@ultra31:~ > > > I looked at the FAQ_Solaris, but found nothing on this case. I remember > making changes to the kernel parameters when I fist installed postgres, but > can't remember where I found that info. > > Any clues? > > -- > System Administration: It's a dirty job, > but someone told I had to do it. > - > Martín Marquésemail: [EMAIL PROTECTED] > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > Administrador de sistemas en math.unl.edu.ar > - >
[HACKERS] DB-Manager Algorithms Essay. Please help!
Hi! My name is Daniel Åkerud, a swedish studen, writing an essay for my exam. The label will be something like: "Database algorithms". I know it is a complex task, and will ofcourse, as soon as possible, specify more preciesly what it will be about. I have thoughts about writing about, for example, how searching a database will go faster by indexing certain columns in a table. And what makes this same procedure slower by indexing wrong, or too many. (Correct me if I am wrong). I assume that there is a cascade of algorithms inside the code of a databasemanager. There is no doubt work for me :) Do you have any tips of places where I can gather information? Do you recommend a book in this topic? I have plans of investingating some of the code in several of the Open Source databasemanagers out there. Thank you, I really appreciate your help! Daniel Åkerud SoftwareEngineering, Malmö University. [EMAIL PROTECTED]
[HACKERS] DB Algorithm Essay, please help
Hi! My name is Daniel Åkerud, a swedish studen, writing an essay for my exam. The label will be something like: "Database algorithms". I know it is a complex task, and will ofcourse, as soon as possible, specify more preciesly what it will be about. I have thoughts about writing about, for example, how searching a database will go faster by indexing certain columns in a table. And what makes this same procedure slower by indexing wrong, or too many. (Correct me if I am wrong). I assume that there is a cascade of algorithms inside the code of a databasemanager. There is no doubt work for me :) Do you have any tips of places where I can gather information? Do you recommend a book in this topic? I have plans of investingating some of the code in several of the Open Source databasemanagers out there. Thank you, I really appreciate your help! Daniel Åkerud SoftwareEngineering, Malmö University. [EMAIL PROTECTED] Get your FREE web-based e-mail and newsgroup access at: http://MailAndNews.com Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser.
Re: [HACKERS] Re: COPY BINARY file format proposal
Tom Lane writes: > I take it from the smiley that you're not serious, but actually it seems > like it might not be a bad idea. I could see appending a CRC to each > tuple record. Comments anyone? I think I missed the point here. With CRC you typically want to detect data corruption. Where's the possible source of corruption here? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] left join bug?
Max Khon <[EMAIL PROTECTED]> writes: > test=# select a.id, b.id from a left join b using(id); > id | id > + > 43 | > 45 | > (2 rows) > test=# select * from a; > id > > 45 > 43 > 34 > (3 rows) Ugh. It looks like mergejoin must be mishandling the first left-side item when the right side is empty. Will take a look... regards, tom lane
[HACKERS] Writing essay, please help!
Hi! My name is Daniel Åkerud, a swedish studen, writing an essay for my exam. The label will be something like: "Database algorithms". I know it is a complex task, and will ofcourse, as soon as possible, specify more preciesly what it will be about. I have thoughts about writing about, for example, how searching a database will go faster by indexing certain columns in a table. And what makes this same procedure slower by indexing wrong, or too many. (Correct me if I am wrong). I assume that there is a cascade of algorithms inside the code of a databasemanager. There is no doubt work for me :) Do you have any tips of places where I can gather information? Do you recommend a book in this topic? I have plans of investingating some of the code in several of the Open Source databasemanagers out there. Thank you, I really appreciate your help! Daniel Åkerud SoftwareEngineering, Malmö University. [EMAIL PROTECTED] Get your FREE web-based e-mail and newsgroup access at: http://MailAndNews.com Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser.
Re: [HACKERS] Creating a 'SET' type
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > However, is it possible to create a type that has different parameters > wherever it is used. > For instance - the varchar type takes as a parameter the max characters in > the field. Although there is only one varchar type, it has different > properties depending on whether or not it is varchar(5) or varchar(20). Right now, that support is hard-wired into the parser for each such type (and there aren't many). It might be interesting to look at what it would take to make a generalized mechanism whereby a type name could accept parameters, with a type-specific routine being responsible for reducing the parameters down to a typmod value. One problem you'd run into, I think, is creation of parsing ambiguities --- is NUMERIC(9,2) a type specification, or a function call? Right now it's a type spec because NUMERIC is a keyword in the grammar, but that won't do for an extensible mechanism. regards, tom lane
Re: [GENERAL] No postgres on Solaris
Martin A. Marques writes: > IpcSemaphoreCreate: semget(key=5432004, num=17, 03600) failed: No space left > on > device http://www.postgresql.org/devel-corner/docs/postgres/kernel-resources.htm#SYSVIPC -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[HACKERS] index support for arrays (GiST)
Hi, we are getting a bit close to add index support for int arrays using GiST interface. This will really drive up performance of our full text search fully based on postgresql. We have a problem with broken index and couldn't find a reason. I attached archive with sources for GiST functions and test suite to show a problem - vacuum analyze at end end of TESTSQL should complain about broken index. Here is a short description: 1. untar in contrib 7.0.* 2. cd _intarray 3. edit Makefile for TESTDB (name of db for test) 4. createdb TESTDB 5. gmake 6. gmake install 7. psql TESTDB < TESTSQL Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 _intarray.tar.gz
Re: [HACKERS] 7.0.3(nofsync) vs 7.1
Mikheev, Vadim wrote: > > > So, I've run simple test (below) to check this. Seems that 7.1 > > > is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI > > > triggers is quite bad for performance. > > > Also, we should add new TODO item: implement dirty reads > > > and use them in RI triggers. > > > > That would fix RI triggers, I guess, but what about plain SELECT FOR > > UPDATE being used by applications? > > What about it? Application normally uses exclusive row locks only when > it's really required by application logic. > > Exclusive PK locks are not required for FK inserts by RI logic, > we just have no other means to ensure PK existence currently. > > Keeping in mind that RI is used near in every application I would > like to see this fixed. And ppl already complained about it. I still don't see how dirty reads can solve the RI problems. If Xact A deletes a PK while Xact B inserts an FK, one of them will either see the new reference or the PK gone. But from a transactional POV it depends on if the opposite Xact finally commits or not to tell if that really happened. With dirty read, you only get "maybe my PK is gone" or "maybe there is a reference". Jan > > > Why exactly is SELECT FOR UPDATE such a performance problem for 7.1, > > anyway? I wouldn't have thought it'd be a big deal... > > I have only one explanation: it reduces number of transactions ready > to commit (because of the same FK writers will wait till first one > committed - ie log fsynced) and WAL commit performance greatly depends > on how many commits were done by single log fsync. > 7.0.3+nofsync commit performance doesn't depend on this factor. > > Vadim > -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [GENERAL] No postgres on Solaris
El Lun 11 Dic 2000 12:07, Martin A. Marques escribió: > Hi, > I have been using Postgres-7.0.2 on Solaris 8 for the past few months, and > was about to upgrade to 7.1-test, and after following carefully the docs, I > get this: > > postgres@ultra31:~ > /usr/local/pgsql/bin/postmaster -D > /usr/local/pgsql/data IpcSemaphoreCreate: semget(key=5432004, num=17, > 03600) failed: No space left on > device Sorry, checked the FAQ (I thought this would be in the FAQ_Solaris, but it was in the general), and I just recompiled without the --with-maxbackends=64, so I ran out of semaphores. Fixed. ;-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
[HACKERS] left join bug? (fwd)
hi, there! test=# create table a(id int primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for table 'a' CREATE test=# create table b(id int references a); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE test=# insert into a values(45); INSERT 34924 1 test=# insert into a values(43); INSERT 34925 1 test=# insert into a values(34); INSERT 34926 1 test=# select a.id, b.id from a left join b using(id); id | id + 43 | 45 | (2 rows) test=# select * from a; id 45 43 34 (3 rows) test=# select * from b; id (0 rows) test=# insert into b values(34); INSERT 34927 1 test=# select a.id, b.id from a left join b using(id); id | id + 34 | 34 43 | 45 | (3 rows) test=# lark:~$psql --version psql (PostgreSQL) 7.1beta1 contains readline, history, multibyte support [...] lark:~$uname -a FreeBSD xxx 4.2-STABLE FreeBSD 4.2-STABLE #0: Wed Dec 6 17:16:57 NOVT 2000 xxx:/usr/obj/usr/src/sys/alf i386 sorry, if it has already been fixed /fjoe
Re: [HACKERS] external function proposal for 7.2
Let me explain why I think the changes I mentioned are a good thing. (BTW gateway.mohawksoft.com seems to going to an old IP address that I haven't had for years, something is strange.) So, using the IP address, go to this web site. http://216.41.12.226/search.php3 This is a test page, not a production page. I'll leave it up for a few days barring power outages and other such non-sense. I have harped about it before, it is a music search system. There is based on an external daemon which does the full text searching. The search is completely independent of Postgres, but I use Postgres as the data source and the presentation system. I use PHP/Apache to interface with Postgres and display data. (One added goody about the design is that the text search engine can be run on a different machine than the Postgres DB, this allows better scalability with common hardware.) The code looks like http://216.41.12.226/testmuze.html (please look at page source, the table strings screw up the page) It takes three select statements and a temp table, to do what one should be able to do with a single select statement and good function support. Please don't get me wrong, I'm not dumping on Postgres at all, but it would be nice to be able to create this sort of application much easier. Support for these sorts of constructs will put Postgres in the real "world class" database category, not just a very strong contender. It has been suggested that I create a Postgres Index, but that is a lot of code that many would not be able to justify to use Postgres. If the function mechanisms were just a little more powerful, this sort of application would be much easier and more efficient, thus a better choice. -- http://www.mohawksoft.com
AW: AW: [HACKERS] PLEASE help with foreign key and inheritance proble m
>> >> create unique index child_id_index on child (id); >Thanks a lot. You saved my day :-))) Always feels good to be able to help :-) > > > CREATE TABLE will create implicit trigger(s) for FOREIGN > KEY check(s) > > > ERROR: UNIQUE constraint matching given keys for referenced > > > table "child" > > > not found > > > > Then the above works. > > Actually the error message sounds sufficiently clear to me, no? > > I retrospect, yes. Still, I think inheritance could/should do that for me > automatically. Is there a good reason why it doesn't ? None, other that 1. noone implemented it and 2nd there was no generally accepted plan on how this should work. e.g. should the unique index for the serial span the whole hierarchy, or should a separate index be created for each table ? As a hint I would keep my fingers off inheritance as it stands now, since all it is good for is to save you some typing for the create table statements. It currently has almost no other functionality except to give you the supertable columns for all rows in the hierarchy if you select * from supertable. Andreas
[HACKERS] left join bug?
hi, there! test=# create table a(id int primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for table 'a' CREATE test=# create table b(id int references a); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE test=# insert into a values(45); INSERT 34924 1 test=# insert into a values(43); INSERT 34925 1 test=# insert into a values(34); INSERT 34926 1 test=# select a.id, b.id from a left join b using(id); id | id + 43 | 45 | (2 rows) test=# select * from a; id 45 43 34 (3 rows) test=# select * from b; id (0 rows) test=# insert into b values(34); INSERT 34927 1 test=# select a.id, b.id from a left join b using(id); id | id + 34 | 34 43 | 45 | (3 rows) test=# lark:~$psql --version psql (PostgreSQL) 7.1beta1 contains readline, history, multibyte support [...] lark:~$uname -a FreeBSD xxx 4.2-STABLE FreeBSD 4.2-STABLE #0: Wed Dec 6 17:16:57 NOVT 2000 xxx:/usr/obj/usr/src/sys/alf i386 sorry, if it has already been fixed /fjoe
Re: [HACKERS] external function proposal for 7.2
Tom Lane wrote: > > mlw <[EMAIL PROTECTED]> writes: > > I just have to find where I call the exit function. > > That will be the hard part. > > FmgrInfo is not currently considered a durable data structure, and I > think you will be in for grief if you try to make any guarantees about > what will happen when one disappears. If you need a cleanup proc to > be called, I'd suggest looking into registering it to be called at > query completion and/or transaction cleanup/abort, as needed. I think making this structure durable with be fairly 'easy' assuming that fmgr_info(...) is called only once prior to operations which requires the function. If this is not the case, then you are 100% right. If my assumption is correct, and please correct me if I am wrong, then all that should be needed to be done is allocate the structure at this point, and pass it around as the function pointer, and just make sure that it is always 'FunctionCallInvoke' that calls the function. Assuming all my assumptions are correct, (and I can't see how that is possible ;-), I should also call the Init function at this time. The big problem is calling the "Exit" function. I am sure that will not be easily done, or even doable, but we can dream. > > Most of the sorts of resources you might need to clean up already have > cleanup mechanisms, so it's not entirely clear that you even *need* > a cleanup proc. Maybe a different way to say that is that Postgres > already has a pretty well-defined cleanup philosophy, and it's geared > to particular resources (memory, open files, etc) not to individual > called functions. You should consider swimming with that tide rather > than against it. Believe me I understand what you are saying, but, I think Postgres, with a few tweaks here and there, targeted at efficient extension mechanisms, could blow away the DB market. I have harped on my text search engine, I know, but I am not the only one that wants to do these sorts of things, and it is discouraging how little information is available. Making it easy for guys like me, to bring functionality into Posgres, will make Postgres the hands down winner for so many projects that otherwise would have to resort to using some crappy db library. Postgres has it all, it has query language, presentation mechanisms, ODBC, tools, etc. Rather than having to write an application around some crappy db library, we could write a few neat functions in a powerful SQL database. I think a little focus on this area will pay off hugely. > > I have no objection to adding another field to FmgrInfo for the callee's > use, if you can show an example or two where it'd be useful. I'm only > concerned about the callback-on-delete part. That sounds like a recipe > for fragility... Yes, this is a concern for sure, if it is a problem, then, absolutely, it should be dropped. -- http://www.mohawksoft.com
AW: [HACKERS] PLEASE help with foreign key and inheritance problem
> I stated this before, but I did not get a helpful answer. I > might have > misunderstood tghe documentation on foreign keys: > > create table global(id serial); > create table child(anything text) inherits(global); need: create unique index child_id_index on child (id); > gives me an error: > CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > ERROR: UNIQUE constraint matching given keys for referenced > table "child" > not found Then the above works. Actually the error message sounds sufficiently clear to me, no? Andreas
[HACKERS] PLEASE help with foreign key and inheritance problem
I stated this before, but I did not get a helpful answer. I might have
misunderstood tghe documentation on foreign keys:
create table global(id serial);
create table child(anything text) inherits(global);
insert into child(anything) values ('test);
Now, a select * from child shows
id anything
-
1 test
So far, so good.
create table dependend(globid int4 references child(id) on update cascade on
delete cascade);
gives me an error:
CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "child"
not found
Once again, _why_ is this? What would inheritance be good for if I can't use
it this way? Bad enough that inheritance of triggers or constraints doesn't
work, but a simple refernce to a attribute should be possible, shouldn't it?
If there is a good reason not to allow it, I would like to know. If not, I
would be willing to help out implementing it, if somebody points me into the
right direction in the code (or documentation)
Horst
Re: [HACKERS] Locale and multibyte support in 7.1
On 13 Dec 2000, Anatoly K. Lasareff wrote:
> Date: 13 Dec 2000 14:06:16 +0300
> From: "Anatoly K. Lasareff" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: [HACKERS] Locale and multibyte support in 7.1
>
>
> Hi!
>
> I download, configure and install postgresql-7.1beta1 _exactly_ the
> same way as my previous version - 7.0.2:
>
>
>
> ./configure --enable-multibyte=KOI8 --enable-locale
> gmake
> gmake install
>
> initdb
>
You need to do
initb -E KOI8
and setup environment properly
Let me know if you still have a problem
Regards,
Oleg
>
>
> But it seems to me locale support gone out. In particulary
>
> select upper('òÕÓÓËÉÊ ÔÅËÓÔ - Russian text');
>
> (first two words are russian in lowercase in KOI8 encoding) don't give
> uppercase russian text - russian letters don't change. But when I do
> the _same_ steps with 7.0.2 - all is OK. May anyone help me? I work
> under FreeBSD 4.0.
>
> --
> Anatoly K. Lasareff Email:[EMAIL PROTECTED]
> http://tolikus.hq.aaanet.ru:8080Phone: (8632)-710071
>
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
[HACKERS] Locale and multibyte support in 7.1
Hi!
I download, configure and install postgresql-7.1beta1 _exactly_ the
same way as my previous version - 7.0.2:
./configure --enable-multibyte=KOI8 --enable-locale
gmake
gmake install
initdb
But it seems to me locale support gone out. In particulary
select upper('òÕÓÓËÉÊ ÔÅËÓÔ - Russian text');
(first two words are russian in lowercase in KOI8 encoding) don't give
uppercase russian text - russian letters don't change. But when I do
the _same_ steps with 7.0.2 - all is OK. May anyone help me? I work
under FreeBSD 4.0.
--
Anatoly K. Lasareff Email:[EMAIL PROTECTED]
http://tolikus.hq.aaanet.ru:8080Phone: (8632)-710071
AW: [HACKERS] SourceForge & Postgres
> > anyway? ;-)) If so, a search for artistid 100050450 definitely *should* > > use a sequential scan. > > I tested this statement against the database and you are right, about 14 > seconds with the index, 4 without. Now I don't understand the problem any more. Are you complaining, that the optimizer is choosing a faster path ? Or are you saying, that you also get the seq scan for other very infrequent values ? Andreas
[HACKERS] triggers and actions tree/2
Hello all, sorry, but I haven't received any replies to my previous message... and it's important for me to solve it. When I perform an action on a psql database (e.g. insert into a table), some more action could be induced, via trigger firing: - is it possible to know at any time the exact action chain? - is it possible to know at any time if the control is inside a trigger (and which one)? Sorry, I tried to search in www.postgresql.org but I wasn't able to find anything useful. These questions arise because I'm trying to keep in sync two identical psql databases; I have audited tables and an audit trail. I'm facing the problem of recognising which actions in the trail were due to a trigger firing, rather than explicitly commanded. Thanks again Fabio
Re: [HACKERS] Bug in ILIKE function?
Christopher Kings-Lynne wrote: > > Hi, > > I have just tried using the ILIKE function in 7.0.3. I assume that it is > just a case-insensitive version of LIKE. (Please correct me if I am wrong > on this assumption.) AFAIK postgres 7.0.3 does not have it, ILIKE appeared in 7.1 But you could use the case-independant regular expressions. Hannu
