Per-server univocal identifier

"Giampaolo Tomassoni" <g.tomassoni@libero.it> writes:

I'm looking for a way to univocally identify the server on which a sql function or statement is running. My idea would be something close to the value returned by a 'host -f' under linux: the FQDN of the host, but even a serial code or a number would be fine to me. It needs only to be immutable, I guess.

Perhaps inet_server_addr() and inet_server_port() would answer. These
aren't super-useful on local connections, however.

How "immutable" do you want it to be exactly? The system_identifier
embedded in pg_control might be interesting if you want something that
will change at initdb. I don't think there's a way to look at that from
SQL but you could write a C function to access it.

regards, tom lane

...omissis...

Perhaps inet_server_addr() and inet_server_port() would answer. These
aren't super-useful on local connections, however.

No, infact. Mine are local cons...

How "immutable" do you want it to be exactly? The system_identifier
embedded in pg_control might be interesting if you want something that
will change at initdb.

The same immutability of a 'host -f' would be fine to me.

I don't think there's a way to look at that from
SQL but you could write a C function to access it.

I would prefer to avoid writing an external module: that way I would have to put more administration effort when upgrading the postgres installation.

Well, I'll resort probably to put a unique value in a table.

Or... Can I put a custom variable in pgsql.conf?

-----------------------------------
Giampaolo Tomassoni - IT Consultant
Piazza VIII Aprile 1948, 4
I-53044 Chiusi (SI) - Italy
Ph: +39-0578-21100

I assume by 'univocal' you mean unequivocal.

Can you set it up in a table per server? or in a file? or would you
rather use a guuid?

And how is this to be made available?

And is it to be unique per machine, or per cluster (since you can have
many postgresql clusters on one machine).

cheers

andrew

Giampaolo Tomassoni wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

And is it to be unique per machine, or per cluster (since you can have
many postgresql clusters on one machine).

Actually, there are *lots* of ambiguities there. For instance, if you
pg_dump and reload a cluster do you want the ID to change or stay the
same? How about copying the $PGDATA tree to another server? How about
redirecting the same cluster to listen on a new port number?

regards, tom lane

I assume by 'univocal' you mean unequivocal.

Yes, sorry about that: I'm writing italish...

Can you set it up in a table per server? or in a file? or would you
rather use a guuid?

A per-server table will probably be my way.

And how is this to be made available?

Well, a function would be fine.

And is it to be unique per machine, or per cluster (since you can have
many postgresql clusters on one machine).

If it is a per-machine discriminator, it will be a per-node discriminator as well...

Also, it will be useful to people not running a cluster (like me), since they only need a multi-master capability on a table for a legacy app...

cheers

andrew

Cheers,

giampaolo

Giampaolo,

On Sun, Jun 18, 2006 at 01:26:21AM +0200, Giampaolo Tomassoni wrote:

Or... Can I put a custom variable in pgsql.conf?

Like that you mean?

----
custom_variable_classes = 'identify' # list of custom variable classnames
identify.id = 42
----

template1=# show identify.id;
identify.id
-------------
42

However pg_settings does not contain variable classes so it can be difficult
to actually use this value. I wonder if this is a bug or a feature?

Joachim

Giampaolo,

On Sun, Jun 18, 2006 at 01:26:21AM +0200, Giampaolo Tomassoni wrote:

Or... Can I put a custom variable in pgsql.conf?

Like that you mean?

----
custom_variable_classes = 'identify' # list of custom variable
classnames
identify.id = 42
----

template1=# show identify.id;
identify.id
-------------
42

However pg_settings does not contain variable classes so it can
be difficult
to actually use this value. I wonder if this is a bug or a feature?

Yes, that would be fine. It doesn't work to me, anyway. I guess the problem is that the setting shall be associated to a postgres module, which have to be responsible for the proper handing of the setting itself. Without an associated module, the setting is not available under the postgres env.