Обсуждение: knowing last modification / last access times of tables..

Hi ,

we need to get rid of lots of old tables which
we suspect are not in use.

Is there any way to know when a table was last read
or modifed ?


does seeing the access info of the datafiles corresponding to these
tables help ?


Regds
mallah.


--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

I am new to these lists and this message may be off-topic for this list. If
so, please advise and I will repost to one of the others. Here is the
question:

We have been running Postgres 7.2.x for our website, and are considering the
upgrade to 7.3.x. However, in reading through the changelogs I notice that
SERIAL values are no longer unique. Now, I'm no SQL guru, but I'm wondering
what happens if two INSERTs are done at the "same" time (we have multiple
web hosts that access one large database server). Would they result in two
identical values on the SERIAL? We haven't gotten a stupendous amount of
traffic, so I don't know what the odds of this are, but I'm wondering why it
was removed.

I really wanted the "DROP COLUMN" command which is available in 7.3.x, but
it seems I'm going to have to remember some really arcane code to add the
UNIQUE to the sequences. I'm wondering how much reworking of my code I'm
going to have to do to upgrade, and whether I should postpone this.
Evaluating the impact of the SERIAL change would be helpful to me, if anyone
could post a response.

Thanks,
Justin Long
www.strategicnetwork.org

On Wed, Apr 30, 2003 at 09:33:03AM -0400, Justin Long wrote:

> We have been running Postgres 7.2.x for our website, and are considering the
> upgrade to 7.3.x. However, in reading through the changelogs I notice that
> SERIAL values are no longer unique. Now, I'm no SQL guru, but I'm wondering

SERIAL _columns_ no longer automatically get a unique index on them.
You need to add one if you want to ensure uniqueness there: CREATE
UNIQUE INDEX. . .

> what happens if two INSERTs are done at the "same" time (we have multiple
> web hosts that access one large database server). Would they result in two
> identical values on the SERIAL? We haven't gotten a stupendous amount of

No.  The underlying _sequence_, which is what those inserts call, is
still transaction- (and connection-) aware, so you won't have this
problem.

I don't actually recall the argument for removing the automatic
unique index.  But it's easy enough at the time of creation, since
you could specify a UNIQUE constraint on the column anyway.

When you upgrade, you should get the unique index, since your dump
will contain it.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Wed, 30 Apr 2003, Justin Long wrote:

> I am new to these lists and this message may be off-topic for this list. If
> so, please advise and I will repost to one of the others. Here is the
> question:
>
> We have been running Postgres 7.2.x for our website, and are considering the
> upgrade to 7.3.x. However, in reading through the changelogs I notice that
> SERIAL values are no longer unique. Now, I'm no SQL guru, but I'm wondering
> what happens if two INSERTs are done at the "same" time (we have multiple
> web hosts that access one large database server). Would they result in two
> identical values on the SERIAL? We haven't gotten a stupendous amount of
> traffic, so I don't know what the odds of this are, but I'm wondering why it
> was removed.

The values from the sequence still should be unique (even for two inserts
at the "same" time) -- it's still a sequence, but it doesn't also add a
unique constraint to the table in addition.  You can add a unique
constraint to the table with alter table if it's important (like the fact
that you expect there to be an index on that or you want to reference that
column and didn't specify it as unique/primary key).

"Justin Long" <justinlong@strategicnetwork.org> writes:
> We have been running Postgres 7.2.x for our website, and are considering the
> upgrade to 7.3.x. However, in reading through the changelogs I notice that
> SERIAL values are no longer unique.

You misread it.  SERIAL does not automatically create a unique index on
the column anymore, but the sequence mechanism hasn't changed.  If you
want a unique index, you just say "SERIAL UNIQUE" instead of "SERIAL".
The index is mainly useful as a backstop to guarantee you don't mess up
(eg, by manually inserting values into the column rather than letting
them be taken from the sequence).  Some might see it as unnecessary
overhead.

            regards, tom lane

> From: Andrew Sullivan <andrew@libertyrms.info>
> I don't actually recall the argument for removing the automatic
> unique index.  But it's easy enough at the time of creation, since
> you could specify a UNIQUE constraint on the column anyway.

Well, I've no idea what the PostgreSQL's particular argument might have been,
but too many indices typically means slower lookups. You want the indices to
be created based on the fields you will most often mention in WHERE statements.

A unique serial number is always useful, but might not be looked at unless the
the final output is a surprising number of records, like two, where one is
expected. No point in indexing it then.

--
Music is a quality, organised in sound and in time.
http://www.hacksaw.org -- http://www.privatecircus.com -- KB1FVD

On Thu, May 01, 2003 at 07:35:15PM -0400, Hacksaw wrote:

> Well, I've no idea what the PostgreSQL's particular argument might
> have been, but too many indices typically means slower lookups. You
> want the indices to be created based on the fields you will most
> often mention in WHERE statements.

I meant the argument on -hackers, but this one that you present might
well be the one that was used.  In any case, just on grounds of
flexibility I think it's a good thing, especially since the old
behaviour can be so easily emulated if desired.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110