Docs (software)

Compile your own C extension for Postgres on Debian

Submitted by ivan on Fri, 02/12/2010 - 09:50.

You'd like to try to write your first "Hello world" as a C extension to Postgres, you're a bit impatient and you don't want to pollute your system with garbage.

  1. aptitude install postgresql-server-dev-YOURVERSION
  2. aptitude install [all the C stuff you need including gcc]
  3. Optional but very useful to learn and to easily find some files you'll need
    apt-get source postgresql-YOURVERSION
  4. mkdir ~/mypgext
    cp PGSOURCEDIR/contrib/someprj/Makefile ~/mypgext
  5. Edit your Makefile. You'd find some info here: Extension Building Infrastructure
  6. Write your C code. You'd be able to find some more info on how to write functions in this site.
  7. export USE_PGXS=1
    make clean
    make

    make clean is needed if you're compiling for several pg versions on a shared folder
  8. modify $MODULE_big.sql to point it at the right .so
    If you're not going to use make install because it picks up installation folder from .configure and you're not willing to build up a ful dev environment, you'd have to point the SQL to the correct .so by hand.
    I came out with this simple script to automate things:
    #!/bin/bash

    export USE_PGXS=1
    make clean
    make
    MODULE_big=$(sed -ne '/MODULE_big/s/^MODULE_big[ \t]*=[ \t]*\([^ \t]*\)/\1/gp' Makefile)
    so=$(ls -1 *"$MODULE_big"*.so)
    sed -e 's#\$libdir[^'"'"']*#'`pwd -P`'/'$so'#g' $MODULE_big.sql > $MODULE_big.l.sql
    sed -e 's#\$libdir[^'"'"']*#'`pwd -P`'/'$so'#g' uninstall_$MODULE_big.sql > uninstall_$MODULE_big.l.sql
    psql test < $MODULE_big.l.sql
  9. You'd launch some test directly from your build script or just test your newly built function from psql

Be warned that writing C extension may crash and burn your Postgres DB

Excluding some tables data from backup but including their schema

Submitted by ivan on Tue, 12/29/2009 - 07:40.

Backup problem

You've some tables that are used for cache or they just contain redundant data that doesn't have to be backup and this cache is large and:

  • backing up and restoring will just take longer
  • you'll have to store much larger files

You'd like to backup everything, with custom format and compression, making a nearly full backup, except the data in those table, but still including the schema of those table and you'd like to safely and quickly restore your backup.

Unfortunately there is no --exclude-table-data option neither --schema-only-table but pg_dump and pg_restore can still handle this in a reasonably clean way.

Backup and restore method

pg_dump -Fc -Z9 -s -t [tables pattern] -d mydb > schema_only.bak
pg_dump -Fc -Z9 -T [tables pattern] -d mydb > nearly_full.bak

and than this will work:
pg_restore -1 -d mydb < nearly_full.bak
pg_restore -1 -d mydb < schema_only.bak

Caveat

In Postgresql 8.3.8 you can't concatenate 2 backups in custom format.

cat nearly_full.bak schema_only.bak | pg_restore -1 -d mydb

silently fail to restore the second backup (schema_only.bak). That means you can't actually restore your full backup in one transaction.

There is no warranty that your DB will continue to be coherent and respect all it's constraint once you exclude some data.
There is no warranty as well that your application will correctly work if you saved some status about the cache in the slice you're backing up, and then restore without the cache. But if you cut the slice in the right place, you'll restore a coherent DB.

HOWTO: Duplicating schema in Postgresql

Submitted by ivan on Tue, 12/01/2009 - 09:06.

Unfortunately you can't set a target schema during a restore, you can just filter a schema

Renaming a schema does seem to be a pretty fast operation

Backup, rename elsewhere, restore

This could be the paranoid approach, at the cost of running a 2nd DB and maybe transfer a lot of data you may reduce the operations inside a transaction on the production DB and even test if you can safely load the new schema

  1. pg_dump -d mydb -Fc -Z9 > mydb.bak
  2. createdb mydb_transfer [other options]
  3. pg_restore -d mydb_transfer < mydb.bak
  4. psql -d mydb_transfer -c 'alter schema XXX rename to YYY;'
  5. pg_dump -d mydb_transfer -Fc --schema=YYY > mydb-YYY.bak
  6. psql -d mydb -c 'create schema YYY;'
  7. pg_restore -1 -dmydb < mydb-YYY.bak ; # single transaction

In the first step you could just backup the schema you want to rename... but I think having a full backup before such kind of change could come handy.

Backup, rename the original schema, reload

This is not for the faint-of-heart, but it saves some operations and time.

  1. pg_dump -d mydb -Fp --schema=XXX > mydb.bak
  2. (echo 'begin;'; echo 'alter schema XXX rename to YYY'; echo 'create schema XXX' authorization AAA; pg_restore --schema=XXX < mydb.bak; echo 'commit;') > psql -d mydb

Variations and caveat

According to your needs and mood you can make some variations on the above methods eg. backing up just the schema vs. the whole DB, using/not using compression...
One thing you may consider is to set the owner of the new/old schema.

Script

here you can find a simple script that will automate everything. Use it at your own risk.

»

pseudo-random sequences in postgresql

Submitted by ivan on Fri, 08/21/2009 - 14:27.

Sometimes you need cheap unique identifiers that aren't that easy to guess

So pick up any pseudo-random sequence generator, wrap it around a sequence, assign it as the default value of a column, add some beautifier

I picked up and adapted a feistel cipher suggested by Daniel Verite

create or replace function feistel_encrypt(value int)
      returns int as
      $$
      declare
        l1 int;
        l2 int;
        r1 int;
        r2 int;
        i int:=0;
      begin
        l1:= (value >> 16) & 65535;
        r1:= value & 65535;
        while i<3 loop
          l2:=r1;
          r2:=l1#((((1366.0*r1+150889)%714025)/714025.0)*32767)::int;
          l1:=l2;
          r1:=r2;
          i:=i+1;
        end loop;
        return ((l1 << 16) | r1);
      end;
$$ language plpgsql strict immutable;
create or replace function feistel_decrypt(value int)
      returns int as
      $$
      declare
        l1 int;
        l2 int;
        r1 int;
        r2 int;
        i int:=0;
      begin
        l2:= (value >> 16) & 65535;
        r2:= value & 65535;
        while i<3 loop
          r1=l2;
          l1:=r2#((((1366.0*l2+150889)%714025)/714025.0)*32767)::int;
          l2:=l1;
          r2:=r1;
          i:=i+1;
        end loop;
        return ((l2 << 16) | r2);
      end;
      $$ language plpgsql strict immutable;

It looks as it is working

select * from feistel_decrypt(feistel_encrypt((2^31-1)::int))
union
select * from feistel_decrypt(feistel_encrypt((-2^31)::int))
union
select * from feistel_decrypt(feistel_encrypt((0)::int))
union
select * from feistel_decrypt(feistel_encrypt((-1)::int))
;

To really make it works like a sequence, create a sequence and assign the column a default value.

create table pr(
  code varchar(16) primary key,
  ...
);
create sequence pr_code_seq owned by pr.code;
alter table pr
  alter column code
    set default
    to_hex(feistel_encrypt(nextval('pr_code_seq')));

It is curious you're assigning an int sequence to a varchar column... but actually it is comfortable and it works and will drop the sequence if the column or table are dropped as expected, no matter the type mismatch.

Variation may be to add some extra noise padding the sequence with some random character

alter table pr
  alter column code
    set default
      rpad(
       to_hex(feistel_encrypt(nextval('pr_code_seq'))) || '-',
       8,
       to_hex((rand()*1000000)::int));

Checking if temporary table exists in PostgreSQL

Submitted by ivan on Tue, 07/21/2009 - 09:31.

When you'll search on google you'll find 2 ways to check if a temp table exists: using EXCEPTION and reading information_schema.

begin;
  select 1 from _yourtable_;
  -- create temp table...
  exception when
    -- do nothing
end;

The EXCEPTION method has 3 drawback:

  • You may have created a permanent table with the same name as the temp table
  • You've to catch the correct exception and depending on what you're doing (just checking if the table exist or creating a new one) you may have to change the EXCEPTION trapping
  • exception codes may change (becoming more fine-grained...)

To get a clearer picture of what's going on you may try this:

begin;
create or replace function tt_test() returns void as
$$
declare
  sch varchar(128);
begin
  create temp table pippo (i int);
  select into sch table_schema from information_schema.tables
  where
  table_name='pippo' and
  table_type='LOCAL TEMPORARY';

  execute 'create table ' || sch || '.pippo (i int);'; -- FAIL
  execute 'create schema ' || sch || ';'; -- NOT TESTED
  create table pippo (i int);  -- SUCCEDE

  create temp table zzz as
    select * from information_schema.tables
    where
    table_name='pippo';

  return;
end;
$$ language plpgsql;
select * from tt_test();
commit;
select * from zzz limit 10;

So a better way could be this:
if exists (select 1 from information_schema.tables where table_name='_yourtable_' and table_type='LOCAL TEMPORARY'

Microsoft Excel ODBC and arbitrary queries

Submitted by ivan on Tue, 05/26/2009 - 12:14.

The problem

You would like to get the result of an arbitrary query in an Access table/Excel sheet but Excel continue to complain it can't find the relation or you can't use parameter

The solution

Build up a "mockup" query

You should have already build an ODBC connection.

  • Data -> Import External Data -> New Database Query
  • Choose the odbc connection you created for Postgresql
  • Don't add any query.
  • Edit directly "SQL"
  • save an easily "greppable" query (eg. select 'ermenegildo';)
  • Ignore complaint.
  • Close Microsoft Query.
  • Say OK to "Import data" (just select a good "top left corner for your table)

Trick Excel and give it the "right" query

This is what happens when the tool thinks it is smarter than you.

  • Tools -> Macro -> Visual Basic Script Editor
  • search your query and change it with any valid SQL, add ? if you need parameters taken from cells
  • save
  • you'll be asked which cell contain the parameter (I didn't try to use more than one parameter)

Now you can have an arbitrary query returned in an Excel sheet.

Claws Mail keyboard weirednes on Debian sid

Submitted by ivan on Fri, 05/08/2009 - 11:10.

You upgraded claws-mail and suddenly your ' (apostrophe) behave like a backspace, undo or ^W...
And it is REALLY annoying since ' is a frequently used character and since it delete stuff... you keep on rewriting things and you can't undo the delete.

The reason is that for some strange destiny some curious keyboard shortcut ended up in your ~/.claws-mail/menurc and the apostrophe got assigned to Undo.

I don't know if it is a code bug (key shouldn't be intercepted in that context) or a configuration bug, but I just commented out nearly all shortcut there.

Postgresql panic: recovery not working and DB not restarting

Submitted by ivan on Wed, 04/08/2009 - 21:25.

Recovering from corrupted WAL in postgresql

It happened to me that due to still unknown problems postgresql freezed and then during restart it was not able anymore to run (segmentation fault).
It could be that the corrupted WAL was triggering some problem in postgresql

OK... the tool to save you is pg_resetxlog.
This command is not in the PATH of Debian. In etch, lenny and sid at the moment of writing you can find it in

/usr/lib/postgresql/8.3/bin/pg_resetxlog
»

Shrinking Windows virtualbox images on Linux: how to make defrag work

Submitted by ivan on Sun, 03/29/2009 - 20:15.

Defrag alone is not going to work, actually it will make the image larger because it will access "larger" portions of the virtual disk. Here are the few steps you've to follow to actually shrink the images

»

Debian wake on lan

Submitted by ivan on Wed, 01/28/2009 - 19:08. 

aptitude install wakeonlan

aptitude install ethtool

edit interfaces

up ethtool -s eth1 wol g #sopass 10:10:10:10:10:10

etherwake support pass but just work as root

12345next ›last »