On 11/10/2011 12:05 PM, David Johnston wrote:
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Wes Cravens
> Sent: Thursday, November 10, 2011 11:54 AM
> To: pgsql-general@postgresql.org
> Sub
On 11/9/2011 7:19 PM, Wes Cravens wrote:
> I have an adjacency list kind of table
>
> CREATE TABLE thingy (
> id int,
> parent int
> );
>
> I'd like to be able to write a procedural function that returns a row or
> rows from this table with an ap
On 11/9/2011 7:34 PM, David Johnston wrote:
> On Nov 9, 2011, at 20:19, Wes Cravens wrote:
>
>> I have an adjacency list kind of table
>>
>> CREATE TABLE thingy (
>>id int,
>>parent int
>> );
>>
>> I'd like to be able to write a
ow IN SELECT * FROM thingy
LOOP
RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = id]
END LOOP,
RETURN
Any help much appreciated,
Wes
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
d that is fixed in
8.2 or 8.3 (don't recall which I saw it in), but have never gotten
confirmation from anyone on that.
Wes
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index s
Just a follow-up on this... The REINDEX took about 2 1/2 days. I didn't
gain much disk space back - a full backup takes just as long as before, but
the vacuum time dropped from 30 hours to 3 hours.
Wes
>> 1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX
x27;re doing large operations like
> this.
I have checkpoint_segments set to 60, and no warnings showing up in the log.
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
On 1/24/08 12:48 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> Wes <[EMAIL PROTECTED]> writes:
>> I'm running 8.1.4. Assume I have exclusive access to the DB.
>
> You really ought to update to 8.1.something-newer, but I digress.
I was planning on upgradi
E had some advantages, but can't
find the information. Is there a performance hit with REINDEX during
creation because of locking issues?
2. I'm assuming REINDEX would avoid the time involved in recreating the
foreign key constraints?
3. With a REINDEX DATABASE, how can I mo
threads on
various linux operating systems. The difference in our application by
simply relinking at run time (LD_PRELOAD) with libtcmalloc was astounding.
Wes
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
U at 100%.
I never saw any resolution to this thread - were the original tests on the
Opteron and OS X identical, or were they two different workloads?
Wes
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
We reindex when it starts taking 24 hours to
vacuum.
The above referenced posting is from May 1, 2006. Is pgsql 8.2 the first
release to have this code?
Wes
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
We reindex when it starts taking 24 hours to
vacuum.
The above referenced posting is from May 1, 2006. Is pgsql 8.2 the first
release to have this code?
Wes
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
illions of documents).
Is anyone aware of any such solutions for PostgreSQL, open source or
otherwise?
Thanks
Wes
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
expirations. So this particular problem may be better solved without any timer functionality either in OR out of the database... if you did have a cron job run to check, you would probably just have it set a boolean field on expired records or something of that sort, and run it a little after midnight, at the start of each day, assuming durations were always being measured in days.
Best of luck,-- Wes Sheldahl[EMAIL PROTECTED]
obably* included among the applications you can install during the Fedora installation process itself. To install the client and server packages via yum, you would do:
yum install postgresql postgresql-serverCheers,-- Wes SheldahlSheldahl Consulting LLChttp://www.sheldahlconsulting.comPhone: 859-338-3
well. Right now it's running as pgsql, UID 70, which I'm sure is the default. Thanks,
Wes SheldahlOn 10/21/06, Marc G. Fournier <[EMAIL PROTECTED]> wrote:
-BEGIN PGP SIGNED MESSAGE-Hash: SHA1Are you running this in a FreeBSD jail under 6.1? It sounds like the problemthat I
t seems a little disingenuous to claim they support them on the site's front page. Oh well. (shrug)
-- Wes Sheldahl[EMAIL PROTECTED]
after restarting postgresql.
Versions/Environment:Postgresql 8.1.4 (installed from ports on FreeBSD 6.1)I've already tried reinstalling the postgresql81-server and postgresql81-client ports and their dependencies via portupgrade, but the symptoms persist. What else should I try?
-- Wes Shel
confirmation. Is there any stronger lock that would not
block SELECT foreign key references? I didn't find any documentation on
what type of lock is grabbed by a when a foreign key is referenced during
SELECT (or other).
Wes
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
e row locks before were single owner and were such that a
> second lock request for the same row would wait for the first to be
> released. Now effectively you have two levels of locks at the row level,
> the weaker of which conflicts with the stronger but not with itself. The
> thing about MVCC is that readers do not have to get either lock if they
> aren't trying to prevent modifications.
Wes
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ecord that job 1 may
already have created in its transaction).
3. Not conflict with foreign key reference locks
SHARE does not appear to be appropriate - it would fail #2. Maybe "SHARE
UPDATE EXCLUSIVE"?
Wes
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
; checking in restore has been improved...
Yes, that is what I did. I'm in the process of testing an upgrade from
7.3.x to 8.1.4 - export from 7.3.x and import into 8.1.4. Unfortunately,
I'm sitting at about 90 hours when I've got about an 80 hour window on a
long weekend...
Wes
much they'd charge for it...
Or if I get ambitious, dig into the code myself if I can figure out where to
start...
Wes
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
index (be it a primary key, or not), which
> prevents
> my plan from working :-(
That was a great idea - too bad it didn't pan out.
I don't suppose there's any (reasonable) way to directly insert into the
system tables to create the constraint? I could knock almost 2 days off of
n out.
That looked like a good shot.
Wes
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
kind of hardware
problem we encountered last year is rare, but it does happen. I've seen
similar things many times over the years. RAID doesn't help you when a
controller mirrors garbage.
> You should look into slony. You can replicate from one version of pgsql
> to another, a
backup. I'll be
switching to online backups once we get upgraded, but if a reload fails
there, I'll again have to fall back to the weekly source backup.
Wes
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
a
database that has referential integrity enabled. Even if there were an
error that crept in to the old database, I don't care - just add the
constraint so I can get back online. Right now I'm looking at around 80-90
hours total to do a reload. What's it going to be a year or
irtual.
Adding foreign key constraints, RSS is about 1.1 GB but virtual is slightly
over 2 GB.
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
, and
those added as a primary key constraint. Currently, pg_dump outputs the FK
constraints after the indexes are built, as the last steps. If I try to add
the FK constraints after loading the database definitions, but without any
indexes, I'm not sure what would
d RAID 1 on a third channel). There are two 2.4 GHz Xeon
processors).
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
ke to eliminate that second 40 hours so I can get
it down to a normal weekend.
Wes
---(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
story'.
Neither that file, nor any *.history file, is anywhere to be found. I can
not find this documented anywhere. What is this file? Does it have to do
with timelines?
Wes
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
On 4/30/06, Tony Lausin <[EMAIL PROTECTED]> wrote:
Hello all,
I'm working on a CMS which requires an open source database capable of
handling hundreds of thousands of users simultaneously, with a high
rate of database writes, and without buckling. We're talking somewhere
between nerve.com/catch2
ate two
indexes for each column - one with and one without the operator class. That
is also what was indicated in the original thread. Defining multiple
indexes on a given column isn't feasible, due to the database size (100
million rows per day).
Wes
---
en_US.UTF8. Leading
substring searches result in a sequential search instead of an indexed
search. Do I still have to initdb to locale=C, or is there a better option
now?
Wes
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
<http://archives.postgresql.org/pgsql-hackers/2002-07/msg00969.php>
How can I safely build indexes in parallel?
At this point, I'm only trying to build two at a time. I will be building
indexes for tables with any where from a few rows to 100 million rows on a
daily basis - I need to maximize p
oblem - or to be 100% safe should I not do the 'drop table'?. I was
afraid I might have to move the 'create table' outside of the transactions.
Many thanks
Wes
---(end of broadcast)---
TIP 1: if posting/reading throu
to hang.
3. Create header_dummy using psql
4. Drop header_dummy
5. Run application - works.
I can repeat this with the 'detail' table. It is 100% reproducible.
What's going on?
Wes
---(end of broadcast)---
TIP 3:
ystem catalog monkey business - just plain old SQL.
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
ne table per 6 hours?)
Thanks
Wes
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
A posting from December 2005 against 8.1.0 (bug #2114) seems to indicate
this should be fixed.
Am I missing something, or is there still a problem? Do I still need to
change '\.\ to '\\.'?
Should I be using PQescapeString on strings being passed to COPY?
Wes
have Postgres do it again. But maybe
there's no choice.
Wes
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> I don't believe it will discard anything on import if the database is
> SQL_ASCII encoded.
That might be worth a shot. I don't really understand the ramifications,
though, especially given Tom's warning. I guess as long as I don't care
abo
g from UTF-8.
I thought about that, but I do want to allow UTF-8 to be stored. I just
want it to replace illegal characters with some valid character so that
invalid records will load. Even if I preprocess the data, I can do no more
than that.
Wes
---(end of broadcast)-
8 byte sequence detected
I'd prefer not to add to the overhead by pre-validating every string, since
PostgreSQL validates it already. Is there a way to get the server to
replace invalid characters with something like blank instead of generating a
fatal error?
Wes
d .h files are for your OS.
>
> This configure attempt could be failing, because it can't locate the
> correct thread headers and/or libraries
Why would I not want to specify enable-thread-safety? I want to be able to
write threaded programs.
--enable-thread-safety works fine un
in the archives with krb5 and
threads. Am I missing something here?
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
t vendor throws such a clause into all their licensing agreements.
Sounds suspiciously like Microsoft...
I'm not a lawyer, but I believe things such as what vendors a government
entity is using is required by law to be public information.
Wes
---(end of broadcast
ts for the next result from a prior PQsendQuery, PQsendQueryParams,
PQsendPrepare, or PQsendQueryPrepared call, and returns it."
(doesn't mention COPY).
I have it working now. Thanks.
Wes
---(end of broadcast)---
TIP 1: if posting/reading through U
s. However,
pgsqlNoticeReceiver is never called.
What am I missing? I need to abort the transaction on any errors in the
COPY.
Wes
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-
calls instead of ECPG)?
Wes
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I'm pondering dumping the keys for A to a file, and B and C to another file,
sorting with uniqueness on the B/C file, then programmatically determining
which keys have been freed. I'm pretty sure this will be much faster, but I
had hoped to avoid an external process
SubPlan
-> Limit (cost=0.00..0.81 rows=1 width=0)
-> Index Scan using messages_i_orig_mdate on messages
(cost=0.00..35148.46 rows=43301 width=0)
Index Cond: (originator = $0)
Which seems like it should be much more efficient.
Wes
---(en
xists(select 1 from
B where BField=addresses.address_key limit 1) ) and ( not exists(select 1
from C where CField=addresses.address_key limit 1) )
Of course, all fields above are indexed.
There are foreign key references in B and C to A. Is there some way to
safely leverage that?
this restriction?
>
> Use --with-includes and --with-libraries as needed.
That doesn't get the library paths into the binary. If the libraries are
not in the default system search path, the user is screwed. Is there a way
to solve this? I think the previous --with-openssl=path set the
y can't find the libraries if
they are not in the default library search path of the user.
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
with 8.x. See:
<http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php>
What is the correct way to work around this restriction? Set
LD_LIBRARY_PATH?
This is on a variety of unix platforms.
Wes
---(end of broadcast)---
TIP 2:
the most offending indexes to separate drives probably isn't an option.
Wes
---(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
nce on starting one. The database is just a hair under one billion
rows, and could take the entire weekend or more to rebuild.
Wes
---(end of broadcast)---
TIP 6: explain analyze is your friend
ng our application, it takes a full weekend to do
the dump/load - the database is just under a billion rows.
Wes
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
w-level locking."
So, until 8.1 PostgreSQL had "something better than row-level locking" for
some things, but no row locking when needed? Or was it row locking is
there, but just no shared row locking?
Wes
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
goes wrong with the
load process.
I've never quite understood why a READ of a record with a foreign key
reference results in the referenced record being locked with more than a
shared lock.
Wes
---(end of broadcast)---
TIP 1: if posting/
Correct, the default setting for PostgreSQL 8.1 W32 value in postgresql.conf
is 'autovacuum' to 'on'.
You can see this and more settings in pgAdmin III by visiting 'Tools',
'Server Configuration', then the config file of your choice.
Now, if only I could setup my home to autovaccum.
-Origin
Go ahead and proceed through the click-throughs...this is still the same old
demo for development and testing only that they have had available for some
time.
This updated on Oct-28th only is for a newer
version(http://www.oracle.com/technology/software/products/database/xe/index
.html) of the sam
Again, the previously provide link is NOT the version that is licensed free
for commercial production use!
The version in the headlines lately suggesting a free Oracle database for
professional and production use is NOT YET PUBLICLY AVAILABLE [expected by
year-end]. Still, the link below is likel
Still, at least Oracle 10g provides for an easy GUI from which to configure
and perform imports and exports of data. Some of use have need for a
database that can dump all data and accept another series of new data...only
to be dropped again in a few days. The GUI tools make this MUCH easier -
es
Precisely the point I was trying to make sure everyone would understand
clearly. Although I don't have a copy of Oracle's suspected new license, if
it is close to the existing license verbiage, even though it is "crippled"
by having certain hardware and software limits, those limits are per
physic
EMAIL PROTECTED]
Sent: Monday, October 31, 2005 11:50 AM
To: Wes Williams
Cc: Postgresql-General; [EMAIL PROTECTED]
Subject: Re: [GENERAL] Oracle 10g Express - any danger for Postgres?
I assume they are probably thinking of a free for non-commercial use,
which is great and all, but I assume that lik
I presume this thread was all brought about by the /. article
http://developers.slashdot.org/article.pl?sid=05/10/31/0659254&tid=221&tid=1
87
According to the link provided in the /. article
(http://news.zdnet.com/2100-3513_22-5920796.html), Oracle has *proposed* a
free version by "year end". Obv
Even with a primary UPS on the *entire PostgreSQL server* does one still
need, or even still recommend, a battery-backed cache on the RAID controller
card? [ref SCSI 320, of course]
If so, I'd be interest in knowing briefly why.
Thanks.
-Original Message-
===snip===
...
every server I'
Type the following at the Windows command prompt (start, run, "cmd"):
convert c: /fs:ntfs /v
It will complain about locked files and perform the convert at the next
reboot, which you should do immediately.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jos
set sql_mode='MYSQL323';
Query OK, 0 rows affected (0.00 sec)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe
Sent: Tuesday, October 25, 2005 3:24 PM
To: Wes Williams
Cc: 'Jan'; pgsql-general@postgresql.org
Subject: Re: [GE
For what it may be worth, executing the same commands into MySQL
5.0.15-nt-max (Win XP Pro) the following it received:
mysql> create table test (i1 int);
Query OK, 0 rows affected (0.41 sec)
mysql> insert into test values (123913284723498723423);
ERROR 1264 (22003): Out of range value adjusted fo
What
platform? Solaris, FreeBSD, Linux, Windows
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Michael
UvhagenSent: Friday, October 21, 2005 6:10 AMTo:
pgsql-general@postgresql.orgSubject: [GENERAL]
passwordHi.I was installing po
w. To resolve my immediate
problem, I can just temporarily rename my hard drive. I'm just reporting
this as a problem that should be fixed.
Wes
install.log has:
make -C doc install
make[4]: Nothing to be done for `install'.
make -C src install
/bin/sh ../config/mkinstalldirs &qu
On 10/2/05 7:48 AM, "William ZHANG" <[EMAIL PROTECTED]> wrote:
> Yes, the Makefiles cannot deal with spaces correctly.
> Seems we should avoid use the `complicated' path.
Such paths are normal on systems with a GUI interface. They are not out of
the ord
System: Mac OS X 10.4.2
PostgreSQL: 8.1 b2
Running 'make check', I get the following failure:
rm -rf ./testtablespace
mkdir ./testtablespace
/bin/sh ./pg_regress --temp-install --top-builddir=../../..
--temp-port=55432 --schedule=./parallel_schedule --multibyte=SQL_ASCII
--load-language=plpgsql
=
-Usnet gateway such that the
originating email addresses are either removed or scrambled so that posting
to the mailing list doesn't result in your email address being plastered all
over Usenet? People that intentionally post to Usenet generally don't use a
replyable email add
now contains 393961361 row
versions in 2435100 pages
INFO: index "message_recipients_i_message" now contains 393934394 row
versions in 1499853 pages
After reindex:
INFO: index "message_recipients_i_recip_date" now contains 401798357 row
versions in 1765613 pages
INFO: index &quo
indexes in order should be much better.
Wes
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
at vacuum processes indexes in index order, not physical disk
order. I guess we add a periodic reindex to our maintenance procedures...
Wes
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On 3/30/05 12:09 PM, "Adam Siegel" <[EMAIL PROTECTED]> wrote:
> How can we physically recover "real" disk space for the rows that were
> deleted from the table?
vacuum full
Wes
---(end of broadcast)---
TIP 8: explain analyze is your friend
s time.
I guess the next step is to try reindexing a couple of the big indexes and
see if that helps.
Wes
---(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
ize).
We're going to try to test the 2.4.29 kernel tomorrow.
Wes
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
feet in releasing hardware monitoring compatible with 2.6
kernel. So, we're going to try a 2.4.29 kernel and hope that the problem is
fixed there. With any luck, by Friday I'll know if the kswapd problem is
fixed in 2.4.29 and if that solves the excessive vac
s on a separate volume.
I thought it was a 2.6 kernel, but it looks like it is 2.4.20. I need to
monitor the system when the vacuum is running to see if sar/top show
anything. I wonder if it's hitting the kswapd thrashing problem?
Wes
---(end of broadcast)---
SM size: 1000 relations + 100 pages = 5920 kB shared
>> memory.
>
> Well, you don't have a problem with FSM being too small anyway ;-)
Nope... Preparation for when deletes start kicking in down the road. If I
can only do a vacuum once a week, I've got to have lots of spac
INFO: "blah": found 0 removable, 366326534 nonremovable row versions in
3241829 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
Wes
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
uld that have
anything to do with the non-linear behavior?
Wes
---(end of broadcast)---
TIP 8: explain analyze is your friend
allback.
> Again, VACUUM VERBOSE info would be informative (it's sufficient to look
> at your larger tables for this).
I'll set that up to run tonight and see if it gives any clues. Last night,
vacuum ran over 5 hours.
Wes
---(end of broadcast)---
nce vacuum has to
sequentially read the entire database, I would have expected a linear
increase - about 1.5 hours now.
There are currently no deletes or modifies to the database - only inserts.
This is on PostgreSQL 7.4.5, RedHat ES 3.0.
Wes
---(end of
oad the database (takes a weekend),
can I define two indexes on the same field, one using operator classes and
one not (and have them automatically used as appropriate)?
Because of the time involved, I'm trying to hold off on another reload until
we upgrade to 8.x.
Wes
--
..
I'm not going to have to "initdb --locale=C" and am I? I looked at index
classes, and that doesn't appear to be something I want to do, due to
performance. What kind of performance hit do you actually take by using an
index class?
Wes
Pg_controldata shows:
Maximum leng
les, the data disk was still the heaviest hit
(expected no or little access there), and it beat the living daylights out
of my swap - pageins/outs like crazy. The I/O on the index disks was
negligible compared to the data and swap disks. I won't try that again...
Wes
---
try to create it's own
index anyway.
Is there something I'm overlooking, or is this a bug?
Wes
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED]
s just
working space to more efficiently build the initial constraint, or does it
actually write this to the database?
Wes
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
eplied to it). The previous statement had been that they had to be
in /etc/rc because startup scripts were now too late in the boot process. I
was just clarifying the reason.
Wes
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choo
On 1/20/05 10:27 PM, "Jonel Rienton" <[EMAIL PROTECTED]> wrote:
> have you tried using /etc/sysctl.conf and saving the shmax value there?
Unfortunately, the -p parameter does not appear to be valid, nor does
'strings -a' show 'conf' in the
1 - 100 of 118 matches
Mail list logo
