mydreambei-ai

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert

# @compiles(Insert)
# def prefix_inserts(insert, compiler, **kw):
#     sql = compiler.visit_insert(insert, **kw)
#     if "RETURNING" not in sql:
#         return sql + ' ON CONFLICT DO NOTHING'
#     else:
#         return sql

mydreambei-ai

from sqlalchemy.ext.declarative import declared_attr, AbstractConcreteBase

class _Event(AbstractConcreteBase):
    @declared_attr
    def hackinfo_id(self):
        return Column(ForeignKey("hackinfos.id", ondelete='CASCADE'))

    @declared_attr
    def hackinfo(self):
        return relationship("HackInfo")

mydreambei-ai

import argparse
import collections
import http.client
import multiprocessing
import os
import re
import time
from multiprocessing import Queue, Lock, Process
from select import poll, POLLIN
from urllib.parse import urlparse

mydreambei-ai

import re
import time

from sqlalchemy import Column, Integer, TIMESTAMP, func, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy import event
from sqlalchemy.ext.declarative import as_declarative, declared_attr, AbstractConcreteBase
from sqlalchemy.orm import scoped_session, sessionmaker, relationship

engine = create_engine('sqlite://', convert_unicode=True, echo=False)

mydreambei-ai

from ctypes import *

def convert_bytes_to_structure(st, byte):
    # sizoef(st) == sizeof(byte)
    memmove(addressof(st), byte, sizeof(st))


def convert_struct_to_bytes(st):
    buffer = create_string_buffer(sizeof(st))
    memmove(buffer, addressof(st), sizeof(st))

mydreambei-ai

Recursive CTE/Postgres

Setup

from sqlalchemy import (String,
                        Integer,
                        engine_from_config,
                        Column,
                        ForeignKey)
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.sql.functions import concat

from sqlalchemy.ext.declarative import declarative_base

mydreambei-ai

Postgresql Server Side Cursor

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocated by the client.

If the dataset is too large to be practically handled on the client side, it is possible to create a server side cursor. Using this kind of cursor it is possible to transfer to the client only a controlled amount of data, so that a large dataset can be examined without keeping it entirely in memory.

Server side cursor are created in PostgreSQL using the DECLARE command and subsequently handled using MOVE, FETCH and CLOSE commands. postgresql-cursor

Cursor

mydreambei-ai

from sqlalchemy import (String,
                        Integer,
                        engine_from_config,
                        Column)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base_1 = declarative_base()

Base_2 = declarative_base()

mydreambei-ai

create or replace function lmy(first INTEGER, item INTEGER) returns INTEGER as $$
BEGIN
  return first * item;
END;
$$
LANGUAGE plpgsql;

create aggregate yml(INTEGER) (
  sfunc = lmy,
  stype = INTEGER,

mydreambei-ai

Postgresql 如何trigger autovacuum and analyze

开启autovacuum的前提条件是打开track_counts。因为系统需要根据跟踪到的tuples计数和阀值进行比较来决定是否出发autovacuum 或 autoanalyze

1. autovacuum 是否开启自动vacuum, analyze.
2. log_autovacuum_min_duration 阀值，用于度量是否需要记录下autovacuum的动作到log里面.(-1表示禁止，0表示记录所有)
3. autovacuum_max_workers 用于指定整个数据库机器同一时间点允许的autovacuum后台进程(不包括lanucher进程)
4. autovacuum_naptime 两个autovacuum或autoanalyze允许周期间的间隔时间。为了看效果，我们在下面的例子把这个值设置为1S。
5. autovacuum_vacuum_threshold 最小触发vacuum的度量值(计数器记录update,delete的tuples)
6. autovacuum_analyze_threshold 最小触发analyze的度量值(计数器记录insert,update,delete的tuples)