Forum .LRN Q&A: More fixes for Postgres port

Collapse
Posted by Janine Ohmer on
1. Could not create a subgroup of a class.

Change dotlrn_community__new, in dotlrn/sql/postgresql/communities-package-create.sql, from

        c_id                            integer;
BEGIN
        c_id := acs_group__new (
to
        c_id                            integer;
        real_archived_p                 char(1);
BEGIN
        if p_archived_p is null then
            real_archived_p := ''f'';
        else
            real_archived_p := p_archived_p;
        end if;

        c_id := acs_group__new (
And then use real_archived_p in the insert into dotlrn_communities_all.

The problem here is that in Oracle you can put a default value on a function parameter, so it set p_archived_p to 'f' automagically. PG doesn't have that feature so we have to do it ourselves.

2. Could not move portlets around on customize page.

Move portal::swap_element.get_prev_sort_key and portal::swap_element.get_next_sort_key from new-portal/tcl/portal-procs.xql to new-portal/tcl/portal-procs-oracle.xql and new-portal/tcl/portal-procs-postgresql.xql. Modify the Postgres version from

            select sort_key as other_sort_key,
                   element_id as other_element_id
            from (select pem.sort_key,
                         element_id
                  from portal_element_map pem,
                       portal_pages pp
                  where pp.portal_id = :portal_id
                  and pem.page_id = :my_page_id
                  and pp.page_id = pem.page_id
                  and region = :region
                  and pem.sort_key < :my_sort_key
                  and state != 'pinned'
                  order by pem.sort_key desc)
            where rownum = 1
to
            select sort_key as other_sort_key,
                   element_id as other_element_id
            from (select pem.sort_key,
                         element_id
                  from portal_element_map pem,
                       portal_pages pp
                  where pp.portal_id = :portal_id
                  and pem.page_id = :my_page_id
                  and pp.page_id = pem.page_id
                  and region = :region
                  and pem.sort_key < :my_sort_key
                  and state != 'pinned'
                  order by pem.sort_key desc) this
            limit 1

(this is get_prev_sort_key; get_next_sort_key is similar)

Also create portal::swap_element.get_portal_element_map_nextval for both databases:

Oracle: select portal_element_map_sk_seq.nextval from dual

Postgres: select nextval('portal_element_map_sk_seq')

And use this query in portal-procs.tcl, function swap_element, instead of db_nextval.

The first changes are due to Postgres syntax differences. The second is because the way nextval has been simulated isn't reliable in PG 7.2, so it is not being used consistently anymore and this is one place where the old way wasn't working.

3. Could not delete pages from customized portal.

In new-portal/sql/postgresql/api-create.sql, change

create function portal_page__delete(integer)
returns integer as '
declare
    p_page_id                       integer;
to
create function portal_page__delete(integer)
returns integer as '
declare
    p_page_id                       alias for $1;

The function doesn't do much without the passed-in page_id! :)

That's all for tonight...

Collapse
Posted by Dan Chak on
Janine,

Thanks for pointing us at this bug.  The fix of checking p_archive_p may appear to work, but it will probably cause errors down the road.  The real problem here was that the call to define_function_args for dotlrn_community__new had some parameters out of order, so when package_instantiate_object is called for a dotlrn_community, a different parameter was being passed as p_archive_p than what would have been expected.

define_function_args also has the facility for defining default values for use by package_instantiate_object.  You can specify these by adding a ";default_value" in the parameter list after the parameter to receive a default value.  The updated define_function_args for dotlrn_community__new, with a default of 'f' for p_archive_p is:

select define_function_args('dotlrn_community__new','community_id,parent_community_id,community_type,community_key,pretty_name,description,archived_p;f,portal_id,non_member_portal_id,package_id,join_policy,creation_date,creation_user,creation_ip,context_id');

You can copy this line into plsql and it will override the old define_function_args.

Collapse
Posted by Janine Ohmer on
Thanks, Dan!  I thought I had checked the order but obviously not closely enough. :)
Collapse
Posted by Dan Chak on
Janine, re the portal_element_map_sk_seq problem, the nextval issue is a big problem that needs to be addressed globally. We're trying to avoid splitting up queries by database based only on a nextval incompatibility. Unfortunately, finding a good general solution to this problem has been .. difficult.

For now, I've proposed a hack to db_nextval that will make the query work for both sequences that are sequences, as well as sequences that are views-acting-as-sequences. That thread is here.

With this updated db_nextval, moving the portlets around works without splitting up the queries, and will also alleviate the need to split up future nextval queries into multiple xql files. Let's see what people have to say about this approach in that thread.

Collapse
Posted by Dan Chak on
Janine,

re the p_page_id bug, Thank you!  It's now fixed in the cvs tree.

Collapse
Posted by Nani rao on
My query in oracle is

INSERT INTO TRAVEL(LOGIN_ID, LOCK_SESSION_ID,  LOCK_OPERATION, LOCK_KEY1 ,USER_NAME )  SELECT 'RAM','CD7F','TER','21','null' from DUAL where not exists  ( SELECT * FROM TRAVEL A  WHERE A.LOCK_OPERATION = 'TER' AND LOCK_KEY1 = '21');

can u help me to ececute same query in postgresql