[GENERAL] Dump all databases to corresponding files
Anybody know of a script that dumps all databases into corresponding dump files, e.g. $ ./dump template0 - template0.sql template1 - template1.sql db1 - db1.sql db2 - db2.sql ... Also, would this approach add up to equal the output of pg_dumpall, or does pg_dumpall dump additional things (if so, please describe how they'd also be dumped)? Thanks, csn Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates (http://voice.yahoo.com) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Selecting from two unrelated tables
I have two tables: items: id, title, added, ... news: id, headline, datetime, ... I'd like to select the latest 25 combined records from both tables. Is there a way to do this using just select? Thanks, csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql rising
PostgreSQL doesn't have any booth babes? ;P csn On 09/20/06 16:38, Philip Hallstrom wrote: [snip] I think that description is false. At a certain point in the management hierarchy, the only way anyone has the ability to evaluate something is on the basis of - if there is someone they can sue. - how attractive the sales rep is. Back in my youth, working for the family business (roofing/siding distributor, not many women, fewer attractive women), the most successful salespeople were always... young attractive women. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] database files are incompatible with server, after computer restart
--- Douglas McNaught [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: I don't think so -- I followed the instructions here: http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx But looking around, I see there's a pg_ctl in /usr/local/bin, but 'port contents postgresql8' shows a pg_ctl in /opt/local/lib/pgsql8/bin. Which one are you intending to run, and which one is first in the PATH? -Doug I don't know which to run now! I tried both '/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata' and putting /opt/local/lib/pgsql8/bin before /usr/local/bin in PATH and still get the same error. Is there some way to specify HAVE_INT64_TIMESTAMP to pg_ctl or just export what's in the existing pgdata and initdb a new dir and import? Thanks, csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] database files are incompatible with server, after computer restart
--- Douglas McNaught [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: I don't know which to run now! I tried both '/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata' and You might want an absolute path for 'pgdata' here--have you tried that? ~ $ /opt/local/lib/pgsql8/bin/pg_ctl start -D /Users/csn/pgdata/ postmaster starting ~ $ FATAL: database files are incompatible with server DETAIL: The database cluster was initialized without HAVE_INT64_TIMESTAMP but the server was compiled with HAVE_INT64_TIMESTAMP. HINT: It looks like you need to recompile or initdb. putting /opt/local/lib/pgsql8/bin before /usr/local/bin in PATH and still get the same error. Why don't you run both pg_ctl binaries with the --version option, then compare against the pg_control file in the data directory (I think that's what it's called). That should hopeully tell you which one to run ~ $ /usr/local/bin/pg_ctl --version pg_ctl (PostgreSQL) 8.1.3 ~ $ /opt/local/lib/pgsql8/bin/pg_ctl --version pg_ctl (PostgreSQL) 8.1.3 pgdata/global/pg_control seems to be binary. pgdata/PG_VERSION has 8.1 in it. Is there some way to specify HAVE_INT64_TIMESTAMP to pg_ctl or just export what's in the existing pgdata and initdb a new dir and import? You need to have the server running to export. :) I'm pretty sure you can get it running; you just haven't tried the right way yet. I'm stymied. ;) Thanks, csn -Doug __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] database files are incompatible with server, after computer restart
--- Douglas McNaught [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: I'm stymied. ;) So you've tried running both pg_ctl binaries against the data directory and both don't work? Yes. I wonder how you ever had a database working in that case. :) Me too! After 'initdb -D pgdata' and 'pg_ctl start -D pgdata' it ran for days during which I used it quite a bit. But once I restarted the computer... Are you sure you have the right data directory? Maybe the one you think was used isn't the one that was actually used. Yes. But I looked around and couldn't find any other data dirs. If you can't get either to work, you should hopefully be able to compile a version of the code with HAVE_64BIT_TIMESTAMP and get it to start up with your data. Hmm, I installed using DarwinPorts (I think, if that uses 'port') -- I wonder if it can be reinstalled that way and pass a HAVE_64BIT_TIMESTAMP flag to it. Besides my current problem, does HAVE_64BIT_TIMESTAMP have much impact on things? But I still strongly suspect this'll be an easy fix once we figure it out. :) Heh, I hope. :) I've never had a problem with PG before, and I have no idea how or why this happened (other than it coincided with rebooting). Thanks, csn -Doug __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] database files are incompatible with server, after computer restart
I installed and started PostgreSQL and it worked fine
for days. Then I restarted my computer and now I can't
start PostgreSQL ('pg_ctl -D pgdata -l pgdata/psql.log
start'). Here's what's in my log:
LOG: received immediate shutdown request
WARNING: terminating connection because of crash of
another server process
DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit,
because another server process exited abnormally and
possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to
the database and repeat your command.
FATAL: database files are incompatible with server
DETAIL: The database cluster was initialized without
HAVE_INT64_TIMESTAMP but the server was compiled with
HAVE_INT64_TIMESTAMP.
HINT: It looks like you need to recompile or initdb.
I guess my computer restart didn't agree with
PostgreSQL. But I've had crashes before and never had
a problem getting PostgreSQL going again. Anybody know
how to fix this? I also tried 'pg_resetxlog -f pgdata'
but still get the same error trying to start up.
Thanks,
csn
Mac OS 10.4.7
PostgreSQL 8.1.3
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] database files are incompatible with server, after computer restart
I don't think so -- I followed the instructions here: http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx But looking around, I see there's a pg_ctl in /usr/local/bin, but 'port contents postgresql8' shows a pg_ctl in /opt/local/lib/pgsql8/bin. ~ $ ll /opt/local/lib/pgsql8/bin/pg_ctl -rwxr-xr-x 2 root admin 47380 Aug 24 14:24 /opt/local/lib/pgsql8/bin/pg_ctl ~ $ ll /usr/local/bin/pg_ctl -rwxr-xr-x 1 root admin 149456 Apr 23 15:00 /usr/local/bin/pg_ctl I can't remember if I tried installing PostgreSQL some time ago using Fink or some other way. Any further ideas? Thanks, csn --- Douglas McNaught [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: DETAIL: The database cluster was initialized without HAVE_INT64_TIMESTAMP but the server was compiled with HAVE_INT64_TIMESTAMP. HINT: It looks like you need to recompile or initdb. Is it possible you have two PG installs on this machine, and you're trying to start the wrong one? Because it's hard to see how a restart could cause this error message--it's saying that your data files are incompatible with the code you're trying to run. -Doug __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] What's a good default encoding?
I tried changing my database to UTF8 and then importing the dump (even tried iconv). It choked (on an accented e). Then somehow the database got created as LATIN9, and I was able to import successfully. I guess if it works, I'll be leaving it alone for the time being. I still have problems when emdashes are stored in the database as HTML entities, but they're displayed as emdashes in a web form, but then get stored back in the database wrong when edited (an accented A IIRC). I dunno - maybe it's a browser or Rails thing. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Disabling persistent connections?
I have a setup (Lighttpd + Mongrel + Ruby on Rails) that seems to be using persistent connection, yet I don't see any config setting in any of their configs to disable it. One of the culprits (likely Mongrel) is keeping postgres connections around, eventually hitting postgres' max connections limit, even though the setup isn't getting much traffic currently. Is there a way in postgres to close idle connection after some time (or other remedy)? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] pg_dumpall: permission denied for relation pg_shadow
I tried using pg_dumpall and got this error: pg_dumpall: query failed: ERROR: permission denied for relation pg_shadow pg_dumpall: query was: SELECT usename, usesysid, passwd, usecreatedb, usesuper, valuntil, (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner FROM pg_shadow I haven't messed around with template0. This is a recent installation of postgres (8.0.x), done via yum. Any idea what the problem is and how to fix it? thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Inserting � in psql - invalid byte sequence for encoding UNICODE: 0xe9
I created a new database with encoding UTF8, connected using psql, and ensured the client encoding is also UTF8 (Unicode). But when I try to insert characters like 'é', I get this error: ERROR: invalid byte sequence for encoding UNICODE: 0xe9 Isn't this possible with psql? Hopefully it's not necessary to insert with values like '\xC3\xA1' instead (which I tried, but the values got inserted as is and weren't converted). CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] What's a good default encoding?
If you're going to be putting emdashes, letters with lines and circles above them, and similar stuff that's mostly European and American in a database, what's a good default encoding to use - UTF-8? CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] �� in text field
I'm updating a field via a web form, and an em-dash is getting stored in the database as 'âÂ-', and is getting displayed back on the web page as 'âÂ'. The encoding of the database is SQL_ASCII - should I change it? And if so, to what and how? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_dump data filter/tree
Is there some way/utility that allows dumping according to specified criteria? Say, for example, you have these tables: members comments items Where comments and items both have f/k's to members, and you'd like to dump all data for a specific member id. Possible? Thanks csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Eliminating seconds fractions from timestamps?
I have a bunch of timestamps like: 2005-11-20 20:45:48.281653-07 How can I change it so they never get saved with seconds fractions? Thanks csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] \s tail?
In psql, is it possible to tail \s (say, the last fifty lines), rather than do \set HISTSIZE 50 and have the entire history be only 50 lines? Like 'history | tail -n 50' in the shell? thanks csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Delete / F/K error
I tried to delete a row from 'sites' and get this rather complex error: SQL error: ERROR: insert or update on table types violates foreign key constraint $1 DETAIL: Key (page_template_id)=(8) is not present in table templates. CONTEXT: SQL statement UPDATE ONLY public.types SET item_template_id = NULL WHERE item_template_id = $1 SQL statement DELETE FROM ONLY public.templates WHERE site_id = $1 In statement: DELETE FROM sites WHERE id='1' I'm not sure what's wrong, or how to fix it. Any ideas? thanks csn __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] unique constraint with a null column?
I have three columns, and one of them can be null. I'd like to create a unique constraint across all three columns and allow only one null value. e.g. a|b|c abc|123|null abc|123|null # not allowed abc|456|null abc|456|987 abc|456|876 def|456|null def|456|null # not allowed Currently, the 'not allowed' lines are allowed. thanks csn __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] unique constraint with a null column?
--- Bruno Wolff III [EMAIL PROTECTED] wrote: On Fri, Dec 30, 2005 at 13:30:40 -0800, CSN [EMAIL PROTECTED] wrote: I have three columns, and one of them can be null. I'd like to create a unique constraint across all three columns and allow only one null value. e.g. a|b|c abc|123|null abc|123|null # not allowed abc|456|null abc|456|987 abc|456|876 def|456|null def|456|null # not allowed Currently, the 'not allowed' lines are allowed. That is how 'unique' constraints are supposed to work. One possible solution is to use some normal value instead of 'NULL' to represent that fact. I know ;). 'c' is actually 'parent_id' with a f/k contraint, so something like '0' wouldn't work. Hmm, IIRC indexes can have WHERE clauses - perhaps I can create a unique index that way... csn __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Delete / F/K error
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Fri, Dec 30, 2005 at 12:09:12PM -0800, CSN wrote:
ERROR: insert or update on table types violates
foreign key constraint $1
DETAIL: Key (page_template_id)=(8) is not present
in
table templates.
CONTEXT: SQL statement UPDATE ONLY
public.types
SET item_template_id = NULL WHERE
item_template_id
= $1
SQL statement DELETE FROM ONLY
public.templates
WHERE site_id = $1
In statement:
DELETE FROM sites WHERE id='1'
What are the table definitions for sites, templates,
and types?
I'd guess you have some ON DELETE CASCADE and ON
DELETE SET NULL
foreign key constraints in templates and types.
Think through what
happens when those constraints are triggered by the
delete on sites;
somehow you're ending up with a foreign key that
violates its
constraint so the delete fails.
What version of PostgreSQL is this?
--
Michael Fuhr
Here's the DDL for types:
CREATE TABLE types (
id integer DEFAULT nextval('types_id_seq'::text)
NOT NULL,
name character varying(255) NOT NULL,
item_count integer DEFAULT 0 NOT NULL,
page_template_id integer,
type_template_id integer,
item_template_id integer,
content_template_id integer,
items_template_id integer,
site_id integer NOT NULL
);
ALTER TABLE ONLY types
ADD CONSTRAINT $1 FOREIGN KEY (page_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT $2 FOREIGN KEY (type_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT $3 FOREIGN KEY (item_template_id)
REFERENCES templates(id) ON UPDATE CASCADE ON DELETE
SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT $4 FOREIGN KEY
(content_template_id) REFERENCES templates(id) ON
UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT $5 FOREIGN KEY
(items_template_id) REFERENCES templates(id) ON UPDATE
CASCADE ON DELETE SET NULL;
ALTER TABLE ONLY types
ADD CONSTRAINT fk_types_sites FOREIGN KEY
(site_id) REFERENCES sites(id) ON UPDATE CASCADE ON
DELETE CASCADE;
And templates:
CREATE TABLE templates (
id integer DEFAULT
nextval('templates_id_seq'::text) NOT NULL,
name character varying(255) NOT NULL,
type_id integer,
site_id integer
);
ALTER TABLE ONLY templates
ADD CONSTRAINT fk_templates_sites FOREIGN KEY
(site_id) REFERENCES sites(id) ON UPDATE CASCADE ON
DELETE CASCADE;
Hmm, looks like I forgot a templates_types f/k.
Sites doesn't have any f/k's or constraints.
I'm using version 8.0.2.
It turns out there were no corresponding records in
table 'types'. Furthermore, 'delete from templates
where site_id=1;' resulted in this error:
ERROR: insert or update on table types violates
foreign key constraint $1
DETAIL: Key (page_template_id)=(8) is not present
in table templates.
CONTEXT: SQL statement UPDATE ONLY
public.types SET item_template_id = NULL WHERE
item_template_id = $1
But I was able to individually delete each template
record, then do 'delete from sites where id=1' with no
resulting errors. I'm still confused what the problem
was.
thanks
csn
__
Yahoo! for Good - Make a difference this year.
http://brand.yahoo.com/cybergivingweek2005/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Negative offsets
I was playing around with negative offsets: select * from table1 order by col1 offset -5 limit 25; select * from table1 order by col1 offset -25 limit 25; select * from table1 order by col1 offset -250 limit 25; They all return the same resultset (offset 0). Is there even any point in allowing negative offsets - such as maybe someday they'll offset backwards? thanks csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] copy with where query?
Is it possible to copy data from a table into a file and specify a query for what data should be included? e.g. \copy table1 to 'data.txt' where col1=true thanks csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] ILIKE '%term%' and Performance
I'm thinking of enabling searches that use queries like select * from items where title ilike '%term%'. The items table has tens of thousands of rows. Is it worth worrying about the performance of such a query (since, if I'm not mistaken, it will never use indices). If it is, what's the best option - use tsearch? How does tsearch (or whatever else) compare performance-wise to not using it, or to typical index-based queries for that matter? thanks csn __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Can this pl/pgsql be simplified?
I have a trigger function that simply updates item counts when the items table changes (member_id or active changes). I'm curious if this bit of the code can be simplified? :) thanks csn ELSIF TG_OP = 'UPDATE' THEN IF (OLD.member_id is NULL and NEW.member_id is not null) or (OLD.member_id is not NULL and NEW.member_id is null) or OLD.member_id NEW.member_id THEN IF OLD.member_id is not null then IF OLD.active is true then update members set items_submitted=items_submitted-1, items_approved=items_approved-1 where id=OLD.member_id; ELSE update members set items_submitted=items_submitted-1 where id=OLD.member_id; END IF; END IF; IF NEW.member_id is not null then IF NEW.active is true then update members set items_submitted=items_submitted+1, items_approved=items_approved+1 where id=NEW.member_id; ELSE update members set items_submitted=items_submitted+1 where id=NEW.member_id; END IF; END IF; ELSIF OLD.active is false and NEW.active is true then update members set items_approved=items_approved+1 where id=NEW.member_id; ELSIF OLD.active is true and NEW.active is false then update members set items_approved=items_approved-1 where id=NEW.member_id; END IF; __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] not null error in trigger on unrelated column
I removed the not null contraint on members.admin and update items set active = false where member_id=38 results in the count columns in members getting updated AND members.admin getting set to NULL. Really bizarre. I dropped the trigger function and the trigger, then recreated both, and now they both appear to function properly. I don't remember seeing anything in the docs stating that this is necessary, so I guess perhaps it's a bug? csn --- CSN [EMAIL PROTECTED] wrote: I have a members table and an items table. Every time items.member_id or items.active gets changed, members.items_submitted and members.items_approved gets updated by a trigger on items. I added an admin column to members, and now this happens: = update items set active = false where member_id=38; ERROR: null value in column admin violates not-null constraint CONTEXT: SQL statement update members set items_approved=items_approved-1 where id= $1 PL/pgSQL function update_member_item_counts line 54 at SQL statement The relevant part of the (after) trigger function on items is: ELSIF OLD.active is true and NEW.active is false then update members set items_approved=items_approved-1 where id=NEW.member_id; END IF; Is it necessary to drop and recreate triggers and/or corresponding functions after changing a table's schema? I don't know how something is trying to set members.admin to null (table members has no triggers). thanks csn Postgresql 8.0.x __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] not null error in trigger on unrelated column
I have a members table and an items table. Every time items.member_id or items.active gets changed, members.items_submitted and members.items_approved gets updated by a trigger on items. I added an admin column to members, and now this happens: = update items set active = false where member_id=38; ERROR: null value in column admin violates not-null constraint CONTEXT: SQL statement update members set items_approved=items_approved-1 where id= $1 PL/pgSQL function update_member_item_counts line 54 at SQL statement The relevant part of the (after) trigger function on items is: ELSIF OLD.active is true and NEW.active is false then update members set items_approved=items_approved-1 where id=NEW.member_id; END IF; Is it necessary to drop and recreate triggers and/or corresponding functions after changing a table's schema? I don't know how something is trying to set members.admin to null (table members has no triggers). thanks csn Postgresql 8.0.x __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(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] Does PG Support Unicode on Windows?
Is there any truth to what this guy is saying? On the other hand, Postgresql claims that Windows does not support Unicode and you can't have Unicode fields on postgresql on Windows. This is a big mistake. See: http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html What do you mean a big mistake? By Microsoft? Or PostgreSQL? The Big mistake is in the Postgresql FAQ in saying that Windows doesn't support Unicode. XP supports Unicode very well indeed. The FAQ I referenced prattles on about Slovenian code pages, of all things. Windows Code pages have been superseded by Unicode. If you go to http://msdn.microsoft.com and search for Unicode you get an eyeful. Having defended the undefendable, however, I'd like to know if postgresql really still doesn't support Unicode on windows, as I plan to develop on WindowsXP and deploy on Linux. Warren Seltzer Thread here: http://www.ruby-forum.com/topic/3690#new csn __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] postgres: unknown hard error
I'm using PG 8.0.x on Windows XP Pro and a dialog popped up with Postgres: unknown hard error. After acknowledging it, the system became somewhat unresponsive and needed a reboot. There was nothing in event viewer (I guess PG logs there - it's configured to log to 'stderr'). Any way to further diagnose this? thanks csn __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] sequence aliases?
--- Martijn van Oosterhout kleptog@svana.org wrote:
On Sun, Nov 06, 2005 at 12:15:45AM -0500, Greg Stark
wrote:
Tom Lane [EMAIL PROTECTED] writes:
My advice to the Rails people would be to fix
whatever it is in their
code that is assuming a particular sequence
name, or indeed assuming
a sequence at all...
Well how else do you find the id of the last
inserted record without assuming
a sequence?
I suppose using something like:
select currval( pg_get_serial_sequence(
'table','col' ) );
I like that better than the current 'table_col_seq'.
Perhaps just currval('table', 'col'). Or perhaps
tables could be made to have a default sequence (one
that's associated with the primary key).
CSN
That avoids hardcoding the sequence id. Not assuming
a sequences at all
may be trickier.
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org
http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95%
perspiration. A patent is a
tool for doing 5% of the work and then sitting
around waiting for someone
else to do the other 95% so you can sue them.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] sequence aliases?
I'm checking out Ruby on Rails and there's a tutorial about using Postgresql views and making them updateable (http://wiki.rubyonrails.com/rails/pages/HowtoUsePostgresViewsAsTables). The tutorial suggests renaming the sequence for the table to coincide with the view so that Rails can automatically access the sequence. I don't like this as it may break other things. Is there a way to create an alias or something (e.g. quotes_seq_id points to quotes_table_seq_id), or is there a better way? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] trapping errors in plpgsql?
I have a table like so: id|username|email with unique indices on username and email. In a plpgsql function if an insert fails because of a duplicate on one of those fields, is it possible to trap the error, figure out which unique fields it applies to, and raise a custom error message? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] PostgreSQL 8.1 vs. MySQL 5.0?
Here are some apparent problems with MySQL 5.0: - Concurrent ALTER TABLE - Replicated Session Variables and Concurrent ALTER TABLE - BIT indexing that [doesn't] actually uses a BIT! - SELECT * FROM FOO WHERE ID IN ( SELECT FOO_ID FROM BAR ) [doesn't use index] http://www.feedblog.org/2005/10/whats_next_afte.html __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump with low priority?
nice comes to mind: nice pg_dump ... On Sat, 2005-10-22 at 07:39, Bryan Field-Elliot wrote: We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed. Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How much slower are numerics?
Another thing I've always wondered about ;), as I use numerics far more than floats. From the docs: However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types How much slower are numerics? And why (I guess it has to do with potentially different sizes)? Thanks, CSN __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] NULL != text ?
I was trying this: IF (OLD.value != NEW.value) THEN -- END IF; and couldn't get the condition to evaluate to true at all if OLD.value was NULL. I also tried: IF (OLD.value NOT LIKE NEW.value) THEN -- END IF; with the same result. But this works: IF ((OLD.value is NULL and NEW.value is NOT NULL) or (OLD.value != NEW.value)) THEN -- END IF; So, does NULL != 'abc' always evaluate to false? The manual (http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html) states don't compare NULL values using =, but nothing about using != CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(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] NULL != text ?
BTW, it (the SQL spec I presume) has always seemed contradictory to me that you can't do: select * from table where field=null; but can do: update table set field=null; (as opposed to 'update table set field to null' or similar). CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] update trigger not working
I'm trying to set up a trigger that simply updates a field's corresponding timestamp to now() whenever the field is updated. But it's not working. Trying to debug, I commented out the inner IF and END and the log seemed to indicate infinite recursion occurred. My next guess is that perhaps NULL's in OLD.stuff is causing the IF to behave other than what I expect. Thanks for any help! CSN CREATE or REPLACE function update_ts() returns trigger as $end$ BEGIN IF (TG_OP='UPDATE') THEN IF (OLD.stuff != NEW.stuff) THEN UPDATE table1 set stuff_ts=now() where id=NEW.id; END IF; END IF; RETURN NULL; END; $end$ language plpgsql; CREATE TRIGGER update_ts AFTER UPDATE ON table1 FOR EACH ROW EXECUTE PROCEDURE update_ts(); __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] Duplicate primary keys/rows
I don't know if I'm going to get a copy of pg_filedump. What's the best way to fix this - dump then restore? CSN --- Tom Lane [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: oid | ctid| xmin | cmin | xmax | cmax | id +---+-+--+-+--+- 125466 | (2672,11) | 1445346 |0 | 1481020 | 0 | 985 125466 | (2745,50) | 1481020 |0 | 1682425 | 2 | 985 Hmm. The fact that the dup rows have the same OID indicates pretty strongly that they are actually two versions of the same row, and not two independently inserted rows. Furthermore we can see that xact 1481020 deleted the first version and inserted the second (note I took the liberty of rearranging your output to make the rows appear in chronological order). So the index hasn't screwed up, exactly; the problem is that both rows appear as good at the same time. But why? It's really highly annoying that we can't see the contents of the infomasks for the rows. Would you be willing to grab a copy of pg_filedump and dump out these two data pages so we can see the complete tuple headers? (If you don't have a compiler then you'd need to find a precompiled copy of pg_filedump for Windows. I don't know if anyone's made one available.) Given that you say the machine has been crashing, my bet is that a crash caused the loss of pg_clog status for xid 1481020 at a time when 2745,50's xmin had been marked committed good, but 2672,11's xmax had not been similarly marked. We have sufficient defenses against this sort of thing *if the disk drive does not lie about write complete*. (Unfortunately the vast majority of el-cheapo PCs are configured to lie with abandon, which means that we can't guarantee data consistency across power failures on such hardware.) It'd be nice to get direct confirmation of that theory though. regards, tom lane __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] Anybody using PostGIS?
I've been meaning to try out PostGIS and see what it is capable of. Is anybody using it? Do you have accompanying URLs? Thanks, CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Oracle buys Innobase
There are some articles on eweek about this: Oracle Finds the Flaw in MySQL's Business Plan http://www.eweek.com/article2/0,1895,1869989,00.asp This is what Oracle says in its release: InnoDB's contractual relationship with MySQL comes up for renewal next year. Oracle fully expects to negotiate an extension of that relationship. This is what Lubet, former Oracle sales mistress, has to say about that: I'm pretty sure, as an ex-Oracle employee, that the sentence in the release about 'We'll certainly be happy to renew the contract,' that it was written by Larry and that he was laughing out loud as he [dictated it]. __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] more than one row returned by a subquery used as an expression
I'm trying to get this query to work: update sectors set companies =(select companies from industries where sector_id =sectors.id); PG returns: ERROR: more than one row returned by a subquery used as an expression Column companies is just a count of rows in the related companies table. Queries like this worked: update industries set companies =(select count(id) from companies where industry_id =industries.id); I know I could do a join in the subselect, but I'm curious why this doesn't work. If I do the subselect by itself, it looks like all of the rows from the industries table are return (I expected an error). Is this the implicit FROM gotcha? CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Duplicate primary keys/rows
--- Michael Fuhr [EMAIL PROTECTED] wrote: On Sun, Oct 09, 2005 at 12:46:51PM -0700, CSN wrote: select * from table1 where id=586; 586|a|b|c|d Do you get different results from the following queries? SET enable_seqscan TO on; SET enable_indexscan TO off; SELECT * FROM table1 WHERE id = 586; This returns 2 rows. SET enable_seqscan TO off; SET enable_indexscan TO on; SELECT * FROM table1 WHERE id = 586; This returns 1 row. Yet: select * from table1 where id=585 and id=587; 585|c|a|e|f 586|a|b|c|d 586|a|b|c|d 587|g|e|r|z What's the output of the following query? RESET enable_seqscan; RESET enable_indexscan; SELECT oid, ctid, xmin, cmin, xmax, cmax, * FROM table1 WHERE id = 585 AND id = 587; oid | ctid| xmin | cmin | xmax | cmax | id +---+-+--+-+--+- 125465 | (3143,78) | 1664385 |0 | 1664386 |2 | 984 125466 | (2745,50) | 1481020 |0 | 1682425 |2 | 985 125466 | (2672,11) | 1445346 |0 | 1481020 |0 | 985 125467 | (3159,28) | 1671875 |0 | 1671876 |2 | 986 (I'm using a different duplicate row - 985. I deleted 586's duplicate.) Is this a problem with the index? Would rebuilding them fix this problem? I'm still curious why this happened, and somewhat troubled that something like this can happen. Thanks for your help, CSN If you get the error 'column oid does not exist' then you've created the table without oids, so just omit oid from the select list: SELECT ctid, xmin, cmin, xmax, cmax, * FROM table1 WHERE id = 585 AND id = 587; Wow, how is this possible? I'm using PG 8.0.3 on Windows XP. This computer has been crashing repeatedly lately, if that could be blamed (bad memory? hard disk? I haven't quite figured out why.) Faulty hardware is one possibile explanation. -- Michael Fuhr __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate primary keys/rows
I don't have a compiler on this machine. If somebody can point me to a copy of pg_filedump for Windows (I didn't see any using Google) I'd be happy to use it. Or perhaps I could compile it under cygwin. The hard drive is a Western Digital 200GB JD (SATA), if that can be used to determine how badly it lies. ;) Thanks, CSN --- Tom Lane [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: oid | ctid| xmin | cmin | xmax | cmax | id +---+-+--+-+--+- 125466 | (2672,11) | 1445346 |0 | 1481020 | 0 | 985 125466 | (2745,50) | 1481020 |0 | 1682425 | 2 | 985 Hmm. The fact that the dup rows have the same OID indicates pretty strongly that they are actually two versions of the same row, and not two independently inserted rows. Furthermore we can see that xact 1481020 deleted the first version and inserted the second (note I took the liberty of rearranging your output to make the rows appear in chronological order). So the index hasn't screwed up, exactly; the problem is that both rows appear as good at the same time. But why? It's really highly annoying that we can't see the contents of the infomasks for the rows. Would you be willing to grab a copy of pg_filedump and dump out these two data pages so we can see the complete tuple headers? (If you don't have a compiler then you'd need to find a precompiled copy of pg_filedump for Windows. I don't know if anyone's made one available.) Given that you say the machine has been crashing, my bet is that a crash caused the loss of pg_clog status for xid 1481020 at a time when 2745,50's xmin had been marked committed good, but 2672,11's xmax had not been similarly marked. We have sufficient defenses against this sort of thing *if the disk drive does not lie about write complete*. (Unfortunately the vast majority of el-cheapo PCs are configured to lie with abandon, which means that we can't guarantee data consistency across power failures on such hardware.) It'd be nice to get direct confirmation of that theory though. regards, tom lane __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Dumb question about serial's upper limit
If integer's range is -2147483648 to +2147483647, why is serial's range only 1 to 2147483647 instead of 1 to about 4294967294? CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(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] Dumb question about serial's upper limit
--- Tom Lane [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: If integer's range is -2147483648 to +2147483647, why is serial's range only 1 to 2147483647 instead of 1 to about 4294967294? How are you going to stuff 4294967294 into an integer field, which as you just stated has an upper limit of 2147483647? If we had an unsigned int type, we could use it for serial and get that result, but we do not. regards, tom lane I was thinking about the types in the C code behind PostgreSQL, rather than types in PG itself. Been a long time since I coded in C but I thought it had unsigned ints and maybe data types could be mapped as so (pardon my ignorance about C/PG's inner workings): PG int = C signed int PG serial = C unsigned int Anyhow, was just something I was curious about. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Duplicate primary keys/rows
This is weird. I set up a table with a serial id field and created a primary key on it. Then I imported data. Running an app against it, I got periodic errors stating duplicate key violates unique constraint pkey_table1. Looking through the table (with phppgadmin), there are duplicate rows: id|f1|f2|f3|f4 585|c|a|e|f 586|a|b|c|d 586|a|b|c|d 587|g|e|r|z However: select * from table1 where id=586; 586|a|b|c|d Yet: select * from table1 where id=585 and id=587; 585|c|a|e|f 586|a|b|c|d 586|a|b|c|d 587|g|e|r|z Wow, how is this possible? I'm using PG 8.0.3 on Windows XP. This computer has been crashing repeatedly lately, if that could be blamed (bad memory? hard disk? I haven't quite figured out why.) Using phppgadmin, I was able to delete one of the duplicate rows (there are several) - don't know how it does that - maybe using OIDs? CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle buys Innobase
Look what somebody suggested! --- If the worst happens and Oracle tries to squash InnoDB, there may already be such an alternative out there. I wonder what it would take to add (and optimize) Postgres storage engine support to MySQL? I don't know exactly how current versions of MySQL and Postgres maesure up performance-wise, but PgSQL seems to have made steady progress on performance improvements. Maybe this is a crazy idea, I don't know how technically or legally feasible it is, but I really like the idea of the two open-source communities uniting to battle Oracle. http://jeremy.zawodny.com/blog/archives/005490.html#comment-21233 __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(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] Oracle buys Innobase
Yep, those were two of my very first questions too. ;) CSN --- Marc G. Fournier [EMAIL PROTECTED] wrote: Stupid question, but what does MySQL bring to the equation? Why not just use PostgreSQL in the first place? On Sun, 9 Oct 2005, CSN wrote: Look what somebody suggested! --- If the worst happens and Oracle tries to squash InnoDB, there may already be such an alternative out there. I wonder what it would take to add (and optimize) Postgres storage engine support to MySQL? I don't know exactly how current versions of MySQL and Postgres maesure up performance-wise, but PgSQL seems to have made steady progress on performance improvements. Maybe this is a crazy idea, I don't know how technically or legally feasible it is, but I really like the idea of the two open-source communities uniting to battle Oracle. http://jeremy.zawodny.com/blog/archives/005490.html#comment-21233 __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(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] PostgreSQL 8.1 vs. MySQL 5.0?
On 10/6/2005 4:37 AM, Tzvetan Tzankov wrote: They have collation and multiple characterset per table and etc. which actually is from 4.1 (not new in 5.0), and postgresql have only one collation per database cluster :-( Otherwise I think their features are all there, but cannot be used togather most of them (you can have foreign key, but not using fulltext ...) AFAIK MySQL's fulltext indexing is only supported on MyIsam tables, so if you want to use it, you lose ACID, hot backup and a couple other nice things entirely for that part of your data. Many MySQL users still believe that the pluggable storage engine design is an advantage ... I think one storage engine that supports the full feature set is better. Jan I agree - MySQL really has a confusing array of different database engines: # MyISAM # MERGE # ISAM # HEAP # InnoDB # BDB or BerkeleyDB Tables # Example # Archive # Federated # CSV # Blackhole # NDB Cluster http://dev.mysql.com/doc/mysql/en/storage-engines.html CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
I'm not sure what XA (distributed transactions) is - is that something that can be achieved with Slony? CSN --- Joshua D. Drake [EMAIL PROTECTED] wrote: On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Well IF they are being completely honest, we don't have XA and we don't have an instance manager but of course who really needs one? Sincerely, Joshua D. Drake Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] PostgreSQL Gotchas
On 10/6/05, Aly S.P Dharshi aly ( dot ) dharshi ( at ) telus ( dot ) net wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? - It's a lot shorter than MySQL's gotchas list. - 8 of the 13 are for versions of PostgreSQL = 8.1 - Of the remaining, I consider select as to be really trivial (and it appears a work-around can be hacked). - lowercase folding. I DO sometimes wish I could use fieldID, etc. without quoting it. - I've never found count(*) to be slow. - I don't know enough about the UNICODE means UTF-8 and RANDOM() failures to comment. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
--- Scott Marlowe [EMAIL PROTECTED] wrote: Federated Storage Engine: Allows MySQL to access tables in other servers like they are here. No real direct equivalent in PostgreSQL, but dblink provides similar functionality. Would that be possible with table partitions? Or Slony? CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
--- Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, 2005-10-05 at 23:41, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Well IF they are being completely honest, we don't have XA and we don't have an instance manager but of course who really needs one? We don't have XA built into the backend, but if I've been following the jdbc list accurately, there's fairly complete XA support for the jdbc driver, which should be available in the 8.1 release. More generally, it's worth making the point that a lot of MySQL's brand new in 5.0 features have been in Postgres for a *long* time, and are therefore likely to be both more stable and better-performing than MySQL's first cut at them. (BTW, it sure seems like MySQL 5.0 has been a heckuva long time in getting to release status. Has anyone here been following that process? Why's it been so painful?) I've been beta testing 5.0.xx releases and reporting bugs. They're pretty fast at fixing individual bugs. Not sure why it's taken so long, really. Maybe they were trying to do too much at once in one release? But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. What's the difference between a fk at the table level vs. column level? The only fk's I've used are one column referencing another. CSN __ Yahoo! for Good Donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
--- CSN [EMAIL PROTECTED] wrote: - 8 of the 13 are for versions of PostgreSQL = 8.1 Doh! - 8 of the 13 are for versions of PostgreSQL 8.1! __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
Yep, I think the SQL spec says fold to uppercase. I'm not sure why PostgreSQL folds to lowercase instead, but if folding has to occur, I prefer lowercase. CSN --- Jim C. Nasby [EMAIL PROTECTED] wrote: On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote: - lowercase folding. I DO sometimes wish I could use fieldID, etc. without quoting it. I believe that may be against ANSI SQL. In any case, the only databases I can think of that don't fold-case in some form are MySQL and Access. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
I had a similar experience speaking to the MySQL folks at (the last) COMDEX. After trying to get them to explain how their licenses work, I was even more confused (and two reps even gave conflicting info). CSN Hi everyone, I've just got back from LinuxWorld in London and seeing this thread thought I would share my experience of the MySQL stand - if you are of a delicate dispostion, please look away now. I basically asked them straight up why I should use MySQL instead of PostgreSQL and was quite surprised by the result, mainly since it was not done on features but more on FUD. The basic message was this: - MySQL is the most popular open source database, with over 6m enterprise installs, with a large company supporting it. PostgreSQL is run by a very small community of developers. - MySQL can be clustered (This was later retracted when I mentioned I needed something that would work on large tables, as apparently their clustering only works in RAM and so will fail on large queries and queries that use a lot of joins). - All the companies that have tried to operate by selling PostgreSQL support services have gone bankrupt, except for EnterpriseDB. - PostgreSQL doesn't have row level locking. And this last comment really took the biscuit - I really hope that the none of the core team read this and decide to throw in the towel: MySQL has the biggest collection of database experts... Open source people don't know how to write databases So all in all, to say I was upset by some of these comments was an understatement. To all the people I spoke to on the PostgreSQL stand, I hope I did it in a way that made them feel empowered to go and try the PostgreSQL for their own applications by mentioning its benefits, and not by spreading FUD about its competition. Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Function keys cause psql to segfault
If I'm in psql (via putty, from WinXP to Redhat) and hit F1-4 (F5+ just display a ~), psql will segmentation fault and exit. Not that I'm in the habit of entering function keys while in psql - I accidentally hit one while entering numbers (lost a fair amount of history). I just tried it using WinXP's command prompt and none of the function keys cause psql to segfault. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Function keys cause psql to segfault
Here's what 'od -c' shows for F1-4: ^[OP^[OQ^[OR^[OS CSN --- Bruce Momjian pgman@candle.pha.pa.us wrote: CSN wrote: If I'm in psql (via putty, from WinXP to Redhat) and hit F1-4 (F5+ just display a ~), psql will segmentation fault and exit. Not that I'm in the habit of entering function keys while in psql - I accidentally hit one while entering numbers (lost a fair amount of history). My guess is that those send a break or some control sequence. od -c might show you what is being output. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Function keys cause psql to segfault
I did 'strace psql dbname' and this was the output after hitting F1: read(0, \33, 1) = 1 read(0, O, 1) = 1 read(0, P, 1) = 1 --- SIGSEGV (Segmentation fault) @ 0 (0) --- +++ killed by SIGSEGV +++ CSN --- Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian pgman@candle.pha.pa.us writes: CSN wrote: If I'm in psql (via putty, from WinXP to Redhat) and hit F1-4 (F5+ just display a ~), psql will segmentation fault and exit. My guess is that those send a break or some control sequence. od -c might show you what is being output. Try watching the psql process with strace in another terminal window. If Bruce's theory is correct (and it sounds good to me) then you should be able to see a signal being delivered to psql. regards, tom lane __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] Function keys cause psql to segfault
Hmm, all I could think of was perl and php - hitting F1-4 just caused these chars to be displayed (the interpreters didn't exit): ^[OP^[OQ^[OR^[OS CSN --- Bruce Momjian pgman@candle.pha.pa.us wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: CSN wrote: If I'm in psql (via putty, from WinXP to Redhat) and hit F1-4 (F5+ just display a ~), psql will segmentation fault and exit. My guess is that those send a break or some control sequence. od -c might show you what is being output. Try watching the psql process with strace in another terminal window. If Bruce's theory is correct (and it sounds good to me) then you should be able to see a signal being delivered to psql. Also, try the function keys in another command-line application and see if that exits too. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function keys cause psql to segfault
It looks like I had readline 4.3 installed. I just installed readline 5.0 - the F1-4 keys still cause psql to segfault. (AFAIK I don't need to recompile postgres for psql to use the newly installed readline). CSN --- Tom Lane [EMAIL PROTECTED] wrote: CSN [EMAIL PROTECTED] writes: I did 'strace psql dbname' and this was the output after hitting F1: read(0, \33, 1) = 1 read(0, O, 1) = 1 read(0, P, 1) = 1 --- SIGSEGV (Segmentation fault) @ 0 (0) --- Hmm ... I don't have an F1 key, but I typed escape-O-P at a psql running under Linux, and got this: Process 28978 attached - interrupt to quit read(0, \33, 1) = 1 read(0, O, 1) = 1 read(0, P, 1) = 1 write(2, \7, 1) = 1 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 read(0, So it seems fine here. I'm wondering if there's something broken about your machine's readline library. regards, tom lane __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Function keys cause psql to segfault
Hmm, in putty (Terminal-Keyboard) I changed the function keys and keypad from ESC[n~ to Linux. Hitting F1-5 in psql outputs ABCDE - no segfaults! Setting it to Xterm R6 also results in function keys 1-4 causing segfaults (there are also options for VT400, VT100+, and SCO - haven't tried those). Thanks, CSN --- Klint Gore [EMAIL PROTECTED] wrote: On Sun, 25 Sep 2005 20:00:03 -0700 (PDT), CSN [EMAIL PROTECTED] wrote: I did 'strace psql dbname' and this was the output after hitting F1: read(0, \33, 1) = 1 read(0, O, 1) = 1 read(0, P, 1) = 1 --- SIGSEGV (Segmentation fault) @ 0 (0) --- +++ killed by SIGSEGV +++ esc O P is PF1 on a VT100/VT200. Does it make any difference if you change the terminal emulation in putty or the term setting in redhat? klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Finding (and deleting) dupes in relation table
I have a table that relates id's of two other tables: table1id, table2id Dupes have found their way into it (create unique index across both fields fails). Is there a quick and easy way to find and delete the dupes (there are tens of thousands of records)? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Finding (and deleting) dupes in relation table
Nevermind, figured it out: select distinct on (table1id, table2id) * into temp from table3; delete from table3; insert into table3 select * from temp; --- CSN [EMAIL PROTECTED] wrote: I have a table that relates id's of two other tables: table1id, table2id Dupes have found their way into it (create unique index across both fields fails). Is there a quick and easy way to find and delete the dupes (there are tens of thousands of records)? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] index row size exceeds btree maximum
This appears related to my previous post: http://archives.postgresql.org/pgsql-general/2005-09/msg00809.php I setup a unique index using the title, yield, and directions fields. Some inserts are causing this error: DBD::Pg::st execute failed: ERROR: index row size 2832 exceeds btree maximum, 2713 CONTEXT: SQL statement insert into stuff (title, yield, directions) values ( $1 , $2 , $3 ) What do I do? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] index row size exceeds btree maximum
--- Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, 2005-09-21 at 15:02, CSN wrote: This appears related to my previous post: http://archives.postgresql.org/pgsql-general/2005-09/msg00809.php I setup a unique index using the title, yield, and directions fields. Some inserts are causing this error: DBD::Pg::st execute failed: ERROR: index row size 2832 exceeds btree maximum, 2713 CONTEXT: SQL statement insert into stuff (title, yield, directions) values ( $1 , $2 , $3 ) What do I do? Don't insert such big values? :) Actually, the standard solution is to use an md5 of the three fields: create unique index threefieldindex on table1 (md5(field1||field2||field3)); Ah, cool! Looks like using tsearch2 would be another option, but I don't plan on searching through the yield or directions fields (except at insert time). http://joseph.randomnetworks.com/archives/2005/08/05/postgresql-index-limitation-index-row-size-x-exceeds-btree-maximum-2713/ CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Preventing duplicate records according to several fields
I have a table like so: id, title, yield, directions and would like to prevent duplicate records from being added (i.e. according to the title, yield, and directions fields). I won't normally be querying on the yield or directions fields, so I just have indexes for id and title. What's the best way to prevent duplicates from being added? - Before inserting, do a 'select id from stuff where title=? and yield=? and directions=?'. This would want the title and directions fields indexed (which seems like a waste of space since they won't be used except for rare inserts). - Create a unique index across the title, yield, and directions fields. - Create a 'hash' field by md5'ing the title, yield, and directions fields, and create a unique index on it. Then when inserting new records, first create a hash and check if it already exists, or have the database automatically handle this (trigger to compute hash field at insert time - unique index will raise an exception). Thanks for any help, insights, suggestions, etc. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] character varying == text?
Just something I was curious about - is there any difference at all between character varying (in the SQL spec) without a length specified and text (not in the SQL spec)? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Utility that creates table schema from csv data?
Probably wishful thinking, but who knows - maybe there's something in contrib! I have a bunch of csv data with the field names specified on the first line of the various files. Is there any such utility that will create a table schema using the field names AND look through the data and determine what data types each field should be? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] Deferred triggers?
CSN wrote: Perhaps another possible feature request! I've looked through the docs and it doesn't appear that it's possible to create deferred triggers - i.e. they don't get called unless the current transaction commits. The semantics of such a thing appear to be indeterminate. What happens if something in the trigger would have caused the original transaction to fail? Most people would expect all changes made by the original transaction, as well as those made by the trigger, to be rolled back. Using deferred triggers as you've defined it would then require chainged transactions, which could get very messy. That doesn't sound too messy - the trigger could either cause the current transaction to abort, or commit. (My understanding is that they currently get called immediately whether or not there is a transaction in progress.) There is always a transaction in progress. I meant when you explicitly enclose multiple statments in a single transaction. -- Guy Rouillier Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Deferred triggers?
Perhaps another possible feature request! I've looked through the docs and it doesn't appear that it's possible to create deferred triggers - i.e. they don't get called unless the current transaction commits. (My understanding is that they currently get called immediately whether or not there is a transaction in progress.) CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] About dropped notifications
The docs state: NOTIFY behaves like Unix signals in one important respect: if the same notification name is signaled multiple times in quick succession, recipients may get only one notification event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifications received. Instead, use NOTIFY to wake up applications that need to pay attention to something, and use a database object (such as a sequence) to keep track of what happened or how many times it happened. I'm considering setting up a script that listens for notifications for a table and if a row is deleted the script will delete that row's corresponding files. If there are thousands of rows in the table, and I do delete from table, or even delete from table where id 1000 and id2000, will the script be notified of the deletion of each and every row (and subsequently be able to delete that row's files), or will only one notify event be received (or some number less than the actual number of rows deleted)? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] POSS. FEATURE REQ: Dynamic Views
For lack of a better term, but I was curious if there is/was any thought about making PG's views automatically see changes in underlying tables, as opposed to currently having to drop/create all corresponding views if a table's structure (add/delete fields, etc.) is changed. CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(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] history is not supported by this installation
Hi, IIRC psql's \s used to work, but I upgraded to 8.x on Windows - which recommends that cygwin/bin be removed from PATH - and I get this error: history is not supported by this installation Reading the docs, it states GNU readline is required. I presume that that was in cygwin/path - what's the remedy to get \s to work? BTW, is there a way to set the number of commands returned by \s? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] selecting rows older than X, ensuring index is used
Hi,
I want to select records that haven't had an error
(logged to last_error) in the last 24 hours. My query
is:
select * from table1
where last_error is null
or extract(epoch from now()-last_error) 86400;
I've created an index on last_error (timestamp with
time zone - can be NULL), then used EXPLAIN:
Seq Scan on table1 (cost=0.00..20.86 rows=217
width=72)
Filter: ((last_error IS NULL) OR
(date_part('epoch'::text, (now() - last_error))
86400::double precision))
There are over 550 rows in table1, so it doesn't look
the index is being used. Is there a way to rewrite
this query so the index is used?
Thanks,
CSN
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Suggestion for Date/Time Functions Section
Hi, I suggestion for the date/time functions in the docs: http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html I was trying to figure out how to do: update table set next=now() + interval 'table.period seconds'; I tried subqueries, the concat operator, and anything else I could think of until I rediscovered the page on the various ways to cast. Perhaps mix up the examples in the docs so other ways to do date arithmetic (and that allow expressions, fields, etc.) are obvious: date '2001-09-28' + cast((7+7)||' seconds' as interval); date '2001-09-28' + ((7+7)||' seconds')::interval); etc. Or am I missing an easier way to do date arithmetic using a table's fields as part of the equation? (I think mysql has date_add(...), date_subtract(...), etc. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Apostrophe doesn't show up in command line
--- Richard Huxton dev@archonet.com wrote: CSN wrote: In a field I have text like in today's news... When I select that field in psql using putty (Latin-1), then apostrophe doesn't show up (shows up as todays), but it does show up in phppgadmin (and other php programs). Is this an issue with psql, or putty (or something else)? It's an issue with your character-set settings somwhere along the line. This sort of thing can be a real pain - you'll need to check every component involved. Start at the Windows/putty end, and check what character set phppgadmin is using (HINT: is it utf-8?) Hmm, how can you tell? I don't see character set specified anywhere in phppgadmin (including conf.inc.php). CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] converting curly apostrophes to standard apostrophes
Is there a way to replace all curly apostrophes with standard apostrophes (presumably with replace(x,y,z))? My database is SQL_ASCII and I can't find a character code for curly apostrophes in ASCII here: http://www.lookuptables.com, but nevertheless there appear to be curly apostrophes in the database. Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Apostrophe doesn't show up in command line
Ah, it's Western ISO-8859-1. Putty has the same setting. I tried changing putty's charset to UTF-8 and now curly apostrophes are displayed as a grey box in psql's output (e.g. in today[box]s news...). Thanks, CSN --- Richard Huxton dev@archonet.com wrote: CSN wrote: and check what character set phppgadmin is using (HINT: is it utf-8?) Hmm, how can you tell? I don't see character set specified anywhere in phppgadmin (including conf.inc.php). View Character Encoding in firefox while you have a page open View Encoding in IE -- Richard Huxton Archonet Ltd Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] converting curly apostrophes to standard apostrophes
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Mon, Aug 15, 2005 at 12:14:16PM -0700, CSN wrote:
Is there a way to replace all curly apostrophes
with
standard apostrophes (presumably with
replace(x,y,z))?
My database is SQL_ASCII and I can't find a
character
code for curly apostrophes in ASCII here:
http://www.lookuptables.com, but nevertheless
there
appear to be curly apostrophes in the database.
The Extended ASCII Codes section of that page
might not match
what your system uses (it doesn't match mine). Have
you tried using
the ascii() function on the offending data?
I logged back in after changing putty's charset to
UTF-8 and am now able to paste an (curly apostrophe)
into psql, however that character still appears as a
grey box in psql's select output. I've tried various
queries to find which rows and fields contain it,
without success (I know it's in some of them):
db=select ascii('');
ascii
---
226
db=select id from news where body ilike '%%';
(0 rows)
db=select id from news where body ilike '%' ||
chr(226) || '%';
db'
db'^C
db=
CSN
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] converting curly apostrophes to standard apostrophes
--- Michael Fuhr [EMAIL PROTECTED] wrote:
On Mon, Aug 15, 2005 at 01:48:00PM -0700, CSN wrote:
db=select ascii('');
ascii
---
226
db=select id from news where body ilike '%%';
(0 rows)
db=select id from news where body ilike '%' ||
chr(226) || '%';
db'
db'^C
db=
What's going on with the last query? The prompt
change suggests
that psql is confused with quoting, and the ^C looks
like you hit
Control-C to get the regular prompt back. Did you
ever run this
query? If it produced no rows then you could widen
the search.
Hmm, I'm on another computer and I just tried that
last query and it worked without psql thinking it
needed another single quote. Appears the chr code is
146 not 226 (turns out chr(226) is â - why that
doesn't cause problems with iso-8859-1/utf-8 xml and
the single/double quotes and dashes do I don't know).
Anyhow, I ended up doing this:
update news set body=replace(body,chr(146),); --
left single quote
update news set body=replace(body,chr(145),); --
right single quote
update news set body=replace(body,chr(147),''); --
left double quote
update news set body=replace(body,chr(148),''); --
right double quote
update news set body=replace(body,chr(150),'-'); -- en
dash
update news set body=replace(body,chr(151),'-'); -- em
dash
and that seems to do the trick. Most places I found
online listed different chars for these codes, but
http://www.webopedia.com/quick_ref/asciicode.asp lists
them. Jeez, I'm so confused with encodings, charsets,
etc. now. :(
Thanks,
CSN
Example:
SELECT id FROM news WHERE body ~ '[\200-\377]';
You could use the string from pattern variant of
substring() to
extract characters in a specific range. If you have
PL/Perl then
it would be trivial to extract all of and only the
special characters
along with their ASCII codes:
CREATE FUNCTION special_chars(text) RETURNS text AS
'
return join( , map {$_: . ord($_)} $_[0] =~
/[\200-\377]/g);
' LANGUAGE plperl IMMUTABLE STRICT;
SELECT id, special_chars(body) FROM news WHERE body
~ '[\200-\377]';
--
Michael Fuhr
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(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] Apostrophe doesn't show up in command line
In a field I have text like in today's news... When I select that field in psql using putty (Latin-1), then apostrophe doesn't show up (shows up as todays), but it does show up in phppgadmin (and other php programs). Is this an issue with psql, or putty (or something else)? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Removing -'s (header) before records in psql
Is it possible to get rid of the header of -'s when selecting rows in psql? For fields with a lot of text, it looks like: select body from news where id=123; -[ RECORD 1 ]- -- -- - body | Additional details ... Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Removing tsearch2 from a database
Greetings- How can I remove tsearch2 (all its tables, types, functions, etc.) from a database? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ERROR: plphp: unable to register function plphp_proc_4947785_trigger
I'm using plphp to create a trigger. I don't see any syntax errors in it (I've checked it with php -l (lint)). When I update a row in the table with the trigger, I get this error: ERROR: plphp: unable to register function plphp_proc_4947785_trigger I've verified that plphp (and plphpu) is installed with 'createlang -l'. Any idea what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
I'm getting this warning in pgsql's log: LOG: plphp: PHP Warning: Call-time pass-by-reference has been deprecated - argument passed by value; If you would like to pass it by reference, modify the declaration of [runtime function name](). If you would like to enable call-time pass-by-reference, you can set allow_call_time_pass_reference to true in your INI file. However, future versions may not support this any longer. in plphp trigger call on line 1 Is there anything I can do about it? I'd email plphp's list, but their mailing list links are 404. Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] ERROR: plphp: unable to register function plphp_proc_4947785_trigger
Nevermind, I found some stray single quotes (in an array var) in a double-quoted string that appears to have been the problem. --- CSN [EMAIL PROTECTED] wrote: I'm using plphp to create a trigger. I don't see any syntax errors in it (I've checked it with php -l (lint)). When I update a row in the table with the trigger, I get this error: ERROR: plphp: unable to register function plphp_proc_4947785_trigger I've verified that plphp (and plphpu) is installed with 'createlang -l'. Any idea what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] plphp crashing server
Uh oh, I think plphp is crashing the server. When I update a row in the table with the trigger, this happens: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. And this is what shows up in the log: LOG: server process (PID 31665) was terminated by signal 11 LOG: terminating any other active server processes Wow. I still don't see anything wrong with the plphp function - how could it cause the entire server to crash? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plphp crashing server
Jeez, nevermind again! Turns out this was the problem in the plphp script: $sql=select * from table where id=123; $result=spi_exec_query($sqll); I'm still curious about the Call-time pass-by-reference has been deprecated warning if anybody knows. Thanks, CSN --- CSN [EMAIL PROTECTED] wrote: Uh oh, I think plphp is crashing the server. When I update a row in the table with the trigger, this happens: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. And this is what shows up in the log: LOG: server process (PID 31665) was terminated by signal 11 LOG: terminating any other active server processes Wow. I still don't see anything wrong with the plphp function - how could it cause the entire server to crash? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
I'm using PHP5, and I'm not passing by reference. My
first stop WAS plphp.commandprompt.com, but none of
their mailing list links for plphp work.
CSN
# jd ( at ) commandprompt ( dot ) com / 2005-08-11
15:45:18 -0700:
Roman Neuhauser wrote:
# cool_screen_name90001 ( at ) yahoo ( dot ) com
/ 2005-08-11 13:23:52 -0700:
I'm getting this warning in pgsql's log:
LOG: plphp: PHP Warning: Call-time
pass-by-reference
has been deprecated - argument passed by value;
If
you would like to pass it by reference, modify
the
declaration of [runtime function name](). If
you
would like to enable call-time
pass-by-reference, you
can set allow_call_time_pass_reference to true
in your
INI file. However, future versions may not
support
this any longer. in plphp trigger call on line
1
Is there anything I can do about it?
Yes.
Actually the below is incorrect. He should be
visiting
plphp.commandprompt.com and signing up for the
list there.
Why? What does the generic warning emitted by
PHP 4 (no need to get
PostgreSQL into the mix) since forever on code
like this:
function foo($arg) {} /* foo is declared to take
$arg by value */
foo($var); /* $var is passed by reference */
have to do with PL/PHP?
It's completely off topic here, however.
You'll find more help in
the PHP manual and/or php-general ( at )
lists ( dot ) php ( dot ) net (you can subscribe
from http://www.php.net/).
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
Sure-
CREATE or REPLACE FUNCTION email_activated_member ()
RETURNS trigger AS $$
$new=$_TD['new'];
$old=$_TD['old'];
if(($_TD['event']=='INSERT' and $new['active']='t') or
($_TD['event']=='UPDATE' and $new['active']=='t' and
$old['active']=='f')) {
$link=$_TD['new'];
$sql=select *
from groups
where id=$link[group_id];
$result=spi_exec_query($sql);
if($result) {
$group=spi_fetch_row($result);
}
if($group) {
$message=EOT
Greetings...
Here are your link details:
Name: $link[name]
URL: $link[url]
Email: $link[email]
Contact Name: $link[contact_name]
Description: $link[description]
Thanks,
$group[name]
$group[url]
EOT;
if(!empty($link['email'])) {
$to=empty($link['contact_name']) ?
$link['email'] : $link[contact_name] $link[email];
mail($to,
Link Activated - $group[name]!,
$message,
From: {$group[name]} $group[email]\r\n);
}
}
}
$$ LANGUAGE 'plphpu';
-- CREATE TRIGGER email_activated_member AFTER INSERT
or UPDATE ON links FOR EACH ROW EXECUTE PROCEDURE
email_activated_member();
It justs lets people know when their link has been
activated.
CSN
--- Roman Neuhauser [EMAIL PROTECTED] wrote:
# [EMAIL PROTECTED] / 2005-08-11
16:49:25 -0700:
I'm using PHP5, and I'm not passing by reference.
My
first stop WAS plphp.commandprompt.com, but none
of
their mailing list links for plphp work.
Can you post the code that triggers the warning?
--
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
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
--- Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2005-08-11 17:36:49 -0700: --- Roman Neuhauser [EMAIL PROTECTED] wrote: Can you post the code that triggers the warning? Sure- CREATE or REPLACE FUNCTION email_activated_member () RETURNS trigger AS $$ $new=$_TD['new']; $old=$_TD['old']; if(($_TD['event']=='INSERT' and $new['active']='t') or You are assigning to $new['active'] instead of the probably wanted comparison. I don't see any byref arguments, and don't know how to help further. Doh! I fixed it but I'm still getting the same warnings in the log. I'd guess maybe it's something plphp is doing on its own with references, but I should probably play around with some more plphp functions and see if they generate the same warnings. Anyhow, thanks for the help. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] NOTIFY/LISTEN, PHP, rule vs. trigger, blocking, missed NOTIFY's
Scott and I were discussing NOTIFY/LISTEN using a PHP
script here:
http://phpbuilder.com/board/showthread.php?t=10302693
Basically:
PHP Code:
#!/usr/bin/php -q
?php
$conn = pg_connect(dbname=test user=user);
pg_query(listen record_deleted);
$interval = 10;
for (;1;){
sleep($interval);
$notify = pg_get_notify($conn);
if ($notify){
print Now we do something;
}
}
?
And the sql code:
CREATE TABLE ntest ( id serial primary key, path text
);
create table naudit ( id int primary key, path text );
create rule audit_test as on delete to ntest do (
insert into naudit(id,path) values (OLD.id, OLD.path);
notify record_deleted );
insert into ntest (path) values
('/usr/local/lib/php.ini2');
delete from ntest;
I think he may be off on some wild and exotic vacation
;) or something - so I'll post my questions here too:
* Is there any reason to use a rule rather than a
trigger? I guess a rule is just simpler.
* Also, think there's any way to just have the PHP
script block until a notify event is actually
received, rather than checking every [sleep] seconds?
* Finally, PG's docs on notify say that if events
happen in rapid succession, notify's might get
dropped. For example: could many item rows get
deleted, but some of their corresponding files not get
deleted due to dropped notify's?
Thanks,
CSN
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Getting actual number of rows updated
Is it possible to have PG report the actual number of rows that actually CHANGED in an update command? e.g. UPDATE items set name=replace(name,'abc','def'); UPDATE 9000 -- Actually only 3 were changed rather than update reporting all rows have been updated? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(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] untrusted languages and non-global superusers?
--- Tino Wildenhain [EMAIL PROTECTED] wrote: (The function uses mail(), so IIRC that necessitates using plphpu). Sending mail from a database function (or doing anything else that involves external side-effects) is generally A Bad Idea, for reasons that have been covered many times in the list archives. Why, exactly? In this situation I just set up a trigger that sends a welcome email to newly inserted members. Very convenient. Why cant your application handle this? Otoh, why dont you provide a function to send mail, which takes some parameters and just let your users use them? No need for everybody to write her own mail function. Convenience! I want the email sent whether the member is added via the web interface, directly in the database, from the command line, etc. I don't see any downside. It's only one user that'll be using this function. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] untrusted languages and non-global superusers?
--- Tino Wildenhain [EMAIL PROTECTED] wrote: Convenience! I want the email sent whether the member is added via the web interface, directly in the database, from the command line, etc. I don't see any Well, I also do such things with a small script which basically LISTENs to notify from database, spools the mails and go sleep again. Could you elaborate how you do this? IIRC, there's an example in the docs using C, but I'd prefer using a scripting language. Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] pg_dump - dump specific functions and other items?
Is it possible to dump specific function definitions using pg_dump? Any other items that can be specifically dumped, besides just tables? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Internal catalogs error in log file
I saw this in my log file: ERROR: invalid regular expression: quantifier operand invalid STATEMENT: SELECT n.nspname as Schema, p.proname as Name, CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) as Result data type, pg_catalog.oidvectortypes(p.proargtypes) as Argument data types FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proname ~ '^+$' ORDER BY 1, 2, 3, 4; It's greek to me ;). Any idea what caused this error, and if there's some sort of problem? Thanks, CSN PG 8.0.2 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
