agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Glyn Astill <glynastill@yahoo.co.uk>
To: Cezariusz Marek <cezariusz.marek@comarch.pl>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: IDENTIFY_SYSTEM
Date: Wed, 5 Feb 2014 14:45:41 +0000 (GMT)
Message-ID: <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com> (raw)
In-Reply-To: <0aa401cf226a$12eb9320$38c2b960$@comarch.pl>
References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl>
	<1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com>
	<0aa401cf226a$12eb9320$38c2b960$@comarch.pl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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


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


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

view thread (6+ messages)  latest in thread

Message-ID: <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com>
Permalink:  ../1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com/
Also on:    postgresql.org/message-id/1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: glynastill@yahoo.co.uk, cezariusz.marek@comarch.pl
  Subject: Re: IDENTIFY_SYSTEM
  In-Reply-To: <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox