pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Continuous inserts...
15+ messages / 7 participants
[nested] [flat]

* Continuous inserts...
@ 2000-08-17 14:30  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:50  Poul L. Christiansen <plc@faroenet.fo>
  parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.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 16:15  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.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 18:38  Webb Sprague <wsprague100@yahoo.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-18 01:41  brianb-pgsql@edsamail.com
  parent: Poul L. Christiansen <plc@faroenet.fo>
  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-18 07:59  Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
  parent: 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-18 08:03  Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
  parent: Webb Sprague <wsprague100@yahoo.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

* Re: Continuous inserts...
@ 2000-08-18 10:57  Poul L. Christiansen <plc@faroenet.fo>
  parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
  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-18 11:21  Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
  parent: 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-18 15:18  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.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-22 07:44  Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
  parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.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-22 15:59  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.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-22 18:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <janwieck@Yahoo.com>
  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-22 18:31  Jan Wieck <janwieck@Yahoo.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.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-23 08:10  Joerg Hessdoerfer <Joerg.Hessdoerfer@sea-gmbh.com>
  parent: Jan Wieck <janwieck@Yahoo.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


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