Re: [HACKERS] Odd error...
At 22:12 16/07/01 -0500, Dominic J. Eidson wrote: morannon:~pg_dump -t bboard openacs | less getTables(): SELECT (for VIEW ec_subsubcategories_augmented) returned NULL oid SELECT was: SELECT definition as viewdef, (select oid from pg_rewrite where rulename='_RET' || viewname) as view_oid from pg_views where viewname = 'ec_subsubcategories_augmented'; Any ideas what would cause this? Probably the length of the view name; which version are you running? I haven't look at PG for a while, but I thought this was fixed in 7.1.2 Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] deferencing array of int8
I've done some searching through the mailing lists and haven't found any info regarding what I need. I have an array of values of type int8. I want to be able to rollup the data and have postgres do all the summing for me. I've looked at the commands and haven't found what I need. Here is what I get: detailed=# select sum(valuearray) from data where objid=34; ERROR: Unable to select an aggregate function sum(_int8) So I decided I would write my own function that I would load into postgres. The problem is, how do I access each element in the array? I can get the array and return it, but in the function I would like to get each separate value and do the summing, then return the summed array. Can anyone help? Thanks, Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Odd error...
On Tue, 17 Jul 2001, Philip Warner wrote: At 22:12 16/07/01 -0500, Dominic J. Eidson wrote: morannon:~pg_dump -t bboard openacs | less getTables(): SELECT (for VIEW ec_subsubcategories_augmented) returned NULL oid SELECT was: SELECT definition as viewdef, (select oid from pg_rewrite where rulename='_RET' || viewname) as view_oid from pg_views where viewname = 'ec_subsubcategories_augmented'; Any ideas what would cause this? Probably the length of the view name; which version are you running? I haven't look at PG for a while, but I thought this was fixed in 7.1.2 openacs=# select version(); version - PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2 (1 row) (pretty sure that's 7.1.0, btw) openacs=# SELECT definition as viewdef, (select oid from pg_rewrite where rulename='_RET' || viewname) as view_oid from pg_views where viewname = 'ec_subsubcategories_augmented'; viewdef | view_oid -+-- SELECT subsubs.subsubcategory_id, subsubs.subcategory_id, subsubs.subsubcategory_name, subsubs.sort_key, subsubs.last_modified, subsubs.last_modifying_user, subsubs.modified_ip_address, subs.subcategory_name, cats.category_id, cats.category_name FROM ec_subsubcategories subsubs, ec_subcategories subs, ec_categories cats WHERE ((subsubs.subcategory_id = subs.subcategory_id) AND (subs.category_id = cats.category_id)); | As you can see, it gets the viewdef part fine, but not the select oid from pg_rewrite where ... part. -- Dominic J. Eidson Baruk Khazad! Khazad ai-menu! - Gimli --- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Bruce Momjian writes: That was me. The point, however, was, given object id 145928, how the heck to you know what table this comes from? I think we will need the relid of the system table. I imagine four columns: object relid object oid reference relid references oid I'm not seeing the point. You're essentially duplicating the information that's already available in the system catalogs. This is bound to become a catastrophe the minute a user steps in and does manual surgery on some catalog. (And yes, manual surgery should still be possible.) But how then do you find the system table that uses the given oid? Wasn't that your valid complaint? -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Hiroshi Inoue [EMAIL PROTECTED] writes: Christopher Kings-Lynne wrote: Just out of interest, is there a special reason it's difficult to implement the DEFAULT feature of alter table add column? Without *DEFAULT* we don't have to touch the table file at all. With *DEFAULT* we have to fill the new column with the *DEFAULT* value for all existent rows. Do we? We could simply declare by fiat that the behavior of ALTER ADD COLUMN is to fill the new column with nulls. Let the user do an UPDATE to fill the column with a default, if he wants to. After all, I'd not expect that an ALTER that adds a DEFAULT spec to an existing column would go through and replace existing NULL entries for me. This is a little trickier if one wants to make a NOT NULL column, however. Seems the standard technique for that could be ALTER tab ADD COLUMN newcol without the not null spec; UPDATE tab SET newcol = something; ALTER tab ALTER COLUMN newcol ADD CONSTRAINT NOT NULL; where the last command would verify that the column contains no nulls before setting the flag, just like ALTER TABLE ADD CONSTRAINT does now (but I think we don't have a variant for NULL/NOT NULL constraints). This is slightly ugly, maybe, but it sure beats not having the feature at all. Besides, it seems to me there are cases where you don't really *want* the DEFAULT value to be used to fill the column, but something else (or even want NULLs). Why should the system force an update of every row in the table with a value that might not be what the user wants? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
Bruce Momjian writes: I'm not seeing the point. You're essentially duplicating the information that's already available in the system catalogs. This is bound to become a catastrophe the minute a user steps in and does manual surgery on some catalog. (And yes, manual surgery should still be possible.) But how then do you find the system table that uses the given oid? It's implied by the column you're looking at. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
I have noticed that a large fraction of the I/O done by 7.1 is associated with initializing new segments of the WAL log for use. (We have to physically fill each segment with zeroes to ensure that the system has actually allocated a whole 16MB to it; otherwise we fall victim to the hole-saving allocation technique of most Unix filesystems.) I just had an idea about how to avoid this cost: why not recycle old log segments? At the point where the code currently deletes a no-longer-needed segment, just rename it to become the next created-in-advance segment. With this approach, shortly after installation the system would converge to a steady state with a constant number of WAL segments (basically CHECKPOINT_SEGMENTS + WAL_FILES + 1, maybe one or two more if load is really high). So, in addition to eliminating initialization writes, we would also reduce the metadata traffic (inode and indirect blocks) to a very low level. That has to be good both for performance and for improving the odds that the WAL files will survive a system crash. The sole disadvantage I can see to this approach is that a recycled segment would not contain zeroes, but valid WAL records. We'd need to take care that in a recovery situation, we not mistake old records beyond the last one we actually wrote for new records we should redo. While checking the xl_prev back-pointers in each record should be sufficient to detect this, I'd feel more comfortable if we extended the XLogPageHeader record to contain the file/segment number that it belongs to. This'd cost an extra 8 bytes per 8K XLOG page, which seems worth it to me. Another issue is whether the recycling logic should be always recycle (hence number of extant WAL segments will never decrease), or should it be more like recycle if there are fewer than WAL_FILES advance segments, else delete. If we were supporting WAL-based UNDO then I think it'd have to be the latter, so that we could reduce the WAL usage from a peak created by a long-running transaction. But with the present logic that the WAL log is truncated after each checkpoint, I think it'd be better just to never delete. Otherwise, the behavior is likely to be that the system varies between N and N+1 extant segments due to roundoff effects (ie, depending on just where you are in the current segment when a checkpoint happens). That's exactly what we do not want. A possible answer is recycle if there are fewer than WAL_FILES + SLOP advance files, else delete, where SLOP is (say) about three or four segments. That would avoid unwanted oscillations in the number of extant files, while still allowing decrease from a peak for UNDO. Comments, better ideas? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Odd error...
Dominic J. Eidson [EMAIL PROTECTED] writes: On Tue, 17 Jul 2001, Philip Warner wrote: Any ideas what would cause this? Probably the length of the view name; which version are you running? I haven't look at PG for a while, but I thought this was fixed in 7.1.2 PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2 IIRC, that was a post-7.1 bug fix. Update to 7.1.2, or shorten your view name by a few characters. 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] SIGCHLD handler in Postgres C function.
[EMAIL PROTECTED] writes:
I have written a postgres C function that
uses a popen linux system call. Orginally when I first tried it I kept
getting an ECHILD. I read a little bit more on the pclose function
and the wait system calls and discoverd that on LINUX if the signal
handler for SIGCHLD is set to SIG_IGN you will get the ECHILD error
on pclose(or wait4 for that matter). So I did some snooping around in
the postgres backend code and found that in the traffic cop that the
SIGCHLD signal handler is set to SIG_IGN. So in my C function right
before the popen call I set the signal handler for SIGCHLD to SIG_DFL
and right after the pclose I set it back to SIG_IGN. I tested this
and it seems to solve my problem.
Hmm. A possibly related bit of ugliness can be found in
src/backend/commands/dbcommands.c, where we ignore ECHILD after
a system() call:
ret = system(buf);
/* Some versions of SunOS seem to return ECHILD after a system() call */
if (ret != 0 errno != ECHILD)
{
Interesting, no? I wonder whether we could get rid of that kluge
if the signal handler was SIG_DFL rather than SIG_IGN. Can anyone
try this on one of the affected versions of SunOS? (Tatsuo, you
seem to have added the ECHILD exception on May 25 2000; the commit
message mentions Solaris but not which version. Could you try it?)
What I'd be inclined to do, rather than swapping the handlers around
while running, is to just have backend startup (tcop/postgres.c) set
the handler to SIG_DFL not SIG_IGN in the first place. That *should*
produce the identical results according to my man pages, but evidently
it's not quite the same thing on some systems.
Changing this might be a zero-cost solution to a portability glitch.
Comments anyone?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] What I do with PostgreSQL
I am pumping about 200gb a week through the pg database, and our estimated database size is something like 4tb by the end of the year. Can anyone say 'Woof!'? Amen, Lamar. I was trying to think of something myself besides 'Wow!'... As a side note, there's a blurb in the July 16, 2001 Interactive Week about the MySQL AB vs NuSphere spat and the last paragraph of the article casts a very favorable nod towards PostgreSQL. I quote (any typos are mine) ... Analysts said MySQL must find a way to generate a development community and support if it wants to compete with another open source database, PostgreSQL, distributed by Red Hat and Great Bridge. Article doesn't say who the analysts are, but the implication that MySQL isn't up to competing with PostgreSQL was interesting to my eyes! :) Darren ---(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] Execution statistics
Hi Hackers, One of the biggest gaps I've found while doing performance tuning is collecting execution statistics. There's EXPLAIN for the planner, but nothing for the executor. Maybe another verb; ACCOUNT? I'm not suggesting this as work that someone else do. I don't mind trying it myself, but I wouldn't mind some guidance on how to make it an acceptable patch. -Steve ---(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] pg_depend
When a table is dropped, you scan all of these objects (their system catalogs) for matches against the table and either do a cascade or restrict. This is not new, we already do this for indexes and descriptions, for instance. I was thinking we could centralize all that checking in pg_depend. However, we could decide just to do the areas where system tables don't work, like foreign keys and sequences. But when I find an oid depends on me, do I start scanning tables looking to see if is a sequence or a foreign key? -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Bruce Momjian writes: That was me. The point, however, was, given object id 145928, how the heck to you know what table this comes from? I think we will need the relid of the system table. I imagine four columns: object relid object oid reference relid references oid I'm not seeing the point. You're essentially duplicating the information that's already available in the system catalogs. This is bound to become a catastrophe the minute a user steps in and does manual surgery on some catalog. (And yes, manual surgery should still be possible.) -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Operator *=
Hello I know that exists a script file for postgreSQL that have the operator *= and others. Where can I find that script ? Thanks Luis Sousa ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Idea: recycle WAL segments, don't delete/recreate'em
Could you explain how postgresql can fall victim the filesystem hole mechanism? Just hoping to force actual storage allocation, or hoping to discourage fragmentation? Most Unix filesystems will not allocate disk blocks until you write in them. If you just seek out past end-of-file, the file pointer is moved but the blocks are unallocated. This is how 'ls' can show a 1gb file that only uses 4k of disk space. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
Bruce Momjian writes: Is it? Are we going to record dependency both ways, e.g primary table - foreign table and foreign table - primary table, or just one of them. And when we see we depend on something, do we know always what it could be. If I drop a table and I depend on oid XXX, do I know if that is a type, function, or serial sequence? When you drop a table, there are only so many things that could depend on it: * rules/views * triggers * check constraints * foreign key constraints * primary key constraints * unique constraints * subtables including their dependencies. There might be others I forgot but a finite list can be defined. When a table is dropped, you scan all of these objects (their system catalogs) for matches against the table and either do a cascade or restrict. This is not new, we already do this for indexes and descriptions, for instance. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Idea: recycle WAL segments, don't delete/recreate 'em
Patrick Macdonald [EMAIL PROTECTED] writes: I understand your solution is for the existing architecture which does not support point-in-time recovery. If this item is picked up, your solution will become a stumbling block due the above mentioned log extent deletions. Hmm, I don't see why it's a stumbling block. There is a notion in the present code that log segments might be moved someplace else for archiving (rather than just be deleted), and I wasn't planning on eliminating that option. I think however that a realistic archival mechanism would not simply keep the log segments verbatim. It could drop the page images, for a huge space savings, and perhaps also eliminate records from aborted transactions. So in reality one could still expect to recycle the log segments, just with a somewhat longer cycle time --- ie, after the archiver is done copying a segment, then you rename it into place as a forward file. In any case, a two-or-three-line change is hardly likely to create much of an obstacle to PIT recovery, compared to some of the more fundamental aspects of the existing WAL design (like its need to start from a complete physical copy of the database files). So I'm not sure why you're objecting on these grounds. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Tom Lane writes: The alternative to pg_depend is to do a brute force scan of all the system catalogs looking for dependent objects. In that case, you'd know what you are looking at, but if we extract the dependencies as a separate table, I don't see how you'd know without being told. The former is what I'm advocating. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Idea: recycle WAL segments, don't delete/recreate'em
Checking application/pgp-signature: FAILURE -- Start of PGP signed section. Hi - pgman wrote: : Most Unix filesystems will not allocate disk blocks until you write in : them. [...] Yes, I understand that, but how is it a problem for postgresql? Uh, I thought we did that so we were not allocating file system blocks during WAL writes. Performance is bad when we do that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
Patrick Macdonald [EMAIL PROTECTED] writes: Well, notion and actual practice can be mutually exclusive. Your initial message stated that you would like to rename the log segment. This insinuated that the log segment was not moved. Therefore, a straight rename would cause problems with the future point-in-time recovery item (ie. the only existing version of log segment N has been renamed to N+5). A backup of the database could not roll forward through this name change as stated. That was my objection. I think you are missing the point completely. The rename will occur only at the time when we would otherwise DELETE the old log segment. If, for PIT or any other purpose, we do not wish to delete a log segment, then it's not going to get recycled either. My proposal is then when, and only when, we are prepared to discard an old log segment forever, we instead rename it to be a created-in-advance future log segment. What you may really be saying is that the existing scheme for management of log segments is inappropriate for PIT usage; if so feel free to propose a better one. But I don't see how recycling of no-longer-wanted segments can break anything. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_depend
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: The alternative to pg_depend is to do a brute force scan of all the system catalogs looking for dependent objects. In that case, you'd know what you are looking at, but if we extract the dependencies as a separate table, I don't see how you'd know without being told. The former is what I'm advocating. Seems like a bad idea; it'll slow down deletes quite a lot, no? Do you really want to (for example) parse every SQL function in the system to see if it refers to a table being dropped? Why would we want to do that work over again for every such delete, rather than doing it once when an object is created and storing the info in a table? Also consider that what you are proposing is (at least) an O(N^2) algorithm when there are a large number of objects. Furthermore, a separate dependency table would allow us to support user-defined dependencies. It could be that the user knows function A should go away if table B does, yet there is no physical dependency that the system would recognize for it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Idea: recycle WAL segments, don't delete/recreate'emm
Bruce Momjian wrote: Checking application/pgp-signature: FAILURE -- Start of PGP signed section. Hi - pgman wrote: : Most Unix filesystems will not allocate disk blocks until you write in : them. [...] Yes, I understand that, but how is it a problem for postgresql? Uh, I thought we did that so we were not allocating file system blocks during WAL writes. Performance is bad when we do that. Performance isn't the question. The problem is when you get a disk full just in the middle of the need to write important WAL information. While preallocation of a new WAL file, it's OK and controlled, but there are more delicate portions of the code. 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Idea: recycle WAL segments, don't delete/recreate 'em
[EMAIL PROTECTED] (Frank Ch. Eigler) writes: Could you explain how postgresql can fall victim the filesystem hole mechanism? Just hoping to force actual storage allocation, or hoping to discourage fragmentation? The former. We'd prefer not to get an unexpected disk full failure while writing to a log file we thought was good. To the extent that prewriting the WAL segment discourages fragmentation, that's good too, but it's just a side benefit. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
On Tue, 17 Jul 2001, Tom Lane wrote: Seems like a bad idea; it'll slow down deletes quite a lot, no? Do you really want to (for example) parse every SQL function in the system to see if it refers to a table being dropped? Why would we want to do that work over again for every such delete, rather than doing it once when an object is created and storing the info in a table? Also consider that what you are proposing is (at least) an O(N^2) algorithm when there are a large number of objects. I think it's actually O(N^M) where there are N system objects and a chain of M dependencies (A depends on B which depends on C = M = 3). Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
On Tue, 17 Jul 2001, Peter Eisentraut wrote: Tom Lane writes: The alternative to pg_depend is to do a brute force scan of all the system catalogs looking for dependent objects. In that case, you'd know what you are looking at, but if we extract the dependencies as a separate table, I don't see how you'd know without being told. The former is what I'm advocating. Why? It's grossly inefficient and requires lots of effort. And scales horribly to adding new things which can depend on others. Following that argument (admittedly to an extreme conclusion), we should rip out index support. After all, all of the info in the index is stored in the table, we don't need to duplicate it elsewhere. pg_depend is a concise way to encode dependencies. We do all of the work at insert, where we know what depends on what. To not have pg_depend means that on delete, we have to scan EVERYTHING to see what depends on what we're dropping. If we find something (and are CASCADEing), we have to check and see if _it_ depends on anything (another complete scan). We have to keep doing complete scans until we find nothing. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Bill Studenmund [EMAIL PROTECTED] writes: I think it's actually O(N^M) where there are N system objects and a chain of M dependencies (A depends on B which depends on C = M = 3). It's probably not *that* bad. It's reasonable to assume that only a small number of objects actually depend directly on any one object you might want to delete. (Performance of deleting, say, the int4 datatype is probably not of major interest ;-) ...) Only for those objects, not for all N, would you need to descend to the next level of search. Nonetheless, a properly indexed pg_depend table would allow you to find these objects directly, and again to find their dependents directly, etc. The brute force approach would require a rather expensive scan over all the system catalogs, plus nontrivial analysis for some types of system objects such as functions. Repeating that for each cascaded delete is even less appetizing than doing it once. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
On Tue, 17 Jul 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: I think it's actually O(N^M) where there are N system objects and a chain of M dependencies (A depends on B which depends on C = M = 3). It's probably not *that* bad. It's reasonable to assume that only a small number of objects actually depend directly on any one object you might want to delete. (Performance of deleting, say, the int4 datatype is probably not of major interest ;-) ...) Only for those objects, not for all N, would you need to descend to the next level of search. Ah yes. It'll be O(ND) where D is the number of dependers (the number of leaves in the dependency tree). Nonetheless, a properly indexed pg_depend table would allow you to find these objects directly, and again to find their dependents directly, etc. The brute force approach would require a rather expensive scan over all the system catalogs, plus nontrivial analysis for some types of system objects such as functions. Repeating that for each cascaded delete is even less appetizing than doing it once. Indeed. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpected results
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Christopher Kings-Lynne wrote: Just out of interest, is there a special reason it's difficult to implement the DEFAULT feature of alter table add column? Without *DEFAULT* we don't have to touch the table file at all. With *DEFAULT* we have to fill the new column with the *DEFAULT* value for all existent rows. Do we? We could simply declare by fiat that the behavior of ALTER ADD COLUMN is to fill the new column with nulls. Let the user do an UPDATE to fill the column with a default, if he wants to. I don't like to fill the column of the existent rows but it seems to be the spec. After all, I'd not expect that an ALTER that adds a DEFAULT spec to an existing column would go through and replace existing NULL entries for me. This is a little trickier if one wants to make a NOT NULL column, however. Seems the standard technique for that could be ALTER tab ADD COLUMN newcol without the not null spec; UPDATE tab SET newcol = something; ALTER tab ALTER COLUMN newcol ADD CONSTRAINT NOT NULL; Yes I love this also. 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] pg_depend
Peter Eisentraut wrote: Bruce Momjian writes: That was me. The point, however, was, given object id 145928, how the heck to you know what table this comes from? I think we will need the relid of the system table. I imagine four columns: object relid object oid reference relid references oid I like object relid object oid object name reference relid reference oid and unadorned DROP doesn't drop dependent objects. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] What I do with PostgreSQL
On Monday 16 July 2001 14:48, alex avriette wrote: Our hardware is a cluster of 3 ultra 10's, a pair of 700-dvd jukeboxes (with burners), a 2.5tb SAN, 10 DAT tape readers, a pair of dvd-roms, and 2 200gb disk packs (one for each of our tape-reading suns -- the other one manages the DVD jukes). We also run capture on four dell poweredge servers running NT. We run the DjVu software on an additional 3 poweredge servers. That stuff is NT. The SAN is run on a cluster of 4 sun e 3500's. I am pumping about 200gb a week through the pg database, and our estimated database size is something like 4tb by the end of the year. In some ways, I am amazed that postgres has stood up to the challenge. In others, however, I am not in the least surprised. Its a fantastic piece of software that requires almost no intervention on my part. I talked to one of our oracle dba's about it. He actually (im not kidding here) did not believe it could be a database if it did not require maintenance. Can anyone say 'Woof!'? This is awesome. Thank you, Alex, for sharing this testimonial -- your database sounds like a serious test of 'scalability' no matter which way you slice it. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
I have noticed that a large fraction of the I/O done by 7.1 is associated with initializing new segments of the WAL log for use. (We have to physically fill each segment with zeroes to ensure that the system has actually allocated a whole 16MB to it; otherwise we fall victim to the hole-saving allocation technique of most Unix filesystems.) I just had an idea about how to avoid this cost: why not recycle old log segments? At the point where the code currently deletes a no-longer-needed segment, just rename it to become the next created-in-advance segment. This sounds good and with UNDO far off, would be a big win. The segement number seems like a good idea. I can't see any disadvantages. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian writes: But how then do you find the system table that uses the given oid? It's implied by the column you're looking at. It is? Remember that we need to use this table to get from an object to the objects that depend on it. A datatype OID, for example, would have table OIDs (for column datatypes), function OIDs (for argument datatypes), operator OIDs (ditto), aggregate OIDs (ditto), etc etc dependent on it. How will you intuit which of those is represented by a given row in pg_depend? The alternative to pg_depend is to do a brute force scan of all the system catalogs looking for dependent objects. In that case, you'd know what you are looking at, but if we extract the dependencies as a separate table, I don't see how you'd know without being told. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGSQL problem with weblogic and OID data type
Sanath Peiris wrote: Hi everybody, I gotta this problem while I was trying to work with weblogic and postgresql with storing images as byte arrays. First, I used JBOSS-2.2.2 as an application server and Postgresql-7.0.3 as a database serevr to run one of my Java enterprise applications. There I used OID data type to store images, and worked fine with the above combination. I used jdbc7.0-1.2.jar as a postgresql jdbc driver. Please look at the sample codes given below. InputStream banner; String bannerID = some id'; PreparedStatement pstmt = dbConnection.prepareStatement(?Insert into tablename (BannerID, Banner) values(?,?)?); int NoOfBytes = banner.available(); byte[] bytebuffer = new byte[NoOfBytes]; banner.read(bytebuffer); pstmt.setString(1, bannerID); pstmt.setBytes(2, bytebuffer); int resultCount = pstmt.executeUpdate(); After this, I successfully deployed this application in weblogic-6.0 using the same Postgresql database and the jdbc driver. There, some other database accessing parts worked fine, but the above image thing is not worked and gave an error message like FastPath call returned ERROR: lo_write: invalid large obj descriptor (0). I think nothing wrong with the codes...and can be a driver problem with weblogic..Can anybody explain the above pls. Sanath Dont know if anybody replied to your question yet. But you should set your AutoCommit to false e.a conn.setAutoCommit(false); Do this before you execute your preparedstatement. That should fix your problem. Regards Phillip ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
Bruce Momjian writes: I'm not seeing the point. You're essentially duplicating the information that's already available in the system catalogs. This is bound to become a catastrophe the minute a user steps in and does manual surgery on some catalog. (And yes, manual surgery should still be possible.) But how then do you find the system table that uses the given oid? It's implied by the column you're looking at. Is it? Are we going to record dependency both ways, e.g primary table - foreign table and foreign table - primary table, or just one of them. And when we see we depend on something, do we know always what it could be. If I drop a table and I depend on oid XXX, do I know if that is a type, function, or serial sequence? -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE ADD COLUMN column SERIAL -- unexpectedresults
Tom Lane writes: Besides, it seems to me there are cases where you don't really *want* the DEFAULT value to be used to fill the column, but something else (or even want NULLs). Then you could use ALTER TABLE t1 ADD COLUMN cn text; ALTER TABLE t1 ALTER COLUMN cn SET DEFAULT 'what you really wanted'; A subtle difference, but it's perfectly consistent. -- And it works already. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: Idea: recycle WAL segments, don't delete/recreate 'em
tgl wrote: : [...] (We have to physically fill each segment with zeroes to : ensure that the system has actually allocated a whole 16MB to it; : otherwise we fall victim to the hole-saving allocation technique : of most Unix filesystems.) [...] Could you explain how postgresql can fall victim the filesystem hole mechanism? Just hoping to force actual storage allocation, or hoping to discourage fragmentation? - FChE ---(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] pg_depend
Then we have an pg_depend entry e.g. pg_class_relid oid of the view_a pg_class_relid oid of the table a 'a' the name of the table and so on. drop table a; (unadorned drop). Then the above entry would be changed to pg_class_relid(unchanged) oid of the view_s(unchagned) pg_class_relid(unchanged) InvalidOid 'a' the name of the table(unchanged) create table a (...); Then the pg_depend entry would be pg_class_relid(unchanged) oid of the view_s(unchagned) pg_class_relid(unchanged) the oid of the new table a 'a' the name of the table(unchanged) So you want to keep the name of the referenced object in case it is dropped. Makes sense. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_depend
At 11:25 18/07/01 +0900, Hiroshi Inoue wrote: Oops I made a mistake. Reference name is needed not an object name, i.e object relid object oid relerence relid reference oid reference name I think any deisgn needs to cater for attr dependencies. eg. create table a (f1 int4, f2 int8); create view view_a as select f2 from a; Then alter table a drop f1; -- Is OK. Should just happen alter table a drop f2; -- Should warn about the view, and/or cascade etc. alter table a alter f2 float; -- Should trigger a view recompilation. ...same thing needs to happen with constraints that reference attrs I *think* tables are the only items that can have subobjects with dependant. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 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: SOMAXCONN (was Re: Solaris source code)
On Thu, Jul 12, 2001 at 11:08:34PM +0200, Peter Eisentraut wrote: Nathan Myers writes: When the system is too heavily loaded (however measured), any further login attempts will fail. What I suggested is, instead of the postmaster accept()ing the connection, why not leave the connection attempt in the queue until we can afford a back end to handle it? Because the new connection might be a cancel request. Supporting cancel requests seems like a poor reason to ignore what load-shedding support operating systems provide. To support cancel requests, it would suffice for PG to listen at another socket dedicated to administrative requests. (It might even ignore MaxBackends for connections on that socket.) Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
