nyuuzyou/wikis huggingface dataset:
Search public functions

Search all functions in your PostgreSQL db for any matching terms.
The input will be used as the regular expresson in the regexp_matches() function.
Eg. 
to find 7 - 10 consecutive digits in any function:
 select function_name,matching_terms from search_public_functions('[0-9]{7,10}',true);
 
string example at the bottom of page
-- load into db as superuser
create or replace function search_public_functions(p_search_strings TEXT, p_case_insensitive BOOLEAN, OUT function_name TEXT, OUT matching_terms TEXT) RETURNS SETOF RECORD AS
$body$
declare
 x RECORD;
 qry TEXT;
 v_match BOOLEAN := 'false';
 v_matches TEXT;
 v_search_strings TEXT := p_search_strings;
 v_case_insensitive BOOLEAN := p_case_insensitive;
 v_funcdef TEXT;
begin
 /* v_search_strings is a list, pipe-separated, exactly what we want to search against.
 NOTE: works on postgresql v8.4
 example:
 select function_name,matching_terms from search_public_functions('crosstab|intersect|except|ctid',true);
 */
 if (v_case_insensitive IS NOT FALSE) then
 v_case_insensitive := TRUE;
 end if;
 qry := 'SELECT n.nspname||.||p.proname|| (||pg_catalog.pg_get_function_arguments(p.oid)||)::TEXT as funcname,
 (select pg_catalog.pg_get_functiondef(p.oid)) as funcdef,
 p.oid as funcoid
 FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 WHERE pg_catalog.pg_function_is_visible(p.oid)
 AND n.nspname <> pg_catalog
 AND n.nspname <> information_schema
 AND NOT p.proisagg
 ORDER BY 1';
 if (p_case_insensitive IS TRUE) then
 v_search_strings := LOWER(v_search_strings);
 end if;
 for x in execute qry loop
 v_match := 'false';
 function_name := null;
 v_funcdef := null;
 select into v_match x.funcdef ~* v_search_strings;
 if ( v_match IS TRUE ) then
 v_matches := null;
 v_funcdef := x.funcdef;
 if (p_case_insensitive IS TRUE) then
 v_funcdef := LOWER(v_funcdef);
 end if;
 select array_to_string(array_agg(val),',') into v_matches from (select distinct array_to_string(regexp_matches(v_funcdef, v_search_strings ,'g'),',') as val) as y2;
 function_name := x.funcname;
 matching_terms := v_matches;
 RETURN NEXT;
 end if;
 end loop;
end;
$body$ language plpgsql SECURITY DEFINER;
It can be called like so:
 select function_name,matching_terms from search_public_functions('crosstab|intersect|except|ctid',true);
 
 
 function_name | matching_terms 
 -------------------------------------------------------+----------------
 public.array_intersect (anyarray, anyarray) | intersect
 public.cant_delete_error () | except
 public.crosstab2 (text) | crosstab
 public.crosstab3 (text) | crosstab
 public.crosstab4 (text) | crosstab
 public.crosstab (text) | crosstab
 public.crosstab (text, integer) | crosstab
 public.crosstab (text, text) | crosstab
 public.find_bad_block (p_tablename text) | ctid,except
 
original code (and any errors) by bricklen 
 
Category:PL/pgSQL
SEPostgreSQL-patch

Please do not discuss this URL on the hackers email list until Wednesday, 2009-01-28 1200 GMT.
== Introduction ==
This patch introduces two new capabilities to Postgres. The first is SQL-level row permissions. With this feature, you can assign permissions to rows similar to the permissions assigned to tables:
 INSERT INTO ratbl_p (security_acl, a, b)
 VALUES ('{rausr_x=r/rausr_o}', 2, 'bbb');
Permissions are assigned by specifying literal strings to the system column 'security_label', rather than using GRANT/REVOKE commands.
The second feature is SE-Linux integration. Security values are assigned as literal strings, like SQL-level row permissions:
 INSERT INTO t1 (security_label, x, y)
 VALUES ('system_u:object_r:sepgsql_table_t:s0', 1, 'aaa');
The system column used is 'security_label'. The big advantage of SE-Linux integration is that database permissions are controlled by a site-wide security policy, rather than a database-specific one.
This wiki has an excellent introduction to the feature set: http://wiki.postgresql.org/wiki/SEPostgreSQL
This basically implements TCSEC (Trusted Computer System Evaluation Criteria). While it currently only supports SE-Linux, it could be extended to support other security infrastructures.
== Patches ==
Most recent patch submission: http://archives.postgresql.org/pgsql-hackers/2009-01/msg00661.php
Documentation patch: http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1398.patch
Regression test patch (has examples): http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1398.patch
== Questions ==
* Have we avoided covert channels as well as we can? http://archives.postgresql.org/pgsql-hackers/2008-09/msg01750.php
* Is the ability to see system table information properly protected?
== Email Discussions ==
* http://archives.postgresql.org/pgsql-hackers/2009-01/msg01991.php
* http://archives.postgresql.org/pgsql-hackers/2009-01/msg02295.php
== Code Size Impact ==
Here is my analysis of the code impact of SE-PostgreSQL vs. text search, which was included in PostgreSQL 8.3. Text search was 4x larger, if measured by total lines or by lines outside dedicated directories; the only larger aspect of SE-PostgreSQL is that there are more C files affected:
 TEXT SEARCH:
 total lines	48924
 /snowball	27232
 /tsearch	 7199
 utils/adt	 6180
 mixed	 8313
 -------
 C files: 46
 SE-PGSQL:
 total lines	10040
 /security	 7721
 mixed	 2319
 -------
 C files: 104
== TODO ==
* Allow system columns 'security_acl' and 'security_label' to be assigned per row without requiring CREATE TABLE options, and require no storage space if not assigned
* Find way to delete unreferenced pg_security rows
== Press ==
Kaigai Kohei and SE PostgreSQL are already listed on the U.S. National Security Agency (NSA) website: http://www.nsa.gov/research/selinux/contrib.shtml
Category:SELinux
Sequential disk access during VACUUM for GiST

== Contact ==
name - Constantine Kuznetsov
via email - chapaev28@ya.ru
== Introduction ==
I work on this idea later. http://www.postgresql.org/message-id/CAPpHfdtxftKcM43EBbj=KteW-3LTTRTkm_ABEt0pMvkfVwQwSA@mail.gmail.com
But then there were problems with "jump back" to rescan parent page.
Now i propose implementation with 2 steps:
1. physical order scan. Creation(child,parent) of hash that needed to fast search parent page.
2. scan of page that be needed rescan after 1 step.
== Expected project schedule ==
1. Implement new gistbulkdelete function. 2 weeks.
2. Test this implementation and writing regression test. 2 weeks.
3. Getting the first result and discussion with mentor. 1 week.
4. Add all memory limitation(maintenance_work_mem) to implementation. 1 week. 
5. If there are problems then solve their. 2 weeks.
6. Cleaning of code, writing of coments and preparing to patch. 1 week.
Serializable DDL

This is an initial discussion of what behavior "Serializable DDL" might have in PostgreSQL.
Notes:
 - Right now, when two serializable transactions run, you can have t1 begin first, then t2 starts and creates a table, t2 then commits. t1 started first, and so it can't see the new table's entry in pg_class, but because we use "SnapshotNow" for table lookups, t1 can actually select from t2's new table.
 - We have to address the issue of FKs being added against existing tables, or even being for new tables, and what the "right" answer is
 - We already have some mechanics to deal with indexes being added and keeping them from being visible "too early" (indcheckmin)
 - We may have to add similar mechanics for constraints, again, to avoid them being visible "too early"
What needs to be visible immediately and what doesn't?
Category:SQL
SerializableToDo

A list of smaller ideas for improvements to SERIALIZABLE:
* Does TABLESAMPLE really need to predicate-lock whole relations? Discussion
* Does index-only-scan really need to predicate-lock whole pages? Discussion Discussion
* Can exclude constraint violations be made to check for SSI failures, like we did for unique constraint violations? Discussion
* Ugh. It doesn't even work correctly for multi-btree tables. Bug report
TL;DR for someone interested hacking on SERIALIZABLE and looking for a small project with real world applications: It would be really nice if SSI failures took priority over unique and exclude constraint violations, because that is a practical requirement for automatic transaction retry. For example, imagine the client puts the logic to run a transaction into a function (lambda, code block, whatever) that will be re-run automatically inside a new transaction by something on the client side (eg Python/Java/Ruby/whatever transaction support libraries) that recognises retryable error conditions (SSI failures, deadlocks, ...). Whereas unique constraint violations should not be retried. The current arrangement makes SSI retry less automatable and thus less usable. The first step would be to get some "isoation tests" going that initially show the currently behaviour (eg new files under src/test/isolation/specs, basic example posted in the multi-btree thread, we also need one for exclusion constraints), and then figure out how to get the desired behaviour working (perhaps with some of the ideas from the threads above).
Bigger ideas to investigate:
* SSI didn't turn out to scale very well past a few cores, due to contention on a couple of LWLocks. Redesign locking protocol, find a better way to clean up finished transactions that isn't so contended? This "garbage collection" is currently serialized and performs terribly.
* Can we use hardware transactional memory to implement SSI efficiently? Early stage experimental patch (this is wild pie-in-the-sky stuff)
* Can we allow SERIALIZABLE READ ONLY DEFERRABLE transactions on streaming replica nodes? Early stage experimental patch Thomas's blog on the topic
* Can we allow scan order to be magically changed (conceptually like SKIP LOCKED, but you wouldn't actually say that) to allow queue-like access patterns to minimise conflicts, to fix the famous worst case for optimistic concurrency control schemes like SSI? Thomas's blog on the topic
* Can we export SSI conflict graphs over postgres_fdw? Suppose we have a 2PC-based multi-server transactions as proposed, then there could be a system to merge all of the conflict graphs from remote nodes into the lead server's transaction set. Transactions could be rejected 'locally' in cases where individual nodes can prove they are not serializable, and global dangerous cycles could be detected after merging the graphs. This requires a way to label SERIALIZABLEXACTs (probably with xid on lead server). Then we could have federated SERIALIZABLE transactions (but only when everyone goes through the same head node).
Set equality

Array equality is sensitive to the order and duplication within the arrays, so it is not suitable for testing arrays as if they were sets.
Set equality can obtained by testing arrays for "contains and is contained by".
= Implementation =
This is a convenience operator. It is not performance optimized, does not support indexes, and does not consider multidimensional arrays.
create function contains_contained_by (anyarray, anyarray) returns boolean 
language SQL as $$
 select $1 <@ $2 and $1 @> $2 
$$
parallel safe --omit this line prior to version 9.6 
immutable; 
create operator <@> (
 procedure = contains_contained_by, 
 leftarg = anyarray, 
 rightarg = anyarray
);
= Usage =
 select '{c,a,b}'::text[] <@> '{b,a,c,b}'::text[]; 
Category:SQL
Set privileges for stored functions in bulk

The query below will generate "GRANT EXECUTE" statements for every stored function in the given namespace to a specified role.
By changing the select statement at the bottom it can be used for other statements that require the function name with the parameter list.
SELECT 'GRANT EXECUTE ON FUNCTION ' || oid::regprocedure || ' TO MyTargetRole;'
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'MyNameSpace');
Category:SQL
SFPUG

= Where to find SFPUG =
Our meetings are usually posted on Meetup. You can also join our mailing list. We meet in San Francisco, Berkeley and Oakland. We have a sister organization which meets in Silicon Valley.
If there is an SFPUG meeting going on right now, you can check out the streaming broadcast.
= SFPUG Videos =
Here are videos of some of our meeting presentations. Video recording and editing is generously contributed by Chanticleer Films. 
* - LISTEN/NOTIFY April 2013 Talk - Sample code and slides
* sfpug-unison-20090311.mov (186.3MB)
* sfpug-cloud-20090408.mov (1062.8MB)
* sfpug-birt-20090512.mov (983.6MB)
* sfpug-rails-20090609.mov (403.5MB)
* sfpug-window-20090811.mov (318.5MB)
= PG9 Beta Test Day =
The SFPUG is in the initial planning stages of organizing a PG9 beta-test day to hammer on the new features. Attendees will not only help get 9 out-the-door but will have the added benefit of getting experience with the new features. We need computers, support-gear and you. A planning page for this event is set up at SFPUG Beta Test Day 
Category:Users group
Show database bloat

== Notes ==
These queries is for informational purposes only. They provide a loose estimate of table growth activity only, and should not be construed as a 100% accurate portrayal of space consumed by database objects. To obtain more accurate information about database bloat, please refer to the pgstattuple or pg_freespacemap contrib modules.
Also note that before version 9.5, data types that are not analyzable, like xml, will make a table look bloated as the space needed for those columns is not accounted for.
== Bloat queries ==
For tables, see these queries. Make sure to pick the correct one for your PostgreSQL version.
For Btree indexes, pick the correct query here depending to your PostgreSQL version.
For more informations about these queries, see the following articles.
== Query from check_postgres ==
This snippet displays the estimated amount of bloat in your tables and indices. Snippet is taken from Greg Sabino Mullane's excellent check_postgres script.
SELECT
 current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
 ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
 CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
 iname, /*ituples::bigint, ipages::bigint, iotta,*/
 ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
 CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
 SELECT
 schemaname, tablename, cc.reltuples, cc.relpages, bs,
 CEIL((cc.reltuples*((datahdr+ma-
 (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
 COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
 COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
 FROM (
 SELECT
 ma,bs,schemaname,tablename,
 (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
 (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
 FROM (
 SELECT
 schemaname, tablename, hdr, ma, bs,
 SUM((1-null_frac)*avg_width) AS datawidth,
 MAX(null_frac) AS maxfracsum,
 hdr+(
 SELECT 1+count(*)/8
 FROM pg_stats s2
 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
 ) AS nullhdr
 FROM pg_stats s, (
 SELECT
 (SELECT current_setting('block_size')::numeric) AS bs,
 CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
 CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
 FROM (SELECT version() AS v) AS foo
 ) AS constants
 GROUP BY 1,2,3,4,5
 ) AS foo
 ) AS rs
 JOIN pg_class cc ON cc.relname = rs.tablename
 JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
 LEFT JOIN pg_index i ON indrelid = cc.oid
 LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC
Sieve of Eratosthenes

Kudos to David Fetter for finally solving this one. 
WITH RECURSIVE
t0(m) AS (
 VALUES(1000)
),
t1(n) AS (
 VALUES(2)
UNION ALL
 SELECT n+1 FROM t1 WHERE n < (SELECT m FROM t0)
),
t2 (n, i) AS (
 SELECT 2*n, 2
 FROM t1 WHERE 2*n <= (SELECT m FROM t0)
UNION ALL
 (
 WITH t3(k) AS (
 SELECT max(i) OVER () + 1 FROM t2
 ),
 t4(k) AS (
 SELECT DISTINCT k FROM t3
 )
 SELECT k*n, k
 FROM
 t1
 CROSS JOIN
 t4
 WHERE k*k <= (SELECT m FROM t0)
 )
)
SELECT n FROM t1 EXCEPT
SELECT n FROM t2
ORDER BY 1;
Simulating iif function

by Emanuel Calvo Franco and Hector de los Santos
Some developers are accustomed to some 'Access' (and others db's) functions.
One of them is the 'iif' function that works very simple and sometimes add
to the query more intelligence.
Well, you will surprising to know that in Postgresql is possible and is quite
simple to add this function.
In plain SQL:
CREATE OR REPLACE FUNCTION iif_sql(boolean, anyelement, anyelement) returns anyelement as
$body$ select case $1 when true then $2 else $3 end $body$
LANGUAGE sql IMMUTABLE;
while you can cover all datatypes at once with this polymorphic function, the type needs to be defined or specified using casts. A simple iif_sql(8<9,'yes','no') will fail. To solve this, you could add an overloaded method to cover this specific case:
CREATE OR REPLACE FUNCTION iif_sql(boolean, unknown, unknown) returns text as
$body$ select case $1 when true then textin(unknownout($2)) else ﻿﻿textin(unknownout($3)) end $body$
LANGUAGE sql IMMUTABLE;
In PL/Pgsql:
CREATE OR REPLACE FUNCTION iif_(boolean, double precision, double precision) RETURNS double precision AS
$body$
DECLARE
	rtVal double precision;
BEGIN
	rtVal := (select case $1 when true then $2 else $3 end);
	return rtVal;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
In PL/Perl (WARNING - we are doing a diferent way of the same problem,
if you want to do it exactly like plpglsql, you must use spi_query_exec() function):
CREATE OR REPLACE FUNCTION iif_perl(boolean, double precision, double precision) RETURNS double precision AS
$body$
if($_[0] =~ /t/){
	return $_[1];
}else{
	return $_[2];
}
$body$
LANGUAGE 'plperl' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Like an C shared object:
#include <stdio.h>
#include "postgres.h"
#include "executor/executor.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(iif_c);
Datum
iif_c (PG_FUNCTION_ARGS){
 bool message = PG_GETARG_BOOL(0);
 float8 first = PG_GETARG_FLOAT8(1);
 float8 second = PG_GETARG_FLOAT8(2);
 if(message == 1){
 PG_RETURN_FLOAT8(first);
 }else{
 PG_RETURN_FLOAT8(second);
 }
}
But in this way you must execute the compilation and statement 'CREATE FUNCTION':
postgres@pgsql:/usr/local/pgsql84/$ gcc -I include/server/ --shared iif_c.c
...and then:
CREATE OR REPLACE FUNCTION iif_c(boolean, double precision, double precision) RETURNS double precision
 AS '/usr/local/pgsql84/compiled/a.out', 'iif_c'
 LANGUAGE C STRICT;
-- test it? go!
SELECT iif_c(9<8,0,9);
Notice that you must modify the type of parameters to accept another type of values!! (except the first).
Between these functions, we have different 'total runtimes':
plpgsql: Total runtime: 0.091 ms
plperl: Total runtime: 0.116 ms
C: Total runtime: 0.034 ms
What a performance! :D
SIP

This page contains information relevant to PostgreSQL about the feature System Integrity Protection (SIP) on macOS.
When you run make check, PostgreSQL creates a temporary installation (in tmp_install/) and starts all the executables needed for the test from there. That way, the tests are run in a normal installation layout. In order for that to work, however, we also need to arrange it so that the shared libraries are picked up from this temporary installation instead of the notional real installation directory (specified by ./configure --prefix). This is done by setting an environment variable such as LD_LIBRARY_PATH on Linux and DYLD_LIBRARY_PATH on macOS. So far, so good.
Recent versions of macOS come with a feature called System Integrity Protection (SIP) enabled by default. This feature encompasses a number of additional security and permission checks. For example, it prevents you from overwriting core operating system files even as root. That's all reasonable. Unfortunately (for us), it also disables the use of DYLD_LIBRARY_PATH, so the above scheme doesn't work out of the box on macOS. If you run make check with SIP enabled, it will look for libraries in the actual installation directory. If there are no suitable files there, the tests will probably crash. You can work around that by always running make install before make check. Note that if you only run make install sometimes before make check, you will end up testing a mix of previously installed code and new code, which could misbehave or in any case not test the code you are actually looking at, thus defeating the point of make check.
Some (many?, most?) PostgreSQL developers turn SIP off for their regular development work. Make your own decision about that.
To disable SIP, boot into recovery mode, open a terminal, and run
 csrutil disable
and reboot.
In macOS version 10.14 and later, it is sufficient to disable
the "Debugging" part of SIP, by running
 csrutil enable --without debug
instead.
The status of SIP can be shown using
 csrutil status
Note that that status display does not reflect changes until after a reboot. Also, on some versions of macOS, if you use the enable --without approach, it appears the status display is wrong (it shows the wrong flags as enabled).
Here are some mailing list threads where this issue was discussed and various workarounds were contemplated (mostly to no avail):
* https://www.postgresql.org/message-id/flat/6a4d6124-41f0-756b-0811-c5c5def7ef4b%402ndquadrant.com
* https://www.postgresql.org/message-id/flat/ecec88aa-c982-a473-1b0f-cea596d405cf%40enterprisedb.com
SkyTools

This is a package of tools in use in Skype for replication and failover. It includes a generic queuing mechanism, PgQ and utility library for Python scripts.
The source code was hosted on Pgfoundry (archived) and later on GitHub, tech lead is Marko Kreen.
A general overview presentation can be found here: , and a PGQ detailed presentation is at http://www.pgcon.org/2008/schedule/events/79.en.html.
== PgQ ==
This is the queue mechanism we use. It consists of PL/pgsql, and C code in database, with Python framework on top of it. It is derived from snapshot-based event handling ideas from Slony-I, re-written for more general use.
=== Features ===
* A database can have many queues. 
* Producers can insert into any queue. 
* There can be several consumers for any given queue and all consumers see all events. 
=== Documentation ===
* PGQ Tutorial
* PgQ admin tool (pgqadm)
* PgQ SQL API overview
* PgQ SQL reference
=== Monitoring ===
A PGQ Munin Plugin is available.
=== See Also ===
PGQ can be the base solution to implement a BASE solution (basically available, soft state, eventually consistent), or more generally to implement an Eventually Consistent system.
Kristo's blog host a detailed example of a python PGQ consumer which sends confirmation emails after user inscription: http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/
== Londiste ==
Replication tool written in Python, using PgQ as event transport, available on Github. GitHub
=== Features ===
* Tables can be added one-by-one into the replication set. 
* The initial COPY for one table does not block event replay for other tables. 
* Can compare tables on both sides. 
=== Documentation ===
* Londiste Tutorial
* Skytools docs are no longer available
=== Monitoring ===
Londiste is "just" a PGQ Consumer, so see about how to monitor the queue itself, with the PGQ_Munin_Plugin e.g.
== walmgr ==
This script sets up WAL archiving, does initial backup and runtime WAL archive and restore.
See its documentation.
== Provided specific consumers ==
Some more tools based on Skytools Framework are available for handling specific jobs :
* Bulk Loader
* Cube Dispatcher
* Queue Mover
* Queue Splitter
* Table Dispatcher
Each documentation page begins with a description section, but to ease your browsing experience, they are all in the same page here: Skytools Specific Consumers.
Category:Replication
Category:Middleware