Re: [HACKERS] Issue on C function that reads int2[] (using "int2vector")
2015-11-30 0:39 GMT-02:00 Tom Lane:
> Rodrigo Hjort writes:
> > I created a custom C function with this signature:
>
> > CREATE FUNCTION calculate_hash(numbers int2[])
> > RETURNS int8
> > AS 'MODULE_PATHNAME', 'pg_calculate_hash'
> > LANGUAGE C
> > IMMUTABLE STRICT;
>
> > And here is the function source code (inspired in codes I found in
> > src/backend/utils/adt/int.c):
>
> > PG_FUNCTION_INFO_V1(pg_calculate_hash);
> > Datum
> > pg_calculate_hash(PG_FUNCTION_ARGS)
> > {
> > int2vector *int2Array = (int2vector *) PG_GETARG_POINTER(0);
>
> Nope. int2vector is not the same as int2[]. It might occasionally seem
> to work, but in general it's not the same type. And this particular
> coding won't work at all on on-disk int2[] data, because it doesn't
> account for toasting.
>
> regards, tom lane
>
Thanks for the advice, Tom.
I ended up with the following code, which worked successfully:
#define ARRPTR16(x) ((uint16 *) ARR_DATA_PTR(x))
#define ARRNELEMS(x) ArrayGetNItems(ARR_NDIM(x), ARR_DIMS(x))
#define ARRISEMPTY(x) (ARRNELEMS(x) == 0)
PG_FUNCTION_INFO_V1(pg_calculate_hash);
Datum
pg_calculate_hash(PG_FUNCTION_ARGS)
{
ArrayType *a = PG_GETARG_ARRAYTYPE_P_COPY(0);
unsigned int i, qtd, tipo, nums[MAX_NUMEROS];
uint16 *da;
qtd = ARRNELEMS(a);
tipo = ARR_ELEMTYPE(a);
da = ARRPTR16(a);
elog(DEBUG1, "pg_calculate_hash(qtd=%d, tipo=%d)", qtd, tipo);
[...]
pfree(a);
PG_RETURN_INT64(hash);
}
Regards,
Rodrigo Hjort
[HACKERS] Help on creating C function that reads int2[] (using "int2vector")
Hello PG Hackers,
I created a custom C function with this signature:
CREATE FUNCTION calculate_hash(numbers int2[])
RETURNS int8
AS 'MODULE_PATHNAME', 'pg_calculate_hash'
LANGUAGE C
IMMUTABLE STRICT;
And here is the function source code (inspired in codes I found in
src/backend/utils/adt/int.c):
PG_FUNCTION_INFO_V1(pg_calculate_hash);
Datum
pg_calculate_hash(PG_FUNCTION_ARGS)
{
int2vector *int2Array = (int2vector *) PG_GETARG_POINTER(0);
const int qtd = int2Array->dim1;
elog(DEBUG1, "pg_calculate_hash(qtd=%d)", qtd);
elog(DEBUG2, " [ndim=%d, dataoffset=%d, elemtype=%d, dim1=%d,
lbound1=%d]",
int2Array->ndim, int2Array->dataoffset, int2Array->elemtype,
int2Array->dim1, int2Array->lbound1);
[...]
}
In order to test it against a table structure, I executed these
instructions on psql:
db=# create table ss (s int2[]);
CREATE TABLE
db=# \d+ ss
Table "public.ss"
Column |Type| Modifiers | Storage | Stats target | Description
++---+--+--+-
s | smallint[] | | extended | |
Has OIDs: no
db=# insert into ss values ('[0:5]={58,17,15,36,59,54}');
INSERT 0 1
db=# select * from ss;
s
---
[0:5]={58,17,15,36,59,54}
(1 row)
Then, whenever calling the function passing the int2[] column directly,
strange values are read into the "int2vector" object:
db=# set client_min_messages to debug2;
SET
db=# select s, calculate_hash(s) from ss;
DEBUG: pg_calculate_hash(qtd=0)
DEBUG:[ndim=0, dataoffset=5376, elemtype=1536, dim1=0,
lbound1=285227520]
s | calculate_hash
---+---
[0:5]={58,17,15,36,59,54} | 0
(1 row)
On the other hand, when I double-cast the int2[] column value, it works as
expected (reading the proper "int2vector" structure):
db=# select s, calculate_hash(s::varchar::int2[]) from ss;
DEBUG: pg_calculate_hash(qtd=6)
DEBUG:[ndim=1, dataoffset=0, elemtype=21, dim1=6, lbound1=0]
s | calculate_hash
---+
[0:5]={58,17,15,36,59,54} | 441352797842128896
(1 row)
Please, what is wrong with that function code?
Thanks in advance.
The whole project is on GitHub:
https://github.com/hjort/mega-sena/tree/master/src
Best regards,
--
Rodrigo Hjort
www.hjort.co
[HACKERS] Issue on C function that reads int2[] (using "int2vector")
Hello PG Hackers,
I created a custom C function with this signature:
CREATE FUNCTION calculate_hash(numbers int2[])
RETURNS int8
AS 'MODULE_PATHNAME', 'pg_calculate_hash'
LANGUAGE C
IMMUTABLE STRICT;
And here is the function source code (inspired in codes I found in
src/backend/utils/adt/int.c):
PG_FUNCTION_INFO_V1(pg_calculate_hash);
Datum
pg_calculate_hash(PG_FUNCTION_ARGS)
{
int2vector *int2Array = (int2vector *) PG_GETARG_POINTER(0);
const int qtd = int2Array->dim1;
elog(DEBUG1, "pg_calculate_hash(qtd=%d)", qtd);
elog(DEBUG2, " [ndim=%d, dataoffset=%d, elemtype=%d, dim1=%d,
lbound1=%d]",
int2Array->ndim, int2Array->dataoffset, int2Array->elemtype,
int2Array->dim1, int2Array->lbound1);
[...]
}
In order to test it against a table structure, I executed these
instructions on psql:
db=# create table ss (s int2[]);
CREATE TABLE
db=# \d+ ss
Table "public.ss"
Column |Type| Modifiers | Storage | Stats target | Description
++---+--+--+-
s | smallint[] | | extended | |
Has OIDs: no
db=# insert into ss values ('[0:5]={58,17,15,36,59,54}');
INSERT 0 1
db=# select * from ss;
s
---
[0:5]={58,17,15,36,59,54}
(1 row)
Then, whenever calling the function passing the int2[] column directly,
strange values are read into the "int2vector" object:
db=# set client_min_messages to debug2;
SET
db=# select s, calculate_hash(s) from ss;
DEBUG: pg_calculate_hash(qtd=0)
DEBUG:[ndim=0, dataoffset=5376, elemtype=1536, dim1=0,
lbound1=285227520]
s | calculate_hash
---+---
[0:5]={58,17,15,36,59,54} | 0
(1 row)
On the other hand, when I double-cast the int2[] column value, it works as
expected (reading the proper "int2vector" structure):
db=# select s, calculate_hash(s::varchar::int2[]) from ss;
DEBUG: pg_calculate_hash(qtd=6)
DEBUG:[ndim=1, dataoffset=0, elemtype=21, dim1=6, lbound1=0]
s | calculate_hash
---+
[0:5]={58,17,15,36,59,54} | 441352797842128896
(1 row)
Please, what is wrong with that function code?
Thanks in advance.
The whole project is on GitHub:
https://github.com/hjort/mega-sena/tree/master/src
Best regards,
--
Rodrigo Hjort
www.hjort.co
Re: [HACKERS] Strange behavior on to_tsquery()
2011/11/3 Tom Lane > I wrote: > > (Just offhand, it rather looks like dict_int and dict_xsyn are both > > assuming that palloc will give back zeroed space, which is bogus...) > > Yeah, this is definitely broken. Patches committed; thanks for the > report. > > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e3e3087d8717c26cd1c4581ba29274ac214eb816 > >regards, tom lane > I modified my code by calling *palloc0()* instead and that issue no longer appears. :D Thanks, Tom Lane! -- Rodrigo Hjort www.hjort.co
[HACKERS] Strange behavior on to_tsquery()
Hello PG hackers,
I created a *custom dictionary* (based on dict_int) and a search
configuration and a strange behavior happens on *PostgreSQL 8.4.9*.
When I invoke the following instruction several times,*
to_tsquery()*returns distinct results:
catalog=> SELECT to_tsquery('custom', 'pi');
to_tsquery
'pi':*
(1 registro)
catalog=> SELECT to_tsquery('custom', 'pi');
to_tsquery
'pi'
(1 registro)
Therefore, when I use *@@ operator* over a *tsvector* column in my table
the result set is not always the same.
1) Do you have any clue on what could be happening and how to solve this
issue, please?
2) Sometimes the value returned by *to_tsquery()* has a ":*" suffix. What
does that mean?
Thanks in advance.
Best Regards,
--
Rodrigo Hjort
www.hjort.co
[HACKERS] DBLink's default user: postgres
Is this the regular behavior on DBLink?
rot=> SELECT user, current_database();
current_user | current_database
--+--
sa_rot | rot
(1 registro)
rot=> SELECT *
rot-> FROM dblink('dbname=escola',
rot(> 'SELECT user, current_database()')
rot-> AS (usr name, db name);
usr| db
--+
postgres | escola
(1 registro)
This way, I fear DBLink functions should become a vulnerability issue on my
database.
Is there any way to protect or override this setting? Or it should be done
on pg_hba.conf only?
--
Regards,
Rodrigo Hjort
http://icewall.org/~hjort
[HACKERS] Deferred Constraints
Dear Hackers, Could you point me the first version PostgreSQL started to support Deferred Constraints (ie: DEFERRABLE keyword on foreign keys creation)? I guess it is earlier than 7.0, right? -- Regards, Rodrigo Hjort http://icewall.org/~hjort ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Ontology on PostgreSQL - is there something?
Dear PG-hackers, Based on the paper below [1], I ask: is there anyone working on, or already tried to such native implementation on PostgreSQL? I didn't find anything related on pgFoundry. There is also a presentation [2] related to the paper. "By Souripriya Das, Eugene Inseok Chong, George Eadon, Jagannathan Srinivasan, Proceedings of the 30th VLDB Conference, Toronto, Canada. Ontologies are increasingly being used to build applications that utilize domain-specific knowledge. This paper addresses the problem of supporting ontology-based semantic matching in RDBMS. The approach enables users to reference ontology data directly from SQL using the semantic match operators, thereby opening up possibilities of combining with other operations such as joins as well as making the ontology-driven applications easy to develop and efficient. In contrast, other approaches use RDBMS only for storage of ontologies and querying of ontology data is typically done via APIs. This paper presents the ontology-related functionality including inferencing, discusses how it is implemented on top of Oracle RDBMS, and illustrates the usage with several database applications." [1] http://www.cs.toronto.edu/vldb04/protected/eProceedings/contents/pdf/IND3P1.PDF [2] http://www-scf.usc.edu/~csci586/ppt-2005/dguo.pps Besides, what are your opinions on the subject? Is it worthful to have such native implementation on PG, even as a modest contrib module? Based on your development know-how on the backend internals, how difficult is it to develop this (3 operators + 1 index type)? -- Best regards, Rodrigo Hjort http://icewall.org/~hjort CELEPAR - Cia de Informática do Paraná - Brasil http://www.pr.gov.br
Re: [HACKERS] Connection Broken with Custom Dicts for TSearch2
How about those "pg_ts*" tables, which are specific for a database? Will they serve to the whole cluster?2006/6/7, Teodor Sigaev <[EMAIL PROTECTED] >:OpenFTS never, but tsearch2 is possible. But it requires enough work to do, so I have doubt that it will be done in 8.2...-- Rodrigo Hjorthttp://icewall.org/~hjort
Re: [HACKERS] Connection Broken with Custom Dicts for TSearch2
Oleg, Actually I got PG 8.1.4 compiled from source on a Debian GNU/Linux 2.6.16-k7-2. My locale is pt_BR, but I configured TSearch2 to use rules from the 'simple'. Then I just followed the instructions from the link. The fact is that it only works at the first time. Regards, Rodrigo Hjort http://icewall.org/~hjort 2006/6/2, Oleg Bartunov <oleg@sai.msu.su>: Rodrigo,you gave us too little information. Did you use your own dictionary ?What's your configuration, version, etc.Oleg
[HACKERS] Connection Broken with Custom Dicts for TSearch2
Sorry, but I thought it that was the most appropriate list for the issue.I was following these instructions:http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/custom-dict.html
And what happens is that the function works just once. Perhaps a malloc/free issue?$ psql fuzzyfuzzy=# select to_tsvector('the quick brown fox jumped over the lazy dog 100'); to_tsvector
'dog':9 'fox':4 'jump':5 'lazi':8 'brown':3 'quick':2 'hundred':10(1 registro)fuzzy=# select to_tsvector('the quick brown fox jumped over the lazy dog 100');
server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.A conexão com servidor foi perdida. Tentando reiniciar: Falhou.
!> \qRegards,Rodrigo Hjorthttp://icewall.org/~hjort
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
I think more exactly, the planner can't possibly know how to plan anindexscan with a leading '%', because it has nowhere to start. The fact is that index scan is performed on LIKE _expression_ on a string not preceded by '%', except when bound parameter is used. select * from table where field like 'THE NAME%'; -- index scanselect * from table where field like '%THE NAME%'; -- seq scanselect * from table where field like :bind_param; -- seq scan (always)Regards, Rodrigo Hjort http://icewall.org/~hjort
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
I'm not used to the PG Internals. But let me see if I understood that.The LIKE operator, when applied on a static string and it is not preceded by '%', causes the planner to search for some indexes in the table in order to make a index scan. Otherwise, i.e. using leading '%' on static text or bound paremeter, makes the planner always do a sequential scan. Is that the scenario?-- Rodrigo Hjorthttp://icewall.org/~hjort 2006/5/23, Tom Lane <[EMAIL PROTECTED]>: "Rodrigo Hjort" <[EMAIL PROTECTED]> writes:> What happens is that only the "004" block uses the index! The "002" code,> which also has no leading percent, does a sequential scan. The difference > between them is that "002" uses bind parameters.Yeah. The LIKE index optimization depends on seeing a constant LIKEpattern at plan time --- otherwise the planner doesn't know whatindexscan parameters to generate. So a bound-parameter query loses. Ideas for improving this situation are welcome ... it's not an easyproblem ...regards, tom lane
[HACKERS] LIKE, leading percent, bind parameters and indexes
PG-Hackers,I got the following picture:detran=# \d sa_dut.tb_usuario Table "sa_dut.tb_usuario" Column | Type | Modifiers-+-+---
numprocesso | bigint | not null nome | character varying(44) | nomemae | character varying(44) | datanascimento | date |
Indexes: "tb_usuario_pkey" PRIMARY KEY, btree (numprocesso) "ix_usuario_11" btree (nome varchar_pattern_ops, nomemae varchar_pattern_ops) "ix_usuario_13" btree (datanascimento, nome varchar_pattern_ops)
As I do not use C locale, I created indexes based on "varchar_pattern_ops".The issue I'm having is based on the following queries:select * from TB_USUARIO where nome like 'TATIANA CRISTINA G%';
select * from TB_USUARIO where nome like '%TATIANA CRISTINA G%';For some reasons, I'm not using text-search engines, like TSearch2, but only the LIKE operator.Here are the query plans involved:
detran=# explain analyze select count(*) as x0_0_ from sa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where (usuario1_.NOME like 'TATIANA CRISTINA G%' and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO);
QUERY PLAN Aggregate (cost=11.94..11.95 rows=1 width=0) (actual time=
143.970..143.972 rows=1 loops=1) -> Nested Loop (cost=0.00..11.94 rows=1 width=0) (actual time=143.935..143.949 rows=1 loops=1) -> Index Scan using ix_usuario_11 on tb_usuario usuario1_ (cost=0.00..6.01
rows=1 width=8) (actual time=93.884..93.889 rows=1 loops=1) Index Cond: (((nome)::text ~>=~ 'TATIANA CRISTINA G'::character varying) AND ((nome)::text ~<~ 'TATIANA CRISTINA H'::character varying))
Filter: ((nome)::text ~~ 'TATIANA CRISTINA G%'::text) -> Index Scan using tb_processo_pkey on tb_processo processo0_ (cost=0.00..5.91 rows=1 width=8) (actual time=50.041..50.044 rows=1 loops=1)
Index Cond: (processo0_.numprocesso = "outer".numprocesso) Total runtime: 144.176 msdetran=# explain analyze select count(*) as x0_0_ from sa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where
(usuario1_.NOME like '%TATIANA CRISTINA G%' and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO);QUERY PLAN-
Aggregate (cost=67534.55..67534.56 rows=1 width=0) (actual time=8101.957..8101.959 rows=1 loops=1) -> Nested Loop (cost=0.00..67534.55 rows=1 width=0) (actual time=5404.106..8101.923 rows=1 loops=1) -> Seq Scan on tb_usuario usuario1_ (cost=
0.00..67528.62 rows=1 width=8) (actual time=5404.056..8101.862 rows=1 loops=1) Filter: ((nome)::text ~~ '%TATIANA CRISTINA G%'::text) -> Index Scan using tb_processo_pkey on tb_processo
processo0_ (cost=0.00..5.91 rows=1 width=8) (actual time=0.034..0.037 rows=1 loops=1) Index Cond: (processo0_.numprocesso = "outer".numprocesso) Total runtime: 8102.105 msWe use Java, and recently we made an effort in order to avoid the leading '%' on LIKE expressions.
The problem is that it wasn't solved, and then I made the following Java code to verify it.What happens is that only the "004" block uses the index! The "002" code, which also has no leading percent, does a sequential scan. The difference between them is that "002" uses bind parameters.
Is it concerned to the JDBC Driver or PostgreSQL itself? What could be done in order to fix it?I could use static parameters, but then the queries would have to be reparsed each time on the backend, missing cache advantages.
package db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement
;import java.sql.ResultSet;import java.sql.SQLException;public class SelectLike { public SelectLike() { long qtd = 0L, inicio = 0L, tempo[] = {0,0,0,0}; try {
Class.forName("org.postgresql.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection con = null; String dbURL = "jdbc:postgresql://10.15.61.6/database";
try { con = DriverManager.getConnection(dbURL, "user", "password"); String sql = "select count(*) as x0_0_ fromsa_dut.TB_PROCESSO processo0_, sa_dut.TB_USUARIO usuario1_ where
(usuario1_.NOME like ? and processo0_.NUMPROCESSO=usuario1_.NUMPROCESSO)"; String nome = "TATIANA CRISTINA G"; PreparedStatement ps = null; ResultSet rs = null;
//001 - '%NAME%' binded if (ps != null) ps.close(); ps = con.prepareStatement(sql);
Re: [HACKERS] Issue on Varchar Ordering
Thanks all. I'm gonna try other locales and encodings.2006/3/29, Tom Lane <[EMAIL PROTECTED]>: "Rodrigo Hjort" <[EMAIL PROTECTED]> writes:> The fact is that I can't raise PostgreSQL with C encoding, as special> characters are used on most tables. You're confusing locale and encoding, which are two different (thoughinterrelated) things.I suspect the right choice for you will be to use C locale with UTF8encoding, or possibly one of the LATINn encodings. regards, tom lane-- Regards,Rodrigo HjortGTI - Projeto PostgreSQLCELEPAR - Cia de Informática do Paraná http://www.pr.gov.br
Re: [HACKERS] Issue on Varchar Ordering
Yeah, I think there's a problem on Linux locales.Using the C locale, it works as expected:[EMAIL PROTECTED]:~$ export LC_ALL=C && cat test.txt | sortGABRIEL ALCIDES KLIM PERONDIGABRIEL ALEXANDRE DA SILVA MANICA GABRIELA HELEDA DE SOUZAGABRIELA JACOBY NOSGABRIELA LETICIA BATISTA NUNESBut when using Brazilian Portuguese, it gives:[EMAIL PROTECTED]:~$ export LC_ALL=pt_BR && cat test.txt | sortGABRIELA HELEDA DE SOUZA GABRIELA JACOBY NOSGABRIEL ALCIDES KLIM PERONDIGABRIELA LETICIA BATISTA NUNESGABRIEL ALEXANDRE DA SILVA MANICA[EMAIL PROTECTED]:~$ export LC_ALL=pt_BR.UTF-8 && cat test.txt | sortGABRIELA HELEDA DE SOUZA GABRIELA JACOBY NOSGABRIEL ALCIDES KLIM PERONDIGABRIELA LETICIA BATISTA NUNESGABRIEL ALEXANDRE DA SILVA MANICA[EMAIL PROTECTED]:~$ export LC_ALL=pt_BR.ISO-8859-1 && cat test.txt | sortGABRIELA HELEDA DE SOUZA GABRIELA JACOBY NOSGABRIEL ALCIDES KLIM PERONDIGABRIELA LETICIA BATISTA NUNESGABRIEL ALEXANDRE DA SILVA MANICAThe fact is that I can't raise PostgreSQL with C encoding, as special characters are used on most tables. Did anyone ever had the same problem on Linux?2006/3/29, Jonah H. Harris <[EMAIL PROTECTED]>: I don't think PostgreSQL's sorting it wrong... here's the output ofLinux's sort utility:[EMAIL PROTECTED]:~> cat test.txtGABRIEL ALEXANDRE DA SILVA MANICAGABRIELA LETICIA BATISTA NUNESGABRIEL ALCIDES KLIM PERONDI GABRIELA JACOBY NOSGABRIELA HELEDA DE SOUZA[EMAIL PROTECTED]:~> cat test.txt | sortGABRIELA HELEDA DE SOUZAGABRIELA JACOBY NOSGABRIEL ALCIDES KLIM PERONDIGABRIELA LETICIA BATISTA NUNESGABRIEL ALEXANDRE DA SILVA MANICA [EMAIL PROTECTED]:~> psql -c "SELECT * FROM aluno ORDER BY nome" postgres id | nome+--- 36 | GABRIELA HELEDA DE SOUZA 37 | GABRIELA JACOBY NOS 38 | GABRIEL ALCIDES KLIM PERONDI 39 | GABRIELA LETICIA BATISTA NUNES 40 | GABRIEL ALEXANDRE DA SILVA MANICA(5 rows)--Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation 732.331.1324-- Regards,Rodrigo HjortGTI - Projeto PostgreSQLCELEPAR - Cia de Informática do Paranáhttp://www.pr.gov.br
[HACKERS] Issue on Varchar Ordering
Pg-Hackers,I'm having the following issue:create table aluno (id serial, nome varchar(60));rodrigo=# select * from aluno order by nome offset 35 limit 5; id | nome+--- 36 | GABRIELA HELEDA DE SOUZA 37 | GABRIELA JACOBY NOS 38 | GABRIEL ALCIDES KLIM PERONDI 39 | GABRIELA LETICIA BATISTA NUNES 40 | GABRIEL ALEXANDRE DA SILVA MANICA(5 registros)The problem is that records with id 38 and 40 might come before the others. It seems that the spacing isn't considered on ordering. The encoding (LATIN1) is correct according to Brazilian Portuguese, and the settings are listed below.rodrigo=# select name, setting from pg_settings where name ~ 'encoding|^lc_|version'; name | setting -+- client_encoding | LATIN1 lc_collate | pt_BR lc_ctype | pt_BR lc_messages | pt_BR lc_monetary | pt_BR lc_numeric | pt_BR lc_time | pt_BR server_encoding | LATIN1 server_version | 8.1.3(9 registros)The server is a Debian 3.1 GNU/Linux with locales pt_BR.What could be wrong? Thanks in advance.-- Regards, Rodrigo HjortGTI - Projeto PostgreSQLCELEPAR - Cia de Informática do Paranáhttp://www.pr.gov.br
Re: [HACKERS] Restoring a Full Cluster on a Different Architecture (32 x 64)
Well, actually we're ain't gonna do this procedure regularly, but just in case of failure - if it ever happens.For the moment, I did the dump/restore and it worked, but took almost 1 hour, due to tsearch2 indexes on a table. Yeah, I thought 64-bit data could be stored on other files than pg_control. So, there's only one way.Thanks for helping!2006/3/14, Jim C. Nasby < [EMAIL PROTECTED]>:On Tue, Mar 14, 2006 at 02:12:39PM -0500, Jonah H. Harris wrote: > On 3/14/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:> >> > Setting up Slony might be another option; you'd essentially be following> > the procedure used to speed up a PostgreSQL upgrade that would normally > > require a dump/reload.>>> If you need to do this on a continuing basis, Slony is the best way to go.> If it's a one-time shot, I'd just pipe pg_dump to a psql that's connected to > PostgreSQL on your 32-bit system.Well, it's not so much a matter of how often you have to do it, but whatkind of downtime you can tolerate. Setting up Slony just to move acluster from one machine to another is a good amount of extra work, so if you don't have the uptime requirement it probably doesn't make sense.--Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]Pervasive Software http://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -- Regards,Rodrigo HjortGTI - Projeto PostgreSQLCELEPAR - Cia de Informática do Paranáhttp://www.pr.gov.br
[HACKERS] Restoring a Full Cluster on a Different Architecture (32 x 64)
Dear PostgreSQL Hackers,We got a PG 8.1 on a Debian 64 bits, which does a full backup (PITR) daily.Then we installed a Debian 32 bits (actually, it's on VMWare) and wanted to restore the previous PG cluster on it. As there are a lot of indexes, specially GiST, "pg_dump" and "pg_restore" are not viable - will take a lot of time!Well, the fact is that we've got the message below on "postmaster" start attempt: "WARNING: Calculated CRC checksum does not match value stored in file. Either the file is corrupt, or it has a different layout than this program is expecting. The results below are untrustworthy."As the architecture on both Linuxes are different (32 and 64 bits), I think "PGDATA/global/pg_control" might contains 64 bit data such that the 32 bits binary won't recognize or even mispell it. Am I right? What could be done in order to fix it? Is there any kind of application to translate it or the only solution was to "pg_dumpall" and "pg_restore" the cluster?** [EMAIL PROTECTED]:/tmp/lala/global$ uname -aLinux pga1 2.6.8-2-686 #1 Tue Aug 16 13:22:48 UTC 2005 i686 GNU/Linux[EMAIL PROTECTED]:/tmp/lala/global$ pg_controldata /var/lib/postgresql/8.1/main/WARNING: Calculated CRC checksum does not match value stored in file. Either the file is corrupt, or it has a different layout than this programis expecting. The results below are untrustworthy.pg_control version number: 812Catalog version number: 200510211 Database system identifier: 4883914971069546458Database cluster state: in productionpg_control last modified: Wed 31 Dec 1969 09:00:00 PM BRTCurrent log file ID: 1142136269 Next log file segment: 0Latest checkpoint location: 1/30Prior checkpoint location: 1/2F71B630Latest checkpoint's REDO location: 1/2F71B5E0Latest checkpoint's UNDO location: 1/2F71B630 Latest checkpoint's TimeLineID: 0Latest checkpoint's NextXID: 0Latest checkpoint's NextOID: 1Latest checkpoint's NextMultiXactId: 36239847Latest checkpoint's NextMultiOffset: 1819439 Time of latest checkpoint: Wed 31 Dec 1969 09:00:11 PM BRTMaximum data alignment: 25Database block size: 0Blocks per segment of large relation: 8Bytes per WAL segment: 0 Maximum length of identifiers: 0Maximum columns in an index: 1093850759Date/time type storage: 64-bit integersMaximum length of locale name: 131072LC_COLLATE:LC_CTYPE: **pgsql01:~# uname -aLinux pgsql01 2.6.8-11-em64t-p4-smp #1 SMP Mon Oct 3 00:07:51 CEST 2005 x86_64 GNU/Linux pgsql01:~# /usr/lib/postgresql/8.1/bin/pg_controldata /pg/data/pg_control version number: 812Catalog version number: 200510211Database system identifier: 4883914971069546458 Database cluster state: in productionpg_control last modified: Mon Mar 13 14:19:42 2006Current log file ID: 1Next log file segment: 51Latest checkpoint location: 1/3289F8E0 Prior checkpoint location: 1/32827710Latest checkpoint's REDO location: 1/3289F8E0Latest checkpoint's UNDO location: 0/0Latest checkpoint's TimeLineID: 1Latest checkpoint's NextXID: 37253588 Latest checkpoint's NextOID: 1819439Latest checkpoint's NextMultiXactId: 11Latest checkpoint's NextMultiOffset: 25Time of latest checkpoint: Mon Mar 13 14:19:42 2006Maximum data alignment: 8 Database block size: 8192Blocks per segment of large relation: 131072Bytes per WAL segment: 16777216Maximum length of identifiers: 64Maximum columns in an index: 32 Date/time type storage: 64-bit integersMaximum length of locale name: 128LC_COLLATE: pt_BRLC_CTYPE: pt_BR** Regards,Rodrigo HjortGTI - Projeto PostgreSQLCELEPAR - Cia de Informática do Paranáhttp://www.pr.gov.br
[HACKERS] Server Programming in C: palloc() and pfree()
I'm having some problems when using "pfree()" on functions in C.Calling it on "psql" gives the exception below on both versions of function "insert()" [1,2] if "pfree()" is enabled:
server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.conexão com servidor foi perdida(connection to the server was lost)
The strange is that it doesn't happen with the function "delstr()" [3], which has "pfree()".What could am I doing wrong?// 1)void insert(char *str, const int start, const char *piece)
{ int lstr = strlen(str); int lnew = lstr + strlen(piece) + 1; char* temp = palloc(lnew); memset((void*) temp, 0, lnew); /* FILE *debug; debug = fopen("/dev/xconsole", "w");
fprintf(debug, "insert('%s', %d, '%s')\n", str, start, piece); //fprintf(debug, "0) '%s'\n", temp); */ if (start <= lstr + 1) { strncpy(temp, str, start - 1);
strcat(temp, piece); char* ptr = str + start - 1; strcat(temp, ptr); strcpy(str, temp); }// pfree(temp); // <-- here it doesn't work... /* fprintf(debug, "-> '%s'\n", str);
fflush(debug); fclose(debug); */}// 2)void insert(char *str, const int start, const char *piece){ int i, j; char* temp = palloc(strlen(str) + strlen(piece) + 1); if (start - 1 <= strlen(str))
{ for (i = 0; i < start - 1; i++) temp[i] = str[i]; for (j = i; j < strlen(piece) + i; j++) temp[j] = piece[j - i]; for (; i < strlen(str); i++, j++)
temp[j] = str[i]; temp[j] = '\0'; strcpy(str, temp); }// pfree(temp); // doesn't work...}// 3)void delstr(char *str, const int start, const int size){ int i, j;
char* temp = palloc(strlen(str) - size + 1); for (i = 0; (i < start - 1) && (i < strlen(str)); i++) temp[i] = str[i]; for (j = start + size - 1; j < strlen(str); i++, j++)
temp[i] = str[j]; temp[i] = '\0'; strcpy(str, temp); pfree(temp); // <-- here it works!}--Regards,Rodrigo HjortGTI - Projeto PostgreSQL
CELEPAR - Cia de Informática do Paraná
http://www.pr.gov.br
