pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedContinuous inserts...
15+ messages / 7 participants
[nested] [flat]
* Continuous inserts...
@ 2000-08-17 14:30 Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-17 14:50 ` Re: Continuous inserts... Poul L. Christiansen <plc@faroenet.fo>
2000-08-17 16:15 ` Re: Continuous inserts... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 3 replies; 15+ messages in thread
From: Joerg Hessdoerfer @ 2000-08-17 14:30 UTC (permalink / raw)
To: pgsql-sql
Hi!
I have an application, where I have to insert data into a table at several
rows per second, 24 hours a day, 365 days a year.
After some period (a week, maybe a month) the data will be reducted to some
degree and deleted from the table.
As far as I understood, I would have to use VACUUM to really free the table
from deleted rows - but VACUUM (esp. on a table with several million rows)
takes some time and prevents me from inserting new data.
Now, I thought I could just rename the table, inserting into a temp table, and
switch the tables back after VACUUMing. Ideally, this should work unnoticed
(and thus without prog. effort) on the client (inserter) side.
Question: would it work to use a transaction to perform the rename?
i.e.: continuous insert into table 'main' from client.
From somewhere else, execute:
begin;
alter table main rename to vac_main;
create table main (...);
end;
would the inserter notice this? Read: would ALL inserts AT ANY TIME succeed?
I know, I could simulate such functionality in the client (inserter). But it
seems more elegant this way...
Greetings,
Joerg
+------**** Science & Engineering Applications GmbH ****------+
| |
| Joerg Hessdoerfer |
| Leading SW developer Phone: +49 (0)2203-962211 |
| S.E.A GmbH Fax: -962212 |
| D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
| http://www.sea-gmbh.com |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-17 14:50 ` Poul L. Christiansen <plc@faroenet.fo>
2000-08-18 01:41 ` Re: Continuous inserts... brianb-pgsql@edsamail.com
2 siblings, 1 reply; 15+ messages in thread
From: Poul L. Christiansen @ 2000-08-17 14:50 UTC (permalink / raw)
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: pgsql-sql
Isn't easier to reduce the table every day and make a daily vacuum which only
lasts a few seconds?
Joerg Hessdoerfer wrote:
> Hi!
>
> I have an application, where I have to insert data into a table at several
> rows per second, 24 hours a day, 365 days a year.
>
> After some period (a week, maybe a month) the data will be reducted to some
> degree and deleted from the table.
>
> As far as I understood, I would have to use VACUUM to really free the table
> from deleted rows - but VACUUM (esp. on a table with several million rows)
> takes some time and prevents me from inserting new data.
>
> Now, I thought I could just rename the table, inserting into a temp table, and
> switch the tables back after VACUUMing. Ideally, this should work unnoticed
> (and thus without prog. effort) on the client (inserter) side.
>
> Question: would it work to use a transaction to perform the rename?
>
> i.e.: continuous insert into table 'main' from client.
>
> From somewhere else, execute:
>
> begin;
> alter table main rename to vac_main;
> create table main (...);
> end;
>
> would the inserter notice this? Read: would ALL inserts AT ANY TIME succeed?
>
> I know, I could simulate such functionality in the client (inserter). But it
> seems more elegant this way...
>
> Greetings,
> Joerg
> +------**** Science & Engineering Applications GmbH ****------+
> | |
> | Joerg Hessdoerfer |
> | Leading SW developer Phone: +49 (0)2203-962211 |
> | S.E.A GmbH Fax: -962212 |
> | D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
> | http://www.sea-gmbh.com |
> +---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-17 14:50 ` Re: Continuous inserts... Poul L. Christiansen <plc@faroenet.fo>
@ 2000-08-18 01:41 ` brianb-pgsql@edsamail.com
0 siblings, 0 replies; 15+ messages in thread
From: brianb-pgsql@edsamail.com @ 2000-08-18 01:41 UTC (permalink / raw)
To: Poul L. Christiansen <plc@faroenet.fo>; +Cc: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; pgsql-sql
Poul L. Christiansen writes:
> Isn't easier to reduce the table every day and make a daily vacuum which only
> lasts a few seconds?
I doubt that it would last just a few seconds. From my experience, VACUUM
on large tables can sap your I/O subsystem, slowing down overall
performance for everyone else.
Joerg, if this is a logging-type application, you may want to consider
creating new tables periodically, e.g. rawdata_YYYY_MM or rawdata_WEEKNO
and put a little more logic into your app to correctly name the table to
perform the INSERT on. The rawdata_YYYY_MM tables should be created in
advance, of course.
You can then safely post-process last month's data, insert results into a
much smaller postprocess_YYYY_MM table, then archive or drop
rawdata_YYYY_MM altogether.
Perhaps my suggestions are coloured by my experiences w/ 6.5, but this
seems to be the safest way to do it without losing data.
Alternately, you could log data to flat files, post-process and then INSERT
into Postgres.
Brian
> Joerg Hessdoerfer wrote:
>
> > Hi!
> >
> > I have an application, where I have to insert data into a table at several
> > rows per second, 24 hours a day, 365 days a year.
> >
> > After some period (a week, maybe a month) the data will be reducted to some
> > degree and deleted from the table.
> >
> > As far as I understood, I would have to use VACUUM to really free the table
> > from deleted rows - but VACUUM (esp. on a table with several million rows)
> > takes some time and prevents me from inserting new data.
> >
> > Now, I thought I could just rename the table, inserting into a temp table, and
> > switch the tables back after VACUUMing. Ideally, this should work unnoticed
> > (and thus without prog. effort) on the client (inserter) side.
> >
> > Question: would it work to use a transaction to perform the rename?
> >
> > i.e.: continuous insert into table 'main' from client.
> >
> > From somewhere else, execute:
> >
> > begin;
> > alter table main rename to vac_main;
> > create table main (...);
> > end;
> >
> > would the inserter notice this? Read: would ALL inserts AT ANY TIME succeed?
> >
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/ AIM: bbaquiran
Work: +63(2)7182222 Home: +63(2) 9227123
I'm smarter than average. Therefore, average, to me, seems kind of stupid.
People weren't purposely being stupid. It just came naturally.
-- Bruce "Tog" Toganazzini
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-17 16:15 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2000-08-17 16:15 UTC (permalink / raw)
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: pgsql-sql
On Thu, 17 Aug 2000, Joerg Hessdoerfer wrote:
> Hi!
>
> I have an application, where I have to insert data into a table at several
> rows per second, 24 hours a day, 365 days a year.
>
> After some period (a week, maybe a month) the data will be reducted to some
> degree and deleted from the table.
>
> As far as I understood, I would have to use VACUUM to really free the table
> from deleted rows - but VACUUM (esp. on a table with several million rows)
> takes some time and prevents me from inserting new data.
>
> Now, I thought I could just rename the table, inserting into a temp table, and
> switch the tables back after VACUUMing. Ideally, this should work unnoticed
> (and thus without prog. effort) on the client (inserter) side.
>
> Question: would it work to use a transaction to perform the rename?
>
> i.e.: continuous insert into table 'main' from client.
>
> From somewhere else, execute:
>
> begin;
> alter table main rename to vac_main;
> create table main (...);
> end;
>
> would the inserter notice this? Read: would ALL inserts AT ANY TIME succeed?
Unfortunately -- no. Also, bad things can happen if the transaction
errors since the rename happens immediately. There's been talk on
-hackers about this subject in the past.
However, you might be able to do something like this, but
I'm not sure it'll work and it's rather wierd:
Have three tables you work with, a and b and c
Set up rule on a to change insert to insert on b.
Insert into a.
When you want to vacuum, change the rule to insert to c.
Vacuum b
Change rule back
move rows from a and c into b
vacuum c
[you will slowly lose space in a, but it should only
be an occasional row since you should only insert into
a while you've deleted the insert rule to b, but
haven't yet added the insert rule to c -- not too many
rows here]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-18 07:59 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 10:57 ` Re: Continuous inserts... Poul L. Christiansen <plc@faroenet.fo>
2000-08-18 11:21 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 15:18 ` Re: Continuous inserts... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-22 07:44 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2 siblings, 4 replies; 15+ messages in thread
From: Joerg Hessdoerfer @ 2000-08-18 07:59 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi!
Thanks all for your input...
At 09:15 17.08.00 -0700, you wrote:
[...]
> > Question: would it work to use a transaction to perform the rename?
> >
> > i.e.: continuous insert into table 'main' from client.
> >
> > From somewhere else, execute:
> >
> > begin;
> > alter table main rename to vac_main;
> > create table main (...);
> > end;
> >
> > would the inserter notice this? Read: would ALL inserts AT ANY TIME
> succeed?
>
>Unfortunately -- no. Also, bad things can happen if the transaction
>errors since the rename happens immediately. There's been talk on
>-hackers about this subject in the past.
>
>However, you might be able to do something like this, but
>I'm not sure it'll work and it's rather wierd:
>
>Have three tables you work with, a and b and c
>
>Set up rule on a to change insert to insert on b.
>Insert into a.
>When you want to vacuum, change the rule to insert to c.
>Vacuum b
>Change rule back
>move rows from a and c into b
>vacuum c
[...]
Good idea - I immediately tested it - rules rule! That seems to work perfectly,
and the client doesn't even see it happen (except for 'selects', one would
have to setup
a rule to return something meaningful then...).
I did:
Two tables, a and b.
Normally, insert into a.
When Vacuuming starts, create rule on a to insert into b
Vacuum a
drop rule
copy records from b to a
vacuum b
Why did you suppose three tables? Did I overlook something?
Greetings,
Joerg
+------**** Science & Engineering Applications GmbH ****------+
| |
| Joerg Hessdoerfer |
| Leading SW developer Phone: +49 (0)2203-962211 |
| S.E.A GmbH Fax: -962212 |
| D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
| http://www.sea-gmbh.com |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-18 10:57 ` Poul L. Christiansen <plc@faroenet.fo>
3 siblings, 0 replies; 15+ messages in thread
From: Poul L. Christiansen @ 2000-08-18 10:57 UTC (permalink / raw)
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql
I'm not familiar with rules. Could you please post the SQL for creating the rule
that you've created?
I going to make such a setup in the near future and this seems to .
Joerg Hessdoerfer wrote:
> Hi!
>
> Thanks all for your input...
>
> At 09:15 17.08.00 -0700, you wrote:
> [...]
> > > Question: would it work to use a transaction to perform the rename?
> > >
> > > i.e.: continuous insert into table 'main' from client.
> > >
> > > From somewhere else, execute:
> > >
> > > begin;
> > > alter table main rename to vac_main;
> > > create table main (...);
> > > end;
> > >
> > > would the inserter notice this? Read: would ALL inserts AT ANY TIME
> > succeed?
> >
> >Unfortunately -- no. Also, bad things can happen if the transaction
> >errors since the rename happens immediately. There's been talk on
> >-hackers about this subject in the past.
> >
> >However, you might be able to do something like this, but
> >I'm not sure it'll work and it's rather wierd:
> >
> >Have three tables you work with, a and b and c
> >
> >Set up rule on a to change insert to insert on b.
> >Insert into a.
> >When you want to vacuum, change the rule to insert to c.
> >Vacuum b
> >Change rule back
> >move rows from a and c into b
> >vacuum c
> [...]
>
> Good idea - I immediately tested it - rules rule! That seems to work perfectly,
> and the client doesn't even see it happen (except for 'selects', one would
> have to setup
> a rule to return something meaningful then...).
>
> I did:
> Two tables, a and b.
> Normally, insert into a.
> When Vacuuming starts, create rule on a to insert into b
> Vacuum a
> drop rule
> copy records from b to a
> vacuum b
>
> Why did you suppose three tables? Did I overlook something?
>
> Greetings,
> Joerg
> +------**** Science & Engineering Applications GmbH ****------+
> | |
> | Joerg Hessdoerfer |
> | Leading SW developer Phone: +49 (0)2203-962211 |
> | S.E.A GmbH Fax: -962212 |
> | D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
> | http://www.sea-gmbh.com |
> +---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-18 11:21 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
3 siblings, 0 replies; 15+ messages in thread
From: Joerg Hessdoerfer @ 2000-08-18 11:21 UTC (permalink / raw)
To: pgsql-sql
Hi!
At 11:57 18.08.00 +0100, you wrote:
>I'm not familiar with rules. Could you please post the SQL for creating
>the rule
>that you've created?
Here we go (if memory serves ;-)
create table a ( num int4, name text );
create table b ( num int4, name text );
rule to insert into b instead of a:
CREATE RULE redirect AS ON insert TO a DO INSTEAD insert into b values (
new.num, new.name );
... the INSTEAD is important!
BTW: is it really necessary to list all fields in the instead part? Anyone?
when finished vacuuming a, do a
DROP RULE redirect;
of course, when you have more/other fields in your table, you need to
change rule's definition.
Hope this helps,
Joerg
+------**** Science & Engineering Applications GmbH ****------+
| |
| Joerg Hessdoerfer |
| Leading SW developer Phone: +49 (0)2203-962211 |
| S.E.A GmbH Fax: -962212 |
| D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
| http://www.sea-gmbh.com |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-18 15:18 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
3 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2000-08-18 15:18 UTC (permalink / raw)
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: pgsql-sql
On Fri, 18 Aug 2000, Joerg Hessdoerfer wrote:
> Good idea - I immediately tested it - rules rule! That seems to work perfectly,
> and the client doesn't even see it happen (except for 'selects', one would
> have to setup
> a rule to return something meaningful then...).
>
> I did:
> Two tables, a and b.
> Normally, insert into a.
> When Vacuuming starts, create rule on a to insert into b
> Vacuum a
> drop rule
> copy records from b to a
> vacuum b
>
> Why did you suppose three tables? Did I overlook something?
I didn't try with vacuum, I just did a table lock and that
seemed to still hang the inserts with two tables, so I figured
maximum safety was adding the third table. If it works with two
that's much cooler. Was this with real data or just a small test
set?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-22 07:44 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 15:59 ` Re: Continuous inserts... Stephan Szabo <sszabo@megazone23.bigpanda.com>
3 siblings, 1 reply; 15+ messages in thread
From: Joerg Hessdoerfer @ 2000-08-22 07:44 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Hi!
At 08:18 18.08.00 -0700, you wrote:
[...]
>I didn't try with vacuum, I just did a table lock and that
>seemed to still hang the inserts with two tables, so I figured
>maximum safety was adding the third table. If it works with two
>that's much cooler. Was this with real data or just a small test
>set?
It was a test set ... ~20000 records, *BUT* I found that postgres
decides when it starts to use the rule - means, if you do continous
inserts on the table and create the rule, there's a varying time until
the rule applies. In my first tests, I re-connected the DB very often,
and the the change seemed immediate.
Any ideas on how to 'promote' the rules faster?!?
Greetings,
Joe
--
+------**** Science & Engineering Applications GmbH ****------+
| |
| Joerg Hessdoerfer |
| Leading SW developer Phone: +49 (0)2203-962211 |
| S.E.A GmbH Fax: -962212 |
| D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
| http://www.sea-gmbh.com |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 07:44 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
@ 2000-08-22 15:59 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-22 18:31 ` Re: Continuous inserts... Jan Wieck <janwieck@Yahoo.com>
0 siblings, 1 reply; 15+ messages in thread
From: Stephan Szabo @ 2000-08-22 15:59 UTC (permalink / raw)
To: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: pgsql-sql
Wierd, I've not seen that behavior really, although I've never
done time sensitive stuff. It might be the time before the
shared cache updates? Not sure really. If you do the rule
inline with your inserts (rather than a second transaction)
does it still wait?
Stephan Szabo
sszabo@bigpanda.com
On Tue, 22 Aug 2000, Joerg Hessdoerfer wrote:
> Hi!
>
> At 08:18 18.08.00 -0700, you wrote:
> [...]
>
> >I didn't try with vacuum, I just did a table lock and that
> >seemed to still hang the inserts with two tables, so I figured
> >maximum safety was adding the third table. If it works with two
> >that's much cooler. Was this with real data or just a small test
> >set?
>
> It was a test set ... ~20000 records, *BUT* I found that postgres
> decides when it starts to use the rule - means, if you do continous
> inserts on the table and create the rule, there's a varying time until
> the rule applies. In my first tests, I re-connected the DB very often,
> and the the change seemed immediate.
>
> Any ideas on how to 'promote' the rules faster?!?
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 07:44 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 15:59 ` Re: Continuous inserts... Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2000-08-22 18:31 ` Jan Wieck <janwieck@Yahoo.com>
2000-08-22 18:16 ` Re: Continuous inserts... Tom Lane <tgl@sss.pgh.pa.us>
2000-08-23 08:10 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 2 replies; 15+ messages in thread
From: Jan Wieck @ 2000-08-22 18:31 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; pgsql-sql
Stephan Szabo wrote:
> Wierd, I've not seen that behavior really, although I've never
> done time sensitive stuff. It might be the time before the
> shared cache updates? Not sure really. If you do the rule
> inline with your inserts (rather than a second transaction)
> does it still wait?
Just jumping in not having followed the discussion. But...
The rules applied to a table by the rewriter are taken out of
the relation descriptor that is returned by heap_open() or
heap_openr().
I haven't looked at the code, but pg_class only has a boolean
telling if a class has rules or not. Could it be that adding
more rules (or dropping just a few instead of all) doesn't
update the pg_class tuple, thus the syscache for the table
isn't invalidated and other backends continue to use the old
information instead of rescanning pg_rewrite?
Jan
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Tue, 22 Aug 2000, Joerg Hessdoerfer wrote:
>
> > Hi!
> >
> > At 08:18 18.08.00 -0700, you wrote:
> > [...]
> >
> > >I didn't try with vacuum, I just did a table lock and that
> > >seemed to still hang the inserts with two tables, so I figured
> > >maximum safety was adding the third table. If it works with two
> > >that's much cooler. Was this with real data or just a small test
> > >set?
> >
> > It was a test set ... ~20000 records, *BUT* I found that postgres
> > decides when it starts to use the rule - means, if you do continous
> > inserts on the table and create the rule, there's a varying time until
> > the rule applies. In my first tests, I re-connected the DB very often,
> > and the the change seemed immediate.
> >
> > Any ideas on how to 'promote' the rules faster?!?
> >
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 07:44 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 15:59 ` Re: Continuous inserts... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-22 18:31 ` Re: Continuous inserts... Jan Wieck <janwieck@Yahoo.com>
@ 2000-08-22 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 15+ messages in thread
From: Tom Lane @ 2000-08-22 18:16 UTC (permalink / raw)
To: Jan Wieck <janwieck@Yahoo.com>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; pgsql-sql
Jan Wieck <janwieck@Yahoo.com> writes:
> I haven't looked at the code, but pg_class only has a boolean
> telling if a class has rules or not. Could it be that adding
> more rules (or dropping just a few instead of all) doesn't
> update the pg_class tuple, thus the syscache for the table
> isn't invalidated and other backends continue to use the old
> information instead of rescanning pg_rewrite?
This is done correctly in current sources --- see
setRelhasrulesInRelation(). However I recall having dorked with that
code not long ago, and I forget what it looked like before. Perhaps
7.0.* is broken in this respect? Would think people would have noticed,
though.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 07:59 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 07:44 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 15:59 ` Re: Continuous inserts... Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-22 18:31 ` Re: Continuous inserts... Jan Wieck <janwieck@Yahoo.com>
@ 2000-08-23 08:10 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
1 sibling, 0 replies; 15+ messages in thread
From: Joerg Hessdoerfer @ 2000-08-23 08:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi!
At 14:16 22.08.00 -0400, you wrote:
>Jan Wieck <janwieck@Yahoo.com> writes:
> > I haven't looked at the code, but pg_class only has a boolean
> > telling if a class has rules or not. Could it be that adding
> > more rules (or dropping just a few instead of all) doesn't
> > update the pg_class tuple, thus the syscache for the table
> > isn't invalidated and other backends continue to use the old
> > information instead of rescanning pg_rewrite?
>
>This is done correctly in current sources --- see
>setRelhasrulesInRelation(). However I recall having dorked with that
>code not long ago, and I forget what it looked like before. Perhaps
>7.0.* is broken in this respect? Would think people would have noticed,
>though.
>
> regards, tom lane
Perhaps I should have mentioned that the test was done using 6.5.3 - I didn't
come around to upgrade yet...
Oh, yes, the mentioned rule was the ONLY rule on that database.
Greetings,
Joerg
--
+------**** Science & Engineering Applications GmbH ****------+
| |
| Joerg Hessdoerfer |
| Leading SW developer Phone: +49 (0)2203-962211 |
| S.E.A GmbH Fax: -962212 |
| D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
| http://www.sea-gmbh.com |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
@ 2000-08-17 18:38 Webb Sprague <wsprague100@yahoo.com>
2000-08-18 08:03 ` Re: Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 1 reply; 15+ messages in thread
From: Webb Sprague @ 2000-08-17 18:38 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>; +Cc: pgsql-sql
Hi All.
Shouldn't Postgres block while vacuuming, and then
continue inserting starting where it left off? Is the
time lag too much?
I am curious because I am going to build a similar app
soon, basically parsing and inserting log file
entries.
W
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
>
> On Thu, 17 Aug 2000, Joerg Hessdoerfer wrote:
>
> > Hi!
> >
> > I have an application, where I have to insert data
> into a table at several
> > rows per second, 24 hours a day, 365 days a year.
> >
> > After some period (a week, maybe a month) the data
> will be reducted to some
> > degree and deleted from the table.
> >
> > As far as I understood, I would have to use VACUUM
> to really free the table
> > from deleted rows - but VACUUM (esp. on a table
> with several million rows)
> > takes some time and prevents me from inserting new
> data.
> >
> > Now, I thought I could just rename the table,
> inserting into a temp table, and
> > switch the tables back after VACUUMing. Ideally,
> this should work unnoticed
> > (and thus without prog. effort) on the client
> (inserter) side.
> >
> > Question: would it work to use a transaction to
> perform the rename?
> >
> > i.e.: continuous insert into table 'main' from
> client.
> >
> > From somewhere else, execute:
> >
> > begin;
> > alter table main rename to vac_main;
> > create table main (...);
> > end;
> >
> > would the inserter notice this? Read: would ALL
> inserts AT ANY TIME succeed?
>
> Unfortunately -- no. Also, bad things can happen if
> the transaction
> errors since the rename happens immediately.
> There's been talk on
> -hackers about this subject in the past.
>
> However, you might be able to do something like
> this, but
> I'm not sure it'll work and it's rather wierd:
>
> Have three tables you work with, a and b and c
>
> Set up rule on a to change insert to insert on b.
> Insert into a.
> When you want to vacuum, change the rule to insert
> to c.
> Vacuum b
> Change rule back
> move rows from a and c into b
> vacuum c
>
> [you will slowly lose space in a, but it should only
> be an occasional row since you should only insert
> into
> a while you've deleted the insert rule to b, but
> haven't yet added the insert rule to c -- not too
> many
> rows here]
>
>
__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Continuous inserts...
2000-08-17 18:38 Re: Continuous inserts... Webb Sprague <wsprague100@yahoo.com>
@ 2000-08-18 08:03 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
0 siblings, 0 replies; 15+ messages in thread
From: Joerg Hessdoerfer @ 2000-08-18 08:03 UTC (permalink / raw)
To: Webb Sprague <wsprague100@yahoo.com>; +Cc: pgsql-sql
hi!
At 11:38 17.08.00 -0700, you wrote:
>Hi All.
>
>Shouldn't Postgres block while vacuuming, and then
>continue inserting starting where it left off? Is the
>time lag too much?
For me - yes. My app can accept some hundredes of ms time lag - not
seconds or, like with a VACUUM, minutes (I've seen it taking hours,
but that was a _LARGE_ table on 6.5.x).
>I am curious because I am going to build a similar app
>soon, basically parsing and inserting log file
>entries.
Rules do the trick - insert into a second table unnoticed by the inserter
client. See my previous posting for slightly more detail.
Greetings,
Joerg
+------**** Science & Engineering Applications GmbH ****------+
| |
| Joerg Hessdoerfer |
| Leading SW developer Phone: +49 (0)2203-962211 |
| S.E.A GmbH Fax: -962212 |
| D-51147 Koeln Internet: joerg.hessdoerfer@sea-gmbh.com |
| http://www.sea-gmbh.com |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2000-08-23 08:10 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-17 14:30 Continuous inserts... Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-17 14:50 ` Poul L. Christiansen <plc@faroenet.fo>
2000-08-18 01:41 ` brianb-pgsql@edsamail.com
2000-08-17 16:15 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-18 07:59 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 10:57 ` Poul L. Christiansen <plc@faroenet.fo>
2000-08-18 11:21 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-18 15:18 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-22 07:44 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-22 15:59 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-22 18:31 ` Jan Wieck <janwieck@Yahoo.com>
2000-08-22 18:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-23 08:10 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
2000-08-17 18:38 Re: Continuous inserts... Webb Sprague <wsprague100@yahoo.com>
2000-08-18 08:03 ` Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox