[GENERAL] Foreign keys question (performance)
Hi. I have a foreign key as such: ALTER TABLE child_table ADD CONSTRAINT fk_child FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL ON DELETE CASCADE ; Questions: 1. Is MATCH FULL adding any value here? If the foreign key is just on an id column, what purpose does it serve? Without it, the results would be the same? Does it affect performance or should I leave it be? (Note that the id is a alphanumeric value) 2. More importantly, in this case basically the child_table cannot have any keys that the parent_table doesn't have either. Will INSERTs and UPDATEs to the parent_table be slower? Or will the foreign key check happen only when INSERT or UPDATE happen to the child_table? Thanks! -- 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] Foreign keys question (performance)
On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: Hi. I have a foreign key as such: ALTER TABLE child_table ADD CONSTRAINT fk_child FOREIGN KEY (stringid) REFERENCES parent_table (stringid) MATCH FULL ON DELETE CASCADE ; Questions: 1. Is MATCH FULL adding any value here? If the foreign key is just on an id column, what purpose does it serve? Without it, the results would be the same? Does it affect performance or should I leave it be? (Note that the id is a alphanumeric value) Nope, it is not. As I understand it, it only does something on multi-column foreign keys where parts of the key are NULL. To quote the documentation: There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented. I can't say much on the impact on performance, but I'd expect that to be negligible in this case: With the MATCH FULL in place, it will need to check whether any of your columns are NULL, but that's only a single column in your case. 2. More importantly, in this case basically the child_table cannot have any keys that the parent_table doesn't have either. Will INSERTs and UPDATEs to the parent_table be slower? Or will the foreign key check happen only when INSERT or UPDATE happen to the child_table? INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weird behavior: deleted row still in index?
Hi.
I have deleted a row from a table. Confirmed by SELECT. All
associated children tables don't have this key value either.
Yet, when I insert this row back again, the primary key index on this
table gives me a duplicate error.
As demonstrated below. PGSQL version is 9.0.5.
Is this common? I have vacuum analyzed the table three times. Still
same problem. Why is the primary key index keeping a value that was
deleted?
Short of a REINDEX (which will lock the entire tableit's a large
one) is there anything I can do to clear up the index?
Thanks!
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 0.759 ms
mydb=# INSERT INTO stores (id) VALUES ('20xrrs3');
mydb-#
ERROR: duplicate key value violates unique constraint idx_stores_pkey
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=#
--
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] Foreign keys question (performance)
On Sun, Dec 4, 2011 at 7:14 PM, Alban Hertroys haram...@gmail.com wrote: On 4 Dec 2011, at 11:19, Phoenix Kiula wrote: INSERTs in the parent table don't need to check for any reference from the child table, since they're new; there can't be a reference. UPDATEs and DELETEs do though, whether you let them CASCADE or not. If you don't, then the database raises a foreign key constraint violation. If you do, then it needs to modify the relevant rows in the child table. Likewise, INSERTs and UPDATEs in the child table need to verify that - if their reference key changed - they're still referencing a valid row. Thanks Albert. Very useful. I had ON DELETE...ALSO DELETE rules earlier and in some cases they let some keys go by in associated tables. Hope foreign key constraint is more reliable! PK -- 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] Weird behavior: deleted row still in index?
On 4 Dec 2011, at 12:32, Phoenix Kiula wrote: mydb=# delete from stores where id = '20xrrs3'; DELETE 0 Time: 0.759 ms It says it didn't delete any rows. Since you get a duplicate key violation on inserting a row to that table, there's obviously a row with that id there. Perhaps there's a DELETE trigger or rule on this table that does something unexpected? It is indeed a possibility that this is a corrupted index, but that is not something that happens unless more serious matters have been (or are) at hand, like hardware failures. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- 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] Weird behavior: deleted row still in index?
On 4 December 2011 12:32, Phoenix Kiula phoenix.ki...@gmail.com wrote:
Hi.
I have deleted a row from a table. Confirmed by SELECT. All
associated children tables don't have this key value either.
Yet, when I insert this row back again, the primary key index on this
table gives me a duplicate error.
As demonstrated below. PGSQL version is 9.0.5.
Is this common? I have vacuum analyzed the table three times. Still
same problem. Why is the primary key index keeping a value that was
deleted?
Short of a REINDEX (which will lock the entire tableit's a large
one) is there anything I can do to clear up the index?
Thanks!
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 0.759 ms
mydb=# INSERT INTO stores (id) VALUES ('20xrrs3');
mydb-#
ERROR: duplicate key value violates unique constraint idx_stores_pkey
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=#
Hi,
could you run the following queries and show us the results?
SELECT count(*) FROM stores WHERE id = '20xrrs3';
delete from stores where id = '20xrrs3';
SELECT count(*) FROM stores WHERE id = '20xrrs3';
and then show us the whole table structure, especially any rules or
triggers.
regards
Szymon
--
*http://simononsoftware.com/* http://simononsoftware.com/
Re: [GENERAL] Weird behavior: deleted row still in index?
On Sun, Dec 4, 2011 at 7:55 PM, Szymon Guz mabew...@gmail.com wrote:
.
and then show us the whole table structure, especially any rules or
triggers.
Not many rules or triggers. See below.
I ran a REINDEX on the key allegedly being violated, and it finished
it in 30 mins or so, but still the same problem:
In fact, I deleted one rule -- and maybe I cancelled it before it
finished, but it does look gone now. Could it be not entirely deleted
and maybe corrupted somewhere out of sight?
The row is surely not in the table. Below some things..
.
VACUUM
Time: 366952.162 ms
mydb=#
mydb=#
mydb=# select * from stores where id = '20xrrs3';
id | url | user_registered | private_key | modify_date | ip | url_md5
---+-+-+-+-++-
(0 rows)
Time: 90.711 ms
mydb=#
mydb=#
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 2.519 ms
mydb=#
mydb=#
mydb=# INSERT INTO stores (id) values ('20xrrs3');
ERROR: duplicate key value violates unique constraint idx_stores_pkey
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=# \d stores
Table public.stores
Column |Type |Modifiers
-+-+-
id | character varying(35) | not null
modify_date | timestamp without time zone | default now()
ip | bigint |
Indexes:
idx_stores_pkey PRIMARY KEY, btree (id)
idx_stores_modify_date btree (modify_date)
Check constraints:
stores_id_check CHECK (id::text ~ '[-.~a-z0-9_]'::text)
Referenced by:
TABLE stores_stats CONSTRAINT fk_stats FOREIGN KEY (id)
REFERENCES stores(id) ON DELETE CASCADE
Rules:
__track_stores_deleted AS
ON DELETE TO stores
WHERE NOT (EXISTS ( SELECT stores_deleted.id
FROM stores_deleted
WHERE stores_deleted.id = old.id)) DO INSERT INTO
stores_deleted (id, modify_date, ip)
VALUES (old.id, old.modify_date, old.ip)
Any other ideas?
--
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] Shared memory usage in PostgreSQL 9.1
* Christoph Zwerschke (c...@online.de) wrote: (Btw, what negative consequences - if any - does it have if I set kernel.shmmax higher as necessary, like all available memory? Does this limit serve only as a protection against greedy applications?) Didn't see this get answered... The long-and-short of that there aren't any negative consequences of having it higher, as I understand it anyway, except the risk of greedy apps. In some cases, shared memory can't be swapped out, which makes it a bit more risky than 'regular' memory getting sucked up by some app. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Shared memory usage in PostgreSQL 9.1
This message has been digitally signed by the sender. Re___GENERAL__Shared_memory_usage_in_PostgreSQL_9_1.eml Description: Binary data - Hi-Tech Gears Ltd, Gurgaon, India -- 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] Shared memory usage in PostgreSQL 9.1
On 4.12.2011 15:06, Stephen Frost wrote: * Christoph Zwerschke (c...@online.de) wrote: (Btw, what negative consequences - if any - does it have if I set kernel.shmmax higher as necessary, like all available memory? Does this limit serve only as a protection against greedy applications?) Didn't see this get answered... The long-and-short of that there aren't any negative consequences of having it higher, as I understand it anyway, except the risk of greedy apps. In some cases, shared memory can't be swapped out, which makes it a bit more risky than 'regular' memory getting sucked up by some app. AFAIK it's just a protection. It simply allows more memory to be allocated as shared segments. If you care about swapping, you should tune vm.swappiness kernel parameter (and vm.overcommit is your friend too). Tomas -- 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] Shared memory usage in PostgreSQL 9.1
Am 04.12.2011 15:17, schrieb sfr...@snowman.net: Didn't see this get answered... The long-and-short of that there aren't any negative consequences of having it higher, as I understand it anyway, except the risk of greedy apps. In some cases, shared memory can't be swapped out, which makes it a bit more risky than 'regular' memory getting sucked up by some app. That's how I understand it as well. So the solution is to calculate an upper limit for the shared memory usage very generously, since it doesn't matter if the limit is set a couple of MBs too high. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Christoph Zwerschke c...@online.de writes: Am 03.12.2011 20:31, schrieb Christoph Zwerschke: Then, the corrected sum is 449627320 Bytes, which is only about 2MB less than was requested. This remaining discrepancy can probably be explained by additional overhead for a PostgreSQL 9.1 64bit server vs. a PostgreSQL 8.3 32bit server for which the table was valid. And this additional overhead obviously is created per max_connections, not per shared_buffers. While the docs suggest there should be 19kB per connection, we measured about 45kB per connection. This explains the about 2MB difference when max_connections is 100. I suspect most of the difference from 8.3 to 9.1 has to do with the additional shared memory eaten by the predicate lock manager (for SSI). That table really ought to get updated to include a factor for max_pred_locks_per_transaction. (And I wonder why max_locks_per_transaction and max_pred_locks_per_transaction aren't documented as part of the memory consumption GUC group?) 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
[GENERAL] WITH and WITH RECURSIVE in single query
Hi. Is here any way to combine WITH and WITH RECURSIVE into single query? Something like: WITH t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL ... ) ? -- Maxim Boguk Senior Postgresql DBA.
Re: [GENERAL] WITH and WITH RECURSIVE in single query
Maxim Boguk maxim.bo...@gmail.com writes: Is here any way to combine WITH and WITH RECURSIVE into single query? You have to put RECURSIVE immediately after WITH, but that doesn't force you to actually make any particular query in the WITH-list recursive. It just makes it possible for a query to be self-referential, not required. 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
[GENERAL] Questions about setting an array element value outside of the update
Lets say i have subquery which produce array[], position and new_value Is here less clumsy way to set array[position] to the new_value (not update but just change an element inside an array) than: SELECT _array[1:pos-1] ||newval ||_array[_pos+1:array_length(_array, 1)] FROM ( SELECT _array, pos, newval FROM some_colmplicated_logic ); The: _array[1:pos-1] ||newval ||_array[_pos+1:array_length(_array, 1)] part is very clumsy for my eyes. PS: that is just small part of the complicated WITH RECURSIVE iterator in real task. -- Maxim Boguk Senior Postgresql DBA.
Re: [GENERAL] WITH and WITH RECURSIVE in single query
On Dec 4, 2011, at 22:28, Maxim Boguk maxim.bo...@gmail.com wrote: Hi. Is here any way to combine WITH and WITH RECURSIVE into single query? Something like: WITH t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL ... ) ? -- Maxim Boguk Senior Postgresql DBA. WITH RECURSIVE q1 As (), q2 AS () ... Add RECURSIVE after the WITH; it then applies to any/all the CTEs. Look at the specification (and description) in the SELECT documentation closely. David J. -- 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] WITH and WITH RECURSIVE in single query
On Mon, Dec 5, 2011 at 2:45 PM, David Johnston pol...@yahoo.com wrote: On Dec 4, 2011, at 22:28, Maxim Boguk maxim.bo...@gmail.com wrote: Hi. Is here any way to combine WITH and WITH RECURSIVE into single query? Something like: WITH t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL ... ) ? -- Maxim Boguk Senior Postgresql DBA. WITH RECURSIVE q1 As (), q2 AS () ... Add RECURSIVE after the WITH; it then applies to any/all the CTEs. Look at the specification (and description) in the SELECT documentation closely. David J. Trouble is I trying to precalculate some data through WITH syntax (non recursive). To be used later in WITH RECURSIVE part (and keep a single of that data instead of N). Something like: WITH _t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL SELECT * FROM r JOIN t ON ... ) So I need have precalculated t table before I start an iterator. Now instead of _t I using record[] + unnest but that appoach very memory hungry for long iterations: WITH RECURSIVE r AS ( SELECT ... ARRAY(SELECT ROW(t.*) FROM some complicated select to speed up recursive part) as _t_array FROM ... UNION ALL SELECT ..., _t_array FROM r JOIN (unnest(_t_array) ...) ON something ) However that approach lead to having copy of the _t_array per each final row, so can use a lot of memory. PS: Yes I know about pl/pgsql but WITH RECURSIVE iterators can give 2-10 time performance gains over implemenation of the same algorythm inside pl/pgsql. -- Maxim Boguk Senior Postgresql DBA.
Re: [GENERAL] Questions about setting an array element value outside of the update
On Dec 4, 2011, at 22:43, Maxim Boguk maxim.bo...@gmail.com wrote: Lets say i have subquery which produce array[], position and new_value Is here less clumsy way to set array[position] to the new_value (not update but just change an element inside an array) than: SELECT _array[1:pos-1] ||newval ||_array[_pos+1:array_length(_array, 1)] FROM ( SELECT _array, pos, newval FROM some_colmplicated_logic ); The: _array[1:pos-1] ||newval ||_array[_pos+1:array_length(_array, 1)] part is very clumsy for my eyes. PS: that is just small part of the complicated WITH RECURSIVE iterator in real task. -- Maxim Boguk Senior Postgresql DBA. My first reaction is that you should question whether you really want to deal with arrays like this in the first place. Maybe describe what you want to accomplish and look for alternatives. I do not know if there is a cleaner way but regardless you should code your logic as a function. If you devise a better way later then changing the algorithm will be very simple. And it also should make you inline SQL easier to follow. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl/pgsql and arrays[]
Some quetions about pl/pgsql and arrays[]. Is such constructions as: RETURN NEXT array[1]; OR SELECT val INTO array[1] FROM ...; Should not work? At least documentation about RETURN NEXT says: RETURN NEXT expression; I think array[1] is a valid expression. -- Maxim Boguk Senior Postgresql DBA.
[GENERAL] What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?
I have a database full of recipes, one recipe per row. I need to store a bunch of arbitrary flags for each recipe to mark various properties such as Gluton-Free, No meat, No Red Meat, No Pork, No Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and Low Carb. Users need to be able to search for recipes that contain one or more of those flags by checking checkboxes in the UI. I'm searching for the best way to store these properties in the Recipes table. My ideas so far: 1. Have a separate column for each property and create an index on each of those columns. I may have upwards of about 20 of these properties, so I'm wondering if there's any drawbacks with creating a whole bunch of BOOL columns on a single table. 2. Use a bitmask for all properties and store the whole thing in one numeric column that contains the appropriate number of bits. Create a separate index on each bit so searches will be fast. 3. Create an ENUM with a value for each tag, then create a column that has an ARRAY of that ENUM type. I believe an ANY clause on an array column can use an INDEX, but have never done this. 4. Create a separate table that has a one-to-many mapping of recipes to tags. Each tag would be a row in this table. The table would contain a link to the recipe, and an ENUM value for which tag is on for that recipe. When querying, I'd have to do a nested SELECT to filter out recipes that didn't contain at least one of these tags. I think this is the more normal way of doing this, but it does make certain queries more complicated - If I want to query for 100 recipes and also display all their tags, I'd have to use an INNER JOIN and consolidate the rows, or use a nested SELECT and aggregate on the fly. Write performance is not too big of an issue here since recipes are added by a backend process, and search speed is critical (there might be a few hundred thousand recipes eventually). I doubt I will add new tags all that often, but I want it to be at least possible to do without major headaches. Thanks! -- 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] Questions about setting an array element value outside of the update
David Johnston pol...@yahoo.com writes: Is here less clumsy way to set array[position] to the new_value (not update but just change an element inside an array) than: SELECT _array[1:pos-1] ||newval ||_array[_pos+1:array_length(_array, 1)] I do not know if there is a cleaner way but regardless you should code your logic as a function. Inside a plpgsql function, you could just do array[pos] := newval; so perhaps it'd be worth creating a helper function that's a wrapper around that. 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] WITH and WITH RECURSIVE in single query
On Dec 4, 2011, at 22:58, Maxim Boguk maxim.bo...@gmail.com wrote: On Mon, Dec 5, 2011 at 2:45 PM, David Johnston pol...@yahoo.com wrote: On Dec 4, 2011, at 22:28, Maxim Boguk maxim.bo...@gmail.com wrote: Hi. Is here any way to combine WITH and WITH RECURSIVE into single query? Something like: WITH t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL ... ) ? -- Maxim Boguk Senior Postgresql DBA. WITH RECURSIVE q1 As (), q2 AS () ... Add RECURSIVE after the WITH; it then applies to any/all the CTEs. Look at the specification (and description) in the SELECT documentation closely. David J. Trouble is I trying to precalculate some data through WITH syntax (non recursive). To be used later in WITH RECURSIVE part (and keep a single of that data instead of N). Something like: WITH _t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL SELECT * FROM r JOIN t ON ... ) So I need have precalculated t table before I start an iterator. Now instead of _t I using record[] + unnest but that appoach very memory hungry for long iterations: WITH RECURSIVE r AS ( SELECT ... ARRAY(SELECT ROW(t.*) FROM some complicated select to speed up recursive part) as _t_array FROM ... UNION ALL SELECT ..., _t_array FROM r JOIN (unnest(_t_array) ...) ON something ) However that approach lead to having copy of the _t_array per each final row, so can use a lot of memory. PS: Yes I know about pl/pgsql but WITH RECURSIVE iterators can give 2-10 time performance gains over implemenation of the same algorythm inside pl/pgsql. -- Maxim Boguk Senior Postgresql DBA. Read the documentation closely, the syntax definition for WITH is precise and accurate. No matter how many queries you want to create you write the word WITH one time. If ANY of your queries require iterative behavior you put the word RECURSIVE after the word WITH. Between individual queries you may only put the name, and optional column alias, along with the required comma. As a side benefit to adding RECURSIVE the order in which the queries appear is no longer relevant. Without RECURSIVE you indeed must list the queries in order of use. David J.
Re: [GENERAL] WITH and WITH RECURSIVE in single query
On Mon, Dec 5, 2011 at 3:15 PM, David Johnston pol...@yahoo.com wrote: On Dec 4, 2011, at 22:58, Maxim Boguk maxim.bo...@gmail.com wrote: On Mon, Dec 5, 2011 at 2:45 PM, David Johnston pol...@yahoo.com pol...@yahoo.com wrote: On Dec 4, 2011, at 22:28, Maxim Boguk maxim.bo...@gmail.com maxim.bo...@gmail.com wrote: Hi. Is here any way to combine WITH and WITH RECURSIVE into single query? Something like: WITH t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL ... ) ? -- Maxim Boguk Senior Postgresql DBA. WITH RECURSIVE q1 As (), q2 AS () ... Add RECURSIVE after the WITH; it then applies to any/all the CTEs. Look at the specification (and description) in the SELECT documentation closely. David J. Trouble is I trying to precalculate some data through WITH syntax (non recursive). To be used later in WITH RECURSIVE part (and keep a single of that data instead of N). Something like: WITH _t AS (some complicated select to speed up recursive part), RECURSIVE r AS ( ... UNION ALL SELECT * FROM r JOIN t ON ... ) So I need have precalculated t table before I start an iterator. Now instead of _t I using record[] + unnest but that appoach very memory hungry for long iterations: WITH RECURSIVE r AS ( SELECT ... ARRAY(SELECT ROW(t.*) FROM some complicated select to speed up recursive part) as _t_array FROM ... UNION ALL SELECT ..., _t_array FROM r JOIN (unnest(_t_array) ...) ON something ) However that approach lead to having copy of the _t_array per each final row, so can use a lot of memory. PS: Yes I know about pl/pgsql but WITH RECURSIVE iterators can give 2-10 time performance gains over implemenation of the same algorythm inside pl/pgsql. -- Maxim Boguk Senior Postgresql DBA. Read the documentation closely, the syntax definition for WITH is precise and accurate. No matter how many queries you want to create you write the word WITH one time. If ANY of your queries require iterative behavior you put the word RECURSIVE after the word WITH. Between individual queries you may only put the name, and optional column alias, along with the required comma. As a side benefit to adding RECURSIVE the order in which the queries appear is no longer relevant. Without RECURSIVE you indeed must list the queries in order of use. David J. Thank you very much David. That work like a charm. another 30% runtime gone. -- Maxim Boguk Senior Postgresql DBA.
Re: [GENERAL] pl/pgsql and arrays[]
Hello
it work on my pc
postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
return next g[1];
return;
end;
$function$
postgres=# select fx();
fx
20
(1 row)
regards
Pavel Stehule
2011/12/5 Maxim Boguk maxim.bo...@gmail.com:
Some quetions about pl/pgsql and arrays[].
Is such constructions as:
RETURN NEXT array[1];
OR
SELECT val INTO array[1] FROM ...;
Should not work?
At least documentation about RETURN NEXT says:
RETURN NEXT expression;
I think array[1] is a valid expression.
--
Maxim Boguk
Senior Postgresql DBA.
--
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] pl/pgsql and arrays[]
On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
it work on my pc
postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
return next g[1];
return;
end;
$function$
postgres=# select fx();
fx
20
(1 row)
regards
Pavel Stehule
Oh sorry.
Seems I didn't tested simple cases.
Error happened when you work with record[] types and return setof:
create table test (id serial);
insert into test select generate_series(1,10);
CREATE OR REPLACE FUNCTION _test_array()
RETURNS SETOF test
LANGUAGE plpgsql
AS $$
DECLARE
_array test[];
_row test%ROWTYPE;
BEGIN
SELECT array(SELECT test FROM test) INTO _array;
--work
--_row := _array[1];
--RETURN NEXT _row;
--also work
--RETURN QUERY SELECT (_array[1]).*;
--error
--RETURN NEXT _array[1];
--error
--RETURN NEXT (_array[1]);
--error
--RETURN NEXT (_array[1]).*;
RETURN;
END;
$$;
2011/12/5 Maxim Boguk maxim.bo...@gmail.com:
Some quetions about pl/pgsql and arrays[].
Is such constructions as:
RETURN NEXT array[1];
OR
SELECT val INTO array[1] FROM ...;
Should not work?
At least documentation about RETURN NEXT says:
RETURN NEXT expression;
I think array[1] is a valid expression.
--
Maxim Boguk
Senior Postgresql DBA.
--
Maxim Boguk
Senior Postgresql DBA.
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
Skype: maxim.boguk
Jabber: maxim.bo...@gmail.com
LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?
МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.
Re: [GENERAL] pl/pgsql and arrays[]
2011/12/5 Maxim Boguk maxim.bo...@gmail.com:
On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hello
it work on my pc
postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
return next g[1];
return;
end;
$function$
postgres=# select fx();
fx
20
(1 row)
regards
Pavel Stehule
Oh sorry.
Seems I didn't tested simple cases.
return next in function that returns composite type needs a composite
variable. Other cases are not supported there.
Regards
Pavel Stehule
Error happened when you work with record[] types and return setof:
create table test (id serial);
insert into test select generate_series(1,10);
CREATE OR REPLACE FUNCTION _test_array()
RETURNS SETOF test
LANGUAGE plpgsql
AS $$
DECLARE
_array test[];
_row test%ROWTYPE;
BEGIN
SELECT array(SELECT test FROM test) INTO _array;
--work
--_row := _array[1];
--RETURN NEXT _row;
--also work
--RETURN QUERY SELECT (_array[1]).*;
--error
--RETURN NEXT _array[1];
--error
--RETURN NEXT (_array[1]);
--error
--RETURN NEXT (_array[1]).*;
RETURN;
END;
$$;
2011/12/5 Maxim Boguk maxim.bo...@gmail.com:
Some quetions about pl/pgsql and arrays[].
Is such constructions as:
RETURN NEXT array[1];
OR
SELECT val INTO array[1] FROM ...;
Should not work?
At least documentation about RETURN NEXT says:
RETURN NEXT expression;
I think array[1] is a valid expression.
--
Maxim Boguk
Senior Postgresql DBA.
--
Maxim Boguk
Senior Postgresql DBA.
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
Skype: maxim.boguk
Jabber: maxim.bo...@gmail.com
LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?
МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.
--
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's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?
On 12/05/2011 12:10 PM, Mike Christensen wrote: I have a database full of recipes, one recipe per row. I need to store a bunch of arbitrary flags for each recipe to mark various properties such as Gluton-Free, No meat, No Red Meat, No Pork, No Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and Low Carb. Users need to be able to search for recipes that contain one or more of those flags by checking checkboxes in the UI. I'm searching for the best way to store these properties in the Recipes table. I'd use hstore to store them as tags. You can then use hstore's GiST index support to get quick lookups. 1. Have a separate column for each property and create an index on each of those columns. I may have upwards of about 20 of these properties, so I'm wondering if there's any drawbacks with creating a whole bunch of BOOL columns on a single table. It'll get frustrating as you start adding new categories, and will drive you insane as soon as you want to let the user define their own categories - which you will land up wanting to do in your problem space. I'd avoid it. 2. Use a bitmask for all properties and store the whole thing in one numeric column that contains the appropriate number of bits. Create a separate index on each bit so searches will be fast. Same as above, it'll get annoying to manage when you want user tagging. 3. Create an ENUM with a value for each tag, then create a column that has an ARRAY of that ENUM type. I believe an ANY clause on an array column can use an INDEX, but have never done this. Same again. 4. Create a separate table that has a one-to-many mapping of recipes to tags. Each tag would be a row in this table. The table would contain a link to the recipe, and an ENUM value for which tag is on for that recipe. When querying, I'd have to do a nested SELECT to filter out recipes that didn't contain at least one of these tags. I think this is the more normal way of doing this, but it does make certain queries more complicated - If I want to query for 100 recipes and also display all their tags, I'd have to use an INNER JOIN and consolidate the rows, or use a nested SELECT and aggregate on the fly. That'll get slow. It'll work and is IMO better than all the other options you suggested, but I'd probably favour hstore over it. -- Craig Ringer -- 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] psql query gets stuck indefinitely
Hi Tomas I tried it on the system having postgres-8.4.0 . And the behavior is same . Cluster means a group of machines having postgres installed on all of them . Same database is created on all the machines one of which working as master DB on which operation (like insert/delete/update) will be performed and others working as Slave Db which will get data replicated to them from master DB by slony . In my cluster setup there are only two machines ( A and B ) one having master Db and other being slave . I execute the below query from system A to system B : psql -Udb name -hhost ip of B -c select sleep(300); This query can be seen running on system B in `ps -eaf | grep postgres` output . Now, while this query is going on, execute below command on system A which will block any packet coming to this machine : iptables -I INPUT -i eth0 -j DROP . Afer 5 mins (which is the sleep period) , the above query will finish on system B . But it can still be seen running on system A . This may be because of the reason that the message (that the query is finished) have not been received by system A . Still I would assume that after (tcp_keepalive_time + tcp_keepalive_probes*tcp_keepalive_intvl) , the above psql query should return on system A as well. But, this query doesn't return until it is killed manually . What could be the reason of that ?? Well , I learnt below from the release notes of postgres : == = postgres 8.1 server side chnages : Add configuration parameters to control TCP/IP keep-alive times for idle, interval, and count (Oliver Jowett) These values can be changed to allow more rapid detection of lost client connections. postgres 9.0 E.8.3.9. Development Tools E.8.3.9.1. libpq Add TCP keepalive settings in libpq (Tollef Fog Heen, Fujii Masao, Robert Haas) Keepalive settings were already supported on the server end of TCP connections. == Does this mean that TCP keep alive settings(that are provided in postgres 8.1 onwards) would only work for lost connections to server and won't work in the case above as above case requires psql (which is client ) to be returned ?? And for the above case the TCP keepalive settings in libpq ( that are provided in postgres 9.0 onwards) would work ?? kernel version on my system is 2.6.27.7-9-default and potstgres-8.4.0. keepalive setting are as below : postgresql.conf #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default system level setiing : net.ipv4.tcp_keepalive_time = 7200 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_intvl = 75 Regards Tamanna On Thu, Dec 1, 2011 at 7:28 PM, Tomas Vondra t...@fuzzy.cz wrote: On 1 Prosinec 2011, 12:57, tamanna madaan wrote: Hi Craig I am able to reproduce the issue now . I have postgres-8.1.2 installed in cluster setup. Well, the first thing you should do is to upgrade, at least to the last 8.1 minor version, which is 8.1.22. It may very well be an already fixed bug (haven't checked). BTW the 8.1 branch is not supported for a long time, so upgrade to a more recent version if possible. Second - what OS are you using, what version? The keep-alive needs support at OS level, and if the OS is upgraded as frequently as the database (i.e. not at all), this might be already fixed. And finally - what do you mean by 'cluster setup'? Tomas -- Tamanna Madaan | Associate Consultant | GlobalLogic Inc. Leaders in Software RD Services ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA Office: +0-120-406-2000 x 2971 www.globallogic.com
Re: [GENERAL] What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?
I have a database full of recipes, one recipe per row. I need to store a bunch of arbitrary flags for each recipe to mark various properties such as Gluton-Free, No meat, No Red Meat, No Pork, No Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and Low Carb. Users need to be able to search for recipes that contain one or more of those flags by checking checkboxes in the UI. I'm searching for the best way to store these properties in the Recipes table. I'd use hstore to store them as tags. You can then use hstore's GiST index support to get quick lookups. 1. Have a separate column for each property and create an index on each of those columns. I may have upwards of about 20 of these properties, so I'm wondering if there's any drawbacks with creating a whole bunch of BOOL columns on a single table. It'll get frustrating as you start adding new categories, and will drive you insane as soon as you want to let the user define their own categories - which you will land up wanting to do in your problem space. I'd avoid it. 2. Use a bitmask for all properties and store the whole thing in one numeric column that contains the appropriate number of bits. Create a separate index on each bit so searches will be fast. Same as above, it'll get annoying to manage when you want user tagging. 3. Create an ENUM with a value for each tag, then create a column that has an ARRAY of that ENUM type. I believe an ANY clause on an array column can use an INDEX, but have never done this. Same again. 4. Create a separate table that has a one-to-many mapping of recipes to tags. Each tag would be a row in this table. The table would contain a link to the recipe, and an ENUM value for which tag is on for that recipe. When querying, I'd have to do a nested SELECT to filter out recipes that didn't contain at least one of these tags. I think this is the more normal way of doing this, but it does make certain queries more complicated - If I want to query for 100 recipes and also display all their tags, I'd have to use an INNER JOIN and consolidate the rows, or use a nested SELECT and aggregate on the fly. That'll get slow. It'll work and is IMO better than all the other options you suggested, but I'd probably favour hstore over it. The hstore module sounds fantastic! I'm curious as to how these columns are serialized back through the driver, such as Npgsql. Do I get the values as strings, such as a comma delimited key/value pair list? Or would I need to do some custom logic to deserialize them? Right now, I'm using Npgsql as a driver, and NHibernate/Castle ActiveRecord as an ORM. Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
