4. It uses the same code as the SQL engine to handle expressions, so
there's no conflict between understanding of how floating-point or
timestamps should behave in corner cases.
Against it is the fact that it's statically typed and fairly inelegant.
--
Richard Huxton
Archonet Ltd
Joshua wrote:
I checked the table and found that none of my fields in the SELECT
statement contain NULLs.
Any other suggestions?
Please post the queries you used.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions
s a
pattern to your problems.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Paolo Bizzarri wrote:
On 5/30/07, Richard Huxton <[EMAIL PROTECTED]> wrote:
Paolo Bizzarri wrote:
> We use postgres as a backend, and we are experimenting some corruption
> problems on openoffice files.
1. How are you storing these files?
Files are stored as large objects. They
.0.0.1 or adding your own if you don't want "trust" security.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
he manuals for full details.
Of course, if you have different rules for each database you'll need to
make more changes than if you have two or three rules that apply to a
whole installation.
With 8.2 you can control access to databases via ROLEs as well as access
to schemas,tables etc.
ith a libpq call you could set "sslmode", but I'm not sure if you can
do that from the command-line.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
, non-
commercial, does not matter - available for Postgres.
The activity stats are in the pg_stat* tables/views. There are plugins
to a range of monitoring systems, e.g. nagios/munin.
--
Richard Huxton
Archonet Ltd
---(end of broadcast
error back from an insert you need to catch it and
rollback yourself.
Of course by default, all PostgreSQL statements execute in their own
transaction. That would mean your SELECT would be fine, and implies
something in your JDBC setup is issuing "BEGIN" without your knowledge.
values
CONTEXT: PL/pgSQL function "function1" line 3 at block variables
initialization
I think it is insane.
Any comments welcome...
What should happen when you try to violate a NOT NULL constraint?
Or are you saying "a" should be initialised with some randomly valid values?
--
ple
disks (well, mount-points), there's no need to play with tablespaces at all.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
s ... FROM ;
ANALYSE my_values;
SELECT * FROM main_table JOIN my_values ON main_table_column =
my_values_column
Of course, that assumes you have your values one per line - see the
manuals for details of what COPY can handle.
--
Richard Huxton
Archonet Ltd
--
cases
what people want is the ability to have a value of type 'number in range
1-20 or text "n/a"' and there's not a simple way to provide that, so
they use null.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
bases with you.
Now, I find the Debian approach complicated and fiddly, but I suspect
that's because what it's doing is complicated and fiddly.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to incr
e oracle vs pg connections.
Second - it's not just that your function does the inserts in the
context of a single transaction, is it? That would mean you're just
seeing the original error repeated.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
g in your looping through tuple-status
2. A bug in execute_for_fetch() filling the tuple-status array.
What happens if you elog the whole array (just to get the ref numbers) -
that should show whether DBI is filling the array incorrectly.
--
Richard Huxton
Archonet Ltd
-
terns;
patt
---
abc%
%123%
(2 rows)
richardh=> SELECT tgt,count(*) FROM targets, patterns
WHERE tgt LIKE patt GROUP BY tgt ORDER BY tgt;
tgt| count
-------+---
abc123def | 2
ghi123def | 1
(2 rows)
HTH
--
Richard Huxton
Archonet Ltd
take a copy of
sth->errstr too:
my ($err,$errstr) = ($sth->err, $sth->errstr);
push @$tuple_status, [ $err, $errstr_cache{$err} ||= $errstr,
$sth->state];
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading
. I'm surprised you're not
just getting a crash.
snprintf(output[0], sizeof(int), "%d", counter);
snprintf(output[1], sizeof(float), "%.5f", result);
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: i
Right?
BUT...what if child table and parent table both use the same sequence
for the primary key. Would the duplication still be an issue?
Well, if you *always* use the sequence you'll be OK (until you run out
of numbers), but it won't stop you manually supplying your own values.
--
users). You should also notice some
substantial performance improvements compared to 7.0.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
test;
t1| t2| t3
-+-+
21:12:30.346289 | 21:12:30.346289 | 2007-06-18 21:12:30.346289
(1 row)
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
ing the wrong type.
Trying to access a dropped column via assignment or RAISE NOTICE gives
errors, which makes me think it's the RETURN statement.
Any developers care to comment?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> writes:
Any developers care to comment?
plpgsql isn't very bright about tuple descriptors containing dropped
columns. The immediate problem is that compatible_tupdesc() doesn't
think a tupdesc containing one column matches on
iew, but I suspect left over from manual methods of
working.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
n is, how can we roll forward from our time of pg_dump, to
our most recent WAL (in case of failure - touch wood).
Can't be done I'm afraid.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 9: In versions below 8.0, the plann
ee, that's ~1.5MB/sec. Something is horribly
wrong there - you could do better than that with a USB 1 drive.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
at a three-part query.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
John D. Burger wrote:
On Jun 21, 2007, at 09:22, Richard Huxton wrote:
Naz Gassiep wrote:
Hi,
If I have a table with users and a table with messages, is it
possible to have a query that returns user.* as well as one extra column
with the number of messages they have posted and the data
to processors. Or
simulate such a thing by having processors call an update_jobs()
function. That way you can pre-allocate jobs to processors (at perhaps a
cost in efficiency if the number of jobs in-queue falls below the number
of processors).
--
Richard Huxton
Archonet Ltd
-
sn't use pg_dump.
--
Richard Huxton
Archonet Ltd
---(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
sql
Second one is easier to scroll through/edit.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
ISTINCT projectname but make no difference. If I
take the 'restrictions' join out it's fine. I've also tried prefacing
all column names with table names without any change
Try the table aliasing, then post the query again (oh, trim the perl/php
if you could) along with some sample data, the results and what the
results should be. Otherwise there's not much anyone can say.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
cha wrote:
But am not able to import the tables with batch file, though am able to run
the same command from the prompt successfully.
Why? What errors are you getting?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don&#
ly want to turn off constraints and indexes and
then re-apply them after the import.
Is there a good reason why you're not using pg_dump/pg_restore for this?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
27;ll mean dumping your database(s) and running initdb again.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
In general, the only ORDER BY you can rely on is one applied to the
final results of your SELECT.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
arter) and then either:
1. rename the table in the dumped file
2. rename it after importing
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Ashish Karalkar wrote:
Hello all,
I want to set Auto vaccunme in such a way that it will start in the Mid night
(12:00 A.M.)
Is there any way to do this except Cron Job for Linux
What's wrong with "cron"? It's the tool that's designed to run timed jobs.
--
Rich
Ashish Karalkar wrote:
Thnkas Richard for your replay.
Actually ,I was looking for some setting in postgresql.conf file
Is there any one?
Not to run at just a specific time.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2
storing them as
files? What are the main advantages you're seeking?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
very same" query on the
"very same" data then something else must be happening in the
background. Check the output of top/vmstat (or Windows equivalents) when
this happens and see if there's a clue.
--
Richard Huxton
Archonet Ltd
---(end of broad
ource Consumption" linked
above too. In particular work_mem is *per sort*, which means one query
can use several times the amount set.
If you post the values for the settings listed in chapter 17.4.1 of the
manuals and a description of what your machine is like, what else it is
doing then
;s not updating correctly. If
it's only happened the once, that will make it very hard to track down.
If it doesn't do it again in the next day or so, I'd be inclined to
REINDEX the table, in case it is in a slightly odd state.
--
Richard Huxton
Archonet Ltd
---
x27;ll probably want
to read up on those too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
o last longer.
Before you go to too much effort though, I'd check that you're really
going to gain a useful performance boost.
--
Richard Huxton
Archonet Ltd
---(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
h-lookup-table using hash
library of choice.
2. Split the article into "words" (however you define that)
3. Use your hash table to lookup each word from the article.
4. Stop on first match
Like Steve Atkins says, I'd use Perl instead of C++ and go home early :-)
ar "$1"SQL state:
42601Context: SQL statement in PL/PgSQL function "parseinsert" near
line 15Is there any workaround? I want to create a temporary table,
copy from and parse the table, all with dynamic path depending of how
the function is called.Thank'sCharles
Try buil
ou can lend a helping hand to.
--
Richard Huxton
Archonet Ltd
---(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
Dave Page wrote:
Richard Huxton wrote:
Charles Pare wrote:
Wow, it works great
Thank's for the quick answer
12 minutes? I've seen bug-patches turned around quicker than that by Tom
;-)
Yeah, that's really quite disappointing Richard - you need to pull your
socks up :-)
connection for template0 all the
time? Why won't pg_dump include a line to accept connection for
template0 just before it writes "\connect template0" and then remove
the permission after it is done with template0?
Might be a bug, but first; did you dump using 8.2's pg
m not sure what PostgreSQL is doing for you here, unless you need some
sort of "buffer" to cope with network bandwidth problems.
Why not just have a secured application sitting in the dmz/on firewall
and connect to both sides transferring for you?
--
Rich
se Perl to hook it all together, but you mentioned jdbc, so
presumably you'll be going for Java.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
around it. You will probably lose data on any damaged pages.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> writes:
Alain Peyrat wrote:
Initial problem:
# pg_dump -O dbname -Ft -f /tmp/database.tar
pg_dump: query to get table columns failed: ERROR: invalid memory alloc
request size 9000688640
After some research, it seems to be relate
r for
high read applications, but write heavy applications suffer from poor
performance.
You might find the "ltree" add-on in contrib useful. Look in the
"contrib" directory of your source installation, or the addons/extras
package of your distribution.
--
Rich
back to yourself using dblink() or dbilink() -
see contrib/ for details.
Don't forget to consider what it means to have a connection string in a
function-body either.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1:
function A. That
will give you a separate session which can commit/rollback separately
from the original.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
ou gave to pg_start_backup, as well as the starting and
ending times and WAL segments of the backup. If you used the label to
identify where the associated dump file is kept, then the archived
history file is enough to tell you which dump file to restore, should
you need to do so."
AlJeux wrote:
Richard Huxton a écrit :
1. Have you had crashes or other hardware problems recently?
No crash but we changed our server (<= seems the cause).
First try was using a file system copy to reduce downtime as it was two
same 7.4.x version but the result was not working (ma
le. The index covers the
columns that make up the primary key or unique columns (a multicolumn
index, if appropriate), and is the mechanism that enforces the constraint."
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: H
angga erwina wrote:
hi all, anybody can you tell me how to measure
performance slony to replicate?? where site is i can
get reference for this topik??
What "performance" are you trying to measure?
--
Richard Huxton
Archonet Ltd
---(end of
e free to do so. That means you might
upgrade and have your application stop working.
So - good practice says don't rely on ORDER BY except in the outermost
level of your query.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TI
encounter a COMMIT further down the file.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
k schemas were introduced in 7.3 so
before that should be easier.
There is a chance that you might reduce the problem by REINDEXing the
table concerned every night. That's just a guess though, and you're real
solution will be to upgrade to something more recent.
--
Then have myimg.php read the image data for 123 from the DB and return it.
This is almost certainly much slower than just streaming the image from
the filesystem. However, cacheing effects might mean you don't care.
--
Richard Huxton
Archonet Ltd
---(e
ser_id can probably be an int4.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Zlatko Matić wrote:
Hello. Is there any way to hide database structure (at least
functions and triggers) from a superuser/administrator?
No. Otherwise they can't be an administrator.
--
Richard Huxton
Archonet Ltd
---(end of broa
search_path = public,lookups;
Seems to work for me on 8.2 - you'll need to disconnect and reconnect to
see it take place though.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ext <> 'F'::text))
In this case, look at the actual times. There are two possibilities:
1. The first query had its data/indexes in cache whereas the second
didn't. Run each three times in a row and see if the times stay roughly
constant.
2. Calls to get_cem_for_directburial() can vary
Francisco Reyes wrote:
Is there a way to unset a role's search_path?
I had set one role with a particular search path. Now want to take that
off so the user can get the database's search_path setting.
ALTER ROLE ... RESET search_path;
--
Richard Huxton
Ar
orks). I'd suspect readline or similar. Try
something like "rpm -q --requires postgresql-client" (you'll need to
check the details, haven't used rpm much recently) to see what packages
psql is depending on. Then just check they look OK for your installation.
--
Ri
t does a table lookup - see if that makes
things faster.
Then I'd just write a couple of trigger functions to keep the cache
table up-to-date and join against it. That will let the planner see
common values and make better predictions for its plans.
If you (or anyone else) would like to explore f
ersions on the various
Linux/BSD/Mac OS-X boxes they have, but there's no reason why they
couldn't be the same version.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
r you.
Failing that, you can get people to save as a tab-separated-values file
(possibly via a macro so they just have one button to push). That is
easy to import via COPY.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
wrong, in which case you might stop the
system from starting. Your logs should say why.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
tched off DEP on the new server.
It's not your server, it's your clients.
--
Richard Huxton
Archonet Ltd
---(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
em manually tweaking these rails migration scripts?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
s also the simplest way to ask
core to tweak the line too: I want to change line nnn in file fff to add
the following...
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
7;t analysed the tables either.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
e all wrong.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
an any
body please suggest what is going wrongThanks In advanceWith
Not sure what you're doing wrong, but there's no need to do this. Use
pg_dump -Fc ...
This format is already compressed.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)-
ave a separate collection of schema-creation/update
scripts that they keep under version control. All changes are then
through running these.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensiv
d come up, hit the spacebar and not find anything slowing the
system down. Obvious once, I'd got the system monitoring turned on, but
PITA until then.
Moral: If it's not doing something immediately useful, I don't want it
running on my server.
--
Richard Huxton
Archonet Ltd
taking out the old tsearch2 installation.
ALTER TABLE pagecontent ADD COLUMN textvector tsvector;
Incidentally, I tend to install tsearch2 in its own schema now, makes it
simpler to check what's installed. It does mean you need to do alter
your search_path though.
Oh, and 8.3 will have
didn't have an error, you could run a separate cron that checks
whether there are recent backup files and emails you if not (find ...
-mtime -1).
Or, if you want to make sure the restore has worked check the database
for a row with a recent timestamp.
--
Richard Huxton
Archone
wouldn't
use the index anyway - you're not filtering or sorting on it.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
n to sub-second responses though,
not if you're trying to return 6 million rows from an even larger table.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
rpose. Of course, faster to run might be at the cost of more
difficult to write. It might also mean your process code can crash the
database server.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
d query caching" feature, there's your
operating-systems file-cache and PG's buffers. Neither of which cache
query-results, but cache disk pages instead.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
have other partial indexes for different values of rcrd_cd, and
do they have similar problems? If this can be reproduced it might point
to something odd with bitmap scans.
Oh, remind me what version of PostgreSQL you're running?
--
Richard Huxton
Archonet Ltd
-
we can get someone
from the core developers to tell you the same thing I just have, and we
can forward that to your boss :-)
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
27;t help.
What you'll need to supply is:
1. The query
2. Details of the table(s) - number of rows, number of distinct values
in columns being matched against
3. Output of EXPLAIN ANALYZE for your query
4. A couple of details of your server (RAM, number of disks, processors etc)
I'm
ta into an import table (TEMPORARY table probably) and then
just use three queries to handle deletion, update and insertion.
Comparing one row at a time is adding a lot of overhead.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5:
now I'm off to beg them to
upgrade.
Hmm - should work in any 8.0.x, the development team don't add new
features in point releases. I'm not sure if this feature wasn't there in
7.4 too.
Might be a bug affecting you though - could be worth checking the
release-notes in the
x27;re worried about them removing the hard-disk then you'll need to set
up an encrypted filesystem and figure out a way to get a password
entered on reboot.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have
g a lot of overhead.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
o upgrade" was the whole body
of the message.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ccess to the files/application, it's game over.
--
Richard Huxton
Archonet Ltd
---(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
an keep the key protected. If you prevent access to a
file containing the key then you can do the same to the database and
application files.
Can you tell us what you're trying to do? What are you going to deploy,
and what threats do you want to protect against?
--
Richard H
1 - 100 of 2283 matches
Mail list logo
