Обсуждение: last UPDATE or INSERT time of a table?
Is there a way to check the last time a table was UPDATEd or INSERTEd?
Is there a timestamp somewhere in the system tables?
(If not that would be really useful to help in web page cacheing and
expiration to learn if any change occured on the data)
Thanks in advance,
--
HIPPOLYTE: Mais si quelque vertu m'est tomb�e en partage,
Seigneur, je crois surtout avoir fait �clater
La haine des forfaits qu'on ose m'imputer.
(Ph�dre, J-B Racine, acte 4, sc�ne 2)
From: "Louis-David Mitterrand" <cunctator@apartia.ch> > Is there a way to check the last time a table was UPDATEd or INSERTEd? > Is there a timestamp somewhere in the system tables? > > (If not that would be really useful to help in web page cacheing and > expiration to learn if any change occured on the data) > > Thanks in advance, You can set up a trigger. See my previous post RFC: automatic "lastchange"... for some more info, or search the archives. - Richard Huxton
On Thu, Feb 15, 2001 at 03:09:16PM -0000, Richard Huxton wrote:
> From: "Louis-David Mitterrand" <cunctator@apartia.ch>
>
> > Is there a way to check the last time a table was UPDATEd or INSERTEd?
> > Is there a timestamp somewhere in the system tables?
> >
> > (If not that would be really useful to help in web page cacheing and
> > expiration to learn if any change occured on the data)
> >
> > Thanks in advance,
>
> You can set up a trigger. See my previous post RFC: automatic
> "lastchange"... for some more info, or search the archives.
Yes, I have read that thread qnd implemented a similar trigger in the
past.
What I meant to do is detect a change at the _table_ level, not the row
level. Is there such a field somewhere in the pg_tables?
--
THESEE: Mais � te condamner tu m'as trop engag�.
Jamais p�re en effet fut-il plus outrag� ?
(Ph�dre, J-B Racine, acte 4, sc�ne 3)
Louis-David Mitterrand wrote: > > On Thu, Feb 15, 2001 at 03:09:16PM -0000, Richard Huxton wrote: > > From: "Louis-David Mitterrand" <cunctator@apartia.ch> > > > > > Is there a way to check the last time a table was UPDATEd or INSERTEd? > > > Is there a timestamp somewhere in the system tables? > > > > > > (If not that would be really useful to help in web page cacheing and > > > expiration to learn if any change occured on the data) > > > > > > Thanks in advance, > > > > You can set up a trigger. See my previous post RFC: automatic > > "lastchange"... for some more info, or search the archives. > > Yes, I have read that thread qnd implemented a similar trigger in the > past. > > What I meant to do is detect a change at the _table_ level, not the row > level. Is there such a field somewhere in the pg_tables? > You could check the date on the file. But a vacuum might alter that. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
From: "Louis-David Mitterrand" <cunctator@apartia.ch>
> On Thu, Feb 15, 2001 at 03:09:16PM -0000, Richard Huxton wrote:
> > From: "Louis-David Mitterrand" <cunctator@apartia.ch>
> >
> > > Is there a way to check the last time a table was UPDATEd or INSERTEd?
> > > Is there a timestamp somewhere in the system tables?
> > >
[snipped]
>
> Yes, I have read that thread qnd implemented a similar trigger in the
> past.
>
> What I meant to do is detect a change at the _table_ level, not the row
> level. Is there such a field somewhere in the pg_tables?
Ah - sorry - misunderstood.
I don't know of any field in the system tables. In fact, I can't see any
timestamps on any system tables and you'd have thought there would be some.
You could do some hack by checking the modified date on the individual
database files (no, I don't like it either).
Or, you could set up a "table_last_modified" table recording (table_name
text, last_mod timestamp) and use a trigger on each table you want to
monitor something like:
CREATE TRIGGER ... UPDATE OR INSERT ... ON FOO ...
UPDATE table_last_modified SET last_mod=now() WHERE table_name='foo'
Probably more effort than you were looking for, but should work.
- Richard Huxton
On Thu, Feb 15, 2001 at 04:45:02PM -0500, Joseph Shraibman wrote:
> > Yes, I have read that thread qnd implemented a similar trigger in the
> > past.
> >
> > What I meant to do is detect a change at the _table_ level, not the row
> > level. Is there such a field somewhere in the pg_tables?
> >
>
> You could check the date on the file. But a vacuum might alter that.
Hmm, I see many files in /var/lib/postgres/data/base but how do I
recognize which file contains what table?
--
ARICIE: De votre injuste haine il n'a pas h�rit� ;
Il ne me traitait point comme une criminelle.
(Ph�dre, J-B Racine, acte 5, sc�ne 3)
On Thu, Feb 15, 2001 at 08:04:44PM -0000, Richard Huxton wrote:
> From: "Louis-David Mitterrand" <cunctator@apartia.ch>
>
>
> > On Thu, Feb 15, 2001 at 03:09:16PM -0000, Richard Huxton wrote:
> > > From: "Louis-David Mitterrand" <cunctator@apartia.ch>
> > >
> > > > Is there a way to check the last time a table was UPDATEd or INSERTEd?
> > > > Is there a timestamp somewhere in the system tables?
> > > >
> [snipped]
> >
> > Yes, I have read that thread qnd implemented a similar trigger in the
> > past.
> >
> > What I meant to do is detect a change at the _table_ level, not the row
> > level. Is there such a field somewhere in the pg_tables?
>
> Ah - sorry - misunderstood.
>
> I don't know of any field in the system tables. In fact, I can't see any
> timestamps on any system tables and you'd have thought there would be some.
> You could do some hack by checking the modified date on the individual
> database files (no, I don't like it either).
But which files? Are they individualized by table? If not, one would
have to fall back on a _database_ modification time, not a table one.
It would be nice to have that information somewhere. What do Pg
developers think?
> Or, you could set up a "table_last_modified" table recording (table_name
> text, last_mod timestamp) and use a trigger on each table you want to
> monitor something like:
>
> CREATE TRIGGER ... UPDATE OR INSERT ... ON FOO ...
> UPDATE table_last_modified SET last_mod=now() WHERE table_name='foo'
>
> Probably more effort than you were looking for, but should work.
Yes, that's what I'll probably implement after all, now that it is
confirmed that there is no way to obtain that info from the system. Not
that much work.
Thanks for your help, cheers,
--
THESEE: Fuis, tra�tre. Ne viens point braver ici ma haine,
Et tenter un courroux que je retiens � peine.
(Ph�dre, J-B Racine, acte 4, sc�ne 2)
Louis-David Mitterrand <cunctator@apartia.ch> writes:
> What I meant to do is detect a change at the _table_ level, not the row
> level. Is there such a field somewhere in the pg_tables?
There is not.
>> You could do some hack by checking the modified date on the individual
>> database files (no, I don't like it either).
This will not work. The last file update time as seen by the Unix
kernel may be later than the last logical update of the table contents,
due to delayed update of tuple commit status bits and suchlike. Not to
mention VACUUM, rolled-back transactions, etc. Under WAL it gets worse:
we may actually postpone data-file writes as long as we can (since we
know it's written to the WAL logfile), so the kernel update time might
also be older than the last committed transaction for the table.
On top of that, a solution based on looking into the database directory
is not available remotely, nor to anyone not logged in as the postgres
user.
I like the recommendation someone else made: add a trigger that writes
an update to some other table whenever you change the table of interest.
regards, tom lane
In 7.0 it is rather obvious. In 7.1 they moved to a number based scheme. I suggested they make a symlink with the name of the table pointing at the numbered file, but I don't think anyone paid attention. The info is in one of the pg_???? tables, I don't know which one offhand. Louis-David Mitterrand wrote: > > On Thu, Feb 15, 2001 at 04:45:02PM -0500, Joseph Shraibman wrote: > > > Yes, I have read that thread qnd implemented a similar trigger in the > > > past. > > > > > > What I meant to do is detect a change at the _table_ level, not the row > > > level. Is there such a field somewhere in the pg_tables? > > > > > > > You could check the date on the file. But a vacuum might alter that. > > Hmm, I see many files in /var/lib/postgres/data/base but how do I > recognize which file contains what table? > > -- > ARICIE: De votre injuste haine il n'a pas hérité ; > Il ne me traitait point comme une criminelle. > (Phèdre, J-B Racine, acte 5, scène 3) -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Hello, I'm trying to do some store procedure in pl/pgsql but the sql server says me: Error: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. I´m using Red Hat Linux 7.0 and Postgress 7.02. i executed: #createlang plpgsql dbhtc createlang: missing required argument PGLIB directory (This is the directory where the interpreter for the procedural language is stored. Traditionally, these are installed in whatever 'lib' directory was specified at configure time.) any help ?
From: "Juan Ramón Cortabitarte" <jcorta@tribctas.gba.gov.ar> > Error: ERROR: Unrecognized language specified in a CREATE FUNCTION: > 'plpgsql'. Recognized languages are sql, C, internal and the created > procedural languages. > > I´m using Red Hat Linux 7.0 and Postgress 7.02. > i executed: > > #createlang plpgsql dbhtc Try doing this as user postgres rather than root - that's why it can't see PGLIB environment var. > createlang: missing required argument PGLIB directory > (This is the directory where the interpreter for the procedural > language is stored. Traditionally, these are installed in whatever > 'lib' directory was specified at configure time.) - Richard Huxton
Juan Ramón Cortabitarte wrote: > Hello, > > I'm trying to do some store procedure in pl/pgsql but the sql server says > me: > > Error: ERROR: Unrecognized language specified in a CREATE FUNCTION: > 'plpgsql'. Recognized languages are sql, C, internal and the created > procedural languages. > > I´m using Red Hat Linux 7.0 and Postgress 7.02. > i executed: > > #createlang plpgsql dbhtc > > createlang: missing required argument PGLIB directory createlang --username=postgres --dbname=yourdb --pglib=/usr/lib/pgsql plpgsql yourdb If you don't see plpgsql.so in PGLIB directory, then you better re-install your PostgreSQL.