Thread: Vacuum and Owner

I run as user "spry" on psql, I created the database as "spry" but when I
run vacuum, all the system tables get skipped because "spry" is not the
owner. Is there a way to fully vacuum my db and the system tables as a user
other than "postgres"?

Is is very important that the system tables are "vaccumed" in addition to my
db tables?

Matt Friedman

From: "Matt Friedman" <matt@daart.ca>


> I run as user "spry" on psql, I created the database as "spry" but when I
> run vacuum, all the system tables get skipped because "spry" is not the
> owner. Is there a way to fully vacuum my db and the system tables as a
user
> other than "postgres"?

Don't think this is possible. You could grant the relevant permissions to
user spry but that's probably not a good idea.

> Is is very important that the system tables are "vaccumed" in addition to
my
> db tables?

Depends how much activity there is in the system tables. If you are
creating/dropping lots of tables/functions etc then they'll need vacuuming
regularly. In general, I'd guess most people need to vacuum system tables
much less often than their data tables.

Perhaps set up a weekly cron job to vacuum the system tables as user
postgres.

- Richard Huxton

Hello!

I have a table, with an update trigger (it change the value of
'last_modify' field to current timestamp)
Sometimes if i try delete rows in this table (before delete, there are some
inserts after a 'begin') i get this change violation error. I don't now
why.
Can somebody help me?

Thank's
--
 nek.

(postgresql 7.0.2)

I have a similiar problem.

I should first say I'm learning about databases and SQL as I go along so
what I'm trying to do may be just plain silly (but it seems OK to me).

My problem can best be explained by  this script ...

CREATE TABLE test (
id INTEGER PRIMARY KEY,
value VARCHAR(25)
);

CREATE TABLE test2 (
id INTEGER NOT NULL REFERENCES test(id) ON DELETE CASCADE
);

INSERT INTO test VALUES ( 2, 'TWO' );
INSERT INTO test2 VALUES ( 2 );
BEGIN;
INSERT INTO test VALUES ( 4, 'FOUR' );
INSERT INTO test2 VALUES ( 4 );
DELETE FROM test WHERE id = 2;                    - this delete occurs OK
DELETE FROM test WHERE id = 4;
                   - this delete causes "ERROR:  triggered data change
violation on relation "test"
COMMIT;

I'm adding then deleting the same rows within a transaction. Should I be able
to do this? It
works fine if I change test2 to

CREATE TABLE test2 (
id INTEGER
);

which suggests the problem is with triggers.

regards, Alasdair.


Peter Vazsonyi wrote:

> Hello!
>
> I have a table, with an update trigger (it change the value of
> 'last_modify' field to current timestamp)
> Sometimes if i try delete rows in this table (before delete, there are some
> inserts after a 'begin') i get this change violation error. I don't now
> why.
> Can somebody help me?
>
> Thank's
> --
>  nek.
>
> (postgresql 7.0.2)

(Apologies if this is appears three times ...)

I think the problem we're having is the same as described here ...

http://www.postgresql.org/docs/pgsql/doc/TODO.detail/foreign

regards, Alasdair.

Peter Vazsonyi wrote:


Alasdair I MacLeod wrote:

> I have a similiar problem.
>
> I should first say I'm learning about databases and SQL as I go along so
> what I'm trying to do may be just plain silly (but it seems OK to me).
>
> My problem can best be explained by  this script ...
>
> CREATE TABLE test (
> id INTEGER PRIMARY KEY,
> value VARCHAR(25)
> );
>
> CREATE TABLE test2 (
> id INTEGER NOT NULL REFERENCES test(id) ON DELETE CASCADE
> );
>
> INSERT INTO test VALUES ( 2, 'TWO' );
> INSERT INTO test2 VALUES ( 2 );
> BEGIN;
> INSERT INTO test VALUES ( 4, 'FOUR' );
> INSERT INTO test2 VALUES ( 4 );
> DELETE FROM test WHERE id = 2;                    - this delete occurs OK
> DELETE FROM test WHERE id = 4;
>                    - this delete causes "ERROR:  triggered data change
> violation on relation "test"
> COMMIT;
>
> I'm adding then deleting the same rows within a transaction. Should I be able
> to do this? It
> works fine if I change test2 to
>
> CREATE TABLE test2 (
> id INTEGER
> );
>
> which suggests the problem is with triggers.
>
> regards, Alasdair.
>
> Peter Vazsonyi wrote:
>
> > Hello!
> >
> > I have a table, with an update trigger (it change the value of
> > 'last_modify' field to current timestamp)
> > Sometimes if i try delete rows in this table (before delete, there are some
> > inserts after a 'begin') i get this change violation error. I don't now
> > why.
> > Can somebody help me?
> >
> > Thank's
> > --
> >  nek.
> >
> > (postgresql 7.0.2)