[GENERAL] Bucardo syncrun
Hi, Can I purge data on Postgrebucardo table syncrun ? If I will follow this steps: bucardo stop psql bucardo truncate bucardo.syncrun ; \q bucardo start The steps to purge all past data that table. Is correct? Is this Simple method? -- *Atenciosamente,Emanuel Araújo* *Linux Certified, DBA PostgreSQL*
[GENERAL] Triggers Operations
Hi, I have an application that replicates data from an Oracle database for postgresql. The flow goes as follows: oracle.table1 -> AppSincronizador -> postgresql.table1 -> Trigger (upd, ins, del) -> postgresql.table2 I'm having situations where the data volume is large that the changes that should be in the final table are not found, getting the tables in postgresql nosync. Well, the application makes a single transaction and makes commits every 1000 records. It is as if the triggers disabled, when manually do the operation is performed. Is there a BUG or situation where the postgresql disable these triggers? So Version: CentOS 6.5 PostgreSQL 9.3.5 Oracle: 11G I found this POST that explain once situation. AFTER triggers are more expensive than BEFORE triggers because They must be queued up Until the statement finishes doing its work, Then executed. They are not spilled to disk if the queue gets big (at least in 9.4 and below, may change in future) are huge queues AFTER trigger can cause memory available to overrun, Resulting in the statement aborting. Link: http://dba.stackexchange.com/questions/88761/scaling-postgresql-triggers PS. Right now I'm not interested in the performance, as this take care later, but the question that there are random operations that do not complete for the final table. Thanks! -- *Atenciosamente,Emanuel Araújo* *Linux Certified, DBA PostgreSQL*
[GENERAL] Extract especific text from a sql statement
Hi, I need help to extract fields and tables from a sql statement. Example: SELECT pc.cod, pc.val, pi.qtd, COALESCE(pc.name, 'empty') AS name, lower(coalesce(pc.email, 'empty')) as email, status FROM pc INNER JOIN pi on (pc.cod = pi.cod) WHERE pc.startdate > CURRENT_DATE order by 1 desc ; I need to return that: pc.cod pc.val pi.qtd pc.name pc.email status pc pi pc.startdate can you help me ? -- *Atenciosamente,Emanuel Araújo*
Re: [GENERAL] How to clone CURRENT_DATE to SYSDATE ?
Thank's Adrian, I want really create another CURRENT_DATE called SYSDATE. postgres=# SELECT CURRENT_DATE ; date 2014-09-25 (1 row) I need that: postgres=# SELECT SYSDATE ; date 2014-09-25 Because, I am trying SymmetricDS between Oracle and PostgreSQL, in my case, there are a lot of fields with "DEFAULT trunc(sysdate)". This situation break when I start the sincronization why the data type there isn't in PostgreSQL. 2014-09-24 16:43 GMT-03:00 Adrian Klaver : > On 09/24/2014 07:39 AM, Emanuel Araújo wrote: > >> Hi, >> >> I need to clone function CURRENT_DATE to SYSDATE in my PostgreSQL. >> >> Does anybody know how to do that it ? >> > > Not sure what you want? > > A clone is an exact replica so cloning CURRENT_DATE would create another > CURRENT_DATE. My guess is that this not what you want. > > So do you want to create SYSDATE in Postgres? > > If so, look at this thread for the issues: > > http://www.postgresql.org/message-id/1409288790481- > 5816851.p...@n5.nabble.com > > >> >> -- >> *Atenciosamente, >> >> Emanuel Araújo* >> */Linux Certified, DBA PostgreSQL >> /* >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Atenciosamente,Emanuel Araújo* *Linux Certified, DBA PostgreSQL*
[GENERAL] How to clone CURRENT_DATE to SYSDATE ?
Hi, I need to clone function CURRENT_DATE to SYSDATE in my PostgreSQL. Does anybody know how to do that it ? -- *Atenciosamente,Emanuel Araújo* *Linux Certified, DBA PostgreSQL*
[GENERAL] Oracle_FDW - Cache lookup failed
Hi,
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
I am executing the simple query in most foreign tables..
Example wher tables with f_ are foreign tables:
SELECT 0 AS u_pkey,
f_vendedor.u_orgvendaAS u_orgvenda,
f_vendedor.s_codvendedor AS s_codvendedor,
grupos.s_codgrupoAS s_codgrupo,
f_produto.s_codproduto
FROM f_vendedor
JOIN (SELECT 'D'AS s_tipogrupo,
codepto::text AS s_codgrupo,
codusur::text AS s_codvendedor
FROM erp.pcusurdepsec
UNION
SELECT DISTINCT
'D'AS s_tipogrupo,
codepto::text AS s_codgrupo,
codusur::text AS s_codvendedor
FROM f_vendedor CROSS JOIN (SELECT DISTINCT codepto FROM
f_produto) f_produto
UNION ALL
SELECT DISTINCT
'S'AS s_tipogrupo,
codsec::text AS s_codgrupo,
codusur::text AS s_codvendedor
FROM erp.pcusurdepsec
UNION
SELECT DISTINCT
'S'AS s_tipogrupo,
codsec::text AS s_codgrupo,
codusur::text AS s_codvendedor
FROM f_vendedor CROSS JOIN (SELECT DISTINCT codsec FROM f_produto)
f_produto
UNION ALL
SELECT 'F' AS s_tipogrupo,
codfornec::text AS s_codgrupo,
codusur::text AS s_codvendedor
FROM erp.pcusurfornec
UNION
SELECT DISTINCT
'F' AS s_tipogrupo,
codfornec::text AS s_codgrupo,
codusur::textAS s_codvendedor
FROM f_vendedor CROSS JOIN (SELECT DISTINCT codfornec FROM
f_produto) f_produto
UNION ALL
SELECT DISTINCT
'I'::textAS s_tipogrupo,
pcdistrib.coddistrib AS s_codgrupo,
f_vendedor.s_codvendedor AS s_codvendedor
FROM erp.pcdistrib
CROSS JOIN f_vendedor
) grupos ON (f_vendedor.s_codvendedor = grupos.s_codvendedor)
INNER JOIN t_vendedor ON (t_vendedor.s_codigo = f_vendedor.s_codvendedor
AND t_vendedor.u_bloqueado = 0)
INNER JOIN f_produto ON ( grupos.s_codgrupo = (CASE WHEN (SELECT s_valor
FROM t_parametro_cliente WHERE s_campo = 'v_utiliza_grupo') = 'D' THEN
f_produto.codepto::text
WHEN (SELECT s_valor
FROM t_parametro_cliente WHERE s_campo = 'v_utiliza_grupo') = 'F' THEN
f_produto.codfornec::text
WHEN (SELECT s_valor
FROM t_parametro_cliente WHERE s_campo = 'v_utiliza_grupo') = 'S' THEN
f_produto.codsec::text
WHEN (SELECT s_valor
FROM t_parametro_cliente WHERE s_campo = 'v_utiliza_grupo') = 'I' THEN
f_produto.coddistrib::text
END) )
WHERE grupos.s_tipogrupo = (SELECT s_valor
FROM t_parametro_cliente
WHERE s_campo = 'v_utiliza_grupo')
;
--
*Atenciosamente,Emanuel Araújo*
*Linux Certified, DBA PostgreSQL*
[GENERAL] Acess Oracle with dbi-link (PostgreSQL) Error Compile
Hello! I'm having trouble making a base to access Oracle via dbi-link, because when installing DBD::Oracle version 1.58 the same mistakes some missing files. Ago as "oci.h", it is being called within the oracle.h The purpose would be to sync data between two tools for integration. Has anyone experienced this? Have any solution or suggestion? There is another tool that I could be using to make this access? The following error returned by Perl when you squeegee "make" make gcc-c-D_REENTRANT-D_GNU_SOURCE -I/root/perl5/lib/perl5/x86_64-linux-thread-multi/auto/DBI-fno-strict- aliasing-pipe-fstack-protector -I/usr/local/include - D_LARGEFILE_SOURCE- D_FILE_OFFSET_BITS = 64-g-O2-pipe-Wall-Wp,-D_FORTIFY_SOURCE = 2-fexceptions- fstack-protector - param = ssp-buffer-size = 4-m64-mtune = generic-DVERSION = \ "1:58 \ "-DXS_VERSION = \" 1:58 \ "-fPIC" -I/usr/lib64/perl5/CORE " -Wall-Won-comment-DUTF8_SUPPORT-DORA_OCI_VERSION = \" 10.2.0.3 \ "- DORA_OCI_102 Oracle.c In file included from Oracle.xs: 1:0: Oracle.h: 37:17: fatal error: oci.h: File or directory not found compilation terminated. make: ** [Oracle.o] Error 1 Thank you. -- *Atenciosamente, Emanuel Araújo* http://eacshm.wordpress.com/ http://www.rootserv.com.br/ * **Linux Certified LPIC-1*
[GENERAL] Problemas com client_encoding ?
Srs. realizei uma instalação de um de nossos servers e me deparei com a seguinte mensagem [user@local ~]$ psql psql: invalid connection option "client_encoding" Depois de procurar sobre o assunto, setei uma variavel chamada PGCLIENTENCODING = UTF8 e isso resolveu meu problema, no entanto, ao instalar um outro server com os mesmos procedimentos, essa variável não foi exigida. O que posso ter errado? 1o. Server SO - Centos 5.7 Final PostgreSQL 9.1.1 Depois atualizado para 9.2.1 e o problema continua... 2o. Server SO - Centos 5.7 Final PostgreSQL 9.1.2 Não houve esse tipo de problema Todas as instalações que fazemos aqui, seguimos os pacotes oficiais do repositorio pgdg e o processo de instalação igual em qualquer situação. Alguém pode me dar um HELP ? -- *Atenciosamente, Emanuel Araújo* http://eacshm.wordpress.com/ * * *Linux Certified LPIC-1*
[GENERAL] connection manager pgpool... help
srs, need help. I have several applications accessing my databases. My customers are divided into databases within my cluster pg905, have at least a 60/90 clients per cluster, heavy number of updates in the bank, as inserts, updates and deletes and an average 50 to 100 simultaneous connections all the time. I plan on using a connection pool (pgpool), thinking to improve performance and have better management of the connections. Based on my scenario, can anyone help me? I'm using postgresql version 9.0.5 thank you -- *Atenciosamente, Emanuel Araújo* http://eacshm.wordpress.com/ * * *Linux Certified LPIC-1*
Re: [GENERAL] Problem dbi_link with postgresql 9.04
hi, news! found that the problem occurs when the dbi_link makes parsing of a field float / double to a text field, because when it creates the tables already created with this type of data. Another thing we see is that the problem is not with null fields or zero bytes but with a value of 1.5, no problem in NULLs or Zero Bytes. Based on that there is any solution?
[GENERAL] Problem dbi_link with postgresql 9.04
Hi,
In one of our applications, we use the dbi_link for communication with a
firebird db, works very well in version 8.3 we have one of our PostgreSQL
server (CentOS 5.3). We are doing tests for migration to version 9.4
or 9.1, and
the use of tests dbi_link got the following errors:
dbi_fortes = # SELECT "NAME" FROM ag. "CLI";
WARNING: SELECT dbi_link.cache_connection (1) at line 12.
CONTEXT: PL / Perl function "remote_select"
ERROR: invalid byte sequence for encoding "LATIN1": 0x00 at line 198.
CONTEXT: PL / Perl function "remote_select"
Originally the db was SQL_ASCII but was migrated to use LATIN1, and the same
problem occurs when using the original encoding (SQL_ASCII).
Using the query to collect just one of the linked table fields, "dbi_fortes =
# SELECT * FROM dbi_link.remote_select (1, 'SELECT NAME FROM CLI':: text)
remote_select (" NAME "text) LIMIT 10;" it returns without no problem.
We think the field of this table that is causing the error, and it
contains NULL
values.
Using "isql" I can usually return the data.
questions:
1. which may have changed from version 8.3/8.4 (works well) to version
9.* which
can cause this kind of incompatibility?
2. does anyone know of any bug dbi_link about it?
3. Is there any other tool similar to dbi_link use?
4. Something else that can help me about it?
Thanks.
Digite um texto ou endereço de um site ou traduza um
documento.<http://translate.google.com.br/?tr=f&hl=pt-BR>
Cancelar <http://translate.google.com.br/?tr=t&hl=pt-BR>
Tradução do português para inglês
--
*Atenciosamente,
Emanuel Araújo*
http://eacshm.wordpress.com/
*
*
*Linux Certified
LPIC-1*
[GENERAL] Pgpool outdated spec
Hi, I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your spec is outdated, there is some spec updated or have to make my changes manually? -- * Emanuel Araújo* http://eacshm.wordpress.com/ * * *Linux Certified LPIC-1*
