Re: IDENTIFY_SYSTEM - Mailing list pgsql-sql

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

> From: Glyn Astill <glynastill@yahoo.co.uk>
> To: Cezariusz Marek <cezariusz.marek@comarch.pl>; "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
> Cc:
> Sent: Wednesday, 5 February 2014, 14:45
> Subject: Re: [SQL] IDENTIFY_SYSTEM
>
> ----- Original Message -----
>
>>  From: Cezariusz Marek <cezariusz.marek@comarch.pl>
>>  To: pgsql-sql@postgresql.org
>>  Cc:
>>  Sent: Wednesday, 5 February 2014, 12:01
>>  Subject: Re: [SQL] IDENTIFY_SYSTEM
>>
>>>   That's part of the streaming replication protocol
>>>
>>>   http://www.postgresql.org/docs/9.3/static/protocol-replication.html
>>>   As long as you're using wal_level >= archive and the
> replication
>>  connection is enabled you can retrieve it via psql
>>
>>  Yes, I know, but there is no way to get the systemid value from a function
> using
>>  just SQL or plpgsql?
>>
>
> I don't think so no, but you may have better luck finding someone more
> knowledgable posting to pgsql-general.  You could do it by calling
> pg_controldata via an untrusted procedural language, not so sure how happy
> I'd be with that myself.  E.g. with plperlu:
>
> CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)
> RETURNS text AS
> $BODY$
>     my $rv;
>     my $data;
>     my $pg_controldata_bin = $_[0];
>     my $sysid;
>    
>     $rv = spi_exec_query('SHOW data_directory', 1);
>     $data = $rv->{rows}[0]->{data_directory};
>    
>     open(FD,"$pg_controldata_bin $data | ");
>    
>     while(<FD>) {
>         if (/Database system identifier:/) {
>             $sysid = $_;
>             for ($sysid) {
>                 s/Database system identifier://;
>                 s/[^0-9]//g;
>             }
>             last;
>         }
>     }
>     close (FD);
>     return $sysid;   
>
> $BODY$
> LANGUAGE plperlu;
>
>

So if I actually ran that:

test=# select get_system_identifier_unsafe('pg_controldata');
 get_system_identifier_unsafe
------------------------------
 5667443312440565226

>
>>>   If it's just for licencing perhaps inet_server_addr() or a plperl
>>  function to grab the mac address of the machine might suffice?
>>
>>  I have to license each database, not just the whole machine. And the
> systemid is
>>  the only unique database identifier I've found.
>
> Is it each database or each postgresql instance / cluster?   How exactly do you
> want your licencing to work? There may be a better way.
>