From cezariusz.marek@comarch.pl Mon Feb 3 08:35:12 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WAEzo-0000vw-1W for pgsql-sql@arkaria.postgresql.org; Mon, 03 Feb 2014 08:35:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WAEzm-0004b9-El for pgsql-sql@arkaria.postgresql.org; Mon, 03 Feb 2014 08:35:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WAEzl-0004ad-9V for pgsql-sql@postgresql.org; Mon, 03 Feb 2014 08:35:09 +0000 Received: from inptr-69-18.comarch.com ([217.74.69.18] helo=odzwierny3.comarch.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WAEzh-0001km-QM for pgsql-sql@postgresql.org; Mon, 03 Feb 2014 08:35:08 +0000 Received: from localhost (localhost [127.0.0.1]) authenticated as cezariusz.marek@comarch.com by odzwierny3.comarch.com with esmtpsa (TLSv1:AES128-SHA:128)(ComArch ESMTP) id 1WAEzg-000OR3-7J for pgsql-sql@postgresql.org; Mon, 03 Feb 2014 09:35:04 +0100 From: "Cezariusz Marek" To: Subject: IDENTIFY_SYSTEM Date: Mon, 3 Feb 2014 09:35:04 +0100 Organization: Comarch SA Message-ID: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_08B0_01CF20C3.37B6EB80" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac8gutSJAygBjR8bSEyJYwEh5QO3Rw== Content-Language: pl X-CA-Antivirus: Scanned by ComArch Antivirus on odzwierny3.comarch.com X-CA-Is-Spam: No X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_08B0_01CF20C3.37B6EB80 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hello, =20 Is there a way to call IDENTIFY_SYSTEM command from SQL? Or otherwise = get the unique system identifier from a function? I need some unique = database indentifier for the licensing purposes. =20 --=20 Cezariusz Marek Mobile +48 608 646 494, Phone +48 33 484 6900, = http://www.comarch.com/ Comarch SA, ul. Micha=B3owicza 12, 43-300 Bielsko-Bia=B3a, POLAND =20 ------=_NextPart_000_08B0_01CF20C3.37B6EB80 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

Hello,

Is there a way to call IDENTIFY_SYSTEM command from SQL? Or otherwise get the = unique system identifier from a function? I need some unique database indentifier = for the licensing purposes.

-- =

Cezariusz = Marek

Mobile = +48 608 646 494, Phone +48 33 484 6900, http://www.comarch.com/

Comarch = SA, ul. Micha=B3owicza 12, 43-300 Bielsko-Bia=B3a, = POLAND

------=_NextPart_000_08B0_01CF20C3.37B6EB80-- From glynastill@yahoo.co.uk Wed Feb 5 11:35:02 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB0kv-0000No-4m for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 11:35:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WB0kt-0005Ty-TJ for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 11:34:59 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB0ks-0005Tr-KH for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 11:34:58 +0000 Received: from nm27-vm8.bullet.mail.ir2.yahoo.com ([212.82.97.59]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB0kl-0007w4-03 for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 11:34:57 +0000 Received: from [212.82.98.54] by nm27.bullet.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 11:34:49 -0000 Received: from [212.82.98.88] by tm7.bullet.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 11:34:48 -0000 Received: from [127.0.0.1] by omp1025.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 11:34:48 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 795017.10909.bm@omp1025.mail.ir2.yahoo.com Received: (qmail 10045 invoked by uid 60001); 5 Feb 2014 11:34:48 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1391600088; bh=ukV9n9MpFN9yR2wvzkIsoZXSIlgr2NIh1KcQdR6IT1I=; 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=QAa0eL8Ujp8g2mGHIaQyVheL0RJ77FZNcpIGP1iIgIGFO3DMidXzRNCtaPLwyPFg1tFygfq0HJQPIj7gC1BbyvWyiHkIvQu6EbwMsm5FXIJ+gElAFRd6jw1Mu5tlLednIbeU4HQVBmD7FSDLLaO00r6YOENqXyHJN+HU+Nk9nhM= 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=bzpUoWHsh5oQqcQnwG1lf8n7k3x7vwQeT4S8lMGG8bzoD9M3b/aamPYfHdGivPnos1oz+QjXT592l5PcC+B9FTu9+fSBSj/rnqvSHC3MADKM7B/SE4B42UaqMvy0phE9UpqmNRSSlYHLfnmE4XBUn8TY7kRf+5fqnq+peZsz8Ro=; X-YMail-OSG: AkLJODEVM1lElqSmY3X9TY3JrS_GMiCbJFxJ6iWTHmsMRmy vqSXMG5J6SdMvvpvkErznKUMhFiVA5DnytTdxNrQrsXCt1ipKm6H0PMkMwga kAwXIrzmLmjpH7a35lGI05wMGVkfaPGWTME.uhLIZAIXjr3k8UIktpMy1iPt th.R3GSOe4trbHSPW3uGRHUAlmd0YCir42aBkvGuN4m7lNxNi_fCqV2G2kA3 8CQibsDj3dpSkc..XP4Ks2qMgNFy3pVAUZIuIAR4gIH3irEur_x84OlcGvKj zx82jcpFEk2_OHEcrqbyTMDf0q6YRmTSCvTTYSGZYO_7S4wmIbDcEaTt38nv UrAlN5lITbDwt4mehS8kJqBkfbLCzF4TPF_aM.vEtjKyLvnqYM5AkYIXtKVN iHxRw0TmV6amjHlOsjYK3XLVrrenC4Jpooc2oF2_rzXTM2aaYh4YTjQi6vSo y2XQQ29st1ByA1ODZZnpyUPDrQ.8RWTzuSKeWvCX3kg7Pw_LaHrKX34WF_Av k9Ef8SRFysK5QtaAP_9_zVDz8Z0j0vRieJvOY8o21gaJeNrjADWdUmHNDKf2 v3Ed6Omdc1lHYhwTFtX4Lt2ihT7kEz8c8LUdIBpLCDUf1FkWDNggOG5c26_w YVA-- Received: from [194.168.202.210] by web133204.mail.ir2.yahoo.com via HTTP; Wed, 05 Feb 2014 11:34:48 GMT X-Rocket-MIMEInfo: 002.001, X19fX19fX19fX19fX19fX19fX19fX19fX19fXwoKPiBGcm9tOiBDZXphcml1c3ogTWFyZWsgPGNlemFyaXVzei5tYXJla0Bjb21hcmNoLnBsPgo.VG86IHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9yZwo.U2VudDogTW9uZGF5LCAzIEZlYnJ1YXJ5IDIwMTQsIDg6MzUKPlN1YmplY3Q6IFtTUUxdIElERU5USUZZX1NZU1RFTQo.IAo.Cj4KPkhlbGxvLAo.wqAKPklzIHRoZXJlIGEgd2F5IHRvIGNhbGwgSURFTlRJRllfU1lTVEVNIGNvbW1hbmQgZnJvbSBTUUw_IE9yIG90aGVyd2lzZSBnZXQgdGhlIHVuaXF1ZSBzeXMBMAEBAQE- X-Mailer: YahooMailWebService/0.8.175.631 References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl> Message-ID: <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com> Date: Wed, 5 Feb 2014 11:34:48 +0000 (GMT) From: Glyn Astill Reply-To: Glyn Astill Subject: Re: IDENTIFY_SYSTEM To: Cezariusz Marek , "pgsql-sql@postgresql.org" In-Reply-To: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: 0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ____________________________ > From: Cezariusz Marek >To: pgsql-sql@postgresql.org >Sent: Monday, 3 February 2014, 8:35 >Subject: [SQL] IDENTIFY_SYSTEM >=20 > > >Hello, >=C2=A0 >Is there a way to call IDENTIFY_SYSTEM command from SQL? Or otherwise get = the unique system identifier from a function? I need some unique database i= ndentifier for the licensing purposes. >=C2=A0 That's part of the streaming replication protocol=C2=A0=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 connecti= on is enabled you can retrieve it via psql http://www.postgresql.org/message-id/AANLkTimFVHvhG73rPykX1z57MvPgskxJY1JuR= YrD9Cf_@mail.gmail.com glyn@test:~$ psql "replication=3D1" -c "IDENTIFY_SYSTEM" =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 systemid=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= | timeline |=C2=A0 xlogpos ---------------------+----------+----------- =C2=A05972513070019772415 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1 | 0= /2EE0368 If it's just for licencing perhaps inet_server_addr() or a plperl function = to grab the mac address of the machine might suffice? >--=20 >Cezariusz Marek >Mobile +48=C2=A0608=C2=A0646=C2=A0494, Phone +48 33=C2=A0484 6900, http://= www.comarch.com/ >Comarch SA, ul. Micha=C5=82owicza 12, 43-300 Bielsko-Bia=C5=82a, POLAND >=C2=A0 > > --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From cezariusz.marek@comarch.pl Wed Feb 5 12:02:27 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB1BT-0001Af-GH for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 12:02:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WB1BT-0006ri-0f for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 12:02:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB1BS-0006rc-Cj for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 12:02:26 +0000 Received: from odzwierny2.comarch.com ([217.74.69.10]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB1BL-0007n4-PK for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 12:02:26 +0000 Received: from localhost (localhost [127.0.0.1]) authenticated as cezariusz.marek@comarch.com by odzwierny2.comarch.com with esmtpsa (TLSv1:AES128-SHA:128)(ComArch ESMTP) id 1WB1B1-000E5x-3N for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 13:02:18 +0100 From: "Cezariusz Marek" To: References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl> <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com> In-Reply-To: <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com> Subject: Re: IDENTIFY_SYSTEM Date: Wed, 5 Feb 2014 13:01:59 +0100 Organization: Comarch SA Message-ID: <0aa401cf226a$12eb9320$38c2b960$@comarch.pl> MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQICKcZhNzNUtvhJd/mIeVvo2v+gagIb82pBmi+fwLA= Content-Language: pl X-CA-Antivirus: Scanned by ComArch Antivirus on odzwierny2.comarch.com X-CA-Is-Spam: No X-Pg-Spam-Score: -0.4 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org > 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 connec= tion 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? > If it's just for licencing perhaps inet_server_addr() or a plperl functio= n to grab the mac address of the machine might suffice? I have to license each database, not just the whole machine. And the system= id is the only unique database identifier I've found. --=20 Cezariusz Marek Mobile +48 608 646 494, Phone +48 33 484 6900, http://www.comarch.com/ Comarch SA, ul. Micha=C5=82owicza 12, 43-300 Bielsko-Bia=C5=82a, POLAND --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From glynastill@yahoo.co.uk Wed Feb 5 14:49:15 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB3mt-0006LE-0P for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 14:49:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WB3ms-0003tZ-CM for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 14:49:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB3mq-0003rh-NQ for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 14:49:12 +0000 Received: from nm32-vm6.bullet.mail.ir2.yahoo.com ([212.82.97.102]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB3mi-0002Xt-M5 for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 14:49:12 +0000 Received: from [212.82.98.52] by nm32.bullet.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 14:49:02 -0000 Received: from [212.82.98.99] by tm5.bullet.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 14:49:02 -0000 Received: from [127.0.0.1] by omp1036.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 14:49:02 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 82806.12831.bm@omp1036.mail.ir2.yahoo.com Received: (qmail 24165 invoked by uid 60001); 5 Feb 2014 14:45:41 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1391611541; bh=rg1C5P1z3uiWXIc23AUZLE2gMBaBpHkpXgIVLQrqRwQ=; 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=SSN7/772LmkjrueqNNKUpPpP6jYwG2n6cA5qE+Q/Zx54qt5IqXDjhC4+t20sSzQtXLsGYKFWbw/EbX0bs8lRVG5yEcaEBrzErR10Hu9cLYXutLYfnZICjyOP4i13TnGKol9CITunT/bpV+dYy98veO7VmUIDXzdtkxCACDJSgwU= 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=; X-YMail-OSG: DbXUcB0VM1kHIKXQ01nPXkHt34f2AZvvs8C.YERJ3suvt3q h9flX4llFNRM5Y1WXUgnrx.RBbPPctrAyzFOWYkc3ArGymyaVszOrypPkb1u Y_daU8VGCzg6LbjAvi7WIPBUsOJFgjLBYrJ_M1Viqrk2PWC9nDoJrvCyGaIt TNb_lXzybAHeUm0sNwECbaR7WuybnANLv_WF_96YtwLf.oUjInAgOucY52d. tdwmzzQSrk651mGTHaYaWGRTmlc9pMk8oJZRqc_j69QFDPwb_LIhJ7KwODGN iQ1ry.707vjNDaGipkpVx8LLgqhVhvVMGpMCfM2Qsi2N76wbK9WW0guOZiOE FLwy0t6N1_sKfrdfbA.LOI_98q6J0vK4gxbCQ7pvcMoeJy9b1poG7GEEKgks CtBTfhwWLDdMDk05kttT2i5C2YSuMXSL8YjCR45RCIsQaGcroppHjkQPhwka FhXLjVbK5InJRvTTs0BJQj_ZU6o2fEWd6AYHOqoHtKTRcHBoaGdGcrMWyycQ 9nkSpGCzecUNmXMcMyBoWkKzYkaay0Nypl1VVecbRB27DnKcWaHficGEMlbl 4 Received: from [194.168.202.210] by web133205.mail.ir2.yahoo.com via HTTP; Wed, 05 Feb 2014 14:45:41 GMT X-Rocket-MIMEInfo: 002.001, LS0tLS0gT3JpZ2luYWwgTWVzc2FnZSAtLS0tLQoKPiBGcm9tOiBDZXphcml1c3ogTWFyZWsgPGNlemFyaXVzei5tYXJla0Bjb21hcmNoLnBsPgo.IFRvOiBwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmcKPiBDYzogCj4gU2VudDogV2VkbmVzZGF5LCA1IEZlYnJ1YXJ5IDIwMTQsIDEyOjAxCj4gU3ViamVjdDogUmU6IFtTUUxdIElERU5USUZZX1NZU1RFTQo.IAo.PiAgVGhhdCdzIHBhcnQgb2YgdGhlIHN0cmVhbWluZyByZXBsaWNhdGlvbiBwcm90b2NvbAo.PiAKPj4gIGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcBMAEBAQE- X-Mailer: YahooMailWebService/0.8.175.631 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 Reply-To: Glyn Astill Subject: Re: IDENTIFY_SYSTEM To: Cezariusz Marek , "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 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ----- Original Message ----- > From: Cezariusz Marek > 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() { =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 From glynastill@yahoo.co.uk Wed Feb 5 14:51:15 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB3oo-0006Rm-SQ for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 14:51:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WB3oo-0006J8-CQ for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 14:51:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB3on-0006J2-Mw for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 14:51:13 +0000 Received: from nm22.bullet.mail.ir2.yahoo.com ([212.82.96.46]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB3og-0002d2-PE for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 14:51:13 +0000 Received: from [212.82.98.58] by nm22.bullet.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 14:51:05 -0000 Received: from [212.82.98.110] by tm11.bullet.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 14:51:05 -0000 Received: from [127.0.0.1] by omp1047.mail.ir2.yahoo.com with NNFMP; 05 Feb 2014 14:51:05 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 538671.62986.bm@omp1047.mail.ir2.yahoo.com Received: (qmail 24879 invoked by uid 60001); 5 Feb 2014 14:51:05 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1391611865; bh=6pemb+bTYoif1ph5dEG0xu28SFvef11EC9XNT+hWPWs=; 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=r8sKLVv1EskKXlijaUsJ1BBTbJoNLX5OUBW5AYo4CxFoN1ekFo/y3uAS6jOvv1TPjf5mVXEMdwn196T26+5NjZ9a1M0cU2tlioiyDf51Gtw/uq6T4DHZYFPtVEN/AqxecEM3J3fRrQm4E5kOkwJ68RDS4XLPg91Z1rLSOdKemGY= 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=KjVEeKUvBHm1mQZt+d0QcK6hws719kt1ypQeTr2vXYAf4Pg6kDjHPgTZoIrfuhP33AccEhrLbJUdZC1h2KhonACp1CmacMFOre4iVoSPFy+0dSIWqoGEr2oeznjp7bGzvt+WQt8go6HowDzUSXLyRwHVR5EIaS/ZGIeuQcusBV0=; X-YMail-OSG: 4QOxGPkVM1kKcpS5f9C0j3IJiT.80o4bUFIz3z6MKH_ceUi ddLftsijNb4pggOFgqcz5SxBx1GQDoaQU51Fy0Moa3XUMGGmtV0ohg8zm.WI f2BMz5RYflKS47RR0tKIgvU8r9Zum3GaSCpCOqpdrOfz1fg53Oh93wztmrdW .IshB3gYDZVbGn7yipHa7KZoPHWqPbqA_lCZ8YwRLfAPK26Lx686CvVXcAze mhaAW8IpRvGgvwX9k5.biX2zpRBdfVmn8udQRGQrtTiKaQ48z_VOarmS4A5C aKH6UJ1AvWK5unczUtNVUVGlP_c3Quk1_a7URgVts_Xun_l4c5MXCNrNcCPc otGTvKW9TTfN74g9KDQetLMvtsDmBQBV8i1ll.ji8I7KDFuF_aNOWKiAuvK4 qv92hiNnMC2g42F1ppMVzaeMqwWrbiLkHu2EdxuMNgOcOSVfoOp959DmGO_2 CKDUaJS8n_poTqRDmmPUCSUY5nJNlx0oWIJo6DeUpuVS77rRnOC9mMmQQryf 0XUQIsVXzVNAn5ypq6Dk_F11u5smpBnk6cfsgFmR__hdAMO99gHnEBdDUW2q x Received: from [194.168.202.210] by web133202.mail.ir2.yahoo.com via HTTP; Wed, 05 Feb 2014 14:51:05 GMT X-Rocket-MIMEInfo: 002.001, LS0tLS0gT3JpZ2luYWwgTWVzc2FnZSAtLS0tLQoKPiBGcm9tOiBHbHluIEFzdGlsbCA8Z2x5bmFzdGlsbEB5YWhvby5jby51az4KPiBUbzogQ2V6YXJpdXN6IE1hcmVrIDxjZXphcml1c3oubWFyZWtAY29tYXJjaC5wbD47ICJwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmciIDxwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmc.Cj4gQ2M6IAo.IFNlbnQ6IFdlZG5lc2RheSwgNSBGZWJydWFyeSAyMDE0LCAxNDo0NQo.IFN1YmplY3Q6IFJlOiBbU1FMXSBJREVOVElGWV9TWVNURU0KPiAKPiAtLS0tLSBPcmlnaW5hbCBNZXMBMAEBAQE- X-Mailer: YahooMailWebService/0.8.175.631 References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl> <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com> <0aa401cf226a$12eb9320$38c2b960$@comarch.pl> <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com> Message-ID: <1391611865.95042.YahooMailNeo@web133202.mail.ir2.yahoo.com> Date: Wed, 5 Feb 2014 14:51:05 +0000 (GMT) From: Glyn Astill Reply-To: Glyn Astill Subject: Re: IDENTIFY_SYSTEM To: Cezariusz Marek , "pgsql-sql@postgresql.org" In-Reply-To: <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ----- Original Message ----- > From: Glyn Astill > To: Cezariusz Marek ; "pgsql-sql@postgresql.o= rg" > Cc:=20 > Sent: Wednesday, 5 February 2014, 14:45 > Subject: Re: [SQL] IDENTIFY_SYSTEM >=20 > ----- Original Message ----- >=20 >> From: Cezariusz Marek >> To: pgsql-sql@postgresql.org >> Cc:=20 >> Sent: Wednesday, 5 February 2014, 12:01 >> Subject: Re: [SQL] IDENTIFY_SYSTEM >>=20 >>> =A0 That's part of the streaming replication protocol >>>=20 >>> =A0 http://www.postgresql.org/docs/9.3/static/protocol-replication.html >>> =A0 As long as you're using wal_level >=3D archive and the=20 > 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 funct= ion=20 > using=20 >> just SQL or plpgsql? >>=20 >=20 > I don't think so no, but you may have better luck finding someone more=20 > knowledgable posting to pgsql-general.=A0 You could do it by calling=20 > pg_controldata via an untrusted procedural language, not so sure how happ= y=20 > I'd be with that myself.=A0 E.g. with plperlu: >=20 > 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() { > =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 >=20 > $BODY$ > LANGUAGE plperlu; >=20 >=20 So if I actually ran that: test=3D# select get_system_identifier_unsafe('pg_controldata'); =A0get_system_identifier_unsafe ------------------------------ =A05667443312440565226 >=20 >>> =A0 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=20 > systemid is=20 >> the only unique database identifier I've found. >=20 > Is it each database or each postgresql instance / cluster?=A0=A0 How exac= tly do you=20 > 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 From bricklen@gmail.com Wed Feb 5 16:15:54 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB58j-00010N-VT for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 16:15:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WB58j-0002tk-Bs for pgsql-sql@arkaria.postgresql.org; Wed, 05 Feb 2014 16:15:53 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB58i-0002td-2x for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 16:15:52 +0000 Received: from mail-ve0-x235.google.com ([2607:f8b0:400c:c01::235]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WB58f-0004ik-4g for pgsql-sql@postgresql.org; Wed, 05 Feb 2014 16:15:51 +0000 Received: by mail-ve0-f181.google.com with SMTP id cz12so473978veb.12 for ; Wed, 05 Feb 2014 08:15:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=DSES+d7xMoL5vxJBgy6R80x1lQiNH2s72CcY8KGbM7M=; b=PAqqwK0jdfNyHdG4C1Cw7ZPZMvPPAdvBzYZcgixHc4r5nAEhskxE5mMJoy7ETCOtag zNxlGt/5nBUte19y7JEaYVdw6dsf7mVHj+7vT62bQhMoOCMigXzxgmCO01Ieytu5PbB/ ykiWMfxXYX9WdvDYA01SzDvIV/j7cdgLiroc+6BVQMnMzcDM76W1N/AfOjiDJio+JqYf xO7GqVTAReNhxKr8sQ9mziuexMPsxOzs+uZaNNle9sY7Q5B1LL6OvhvoUGBTODiuKKCS /0yMIPz8j1pp0WXcWwUXUjitkXnqkwf8f1O+/cqQJaCo3MSgBHmXlWvnZZK8abHQm85y IWPQ== MIME-Version: 1.0 X-Received: by 10.52.111.161 with SMTP id ij1mr11159vdb.80.1391616948273; Wed, 05 Feb 2014 08:15:48 -0800 (PST) Received: by 10.58.197.71 with HTTP; Wed, 5 Feb 2014 08:15:48 -0800 (PST) In-Reply-To: <1391611865.95042.YahooMailNeo@web133202.mail.ir2.yahoo.com> References: <08af01cf20ba$d5f0aec0$81d20c40$@comarch.pl> <1391600088.5800.YahooMailNeo@web133204.mail.ir2.yahoo.com> <0aa401cf226a$12eb9320$38c2b960$@comarch.pl> <1391611541.96644.YahooMailNeo@web133205.mail.ir2.yahoo.com> <1391611865.95042.YahooMailNeo@web133202.mail.ir2.yahoo.com> Date: Wed, 5 Feb 2014 08:15:48 -0800 Message-ID: Subject: Re: IDENTIFY_SYSTEM From: bricklen To: Glyn Astill Cc: Cezariusz Marek , "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=bcaec5486346dd861f04f1ab11db X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --bcaec5486346dd861f04f1ab11db Content-Type: text/plain; charset=UTF-8 On Wed, Feb 5, 2014 at 6:51 AM, Glyn Astill wrote: > > > > 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() { > > 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 > Joe Conway wrote something a few years ago which could probably be brought up to date and made into a Postgresql extension. https://github.com/jconway/pg_controldata --bcaec5486346dd861f04f1ab11db Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

= On Wed, Feb 5, 2014 at 6:51 AM, Glyn Astill <glynastill@yahoo.co.uk= > wrote:

>
> I don't think so no, but you may have better luck finding someone = more
> knowledgable posting to pgsql-general.=C2=A0 You could do it by callin= g
> pg_controldata via an untrusted procedural language, not so sure how h= appy
> I'd be with that myself.=C2=A0 E.g. with plperlu:
>
> CREATE OR REPLACE FUNCTION get_system_identifier_unsafe(text)
> RETURNS text AS
> $BODY$
> =C2=A0=C2=A0=C2=A0 my $rv;
> =C2=A0=C2=A0=C2=A0 my $data;
> =C2=A0=C2=A0=C2=A0 my $pg_controldata_bin =3D $_[0];
> =C2=A0=C2=A0=C2=A0 my $sysid;
> =C2=A0=C2=A0=C2=A0
> =C2=A0=C2=A0=C2=A0 $rv =3D spi_exec_query('SHOW data_directory'= ;, 1);
> =C2=A0=C2=A0=C2=A0 $data =3D $rv->{rows}[0]->{data_directory}; > =C2=A0=C2=A0=C2=A0
> =C2=A0=C2=A0=C2=A0 open(FD,"$pg_controldata_bin $data | ");<= br> > =C2=A0=C2=A0=C2=A0
> =C2=A0=C2=A0=C2=A0 while(<FD>) {
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 if (/Database system identifier:= /) {
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 $sysid =3D $_= ;
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 for ($sysid) = {
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0= =C2=A0 s/Database system identifier://;
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0= =C2=A0 s/[^0-9]//g;
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 }
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 last;
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 }
> =C2=A0=C2=A0=C2=A0 }
> =C2=A0=C2=A0=C2=A0 close (FD);
> =C2=A0=C2=A0=C2=A0 return $sysid;=C2=A0=C2=A0=C2=A0
>
> $BODY$
> LANGUAGE plperlu;
>
>

So if I actually ran that:

test=3D# select get_system_identifier_unsafe('pg_controldata');
=C2=A0get_system_identifier_unsafe
------------------------------
=C2=A05667443312440565226

Joe Conway wrote something a few years ago which could probab= ly be brought up to date and made into a Postgresql extension. https://github.com/jconway/pg_con= troldata

--bcaec5486346dd861f04f1ab11db--