Обсуждение: Schema unique stamp (OID, MD5?)

Dear friends,

Do you see an existing way to stamp an existing schema with a unique value
(OID?). This would be very usefull for pgAdmin2 when working in development
teams to decide whether a schema should be reloaded or not.

Using PLbash, this would look like :
"md5sum < pg_dump --schema-only database_name"
Are there plans to implement such a feature server-side in C++?

Best regards,
Jean-Michel POURE

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 23 February 2002 19:59
> To: pgadmin-hackers@postgresql.org
> Subject: [pgadmin-hackers] Schema unique stamp (OID, MD5?)
>
>
> Dear friends,
>
> Do you see an existing way to stamp an existing schema with a
> unique value
> (OID?). This would be very usefull for pgAdmin2 when working
> in development
> teams to decide whether a schema should be reloaded or not.
>
> Using PLbash, this would look like :
> "md5sum < pg_dump --schema-only database_name"
> Are there plans to implement such a feature server-side in C++?

This would be very expensive, even on moderately sized databases wouldn't
it? Why not just check the ctid of the relevant tuple to see if it has
changed since it was last read.

Regards, Dave.

Le Samedi 23 Février 2002 21:53, Dave Page a écrit :
> This would be very expensive, even on moderately sized databases wouldn't
> it? Why not just check the ctid of the relevant tuple to see if it has
> changed since it was last read.

What if you create a trigger on a function which does not exist anymore. Is
there a way to query a unique OID?

Sorry, I don't understand what you are asking.

/D

_____________Original message ____________
Subject:    Re: [pgadmin-hackers] Schema unique stamp (OID, MD5?)
Sender:    Jean-Michel POURE <jm.poure@freesurf.fr>
Date:        Sun, 24 Feb 2002 08:49:07 +0000

Le Samedi 23 Février 2002 21:53, Dave Page a écrit :
> This would be very expensive, even on moderately sized databases wouldn't
> it? Why not just check the ctid of the relevant tuple to see if it has
> changed since it was last read.

What if you create a trigger on a function which does not exist anymore. Is
there a way to query a unique OID?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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.

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...

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.

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...

Cheers,
Jean-Michel

> -----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.