0023603: 3 warnings related to views are always shown on every pg_restore

Description

The following warnings are shown in every pg_restore:

pg_restore: WARNING: column "delete_btn" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
pg_restore: WARNING: column "em_aprm_modif_paym_sched" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
pg_restore: WARNING: column "em_aprm_modif_paym_out_sched" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

As far as we know those are harmless but may confuse users checking on warnigns during pg_restore.

They are triggered by using i.e.

'N' as somealias

as a part of a view definition.

A quick check showed that changing those to to_char('N') does silence the warning.

A small patch doing this change is attached.

This does survive a try-run but is not tested otherwise.

Note: one or both of those view definitions seem not used anymore so another option would be to remove those also.

Steps To Reproduce

Take a pi workspace
run a pg_dump of the underlying database
create a new empty database and restore the dump into it.
Check on warnings shown during restore.

Tags

No tags attached.

Attached Files

23603.diff [^] (7,780 bytes) 2013-04-22 09:59 [Show Content] [Hide Content]

diff -r 9236c5ce39d9 modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_FINACC_TRANSACTION_V.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_FINACC_TRANSACTION_V.xml	Fri Apr 19 16:43:16 2013 +0000
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_FINACC_TRANSACTION_V.xml	Mon Apr 22 09:59:17 2013 +0200
@@ -1,4 +1,4 @@
 <?xml version="1.0"?>
   <database name="VIEW APRM_FINACC_TRANSACTION_V">
-    <view name="APRM_FINACC_TRANSACTION_V"><![CDATA[SELECT trx.fin_finacc_transaction_id AS aprm_finacc_transaction_v_id, trx.fin_finacc_transaction_id, trx.ad_client_id, trx.ad_org_id, trx.isactive, trx.created, trx.createdby, trx.updated, trx.updatedby, trx.fin_financial_account_id, trx.line, trx.description, trx.statementdate, trx.trxtype, trx.fin_payment_id, trx.c_glitem_id, trx.depositamt, trx.paymentamt, trx.c_currency_id, trx.fin_reconciliation_id, trx.status, trx.createdbyalgorithm, trx.posted, trx.dateacct, trx.processed, trx.processing, trx.user1_id, trx.user2_id, trx.c_activity_id, trx.c_campaign_id, trx.c_project_id, trx.foreign_currency_id, trx.foreign_amount, trx.em_aprm_modify, trx.foreign_convert_rate,  CASE trx.status WHEN 'RPPC' THEN 'Y' ELSE 'N' END AS cleared, COALESCE(trx.c_bpartner_id, p.c_bpartner_id) AS c_bpartner_id, trx.m_product_id, trx.c_salesregion_id, p.documentno AS paymentdocno, COALESCE(r.processed, 'N') AS reconciled, 'N' AS delete_btn, (SELECT ad_table.ad_table_id FROM ad_table WHERE lower(ad_table.tablename) = 'fin_finacc_transaction') AS forced_table_id, trx.c_costcenter_id FROM fin_finacc_transaction trx LEFT JOIN fin_payment p ON trx.fin_payment_id = p.fin_payment_id LEFT JOIN fin_reconciliation r ON trx.fin_reconciliation_id = r.fin_reconciliation_id]]></view>
+    <view name="APRM_FINACC_TRANSACTION_V"><![CDATA[SELECT trx.fin_finacc_transaction_id AS aprm_finacc_transaction_v_id, trx.fin_finacc_transaction_id, trx.ad_client_id, trx.ad_org_id, trx.isactive, trx.created, trx.createdby, trx.updated, trx.updatedby, trx.fin_financial_account_id, trx.line, trx.description, trx.statementdate, trx.trxtype, trx.fin_payment_id, trx.c_glitem_id, trx.depositamt, trx.paymentamt, trx.c_currency_id, trx.fin_reconciliation_id, trx.status, trx.createdbyalgorithm, trx.posted, trx.dateacct, trx.processed, trx.processing, trx.user1_id, trx.user2_id, trx.c_activity_id, trx.c_campaign_id, trx.c_project_id, trx.foreign_currency_id, trx.foreign_amount, trx.em_aprm_modify, trx.foreign_convert_rate,  CASE trx.status WHEN 'RPPC' THEN 'Y' ELSE 'N' END AS cleared, COALESCE(trx.c_bpartner_id, p.c_bpartner_id) AS c_bpartner_id, trx.m_product_id, trx.c_salesregion_id, p.documentno AS paymentdocno, COALESCE(r.processed, 'N') AS reconciled, to_char('N') AS delete_btn, (SELECT ad_table.ad_table_id FROM ad_table WHERE lower(ad_table.tablename) = 'fin_finacc_transaction') AS forced_table_id, trx.c_costcenter_id FROM fin_finacc_transaction trx LEFT JOIN fin_payment p ON trx.fin_payment_id = p.fin_payment_id LEFT JOIN fin_reconciliation r ON trx.fin_reconciliation_id = r.fin_reconciliation_id]]></view>
   </database>
diff -r 9236c5ce39d9 src-db/database/model/views/FIN_PAYMENT_SCHED_INV_V.xml
--- a/src-db/database/model/views/FIN_PAYMENT_SCHED_INV_V.xml	Fri Apr 19 16:43:16 2013 +0000
+++ b/src-db/database/model/views/FIN_PAYMENT_SCHED_INV_V.xml	Mon Apr 22 09:59:17 2013 +0200
@@ -1,4 +1,4 @@
 <?xml version="1.0"?>
   <database name="VIEW FIN_PAYMENT_SCHED_INV_V">
-    <view name="FIN_PAYMENT_SCHED_INV_V"><![CDATA[SELECT ps.fin_payment_schedule_id AS fin_payment_sched_inv_v_id, ps.ad_client_id, ps.ad_org_id, ps.isactive, ps.created, ps.createdby, ps.updated, ps.updatedby, ps.c_invoice_id, ps.c_order_id, ps.duedate, ps.expecteddate, ps.fin_paymentmethod_id, ps.amount AS expected, ps.paidamt AS received, ps.outstandingamt AS outstanding, ps.c_currency_id, (SELECT max(p.paymentdate) AS max FROM fin_payment p LEFT JOIN fin_payment_detail pd ON pd.fin_payment_id = p.fin_payment_id LEFT JOIN fin_payment_scheduledetail psd ON pd.fin_payment_detail_id = psd.fin_payment_detail_id WHERE ps.c_invoice_id IS NOT NULL AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS lastpayment, (SELECT count(*) AS count FROM fin_payment_detail pd, fin_payment_scheduledetail psd WHERE pd.fin_payment_detail_id = psd.fin_payment_detail_id AND ps.c_invoice_id IS NOT NULL AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS numberofpayments, ps.fin_payment_priority_id, ps.update_payment_plan, to_char('Y') AS ob_selected, (SELECT  CASE WHEN to_number(max(pdv.paymentdate) - min(ops.duedate)) > 0 THEN to_number(max(pdv.paymentdate) - min(ops.duedate)) ELSE 0 END AS "case" FROM fin_orig_paym_scheddetail opsd LEFT JOIN fin_orig_payment_schedule ops ON opsd.fin_orig_payment_schedule_id = ops.fin_orig_payment_schedule_id LEFT JOIN fin_payment_scheduledetail psd ON psd.fin_payment_scheduledetail_id = opsd.fin_payment_scheduledetail_id LEFT JOIN fin_payment_detail_v pdv ON pdv.fin_payment_detail_v_id = psd.fin_payment_scheduledetail_id WHERE psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS daysoverdue, COALESCE((SELECT sum(psd.amount) AS sum FROM fin_payment_scheduledetail psd, fin_payment_detail pd LEFT JOIN fin_payment p ON pd.fin_payment_id = p.fin_payment_id WHERE psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id AND psd.fin_payment_detail_id = pd.fin_payment_detail_id AND p.status = 'RPAE'), 0) AS awaiting_execution, 'Y' AS em_aprm_modif_paym_sched, 'Y' AS em_aprm_modif_paym_out_sched FROM fin_payment_schedule ps]]></view>
+    <view name="FIN_PAYMENT_SCHED_INV_V"><![CDATA[SELECT ps.fin_payment_schedule_id AS fin_payment_sched_inv_v_id, ps.ad_client_id, ps.ad_org_id, ps.isactive, ps.created, ps.createdby, ps.updated, ps.updatedby, ps.c_invoice_id, ps.c_order_id, ps.duedate, ps.expecteddate, ps.fin_paymentmethod_id, ps.amount AS expected, ps.paidamt AS received, ps.outstandingamt AS outstanding, ps.c_currency_id, (SELECT max(p.paymentdate) AS max FROM fin_payment p LEFT JOIN fin_payment_detail pd ON pd.fin_payment_id = p.fin_payment_id LEFT JOIN fin_payment_scheduledetail psd ON pd.fin_payment_detail_id = psd.fin_payment_detail_id WHERE ps.c_invoice_id IS NOT NULL AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS lastpayment, (SELECT count(*) AS count FROM fin_payment_detail pd, fin_payment_scheduledetail psd WHERE pd.fin_payment_detail_id = psd.fin_payment_detail_id AND ps.c_invoice_id IS NOT NULL AND psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS numberofpayments, ps.fin_payment_priority_id, ps.update_payment_plan, to_char('Y') AS ob_selected, (SELECT  CASE WHEN to_number(max(pdv.paymentdate) - min(ops.duedate)) > 0 THEN to_number(max(pdv.paymentdate) - min(ops.duedate)) ELSE 0 END AS "case" FROM fin_orig_paym_scheddetail opsd LEFT JOIN fin_orig_payment_schedule ops ON opsd.fin_orig_payment_schedule_id = ops.fin_orig_payment_schedule_id LEFT JOIN fin_payment_scheduledetail psd ON psd.fin_payment_scheduledetail_id = opsd.fin_payment_scheduledetail_id LEFT JOIN fin_payment_detail_v pdv ON pdv.fin_payment_detail_v_id = psd.fin_payment_scheduledetail_id WHERE psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id) AS daysoverdue, COALESCE((SELECT sum(psd.amount) AS sum FROM fin_payment_scheduledetail psd, fin_payment_detail pd LEFT JOIN fin_payment p ON pd.fin_payment_id = p.fin_payment_id WHERE psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id AND psd.fin_payment_detail_id = pd.fin_payment_detail_id AND p.status = 'RPAE'), 0) AS awaiting_execution, to_char('Y') AS em_aprm_modif_paym_sched, to_char('Y') AS em_aprm_modif_paym_out_sched FROM fin_payment_schedule ps]]></view>
   </database>

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0058119) hgbot (developer) 2013-04-23 10:57	Repository: erp/devel/pi Changeset: 191b774fbb863439071eebf0325fcb2fcf2b7a91 Author: David Miguelez <david.miguelez <at> openbravo.com> Date: Mon Apr 22 19:17:06 2013 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/191b774fbb863439071eebf0325fcb2fcf2b7a91 [^] Fixes Issue 23603. In views changes 'N' to TO_CHAR('N') for avoid warnings. --- M modules/org.openbravo.advpaymentmngt/src-db/database/model/views/APRM_FINACC_TRANSACTION_V.xml M src-db/database/model/views/FIN_PAYMENT_SCHED_INV_V.xml ---

(0058156) hudsonbot (viewer) 2013-04-23 21:03	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/f6ee8c2944a0 [^] Maturity status: Test

(0058490) dmiguelez (viewer) 2013-05-07 16:41	Code Review + Testing Ok

Issue History
Date Modified	Username	Field	Change
2013-04-22 09:58	shuehner	New Issue
2013-04-22 09:58	shuehner	Assigned To	=> dmiguelez
2013-04-22 09:58	shuehner	Modules	=> Core
2013-04-22 09:58	shuehner	OBNetwork customer	=> No
2013-04-22 09:58	shuehner	Triggers an Emergency Pack	=> No
2013-04-22 09:59	shuehner	File Added: 23603.diff
2013-04-23 10:57	hgbot	Checkin
2013-04-23 10:57	hgbot	Note Added: 0058119
2013-04-23 10:57	hgbot	Status	new => resolved
2013-04-23 10:57	hgbot	Resolution	open => fixed
2013-04-23 10:57	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/191b774fbb863439071eebf0325fcb2fcf2b7a91 [^]
2013-04-23 21:03	hudsonbot	Checkin
2013-04-23 21:03	hudsonbot	Note Added: 0058156
2013-04-25 14:04	dmiguelez	Review Assigned To	=> shuehner
2013-05-07 16:41	dmiguelez	Note Added: 0058490
2013-05-07 16:41	dmiguelez	Status	resolved => closed