Re: [GENERAL] index vs. seq scan choice?
In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: % "George Pavlov" <[EMAIL PROTECTED]> writes: % >> From: Joshua D. Drake [mailto:[EMAIL PROTECTED] % >> In those rare cases wouldn't it make more sense to just set % >> enable_seqscan to off; run query; set enable_seqscan to on; % % > 1. these cases are not that rare (to me); % % It strikes me that you probably need to adjust the planner cost % parameters to reflect reality on your system. Usually dropping % random_page_cost is the way to bias the thing more in favor of % index scans. Also, increasing effective_cache_size. (And increasing statistics...) -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Move a database from one server to other
In article <[EMAIL PROTECTED]>, Nicholas Barr <[EMAIL PROTECTED]> wrote: % Only copy the data directory if both servers are offline and not running % and if both servers use the same version of postgres. This method is not % recommended AFAIK. It _is_ recommended for setting up a warm-standby server (it's the only way). I copy database clusters around all the time. If the database is shut down, there's no harm in it and it's usually faster and always simpler than dump/restore. Copying a snapshot of a running system only has a teenly little bit of harm in it and you don't even have to shut down the db. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] growing disk usage problem: alternative solution?
In article <[EMAIL PROTECTED]>, Vivek Khera <[EMAIL PROTECTED]> wrote: % % On Jun 26, 2007, at 3:31 PM, Bill Moran wrote: % % > VACUUM FULL and REINDEX are not required to maintain disk usage. % > Good old- % > fashoned VACUUM will do this as long as your FSM settings are high % > enough. % > % % I find this true for the data but not necessarily for indexes. The % other week I reindexed a couple of O(100,000,000) row tables and % shaved about 20Gb of index bloat. Those tables are vacuumed % regularly, but we do a large data purge every few weeks. I think % that causes some issues. I'm running 8.1. If you have an index on some monotonically increasing field (i.e., a sequence or date), and you purge by deleting from the low end of this index, then that space won't be reclaimed by vacuum. Vacuum full won't help, either. You (only) need to rebuild the affected indices. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ordering by a complex field
In article <[EMAIL PROTECTED]>, Robert James <[EMAIL PROTECTED]> wrote: % I'd like to order so that records where field='2' come first, then '1', then % '9', then anything but '0', then '0'. Is there anyway to do this in a % standard order by clause (that is, without writing a new SQL function)? You can use a case statement in the order by clause order by case when field = '0' then 4 when field = '1' then 1 when field = '2' then 0 when field = '9' then 2 else 3 end -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Porting MySQL data types to PostgreSQL
In article <[EMAIL PROTECTED]>, Jim Nasby <[EMAIL PROTECTED]> wrote: % On Jul 26, 2007, at 11:06 AM, Jeff Davis wrote: % > If you really do need an unsigned type, this is a good use of % > postgresql's extensible type system. You can just create an unsigned % > type for yourself. % % If you do that please start a project on pgfoundry so others can % contribute and benefit. In fact, if you do start one let me know and % I'll try and help out. One problem with this idea is the treatment of implicit casts between numeric types in TypeCategory(). For implicit casts to work, the type's OID has to be listed in that function (i.e., it has to be a built-in type). -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(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: [GENERAL] Porting MySQL data types to PostgreSQL
In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: % [EMAIL PROTECTED] (Patrick TJ McPhee) writes: % > One problem with this idea is the treatment of implicit casts between % > numeric types in TypeCategory(). For implicit casts to work, the type's % > OID has to be listed in that function (i.e., it has to be a built-in type). % % That's not the case. There probably are some things that won't work % nicely if TypeCategory() doesn't recognize the type as numeric category, % but to claim that implicit casts won't work at all is wrong. I didn't say they won't work at all, but I do say that they won't work completely. I had to play around with it before I remembered where things broke down. Suppose you have a type called unsigned, written in C, with an implicit cast from int4 to unsigned. Then SELECT 23::unsigned UNION SELECT 0; will work if unsigned has one of the numeric OIDs known to TypeCategory(), but not if it was defined normally using CREATE TYPE. You can characterise this as working, just not nicely, but it's still a problem for anyone trying to implement unsigned, or any other kind of numeric value, as a user-defined type. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] memory optimization
In article <[EMAIL PROTECTED]>, Sabin Coanda <[EMAIL PROTECTED]> wrote: [...] % So, what is better from the postgres memory point of view: to use temporary % objects, or to use common variables ? Temp tables can cause serious bloat in some of the system catalog tables. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Selecting K random rows - efficiently!
In article <[EMAIL PROTECTED]>, cluster <[EMAIL PROTECTED]> wrote: % > How important is true randomness? % % The goal is an even distribution but currently I have not seen any way % to produce any kind of random sampling efficiently. Notice the word How about generating the ctid randomly? You can get the number of pages from pg_class and estimate the number of rows either using the number of tuples in pg_class or just based on what you know about the data. Then just generate two series of random numbers, one from 0 to the number of pages and the other from 1 to the number of rows per page, and keep picking rows until you have enough numbers. Assuming there aren't too many dead tuples and your estimates are good, this should retrieve n rows with roughly n look-ups. If your estimates are low, there will be tuples which can never be selected, and so far as I know, there's no way to construct a random ctid in a stock postgres database, but apart from that it seems like a good plan. If efficiency is important, you could create a C function which returns a series of random tids and join on that. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Determining current block size?
In article <[EMAIL PROTECTED]>, John Wells <[EMAIL PROTECTED]> wrote: % I see that BLOCK_SIZE can be set at compile time, but is there a way % to determine what block size is in use in a running system? I've been % searching but have been unsuccessful so far. show block_size; If you try to start the database with a postmaster compiled with the wrong block size, the error message tells you what block size you need. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] timestamp with time zone
In article <[EMAIL PROTECTED]>, Tatsuo Ishii <[EMAIL PROTECTED]> wrote: % From: "Magnus Hagander" <[EMAIL PROTECTED]> [...] % > > Can I get "+04" without knowing that I inserted the data using "+0400" % > > time zone? % > No. The closest you can get is to store the tz in a different column % and use AT TIMEZONE (which accepts a column name as argument) % Or use date + time with time zone. This could cause problems if you want to insert a timestamp for a date with different DST settings. It also seems a lot more cumbersome to do it this way. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [OT] "advanced" database design (long)
In article <[EMAIL PROTECTED]>, Alex Turner <[EMAIL PROTECTED]> wrote: % % I"m not a database expert, but wouldn't % % create table attribute ( % attribute_id int % attribute text % ) % % create table value ( % value_id int % value text % ) % % create table attribute_value ( % entity_id int % attribute_id int % value_id int % ) % % give you a lot less pages to load than building a table with say 90 columns % in it that are all null, which would result in better rather than worse % performance? Suppose you want one row of data. Say it's one of the ones where the columns aren't all nulls. You look up 90 rows in attribute_value, then 90 rows in attribute, then 90 rows in value. You're probably looking at 3-6 pages of index data, and then somewhere between 3 and 270 pages of data from the database, for one logical row of data. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(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: [GENERAL] Postgresql Page Layout details
In article <[EMAIL PROTECTED]>, Richard Huxton <[EMAIL PROTECTED]> wrote: % Some people used to suggest that a larger blocksize helped with specific % disk systems & disk block sizes. This means changing the setting in one % of the header files and recompiling. It also means your database files % aren't compatible with a normally-compiled version of PostgreSQL. I've % not seen anyone mention it recently, so maybe it's just not worth the % trouble any more. I suspect there's just not much to say about it. It makes good sense to match the database block size to the filesystem block size, particularly if the filesystem blocks are larger than 8k. It's not exactly a lot of trouble to set it up, assuming you compile the database yourself anyway, and it allows the database to do a better job of I/O management. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] -- 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] Unloading a table consistently
In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: % If you can't tolerate locking out writers for that long, you won't % be able to use TRUNCATE. The operation I think you were imagining is % % BEGIN; % SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; % COPY log TO 'filename-path'; % DELETE FROM log; % COMMIT; % VACUUM log; -- highly recommended at this point How about something along the lines of BEGIN; ALTER TABLE log RENAME to log_old; CREATE TABLE log(...); COMMIT; BEGIN; LOCK table log_old; COPY log_old TO 'filename-path'; DROP TABLE log_old; COMMIT; I believe this will keep the writers writing while keeping the efficiency of truncating. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] -- 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] Importing undelimited files (Flat Files or Fixed-Length records)
In article <[EMAIL PROTECTED]>, Bill Thoen <[EMAIL PROTECTED]> wrote: % I've got to load some large fixed-legnth ASCII records into PG and I was % wondering how this is done. The Copy command looks like it works only % with delimited files, and I would hate to have to convert these files to % INSERT-type SQL to run them through psql.. Is there a way one can % specify a table structure with raw field widths and then just pass it a % flat file? pg_loader is supposed to handle this. http://pgfoundry.org/projects/pgloader -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] -- 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] PostgreSQL vs FreeBSD 7.0 as regular user
In article <[EMAIL PROTECTED]>, Greg Smith <[EMAIL PROTECTED]> wrote: % Looks like the PostgreSQL documentation here ( % http://www.postgresql.org/docs/current/static/kernel-resources.html ) is % now outdated. From http://www.manpages.info/freebsd/sysctl.8.html : % % "The -w option has been deprecated and is silently ignored." % % Looks like the correct thing to do here now is to edit the % /etc/sysctl.conf file, then issue: % % /etc/rc.d/sysctl reload I guess this would work, but you can still change variables from the command-line. It's just that -w isn't required any more (i.e., the same command works with or without the -w flag). I'm not sure the docs should change, since -w is still required at least on NetBSD. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] -- 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 variables
In article <[EMAIL PROTECTED]>, Paul Cunningham <[EMAIL PROTECTED]> wrote: % I use a bash script (similar to following example) to update tables. % % psql -v passed_in_var=\'some_value\' -f script_name % % Is it possible to pass a value back from psql to the bash script? If you run it like this $(psql -v passed_in_var=\'some_value\' -f script_name) and arrange for all the script output to be in the form var1="value 1" var2="value 2" var3="value 3" ... then var1, var2, etc will be set in bash (or ksh, or the posix shell). Note that there are no spaces around the equals signs. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Recovering real disk space
In article <[EMAIL PROTECTED]>, Adam Siegel <[EMAIL PROTECTED]> wrote: [...] % We perform a vacuum full after each mass delete. This cycle can happen % many times during over a couple of weeks. We are in a test lab % environment and are generating a lot of data. % % One of the problems we have is that the vacuum on the table can take up % to 10 hours. We also expect to see the physical disk space go down, but % this does not happen. Try vacuum full verbose next time to see what it's doing. Try reindexing after the vacuum is done. You may find an ordinary vacuuum is faster and just as useful as vacuum full assuming you're filling and deleting from the same table all the time. It won't free up space, but it will allow you to maintain a high-water mark. Look at the relpages column in pg_class to see which relations are using up the most space. If you're clearing out all the data for a set of tables, drop them and recreate them. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What means Postgres?
In article <[EMAIL PROTECTED]>, Daniel Schuchardt <[EMAIL PROTECTED]> wrote: % but there it is only clear that Postgres is based in Ingres. But i also % don't know what Ingres means. Ingres was a Spanish painter. Not every name has to mean something. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Optimising Union Query.
In article <[EMAIL PROTECTED]>, Rob Kirkbride <[EMAIL PROTECTED]> wrote: % I've done a explain analyze and as I expected the database has to check % every row in each of the three tables below but I'm wondering if I can This is because you're returning a row for every row in the three tables. % select l.name,l.id from pa i,locations l where i.location=l.id union % select l.name,l.id from andu i,locations l where i.location=l.id union % select l.name,l.id from idu i,locations l where i.location=l.id; You might get some improvement from select name,id from locations where id in (select distinct location from pa union select distinct location from andu union select distinct location from idu); this query might be helped by an index on location in each of those three tables, but probably not. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT
In article <[EMAIL PROTECTED]>,
Stephane Bortzmeyer <[EMAIL PROTECTED]> wrote:
% But it does not apply to primary keys containing a group of
% columns. In that case (my case), columns do not have to be UNIQUE. But
% they have to be NOT NULL, which puzzles me.
It does apply to primary keys containing groups of columns.
You can get the table definition you want by using a unique constraint,
but you should know that in SQL, unique constraints don't apply to
rows containing null values in the constrained columns. If you
do this:
create table x (
name TEXT NOT NULL,
address INET,
CONSTRAINT na UNIQUE (name, address)
);
your table definition will be as you want it, but the constraint you
want won't be there.
$ INSERT INTO x VALUES ('alpha');
INSERT 194224 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194225 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194226 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194227 1
--
Patrick TJ McPhee
North York Canada
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Determining when a row was inserted
In article <[EMAIL PROTECTED]>, Alex Turner <[EMAIL PROTECTED]> wrote: % I really wasn't suggesting it be put in the table structure at the DB level, % more a sidebar suggestion for people building schemas for companies. I can't % count the number of times I've been asked when something was inserted and we % didn't have an answer for the question. Wouldn't it be nice for a change to % be _ahead_ of the game? Just sticking a time stamp on the row doesn't solve this problem, though, unless you preclude the possibility of the row being updated. Typically, someone wants to know when a particular field held a particular value, and you need an audit table for that. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to create unique constraint on NULL columns
In article <[EMAIL PROTECTED]>, Andrus <[EMAIL PROTECTED]> wrote: % > Then redesign this as a many to many relation. That way someone can % > have access to one, two, three, four, or all departments. % % This means adding separate row for each department into permission table. % If new department is added, I must determine in some way users which are % allowed access to all % departments and add nw rows to permission table automatically. % % It seems more reasonable to use NULL department value as "do'nt know, all % departments allowed" But wouldn't you want to have an entry in the department table with NULL for the department ID? I mean, why should NULL act like NULL wrt foreign keys, but not wrt unique constraints? -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PSQL suggested enhancement
In article <[EMAIL PROTECTED]>, Roger Hand <[EMAIL PROTECTED]> wrote: % If pg outputs a simple xml format, it can easily be transformed via xslt % into OpenDoc table format, alternate html formats, or the alternate xml % format of your choice. Well, pg does output a simple xml format, which can be transformed via xslt, and yet here's a suggestion for an enhancement to do it differently. To be more explicit, if you turn on html output and turn off the footer \H \pset footer off what you get isn't always valid HTML, but it does seem to be well-formed XML, which can be easily transformed to the XML you really want. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Can a function determine whether a primary key constraint exists on a table?
In article <[EMAIL PROTECTED]>, Albe Laurenz <[EMAIL PROTECTED]> wrote: % > How can I check for the % > presence of constraints inside a function? % % select t.oid as tableid, t.relname as tablename, % c.oid as constraintid, conname as constraintname % from pg_constraint c join pg_class t on (c.conrelid = t.oid); or, perhaps simpler, select * from information_schema.table_constraints where constraint_type = 'PRIMARY KEY'; -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Restore database from files (not dump files)?
In article <[EMAIL PROTECTED]>, wheel <[EMAIL PROTECTED]> wrote: % I copied all of the database 'parts' to the new 'base' directory. I am % not sure how carefully anyone has read what I wrote. But it's so simple % what I'm asking about, or so it would seem to me. As several people have pointed out, you can't do this. % In another install of pg (assuming BM is not reading this and won't be % upset by abbreviations) there was a database at this location: % % c:\postgresql\data\base\16404 % % at least I think that is a database folder, I've never read anything % about where/how postgres stores the files for a database. Under the % 16404 folder are many other folders, I think they comprise that % particular database. % % On a separate postgres installation, ie different server, I want to move % that 16404 folder (with child dirs etc, what I assume are it's parts) to % what amounts to the same location: % % c:\postgresql\data\base\16404 As several people have pointed out, this will not work. You can copy c:\postgresql\data and everything under it, but you can't copy individual subdirectories and have it work. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Any form of connection-level "session variable" ?
In article <[EMAIL PROTECTED]>, John McCawley <[EMAIL PROTECTED]> wrote: % Is there any way I could establish this ID initially in some sort of % connection-level variable, and from this point on reference that % variable? We do this sort of thing using a custom C function, but I've been thinking lately you could use a custom variable class to do the same thing. Put custom_variable_classes = 'session' in your postgresql.conf, and you can have set session.myid = 23; then retrieve the value either by joining to pg_settings or using show session.myid; You can perform per-user initialisation with alter user set session.myid = 23; Which allows the value to persist between sessions. I haven't done anything with this idea so I can't say how well it works or whether there are downsides to it. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] OT: Canadian Tax Database
In article <[EMAIL PROTECTED]>, Richard Huxton wrote: % http://www.thestar.com/News/article/189175 % % "For instance, in some cases the field for the social insurance number % was instead filled in with a birth date." % % Unbelievable. Sixty years of electronic computing, fifty years use in % business and the "professionals" who built the tax system for a wealthy % democratic country didn't use data types. To be fair, this is not "the tax system". It's a staging database used for electronic filing, and it's pretty common to use typeless databases in the first stage of that sort of application. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Lifecycle of PostgreSQL releases
In article <[EMAIL PROTECTED]>, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: % Could people for once treat bugs as unacceptable instead an accepted % thing? It seems like you're responding to someone who's saying precisely that he considers bugs unacceptable and doesn't want to introduce them into a stable environment. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Check the existance of temporary table
In article <[EMAIL PROTECTED]>, Martin Gainty <[EMAIL PROTECTED]> wrote: % Assuming your schema will be pg_temp_1 Not a particularly reasonable assumption... % vi InitialTableDisplayStatements.sql % select * from pg_tables where pg_namespace = 'pg_temp1'; pmcphee=# select * from pg_tables where schemaname like 'pg_temp%'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +---++++--+- pg_temp_2 | x | pmcphee| x | f | f| f (1 row) pmcphee=# select * from x; ERROR: relation "x" does not exist But the test itself is problematic. I think this query is better. select pg_table_is_visible(pg_class.oid) from pg_class, pg_namespace where relname = 'x' and relnamespace = pg_namespace.oid and nspname like 'pg_temp%'; >From the same session where the select failed: pmcphee=# select pg_table_is_visible(pg_class.oid) pmcphee-# from pg_class, pg_namespace pmcphee-# where relname = 'x' and pmcphee-# relnamespace = pg_namespace.oid and pmcphee-# nspname like 'pg_temp%'; pg_table_is_visible - f (1 row) If I go on to create the temp table in the current session, this returns pg_table_is_visible - f t (2 rows) so you need to be ready for more than one row, or sort the output and put a limit on it. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Re: The rule question before, request official documentation on the problem
In article <[EMAIL PROTECTED]>,
Chris Travers <[EMAIL PROTECTED]> wrote:
% DO ALSO rules involving NEW are fundamentally dangerous to the integrity
% of data because NEW is not guaranteed to be internally consistent. DO
% INSTEAD rules are fine (there is only one NEW), as are any DO ALSO rules
% involving OLD.
It seems to me that this sort of dogmatism is fundamentally dangerous.
CREATE TABLE x (a varchar(20) PRIMARY KEY, b INT NOT NULL);
CREATE TABLE y (a varchar(20) NOT NULL, b INT NOT NULL);
CREATE RULE y_ins AS ON INSERT TO y DO UPDATE x SET b=b+new.b WHERE a=new.a;
CREATE RULE y_del AS ON DELETE TO y DO UPDATE x SET b=b-old.b WHERE a=old.a;
INSERT INTO x VALUES ('a', 0);
INSERT INTO y VALUES ('a', 2);
INSERT INTO y VALUES ('a', 2);
SELECT * FROM x;
a | b
---+---
a | 4
DELETE FROM y;
SELECT * FROM x;
a | b
---+---
a | 2
The DO ALSO rules involving OLD didn't do so well here.
The section on rules v. triggers could do with a caveat or two, but
it's a bit much to call them "fundamentally dangerous".
--
Patrick TJ McPhee
North York Canada
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] can't start tsearch2 in 8.2.4
In article <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> wrote: % On Apr 20, 11:38 pm, [EMAIL PROTECTED] wrote: % > When I try to initiate tsearch2 in 8.2.4, I got the following error. % > % > $ psql emedia_db < tsearch2.sql % > SET % > BEGIN % > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index % > "pg_ts_dict_pkey" for table "pg_ts_dict" % > CREATE TABLE % > ERROR: incompatible library "/usr/local/pgsql/lib/tsearch2.so": % > missing magic block % > % > .. % > % > Does anyone know if there is a fix available? % > % > Thanks % % I have seen discussion about adding PG_MODULE_MAGIC to .c or .h code. % Does anyone have suggestion about where I should add this to tsearch2 % code, and how should I rebuild tsearch2 with this? Probably the easiest thing is to use the tsearch2 from the 8.2 contrib directory. It already works with 8.2. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Moving Tablespaces
In article <[EMAIL PROTECTED]>, Allen Fair <[EMAIL PROTECTED]> wrote: % Do you or (or anyone else) suggest a method for moving databases from % one server to another without the time consuming dump/transfer/restore % process? Anything in the contrib directory or a good management tool? If you want to transfer an entire server to a different machine (i.e., not just a database, but all the databases), you can simply copy the files, assuming the other machine has the same postgres version and is architecturally compatible. Tom's answer refers to mixing table files from one postgres server with table files from a different postgres server. If you need to do something like that, the answer is to use replication to move the data over as it changes. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Plans for 8.2?
In article <[EMAIL PROTECTED]>, Harry Jackson <[EMAIL PROTECTED]> wrote: % I am not aware of Oracle etc having a seperate company that sells % replication on top of their database although I could be wrong. There's more than one third-party replication offering for Oracle. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(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: [GENERAL] Temporary table visibility
In article <[EMAIL PROTECTED]>, James Croft <[EMAIL PROTECTED]> wrote: [given a bunch of temporary tables called session_data] % How can I determine if one of the above relations is a temporary % table in the current session (one of them, the first in ns 2200, is a % normal permanent table)? If there's data in the table, you could "select tableoid from session_data limit 1", then check the namespace corresponding to that table. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PSQL Data Type: text vs. varchar(n)
In article <[EMAIL PROTECTED]>, Jim Nasby <[EMAIL PROTECTED]> wrote: % Not sure if it's still true, but DB2 used to limit varchar to 255. I % don't think anyone limits it lower than that. Sybase: 254. Silently truncates. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] primary keys
In article <[EMAIL PROTECTED]>, Klint Gore <[EMAIL PROTECTED]> wrote: % works for me on version 8.1.3 % % SELECT attname % FROM pg_index %JOIN pg_class ON (indrelid = pg_class.oid) %JOIN pg_attribute ON (attrelid = pg_class.oid) % WHERE indisprimary IS TRUE %AND attnum = any(indkey) %AND relname = $tablename; This will work on 7.4, 8.0, or 8.1 SELECT attname FROM pg_index JOIN pg_class as c1 ON (indrelid = c1.oid) JOIN pg_class as c2 ON (indexrelid = c2.oid) JOIN pg_attribute ON (attrelid = c2.oid) WHERE indisprimary AND c1.relname = $tablename ; No arrays are hurt by this query. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Clustered table order is not preserved on insert
In article <[EMAIL PROTECTED]>, Jim C. Nasby <[EMAIL PROTECTED]> wrote: % Keep reading... from the same paragraph: % % Clustering is a one-time operation: when the table is subsequently % updated, the changes are not clustered. But this isn't really relevant to the question. More to the point is this tidbit from the documentation for SELECT: If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce. This is not necessarily the order in which they're stored on disk. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql \echo strangeness with :variables
In article <[EMAIL PROTECTED]>, Jerry Sievers <[EMAIL PROTECTED]> wrote: % [EMAIL PROTECTED] % = \set date `date '+%Y%m%d'` % % [EMAIL PROTECTED] % = \echo :date % 20060524 % % [EMAIL PROTECTED] % = \echo foo_:date % foo_:date <-- Was expecting this to expand... see below variables need to be space-delimited in \ contexts. Work-around: \set tn 'foo_' :date \echo :tn create table :tn(); -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(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: [GENERAL] are there static variables in pgsql?
In article <[EMAIL PROTECTED]>, Yavuz Kavus <[EMAIL PROTECTED]> wrote: % i am writing a recursive procedure in pl/pgsql. % i need to check whether a condition is true in any step of recursive calls. [...] % i think i may achieve this with a static variable(shared among all calls). I'm not sure a static variable is the right way to achieve this, but you could use a custom_variable_class for this. Add this to your postgresql.conf: custom_variable_classes='global' Then you can set and show variables prefixed by global.: set global.success = 'true'; -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Performance Question
In article <[EMAIL PROTECTED]>, Terry Lee Tucker <[EMAIL PROTECTED]> wrote: % elements of 50 thousand records on 8 structurally identical databases. We % threw together the script and decided to just delete the record and re-insert % it with the data that was brought into sync. Now the question: Is it just as % fast to do it this way, or is there some hidden advantage to performing an % update? If you have foreign key relationships to the table being updated, then deleting from that table will often be slower than updating. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Physical block structure in PostgreSQL
In article <[EMAIL PROTECTED]>, Spendius <[EMAIL PROTECTED]> wrote: % (I read the pages % http://www.postgresql.org/docs/8.1/interactive/storage.html % and saw things regarding files and "pages" that are "usually 8k"-big % etc. but % saw no further info about "blocks" - they speak of "items" here: what % is it ?) An item is the thing that's stored on the page. For instance, a database table is stored in a bunch of pages in some file. Each row in the table is stored as an item on a page, starting with a HeapTupleHeaderData. The structure of an item for an index page might be different, though. I found there was enough information in the section you cite to write a simple data dumping tool in an emergency a while ago. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Restoring database from old DATA folder
In article <[EMAIL PROTECTED]>, RPK <[EMAIL PROTECTED]> wrote: % I tried to modify the parameters of the .CONF file when the pgsql-8.1 % service was running. The next time I started, it displayed error: "The % service did not listen to local host". (something like this). % % So I copied the old DATA folder and un-installed PostgreSQL. I again % re-installed it and replace the new DATA folder with the old one. Both % previous and new are same versions and same settings were used during % installation. I think what you need to do is to fix whatever's wrong with your config file. When you made a copy of the old data folder, you copied the config file with it. When you restored the copy, you restored the config file. Do you have a copy of the version that was used the last time the database started? >From what you've said so far, I suggest you search for listen_addresses and set it to '*'. If the database doesn't start, I strongly suggest you look in the log for the error messages and report them verbatim to the list. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Deathly slow performance on SMP red-hat system
I have a Red Hat ELsmp system running on a dual opteron hp server with 16 gigs of memory. I believe the RH installation is straight out of the box. I've compiled postgres 8.1.4 from sources. The problem is that query performance is horrible, and I can't think why, although it seems clear the problem is outside the query engine. For instance, "select 1" takes on the order of 100ms. Explain analyze doesn't shed much light on why this might be: explain analyze select 1; QUERY PLAN Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.006 rows=1 loops=1) Total runtime: 0.059 ms (2 rows) But if I turn on duration logging, I get timings like LOG: duration: 91.480 ms I was able to pg_reload 80 gigs of data in a reasonable amount of time, but individual queries are taking a long time. I'm wondering if anyone's seen anything like this. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Deathly slow performance on SMP red-hat system
In article <[EMAIL PROTECTED]>, Joshua D. Drake <[EMAIL PROTECTED]> wrote: % Patrick TJ McPhee wrote: [...] [the query is "select 1"] % > But if I turn on duration logging, I get timings like % > LOG: duration: 91.480 ms [...] % Vacuum? Analyze? I had autovacuum on initially, but turned it off. The slowness was in evidence from the point the data was loaded, when presumably vacuum would be superfluous. The data is analyzed. Right now, I'm not so worried about my real data as "select 1" being two orders of magnitude slower than I'd expect it to be. Steve Poe asked if I've modified postgresql.conf, and if the database and logs are on separate volumes. The .conf file has the memory parameters (shared buffers, work mem, effective cache size, etc) bumped up quite a bit. We have the block size set to 16k and the statistics target has been increased from the default. I had some of the planner costs adjusted as well, but they don't seem to be material to the problem. It's basically a copy of the .conf file that's working well in production on similar hardware under NetBSD. The logs and data are all one file system, which seems to be on a logical volume with a single disk sitting under it. Florian Pflug reports that he had a similar problem due to a slow RAID controller driver, to which I have no comment. Thanks for your comments. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Record creation date
In article <[EMAIL PROTECTED]>, Rafael Barrera Oro <[EMAIL PROTECTED]> wrote: % Hello everyone! i am checking a database due to some inconsintencies and % i was wandering if its possible to find out the creation date of a % record (using the oid, whatever...). Maybe i am saying something stupid % (since i am no DB expert) so if this is the case, please point it out % gently. Whatever: xmin, xmax These give the range of transaction ids over which the data in a row are valid. If xmin is 2, it means the change was made long enough ago that vacuum has frozen it to avoid transaction wrap-around (or that you ran vacuum freeze). Otherwise, the xmin can be compared to the xmin of some table with a timestamp to get the approximate time the record was last changed. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Deathly slow performance on SMP red-hat system
In article <[EMAIL PROTECTED]>, Florian G. Pflug <[EMAIL PROTECTED]> wrote: % You could try doing: % % begin; % select 1; % select 1; % ... % rollback; Yes, I tried that, but each select is on the order of 100ms. % off. If that doesn't reveal a possible cause, then I'd suggest that % you strace the backend you're connected to, and try to see where it's % spending it's time. Since 100ms for a simple "select 1;" is way out I tried that, but I don't have the results handy, and the machine's not responding now. I remember the first time I looked at the result, I thought it was spending an inordinate amount of time in gettimeofday, but I also had an strace which showed time in send, which is where I think the problem is. I'm pretty strongly convinced that the time is being spent sending data to the client. Actually I measured the full round trip and found it was taking on the order of 200ms, which is consistent with no time at all to perform the select, but 100ms each to send the query to the db and the result to the client. I think I need to checkout the socket layer on this machine. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
