Message-ID: <c38b0ee1-527a-5e1c-b807-eb99b26faa2e@jakobs.com>
Date: Wed, 2 Feb 2022 08:16:00 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.3.2
From: Holger Jakobs <holger@jakobs.com>
Subject: Re: Determine potential change in table size after a column dropped?
To: pgsql-admin@lists.postgresql.org
References: 
 <CAOC+FBWMDP4DOPEYMZH6ZS6EzKV8_twSSebGwD2eLUcEK+i4yg@mail.gmail.com>
 <CAM+6J96mtviadpmVVN+GuVkrOYM7UQ=A+p6Frs7xz9pGusYkyA@mail.gmail.com>
 <c9297bfb-409b-d1b9-9aba-e3c3491068e5@gmail.com>
 <CAOC+FBVWoYZS9u1yvAXRa=3fH8gA1m99L+ujAgt4fLf_-kq4ag@mail.gmail.com>
 <84b61f419a0a5ba1049c7d74a85b6790bfe9e1e9.camel@cybertec.at>
 <CAOC+FBWhRvt9TORHxG1K1AA_MCE+0Y=j7wUzuD6wQShEzDCbdA@mail.gmail.com>
 <599f850b109d98148ee512890ebe6ec4bfbfe26b.camel@cybertec.at>
 <CAFnD8DUrgpzrUZG9pBqSKgsjoErJ=mS=+JZhuVghsP0hrpo1cg@mail.gmail.com>
Content-Language: de-DE
In-Reply-To: 
 <CAFnD8DUrgpzrUZG9pBqSKgsjoErJ=mS=+JZhuVghsP0hrpo1cg@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="------------OFbXZk8Ie6zMsVG6zhg2ZkO1"
Archived-At: 
 <https://www.postgresql.org/message-id/c38b0ee1-527a-5e1c-b807-eb99b26faa2e%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--------------OFbXZk8Ie6zMsVG6zhg2ZkO1
Content-Type: multipart/mixed; boundary="------------CAFu50Iktzzpqy0IiRK5NCgB";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <c38b0ee1-527a-5e1c-b807-eb99b26faa2e@jakobs.com>
Subject: Re: Determine potential change in table size after a column dropped?
References: <CAOC+FBWMDP4DOPEYMZH6ZS6EzKV8_twSSebGwD2eLUcEK+i4yg@mail.gmail.com>
 <CAM+6J96mtviadpmVVN+GuVkrOYM7UQ=A+p6Frs7xz9pGusYkyA@mail.gmail.com>
 <c9297bfb-409b-d1b9-9aba-e3c3491068e5@gmail.com>
 <CAOC+FBVWoYZS9u1yvAXRa=3fH8gA1m99L+ujAgt4fLf_-kq4ag@mail.gmail.com>
 <84b61f419a0a5ba1049c7d74a85b6790bfe9e1e9.camel@cybertec.at>
 <CAOC+FBWhRvt9TORHxG1K1AA_MCE+0Y=j7wUzuD6wQShEzDCbdA@mail.gmail.com>
 <599f850b109d98148ee512890ebe6ec4bfbfe26b.camel@cybertec.at>
 <CAFnD8DUrgpzrUZG9pBqSKgsjoErJ=mS=+JZhuVghsP0hrpo1cg@mail.gmail.com>
In-Reply-To: <CAFnD8DUrgpzrUZG9pBqSKgsjoErJ=mS=+JZhuVghsP0hrpo1cg@mail.gmail.com>

--------------CAFu50Iktzzpqy0IiRK5NCgB
Content-Type: multipart/alternative;
 boundary="------------yFfkPk8i6vOAqyBOE1sUAP4z"

--------------yFfkPk8i6vOAqyBOE1sUAP4z
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: base64

QW0gMDEuMDIuMjIgdW0gMTQ6NDYgc2NocmllYiBKaWFua2FuZyBKaToNCj4gSGkgQWxsIFN1
cGVybWVuIEV4cGVydHMsDQo+DQo+IEknbSBuZXcgaW4gcGdzcWwgYW5kIGhhdmUgYSBzaW1p
bGFyIHByb2JsZW0gZm9yIGEgdGltZXNjYWxlIHBnREIuIEEgREIgDQo+IHRhYmxlIGlzIGZv
ciBzdG9yaW5nIHJhdyBzZXNzaW9ucyBkYXRhIHJlY2VpdmVkIHRocm91Z2ggSW9UIG5ldHdv
cmsgDQo+IGZyb20gbWFueSByZW1vdGUgbWFjaGluZXMuIFRoZSBkYXRhIGZvcm1hdCBpcyB0
aGUgc2FtZSBmb3IgYWxsIHRoZSANCj4gbWFjaGluZXMgYnV0IHRoZSBzZXNzaW9ucyBsYXN0
aW5nLXBlcmlvZHMgY291bGQgYmUgZGlmZmVyZW50IGZyb20gMSANCj4gbWludXRlIHRvIDEg
aG91ciBhbmQgc3VjaC4gRWFjaCBtYWNoaW5lIGNvdWxkIGJlIGFjdGl2YXRlZCBvbmNlIGEg
ZGF5IA0KPiBvciBhIGZldyB0aW1lcyBhIGRheSByYW5kb21seS4NCj4NCj4gTXkgcXVlc3Rp
b24gaXM6DQo+IDEuIEhvdyB0byBzZXR1cCBhIHdhdGNoLWRvZyB0byBkZXRlY3QgbmV3IGRh
dGEgaGFzIGJlZW4gYWRkZWQgaW50byB0aGUgDQo+IERCLCBhbmQNCj4gMi4gSG93IHRvIHBp
Y2stdXAgdGhlIG5ld2x5IGNvbXBsZXRlZCBzZXNzaW9ucyBkYXRhIHNpbmNlIGxhc3QgcGlj
ay11cCANCj4gYW5kIHB1dCBpdCBpbnRvIGEgYnVmZmVyIHRhYmxlIGRlZGljYXRlZCB0byBu
ZXcgZGF0YSBmb3IgZnVydGhlciBFVEwgDQo+IHByb2Nlc3Npbmc/DQo+DQo+IElmIHlvdSBo
YXZlIHNvbWUgc2NyaXB0cyBpbiBwZ1NRTCwgUHl0aG9uIG9yIEMsIGl0IHdpbGwgYmUgZ3Jl
YXRseSANCj4gYXBwcmVjaWF0ZWQhDQo+DQo+IFRoYW5rIHlvdS4NCj4NCj4gQmVzdCByZWdh
cmRzLCBKaQ0KPg0KPg0KPg0KSW4gb3JkZXIgdG8gZ2V0IG5vdGlmaWVkIHNob3VsZCBuZXcg
cm93cyBhcnJpdmUgKG9yIGN1cnJlbnQgb25lcyB1cGRhdGVkIA0Kb3IgZGVsZXRlZCksIHlv
dSBjYW4gaW5zdGFsbCBhIHRyaWdnZXIgd2hpY2ggZmlyZXMgYSBOT1RJRlkgY29tbWFuZCBv
biBhIA0KbmFtZSAoY2hhbm5lbCkuDQoNCkFsbCBvdGhlciBzZXNzaW9ucyB3aGljaCBoYXZl
IGlzc3VlZCBhIExJU1RFTiBvbiB0aGUgc2FtZSBuYW1lIChjaGFubmVsKSANCndpbGwgcmVj
ZWl2ZSBhIG5vdGlmaWNhdGlvbi4NCg0KVW5mb3J0dW5hdGVseSwgbm90IGFsbCBsYW5ndWFn
ZXMgYW5kIGRyaXZlcnMgc3VwcG9ydCB0aGlzLg0KDQpSZWNlbnRseSwgSSB1cGRhdGVkIHRo
ZSBjb2RlIGZvciBwZ19saXN0ZW4gaW4gdGhlIHNjcmlwdCBsYW5ndWFnZSBUY2wuIA0KSXQn
cyBjb21taXR0ZWQsIGJ1dCBubyBuZXcgdmVyc2lvbiByZWxlYXNlZCB5ZXQuDQoNCg0KLS0g
DQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkw
MTINCg0K
--------------yFfkPk8i6vOAqyBOE1sUAP4z
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 01.02.22 um 14:46 schrieb Jiankang
      Ji:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CAFnD8DUrgpzrUZG9pBqSKgsjoErJ=3DmS=3D+JZhuVghsP0hrpo1cg@mail.=
gmail.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">Hi All Supermen Experts,
        <div><br>
        </div>
        <div>I'm new in pgsql and have a similar problem for a timescale
          pgDB. A DB table is for storing raw sessions data received
          through IoT network from many remote machines. The data format
          is the same for all the machines but the sessions
          lasting-periods could be different from 1 minute to 1 hour and
          such. Each machine could be activated once a day or a few
          times a day randomly.</div>
        <div><br>
        </div>
        <div>My question is:</div>
        <div>1. How to setup a watch-dog to detect new data has been
          added into the DB, and=C2=A0</div>
        <div>2. How to pick-up the newly completed sessions data since
          last pick-up and put it into a buffer table dedicated to new
          data for further ETL processing?</div>
        <div><br>
        </div>
        <div>If you have some scripts in pgSQL, Python or C, it will be
          greatly appreciated!</div>
        <div><br>
        </div>
        <div>Thank you.</div>
        <div><br>
        </div>
        <div>Best regards, Ji</div>
      </div>
      <br>
      <div class=3D"gmail_quote"><br>
        <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=

          <br>
        </blockquote>
      </div>
    </blockquote>
    <p>In order to get notified should new rows arrive (or current ones
      updated or deleted), you can install a trigger which fires a
      NOTIFY command on a name (channel).</p>
    <p>All other sessions which have issued a LISTEN on the same name
      (channel) will receive a notification.</p>
    <p>Unfortunately, not all languages and drivers support this.</p>
    <p>Recently, I updated the code for pg_listen in the script language
      Tcl. It's committed, but no new version released yet.</p>
    <p><br>
    </p>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>
--------------yFfkPk8i6vOAqyBOE1sUAP4z--


--------------CAFu50Iktzzpqy0IiRK5NCgB--

--------------OFbXZk8Ie6zMsVG6zhg2ZkO1
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYfovsAUDAAAAAAAKCRCKV5yTsxlhuj8s
AKCaOqzXt4r3XE7N3yUEN6s1Cw4ySACgv6VQJPFWK7ucMOgeuvOw9Zg7ihs=
=aKmT
-----END PGP SIGNATURE-----

--------------OFbXZk8Ie6zMsVG6zhg2ZkO1--