Re: [GENERAL] invalid byte sequence for encoding UNICODE
On Jul 24, 8:06 pm, [EMAIL PROTECTED] (AlannY) wrote: Hi there. Many times, I'm confronting with that strange problem: invalid byte sequence for encoding UNICODE. So, I guess, Postgresql can't allow me to use some symbols which is not a part of UNICODE. But what is that symbals? I'm attaching a screenshot with THAT dead-symbol. As you can see - it's an unknown symbol in the end of Cyrillic. First of all, I have checked my data with iconv (iconv -f UTF-8 -t UTF-8 data.txt) and there are no errors, so, I guess, there are no dead-symbols. So the question is: is it possible to find a *table* with forbitten characters for encoding UNICODE? If I can get it - I can kill that dead-characters in my program ;-) Thank you. -- Sent via pgsql-general mailing list ([EMAIL PROTECTED]) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general To say the truth, there are no characters, forbidden in UNICODE as there are no characters, that you can have, that are not in UNICODE. The other thing is UTF8, that encodes real UNICODE into 8bit byte sequence. There errors occur. What does the command: show lc_ctype; show? As Tom has said, more information about your system would be really handy... With best regards, -- Valentine Gogichashvili -- 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] php + postgresql
You need something like this:
$query = select id, name from tablename;
$result = pg_query($query);
while ($row = pg_fetch_array($result)) {
$content = $row[0];
}
That's actually what I was using.
The scoping wasn't the issue either.
Today I switched back to pg_connect() from pg_pconnect(), made some
changes to my overall architecture and re-wrote my database stuff. Then
re-booted.
Not sure what fixed it but all working now. I'm only working on a draft
skeleton right now so am free to fiddle.
Keep finding cool features in PostgreSQL, I think I'm sold!
Thanks
Mick
--
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] High activity short table and locks
Hello, My first impression is that vacuuming the offending table very often helps a lot. I'm doing it by hand for now but I will have a cronjob for this. By the way, it seems I don't need thoses indexes anymore. Thanks a lot for your helpful advices. On Thu, Jul 24, 2008 at 6:19 PM, Richard Huxton [EMAIL PROTECTED] wrote: Guillaume Bog wrote: On Wed, Jul 23, 2008 at 11:17 PM, Richard Huxton [EMAIL PROTECTED] wrote: I tried a vacuum full and had to stop it as it was blocking the server for too long. Below is the partial results I got. It seems you are right: enormous amount of dead space and rows. I did the same vacuum later and it seems to have improved a lot the performance. I need to check again tomorrow. We don't have autovacuum, but as it seems autovacuum cannot target a specific table, I may prefer do it by cron every minute, as you suggest. There's a pg_autovacuum system table that lets you tune things table-by-table. See the manual for details. In your case, a manual vacuum once a minute will be a huge step forward anyway. vf_cn2fr=# VACUUM FULL VERBOSE lockers ; INFO: vacuuming public.lockers INFO: lockers: found 4228421 removable, 107 nonremovable row versions in 64803 pages Well, that table is at least 1000 times larger than it needs to be. If you've run a VACUUM FULL, you'll want to run REINDEX on all the indexes on that table too. 64803 pages containing 512643700 free bytes are potential move destinations. Ouch! that's a 500MB table holding 100 live rows. You could fiddle around setting up ramdisks and pointing tablespaces there, but I'm not sure it's worth it. If it is possible to have no WAL at all on this table, I'd prefer to try it. It seems completely useless and is probably taking a fair amount of i/o. It's a bit early to be sure if the solution is there, but I feel you already did throw some good light on my dark path, I have to thank you for that. Afraid not. The synchronous_commit setting can reduce the disk I/O though. -- Richard Huxton Archonet Ltd
Re: [GENERAL] High activity short table and locks
Guillaume Bog wrote: Hello, My first impression is that vacuuming the offending table very often helps a lot. I'm doing it by hand for now but I will have a cronjob for this. By the way, it seems I don't need thoses indexes anymore. Thanks a lot for your helpful advices. Excellent! Oh, when you set up your cron-job, it's probably better to vacuum this sort of table too often rather than not enough. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I often have to do update if exist, else insert, is my database design wrong?
Hi. This is just some thoughts about database design. I often find my self having to do this update table_XY set x=..., y=... where x=... AND y=; if not found then insert into table_XY (x,y) values (...,...); end if; Is this normal or are there something else I could do so I don't have to check if it exists? Or is there some more general problem with the table design? table_XY is in this case (and most cases) a table like this create table table_XY ( x int references table_X, y int references table_Y ); I could of course add a constraint unique(x,y) to avoid duplicates, but that would not change the the need to check if it exists before inserting. I could also do delete from table_XY where x=... and y=... insert into table_XY (x,y) values (...,...); But that would seem to be very slow. One idea is to put in dummy records for each x,y combination, as a default value. But if table_XY has very few records, it seems like a complete vaste of resources. If I really wanted table_XY to contain a complete set of records of all possible pairs of x,y values, how would I do that? The only way I can think of is that when inserting into table_X, I'd do insert into table_XY VALUES SELECT xvalue,table_Y.id FROM table_Y; where table_Y contains a primary key called id. How would you (you who knows this stuff far better than me) do this? :-) For the developers: a combined insert/update command would be nice :-) -- 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] I often have to do update if exist, else insert, is my database design wrong?
A B wrote: Or is there some more general problem with the table design? table_XY is in this case (and most cases) a table like this create table table_XY ( x int references table_X, y int references table_Y ); Without knowing what XY are and what you are using table_XY for it's going to be difficult for anyone to offer useful advice. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] limit with subselect
Sorry if this is a double posting, I think the previous message was lost. I have two tables T ( id int primary key, a int, b int ) T2 ( id int references T, c int ); and I wish to get 20 lines from T like this select id,a,b from T where id not in (select id from T2 where c=5) limit 20; but that does not seem to work. How can I get what I want? What 20 records are selected is not important. I just need 20. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Subselect with limit
I guess I can't do like this SELECT id,a,b FROM T WHERE id NOT IN (SELECT id FROM T2 WHERE c=5) LIMIT 20; I only want 20 rows. How do I do it? I have table T ( id ,a,b integer, primary key(id) ); T2 (id -- 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] limit with subselect
am Fri, dem 25.07.2008, um 12:02:23 +0200 mailte A B folgendes: Sorry if this is a double posting, I think the previous message was lost. I have two tables T ( id int primary key, a int, b int ) T2 ( id int references T, c int ); and I wish to get 20 lines from T like this select id,a,b from T where id not in (select id from T2 where c=5) limit 20; but that does not seem to work. How can I get what I want? What 20 records are selected is not important. I just need 20. Works for me: test=# create table t (id serial primary key, a int); NOTICE: CREATE TABLE will create implicit sequence t_id_seq for serial column t.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE test=*# create table t2 (id int references t, c int); CREATE TABLE test=*# insert into t (a) select * from generate_Series(10,50) s; INSERT 0 41 test=*# insert into t2 values (1,1); INSERT 0 1 test=*# insert into t2 values (2,2); INSERT 0 1 test=*# insert into t2 values (3,3); INSERT 0 1 test=*# insert into t2 values (4,4); INSERT 0 1 test=*# select * from t where id not in (select id from t2 where c=3) limit 5; id | a + 1 | 10 2 | 11 4 | 13 5 | 14 6 | 15 (5 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.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] limit with subselect
Yes it obviously does! I must admit that (as usual) I found the error shortly after sending the post. The problem was that instead of select id,a,b from T where id not in (select id from T2 where c=5) I wrote select id,a,b from T where id not in (select YYY from T2 where c=5) where YYY was a non existant column in T2. I think that I sometimes got an empty set from the subquery, and sometimes not. 2008/7/25 A. Kretschmer [EMAIL PROTECTED]: am Fri, dem 25.07.2008, um 12:02:23 +0200 mailte A B folgendes: Sorry if this is a double posting, I think the previous message was lost. I have two tables T ( id int primary key, a int, b int ) T2 ( id int references T, c int ); and I wish to get 20 lines from T like this select id,a,b from T where id not in (select id from T2 where c=5) limit 20; but that does not seem to work. How can I get what I want? What 20 records are selected is not important. I just need 20. Works for me: test=# create table t (id serial primary key, a int); NOTICE: CREATE TABLE will create implicit sequence t_id_seq for serial column t.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE test=*# create table t2 (id int references t, c int); CREATE TABLE test=*# insert into t (a) select * from generate_Series(10,50) s; INSERT 0 41 test=*# insert into t2 values (1,1); INSERT 0 1 test=*# insert into t2 values (2,2); INSERT 0 1 test=*# insert into t2 values (3,3); INSERT 0 1 test=*# insert into t2 values (4,4); INSERT 0 1 test=*# select * from t where id not in (select id from t2 where c=3) limit 5; id | a + 1 | 10 2 | 11 4 | 13 5 | 14 6 | 15 (5 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] limit with subselect
On Fri, Jul 25, 2008 at 12:02:23PM +0200, A B wrote: and I wish to get 20 lines from T like this select id,a,b from T where id not in (select id from T2 where c=5) limit 20; but that does not seem to work. How can I get what I want? What 20 records are selected is not important. I just need 20. You've told us what you're expecting, but not what you're actually getting so most responses (including this) are going to be guesses! More information = better responses! My guess is that you're not getting anything back at all because one of the ids in T2 is null. If that is the case, you need to change the query to look like: SELECT id,a,b FROM t WHERE id NOT IN ( SELECT id FROM t2 WHERE c=5 AND id IS NOT NULL) LIMIT 20; Sam -- 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] php + postgresql
In response to Chris [EMAIL PROTECTED]: There does seem to be some evidence of problems historically with PHP and persistent connections in PostgreSQL, on the PHP forums. The advice is typically to avoid them. You'll find the same advice for mysql + persistent connections or any other db + persistent connections. It's not a php+postgres thing. They're manageable if you know all the ins and outs. The big advantage is speed, as they avoid the cost of establishing the initial TCP connection and logging in. In my experiments, this cut the run time for the average script in half. But you have to deal with managing an overwhelming # of perpetually open connections, which takes a lot of resources on both the server and the client side, in addition to problems like connection settings persisting from one script to the next. My opinion is avoid them unless you have a demonstrated need for the speed increase. In that case, make sure you have the time to understand and code for all the potential issues. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. -- 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] php + postgresql
Hi, admin wrote: Thanks again for replies. I know those questions were pretty vague. I need to set up some methodical test scripts that replicate my problems, so that it is clear what is going on. There does seem to be some evidence of problems historically with PHP and persistent connections in PostgreSQL, on the PHP forums. The advice is typically to avoid them. usually it goes so far to avoid PHP alltogether ;) Is there any special reason to use PHP? There are a couple other scripting languages useable for the web which do all have better abstration available. (afaic even PHP does have some more abstration to just using pg* functions) Tino. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
Aarni Ruuhimäki wrote: ... Not sure what causes this with your server but I always use something like this, ie first connect then do your stuff and then close the connection: require(dbconnect.inc); // holds the $conn which is pg_connect(with passes) I would avoid that in favour of using $HOME/.pgpass http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] import from ibm db2
i'm looking for a way to export db2 database schema/data and import it in postgres. don't know if it matters but i'm using postgres on ubuntu (version 8.3.3-0ubuntu0.8.04) for development and deployment is on db2 (obviously not my choice). sometimes i need to import data from production server so i'm looking for some tools to enable me to import data into postgres. Aljosa Mohorovic -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help Me !!!
Hello boys, I have a problem are not practical for sql. I helped to find the 'error of this query? SELECT fresh.articoli.barcode, fresh.articoli.descrizione, fresh.articoli.grammatura, fresh.articoli.id_marchio, fresh.articoli.imballo, fresh.articoli.codicecliente, fresh.articoli.user4, fresh.articoli.codiceean, natura.ass_codici.id_articolo, natura.ass_codici.id_articolo_cosmo FROM fresh.articoli INNER JOIN natura.ass_codici ON (fresh.articoli.id_articolo = natura.ass_codici.id_articolo) AND (natura.ass_codici.id_articolo_cosmo = fresh.articoli.codice) Grazie Marco -- 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] Substitute a variable in PL/PGSQL.
Hi Francisco, Francisco Reyes wrote: On 12:33 am 07/22/08 Steve Martin [EMAIL PROTECTED] wrote: Hi, I am trying to create a PL/PGSQL function to return the values of the fields in a record, e.g. 1 value per row in the output of the function. Are you trying to do a generic function that would work for any table or for just a single table? Is it goint to run against a large data set? What I am trying to do is find the difference between two tables, one that stores the information in a single column, and the other which stores the same data in multiple columns. E.g. CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text, col8 text, col9 text, col10 text); CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL, other_data text, CONSTRAINT test2_index PRIMARY KEY( col_data, some_data )); Trying to find data set in test2.col_data that is not in test.col1 to test.col10. The data sets are very small, e.g. 10 000 rows. Using pl/pgsql. the tried using the pl/pgsql's EXECUTE statement, CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$ DECLARE ted text; bob RECORD; BEGIN FOR bob IN SELECT * FROM test LOOP FOR i IN 1..10 LOOP ted := 'bob.col' || i; EXECUTE 'RETURN NEXT ' || ted; -- RETURN NEXT bob.col1; END LOOP; END LOOP; RETURN; END $$ LANGUAGE plpgsql; test= select * from testfunc() ; ERROR: syntax error at or near RETURN at character 1 QUERY: RETURN NEXT bob.col1 CONTEXT: PL/pgSQL function testfunc line 8 at execute statement LINE 1: RETURN NEXT bob.col1 ^ test= Note Postgres version 8.1.10. Regards Steve Martin -- 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] Substitute a variable in PL/PGSQL.
Merlin Moncure wrote:
On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore [EMAIL PROTECTED] wrote:
here is a way to do it with record variables...no inner loop but
doesn't the column names. with a little work you could add those with
some queries to information_schema (i don't think it's worth it
though).
create or replace function ff(tablename text) returns setof text as
$$
declare
r record;
begin
for r in
execute 'select record_out(' || tablename || ') as f' ||
' from ' || tablename loop
return next r.f;
end loop;
end;
$$ language plpgsql;
merlin
Hi Merlin,
Where can I find out more on the record_out function?
Steve M.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sequence
Hello.
A table has two primary keys: CREATE TABLE example ( pk1 integer, pk2
integer, PRIMARY KEY (pk1, pk2) ). To add a new record I use command:
INSERT INTO example (pk1, pk2) VALUES (0, 0). Before adding the new
record I have to find out the last value of pk2. How can I use something
like this: INSERT INTO example (pk1, pk2) VALUES (0, nextval('pk2'))?
If a table just has one primary key I can use sequence (CREATE
SEQUENCE). What about two primary keys?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sequence
Hello.
A table has two primary keys: CREATE TABLE example ( pk1 integer, pk2
integer, PRIMARY KEY (pk1, pk2) ). To add a new record I use command:
INSERT INTO example (pk1, pk2) VALUES (0, 0). Before adding the new
record I have to find out the last value of pk2. How can I use something
like this: INSERT INTO example (pk1, pk2) VALUES (0, nextval('pk2'))?
If a table just has one primary key I can use sequence (CREATE
SEQUENCE). What about two primary keys?
--
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] Full text index without accents
This would probably help:
CREATE OR REPLACE FUNCTION norm_text_latin(character varying)
RETURNS character varying AS
$BODY$
declare
p_str alias for $1;
v_str varchar;
begin
select translate(p_str, 'ÀÁÂÃÄÅ', 'AA') into v_str;
select translate(v_str, 'ÉÈËÊ', '') into v_str;
select translate(v_str, 'ÌÍÎÏ', '') into v_str;
select translate(v_str, 'ÌÍÎÏ', '') into v_str;
select translate(v_str, 'ÒÓÔÕÖ', 'O') into v_str;
select translate(v_str, 'ÙÚÛÜ', '') into v_str;
select translate(v_str, 'àáâãäå', 'aa') into v_str;
select translate(v_str, 'èéêë', '') into v_str;
select translate(v_str, 'ìíîï', '') into v_str;
select translate(v_str, 'òóôõö', 'o') into v_str;
select translate(v_str, 'ùúûü', '') into v_str;
select translate(v_str, 'Çç', 'Cc') into v_str;
return v_str;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
There's also o useful functions here:
http://www.project-open.org/doc/intranet-search-pg/intranet-search-pg-create.sql
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fco. Mario
Barcala Rodríguez
Sent: July 24, 2008 4:47 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Full text index without accents
Finally I create a function like:
CREATE OR REPLACE FUNCTION nonsensible (text) RETURNS text AS $$
DECLARE
var1 varchar;
BEGIN
var1=replace($1, 'á', 'a');
var1=replace(var1, 'é', 'e');
var1=replace(var1, 'í', 'i');
var1=replace(var1, 'ó', 'o');
var1=replace(var1, 'ú', 'u');
var1=replace(var1, 'Á', 'A');
var1=replace(var1, 'É', 'E');
var1=replace(var1, 'Í', 'I');
var1=replace(var1, 'Ó', 'O');
var1=replace(var1, 'Ú', 'U');
return var1;
END
$$LANGUAGE plpgsql immutable;
Then, create text indexes, one for sensible queries and other for
unsensible ones:
CREATE INDEX textindex ON document USING
gin(to_tsvector('spanish',text));
CREATE INDEX textindexn ON document USING
gin(to_tsvector('spanish',nonsensible(text)));
And then make a query sensible or unsensible to accents doing:
SELECT id FROM document WHERE to_tsvector('spanish',text) @@
to_tsquery('spanish','word_with_accent');
or:
SELECT id FROM document WHERE to_tsvector('spanish',nonsensible(text))
@@ to_tsquery('spanish',nonsensible('word_with_accent'));
respectively.
I think postgreSQL uses both indexes as necessary. I believe to remember
reading something about it in the documentation.
Thank you very much,
Mario Barcala
Here is an example
CREATE FUNCTION dropatsymbol(text) RETURNS text
AS 'select replace($1, ''@'', '' '');'
LANGUAGE SQL;
arxiv=# select to_tsvector('english',dropatsymbol('[EMAIL PROTECTED]'));
to_tsvector
-
'oleg':1 'sai.msu.su':2
--
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] Substitute a variable in PL/PGSQL.
Steve Martin wrote:
Hi,
I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.
How do you substitute a variable?
Test case:
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
text, col6 text, col7 text, col8 text, col9 text, col10 text);
INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i',
'j');
INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
'J');
INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9',
'10');
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLAREted varchar;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
RETURN NEXT ted;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select * from testfunc();
testfunc ---
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
(30 rows)
test=
Or:
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE bob RECORD;
ted TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
FOR bob IN SELECT ted FROM test LOOP
RETURN NEXT bob;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select * from testfunc();
testfunc --
(col1)
(col1)
(col1)
(col2)
(col2)
(col2)
(col3)
(col3)
(col3)
(col4)
(col4)
(col4)
(col5)
(col5)
(col5)
(col6)
(col6)
(col6)
(col7)
(col7)
(col7)
(col8)
(col8)
(col8)
(col9)
(col9)
(col9)
(col10)
(col10)
(col10)
(30 rows)
test=
Or is there another way other than using another procedural language.
Thanks - Steve M.
Found that this function works if I process by column.
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
may TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
may := ' SELECT ' || ted || ' as col FROM test';
FOR bob IN EXECUTE may LOOP
RETURN NEXT bob.col;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select testfunc as data from testfunc() ;
data
--
a
A
1
b
B
2
c
C
3
d
D
4
e
E
5
f
F
6
g
G
7 d
D
4
e
E
5
f
F
6
g
G
7
h
H
8
i
I
9
j
J
10
(30 rows)
test=
Any ideas on how to process by row?
Steve Martin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PANIC: could not write to log file 0
Hi All - I am using PostgreSQL 8.1 which came packaged with WS_FTP Server 6. The PostgreSQL service has died on me a few times over the past week and Ipswitch support has been no help. I found the following in the PostgreSQL log each time this happens. Might anyone have some suggestions on how I can resolve this? Thanks much -Isaiah 2008-07-23 23:06:16 PANIC: could not write to log file 0, segment 29 at offset 4915200, length 16384: Permission denied 2008-07-23 23:06:16 STATEMENT: UPDATE Host_Users SET User_Failed_Login_Count = 0 WHERE (UserID = 4) This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2008-07-23 23:06:16 LOG: server process (PID 2100) was terminated by signal 3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql \dt and identical table names across multiple schemas
This may have been brought up before, but if not, I thought I would bring it to attention, as I think this would be an easy fix. The problem: 2 (or more) schemas that have an identical table name. From within psql, the \dt (and variants of it) will only show a single version of that table. example input: CREATE SCHEMA schema1; CREATE TABLE schema1.tableX (id integer); CREATE TABLE schema1.tableY (id integer); CREATE SCHEMA schema2; CREATE TABLE schema2.tableX (id integer); CREATE TABLE schema2.tableZ (id integer); set search_path to schema1, schema2; \dt example output from \dt command: schema1 | tablex | table | username schema1 | tabley | table | username schema2 | tablez | table | username It seems to me, that if the schemas are even listed in the output of the \dt command, all identically-named tables should be listed. In fact, showing the schema name but not showing all the tables becomes non-intuitive and somewhat misleading. Thoughts?
[GENERAL] Do text columns create pg_toast tables?
I have started testing 8.3.3 and investigating how autovacuum can replace most or all of our cron maintenance tasks. While looking at autovacuum logs I see messages for vacuum come in pairs: Jul 25 02:31:19 iprobe001 postgres[25488]: [4-1] LOG: automatic vacuum of table issp.iprobe001.probe_transfer: index scans: 1 Jul 25 02:31:19 iprobe001 postgres[25488]: [4-2]pages: 0 removed, 1312 remain Jul 25 02:31:19 iprobe001 postgres[25488]: [4-3]tuples: 110456 removed, 35557 remain Jul 25 02:31:19 iprobe001 postgres[25488]: [4-4]system usage: CPU 0.00s/0.16u sec elapsed 1.83 sec Jul 25 02:31:19 iprobe001 postgres[25488]: [5-1] LOG: automatic vacuum of table issp.pg_toast.pg_toast_16516: index scans: 0 Jul 25 02:31:19 iprobe001 postgres[25488]: [5-2]pages: 0 removed, 0 remain Jul 25 02:31:19 iprobe001 postgres[25488]: [5-3]tuples: 0 removed, 0 remain Jul 25 02:31:19 iprobe001 postgres[25488]: [5-4]system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec The first table is what I was expecting, but I was surprised by the pg_toast entry. I know I don't have large columns data wise in the transfer table, but is the threat of having one ( I have one column defined as 'text') enough for it to create the toast infrastructure? Would changing the column to varchar be more efficient? Or is it the unbound nature that creates toast table and we need to be looking at more of a varchar(n) scenario? Thanks for the advice, Woody iGLASS Networks 3300 Green Level Rd. West Cary NC 27519 (919) 387-3550 x813 www.iglass.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] php + postgresql
On Friday 25 July 2008 15:33, you wrote: I would avoid that in favour of using $HOME/.pgpass http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html HTH Tino Hi, Quite right you are. Or something like this? require(/eg/unknown_path/deep_somewhere_else/dbconnect_app_name.php); BR, Aarni -- Burglars usually come in through your windows. -- 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] Substitute a variable in PL/PGSQL.
What I am trying to do is find the difference between two tables, one that stores the information in a single column, and the other which stores the same data in multiple columns. E.g. CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text, col8 text, col9 text, col10 text); CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL, other_data text, CONSTRAINT test2_index PRIMARY KEY( col_data, some_data )); Trying to find data set in test2.col_data that is not in test.col1 to test.col10. FINALLY you get to the requirements. Next time, just ask a question like the above. You were asking how to solve a technical problem that didn't relate to the actual business need. Here are three ways to skin this cat. --version 1 select col_data from test2 except select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') || coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') || coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') || coalesce(col10, '') from test --version 2 select col_data from test2 t2 where not exists (select null from test t where t2.col_data = coalesce(t.col1, '') || coalesce(t.col2, '') || coalesce(t.col3, '') || coalesce(t.col4, '') || coalesce(t.col5, '') || coalesce(t.col6, '') || coalesce(t.col7, '') || coalesce(t.col8, '') || coalesce(t.col9, '') || coalesce(t.col10, '')) --version 3 select t2.col_data from test2 t2 left join (select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') || coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') || coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') || coalesce(col10, '') as col_data from test) t on t2.col_data = t.col_data where t.col_data is null Jon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Data base tables design questions for: user saved forms, user parameters
Hello, I need some comments or advices regarding different tables designs approach, for two part of our needs. _*User saved forms*_ in our project, we want to give to our users ability to save form fields values for later use. It will be named the fast OR saved searches. The user can save more than one fast-search per search-form, for that he can name it accordingly to the search characteristics. We have a relatively huge number of search forms, each one with specific fields. So I don't want, if possible, to use a distinct table for each form. The good and logic way to design the table for this purpose looks like : SAVED_USERS_FORMS ( USER_ID INTEGER,-- corresponding user FORM_ID INTEGER,-- system wide unique form identificator (maybe varchar2 code rather than number?) SAVED_FORM_NAME VARCHAR, -- name of the shorcut/saved form, by user SAVED_FIELDS_VALUES_PAIRS ???, -- saved fields key/values for PK (user, form, name) combination PRIMARY KEY (USER_ID, FORM_ID, SAVED_NAME) ); The hesitation here is : how to store the fields values pairs, in FIELDS_VALUES? - XML field? - our custom text structure and formating representing something key = value - our custom serialized Java object into a field OR I think that the EAV kind of modelling technique can be a flexible way to achieve our goal, but as I read on the net there's an important set of downsides with this approach. But, this way, my table is something like: SAVED_USERS_FORMS ( USER_ID INTEGER, FORM_ID INTEGER, SAVED_FORM_NAME VARCHAR, FIELD_NAME VARCHAR, SAVED_FIELD_VALUE , -- saved field values for PK (user, form, name, field name) combination PRIMARY KEY (USER_ID, FORM_ID, SAVED_NAME, FIELD_NAME) ); So the new problem can be the field SAVED_FIELD_VALUE type enforcement. Do we need to have a field for each data type, so validation complexity can increase dramatically... Do we need or is suggested to have in a way or another meta data tables describing supported form fields, data types, etc? Other things to take into account: * simple and scalable solution, heh, ye, please. * what happens if the form evolve over time? the final solution must not crash or cause some kind of inconsistencies. * . others . ? To enforce SAVED_USERS_FORM to follow defined forms fields, upon adding/delete one or more fields, consistency can be achieved by simple meta data tables describing forms and associated fields. So the design will look: SEARCH_FORMS ( FORM_ID, ... PRIMARY KEY (FORM_ID) ); SEARCH_FORM_FIELDS ( FORM_ID, FIELD_NAME , ... PRIMARY KEY (FORM_ID, FIELD_NAME) ); SAVED_USERS_FORMS ( USER_ID, FORM_ID, SAVED_FORM_NAME, FIELD_NAME, SAVED_FIELD_VALUE, ... PRIMARY KEY (USER_ID, FORM_ID, SAVED_NAME, FIELD_NAME) ); but, this EAV approach isn't perfect because the value field must be a varchar field that we'll possibly store dates, numbers, What is the perfect solution? _*User parameters *_Same kind of problem for storing user system parameters... Is it better to go with one table, 1 row per user with 1 collumn per parameter like: USER_PARAMS ( USER_ID, PARAM1 , PARAM2, PK (USER_ID) ); this way, we can easily enforce the data types per parameters... Or a more flexible approach with a table like: USER_PARAMS ( USER_ID, PARAM_ID, PARAM_VALUE -- FLEXIBLE TYPE? VARCHAR? PK (USER_ID, PARAM_ID) ); USER_PARAMS ( USER_ID, PARAM_ID, PARAM_VALUE_INTEGERINTEGER, PARAM_VALUE_VACHAR VARCHAR(4000), PARAM_VALUE_DATE DATE, ... PK (USER_ID, PARAM_ID) ); like many modellers, it's easy to fall into the generic models easy to maintain but harder to optimize and to ensure consistency? do we need a kind of api for manipulating the parameters, enforcing types, etc? _* *_thanks for any help or comments Bruno
Re: [GENERAL] PANIC: could not write to log file 0
[EMAIL PROTECTED] wrote: 2008-07-23 23:06:16 PANIC: could not write to log file 0, segment 29 at offset 4915200, length 16384: Permission denied 2008-07-23 23:06:16 STATEMENT: UPDATE Host_Users SET User_Failed_Login_Count = 0 WHERE (UserID = 4) This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. I guess you're using Windows, then. I don't suppose there's a virus scanner installed? Reports here suggest that they cause a *lot* of problems, even if you set them to ignore PostgreSQL. Have you checked that the PostgreSQL data directory is in fact writable by the user/role PostgreSQL runs as? Check ACLs and also make sure there aren't any read-only flags set. Information such as which version of Windows you're using, what filesystem type the database is on, details of the ACLs set on the PostgreSQL data directory and pg_xlog subdirectory, etc would also be helpful. 2008-07-23 23:06:16 LOG: server process (PID 2100) was terminated by signal 3 On a UNIX machine that's SIGQUIT. Since PostgreSQL 8.1 used Cygwin for Windows support I guess it's probably SIGQUIT there, too. -- 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] Do text columns create pg_toast tables?
Woody Woodring wrote: The first table is what I was expecting, but I was surprised by the pg_toast entry. I know I don't have large columns data wise in the transfer table, but is the threat of having one ( I have one column defined as 'text') enough for it to create the toast infrastructure? Yep. Would changing the column to varchar be more efficient? Or is it the unbound nature that creates toast table and we need to be looking at more of a varchar(n) scenario? Nope. In any case, since you're not using large values, the vacuum of the toast table is essentially free. Still, I have a patch that will separate the vacuuming of toast tables from main tables by autovacuum (8.4 material only). This will avoid vacuuming the toast table until it is needed on its own right, and vice versa. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] PANIC: could not write to log file 0
[EMAIL PROTECTED] escribió: Hi All - I am using PostgreSQL 8.1 which came packaged with WS_FTP Server 6. The PostgreSQL service has died on me a few times over the past week and Ipswitch support has been no help. I found the following in the PostgreSQL log each time this happens. Might anyone have some suggestions on how I can resolve this? Thanks much -Isaiah 2008-07-23 23:06:16 PANIC: could not write to log file 0, segment 29 at offset 4915200, length 16384: Permission denied Do you (the user running the Postgres service, that is) not have permissions to write on the pg_xlog directory, or some of the files therein? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Full text index without accents
Jonathan Bond-Caron wrote: This would probably help: CREATE OR REPLACE FUNCTION norm_text_latin(character varying) RETURNS character varying AS $BODY$ declare p_str alias for $1; v_str varchar; begin select translate(p_str, 'ÀÁÂÃÄÅ', 'AA') into v_str; Hmm, why not simply use to_ascii() ? -- 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
Re: [GENERAL] Help Me !!!
[EMAIL PROTECTED] wrote: Hello boys, I have a problem are not practical for sql. I helped to find the 'error of this query? SELECT fresh.articoli.barcode, fresh.articoli.descrizione, fresh.articoli.grammatura, fresh.articoli.id_marchio, fresh.articoli.imballo, fresh.articoli.codicecliente, fresh.articoli.user4, fresh.articoli.codiceean, natura.ass_codici.id_articolo, natura.ass_codici.id_articolo_cosmo FROM fresh.articoli INNER JOIN natura.ass_codici ON (fresh.articoli.id_articolo = natura.ass_codici.id_articolo) AND (natura.ass_codici.id_articolo_cosmo = fresh.articoli.codice) Grazie Marco The query appears to be fine as I can see it. Are you getting an error or is it not returning the data you expect? Without an error message or more information on your table structure we can only guess what is wrong. Are you sure the columns used in the join are correct? -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Do text columns create pg_toast tables?
Alvaro Herrera [EMAIL PROTECTED] writes: In any case, since you're not using large values, the vacuum of the toast table is essentially free. Yeah. Note the last line of the quoted log: Jul 25 02:31:19 iprobe001 postgres[25488]: [5-4]system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec An empty toast table really isn't worth worrying about. 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] php + postgresql
Is there any special reason to use PHP? There are a couple other scripting languages useable for the web which do all have better abstration available. (afaic even PHP does have some more abstration to just using pg* functions) Well, yes, there are alternatives of course and I could write this stuff in perl or python but it'd take me 10 times as long because my experience is elsewhere. Learning new stuff is always good, but at the end of the day I get paid for making stuff work on time and in budget ... mostly :-) I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly slow, PHP is supposedly undisciplined and unprofessional. You sure can still write spaghetti with PHP5 if you want to, but you can also write decent code with planning and standards. But good, bad or ugly, it's what I personally am most productive in. I have used PHP's PEAR DB abstraction class many times. It doen't really save much time or effort writing code, and has a performance overhead. I don't need to allow the possibility of switching to another database and stuff like that. Mick -- 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 \dt and identical table names across multiple schemas
Murat Tasan [EMAIL PROTECTED] writes: This may have been brought up before, but if not, I thought I would bring it to attention, as I think this would be an easy fix. The problem: 2 (or more) schemas that have an identical table name. From within psql, the \dt (and variants of it) will only show a single version of that table. That is intentional. Use \dt *.foo if you want to see all the tables named foo. 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] Full text index without accents
Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, why not simply use to_ascii() ? The big problem with to_ascii is its inadequate set of supported encodings. Somebody *really* needs to give it some love on that front. 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] Data base tables design questions for: user saved forms, user parameters
I'm facing a very similar problem where I work (local govt) where we want to put around 100 forms online. The idea of 100 seperate database tables is too painful to contemplate. Depending on the nature of the data, I suppose, both the structured text string and the serialised object options sound viable. I'm very new here, but I think I've seen mention somewhere in the docs about PG being able to store arrays? If this is true, it might be another option. Mick -- 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] Full text index without accents
Ya the function name norm_text_latin() was probably misleading, it takes
latin1-ish characters *encoded in UTF8* and brings them to ascii.
Definitely, the following would be much simpler:
SELECT to_ascii('ÀÁÂÃÄÅÒÓÔÕÖ', 'UTF8')
As of 8.3, you have to do some magic with to_ascii() and utf8 characters
SELECT to_ascii(convert_to_latin('ÀÁÂÃÄÅÒÓÔÕÖ'), 'LATIN1')
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: July 25, 2008 10:42 AM
To: Alvaro Herrera
Cc: Jonathan Bond-Caron; 'Fco. Mario Barcala Rodríguez';
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Full text index without accents
Alvaro Herrera [EMAIL PROTECTED] writes:
Hmm, why not simply use to_ascii() ?
The big problem with to_ascii is its inadequate set of supported
encodings. Somebody *really* needs to give it some love on that
front.
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
--
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] php + postgresql
Hi, admin wrote: Is there any special reason to use PHP? There are a couple other scripting languages useable for the web which do all have better abstration available. (afaic even PHP does have some more abstration to just using pg* functions) Well, yes, there are alternatives of course and I could write this stuff in perl or python but it'd take me 10 times as long because my experience is elsewhere. Learning new stuff is always good, but at the end of the day I get paid for making stuff work on time and in budget ... mostly :-) I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly slow, PHP is supposedly undisciplined and unprofessional. You sure can still Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. write spaghetti with PHP5 if you want to, but you can also write decent code with planning and standards. But good, bad or ugly, it's what I personally am most productive in. I have used PHP's PEAR DB abstraction class many times. It doen't really save much time or effort writing code, and has a performance overhead. I don't need to allow the possibility of switching to another database and stuff like that. Sure, you must consider it yourself but having a little abstraction helps even as kind of inherent documentation when you later need to touch your code again. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] php + postgresql
On Fri, 2008-07-25 at 17:40 +0200, Tino Wildenhain wrote: Hi, I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly slow, PHP is supposedly undisciplined and unprofessional. You sure can still Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. I actually think that the analogy is valid. *Most* PHP users don't know its a hack, those same users are going to be the ones that think PostgreSQL is slow. Joshua D. Drake P.S. To be fair, PHP has gotten much better over the last few releases. -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] php + postgresql
Obviously he is a newbie out of the woods- couldn't make a connection and print results something that the rest of us have been doing for years. It is newbies like him and fan-boys of Ruby/Python/Perl who give PHP a bad name. But I fail to understand the little animosity within some PostgreSQL users to PHP- is it the LAMP stack? Cheers, Bill On Fri, Jul 25, 2008 at 11:53 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Fri, 2008-07-25 at 17:40 +0200, Tino Wildenhain wrote: Hi, I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly slow, PHP is supposedly undisciplined and unprofessional. You sure can still Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. I actually think that the analogy is valid. *Most* PHP users don't know its a hack, those same users are going to be the ones that think PostgreSQL is slow. Joshua D. Drake P.S. To be fair, PHP has gotten much better over the last few releases. -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] Data base tables design questions for: user saved forms, user parameters
Bruno Lavoie wrote: We have a relatively huge number of search forms, each one with specific fields. So I don't want, if possible, to use a distinct table for each form. This is one of the few areas where storing XML in a relational database seems to make sense to me. Forms are well suited to description and validation by XML documents; in fact, with standards like XFORMS there's even a degree of agreement on ways to model and present them. There isn't any particularly strong reason to store the data in a relational DB if you go for XML storage - but if your app already uses PostgreSQL, which it presumably does, then it's much more convenient to be able to use the same access, authentication and storage methods for your form data as everything else. The hesitation here is : how to store the fields values pairs, in FIELDS_VALUES? (1) XML field? I'd certainly be tempted. I'd want to make sure I had good DTDs for my forms, though, and had some sort of form versioning in place to handle detection and conversion or invalidation of old saved form data. Pg's XML support is still pretty rudimentary, but it's handy enough that you can do useful queries on your stored XML data. I'd be highly likely to implement this using (3) if I was tackling the problem. (2) our custom text structure and formating representing something key = value This gets ugly because of the need to store a variety of data types for values. One workaround is to store the values as their textual representations rather than as the real type. That makes any sort of checking and validation even harder than it already is, though, and may also slow things down. (3) our custom serialized Java object into a field I certainly wouldn't want to use any sort of binary object serialization; I'd say that's a recipe for pain and eventual disaster. Serialization to/from XML isn't too bad an idea, though, as Java provides mechanisms for object versioning, etc, and in general makes object-XML (de)serialization surprisingly reasonable. I'd still want a DTD or some other XML schema definition to permit checking of the data in-situ. Personally I think that if you want to store data for may different types of form in one table, and want even rudimentary checking and validation, then XML serialization of Java objects is probably the way to go. * what happens if the form evolve over time? the final solution must not crash or cause some kind of inconsistencies. Proper and careful use of Java's XML serialization should take care of that. It does take extra effort, planning, and thought, but you're signing up for that one way or another by tackling this problem at all. What is the perfect solution? Much like storing generic objects in an RDBMS, the problem isn't a very clean match to the relational model. As such, I doubt there is a perfect solution. I suspect that the most strictly clean approach would be to properly model each form as a table or (more likely) set of related tables. It doesn't sound like that's practical for your particular app, though. XML serialized objects might be the most reasonable compromise. I'll be very interested in the other responses to this, though, and in the resources/articles people reference. like many modellers, it's easy to fall into the generic models easy to maintain but harder to optimize and to ensure consistency? There's a real risk of falling for Enterprise-ey design with system-builder-builders and such. This site: http://thedailywtf.com/ is full of examples of such, including some pretty horrifying database-related ones. For example: http://thedailywtf.com/Articles/The_Enterprise_Rules_Engine.aspx http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx -- 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] php + postgresql
I too don't get the animosity. it's not like you can't write bad code in perl, java, ruby or python. The real issue is the quality of the programmer. On Fri, Jul 25, 2008 at 10:29 AM, Bill Wordsworth [EMAIL PROTECTED] wrote: Obviously he is a newbie out of the woods- couldn't make a connection and print results something that the rest of us have been doing for years. It is newbies like him and fan-boys of Ruby/Python/Perl who give PHP a bad name. But I fail to understand the little animosity within some PostgreSQL users to PHP- is it the LAMP stack? Cheers, Bill On Fri, Jul 25, 2008 at 11:53 AM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Fri, 2008-07-25 at 17:40 +0200, Tino Wildenhain wrote: Hi, I think that PHP (like PostgreSQL, perhaps?) suffers from a reputation hangover from years ago. PostgreSQL was supposedly slow, PHP is supposedly undisciplined and unprofessional. You sure can still Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. I actually think that the analogy is valid. *Most* PHP users don't know its a hack, those same users are going to be the ones that think PostgreSQL is slow. Joshua D. Drake P.S. To be fair, PHP has gotten much better over the last few releases. -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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 -- 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] php + postgresql
On Fri, 2008-07-25 at 12:29 -0400, Bill Wordsworth wrote: Obviously he is a newbie out of the woods- couldn't make a connection and print results something that the rest of us have been doing for years. It is newbies like him and fan-boys of Ruby/Python/Perl who give PHP a bad name. No, it is PHP that gives PHP a bad name. That being said, it depends on your perception. I personally have zero problem with PHP. It has always done what I have asked of it. I do however prefer Python. But I fail to understand the little animosity within some PostgreSQL users to PHP- is it the LAMP stack? No. It because PHP is developed wrong. If you talk to engineers and you say to them, Can you take a look at this code and tell me what you think?. Any engineer worth their salt is going to tell you that the PHP code is scary. Whereas the PostgreSQL code is nicely done. (notice I have not used the word perfect anywhere.) There are also particulars about the language that are just wrong (as I understand it). Specifically in consistency, namespace issues and some others. Coming from my perspective, I could care less that PHPs code is a gnarled mess because I am not a C developer. Nor am I what would be considered a Software Engineer. I am a hack of a developer and a reasonable DBA/Sysadmin. My job is Consultant. If I were an Engineer and I work with several, I wouldn't like PHP either. The majority of known PostgreSQL community people are Engineers. Thus you get the hating. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems with pg_dump and -t wildcards
I'm having some problems with pg_dump and the -t switch. I can't get it to work using wildcards nor with multiple -t switches. I'm using PostgreSQL 8.1.5 and trying to dump just a table and its associated sequence. As shown below, the table il_sections and the sequence il_sections_gid_seq both exist in the database spatial1. But neither pg_dump -Fc -t 'il_sections*' spatial1 il_plss.dump nor pg_dump -Fc -t il_sections -t il_sections_gid_seq spatial1 il_plss.dump will work. The former generates the error listed below while the latter just creates a tiny file with nothing but a couple of comments in it. Anyone know what's wrong? - Bill Thoen Here's what I get: $ psql -dspatial1 Welcome to psql 8.1.5, the PostgreSQL interactive terminal. ... spatial1=# \d List of relations Schema | Name | Type | Owner +---+--+-- public | US_States_ogc_fid_seq | sequence | bthoen public | counties | table| bthoen public | counties_gid_seq | sequence | bthoen public | geometry_columns | table| postgres public | il_sections | table| bthoen public | il_sections_gid_seq | sequence | bthoen public | spatial_ref_sys | table| postgres public | states| table| bthoen (8 rows) spatial1=# \q $ pg_dump -Fc -t 'il_section*' spatial1 il_plss.dump pg_dump: specified table il_section* does not exist -- 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] php + postgresql
On Fri Jul 25 01:03 PM, Joshua D. Drake wrote: On Fri, 2008-07-25 at 12:29 -0400, Bill Wordsworth wrote: Obviously he is a newbie out of the woods- couldn't make a connection and print results something that the rest of us have been doing for years. It is newbies like him and fan-boys of Ruby/Python/Perl who give PHP a bad name. No. It because PHP is developed wrong. If you talk to engineers and you say to them, Can you take a look at this code and tell me what you think?. Any engineer worth their salt is going to tell you that the PHP code is scary. Whereas the PostgreSQL code is nicely done. (notice I have not used the word perfect anywhere.) Uhm, let's not start a PHP debate. Traditionally PHP in terms of design philosophy is more like mySQL: whatever works. PostgreSQL is more an enterprise level database and cares about the enterprise architecture. PHP is just and always was a hack. I'd say the Web is just and always was a hack, so for the Web: PHP is an excellent language and in my opinion the best. I don't like statements like is developed wrong, there's no right or wrong just different approaches to specific problems with advantages and disadvantages. You don't see people making hats out of heavily engineered machines do you? That said PHP is improving and does deserve more enterprise respect mostly thanks to support from Zend (http://www.zend.com/en/) and other companies. I use .NET, java and PHP and with experience you learn to use/speak the right language for the job. hack languages sometimes get the job done faster. -- 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] Problems with pg_dump and -t wildcards
On Fri, 2008-07-25 at 11:36 -0600, Bill Thoen wrote: I'm having some problems with pg_dump and the -t switch. I can't get it to work using wildcards nor with multiple -t switches. I'm using PostgreSQL 8.1.5 and trying to dump just a table and its associated sequence. That is because it doesn't work like that. IIRC you can dump exactly one table with the -t switch. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] php + postgresql
On Fri, 2008-07-25 at 13:41 -0400, Jonathan Bond-Caron wrote: On Fri Jul 25 01:03 PM, Joshua D. Drake wrote: I use .NET, java and PHP and with experience you learn to use/speak the right language for the job. hack languages sometimes get the job done faster. You seemed to have completely missed the point of my post. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] php + postgresql
On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: Uhm, let's not start a PHP debate. The post would have been more effective if you'd stopped there ;-) That said, I'd say the Web is just and always was a hack I have to object to this pretty strongly. What premises do you have for this argument? It seems to me that the http and (at least recently) xhtml specifications have been pretty rigorous. That rigour is actually one of the things many people who want to get 'er done complain about. (Note, however, that I'm firmly in the camp that says you can write lousy code in any language.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] I often have to do update if exist, else insert, is my database design wrong?
Is this normal or are there something else I could do so I don't have to check if it exists? I would say that it is normal. For the developers: a combined insert/update command would be nice :-) Mysql has such a beast along a some other non SQL compliant extensions. One possible approach to what you are trying to do would be: update existing table with new values delete from new table all records that already exist insert remaining new records Which can be done all in pure sql. No need to write a program. -- 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] I often have to do update if exist, else insert, is my database design wrong?
On Jul 25, 2008, at 11:46 AM, Francisco Reyes wrote: Is this normal or are there something else I could do so I don't have to check if it exists? I would say that it is normal. For the developers: a combined insert/update command would be nice :-) Mysql has such a beast along a some other non SQL compliant extensions. MERGE isn't trivial, but the developers are well aware of the desire for it. One possible approach to what you are trying to do would be: update existing table with new values delete from new table all records that already exist insert remaining new records Which can be done all in pure sql. No need to write a program. Well... the race conditions may bite you there, if you have concurrent access. This - http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE - is the usual approach suggested for a concurrent-access safe upsert/merge right now. Cheers, Steve -- 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] php + postgresql
On Jul 25, 2008, at 11:20 AM, Andrew Sullivan wrote: On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: I'd say the Web is just and always was a hack I have to object to this pretty strongly. He has a point, though. If you were starting out to build a user interface framework for building applications to be used by general users, I really doubt you'd end up with the current situation of HTTP, HTML, CSS, Javascript. But that's no matter, really, because here we are. Same for PHP. If you wanted to build a great, elegant, scripting language for writing web front ends, you almost certainly would not end up with PHP. But, here we are. Coming from a C++ and Java background, I find PHP to be just nasty in a lot of ways, but it gets the job done. Most developers don't make deep informed decisions about PHP vs other languages. They use it because everyone else is, there is a huge ecosystem of support around it, it's easy to get something flopping around on the table quickly, and they know *for sure* that they can host it anywhere. Which, really, are not terrible reasons to pick a development environment. Dragging the subject back to PostgreSQL, it's the same thing with MySQL vs PG. Very few people do detailed technical analyses of exactly which DB to use (and, if they do, they use PG :) ). They use MySQL because everyone else does, it gets the job done (or at least appears to), and, most importantly, every $9.95/month hosting plan in the world includes MySQL because Wordpress requires 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] Problems with pg_dump and -t wildcards
Joshua D. Drake [EMAIL PROTECTED] writes: On Fri, 2008-07-25 at 11:36 -0600, Bill Thoen wrote: I'm having some problems with pg_dump and the -t switch. I can't get it to work using wildcards nor with multiple -t switches. I'm using PostgreSQL 8.1.5 and trying to dump just a table and its associated sequence. That is because it doesn't work like that. IIRC you can dump exactly one table with the -t switch. -t does take a wildcard pattern ... in 8.2 and later. I don't believe 8.1 handled more than one -t switch, either. 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] php + postgresql
Andrew Sullivan wrote: On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: Uhm, let's not start a PHP debate. Well it was just a innocent question since the original poster did not seem to know the language of choice good enough to solve this rather basic problem. (Note, however, that I'm firmly in the camp that says you can write lousy code in any language.) Sure, but it seems some languages makes it more easy to write lousy code instead of something elegant. (And be it just because they are so common that you just have a bay of bad examples to choose from, add some cargo cult programming and be ready :-) Ok, back on topic again :-) T. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Problems with pg_dump and -t wildcards
On Fri, Jul 25, 2008 at 11:36:14AM -0600, Bill Thoen wrote: I'm having some problems with pg_dump and the -t switch. I can't get it to work using wildcards nor with multiple -t switches. I'm using PostgreSQL 8.1.5 and trying to dump just a table and its associated sequence. You can't do that. The ability to specify multiple -t switches came in 8.2. You can work around this if you have a custom dump format, by just restoring the tables you want using pg_restore. This is a pretty hideous workaround, though. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] php + postgresql
On Fri Jul 25 02:20 PM, Andrew Sullivan wrote: On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: Uhm, let's not start a PHP debate. The post would have been more effective if you'd stopped there ;-) Agreed :) That said, I'd say the Web is just and always was a hack I have to object to this pretty strongly. I should take that back, there are excellent standards and engineering behind them. No point in starting a web debate, but I'll just say I meant hack in the way it glues together an abundant amount of technology and most impressively, it works! One thing's clear to me, I'll keep on using postgreSQL, it just makes me smile, Sincerely, jon -- 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] php + postgresql
On Friday 25. July 2008, Christophe wrote: Most developers don't make deep informed decisions about PHP vs other languages. They use it because everyone else is, there is a huge ecosystem of support around it, it's easy to get something flopping around on the table quickly, and they know *for sure* that they can host it anywhere. Which, really, are not terrible reasons to pick a development environment. My 2 cents: The prime reason for the popularity of PHP is probably the very gentle learning curve. You can start with a static HTML page, and introduce a few PHP snippets to show dynamic content. For us self-taught people, that means that you get instant results with minimal work. If any language want to compete with PHP in popularity, I believe that it must be just as easy to mingle with HTML. $DEITY, I would love to be able to include Perl code in a HTML page inside a pair of ?pl and ? tags. Now, I don't write PHP scripts like that anymore. I like to have every single character served as HTML to be generated by a function. And I realize that Perl would do that even better than PHP. But as I have become quite proficient with PHP, I tend to keep using that. It surely does the job. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ My Jazz Jukebox: http://www.last.fm/user/leifbk/ -- 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] Problems with pg_dump and -t wildcards
Ah ha! I'm reading the wrong docs. Duh... I guess it's upgrade time! Thank you. Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: On Fri, 2008-07-25 at 11:36 -0600, Bill Thoen wrote: I'm having some problems with pg_dump and the -t switch. I can't get it to work using wildcards nor with multiple -t switches. I'm using PostgreSQL 8.1.5 and trying to dump just a table and its associated sequence. That is because it doesn't work like that. IIRC you can dump exactly one table with the -t switch. -t does take a wildcard pattern ... in 8.2 and later. I don't believe 8.1 handled more than one -t switch, either. 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] Data base tables design questions for: user saved forms, user parameters
On Fri, Jul 25, 2008 at 8:35 AM, Bruno Lavoie [EMAIL PROTECTED] wrote: The hesitation here is : how to store the fields values pairs, in FIELDS_VALUES? Check out contrib/hstore: http://www.postgresql.org/docs/current/static/hstore.html -- 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 vs FreeBSD 7.0 as regular user
Hi, is there anyone using PostgreSQL on FreeBSD 7.0 starting from scratch? I compiled 8.3.3 and wanted to run initdb in my home directory but it fails with the error below. [EMAIL PROTECTED] ~]$ PGDATA=/home/zozo/pgd833 PATH=/home/zozo/pgc833/bin:$PATH initdb The files belonging to this database system will be owned by user zozo. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. creating directory /home/zozo/pgd833 ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in /home/zozo/pgd833/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=16, size=1785856, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1785856 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 13). The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1 initdb: removing data directory /home/zozo/pgd833 I configured the shared memory settings in advance according to the PostgreSQL 8.3 online docs: $ cat /etc/sysctl.conf ... kern.ipc.shmall=32768 kern.ipc.shmmax=134217728 kern.ipc.semmap=256 These three settings were also set with sysctl -w ... to take effect immediately. So, there is enough shared memory, especially for the downsized configuration determined at initdb time. Why can't I get some shared memory as a regular user? It's a fresh install of FreeBSD 7.0, only bison and gmake were added from the ports repository and the above sysctls were set in the system. No matter if I log in from the console or via ssh, I get the same error above. Is there a magic to enable a user to allocate shared memory? Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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 vs FreeBSD 7.0 as regular user
On Fri, 2008-07-25 at 22:39 +0200, Zoltan Boszormenyi wrote: Hi, is there anyone using PostgreSQL on FreeBSD 7.0 starting from scratch? I compiled 8.3.3 and wanted to run initdb in my home directory but it fails with the error below. I configured the shared memory settings in advance according to the PostgreSQL 8.3 online docs: $ cat /etc/sysctl.conf ... kern.ipc.shmall=32768 kern.ipc.shmmax=134217728 kern.ipc.semmap=256 These three settings were also set with sysctl -w ... to take effect immediately. Are you buy chance in a jail? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] php + postgresql
On Fri, Jul 25, 2008 at 1:47 PM, Leif B. Kristensen [EMAIL PROTECTED] wrote: On Friday 25. July 2008, Christophe wrote: Most developers don't make deep informed decisions about PHP vs other languages. They use it because everyone else is, there is a huge ecosystem of support around it, it's easy to get something flopping around on the table quickly, and they know *for sure* that they can host it anywhere. Which, really, are not terrible reasons to pick a development environment. My 2 cents: The prime reason for the popularity of PHP is probably the very gentle learning curve. You can start with a static HTML page, and introduce a few PHP snippets to show dynamic content. For us self-taught people, that means that you get instant results with minimal work. For me I came from a C background, with bits of Pascal, and old Line numbered BASIC (Hey, it's all we had on our govt spec Burroughs systems in 1985). the reason I picked php back in the day was that it was a lot like C, a little like perl (the parts I like) and it had a small enough memory footprint I could run a decent server with pgsql 6.5.3, apache 1.3.4 and php 3.0.5 on a 64 Meg RAM P-100 when everything else I'd tried just crashed and burned or ground to a halt on that poor little machine. Years later and we build php servers with 8 Gigs ram, use memcached, and other cool tricks to make them even faster. But for all the bad engineering in php's code base, I've never had a problem building a stable server with it. As long as I left out any mysql libs. :) -- 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 vs FreeBSD 7.0 as regular user
Joshua D. Drake írta: On Fri, 2008-07-25 at 22:39 +0200, Zoltan Boszormenyi wrote: Hi, is there anyone using PostgreSQL on FreeBSD 7.0 starting from scratch? I compiled 8.3.3 and wanted to run initdb in my home directory but it fails with the error below. I configured the shared memory settings in advance according to the PostgreSQL 8.3 online docs: $ cat /etc/sysctl.conf ... kern.ipc.shmall=32768 kern.ipc.shmmax=134217728 kern.ipc.semmap=256 These three settings were also set with sysctl -w ... to take effect immediately. Are you buy chance in a jail? Joshua D. Drake I don't know. How to determine? Running this as my own user: $ sysctl -a | grep ^kern.ipc shows the same settings as above. Thanks. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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 vs FreeBSD 7.0 as regular user
On 4:53 pm 07/25/08 Zoltan Boszormenyi [EMAIL PROTECTED] wrote: I don't know. How to determine? Running this as my own user: Is this your own machine or at an ISP? If it is your own machine, then most likely you are not in a jail. You would know if you were since you would have had to do it. If at an ISP once way to know if you are in a jail I think is to try to ping and traceroute. I think by default you can't do one of those within a jail. Also try ifconfig. A jail will show you a single IP. A real machine will show you usually at least two. 127.0.0.1 and some other address. -- 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 vs FreeBSD 7.0 as regular user
Francisco Reyes írta: On 4:53 pm 07/25/08 Zoltan Boszormenyi [EMAIL PROTECTED] wrote: I don't know. How to determine? Running this as my own user: Is this your own machine or at an ISP? It's my own machine, FreeBSD is installed as a VMWare guest. If it is your own machine, then most likely you are not in a jail. You would know if you were since you would have had to do it. If at an ISP once way to know if you are in a jail I think is to try to ping and traceroute. I think by default you can't do one of those within a jail. Also try ifconfig. A jail will show you a single IP. A real machine will show you usually at least two. 127.0.0.1 and some other address. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] Help Me !!!
2008/7/24 [EMAIL PROTECTED]: Hello boys, I have a problem are not practical for sql. I helped to find the 'error of this query? SELECT fresh.articoli.barcode, fresh.articoli.descrizione, fresh.articoli.grammatura, fresh.articoli.id_marchio, fresh.articoli.imballo, fresh.articoli.codicecliente, fresh.articoli.user4, fresh.articoli.codiceean, natura.ass_codici.id_articolo, natura.ass_codici.id_articolo_cosmo FROM fresh.articoli INNER JOIN natura.ass_codici ON (fresh.articoli.id_articolo = natura.ass_codici.id_articolo) AND (natura.ass_codici.id_articolo_cosmo = fresh.articoli.codice) Grazie Marco Looks fine to me too.. but you were only talking to the boys.. not the girls ;) LadyNikon
[GENERAL] Sequence
Hello.
Each user has a lot of guests. Each guest only has one user.
1. I create a table users:
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
name varchar(256)
);
2. I create a table guests:
CREATE TABLE guests (
user_id integer,
guest_id SERIAL,
PRIMARY KEY (user_id, guest_id),
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)
3. I add two new users:
insert into users (name) values ('alex2008');
insert into users (name) values ('jack2008');
select * from users;
user_id | name
-+--
1 | alex2008
2 | jack2008
(2 rows)
4. I add two new guests to the user alex2008 and one new guest to the
user jack2008:
insert into guests (user_id, name) values (1, 'Mark Twain');
insert into guests (user_id, name) values (1, 'Anna Black');
insert into guests (user_id, name) values (2, 'John Black');
select * from guests;
user_id | guest_id |name
-+--+
1 |1 | Mark Twain
1 |2 | Anna Black
2 |3 | John Black
(3 rows)
I want to have a different result of insert command:
user_id | guest_id |name
-+--+
1 |1 | Mark Twain
1 |2 | Anna Black
2 |1 | John Black
Sequence guests_guest_id_seq is not connected with the field user_id. It
increases in spite of values of user_id. How can I solve this problem?
--
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] A couple of newbie questions ...
On Jul 23, 2008, at 12:00 PM, Shane Ambler wrote: To be honest I hadn't seen the use of INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone with INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z') is DEFAULT a better option than using NULL? or is it just a preference to spell out the implied default entry? I've only used DEFAULT in CREATE TABLE(...) The semantics of using DEFAULT or NULL is completely different. If the column has a default value (for example, 0), then including DEFAULT in your insert list will give you the default value of zero. But if you include NULL in your insert list -- you'll get NULL, not zero. If nothing is included for the column in your insert list, you'll get the column default if it has one, otherwise NULL. John DeSoi, Ph.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] php + postgresql
Well no PHP is conceptual undisciplined and confusing. I would not compare this with Postgresql itself which is very professional developed with a great vision. PHP is just and always was a hack. I didn't mean to compare PG and PHP at the level of engineering quality, but to suggest that perhaps both suffer from people continuing to hold rigid preconceptions about them based on how things were 5 or 10 years ago. Anyway, while I'm quite happy to continue banging out things that just work in PHP for the time being, you suggest (in a subsequent post) that there is one scripting language in particular that you'd use ... might I enquire which language that is, and why? Just curious, I'm definitely not looking for an ideological debate. Re the possible heightened level of animosity to PHP in PG circles, if it exists, could it have anything to do with PHP's close association with MySql? The animosity, by the way, seems to go both ways, I think I saw something about Rasmus Lerdorf bagging PostgreSQL on Slashdot(?) recently. Personally, I'm not overly concerned either way. I'm happy to leave the academic debates to those with the time to pursue them. I'm the first to admit I know little about the art and science of relational database design and admin. But up to this point, I haven't needed to. It doesn't take rocket science to store and retrieve some text for a few web pages in a database. Anyway, this is proving an interesting, lively and helpful community, hope to learn lots more about doing things the PostgreSQL way ... with PHP :-). Mick -- 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] php + postgresql
On Sat, 2008-07-26 at 11:13 +0930, admin wrote: Anyway, while I'm quite happy to continue banging out things that just work in PHP for the time being, you suggest (in a subsequent post) that there is one scripting language in particular that you'd use ... might I enquire which language that is, and why? Just curious, I'm definitely not looking for an ideological debate. You do realize that you just opened one of the longest, loudest and most inherently beer inducing arguments known to man since Emacs vs Vi? (answer: Joe) So why not! I use Python. I love Python. Although I guarantee you that others will say ruby, perl, java (well maybe not java). The answer to your question is: Use what works for you. I used PHP for years, I actually used Perl before PHP but got tired of the Perl oddness. I moved on to Python and love it. There are things in it I don't like (just see subprocess) but for the most part, its gorgeous. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] Sequence
select * from guests; user_id | guest_id |name -+--+ 1 |1 | Mark Twain 1 |2 | Anna Black 2 |3 | John Black (3 rows) I want to have a different result of insert command: user_id | guest_id |name -+--+ 1 |1 | Mark Twain 1 |2 | Anna Black 2 |1 | John Black Sequence guests_guest_id_seq is not connected with the field user_id. It increases in spite of values of user_id. How can I solve this problem? If possible, design your application to be happy with the way it is already. Those keys shouldn't really be user visible anyway. If you really have to have per-user guest IDs (and, I'm guessing, contiguous sequences of guest IDs) you'll have to do a fair bit of work. The usual approach seems to be using a trigger function to trap inserts and deletes and rewrite the guest_id field appropriately. If you don't need contiguous guest IDs - ie you're happy with a sequence like 1 3 4 5 8 after IDs 2, 6 and 7 have been DELETEd - then you can emulate a sequence with a per-user counter. Eg: CREATE TABLE user ( user_id SERIAL PRIMARY KEY, guest_id_ctr INTEGER ); CREATE TABLE guest ( user_id INTEGER, guest_id INTEGER, PRIMARY KEY(user_id, guest_id) ); then do inserts into guest with a sequence of operations like this (assuming the user_id of interest is 111): UPDATE user SET guest_id_ctr = guest_id_ctr + 1 WHERE user_id = 111 RETURNING guest_id_ctr; -- Now, using the value obtained with the previous statement, say 4: INSERT INTO guest (user_id, guest_id) VALUES (111, 4); That assumes you're using a version of PostgreSQL new enough to support UPDATE ... RETURNING. If not, you need to use SELECT FOR UPDATE to obtain the value, followed by a separate UPDATE statement to actually increment it. If you do need contiguous values of guest_ids within a given user_id then you'll need to use a different approach, most likely PL/PgSQL triggers or the use of function wrappers for DML operations on the table. However, in most cases an application requirement of contiguous IDs is a design fault that should be fixed, rather than hacked around in the database. -- 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
[GENERAL] Strange Postgresql behavior solved
I spent a day on this, and it's really not a PostgreSQL issue, but I thought I'd post it in case someone else comes down with it. Scenario: I moved the physical location and networking environment of the server. It's on Mac OS X - XServe, but that isn't germaine to the story. Originally, the server was the DHCP router for the network, now it sits in a demilitarized zone off a DLink router that's providing DHCP and NAT. Symptoms: Postgres was unable to resolve *some* simple queries, like Select * from salestable where thekey = 118, although it would work for thekey values of 1 all the way to 117. The connection would just freeze, and timeout after a couple of minutes. My application worked this way, and so did pgAdmin, but Navicat LE didn't! Solution: I finally realized that my application and pgAdmin were both accessing the server using the domain name, and Navicat was using the IP number. Indeed, replacing the connection data with the IP number on the app and pgAdmin made the world safe again. Probably some funky stuff with the router (not one of their expensive ones) that caused all the consternation, but I originally thought corrupt database (because I could get 117 records to come out fine, but not the 118th). Also, I had narrowed it down to failing only when accessing the last three fields of that 118th record, the first 40 fields were fine. -Owen -- 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] Strange Postgresql behavior solved
Owen Hartnett [EMAIL PROTECTED] writes: I spent a day on this, and it's really not a PostgreSQL issue, but I thought I'd post it in case someone else comes down with it. Scenario: I moved the physical location and networking environment of the server. It's on Mac OS X - XServe, but that isn't germaine to the story. Originally, the server was the DHCP router for the network, now it sits in a demilitarized zone off a DLink router that's providing DHCP and NAT. Symptoms: Postgres was unable to resolve *some* simple queries, like Select * from salestable where thekey = 118, although it would work for thekey values of 1 all the way to 117. The connection would just freeze, and timeout after a couple of minutes. My application worked this way, and so did pgAdmin, but Navicat LE didn't! Solution: I finally realized that my application and pgAdmin were both accessing the server using the domain name, and Navicat was using the IP number. Indeed, replacing the connection data with the IP number on the app and pgAdmin made the world safe again. What this sounds like to me is that you've got two postmasters running on different ports, or something close to that. The specific behavior you describe is absolutely not sensible. 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
