[GENERAL] plpgsql function with update and seeing changed data from outside during run
Hi, I have a plpgsql function where I read data from a table in a loop and update data in a different table. Is it possible to see the updated data from a different access during the run of this function? Or is this impossible because the function is a transaction and no data change is visible outside until the function is finished? I can see the changed data inside the function. the function looks something like this create or replace function insert_log(i_log_id INT, i_queue_id INT) returns varchar as $$ declare [... here are record, etc declarations] begin for myrec in select * from queue where queue_id = i_queue_id; loop insert into log_sub () values (); update log set rows = [internal row count] where log_id = i_log_id; end loop end; $$ language plpgsql; -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
I can try this, but I have never done anything with plperl yet. 2011/6/9 pasman pasmański pasma...@gmail.com: If you rewrite your function in plperlu , you can store data in shared memory. 2011/6/9, Clemens Schwaighofer clemens.schwaigho...@e-graphics.com: Hi, I have a plpgsql function where I read data from a table in a loop and update data in a different table. Is it possible to see the updated data from a different access during the run of this function? Or is this impossible because the function is a transaction and no data change is visible outside until the function is finished? I can see the changed data inside the function. the function looks something like this create or replace function insert_log(i_log_id INT, i_queue_id INT) returns varchar as $$ declare [... here are record, etc declarations] begin for myrec in select * from queue where queue_id = i_queue_id; loop insert into log_sub () values (); update log set rows = [internal row count] where log_id = i_log_id; end loop end; $$ language plpgsql; -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
2011/6/9 Craig Ringer cr...@postnewspapers.com.au: On 9/06/2011 2:41 PM, Clemens Schwaighofer wrote: Hi, I have a plpgsql function where I read data from a table in a loop and update data in a different table. Is it possible to see the updated data from a different access during the run of this function? Or is this impossible because the function is a transaction and no data change is visible outside until the function is finished? I can see the changed data inside the function. (as far as I know) It's not possible for a function to see data committed by other transactions since that function began executing, whether or not those other transactions have committed. A function *can* see changes it or functions it has called have made within its own transaction. The reason for this is that PL/PgSQL functions, whether they are in READ_COMMITTED or SERIALIZABLE mode, do not get an updated snapshot at any point while they are running. The database system takes a snapshot of the state of the database when the function starts running, and that's all the function can see until it's finished. A *transaction* can see data that has been committed by other transactions since it started if it is in READ_COMMITTED mode. Each individual statement run in the transaction cannot; it gets a snapshot when the statement starts and keeps it until the statement ends. PL/PgSQL functions can only be called from SQL statements, and are subject to that rule. If you want to see updates made since your function started, you'll need to either use dblink to have the function control a second connection to the database and do all the work via that, or you'll need to keep your function outside the database in a program that connects to PostgreSQL. What is the goal of this function? I don't see the point of it as written, but perhaps it's been simplified to the point where it's no longer meaingful or useful. The sample is just over simplified. What I do is I read data from one table and copy it into a log table. As the data set can be quite big (about 200.000 rows) I wanted to add a process progress that can be viewed in the web interface. So there is one script that runs this function and then a web interface where I wanted to have the progress. create or replace function insert_log(i_log_id INT, i_queue_id INT) returns varchar as $$ declare [... here are record, etc declarations] begin for myrec in select * from queue where queue_id = i_queue_id; loop insert into log_sub () values (); update log set rows = [internal row count] where log_id = i_log_id; end loop end; $$ language plpgsql; -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function with update and seeing changed data from outside during run
2011/6/9 Tom Lane t...@sss.pgh.pa.us: Merlin Moncure mmonc...@gmail.com writes: On Thu, Jun 9, 2011 at 4:46 AM, Craig Ringer cr...@postnewspapers.com.au wrote: (as far as I know) It's not possible for a function to see data committed by other transactions since that function began executing, whether or not those other transactions have committed. This is not correct. Yes, a snapshot is created, but that doesn't prevent you from seeing external changes. I have in fact many times relied on being able to block in a pl/pgsql loop and wait for a record to be set or something like that. Note that the function needs to be declared VOLATILE for that to work. But I believe the OP's question was the other way around: he wanted to be able to see changes made by a function from elsewhere, before the function completes. That's not possible, unless you resort to hacks like using dblink to get the effect of autonomous subtransactions. Thanks, seems my only way would be to try dblink then. -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DBD::PG and long running queries and tcp stack timeout
Hi, I have a script that runs a query on a remote server. The query will take quite some time to finish. Now the problem is that the tcp stack will timeout before the query is finished. I am running the query as async and have a loop where I query the pg_ready status every 5 seconds. Is there anyway to send some NOOP or anything so this connection does not timeout? Just reading pg_ready seems to do nothing, probably because it gets filled once the query is done. Running the query as not async has the same issues with timeout. -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DBD::PG and long running queries and tcp stack timeout
Hi, I have a script that runs a query on a remote server. The query will take quite some time to finish. Now the problem is that the tcp stack will timeout before the query is finished. I am running the query as async and have a loop where I query the pg_ready status every 5 seconds. Is there anyway to send some NOOP or anything so this connection does not timeout? Just reading pg_ready seems to do nothing, probably because it gets filled once the query is done. Running the query as not async has the same issues with timeout. -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DBD::PG and long running queries and tcp stack timeout
yeah there is a NAT firewall inbetween. I can check there too. But interesting thing is, if I set the tcp_keepalive_time higher it won't time out. But still ... a bit strange. 2011/6/2 John R Pierce pie...@hogranch.com: On 06/01/11 11:35 PM, Clemens Schwaighofer wrote: Hi, I have a script that runs a query on a remote server. The query will take quite some time to finish. Now the problem is that the tcp stack will timeout before the query is finished. is there a NAT firewall or something else in the middle thats doing connection tracking? tcp shouldn't time out like that even if your query is taking multiple hours. -- john r pierce N 37, W 123 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to show the current schema or search path in the psql PROMP
Right now I added two simple wrappers in my .psqlrc \set shsh 'SHOW search_path;' \set setsh 'SET search_path TO' So I can at least set and check the schema more quickly. But seeing it in the PROMPT would be th best. On Sat, Jun 12, 2010 at 05:26, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jun 11, 2010 at 2:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Scott Marlowe scott.marl...@gmail.com writes: But that runs a shell command, how's that supposed to get the search_path? I've been trying to think up a solution to that and can't come up with one. Yeah, and you do *not* want the prompt mechanism trying to send SQL commands... Would a more generic way to access pgsql settings in a \set prompt be useful? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What Linux edition we should chose?
Hi, I run debian/testing since years and it is the best in my opinion. Besides the fact that new versions come in quite fast (after the wait phase from unstable to testing) the upgrade for major versions (eg 8.3 to 8.4) is very simple as it does not override the old files but does a parallel install. This is something I do miss from the RPM versions. Because if you do not dump the data before you upgrade, you are quit screwed. On Mon, May 31, 2010 at 17:29, Michal Szymanski dy...@poczta.onet.pl wrote: Hi, Currently we use Debian, but it chosen by our OS admnistrator. Now we can change our OS and it is question what Linux edition will be the best. We would like have access to new versions of Postgres as soon as possible, for Debian sometimes we had to wait many weeks for official packages. Regards Michal Szymanski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with partition tables and schemas
Hi, I have a problem with partition tables and schemas Postgres: 8.4.2 on redhat and debian I have three schemas public (the default one), live and test live and test are identical copies in table layout, just the tables are created for each one sperated. in those two schemas I have two tables that do logging for me CREATE TABLE session ( session_id SERIAL, session_string VARCHAR, previous_session_string VARCHAR, identified_agent VARCHAR, session_updated TIMESTAMP WITHOUT TIME ZONE, session_created TIMESTAMP WITHOUT TIME ZONE, PRIMARY KEY (session_id) ) INHERITS (public.generic) WITHOUT OIDS; CREATE TABLE visit ( visit_id SERIAL, session_id INT NOT NULL, path VARCHAR, menu_code VARCHAR, page_code VARCHAR, idkey VARCHAR, referer VARCHAR, redirect_url VARCHAR, date_visited TIMESTAMP WITHOUT TIME ZONE, PRIMARY KEY (visit_id), FOREIGN KEY (idkey) REFERENCES page (idkey) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (session_id) REFERENCES session (session_id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE ) INHERITS (public.generic) WITHOUT OIDS; and then I create tables for each month CREATE TABLE session_201002 ( CHECK ( date_created = DATE '2010-02-01' AND date_created DATE '2010-03-01' ) ) INHERITS (session); CREATE TABLE visit_201002 ( CHECK ( date_created = DATE '2010-02-01' AND date_created DATE '2010-03-01' ) ) INHERITS (visit); I add the primary key ALTER TABLE session_201002 ADD PRIMARY KEY (session_id); ALTER TABLE visit_201002 ADD PRIMARY KEY (visit_id); and I add several indexes (not shown here) and then FK constraints ALTER TABLE visit_201002 ADD CONSTRAINT visit_201002_session_id_fkey FOREIGN KEY (session_id) REFERENCES session (session_id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE visit_201002 ADD CONSTRAINT visit_201002_idkey_fkey FOREIGN KEY (idkey) REFERENCES page (idkey) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE; and my triggers for some internal date setting CREATE TRIGGER trg_session_201002 BEFORE INSERT OR UPDATE ON session_201002 FOR EACH ROW EXECUTE PROCEDURE public.set_generic(); CREATE TRIGGER trg_visit_201002 BEFORE INSERT OR UPDATE ON visit_201002 FOR EACH ROW EXECUTE PROCEDURE public.set_generic(); Finally I add the main triggers for the partition: -- session CREATE OR REPLACE FUNCTION session_insert_trigger () RETURNS TRIGGER AS $$ BEGIN IF ( NEW.date_created = DATE '2010-02-01' AND NEW.date_created DATE '2010-03-01') THEN INSERT INTO session_201002 VALUES (NEW.*); ELSE INSERT INTO session_overflow VALUES (NEW.*); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- and attach to session table CREATE TRIGGER trg_session_insert BEFORE INSERT OR UPDATE ON session FOR EACH ROW EXECUTE PROCEDURE session_insert_trigger(); -- visit CREATE OR REPLACE FUNCTION visit_insert_trigger () RETURNS TRIGGER AS $$ BEGIN IF ( NEW.date_created = DATE '2010-02-01' AND NEW.date_created DATE '2010-03-01') THEN INSERT INTO visit_201002 VALUES (NEW.*); ELSE INSERT INTO visit_overflow VALUES (NEW.*); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- and attach to visit table CREATE TRIGGER trg_visit_insert BEFORE INSERT OR UPDATE ON visit FOR EACH ROW EXECUTE PROCEDURE visit_insert_trigger(); Everything is done for each schema separately when I am in the schema itself (via SET search_path TO test/live) my problem is, when I insert data into the visit table it tries to find the session data in the live schema. I have no idea why, because no schema was copied or inherited from the other side. Is there any explanation for this? Creating FK on the main (dummy) tables makes no sense, because there is no data stored in them anyway. I tried to create everything and every command where each table or function is prefixed with the schema name, but with the same result. Anyone can give me some tips what I am doing wrong? -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with partition tables and schemas
I already tried that. even with prefixing the alter table statement with the schema it does not work. I suspect that has something to do that when I try to connect eg visit_201002 with the session table, that there is not data in the session table itself, but in its subtable, I can connect visit_201002 to session_201002 very fine, but that just not what I would like to have. On Wed, Feb 3, 2010 at 00:55, Tom Lane t...@sss.pgh.pa.us wrote: Clemens Schwaighofer clemens_schwaigho...@e-gra.co.jp writes: my problem is, when I insert data into the visit table it tries to find the session data in the live schema. I have no idea why, because no schema was copied or inherited from the other side. I think you need to schema-qualify the table names used in the triggers. As-is they'll be sensitive to whatever your search_path is. regards, tom lane -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On Thu, Aug 20, 2009 at 21:52, Jasen Bettsja...@xnet.co.nz wrote:
On 2009-08-19, Clemens Schwaighofer clemens_schwaigho...@e-gra.co.jp wrote:
On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartzmer...@stonehenge.com
wrote:
Andre == Andre Lopes lopes80an...@gmail.com writes:
Andre I'm developing a function with some checks, for example... to check
if the
Andre e-mail is valid or not.
How are you hoping to do this? The regex to validate an email
address syntactically is pretty large:
http://ex-parrot.com/~pdw/Mail-RFC822-Address.html
And no, I'm not kidding. If your regex is smaller than that, you aren't
validating email... you're validating something kinda like email.
Just in my opinion, this regex is completely too large. For basic
validating something like:
^[A-Za-z0-9!#$%'*+-\/=?^_`{|}~][A-Za-z0-9!#$%'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
works very well
not good: eg:
fails this valid address* : ad...@xxx.museum
yes it does, but all I need to change is {2,4}, to {2,6} or {2,}
accepts this invalid one : y...@gmail..com
and not it does not. I just tested it here.
The regex helps to avoid stuff like this:
f...@bar.com
foo@@bar.com
f...@.bar.com
f...@bar
etc
musedoma replaced with several x to protect the innocent from spam
in some contexts email adrresses with no domain part are valid
addresses with [bracketed] mx servers instead of a domain and/or bang
paths are also allowed (but not in common use and often not desirable)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp
This e-mail is intended only for the named person or entity to which it is
addressed and contains valuable business information that is privileged,
confidential and/or otherwise protected from disclosure. If you received this
e-mail in error, any review, use, dissemination, distribution or copying of
this e-mail is strictly prohibited. Please notify us immediately of the error
via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your
system, retaining no copies in any media.We appreciate your cooperation.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartzmer...@stonehenge.com wrote:
Andre == Andre Lopes lopes80an...@gmail.com writes:
Andre I'm developing a function with some checks, for example... to check if
the
Andre e-mail is valid or not.
How are you hoping to do this? The regex to validate an email
address syntactically is pretty large:
http://ex-parrot.com/~pdw/Mail-RFC822-Address.html
And no, I'm not kidding. If your regex is smaller than that, you aren't
validating email... you're validating something kinda like email.
Just in my opinion, this regex is completely too large. For basic
validating something like:
^[A-Za-z0-9!#$%'*+-\/=?^_`{|}~][A-Za-z0-9!#$%'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
works very well
For example, fredbar...@stonehenge.com is a valid email address. (Go
ahead, try it... it has an autoresponder.)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp
This e-mail is intended only for the named person or entity to which it is
addressed and contains valuable business information that is privileged,
confidential and/or otherwise protected from disclosure. If you received this
e-mail in error, any review, use, dissemination, distribution or copying of
this e-mail is strictly prohibited. Please notify us immediately of the error
via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your
system, retaining no copies in any media.We appreciate your cooperation.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On 08/19/2009 11:41 PM, Randal L. Schwartz wrote:
Clemens == Clemens Schwaighofer clemens_schwaigho...@e-gra.co.jp
writes:
Clemens Just in my opinion, this regex is completely too large. For basic
Clemens validating something like:
Clemens
^[A-Za-z0-9!#$%'*+-\/=?^_`{|}~][A-Za-z0-9!#$%'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
Clemens works very well
Fails on .mobile TLD. Has a pointless {1} in it, which does
absolutely nothing, providing that the creator of the regex was already
missing a few clues.
That's the problem with these kinds of regex... you test it on what
you know, but you're not consulting the *actual* *internet* specifications
(which have been readily available since the dawn of Internet time).
Either use the regex I pointed to already, or stay with the simpler:
/\...@.*\s/
which will at least not deny anyone with a *perfectly legitimate* email
address from making it into your system.
Or, use your regex *only* in an *advice* category, with the ability
for the user to say yes, I'm really sure this is my address.
Please, for the sake of the net, do the Right Thing here. This is
what I'm arguing for. Anything less than that, and your code deserves
to end up in thedailywtf.com as an example of what *not* to do.
I am not going to defend any regex here, but in my opinion it helps on
what I want to see in email addresses.
Yes it fails on mobile, but I have not yet seen one. Probably the best
thing is to test nothing at all. Just accept it ...
--
[ Clemens Schwaighofer -=:~ ]
[ IT Engineer/Web Producer/Planning ]
[ E-Graphics Communications SP Digital ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ]
[ http://www.e-gra.co.jp ]
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Strange Grant behavior in postgres 8.3
On 02/18/2009 01:15 PM, John R Pierce wrote: Schwaighofer Clemens wrote: So what do I do wrong? Even if I do the GRANT command as user 'foo' who is the database owner, I still cannot select with the user 'bar'. It only works if I set GRANT rights for the TABLE itself: as user 'foo' logged in = grant all on table test to bar; that is correct. DATABASE privileges relate to connecting to the database, permissions to create objects and so forth. each object in the database has its own access rights. for typical application use, I create teh database so the primary application account owns the database, then let that account create all the tables so it owns those too. $ sudo -u postgres createuser someuser $ sudo -u postgres createdb -o someuser somedb then access this database with that user to create the tables and such I see, normally I always create a user that owns the DB, so I don't have those problems. But yesterday I run in some issues with table ownership and thought if I just give the user all rights for the DB, he should have all rights to the tables too. The other problem is, that there is no grant all on table db.* ... but I have to do that for each table seperate, or in a grant all on table a, b, I am not sure if there is an easier way, except perhaps through a select from the pg_ catalog for this db and get the table names there ... -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Query Question
On 02/11/2009 01:10 AM, Ioana Danes wrote: Try working with this: SELECT m.key AS mailings_key, m.name AS mailings_name, COALESCE(u.key,'') AS userdata_key, COALESCE(u.uid,'') AS userdata_uid, COALESCE(u.name,'') AS userdata_name FROM (SELECT m0.key, m0.name, u0.uid FROM mailings m0, (SELECT DISTINCT uid FROM userdata) AS u0 ORDER BY u0.uid, m0.key) AS m LEFT OUTER JOIN userdata u ON u.key = m.key AND u.uid = m.uid ORDER BY m.uid, m.key Great, this one works too! Cheers, Ioana --- On Tue, 2/10/09, Schwaighofer Clemens clemens.schwaigho...@tequila.jp wrote: From: Schwaighofer Clemens clemens.schwaigho...@tequila.jp Subject: [GENERAL] Query Question To: pgsql-general@postgresql.org Received: Tuesday, February 10, 2009, 5:30 AM I have two tables Table public.mailings Column | Type| Modifiers +---+--- key| character varying | name | character varying | Table public.userdata Column | Type| Modifiers +---+--- key| character varying | uid| character varying | name | character varying | which hold the following data mailing: key | name -+ A1 | Test 1 A2 | Test 2 A3 | Test 3 A4 | Test 4 userdata: key | uid | name -+-+ A1 | B1 | Test 1 A3 | B1 | Test 3 A2 | B2 | Test 2 A3 | B2 | Test 3 A4 | B2 | Test 4 A2 | B2 | Test 2 A1 | B3 | Test 1 A4 | B3 | Test 4 A1 | B4 | Test 1 A2 | B5 | Test 2 A3 | B5 | Test 3 A4 | B5 | Test 4 A1 | B6 | Test 1 A2 | B6 | Test 2 A3 | B6 | Test 3 A4 | B6 | Test 4 I want to select the data between userdata and mailings, that adds me a null row to the mailings if mailing table does not have a matching row for key in the grouping uid. So eg the result should look like this key | name | key | uid | name -++-+-+ A1 | Test 1 | A1 | B1 | Test 1 A2 | Test 2 | | | A3 | Test 3 | A3 | B1 | Test 3 A4 | Test 4 | | | A1 | Test 1 | | | A2 | Test 2 | A2 | B2 | Test 2 A2 | Test 2 | A2 | B2 | Test 2 A3 | Test 3 | A3 | B2 | Test 3 A4 | Test 4 | A4 | B2 | Test 4 ... but my problem is, that a normal join will not work, because both tables will hold a complete set of matching key data. I need to sub group the join through the uid column from the userdata. But i have no idea how to do this. Any idea if there is a simple way to do this? -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
On 01/24/2009 12:42 AM, Richard Huxton wrote: Clemens Schwaighofer wrote: On 01/22/2009 07:11 PM, Richard Huxton wrote: Clemens Schwaighofer wrote: Hi, I just literally trashed my test server with one delete statement because the psql used up all its memory and started to swap like crazy. my delete looked like this DELETE FROM table_foo WHERE bar_id IN (SELECT DISTINCT bar_id FROM bar LEFT JOIN ... LEFT JOIN ... LEFT JOIN WHERE table.bar_id IS NULL AND ...) Is it your psql client or PostgreSQL backend that used up all the memory? I can't see how a DELETE can use up memory in psql. psql used up all my physical memory. I didn't thought so too, but I learned something new. a DELETE can use up all my memeory. Can we establish that it really is psql? Can you check with ps aux or top that it's psql and not a backend (postgres) running the query? yes, I checked it with top and ps aux, it is only psql, not the connecting postgres worker Also, can we establish that it is memory that we're running out of - again, ps, top or free -m should show that. it is the physical memory. I will run the process again, and send in results for confirmation -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
[GENERAL] how to avoid that a postgres session eats up all the memory
Hi, I just literally trashed my test server with one delete statement because the psql used up all its memory and started to swap like crazy. my delete looked like this DELETE FROM table_foo WHERE bar_id IN (SELECT DISTINCT bar_id FROM bar LEFT JOIN ... LEFT JOIN ... LEFT JOIN WHERE table.bar_id IS NULL AND ...) so basically it runs a select to see what entries do not have any reference data and then should delete them all. Now, my question is. How can I setup postgres to not use up all the memory and then make the server useless. How can I set it up, so it dies with out of memory before I have to restart the server. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
On 01/22/2009 07:11 PM, Richard Huxton wrote: Clemens Schwaighofer wrote: Hi, I just literally trashed my test server with one delete statement because the psql used up all its memory and started to swap like crazy. my delete looked like this DELETE FROM table_foo WHERE bar_id IN (SELECT DISTINCT bar_id FROM bar LEFT JOIN ... LEFT JOIN ... LEFT JOIN WHERE table.bar_id IS NULL AND ...) Is it your psql client or PostgreSQL backend that used up all the memory? I can't see how a DELETE can use up memory in psql. psql used up all my physical memory. I didn't thought so too, but I learned something new. a DELETE can use up all my memeory. so basically it runs a select to see what entries do not have any reference data and then should delete them all. Now, my question is. How can I setup postgres to not use up all the memory and then make the server useless. How can I set it up, so it dies with out of memory before I have to restart the server. You shouldn't need to restart the server at all - what operating system are you running? i run Linux, Debian/Testing with a 2.6.25.1 kernel. And once he started to swap, or whatever, the load skyrocketed (last was 78) and I couldn't do anything. What memory settings are you using? How much RAM do you have? How much do you want to keep for other applications? As this is just a test/development box, I have no special settings. shared_buffers = 24MB max_fsm_pages = 153600 The server itself has 1GB of memory. All I want, is that psql client does not use up all the memory and make the system unresponsive. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] how to avoid that a postgres session eats up all the memory
On 01/22/2009 07:19 PM, Grzegorz Jaśkiewicz wrote: try making it in two steps,using temp table maybe. how many rows does the subselect generate ? the subselect returns 57.000 rows. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Web Producer/Planning/Manager ] [ E-Graphics Communications SP Digital ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.e-gra.co.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Postgres 8.3 only uses seq scan
On 11/26/2008 06:44 PM, [EMAIL PROTECTED] wrote: Try running EXPLAIN ANALYZE - that gives much more information. For example it may show differences in number of rows between the two machines, that the statistics are not up to date, etc. Thanks a lot for this tip -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] signature.asc Description: OpenPGP digital signature
[GENERAL] Postgres 8.3 only uses seq scan
Hi,
I have system here with Debian/Testing and the latest 8.2 and 8.3
database installed.
on a blank database I create two very simple tables
Table public.foo
Column | Type| Modifiers
+---+--
foo_id | integer | not null default
nextval('foo_foo_id_seq'::regclass)
test | character varying |
Indexes:
foo_pkey PRIMARY KEY, btree (foo_id)
Table public.bar
Column | Type| Modifiers
+---+--
bar_id | integer | not null default
nextval('bar_bar_id_seq'::regclass)
foo_id | integer | not null
test | character varying |
Indexes:
bar_pkey PRIMARY KEY, btree (bar_id)
bar_foo_id_idx btree (foo_id)
Foreign-key constraints:
bar_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foo(foo_id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE
now if I run a simple join query over both tables Postgres 8.2 gives
this back for the explain:
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
Nested Loop (cost=0.00..33.14 rows=3 width=76)
- Index Scan using bar_foo_id_idx on bar b (cost=0.00..12.30
rows=3 width=40)
- Index Scan using foo_pkey on foo f (cost=0.00..6.93 rows=1 width=36)
Index Cond: (f.foo_id = b.foo_id)
but on the 8.3 version i get this back
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
--
Hash Join (cost=1.07..2.14 rows=3 width=24)
Hash Cond: (b.foo_id = f.foo_id)
- Seq Scan on bar b (cost=0.00..1.03 rows=3 width=14)
- Hash (cost=1.03..1.03 rows=3 width=10)
- Seq Scan on foo f (cost=0.00..1.03 rows=3 width=10)
once I insert a million rows he does use the index:
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
---
Nested Loop (cost=0.00..26.39 rows=9 width=35)
- Seq Scan on foo f (cost=0.00..1.03 rows=3 width=21)
- Index Scan using bar_foo_id_idx on bar b (cost=0.00..8.42 rows=3
width=14)
Index Cond: (b.foo_id = f.foo_id)
I have seen this behavior on all of my postgres 8.3 installs. The
indexes are there, auto vacuum is turned on. even a reindex of the
tables does not help. The configuration files are identical in grounds
of memory usage, query planning, etc.
I see this on RPM packages for RedHat Enterprise, self compiled for
FreeBSD 4, and debian packages. I am seriously very very confused.
What can I do to debug this further, or find out why this happens?
Does this mean Postgres 8.3 thinks a sequence scan is faster than an
index scan? Even on tables with hundred thousands rows?
--
[ Clemens Schwaighofer -=:~ ]
[ IT Engineer/Manager]
[ E-Graphics Communications, TEQUILA\ Japan IT Group ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp ]
signature.asc
Description: PGP signature
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Postgres 8.3 only uses seq scan
On 11/26/2008 02:04 PM, Scott Marlowe wrote: On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer [EMAIL PROTECTED] wrote: but on the 8.3 version i get this back # explain select * from foo f, bar b where f.foo_id = b.foo_id; QUERY PLAN -- Hash Join (cost=1.07..2.14 rows=3 width=24) Hash Cond: (b.foo_id = f.foo_id) - Seq Scan on bar b (cost=0.00..1.03 rows=3 width=14) - Hash (cost=1.03..1.03 rows=3 width=10) - Seq Scan on foo f (cost=0.00..1.03 rows=3 width=10) Of course it uses a seq scan. All the data fits handily into a single page I assume. okay, the strange thing is, that in 8.2 it always used an index scan. once I insert a million rows he does use the index: # explain select * from foo f, bar b where f.foo_id = b.foo_id; QUERY PLAN --- Nested Loop (cost=0.00..26.39 rows=9 width=35) - Seq Scan on foo f (cost=0.00..1.03 rows=3 width=21) - Index Scan using bar_foo_id_idx on bar b (cost=0.00..8.42 rows=3 width=14) Index Cond: (b.foo_id = f.foo_id) I don't see a million rows here, only three. Have you run analyze after loading all that data? Or is it retrieving 3 rows out of a million? If so then an index scan does make sense. yeah, there are 3 matching rows, and the rest is just data to make the table big. I am just still confused, because if Postgres does only use seq scan even in very large databases, I am worried I do something very wrong in my DB design ... -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Postgres 8.3 only uses seq scan
On 11/26/2008 02:15 PM, Scott Marlowe wrote: On Tue, Nov 25, 2008 at 10:07 PM, Clemens Schwaighofer [EMAIL PROTECTED] wrote: On 11/26/2008 02:04 PM, Scott Marlowe wrote: On Tue, Nov 25, 2008 at 8:39 PM, Clemens Schwaighofer [EMAIL PROTECTED] wrote: but on the 8.3 version i get this back # explain select * from foo f, bar b where f.foo_id = b.foo_id; QUERY PLAN -- Hash Join (cost=1.07..2.14 rows=3 width=24) Hash Cond: (b.foo_id = f.foo_id) - Seq Scan on bar b (cost=0.00..1.03 rows=3 width=14) - Hash (cost=1.03..1.03 rows=3 width=10) - Seq Scan on foo f (cost=0.00..1.03 rows=3 width=10) Of course it uses a seq scan. All the data fits handily into a single page I assume. okay, the strange thing is, that in 8.2 it always used an index scan. Are there more rows in the 8.2 table you're testing on? Or is the whole table small enough to fit on a few pages? I highly doubt that. I have right now in one of the DBs I transfered tables from ~100.000 down to ~40.000 rows that all join together. I somehow really doubt that fit in a few pages. That is why I was so surprised to see such a big difference in the explain. once I insert a million rows he does use the index: # explain select * from foo f, bar b where f.foo_id = b.foo_id; QUERY PLAN --- Nested Loop (cost=0.00..26.39 rows=9 width=35) - Seq Scan on foo f (cost=0.00..1.03 rows=3 width=21) - Index Scan using bar_foo_id_idx on bar b (cost=0.00..8.42 rows=3 width=14) Index Cond: (b.foo_id = f.foo_id) I don't see a million rows here, only three. Have you run analyze after loading all that data? Or is it retrieving 3 rows out of a million? If so then an index scan does make sense. yeah, there are 3 matching rows, and the rest is just data to make the table big. I am just still confused, because if Postgres does only use seq scan even in very large databases, I am worried I do something very wrong in my DB design ... Postgresql has no visibility in its indexes, meaning that whether it uses an index or not, it still has to go to the table to see if the tuple is actually visible to this transaction. For this reason, PostgreSQL switches to sequential scans quicker than other dbs that have visibility information in their indexes. The planner is pretty smart, but if you're going to hit a large % of the table anyway, it switches to sequential scans since it will have to retreive the majority of the table anyway. So, I am fine when I trust the Postgresql planner :) Because speed wise I see no difference that 8.3 would be slower than 8.2 -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Postgres 8.3 only uses seq scan
On 11/26/2008 03:20 PM, Scott Marlowe wrote: On Tue, Nov 25, 2008 at 10:22 PM, Clemens Schwaighofer [EMAIL PROTECTED] wrote: On 11/26/2008 02:15 PM, Scott Marlowe wrote: Are there more rows in the 8.2 table you're testing on? Or is the whole table small enough to fit on a few pages? I highly doubt that. I have right now in one of the DBs I transfered tables from ~100.000 down to ~40.000 rows that all join together. I somehow really doubt that fit in a few pages. Right, with more rows, and choosing fewer, pgsql will go for an index scan. if choosing a good %, the seq scan. okay, then I think I get it. One of my test queries was actually selecting a very big chunk (90%) of the data, so it makes sense the planner chooses seq scan over index scan here. So, I am fine when I trust the Postgresql planner :) Because speed wise I see no difference that 8.3 would be slower than 8.2 Well, the planner's not perfect. Some off corner cases can catch it out, or if your database isn't analyzed after a lot of changes it may make an uninformed decision. But most the time it makes the right, or close enough, decision. Well, I have autovacuum turned on, so this should hopefully keep the planner up to date. Anyway, thanks a lot for your help. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] signature.asc Description: OpenPGP digital signature
[GENERAL] Postgres 8.3 is not using indexes
Hi, i just stumbled on something very strange. I have here a Postgres 8.3 and a Postgres 8.2 installation, as I am in the process of merging. Both are from the debian/testing tree, both have the same configuration file. In my DB where I found out this trouble I have two tables, I do a very simple join over both. The foreign key in the second table has an index. Postgres 8.2 gives me this out: explain SELECT DISTINCT email FROM email e, email_group eg WHERE e.email_group_id = eg.email_group_i QUERY PLAN -- Unique (cost=65.16..66.81 rows=85 width=27) - Sort (cost=65.16..65.98 rows=330 width=27) Sort Key: e.email - Merge Join (cost=0.00..51.35 rows=330 width=27) Merge Cond: (eg.email_group_id = e.email_group_id) - Index Scan using email_group_pkey on email_group eg (cost=0.00..12.91 rows=44 width=4) - Index Scan using idx_email_email_group_id on email e (cost=0.00..34.21 rows=330 width=31) Postgres 8.3 returns this: explain SELECT DISTINCT email FROM email e, email_group eg WHERE e.email_group_id = eg.email_group_id; QUERY PLAN --- Unique (cost=268688.95..274975.13 rows=51213 width=26) - Sort (cost=268688.95..271832.04 rows=1257236 width=26) Sort Key: e.email - Hash Join (cost=2.12..85452.48 rows=1257236 width=26) Hash Cond: (e.email_group_id = eg.email_group_id) - Seq Scan on email e (cost=0.00..68163.36 rows=1257236 width=30) - Hash (cost=1.50..1.50 rows=50 width=4) - Seq Scan on email_group eg (cost=0.00..1.50 rows=50 width=4) I have reindexed the tables, vacuum (analyze) the whole DB, checked the config if there are some settings different. But I am at a loss here. Why is Postgres not using the indexes in the 8.3 installation. I tried this on a different DB on the same server and on a different server and I always get seq_scan back and never the usage of the index. Any tips why this is so? -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] php + postgresql website ?
--On Monday, June 30, 2008 08:06:25 PM +0800 paragasu [EMAIL PROTECTED] wrote: i can name a lot of website written in mysql and php. currently, i am planning to use postgresql database for my next project. i know there is a lot of testimonial about postgresql is better than mysql. can anyone please give me an example of website using postgresql database? I can't give you a direct example, but I use postgres for all of my webpages with database ranging from a view MB to up to several GB with tens of thausends of rows. I haven't had any trouble since the 5 years I am using it ... [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Probably been asked a hundred times before.
--On Tuesday, June 24, 2008 10:30:14 AM -0400 David Siebert [EMAIL PROTECTED] wrote: Which disto is best for running a Postgres server? I run most of my postgres servers on Debian. I really love it, because once a new major version comes out you can very easy install it parallel to your current version and test and once you are done, migrate the data and just switch the port. I also have some external servers with RedHat ES and postgres. But when I upgrade there I use the RPMs provided by postgres. Major version upgrades are a bit more tricky here, but they work. [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Index cleanup
Hi, recently I went through some dev dbs just to clean up some unused tables. What I found out afterwards is that even if you run a vacuum full analyze it doesn't removed the index data. So I was left with table size of a view bytes and gigabyte large indexes. Of course a re-index cleaned that up. My question now is, is this intended? Should vacuum full not clean out the indexes if the table for this index is emptied or a lot of data is removed. Should that happen after some time? I doubt, because on one of my life servers a test db had a size of 33MB overall, but an index size of 1.5GB ... Whats the best way to handles this on a production system. I doubt its wise to recreate indexes every month or so ... -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Encoding conversion problem
On 04/23/2008 04:33 PM, Albe Laurenz wrote: Michael Fuhr wrote: I sometimes have a problem with conversion of encodings eg from UTF-8 tio ShiftJIS: ERROR: character 0xf0a0aeb7 of encoding UTF8 has no equivalent in SJIS I have no idea what character this is, I cannot view it in my browser, etc. It translates to Unicode 10BB7, which is not defined. Actually it's U+20BB7 CJK UNIFIED IDEOGRAPH-20BB7. Oops, you're correct. Made an error in my calculations. Thanks. So that explains the problem. Still, to handle it, the offending character needs to be changed before converting to SJIS. probably wont get around a clean up before writing script. *sigh* Or export the data in UTF-8 ... -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Encoding conversion problem
On 04/22/2008 05:37 PM, Albe Laurenz wrote: Clemens Schwaighofer wrote: I sometimes have a problem with conversion of encodings eg from UTF-8 tio ShiftJIS: ERROR: character 0xf0a0aeb7 of encoding UTF8 has no equivalent in SJIS I have no idea what character this is, I cannot view it in my browser, etc. It translates to Unicode 10BB7, which is not defined. I guess that is not intended; can you guess what the character(s) should be? to be honest no idea. its some chinese character, I have no idea how the user input this, because this is a japanese page. I actually found the carachter, but only my Mac OS X can show it. It looks similar to a japanese character used for a name, but how the chinese one got selected is a mystery to me ... If I run the conversion through PHP with mb_convert_encoding it works, perhaps he is ignoring the character. Is there a way to do a similar thing, like ignoring this character in postgres too? As far as I know, no. You'll have to fix the data before you import them. well, the web page data is in utf8 so I never see this issue, except I would write a method that detects illegal shift_jis characters, and thats difficult. The reporting is only done in CSV ... so I am not sure if it is worth to waste too much time here. thanks for the tip. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Encoding conversion problem
On 04/22/2008 07:30 PM, Albe Laurenz wrote: Clemens Schwaighofer wrote: I sometimes have a problem with conversion of encodings eg from UTF-8 tio ShiftJIS: ERROR: character 0xf0a0aeb7 of encoding UTF8 has no equivalent in SJIS I have no idea what character this is, I cannot view it in my browser, etc. It translates to Unicode 10BB7, which is not defined. I guess that is not intended; can you guess what the character(s) should be? to be honest no idea. its some chinese character, I have no idea how the user input this, because this is a japanese page. I actually found the carachter, but only my Mac OS X can show it. It looks similar to a japanese character used for a name, but how the chinese one got selected is a mystery to me ... Are you sure that your Mac OS X computer interprets the character as UTF-8? That I cannot be sure, I just searched through a page that has a complete list. OS X can render it, Linux cannot, I have not tried windows. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres Encoding conversion problem
Hi, I sometimes have a problem with conversion of encodings eg from UTF-8 tio ShiftJIS: ERROR: character 0xf0a0aeb7 of encoding UTF8 has no equivalent in SJIS I have no idea what character this is, I cannot view it in my browser, etc. If I run the conversion through PHP with mb_convert_encoding it works, perhaps he is ignoring the character. Is there a way to do a similar thing, like ignoring this character in postgres too? -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] strange dump problem
local system: 8.2.7
remote system: 8.2.5
problem:
I have a small database with some tables. one of the tables had no
primary key, but a column with a fitting sequence (was created with serial).
so I added a primary key to it and it is visible and working.
but, when I dump some of the tables (4, 1 generic thats get inherit, 3
normal ones), only the other two are dumped in a normal format:
...
display_oid integer not null,
...
create sequence ...
etc,
the one table where I added the primary afterwards gets dumped like this:
...
mobile_oid integer DEFAULT nextval('mobile_mobile_oid_seq'::regclass)
NOT NULL,
...
and no sequence is created, etc
when I try to import this into the remote system it failes and does not
want to create the one table, because it cannot find the sequence
'mobile_mobile_oid_seq'
the data was dumped with:
pg_dump -U postgres -c -f output.sql -t table_1 -t ... database_name
is there any explenation why postgres would do that?
--
[ Clemens Schwaighofer -=:~ ]
[ IT Engineer/Manager, TEQUILA\ Japan IT Group ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp ]
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Survey: renaming/removing script binaries (createdb, createuser...)
1) What type of names do you prefer? --- b) new one with pg_ prefix - pg_createdb, pg_creteuser ... 2) How often do you use these tools? --- a) every day (e.g. in my cron) 3) What name of initdb do you prefer? -- -- b) pg_initdb 4) How do you perform VACUUM? - a) vacuumdb - shell command b) VACUUM - SQL command [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question for Postgres 8.3
hi, on the page: http://www.postgresql.org/docs/8.3/static/release-8-3.html in point E.1.2.3: * Disallow database encodings that are inconsistent with the server's locale setting (Tom) does this mean, if my server LOCALE is for example UTF-8.en_US, and I want to create a EUC_JP database it gets rejected? do I missunderstand that? Normaly my servers have default locale set to UTF-8.en_US but also have the locales for UTF-8.ja_JP and EUC_JP there, 99.9% of my databases are utf-8, but I have some clients that created EUC_JP databases, will the upgrade affect this? [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] ---(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] Question for Postgres 8.3
On 02/05/2008 11:38 AM, Tom Lane wrote: Clemens Schwaighofer [EMAIL PROTECTED] writes: * Disallow database encodings that are inconsistent with the server's locale setting (Tom) does this mean, if my server LOCALE is for example UTF-8.en_US, and I want to create a EUC_JP database it gets rejected? do I missunderstand that? Nope, you have it correctly. okay, good, as I finally have a reason to change this then on those view databases. Normaly my servers have default locale set to UTF-8.en_US but also have the locales for UTF-8.ja_JP and EUC_JP there, 99.9% of my databases are utf-8, but I have some clients that created EUC_JP databases, will the upgrade affect this? I'm surprised your clients haven't been screaming about bogus sorting and upper/lowercasing behavior. well, originally they were on a server that is also in EUC_JP, but they need to move away to one of my servers that is pure UTF-8, and I cannot change this setting because of my other databases, so probably better to convert them over to UTF-8 and adept the scripts to convert to the target encoding, rather than going on with the same mess. If you want to support multiple encodings, the only safe locale choice is (and always has been) C. If you doubt this, troll the archives for awhile --- for example, searching for locale+encoding in pgsql-bugs should provide plenty of amusing reading matter. 8.3 is just refusing to do things that are known to be unsafe in previous releases. regards, tom lane -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question for Postgres 8.3
On 02/05/2008 03:38 PM, Hiroshi Saito wrote: Hi. This reply is very glad for me!:-) However, There is still a locale problem. If I use an EUC database on an UTF8 system? Well I will try out and see if it breaks everything. Anyway, thanks Regards, Hiroshi Saito - Original Message - From: Clemens Schwaighofer [EMAIL PROTECTED] I'm surprised your clients haven't been screaming about bogus sorting and upper/lowercasing behavior. Umm, This is a mere information. (Since you dislike, this may be a noise.?) As for upper/lower. It does not change, even if uses which locale in us Japanese. (there is nothing with the alphabet) Then, In spite of using a small letter and a capital letter properly clearly. As for sorting. It has a difference, if sorting of a dictionary is desired. However, each is the not Japanese order of the JIS X 4061:1996(Collation of Japanese character string), probably. but, I think that LOCAL=C is clear as a sorting of a dictionary. It seems that selection is good by the situation well. See, Although you may be unable to see (However, this is Japanes shift-jis) http://winpg.jp/~saito/pg_bug/Japanese_LowerUpper_sort.txt もしかしたらLinux LOCALEはUTF-8.en_USとPostgresのLOCALEはCその日本語の ソートは正しくない？僕はいつでもLOCAL=Cある、と今テストしました。ソート は正しいです。 ありがとうございます。 -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jp ] signature.asc Description: OpenPGP digital signature
[GENERAL] Query problem
hi,
thats the first time I am a bit confused by a query not working.
I have this table:
gullevek=# \d test
Table public.test
Column | Type| Modifiers
-+---+
test_id | integer | not null default
nextval('test_test_id_seq'::regclass)
email_a | character varying |
email_b | character varying |
Indexes:
test_pkey PRIMARY KEY, btree (test_id)
with this content:
gullevek=# select * from test;
test_id |email_a| email_b
-+---+-
2 | [EMAIL PROTECTED] | [EMAIL PROTECTED]
1 | [EMAIL PROTECTED] |
(2 rows)
if I do this select:
select * from (select test_id, email_a FROM test WHERE email_a =
'[EMAIL PROTECTED]') as s, (select test_id, email_b from test where email_b =
'[EMAIL PROTECTED]') as t;
I get no rows back. should it return something? subquery a doesn't
return one, because the email does not exist, b returns something.
how do I have to make the query so I get a result back and in one row?
--
[ Clemens Schwaighofer -=:~ ]
[ TEQUILA\ Japan IT Group]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp ]
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Query problem
On 2007/10/12, at 23:22, Pavel Stehule wrote: you get 0 rows. [EMAIL PROTECTED] isn't anywhere and [EMAIL PROTECTED] cannot do pair with any. else 0 * 1 = 0 Thanks, go it now. Basically very simple. I probably need to approach the problem different. So both are read independent or something like this. -- [ Clemens Schwaighofer -=:~ ] [ TEQUILA\ Japan IT Engineer ] [6-17-2 Ginza Chuo-ku, Tokyo 104-0061, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jphttp://www.tbwajapan.co.jp ] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] bad function lets postgres crash
hi,
Although I do a lot with postgres, I am quite new to functions. So
yesterday I was playing around with a very simple one. Of course I did
something very stupid and created an infinite loop in the end.
My problem is, that this crashed one of my postgres server, but the
other just through an error with
LOG: autovacuum: processing database adidas_myrunning
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter max_stack_depth.
CONTEXT: SQL statement ...
The one that crashed through out this:
[2007-07-03 19:03:14 JST [EMAIL PROTECTED] idle] LOG: statement:
insert into mail_log (mailid, log_time_raw) values ('l617kuTu072790',
'Jul 2 17:31:33');
[2007-07-03 19:03:15 JST @ ] LOG: server process (PID 10345) was
terminated by signal 11
[2007-07-03 19:03:15 JST @ ] LOG: terminating any other active server
processes
after that it restarted itself again.
The function I created was this:
CREATE OR REPLACE FUNCTION insert_mail_log() RETURNS trigger
AS $$
DECLARE
myrec RECORD;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT INTO myrec t.* FROM mail_log t WHERE t.log_time_raw =
NEW.log_time_raw AND t.mailid = NEW.mailid;
IF NOT FOUND THEN
INSERT INTO mail_log (ident, email, log_time_raw, log_time,
mail_host, dsn, status, relay, mailid, exported) VALUES (NEW.ident,
NEW.email, NEW.log_time_raw, NEW.log_time, NEW.mail_host, NEW.dsn,
NEW.status, NEW.relay, NEW.mailid, NEW.exported);
RAISE EXCEPTION 'NOT FOUND INSERT';
RETURN NEW;
ELSE
RAISE EXCEPTION 'FOUND UPDATE';
RETURN NULL;
END IF;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
of course my logic flow was wrong, and an insert into the table where I
call this as an trigger is totaly stupid.
both servers are debian packages 8.1.9-1, but on the one that crashed I
increased the stack depth to 8192. Could it be that postgres ran out of
memory and terminated itself?
--
[ Clemens Schwaighofer -=:~ ]
[ TEQUILA\ Japan IT Group]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp ]
signature.asc
Description: OpenPGP digital signature
