Re: [SQL] Migrating a Database to a new tablespace
Hi, Tom, Tom Lane wrote: > Match the subdirectory names against pg_database.oid --- any subdir that > doesn't correspond to any live entry in pg_database is junk and can be > flushed. Within a valid database's subdirectory, match the file names > to that database's pg_class.relfilenode (not oid!) to see if any of them > are live. Ah, that did it. Both "top level" Directories are not found in pg_database.oid, so they are leftovers from dropped databases. I deleted them, and then the tablespace could be dropped. Thanks a lot, Tom. As I said the leftovers are likely to be caused by hard kills and backend crashes, so I would not go into deeper analysis, but maybe the finding and possibly removing of such leftovers should be half-automated to assist server admins. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] LinkedList
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. Any information that can point me in the direction to figure this out would be appreciated. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: 1, 0 3, 1 7, 3 9, 7 ... Any suggestions would be helpful, or I will have to implement the table differently. Thanks Ray Madigan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] LinkedList
On Wed, 2006-04-26 at 11:09, Ray Madigan wrote: > I have a table that I created that implements a linked list. I am not an > expert SQL developer and was wondering if there are known ways to traverse > the linked lists. Any information that can point me in the direction to > figure this out would be appreciated. The table contains many linked lists > based upon the head of the list and I need to extract all of the nodes that > make up a list. The lists are simple with a item and a link to the history > item so it goes kind of like: > > 1, 0 > 3, 1 > 7, 3 > 9, 7 > ... > > Any suggestions would be helpful, or I will have to implement the table > differently. You should be able to do this with a fairly simple self-join... select a.id, b.aid, a.field1, b.field1 from mytable a join mytable b on (a.id=b.aid) Or something like that. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Migrating a Database to a new tablespace
Markus Schaber <[EMAIL PROTECTED]> writes: > As I said the leftovers are likely to be caused by hard kills and > backend crashes, so I would not go into deeper analysis, but maybe the > finding and possibly removing of such leftovers should be half-automated > to assist server admins. It's been discussed. Personally I'm afraid of the idea of automatically deleting files that seem unreferenced, but having a tool to find them for manual deletion isn't a bad idea. I think Bruce had a prototype patch at one point --- not sure what the status is. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Migrating a Database to a new tablespace
Tom Lane wrote: > Markus Schaber <[EMAIL PROTECTED]> writes: > > As I said the leftovers are likely to be caused by hard kills and > > backend crashes, so I would not go into deeper analysis, but maybe the > > finding and possibly removing of such leftovers should be half-automated > > to assist server admins. > > It's been discussed. Personally I'm afraid of the idea of automatically > deleting files that seem unreferenced, but having a tool to find them > for manual deletion isn't a bad idea. I think Bruce had a prototype > patch at one point --- not sure what the status is. > I have work someone did in the past. I just need to be updated to deal with tablespaces. ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* Let me know if you want details. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LinkedList
Scott, Thanks for your reply, I tried what you said, worked around a few things but I am still stuck. The main reason is I didn't do an adequate job of explaining the situation. The table implements many linked lists and I want to traverse one of them given the end of the list. Say the table contains h | v | j 1 0 100 3 1 300 5 3 500 7 5 700 2 0 200 4 2 400 6 4 600 8 6 800 If I specify t.h = 8 I want to traverse the even part of the table If I specify t.h = 7 I want to traverse the odd part of the table If you can send me to a book to read I am willing Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe Sent: Wednesday, April 26, 2006 8:59 AM To: Ray Madigan Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] LinkedList On Wed, 2006-04-26 at 11:09, Ray Madigan wrote: > I have a table that I created that implements a linked list. I am not an > expert SQL developer and was wondering if there are known ways to traverse > the linked lists. Any information that can point me in the direction to > figure this out would be appreciated. The table contains many linked lists > based upon the head of the list and I need to extract all of the nodes that > make up a list. The lists are simple with a item and a link to the history > item so it goes kind of like: > > 1, 0 > 3, 1 > 7, 3 > 9, 7 > ... > > Any suggestions would be helpful, or I will have to implement the table > differently. You should be able to do this with a fairly simple self-join... select a.id, b.aid, a.field1, b.field1 from mytable a join mytable b on (a.id=b.aid) Or something like that. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Migrating a Database to a new tablespace
On Wed, Apr 26, 2006 at 12:35:39PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Markus Schaber <[EMAIL PROTECTED]> writes: > > > As I said the leftovers are likely to be caused by hard kills and > > > backend crashes, so I would not go into deeper analysis, but maybe the > > > finding and possibly removing of such leftovers should be half-automated > > > to assist server admins. > > > > It's been discussed. Personally I'm afraid of the idea of automatically > > deleting files that seem unreferenced, but having a tool to find them > > for manual deletion isn't a bad idea. I think Bruce had a prototype > > patch at one point --- not sure what the status is. > > > > I have work someone did in the past. I just need to be updated to deal > with tablespaces. > > ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* > > Let me know if you want details. Is it able to also delete the cruft? Seems to be a useful extension, especially on windows, which AFAIK doesn't have an equivalent to ``. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LinkedList
decibel=# select * from t; a | b ---+--- 1 | 0 3 | 1 5 | 3 7 | 5 2 | 0 4 | 2 6 | 4 8 | 6 (8 rows) decibel=# select * from t x join t y on(x.a=y.b) where y.a=7; a | b | a | b ---+---+---+--- 5 | 3 | 7 | 5 (1 row) decibel=# select * from t x join t y on(x.a=y.b) where y.a=8; a | b | a | b ---+---+---+--- 6 | 4 | 8 | 6 (1 row) decibel=# As you can see, it selects the right data, but you'll need to step through it somehow. You might be able to do it with a generate_series(), or you can use a function. If we get WITH support/recursion in 8.2 you'd use that. I think that "SQL For Smarties" by Joe Celko might have an example of how to do this without using a function. Even if it doesn't it's a book any serious database developer should own. On Wed, Apr 26, 2006 at 10:35:15AM -0700, Ray Madigan wrote: > Scott, > > Thanks for your reply, I tried what you said, worked around a few things > but I am still stuck. The main reason is I didn't do an adequate job of > explaining the situation. The table implements many linked lists and I want > to traverse one of them given the end of the list. > > Say the table contains > > h | v | j > 1 0 100 > 3 1 300 > 5 3 500 > 7 5 700 > > 2 0 200 > 4 2 400 > 6 4 600 > 8 6 800 > > If I specify t.h = 8 I want to traverse the even part of the table > If I specify t.h = 7 I want to traverse the odd part of the table > > If you can send me to a book to read I am willing > > Thanks > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe > Sent: Wednesday, April 26, 2006 8:59 AM > To: Ray Madigan > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] LinkedList > > > On Wed, 2006-04-26 at 11:09, Ray Madigan wrote: > > I have a table that I created that implements a linked list. I am not an > > expert SQL developer and was wondering if there are known ways to traverse > > the linked lists. Any information that can point me in the direction to > > figure this out would be appreciated. The table contains many linked > lists > > based upon the head of the list and I need to extract all of the nodes > that > > make up a list. The lists are simple with a item and a link to the > history > > item so it goes kind of like: > > > > 1, 0 > > 3, 1 > > 7, 3 > > 9, 7 > > ... > > > > Any suggestions would be helpful, or I will have to implement the table > > differently. > > You should be able to do this with a fairly simple self-join... > > select a.id, b.aid, a.field1, b.field1 > from mytable a > join mytable b > on (a.id=b.aid) > > Or something like that. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
