Re: Linux File Systems and Postgres (or dbs in general)

Hi list,

I need to setup a RULE for INSERTing into a VIEW.
The rule has to insert several times into the same table, and I need to
remember the currval()s for another INSERT.

Are there any variables or some other state that I could abuse to safe the
values?
I already tried to CREATE TEMP TABLE only for inserting the values there, but
that doesn't seem to be allowed inside RULEs.
If there is no other

pseudocode:

CREATE TABLE bar (id_bar SERIAL PRIMARY KEY, bar int);
CREATE TABLE bar_bar (id1_bar int, id2_bar int);
CREATE VIEW
foo
AS
SELECT
bar1.bar AS bar1,
bar2.bar AS bar2
FROM
bar_bar,
bar bar1,
bar bar2
WHERE
bar_bar.id1_bar=bar1.id_bar AND
bar_bar.id2_bar=bar2.id_bar;

CREATE RULE
foo_ins
AS ON INSERT TO
foo
DO INSTEAD (
INSERT INTO bar (bar) VALUES (NEW.bar1);
INSERT INTO bar (bar) VALUES (NEW.bar2);
INSERT INTO bar_bar (id1_bar, id2_bar) VALUES (
currval_of_1st_insert('public.bar_id_bar_seq'::text),
currval_of_2nd_insert('public.bar_id_bar_seq'::text)
)
);

TIA
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : www.e-trolley.de

hi all, is there an optimal linux file system choice
for a system that will be used as a db server?

i seem to recall having read somewhere that there were
at least two choices (one being journaling) and one
set up was best for file management (desktop) and
another was best for database management.

tia...

ps - yeah, i know. i recently joined
linuxquestions.org so they will be working me through
most of the linux issues going forward. ;-) i think
this is question is relevant to novice pgsql users, though.

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

On Thu, 1 Sep 2005, operationsengineer1(at)yahoo(dot)com wrote:

> hi all, is there an optimal linux file system choice
> for a system that will be used as a db server?
>
> i seem to recall having read somewhere that there were
> at least two choices (one being journaling) and one
> set up was best for file management (desktop) and
> another was best for database management.

I am generally biased towards SGI's XFS. Otherwise JFS and Reiserfs may
also be good candidates.

--
Aly S.P Dharshi
aly(dot)dharshi(at)telus(dot)net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

OE,

> hi all, is there an optimal linux file system choice
> for a system that will be used as a db server?

Well, XFS and JFS offer some advantages. However, I think support for them
under Linux is still flaky.

For Reiser and Ext3, it's about neck-and-neck; Reiser does marginally better
with lots of small tables, otherwise worse. In either case, the important
thing is to remember to set the lowest level of journalling
( data=writeback ) otherwise you take a huge performance hit for the
journalling.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Hello All,

> Well, XFS and JFS offer some advantages. However, I think support for them
> under Linux is still flaky.

Under OSes from RedHat yes. SuSE, Ubuntu and friends have way better support
for it.

Aly.
--
Aly S.P Dharshi
aly(dot)dharshi(at)telus(dot)net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"