Friday, April 22, 2011

PSM (zero) is completed, and needs you


yesterday I finished work on prototype implementation of SQL/PSM language for PostgreSQL. Now, this language supports all necessary features:

  • basic features - arrays, composite types, triggers
  • advanced features - table returned functions, IN/OUT variables
  • SQL/PSM specific features - warning, exceptions (general, SQLCODE) handlers, SIGNAL, RESIGNAL statements
  • some DB2 or MySQL features - multi assign statement, support for magic SQLSTATE and SQLCODE variables
some samples:
create or replace function test74_2()
returns text as $$
begin atomic
  declare not_found condition for sqlstate '03000';
  declare undo handler for not_found
      declare xx, yy text;
      get stacked diagnostics xx = condition_identifier, yy = returned_sqlstate;
      return xx || ' Signal handled ' || yy;
  signal not_found;
$$ language psm0;

create or replace function test66(a int, out r int) as $$
  declare continue handler for sqlstate '01002'
    set r = r + 1;
  declare continue handler for sqlstate '01003'
    set r = r + 2;
  set r = 0;
x: while a > 0 do
     if a % 2 = 0 then
       signal sqlstate '01002';
       signal sqlstate '01003';
     end if;
     set a = a - 1;
   end while;
$$ language psm0;

This PL isn't designed as an replacement of PL/pgSQL. It is designed as second language with little bit different philosophy and goals.

  • complete validation of embedded SQL in compile time,
  • result of embedded SQL is transformed to target type early
The main goal is a searching bugs inside embeded SQL early - usually in compile time. This goal changes a rules in languages. PSM is very static language. There are more tasks, that should be solved via dynamic SQL than in PL/pgSQL. On second hand, lot of PL/pgSQL runtime bugs can be detected in PSM in compile time.

  • Complete revision and review, more comments - any volunteers ?
  • Performance optimizations,
  • Cleaning of error messages

Source code is available from github.
I invite any help!


At April 22, 2011 at 6:26 AM , Blogger Simon Riggs said...

Very pleased to see this. Well done.

I'm sure many people would love to help, but before they do, we need to clarify the Copyright, Licence and contributions policy. I can't find any mention of licence on any of the files, which makes me a little nervous. Right now, I wouldn't advise others to use it because of this.

Can you clarify those things please?

At April 22, 2011 at 6:40 AM , Blogger Pavel Stěhule said...

[Simon] - It will be released under BSD licence - the final decision should do my employer - but I don't expect any problems. CZ.NIC supports open source projects and it is a PostgreSQL user - Czech Domain Registry uses PostgreSQL as database backend.

It's designed for integration to core,I hope in 9.2 or 9.3 - there is lot of work and it need a relative significant enhancing of SPI api (for clean implementation). Code from git working with current GIT Postgres.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home