ALTER TABLE posts ADD myrand DOUBLE PRECISION;UPDATE posts SET myrand = RANDOM(); CREATE INDEX myrand_posts ON posts(myrand,id); ANALYZE VERBOSE posts;
ALTER TABLE posts ALTER myrand
SET DEFAULT RANDOM();
ALTER TABLE posts ALTER myrand
SET NOT NULL;
--SELECT * FROM posts WHERE myrand >= (SELECT RANDOM() OFFSET 0) ORDER BY myrand ASC LIMIT 1;
Determining who's blocking who in Postgres
If you have databases like I do with lots of concurrent queries, you can sometime run into situations where you issue a query and it just hangs there blocked. Or, more likely somebody or something issues a query and then comes calling when it doesn't seem to be doing anything.
Of course, you have the handy pg_stat_activity and pg_locks views at your disposal, but when it comes to determining exactly which queries are blocking which others and on what table, querying those alone is a tedious way to get the answer. What you really need is a query that sums it all up, in one neat and tidy bundle. Well, my friends here is such a query:
SELECT
bl.procpid as blocked_pid,
bl.usename as user,
bl.current_query as blocked_query,
bl.query_start,
relname as blocked_on ,
lq.procpid as blocking_pid,
lq.usename as user,
lq.current_query as blocking_query,
lq.query_start,
pgl2.mode as lock_type
FROM pg_stat_activity bl, pg_locks pgl1,
pg_stat_activity lq, pg_locks pgl2, pg_class
WHERE bl.procpid = pgl1.pid
AND not pgl1.granted
AND pg_class.oid = pgl1.relation
AND pgl2.relation = pgl1.relation
AND pgl2.granted
AND lq.procpid = pgl2.pid;
In extended mode (\x) psql returns something along these lines for this query:
blocked_pid | 21418
user | sueuser
blocked_query | insert values ('foo', 'bar', 'baz')
into extremely_large_table;
query_start | 2007-02-13 15:14:06.77606-08
blocked_on | extremely_large_table
blocking_pid | 21417
user | joeuser
blocking_query | delete from extremely_large_table;
query_start | 2007-02-13 14:45:34.637675-08
lock_type | AccessExclusiveLock
psql -c "\d tablename" > 1.txt
get the table structure
cat 1.txt | awk '{print $1}' > 2.txt
get the 1st word (column name)
for x in `cat 2.txt`; do
echo "alter table tablename rename \"$x\" to $x;" >> 3.txt
done
build a file of sql commands to run
then, paste these into psql
so have you discovered pgsql users, and schemas yet
psql -U postgres
psql> create user newuser password 'newuser';
psql> create schema newuser authorization newuser;
psql> \q
# psql -U newuser
psql> create table foo();
psql> \d
the table is then owned by newuser user, in its own schema (like a namespace). this is somewhat how oracle user/schema owned tables are done.
this lets you have one database and then have many users within that database, each in their own schema.
this is same effect for having a different database for each project i guess.
10k rows. varchar types, no indexes. takes a while it seems.
ok j00 ready?
this is what i did:
alter table localeze_amacai_business add id integer;
create sequence localeze_amacai_business_id_seq;
create or replace function assign_localize_pk ()
returns integer as $_$
DECLARE
_id integer;
_count integer;
_row record;
BEGIN
for _row in select * from localeze_amacai_business LOOP
select into _id nextval('localeze_amacai_business_id_seq');
update localeze_amacai_business
set id = _id
where "PERSISTENTRECORDID" = _row."PERSISTENTRECORDID";
END LOOP;
return _count;
END;
$_$ language plpgsql;
select assign_localize_pk();
drop function assign_localize_pk();
alter table localeze_amacai_business alter id set not null;
alter table localeze_amacai_business add constraint localeze_amacai_business_pk primary key (id);
but how 2 set that sequence to this new table
11:18:30 am
Travis
so now it was built using the sequence
select last_value from localeze_amacai_business_id_seq
coup-# ;
last_value
------------
9258
oh, thats e-z
just:
alter table localeze_amacai_business alter id set default nextval('localeze_amacai_business_id_seq');
so now new inserts will invoke the sequence and you dont have to specify the id value
or you could always do
select into _id nextval('localeze_amacai_business_id_seq');
insert into. ... (id, ...) values (_id, ..)
table
i do not understand
i do not want to specify id values
11:20:33 am
Travis
i mod the existing table , added that "id" column to it
and it defaults to the sequence now
so how do you relate to the table if you dont care what its id is?
postgresql now has the insert into .. working
so create table2 table with all the columns that table1 (original has)
hm, actually select into needs the table to not exist
i guess it is possible in postgresql too, wher you can have the original table, and a new table with the id pk column and then make a plpgsql function that
for _row in select * from table LOOP
select into _id nextval('a_sequence');
insert into new_table(id, ....) values (_id, .....);
LOOP
create or replace function strip_phone_number(
_in varchar
) returns varchar as $_$
DECLARE
_len integer;
_i integer;
_chr varchar;
_test varchar;
_result varchar;
BEGIN
select into _len length(_in);
_i := 0;
select into _result '';
while _i <= _len LOOP
_i := _i + 1;
select into _chr substring(_in from _i for 1);
select into _test substring(_chr from '[0-9]$');
--_test := _chr;
if (_test is not null) then
select into _result _result || _test;
end if;
raise debug '%:%:%:%', _i, _chr, _test, _result;
END LOOP;
return _result;
END;
$_$ language plpgsql;
(u will need to fix the tabs thing in msn pastes)
select strip_phone_number('(123) 456-8909 x 1234');
strip_phone_number
--------------------
12345689091234
(1 row)
so that dumps the "not digit" characters from a string in pl/pgsql
but what good does that do?
dont you need the ui to undo that?
yum install proj proj-devel
#get postgres source
cd contrib
svn co http://svn.refractions.net/postgis/trunk postgis
cd postgis
./autogen.sh
./configure --with-pgsql=/usr/local/pgsql/bin/pg_config
make && sudo make install
/usr/local/pgsql/bin/createlang plpgsql coupon
/usr/local/pgsql/bin/createlang plpgsql coupon -U postgres
/usr/local/pgsql/bin/psql -d coupon -f lwpostgis.sql -U postgres
/usr/local/pgsql/bin/psql -d coupon -f spatial_ref_sys.sql -U postgres
psql is so good
hey
in psql
there is a \h
which gives you sql query help,
so \h select
tehn as you are typing things out, hitting tab gives you options for what to use next sometimes
try typing
alter table [tab]
then it displays list of tables it can see.
then there is \? which lists other meta commands
like \dt shows tables
\l lists databases \
\dn lists schemas
\du lists users
well, for your setup mostly you have 1 users, one schema, and couple databases
when you are connected to one database in psql, \c newdbname
chages databases
after a few days you get used to the auto-complete features, and the \h things to help you, it feels like a gui sort of, but much much faster than pgadmin
though, i got into postgresql after being forced to work with oracle (yuk)
the only oracle gui at the time was toad, which is by Que$t $oftware.
and we never could afford to buy it
so we learned the oracle meta database,
which is oddly enough, tables and a database, to describe the database
postgresql has this too
the pg_catalog, where there are what looks like tables, to describe our user databases, schemas, tables, and our database objects.
and the \d commands in psql sort of are short cuts for this
but you can also do select .. from pg_* tables, and that gives us information on table features, and the columns, which is what pgadmin is doing behind the scenes for us to display their things all nicely formatted.
mysql, on the otherhand, does not have a meta database, but then what do you expect from junk :)
in version 3.3 the "show databases" command actually invoked a system command to "ls" (list files) in the mysql directory, since mysql used to (still does?) create databases as directories on the file system.
this has the horrible side effect of making table names Case Sensitive, which violates the SQL standard (lol, and mysql claimes to be sql compliant, but cant even get case insensitive table names :!)
we discovered that one time the hard way by migrating a mysql on windows app to mysql on unix, and of course developers will make code in different spots like
select * from MyTable
select * from mytable
select * from MYTABLE
etc
-- Function: update_total_votes_pictures()
-- DROP FUNCTION update_total_votes_pictures();
CREATE OR REPLACE FUNCTION update_total_votes_pictures()
RETURNS int4 AS
$BODY$ -- returns the number of pixpair entries that were created
DECLARE
_count integer;
all RECORD;
_total_a integer;
_total_b integer;
_total integer;
BEGIN
_count := 0;
FOR all in
select id from pictures
LOOP
select into _total_a sum(pic1_votes) from pixpair where pic1_id = all.id;
if ( _total_a is NULL ) then
_total_a := 0;
end if;
select into _total_b sum(pic2_votes) from pixpair where pic2_id = all.id;
if ( _total_b is NULL ) then
_total_b := 0;
end if;
_total := _total_a + _total_b;
update pictures set total_votes = _total where id = all.id;
_count := _count + 1;
END LOOP;
return _count;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION update_total_votes_pictures() OWNER TO postgres;
i want to move the running postgresql to a different directory any idea on how 2 do it
as it is running outta space
only 200mb left in 5gig
and in a day that ll be filled
well,
a database can be created to use a tablespace
where the tablespace can be another mounted volume
you want to make sure the volume is never not mounted , such as have it mounted in /etc/fstab
well, lets say you go by a nice new 80gb drive
and format it with ext3 or what ever you like to do
and hook it up in /etc/fstab so that it is /u01 or some path you like a lot
and then in postgresql
create tablespace something path '/u01/myfolder/mytablespace'
then you have to do something to alter database set tablespace , so that it uses that
hmm, iv never actually migrated an existing database to a differetn tablespace,
but i think it must be possible to move things from one tablespace to another one, so that it will physically sit on the new drive for us.
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
SET TABLESPACE
This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. See also CREATE TABLESPACE.
Query Writing Rules
For all database management systems (DBMSes), "round-trip" time is significant. This is the amount of time which it takes a query to get through the the language parser, the driver, across the network interface, the database parser, the planner, the executor, the parser again, back across the network interface, through the driver data handler, and to the client application. DBMSes vary in the amount of time and CPU they take to process this cycle, and for a variety of reasons PostgreSQL is a the high end of time and system resources per round-trip.
Further, PostgreSQL has significant per-transaction overhead, including log output and visibility rules which need to be set with each transaction. While you may think that you are not using transactions for singleton read-only SELECT statement, in fact every single statement in PostgreSQL is in a transaction. In the absence of an explicit transaction, the statement itself is an implicit transaction.
Offsetting this, PostgreSQL is only barely second to Oracle in processing large complex queries, and has the capability to handle complex multi-statement transactions with overlapping concurrency conflicts with ease. We also support cursors, both scrollable and non-scrollable.
Tip 1: Never use many small selects when one big query could go the job.
It's common in MySQL applications to handle joins in the application code; that is, by querying the ID from the parent record and then looping through the child records with that ID manually. This can result in running hundreds or thousands of queries per user interface screen. Each of these queries carres 2-6 milleseconds of round-trip time, which doesn't seem significant until you add it up for 1000 queries, at which point you're losing 3-5 seconds to round trip time. Comparatively, retrieving all of those records in a single query only takes a few hundred milleseconds, a time savings of 80%.
Tip 2: Group many small UPDATES, INSERTS or DELETEs into large statements, or failing that, large transactions.
First, the lack of subselects in early versions of MySQL has caused application developers to design their data modification statements (DML) in much the same way as joins-in-middleware. This is also a bad approach for PostgreSQL. Instead, you want to take advantage of subselects and joins in your UPDATE, INSERT and DELETE statements to try to modify batches in a single statement. This reduces round-trip time and transaction overhead.
In some cases, however, there is no single query which can write all the rows you want and you have to use a bunch of serial statements. In this case, you want to make sure to wrap your series DML statements in an explicit transaction (e.g. BEGIN; UPDATE; UPDATE; UPDATE; COMMIT;). This reduces transaction overhead and can cut execution time by as much as 50%.
Tip 3: Consider bulk loading instead of serial INSERTS
PostgreSQL provides a bulk loading mechanism called COPY, which takes tab-delimited or CSV input from a file or pipe. Where COPY can be used instead of hundreds or thousands of INSERTS, it can cut execution time by up to 75%.
I just got wikipgedia 0.0.4 installed and working, and it is running
sweeter than a horses arse at the Ascot races. tsearch2 gave me some
troubles, but they now seem to be resolved. For those of you new to
postgres or tsearch, I'll show you how I did it.
This HOWTO assumes the following:
You are running Debian unstable and you have the Postgres 8.1 client,
server, and contrib packages installed and running.
Create your database "wikidb" with owner "wikiadmin" and the schema
"mediawiki".
$ su - postgres -c "psql template1"
template1=# CREATE USER wikiadmin WITH PASSWORD "somepassword";
template1=# CREATE DATABASE wikidb WITH OWNER wikiadmin;
template1=# \c wikidb
wikidb=# CREATE SCHEMA mediawiki;
wikidb=# \i /usr/share/postgresql/8.1/contrib/tsearch2.sql
The \c command in psql connects you to the wikidb database.
The \i command in psql "includes" the named file, executing all the SQL
commands in the file as if you had typed them in.
Notice we didn't give the "wikiadmin" account superuser powers inside
postgres. For security reasons, I don't recommend it. So you need to
install tsearch2 into the wikidb database as the user "postgres", the
default superuser account. The commands above accomplish that for you.
When I did this I noticed various errors that worried me. Everything
seems to work, but I'd prefer a version of tsearch2 that didn't spit out
those errors during install. They seem harmless, so I will reproduce
them here, in case any googler shares my anxiety:
### TSORT2 INSTALL ERROR MESSAGES ###
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:13:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_dict_pkey" for table "pg_ts_dict"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:145:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_parser_pkey" for table "pg_ts_parser"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:244:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_cfg_pkey" for table "pg_ts_cfg"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:251:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_cfgmap_pkey" for table "pg_ts_cfgmap"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:337:
NOTICE: type "tsvector" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:342:
NOTICE: argument type tsvector is only a shell
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:396:
NOTICE: type "tsquery" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:401:
NOTICE: argument type tsquery is only a shell
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:543:
NOTICE: type "gtsvector" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:548:
NOTICE: argument type gtsvector is only a shell
### END OF ERROR MESSAGES ###
Once tsearch2 was installed, I went through the steps to getting
wikipgedia set up through the web browser. It seemed to work. The main
page popped up. I clicked the Edit link. Clicked the Save button.
Horror!
### EDIT ERROR MESSAGES ###
Warning: pg_query(): Query failed: ERROR: column "si_title" of relation
"searchindex" does not exist in
/my/path/to/html/wiki/includes/DatabasePostgreSQL.php on line 98
A database error has occurred Query: INSERT INTO searchindex
(si_page,si_title,si_text) VALUES ( 1, to_tsvector('main
page'),to_tsvector(' wiki software successfully installed please see
documentation on customizing the interface and the user user''s guide
for usage and configuration help test test test second test; see her
knickers in a knot sh bm bm bm one more time for the howto ')) Function:
SearchTsearch2:update Error: 1 ERROR: column "si_title" of relation
"searchindex" does not exist
Backtrace:
* GlobalFunctions.php line 500 calls wfbacktrace()
* DatabasePostgreSQL.php line 573 calls wfdebugdiebacktrace()
* Database.php line 383 calls databasepostgresql::reportqueryerror()
* SearchTsearch2.php line 116 calls databasepostgresql::query()
* SearchUpdate.php line 103 calls searchtsearch2::update()
* index.php line 270 calls searchupdate::doupdate()
### END OF EDIT ERROR MESSAGES ###
Finally, a tsearch2 webpage mentioned something about granting search
privileges to some of the tsearch2 tables. So I did this, first
assuming the powers of the postgres account:
$ su - postgres -c "psql wikidb"
wikidb=# GRANT SELECT ON pg_ts_dict to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_parser to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_cfg to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_cfgmap to wikiadmin;
After doing this, wikipgedia worked like a champ. Edit was fast and
snappy and gave no errors.
Kudos to the wikipgedia team. If only every software package was so
easy to install. A pity I am no longer able to package it up and
include it in Debian.
made some pretty neat bash shell scripts that interact with postgresql database just from using psql
#!/bin/sh
#variable for common things postgresql needs
PSQL="/usr/bin/psql -q -t -h 192.168.1.4 -U thein -c ";
function log_mail() {
ip="${1}";
get_country "${ip}";
timestamp="`date +"%Y-%m-%d %H:%M:%S"`";
result="`${PSQL} "set search_path=net; select ip_block_log_save('${timestamp}', '${ip}', '${country}', false);"`";
}
so this is a crusty old example, but i am doing a psql call to invoke ip_block_log_save() stored procedure, from inside a bash shell
there are other goodies there too
but the general idea is it is part of my incoming SMTP email server, and it compares the from address to a blacklist in a postgresql database.
psql -q -t -h thehost -U theuser -c "select command here";
that type of command line parameters supress the formatting of psql output;
so it is a low level basic way to have shell scripts to work with databases, by using the psql command with -c "command to run" option
have a column named created tat says when the row was inserted
i want to choose say the columns inserted in the last 4 hrs
10 hrs or 1 day
i cudnt find out how to do it
select * from polls where created .. then waht
are the random database queries too slow
http://www.teenwag.com/poll?n=253
Travis
select * from polls.... where... and extract('epoch' from created) < 14400
wat is epoch
7:04:02 pm
Travis
where the epoch field is the time in seconds, since 1970
and 1440 is how many seconds in 4 hours
actually
7:04:23 pm
is there a field in minutes or hours
7:04:39 pm
Travis
that needs to be and (now() - extract('epoch' from created)) < 14400
there is a field to get the day, hour, minute,
year, month, but those are absolute (==)
if you want something that is 4 hours ago or newer, then you need to do
interval arithmetic yourself by taking the current time
crap that still aitn it
and (extract('epoch' from now()) - extract('epoch' from created)) < 14400
so this takes the absolute time, in seconds now and subtracts the
absolute time from when it was created
which if smaller than 60*60*4, is under 4 hours
7:07:41 pm
wow
kewl
7:08:22 pm
Travis
there is the age(timestamp) function but that displays pretty printing
formattng
not very usefl for a query
http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html
select backend_start, client_addr from pg_stat_activity order by backend_start;
select * from pg_stat_activity order by backend_start;
in psql, i do the
set client_min_messages TO debug;
and then in the function you can do
raise debug 'foo message: %', _a value;
where % are replaced by the _a_value, variable, etc?
the raise debug is only shown if client_min_messages is debug
so you can leave them in there after, and turn on debug mode later
then
when you create
the function
to test i sometimes do
begin transaction;
select function();
and then when it fails, or after i poke at results,
rollback;
well, handy for some kinds of testing
i would start off with
create function do_vore (
pixpair_id integer,
picture_id integer
) returns integer as $$
DECLARE
_id integer;
BEGIN
_id := 0;
-- insert into pixpair_ips
-- increment count in pixpair
-- update picture set totals there too
return _id;
END;
$$ language plpgsql;?1
remove_dup_pixpair
select pic_id, sum(pic_votes) as votes from
(
select pic1_id as pic_id,
pic1_votes as pic_votes
from
pixpair p1
union
select pic2_id as pic_id,
pic2_votes as pic_votes
from
pixpair p2
) a
group by pic_id
12:45:29 am
sum of all pictures.total_votes should equal pixpair.total_votes
12:45:33 am
Travis
and then, i sum this.
select sum(votes) from
(
select pic_id, sum(pic_votes) as votes from
(
select pic1_id as pic_id,
pic1_votes as pic_votes
from
pixpair p1
union
select pic2_id as pic_id,
pic2_votes as pic_votes
from
pixpair p2
) a
group by pic_id
)b
sum
-------
11622
and that gives a differnt number still.
