PostgreSQL Database Server

SQL is a very easy to use language and it is pretty easy to make things work out of the box. In fact, from my point of view simplicity is one of the core advantages of SQL in general. But, what if you want to compare dozens of columns with each other? It is not […]

It's been discussed for several years, so I'm very happy to finally be able to announce that next year, there will be a Nordic PGDay, in Stockholm, Sweden, on March 20th. It will follow a pattern that is common to those of you that have already ... [More] visited other PostgreSQL event - a combination of great talks from PostgreSQL insiders, and a hallway track to meet and talk with other PostgreSQL people. As this is our first instance in the Nordics, it will be a single-track single-day event, but we hope to grow it in the future. Our main target audience is in the Nordics, and we hope to see a lot of visitors not just from Sweden but also from our neighboring countries. Since this means the official language of the conference will be English, we will of course also welcome both visitors and speakers from the rest of Europe and the world as well! We have not yet opened our call for papers, but plan to do so shortly. We have also not opened registration yet, as we do not know exactly what the entrance cost will be yet. This will be depending on how many sponsors we can sign up for the event - which we have also not opened up for yet (a pattern is emerging here). But the date and venue is set, so mark your calendars and start moving other events around so you can be sure to make it... If your company is interested in sponsoring the event, please let us know at [email protected], and we'll be sure to send you our sponsorship information as soon as it's available! For the rest of you, keep up with our website or follow us on Twitter for updates! See you in Stockholm in March next year! [Less]

Three years ago I worked on complex project, that was written in PL/pgSQL almost. I wrote a regress tests, but these tests was not able to find all errors due missing coverage some paths. So I wrote simple extension, that ensured a plan preparation ... [More] for all embedded SQL on start. This extension helps me to find 99% bugs. I cleaned this extension and I published it as plpgsql_lint. As a plpgsql developer I am sure, so this functionality is missing in core, so I started in core implementation. Two years later I am still on start. I didn't find how to merge a requested functionality with current code cleanly (so committers will be happy) - probably we have to do some significant plpgsql refactoring first. And now is clean, so this code will not be in prepared release PostgreSQL 9.4. Although a code is not enough for committers, it can be useful for common plpgsql developers. I started new github project - plpgsql_check. It is plpgsql_lint (next generation). plpgsql_lint checked functions on start. plpgsql_check is coming with plpgsql_check_function. You can check any plpgsql function without execution. It can be joined with event trigger and you can ensure a extended check after function creating.examples:postgres=# LOAD 'plpgsql';LOADpostgres=# CREATE EXTENSION plpgsql_check;LOADpostgres=# CREATE TABLE t1(a int, b int);CREATE TABLEpostgres=#CREATE OR REPLACE FUNCTION public.f1()RETURNS voidLANGUAGE plpgsqlAS $function$DECLARE r record;BEGIN FOR r IN SELECT * FROM t1 LOOP RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column END LOOP;END;$function$;CREATE FUNCTIONpostgres=# select f1(); -- execution doesn't find a bug due empty table t1 f1 ──── (1 row)postgres=# \xExpanded display is on.postgres=# select * from plpgsql_check_function_tb('f1()');─[ RECORD 1 ]───────────────────────────functionid │ f1lineno │ 6statement │ RAISEsqlstate │ 42703message │ record "r" has no field "c"detail │ [null]hint │ [null]level │ errorposition │ 0query │ [null]postgres=# \sf+ f1 CREATE OR REPLACE FUNCTION public.f1() RETURNS void LANGUAGE plpgsql1 AS $function$2 DECLARE r record;3 BEGIN4 FOR r IN SELECT * FROM t15 LOOP6 RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column7 END LOOP;8 END;9 $function$select * from plpgsql_check_function('f1()', fatal_errors := false); plpgsql_check_function ------------------------------------------------------------------------ error:42703:4:SQL statement:column "c" of relation "t1" does not exist Query: update t1 set c = 30 -- ^ error:42P01:7:RAISE:missing FROM-clause entry for table "r" Query: SELECT r.c -- ^ error:42601:7:RAISE:too few parameters specified for RAISE(7 rows)postgres=# select * from plpgsql_check_function('fx()', format:='xml'); plpgsql_check_function ──────────────────────────────────────────────────────────────── <function oid="16400"> ↵ <issue> ↵ <level>error</level> ↵ <sqlstate>42P01</Sqlstate> ↵ <message>relation "foo111" does not exist</Message> ↵ <stmt lineno="3">RETURN</Stmt> ↵ <query position="23">SELECT (select a from foo111)</Query>↵ </Issue> ↵ </Function> (1 row) [Less]

It took sometime to me to understand the beautiful, and powerful concept, i.e, REGEX.Below is the REGEX expression to parse the CSV log files, which have been generated by PostgreSQL.Regex is ... [More] :--=-=-=-=-="^((([^,\"\n\r]*)|(\"([^\"]|(\"\"))*\")),){22}(\"([^\"]|(\"\"))*\"[\r\n]+)" Use any regex tools like RegexBuddy, which will give you detailed information about this regular expression. దినేష్ కుమార్Dinesh Kumar [Less]

I had developed this game a long ago, and would like to share with the world.Yes, ofcourse, we can optimize the code of c here, but i have concentrated only on output for this. Once, i got the desired result, i haven't looked into any of the line in ... [More] this code. {Very bad habbit, i need to over come this.}I hope you enjoy it, and correct if any problems occurs.This game is for only 2 players, which will give you the realtime game feel with your opponent.First find the code, and then instructions.C Program-=-=-=-=-=-#include<stdio.h>#include<ncurses.h>#include "/opt/PostgreSQL/9.0/include/libpq-fe.h"#include<stdlib.h>#include<string.h>char symbol[3];PGconn * PGconnect(char ch){PGconn *conn;PGresult *res;FILE *fp;int cnt,i=0;char conn_string[500],hostaddr[32],port[7],dbname[50],user[50],password[50],name[10];const char *paramValues[2];fp=fopen("/tmp/.cred","r");if(ch=='y'){fscanf(fp,"%[^:]s",hostaddr);fscanf(fp,":%[^:]s",port);fscanf(fp,":%[^:]s",dbname);fscanf(fp,":%[^:]s",user);fscanf(fp,":%s",password);sprintf(conn_string,"hostaddr=%s port=%s dbname=%s user=%s password=%s",hostaddr,port,dbname,user,password);}else{fscanf(fp,"%[^\n]s",conn_string);conn_string[0]='\0';fscanf(fp,"\n%[^:]s",hostaddr);fscanf(fp,":%[^:]s",port);fscanf(fp,":%[^:]s",dbname);fscanf(fp,":%[^:]s",user);fscanf(fp,":%s",password);sprintf(conn_string,"hostaddr=%s port=%s dbname=%s user=%s password=%s",hostaddr,port,dbname,user,password);}conn = PQconnectdb(conn_string);if (PQstatus(conn) == CONNECTION_BAD)        {                 fprintf(stderr,"Not able to connect to the database %s",PQerrorMessage(conn));                 return NULL;        }if(ch=='y'){res=PQexec(conn,"BEGIN");PQclear(res);res=PQexec(conn,"DECLARE find_taken CURSOR FOR select count(*) from public.pggame_control where taken=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(conn));                PQclear(res);        }PQclear(res);res = PQexec(conn, "FETCH ALL in find_taken");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(conn,"END");PQclear(res);if(cnt==0){res=PQexec(conn,"TRUNCATE public.pggame_control");PQclear(res);res=PQexec(conn,"INSERT INTO public.pggame_control values(false,false)");PQclear(res);}printf("Enter Your Name -> ");scanf("%s",name);paramValues[0]=name;printf("Enter Your Symbol -> ");scanf("%s",symbol);paramValues[1]=symbol;res=PQexec(conn,"TRUNCATE public.pggame_status");PQclear(res);res=PQexecParams(conn,"INSERT INTO public.pggame_status(name,symbol,comments,status) values($1,$2,'',false)",2,NULL,paramValues,NULL,NULL,1);                if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));                PQclear(res);                }                PQclear(res);}res=PQexec(conn,"TRUNCATE public.pggame_spots");PQclear(res);res=PQexec(conn,"TRUNCATE public.pggame_positions");PQclear(res);res=PQexec(conn,"INSERT INTO public.pggame_positions values(0,0)");PQclear(res);fclose(fp);return conn;}int main(){int y=1,x=1,i=1,one=0;char ch,x1[10],y1[10],cnt,remote_spot_check[200];PGconn     *your_conn,*other_conn;PGresult   *res;FILE *log;const char *paramValues[2];log=fopen("/tmp/pggame.log","w");if(!((your_conn=PGconnect('y'))&&(other_conn=PGconnect('o'))))return 1;initscr();keypad(stdscr,TRUE);noecho();x=1;y=1;while(x 10<140){x=x 2;mvprintw(0,x 9,"-=");mvprintw(36,x 9,"-=");y=1;}y=0;while(y<35){ y;mvprintw(y,12,".");mvprintw(y,141,".");}y=2;x=15;refresh();while(1){res=PQexec(your_conn,"BEGIN");PQclear(res);res=PQexec(your_conn,"DECLARE find_taken CURSOR FOR select count(*) from public.pggame_control where taken=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(your_conn, "FETCH ALL in find_taken");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(your_conn,"END");PQclear(res);if(cnt>=1){res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_spots CURSOR FOR select count(*) from public.pggame_spots");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_spots");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);if(cnt>=1){res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE get_remote_pos CURSOR FOR select * from public.pggame_spots where ctid=(select max(ctid) from public.pggame_spots)");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in get_remote_pos");y=atoi(PQgetvalue(res,0,0));x=atoi(PQgetvalue(res,0,1));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE get_symbol CURSOR FOR select symbol from public.pggame_status");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in get_symbol");mvprintw(y 1,x 15,"%s",PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);}res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_win CURSOR FOR select count(*) from public.pggame_status where status=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_win");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");if(cnt>=1){res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_win CURSOR FOR select name from public.pggame_status where status=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_win");mvprintw(15,40,"***** %s WIN The Game ***** .... Press Any Key To Exit ... ",PQgetvalue(res,0,0));refresh();PQclear(res);res=PQexec(other_conn,"END");getch();getch();endwin();PQfinish(your_conn);PQfinish(other_conn);return 0;}res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select count(*) from public.pggame_positions");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_table");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);if(cnt==1){res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select * from public.pggame_positions");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_table");y=atoi(PQgetvalue(res,0,0));x=atoi(PQgetvalue(res,0,1));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);mvprintw(37,125,"CONTROL ==>> ");mvprintw(y,x,"");}else{mvprintw(37,125,"CONTROL ==>> ");mvprintw(1,1,"");}refresh();usleep(10000);one=0;continue;}mvprintw(37,125,"CONTROL <<== ");mvprintw(y,x,"");refresh();if(cnt==0){res=PQexec(other_conn,"UPDATE public.pggame_control SET taken=true");  if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(stderr, "Update failed: %s", PQerrorMessage(other_conn));                PQclear(res);                }                PQclear(res);if(one==0){one=one 1;res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_spots CURSOR FOR select count(*) from public.pggame_spots");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_spots");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);if(cnt>=1){res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE get_remote_pos CURSOR FOR select * from public.pggame_spots where ctid=(select max(ctid) from public.pggame_spots)");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in get_remote_pos");y=atoi(PQgetvalue(res,0,0));x=atoi(PQgetvalue(res,0,1));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE get_symbol CURSOR FOR select symbol from public.pggame_status");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in get_symbol");mvprintw(y 1,x 15,"%s",PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);res=PQexec(other_conn,"BEGIN");PQclear(res);res=PQexec(other_conn,"DECLARE find_table CURSOR FOR select * from public.pggame_positions");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_table");y=atoi(PQgetvalue(res,0,0));x=atoi(PQgetvalue(res,0,1));PQclear(res);res=PQexec(other_conn,"END");PQclear(res);mvprintw(y,x,"");refresh();}}res=PQexec(your_conn,"BEGIN");PQclear(res);res=PQexec(your_conn,"DECLARE find_spotted CURSOR FOR select count(*) from public.pggame_control where spot=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(your_conn, "FETCH ALL in find_spotted");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(your_conn,"END");PQclear(res);if(cnt>=1){res=PQexec(your_conn,"UPDATE public.pggame_control SET taken=true where taken=false"); if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(stderr, "Update failed: %s", PQerrorMessage(your_conn));                PQclear(res);                }                PQclear(res);res=PQexec(other_conn,"UPDATE public.pggame_control SET taken=false where taken=true"); if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(stderr, "Update failed: %s", PQerrorMessage(other_conn));                PQclear(res);                }                PQclear(res);res=PQexec(your_conn,"UPDATE public.pggame_control set spot=false where spot=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(stderr, "Update failed: %s", PQerrorMessage(your_conn));                PQclear(res);                }                PQclear(res);}}ch=getch();if(ch=='q'||ch=='Q')break;switch(ch){case 'a':case 'A':if(x==15)x=137;elsex=x-2;mvprintw(y,x,"");break;case 's':case 'S':if(y==34)y=1;elsey=y 1;mvprintw(y,x,"");break;case 'w':case 'W':if(y==1)y=34;elsey=y-1;mvprintw(y,x,"");break;case 'd':case 'D':if(x==137)x=15;elsex=x 2;mvprintw(y,x,"");break;case 'j':case 'J':res=PQexec(other_conn,"BEGIN");PQclear(res);sprintf(remote_spot_check,"DECLARE find_same_spot CURSOR FOR select count(*) from public.pggame_spots where y=%d and x=%d",y-1,x-15);res=PQexec(other_conn,remote_spot_check);if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(other_conn));                PQclear(res);        }PQclear(res);res = PQexec(other_conn, "FETCH ALL in find_same_spot");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(other_conn,"END");if(cnt==0){echo();mvprintw(y,x,"%s",symbol);sprintf(y1,"%d",y-1);sprintf(x1,"%d",x-15);paramValues[0]=y1;paramValues[1]=x1;res=PQexecParams(your_conn,"INSERT INTO public.pggame_spots(y,x) values($1,$2)",2,NULL,paramValues,NULL,NULL,1);                if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(log, "INSERT failed: %s", PQerrorMessage(your_conn));                PQclear(res);                }                PQclear(res);noecho();refresh();}}sprintf(y1,"%d",y);sprintf(x1,"%d",x);paramValues[0]=y1;paramValues[1]=x1;res=PQexecParams(your_conn,"INSERT INTO public.pggame_positions(y,x) values($1,$2)",2,NULL,paramValues,NULL,NULL,1);  if (PQresultStatus(res) != PGRES_COMMAND_OK)                {                fprintf(log, "INSERT failed: %s", PQerrorMessage(your_conn));                PQclear(res);                }                PQclear(res);res=PQexec(your_conn,"BEGIN");PQclear(res);res=PQexec(your_conn,"DECLARE find_win CURSOR FOR select count(*) from public.pggame_status where status=true");if (PQresultStatus(res) != PGRES_COMMAND_OK)        {                fprintf(stderr, "Erorr %s", PQerrorMessage(your_conn));                PQclear(res);        }PQclear(res);res = PQexec(your_conn, "FETCH ALL in find_win");cnt=atoi(PQgetvalue(res,0,0));PQclear(res);res=PQexec(your_conn,"END");if(cnt>=1){mvprintw(15,40,"***** CONGRATULATIONS  !! ***** You WIN The Game .... Press Any Key To Exit ...");refresh();getch();getch();PQfinish(your_conn);PQfinish(other_conn);endwin();return 0;}}getch();PQfinish(your_conn);PQfinish(other_conn);endwin();return 0;} [Less]

Postgres 9.4 has added a new feature allowing to delay WAL replay on standbys or even to delay recovery by a given amount of time. It has been introduced by this commit. commit 36da3cfb457b77a55582f68208d815f11ac1399e Author: Simon Riggs Date: Thu ... [More] Dec 12 10:53:20 2013 +0000 　 Allow time delayed standbys and recovery 　 Set min_recovery_apply_delay to [...] [Less]

When doing trainings or consulting sessions I got the feeling that most people are not fully aware of PostgreSQL’s shutdown modes. However, knowing about this functionality is pretty important. Without this information sysadmis may cause unintended ... [More] damage and issues. This can easily be avoided. 3 shutdown modes When doing pg_ctl –help the system will reveal […] [Less]

Otto Bretz reported bug in OmniPITR. The bug was that when using -dr (remote destinations for backups) – you couldn't use –skip-xlogs. Obvious overlook on my side. Fix was trivial, and so 1.3.2 version was born.

One of the features new to PostgreSQL 9.3 is the COPY FROM PROGRAM sql construct and in psql the equivalent \copy from program. Michael Paquier covered the COPY TO/FROM PROGRAM in hist Postgres 9.3 feature highlight: COPY TO/FROM PROGRAM. Depesz ... [More] covered the companion psql construction in Support for piping copy to from an external program. Michael demonstrated an example using curl. I wanted to try something similar using wget since I have wget readily available on all my Linux and Unix boxes. For this example I'll demonstrate doing it on windows, but doing it on Linux is much the same and simpler since the wget and curl are generally already in Linux default path. Continue reading "Using wget directly from PostgreSQL using COPY FROM PROGRAM" [Less]

Database security is always an essential issue in any database application. Especially when critical data is stored, it might be interesting to know who has changed which data when and how. To track those changes made to tables in PostgreSQL you can write yourself a generic changelog trigger. The easiest way to do that is […]