Thread: Per-server univocal identifier
Dears, I'm looking for a way to univocally identify the server on which a sql function or statement is running. My idea would besomething close to the value returned by a 'host -f' under linux: the FQDN of the host, but even a serial code or a numberwould be fine to me. It needs only to be immutable, I guess. I know there is something suitable under Oracle and, even worse, under mysql... The purpose is mostly related to a light replication problem I have, in which I need to 'emulate' a multi-master replicationon a table. I placed a question on the IRC list and I found a couple of unreplied messages asking the same thing in the pgsql-generallist. Regards, ----------------------------------- Giampaolo Tomassoni - IT Consultant Piazza VIII Aprile 1948, 4 I-53044 Chiusi (SI) - Italy Ph: +39-0578-21100
"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
wouldbe something close to the value returned by a 'host -f' under linux: the FQDN of the host, but even a serial code
ora 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 upgradingthe 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: >Dears, > >I'm looking for a way to univocally identify the server on which a sql function or statement is running. My idea would besomething close to the value returned by a 'host -f' under linux: the FQDN of the host, but even a serial code or a numberwould be fine to me. It needs only to be immutable, I guess. > >I know there is something suitable under Oracle and, even worse, under mysql... > >The purpose is mostly related to a light replication problem I have, in which I need to 'emulate' a multi-master replicationon a table. > >I placed a question on the IRC list and I found a couple of unreplied messages asking the same thing in the pgsql-generallist. > > > >
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 tablefor a legacy app... > cheers > > andrew Cheers, giampaolo > > Giampaolo Tomassoni wrote: > > >Dears, > > > >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. > > > >I know there is something suitable under Oracle and, even worse, > under mysql... > > > >The purpose is mostly related to a light replication problem I > have, in which I need to 'emulate' a multi-master replication on a table. > > > >I placed a question on the IRC list and I found a couple of > unreplied messages asking the same thing in the pgsql-general list. > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
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 apostgres 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. > > > Joachim > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq