Re: Schema unique stamp (OID, MD5?)

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 24 February 2002 15:40
> To: Dave Page; pgadmin-hackers@postgresql.org
> Subject: Re: [pgadmin-hackers] Schema unique stamp (OID, MD5?)
>
>
> Dear Dave,
>
> MD5 is a way to create a unique signature of files. For
> example, it is used
> to create unique signatures or RPMs or ISO files.

Yes, I know.

> MD5 could well be used to create a unique signature of the
> database schema:
>
> In bash :
> md5sum < pg_dump --schema-only database_name.
>
> result (example):
> 9b05A8d545...

Yes.

> md5stamp ("database", db_name) : would update the signature of schema
> server-side. The signature would be stored along in
> pg_database in a "stamp"
> field.

As I said in my ealier email on the subject, this would probably be
expensive to do often enough to be useful. This was run using the PostgreSQL
7.2 regression test database, running on Cygwin/Windows XP on a 512Mb,
850MHz Dell Inspiron 8000 laptop:

PC20 $ time pg_dump -s regression > regression.sql

real    0m14.210s
user    0m0.620s
sys     0m1.201s

A second run (because things may be cached now yields:

PC20 $ time pg_dump -s regression > regression.sql

real    0m6.914s
user    0m0.600s
sys     0m1.161s

And there will be extra expense in calculating the checksum.

> Then, in case of multi-user development, pgAdmin2 could be
> aware of a change
> in schema, simply looking at db name "stamp". Maybe there is
> an easier way to
> proceed...

Yes, as I suggested, check the ctid of the relevant tuple. If it has
changed, the the object the tuple represents has been updated, if you can't
find it, it's been deleted.

Regards, Dave.