Hi Jeff,
This is the mail I was talking about in the last con-call. Please
provide your thoughts on these issues.
Best regards,
On Thu, Jan 22, 2009 at 5:59 PM, Gurjeet Singh <
[email protected]> wrote:
> .) Orafce support for Postgres 8.1:
>
> I am unable to find any concrete documentation on Orafce, apart from
> the README in the sources. So can't comment on the deficiencies it'd have if
> ported to PG 8.1.
>
> .) Empty string in queries. ('')
>
> The proposed solution of putting a CHECK constraint will not work, as
> it is; we might have to go a step further.
>
> Putting
>
> col1 varchar(20) CHECK ( col1 <> '' )
>
> will not work since '' is equivalent to NULL, so will allow
> INSERT/UPDATE to store empty string (NULL) in this column. Instead,
>
> col1 varchar(20) CHECK( col IS NOT NULL )
>
> will work, but is equivalent to
>
> col1 varchar(20) NOT NULL
>
> Will putting NOT NULL constraint on all CHAR columns be acceptable to
> the application/developers?
>
> .) ROWNUM vs. LIMIT..OFFSET
>
> I researched a bit, and also grepped for the ROWNUM usage in the whole
> source tree, and it seems that there's no acceptable workaround that will
> work for both the databases. So I think we will have to have two different
> versions of such queries, one for Oracle and one for Postgres; unless, the
> application can be modified to not require this feature.
>
> There are two kinds of usages I saw in the code:
>
> 1) SELECT ... FROM .... WHERE rownum < 3
> 2) SELECT ..., rownum AS rank FROM ....
>
> The first kind of usage can be easily imitated using the LIMIT clause
> usage, but the second kind of usage is quite hard to port to Postgres!
> Here's an article discussing a few alternatives, but none of them elegant:
>
>
> http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/
>
> I am trying to devise a new contrib/ module for Postgres, that might
> give us ROWNUM compatibilty. I'll get back to you on this in a couple of
> days.
>
> .) Multi-column updates using subquery
>
> As posted by Bruce earlier, we can use the FROM clause in UPDATE to get
> around this problem. So I guess, we will have to maintain two versions of
> such queries too.
>
> .) SYSDATE vs. CURRENT_TIMESTAMP
>
> We understand that these two are very different in their behavoiur, so
> my suggestion would be to create a wrapper function in bot Oracle and
> Postgres, something like getCurrentTimestamp(). The Oracle version of this
> function would simply return SYSDATE, and the Postgres version would be
> written to match Oracle's output format, as well as datatype.
>
> .) Porting sequence_name.nextval() and currval().
>
> The proposal here would be to create wrappers in Oracle, which would
> work the same way as the Postgres' version of these functions work, and then
> use these wrappers all across the code to get next/current value of a
> sequence.
>
> currval( varchar ), nextval( varchar ) -- accepts sequnce name, and
> returns the current/next value of the sequence.
>
> Internally, in Oracle, these functions would simply return the result
> of seqname.nextval(); we might need to use the dynamic SQL in Oracle for
> this.
>
> .) Autonomous Transactions
>
> IMHO, Autonomous Transactions are not for avoiding mutating table
> problem; but they might be used to get around it. While inside a transaction
> (say Ta) we can start another transaction (say Tb), and the result is that,
> that irrespective of whether Ta COMMITs or ROLLsBACK, Tb's COMMIT/ROLLBACK
> will be persistent. IOW, Ta is not affected by Tb's final status, and Tb is
> not affected by Ta's final status; but you may get into a deadlock if you
> are not careful.
>
> So, since Postgres does not provide native support for Autonomous
> transactions, the proposal is to create a loopback dblink, and use that to
> execute the stored procedures that need this behaviour of two parallel
> transactions.
>
>
> We need some consensus on these above points, and the points/approaches
> that have already been agreed upon in the previous thread will be updated on
> the Wiki.
>
> Best regards,
> --
> gurjeet[[email protected]
> EnterpriseDB http://www.enterprisedb.com
>
> singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
>
>
--
gurjeet[[email protected]
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
