MS SQL to PostgreSQL

gpg primer

2008-08-26T16:18:00.005+08:00

miblogic@gmail.com procedure:

gpg --gen-key
gpg --list-keys

// export public key, --armor makes the key file ascii readable
gpg --armor --export miblogic@gmail.com > miblogic.gmail.public.key
// just the same
gpg -a --export miblogic@gmail.com > miblogic.gmail.public.key

// export private key
gpg --armor --export-secret-key miblogic@gmail.com > miblogic.gmail.private.key

// import the public key of miblogic@yahoo.com
gpg --import miblogic.yahoo.public.key

// gpg a piped output:
pg_dump -i -h 127.0.0.1 -p 5432 -U postgres -F c -v Cashflow | gpg --recipient miblogic@yahoo.com --encrypt --output Cashflow.backup.gpg

// gpg a file:
gpg --recipient miblogic@yahoo.com ---output file.txt.encrypted --encrypt file.txt

// miblogic@yahoo.com decrypting a message from miblogic@gmail.com
gpg --recipient miblogic@yahoo.com --output the_file.txt --decrypt file.txt.encrypted

my first time to win computer chess (GNU Chess)

2008-07-14T15:04:00.004+08:00

killing time from our Linux server this afternoon after i ate lunch :-D

:-) my first time to win again against computer, also my first time to play GNU Chess (using normal settings) next time i'll try the difficult level

simple rant: a CRUD day

2008-07-03T14:10:00.006+08:00

i don't get all these trend about using stored procs for CRUD. i'm not a web app programmer, i mostly develop desktop apps. how to code stored procs for header and detail tables (textboxes above and datagridview below) ?

i would use stored procs for reports. but for desktop app CRUD routines? 怎么办 (how to do?)

using stored procs for simple database chores such as CRUD have an architecture astronaut ring to me. that's my impression of stored proc'd CRUDs

if there's a way to pass the delta of the dataset (header and detail tables) directly to just one stored proc (create procedure invoice_save @invoice_rec_id varchar, @invoice_header table, @invoice_detail table), i might use SP for CRUD

i learned that ms sql 2005 allow passing XML(passing list of rows is now allowed) to stored procs, so passing of header and detail to SP is now allowed, but then again, another problem persists, for datagrid's changes, does mssql 2005 accepts datatable's delta (datagrid's changes) through XML?

is enforcing business logic on C# middletier not elegant? Remoting and Webservices can also act as stored proc for rdbms(es) which don't have stored proc. it's also centrally managed, no need to re-distribute the business logic changes to other apps (desktop or otherwise). and also, these middle-tier stuffs are more amenable to horizontal scaling

in this age of B2B, do you think enterprise communicate to each other directly via stored procs? web services, ajax, remoting, etc also have these "centrally-managed" aspect in it

with all these great facilitators (dcom, rds, java rmi, .net web services, .net remoting) of centrally-managed code, would you still use stored procs?

why app programmers of the world, why do you code your CRUD on stored proc? do middletiers doesn't cut it?

P.S.
i will not convert my datagridview to to textboxes, users find datagrid editing very intuitive. would you like to earn the ire of my programs' users? :-)

sql code readability

2008-04-24T10:43:00.003+08:00

though logically the same, this is easier to read:

select coalesce(
(select CURRENT_TIMESTAMP > unlocked_expiration
from invoice
where invoice_rec_id = 'value here'), true) as has_expired

than this:

select coalesce(
(select unlocked_expiration < CURRENT_TIMESTAMP
from invoice
where invoice_rec_id = 'value here'), true) as has_expired

NULL should be renamed to UNKNOWN

2008-03-28T23:37:00.008+08:00

problem:

the following has no output when it should has:

select * from src where n not in (select n from dst);

reproduce the problem:

create table src (n int);

create table dst(n int);

insert into src(n) values(1),(2),(3),(4),(5);
insert into dst(n) values(1),(2),(3);
select * from src where n not in (select n from dst); -- lists 4, 5

insert into dst(n) values(null);
select * from src where n not in (select n from dst); -- no output

should list 4 and 5?

it isn't, it didn't list anything

hmm.. seems strange

explanation:

SELECT 'SEE' WHERE 7 IN (SELECT 2 UNION SELECT 4)
is a shorthand for:
SELECT 'SEE' WHERE 7 = 2 OR 7 = 4

SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4

now let's inject NULL data :

SELECT 'SEE' WHERE 7 IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 = 2 OR 7 = 4 or 7 = NULL

SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4 AND 7 <> NULL

you cannot compare NULL, null means unknown. by the virtue of it being unknown, the expression 7 <> NULL is neither true nor false. the expression 7 = NULL, is also neither true nor false. 7 <> NULL: NULL, 7 = NULL: NULL.

100 <> 500 results true
100 = 500 results false
100 <> NULL results NULL
100 = NULL results NULL
100 IS NULL results false
100 IS NOT NULL results true
NULL = NULL results NULL
NULL <> NULL results NULL

NULL AND NULL results NULL
NULL AND TRUE results NULL
TRUE AND NULL results NULL
TRUE AND TRUE results TRUE

NULL OR NULL results NULL
NULL OR TRUE results TRUE
TRUE OR NULL results TRUE
TRUE OR TRUE results TRUE

NULL represents unknown, cannot be determined, not yet determined

rough analogy, think of WHERE A <> B as left hand <> right hand

WHERE 100 <> NULL

in plain english: is the left hand holding 100 dollar bill different with the bill in the closed right hand?

so what's the answer for 100 <> NULL?

let's have an example, your friend approach you, his/her left hand is open, you can see 100 bucks, right hand is close, you can't see anything. then he/she asks you if right hand holds different bill than the left hand, what would you(or any human) would answer? a human would answer, "it's different", "not different", or could answer "i don't know"

while on a computer, when you ask it, the computer's answer is always "i don't know." there is no gratification for a computer to guess correctly. perhaps, it's human nature to try to predict unknown forces, like trying to gauge if himself is capable of ESP, of capable of correctly predicting things 9 out of 10, or inclined in believing in divination(god will help me correctly guess so i can win this Deal or No Deal). humans are constantly feeding on gratifications, be it a guessing game, or trying their "pure luck"(believing in divination sometimes plays a large role here). so much for digressing :-)

so there we go, computers can't guess, it always answer "i don't know" when faced with unknown.

so the answer for the expression WHERE 100 <> NULL is unknown. neither true nor false. 100 = NULL is also unknown, neither true nor false. perhaps, RDBMS vendors should rename NULL to UNKNOWN.

let's try replacing NULL with UNKNOWN

100 <> 500 results true
100 = 500 results false
100 <> UNKNOWN results UNKNOWN
100 = UNKNOWN results UNKNOWN
100 IS UNKNOWN results false
100 IS NOT UNKNOWN results true
UNKNOWN = UNKNOWN results UNKNOWN
UNKNOWN <> UNKNOWN results UNKNOWN

UNKNOWN AND UNKNOWN results UNKNOWN
UNKNOWN AND TRUE results UNKNOWN
TRUE AND UNKNOWN results UNKNOWN
TRUE AND TRUE results TRUE

UNKNOWN OR UNKNOWN results UNKNOWN
UNKNOWN OR TRUE results TRUE
TRUE OR UNKNOWN results TRUE
TRUE OR TRUE results TRUE

there it is, more intuitive, perhaps when reading NULL, mentally read them as UNKNOWN.

these would not return a row:
SELECT * FROM employee WHERE FALSE
SELECT * FROM employee WHERE NULL

this could:
SELECT * FROM employe WHERE TRUE

let's go back on NOT IN construct:

SELECT 'SEE' WHERE 7 NOT IN (SELECT 2 UNION SELECT 4 UNION SELECT NULL)
is a shorthand for:
SELECT 'SEE' WHERE 7 <> 2 AND 7 <> 4 AND 7 <> NULL

resolved to:
SELECT 'SEE' WHERE TRUE AND TRUE AND NULL

then resolved to:
SELECT 'SEE' WHERE NULL

final result: no output

perhaps the rationale for using the word NULL is because it sounds so computer-sciencey. as for not using UNKNOWN as an identifier for er.. unknown, heck, it's hard to read double negatives, NOT UNKNOWN, oh.. humour me :-)

but... all these arguments can be thrown out of the window when mainstream programming languages like c# go the extra mile to make null comparisons not much to fuzz about:

int? a = 7;

int? b = null;

if (a != null) Console.WriteLine("indeed");

if (a != b) Console.WriteLine("still indeed");

programmer's life is sometimes difficult, impedance mismatches on our array of tools are in your face. our tools sometimes deviates from what we expected and asserted them to be. nonetheless we're constantly humbly learning to deal and get over with it.

which led me to the conclusion that i shouldn't have written these IS NULL, IS NOT NULL stuffs in the first place :-)

// Buen

Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer.
- Fred Brooks, Jr.

P.S.

i'd go on a limb here to generalize VB.NET's inconsistency(for the nth time), it's the MySQL of imperative programming languages. null handling in vb.net is basically broken. but perhaps one of VB.NET's few redeeming factor is its block-structured constructs, e.g. If-EndIf, Select-EndSelect, For-Next, Do-Loop, etc, they are very programmer-friendly, i wish C# use those block delineators instead of curly brackets

string s = null;
if (s == "") MessageBox.Show("Not Good"); else MessageBox.Show("Good");
// outputs Good

Dim s As String
s = Nothing
If s = "" Then
MessageBox.Show("Broken")
Else
MessageBox.Show("Expect Nothing")
End If
' outputs Broken

How to detect if field's value changes in UPDATE trigger

2007-12-24T09:31:00.000+08:00

use this approach if your field is NOT NULLable:

if NEW.field <> OLD.field then
raise notice 'something changes';
end if;

use this approach if your field is NULLable: # is the XOR of Postgres

false xor false = false
false xor true = true
true xor false = true
true xor true = false

if

( (NEW.field is null)::int # (OLD.field is null)::int ) = 1

OR

NEW.field <> OLD.field

then

raise notice 'something changes';

end if;

Calculate Age or Birthday

2007-12-22T19:42:00.000+08:00

CREATE or replace FUNCTION CalculateAge(m_birthday date, m_datetoday date)
RETURNS INTEGER
AS
$$
BEGIN

RETURN

(
select
(
CASE
WHEN dateadd('y', datediff ('y', m_birthday, m_datetoday), m_birthday) > m_datetoday
THEN datediff ('y', m_birthday, m_datetoday) - 1
ELSE datediff ('y', m_birthday, m_datetoday)
END)

);

END;
$$ language 'plpgsql';

-- see my previous post on dateadd and datediff

gapless sequence without using extra table, however, locks the whole table

2007-12-22T19:02:00.000+08:00

CREATE OR REPLACE FUNCTION get_tryrecord_lastid()
RETURNS int AS
$$
begin
lock table tryrecord;
return coalesce( (select tryrecord_id from tryrecord order by tryrecord_id desc limit 1) + 1, 1 );
end;
$$
LANGUAGE 'plpgsql' ;

CREATE TABLE tryrecord
(
tryrecord_id integer NOT NULL DEFAULT get_tryrecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_tryrecord PRIMARY KEY (tryrecord_id)
);

begin;
insert into tryrecord(name) values('see');
insert into tryrecord(name) values('great');
end;

gapless sequence without locking the whole table

2007-12-22T18:55:00.000+08:00

create table therecord_lastid
(
last_id int default 0,
constraint pk_therecord_lastid primary key (last_id)
);
insert into therecord_lastid values(0);

CREATE OR REPLACE FUNCTION get_therecord_lastid()
RETURNS int AS
$$
declare
x int;
begin
update therecord_lastid set last_id = last_id + 1;
select last_id into x from therecord_lastid;
return x;
end;
$$
LANGUAGE 'plpgsql' ;

CREATE TABLE testrecord
(
testrecord_id integer NOT NULL DEFAULT get_therecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_testrecord PRIMARY KEY (testrecord_id)
);

begin;
insert into testrecord(name) values('see');
insert into testrecord(name) values('great');
end;

select * from testrecord

if you have many foreign keys, how to check that only one of them must not be null

2007-12-22T11:48:00.000+08:00

create table shipment
(
int seq serial bigint not null

client_code,
supplier_code,
testing_company_code,
branch_code,

date_to_ship date,

constraint fk_shipment__client foreign key(client_code)
references client(client_code),

constraint fk_shipment__supplier foreign key(supplier_code)
references supplier(supplier_code),

constraint fk_shipment__testing_company foreign key(testing_company_code)
references testing_company(testing_company_code),

constraint fk_shipment__branch foreign key(branch_code)
references branch(branch_code),

constraint ck_shipment__company check
(
(client_code is not null)::int
+
(supplier_code is not null)::int
+
(testing_company_code is not null)::int
+
(branch_code is not null)::int
) = 1

);

Note: if you only need to test at least one of them is not null, just change = 1 to >= 1.

or better yet, do this:

constraint ck_shipment__company

check(coalesce(client_code, supplier_code, testing_company_code, branch_code) is not null)

Postgres is faster on Windows XP (on AMD only)

2007-12-22T11:34:00.000+08:00

(an e-mail i wrote to my peers last july 23, guess it's better to post this on blog. these are all out-of-the-box installation)

this is my latest benchmarkings from fresh installation i performed last saturday, i use the same drive on all installation. Western Digital 160 GB.

Core 2 Duo E6700 2.67 GHz vs AMD64 3400+ 2.0 GHz

select * from technicalsheet_color order by techsheetid, recid limit 100:

c2d 2.67 GHz + ubuntu64 + postgresql = 6 seconds
amd64 2.0 GHz + ubuntu64 + postgresql = 9 seconds

c2d 2.67 GHz + winxp32 + postgresql = 12 seconds
amd64 2.0 GHz + winxp32 + postgresql = 8 seconds

as you can gloss from the results, the speed differences are not symmetrical, it is hard to draw one-sweeping-generalization™ conclusion ;-)

PostgreSQL on laptops doesn't bodes well for Windows XP users, because most of laptops are Core 2 Duo than AMD64/Turion. PostgreSQL runs two times faster on Core 2 Duo if you only use Linux.

upsides:
* PostgreSQL on Ubuntu64 is faster on Core 2 Duo
* PostgreSQL on WinXP32 is faster on AMD64

downsides:
* PostgreSQL on WinXP32 slow down miserably on Core 2 Duo
* on AMD64, PostgreSQL is only marginally faster on WinXP32 than its Ubuntu64 counterpart

it is interesting to note that Dave Cutler (Windows NT,2000,XP,Vista,2003 chief engineer) have work closely/co-developed AMD64 with AMD, this could explain why Windows runs marginally better on AMD64 than Core 2 Duo.
and also, maybe AMD64 or Athlon X2 runs better than Core 2 Duo, i haven't yet pitted AMD's 2.6 GHz against Core 2 Duo 2.67 GHz :-)

on Core 2 Duo, WinXP to Ubuntu gains two-fold speed increase, from 12 seconds to 6 seconds. at first, i thought AMD64 WinXP to Ubuntu would gain the same twofold speed increase (8 to 4 seconds), but as i benchmarked it, it wasn't.
AMD64 seems was designed to run Windows well, Microsoft's Dave Cutler and AMD collaboration at its best :-)

just draw your own conclusions :-)

i'll just choose the best combo of processor + os + database

p.s.

these are the results without the ordering:

select * from technicalsheet_color limit 100:

c2d 2.67 GHz + ubuntu64 + postgresql = 11 seconds
amd64 2.0 GHz + ubuntu64 + postgresql = 16 seconds

c2d 2.67 GHz + winxp32 + postgresql = 19 seconds
amd64 2.0 GHz + winxp32 + postgresql = 13 seconds

get the last balance of each account. select the last row of each group

2007-12-20T15:42:00.000+08:00

get the last balance of each account

MS SQL-way:

select * from
accountinstallmentfinancingdetailfinal detail

inner join (select invoiceno, pmtstructureno, max (paymentsortorder) as lastrow
group by invoiceno, pmtstructureno) as lastrowofaccount

on lastrowofaccount.invoiceno = detail.invoiceno and lastrowofaccount.pmtstructureno = detail.pmtstructureno and lastrowofaccount.lastrow = detail.paymentsortorder;

PostgreSQL way (note: the above would also work on PostgreSQL):

select distinct on (invoiceno, pmtstructureno) *
from accountinstallmentfinancingdetailfinal
order by invoiceno, pmtstructureno, paymentsortorder desc;

PostgreSQL GROUP_CONCAT

2007-12-19T23:10:00.000+08:00

CREATE TABLE produk
(
seq_id serial NOT NULL,
name character varying(100) NOT NULL
) ;

INSERT INTO produk (seq_id, name) VALUES (1, 'beer');
INSERT INTO produk (seq_id, name) VALUES (2, 'in');
INSERT INTO produk (seq_id, name) VALUES (3, 'beer');
INSERT INTO produk (seq_id, name) VALUES (4, 'can');
INSERT INTO produk (seq_id, name) VALUES (6, 'goods');
INSERT INTO produk (seq_id, name) VALUES (7, 'goods');
INSERT INTO produk (seq_id, name) VALUES (8, 'can');
INSERT INTO produk (seq_id, name) VALUES (5, 'goods');

name | id_of_duplicates
----------+------------------
beer | 1,3
can | 4,8
goods | 5,6,7
in | 2

MySQL:

select name, group_concat(cast(seq_id as char)) as id_of_duplicates
from produk
group by name
order by name;

PostgreSQL:

create aggregate array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);

first approach:

select name, array_accum(seq_id)
from produk
group by name

select name, array_to_string(array_accum(seq_id), ',')
from produk
group by name;

second approach (mysql-compatible approach):

select name, group_concat(seq_id) as id_of_duplicates
from produk
group by name
order by name;

MySQL GROUP_CONCAT with ordering:

select name, group_concat(cast(seq_id as char) order by seq_id) as id_of_duplicates
from produk
group by name
order by name;

PostgreSQL equivalent:

select name, group_concat(distinct seq_id) as id_of_duplicates
from produk
group by name
order by name;

using customized sort:

select name, group_concat(seq_id) as id_of_duplicates
from
(
select name, seq_id
from produk
order by name, seq_id
) as x
group by name

PostgreSQL is a lot more nicer, it allows you to define your own aggregate function. GROUP_CONCAT is not built-in, with user-defined aggregate, you can create one.

return the number of digits after the decimal point

2007-12-19T19:32:00.000+08:00

select greatest(length(abs(x - trunc(x))) - 2, 0) from (values (12345.12345::numeric), (1234.1234::numeric), (1234.123::numeric), (-1234.123::numeric), (1::numeric), (10::numeric)) as t(x);

select length(regexp_replace(x::numeric::text,'^.*\\.',''))
from
(values (12345.12345::numeric), (1234.1234::numeric), (-1234.123::numeric)) as t(x);

updates in PostgreSQL, MS SQL, MySQL

2007-12-19T01:33:00.000+08:00

PostgreSQL: OK, MS SQL: OK (just change the "||" to "+"), MySQL: NOT OK

/* very orthogonal, since only one table allowed to be updated, no need to "set product.name = ", "set name = " will do. in fact if you do: "set product.name = ", postgres will disallow it */

update product set name = product.name || ':' || prod.name
from prod
where prod.seq_id = product.seq_id

**********************************

PostgreSQL: OK (good for re-using existing query, must alias the source table if the source table conflicts with to-be-updated-table name), MS SQL: NOT OK, MySQL: NOT OK

update product set name = product.name || ':' || prod.name
from product x
inner join prod on prod.seq_id = x.seq_id
where x.seq_id = product.seq_id /* don't forget to filter the to-be-updated-table to queried rows */

**********************************

PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK

/* note: even only one possible table can be updated, requires the fully-qualified name for the fields to be set. i.e. set product.name */

update product,prod set product.name = concat(product.name, ':', prod.name)
where prod.seq_id = product.seq_id;

**********************************

PostgreSQL: NOT OK, MS SQL: OK (good for re-using existing query), MySQL: NOT OK

update product set name = product.name + ':' + prod.name
from product /* limitation: cannot alias this, must be same name as the table to-be-updated */
inner join prod on prod.seq_id = product.seq_id

***********************************

PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK

update product join prod on prod.seq_id = product.seq_id

set product.name = concat(product.name, ':', prod.name);

/* nice update syntax, not ambiguous as MS SQL. similar to MS Access update join syntax, though not as good/flexible as PostgreSQL, wherein you can just drop-in your previous query to the update statement */

delete duplicate rows (and leaving one copy) in PostgreSQL, MS SQL, MySQL

2007-12-19T00:57:00.001+08:00

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

delete from product where
seq_id not in (select min(seq_id) from product group by name)

to make this work with MySQL:

delete from product where
seq_id not in (select x from (select min(seq_id) as x from product group by name))

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

delete from product where
exists(select * from product x where x.name = product.name and product.seq_id > x.seq_id)

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

/* advantage: can re-use existing query's logic for finding the first record of each duplicate */

delete from product where
exists(
select x.name, min(x.seq_id)
from product x
where x.name = product.name
group by x.name
having product.seq_id > min(x.seq_id)
);

MS SQL: OK, PostgreSQL: NOT OK, MySQL: OK

delete product from product
inner join product b
on b.name = product.name and product.seq_id > b.seq_id;

MS SQL: OK, PostgreSQL: OK, MySQL: NOT OK

delete from product where
seq_id in
(select a.seq_id from product a inner join product b on a.name = b.name and a.seq_id > b.seq_id);

to make this work with MySQL:

delete from product where
seq_id in
(select x from (select a.seq_id as x from product a inner join product b on a.name = b.name and a.seq_id > b.seq_id));

/* MySQL is pretty lame in this last case */

PostgreSQL DATEDIFF

2007-12-08T14:13:00.000+08:00

version 8.0 and higher

CREATE OR REPLACE FUNCTION datediff(p_Interval "varchar", p_DateFrom date, p_DateTo date)
RETURNS int4 AS
$BODY$

BEGIN
if p_Interval = 'm' then
return ((date_part('y',p_DateTo) * 12) + date_part('month',p_DateTo))
- ((date_part('y',p_DateFrom) * 12) + date_part('month',p_DateFrom));
elseif p_Interval = 'y' then
return date_part('y',p_DateTo) - date_part('y',p_DateFrom);
else
raise exception 'Datediff: Interval not supported';
return 0;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

before version 8.0

CREATE OR REPLACE FUNCTION datediff("varchar", date, date)
RETURNS int4 AS
$BODY$

DECLARE
p_Interval ALIAS FOR $1;
p_DateFrom ALIAS FOR $2;
p_DateTo ALIAS FOR $3;

BEGIN
if p_Interval = 'm' then
return ((date_part('y',p_DateTo) * 12) + date_part('month',p_DateTo))
- ((date_part('y',p_DateFrom) * 12) + date_part('month',p_DateFrom));
elseif p_Interval = 'y' then
return date_part('y',p_DateTo) - date_part('y',p_DateFrom);
else
raise exception 'Datediff: Interval not supported';
return 0;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

faster join

2006-07-26T02:04:00.000+08:00

-- slower 91.56%
alter PROCEDURE x_Financing_ExceedsDueDate
@NumberOfDays INT
AS
DECLARE @DayToday DATETIME
SET @DayToday = convert(DATETIME,CONvERT(vARCHAR,GETDATE(),112))
SELECT
X.InvoiceNo, X.PmtStructureNo, Fullname = COALESCE(i.Fullname, m.FUllname),D.PayImmediately,
DaysExceed = DATEDIFF(DAY, BillStatementDate, @DayToday)
FROM AccountInstallmentFinancingDetailFinal DINNER JOIN ( SELECT InvoiceNo, PmtStructureNo, PSO = MAX(PaymentSortOrder) FROM AccountInstallmentFinancingDetailFinal GROUP BY InvoiceNo, PmtStructureNo ) XON X.InvoiceNo = D.InvoiceNoAND X.PmtStructureNo = D.PmtStructureNoAND X.PSO = D.PaymentSortOrder
LEFT JOIN AccountInstallment ION I.InvoiceNo = D.InvoiceNo
LEFT JOIN Financing_AssumptionOfMortgage mON m.AoMNo = d.InvoiceNo
WHERE D.BillStatementDate IS NOT NULL AND @DayToday >= BillStatementDate + @NumberOfDays AND PayImmediately > 0

-- faster 8.44%

ALTER PROCEDURE Financing_ExceedsDueDate
@NumberOfDays INT
AS
DECLARE @DayToday DATETIME
SET @DayToday = convert(DATETIME,CONvERT(vARCHAR,GETDATE(),112))
SELECT
X.InvoiceNo, X.PmtStructureNo, A.Fullname,D.PayImmediately,
DaysExceed = DATEDIFF(DAY, BillStatementDate, @DayToday)
FROM AccountInstallmentFinancingDetailFinal DINNER JOIN ( SELECT InvoiceNo, PmtStructureNo, PSO = MAX(PaymentSortOrder) FROM AccountInstallmentFinancingDetailFinal GROUP BY InvoiceNo, PmtStructureNo ) XON X.InvoiceNo = D.InvoiceNoAND X.PmtStructureNo = D.PmtStructureNoAND X.PSO = D.PaymentSortOrder
INNER JOIN ( SELECT InvoiceNo, Fullname FROM AccountInstallment UNION SELECT AoMNo, Fullname FROM Financing_AssumptionOfMortgage ) A
ON X.InvoiceNo = A.InvoiceNo
WHERE D.BillStatementDate IS NOT NULL AND @DayToday >= BillStatementDate + @NumberOfDays AND PayImmediately > 0

math of reproduction

2006-05-14T03:24:00.000+08:00

Slashdot | Review: Matrix: Reloaded: "About 12 people reproducing into 250,000 people so quickly: yes this is possible. if each woman produced as many children as could be done healthily, say 10, you could do it in 4 new generations:

7 women * 10 children = 70 new ppl.
70 * 0.5 (50% girls) * 10 children = 350 new ppl.
350 *0.5 *10 = +1750 ppl.
8750 *0.5*10 = +43750 ppl.
218750 *0.5*10 = +218750 ppl.

12 + 70 + 350 + 1750 + 43750 + 218750
= 273,420 ppl.

Figure the early generation eventually dies off, and some other die off as well, you could get close to 250k easily in 100 years.

Assuming the first 12 are near child-bearing age when they start, and the bulk of the ppl get the reproducing done around age 25, 25*4 = 100 years."

2006-05-09T11:37:00.000+08:00

Slashdot | Computer Security, The Next 50 Years:

Educating users
(Score:5, Insightful)
by reldruH (956292) on Monday May 08, @02:31AM (#15283657)
(Last Journal: Wednesday May 03, @05:24PM)
What the article is basically saying is that we have to teach people how to use their computers. >85% of all the computer problems I encounter are PEBKAC (Problem Exists Between Keyboard And Chair). It's like the old saying goes, make something idiot proof and the world will make a better idiot. If people just learn how to use their computers (you shouldn't download exe's from people you don't know, a firewall is a good thing to have, ActiveX controls aren't safe and your default response shouldn't be to install them no matter what IE says) a huge number of problems would be eliminated. Like it or not, users are the biggest computer problem today. The problem shouldn't be usability, it should be user-ability.

2006-05-08T00:25:00.000+08:00

Intolerance of ambiguity is the mark of an authoritarian personality. Theodor Wiesengrund Adorno Quotation: "'Intolerance of ambiguity is the mark of an authoritarian personality.' -
-- Theodor Wiesengrund Adorno "

scalar function

2006-04-20T09:27:00.000+08:00


MS SQL:

 CREATE FUNCTION FormatterNumber(@i INT) RETURNS VARCHAR(100)
 AS
 BEGIN
  RETURN CONVERT(VARCHAR, @i) + ' Michael the INT'
 END
 GO
 SELECT DBO.FormatterNumber(1)

PostgreSQL: Note postresql parameter can be also be referenced by their parameter number, e.g. $1, $2..



 -- we don't have to declare varchar(100) in returns clause,
 -- it is adjusted dynamically
 
 -- method 1, this is flexible, can have multiple statements between begin and end
 CREATE FUNCTION FormatterNumber(m_I INT) RETURNS VARCHAR
 AS
 $$
 BEGIN
  RETURN m_I::VARCHAR || ' INT';
  -- RETURN $1::VARCHAR || ' INT'; -- this will work too
 END;
 $$
 LANGUAGE 'plpgsql';



 -- method 2, just change the language 'plpgsql' to 'sql',
 -- this is rigid, only one statement is allowed,
 -- though seems this is much faster(haven't yet benchmark against method 1)

 -- somehow the disadvantage of language 'sql' is you can't refer the paramater
 -- by its name, only by parameter position

 CREATE FUNCTION FormatterNumber(m_I INT) RETURNS VARCHAR
 AS
 $$
  SELECT $1::VARCHAR || ' Michael the INT';
 $$
 LANGUAGE 'sql';



 invocation sample:

 SELECT FormatterNumber(1);






MS SQL:

 CREATE FUNCTION FormatterDate(@d DATETIME) RETURNS VARCHAR(100)
 AS
 BEGIN
  RETURN CONVERT(VARCHAR, @d) + ' Michael the date'
 END
 GO

 SELECT DBO.FormatterDate('November 5 2006')


PostgreSQL:

  

 -- method 1
 CREATE OR REPLACE FUNCTION FormatterDate(m_D DATE) RETURNS VARCHAR
 AS
 $$
 BEGIN
  RETURN m_D::VARCHAR || ' Michael the date';
 END;
 $$
 LANGUAGE 'plpgsql';



 -- method 2
 CREATE OR REPLACE FUNCTION FormatterDate(m_D DATE) RETURNS VARCHAR
 AS
 $$
  SELECT $1::VARCHAR || ' Michael the date';
 $$
 LANGUAGE 'plpgsql';


 SELECT FormatterDate('November 5 2006')

PostgreSQL is much flexible in its function, you can overload function name,
you can have two function with same name,
resulting to a more symmetrical usage of function



 -- method 1
 CREATE OR REPLACE FUNCTION Formatter(m_I INT) RETURNS VARCHAR
 AS
 $$
 BEGIN
  RETURN m_D::VARCHAR || ' Michael the INT';
 END;
 $$
 LANGUAGE 'plpgsql';



 -- method 2
 CREATE OR REPLACE FUNCTION Formatter(m_D DATE) RETURNS VARCHAR
 AS
 $$
  SELECT $1::VARCHAR || ' Michael the date';
 $$
 LANGUAGE 'plpgsql';



 SELECT Formatter(1);
 SELECT Formatter('nov 5 2006');

best practice for avoiding dirty read

2006-04-19T14:47:00.001+08:00

for transactions or triggers that do any data manipulation routine(UPDATE,DELETE,INSERT?), follow guideline below:

for MS SQL, always put TABLOCKX in SELECT clause:
SET @PreviousQty = (SELECT Qty FROM Inventory WITH(TABLOCKX) WHERE ItemCode = @ItemCode

FOR PostgreSQL, always put FOR UPDATE in SELECT clause:
m_PreviousQty = (SELECT Qty FROM Inventory WHERE ItemCode = m_ItemCode FOR UPDATE)

always put row lock even if the SELECTED will not be the table to be written upon. this will ensure preventing dirty reads from other concurrently executing transactions that modifies table you read.

Note regarding row-level locking:
WITH(TABLOCKX) and FOR UPDATE can also be used to implement row-level pessimistic locking for LAN-based application transactions, i.e. when you open one record, no other program can read the same record, this is called pessimistic locking.

shared row locks

2006-04-19T14:15:00.000+08:00

PostgreSQL 8.1:

Shared row locks are a very important performance improvement for PostgreSQL’s implementation of referential integrity. PostgreSQL uses triggers to implement foreign keys, and thus it acquires locks using SQL statements. Before PostgreSQL 8.1, this meant the least restrictive lock it could acquire was a row exclusive lock (using a SELECT … FOR UPDATE statement). With 8.1, the developers added a method of acquiring a shared row lock via SQL (SELECT … FOR SHARE). This greatly reduces the locking contention in databases with lots of referential integrity.

DATEADD

2006-04-19T08:29:00.000+08:00

MSSQL
select dateadd(year,1, '2004-2-29')

PostgreSQL
select '2004-2-29'::date + cast('1 years' as interval)

MSSQL:
select dateadd(month,1,'2004-2-29')

PostgreSQL:
select '2004-2-29'::date + cast('1 months' as interval)

MSSQL:
select dateadd(month,1, '1976-11-05 6:29 AM')

PostgreSQL:
select '2006-01-06 6:29 AM'::timestamptz + cast('1 months' as interval)

MSSQL:
select dateadd(day,1,'2006-01-06 6:29 AM')
-- or --
select convert(datetime,'2006-01-06 6:29 AM') + 1

PosgtgreSQL:
select '2006-01-06 6:29 AM'::timestamp + cast('1 days' as interval);

------------------------------------------------------------------------
------------------------------------------------------------------------
MS SQL equivalent compatible DATEADD function

-- Function: dateadd(character varying, integer, date)

-- DROP FUNCTION dateadd(character varying, integer, date);

CREATE OR REPLACE FUNCTION dateadd(character varying, integer, date)
RETURNS date AS
$BODY$

DECLARE
p_Interval ALIAS FOR $1;
p_N ALIAS FOR $2;
p_Date ALIAS FOR $3;

BEGIN
if p_Interval = 'm' then
return p_Date + cast(p_N || ' months' as interval);
elseif p_Interval = 'y' then
return p_Date + cast(p_N || ' years' as interval);
else
raise exception 'dateadd interval parameter not supported';
-- raise exception 'hello';
return null;
end if;
END;
$BODY$
LANGUAGE 'plpgsql';

strip the time portion of the datetime

2006-04-19T01:55:00.000+08:00

MS SQL:

select convert(datetime,convert(varchar,getdate(),101))

PostgreSQL:

select now()::date

ANSI SQL: PostgreSQL OK, MS SQL NOT

select cast(now() as date)

current row version checking. uses for concurrency checking

2006-04-19T01:41:00.000+08:00

MS SQL:

-- checks if row has changed since last read
CREATE TABLE Distributor
(
MemberId VARCHAR(20),
TotalSales NUMERIC(10,2),
ConcurrencyCheck TIMESTAMP -- not related to ANSI-SQL 92 timestamp
)

INSERT INTO Distributor(MemberId, TotalSales) SELECT 'Michael',1000
SELECT * FROM Distributor
UPDATE Distributor SET TotalSales = TotalSales + 7000
SELECT * FROM Distributor

Here's the result:

MemberId TotalSales ConcurrencyCheck
-------------------- ------------ ------------------
Michael 1000.00 0x0000000000007C3B

MemberId TotalSales ConcurrencyCheck
-------------------- ------------ ------------------
Michael 8000.00 0x0000000000007C3C

---------------------------------------------------------

PostgreSQL:

Note: there's a hidden field in each postgres table, xmin for example, xmin changes every time changes happen in the row

-- checks if row has changed since last read
CREATE TABLE Distributor
(
MemberId VARCHAR(20),
TotalSales NUMERIC(10,2)
);

INSERT INTO Distributor(MemberId, TotalSales) SELECT 'Michael',1000;
SELECT *, Distributor.xmin FROM Distributor;
UPDATE Distributor SET TotalSales = TotalSales + 7000;
SELECT *, Distributor.xmin FROM Distributor;

Here's the result:

memberid | totalsales | xmin
----------+------------+------
Michael | 1000.00 | 3624

memberid | totalsales | xmin
----------+------------+------
Michael | 8000.00 | 3639

to convert xmin to integer(thanks to AndrewSN, freenode #postgresql):
select int8in(xidout(xmin)) from company

row-level locking, dirty-read prevention

2006-04-19T00:38:00.000+08:00

explicit row-level locking and its uses

----------

MS SQL: (have tested WITH(ROWLOCK) doesn't seems working)

SET @m_Hit = @m_Hit + (SELECT hit FROM topleveldomainhits WITH(TABLOCKX) WHERE tldsuffix = '.com')

SET @m_Hit = @m_Hit + 1

PostgreSQL:

SELECT hit INTO m_Hit FROM topleveldomainhits WHERE tldsuffix = '.com' FOR UPDATE;

m_Hit = m_Hit + 1;

--------------------------------------------------

Note:

Explicit row-Level Locking is not needed if you are not doing complex computation, for the sake of discussion we just make an example of complex hit computation : )

here is a non-complex hit computation:
-- statement A:
UPDATE topleveldomainhits SET hit = hit + 1 WHERE tldsuffix = '.com'

row-level locking is needed if you are doing complex computation,e.g. passing result to variables(likely scenario is doing multi-line computation) and/or the possibility of parallel processing is very high.

if the statement is is one just one line like statement A, and two users executed it in parallel and even you SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, they won't overwrite each other's data. single statements are an atomic operation in itself. so pagehit = 2 :)

-------------------------------
-------------------------------

another example of where explicit row-level locking is necessary, (complex computations)

for the purpose of discussion let's say we have only column field which is Bonus and employeeid

--- bonus computation prodedure run once every month ---



TotalBonus = $80
YearsWork 5
MonthsCount = 3


Final TotalBonus = $1200

MSSQL:

computer A:


CREATE PROCEDURE ComputeYearBonus
    @EmployeeID VARCHAR(8)
AS
    SET XACT_ABORT ON

    BEGIN TRAN
    DECLARE @YearsWork INT

    SELECT @YearsWork = YearCount FROM YearsWork WITH(TABLOCKX) WHERE Employee =
@EmployeeId

    DECLARE @Bonus MONEY
    -- process 1. read bonus = 80 * 5 = $400
    SET @Bonus = (SELECT TotalBonus FROM Employee WITH(TABLOCKX) WHERE EmployeeId = @EmployeeId) * @YearsWork

    -- process 4. write bonus = $400
    UPDATE Employee SET TotalBonus = @Bonus WHERE EmployeeId = @EmployeeID

    COMMIT TRAN
GO

computer B:


CREATE PROCEDURE ComputeMonthBonus
    @EmployeeID VARCHAR(8)

AS
    SET XACT_ABORT ON
    BEGIN TRAN

    DECLARE @MonthsWork INT
    SELECT @MonthsWork = MonthCount FROM MonthsWorkThisYear WITH(TABLOCKX) WHERE Employee = @EmployeeId

    DECLARE @Bonus MONEY
   
    -- process 2. read bonus = 80 * 3 = $240    
    SET @Bonus = (SELECT TotalBonus FROM Employee WITH(TABLOCKX) WHERE EmployeeId = @EmployeeId) * @MonthsWork

    -- process 4. write bonus = $240
    UPDATE Employee SET TotalBonus = @Bonus WHERE EmployeeId = @EmployeeID

    COMMIT TRAN

GO

without WITH(TABLOCKX), when computer A and computer B execute at the same time, there is a high probability that they will overwrite each other's bonus computation. see the steps above. last written bonus is $240 instead of $1200.

if we put WITH(TABLOCKX) in our SELECTs, process 2 will wait until computer A commits. after computer A commits, computer B will read the bonus as $400, then $400 multiply by $3 = $1200.

same thing would effect even if computer B executes first, computer B computes $240. then computer A would read the bonus, computer A can't read the bonus until computer B commits. after B commits, computer A will read the bonus of $240. then computer A will multiply this by 5. $240 * 5 = $1200.

you'll notice that in the YearsWork in MonthsWorkThisYear we also put TABLOCKX, same thing, if there are other parallel executing transaction or trigger that updates the two table at the same time with our ComputeBonus. the computebonus will be messed, there will be dirty reads if there is no row-locking, we can't afford to have our bonus miscomputed.

------------------------------------------------



PostgreSQL Version:


postgresql has implicit BEGIN TRAN AND COMMIT TRAN:



CREATE FUNCTION ComputeYearBonus() RETURNS VOID
    m_EmployeeID VARCHAR(8)
AS
$$
DECLARE
    m_YearsWork INT;
    m_Bonus NUMERIC;
BEGIN


    SELECT YearCount INTO m_YearsWork  FROM YearsWork WHERE Employee = m_EmployeeId FOR UPDATE;

    -- process 1. read bonus = 80 * 5 = $400
    m_Bonus = (SELECT TotalBonus FROM Employee WHERE EmployeeId = m_EmployeeId FOR UPDATE) * m_YearsWork;


    -- process 4. write bonus = $400
    UPDATE Employee SET TotalBonus = m_Bonus WHERE EmployeeId = m_EmployeeID;

END;
$$
LANGUAGE 'plpgsql';




CREATE FUNCTION ComputeMonthBonus() RETURNS VOID
    m_EmployeeID VARCHAR(8)

AS
$$
DECRLARE
    m_MonthsWork INT
    m_Bonus NUMERIC
BEGIN


    SELECT MonthCount INTO m_MonthsWork FROM MonthsWorkThisYear WHERE Employee = m_EmployeeId FOR UPDATE;


    -- process 2. read bonus = 80 * 3 = $240    
    m_Bonus = (SELECT TotalBonus FROM Employee WHERE EmployeeId = m_EmployeeId FOR UPDATE) * m_MonthsWork;


    -- process 4. write bonus = $240
    UPDATE Employee SET TotalBonus = m_Bonus WHERE EmployeeId = m_EmployeeID;

END;
$$
LANGUAGE 'plpgsql';

table-level locking

2006-04-19T00:09:00.000+08:00

MS SQL:
SET @m_Hit = (SELECT hit FROM totalhits WITH (TABLOCKX))
SET @m_Hit = @m_Hit + 1

PostgreSQL:

method 1:
SELECT hit INTO m_Hit FROM totalhits FOR UPDATE;
m_Hit = m_Hit + 1;

method 2:
LOCK TABLE pagehit IN SHARE ROW EXCLUSIVE MODE;
SELECT hit INTO m_Hit FROM totalhits;
m_Hit = m_Hit + 1;

mssql features not present in postgresql

2006-04-16T13:24:00.000+08:00

When migrating from PostgreSQL to MS SQL be aware that the following isn't present in PostgreSQL version 8

1. Returning multiple recordset from a function
2. Formula (but can be simulated in trigger)
3. Case Insensitive search (have to use ILIKE or UPPER)

ms sql stored proc, views, functions, triggers migrations to postgresql

2006-04-16T02:01:00.000+08:00