Обсуждение: records chaining
I need record changing history. Of course I need to keep old records, and some how point to it for show changes. First of my idea is to make a single column in every record pointing to his "parent" but after I started thinking how SQL can handle it. Whats your opinion, is it possible to write a recursive SELECT to get all records back to that whats parent reference has no reference (NULL)? At this moment I would be happy to know that is it possible - I'm only planning and migrating an old database, the coding seem to be "far" away.
Thanks in advance
Gábor Rózsa
This message, along with your previous one, suggests that you are trying to limit the size of your tables. Is there any good reason for this? How many records do you expect to have? What hardware will you be running on? --- t�vis <tovises@freemail.hu> wrote: > I need record changing history. Of course I need to > keep old records, and some how point to it for show > changes. First of my idea is to make a single column > in every record pointing to his "parent" but after I > started thinking how SQL can handle it. Whats your > opinion, is it possible to write a recursive SELECT > to get all records back to that whats parent > reference has no reference (NULL)? At this moment I > would be happy to know that is it possible - I'm > only planning and migrating an old database, the > coding seem to be "far" away. > Thanks in advance > G�bor R�zsa > > __________________________________ Do you Yahoo!? Plan great trips with Yahoo! Travel: Now over 17,000 guides! http://travel.yahoo.com/p-travelguide
I'am migrating from "single-user" database (Clarion - TopSpeed) to
PostgreSQL. I'am newbie not only in PostgreSQL but in SQL also.
With previouse database handling it was important to keep database files
relativelly short "chunks":
- single-user is always draw hole database file to the client;
- archiving is very comfortable - 500 Mbyte pen drive was enough to draw
all news;
- now speed penalty for growing database files (I met SQL system where
every year
datas should be moved to an "archive", because of the painfull speed -
not PostgreSQL).
The system, what I planning is receiving short binary messages from some
thousend of different sources, after converted to hex ASCII it's about 30
Mbyte of ASCII string, in old system, in a quarter received about 8,000,000
messages - records.
I would like to automate every year moving the database, keep it that way to
get "archive" from the same application, to give the user easy way to get
"messages" from 5 or more years old.
The hardware is not choosen. I will do that, but of course I need to keep a
realistic budget, and the accent should be on reliability, based not on
clustering but on flexibility. That means I need keep large database in easy
to handle size(s) - mirrored...
The record "chaining" is need to keep track changing of sources database,
where always are large fluctuation and modification - some hundred of
records in a month. I've should track what and who and when changed the
record!
----- Original Message -----
From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
To: "tövis" <tovises@freemail.hu>; <pgsql-novice@postgresql.org>
Sent: Tuesday, April 19, 2005 3:59 PM
Subject: Re: [NOVICE] records chaining
> This message, along with your previous one, suggests
> that you are trying to limit the size of your tables.
> Is there any good reason for this? How many records
> do you expect to have? What hardware will you be
> running on?
>
> --- tövis <tovises@freemail.hu> wrote:
>> I need record changing history. Of course I need to
>> keep old records, and some how point to it for show
>> changes. First of my idea is to make a single column
>> in every record pointing to his "parent" but after I
>> started thinking how SQL can handle it. Whats your
>> opinion, is it possible to write a recursive SELECT
>> to get all records back to that whats parent
>> reference has no reference (NULL)? At this moment I
>> would be happy to know that is it possible - I'm
>> only planning and migrating an old database, the
>> coding seem to be "far" away.
>> Thanks in advance
>> Gábor Rózsa
>>
>>
>
>
>
> __________________________________
> Do you Yahoo!?
> Plan great trips with Yahoo! Travel: Now over 17,000 guides!
> http://travel.yahoo.com/p-travelguide
>
You may want to check out the following article from PostgreSQL General Bits that seems to relate directly to your question: Distributed Indexing with Table Inheritance - http://www.varlena.com/varlena/GeneralBits/98.php: "The best example of this is a table which grows in time. Suppose you have a log table of customer support calls that you keep over a very long period of time. The application requires 1) most queries are on the current time set and 2) occassional queries are required for the entire data set. You can create a parent table and one child table of current information and other child tables populated with archived data. The child tables look exactly like the parent table in this case. " Regards, George ----- Original Message ----- From: "tövis" <tovises@freemail.hu> To: "pgsql novice" <pgsql-novice@postgresql.org> Sent: Wednesday, April 20, 2005 3:36 AM Subject: Re: [NOVICE] records chaining > I'am migrating from "single-user" database (Clarion - TopSpeed) to > PostgreSQL. I'am newbie not only in PostgreSQL but in SQL also. > With previouse database handling it was important to keep database files > relativelly short "chunks": > - single-user is always draw hole database file to the client; > - archiving is very comfortable - 500 Mbyte pen drive was enough to draw > all news; > - now speed penalty for growing database files (I met SQL system where > every year > datas should be moved to an "archive", because of the painfull speed - > not PostgreSQL). > The system, what I planning is receiving short binary messages from some > thousend of different sources, after converted to hex ASCII it's about 30 > Mbyte of ASCII string, in old system, in a quarter received about > 8,000,000 messages - records. > I would like to automate every year moving the database, keep it that way > to get "archive" from the same application, to give the user easy way to > get "messages" from 5 or more years old. > The hardware is not choosen. I will do that, but of course I need to keep > a realistic budget, and the accent should be on reliability, based not on > clustering but on flexibility. That means I need keep large database in > easy to handle size(s) - mirrored... > The record "chaining" is need to keep track changing of sources database, > where always are large fluctuation and modification - some hundred of > records in a month. I've should track what and who and when changed the > record! > > ----- Original Message ----- > From: "Jeff Eckermann" <jeff_eckermann@yahoo.com> > To: "tövis" <tovises@freemail.hu>; <pgsql-novice@postgresql.org> > Sent: Tuesday, April 19, 2005 3:59 PM > Subject: Re: [NOVICE] records chaining > > >> This message, along with your previous one, suggests >> that you are trying to limit the size of your tables. >> Is there any good reason for this? How many records >> do you expect to have? What hardware will you be >> running on? >> >> --- tövis <tovises@freemail.hu> wrote: >>> I need record changing history. Of course I need to >>> keep old records, and some how point to it for show >>> changes. First of my idea is to make a single column >>> in every record pointing to his "parent" but after I >>> started thinking how SQL can handle it. Whats your >>> opinion, is it possible to write a recursive SELECT >>> to get all records back to that whats parent >>> reference has no reference (NULL)? At this moment I >>> would be happy to know that is it possible - I'm >>> only planning and migrating an old database, the >>> coding seem to be "far" away. >>> Thanks in advance >>> Gábor Rózsa >>> >>> >> >> >> >> __________________________________ >> Do you Yahoo!? >> Plan great trips with Yahoo! Travel: Now over 17,000 guides! >> http://travel.yahoo.com/p-travelguide >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
my current win dev box pgsql is encoded with ASCII. based on what i've learned here, i want to migrate to Latin1. it sounds like i have to initdb all over again and lose all my table structures and data. is there any way to do a dump to retain the deata and table structures, do an initdb to set up Latin1 encoding and then restore the data and table structures w/o the encoding? is this a lost cause? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
It's really seem written to me! Thankx. ----- Original Message ----- From: "George Weaver" <gweaver@shaw.ca> To: "tövis" <tovises@freemail.hu>; "pgsql novice" <pgsql-novice@postgresql.org> Sent: Wednesday, April 20, 2005 12:40 PM Subject: Re: [NOVICE] records chaining > You may want to check out the following article from PostgreSQL General > Bits that seems to relate directly to your question: > > Distributed Indexing with Table Inheritance - > http://www.varlena.com/varlena/GeneralBits/98.php: > > "The best example of this is a table which grows in time. Suppose you have > a log table of customer support calls that you keep over a very long > period of time. The application requires 1) most queries are on the > current time set and 2) occassional queries are required for the entire > data set. You can create a parent table and one child table of current > information and other child tables populated with archived data. The child > tables look exactly like the parent table in this case. " > > Regards, > George > > > ----- Original Message ----- > From: "tövis" <tovises@freemail.hu> > To: "pgsql novice" <pgsql-novice@postgresql.org> > Sent: Wednesday, April 20, 2005 3:36 AM > Subject: Re: [NOVICE] records chaining > > >> I'am migrating from "single-user" database (Clarion - TopSpeed) to >> PostgreSQL. I'am newbie not only in PostgreSQL but in SQL also. >> With previouse database handling it was important to keep database files >> relativelly short "chunks": >> - single-user is always draw hole database file to the client; >> - archiving is very comfortable - 500 Mbyte pen drive was enough to draw >> all news; >> - now speed penalty for growing database files (I met SQL system where >> every year >> datas should be moved to an "archive", because of the painfull speed - >> not PostgreSQL). >> The system, what I planning is receiving short binary messages from some >> thousend of different sources, after converted to hex ASCII it's about 30 >> Mbyte of ASCII string, in old system, in a quarter received about >> 8,000,000 messages - records. >> I would like to automate every year moving the database, keep it that way >> to get "archive" from the same application, to give the user easy way to >> get "messages" from 5 or more years old. >> The hardware is not choosen. I will do that, but of course I need to keep >> a realistic budget, and the accent should be on reliability, based not on >> clustering but on flexibility. That means I need keep large database in >> easy to handle size(s) - mirrored... >> The record "chaining" is need to keep track changing of sources >> database, where always are large fluctuation and modification - some >> hundred of records in a month. I've should track what and who and when >> changed the record! >> >> ----- Original Message ----- >> From: "Jeff Eckermann" <jeff_eckermann@yahoo.com> >> To: "tövis" <tovises@freemail.hu>; <pgsql-novice@postgresql.org> >> Sent: Tuesday, April 19, 2005 3:59 PM >> Subject: Re: [NOVICE] records chaining >> >> >>> This message, along with your previous one, suggests >>> that you are trying to limit the size of your tables. >>> Is there any good reason for this? How many records >>> do you expect to have? What hardware will you be >>> running on? >>> >>> --- tövis <tovises@freemail.hu> wrote: >>>> I need record changing history. Of course I need to >>>> keep old records, and some how point to it for show >>>> changes. First of my idea is to make a single column >>>> in every record pointing to his "parent" but after I >>>> started thinking how SQL can handle it. Whats your >>>> opinion, is it possible to write a recursive SELECT >>>> to get all records back to that whats parent >>>> reference has no reference (NULL)? At this moment I >>>> would be happy to know that is it possible - I'm >>>> only planning and migrating an old database, the >>>> coding seem to be "far" away. >>>> Thanks in advance >>>> Gábor Rózsa >>>> >>>> >>> >>> >>> >>> __________________________________ >>> Do you Yahoo!? >>> Plan great trips with Yahoo! Travel: Now over 17,000 guides! >>> http://travel.yahoo.com/p-travelguide >>> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >