[SQL] Speeding up schema changes
Is there a way to speed up simple schema changes like ... ALTER TABLE foo ADD COLUMN bar CHAR(64); ... where foo already contains millions of records? On a live database changes like this can take hours. Even when the database is idle. Is there a better way to do this? S. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Speeding up schema changes
"Stefan Arentz" <[EMAIL PROTECTED]> writes: > Is there a way to speed up simple schema changes like ... > > ALTER TABLE foo ADD COLUMN bar CHAR(64); > > ... where foo already contains millions of records? > > On a live database changes like this can take hours. Even when the > database is idle. Are you sure that's exactly like the change you're making? I think that should be instantaneous because it will add a new column which is null everywhere. It doesn't have to actually modify the table contents at all to do that, just the schema. It could be slow if the table is extremely busy and it has trouble getting the lock but I don't think it's easy to create that situation, at least not such that it will last more than a few seconds. If, on the other hand, you added a column with a default value then it would be an entirely different scenario. In that case it has to rewrite the whole table with the new values in every record. It also has to reindex every index for the new table contents and so on. So if you didn't have to initialise the contents you would avoid the wait. Also, incidentally do you have a good reason to use CHAR instead of varchar or text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar will take only as many bytes as the data you're storing (plus 4 bytes). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Constraints for grouping
I have a question that I've thought in my head about using triggers, but I figure to ask people that do SQL more than I. So, I have a table that I want two columns. (1) A featured column which is for only 1 row, once it switched to another row than all other rows must be false title | author_id | categories | featured +---+--+- Thierry Beta Release | 3 | 11 | True Different Approach | 3 | 11 | Ottawa Does Not Heart Hip-Hop | 3 | 11 | (2) A featured column by category and only allow category_feature equal to the number of categories. Is SQL the way to go (and how - ideas), or should I use python for the heavy lifting? Thanks for any input, J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Execute SQL statements with 'context'/predefined variables
I was wondering if it is possible to set the 'context' for running an
sql command:
For example, say you have a rule which upon update to table TEST of
type [a int, b varchar(10), c varchar(50)]:
INSERT INTO Log(a * 5, substring(b,0,2), randomize(c))
So an update of (5, 'hello', 'world') will add a row (25, 'he',
'##aa2') into the Log table.
Here we can perform operations and functions on the values of the
updated row, as the query engine has reference to the
(fieldname,value) pairs from the update. That is, the engine knows
that a=5, b='hello', etc.
My question - can you setup a similar 'context' for a statement
executed from python..
I.e. Let's say we have a python function, which has a name/value pair
tuple equivilent to the updated row
Is it possible to use the SQL parser to perform the operation in the
same manner?
I.e. from a plpython function can we do a:
plpy.execute("INSERT INTO Log(a * 5, substring(b,1,5), randomize(c))")
-so that the db engine executing the insert knows that a=5, b='hello'
and c='world',
-and thus perform operations on them
Clearly, we can have python excute the functions, and execute a string
with the values (function results). Unfortunately I can't (easily) do
this, as the 'insert' definition functions are passed defined as
strings, in which case I'd have to write a parser to work out what are
the functions, operations, variable names, etc.
It would be great to leverage from the SQL engine to handle this task,
which I can easily do so long as I can pass some variables to the db
before executing the command...
I guess bottom line, is it possible to execute a bunch of SQL
statements with some predefined variables?
I'm sure you're wondering why not just do it ALL in SQL. Kinda
complicated, so I might stall on that explanation if possible.
Thanks for your help!
Blay
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Constraints for grouping
--- PostgreSQL Admin <[EMAIL PROTECTED]> wrote: > I have a question that I've thought in my head about using triggers, but > I figure to ask people that do SQL more than I. So, I have a table that > I want two columns. > > (1) A featured column which is for only 1 row, once it switched to > another row than all other rows must be false > > title | author_id | categories | featured > +---+--+- > Thierry Beta Release | 3 | 11 | True > Different Approach | 3 | 11 | > Ottawa Does Not Heart Hip-Hop | 3 | 11 | > > (2) A featured column by category and only allow category_feature equal > to the number of categories. > > Is SQL the way to go (and how - ideas), or should I use python for the > heavy lifting? Are you saying that you have two featured columns? One column for category features and the other for article feature? If I understand you correctly, Item 1 can be achieved nicely with partial indexes. for example, if you only want to allow one row in the entire table to have "feature"= true: CREATE INDEX Only_one_row_true ON Your_table ( featured ) WHERE featured = true; Or if you want to only allow 1 featured article per catagory then: CREATE INDEX Only_one_row_true_per_catagory ON Your_table ( catigories, featured ) WHERE featured = true; This will ensure that the sum( featured = true ) <= sum( unique( catagories )). Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Constraints for grouping
OOPS! --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > CREATE UNIQUE INDEX Only_one_row_true > ON Your_table ( featured ) >WHERE featured = true; > > Or if you want to only allow 1 featured article per catagory then: > > CREATE UNIQUE INDEX Only_one_row_true_per_catagory > ON Your_table ( catigories, featured ) >WHERE featured = true; I forgot the unique part of the DDL. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Execute SQL statements with 'context'/predefined variables
> I was wondering if it is possible to set the 'context' for running an
> sql command
Oracle has a Context('varname') that returns the value of varname for
the session. to best of my knowledge pg has nothing like this.
> I guess bottom line, is it possible to execute a bunch of SQL
> statements with some predefined variables?
you could use rules to inject you constants as long as you were dealing
with known tables/views; you'd have to rewrite the rules each time the
constants changed; the rules are not session oriented - each sessions
would use the same rule.
(if you have not used pg rules imho you are in for a very pleasant
surprise.)
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Constraints for grouping
> --- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > >> CREATE UNIQUE INDEX Only_one_row_true >> ON Your_table ( featured ) >>WHERE featured = true; >> >> Or if you want to only allow 1 featured article per catagory then: >> >> CREATE UNIQUE INDEX Only_one_row_true_per_catagory >> ON Your_table ( catigories, featured ) >>WHERE featured = true; >> > > I forgot the unique part of the DDL. > > Regards, > Richard Broersma Jr. > > Thanks for the information. I will check it out and get back to you. Thanks again, J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Cast on character columns in views
Hello, I have a scenario like this: CREATE TABLE table1 ( id serial NOT NULL, col1 character varying(30), CONSTRAINT pk_table1 PRIMARY KEY (id) ); CREATE TABLE table2 ( fk_table1 integer, type1 character(3), id serial NOT NULL, CONSTRAINT pk_table2 PRIMARY KEY (id) ); CREATE TABLE table3 ( id serial NOT NULL, type2 integer, fk_table1 integer, CONSTRAINT pk_table3 PRIMARY KEY (id) ); CREATE VIEW view1 AS SELECT table1.id, table1.col1, table2.type1, NULL AS type2 FROM table1 JOIN table2 ON table2.fk_table1 = table1.id UNION ALL SELECT table1.id, table1.col1, NULL AS type1, table3.type2 FROM table1 JOIN table3 ON table3.fk_table1 = table1.id; It's all ok except by the fact that when I retrieve data from view1 the column type1 become bpchar instead of character(3) There are some manner to make the view return type1 as character(3) when I do a command like "select * from view1" ? It's strangeous because type2 return as integer. I try to do a CREATE VIEW view1 AS SELECT table1.id, table1.col1, table2.type1, NULL AS type2 FROM table1 JOIN table2 ON table2.fk_table1 = table1.id UNION ALL SELECT table1.id, table1.col1, NULL::character(3) AS type1, table3.type2 FROM table1 JOIN table3 ON table3.fk_table1 = table1.id; But i got an error: ERROR: cannot change data type of view column "type1" I try this too but got the same error: CREATE VIEW view1 AS SELECT table1.id, table1.col1, table2.type1, NULL AS type2 FROM table1 JOIN table2 ON table2.fk_table1 = table1.id UNION ALL SELECT table1.id, table1.col1, ''::character(3) AS type1, table3.type2 FROM table1 JOIN table3 ON table3.fk_table1 = table1.id; I'm doing a cast on a query on view1 like select type1::character(3) from view1 , but if exists a manner of do this without this cast it will much appreciated. TIA -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Execute SQL statements with 'context'/predefined variables
... > When you say use rules to inject constants, how would I go about > doing this? Could you maybe give a brief example? create view tab1_dml as select * from tab1; -- note: -- CONSTANT1 = 8 -- CONSTANT2 = 15 create or replace rule tab1_insert as on insert to tab1_dml do instead( insert into tab1( c1, c2 ) values( new.c1 * 8, 15 ); ); Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Cast on character columns in views
--- "Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote: > CREATE VIEW view1( id, col1, type1, type2) AS > SELECT table1.id, >table1.col1, >CAST( table2.type1 AS CHARACTER( 3 )), >NULL > FROM table1 > JOIN table2 ON table2.fk_table1 = table1.id > UNION ALL > SELECT table1.id, >table1.col1, >CAST( NULL AS CHARACTER( 3 )), >table3.type2 > FROM table1 > JOIN table3 ON table3.fk_table1 = table1.id; Would the above changes work? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Request into several DBMS simultaneously on DDL and DML
DT> As result, __new type of system information__ appears: DT> field, refering to other field by foreign key, DT> has additional bit except own value - DT> bit specifies, whether record, to which it refers, DT> is in the same or in other database. DT> It's necessary to not signalize about break of foreign key, DT> if remote database will not connected in next time. As result, new type of system information appears - field, refering to other field by foreign key, indeed contain two values: (1) usual value, assinged into field by user select FieldFK, ... insert into ... values (FieldFK, ...) (2) system value, which contain identifier of external database (if record, to which field refers, is in external database) and to which it's possible to refer, if to write "#sys" after name of field, containing usual value: select FieldFK#sys, ... insert into ... values (FieldFK#sys, ...) And as result we can give possibility to refer to external database by foreign key at creating or altering table, or not give (deprive this possibility) - it is doing by creating (adding) or not creating (deleting) of field, name of which is finished by "#sys": create/alter table ... ( ... FieldFK num3, FieldFK#sys num2, ... ) Also as result, it's possible to create stored procedures, which, being started by not author, will executed in external database: (1) under login of user, which start procedure create procedure ... (2) under login of author of procedure create sticky procedure ... Each database stores all nicknames, known for its users, in system table 'sys-nicknames' for execution of stored procedures and timers (some of nicknames can name database, in which this enumeration of nicknames is). IdUser al an username password IdDb nickname datatime 101database.com Smithpwds 31 db1 101 123.123.123.123 33 db2 105storage.com 234.234.234.234 Tomson34 db1 'IdUser' is unique identifier of user in this database 'al' (address literal) is DNS-address 'an' (address numeric) is IP-address if 'al'?null, then it is used if field 'al'=null, then 'an' is used 'username' is name of user in external database 'password' is password of user in external database. And: SQL-command or stored procedure, refering to external database, asks username for external database in program-terminal (if 'username'=null for this 'IdUser' and for this nickname) and password (if 'password'=null) timer, refering to external database, will not begin execution, if 'username'=null or 'password'=null, nothing question will be 'IdDb' is identifier of external database, unique inside this database (if 'IdDb'=null, then it is database, in which this record itself is) 'nickname' is nickname of external database (several different 'IdUser' can have identical 'nickname' with identical or different 'al' and 'an', but one 'IdUser' cann't has two identical 'nickname') 'datatime' is data of last updating of this record of table 'sys-nicknames' (it is used for replication of tables 'sys-nicknames') It's possible to change database, in which user "is", by command 'connect', in which username, password and address of database are not specified - username, password and address of database will be taken from table 'sys-nicknames' (next command 'default' will use table 'sys-nicknames' of new database, i.e. of 'db2'): connect db2; Command 'connect' affects to table 'sys-nicknames' of database, in which user is: it (1) updates field 'an' by value, got from DNS-server (field 'an' itself exists for event of breakage of DNS-server) (2) adds new records (in which field 'password'=null), when user executes it into external database, which yet not registered in 'sys-nicknames' with new 'ra' and 'username' (value of field 'password' is set by command 'update', rights of access are given to user on each record separately, including to each record of system table http://sql40.chat.ru/site/sql40/en/author/ddl_eng.htm#department ) connect ra="data.storage.com" username="Johnson" password="pwdj" nickname="db4"; update sys-nicknames set password=pwdj where ra="data.storage.com" username="Johnson" nickname="db4"; IdUser al an username password IdDb nickname datatime 101data.storage.com 234.234.234.234 Johnson pwdj 38 db4 Summary of all ideas is in http://sql40.chat.ru/site/sql40/en/author/mc_eng.htm Dmitry Turin HTML6 (6.4.0) http://html60.chat.ru SQL4 (4.2.0) http://sql40.chat.ru Unicode2 (2.0.1) http://unicode2.chat.ru Computer2 (2.0.3) http://computer20.chat.ru ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Difference between "foo is false" and "foo=false"? Partial index on boolean.
Expecting to save 4 seconds per query, I built a partial index on a
table, and was surprised that it did not work. Could someone explain
the difference between "foo=false" and "foo is false", for a boolean
type column?
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event
(reconciled) where reconciled=false;
stage=# select pg_total_relation_size('eg_ve_reconciled_partial');
pg_total_relation_size
8192
stage=# explain select count(*) from EG_VEHICLE_EVENT where reconciled is
false;
--
Aggregate (cost=33169.57..33169.58 rows=1 width=0)
-> Seq Scan on eg_vehicle_event (cost=0.00..33169.57
rows=1 width=0)
Filter: (reconciled IS FALSE)
stage=# explain select count(*) from EG_VEHICLE_EVENT where reconciled=false;
---
Aggregate (cost=1.02..1.03 rows=1 width=0)
-> Index Scan using eg_ve_reconciled_partial on
eg_vehicle_event (cost=0.00..1.01 rows=1 width=0)
Index Cond: (reconciled = false)
The problem is that my test query above is fast, but the real query
from Hibernate is still dog slow. Here's the pg_log entry:
LOG: duration: 4260.575 ms statement: EXECUTE C_50292 [PREPARE:
select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from
EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1 ) and
(vehicleeve0_.RECONCILED=$2 )]
I tried building two indexes, one for "is false" one for "=false", but
the Hibernate query is still slow. Yet the hand-run version uses the
index easily:
stage=# explain analyze select count(vehicleeve0_.VEHICLE_EVENT_ID)
as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where
(vehicleeve0_.CSO_ID=2 )and(vehicleeve0_.RECONCILED=false);
QUERY
PLAN
---
Aggregate (cost=81.75..81.76 rows=1 width=4) (actual
time=56.153..56.154 rows=1 loops=1)
-> Index Scan using eg_ve_reconciled_partial on
eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4)
(actual time=0.126..44.548 rows=10345 loops=1)
Index Cond: (reconciled = false)
Filter: (cso_id = 2)
Total runtime: 64.825 ms
(5 rows)
--
Visit http://www.obviously.com/
Re: [SQL] Difference between "foo is false" and "foo=false"? Partial index on boolean.
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > Could someone explain > the difference between "foo=false" and "foo is false", for a boolean > type column? They give different results for NULL --- specifically, NULL for the former and FALSE for the latter. Don't blame me, it's in the spec... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Difference between "foo is false" and "foo=false"? Partial index on boolean.
Tom Lane wrote: Bryce Nesbitt <[EMAIL PROTECTED]> writes: Could someone explain the difference between "foo=false" and "foo is false", for a boolean type column? They give different results for NULL --- specifically, NULL for the former and FALSE for the latter. Don't blame me, it's in the spec... Thanks, and Got It. This particular column is: reconciled | boolean | not null On PostgreSQL 8.1.9. So given all that, why would the Hibernate query fail to use the partial index? I eventually created three indexes, and only the hideously large full index increases performance: Indexes: "eg_vehicle_event_pkey" PRIMARY KEY, btree (vehicle_event_id) "no_duplicate_events" UNIQUE, btree (cso_id, event_type, "timestamp", fob_number, hardware_number) "eg_ve_reconciled_full" btree (reconciled) "eg_ve_reconciled_partial" btree (reconciled) WHERE reconciled = false "eg_ve_reconciled_partial_is" btree (reconciled) WHERE reconciled IS FALSE Foreign-key constraints: "fk_event_admin" FOREIGN KEY (admin_id) REFERENCES eg_admin(admin_id) "fkd28396aacabde72e" FOREIGN KEY (vehicle_id) REFERENCES eg_vehicle(vehicle_id) "fkd28396aaf61930e0" FOREIGN KEY (member_id) REFERENCES eg_member(member_id) Only the full index prevents a "false" scan from taking 4 seconds: LOG: duration: 4260.575 ms statement: EXECUTE C_50292 [PREPARE: select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1 ) and (vehicleeve0_.RECONCILED=$2 )] -- Visit http://www.obviously.com/
Re: [SQL] Cast on character columns in views
Richard Broersma Jr wrote: --- "Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote: CREATE VIEW view1( id, col1, type1, type2) AS SELECT table1.id, table1.col1, CAST( table2.type1 AS CHARACTER( 3 )), NULL FROM table1 JOIN table2 ON table2.fk_table1 = table1.id UNION ALL SELECT table1.id, table1.col1, CAST( NULL AS CHARACTER( 3 )), table3.type2 FROM table1 JOIN table3 ON table3.fk_table1 = table1.id; Would the above changes work? Regards, Richard Broersma Jr. Hi Richard, Your changes works ! But now I know what mistake I did: The error is occurring because I'm doing a CREATE OR REPLACE VIEW command. The command with null:character(3) works too. The error is because I create a view then try to change the definition with the CREATE OR REPLACE VIEW command When I drop the view first , and then create again the view (in a separated transaction), now the command works! (this is a bug?) I'm using a postgres 8.2.4 on Linux. Thanks a lot! -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
