[GENERAL] Numeric to integer Type conversion
We have database table with the following columns, foo -- name VARCHAR(20) bar NUMERIC(20,0) We were running version 7.2.1 until now. The following SQL used to work fine in 7.2.1, SELECT name FROM foo WHERE (bar 64) 0; Now we upgraded to version 8.1.5 and getting the error, ERROR: operator does not exist: numeric integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. NUMERIC(20,0) is probably not the best way to define a column to be used for bit arithmetic, but we cant change the column type because of legacy. Is there anyway I can get the existing SQL to work without any changes on the application side. Thanks, ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Numeric to integer Type conversion
am Tue, dem 23.01.2007, um 23:47:08 -0800 mailte Saravanan Bellan folgendes: We have database table with the following columns, foo -- name VARCHAR(20) bar NUMERIC(20,0) We were running version 7.2.1 until now. The following SQL used to work fine in 7.2.1, SELECT name FROM foo WHERE (bar 64) 0; Now we upgraded to version 8.1.5 and getting the error, ERROR: operator does not exist: numeric integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. NUMERIC(20,0) is probably not the best way to define a column to be used for bit arithmetic, but we cant change the column type because of legacy. Is there anyway I can get the existing SQL to work without any changes on the application side. Try to create a now operator like this: test=# create table foo (name varchar(20), bar NUMERIC(20,0)); CREATE TABLE test=*# commit; COMMIT test=# create function f_foo(numeric, int) returns int as $$ begin return ($1::int) $2; end; $$ language 'plpgsql'; CREATE FUNCTION test=*# create operator (leftarg = numeric, rightarg = int, procedure = f_foo, commutator = ); CREATE OPERATOR test=*# SELECT name FROM foo WHERE (bar 64) 0; name -- (0 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [1/2 OFF] Varlena.com inaccessible from .br (Blocked?)
2007/1/23, Alvaro Herrera [EMAIL PROTECTED]: Jorge Godoy wrote: elein [EMAIL PROTECTED] writes: Is there still a problem here? Does anyone know what the problem was? I have no trouble accessing varlena.com, but I am not a good tester for that. I was out of town during this thread but my machine did not go down, but my isp could have. You could have emailed me [EMAIL PROTECTED] I hope this message gets to you. The website is still unreachable from Brazil. It looks like there's something blocking South America somewhere on your configurations. When I tried accessing using an UNC account it worked perfectly, so it wasn't a problem with the machine being down. Could you check with your ISP if they're blocking us from down here? ;-) They are certainly blocking some networks. I have a report from a guy in Venezuela which cannot access the site when connected via CANTV, but can access it readily when using a different provider whose name escapes me. Right now I can access varlena.com from Brasília, Brazil, IP 200-140-160-96.bsace705.dsl.brasiltelecom.net.br Regards, -- Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] how to read bytea field
This might help you: select encode(col1,'escape') from tblBytea; where col1 is of type bytea... - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/24/07, marcelo Cortez [EMAIL PROTECTED] wrote: folks help me ,i cant read bytea type field's. how to convert bytea to text or varchar ? when using bytea types? any clue be appreciated best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] copy schema X to schema Y in the same DB
I think the way of doing that will be taking a schema backup using pg_dump -n option and then restoring that again in the same database using a different schema name with psql. Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/24/07, johnf [EMAIL PROTECTED] wrote: Hi, I would like to copy a schema X to a new schema Y within the same database. Is this possible? -- John Fabiani ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Who is Slony Master/Slave + general questions.
On Sat, 20 Jan 2007 11:07:57 +0500, Shoaib Mir [EMAIL PROTECTED] wrote: I dont have the replication setup on my machine right now but I guess as far as I remember you can surely check for the master and slave nodes from a Slony schema table. I think the notion of master and slave server is a little bit misleading here: We have sets and a node could be a origin or subscriber of them. Thinking that way, one idea to get that information is to issue SELECT a.set_id, a.set_comment, (SELECT last_value FROM _replication.sl_local_node_id) AS local_id, CASE WHEN a.set_origin = (SELECT last_value FROM _replication.sl_local_node_id) THEN TRUE ELSE FALSE END AS master_node FROM _replication.sl_set a; This gives you a result set which holds TRUE for every set the current node is an origin node for. Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 19 Jan 2007 08:25:23 -0800, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I am using LinuxHA to manage the failover and Slony as part of to failover to move to the healthy node. But my question was more along the lines, if a user has access to both databases (master and slave) but does not know which one is which, how can you tell? Take a scenario: you configure 2 servers as master and slave. You walk for a period of time during which a number failovers occur. You come back. Can I query a sl_ table to determine which server is the current master and which one is the current slave? If you are using LinuxHA you have a virtual IP adress for your Cluster which points to the current active master on your cluster. Connecting to the master node should always happen through this IP adress, so you always know you are on the master when using this IP. You could then spread read operations along the IPs assigned directly to each node, declaring these connections read only. Bernd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
Shoaib ,folks Yes i know, but if your define bytea field and store bytea in this field , decode don't work, because decode function has text parameter not bytea ,so how do that to read bytea field to text again? what function convert bytea to text? best regards mdc --- Shoaib Mir [EMAIL PROTECTED] escribió: This might help you: select encode(col1,'escape') from tblBytea; where col1 is of type bytea... - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/24/07, marcelo Cortez [EMAIL PROTECTED] wrote: folks help me ,i cant read bytea type field's. how to convert bytea to text or varchar ? when using bytea types? any clue be appreciated best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
Hi,
marcelo Cortez wrote:
Yes i know, but if your define bytea field and store
bytea in this field , decode don't work,
Sure it does:
test=# select encode(E'\\000\\001', 'escape')::text;
encode
--
\000\x01
(1 row)
If you inspect the function, you'll find that encode can *only* handle
bytea, not text (as the first parameter):
test=# \df encode;
List of functions
Schema | Name | Result data type | Argument data types
++--+-
pg_catalog | encode | text | bytea, text
(1 row)
And trying to feed it text gives:
test=# select encode('some text'::text, 'escape')::text;
ERROR: function encode(text, unknown) does not exist
Are you sure you tested with a real bytea field?
Regards
Markus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
hi Markus ,folks Are you sure you tested with a real bytea field? Yeah , i store bytea using encode function , how you say . The field of my table is bytea type , and store real bytea data in this field. My problem is , i can't convert this field to text anymore, not function receiving bytea and return text exist's. I think my mistake was use bytea field. I thinking in turn this field to text and use decode/encode for storage binary data. please correct me if i'm wrong. best regards mdc Regards Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to read bytea field
marcelo Cortez wrote: Are you sure you tested with a real bytea field? Yeah , i store bytea using encode function , how you say . No, you store using the decode function. = \df decode List of functions Schema | Name | Result data type | Argument data types ++--+- pg_catalog | decode | bytea| text, text The field of my table is bytea type , and store real bytea data in this field. My problem is , i can't convert this field to text anymore, not function receiving bytea and return text exist's. That's what your encode function should do. = \df encode List of functions Schema | Name | Result data type | Argument data types ++--+- pg_catalog | encode | text | bytea, text (1 row) What does \df encode show for you? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] how to read bytea field
Decode works as expected for me Try the following: select decode((encode(E'\\000\\001', 'escape')::text), 'escape'); --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/24/07, marcelo Cortez [EMAIL PROTECTED] wrote: hi Markus ,folks Are you sure you tested with a real bytea field? Yeah , i store bytea using encode function , how you say . The field of my table is bytea type , and store real bytea data in this field. My problem is , i can't convert this field to text anymore, not function receiving bytea and return text exist's. I think my mistake was use bytea field. I thinking in turn this field to text and use decode/encode for storage binary data. please correct me if i'm wrong. best regards mdc Regards Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to read bytea field
Hi, marcelo Cortez wrote: Are you sure you tested with a real bytea field? Yeah , i store bytea using encode function , how you say . I never said 'use encode function to store bytea'. I tried to explain that encode returns TEXT. The field of my table is bytea type , and store real bytea data in this field. That's contradictory to the above. Please show me exactly what you do, please (the INSERT as well as the SELECT you want to use). I think my mistake was use bytea field. I thinking in turn this field to text and use decode/encode for storage binary data. That sounds like wasting diskspace and I/O bandwith. Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to read bytea field
folks
my table
CREATE TABLE pblfield
(
id_ integer NOT NULL,
value_field bytea,
name character varying(128),
osset integer,
length integer,
version_ integer,
inst_class_ character varying(128),
CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
)
insert into pblfield( id_ , value_field ) values( 1 ,
encode(E'\\000\\001', 'escape') ) ;
insert into pblfield( id_ , value_field ) values( 2 ,
encode(E'\\000\\002', 'escape') ) ;
etc...
now, i want to recover value_field in text form
some thing like..
select id_ , decode(value_field) from pblfield ;
WRONG WRONG ... decode has text parameter ...!
Ok ,next try .
select id_ , decode(value_field ::text ) from
pblfield.
WRONG WRONG ... bytea not cast to string ...!
so, how do that ???
I think solution is:
CREATE TABLE pblfield
(
id_ integer NOT NULL,
value_field text , /* here text field */
name character varying(128),
osset integer,
length integer,
version_ integer,
inst_class_ character varying(128),
CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
)
insert into pblfield( id_ , value_field ) values( 1 ,
encode('\\000\\001', 'escape')::text ) ;
insert into pblfield( id_ , value_field ) values( 2 ,
encode(E'\\000\\002', 'escape') ::text ) ;
select id_ , value_field from pblfield ;
works and
select id_ , decode(value_field ,'escaped' ) from
pblfield ;
works too!!!
folks thanks for your time and responses.
best regards
Last cuestion , when bytea field ( type) is usable?
for storage to external files???
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
marcelo Cortez wrote: folks my table CREATE TABLE pblfield ( id_ integer NOT NULL, value_field bytea, name character varying(128), osset integer, length integer, version_ integer, inst_class_ character varying(128), CONSTRAINT pblfield_pkey PRIMARY KEY (id_) ) insert into pblfield( id_ , value_field ) values( 1 , encode(E'\\000\\001', 'escape') ) ; NO! Go back and read what everyone is saying about the direction decode/encode work in. You're using them the wrong way around. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to read bytea field
ok my mistake , insert into pblfield( id_ , value_field ) values(1 ,encode(E'\\000\\001', 'escape') ::bytea ) ; best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] how to read bytea field
marcelo Cortez wrote: ok my mistake , insert into pblfield( id_ , value_field ) values(1 ,encode(E'\\000\\001', 'escape') ::bytea ) ; No. Use decode to convert text to bytea, and encode to convert bytea to text. = CREATE TABLE tb (b bytea); CREATE TABLE richardh= INSERT INTO tb VALUES ( decode(E'\\000\\001\\002','escape') ); INSERT 0 1 richardh= SELECT encode(b,'escape') FROM tb; encode -- \000\x01\x02 (1 row) richardh= SELECT encode(b,'hex') FROM tb; encode 000102 (1 row) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to read bytea field
Richard H i'm understood now!, sorry for the noise. thanks very, very much best regards mdc --- Richard Huxton dev@archonet.com escribió: marcelo Cortez wrote: ok my mistake , insert into pblfield( id_ , value_field ) values(1 ,encode(E'\\000\\001', 'escape') ::bytea ) ; No. Use decode to convert text to bytea, and encode to convert bytea to text. = CREATE TABLE tb (b bytea); CREATE TABLE richardh= INSERT INTO tb VALUES ( decode(E'\\000\\001\\002','escape') ); INSERT 0 1 richardh= SELECT encode(b,'escape') FROM tb; encode -- \000\x01\x02 (1 row) richardh= SELECT encode(b,'hex') FROM tb; encode 000102 (1 row) -- Richard Huxton Archonet Ltd __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
marcelo Cortez wrote: Richard H i'm understood now!, sorry for the noise. The thing to remember is it's encode/decode from the point of view of the bytea type. So hex/escape are encodings of the real value. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] missing cache data for cache id 27
I'm getting the above error when i try to replace a function of mine. It seems i have two problems: the latest dump (through phpPGAdmin) works fine, except that a function that should return a record was replaced without the column definition list, so calls on it are failing. from pg_dump: CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS SETOF record AS $$ should be: CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text, OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$ So, i tried re-defining the function with the OUT params, and was hit with the error in the subject line. I was able to DROP it first, then re-create it. Now everything seems fine. But does anyone know what the error means? And why does the function definition in the db dump not reflect that OUT params are called for? Here's the entire function, fwiw: -- snip -- CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text, OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$ DECLARE rec record; BEGIN FOR rec IN EXECUTE 'SELECT id, name, 1 AS total FROM service_type ORDER BY NAME ASC' LOOP name := rec.name; id := rec.id; SELECT INTO rec.total SUM(CASE sp.accepted WHEN TRUE THEN 1 ELSE 0 END) FROM service_provider AS sp WHERE sp.id IN ( SELECT spst.service_provider_id FROM service_provider_service_type AS spst WHERE spst.service_type_id = rec.id ); -- If none for this service type, give it a total of zero IF rec.total IS NULL THEN SELECT INTO total 0; ELSE total := rec.total; END IF; RETURN NEXT; END LOOP; RETURN; END; $$ LANGUAGE plpgsql IMMUTABLE; -- snip -- brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] missing cache data for cache id 27
brian wrote: I'm getting the above error when i try to replace a function of mine. It seems i have two problems: the latest dump (through phpPGAdmin) works fine, except that a function that should return a record was replaced without the column definition list, so calls on it are failing. from pg_dump: CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS SETOF record AS $$ should be: CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text, OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$ What version of PostgreSQL and what version of pg_dump are we talking about here? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] missing cache data for cache id 27
Richard Huxton wrote: brian wrote: I'm getting the above error when i try to replace a function of mine. It seems i have two problems: the latest dump (through phpPGAdmin) works fine, except that a function that should return a record was replaced without the column definition list, so calls on it are failing. from pg_dump: CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS SETOF record AS $$ should be: CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text, OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$ What version of PostgreSQL and what version of pg_dump are we talking about here? Ach! 8.1.4, sorry. b ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] missing cache data for cache id 27
brian [EMAIL PROTECTED] writes: I'm getting the above error when i try to replace a function of mine. In what PG version? http://archives.postgresql.org/pgsql-bugs/2006-10/msg00044.php http://archives.postgresql.org/pgsql-committers/2006-10/msg00084.php And why does the function definition in the db dump not reflect that OUT params are called for? Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code to fail to notice the OUT parameters, since it'd not know about the new columns in pg_proc ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] missing cache data for cache id 27
Tom Lane wrote: brian [EMAIL PROTECTED] writes: I'm getting the above error when i try to replace a function of mine. In what PG version? http://archives.postgresql.org/pgsql-bugs/2006-10/msg00044.php http://archives.postgresql.org/pgsql-committers/2006-10/msg00084.php And why does the function definition in the db dump not reflect that OUT params are called for? Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code to fail to notice the OUT parameters, since it'd not know about the new columns in pg_proc ... phpPgAdmin 4.0.1 (4.1 is latest). But i was under the impression that it simply makes a call to pg_dump, so wasn't expecting the problem lay in the front end. b ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] missing cache data for cache id 27
brian [EMAIL PROTECTED] writes: Tom Lane wrote: Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code to fail to notice the OUT parameters, since it'd not know about the new columns in pg_proc ... phpPgAdmin 4.0.1 (4.1 is latest). But i was under the impression that it simply makes a call to pg_dump, so wasn't expecting the problem lay in the front end. Um, but what pg_dump is it invoking? ISTR that phpPgAdmin uses the -i option to pg_dump, so that you wouldn't find out if the pg_dump was too old. In my book using that option by default verges on being a war crime, but I'm sure they think it's a good idea. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] missing cache data for cache id 27
Tom Lane wrote: brian [EMAIL PROTECTED] writes: Tom Lane wrote: Need a newer phpPGAdmin, perhaps? It'd be unsurprising for pre-8.1 code to fail to notice the OUT parameters, since it'd not know about the new columns in pg_proc ... phpPgAdmin 4.0.1 (4.1 is latest). But i was under the impression that it simply makes a call to pg_dump, so wasn't expecting the problem lay in the front end. Um, but what pg_dump is it invoking? ISTR that phpPgAdmin uses the -i option to pg_dump, so that you wouldn't find out if the pg_dump was too old. In my book using that option by default verges on being a war crime, but I'm sure they think it's a good idea. Right. I'd done: $ /usr/bin/pg_dump --version pg_dump (PostgreSQL) 8.1.4 but a quick glance at phpPGAdmin's config reminds me that it has its own version: $ /usr/bin/phpPgAdmin/pg_dump --version pg_dump (PostgreSQL) 8.0.4 I'll upgrade to 4.1, dump the db, and see how it recreates the function (whether it includes the OUT params). Thanks for the heads-up, Tom! b ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Stats collector frozen?
I've noticed that my tables are not being auto vacuumed or analyzed regularly, even though I have very aggressive autovacuum settings. The stats collector appears to still be running, since I can see a postgres.exe process with -forkcol. However, I never notice it using I/O or CPU usage. Also, querying the pg_stat_user_tables view shows no change in table stats even for tables that change very frequently. I see error such as these in the log every now and then - not sure if they are related. These have been discussed at length in other posts and seems to have something to do with PG holding onto old file handles (Windows specific): 2007-01-24 06:24:16 ERROR: could not open relation 1663/16404/333779: Permission denied 2007-01-24 06:24:17 ERROR: could not open relation 1663/16404/333779: Permission denied 2007-01-24 06:24:18 ERROR: could not open relation 1663/16404/333779: Permission denied 2007-01-24 06:24:19 ERROR: could not open relation 1663/16404/333779: Permission denied I'm running PG 8.2.1 on Windows. Here is some of the output from show all: autovacuum;on autovacuum_analyze_scale_factor;0.02 autovacuum_analyze_threshold;250 autovacuum_freeze_max_age;2 autovacuum_naptime;1min autovacuum_vacuum_cost_delay;-1 autovacuum_vacuum_cost_limit;-1 autovacuum_vacuum_scale_factor;0.08 autovacuum_vacuum_threshold;500 stats_block_level;on stats_command_string;on stats_reset_on_server_start;off stats_row_level;on stats_start_collector;on vacuum_cost_delay;20ms vacuum_cost_limit;200 vacuum_cost_page_dirty;20 vacuum_cost_page_hit;1 vacuum_cost_page_miss;10 vacuum_freeze_min_age;1 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Cannot Restart PostgreSQL-8.1.4
I had a problem with SQL-Ledger running on the local httpd that traced back to some crufty old libpg.so* from 2003 and 2004 in /usr/local/lib. I removed those (saved them, actually), ran ldconfig, then restarted both httpd and postgresql. Unfortunately, the latter really has not started despite indicating on the console that it has. Postgres-8.1.4 installed. Here're the libaries in /usr/lib/: [EMAIL PROTECTED] ~]$ ll /usr/lib/libpq* -rw-r--r-- 1 root root 149728 2006-05-24 15:06 /usr/lib/libpq.a lrwxrwxrwx 1 root root 12 2006-07-06 17:19 /usr/lib/libpq.so - libpq.so.4.1* lrwxrwxrwx 1 root root 12 2006-01-27 10:22 /usr/lib/libpq.so.3 - libpq.so.3.1* -rwxr-xr-x 1 root root 110586 2006-01-26 09:49 /usr/lib/libpq.so.3.1* lrwxrwxrwx 1 root root 12 2006-07-06 17:19 /usr/lib/libpq.so.4 - libpq.so.4.1* -rwxr-xr-x 1 root root 111532 2006-05-24 15:06 /usr/lib/libpq.so.4.1* And, in case postgres is looking in /usr/local/lib/ it has: [EMAIL PROTECTED] ~]$ ll /usr/local/lib/libpq* lrwxrwxrwx 1 root root 21 2007-01-24 10:38 /usr/local/lib/libpq.so - /usr/lib/libpq.so.4.1* lrwxrwxrwx 1 root root 21 2007-01-24 10:35 /usr/local/lib/libpq.so.3 - /usr/lib/libpq.so.3.1* lrwxrwxrwx 1 root root 21 2007-01-24 10:38 /usr/local/lib/libpq.so.4 - /usr/lib/libpq.so.4.1* When I run '/etc/rc.d/rc.postgresql start' it returns 'Starting PostgreSQL: ok', but '/etc/rc.d/rc.postgresql status' returns 'pg_ctl: neither postmaster nor postgres running' which is true. I don't know what I did to break the installation, but I would greatly appreciate help getting it running again ASAP. TIA, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stats collector frozen?
Jeremy Haile [EMAIL PROTECTED] writes: The stats collector appears to still be running, since I can see a postgres.exe process with -forkcol. However, I never notice it using I/O or CPU usage. Also, querying the pg_stat_user_tables view shows no change in table stats even for tables that change very frequently. Is $PGDATA/global/pgstat.stat getting updated? (watch the file mod time at least) Do you see any pgstat.tmp file in there? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] capacity of tables
Hello, i am Guillermo Arias, from Peru. I have a doubt about capacity of tables.I am developing a software for accountants, and my principal problem is about the table for the vouchers. I have to decide to make a table for each year or only one table for all the years. This table has 11 fields: varchar(10) and 2 fields: numeric (12,2) and is intended to have 900,000 records per year x 13 years = 11'700,000 recordsWhat can you suggest me? i do not want the system to be slow using this table.thanks[EMAIL PROTECTED]Get your FREE, LinuxWaves.com Email Now! -- http://www.LinuxWaves.comJoin Linux Discussions! -- http://Community.LinuxWaves.com
Re: [GENERAL] capacity of tables
One table. If you need to split, you can allways do that via inheritance constraint exclusion, thereby creating table partitioning. Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
Rich Shepard [EMAIL PROTECTED] writes: When I run '/etc/rc.d/rc.postgresql start' it returns 'Starting PostgreSQL: ok', but '/etc/rc.d/rc.postgresql status' returns 'pg_ctl: neither postmaster nor postgres running' which is true. Have you looked in the postmaster log? Under almost all circumstances, a failing postmaster should emit some sort of bleat on stderr, or perhaps to syslog if you've configured it that way (and it managed to get as far as absorbing the contents of postgresql.conf). Some PG start scripts redirect the postmaster's stderr to /dev/null; if yours does, change it, at least till you've resolved the problem. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stats collector frozen?
pgstat.stat was last updated 1/22 12:25pm - there is no pgstat.tmp. Coincidentally (I think not) - the last auto-analyze was performed at 2007-01-22 12:24:11.424-05. The logs for 1/22 are empty - so no errors or anything like that to give clues... Thanks! Jeremy Haile On Wed, 24 Jan 2007 14:00:52 -0500, Tom Lane [EMAIL PROTECTED] said: Jeremy Haile [EMAIL PROTECTED] writes: The stats collector appears to still be running, since I can see a postgres.exe process with -forkcol. However, I never notice it using I/O or CPU usage. Also, querying the pg_stat_user_tables view shows no change in table stats even for tables that change very frequently. Is $PGDATA/global/pgstat.stat getting updated? (watch the file mod time at least) Do you see any pgstat.tmp file in there? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] capacity of tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/24/07 13:06, guillermo arias wrote: Hello, i am Guillermo Arias, from Peru. I have a doubt about capacity of tables. I am developing a software for accountants, and my principal problem is about the table for the vouchers. I have to decide to make a table for each year or only one table for all the years. This table has 11 fields: varchar(10) and 2 fields: numeric (12,2) and is intended to have 900,000 records per year x 13 years = 11'700,000 records PostgreSQL will easily handle 12 million rows. What can you suggest me? i do not want the system to be slow using this table. Performance (*not* including hardware) is based on: 1. Well-written queries. 2. How the indexes match the queries. EXPLAIN ANALYZE is your friend!! 3. The knowledge that it is expensive to insert into/update/delete from an index, so create the indexes you need, but don't go crazy. 4. Continual monitoring: production usage patterns will probably be different from what you expected. Do not be surprised if you have to add or modify indexes later on. 5. Using an up-to-date version of PostgreSQL. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFt7LsS9HxQb37XmcRAo8QAJwLjj26KiJl7gNvt6joKTuo6oGrIwCfWHcz y9EqHqWygdYKPss3J47TgUc= =jaMf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4 -- SOLVED!
On Wed, 24 Jan 2007, Tom Lane wrote: Have you looked in the postmaster log? Tom, I went looking for it before writing, but did not find it. Now I have. The ownership and permissions of /var/lib/pgsql and /var/lib/postgresql were FUBAR. I changed the permissions and it's now running. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, 24 Jan 2007, Tom Lane wrote: Have you looked in the postmaster log? Tom, et al.: I got postmaster running now, but have been blocked by Bug #2701 when I try to run SQL-Ledger. Google has 1,830 hits for it, so I'm far from the first. :-) I'm running -8.1.4 here. In /usr/lib/ I have: lrwxrwxrwx 1 root root 12 2006-07-06 17:19 libpq.so - libpq.so.4.1* lrwxrwxrwx 1 root root 12 2006-01-27 10:22 libpq.so.3 - libpq.so.3.1* -rwxr-xr-x 1 root root 110586 2006-01-26 09:49 libpq.so.3.1* lrwxrwxrwx 1 root root 12 2006-07-06 17:19 libpq.so.4 - libpq.so.4.1* -rwxr-xr-x 1 root root 111532 2006-05-24 15:06 libpq.so.4.1* What version of libpq.so do I need? Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, Jan 24, 2007 at 12:02:30PM -0800, Rich Shepard wrote: I got postmaster running now, but have been blocked by Bug #2701 when I try to run SQL-Ledger. Google has 1,830 hits for it, so I'm far from the first. :-) I'm running -8.1.4 here. In /usr/lib/ I have: snip What version of libpq.so do I need? The server doesn't care, what matter is what version the *client* (in this case SQL-Ledger) expects. ldd should tell you that. (It's ok to have multiple versions of the client library installed...) Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Installing PostgreSQL under Cpanel
On Wed, Jan 24, 2007 at 02:17:53 +0800, Erick Papadakis [EMAIL PROTECTED] wrote: I was just looking at the ident/trust/etc authentication banter from pgsql docs. Couldn't make out what greek was on there. When I jostled a bit, and finally understood it, and really wanted to write it in plain English for the next simple user like myself who just wants to get cracking with the db and doesn't care about the admin intricasies, then how I should I write it? That's really not a good idea. I strongly recommend at least skimming through the entire postgres manual before trying to admin your own instance of it. Spending some time up front to better understand what you are doing and what options you have is going to save you much grief down the road. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys from INSERT
On Tue, Jan 23, 2007 at 23:19:47 -0600, Adam Rich [EMAIL PROTECTED] wrote: And your normal query would be this: INSERT into mytable (id,value) values (1,foo),(2,bar); Your new query would be like this: INSERT into mytable (id,value) values (1,foo),(2,bar) RETURNING id; Note that you will want to be using single quotes not double quotes. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] capacity of tables
People In my experience work very well con tables with 172.000.000 of records ( 172 millions). In fact is not too large number of records for postgresql. important aspect of this installation is your .conf file, take care of this, check old email with config subject. Best regards mdc --- Ron Johnson [EMAIL PROTECTED] escribió: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/24/07 13:06, guillermo arias wrote: Hello, i am Guillermo Arias, from Peru. I have a doubt about capacity of tables. I am developing a software for accountants, and my principal problem is about the table for the vouchers. I have to decide to make a table for each year or only one table for all the years. This table has 11 fields: varchar(10) and 2 fields: numeric (12,2) and is intended to have 900,000 records per year x 13 years = 11'700,000 records PostgreSQL will easily handle 12 million rows. What can you suggest me? i do not want the system to be slow using this table. Performance (*not* including hardware) is based on: 1. Well-written queries. 2. How the indexes match the queries. EXPLAIN ANALYZE is your friend!! 3. The knowledge that it is expensive to insert into/update/delete from an index, so create the indexes you need, but don't go crazy. 4. Continual monitoring: production usage patterns will probably be different from what you expected. Do not be surprised if you have to add or modify indexes later on. 5. Using an up-to-date version of PostgreSQL. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFt7LsS9HxQb37XmcRAo8QAJwLjj26KiJl7gNvt6joKTuo6oGrIwCfWHcz y9EqHqWygdYKPss3J47TgUc= =jaMf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, 24 Jan 2007, Martijn van Oosterhout wrote: What version of libpq.so do I need? The server doesn't care, what matter is what version the *client* (in this case SQL-Ledger) expects. ldd should tell you that. Martijn, OK. I'll go back there (which is where I started this journey). I don't know how to run ldd on a perl script. (It's ok to have multiple versions of the client library installed...) Sure. I do for many libraries. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Idle in transaction - Explination ..
Where I work I'm in charge of more then a few PostgreSQL databases. I understand why idle in transaction is bad, however I have some developers who I'm having a real difficult time fully explaining to them why its bad. Oh, and by bad I mean they have transactions that are sitting idle for 6+ hours at a time. Mainly because they don't speak very good English, and my words like MVCC and VACUUM have them tilting their heads wondering what language I'm speaking. I've tried searching the mailing lists for a good explanation, but haven't really found one thats easy to translate. They are Japanese, but I don't speak Japanese, so finding any resource in Japanese that explains it is beyond my ability. Would anyone happen to have a simple explanation, or a page online thats written in Japanese that I can pass off that might explain why this is bad? Is there a Wiki somewhere that says 101 ways to cause your DBA an aneurysm that covers things like this? :) Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Installing PostgreSQL under Cpanel
# [EMAIL PROTECTED] / 2007-01-23 23:17:31 +0800: Please don't top-post, it disturbes the flow of the communication. Interesting. I prefer getting to the point an author is making. Top posting means you end up far off the mark. There's documentation about that too, at the end of the installation instructions IIRC. I did not see them. Please point me? I'd deeply appreciate it. Please stop asking for spoonfeeding. You have no problems reading email, how about some web pages? http://www.postgresql.org/docs/8.2/interactive/index.html http://www.postgresql.org/docs/8.2/interactive/bookindex.html -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stats collector frozen?
Searching the archives, I found a couple of 2006 posts that seem somewhat related to my problem (although I don't see any solutions listed...): http://archives.postgresql.org/pgsql-hackers/2006-09/msg00415.php http://archives.postgresql.org/pgsql-hackers/2006-04/msg00127.php Tom, since you were involved in these - did you ever figure out how to resolve the issues of the stats collector getting stuck in Windows? Thanks, Jeremy Haile On Wed, 24 Jan 2007 14:19:05 -0500, Jeremy Haile [EMAIL PROTECTED] said: pgstat.stat was last updated 1/22 12:25pm - there is no pgstat.tmp. Coincidentally (I think not) - the last auto-analyze was performed at 2007-01-22 12:24:11.424-05. The logs for 1/22 are empty - so no errors or anything like that to give clues... Thanks! Jeremy Haile On Wed, 24 Jan 2007 14:00:52 -0500, Tom Lane [EMAIL PROTECTED] said: Jeremy Haile [EMAIL PROTECTED] writes: The stats collector appears to still be running, since I can see a postgres.exe process with -forkcol. However, I never notice it using I/O or CPU usage. Also, querying the pg_stat_user_tables view shows no change in table stats even for tables that change very frequently. Is $PGDATA/global/pgstat.stat getting updated? (watch the file mod time at least) Do you see any pgstat.tmp file in there? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wednesday 24 January 2007 13:08, Rich Shepard [EMAIL PROTECTED] wrote: OK. I'll go back there (which is where I started this journey). I don't know how to run ldd on a perl script. It'll be whatever version the DBD::Pg module is compiled to use. Just rebuilding and installing an updated version of that package will likely fix your problem. -- Pulling together is the aim of despotism and tyranny. Free men pull in all kinds of directions. -- Terry Pratchett ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, Jan 24, 2007 at 01:08:04PM -0800, Rich Shepard wrote: The server doesn't care, what matter is what version the *client* (in this case SQL-Ledger) expects. ldd should tell you that. Martijn, OK. I'll go back there (which is where I started this journey). I don't know how to run ldd on a perl script. Method 1: Find the .so module that perl uses (try locate Pg.so and ldd that). Method 2: strace -e open program And look at which version it tries to open. How exactly is it failing? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Idle in transaction - Explination ..
On Wed, Jan 24, 2007 at 01:15:43PM -0800, Weslee Bilodeau wrote: Where I work I'm in charge of more then a few PostgreSQL databases. I understand why idle in transaction is bad, however I have some developers who I'm having a real difficult time fully explaining to them why its bad. It's bad because it can invisibly lock other objects. There may be other reasons, too. Cheers, D Oh, and by bad I mean they have transactions that are sitting idle for 6+ hours at a time. Mainly because they don't speak very good English, and my words like MVCC and VACUUM have them tilting their heads wondering what language I'm speaking. I've tried searching the mailing lists for a good explanation, but haven't really found one thats easy to translate. They are Japanese, but I don't speak Japanese, so finding any resource in Japanese that explains it is beyond my ability. Would anyone happen to have a simple explanation, or a page online thats written in Japanese that I can pass off that might explain why this is bad? Is there a Wiki somewhere that says 101 ways to cause your DBA an aneurysm that covers things like this? :) Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Idle in transaction - Explination ..
Well, in very short terms: a idle transaction is not committed. This means, when it's a writing transaction, that in the best case you have one or more row locks blocking access to the updated/inserted rows and in the worst case one or more table locks, which will block access to a table completely. On Wednesday 24 January 2007 13:15, Weslee Bilodeau wrote: Where I work I'm in charge of more then a few PostgreSQL databases. I understand why idle in transaction is bad, however I have some developers who I'm having a real difficult time fully explaining to them why its bad. Oh, and by bad I mean they have transactions that are sitting idle for 6+ hours at a time. Mainly because they don't speak very good English, and my words like MVCC and VACUUM have them tilting their heads wondering what language I'm speaking. I've tried searching the mailing lists for a good explanation, but haven't really found one thats easy to translate. They are Japanese, but I don't speak Japanese, so finding any resource in Japanese that explains it is beyond my ability. Would anyone happen to have a simple explanation, or a page online thats written in Japanese that I can pass off that might explain why this is bad? Is there a Wiki somewhere that says 101 ways to cause your DBA an aneurysm that covers things like this? :) Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, 24 Jan 2007, Martijn van Oosterhout wrote: Method 1: Find the .so module that perl uses (try locate Pg.so and ldd that). Martijn, Thank you for teaching me a new trick today. The results of method 1 are: /lib/libsafe.so.2 (0xb7f76000) linux-gate.so.1 = (0xe000) libpq.so.3 = /usr/lib/libpq.so.3 (0xb7f5f000) libc.so.6 = /lib/tls/libc.so.6 (0xb7e1a000) libdl.so.2 = /lib/tls/libdl.so.2 (0xb7e16000) libcrypt.so.1 = /lib/tls/libcrypt.so.1 (0xb7de8000) libresolv.so.2 = /lib/tls/libresolv.so.2 (0xb7dd4000) libnsl.so.1 = /lib/tls/libnsl.so.1 (0xb7dbd000) /lib/ld-linux.so.2 (0x8000) So it's finding all the libraries it wants, and it's using the older libpq.so. Interesting. Method 2: strace -e open program It's a perl script -- actually, a large family of scripts -- that act as middleware between a web browser and an httpd. I don't see anything in the login.pl output that looks suspicious. How exactly is it failing? I open a new firefox tab and open http://localhost/sql-ledger/login.pl. After entering my username and password and pressing [Enter] I get an internal server error: The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, [EMAIL PROTECTED] and inform them of the time the error occurred, and anything you might have done that may have caused the error. More information about this error may be available in the server error log. The log file (/var/log/apache/error_log) shows: [Wed Jan 24 15:01:20 2007] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl /usr/local/sql-ledger/login.pl: symbol lookup error: /usr/lib/perl5/site_perl/5.8.8/i486-linux/auto/DBD/Pg/Pg.so: undefined symbol: PQserverVersion The version of DBD::Pg is 1.4.9 which is the most recent one available. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Postgres processes have a burst of CPU usage
Hello all, I have a setup in which four client machines access a Postgres database (8.1.1) (on a Linux box). So, there are connections from each machine to the database; hence, the Linux box has about 2 postgres processes associated with each machine. I am using the JDBC driver (postgresql-8.1-404.jdbc3.jar) to talk to the database. I am also using the Spring framework(1.2.2) and Hibernate (3.0.5) on top of JDBC. I use Apache's DBCP database connection pool (1.2.1). Now, there is one particular update that I make from one of the client machines - this involves a reasonably large object graph (from the Java point of view). It deletes a bunch of rows (around 20 rows in all) in 4-5 tables and inserts another bunch into the same tables. When I do this, I see a big spike in the CPU usage of postgres processes that are associated with ALL the client machines, not just the one I executed the delete/insert operation on. The spike seems to happen a second or two AFTER the original update completes and last for a few seconds. Is it that this operation is forcibly clearing some client cache on ALL the postgres processes? Why is there such an interdependency? Can I set some parameter to turn this off? Regards and thanks, S.Aiylam Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plpythonu array parameter
Anyone interested in arrays and plpython might find this interesting.
Based on the conversation below I put these functions in a library
(pg_stuff.py):
def arr2list(a):
a = a.replace({,[).replace(},])
pylist = eval(a)
return pylist
def list2arr(a):
parm = `a`
parm=parm.replace([,{).replace(],})
return parm
Then I was able to call them like this:
CREATE TYPE int_triple AS(
x int, y int, z int);
CREATE FUNCTION py_explode_4 (a int[], b int[])
returns setof int_triple
AS $$
import sys
sys.path.append('/Users/wsprague/lib')
import pg_stuff
x_list = pg_stuff.arr2list(a)
y_list = pg_stuff.arr2list(b)
for x in x_list:
for y in y_list:
yield(x, y, x+y)
$$ LANGUAGE plpythonu;
Yielding the following in postgres:
or_gis=# select * from py_explode_4(array[1,2], array[10,20]);
x | y | z
---++
1 | 10 | 11
1 | 20 | 21
2 | 10 | 12
2 | 20 | 22
(4 rows)
Not exactly earth shattering, but still cool. My next hack will be to
convert an array to a matrix and find the eigenvalues and convert
back... It does seem like array to list conversion should happen
automatically, though.
Sim Zacks wrote:
I ran into an interesting issue trying to pass an array to a plpythonu
function (Postgresql 8.03).
When I googled the issue I found a number of people asking similar questions
and they haven't found
an answer. The problem is that there is no type mapped from a postgresql
array to a python list.
These conversion functions will map between a postgresql array and a python
list and back. Is there
a way to stick this in the integration code somewhere, so that every time an
array is passed to/from
a python function it converts it transparently?
In the python function itself this type of code will work, what I don't know
is how to change the
internals so that when an array variable is passed in it automatically puts
it through this code and
hands off a python list variable.
The second function would work as is, and return a postgresql array.
Of course both would have to work with any type of array and not just text[]
create or replace function pgarray_to_pylist(text[]) returns {python list} as
$$
parm=args[0]
parm=parm.replace({,[).replace(},])
pylist=eval(parm)
return pylist
$$ language 'plpythonu'
create or replace function pylist_to_pgarray({python list}) return text[] as
$$
parm=`args[0]`
parm=parm.replace([,{).replace(],})
return parm
$$ language 'plpythonu'
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Installing PostgreSQL under Cpanel
There are a couple of PostgreSQL tutorials around the web. I have followed them. Downloaded the *.rpm files and installed them. Then gone into my WHM (https://myserver:2087) and enabled the config, and set up the postgres user with an su command adduser postgres. Now what? 1. Where's the interactive shell? 2. How do I start the service? How should I set it up to restart automatically if (a) the service fails (b) machine reboots? 3. How can I start creating a database, creating users, testing things out? I looked at the Documentation (http://www.postgresql.org/docs/8.2/interactive/config-setting.html) and could not find one intuitive instruction to actually get cracking with PostgreSQL without getting all configgy. I'd appreciate some pointers. Thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Problem with replace
Hello, I have to relpace string http://example.com/index.php?module=articlesid= to string /module/ in whole column in table. How exacly use replace? I noob in postgres, I think it could be quite easy. Please, help ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Converting 7.x to 8.x
What would be the faster way to convert a 7.4.x database into an 8.x database? A dump of the database takes over 20 hours so we want to convert the database without having to do a dump and resptore.
[GENERAL] NETEZZA cursors, anyone?
Greetings, How can we use cursors in NETEZZA? Can anyone please show me a simple example here. I am asking about netezza is because it uses postgresql. and I cant find any NETEZZA group here. Any help would be appreciated TIA ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] NETEZZA cursors, anyone?
Greetings, How can we use cursors in NETEZZA? Can anyone please show me a simple example here. I am asking about netezza because it runs on postgresql. and I cant find any NETEZZA group here. Any help would be appreciated TIA ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On 1/24/07, Rich Shepard [EMAIL PROTECTED] wrote: libpq.so.3 = /usr/lib/libpq.so.3 (0xb7f5f000) So it's finding all the libraries it wants, and it's using the older libpq.so. Interesting. Where is your other installation of PostgreSQL installed? I suspect this is because somewhere along the line you had a partial installation of PostgreSQL (newer pg_config, but missing libpq.so.4) [Wed Jan 24 15:01:20 2007] [error] [client 127.0.0.1] Premature end of script headers: /usr/local/sql-ledger/login.pl /usr/local/sql-ledger/login.pl: symbol lookup error: /usr/lib/perl5/site_perl/5.8.8/i486-linux/auto/DBD/Pg/Pg.so: undefined symbol: PQserverVersion The version of DBD::Pg is 1.4.9 which is the most recent one available. The reason is when DBD::Pg probed the PostgreSQL configuration using pg_config --version it received a response of at least 8.0.0 or later. Yet when it attempted to link it could only find libpq.so.3. I would try rebuilding DBD::Pg, and run make test to verify whether it works before installing. I don't know what the rules are for ld to locate libraries and link them, but perhaps if ldconfig was not run and libpq.so.4 didn't link to the right version it used libpq.so.3. -- Chad http://www.postgresqlforums.com/
Re: [GENERAL] Questions about horizontal partitioning
Ron Johnson wrote: On 01/08/07 20:39, Tom Lane wrote: John Sales mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] writes: By doing this, I'm hoping that the query optimizer is smart enough to see that if a query comes in and requests only the six columns (that are in the narrower table) that PostgreSQL won't have to load the wider table into the buffer pool, and thereby actually have to only access about 10% the amount of disk that it presently does. No. It still has to touch the second table to confirm the existence of rows to join to. But if a query /requests *only* the six columns (that are in the narrower table)/, why will the optimizer care about the other 224 columns? It would. A query that uses an inner join implies that a matching entry must exist in both tables - so the join must occur, otherwise you could be returning rows that don't satisfy the join condition. However, if the primary key is entirely within those six columns, there will have to be an index on it in both tables to enforce the primary key constraint. In that case, an inner join could be performed with an index lookup or an index scan plus hash join, for a query that didn't use any other columns. Whether that translates into a significant I/O reduction depends on how wide and how frequently non-NULL those other columns are.
Re: [GENERAL] Installing PostgreSQL under Cpanel
Have you read http://www.postgresql.org/docs/8.2/static/tutorial.html ?? If there are specific parts in there that are unclear or missing I'm sure the developers would be happy to know. -Doug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] column insert/alter got me stumped!
guys,
just wanna change value of 1 existing column
# insert into tablename (columnname) values ('value');
...works
# select columnname from tablename where columnname='value';
...works
# insert into tablename (columnname) values ('value') select
columnname from tablename where columnname='value';
or
# insert into tablename (columnname) values ('value') where columnname='value';
...combinations don't work
# alter table tablename alter column columnname set value='value';
...doesn't work either
embarrassingly simple? pgadmin III thinks so
jzs
http://www.postgresql.org/docs/8.1/static/sql-insert.html
http://www.postgresql.org/docs/8.0/static/sql-altertable.html
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] column insert/alter got me stumped!
I think you want this:
update tablename set columname = 'value'
where column2 = 'value2';
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of John Smith
Sent: Wednesday, January 24, 2007 5:59 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] column insert/alter got me stumped!
guys,
just wanna change value of 1 existing column
# insert into tablename (columnname) values ('value');
...works
# select columnname from tablename where columnname='value';
...works
# insert into tablename (columnname) values ('value') select
columnname from tablename where columnname='value';
or
# insert into tablename (columnname) values ('value') where
columnname='value';
...combinations don't work
# alter table tablename alter column columnname set value='value';
...doesn't work either
embarrassingly simple? pgadmin III thinks so
jzs
http://www.postgresql.org/docs/8.1/static/sql-insert.html
http://www.postgresql.org/docs/8.0/static/sql-altertable.html
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, 24 Jan 2007, Alan Hodgson wrote: It'll be whatever version the DBD::Pg module is compiled to use. Just rebuilding and installing an updated version of that package will likely fix your problem. Alan, I'm not following you. DBD::Pg is the most current version (1.49 from May 2006). That I built and installed this morning. It's calling /usr/lib/libpg.so.3.1, but I also have /usr/lib/ligpg.so.4.1. The perl script, login.pl, hasn't changed in a couple of years. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
Rich Shepard [EMAIL PROTECTED] writes: I'm not following you. DBD::Pg is the most current version (1.49 from May 2006). That I built and installed this morning. It's calling /usr/lib/libpg.so.3.1, but I also have /usr/lib/ligpg.so.4.1. Somehow DBD::Pg picked up the wrong (older) version of libpq.so; you need to look into why. Wrong search path while building maybe? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4
On Wed, 24 Jan 2007, Tom Lane wrote: Somehow DBD::Pg picked up the wrong (older) version of libpq.so; you need to look into why. Wrong search path while building maybe? Tom, I _thought_ that might be what happened, but I wanted to make sure before I dug myself into a hole. You are absolutely correct. I'll bet it looked first at /usr/local/lib and found the really old versions of libpq.so. I had cleaned those out, but after making and installing Pg.so. I looked through the DBD::Pg source files and the paths looked OK to my naive eyes. So I just re-ran the 'perl Makefile.PL, make, make test, and make install' series. All tests passed, and SQL-Ledger now comes up. Whew!! I'll bet those old libraries were left over from Red Hat 7.3; it was in September of 2003 that I migrated to Slackware. You have my sincere thanks for clearing up a mess that occupied me since yesterday afternoon. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Adam Rich wrote: Let's say you have a table with id, value columns. And your normal query would be this: INSERT into mytable (id,value) values (1,foo),(2,bar); Your new query would be like this: INSERT into mytable (id,value) values (1,foo),(2,bar) RETURNING id; And you would get a result back with one column (id) and two rows (the newly inserted keys). You can also return other fields if you like, you're not limited to just the generated keys. Thank you Alvaro and Adam, Now playing devil's advocate, can anyone see scenarios where this will not work as expected? Examples (descriptions not sql necessarily) of those would be helpful too... Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Now playing devil's advocate, can anyone see scenarios where this will not work as expected? Examples (descriptions not sql necessarily) of those would be helpful too... Just to be sure, will the RETURNING clause work with custom sequences (say, non numeric or increment by two) or other types of key generators?... And how will triggers interfere with it (if at all)? I honestly have limited experience with server generated keys that are not numeric/serial (or uuids), or with cases where triggers, constraints, etc might come into play (I'm used to using the DB mostly as a storage device and using server-side logic..) Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem with replace
On 23 Jan 2007 13:05:41 -0800, slawosz [EMAIL PROTECTED] wrote:
Hello,
I have to relpace string
http://example.com/index.php?module=articlesid= to string /module/ in
whole column in table. How exacly use replace? I noob in postgres, I
think it could be quite easy.
Please, help
try replace command for simple things:
Administrator=# select replace ('abcdef', 'ab', '12');
replace
-
12cdef
(1 row)
(to do the whole table, do:)
update foo set bar = replace(bar, ab, '12');
for more complex things use regex_replace() (see docs)
merlin
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Idle in transaction - Explination ..
On 1/25/07, Weslee Bilodeau [EMAIL PROTECTED] wrote: Where I work I'm in charge of more then a few PostgreSQL databases. I understand why idle in transaction is bad, however I have some developers who I'm having a real difficult time fully explaining to them why its bad. Oh, and by bad I mean they have transactions that are sitting idle for 6+ hours at a time. Mainly because they don't speak very good English, and my words like MVCC and VACUUM have them tilting their heads wondering what language I'm speaking. I've tried searching the mailing lists for a good explanation, but haven't really found one thats easy to translate. They are Japanese, but I don't speak Japanese, so finding any resource in Japanese that explains it is beyond my ability. Would anyone happen to have a simple explanation, or a page online thats written in Japanese that I can pass off that might explain why this is bad? Is there a Wiki somewhere that says 101 ways to cause your DBA an aneurysm that covers things like this? :) Long running transactions (waiting on user input especially) are bad in practice and also in principle. The purpose of transactions is to accumulate multiple changes to a datastore so that you can never catch it in an invalid state. MVCC allows the database to do this while providing concurrency...the whole point of that is to keep as few locks for the shortest term possible. A major challenge in high activity databases is to keep information consistent and valid at all times while at the same time minimizing contention to high traffic objects. If you must keep long-term locks, check out advisory locks (8.2+): Keep in mind that while advisory locks avoid some of the headaches associated with long transactions they have concurrency issues at the same time. I wrote a little bit about them (shameless plug:) http://merlinmoncure.blogspot.com/2006/12/postgresql-8.html http://merlinmoncure.blogspot.com/2006/12/advisory-locks-part-2.html Most applications do not require long term (aka pessimistic) locks: they are a crutch. A more elegant solution is to provide an update mechanism such that the application alerts the user that the data has changed out from under them (meaning, the user's terminal has stale data) before sending it back to the server. This provides an opportunity to merge changes or pick one set of data over another. At the very least, it provides a way to minimize contention to when data is actually changed instead of broad high level locks by simply viewing data. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Ken Johanson wrote: Just to be sure, will the RETURNING clause work with custom sequences (say, non numeric or increment by two) or other types of key generators?... And how will triggers interfere with it (if at all)? RETURNING has nothing to do with sequences per se - it's just a way of getting at any of the columns of the new row, regardless of how they got filled. The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. http://www.postgresql.org/docs/8.2/interactive/sql-insert.html - John Burger MITRE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Ken Johanson wrote: Now playing devil's advocate, can anyone see scenarios where this will not work as expected? Examples (descriptions not sql necessarily) of those would be helpful too... Just to be sure, will the RETURNING clause work with custom sequences (say, non numeric or increment by two) or other types of key generators?... And how will triggers interfere with it (if at all)? I honestly have limited experience with server generated keys that are not numeric/serial (or uuids), or with cases where triggers, constraints, etc might come into play (I'm used to using the DB mostly as a storage device and using server-side logic..) As far as I know, RETURNING will give you exactly the values that are put into the table. If you had a weird sequence or strange stuff invoked in functions, they will be computed much earlier than the RETURNING values be fetched, so the latter will get the correct values all the time. (It would be quite dumb to do otherwise anyway). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] indexing primary and foreign keys w/lookup table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All. I was wondering...I currently have indexes on the primary key id and foreign key id's for tables that resemble the following. Is this a good idea/when would it benefit me? I don't want waste a lot of unnecessary space on indexes. CREATE TABLE stuff ( id BIGSERIAL PRIMARY KEY, stuff TEXT ); CREATE INDEX stuff_id ON stuff(id); CREATE TABLE accounts ( id BIGSERIAL PRIMARY KEY, nameTEXT, email TEXT, ); CREATE INDEX accounts_id ON accounts(id); CREATE TABLE stuff_by_account ( account_id BIGINT REFERENCES accounts(id), stuff_idBIGINT REFERENCES stuff(id) ); CREATE INDEX stuff_by_account_account_id ON stuff_by_account (account_id); CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id); do I need any/all of these indexes for my lookup table to work well? I am thinking I can get rid of stuff_id and accounts_id. Thoughts? - -Neal -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (Darwin) iD8DBQFFuC6POUuHw4wCzDMRArt1AJoC9QUwmTxgcUKw+Agp+zYIDq/G/QCgolHT oDFkLBCLjZBST7ypzbOOfew= =CCSs -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] indexing primary and foreign keys w/lookup table
CREATE TABLE stuff_by_account ( account_idBIGINT REFERENCES accounts(id), stuff_idBIGINT REFERENCES stuff(id) ); CREATE INDEX stuff_by_account_account_id ON stuff_by_account(account_id); CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id); do I need any/all of these indexes for my lookup table to work well? I am thinking I can get rid of stuff_id and accounts_id. Thoughts? You should have indexes on the fields used in joins. So if you join stuff_by_account to accounts using account_id, make sure there is an index on both sides of that (primary key already has one but the lookup table needs one too). Foreign keys need them because when a row gets added/removed, the index is used to quickly make sure the data is in the external table. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Converting 7.x to 8.x
Carlos wrote: What would be the faster way to convert a 7.4.x database into an 8.x database? A dump of the database takes over 20 hours so we want to convert the database without having to do a dump and resptore. That's your only option as far as I know (I'm sure someone will correct me if that's not the case). You can't do a binary conversion or anything like that because the postgres internals are different between major versions. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] indexing primary and foreign keys w/lookup table
On 1/25/07, Neal Clark [EMAIL PROTECTED] wrote: I was wondering...I currently have indexes on the primary key id and foreign key id's for tables that resemble the following. Is this a good idea/when would it benefit me? I don't want waste a lot of unnecessary space on indexes. CREATE TABLE stuff ( id BIGSERIAL PRIMARY KEY, stuff TEXT ); CREATE INDEX stuff_id ON stuff(id); postgresql will create an index for you if you have a primary key on the table...so you don't have to create one yourself. CREATE TABLE stuff_by_account ( account_id BIGINT REFERENCES accounts(id), stuff_idBIGINT REFERENCES stuff(id) ); I this is wrong. as you have laid it out, the create way to create this table would be CREATE TABLE stuff_by_account ( account_id BIGINT REFERENCES accounts(id), stuff_idBIGINT REFERENCES stuff(id), primary key(account_id, stuff_id) ); this will create a key (and thus an index), on account_id, stuff_id. This will speed up lookups to account and greatly speed lookups to account and stuff at the same time. However, you may want to create in index on stuff alone. do I need any/all of these indexes for my lookup table to work well? I am thinking I can get rid of stuff_id and accounts_id. Thoughts? Try giving natural keys a whirl. This means not automatically making a primary serial key for every table and trying to make primary keys from the non autogenerated keys in the table. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Example of RETURNING clause to get auto-generated keys
Alvaro Herrera [EMAIL PROTECTED] writes: Ken Johanson wrote: Just to be sure, will the RETURNING clause work with custom sequences (say, non numeric or increment by two) or other types of key generators?... And how will triggers interfere with it (if at all)? As far as I know, RETURNING will give you exactly the values that are put into the table. RETURNING evaluates the given expression-list over the values that were actually stored. There's no way for a datatype or BEFORE trigger to fool it. The only possibly interesting case is if you had an AFTER trigger that proceeded to modify the stored row by issuing an UPDATE ... but that would be a pretty silly/inefficient way to do things, and even then I think that RETURNING is telling the truth as of the time that the tuple insert/update happened. It can't be supposed to be prescient about subsequent changes. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] indexing primary and foreign keys w/lookup table
On Wed, Jan 24, 2007 at 20:14:07 -0800, Neal Clark [EMAIL PROTECTED] wrote: I was wondering...I currently have indexes on the primary key id and foreign key id's for tables that resemble the following. Is this a good idea/when would it benefit me? I don't want waste a lot of unnecessary space on indexes. Not exactly. Primary keys already result in an index being created to enforce uniqueness, so the manually created indexes are redundant. CREATE TABLE stuff_by_account ( account_id BIGINT REFERENCES accounts(id), stuff_idBIGINT REFERENCES stuff(id) ); CREATE INDEX stuff_by_account_account_id ON stuff_by_account (account_id); CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id); For this last case, you most likely want to declare either account_id, stuff_id or stuff_id, account_id as a primary key. You may want to create an index just on the second column of the primary key, depending on your usage pattern. You almost certainly wouldn't want to create an index on the first column of the primary key. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Converting 7.x to 8.x
On Thu, Jan 25, 2007 at 15:43:19 +1100, Chris [EMAIL PROTECTED] wrote: Carlos wrote: What would be the faster way to convert a 7.4.x database into an 8.x database? A dump of the database takes over 20 hours so we want to convert the database without having to do a dump and resptore. That's your only option as far as I know (I'm sure someone will correct me if that's not the case). You can't do a binary conversion or anything like that because the postgres internals are different between major versions. People use slony to do this. You can ask on the slony list for more details. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
