DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=ukSaan8ZMJt8VccKDDgUSzmODwoanEM9N6g4HPbHiU4B5B109vRyI9TvZwjieyaEYoW0kEJCuWnN4F4RAtHMcwzQjg/szmASWAg/vdcjofUK9il38WRaJs1gtGD/MuqEsfOGqiWIyqhEZvJlInpBfHpWxc70A8nF4ewzDUFy8Ks=;
References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl>
 <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com>
 <0aa401cf226a$12eb9320$38c2b960$@comarch.pl>
Message-ID: <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com>
Date: Wed, 5 Feb 2014 14:45:41 +0000 (GMT)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: Glyn Astill <glynastill@yahoo.co.uk>
Subject: Re: IDENTIFY_SYSTEM
To: Cezariusz Marek <cezariusz.marek@comarch.pl>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <0aa401cf226a$12eb9320$38c2b960$@comarch.pl>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

----- Original Message -----

> From: Cezariusz Marek <cezariusz.marek@comarch.pl>
> To: pgsql-sql@postgresql.org
> Cc:=20
> Sent: Wednesday, 5 February 2014, 12:01
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>=20
>>  That's part of the streaming replication protocol
>>=20
>>  http://www.postgresql.org/docs/9.3/static/protocol-replication.html
>>  As long as you're using wal_level >=3D archive and the replication=20
> connection is enabled you can retrieve it via psql
>=20
> Yes, I know, but there is no way to get the systemid value from a functio=
n using=20
> just SQL or plpgsql?
>=20

I don't think so no, but you may have better luck finding someone more know=
ledgable posting to pgsql-general.=A0 You could do it by calling pg_control=
data via an untrusted procedural language, not so sure how happy I'd be wit=
h that myself.=A0 E.g. with plperlu:

CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)=20
RETURNS text AS=20
$BODY$
=A0=A0=A0 my $rv;
=A0=A0=A0 my $data;
=A0=A0=A0 my $pg_controldata_bin =3D $_[0];
=A0=A0=A0 my $sysid;
=A0=A0=A0=20
=A0=A0=A0 $rv =3D spi_exec_query('SHOW data_directory', 1);
=A0=A0=A0 $data =3D $rv->{rows}[0]->{data_directory};
=A0=A0=A0=20
=A0=A0=A0 open(FD,"$pg_controldata_bin $data | ");
=A0=A0=A0=20
=A0=A0=A0 while(<FD>) {
=A0=A0=A0 =A0=A0=A0 if (/Database system identifier:/) {
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 $sysid =3D $_;
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 for ($sysid) {
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 s/Database system identifier://;
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 s/[^0-9]//g;
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 }
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 last;
=A0=A0=A0 =A0=A0=A0 }
=A0=A0=A0 }
=A0=A0=A0 close (FD);
=A0=A0=A0 return $sysid;=A0=A0=A0=20

$BODY$
LANGUAGE plperlu;


>>  If it's just for licencing perhaps inet_server_addr() or a plperl=20
> function to grab the mac address of the machine might suffice?
>=20
> I have to license each database, not just the whole machine. And the syst=
emid is=20
> the only unique database identifier I've found.

Is it each database or each postgresql instance / cluster?=A0=A0 How exactl=
y do you want your licencing to work? There may be a better way.


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql