Re: IDENTIFY_SYSTEM

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

> From: Cezariusz Marek <cezariusz(dot)marek(at)comarch(dot)pl>
> To: pgsql-sql(at)postgresql(dot)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;

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