Richard Huxton schrieb:
Alexander Elgert wrote:
Hello,
I programmed a little script which iterates over all databases in a
DBMS, iterating over all tables and then over all columns.
This skript works for mysql and postgres.
1. Solution overview
====================
foreach database {
foreach table {
foreach column {
do something ...
}
}
}
OK - obviously not the way to do it if you really want all the columns
in all the databases...
1. Solution detail
==================
foreach database
----------------
mysql: show databases
postgres: SELECT d.datname as Name FROM pg_catalog.pg_database d
WHERE (d.datname != 'template0' AND d.datname != 'template1') ORDER
BY 1;)
You can have template databases called other names too of course.
foreach table
-------------
mysql: show tables
postgres: select table_name from information_schema.tables where
table_schema = 'public')
foreach column
--------------
mysql: show columns from '%s'
postgres: select column_name from information_schema.columns where
table_name = '%s')
(If there are better queries for postgres, please let me know.)
2. Solution
===========
I found the postgres version VERY slow, so a decided to fetch
Define VERY - it took what, milliseconds to do this? Seconds? Hours?
I wrote two different functions to get the data out of the database, the
one uses a huge amount of queries, the other uses an array to hold the data.
Because in mysql I have to use "show tables/show columns from ..." I
thought I can use the same style in postgres.
Of course, it works, but the unoptimized postgres code is 80 times
slower. ;(
The overhead for a single 'show columns from ...' seems to be small in
mysql.
Please read on, I hope I am able to show you the difference.
select table_name, column_name from information_schema.columns
where table_schema = 'public'
and wrote the output to an two dimensional array to process the
elements later:
$tableA[$row['table_name']][$row['column_name']]=True;
Not sure what this code is meant to show.
foreach database {
foreach table_name, column_name {
$tableA[$row['table_name']][$row['column_name']]=True;
}
// process 2-dim $tableA array
}
This results in a structure where I can itereate over all keys in the
2-dim array.
You can see I iterate first over the databases and then over table AND
columns!
--- mysql: ~1s (Database X)
--- postgres: ~1s (Database Y)
;)
In contrast: =======================================================
foreach database {
foreach table {
foreach column {
do something ...
}
}
}
--- mysql: ~1s (Database X)
--- postgres: ~80s (Database Y)
;(
The second approach ist much faster, this must be because there is no
nesting. ;(
What nesting? Are you trying to do sub-queries of some sort?
I did a loop over all tables and THEN calling a query for each table to
get the columns (from the same table).
Yes, there are definitively more queries the DBMS has to manage.
(It is a bad style, but it is intuitive. Maybe the overhead of a single
query is more time consuming than in mysql.)
In your first example, you were querying for individual rows, in the
second you fetch them all at once. Of course it's quicker to run one
query and return many rows than many queries each returning one row.
Otherwise your RDBMS has something seriously wrong with it.
Further - I think - there is no real caching done in the PHP Library
of xampp 1.5.4, php_pgsql.dll ;(
No (not that I'm sure what xampp is). Look into memcache/pg_memcache
if you want caching - that does it properly.
Thanks.
(xampp is an environment to develop wep applications.)
It would be very helpful what causes the problem.
You haven't actually described a problem afaict. Can you describe what
it is you're trying to do? Do you want:
1. Details of a single column
2. To loop through all the columns doing something
If you can say which of these you're trying to do, and what the
"something" is, then I'm sure there's somebody here who can help.
I think it will be the best to provide example code.
Another question rises from this task:
======================================
Is there any possibility to use the shortcut \d as a query from PHP?
I used psql -E to display the query, but these queries rely on the
system catalogs, but the page:
http://www.postgresql.org/docs/8.2/interactive/information-schema.html
says they are not stable.
And - of course - it is much easier to type! ;)
Nope - \d is psql only. That's because what it does changes with the
system catalogues. If you want a stable representation, use the
information_schema. If you want all the PostgreSQL-specific details
you'll have to cope with changes to the system catalogues. However, I
seem to remember some utility views that pgadmin uses that might be of
use.
It would be nice to have the \d-query stored somewhere in the database.
Oh - and you might find phppgadmin useful to look at.
Thanks.
Greetings,
Alexander Elgert
