Forem: andi

new stuff from postgres learned

andi — Wed, 05 Jun 2019 10:24:27 +0000

watch

if you have open psql shell and want to have refreshed by n-seconds certain command, after executing this command once just type, e.g 10 stands for refresh each 10 seconds

\watch 10

SECURITY DEFINER - running procedure/function as OWNER

default behavior: fun is run by executing user, you can easily change it with SECURITY DEFINER you add just after defining your procedure e.g:

CREATE OR REPLACE FUNCTION public.foo() 
RETURNS VOID AS $$

-- do whatever you need here and remember

$$ LANGUAGE plpgsql SECURITY DEFINER;

the user inside the scope is the owner of the function - the one who defined it (effective user)
and to get the user actually calling it you may need to have wrapping function called without SECURITY DEFINER grab there user and pass it to the function run as SECURITY DEFINER.

just for reference the default modifier is called SECURITY INVOKER.

https://www.postgresql.org/docs/current/sql-createfunction.html

pg_stat_activity

One row per server process, showing information related to the current activity of that process, such as state and current query. Find it useful as lookup for more context info missing in event trigger with ddl_command_start.

https://www.postgresql.org/docs/9.2/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

debug level

SET client_min_messages TO DEBUG5;

all available from DEBUG5 to PANIC described here https://www.postgresql.org/docs/9.1/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS

using it in PL/PgSQL:


RAISE NOTICE `running as user: %', session_user;

using it in plpythonu:

plpy.debug('query: {}'.format(query))

Typed python + typed NamedTuple <3

andi — Wed, 05 Jun 2019 09:48:52 +0000

be aware of limitations:

Function annotations are nothing more than a way of associating arbitrary Python expressions with various parts of a function at compile-time.

https://stackoverflow.com/a/43977962/953553

way to use:

defining a class with typed fields
specify types of args the functions receive

how it is checked?

e.g good IDE like pycharm will do it
install pep8 and mypy-lang to be warned

EXAMPLES

basic types

(run with pep8 and mypy-lang installed):

def greeting(name: str) -> str:
     return 'Hello ' + name

then calling:

greeting(2)

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<stdin>", line 2, in greeting
TypeError: Can't convert 'int' object to str implicitly

greeting('3')
#'Hello 3'

support for classes and inheritance

more complex types:

List

from typing import List

Tuple

from typing import Tuple

NamedTuple

from typing import NamedTuple

Dict

from typing import Dict

Union

is the way to allow more than one type for varaible.

Optional

more nice examples of typed stuff here:

https://blog.daftcode.pl/first-steps-with-python-type-system-30e4296722af

typed tuple in python3 is typed version of collections.namedtuple

from typing import NamedTuple


class Employee(NamedTuple):
    name: str
    id: int = 3

employee = Employee('Guido')
assert employee.id == 3

plpythonu PL/Python - on snake inside elephant

andi — Wed, 05 Jun 2019 09:47:26 +0000

actually we would to something another way around ;) snake inside the elephant, not the other way around like in the little prince story.

good intro can be this talk available on youtube:

followed by it's slides: https://wulczer.org/pywaw-summit.pdf

prelude - what do you need to setup in your postgres?

if you play with plpython3u on fresh postgres instance you will probably need install/setup the following:

apt-get update
apt-get install postgresql-plpython3-11

be careful to install good version for your postgres version:

inside psql:

CREATE EXTENSION plpython3u;

example fun

create function histogram(a float[], bins int = 10)
returns int[]
as $$
import numpy
return numpy.histogram(a, bins)[0]
$$ language plpython3u;

you may need install on postgres machine additional python libraries (e.g numpy for above), first install pip: apt-get install python3-pip

Important things to remember:

keep you functions in separated module with option to mock plpython magic, so these are GOOD tested

python dataclasses more DRY, a kinda of scaffold for methods

andi — Wed, 05 Jun 2019 09:46:58 +0000

dataclass - New in version 3.7 (https://docs.python.org/3/library/dataclasses.html)

f'' formatting - since 3.6 (https://realpython.com/python-f-strings/)

described in PEP0557

Although they use a very different mechanism, Data Classes can be thought of as "mutable namedtuples with defaults". Because Data Classes use normal class definition syntax, you are free to use inheritance, metaclasses, docstrings, user-defined methods, class factories, and other Python class features.

the decorator adds generated method definitions to the class to support instance initialization, a repr, comparison methods, and optionally other methods(...) Such a class is called a Data Class, but there's really nothing special about the class: the decorator adds generated methods to the class and returns the same class it was given.

https://www.python.org/dev/peps/pep-0557/

EXAMPLE!

@dataclass
class InventoryItem:
    '''Class for keeping track of an item in inventory.'''
    name: str
    unit_price: float
    quantity_on_hand: int = 0

    def total_cost(self) -> float:
        return self.unit_price * self.quantity_on_hand

The @dataclass decorator will add the equivalent of these methods to the InventoryItem

def __init__(self, name: str, unit_price: float, quantity_on_hand: int = 0) -> None:
    self.name = name
    self.unit_price = unit_price
    self.quantity_on_hand = quantity_on_hand
def __repr__(self):
    return f'InventoryItem(name={self.name!r}, unit_price={self.unit_price!r}, quantity_on_hand={self.quantity_on_hand!r})'
def __eq__(self, other):
    if other.__class__ is self.__class__:
        return (self.name, self.unit_price, self.quantity_on_hand) == (other.name, other.unit_price, other.quantity_on_hand)
    return NotImplemented
def __ne__(self, other):
    if other.__class__ is self.__class__:
        return (self.name, self.unit_price, self.quantity_on_hand) != (other.name, other.unit_price, other.quantity_on_hand)
    return NotImplemented
def __lt__(self, other):
    if other.__class__ is self.__class__:
        return (self.name, self.unit_price, self.quantity_on_hand) < (other.name, other.unit_price, other.quantity_on_hand)
    return NotImplemented
def __le__(self, other):
    if other.__class__ is self.__class__:
        return (self.name, self.unit_price, self.quantity_on_hand) <= (other.name, other.unit_price, other.quantity_on_hand)
    return NotImplemented
def __gt__(self, other):
    if other.__class__ is self.__class__:
        return (self.name, self.unit_price, self.quantity_on_hand) > (other.name, other.unit_price, other.quantity_on_hand)
    return NotImplemented
def __ge__(self, other):
    if other.__class__ is self.__class__:
        return (self.name, self.unit_price, self.quantity_on_hand) >= (other.name, other.unit_price, other.quantity_on_hand)
    return NotImplemented

https://docs.python.org/3/library/dataclasses.html