Re: [GENERAL] comparing NEW and OLD (any good this way?)
Sam Mason wrote: I've just realized another case where it's not consistent; why does the following return true: SELECT row(null) IS NULL; and yet the following false: SELECT row(row(null)) IS NULL; You're intentionally assuming that row(null) IS NULL evaluating to true implies that row(null) can be replaced by NULL. As discussed upthread, this is not the case. I think I'm saying that PG should be deliberately breaking specified behavior and go back to pre-8.2 behavior in this regard. But let's run your example with 8.1: # SELECT row(null) IS NULL; ?column? -- t # SELECT row(row(null)) IS NULL; ?column? -- f These are the same results that you say are inconsistant, so pre-8.2 behavior doesn't help here... Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best database model for canvassing (and analysing) opinion
Hi all, A school wants to offer a number of short courses on a number of different dates. Students apply online for a single course at a time and choose one or more dates (from a list) which would suit them. Once the application period is over which course is taught when is decided soley on the basis of maximising the number of students that can attend. Perhaps the simplest model is a database table 'application_forms' which includes two text columns; 'course' and 'preferred_dates' with entries that look like this: course: Drama prefered_dates: Sat_22Aug09, Tue_25Aug09, Tue_08Sep09 The data can then be usefully presented in a series of SELECT statements (one for each date): AS SELECT count(*), course FROM application_forms WHERE preferred_dates like '%Sat_22Aug09%' GROUP BY course ORDER BY count DESC; count | course ---+- 7 | Drama 3 | Readers 1 | Self-study but clearly this method doesn't scale very well as the number of dates increases. A single table of results looking something like this would be far better, but how? date | course_suiting_most_applicants | num_applicants - --++--- Sat_22Aug09 | Drama | 7 Tue_25Aug09 | Readers| 4 Any advice/tips/pointers/suggestions for a database design newbie very much appreciated. Regards, Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 2 versions of Postgres on the same machine
Hi This is probably more like linux question but strictly related to PG so I hope somebody can help me. I need to have 8.3 8.4 installed on the same machine (for pg_migrator). As I'm not Linux guru I used to install/update Postgres using yum from PGDG. Is there any clever way to install parallel version of PG using yum or other way (without compilation please :D ) -- 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] 2 versions of Postgres on the same machine
wstrzalka wrote: Hi This is probably more like linux question but strictly related to PG so I hope somebody can help me. I need to have 8.3 8.4 installed on the same machine (for pg_migrator). As I'm not Linux guru I used to install/update Postgres using yum from PGDG. Is there any clever way to install parallel version of PG using yum or other way (without compilation please :D ) not easily, the RPMs that yum fetches have the library paths hard coded. building postgres from source is actually very easy. just follow the README and/or INSTALL in the source tarball. build it for an alternate root path, like /usr/local/pgsql84/... (you'd specify this as an option on the ./configure step), then after its built and installed to that alternate path, and you've run initdb, you would edit postgresql.conf in this alternate ./data directory to change the port to a different port, such as 5433 instead of the default 5432. -- 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] psql command line editor
On 2009-08-17, Bob Gobeille bob.gobei...@hp.com wrote: I use PSQL to set my editor to vi. This works as expected in psql, \e brings up vi and I edit away. Is there any way to set my psql command line editor to also use vi (just like I do with set -o vi in bash)? I can't find this in the docs. psql command-line eritor is readline and being a GNU product it defaults to emacs mode :) do man 3 realine for more info search for INPUTRC - less uses vi bindings :) -- 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] design, plpgsql and sql injection in dynamically generated sql
On Mon, 17 Aug 2009 12:48:21 +0200
Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
I am not sure, if it's possible for you. PostgreSQL 8.4 has EXECUTE
USING clause, it is 100% safe.
Sorry I don't get it.
How can I use USING safely when the substitution involves a table
name?
The examples I've seen just involve column values.
Where is the corresponding fine manual page?
Still I don't get how USING could make safer plpgsql functions...
well... I'm going to check some prejudices I have on pg functions
firts...
I thought that if you passed eg. text to
create or replace function typetest(a int) returns text as
$$
begin
raise notice 'is this an int? %', a;
-- don't do anything else with a
and calling
select * from typetest('tonno');
was going to raise an error anyway.
So somehow I find the example here
http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html
not really helpful in understanding what's going on.
Maybe an example with text comparing a version using quote_literal
and one using USING could be clearer...
or am I completely missing the point?
far from an optimal solution I've built a client side array of
permitted table, key to dynamically build the query on the client
side.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Configuration Question
Greetings: Is there a way to get hold of an environment variable such that it can be referenced in postgresql.conf? In particular, I'd like to be able to point dynamic_library_path to an environment variable defined at the system level as in dynamic_library_path = '$SOURCE:$libdir'. master=# select version(); version PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) TIA -- -- 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] design, plpgsql and sql injection in dynamically generated sql
2009/8/18 Ivan Sergio Borgonovo m...@webthatworks.it:
On Mon, 17 Aug 2009 12:48:21 +0200
Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
I am not sure, if it's possible for you. PostgreSQL 8.4 has EXECUTE
USING clause, it is 100% safe.
Sorry I don't get it.
How can I use USING safely when the substitution involves a table
name?
The examples I've seen just involve column values.
Where is the corresponding fine manual page?
Still I don't get how USING could make safer plpgsql functions...
well... I'm going to check some prejudices I have on pg functions
firts...
some unsafe function:
create or replace function foo(tablename varchar, parameter varchar)
returns int as $$
declare _result integer;
begin
execute 'select i from ' || table_name || ' where x = \'' ||
parameter || '\'' into _result;
return result;
end;
$$ language plpgsql strict;
I thing, so there are two safe variants
create or replace function foo(tablename varchar, parameter varchar)
returns int as $$
declare _result integer;
begin
execute 'select i from ' || quote_ident(table_name) || ' where x = '
|| quote_literal(parameter) into _result;
return _result;
end;
$$ language plpgsql strict;
or
create or replace function foo(tablename varchar, parameter varchar)
returns int as $$
declare _result integer;
begin
execute 'select i from ' || table_name::regclass || ' where x = $1'
using parameter into _result;
return _result;
end;
$$ language plpgsql strict;
USING works like prepared statements.
regards
Pavel Stehule
I thought that if you passed eg. text to
create or replace function typetest(a int) returns text as
$$
begin
raise notice 'is this an int? %', a;
-- don't do anything else with a
and calling
select * from typetest('tonno');
was going to raise an error anyway.
So somehow I find the example here
http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html
not really helpful in understanding what's going on.
Maybe an example with text comparing a version using quote_literal
and one using USING could be clearer...
or am I completely missing the point?
far from an optimal solution I've built a client side array of
permitted table, key to dynamically build the query on the client
side.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
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] comparing NEW and OLD (any good this way?)
On Tue, Aug 18, 2009 at 10:32:35AM +0200, Daniel Verite wrote: Sam Mason wrote: I've just realized another case where it's not consistent; why does the following return true: SELECT row(null) IS NULL; and yet the following false: SELECT row(row(null)) IS NULL; You're intentionally assuming that row(null) IS NULL evaluating to true implies that row(null) can be replaced by NULL. As discussed upthread, this is not the case. But you've still not said how is this useful! I can reformulate maths so that 1+0 1+(0), but this is not useful behavior. Programmers need logical abstractions upon which to build and without them you end up with even more bugs. I think I'm saying that PG should be deliberately breaking specified behavior and go back to pre-8.2 behavior in this regard. But let's run your example with 8.1: # SELECT row(null) IS NULL; ?column? -- t # SELECT row(row(null)) IS NULL; ?column? -- f These are the same results that you say are inconsistant, so pre-8.2 behavior doesn't help here... Doh, that'll learn me--I never actually tried older versions. I was just repeating what the docs said about the behavior changing in 8.2. http://www.postgresql.org/docs/current/static/functions-comparison.html#AEN7444 and was mis-interpreting what it was saying. -- Sam http://samason.me.uk/ -- 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] 2 versions of Postgres on the same machine
On Tue, Aug 18, 2009 at 02:16:20AM -0700, wstrzalka wrote: I need to have 8.3 8.4 installed on the same machine (for pg_migrator). As I'm not Linux guru I used to install/update Postgres using yum from PGDG. If you could use Debian or something based on it (e.g. Ubuntu) then this is how it works by default. You can have as many different major (i.e. 8.2, 8.3 and 8.4) versions installed and running at the same time as you want. Building from source isn't too hard though, it's the keeping it up to date that's more of a fiddle. -- Sam http://samason.me.uk/ -- 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] design, plpgsql and sql injection in dynamically generated sql
On Tue, 18 Aug 2009 12:38:49 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: some unsafe function: I suspected something similar. I think many would appreciate if you put these examples here http://www.okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html and substitute the int example there with the text one. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Best database model for canvassing (and analysing) opinion
On Tue, Aug 18, 2009 at 08:58:12AM +, Sebastian Tennant wrote:
Perhaps the simplest model is a database table 'application_forms' which
includes two text columns; 'course' and 'preferred_dates' with entries that
look like this:
course: Drama
prefered_dates: Sat_22Aug09, Tue_25Aug09, Tue_08Sep09
I'd use the standard date data type for storing dates in, it's much
more useful than text values. Then the conventional method of breaking
single (non-normalized) tables down into smaller normalized tables.
I've got a bit over the top here, but shows what could be done if this
was going to be a bigger database.
-- list of students, if you want
CREATE TABLE students (
student TEXT PRIMARY KEY,
nameTEXT,
email TEXT
);
-- list of courses, again only if you want database to be able to check
-- that people are signing up for valid courses
CREATE TABLE courses (
course TEXT PRIMARY KEY,
runby TEXT,
description TEXT
);
-- which courses are available on which days
CREATE TABLE course_availability (
course TEXT REFERENCES courses,
date DATE,
PRIMARY KEY (course,date)
);
-- which students want to do which courses
CREATE TABLE application_forms (
student TEXT REFERENCES students,
course TEXT REFERENCES courses,
PRIMARY KEY (studentnum,course)
);
-- and on which days do they want to do them
CREATE TABLE application_preferred_date (
student TEXT,
course TEXT,
dateDATE,
PRIMARY KEY (student,course,date),
FOREIGN KEY (student,course) REFERENCES application_forms,
FOREIGN KEY (course,date) REFERENCES course_availability
);
The only table that's really needed to solve your original problem would
be the last one, but the others provide all the checks that the data
is actually going in correctly and may or may not be useful depending
on your problem. The main thing to notice is lots of tables with few
columns, the reason being is that the database normally takes care of
the rows and you, the DBA/programmer, take care of the columns. Thus
the more work you can give to the database the better.
If my student number is 'cs1234' and I want to do a database course, I
would put in:
INSERT INTO application_forms (studentnum,course) VALUES
('cs1234','database 101');
INSERT INTO application_preferred_date (studentnum,course,date) VALUES
('cs1234','database 101','2009-08-26'),
('cs1234','database 101','2009-08-28'),
('cs1234','database 101','2009-08-31');
If I wanted to know how many people wanted to do each course on each
date, I'd just do:
SELECT course, date, COUNT(*)
FROM application_preferred_date
GROUP BY course, date;
Hope that gives you some ideas!
--
Sam http://samason.me.uk/
--
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] multiple paramters in aggregate function
On 18 Aug 2009, at 6:51, Sim Zacks wrote: That would be true if all units were always convertible to mm, but we have volume also, we also have feet etc.. So that the easiest and How did you plan on solving that in your multiple-argument aggregate? Fake their value by adding 0? That's no different for my suggested solution. cleanest thing to do, with out having to throw the all the functions into a case statement is an aggregate function that takes the 2 unit types and then gives back a result. I won't try to force something on you, it's your project after all, but I think you're still seeing only part of the picture I was trying to show you. You have a table with quantities in different units, and you want to summarise those. If you do that with old-fashioned pen paper the first thing you do is convert all your quantities to the same unit so that you can add them properly. That's basic math. In this case however we have far better tools, namely a computer with a database. It's easy to create a table with units and their conversion factor to a standard unit. If you go a bit further you'd create a few tables linking units and how to convert them to each other, which also solves the case where you're not dealing with just distances (the volumes you mention above, for example). Once you have that, it's easy to write a few (immutable!) functions: - convert_to(quantity, unit), which converts a quantity in a given unit to a standard unit, and - convert_from(quantity, unit), which converts a quantity in your standard unit to the given unit. Then you simply write your query as: SELECT convert_from(SUM(convert_to(quantity, unit)), 'inch') FROM table; If you're going for the more complicated approach that can directly convert any unit to any other (provided the record that links them exists) the query gets even simpler. You only need one conversion function in that case: - convert_unit(quantity, from_unit, to_unit) and your query would become: SELECT SUM(convert_unit(quantity, unit, 'inch')) FROM table; If you're worried about accuracy; the different unit styles have fixed conversion factors with a finite accuracy. For example; 1 inch is 25.40 mm - that's accurate. If you take their accuracy into account when defining your quantity columns/variables you won't get any rounding errors caused by the unit conversion. Considering you're using at least one of those functions in an aggregate it's probably worth implementing them in C instead of for example pl/pgsql, but the latter is easier to test the concept. And you get the added bonus of being able to convert units anywhere you like. If you have customers who prefer seeing their quantities measured in imperial units and customers preferring standard units you can serve them both. It adds value to your project; You may recall a recent space probe that went off in the wrong direction because it had a mix of imperial and standard units used in its design and someone somewhere forgot to correct for that in a piece of software... In fact, having these tables and functions available would be useful to many people. It would make a great pgfoundry project I think. Well I don't think you got Alban's suggestion right... What he was trying to say was: - use a regular (not aggregated) function to convert all measures to mm - use the normal SUM() to sum those value - use another regular function to convert from mm to whatever select mm_to_m(sum(convert_to_mm(measure))) from a Which is easier than my solution -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Alban Hertroys -- Screwing up is the correct approach to attaching something to the ceiling. !DSPAM:737,4a8a8ee410137968484637! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] There are procedures in Postgres 8.3?
Hi, I have some functions developed in postgres to work as a procedure. But now I see in the Postgres Studio that we have the option to create a procedure... So my question? there are procedures in Postgres 8.3? If yes, what the correct syntax to write procedures in Postgres 8.3? Best Regards, André.
Re: [GENERAL] Best database model for canvassing (and analysing) opinion
Sam Mason wrote: SELECT course, date, COUNT(*) FROM application_preferred_date GROUP BY course, date; Hope that gives you some ideas! the problem as stated is more complex than that. A student could, in theory, pick several different courses on the same dates on the assumption that he doesn't care what course on which date. so if the optimizer/solver has counted this student for course1 on date1, he can't be counted for course2 on that same date, only on an alternate date he may have also specified. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] index pg_authid_rolname_index is not a btree
Hard disk containing PostgreSql 8.1 database on Windows crashes and there was no new backup copy. I installed 8.1.9 to new computer and copied data directory from crashed disk to it. data directory contains a lot of files with a lot of data. Trying to connect to template0 or any other database in this cluster causes error Error connecting to the server: FATAL: index pg_authid_rolname_index is not a btree How to recover data from this cluster ? Andrus. -- 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] There are procedures in Postgres 8.3?
2009/8/18 Andre Lopes lopes80an...@gmail.com: Hi, I have some functions developed in postgres to work as a procedure. But now I see in the Postgres Studio that we have the option to create a procedure... So my question? there are procedures in Postgres 8.3? If yes, what the correct syntax to write procedures in Postgres 8.3? No, PostgreSQL doesn't support stored procedures - only stored functions. I don't know Postgres Studio - It maybe support EnterpriseDB, that supports stored procedures in PL/SQL syntax. regards Pavel Stehule Best Regards, André. -- 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] Best database model for canvassing (and analysing) opinion
On Tue, Aug 18, 2009 at 05:24:52AM -0700, John R Pierce wrote: Sam Mason wrote: SELECT course, date, COUNT(*) FROM application_preferred_date GROUP BY course, date; the problem as stated is more complex than that. A student could, in theory, pick several different courses on the same dates on the assumption that he doesn't care what course on which date. Yup so if the optimizer/solver has counted this student for course1 on date1, he can't be counted for course2 on that same date, only on an alternate date he may have also specified. Huh, fun optimisation problem. There must be standard solutions to it though and I'm pretty sure it's something I'd like to solve outside the database. Or am I missing something else? I was just answering call for advice/tips/pointers/suggestions for a database design newbie. -- Sam http://samason.me.uk/ -- 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] design, plpgsql and sql injection in dynamically generated sql
2009/8/18 Ivan Sergio Borgonovo m...@webthatworks.it: On Tue, 18 Aug 2009 12:38:49 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: some unsafe function: I suspected something similar. I think many would appreciate if you put these examples here http://www.okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html and substitute the int example there with the text one. actualized http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html regards Pavel thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Function Logging
On Monday 17 August 2009 8:50:09 pm Andrew Bartley wrote: So the information i have thus far is that, I am not easily able to log the statements from a function. Does anyone know why it was removed... that is it 7.2 logged this information. Thanks Again Andrew Bartley You are going to have to show an example of what you want and/or provide a better description of what you wish to achieve. At this point I ,for one, am confused as to what you want. -- Adrian Klaver akla...@comcast.net -- 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] Configuration Question
v8.3 FAQ_Solaris To point it to the right location, set the LD_LIBRARY_PATH environment variable, e.g., LD_LIBRARY_PATH=/usr/sfw/lib:/opt/sfw/lib:/usr/local/lib export LD_LIBRARY_PATH and restart configure. You will also have to keep this setting whenever you run any of the installed PostgreSQL programs. Alternatively, set the environment variable LD_RUN_PATH. See the ld(1) man page for more information. either LD_LIBRARY_PATH or LD_RUN_PATH should contain binary folder Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: te...@chosen-ones.org To: pgsql-general@postgresql.org Subject: [GENERAL] Configuration Question Date: Tue, 18 Aug 2009 06:32:25 -0400 Greetings: Is there a way to get hold of an environment variable such that it can be referenced in postgresql.conf? In particular, I'd like to be able to point dynamic_library_path to an environment variable defined at the system level as in dynamic_library_path = '$SOURCE:$libdir'. master=# select version(); version PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) TIA -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows Live: Keep your friends up to date with what you do online. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009
Re: [GENERAL] Configuration Question
On Tuesday 18 August 2009 09:28, Martin Gainty wrote: v8.3 FAQ_Solaris To point it to the right location, set the LD_LIBRARY_PATH environment variable, e.g., LD_LIBRARY_PATH=/usr/sfw/lib:/opt/sfw/lib:/usr/local/lib export LD_LIBRARY_PATH and restart configure. You will also have to keep this setting whenever you run any of the installed PostgreSQL programs. Alternatively, set the environment variable LD_RUN_PATH. See the ld(1) man page for more information. either LD_LIBRARY_PATH or LD_RUN_PATH should contain binary folder Martin Gainty OK. Thanks for the help. I'll give this a try. From: te...@chosen-ones.org To: pgsql-general@postgresql.org Subject: [GENERAL] Configuration Question Date: Tue, 18 Aug 2009 06:32:25 -0400 Greetings: Is there a way to get hold of an environment variable such that it can be referenced in postgresql.conf? In particular, I'd like to be able to point dynamic_library_path to an environment variable defined at the system level as in dynamic_library_path = '$SOURCE:$libdir'. master=# select version(); version - --- PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) TIA -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows Live: Keep your friends up to date with what you do online. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:O N:WL:en-US:SI_SB_online:082009 -- -- 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] Configuration Question
Terry Lee Tucker te...@chosen-ones.org writes: Is there a way to get hold of an environment variable such that it can be referenced in postgresql.conf? No, but you could perhaps set that GUC on the postmaster command line instead. postmaster ... --dynamic_library_path=$SOURCE:$$libdir 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] comparing NEW and OLD (any good this way?)
Sam Mason wrote: You're intentionally assuming that row(null) IS NULL evaluating to true implies that row(null) can be replaced by NULL. As discussed upthread, this is not the case. But you've still not said how is this useful! To me, IS NULL applied to rows, as a test of combined-nullnesss of the columns inside the row, doesn't indeed look like something I'd use on a regular basis, if at all. But I'll use IS DISTINCT FROM NULL on records. I sympathize with the opinion that the standard hijacks the IS NULL operator for rows in a way that is problematic (though not unworkable). But who cares if it's not useful to some, or even to the majority? The standard opted for that definition years ago, and also PG opted to implement it. It's too late. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgre RAISE NOTICE and PHP
Hi, I'm developing a function with some checks, for example... to check if the e-mail is valid or not. If the e-mail is not valid I put a line with RAISE NOTICE 'E-mail not valid'. I need to know if it is possible to show this RAISE NOTICE when I run this function from PHP. Best Regards, André.
Re: [GENERAL] 2 versions of Postgres on the same machine
On Tue, 2009-08-18 at 02:16 -0700, wstrzalka wrote: Is there any clever way to install parallel version of PG using yum or other way (without compilation please :D ) There is no way to do it with RPMS :( Install one of the versions using yum, and compile the other please. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Postgre RAISE NOTICE and PHP
Andre == Andre Lopes lopes80an...@gmail.com writes: Andre I'm developing a function with some checks, for example... to check if the Andre e-mail is valid or not. How are you hoping to do this? The regex to validate an email address syntactically is pretty large: http://ex-parrot.com/~pdw/Mail-RFC822-Address.html And no, I'm not kidding. If your regex is smaller than that, you aren't validating email... you're validating something kinda like email. For example, fredbar...@stonehenge.com is a valid email address. (Go ahead, try it... it has an autoresponder.) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 mer...@stonehenge.com URL:http://www.stonehenge.com/merlyn/ Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc. See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PQgetlength vs. octet_length()
This thread was originally posted (incorrectly by me) to the hackers mailing list. Moving the discussion to the gerenal. Hi Greg, That is what Pierre pointed out, and you are both right. I am using the text mode. But it seems pretty crazy that a 140meg bit of data goes to 1.3 gigs. Does that seem a bit excessive? I avoided the binary mode because that seemed to be rather confusing when having to deal with non-bytea data types. The docs make it sound like binary mode should be avoided because what you get back for a datetime varies per platform. Thanks, Michael. On Tue, Aug 18, 2009 at 12:15 PM, Greg Stark gsst...@mit.edu wrote: On Tue, Aug 18, 2009 at 4:04 PM, Michael Clarkcodingni...@gmail.com wrote: Hello - am I in the wrong mailing list for this sort of problem? :- Probably but it's also a pretty technical point and you're programming in C so it's kind of borderline. If you're using text-mode then your datum that you're getting from libpq is a text representation of the datum. For bytea in released versions that means anything which isn't a printable ascii character will be octal encoded like \123. You can use PQunescapeBytea to unescape it. If you use binary encoding then you don't have to deal with that. Though I seem to recall there is still a gotcha you have to worry about if there are nul bytes in your datum. I don't recall exactly what that meant you had to do though. -- greg http://mit.edu/~gsstark/resume.pdf
Re: [GENERAL] PQgetlength vs. octet_length()
On Tue, Aug 18, 2009 at 6:39 PM, Michael Clarkcodingni...@gmail.com wrote: But it seems pretty crazy that a 140meg bit of data goes to 1.3 gigs. Does that seem a bit excessive? From what you posted earlier it looked like it was turning into about 500M which sounds about right. Presumably either libpq or your code is holding two copies of it in ram at some point in the process. 8.5 will have an option to use a denser hex encoding but it will still be 2x as large as the raw data. I avoided the binary mode because that seemed to be rather confusing when having to deal with non-bytea data types. The docs make it sound like binary mode should be avoided because what you get back for a datetime varies per platform. There are definitely disadvantages. Generally it requires you to know what the binary representation of your data types is and they're not all well documented or guaranteed not to change in the future. I wouldn't recommend someone try to decode a numeric or a postgres array for example. And floating point numbers are platform dependent. But bytea is a case where it seems more natural to use binary than text representation. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Unit conversion database (was: multiple paramters in aggregate function)
Hello all,
Inspired by the original discussion on aggregating quantities of
different units I made a start at a unit conversion database and the
result is here: http://solfertje.student.utwente.nl/documents/units.sql
This is not a complete implementation, I just thought I'd show you
what I got so far and hope you have some ideas about a few problems
I'm facing.
What it can do is convert a bunch of units to and fro, including to
the same unit, using a conversion factor and a pair of offsets to
adjust for zero-point differences (°C to °F or K for example).
By default it installs itself in a schema named 'units'. At the end of
the script is a query that converts '23' (which happened to be the
temperature here while I was testing) from every known unit to every
other known unit. That's meant as a test, but it also makes verifying
correctness fairly easy.
Problem areas are:
- It doesn't contain every possible conversion yet.
Some units are probably just plain wrong too. I don't know every unit
in the list, that's why. I'm especially unfamiliar with imperial units
and some of the more esoteric units. Corrections and additions are
welcome.
- It can't handle unit scaling yet ('mm' to 'm' for example).
There are some units in there that are scaled by default ('kg' is the
standard unit for mass and not 'g'), and some units seem to be not
scalable at all (ever heard of 'mK' - 'milliKelvin'?). This may be
solved by adding a base_scale column which could be NULL if not
applicable.
- Some units are combinations of multiple base-units that would
require parsing the combined unit to determine how to scale or convert
parts of it. I haven't found a good way of handling that yet, maybe I
just shouldn't... I have a feeling that at the very least parsing
units should only happen if the unit isn't a base-unit, which can
simply be flagged.
The latter two issues seem to require a unit parser, which seems a bit
heavy-weight. Or I should just drop all the combined units and only
deal with base-units. Suggestions or even code are welcome.
On 18 Aug 2009, at 13:22, Alban Hertroys wrote:
In this case however we have far better tools, namely a computer
with a database. It's easy to create a table with units and their
conversion factor to a standard unit. If you go a bit further you'd
create a few tables linking units and how to convert them to each
other, which also solves the case where you're not dealing with just
distances (the volumes you mention above, for example).
Once you have that, it's easy to write a few (immutable!) functions:
- convert_to(quantity, unit), which converts a quantity in a given
unit to a standard unit, and
- convert_from(quantity, unit), which converts a quantity in your
standard unit to the given unit.
Then you simply write your query as:
SELECT convert_from(SUM(convert_to(quantity, unit)), 'inch') FROM
table;
If you're going for the more complicated approach that can directly
convert any unit to any other (provided the record that links them
exists) the query gets even simpler. You only need one conversion
function in that case:
- convert_unit(quantity, from_unit, to_unit)
and your query would become:
SELECT SUM(convert_unit(quantity, unit, 'inch')) FROM table;
Alban Hertroys
--
Screwing up is the correct approach to attaching something to the
ceiling.
!DSPAM:737,4a8aec0910131445318212!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: PQgetlength vs. octet_length()
On Tue, Aug 18, 2009 at 1:48 PM, Greg Stark gsst...@mit.edu wrote: On Tue, Aug 18, 2009 at 6:39 PM, Michael Clarkcodingni...@gmail.com wrote: But it seems pretty crazy that a 140meg bit of data goes to 1.3 gigs. Does that seem a bit excessive? From what you posted earlier it looked like it was turning into about 500M which sounds about right. Presumably either libpq or your code is holding two copies of it in ram at some point in the process. From what I saw, stopped at this line in my code running through gdb: const char *valC = PQgetvalue(result, rowIndex, i); my mem usage was 300megs. Stepping over this line it went to 1.3 gigs. Unless there is some way to misconfigure something, I can't think how my code could do that. I will profile it and see if I can tell who is holding on to that memory. 8.5 will have an option to use a denser hex encoding but it will still be 2x as large as the raw data. Sweet! I avoided the binary mode because that seemed to be rather confusing when having to deal with non-bytea data types. The docs make it sound like binary mode should be avoided because what you get back for a datetime varies per platform. There are definitely disadvantages. Generally it requires you to know what the binary representation of your data types is and they're not all well documented or guaranteed not to change in the future. I wouldn't recommend someone try to decode a numeric or a postgres array for example. And floating point numbers are platform dependent. But bytea is a case where it seems more natural to use binary than text representation. To do something like this, I guess it would be best for my wrapper to being to detect when I have a bytea column in a table and do 2 fetchs, one in text for all other columns, and one in binary for the bytea column. Is this the best way to handle that do you think? Thanks, Michael.
[GENERAL] Any justification for sequence table vs. native sequences?
I just stumbled across this table in a database developed by a collegue: field_name | next_value | lock +-+ id_alert| 500010 | FREE id_page | 500087 | FREE id_group| 500021 | FREE These id_ fields correspond to the primary keys on their respective tables. Instead of making them of type serial, they are of bigints with a NOT NULL constraint, and the sequence numbers are being managed by the application (not the database.) I googled around a bit trying to find an argument either in favour of or against this approach, but didn't find much. I can't see the advantage to this approach over using native PostgreSQL sequences, and it seems that there are plenty of disadvantages (extra database queries to find the next sequence number for one, and a locking mechanism that doesn't play well with multiuser updates for two.) Can anyone comment on this? Has anyone ever had to apply a pattern like this when native sequences weren't sufficient? If so, what was the justification? Thanks, -- *Doug Gorley* | doug.gor...@gmail.com mailto:doug.gor...@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] Any justification for sequence table vs. native sequences?
On 08/18/2009 01:14 PM, Doug Gorley wrote: I just stumbled across this table in a database developed by a collegue: field_name | next_value | lock +-+ id_alert| 500010 | FREE id_page | 500087 | FREE id_group| 500021 | FREE These id_ fields correspond to the primary keys on their respective tables. Instead of making them of type serial, they are of bigints with a NOT NULL constraint, and the sequence numbers are being managed by the application (not the database.) I googled around a bit trying to find an argument either in favour of or against this approach, but didn't find much. I can't see the advantage to this approach over using native PostgreSQL sequences, and it seems that there are plenty of disadvantages (extra database queries to find the next sequence number for one, and a locking mechanism that doesn't play well with multiuser updates for two.) Can anyone comment on this? Has anyone ever had to apply a pattern like this when native sequences weren't sufficient? If so, what was the justification? One justification I can see is if there would otherwise be an unmanageably large number of individual sequences. I have an app in which there is a table containing things that have a type code. There can be an arbitrary number of type codes and in practice may be many dozens. Each thing also has a user-visible id number which users normally assign sequentially within each type. The app currently creates a sequence for each type and uses them to provide a default values for the id numbers. I am considering changing this to something like you describe. In my case there is a low insert rate so contention (which I read is the biggest problem with this approach) should not be an issue. -- 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] Any justification for sequence table vs. native sequences?
Doug Gorley doug.gor...@gmail.com wrote: I just stumbled across this table in a database developed by a collegue: field_name | next_value | lock +-+ id_alert| 500010 | FREE id_page | 500087 | FREE id_group| 500021 | FREE These id_ fields correspond to the primary keys on their respective tables. Instead of making them of type serial, they are of bigints with a NOT NULL constraint, and the sequence numbers are being managed by the application (not the database.) I googled around a bit trying to find an argument either in favour of or against this approach, but didn't find much. I can't see the advantage to this approach over using native PostgreSQL sequences, and it seems that there are plenty of disadvantages (extra database queries to find the next sequence number for one, and a locking mechanism that doesn't play well with multiuser updates for two.) Can anyone comment on this? Has anyone ever had to apply a pattern like this when native sequences weren't sufficient? If so, what was the justification? The only reason I can think to add that much complexity is to ensure gap-free sequences, which Postgres' internal sequences do _not_ guarantee. And yes, it's pretty much guaranteed to be slower than built in sequences, with blocking when multiple threads want a sequence all at the same time. I'm rather concerned by the third column, as I'm not sure what his implementation approach is, and I'm concerned that he's using a home-brewed locking mechanism instead of using table locks. -- Bill Moran http://www.potentialtech.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] Postgre RAISE NOTICE and PHP
On Tue, Aug 18, 2009 at 10:57 AM, Andre Lopeslopes80an...@gmail.com wrote: Hi, I'm developing a function with some checks, for example... to check if the e-mail is valid or not. If the e-mail is not valid I put a line with RAISE NOTICE 'E-mail not valid'. I need to know if it is possible to show this RAISE NOTICE when I run this function from PHP. http://www.php.net/manual/en/function.pg-last-notice.php example plpgsql function: create or replace function tester() returns int language plpgsql as $$ BEGIN raise notice 'whoops'; return 1; END $$; example php: ?php $conn = pg_connect(dbname=smarlowe); $res = pg_query(select tester()); print pg_last_notice($conn); print \n; ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Access Control System - Design
Hi, I need to implement a Access Control System, but I don't have any clue of what it is the ideal system... I will try to explain my problem... I have 4 levels of users in my web application, Super Administrator, Administrator, Manager and Worker. The database have data from more than one company. But all different companies belong to the same group of bussiness. So... the . Super Administrator will access to the data of all companies . Administrator will access to the data of only one company(his company) . Manager will access to the data of a region of only one company AND all actions must be confirmed by the Administrator. . Worker will access only to the data that he inserts to the system AND all actions must be confirmed by the Manager of his region. Here I have the requirements of the Access Control System and the requirements of the Workflow. I have read this document, but I don't know wich system to use. Here is the document: http://www.tonymarston.net/php-mysql/role-based-access-control.html What is your advice for me? There are some open-source systems ready to use? Best Regards, André.
[GENERAL] PL/PGSQL: why IF test the whole condition before failing or not?
Hi list, I'm having trouble with - believe me! - the IF operator on a PL/PGSQL function used by a trigger. I'm using one unique function to process the three triggers events (delete, update and insert), but when I reference OLD or NEW on a IF CONDITION, I get an error even when testing BEFORE if it's a UPDATE event or not. example: IF TG_OP = 'UPDATE' AND OLD.field != NEW.field THEN ... END IF; The question is: if the trigger was not fired by an UPDATE event, shouldn't it make the first test and then ignore the rest of the condition? I know that some languages work like this (testing the whole condition) while others don't, but I searched for an alternative without success. Any advice would be much appreciated! Thanks in advance -- 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] PL/PGSQL: why IF test the whole condition before failing or not?
Suporte PK fkno...@gmail.com writes: IF TG_OP = 'UPDATE' AND OLD.field != NEW.field THEN ... The question is: if the trigger was not fired by an UPDATE event, shouldn't it make the first test and then ignore the rest of the condition? No. This is a very very common error. The behavior is not as short-circuity as you'd think. Break it into two IFs. 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] Any justification for sequence table vs. native sequences?
Bill Moran wmo...@potentialtech.com writes: And yes, it's pretty much guaranteed to be slower than built in sequences, with blocking when multiple threads want a sequence all at the same time. It's also going to create a vacuum bottleneck unless the insert rate is quite low, because each ID assignment will create another dead row in the sequence management table. I'm rather concerned by the third column, as I'm not sure what his implementation approach is, and I'm concerned that he's using a home-brewed locking mechanism instead of using table locks. Indeed, that looks a bit scary/pointless. You could at least use SELECT FOR UPDATE to lock the rows. 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] index pg_authid_rolname_index is not a btree
Andrus Moor wrote: Hard disk containing PostgreSql 8.1 database on Windows crashes and there was no new backup copy. I installed 8.1.9 to new computer and copied data directory from crashed disk to it. data directory contains a lot of files with a lot of data. Trying to connect to template0 or any other database in this cluster causes error Error connecting to the server: FATAL: index pg_authid_rolname_index is not a btree You can get around that particular problem by reindexing the pg_authid table. But my guess is that you'll find that there's corruption elsewhere that's not so easily recoverable ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] text type has no default operator class for GIN?
CREATE INDEX ufile_name_search ON public.uploadtree USING GIN (ufile_name); ERROR: data type text has no default operator class for access method gin HINT: You must specify an operator class for the index or define a default operator class for the data type. This is on a new 8.3 install. Why is this happening? Isn't creating a GIN index on a text type a common thing to do? I'm not seeing any mention of this in 8.3.7 docs cha 12 (Full Text Search). Do I really have to CREATE OPERATOR CLASS to make this work? I see http://www.postgresql.org/docs/8.3/interactive/sql-createopclass.html many thanks, bob -- 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] Access Control System - Design
Andre Lopes wrote: Hi, I need to implement a Access Control System, but I don't have any clue of what it is the ideal system... I will try to explain my problem... I have 4 levels of users in my web application, Super Administrator, Administrator, Manager and Worker. The database have data from more than one company. But all different companies belong to the same group of bussiness. So... the . Super Administrator will access to the data of all companies . Administrator will access to the data of only one company(his company) . Manager will access to the data of a region of only one company AND all actions must be confirmed by the Administrator. . Worker will access only to the data that he inserts to the system AND all actions must be confirmed by the Manager of his region. your workflow management sounds like it will need to be enforced by the business logic of your web application, as postgres roles won't have anywhere near that level granularity, nor will they support any sort of approval requirements. most likely, the business logic will just use one postgres role which grants it access to the whole database, and all finer granularity management, including your approval rules will be in that business logic. You'd have table(s) for your workers and managers and administrators with links to their parent approvals. no users except the database administrators would have direct access to the actual database, instead, all production operations would pass through your business logic layer. as to how you implement this approval process, well, pending change requests could go into a pending approval queue/table and generate the appropriate notifications, then when the various managers/administrators browse and make these approvals, the state is advanced until the actual changes can be committed to the real data tables. -- 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] text type has no default operator class for GIN?
On Aug 18, 2009, at 3:46 PM, Gobeille, Robert wrote:
CREATE INDEX ufile_name_search ON public.uploadtree USING GIN
(ufile_name);
ERROR: data type text has no default operator class for access method
gin
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
This is on a new 8.3 install.
Why is this happening? Isn't creating a GIN index on a text type a
common thing to do?
I'm not seeing any mention of this in 8.3.7 docs cha 12 (Full Text
Search).
Do I really have to CREATE OPERATOR CLASS to make this work?
I see http://www.postgresql.org/docs/8.3/interactive/sql-createopclass.html
I still don't understand the above, but the following works:
create index ufile_name_ginidx on uploadtree using
gin(to_tsvector('english', ufile_name));
Bob
--
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] Postgre RAISE NOTICE and PHP
Andre, See this PHP page: http://www.php.net/manual/en/function.pg-last-notice.php Andre Lopes wrote: Hi, I'm developing a function with some checks, for example... to check if the e-mail is valid or not. If the e-mail is not valid I put a line with RAISE NOTICE 'E-mail not valid'. I need to know if it is possible to show this RAISE NOTICE when I run this function from PHP. Best Regards, André. -- 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] text type has no default operator class for GIN?
On Tue, Aug 18, 2009 at 03:50:47PM -0600, Bob Gobeille wrote:
On Aug 18, 2009, at 3:46 PM, Gobeille, Robert wrote:
CREATE INDEX ufile_name_search ON public.uploadtree USING GIN
(ufile_name);
ERROR: data type text has no default operator class for access method
gin
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
[..]
I still don't understand the above, but the following works:
create index ufile_name_ginidx on uploadtree using
gin(to_tsvector('english', ufile_name));
Not sure if understand very well myself, but GIN indexes can only speed
up specific access patterns and these are exposed through various
different operators.
When PG refuses to create a GIN index on a plain TEXT column it's saying
that it doesn't how to use those operators with a values of TEXT type.
As soon as you pull this value apart (with the to_tsvector) you end up
with something that PG can get some traction on and all is good.
Maybe a useful question to ask is, what are you expecting PG do to when
you create a GIN index on this TEXT column?
--
Sam http://samason.me.uk/
--
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] text type has no default operator class for GIN?
Sam Mason s...@samason.me.uk writes: On Tue, Aug 18, 2009 at 03:50:47PM -0600, Bob Gobeille wrote: CREATE INDEX ufile_name_search ON public.uploadtree USING GIN (ufile_name); ERROR: data type text has no default operator class for access method gin Not sure if understand very well myself, but GIN indexes can only speed up specific access patterns and these are exposed through various different operators. What GIN indexes are good for is indexing equality queries on the components of something the database otherwise thinks of as a single object. For instance you can GIN-index searches for arrays containing a particular value as a member. Now type text doesn't have any built-in notion of a component, other than individual characters, which aren't normally that interesting to search for. What I suppose the OP has in mind is full-text searching, which is looking for component *words*. But word is a very language- and context-dependent concept. And defining which words are to be considered equal for searching purposes is even more so. If we'd hard-wired one notion of word into datatype text, it wouldn't be very flexible. The point of the tsvector layer is to have a configurable way to extract searchable words from a chunk of text. There are also some implementation advantages like not having to repeat that processing constantly during a search --- but the main point is having a place to define what a word is and what search equality means. 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] Function Logging
On 18/08/2009 9:26 PM, Adrian Klaver wrote: On Monday 17 August 2009 8:50:09 pm Andrew Bartley wrote: So the information i have thus far is that, I am not easily able to log the statements from a function. Does anyone know why it was removed... that is it 7.2 logged this information. Thanks Again Andrew Bartley You are going to have to show an example of what you want and/or provide a better description of what you wish to achieve. At this point I ,for one, am confused as to what you want. It sounds to me like the OP is saying that in 7.2 log_statement logged each statement of a PL/PgSQL function where in 8.3 it does not. -- Craig Ringer -- 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] 2 versions of Postgres on the same machine
On 08/18/2009 10:34 PM, Devrim GÜNDÜZ wrote: On Tue, 2009-08-18 at 02:16 -0700, wstrzalka wrote: Is there any clever way to install parallel version of PG using yum or other way (without compilation please :D ) There is no way to do it with RPMS :( Install one of the versions using yum, and compile the other please. Or use EnterpriseDB's one-click installer for PostgreSQL 8.3 and PostgreSQL 8.4, you will have 2 parallel version of server running simultaneously without needing to compile from source. -- Regards, Sachin Srivastava www.enterprisedb.com
Re: [GENERAL] Idle processes chewing up CPU?
Hi Craig/Tom, I've managed to trap the full stack trace this time - 2 processes chewing up 25% each (1 core each on a quad core server), while SELECT * FROM pg_stat_activity revealed they were IDLE. I also confirmed that the two runaway processes were started by a developer remotely connecting pgAdmin via SSL. It appears that no actual queries were run, just the connection established. Unfortunately I couldn't confirm if the connections were dirty disconnected. Relevant DLL versions are: 8.3\bin\LIBEAY32.DLL - 0.9.8.5 8.3\bin\SSLEAY32.DLL - 0.9.8.5 Other DLLS - standard for Windows 2003 Server SP2 The juiciest stack traces I captured (in no particular order) were: ntkrnlpa.exe!KiUnexpectedInterrupt+0x48 ntkrnlpa.exe!KeWaitForSingleObject+0x346 ntkrnlpa.exe!ZwYieldExecution+0x3514 hal.dll!KfRaiseIrql+0xe5 hal.dll!KeRaiseIrqlToSynchLevel+0x8d hal.dll!HalEndSystemInterrupt+0x67 hal.dll!HalInitializeProcessor+0x856 LIBEAY32.dll!lh_doall_arg+0x1c4 ntkrnlpa.exe!KiUnexpectedInterrupt+0x48 ntkrnlpa.exe!KeWaitForSingleObject+0x346 ntkrnlpa.exe!ZwYieldExecution+0x3514 hal.dll!KfRaiseIrql+0xe5 hal.dll!KeRaiseIrqlToSynchLevel+0x8d hal.dll!KfLowerIrql+0x62 ntkrnlpa.exe!ZwYieldExecution+0x163a ntkrnlpa.exe!KeInsertQueueApc+0x57 ntkrnlpa.exe!IoCancelIrp+0x27d hal.dll!HalEndSystemInterrupt+0x6e hal.dll!HalInitializeProcessor+0x856 ntkrnlpa.exe!IofCallDriver+0x45 ntkrnlpa.exe!NtWriteFile+0x2943 ntkrnlpa.exe!NtWriteFile+0x36cb ntkrnlpa.exe!NtDeviceIoControlFile+0x2a ntkrnlpa.exe!KeReleaseInStackQueuedSpinLockFromDpcLevel+0xb64 ntdll.dll!KiFastSystemCallRet WS2_32.dll!WSARecv+0x65 WSOCK32.dll!recv+0x31 LIBEAY32.dll!BIO_sock_should_retry+0x57 postgres.exe!my_sock_read+0x1b LIBEAY32.dll!BIO_read+0x6f SSLEAY32.dll!SSLv3_client_method+0x1ee1 SSLEAY32.dll!SSLv3_client_method+0x22ea mswsock.dll!StartWsdpService+0x500 SSLEAY32.dll!SSLv3_client_method+0x225a SSLEAY32.dll!SSLv3_client_method+0x2a15 postgres.exe!pgwin32_waitforsinglesocket+0x1ed postgres.exe!secure_read+0x26 postgres.exe!pq_recvbuf+0x71 postgres.exe!pq_getbyte+0x15 postgres.exe!SocketBackend+0x6 postgres.exe!PostgresMain+0xbf8 postgres.exe!BackendRun+0x200 postgres.exe!SubPostmasterMain+0x21d postgres.exe!main+0x177 postgres.exe!__tmainCRTStartup+0x10f kernel32.dll!ProcessIdToSessionId+0x209 ntkrnlpa.exe!ZwYieldExecution+0x163a ntkrnlpa.exe!KeSetEvent+0xcc ntkrnlpa.exe!PsLookupThreadByThreadId+0x54bc ntkrnlpa.exe!KiDeliverApc+0xbb ntkrnlpa.exe!ZwYieldExecution+0x3801 ntkrnlpa.exe!KeWaitForSingleObject+0x346 ntkrnlpa.exe!ZwYieldExecution+0x3514 ntkrnlpa.exe!KiCheckForKernelApcDelivery+0x1c ntkrnlpa.exe!wctomb+0x4229 ntkrnlpa.exe!IofCallDriver+0x45 ntkrnlpa.exe!NtWriteFile+0x2943 ntkrnlpa.exe!NtWriteFile+0x36cb ntkrnlpa.exe!NtDeviceIoControlFile+0x2a ntkrnlpa.exe!KeReleaseInStackQueuedSpinLockFromDpcLevel+0xb64 ntdll.dll!KiFastSystemCallRet WS2_32.dll!WSARecv+0x65 WSOCK32.dll!recv+0x31 LIBEAY32.dll!BIO_sock_should_retry+0x57 postgres.exe!my_sock_read+0x1b LIBEAY32.dll!BIO_read+0x6f SSLEAY32.dll!SSLv3_client_method+0x1ee1 SSLEAY32.dll!SSLv3_client_method+0x22ea mswsock.dll!StartWsdpService+0x500 SSLEAY32.dll!SSLv3_client_method+0x225a SSLEAY32.dll!SSLv3_client_method+0x2a15 postgres.exe!pgwin32_waitforsinglesocket+0x1ed postgres.exe!secure_read+0x26 postgres.exe!pq_recvbuf+0x71 postgres.exe!pq_getbyte+0x15 postgres.exe!SocketBackend+0x6 postgres.exe!PostgresMain+0xbf8 postgres.exe!BackendRun+0x200 postgres.exe!SubPostmasterMain+0x21d postgres.exe!main+0x177 postgres.exe!__tmainCRTStartup+0x10f kernel32.dll!ProcessIdToSessionId+0x209 ntkrnlpa.exe!KiUnexpectedInterrupt+0x48 ntkrnlpa.exe!KeWaitForSingleObject+0x346 ntkrnlpa.exe!ZwYieldExecution+0x3514 ntkrnlpa.exe!KiCheckForKernelApcDelivery+0x1c ntkrnlpa.exe!NtWriteFile+0x3195 ntkrnlpa.exe!NtDeviceIoControlFile+0x2a ntkrnlpa.exe!KeReleaseInStackQueuedSpinLockFromDpcLevel+0xb64 ntdll.dll!KiFastSystemCallRet WS2_32.dll!WSARecv+0x65 WSOCK32.dll!recv+0x31 LIBEAY32.dll!BIO_sock_should_retry+0x57 postgres.exe!my_sock_read+0x1b LIBEAY32.dll!BIO_read+0x6f SSLEAY32.dll!SSLv3_client_method+0x1ee1 SSLEAY32.dll!SSLv3_client_method+0x22ea mswsock.dll!StartWsdpService+0x500 SSLEAY32.dll!SSLv3_client_method+0x225a SSLEAY32.dll!SSLv3_client_method+0x2a15 postgres.exe!pgwin32_waitforsinglesocket+0x1ed postgres.exe!secure_read+0x26 postgres.exe!pq_recvbuf+0x71 postgres.exe!pq_getbyte+0x15 postgres.exe!SocketBackend+0x6 postgres.exe!PostgresMain+0xbf8 postgres.exe!BackendRun+0x200 postgres.exe!SubPostmasterMain+0x21d postgres.exe!main+0x177 postgres.exe!__tmainCRTStartup+0x10f kernel32.dll!ProcessIdToSessionId+0x209 I'd appreciate any help diagnosing this problem - cutting off remote access via SSL isn't the ideal solution. Regards, -Brendan -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Wednesday, 5 August 2009 5:44 PM To: Brendan Hill Cc: 'Tom Lane'; pgsql-general@postgresql.org Subject: RE: [GENERAL] Idle processes chewing up CPU? On Wed, 2009-08-05 at 16:44 +1000, Brendan
Re: [GENERAL] Idle processes chewing up CPU?
On 19/08/2009 12:31 PM, Brendan Hill wrote: Hi Craig/Tom, I've managed to trap the full stack trace this time The common part of those traces is: ntdll.dll!KiFastSystemCallRet WS2_32.dll!WSARecv+0x65 WSOCK32.dll!recv+0x31 LIBEAY32.dll!BIO_sock_should_retry+0x57 postgres.exe!my_sock_read+0x1b LIBEAY32.dll!BIO_read+0x6f SSLEAY32.dll!SSLv3_client_method+0x1ee1 SSLEAY32.dll!SSLv3_client_method+0x22ea mswsock.dll!StartWsdpService+0x500 SSLEAY32.dll!SSLv3_client_method+0x225a SSLEAY32.dll!SSLv3_client_method+0x2a15 postgres.exe!pgwin32_waitforsinglesocket+0x1ed postgres.exe!secure_read+0x26 postgres.exe!pq_recvbuf+0x71 postgres.exe!pq_getbyte+0x15 postgres.exe!SocketBackend+0x6 postgres.exe!PostgresMain+0xbf8 postgres.exe!BackendRun+0x200 postgres.exe!SubPostmasterMain+0x21d postgres.exe!main+0x177 postgres.exe!__tmainCRTStartup+0x10f kernel32.dll!ProcessIdToSessionId+0x209 Now, it's not possible to tell for sure from the traces alone whether this part of the trace shows the same instances of the same function calls, or whether there's a loop happening such that (eg) pgwin32_waitforsinglesocket is being called over and over and over. To find that out, you'd need to attach a debugger and set a breakpoint somewhere suitable. Personally, though, as a somewhat informed stab in the dark I suspect that the above part of the call stack is actually entered once and not left. I'd say that when Pg calls my_sock_read(...), resulting in a call to recv(...) and from there a kernel system call, that's as far as it goes. The system call never returns. Why? I suspect you have a buggy network driver or faulty network card. The unexpected interrupt hander being called in one of the stack tracces certainly has to make you wonder. I'd appreciate any help diagnosing this problem - cutting off remote access via SSL isn't the ideal solution. I'd replace the NIC with one from a different manufacturer, at least temporarily. I won't be shocked if the problem goes away. -- Craig Ringer -- 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] Idle processes chewing up CPU?
Hi Craig, thanks for the analysis. If I attach a debugger on the runaway child process, will this halt execution for all the other child processes (ie. freeze the server)? And, can I attach Visual Studio C++ 2008, or is there a recommended debugger for Windows debugging? Given the reliability of the server in the past, I'd probably be expecting an issue with OpenSSL instead, but with debugging attached I should be able to say for sure. Regards, -Brendan -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Wednesday, 19 August 2009 3:12 PM To: Brendan Hill Cc: pgsql-general@postgresql.org; 'Tom Lane' Subject: Re: [GENERAL] Idle processes chewing up CPU? On 19/08/2009 12:31 PM, Brendan Hill wrote: Hi Craig/Tom, I've managed to trap the full stack trace this time The common part of those traces is: ntdll.dll!KiFastSystemCallRet WS2_32.dll!WSARecv+0x65 WSOCK32.dll!recv+0x31 LIBEAY32.dll!BIO_sock_should_retry+0x57 postgres.exe!my_sock_read+0x1b LIBEAY32.dll!BIO_read+0x6f SSLEAY32.dll!SSLv3_client_method+0x1ee1 SSLEAY32.dll!SSLv3_client_method+0x22ea mswsock.dll!StartWsdpService+0x500 SSLEAY32.dll!SSLv3_client_method+0x225a SSLEAY32.dll!SSLv3_client_method+0x2a15 postgres.exe!pgwin32_waitforsinglesocket+0x1ed postgres.exe!secure_read+0x26 postgres.exe!pq_recvbuf+0x71 postgres.exe!pq_getbyte+0x15 postgres.exe!SocketBackend+0x6 postgres.exe!PostgresMain+0xbf8 postgres.exe!BackendRun+0x200 postgres.exe!SubPostmasterMain+0x21d postgres.exe!main+0x177 postgres.exe!__tmainCRTStartup+0x10f kernel32.dll!ProcessIdToSessionId+0x209 Now, it's not possible to tell for sure from the traces alone whether this part of the trace shows the same instances of the same function calls, or whether there's a loop happening such that (eg) pgwin32_waitforsinglesocket is being called over and over and over. To find that out, you'd need to attach a debugger and set a breakpoint somewhere suitable. Personally, though, as a somewhat informed stab in the dark I suspect that the above part of the call stack is actually entered once and not left. I'd say that when Pg calls my_sock_read(...), resulting in a call to recv(...) and from there a kernel system call, that's as far as it goes. The system call never returns. Why? I suspect you have a buggy network driver or faulty network card. The unexpected interrupt hander being called in one of the stack tracces certainly has to make you wonder. I'd appreciate any help diagnosing this problem - cutting off remote access via SSL isn't the ideal solution. I'd replace the NIC with one from a different manufacturer, at least temporarily. I won't be shocked if the problem goes away. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
