Re: [GENERAL] File System backup
On 06/22/2013 10:32 PM, itishree sukla wrote: yes, when i tried to start, postgresql service using init.d its gave me the error removed stale pid, postgresql failed to start. What is the actual error message? Regards, Itishree -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore order and check constraints
Hi, I recently added a check constraint onto a table in my database, that uses a stored procedure to check one of the inserted columns against the data of another table. I know that this is stretching the limit of what a check constraint is meant to be, but is there a way, short of editing the pg_restore list manually every time, to guarantee that the table used for validation is populated before the table with the data being validated? Right now it is restoring out of order, and the table is not getting populated correctly. if I have to rewrite as a trigger, I will do that, but I like the check constraint because it checks all of the entries when it's applied. Any suggestions? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle
Re: [GENERAL] I want to make an example of using parameterized path
=?UTF-8?B?6auY5YGl?= writes: >>> So I think that the above example can not show that "parameterized path" >>> has been created. >> But if you try the PREPAREd sets in versions before 9.2, you will find >> they use the same plan as each other. Allowing them to differ based on the >> parameter they are executed with, just like the non-PREPARE ones differ, is >> what parameterized paths is all about. No, actually, parameterized paths have nothing to do with parameterized queries. Here's a trivial example: regression=# create table sml as select generate_series(1,100,10) as x; SELECT 10 regression=# analyze sml; ANALYZE regression=# create table big as select generate_series(1,100) as y; SELECT 100 regression=# alter table big add primary key(y); ALTER TABLE regression=# analyze big; ANALYZE regression=# explain select * from sml, big where x=y; QUERY PLAN --- Nested Loop (cost=0.42..85.65 rows=10 width=8) -> Seq Scan on sml (cost=0.00..1.10 rows=10 width=4) -> Index Only Scan using big_pkey on big (cost=0.42..8.45 rows=1 width=4) Index Cond: (y = sml.x) (4 rows) The indexscan on "big" is a parameterized path (or was when it was still inside the planner, anyway). It's parameterized by "sml.x", which is a value that is not available from the "big" table so it has to be passed in from the current outer row of a nestloop join. Now, pre-9.2 PG versions were perfectly capable of generating plans that looked just like that one, but the planner's method for doing so was a lot more ad-hoc back then. The main practical benefit that we got from the parameterized-path rewrite is that the planner can now generate plans that require pushing an outer-row value down through more than one level of join. For instance, consider this rather artificial example: regression=# explain select * from sml left join (sml s2 join big on s2.x <= y) on big.y = sml.x; QUERY PLAN - Nested Loop Left Join (cost=0.42..98.08 rows=33 width=12) -> Seq Scan on sml (cost=0.00..1.10 rows=10 width=4) -> Nested Loop (cost=0.42..9.67 rows=3 width=8) Join Filter: (s2.x <= big.y) -> Index Only Scan using big_pkey on big (cost=0.42..8.44 rows=1 width=4) Index Cond: (y = sml.x) -> Seq Scan on sml s2 (cost=0.00..1.10 rows=10 width=4) (7 rows) The joins have to be done in that order because the leftjoin and inner join don't commute. So "sml.x" is being passed down through the inner nestloop join. Pre-9.2 could not have found that plan, and would have had to do something involving a full-table scan of "big". 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] Postgres DB crashing
Alan Hodgson writes: > On Thursday, June 20, 2013 07:52:21 AM Merlin Moncure wrote: >> OP needs to explore use of connection pooler, in particular pgbouncer. >> Anyways none of this explains why the server is actually crashing. > It might be hitting file descriptor limits. I didn't dig into the earlier > part > of this thread much, though. The disturbing part of the original report was this: >>> 2013-06-11 16:54:14 GMT [6]: [1-1]PANIC: stuck spinlock >>> (0x2aaab54279d4) detected at bufmgr.c:1239 which implies that something was holding a buffer header spinlock for an unreasonably long time (roughly 2 minutes, when no operation that holds such a lock should take more than a few nanoseconds). But if you were running a load test that absolutely mashed the machine into the ground, as the OP seems to have been doing, maybe that could happen --- perhaps some unlucky backend got interrupted and then swapped out during the narrow window where it held such a lock, and the machine was too overloaded to give that process any more cycles for a very long time. As has been noted already, this test setup seems to have overloaded the machine by at least two orders of magnitude compared to useful settings for the available hardware. The "stuck spinlock" error would only come out if a lock had been held for quite a lot more than two orders of magnitude more time than expected, though. So I'm not entirely sure that I buy this theory; but it's hard to see another one. (I discount the obvious other theory that there's a software bug, because I just looked through 9.2's bufmgr.c very carefully, and there are no code paths where it fails to release a buffer header lock within a very few instructions from where it took the lock.) 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] pg_restore order and check constraints
El 23/06/13 13:34, Moshe Jacobson escribió: Hi, I recently added a check constraint onto a table in my database, that uses a stored procedure to check one of the inserted columns against the data of another table. Is it possible to see the function? I know that this is stretching the limit of what a check constraint is meant to be, but is there a way, short of editing the pg_restore list manually every time, to guarantee that the table used for validation is populated before the table with the data being validated? What for? If the dumps actually are taken without contraints, data restored (much faster as no constraints have to be checked, and just then constraints are added via ALTER TABLE. Right now it is restoring out of order, and the table is not getting populated correctly. Why not? if I have to rewrite as a trigger, I will do that, but I like the check constraint because it checks all of the entries when it's applied. Any suggestions? We'd need to see how the constraint is added, and the function you say is used for checking the constrants. -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] pg_restore order and check constraints
On Sun, Jun 23, 2013 at 5:04 PM, Martín Marqués wrote: > Is it possible to see the function? Yes -- It checks that the given vendor has the given vendor_type by calling fn_get_vendor_types_by_vendor(), which gets its data from another table, tb_vendor_vendor_type (a join table between tb_vendor and tb_vendor_type): CREATE OR REPLACE FUNCTION public.fn_vendor_has_vendor_type(in_vendor integer, in_vendor_type integer) RETURNS boolean LANGUAGE plpgsql STABLE STRICT AS $function$ BEGIN IF in_vendor_type IN( SELECT fn_get_vendor_types_by_vendor( in_vendor ) )THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END $function$ I've installed this function on tb_project_vendor, which has a vendor_type column: ALTER TABLE tb_project_vendor ADD CONSTRAINT "ck_project_vendor_has_vendor_type" CHECK( fn_vendor_has_vendor_type( vendor, vendor_type ) ); So when the data for tb_project_vendor is restored before the data for tb_vendor_vendor_type, I get errors on restore. I know that this is stretching the limit of what a check constraint is > > meant to be, but is there a way, short of editing the pg_restore list >> manually every time, to guarantee that the table used for validation is >> populated before the table with the data being validated? >> > > What for? If the dumps actually are taken without contraints, data > restored (much faster as no constraints have to be checked, and just then > constraints are added via ALTER TABLE. So you suggest I use a trigger instead of a constraint? Thanks -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle
[GENERAL] Strict mode in postgresql??
Hi, Is there any settings in postgresql version 9.2 similar to mysql strict mode? I need to get rid of some type casting errors in the upgrading process from version 7.4 to 9.2, if this mode is not available I have to modify queries in the system to make it work... Thanks in Advance, Arun
Re: [GENERAL] Strict mode in postgresql??
Arun P.L wrote: > Hi, > > Is there any settings in postgresql version 9.2 similar to mysql strict mode? > I > need to get rid of some type casting errors in the upgrading process from > version 7.4 to 9.2, if this mode is not available I have to modify queries in > the system to make it work... No, there isn't such an option. If you now on 9.2 and there are problems with missings casts, you can recreate that, read http://petereisentraut.blogspot.de/2008/03/readding-implicit-casts-in-postgresql.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] pg_restore order and check constraints
On Jun 24, 2013, at 3:47, Moshe Jacobson wrote: > On Sun, Jun 23, 2013 at 5:04 PM, Martín Marqués > wrote: > Is it possible to see the function? > > Yes -- It checks that the given vendor has the given vendor_type by calling > fn_get_vendor_types_by_vendor(), which gets its data from another table, > tb_vendor_vendor_type (a join table between tb_vendor and tb_vendor_type): > > CREATE OR REPLACE FUNCTION public.fn_vendor_has_vendor_type(in_vendor > integer, in_vendor_type integer) > RETURNS boolean > LANGUAGE plpgsql > STABLE STRICT > AS $function$ > BEGIN > IF in_vendor_type IN( SELECT fn_get_vendor_types_by_vendor( in_vendor ) > )THEN > RETURN TRUE; > ELSE > RETURN FALSE; > END IF; > END > $function$ Isn't that just an FK constraint on (in_vendor, in_vendor_type) ? The vendor must be related to a vendor_type somewhere, and since they're in separate tables they're probably even unique. Sounds like an FK constraint would do the job. Back to the original question; You're hiding for the planner that there is a relation to another table by using plpgsql. That's why pg_dump/restore don't know that these tables need to be restored in a specific order. That said, I was under the impression that constraints are implemented as triggers to begin with, so I'm a bit surprised that the constraint is causing you issues on restore. As Martin suggests, if you put the entire check constraint as a trigger on the table, then the trigger will be added after the table has been created and copied to (with an ALTER TABLE) and the rows inside don't get checked by the trigger function and thus the "constraint" won't fire too early. It's a choice between abusing a check constraint for something it wasn't entirely meant for or using a trigger. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
