[ADMIN] How to disconnect a user?
I'm wondering how to cleanly disconnect a user from the DB. For the moment, the only way I found is to kill the pid of the user but I found it quite ugly. -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] pg_dump failed
hello, i've got the following problem: OS - Solaris 8/9 on Sun SPARC PGSQL 7.1.3 when i try to dump the database with pg_dump command i've got the following error: -- last builtin oid is 18539 -- reading user-defined types -- reading user-defined functions -- reading user-defined aggregates -- reading user-defined operators -- reading user-defined tables getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. '. the same error appears when a try to display the database schema using \d in psql shell e.g. $ psql -U user db psql# \d getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. '. but when a type: psql# \d table_name everything works fine ... what's wrong ? pg_dump is really really really important for me ... thanx in advance ... -- Milan Krajnak senior software architect e-mail: [EMAIL PROTECTED] phone: +421 2 50267546 fax : +421 2 50267500 Ability Development SK http://www.abilitydev.com smime.p7s Description: S/MIME Cryptographic Signature
[ADMIN] ODBC Driver generates a too big "windows swap file" and it's too slow
Hi List,
First of all, I tried to subcribe the ODBC list but it seems that the
subscription's link is broken ! So here it goes:
I have a delphi software use ttable components that converts dbf information
to PostgreSQL an Oracle Databases. My problem is that PostgreSQL is too slow,
the oracle db makes this convertion in 3.45 min and the Pg db makes int 29 min.
The software is the same ( only the database reference is diferent ) , this
sotware uses BDE to access the database with oracle native driver and using
postgreSQL odbc driver version 5. Both databases are in the same machine (
Pentium 4 1.8Ghz, 384MB RAM DDR ) running RH 9 , Oracle 9i and PostgreSQL 7.3.2-
3.
When I ran this conversion I "snorted" the communication between the server
and the station to see how it does the sql requests , here it goes:
ORACLE :
- select owner, object_name, object_type, created from sys.all_objects where
object_type in ('TABLE', 'VIEW' ) and owner = 'VENDAS' and object_name
= 'FTCOFI00' order by 1 ASC, 2 ASC
- select owner, index_name, uniqueness from sys.all_indexes where table_owner
= 'VENDAS' and table_name = 'FTCOFI00' order by owner ASC, index_name ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCOFI01' order by column_position ASC
-
SELECT "EMP" ,"FIL" ,"CODIGO_FISCAL" ,"CODIGO_FISCAL_ESTORNO" ,"DESCRICAO_FISCAL
" ,"CODIGO_OPERACIONAL" ,"DESCRICAO_USUARIO" ,"COD_NATIPI" ,"COD_NATIBGE" ,"EXTO
_NF1" ,"TEXTO_NF2" ,"NF_NORMALDIF" ,"NF_TRANSFILIAL" ,"COD_FILIAL" ,"COD_LANCTO_
FILIAL" ,"NF_EXPORTACAO_DIRETA" ,"NF_EXPORTACAO_INDIRETA" ,"NF_SIMPREMESSA" ,"NF
_DEVOLUCAO" ,"NF_ENTRADA" ,"NF_REPOSICAO" ,"NF_OUTRASERIE" ,"NF_CONSIGNACAO" ,"N
F_PRODGRATIS" ,"NF_FATURANTECIP" ,"NF_DIFBASEICM" ,"NF_DIF_VALORICM" ,"NF_DIFBAS
EIPI" ,"NF_DIFVALORIPI" ,"NF_DIFPRECO" ,"BLOQ_CREDITO" ,"LIBERA_CREDITO" ,"VER_P
ARAM_VENDAS" ,"ENTRA_COBRANCA" ,"BASECALC_VLRBRUTO" ,"DESCNF_REFICM" ,"ALIQICM_I
GUALEST" ,"COD_TRIBICM" ,"COD_TRIBIPI" ,"ATUAL_ESTOQUE" ,"ATUAL_FABRICACAO" ,"AT
UAL_FATURA" ,"ATUAL_OUTENTR" ,"ATUAL_OUTSAIDA" ,"ATUAL_TRANFIL" ,"ATUAL_SEMIACAB
" ,"ATUAL_CARTPED" ,"ATUAL_ENTRSAID" ,"REV_CUSTMEDIO" ,"DIGITAR_FISICO" ,"DIGITA
R_FINANCEIRO" ,"USAR_CUSTO_CMU_INFORMAR" ,"GRUPO_FATURAMENTO" ,"TIPO_NF" ,"RESUM
O_FISCAL_CODIGO" ,
"ATUAL_DISTRIB" ,"IMPR_OBS_NF_REG_ES" ,"DIFE_RECEITA" ,"COD_LANCTO" ,"SITUACAO"
FROM "FTCOFI00" ORDER BY "EMP" ASC , "FIL" ASC , "CODIGO_FISCAL" ASC
- select owner, object_name, object_type, created from sys.all_objects where
object_type in ('TABLE', 'VIEW') and owner = 'VENDAS' and object_name
= 'FTCLCR00' order by 1 ASC, 2 ASC
- select owner, index_name, uniqueness from sys.all_indexes where table_owner
= 'VENDAS' and table_name = 'FTCLCR00' order by owner ASC, index_name ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR01' order by column_position ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR02' order by column_position ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR03' order by column_position ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR04' order by column_position ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR05' order by column_position ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR06' order by column_position ASC
- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR07' order by column_position ASC
-
SELECT "EMP" ,"FIL" ,"TIPO_CADASTRO" ,"CODIGO" ,"RAZAO_SOCIAL" ,"NOME_FANTASIA"
,"EMP_ENDERECO" ,"EMP_NRO" ,"EMP_COMPLEMENTO" ,"EMP_BAIRRO" ,"EMP_CIDADE" ,"EMP_
ESTADO" ,"EMP_CEP" ,"EMP_PAIS" ,"EMP_EAN" ,"COB_ENDERECO" ,"COB_NRO" ,"COB_COMPL
EMENTO" ,"COB_BAIRRO" ,"COB_CIDADE" ,"COB_ESTADO" ,"COB_CEP" ,"COB_PAIS" ,"COB_E
AN" ,"ENT_ENDERECO" ,"ENT_NRO" ,"ENT_COMPLEMENTO" ,"ENT_BAIRRO" ,"ENT_CIDADE" ,"
ENT_ESTADO",
"ENT_CEP" ,"ENT_PAIS" ,"ENT_EAN" ,"LOJA_EAN" ,"TELEFONE" ,"CELULAR" ,"FAX" ,"EMA
IL" ,"SITE" ,"CONTATO_NOME" ,"CONTATO_TELEFONE" ,"CONTATO_EMAIL" ,"CONTATO_DDMM_
ANIV" ,"SITUACAO_CADASTRO" ,"OBSERVACOES" ,"DATA_CADASTRO" ,"DATA_ALTERACAO" ,"T
IPO_CONTRIBUINTE" ,"CODIGO_CONTRIBUINTE" ,"TIPO_INSCRICAO" ,"CODIGO_INSCRICAO","
CODIGO_REDE" ,"CODIGO_TIPO_CLIENTE" ,"CODIGO_GRUPO_CLIENTE" ,"CODIGO_SUFRAMA" ,"
DATA_VALIDADE_SUFRAMA" ,"LIMITE_CREDITO" ,
"MARCA" ,"CLASSE" ,"BANDEIRA_CLIENTE" ,"CODIGO_TIPO_CREDOR" ,"NOME_REPRESENTANTE
" ,"TIPO_CONDICAO_PGTO" ,"PRAZO_PGTO_01" ,"PRAZO_PGTO_02" ,"PRAZO_PGTO_03" ,"COD
IGO_MOEDA_COMPRA" ,"FATOR_QUALIDADE" ,"DESPESA_FINANCEIRA" ,"CODIGO_DARF" ,"CODI
GO_NATUREZA_RENDIMENTO" ,"CONTA_CORRENTE_BANCO" ,"CONTA_CORRENTE_AGENCIA" ,"CONT
A_CORRENTE_NUMERO" ,"FORNECEDOR_SULPLASTIC" ,"SUFRAM
[ADMIN] Reindex database
Any rule of thumb for REINDEX DATABASE? Once per month? Per x transactions? -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Cranel. Technology. Integrity. Focus. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] pg_dump failed
Milan Krajnak <[EMAIL PROTECTED]> writes: > psql# \d > getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- > backend > closed the channel unexpectedly. Standard questions: what shows up in the postmaster's log (stderr or syslog depending on your setup) when this happens? Is there a core file dropped in the database subdirectory ($PGDATA/base/something/)? If so, please get a debugger backtrace from it. I get the impression that there may be corrupt data in your pg_class file --- can you do "select * from pg_class" without a crash? How about pg_user and pg_rewrite? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Tables Without OIDS and its effect
Dear all , I have created my tables without OIDS now my doubts are : 1. Will this speed up the data insertion process 2. Though I have not written any code in my any of the pgsql functions which depend on OIDS 1. Will without OIDS the functions behave internally differently 2. Will my application break at any point 3. I decided to work with out OIDS because 1. It has a limit of -2147483648 to +2147483647 2 Due to this limitation I would not like to drop recreate my database because it is a bit difficult/dirty process All links and suggestion pertaining to OIDS are most welcome my mail box is at your disposal and dont hassitate to drop a two line comment. --- My Sys Config: RH9.0 PostgreSQL 7.3.4 GCC 3.2.2 PHP 4.3.4 -- Regards, V Kashyap ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Reindex database
On Fri, 12 Dec 2003, Greg Spiegelberg wrote: > Any rule of thumb for REINDEX DATABASE? Once per month? > Per x transactions? You shouldn't need to reindex databases for the most part. If there's lots of catalog updates / delete, it might be occasionally useful to down the database and reindex those catalogs in single user mode since you can't reindex them on a live database if I remember correctly. If you are running 7.3.5 or before, you might need to schedule daily / weekly / monthly etc reindexes of indexes that show unreasonable growth problems. This should be mostly fixed for 7.4, and reindexes should not really be needed anymore, we hope :-). generally speaking, reindex is for fixing broken indexes, not generaly maintenance. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] PostgreSQL features
Hy, I'm a newbie and i'm looking for several informations of PostgreSQL to help me for the choice of my next database. - What's the number max of keys ? - Is thre a max lenght for the name of database, table, index, column ? - Is it possible to make partions of a table ?and how ? Thank's E-mail, Dialogue en direct et SMS gratuits sur minitel : 3615 CARAMAIL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Help---- Urgent
Create a new column alter add column varchar(25); Then copy the old column into the new column: update set = ; Drop the last column: alter drop column ; Rename the column: alter rename column to ; Take care for INDEXES if there are any. This should do it. Best regards. - Original Message - From: "Chitta Ranjan Mishra" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 11, 2003 7:19 AM Subject: [ADMIN] Help Urgent > Dear Sir, > I wnat to alter the size of one of the column of a > table. How to do this in Postgres ? Plz help me. > It's very urgent... > > I tried with the following syntax : > but it failed > > alter table tablename modify(coumnname varchar(25)); > > it's very urgnet...plz reply soon.. > > Thanking you, > Regds > C.R.Mishra > > > > > > Yahoo! India Mobile: Download the latest polyphonic ringtones. > Go to http://in.mobile.yahoo.com > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] pg 7.4 on debian
[EMAIL PROTECTED] (Oliver Elphick) wrote: > Peter Eisentraut has copied the binary packages to: > ftp://ftp.postgresql.org/pub/binary/v7.4/debian/ > > These are the md5sums of the woody release of 7.4: (...) Thank you for making it availiable again, Peter and Oliver! I assume the correct line for /etc/apt/sources.list is: deb ftp://ftp.postgresql.org/pub/binary/v7.4/debian/ main non-free contrib I will try to install it tomorrow and report back. Regards Erwin Brandstetter ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] PostgreSQL features
On Thu, Dec 11, 2003 at 16:27:25 +0100, arnaud Betoule <[EMAIL PROTECTED]> wrote: > Hy, > I'm a newbie and i'm looking for several informations of PostgreSQL to help me for > the choice of my next > database. > - What's the number max of keys ? By default the limit is 32 keys on a single multicolumn index. You can recompile the code with a larger limit if you want. I don't think there is a limit on the number of indexes on a single table. > - Is thre a max lenght for the name of database, table, index, column ? The default maximum is 63 characters. By recompiling you can use a larger value. Howeber I think you need to make sure all of the tools that anyone uses must also be built this way, so it isn't a change to make lightly. > - Is it possible to make partions of a table ?and how ? No. However you can use partial indexes to solve some of the problems you might try to solve by partitioning tables. > Thank's > > E-mail, Dialogue en direct et SMS gratuits sur minitel : 3615 CARAMAIL > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] pg 7.4 on debian
Hi NG! Getting *apt-get update* to work with the above mentioned package turned out to be tricky. What I supplied in my last posting was incorrect of course. The line in sources.list needs a distribution after the URI. Failed: deb ftp://ftp.postgresql.org/pub/binary/v7.4/debian/ main non-free contrib But the following line did not work either, because then apt-get searched in .../debian/dists/woody/..., but the link is in fact ../debian/woody/.. (no "dists" in the path). Failed: deb ftp://ftp.postgresql.org/pub/binary/v7.4/debian/ woody main non- free contrib Finally I succeeded using this line. Distribution ends with a slash (/) and components are omitted. The paths to /contrib and /non-free turned out to be empty anyway. Success: deb ftp://ftp.postgresql.org/pub/binary/v7.4/debian/ woody/main/binary- i386/ The whole procedure took hours because between failures I had to try many times to connect to ftp.postgrsql.org. "530 The maximum number of concurrent connections has been reached" .. And the mirror sites do not provide the files (at least not ). Finally even the provider of my news-server would not let me in to report back here."400 too many users".. Duh! So all I achieved this afternoon was to finally make *apt-get update* read Packages.gz. I hope the update itself will go smoothly. Regards Erwin Brandstetter -- No z in my email. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Tables Without OIDS and its effect
Dear all , I have created my tables without OIDS now my doubts are : 1. Will this speed up the data insertion process 2. Though I have not written any code in my any of the pgsql functions which depend on OIDS 1. Will without OIDS the functions behave internally differently 2. Will my application break at any point 3. I decided to work with out OIDS because 1. It has a limit of -2147483648 to +2147483647 2 Due to this limitation I would not like to drop recreate my database because it is a bit difficult/dirty process All links and suggestion pertaining to OIDS are most welcome my mail box is at your disposal and dont hassitate to drop a two line comment. --- My Sys Config: RH9.0 PostgreSQL 7.3.4 GCC 3.2.2 PHP 4.3.4 -- Regards, V Kashyap ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] ODBC Driver generates a too big "windows swap file" and
On Fri, 12 Dec 2003, Rhaoni Chiu Pereira wrote: Hi, is there a switch in your pgsql/odbc connector to enable cursors? If so, try turning that on. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [PERFORM] Tables Without OIDS and its effect
Sai Hertz And Control Systems <[EMAIL PROTECTED]> writes: > I have created my tables without OIDS now my doubts are : > 1. Will this speed up the data insertion process Slightly. It means that each inserted row will be 4 bytes smaller (on disk), which in turn means you can fit more tuples on a page, and therefore you'll need fewer pages and less disk space. However, I'd be surprised if the performance improvement is very significant. > 2. Though I have not written any code in my any of the pgsql functions > which depend on OIDS > 1. Will without OIDS the functions behave internally differently > 2. Will my application break at any point No. BTW, we intend to phase out the use of OIDs for user tables in the long term. There have been a few threads on -hackers that discuss the plans for doing this. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
