Re: [GENERAL] "disappearing" rows in temp table, in recursing trigger
Eric Worden wrote: > The recursive function creates a temp table [...] I > have version 8.1.10. While I haven't looked in detail, I'd be surprised if this wasn't an issue with pre-8.3 versions lack of any way to automatically re-generate cached plans in functions. Try running your code on an 8.3 installation and see if you still get the issue. I strongly suspect you won't. If this does prove to be the case, there are workarounds for pre-8.3 versions, usually involving the use of EXECUTE to bypass the cache and force re-planning of statements at every execution. However, if it's practical to do so upgrading to 8.3 might be a nicer and (in the long run) easier option. -- 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
[GENERAL] subselect and count (DISTINCT expression [ , ... ] ) performances
On Fri, 26 Dec 2008 19:13:48 -0500 Tom Lane wrote: > The subselect syntax certainly seems like the one most likely to > work across different SQL implementations. WITH is a pretty subselects actually works on mysql too but on a 1M table with about 300K unique columns it performs more than 4 times slower than select (distinct a,b) from table 18sec vs. 4sec Times were similar for innodb and myisam. Postgresql needs 17sec with subselect. I didn't try to see how both db could perform with indexes. mysql performance is impressive. I thought that most of the time would be spent on "distinct" where postgresql shouldn't suffer from its "count" implementation. But well still 300K rows to count on 1M aren't few. -- 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] Automatic CRL reload
Andrej Podzimek wrote: > "The files server.key, server.crt, root.crt, and root.crl are only > examined during server start; so you must restart the server for > changes in them to take effect." > (http://www.postgresql.org/docs/8.3/static/ssl-tcp.html) > > This is perfectly fine for server.key, server.crt and root.crt. These > files change quite rarely. However, root.crl usually chages once a > month (which is the default in OpenSSL) or even more often when > necessary. I think the right solution here is to reload the CRL file on SIGHUP (reload). Whoever changes the CRL file should send a signal. I've had that on my TODO list for a while. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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 query sort
I have a table, queries, with a column value. There is a trigger on
this table that inserts into query_history for each update to value.
I'm trying to graph the query_history table, so I was using a custom
aggregate to turn it into an array:
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
This worked out pretty well. I was initially concerned that the order
was correct because the table was stored in the right order on the
disk, so I got a query something like this:
select queries.query_id,
array_accum(value) as current_values,
min(query_time) as min_time,
max(query_time) as max_time
fromqueries,
(select query_id, value, query_time
fromquery_history
order by query_time) hist
where queries.query_id = hist.query_id
anddirty = true
andquery_time <= update_time
andquery_time > update_time - '1 hour'::interval
group by queries.query_id
This works out, but I decided to switch to the last 16 values instead
of the last hour.
So I ended up with this:
select queries.query_id,
array_accum(value) as current_values,
null as previous_values,
min(query_time) as min_time,
max(query_time) as max_time
fromqueries,
(select query_id, value, query_time from (
select query_id, value, query_time
fromquery_history
order by query_time desc
limit 16) desc_hist
order by query_time desc) hist
where queries.query_id = hist.query_id
anddirty = true
andquery_time <= update_time
group by queries.query_id
The part I'm wondering about is this piece:
(select query_id, value, query_time from (
select query_id, value, query_time
fromquery_history
order by query_time desc
limit 16) desc_hist
order by query_time desc) hist
I was intiially trying to sort the inner loop by the time descending,
and the outer loop by the time ascending, but that resulted in an
array that was the reverse of the desired order. Switching the outer
query to order by desc fixed it and comes out in the proper order.
It seems like I should be able to order by quer_time desc and then
query_time asc. Am I missing something? Is this a bug?
-Jeff
--
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 query sort
"Jeffrey Melloy" writes: > It seems like I should be able to order by quer_time desc and then > query_time asc. Am I missing something? You'd have to do it *after* the join and GROUP BY if you want it to control the input to the aggregate reliably. Either of those operations will feel free to output rows in whatever order suits its convenience. Ordering in the sub-select is useful in connection with making sure its LIMIT produces the tuples you want, but it isn't going to be preserved through the join and GROUP BY. Also, I think what you really need here is to have the intermediate query "ORDER BY queries.query_id, query_time"; otherwise you risk having the GROUP BY do a sort by query_id, which'll destroy the desired ordering on query_time within query_id groups. 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] Finding foreign keys that are missing indexes
I'm aware that if you create a foreign key constraint, no indexes are
automatically created.
I would like to find a way to programatically inspect all my foreign keys and
identify possibly missing indexes on either table (either the table defining
the constraint or the table being referenced).
I wasn't able to find anything searching Google or the pg archives.
Attached is a first attempt. Just run the missing-fk-indexes.sql through psql.
e.g.,
psql -q mydb -f missing-fk-indexes.sql
I know the output can be improved, but is this headed toward the right
direction and/or is there already a simpler way to accomplish this?
Briefly, it finds all the unique tables/columns referenced by foreign keys.
Then it examines all the indexes, looking for any that are a prefix of the fk
columns. It writes out any tables/columns where no indexes are found, followed
by a list of the fk's that reference those tables/columns.
Also attached is a trivial test schema to run it against.
_
Life on your PC is safer, easier, and more enjoyable with Windows Vista®.
http://clk.atdmt.com/MRT/go/127032870/direct/01//* psql -E to see internal queries */
CREATE TEMP TABLE temp_objects AS
SELECT c.oid
,n.nspname
,c.relname
,c.relkind
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE 1=1
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
-- AND pg_catalog.pg_table_is_visible(c.oid)
;
/* Find all unique associations used by fk's */
CREATE TEMP TABLE temp_fk AS
SELECT DISTINCT ON (oid, cols_string)
*
FROM (
SELECT nspname
,relname
,oid
,conkey AS cols
,array_to_string(conkey, ',') AS cols_string
FROM temp_objects c
JOIN pg_catalog.pg_constraint r ON (r.conrelid = c.oid)
WHERE r.contype = 'f'
UNION
SELECT nspname
,relname
,oid
,confkey AS cols
,array_to_string(confkey, ',') AS cols_string
FROM temp_objects c
JOIN pg_catalog.pg_constraint r ON (r.confrelid = c.oid)
WHERE r.contype = 'f'
) z
;
-- SELECT * FROM temp_fk;
/* look for any indexes that could be used by each fk association */
CREATE TEMP TABLE temp_check_indexes AS
SELECT fk.oid
,fk.cols
,fk.cols_string
,index_name || ' on ' || indkey::text AS "index"
,i.index_cols_string || ' ~* ' || ('^' || fk.cols_string || '(,|$)') AS "like"
,fk.nspname || '.' || fk.relname || '.' || fk.cols_string AS fk_name
,index_cols_string ~* ('^' || fk.cols_string || '(,|$)') AS has_index
FROM temp_fk fk
JOIN (
SELECT temp_objects.relname AS index_name
,indrelid
,indkey AS indkey
,array_to_string(indkey::smallint[], ',') AS index_cols_string
FROM pg_catalog.pg_index
JOIN temp_objects ON (pg_index.indexrelid = temp_objects.oid)
) i ON (fk.oid = i.indrelid)
;
-- SELECT * FROM temp_check_indexes;
/* find all the fk associations where we *didn't* find an index */
CREATE TEMP TABLE temp_possibly_missing_indexes AS
SELECT oid
,cols_string
FROM temp_check_indexes
GROUP BY oid, cols_string
HAVING NOT bool_or(has_index)
;
\echo 'Possibly missing indexes on:'
SELECT t.oid
,c.nspname
,c.relname
,cols_string AS columns
FROM temp_possibly_missing_indexes t
JOIN temp_objects c ON c.oid = t.oid
;
\echo 'Foreign keys referencing above:'
SELECT DISTINCT ON (nspname, relname, conname)
nspname
,relname
,conname AS "foreign key"
,CASE WHEN t.oid = r.conrelid THEN 'constraint_table' ELSE 'references_table' END AS direction
FROM pg_catalog.pg_constraint r
JOIN temp_objects c ON (c.oid = r.conrelid)
JOIN temp_possibly_missing_indexes t ON (
(t.oid = r.conrelid AND t.cols_string = array_to_string(r.conkey, ','))
OR
(t.oid = r.confrelid AND t.cols_string = array_to_string(r.confkey, ','))
)
WHERE r.contype = 'f'
;
CREATE TABLE cars (
car_id serial PRIMARY KEY
,make text NOT NULL
,model text NOT NULL
);
CREATE TABLE owners (
owner_id serial PRIMARY KEY
,name text
);
CREATE TABLE owner_cars (
owner_car_id serial PRIMARY KEY
,owner_id integer NOT NULL REFERENCES owners ON UPDATE CASCADE
,car_id integer NOT NULL REFERENCES cars ON UPDATE CASCADE
);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
