sn'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@po
rt 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
7;;
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
ck 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 Yor
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.
--
he 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 cumbersom
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
w, 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
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.
--
Patri
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
% > OI
t 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
andard 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
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
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
hing 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
c
ow 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 tsear
e 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 bro
ace.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
27;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
peless
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
;
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 McPhe
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
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]
ed 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 Y
saction 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
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
load 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:
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)
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 broadcas
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
[EMA
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'
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/rea
he 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
lename
;
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
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 C
manent 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)-
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.
--
e 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)
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
x27;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)
d. 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]
('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
rom 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 McPhe
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
N
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
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
46 matches
Mail list logo
