resource "null_resource" "cluster" {

  connection {
    type        = "ssh"
    user        = "admin"
    private_key = base64decode(var.private_key)
    host        = aws_lightsail_instance.lightsail.public_ip_address
    timeout     = "3600s"
  }

  provisioner "local-exec" {
    command = <<EOT
            echo "{\"host\":\"${random_string.subdomain.result}\",\"type\":\"A\",\"answer\":\"${aws_lightsail_instance.lightsail.public_ip_address}\",\"ttl\":300}" | \
            curl "https://api.name.com/v4/domains/${var.name_domain}/records" \
            -s \
            -X POST \
            -H "Accept: application/json" \
            -H "Content-Type: application/json" \
            -u "${var.name_username}":"${var.name_token}" \
            -d @-
        EOT
  }

}

brew install virtualbox
brew install vagrant
brew install podman

Vagrant.configure("2") do |config|
  config.vm.box = "generic/fedora32"
  config.vm.hostname = "fedora"
  config.vm.provider "virtualbox" do |vb|
     vb.memory = "1024"
     vb.cpus = 1
  end
end

sudo dnf -y install podman
systemctl --user enable --now podman.socket
sudo loginctl enable-linger $USER
sudo systemctl enable --now sshd

podman --remote info

alias docker=podman

"routing": {
    "rules": [
        {
            "type": "field",
            "outboundTag": "direct",
            "domain": [
                "geosite:private",
                "geosite:apple-cn",
                "geosite:cn"
            ]
        },
        {
            "type": "field",
            "outboundTag": "direct",
            "ip": [
                "geoip:private",
                "geoip:cn"
            ]
        }
    ]
}

"routing": {
  "rules": [
    {
      "type": "field",
      "outboundTag": "Direct",
      "domain": [
        "geosite:private",
        "geosite:tld-cn"
      ]
    },
    {
      "type": "field",
      "outboundTag": "Proxy",
      "domain": [
        "geosite:geolocation-!cn"
      ]
    },
    {
      "type": "field",
      "outboundTag": "Direct",
      "domain": [
        "geosite:cn"
      ]
    }
  ]
}

CREATE TABLE players AS
SELECT generate_series(1, 10000000)                                     AS id,
       uuid_in(md5(random()::TEXT || clock_timestamp()::TEXT)::CSTRING) AS username,
       floor(random() * 100)                                            AS score;

CREATE UNIQUE INDEX players_id_uindex
    ON players (id);

SELECT * FROM players ORDER BY id OFFSET 10 LIMIT 10; // Execution Time: 0.034 ms
SELECT * FROM players ORDER BY id OFFSET 500000 LIMIT 10; // Execution Time: 115.706 ms
SELECT * FROM players ORDER BY id OFFSET 1000000 LIMIT 10; // Execution Time: 235.941 ms
SELECT * FROM players ORDER BY id OFFSET 5000000 LIMIT 10; // Execution Time: 1212.642 ms
SELECT * FROM players ORDER BY id OFFSET 9000000 LIMIT 10; // Execution Time: 2181.703 ms

SELECT * FROM players WHERE id > 10 ORDER BY id LIMIT 10; // Execution Time: 0.033 ms
SELECT * FROM players WHERE id > 500000 ORDER BY id LIMIT 10; // Execution Time: 0.043 ms
SELECT * FROM players WHERE id > 1000000 ORDER BY id LIMIT 10; // Execution Time: 0.041 ms
SELECT * FROM players WHERE id > 5000000 ORDER BY id LIMIT 10; // Execution Time: 0.037 ms
SELECT * FROM players WHERE id > 9000000 ORDER BY id LIMIT 10; // Execution Time: 0.069 ms

CREATE INDEX players_score_username_index ON players (score DESC, username DESC);

SELECT * FROM players ORDER BY score DESC, username DESC OFFSET 500000 LIMIT 10; //Execution Time: 1508.534 ms

SELECT * FROM players WHERE (score, username) < (94, 'fdf62b1a-5b4e-c561-38ad-74ac4f099a46') ORDER BY score DESC, username DESC LIMIT 10; //Execution Time: 0.077 ms


(score, username) < (94, 'fdf62b1a-5b4e-c561-38ad-74ac4f099a46')

// 效果等同于
score < 94 OR (score = 94 AND username < 'fdf62b1a-5b4e-c561-38ad-74ac4f099a46')

SELECT p.id,
       p.username,
       p.score,
       CASE row_number()
            OVER (ORDER BY p.score DESC , p.username DESC ) % 10
           WHEN 0 THEN 1
           ELSE 0
           END page_boundary
FROM players AS p
ORDER BY p.score DESC, p.username DESC

SELECT p.id,
       p.username,
       p.score,
       page_boundary,
       row_number()
       OVER (ORDER BY p.score DESC , p.username DESC) + 1 page_number
FROM (
         SELECT p.id,
                p.username,
                p.score,
                CASE row_number()
                     OVER (ORDER BY p.score DESC , p.username DESC ) % 10
                    WHEN 0 THEN 1
                    ELSE 0
                    END page_boundary
         FROM players AS p
         ORDER BY p.score DESC, p.username DESC
     ) AS p
WHERE p.page_boundary = 1;

SELECT * FROM players WHERE (score, username) < (99, 'fff79537-86b8-5bd1-a474-717f151e0448') ORDER BY score DESC, username DESC;

SELECT * FROM players ORDER BY score DESC, username DESC OFFSET 10 LIMIT 10;

create table [schema-name].copy
partition by date([timestamp-column])
as select * from [schema-name].[table-name];
drop table [schema-name].[table-name];

import logging


log = logging.getLogger()
log.setLevel(logging.DEBUG)

handler = logging.StreamHandler(sys.stdout)
handler.setLevel(logging.INFO)
formatter = logging.Formatter('[%(threadName)s] %(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
log.addHandler(handler)

log.info("Hello %s", "world)

CREATE EXTENSION postgis

ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326);
UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

SELECT lat,
       lon,
       location,
       cnt
FROM ggg
WHERE 1 = 1
  AND cnt > 1
  AND ST_DWithin(
        ggg.geom,
        ST_MakePoint(42.9764, 47.5024)::GEOGRAPHY,
        200000
    );