Re: [GENERAL] Bug? Query plans / EXPLAIN using gigabytes of memory
On 26/04/12 16:58, Tom Lane wrote: Toby Corkindale writes: On 26/04/12 15:30, Tom Lane wrote: Hm, is the update target an inheritance tree? The target is the parent table of a bunch of partitions. How many would "a bunch" be, exactly? I'm fairly sure that the complex view would get re-planned for each target table ... The table being updated (line) has 57 child tables. -Toby -- 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] Bug? Query plans / EXPLAIN using gigabytes of memory
On 26/04/12 17:16, Toby Corkindale wrote: On 26/04/12 16:58, Tom Lane wrote: Toby Corkindale writes: On 26/04/12 15:30, Tom Lane wrote: Hm, is the update target an inheritance tree? The target is the parent table of a bunch of partitions. How many would "a bunch" be, exactly? I'm fairly sure that the complex view would get re-planned for each target table ... The table being updated (line) has 57 child tables. Although we are specifying a value for the column which they are partitioned on. ie. CREATE TABLE line (file_id, lineno, status, primary key (file_id, lineno)); CREATE TABLE line_1 ( CHECK (file_id=1) ) INHERITS (line); CREATE TABLE line_2 ( CHECK (file_id=2) ) INHERITS (line); UPDATE line SET status = something FROM complex_view cv WHERE cv.file_id = 2 AND line.file_id=2; -- 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] Bug? Query plans / EXPLAIN using gigabytes of memory
On 26/04/12 13:11, Tom Lane wrote: Toby Corkindale writes: Just wondering if anyone else has thoughts on this? I'm still suspicious that this is a bug. Well, if you were to provide a reproducible test case, somebody might be motivated to look into it. There could be a memory leak in the planner somewhere, but without a test case it's not very practical to go look for it. Hi, I've created a bit of a test case now. There's a Perl script here: http://dryft.net/postgres/ Running it will create a test database that's populated with quite a lot of schemas and partitioned tables, and a few views. Running EXPLAIN on the query on that database at the end added ~700MB to the server-side postgres process. It's not the same as 3.4GB I've seen on our bigger database warehouse, but maybe it's enough to help? Let me know if I can help elaborate further, Toby -- 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] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger
I'm not sure the cause is that DELETE does not see the row. Seems the following method solves the problem when 2 same-time transactions are active: CREATE FUNCTION a_tr() RETURNS trigger AS $body$ DECLARE tmp INTEGER; BEGIN -- Lock until the mathed entry (possibly phantom - i.e. not yet committed -- by another transaction) is released. SELECT i INTO tmp FROM a WHERE i = NEW.i FOR UPDATE; -- The lock is released here in one of two cases: -- 1. Matched row was phantom, so tmp IS NULL now. -- 2. Matched row was real and committed, so tmp holds its ID. -- So we cannot use ID in tmp - it is not always returned. That's why we have to -- duplicate the selection predicate above... DELETE FROM a WHERE i = NEW.i; RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; But this method still does not work if 3 or more transactions are active (if I commit first and commit second, the third fails with "duplicate key" error). Are there any universal method which could be implemented purely in a trigger?.. On Fri, Jan 27, 2012 at 3:45 PM, Julian v. Bock wrote: > Hi > > > "DK" == Dmitry Koterov writes: > > DK> create table a(i integer); > DK> CREATE UNIQUE INDEX a_idx ON a USING btree (i); > DK> CREATE FUNCTION a_tr() RETURNS trigger AS > DK> $body$ > DK> BEGIN > DK> DELETE FROM a WHERE i = NEW.i; > DK> RETURN NEW; > DK> END; > DK> $body$ > DK> LANGUAGE 'plpgsql'; > DK> CREATE TRIGGER a_tr BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE > DK> a_tr(); > > The DELETE doesn't see the row the other transaction inserted and > doesn't delete anything (and doesn't block). This happens later when the > row is inserted and the index is updated. > > You can try the insert and catch the unique violation in a loop (see > http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html) > although that won't work with a BEFORE trigger. > > Regards, > Julian > > -- > Julian v. Bock Projektleitung Software-Entwicklung > OpenIT GmbH Tel +49 211 239 577-0 > In der Steele 33a-41 Fax +49 211 239 577-10 > D-40599 Düsseldorf http://www.openit.de > > HRB 38815 Amtsgericht Düsseldorf USt-Id DE 812951861 > Geschäftsführer: Oliver Haakert, Maurice Kemmann > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] R-tree parallel index creation
Hello, Anyone know if this features is supported in PostgreSQL: "R-tree index creation can be subdivided into smaller tasks that can be performed in parallel" ? Best Regards, Iacovlev Pavel
[GENERAL] WARNING: worker took too long to start; cancelled
Hi, Postgres9.0.5 is operated on Linux. Left the following logs and CPU shows 100%. WARNING: worker took too long to start; cancelled The error which is not connectable with a database had generated web application then. When DB server was rebooted, it came to operate correctly again. Although I think that there are some causes by which this log is displayed, if you know at least any one information about a cause, please let me know. Moreover, if a solution is also known, please let me know.
Re: [GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger
I have had 2 hours of experiments and finally I suppose that there is no way to satisfy this unique constraint index from within a trigger with non-whole-table locking. So Julian seems to be right (unfortunately). Only LOOP BEGIN INSERT ...; EXIT; EXCEPTION WHEN unique_violation THEN DELETE FROM ... WHERE ; END; END LOOP; construction helps. There seems to be no way to implement the same using triggers only. On Thu, Apr 26, 2012 at 3:39 PM, Dmitry Koterov wrote: > I'm not sure the cause is that DELETE does not see the row. > > Seems the following method solves the problem when 2 same-time > transactions are active: > > CREATE FUNCTION a_tr() RETURNS trigger AS > $body$ > DECLARE > tmp INTEGER; > BEGIN > -- Lock until the mathed entry (possibly phantom - i.e. not yet > committed > -- by another transaction) is released. > SELECT i INTO tmp FROM a WHERE i = NEW.i FOR UPDATE; > > -- The lock is released here in one of two cases: > -- 1. Matched row was phantom, so tmp IS NULL now. > -- 2. Matched row was real and committed, so tmp holds its ID. > -- So we cannot use ID in tmp - it is not always returned. That's why > we have to > -- duplicate the selection predicate above... > DELETE FROM a WHERE i = NEW.i; > > RETURN NEW; > END; > $body$ > LANGUAGE 'plpgsql'; > > But this method still does not work if 3 or more transactions are active > (if I commit first and commit second, the third fails with "duplicate key" > error). > > Are there any universal method which could be implemented purely in a > trigger?.. > > > > On Fri, Jan 27, 2012 at 3:45 PM, Julian v. Bock wrote: > >> Hi >> >> > "DK" == Dmitry Koterov writes: >> >> DK> create table a(i integer); >> DK> CREATE UNIQUE INDEX a_idx ON a USING btree (i); >> DK> CREATE FUNCTION a_tr() RETURNS trigger AS >> DK> $body$ >> DK> BEGIN >> DK> DELETE FROM a WHERE i = NEW.i; >> DK> RETURN NEW; >> DK> END; >> DK> $body$ >> DK> LANGUAGE 'plpgsql'; >> DK> CREATE TRIGGER a_tr BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE >> DK> a_tr(); >> >> The DELETE doesn't see the row the other transaction inserted and >> doesn't delete anything (and doesn't block). This happens later when the >> row is inserted and the index is updated. >> >> You can try the insert and catch the unique violation in a loop (see >> http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html >> ) >> although that won't work with a BEFORE trigger. >> >> Regards, >> Julian >> >> -- >> Julian v. Bock Projektleitung Software-Entwicklung >> OpenIT GmbH Tel +49 211 239 577-0 >> In der Steele 33a-41 Fax +49 211 239 577-10 >> D-40599 Düsseldorf http://www.openit.de >> >> HRB 38815 Amtsgericht Düsseldorf USt-Id DE 812951861 >> Geschäftsführer: Oliver Haakert, Maurice Kemmann >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
[GENERAL] database error xx000?
I am doing a ton of pgsql over here defining and redefining functions and triggers and every day or so I get this: Error: Database error XX000: cache lookup failed for type 5276542 > > Query: select dcm.task_user_dispos(42895::bigint, 870::bigint) > > [condition type: internal-error] > > If I exit everything and start over (but not bouncing the PG server) the problem goes away. I gather this kind of DB error is not a user error, but I would still be interested in learning how to avoid it because even in production we will need to be redefining functions periodically. Does the above give anyone any ideas at all about what is going on? Environemnt is: Version string PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by > gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit Thx, ken
Re: [GENERAL] database error xx000?
Kenneth Tilton writes: > I am doing a ton of pgsql over here defining and redefining functions and > triggers and every day or so I get this: > Error: Database error XX000: cache lookup failed for type 5276542 > Does the above give anyone any ideas at all about what is going on? No. Can you produce a self-contained test case? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] database error xx000?
On Thu, Apr 26, 2012 at 2:24 PM, Tom Lane wrote: > Kenneth Tilton writes: > > I am doing a ton of pgsql over here defining and redefining functions and > > triggers and every day or so I get this: > > > Error: Database error XX000: cache lookup failed for type 5276542 > > > Does the above give anyone any ideas at all about what is going on? > > No. :) What is "type 5276542"? Does not look like much of a type. And why is a cache lookup an error? If it is not in the cache go find the definition! :) I guess it "knows" something should be in the cache and then does not find it. Prolly that code needs to be taught about the possibility of things being redefined so it goes out and finds the function by name. Of course if it threw away the name and kept only a cache pointer...well, then it needs to stop throwing away the name. > Can you produce a self-contained test case? > I doubt it. Every test iteration I run includes a lot of redefining of functions and triggers all over the map, and it works fine thru dozens of iterations. The wheels seem to come off after a serious refactoring. Nothing is needed to clear the problem except (it seems) close any connections to the DB, so I guess some optimization does not work in the face of sufficient dynamic redefinition. -kt
Re: [GENERAL] database error xx000?
On Thu, Apr 26, 2012 at 1:59 PM, Kenneth Tilton wrote: > On Thu, Apr 26, 2012 at 2:24 PM, Tom Lane wrote: > > >> Can you produce a self-contained test case? >> > > I doubt it. Every test iteration I run includes a lot of redefining of > functions and triggers all over the map, and it works fine thru dozens of > iterations. The wheels seem to come off after a serious refactoring. > Nothing is needed to clear the problem except (it seems) close any > connections to the DB, so I guess some optimization does not work in the > face of sufficient dynamic redefinition. > > > Suggestion: create a program which attempts to stress-test Postgresql by doing similar things. Even if a single test case does not reliably reproduce the issue, a stress-test might trigger the condition after some time.
[GENERAL] Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
Hello, I run a small card game with PostgreSQL 8.4.11 on CentOS 6 at https://apps.facebook.com/video-preferans/ List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+--+--+-+-+--- pref | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | The client is in Flash, game daemon in Perl 5.10.1 and the web pages (using same database) in PHP 5.3. My game uses UTF8 and uses the Unicode chars ♠♦♣♥ for the card suits + game language/users are russian. The users ask for 1 feature for longer time already: a game journal (i.e. logging of cards dealt and played). I'd like to store that data (user names, their hands, etc.) as JSON data in UTF8 encoding, length aprox. 1000 bytes Does anybody have an advice on what data type to use best for such a JSON "string"? Should I take varchar, text or bytea. And for the latter - how to handle it in Perl if I currently use DBI and DBD::Pg? For PHP I probably should use pg_(un)escape_bytea? (And does this all work with "?" placholders?) Regards Alex -- 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] Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
On Thu, Apr 26, 2012 at 2:45 PM, Alexander Farber wrote: > Hello, > > I run a small card game with PostgreSQL 8.4.11 on > CentOS 6 at https://apps.facebook.com/video-preferans/ > > List of databases > Name | Owner | Encoding | Collation | Ctype | Access > privileges > ---+--+--+-+-+--- > pref | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > > The client is in Flash, game daemon in Perl 5.10.1 > and the web pages (using same database) in PHP 5.3. > > My game uses UTF8 and uses the Unicode chars ♠♦♣♥ > for the card suits + game language/users are russian. > > The users ask for 1 feature for longer time already: > a game journal (i.e. logging of cards dealt and played). > > I'd like to store that data (user names, their hands, etc.) > as JSON data in UTF8 encoding, length aprox. 1000 bytes > > Does anybody have an advice on what data type > to use best for such a JSON "string"? > > Should I take varchar, text or bytea. > > And for the latter - how to handle it in Perl > if I currently use DBI and DBD::Pg? > > For PHP I probably should use pg_(un)escape_bytea? > > (And does this all work with "?" placholders?) upgrade your database and use the new json type/features. merlin -- 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] database error xx000?
On Thu, Apr 26, 2012 at 3:07 PM, dennis jenkins wrote: > On Thu, Apr 26, 2012 at 1:59 PM, Kenneth Tilton wrote: > >> On Thu, Apr 26, 2012 at 2:24 PM, Tom Lane wrote: >> >> >>> Can you produce a self-contained test case? >>> >> >> I doubt it. Every test iteration I run includes a lot of redefining of >> functions and triggers all over the map, and it works fine thru dozens of >> iterations. The wheels seem to come off after a serious refactoring. >> Nothing is needed to clear the problem except (it seems) close any >> connections to the DB, so I guess some optimization does not work in the >> face of sufficient dynamic redefinition. >> >> >> > Suggestion: create a program which attempts to stress-test Postgresql by > doing similar things. Even if a single test case does not reliably > reproduce the issue, a stress-test might trigger the condition after some > time. > > I am starting to suspect this does it (having reproduced the problem with vastly less effort than suspected): with function A calling function B, and function B coming in several variants (diff params), drop then define A and then drop/define only one variant of B. Order there may not matter. I plan anyway to enhance our builder so it knows about function overloading and rebuilds all variants when it rebuilds one. If the problem goes away I'll take a crack at creating a reproducible. Thx for the feedback. -ken
[GENERAL] Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE
Hello. For example, I have 2 functions like these: CREATE OR REPLACE FUNCTION first(a INTEGER, b INTEGER, ...) RETURNS ... AS $body$ ...any SQL which uses $1, $2 etc. arguments, plus LIMIT $3... $body$ LANGUAGE 'sql' *STABLE* and CREATE OR REPLACE FUNCTION second(a INTEGER, b INTEGER, ...) RETURNS ... AS $body$ DECLARE res ...; BEGIN EXECUTE '...the same SELECT, ' || 'but ' || quote_literal(a) || ' args are embedded, plus ' || 'LIMIT ' || quote_literal($3) INTO res; RETURN res; END; $body$ LANGUAGE 'plpgsql' *STABLE* And then I call EXPLAIN ANALYZE SELECT * FROM first(...); EXPLAIN ANALYZE SELECT * FROM second(...); Should these two queries be executed by the same time usage (i.e. does PostgreSQL generate same plans for inner queries)? I always thought that the answer is YES: if a function is STABLE and with language=SQL, its SQL code is embedded into outer context after all arguments are expanded into their values (so the plan is built after argument expansion). But some days ago I detected a case when second() works about 100 times faster than first(), and the cause is seems that the planner does not see all of expanded arguments in first() (if I replace arguments to constants in first(), especially in LIMIT clause, it begins to work the same speed as second() does). Unfortunately EXPLAIN ANALYZE does not go into functions and shows only overall time, so I have no real information about what plan is actually used in first(). Could you please comment this case a bit?..
Re: [GENERAL] Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
Hello, On Thu, Apr 26, 2012 at 10:11 PM, Merlin Moncure wrote: >> Does anybody have an advice on what data type >> to use best for such a JSON "string"? >> >> Should I take varchar, text or bytea. >> >> And for the latter - how to handle it in Perl >> if I currently use DBI and DBD::Pg? >> >> For PHP I probably should use pg_(un)escape_bytea? >> >> (And does this all work with "?" placholders?) > > upgrade your database and use the new json type/features. do you mean PostgreSQL 9.x? I don't see anything "json" at http://www.postgresql.org/docs/9.0/static/datatype.html or in the search (besides something in "contrib")? And does it work well with Perl 5.10 and PHP 5.3? My hoster offers CentOS 6.x only Regards Alex -- 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] Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?
On Thu, Apr 26, 2012 at 3:46 PM, Alexander Farber wrote: > Hello, > > On Thu, Apr 26, 2012 at 10:11 PM, Merlin Moncure wrote: >>> Does anybody have an advice on what data type >>> to use best for such a JSON "string"? >>> >>> Should I take varchar, text or bytea. >>> >>> And for the latter - how to handle it in Perl >>> if I currently use DBI and DBD::Pg? >>> >>> For PHP I probably should use pg_(un)escape_bytea? >>> >>> (And does this all work with "?" placholders?) >> >> upgrade your database and use the new json type/features. > > do you mean PostgreSQL 9.x? > > I don't see anything "json" at > http://www.postgresql.org/docs/9.0/static/datatype.html > or in the search (besides something in "contrib")? > > And does it work well with Perl 5.10 and PHP 5.3? > > My hoster offers CentOS 6.x only it's in core for 9.2. for 9.1 you have to compile and install it as an extension: http://people.planetpostgresql.org/andrew/index.php?/archives/255-JSON-for-PG-9.2-...-and-now-for-9.1!.html also http://people.planetpostgresql.org/andrew/index.php?/archives/244-Under-the-wire.html barring the above, I'd definitely use the 'text' type (or varchar, which is the same thing). all should play well with php. merlin -- 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] Bug? Query plans / EXPLAIN using gigabytes of memory
Toby Corkindale writes: > I've created a bit of a test case now. > There's a Perl script here: > http://dryft.net/postgres/ AFAICT, what is happening is that we're repeating the planning of that messy nest of views for each child table of foo. For most of the children the planner eventually decides that the join degenerates to nothing because of constraint exclusion, but not until it's expended a fair amount of time and memory space per child. I looked at whether we could improve that by having inheritance_planner use a temporary memory context per child, but that doesn't look very practical: it would add a good deal of extra data-copying overhead, and some of the data structures involved are not easily copiable. The general scheme of replanning per child might be questioned as well, but IMO it's fairly important given the looseness of inheritance restrictions --- it's not unlikely that you *need* different plans for different children. We might be able to reconsider that approach whenever we invent an explicit concept of partitioned tables, since presumably the partitions would all be essentially alike. In the meantime, the best advice I can come up with is to reconsider whether you need so many partitions. That mechanism is really designed for only a dozen or two partitions at most. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PHP SQL Color Syntax that is Postgresql & GPL3 Compatible?
I took a look. The syntax highlighting in phpPgAdmin is nicely self-contained in one file, and BSD-licensed. Unfortunately the actual highlighting is less advanced than some others. (I've gotten spoiled by vim, but suspect it's not written in PHP!) Now I'm looking at a couple of javascript options with good highlighting instead. Thanks for the suggestion. Ken On Wed, Apr 25, 2012 at 4:55 AM, John DeSoi wrote: > On Apr 25, 2012, at 6:57 AM, Ken Tanzer wrote: > > > Hi. I'm looking for an Open Source PHP code that will take plain text > SQL and turn it into colorful HTML. If it could take messy code and clean > up indents and such (a la SQLinForm), that would be a nice bonus. Ideally > it would understand many flavors of SQL, but handling Postgresql syntax is > most important. I want to include this in my own project, so it needs to > be redistributable and specifically GPL3 compatible. Again ideally, it > would be a standalone and fairly lightweight piece of code, rather than > having to embed some monster package. > > > > Does anyone know of such a beast? TIA for any help provided. > > > Not stand alone, but the phpPgAdmin project colors SQL/PostgreSQL code and > I believe the license is GPL. > > http://phppgadmin.sourceforge.net/doku.php?id=start > > > > > > John DeSoi, Ph.D. > > > > > -- AGENCY Software A data system that puts you in control *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801
Re: [GENERAL] PHP SQL Color Syntax that is Postgresql & GPL3 Compatible?
On Apr 26, 2012, at 11:23 PM, Ken Tanzer wrote: > I took a look. The syntax highlighting in phpPgAdmin is nicely > self-contained in one file, and BSD-licensed. Unfortunately the actual > highlighting is less advanced than some others. (I've gotten spoiled by vim, > but suspect it's not written in PHP!) Now I'm looking at a couple of > javascript options with good highlighting instead. I'm not sure what your ultimate goal is, but here are a couple of open source projects where I have implemented PostgreSQL syntax coloring. PHP/TextMate: https://github.com/desoi/pgedit-textmate Javascript/Ace: https://github.com/desoi/ace John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
