Thread: postgres on a PDA

postgres on a PDA

From
"Al Bean"
Date:
Hi,

I use a linux based PDA (the Zaurus from Sharp).  There are no good, open
source  databases for this device so I'm thinking of cross-compiling
postgresql for the ARM processor and getting it to run on the Zaurus.  I
need to do this because I have a 150MB Access database that I would like to
take with me (I'll figure out some way to port the data once I get postgres
up and running).

My PDA has 32MB of RAM (well more like 16 with the way Sharp allocates it)
and I use a 256MB FLASH memory card which sort of looks like a hard drive on
the PDA. I know postgresql will run in about 8MB of RAM so I think I should
be ok on this front but I'm wondering about the data residing in FLASH.
Sure I'm prepared for a huge performance hit but aside from that what else
should I expect?

Any thoughts you guys might have are appreciated!

-Al


_________________________________________________________________
The new MSN 8 is here: Try it free* for 2 months
http://join.msn.com/?page=dept/dialup


Re: postgres on a PDA

From
Justin Clift
Date:
Al Bean wrote:
> Hi,
>
> I use a linux based PDA (the Zaurus from Sharp).  There are no good,
> open source  databases for this device so I'm thinking of
> cross-compiling postgresql for the ARM processor and getting it to run
> on the Zaurus.  I need to do this because I have a 150MB Access database
> that I would like to take with me (I'll figure out some way to port the
> data once I get postgres up and running).
>
> My PDA has 32MB of RAM (well more like 16 with the way Sharp allocates
> it) and I use a 256MB FLASH memory card which sort of looks like a hard
> drive on the PDA. I know postgresql will run in about 8MB of RAM so I
> think I should be ok on this front but I'm wondering about the data
> residing in FLASH.  Sure I'm prepared for a huge performance hit but
> aside from that what else should I expect?

No idea, but the idea of trying it sounds interesting.  It's probably
also worthwhile sending your email to the PostgreSQL "Hackers" mailing
list with a subject line of something like "PostgreSQL on ARM cpu?".

:-)

Regards and best wishes,

Justin Clift


> Any thoughts you guys might have are appreciated!
>
> -Al
>
>
> _________________________________________________________________
> The new MSN 8 is here: Try it free* for 2 months
> http://join.msn.com/?page=dept/dialup
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


Re: postgres on a PDA

From
Tom Lane
Date:
"Al Bean" <albean84@hotmail.com> writes:
> My PDA has 32MB of RAM (well more like 16 with the way Sharp allocates it)
> and I use a 256MB FLASH memory card which sort of looks like a hard drive on
> the PDA. I know postgresql will run in about 8MB of RAM so I think I should
> be ok on this front but I'm wondering about the data residing in
> FLASH.

Postgres has kind of gotten away from the notion of a small disk
footprint :-(.  Some things to look at:

* Definitely reduce the size of WAL segments (see XLogSegSize).  You
can't afford the default 16MB.

* You probably don't want to have multiple databases.  I'd suggest doing
all your work in template1.  Consider also dropping template0 --- its
only value would be to reinit template1, and if you need to do that you
might as well re-initdb.

But I fear the real problem is going to be that FLASH memory has a
limited lifetime (measured in write cycles).  Postgres is going to spend
that lifetime with abandon, because it has absolutely no motivation to
avoid disk writes.  Are you prepared to replace the FLASH every so
often?

            regards, tom lane

Re: postgres on a PDA

From
"Dan Langille"
Date:
On 14 Jan 2003 at 23:43, Tom Lane wrote:

> But I fear the real problem is going to be that FLASH memory has a
> limited lifetime (measured in write cycles).  Postgres is going to
> spend that lifetime with abandon, because it has absolutely no
> motivation to avoid disk writes.  Are you prepared to replace the
> FLASH every so often?

What, roughly, is the limit?
--
Dan Langille : http://www.langille.org/


Re: postgres on a PDA

From
Tom Lane
Date:
"Dan Langille" <dan@langille.org> writes:
> On 14 Jan 2003 at 23:43, Tom Lane wrote:
>> But I fear the real problem is going to be that FLASH memory has a
>> limited lifetime (measured in write cycles).

> What, roughly, is the limit?

I am probably a decade out of date on this, but when I last paid
attention I think it was on the order of magnitude of 10000 write
cycles --- which Postgres could blow through in no time.  I hope
it's better now, but I dunno by how much.  Anyone have more
up-to-date info?

            regards, tom lane

Re: postgres on a PDA

From
Thomas Beutin
Date:
On Tue, Jan 14, 2003 at 11:43:26PM -0500, Tom Lane wrote:
> But I fear the real problem is going to be that FLASH memory has a
> limited lifetime (measured in write cycles).  Postgres is going to spend
> that lifetime with abandon, because it has absolutely no motivation to
> avoid disk writes.  Are you prepared to replace the FLASH every so
> often?
The IBM MicroDrive might be a solution for this kind of problem.

Greetings,
-tb
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

Re: postgres on a PDA

From
dalgoda@ix.netcom.com (Mike Castle)
Date:
In article <1883.1042606899@sss.pgh.pa.us>,
Tom Lane  <tgl@sss.pgh.pa.us> wrote:
>attention I think it was on the order of magnitude of 10000 write
>cycles --- which Postgres could blow through in no time.  I hope
>it's better now, but I dunno by how much.  Anyone have more
>up-to-date info?

Only about 1 order of magnitude better.  From the Linux Embedded FAQ:

3.3 Flash Limited write cycles

   Flash  have limited write cycles capabilities from 200 000 to 400 000.
   Using  swap  on such device is dangerous. 300 000 writes gives you 200
   days  at  1  write / minute and 83 hours at 1 write / second. More, If
   you interrupt power at arbitrary times while the device is writing,
   you can lose the integrity of the file system being modified. The loss
   is not limited to the 512 byte sector being modified, as it generally
   is  with rotating disks; you can lose an entire erase block, maybe 64K
   at once. The risk goes up as the "disk" approaches full, because erase
   rewrite  cycles happen more often in this condition. Un*x file systems
   spread their super block tables around the "disk", in the hope that at
   least  one will survive a head crash. Create one file, then /bin/sync:
   what percentage of the device's erase blocks get hit?


I like the part that goes "you can lose the integrity of the file system
being modified."

To be honest, would BerkeleyDB be better?

mrc

--
     Mike Castle      dalgoda@ix.netcom.com      www.netcom.com/~dalgoda/
    We are all of us living in the shadow of Manhattan.  -- Watchmen
fatal ("You are in a maze of twisty compiler features, all different"); -- gcc

Re: postgres on a PDA

From
Philip Hallstrom
Date:
> "Dan Langille" <dan@langille.org> writes:
> > On 14 Jan 2003 at 23:43, Tom Lane wrote:
> >> But I fear the real problem is going to be that FLASH memory has a
> >> limited lifetime (measured in write cycles).
>
> > What, roughly, is the limit?
>
> I am probably a decade out of date on this, but when I last paid
> attention I think it was on the order of magnitude of 10000 write
> cycles --- which Postgres could blow through in no time.  I hope
> it's better now, but I dunno by how much.  Anyone have more
> up-to-date info?

We just bought a compact flash from viking and I believe it was 100,000.
It might have been 1mil, but now I don't remember.  I'll ask my co-worker
when he gets in and if it's different let you know.

seems like it would still go through it pretty fast though.


Re: postgres on a PDA

From
Tom Lane
Date:
dalgoda@ix.netcom.com (Mike Castle) writes:
> To be honest, would BerkeleyDB be better?

Very possibly, if it gets the job done for you.  But last I heard, there
is no SQL engine in BerkeleyDB --- you have to write code against their
access API.  Depending on your taste and the nature of your application,
that might or might not be nicer than writing SQL ...

            regards, tom lane

Re: postgres on a PDA

From
Garo Hussenjian
Date:
Yes... The (now Hitachi) Microdrive is a 3600 RPM hard drive with a CF form
factor, and stores up to 1Gb...

The other solution is to build a web application and use the Z's web browser
with a Wireless CF Modem...

Garo

on 1/15/03 3:12 AM, Thomas Beutin at tyrone@laokoon.IN-Berlin.DE wrote:

> On Tue, Jan 14, 2003 at 11:43:26PM -0500, Tom Lane wrote:
>> But I fear the real problem is going to be that FLASH memory has a
>> limited lifetime (measured in write cycles).  Postgres is going to spend
>> that lifetime with abandon, because it has absolutely no motivation to
>> avoid disk writes.  Are you prepared to replace the FLASH every so
>> often?
> The IBM MicroDrive might be a solution for this kind of problem.
>
> Greetings,
> -tb

=-=-==-=-=-==

Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608

Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com


Re: postgres on a PDA

From
Vivek Khera
Date:
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> dalgoda@ix.netcom.com (Mike Castle) writes:
>> To be honest, would BerkeleyDB be better?

TL> Very possibly, if it gets the job done for you.  But last I heard, there
TL> is no SQL engine in BerkeleyDB --- you have to write code against their
TL> access API.  Depending on your taste and the nature of your application,
TL> that might or might not be nicer than writing SQL ...

See http://www.hwaci.com/sw/sqlite/index.html for an embedded SQL
engine.  It used to be based on GDBM, but I'm not sure what the
backing store is now.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: postgres on a PDA

From
Nicolas Boretos
Date:
Tom Lane wrote:
> dalgoda@ix.netcom.com (Mike Castle) writes:
>
>>To be honest, would BerkeleyDB be better?
>
>
> Very possibly, if it gets the job done for you.  But last I heard, there
> is no SQL engine in BerkeleyDB --- you have to write code against their
> access API.  Depending on your taste and the nature of your application,
> that might or might not be nicer than writing SQL ...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> .
>

Hi,

There's also Sqlite and Metakit; one with and one without an sql front
end...

regards,
nicolas boretos



three timestamps/table, use of 'now' in table creation statement

From
Dennis Gearon
Date:
I want to have three timestamps columns in a table:

'created'
'modified'
'archived'

not unlike more modern file systems.

What I want to happen is that all three columns get set with the 'now'
value to the nearest second, and they ALL have the exact same value.

I want to be able to compare 'created' with 'modified'
        to see if it's been modified,
I want to be able to compare 'created' with 'archived'
        to see if it's been archived.

I don't want to have NULL's with their propagation issues.

Can I use the constant 'now' in the table creation statements?

Will all three columns get assigned the same value at the start of each
record creation, or will I have some that might straddle a second
boundary and be one second apart?

How do I tell the 'now' constant to be to the second's place in
precision?


--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

Re: three timestamps/table, use of 'now' in table creation statement

From
Bruno Wolff III
Date:
Please don't reply to existing threads to start new ones.

On Thu, Jan 16, 2003 at 01:08:09 -0800,
  Dennis Gearon <gearond@cvc.net> wrote:
> I want to have three timestamps columns in a table:
>
> What I want to happen is that all three columns get set with the 'now'
> value to the nearest second, and they ALL have the exact same value.

You will get the same value if the three times are all obtained within
the same transaction.

Re: three timestamps/table, use of 'now' in table creation statement

From
Bruno Wolff III
Date:
Note you seem to have forgotten to cc the list on this message.

On Thu, Jan 16, 2003 at 09:42:10 -0800,
  Dennis Gearon <gearond@cvc.net> wrote:
> Thank you for all the replies, I now have half of my question answered. What I need to know is
> what I can put in the table definition for the default value to have the current timestamp put in
> the three timestamp fields.
>
> 1/ Can I put 'now' as the default value
>     in the table definition?
>
> 2/ can I put CURRENT_TIMESTAMP(0) as the default value ...
>     in the table definition?

I believe both of these will work. If the column is of type timestamp
(as opposed to timestamp(0)) then 'now''s value won't be rounded to
the nearest second.

Re: three timestamps/table, use of 'now' in table creation statement

From
"codeWarrior"
Date:
"Bruno Wolff III" <bruno@wolff.to> wrote in message
news:20030116144423.GA24326@wolff.to...
> Please don't reply to existing threads to start new ones.
>
> On Thu, Jan 16, 2003 at 01:08:09 -0800,
>   Dennis Gearon <gearond@cvc.net> wrote:
> > I want to have three timestamps columns in a table:
> >
> > What I want to happen is that all three columns get set with the 'now'
> > value to the nearest second, and they ALL have the exact same value.
>
> You will get the same value if the three times are all obtained within
> the same transaction.

In your table schema:

"change_dt" timestamptz NULL default 'NOW()',

Which makes absolute sense on an INSERT -- INSERT tblName (field) VALUES
(nval, nval, )  will AUTOMAGICALLY set your fields to their default...

When you UPDATE -- Only update the colums you needs AND change_dt

Ya' know.... UPDATE tblName set dad = '', dada2 = '', change_dt = 'NOW()'...
WHERE dada = dada...


> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



Re: three timestamps/table, use of 'now' in table creation statement

From
will trillich
Date:
On Thu, Jan 16, 2003 at 07:53:46AM -0800, codeWarrior wrote:
> "Bruno Wolff III" <bruno@wolff.to> wrote:
> > Dennis Gearon <gearond@cvc.net> wrote:
> > > I want to have three timestamps columns in a table:
> >
> > > What I want to happen is that all three columns get set with the 'now'
> > > value to the nearest second, and they ALL have the exact same value.
> >
> > You will get the same value if the three times are all obtained within
> > the same transaction.
>
> In your table schema:
>
> "change_dt" timestamptz NULL default 'NOW()',
>
> Which makes absolute sense on an INSERT -- INSERT tblName (field) VALUES
> (nval, nval, )  will AUTOMAGICALLY set your fields to their default...
>
> When you UPDATE -- Only update the colums you needs AND change_dt
>
> Ya' know.... UPDATE tblName set dad = '', dada2 = '', change_dt = 'NOW()'...
> WHERE dada = dada...

missed the start of this thread --

how about some rules?

    create TABLE _plith (
        id       serial,
        created  timestamp(0) default current_timestamp,
        modified timestamp(0),
        accessed timestamp(0),
        dat      text,
        primary key ( id )
    );

    create VIEW plith as
    select
        created,
        modified,
        accessed,
        dat
    from
        _plith
    ;

    create RULE plith_add as
    ON INSERT to plith
    do instead (
        insert into _plith (
            created,
            modified,
            accessed,
            dat
        ) values (
            current_timestamp,
            current_timestamp,
            current_timestamp,
            NEW.dat
        );
    );

    create RULE plith_edit as
    ON UPDATE to plith
    do instead (
        update _plith set
        --    created <= leave it alone!,
            modified = current_timestamp,
        --    accessed = current_timestamp, -- depends on your paradigm
            dat      = NEW.dat
        where
            id       = NEW.id
        ;
    );

and then -- not sure about this one... proceed at your own risk
-- something like this, perhaps?

    create RULE plith_look as -- maybe, probably not...
    ON SELECT to plith
    do instead (
        update _plith set
            accessed = current_timestamp
        where -- hmm! not sure how this would work...
            id       = OLD.id
        ;
        select
            id,
            created,
            modified,
            accessed,
            dat
        from
            _plith
        ;
    );

judging by some of the docs i've been scanning, this ON SELECT
rule might not work. (if the docs are up to date.)

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !