[GENERAL] RE: [GENERAL] Strange Connection Problem….
Jerry LeVan wrote: I recently modified one of my Fedora boxes by changing it's name and ip. I also disabled the internal wifi ( connection speed was dropping to 1 mb/sec ) and configured a USB wifi stick ( wow 270~300 mb/sec ). As I checked out the refurbed box networking was ok and I was able to connect to Postgresql using pgsql and some of my personal apps. However I could not connect to Postgresql from my other machines. I tried ssh from another machine to the modified machine and of course ssh complained about have a bad key ( had renamed the machine to a machine that I had given away recently and the key to the old machine was still present.) After I fixed the ssh problem I *was* able to connect to Postgresql on the refurbed machine. Do the postgresql libraries silently check to see if there is a ssh 'footprint' available for a target machine and reject the connection attempt if they do not match? PostgreSQL does nothing of that sort, but it uses OpenSSL for SSL. So if OpenSSL (which is also used by OpenSSH) refuses the connection, that will affect PostgreSQL. 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] Database schema
Dhiraj Gupta wrote: I have created a database name 'ofbiz. then the default schema name public created automatically. I want to create schema name ofbiz in the database ofbiz when I create database name ofbiz then the schema name ofbiz will create automatically. how it is possible if yes, There is no way to automatically create a schema when you create a database. Connect to the newly created database and create it with CREATE SCHEMA. You can DROP SCHEMA public if you don't need it. 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] Database schema
Dhiraj -- From: Dhiraj Gupta dhira...@nic.in To: pgsql-general@postgresql.org Sent: Sunday, December 2, 2012 9:35 PM Subject: [GENERAL] Database schema Hi All, I have created a database name 'ofbiz. then the default schema name public created automatically. I want to create schema name ofbiz in the database ofbiz when I create database name ofbiz then the schema name ofbiz will create automatically. how it is possible if yes, Thanks Dhiraj Gupta When a new database is created, the template is the builtin database called template1. If you create the schema in that database, that schema will be made part of any new database you make. You could also make a new template database, create the schema in that one, and then create your new databases using the WITH TEMPLATE = option in CREATE DATABASE. So if you have need of different schemas with different databases, that would be more extensible by making new templates, onjhe3 for each type./. HTH, Greg Williamson -- 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] Database schema
Albe Laurenz laurenz.a...@wien.gv.at wrote: Dhiraj Gupta wrote: I have created a database name 'ofbiz. then the default schema name public created automatically. I want to create schema name ofbiz in the database ofbiz when I create database name ofbiz then the schema name ofbiz will create automatically. how it is possible if yes, There is no way to automatically create a schema when you create a database. Connect to the newly created database and create it with CREATE SCHEMA. You can DROP SCHEMA public if you don't need it. There is a way to create a so called schema in every new created database: create that schema in the template1, or create a new template database and use this template to create a new database. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Set returning functions in the SELECT list
On 2012-11-16, Tom Lane t...@sss.pgh.pa.us wrote: Ryan Kelly rpkell...@gmail.com writes: I have a question about the behavior of SRFs in the SELECT list. If you have more than one in a select list, the number of resulting rows is the least common multiple of their periods, because the select list gets cycled until they all terminate at the same time. Also, I'm not sure where this difference in behavior is documented. AFAIK it's not documented. Every time it comes up there are arguments about whether to change it, and nobody has wanted to make a permanent commitment to this behavior by documenting it. (On the other hand, since it's been like this since Berkeley days, it would be pretty hard to make an adequate case for changing it and likely breaking applications ...) And yes, I understand doing this is deprecated and my results would probably be better achieved with LATERAL when 9.3 comes out. Yeah, LATERAL has a lot less semantic messiness to it. for now you can wrap generate_series in as many CTEs as you want and do a join if you need a cross-product. -- ⚂⚃ 100% natural -- 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] SETOF come ritorno delle funzioni
Le lundi 03 décembre 2012 à 08:29 +0100, Pavel Stehule a écrit :
Hello
sorry, a used language in this mailing list is English language
You're right, of course, but I'll try and answer since I read a bit of
Italian.
Piviul, si deve scrivere in inglese qua, per ottenere delle risposte.
2012/11/30 Piviul piv...@riminilug.it:
Ciao a tutti, avrei bisogno di creare una funzione che restituisca un
insieme di record. Ho visto che è possibile fare restituire ad una
funzione una tabella di cui si definiscono i campi all'interno della
funzione stessa
CREATE FUNCTION foo() RETURNS TABLE(id INT, foo TEXT)
Poi nel corpo provo a costruirmi il record da restituire con RETURN NEXT
ma mi da un errore: RETURN NEXT non può avere un parametro in una
funzione con parametri OUT a o vicino r dove r è la variabile di
tipo record che vorrei accodare all'output.
Se ho capito bene, basta usare questo :
CREATE OR REPLACE FUNCTION foo(text) RETURNS TABLE(id INT, nome TEXT, a
text) AS
$pippo$
SELECT id, nome, $1
FROM foo;
$pippo$ LANGUAGE sql;
select foo('a');
Vorrei in altre parole fare una funzione tipo:
CREATE OR REPLACE FUNCTION magazzino.foo()
RETURNS TABLE(id INT, nome TEXT) AS
$pippo$
DECLARE
r RECORD;
BEGIN
FOR r in
SELECT id::int, nome::text
FROM foo
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$pippo$ LANGUAGE plpgsql;
there should be identifier collision - you cannot simply mix plpgsql
variables and sql identifiers - so you have to use qualified
identifiers - schema.name
CREATE OR REPLACE FUNCTION magazzino.foo()
RETURNS TABLE(id INT, nome TEXT) AS
$pippo$
BEGIN
FOR id, nome in
SELECT foo.id::int, foo.nome::text
FROM foo
LOOP
RETURN NEXT;
END LOOP;
RETURN;
END;
$pippo$ LANGUAGE plpgsql;
Regards
Pavel Stehule
Cosa sbaglio?
Piviul
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Vincent Veyron
http://marica.fr
Logiciel pour département juridique
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Which is faster: char(14) or varchar(14)
I think I already know the answer (char(14)), but I would like to confirm: which is faster? In Brazil, company id has 14 digits (12 identifiers, 2 control digits). By today, application use varchar(14) for these, but I intend to optimize insert/update/delete and search, and I'm considering to change it to char(14). Will it give ANY gain? I do use equality and like operators for search. Regards, Edson -- 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] Which is faster: char(14) or varchar(14)
On Tue, Dec 4, 2012 at 11:44:20AM -0200, Edson Richter wrote: I think I already know the answer (char(14)), but I would like to confirm: which is faster? In Brazil, company id has 14 digits (12 identifiers, 2 control digits). By today, application use varchar(14) for these, but I intend to optimize insert/update/delete and search, and I'm considering to change it to char(14). Will it give ANY gain? I do use equality and like operators for search. You need to test it but I doubt there would be any measurable difference. If it will always be 14, I would use char(14), and perhaps use a CHECK constraint to make sure it is always 14 with spaces. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Which is faster: char(14) or varchar(14)
Em 04/12/2012 11:50, Pavel Stehule escreveu: Hello 2012/12/4 Edson Richter edsonrich...@hotmail.com: I think I already know the answer (char(14)), but I would like to confirm: which is faster? In Brazil, company id has 14 digits (12 identifiers, 2 control digits). By today, application use varchar(14) for these, but I intend to optimize insert/update/delete and search, and I'm considering to change it to char(14). Will it give ANY gain? I do use equality and like operators for search. There are no big differences between char and varchar - char can be little bit slower, because empty chars to limit are filled by space. So usually varchar is more effective (in PostgreSQL). In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? Sorry if there are many questions in one, but I'm in a brainstorm... Thanks, Edson Regards Pavel Stehule Regards, Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Which is faster: char(14) or varchar(14)
Edson Richter wrote:
Also, I see all varchar(...) created are by default storage =
EXTENDED (from Pg Admin), while other datatypes (like numeric,
smallint, integer) are storage = MAIN.
That's unlikely to matter on a 14 character value.
Can I have a gain using fixed length datatype in place of
current varchar (like numeric (14,0))?
Or changing to char(14) check length(doc)=14 and
storage=MAIN?
In PostgreSQL char(n) is never, ever, under any circumstances
faster than varchar(n) to store or retrieve. char(n) is stored
exactly the same as varchar(n) except that before storing the
length is checked and spaces are added if necessary to fill it out
to the maximum length, and when comparing spaces are stripped
before using the value in comparisons to other strings. The
semantics of char(n) are confusing and very odd. Personally, I
recommend never, ever using char(n).
PostgreSQL provides a function to check the storage length in bytes
for various types of objects (although some of them might be
compressed or stored out of line under some circumstances).
test=# select pg_column_size('12345678901234'::char(14));
pg_column_size
18
(1 row)
test=# select pg_column_size('1'::char(14));
pg_column_size
18
(1 row)
test=# select pg_column_size('12345678901234'::varchar(14));
pg_column_size
18
(1 row)
test=# select pg_column_size('1'::varchar(14));
pg_column_size
5
(1 row)
test=# select pg_column_size('12345678901234'::numeric(14,0));
pg_column_size
14
(1 row)
test=# select pg_column_size('1'::numeric(14,0));
pg_column_size
8
(1 row)
test=# select pg_column_size('12345678901234'::bigint);
pg_column_size
8
(1 row)
If your value is always 14 numeric digits, bigint would save space
and generally be faster than varcher(14).
-Kevin
--
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] Which is faster: char(14) or varchar(14)
Em 04/12/2012 12:53, Kevin Grittner escreveu:
Edson Richter wrote:
Also, I see all varchar(...) created are by default storage =
EXTENDED (from Pg Admin), while other datatypes (like numeric,
smallint, integer) are storage = MAIN.
That's unlikely to matter on a 14 character value.
Can I have a gain using fixed length datatype in place of
current varchar (like numeric (14,0))?
Or changing to char(14) check length(doc)=14 and
storage=MAIN?
In PostgreSQL char(n) is never, ever, under any circumstances
faster than varchar(n) to store or retrieve. char(n) is stored
exactly the same as varchar(n) except that before storing the
length is checked and spaces are added if necessary to fill it out
to the maximum length, and when comparing spaces are stripped
before using the value in comparisons to other strings. The
semantics of char(n) are confusing and very odd. Personally, I
recommend never, ever using char(n).
PostgreSQL provides a function to check the storage length in bytes
for various types of objects (although some of them might be
compressed or stored out of line under some circumstances).
test=# select pg_column_size('12345678901234'::char(14));
pg_column_size
18
(1 row)
test=# select pg_column_size('1'::char(14));
pg_column_size
18
(1 row)
test=# select pg_column_size('12345678901234'::varchar(14));
pg_column_size
18
(1 row)
test=# select pg_column_size('1'::varchar(14));
pg_column_size
5
(1 row)
test=# select pg_column_size('12345678901234'::numeric(14,0));
pg_column_size
14
(1 row)
test=# select pg_column_size('1'::numeric(14,0));
pg_column_size
8
(1 row)
test=# select pg_column_size('12345678901234'::bigint);
pg_column_size
8
(1 row)
If your value is always 14 numeric digits, bigint would save space
and generally be faster than varcher(14).
Thanks, I've learned a lot.
Now, I'll make my home work.
Regards,
Edson
-Kevin
--
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] Which is faster: char(14) or varchar(14)
Edson Richter edsonrich...@hotmail.com writes: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? Sounds like premature optimization to me. I'd first express what I want as clear as possible, e.g. CREATE DOMAIN BrazilianCompanyId AS char(14), and try to spot and fix performance problems when I'm done with all that. -- 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] Which is faster: char(14) or varchar(14)
Em 04/12/2012 14:59, hari.fu...@gmail.com escreveu: Edson Richter edsonrich...@hotmail.com writes: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? Sounds like premature optimization to me. I'd first express what I want as clear as possible, e.g. CREATE DOMAIN BrazilianCompanyId AS char(14), and try to spot and fix performance problems when I'm done with all that. Actually, I already stressed performance over these fields (query optimization, indexing, reverse indexing, full text index inside PostgreSQL and outside PostgreSQL, etc). At current stage, I'm just looking for finetuning. Maybe storage is one possibility. It's already established database that I can't make big changes (even changing from varchar to decimal or bigint would not be possible because of leading zeroes). Thanks for all that provided hints! I've learned a lot with you all. Regards, Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq error message deallocation
PQerrorMessage function return char const* char const* msg = PQerrorMessage(conn); Now since it's const, I don't think I should be deallocating it and I've never seen that done in any examples. But then, when and how does it get freed? At first I thought it gets deallocated once another error message is requested but that's not the case. // cause some errorchar const* msg1 = PQerrorMessage(pgconn);// cause another errorchar const* msg2 = PQerrorMessage(pgconn);// still worksstd::cout msg1 msg2 std::endl; Can someone shed some light on this for me? -- View this message in context: http://postgresql.1045698.n5.nabble.com/libpq-error-message-deallocation-tp5735032.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] libpq error message deallocation
2012/12/4 icholy ilia.ch...@gmail.com PQerrorMessage function return char const* char const* msg = PQerrorMessage(conn); Now since it's const, I don't think I should be deallocating it and I've never seen that done in any examples. But then, when and how does it get freed? At first I thought it gets deallocated once another error message is requested but that's not the case. // cause some error char const* msg1 = PQerrorMessage(pgconn); // cause another error char const* msg2 = PQerrorMessage(pgconn); // still works std::cout msg1 msg2 std::endl; Can someone shed some light on this for me? PQerrorMessage() returns pointer to the last allocated string from the PGConn. The memory on this string will be deallocated with PQfinish(). In the above case, msg1 is invalid pointer and you just got lucky. Please, see description of PQerrorMessage() here http://www.postgresql.org/docs/9.2/static/libpq-status.html // Dmitriy.
Re: [GENERAL] libpq error message deallocation
thanks for the quick reply! -- View this message in context: http://postgresql.1045698.n5.nabble.com/libpq-error-message-deallocation-tp5735032p5735046.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UPDATE using subquery with joined tables
Hi, I need to update field1 in table1, gathering data from field1 in table2. The following SELECT shows the data as it needs to be updated: SELECT a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1') FROM table1 a JOIN table2 b USING (id) WHERE a.field1 NOT LIKE '%mypattern%' AND b.field1 LIKE '%mypattern%'; I am not sure how to translate this into an UPDATE statement for fiel1 in table1 efficiently. Any tips welcome. Cheers, -- Seb -- 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] UPDATE using subquery with joined tables
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Sebastian P. Luque Sent: Tuesday, December 04, 2012 2:53 PM To: pgsql-general@postgresql.org Subject: [GENERAL] UPDATE using subquery with joined tables Hi, I need to update field1 in table1, gathering data from field1 in table2. The following SELECT shows the data as it needs to be updated: SELECT a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1') FROM table1 a JOIN table2 b USING (id) WHERE a.field1 NOT LIKE '%mypattern%' AND b.field1 LIKE '%mypattern%'; I am not sure how to translate this into an UPDATE statement for fiel1 in table1 efficiently. Any tips welcome. Cheers, -- Seb Ignoring the efficiently part the general form for a joining update is: UPDATE table SET field = src.field FROM ( SELECT id, field FROM ... -- make this query as complex as needed; including WITH if necessary ) src WHERE table.id = src.id; SO Not Tested: UPDATE table1 SET field1 = t2.new_field_1 FROM ( SELECT id --# need to add the linking ID to the subquery , a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1') AS new_field_1 --# provide an alias for this column FROM table1 a JOIN table2 b USING (id) WHERE a.field1 NOT LIKE '%mypattern%' AND b.field1 LIKE '%mypattern%'; ) t2 WHERE table1.id = t2.id; David J. -- 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] Which is faster: char(14) or varchar(14)
On 05/12/12 06:06, Edson Richter wrote: Em 04/12/2012 14:59, hari.fu...@gmail.com escreveu: Edson Richter edsonrich...@hotmail.com writes: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? Sounds like premature optimization to me. I'd first express what I want as clear as possible, e.g. CREATE DOMAIN BrazilianCompanyId AS char(14), and try to spot and fix performance problems when I'm done with all that. Actually, I already stressed performance over these fields (query optimization, indexing, reverse indexing, full text index inside PostgreSQL and outside PostgreSQL, etc). At current stage, I'm just looking for finetuning. Maybe storage is one possibility. It's already established database that I can't make big changes (even changing from varchar to decimal or bigint would not be possible because of leading zeroes). Thanks for all that provided hints! I've learned a lot with you all. Regards, Edson If your number is always the same length, you don't need to store the zeros in the database, so you can use bigint! You can add the leading zeros when you display to the user. More specifically, you could add leading zeros in the SQL you use to extract the value from the database. N.B. lpad(*) truncates values larger than the field size! For example: DROP TABLE IF EXISTS tabzer; CREATE TABLE tabzer ( id SERIAL PRIMARY KEY, payload bigint ); INSERT INTO tabzer (payload) VALUES (123), (1234567890), (1234567890123456), (12345678901234567) ; TABLE tabzer; SELECT lpad(t.payload::text, 16, '0') FROM tabzer t /**/;/**/ -- 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] UPDATE using subquery with joined tables
Seems like this should work (untested though): UPDATE table1 a SET field1 = a.field1 || (SELECT regexp_replace(b.field1, '', '...') FROM table2 b WHERE a.id = b.id AND b.field1 LIKE '') WHERE a.field1 NOT LIKE '' Paul On Tue, Dec 4, 2012 at 11:52 AM, Sebastian P. Luque splu...@gmail.comwrote: Hi, I need to update field1 in table1, gathering data from field1 in table2. The following SELECT shows the data as it needs to be updated: SELECT a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1') FROM table1 a JOIN table2 b USING (id) WHERE a.field1 NOT LIKE '%mypattern%' AND b.field1 LIKE '%mypattern%'; I am not sure how to translate this into an UPDATE statement for fiel1 in table1 efficiently. Any tips welcome. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- _ Pulchritudo splendor veritatis.
Re: [GENERAL] Which is faster: char(14) or varchar(14)
On Tue, Dec 4, 2012 at 5:57 AM, Edson Richter edsonrich...@hotmail.com wrote: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? May be I am late with my reply but I would also recommend to take into consideration the article from depesz where he explains and tests all the textual types http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/. Very useful one. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_shadow and pgcrypto
Hi all, I'm currently performing a hash check for password verification.I'm generating an md5 hash or checking for plain text in pg_shadow. However, outside of these two out-of-the-box options, what if someone is using pg-crypto or any other PAM ? How can I differentiate between say md5 and pgcrypto ? I see the md5 is prefixed in hash in pg_shadow. I was wondering what other prefixes may exist, say for pgcrypto. If you have an examples of what a pgcrypto or any other PAM hash would look like (or what they would at least begin with), that would be great. Thanks, Frank Frank Cavaliero Database Administrator IBM Infosphere Guardium
Re: [GENERAL] Which is faster: char(14) or varchar(14)
Em 04/12/2012 18:49, Sergey Konoplev escreveu: On Tue, Dec 4, 2012 at 5:57 AM, Edson Richter edsonrich...@hotmail.com wrote: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? May be I am late with my reply but I would also recommend to take into consideration the article from depesz where he explains and tests all the textual types http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/. Very useful one. Yes, good education as well. I've read, and I'll consider the different datatypes and more domain use in future. But I don't see much changes in our schema, so using varchar(14) seems to be as good as text. My experience with other databases (MS SQL Server and Oracle) seems to not apply 1:1 here (I've studied MSSQL internals and - at least up to 2005 version - is much more efficient with char than with varchar than with text - for all operations). Thanks again, Edson -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.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] Which is faster: char(14) or varchar(14)
Hello 2012/12/4 Edson Richter edsonrich...@hotmail.com: Em 04/12/2012 18:49, Sergey Konoplev escreveu: On Tue, Dec 4, 2012 at 5:57 AM, Edson Richter edsonrich...@hotmail.com wrote: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are storage = MAIN. Can I have a gain using fixed length datatype in place of current varchar (like numeric (14,0))? Or changing to char(14) check length(doc)=14 and storage=MAIN? May be I am late with my reply but I would also recommend to take into consideration the article from depesz where he explains and tests all the textual types http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/. Very useful one. Yes, good education as well. I've read, and I'll consider the different datatypes and more domain use in future. But I don't see much changes in our schema, so using varchar(14) seems to be as good as text. My experience with other databases (MS SQL Server and Oracle) seems to not apply 1:1 here (I've studied MSSQL internals and - at least up to 2005 version - is much more efficient with char than with varchar than with text - for all operations). sure - PostgreSQL has different design than older SQL servers that was developed for fixed length records. So some knowledges related to these databases are wrong here. Regards Pavel Stehule Thanks again, Edson -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql logfilename and times in GMT - not EST
We have a test 9.2.0 db running on openSuse 12.2. When I select now() I get the correct timezone and date back (-5 hours). When I do date at the os prompt, I get the right timezone back. I changed postgres.conf to have timezone = 'EST' and restarted postgres. However the log file is still 5 hours ahead. What gives? Not the end of the world but a bit annoying. Bryan.
Re: [GENERAL] Postgresql logfilename and times in GMT - not EST
On Tue, Dec 4, 2012 at 1:59 PM, Bryan Montgomery mo...@english.net wrote: We have a test 9.2.0 db running on openSuse 12.2. When I select now() I get the correct timezone and date back (-5 hours). When I do date at the os prompt, I get the right timezone back. I changed postgres.conf to have timezone = 'EST' and restarted postgres. However the log file is still 5 hours ahead. What gives? Not the end of the world but a bit annoying. you need to set log_timezone . This is a new 'feature' in 9.2 that annoyed me as well. I assume that there was a good use case for this. -- 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] Postgresql logfilename and times in GMT - not EST
Lonni J Friedman netll...@gmail.com writes: On Tue, Dec 4, 2012 at 1:59 PM, Bryan Montgomery mo...@english.net wrote: I changed postgres.conf to have timezone = 'EST' and restarted postgres. However the log file is still 5 hours ahead. What gives? Not the end of the world but a bit annoying. you need to set log_timezone . This is a new 'feature' in 9.2 that annoyed me as well. I assume that there was a good use case for this. New? log_timezone has been around since 8.3, and it seems like a good idea to me --- what if you have N sessions each with its own active timezone setting? Timestamps in the log would be an unreadable mismash if there weren't a separate log_timezone setting. What did change in 9.2 is that initdb sets values for timezone and log_timezone in postgresql.conf, so it's the initdb environment that will determine what you get in the absence of any manual action. Before that it was the postmaster's environment. 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] Postgresql logfilename and times in GMT - not EST
On Tue, Dec 4, 2012 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: On Tue, Dec 4, 2012 at 1:59 PM, Bryan Montgomery mo...@english.net wrote: I changed postgres.conf to have timezone = 'EST' and restarted postgres. However the log file is still 5 hours ahead. What gives? Not the end of the world but a bit annoying. you need to set log_timezone . This is a new 'feature' in 9.2 that annoyed me as well. I assume that there was a good use case for this. New? log_timezone has been around since 8.3, and it seems like a good idea to me --- what if you have N sessions each with its own active timezone setting? Timestamps in the log would be an unreadable mismash if there weren't a separate log_timezone setting. What did change in 9.2 is that initdb sets values for timezone and log_timezone in postgresql.conf, so it's the initdb environment that will determine what you get in the absence of any manual action. Before that it was the postmaster's environment. Sorry, I meant new, in that its impact changed in 9.2 such that it needed to be explicitly set to not get UTC by default, whereas in the past that wasn't required. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Permission denied in file_fdw (Windows)
Hi I'm getting an error when reading from a file_fdw table in a Windows environment. Any hints? (see below). And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not really verbose :- At least following format options should be mentioned: 'xml', 'text', 'csv', 'binary'. Yours, Stefan That's what I'm doing: CREATE EXTENSION file_fdw; CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE foreignfiletable(id text, title text) SERVER file_server OPTIONS(format 'csv', header 'true', filename 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); SELECT * FROM foreignfiletable; -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be opened for reading: Permission denied. -- 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] Permission denied in file_fdw (Windows)
On 04/12/2012 23:39, Stefan Keller wrote: Hi I'm getting an error when reading from a file_fdw table in a Windows environment. Any hints? (see below). And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not really verbose :- At least following format options should be mentioned: 'xml', 'text', 'csv', 'binary'. Yours, Stefan That's what I'm doing: CREATE EXTENSION file_fdw; CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE foreignfiletable(id text, title text) SERVER file_server OPTIONS(format 'csv', header 'true', filename 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); SELECT * FROM foreignfiletable; -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be opened for reading: Permission denied. Have you checked that the user account under which the server is running has the necessary permissions to read that file? - That's what the error message would seem to indicate. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Permission denied in file_fdw (Windows)
Hi, On 5.12.2012 00:39, Stefan Keller wrote: Hi I'm getting an error when reading from a file_fdw table in a Windows environment. Any hints? (see below). Well, the file clearly isn't accessible by the postgres user (or whatever user you're using in Windows). The file is opened from a PostgreSQL backend process, not using your regular user. And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not really verbose :- At least following format options should be mentioned: 'xml', 'text', 'csv', 'binary'. It clearly states that format accepts the same values as COPY and even provides a link to COPY documentation. Placing this kind of details onto both pages would be just maintenance burden. Yours, Stefan That's what I'm doing: CREATE EXTENSION file_fdw; CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE foreignfiletable(id text, title text) SERVER file_server OPTIONS(format 'csv', header 'true', filename 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); SELECT * FROM foreignfiletable; -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be opened for reading: Permission denied. What else could PostgreSQL tell you? It tried to open the file, did a regular open() or something like that and it failed with EACCES error code. That's all the info there is. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How get column-wise table info from an arbitrary query?
I am porting from MySQL some code that has to take an arbitrary query involving joins and build up a dictionary (in an HLL talking to Postgres over a socket) where each column name will be the key. The catch is that there will be duplicate entries where two joined tables have the same column such as id, so I have to get the source table for each column. Here is a sample query: select * from providers p inner join provider_types pt on pt.id = p.provider_type_id; I actually figured out how to get the table OID which would suffice, but I am porting MySQL code that could get fully qualified column names including a table alias if that were used. We allow other code to look up values in the dictionary with the alias as a prefix as a convenience, eg. p.id or pt.id. I can easily fake this if we predefine a unique alias-table pairing (which we follow anyway), but if Postgres itself offers this it would be that much cleaner. So: Is there any way on an arbitrary query to determine column names qualified by table aliases? Thx, kt -- Kenneth Tilton *Director of Software Development* *MCNA Dental Plans* 200 West Cypress Creek Road Suite 500 Fort Lauderdale, FL 33309 954-730-7131 X181 (Office) 954-628-3347 (Fax) 1-800-494-6262 X181 (Toll Free) ktil...@mcna.net glip...@mcna.net (Email) www.mcna.net (Website) CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
Re: [GENERAL] How get column-wise table info from an arbitrary query?
On 05/12/2012 01:04, Kenneth Tilton wrote: I am porting from MySQL some code that has to take an arbitrary query involving joins and build up a dictionary (in an HLL talking to Postgres over a socket) where each column name will be the key. The catch is that there will be duplicate entries where two joined tables have the same column such as id, so I have to get the source table for each column. Here is a sample query: select * from providers p inner join provider_types pt on pt.id http://pt.id = p.provider_type_id; I actually figured out how to get the table OID which would suffice, but I am porting MySQL code that could get fully qualified column names including a table alias if that were used. We allow other code to look up values in the dictionary with the alias as a prefix as a convenience, eg. p.id http://p.id or pt.id http://pt.id. Maybe I'm misunderstanding, but why don't you just give the columns an alias directly? - select p.id as p_id, pt.id as pt_id, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] How get column-wise table info from an arbitrary query?
On 05/12/2012 01:11, Raymond O'Donnell wrote: On 05/12/2012 01:04, Kenneth Tilton wrote: I am porting from MySQL some code that has to take an arbitrary query involving joins and build up a dictionary (in an HLL talking to Postgres over a socket) where each column name will be the key. The catch is that there will be duplicate entries where two joined tables have the same column such as id, so I have to get the source table for each column. Here is a sample query: select * from providers p inner join provider_types pt on pt.id http://pt.id = p.provider_type_id; I actually figured out how to get the table OID which would suffice, but I am porting MySQL code that could get fully qualified column names including a table alias if that were used. We allow other code to look up values in the dictionary with the alias as a prefix as a convenience, eg. p.id http://p.id or pt.id http://pt.id. Maybe I'm misunderstanding, but why don't you just give the columns an alias directly? - select p.id as p_id, pt.id as pt_id, Sorry, I *am* misunderstanding - I missed the arbitrary bit. Too late at night to be working... Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] How get column-wise table info from an arbitrary query?
On Tue, Dec 4, 2012 at 8:26 PM, Raymond O'Donnell r...@iol.ie wrote: On 05/12/2012 01:11, Raymond O'Donnell wrote: On 05/12/2012 01:04, Kenneth Tilton wrote: I am porting from MySQL some code that has to take an arbitrary query involving joins and build up a dictionary (in an HLL talking to Postgres over a socket) where each column name will be the key. The catch is that there will be duplicate entries where two joined tables have the same column such as id, so I have to get the source table for each column. Here is a sample query: select * from providers p inner join provider_types pt on pt.id http://pt.id = p.provider_type_id; I actually figured out how to get the table OID which would suffice, but I am porting MySQL code that could get fully qualified column names including a table alias if that were used. We allow other code to look up values in the dictionary with the alias as a prefix as a convenience, eg. p.id http://p.id or pt.id http://pt.id. Maybe I'm misunderstanding, but why don't you just give the columns an alias directly? - select p.id as p_id, pt.id as pt_id, Sorry, I *am* misunderstanding - I missed the arbitrary bit. Too late at night to be working... g No harm, and I did consider that as a brute force workaround, because we are already a little brittle in that we are assuming p as the table alias. So we /could/ do: select p.id p_id, p.*, pt.id pt_id, pt.* ...etc... Hmmm, that might be the easy way out, albeit brute. :) Thx, kt Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Kenneth Tilton *Director of Software Development* *MCNA Dental Plans* 200 West Cypress Creek Road Suite 500 Fort Lauderdale, FL 33309 954-730-7131 X181 (Office) 954-628-3347 (Fax) 1-800-494-6262 X181 (Toll Free) ktil...@mcna.net glip...@mcna.net (Email) www.mcna.net (Website) CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
[GENERAL] how do I grant select to one user for all tables in a DB?
V9.1.5 on linux
User select created (yup, that's right, they want the user name to be
select. Guess what ptivs it is to have! Don't kill the messanger :-) )
postgres=# grant select on all tables in schema sde to select;
ERROR: schema sde does not exist
postgres=# \l
List of databases
Name| Owner | Encoding | Collate |Ctype| Access privileges
---+-+--+-+-+-
postgres | pgdbadm | UTF8 | C | en_US.UTF-8 |
sde | pgdbadm | UTF8 | C | en_US.UTF-8 |
template0 | pgdbadm | UTF8 | C | en_US.UTF-8 | =c/pgdbadm +
| | | | | pgdbadm=CTc/pgdbadm
template1 | pgdbadm | UTF8 | C | en_US.UTF-8 | =c/pgdbadm +
| | | | | pgdbadm=CTc/pgdbadm
(4 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
---++---
insert|| {}
pgdbadm | Superuser, Create role, Create DB, Replication | {}
select|| {}
Bottom line is that I want this select user to be able to query all tables
yet to be created in the DB without having to issue grant statments after table
craation. But just select, no more.
Thanks in Advance !
[GENERAL] ts_headline and query with hyphen
Hi
I have a question about ts_headline, when the query includes word like
'on-line' - only the 'line' part is highlighted, even though the whole
phrase is indexed too, some details below.
Postgresql 9.1.6
select
token, dictionary, lexemes
from
ts_debug('play on-line') where alias 'blank';
token | dictionary | lexemes
-+--+--
play| english_stem | {play}
on-line | english_stem | {on-lin}
on | english_stem | {}
line| english_stem | {line}
select to_tsquery('play on-line');
to_tsquery
'play' 'on-lin' 'line'
select ts_headline('play on-line', to_tsquery('play on-line'));
ts_headline
bplay/b on-bline/b
Same as
select ts_headline('play on-line', to_tsquery('play line'));
ts_headline
bplay/b on-bline/b
Is that the intended behaviour? I guess the problem here is that 'on' is
not a lexem, but then what about on-lin?
In another example, I thought that a hyphenated match would have some
kind of preference
select token, dictionary, lexemes from ts_debug('custom-built query')
where alias 'blank';
token | dictionary |lexemes
--+--+
custom-built | english_stem | {custom-built}
custom | english_stem | {custom}
built| english_stem | {built}
query| english_stem | {queri}
select to_tsquery('query custom-built');
to_tsquery
---
'queri' 'custom-built' 'custom' 'built'
select ts_headline('custom-built query', to_tsquery('query
custom-built'));
ts_headline
-
bcustom/b-bbuilt/b bquery/b
This works better, but still both parts of 'custom-built' are
highlighted separately. But maybe ts_headline understands or operates on
single, not hyphenated words only?
thanks
daniel
--
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] ts_headline and query with hyphen
daniel dochto...@gmail.com writes:
I have a question about ts_headline, when the query includes word like
'on-line' - only the 'line' part is highlighted, even though the whole
phrase is indexed too, some details below.
Part of the reason is that on is a stop word (at least in the default
english dictionary). That's why you get
select to_tsquery('play on-line');
to_tsquery
'play' 'on-lin' 'line'
and not 'play' 'on-lin' 'on' 'line'. If you did get the latter
then you'd get a headline result with both parts highlighted, similar to
your custom-built case.
But maybe ts_headline understands or operates on
single, not hyphenated words only?
Dunno. It would seem reasonable to highlight the whole compound in
these cases, but I have no idea how hard that is.
Another thing that seems a bit odd here is that we seem to be stemming
the compound word as a whole, but not the individual parts. Not sure
how sane that combination of choices is ...
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] Statistics mismatch between n_live_tup and actual row count
I am also seeing a drift in the n_live_tup value compared to actual row count on the table on PG9.0.6 It drifts after a vacuum , you can bring it back closer to the actual number by running ANALYSE several times, you can lock it back into the right value with a vacuum full, but then if you run a vacuum it shows a n_live_tup less than the actual rows in the table. This is on a table seeing 80% HOT updates. Its a table that is heavily updated. Server not running autovacuum Tim -- View this message in context: http://postgresql.1045698.n5.nabble.com/Statistics-mismatch-between-n-live-tup-and-actual-row-count-tp5059317p5735108.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how do I grant select to one user for all tables in a DB?
On Wed, Dec 5, 2012 at 2:12 PM, Gauthier, Dave dave.gauth...@intel.com wrote: V9.1.5 on linux User select created (yup, that's right, they want the user name to be select. Guess what ptivs it is to have! Don't kill the messanger :-) ) postgres=# grant select on all tables in schema sde to select; ERROR: schema sde does not exist postgres=# \l List of databases Your immediate problem is that sde is a database, not a schema. They're different things, despite MySQL conflating the terms. What you're trying to do is a perfectly reasonable way to create a backup user. And it's definitely possible; check out ALTER DEFAULT PRIVILEGES: http://www.postgresql.org/docs/9.1/static/sql-alterdefaultprivileges.html I think that's what you need there! ChrisA -- 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] ts_headline and query with hyphen
On 12/05/2012 04:49 AM, Tom Lane wrote:
daniel dochto...@gmail.com writes:
I have a question about ts_headline, when the query includes word like
'on-line' - only the 'line' part is highlighted, even though the whole
phrase is indexed too, some details below.
Part of the reason is that on is a stop word (at least in the default
english dictionary). That's why you get
select to_tsquery('play on-line');
to_tsquery
'play' 'on-lin' 'line'
and not 'play' 'on-lin' 'on' 'line'. If you did get the latter
then you'd get a headline result with both parts highlighted, similar to
your custom-built case.
I understand the 'on' part, but still, 'on-lin' is passed to the
ts_headline, so I thought that match would be preferred over 'line' and
highlighted as a whole.
Additionally, with a specific value of MaxWords I could see a dangling
line at the start of a headline (on- has been cut off), which is
kinda troubling, because it's not even an English document. It doesn't
seem to happen to queries like 'custom-built' - I can't see it being
split neither in the beginning of a headline nor at the end.
Just to be clear - the headline with cut off on- is OK (having the
matched stuff somewhere in the middle, though with highlighted 'line'
only), it's just that the word 'on-line' is used multiple times in the
doc and it happended to appear at the beginning of a headline. Cutting
was not affected by ShortWord setting, so I guess it's a stopword thing
again. If that's the case, then IMHO it should treat hyphenated words as
1 when creating the headline and not cut off like that. But maybe it was
intended to work like that..
But maybe ts_headline understands or operates on
single, not hyphenated words only?
Dunno. It would seem reasonable to highlight the whole compound in
these cases, but I have no idea how hard that is.
Right, although that latter case is easy to fix outside postgres and
still looks fine - I've included it just as an example. Former causes a
few problems in specific cases, I have to fix them manually now, word by
word.
Another thing that seems a bit odd here is that we seem to be stemming
the compound word as a whole, but not the individual parts. Not sure
how sane that combination of choices is ...
Good question, hope others will jump in.
thanks,
daniel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table with million rows - and PostgreSQL 9.1 is not using the index
I've a table with 110 rows, with streets. I'm making a partial search using zip code, and PostgreSQL is ignoring my ZIP index. I'm sure I'm making some mistake, but I can't see where. The query is: SELECT t2.ID, t2.CEP, t2.COMPLEMENTO, t2.NOME, t2.NOMESEMACENTOS, t2.TIPO, t2.BAIRRO_ID FROM LOCALIDADE t0, LOGRADOURO t2, BAIRRO t1 WHERE t2.CEP LIKE '81630160%' AND ((t1.ID = t2.BAIRRO_ID) AND (t0.ID = t1.LOCALIDADE_ID)) ORDER BY t0.NOME; (for reference, BAIRRO = town, LOCALIDADE = city, LOGRADOURO = street) Here is the result of explain analyze: Sort (cost=11938.72..11938.74 rows=91 width=93) Sort Key: t0.nome - Nested Loop (cost=0.00..11938.42 rows=91 width=93) - Nested Loop (cost=0.00..11935.19 rows=91 width=85) - Seq Scan on logradouro t2 (cost=0.00..11634.42 rows=91 width=81) Filter: ((cep)::text ~~ '81630160%'::text) - Index Scan using pkbairro on bairro t1 (cost=0.00..3.30 rows=1 width=8) Index Cond: (id = t2.bairro_id) - Index Scan using pklocalidade on localidade t0 (cost=0.00..0.03 rows=1 width=16) Index Cond: ((id)::text = (t1.localidade_id)::text) I've few tweaks in postgresql.conf: shared_buffers = 2GB temp_buffers = 32MB max_prepared_transactions = 50 work_mem = 32MB maintenance_work_mem = 16MB max_stack_depth = 4MB max_files_per_process = 15000 random_page_cost = 2.0 cpu_tuple_cost = 0.001 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.00025 effective_cache_size = 512MB Everything else is default configuration. This machine is Intel Quad 3.1Ghz, with 8 threads, 8Gig of RAM, 8Gig of Swap, running CentOS 6.3 64bit. Machine is free almost all the time. Thanks for your advice, Edson -- 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] Table with million rows - and PostgreSQL 9.1 is not using the index
I'm no expert on this, but it will likely be more helpful to others if you include the table description with all the indices. On Tue, Dec 4, 2012 at 8:44 PM, Edson Richter edsonrich...@hotmail.com wrote: I've a table with 110 rows, with streets. I'm making a partial search using zip code, and PostgreSQL is ignoring my ZIP index. I'm sure I'm making some mistake, but I can't see where. The query is: SELECT t2.ID, t2.CEP, t2.COMPLEMENTO, t2.NOME, t2.NOMESEMACENTOS, t2.TIPO, t2.BAIRRO_ID FROM LOCALIDADE t0, LOGRADOURO t2, BAIRRO t1 WHERE t2.CEP LIKE '81630160%' AND ((t1.ID = t2.BAIRRO_ID) AND (t0.ID = t1.LOCALIDADE_ID)) ORDER BY t0.NOME; (for reference, BAIRRO = town, LOCALIDADE = city, LOGRADOURO = street) Here is the result of explain analyze: Sort (cost=11938.72..11938.74 rows=91 width=93) Sort Key: t0.nome - Nested Loop (cost=0.00..11938.42 rows=91 width=93) - Nested Loop (cost=0.00..11935.19 rows=91 width=85) - Seq Scan on logradouro t2 (cost=0.00..11634.42 rows=91 width=81) Filter: ((cep)::text ~~ '81630160%'::text) - Index Scan using pkbairro on bairro t1 (cost=0.00..3.30 rows=1 width=8) Index Cond: (id = t2.bairro_id) - Index Scan using pklocalidade on localidade t0 (cost=0.00..0.03 rows=1 width=16) Index Cond: ((id)::text = (t1.localidade_id)::text) I've few tweaks in postgresql.conf: shared_buffers = 2GB temp_buffers = 32MB max_prepared_transactions = 50 work_mem = 32MB maintenance_work_mem = 16MB max_stack_depth = 4MB max_files_per_process = 15000 random_page_cost = 2.0 cpu_tuple_cost = 0.001 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.00025 effective_cache_size = 512MB Everything else is default configuration. This machine is Intel Quad 3.1Ghz, with 8 threads, 8Gig of RAM, 8Gig of Swap, running CentOS 6.3 64bit. Machine is free almost all the time. -- 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] ts_headline and query with hyphen
As a follow up to my previous comment, this is a cutting example
select ts_headline('game played on-line', to_tsquery('on-line game'),
'MaxWords=3,MinWords=2,ShortWord=1');
ts_headline
---
bgame/b played on
that can't be right...
daniel
--
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] Table with million rows - and PostgreSQL 9.1 is not using the index
On Wednesday, December 05, 2012 02:44:39 AM Edson Richter wrote: Sort (cost=11938.72..11938.74 rows=91 width=93) Sort Key: t0.nome - Nested Loop (cost=0.00..11938.42 rows=91 width=93) - Nested Loop (cost=0.00..11935.19 rows=91 width=85) - Seq Scan on logradouro t2 (cost=0.00..11634.42 rows=91 width=81) Filter: ((cep)::text ~~ '81630160%'::text) According to that the logradouro table only has 91 rows, which is why it seq- scanned it. Has it been analyzed? Also, partial text matches require a special index declaration, as I recall. Maybe post a \d of each table to help troubleshoot this. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq
Hello, I would like to build a C program, that can access to a Postgres database and I would like to compile the client myself. I think I need libpq for access the database, but I can not download the sources of the driver, because www.libpqxx.org seems to be down. Where can download the sources? I don't want to build the full Postgres database, because the database runs on another host, so I need only the library. Thanks Phil -- 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] libpq
On 12/4/2012 11:15 PM, Philipp Kraus wrote: I would like to build a C program, that can access to a Postgres database and I would like to compile the client myself. I think I need libpq for access the database, but I can not download the sources of the driver, becausewww.libpqxx.org seems to be down. Where can download the sources? I don't want to build the full Postgres database, because the database runs on another host, so I need only the library. iibpqxx is a seperate project, and is a C++ binding for libpq. libpq is part of the postgres server package, and is built when you build the server.it shares a bunch of data definitions need by the protocol handler.you should go ahead and build the full server package, but then just use the library. in general, its better to use the packaged shared library that's distributed with or for your target operating system -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
