.) 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[.sin...@enterprisedb.com
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
./web/modules/rhn/RHN/DB/Errata.pm: AND ROWNUM = 1
./web/modules/rhn/RHN/DB/DataSource/xml/ConfigChannel_queries.xml: rownum AS
rank
./monitoring/SputLite/html/cgi-bin/create_commands.cgi: WHERE ROWNUM <=
$MAX_ROW_COUNT
./monitoring/SputLite/html/cgi-bin/create_commands.cgi: WHERE ROWNUM <= $size
./monitoring/PerlModules/NP/Probe/DataSource/test/TestOracle.pm: from
probe where rownum < 10
./monitoring/PerlModules/NP/NOT-USED/TelAlert/TelAlert.pm:WHERE rownum = 1
./satellite/install/Spacewalk-Setup/lib/Spacewalk/Setup.pm: and rownum = 1
./satellite/schema/DBObjects.py: select rownum, uo.*
./java/code/webapp/WEB-INF/pages/common/fragments/channel/isorow.jspf:
<c:when test="${rownum % 2 == 0}"><tr class="list-row-even"></c:when>
./java/code/webapp/WEB-INF/pages/common/fragments/channel/isorow.jspf: <c:set
var="rownum" value="${rownum+1}"/>
./java/code/src/com/redhat/rhn/common/db/datasource/xml/ErrataCache_queries.xml:
AND ROWNUM = 1) AS errata_id,
./java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml:
where rownum <= :num
./java/code/src/com/redhat/rhn/common/db/datasource/xml/config_queries.xml:where
rownum <= :num
./schema/spacewalk/rhnsat/procs/delete_server.sql: and rownum <= 1;
./schema/spacewalk/rhnsat/procs/lookup_first_matching_cf.sql:
rownum rn
./schema/spacewalk/rhnsat/views/rhnServerNeededPackageView.sql: AND
ROWNUM = 1),
./schema/spacewalk/rhnsat/packages/rhn_org.pkb: and
rownum = 1;
./schema/spacewalk/rhnsat/packages/rhn_org.pkb:
and rownum = 1;
./schema/spacewalk/rhnsat/packages/rhn_org.pkb:
and rownum = 1;
./schema/spacewalk/rhnsat/packages/rhn_channel.pkb: select
rownum row_number, server_id, modified from (
./schema/spacewalk/rhnsat/packages/rhn_channel.pkb: where rownum >
quantity_in
./schema/spacewalk/rhnsat/packages/rhn_server.pkb: and rownum = 1;
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb: select
rownum, vi.virtual_system_id
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb: where
rownum <= quantity_in;
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb: select
rownum, vi.virtual_system_id
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb: where
rownum <= quantity_in;
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb: -- will
work w/ rownum, swaping 'em in the body of this if...
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb:
select rownum row_number,
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb:
select rownum row_number,
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb:
rownum row_number
./schema/spacewalk/rhnsat/packages/rhn_entitlements.pkb:
select rownum row_number,
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/207-rhn_org.pkb.sql:
and rownum = 1;
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/207-rhn_org.pkb.sql:
and rownum = 1;
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/207-rhn_org.pkb.sql:
and rownum = 1;
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
select rownum, vi.virtual_system_id
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
where rownum <= quantity_in;
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
select rownum, vi.virtual_system_id
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
where rownum <= quantity_in;
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
-- will work w/ rownum, swaping 'em in the body of this if...
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
select rownum row_number,
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
select rownum row_number,
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
rownum row_number
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/205-rhn_entitlements.pkb.sql:
select rownum row_number,
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/203-rhn_channel.pkb.sql:
select rownum row_number, server_id, modified from (
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/203-rhn_channel.pkb.sql:
where rownum > quantity_in
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/060-rhn_channel-deterministic.sql:
select rownum row_number, server_id, modified from (
./schema/spacewalk/upgrade/satellite-5.1-spacewalk-0.2/060-rhn_channel-deterministic.sql:
where rownum > quantity_in
./schema/spacewalk/upgrade/spacewalk-0.3-spacewalk-0.4/130-delete_server.sql:
and rownum <= 1;
./schema/spacewalk/upgrade/spacewalk-0.3-spacewalk-0.4/150-rhn_org.pkb.sql:
and rownum = 1;
./schema/spacewalk/upgrade/spacewalk-0.3-spacewalk-0.4/150-rhn_org.pkb.sql:
and rownum = 1;
./schema/spacewalk/upgrade/spacewalk-0.3-spacewalk-0.4/150-rhn_org.pkb.sql:
and rownum = 1;
./schema/spacewalk/upgrade/spacewalk-0.3-spacewalk-0.4/122-rhn_channel.pkb.sql:
select rownum row_number, server_id, modified from (
./schema/spacewalk/upgrade/spacewalk-0.3-spacewalk-0.4/122-rhn_channel.pkb.sql:
where rownum > quantity_in
./documentation/python-doc/server.rhnSQL.sql_table-pysrc.html:<a
name="L75"></a><tt class="py-lineno"> 75</tt> <tt class="py-line"><tt
class="py-comment"></tt> <tt class="py-name">sql</tt> <tt
class="py-op">=</tt> <tt class="py-string">"select %s from %s where rownum =
0"</tt> <tt class="py-op">%</tt> <tt class="py-op">(</tt><tt
class="py-name">self</tt><tt class="py-op">.</tt><tt
class="py-name">__hashid</tt><tt class="py-op">,</tt> <tt
class="py-name">self</tt><tt class="py-op">.</tt><tt
class="py-name">__table</tt><tt class="py-op">)</tt> </tt>
./documentation/python-doc/server.rhnSQL.sql_row-pysrc.html:<a
name="L42"></a><tt class="py-lineno"> 42</tt> <tt class="py-line"><tt
class="py-comment"></tt> <tt class="py-name">sql</tt> <tt
class="py-op">=</tt> <tt class="py-string">"select %s from %s where rownum =
0"</tt> <tt class="py-op">%</tt> <tt class="py-op">(</tt><tt
class="py-name">self</tt><tt class="py-op">.</tt><tt
class="py-name">hashname</tt><tt class="py-op">,</tt> <tt
class="py-name">self</tt><tt class="py-op">.</tt><tt id="link-14"
class="py-name"><a title="satellite_tools.satComputePkgHeaders.table"
class="py-name" href="#" onclick="return doclink('link-14', 'table',
'link-11');">table</a></tt><tt class="py-op">)</tt> </tt>
./documentation/python-doc/satellite_tools.exporter.exportLib-pysrc.html:<a
name="L505"></a><tt class="py-lineno"> 505</tt> <tt class="py-line"><tt
class="py-string"> and rownum < 3</tt> </tt>
./documentation/python-doc/satellite_tools.exporter.exportLib-pysrc.html:<a
name="L622"></a><tt class="py-lineno"> 622</tt> <tt class="py-line"><tt
class="py-string"> and rownum < 3</tt> </tt>
./documentation/python-doc/satellite_tools.exporter.exportLib-pysrc.html:<a
name="L671"></a><tt class="py-lineno"> 671</tt> <tt class="py-line"><tt
class="py-string"> and rownum < 3</tt> </tt>
./documentation/python-doc/satellite_tools.exporter.exportLib-pysrc.html:<a
name="L772"></a><tt class="py-lineno"> 772</tt> <tt class="py-line"><tt
class="py-string"> where rownum < 3</tt> </tt>
./documentation/python-doc/server.importlib.errataCache-pysrc.html:<a
name="L33"></a><tt class="py-lineno">33</tt> <tt class="py-line"><tt
class="py-string"> and rownum < 2</tt> </tt>
./documentation/python-doc/server.action.rollback-pysrc.html:<a
name="L51"></a><tt class="py-lineno">51</tt> <tt class="py-line"><tt
class="py-string"> and rownum < 2</tt> </tt>
./documentation/python-doc/server.rhnServer.server_lib-pysrc.html:<a
name="L195"></a><tt class="py-lineno">195</tt> <tt class="py-line"> <tt
class="py-name">rownum</tt> <tt class="py-op">=</tt> <tt
class="py-name">h</tt><tt class="py-op">.</tt><tt id="link-69"
class="py-name"><a
title="satellite_tools.intersat_sync.dumper.DatabaseStatement.execute
./documentation/python-doc/server.rhnServer.server_lib-pysrc.html:<a
name="L197"></a><tt class="py-lineno">197</tt> <tt class="py-line"> <tt
class="py-keyword">if</tt> <tt class="py-name">rownum</tt> <tt
class="py-op">==</tt> <tt class="py-number">0</tt><tt class="py-op">:</tt> </tt>
./documentation/db-schema/spacewalk-0.1/sql_sources/view/rhnserverneededpackageview.sql:
AND ROWNUM = 1),
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_server.sql:
and rownum = 1;
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
select rownum, vi.virtual_system_id
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
where rownum <= quantity_in;
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
select rownum, vi.virtual_system_id
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
where rownum <= quantity_in;
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
select rownum row_number,
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
select rownum row_number,
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
rownum row_number
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_entitlements.sql:
select rownum row_number,
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_org.sql:
and rownum = 1;
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_org.sql:
and rownum = 1;
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_org.sql:
and rownum = 1;
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_channel.sql:
select rownum row_number, server_id, modified from (
./documentation/db-schema/spacewalk-0.1/sql_sources/package/rhn_channel.sql:
where rownum > quantity_in
./documentation/db-schema/spacewalk-0.1/sql_sources/function/lookup_first_matching_cf.sql:
rownum rn
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
404<span class="operator">:</span> <span
class="reserved">select</span> rownum<span class="operator">,</span> vi<span
class="operator">.</span>virtual_system_id
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
416<span class="operator">:</span> <span
class="reserved">where</span> rownum <span class="operator"><</span><span
class="operator">=</span> quantity_in;
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
420<span class="operator">:</span> <span
class="reserved">select</span> rownum<span class="operator">,</span> vi<span
class="operator">.</span>virtual_system_id
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
432<span class="operator">:</span> <span
class="reserved">where</span> rownum <span class="operator"><</span><span
class="operator">=</span> quantity_in;
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
707<span class="operator">:</span>
<span class="reserved">select</span> rownum row_number<span
class="operator">,</span>
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
732<span class="operator">:</span> <span
class="reserved">select</span> rownum row_number<span class="operator">,</span>
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
1083<span class="operator">:</span>
rownum row_number
./documentation/db-schema/spacewalk-0.1/package-RHN_ENTITLEMENTS.html:
1194<span class="operator">:</span>
<span class="reserved">select</span> rownum row_number<span
class="operator">,</span>
./documentation/db-schema/spacewalk-0.1/view-RHNSERVERNEEDEDPACKAGEVIEW.html:
<span class="reserved">AND</span> ROWNUM <span class="operator">=</span>
1<span class="operator">)</span><span class="operator">,</span>
./documentation/db-schema/spacewalk-0.1/package-RHN_SERVER.html: 338<span
class="operator">:</span> <span class="reserved">and</span> rownum <span
class="operator">=</span> 1;
./documentation/db-schema/spacewalk-0.1/function-LOOKUP_FIRST_MATCHING_CF.html:
13<span class="operator">:</span> rownum rn
./documentation/db-schema/spacewalk-0.1/package-RHN_ORG.html: 103<span
class="operator">:</span> <span
class="reserved">and</span> rownum <span class="operator">=</span> 1;
./documentation/db-schema/spacewalk-0.1/package-RHN_ORG.html: 123<span
class="operator">:</span> <span
class="reserved">and</span> rownum <span class="operator">=</span> 1;
./documentation/db-schema/spacewalk-0.1/package-RHN_ORG.html: 146<span
class="operator">:</span> <span
class="reserved">and</span> rownum <span class="operator">=</span> 1;
./documentation/db-schema/spacewalk-0.1/package-RHN_CHANNEL.html: 618<span
class="operator">:</span> <span class="reserved">select</span>
rownum row_number<span class="operator">,</span> server_id<span
class="operator">,</span> modified <span class="reserved">from</span> <span
class="operator">(</span>
./documentation/db-schema/spacewalk-0.1/package-RHN_CHANNEL.html: 626<span
class="operator">:</span> <span class="reserved">where</span>
rownum <span class="operator">></span> quantity_in
./backend/server/importlib/errataCache.py: and rownum < 2
./backend/server/action/rollback.py: and rownum < 2
./backend/server/rhnSQL/sql_row.py: sql = "select %s from %s where rownum =
0" % (self.hashname, self.table)
./backend/server/rhnSQL/sql_table.py: sql = "select %s from %s where rownum =
0" % (self.__hashid, self.__table)
./backend/server/rhnServer/server_lib.py: rownum = h.execute(new_id =
ret_id, org_id = org_id,
./backend/server/rhnServer/server_lib.py: if rownum == 0:
./backend/satellite_tools/exporter/exportLib.py: and rownum < 3
./backend/satellite_tools/exporter/exportLib.py: and rownum < 3
./backend/satellite_tools/exporter/exportLib.py: and rownum < 3
./backend/satellite_tools/exporter/exportLib.py: where rownum < 3
./backend/satellite_exporter/exporter/exportLib.py: and rownum < 3
./backend/satellite_exporter/exporter/exportLib.py: and rownum < 3
./backend/satellite_exporter/exporter/exportLib.py: and rownum < 3
./backend/satellite_exporter/exporter/exportLib.py: where rownum < 3
../../../../spacewalk/java/code/src/com/redhat/rhn/common/db/datasource/xml/Set_queries.xml:
WHERE rowid IN(SELECT s.rowid
../../../../spacewalk/java/code/src/com/redhat/rhn/common/db/datasource/xml/Set_queries.xml:
WHERE rowid IN(SELECT s.rowid
../../../../spacewalk/java/code/src/com/redhat/rhn/common/db/datasource/xml/Set_queries.xml:
WHERE rowid IN(SELECT s.rowid
../../../../spacewalk/schema/spacewalk/rhnsat/procs/pxt_session_cleanup.sql:
select rowid from PXTSessions
../../../../spacewalk/schema/spacewalk/rhnsat/procs/pxt_session_cleanup.sql:
delete from PXTSessions where rowid = session.rowid;
../../../../spacewalk/schema/spacewalk/rhnsat/tables/rhnEmailAddress_indexes.sql:--
but updates (where the lookup is on id) do a rowid lookup. user_id will
../../../../spacewalk/schema/spacewalk/rhnsat/tables/rhnEmailAddress_indexes.sql:--
do a rowid lookup as well, but that isn't (yet) the most heavily hit path.
../../../../spacewalk/documentation/db-schema/spacewalk-0.1/sql_sources/procedure/pxt_session_cleanup.sql:
select rowid from PXTSessions
../../../../spacewalk/documentation/db-schema/spacewalk-0.1/sql_sources/procedure/pxt_session_cleanup.sql:
delete from PXTSessions where rowid = session.rowid;
../../../../spacewalk/backend/server/rhnSQL/sql_table.py: def __init__(self,
dict, db, sql, rowid, cache = None):
../../../../spacewalk/backend/server/rhnSQL/sql_table.py: self.__rowid =
rowid
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
