[ADMIN][GENERAL] openvz and shared memory trouble
Hi, I have a problem with postgres in combination with openvz. My hot standby crashes on me when i give it a normal value for shared_buffers (4GB, which 25% of the memory in the container). When i tune down the shared_buffers, it works again. But of course this is far from desireable. Now, i've seen a couple more messages around the net about openVZ and postgres that weren't very encouraging: http://www.postgresql.org/message-id/CAHyXU0xa5EgvjeH=4vp-ezdjds5kmquidivvtrljy-uz62y...@mail.gmail.com http://www.postgresql.org/message-id/23206.1254683...@sss.pgh.pa.us http://postgresql.1045698.n5.nabble.com/BUG-9721-Fatal-error-on-startup-no-free-slots-in-PMChildFlags-array-td5797390.html But i am in a position where we already have a pretty strong binding with openVZ. I also have a colleague that has been using openVZ and postgres on production for quite a while now, without any problems. And already 1 of our servers is running a production database in openVZ that is well used, without any trouble of this kind. Now, what i would like to know is: - is there clarity about what the issues are at all - is it just 1 problem (in openVZ) that make openVZ and postgres so poorly compatible? - how can i stay out of trouble (when using postgres inside openVZ) - who else has trouble with openVZ in combination with postgres, and what constitutes the trouble? regards, WBL -- Quality comes from focus and clarity of purpose -- Mark Shuttleworth
Re: [GENERAL] Oracle_FDW - Cache lookup failed
Emanuel Araújo wrote: I have a problem wiht Oracle FDW 0.9.10 in PostgreSQL 9.3 I am using pg 9.3.4 Oracle FDW 0.9.10 Fedora 20 Client Oracle Release 11.2.0.3.0 Return Error: ERROR: cache lookup failed for type 0 This is a known bug, the fix is already committed: https://github.com/laurenz/oracle_fdw/commit/9e0a421b16e542c5caf9734a923e87f050cdbefd I plan to release a new version soon since this has already hit several people. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Hi Chris or anyone who can help When I try this just below, it complains about foo (new table created in db2) , saying *ERROR: schema foo does not exist*. I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; *From:* Khangelani Gama [mailto:kg...@argility.com kg...@argility.com] *Sent:* Friday, March 28, 2014 7:52 AM *To:* 'ch...@chriscurvey.com'; 'pgsql' *Subject:* RE: [GENERAL] Synchronizing a table that is two different databases : Need to dump a table a insert from db1 and change the insert statements into UPDATE statements Thank you very much, I will try option 2 because option 1 will give me FK constrains. These two databases are not exactly similar, but few tables on each of these Databases needs to be exactly the same, in this case db1 is the reliable one, hence that's why I need to update db2 with what it's in db1. *From:* ccur...@gmail.com [mailto:ccur...@gmail.com ccur...@gmail.com] *On Behalf Of *Chris Curvey *Sent:* Thursday, March 27, 2014 3:08 PM *To:* Khangelani Gama; pgsql *Subject:* Re: [GENERAL] Synchronizing a table that is two different databases : Need to dump a table a insert from db1 and change the insert statements into UPDATE statement (For clarity, I'm going to call the table in question foo.) Option 1: If you just want to do a full copy of db1.foo to db2.foo, then do that. Dump foo from db1, truncate foo in db2 and load db2.foo from the dump. But you might not be able to do that (foreign keys and such). In that case.. Option 2: Dump the table from db1, then load it into a table with a different name (bar) in db2. Then you can do this: begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; insert into foo (br_cde, br_desc, br_active, ...) select br_cde, br_desc, br_active, from bar where not exists ( select * from foo where foo.br_cde = bar.br_cde); commit; CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
On 28 March 2014 11:07, Khangelani Gama kg...@argility.com wrote: Hi Chris or anyone who can help When I try this just below, it complains about foo (new table created in db2) , saying ERROR: schema foo does not exist. I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; Really? I would have expected ERROR: table or schema bar does not exist. Look up the syntax for UPDATE FROM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
On Fri, Mar 28, 2014 at 5:56 AM, Khangelani Gama kg...@argility.com wrote: Hi Chris or anyone who can help When I try this just below, it complains about foo , saying ERROR: schema foo does not exist. I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; you need to replace foo and bar with the names of your tables.
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Hi Chris I did replace foo and bar tables with my tables, in db2 I have table foo(the one to be updated) and the table bar(the one which has data I got from db1) *From:* ccur...@gmail.com [mailto:ccur...@gmail.com] *On Behalf Of *Chris Curvey *Sent:* Friday, March 28, 2014 1:50 PM *To:* Khangelani Gama *Cc:* pgsql *Subject:* Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements On Fri, Mar 28, 2014 at 5:56 AM, Khangelani Gama kg...@argility.com wrote: Hi Chris or anyone who can help When I try this just below, it complains about foo , saying ERROR: schema foo does not exist. I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; you need to replace foo and bar with the names of your tables. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Not sure, but maybe update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where br_cde = bar.br_cde; Anyway it seem sto be terribly bad idea to give those kind of names ! Cheers, Rémi-C
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
I think the convention on this list is to bottom-post. On Fri, Mar 28, 2014 at 7:59 AM, Khangelani Gama kg...@argility.com wrote: Hi Chris I did replace foo and bar tables with my tables, in db2 I have table foo(the one to be updated) and the table bar(the one which has data I got from db1) *From:* ccur...@gmail.com [mailto:ccur...@gmail.com] *On Behalf Of *Chris Curvey *Sent:* Friday, March 28, 2014 1:50 PM *To:* Khangelani Gama *Cc:* pgsql *Subject:* Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements On Fri, Mar 28, 2014 at 5:56 AM, Khangelani Gama kg...@argility.com wrote: Hi Chris or anyone who can help When I try this just below, it complains about foo , saying ERROR: schema foo does not exist. I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; you need to replace foo and bar with the names of your tables. I'm stumped. Here's the test that I just ran on both 9.2 and 8.4, and I'm not getting an error: drop table if exists foo; drop table if exists bar; create table foo (a int, b int); create table bar (a int, b int); insert into foo values (1, 1); insert into foo values (2, 2); insert into bar values (1, 3); insert into bar values (4, 4); update foo set b = bar.b from bar where foo.a = bar.a; select * from bar; Perhaps if you want to privately send me the exact statement you are using, I can take quick look and see if there's something obvious.
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Thank you very much, I on my previous try I did not include the FROM. I will just do thoroughly checks before COMMIT. Thanks very much. *From:* ccur...@gmail.com [mailto:ccur...@gmail.com] *On Behalf Of *Chris Curvey *Sent:* Friday, March 28, 2014 2:38 PM *To:* Khangelani Gama *Cc:* pgsql *Subject:* Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements I think the convention on this list is to bottom-post. On Fri, Mar 28, 2014 at 7:59 AM, Khangelani Gama kg...@argility.com wrote: Hi Chris I did replace foo and bar tables with my tables, in db2 I have table foo(the one to be updated) and the table bar(the one which has data I got from db1) *From:* ccur...@gmail.com [mailto:ccur...@gmail.com] *On Behalf Of *Chris Curvey *Sent:* Friday, March 28, 2014 1:50 PM *To:* Khangelani Gama *Cc:* pgsql *Subject:* Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements On Fri, Mar 28, 2014 at 5:56 AM, Khangelani Gama kg...@argility.com wrote: Hi Chris or anyone who can help When I try this just below, it complains about foo , saying ERROR: schema foo does not exist. I got stuck on this error for a while now but still trying to see why but still no luck so far. If you have something please help begin; update foo set br_desc = bar.br_desc , br_active = bar.br_active (rest of columns) where foo.br_cde = bar.br_cde; you need to replace foo and bar with the names of your tables. I'm stumped. Here's the test that I just ran on both 9.2 and 8.4, and I'm not getting an error: drop table if exists foo; drop table if exists bar; create table foo (a int, b int); create table bar (a int, b int); insert into foo values (1, 1); insert into foo values (2, 2); insert into bar values (1, 3); insert into bar values (4, 4); update foo set b = bar.b from bar where foo.a = bar.a; select * from bar; Perhaps if you want to privately send me the exact statement you are using, I can take quick look and see if there's something obvious. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] is there a way to dump the version of extensions
On Thu, Mar 27, 2014 at 7:11 PM, Manuel Kniep man...@adjust.com wrote: Hi, when doing a pg_dump on postgres 9.2 the resulting sql file only has CREATE EXTENSION extension_name; I would like to include the specific version of the extension to make sure that the correct version is installed when loading the dump like CREATE EXTENSION extension_name VERSION vesion; Is there a way to do that? With pg_dump only, no. It only dumps CREATE EXTENSION with the extension name and the schema on which it is created to ensure that the latest version available with server is created. You could always correct by hand the dump file created though, just be sure that the version of the extension you are compiling is compatible with the server you are going to load the dump to. Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN][GENERAL] openvz and shared memory trouble
On 03/28/2014 01:51 AM, Willy-Bas Loos wrote: Hi, I have a problem with postgres in combination with openvz. My hot standby crashes on me when i give it a normal value for shared_buffers (4GB, which 25% of the memory in the container). When i tune down the shared_buffers, it works again. But of course this is far from desireable. What is the error that shows up in the logs when the 'machine' crashes? 25% is a suggested value, not an absolute value. Desirable would seem to be a value that works for your situation and maintains performance. Is there any indication that running with a lower value adversely affects performance? Now, i've seen a couple more messages around the net about openVZ and postgres that weren't very encouraging: http://www.postgresql.org/message-id/CAHyXU0xa5EgvjeH=4vp-ezdjds5kmquidivvtrljy-uz62y...@mail.gmail.com http://www.postgresql.org/message-id/23206.1254683...@sss.pgh.pa.us http://postgresql.1045698.n5.nabble.com/BUG-9721-Fatal-error-on-startup-no-free-slots-in-PMChildFlags-array-td5797390.html But i am in a position where we already have a pretty strong binding with openVZ. I also have a colleague that has been using openVZ and postgres on production for quite a while now, without any problems. And already 1 of our servers is running a production database in openVZ that is well used, without any trouble of this kind. Now, what i would like to know is: * is there clarity about what the issues are at all * is it just 1 problem (in openVZ) that make openVZ and postgres so poorly compatible? * how can i stay out of trouble (when using postgres inside openVZ) * who else has trouble with openVZ in combination with postgres, and what constitutes the trouble? A quick search found this: http://forum.openvz.org/index.php?t=msggoto=12061srch=postgresql+shared#msg_12061 and http://openvz.org/FAQ#User_Beancounters_.28UBC.29 regards, WBL -- Quality comes from focus and clarity of purpose -- Mark Shuttleworth -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Hi Chris or anyone who can help
When I try this just below, it complains about foo , saying ERROR: schema
foo does not exist. I got stuck on this error for a while now but still
trying to see why but still no luck so far. If you have something please
help
begin;
update foo
set br_desc = bar.br_desc
, br_active = bar.br_active
(rest of columns)
where foo.br_cde = bar.br_cde;
databases has got:
SHOW search_path;
search_path
$user,public
(1 row)
*From:* Khangelani Gama [mailto:kg...@argility.com kg...@argility.com]
*Sent:* Friday, March 28, 2014 7:52 AM
*To:* 'ch...@chriscurvey.com'; 'pgsql'
*Subject:* RE: [GENERAL] Synchronizing a table that is two different
databases : Need to dump a table a insert from db1 and change the insert
statements into UPDATE statements
Thank you very much, I will try option 2 because option 1 will give me FK
constrains. These two databases are not exactly similar, but few tables on
each of these Databases needs to be exactly the same, in this case db1 is
the reliable one, hence that's why I need to update db2 with what it's in
db1.
*From:* ccur...@gmail.com [mailto:ccur...@gmail.com ccur...@gmail.com] *On
Behalf Of *Chris Curvey
*Sent:* Thursday, March 27, 2014 3:08 PM
*To:* Khangelani Gama; pgsql
*Subject:* Re: [GENERAL] Synchronizing a table that is two different
databases : Need to dump a table a insert from db1 and change the insert
statements into UPDATE statements
On Thu, Mar 27, 2014 at 6:16 AM, Khangelani Gama kg...@argility.com wrote:
Hi all
Synchronizing a *table* that is in two different databases(e.g *db1 and db2*).
Please help me with this. I need to dump a table as INSERTS from db1
(postgres 9.1.2) and change the INSERT statements into UPDATE statements in
order to apply that change in *db2(postgres 8.3.0.112)* which has the same
*table* as *db1*. Where the record does not exist I need to insert that
record. There is more than 1000 INSERTS I need to convert to UPDATES for
the same table. Please help .
*Example:*
Below it's the INSERT from postgres 9.1.2 DATABASE which should convert to
UPDATE statement.
INSERT INTO* table* (br_cde, br_desc, br_active, br_closed, grp_cde,
ctry_cde, state_cde, br_addr_line1, br_addr_line2, br_addr_line3,
br_addr_line4, br_addr_cde, br_post_addr_line1, br_post_addr_line2,
br_post_addr_line3, br_post_addr_line4, br_post_addr_cde, br_phone,
tax_cde, br_server_name, br_lang_ind, bureau_cde, br_risk_typ_cde,
br_access_ccd, br_access_bureau, br_network_active, br_desc_short,
br_is_trading, br_is_test, br_is_nomodem, br_is_whs, br_is_merch,
br_is_cco, br_is_central, br_is_merch_active, br_central_brn, br_merch_brn,
br_cco_brn, br_cgo_brn, br_cluster_brn, br_contact, br_tollfree, br_fax,
br_telex, br_email, ops_cde, ops_director, br_cmpy_reg, br_tax_reg,
audit_id, comp_cde, br_batch, br_release_pilot_type, br_is_order_active,
bnk_id, br_class_cde, br_erp_xref, br_to_open, br_to_close, br_comments,
br_is_hire, br_is_voucher, br_is_clear_center, br_is_headoffice,
br_is_nsp_active, usr_pass_history, br_network_protocol,
br_gen_ins_central_brn, br_cpi_ins_central_brn, br_rsc_area, br_rsc_perc,
br_switch_deduc, br_tvlic_dealer_no, br_cco_followup, br_triad_live,
br_do_cust_locking, br_is_rainbow, br_nca_live, br_ncr_reg_no, prov_cde,
br_is_data_archive, br_icu_brn, br_living_expenses_live, br_ab_dorder_live,
br_ab_receipts_active, br_spiv_max_alloc_perc, br_merger_brn,
br_dep_bnk_acc, br_merchant_number, br_goods_ins_live, br_cgs_connection,
whs_cde, br_crm_brn, subscription_active, br_prn_doc_default_active,
br_use_jms, br_sso_active, br_paym8_properties, creditors_supp_no_prefix,
br_block_debtors_obo, ccms_branch, br_is_ccms_live, ceres_conv_flag,
cims_branch, br_is_cims_live, br_accept_sql_releases) VALUES ('9940',
'ELLERINES CENTRAL - 9940', true, NULL, '1', 'BAF', 'BAF', '209 SMIT
STREET', 'BRA', 'Jgf', 'Jrg', '3025', '209 SMIT STREET', 'BRA', 'Jrg',
NULL, '2017', '(4562) 712 1300', '1', 'dfgike.com', 'ENG', 'ITC', 'L',
false, false, false, 'BATCH - 9940', false, false, false, false, false,
false, true, false, '9940', NULL, NULL, '10.52.1.31', '9940', 'DOUG', NULL,
'(4562) 712 1300' ', NULL, NULL, '001', NULL, '1969/02687/07', NULL, 0,
NULL, '9940', NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, false,
false, false, false, false, 30, 'WS2~WS2', '1002', '1002', NULL, NULL,
false, NULL, NULL, false, true, false, true, 'NC', NULL, true, NULL, true,
true, NULL, NULL, NULL, NULL, NULL, true, 'oracle_live', NULL, NULL, true,
NULL, true, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false);
(For clarity, I'm going to call the table in question foo.)
Option 1: If you just want to do a full copy of db1.foo to db2.foo, then
do that. Dump foo from db1, truncate foo in db2 and load db2.foo from the
dump. But you might not be able to do that (foreign keys and such). In
that case..
Option 2: Dump the table from db1, then load it into a table
[GENERAL] The result of the last function call overwrites the result of previous function calls
Hello PostgreSQL community!
I'm a big fan of PostgreSQL, but am new to the development side of things, so I
apologise in advance if this email is not directed to the correct location.
I have been working on an extension to PostgreSQL server to provide JSON
support. This JSON support is different from that in provided in PostgreSQL 9.x
- it doesn't use the new operators, it has different functions, it works on
PostgreSQL 7.x, etc, all of which were requirements before I started this
project.
One item of functionality is the ability to navigate through a JSON object (as
a text field) using an informal JSON path. For example, given the JSON string:
{foods:[Cheese,Ham,Potato]}
...you can find the second food with the following JSON path string
$.foods[1]
This functionality works. However, if you have two calls to this one function
in the same statement returning a single row, the result of the last call
overwrites the result of the previous calls.
select json_path_f(str, '$.foods[0]') as a, json_path_f(str, '$.foods[1]') as b
from (select cast('{foods:[Cheese,Ham,Potato]}' as text) as str) xx
The above will produce two columns a and b, but both will contain the value
'Ham' whereas in reality, column a should contain the value 'Cheese'.
Note that I know the function works, because the C plugin outputs the following
messages for the above SQL statement (printed via the SPI elog(NOTICE)
function). This happens immediately prior to the PG_RETURN_TEXT() macro.
NOTICE: json_path_f: Result as json string: Cheese
NOTICE: json_path_f: Result as json string: Ham
Note, however, that things work as expected when the function returns multiple
rows.
create table ztmp.json_path_test (path text);
insert into ztmp.json_path_test values ('$.foods[ 2 ]'), ('$.foods[0]');
select json_path_f('{foods:[Cheese,Ham,Potato]}', path) from
ztmp.json_path_test
My question is: Can someone please enlighten me as to why the result of the
last function call overwrites the results to the previous function calls?
Michael Ainsworth
ABRI, University of New England
ARMIDALE, NSW 2351
Ph : (02) 6773 3775
This message contains confidential information and is intended only for the
individual named.
Do not disseminate, distribute or copy this e-mail without authorisation.
Please notify
the sender immediately by e-mail if you have received this e-mail by mistake
and delete
this e-mail from your system. E-mail transmission cannot be guaranteed to be
secure or
error-free as information could be intercepted, corrupted, lost, destroyed,
arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any errors
or omissions in the contents of this message, which arise as a result of e-mail
transmission.
If verification is required please request a hard-copy version.
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
Hi Chris or anyone who can help
When I try this just below, it complains about foo , saying ERROR: schema
foo does not exist. I got stuck on this error for a while now but still
trying to see why but still no luck so far. If you have something please
help
begin;
update foo
set br_desc = bar.br_desc
, br_active = bar.br_active
(rest of columns)
where foo.br_cde = bar.br_cde;
databases has got:
SHOW search_path;
search_path
$user,public
(1 row)
*From:* Khangelani Gama [mailto:kg...@argility.com]
*Sent:* Friday, March 28, 2014 7:52 AM
*To:* 'ch...@chriscurvey.com'; 'pgsql'
*Subject:* RE: [GENERAL] Synchronizing a table that is two different
databases : Need to dump a table a insert from db1 and change the insert
statements into UPDATE statements
Thank you very much, I will try option 2 because option 1 will give me FK
constrains. These two databases are not exactly similar, but few tables on
each of these Databases needs to be exactly the same, in this case db1 is
the reliable one, hence that's why I need to update db2 with what it's in
db1.
*From:* ccur...@gmail.com [mailto:ccur...@gmail.com ccur...@gmail.com] *On
Behalf Of *Chris Curvey
*Sent:* Thursday, March 27, 2014 3:08 PM
*To:* Khangelani Gama; pgsql
*Subject:* Re: [GENERAL] Synchronizing a table that is two different
databases : Need to dump a table a insert from db1 and change the insert
statements into UPDATE statements
On Thu, Mar 27, 2014 at 6:16 AM, Khangelani Gama kg...@argility.com wrote:
Hi all
Synchronizing a *table* that is in two different databases(e.g *db1 and db2*).
Please help me with this. I need to dump a table as INSERTS from db1
(postgres 9.1.2) and change the INSERT statements into UPDATE statements in
order to apply that change in *db2(postgres 8.3.0.112)* which has the same
*table* as *db1*. Where the record does not exist I need to insert that
record. There is more than 1000 INSERTS I need to convert to UPDATES for
the same table. Please help .
*Example:*
Below it's the INSERT from postgres 9.1.2 DATABASE which should convert to
UPDATE statement.
INSERT INTO* table* (br_cde, br_desc, br_active, br_closed, grp_cde,
ctry_cde, state_cde, br_addr_line1, br_addr_line2, br_addr_line3,
br_addr_line4, br_addr_cde, br_post_addr_line1, br_post_addr_line2,
br_post_addr_line3, br_post_addr_line4, br_post_addr_cde, br_phone,
tax_cde, br_server_name, br_lang_ind, bureau_cde, br_risk_typ_cde,
br_access_ccd, br_access_bureau, br_network_active, br_desc_short,
br_is_trading, br_is_test, br_is_nomodem, br_is_whs, br_is_merch,
br_is_cco, br_is_central, br_is_merch_active, br_central_brn, br_merch_brn,
br_cco_brn, br_cgo_brn, br_cluster_brn, br_contact, br_tollfree, br_fax,
br_telex, br_email, ops_cde, ops_director, br_cmpy_reg, br_tax_reg,
audit_id, comp_cde, br_batch, br_release_pilot_type, br_is_order_active,
bnk_id, br_class_cde, br_erp_xref, br_to_open, br_to_close, br_comments,
br_is_hire, br_is_voucher, br_is_clear_center, br_is_headoffice,
br_is_nsp_active, usr_pass_history, br_network_protocol,
br_gen_ins_central_brn, br_cpi_ins_central_brn, br_rsc_area, br_rsc_perc,
br_switch_deduc, br_tvlic_dealer_no, br_cco_followup, br_triad_live,
br_do_cust_locking, br_is_rainbow, br_nca_live, br_ncr_reg_no, prov_cde,
br_is_data_archive, br_icu_brn, br_living_expenses_live, br_ab_dorder_live,
br_ab_receipts_active, br_spiv_max_alloc_perc, br_merger_brn,
br_dep_bnk_acc, br_merchant_number, br_goods_ins_live, br_cgs_connection,
whs_cde, br_crm_brn, subscription_active, br_prn_doc_default_active,
br_use_jms, br_sso_active, br_paym8_properties, creditors_supp_no_prefix,
br_block_debtors_obo, ccms_branch, br_is_ccms_live, ceres_conv_flag,
cims_branch, br_is_cims_live, br_accept_sql_releases) VALUES ('9940',
'ELLERINES CENTRAL - 9940', true, NULL, '1', 'BAF', 'BAF', '209 SMIT
STREET', 'BRA', 'Jgf', 'Jrg', '3025', '209 SMIT STREET', 'BRA', 'Jrg',
NULL, '2017', '(4562) 712 1300', '1', 'dfgike.com', 'ENG', 'ITC', 'L',
false, false, false, 'BATCH - 9940', false, false, false, false, false,
false, true, false, '9940', NULL, NULL, '10.52.1.31', '9940', 'DOUG', NULL,
'(4562) 712 1300' ', NULL, NULL, '001', NULL, '1969/02687/07', NULL, 0,
NULL, '9940', NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, false,
false, false, false, false, 30, 'WS2~WS2', '1002', '1002', NULL, NULL,
false, NULL, NULL, false, true, false, true, 'NC', NULL, true, NULL, true,
true, NULL, NULL, NULL, NULL, NULL, true, 'oracle_live', NULL, NULL, true,
NULL, true, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, false, false);
(For clarity, I'm going to call the table in question foo.)
Option 1: If you just want to do a full copy of db1.foo to db2.foo, then
do that. Dump foo from db1, truncate foo in db2 and load db2.foo from the
dump. But you might not be able to do that (foreign keys and such). In
that case..
Option 2: Dump the table from db1, then load it into a table with a
different name
Re: [GENERAL] Help with exclusion constraint
On Fri, Mar 28, 2014 at 12:45 PM, hari.fu...@gmail.com wrote: I want to ensure that for any given value of fk_col that there is a maximum of one row with bool_col = true. This should be what you want: ALTER TABLE exclusion_example ADD CONSTRAINT ex EXCLUDE (fk_col WITH =) WHERE (bool_col); Yes, that would do the trick! I think I'll just set up a partial unique index as per Igor's suggestion, however. Thank you! Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements
have you tried
update foo
set br_desc = bar.br_desc
, br_active = bar.br_active
(rest of columns)
from bar
where foo.br_cde = bar.br_cde;
From: Khangelani Gama
Sent: Friday, March 28, 2014 9:59 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Synchronizing a table that is in two different databases
: Need to dump a table as inserts from db1 and change the insert statements
into UPDATE statements
Hi Chris or anyone who can help
When I try this just below, it complains about foo , saying “ERROR: schema
“foo” does not exist.” I got stuck on this error for a while now but still
trying to see why but still no luck so far. If you have something please help
begin;
update foo
set br_desc = bar.br_desc
, br_active = bar.br_active
(rest of columns)
where foo.br_cde = bar.br_cde;
databases has got:
SHOW search_path;
search_path
$user,public
(1 row)
From: Khangelani Gama [mailto:kg...@argility.com]
Sent: Friday, March 28, 2014 7:52 AM
To: 'ch...@chriscurvey.com'; 'pgsql'
Subject: RE: [GENERAL] Synchronizing a table that is two different databases :
Need to dump a table a insert from db1 and change the insert statements into
UPDATE statements
Thank you very much, I will try option 2 because option 1 will give me FK
constrains. These two databases are not exactly similar, but few tables on each
of these Databases needs to be exactly the same, in this case db1 is the
reliable one, hence that’s why I need to update db2 with what it’s in db1.
From: ccur...@gmail.com [mailto:ccur...@gmail.com] On Behalf Of Chris Curvey
Sent: Thursday, March 27, 2014 3:08 PM
To: Khangelani Gama; pgsql
Subject: Re: [GENERAL] Synchronizing a table that is two different databases :
Need to dump a table a insert from db1 and change the insert statements into
UPDATE statements
On Thu, Mar 27, 2014 at 6:16 AM, Khangelani Gama kg...@argility.com wrote:
Hi all
Synchronizing a table that is in two different databases(e.g db1 and db2).
Please help me with this. I need to dump a table as INSERTS from db1 (postgres
9.1.2) and change the INSERT statements into UPDATE statements in order to
apply that change in db2(postgres 8.3.0.112) which has the same table as db1.
Where the record does not exist I need to insert that record. There is more
than 1000 INSERTS I need to convert to UPDATES for the same table. Please help
.
Example:
Below it’s the INSERT from postgres 9.1.2 DATABASE which should convert to
UPDATE statement.
INSERT INTO table (br_cde, br_desc, br_active, br_closed, grp_cde, ctry_cde,
state_cde, br_addr_line1, br_addr_line2, br_addr_line3, br_addr_line4,
br_addr_cde, br_post_addr_line1, br_post_addr_line2, br_post_addr_line3,
br_post_addr_line4, br_post_addr_cde, br_phone, tax_cde, br_server_name,
br_lang_ind, bureau_cde, br_risk_typ_cde, br_access_ccd, br_access_bureau,
br_network_active, br_desc_short, br_is_trading, br_is_test, br_is_nomodem,
br_is_whs, br_is_merch, br_is_cco, br_is_central, br_is_merch_active,
br_central_brn, br_merch_brn, br_cco_brn, br_cgo_brn, br_cluster_brn,
br_contact, br_tollfree, br_fax, br_telex, br_email, ops_cde, ops_director,
br_cmpy_reg, br_tax_reg, audit_id, comp_cde, br_batch, br_release_pilot_type,
br_is_order_active, bnk_id, br_class_cde, br_erp_xref, br_to_open, br_to_close,
br_comments, br_is_hire, br_is_voucher, br_is_clear_center, br_is_headoffice,
br_is_nsp_active, usr_pass_history, br_network_protocol,
br_gen_ins_central_brn, br_cpi_ins_central_brn, br_rsc_area, br_rsc_perc,
br_switch_deduc, br_tvlic_dealer_no, br_cco_followup, br_triad_live,
br_do_cust_locking, br_is_rainbow, br_nca_live, br_ncr_reg_no, prov_cde,
br_is_data_archive, br_icu_brn, br_living_expenses_live, br_ab_dorder_live,
br_ab_receipts_active, br_spiv_max_alloc_perc, br_merger_brn, br_dep_bnk_acc,
br_merchant_number, br_goods_ins_live, br_cgs_connection, whs_cde, br_crm_brn,
subscription_active, br_prn_doc_default_active, br_use_jms, br_sso_active,
br_paym8_properties, creditors_supp_no_prefix, br_block_debtors_obo,
ccms_branch, br_is_ccms_live, ceres_conv_flag, cims_branch, br_is_cims_live,
br_accept_sql_releases) VALUES ('9940', 'ELLERINES CENTRAL - 9940', true, NULL,
'1', 'BAF', 'BAF', '209 SMIT STREET', 'BRA', 'Jgf', 'Jrg', '3025', '209 SMIT
STREET', 'BRA', 'Jrg', NULL, '2017', '(4562) 712 1300', '1', 'dfgike.com',
'ENG', 'ITC', 'L', false, false, false, 'BATCH - 9940', false, false, false,
false, false, false, true, false, '9940', NULL, NULL, '10.52.1.31', '9940',
'DOUG', NULL, '(4562) 712 1300' ‘, NULL, NULL, '001', NULL, '1969/02687/07',
NULL, 0, NULL, '9940', NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, false,
false, false, false, false, 30, 'WS2~WS2', '1002', '1002', NULL, NULL, false,
NULL, NULL, false, true, false, true, 'NC', NULL, true, NULL, true, true, NULL,
NULL, NULL, NULL, NULL, true, 'oracle_live', NULL, NULL, true, NULL, true,
false, NULL, NULL,
Re: [GENERAL] Help with exclusion constraint
Moshe Jacobson mo...@neadwerx.com writes: Take the following table: CREATE TABLE exclusion_example AS ( pk_col integer primary key, fk_col integer not null references other_table, bool_col boolean not null ); I want to ensure that for any given value of fk_col that there is a maximum of one row with bool_col = true. This should be what you want: ALTER TABLE exclusion_example ADD CONSTRAINT ex EXCLUDE (fk_col WITH =) WHERE (bool_col); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with exclusion constraint
On Fri, Mar 28, 2014 at 12:21 PM, Igor Neyman iney...@perceptron.comwrote: For this: any given value of fk_col that there is a maximum of one row with bool_col = true. why don't you (instead) create partial unique index: CREATE UNIQUE INDEX on exclusion_example(fk_col, bool_col) WHERE bool_col IS TRUE; Ahh yes, why didn't I think of that? Thank you. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
[GENERAL] upgrading from debian 6 to 7--do in place or wipe-and-install?
We're looking at upgrading our database hosts running postgres 9.2 from debian 6/squeeze to debian 7/wheezy. It seems to me that using apt to upgrade in-place would be less work than wiping the boxes and reinstalling debian 7 from scratch, but the latter way would be cleaner. Does anybody have any experience with upgrading in-place? Are there any recommendations for one or the other?
[GENERAL] Help with exclusion constraint
Take the following table: CREATE TABLE exclusion_example AS ( pk_col integer primary key, fk_col integer not null references other_table, bool_col boolean not null ); I want to ensure that for any given value of fk_col that there is a maximum of one row with bool_col = true. I wanted to write an exclusion constraint such as this: alter table exclusion_example add exclude using btree ( fk_col with = , bool_col with and ); But this doesn't work because and is not an operator. So I created my own operator (bool, bool): create function fn_boolean_and( bool, bool ) returns bool as $_$ select $1 and $2; $_$ language sql stable strict; create operator ( procedure = fn_boolean_and(bool, bool), leftarg = bool, rightarg = bool, commutator = ); But now when I try to create the exclusion constraint, I get the following: mydb=# alter table exclusion_example add exclude using btree ( fk_col with = , bool_col with and ); ERROR: operator (boolean,boolean) is not a member of operator family bool_ops DETAIL: The exclusion operator must be related to the index operator class for the constraint. I'm not sure what to do about this. Any help would be appreciated. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] The result of the last function call overwrites the result of previous function calls
Michael Ainsworth michael.ainswo...@abri.une.edu.au writes: My question is: Can someone please enlighten me as to why the result of the last function call overwrites the results to the previous function calls? Sounds to me like your function is using a static area for its result, or something along that line. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dead rows not getting removed during vacuum
Thank you Tom. We will be upgrading soon. Regards, Granthana On Mon, Mar 24, 2014 at 7:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Granthana Biswas granth...@zedo.com writes: Version is PostgreSQL 9.1.4. You do realize you're missing almost two years' worth of bug fixes? The current release in that branch is 9.1.13, and a quick look through the git history shows quite a number of replication-related fixes. One that seems particularly notable in this connection is: commit 16222f32ed56d3ebc4136133662d932299188955 Author: Simon Riggs si...@2ndquadrant.com Date: Thu Jun 7 19:24:47 2012 +0100 Wake WALSender to reduce data loss at failover for async commit. WALSender now woken up after each background flush by WALwriter, avoiding multi-second replication delay for an all-async commit workload. Replication delay reduced from 7s with default settings to 200ms, allowing significantly reduced data loss at failover. Andres Freund and Simon Riggs You wouldn't happen to be running with synchronous_commit off, would you? Whether this is the explanation for your problem or not, it's really irresponsible to still be running 9.1.4 at this point. There are several known data-loss-inducing bugs in it that will eat your data sooner or later. regards, tom lane
Re: [GENERAL] Help with exclusion constraint
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Moshe Jacobson Sent: Friday, March 28, 2014 10:31 AM To: pgsql-general Subject: [GENERAL] Help with exclusion constraint Take the following table: CREATE TABLE exclusion_example AS ( pk_col integer primary key, fk_col integer not null references other_table, bool_col boolean not null ); I want to ensure that for any given value of fk_col that there is a maximum of one row with bool_col = true. I wanted to write an exclusion constraint such as this: alter table exclusion_example add exclude using btree ( fk_col with = , bool_col with and ); .. .. .. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. 2323 Cumberland Parkway * Suite 201 * Atlanta, GA 30339 Quality is not an act, it is a habit. - Aristotle For this: any given value of fk_col that there is a maximum of one row with bool_col = true. why don't you (instead) create partial unique index: CREATE UNIQUE INDEX on exclusion_example(fk_col, bool_col) WHERE bool_col IS TRUE; Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] what is sublinks in query planner?
Hi, I looked for description of sublinks in the code as well as online but failed to come with any information. Was wondering someone can provide a line or two of what sublinks are suppose to do? Thanks. Sandeep
Re: [GENERAL] what is sublinks in query planner?
Sandeep Gupta gupta.sand...@gmail.com writes: I looked for description of sublinks in the code as well as online but failed to come with any information. Was wondering someone can provide a line or two of what sublinks are suppose to do? They're sub-SELECTs used in expression contexts (ie, not directly in a FROM list). The comments for the SubLink and SubPlan node types in primnodes.h may help you. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what is sublinks in query planner?
Hi Tom, Sure. Thanks for the info. This helps. I will look into primnodes.h as well. -Sandeep On Fri, Mar 28, 2014 at 3:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Sandeep Gupta gupta.sand...@gmail.com writes: I looked for description of sublinks in the code as well as online but failed to come with any information. Was wondering someone can provide a line or two of what sublinks are suppose to do? They're sub-SELECTs used in expression contexts (ie, not directly in a FROM list). The comments for the SubLink and SubPlan node types in primnodes.h may help you. regards, tom lane
Re: [GENERAL] PG choosing nested loop for set membership?
On Tue, Mar 25, 2014 at 2:00 PM, Brian Crowell br...@fluggo.com wrote: Hello, it's me, a Postgres n00b again. I'm dealing with a query that scans a rather large table (94,000,000 tuples or so) and just picks out certain rows and sums them: select dci.snapshot_time as time, round(sum(dci.todays_pl)::numeric,0) as pl from dbo._pl_data_cache_intraday dci where dci.snapshot_time between '2014-03-25 11:32:40.004552-05'::timestamptz and '2014-03-25 12:02:40.015177-05'::timestamptz and dci.symbol in (select sec.symbol from dbo.security_underliers sec where sec.ultimate_underlier = 'SPY') and dci.manager = 'BJC' and dci.account in (select account from pl2.visible_accounts where is_fund) group by dci.snapshot_time order by dci.snapshot_time; For the most part, Postgres is doing the right thing: snapshot_time is the lead column in all of the table's indexes, so it's able to pick up the source rows fairly quickly in its index scan. It's also enforcing dci.manager = 'BJC' in the same scan, and does a Hash Semi Join for dci.symbol in (...). The trouble comes when enforcing the dci.account in (...) search condition: pl2.visible_accounts is a view that determines which accounts the current user can see, which, depending on who you are, can be several hundred or none at all. Postgres estimates the output of this query as two rows, but in my case, it's actually 240. Unfortunately, that leads the query planner to try to think a nested loop is cheap enough to enforce this, when actually it's really expensive. Can you show the explain plan for that? I can't get it to use anything but a hash join for this type of thing even when the estimated rows in the in-list are 2, unless I disable hash joins altogether. So I'm curious how your plan differs from the ones I've dummied up. Cheers, Jeff
Re: [GENERAL] EBCDIC conversion
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 28/03/14 00:31, David W Noon wrote:
I think the real issue is that you need icu or libiconv installed.
You will also need to know which EBCDIC code page you want: U.S.
EBCDIC is cp37, IIRC.
I suspect you will need to write a C or Python function that
performs a test conversion, using icu or libiconv, and return a
Boolean result. It should not be too difficult if you can write C
or Python.
Since I had a few minutes tonight, I created a quick hack in Python.
It should be attached to this message, unless the list server removes it.
Here is a sample usage in psql:
dwn=# \i ./check_code_page.sql
CREATE FUNCTION
dwn=# select check_code_page('abcxyz', 'cp037');
check_code_page
- -
t
(1 row)
dwn=# select check_code_page('abcä', 'cp037');
check_code_page
- -
f
(1 row)
You'll note that the a-umlaut character fails in the conversion to
cp037 (U.S. EBCDIC).
- --
Regards,
Dave [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
david.w.n...@googlemail.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.22 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iEYEARECAAYFAlM2EKAACgkQogYgcI4W/5QgYACcDRjE4Ov9GomHX/6RWP+vJnsG
7Y8AoK3K7IbHVLmAmBV8prdvNy9sJ3Pm
=04Rb
-END PGP SIGNATURE-
check_code_page.sql
Description: application/sql
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
