Názory k článku Jak nepoužívat PL/pgSQL (případně PL/SQL) – fatální chyby při vývoji
-
houskat (neregistrovaný)
Super clanek.
Nevim jak v postgre, ale u tech typu (u mssql) bych jeste pridal poznatek, ze typ muze byt totozny, ale u textu zalezi i na collation (tedy, jestli ma byt porovnani case sensitive, pripadne accent sensitive, pripadne jaka je code page). Pri rozdilnosti pak musi server taky vicemene provadet scan (pokud se jedna o join).
Na prvni pohled to neni videt, protoze se pri definici typu pro tabulku/promennou pouzije default collation (vlastnost databaze). Pri rozdilnosti sice server upozorni, ze se musi jeden z nich vybrat, ale programator tam vetsinou bez rozmyslu hodi jednu z nich, misto toho aby se zamyslel nad tim, ktera tabulka bude v budoucnu mensi a proto bude jeji scan lacinejsi. (pripadne se zamyslel nad tim, jestli vubec ma mit ruzne collation v databazi) -
Karel (neregistrovaný)
Oracle má také jednu extra vrstvu legrace ve chvíli, kdy se nastaví, že je case-insensitive. On stále v datech bude mít mix velkých a malých písmen. Pokud se pak objeví podmínka " WHERE X = 'Pepa' ", tak se stanou dvě věci:
1. Oracle vypočítá hodnotu pro 'Pepa': HEXTORAW('7065706100')
2. Nebude pracovat se slupcem X, ale s touhle funkcí: NLSSORT("X" , 'nls_sort=''BINARY_CI''')Výkon půjde okamžitě do kolen, přestože si datový typ plně odpovídá. Řešením je udělat si index s tou funkcí NLSSORT. Bohužel to platí i pro JOIN - nedělá se přes sloupce, ale přes ty NLSSORT funkce. Dokud je v obou tabulkách index s funkcí, tak to funguje dobře. Jakmile chybí, tak je problém.
Přidejte fakt, že case (in)sensitive je parametr session, nikoliv instance, a legrace se násobí!
-
Karel (neregistrovaný)
Teď jsem to ověřil na Oracle 12c. Bylo to nastaveno při vytváření instance a je to vidět nastavené v V$SYSTEM_PARAMETER - nls_comp = LINGUISTIC, nls_sort = BINARY_CI.
Po přihlášení uživatele SYS se ale do session použije BINARY, BINARY.
Aplikační uživatel mi tam po loginu taky nacpe BINARY.Takže BINARY_CI mi to bere jen když mu to explicitně nastavím triggerem na session. Nastavení instance nemá na nic vliv. Pokud si dobře pamatuji to, co nás učili, tak je to tím, že přednost má nastavení session a ta bere hodnotu z klienta. Pokud klient nemá nastaveno nic, tak se bere default (NULL), což je BINARY. Proto je potřeba to vždy v login triggeru do session vnutit.
-
Martinus (neregistrovaný)
Tak jsem to nastavil na 11g a funguje to jak ma .. nemate na klientovy nastaveny nls_comp a nls_sort variablies?
Puvodni nastaveni:
SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_SORT','NLS_COMP');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT
NLS_COMP BINARYSQL> alter system set nls_comp=LINGUISTIC scope=spfile;
System altered.
SQL> alter system set nls_sort = BINARY_CI scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 536874064 bytes
Database Buffers 289406976 bytes
Redo Buffers 6565888 bytes
Database mounted.
Database opened.Nastaveni po zmene:
SQL> show user
USER is "SYS"SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_SORT','NLS_COMP');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT BINARY_CI
NLS_COMP LINGUISTICSQL> show parameter nls_comp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_comp string LINGUISTIC
SQL> show parameter nls_sortNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY_CITaky aplikacni user to vidi spravne:
SQL> show user
USER is "SORT"
SQL> show parameter nls_compNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_comp string LINGUISTIC
SQL> show parameter nls_sort;NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY_CI
SQL> -
Martinus (neregistrovaný)
Jeste doplnim .. env variable na to nema vliv, pouze alter session, po prihlaseni opet hodnota z spfile:
SQL> hradec:~ $ env|grep NLS
NLS_SORT=BINARY
hradec:~ $ sqlplus sortSQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 07:33:33 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_SORT','NLS_COMP');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT BINARY_CI
NLS_COMP LINGUISTICSQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY_CI
SQL> alter session set nls_sort=binary;Session altered.
SQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARYSQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
hradec:~ $ sqlplus sortSQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 07:34:31 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter nls_sort
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string BINARY_CI -
Karel (neregistrovaný)
select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_SORT','NLS_COMP')
NLS_SORT = BINARY_CI
NLS_COMP = LINGUISTICshow parameter nls_comp
nls_comp string BINARY
Nastavení klienta ovlivnit nemůžu. Tak jak je nastaven teď to bez login triggeru dopadne viz výše. Klient, to je úplně jiný tým lidí na jiném kontinentu. Proto je to v login triggeru, na nastavení klienta se spolehnout nemůžeme. Žil jsem v domění, že na to nespoléhá nikdy nikdo.
-
Martinus (neregistrovaný)
Ja uz jsem setkal s takovou sortou lidi a nastavenim klientu, ze se uz nedivim vubec nicemu a co nevidim na vlastnim monitoru a nemuzu primo vyzkouset ... tak tomu neverim :-))
Trosku jsem pohledal v Database Globalization Support Guide
https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188Tabulka priorit pro nastaveni nls je:
Table 3-1 Methods of Setting NLS Parameters and Their Priorities
Table 3-1 shows the precedence order of the different methods of setting NLS parameters. Higher priority settings override lower priority settings. For example, a default value has the lowest priority and can be overridden by any other method.Priority Method
1 (highest)
Explicitly set in SQL functions2
Set by an ALTER SESSION statement3
Set as an environment variable4
Specified in the initialization parameter file5
DefaultNLS_COMP a NLS_SORT lze nastavit vsemi zpusoby: I = Initialization Parameter File E = Environment Variable A = ALTER SESSION
Hodne stesti, Oracle forever ;-)
-
qwe (neregistrovaný)
K tomu psani kodu v PgAdminu a podobnych nastrojech.. (ted trochu ze strany vyvoje oracle a PL/SQL a nastoje k tomu).
Kod se ma psat v IDE ktery je na to urcene, psat to v editoru x je sice mozne, ale z daleka nie je to tak pohodlne jak k tomu urcene IDE. Naseptavani, hinty, warningy, chyby z kompilace.
Souhlas s dalsima nazorama z te sekce, ale rekl bych ze vyvoj v IDE pro PL/SQL a nad tou databazi (idealne se stejnyma strukturama, i kdyz je to jenom nejaka lokalna kopie), je pohodlnejsi a bezpecnejsi. -
Ono je jedno, jestli to píšete v PgAdminu nebo v něčem .. důležité je, kde jsou zdrojáky uložené. Je chybou, když při deploymentu děláte reverze engineering databáze. Navíc přicházíte, jak už bylo zmíněno, o verzování, větvění, v podstatě přicházíte o jakou koliv moderní správu kódu ... K tomu mám zkušenost, že díky tomuto stylu se na produkční databáze zanáší nehotový, mrtvý kód.
Bohužel pro Postgres žádné kvalitní cost free prostředí neexistuje (podle mého gusta). Existují extenze do Emacsu a i Vi, nicméně to asi nebude pro každého.
-
qwe (neregistrovaný)
U compilovanych objektu PL/SQL, ci PL/pgSQL (package, procedury, funkce view, pripadne i objektove typy) reverse engineering potreba nie je. Create a replace to resi. O to horsi jsou tabulky (a typy/objekty s navaznostama na dalsi typy).
A to je ta cast kde prave dane IDE exceluje. Ano kod bude v DB, ale nic mi v tom nebrani abych to ukladal do souboru, verzoval.Nemit zdrojaky v DB je jednak nemozne, a jinak clovek by prisel i o to ze vlastni praci 'zvaliduje'.
Nemit zdrojaky v mimo DB je taky blbe, clovek prijde o historii vlastni prace (krasna znama situace kdyz to nekdo prepise), nemit je verzovane na dlhodoby projekt docela nemozne.Verzovani, vetveni potreba, tie argumenty ani nepopiram, ale s IDE/nastojema ktery to primo kompiluju do DB a resi veskere navaznosti na dalsi objekty.
Bohuzel ani pro Oracle zadne free cost reseni na to neexistuje.
K verzovani nasazovani je tam mimojine Liquibase (ne jenom oracle ale kopa dalsich sql databaz), ktery pomaha k verzovani a rollbacku, dobre se integruje do CI, ale trochu i pridava praci. -
Možná si představujem trochu něco jiného pod pojmem reverse engineering. Pokud nemáte větvě, tak všichni jedou jakoby v masteru - a jelikož se bojí přepisu, tak pushují i nedodělanou práci. Pokud by dva uživatele upravovali stejnou funkci, tak to dopadne špatně. Tím rychlým pushováním se vám do mástera (nic jiného neexistuje) může dostat nedopečený kód. A jelikož se dělá komplet deployment - tak takový kód se dostane i na produkci.
Když máte kód primárně v souborech a ve verzovacím systému, tak můžete udělat merge, rebase - kolize zjistíte.
Samozřejmě, že pro testování ten kód nahraji do db - a zde je umím i zvalidujovat - to se jinde udělat nedá.
Ještě jedna poznámka - ve své poznámce jsem primárně cílil na Postgres. Práce s db objekty, kompilace, řešení dependencí je v Postgresu hodně jiná než v Oracle. To vyvedení db objektů do souborů se v PG udělá jednodušeji - v některých případech je i nezbytné (např. u pohledů). Navíc Oracle má packages - a kód z packages má hodně podobný formát tomu, co by vzniklo, když uložím pg zdrojáky do souboru.
Určitě se nebráním kvalitnímu IDE - a kdybych nebyl líný, tak bych si napsal makra do Emacsu nebo geditu (zas na druhou stranu nemusím udělat všechno sám :)). V každém případě, to na co jsem hlavně upozorňoval je použití čistě pgAdmina - který sice umí autocomplete, ale neumí verzování, zahazuje vnější komentáře - a nemá nic, co by umožňovalo vytvářet vyšší celky ve smyslu modulů nebo packages.
-
qwe (neregistrovaný)
Jo, slo o nedorozumeni s reverse engineeringem.
K verzovani kodu by to chtelo jeste rozumny build system, a nejaky volne dostupny, jednoducho pouzitelny pro Oracle jsem taky nenarazil.
Vyvedeni kodu z oraclu do souboru problem nie je, u tabulek diff mezi systemama je trochu horsi. Porovnani s PG nemam, skusenosti s PG nejsou moc hluboke.
Kvalitne IDE pro Oracle existuji, ale u kazdeho chyby neco..
Bohuzel vyvoj na databazemi v nekterych kulha nekde po ostatnich systemech, tak s toolingem, best practicema a obecne s ekosystemem. Minimalne ve sveta Oraclu.
-
honzucha (neregistrovaný)
Žádné lowcost řešení neexistuje. My máme development, test, smoke - test a production verze db. pravda v MS SQL, ale to na principu nic nemění. Developer v podstatě na základe jira tiketu vykoná praci pomocí create / alter. pak se z db vygeneruje kód a uloží jako Git branch, následuje merge request a jednou týdne release do Test -> Smoke Test -> Production to vše za pomoci Jenkins. ale od CI to má ještě hodně daleko. ze začátku to byl celkem bolestný proces, ale teď už je to v poho. Jako větší problém vidím unit testing t-sql a úplně nejhorší je jakykoliv testing MOLAP kostek, které jsou nad zmíněnými DB.
-
CTE je ohledně práce s katalogem gratis. Na druhou stranu nemá statistiky (a proto je gratis) - a co je občas výhodou, občas nevýhodou - v postgresu je implementováno jako bariéra pro optimalizátor - nepřenáší se odhady - https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/.
-
honzucha (neregistrovaný)
CTE dle mého skromného názoru by melo byt zakázáno. Když vidím SQL co je schopen zplodit analyst a pak se divi, že má dotaz běžící několik hodin a ani mu to nepřijde divný a špatný.... naposledy jeden zplodil cte s několika biliony rows a v podstatě nám odstřelil tempdb (mssql ) poté co narostla o 1.5 TB. nemluvě o tom, že jsme ho omylem zaradili do špatné skupiny a neaplikoval se na něj resource governor a granted query memory byla přes 500GB RAM (ano není to překlep, máme 3 TB RAM na každém sql node )
-
temp může zabít jakýkoliv kartézák, občas i interně generovaný, když má planner extra špatný odhad - na to nemusí být CTE. Na větších datech přeplnění tempu může být z nejrůznějších důvodů a vubec by mně to na čemkoliv nepřekvapilo. Od toho jsou timeouty, případně limity na temp files na pg. Dotazy, které generují BI tooly jsou občas síla. Dostal se ke mně pomalý dotaz na pg .. explain měl 3MB.
-
honzucha (neregistrovaný)
Tak o preplneni žádná. ale v tomhle pripade se zadny kartezak nekonal. To jen analyst (rozumějte člověk) napsal CTE s jedním dost špatným derivovaným pocitanym sloupcem aby toho nebylo málo tak to ještě groupnul pomocí case statementu a jako třešničku na dortu to pak v následujícím CTE joinul přes ten computed column s jinou tabulkou opět přes computed value. takže plan s column store vypadal asi tak, že místo v batch mode to celé běželo row by row.
Default timeout to sice řeší ale jen do okamziku, kdy ho změníte v connection stringu a dotyčný bohužel ještě vygooglil hinty a změnil i MAXDOP (degree of parallelism)
ale jak říkám toto je z mssql světa, ale principiálně stejné všude.
-
Ivan C (neregistrovaný)
I read the article while translated using Google Translate, so my apologies if I got "lost" ;-)
In the section "Additional performance recommendations" you mention "Instead of temporary tables in Postgres, use the field" (in original "Místo dočasných tabulek v Postgres používejte pole"). Can you please explain what this "field" (pole) is?
We use temporary tables extensively, and alternatives are very welcome.
Thank you.
-
Kathy13 (neregistrovaný)
No nic, jdu juknout na podobnou blbost :D http://pornot.cz/category/teen-holky/
-
Přišlo mi to jasné, ale uvedu příklad.
CREATE TEMP TABLE xx AS SELECT id FROM tab WHERE ... FOR r IN SELECT ... FROM t WHERE id IN (SELECT id FROM xx) --- lepe DECLARE xx int[]; SELECT array_agg(id) INTO xx FROM tab WHERE ... FOR r IN SELECT ... FROM t WHERE id = ANY(xx)
Důvodů, proč se používá dočasná tabulka může být vícero - někdy se používá pro předávání např. seznamu id mezi procedurami. V takovém případě se právě perfektně uplatní pole.
CREATE OR REPLACE FUNCTION zrus_xx(ids int[]) RETURNS void AS $$ BEGIN DELETE FROM tab WHERE id = ANY(ids) ..
ČLÁNKY DO MAILU