Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert
On Mon, Jan 19, 2009 at 12:53 AM, Grzegorz Jaśkiewicz wrote: > 2009/1/19 Scott Marlowe : >> Submit a patch. :) >> >> But seriously, it's doing what you told it to do. There might be >> corner cases where you need a trigger to fire for a row on change, and >> short-circuiting could cause things to fail in unexpected ways. > > as far as my little knowledge about pg goes, that would be just > another addition to planner. Say - when there's more > than X % of value Y, and we do set column X to Y, it could add that > 'where'. But what if we have more WHERE statements, and they are quite > contradictory, etc, etc. It could actually do more damage than good. > (yes, I do have quite few more 'against' than for) Yes, but what about a table with an update trigger on it that does some interesting bit of housekeeping when rows are updated? It might be that you have ten rows, all with the number 4 in them, and you update the same field again to 4. With the trigger some other processing gets kicked off and some maintenance script picks up those values and does something. If the db autoshort-circuited like you want, the trigger would never fire. According to the strictest interpretation, setting a value from 4 to 4 is still a change. But the database just changed the rules underneath you. It's a prime example of fixing a problem created by not knowing how the database works, and creating a possible problem for people who do know how it works. -- 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] left join with smaller table or index on (XXX is not null) to avoid upsert
2009/1/19 Scott Marlowe : > Yes, but what about a table with an update trigger on it that does > some interesting bit of housekeeping when rows are updated? exactly, that's another one of reasons why I wouldn't write that patch :P > It's a prime example of fixing a problem created by not knowing how > the database works, and creating a possible problem for people who do > know how it works. Like I said, I was just daydreaming right after getting out of bed. Forgive me, also - I do know how it works, but it is interesting to explore such options sometimes - to learn that the simple design of db is the best possible :) -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] too smart update (was: left join with smaller table or index on (XXX is not null) to avoid upsert)
On Mon, 19 Jan 2009 01:18:35 -0700 "Scott Marlowe" wrote: > On Mon, Jan 19, 2009 at 12:53 AM, Grzegorz Jaśkiewicz > wrote: > > 2009/1/19 Scott Marlowe : > >> Submit a patch. :) > >> > >> But seriously, it's doing what you told it to do. There might be > >> corner cases where you need a trigger to fire for a row on > >> change, and short-circuiting could cause things to fail in > >> unexpected ways. > > as far as my little knowledge about pg goes, that would be just > > another addition to planner. Say - when there's > > more than X % of value Y, and we do set column X to Y, it could > > add that 'where'. But what if we have more WHERE statements, and > > they are quite contradictory, etc, etc. It could actually do > > more damage than good. (yes, I do have quite few more 'against' > > than for) > Yes, but what about a table with an update trigger on it that does > some interesting bit of housekeeping when rows are updated? It > might be that you have ten rows, all with the number 4 in them, > and you update the same field again to 4. With the trigger some But what should be the expected/standard behaviour? It seems that unless an update should fire triggers just if columns get updated... things will start to be a bit non-deterministic. You'll have to take into account rules etc... eg. FOUND is set true when conditions are met, not when columns are changed etc... -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] left join with smaller table or index on (XXX is not null) to avoid upsert
On Sun, 18 Jan 2009 19:44:40 -0700 "Scott Marlowe" wrote: > You could update returning rowsupdated, so you could run that and > get a list of all the rows that were updated. Then build a simple > select where not in (those rows) to get the rest for inserting. uh nice addition. I didn't check all the goodies I got when I moved from 8.1 to 8.3. I mostly was interested in tsearch. Still while it makes nearly trivial to write upsert it looks like it will still make the server sweat compared to MySQL REPLACE. In postgresql I could write a rule, but it will be globally defined and it is a much permanent solution than using an upsert (aka REPLACE) on a statement basis. > > I'm expecting that: > > - ProductPrice will contain roughly but less than 10% of the > > catalogue. > Then an index will only help when you're selecting on something > more selective. unless your rows are really skinny, a sequential > scan will usually win over an index scan. They should be very skinny. create table ProductPrice( ProductID int references Product (ProductID), DiscountedPrice numeric(4,2) ); > > Since I haven't been able to find a quick way to build up a > > hierarchy of promotions to apply/re-apply discounts when > > promotion are added/deleted, creating/deleting promotions looks > > critical as well. > > The best thing I was able to plan was just to reapply all > > promotions if one is deleted. > Watch out for bloat when doing this. A simple where change of > update table set b = 45 ; > to > update table set b = 45 where b <> 45 ; > can save the db a lot of work, and if you can apply the same logic > to your update to save some dead tuples it's worth looking into. > Updating whole tables wholesale is not definitely not pgsql's > strong suit. oh that's really a good suggestion since just a higher discount have to be applied. I just have to understand how to apply it. If I actually have all the rows everything will be an update and I can actually exploit your suggestion. update ProductPrices set DiscountedPrice=round(q.Price*Discount,2) from somefunction() q where ProductPrices.ProductID=q.ProductID and ProductPrices.DiscountedPrice>round(q.Price*Discount,2); If I have to "upsert" most of the advantage of reducing the # of updates with an additional condition seems to be lost. > > So it looks to me that approach B is going to make updating of > > discounts easier, but I was wondering if it makes retrieval of > > Products and Prices slower. > > If you do bulk updates, you'll blow out your tables if you don't > keep them vacuumed. 50% dead space is manageable, if your data > set is reasonably small (under a few hundred meg). Just make sure > you don't run 20 updates on a table in a row, that kind of thing. That's going exactly to be the case. If I can't easily spot intersections between promotions, and I doubt I can in a cheap way, I'll have to run 20 to 100 updates every time I delete a promotion. Set the DiscountedPrice to null for every ProductID I'm going to delete from the promotion and reapply all the promotions. But well maybe you helped me to find another approach. Since I'm going to reapply all promotions I could: - delete whole table - insert prices starting from the promotions with higher discount - skip on failed insert since other promotions will have same or lower discount. So I'll have just the rows I need in ProductPrice table, no need to index on is not null, smaller table so faster to left join and keep it in memory. But... well how am I going to: -- Discount=40 insert into ProductPrice from ( select ProductID, round(q.Price*Discount,2) from mypromofunction(...) ); -- Discount=40 insert into ProductPrice from ( select ProductID, round(q.Price*Discount,2) from mypromofunction(...) ); -- Discount=30 insert into ProductPrice from ( select ProductID, round(q.Price*Discount,2) from mypromofunction(...) ); OK one more approach: create table ProductPromoPrice( PromoID references Promo (PromoID) on delete cascade, ProductID references Product (ProductID on delete cascade, DiscountedPrice numeric ); create table ProductPrice( ProductID references Product (ProductID) on delete cascade, DiscountedPrice numeric ); insert into ProductPromoPrice from select PromoID, ProductID, round(q.Price*Discount,2) from mypromofunction(...) ); ... [1] insert into ProductPrice select ProductID, min(DiscountedPrice) from ProductPromoPrice group by ProductID; I just did: create table test.Prices(ItemID int, Price real); insert into test.Prices select BrandID, max(ListPrice) from catalog_items group by BrandID; took 1sec insert into test.Prices select ProductID, ListPrice from catalog_items; took 4 sec If I'm expecting that - discounted articles may be 10% of the whole catalogue - a maximum overlap of 60%. - large overlap it may involve a small # of promotions - no more than 100 promotions what execution time should I expect from query [1]? Should an index on ProductProm
[GENERAL] How can I display the contents of a function?
I would like to see what's in a function. I can do this if I use pgAdmin III just by clicking on the function, and it appears in the SQL pane opposite. But how is this done? Is there a command like DESCRIBE FUNCTION logging.do_stuff; ? And if possible, doing this without psql. Thanks Thom
Re: [GENERAL] How can I display the contents of a function?
Thom Brown a écrit : > I would like to see what's in a function. I can do this if I use pgAdmin > III just by clicking on the function, and it appears in the SQL pane > opposite. But how is this done? Is there a command like DESCRIBE FUNCTION > logging.do_stuff; ? And if possible, doing this without psql. > pgAdmin looks at the prosrc column of the pg_proc catalog. You can do the same with this query: SELECT prosrc FROM pg_proc WHERE proname='your function name here'; Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- 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 can I display the contents of a function?
In response to Thom Brown : > I would like to see what's in a function. I can do this if I use pgAdmin III > just by clicking on the function, and it appears in the SQL pane opposite. > But > how is this done? Is there a command like DESCRIBE FUNCTION logging.do_stuff; > ? And if possible, doing this without psql. All details about a function are stored in pg_proc. So you can do a select on this table to retrieve the source-code. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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 can I display the contents of a function?
Thom Brown wrote: I would like to see what's in a function. I can do this if I use pgAdmin III just by clicking on the function, and it appears in the SQL pane opposite. But how is this done? Is there a command like DESCRIBE FUNCTION logging.do_stuff; ? And if possible, doing this without psql. Thanks Thom You can do \df+ function_name(arg type, arg type...) in psql -- 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 can I display the contents of a function?
Thanks everyone! I looked around the user-defined functions section of the documentation, but there wasn't any mention of this. I'm sure others would find it useful if it were included, or at least referenced to. Thom 2009/1/19 A. Kretschmer > In response to Thom Brown : > > I would like to see what's in a function. I can do this if I use pgAdmin > III > > just by clicking on the function, and it appears in the SQL pane > opposite. But > > how is this done? Is there a command like DESCRIBE FUNCTION > logging.do_stuff; > > ? And if possible, doing this without psql. > > All details about a function are stored in pg_proc. So you can do a > select on this table to retrieve the source-code. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] How can I look at a recursive table dependency tree?
I want to DROP CASCADE a table, but I am afraid that amoung numerous recursive dependencies there will be smth, that I don't want to drop. Is there a way to watch all dependencies recursively without doing a drop? -- 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 can I display the contents of a function?
Thom Brown wrote: I would like to see what's in a function. I can do this if I use pgAdmin III just by clicking on the function, and it appears in the SQL pane opposite. But how is this done? Is there a command like DESCRIBE FUNCTION logging.do_stuff; ? And if possible, doing this without psql. Thanks Thom You can also do \df+ function_name(args) in psql -- 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 can I display the contents of a function?
In response to Thom Brown :
> Thanks everyone! I looked around the user-defined functions section of the
> documentation, but there wasn't any mention of this. I'm sure others would
> find it useful if it were included, or at least referenced to.
Just for info:
wait for the upcoming new release 8.4, it contains a function called
pg_get_functiondef(). Example:
test=# create or replace function foo(in a int) returns int as $$begin return
a*10; end; $$language plpgsql;
CREATE FUNCTION
test=# select pg_get_functiondef('foo'::regproc);
pg_get_functiondef
--
CREATE OR REPLACE FUNCTION public.foo(a integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$begin return a*10; end; $function$
(1 row)
http://developer.postgresql.org/pgdocs/postgres/functions-info.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
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 can I display the contents of a function?
Another reason to look forward to 8.4. :)
Does that function return information in an identical way to selecting from
pg_proc?
2009/1/19 A. Kretschmer
> In response to Thom Brown :
> > Thanks everyone! I looked around the user-defined functions section of
> the
> > documentation, but there wasn't any mention of this. I'm sure others
> would
> > find it useful if it were included, or at least referenced to.
>
> Just for info:
>
> wait for the upcoming new release 8.4, it contains a function called
> pg_get_functiondef(). Example:
>
> test=# create or replace function foo(in a int) returns int as $$begin
> return a*10; end; $$language plpgsql;
> CREATE FUNCTION
> test=# select pg_get_functiondef('foo'::regproc);
>pg_get_functiondef
> --
> CREATE OR REPLACE FUNCTION public.foo(a integer)
> RETURNS integer
> LANGUAGE plpgsql
> AS $function$begin return a*10; end; $function$
>
> (1 row)
>
>
> http://developer.postgresql.org/pgdocs/postgres/functions-info.html
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> 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 can I display the contents of a function?
In response to Thom Brown : > Another reason to look forward to 8.4. :) > > Does that function return information in an identical way to selecting from > pg_proc? Apparently. > > 2009/1/19 A. Kretschmer Please no fullquote below your text, thx. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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 can I look at a recursive table dependency tree?
Igor Katson wrote: > I want to DROP CASCADE a table, but I am afraid that amoung numerous > recursive dependencies there will be smth, that I don't want to drop. > > Is there a way to watch all dependencies recursively without doing a drop? BEGIN; DROP CASCADE... -- check things ROLLBACK; -- Richard Huxton Archonet Ltd -- 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 can I look at a recursive table dependency tree?
On Mon, 19 Jan 2009 14:19:51 + Richard Huxton wrote: > Igor Katson wrote: > > I want to DROP CASCADE a table, but I am afraid that amoung > > numerous recursive dependencies there will be smth, that I don't > > want to drop. > > > > Is there a way to watch all dependencies recursively without > > doing a drop? > BEGIN; > DROP CASCADE... > -- check things > ROLLBACK; Isn't it going to be a pretty expensive way to see? Is the default log level enough to take note of the things that will be touched? (including eg. sequences, indexes etc...)? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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 can I look at a recursive table dependency tree?
2009/1/19 Igor Katson > I want to DROP CASCADE a table, but I am afraid that amoung numerous > recursive dependencies there will be smth, that I don't want to drop. > > Is there a way to watch all dependencies recursively without doing a drop? you could query the pg_depend system catalog: http://www.postgresql.org/docs/8.3/static/catalog-pg-depend.html but regarding recursive queries, you have to wait for 8.4 :) -- Filip Rembiałkowski
Re: [GENERAL] How can I look at a recursive table dependency tree?
In response to Igor Katson : > I want to DROP CASCADE a table, but I am afraid that amoung numerous > recursive dependencies there will be smth, that I don't want to drop. > > Is there a way to watch all dependencies recursively without doing a drop? You can walk through pg_depend. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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 can I look at a recursive table dependency tree?
Ivan Sergio Borgonovo wrote: > On Mon, 19 Jan 2009 14:19:51 + > Richard Huxton wrote: > >> Igor Katson wrote: >>> Is there a way to watch all dependencies recursively without >>> doing a drop? > >> BEGIN; >> DROP CASCADE... >> -- check things >> ROLLBACK; > > Isn't it going to be a pretty expensive way to see? Not necessarily - you're not likely to have a lot of concurrency on a backup database. And we are all testing this sort of stuff on a backup database, aren't we? > Is the default log level enough to take note of the things that will > be touched? (including eg. sequences, indexes etc...)? I don't think so. You could pull the information from pg_depend (http://www.postgresql.org/docs/8.3/static/catalog-pg-depend.html) which is what the DROP will be doing. -- Richard Huxton Archonet Ltd -- 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 can I look at a recursive table dependency tree?
On Mon, 19 Jan 2009 14:41:12 + Richard Huxton wrote: > Ivan Sergio Borgonovo wrote: > > On Mon, 19 Jan 2009 14:19:51 + > > Richard Huxton wrote: > > > >> Igor Katson wrote: > >>> Is there a way to watch all dependencies recursively without > >>> doing a drop? > > > >> BEGIN; > >> DROP CASCADE... > >> -- check things > >> ROLLBACK; > > > > Isn't it going to be a pretty expensive way to see? > Not necessarily - you're not likely to have a lot of concurrency > on a backup database. And we are all testing this sort of stuff on > a backup database, aren't we? Isn't it going to be expensive even if there is no concurrency? mvcc should be pretty efficient to rollback transactions but... well it should have a cost anyway... and you add deleting to rolling back, not just traversing some schema somehow. Surely recursively traversing a schema may be expensive in termos of programming time if there is no pre-build function. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] left join with smaller table or index on (XXX is not null) to avoid upsert
"Scott Marlowe" writes: > But seriously, it's doing what you told it to do. There might be > corner cases where you need a trigger to fire for a row on change, and > short-circuiting could cause things to fail in unexpected ways. The other argument against doing this by default is that with non-stupidly-written applications, the cycles expended to check for vacuous updates would invariably be wasted. Even if the case did come up occasionally, it's not hard at all to foresee that the extra checking could be a net loss overall. But having said that: 8.4 will provide a standard trigger that short-circuits vacuous updates, which you can apply to tables in which you think vacuous updates are likely. It's your responsibility to place the trigger so that it doesn't interfere with any other trigger processing you may have. 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] left join with smaller table or index on (XXX is not null) to avoid upsert
On Mon, Jan 19, 2009 at 4:43 PM, Tom Lane wrote: > But having said that: 8.4 will provide a standard trigger that > short-circuits vacuous updates, which you can apply to tables in which > you think vacuous updates are likely. It's your responsibility to place > the trigger so that it doesn't interfere with any other trigger > processing you may have. Tom, Can you point us to http://developer.postgresql.org/pgdocs/postgres/ where it is described in more detail ? -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] number of connections
Hi! First of all, excuse my english... I have a doubt. I am designing a program for manage CV's. This program connect with a PostgresDB. This program will be used for 5,000 persons becaus it will be used in a University. Then, ¿how many actives connection could be postgres? ¿does it support a very big cuantity of information? Possibly, in the future, the program will grow up with more information and more users. ¿can i use in this case postgres?¿has it a good scalability? Thank you. Ruben FS
Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert
On Mon, Jan 19, 2009 at 09:48, Grzegorz Jaśkiewicz wrote: > On Mon, Jan 19, 2009 at 4:43 PM, Tom Lane wrote: >> But having said that: 8.4 will provide a standard trigger that >> short-circuits vacuous updates, which you can apply to tables in which >> you think vacuous updates are likely. It's your responsibility to place >> the trigger so that it doesn't interfere with any other trigger >> processing you may have. > > Tom, Can you point us to > http://developer.postgresql.org/pgdocs/postgres/ where it is described > in more detail ? I assume he is talking about suppress_redundant_updates_trigger, see http://developer.postgresql.org/pgdocs/postgres/functions-trigger.html -- 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] number of connections
On Mon, Jan 19, 2009 at 12:10 PM, Rubén F. wrote: > First of all, excuse my english... > > I have a doubt. I am designing a program for manage CV's. This program > connect with a PostgresDB. This program will be used for 5,000 persons > becaus it will be used in a University. Then, ¿how many actives connection > could be postgres? ¿does it support a very big cuantity of information? > > Possibly, in the future, the program will grow up with more information and > more users. ¿can i use in this case postgres?¿has it a good scalability? We have database servers where the PostgreSQL instances are configured to support on the order of a thousand concurrent connections, and definitely have hundreds active at a time fairly frequently, so that kind of scalability is certainly possible. To support that, we have rather powerful hardware; that's not running on "a cheap PC with an IDE disk drive." It would actually be pretty unusual for the sort of application you are describing to actually require thousands of concurrent connections. There may be thousands of users accessing the application, but the database access is likely to be sporadic. Web application frameworks typically offer support for "connection pools" so that a much smaller number of actual database connections is used to support a large number of users. As for quantity of data, any respectable database can store very large quantities of data. "Many gigabytes" should be easy. Terabytes are where the challenges begin, but there certainly are organizations using PostgreSQL to support terabyte-sized databases. -- http://linuxfinances.info/info/linuxdistributions.html Katharine Hepburn - "Death will be a great relief. No more interviews." -- 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 can I look at a recursive table dependency tree?
Ivan Sergio Borgonovo wrote: > On Mon, 19 Jan 2009 14:41:12 + > Richard Huxton wrote: > >> Ivan Sergio Borgonovo wrote: >>> On Mon, 19 Jan 2009 14:19:51 + >>> Richard Huxton wrote: >>> Igor Katson wrote: > Is there a way to watch all dependencies recursively without > doing a drop? BEGIN; DROP CASCADE... -- check things ROLLBACK; >>> Isn't it going to be a pretty expensive way to see? > >> Not necessarily - you're not likely to have a lot of concurrency >> on a backup database. And we are all testing this sort of stuff on >> a backup database, aren't we? > > Isn't it going to be expensive even if there is no concurrency? Not particularly. If you DELETE a lot of rows that can be expensive, but dropping a table doesn't need to track each record individually. > mvcc should be pretty efficient to rollback transactions but... well > it should have a cost anyway... and you add deleting to rolling > back, not just traversing some schema somehow. No deletion, just removes the table (and its indexes) from catalogues and deletes the relevant file(s) on commit. > Surely recursively traversing a schema may be expensive in termos of > programming time if there is no pre-build function. Well, it shouldn't take more than an hour or so to write and test a function. Never done so myself, since I tend to know what my schemas look like. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Res: [GENERAL] How can I look at a recursive table dependency tree?
In postgresql 8.2 ,how find it? De: Richard Huxton Para: Ivan Sergio Borgonovo Cc: pgsql-general@postgresql.org Enviadas: Segunda-feira, 19 de Janeiro de 2009 14:36:46 Assunto: Re: [GENERAL] How can I look at a recursive table dependency tree? Ivan Sergio Borgonovo wrote: > On Mon, 19 Jan 2009 14:41:12 + > Richard Huxton wrote: > >> Ivan Sergio Borgonovo wrote: >>> On Mon, 19 Jan 2009 14:19:51 + >>> Richard Huxton wrote: >>> Igor Katson wrote: > Is there a way to watch all dependencies recursively without > doing a drop? BEGIN; DROP CASCADE... -- check things ROLLBACK; >>> Isn't it going to be a pretty expensive way to see? > >> Not necessarily - you're not likely to have a lot of concurrency >> on a backup database. And we are all testing this sort of stuff on >> a backup database, aren't we? > > Isn't it going to be expensive even if there is no concurrency? Not particularly. If you DELETE a lot of rows that can be expensive, but dropping a table doesn't need to track each record individually. > mvcc should be pretty efficient to rollback transactions but... well > it should have a cost anyway... and you add deleting to rolling > back, not just traversing some schema somehow. No deletion, just removes the table (and its indexes) from catalogues and deletes the relevant file(s) on commit. > Surely recursively traversing a schema may be expensive in termos of > programming time if there is no pre-build function. Well, it shouldn't take more than an hour or so to write and test a function. Never done so myself, since I tend to know what my schemas look like. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Re: [GENERAL] array_agg for 8.3
Hi Jeff, On Sun, 18 Jan 2009, Jeff Davis wrote: On Sun, 2009-01-18 at 16:52 -0500, Faheem Mitha wrote: Hi Jeff, When I try to run array_agg.sql inside psql I get btsnp_test=# \i '/tmp/array_agg/array_agg.sql' BEGIN psql:/tmp/array_agg/array_agg.sql:5: ERROR: could not access file "$libdir/array_agg": No such file or directory What that means is that it's not really installed into the global postgresql instance. What did you do to install it? I haven't installed it anywhere. It is trying to install to the system, which is a no-no. fah...@orwell:/tmp/array_agg$ make install mkdir -p -- /usr/share/postgresql/8.3/contrib mkdir: cannot create directory `/usr/share/postgresql/8.3/contrib': Permission denied make: *** [installdirs] Error 1 In any case, I don't have admin permissions on the machine I'm trying to install it to. I replaced '$libdir/array_agg' in the following text by the current location of the shared library on the machine, namely '/tmp/array_agg/array_agg', since the shared library file is /tmp/array_agg/array_agg.so. From the documentation, it sounds like '/tmp/array_agg/array_agg.so' would also work. "CREATE OR REPLACE FUNCTION UNNEST(ANYARRAY) RETURNS SETOF anyelement AS '$libdir/array_agg','array_unnest' LANGUAGE 'C' IMMUTABLE;" This appears to work. I get btsnp_test=# \i array_agg.sql BEGIN CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE AGGREGATE UPDATE 1 UPDATE 1 UPDATE 1 UPDATE 1 COMMIT The test code you have in README now runs. Is that Ok? If there is a better approach to this, please let me know. Make sure you have pg_config in your PATH environment variable, change directory to /tmp/array_agg, and then run "make install". That should install it in the global postgresql instance, and then you can run the SQL file to install it in the specific database. Thanks very much for your help. Regards, Faheem. array_agg.sql BEGIN; CREATE OR REPLACE FUNCTION UNNEST(ANYARRAY) RETURNS SETOF anyelement AS '$libdir/array_agg','array_unnest' LANGUAGE 'C' IMMUTABLE; CREATE OR REPLACE FUNCTION ARRAY_AGG_TRANSFN(INT, ANYELEMENT) RETURNS INT AS '$libdir/array_agg' LANGUAGE 'C' IMMUTABLE; CREATE OR REPLACE FUNCTION ARRAY_AGG_FINALFN(ANYELEMENT) RETURNS ANYARRAY AS '$libdir/array_agg' LANGUAGE 'C' IMMUTABLE; CREATE AGGREGATE ARRAY_AGG(anyelement) ( SFUNC = ARRAY_AGG_TRANSFN, STYPE = INT, FINALFUNC = ARRAY_AGG_FINALFN ); -- -- We need to properly set the state type for array_agg to be -- "internal", but that's impossible with regular SQL. So, we make the -- changes in the catalog directly. -- UPDATE pg_aggregate SET aggtranstype = 2281 WHERE aggfnoid = 'array_agg'::regproc; UPDATE pg_proc SET prorettype = 2277 WHERE oid = 'array_agg'::regproc; UPDATE pg_proc SET prorettype = 2281, proargtypes = '2281 2283' WHERE oid = 'array_agg_transfn'::regproc; UPDATE pg_proc SET proargtypes = '2281' WHERE oid = 'array_agg_finalfn'::regproc; COMMIT; -- 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] array_agg for 8.3
On Mon, 19 Jan 2009, Jeff Davis wrote: On Mon, 2009-01-19 at 13:40 -0500, Faheem Mitha wrote: In any case, I don't have admin permissions on the machine I'm trying to install it to. At absolute minimum, you need PostgreSQL superuser privileges. If you don't, you need to set up a new PostgreSQL instance (which you can do as a normal non-root user), and then you will have superuser privileges for your own instance. If you do have PostgreSQL superuser privileges, but not root on the machine, you can install the module to some other location by changing some paths around, although that might be slightly ugly. Hi Jeff, Yes, I have pg superuser privileges. I *think* this is the same as having sudo rights for postgres, right? Are you suggesting adding another entry to the library path, or whatever this is called? Like ~/.postgresql/ or something like that? To be clear, even if I have admin on the machine, it is generally undesirable to install unpackaged software to the machine, so I'd have to package it first. I guess that is an option if I find I am using it a lot. Is there any major downside to the way I'm doing it, as described in my last message? Thanks again for your help. Regards, Faheem. -- 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] array_agg for 8.3
On Mon, 2009-01-19 at 13:40 -0500, Faheem Mitha wrote: > In any case, I don't have admin permissions on the machine I'm trying to > install it to. At absolute minimum, you need PostgreSQL superuser privileges. If you don't, you need to set up a new PostgreSQL instance (which you can do as a normal non-root user), and then you will have superuser privileges for your own instance. If you do have PostgreSQL superuser privileges, but not root on the machine, you can install the module to some other location by changing some paths around, although that might be slightly ugly. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CREATE parametric partial INDEX within a function body
Hi all. I have a maintenance PL/pgSQL function that needs to recreate a partial index (not a REINDEX, though). In the WHERE condition of the index I have one of the function arguments. A plain "CREATE INDEX ... WHERE ..." will lead to a runtime error like this: tmp2=# SELECT * FROM f_maint1( '20080401'::timestamptz ); ERROR: there is no parameter $1 CONTEXT: SQL statement "CREATE INDEX i_special_part ON t_atable( col1,col2 ) WHERE col3 >= $1 " PL/pgSQL function "f_maint1" line 28 at SQL statement To work this issue around I switched to dynamic SQL with "EXECUTE 'CREATE INDEX ...' " This means to me that the CREATE INDEX within a PL/pgSQL body cannot use function arguments in the WHERE condition. Is this the expected behavior? If so, why? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- 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] array_agg for 8.3
On Mon, 2009-01-19 at 14:09 -0500, Faheem Mitha wrote: > Yes, I have pg superuser privileges. I *think* this is the same as having > sudo rights for postgres, right? Are you suggesting adding another entry > to the library path, or whatever this is called? Like ~/.postgresql/ or > something like that? If you have sudo rights for postgres, that should be fine. All you need to do is "make" in the module, and it will produce a file array_agg.so. Copy the .so file to someplace (wherever makes the most sense for you), and then change all the paths in the .sql file to point to that .so file. Then run the .sql file as the postgresql superuser. Regards, Jeff Davis -- 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] left join with smaller table or index on (XXX is not null) to avoid upsert
On Sun, 18 Jan 2009 22:12:07 +0100 Ivan Sergio Borgonovo wrote: > I've to apply a discounts to products. > > For each promotion I've a query that select a list of products and > should apply a discount. > > Queries may have intersections, in these intersections the highest > discount should be applied. > > Since queries may be slow I decided to proxy the discount this way: Actually: premature optimization is the root of all evil (Knuth). Although I haven't reached any definitive conclusion clean design and normalisation seem paid off. A normal query to retrieve a list of products seems nearly unaffected by keeping a create table Promo ( PromoID serial primary key, PromoStart timestamp, PromoEnd timestamp, ..); and a create table PromoItem( PromoID int references Promo (PromoID) on delete cascade, ItemID int references Product (ProductID) on delete cascade, Discount numeric(4,2) not null ); and looking for max discount in a join on the fly. That's on a 1M items and on 40K products on promo. Distribution of promo was random, I'll dig further to get an idea of worst case. What's important is that a simple search over the catalogue takes nearly the same time that a query that search through the catalogue and find the appropriate discount. Thanks to Knuth and to Postgresql coders. I'll post a more detailed solution as soon as it's enough refined and if I'm sure of its correctness. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] CREATE parametric partial INDEX within a function body
On Mon, Jan 19, 2009 at 08:19:06PM +0100, Reg Me Please wrote: > Hi all. > > I have a maintenance PL/pgSQL function that needs to recreate a partial index > (not a REINDEX, though). > In the WHERE condition of the index I have one of the function arguments. > A plain "CREATE INDEX ... WHERE ..." will lead to a runtime error like this: > > tmp2=# SELECT * FROM f_maint1( '20080401'::timestamptz ); > ERROR: there is no parameter $1 > CONTEXT: SQL statement "CREATE INDEX i_special_part ON t_atable( col1,col2 ) > WHERE col3 >= $1 " > PL/pgSQL function "f_maint1" line 28 at SQL statement > > To work this issue around I switched to dynamic SQL with "EXECUTE 'CREATE > INDEX ...' " What about EXECUTE 'CREATE INDEX bla ON t_table WHERE (col >= ' || pg_catalog.quote_literal($1) || ')'; Regards, Gerhard signature.asc Description: Digital signature
[GENERAL] pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC
Greetings: I already did some searches on the "pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC" error and am still not sure why my database's not restoring after upgrading the FreeBSD and select ports such as PostGreSQL. I did see something from a long time ago about altering the table, especially column names, quite possibly being a problem and I have done a lot of that, but as many backups as I've also done this past year, this' the first time I've had to use the restore again and am now not even sure how to get my database back. If this seems familiar and simple enough for anyone, please advise including which details may be useful here. Thanks,
Re: [GENERAL] CREATE parametric partial INDEX within a function body
On Monday 19 January 2009 22:49:17 Gerhard Heift wrote: > On Mon, Jan 19, 2009 at 08:19:06PM +0100, Reg Me Please wrote: > > Hi all. > > > > I have a maintenance PL/pgSQL function that needs to recreate a partial > > index (not a REINDEX, though). > > In the WHERE condition of the index I have one of the function arguments. > > A plain "CREATE INDEX ... WHERE ..." will lead to a runtime error like > > this: > > > > tmp2=# SELECT * FROM f_maint1( '20080401'::timestamptz ); > > ERROR: there is no parameter $1 > > CONTEXT: SQL statement "CREATE INDEX i_special_part ON t_atable( > > col1,col2 ) WHERE col3 >= $1 " > > PL/pgSQL function "f_maint1" line 28 at SQL statement > > > > To work this issue around I switched to dynamic SQL with "EXECUTE 'CREATE > > INDEX ...' " > > What about > > EXECUTE 'CREATE INDEX bla ON t_table WHERE (col >= ' > > || pg_catalog.quote_literal($1) || ')'; > > Regards, > Gerhard Exactly what I did: > > To work this issue around I switched to dynamic SQL with "EXECUTE 'CREATE > > INDEX ...' " The question remains, though. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- 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] Is this on the to-do list?
Thomas Kellerer wrote: > A B wrote on 18.01.2009 22:43: > > From the docs: > > http://www.postgresql.org/docs/8.3/interactive/sql-update.html > > > > "According to the standard, the column-list syntax should allow a list > > of columns to be assigned from a single row-valued expression, such as > > a sub-select: > > UPDATE accounts SET (contact_last_name, contact_first_name) = > > (SELECT last_name, first_name FROM salesmen > > WHERE salesmen.id = accounts.sales_id); > > This is not currently implemented ? the source must be a list of > > independent expressions." > > > > Is this feature going into postgresql any day soon? :-) > > > It's on the TODO list: > > http://wiki.postgresql.org/wiki/Todo#UPDATE Also, I don't know if anyone working on this item for 8.4 or 8.5. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] conexiones ssl
Hola a todos necesito ayuda como configurar mi base de datos como aceptar conexiones ssl desde cualquier ip, mi base de datos esta instalada en Windows: En donde tengo que hacerlo: pg_hba.conf postgresql.conf Gracias Henry Interiano San Pedro Sula, Honduras _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us
[GENERAL] How to find how much postgresql use the memory?
Hi all, I wonder how to find how much postgresql memory consumption is (real, not allocated) ? thx.
Re: [GENERAL] conexiones ssl
On Tuesday 20 January 2009 03:27:14 Henry Interiano wrote: > Hola a todos > > necesito ayuda como configurar mi base de datos como aceptar conexiones ssl > desde cualquier ip, mi base de datos esta instalada en Windows: -> pgsql-es-ay...@postgresql.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
